public List <GRVList> GetAllGRVList() { //...Create New Instance of Object... List <GRVList> list = new List <GRVList>(); GRVList ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_GRVList WHERE Removed=0", con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new GRVList(); ins.GRVListID = Convert.ToInt32(drI["GRVListID"]); ins.InvoiceNumber = Convert.ToString(drI["InvoiceNumber"]); ins.StateDate = Convert.ToDateTime(drI["StateDate"]); ins.Number = Convert.ToInt32(drI["Number"]); ins.PayDate = Convert.ToDateTime(drI["PayDate"]); ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]); ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]); ins.InvoiceDate = Convert.ToDateTime(drI["InvoiceDate"]); ins.ExcludingVat = Convert.ToDecimal(drI["ExcludingVat"]); ins.IncludingVat = Convert.ToDecimal(drI["IncludingVat"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.GRVTypeID = Convert.ToInt32(drI["GRVTypeID"]); ins.SupplierID = Convert.ToInt32(drI["SupplierID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return(list); }
public GRVList UpdateFromImport(GRVList 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.GRVListUpdateImport; cmdI.CommandType = System.Data.CommandType.StoredProcedure; //cmdI.Parameters.AddWithValue("@GRVListID", ins.GRVListID); cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); cmdI.Parameters.AddWithValue("@StateDate", ins.StateDate); cmdI.Parameters.AddWithValue("@Number", ins.Number); cmdI.Parameters.AddWithValue("@PayDate", ins.PayDate); cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber); cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); cmdI.Parameters.AddWithValue("@InvoiceDate", ins.InvoiceDate); cmdI.Parameters.AddWithValue("@ExcludingVat", ins.ExcludingVat); cmdI.Parameters.AddWithValue("@IncludingVat", ins.IncludingVat); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@GRVTypeID", ins.GRVTypeID); cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@ExpectedPayDate", ins.ExpectedPayDate); cmdI.ExecuteNonQuery(); cmdI.Connection.Close(); return(ins); }
public List <GRVList> setData(IQueryable <GRVExcel> data, int BatchId) { List <GRVList> lst = new List <GRVList>(); DateTime parse; int count = 0; foreach (GRVExcel row in data) { //if (!CheckGRVDuplicate(row.Number.ToString())) //{ GRVList imp = new GRVList(); imp.BatchId = BatchId; imp.InvoiceNumber = row.InvNo; count++; if (row.Typ == null) { row.Typ = "CLM"; } imp.GRVTypeID = (row.Typ.Equals("GRV")) ? 1 : 2; imp.Number = row.Number; imp.PinkSlipNumber = row.GRVBook; imp.GRVDate = (DateTime.TryParse(row.GRVDate, out parse)) ? Convert.ToDateTime(row.GRVDate) : DateTime.Now; imp.InvoiceDate = (DateTime.TryParse(row.InvDate, out parse)) ? Convert.ToDateTime(row.InvDate) : imp.GRVDate; imp.SupplierID = GetSupplierByName(row.SupplierName); imp.SupplierDetails = row.SupplierName; imp.ExcludingVat = checkDecimal(row.ExclVAT); imp.IncludingVat = checkDecimal(row.InclVAT); imp.StateDate = new DateTime(1900, 1, 1); imp.PayDate = GetPaymentDate(imp.SupplierID, imp.GRVDate); imp.hasError = (imp.SupplierID == 0) ? true : false; //...Add to return list lst.Add(imp); //} } return(lst); }
public GRVList ConvertToOther(GRVListImport ins) { GRVList ret = new GRVList(); ret.CompanyID = ins.CompanyID; ret.CreatedDate = ins.CreatedDate; ret.ExcludingVat = ins.ExcludingVat; ret.GRVDate = ins.GRVDate; ret.GRVTypeID = ins.GRVTypeID; ret.IncludingVat = ins.IncludingVat; ret.InvoiceDate = ins.InvoiceDate; ret.InvoiceNumber = ins.InvoiceNumber; ret.ModifiedBy = ins.ModifiedBy; ret.ModifiedDate = ins.ModifiedDate; ret.Number = ins.Number; ret.PayDate = GetPaymentDate(ins.SupplierID, ins.GRVDate); ret.PinkSlipNumber = ins.PinkSlipNumber; ret.StateDate = ins.StateDate; ret.SupplierID = ins.SupplierID; ret.ExpectedPayDate = GetExpectedPayDate(ins.PinkSlipNumber, ins.SupplierID); return ret; }
public GRVList ConvertToOther(GRVListImport ins) { GRVList ret = new GRVList(); ret.CompanyID = ins.CompanyID; ret.CreatedDate = ins.CreatedDate; ret.ExcludingVat = ins.ExcludingVat; ret.GRVDate = ins.GRVDate; ret.GRVTypeID = ins.GRVTypeID; ret.IncludingVat = ins.IncludingVat; ret.InvoiceDate = ins.InvoiceDate; ret.InvoiceNumber = ins.InvoiceNumber; ret.ModifiedBy = ins.ModifiedBy; ret.ModifiedDate = ins.ModifiedDate; ret.Number = ins.Number; ret.PayDate = GetPaymentDate(ins.SupplierID, ins.GRVDate); ret.PinkSlipNumber = ins.PinkSlipNumber; ret.StateDate = ins.StateDate; ret.SupplierID = ins.SupplierID; ret.ExpectedPayDate = GetExpectedPayDate(ins.PinkSlipNumber, ins.SupplierID); return(ret); }
public List <GRVList> setData(List <List <string> > data, int BatchId) { int count = 1; List <GRVList> lst = new List <GRVList>(); DateTime parse; foreach (List <string> row in data) { GRVList imp = new GRVList(); imp.BatchId = BatchId; imp.InvoiceNumber = row[0]; //...String //imp.Number = checkInt(row[1]); //...Int imp.GRVTypeID = (row[2].Equals("GRV")) ? 1 : 2; //..Int // GRV Unique imp.Number = checkInt(row[3]); //...Int //imp.PinkSlipNumber = row[4]; //...String imp.PinkSlipNumber = row[5]; //...String imp.GRVDate = (DateTime.TryParse(row[6], out parse)) ? Convert.ToDateTime(row[6]) : DateTime.MaxValue; //...Date imp.InvoiceDate = (DateTime.TryParse(row[7], out parse)) ? Convert.ToDateTime(row[7]) : DateTime.MaxValue; //...Date imp.SupplierID = GetSupplierByName(row[8]); imp.SupplierDetails = row[8]; imp.ExcludingVat = checkDecimal(row[9]); imp.IncludingVat = checkDecimal(row[11]); imp.StateDate = DateTime.Now; imp.PayDate = GetPaymentDate(imp.SupplierID, imp.InvoiceDate); imp.hasError = (imp.SupplierID == 0) ? true : false; //...Add to return list lst.Add(imp); } return(lst); }
public JsonResult _UpdateGRVLists(GRVList ins) { //...Update Object GRVList ins2 = GRVRep.Update(ins); //...Repopulate Grid... return Json(new GridModel(GRVRep.GetAllGRVList())); }
public JsonResult _InsertGRVLists(GRVList ins) { //...Insert Object ins.PayDate = GRVRep.GetPaymentDate(ins.SupplierID, ins.GRVDate); GRVList ins2 = GRVRep.Insert(ins); //...Repopulate Grid... return Json(new GridModel(GRVRep.GetAllGRVList())); }
public List<GRVList> setData(List<List<string>> data, int BatchId) { int count = 1; List<GRVList> lst = new List<GRVList>(); DateTime parse; foreach (List<string> row in data) { GRVList imp = new GRVList(); imp.BatchId = BatchId; imp.InvoiceNumber = row[0]; //...String //imp.Number = checkInt(row[1]); //...Int imp.GRVTypeID = (row[2].Equals("GRV")) ? 1 : 2; //..Int // GRV Unique imp.Number = checkInt(row[3]); //...Int //imp.PinkSlipNumber = row[4]; //...String imp.PinkSlipNumber = row[5]; //...String imp.GRVDate = (DateTime.TryParse(row[6], out parse)) ? Convert.ToDateTime(row[6]) : DateTime.MaxValue; //...Date imp.InvoiceDate = (DateTime.TryParse(row[7], out parse)) ? Convert.ToDateTime(row[7]) : DateTime.MaxValue; //...Date imp.SupplierID = GetSupplierByName(row[8]); imp.SupplierDetails = row[8]; imp.ExcludingVat = checkDecimal(row[9]); imp.IncludingVat = checkDecimal(row[11]); imp.StateDate = DateTime.Now; imp.PayDate = GetPaymentDate(imp.SupplierID, imp.InvoiceDate); imp.hasError = (imp.SupplierID == 0) ? true : false; //...Add to return list lst.Add(imp); } return lst; }
public List<GRVList> setData(IQueryable<GRVExcel> data, int BatchId) { List<GRVList> lst = new List<GRVList>(); DateTime parse; int count = 0; foreach (GRVExcel row in data) { //if (!CheckGRVDuplicate(row.Number.ToString())) //{ GRVList imp = new GRVList(); imp.BatchId = BatchId; imp.InvoiceNumber = row.InvNo; count++; if (row.Typ == null) { row.Typ = "CLM"; } imp.GRVTypeID = (row.Typ.Equals("GRV")) ? 1 : 2; imp.Number = row.Number; imp.PinkSlipNumber = row.GRVBook; imp.GRVDate = (DateTime.TryParse(row.GRVDate, out parse)) ? Convert.ToDateTime(row.GRVDate) : DateTime.Now; imp.InvoiceDate = (DateTime.TryParse(row.InvDate, out parse)) ? Convert.ToDateTime(row.InvDate) : imp.GRVDate; imp.SupplierID = GetSupplierByName(row.SupplierName); imp.SupplierDetails = row.SupplierName; imp.ExcludingVat = checkDecimal(row.ExclVAT); imp.IncludingVat = checkDecimal(row.InclVAT); imp.StateDate = new DateTime(1900, 1, 1); imp.PayDate = GetPaymentDate(imp.SupplierID, imp.GRVDate); imp.hasError = (imp.SupplierID == 0) ? true : false; //...Add to return list lst.Add(imp); //} } return lst; }
public GRVList InsertTemp(GRVList 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 = "GRVListIns_" + 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; if (ins.PayDate == DateTime.MinValue) { ins.PayDate = DateTime.Now; } try { //...Insert Record... cmdI.CommandText = StoredProcedures.GRVTempInsert; cmdI.CommandType = System.Data.CommandType.StoredProcedure; //cmdI.Parameters.AddWithValue("@GRVListID", ins.GRVListID); cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); cmdI.Parameters.AddWithValue("@StateDate", ins.StateDate); cmdI.Parameters.AddWithValue("@Number", ins.Number); cmdI.Parameters.AddWithValue("@PayDate", ins.PayDate); cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber); cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); cmdI.Parameters.AddWithValue("@InvoiceDate", ins.InvoiceDate); cmdI.Parameters.AddWithValue("@ExcludingVat", ins.ExcludingVat); cmdI.Parameters.AddWithValue("@IncludingVat", ins.IncludingVat); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@GRVTypeID", ins.GRVTypeID); cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@Removed", ins.Removed); cmdI.Parameters.AddWithValue("@BatchId", ins.BatchId); cmdI.Parameters.AddWithValue("@hasError", ins.hasError); cmdI.Parameters.AddWithValue("@SupplierDetails", ins.SupplierDetails); //...Return new ID ins.GRVListID = (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 List<GRVList> GetGRVListsPerSupplierType(int SupplierTypeID) { //...Create New Instance of Object... List<GRVList> list = new List<GRVList>(); GRVList ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("SELECT * FROM t_GRVList WHERE SupplierTypeID = " + SupplierTypeID, con); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new GRVList(); ins.GRVListID = Convert.ToInt32(drI["GRVListID"]); ins.InvoiceNumber = Convert.ToString(drI["InvoiveNumber"]); ins.StateDate = Convert.ToDateTime(drI["StateDate"]); ins.Number = Convert.ToInt32(drI["Number"]); ins.PayDate = Convert.ToDateTime(drI["PayDate"]); ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]); ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]); ins.InvoiceDate = Convert.ToDateTime(drI["InvoiceDate"]); ins.ExcludingVat = Convert.ToDecimal(drI["ExcludingVat"]); ins.IncludingVat = Convert.ToDecimal(drI["IncludingVat"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.GRVTypeID = Convert.ToInt32(drI["GRVTypeID"]); ins.SupplierID = Convert.ToInt32(drI["SupplierID"]); ins.CompanyID = Convert.ToInt32(drI["CompanyID"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]); ins.Removed = Convert.ToBoolean(drI["Removed"]); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return list; }
public List<GRVList> GetAllGRVList(string InvoiceNumber, string PinkSlipNumber, string Supplier, string From, string To) { //...Create New Instance of Object... List<GRVList> list = new List<GRVList>(); GRVList ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; if (From.Equals("")) From = "1900-01-01"; if (To.Equals("")) To = "2100-01-01"; //...SQL Commands... if (Supplier.Equals("")) { cmdI = new SqlCommand("SELECT G.*, S.Supplier FROM t_GRVList G INNER JOIN t_Supplier S ON G.SupplierID = S.SupplierID " + "WHERE G.InvoiceNumber LIKE '%" + InvoiceNumber + "' AND G.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'" + " AND G.GRVDate >= '" + From + "'" + " AND G.GRVDate <= '" + To + "' AND G.Removed=0", con); } else { cmdI = new SqlCommand("SELECT G.*, S.Supplier FROM t_GRVList G INNER JOIN t_Supplier S ON G.SupplierID = S.SupplierID " + "WHERE G.InvoiceNumber LIKE '%" + InvoiceNumber + "' AND G.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'" + " AND G.SupplierID =" + Supplier + " AND G.GRVDate >= '" + From + "'" + " AND G.GRVDate <= '" + To + "' AND G.Removed=0", con); } cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new GRVList(); ins.GRVListID = Convert.ToInt32(drI["GRVListID"]); ins.InvoiceNumber = Convert.ToString(drI["InvoiceNumber"]); ins.StateDate = Convert.ToDateTime(drI["StateDate"]); ins.Number = Convert.ToInt32(drI["Number"]); ins.PayDate = Convert.ToDateTime(drI["PayDate"]); ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]); ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]); ins.InvoiceDate = Convert.ToDateTime(drI["InvoiceDate"]); ins.ExcludingVat = Convert.ToDecimal(drI["ExcludingVat"]); ins.IncludingVat = Convert.ToDecimal(drI["IncludingVat"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.GRVTypeID = Convert.ToInt32(drI["GRVTypeID"]); ins.SupplierID = Convert.ToInt32(drI["SupplierID"]); 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.SupplierDetails = drI["Supplier"].ToString(); if (drI["ExpectedPayDate"] == DBNull.Value) { ins.ExpectedPayDate = DateTime.MinValue; } else { ins.ExpectedPayDate = Convert.ToDateTime(drI["ExpectedPayDate"]); } list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return list; }
public GRVList UpdateFromImport(GRVList 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.GRVListUpdateImport; cmdI.CommandType = System.Data.CommandType.StoredProcedure; //cmdI.Parameters.AddWithValue("@GRVListID", ins.GRVListID); cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); cmdI.Parameters.AddWithValue("@StateDate", ins.StateDate); cmdI.Parameters.AddWithValue("@Number", ins.Number); cmdI.Parameters.AddWithValue("@PayDate", ins.PayDate); cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber); cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); cmdI.Parameters.AddWithValue("@InvoiceDate", ins.InvoiceDate); cmdI.Parameters.AddWithValue("@ExcludingVat", ins.ExcludingVat); cmdI.Parameters.AddWithValue("@IncludingVat", ins.IncludingVat); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@GRVTypeID", ins.GRVTypeID); cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@ExpectedPayDate", ins.ExpectedPayDate); cmdI.ExecuteNonQuery(); cmdI.Connection.Close(); return ins; }
public GRVList InsertTemp(GRVList 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 = "GRVListIns_" + 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; if (ins.PayDate == DateTime.MinValue) { ins.PayDate = DateTime.Now; } try { //...Insert Record... cmdI.CommandText = StoredProcedures.GRVTempInsert; cmdI.CommandType = System.Data.CommandType.StoredProcedure; //cmdI.Parameters.AddWithValue("@GRVListID", ins.GRVListID); cmdI.Parameters.AddWithValue("@InvoiceNumber", ins.InvoiceNumber); cmdI.Parameters.AddWithValue("@StateDate", ins.StateDate); cmdI.Parameters.AddWithValue("@Number", ins.Number); cmdI.Parameters.AddWithValue("@PayDate", ins.PayDate); cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber); cmdI.Parameters.AddWithValue("@GRVDate", ins.GRVDate); cmdI.Parameters.AddWithValue("@InvoiceDate", ins.InvoiceDate); cmdI.Parameters.AddWithValue("@ExcludingVat", ins.ExcludingVat); cmdI.Parameters.AddWithValue("@IncludingVat", ins.IncludingVat); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@GRVTypeID", ins.GRVTypeID); cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID); cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID); cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate); cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId); cmdI.Parameters.AddWithValue("@Removed", ins.Removed); cmdI.Parameters.AddWithValue("@BatchId", ins.BatchId); cmdI.Parameters.AddWithValue("@hasError", ins.hasError); cmdI.Parameters.AddWithValue("@SupplierDetails", ins.SupplierDetails); //...Return new ID ins.GRVListID = (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 List <GRVList> GetAllGRVList(string InvoiceNumber, string PinkSlipNumber, string Supplier, string From, string To) { //...Create New Instance of Object... List <GRVList> list = new List <GRVList>(); GRVList ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; if (From.Equals("")) { From = "1900-01-01"; } if (To.Equals("")) { To = "2100-01-01"; } //...SQL Commands... if (Supplier.Equals("")) { cmdI = new SqlCommand("SELECT G.*, S.Supplier FROM t_GRVList G INNER JOIN t_Supplier S ON G.SupplierID = S.SupplierID " + "WHERE G.InvoiceNumber LIKE '%" + InvoiceNumber + "' AND G.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'" + " AND G.GRVDate >= '" + From + "'" + " AND G.GRVDate <= '" + To + "' AND G.Removed=0", con); } else { cmdI = new SqlCommand("SELECT G.*, S.Supplier FROM t_GRVList G INNER JOIN t_Supplier S ON G.SupplierID = S.SupplierID " + "WHERE G.InvoiceNumber LIKE '%" + InvoiceNumber + "' AND G.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'" + " AND G.SupplierID =" + Supplier + " AND G.GRVDate >= '" + From + "'" + " AND G.GRVDate <= '" + To + "' AND G.Removed=0", con); } cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new GRVList(); ins.GRVListID = Convert.ToInt32(drI["GRVListID"]); ins.InvoiceNumber = Convert.ToString(drI["InvoiceNumber"]); ins.StateDate = Convert.ToDateTime(drI["StateDate"]); ins.Number = Convert.ToInt32(drI["Number"]); ins.PayDate = Convert.ToDateTime(drI["PayDate"]); ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]); ins.GRVDate = Convert.ToDateTime(drI["GRVDate"]); ins.InvoiceDate = Convert.ToDateTime(drI["InvoiceDate"]); ins.ExcludingVat = Convert.ToDecimal(drI["ExcludingVat"]); ins.IncludingVat = Convert.ToDecimal(drI["IncludingVat"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.GRVTypeID = Convert.ToInt32(drI["GRVTypeID"]); ins.SupplierID = Convert.ToInt32(drI["SupplierID"]); 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.SupplierDetails = drI["Supplier"].ToString(); if (drI["ExpectedPayDate"] == DBNull.Value) { ins.ExpectedPayDate = DateTime.MinValue; } else { ins.ExpectedPayDate = Convert.ToDateTime(drI["ExpectedPayDate"]); } list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return(list); }