//ham bao cao doanh thu van chuyen theo khach hang
public void RevenueByCustomer(string CustomerName, string CustomerId, string Start, string End)
{
DateTime start = DateTime.ParseExact(Start + " 00:00", "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture);
DateTime end = DateTime.ParseExact(End + " 23:59", "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture);
FileInfo template = new FileInfo(HostingEnvironment.MapPath("~/Content/template.xlsx"));
ExcelPackage EP = new ExcelPackage(template);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelWorksheet Sheet = EP.Workbook.Worksheets[0];
int id = Int32.Parse(CustomerId);
List <VT_RevenueByCustomer_Result> list = db.VT_RevenueByCustomer(id, start, end).ToList();
int row = 16;
Sheet.Cells[string.Format("D6")].Value = CustomerName;
foreach (var item in list)
{
int sum = 0;
Sheet.Cells[string.Format("A{0}", row)].Value = item.Type;
Sheet.Cells[string.Format("C{0}", row)].Value = item.doc;
Sheet.Cells[string.Format("D{0}", row)].Value = item.TruckNo;
Sheet.Cells[string.Format("E{0}", row)].Value = item.CntrNo;
Sheet.Cells[string.Format("F{0}", row)].Value = item.Name;
Sheet.Cells[string.Format("G{0}", row)].Value = item.CreateDate;
if (item.CntrSize.Contains("20"))
{
Sheet.Cells[string.Format("H{0}", row)].Value = "1";
Sheet.Cells[string.Format("K{0}", row)].Value = item.FeeAmount;
sum += Int32.Parse(item.FeeAmount.ToString());
}
else if (item.CntrSize.Contains("40"))
{
Sheet.Cells[string.Format("I{0}", row)].Value = "1";
Sheet.Cells[string.Format("L{0}", row)].Value = item.FeeAmount;
sum += Int32.Parse(item.FeeAmount.ToString());
}
else if (item.CntrSize.Contains("45"))
{
Sheet.Cells[string.Format("J{0}", row)].Value = "1";
Sheet.Cells[string.Format("M{0}", row)].Value = item.FeeAmount;
sum += Int32.Parse(item.FeeAmount.ToString());
}
if (db.VT_GetLiftingFee(item.TransID, item.CntrSize).FirstOrDefault() != null)
{
if (item.CntrSize.Contains("20"))
{
Sheet.Cells[string.Format("N{0}", row)].Value = db.VT_GetLiftingFee(item.TransID, item.CntrSize);
}
else if (item.CntrSize.Contains("40"))
{
Sheet.Cells[string.Format("O{0}", row)].Value = db.VT_GetLiftingFee(item.TransID, item.CntrSize);
}
else if (item.CntrSize.Contains("45"))
{
Sheet.Cells[string.Format("P{0}", row)].Value = db.VT_GetLiftingFee(item.TransID, item.CntrSize);
}
sum += Int32.Parse(db.VT_GetLiftingFee(item.TransID, item.CntrSize).FirstOrDefault().ToString());
}
Sheet.Cells[string.Format("Q{0}", row)].Value = sum;
Sheet.Cells[string.Format("R{0}", row)].Value = item.Note;
row++;
}
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx");
Response.BinaryWrite(EP.GetAsByteArray());
Response.End();
}