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