everlight.eap/backend/DllEapDal/OFILM/ForecastDal.cs

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;
        }
        /// <summary>
        /// 设备需求预测
        /// </summary>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<ForecastDto> 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<ForecastDto>(sql);
            return models;
        }
        /// <summary>
        /// 设备需求预测总数
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        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<ForecastDto>(sql).FirstOrDefault();
            if (entities != null)
            {
                return entities.Count;
            }
            return 0;
        }

        public IEnumerable<ForecastDto> getMainPlan()
        {
            string sql = "SELECT DISTINCT planTime FROM productionplan  WHERE planTime like '%主计划%'";
            var model = CurrDb.FindList<ForecastDto>(sql);
            return model;
        }
        public IEnumerable<ForecastDto> getMaxPlan()
        {
            string sql = "SELECT max(STR_TO_DATE(planTime,'%Y-%m-%d') ) planTime FROM productionplan  WHERE planTime not like '%主计划%'";
            var model = CurrDb.FindList<ForecastDto>(sql);
            return model;
        }
        public IEnumerable<ForecastDto> getMinPlan()
        {
            string sql = "SELECT min(STR_TO_DATE(planTime,'%Y-%m-%d') ) planTime FROM productionplan  WHERE planTime not like '%主计划%'";
            var model = CurrDb.FindList<ForecastDto>(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;
        }
        /// <summary>
        /// 预测详情总览
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ForecastDto> 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<ForecastDto>(sql);
            return model;
        }
        public IEnumerable<ForecastDto> 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<ForecastDto>(sql);
            return model;
        }
        /// <summary>
        /// 预测详情总行数
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        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<ForecastDto>(sql).FirstOrDefault();
            if (entities != null)
            {
                return entities.Count;
            }
            return 0;
        }

        /// <summary>
        /// 获取园区
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<SelectDto<string>> 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<SelectDto<string>>(sql);
            return entities;
        }
        /// <summary>
        /// 获取机种下拉框
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<SelectDto<string>> GetMultipleMacTypeSelects(string filter)
        {
            var sql = $@"select distinct MachineType value,MachineType label
                            FROM WorkingProcedure where 1=1 {filter}";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }

        /// <summary>
        /// 获取楼层
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<SelectDto<string>> 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<SelectDto<string>>(sql);
            return entities;
        }
        /// <summary>
        /// 预测详情转DataTable
        /// </summary>
        /// <param name="roles"></param>
        /// <returns></returns>
        public DataTable Getfor(IEnumerable<ForecastDto> 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<string, IEnumerable<ForecastDto>> dic = new Dictionary<string, IEnumerable<ForecastDto>>();
                    foreach (var itemd in plan)
                    {
                        dic.Add(itemd.PlanTime, CurrDb.FindList<ForecastDto>(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;
        }
        /// <summary>
        /// 预测详情转DataTable
        /// </summary>
        /// <param name="macList"></param>
        /// <param name="Equipmentmodels"></param>
        /// <returns></returns>
        public DataTable GetForDetail(IEnumerable<ForecastDto> macList, IEnumerable<ForecastDto> 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<AntdColumn>();
            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<AntdColumn>();
            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;
        }
    }
}