User:Wikiacer/sandbox

新舊代號對照表 專營電支機構	新單位代號(3碼)	新單位代號(7碼)	舊單位代號(3碼)	舊單位代號(7碼) 歐付寶電子支付（股）公司	397	3970001	E01	E010001 橘子支行動支付(股)公司	395	3950001	E02	E020001 簡單行動支付(股)公司	398	3980001	E03	E030001 國際連(股)公司	394	3940001	E05	E050001 街口電子支付(股)公司	396	3960001	E06	E060001 一卡通票證(股)公司	391	3910001	E50 T03	E500001 T030001 遠鑫電子票證股份有限公司	393	3930001	E51 T04	E510001 T040001 悠遊卡股份有限公司	390	3900001	E52 T01	E520001 T010001 愛金卡股份有限公司	392	3920001	E53 T02	E530001 T020001

st60011005T 2021/5/25	11:40:54	808EP01	P55	0	Q	X101010107	0	xx_3id   	A	1	B	0 2021/5/25	11:40:54	822EP01	P40	0	Q	X101010107	0	xx_3id   	A	1	B	0 2021/5/25	11:40:54	822EP01	P44	0	Q	X101010107	0	xx_3id   	A	A	B	0

select ISSUE,START_DATE from kp300t where ISSUE='822' ISSUE	START_DATE 822	1100830 822	1100530 822	1090730

sql results: X101010107	822	822EP01	xx_3id   	1100525		1100425	1100825

original: (select idn_ban, main_code, bank_code, user_id, query_ymd, start_date, b_30, a_90 from ( select UCASE(b.IDN_BAN) as idn_ban, b.MAIN_CODE, b.BANK_CODE, max(b.user_id) as user_id ,b.query_ymd ,(					 		case when kp.start_date between								( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 )								and								( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 )							then 'match'							else kp.start_date							end						) as kp300t_mh ,kp.start_date ,( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 ) as b_30 ,( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 ) as a_90 from (					select 					 UCASE(sd.query_key) as idn_ban,					  sd.main_code, sd.bank_code, 					  right(digits(day(sd.date)),2) as day, 					  si.inq_purpose_1,					  sd.ep_id as user_id, 					  (to_char(sd.date,'yyyymmdd')-19110000) as query_ymd					  from JCIC.EP_RES_11007 sd						  ,XMLTABLE('$doc/inputs' PASSING XMLPARSE(DOCUMENT CAST('  '||replace(replace(sd.query_input, ' ',''),',','  ')||'   ' as CLOB)) as "doc" COLUMNS query_id VARCHAR(50) PATH 'item/value[1]', inq_purpose_1 VARCHAR(2) PATH 'item/value[2]', inq_purpose_2 VARCHAR(2) PATH 'item/value[3]', inq_purpose_3 VARCHAR(2) PATH 'item/value[4]' ) si					 where 1=1					  and sd.query_type in ('P40', 'P44')					   and si.inq_purpose_1 = 'A'					   and si.inq_purpose_2 = 'A'					   and ( SUBSTR(sd.query_key, 1, 1) Between '0' and '9'  OR							SUBSTR(sd.query_key, 1, 1) Between 'A' and 'z' )					union all					select 					    UCASE(ta.IDN_BAN) as idn_ban, 					    tb.main_code, ta.bank_code, right(digits(day(ta.query_date)),2) as day, ta.inq_purpose_1,					    ta.user_id					   , (to_char(ta.query_date, 'yyyymmdd')-19110000) as query_ymd					 from JCIC.ST60011005T ta INNER JOIN JCIC.AB530T TB						ON TA.BANK_CODE=TB.BANK_CODE 					  where 1=1					    and ta.query_item in ('P40', 'P44')					    and ta.inq_purpose_1 = 'A'					    and ta.inq_purpose_2 = 'A'					    AND ( SUBSTR(ta.IDN_BAN, 1, 1) Between '0' and '9'  OR							SUBSTR(ta.IDN_BAN, 1, 1) Between 'A' and 'z' ) 				)b left outer join JCIC.kp300t kp on 					kp.idn_ban = UCASE(b.idn_ban) and (						case 							when b.main_code = 'T01' then kp.issue in ('E52','T01')							when b.main_code = 'T02' then kp.issue in ('E53','T02')							when b.main_code = 'T03' then kp.issue in ('E50','T03')							when b.main_code = 'T04' then kp.issue in ('E51','T04')							else kp.issue = b.main_code						end 						) 2022/9/2 下午 01:46:05				GROUP BY UCASE(b.IDN_BAN), b.MAIN_CODE, b.BANK_CODE, b.user_id, b.query_ymd, to_char(kp.ins_date,'yyyymmdd'),kp.r_name,kp.start_date ) where kp300t_mh is null or kp300t_mh <> 'match' )

fixed: (select idn_ban, main_code, bank_code, user_id, query_ymd, start_date, b_30, a_90 from ( select UCASE(b.IDN_BAN) as idn_ban, b.MAIN_CODE, b.BANK_CODE, max(b.user_id) as user_id ,b.query_ymd ,(							case when kp.start_date between								( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 )								and								( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 )							then 'match'							else kp.start_date							end						) as kp300t_mh ,kp.start_date ,( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 ) as b_30 ,( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 ) as a_90 from (					select 					 UCASE(sd.query_key) as idn_ban,					  sd.main_code, sd.bank_code, 					  right(digits(day(sd.date)),2) as day, 					  si.inq_purpose_1,					  sd.ep_id as user_id, 					  (to_char(sd.date,'yyyymmdd')-19110000) as query_ymd					  from JCIC.EP_RES_11007 sd						  ,XMLTABLE('$doc/inputs' PASSING XMLPARSE(DOCUMENT CAST('  '||replace(replace(sd.query_input, ' ',''),',','  ')||'   ' as CLOB)) as "doc" COLUMNS query_id VARCHAR(50) PATH 'item/value[1]', inq_purpose_1 VARCHAR(2) PATH 'item/value[2]', inq_purpose_2 VARCHAR(2) PATH 'item/value[3]', inq_purpose_3 VARCHAR(2) PATH 'item/value[4]' ) si					 where 1=1					  and sd.query_type in ('P40', 'P44')					   and si.inq_purpose_1 = 'A'					   and si.inq_purpose_2 = 'A'					   and ( SUBSTR(sd.query_key, 1, 1) Between '0' and '9'  OR							SUBSTR(sd.query_key, 1, 1) Between 'A' and 'z' )					union all					select 					    UCASE(ta.IDN_BAN) as idn_ban, 					    tb.main_code, ta.bank_code, right(digits(day(ta.query_date)),2) as day, ta.inq_purpose_1,					    ta.user_id					   , (to_char(ta.query_date, 'yyyymmdd')-19110000) as query_ymd					 from JCIC.ST60011005T ta INNER JOIN JCIC.AB530T TB						ON TA.BANK_CODE=TB.BANK_CODE 					  where 1=1					    and ta.query_item in ('P40', 'P44')					    and ta.inq_purpose_1 = 'A'					    and ta.inq_purpose_2 = 'A'					    AND ( SUBSTR(ta.IDN_BAN, 1, 1) Between '0' and '9'  OR							SUBSTR(ta.IDN_BAN, 1, 1) Between 'A' and 'z' ) 				)b left outer join JCIC.kp300t kp on 					kp.idn_ban = UCASE(b.idn_ban) and (						case 							when b.main_code = 'T01' then kp.issue in ('E52','T01')							when b.main_code = 'T02' then kp.issue in ('E53','T02')							when b.main_code = 'T03' then kp.issue in ('E50','T03')							when b.main_code = 'T04' then kp.issue in ('E51','T04')							else kp.issue = b.main_code						end 						) GROUP BY UCASE(b.IDN_BAN), b.MAIN_CODE, b.BANK_CODE, b.user_id, b.query_ymd, to_char(kp.ins_date,'yyyymmdd'),kp.r_name,kp.start_date ) where kp300t_mh is null or kp300t_mh <> 'match' )

-- VERSION="1%A% %G% %U%"

--DROP TABLE FT_ANNUAL_PAYMENT ;

CREATE TABLE FT_ANNUAL_PAYMENT (  JCIC_CODE        CHAR(05)         NOT NULL,    C_NAME           CHAR(60)         DEFAULT ,   BAN              CHAR(10)         NOT NULL,   C_YEAR           CHAR(03)         NOT NULL,   ACCOUNTING_DATE  CHAR(07)         DEFAULT ,   EFFECTIVE_DATE   CHAR(07)         DEFAULT ,   ANNUAL_FEE       DECIMAL(7)       NOT NULL DEFAULT 1,   MAINT_ID         CHAR(10)         NOT NULL,   NOTES            VARCHAR(100)     DEFAULT ,   INS_TIME         TIMESTAMP        NOT NULL DEFAULT CURRENT TIMESTAMP,   UPD_TIME         TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP );

