public List <SparInvoiceRecon> GetRecons()
{
//...Create New Instance of Object...
List <SparInvoiceRecon> list = new List <SparInvoiceRecon>();
SparInvoiceRecon ins;
int GRVType = 0;
string GRVInvoice = "";
decimal GRVAmount = 0;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("select sr.*, COALESCE(g.InvoiceDate,0) as StateDate, COALESCE(g.IncludingVat,0) as GRVAmount, COALESCE(g.GRVTypeID,0) as GRVGRVType, COALESCE(g.InvoiceNumber,'No') as GRVInvoice "
+ " from t_SparRecon sr left join t_GRVList g on sr.InvoiceNumber = g.InvoiceNumber and sr.GRVTypeId = g.GRVTypeID "
+ " where sr.Removed = 0 order by g.StateDate DESC, sr.SparReconId DESC", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new SparInvoiceRecon();
ins.SparReconId = Convert.ToInt32(drI["SparReconId"]);
ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]);
ins.InvoiceNumber = drI["InvoiceNumber"].ToString();
ins.GRVTypeId = Convert.ToInt32(drI["GRVTypeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
ins.Removed = Convert.ToBoolean(drI["Removed"]);
ins.SupplierId = Convert.ToInt32(drI["SupplierId"]);
ins.Amount = Convert.ToDecimal(drI["Amount"]);
ins.PaidAmount = Convert.ToDecimal(drI["PaidAmount"]);
GRVInvoice = drI["GRVInvoice"].ToString();
GRVType = Convert.ToInt32(drI["GRVGRVType"]);
GRVAmount = Convert.ToDecimal(drI["GRVAmount"]);
ins.Status = GetReconStatus(ins, GRVAmount, GRVType, GRVInvoice);
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return(list);
}
public JsonResult _Insert(SparInvoiceRecon ins)
{
//...Fix InvoiceNumber
InvoiceNumberGRVType data = ReconRep.FilterInvoiceNumber(ins.InvoiceNumber);
ins.InvoiceNumber = data.InvoiceNumber;
ins.GRVTypeId = data.GRVTypeId;
GRVRep.UpdateStateDate(ins.GRVDate, ins.InvoiceNumber, ins.SupplierId, ins.GRVTypeId);
SparInvoiceRecon ins2 = ReconRep.Insert(ins);
//...Repopulate Grid...
return Json(new GridModel(ReconRep.GetRecons()));
}
public string GetReconStatus(SparInvoiceRecon ins, decimal GRVAmount, int GRVType, string InvoiceNumber)
{
string ret = "Matched";
if (ins.GRVTypeId == 3)
{
ret = "GRV Type not specified in Invoice Recon";
}
else
{
if (!ins.InvoiceNumber.Equals(InvoiceNumber))
{
ret = "Not matched to GRV";
}
else
{
if (ins.GRVTypeId != GRVType)
{
ret = "GRV Types do not match";
}
else
{
if (ins.Amount != GRVAmount)
{
decimal dif = ins.Amount - GRVAmount;
if (dif < 0)
{
if (GRVAmount < 0)
{
ret = "Spar Recon Amount greater than GRV Amount (R" + dif.ToString() + ")";
}
else
{
ret = "GRV Amount greater than Spar Recon Amount (R" + (dif * -1).ToString() + ")";
}
}
else
{
if (GRVAmount < 0)
{
ret = "GRV Amount greater than Spar Recon Amount (R" + (dif * -1).ToString() + ")";
}
else
{
ret = "Spar Recon Amount greater than GRV Amount (R" + dif.ToString() + ")";
}
}
}
}
}
}
return(ret);
}
public ActionResult _InsertPaymentFromMulti(SparInvoiceRecon ins)
{
//...Fix InvoiceNumber
InvoiceNumberGRVType data = ReconRep.FilterInvoiceNumber(ins.InvoiceNumber);
ins.InvoiceNumber = data.InvoiceNumber;
ins.GRVTypeId = data.GRVTypeId;
GRVRep.UpdateStateDate(ins.GRVDate, ins.InvoiceNumber, ins.SupplierId, ins.GRVTypeId);
SparInvoiceRecon ins2 = ReconRep.Insert(ins);
string Status = ReconRep.GetReconStatus(ins2.SparReconId);
if (Status != "null")
{
return Content(Status.ToString()+" "+ins2.InvoiceNumber.ToString(), "text/html");
}
else
{
return Content("0", "text/html");
}
}
public SparInvoiceRecon GetRecon(int SparReconId)
{
//...Create New Instance of Object...
SparInvoiceRecon ins = new SparInvoiceRecon();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM t_SparRecon s WHERE s.Removed=0 AND s.SparReconId=" + SparReconId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.SparReconId = Convert.ToInt32(drI["SparReconId"]);
ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]);
ins.InvoiceNumber = drI["InvoiceNumber"].ToString();
ins.GRVTypeId = Convert.ToInt32(drI["GRVTypeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
ins.Removed = Convert.ToBoolean(drI["Removed"]);
ins.SupplierId = Convert.ToInt32(drI["SupplierId"]);
ins.Amount = Convert.ToDecimal(drI["Amount"]);
ins.PaidAmount = Convert.ToDecimal(drI["PaidAmount"]);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return(ins);
}
public SparInvoiceRecon Update(SparInvoiceRecon ins)
{
//...Get User and Date Data...
string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
//...Update Record...
cmdI.Parameters.Clear();
cmdI.CommandText = StoredProcedures.SparReconUpdate;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@SparReconId", ins.SparReconId); // int
cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); // datetime
cmdI.Parameters.AddWithValue("@SupplierId", ins.SupplierId); // int
cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); // varchar(100)
cmdI.Parameters.AddWithValue("@Amount", ins.Amount); // decimal(18,2)
cmdI.Parameters.AddWithValue("@PaidAmount", ins.PaidAmount);
cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); // varchar(100)
cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); // datetime
cmdI.Parameters.AddWithValue("@CompanyId", 1); // int
cmdI.Parameters.AddWithValue("@GRVTypeId", ins.GRVTypeId); // int
cmdI.ExecuteNonQuery();
cmdI.Connection.Close();
return(ins);
}
public SparInvoiceRecon Insert(SparInvoiceRecon ins)
{
//...Get User and Date Data...
string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
string strTrx = "SparReconIns_" + EmployeeId;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
//...Command Interface...
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx;
trx = con.BeginTransaction(strTrx);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
//...Insert Record...
cmdI.CommandText = StoredProcedures.SparReconInsert;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); // datetime
cmdI.Parameters.AddWithValue("@SupplierId", ins.SupplierId); // int
cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); // varchar(100)
cmdI.Parameters.AddWithValue("@Amount", ins.Amount); // decimal(18,2)
cmdI.Parameters.AddWithValue("@PaidAmount", ins.PaidAmount); // decimal(18,2)
cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); // varchar(100)
cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); // datetime
cmdI.Parameters.AddWithValue("@CompanyId", 1); // int
cmdI.Parameters.AddWithValue("@GRVTypeId", ins.GRVTypeId); // int
//...Return new ID
ins.SparReconId = (int)cmdI.ExecuteScalar();
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null)
{
trx.Rollback();
}
}
finally
{
//Check for close and respond accordingly
if (con.State != ConnectionState.Closed)
{
con.Close();
}
//Clean up
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
return(ins);
}
public JsonResult _Update(SparInvoiceRecon ins)
{
//...Fix InvoiceNumber
if((ins.InvoiceNumber.Contains(" - GRV")) || (ins.InvoiceNumber.Contains(" - CLM")))
{
InvoiceNumberGRVType data = ReconRep.FilterInvoiceNumber(ins.InvoiceNumber);
ins.InvoiceNumber = data.InvoiceNumber;
ins.GRVTypeId = data.GRVTypeId;
}
GRVRep.UpdateStateDate(ins.GRVDate, ins.InvoiceNumber,ins.SupplierId, ins.GRVTypeId);
SparInvoiceRecon ins2 = ReconRep.Update(ins);
//...Repopulate Grid...
return Json(new GridModel(ReconRep.GetRecons()));
}
public SparInvoiceRecon GetRecon(int SparReconId)
{
//...Create New Instance of Object...
SparInvoiceRecon ins = new SparInvoiceRecon();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM t_SparRecon s WHERE s.Removed=0 AND s.SparReconId="+SparReconId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.SparReconId = Convert.ToInt32(drI["SparReconId"]);
ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]);
ins.InvoiceNumber = drI["InvoiceNumber"].ToString();
ins.GRVTypeId = Convert.ToInt32(drI["GRVTypeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
ins.Removed = Convert.ToBoolean(drI["Removed"]);
ins.SupplierId = Convert.ToInt32(drI["SupplierId"]);
ins.Amount = Convert.ToDecimal(drI["Amount"]);
ins.PaidAmount = Convert.ToDecimal(drI["PaidAmount"]);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return ins;
}
public SparInvoiceRecon Update(SparInvoiceRecon ins)
{
//...Get User and Date Data...
string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
//...Update Record...
cmdI.Parameters.Clear();
cmdI.CommandText = StoredProcedures.SparReconUpdate;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@SparReconId", ins.SparReconId); // int
cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); // datetime
cmdI.Parameters.AddWithValue("@SupplierId", ins.SupplierId); // int
cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); // varchar(100)
cmdI.Parameters.AddWithValue("@Amount", ins.Amount); // decimal(18,2)
cmdI.Parameters.AddWithValue("@PaidAmount", ins.PaidAmount);
cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); // varchar(100)
cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); // datetime
cmdI.Parameters.AddWithValue("@CompanyId", 1); // int
cmdI.Parameters.AddWithValue("@GRVTypeId", ins.GRVTypeId); // int
cmdI.ExecuteNonQuery();
cmdI.Connection.Close();
return ins;
}
public SparInvoiceRecon Insert(SparInvoiceRecon ins)
{
//...Get User and Date Data...
string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
string strTrx = "SparReconIns_" + EmployeeId;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
//...Command Interface...
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx;
trx = con.BeginTransaction(strTrx);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
//...Insert Record...
cmdI.CommandText = StoredProcedures.SparReconInsert;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); // datetime
cmdI.Parameters.AddWithValue("@SupplierId", ins.SupplierId); // int
cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); // varchar(100)
cmdI.Parameters.AddWithValue("@Amount", ins.Amount); // decimal(18,2)
cmdI.Parameters.AddWithValue("@PaidAmount", ins.PaidAmount); // decimal(18,2)
cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); // varchar(100)
cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); // datetime
cmdI.Parameters.AddWithValue("@CompanyId", 1); // int
cmdI.Parameters.AddWithValue("@GRVTypeId", ins.GRVTypeId); // int
//...Return new ID
ins.SparReconId = (int)cmdI.ExecuteScalar();
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null) trx.Rollback();
}
finally
{
//Check for close and respond accordingly
if (con.State != ConnectionState.Closed)
{
con.Close();
}
//Clean up
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
return ins;
}
public string GetReconStatus(SparInvoiceRecon ins, decimal GRVAmount, int GRVType, string InvoiceNumber)
{
string ret = "Matched";
if (ins.GRVTypeId == 3)
{
ret = "GRV Type not specified in Invoice Recon";
}
else
{
if (!ins.InvoiceNumber.Equals(InvoiceNumber))
{
ret = "Not matched to GRV";
}
else
{
if (ins.GRVTypeId != GRVType)
{
ret = "GRV Types do not match";
}
else
{
if (ins.Amount != GRVAmount)
{
decimal dif = ins.Amount - GRVAmount;
if (dif < 0)
{
if (GRVAmount < 0)
{
ret = "Spar Recon Amount greater than GRV Amount (R" + dif.ToString() + ")";
}
else
{
ret = "GRV Amount greater than Spar Recon Amount (R" + (dif * -1).ToString() + ")";
}
}
else
{
if (GRVAmount < 0)
{
ret = "GRV Amount greater than Spar Recon Amount (R" + (dif * -1).ToString() + ")";
}
else
{
ret = "Spar Recon Amount greater than GRV Amount (R" + dif.ToString() + ")";
}
}
}
}
}
}
return ret;
}
public string GetReconStatus(int ReconId)
{
//...Create New Instance of Object...
SparInvoiceRecon ins = new SparInvoiceRecon();
int GRVType = 0;
string GRVInvoice = "";
decimal GRVAmount = 0;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("select sr.*, COALESCE(g.InvoiceDate,0) as StateDate, COALESCE(g.IncludingVat,0) as GRVAmount, COALESCE(g.GRVTypeID,0) as GRVGRVType, COALESCE(g.InvoiceNumber,'No') as GRVInvoice "
+ " from t_SparRecon sr left join t_GRVList g on sr.InvoiceNumber = g.InvoiceNumber and sr.GRVTypeId = g.GRVTypeID "
+ " where sr.Removed = 0 and sr.SparReconId = " + ReconId + " order by g.StateDate DESC, sr.SparReconId DESC", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.SparReconId = Convert.ToInt32(drI["SparReconId"]);
ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]);
ins.InvoiceNumber = drI["InvoiceNumber"].ToString();
ins.GRVTypeId = Convert.ToInt32(drI["GRVTypeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
ins.Removed = Convert.ToBoolean(drI["Removed"]);
ins.SupplierId = Convert.ToInt32(drI["SupplierId"]);
ins.Amount = Convert.ToDecimal(drI["Amount"]);
ins.PaidAmount = Convert.ToDecimal(drI["PaidAmount"]);
GRVInvoice = drI["GRVInvoice"].ToString();
GRVType = Convert.ToInt32(drI["GRVGRVType"]);
GRVAmount = Convert.ToDecimal(drI["GRVAmount"]);
ins.Status = GetReconStatus(ins, GRVAmount, GRVType, GRVInvoice);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return ins.Status;
}
