public DataTable exceldataLife(string location)
{
try
{
CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches();
String BatchNo = IBTBatch.GetBatchNo("BATCH_NO");
DateTime CurrentDate = DateTime.Now;
DataTable ds = new DataTable();
OleDbCommand excelCommand = new OleDbCommand();
OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + location + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection excelConn = new OleDbConnection(excelConnStr);
excelConn.Open();
DataTable dtPatterns = new DataTable();
//string usrName = "deshapriya.sooriyaTest";//Remove Please
excelCommand = new OleDbCommand("SELECT `SERIAL`".ToString() + "as SERIAL,`TRANSACTION_CODE`".ToString() + " as TRANSACTION_CODE,`VOUCHER_DATE`".ToString() + " as VOUCHER_DATE,`PAYMENT_MODE`".ToString() + " as PAYMENT_MODE,`NARRATION`".ToString() + " as NARRATION,`POLICY_NUMBER`".ToString() + " as POLICY_NUMBER,`AMOUNT`".ToString() + " as AMOUNT,`POLICY_FEE`".ToString() + " as POLICY_FEE,`CASH_ACCOUNT`".ToString() + " as CASH_ACCOUNT,`POLICY_BRANCH`".ToString() + " as POLICY_BRANCH,`PAYING_PARTY`".ToString() + " as PAYING_PARTY,`INSTRUMENT_NUMBER`".ToString() + " as INSTRUMENT_NUMBER,`INSTRUMENT_DATE` ".ToString() + " as INSTRUMENT_DATE,`STATUS`".ToString() + " as STATUS,'" + usrName + "' as CREATEDBY,'" + CurrentDate + "' as CREATED_DATE, '" + BatchNo + "' as BATCH_NO, 'UPLOADED' as BATCH_STATUS, 'MANUAL' as BATCH_TYPE FROM [UPLOAD$]", excelConn);
//excelCommand = new OleDbCommand("SELECT `SERIAL`".ToString() + "as SERIAL,`TRANSACTION_CODE`".ToString() + " as TRANSACTION_CODE,`VOUCHER_DATE`".ToString() + " as VOUCHER_DATE,`PAYMENT_MODE`".ToString() + " as PAYMENT_MODE,`NARRATION`".ToString() + " as NARRATION,`POLICY_NUMBER`".ToString() + " as POLICY_NUMBER,`AMOUNT`".ToString() + " as AMOUNT,`POLICY_FEE`".ToString() + " as POLICY_FEE,`CASH_ACCOUNT`".ToString() + " as CASH_ACCOUNT,`POLICY_BRANCH`".ToString() + " as POLICY_BRANCH,`PAYING_PARTY`".ToString() + " as PAYING_PARTY,`INSTRUMENT_NUMBER`".ToString() + " as INSTRUMENT_NUMBER,`INSTRUMENT_DATE` ".ToString() + " as INSTRUMENT_DATE,`STATUS`".ToString() + " as STATUS,'" + usrName + "' as CREATEDBY,'" + CurrentDate + "' as CREATED_DATE, '" + BatchNo + "' as BATCH_NO, 'UPLOADED' as BATCH_STATUS, 'MANUAL' as BATCH_TYPE FROM [UPLOAD$]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dtPatterns);
return(dtPatterns);
}
catch (Exception)
{
throw;
}
}
public void UpdateMRPMCR(string Type)//----------------Update MRP MCR-------------------------------//
{
try
{
DataTable DtTemp = GetUnmatchedDetails();
String StrMRP = "";
String StrMCR = "";
ArrayList UnmatchedList = new ArrayList();
if (DtTemp.Rows.Count == 0)
{
return;
}
foreach (DataRow Dr in DtTemp.Rows)
{
if (Dr["DeptDesc"].ToString() == "MRP")
{
StrMRP = StrMRP + "'" + Dr["serial_NO"].ToString() + "',";
}
if (Dr["DeptDesc"].ToString() == "MCR")
{
StrMCR = StrMCR + "'" + Dr["serial_NO"].ToString() + "',";
}
}
if (StrMCR != "")
{
StrMCR = StrMCR.Remove(StrMCR.Length - 1, 1);
}
else
{
StrMCR = "'xxxxxxxxxxxxxxxxx'";
}
if (StrMRP != "")
{
StrMRP = StrMRP.Remove(StrMRP.Length - 1, 1);
}
else
{
StrMRP = "'xxxxxxxxxxxxxxxxx'";
}
//------------------------------Get MRP/MCR Details---------------------------------
var dtMRP = new DataTable();
var dtMCR = new DataTable();
SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnForMRP"].ConnectionString);
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandType = System.Data.CommandType.Text;
//sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME, " +
// " C.Premiumpolicyfee AS PREMIUM,UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " +
// " C.Ibtdate AS IBTDATE,UPPER(REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','')) AS CASHACCOUNT," +
// " 'MRP' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM Chequedetail C,BANK BAN " +
// " WHERE BAN.BNKCode = C.Code " +
// " --AND Convert(datetime,C.ibtDate,103) >= '05/08/2016'" +
// " --AND Convert(datetime,C.ibtDate,103) <= '11/08/2016' " +
// " AND REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','') IN (" + StrMRP + ") " +
// " UNION " +
// " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME," +
// " C.Premiumpolicyfee AS PREMIUM,UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " +
// " C.Ibtdate AS IBTDATE,UPPER(REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','')) AS CASHACCOUNT, " +
// " 'MCR' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM ChequedetailMicro C,BANK BAN " +
// " WHERE BAN.BNKCode = C.Code " +
// " --AND Convert(datetime,C.ibtDate,103) >= '05/08/2016' " +
// " --AND Convert(datetime,C.ibtDate,103) <= '11/08/2016' " +
// " AND REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','') IN (" + StrMCR + ") ";
//25-09-2017
//sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME, " +
// " C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " +
// " C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT," +
// " 'MRP' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM Chequedetail C,BANK BAN " +
// " WHERE BAN.BNKCode = C.Code " +
// " AND C.ACCOUNT IN (" + StrMRP + ") " +
// " UNION " +
// " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME," +
// " C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " +
// " C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT, " +
// " 'MCR' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM ChequedetailMicro C,BANK BAN " +
// " WHERE BAN.BNKCode = C.Code " +
// " AND C.ACCOUNT IN (" + StrMCR + ") order by PRODUCT";
if (Type == "MRP")
{
sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME, " +
" C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " +
" C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT," +
" 'MRP' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count,Premiumpolicyfee FROM Chequedetail C,BANK BAN " +
" WHERE BAN.BNKCode = C.Code " +
" AND C.ACCOUNT IN (" + StrMRP + ") ";
}
if (Type == "MCR")
{
sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME," +
" C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " +
" C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT, " +
" 'MCR' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count,Premiumpolicyfee FROM ChequedetailMicro C,BANK BAN " +
" WHERE BAN.BNKCode = C.Code " +
" AND C.ACCOUNT IN (" + StrMCR + ") order by PRODUCT";
}
sqlcmd.Connection = sqlconn;
sqlconn.Open();
var dataReader = sqlcmd.ExecuteReader();
dtMRP.Load(dataReader);
DataView dv = dtMRP.DefaultView;
dv.Sort = "IBTDATE desc";
dtMRP = dv.ToTable();
sqlconn.Close();
sqlconn.Dispose();
dataReader.Close();
sqlcmd.Dispose();
DataTable DtRecCount = new DataTable();
DtRecCount.Columns.Add("SerialNo", typeof(string));
DtRecCount.Columns.Add("Count", typeof(int));
ArrayList arrRegular = new ArrayList();
ArrayList arrBulk = new ArrayList();
foreach (DataRow item in dtMRP.Rows)
{
String CASHACCOUNT = item["CASHACCOUNT"].ToString();
DataRow[] DrTemp1 = dtMRP.Select("CASHACCOUNT = '" + CASHACCOUNT + "'");
if (DrTemp1.Count() > 1)
{
if (!arrBulk.Contains(CASHACCOUNT))
{
if (!(CASHACCOUNT == "" || CASHACCOUNT == " " || CASHACCOUNT == null))
{
arrBulk.Add(CASHACCOUNT);
}
}
}
else
{
if (!(CASHACCOUNT == "" || CASHACCOUNT == " " || CASHACCOUNT == null))
{
arrRegular.Add(CASHACCOUNT);
}
}
}
CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches();
String BatchNo = IBTBatch.GetBatchNo("BATCH_NO");
//------------------------- MRP / MCR Receipts Updation---------------------
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString()))
{
conn.Open();
OracleCommand command = conn.CreateCommand();
OracleTransaction transaction;
transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
command.Transaction = transaction;
foreach (var item in arrRegular)
{
String CASHACCOUNT = item.ToString();
DataRow Dr = dtMRP.Select("CASHACCOUNT = '" + CASHACCOUNT + "'").Single();
//-----------------------Regular Receipting-------------------------------------
//OracleCommand cmd = new OracleCommand();//conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandText = "SP_FAS_UPDATE_MRPMCR_DTL";
//cmd.CommandType = CommandType.StoredProcedure;
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SP_FAS_IBT_UPD_MRPMCR_DTL";
cmd.CommandType = CommandType.StoredProcedure;
String SerialNo = Dr["CASHACCOUNT"].ToString();
String PolicyNo = Dr["PROPOSALNO"].ToString();
String Premium = Dr["PREMIUM"].ToString();
String PolicyFee = Dr["POLICYFEE"].ToString();
String MRPNarration = Dr["HNBCODE"].ToString() + '-' + Dr["CASHACCOUNT"].ToString() + ';' + PolicyNo + ':' + Dr["CLIENTNAME"].ToString();
String MRPPayingParty = Dr["BRANCHNAME"].ToString();
String Premiumpolicyfee = Dr["Premiumpolicyfee"].ToString();
cmd.Parameters.AddWithValue("vSerialNo", OracleType.VarChar).Value = SerialNo;
cmd.Parameters.AddWithValue("vPolicyNo", OracleType.VarChar).Value = PolicyNo;
cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = Premium;
cmd.Parameters.AddWithValue("vMRPNarration", OracleType.VarChar).Value = MRPNarration.Trim();
cmd.Parameters.AddWithValue("vMRPPayingParty", OracleType.VarChar).Value = "Hatton National Bank PLC-" + MRPPayingParty.Trim();
cmd.Parameters.AddWithValue("vMRPPolicyFee", OracleType.Number).Value = PolicyFee;
cmd.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo;
//23-11-2017
cmd.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = Type;// "MRP";
cmd.Parameters.AddWithValue("vPremiumpolicyfee", OracleType.Number).Value = Premiumpolicyfee;
cmd.Parameters.Add("vResult", OracleType.VarChar, 100).Direction = ParameterDirection.Output;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
string vResult = cmd.Parameters["vResult"].Value.ToString();
if (vResult != "Updated")
{
UnmatchedList.Add(vResult);
}
//UnmatchedList.Add(vResult);
//-------------------------------------------------------------------------------
}
//------------------------Bulk Receipting----------------------------------------
Int16 SubNo = 1;
foreach (var item in arrBulk)
{
String CASHACCOUNT = item.ToString();
DataRow[] Dr = dtMRP.Select("CASHACCOUNT = '" + CASHACCOUNT + "'");
DataTable dtTemp = Dr.CopyToDataTable();
//---------Validate MRP Divivion Entered Total With Statement Total--------
Double TempMRPTotal = 0;
foreach (DataRow row in dtTemp.Rows)
{
TempMRPTotal = TempMRPTotal + (Convert.ToDouble(row["Premium"].ToString()) + Convert.ToDouble(row["POLICYFEE"].ToString()));
}
Boolean Temp = ValidateTotals(item.ToString(), TempMRPTotal);
if (Temp != true)
{
UnmatchedList.Add(item.ToString());
continue;
}
//-------------------------------------------------------------------------
Int16 Count = 1;
foreach (DataRow row in dtTemp.Rows)
{
String SerialNo = row["cashaccount"].ToString();
String RefNumber = row["proposalno"].ToString();
String Amount = row["Premium"].ToString();
String PolicyFee = row["POLICYFEE"].ToString();
String MRPNarration = row["HNBCODE"].ToString() + '-' + row["CASHACCOUNT"].ToString() + ';' + RefNumber + ':' + row["CLIENTNAME"].ToString();
String MRPPayingParty = row["BRANCHNAME"].ToString();
OracleCommand cmdBulk = new OracleCommand();//conn.CreateCommand();
cmdBulk.Connection = conn;
cmdBulk.CommandText = "SP_FAS_IBT_UPD_MRPMCR_DTL_BULK";
cmdBulk.CommandType = CommandType.StoredProcedure;
//cmdBulk.Parameters.AddWithValue("vID", OracleType.Int32).Value = DtlID;
cmdBulk.Parameters.AddWithValue("vSerialNo", OracleType.VarChar).Value = SerialNo;
cmdBulk.Parameters.AddWithValue("vSubNo", OracleType.VarChar).Value = SubNo;
cmdBulk.Parameters.AddWithValue("vRefNumber", OracleType.VarChar).Value = RefNumber;
cmdBulk.Parameters.AddWithValue("vAmount", OracleType.Number).Value = Convert.ToDouble(Amount);
cmdBulk.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = Type;// "MRP";
cmdBulk.Parameters.AddWithValue("vCount", OracleType.Number).Value = Count;
cmdBulk.Parameters.AddWithValue("vMRPNarration", OracleType.VarChar).Value = MRPNarration.Trim();
cmdBulk.Parameters.AddWithValue("vMRPPayingParty", OracleType.VarChar).Value = "Hatton National Bank PLC-" + MRPPayingParty.Trim();
cmdBulk.Parameters.AddWithValue("vMRPPolicyFee", OracleType.Number).Value = PolicyFee;
cmdBulk.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo;
SubNo = Convert.ToInt16(SubNo + 1);
cmdBulk.Transaction = transaction;
cmdBulk.ExecuteNonQuery();
Count++;
}
SubNo = 1;
}
//-------------------------------------------------------------------------------
transaction.Commit();
conn.Close();
//Emailling The Figure -- Unmatched List(Total check with uploaded record total if not tally reject)
if (UnmatchedList.Count > 0)
{
CommonCLS.IBTEmails.EmailAlertCommon(9, UnmatchedList, "");
}
}
//----------------------------------------------------------------
}
catch (Exception ex)
{
throw;
}
}
protected void cmdConfirm_Click(object sender, EventArgs e)//----------------------------------Records Confirm Click Event----------------------------------------//
{
//double dr_total = 0;
if (page_result.Visible == true)
{
page_result.Visible = false;
}
get_LiveRecords_Total();
if (int.Parse(Session["TotalRecords_WhenPageLoad"].ToString()) == LiveRecord_Count)
{
//Check Totals-------------
Double TempTot = 0;
DataTable dtTemp = (DataTable)Session["TransList"];
if (dtTemp == null)
{
return;
}
foreach (DataRow item in dtTemp.Rows)
{
TempTot = TempTot + Convert.ToDouble(item["Amount"].ToString());
}
//*****************************************-----New Fn-----**********************************//
double real_total = double.Parse(txtTotal.Text);
double calculation_differance = TempTot - real_total;
if ((Math.Abs(calculation_differance) <= 0.1) || (calculation_differance == 0))
{
if (calculation_differance > 0)
{
calculation_differance = (-1) * (calculation_differance);
}
else
{
calculation_differance = (1) * (calculation_differance);
}
}
else
{
call_error_msg(false);
lblResult.Text = "Calculation Differance Is Greater than 0.1 ";
return;
}
//********************************************************************************************//
CommonCLS.CommonFunctions clsCom = new CommonCLS.CommonFunctions();
DataTable Dt = (DataTable)Session["TransList"];
Int16 SubNo = 1;
CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches();
String BatchNo = IBTBatch.GetBatchNo("BATCH_NO");
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString()))
{
conn.Open();
OracleCommand command = conn.CreateCommand();
OracleTransaction transaction;
transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
try
{
Int16 Count = 1;
foreach (DataRow item in Dt.Rows)
{
if (item["Status"].ToString().Trim() != "New")
{
continue;
}
OracleCommand cmd = new OracleCommand();//conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_fas_ibt_BulkReceipt";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("vID", OracleType.Int32).Value = DtlID;
cmd.Parameters.AddWithValue("vSubNo", OracleType.VarChar).Value = SubNo;
cmd.Parameters.AddWithValue("vRefNumber", OracleType.VarChar).Value = item["Ref_no"].ToString();
//cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = double.Parse(item["Amount"].ToString());
cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = double.Parse(item["Amount"].ToString()) + (calculation_differance);
calculation_differance = 0;
cmd.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = usrName;
cmd.Parameters.AddWithValue("vCount", OracleType.Number).Value = Count;
cmd.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo;
SubNo = Convert.ToInt16(SubNo + 1);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
Count++;
}
arrRemoveListRV = (ArrayList)Session["arrRemoveListRV"];
if (arrRemoveListRV != null)
{
foreach (Object obj in arrRemoveListRV)
{
OracleCommand cmd = new OracleCommand();//conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_fas_ibt_bulk_rv_Remove";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("BulkRV_ID", OracleType.Int32).Value = Convert.ToInt32(obj);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, "----Bulk Receipt Confirmed!---- By: " + usrName + " Base Record ID :" + DtlID, Server.MapPath("~/IBTLogFiles/Log.txt"));
conn.Close();
}
catch (Exception ex)
{
CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.InnerException.ToString(), Server.MapPath("~/IBTLogFiles/Log.txt"));
call_error_msg(false);
lblResult.Text = ex.InnerException.ToString();
}
}
ClearControls();
running_count = 0;
if (running_count == 0)
{
get_TotalRecords_WhenPageLoad();
running_count = running_count + 1;//New
}
call_error_msg(true);
lblResult.Text = "Records Confirmed For Receipt...!";
}
else
{
running_count = 0;
call_error_msg(false);
lblResult.Text = "Record Locked By Another User...! Please Refresh And Try Again!";
}
}
private void ConfirmRecords(Boolean SelectAll)//-----------------------------------------------Confirm All Records------------------------------------------------//
{
try
{
//Int32 RecCount = GetUnreceiptedCount();
//if (RecCount > 499)
//{
// call_error_msg(true);
// lblResult.Text = "Please Run the relavant receipt batch and do the other confirmations...!";
// return;
//}
CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches();
String BatchNo = IBTBatch.GetBatchNo("BATCH_NO");
if (GrdIBTConfirmation.Rows.Count > 0)
{
using (OracleConnection OraCon = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString()))
{
OraCon.Open();
OracleCommand command = OraCon.CreateCommand();
OracleTransaction transaction;
// Start a local transaction
transaction = OraCon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
try
{
DataTable Dt = (DataTable)Session["ConfirmedData"];
//when user select all the records to be confirmed
if (SelectAll)
{
Int16 Count = 0;
foreach (DataRow item in Dt.Rows)
{
//if (Count == 500)
//{
// call_error_msg(true);
// lblResult.Text = "Please Run the relavant receipt batch and do the other confirmations...!";
// break;
//}
OracleCommand cmd = OraCon.CreateCommand();
cmd.CommandText = "SP_FAS_IBT_CONFIRMATION";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("vPolicyNo", OracleType.VarChar).Value = item["POLICY_NO"].ToString();
cmd.Parameters.Add("vProduct", OracleType.VarChar).Value = item["VALUE"].ToString();
cmd.Parameters.Add("vAmount", OracleType.Number).Value = item["CREDIT"].ToString();
cmd.Parameters.Add("vID", OracleType.Number).Value = Convert.ToInt64(item["ID"].ToString());
cmd.Parameters.Add("vCreatedBy", OracleType.VarChar).Value = usrName;
cmd.Parameters.Add("vBatchNo", OracleType.VarChar).Value = BatchNo;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery(); //***********************************************************************************************Test
Count++;
}
}
else
{
foreach (GridViewRow row in GrdIBTConfirmation.Rows)//Selected Records
{
CheckBox chk = (row.Cells[0].FindControl("chkboxselect") as CheckBox);
DropDownList Commision = row.Cells[1].FindControl("ddlCommision") as DropDownList;
if ((chk != null && chk.Checked) || SelectAll)
{
OracleCommand cmd = OraCon.CreateCommand();
cmd.CommandText = "SP_FAS_IBT_CONFIRMATION";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("vPolicyNo", OracleType.VarChar).Value = row.Cells[6].Text.ToString();
cmd.Parameters.Add("vProduct", OracleType.VarChar).Value = row.Cells[9].Text.ToString();
cmd.Parameters.Add("vAmount", OracleType.Number).Value = Convert.ToDouble(row.Cells[7].Text);
cmd.Parameters.Add("vID", OracleType.Number).Value = Convert.ToInt64(row.Cells[1].Text);
cmd.Parameters.Add("vCreatedBy", OracleType.VarChar).Value = usrName;
cmd.Parameters.Add("vBatchNo", OracleType.VarChar).Value = BatchNo;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();//***********************************************************************************************Test
//--------Send Data To TCS for Receipting---------
// Exess
// Partial
// When Two Debit Returns
// When PPW Cancelled
//------------------------------------------------
}
}
}
transaction.Commit();
OraCon.Close();
GrdIBTConfirmation.DataSource = null;
GrdIBTConfirmation.DataBind();
//--------Load New business and renewal system detected exeptions---------
LoadExeptionData();
//------------------------------------------------------------------------
call_error_msg(true);
lblResult.Text = "Confirmation Successfull...!";
CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, "----IBT Confirmation Successfull ---- Active User: "******"~/IBTLogFiles/Log.txt"));
//LoadData();
}
catch (Exception ex)
{
CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.InnerException.ToString(), Server.MapPath("~/IBTLogFiles/Log.txt"));
call_error_msg(false);
lblResult.Text = ex.InnerException.ToString();
}
}
}
else
{
call_error_msg(false);
lblResult.Text = "No Matching Records Available!";
}
}
catch (Exception)
{
throw;
}
}
