public void Save(ReceiptsMatch saveObj) { using (var session = GetSession()) { using (var trans = session.BeginTransaction()) { session.FlushMode = FlushMode.Commit; session.SaveOrUpdate(saveObj); trans.Commit(); session.Flush(); //} } } }
//public void GetPolicyInfo(int BatchNo, ref List<String> _err_msg, ref List<String> _unmatch_proposalnos) public void GetPolicyInfo(int BatchNo, ref List<String> _err_msg, ref int ReadRecords, ref int MatchRecords, ref int UnMatchRecords) { string ProposalNo; string CompanyCode; //string BatchNo; long SerialNo; string TransType; string Temp_TransNo; DateTime DocDate = DateTime.Now; DateTime EntryDate = DateTime.Now; string TransMode_Desc; string TransMode = ""; string ReceiptType; string RefNo;//get from policy det but same as proposal no string ProductCode = "";//get from policy det string DocNo; string CurrencyType; string Chq_teller_No; DateTime Chq_Inward_Date = DateTime.Now; string Payer_Payee_Name; string Trans_Desc1; string Trans_Desc2; DateTime Eff_Date = DateTime.Now; string Branch_Code; string Bank_Code; decimal Amount_LC; double Amount_FC; string Insured_Code = "";//get from policy det string Agent_Code = "";//get from policy det string Commission_YN; decimal Poly_Contrib = 0;//get from premium info string Mop = "";//get from premium info string dr_Main; string dr_Sub; string Cr_Main; string Cr_Sub; string Ledger_Type_Cr; string Flag; string OperId; string FileNo = "";//get from policy det DateTime Proc_Date = DateTime.Now; string strquery; string docMonth, docYear, newReceiptNo, newSerialNum; CustodianLife.Model.ReceiptsMatch Rceipt; float nRow = 0; string strGen_Msg = String.Empty; string sFT = ""; String ftm = "Y"; String ftime = "Y"; sFT = "Y"; var errmsg = new List<String>(); long my_intCNT = 0; SqlCommand myole_cmd = null; string mystr_sql = ""; using (var session = GetSession()) { using (var conn = session.Connection as SqlConnection) { SqlConnection connectionSQL = new SqlConnection(); SqlDataAdapter da; DataSet ds; DataRow row; //Assign the connection converted form Nhibernate to Sqlconnection into connectionSQL connectionSQL = conn; SqlCommand commandSQL, commandSQL1; SqlDataReader dr, dr1; //commandSQL.CommandText = "SELECT * FROM TBFN_RECEIPTS_DOWNLOAD"; //strquery = "SELECT * FROM TBFN_RECEIPTS_DOWNLOAD WHERE TBFN_RECPT_DNLD_MATCH='A'"; strquery = "SELECT * FROM TBFN_RECEIPTS_DOWNLOAD WHERE TBFN_RECPT_DNLD_MATCH='A'"; strquery = strquery + " AND TBFN_RECPT_DNLD_BATCH_DATE='" + BatchNo + "'"; da = new SqlDataAdapter(strquery, connectionSQL); ds = new DataSet(); da.Fill(ds); //data.Close() MyLoop_Start: //using (SqlDataReader dr = commandSQL.ExecuteReader()) //{ //SqlDataReader dr = commandSQL.ExecuteReader(); try { //while (dr.Read()) //{ for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { ReadRecords = ds.Tables[0].Rows.Count; nRow += 1; //row row = ds.Tables[0].Rows[i]; ProposalNo = row["TBFN_RECPT_DNLD_POL_NO"].ToString(); RefNo = ProposalNo; Payer_Payee_Name = row["TBFN_RECPT_DNLD_CUST_NAME"].ToString(); CompanyCode = "001"; CurrencyType = "0001"; TransType = "R"; Temp_TransNo = ""; Branch_Code = ""; Bank_Code = ""; DocDate = Convert.ToDateTime(row["TBFN_RECPT_DNLD_PAY_DT_TM"]); ReceiptType = "D"; Trans_Desc1 = row["TBFN_RECPT_DNLD_BANK"].ToString(); Trans_Desc2 = row["TBFN_RECPT_DNLD_PAYMENT"].ToString(); Amount_LC = Convert.ToDecimal(row["TBFN_RECPT_DNLD_AMOUNT"]); Chq_Inward_Date = Convert.ToDateTime(row["TBFN_RECPT_DNLD_CHQ_VAL_DT"]); Commission_YN = "Y"; dr_Main = row["TBFN_RECPT_DNLD_BANK_CODE"].ToString(); TransMode_Desc = row["TBFN_RECPT_DNLD_PAY_MODE"].ToString(); if (TransMode_Desc == "Cash") { TransMode = "C"; } else if (TransMode_Desc == "Own Bank Cheque" || TransMode_Desc == "Other Bank Cheque") { TransMode = "Q"; } else if (TransMode_Desc == "Internal Transfer") { TransMode = "T"; } else if (TransMode_Desc == "Debit Card") { //Check later TransMode = "D"; } Chq_teller_No = row["TBFN_RECPT_DNLD_DEP_SLP_CARD_NO"].ToString(); DataSet dt1 = new DataSet(); DataRow dr2; ReceiptsRepository recRep = new ReceiptsRepository(); dt1 = recRep.GetPolicyInfoDataSet(ProposalNo, "D"); if (dt1.Tables[0].Rows.Count > 0) { dr2 = dt1.Tables[0].Rows[0]; Insured_Code = dr2["TBIL_POLY_ASSRD_CD"].ToString(); Agent_Code = dr2["TBIL_POLY_AGCY_CODE"].ToString().ToString(); Poly_Contrib = Convert.ToDecimal(dr2["TBIL_POL_PRM_DTL_MOP_PRM_LC"]); Mop = dr2["Payment_Mode"].ToString(); FileNo = dr2["File_No"].ToString(); ProductCode = dr2["Product_Code"].ToString(); if (dr2["TBIL_POLICY_EFF_DT"] != DBNull.Value) Eff_Date = Convert.ToDateTime(dr2["TBIL_POLICY_EFF_DT"]); else Eff_Date = Convert.ToDateTime("01/01/2014"); docMonth = BatchNo.ToString().Substring(4, 2); docYear = BatchNo.ToString().Substring(0, 4); //get new serial number //newSerialNum = GetNextSerialNumber("L01", "001", docMonth, docYear, " ", "12", "11"); newSerialNum = NextSerialNumber(); //get new receipt number //newReceiptNo = GetNextSerialNumber("RCN", "002", "001", docYear, "IL-BR-", "12", "11"); newReceiptNo = "IL-MT-" + newSerialNum; SerialNo = Convert.ToInt64(newSerialNum.Trim()); //dr_Main has value from the excel uploaded file. // dr_Main = ""; dr_Sub = ""; Cr_Main = ""; Cr_Sub = ""; Ledger_Type_Cr = ""; Flag = "A"; OperId = ""; Rceipt = new ReceiptsMatch(); Rceipt.AgentCode = Agent_Code; Rceipt.AmountFC = Amount_LC; Rceipt.AmountLC = Amount_LC; Rceipt.BankCode = Bank_Code; Rceipt.BatchNo = BatchNo; Rceipt.BranchCode = Branch_Code; Rceipt.ChequeDate = Chq_Inward_Date; Rceipt.ChequeInwardNo = ""; Rceipt.ChequeTellerNo = Chq_teller_No; Rceipt.CommissionApplicable = Commission_YN; Rceipt.CompanyCode = CompanyCode; Rceipt.CurrencyType = CurrencyType; Rceipt.EntryDate = EntryDate; Rceipt.InsuredCode = Insured_Code; Rceipt.MainAccountCredit = Cr_Main; Rceipt.MainAccountDebit = dr_Main; Rceipt.PayeeName = Payer_Payee_Name; Rceipt.PolicyPaymentMode = Mop; Rceipt.PolicyRegularContribution = Poly_Contrib; Rceipt.ReceiptType = ReceiptType; Rceipt.ReferenceNo = RefNo; Rceipt.SerialNo = SerialNo; Rceipt.SubAccountCredit = Cr_Sub; Rceipt.SubAccountDebit = dr_Sub; Rceipt.DocNo = newReceiptNo; Rceipt.TempTransNo = Temp_TransNo; Rceipt.TranDescription1 = Trans_Desc1; Rceipt.TranDescription2 = Trans_Desc2; Rceipt.TransDate = Eff_Date; Rceipt.TransMode = TransMode; Rceipt.TransType = TransType; Rceipt.CurrencyType = CurrencyType; Rceipt.LedgerTypeCredit = "T"; Rceipt.FileNo = FileNo; Rceipt.ProductCode = ProductCode; Rceipt.Flag = "A"; Rceipt.OperId = "001"; Rceipt.ProcDate = BatchNo.ToString(); //Saving into receipt file try { Save(Rceipt); UpdateMatchStatus(ProposalNo); MatchRecords = MatchRecords + 1; } catch (Exception ex) { strGen_Msg = (" * Error while saving Row: " + (nRow.ToString() + " record... ")); if (ftime == "Y") { ftime = "N"; _err_msg = ErrRoutine(strGen_Msg); } else _err_msg.Add(ErrRoutine(strGen_Msg).ToString()); continue; // goto MyLoop_888; } } } //while read end } catch (Exception h) { strGen_Msg = (" * General System Error : " + (h.ToString())); if (ftime == "Y") { ftime = "N"; _err_msg = ErrRoutine(strGen_Msg); _err_msg.Add(ErrRoutine(strGen_Msg).ToString()); } else _err_msg.Add(ErrRoutine(strGen_Msg).ToString()); goto MyLoop_End_2; //throw new Exception("ERROR!: " + "may not be setup. Pls Check this number against a valid name and setup it up as a member"); } // }//end data reader MyLoop_888: if ((strGen_Msg != "")) { errmsg.Add(strGen_Msg.ToString()); //_err_msg = errmsg; } strGen_Msg = ""; connectionSQL.Close(); UnMatchRecords = ReadRecords - MatchRecords; MyLoop_999: MyLoop_End: my_intCNT = 1; MyLoop_End_1: _err_msg = ErrRoutine("Successful!"); //_unmatch_proposalnos = ErrRoutine("Successful!"); MyLoop_End_2: connectionSQL.Close(); } } }