#sonic 819 autorun DEMO

import java.sql.Connection;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;

import ru.it.metasonic.ruspost.infopoint.utils.BOHelper;
import ru.lbpm.metasonic.businessobjects.DbHelper;
import ru.lbpm.metasonic.businessobjects.DbHelper.ValueWrapper;

import com.jcom1.api.dto.init.InitProcessInstanceData;
import com.jcom1.api.dto.interfaces.IProcessInstanceBean;
import com.jcom1.api.dto.interfaces.IStorageInstanceBean;
import com.jcom1.api.dto.interfaces.ISubjectInstanceBean;
import com.jcom1.api.exceptions.ConnectionException;
import com.jcom1.api.interfaces.IAuthorizationAdministrationApi;
import com.jcom1.api.interfaces.IAuthorizationApi;
import com.jcom1.api.interfaces.IBo20AccessApi;
import com.jcom1.api.interfaces.ICommonApi;
import com.jcom1.api.interfaces.IInstanceAdministrationApi;
import com.jcom1.api.util.Pair;
import com.jcom1.runtime.refinement.AbstractRefinement;
import com.jcom1.runtime.refinement.FunctionStateRequest;
import com.jcom1.runtime.refinement.FunctionStateResponse;
import com.jcom1.runtime.refinement.RefinementGenerator;

import de.metasonic.businessobjects.model.interfaces.IView;
import de.metasonic.refinement.global.api.ApiUtils;

/**
 * @generated
 */
/**
 * This method is called when the named function state is reached State:
 * автозапуск формирования StateType: FUNCTION
 *
 * Return values "FunctionTransitionDescription_WZGO8WRYEeaW6tb5t7D8Ag" when
 * result is "" to go to state Ожидание запуска
 *
 * @return return the id of the transition to follow e.g. // return new
 *         FunctionStateResponse("transitionXXXXXXXX");
 */
