//FILTERING FUND MANGERS LIST FOR FEE
public List <GLFeeRepo> GetFeesList2(GLFeeRepo GLFee)
{
try
{
AppSettings app = new AppSettings();
con = app.GetConnection();
List <GLFeeRepo> bn = new List <GLFeeRepo>();
if (string.IsNullOrEmpty(GLFee.Fee_Id) || string.IsNullOrEmpty(GLFee.Scheme_Fund_Id))
{
}
else if (GLFee.Fee_Id == "F0002")
{
string query = "Select * from VW_SCH_FEE_APPLY_FM WHERE FEE_ID = '" + GLFee.Fee_Id + "' and SCHEME_FUND_ID = '" + GLFee.Scheme_Fund_Id + "'";
return(bn = con.Query <GLFeeRepo>(query).ToList());
}
else if (GLFee.Fee_Id != "F0002")
{
string query = "Select * from VW_SCH_FEE_APPLY WHERE FEE_ID = '" + GLFee.Fee_Id + "' and SCHEME_FUND_ID = '" + GLFee.Scheme_Fund_Id + "' ";
return(bn = con.Query <GLFeeRepo>(query).ToList());
}
return(bn);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Dispose();
}
}
//public DataSet GLFeeApproveData()
//{
// 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_GLFEE_PENDING");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Connection = (OracleConnection)con;
// param = cmd.Parameters.Add("p_result", OracleDbType.RefCursor);
// param.Direction = ParameterDirection.Output;
// da = new OracleDataAdapter(cmd);
// da.Fill(ds, "glfeepend");
// return ds;
// }
// catch (Exception)
// {
// throw;
// }
//}
//public IEnumerable<GLFeeRepo> GLApproveList(GLFeeRepo GLFee)
//{
// try
// {
// DataSet dt = GLFeeApproveData();
// var eList = dt.Tables[0].AsEnumerable().Select(row => new GLFeeRepo
// {
// TID = row.Field<decimal>("TID"),
// Scheme_Fund_Id = row.Field<string>("scheme_fund_id"),
// Fee_Id = row.Field<string>("fee_id"),
// Fee_Description = row.Field<string>("fee_description"),
// Trans_Date = row.Field<DateTime>("trans_date"),
// Apply_Amount = row.Field<decimal>("apply_amount"),
// Paid_Amount = row.Field<decimal>("Paid_Amount"),
// Narration = row.Field<string>("NARRATION"),
// Fund_Manager_Id = row.Field<string>("Fund_Manager_Id"),
// Fund_Manager = row.Field<string>("Fund_Manager"),
// GL_Account_No = row.Field<string>("GL_Account_No"),
// GL_Account_Name = row.Field<string>("GL_Account_Name"),
// GL_Balance = row.Field<decimal>("GL_Balance") * -1,
// Fund = row.Field<string>("FUND_NAME"),
// Scheme = row.Field<string>("SCHEME_NAME"),
// }).ToList();
// return eList;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (con.State == ConnectionState.Open)
// {
// con.Close();
// if (con != null) { con = null; }
// }
// }
//}
public void GLApproveList(GLFeeRepo GLFee)
{
try
{
//Get connection
var con = new AppSettings();
var param = new DynamicParameters();
if (string.IsNullOrEmpty(GLFee.Fee_Id) || string.IsNullOrEmpty(GLFee.Scheme_Fund_Id))
{
}
else
{
param.Add("P_FEE_ID", GLFee.Fee_Id, DbType.String, ParameterDirection.Input);
param.Add("P_SCHEME_FUND_ID", GLFee.Scheme_Fund_Id, DbType.String, ParameterDirection.Input);
param.Add("P_FM_ID", GLFee.Fund_Manager_Id, DbType.String, ParameterDirection.Input);
param.Add("V1", "", DbType.String, ParameterDirection.Output);
param.Add("V2", "", DbType.String, ParameterDirection.Output);
con.GetConnection().Execute("SEL_GLFEE_FUNDM_SEARCH", param, commandType: CommandType.StoredProcedure);
GLFee.Fund_Manager = param.Get <string>("V1");
GLFee.Fund_Manager_Id = param.Get <string>("V2");
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool Reverse_Fee(GLFeeRepo GLFee)
{
var app = new AppSettings();
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 log TABLE
DynamicParameters param = new DynamicParameters();
param.Add(name: "P_TID", value: TID, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param.Add(name: "P_REC_STATUS", value: "REVERSED", dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("APP_GLFEE", param, commandType: CommandType.StoredProcedure);
//UPDATE GL_ACCOUNT TABLE AND GL_TRANSACTION TABLE
DynamicParameters param_gl = new DynamicParameters();
param_gl.Add(name: "P_GL_NO", value: GLFee.GL_Account_No, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_AMOUNT", value: GLFee.Paid_Amount, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param_gl.Add(name: "P_NARRATION", value: GLFee.Narration, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_TID", value: GLFee.TID, dbType: DbType.Decimal, 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_TRANS_DATE", value: GlobalValue.Scheme_Today_Date, dbType: DbType.DateTime, direction: ParameterDirection.Input);
param_gl.Add(name: "P_SF_ID", value: GLFee.Scheme_Fund_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_FEE_ID", value: GLFee.Fee_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param_gl.Add(name: "P_FM_ID", value: GLFee.Fund_Manager_Id, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("APP_FEE_GL_TRANS_REV", param_gl, 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 bool Disapprove_GLFee(GLFeeRepo GLFee)
{
var app = new AppSettings();
// get the pending purchase record
GLFee.GetGLFeePendingList(TID);
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 log TABLE
DynamicParameters param = new DynamicParameters();
param.Add(name: "P_TID", value: TID, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param.Add(name: "P_REC_STATUS", value: "DISAPPROVED", dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute("APP_GLFEE", 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();
}
}
}
}
//GET GL DEBIT ACCOUNT BALANCE FROM GL ACCOUNT TABLE
public void Get_Balance(GLFeeRepo GLFee)
{
try
{
//Get connection
var con = new AppSettings();
var param = new DynamicParameters();
param.Add("P_GL_NO", GLFee.GL_Account_No, DbType.String, ParameterDirection.Input);
param.Add("VDATA", null, DbType.Decimal, ParameterDirection.Output);
con.GetConnection().Execute("SEL_GL_BALANCE_FEE", param, commandType: CommandType.StoredProcedure);
GLFee.GL_Balance = param.Get <decimal>("VDATA");
}
catch (Exception ex)
{
throw ex;
}
}
public void GetFeesList(GLFeeRepo GLFee)
{
try
{
//Get connection
var con = new AppSettings();
var param = new DynamicParameters();
if (string.IsNullOrEmpty(GLFee.Fee_Id) || string.IsNullOrEmpty(GLFee.Scheme_Fund_Id))
{
}
else if (GLFee.Fee_Id == "F0002")
{
param.Add("P_FEE_ID", GLFee.Fee_Id, DbType.String, ParameterDirection.Input);
param.Add("P_FM_ID", GLFee.Fund_Manager_Id, DbType.String, ParameterDirection.Input);
param.Add("P_SCHEME_FUND_ID", GLFee.Scheme_Fund_Id, DbType.String, ParameterDirection.Input);
param.Add("VABAL", null, DbType.Decimal, ParameterDirection.Output);
param.Add("VLADATE", null, DbType.DateTime, ParameterDirection.Output);
param.Add("VLPDATE", null, DbType.DateTime, ParameterDirection.Output);
con.GetConnection().Execute("SEL_SCHEME_APPLY_VAL", param, commandType: CommandType.StoredProcedure);
GLFee.Apply_Bal = param.Get <decimal>("VABAL");
GLFee.Last_Apply_Date = param.Get <DateTime>("VLADATE");
GLFee.Last_Paid_Date = param.Get <DateTime>("VLPDATE");
}
else if (GLFee.Fee_Id != "F0002")
{
param.Add("P_FEE_ID", GLFee.Fee_Id, DbType.String, ParameterDirection.Input);
param.Add("P_FM_ID", GLFee.Fund_Manager_Id, DbType.String, ParameterDirection.Input);
param.Add("P_SCHEME_FUND_ID", GLFee.Scheme_Fund_Id, DbType.String, ParameterDirection.Input);
param.Add("VABAL", null, DbType.Decimal, ParameterDirection.Output);
param.Add("VLADATE", null, DbType.DateTime, ParameterDirection.Output);
param.Add("VLPDATE", null, DbType.DateTime, ParameterDirection.Output);
con.GetConnection().Execute("SEL_SCHEME_APPLY", param, commandType: CommandType.StoredProcedure);
GLFee.Apply_Bal = param.Get <decimal>("VABAL");
GLFee.Last_Apply_Date = param.Get <DateTime>("VLADATE");
GLFee.Last_Paid_Date = param.Get <DateTime>("VLPDATE");
}
}
catch (Exception ex)
{
throw ex;
}
}
public void SaveRecord(GLFeeRepo GLFee)
{
try
{
//Get connectoin
var app = new AppSettings();
con = app.GetConnection();
var param = new DynamicParameters();
param.Add(name: "P_TRANS_DATE", value: GLFee.Trans_Date, dbType: DbType.DateTime, direction: ParameterDirection.Input);
param.Add(name: "P_AMOUNT", value: GLFee.Paid_Amount, dbType: DbType.Decimal, direction: ParameterDirection.Input);
param.Add(name: "P_SF_ID", value: GLFee.Scheme_Fund_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_FEE_ID", value: GLFee.Fee_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_APPLY_PAID", value: "PAID", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_GL_NO", value: GLFee.GL_Account_No, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_REC_STATUS", value: "PENDING", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "P_NARRATION", value: GLFee.Narration, 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_FM_ID", value: GLFee.Fund_Manager_Id, dbType: DbType.String, direction: ParameterDirection.Input);
int result = con.Execute(sql: "ADD_FEEPAYMENT", param: param, commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
if (con != null)
{
con = null;
}
}
}
}