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 <PinkslipOrderReport> GetPinkslipRange(NumericalRangeQuery query)
{
//...Create New Instance of Object...
List <PinkslipOrderReport> list = new List <PinkslipOrderReport>();
PinkslipOrderReport 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 * from t_Order o "
+ " where o.Removed = 0 AND o.PinkSlipNumber >= " + query.From
+ " AND o.PinkSlipNumber <= " + query.To, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new PinkslipOrderReport();
ins.PinkslipNumber = drI["PinkSlipNumber"].ToString();
ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]).ToShortDateString();
ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]).ToShortDateString();
ins.OrderTotal = drI["Amount"].ToString();
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return(list);
}
public List<PinkslipOrderReport> GetPinkslipRange(NumericalRangeQuery query)
{
//...Create New Instance of Object...
List<PinkslipOrderReport> list = new List<PinkslipOrderReport>();
PinkslipOrderReport 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 * from t_Order o "
+ " where o.Removed = 0 AND o.PinkSlipNumber >= " + query.From
+ " AND o.PinkSlipNumber <= " + query.To, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new PinkslipOrderReport();
ins.PinkslipNumber = drI["PinkSlipNumber"].ToString();
ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]).ToShortDateString();
ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]).ToShortDateString();
ins.OrderTotal = drI["Amount"].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;
}
public ActionResult GetPinkslipOrderReport(NumericalRangeQuery ins)
{
List<PinkslipOrderReport> report = reportrepo.GetPinkslipRange(ins);
StringWriter sw = new StringWriter();
sw.WriteLine("\"Pink Slip Number\",\"Order Date\",\"Expeceted Delivery Date\",\"Order Total\"");
string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=Pinkslip_Orders_" + name + ".csv");
Response.ContentType = "text/csv";
foreach (PinkslipOrderReport ex in report)
{
sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\"",
ex.PinkslipNumber
, ex.OrderDate
, ex.ExpectedDeliveryDate
, ex.OrderTotal
));
}
Response.Write(sw.ToString());
Response.End();
return null;
}
