public bool Reverse_Unit_Purchases(Remit_PurchaseRepo PurchaseRepo)
{
var app = new AppSettings();
// get the pending purchase record
PurchaseRepo.GetPurchasePendingList(Purchase_Id);
TransactionOptions tsOp = new TransactionOptions();
tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp);
tsOp.Timeout = TimeSpan.FromMinutes(60);
using (OracleConnection conn = new OracleConnection(app.conString())) //
{
try
{
//go off
//UPDATE GL_ACCOUNT TABLE AND GL_TRANSACTION TABLE
//get scheme_fund_totals
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (OracleCommand cmd_sft = new OracleCommand())
{
cmd_sft.Connection = conn;
cmd_sft.CommandType = CommandType.StoredProcedure;
cmd_sft.CommandText = "SEL_REV_PURCH_BY_FUND_TOTAL";
cmd_sft.Parameters.Add("p_con_log_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = PurchaseRepo.Purchase_Id;
cmd_sft.Parameters.Add("p_result", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataReader dr = cmd_sft.ExecuteReader();
while (dr.Read())
{
string scheme_fund_id = (dr["SCHEME_FUND_ID"].ToString()); // p_result.Value.ToString();SCHEME_FUND_ID
decimal con_purchased_amt = Convert.ToDecimal(dr["CON_PURCHASE_AMOUNT"].ToString());
decimal surcharge_purchased_amt = 0; // Convert.ToDecimal(dr["SUR_PURCHASE_AMOUNT"].ToString());
//
DynamicParameters param_gl = new DynamicParameters();
param_gl.Add(name: "P_ES_ID", value: PurchaseRepo.ES_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_SCHEME_FUND_ID", value: scheme_fund_id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_CON_PURCHASE_AMOUNT", value: con_purchased_amt, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_gl.Add(name: "P_SUR_PURCHASE_AMOUNT", value: surcharge_purchased_amt, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_gl.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_AUTH_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_AUTH_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.DateTime, direction: ParameterDirection.Input);
conn.Execute("REV_REMIT_PURCHASE_GL_TRANS", param_gl, commandType: CommandType.StoredProcedure);
}
}
// UPDATE DEFERRED MEMBERSHIP
DynamicParameters param_DEF = new DynamicParameters();
param_DEF.Add(name: "P_S_ID", value: PurchaseRepo.Scheme_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_DEF.Add(name: "P_EMPLOYER", value: PurchaseRepo.Employer_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_DEF.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("UPD_REMIT_E_DEFERRED_REV", param_DEF, commandType: CommandType.StoredProcedure);
//// UPDATE REMIT_UNIT_PURCHASES_LOG TABLE
////PurchaseRepo.ApprovePurchaseRecord(PurchaseRepo.Purchase_Id);
//DynamicParameters param = new DynamicParameters();
//param.Add(name: "P_PURCHASE_LOG_ID", value: Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
//param.Add(name: "P_TRANS_STATUS", value: "REVERSED", dbType: DbType.String, direction: ParameterDirection.Input);
//param.Add(name: "P_REV_REASON", value: "NA", dbType: DbType.String, direction: ParameterDirection.Input);
//param.Add(name: "P_AUTH_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
//param.Add(name: "P_AUTH_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
//conn.Execute("REV_REMIT_PURCHASE", param, commandType: CommandType.StoredProcedure);
//Update REMIT_CON_LOG Table
// PurchaseRepo.UpdateConLogRecord(PurchaseRepo);
DynamicParameters param_conl = new DynamicParameters();
param_conl.Add(name: "P_CON_LOG_ID", value: PurchaseRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_CONTRIBUTION", value: PurchaseRepo.Total_Contribution, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_PURCHASE", value: PurchaseRepo.Temp_Con_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_CON_BALANCE", value: PurchaseRepo.Con_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_SURCHARGE", value: PurchaseRepo.Total_Surcharge, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_SUR_PURCHASE", value: PurchaseRepo.Temp_Sur_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_SUR_BALANCE", value: PurchaseRepo.Sur_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_BALANCE", value: PurchaseRepo.Total_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
conn.Execute("REV_REMIT_CON_PURCHASE", param_conl, commandType: CommandType.StoredProcedure);
// delete from purchase trans table and trigger update con_log_details table
DynamicParameters param_con = new DynamicParameters();
param_con.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_con.Add(name: "P_PURCHASE_DATE", value: PurchaseRepo.Trans_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
conn.Execute("REV_REMIT_TRANS_DET_PURCHASE", param_con, commandType: CommandType.StoredProcedure);
// delete from purchase LOG
DynamicParameters paramLOG_con = new DynamicParameters();
paramLOG_con.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("REV_REMIT_LOG_PURCHASE", paramLOG_con, commandType: CommandType.StoredProcedure);
// UPDATE ES TABLE(CASH BALANCE)---------------------
DynamicParameters param_cash = new DynamicParameters();
param_cash.Add(name: "P_ES_ID", value: PurchaseRepo.ES_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_cash.Add(name: "P_CASH_BALANCE", value: PurchaseRepo.Temp_Con_Purchase + PurchaseRepo.Temp_Sur_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
conn.Execute("REV_REMIT_ES_PURCHASE", param_cash, commandType: CommandType.StoredProcedure);
ts.Complete();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
ts.Dispose();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
public bool Approve_Unit_Purchases(Remit_PurchaseRepo PurchaseRepo)
{
var app = new AppSettings();
// get the pending purchase record
PurchaseRepo.GetPurchasePendingList(Purchase_Id);
TransactionOptions tsOp = new TransactionOptions();
tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp);
tsOp.Timeout = TimeSpan.FromMinutes(160);
using (OracleConnection conn = new OracleConnection(app.conString())) //
{
try
{
// UPDATE REMIT_CON_LOG TABLE
PurchaseRepo.Temp_Con_Purchase = 0;
PurchaseRepo.Temp_Sur_Purchase = 0;
if (PurchaseRepo.Con_Balance >= PurchaseRepo.Cash_Balance)
{
PurchaseRepo.Temp_Con_Purchase = Math.Round(PurchaseRepo.Cash_Balance, 2);
PurchaseRepo.Con_Balance = Math.Round(PurchaseRepo.Con_Balance, 2) - Math.Round(PurchaseRepo.Temp_Con_Purchase, 2);
PurchaseRepo.Cash_Balance = 0;
PurchaseRepo.Total_Purchase = Math.Round(PurchaseRepo.Total_Purchase, 2) + Math.Round(PurchaseRepo.Temp_Con_Purchase, 2);
}
else if (PurchaseRepo.Con_Balance < PurchaseRepo.Cash_Balance)
{
PurchaseRepo.Temp_Con_Purchase = Math.Round(PurchaseRepo.Con_Balance, 2);
PurchaseRepo.Cash_Balance = Math.Round(PurchaseRepo.Cash_Balance, 2) - Math.Round(PurchaseRepo.Temp_Con_Purchase, 2);
PurchaseRepo.Con_Balance = 0;
PurchaseRepo.Total_Purchase = Math.Round(PurchaseRepo.Total_Purchase, 2) + Math.Round(PurchaseRepo.Temp_Con_Purchase, 2);
}
else if (PurchaseRepo.Sur_Balance > 0 && PurchaseRepo.Cash_Balance > 0)
{
if (PurchaseRepo.Sur_Balance >= PurchaseRepo.Cash_Balance)
{
PurchaseRepo.Temp_Sur_Purchase = Math.Round(PurchaseRepo.Cash_Balance, 2);
PurchaseRepo.Sur_Balance = Math.Round(PurchaseRepo.Sur_Balance, 2) - Math.Round(PurchaseRepo.Temp_Sur_Purchase, 2);
PurchaseRepo.Cash_Balance = 0;
PurchaseRepo.Total_Sur_Purchase = Math.Round(PurchaseRepo.Total_Sur_Purchase, 2) + Math.Round(PurchaseRepo.Temp_Sur_Purchase, 2);
}
else if (PurchaseRepo.Sur_Balance < PurchaseRepo.Cash_Balance)
{
PurchaseRepo.Temp_Sur_Purchase = Math.Round(PurchaseRepo.Sur_Balance, 0);
PurchaseRepo.Cash_Balance = Math.Round(PurchaseRepo.Cash_Balance, 2) - Math.Round(PurchaseRepo.Temp_Sur_Purchase, 2);
PurchaseRepo.Sur_Balance = 0;
PurchaseRepo.Total_Sur_Purchase = Math.Round(PurchaseRepo.Total_Sur_Purchase, 2) + Math.Round(PurchaseRepo.Temp_Sur_Purchase, 2);
}
}
PurchaseRepo.Total_Balance = Math.Round(PurchaseRepo.Con_Balance, 2) + Math.Round(PurchaseRepo.Sur_Balance, 2);
// UPDATE REMIT_UNIT_PURCHASES_LOG TABLE
//PurchaseRepo.ApprovePurchaseRecord(PurchaseRepo.Purchase_Id);
DynamicParameters param = new DynamicParameters();
param.Add(name: "P_PURCHASE_LOG_ID", value: Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_TRANS_STATUS", value: "ACTIVE", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_AUTH_STATUS", value: "AUTHORIZED", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_CON_PURCHASE_AMOUNT", value: PurchaseRepo.Temp_Con_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param.Add(name: "P_SUR_PURCHASE_AMOUNT", value: PurchaseRepo.Temp_Sur_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param.Add(name: "P_AUTH_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_AUTH_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
conn.Execute("APP_REMIT_PURCHASE_NEW", param, commandType: CommandType.StoredProcedure);
//Update REMIT_CON_LOG Table
// PurchaseRepo.UpdateConLogRecord(PurchaseRepo);
DynamicParameters param_conl = new DynamicParameters();
param_conl.Add(name: "P_CON_LOG_ID", value: PurchaseRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_CONTRIBUTION", value: PurchaseRepo.Total_Contribution, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_PURCHASE", value: PurchaseRepo.Total_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_CON_BALANCE", value: PurchaseRepo.Con_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_SURCHARGE", value: PurchaseRepo.Total_Surcharge, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_SUR_PURCHASE", value: PurchaseRepo.Total_Sur_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_SUR_BALANCE", value: PurchaseRepo.Sur_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_TOTAL_BALANCE", value: PurchaseRepo.Total_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_conl.Add(name: "P_PURCHASE_DATE", value: PurchaseRepo.Trans_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
conn.Execute("UPD_REMIT_CON_PURCHASE", param_conl, commandType: CommandType.StoredProcedure);
// UPDATE REMIT_CON_DETAILS TABLE
if (PurchaseRepo.Total_Contribution > 0)
{
// PurchaseRepo.UpdateConDetailsRecord(PurchaseRepo);
DynamicParameters param_con = new DynamicParameters();
param_con.Add(name: "P_CON_LOG_ID", value: PurchaseRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_con.Add(name: "P_TEMP_CON_PURCHASE", value: PurchaseRepo.Temp_Con_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_con.Add(name: "P_TOTAL_CONTRIBUTION", value: PurchaseRepo.Total_Contribution, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_con.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_con.Add(name: "P_PURCHASE_DATE", value: PurchaseRepo.Trans_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
param_con.Add(name: "P_CON_TYPE", value: PurchaseRepo.Con_Type, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("UPD_REMIT_CON_DET_PURCHASE", param_con, commandType: CommandType.StoredProcedure);
}
if (PurchaseRepo.Total_Surcharge > 0)
{
//PurchaseRepo.UpdateSurDetailsRecord(PurchaseRepo);
DynamicParameters param_def = new DynamicParameters();
param_def.Add(name: "P_CON_LOG_ID", value: PurchaseRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_def.Add(name: "P_TEMP_SUR_PURCHASE", value: PurchaseRepo.Temp_Sur_Purchase, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_def.Add(name: "P_TOTAL_SURCHARGE", value: PurchaseRepo.Total_Surcharge, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_def.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_def.Add(name: "P_PURCHASE_DATE", value: PurchaseRepo.Trans_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
conn.Execute("UPD_REMIT_SUR_DET_PURCHASE", param_def, commandType: CommandType.StoredProcedure);
}
//go off
//UPDATE GL_ACCOUNT TABLE AND GL_TRANSACTION TABLE
//get scheme_fund_totals
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (OracleCommand cmd_sft = new OracleCommand())
{
cmd_sft.Connection = conn;
cmd_sft.CommandType = CommandType.StoredProcedure;
cmd_sft.CommandText = "SEL_REMIT_PURCH_BY_FUND_TOTAL";
cmd_sft.Parameters.Add("p_con_log_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = PurchaseRepo.Con_Log_Id;
cmd_sft.Parameters.Add("p_result", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataReader dr = cmd_sft.ExecuteReader();
while (dr.Read())
{
string scheme_fund_id = (dr["SCHEME_FUND_ID"].ToString()); // p_result.Value.ToString();SCHEME_FUND_ID
decimal con_purchased_amt = Convert.ToDecimal(dr["CON_PURCHASE_AMOUNT"].ToString());
decimal surcharge_purchased_amt = Convert.ToDecimal(dr["SUR_PURCHASE_AMOUNT"].ToString());
//
DynamicParameters param_gl = new DynamicParameters();
param_gl.Add(name: "P_ES_ID", value: PurchaseRepo.ES_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_SCHEME_FUND_ID", value: scheme_fund_id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_CON_PURCHASE_AMOUNT", value: con_purchased_amt, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_gl.Add(name: "P_SUR_PURCHASE_AMOUNT", value: surcharge_purchased_amt, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_gl.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_AUTH_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_AUTH_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.DateTime, direction: ParameterDirection.Input);
conn.Execute("ADD_REMIT_PURCHASE_GL_TRANS", param_gl, commandType: CommandType.StoredProcedure);
}
}
// CLEAR TEMP FIELDS in db---------------------
DynamicParameters param_tem = new DynamicParameters();
param_tem.Add(name: "P_CON_LOG_ID", value: PurchaseRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_tem.Add(name: "P_PURCHASE_LOG_ID", value: PurchaseRepo.Purchase_Id, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("UPD_REMIT_PURCHASE_ZERO", param_tem, commandType: CommandType.StoredProcedure);
// UPDATE ES TABLE(CASH BALANCE)---------------------
DynamicParameters param_cash = new DynamicParameters();
param_cash.Add(name: "P_ES_ID", value: PurchaseRepo.ES_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_cash.Add(name: "P_CASH_BALANCE", value: PurchaseRepo.Cash_Balance, dbType: DbType.Decimal, direction: ParameterDirection.Input);
conn.Execute("UPD_REMIT_ES_PURCHASE", param_cash, commandType: CommandType.StoredProcedure);
///GET DEFFERED MEMBERS
//var param = new DynamicParameters();
//param.Add("P_SID", GlobalValue.Report_Param_1, DbType.String, ParameterDirection.Input);
//param.Add("P_DATE", GlobalValue.Report_Param_2, DbType.String, ParameterDirection.Input);
//con.GetConnection().Execute("Z_DEFERRED", param, commandType: CommandType.StoredProcedure);
//// recalculate units for employees
//DynamicParameters param_cash2 = new DynamicParameters();
//param_cash2.Add(name: "P_E_ID", value: PurchaseRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input);
//conn.Execute("UPD_EMPLOYEE_RECAL", param_cash2, commandType: CommandType.StoredProcedure);
ts.Complete();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
ts.Dispose();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}