package cn.com.countrygarden.bi.main.dao.businessCenterSubmission.sqlprovider;

import cn.com.countrygarden.bi.main.dto.businessCenterSubmission.SumSheetDTO;

/**
 * <pre>
 *
 * </pre>
 *
 * @author wxy
 * @version 1.00.00
 * <p>
 * <p>
 * <pre>
 * 修改记录
 *    修改后版本:     修改人：  修改日期:     修改内容:
 * </pre>
 */
public class BusinessCenterSubmissionSqlProvider {

    public String getUnfinishedCause() {
        String sql = "SELECT\n" +
                "\tid ,\n" +
                "\tunfinished_cause_name AS causeName\n" +
                "FROM\n" +
                "\tbcs_ask_unfinished_cause";
        return sql;
    }

    public String getSumSheetList(String organCode, String commId, String id, String branchName,
                                  String isPrd, String isDouble, String unfinishedCause, String isDoubleSixMonth,
                                  String startTime, String endTime) {
        String sql = "SELECT\n" +
                "\tA.id,\n" +
                "\tA.organ_code AS organCode,\n" +
                "\tA.organ_name AS organName,\n" +
                "\tA.comm_Id AS commId,\n" +
                "\tA.comm_name AS commName,A.branch_name AS branchName,\n" +
                "\tA.isprd,\n" +
                "\tA.isdouble,\n" +
                "\tA.unfinished_cause AS unfinishedCause,\n" +
                "\tA.unfinished_cause_text AS unfinishedCauseText,\n" +
                "\tA.isdouble_six_month AS isDoubleSixMonth,\n" +
                "\tA.isdouble_six_month_flase_cause AS isDoubleSixMonthFlaseCause,\n" +
                "\tA.ask_for_num_agree_2015 AS askForNumAgree2015,\n" +
                "\tA.ask_for_num_disagree_2015 AS askForNumDisagree2015,\n" +
                "\tA.ask_for_num_agree_2016 AS askForNumAgree2016,\n" +
                "\tA.ask_for_num_disagree_2016 AS askForNumDisagree2016,\n" +
                "\tA.ask_for_num_agree_2017 AS askForNumAgree2017,\n" +
                "\tA.ask_for_num_disagree_2017 AS askForNumDisagree2017,\n" +
                "\tA.ask_for_num_agree_2018 AS askForNumAgree2018,\n" +
                "\tA.ask_for_num_disagree_2018 AS askForNumDisagree2018,\n" +
                "\tA.ask_for_num_agree_2019 AS askForNumAgree2019,\n" +
                "\tA.ask_for_num_disagree_2019 AS askForNumDisagree2019,\n" +
                "\tA.ask_for_num_agree_2020 AS askForNumAgree2020,\n" +
                "\tA.ask_for_num_disagree_2020 AS askForNumDisagree2020,\n" +
                "\tA.ask_for_num_agree_2021 AS askForNumAgree2021,\n" +
                "\tA.ask_for_num_disagree_2021 AS askForNumDisagree2021,\n" +
                "\tA.ask_for_num_agree_2022 AS askForNumAgree2022,\n" +
                "\tA.ask_for_num_disagree_2022 AS askForNumDisagree2022,\n" +
                "\tA.ask_date_case AS askDateCase,\n" +
                "\tA.finish_double_date AS finishDoubleDate,\n" +
                "\tA.remark\n" +
                "FROM\n" +
                "\tbcs_ask_sumsheet A\n" +
                "WHERE\n" +
                "\tA.Isdelete = 0";
        if(id != null && !id.equals("")){
            sql += " AND A.id = '" + id + "'";
        }
        if (organCode != null && !organCode.equals("") && !organCode.equals("01")) {
            sql += " AND A.organ_code = '" + organCode + "'";
        }
        if (commId != null && !commId.equals("") && !commId.equals("0")) {
            sql += " AND A.comm_Id = '" + commId + "'";
        }
        if (branchName != null && branchName.equals("")) {
            sql += " AND A.branch_name = '" + branchName + "'";
        }
        if (isPrd != null && !isPrd.equals("")) {
            sql += " AND A.isprd = '" + isPrd + "'";
        }
        if (isDouble != null && !isDouble.equals("")) {
            sql += " AND A.isdouble = '" + isDouble + "'";
        }
        if (unfinishedCause != null && !unfinishedCause.equals("")) {
            sql += " AND A.unfinished_cause = '" + unfinishedCause + "'";
        }
        if (isDoubleSixMonth != null && !isDoubleSixMonth.equals("")) {
            sql += " AND A.isdouble_six_month = '" + isDoubleSixMonth + "'";
        }
        if (startTime != null && !startTime.equals("")) {
            sql += " AND A.finish_double_date >= '" + startTime + "'";
        }
        if (endTime != null && !endTime.equals("")) {
            sql += " AND A.finish_double_date <= '" + endTime + "'";
        }
        return sql;
    }