ALTER TABLE FT_ANNUAL_PAYMENT ADD CONSTRAINT PK_FT_ANNUAL_PAYMENT PRIMARY KEY (JCIC_CODE, BAN, C_YEAR);

CREATE INDEX IX_FT_ANNUAL_PAYMENT ON FT_ANNUAL_PAYMENT (JCIC_CODE, BAN, C_YEAR);

COMMENT ON TABLE FT_ANNUAL_PAYMENT                    IS '金融科技業者_年費資料'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.JCIC_CODE               IS '業者代碼'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.C_NAME           IS '中文名稱'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.BAN           IS '統一編號'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.ACCOUNTING_DATE           IS '入帳日期'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.EFFECTIVE_DATE           IS '生效日期'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.ANNUAL_FEE        IS '年費繳交金額'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.MAINT_ID            IS '維護人員帳號'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.NOTES            IS '備註'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.INS_TIME            IS '新增日期時間'; COMMENT ON COLUMN FT_ANNUAL_PAYMENT.UPD_TIME            IS '修改日期時間';

--20220616 依視窗組要求新增以下授權使用者

GRANT SELECT,INSERT,UPDATE,DELETE ON FT_ANNUAL_PAYMENT TO USER WFT_CO;

--20220616 依視窗組要求新增以下授權使用者_end

-- VERSION="1%A% %G% %U%"

--DROP TABLE FT_QYFEE_CFG ;

CREATE TABLE FT_QYFEE_CFG (  JCIC_CODE        CHAR(05)         NOT NULL,    C_NAME           CHAR(60)         DEFAULT ,   BAN              CHAR(10)         NOT NULL,   BILLING_YMS      CHAR(05)         NOT NULL DEFAULT '00001',   BILLING_SIGN     CHAR(01)         NOT NULL DEFAULT 'Y',   THRESHOLD        DECIMAL(6)       NOT NULL DEFAULT 1,   AMT_BELOW        DECIMAL(3)       NOT NULL DEFAULT 1,   DIS_AMT_BELOW    DECIMAL(3)       NOT NULL DEFAULT 1,   AMT_ABOVE        DECIMAL(3)       NOT NULL DEFAULT 1,   DIS_AMT_ABOVE    DECIMAL(3)       NOT NULL DEFAULT 1,   NOTES            VARCHAR(100)     DEFAULT ,   MAINT_ID         CHAR(10)         NOT NULL,   INS_TIME         TIMESTAMP        NOT NULL DEFAULT CURRENT TIMESTAMP,   UPD_TIME         TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP );

ALTER TABLE FT_QYFEE_CFG ADD CONSTRAINT PK_FT_QYFEE_CFG PRIMARY KEY (JCIC_CODE, BAN, UPD_TIME);

CREATE INDEX IX_FT_QYFEE_CFG ON FT_QYFEE_CFG (JCIC_CODE); CREATE INDEX IX_FT_QYFEE_CFG2 ON FT_QYFEE_CFG (BAN);

COMMENT ON TABLE FT_QYFEE_CFG                    IS '金融科技業者_查詢費參數檔'; COMMENT ON COLUMN FT_QYFEE_CFG.JCIC_CODE               IS '業者代碼'; COMMENT ON COLUMN FT_QYFEE_CFG.C_NAME           IS '中文名稱'; COMMENT ON COLUMN FT_QYFEE_CFG.BAN           IS '統一編號'; COMMENT ON COLUMN FT_QYFEE_CFG.BILLING_YMS        IS '計費起始年月'; COMMENT ON COLUMN FT_QYFEE_CFG.BILLING_SIGN            IS '計費啟用註記'; COMMENT ON COLUMN FT_QYFEE_CFG.THRESHOLD            IS '計費門檻值'; COMMENT ON COLUMN FT_QYFEE_CFG.AMT_BELOW            IS '小於或相等門檻值的單筆資料金額'; COMMENT ON COLUMN FT_QYFEE_CFG.DIS_AMT_BELOW            IS '小於或相等門檻值的單筆資料年費扣抵金額'; COMMENT ON COLUMN FT_QYFEE_CFG.AMT_ABOVE            IS '大於門檻值的單筆資料金額'; COMMENT ON COLUMN FT_QYFEE_CFG.DIS_AMT_ABOVE            IS '大於的單筆資料年費扣抵金額'; COMMENT ON COLUMN FT_QYFEE_CFG.NOTES            IS '備註'; COMMENT ON COLUMN FT_QYFEE_CFG.MAINT_ID            IS '維護人員帳號'; COMMENT ON COLUMN FT_QYFEE_CFG.INS_TIME            IS '新增日期時間'; COMMENT ON COLUMN FT_QYFEE_CFG.UPD_TIME            IS '修改日期時間';

--20220616 依視窗組要求新增以下授權使用者

GRANT SELECT,INSERT,UPDATE,DELETE ON FT_QYFEE_CFG TO USER WFT_CO;

--20220616 依視窗組要求新增以下授權使用者_end

using System; using System.Collections.Generic; using System.Linq; using System.Web; using WFT.lib; using System.Data; using WFT.Service; using System.IO; using System.Text;

