public FileResult GetGridHisForJerry(GetExamineReport_MasterParam param) { var db = new EnergyNetEntities(); #region Query Data var sql = @"Select A.CustID,D.CustName,A.DeliverDate,M.FixDate,M.CheckDate,P.LoginName As CheckerNme,A.GreenCheckState,C.repord_id,C.opt_id,C.mem,M.examine_report_record_id from ExamineReport_Master As A Inner Join ExamineReport_Record As M On A.CustID=M.CustID And A.YY=M.YY Inner Join Grad As B On M.examine_report_record_id = B.examine_report_record_id Inner Join Graid_Detail As C On B.grad_id = C.graid_id Inner Join BuileBase As D On A.YY =D.YY And A.CustID = D.CustID Left Join Tb_Login As P On A.CheckerId = P.loginid Left Join Tb_Login As T On M.AtuCheckerId = T.loginid Where M.CheckDate Is Not Null And A.CustID !='AAA' And C.opt_id!='p00' Order by A.CustID,M.examine_report_record_id,C.repord_id,C.opt_id"; var items = db.Database.SqlQuery<GetGridHisForJerryModal>(sql); #endregion var rpt_option = ReportList.GradOption(); var rpt_name = ReportList.GradRpt(); IList<SetGridHisForJerryModal> mdl = new List<SetGridHisForJerryModal>(); var gp1 = items.GroupBy(x => x.CustID); foreach (var _gp1 in gp1) { var gp2 = _gp1.GroupBy(x => x.examine_report_record_id); var max_id = gp2.Max(x => x.Key); foreach (var _gp2 in gp2) { if (_gp2.Key == max_id) { var gp3 = _gp2.GroupBy(x => new { x.repord_id, x.CustID, x.CustName, x.DeliverDate, x.FixDate, x.CheckDate, x.GreenCheckState, x.CheckerNme }); foreach (var _gp3 in gp3) { #region MyRegion var md = new SetGridHisForJerryModal(); md.CustID = _gp3.Key.CustID; md.CustName = _gp3.Key.CustName; md.DeliverDate = _gp3.Key.DeliverDate; md.FixDate = _gp3.Key.FixDate; md.CheckDate = _gp3.Key.CheckDate; md.GreenCheckState = _gp3.Key.GreenCheckState; md.repord_id = _gp3.Key.repord_id; md.repord_name = rpt_name[_gp3.Key.repord_id]; md.CheckerNme = _gp3.Key.CheckerNme; var options = _gp3.Select(x => x.opt_id); IList<string> option_name = new List<string>(); foreach (var option in options) { if (rpt_option.Any(x => x.Key == _gp3.Key.repord_id)) { var get_rpt_option_name = rpt_option[_gp3.Key.repord_id]; var get_name = get_rpt_option_name.Any(x => x.Key == option) ? get_rpt_option_name[option] : ""; if (option == "pth") get_name = "其他:" + _gp3.FirstOrDefault(x => x.opt_id == "pth").mem; option_name.Add(get_name); } } md.option_context = string.Join("、", option_name); mdl.Add(md); //Console.WriteLine(_gp3.Key.CustID); #endregion } } } } FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/審核情形view.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["工作表1"]; int i = 3; int n = 8; var pos = new Dictionary<string, int> { { "r01",n+0}, { "r02",n+1}, { "r03",n+2}, { "r41",n+3}, { "r42",n+4}, { "r51",n+5}, { "r52",n+6}, { "r53",n+7}, { "r54",n+8}, { "r55",n+9}, { "r06",n+10}, { "r07",n+11}, { "r08",n+12}, { "r91",n+13}, { "r92",n+14}, { "r93",n+15}, { "r94",n+16}, { "r95",n+17}, { "rA1",n+18}, { "rA3",n+19}, { "rA4",n+20}, { "rB1",n+21}, { "rB3",n+22}, { "rB4",n+23} }; var now_repord_id = string.Empty; var now_custid = string.Empty; foreach (var item in mdl) { if (item.CustID != now_custid) { i++; } now_custid = item.CustID; sheet.Cells[i + 1, 1].Value = item.CustID; sheet.Cells[i + 1, 2].Value = item.CustName; sheet.Cells[i + 1, 3].Value = fma_date(item.DeliverDate); sheet.Cells[i + 1, 4].Value = fma_date(item.FixDate); sheet.Cells[i + 1, 5].Value = fma_date(item.CheckDate); sheet.Cells[i + 1, 6].Value = item.CheckerNme; sheet.Cells[i + 1, 7].Value = fam_GreenState(item.GreenCheckState); sheet.Cells[i + 1, pos[item.repord_id]].Value = item.option_context; //i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "申報資料審查歷程紀錄表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); }
public FileResult GetGridHis(GetExamineReport_MasterParam param) { var db = new EnergyNetEntities(); #region Query Data var sql = @"Select A.YY,A.CustID,D.CustName,A.DeliverDate,M.FixDate,M.CheckDate,M.CheckState,C.repord_id,C.opt_id,C.mem,M.AtuCheckerId ,P.LoginName As CheckerNme,T.LoginName As ActuName from ExamineReport_Master As A Inner Join ExamineReport_Record As M On A.CustID=M.CustID And A.YY=M.YY Inner Join Grad As B On M.examine_report_record_id = B.examine_report_record_id Inner Join Graid_Detail As C On B.grad_id = C.graid_id Inner Join BuileBase As D On A.YY =D.YY And A.CustID = D.CustID Left Join Tb_Login As P On A.CheckerId = P.loginid Left Join Tb_Login As T On M.AtuCheckerId = T.loginid Where M.CheckDate Is Not Null And A.CustID !='AAA' And D.YY = 2015 Order by A.CustID,M.examine_report_record_id,C.repord_id,C.opt_id"; var items = db.Database.SqlQuery<GradHisList>(sql); #endregion var rpt_option = ReportList.GradOption(); var rpt_name = ReportList.GradRpt(); FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/GradHis.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["Query"]; int i = 1; foreach (var item in items) { var rpts = string.Empty; var option_name = string.Empty; var rptName = string.Empty; if (rpt_option.Any(x => x.Key == item.repord_id)) { var rpt = rpt_option[item.repord_id]; option_name = rpt.Any(x => x.Key == item.opt_id) ? rpt[item.opt_id] : "No"; rptName = rpt_name[item.repord_id]; } sheet.Cells[i + 1, 1].Value = i; sheet.Cells[i + 1, 2].Value = item.YY; sheet.Cells[i + 1, 3].Value = item.CustID; sheet.Cells[i + 1, 4].Value = item.CustName; sheet.Cells[i + 1, 5].Value = fma_date(item.DeliverDate); sheet.Cells[i + 1, 6].Value = fma_date(item.FixDate); sheet.Cells[i + 1, 7].Value = fma_date(item.CheckDate); sheet.Cells[i + 1, 8].Value = rptName; sheet.Cells[i + 1, 9].Value = fam_CheckState(item.CheckState); sheet.Cells[i + 1, 10].Value = option_name + (item.mem == null ? "" : item.mem); sheet.Cells[i + 1, 11].Value = item.CheckerNme; sheet.Cells[i + 1, 12].Value = item.ActuName; i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "申報資料審查歷程紀錄表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); }
public async Task<string> GetExamineReport_Master_Progress(GetExamineReport_MasterParam param) { Log.Write("Query GetExamineReport_Master_Progress"); Log.WriteToFile(); var db = new EnergyNetEntities(); var predicate = PredicateBuilder.True<ExamineReport_Master>(); predicate = predicate.And(x => x.YY == 2015); predicate = predicate.And(x => x.IsReject == false); if (param.CustId != null) predicate = predicate.And(x => x.CustID == param.CustId); if (param.CheckerId != null) predicate = predicate.And(x => x.CheckerId == param.CheckerId); if (param.AtuCheckerId != null) predicate = predicate.And(x => x.AtuCheckerId == param.AtuCheckerId); if (param.DeliverDate_Start != null) predicate = predicate.And(x => x.DeliverDate >= param.DeliverDate_Start); if (param.DeliverDate_End != null) predicate = predicate.And(x => x.DeliverDate <= param.DeliverDate_End); if (param.FixDate_Start != null) predicate = predicate.And(x => x.FixDate >= param.FixDate_Start); if (param.FixDate_End != null) predicate = predicate.And(x => x.FixDate <= param.FixDate_End); if (param.GreenCheckState != null) predicate = predicate.And(x => x.GreenCheckState == param.GreenCheckState); if (param.EconCheckState != null) predicate = predicate.And(x => x.EconCheckState == param.EconCheckState); var page_size = 10; int page_now = param.page == null ? 1 : (int)param.page; int skip_row = (page_size * (page_now - 1)); var dnow = DateTime.Now; var items = db.ExamineReport_Master.AsExpandable() .Where(predicate) .OrderByDescending(x => DbFunctions.DiffDays(x.FixDate, DateTime.Now)) .Select(x => new ExamineReport_Master_Progress() { YY = x.YY, CustID = x.CustID, DeliverDate = x.DeliverDate, FixDate = x.FixDate, GreenCheckState = x.GreenCheckState, EconCheckState = x.EconCheckState, CheckerId = x.CheckerId, CheckerName = x.Tb_Login_CheckerId.LoginName, CheckDate = x.CheckDate, CheckCount = x.CheckCount, InitPassDate = x.InitPassDate, EconPassDate = x.EconPassDate, CustName = x.BuileBase.CustName, AtuName = x.Tb_Login_AtuCheckerId.LoginName, diffDeliverDays = DbFunctions.DiffDays(x.DeliverDate, DateTime.Now), diffFixDays = DbFunctions.DiffDays(x.FixDate, DateTime.Now), diffCheckDays = DbFunctions.DiffDays(x.CheckDate, DateTime.Now) }); var row_count = await items.CountAsync(); int page_total = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(row_count) / page_size)); if (page_total == 0) page_total = 1; var result = await items.Skip(skip_row).Take(page_size).ToListAsync(); //foreach (var rlt in result) //{ // if (rlt.FixDate != null && (rlt.GreenCheckState == "C" || rlt.GreenCheckState == "D")) // {//已經修正 但還沒審 // rlt.diffFixDays = (DateTime.Now - (DateTime)rlt.FixDate).Days; // } // if (rlt.CheckDate != null && rlt.GreenCheckState == "B") // {//已經審過 但未通過 仍未補正 // rlt.diffCheckDays = (DateTime.Now - (DateTime)rlt.CheckDate).Days; // } //} var r = new GridExamineReport_Master(); r.items = result; r.page = page_now; r.page_total = page_total; r.rows_count = row_count; return defJSONNull(r); }
public FileResult GetExam(GetExamineReport_MasterParam param) { var db = new EnergyNetEntities(); #region Query Data var predicate = PredicateBuilder.True<ExamineReport_Master>(); predicate = predicate.And(x => x.YY == 2015); predicate = predicate.And(x => x.IsReject == false); if (param.CustId != null) predicate = predicate.And(x => x.CustID == param.CustId); if (param.CheckerId != null) predicate = predicate.And(x => x.CheckerId == param.CheckerId); if (param.AtuCheckerId != null) predicate = predicate.And(x => x.AtuCheckerId == param.AtuCheckerId); if (param.DeliverDate_Start != null) predicate = predicate.And(x => x.DeliverDate >= param.DeliverDate_Start); if (param.DeliverDate_End != null) predicate = predicate.And(x => x.DeliverDate <= param.DeliverDate_End); if (param.FixDate_Start != null) predicate = predicate.And(x => x.FixDate >= param.FixDate_Start); if (param.FixDate_End != null) predicate = predicate.And(x => x.FixDate <= param.FixDate_End); if (param.GreenCheckState != null) predicate = predicate.And(x => x.GreenCheckState == param.GreenCheckState); if (param.EconCheckState != null) predicate = predicate.And(x => x.EconCheckState == param.EconCheckState); var items = db.ExamineReport_Master.AsExpandable() .Where(predicate) .OrderByDescending(x => x.DeliverDate) .Select(x => new { x.YY, x.CustID, x.DeliverDate, x.FixDate, x.GreenCheckState, x.EconCheckState, x.CheckerId, CheckerName = x.Tb_Login_CheckerId.LoginName, x.CheckDate, x.CheckCount, x.InitPassDate, x.EconPassDate, x.IsReject, x.AtuCheckerId, CustName = x.BuileBase.CustName, AtuName = x.Tb_Login_AtuCheckerId.LoginName }).ToList(); #endregion FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/ExamQuery.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["Query"]; int i = 1; foreach (var item in items) { sheet.Cells[i + 1, 1].Value = i; sheet.Cells[i + 1, 2].Value = item.CustID; sheet.Cells[i + 1, 3].Value = item.CustName; sheet.Cells[i + 1, 4].Value = item.CheckerName; sheet.Cells[i + 1, 5].Value = item.AtuName; sheet.Cells[i + 1, 6].Value = fma_date(item.DeliverDate); sheet.Cells[i + 1, 7].Value = fam_GreenCheckState(item.GreenCheckState); sheet.Cells[i + 1, 8].Value = fma_date(item.FixDate); sheet.Cells[i + 1, 9].Value = fma_date(item.CheckDate); sheet.Cells[i + 1, 10].Value = item.CheckCount; sheet.Cells[i + 1, 11].Value = fma_date(item.InitPassDate); sheet.Cells[i + 1, 12].Value = fam_EconCheckState(item.EconCheckState); sheet.Cells[i + 1, 13].Value = fma_date(item.EconPassDate); i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "申報資料審核-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); }
public async Task<string> GetExamineReport_Master(GetExamineReport_MasterParam param) { Log.Write("Query GetExamineReport_Master"); Log.WriteToFile(); var db = new EnergyNetEntities(); var predicate = PredicateBuilder.True<ExamineReport_Master>(); predicate = predicate.And(x => x.YY == 2015); predicate = predicate.And(x => x.IsReject == false); if (param.CustId != null) predicate = predicate.And(x => x.CustID == param.CustId); if (param.CheckerId != null) predicate = predicate.And(x => x.CheckerId == param.CheckerId); if (param.AtuCheckerId != null) predicate = predicate.And(x => x.AtuCheckerId == param.AtuCheckerId); if (param.DeliverDate_Start != null) predicate = predicate.And(x => x.DeliverDate >= param.DeliverDate_Start); if (param.DeliverDate_End != null) predicate = predicate.And(x => x.DeliverDate <= param.DeliverDate_End); if (param.FixDate_Start != null) predicate = predicate.And(x => x.FixDate >= param.FixDate_Start); if (param.FixDate_End != null) predicate = predicate.And(x => x.FixDate <= param.FixDate_End); if (param.GreenCheckState != null) predicate = predicate.And(x => x.GreenCheckState == param.GreenCheckState); if (param.EconCheckState != null) predicate = predicate.And(x => x.EconCheckState == param.EconCheckState); var page_size = 10; int page_now = param.page == null ? 1 : (int)param.page; int skip_row = (page_size * (page_now - 1)); Log.Write(new string[] { "skip_row", skip_row.ToString() }); Log.WriteToFile(); var items = db.ExamineReport_Master.AsExpandable() .Where(predicate) .OrderByDescending(x => x.DeliverDate) .Select(x => new { x.YY, x.CustID, x.DeliverDate, x.FixDate, x.GreenCheckState, x.EconCheckState, x.CheckerId, CheckerName = x.Tb_Login_CheckerId.LoginName, x.CheckDate, x.CheckCount, x.InitPassDate, x.EconPassDate, x.IsReject, x.AtuCheckerId, CustName = x.BuileBase.CustName, AtuName = x.Tb_Login_AtuCheckerId.LoginName }); var row_count = await items.CountAsync(); int page_total = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(row_count) / page_size)); if (page_total == 0) page_total = 1; var result = await items.Skip(skip_row).Take(page_size).ToListAsync(); var r = new GridExamineReport_Master(); r.items = result; r.page = page_now; r.page_total = page_total; r.rows_count = row_count; return defJSONNull(r); }