using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using CoreSavingLibrary; using System.Data; using DataLibrary; namespace Saving.Applications.shrlon { public partial class CmdocumentControl : PageWebSheet, WebSheet { public void InitJsPostBack() { } public void WebSheetLoadBegin() { if(!IsPostBack){ setGridview(); } } private void setGridview() { try { string sql = "select * from cmdocumentcontrol where (document_code='SLSLIPPAYIN_2' or document_code='SLSLIPPAYIN_3' or document_code='SLSLIPPAYIN_4' or document_code like 'CONTNO%' ) and coop_id={0} order by document_code asc"; sql = WebUtil.SQLFormat(sql,state.SsCoopId); Sdt d = WebUtil.QuerySdt(sql); DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[5] { new DataColumn("document_code", typeof(string)), new DataColumn("document_name",typeof(string)), new DataColumn("last_documentno",typeof(Int32)), new DataColumn("document_prefix",typeof(string)), new DataColumn("system_code",typeof(string)), }); while (d.Next()) { dt.Rows.Add(d.GetString("document_code"), d.GetString("document_name"), d.GetInt32("last_documentno"), d.GetString("document_prefix"),d.GetString("system_code")); } GridView1.DataSource = dt; GridView1.DataBind(); } catch { } } public void CheckJsPostBack(string eventArg) { } public void SaveWebSheet() { } public void WebSheetLoadEnd() { } protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; setGridview(); } protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { try { GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex]; Label id = (Label)row.FindControl("lbl_ID") as Label; TextBox name = (TextBox)row.FindControl("txt_Name"); TextBox documentno = (TextBox)row.FindControl("txt_documentno") as TextBox; TextBox prefix = (TextBox)row.FindControl("txt_prefix") as TextBox; string sql = "update cmdocumentcontrol set document_name =" + "'" + name.Text + "'" + ",last_documentno=" + "'" + documentno.Text + "'" + ", document_prefix=" + "'" + prefix.Text + "'" + " where coop_id = " + "'" + state.SsCoopControl + "'" + " and document_code =" + "'" + id.Text + "'" + " and system_code='LON'"; sql = WebUtil.SQLFormat(sql); Sdt d = WebUtil.QuerySdt(sql); } catch { } GridView1.EditIndex = -1; setGridview(); } protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; setGridview(); } protected void Update_Click(object sender, EventArgs e) { String loan = "", loan2 = ""; try { // 1 เริ่มทำ ท่อนนี้ก่อน //Sdt master = WebUtil.QuerySdt(" select loancontract_no from lncontmaster order by loancontract_no asc "); //while (master.Next()) //{ // decimal prin = 0; // Sdt st = WebUtil.QuerySdt("select * from lncontstatement2 where loancontract_no='" + master.GetString("loancontract_no") + "' order by loancontract_no , seq_no "); // while (st.Next()) // { // if (st.GetString("loanitemtype_code") == "LRC") // { // prin = st.GetDecimal("principal_balance"); // } // else // { // if (st.GetDecimal("principal_payment") > 0 && st.GetString("loanitemtype_code") == "LPX") // { // prin = prin - st.GetDecimal("principal_payment"); // } // WebUtil.QuerySdt(" update lncontstatement2 set principal_balance = " + prin + " where loancontract_no='" + master.GetString("loancontract_no") + "' and seq_no = " + st.GetInt32("seq_no") + " "); // } // } //} /////end ///2. กรณี ที่ seq_no =1 ไม่ใช่ LRC /// //Sdt dg = WebUtil.QuerySdt("select l.principal_balance as prn, lncontstatement.* from lncontmaster l, lncontstatement where l.loancontract_no= lncontstatement.loancontract_no and seq_no =1 and loanitemtype_code <> 'LRC'"); //while (dg.Next()) //{ // decimal prn = 0; // Sdt dt = WebUtil.QuerySdt("select * from lncontstatement where loancontract_no ='" + dg.GetString("loancontract_no") + "' order by seq_no desc "); // while (dt.Next()) // { // if (dt.GetString("loanitemtype_code") == "LPX" || dt.GetString("loanitemtype_code") == "LTA") // { // WebUtil.QuerySdt("update lncontstatement set principal_balance = " + prn + " where loancontract_no ='" + dg.GetString("loancontract_no") + "' and seq_no = " + dt.GetInt32("seq_no") + " "); // prn = prn + dt.GetDecimal("principal_payment"); // } // else // { // WebUtil.QuerySdt("update lncontstatement set principal_balance = " + prn + ",principal_payment=" + prn + " where loancontract_no ='" + dg.GetString("loancontract_no") + "' and seq_no = " + dt.GetInt32("seq_no") + " "); // // prn = prn + dt.GetDecimal("principal_payment"); // } // } //} //// //end string loans =""; //statement // CREATE TABLE "LNCONTSTATEMENT2" ("COOP_ID" CHAR(6) NOT NULL, "LOANCONTRACT_NO" VARCHAR2(15) NOT NULL, "SEQ_NO" NUMBER(5,0) NOT NULL, "LOANITEMTYPE_CODE" CHAR(3), "SLIP_DATE" DATE, "OPERATE_DATE" DATE, "ACCOUNT_DATE" DATE, "REF_DOCNO" VARCHAR2(15), "PERIOD" NUMBER(5,0), "PRINCIPAL_PAYMENT" NUMBER(15,2), "INTEREST_PAYMENT" NUMBER(15,2), "PRINCIPAL_BALANCE" NUMBER(15,2), "PRNCALINT_AMT" NUMBER(15,2), "CALINT_FROM" DATE, "CALINT_TO" DATE, "BFINTARREAR_AMT" NUMBER(9,2), "INTEREST_PERIOD" NUMBER(9,2), "INTEREST_ARREAR" NUMBER(9,2), "INTEREST_RETURN" NUMBER(9,2), "MONEYTYPE_CODE" CHAR(3), "ITEM_STATUS" NUMBER(2,0), "ENTRY_ID" VARCHAR2(32), "ENTRY_DATE" DATE, "ENTRY_BYCOOPID" CHAR(6), "BFCONTSTATUS_DESC" VARCHAR2(6), "BFPERIOD_ARREAR" NUMBER(5,0), "PRINCIPAL_ARREAR" NUMBER(15,2), "PRN_BAL" NUMBER(15,2) DEFAULT '0', "FINE_AMT" NUMBER(15,2) DEFAULT 0, "REF_SLIPNO" VARCHAR2(15), "BFINTEREST_ARREAR" NUMBER(9,2), "BFINTRETURN_AMT" NUMBER(9,2) DEFAULT 0, "REMARK" VARCHAR2(200), "TEMP_DATE" DATE, "INTACCUM_DATE" DATE, "INTCOMPARR_BAL" NUMBER(15,2) DEFAULT 0, "CALAVG_STATUS" NUMBER(2,0) DEFAULT 0, "FINEYEAR_ARREAR" NUMBER(9,2), "FINE_ARREAR" NUMBER(9,2), "BFPRINCIPAL_ARREAR" NUMBER(15,2), "BFFINEYEAR_AMT" NUMBER(15,2), "BFINTYEARARR_AMT" NUMBER(15,2), "INTARR_PAYMENT" NUMBER(15,2), "FINEARR_PAYMENT" NUMBER(15,2), "INT_COMPENSATE" NUMBER(15,2), "FINE_PERIOD" NUMBER(15,2), "FINE_PAYMENT" NUMBER(15,2), "FINE_RETURN" NUMBER(15,2), "BFFINE_ARREAR" NUMBER(15,2), "PRINCIPALPAY_PERIOD" NUMBER(10,0), "INTYEAR_COMPENSATE" NUMBER(9,2), "BFINT_PREIOD" NUMBER(10,2), "BFFINE_PREIOD" NUMBER(10,2), "PRTCARD_FLAG" NUMBER(1,0), "PRTBOOK_FLAG" NUMBER(1,0)) ; //ALTER TABLE "LNCONTSTATEMENT2" ADD ( CONSTRAINT PK_E3323443 PRIMARY KEY ( "LOANCONTRACT_NO", "SEQ_NO", "COOP_ID" )) ; //CREATE INDEX "LNCONTSTATEMENTSLIPDATE2" ON "LNCONTSTATEMENT2" ("SLIP_DATE" ) ; // 3.เริ่ม group statement // try // { // int i = 1; // WebUtil.QuerySdt("delete from lncontstatement2"); // Sdt stm = WebUtil.QuerySdt(@"select //COOP_ID , LOANCONTRACT_NO ,MIN(seq_no)as seq_no , LOANITEMTYPE_CODE , // SLIP_DATE , OPERATE_DATE , ACCOUNT_DATE , REF_DOCNO , PERIOD , sum(principal_payment)as principal_payment , //sum(INTEREST_PAYMENT)as INTEREST_PAYMENT ,max( PRINCIPAL_BALANCE)as PRINCIPAL_BALANCE ,sum( PRNCALINT_AMT)as PRNCALINT_AMT , CALINT_FROM , // CALINT_TO ,max( BFINTARREAR_AMT)as BFINTARREAR_AMT ,sum( INTEREST_PERIOD)as INTEREST_PERIOD , sum( INTEREST_ARREAR)as INTEREST_ARREAR , //sum( INTEREST_RETURN)as INTEREST_RETURN , MONEYTYPE_CODE , 1 as item_status , ENTRY_ID , ENTRY_DATE , // ENTRY_BYCOOPID , BFCONTSTATUS_DESC , max( BFPERIOD_ARREAR)as BFPERIOD_ARREAR ,max( PRINCIPAL_ARREAR)as PRINCIPAL_ARREAR , sum( PRN_BAL)as PRN_BAL , //sum( FINE_AMT)as FINE_AMT , REF_SLIPNO , // max( BFINTEREST_ARREAR)as BFINTEREST_ARREAR , sum( BFINTRETURN_AMT)as BFINTRETURN_AMT , REMARK, TEMP_DATE , INTACCUM_DATE , sum( INTCOMPARR_BAL)as INTCOMPARR_BAL , // CALAVG_STATUS , sum( FINEYEAR_ARREAR)as FINEYEAR_ARREAR ,sum( FINE_ARREAR)as FINE_ARREAR ,max( BFPRINCIPAL_ARREAR)as BFPRINCIPAL_ARREAR , // max( BFFINEYEAR_AMT)as BFFINEYEAR_AMT , max( BFINTYEARARR_AMT) as BFINTYEARARR_AMT , // sum( INTARR_PAYMENT)as INTARR_PAYMENT , sum( FINEARR_PAYMENT)as FINEARR_PAYMENT , sum( INT_COMPENSATE)as INT_COMPENSATE , sum( FINE_PERIOD)as FINE_PERIOD //,sum( FINE_PAYMENT)as FINE_PAYMENT , sum( FINE_RETURN)as FINE_RETURN ,sum( BFFINE_ARREAR)as BFFINE_ARREAR , //sum( PRINCIPALPAY_PERIOD) , INTYEAR_COMPENSATE ,sum(BFINT_PREIOD)as BFINT_PREIOD , sum(BFFINE_PREIOD)as BFFINE_PREIOD, // max(PRTCARD_FLAG)as PRTCARD_FLAG ,max( PRTBOOK_FLAG)as PRTBOOK_FLAG from lncontstatement // group by COOP_ID , LOANCONTRACT_NO , LOANITEMTYPE_CODE , // SLIP_DATE , OPERATE_DATE , ACCOUNT_DATE , REF_DOCNO , PERIOD // , CALINT_FROM , // CALINT_TO , MONEYTYPE_CODE , 1 , ENTRY_ID , ENTRY_DATE , // ENTRY_BYCOOPID , BFCONTSTATUS_DESC , REF_SLIPNO , // REMARK, TEMP_DATE , INTACCUM_DATE , // CALAVG_STATUS , 0 , INTYEAR_COMPENSATE order by LOANCONTRACT_NO,seq_no,slip_date,seq_no"); // TextBox1.Text = ""; // while (stm.Next()) // { // loans = stm.GetString("LOANCONTRACT_NO"); // String insert = @" insert into lncontstatement2(COOP_ID , LOANCONTRACT_NO , SEQ_NO , LOANITEMTYPE_CODE , SLIP_DATE , OPERATE_DATE , ACCOUNT_DATE , REF_DOCNO //, PERIOD , PRINCIPAL_PAYMENT , INTEREST_PAYMENT , PRINCIPAL_BALANCE , PRNCALINT_AMT , CALINT_FROM , CALINT_TO //, BFINTARREAR_AMT , INTEREST_PERIOD , INTEREST_ARREAR , INTEREST_RETURN , MONEYTYPE_CODE , ITEM_STATUS //, ENTRY_ID , ENTRY_DATE , ENTRY_BYCOOPID , BFCONTSTATUS_DESC , BFPERIOD_ARREAR , PRINCIPAL_ARREAR , PRN_BAL //, FINE_AMT , REF_SLIPNO , BFINTEREST_ARREAR , BFINTRETURN_AMT , REMARK , TEMP_DATE , INTACCUM_DATE , INTCOMPARR_BAL // , CALAVG_STATUS , FINEYEAR_ARREAR , FINE_ARREAR , BFPRINCIPAL_ARREAR , BFFINEYEAR_AMT , BFINTYEARARR_AMT , INTARR_PAYMENT // , FINEARR_PAYMENT , INT_COMPENSATE , FINE_PERIOD , FINE_PAYMENT , FINE_RETURN , BFFINE_ARREAR , PRINCIPALPAY_PERIOD //, INTYEAR_COMPENSATE , BFINT_PREIOD , BFFINE_PREIOD , PRTCARD_FLAG , PRTBOOK_FLAG ) values( " + // " '000401' , '" + stm.GetString("LOANCONTRACT_NO") + "' , " + stm.GetDecimal("seq_no") + " , '" + stm.GetString("LOANITEMTYPE_CODE") + "' , {0} , {1} , {2} , '" + stm.GetString("REF_DOCNO") + "' " + // ", " + stm.GetDecimal("PERIOD") + " , " + stm.GetDecimal("PRINCIPAL_PAYMENT") + " , " + stm.GetDecimal("INTEREST_PAYMENT") + " ," + stm.GetDecimal("PRINCIPAL_BALANCE") + " , " + stm.GetDecimal("PRNCALINT_AMT") + " , {3} , {4} " + // ", " + stm.GetDecimal("BFINTARREAR_AMT") + " , " + stm.GetDecimal("INTEREST_PERIOD") + " , " + stm.GetDecimal("INTEREST_ARREAR") + " , " + stm.GetDecimal("INTEREST_RETURN") + " , '" + stm.GetString("MONEYTYPE_CODE") + "' , 1 " + // ", 'ISC' , {5} , '000401' , '" + stm.GetString("BFCONTSTATUS_DESC") + "' , " + stm.GetDecimal("BFPERIOD_ARREAR") + " , " + stm.GetDecimal("PRINCIPAL_ARREAR") + " , " + stm.GetDecimal("PRN_BAL") + " " + // ", " + stm.GetDecimal("FINE_AMT") + " , '" + stm.GetString(" REF_SLIPNO") + "' , " + stm.GetDecimal("BFINTEREST_ARREAR") + " , " + stm.GetDecimal("BFINTRETURN_AMT") + " ,'" + stm.GetString("REMARK") + "' , {6} , {7} , 0 " + // ", 0 , " + stm.GetDecimal("FINEYEAR_ARREAR") + " , " + stm.GetDecimal("FINE_ARREAR") + " ," + stm.GetDecimal("BFPRINCIPAL_ARREAR") + " , " + stm.GetDecimal("BFFINEYEAR_AMT") + " , " + stm.GetDecimal("BFINTYEARARR_AMT") + " ," + stm.GetDecimal("INTARR_PAYMENT") + " " + // ", " + stm.GetDecimal("FINEARR_PAYMENT") + " , 0 , " + stm.GetDecimal("FINE_PERIOD") + " ," + stm.GetDecimal("FINE_PAYMENT") + " , " + stm.GetDecimal("FINE_RETURN") + " ," + stm.GetDecimal("BFFINE_ARREAR") + " , " + stm.GetDecimal("PRINCIPALPAY_PERIOD") + " " + // ", 0 ," + stm.GetDecimal("BFINT_PREIOD") + " ," + stm.GetDecimal("BFFINE_PREIOD") + " , 0 , 0 ) "; // insert = WebUtil.SQLFormat(insert, stm.GetDate("SLIP_DATE"), stm.GetDate("OPERATE_DATE"), stm.GetDate("ACCOUNT_DATE"), stm.GetDate("CALINT_FROM") // , stm.GetDate("CALINT_TO"), stm.GetDate("ENTRY_DATE"), stm.GetDate("TEMP_DATE"), stm.GetDate("INTACCUM_DATE")); // WebUtil.QuerySdt(insert); // // TextBox1.Text = stm.GetString("LOANCONTRACT_NO") + " จาก " + stm.GetRowCount().ToString() + " ถึง " + stm.GetRowIndex().ToString(); // } // // TextBox1.Text = stm.GetRowCount().ToString()+" "+loans; // // stm.Clear(); // } // catch (Exception ex) { // LtServerMessage.Text = ex.ToString(); // } //end //4. ทำการย้าย ข้อมูลไป lncontstatement // WebUtil.QuerySdt("delete from lncontstatement"); //WebUtil.QuerySdt("insert into lncontstatement select * from lncontstatement2"); // end //5. ทำการ gen principalpay_period //Sdt master = WebUtil.QuerySdt(" select loancontract_no from lncontmaster order by loancontract_no asc "); //while (master.Next()) //{ // Decimal period_payment = 0; // DateTime expireperiod_date; // Sdt period = WebUtil.QuerySdt("select period_payment,expireperiod_date from lncontperiodpaydet where loancontract_no = '" + master.GetString("loancontract_no") + "' order by period asc"); // while (period.Next()) // { // period_payment = period.GetDecimal("period_payment"); // expireperiod_date = period.GetDate("expireperiod_date"); // String statement = "select principal_payment,slip_date,bfprincipal_arrear,seq_no from lncontstatement where loancontract_no = {0} and slip_date between {1} and {2} and loanitemtype_code = 'LPX' order by seq_no asc "; // statement = WebUtil.SQLFormat(statement, master.GetString("loancontract_no"), expireperiod_date.AddYears(-1).AddDays(1), expireperiod_date); // Sdt stm = WebUtil.QuerySdt(statement); // while (stm.Next()) // { // WebUtil.QuerySdt("update lncontstatement set principalpay_period = " + period_payment + " where loancontract_no = '" + master.GetString("loancontract_no") + "' and seq_no = " + stm.GetInt32("seq_no") + " "); // period_payment = period_payment - stm.GetDecimal("principal_payment"); // } // } //} //end //6.update ยอด ดบ/ ค่าปรับ ใหม่ //update lncontstatement set interest_period = interest_period-intarr_payment where intarr_payment>0;commit; //update lncontstatement set fine_period = fine_period-finearr_payment where finearr_payment>0;commit; Sdt master = WebUtil.QuerySdt(" select loancontract_no from lncontstatement group by loancontract_no order by loancontract_no asc "); while (master.Next()) { decimal prin = 0,pay=0; Sdt st = WebUtil.QuerySdt("select principal_balance,loancontract_no,principal_payment,seq_no from lncontstatement where loancontract_no='" + master.GetString("loancontract_no") + "' and loanitemtype_code='LPX' order by seq_no desc "); while (st.Next()) { prin += st.GetDecimal("principal_balance"); WebUtil.QuerySdt(" update lncontstatement set principal_balance = " + prin + " where loancontract_no='" + master.GetString("loancontract_no") + "' and seq_no = " + st.GetInt32("seq_no") + " "); pay = st.GetDecimal("principal_payment"); prin += pay; } } /////end // //update lncontmaster a //set a.last_stm_no = ( select max(b.seq_no) from lncontstatement b //where a.loancontract_no = b.loancontract_no ); //update lncontstatement set principal_balance = ( select principal_balance from lncontmaster where lncontmaster.last_stm_no = lncontstatement.seq_no and //lncontmaster.loancontract_no = lncontstatement.loancontract_no );commit; //select lncontmaster.loancontract_no,lncontmaster.principal_balance , lncontstatement.principal_balance from lncontmaster , lncontstatement where lncontmaster.loancontract_no = lncontstatement.loancontract_no and lncontmaster.last_stm_no = lncontstatement.seq_no and // lncontmaster.principal_balance <> lncontstatement.principal_balance; LtServerMessage.Text = "เรียบร้อย"; // string loancontract_no = ""; // string seq_no = ""; // DateTime slip_date = state.SsWorkDate; // string sql = "select * from lncontmaster where coop_id={0} and (loanapprove_date between to_date('31/03/2016','dd/MM/yyyy') and to_date('31/03/2017','dd/MM/yyyy'))"; // sql = WebUtil.SQLFormat(sql, state.SsCoopId); // Sdt d = WebUtil.QuerySdt(sql); // while (d.Next()) // { // loancontract_no = d.GetString("loancontract_no"); // string statement = "select max(seq_no) as seq_no from lncontstatement where loancontract_no={0} and item_status=1 and coop_id={1}"; // statement = WebUtil.SQLFormat(statement, loancontract_no, state.SsCoopId); // Sdt d2 = WebUtil.QuerySdt(statement); // if (d2.Next()) // { // seq_no = d2.GetString("seq_no"); // } // string select_slip_date = "select slip_date from lncontstatement where loancontract_no={0} and coop_id={1} and seq_no={2}"; // select_slip_date = WebUtil.SQLFormat(select_slip_date, loancontract_no, state.SsCoopId, seq_no); // Sdt d3 = WebUtil.QuerySdt(select_slip_date); // if (d3.Next()) // { // slip_date = d3.GetDate("slip_date"); // } // string update = "update lncontmaster set lastpayment_date={0} where loancontract_no={1} and coop_id={2}"; // update = WebUtil.SQLFormat(update, slip_date, loancontract_no, state.SsCoopId); // WebUtil.QuerySdt(update); // } } catch (Exception ex) { LtServerMessage.Text = ex.ToString(); } } protected void Update_lncontstatement_Click(object sender, EventArgs e) { try { string lncontmaster = @"WITH query1 AS ( select lncontmaster.loancontract_no,sum(lncontperiodpaydet.period_payment - prinpaid_amt) as a from lncontmaster inner join lncontperiodpaydet on lncontmaster.loancontract_no = lncontperiodpaydet.loancontract_no where principal_balance = 0 and withdrawable_amt =0 group by lncontmaster.loancontract_no )SELECT query1.loancontract_no,a,lncontmaster.principal_balance FROM query1 inner join lncontmaster on lncontmaster.loancontract_no = query1.loancontract_no where a <> principal_balance order by lncontmaster.principal_balance"; lncontmaster = WebUtil.SQLFormat(lncontmaster); Sdt d2 = WebUtil.QuerySdt(lncontmaster); while (d2.Next()) { // update lncontperiodpaydet period_payment,principal_arrear,prinpaid_amt string lncontperiod = @"update lncontperiodpaydet set principal_arrear = 0,prinpaid_amt=period_payment where loancontract_no = {0} and coop_id={1}"; lncontperiod = WebUtil.SQLFormat(lncontperiod, d2.GetString("loancontract_no"), state.SsCoopId); WebUtil.QuerySdt(lncontperiod); } } catch { } } } }