    public String addSumSheet(SumSheetDTO dto,String userCode){
        String sql = "INSERT INTO bcs_ask_sumsheet (\n" +
                "\torgan_code,\n" +
                "\torgan_name,\n" +
                "\tcomm_id,\n" +
                "\tcomm_name,\n" +
                "\tbranch_name,\n" +
                "\tisprd,\n" +
                "\tisdouble,\n" +
                "\tunfinished_cause,\n" +
                "\tunfinished_cause_text,\n" +
                "\tisdouble_six_month,\n" +
                "\tisdouble_six_month_flase_cause,\n" +
                "\task_for_num_agree_2015,\n" +
                "\task_for_num_disagree_2015,\n" +
                "\task_for_num_agree_2016,\n" +
                "\task_for_num_disagree_2016,\n" +
                "\task_for_num_agree_2017,\n" +
                "\task_for_num_disagree_2017,\n" +
                "\task_for_num_agree_2018,\n" +
                "\task_for_num_disagree_2018,\n" +
                "\task_for_num_agree_2019,\n" +
                "\task_for_num_disagree_2019,\n" +
                "\task_for_num_agree_2020,\n" +
                "\task_for_num_disagree_2020,\n" +
                "\task_for_num_agree_2021,\n" +
                "\task_for_num_disagree_2021,\n" +
                "\task_for_num_agree_2022,\n" +
                "\task_for_num_disagree_2022,\n" +
                "\task_date_case,\n" +
                "\tfinish_double_date,\n" +
                "\tremark,\n" +
                "\tIsdelete,\n" +
                "\tcreate_by,\n" +
                "\tcreate_time\n" +
                ")\n" +
                "VALUES\n" +
                "\t(\n" +
                "\t\t'"+dto.getOrganCode()+"',\n" +
                "\t\t'"+dto.getOrganName()+"',\n" +
                "\t\t"+dto.getCommId()+",\n" +
                "\t\t'"+dto.getCommName()+"',\n" +
                "\t\t'"+dto.getBranchName()+"',\n" +
                "\t\t"+dto.getIsPrd()+",\n" +
                "\t\t'"+dto.getIsDouble()+"',\n" +
                "\t\t"+dto.getUnfinishedCause()+",\n" +
                "\t\t'"+dto.getUnfinishedCauseText()+"',\n" +
                "\t\t"+dto.getIsDoubleSixMonth()+",\n" +
                "\t\t'"+dto.getIsDoubleSixMonthFlaseCause()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2015()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2015()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2016()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2016()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2017()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2017()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2018()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2018()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2019()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2019()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2020()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2020()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2021()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2021()+"',\n" +
                "\t\t'"+dto.getAskForNumAgree2022()+"',\n" +
                "\t\t'"+dto.getAskForNumDisagree2022()+"',\n" +
                "\t\t'"+dto.getAskDateCase()+"',\n" +
                "\t\t'"+dto.getFinishDoubleDate()+"',\n" +
                "\t\t'"+dto.getRemark()+"',\n" +
                "\t\t0,\n" +
                "\t\t'"+userCode+"',\n" +
                "\t\tNOW()\n" +
                "\t)";
        return sql;
    }

