Winsoft.GOV/Winsoft.GOV.Framework/Provider/PowerMettersDetailProvider.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Winsoft.GOV.Framework.Model;
using Dapper;

namespace Winsoft.GOV.Framework.Provider
{
    public class PowerMettersDetailProvider : BaseProvider<PowerMattersDetail>
    {
        private string cols = "`ROWGUID`, "
                        + "`UPDATE_DATE`, "
                        + "`UPDATE_TYPE`, "
                        + "`QL_KIND`, "
                        + "`QL_MAINITEM_ID`, "
                        + "`QL_SUBITEM_ID`, "
                        + "`SHIQUANCJ`, "
                        + "`BELONGXIAQUCODE`, "
                        + "`QL_DEP_ORGCDOE`, "
                        + "`ITEMSOURCE`, "
                        + "`VERSION_NUMBER`, "
                        + "`VERSION_DATE`, "
                        + "`QL_EFFECT_TIME`, "
                        + "`QL_INNER_CODE`, "
                        + "`QL_NAME`, "
                        + "`QL_STATE`, "
                        + "`LAWBASIS`, "
                        + "`ANTICIPATE_DAY`, "
                        + "`ANTICIPATE_TYPE`, "
                        + "`PROMISE_DAY`, "
                        + "`APPLYERMIN_COUNT`, "
                        + "`IS_PILOT`, "
                        + "`QL_DEP`, "
                        + "`LEAD_DEPT`, "
                        + "`BJTYPE`, "
                        + "`BENJISPXZ`, "
                        + "`HANDLE_FREQUENCY`, "
                        + "`XINGZHENXDRXZ`, "
                        + "`APPLY_CONDITION`, "
                        + "`BANJIAN_FINISHFILES`, "
                        + "`LINK_TEL`, "
                        + "`SUPERVISE_TEL`, "
                        + "`WEBAPPLYURL`, "
                        + "`WEBCONSULTURL`, "
                        + "`CHARGE_FLAG`, "
                        + "`CHARGE_BASIS`, "
                        + "`RIGHTCLASS_QIYEZT`, "
                        + "`RIGHTCLASS_QIYEDX`, "
                        + "`RIGHTCLASS_GERENSX`, "
                        + "`RIGHTCLASS_GERENDX`, "
                        + "`IN_FLOW_INFO`, "
                        + "`MATERIAL_INFO`, "
                        + "`CHARGEITEM_INFO`, "
                        + "`QA_INFO`, "
                        + "`ACCEPT_ADDRESS_INFO`, "
                        + "`SYNC_SIGN`, "
                        + "`SYNC_DATE`, "
                        + "`SYNC_ERROR_DESC`, "
                        + "`OUORGCODE`, "
                        //+ "`ROWGUIDbak`, "
                        + "`OUGUID`, "
                        //+ "`sync_state`, "
                        + "`FaRenUrl`, "
                        + "`GeRenFlag`, "
                        + "`Is_TongJian`, "
                        + "`QL_Full_ID`, "
                        + "`ENTRUST`, "
                        + "`ENTRUSTDES`, "
                        + "`APPLYERMIN_COUNT_DESC`, "
                        + "`OUTypeCode`, "
                        + "`OUT_FLOW_DESC`, "
                        + "`BANJIAN_FINISHTYPE`, "
                        + "`IS_SpecialPro`, "
                        + "`State2`, "
                        + "`NoSuit_Apply`, "
                        + "`IS_UnifyDo`, "
                        + "`IS_UpUnify`, "
                        + "`QL_ATT`, "
                        + "`BakNote`, "
                        + "`WebApplyMode`, "
                        + "`ITEMSOURCETYPE`, "
                        + "`Is_SimplePunish`, "
                        + "`Is_LevyWaiver`, "
                        + "`FeeBasis`, "
                        + "`FACT_INFO`, "
                        + "`IS_CS`, "
                        + "`UnifyDoDes`, "
                        + "`IS_VLB`, "
                        //+ "`NoUnify_Do`, "
                        + "`QL_INNER_CODE_ITEM`, "
                        //+ "`IsHasOwnFlow`, "
                        //+ "`Is_TouZiP`, "
                        //+ "`bak1`, "
                        //+ "`bak2`, "
                        //+ "`bak3`, "
                        + "`Acp_institution`, "
                        + "`Dec_institution`, "
                        + "`Content_involve`, "
                        + "`Applicable_object`, "
                        + "`XINGZHENXDRXY`, "
                        + "`Count_limit`, "
                        + "`Count_note`, "
                        + "`Ban_requirement`, "
                        + "`SHIXIANGSCtype`, "
                        + "`SHIXIANGSCLX`, "
                        + "`Apply_type`, "
                        + "`Apply_type_tel`, "
                        + "`Apply_type_mail`, "
                        + "`Apply_type_fax`, "
                        + "`Handle_type`, "
                        + "`MbFarenAdd`, "
                        + "`MbGerenFlag`, "
                        + "`HangYeClassType`, "
                        + "`QL_Sub_Kind`, "
                        + "`IM_FLOW_url`, "
                        + "`OUT_FLOW_url`, "
                        + "`Service_mode`, "
                        + "`Service_day`, "
                        + "`DesTime`, "
                        + "`APPWEBAPPLYURL`, "
                        + "`APPAPPOINTMENTURL`, "
                        + "`APPOINTMENTURL`, "
                        + "`IS_WEBAPPOINTMENT`, "
                        + "`WEBAPPOINTMENTPERIOD`, "
                        + "`MAINCONTEXT`, "
                        + "`DO_DEPT`, "
                        + "`RELATEDGUID`, "
                        + "`RELATED`, "
                        + "`IS_EXPRESS`, "
                        + "`ISPYC`, "
                        + "`LBSX`, "
                        + "`Service_Sub_Kind`";