namespace WFT.Customize.Service {   public class CST2701ASvc : WFTService {       ///         /// 報表類型 ///        private enum ReportType {           ///             /// ATT4_1 報表 ///            ATT4_1, ///            /// ATT3 報表 ///            ATT3, ///            /// ATT4_2 報表 ///            ATT4_2, ///            /// ATT4_3 報表 ///            ATT4_3, ///            /// ATT4_4 報表 ///            ATT4_4, ///            /// W01_Detail 報表 ///            W01_Detail, ///            /// W01_Branch 報表 ///            W01_Branch, ///            /// W01_MainBank 報表 ///            W01_MainBank }

///        /// 檔案資源鎖定物件 ///        static object lockMe = new object; ///        /// (更新,新增,查詢)資料 ///        ///  ///  public DataTable dataUpdateA(string QAYYYMM,string YYYSS) {           string sql = String.Empty; object[] strParam = null; DataTable dtResult = new DataTable;

try {               //using timeout instead //DaoManager.getConnection.Open; //DaoManager.getConnection.BeginTransaction;

IBM.Data.DB2.DB2Command db = new IBM.Data.DB2.DB2Command(sql, DaoManager.getConnection.getDB2Connection, DaoManager.getConnection.getDB2Transaction); db.CommandTimeout = 6000; //1.2.initWa001t類 //1.2.1               sql = String.Format(@"delete                                       from {0}wa001t                                       where BATCHNO like ? and SEQ_ID like 'A%' ", Configure.GetSchema("wa001t")); strParam = new object[1]; strParam[0] = String.Format("{0}%", YYYSS); DaoDbCmd.execute(sql, strParam);

//1.3.bld_W01_A類 //1.3.1               sql = String.Format(@"INSERT INTO {0}wa001t (QUERY_DATE,BANK_CODE,QUERY_WAY,QUERY_ITEM,IDN_BAN,USER_ID,SEQ_ID,BATCHNO)                                      with c01_all(query_ymd, bank_code, query_way, query_item, idn_ban, user_id)	as (	   select query_ymd, bank_code, 'A' as query_way, 'AAA' as query_item			, idn_ban, max(user_id) as user_id		from (	    select A.year||A.month||A.day as query_ymd,A.bank_code 	          ,A.idn_ban, A.user_id	     from {1}st500{2}t A join {3}sa141t B	          on substr(A.bank_code,1,3)=substr(B.bank_code,1,3)	             and A.query_item=B.query_item	             and A.qitem_sub=B.qitem_sub	     where (B.memo like '%CDM511%' 	         or B.memo like '%CDM512%' 	         or B.memo like '%CDM513%' 	         or B.memo like '%CDM514%' 	         or B.memo like '%CDM515%' 	         or B.memo like '%CDM516%' 	         or B.memo like '%CDM517%' or B.memo like '%CDM262%' or B.memo like '%CDM263%' )	          and start_yymm <=  ?	           and value(nullif(end_yymm,' '),'99912') >= ?	     union all	     select year||month||day as query_ymd, bank_code	            ,idn_ban, user_id 	     from {4}st500{5}t 	     where query_item in ('C05','C06','C07')	     ) group by idn_ban, bank_code, query_ymd ), c01_rank(query_ymd, bank_code, query_way, query_item, idn_ban, user_id, rank) as 	  ( select query_ymd, bank_code, query_way, query_item, idn_ban, user_id, rand from c01_all ),c01_seq(query_ymd, bank_code, query_way, query_item, idn_ban, user_id, seq)	   as 	    ( select query_ymd, bank_code, query_way, query_item, idn_ban, user_id ,row_number over (partition by bank_code order by rank ) from c01_rank ), c01_seqid(query_ymd, bank_code, query_way, query_item, idn_ban, user_id, seq_id ) as 	     ( select query_ymd, bank_code, query_way, query_item, idn_ban, user_id ,row_number over (partition by bank_code order by query_ymd, idn_ban) from c01_seq where seq<=10 order by bank_code, query_ymd, idn_ban ) 	      select query_ymd, bank_code, query_way, query_item, idn_ban, user_id	              ,'A'||substr('000000000',1,9-length(rtrim(char(seq_id))))||rtrim(cast(seq_id AS CHAR(9))), ?	       from c01_seqid", Configure.GetSchema("wa001t"), Configure.GetSchema(String.Format("st500{0}t", QAYYYMM)), QAYYYMM, Configure.GetSchema("sa141t"), Configure.GetSchema(String.Format("st500{0}t", QAYYYMM)), QAYYYMM);                strParam = new object[3];                strParam[0] = QAYYYMM;                strParam[1] = QAYYYMM;                strParam[2] = YYYSS;                try                {                    DaoDbCmd.execute(sql, strParam);                }                catch (Exception ex)                {                    throw ex;                }

sql = String.Format(@"UPDATE JCIC.WA900T SET  DELETE_FLAG = 'Y' where CLASS ='A' and DELETE_FLAG = 'N' "); try {                   DaoDbCmd.execute(sql); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"INSERT INTO JCIC.wa900t (  CLASS  ,YM  ,MAINT_ID  ,DELETE_FLAG  ,INERT_TIME  ,BATCHNO ) VALUES ('A', ?, ?,'N',current_timestamp, ? )");

string username = System.Web.HttpContext.Current.User.Identity.Name; string usernameFormal = username.Split('\\').Last;

strParam = new object[3]; strParam[0] = QAYYYMM; strParam[1] = usernameFormal; strParam[2] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

// don't need to commit, IBM.Data.DB2.DB2Command db               //  DaoManager.getConnection.Commit;

}           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               DaoManager.closeConnection; }           return dtResult; }       public DataTable dataUpdateB(string QBYYYMM, string YYYSS) {           string sql = String.Empty; object[] strParam = null; DataTable dtResult = new DataTable;

try {               //using timeout instead //DaoManager.getConnection.Open; //DaoManager.getConnection.BeginTransaction;

IBM.Data.DB2.DB2Command db = new IBM.Data.DB2.DB2Command(sql, DaoManager.getConnection.getDB2Connection, DaoManager.getConnection.getDB2Transaction); db.CommandTimeout = 6000;

//1.2.initWa001t類 //1.2.1               sql = String.Format(@"delete                                       from {0}wa001t                                       where BATCHNO like ? and SEQ_ID like 'B%' ", Configure.GetSchema("wa001t")); strParam = new object[1]; strParam[0] = String.Format("{0}%", YYYSS); DaoDbCmd.execute(sql, strParam);

//1.4.bld_W01_B類 //1.4.1               sql = String.Format(@"INSERT INTO {0}wa001t (QUERY_DATE,BANK_CODE,QUERY_WAY,QUERY_ITEM,IDN_BAN,USER_ID,SEQ_ID,BATCHNO)                                      with R08_all(query_date, bank_code, query_way, query_item, idn_ban, user_id)                                      as (                                        select query_ymd, bank_code, 'B' as query_item, 'BBB' as query_item			                              , idn_ban, max(user_id) as user_id 		                                  from(                                                 select A.year||A.month||A.day as query_ymd                                                 ,A.bank_code                                                    ,A.query_way                                                 ,A.query_item                                                 ,A.idn_ban                                                 ,A.user_id from {1}st500{2}t A join {3}sa141t B                                                              on substr(A.bank_code,1,3)=substr(B.bank_code,1,3) and A.query_item=B.query_item and A.qitem_sub=B.qitem_sub where (B.memo like '%RAS004%' or B.memo like '%RAM005%' or B.memo like '%RAM006%') and start_yymm <= ? and value(nullif(end_yymm,' '),'99912') >= ? union all select year||month||day ,bank_code ,query_way ,query_item ,idn_ban ,user_id from {4}st500{5}t where query_item='R08' )			                        group by idn_ban, bank_code, query_ymd 		                                          ), R08_rank(query_date, bank_code, query_way, query_item, idn_ban, user_id, rank) as (select query_date, bank_code, query_way, query_item, idn_ban, user_id, rand                                           from R08_all                                         ), R08_seq(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq) as (select query_date, bank_code, query_way, query_item, idn_ban, user_id,                                                 row_number over (partition by bank_code order by rank)                                            from R08_rank                                          ), R08_seqid(query_date,bank_code,query_way,query_item,idn_ban,user_id,seq_id) as (select query_date, bank_code, query_way, query_item, idn_ban, user_id,                                         row_number over (partition by bank_code order by query_date,idn_ban)                                          from R08_seq                                          where seq <=20                                          order by bank_code, query_date,idn_ban                                         ) select query_date, bank_code, query_way, query_item, idn_ban, user_id, 'B'||substr('000000000',1,9-length(rtrim(char(seq_id))))||rtrim(cast(seq_id AS CHAR(9))), ? from R08_seqid;", Configure.GetSchema("wa001t"),                                                                                                                                         Configure.GetSchema(String.Format("st500{0}t", QBYYYMM)),                                                                                                                                          QBYYYMM,                                                                                                                                          Configure.GetSchema("sa141t"),                                                                                                                                          Configure.GetSchema(String.Format("st500{0}t", QBYYYMM)),                                                                                                                                          QBYYYMM); strParam = new object[3]; strParam[0] = QBYYYMM; strParam[1] = QBYYYMM; strParam[2] = YYYSS; try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"UPDATE JCIC.WA900T SET  DELETE_FLAG = 'Y' where CLASS ='B' and DELETE_FLAG = 'N' "); try {                   DaoDbCmd.execute(sql); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"INSERT INTO JCIC.wa900t (  CLASS  ,YM  ,MAINT_ID  ,DELETE_FLAG  ,INERT_TIME  ,BATCHNO ) VALUES ('B', ?, ?,'N',current_timestamp, ? )");

string username = System.Web.HttpContext.Current.User.Identity.Name; string usernameFormal = username.Split('\\').Last;

strParam = new object[3]; strParam[0] = QBYYYMM; strParam[1] = usernameFormal; strParam[2] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

// don't need to commit, IBM.Data.DB2.DB2Command db                //  DaoManager.getConnection.Commit;

}           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               DaoManager.closeConnection; }           return dtResult; }       public DataTable dataUpdateC(string QCYYYMM, string YYYSS) {           string sql = String.Empty; object[] strParam = null; DataTable dtResult = new DataTable;

try {               //using timeout instead //DaoManager.getConnection.Open; //DaoManager.getConnection.BeginTransaction;

IBM.Data.DB2.DB2Command db = new IBM.Data.DB2.DB2Command(sql, DaoManager.getConnection.getDB2Connection, DaoManager.getConnection.getDB2Transaction); db.CommandTimeout = 6000;

//1.2.initWa001t類 //1.2.1               sql = String.Format(@"delete                                       from {0}wa001t                                       where BATCHNO like ? and SEQ_ID like 'C%' ", Configure.GetSchema("wa001t")); strParam = new object[1]; strParam[0] = String.Format("{0}%", YYYSS); DaoDbCmd.execute(sql, strParam);

//1.4.3 bld_W01_C類 sql = String.Format(@"INSERT INTO {0}wa001t (QUERY_DATE,BANK_CODE,QUERY_WAY,QUERY_ITEM,IDN_BAN,USER_ID,SEQ_ID,BATCHNO)                                     with T_all(query_date, bank_code, idn_ban, user_id)                                      as (select char((year(QUERY_DATE)-1911)*10000+month(QUERY_DATE)*100+day(QUERY_DATE))                                          ,bank_code                                          ,ban                                          ,max(user_id)                                          from {1}st530t A                                          where (year(QUERY_DATE)-1911)*100+month(QUERY_DATE)=?                                          group by char((year(QUERY_DATE)-1911)*10000+month(QUERY_DATE)*100+day(QUERY_DATE)), bank_code, ban),                                      T_rank(query_date, bank_code, idn_ban, user_id, rank)                                      as (select query_date, bank_code, idn_ban, user_id, rand from T_all),                                     T_seq(query_date, bank_code, idn_ban, user_id, seq)                                      as (select query_date, bank_code, idn_ban, user_id , ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY rank) from T_rank),                                     T_seqid(query_date, bank_code, idn_ban, user_id, seq_id)                                      as (select query_date, bank_code, idn_ban, user_id , ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY query_date,idn_ban) from T_seq where seq<=5)                                     select query_date                                             ,bank_code                                             ,'C'                                             ,'CCC'                                             ,idn_ban                                             ,user_id                                                                                       ,'C'||substr('000000000',1,9-length(rtrim(char(seq_id))))||rtrim(cast(seq_id AS CHAR(9))), ?                                       from T_seqid;", Configure.GetSchema("wa001t")                                                   , Configure.GetSchema("st530t"));

strParam = new object[2]; strParam[0] = QCYYYMM; strParam[1] = YYYSS; try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"UPDATE JCIC.WA900T SET  DELETE_FLAG = 'Y' where CLASS ='C' and DELETE_FLAG = 'N' "); try {                   DaoDbCmd.execute(sql); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"INSERT INTO JCIC.wa900t (  CLASS  ,YM  ,MAINT_ID  ,DELETE_FLAG  ,INERT_TIME  ,BATCHNO ) VALUES ('C', ?, ?,'N',current_timestamp, ? )");

string username = System.Web.HttpContext.Current.User.Identity.Name; string usernameFormal = username.Split('\\').Last;

strParam = new object[3]; strParam[0] = QCYYYMM; strParam[1] = usernameFormal; strParam[2] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }               // don't need to commit, IBM.Data.DB2.DB2Command db                 //  DaoManager.getConnection.Commit;

}           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               DaoManager.closeConnection; }           return dtResult; }       public DataTable dataUpdateD(string QDYYYMM, string YYYSS) {           string sql = String.Empty; object[] strParam = null; DataTable dtResult = new DataTable;

try {               //using timeout instead //DaoManager.getConnection.Open; //DaoManager.getConnection.BeginTransaction;

IBM.Data.DB2.DB2Command db = new IBM.Data.DB2.DB2Command(sql, DaoManager.getConnection.getDB2Connection, DaoManager.getConnection.getDB2Transaction); db.CommandTimeout = 0;

//1.2.initWa001t類 //1.2.1               sql = String.Format(@"delete                                       from {0}wa001t                                       where BATCHNO like ? and SEQ_ID like 'D%' ", Configure.GetSchema("wa001t")); strParam = new object[1]; strParam[0] = String.Format("{0}%", YYYSS); DaoDbCmd.execute(sql, strParam);

//1.4.4.0 bld_W01_D類   20190311  chinchi

object[] strMonthParam = null; strMonthParam = new object[6]; strMonthParam[0] = QDYYYMM; for (int i = 1; i < 6; i++) {

if ((Int32.Parse(QDYYYMM.Substring(3, 2)) + i) >= 13) strMonthParam[i] = (Int32.Parse(QDYYYMM.Substring(0, 3)) + 1).ToString + "0" + ((Int32.Parse(QDYYYMM.Substring(3, 2)) + i) % 12).ToString; else if ((Int32.Parse(QDYYYMM.Substring(3, 2)) + i) >= 10) strMonthParam[i] = (Int32.Parse(QDYYYMM.Substring(0, 3)).ToString + (Int32.Parse(QDYYYMM.Substring(3, 2)) + i).ToString);    //ex 10710 else strMonthParam[i] = (Int32.Parse(QDYYYMM.Substring(0, 3)).ToString + "0" + (Int32.Parse(QDYYYMM.Substring(3, 2)) + i).ToString); //ex 10702 }

sql = String.Format(@"INSERT INTO {0}wa001t (QUERY_DATE,BANK_CODE,QUERY_WAY,QUERY_ITEM,IDN_BAN,USER_ID,SEQ_ID,BATCHNO)                                    with T_all(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id) as	(		select query_month, query_ymd, bank_code, 'D'as query, 'DDD' as query_item			, idn_ban, max(user_id) as user_id 		from (				((select A.month as query_month, A.year||A.month||A.day as query_ymd,A.bank_code ,A.query_way ,A.query_item ,A.idn_ban ,A.user_id	        from {1}st500{2}t A join {3}sa141t B 				 on substr(A.bank_code,1,3)=substr(B.bank_code,1,3)				 and A.query_item=B.query_item				 and A.qitem_sub=B.qitem_sub	        where 		    B.memo like '%BAM044%'		    and start_yymm <= ?		    and value(nullif(end_yymm,' '),'99912') >= ?	        union all	        select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {1}st500{2}t where query_item='B46' and year||month = ? )			union all           (select A.month as query_month, A.year||A.month||A.day as query_ymd,A.bank_code ,A.query_way ,A.query_item ,A.idn_ban ,A.user_id from {4}st500{5}t A join {3}sa141t B 				 on substr(A.bank_code,1,3)=substr(B.bank_code,1,3) and A.query_item=B.query_item and A.qitem_sub=B.qitem_sub where B.memo like '%BAM044%' and start_yymm <= ? and value(nullif(end_yymm,' '),'99912') >= ? union all select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {4}st500{5}t where query_item='B46' and year||month = ? )			union all           (select A.month as query_month, A.year||A.month||A.day as query_ymd,A.bank_code ,A.query_way ,A.query_item ,A.idn_ban ,A.user_id from {6}st500{7}t A join {3}sa141t B 				 on substr(A.bank_code,1,3)=substr(B.bank_code,1,3) and A.query_item=B.query_item and A.qitem_sub=B.qitem_sub where B.memo like '%BAM044%' and start_yymm <= ? and value(nullif(end_yymm,' '),'99912') >= ? union all select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {6}st500{7}t where query_item='B46' and year||month = ? )			union all           (select A.month as query_month, A.year||A.month||A.day as query_ymd,A.bank_code ,A.query_way ,A.query_item ,A.idn_ban ,A.user_id from {8}st500{9}t A join {3}sa141t B 				 on substr(A.bank_code,1,3)=substr(B.bank_code,1,3) and A.query_item=B.query_item and A.qitem_sub=B.qitem_sub where B.memo like '%BAM044%' and start_yymm <= ? and value(nullif(end_yymm,' '),'99912') >= ? union all select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {8}st500{9}t where query_item='B46' and year||month = ? )			union all           (select A.month as query_month, A.year||A.month||A.day as query_ymd,A.bank_code ,A.query_way ,A.query_item ,A.idn_ban ,A.user_id from {10}st500{11}t A join {3}sa141t B 				 on substr(A.bank_code,1,3)=substr(B.bank_code,1,3) and A.query_item=B.query_item and A.qitem_sub=B.qitem_sub where B.memo like '%BAM044%' and start_yymm <= ? and value(nullif(end_yymm,' '),'99912') >= ? union all select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {10}st500{11}t where query_item='B46' and year||month = ? )			union all           (select A.month as query_month, A.year||A.month||A.day as query_ymd,A.bank_code ,A.query_way ,A.query_item ,A.idn_ban ,A.user_id from {12}st500{13}t A join {3}sa141t B 				 on substr(A.bank_code,1,3)=substr(B.bank_code,1,3) and A.query_item=B.query_item and A.qitem_sub=B.qitem_sub where B.memo like '%BAM044%' and start_yymm <= ? and value(nullif(end_yymm,' '),'99912') >= ? union all select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {12}st500{13}t where query_item='B46' and year||month = ? ))			)			group by idn_ban, bank_code, query_month,query_ymd	) ,T_rank(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, rank) as 	 (	  select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, rand	   from T_all	 ),T_seq(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, seq) as 	  (		select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id	, ROW_NUMBER OVER (PARTITION BY query_month, bank_code ORDER BY rank) 		from T_rank	  ),T_seqid(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id, randnum ) as 	  (		select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, 		ROW_NUMBER OVER (PARTITION BY query_month, bank_code ORDER BY query_month,idn_ban),rand    		from T_seq		where seq<=2	  ),T_reasignnum(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id2 ) as 	  ( select query_date, bank_code, query_way, query_item, idn_ban, user_id, ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY randnum) from T_seqid ),T_final(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id3 ) as 	 ( select query_date, bank_code, query_way, query_item, idn_ban, user_id, ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY seq_id2) from T_reasignnum where seq_id2<=5 )	 select query_date, bank_code, query_way, query_item, idn_ban, user_id,	  'D'||substr('000000000',1,9-length(rtrim(char(seq_id3))))||rtrim(cast(seq_id3 AS CHAR(9))), ?  	  from T_final	  order by bank_code, query_date	;", Configure.GetSchema("wa001t"),                                                                                                                          Configure.GetSchema(String.Format("st500{0}t", strMonthParam[0])),                                                                                                                          strMonthParam[0],                                                                                                                          Configure.GetSchema("sa141t"),                                                                                                                          Configure.GetSchema(String.Format("st500{0}t", strMonthParam[1])), strMonthParam[1], Configure.GetSchema(String.Format("st500{0}t", strMonthParam[2])), strMonthParam[2], Configure.GetSchema(String.Format("st500{0}t", strMonthParam[3])), strMonthParam[3], Configure.GetSchema(String.Format("st500{0}t", strMonthParam[4])), strMonthParam[4], Configure.GetSchema(String.Format("st500{0}t", strMonthParam[5])), strMonthParam[5]);

strParam = new object[19]; strParam[0] = strMonthParam[0]; strParam[1] = strMonthParam[0]; strParam[2] = strMonthParam[0]; strParam[3] = strMonthParam[1]; strParam[4] = strMonthParam[1]; strParam[5] = strMonthParam[1]; strParam[6] = strMonthParam[2]; strParam[7] = strMonthParam[2]; strParam[8] = strMonthParam[2]; strParam[9] = strMonthParam[3]; strParam[10] = strMonthParam[3]; strParam[11] = strMonthParam[3]; strParam[12] = strMonthParam[4]; strParam[13] = strMonthParam[4]; strParam[14] = strMonthParam[4]; strParam[15] = strMonthParam[5]; strParam[16] = strMonthParam[5]; strParam[17] = strMonthParam[5]; strParam[18] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"UPDATE JCIC.WA900T SET  DELETE_FLAG = 'Y' where CLASS ='D' and DELETE_FLAG = 'N' "); try {                   DaoDbCmd.execute(sql); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"INSERT INTO JCIC.wa900t (  CLASS  ,YM  ,MAINT_ID  ,DELETE_FLAG  ,INERT_TIME  ,BATCHNO ) VALUES ('D', ?, ?,'N',current_timestamp, ? )");

string username = System.Web.HttpContext.Current.User.Identity.Name; string usernameFormal = username.Split('\\').Last;

strParam = new object[3]; strParam[0] = QDYYYMM; strParam[1] = usernameFormal; strParam[2] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }               // don't need to commit, IBM.Data.DB2.DB2Command db                 //  DaoManager.getConnection.Commit;

}           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               DaoManager.closeConnection; }           return dtResult; }       public DataTable dataUpdateE(string QEYYYMM, string YYYSS) {           string sql = String.Empty; object[] strParam = null; DataTable dtResult = new DataTable;

try {               //using timeout instead //DaoManager.getConnection.Open; //DaoManager.getConnection.BeginTransaction;

IBM.Data.DB2.DB2Command db = new IBM.Data.DB2.DB2Command(sql, DaoManager.getConnection.getDB2Connection, DaoManager.getConnection.getDB2Transaction); db.CommandTimeout = 6000;

//1.2.initWa001t類 //1.2.1               sql = String.Format(@"delete                                       from {0}wa001t                                       where BATCHNO like ? and SEQ_ID like 'E%' ", Configure.GetSchema("wa001t")); strParam = new object[1]; strParam[0] = String.Format("{0}%", YYYSS); DaoDbCmd.execute(sql, strParam);

//1.4.4.0 bld_W01_E類   20200911  chinchi

//caculate QEYYYMM   YYYMM, YYYMM+1 ... YYYMM+11, total 12 months , remember to mode 12 object[] strMonthParamE = null; strMonthParamE = new object[12]; strMonthParamE[0] = QEYYYMM; for (int i = 1; i < 12; i++) {

if ((Int32.Parse(QEYYYMM.Substring(3, 2)) + i) >= 13) strMonthParamE[i] = (Int32.Parse(QEYYYMM.Substring(0, 3)) + 1).ToString + "0" + ((Int32.Parse(QEYYYMM.Substring(3, 2)) + i) % 12).ToString; else if ((Int32.Parse(QEYYYMM.Substring(3, 2)) + i) >= 10) strMonthParamE[i] = (Int32.Parse(QEYYYMM.Substring(0, 3)).ToString + (Int32.Parse(QEYYYMM.Substring(3, 2)) + i).ToString);    //ex 10710 else strMonthParamE[i] = (Int32.Parse(QEYYYMM.Substring(0, 3)).ToString + "0" + (Int32.Parse(QEYYYMM.Substring(3, 2)) + i).ToString); //ex 10702 }

sql = String.Format(@"INSERT INTO {0}wa001t (QUERY_DATE,BANK_CODE,QUERY_WAY,QUERY_ITEM,IDN_BAN,USER_ID,SEQ_ID,BATCHNO)                                    with T_all(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id) as	(		select query_month, query_ymd, bank_code, 'E'as query, query_item			, idn_ban, max(user_id) as user_id 		from ( ((select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id	   from {1}st500{2}t 	   where query_item in ('T50','T51','T52','T53','T54')		 and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id	   from {3}st500{4}t 	   where query_item in ('T50','T51','T52','T53','T54')		 and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {5}st500{6}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {7}st500{8}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {9}st500{10}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {11}st500{12}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {13}st500{14}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {15}st500{16}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {17}st500{18}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {19}st500{20}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {21}st500{22}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ?) union all (select month as query_month, year||month||day as query_ymd,bank_code ,query_way ,query_item ,idn_ban ,user_id from {23}st500{24}t where query_item in ('T50','T51','T52','T53','T54') and year||month = ? ))

)			group by idn_ban, bank_code, query_month,query_ymd, query_item	) ,T_rank(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, rank) as 	 (	  select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, rand	   from T_all	 ),T_seq(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, seq) as 	  (		select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id	, ROW_NUMBER OVER (PARTITION BY query_month, bank_code ORDER BY rank) 		from T_rank	  ),T_seqid(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id, randnum ) as 	  (		select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, 		ROW_NUMBER OVER (PARTITION BY query_month, bank_code ORDER BY query_month,idn_ban),rand    		from T_seq		where seq<=2	  ),T_reasignnum(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id2 ) as 	  ( select query_date, bank_code, query_way, query_item, idn_ban, user_id, ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY randnum) from T_seqid ),T_final(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id3 ) as 	 ( select query_date, bank_code, query_way, query_item, idn_ban, user_id, ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY seq_id2) from T_reasignnum where seq_id2<=10 )	 select query_date, bank_code, query_way, query_item, idn_ban, user_id,	  'E'||substr('000000000',1,9-length(rtrim(char(seq_id3))))||rtrim(cast(seq_id3 AS CHAR(9))), ? 	  from T_final	  order by bank_code, query_date	;", Configure.GetSchema("wa001t"), Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[0])), strMonthParamE[0], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[1])), strMonthParamE[1], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[2])), strMonthParamE[2], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[3])), strMonthParamE[3], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[4])), strMonthParamE[4], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[5])), strMonthParamE[5], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[0])), strMonthParamE[0], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[1])), strMonthParamE[1], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[2])), strMonthParamE[2], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[3])), strMonthParamE[3], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[4])), strMonthParamE[4], Configure.GetSchema(String.Format("st500{0}t", strMonthParamE[5])), strMonthParamE[5]);

