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