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