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