using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using CoreSavingLibrary; using DataLibrary; ////////////////////////////////////////// using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; using System.Data.OracleClient; using System.Globalization; ///////////////////////////////////////// using System.IO; using System.Text; using OfficeOpenXml; ///////////////////////////////////////// namespace Saving.Applications.ap_deposit.ws_yrreq_excal_ctrl { public partial class ws_yrreq_excal : PageWebSheet, WebSheet { String pbl = "dp_imptxt_reqdeposit.pbl"; [JsPostBack] public string GetValues { get; set; } [JsPostBack] public string GetDetails { get; set; } [JsPostBack] public string ChangeAcc { get; set; } [JsPostBack] public string DeleteRowDetail { get; set; } [JsPostBack] public string JsAddDetail { get; set; } public void InitJsPostBack() { dsMain.InitStatement(this); } public void WebSheetLoadBegin() { if (!IsPostBack) { dsMain.Ddtofrom_accid(); dsMain.DATA[0].entry_date = state.SsWorkDate; dsMain.DATA[0].recvtype_id = "DTS"; dsMain.DATA[0].tofrom_accid = "11010001"; dsMain.DATA[0].entry_item=1; DwList.InsertRow(0); DwUtil.RetrieveDDDW(DwList, "tofrom_accid", pbl, null); DwUtil.RetrieveDataWindow(DwList, pbl, null, state.SsWorkDate, state.SsCoopControl, dsMain.DATA[0].entry_item); } else { this.RestoreContextDw(DwList); } } public void CheckJsPostBack(string eventArg) { if (eventArg == GetValues) { if (txtInput.FileName == null || txtInput.FileName == "") { DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, state.SsCoopControl, dsMain.DATA[0].entry_item); DwUtil.RetrieveDDDW(DwList, "tofrom_accid", pbl, null); } else { string tofrom_accid = "", cashtype_code = "TRN", itemtype_code = ""; decimal entry_item = 0; tofrom_accid = dsMain.DATA[0].tofrom_accid; entry_item = dsMain.DATA[0].entry_item; itemtype_code = dsMain.DATA[0].recvtype_id; if (itemtype_code == "") { itemtype_code = "DTS"; } string error = " ---> "; if (txtInput.HasFile) { if (System.IO.Path.GetExtension(txtInput.FileName) == ".xlsx") { ExecuteDataSource exe = new ExecuteDataSource(this); string into = Server.MapPath("~/WSRPDF/") + DateTime.Now.ToString("ddMMyyyyHHmmss") + "_" + txtInput.FileName; txtInput.PostedFile.SaveAs(into); FileInfo excel = new FileInfo(into); using (var package = new ExcelPackage(excel)) { var workbook = package.Workbook; var worksheet = workbook.Worksheets.First(); for (int i = 2; i <= worksheet.Dimension.End.Row; i++) { try { Int32 seq = 0; Sdt d = WebUtil.QuerySdt("select max(seq_no) as seq_no from DPDEPTADVANCEDW where deptaccount_no = '" + worksheet.Cells[i, 1].Text.ToString().Trim() + "'"); if (d.Next()) { seq = d.GetInt32("seq_no") + 1; } string sql = @" insert into DPDEPTADVANCEDW (coop_id,dw_date,deptaccount_no,seq_no,entry_date ,entry_time,entry_id,dw_amount,dw_flag,post_status, machine_id,item_status,tofrom_accid,cashtype_code, itemtype_code,recv_period ) values( {0},{1},{2} ,{3},{11} ,{11},{4},{5},{8},0, {6},1,{7},{9}, {10},0 )"; sql = WebUtil.SQLFormat(sql, state.SsCoopControl, dsMain.DATA[0].entry_date, worksheet.Cells[i, 1].Text.ToString().Trim(), seq, state.SsUsername, worksheet.Cells[i, 2].Text.ToString().Trim(), state.SsClientIp, tofrom_accid, entry_item, cashtype_code, itemtype_code, DateTime.Today); WebUtil.QuerySdt(sql); } catch { error += worksheet.Cells[i, 1].Text.ToString().Trim() + ","; } } } LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกข้อมูลเสร็จสิ้น" + error); DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, state.SsCoopControl, dsMain.DATA[0].entry_item); } } else { LtServerMessage.Text = WebUtil.ErrorMessage("ต้องเป็น ไฟล์ .xlsx เท่านั้น"); } } } else if (eventArg == "JsAddDetail") { try { DwList.InsertRow(Convert.ToInt32(rowcount.Value) + 1); } catch { } } else if (eventArg == "GetDetails") { try { string ls_deptno = "", ls_accbranch = "", ls_tofromaccid = "", ls_memno = "", ls_depttype = "", ls_depttype_groupcode = ""; string ls_timedueprefer = "", ls_ref_docno = "", ls_referapp = "", ls_deptitem = "", ls_cashtype = "", ls_referslipno = ""; decimal ldc_pmon = 0, ldc_balance = 0, ldc_withdrawable = 0, ldc_accuint_amt = 0, ldc_intarrear_amt = 0; decimal ldc_chqpend = 0, ldc_doperate_bal = 0, ldc_mincalint = 0, ldc_balance_calint = 0, ldc_interest = 0; decimal ldc_balance_temp = 0, ldc_withdrawable_temp = 0, ldc_doperate_bal_temp = 0, ldc_dept_amt = 0; decimal ldc_chqamt = 0, ldc_with_amt = 0, li_sequest = 0; decimal ldc_spcintrate = 0, li_laststmno = 0, ldc_newintrate = 0; int li_closestatus = 0, li_scpintrate_status = 0, li_waydept = 0; DateTime ldtm_lastcalint_date, ldtm_operatedate, ldtm_pmon, ldtm_calintto, ldtm_calintfrom = state.SsWorkDate; DateTime ldtm_prncdue, ldtm_mustcleardate, ldtm_chqdate, ldtm_entrytime; int li_seqno = 0, li_chqlate = 0, li_flag = 0; int li_chk = 0, li_calint_round = 0, li_monthdue = 0, li_max_prnc = 0, li_calintmrth = 0, li_floatday = 0; long ll_rc = 0, ll_index, ll_row, ll_rownewrate; string ls_chqno = "", ls_bank = "", ls_branch = "", ls_chqtype = "", ls_slipno = ""; ldtm_operatedate = dsMain.DATA[0].entry_date; ldtm_pmon = dsMain.DATA[0].entry_date; ldtm_calintto = dsMain.DATA[0].entry_date; li_flag = Convert.ToInt32(dsMain.DATA[0].entry_item); li_waydept = Convert.ToInt32(dsMain.DATA[0].from_way); ls_deptitem = (dsMain.DATA[0].recvtype_id); if (li_flag == 1) { if (li_waydept == 1) { // โอนธนาคาร ls_cashtype = "TRN"; ls_referslipno = wcf.NCommon.of_getnewdocno(state.SsWsPass, state.SsCoopControl, "ADVCHQDEPT"); } else if (li_waydept == 2) { // โอนภายใน ls_cashtype = "TRN"; ls_referslipno = ""; } else if (li_waydept == 3) { // ฝากเช็ค ls_cashtype = "CHQ"; ls_referslipno = wcf.NCommon.of_getnewdocno(state.SsWsPass, state.SsCoopControl, "ADVCHQDEPT"); } } // เริ่มทำการประมวลผลแต่ละบัญชี for (int i = 1; i <= DwList.RowCount; i++) { if (DwList.GetItemDecimal(i, "flag") == 1) { ls_deptno = DwList.GetItemString(i, "deptaccount_no").Trim(); ldc_pmon = DwList.GetItemDecimal(i, "dw_amount"); ls_tofromaccid = DwList.GetItemString(i, "tofrom_accid"); ls_accbranch = state.SsCoopControl; string shq = @" select member_no, dpdeptmaster.depttype_code as depttype_code, dpdepttype.deptgroup_code as deptgroup_code, laststmseq_no, prncbal, withdrawable_amt, deptclose_status, sequest_status, lastcalint_date, accuint_amt, intarrear_amt, spcint_rate, spcint_rate_status, checkpend_amt, dept_amt, with_amt from dpdeptmaster , dpdepttype where dpdeptmaster.depttype_code = dpdepttype.depttype_code and ( deptaccount_no = {0} ) and ( dpdeptmaster.coop_id = {1} )"; shq = WebUtil.SQLFormat(shq, ls_deptno, state.SsCoopControl); Sdt ds = WebUtil.QuerySdt(shq); if (ds.Next()) { ls_memno = ds.GetString("member_no"); ls_depttype = ds.GetString("depttype_code"); li_laststmno = ds.GetDecimal("laststmseq_no"); ldc_balance = ds.GetDecimal("prncbal"); ldc_withdrawable = ds.GetDecimal("withdrawable_amt"); li_closestatus = ds.GetInt32("deptclose_status"); li_sequest = ds.GetDecimal("laststmseq_no"); ldtm_lastcalint_date = ds.GetDate("lastcalint_date"); ldc_accuint_amt = ds.GetDecimal("accuint_amt"); ldc_intarrear_amt = ds.GetDecimal("intarrear_amt"); ldc_spcintrate = ds.GetDecimal("spcint_rate"); li_scpintrate_status = ds.GetInt32("spcint_rate_status"); ldc_chqpend = ds.GetDecimal("checkpend_amt"); ldc_dept_amt = ds.GetDecimal("dept_amt"); ldc_with_amt = ds.GetDecimal("with_amt"); ls_depttype_groupcode = ds.GetString("deptgroup_code"); ldtm_calintfrom = ldtm_lastcalint_date; } // ตรวจสอบว่ามียอดเงินที่จะให้ทำรายการหรือไม่ if (ldc_pmon > 0) { // ตรวจสอบการปิดบัญชี if (li_closestatus == 1) { } // ตรวจสอบการอายัดบัญชี if (li_sequest == 1 || li_sequest == 0) { // หากเป็นรายการถอน if (li_flag == -1) { // ตรวจสอบคงเหลือขั้นต่ำ //if ( this.of_chkminbal( ls_depttype, ls_deptno, ls_accbranch, ldc_pmon ) = Failure ) then } } else if (li_sequest == 2) { } else if (li_sequest == 3) { } } ldc_balance_calint = ldc_balance; //// ดึงว่ามีระยะการฝากหรือไม่ //ll_rc = integer( inv_depositsrv.of_getattribdeposittype( ls_depttype, "timedue_flag" ) ) //if isnull( ll_rc ) then ll_rc = 0 // หากมีระยะการฝาก ทำเฉพาะรายการฝาก if (ll_rc == 1 && li_flag == 1) { // วันคำนวณดอกเบี้ยล่าสุด ldtm_lastcalint_date = ldtm_pmon; } // ============ จบ กรณีมีกำหนด ที่ Master ================= ldc_interest = 0; // ตรวจสอบว่าทำฝาก หรือ ถอน if (li_flag == 1) { ldc_accuint_amt = ldc_accuint_amt + ldc_interest; ldc_balance_temp = ldc_balance + ldc_pmon; ldc_withdrawable_temp = ldc_withdrawable + ldc_pmon; ldc_dept_amt = ldc_dept_amt + ldc_pmon; } else { ldc_accuint_amt = ldc_accuint_amt - ldc_interest; ldc_balance_temp = ldc_balance - ldc_pmon; ldc_withdrawable_temp = ldc_withdrawable - ldc_pmon; ldc_with_amt = ldc_with_amt + ldc_pmon; ls_deptitem = "WTR"; } ldc_doperate_bal_temp = ldc_pmon; li_laststmno++; // บันทึกรายละเอียดลงทะเบียนเงินฝาก String sql2 = @" update dpdeptmaster set prncbal = {0}, withdrawable_amt = {1} , accuint_amt = {2}, lastaccess_date = {3}, laststmseq_no = {4}, doperate_bal = {5}, lastmovement_date = {6}, dept_amt = {7}, with_amt = {8} where ( deptaccount_no = {9} ) and ( coop_id = {10} )"; sql2 = WebUtil.SQLFormat(sql2, ldc_balance_temp, ldc_withdrawable_temp, ldc_accuint_amt, ldtm_operatedate, li_laststmno, ldc_doperate_bal_temp, ldtm_operatedate, ldc_dept_amt, ldc_with_amt, ls_deptno, state.SsCoopControl); WebUtil.QuerySdt(sql2); // บันทึกลง Slip li_max_prnc = 0; ls_ref_docno = wcf.NCommon.of_getnewdocno(state.SsWsPass, state.SsCoopControl, "DPSLIPNO"); ls_referapp = "DEP"; if (ls_tofromaccid == null) { ls_tofromaccid = ""; } String InsSql = @" insert into dpdeptslip ( deptslip_no, deptaccount_no, depttype_code, deptslip_date, recppaytype_code, deptslip_amt, prncbal, withdrawable_amt, entry_id, entry_date, machine_id, deptitemtype_code, dpstm_no, item_status, calint_from, calint_to, deptgroup_code, tofrom_accid, refer_app, refer_slipno, cash_type, showfor_dept, int_amt, accuint_amt, coop_id, tax_amt, due_flag, deptslip_netamt,deptcoop_id ) values ( {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, 0, 0,{5},{24} ) "; InsSql = WebUtil.SQLFormat(InsSql, ls_ref_docno, ls_deptno, ls_depttype, ldtm_operatedate, ls_deptitem, ldc_pmon, ldc_balance, ldc_withdrawable, state.SsUsername, ldtm_operatedate, state.SsClientIp, ls_deptitem, li_laststmno, 1, ldtm_calintfrom, ldtm_calintto, ls_depttype_groupcode, ls_tofromaccid, ls_referapp, ls_referslipno, ls_cashtype, 1, ldc_interest, ldc_accuint_amt, ls_accbranch, state.SsCoopControl, ldc_pmon); WebUtil.QuerySdt(InsSql); // บันทึกลง Statement String Inserts = @" INSERT INTO DPDEPTSTATEMENT ( DEPTACCOUNT_NO, SEQ_NO, DEPTITEMTYPE_CODE, OPERATE_DATE, DEPTSLIP_NO, DEPTITEM_AMT, BALANCE_FORWARD, PRNCBAL, PRNC_NO, TAX_AMT, INT_AMT, ACCUINT_AMT, RETINT_AMT, ITEM_STATUS, PRNTOPB_STATUS, PRNTOCARD_STATUS, CHECKBOOK_CODE_PB, ENTRY_ID, ENTRY_DATE, CLOSEDAY_STATUS, CALINT_FROM, CALINT_TO, CASH_TYPE, AUTHORIZE_ID, MACHINE_ID, NO_BOOK_FLAG, CHEQUE_PENDING, REF_SEQ_NO, PAGE_PB, LINE_PB, coop_id ) values ( {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, 0, {9}, {10}, 0, 1, 0, 0, null, {11}, {12}, 0, {13}, {14}, {15}, null, {18}, 0, {19}, null, 1, null, {16} ) "; Inserts = WebUtil.SQLFormat(Inserts, ls_deptno, li_laststmno, ls_deptitem, ldtm_pmon, ls_ref_docno, ldc_pmon, ldc_balance, ldc_balance_temp, li_max_prnc, ldc_interest, ldc_accuint_amt, state.SsUsername, ldtm_pmon, ldtm_calintfrom, ldtm_calintto, ls_cashtype, state.SsCoopControl, state.SsUsername, state.SsClientIp, ldc_chqpend, ls_accbranch); WebUtil.QuerySdt(Inserts); WebUtil.QuerySdt("update DPDEPTADVANCEDW set POST_STATUS =1 where deptaccount_no ='" + ls_deptno + "' and coop_id='" + state.SsCoopControl + "' "); } } LtServerMessage.Text = WebUtil.CompleteMessage("ผ่านรายการสำเร็จ"); DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, state.SsCoopControl, dsMain.DATA[0].entry_item); DwUtil.RetrieveDDDW(DwList, "tofrom_accid", pbl, null); } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex.ToString()); } } else if (eventArg == ChangeAcc) { ChangFnACC(); } else if (eventArg == DeleteRowDetail) { int i = Convert.ToInt32(HdCheck.Value); try { string acc = DwList.GetItemString(i, "deptaccount_no"); decimal seq = DwList.GetItemDecimal(i, "seq_no"); WebUtil.QuerySdt("delete from DPDEPTADVANCEDW where deptaccount_no = '" + acc + "' and seq_no =" + seq + ""); DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, state.SsCoopControl, dsMain.DATA[0].entry_item); }catch(Exception e){ } } } private void ChangFnACC() { int i = Convert.ToInt32(HdCheck.Value); string dept = acc_no.Value;// DwList.GetItemString(i, "deptaccount_no"); Sdt d = WebUtil.QuerySdt("select * from dpdeptmaster where deptaccount_no like '%" + dept + "'"); if (d.Next()) { DwList.SetItemString(i, "deptaccount_no", d.GetString("deptaccount_no")); DwList.SetItemString(i, "deptaccount_name", d.GetString("deptaccount_name")); } } public void SaveWebSheet() { try { WebUtil.QuerySdt("delete from DPDEPTADVANCEDW where dw_flag = " + dsMain.DATA[0].entry_item + " "); for (int i = 1; i <= DwList.RowCount; i++) { Int32 seq = 0; Sdt d = WebUtil.QuerySdt("select max(seq_no) as seq_no from DPDEPTADVANCEDW where deptaccount_no = '" + DwList.GetItemString(i, "deptaccount_no") + "'"); if (d.Next()) { seq = d.GetInt32("seq_no") + 1; } string sql = @" insert into DPDEPTADVANCEDW (coop_id,dw_date,deptaccount_no,seq_no,entry_date ,entry_time,entry_id,dw_amount,dw_flag,post_status, machine_id,item_status,tofrom_accid,cashtype_code, itemtype_code,recv_period ) values( {0},{1},{2} ,{3},{11} ,{11},{4},{5},{8},0, {6},1,{7},{9}, {10},0 )"; sql = WebUtil.SQLFormat(sql, state.SsCoopControl, dsMain.DATA[0].entry_date, DwList.GetItemString(i, "deptaccount_no"), seq, state.SsUsername, DwList.GetItemDecimal(i, "dw_amount"), state.SsClientIp, DwList.GetItemString(i, "tofrom_accid"), dsMain.DATA[0].entry_item , "TRN", dsMain.DATA[0].recvtype_id, DateTime.Today); WebUtil.QuerySdt(sql); } LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกข้อมูลเสร็จสิ้น" ); DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, state.SsCoopControl, dsMain.DATA[0].entry_item); }catch { } } public void WebSheetLoadEnd() { DwList.SaveDataCache(); } } }