package com.seeyon.apps.src_flowIntegration.dao;

import com.seeyon.apps.src_flowIntegration.util.SQLParamUtil;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.util.JDBCAgent;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SrcFlowDao{

    private String getFormIdByTemplateCode = "select form_appid from ctp_template where TEMPLETE_NUMBER = ?";
    private String getViewIdByFormId = "select view_id from cap_form_view_info where form_id = ? and view_sore = 1 and view_type = 'seeyonform' and delete_flag = 0";
    private String getAuthIdByFormId = "select resource_id from cap_form_auth_info where form_id = ? and type = 'add' and deleted = 0 and default_auth = 1 ";
    private String getFieldInfoByFormId = "select field_info from cap_form_definition where id = ?";
    private String uniqueDataValidation = "select #{fieldName} from #{tableName} where #{fieldName} = ?";
    private String getFormDataByParam = "select #{fieldName} from #{tableName} where 1=1 #{whereString} ";
    private String getThirdpartyParamByThirdpartyCode = "select param_value from thirdparty_register where app_code = ?";
    private String getColSummaryById = "select id,state,subject,start_member_id,process_id,templete_id,form_recordid,formid,form_appid from col_summary where id = ?";
    private String getProcessLogsByProcessId = "select id,process_id,action_id,action_user_id,action_time,action_desc from ctp_process_log where process_id = ?";
    private String getColSummaryByFormRecordid = "select id,state,subject,start_member_id,process_id,templete_id,form_recordid,formid,form_appid from col_summary where form_recordid = ?";
    private String getCtpAffairBySummaryId = "select id,member_id,state,sub_state from ctp_affair where object_id = ?";
    private String getFieldInfoByFormName = "select field_info from CAP_FORM_DEFINITION where field_info like ?";

    private String getMetadataNameByLabel = "select name from ctp_metadata_column where label = ? and is_enable = 1";


    public String getFormIdByTemplateCode(String templateCode){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getFormIdByTemplateCode);
            List<Object> p = new ArrayList<Object>();
            p.add(templateCode);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("form_appid");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String getViewIdByFormId(String formId){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getViewIdByFormId);
            List<Object> p = new ArrayList<Object>();
            p.add(formId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("view_id");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String getAuthIdByFormId(String formId){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getAuthIdByFormId);
            List<Object> p = new ArrayList<Object>();
            p.add(formId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("resource_id");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String getFieldInfoByFormId(String formId){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getFieldInfoByFormId);
            List<Object> p = new ArrayList<Object>();
            p.add(formId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("field_info");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String uniqueDataValidation (String tableName,String fieldName,String flowId){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(uniqueDataValidation);
            String replacedStr = sql.toString().replace("#{tableName}", tableName).replace("#{fieldName}", fieldName);
            sql = new StringBuilder(replacedStr);
            List<Object> p = new ArrayList<Object>();
            p.add(flowId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get(fieldName);
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public List<Map<String,Object>> getFormDataByParam (String tableName,String[] fieldNames,Map<String,String> params){
        List<Map<String,Object>> res = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getFormDataByParam);
            String fieldName = SQLParamUtil.joinWithCommaFilterEmpty(fieldNames);
            String whereString = SQLParamUtil.mapToAndCondition(params);
            String replacedStr = sql.toString().replace("#{tableName}", tableName).replace("#{fieldName}", fieldName).replace("#{whereString}",whereString);
            sql = new StringBuilder(replacedStr);
            System.out.println(sql.toString());
            List<Object> p = new ArrayList<Object>();
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                for(int i = 0 ; i < list.size() ; i++){
                    Map<String,Object> r = list.get(i);
                    res.add(r);
                }
                return res;
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String getThirdpartyParamByThirdpartyCode(String thirdpartyCode){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getThirdpartyParamByThirdpartyCode);
            List<Object> p = new ArrayList<Object>();
            p.add(thirdpartyCode);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("param_value");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String getMetadataNameByLabel(String labelName){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getMetadataNameByLabel);
            List<Object> p = new ArrayList<Object>();
            p.add(labelName);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("name");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    id,state,subject,start_member_id,process_id,templete_id,form_recordid,formid,form_appid
    public Map<String,Object> getColSummaryById(String summaryId){
        Map<String,Object> res = new HashMap<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getColSummaryById);
            List<Object> p = new ArrayList<Object>();
            p.add(summaryId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                res = list.get(0);
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    id,process_id,action_user_id,action_time,action_desc
    public List<Map<String,String>> getProcessLogsByProcessId(String processId){
        List<Map<String,String>> res = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getProcessLogsByProcessId);
            List<Object> p = new ArrayList<Object>();
            p.add(processId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                for(int i = 0 ; i< list.size();i++){
                    Map<String,Object> data = list.get(i);
                    Map<String,String> map = new HashMap<>();
                    map.put("id",data.get("id")!=null?data.get("id").toString():"");
                    map.put("processId",data.get("process_id")!=null?data.get("process_id").toString():"");
                    map.put("actionId",data.get("action_id")!=null?data.get("action_id").toString():"");
                    map.put("actionUserId",data.get("action_user_id")!=null?data.get("action_user_id").toString():"");
                    map.put("actionTime",data.get("action_time")!=null?data.get("action_time").toString():"");
                    map.put("actionDesc",data.get("action_desc")!=null?data.get("action_desc").toString():"");
                    res.add(map);
                }
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public Map<String,String> getColSummaryByFormRecordid(String formRecordid){
        Map<String,String> res = new HashMap<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getColSummaryByFormRecordid);
            List<Object> p = new ArrayList<Object>();
            p.add(formRecordid);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                res = list.get(0);
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public List<Map<String,String>> getCtpAffairBySummaryId(String summaryId){
        List<Map<String,String>> res = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getCtpAffairBySummaryId);
            List<Object> p = new ArrayList<Object>();
            p.add(summaryId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                for(int i = 0 ; i< list.size();i++){
                    Map<String,Object> data = list.get(i);
                    Map<String,String> map = new HashMap<>();
                    map.put("id",data.get("id")!=null?data.get("id").toString():"");
                    map.put("memberId",data.get("member_id")!=null?data.get("member_id").toString():"");
                    map.put("state",data.get("state")!=null?data.get("state").toString():"");
                    map.put("subState",data.get("sub_state")!=null?data.get("sub_state").toString():"");
                    res.add(map);
                }
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

    public String getFieldInfoByFormName(String tableName){
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getFieldInfoByFormName);
            List<Object> p = new ArrayList<Object>();
            p.add("%"+tableName+"%");
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String,Object> map = list.get(0);
                Object obj = map.get("field_info");
                res = obj!=null?obj.toString():"";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }


}
