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 static PowerMettersDetailProvider instance;
        public PowerMettersDetailProvider() : base("gov")
        {
        }

        public static PowerMettersDetailProvider Instance
        {
            get
            {
                CreateInstance(ref instance);
                return instance;
            }
        }

        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 `gov`.`powermettersdetail` "
                        + "(`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`, "
                        + "`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`) "
                        + "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, "
                        + "@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); ";
                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 " +
                            "`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 " +
                            " WHERE `QL_INNER_CODE` = @QL_INNER_CODE;";
                }
                else
                {
                    Console.WriteLine("插入：" + p.QL_INNER_CODE);
                    sql = "INSERT INTO `gov`.`powermettersdetail` "
                        + "(`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`, "
                        + "`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`) "
                        + "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, "
                        + "@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); ";
                }
                connection.Execute(sql, p);
            }
        }
    }
}