        private string values = "@ROWGUID, "
                        + "@UPDATE_DATE, "
                        + "@UPDATE_TYPE, "
                        + "@QL_KIND, "
                        + "@QL_MAINITEM_ID, "
                        + "@QL_SUBITEM_ID, "
                        + "@SHIQUANCJ, "
                        + "@BELONGXIAQUCODE, "
                        + "@QL_DEP_ORGCDOE, "
                        + "@ITEMSOURCE, "
                        + "@VERSION_NUMBER, "
                        + "@VERSION_DATE, "
                        + "@QL_EFFECT_TIME, "
                        + "@QL_INNER_CODE, "
                        + "@QL_NAME, "
                        + "@QL_STATE, "
                        + "@LAWBASIS, "
                        + "@ANTICIPATE_DAY, "
                        + "@ANTICIPATE_TYPE, "
                        + "@PROMISE_DAY, "
                        + "@APPLYERMIN_COUNT, "
                        + "@IS_PILOT, "
                        + "@QL_DEP, "
                        + "@LEAD_DEPT, "
                        + "@BJTYPE, "
                        + "@BENJISPXZ, "
                        + "@HANDLE_FREQUENCY, "
                        + "@XINGZHENXDRXZ, "
                        + "@APPLY_CONDITION, "
                        + "@BANJIAN_FINISHFILES, "
                        + "@LINK_TEL, "
                        + "@SUPERVISE_TEL, "
                        + "@WEBAPPLYURL, "
                        + "@WEBCONSULTURL, "
                        + "@CHARGE_FLAG, "
                        + "@CHARGE_BASIS, "
                        + "@RIGHTCLASS_QIYEZT, "
                        + "@RIGHTCLASS_QIYEDX, "
                        + "@RIGHTCLASS_GERENSX, "
                        + "@RIGHTCLASS_GERENDX, "
                        + "@IN_FLOW_INFO, "
                        + "@MATERIAL_INFO, "
                        + "@CHARGEITEM_INFO, "
                        + "@QA_INFO, "
                        + "@ACCEPT_ADDRESS_INFO, "
                        + "@SYNC_SIGN, "
                        + "@SYNC_DATE, "
                        + "@SYNC_ERROR_DESC, "
                        + "@OUORGCODE, "
                        //+@"`ROWGUIDbak, "
                        + "@OUGUID, "
                        //+@"`sync_state, "
                        + "@FaRenUrl, "
                        + "@GeRenFlag, "
                        + "@Is_TongJian, "
                        + "@QL_Full_ID, "
                        + "@ENTRUST, "
                        + "@ENTRUSTDES, "
                        + "@APPLYERMIN_COUNT_DESC, "
                        + "@OUTypeCode, "
                        + "@OUT_FLOW_DESC, "
                        + "@BANJIAN_FINISHTYPE, "
                        + "@IS_SpecialPro, "
                        + "@State2, "
                        + "@NoSuit_Apply, "
                        + "@IS_UnifyDo, "
                        + "@IS_UpUnify, "
                        + "@QL_ATT, "
                        + "@BakNote, "
                        + "@WebApplyMode, "
                        + "@ITEMSOURCETYPE, "
                        + "@Is_SimplePunish, "
                        + "@Is_LevyWaiver, "
                        + "@FeeBasis, "
                        + "@FACT_INFO, "
                        + "@IS_CS, "
                        + "@UnifyDoDes, "
                        + "@IS_VLB, "
                        //+@"`NoUnify_Do, "
                        + "@QL_INNER_CODE_ITEM, "
                        //+@"`IsHasOwnFlow, "
                        //+@"`Is_TouZiP, "
                        //+@"`bak1, "
                        //+@"`bak2, "
                        //+@"`bak3, "
                        + "@Acp_institution, "
                        + "@Dec_institution, "
                        + "@Content_involve, "
                        + "@Applicable_object, "
                        + "@XINGZHENXDRXY, "
                        + "@Count_limit, "
                        + "@Count_note, "
                        + "@Ban_requirement, "
                        + "@SHIXIANGSCtype, "
                        + "@SHIXIANGSCLX, "
                        + "@Apply_type, "
                        + "@Apply_type_tel, "
                        + "@Apply_type_mail, "
                        + "@Apply_type_fax, "
                        + "@Handle_type, "
                        + "@MbFarenAdd, "
                        + "@MbGerenFlag, "
                        + "@HangYeClassType, "
                        + "@QL_Sub_Kind, "
                        + "@IM_FLOW_url, "
                        + "@OUT_FLOW_url, "
                        + "@Service_mode, "
                        + "@Service_day, "
                        + "@DesTime, "
                        + "@APPWEBAPPLYURL, "
                        + "@APPAPPOINTMENTURL, "
                        + "@APPOINTMENTURL, "
                        + "@IS_WEBAPPOINTMENT, "
                        + "@WEBAPPOINTMENTPERIOD, "
                        + "@MAINCONTEXT, "
                        + "@DO_DEPT, "
                        + "@RELATEDGUID, "
                        + "@RELATED, "
                        + "@IS_EXPRESS, "
                        + "@ISPYC, "
                        + "@LBSX, "
                        + "@Service_Sub_Kind";

