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();
                }
            }
        }