public ActionResult ExportReport(string from, string to, string cus_id, string status)
{
var order = new ORDERED();
var orderReport = new OrderReport();
var lstOrderReport = new List <OrderReport>();
var fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var list = order.GetSumExport(from.Trim(), to.Trim(), cus_id.Trim(), status.Trim());
var orderKinds = list.Select(o => o.ORD_USAGE).Distinct().ToList();
foreach (var kind in orderKinds)
{
var salesTeamResult = orderReport.GetRowsBySalesAndKind(list, "Sales Team", kind);
var solutionTeamResult = orderReport.GetRowsBySalesAndKind(list, "Solution Sales", kind);
var solutionImproveTeamResult = orderReport.GetRowsBySalesAndKind(list, "Solution Improvement ", kind);
if (salesTeamResult != null)
{
lstOrderReport.AddRange(salesTeamResult);
}
if (solutionTeamResult != null)
{
lstOrderReport.AddRange(solutionTeamResult);
}
if (solutionImproveTeamResult != null)
{
lstOrderReport.AddRange(solutionImproveTeamResult);
}
var sumRow = new OrderReport();
sumRow.Name = kind;
sumRow.InQuiryTotal = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.InQuiryTotal).Sum();
sumRow.InQuiry200 = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.InQuiry200).Sum();
sumRow.InQuiry300 = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.InQuiry300).Sum();
sumRow.InQuiry400 = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.InQuiry400).Sum();
sumRow.ContractTotal = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.ContractTotal).Sum();
sumRow.Contract200 = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.Contract200).Sum();
sumRow.Contract300 = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.Contract300).Sum();
sumRow.Contract400 = (from o in lstOrderReport where o.Usage != null && o.Usage.Contains(kind) select o.Contract400).Sum();
lstOrderReport.Add(sumRow);
}
var Total = new OrderReport();
Total.Name = "Grand Total";
Total.InQuiryTotal = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.InQuiryTotal).Sum();
Total.InQuiry200 = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.InQuiry200).Sum();
Total.InQuiry300 = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.InQuiry300).Sum();
Total.InQuiry400 = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.InQuiry400).Sum();
Total.ContractTotal = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.ContractTotal).Sum();
Total.Contract200 = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.Contract200).Sum();
Total.Contract300 = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.Contract300).Sum();
Total.Contract400 = (from o in lstOrderReport where o.CUSTOMER_ID == null select o.Contract400).Sum();
var saleTeamTotal = new OrderReport();
saleTeamTotal.Name = "Sales Team Total";
saleTeamTotal.InQuiryTotal = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.InQuiryTotal).Sum();
saleTeamTotal.InQuiry200 = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.InQuiry200).Sum();
saleTeamTotal.InQuiry300 = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.InQuiry300).Sum();
saleTeamTotal.InQuiry400 = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.InQuiry400).Sum();
saleTeamTotal.ContractTotal = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.ContractTotal).Sum();
saleTeamTotal.Contract200 = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.Contract200).Sum();
saleTeamTotal.Contract300 = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.Contract300).Sum();
saleTeamTotal.Contract400 = (from o in lstOrderReport where o.Name.Contains("Sales Team") select o.Contract400).Sum();
var solutionTeamTotal = new OrderReport();
solutionTeamTotal.Name = "Solution Sales Total";
solutionTeamTotal.InQuiryTotal = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.InQuiryTotal).Sum();
solutionTeamTotal.InQuiry200 = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.InQuiry200).Sum();
solutionTeamTotal.InQuiry300 = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.InQuiry300).Sum();
solutionTeamTotal.InQuiry400 = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.InQuiry400).Sum();
solutionTeamTotal.ContractTotal = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.ContractTotal).Sum();
solutionTeamTotal.Contract200 = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.Contract200).Sum();
solutionTeamTotal.Contract300 = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.Contract300).Sum();
solutionTeamTotal.Contract400 = (from o in lstOrderReport where o.Name.Contains("Solution Sales") select o.Contract400).Sum();
//Solution Improvement
var solutionImprovementTeamTotal = new OrderReport();
solutionImprovementTeamTotal.Name = "Solution Improvement Total";
solutionImprovementTeamTotal.InQuiryTotal = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.InQuiryTotal).Sum();
solutionImprovementTeamTotal.InQuiry200 = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.InQuiry200).Sum();
solutionImprovementTeamTotal.InQuiry300 = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.InQuiry300).Sum();
solutionImprovementTeamTotal.InQuiry400 = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.InQuiry400).Sum();
solutionImprovementTeamTotal.ContractTotal = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.ContractTotal).Sum();
solutionImprovementTeamTotal.Contract200 = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.Contract200).Sum();
solutionImprovementTeamTotal.Contract300 = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.Contract300).Sum();
solutionImprovementTeamTotal.Contract400 = (from o in lstOrderReport where o.Name.Contains("Solution Improvement") select o.Contract400).Sum();
lstOrderReport.Add(saleTeamTotal);
lstOrderReport.Add(solutionTeamTotal);
lstOrderReport.Add(solutionImprovementTeamTotal);
lstOrderReport.Add(Total);
var template = Server.MapPath("~/Template/order.xlsx");
// start from cell E:4
var i = 4;
using (ExcelPackage package = new ExcelPackage(new FileInfo(template)))
{
ExcelWorksheet ws = package.Workbook.Worksheets.FirstOrDefault();
ws.Cells["K1"].Value = string.Format("DOMESTIC ORDER RECORD {0} ~ {1}", from, to);
foreach (var item in lstOrderReport)
{
ws.Cells["E" + i].Value = item.Name.Trim();
ws.Cells["F" + i].Value = item.InQuiryTotal;
ws.Cells["G" + i].Value = item.InQuiry300;
ws.Cells["H" + i].Value = item.InQuiry400;
ws.Cells["I" + i].Value = item.InQuiry200;
ws.Cells["J" + i].Value = 0; // No1
ws.Cells["K" + i].Value = item.ContractTotal;
ws.Cells["L" + i].Value = item.Contract300;
ws.Cells["M" + i].Value = item.Contract400;
ws.Cells["N" + i].Value = item.Contract200;
ws.Cells["O" + i].Value = 0; // No1
ws.Cells["P" + i].Value = item.ContractPrice300;
ws.Cells["Q" + i].Value = item.ContractPrice400;
ws.Cells["R" + i].Value = item.ContractPrice200;
ws.Cells["S" + i].Value = 0; // No1
ws.Cells["T" + i].Value = item.BidPrice300;
ws.Cells["U" + i].Value = item.BidPrice400;
ws.Cells["V" + i].Value = item.BidPrice200;
ws.Cells["W" + i].Value = 0; // No1
ws.Cells["Z" + i].Value = item.InQuiryBA300;
ws.Cells["AA" + i].Value = item.InQuiryBA400;
ws.Cells["AB" + i].Value = item.InQuiryBA200;
ws.Cells["AC" + i].Value = item.InQuiry2B2D300;
ws.Cells["AD" + i].Value = item.InQuiry2B2D400;
ws.Cells["AE" + i].Value = item.InQuiry2B2D200;
ws.Cells["AF" + i].Value = 0; // NO 1
ws.Cells["AG" + i].Value = item.InQuiry300;
ws.Cells["AH" + i].Value = item.InQuiry400;
ws.Cells["AI" + i].Value = item.InQuiry200;
ws.Cells["AJ" + i].Value = 0; // No 1
ws.Cells["AK" + i].Value = item.InQuiryTotal;
// kkk
ws.Cells["AO" + i].Value = item.ContractBA300;
ws.Cells["AP" + i].Value = item.ContractBA400;
ws.Cells["AQ" + i].Value = item.ContractBA200;
ws.Cells["AR" + i].Value = item.Contract2B2D300;
ws.Cells["AS" + i].Value = item.Contract2B2D400;
ws.Cells["AT" + i].Value = item.Contract2B2D200;
ws.Cells["AU" + i].Value = 0; // NO 1
ws.Cells["AV" + i].Value = item.Contract300;
ws.Cells["AW" + i].Value = item.Contract400;
ws.Cells["AX" + i].Value = item.Contract200;
ws.Cells["AY" + i].Value = 0; // No 1
ws.Cells["AZ" + i].Value = item.ContractTotal;
i++;
}
var index2 = i + 2;
var index3 = i + 3;
var index4 = i + 4;
ws.Cells["E" + index2].Value = "INQUIRY";
ws.Cells["N" + index2].Value = "CONTRACT";
ws.Cells["G" + index2].Value = ws.Cells["Q" + index2].Value = "300";
ws.Cells["H" + index2].Value = ws.Cells["R" + index2].Value = "400";
ws.Cells["I" + index2].Value = ws.Cells["S" + index2].Value = "200";
ws.Cells["G" + index2].Value = ws.Cells["Q" + index2].Value = "300";
ws.Cells["J" + index2].Value = ws.Cells["T" + index2].Value = "SUB TOTAL";
ws.Cells["L" + index2].Value = ws.Cells["V" + index2].Value = "TOTAL";
ws.Cells["E" + index3].Value = ws.Cells["N" + index3].Value = "SURFACE";
ws.Cells["F" + index3].Value = ws.Cells["P" + index3].Value = "BA";
ws.Cells["F" + index4].Value = ws.Cells["P" + index4].Value = "2B & 2D";
var inquiryBA300 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.InQuiryBA300).Sum();
var inquiryBA400 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.InQuiryBA400).Sum();
var inquiryBA200 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.InQuiryBA200).Sum();
var subTotalInquiryBA = inquiryBA300 + inquiryBA400 + inquiryBA200;
var inquiry2B2D300 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.InQuiry2B2D300).Sum();
var inquiry2B2D400 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.InQuiry2B2D400).Sum();
var inquiry2B2D200 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.InQuiry2B2D200).Sum();
var subTotalInquiry2B2D = inquiry2B2D300 + inquiry2B2D400 + inquiry2B2D200;
var inquiryTotal = subTotalInquiryBA + subTotalInquiry2B2D;
var contractBA300 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.ContractBA300).Sum();
var contractBA400 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.ContractBA400).Sum();
var contractBA200 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.ContractBA200).Sum();
var subTotalContractBA = contractBA300 + contractBA400 + contractBA200;
var contract2B2D300 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.Contract2B2D300).Sum();
var contract2B2D400 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.Contract2B2D400).Sum();
var contract2B2D200 = (from o in lstOrderReport where o.CUSTOMER_ID != null select o.Contract2B2D200).Sum();
var subTotalContrac2B2D = contract2B2D300 + contract2B2D400 + contract2B2D200;
var contractTotal = subTotalContractBA + subTotalContrac2B2D;
ws.Cells["G" + index3].Value = inquiryBA300;
ws.Cells["H" + index3].Value = inquiryBA400;
ws.Cells["I" + index3].Value = inquiryBA200;
ws.Cells["J" + index3].Value = subTotalInquiryBA;
ws.Cells["G" + index4].Value = inquiry2B2D300;
ws.Cells["H" + index4].Value = inquiry2B2D400;
ws.Cells["I" + index4].Value = inquiry2B2D200;
ws.Cells["J" + index4].Value = subTotalInquiry2B2D;
ws.Cells["L" + index3].Value = inquiryTotal;
ws.Cells["Q" + index3].Value = contractBA300;
ws.Cells["R" + index3].Value = contractBA400;
ws.Cells["S" + index3].Value = contractBA200;
ws.Cells["T" + index3].Value = subTotalContractBA;
ws.Cells["V" + index3].Value = contractTotal;
ws.Cells["Q" + index4].Value = contract2B2D300;
ws.Cells["R" + index4].Value = contract2B2D400;
ws.Cells["S" + index4].Value = contract2B2D200;
ws.Cells["T" + index4].Value = subTotalContrac2B2D;
ws.Column(5).Width = 50;
//buffer = package.Stream as MemoryStream;
Byte[] fileBytes = package.GetAsByteArray();
Response.ClearContent();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(fileBytes);
Response.Flush();
Response.End();
}
return(RedirectToAction("Order"));;
}
