public ActionResult Download(string date)
{
List <ReportPerMesin> result = new List <ReportPerMesin>();
DateTime selectedDate = Convert.ToDateTime(date);
using (HELLOWEntities db = new HELLOWEntities())
{
result = (from daily in db.tt_Daily
join record in db.tm_Recorder on daily.Recorder_FK equals record.Recorder_PK
join trans in db.tt_Transaction on daily.Daily_PK equals trans.Daily_FK into gj
from subpet in gj.DefaultIfEmpty()
join mesin in db.tm_Mesin on subpet.Mesin_FK equals mesin.Mesin_PK
join kw in db.tm_KodeWarna on subpet.KodeWarna_FK equals kw.KodeWarna_PK
where daily.Status_FK == 1 && DbFunctions.TruncateTime(daily.Date) == selectedDate.Date
select new ReportPerMesin
{
NoMesin = mesin.KodeMesin,
KodeWarna = kw.KodeWarna,
HasilKain = db.tt_TransactionDetail.Where(t => t.Transaction_FK == subpet.Transaction_PK && t.Status_FK == 1 && subpet.Status_FK == 1).Sum(i => (Double?)i.HasilKain) ?? 0,
Penambahan = subpet.Status_FK == 1 ? subpet.Penambahan ?? 0 : 0,
TotalKain = db.tt_TransactionDetail.Where(t => t.Transaction_FK == subpet.Transaction_PK && t.Status_FK == 1 && subpet.Status_FK == 1).Sum(i => (Double?)i.HasilKain) + subpet.Penambahan ?? 0
}).GroupBy(l => new
{
l.NoMesin,
l.KodeWarna
})
.Select(cl => new ReportPerMesin
{
NoMesin = cl.FirstOrDefault().NoMesin,
KodeWarna = cl.FirstOrDefault().KodeWarna,
HasilKain = cl.Sum(x => x.HasilKain),
Penambahan = cl.Sum(x => x.Penambahan),
TotalKain = cl.Sum(x => x.TotalKain)
}).ToList();
}
XLWorkbook wb = new XLWorkbook();
DataTable dt = DataCommonHelper.ConvertListToDataTable(result, string.Empty);
wb.Worksheets.Add(dt, "Report");
IXLWorksheet ws = wb.Worksheet(1);
ws.Columns().AdjustToContents();
string myName = Server.UrlEncode("ReportPerMesin" + date + ".xlsx");
MemoryStream stream = GetStream(wb);// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
return(View());
}
public ActionResult DownloadWaving(ReportProperty rp)
{
List <ReportListWaving> result = new List <ReportListWaving>();
using (HELLOWEntities db = new HELLOWEntities())
{
result = (from td in db.tt_TransactionDetail
join t in db.tt_Transaction on td.Transaction_FK equals t.Transaction_PK
join mesin in db.tm_Mesin on t.Mesin_FK equals mesin.Mesin_PK
join statmesin in db.tm_StatusMesin on mesin.StatusMesin_FK equals statmesin.StatusMesin_PK
join daily in db.tt_Daily on t.Daily_FK equals daily.Daily_PK
join kodewarna in db.tm_KodeWarna on t.KodeWarna_FK equals kodewarna.KodeWarna_PK
join op in db.tm_Operator on td.Operator_FK equals op.Operator_PK
where daily.Status_FK == 1 && t.Status_FK == 1 && td.Status_FK == 1 && daily.Date >= rp.startdate && daily.Date <= rp.enddate
select new ReportListWaving
{
NoOperator = op.NoOperator.ToString(),
NIP = op.NIP,
Nama = op.Nama,
HasilKain = td.HasilKain,
Total = Math.Round(kodewarna.Pick * statmesin.Nilai * td.HasilKain, 2)
}).GroupBy(l => l.NoOperator)
.Select(cl => new ReportListWaving
{
NoOperator = cl.FirstOrDefault().NoOperator,
NIP = cl.FirstOrDefault().NIP,
Nama = cl.FirstOrDefault().Nama,
HasilKain = cl.Sum(x => x.HasilKain),
Total = cl.Sum(x => x.Total)
}).ToList();
}
XLWorkbook wb = new XLWorkbook();
DataTable dt = DataCommonHelper.ConvertListToDataTable(result, string.Empty);
wb.Worksheets.Add(dt, "GajiWaving");
IXLWorksheet ws = wb.Worksheet(1);
string myName = Server.UrlEncode("GajiWaving.xlsx");
MemoryStream stream = GetStream(wb);// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
return(View());
}
public ActionResult DownloadInspect(ReportProperty rp)
{
List <ReportListWaving> result = new List <ReportListWaving>();
using (HELLOWEntities db = new HELLOWEntities())
{
result = (from t in db.tt_Transaction
join daily in db.tt_Daily on t.Daily_FK equals daily.Daily_PK
join rc in db.tm_Recorder on daily.Recorder_FK equals rc.Recorder_PK
where daily.Status_FK == 1 && t.Status_FK == 1 && daily.Date >= rp.startdate && daily.Date <= rp.enddate
select new ReportListWaving
{
NoOperator = rc.NoRecorder.ToString(),
NIP = rc.NIP,
Nama = rc.Nama,
HasilKain = db.tt_TransactionDetail.Where(x => x.Transaction_FK == t.Transaction_PK).Sum(i => ((Double?)i.HasilKain) ?? 0) + (t.Penambahan ?? 0.0)
}).GroupBy(l => l.NoOperator)
.Select(cl => new ReportListWaving
{
NoOperator = cl.FirstOrDefault().NoOperator,
NIP = cl.FirstOrDefault().NIP,
Nama = cl.FirstOrDefault().Nama,
HasilKain = cl.Sum(x => x.HasilKain),
}).ToList();
foreach (var item in result)
{
item.Total = item.HasilKain * 25;
}
}
XLWorkbook wb = new XLWorkbook();
DataTable dt = DataCommonHelper.ConvertListToDataTable(result, string.Empty);
wb.Worksheets.Add(dt, "GajiInspect");
IXLWorksheet ws = wb.Worksheet(1);
string myName = Server.UrlEncode("GajiInspect.xlsx");
MemoryStream stream = GetStream(wb);// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
return(View());
}