using System; using CoreSavingLibrary; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Xml.Linq; using Sybase.DataWindow; using DataLibrary; using CoreSavingLibrary.WcfNKeeping; using CoreSavingLibrary.WcfNShrlon; using System.Web.Services.Protocols; using System.Globalization; namespace Saving.Applications.shrlon { public partial class w_sheet_proc18nmn : PageWebSheet, WebSheet { CultureInfo th = System.Globalization.CultureInfo.GetCultureInfo("th-TH"); //ประกาศตัวแปร #region Variable private n_keepingClient KeepingService; private n_shrlon NshrlonService; protected String postNewClear; protected String postRefresh; protected String postProcClsDay; private DwThDate tDwOption; protected String postMonth; //mikekong new process public string outputProcess; protected String Call_process; #endregion //============================================ #region Websheet Members public void InitJsPostBack() { tDwOption = new DwThDate(Dw_option, this); tDwOption.Add("process_date", "process_tdate"); tDwOption.Add("sbfyear_date", "sbfyear_tdate"); tDwOption.Add("s_date", "s_tdate"); tDwOption.Add("e_date", "e_tdate"); tDwOption.Add("nx_date", "nx_tdate"); //========================================= postNewClear = WebUtil.JsPostBack(this, "postNewClear"); postRefresh = WebUtil.JsPostBack(this, "postRefresh"); postProcClsDay = WebUtil.JsPostBack(this, "postProcClsDay"); postMonth = WebUtil.JsPostBack(this, "postMonth"); Call_process = WebUtil.JsPostBack(this, "Call_process"); } public void WebSheetLoadBegin() { Hd_process.Value = "false"; if (!IsPostBack) { JspostNewClear(); // of_checkclsday(); } else { this.RestoreContextDw(Dw_option); } } public void CheckJsPostBack(string eventArg) { // Event ที่เกิดจาก JavaScript switch (eventArg) { case "postNewClear": JspostNewClear(); break; case "postRefresh": //Refresh(); break; case "postProcClsDay": JspostProcClsDay(); break; case "Call_process": update_interest(); break; case "postMonth": //เปิดไว้ก่อนปิดสิ้นปี 59 int year = Convert.ToInt32(Dw_option.GetItemDecimal(1, "year_no")); //ปี 59 Dw_option.SetItemString(1, "sbfyear_tdate", "0107" + Convert.ToString(year - 1)); Dw_option.SetItemString(1, "s_tdate", "0107" + Convert.ToString(year)); Dw_option.SetItemString(1, "e_tdate", "3006" + Convert.ToString(year + 1)); Dw_option.SetItemString(1, "process_tdate", "3006" + Convert.ToString(year + 1)); Dw_option.SetItemString(1, "nx_tdate", "3006" + Convert.ToString(year + 2)); year = year - 543; DateTime sbfyear_date = new DateTime(year - 1, 7, 1, 0, 0, 0, 0); DateTime s_date = new DateTime(year, 7, 1, 0, 0, 0, 0); DateTime e_date = new DateTime(year + 1, 6, 30, 0, 0, 0, 0); DateTime nx_date = new DateTime(year + 2, 6, 30, 0, 0, 0, 0); Dw_option.SetItemDate(1, "process_date", e_date); Dw_option.SetItemDate(1, "sbfyear_date", sbfyear_date); Dw_option.SetItemDate(1, "s_date", s_date); Dw_option.SetItemDate(1, "e_date", e_date); Dw_option.SetItemDate(1, "nx_date", nx_date); break; } } public void SaveWebSheet() { } public void WebSheetLoadEnd() { Dw_option.SaveDataCache(); } #endregion //============================================= // function เคลียร์หน้าจอ private void JspostNewClear() { Dw_option.Reset(); Dw_option.InsertRow(0); WebUtil.RetrieveDDDW(Dw_option, "as_slntype", "sl_shlnproc", null); WebUtil.RetrieveDDDW(Dw_option, "as_sgrp", "sl_shlnproc", state.SsCoopControl); WebUtil.RetrieveDDDW(Dw_option, "as_elntype", "sl_shlnproc", null); WebUtil.RetrieveDDDW(Dw_option, "as_egrp", "sl_shlnproc", state.SsCoopControl); //เปิดไว้ก่อนปิดสิ้นปี 59 int year = state.SsWorkDate.Year; //ปี 59 Dw_option.SetItemDecimal(1, "year_no", year + 543); Dw_option.SetItemDecimal(1, "month_no", 6); DateTime sbfyear_date = new DateTime(year - 1, 7, 1, 0, 0, 0, 0); DateTime s_date = new DateTime(year, 7, 1, 0, 0, 0, 0); DateTime e_date = new DateTime(year + 1, 6, 30, 0, 0, 0, 0); DateTime nx_date = new DateTime(year + 2, 6, 30, 0, 0, 0, 0); Dw_option.SetItemString(1, "sbfyear_tdate", "0107" + Convert.ToString(year + 542)); Dw_option.SetItemString(1, "s_tdate", "0107" + Convert.ToString(year + 543)); Dw_option.SetItemString(1, "e_tdate", "3006" + Convert.ToString(year + 544)); Dw_option.SetItemString(1, "process_tdate", "3006" + Convert.ToString(year + 544)); Dw_option.SetItemString(1, "nx_tdate", "3006" + Convert.ToString(year + 545)); //หลังปิดสิ้นปี ค่อยเอา comment ออก //Dw_option.SetItemDecimal(1, "year_no", state.SsWorkDate.Year+543); //Dw_option.SetItemDecimal(1, "month_no", 3); //DateTime sbfyear_date = new DateTime(state.SsWorkDate.Year -1, 4, 1, 0, 0, 0, 0); //DateTime s_date = new DateTime(state.SsWorkDate.Year, 4, 1, 0, 0, 0, 0); //DateTime e_date = new DateTime(state.SsWorkDate.Year +1, 3, 31, 0, 0, 0, 0); //DateTime nx_date = new DateTime(state.SsWorkDate.Year + 2, 3, 31, 0, 0, 0, 0); //Dw_option.SetItemString(1, "sbfyear_tdate", "0104" + Convert.ToString(state.SsWorkDate.Year+543)); //Dw_option.SetItemString(1, "s_tdate", "0104" + Convert.ToString(state.SsWorkDate.Year+543)); //Dw_option.SetItemString(1, "e_tdate", "3103" + Convert.ToString(state.SsWorkDate.Year+544)); //Dw_option.SetItemString(1, "nx_tdate", "3103" + Convert.ToString(state.SsWorkDate.Year+545)); Dw_option.SetItemDate(1, "process_date", e_date); Dw_option.SetItemDate(1, "sbfyear_date", sbfyear_date); Dw_option.SetItemDate(1, "s_date", s_date); Dw_option.SetItemDate(1, "e_date", e_date); Dw_option.SetItemDate(1, "nx_date", nx_date); string[] minmax = ReportUtil.GetMinMaxLoantype(); Dw_option.SetItemString(1, "as_slntype", minmax[0]); Dw_option.SetItemString(1, "as_elntype", minmax[1]); string[] minmax2 = ReportUtil.GetMinMaxMembgroup(); Dw_option.SetItemString(1, "as_sgrp", minmax2[0]); Dw_option.SetItemString(1, "as_egrp", minmax2[1]); tDwOption.Eng2ThaiAllRow(); } private void JspostProcClsDay() { //DateTime ldtm_clsdate = Dw_option.GetItemDateTime(1, "clsday_date"); //DateTime ldtm_workdate = state.SsWorkDate; //if (ldtm_clsdate != ldtm_workdate) //{ // this.SetOnLoadedScript("alert('วันที่ปิดสิ้นวันไม่ใช่วันทำการ \\nวันที่ทำรายการ : " + ldtm_workdate.ToString("dd/MM/yyyy", th) + "\\nกรุณาตรวจสอบ')"); // return; //} /// start string sgroup = Dw_option.GetItemString(1, "as_sgrp").Trim(); string egroup = Dw_option.GetItemString(1, "as_egrp").Trim(); try { CultureInfo th = System.Globalization.CultureInfo.GetCultureInfo("th-TH"); // DateTime ss = dw_periodpay.GetItemDate(row, "expireperiod_date"); //***************** String process_tdate = Dw_option.GetItemString(1, "process_tdate"); Int16 month = Convert.ToInt16(process_tdate.Substring(2, 2)); // month = month; DateTime loanrcv_dates = DateTime.ParseExact(process_tdate, "ddmmyyyy", null).AddMonths(month - 1).AddYears(-543); Dw_option.SetItemDate(1, "process_date", loanrcv_dates); //*************** //***************** String sbfyear_date = Dw_option.GetItemString(1, "sbfyear_tdate"); Int16 month22 = Convert.ToInt16(sbfyear_date.Substring(2, 2)); // month = month; DateTime sbfyear_dates = DateTime.ParseExact(sbfyear_date, "ddmmyyyy", null).AddMonths(month22 - 1).AddYears(-543); Dw_option.SetItemDate(1, "sbfyear_date", sbfyear_dates); //*************** //***************** String s_date = Dw_option.GetItemString(1, "s_tdate"); Int16 month3 = Convert.ToInt16(s_date.Substring(2, 2)); // month = month; DateTime s_dates = DateTime.ParseExact(s_date, "ddmmyyyy", null).AddMonths(month3 - 1).AddYears(-543); Dw_option.SetItemDate(1, "s_date", s_dates); //*************** //***************** String e_date = Dw_option.GetItemString(1, "e_tdate"); Int16 month4 = Convert.ToInt16(process_tdate.Substring(2, 2)); // month = month; DateTime e_dates = DateTime.ParseExact(e_date, "ddmmyyyy", null).AddMonths(month4 - 1).AddYears(-543); Dw_option.SetItemDate(1, "e_date", e_dates); //*************** //***************** String nx_date = Dw_option.GetItemString(1, "nx_tdate"); Int16 month5 = Convert.ToInt16(process_tdate.Substring(2, 2)); // month = month; DateTime nx_dates = DateTime.ParseExact(nx_date, "ddmmyyyy", null).AddMonths(month5 - 1).AddYears(-543); Dw_option.SetItemDate(1, "nx_date", nx_dates); //*************** NshrlonService = wcf.NShrlon; String dwMain_XML = Dw_option.Describe("DataWindow.Data.XML"); try { string delete = "delete from LNLOANPAYMENTSUMMARY where year_no = " + Dw_option.GetItemDecimal(1, "year_no") + " and month_no = " + Dw_option.GetItemDecimal(1, "month_no") + " "; delete = WebUtil.SQLFormat(delete); WebUtil.QuerySdt(delete); } catch { } //int r = wcf.NShrlon.of_proc_loanpayment_summary(state.SsWsPass, state.SsCoopControl, sgroup, egroup, dwMain_XML); //// int r = wcf.NShrlon.of_loanpayment_summary(state.SsWsPass, state.SsCoopControl, sgroup,egroup, dwMain_XML); //if (r > 0) //{ // LtServerMessage.Text = WebUtil.CompleteMessage("ผ่าน"); // update_interest(); //} //else //{ // LtServerMessage.Text = WebUtil.ErrorMessage("ไม่ผ่าน"); //} //// end //KeepingService = wcf.NKeeping;s //str_slcls_proc astr_slcls_proc = new str_slcls_proc(); //astr_slcls_proc.xml_option = Dw_option.Describe("DataWindow.Data.XML"); //KeepingService.RunKpSlClsDayProcess(state.SsWsPass, ref astr_slcls_proc, state.SsApplication, state.CurrentPage);//RunKpSlClsDayProcess //Hd_process.Value = "true"; //mikekong new process outputProcess = WebUtil.runProcessing(state, "PRO18NMN", dwMain_XML, "", ""); // start } catch (SoapException ex) { LtServerMessage.Text = WebUtil.ErrorMessage(WebUtil.SoapMessage(ex)); } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex.ToString()); } //end } public void update_interest() { // intfine_status string select_lnloanpaymentsummary = @"select lnloanpaymentsummary.loancontract_no,intinyear_amt,lnfineinyear_amt from lnloanpaymentsummary inner join lncontmaster on trim(lnloanpaymentsummary.loancontract_no) = trim(lncontmaster.loancontract_no) where (lnloanpaymentsummary.year_no={2} and lnloanpaymentsummary.month_no={3} ) and (lncontmaster.lastcalint_date between {1}and {0} ) group by lnloanpaymentsummary.loancontract_no,intinyear_amt,lnfineinyear_amt "; select_lnloanpaymentsummary = WebUtil.SQLFormat(select_lnloanpaymentsummary, Dw_option.GetItemDateTime(1, "process_date"), Dw_option.GetItemDateTime(1, "s_date").AddDays(-1) , Dw_option.GetItemDecimal(1, "year_no"), Dw_option.GetItemDecimal(1, "month_no")); Sdt d = WebUtil.QuerySdt(select_lnloanpaymentsummary); while (d.Next()) { string loancontract_no = d.GetString("loancontract_no"); decimal interest = d.GetDecimal("intinyear_amt") + calinitnew(loancontract_no); decimal lnfineinyear_amt = d.GetDecimal("lnfineinyear_amt") + calfine_new(loancontract_no); string update = @"update LNLOANPAYMENTSUMMARY set intinyear_amt = {0},lnfineinyear_amt = {1} where loancontract_no={2} and (year_no={3} and month_no={4} ) "; update = WebUtil.SQLFormat(update, interest, lnfineinyear_amt, loancontract_no, Dw_option.GetItemDecimal(1, "year_no"), Dw_option.GetItemDecimal(1, "month_no")); WebUtil.QuerySdt(update); } d.Clear(); } public decimal calinitnew(String loancontract)// { DateTime date_now = Dw_option.GetItemDateTime(1, "process_date");//วันที่ทำการ Decimal intrate = 0;//อัตราดอกเบี้ย Decimal cal_int = 0; // get yaer จาก ค่าคงที่ decimal year = 0, rdintdec_type = 0, INTROUNDSUM_TYPE = 0; string sql21 = @" select dayinyear,rdintdec_type,INTROUNDSUM_TYPE from lnloanconstant "; sql21 = WebUtil.SQLFormat(sql21, loancontract); Sdt dt21 = WebUtil.QuerySdt(sql21); if (dt21.Next()) { year = dt21.GetDecimal("dayinyear"); rdintdec_type = dt21.GetDecimal("rdintdec_type"); INTROUNDSUM_TYPE = dt21.GetDecimal("INTROUNDSUM_TYPE"); } string sql3 = @" select contract_interest from lncontmaster where loancontract_no={0}"; sql3 = WebUtil.SQLFormat(sql3, loancontract); Sdt dt3 = WebUtil.QuerySdt(sql3); if (dt3.Next()) { intrate = dt3.GetDecimal("contract_interest"); } if (intrate == -1)// ถ้ามากกว่า 0 ให้ไปดึงดอกเบี้ยจาก lncontmaster { if (INTROUNDSUM_TYPE == 0) { //คิดแบบ ไม่จัดชั้น string sql2 = @" select lncfloanintratedet.interest_rate from lncfloanintratedet left join lnloantype on lnloantype.inttabfix_code = lncfloanintratedet.loanintrate_code left join lncontmaster on lncontmaster.loantype_code=lnloantype.loantype_code where lncontmaster.loancontract_no= {0}"; sql2 = WebUtil.SQLFormat(sql2, loancontract); Sdt dt2 = WebUtil.QuerySdt(sql2); if (dt2.Next()) { intrate = dt2.GetDecimal("interest_rate"); } } else {//คิดแบบ จัดชั้น string sql2 = @" select lir.int_rate from lncontmaster lc inner join lnloantypeintrate lir on lir.loantype_code = lc.loantype_code inner join mbmembmaster mb on mb.member_no = lc.member_no and mb.mblntype_level = lir.mblntype_level where lc.loancontract_no= {0}"; sql2 = WebUtil.SQLFormat(sql2, loancontract); Sdt dt2 = WebUtil.QuerySdt(sql2); if (dt2.Next()) { intrate = dt2.GetDecimal("int_rate"); } } } decimal int_cal = 0; string sql = @" SELECT principal_balance,lastcalint_date FROM lncontmaster WHERE loancontract_no = {0}"; sql = WebUtil.SQLFormat(sql, loancontract); Sdt dt = WebUtil.QuerySdt(sql); if (dt.Next()) { DateTime calint = dt.GetDate("lastcalint_date"); Decimal principal_balance = dt.GetDecimal("principal_balance"); // ยอดคงเหลือ TimeSpan diff1 = date_now.Subtract(calint); Double TotalDay2 = diff1.TotalDays; int CalDay = Convert.ToInt32(TotalDay2); // คำนวณวัน intrate = intrate / 100; //คำนวณดอกเบี้ย กล้า cal_int = (principal_balance * intrate * CalDay) / year; // ด/บใหม่ } if (rdintdec_type == 0)//ทศนิยม 2 ตำแหน่ง { int_cal = Convert.ToDecimal(Math.Round(cal_int, 2)); } else if (rdintdec_type == 1)//ปัดทิ้ง { int_cal = Convert.ToDecimal(Math.Floor(cal_int)); } else if (rdintdec_type == 2)//ปัดทิ้ง { int_cal = Convert.ToDecimal(Math.Ceiling(cal_int)); } else { int_cal = Convert.ToDecimal(Math.Round(cal_int, 0)); } return int_cal; } public decimal calfine_new(String loancontract) { DateTime date_now = Dw_option.GetItemDateTime(1, "process_date");//วันที่ทำการ DateTime lastcalfine_date = state.SsWorkDate; DateTime expireperiod_date = date_now; Double principal_arrear = 0; Double cal_int = 0; //ผลลัพธ์ Double year = 0; Double cal_fineall = 0; // int calfine = 0; // get yaer จาก ค่าคงที่ int rdintsatang_type = 0; // get yaer จาก ค่าคงที่ string sql2 = @" select dayinyear,rdintsatang_type from lnloanconstant"; sql2 = WebUtil.SQLFormat(sql2, loancontract); Sdt dt2 = WebUtil.QuerySdt(sql2); if (dt2.Next()) { year = dt2.GetDouble("dayinyear"); rdintsatang_type = dt2.GetInt32("rdintsatang_type");//ปัดเศษ } string sql = @" select lastcalfine_date,principal_arrear,expireperiod_date,lastcalfine_date from lncontperiodpaydet where loancontract_no= {0} and principal_arrear > 0 and expireperiod_date < {1} order by period asc "; sql = WebUtil.SQLFormat(sql, loancontract, date_now); Sdt dt = WebUtil.QuerySdt(sql); while (dt.Next()) { // lastcalfine_date = dt.GetDate("lastcalfine_date"); //วันที่คิดล่าสุด expireperiod_date = dt.GetDate("expireperiod_date"); //วันที่ต้องจ่าย principal_arrear = dt.GetDouble("principal_arrear"); // ยอดคงเหลือ lastcalfine_date = dt.GetDate("lastcalfine_date"); TimeSpan diff1 = date_now.Subtract(expireperiod_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด Double TotalDay2 = diff1.TotalDays; if (TotalDay2 < 0) { cal_int = 0; } else { Double percen = 0.03; TimeSpan diff2 = date_now.Subtract(lastcalfine_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด Double TotalDay3 = diff2.TotalDays; int CalDay = Convert.ToInt32(TotalDay3); // คำนวณวัน String hart = "select usemangrt_comaxvalue,principal_balance from lnloantype inner join lncontmaster on lnloantype.loantype_code = lncontmaster.loantype_code where loancontract_no = '" + loancontract + "'"; Sdt sd = WebUtil.QuerySdt(hart); if (sd.Next()) { percen = Convert.ToDouble(sd.GetDecimal("usemangrt_comaxvalue") / 100); if (sd.GetDecimal("principal_balance") <= 0) { principal_arrear = 0; } } cal_int = (principal_arrear * percen * CalDay) / year; // ค่าปรับใหม่ } cal_fineall += cal_int; } decimal fine = 0; if (rdintsatang_type == 0)//ทศนิยม 2 ตำแหน่ง { fine = Convert.ToDecimal(Math.Round(cal_fineall, 2)); } else if (rdintsatang_type == 1)//ปัดทิ้ง { fine = Convert.ToDecimal(Math.Floor(cal_fineall)); } else if (rdintsatang_type == 2)//ปัดขึ้น { fine = Convert.ToDecimal(Math.Ceiling(cal_fineall)); } else { decimal fils = Convert.ToDecimal(Math.Floor(cal_fineall)); fine = Convert.ToDecimal(Math.Round(cal_fineall, 2)); fils = (fine - fils) * 100; if (fils >= 49) { fine = Convert.ToDecimal(Math.Ceiling(cal_fineall)); } else { fine = Convert.ToDecimal(Math.Floor(cal_fineall)); } } return fine; } public double getPricipalBalance(String loancontract) { double principal_balance = 0; string sql = @"select principal_balance from lncontmaster where loancontract_no='" + loancontract + "' "; sql = WebUtil.SQLFormat(sql, loancontract); Sdt dt = WebUtil.QuerySdt(sql); while (dt.Next()) { principal_balance = dt.GetDouble("principal_balance"); // เงินต้น คงเหลือ } return principal_balance; } //เช็คปิดวัน // public void of_checkclsday() // { // string ls_closedayid = ""; // DateTime ldtm_clsdate = Dw_option.GetItemDateTime(1, "process_date"); // DateTime ldtm_workdate = state.SsWorkDate; // string sql = @"select closeday_id , workdate // from amappstatus // where coop_id = {0} // and application = {1} // and closeday_status = 1"; // sql = WebUtil.SQLFormat(sql, state.SsCoopControl, state.SsApplication); // Sdt dt = WebUtil.QuerySdt(sql); // if (dt.Next()) // { // ls_closedayid = dt.GetString("closeday_id"); // ldtm_workdate = dt.GetDate("workdate"); // this.SetOnLoadedScript("alert('ระบบปิดสิ้นวันไปเรียบร้อยแล้ว \\nผู้ทำรายการ : " + ls_closedayid + " \\nวันที่ทำรายการ : " + ldtm_workdate.ToString("dd/MM/yyyy", th) + "\\nกรุณาตรวจสอบ')"); // return; // } //} } }