        private string sets = "`ROWGUID` = @ROWGUID, " +
                            "`UPDATE_DATE` = @UPDATE_DATE, " +
                            "`UPDATE_TYPE` = @UPDATE_TYPE, " +
                            "`QL_KIND` = @QL_KIND, " +
                            "`QL_MAINITEM_ID` = @QL_MAINITEM_ID, " +
                            "`QL_SUBITEM_ID` = @QL_SUBITEM_ID, " +
                            "`SHIQUANCJ` = @SHIQUANCJ, " +
                            "`BELONGXIAQUCODE` = @BELONGXIAQUCODE, " +
                            "`QL_DEP_ORGCDOE` = @QL_DEP_ORGCDOE, " +
                            "`ITEMSOURCE` = @ITEMSOURCE, " +
                            "`VERSION_NUMBER` = @VERSION_NUMBER, " +
                            "`VERSION_DATE` = @VERSION_DATE, " +
                            "`QL_EFFECT_TIME` = @QL_EFFECT_TIME, " +
                            "`QL_INNER_CODE` = @QL_INNER_CODE, " +
                            "`QL_NAME` = @QL_NAME, " +
                            "`QL_STATE` = @QL_STATE, " +
                            "`LAWBASIS` = @LAWBASIS, " +
                            "`ANTICIPATE_DAY` = @ANTICIPATE_DAY, " +
                            "`ANTICIPATE_TYPE` = @ANTICIPATE_TYPE, " +
                            "`PROMISE_DAY` = @PROMISE_DAY, " +
                            "`APPLYERMIN_COUNT` = @APPLYERMIN_COUNT, " +
                            "`IS_PILOT` = @IS_PILOT, " +
                            "`QL_DEP` = @QL_DEP, " +
                            "`LEAD_DEPT` = @LEAD_DEPT, " +
                            "`BJTYPE` = @BJTYPE, " +
                            "`BENJISPXZ` = @BENJISPXZ, " +
                            "`HANDLE_FREQUENCY` = @HANDLE_FREQUENCY, " +
                            "`XINGZHENXDRXZ` = @XINGZHENXDRXZ, " +
                            "`APPLY_CONDITION` = @APPLY_CONDITION, " +
                            "`BANJIAN_FINISHFILES` = @BANJIAN_FINISHFILES, " +
                            "`LINK_TEL` = @LINK_TEL, " +
                            "`SUPERVISE_TEL` = @SUPERVISE_TEL, " +
                            "`WEBAPPLYURL` = @WEBAPPLYURL, " +
                            "`WEBCONSULTURL` = @WEBCONSULTURL, " +
                            "`CHARGE_FLAG` = @CHARGE_FLAG, " +
                            "`CHARGE_BASIS` = @CHARGE_BASIS, " +
                            "`RIGHTCLASS_QIYEZT` = @RIGHTCLASS_QIYEZT, " +
                            "`RIGHTCLASS_QIYEDX` = @RIGHTCLASS_QIYEDX, " +
                            "`RIGHTCLASS_GERENSX` = @RIGHTCLASS_GERENSX, " +
                            "`RIGHTCLASS_GERENDX` = @RIGHTCLASS_GERENDX, " +
                            "`IN_FLOW_INFO` = @IN_FLOW_INFO, " +
                            "`MATERIAL_INFO` = @MATERIAL_INFO, " +
                            "`CHARGEITEM_INFO` = @CHARGEITEM_INFO, " +
                            "`QA_INFO` = @QA_INFO, " +
                            "`ACCEPT_ADDRESS_INFO` = @ACCEPT_ADDRESS_INFO, " +
                            "`SYNC_SIGN` = @SYNC_SIGN, " +
                            "`SYNC_DATE` = @SYNC_DATE, " +
                            "`SYNC_ERROR_DESC` = @SYNC_ERROR_DESC, " +
                            "`OUORGCODE` = @OUORGCODE, " +
                            "`OUGUID` = @OUGUID, " +
                            "`sync_state` = @sync_state, " +
                            "`FaRenUrl` = @FaRenUrl, " +
                            "`GeRenFlag` = @GeRenFlag, " +
                            "`Is_TongJian` = @Is_TongJian, " +
                            "`QL_Full_ID` = @QL_Full_ID, " +
                            "`ENTRUST` = @ENTRUST, " +
                            "`ENTRUSTDES` = @ENTRUSTDES, " +
                            "`APPLYERMIN_COUNT_DESC` = @APPLYERMIN_COUNT_DESC, " +
                            "`OUTypeCode` = @OUTypeCode, " +
                            "`OUT_FLOW_DESC` = @OUT_FLOW_DESC, " +
                            "`BANJIAN_FINISHTYPE` = @BANJIAN_FINISHTYPE, " +
                            "`IS_SpecialPro` = @IS_SpecialPro, " +
                            "`State2` = @State2, " +
                            "`NoSuit_Apply` = @NoSuit_Apply, " +
                            "`IS_UnifyDo` = @IS_UnifyDo, " +
                            "`IS_UpUnify` = @IS_UpUnify, " +
                            "`QL_ATT` = @QL_ATT, " +
                            "`BakNote` = @BakNote, " +
                            "`WebApplyMode` = @WebApplyMode, " +
                            "`ITEMSOURCETYPE` = @ITEMSOURCETYPE, " +
                            "`Is_SimplePunish` = @Is_SimplePunish, " +
                            "`Is_LevyWaiver` = @Is_LevyWaiver, " +
                            "`FeeBasis` = @FeeBasis, " +
                            "`FACT_INFO` = @FACT_INFO, " +
                            "`IS_CS` = @IS_CS, " +
                            "`UnifyDoDes` = @UnifyDoDes, " +
                            "`IS_VLB` = @IS_VLB, " +
                            //"`NoUnify_Do` = @NoUnify_Do, " +
                            "`QL_INNER_CODE_ITEM` = @QL_INNER_CODE_ITEM, " +
                            //"`IsHasOwnFlow` = @IsHasOwnFlow, " +
                            //"`Is_TouZiP` = @Is_TouZiP, " +
                            //"`bak1` = @bak1, " +
                            //"`bak2` = @bak2, " +
                            //"`bak3` = @bak3, " +
                            "`Acp_institution` = @Acp_institution, " +
                            "`Dec_institution` = @Dec_institution, " +
                            "`Content_involve` = @Content_involve, " +
                            "`Applicable_object` = @Applicable_object, " +
                            "`XINGZHENXDRXY` = @XINGZHENXDRXY, " +
                            "`Count_limit` = @Count_limit, " +
                            "`Count_note` = @Count_note, " +
                            "`Ban_requirement` = @Ban_requirement, " +
                            "`SHIXIANGSCtype` = @SHIXIANGSCtype, " +
                            "`SHIXIANGSCLX` = @SHIXIANGSCLX, " +
                            "`Apply_type` = @Apply_type, " +
                            "`Apply_type_tel` = @Apply_type_tel, " +
                            "`Apply_type_mail` = @Apply_type_mail, " +
                            "`Apply_type_fax` = @Apply_type_fax, " +
                            "`Handle_type` = @Handle_type, " +
                            "`MbFarenAdd` = @MbFarenAdd, " +
                            "`MbGerenFlag` = @MbGerenFlag, " +
                            "`HangYeClassType` = @HangYeClassType, " +
                            "`QL_Sub_Kind` = @QL_Sub_Kind, " +
                            "`IM_FLOW_url` = @IM_FLOW_url, " +
                            "`OUT_FLOW_url` = @OUT_FLOW_url, " +
                            "`Service_mode` = @Service_mode, " +
                            "`Service_day` = @Service_day, " +
                            "`DesTime` = @DesTime, " +
                            "`APPWEBAPPLYURL` = @APPWEBAPPLYURL, " +
                            "`APPAPPOINTMENTURL` = @APPAPPOINTMENTURL, " +
                            "`APPOINTMENTURL` = @APPOINTMENTURL, " +
                            "`IS_WEBAPPOINTMENT` = @IS_WEBAPPOINTMENT, " +
                            "`WEBAPPOINTMENTPERIOD` = @WEBAPPOINTMENTPERIOD, " +
                            "`MAINCONTEXT` = @MAINCONTEXT, " +
                            "`DO_DEPT` = @DO_DEPT, " +
                            "`RELATEDGUID` = @RELATEDGUID, " +
                            "`RELATED` = @RELATED, " +
                            "`IS_EXPRESS` = @IS_EXPRESS, " +
                            "`ISPYC` = @ISPYC, " +
                            "`LBSX` = @LBSX, " +
                            "`Service_Sub_Kind` = @Service_Sub_Kind ";
        public PowerMettersDetailProvider(County county) : base(county)
        {

        }

