#sonic 819_fri_stable2 autorunRefinement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 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.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;

@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_5mLyIVMdEeaL5PGyiwnPkA";

    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 ID_AS_GIP_ID = "GIP_ID";
    private final String CONTROLLER_GIP = "CONTROLLER-GIP";

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

    private int controllerGipId;
    /**
     * @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.error("\n");

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

        String ufpsQuery = String.format(
                "SELECT DISTINCT %s, %s FROM POSTAMT_VIEW",

                UFPS_INDEX, UFPS_NAME);

        String postamtQueryTemplate = String.format(
                "SELECT %s, %s FROM POSTAMT_VIEW " +
                        "WHERE %s = %s",

                POSTAMT_INDEX, POSTAMT_NAME,
                UFPS_INDEX, "%s");

        String controllerGipIdQuery = String.format(
                "SELECT ID AS %s FROM UM_GROUP " +
                        "WHERE NAME = '%s'",

                ID_AS_GIP_ID, CONTROLLER_GIP);

        String potentialOwnerQueryTemplate = String.format(
                "SELECT A.UM_GROUP AS %s, A.%s, B.UM_GROUP AS %s "+
                        "FROM UM_GROUP_UM_USER AS A "+
                        "LEFT JOIN UM_GROUP_UM_USER AS B " +
                            "ON A.%s = B.%s "+
                        "WHERE A.UM_GROUP = %s AND B.UM_GROUP = %s",

                UM_GROUP_AS_INDEX, UM_USER, UM_GROUP_AS_ROLE,
                UM_USER, UM_USER,
                "%s", "%s");

        // //Caused by: java.lang.NoClassDefFoundError:
        // com/mchange/v2/c3p0/PooledDataSource

        Connection connection = null;
        try {// (final Connection connection =
                // DbHelper.getConnection(DbHelper.PROP_DB_INT);){
            connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PSW);
            log.error("connection has been set");

            // form query as SELECT TOP 1 ID AS ...
            controllerGipId = getControllerGipId(connection, controllerGipIdQuery);
            log.error(CONTROLLER_GIP + "'s ID: " + controllerGipId);

            Statement ufpses = connection.createStatement();
            //ufpses.setMaxRows(1);
            ResultSet ufpsResult = ufpses.executeQuery(ufpsQuery);
            log.error("executing: " + ufpsQuery);

            while (ufpsResult.next()) {

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

                // retrieve potential owners as:
                // users from current Ufps group that also belong to controller-gip group
                // form query as ... WHERE a.UM_GROUP = 249030 AND b.UM_GROUP = 13
                String potentialOwnersQuery = String.format(potentialOwnerQueryTemplate, ufpsIndex,controllerGipId);
                String[] potentialOwners = getPotentialOwners(connection,potentialOwnersQuery);

                // form query as ... WHERE UFPS_INDEX = 249030
                String postamtByUfpsQuery = String.format(postamtQueryTemplate,
                        ufpsIndex);

                ResultSet postamtByUfpsResult = connection.createStatement().executeQuery(postamtByUfpsQuery);
                log.error("executing: " + postamtByUfpsQuery);

                while (postamtByUfpsResult.next()) {

                    String postamtIndex = postamtByUfpsResult.getString(POSTAMT_INDEX);
                    String postamtName = postamtByUfpsResult.getString(POSTAMT_NAME);
                    log.error("     postamt: " + postamtIndex + " / "+ postamtName);

                    // ============================================================================================================
                    log.error("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.error("process '" + procName+ "' has been launched");
                    } catch (Exception e) {
                        log.error("error creating process instance with name: "+ procName);
                    }


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

                        // set potential owners as:
                        // users from current Ufps group that also belong tocontroller-gip group
                        commonApi.releaseSubjectInstance(Long.valueOf(primeSubjectInstanceBean.getId()), opsControlFirstStateDescrId);
                        instanceAdminApi.setPotentialOwners(Long.valueOf(primeSubjectInstanceBean.getId()), potentialOwners);

                        IView operationPlan = createPrimeBO(primeSubjectInstanceBean);
                        ISubjectInstanceBean newPrimeBean = getPrimeSubject(processInstanceBean);

                        log.error("Editors have been removed. New potential owners["
                                + newPrimeBean.getPotentialOwners().length + "/" + potentialOwners.length + "]:");
                        for (String o : newPrimeBean.getPotentialOwners()) log.error("-->" + o);

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

                            // explicitly create commonInfo BO
                            Class<?> opPlanCommonInfoType = BOHelper.getType(operationPlan, COMMON_INFO);
                            log.error("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.error("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.error("date attribuites has been set");
                            } else {
                                log.error("error retrieving commonInfo");
                            }
                            // ============================================================================================================
                            log.error("retrieving opPlanPostamt");

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

                            // search by set field (postamtIndx) among allobjects from same connector
                            // the first and only elemnt - our goal
                            List<IView> postamtViews = accessApi.getViews(primeSubjectInstanceBean, postamtImpl);

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

                            log.error("saving BO changes");
                            saveNewPrimeBO(primeSubjectInstanceBean,operationPlan);

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

        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (!connection.isClosed()) {
                    connection.close();
                }
            } catch (SQLException e) {
                log.error("error closing connection");
            }
        }

        return null;
    }

    private int getControllerGipId(Connection connection, String query) {
        int gipId = -1;

        try {
            log.error("executing: " + query);
            Statement controllerGipIdStmt = connection.createStatement();
            controllerGipIdStmt.setMaxRows(1);

            ResultSet cgiSet = controllerGipIdStmt.executeQuery(query);

            if (cgiSet.next())
                gipId = cgiSet.getInt(ID_AS_GIP_ID);

            cgiSet.close();

        } catch (SQLException e) {
            log.error("error fetching " + CONTROLLER_GIP + "'s ID");
        }

        return gipId;
    }

    private String[] getPotentialOwners(Connection connection, String query) {
        List<String> ownersList = new ArrayList<String>();

        try {
            ResultSet ownersResult = connection.createStatement().executeQuery(query);
            log.error("executing: " + query);

            while (ownersResult.next()) {

                int umGroupAsIndex = ownersResult.getInt(UM_GROUP_AS_INDEX);
                int umUserAsId = ownersResult.getInt(UM_USER);
                int umGroupAsRole = ownersResult.getInt(UM_GROUP_AS_ROLE);
                log.error("indx/id/role: " + umGroupAsIndex + " / "+ umUserAsId + " / " + umGroupAsRole);

                ownersList.add(String.valueOf(umUserAsId));
            }
            ownersResult.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ownersList.toArray(new String[0]);
    }

    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 getLatestProcessVersionId(String processDescriptionId)
            throws Exception {
        String processVersion = processVersions.get(processDescriptionId);

        if (processVersion == null) {

            final String LATEST_VER = "LATEST_VER";

            String latestProcVersionQuery = String.format(
                    "SELECT ID AS %s FROM RT_PROCDESC "+
                            "WHERE UPLOADDATE = (SELECT MAX(UPLOADDATE) "+
                                    "FROM RT_PROCDESC " +
                                    "WHERE JPASS_ID = '%s')",

                    LATEST_VER,
                    processDescriptionId);

            Connection connection = null;
            try {//(final Connection connection = DbHelper
                //  .getConnection(DbHelper.PROP_DB_INT);) {
                 connection = DriverManager.getConnection(DB_URL, DB_USER,DB_PSW);

                Statement stmt = connection.createStatement();
                stmt.setMaxRows(1);
                ResultSet procVersionSet = stmt.executeQuery(latestProcVersionQuery);

                if (procVersionSet.next()) {

                    processVersion = String.valueOf(procVersionSet.getInt(LATEST_VER));
                    processVersions.put(processDescriptionId, processVersion);

                    log.debug("processDescriptionId: " + processDescriptionId + " / processVersion: " + processVersion);
                }
                procVersionSet.close();

            } catch (Exception e2) {
                log.error("error while retrieving latest process version for process: " + processDescriptionId);
            } finally {
                try {
                    if (!connection.isClosed()) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    log.error("error closing connection");
                }
            }

            /*
             * try {
             *
             * ICommonApi commonApi = ApiUtils.lookupCommonApi(); // TODO change
             * to direct JDBC select // extremely time consuming method up to 3
             * minutes! IProcessDescriptionBean[] procDescBeans = commonApi
             * .getProcessVersions(processDescriptionId); // get latest version
             * long maxTimeStamp = 0; for (int c = procDescBeans.length; c-- >
             * 0;) { if (procDescBeans[c].isProductive()) { if
             * (procDescBeans[c].getUploadDate() > maxTimeStamp) { maxTimeStamp
             * = procDescBeans[c].getUploadDate(); processVersion =
             * procDescBeans[c].getVersion(); } } }
             * processVersions.put(processDescriptionId, processVersion);
             * log.debug("processDescriptionId: " + processDescriptionId +
             * " / processVersion: " + processVersion);
             *
             *
             * } catch (Exception e) { log.error(e); e.printStackTrace(); }
             */
        }
        log.error("latest ver: " + processVersion);
        return processVersion;
    }

}