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