strParam = new object[13]; strParam[0] = strMonthParamE[0]; strParam[1] = strMonthParamE[1]; strParam[2] = strMonthParamE[2]; strParam[3] = strMonthParamE[3]; strParam[4] = strMonthParamE[4]; strParam[5] = strMonthParamE[5]; strParam[6] = strMonthParamE[6]; strParam[7] = strMonthParamE[7]; strParam[8] = strMonthParamE[8]; strParam[9] = strMonthParamE[9]; strParam[10] = strMonthParamE[10]; strParam[11] = strMonthParamE[11]; strParam[12] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"UPDATE JCIC.WA900T SET  DELETE_FLAG = 'Y' where CLASS ='E' and DELETE_FLAG = 'N' "); try {                   DaoDbCmd.execute(sql); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"INSERT INTO JCIC.wa900t (  CLASS  ,YM  ,MAINT_ID  ,DELETE_FLAG  ,INERT_TIME  ,BATCHNO ) VALUES ('E', ?, ?,'N',current_timestamp, ? )");

string username = System.Web.HttpContext.Current.User.Identity.Name; string usernameFormal = username.Split('\\').Last;

strParam = new object[3]; strParam[0] = QEYYYMM; strParam[1] = usernameFormal; strParam[2] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

// don't need to commit, IBM.Data.DB2.DB2Command db                //  DaoManager.getConnection.Commit;

}           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               DaoManager.closeConnection; }           return dtResult; }       public DataTable dataUpdateF(string QFYYYMM, string YYYSS) {           string sql = String.Empty; object[] strParam = null; DataTable dtResult = new DataTable;

try {               //using timeout instead //DaoManager.getConnection.Open; //DaoManager.getConnection.BeginTransaction;

IBM.Data.DB2.DB2Command db = new IBM.Data.DB2.DB2Command(sql, DaoManager.getConnection.getDB2Connection, DaoManager.getConnection.getDB2Transaction); db.CommandTimeout = 6000;

//1.2.initWa001t類 //1.2.1               sql = String.Format(@"delete                                       from {0}wa001t                                       where BATCHNO like ? and SEQ_ID like 'F%' ", Configure.GetSchema("wa001t")); strParam = new object[1]; strParam[0] = String.Format("{0}%", YYYSS); DaoDbCmd.execute(sql, strParam);

//caculate QFYYYMM   YYYMM, YYYMM+1 ... YYYMM+3, total 4 months , remember to mode 12 object[] strMonthParamF = null; strMonthParamF = new object[4]; strMonthParamF[0] = QFYYYMM; for (int i = 1; i < 4; i++) {

if ((Int32.Parse(QFYYYMM.Substring(3, 2)) + i) >= 13) strMonthParamF[i] = (Int32.Parse(QFYYYMM.Substring(0, 3)) + 1).ToString + "0" + ((Int32.Parse(QFYYYMM.Substring(3, 2)) + i) % 12).ToString; else if ((Int32.Parse(QFYYYMM.Substring(3, 2)) + i) >= 10) strMonthParamF[i] = (Int32.Parse(QFYYYMM.Substring(0, 3)).ToString + (Int32.Parse(QFYYYMM.Substring(3, 2)) + i).ToString);    //ex 10710 else strMonthParamF[i] = (Int32.Parse(QFYYYMM.Substring(0, 3)).ToString + "0" + (Int32.Parse(QFYYYMM.Substring(3, 2)) + i).ToString); //ex 10702 }

sql = String.Format(@"INSERT INTO {0}wa001t (QUERY_DATE,BANK_CODE,QUERY_WAY,QUERY_ITEM,IDN_BAN,USER_ID,SEQ_ID,BATCHNO)                                    with T_all(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id) as	(		select query_month, query_ymd, bank_code, 'F'as query, 'FFF' as query_item			, idn_ban, max(user_id) as user_id 		from ( ((select TA.month as query_month, TA.year||TA.month||TA.day as query_ymd,          TA.bank_code ,TA.query_way ,TA.query_item ,TA.idn_ban ,TA.user_id	   from {1}st500{2}t TA INNER JOIN {3}WA009W TB			ON TA.BANK_CODE=TB.BANK_CODE and TA.idn_ban = TB.idn_ban and TB.status = '' 	   where TA.query_item = 'T70'		 and TA.inq_purpose_1 in ('A', 'H')		 and TA.year||TA.month = ?) union all (select TA.month as query_month, TA.year||TA.month||TA.day as query_ymd,          TA.bank_code ,TA.query_way ,TA.query_item ,TA.idn_ban ,TA.user_id from {4}st500{5}t TA INNER JOIN {6}WA009W TB			ON TA.BANK_CODE=TB.BANK_CODE and TA.idn_ban = TB.idn_ban and TB.status = '' where TA.query_item = 'T70' and TA.inq_purpose_1 in ('A', 'H') and TA.year||TA.month = ?) union all (select TA.month as query_month, TA.year||TA.month||TA.day as query_ymd, TA.bank_code ,TA.query_way ,TA.query_item ,TA.idn_ban ,TA.user_id from {7}st500{8}t TA INNER JOIN {9}WA009W TB			ON TA.BANK_CODE=TB.BANK_CODE and TA.idn_ban = TB.idn_ban and TB.status = '' where TA.query_item = 'T70' and TA.inq_purpose_1 in ('A', 'H') and TA.year||TA.month = ?) union all (select TA.month as query_month, TA.year||TA.month||TA.day as query_ymd, TA.bank_code ,TA.query_way ,TA.query_item ,TA.idn_ban ,TA.user_id from {10}st500{11}t TA INNER JOIN {12}WA009W TB			ON TA.BANK_CODE=TB.BANK_CODE and TA.idn_ban = TB.idn_ban and TB.status = '' where TA.query_item = 'T70' and TA.inq_purpose_1 in ('A', 'H') and TA.year||TA.month = ? ))

)			group by idn_ban, bank_code, query_month,query_ymd	) ,T_rank(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, rank) as 	 (	  select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, rand	   from T_all	 ),T_seq(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, seq) as 	  (		select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id	, ROW_NUMBER OVER (PARTITION BY query_month, bank_code ORDER BY rank) 		from T_rank	  ),T_seqid(query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id, randnum ) as 	  (		select query_month, query_date, bank_code, query_way, query_item, idn_ban, user_id, 		ROW_NUMBER OVER (PARTITION BY query_month, bank_code ORDER BY query_month,idn_ban),rand    		from T_seq		where seq<=2	  ),T_reasignnum(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id2 ) as 	  ( select query_date, bank_code, query_way, query_item, idn_ban, user_id, ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY randnum) from T_seqid ),T_final(query_date, bank_code, query_way, query_item, idn_ban, user_id, seq_id3 ) as 	 ( select query_date, bank_code, query_way, query_item, idn_ban, user_id, ROW_NUMBER OVER (PARTITION BY bank_code ORDER BY seq_id2) from T_reasignnum where seq_id2<=5 )	 select query_date, bank_code, query_way, query_item, idn_ban, user_id,	  'F'||substr('000000000',1,9-length(rtrim(char(seq_id3))))||rtrim(cast(seq_id3 AS CHAR(9))), ?  	  from T_final	  order by bank_code, query_date	;", Configure.GetSchema("wa001t"),      Configure.GetSchema(String.Format("st500{0}t", strMonthParamF[0])), strMonthParamF[0], Configure.GetSchema("wa009w"), Configure.GetSchema(String.Format("st500{0}t", strMonthParamF[1])), strMonthParamF[1], Configure.GetSchema("wa009w"), Configure.GetSchema(String.Format("st500{0}t", strMonthParamF[2])), strMonthParamF[2], Configure.GetSchema("wa009w"), Configure.GetSchema(String.Format("st500{0}t", strMonthParamF[3])), strMonthParamF[3], Configure.GetSchema("wa009w"));

