public string GetDetails(Detail search) { string sql = ""; if (search.type == null || search.type == "month") { sql = @"select (case when a.supply_id is null then b.supplyid else a.supply_id end) 'SupplyId', (case when a.supply_name is null then b.supply_name else a.supply_name end) 'SupplyName', (case when a.department_name is null then b.department_name else a.department_name end) 'DepartmentName', (case when b.quantity_provide is null then 0 else b.quantity_provide end) 'SupplyProvide', (case when b.quantity is null then 0 else b.quantity end) 'SupplyQuantity', (case when a.unit is null then b.unit else a.unit end) 'SupplyUnit', sum(case when a.used is null then 0 else a.used end) 'SupplyUsed', sum(case when a.thuhoi is null then 0 else a.thuhoi end) 'SupplyEviction' from (select s.supply_id, s.supply_name, es.department_id, d.department_name, s.unit, esd.used,esd.thuhoi from Supply s inner join Equipment_SCTX_Detail esd on s.supply_id = esd.supplyid inner join Equipment_SCTX es on es.maintain_id = esd.maintain_id inner join Department d on es.department_id = d.department_id and MONTH(es.[date]) = @month AND YEAR(es.[date]) = @year group by s.supply_id, es.department_id, s.supply_name, d.department_name, s.unit,esd.used,esd.thuhoi union all select s.supply_id, s.supply_name, mc.departmentid, d.department_name, s.unit, mcd.used,mcd.thuhoi from Supply s inner join Maintain_Car_Detail mcd on s.supply_id = mcd.supplyid inner join Maintain_Car mc on mc.maintainid = mcd.maintainid inner join Department d on d.department_id = mc.departmentid and MONTH(mc.[date]) = @month AND YEAR(mc.[date]) = @year group by s.supply_id, mc.departmentid, s.supply_name, d.department_name, s.unit,mcd.used,mcd.thuhoi union all select s.supply_id, s.supply_name, fac.department_id, d.department_name, s.unit, sum(fac.consumption_value) 'used', 0 'thuhoi' from Supply s inner join Fuel_activities_consumption fac on s.supply_id = fac.fuel_type and MONTH(fac.[date]) = @month AND YEAR(fac.[date]) = @year inner join Department d on d.department_id = fac.department_id group by s.supply_id, s.supply_name, fac.department_id, d.department_name, s.unit ) as a full outer join (select sp.supplyid, s.supply_name, sp.departmentid, d.department_name, sum(sp.quantity) 'quantity',sum(sp.quantity_provide) 'quantity_provide', s.unit from Supply s inner join SupplyPlan sp on s.supply_id = sp.supplyid inner join Department d on sp.departmentid = d.department_id where MONTH(sp.[date]) = @month and year(sp.[date]) = @year and sp.departmentid != 'CV' group by sp.supplyid, s.supply_name, sp.departmentid, d.department_name, s.unit ) as b on a.department_id = b.departmentid and a.supply_id = b.supplyid where (a.supply_id like @supplyid or b.supplyid like @supplyid ) and (b.departmentid like @departid or a.department_id like @departid ) and (a.supply_name like @supplyname or b.supply_name like @supplyname ) and (b.department_name like @departname or a.department_name like @departname ) group by a.supply_id, a.department_id, b.supplyid,b.quantity, b.departmentid, a.supply_name, b.supply_name, a.department_name, b.department_name, a.unit, b.unit,b.quantity_provide"; } if (search.type == "year") { sql = @"select (case when a.supply_id is null then b.supplyid else a.supply_id end) 'SupplyId', (case when a.supply_name is null then b.supply_name else a.supply_name end) 'SupplyName', (case when a.department_name is null then b.department_name else a.department_name end) 'DepartmentName', (case when b.quantity_provide is null then 0 else b.quantity_provide end) 'SupplyProvide', (case when b.quantity is null then 0 else b.quantity end) 'SupplyQuantity', (case when a.unit is null then b.unit else a.unit end) 'SupplyUnit', sum(case when a.used is null then 0 else a.used end) 'SupplyUsed', sum(case when a.thuhoi is null then 0 else a.thuhoi end) 'SupplyEviction' from (select s.supply_id, s.supply_name, es.department_id, d.department_name, s.unit, esd.used,esd.thuhoi from Supply s inner join Equipment_SCTX_Detail esd on s.supply_id = esd.supplyid inner join Equipment_SCTX es on es.maintain_id = esd.maintain_id inner join Department d on es.department_id = d.department_id AND YEAR(es.[date]) = @year group by s.supply_id, es.department_id, s.supply_name, d.department_name, s.unit,esd.used,esd.thuhoi union all select s.supply_id, s.supply_name, mc.departmentid, d.department_name, s.unit, mcd.used,mcd.thuhoi from Supply s inner join Maintain_Car_Detail mcd on s.supply_id = mcd.supplyid inner join Maintain_Car mc on mc.maintainid = mcd.maintainid inner join Department d on d.department_id = mc.departmentid AND YEAR(mc.[date]) = @year group by s.supply_id, mc.departmentid, s.supply_name, d.department_name, s.unit,mcd.used,mcd.thuhoi union all select s.supply_id, s.supply_name, fac.department_id, d.department_name, s.unit, sum(fac.consumption_value) 'used', 0 'thuhoi' from Supply s inner join Fuel_activities_consumption fac on s.supply_id = fac.fuel_type AND YEAR(fac.[date]) = @year inner join Department d on d.department_id = fac.department_id group by s.supply_id, s.supply_name, fac.department_id, d.department_name, s.unit ) as a full outer join (select sp.supplyid, s.supply_name, sp.departmentid, d.department_name, sum(sp.quantity) 'quantity',sum(sp.quantity_provide) 'quantity_provide', s.unit from Supply s inner join SupplyPlan sp on s.supply_id = sp.supplyid inner join Department d on sp.departmentid = d.department_id where year(sp.[date]) = @year and sp.departmentid != 'CV' group by sp.supplyid, s.supply_name, sp.departmentid, d.department_name, s.unit ) as b on a.department_id = b.departmentid and a.supply_id = b.supplyid where (a.supply_id like @supplyid or b.supplyid like @supplyid ) and (b.departmentid like @departid or a.department_id like @departid ) and (a.supply_name like @supplyname or b.supply_name like @supplyname ) and (b.department_name like @departname or a.department_name like @departname ) group by a.supply_id, a.department_id, b.supplyid,b.quantity, b.departmentid, a.supply_name, b.supply_name, a.department_name, b.department_name, a.unit, b.unit,b.quantity_provide "; } return(sql); }
public ActionResult ExportDetail(string SupplyId, string SupplyName, string DepartmentId, string DeparmentName, string type, string month, string year) { try { var val = month.Split(' '); var details = new List <DataTieuHao>(); int start = Convert.ToInt32(Request["start"]); int length = Convert.ToInt32(Request["length"]); string sortColumnName = Request["columns[" + Request["order[0][column]"] + "][name]"]; string sortDirection = Request["order[0][dir]"]; string path = HostingEnvironment.MapPath("/excel/CDVT/download/"); string templateFilename = "tieu-hao-vat-tu-chi-tiet.xlsx"; string downloadFilename = "tieu-hao-vat-tu-chi-tiet-download.xlsx"; FileInfo file = new FileInfo(path + templateFilename); var search = new Detail { SupplyId = SupplyId.Trim(), SupplyName = SupplyName.Trim(), DepartmentId = DepartmentId.Trim(), DeparmentName = DeparmentName.Trim(), type = type.Trim(), month = month.Trim(), year = year.Trim() }; if (search.type == null || search.type == "month") { details = context.Database.SqlQuery <DataTieuHao>(GetDetails(search), new SqlParameter("month", val[1]), new SqlParameter("year", val[2]), new SqlParameter("supplyid", "%" + search.SupplyId + "%"), new SqlParameter("departid", "%" + search.DepartmentId + "%"), new SqlParameter("supplyname", "%" + search.SupplyName + "%"), new SqlParameter("departname", "%" + search.DeparmentName + "%")).ToList(); } if (type == "year") { details = context.Database.SqlQuery <DataTieuHao>(GetDetails(search), new SqlParameter("year", year), new SqlParameter("supplyid", "%" + search.SupplyId + "%"), new SqlParameter("departid", "%" + search.DepartmentId + "%"), new SqlParameter("supplyname", "%" + search.SupplyName + "%"), new SqlParameter("departname", "%" + search.DeparmentName + "%")).ToList(); } using (ExcelPackage workbook = new ExcelPackage(file)) { int index = 2; ExcelWorkbook excelWorkbook = workbook.Workbook; ExcelWorksheet excelWorksheet = excelWorkbook.Worksheets.First(); foreach (var vattu in details) { excelWorksheet.Cells[index, 1].Value = vattu.SupplyId; excelWorksheet.Cells[index, 2].Value = vattu.SupplyName; excelWorksheet.Cells[index, 3].Value = vattu.DepartmentName; excelWorksheet.Cells[index, 4].Value = vattu.SupplyProvide; excelWorksheet.Cells[index, 5].Value = vattu.SupplyQuantity; excelWorksheet.Cells[index, 6].Value = vattu.SupplyUnit; excelWorksheet.Cells[index, 7].Value = vattu.SupplyUsed; excelWorksheet.Cells[index, 8].Value = vattu.SupplyEviction; index++; } workbook.SaveAs(new FileInfo(HostingEnvironment.MapPath($"/excel/CDVT/download/{downloadFilename}"))); string handle = Guid.NewGuid().ToString(); using (MemoryStream memoryStream = new MemoryStream()) { workbook.SaveAs(memoryStream); memoryStream.Position = 0; TempData[handle] = memoryStream.ToArray(); } return(Json(new { success = true, data = new { FileGuid = handle, FileName = downloadFilename } }, JsonRequestBehavior.AllowGet)); } } catch (Exception) { return(Json(new { success = false }, JsonRequestBehavior.AllowGet)); } }