    public String updateSumSheet(SumSheetDTO dto,String userCode){
        String sql = "UPDATE bcs_ask_sumsheet\n" +
                "SET update_by = '"+userCode+"',\n" +
                "\t\tupdate_time = NOW()\n" ;
        if (dto.getOrganCode() != null && !dto.getOrganCode().equals("")){
            sql += " ,organ_code = '"+dto.getOrganCode()+"'";
        }
        if (dto.getOrganName() != null && !dto.getOrganName().equals("")){
            sql += " ,organ_name = '"+dto.getOrganName()+"'";
        }
        if (dto.getCommId() != null && !dto.getCommId().equals("")){
            sql += " ,comm_Id = '"+dto.getCommId()+"'";
        }
        if (dto.getCommName() != null && !dto.getCommName().equals("")){
            sql += " ,comm_name = '"+dto.getCommName()+"'";
        }
        if (dto.getBranchName() != null && !dto.getBranchName().equals("")){
            sql += " ,branch_name = '"+dto.getBranchName()+"'";
        }
        if (dto.getIsPrd() != null && !dto.getIsPrd().equals("")){
            sql += " ,isprd = '"+dto.getIsPrd()+"'";
        }
        if (dto.getIsDouble() != null && !dto.getIsDouble().equals("")){
            sql += " ,isdouble = '"+dto.getIsDouble()+"'";
        }
        if (dto.getUnfinishedCause() != null && !dto.getUnfinishedCause().equals("")){
            sql += " ,unfinished_cause = '"+dto.getUnfinishedCause()+"'";
        }
        if (dto.getUnfinishedCauseText() != null && !dto.getUnfinishedCauseText().equals("")){
            sql += " ,unfinished_cause_text = '"+dto.getUnfinishedCauseText()+"'";
        }
        if (dto.getIsDoubleSixMonth() != null && !dto.getIsDoubleSixMonth().equals("")){
            sql += " ,isdouble_six_month = '"+dto.getIsDoubleSixMonth()+"'";
        }
        if (dto.getIsDoubleSixMonthFlaseCause() != null && !dto.getIsDoubleSixMonthFlaseCause().equals("")){
            sql += " ,isdouble_six_month_flase_cause = '"+dto.getIsDoubleSixMonthFlaseCause()+"'";
        }
        if (dto.getAskForNumAgree2015() != null && !dto.getAskForNumAgree2015().equals("")){
            sql += " ,ask_for_num_agree_2015 = '"+dto.getAskForNumAgree2015()+"'";
        }
        if (dto.getAskForNumDisagree2015() != null && !dto.getAskForNumDisagree2015().equals("")){
            sql += " ,ask_for_num_disagree_2015 = '"+dto.getAskForNumDisagree2015()+"'";
        }
        if (dto.getAskForNumAgree2016() != null && !dto.getAskForNumAgree2016().equals("")){
            sql += " ,ask_for_num_agree_2016 = '"+dto.getAskForNumAgree2016()+"'";
        }
        if (dto.getAskForNumDisagree2016() != null && !dto.getAskForNumDisagree2016().equals("")){
            sql += " ,ask_for_num_disagree_2016 = '"+dto.getAskForNumDisagree2016()+"'";
        }
        if (dto.getAskForNumAgree2017() != null && !dto.getAskForNumAgree2017().equals("")){
            sql += " ,ask_for_num_agree_2017 = '"+dto.getAskForNumAgree2017()+"'";
        }
        if (dto.getAskForNumDisagree2017() != null && !dto.getAskForNumDisagree2017().equals("")){
            sql += " ,ask_for_num_disagree_2017 = '"+dto.getAskForNumDisagree2017()+"'";
        }
        if (dto.getAskForNumAgree2018() != null && !dto.getAskForNumAgree2018().equals("")){
            sql += " ,ask_for_num_agree_2018 = "+dto.getAskForNumAgree2018()+"";
        }
        if (dto.getAskForNumDisagree2018() != null && !dto.getAskForNumDisagree2018().equals("")){
            sql += " ,ask_for_num_disagree_2018 = "+dto.getAskForNumDisagree2018()+"";
        }
        if (dto.getAskForNumAgree2019() != null && !dto.getAskForNumAgree2019().equals("")){
            sql += " ,ask_for_num_agree_2019 = "+dto.getAskForNumAgree2019()+"";
        }
        if (dto.getAskForNumDisagree2019() != null && !dto.getAskForNumDisagree2019().equals("")){
            sql += " ,ask_for_num_disagree_2019 = "+dto.getAskForNumDisagree2019()+"";
        }
        if (dto.getAskForNumAgree2020() != null && !dto.getAskForNumAgree2020().equals("")){
            sql += " ,ask_for_num_agree_2020 = "+dto.getAskForNumAgree2020()+"";
        }
        if (dto.getAskForNumDisagree2020() != null && !dto.getAskForNumDisagree2020().equals("")){
            sql += " ,ask_for_num_disagree_2020 = "+dto.getAskForNumDisagree2020()+"";
        }
        if (dto.getAskForNumAgree2021() != null && !dto.getAskForNumAgree2021().equals("")){
            sql += " ,ask_for_num_agree_2021 = "+dto.getAskForNumAgree2021()+"";
        }
        if (dto.getAskForNumDisagree2021() != null && !dto.getAskForNumDisagree2021().equals("")){
            sql += " ,ask_for_num_disagree_2021 = "+dto.getAskForNumDisagree2021()+"";
        }
        if (dto.getAskForNumAgree2022() != null && !dto.getAskForNumAgree2022().equals("")){
            sql += " ,ask_for_num_agree_2022 = "+dto.getAskForNumAgree2022()+"";
        }
        if (dto.getAskForNumDisagree2022() != null && !dto.getAskForNumDisagree2022().equals("")){
            sql += " ,ask_for_num_disagree_2022 = "+dto.getAskForNumDisagree2022()+"";
        }
        if (dto.getAskDateCase() != null && !dto.getAskDateCase().equals("")){
            sql += " ,ask_date_case = '"+dto.getAskDateCase()+"'";
        }
        if (dto.getFinishDoubleDate() != null && !dto.getFinishDoubleDate().equals("")){
            sql += " ,finish_double_date = '"+dto.getFinishDoubleDate()+"'";
        }
        if (dto.getRemark() != null && !dto.getRemark().equals("")){
            sql += " ,remark = '"+dto.getRemark()+"'";
        }
        sql +=        " WHERE\n" +
                "\tid = '"+dto.getId()+"'";
        return sql;
    }
    public String deleteSumSheet(String id,String userCode){
        String sql = "UPDATE bcs_ask_sumsheet\n" +
                "SET Isdelete = 1,\n" +
                " update_by = '"+userCode+"',\n" +
                " update_time = NOW()\n" +
                "WHERE\n" +
                "\tid = '"+id+"'";
        return sql;
    }

    public String getUserRoleList(String userCode){
        String sql = "SELECT\n" +
                "\trole_code\n" +
                "FROM\n" +
                "\tuser_roles\n" +
                "WHERE\n" +
                "\tuser_code = '"+userCode+"' ORDER BY id DESC";
        return sql;
    }
}