        public void DeleteByPowerMattersBase()
        {
            const string sql = "delete from powermettersdetail where QL_INNER_CODE not in (select ql_inner_code from powermattersbase);";
            using (IDbConnection connection = GetDbConnection())
            {
                connection.Execute(sql, 60000);
            }
        }

        public PowerMattersDetail FindByRowID(string rowID)
        {
            const string sql = "select * from powermettersdetail where QL_INNER_CODE=@rowID;";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.Query<PowerMattersDetail>(sql, new { rowID = rowID }).FirstOrDefault<PowerMattersDetail>();
            }
        }

        public DateTime FindLastUpdateDate()
        {
            const string sql = "select Max(UPDATE_DATE) from powermettersdetail;";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.QueryFirst<DateTime>(sql);
            }
        }

        public bool IsExist(string ql_inner_code)
        {
            using (IDbConnection connection = GetDbConnection())
            {
                string sql = "select count(*) from powermettersdetail where QL_INNER_CODE=@ql_inner_code;";
                return connection.QueryFirstOrDefault<int>(sql, new { ql_inner_code = ql_inner_code }) > 0;
            }
        }
        public void Insert(PowerMattersDetail p)
        {
            using (IDbConnection connection = GetDbConnection())
            {
                string sql = "INSERT INTO `powermettersdetail` "
                        + "("+ cols + ") "
                        + "VALUES "
                        + "("+values+"); ";
                connection.Execute(sql, p);
            }           
        }

        public void UpdateOrInsert(PowerMattersDetail p)
        {
            using (IDbConnection connection = GetDbConnection())
            {
                string sql = "select ROWGUID from powermettersdetail where QL_INNER_CODE=@QL_INNER_CODE;";
                IEnumerable<string> tmp = connection.Query<string>(sql, p);
                if (tmp != null && tmp.Count() > 0)
                {
                    sql = "UPDATE `powermettersdetail` SET " + sets +  " WHERE `QL_INNER_CODE` = @QL_INNER_CODE;";
                }
                else
                {
                    Console.WriteLine("插入：" + p.QL_INNER_CODE);
                    sql = "INSERT INTO `powermettersdetail` (" + cols + ") VALUES (" + values + "); ";
                }
                connection.Execute(sql, p);
            }
        }
    }
}