public void DelRecord(Remit_Unit_TransferRepo Unit_TransferRepo) { try { //Get Connection AppSettings app = new AppSettings(); conn = app.GetConnection(); DynamicParameters param = new DynamicParameters(); param.Add(name: "P_PURCHASE_LOG_ID", value: Unit_TransferRepo.Purchase_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_CON_LOG_ID", value: Unit_TransferRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_ESF_ID", value: Unit_TransferRepo.ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_MAKER_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input); conn.Execute("ADD_REMIT_UNIT_DEL_EMP", param, commandType: CommandType.StoredProcedure); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); if (conn != null) { conn = null; } } } }
// merge employee accounts public bool Approve_Unit_Merge(Remit_Unit_TransferRepo Unit_TransferRepo) { var app = new AppSettings(); // get the pending purchase record Unit_TransferRepo.GetPendingUTList(Unit_TransferRepo); TransactionOptions tsOp = new TransactionOptions(); tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot; TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp); tsOp.Timeout = TimeSpan.FromMinutes(20); using (OracleConnection conn = new OracleConnection(app.conString())) // { try { DynamicParameters param = new DynamicParameters(); param.Add(name: "P_FROM_ESF_ID", value: Unit_TransferRepo.From_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_TO_ESF_ID", value: Unit_TransferRepo.To_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_MAKER_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_MAKE_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input); param.Add(name: "P_REASON_TRANSFER", value: Unit_TransferRepo.Reason_Transfer, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_FROM_NAME", value: Unit_TransferRepo.From_Surname + " " + Unit_TransferRepo.From_First_Name, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_TO_NAME", value: Unit_TransferRepo.To_Surname + " " + Unit_TransferRepo.To_First_Name, dbType: DbType.String, direction: ParameterDirection.Input); conn.Execute("ADD_REMIT_UNIT_MERGE_EMP", param, commandType: CommandType.StoredProcedure); ts.Complete(); return(true); } catch (Exception ex) { string xx = ex.ToString(); throw; } finally { ts.Dispose(); if (conn.State == ConnectionState.Open) { conn.Close(); } } } }
public void SaveRecord(Remit_Unit_TransferRepo Unit_TransferRepo) { try { //Get Connection AppSettings app = new AppSettings(); conn = app.GetConnection(); DynamicParameters param = new DynamicParameters(); param.Add(name: "P_PURCHASE_LOG_ID", value: Unit_TransferRepo.Purchase_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_CON_LOG_ID", value: Unit_TransferRepo.Con_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_FROM_ESF_ID", value: Unit_TransferRepo.From_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_TO_ESF_ID", value: Unit_TransferRepo.To_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_EMPLOYER_AMT", value: Unit_TransferRepo.Employer_Amt, dbType: DbType.Decimal, direction: ParameterDirection.Input); param.Add(name: "P_EMPLOYEE_AMT", value: Unit_TransferRepo.Employee_Amt, dbType: DbType.Decimal, direction: ParameterDirection.Input); param.Add(name: "P_EMPLOYER_UNITS", value: Unit_TransferRepo.Employer_Units, dbType: DbType.Decimal, direction: ParameterDirection.Input); param.Add(name: "P_EMPLOYEE_UNITS", value: Unit_TransferRepo.Employee_Units, dbType: DbType.Decimal, direction: ParameterDirection.Input); param.Add(name: "P_UNIT_PRICE", value: Unit_TransferRepo.Unit_Price, dbType: DbType.Decimal, direction: ParameterDirection.Input); param.Add(name: "P_PURCHASE_TYPE", value: Unit_TransferRepo.Purchase_Type, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_FROM_NAME", value: Unit_TransferRepo.From_Surname + " " + Unit_TransferRepo.From_First_Name, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_TO_NAME", value: Unit_TransferRepo.To_Surname + " " + Unit_TransferRepo.To_First_Name, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_FOR_MONTH", value: Unit_TransferRepo.For_Month, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_FOR_YEAR", value: Unit_TransferRepo.For_Year, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_REASON_TRANSFER", value: Unit_TransferRepo.Reason_Transfer, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_MAKER_ID", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_MAKE_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input); conn.Execute("ADD_REMIT_UNIT_TRANSFER_EMP", param, commandType: CommandType.StoredProcedure); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); if (conn != null) { conn = null; } } } }
public List <Remit_Unit_TransferRepo> GetPurchasesList2(Remit_Unit_TransferRepo Unit_TransferRepo) { try { var app = new AppSettings(); conn = app.GetConnection(); List <Remit_Unit_TransferRepo> bn = new List <Remit_Unit_TransferRepo>(); string query = "Select * from VW_ESF_UNIT_TRANSFER WHERE SCHEME_FUND_ID = '" + Unit_TransferRepo.Scheme_Fund_Id + "' and ESF_ID = '" + Unit_TransferRepo.ESF_Id + "' "; return(bn = conn.Query <Remit_Unit_TransferRepo>(query).ToList()); } catch (Exception ex) { throw ex; } finally { conn.Dispose(); } }
//// FOR ACTIVE RECEIPTS //public DataSet ReceiptActiveData() //{ // try // { // //Get connection // var app = new AppSettings(); // con = app.GetConnection(); // DataSet ds = new DataSet(); // OracleDataAdapter da = new OracleDataAdapter(); // OracleCommand cmd = new OracleCommand(); // OracleParameter param = cmd.CreateParameter(); // cmd.CommandText = "SEL_REMIT_RECEIPT_ACTIVE"; // cmd.CommandType = CommandType.StoredProcedure; // cmd.Connection = (OracleConnection)con; // param = cmd.Parameters.Add("cur", OracleDbType.RefCursor); // param.Direction = ParameterDirection.Output; // da = new OracleDataAdapter(cmd); // da.Fill(ds, "receipt"); // return ds; // } // catch (Exception) // { // throw; // } // public IEnumerable<Remit_ReceiptRepo> GetReceiptActiveList() // { // try // { // DataSet dt = ReceiptActiveData(); // var eList = dt.Tables[0].AsEnumerable().Select(row => new Remit_ReceiptRepo // { // ES_Id = row.Field<string>("ES_ID"), // Scheme_Id = row.Field<string>("SCHEME_ID"), // Scheme_Name = row.Field<string>("SCHEME_NAME"), // Employer_Id = row.Field<string>("EMPLOYER_ID"), // Employer_Name = row.Field<string>("Employer_Name"), // Receipt_Id = row.Field<string>("RECEIPT_ID"), // Trans_Amount = row.Field<Decimal>("TRANS_AMOUNT"), // Actual_Receipt_Date = row.Field<DateTime>("ACTUAL_RECEIPT_DATE"), // Narration = row.Field<string>("NARRATION"), // Narration_Syatem = row.Field<string>("NARRATION_SYSTEM"), // Payment_Mode = row.Field<string>("PAYMENT_MODE"), // Instrument_No = row.Field<string>("INSTRUMENT_NO"), // Receipt_Status = row.Field<string>("RECEIPT_STATUS"), // Auth_Status = row.Field<string>("AUTH_STATUS") // }).ToList(); // return eList; // } // catch (Exception) // { // throw; // } // } // // FOR PENDING REVERSED RECEIPTS // public DataSet ReverseReceiptData() // { // try // { // //Get connection // var app = new AppSettings(); // con = app.GetConnection(); // DataSet ds = new DataSet(); // OracleDataAdapter da = new OracleDataAdapter(); // OracleCommand cmd = new OracleCommand(); // OracleParameter param = cmd.CreateParameter(); // cmd.CommandText = "SEL_REMIT_RECEIPT_R_PENDING"; // cmd.CommandType = CommandType.StoredProcedure; // cmd.Connection = (OracleConnection)con; // param = cmd.Parameters.Add("cur", OracleDbType.RefCursor); // param.Direction = ParameterDirection.Output; // da = new OracleDataAdapter(cmd); // da.Fill(ds, "receipt"); // return ds; // } // catch (Exception) // { // throw; // } // } // public IEnumerable<Remit_ReceiptRepo> GetReverseReceiptList() // { // try // { // DataSet dt = ReverseReceiptData(); // var eList = dt.Tables[0].AsEnumerable().Select(row => new Remit_ReceiptRepo // { // ES_Id = row.Field<string>("ES_ID"), // Scheme_Id = row.Field<string>("SCHEME_ID"), // Scheme_Name = row.Field<string>("SCHEME_NAME"), // Employer_Id = row.Field<string>("EMPLOYER_ID"), // Employer_Name = row.Field<string>("Employer_Name"), // Receipt_Id = row.Field<string>("RECEIPT_ID"), // Trans_Amount = row.Field<Decimal>("TRANS_AMOUNT"), // Actual_Receipt_Date = row.Field<DateTime>("ACTUAL_RECEIPT_DATE"), // Narration = row.Field<string>("NARRATION"), // Narration_Syatem = row.Field<string>("NARRATION_SYSTEM"), // Payment_Mode = row.Field<string>("PAYMENT_MODE"), // Instrument_No = row.Field<string>("INSTRUMENT_NO"), // Receipt_Status = row.Field<string>("RECEIPT_STATUS"), // Auth_Status = row.Field<string>("AUTH_STATUS") // }).ToList(); // return eList; // } // catch (Exception) // { // throw; // } // } // public DataSet PurchaseESData() // { // try // { // //Get connection // var app = new AppSettings(); // con = app.GetConnection(); // DataSet ds = new DataSet(); // OracleDataAdapter da = new OracleDataAdapter(); // OracleCommand cmd = new OracleCommand(); // OracleParameter param = cmd.CreateParameter(); // cmd.CommandText = "SEL_REMIT_RECEIPT_EMPLOYER"; // cmd.CommandType = CommandType.StoredProcedure; // cmd.Connection = (OracleConnection)con; // param = cmd.Parameters.Add("cur", OracleDbType.RefCursor); // param.Direction = ParameterDirection.Output; // da = new OracleDataAdapter(cmd); // da.Fill(ds, "purchase"); // return ds; // } // catch (Exception) // { // throw; // } // } // public IEnumerable<Remit_PurchaseRepo> GetPurchaseESList() // { // try // { // DataSet dt = PurchaseESData(); // var eList = dt.Tables[0].AsEnumerable().Select(row => new Remit_PurchaseRepo // { // ES_Id = row.Field<string>("ES_ID"), // Scheme_Id = row.Field<string>("SCHEME_ID"), // Scheme_Name = row.Field<string>("SCHEME_NAME"), // Employer_Id = row.Field<string>("EMPLOYER_ID"), // Employer_Name = row.Field<string>("Employer_Name"), // Cash_Balance = row.Field<decimal>("CASH_BALANCE"), // Today_Date = row.Field<DateTime>("TODAY_DATE") // }).ToList(); // return eList; // } // catch (Exception) // { // throw; // } // } // public bool isYearMonthValid(Remit_PurchaseRepo PurchaseRepo) // { // try // { // //Get connection // var con = new AppSettings(); // var param = new DynamicParameters(); // param.Add("P_FOR_YEAR", For_Year, DbType.Decimal, ParameterDirection.Input); // param.Add("P_FOR_MONTH", For_Month, DbType.Decimal, ParameterDirection.Input); // param.Add("P_ES_ID", ES_Id, DbType.String, ParameterDirection.Input); // param.Add("VDATA", null, DbType.Int32, ParameterDirection.Output); // con.GetConnection().Execute("SEL_REMIT_YEAR_MONTH", param, commandType: CommandType.StoredProcedure); // int paramoption = param.Get<int>("VDATA"); // if (paramoption == 0) // return false; // else // return true; // } // catch (Exception ex) // { // throw ex; // } //GET UNIT TRANSFER PENDING public List <Remit_Unit_TransferRepo> GetPendingUTList(Remit_Unit_TransferRepo Unit_TransferRepo) { try { var app = new AppSettings(); conn = app.GetConnection(); List <Remit_Unit_TransferRepo> bn = new List <Remit_Unit_TransferRepo>(); string query = "Select * from REMIT_UNIT_TRANSFER_LOG WHERE AUTH_STATUS = 'PENDING' "; return(bn = conn.Query <Remit_Unit_TransferRepo>(query).ToList()); } catch (Exception ex) { throw ex; } finally { conn.Dispose(); } }
public bool Approve_Unit_Transfer(Remit_Unit_TransferRepo Unit_TransferRepo) { var app = new AppSettings(); // get the pending purchase record Unit_TransferRepo.GetPendingUTList(Unit_TransferRepo); TransactionOptions tsOp = new TransactionOptions(); tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot; TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp); tsOp.Timeout = TimeSpan.FromMinutes(20); using (OracleConnection conn = new OracleConnection(app.conString())) // { try { // Update remit_unit_transfer table DynamicParameters param = new DynamicParameters(); param.Add(name: "P_TID", value: TID, dbType: DbType.String, direction: ParameterDirection.Input); param.Add(name: "P_REC_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_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_UNIT_TRANSFER", param, commandType: CommandType.StoredProcedure); //Update remit_unit_purchase_trans table DynamicParameters param_conl = new DynamicParameters(); param_conl.Add(name: "P_PURCHASE_LOG_ID", value: Purchase_Log_Id, dbType: DbType.String, direction: ParameterDirection.Input); param_conl.Add(name: "P_PURCHASE_TYPE", value: Purchase_Type, dbType: DbType.String, direction: ParameterDirection.Input); param_conl.Add(name: "P_FROM_ESF_ID", value: From_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param_conl.Add(name: "P_TO_ESF_ID", value: To_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param_conl.Add(name: "P_EMPLOYER_AMT", value: Unit_TransferRepo.Employer_Amt, dbType: DbType.Decimal, direction: ParameterDirection.Input); param_conl.Add(name: "P_EMPLOYEE_AMT", value: Unit_TransferRepo.Employee_Amt, dbType: DbType.Decimal, direction: ParameterDirection.Input); conn.Execute("UPD_REMIT_UNIT_PUR_TRANS_UT", param_conl, commandType: CommandType.StoredProcedure); //Update Employee_Scheme_Fund table DynamicParameters param_cash = new DynamicParameters(); param_cash.Add(name: "P_FROM_ESF_ID", value: From_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param_cash.Add(name: "P_TO_ESF_ID", value: To_ESF_Id, dbType: DbType.String, direction: ParameterDirection.Input); param_cash.Add(name: "P_EMPLOYER_AMT", value: Unit_TransferRepo.Employer_Amt, dbType: DbType.Decimal, direction: ParameterDirection.Input); param_cash.Add(name: "P_EMPLOYEE_AMT", value: Unit_TransferRepo.Employee_Amt, dbType: DbType.Decimal, direction: ParameterDirection.Input); param_cash.Add(name: "P_EMPLOYER_UNITS", value: Unit_TransferRepo.Employer_Units, dbType: DbType.Decimal, direction: ParameterDirection.Input); param_cash.Add(name: "P_EMPLOYEE_UNITS", value: Unit_TransferRepo.Employee_Units, dbType: DbType.Decimal, direction: ParameterDirection.Input); conn.Execute("UPD_REMIT_ESF_TRANSFER", param_cash, commandType: CommandType.StoredProcedure); ts.Complete(); return(true); } catch (Exception ex) { string xx = ex.ToString(); throw; } finally { ts.Dispose(); if (conn.State == ConnectionState.Open) { conn.Close(); } } } }