strParam = new object[5]; strParam[0] = strMonthParamF[0]; strParam[1] = strMonthParamF[1]; strParam[2] = strMonthParamF[2]; strParam[3] = strMonthParamF[3]; strParam[4] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"UPDATE JCIC.WA900T SET  DELETE_FLAG = 'Y' where CLASS ='F' and DELETE_FLAG = 'N' "); try {                   DaoDbCmd.execute(sql); }               catch (Exception ex) {                   throw ex; }

sql = String.Format(@"INSERT INTO JCIC.wa900t (  CLASS  ,YM  ,MAINT_ID  ,DELETE_FLAG  ,INERT_TIME  ,BATCHNO ) VALUES ('F', ?, ?,'N',current_timestamp, ? )");

string username = System.Web.HttpContext.Current.User.Identity.Name; string usernameFormal = username.Split('\\').Last;

strParam = new object[3]; strParam[0] = QFYYYMM; strParam[1] = usernameFormal; strParam[2] = YYYSS;

try {                   DaoDbCmd.execute(sql, strParam); }               catch (Exception ex) {                   throw ex; }

// don't need to commit, IBM.Data.DB2.DB2Command db                //  DaoManager.getConnection.Commit;

}           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               DaoManager.closeConnection; }           return dtResult; }

} }

