using System; using System.Collections.Generic; using System.Globalization; using System.Linq; using System.Text; using DataLibrary; using Sybase.DataWindow; namespace GcoopServiceCs { public class InsuranceService { private String connectionString; private pbservice.str_progress progress; public InsuranceService(String connectionString) { this.connectionString = connectionString; } // doy init. ใบสมัคร ///

/// การ Init ค่าให้ DataWindow 2 ตัว คือ DwMain และ DwLoan
/// 1. สร้าง DataStore ตั้งชื่อว่า dwMain นำ XML จาก argument มา import ใส่
/// 2. ไป select ข้อมูลจากทะเบียนสมาชิกใส่ Sdt แล้วนำค่าต่างๆ ไปยิงใส่ dwMain
/// 3. ไป select ข้อมูลหุ้นแล้วยิงใส่ dwMain
/// 4. สร้าง DataStore สำหรับข้อมูลเงินกู้ dwLoan เพื่อหายอด sum ของเงินกู้ และ export xml เพื่อส่งไปให้หน้า UI
/// 5. เมื่อยิงค่าตั้งต้นของ dwMain ครบแล้วให้ export xml ของ dwMain ส่งไปให้หน้า UI ///

/// pbl แบบ fullpath /// xml จาก DwMain /// คืนค่า Array String รูปแบบ XML 2 ตัวคือ DwMain(d_as_insureqnew) และ DwLoan(d_as_loandetail) public String[] InitInsuRequestNew(String pbl, String xmlInsuRequest) { String[] resu = new String[2]; resu[0] = ""; resu[1] = ""; Sta ta = new Sta(connectionString); try { //1. สร้าง DataStore ตั้งชื่อว่า dwMain นำ XML จาก argument มา import ใส่ DataStore dwMain = new DataStore(); dwMain.LibraryList = pbl; dwMain.DataWindowObject = "d_as_insureqnew"; dwMain.Reset(); dwMain.ImportString(xmlInsuRequest, FileSaveAsType.Xml); String memberNo = dwMain.GetItemString(1, "member_no"); //2. ไป select ข้อมูลจากทะเบียนสมาชิกใส่ Sdt แล้วนำค่าต่างๆ ไปยิงใส่ dwMain String sql = @" SELECT MBMEMBMASTER.MEMBER_NO, MBMEMBMASTER.MEMB_NAME, MBMEMBMASTER.MEMB_SURNAME, MBMEMBMASTER.MEMBGROUP_CODE, MBUCFPRENAME.PRENAME_SHORT, MBUCFMEMBGROUP.MEMBGROUP_DESC, MBMEMBMASTER.BIRTH_DATE, MBMEMBMASTER.MEMBER_DATE, MBMEMBMASTER.card_person FROM MBMEMBMASTER, MBUCFMEMBGROUP, MBUCFPRENAME WHERE ( MBUCFMEMBGROUP.MEMBGROUP_CODE = MBMEMBMASTER.MEMBGROUP_CODE ) and ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and ( ( MBMEMBMASTER.MEMBER_NO = '" + memberNo + "' ) )"; Sdt dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dwMain.SetItemString(1, "full_name", dt.GetString("PRENAME_SHORT") + dt.GetString("memb_name") + " " + dt.GetString("memb_surname")); dwMain.SetItemString(1, "membgroup_code", dt.GetString("MEMBGROUP_CODE")); dwMain.SetItemDateTime(1, "birth_date", dt.GetDate("birth_date")); dwMain.SetItemString(1, "person_card", dt.GetString("card_person")); int year = DateTime.Today.Year - dt.GetDate("birth_date").Year; try { //3. ไป select ข้อมูลหุ้นแล้วยิงใส่ dwMain String sqlShare = @" select sum( shsharemaster.sharestk_amt * shsharetype.share_value ) as summy from shsharemaster, shsharetype where ( shsharetype.sharetype_code = shsharemaster.sharetype_code ) and ( ( shsharemaster.member_no = '" + memberNo + "' ) )"; Sdt dtSumShare = ta.Query(sqlShare); if (dtSumShare.Next()) { //3.1 ยิงค่าใส่ dwMain dwMain.SetItemDecimal(1, "share_amt", dtSumShare.GetDecimal(0)); } else throw new Exception(); } catch { dwMain.SetItemDecimal(1, "share_amt", 0m); } try { //4. สร้าง DataStore สำหรับข้อมูลเงินกู้ dwLoan เพื่อหายอด sum ของเงินกู้ และ export xml เพื่อส่งไปให้หน้า UI DataStore dwLoan = new DataStore(pbl, "d_as_loandetail"); DwTrans sqlca = new DwTrans(connectionString); sqlca.Connect(); try { dwLoan.SetTransaction(sqlca); dwLoan.Retrieve(memberNo); if (dwLoan.RowCount > 0) { //4.1 หายอด sum เพื่อยิงใส่ dwMain decimal loanAmt = Convert.ToDecimal(dwLoan.Describe("evaluate( 'sum( principal_balance for all )', " + dwLoan.RowCount + " )")); dwMain.SetItemDecimal(1, "loan_amt", loanAmt); //4.2 export xml ใส่ใน array ช่อง 1 เพื่อส่งกลับให้ UI resu[1] = dwLoan.Describe("DataWindow.Data.XML"); } else throw new Exception(); } catch (Exception exLoan) { sqlca.Disconnect(); throw exLoan; } sqlca.Disconnect(); } catch { dwMain.SetItemDecimal(1, "loan_amt", 0m); resu[1] = ""; } //5. เมื่อยิงค่าตั้งต้นของ dwMain ครบแล้วให้ export xml ของ dwMain ส่งไปให้หน้า UI resu[0] = dwMain.Describe("DataWindow.Data.XML"); ta.Close(); } else { throw new Exception("ไม่มีสมาชิก " + memberNo + " อยู่ในระบบ"); } } catch (Exception ex) { try { ta.Close(); } catch { } throw ex; } return resu; } // doy save. ใบสมัคร public int SaveInsuRequestNew(String pbl, DateTime workDate, String xmlInsuRequest) { Sta ta = new Sta(connectionString); try { ta.Transection(); DataStore dwMain = new DataStore(pbl, "d_as_insureqnew"); dwMain.ImportString(xmlInsuRequest, FileSaveAsType.Xml); string docNo = ""; docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSAPPLDOCNO, workDate.Year + 543, ta); dwMain.SetItemString(1, "insreqdoc_no", docNo); String sqlInsert = new DwHandle(dwMain).SqlInsertSyntax("INSREQNEW", 1); String sqlUpdate = new DwHandle(dwMain).SqlUpdateSyntax("INSREQNEW", 1); ta.Exe(sqlInsert); ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } // a save.อนุมัติสมัครประกัน public int SaveAppinsnew(String member_no) { Sta ta = new Sta(connectionString); try { ta.Transection(); String sqlStr, instype_code, level_code, insgroupdoc_no, remark, insreqdoc_no, kpmembno, marrige_name, insgroupno_ref, person_card; DateTime insgroup_date, insreq_date, inswd_date, resign_date, process_date, mbdead_date, senddoc_date, compaccept_date, startsafe_date, endsafe_date; Decimal inscost_blance, insperod_payment, insstk_blance, last_period, insmemb_status, last_stm_no, inspayment_arrear, inspayment_status, loan_amt, share_amt, misspay_amt, afine_blance, loanreq_amt, first_period, inskpothers_status, insmemb_type, company_no, periodpay_amt, inspayment_type, insreturn_amt, inspayment_amt, insgroup_id; Sdt dt1 = new Sdt(); Sdt dt2 = new Sdt(); sqlStr = @" SELECT INSREQDOC_NO, MEMBER_NO, MEMBGROUP_CODE, INREQ_DATE, INSAPROVE_DATE, INSREQCOST_AMT, INSAPCOST_AMT, EX_STATUS1, EX_STATUS2, EX_STATUS3, EX_STATUS4, LOAN_AMT, SHARE_AMT, ENTRY_ID, ENTRY_DATE, INSREQ_STATUS, INSGROUPDOC_NO, APPROVE_ID, INSTYPE_CODE, INSPERIOD_PAYMENT, COOPBRANCH_ID, BIRTH_DATE, MEMBER_DATE, AGE, INSLEVEL_CODE, LOANREQ_AMT, MARRIGE_NAME, INSGROUPNO_REF, INSMEMB_TYPE, PERSON_CARD, INSPAYMENT_ARREAR, INSPAYMENT_AMT FROM INSREQNEW WHERE MEMBER_NO = '" + member_no + "' "; dt1 = ta.Query(sqlStr); dt1.Next(); try { insreqdoc_no = dt1.GetString("INSREQDOC_NO"); } catch { insreqdoc_no = "0000000"; } insgroupdoc_no = member_no;//dt1.GetString("INSGROUPDOC_NO"); try { instype_code = dt1.GetString("INSTYPE_CODE"); } catch { instype_code = "01"; }; try { level_code = dt1.GetString("INSLEVEL_CODE"); } catch { level_code = ""; } try { person_card = dt1.GetString("PERSON_CARD"); } catch { person_card = ""; } try { inscost_blance = dt1.GetDecimal("INSREQCOST_AMT"); } catch { inscost_blance = 0; } try { loan_amt = dt1.GetDecimal("LOAN_AMT"); } catch { loan_amt = 0; } try { share_amt = dt1.GetDecimal("SHARE_AMT"); } catch { share_amt = 0; } try { loanreq_amt = dt1.GetDecimal("LOANREQ_AMT"); } catch { loanreq_amt = 0; } try { inspayment_arrear = dt1.GetDecimal("INSPAYMENT_ARREAR"); } catch { inspayment_arrear = 0; } try { inspayment_amt = dt1.GetDecimal("INSPAYMENT_AMT"); } catch { inspayment_amt = 0; } try { periodpay_amt = dt1.GetDecimal("INSPERIOD_PAYMENT"); } catch { periodpay_amt = 0; } try { insreq_date = dt1.GetDate("INREQ_DATE"); } catch { insreq_date = DateTime.Now; } try { marrige_name = dt1.GetString("MARRIGE_NAME"); } catch { marrige_name = ""; } try { insmemb_type = dt1.GetDecimal("INSMEMB_TYPE"); } catch { insmemb_type = 1; } try { insgroup_date = dt1.GetDate("INSAPROVE_DATE"); } catch { insgroup_date = DateTime.Now; } sqlStr = "select max(insgroup_id)as insgroup_id from insgroupmaster"; dt2 = ta.Query(sqlStr); dt2.Next(); insgroup_id = dt2.GetDecimal("insgroup_id") + 1; sqlStr = @"insert into insgroupmaster (member_no, insreqdoc_no, insgroupdoc_no, instype_code, level_code , person_card , inscost_blance , loan_amt , share_amt , loanreq_amt, inspayment_arrear , inspayment_amt , periodpay_amt , insreq_date , marrige_name ,insmemb_type, insgroup_date ,insgroup_id) values( '" + member_no + @"', '" + insreqdoc_no + @"', '" + insgroupdoc_no + @"', '" + instype_code + @"', '" + level_code + @"', '" + person_card + @"', " + inscost_blance + @", " + loan_amt + @", " + share_amt + @", " + loanreq_amt + @", " + inspayment_arrear + @", " + inspayment_amt + @", " + periodpay_amt + @", to_date('" + insreq_date.ToString("dd/MM/yyyy") + @"', 'dd/mm/yyyy'), '" + marrige_name + @"', " + insmemb_type + @", to_date('" + insgroup_date.ToString("dd/MM/yyyy") + @"', 'dd/mm/yyyy'), " + insgroup_id + @")"; // sqlStr = @" INSERT INTO INSGROUPMASTER // ( MEMBER_NO, INSTYPE_CODE, LEVEL_CODE, INSGROUPDOC_NO, INSGROUP_DATE, INSREQ_DATE, INSWD_DATE, INSCOST_BLANCE, INSPEROD_PAYMENT, INSSTK_BLANCE, // LAST_PERIOD, INSMEMB_STATUS, RESIGN_DATE, LAST_STM_NO, INSPAYMENT_ARREAR, INSPAYMENT_STATUS, LOAN_AMT, SHARE_AMT, REMARK, PROCESS_DATE, // MISSPAY_AMT, INSREQDOC_NO, MBDEAD_DATE, SENDDOC_DATE, COMPACCEPT_DATE, AFINE_BLANCE, LOANREQ_AMT, FIRST_PERIOD, STARTSAFE_DATE, ENDSAFE_DATE, // INSKPOTHERS_STATUS, KPMEMBNO, MARRIGE_NAME, INSMEMB_TYPE, INSGROUPNO_REF, PERSON_CARD, COMPANY_NO, PERIODPAY_AMT, INSPAYMENT_TYPE, INSRETURN_AMT, // INSPAYMENT_AMT, INSGROUP_ID ) // VALUES ( 'member_no', 'instype_code', 'level_code', 'insgroupdoc_no', insgroup_date, insreq_date, inswd_date, // inscost_blance, insperod_payment, insstk_blance, last_period, insmemb_status, resign_date, last_stm_no, // inspayment_arrear, inspayment_status, loan_amt, share_amt,'remark', process_date, // misspay_amt, 'insreqdoc_no', mbdead_date, senddoc_date, compaccept_date, // afine_blance, // loanreq_amt, // first_period, // startsafe_date, //endsafe_date, // inskpothers_status, // 'kpmembno', // 'marrige_name', // insmemb_type, // 'insgroupno_ref', // 'person_card', // company_no, // periodpay_amt, // inspayment_type, //insreturn_amt, // inspayment_amt, // insgroup_id ) "; ta.Exe(sqlStr); ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } // Wu Save.ใบสมัคร และ แก้ไขข้อมูลประกัน public int SaveInsDetail(String pbl, String xmlInsMaster, String xmlInsStmt, String xmlInsGain, String new_status, String rowModify, Decimal oldInsPayment) { String sqlText; int li_row, li_rowcount; Sta ta = new Sta(connectionString); try { ta.Transection(); //Import DataWindow to DataStore DataStore dw_insmaster = new DataStore(pbl, "d_sk_prakunmaster"); dw_insmaster.ImportString(xmlInsMaster, FileSaveAsType.Xml); DataStore dw_insstmt = new DataStore(pbl, "d_sk_prakunstmt"); dw_insstmt.ImportString(xmlInsStmt, FileSaveAsType.Xml); DataStore dw_insgain = new DataStore(pbl, "d_sk_prakungain"); dw_insgain.ImportString(xmlInsGain, FileSaveAsType.Xml); //check save '0'=Update , '1'=Insert if (new_status == "0") { //---- UPDATE --------------- sqlText = new DwHandle(dw_insmaster).SqlUpdateSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); Decimal ldc_insgpid = dw_insmaster.GetItemDecimal(1, "insgroup_id"); // insgroupstatement :'true'= RowChange if (getBitVar(rowModify, 1)) { sqlText = "DELETE FROM INSGROUPSTATEMENT WHERE INSGROUP_ID = " + ldc_insgpid.ToString().Trim(); ta.Exe(sqlText); li_rowcount = dw_insstmt.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insstmt).SqlInsertSyntax("INSGROUPSTATEMENT", li_row); ta.Exe(sqlText); } } else //getBitVar(rowModify, 1) == false { li_rowcount = dw_insstmt.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insstmt).SqlUpdateSyntax("INSGROUPSTATEMENT", 1); ta.Exe(sqlText); } } // insgroupgain :'true'= RowChange if (getBitVar(rowModify, 2)) { sqlText = "DELETE FROM INSGROUPGAIN WHERE INSGROUP_ID = " + ldc_insgpid.ToString().Trim(); ta.Exe(sqlText); li_rowcount = dw_insgain.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insgain).SqlInsertSyntax("INSGROUPGAIN", li_row); ta.Exe(sqlText); } } else //getBitVar(rowModify, 2) == false { li_rowcount = dw_insgain.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insgain).SqlUpdateSyntax("INSGROUPGAIN", 1); ta.Exe(sqlText); } } // insreqchggroup :'true' = Insert if (getBitVar(rowModify, 3)) { //---- INSERT INSREQCHGGROUP----- Sdt dt; String ls_docno, ls_memberno, ls_instype, ls_olevel, ls_nlevel, ls_entryid, ls_insdoc; Decimal ldc_oBlance, ldc_nBlance, ldc_opay, ldc_npay; DateTime ldtm_operate, ldtm_safe; CultureInfo lci_machine = CultureInfo.CurrentCulture; ldtm_operate = getDateTimeCulture(DateTime.Today.Day, DateTime.Today.Month, DateTime.Today.Year, lci_machine); try { ldtm_safe = dw_insmaster.GetItemDateTime(1, "startsafe_date"); } catch { ldtm_safe = Convert.ToDateTime("01/01/1900"); } ls_docno = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCHGDOCNO, DateTime.Today.Year + 543, ta); ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); ls_instype = dw_insmaster.GetItemString(1, "instype_code").Trim(); try { ls_insdoc = dw_insmaster.GetItemString(1, "insgroupdoc_no").Trim(); } catch { ls_insdoc = "-"; } try { ls_nlevel = dw_insmaster.GetItemString(1, "level_code").Trim(); } catch { ls_nlevel = ""; } try { ldc_nBlance = dw_insmaster.GetItemDecimal(1, "inscost_blance"); } catch { ldc_nBlance = 0; } ldc_opay = oldInsPayment; try { ldc_npay = dw_insmaster.GetItemDecimal(1, "inspayment_amt"); } catch { ldc_npay = 0; } sqlText = "SELECT LEVEL_CODE,MAXINSCOST_AMT FROM INSLEVELCOST WHERE INSPERIOD_PAYMENT = " + ldc_opay.ToString().Trim() + " AND INSTYPE_CODE = '" + ls_instype + "'"; dt = ta.Query(sqlText); //if (dt.Rows.Count > 0) if (dt.Next()) { ls_olevel = dt.GetString("LEVEL_CODE"); ldc_oBlance = dt.GetDecimal("MAXINSCOST_AMT"); } else { ls_olevel = ""; ldc_oBlance = 0; } ls_entryid = "admin"; sqlText = "INSERT INTO INSREQCHGGROUP " + "( INSREQCHGDOC_NO, MEMBER_NO, INSTYPE_CODE, OPERATE_DATE, " + "OLD_LEVELGROUP, NEW_LEVELGROUP, ENTRY_DATE, ENTRY_ID, " + "COOPBRANCH_ID, INSCOST_OLD, INSCOST_NEW, INSGROUPDOC_NO, " + "OLD_PERIODPAY, NEW_PERIODPAY, APPROVE_PROMTLY, INSREQCHG_STATUS, " + "OLDSAFE_DATE, NEWSAFE_DATE, APPROVE_DATE, APPLTYPE_CODE, INSGROUP_ID ) VALUES (" + "'" + ls_docno + "','" + ls_memberno + "','" + ls_instype + "'," + "to_date('" + ldtm_operate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "'" + ls_olevel + "','" + ls_nlevel + "',sysdate,'" + ls_entryid + "'," + "null," + ldc_oBlance.ToString().Trim() + "," + ldc_nBlance.ToString().Trim() + "," + "'" + ls_insdoc + "'," + ldc_opay.ToString().Trim() + "," + ldc_npay.ToString().Trim() + "," + "null,1,to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_operate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy'),'02'," + ldc_insgpid + ")"; ta.Exe(sqlText); } //--------------------------- } else //new_status == "1" (Insert) { //---- INSERT --------------- //-- INSREQNEW String ls_memberno, ls_instype, ls_level, ls_marriggeName, ls_insdoc, ls_personcard; String ls_entyrid, ls_docNo, ls_applcode; Decimal ldc_insreqcost, ldc_insmemb, ldc_inspayment, ldc_insgpid; DateTime ldtm_insdate, ldtm_birth, ldtm_receive, ldtm_safe;//, ldtm_effective; ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); CultureInfo lci_machine = CultureInfo.CurrentCulture; ldtm_insdate = dw_insmaster.GetItemDateTime(1, "insgroup_date"); ldtm_insdate = getDateTimeCulture(ldtm_insdate.Day, ldtm_insdate.Month, ldtm_insdate.Year, lci_machine); try { ldc_insreqcost = dw_insmaster.GetItemDecimal(1, "inscost_blance"); } catch { ldc_insreqcost = 0; } try { ls_insdoc = dw_insmaster.GetItemString(1, "insgroupdoc_no").Trim(); } catch { ls_insdoc = "-"; } try { ls_applcode = dw_insmaster.GetItemString(1, "appltype_code").Trim(); } catch { ls_applcode = ""; } ls_instype = dw_insmaster.GetItemString(1, "instype_code").Trim(); try { ldtm_birth = dw_insmaster.GetItemDateTime(1, "birth_date"); ldtm_birth = getDateTimeCulture(ldtm_birth.Day, ldtm_birth.Month, ldtm_birth.Year, lci_machine); } catch { ldtm_birth = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } try { ldtm_receive = dw_insmaster.GetItemDateTime(1, "receive_date"); ldtm_receive = getDateTimeCulture(ldtm_receive.Day, ldtm_receive.Month, ldtm_receive.Year, lci_machine); } catch { ldtm_receive = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } try { ldtm_safe = dw_insmaster.GetItemDateTime(1, "startsafe_date"); ldtm_safe = getDateTimeCulture(ldtm_safe.Day, ldtm_safe.Month, ldtm_safe.Year, lci_machine); } catch { ldtm_safe = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } // สมัครใจไม่มี 180 วัน //try //{ // ldtm_effective = dw_insmaster.GetItemDateTime(1, "effective_date"); //} //catch { ldtm_effective = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } try { ls_level = dw_insmaster.GetItemString(1, "level_code").Trim(); } catch { ls_level = ""; } ls_marriggeName = dw_insmaster.GetItemString(1, "marrige_name").Trim(); ldc_insmemb = dw_insmaster.GetItemDecimal(1, "insmemb_type"); try { ls_personcard = dw_insmaster.GetItemString(1, "person_card").Trim(); } catch { ls_personcard = ""; } try { ldc_inspayment = dw_insmaster.GetItemDecimal(1, "inspayment_amt"); } catch { ldc_inspayment = 0; } ldc_insgpid = dw_insmaster.GetItemDecimal(1, "insgroup_id"); ls_docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSAPPLDOCNO, DateTime.Today.Year + 543, ta); ls_entyrid = "admin"; sqlText = "INSERT INTO INSREQNEW " + "( INSREQDOC_NO, MEMBER_NO, INREQ_DATE, INSAPROVE_DATE, INSREQCOST_AMT, " + "INSAPCOST_AMT, EX_STATUS1, EX_STATUS2, EX_STATUS3, EX_STATUS4, ENTRY_ID, ENTRY_DATE, " + "INSREQ_STATUS, INSGROUPDOC_NO, APPROVE_ID, INSTYPE_CODE, INSPERIOD_PAYMENT, " + //"BIRTH_DATE, RECEIVE_DATE, SAFE_DATE, EFFECTIVE_DATE ,INSLEVEL_CODE, MARRIGE_NAME, " + "BIRTH_DATE, RECEIVE_DATE, SAFE_DATE ,INSLEVEL_CODE, MARRIGE_NAME, " + "INSGROUPNO_REF, INSMEMB_TYPE, PERSON_CARD, INSPAYMENT_AMT, APPLTYPE_CODE, INSGROUP_ID ) " + "VALUES ('" + ls_docNo + "','" + ls_memberno + "'," + "to_date('" + ldtm_insdate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_insdate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insreqcost.ToString().Trim() + "," + ldc_insreqcost.ToString().Trim() + ",1,1,1,1," + "'" + ls_entyrid + "',sysdate,1,'" + ls_insdoc + "','" + ls_entyrid + "','" + ls_instype + "',null," + "to_date('" + ldtm_birth.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_receive.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + //"to_date('" + ldtm_effective.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "'" + ls_level + "','" + ls_marriggeName + "',null," + ldc_insmemb.ToString().Trim() + ",'" + ls_personcard + "'," + ldc_inspayment.ToString().Trim() + ",'" + ls_applcode + "'," + ldc_insgpid.ToString().Trim() + ")"; ta.Exe(sqlText); //-- INSGROUPMASTER sqlText = new DwHandle(dw_insmaster).SqlInsertSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); li_rowcount = dw_insgain.RowCount; if (getBitVar(rowModify, 2)) { for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insgain).SqlInsertSyntax("INSGROUPGAIN", li_row); ta.Exe(sqlText); } } //--------------------------- } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } public int SaveInsHealth(String pbl, String xmlInsMaster, String xmlInsStmt, String new_status, String rowModify, Decimal oldInsPayment) { String sqlText; int li_row, li_rowcount; Sta ta = new Sta(connectionString); try { ta.Transection(); //Import DataWindow to DataStore DataStore dw_insmaster = new DataStore(pbl, "d_health_prakunmaster"); dw_insmaster.ImportString(xmlInsMaster, FileSaveAsType.Xml); DataStore dw_insstmt = new DataStore(pbl, "d_health_prakunstmt"); dw_insstmt.ImportString(xmlInsStmt, FileSaveAsType.Xml); //DataStore dw_insgain = new DataStore(pbl, "d_sk_prakungain"); //dw_insgain.ImportString(xmlInsGain, FileSaveAsType.Xml); //check save '0'=Update , '1'=Insert if (new_status == "0") { //---- UPDATE --------------- sqlText = new DwHandle(dw_insmaster).SqlUpdateSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); Decimal ldc_insgpid = dw_insmaster.GetItemDecimal(1, "insgroup_id"); // insgroupstatement :'true'= RowChange if (getBitVar(rowModify, 1)) { sqlText = "DELETE FROM INSGROUPSTATEMENT WHERE INSGROUP_ID = " + ldc_insgpid.ToString().Trim(); ta.Exe(sqlText); li_rowcount = dw_insstmt.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insstmt).SqlInsertSyntax("INSGROUPSTATEMENT", li_row); ta.Exe(sqlText); } } else //getBitVar(rowModify, 1) == false { li_rowcount = dw_insstmt.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { sqlText = new DwHandle(dw_insstmt).SqlUpdateSyntax("INSGROUPSTATEMENT", 1); ta.Exe(sqlText); } } // insgroupgain :'true'= RowChange //if (getBitVar(rowModify, 2)) //{ // sqlText = "DELETE FROM INSGROUPGAIN WHERE INSGROUP_ID = " + ldc_insgpid.ToString().Trim(); // ta.Exe(sqlText); // li_rowcount = dw_insgain.RowCount; // for (li_row = 1; li_row <= li_rowcount; li_row++) // { // sqlText = new DwHandle(dw_insgain).SqlInsertSyntax("INSGROUPGAIN", li_row); // ta.Exe(sqlText); // } //} //else //getBitVar(rowModify, 2) == false //{ // li_rowcount = dw_insgain.RowCount; // for (li_row = 1; li_row <= li_rowcount; li_row++) // { // sqlText = new DwHandle(dw_insgain).SqlUpdateSyntax("INSGROUPGAIN", 1); // ta.Exe(sqlText); // } //} // insreqchggroup :'true' = Insert if (getBitVar(rowModify, 3)) { //---- INSERT INSREQCHGGROUP----- //Sdt dt; //String ls_docno, ls_memberno, ls_instype, ls_olevel, ls_nlevel, ls_entryid, ls_insdoc; //Decimal ldc_oBlance, ldc_nBlance, ldc_opay, ldc_npay; //DateTime ldtm_operate, ldtm_safe; //CultureInfo lci_machine = CultureInfo.CurrentCulture; //ldtm_operate = getDateTimeCulture(DateTime.Today.Day, DateTime.Today.Month, DateTime.Today.Year, lci_machine); //try //{ // ldtm_safe = dw_insmaster.GetItemDateTime(1, "startsafe_date"); //} //catch { ldtm_safe = Convert.ToDateTime("01/01/1900"); } //ls_docno = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCHGDOCNO, DateTime.Today.Year + 543, ta); //ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); //ls_instype = dw_insmaster.GetItemString(1, "instype_code").Trim(); //try //{ // ls_insdoc = dw_insmaster.GetItemString(1, "insgroupdoc_no").Trim(); //} //catch { ls_insdoc = "-"; } //try //{ // ls_nlevel = dw_insmaster.GetItemString(1, "level_code").Trim(); //} //catch { ls_nlevel = ""; } //try //{ // ldc_nBlance = dw_insmaster.GetItemDecimal(1, "inscost_blance"); //} //catch { ldc_nBlance = 0; } //ldc_opay = oldInsPayment; //try //{ // ldc_npay = dw_insmaster.GetItemDecimal(1, "inspayment_amt"); //} //catch { ldc_npay = 0; } //sqlText = "SELECT LEVEL_CODE,MAXINSCOST_AMT FROM INSLEVELCOST WHERE INSPERIOD_PAYMENT = " + ldc_opay.ToString().Trim() + // " AND INSTYPE_CODE = '" + ls_instype + "'"; //dt = ta.Query(sqlText); ////if (dt.Rows.Count > 0) //if (dt.Next()) //{ // ls_olevel = dt.GetString("LEVEL_CODE"); // ldc_oBlance = dt.GetDecimal("MAXINSCOST_AMT"); //} //else //{ // ls_olevel = ""; // ldc_oBlance = 0; //} //ls_entryid = "admin"; //sqlText = "INSERT INTO INSREQCHGGROUP " + // "( INSREQCHGDOC_NO, MEMBER_NO, INSTYPE_CODE, OPERATE_DATE, " + // "OLD_LEVELGROUP, NEW_LEVELGROUP, ENTRY_DATE, ENTRY_ID, " + // "COOPBRANCH_ID, INSCOST_OLD, INSCOST_NEW, INSGROUPDOC_NO, " + // "OLD_PERIODPAY, NEW_PERIODPAY, APPROVE_PROMTLY, INSREQCHG_STATUS, " + // "OLDSAFE_DATE, NEWSAFE_DATE, APPROVE_DATE, APPLTYPE_CODE, INSGROUP_ID ) VALUES (" + // "'" + ls_docno + "','" + ls_memberno + "','" + ls_instype + "'," + // "to_date('" + ldtm_operate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + // "'" + ls_olevel + "','" + ls_nlevel + "',sysdate,'" + ls_entryid + "'," + // "null," + ldc_oBlance.ToString().Trim() + "," + ldc_nBlance.ToString().Trim() + "," + // "'" + ls_insdoc + "'," + ldc_opay.ToString().Trim() + "," + ldc_npay.ToString().Trim() + "," + // "null,1,to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + // "to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + // "to_date('" + ldtm_operate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy'),'02'," + // ldc_insgpid + ")"; //ta.Exe(sqlText); } //--------------------------- } else //new_status == "1" (Insert) { //---- INSERT --------------- //-- INSREQNEW String ls_memberno, ls_instype, ls_level, ls_marriggeName, ls_insdoc, ls_personcard; String ls_entyrid, ls_docNo, ls_applcode; Decimal ldc_insreqcost, ldc_insmemb, ldc_inspayment, ldc_insgpid; DateTime ldtm_insdate, ldtm_birth, ldtm_receive, ldtm_safe;//, ldtm_effective; ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); CultureInfo lci_machine = CultureInfo.CurrentCulture; ldtm_insdate = dw_insmaster.GetItemDateTime(1, "insgroup_date"); ldtm_insdate = getDateTimeCulture(ldtm_insdate.Day, ldtm_insdate.Month, ldtm_insdate.Year, lci_machine); try { ldc_insreqcost = dw_insmaster.GetItemDecimal(1, "inscost_blance"); } catch { ldc_insreqcost = 0; } try { ls_insdoc = dw_insmaster.GetItemString(1, "insgroupdoc_no").Trim(); } catch { ls_insdoc = "-"; } try { ls_applcode = dw_insmaster.GetItemString(1, "appltype_code").Trim(); } catch { ls_applcode = ""; } ls_instype = dw_insmaster.GetItemString(1, "instype_code").Trim(); try { ldtm_birth = dw_insmaster.GetItemDateTime(1, "birth_date"); ldtm_birth = getDateTimeCulture(ldtm_birth.Day, ldtm_birth.Month, ldtm_birth.Year, lci_machine); } catch { ldtm_birth = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } try { ldtm_receive = dw_insmaster.GetItemDateTime(1, "receive_date"); ldtm_receive = getDateTimeCulture(ldtm_receive.Day, ldtm_receive.Month, ldtm_receive.Year, lci_machine); } catch { ldtm_receive = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } try { ldtm_safe = dw_insmaster.GetItemDateTime(1, "startsafe_date"); ldtm_safe = getDateTimeCulture(ldtm_safe.Day, ldtm_safe.Month, ldtm_safe.Year, lci_machine); } catch { ldtm_safe = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } // สมัครใจไม่มี 180 วัน //try //{ // ldtm_effective = dw_insmaster.GetItemDateTime(1, "effective_date"); //} //catch { ldtm_effective = new DateTime(); }//Convert.ToDateTime("01/01/1900"); } try { ls_level = dw_insmaster.GetItemString(1, "level_code").Trim(); } catch { ls_level = ""; } ls_marriggeName = dw_insmaster.GetItemString(1, "marrige_name").Trim(); ldc_insmemb = dw_insmaster.GetItemDecimal(1, "insmemb_type"); try { ls_personcard = dw_insmaster.GetItemString(1, "person_card").Trim(); } catch { ls_personcard = ""; } try { ldc_inspayment = dw_insmaster.GetItemDecimal(1, "inspayment_amt"); } catch { ldc_inspayment = 0; } ldc_insgpid = dw_insmaster.GetItemDecimal(1, "insgroup_id"); ls_docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSAPPLDOCNO, DateTime.Today.Year + 543, ta); ls_entyrid = "admin"; sqlText = "INSERT INTO INSREQNEW " + "( INSREQDOC_NO, MEMBER_NO, INREQ_DATE, INSAPROVE_DATE, INSREQCOST_AMT, " + "INSAPCOST_AMT, EX_STATUS1, EX_STATUS2, EX_STATUS3, EX_STATUS4, ENTRY_ID, ENTRY_DATE, " + "INSREQ_STATUS, INSGROUPDOC_NO, APPROVE_ID, INSTYPE_CODE, INSPERIOD_PAYMENT, " + //"BIRTH_DATE, RECEIVE_DATE, SAFE_DATE, EFFECTIVE_DATE ,INSLEVEL_CODE, MARRIGE_NAME, " + "BIRTH_DATE, RECEIVE_DATE, SAFE_DATE ,INSLEVEL_CODE, MARRIGE_NAME, " + "INSGROUPNO_REF, INSMEMB_TYPE, PERSON_CARD, INSPAYMENT_AMT, APPLTYPE_CODE, INSGROUP_ID ) " + "VALUES ('" + ls_docNo + "','" + ls_memberno + "'," + "to_date('" + ldtm_insdate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_insdate.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insreqcost.ToString().Trim() + "," + ldc_insreqcost.ToString().Trim() + ",1,1,1,1," + "'" + ls_entyrid + "',sysdate,1,'" + ls_insdoc + "','" + ls_entyrid + "','" + ls_instype + "',null," + "to_date('" + ldtm_birth.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_receive.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + //"to_date('" + ldtm_effective.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "'" + ls_level + "','" + ls_marriggeName + "',null," + ldc_insmemb.ToString().Trim() + ",'" + ls_personcard + "'," + ldc_inspayment.ToString().Trim() + ",'" + ls_applcode + "'," + ldc_insgpid.ToString().Trim() + ")"; ta.Exe(sqlText); //-- INSGROUPMASTER sqlText = new DwHandle(dw_insmaster).SqlInsertSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); //li_rowcount = dw_insgain.RowCount; //if (getBitVar(rowModify, 2)) //{ // for (li_row = 1; li_row <= li_rowcount; li_row++) // { // sqlText = new DwHandle(dw_insgain).SqlInsertSyntax("INSGROUPGAIN", li_row); // ta.Exe(sqlText); // } //} //--------------------------- } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } public int ForceIns(String pbl, String xmlInsLoan, String as_objectName, DateTime adtm_operate) { progress = new pbservice.str_progress(); String sqltext; Sdt dt; int li_row, li_rowcount; Decimal ldc_flag, ldc_ins, ldc_loanapp, ldc_loannorm, ldc_loanextra, ldc_insmemb, ldc_debt, ldc_insreqcost, ldc_inspayment, ldc_insgpid, ldc_loanamt, ldc_shareamt; String ls_memberno, ls_instype, ls_entryid, ls_insdoc, ls_pname, ls_name, ls_sname, ls_marriggename, ls_docno, ls_level, ls_personcard; DateTime ldtm_strcont, ldtm_birth, ldtm_safe; progress.status = 8; Sta ta = new Sta(connectionString); try { ta.Transection(); //Import DataWindow to DataStore DataStore dw_list = new DataStore(pbl, as_objectName); //DataStore dw_list = new DataStore(pbl, "d_ins_forcenew_data"); dw_list.ImportString(xmlInsLoan, FileSaveAsType.Xml); ls_entryid = "admin"; ls_insdoc = "-"; ls_instype = "01"; ldc_insmemb = 1; li_rowcount = dw_list.RowCount; progress.progress_max = li_rowcount; progress.subprogress_max = li_rowcount; for (li_row = 1; li_row <= li_rowcount; li_row++) { progress.progress_index = li_row; progress.subprogress_index = li_row; ldc_flag = dw_list.GetItemDecimal(li_row, "operate_flag"); if (ldc_flag == 1) { /// --- Get Data From DataWindow --------------------------- ls_memberno = dw_list.GetItemString(li_row, "member_no").Trim(); ldc_loanapp = dw_list.GetItemDecimal(li_row, "loanapprove_amt"); ldtm_strcont = dw_list.GetItemDateTime(li_row, "startcont_date"); //ldtm_strcont = adtm_operate; try { ldc_ins = dw_list.GetItemDecimal(li_row, "ins_amt"); } catch { ldc_ins = 0; } try { ldc_loannorm = dw_list.GetItemDecimal(li_row, "normloan_amt"); } catch { ldc_loannorm = 0; } try { ldc_loanextra = dw_list.GetItemDecimal(li_row, "extraloan_amt"); } catch { ldc_loanextra = 0; } //ldc_debt = ldc_loannorm + ldc_loanextra + ldc_loanapp; /// Get Loan Data------------------------------------ if (as_objectName == "d_ins_forcenew_data_extra") { ldc_loanamt = ldc_loannorm; } else { ldc_loanamt = ldc_loanapp; } ldc_shareamt = ldc_loanextra; /// Get Loan Data------------------------------------ try { ldc_debt = dw_list.GetItemDecimal(li_row, "debt_amt"); } catch { ldc_debt = 0; } try { ldtm_birth = dw_list.GetItemDateTime(li_row, "birth_date"); } catch { ldtm_birth = new DateTime(1900, 1, 1); } try { ls_personcard = dw_list.GetItemString(li_row, "card_person"); } catch { ls_personcard = ""; } try { ls_pname = dw_list.GetItemString(li_row, "prename_desc").Trim(); } catch { ls_pname = "นาย"; } try { ls_name = dw_list.GetItemString(li_row, "memb_name").Trim(); } catch { ls_name = " "; } try { ls_sname = dw_list.GetItemString(li_row, "memb_surname").Trim(); } catch { ls_sname = " "; } ls_marriggename = ls_pname + ls_name + " " + ls_sname; // Cover ldtm_safe = ldtm_strcont; sqltext = "SELECT MIN(MAXINSCOST_AMT) AS MAXINSCOST_AMT FROM INSLEVELCOST " + "WHERE INSTYPE_CODE = '" + ls_instype + "' " + "AND MAXINSCOST_AMT >= " + ldc_debt.ToString().Trim(); dt = ta.Query(sqltext); //1 //if (dt.Next()) //{ // ldc_insreqcost = dt.GetDecimal("MAXINSCOST_AMT"); // sqltext = "SELECT LEVEL_CODE,INSPERIOD_PAYMENT FROM INSLEVELCOST WHERE MAXINSCOST_AMT = " + // ldc_insreqcost.ToString().Trim(); // dt = ta.Query(sqltext); // if (dt.Next()) // { // ls_level = dt.GetString("LEVEL_CODE"); // ldc_inspayment = dt.GetDecimal("INSPERIOD_PAYMENT"); // } //} //2 dt.Next(); ldc_insreqcost = dt.GetDecimal("MAXINSCOST_AMT"); sqltext = "SELECT LEVEL_CODE,INSPERIOD_PAYMENT FROM INSLEVELCOST " + "WHERE INSTYPE_CODE = '" + ls_instype + "' " + "AND MAXINSCOST_AMT = " + ldc_insreqcost.ToString().Trim(); dt = ta.Query(sqltext); dt.Next(); ls_level = dt.GetString("LEVEL_CODE"); ldc_inspayment = dt.GetDecimal("INSPERIOD_PAYMENT"); /// -------------------------------------------------------- if (ldc_ins == 0) { sqltext = "SELECT MAX(INSGROUP_ID) AS INSGROUP_ID FROM INSGROUPMASTER"; dt = ta.Query(sqltext); dt.Next(); ldc_insgpid = dt.GetDecimal("INSGROUP_ID"); ldc_insgpid = ldc_insgpid + 1; ls_docno = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSAPPLDOCNO, ldtm_strcont.Year + 543, ta); sqltext = "INSERT INTO INSREQNEW " + "( INSREQDOC_NO, MEMBER_NO, INREQ_DATE, INSAPROVE_DATE, " + "INSREQCOST_AMT, INSAPCOST_AMT, EX_STATUS1, EX_STATUS2, EX_STATUS3, EX_STATUS4, " + "ENTRY_ID, ENTRY_DATE, INSREQ_STATUS, INSGROUPDOC_NO, APPROVE_ID, INSTYPE_CODE, " + "INSPERIOD_PAYMENT, BIRTH_DATE, INSLEVEL_CODE, MARRIGE_NAME, INSGROUPNO_REF, " + "INSMEMB_TYPE, PERSON_CARD, INSPAYMENT_AMT, APPLTYPE_CODE, INSGROUP_ID ) " + "VALUES ('" + ls_docno + "','" + ls_memberno + "'," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insreqcost.ToString().Trim() + "," + ldc_insreqcost.ToString().Trim() + ",1,1,1,1," + "'" + ls_entryid + "',sysdate,1,'" + ls_insdoc + "','" + ls_entryid + "','" + ls_instype + "',null," + "to_date('" + ldtm_birth.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "'" + ls_level + "','" + ls_marriggename + "',null," + ldc_insmemb.ToString().Trim() + ",'" + ls_personcard + "'," + ldc_inspayment.ToString().Trim() + "," + "null," + ldc_insgpid.ToString().Trim() + ")"; ta.Exe(sqltext); sqltext = "INSERT INTO INSGROUPMASTER " + "( INSGROUP_ID, INSMEMBSEQ_NO, MEMBER_NO, INSTYPE_CODE, LEVEL_CODE, INSGROUPDOC_NO, " + "INSGROUP_DATE, INSREQ_DATE, INSCOST_BLANCE, INSPEROD_PAYMENT, INSMEMB_STATUS, " + "MARRIGE_NAME, INSMEMB_TYPE, INSPAYMENT_TYPE, INSPAYMENT_AMT, INSREQDOC_NO, " + "APPLTYPE_CODE, PERSON_CARD, LOAN_AMT, SHARE_AMT, " + "BIRTH_DATE, RECEIVE_DATE, EFFECTIVE_DATE, STARTSAFE_DATE) " + "VALUES (" + ldc_insgpid.ToString().Trim() + ",1,'" + ls_memberno + "','" + ls_instype + "','" + ls_level + "','-'," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insreqcost.ToString().Trim() + "," + ldc_inspayment.ToString().Trim() + ",1,'" + ls_marriggename + "'," + ldc_insmemb.ToString().Trim() + ",1," + ldc_inspayment.ToString().Trim() + ",'" + ls_docno + "','03','" + ls_personcard + "'," + ldc_loanamt.ToString().Trim() + "," + ldc_shareamt.ToString().Trim() + "," + "to_date('" + ldtm_birth.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')" + ")"; ta.Exe(sqltext); } else { String ls_olevel; Decimal ldc_oBlance, ldc_opay; sqltext = "SELECT INSGROUP_ID, LEVEL_CODE, INSCOST_BLANCE, INSPAYMENT_AMT " + "FROM INSGROUPMASTER WHERE INSMEMB_TYPE = 1 AND MEMBER_NO = '" + ls_memberno + "' " + "AND INSTYPE_CODE = '" + ls_instype + "'"; dt = ta.Query(sqltext); dt.Next(); ldc_insgpid = dt.GetDecimal("INSGROUP_ID"); ldc_oBlance = dt.GetDecimal("INSCOST_BLANCE"); ldc_opay = dt.GetDecimal("INSPAYMENT_AMT"); ls_olevel = dt.GetString("LEVEL_CODE"); ls_docno = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCHGDOCNO, ldtm_strcont.Year + 543, ta); sqltext = "INSERT INTO INSREQCHGGROUP " + "( INSREQCHGDOC_NO, MEMBER_NO, INSTYPE_CODE, OPERATE_DATE, " + "OLD_LEVELGROUP, NEW_LEVELGROUP, ENTRY_DATE, ENTRY_ID, " + "COOPBRANCH_ID, INSCOST_OLD, INSCOST_NEW, INSGROUPDOC_NO, " + "OLD_PERIODPAY, NEW_PERIODPAY, APPROVE_PROMTLY, INSREQCHG_STATUS, " + "OLDSAFE_DATE, NEWSAFE_DATE, APPROVE_DATE, INSGROUP_ID ) VALUES (" + "'" + ls_docno + "','" + ls_memberno + "','" + ls_instype + "'," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "'" + ls_olevel + "','" + ls_level + "',sysdate,'" + ls_entryid + "'," + "null," + ldc_oBlance.ToString().Trim() + "," + ldc_insreqcost.ToString().Trim() + "," + "'" + ls_insdoc + "'," + ldc_opay.ToString().Trim() + "," + ldc_inspayment.ToString().Trim() + "," + "null,1,to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_safe.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + ldtm_strcont.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insgpid + ")"; ta.Exe(sqltext); sqltext = "UPDATE INSGROUPMASTER SET " + "LEVEL_CODE = '" + ls_level + "', " + "INSCOST_BLANCE = " + ldc_insreqcost.ToString().Trim() + ", " + "INSPAYMENT_AMT = " + ldc_inspayment.ToString().Trim() + ", " + "INSPEROD_PAYMENT = " + ldc_inspayment.ToString().Trim() + ", " + "LOAN_AMT = " + ldc_loanamt.ToString().Trim() + ", " + "SHARE_AMT = " + ldc_shareamt.ToString().Trim() + ", " + "APPLTYPE_CODE = '04' " + "WHERE INSGROUP_ID = " + ldc_insgpid.ToString().Trim(); ta.Exe(sqltext); } } } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } progress.status = -1; progress.error_text = ex.Message; progress.progress_text = ex.Message; throw ex; } return 1; } private Boolean getBitVar(String as_var, int ai_bit) { Boolean lb_return; int li_valueBit, li_var, li_temp; // ค่าที่จะหาหลักบิท li_var = Convert.ToInt32(as_var); // ค่าของหลักบิทที่จะหา li_valueBit = Convert.ToInt32(Square(2, ai_bit - 1)); li_temp = li_var / li_valueBit; li_var = li_temp; li_temp = li_var % 2; if (li_temp == 1) { lb_return = true; } else { lb_return = false; } return lb_return; } private Decimal Square(Decimal adc_x, int li_y) { Decimal ldc_return = adc_x; if (li_y == 0) ldc_return = 1; for (int i = 1; i < li_y; i++) { ldc_return = ldc_return * adc_x; } return ldc_return; } // a init.ใบคำขอปิดประกัน public String[] InitInsuReqResign(String pbl, String xmlInsuReqResign) { String[] resu = new String[1]; resu[0] = ""; // resu[1] = ""; Sta ta = new Sta(connectionString); try { //1. สร้าง DataStore ตั้งชื่อว่า dwMain นำ XML จาก argument มา import ใส่ DataStore dwMain = new DataStore(); dwMain.LibraryList = pbl; dwMain.DataWindowObject = "d_ins_mainresign"; dwMain.Reset(); dwMain.ImportString(xmlInsuReqResign, FileSaveAsType.Xml); String memberNo = dwMain.GetItemString(1, "member_no"); //2. ไป select ข้อมูลจากทะเบียนสมาชิกใส่ Sdt แล้วนำค่าต่างๆ ไปยิงใส่ dwMain String sqlStr = @" SELECT MBMEMBMASTER.MEMBER_NO, MBMEMBMASTER.MEMB_NAME, MBMEMBMASTER.MEMB_SURNAME, MBMEMBMASTER.MEMBGROUP_CODE, MBUCFPRENAME.PRENAME_SHORT, MBUCFMEMBGROUP.MEMBGROUP_DESC, MBMEMBMASTER.BIRTH_DATE, MBMEMBMASTER.MEMBER_DATE, MBMEMBMASTER.MEMBER_STATUS, MBMEMBMASTER.card_person, MBUCFMEMBTYPE.MEMBTYPE_CODE, MBUCFMEMBTYPE.MEMBTYPE_DESC FROM MBMEMBMASTER, MBUCFMEMBGROUP, MBUCFPRENAME ,MBUCFMEMBTYPE WHERE ( MBUCFMEMBGROUP.MEMBGROUP_CODE = MBMEMBMASTER.MEMBGROUP_CODE ) and ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and ( MBMEMBMASTER.MEMBTYPE_CODE = MBUCFMEMBTYPE.MEMBTYPE_CODE ) and ( ( MBMEMBMASTER.MEMBER_NO = '" + memberNo + "' ) )"; Sdt dt = ta.Query(sqlStr); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dwMain.SetItemString(1, "member_no", dt.GetString("MEMBER_NO")); dwMain.SetItemString(1, "memb_name", dt.GetString("MEMB_NAME")); dwMain.SetItemString(1, "memb_surname", dt.GetString("MEMB_SURNAME")); dwMain.SetItemDate(1, "member_date", dt.GetDate("MEMBER_DATE")); dwMain.SetItemString(1, "membgroup_code", dt.GetString("MEMBGROUP_CODE")); dwMain.SetItemString(1, "membgroup_desc", dt.GetString("MEMBGROUP_DESC")); dwMain.SetItemString(1, "membtype_code", dt.GetString("MEMBTYPE_CODE")); dwMain.SetItemString(1, "membtype_desc", dt.GetString("MEMBTYPE_DESC")); dwMain.SetItemDecimal(1, "member_status", dt.GetDecimal("MEMBER_STATUS")); //3. เมื่อยิงค่าตั้งต้นของ dwMain ครบแล้วให้ export xml ของ dwMain ส่งไปให้หน้า UI resu[0] = dwMain.Describe("DataWindow.Data.XML"); ta.Close(); } else { throw new Exception("ไม่มีสมาชิก " + memberNo + " อยู่ในระบบ"); } } catch (Exception ex) { try { ta.Close(); } catch { } throw ex; } return resu; } // a save.ใบคำขอปิดประกัน public int SaveInsuReqResign(String pbl, DateTime workDate, String xmlInsuReqResign, String xmlInsuReqResigndel) { Sta ta = new Sta(connectionString); String sqlUpdate = "", sqlInsert = ""; Decimal REQRESIGN_STATUS = 1, INSCOST_BALANCE; DateTime RESIGN_DATE; String INSTYPE_CODE, MEMBER_NO, INSGROUPDOC_NO, LEVEL_CODE, OPERATE_DATE, MBDEAD_DATE, SENDDOC_DATE, INSSTK_BLANCE, INSRESIGN_CASE, REMARK, ENTRY_DATE, ENTRY_ID, COOPBRANCH_ID, AFINE_BLANCE, CLOSEINS_DATE, CLOSEINS_ID, COMPACCEPT_DATE, APVIMMEDIATE_FLAG, INSGROUP_ID; try { ta.Transection(); DataStore dwMain = new DataStore(pbl, "d_ins_mainresign"); DataStore dwdetail = new DataStore(pbl, "d_ins_resign_det"); dwMain.ImportString(xmlInsuReqResign, FileSaveAsType.Xml); dwdetail.ImportString(xmlInsuReqResigndel, FileSaveAsType.Xml); //dwMain.SetItemString(1, "insreqresign_no", docNo); //sqlInsert = new DwHandle(dwMain).SqlInsertSyntax("INSREQRESIGN", 1); //ta.Exe(sqlInsert); int row = dwdetail.RowCount; for (int i = 1; i <= row; i++) { Decimal ldc_operateflag = dwdetail.GetItemDecimal(i, "operate_flag"); if (ldc_operateflag == 1) { String INSREQRESIGN_NO = ""; INSREQRESIGN_NO = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCLDOCNO, DateTime.Today.Year + 543, ta); INSTYPE_CODE = dwdetail.GetItemString(i, "instype_code"); MEMBER_NO = dwdetail.GetItemString(i, "member_no"); INSGROUPDOC_NO = dwdetail.GetItemString(i, "insgroupdoc_no"); INSRESIGN_CASE = dwdetail.GetItemString(i, "insresign_case"); CultureInfo lci_machine = CultureInfo.CurrentCulture; RESIGN_DATE = dwdetail.GetItemDateTime(i, "resign_date"); RESIGN_DATE = getDateTimeCulture(RESIGN_DATE.Day, RESIGN_DATE.Month, RESIGN_DATE.Year, lci_machine); //----------------------- //System.Globalization.CultureInfo den = new System.Globalization.CultureInfo("en-US"); //String crrDate = DateTime.Now.ToString("dd/MM/yyyy"); //string[] str = crrDate.Split('/'); //DateTime d = Convert.ToDateTime(new DateTime(Convert.ToInt32(str[2]), Convert.ToInt32(str[1]), Convert.ToInt32(str[0])), den); //RESIGN_DATE = (RESIGN_DATE == workDate) ? d : RESIGN_DATE; //dwdetail.SetItemDateTime(i, "resign_date", RESIGN_DATE); //RESIGN_DATE = dwdetail.GetItemDateTime(i, "resign_tdate"); //----------------------- INSCOST_BALANCE = dwdetail.GetItemDecimal(i, "inscost_blance"); INSGROUP_ID = dwdetail.GetItemString(i, "insgroup_id"); Decimal operate_flag = dwdetail.GetItemDecimal(i, "operate_flag"); sqlInsert = @" INSERT INTO INSREQRESIGN ( INSREQRESIGN_NO, INSTYPE_CODE, MEMBER_NO, INSGROUPDOC_NO, LEVEL_CODE, OPERATE_DATE, MBDEAD_DATE, SENDDOC_DATE, INSCOST_BALANCE, INSSTK_BLANCE, INSRESIGN_CASE, REMARK, ENTRY_DATE, ENTRY_ID, REQRESIGN_STATUS, COOPBRANCH_ID, AFINE_BLANCE, CLOSEINS_DATE, CLOSEINS_ID, COMPACCEPT_DATE, APVIMMEDIATE_FLAG, INSGROUP_ID ) VALUES ( '" + INSREQRESIGN_NO + @"', '" + INSTYPE_CODE + @"', '" + MEMBER_NO + @"', '" + INSGROUPDOC_NO + @"', null, to_date('" + RESIGN_DATE.ToString("dd/MM/yyyy") + @"','dd/mm/yyyy'), null,null, " + INSCOST_BALANCE + @", null, '" + INSRESIGN_CASE + @"', null, sysdate, null, " + REQRESIGN_STATUS + @", null, null, null, null, null, null , " + INSGROUP_ID + ")"; ta.Exe(sqlInsert); dwdetail.SetItemDecimal(i, "insmemb_status", -1); sqlUpdate = new DwHandle(dwdetail).SqlUpdateSyntax("INSGROUPMASTER", i); ta.Exe(sqlUpdate); } } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } public String[] InitInsuSliDet(String pbl, String xmlInsuReqResign) { String[] resu = new String[1]; resu[0] = ""; // resu[1] = ""; Sta ta = new Sta(connectionString); try { //1. สร้าง DataStore ตั้งชื่อว่า dwMain นำ XML จาก argument มา import ใส่ DataStore dwMain = new DataStore(); dwMain.LibraryList = pbl; dwMain.DataWindowObject = "d_ins_slip_operate_etc"; dwMain.Reset(); dwMain.ImportString(xmlInsuReqResign, FileSaveAsType.Xml); String memberNo = dwMain.GetItemString(1, "member_no"); //2. ไป select ข้อมูลจากทะเบียนสมาชิกใส่ Sdt แล้วนำค่าต่างๆ ไปยิงใส่ dwMain String sql = @" SELECT MBMEMBMASTER.MEMBER_NO, MBMEMBMASTER.MEMB_NAME, MBMEMBMASTER.MEMB_SURNAME, MBMEMBMASTER.MEMBGROUP_CODE, MBUCFPRENAME.PRENAME_SHORT, MBUCFMEMBGROUP.MEMBGROUP_DESC, MBMEMBMASTER.BIRTH_DATE, MBMEMBMASTER.MEMBER_DATE, MBMEMBMASTER.card_person FROM MBMEMBMASTER, MBUCFMEMBGROUP, MBUCFPRENAME WHERE ( MBUCFMEMBGROUP.MEMBGROUP_CODE = MBMEMBMASTER.MEMBGROUP_CODE ) and ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and ( ( MBMEMBMASTER.MEMBER_NO = '" + memberNo + "' ) )"; Sdt dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dwMain.SetItemString(1, "prename_desc", dt.GetString("PRENAME_SHORT")); dwMain.SetItemString(1, "memb_name", dt.GetString("MEMB_NAME")); dwMain.SetItemString(1, "memb_surname", dt.GetString("MEMB_SURNAME")); dwMain.SetItemString(1, "membgroup_code", dt.GetString("MEMBGROUP_CODE")); //3. เมื่อยิงค่าตั้งต้นของ dwMain ครบแล้วให้ export xml ของ dwMain ส่งไปให้หน้า UI resu[0] = dwMain.Describe("DataWindow.Data.XML"); ta.Close(); } else { throw new Exception("ไม่มีสมาชิก " + memberNo + " อยู่ในระบบ"); } } catch (Exception ex) { try { ta.Close(); } catch { } throw ex; } return resu; } //-- By AOI 01.04.2011 public String[] InitInsuSliReq(String pbl, String xmlInsuReqResign) { String[] resu = new String[2]; resu[0] = ""; resu[1] = ""; Sta ta = new Sta(connectionString); try { //1. สร้าง DataStore ตั้งชื่อว่า dwMain นำ XML จาก argument มา import ใส่ DataStore dwMain = new DataStore(); dwMain.LibraryList = pbl; dwMain.DataWindowObject = "d_ins_slip_operate_main"; dwMain.Reset(); dwMain.ImportString(xmlInsuReqResign, FileSaveAsType.Xml); String memberNo = dwMain.GetItemString(1, "member_no"); //2. ไป select ข้อมูลจากทะเบียนสมาชิกใส่ Sdt แล้วนำค่าต่างๆ ไปยิงใส่ dwMain String sql = @" SELECT MBMEMBMASTER.MEMBER_NO, MBMEMBMASTER.MEMB_NAME, MBMEMBMASTER.MEMB_SURNAME, MBMEMBMASTER.MEMBGROUP_CODE, MBUCFPRENAME.PRENAME_SHORT, MBUCFMEMBGROUP.MEMBGROUP_DESC, MBMEMBMASTER.BIRTH_DATE, MBMEMBMASTER.MEMBER_DATE, MBMEMBMASTER.card_person FROM MBMEMBMASTER, MBUCFMEMBGROUP, MBUCFPRENAME WHERE ( MBUCFMEMBGROUP.MEMBGROUP_CODE = MBMEMBMASTER.MEMBGROUP_CODE ) and ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and ( ( MBMEMBMASTER.MEMBER_NO = '" + memberNo + "' ) )"; Sdt dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dwMain.SetItemString(1, "prename_desc", dt.GetString("PRENAME_SHORT")); dwMain.SetItemString(1, "memb_name", dt.GetString("MEMB_NAME")); dwMain.SetItemString(1, "memb_surname", dt.GetString("MEMB_SURNAME")); dwMain.SetItemString(1, "membgroup_code", dt.GetString("MEMBGROUP_CODE")); try { //4. สร้าง DataStore สำหรับข้อมูลเงินกู้ dwLoan เพื่อหายอด sum ของเงินกู้ และ export xml เพื่อส่งไปให้หน้า UI DataStore dwLoan = new DataStore(pbl, "d_ins_slip_operate_etc"); DwTrans sqlca = new DwTrans(connectionString); sqlca.Connect(); try { dwLoan.SetTransaction(sqlca); dwLoan.Retrieve(memberNo); if (dwLoan.RowCount > 0) { //4.1 หายอด sum เพื่อยิงใส่ dwMain //decimal loanAmt = Convert.ToDecimal(dwLoan.Describe("evaluate( 'sum( principal_balance for all )', " + dwLoan.RowCount + " )")); //dwMain.SetItemDecimal(1, "loan_amt", loanAmt); //4.2 export xml ใส่ใน array ช่อง 1 เพื่อส่งกลับให้ UI resu[1] = dwLoan.Describe("DataWindow.Data.XML"); //------- เพิ่มเติม ตัวเลขยอดเงินต้องรวมไว้เรียบร้อย 05.04.2011-09.52 Decimal cost = 0; for (int r = 0; r < dwLoan.RowCount; r++) { cost = (cost == 0) ? dwLoan.GetItemDecimal(r + 1, "insperod_payment") : cost + dwLoan.GetItemDecimal(r + 1, "insperod_payment"); } dwMain.SetItemDecimal(1, "slip_amt", cost); //----------------- End 05.04.2011-09.52 } else throw new Exception("ไม่พบข้อมูลหนี้ของสมาชิก " + memberNo + " ในระบบ"); } catch (Exception exLoan) { sqlca.Disconnect(); throw exLoan; } sqlca.Disconnect(); } catch { // dwMain.SetItemDecimal(1, "loan_amt", 0m); resu[1] = ""; } // ---- สร้างตัว Detail //3. เมื่อยิงค่าตั้งต้นของ dwMain ครบแล้วให้ export xml ของ dwMain ส่งไปให้หน้า UI resu[0] = dwMain.Describe("DataWindow.Data.XML"); ta.Close(); } else { throw new Exception("ไม่มีสมาชิก " + memberNo + " อยู่ในระบบ"); } } catch (Exception ex) { try { ta.Close(); } catch { } throw ex; } return resu; } // by aoi 01.04.2011 public int SaveInsuSliReq(String pbl, DateTime workDate, String xmlInsuReqResign, String xmlInsuSlipDet) { Sta ta = new Sta(connectionString); ta.Transection(); try { DataStore dwMain = new DataStore(pbl, "d_ins_slip_operate_main"); dwMain.ImportString(xmlInsuReqResign, FileSaveAsType.Xml); System.Globalization.CultureInfo den = new System.Globalization.CultureInfo("en-US"); String crrDate = DateTime.Now.ToString("dd/MM/yyyy"); string[] str = crrDate.Split('/'); DateTime d = Convert.ToDateTime(new DateTime(Convert.ToInt32(str[2]), Convert.ToInt32(str[1]), Convert.ToInt32(str[0])), den); string membno = dwMain.GetItemString(1, "member_no"); string entryid = dwMain.GetItemString(1, "entry_id"); string docNo = ""; string slipNo = ""; docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCLDOCNO, workDate.Year + 543, ta); dwMain.SetItemString(1, "document_no", docNo); dwMain.SetItemString(1, "slip_no", docNo); dwMain.SetItemDateTime(1, "entry_date", DateTime.Now); dwMain.SetItemDateTime(1, "slip_date", d); dwMain.SetItemDateTime(1, "operate_date", d); slipNo = docNo; String sqlInsert = new DwHandle(dwMain).SqlInsertSyntax("CMSHRLONSLIP", 1); ta.Exe(sqlInsert); DataStore dwDetail = new DataStore(pbl, "d_ins_slip_operate_etc"); dwDetail.ImportString(xmlInsuSlipDet, FileSaveAsType.Xml); int rw = dwDetail.RowCount; int[] k = new int[rw]; for (int m = 0; m < rw; m++) { Decimal chkBox = dwDetail.GetItemDecimal(m + 1, "totalcost"); if (chkBox == 1) { //-- แก้ไขเพิ่มเติม 03.04.2011-10.44 String typecode = dwDetail.GetItemString(m + 1, "instype_code"); String docCode = dwDetail.GetItemString(m + 1, "insgroupdoc_no"); String descrpt = dwDetail.GetItemString(m + 1, "inscompany_name"); Decimal pay = dwDetail.GetItemDecimal(m + 1, "insperod_payment"); Decimal remain = dwDetail.GetItemDecimal(m + 1, "inspayment_arrear"); Decimal insstk_balance = dwDetail.GetItemDecimal(m + 1, "insstk_blance"); Decimal stm_no = dwDetail.GetItemDecimal(m + 1, "last_stm_no"); Decimal priod_no = dwDetail.GetItemDecimal(m + 1, "last_period"); Decimal xRem = remain - pay; //--- ยอดคงเหลือค้างชำระ Decimal grpID = dwDetail.GetItemDecimal(m + 1, "insgroup_id"); Decimal ldk_balance = dwDetail.GetItemDecimal(m + 1, "insstk_blance"); ldk_balance = ldk_balance + pay; dwDetail.SetItemDecimal(m + 1, "inspayment_arrear", xRem); dwDetail.SetItemDecimal(m + 1, "insstk_blance", insstk_balance + pay); dwDetail.SetItemDecimal(m + 1, "last_period", priod_no); dwDetail.SetItemDecimal(m + 1, "last_stm_no", stm_no + 1); //============================================================================== // insgroupmaster String sqlUpdate = @"UPDATE insgroupmaster set inspayment_arrear = " + xRem + @", insgroup_date = to_date('" + crrDate + @"','dd/mm/yyyy'), insreq_date = to_date('" + crrDate + @"','dd/mm/yyyy'), insstk_blance = nvl(insstk_blance,0)+" + pay + @", last_period = nvl(last_period,0)+1, last_stm_no = nvl(last_stm_no,0) + 1 WHERE member_no = '" + membno + @"' and instype_code='" + typecode + @"' and insgroupdoc_no='" + docCode + "'"; ta.Exe(sqlUpdate); //============================================================================== // cmshrlonslipdet Decimal nxtNo = NextNo("cmshrlonslipdet", "slip_no", slipNo, ta); sqlInsert = @"insert into cmshrlonslipdet(slip_no,slipitemtype_code,seq_no,shrlontype_code, operate_flag,Slipitem_desc,item_payamt,item_balance) values('" + slipNo + @"', 'INA'," + nxtNo + @", '" + typecode + @"', 1,'" + descrpt + @"', " + pay + "," + xRem + ")"; ta.Exe(sqlInsert); //============================================================================== // insgroupstatement; decimal xseq_no = NextNo("insgroupstatement", "insgroup_id", grpID.ToString(), ta); String dsql = @"insert into insgroupstatement (member_no,instype_code,seq_no,insitemtype_code, insperiod_payment,insprince_balance,operate_date,insgroupslip_date,entry_date, entry_id,insgroupdoc_no,insgroup_id,refdoc_no) values('" + membno + "','" + typecode + "'," + xseq_no + ",'IPX'," + pay + @", " + ldk_balance + @", to_date('" + crrDate + @"','dd/mm/yyyy'), to_date('" + crrDate + @"','dd/mm/yyyy'), to_date('" + crrDate + @"','dd/mm/yyyy'), '" + entryid + @"', '" + docCode + "'," + grpID + ",'" + slipNo + "')"; ta.Exe(dsql); } } //===============================End Edit ta.Commit(); ta.Close(); //---- 01.04.2011 Save Detail } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } private Decimal NextNo(String tbl, String where, String vle, Sta ta) { String sql = @"select nvl(max(seq_no),0) +1 nextNo from " + tbl + " where " + where + " = '" + vle + "'"; Decimal seqNo = 0; Sdt dtDtl = ta.Query(sql); dtDtl.Next(); seqNo = dtDtl.GetDecimal("nextNo"); return seqNo; } // by AOI 20.04.2011.13.34 //---- 31.03.2011 Save Detail public int SaveSlipDet(String pbl, DateTime workDate, String xmlInsuReqRequest, String slipno) { Sta ta = new Sta(connectionString); try { ta.Transection(); DataStore dwDetail = new DataStore(pbl, "d_ins_slip_operate_etc"); dwDetail.ImportString(xmlInsuReqRequest, FileSaveAsType.Xml); string docNo = ""; docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCLDOCNO, workDate.Year + 543, ta); dwDetail.SetItemString(1, "seq_no", docNo); dwDetail.SetItemString(1, "slip_no", slipno); String sqlInsert = new DwHandle(dwDetail).SqlInsertSyntax("cmshrlonslipdet", 1); String sqlUpdate = new DwHandle(dwDetail).SqlUpdateSyntax("cmshrlonslipdet", 1); ta.Exe(sqlInsert); ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } //by kae ยกเลิกปิดประกัน public int SaveCancelresign(String pbl, DateTime workDate, String xmlInsuCancelResign) { Sta ta = new Sta(connectionString); try { ta.Transection(); DataStore dwMain = new DataStore(pbl, "d_ins_approve_listnew"); dwMain.ImportString(xmlInsuCancelResign, FileSaveAsType.Xml); String sqlUpdate = ""; //DwUtil.UpdateDateWindow(dw_search, "as_appinsresign.pbl", "INSREQRESIGN"); int row = dwMain.RowCount; for (int i = 1; i <= row; i++) { decimal status = dwMain.GetItemDecimal(i, "reqresign_status"); decimal ldc_insgpid = dwMain.GetItemDecimal(i, "insgroup_id"); if (status == -9) { String sql = "UPDATE insgroupmaster set insmemb_status= 1 " + "where INSGROUPMASTER.insgroup_id = " + ldc_insgpid.ToString().Trim(); ta.Exe(sql); sqlUpdate = new DwHandle(dwMain).SqlUpdateSyntax("INSREQRESIGN", i); ta.Exe(sqlUpdate); //sql = "UPDATE insreqresign set reqresign_status = -9 " + // " where INSGROUPMASTER.insgroup_id = " + ldc_insgpid.ToString().Trim(); //ta.Exe(sql); } } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } private DateTime getDateTimeCulture(int ai_day, int ai_month, int ai_year, CultureInfo aci_culture) { DateTime ldtm_output; String ls_dateformat, ls_day, ls_month, ls_year; ls_day = "0" + ai_day.ToString().Trim(); ls_day = ls_day.Substring(ls_day.Length - 2, 2); ls_month = "0" + ai_month.ToString().Trim(); ls_month = ls_month.Substring(ls_month.Length - 2, 2); ls_year = ai_year.ToString().Trim(); ls_dateformat = ls_day + ls_month + ls_year; ldtm_output = DateTime.ParseExact(ls_dateformat, "ddMMyyyy", aci_culture); return ldtm_output; } public pbservice.str_progress GetProgress() { return progress; } //พี่อ้อย*********** public int SaveInsVehicle(String pbl, String xmlInsMaster, String xmlInsStmt, String new_status) { String sqlText; decimal li_grpid; Sta ta = new Sta(connectionString); try { ta.Transection(); Sdt dt; //Import DataWindow to DataStore DataStore dw_insmaster = new DataStore(pbl, "d_md_car"); dw_insmaster.ImportString(xmlInsMaster, FileSaveAsType.Xml); DataStore dw_insstmt = new DataStore(pbl, "d_md_cardetail"); dw_insstmt.ImportString(xmlInsStmt, FileSaveAsType.Xml); Decimal ldc_cmpy; //check save '0'=Update , '1'=Insert if (new_status == "0") { sqlText = new DwHandle(dw_insmaster).SqlUpdateSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); sqlText = new DwHandle(dw_insstmt).SqlUpdateSyntax("INSGROUPCARDET", 1); ta.Exe(sqlText); String lsip_no, ls_membno; DateTime slp_dte; Decimal grpId; try { lsip_no = dw_insstmt.GetItemString(1, "slip_no"); } catch { lsip_no = ""; } try { slp_dte = dw_insstmt.GetItemDateTime(1, "slip_date"); } catch { slp_dte = new DateTime(1900, 1, 1); } try { grpId = dw_insstmt.GetItemDecimal(1, "INSGROUP_ID"); } catch { grpId = dw_insmaster.GetItemDecimal(1, "INSGROUP_ID"); } ls_membno = dw_insstmt.GetItemString(1, "MEMBER_NO"); try { ldc_cmpy = dw_insstmt.GetItemDecimal(1, "COMPANY_ID"); } catch { ldc_cmpy = 0; } sqlText = "SELECT SLIP_NO FROM INSSLIPPAYIN WHERE INSGROUP_ID =" + grpId; dt = ta.Query(sqlText); //if (lsip_no != "") //{ if (dt.Next()) { sqlText = "UPDATE INSSLIPPAYIN SET SLIP_NO='" + lsip_no + "',SLIP_DATE= to_date('" + slp_dte.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "','dd/mm/yyyy')" + ",COMPANY_ID=" + ldc_cmpy + " " + " WHERE INSGROUP_ID =" + grpId; ta.Exe(sqlText); } else { sqlText = "INSERT INTO INSSLIPPAYIN(SLIP_NO,SLIP_DATE,INSGROUP_ID,MEMBER_NO,COMPANY_ID)" + " VALUES('" + lsip_no + "',to_date('" + slp_dte.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "','dd/mm/yyyy')," + grpId + ",'" + ls_membno + "'," + ldc_cmpy + ")"; ta.Exe(sqlText); } //} } else //new_status == "1" (Insert) { //---- INSERT --------------- sqlText = "SELECT max(NVL(insgroup_id,0))+1 AS INSGROUP_ID FROM insgroupmaster"; dt = ta.Query(sqlText); //if (dt.Rows.Count > 0) if (dt.Next()) { li_grpid = dt.GetDecimal("INSGROUP_ID"); } else { li_grpid = 0; } //-- INSREQNEW //try //{ // ls_birth = dw_insmaster.GetItemString(1, "birth_tdate"); // ldtm_birth = getEnDate(ls_birth);//dw_insmaster.GetItemDateTime(1, "birth_date"); //} //catch { ldtm_birth = Convert.ToDateTime("01/01/1900"); } String ls_memberno, ls_instype, ls_level, ls_marriggeName, ls_insdoc, ls_personcard; String ls_entyrid, ls_docNo, lsd_slipno; Decimal ldc_insreqcost, ldc_insmemb, ldc_inspayment, ldc_insgpid; DateTime ldtm_insdate, ldtm_birth; ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); ldtm_insdate = dw_insmaster.GetItemDateTime(1, "insgroup_date"); try { ldc_insreqcost = dw_insmaster.GetItemDecimal(1, "inscost_blance"); } catch { ldc_insreqcost = 0; } try { ls_insdoc = dw_insmaster.GetItemString(1, "insgroupdoc_no").Trim(); } catch { ls_insdoc = dw_insstmt.GetItemString(1, "nummernschild"); } ls_instype = dw_insmaster.GetItemString(1, "instype_code").Trim(); try { ls_level = dw_insmaster.GetItemString(1, "level_code").Trim(); } catch { ls_level = ""; } ls_marriggeName = dw_insmaster.GetItemString(1, "marrige_name").Trim(); ldc_insmemb = dw_insmaster.GetItemDecimal(1, "insmemb_type"); try { ls_personcard = dw_insmaster.GetItemString(1, "person_card").Trim(); } catch { ls_personcard = ""; } try { ldc_inspayment = dw_insmaster.GetItemDecimal(1, "inspayment_amt"); } catch { ldc_inspayment = 0; } try { // ls_birth = dw_insmaster.GetItemString(1, "birth_tdate"); ldtm_birth = dw_insmaster.GetItemDateTime(1, "birth_date"); } catch { ldtm_birth = Convert.ToDateTime("01/01/1900"); } try { ldc_cmpy = dw_insstmt.GetItemDecimal(1, "COMPANY_ID"); } catch { ldc_cmpy = 0; } ldc_insgpid = li_grpid;//dw_insmaster.GetItemDecimal(1, "insgroup_id"); ls_docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSAPPLDOCNO, ldtm_insdate.Year + 543, ta); ls_entyrid = "admin"; sqlText = "INSERT INTO INSREQNEW " + "( INSREQDOC_NO, MEMBER_NO, INREQ_DATE, INSAPROVE_DATE, " + "INSREQCOST_AMT, INSAPCOST_AMT, EX_STATUS1, EX_STATUS2, EX_STATUS3, EX_STATUS4, " + "ENTRY_ID, ENTRY_DATE, INSREQ_STATUS, INSGROUPDOC_NO, APPROVE_ID, INSTYPE_CODE, " + "INSPERIOD_PAYMENT, BIRTH_DATE, INSLEVEL_CODE, MARRIGE_NAME, INSGROUPNO_REF, " + "INSMEMB_TYPE, PERSON_CARD, INSPAYMENT_AMT, APPLTYPE_CODE, INSGROUP_ID ) " + "VALUES ('" + ls_docNo + "','" + ls_memberno + "'," + "to_date('" + DateTime.Now.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + DateTime.Now.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insreqcost.ToString().Trim() + "," + ldc_insreqcost.ToString().Trim() + ",1,1,1,1," + "'" + ls_entyrid + "',sysdate,1,'" + ls_insdoc + "','" + ls_entyrid + "','" + ls_instype + "',null," + "to_date('" + ldtm_birth.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "', 'dd/mm/yyyy')," + "'" + ls_level + "','" + ls_marriggeName + "',null," + ldc_insmemb.ToString().Trim() + ",'" + ls_personcard + "'," + ldc_inspayment.ToString().Trim() + "," + "null," + ldc_insgpid.ToString().Trim() + ")"; ta.Exe(sqlText); //------------------------------------------------------------------------ //--------------- INSGROUPMASTER dw_insmaster.SetItemDecimal(1, "INSGROUP_ID", ldc_insgpid); //dw_insmaster.SetItemDecimal(1, "insmemb_status", 1); //dw_insmaster.SetItemString(1, "LOCATION_DESC", dw_insstmt.GetItemString(1, "location_desc")); sqlText = new DwHandle(dw_insmaster).SqlInsertSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); //sqlText = "UPDATE INSGROUPMASTER SET LOCATION_DESC ='" + dw_insstmt.GetItemString(1, "location_desc") + // "' WHERE INSGROUP_ID =" + ldc_insgpid; //ta.Exe(sqlText); //------------------------------------------------------------------------ /*--------------- INSGROUPCARDET---------------*/ try { lsd_slipno = dw_insstmt.GetItemString(1, "slip_no"); } catch { lsd_slipno = ""; } try { ldtm_birth = dw_insstmt.GetItemDateTime(1, "slip_date"); } catch { ldtm_birth = Convert.ToDateTime("01/01/1900"); } dw_insstmt.SetItemDecimal(1, "INSGROUP_ID", ldc_insgpid); sqlText = new DwHandle(dw_insstmt).SqlInsertSyntax("INSGROUPCARDET", 1); ta.Exe(sqlText); //------------------------------------------------------------------------ //----------------- INSSLIPPAYIN sqlText = "SELECT SLIP_NO FROM INSSLIPPAYIN WHERE INSGROUP_ID =" + ldc_insgpid; dt = ta.Query(sqlText); if (dt.Next()) { sqlText = "UPDATE INSSLIPPAYIN SET SLIP_NO='" + lsd_slipno + "',SLIP_DATE= to_date('" + ldtm_birth.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "','dd/mm/yyyy')" + ",COMPANY_ID=" + ldc_cmpy + " " + " WHERE INSGROUP_ID =" + ldc_insgpid; ta.Exe(sqlText); } //---------- กรณีที่ไม่มีข้อมูล else { sqlText = "INSERT INTO INSSLIPPAYIN (SLIP_NO,SLIP_DATE,INSGROUP_ID,MEMBER_NO,COMPANY_ID)" + " VALUES('" + lsd_slipno + "',to_date('" + ldtm_birth.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "','dd/mm/yyyy')," + ldc_insgpid + ",'" + ls_memberno + "'," + ldc_cmpy + ")"; ta.Exe(sqlText); } //if (lsd_slipno != "") //{ // //sqlText = "INSERT INTO INSSLIPPAYIN(SLIP_NO,SLIP_DATE,INSGROUP_ID,MEMBER_NO,COMPNAY_ID)" + // // " VALUES('" + lsd_slipno + "',to_date('" + ldtm_birth.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + // // "','dd/mm/yyyy')," + ldc_insgpid + ",'" + ls_memberno + "')"; // //ta.Exe(sqlText); // sqlText = "INSERT INTO INSSLIPPAYIN(SLIP_NO,SLIP_DATE,INSGROUP_ID,MEMBER_NO,COMPNAY_ID)" + // " VALUES('" + lsd_slipno + "',to_date('" + ldtm_birth.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + // "','dd/mm/yyyy')," + ldc_insgpid + ",'" + ls_memberno + "'," + ldc_cmpy + ")"; // ta.Exe(sqlText); //} } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } public int SaveInsBuilding(String pbl, String xmlInsMaster, String xmlInsDetail, String new_status) { String sqlText; //int li_row, li_rowcount; decimal li_grpid; Sta ta = new Sta(connectionString); try { ta.Transection(); Sdt dt; //Import DataWindow to DataStore DataStore dw_insmaster = new DataStore(pbl, "d_md_car"); DataStore dw_insDetail = new DataStore(pbl, "d_as_insurfire"); dw_insmaster.ImportString(xmlInsMaster, FileSaveAsType.Xml); dw_insDetail.ImportString(xmlInsDetail, FileSaveAsType.Xml); //check save '0'=Update , '1'=Insert String ls_memberno, ownhmename, exterwall, roof, roofbeam, upperfl, postcode, grpno_ref; String road, tambo, amphur, city, bdingno, bdingarea, bookno, bookarea, ls_personcard, ls_instype, ls_level, soy, addrno, ls_ownerhme, ls_insdoc, ls_marriggeName, ls_docNo, ls_entyrid, lsd_slipno, ls_remark, ls_buildid, ls_levelno, internalwll, noofbding, ls_companyname; decimal noofstorey, moo, ldc_insreqcost, ldc_insmemb, ldc_inspayment, ldc_cmpy; DateTime ld_slipdate; try { ls_ownerhme = dw_insDetail.GetItemString(1, "owner_home"); } catch { ls_ownerhme = ""; } try { ownhmename = dw_insDetail.GetItemString(1, "owner_homename"); } catch { ownhmename = ""; } try { exterwall = dw_insDetail.GetItemString(1, "EXTERNALWALL"); } catch { exterwall = ""; } try { internalwll = dw_insDetail.GetItemString(1, "INTERNALAREA"); } catch { internalwll = ""; } try { roof = dw_insDetail.GetItemString(1, "ROOF"); } catch { roof = ""; } try { roofbeam = dw_insDetail.GetItemString(1, "ROOFBEAM"); } catch { roofbeam = ""; } try { noofbding = dw_insDetail.GetItemString(1, "NOOFBUILDING"); } catch { noofbding = ""; } try { upperfl = dw_insDetail.GetItemString(1, "UPPERFLOOR"); } catch { upperfl = ""; } try { road = dw_insDetail.GetItemString(1, "ROAD"); } catch { road = ""; } try { tambo = dw_insDetail.GetItemString(1, "TAMBOL"); } catch { tambo = ""; } try { amphur = dw_insDetail.GetItemString(1, "AMPHUR"); } catch { amphur = ""; } try { city = dw_insDetail.GetItemString(1, "CITY"); } catch { city = ""; } try { bdingno = dw_insDetail.GetItemString(1, "buildingname"); } catch { bdingno = ""; } try { bdingarea = dw_insDetail.GetItemString(1, "buildingno"); } catch { bdingarea = ""; } try { bookno = dw_insDetail.GetItemString(1, "bookareano"); } catch { bookno = ""; } try { bookarea = dw_insDetail.GetItemString(1, "bookarea"); } catch { bookarea = ""; } try { moo = dw_insDetail.GetItemDecimal(1, "MOO"); } catch { moo = 0; } try { soy = dw_insDetail.GetItemString(1, "soy"); } catch { soy = ""; } try { addrno = dw_insDetail.GetItemString(1, "addressno"); } catch { addrno = ""; } try { postcode = dw_insDetail.GetItemString(1, "postcode"); } catch { postcode = ""; } try { noofstorey = dw_insDetail.GetItemDecimal(1, "NOOFSTOREY"); } catch { noofstorey = 0; } try { grpno_ref = dw_insDetail.GetItemString(1, "insgroupno_ref"); } catch { grpno_ref = ""; } try { ls_buildid = dw_insDetail.GetItemString(1, "building_id"); } catch { ls_buildid = ""; } try { ls_levelno = dw_insDetail.GetItemString(1, "level_no"); } catch { ls_levelno = ""; } try { ls_remark = dw_insDetail.GetItemString(1, "remark"); } catch { ls_remark = ""; } try { lsd_slipno = dw_insDetail.GetItemString(1, "slip_no"); } catch { lsd_slipno = ""; } try { ld_slipdate = dw_insDetail.GetItemDateTime(1, "slip_date"); } catch { ld_slipdate = Convert.ToDateTime("01/01/1900"); } try { ldc_cmpy = dw_insDetail.GetItemDecimal(1, "COMPANY_ID"); } catch { ldc_cmpy = 0; } try { ls_companyname = dw_insDetail.GetItemString(1, "COMPANYNAME"); } catch { ls_companyname = ""; } ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); String ls_desc = ""; ls_desc += (bdingno != "") ? "อาคารชุดชื่อ " + bdingno : ""; ls_desc += (ls_levelno != "") ? " ชั้น " + ls_levelno : ""; if (ls_buildid != "") { ls_desc += " อาคารเลขที่ " + ls_buildid; } if (bookarea != "") { ls_desc += " โฉนดเลขที่ " + bookarea; } if (bdingarea != "") { ls_desc += " ห้องชุดที่ " + bdingarea; } if (ls_remark != "") { ls_desc += " ลักษณะสิ่งปลูกสร้าง " + ls_remark; } if (addrno != "") { ls_desc += " " + addrno; } if (road != "") { ls_desc += " ถนน" + road; } if (moo != 0) { ls_desc += " หมู่ที่ " + moo; } if (soy != "") { ls_desc += " ซอย, หมู่บ้าน" + soy; } if (tambo != "") { ls_desc += " ตำบล" + tambo; } if (amphur != "") { ls_desc += " อำเภอ" + amphur; } if (city != "") { ls_desc += " " + city; } if (postcode != "") { ls_desc += " " + postcode; } if (new_status == "0") { sqlText = new DwHandle(dw_insmaster).SqlUpdateSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); try { li_grpid = dw_insmaster.GetItemDecimal(1, "INSGROUP_ID"); } catch { li_grpid = 0; } //--------------------------- dw_insDetail.SetItemDecimal(1, "INSGROUP_ID", li_grpid); sqlText = new DwHandle(dw_insDetail).SqlUpdateSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); if (ls_desc != "") { sqlText = "UPDATE INSGROUPMASTER SET LOCATION_DESC ='" + ls_desc + "' WHERE INSGROUP_ID = " + li_grpid; ta.Exe(sqlText); } } else //new_status == "1" (Insert) { //---- INSERT --------------- sqlText = "SELECT max(NVL(insgroup_id,0))+1 AS INSGROUP_ID FROM INSGROUPMASTER"; dt = ta.Query(sqlText); if (dt.Next()) { li_grpid = dt.GetDecimal("INSGROUP_ID"); } else { li_grpid = 0; } //-- INSREQNEW DateTime ldtm_insdate, ldtm_birth; //ls_memberno = dw_insmaster.GetItemString(1, "member_no").Trim(); ldtm_insdate = dw_insmaster.GetItemDateTime(1, "insgroup_date"); ls_instype = dw_insmaster.GetItemString(1, "instype_code").Trim(); ls_marriggeName = dw_insmaster.GetItemString(1, "marrige_name").Trim(); ldc_insmemb = dw_insmaster.GetItemDecimal(1, "insmemb_type"); try { ldc_insreqcost = dw_insmaster.GetItemDecimal(1, "inscost_blance"); } catch { ldc_insreqcost = 0; } try { ls_insdoc = dw_insmaster.GetItemString(1, "insgroupdoc_no").Trim(); } catch { ls_insdoc = grpno_ref; dw_insmaster.SetItemString(1, "insgroupdoc_no", grpno_ref); } try { ldtm_birth = dw_insmaster.GetItemDateTime(1, "birth_date"); } catch { ldtm_birth = Convert.ToDateTime("01/01/1900"); } try { ls_level = dw_insmaster.GetItemString(1, "level_code").Trim(); } catch { ls_level = ""; } try { ls_personcard = dw_insmaster.GetItemString(1, "person_card").Trim(); } catch { ls_personcard = ""; } try { ldc_inspayment = dw_insmaster.GetItemDecimal(1, "inspayment_amt"); } catch { ldc_inspayment = 0; } //ldc_insgpid = li_grpid;//dw_insmaster.GetItemDecimal(1, "insgroup_id"); ls_docNo = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSAPPLDOCNO, ldtm_insdate.Year + 543, ta); ls_entyrid = "admin"; //-------------------------- INSREQNEW sqlText = "INSERT INTO INSREQNEW " + "( INSREQDOC_NO, MEMBER_NO, INREQ_DATE, INSAPROVE_DATE, " + "INSREQCOST_AMT, INSAPCOST_AMT, EX_STATUS1, EX_STATUS2, EX_STATUS3, EX_STATUS4, " + "ENTRY_ID, ENTRY_DATE, INSREQ_STATUS, INSGROUPDOC_NO, APPROVE_ID, INSTYPE_CODE, " + "INSPERIOD_PAYMENT, BIRTH_DATE, INSLEVEL_CODE, MARRIGE_NAME, INSGROUPNO_REF, " + "INSMEMB_TYPE, PERSON_CARD, INSPAYMENT_AMT, APPLTYPE_CODE, INSGROUP_ID ) " + "VALUES ('" + ls_docNo.Trim() + "','" + ls_memberno + "'," + "to_date('" + DateTime.Now.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + "to_date('" + DateTime.Now.ToString("dd/MM/yyyy") + "', 'dd/mm/yyyy')," + ldc_insreqcost.ToString().Trim() + "," + ldc_insreqcost.ToString().Trim() + ",1,1,1,1," + "'" + ls_entyrid + "',sysdate,1,'" + ls_insdoc + "','" + ls_entyrid + "','" + ls_instype + "',null," + "to_date('" + ldtm_birth.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "', 'dd/mm/yyyy')," + "'" + ls_level + "','" + ls_marriggeName + "',null," + ldc_insmemb.ToString().Trim() + ",'" + ls_personcard + "'," + ldc_inspayment.ToString().Trim() + "," + "null," + li_grpid.ToString().Trim() + ")"; ta.Exe(sqlText); //--------------------------- INSGROUPMASTER dw_insmaster.SetItemDecimal(1, "INSGROUP_ID", li_grpid); sqlText = new DwHandle(dw_insmaster).SqlInsertSyntax("INSGROUPMASTER", 1); ta.Exe(sqlText); //--------------------------- if (li_grpid != 0) { sqlText = "UPDATE INSGROUPMASTER SET OWNER_HOME ='" + ls_ownerhme + "',OWNER_HOMENAME ='" + ownhmename + "',NOOFSTOREY =" + noofstorey + ",EXTERNALWALL ='" + exterwall + "',INTERNALAREA ='" + internalwll + "',ROOFBEAM ='" + roofbeam + "',ROOF ='" + roof + "',NOOFBUILDING ='" + noofbding + "',UPPERFLOOR ='" + upperfl + "',ROAD ='" + road + "',TAMBOL ='" + tambo + "',AMPHUR ='" + amphur + "',CITY ='" + city + "',buildingname ='" + bdingno + "',buildingno ='" + bdingarea + "',bookareano ='" + bookno + "',bookarea ='" + bookarea + "',MOO =" + moo + ",soy ='" + soy + "',insgroupno_ref='" + grpno_ref.Trim() + "',postcode ='" + postcode + "',addressno ='" + addrno + "',building_id='" + ls_buildid + "',level_no ='" + ls_levelno + "',remark ='" + ls_remark + "',location_desc = '" + ls_desc + "' WHERE INSGROUP_ID =" + li_grpid; ta.Exe(sqlText); } } //---------------------------- sqlText = "SELECT SLIP_NO FROM INSSLIPPAYIN WHERE INSGROUP_ID =" + li_grpid; dt = ta.Query(sqlText); if (dt.Next()) { sqlText = "UPDATE INSSLIPPAYIN SET SLIP_NO='" + lsd_slipno + "',SLIP_DATE= to_date('" + ld_slipdate.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "','dd/mm/yyyy')" + ",COMPANY_ID=" + ldc_cmpy + " " + " WHERE INSGROUP_ID =" + li_grpid; ta.Exe(sqlText); } else { sqlText = "INSERT INTO INSSLIPPAYIN(SLIP_NO,SLIP_DATE,INSGROUP_ID,MEMBER_NO,COMPANY_ID)" + " VALUES('" + lsd_slipno + "',to_date('" + ld_slipdate.ToString("dd/MM/yyyy", new CultureInfo("en-US")) + "','dd/mm/yyyy')," + li_grpid + ",'" + ls_memberno + "'," + ldc_cmpy + ")"; ta.Exe(sqlText); } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } public int SaveInsPasion(String pbl, String xmlDetail, String rowID, String new_status) { String sqlText; decimal li_grpid; Sta ta = new Sta(connectionString); try { ta.Transection(); Sdt dt; //Import DataWindow to DataStore // DataStore dw_insmaster = new DataStore(pbl, "d_as_member_person_x"); DataStore dw_detail = new DataStore(pbl, "d_as_ins_pasion_x"); // dw_insmaster.ImportString(xmlInsMaster, FileSaveAsType.Xml); dw_detail.ImportString(xmlDetail, FileSaveAsType.Xml); //check save '0'=Update , '1'=Insert if (new_status == "0") { int m = 0; String[] x = rowID.Split(','); //x.Length; for (m = 0; m < x.Length; m++) { int k = Convert.ToInt32(x[m]); sqlText = new DwHandle(dw_detail).SqlUpdateSyntax("INSGROUPMASTER", k); ta.Exe(sqlText); } } else //new_status == "1" (Insert) { //---- INSERT --------------- sqlText = "SELECT max(NVL(insgroup_id,0))+1 AS INSGROUP_ID FROM INSGROUPMASTER"; dt = ta.Query(sqlText); if (dt.Next()) { li_grpid = dt.GetDecimal("INSGROUP_ID"); } else { li_grpid = 0; } //-- INSREQNEW //--------------------------- } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } public int SaveInsCommission(String pbl, String xmlMain) { Sta ta = new Sta(connectionString); Sdt dt; try { DataStore dw = new DataStore(pbl, "d_ins_commission"); dw.ImportString(xmlMain, FileSaveAsType.Xml); //String tbl = "INSCOMMISSION"; DateTime mth = dw.GetItemDateTime(1, "recorddate"); String cmp = dw.GetItemString(1, "company_id"); //---- หาจำนวน loop String strdte = dw.GetItemString(1, "end_tdate"); DateTime dte = getEnDate(strdte); DateTime xdte = dw.GetItemDateTime(1, "recorddate"); System.TimeSpan dif = dte.Subtract(xdte); Double xst = dif.TotalDays; // จำนวน loop ที่จะวนทั้งหมด int rw = 1; //dte = dte.AddDays(1); //---------------------------- for (int m = 0; m <= xst; m++) { //==================== sum ta.Transection(); String sql = @" SELECT sum(a.insamount_amt) amt,c.percent FROM insgroupmaster a ,insslippayin b,inscompany c WHERE a.instype_code ='07' and a.insgroup_id = b.insgroup_id and b.company_id = c.company_id and c.company_id = '" + cmp.Trim() + "' AND b.Slip_date = to_date('" + mth.ToString("ddMMyyyy", new CultureInfo("en-US")) + "','ddmmyyyy')" + @" group by c.percent"; dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) Decimal tlcost = (dt.GetDecimal("AMT") * dt.GetDecimal("PERCENT")) + dt.GetDecimal("AMT"); decimal tlpct = (dt.GetDecimal("AMT") * dt.GetDecimal("PERCENT")); dw.SetItemDecimal(1, "COST", dt.GetDecimal("AMT")); dw.SetItemDecimal(1, "PERCENT", dt.GetDecimal("PERCENT")); dw.SetItemDecimal(1, "total_receive", tlcost); dw.SetItemDecimal(1, "total_cst", tlpct); dw.SetItemDateTime(1, "recorddate", mth); String yrs = Convert.ToString(mth.Year + 543); yrs = yrs + mth.Month.ToString("00"); dw.SetItemString(1, "yearmonth", yrs); String sqlTxt = "SELECT * FROM INSCOMMISSION WHERE RECORDDATE = to_date('" + mth.ToString("ddMMyyyy", new CultureInfo("en-US")) + "','ddmmyyyy') AND COMPANY_ID =" + cmp.Trim() + ""; dt = ta.Query(sqlTxt); //----------- update if (dt.Next()) { sqlTxt = new DwHandle(dw).SqlUpdateSyntax("INSCOMMISSION", 1); } //----------- Insert else { sqlTxt = new DwHandle(dw).SqlInsertSyntax("INSCOMMISSION", 1); } ta.Exe(sqlTxt); rw += 1; //ta.Close(); } mth = xdte.AddDays(1); xdte = mth; } ta.Commit(); ta.Close(); } catch { ta.RollBack(); ta.Close(); } return 1; } public int SaveInsPercent(String pbl, String xmlMain) { Sta ta = new Sta(connectionString); Sdt dt; try { DataStore dw = new DataStore(pbl, "d_ins_percentcomadd"); dw.ImportString(xmlMain, FileSaveAsType.Xml); //---------------------------- //==================== sum ta.Transection(); String sql = @"SELECT MAX(NVL(COMPANY_ID,0)) +1 AS CMPID FROM INSCOMPANY"; dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dw.SetItemDecimal(1, "", dt.GetDecimal("CMPID")); //String sqlTxt = "SELECT * FROM INSCOMMISSION WHERE RECORDDATE = to_date('" + mth.ToString("ddMMyyyy", new CultureInfo("en-US")) + "','ddmmyyyy') AND COMPANY_ID =" + cmp.Trim() + ""; //dt = ta.Query(sqlTxt); ////----------- update //if (dt.Next()) //{ // sqlTxt = new DwHandle(dw).SqlUpdateSyntax("INSCOMMISSION", 1); //} ////----------- Insert //else //{ // sqlTxt = new DwHandle(dw).SqlInsertSyntax("INSCOMMISSION", 1); //} //ta.Exe(sqlTxt); } ta.Commit(); ta.Close(); } catch { ta.RollBack(); ta.Close(); } return 1; } public String[] InitCommission(String pbl, String xmlMain) { String[] resu = new String[1]; resu[0] = ""; Sta ta = new Sta(connectionString); try { DataStore dwMain = new DataStore(); dwMain.LibraryList = pbl; dwMain.DataWindowObject = "d_ins_commission"; dwMain.Reset(); dwMain.ImportString(xmlMain, FileSaveAsType.Xml); String cmpny = dwMain.GetItemString(1, "company_id"); DateTime mth = dwMain.GetItemDateTime(1, "recorddate"); //---- หาจำนวน loop String strdte = dwMain.GetItemString(1, "end_tdate"); DateTime dte = getEnDate(strdte); //DateTime xdte = dwMain.GetItemDateTime(1, "recorddate"); //System.TimeSpan dif = dte.Subtract(xdte); //dte = dte.AddDays(1); //Double xst = dif.TotalDays; //---------------------------- String sql = @" SELECT sum(a.insamount_amt) amt,c.percent FROM insgroupmaster a ,insslippayin b,inscompany c WHERE a.instype_code ='07' and a.insgroup_id = b.insgroup_id and b.company_id = c.company_id and c.company_id = '" + cmpny.Trim() + "' AND b.Slip_date between to_date('" + mth.ToString("ddMMyyyy", new CultureInfo("en-US")) + "','ddmmyyyy') AND " + @" to_date('" + dte.ToString("ddMMyyyy", new CultureInfo("en-US")) + @"','ddmmyyyy') group by c.percent"; Sdt dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dwMain.SetItemDecimal(1, "COST", dt.GetDecimal("AMT")); dwMain.SetItemDecimal(1, "PERCENT", dt.GetDecimal("PERCENT")); resu[0] = dwMain.Describe("DataWindow.Data.XML"); } else { resu[0] = ""; } ta.Close(); } catch (Exception ex) { try { ta.Close(); } catch { } throw ex; } return resu; } public DateTime getEnDate(string as_input) { DateTime ldtm_output; int li_year; li_year = Convert.ToInt32(as_input.Substring(4, 4)); li_year -= 543; // Convert = "MM/dd/yyyy" //ldtm_output = Convert.ToDateTime(as_input.Substring(2, 2) + '/' + as_input.Substring(0, 2) + '/' + li_year.ToString()); ldtm_output = DateTime.ParseExact(as_input, "ddMMyyyy", new CultureInfo("en-US")); int dd = ldtm_output.Day; int mm = ldtm_output.Month; int yy = (Convert.ToInt32(ldtm_output.Year.ToString()) - 543); ldtm_output = new DateTime(yy, mm, dd); return ldtm_output; } public String[] InitInsPasion(String pbl, String xmlInsuRequest) { String[] resu = new String[1]; resu[0] = ""; //resu[1] = ""; Sta ta = new Sta(connectionString); try { //1. สร้าง DataStore ตั้งชื่อว่า dwMain นำ XML จาก argument มา import ใส่ DataStore dwMain = new DataStore(); dwMain.LibraryList = pbl; dwMain.DataWindowObject = "d_as_member_person_x"; dwMain.Reset(); dwMain.ImportString(xmlInsuRequest, FileSaveAsType.Xml); String memberNo = dwMain.GetItemString(1, "member_no"); //2. ไป select ข้อมูลจากทะเบียนสมาชิกใส่ Sdt แล้วนำค่าต่างๆ ไปยิงใส่ dwMain String sql = @" SELECT MBMEMBMASTER.MEMBER_NO, MBMEMBMASTER.MEMB_NAME, MBMEMBMASTER.MEMB_SURNAME, MBMEMBMASTER.MEMBGROUP_CODE, MBUCFPRENAME.PRENAME_SHORT, MBUCFMEMBGROUP.MEMBGROUP_DESC, MBMEMBMASTER.BIRTH_DATE, MBMEMBMASTER.MEMBER_DATE, MBMEMBMASTER.card_person,trunc((months_between(sysdate,MBMEMBMASTER.BIRTH_DATE))/12) as ageofemp FROM MBMEMBMASTER, MBUCFMEMBGROUP, MBUCFPRENAME WHERE ( MBUCFMEMBGROUP.MEMBGROUP_CODE = MBMEMBMASTER.MEMBGROUP_CODE ) and ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and ( ( MBMEMBMASTER.MEMBER_NO = '" + memberNo + "' ) )"; Sdt dt = ta.Query(sql); if (dt.Next()) { // 2.1 เริ่มนำจาก Sdt dt มายิงให้กับ dwMain (*Sdt inherit มาจาก DataTable) dwMain.SetItemString(1, "full_name", dt.GetString("PRENAME_SHORT") + dt.GetString("memb_name") + " " + dt.GetString("memb_surname")); //dwMain.SetItemString(1, "membgroup_code", dt.GetString("MEMBGROUP_CODE")); int year = DateTime.Today.Year - dt.GetDate("birth_date").Year; try { //3. ไป select ข้อมูลหุ้นแล้วยิงใส่ dwMain String sqlShare = @" select insgroupdoc_no,startsafe_date,marrige_name,inspayment_amt,birth_date,insmemb_type, trunc((months_between(sysdate,insgroupmaster.BIRTH_DATE))/12) as ageofemp from insgroupmaster where member_no = '" + memberNo + "' and instype_code = '06'"; Sdt dtSumShare = ta.Query(sqlShare); if (dtSumShare.Next()) { //3.1 ยิงค่าใส่ dwMain dwMain.SetItemString(1, "insgroupdoc_no", dtSumShare.GetString("insgroupdoc_no")); dwMain.SetItemDateTime(1, "startsafe_date", dtSumShare.GetDate("startsafe_date")); dwMain.SetItemString(1, "marrige_name", dtSumShare.GetString("marrige_name")); dwMain.SetItemDecimal(1, "inspayment_amt", dtSumShare.GetDecimal("inspayment_amt")); dwMain.SetItemDateTime(1, "birth_date", dtSumShare.GetDate("birth_date")); dwMain.SetItemDecimal(1, "ageofemp", dtSumShare.GetDecimal("ageofemp")); dwMain.SetItemDecimal(1, "insmemb_type", dt.GetDecimal("insmemb_type")); } else throw new Exception(); } catch { dwMain.SetItemDecimal(1, "inspayment_amt", 0m); dwMain.SetItemString(1, "insgroupdoc_no", ""); //dwMain.SetItemDateTime(1, "startsafe_date", DateTime.Now); dwMain.SetItemString(1, "marrige_name", ""); dwMain.SetItemDateTime(1, "birth_date", dt.GetDate("birth_date")); dwMain.SetItemDecimal(1, "ageofemp", dt.GetDecimal("ageofemp")); dwMain.SetItemDecimal(1, "insmemb_type", 1); } //5. เมื่อยิงค่าตั้งต้นของ dwMain ครบแล้วให้ export xml ของ dwMain ส่งไปให้หน้า UI resu[0] = dwMain.Describe("DataWindow.Data.XML"); ta.Close(); } else { throw new Exception("ไม่มีสมาชิก " + memberNo + " อยู่ในระบบ"); } } catch (Exception ex) { try { ta.Close(); } catch { } throw ex; } return resu; } //********* // Wu ปิดประกันตามวาระ public int MbResign(String pbl, String xmlData) { String sqlText; int li_row, li_rowcount; Sta ta = new Sta(connectionString); try { ta.Transection(); DataStore dw_data = new DataStore(pbl, "d_ins_mbresign_data"); dw_data.ImportString(xmlData, FileSaveAsType.Xml); Decimal REQRESIGN_STATUS = 1; Decimal ldc_flag, ldc_mbtype, INSCOST_BALANCE, INSGROUP_ID; String ls_memberno, INSREQRESIGN_NO, INSTYPE_CODE, INSGROUPDOC_NO, INSRESIGN_CASE; DateTime RESIGN_DATE; li_rowcount = dw_data.RowCount; for (li_row = 1; li_row <= li_rowcount; li_row++) { ldc_flag = dw_data.GetItemDecimal(li_row, "item_flag"); if (ldc_flag == 1) { ls_memberno = dw_data.GetItemString(li_row, "member_no"); ldc_mbtype = dw_data.GetItemDecimal(li_row, "insmemb_type"); INSREQRESIGN_NO = ""; INSREQRESIGN_NO = new DocumentControl().NewDocumentNo(DocumentTypeCode.INSCLDOCNO, DateTime.Today.Year + 543, ta); INSTYPE_CODE = dw_data.GetItemString(li_row, "instype_code"); INSGROUPDOC_NO = dw_data.GetItemString(li_row, "insgroupdoc_no"); INSRESIGN_CASE = dw_data.GetItemString(li_row, "insresign_case"); CultureInfo lci_machine = CultureInfo.CurrentCulture; RESIGN_DATE = dw_data.GetItemDateTime(li_row, "resign_date"); RESIGN_DATE = getDateTimeCulture(RESIGN_DATE.Day, RESIGN_DATE.Month, RESIGN_DATE.Year, lci_machine); INSCOST_BALANCE = dw_data.GetItemDecimal(li_row, "inscost_blance"); INSGROUP_ID = dw_data.GetItemDecimal(li_row, "insgroup_id"); sqlText = @" INSERT INTO INSREQRESIGN ( INSREQRESIGN_NO, INSTYPE_CODE, MEMBER_NO, INSGROUPDOC_NO, LEVEL_CODE, OPERATE_DATE, MBDEAD_DATE, SENDDOC_DATE, INSCOST_BALANCE, INSSTK_BLANCE, INSRESIGN_CASE, REMARK, ENTRY_DATE, ENTRY_ID, REQRESIGN_STATUS, COOPBRANCH_ID, AFINE_BLANCE, CLOSEINS_DATE, CLOSEINS_ID, COMPACCEPT_DATE, APVIMMEDIATE_FLAG, INSGROUP_ID ) VALUES ( '" + INSREQRESIGN_NO + @"', '" + INSTYPE_CODE + @"', '" + ls_memberno + @"', '" + INSGROUPDOC_NO + @"', null, to_date('" + RESIGN_DATE.ToString("dd/MM/yyyy") + @"','dd/mm/yyyy'), null,null, " + INSCOST_BALANCE + @", null, '" + INSRESIGN_CASE + @"', null, sysdate, null, " + REQRESIGN_STATUS + @", null, null, null, null, null, null , " + INSGROUP_ID.ToString() + ")"; ta.Exe(sqlText); sqlText = "UPDATE INSGROUPMASTER SET INSMEMB_STATUS = 0 WHERE INSGROUP_ID = " + INSGROUP_ID.ToString(); ta.Exe(sqlText); } } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } try { ta.Close(); } catch { } throw ex; } return 1; } } }