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