public ActionResult GetOrdervsGRVReport(DateTimeFromToQuery ins) { List<OrdervsGRVReport> report = reportrepo.GetOrdervsGRVReport(ins); StringWriter sw = new StringWriter(); sw.WriteLine("\"Day\",\"Date\",\"Order Total\",\"Friday Total\",\"GRV Total\",\"Friday Total\""); string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString(); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=OrdervsGRV_" + name + ".csv"); Response.ContentType = "text/csv"; foreach (OrdervsGRVReport ex in report) { sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\"", ex.Day , ex.Date , ex.OrderTotal , ex.FridayOrderTotal , ex.GRVTotal , ex.FridayGRVTotal)); } //Response.ClearContent(); //Response.AddHeader("content-disposition", "attachment;filename=MemberDetailReport_" + GroupName + "_" + name + ".zip"); //Response.ContentType = "application/zip"; Response.Write(sw.ToString()); Response.End(); return null; }
public List <SparReconReport> GetSparReconReport(DateTimeFromToQuery query) { SparReconRepository reconrepo = new SparReconRepository(); //...Create New Instance of Object... List <SparReconReport> list = new List <SparReconReport>(); SparReconReport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = new SqlCommand(); cmdI.CommandTimeout = 540; cmdI.Connection = con; cmdI.CommandText = "f_Admin_Report_SparRecon"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@SelectedDate", query.From); cmdI.Parameters.AddWithValue("@SupplierType", query.IntergerSelect); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new SparReconReport(); ins.StateDate = Convert.ToDateTime(drI["StateDate"]).ToShortDateString(); ins.GRVExVAT = drI["GRVExVAT"].ToString(); ins.GRVInVAT = drI["GRVInVAT"].ToString(); ins.GRVInvoiceNumber = drI["GRVInvoiceNumber"].ToString(); ins.GRVPayDate = Convert.ToDateTime(drI["GRVPayDate"]).ToShortDateString(); ins.GRVPinkSlipNumber = drI["GRVPinkSlipNumber"].ToString(); ins.GRVType = drI["GRVType"].ToString(); ins.ReconAmount = drI["ReconAmount"].ToString(); ins.ReconDate = Convert.ToDateTime(drI["ReconDate"]).ToShortDateString(); ins.ReconInvoiceNumber = drI["ReconInvoiceNumber"].ToString(); ins.ReconType = drI["ReconType"].ToString(); ins.Supplier = drI["Supplier"].ToString(); ins.Status = reconrepo.GetReconStatus(Convert.ToDecimal(ins.ReconAmount), Convert.ToInt32(ins.ReconType), ins.ReconInvoiceNumber , Convert.ToDecimal(ins.GRVInVAT), Convert.ToInt32(ins.GRVType), ins.GRVInvoiceNumber); list.Add(ins); } } //...Close Connections... cmdI.Connection.Close(); con.Dispose(); //...Return... return(list); }
public ActionResult GetCashOfficeReport(DateTimeFromToQuery ins) { List<CashOfficeReport> report = reportrepo.GetCashOfficeReport(ins); StringWriter sw = new StringWriter(); sw.WriteLine("\"Money Unit\",\"Sealed\",\"Opened\",\"Notes\",\"Cashiers\",\"Total\",\"Drops\",\"Transits\"," + "\"Day End Balance\""); string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString(); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=Cashoffice" + "_" + name + ".csv"); Response.ContentType = "text/csv"; decimal notestotal = 0; decimal cashiertotal = 0; decimal droptotal = 0; decimal trasittotal = 0; foreach (CashOfficeReport ex in report) { notestotal += ex.Sealed + ex.Opened + ex.TotalInOffice; cashiertotal += ex.CashierTotal; droptotal += ex.Drop; trasittotal += 0; sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\"", ex.MoneyUnit , ex.Sealed , ex.Opened , (ex.Sealed + ex.Opened + ex.TotalInOffice) , ex.CashierTotal , (ex.Sealed + ex.Opened + ex.TotalInOffice + ex.CashierTotal) , ex.Drop , 0 , (ex.Sealed + ex.Opened + ex.TotalInOffice + ex.CashierTotal - ex.Drop - 0) )); } decimal pickuptotal = reportrepo.GetPickUpTotal(ins.From); sw.WriteLine(", , ,{0},{1},{2},{3},{4},{5}" , notestotal, cashiertotal, (notestotal + cashiertotal), droptotal, trasittotal, (notestotal + cashiertotal - droptotal - trasittotal)); sw.WriteLine("\" \""); sw.WriteLine(", , , , , , ,\"Total Pickups\",\"{0}\"", pickuptotal); sw.WriteLine(", , , , , , ,\"Day end Total\",\"{0}\"", (notestotal + cashiertotal - droptotal - trasittotal + pickuptotal)); Response.Write(sw.ToString()); Response.End(); return null; }
public List <CashOfficeReport> GetCashOfficeReport(DateTimeFromToQuery query) { //...Create New Instance of Object... List <CashOfficeReport> list = new List <CashOfficeReport>(); CashOfficeReport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = new SqlCommand(); cmdI.CommandTimeout = 540; cmdI.Connection = con; cmdI.CommandText = "f_Admin_Report_CashOffice"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@SelectedDate", query.From); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new CashOfficeReport(); ins.MoneyUnit = drI["MoneyUnit"].ToString(); ins.Sealed = Convert.ToDecimal(drI["Sealed"]); ins.Opened = Convert.ToDecimal(drI["Opened"]); ins.TotalInOffice = Convert.ToDecimal(drI["Notes"]); ins.CashierTotal = Convert.ToDecimal(drI["Cashiers"]); ins.Drop = Convert.ToDecimal(drI["Drops"]); list.Add(ins); } } //...Close Connections... cmdI.Connection.Close(); con.Dispose(); //...Return... return(list); }
public List <PinkslipOrderReport> GetOrdersPerExpectedDeliveryDate(DateTimeFromToQuery query) { //...Create New Instance of Object... List <PinkslipOrderReport> list = new List <PinkslipOrderReport>(); PinkslipOrderReport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("select * from t_Order o " + " where DATEPART(DAY,o.ExpectedDeliveryDate) = " + query.From.Day + " AND DATEPART(MONTH,o.ExpectedDeliveryDate) = " + query.From.Month + " AND DATEPART(YEAR, o.ExpectedDeliveryDate) = " + query.From.Year, 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.OrderTotal = drI["Amount"].ToString(); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return(list); }
public List<OrdervsGRVReport> GetOrdervsGRVReport(DateTimeFromToQuery query) { string FromDate = query.From.ToString(); string ToDate = query.To.ToString(); if(query.To.Year.Equals(1)) { ToDate = "2050-01-01"; //...Max date of Report (Can be changed anytime) } List<OrdervsGRVReport> lst = new List<OrdervsGRVReport>(); OrdervsGRVReport ins; DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = new SqlCommand(); cmdI.CommandTimeout = 540; cmdI.Connection = con; cmdI.CommandText = "f_Admin_Report_OrdersvsGRVPerDay"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@FromDate", FromDate); cmdI.Parameters.AddWithValue("@ToDate", ToDate); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new OrdervsGRVReport(); ins.Date = Convert.ToDateTime(drI["Date"]).ToShortDateString(); ins.GRVTotal = drI["GRVAmount"].ToString(); ins.OrderTotal = drI["OrderAmount"].ToString(); lst.Add(ins); } } DateTime parser; decimal ortotal = 0; decimal grvtotal = 0; foreach (OrdervsGRVReport item in lst) { DateTime.TryParse(item.Date, out parser); item.Day = parser.DayOfWeek.ToString(); ortotal += Convert.ToDecimal(item.OrderTotal); grvtotal += Convert.ToDecimal(item.GRVTotal); if (parser.DayOfWeek == DayOfWeek.Friday) { item.FridayOrderTotal = ortotal.ToString(); item.FridayGRVTotal = grvtotal.ToString(); ortotal = 0; grvtotal = 0; } else { item.FridayGRVTotal = ""; item.FridayOrderTotal = ""; } } cmdI.Connection.Close(); con.Dispose(); return lst; }
public List<PinkslipOrderReport> GetOrdersPerExpectedDeliveryDate(DateTimeFromToQuery query) { //...Create New Instance of Object... List<PinkslipOrderReport> list = new List<PinkslipOrderReport>(); PinkslipOrderReport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI; //...SQL Commands... cmdI = new SqlCommand("select * from t_Order o " + " where DATEPART(DAY,o.ExpectedDeliveryDate) = " + query.From.Day + " AND DATEPART(MONTH,o.ExpectedDeliveryDate) = " + query.From.Month + " AND DATEPART(YEAR, o.ExpectedDeliveryDate) = " + query.From.Year, 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.OrderTotal = drI["Amount"].ToString(); list.Add(ins); } } //...Close Connections... drI.Close(); con.Close(); //...Return... return list; }
public List<CashOfficeReport> GetCashOfficeReport(DateTimeFromToQuery query) { //...Create New Instance of Object... List<CashOfficeReport> list = new List<CashOfficeReport>(); CashOfficeReport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = new SqlCommand(); cmdI.CommandTimeout = 540; cmdI.Connection = con; cmdI.CommandText = "f_Admin_Report_CashOffice"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@SelectedDate", query.From); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new CashOfficeReport(); ins.MoneyUnit = drI["MoneyUnit"].ToString(); ins.Sealed = Convert.ToDecimal(drI["Sealed"]); ins.Opened = Convert.ToDecimal(drI["Opened"]); ins.TotalInOffice = Convert.ToDecimal(drI["Notes"]); ins.CashierTotal = Convert.ToDecimal(drI["Cashiers"]); ins.Drop = Convert.ToDecimal(drI["Drops"]); list.Add(ins); } } //...Close Connections... cmdI.Connection.Close(); con.Dispose(); //...Return... return list; }
public List<SparReconReport> GetSparReconReport(DateTimeFromToQuery query) { SparReconRepository reconrepo = new SparReconRepository(); //...Create New Instance of Object... List<SparReconReport> list = new List<SparReconReport>(); SparReconReport ins; //...Database Connection... DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = new SqlCommand(); cmdI.CommandTimeout = 540; cmdI.Connection = con; cmdI.CommandText = "f_Admin_Report_SparRecon"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@SelectedDate", query.From); cmdI.Parameters.AddWithValue("@SupplierType", query.IntergerSelect); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new SparReconReport(); ins.StateDate = Convert.ToDateTime(drI["StateDate"]).ToShortDateString(); ins.GRVExVAT = drI["GRVExVAT"].ToString(); ins.GRVInVAT = drI["GRVInVAT"].ToString(); ins.GRVInvoiceNumber = drI["GRVInvoiceNumber"].ToString(); ins.GRVPayDate = Convert.ToDateTime(drI["GRVPayDate"]).ToShortDateString(); ins.GRVPinkSlipNumber = drI["GRVPinkSlipNumber"].ToString(); ins.GRVType = drI["GRVType"].ToString(); ins.ReconAmount = drI["ReconAmount"].ToString(); ins.ReconDate = Convert.ToDateTime(drI["ReconDate"]).ToShortDateString(); ins.ReconInvoiceNumber = drI["ReconInvoiceNumber"].ToString(); ins.ReconType = drI["ReconType"].ToString(); ins.Supplier = drI["Supplier"].ToString(); ins.Status = reconrepo.GetReconStatus(Convert.ToDecimal(ins.ReconAmount), Convert.ToInt32(ins.ReconType), ins.ReconInvoiceNumber , Convert.ToDecimal(ins.GRVInVAT), Convert.ToInt32(ins.GRVType), ins.GRVInvoiceNumber); list.Add(ins); } } //...Close Connections... cmdI.Connection.Close(); con.Dispose(); //...Return... return list; }
public List <OrdervsGRVReport> GetOrdervsGRVReport(DateTimeFromToQuery query) { string FromDate = query.From.ToString(); string ToDate = query.To.ToString(); if (query.To.Year.Equals(1)) { ToDate = "2050-01-01"; //...Max date of Report (Can be changed anytime) } List <OrdervsGRVReport> lst = new List <OrdervsGRVReport>(); OrdervsGRVReport ins; DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = new SqlCommand(); cmdI.CommandTimeout = 540; cmdI.Connection = con; cmdI.CommandText = "f_Admin_Report_OrdersvsGRVPerDay"; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@FromDate", FromDate); cmdI.Parameters.AddWithValue("@ToDate", ToDate); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); //...Retrieve Data... if (drI.HasRows) { while (drI.Read()) { ins = new OrdervsGRVReport(); ins.Date = Convert.ToDateTime(drI["Date"]).ToShortDateString(); ins.GRVTotal = drI["GRVAmount"].ToString(); ins.OrderTotal = drI["OrderAmount"].ToString(); lst.Add(ins); } } DateTime parser; decimal ortotal = 0; decimal grvtotal = 0; foreach (OrdervsGRVReport item in lst) { DateTime.TryParse(item.Date, out parser); item.Day = parser.DayOfWeek.ToString(); ortotal += Convert.ToDecimal(item.OrderTotal); grvtotal += Convert.ToDecimal(item.GRVTotal); if (parser.DayOfWeek == DayOfWeek.Friday) { item.FridayOrderTotal = ortotal.ToString(); item.FridayGRVTotal = grvtotal.ToString(); ortotal = 0; grvtotal = 0; } else { item.FridayGRVTotal = ""; item.FridayOrderTotal = ""; } } cmdI.Connection.Close(); con.Dispose(); return(lst); }
public ActionResult GetSparReconReport(DateTimeFromToQuery ins) { List<SparReconReport> report = reportrepo.GetSparReconReport(ins); StringWriter sw = new StringWriter(); sw.WriteLine("\"Status\",\"GRV Invoice Number\",\"Sate Date\",\"GRV Pay Date\",\"Excluding VAT\",\"Including VAT\",\"Pink Slip Number\",\"GRV Type\" " +" ,\"Supplier\",\"Recon Invoice Number\",\"Recon GRV Date\", Recon Amount, Recon GRV Type"); string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString(); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=SparInvoiceRecon" + "_" + name + ".csv"); Response.ContentType = "text/csv"; sw.WriteLine("\"GRV's not on Spar Invoice\""); foreach (SparReconReport ex in report) { if (ex.ReconInvoiceNumber.Equals("No GRV")) { sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\"", "No Recon" , ex.GRVInvoiceNumber , ex.StateDate , ex.GRVPayDate , ex.GRVExVAT , ex.GRVInVAT , ex.GRVPinkSlipNumber , (ex.GRVType.Equals("1")) ? "GRV" : "CLM" , ex.Supplier , "" , "" , "" , "" )); } } sw.WriteLine("\" \""); sw.WriteLine("\"Spar Invoices not in GRV List\""); foreach (SparReconReport ex in report) { if (ex.GRVInvoiceNumber.Equals("No Recon")) { sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\"", ex.Status , "" , "" , "" , "" , "" , "" , "" , ex.Supplier , ex.ReconInvoiceNumber , ex.ReconDate , ex.ReconAmount , (ex.ReconType.Equals("1")) ? "GRV" : "CLM" )); } } sw.WriteLine("\" \""); sw.WriteLine("\"Matched on GRV List and Spar Invoice\""); foreach (SparReconReport ex in report) { if ((!ex.GRVInvoiceNumber.Equals("No Recon")) && (!ex.ReconInvoiceNumber.Equals("No GRV"))) { sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\"", ex.Status , ex.GRVInvoiceNumber , ex.StateDate , ex.GRVPayDate , ex.GRVExVAT , ex.GRVInVAT , ex.GRVPinkSlipNumber , (ex.GRVType.Equals("1")) ? "GRV" : "CLM" , ex.Supplier , ex.ReconInvoiceNumber , ex.ReconDate , ex.ReconAmount , (ex.ReconType.Equals("1")) ? "GRV" : "CLM" )); } } Response.Write(sw.ToString()); Response.End(); return null; }
public ActionResult GetOrdersExpectedDateReport(DateTimeFromToQuery ins) { List<PinkslipOrderReport> report = reportrepo.GetOrdersPerExpectedDeliveryDate(ins); StringWriter sw = new StringWriter(); sw.WriteLine("\"Pink Slip Number\",\"Order 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=Orders_for_" + ins.From.ToShortDateString() + "_" + name + ".csv"); Response.ContentType = "text/csv"; foreach (PinkslipOrderReport ex in report) { sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\"", ex.PinkslipNumber , ex.OrderDate , ex.OrderTotal )); } Response.Write(sw.ToString()); Response.End(); return null; }