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