using Cksoft.Data;
using DllEapEntity;
using DllEapEntity.Dtos;
using DllEapEntity.OFILM;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace DllEapDal.OFILM
{
public class ForecastDal
{
private IDatabase CurrDb = null;
public ForecastDal(IDatabase db)
{
CurrDb = db;
}
///
/// 设备需求预测
///
///
///
///
///
///
///
///
public IEnumerable Get(int start, int length, string order, string sort, string filter, string errorinfo)
{
var sql = $@"
select MAX(id) id,equipmentmodel,park,floor,MAX(score) score,IFNULL(MAX( number ),0) number
FROM (
SELECT MAX(t.id) id,equipmentmodel,park,floor,MAX(score) score,planTime,CEILING(SUM(CASE
WHEN locate('FQC',assignmentContent) >0 THEN genfive(number)
WHEN accounting='0.25' THEN
gensecfive(number)
WHEN accounting='0.5' THEN
genfive(number)
WHEN accounting='1' THEN CEILING(number)
ELSE
number
END
)) number
FROM
(
SELECT tt.equipmentmodel,
tt.id,
tt.park,
tt.floor,
b.machineType,
b.workshopSection,
c.planTime,
tt.score,
c.dayPlan,
b.assignmentContent,
accounting,
b.UPH,
b.eropTurnoverRate,
CASE b.workshopSection
WHEN 'COB' THEN
c.dayPlan/22/0.95/.97/ replace (b.eropTurnoverRate,'%','')*100 /b.UPH
WHEN 'EOL' THEN
c.dayPlan/22/0.95/ replace (b.eropTurnoverRate,'%','')*100/b.UPH
ELSE
0
END number
from
(
(select id,equipmentmodel,accounting,'三号园区' as park,'2#1F' as floor,IFNULL(threeSecFir,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'未来城' as park,'A1#1F' as floor,IFNULL(futureCity,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'未来城' as park,'A1#3F' as floor,IFNULL(futureCityThird,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#1F' as floor,IFNULL(secfirstFir,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#2F' as floor,IFNULL(secFirstSec,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#3F' as floor,IFNULL(secFirstThird,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A2#1F' as floor,IFNULL(secSecFirst,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A2#2F' as floor,IFNULL(secSecSec,0) as score from equipmentnumber)
) tt
LEFT JOIN workingprocedure b on tt.equipmentModel=b.equipmentModel and tt.park = b.park and tt.Floor=b.floor
LEFT JOIN (SELECT park,floor,customer,planTime,SUM(dayPlan) dayPlan FROM productionplan WHERE planTime>=CURRENT_DATE() GROUP BY park, floor,customer,planTime) c ON c.customer=b.machineType
and c.Floor = b.Floor
WHERE 1=1
{filter}
order by equipmentmodel,tt.floor
) t
GROUP BY equipmentmodel,park,floor,planTime
) e
GROUP BY equipmentmodel,park,floor
limit {start - 1},{length}
";
var models = CurrDb.FindList(sql);
return models;
}
///
/// 设备需求预测总数
///
///
///
public int GetCount(string filter)
{
string errorinfo = string.Empty;
var sql = $@" select Count(*) Count
FROM (
SELECT equipmentmodel,park,floor
FROM
(
SELECT tt.equipmentmodel,
tt.park,
tt.floor
from
(
(select id,equipmentmodel,accounting,'三号园区' as park,'2#1F' as floor,IFNULL(threeSecFir,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'未来城' as park,'A1#1F' as floor,IFNULL(futureCity,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'未来城' as park,'A1#3F' as floor,IFNULL(futureCityThird,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#1F' as floor,IFNULL(secfirstFir,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#2F' as floor,IFNULL(secFirstSec,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#3F' as floor,IFNULL(secFirstThird,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A2#1F' as floor,IFNULL(secSecFirst,0) as score from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A2#2F' as floor,IFNULL(secSecSec,0) as score from equipmentnumber)
) tt
LEFT JOIN workingprocedure b on tt.equipmentModel=b.equipmentModel and tt.park = b.park and tt.Floor=b.floor
LEFT JOIN (SELECT park,floor,customer FROM productionplan WHERE planTime>=CURRENT_DATE() GROUP BY park, floor,customer) c ON c.customer=b.machineType
WHERE 1=1 {filter}
order by equipmentmodel,tt.floor
) t
GROUP BY equipmentmodel,park,floor
) e
";
var entities = CurrDb.FindList(sql).FirstOrDefault();
if (entities != null)
{
return entities.Count;
}
return 0;
}
public IEnumerable getMainPlan()
{
string sql = "SELECT DISTINCT planTime FROM productionplan WHERE planTime like '%主计划%'";
var model = CurrDb.FindList(sql);
return model;
}
public IEnumerable getMaxPlan()
{
string sql = "SELECT max(STR_TO_DATE(planTime,'%Y-%m-%d') ) planTime FROM productionplan WHERE planTime not like '%主计划%'";
var model = CurrDb.FindList(sql);
return model;
}
public IEnumerable getMinPlan()
{
string sql = "SELECT min(STR_TO_DATE(planTime,'%Y-%m-%d') ) planTime FROM productionplan WHERE planTime not like '%主计划%'";
var model = CurrDb.FindList(sql);
return model;
}
public string getsql(string filter,string subfilter)
{
string sql = $@"
SELECT equipmentmodel,park,floor,MAX(sore) score,CEILING(SUM(CASE accounting
WHEN '0.25' THEN
gensecfive(number)
WHEN '0.5' THEN
genfive(number)
ELSE
number
END
)) number
FROM
(
SELECT tt.equipmentmodel,
tt.park,
tt.floor,
b.machineType,
tt.sore,
c.dayPlan,
accounting,
c.planTime,
b.eropTurnoverRate,
CASE b.workshopSection
WHEN 'COB' THEN
c.dayPlan/28/22/0.95/.97/ replace (b.eropTurnoverRate,'%','')*100 /b.UPH
WHEN 'EOL' THEN
c.dayPlan/28/22/0.95/ replace (b.eropTurnoverRate,'%','')*100 /b.UPH
ELSE
0
END number
from
(
(select id,equipmentmodel,accounting,'三号园区' as park,'2#1F' as floor,IFNULL(threeSecFir,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'未来城' as park,'A1#1F' as floor,IFNULL(futureCity,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'未来城' as park,'A1#3F' as floor,IFNULL(futureCityThird,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#1F' as floor,IFNULL(secfirstFir,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#2F' as floor,IFNULL(secFirstSec,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A1#3F' as floor,IFNULL(secFirstThird,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A2#1F' as floor,IFNULL(secSecFirst,0) as sore from equipmentnumber)
UNION
(select id,equipmentmodel,accounting,'二号园区' as park,'A2#2F' as floor,IFNULL(secSecSec,0) as sore from equipmentnumber)
) tt
LEFT JOIN workingprocedure b on tt.equipmentModel=b.equipmentModel and tt.park = b.park and tt.Floor=b.floor
LEFT JOIN (SELECT * FROM productionplan WHERE planTime = '{filter}') c ON c.customer=b.machineType
WHERE 1=1 {subfilter}
order by equipmentmodel,tt.floor
-- limit 460,20
) t
GROUP BY equipmentmodel,park,floor";
return sql;
}
///
/// 预测详情总览
///
///
public IEnumerable getDetail(string filter)
{
string sql = $@"SELECT equipmentmodel,park,floor,MAX(sore) score,planTime,CEILING(SUM(CASE
WHEN locate('FQC',assignmentContent) >0 THEN genfive(number)
WHEN accounting='0.25' THEN
gensecfive(number)
WHEN accounting='0.5' THEN
genfive(number)
WHEN accounting='1' THEN CEILING(number)
ELSE
number
END
)) number
FROM
(
SELECT tt.equipmentmodel,
tt.park,
tt.floor,
b.machineType,
tt.sore,
b.assignmentContent,
c.dayPlan,
accounting,
DATE_FORMAT(c.planTime, '%Y-%m-%d') planTime,
b.eropTurnoverRate,
CASE b.workshopSection
WHEN 'COB' THEN
c.dayPlan / 22 / 0.95 / .97 / replace(b.eropTurnoverRate, '%', '') *100 /b.UPH
WHEN 'EOL' THEN
c.dayPlan / 22 / 0.95 / replace(b.eropTurnoverRate, '%', '') *100 /b.UPH
ELSE
0
END number
from
(
(select id, equipmentmodel, accounting, '三号园区' as park, '2#1F' as floor, IFNULL(threeSecFir, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '未来城' as park, 'A1#1F' as floor, IFNULL(futureCity, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '未来城' as park, 'A1#3F' as floor, IFNULL(futureCityThird, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A1#1F' as floor, IFNULL(secfirstFir, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A1#2F' as floor, IFNULL(secFirstSec, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A1#3F' as floor, IFNULL(secFirstThird, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A2#1F' as floor, IFNULL(secSecFirst, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A2#2F' as floor, IFNULL(secSecSec, 0) as sore from equipmentnumber)
) tt
LEFT JOIN workingprocedure b on tt.equipmentModel = b.equipmentModel and tt.park = b.park and tt.Floor = b.floor
LEFT JOIN(SELECT* FROM productionplan WHERE planTime not like '%主计划%') c ON c.customer = b.machineType
and c.Floor = b.Floor
WHERE 1 = 1 {filter}
order by equipmentmodel,tt.floor
) t
WHERE planTime is not null
GROUP BY equipmentmodel,park,floor,planTime
order by planTime";
var model = CurrDb.FindList(sql);
return model;
}
public IEnumerable getDetailTotal(string filter)
{
string sql = $@"SELECT sum(sore) score,planTime,CEILING(SUM(CASE
WHEN locate('FQC',assignmentContent) >0 THEN genfive(number)
WHEN accounting='0.25' THEN
gensecfive(number)
WHEN accounting='0.5' THEN
genfive(number)
WHEN accounting='1' THEN CEILING(number)
ELSE
number
END
)) number
FROM
(
SELECT tt.equipmentmodel,
tt.park,
tt.floor,
b.machineType,
tt.sore,
b.assignmentContent,
c.dayPlan,
accounting,
DATE_FORMAT(c.planTime, '%Y-%m-%d') planTime,
b.eropTurnoverRate,
CASE b.workshopSection
WHEN 'COB' THEN
c.dayPlan / 22 / 0.95 / .97 / replace(b.eropTurnoverRate, '%', '') *100 /b.UPH
WHEN 'EOL' THEN
c.dayPlan / 22 / 0.95 / replace(b.eropTurnoverRate, '%', '') *100 /b.UPH
ELSE
0
END number
from
(
(select id, equipmentmodel, accounting, '三号园区' as park, '2#1F' as floor, IFNULL(threeSecFir, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '未来城' as park, 'A1#1F' as floor, IFNULL(futureCity, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '未来城' as park, 'A1#3F' as floor, IFNULL(futureCityThird, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A1#1F' as floor, IFNULL(secfirstFir, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A1#2F' as floor, IFNULL(secFirstSec, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A1#3F' as floor, IFNULL(secFirstThird, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A2#1F' as floor, IFNULL(secSecFirst, 0) as sore from equipmentnumber)
UNION
(select id, equipmentmodel, accounting, '二号园区' as park, 'A2#2F' as floor, IFNULL(secSecSec, 0) as sore from equipmentnumber)
) tt
LEFT JOIN workingprocedure b on tt.equipmentModel = b.equipmentModel and tt.park = b.park and tt.Floor = b.floor
LEFT JOIN(SELECT* FROM productionplan WHERE planTime not like '%主计划%') c ON c.customer = b.machineType
WHERE 1 = 1 {filter}
order by equipmentmodel,tt.floor
) t
WHERE planTime is not null
GROUP BY planTime
order by planTime";
var model = CurrDb.FindList(sql);
return model;
}
///
/// 预测详情总行数
///
///
///
public int GetEmCount(string filter)
{
string errorinfo = string.Empty;
var sql = $@"select Count(1) Count from ( (select id,equipmentmodel,'三号园区' as park,'2#1F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'未来城' as park,'A1#1F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'未来城' as park,'A1#3F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'二号园区' as park,'A1#1F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'二号园区' as park,'A1#2F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'二号园区' as park,'A1#3F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'二号园区' as park,'A2#1F' as floor from equipmentnumber)
UNION
(select id,equipmentmodel,'二号园区' as park,'A2#2F' as floor from equipmentnumber) )tt
where 1=1 {filter}
";
var entities = CurrDb.FindList(sql).FirstOrDefault();
if (entities != null)
{
return entities.Count;
}
return 0;
}
///
/// 获取园区
///
///
///
public IEnumerable> GetMultipleSelects(string filter)
{
var sql = $@"select park value,park label
FROM
(
(select '三号园区' as park)
UNION
(select '未来城' as park)
UNION
(select '二号园区' as park)
) t";
var entities = CurrDb.FindList>(sql);
return entities;
}
///
/// 获取机种下拉框
///
///
///
public IEnumerable> GetMultipleMacTypeSelects(string filter)
{
var sql = $@"select distinct MachineType value,MachineType label
FROM WorkingProcedure where 1=1 {filter}";
var entities = CurrDb.FindList>(sql);
return entities;
}
///
/// 获取楼层
///
///
///
public IEnumerable> GetMultipleSelectsFloor(string filter)
{
var sql = $@"
select DISTINCT floor value,floor label
FROM
(
(select '三号园区' as park, '2#1F' as floor )
UNION
(select '未来城' as park, 'A1#1F' as floor )
UNION
(select '未来城' as park, 'A1#3F' as floor )
UNION
(select '二号园区' as park, 'A1#1F' as floor )
UNION
(select '二号园区' as park, 'A1#2F' as floor)
UNION
(select '二号园区' as park, 'A1#3F' as floor )
UNION
(select '二号园区' as park, 'A2#1F' as floor )
UNION
(select '二号园区' as park, 'A2#2F' as floor )
) t
WHERE 1=1 {filter}";
var entities = CurrDb.FindList>(sql);
return entities;
}
///
/// 预测详情转DataTable
///
///
///
public DataTable Getfor(IEnumerable roles)
{
string plantime = new ProductPlanDal(CurrDb).getMin().PlanTime;
var plan = getMainPlan();
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(plantime))
{
string time = plantime;
DateTime st = Convert.ToDateTime(time);
dt.Columns.Add("设备型号");
dt.Columns.Add("园区");
dt.Columns.Add("楼层");
dt.Columns.Add("现有数量");
dt.Columns.Add("ParkRowSpan");
dt.Columns.Add("ModelRowSpan");
dt.Columns.Add(st.ToString("MM") + "月预测");
for (int h = 1; h <= plan.Count(); h++)
{
dt.Columns.Add(st.AddMonths(h).ToString("MM") + "月预测");
}
dt.Columns.Add(st.ToString("MM") + "月利用率");
for (int h = 1; h <= plan.Count(); h++)
{
dt.Columns.Add(st.AddMonths(h).ToString("MM") + "月利用率");
}
dt.Columns.Add("预警");
if (dt != null && dt.Columns.Count > 0)
{
string subfilter = $" and tt.id in ( {string.Join(",", roles.Select(l => l.ID).ToArray())})";
Dictionary> dic = new Dictionary>();
foreach (var itemd in plan)
{
dic.Add(itemd.PlanTime, CurrDb.FindList(getsql(itemd.PlanTime, subfilter)));
}
string Equipmentmodel = "";
var last = roles.LastOrDefault();
foreach (var it in roles)
{
DataRow row = dt.NewRow();
if (string.IsNullOrEmpty(Equipmentmodel))
{
Equipmentmodel = it.Equipmentmodel;
#region
row["设备型号"] = it.Equipmentmodel;
row["园区"] = it.Park;
row["楼层"] = it.Floor;
row["现有数量"] = it.Score;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
row[st.ToString("MM") + "月预测"] = it.Number;
int i = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == it.Equipmentmodel && l.Floor == it.Floor && l.Park == it.Park).Select(c => c.Number).FirstOrDefault();
row[st.AddMonths(i + 1).ToString("MM") + "月预测"] = (number == null ? "0" : number);
i++;
}
if (it.Score?.Trim() == "0" || string.IsNullOrEmpty(it.Score) || it.Score=="0")
{
row[st.ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.ToString("MM") + "月利用率"] = (Convert.ToDecimal(it.Number) / Convert.ToInt32(it.Score) * 100).ToString("0.00") + "%";
}
int m = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == it.Equipmentmodel && l.Floor == it.Floor && l.Park == it.Park).Select(c => c.Number).FirstOrDefault();
if (number == "0" || string.IsNullOrEmpty(number) || it.Score=="0")
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = (Convert.ToDecimal(number) / Convert.ToInt32(it.Score) * 100).ToString("0.00") + "%";
}
m++;
}
row["预警"] = "88%";
dt.Rows.Add(row);
#endregion
}
else
{
if (Equipmentmodel != it.Equipmentmodel)
{
#region 合计
string Scores = roles.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(p => Convert.ToInt32(p.Score)).ToString();
string numbers = roles.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(p => Convert.ToInt32(p.Number)).ToString();
row["设备型号"] = "合计";
row["园区"] = "合计";
row["楼层"] = "合计";
row["现有数量"] = Scores;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
row[st.ToString("MM") + "月预测"] = numbers;
int i = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(c => Convert.ToInt32(c.Number)).ToString();
row[st.AddMonths(i + 1).ToString("MM") + "月预测"] = (number == null ? "0" : number);
i++;
}
if (Scores == "0" || string.IsNullOrEmpty(Scores) || Scores=="0")
{
row[st.ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.ToString("MM") + "月利用率"] = (Convert.ToDecimal(numbers) / Convert.ToInt32(Scores) * 100).ToString("0.00") + "%";
}
int m = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(c => Convert.ToInt32(c.Number)).ToString();
if (number == "0" || string.IsNullOrEmpty(number) || it.Score=="0")
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = (Convert.ToDecimal(number) / Convert.ToInt32(Scores) * 100).ToString("0.00") + "%";
}
m++;
}
row["预警"] = "88%";
dt.Rows.Add(row);
#endregion
Equipmentmodel = it.Equipmentmodel;
#region
row = dt.NewRow();
row["设备型号"] = it.Equipmentmodel;
row["园区"] = it.Park;
row["楼层"] = it.Floor;
row["现有数量"] = it.Score;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
row[st.ToString("MM") + "月预测"] = it.Number;
i = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == it.Equipmentmodel && l.Floor == it.Floor && l.Park == it.Park).Select(c => c.Number).FirstOrDefault();
row[st.AddMonths(i + 1).ToString("MM") + "月预测"] = (number == null ? "0" : number);
i++;
}
if (it.Score?.Trim() == "0" || string.IsNullOrEmpty(it.Score) || it.Score=="0")
{
row[st.ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.ToString("MM") + "月利用率"] = (Convert.ToDecimal(it.Number) / Convert.ToInt32(it.Score) * 100).ToString("0.00") + "%";
}
m = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == it.Equipmentmodel && l.Floor == it.Floor && l.Park == it.Park).Select(c => c.Number).FirstOrDefault();
if (number == "0" || string.IsNullOrEmpty(number) || it.Score=="0")
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = (Convert.ToDecimal(number) / Convert.ToInt32(it.Score) * 100).ToString("0.00") + "%";
}
m++;
}
row["预警"] = "88%";
dt.Rows.Add(row);
#endregion
}
else
{
#region
row["设备型号"] = it.Equipmentmodel;
row["园区"] = it.Park;
row["楼层"] = it.Floor;
row["现有数量"] = it.Score;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
row[st.ToString("MM") + "月预测"] = it.Number;
int i = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == it.Equipmentmodel && l.Floor == it.Floor && l.Park == it.Park).Select(c => c.Number).FirstOrDefault();
row[st.AddMonths(i + 1).ToString("MM") + "月预测"] = (number == null ? "0" : number);
i++;
}
if (it.Score?.Trim() == "0" || string.IsNullOrEmpty(it.Score) || it.Score=="0")
{
row[st.ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.ToString("MM") + "月利用率"] = (Convert.ToDecimal(it.Number) / Convert.ToInt32(it.Score) * 100).ToString("0.00") + "%";
}
int m = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == it.Equipmentmodel && l.Floor == it.Floor && l.Park == it.Park).Select(c => c.Number).FirstOrDefault();
if (number == "0" || string.IsNullOrEmpty(number) || it.Score=="0")
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = (Convert.ToDecimal(number) / Convert.ToInt32(it.Score) * 100).ToString("0.00") + "%";
}
m++;
}
row["预警"] = "88%";
dt.Rows.Add(row);
#endregion
}
}
}
if (last != null)
{
DataRow row = dt.NewRow();
#region 合计
string Scores = roles.Where(l => l.Equipmentmodel == last.Equipmentmodel).Sum(p => Convert.ToInt32(p.Score)).ToString();
string numbers = roles.Where(l => l.Equipmentmodel == last.Equipmentmodel).Sum(p => Convert.ToInt32(p.Number)).ToString();
row["设备型号"] = "合计";
row["园区"] = "合计";
row["楼层"] = "合计";
row["现有数量"] = Scores;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
row[st.ToString("MM") + "月预测"] = numbers;
int i = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == last.Equipmentmodel).Sum(c => Convert.ToInt32(c.Number)).ToString();
row[st.AddMonths(i + 1).ToString("MM") + "月预测"] = (number == null ? "0" : number);
i++;
}
if (Scores == "0" || string.IsNullOrEmpty(Scores) || Scores=="0")
{
row[st.ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.ToString("MM") + "月利用率"] = (Convert.ToDecimal(numbers) / Convert.ToInt32(Scores) * 100).ToString("0.00") + "%";
}
int m = 0;
foreach (var item in plan)
{
var planNew = dic[item.PlanTime];
string number = planNew.Where(l => l.Equipmentmodel == last.Equipmentmodel).Sum(c => Convert.ToInt32(c.Number)).ToString();
if (number == "0" || string.IsNullOrEmpty(number) || last.Score=="0")
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = "0.00%";
}
else
{
row[st.AddMonths(m + 1).ToString("MM") + "月利用率"] = (Convert.ToDecimal(number) / Convert.ToInt32(Scores) * 100).ToString("0.00") + "%";
}
m++;
}
row["预警"] = "88%";
dt.Rows.Add(row);
#endregion
}
}
}
var modelGroup = roles.GroupBy(c => c.Equipmentmodel);
// 合并行数
foreach (var model in modelGroup)
{
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["设备型号"].ToString() == model.Key)
{
dt.Rows[i]["ModelRowSpan"] = model.Count()+1;
var parkGroup = roles.Where(l => l.Equipmentmodel == model.Key).GroupBy(c => c.Park);
foreach (var it in parkGroup)
{
if (dt.Rows[i]["园区"].ToString() == it.Key)
{
dt.Rows[i]["ParkRowSpan"] = it.Count();
}
}
}
}
}
return dt;
}
///
/// 预测详情转DataTable
///
///
///
///
public DataTable GetForDetail(IEnumerable macList, IEnumerable Equipmentmodels)
{
DataTable dt = new DataTable();
dt.Columns.Add("设备型号");
dt.Columns.Add("园区");
dt.Columns.Add("楼层");
dt.Columns.Add("现有数量");
dt.Columns.Add("ParkRowSpan");
dt.Columns.Add("ModelRowSpan");
DateTime dd = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date.AddMonths(1).AddDays(-1);//当月最后一天
DateTime maxplan = Convert.ToDateTime( getMaxPlan().FirstOrDefault().PlanTime);
DateTime minplan = Convert.ToDateTime(getMinPlan().FirstOrDefault().PlanTime);
int days = maxplan.Subtract(DateTime.Now).Days + 1;
for (int i = 0; i <= days; i++)
{
var date = DateTime.Now.AddDays(i).ToString("MM月dd日");
dt.Columns.Add(date);
}
string Equipmentmodel = "";
var last = Equipmentmodels.LastOrDefault();
if (Equipmentmodels != null)
{
foreach (var mac in Equipmentmodels)
{
DataRow row = dt.NewRow();
if (string.IsNullOrEmpty(Equipmentmodel))
{
Equipmentmodel = mac.Equipmentmodel;
#region
row["设备型号"] = mac.Equipmentmodel;
row["园区"] = mac.Park;
row["楼层"] = mac.Floor;
row["现有数量"] = mac.Score;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
for (int i = 0; i <= days; i++)
{
var date = DateTime.Now.AddDays(i).ToString("yyyy-MM-dd");
string number = macList.Where(m => m.PlanTime == date && m.Equipmentmodel == mac.Equipmentmodel && m.Floor == mac.Floor && m.Park == mac.Park).Select(l => l.Number).FirstOrDefault();
row[DateTime.Now.AddDays(i).ToString("MM月dd日")] = (number == null ? "0" : number);
}
dt.Rows.Add(row);
#endregion
}
else
{
if (Equipmentmodel != mac.Equipmentmodel)
{
#region 合计
string Scores = Equipmentmodels.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(p => Convert.ToInt32(p.Score)).ToString();
string numbers = Equipmentmodels.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(p => Convert.ToInt32(p.Number)).ToString();
row["设备型号"] = "合计"; ;
row["园区"] = "合计"; ;
row["楼层"] = "合计"; ;
row["现有数量"] = Scores;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
for (int i = 0; i <= days; i++)
{
var date = DateTime.Now.AddDays(i).ToString("yyyy-MM-dd");
string number = macList.Where(m => m.PlanTime == date && m.Equipmentmodel == Equipmentmodel).Sum(l => Convert.ToInt32(l.Number)).ToString();
row[DateTime.Now.AddDays(i).ToString("MM月dd日")] = (number == null ? "0" : number);
}
dt.Rows.Add(row);
#endregion
Equipmentmodel = mac.Equipmentmodel;
#region
row = dt.NewRow();
row["设备型号"] = mac.Equipmentmodel;
row["园区"] = mac.Park;
row["楼层"] = mac.Floor;
row["现有数量"] = mac.Score;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
for (int i = 0; i <= days; i++)
{
var date = DateTime.Now.AddDays(i).ToString("yyyy-MM-dd");
string number = macList.Where(m => m.PlanTime == date && m.Equipmentmodel == mac.Equipmentmodel && m.Floor == mac.Floor && m.Park == mac.Park).Select(l => l.Number).FirstOrDefault();
row[DateTime.Now.AddDays(i).ToString("MM月dd日")] = (number == null ? "0" : number);
}
dt.Rows.Add(row);
#endregion
}
else
{
#region
row["设备型号"] = mac.Equipmentmodel;
row["园区"] = mac.Park;
row["楼层"] = mac.Floor;
row["现有数量"] = mac.Score;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
for (int i = 0; i <= days; i++)
{
var date = DateTime.Now.AddDays(i).ToString("yyyy-MM-dd");
string number = macList.Where(m => m.PlanTime == date && m.Equipmentmodel == mac.Equipmentmodel && m.Floor == mac.Floor && m.Park == mac.Park).Select(l => l.Number).FirstOrDefault();
row[DateTime.Now.AddDays(i).ToString("MM月dd日")] = (number == null ? "0" : number);
}
dt.Rows.Add(row);
#endregion
}
}
}
}
if (last != null)
{
DataRow row = dt.NewRow();
#region 合计
string Scores = Equipmentmodels.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(p => Convert.ToInt32(p.Score)).ToString();
string numbers = Equipmentmodels.Where(l => l.Equipmentmodel == Equipmentmodel).Sum(p => Convert.ToInt32(p.Number)).ToString();
row["设备型号"] = "合计"; ;
row["园区"] = "合计"; ;
row["楼层"] = "合计"; ;
row["现有数量"] = Scores;
row["ParkRowSpan"] = "";
row["ModelRowSpan"] = "";
for (int i = 0; i <= days; i++)
{
var date = DateTime.Now.AddDays(i).ToString("yyyy-MM-dd");
string number = macList.Where(m => m.PlanTime == date && m.Equipmentmodel == last.Equipmentmodel).Sum(l => Convert.ToInt32(l.Number)).ToString();
row[DateTime.Now.AddDays(i).ToString("MM月dd日")] = (number == null ? "0" : number);
}
dt.Rows.Add(row);
#endregion
}
var modelGroup = Equipmentmodels.GroupBy(c => c.Equipmentmodel);
foreach (var model in modelGroup)
{
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["设备型号"].ToString() == model.Key)
{
dt.Rows[i]["ModelRowSpan"] = model.Count()+1;
var parkGroup = Equipmentmodels.Where(l => l.Equipmentmodel == model.Key).GroupBy(c => c.Park);
foreach (var it in parkGroup)
{
if (dt.Rows[i]["园区"].ToString() == it.Key)
{
dt.Rows[i]["ParkRowSpan"] = it.Count();
}
}
}
}
}
return dt;
}
public AntdComplexTableDto GetComplexData(string filter, int start,
int length)
{
string errorinfo = "";
var model = Get(start, length, "", "", filter, errorinfo);
var dt = Getfor(model);
var headers = new List();
if (dt != null && dt.Columns.Count > 0)
{
foreach (DataColumn item in dt.Columns)
{
if (item.ColumnName == "ParkRowSpan" || item.ColumnName == "ModelRowSpan")
continue;
headers.Add(new AntdColumn { ColName = item.ColumnName });
}
}
var antdData = new AntdComplexTableDto();
antdData.Columns = headers;
antdData.Datas = dt;
return antdData;
}
public AntdComplexTableDto GetComplexDataDetail(string filter, int start,
int length)
{
var macList = getDetail(filter).ToList();//获取当月预测数据
var en = new EquipmentnumberDal(CurrDb);
var Equipmentmodels = en.getEquipmentmodel(start, length, "", "", filter);//获取机型，园区，楼层
var dt = GetForDetail(macList, Equipmentmodels);
var headers = new List();
if (dt != null && dt.Columns.Count > 0)
{
foreach (DataColumn item in dt.Columns)
{
if (item.ColumnName == "ParkRowSpan" || item.ColumnName == "ModelRowSpan")
continue;
headers.Add(new AntdColumn { ColName = item.ColumnName });
}
}
var antdData = new AntdComplexTableDto();
antdData.Columns = headers;
antdData.Datas = dt;
return antdData;
}
}
}