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