@RefinementGenerator(id = "InternalSubjectDescription_rhsOwWRXEeaW6tb5t7D8Ag")
public class Оператор_по_контролю_ОПС_ОтделаRefinement extends
        AbstractRefinement {

    @Autowired
    ICommonApi commonApi;

    @Autowired
    IBo20AccessApi accessApi;

    @Autowired
    IAuthorizationApi authApi;

    @Autowired
    IAuthorizationAdministrationApi administrationApi;

    private final String opsControlDescriptionId = "ProcessDescription_pn9a4VMdEeaL5PGyiwnPkA";
    private final String opsControlFirstStateDescrId = "FunctionStateDescription_NZI2wWqtEeaiwr50C3BsOA";

    private final String DB_URL = "jdbc:hsqldb:hsql://localhost/jcom1";
    private final String DB_USER = "sa";
    private final String DB_PSW = "";

    private final String UFPS_INDEX = "UFPS_INDEX";
    private final String UFPS_NAME = "UFPS_NAME";
    private final String POSTAMT_INDEX = "POSTAMT_INDEX";
    private final String POSTAMT_NAME = "POSTAMT_NAME";
    private final String UM_GROUP = "UM_GROUP";
    private final String UM_USER = "UM_USER";
    private final String UM_GROUP_AS_INDEX = "INDX";
    private final String UM_GROUP_AS_ROLE = "ROLE";
    private final String RUSPOST_DEPARTMENT_CONTROLLER = "RUSPOST-DEPARTMENT-CONTROLLER";
    private final String DEPARTMENT_CONTROLLER_GROUP_ID = "DEPARTMENT_CONTROLLER_GROUP_ID";

    private final String COMMON_INFO = "commonInfo";
    private final String POSTAMT = "infoPoint";
    private final String METADATA = "metaData";

    private int departmentControllerGroupId;
    /**
     * @generated
     */
    private final static Log log = LogFactory
            .getLog("com.jcom1.refinement.автозапуск_автоформирования_плана.оператор_по_контролю_опс_отделаrefinement");

    /**
     * This method is called when the named function state is reached State:
     * автозапуск формирования StateType: FUNCTION
     *
     * Return values "FunctionTransitionDescription_WZGO8WRYEeaW6tb5t7D8Ag" when
     * result is "" to go to state Ожидание запуска
     *
     * @return return the id of the transition to follow e.g. // return new
     *         FunctionStateResponse("transitionXXXXXXXX");
     */
    @RefinementGenerator(id = "FunctionStateDescription_G_53Y2RYEeaW6tb5t7D8Ag")
    public FunctionStateResponse functionавтозапуск_формирования(
            FunctionStateRequest functionStateRequest) {
        log.debug("\n");

        IInstanceAdministrationApi instanceAdminApi = null;
        try {
            instanceAdminApi = ApiUtils.lookupInstanceAdministrationApi();
        } catch (ConnectionException e1) {
            log.debug("error retrieving instanceAdministrationApi");
        }

        String departmentControllerGroupIdQuery = "SELECT ID AS DEPARTMENT_CONTROLLER_GROUP_ID FROM UM_GROUP WHERE NAME = ?";

        String ufpsQuery = "SELECT DISTINCT UFPS_INDEX, UFPS_NAME FROM POSTAMT_VIEW";

        String potentialOwnersQuery = "SELECT A.UM_GROUP AS UFPS_INDEX, A.UM_USER, B.UM_GROUP AS DEPARTMENT_CONTROLLER_GROUP_ID "
                + "FROM UM_GROUP_UM_USER A "
                + "LEFT JOIN UM_GROUP_UM_USER B "
                + "ON A.UM_USER = B.UM_USER "
                + "WHERE A.UM_GROUP = ? AND B.UM_GROUP = ?";

        String postamtQuery = "SELECT POSTAMT_INDEX, POSTAMT_NAME FROM POSTAMT_VIEW WHERE UFPS_INDEX = ?";

        try (final Connection connection = DbHelper
                .getConnection(DbHelper.PROP_DB_INT);) {
            log.debug("connection has been set");

            // form query as SELECT TOP 1 ID AS ...
            departmentControllerGroupId = getDepartmentControllerGroupId(
                    connection, departmentControllerGroupIdQuery,
                    RUSPOST_DEPARTMENT_CONTROLLER);
            log.debug(DEPARTMENT_CONTROLLER_GROUP_ID + ": "
                    + departmentControllerGroupId);

            List<Pair> ufpsIndexNamePairs = getUfpsIndexNamePairs(connection,
                    ufpsQuery);

            for (Pair ufpsIndexNamePair : ufpsIndexNamePairs) {

                String ufpsIndex = ufpsIndexNamePair.getFirstElement();
                String ufpsName = ufpsIndexNamePair.getSecondElement();
                log.debug("     ufps: " + ufpsIndex + " / " + ufpsName);

                // retrieve potential owners as:
                // users from current Ufps group that also belong to
                // RUSPOST-DEPARTMENT-CONTROLLER group
                // form query as ... WHERE a.UM_GROUP = 249030 AND b.UM_GROUP =
                // 13
                List<String> potentialOwners = getPotentialOwners(connection,
                        potentialOwnersQuery, Integer.valueOf(ufpsIndex),
                        departmentControllerGroupId);

                if (0 == potentialOwners.size()) {
                    log.debug("Skipping process creation for Ufps: "
                            + ufpsIndex + "/" + ufpsName);
                    log.debug("Reason: no potential owners were found for this Ufps");
                    continue;
                }

                // now as we got potOwners list
                // lets iterate over all postamts of current ufps
                List<Pair> postamtIndexNamePairs = getPostamtIndexNamePairs(
                        connection, postamtQuery, ufpsIndex);

                for (Pair postamtIndexNamePair : postamtIndexNamePairs) {

                    String postamtIndex = postamtIndexNamePair
                            .getFirstElement();
                    String postamtName = postamtIndexNamePair
                            .getSecondElement();
                    log.debug("     postamt: " + postamtIndex + " / "
                            + postamtName);

                    // ============================================================================================================
                    // now as we got single postamt
                    // lets create a process for it
                    log.debug("launching process");
                    IProcessInstanceBean processInstanceBean = null;
                    String procName = null;
                    try {
                        SimpleDateFormat sdf = new SimpleDateFormat(
                                "dd/MM/yyyy HH:mm:ss");
                        procName = postamtName + " Контроль ОПС ["
                                + sdf.format(new Date()) + "]";
                        String creatorId = commonApi.getSubjectInstanceBean(
                                functionStateRequest.getSubjectInstanceId())
                                .getEditor();
                        int priority = 3;

                        processInstanceBean = createProcessInstance(
                                opsControlDescriptionId, procName, creatorId,
                                priority);

                        log.debug("process '" + procName + "' has been created");
                    } catch (Exception e) {
                        log.debug("error creating process instance with name: "
                                + procName);
                    }

                    if (null != processInstanceBean) {
                        log.debug("retrieving prime BO ad setting pot :) owners");
                        ISubjectInstanceBean primeSubjectInstanceBean = getPrimeSubject(processInstanceBean);

                        // setting potential owners
                        commonApi.releaseSubjectInstance(
                                Long.valueOf(primeSubjectInstanceBean.getId()),
                                opsControlFirstStateDescrId);
                        instanceAdminApi.setPotentialOwners(
                                Long.valueOf(primeSubjectInstanceBean.getId()),
                                potentialOwners.toArray(new String[0]));

                        // retrieve prime BO to preset it's attributes
                        IView operationPlan = createPrimeBO(primeSubjectInstanceBean);
                        ISubjectInstanceBean newPrimeBean = getPrimeSubject(processInstanceBean);

                        log.debug("Editor has been removed. Potential owners["
                                + newPrimeBean.getPotentialOwners().length
                                + "/" + potentialOwners.size() + "]:");
                        for (String o : newPrimeBean.getPotentialOwners())
                            log.debug("-->" + o);

                        if (null != operationPlan) {
                            // ============================================================================================================
                            log.debug("retrieving opPlanCommonInfo");

                            // explicitly create commonInfo BO
                            Class<?> opPlanCommonInfoType = BOHelper.getType(
                                    operationPlan, COMMON_INFO);
                            log.debug("comInfoTypeClass:"
                                    + opPlanCommonInfoType);
                            Object opPlanCommonInfoImpl = opPlanCommonInfoType
                                    .newInstance();
                            BOHelper.setAttribute(operationPlan, COMMON_INFO,
                                    opPlanCommonInfoImpl);

                            Object commonInfo = BOHelper.getAttribute(
                                    operationPlan, COMMON_INFO);

                            // retrieve newly created commonInfo and set
                            // it'sattributes
                            if (null != commonInfo) {
                                log.debug("commonInfo retrieved. setting date attributes");

                                Date periodStart = DateTime.now()
                                        .minusMonths(1).toDate();
                                Date periodEnd = DateTime.now().toDate();

                                BOHelper.setAttribute(commonInfo,
                                        "periodStart", periodStart);
                                BOHelper.setAttribute(commonInfo, "periodEnd",
                                        periodEnd);
                                log.debug("date attribuites has been set");
                            } else {
                                log.debug("error retrieving commonInfo");
                            }

                            // ============================================================================================================
                            log.debug("retrieving opPlanPostamt");

                            // explicitly create postamt BO
                            Class<?> postamtType = BOHelper.getType(
                                    operationPlan, POSTAMT);
                            log.debug("postamtTypeCLass: " + postamtType);
                            IView postamtImpl = (IView) (postamtType
                                    .newInstance());
                            BOHelper.setAttribute(postamtImpl,
                                    "postamtPostamtIndex", postamtIndex);

                            List<IView> postamtViews = accessApi.getViews(
                                    primeSubjectInstanceBean, postamtImpl);

                            if (1 == postamtViews.size()) {
                                BOHelper.setAttribute(operationPlan, POSTAMT,
                                        postamtViews.get(0));
                                log.debug("postamt has been set");
                            } else {
                                log.debug("error retrieving postamt by index: "
                                        + postamtIndex
                                        + " - no postamts at all or more than one postamt exists");
                            }

                            // ============================================================================================================
                            log.debug("saving BO changes");
                            saveNewPrimeBO(primeSubjectInstanceBean,
                                    operationPlan);

                        } else {
                            log.debug("theres no opPlan BO for process: "
                                    + procName);
                        }
                    } else {
                        log.debug("theres no procInstanceBean for process: "
                                + procName);
                    }
                    log.debug("all done");
                    log.debug("\n");

                }
            }

        } catch (Exception e2) {
            e2.printStackTrace();
        }

        return null;
    }

    private int getDepartmentControllerGroupId(Connection connection,
            String query, String departmentControllerGroupId) throws Exception {
        log.debug("getRuspostDepartmentControllerGroupId begin");
        log.debug("executing: " + query);

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();

        valueWrappers.add(new ValueWrapper(departmentControllerGroupId,
                String.class));

        int id = (int) DbHelper.executeQuery(connection, query, valueWrappers,
                true, new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet rerSet)
                            throws Exception {

                        if (rerSet.next())
                            return rerSet
                                    .getInt(DEPARTMENT_CONTROLLER_GROUP_ID);

                        return -1;
                    }
                });

        log.debug("getRuspostDepartmentControllerGroupId end");
        return id;
    }

    private List<Pair> getUfpsIndexNamePairs(Connection connection, String query)
            throws Exception {
        log.debug("getUfpsIndexNamePairs begin");
        log.debug("executing: " + query);

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();

        List<Pair> ufpsIndexNamePairs = (List<Pair>) DbHelper.executeQuery(
                connection, query, valueWrappers, true,
                new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet result)
                            throws Exception {
                        List<Pair> ufpsIndexNamePairs = new ArrayList<Pair>();

                        while (result.next()) {

                            int ufpsIndex = result.getInt(UFPS_INDEX);
                            String ufpsName = result.getString(UFPS_NAME);
                            log.error("  ufps: " + ufpsIndex + " / " + ufpsName);

                            ufpsIndexNamePairs.add(new Pair(String
                                    .valueOf(ufpsIndex), ufpsName));
                        }

                        return ufpsIndexNamePairs;
                    }
                });

        log.debug("getUfpsIndexNamePairs end");
        return ufpsIndexNamePairs;
    }

    private List<Pair> getPostamtIndexNamePairs(Connection connection,
            String query, String ufpsIndex) throws Exception {
        log.debug("getPostamtIndexNamePairs begin");
        log.debug("executing: " + query);
        int ufpsIndexInt = Integer.valueOf(ufpsIndex);

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();

        valueWrappers.add(new ValueWrapper(ufpsIndexInt, Integer.class));

        List<Pair> postamtIndexNamePairs = (List<Pair>) DbHelper.executeQuery(
                connection, query, valueWrappers, true,
                new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet result)
                            throws Exception {
                        List<Pair> postamtIndexNamePairs = new ArrayList<Pair>();

                        while (result.next()) {

                            int postamtIndex = result.getInt(POSTAMT_INDEX);
                            String postamtName = result.getString(POSTAMT_NAME);
                            log.debug("  postamt: " + postamtIndex + " / "
                                    + postamtName);

                            postamtIndexNamePairs.add(new Pair(String
                                    .valueOf(postamtIndex), postamtName));
                        }

                        return postamtIndexNamePairs;
                    }
                });

        log.debug("getPostamtIndexNamePairs end");
        return postamtIndexNamePairs;
    }

    private List<String> getPotentialOwners(Connection connection,
            String query, int ufpsIndex, int departmentControllerGroupId)
            throws Exception {
        log.debug("getPotentialOwners begin");
        log.debug("executing: " + query);

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();

        valueWrappers.add(new ValueWrapper(ufpsIndex, Integer.class));
        valueWrappers.add(new ValueWrapper(departmentControllerGroupId,
                Integer.class));

        List<String> potOwnersIds = (List<String>) DbHelper.executeQuery(
                connection, query, valueWrappers, true,
                new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet result)
                            throws Exception {
                        List<String> potOwnersIds = new ArrayList<String>();

                        while (result.next()) {

                            int ufpsIndex = result.getInt(UFPS_INDEX);
                            int userId = result.getInt(UM_USER);
                            int departmentControllerGroupId = result
                                    .getInt(DEPARTMENT_CONTROLLER_GROUP_ID);

                            log.debug("  user: " + ufpsIndex + " / [" + userId
                                    + "] / " + departmentControllerGroupId);

                            potOwnersIds.add(String.valueOf(userId));
                        }

                        return potOwnersIds;
                    }
                });

        log.debug("getPotentialOwners end");
        return potOwnersIds;
    }

    private ISubjectInstanceBean getPrimeSubject(
            IProcessInstanceBean processInstanceBean) throws Exception {
        ICommonApi commonApi = ApiUtils.lookupCommonApi();
        String subjectInstanceId = processInstanceBean.getSubjectInstances()[0];
        ISubjectInstanceBean subjectInstanceBean = commonApi
                .getSubjectInstanceBean(subjectInstanceId);
        return subjectInstanceBean;
    }

    private IView createPrimeBO(ISubjectInstanceBean subjectInstanceBean)
            throws Exception {
        IBo20AccessApi bo20AccessApi = ApiUtils.lookupBo20AccessApi();
        Set<IStorageInstanceBean> storages = bo20AccessApi.getStorages(
                subjectInstanceBean, null);
        IStorageInstanceBean storage = storages.iterator().next();
        IView iView = bo20AccessApi.createIViewOnStorage(subjectInstanceBean,
                storage);
        return iView;
    }

    private void saveNewPrimeBO(ISubjectInstanceBean subjectInstanceBean,
            IView iView) throws Exception {
        IBo20AccessApi bo20AccessApi = ApiUtils.lookupBo20AccessApi();
        Set<IStorageInstanceBean> storages = bo20AccessApi.getStorages(
                subjectInstanceBean, null);
        IStorageInstanceBean storage = storages.iterator().next();
        bo20AccessApi
                .saveNewIViewInStorage(subjectInstanceBean, iView, storage);
    }

    private Map<String, String> processVersions = new HashMap<String, String>();

    private IProcessInstanceBean createProcessInstance(
            String processDescriptionId, String name, String userId,
            int priority) throws Exception {
        ICommonApi commonApi = ApiUtils.lookupCommonApi();

        String processVersionId = getLatestProcessVersionId(processDescriptionId);

        InitProcessInstanceData initProcessInstanceData = new InitProcessInstanceData(
                processVersionId,
                com.jcom1.api.constants.Constants.Process.Mode.PRODUCTIVE);

        initProcessInstanceData.setName(name);
        initProcessInstanceData.setCreatorId(userId);
        initProcessInstanceData.setPriority(priority);

        String processInstanceId = commonApi
                .createProcessInstance(initProcessInstanceData);

        IProcessInstanceBean processInstance = commonApi
                .getProcessInstanceBean(processInstanceId);

        return processInstance;
    }

    private String getLatestProcessVersionIdFromDB(Connection connection,
            String query, String processDescriptionId) throws Exception {
        log.debug("getLatestProcessVersionIdFromDB begin");
        log.debug("executing: " + query);
        final String LATEST_VER_ID = "LATEST_VER_ID";

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();

        valueWrappers.add(new ValueWrapper(processDescriptionId, String.class));

        String lastestVerId = (String) DbHelper.executeQuery(connection, query,
                valueWrappers, true, new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet rerSet)
                            throws Exception {

                        if (rerSet.next())
                            return rerSet.getString(LATEST_VER_ID);

                        return String.valueOf(-1);
                    }
                });

        return lastestVerId;
    }

    private String getLatestProcessVersionId(String processDescriptionId)
            throws Exception {
        log.debug("getLatestProcessVersionId begin");
        String processVersion = processVersions.get(processDescriptionId);

        if (processVersion == null) {

            String latestProcVersionQuery = "SELECT ID AS LATEST_VER_ID "
                    + "FROM RT_PROCDESC " + "WHERE UPLOADDATE = ( "
                    + "     SELECT MAX(UPLOADDATE) " + "        FROM RT_PROCDESC "
                    + "     WHERE JPASS_ID = ?)";

            try (final Connection connection = DbHelper
                    .getConnection(DbHelper.PROP_DB_INT);) {

                processVersion = getLatestProcessVersionIdFromDB(connection,
                        latestProcVersionQuery, processDescriptionId);
                processVersions.put(processDescriptionId, processVersion);

            } catch (Exception e2) {
                log.debug("error while retrieving latest process version for process: "
                        + processDescriptionId);
            }
        }
        log.debug("latest ver: " + processVersion);
        log.debug("getLatestProcessVersionId end");
        return processVersion;
    }

}