public List <PinkslipGRVReport> GetPinkslipGRVRange(NumericalRangeQuery query)
{
//...Create New Instance of Object...
List <PinkslipGRVReport> list = new List <PinkslipGRVReport>();
PinkslipGRVReport ins;
if (query.To == 0)
{
query.To = 9999999;
}
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("select o.PinkSlipNumber, s.Supplier, o.OrderDate"
+ " ,o.Amount as OrderAmount"
+ " ,SUM(g.IncludingVat) as GRVTotal"
+ " ,(SELECT TOP 1 t.GRVDate FROM t_GRVList t WHERE t.PinkSlipNumber = o.PinkSlipNumber ORDER BY t.GRVDate DESC) as LastDate"
+ " from t_Order o inner join t_GRVList g on o.PinkSlipNumber = g.PinkSlipNumber"
+ " inner join t_Supplier s on o.SupplierID = s.SupplierID"
+ " where o.PinkSlipNumber >= " + query.From + " and o.PinkSlipNumber <= " + query.To
+ " Group by o.PinkSlipNumber, s.Supplier, o.OrderDate, o.Amount", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new PinkslipGRVReport();
ins.PinkslipNumber = drI["PinkSlipNumber"].ToString();
ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]).ToShortDateString();
ins.GRVDate = Convert.ToDateTime(drI["LastDate"]).ToShortDateString();
ins.OrderTotal = drI["OrderAmount"].ToString();
ins.GRVTotal = drI["GRVTotal"].ToString();
ins.Supplier = drI["Supplier"].ToString();
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return(list);
}
public List<PinkslipGRVReport> GetPinkslipGRVRange(NumericalRangeQuery query)
{
//...Create New Instance of Object...
List<PinkslipGRVReport> list = new List<PinkslipGRVReport>();
PinkslipGRVReport ins;
if (query.To == 0)
{
query.To = 9999999;
}
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("select o.PinkSlipNumber, s.Supplier, o.OrderDate"
+ " ,o.Amount as OrderAmount"
+ " ,SUM(g.IncludingVat) as GRVTotal"
+ " ,(SELECT TOP 1 t.GRVDate FROM t_GRVList t WHERE t.PinkSlipNumber = o.PinkSlipNumber ORDER BY t.GRVDate DESC) as LastDate"
+ " from t_Order o inner join t_GRVList g on o.PinkSlipNumber = g.PinkSlipNumber"
+ " inner join t_Supplier s on o.SupplierID = s.SupplierID"
+ " where o.PinkSlipNumber >= " + query.From + " and o.PinkSlipNumber <= " + query.To
+ " Group by o.PinkSlipNumber, s.Supplier, o.OrderDate, o.Amount", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new PinkslipGRVReport();
ins.PinkslipNumber = drI["PinkSlipNumber"].ToString();
ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]).ToShortDateString();
ins.GRVDate = Convert.ToDateTime(drI["LastDate"]).ToShortDateString();
ins.OrderTotal = drI["OrderAmount"].ToString();
ins.GRVTotal = drI["GRVTotal"].ToString();
ins.Supplier = drI["Supplier"].ToString();
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return list;
}