using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using DataLibrary; using CoreSavingLibrary; namespace Saving.CriteriaIReport.process_confirmbook { public partial class process_confirmbook : PageWebReport, WebReport { protected String app; protected String gid; protected String rid; protected String jsPostMember; public void InitJsPostBack() { dsMain.InitDsMain(this); dsMain.DdMembgroup(); } public void WebSheetLoadBegin() { //--- Page Arguments try { app = Request["app"].ToString(); } catch { } if (app == null || app == "") { app = state.SsApplication; } try { gid = Request["gid"].ToString(); } catch { } try { rid = Request["rid"].ToString(); } catch { } //Report Name. try { Sta ta = new Sta(state.SsConnectionString); String sql = ""; sql = @"SELECT REPORT_NAME FROM WEBREPORTDETAIL WHERE ( GROUP_ID = '" + gid + @"' ) AND ( REPORT_ID = '" + rid + @"' )"; Sdt dt = ta.Query(sql); ReportName.Text = dt.Rows[0]["REPORT_NAME"].ToString(); ta.Close(); } catch { ReportName.Text = "[" + rid + "]"; } if (!IsPostBack) { dsMain.DdMembgroup(); dsMain.DdCoopId(); dsMain.DATA[0].coop_id = state.SsCoopControl; dsMain.DATA[0].date = state.SsWorkDate; } } public void CheckJsPostBack(string eventArg) { } public void RunReport() { DELETE_CMLNSHCONFIRMBOOK(); INSERT_CMLNSHCONFIRMBOOK(); } private void DELETE_CMLNSHCONFIRMBOOK() { string sql = "delete from CMLNSHCONFIRMBOOK where coop_id={0}"; sql = WebUtil.SQLFormat(sql, state.SsCoopId); WebUtil.QuerySdt(sql); } private void INSERT_CMLNSHCONFIRMBOOK() { //insert LON try { string sql = "select mb.member_no,ln.loancontract_no,ln.loantype_code,ln.principal_balance,ln.loanapprove_amt,ln.interest_arrear,ln.fine_arrear,ln.principal_arrear from lncontmaster ln inner join MBMEMBMASTER mb on ln.member_no = mb.member_no where mb.coop_id ={0} and mb.MEMBGROUP_CODE between {1} and {2} and ln.contract_status =1 and (ln.principal_balance >0 or ln.interest_arrear > 0 or ln.fine_arrear >0 or ln.principal_arrear >0 )"; string ref_system = string.Empty, lnsh_type = string.Empty, member_no = string.Empty, loancontract_no = string.Empty, loantype_code = string.Empty; decimal seq_no = 0, principal_balance = 0, loanapprove_amt = 0, principal_arrear = 0, interest_arrear = 0, fine_arrear = 0; sql = WebUtil.SQLFormat(sql, state.SsCoopId, dsMain.DATA[0].membgroup_start, dsMain.DATA[0].membgroup_end); Sdt dt = WebUtil.QuerySdt(sql); while (dt.Next()) { member_no = dt.GetString("member_no"); loancontract_no = dt.GetString("loancontract_no"); loantype_code = dt.GetString("loantype_code"); principal_balance = dt.GetDecimal("principal_balance"); loanapprove_amt = dt.GetDecimal("loanapprove_amt"); interest_arrear = dt.GetDecimal("interest_arrear"); fine_arrear = dt.GetDecimal("fine_arrear"); principal_arrear = dt.GetDecimal("principal_arrear"); ref_system = "LON"; lnsh_type = "03"; seq_no = get_seq_no(state.SsCoopId, member_no); string sql2 = "insert into CMLNSHCONFIRMBOOK (coop_id,seq_no,member_no,lnsh_type,loancontract_no,loantype_code,principle_balanc,ref_system,loanapprove_amt,interest_arrear,fine_arrear,principal_arrear) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})"; sql2 = WebUtil.SQLFormat(sql2, state.SsCoopId, seq_no, member_no, lnsh_type, loancontract_no, loantype_code, principal_balance, ref_system, loanapprove_amt, interest_arrear, fine_arrear, principal_arrear); WebUtil.QuerySdt(sql2); } } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } ////insert DEP try { string sql = "select mb.member_no,dp.deptaccount_no, dp.depttype_code,dp.beginbal from dpdeptmaster dp inner join mbmembmaster mb on mb.member_no = dp.member_no where mb.coop_id ={0} and mb.MEMBGROUP_CODE between {1} and {2} and dp.deptclose_status=0 and dp.prncbal>0 "; string ref_system = string.Empty, lnsh_type = string.Empty, member_no = string.Empty, depttype_code = string.Empty, deptaccount_no = string.Empty; decimal seq_no = 0, beginbal = 0; sql = WebUtil.SQLFormat(sql, state.SsCoopId, dsMain.DATA[0].membgroup_start, dsMain.DATA[0].membgroup_end); Sdt dt = WebUtil.QuerySdt(sql); while (dt.Next()) { member_no = dt.GetString("member_no"); depttype_code = dt.GetString("depttype_code"); beginbal = dt.GetDecimal("beginbal"); deptaccount_no = dt.GetString("deptaccount_no"); ref_system = "DEP"; lnsh_type = "02"; seq_no = get_seq_no(state.SsCoopId, member_no); string sql2 = "insert into CMLNSHCONFIRMBOOK (coop_id,seq_no,member_no,lnsh_type,loantype_code,principle_balanc,ref_system,loancontract_no) values({0},{1},{2},{3},{4},{5},{6},{7})"; sql2 = WebUtil.SQLFormat(sql2, state.SsCoopId, seq_no, member_no, lnsh_type, depttype_code, beginbal, ref_system, deptaccount_no); WebUtil.QuerySdt(sql2); } } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } //insert share try { string sql = "select mb.member_no, sh.sharetype_code,sh.sharebegin_amt from shsharemaster sh inner join mbmembmaster mb on mb.member_no = sh.member_no where mb.coop_id ={0} and mb.MEMBGROUP_CODE between {1} and {2} and sh.sharemaster_status=1 and sh.sharestk_amt>0 "; string book_desc="มูลค่าหุ้น",ref_system = string.Empty, lnsh_type = string.Empty, member_no = string.Empty, sharetype_code = string.Empty, loantype_code="00"; decimal seq_no = 0, sharebegin_amt = 0, share_value = 10; sql = WebUtil.SQLFormat(sql, state.SsCoopId, dsMain.DATA[0].membgroup_start, dsMain.DATA[0].membgroup_end); Sdt dt = WebUtil.QuerySdt(sql); while (dt.Next()) { member_no = dt.GetString("member_no"); sharetype_code = dt.GetString("sharetype_code"); sharebegin_amt = dt.GetDecimal("sharebegin_amt"); ref_system = "SHR"; lnsh_type = "00"; seq_no = get_seq_no(state.SsCoopId, member_no); string sql2 = "insert into CMLNSHCONFIRMBOOK (coop_id,seq_no,member_no,lnsh_type,principle_balanc,ref_system,book_desc,share_value,loantype_code,SHARSTK_AMT) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9})"; sql2 = WebUtil.SQLFormat(sql2, state.SsCoopId, seq_no, member_no, lnsh_type, sharebegin_amt * share_value, ref_system, book_desc, share_value, loantype_code, sharebegin_amt); WebUtil.QuerySdt(sql2); } } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } try { iReportArgument arg = new iReportArgument(); arg.Add("coop_id", iReportArgumentType.String, state.SsCoopId); arg.Add("date", iReportArgumentType.Date, dsMain.DATA[0].date); if (dsMain.DATA[0].send_date != null) { arg.Add("send_date", iReportArgumentType.Date, dsMain.DATA[0].send_date); } if (dsMain.DATA[0].return_date != null) { arg.Add("return_date", iReportArgumentType.Date, dsMain.DATA[0].return_date); } if (dsMain.DATA[0].aname != string.Empty) { arg.Add("aname", iReportArgumentType.String, dsMain.DATA[0].aname); } if (dsMain.DATA[0].bname != string.Empty) { arg.Add("bname", iReportArgumentType.String, dsMain.DATA[0].bname); } iReportBuider report = new iReportBuider(this, arg); report.Retrieve(); } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } } public decimal get_seq_no(string coop_id,string member_no) { decimal seq_no = 0; try { ////get_seq_no string sql20 = @"select max(seq_no)as seq_no from CMLNSHCONFIRMBOOK where coop_id ={0} and member_no = {1} "; sql20 = WebUtil.SQLFormat(sql20, coop_id, member_no); Sdt dt20 = WebUtil.QuerySdt(sql20); while (dt20.Next()) { seq_no = dt20.GetDecimal("seq_no"); seq_no += 1; } } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } return seq_no; } public void WebSheetLoadEnd() { } } }