using System; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using WFT.SingleTableMtn.Model; using System.Collections.Generic; using WFT.lib; using WFT.SingleTableMtn.Base;

namespace WFT.SingleTableMtn {   public partial class FT_ANNUAL_PAYMENTMtn : TableMtn, ICallbackEventHandler {       //回傳值 protected string RefValue;

public void Page_PreInit(object sender, EventArgs e)       { base.Page_PreInit(sender, e); }

public void Page_Init(object sender, EventArgs e)       { base.Page_Init(sender, e); }

public void Page_Load(object sender, EventArgs e)       { //產生畫面 Layout Dictionary keyColumns = null; Dictionary typeColumns = null; Dictionary columns = GetLayoutColumnInfo(out keyColumns, out typeColumns); this.GenerateAttributes(columns, keyColumns, typeColumns); this.GenerateCSS; this.GenerateJS; base.Page_Load(sender, e); }

protected void Page_LoadComplete(object sender, EventArgs e)       { base.Page_LoadComplete(sender, e); }

protected void Page_Unload(object sender, EventArgs e)       { base.Page_Unload(sender, e); }

#region 註冊事件 ///        /// 註冊頁面事件 ///        public override void regPageEvent {           // 表單初始化前事件 this.BeforePageInit += new Action(this.CustomBeforePageInit); // 表單初始化後事件 this.AfterPageInit += new Action(this.CustomAfterPageInit); // 表單載入前事件 this.BeforePageLoad += new Action(this.CustomBeforePageLoad); // 表單載入後事件 this.AfterPageLoad += new Action(this.CustomAfterPageLoad); // 表單載入完成前事件 this.BeforePageLoadComplete += new Action(this.CustomBeforePageLoadComplete); // 表單載入完成後事件 this.AfterPageLoadComplete += new Action(this.CustomAfterPageLoadComplete); // 表單卸載前事件 this.BeforePageUnload += new Action(this.CustomBeforePageUnload); // 表單卸載後事件 this.AfterPageUnload += new Action(this.CustomAfterPageUnload); // 更新前事件 this.BeforeUpdate += new Action(this.CustomBeforeUpdate); // 更新時事件 this.Updating += new Action, Dictionary, WFT.lib.SystemCommandEventArgs>(this.CustomUpdating); // 更新後事件 this.AfterUpdate += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterUpdate); // 新增前事件 this.BeforeInsert += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeInsert); // 新增時事件 this.Inserting += new Action<string, Dictionary<string,object>, WFT.lib.SystemCommandEventArgs>(this.CustomInserting); // 新增後事件 this.AfterInsert += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterInsert); // 刪除前事件 this.BeforeDelete += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeDelete); // 刪除時事件 this.Deleting += new Action<string, Dictionary<String, Object>, Dictionary<string,object>, WFT.lib.SystemCommandEventArgs>(this.CustomDeleting); // 刪除後事件 this.AfterDelete += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterDelete); // 編輯前事件 this.BeforeEdit += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeEdit); // 編輯時事件 this.Editing += new Action<Control, WFT.lib.SystemCommandEventArgs>(this.CustomEditing); // 編輯後事件 this.AfterEdit += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterEdit); // 移到下一頁前事件 this.BeforeNext += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeNext); // 移到下一頁時事件 this.Nexting += new Action<System.Web.UI.WebControls.GridViewRow, WFT.lib.SystemCommandEventArgs>(this.CustomNexting); // 移到下一頁後事件 this.AfterNext += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterNext); // 移到上一頁前事件 this.BeforePrevious += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforePrevious); // 移到上一頁時事件 this.Previousing += new Action<System.Web.UI.WebControls.GridViewRow, WFT.lib.SystemCommandEventArgs>(this.CustomPreviousing); // 移到下一頁後事件 this.AfterPrevious += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterPrevious); // 列印前事件 this.BeforePrint += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforePrint); // 列印時事件 this.Printing += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomPrinting); // 列印後事件 this.AfterPrint += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterPrint); // 清除前事件 this.BeforeReset += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeReset); // 清除時事件 this.Reseting += new Action<string, WFT.lib.SystemCommandEventArgs>(this.CustomReseting); // 清除後事件 this.AfterReset += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterReset); // 回清單頁前事件 this.BeforeToList += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeToList); // 回清單頁時事件 this.ToListing += new Action<string, WFT.lib.SystemCommandEventArgs>(this.CustomToListing); // 回清單頁後事件 this.AfterToList += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterToList); // 回查詢頁前事件 this.BeforeToQuery += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeToQuery); // 回查詢頁時事件 this.ToQuerying += new Action<string, WFT.lib.SystemCommandEventArgs>(this.CustomToQuerying); // 回查詢頁後事件 this.AfterToQuery += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterToQuery); // 回主作業前事件 this.BeforeToCustomize += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeToCustomize); // 回主作業時事件 this.ToCustomizing += new Action<string, WFT.lib.SystemCommandEventArgs>(this.CustomToCustomizing); // 回主作業後事件 this.AfterToCustomize += new Action<object, WFT.lib.SystemCommandEventArgs>(this.CustomAfterToCustomize); // 顯示資料前事件 this.BeforeShow += new Action<System.Web.UI.WebControls.GridViewRow, WFT.lib.SystemCommandEventArgs>(this.CustomBeforeShow); // 顯示資料時事件 this.Showing += new Action<DataTable, WFT.lib.SystemCommandEventArgs>(this.CustomShowing); // 顯示資料後事件 this.AfterShow += new Action<System.Web.UI.WebControls.GridViewRow, WFT.lib.SystemCommandEventArgs>(this.CustomAfterShow); // 表單狀態前事件 this.BeforeFormState += new Action<object, WFT.lib.SystemEventArgs>(this.CustomBeforeFormState); // 表單狀態後事件 this.AfterFormState += new Action<object, WFT.lib.SystemEventArgs>(this.CustomAfterFormState); // 單一資料表維護記錄前事件 this.BeforeMtnLog += new Action<string, Dictionary<string, object>, WFT.lib.SystemEventArgs>(this.CustomBeforeMtnLog); // 單一資料表維護記錄後事件 this.AfterMtnLog += new Action<string, Dictionary<string, object>, WFT.lib.SystemEventArgs>(this.CustomAfterMtnLog); // 取得 REF_ID 結果事件 this.GetRef_ID += this.CustomGetRef_ID; // 取得 DEFAULT_VALUE 結果事件 this.GetDefault_Value += new Func<string, string, string>(this.CustomGetDefault_Value); // 取得 FORCE_VALUE 結果事件 this.GetForce_Value += new Func<string, string, string>(this.CustomGetForce_Value); // 取得 GET_VALUE 結果事件 this.GetGet_Value += new Func<string, string, string>(this.CustomGetGet_Value); }

///        /// 註冊控制項事件 ///        public override void regControlEvent {			// 查詢視窗載入前事件 this.ucQueryWindow.BeforePageLoad += new Action<object, WFT.lib.SystemEventArgs>(this.CustomBeforeQueryWindowPageLoad); // 查詢視窗載入後事件 this.ucQueryWindow.AfterPageLoad += new Action<object, WFT.lib.SystemEventArgs>(this.CustomAfterQueryWindowPageLoad); // 查詢視窗載入完成前事件 this.ucQueryWindow.BeforePageLoadComplete += new Action<object, WFT.lib.SystemEventArgs>(this.CustomBeforeQueryWindowPageLoadComplete); // 查詢視窗載入完成後事件 this.ucQueryWindow.AfterPageLoadComplete += new Action<object, WFT.lib.SystemEventArgs>(this.CustomAfterQueryWindowPageLoadComplete); // 取得 REF_ID 結果事件(開窗元件) this.ucQueryWindow.GetRef_ID += this.CustomGetRef_ID; }       #endregion

#region 表單狀態 ///        /// 設定表單狀態(Before) /// 		public override void SetBeforeFormState(object sender, SystemEventArgs e)       { if (BeforeFormState != null) {				BeforeFormState(sender, e); }       }

///        /// 設定表單狀態(After) /// 		public override void SetAfterFormState(object sender, SystemEventArgs e)       {

if (AfterFormState != null) {				AfterFormState(sender, e); }       }		#endregion

///        /// 取得下拉清單資料 /// 		/// <param name="colName">欄位名稱 public DataTable GetListData(string colName) {			string sql = String.Empty; try {				DataTable dt = null; DataRow drN = null; List<string[]> values = null; switch(colName) {					default: break; }				drN = dt.NewRow; drN["CODE"] = String.Empty; drN["DESC"] = "請選擇"; dt.Rows.InsertAt(drN, 0); return dt; }			catch(Exception ex) {				Logger.log(String.Format("{0} {1} {2} error:{3}", getSchemaTableName, "Mtn", "GetListData", ex.Message)); }			return null; }

#region Callback 函式 ///        /// 引發 Callback 事件處理 ///        /// 代號 public void RaiseCallbackEvent(string value) {           string[] values = value.Split('|'); string action = String.Empty; string colName = String.Empty; string srcValue = String.Empty;

if (values.Length > 1) {               action = values[0]; colName = values[1]; srcValue = values[2]; switch (action) {					case "ref_type": string sql = String.Empty; switch(colName) {							case "BAN": if (this.GetRef_ID != null) {									int level = 0; sql = this.GetRef_ID("IDN_BAN", ref level, srcValue, null, null, null); }

break; default: break; }

try {							if (!String.IsNullOrEmpty(sql)) {								//將來源字串轉換成 %% 形式 if (sql.Replace(" ","").Contains("like?")) {									srcValue = String.Format("{0}%", srcValue); }

DataTable dt = DaoDbCmd.query(sql, new String[] { srcValue }); if (dt.Rows.Count > 0) {									//回傳第1個欄位的值 string colValue = dt.Rows[0][0].ToString; if (!String.IsNullOrEmpty(colValue)) {                                       if (!String.IsNullOrEmpty(colValue.Trim)) {                                           RefValue = colValue.Trim; }                                       else {                                           RefValue = " "; }                                   }                                    else {                                       RefValue = String.Empty; }								}							}							else {								RefValue = "SQLNotExist"; }						}						catch(Exception ex) {							if (ex.Message.Trim.IndexOf("SQLSTATE=42704") != -1) {								RefValue = "SystemTableNotExist"; }							Logger.log(String.Format("{0} {1} {2} error:{3}", getSchemaTableName, "Mtn", "RaiseCallbackEvent", ex.Message)); }						break; case "get_value": if (GetGet_Value != null) {							RefValue = GetGet_Value(colName, srcValue); if (!String.IsNullOrEmpty(RefValue) && RefValue.ToUpper.StartsWith("@") && !RefValue.TrimEnd.ToUpper.Equals("@")) {								//記錄未處理 Logger.log(String.Format("{0} {1} {2} error:{3}", getSchemaTableName, "Mtn", "RaiseCallbackEvent", String.Format("{0} 欄位的 GET_VALUE 屬性:{1} 尚未處理. ", colName, RefValue))); RefValue = null; }						}						break; default: break; }			}       }

//       ///         /// 回傳 Callback 結果 ///        ///          public string GetCallbackResult {           return RefValue.ShowDBString; }       #endregion

///        /// 取得 Schema Table Name ///        /// Schema 表格名稱 public override String getSchemaTableName {           return "FT_ANNUAL_PAYMENT"; }

protected void btnInsert_Click(object sender, EventArgs e)       { showMessage("TEST OK2"); // DataTable dtQuery = null; string sql1 = String.Empty; string sql2 = String.Empty; try {               DaoManager.getConnection.Open; DaoManager.getConnection.BeginTransaction;

///0.0 chinchi 20190226 ///            //   sql = String.Format(@"select YM,INERT_TIME,MAINT_ID,BATCHNO from  JCIC.WA900T where class='A' and DELETE_FLAG='N' "); // sql2 = String.Format(@"select YM,INERT_TIME,MAINT_ID,BATCHNO from JCIC.WA900T where class='A' and DELETE_FLAG='N' "); sql1 = "INSERT INTO \"JCIC\".\"FT_QYFEE_CFG\"" + "\r\n" + "	(\"AMT_ABOVE\", \"AMT_BELOW\", \"BAN\", \"BILLING_SIGN\", \"BILLING_YMS\", \"C_NAME\", \"DIS_AMT_ABOVE\", \"DIS_AMT_BELOW\", \"INS_TIME\", \"JCIC_CODE\", \"MAINT_ID\", \"NOTES\", \"THRESHOLD\", \"UPD_TIME\")" + "\r\n" + "	VALUES (9, 91, 'AYCWJFDROC', 'H', 'VDFAG', 'OCQDPIDEMK', 43, 84, '2009-09-05-22.48.44', 'ALFTH', 'AMLKUFHGBI', 'FPNJ', 7, '2003-11-25 01:45:47')";

sql2 = "INSERT INTO \"JCIC\".\"FT_QYFEE_CFG\"" + "\r\n" + "	(\"AMT_ABOVE\", \"AMT_BELOW\", \"BAN\", \"BILLING_SIGN\", \"BILLING_YMS\", \"C_NAME\", \"DIS_AMT_ABOVE\", \"DIS_AMT_BELOW\", \"INS_TIME\", \"JCIC_CODE\", \"MAINT_ID\", \"NOTES\", \"THRESHOLD\", \"UPD_TIME\")" + "\r\n" + "	VALUES (9, 91, 'AYCWJFDROD', 'H', 'VDFAG', 'OCQDPIDEMK', 43, 84, '2009-09-05-22.48.44', 'ALFTH', 'AMLKUFHGBI', 'FPNJ', 7, '2003-11-25 01:45:47')"; try {                   //DaoDbCmd.execute(sql); //   dtQuery = DaoDbCmd.query(sql); DaoDbCmd.execute(sql1); DaoDbCmd.execute(sql2);

}               catch (Exception ex) {                   throw ex; }               DaoManager.getConnection.Commit; }           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {          //     showMessage(dtQuery.Rows[0][0].ToString + " 資料更新時間:" + dtQuery.Rows[0][1].ToString + " 更新者" + dtQuery.Rows[0][2].ToString + " 作業批次號" + dtQuery.Rows[0][3].ToString); //TextBox1.Text = dtQuery.Rows[0][0].ToString + " 資料更新時間:" + dtQuery.Rows[0][1].ToString + " 更新者" + dtQuery.Rows[0][2].ToString + " 作業批次號" + dtQuery.Rows[0][3].ToString; DaoManager.closeConnection; }           showMessage("TEST OK2"); //System.Environment.Exit(System.Environment.ExitCode); //HttpResponse.Equals }

protected void Button1_Click(object sender, EventArgs e)       { showMessage("TEST OK1"); // DataTable dtQuery = null; string sql1 = String.Empty; string sql2 = String.Empty; try {               DaoManager.getConnection.Open; DaoManager.getConnection.BeginTransaction;

///0.0 chinchi 20190226 ///               //   sql = String.Format(@"select YM,INERT_TIME,MAINT_ID,BATCHNO from  JCIC.WA900T where class='A' and DELETE_FLAG='N' "); // sql2 = String.Format(@"select YM,INERT_TIME,MAINT_ID,BATCHNO from JCIC.WA900T where class='A' and DELETE_FLAG='N' "); sql1 = "INSERT INTO \"JCIC\".\"FT_ANNUAL_PAYMENT\""+"\r\n"+ "	(\"ACCOUNTING_DATE\", \"ANNUAL_FEE\", \"BAN\", \"C_NAME\", \"C_YEAR\", \"EFFECTIVE_DATE\", \"INS_TIME\", \"JCIC_CODE\", \"MAINT_ID\", \"NOTES\", \"UPD_TIME\")"+"\r\n"+ "	VALUES ('FMMRPMI', 9, 'ADHNJCGMEJ', 'CSMIGKRMVT', 'AJG', 'XEFKXLG', '2021-06-16-10.06.55', 'ASWOE', 'GYRCTAIIBM', 'XSOGTDMBYD', '2003-06-27-16.04.32');";

sql2 = "INSERT INTO \"JCIC\".\"FT_QYFEE_CFG\"" + "\r\n" + "	(\"AMT_ABOVE\", \"AMT_BELOW\", \"BAN\", \"BILLING_SIGN\", \"BILLING_YMS\", \"C_NAME\", \"DIS_AMT_ABOVE\", \"DIS_AMT_BELOW\", \"INS_TIME\", \"JCIC_CODE\", \"MAINT_ID\", \"NOTES\", \"THRESHOLD\", \"UPD_TIME\")" + "\r\n" + "	VALUES (9, 91, 'AYCWJFDROD', 'H', 'VDFAG', 'OCQDPIDEMK', 43, 84, '2009-09-05-22.48.44', 'ALFTH', 'AMLKUFHGBI', 'FPNJ', 7, '2003-11-25 01:45:47')"; try {                   //DaoDbCmd.execute(sql); //   dtQuery = DaoDbCmd.query(sql); DaoDbCmd.execute(sql1); DaoDbCmd.execute(sql2);

}               catch (Exception ex) {                   throw ex; }               DaoManager.getConnection.Commit; }           catch (Exception exp) {               Logger.log("SQLException", exp.Message); DaoManager.getConnection.Rollback; throw new Exception("更新資料錯誤"); }           finally {               //     showMessage(dtQuery.Rows[0][0].ToString + " 資料更新時間:" + dtQuery.Rows[0][1].ToString + " 更新者" + dtQuery.Rows[0][2].ToString + " 作業批次號" + dtQuery.Rows[0][3].ToString); //TextBox1.Text = dtQuery.Rows[0][0].ToString + " 資料更新時間:" + dtQuery.Rows[0][1].ToString + " 更新者" + dtQuery.Rows[0][2].ToString + " 作業批次號" + dtQuery.Rows[0][3].ToString; DaoManager.closeConnection; }           showMessage("TEST OK3"); //System.Environment.Exit(System.Environment.ExitCode); //HttpResponse.Equals }   } }

業者代碼	中文名稱	統編	計費起始年月	計費啟用註記	計費門檻值	"小於或相等門檻值的 單筆資料金額"	"小於或相等門檻值的 單筆資料年費扣抵金額"	"大於門檻值的 單筆資料金額"	"大於門檻值的 單筆資料年費扣抵金額"	備註	修改者	新增日期時間	修改日期時間 JCIC_CODE	C_NAME	BAN	BILLING_YMS	BILLING_SIGN	THRESHOLD	AMT_BELOW	DIS_AMT_BELOW	AMT_ABOVE	DIS_AMT_ABOVE	NOTES	MAINT_ID	INS_TIME	UPD_TIME JCIC123	測試公司1	B234	11105	Y	100	30	10	40	0	這是備註	TESTER	SYSDATE	SYSDATE 寫入資料時間 (1)使用者建立年費資料並儲存後；隨即建立一筆資料 注意事項 說明 計費門檻值 = 年費．年費繳交金額 / 10，四捨五入至個位數