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

using Cksoft.Data;
using DllEapEntity.OFILM;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DllEapEntity.Dtos;
using System.Data;
using DllEapEntity;
using Microsoft.Extensions.Logging;

namespace DllEapDal.OFILM
{
    public class DataCenterDal
    {
        private IDatabase CurrDb = null;
        ILogger<DataCenterDal> logger;
        public DataCenterDal(IDatabase db)
        {
            CurrDb = db;
        }

        public DataCenterDal( IDatabase db, ILogger<DataCenterDal> logg)
        {
            CurrDb = db;
            this.logger = logg;
        }

        #region 设备及维修费用
        public IEnumerable<ChartEntity> GetMacCount()
        {
            string sql = @"select FACTORY as Name,count(1) as Value from ccmrpt.CCMMES_FIXED_ASSETS_V group by FACTORY order by FACTORY";
            return CurrDb.FindList<ChartEntity>(sql);
        }

        public IEnumerable<ChartEntity> GetMacCharge(int type = 1)
        {
            string filter = type == 1 ? " stock<0" : "stock>0";
            filter = filter + " and PART_TYPE='OR' ";
            DateTime dt = DateTime.Now;
            string end = dt.ToString("yyyy-MM-dd 23:59:59");
            string start = dt.AddDays(-7).ToString("yyyy-MM-dd 00:00:00");
            filter = filter + $"AND CREATED between to_date('{start}','yyyy-mm-dd hh24:mi:ss') and to_date('{end}','yyyy-mm-dd hh24:mi:ss')";
            string sql = $@"select FACTORY as Name,sum(stock*price) as Value from ccmrpt.CCMMES_SPARE_PRICE_V where {filter} group by FACTORY order by FACTORY";
            return CurrDb.FindList<ChartEntity>(sql);
        }

        public IEnumerable<ChartEntity> GetMacChargeNew(string filter)
        {

            string sql = $@"	SELECT
		                            t.department Name,
		                            nvl(sum( t.stock * t.price ),0) AS Value 
	                            FROM
		                            ccmrpt.CCMMES_SPARE_PRICE_V t
	                            WHERE
		                            1 = 1 
		                            {filter}
		                            AND t.PART_TYPE = 'OR' 
		                            and t.department is not null
	                            GROUP BY
		                            t.department
	                            ORDER BY
		                            department  ";
            return CurrDb.FindList<ChartEntity>(sql);
        }

        public IEnumerable<ChartEntity> GetMacChargeUser(string filter)
        {

            string sql = $@"		SELECT
		                             trunc(t.CREATED, 'mm') Mon,
		                            t.department Name,
		                            nvl(sum( t.stock * t.price ),0) AS Value 
	                            FROM
		                            ccmrpt.CCMMES_SPARE_PRICE_V t
	                            WHERE
		                            1 = 1 
                            {filter}
		                            AND t.PART_TYPE = 'OR' 
		                            and t.department is not null
	                            GROUP BY
		                             trunc(t.CREATED, 'mm'),
		                            t.department
	                            ORDER BY
		                            department , trunc(t.CREATED, 'mm')  ";
            return CurrDb.FindList<ChartEntity>(sql);
        }

        public IEnumerable<ChartEntity> GetMacChargeStock(string filter)
        {

            string sql = $@"	SELECT
                                -- 	trunc(a.CREATED, 'dd') mon,

		                                (case when (a.factory = '2号园区' and a.warehouse in ('1F-COB', '1F-EOL')) then '设备一课'
                                                    when (a.factory = '2号园区' and a.warehouse in ('2F-COB', '2F-EOL')) then '设备二课'
                                                    when (a.factory = '2号园区' and a.warehouse in ('3F-COB', '3F-EOL')) then '设备三课'
                                                    when (a.factory = '未来城' and a.warehouse in ('1F-COB', '2F-EOL', '3F-EOL')) then '设备四课'
                                                    when (a.factory = '3号园区' and a.warehouse in ('1F-COB', '1F-EOL', '2F-EOL')) then '设备五课'
                                                    when (a.factory = '未来城' and a.warehouse in ('中央仓库')) then '中央仓库' else null end) Name,
		                                nvl(sum( a.stock * a.price ),0) AS Value 
	                                FROM
		                                ccmrpt.CCMMES_SPARE_PRICE_V a
	                                WHERE
		                                1 = 1 
                                {filter}
                                -- 		 and a.department is not null
	                                GROUP BY
                                --  trunc(a.CREATED, 'dd'),
		                                (case when (a.factory = '2号园区' and a.warehouse in ('1F-COB', '1F-EOL')) then '设备一课'
                                                    when (a.factory = '2号园区' and a.warehouse in ('2F-COB', '2F-EOL')) then '设备二课'
                                                    when (a.factory = '2号园区' and a.warehouse in ('3F-COB', '3F-EOL')) then '设备三课'
                                                    when (a.factory = '未来城' and a.warehouse in ('1F-COB', '2F-EOL', '3F-EOL')) then '设备四课'
                                                    when (a.factory = '3号园区' and a.warehouse in ('1F-COB', '1F-EOL', '2F-EOL')) then '设备五课'
                                                    when (a.factory = '未来城' and a.warehouse in ('中央仓库')) then '中央仓库' else null end) ";
            return CurrDb.FindList<ChartEntity>(sql);
        }
        public IEnumerable<ChartEntity> GetMacChargeStockMonth(string starttime, string endtime)
        {
            DateTime dt1 = Convert.ToDateTime(endtime);

            DateTime dt2 = Convert.ToDateTime(starttime);

            int Year = dt2.Year - dt1.Year;

            int Month = (dt1.Year - dt2.Year) * 12 + (dt1.Month - dt2.Month);
            string sql = $@"	SELECT
                                '{dt1.AddMonths(-1).ToString("yyyy-MM-01")}' mon,
		                        (case when (a.factory = '2号园区' and a.warehouse in ('1F-COB', '1F-EOL')) then '设备一课'
                                            when (a.factory = '2号园区' and a.warehouse in ('2F-COB', '2F-EOL')) then '设备二课'
                                            when (a.factory = '2号园区' and a.warehouse in ('3F-COB', '3F-EOL')) then '设备三课'
                                            when (a.factory = '未来城' and a.warehouse in ('1F-COB', '2F-EOL', '3F-EOL')) then '设备四课'
                                            when (a.factory = '3号园区' and a.warehouse in ('1F-COB', '1F-EOL', '2F-EOL')) then '设备五课'
                                            when (a.factory = '未来城' and a.warehouse in ('中央仓库')) then '中央仓库' else null end) Name,
		                        nvl(sum( a.stock * a.price ),0) AS Value 
	                        FROM
		                        ccmrpt.CCMMES_SPARE_PRICE_V a
	                        WHERE
		                        1 = 1 
                        AND a.CREATED < to_date( '{dt1.AddMonths(1).ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-mm-dd hh24:mi:ss' ) 
                            GROUP BY
		(case when (a.factory = '2号园区' and a.warehouse in ('1F-COB', '1F-EOL')) then '设备一课'
                    when (a.factory = '2号园区' and a.warehouse in ('2F-COB', '2F-EOL')) then '设备二课'
                    when (a.factory = '2号园区' and a.warehouse in ('3F-COB', '3F-EOL')) then '设备三课'
                    when (a.factory = '未来城' and a.warehouse in ('1F-COB', '2F-EOL', '3F-EOL')) then '设备四课'
                    when (a.factory = '3号园区' and a.warehouse in ('1F-COB', '1F-EOL', '2F-EOL')) then '设备五课'
                    when (a.factory = '未来城' and a.warehouse in ('中央仓库')) then '中央仓库' else null end)                                ";
            for (int i = 1; i < Month; i++)
            {
                sql += $@" union all	SELECT
                            '{dt1.AddMonths(-i - 1).ToString("yyyy-MM-01")}' mon,
		                    (case when (a.factory = '2号园区' and a.warehouse in ('1F-COB', '1F-EOL')) then '设备一课'
                                        when (a.factory = '2号园区' and a.warehouse in ('2F-COB', '2F-EOL')) then '设备二课'
                                        when (a.factory = '2号园区' and a.warehouse in ('3F-COB', '3F-EOL')) then '设备三课'
                                        when (a.factory = '未来城' and a.warehouse in ('1F-COB', '2F-EOL', '3F-EOL')) then '设备四课'
                                        when (a.factory = '3号园区' and a.warehouse in ('1F-COB', '1F-EOL', '2F-EOL')) then '设备五课'
                                        when (a.factory = '未来城' and a.warehouse in ('中央仓库')) then '中央仓库' else null end) Name,
		                    nvl(sum( a.stock * a.price ),0) AS Value 
	                    FROM
		                    ccmrpt.CCMMES_SPARE_PRICE_V a
	                    WHERE
		                    1 = 1 
                    AND a.CREATED < to_date( '{dt1.AddMonths(-i + 1).ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-mm-dd hh24:mi:ss' ) 
GROUP BY

		(case when (a.factory = '2号园区' and a.warehouse in ('1F-COB', '1F-EOL')) then '设备一课'
                    when (a.factory = '2号园区' and a.warehouse in ('2F-COB', '2F-EOL')) then '设备二课'
                    when (a.factory = '2号园区' and a.warehouse in ('3F-COB', '3F-EOL')) then '设备三课'
                    when (a.factory = '未来城' and a.warehouse in ('1F-COB', '2F-EOL', '3F-EOL')) then '设备四课'
                    when (a.factory = '3号园区' and a.warehouse in ('1F-COB', '1F-EOL', '2F-EOL')) then '设备五课'
                    when (a.factory = '未来城' and a.warehouse in ('中央仓库')) then '中央仓库' else null end)
                                                        ";
            }
            return CurrDb.FindList<ChartEntity>(sql);
        }

        public IEnumerable<ChartEntity> GetMacChargeFirst(int type = 1)
        {
            string filter = type == 1 ? " stock<0" : "stock>0";
            string sql = $@"select FACTORY as Name,sum(stock*price) as Value from ccmrpt.CCMMES_SPARE_PRICE_V where {filter} group by FACTORY order by FACTORY";
            return CurrDb.FindList<ChartEntity>(sql);
        }

        public IEnumerable<MacEntity> GetMacList(int start, int end, string filter, string sort, string order, out int total, ref string errorinfo)
        {
            string sql = $@"select  * from (select rownum as row_number,FACTORY as Factory,LOCATION as Floor,ASSET_NAME as MacName,
                            ASSET_MODEL as MacModel,ASSET_CODE as AssetCode,EQP_ID as MacCode,DAOCHANG_DATE as EnterDate,
                            YANSHOU_DATE as CheckDate,YOUXIAO_DATE as ValidDate,ME_NAME as MEName,ME_NO as MENo,WORKSHOP_NAME  as WorkShopName,WORKSHOP_NO as WorkShopNo
                            from ccmrpt.CCMMES_FIXED_ASSETS_V 
                            where 1=1 {filter}   order by {sort} {order}) t where t.row_number>={start} and t.row_number<{end}";

            string countSql = $"select count(1) from ccmrpt.CCMMES_FIXED_ASSETS_V where 1=1 {filter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            return CurrDb.FindList<MacEntity>(sql);
        }

        public IEnumerable<MacChargeEntity> GetMacChargeList(int start, int end, string filter, string sort, string order, out int total, int type, ref string errorinfo)
        {
            filter += (type == 1 ? " and stock<0" : " and stock>0");
            filter = filter + " and PART_TYPE='OR' ";
            string sql = $@"select rownum as row_number,t.* from(select FACTORY as Factory,warehouse as Floor,sum(stock*price) as StockCharge  from ccmrpt.CCMMES_SPARE_PRICE_V 
                            where 1=1 {filter}
                            group by FACTORY,warehouse 
                            order by {sort} {order}) t where  rownum<{end} and rownum>={start}";

            string countSql = $"select count(1) from(select FACTORY,warehouse from ccmrpt.CCMMES_SPARE_PRICE_V where 1=1 {filter} group by FACTORY,warehouse)";
            //string sql = $@" select  * from (select rownum as row_number,FACTORY as Factory,warehouse as Floor,ITEM as Item,ITEM_DESC as ItemDesc,
            //                     CREATED as CreateDate,stock*price as UseCharge
            //                     from ccmrpt.CCMMES_SPARE_PRICE_V 
            //                    where  stock*price>0 {filter} and rownum<{end}  order by {sort} {order}) t  
            //                     where row_number>={start}";
            //string countSql = $"select count(1) from ccmrpt.CCMMES_SPARE_PRICE_V where stock*price>0 {filter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            return CurrDb.FindList<MacChargeEntity>(sql);
        }
        public IEnumerable<MacChargeEntity> GetMacChargeUserList(int start, int end, string filter, string sort, string order, out int total, int type, ref string errorinfo)
        {

            filter = filter + " and PART_TYPE='OR' ";
            string sql = $@"select rownum as row_number,t.* from(SELECT
		                    t.department Floor,
		                    nvl(sum( t.stock * t.price ),0) AS UseCharge 
	                    FROM
		                    ccmrpt.CCMMES_SPARE_PRICE_V t
	                    WHERE
		                    1 = 1 
		                    {filter}
		                    and t.department is not null
	                    GROUP BY
		                    t.department
	                    ORDER BY
		                    department ASC ) t where  rownum<{end} and rownum>={start}";

            string countSql = $"select count(1) from(select FACTORY,warehouse from ccmrpt.CCMMES_SPARE_PRICE_V where 1=1 {filter} group by FACTORY,warehouse)";
            //string sql = $@" select  * from (select rownum as row_number,FACTORY as Factory,warehouse as Floor,ITEM as Item,ITEM_DESC as ItemDesc,
            //                     CREATED as CreateDate,stock*price as UseCharge
            //                     from ccmrpt.CCMMES_SPARE_PRICE_V 
            //                    where  stock*price>0 {filter} and rownum<{end}  order by {sort} {order}) t  
            //                     where row_number>={start}";
            //string countSql = $"select count(1) from ccmrpt.CCMMES_SPARE_PRICE_V where stock*price>0 {filter}";
            //  total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            var models = CurrDb.FindList<MacChargeEntity>(sql);
            total = models.Count();
            return models;
        }


        #endregion

        #region 设备状态
        public IEnumerable<MacStatusEntity> GetMacStatusList()
        {
            string sql = @"SELECT a.id AS FactoryId,a.FName AS Factory,c.id AS FloorId,c.fname AS Floor,
                            ms.StatusID,s.FName as StatusName,d.id as PlantId,d.FName as Plant,
                            concat('rgb(',s.red,',',s.green,',',s.blue,')') as Color
                            FROM machine m
                            inner JOIN factoryregion a ON a.Id=m.FactoryId
                            INNER JOIN factoryregion b ON b.Id=m.RegionId
                            INNER JOIN factoryregion c ON c.Id=b.ParentId
                            INNER JOIN factoryregion d ON d.Id=c.ParentId
                            inner join macstatus01 ms ON ms.MacCode=m.FCode
                            inner JOIN standardstatus s ON s.StatusVal=ms.StatusID
                            WHERE FactoryId!=154";
            return CurrDb.FindList<MacStatusEntity>(sql);
        }
        #endregion

        #region 设备MTBA
        public IEnumerable<MacMTBAEntity> GetMacMTBA()
        {
            string sql = $@"SELECT me.StartTime,a.id AS FactoryId,a.FName AS Factory,d.id as PlantId,d.FName as Plant
,me.EventCode,SUM(me.timelen) AS TimeSpan,sum(me.count) AS Count
                            from ( SELECT sum(timespan) as timelen,count(1) as count,mcacode,eventcode,DATE_FORMAT(StartTime,'%Y-%m-%d') as StartTime FROM  mcaeventstatisticbyday e
                            WHERE e.StartTime>='{DateTime.Today.AddDays(-6).ToString("yyyy-MM-dd")}'
                            group by mcacode,eventcode,DATE_FORMAT(StartTime,'%Y-%m-%d')) me
                            inner join  machine mac on mac.FCode=me.McaCode
                            inner JOIN factoryregion a ON a.Id=mac.FactoryId
                            INNER JOIN factoryregion b ON b.Id=mac.RegionId
                            INNER JOIN factoryregion c ON c.Id=b.ParentId
                            INNER JOIN factoryregion d ON d.Id=c.ParentId
                            WHERE mac.FactoryId!=154
                            GROUP BY  a.id,a.FName,d.id,d.FName,me.EventCode,me.StartTime";
            return CurrDb.FindList<MacMTBAEntity>(sql);
        }
        public List<MtbaDay> GetMtbaDays(DateTime startTime, DateTime endTime)
        {
            List<MtbaDay> mtbadays = new List<MtbaDay>();
            int days = endTime.Subtract(startTime).Days + 1;
            for (int i = 0; i <= days; i++)
            {
                MtbaDay mtbaDay = new MtbaDay();
                if (i == 0)
                {
                    if (startTime < Convert.ToDateTime(startTime.ToString("yyyy-MM-dd") + " 08:30"))
                    {
                        mtbaDay.StartTime = startTime;
                        mtbaDay.EndTime = Convert.ToDateTime(startTime.ToString("yyyy-MM-dd") + " 08:30");
                        mtbaDay.DayTime = startTime.AddDays(-1);
                        if (mtbaDay.EndTime > endTime)
                        {
                            mtbaDay.EndTime = endTime;
                            mtbadays.Add(mtbaDay);
                            break;
                        }
                        mtbadays.Add(mtbaDay);
                        MtbaDay mtbaDay2 = new MtbaDay();
                        mtbaDay2.StartTime = Convert.ToDateTime(startTime.ToString("yyyy-MM-dd") + " 08:30");
                        mtbaDay2.EndTime = Convert.ToDateTime(startTime.AddDays(1).ToString("yyyy-MM-dd") + " 08:30");
                        mtbaDay2.DayTime = startTime;
                        if (mtbaDay2.EndTime > endTime)
                        {
                            mtbaDay2.EndTime = endTime;
                            mtbadays.Add(mtbaDay2);
                            break;
                        }
                        mtbadays.Add(mtbaDay2);
                    }
                    else
                    {
                        mtbaDay.StartTime = startTime;
                        mtbaDay.EndTime = Convert.ToDateTime(startTime.AddDays(1).ToString("yyyy-MM-dd") + " 08:30");
                        mtbaDay.DayTime = startTime;
                        if (mtbaDay.EndTime >= endTime)
                        {
                            mtbaDay.EndTime = endTime;
                            mtbadays.Add(mtbaDay);
                            break;
                        }
                        mtbadays.Add(mtbaDay);
                    }
                }
                else
                {
                    mtbaDay.StartTime = Convert.ToDateTime(startTime.AddDays(i).ToString("yyyy-MM-dd") + " 08:30");
                    mtbaDay.EndTime = Convert.ToDateTime(startTime.AddDays(i + 1).ToString("yyyy-MM-dd") + " 08:30");
                    mtbaDay.DayTime = startTime.AddDays(i);
                    if (mtbaDay.EndTime >= endTime)
                    {
                        mtbaDay.EndTime = endTime;
                        mtbadays.Add(mtbaDay);
                        break;
                    }
                    mtbadays.Add(mtbaDay);
                }
            }
            return mtbadays;
        }
        public string CheckTable(DateTime startTime, DateTime endTime, string filter, string subfilter, string sub,ref List<MtbaDay> mtbaDays,string mtbatemp)
        {
            DelTmp(mtbatemp);
            string tablename = GetTableName();
            //将数据添加到临时表
            // 获取时间区间集合
            var mtbadays = GetMtbaDays(startTime, endTime);
            mtbaDays = mtbadays;
            foreach (var items in mtbadays)
            {
                logger.LogError("开始获取mtba临时数据");
                // 获取当天mtba数据
                var mtbaListdetail = GetMacMTBADetail(items.StartTime, items.EndTime, filter, subfilter, sub).ToList();
                logger.LogError("完成获取mtba临时数据");
                if (mtbaListdetail != null)
                {
                    logger.LogError("开始新增mtba临时数据");
                    // 将数据插入临时表
                    InsertMtbaTempory(mtbaListdetail, items.DayTime, tablename);
                    logger.LogError("完成新增mtba临时数据");
                }
            }
            return tablename;
        }
        public string GetTableName()
        {
            string tablename = "mtbatemp" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
            string sql = $@"create  table {tablename}(
                                                FactoryId int ,
                                                Factory VARCHAR(50),
                                                PlantId INT,
                                                Plant VARCHAR(50),
                                                EventCode VARCHAR(300),
                                                TimeSpan DECIMAL,
                                                Count int,
                                                CountOne int,
                                                CountTwo int,
                                                StartTime datetime,
                                                Floor VARCHAR(50),
                                                ModelCode VARCHAR(50),
                                                MacName VARCHAR(50),
                                                MacCode VARCHAR(50),
                                                  name varchar(20) 
                                                )Engine = InnoDB default charset utf8; ";
            CurrDb.ExecuteBySql(sql);
            
            return tablename;
        }
        public void DelTmp(string mtbatemp)
        {
            try
            {
               int mtba= Convert.ToInt32(mtbatemp);
                string sql = $@"SELECT
	                            TABLE_name 
                            FROM
	                            INFORMATION_SCHEMA.TABLES 
                            WHERE
	                            TABLE_SCHEMA = 'eap' 
	                            AND TABLE_NAME like 'mtbatemp%'
	                            AND Create_Time <(NOW() - INTERVAL {mtba} HOUR);";
                var models = CurrDb.FindList<string>(sql).ToList();
                models.Remove("mtbacode");
                if (models.Count() > 0)
                {
                    string tablename = String.Join(",", models);
                    string delsql = $@" drop table {tablename};";
                    CurrDb.ExecuteBySql(delsql);
                }
            }
            catch
            {
            }
        }
        public IEnumerable<MacMTBAEntity> GetMacMTBADetail(DateTime start, DateTime end, string filter, string subfilter, string sub)
        {
            List<MacMTBAEntity> models = new List<MacMTBAEntity>();
            try
            {
                var day = DateTime.Now.Subtract(start).Days;
                string sqls = "";
                string dbExist = "";
                string tableExist = "";
                //一个月内
                if (day <= 30)
                {
                    sqls = $@"SELECT
		                        sum( timespan ) AS timelen,
		                        count( 1 ) AS count,
		                        mcacode,
		                        AlarmCode,
		                        eventcode,
		                        DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime 
	                        FROM
		                        mcaeventstatisticbyday e 
	                        WHERE
		                        StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' {subfilter} 
	                        GROUP BY
		                        mcacode,
		                        eventcode,AlarmCode,
		                        DATE_FORMAT( StartTime, '%Y-%m-%d' ) ";
                }
                else
                {
                    string table = "";
                    // 判断时间是否一个月内数据 eap
                    string year = start.ToString("yyyy");
                    string month = start.ToString("MM");
                    //
                    if (start.ToString("yyyy") == end.ToString("yyyy"))
                    {
                        if (start.ToString("MM") == end.ToString("MM"))
                        {
                            table = $" eap{year}.mcaeventstatisticbyday{month}";
                            dbExist = CurrDb.FindList<string>($@"select TABLE_NAME from INFORMATION_SCHEMA.TABLES where 
TABLE_SCHEMA = 'eap{year}' and TABLE_NAME = 'mcaeventstatisticbyday{month}'; ").FirstOrDefault();
                            if (string.IsNullOrEmpty(dbExist))
                            {
                                return models;
                            }
                            sqls = $@"SELECT
		sum( timespan ) AS timelen,
		count( 1 ) AS count,
		mcacode,
		AlarmCode,
		eventcode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime 
	FROM
		{table} e 
	WHERE
		StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' {subfilter} 
	GROUP BY
		mcacode,
		eventcode,AlarmCode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) ";
                        }
                        else
                        {
                            dbExist = CurrDb.FindList<string>($@"select TABLE_NAME from INFORMATION_SCHEMA.TABLES where 
TABLE_SCHEMA = 'eap{year}' and TABLE_NAME = 'mcaeventstatisticbyday{month}'; ").FirstOrDefault();
                            if (string.IsNullOrEmpty(dbExist))
                            {
                                return models;
                            }
                            sqls = $@"SELECT
		sum( timespan ) AS timelen,
		count( 1 ) AS count,
		mcacode,
		AlarmCode,
		eventcode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime 
	FROM
		eap{year}.mcaeventstatisticbyday{month} e 
	WHERE
		StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' {subfilter} 
	GROUP BY
		mcacode,
		eventcode,AlarmCode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) ";
                            dbExist = CurrDb.FindList<string>($@"select TABLE_NAME from INFORMATION_SCHEMA.TABLES where 
TABLE_SCHEMA = 'eap{year}' and TABLE_NAME = 'mcaeventstatisticbyday{end.ToString("MM")}'; ").FirstOrDefault();
                            if (!string.IsNullOrEmpty(dbExist))
                            {
                                sqls += $@"union all
SELECT

        sum(timespan) AS timelen,
      count( 1 ) AS count,
      mcacode,
      AlarmCode,
      eventcode,
      DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime

    FROM
        eap{ year}.mcaeventstatisticbyday{ end.ToString("MM")}
                            e
WHERE

        StartTime >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' { subfilter}
                            GROUP BY

        mcacode,
		eventcode,AlarmCode,
		DATE_FORMAT(StartTime, '%Y-%m-%d')
";
                            }
                        }
                    }
                    else
                    {
                        dbExist = CurrDb.FindList<string>($@"select TABLE_NAME from INFORMATION_SCHEMA.TABLES where 
TABLE_SCHEMA = 'eap{start.ToString("yyyy")}' and TABLE_NAME = 'mcaeventstatisticbyday{start.ToString("MM")}'; ").FirstOrDefault();
                        if (string.IsNullOrEmpty(dbExist))
                        {
                            return models;
                        }
                        sqls = $@"SELECT
		sum( timespan ) AS timelen,
		count( 1 ) AS count,
		mcacode,
		AlarmCode,
		eventcode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime 
	FROM
		eap{start.ToString("yyyy")}.mcaeventstatisticbyday{start.ToString("MM")} e 
	WHERE
		StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' {subfilter} 
	GROUP BY
		mcacode,
		eventcode,AlarmCode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) ";
                        dbExist = CurrDb.FindList<string>($@"select TABLE_NAME from INFORMATION_SCHEMA.TABLES where 
TABLE_SCHEMA = 'eap{end.ToString("yyyy")}' and TABLE_NAME = 'mcaeventstatisticbyday{end.ToString("MM")}'; ").FirstOrDefault();
                        if (!string.IsNullOrEmpty(dbExist))
                        {
                            sqls += $@" union all
SELECT
		sum( timespan ) AS timelen,
		count( 1 ) AS count,
		mcacode,
		AlarmCode,
		eventcode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime 
	FROM
		eap{end.ToString("yyyy")}.mcaeventstatisticbyday{end.ToString("MM")} e 
	WHERE
		StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' {subfilter} 
	GROUP BY
		mcacode,
		eventcode,AlarmCode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' )
";
                        }
                    }
                }
                // 根据日期把分库分表的数据查询到临时表
                // 将临时表的数据和其他表进行关联

                #region
                //string sql = $@"SELECT me.StartTime,i.id AS FactoryId,i.FName AS Factory,k.id AS FloorId,k.fname AS Floor,
                //                a.FCode AS MacCode,a.FName AS MacName,d.FCode AS ModelCode,me.EventCode,me.timelen AS TimeSpan,me.count AS Count
                //                from ( SELECT sum(timespan) as timelen,count(1) as count,mcacode,eventcode,DATE_FORMAT(StartTime,'%Y-%m-%d') as StartTime FROM  mcaeventstatisticbyday e
                //                WHERE StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'
                //                group by mcacode,eventcode,DATE_FORMAT(StartTime,'%Y-%m-%d') ) me
                //                inner join  machine a on a.FCode=me.McaCode
                //                INNER JOIN macmodel d ON d.ID=a.MModeID
                //                inner JOIN factoryregion i ON i.Id=a.FactoryId 
                //                INNER JOIN factoryregion j ON j.Id=a.RegionId
                //                INNER JOIN factoryregion k ON k.Id=j.ParentId 
                //                INNER JOIN factoryregion l ON l.id=k.ParentId 
                //                WHERE (i.id<154 OR i.id>154) {filter} order by me.count desc";
                #endregion
                string sql = $@"SELECT IFNULL(StartTime,'{start.ToString("yyyy-MM-dd")}') StartTime,FactoryId,Factory,FloorId,Floor,MacCode,MacName,macnumber,ModelCode,EventCode,
sum(TimeSpan) TimeSpan,SUM(Count) Count,
sum( CASE  WHEN faulttype=1 THEN Count WHEN faulttype='' THEN  Count WHEN faulttype IS NULL THEN Count ELSE 0 END ) CountOne,
sum( CASE faulttype WHEN 2 THEN Count ELSE 0 END ) CountTwo 
FROM(
SELECT
	me.StartTime,
	i.id AS FactoryId,
	i.FName AS Factory,
	k.id AS FloorId,
	k.fname AS Floor,
	a.FCode AS MacCode,
	a.FName AS MacName,
	d.FCode AS ModelCode,
    machinenumber.macnumber macnumber,
	me.EventCode,
	me.timelen AS TimeSpan,
	me.count AS Count 
 ,p.faulttype
FROM
	(
	{sqls}
	) me
	right  JOIN machine a ON a.FCode = me.McaCode
	INNER JOIN macmodel d ON d.ID = a.MModeID
  	left JOIN mtbacode p on p.MModeID = d.id and me.AlarmCode = p.AlarmCode
	left JOIN factoryregion i ON i.Id = a.FactoryId
	left JOIN factoryregion j ON j.Id = a.RegionId
	left JOIN factoryregion k ON k.Id = j.ParentId
	left JOIN factoryregion l ON l.id = k.ParentId 
    left  join machinenumber  on a.id=machinenumber.macid   
WHERE
	1=1 {filter} {sub}
ORDER BY
	me.count DESC
	) qq
	GROUP BY StartTime,FactoryId,Factory,FloorId,Floor,MacCode,MacName,macnumber,ModelCode,EventCode
";
                models = CurrDb.FindList<MacMTBAEntity>(sql).ToList();
                logger.LogError(sql) ;
                return models;
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
                return models;
            }
        }
        public bool InsertMtbaTempory(IEnumerable<MacMTBAEntity> mtbaListdetail, DateTime DayTime, string tablename)
        {
            try
            {
               
                StringBuilder stringBuilder = new StringBuilder();
                string insert_sql = $@"insert into {tablename} (FactoryId,Factory,Floor,MacName,ModelCode,EventCode,TimeSpan,Count,CountOne,CountTwo,StartTime,MacCode)values";
                for (int i = 0; i < mtbaListdetail.Count(); i++)
                {
                    var item = mtbaListdetail.ToList()[i];
                    if (mtbaListdetail.Count() - 1 == i)
                    {

                        string value_sql = $@"({item.FactoryId},'{item.Factory}','{item.Floor}','{item.MacName}','{item.ModelCode}','{item.EventCode}',{item.TimeSpan},{item.Count},{item.CountOne},{item.CountTwo},'{DayTime.ToString("yyyy-MM-dd")}','{item.MacCode}') ";
                        stringBuilder.AppendLine(value_sql);
                    }
                    else
                    {
                        string value_sql = $@"({item.FactoryId},'{item.Factory}','{item.Floor}','{item.MacName}','{item.ModelCode}','{item.EventCode}',{item.TimeSpan},{item.Count},{item.CountOne},{item.CountTwo},'{DayTime.ToString("yyyy-MM-dd")}','{item.MacCode}'), ";
                        stringBuilder.AppendLine(value_sql);
                    }
                }
                //foreach (var item in mtbaListdetail)
                //{
                //    string value_sql=$@"({item.FactoryId},'{item.Factory}','{item.Floor}','{item.MacName}','{item.ModelCode}','{item.EventCode}',{item.TimeSpan},{item.Count},{item.CountOne},{item.CountTwo},'{item.StartTime}','{item.MacCode}'), ";
                //    stringBuilder.AppendLine(value_sql);
                //}
                if (!string.IsNullOrEmpty(stringBuilder.ToString()))
                {
                    //logger.LogError(insert_sql + stringBuilder.ToString());
                    CurrDb.ExecuteBySql(insert_sql + stringBuilder.ToString());
                }
                return true;
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
                return false;
            }
        }

        public IEnumerable<MacMTBAEntity> GetMacMTBADetail(string tablename)
        {

            string sql = $@"SELECT FactoryId,
Factory ,
PlantId ,
Plant ,
EventCode ,
TimeSpan ,
Count ,
CountOne ,
CountTwo ,
StartTime ,
Floor ,
ModelCode ,
MacName ,
MacCode  from {tablename} order by Count desc";
            return CurrDb.FindList<MacMTBAEntity>(sql);
        }
        public IEnumerable<MacMTBAEntity> GetMacMTBADetailTotal(string tablename,string subfilter)
        {
            string sql = $@"SELECT
                            EventCode ,
                            sum(TimeSpan) TimeSpan ,
                            sum(Count) Count ,
                            StartTime from
                          (SELECT *  from {tablename} where 1=1 {subfilter}) b group by EventCode,StartTime 
order by 	sum( TimeSpan ) / ( CASE WHEN sum( Count ) = 0 THEN 1 ELSE sum( Count ) END ) / 60  ";
            return CurrDb.FindList<MacMTBAEntity>(sql);
        }
        public IEnumerable<MacMTBAEntity> GetMacMTBADetailTotal(DateTime start, DateTime end, string filter, string subfilter, string sub)
        {
            string sql = $@"SELECT StartTime,EventCode,
sum(TimeSpan) TimeSpan,SUM(Count) Count,
sum( CASE  WHEN faulttype=1 THEN Count WHEN faulttype='' THEN  Count WHEN faulttype IS NULL THEN Count ELSE 0 END ) CountOne,
sum( CASE faulttype WHEN 2 THEN Count ELSE 0 END ) CountTwo 
FROM(
SELECT
	me.StartTime,
	i.id AS FactoryId,
	i.FName AS Factory,
	k.id AS FloorId,
	k.fname AS Floor,
	a.FCode AS MacCode,
	a.FName AS MacName,
	d.FCode AS ModelCode,
	me.EventCode,
	me.timelen AS TimeSpan,
	me.count AS Count 
 ,p.faulttype
FROM
	(
	SELECT
		sum( timespan ) AS timelen,
		count( 1 ) AS count,
		mcacode,
		AlarmCode,
		eventcode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) AS StartTime 
	FROM
		mcaeventstatisticbyday e 
	WHERE
		StartTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and StartTime<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' {subfilter} 
	GROUP BY
		mcacode,
		eventcode,AlarmCode,
		DATE_FORMAT( StartTime, '%Y-%m-%d' ) 
	) me
	INNER JOIN machine a ON a.FCode = me.McaCode
	INNER JOIN macmodel d ON d.ID = a.MModeID
  	left JOIN mtbacode p on p.MModeID = d.id and me.AlarmCode = p.AlarmCode
	INNER JOIN factoryregion i ON i.Id = a.FactoryId
	INNER JOIN factoryregion j ON j.Id = a.RegionId
	INNER JOIN factoryregion k ON k.Id = j.ParentId
	INNER JOIN factoryregion l ON l.id = k.ParentId 
WHERE
	( i.id < 154 OR i.id > 154 ) {filter} {sub}
ORDER BY
	me.count DESC
	) qq
	GROUP BY StartTime,EventCode
";
            return CurrDb.FindList<MacMTBAEntity>(sql);
        }

        public IEnumerable<Machine> Get(DateTime startTime, DateTime endTime, int start, int length, string order, string sort, string filter, string sub, string tablename, string errorinfo)
        {
            #region
            //    string sql = $@"SELECT
            //                     c.FCode,
            //FactoryName,
            //PlantName,
            //FloorName,
            //MModeCode,
            //FName,
            //                     c.timespan / c.count / 60 count,
            //                     timespan 								
            //                    FROM						
            //                     (
            //                     SELECT
            //                      a.FCode,
            //	i.FName FactoryName,
            //	l.FName PlantName,
            //	k.FName FloorName,
            //	d.FCode MModeCode,
            //	a.FName FName,						
            //                      ifnull( b.timespan, '0' ) timespan,
            //                      ifnull( b.count, '0' ) count 
            //                     FROM
            //                      machine a
            //                      LEFT JOIN (
            //                      SELECT
            //                       mcacode,
            //                       sum( CASE WHEN ( EventCode = 'C00007' ) THEN 1 ELSE 0 END ) AS count,
            //                       sum( CASE WHEN EventCode = 'C00012' THEN timespan ELSE 0 END ) AS timespan 
            //                      FROM
            //                       mcaeventstatisticbyday 
            //                      WHERE
            //                       starttime >= '{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and  starttime<'{endTime.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'
            //                      GROUP BY
            //                       mcacode 
            //                      ) b ON a.fcode = b.mcacode 
            //	left outer join macmodel d on a.MModeID =d.id

            //	left outer join supplier e on d.SupplierID =e.id
            //	left outer join mactprocess f on a.id =f.macid
            //	left outer join tprocess g on f.PCode =g.FCode
            //                            left JOIN factoryregion i ON i.Id=a.FactoryId 
            //                            left JOIN factoryregion j ON j.Id=a.RegionId
            //                            left JOIN factoryregion k ON k.Id=j.ParentId 
            //                            left JOIN factoryregion l ON l.id=k.ParentId 
            //                            where 1=1 {filter}
            //                     ) c 
            //                    ORDER BY
            //                     c.timespan / (case when sum( c.count)=0 then 1 else sum( c.count) end) DESC limit {(start)},{length}";
            #endregion

            #region
            //            string sql = $@"
            //SELECT
            //	c.FCode,
            //	FactoryName,
            //	PlantName,
            //	FloorName,
            //	MModeCode,
            //	FName,
            // sum(	c.timespan) /(case when sum( c.count)=0 then 1 else sum( c.count) end) / 60 count,
            //SUM(c.count) allcount,
            //SUM(TimeSpan) TimeSpan,
            //sum( CASE  WHEN faulttype=1 THEN Count WHEN faulttype='' THEN  Count WHEN faulttype IS NULL THEN Count ELSE 0 END ) CountOne,
            //sum( CASE faulttype WHEN 2 THEN Count ELSE 0 END ) CountTwo

            //FROM
            //	(
            //	SELECT
            //		a.FCode,
            //		i.FName FactoryName,
            //		l.FName PlantName,
            //		k.FName FloorName,
            //		d.FCode MModeCode,
            //		a.FName FName,
            //		ifnull( b.timespan, '0' ) timespan,
            //		ifnull( b.count, '0' ) count 
            //		,p.faulttype
            //	FROM
            //		machine a
            //		LEFT JOIN (
            //		SELECT
            //			mcacode,AlarmCode,
            //			sum( CASE WHEN ( EventCode = 'C00007' ) THEN 1 ELSE 0 END ) AS count,
            //			sum( CASE WHEN EventCode = 'C00012' THEN timespan ELSE 0 END ) AS timespan 
            //		FROM
            //			mcaeventstatisticbyday 
            //		WHERE
            //			starttime >= '{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and  starttime<'{endTime.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' 
            //		GROUP BY
            //			mcacode ,AlarmCode
            //		) b ON a.fcode = b.mcacode
            //		LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
            //		LEFT JOIN mtbacode p on p.MModeID = a.MModeID and b.AlarmCode = p.AlarmCode
            //		LEFT OUTER JOIN supplier e ON d.SupplierID = e.id
            //		LEFT OUTER JOIN mactprocess f ON a.id = f.macid
            //		LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
            //		INNER JOIN factoryregion i ON i.Id = a.FactoryId
            //		INNER JOIN factoryregion j ON j.Id = a.RegionId
            //		INNER JOIN factoryregion k ON k.Id = j.ParentId
            //		INNER JOIN factoryregion l ON l.id = k.ParentId 
            //	WHERE
            //		1 = 1  {filter} {sub}
            //	) c 
            //	GROUP BY c.FCode,
            //	FactoryName,
            //	PlantName,
            //	FloorName,
            //	MModeCode,
            //	FName
            //ORDER BY
            //	sum(	c.timespan) /(case when sum( c.count)=0 then 1 else sum( c.count) end) / 60 DESC 
            //limit {(start)},{length}";
            #endregion
            string sql = $@" 
select 
i.FName FactoryName,
l.FName PlantName,
k.FName FloorName,
d.FCode MModeCode,
a.FName FName,
a.FCode FCode
,machinenumber.macnumber macnumber, b.timespan /(case when  b.count=0 then 1 else b.count end) / 60
from 
machine a
INNER JOIN(SELECT
            			maccode MacCode,
            			sum( CASE WHEN ( EventCode = 'C00007' ) THEN count ELSE 0 END ) AS count,
            			sum( CASE WHEN EventCode = 'C00012' THEN timespan ELSE 0 END ) AS timespan 
            		FROM
            			{tablename} 
            		GROUP BY
            			maccode)  b on a.FCode=b.MacCode
LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
LEFT JOIN factoryregion i ON i.Id = a.FactoryId
LEFT JOIN factoryregion j ON j.Id = a.RegionId
LEFT JOIN factoryregion k ON k.Id = j.ParentId
LEFT JOIN factoryregion l ON l.id = k.ParentId
left join machinenumber on a.id=machinenumber.MacId

ORDER BY b.timespan /(case when  b.count=0 then 1 else b.count end) / 60 DESC 
limit {(start)},{length} ";
            var pros = CurrDb.FindList<Machine>(sql);
            //if (pros != null && pros.Count() > 0)
            //{
            //    var regionDal = new FactoryRegionDal(CurrDb);
            //    var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, errorinfo);
            //    foreach (var item in pros)
            //    {
            //        item.RegionName = regionDal.GetFullRegionName(item.RegionId, allRegions);
            //    }
            //}
            return pros;
        }
        #endregion

        #region 设备报警
        public IEnumerable<ChartEntity> GetMacAlarm()
        {
            string sql = $@"SELECT mt.PCode AS Name,sum(me.count) AS Value
                            from (SELECT count(1) as count,mcacode FROM  mcaeventstatisticbyday e
                            WHERE e.StartTime>='{DateTime.Today.ToString("yyyy-MM-dd")}' and e.EventCode='C00007'
                            group by mcacode ) me
                            inner join   machine mac on mac.FCode=me.McaCode
                            inner join mactprocess mt on mac.id=mt.macid
                            WHERE mac.factoryid!=154
                            GROUP BY  mt.PCode
                            ORDER BY Value DESC LIMIT 0,5";
            return CurrDb.FindList<ChartEntity>(sql);
        }
        #endregion

        #region 查询车间
        public IEnumerable<SelectDto<string>> GetLocationSelect(string factory)
        {
            string sql = $@"select LOCATION as Value from ccmrpt.CCMMES_FIXED_ASSETS_V  where FACTORY='{factory}'
                            group by LOCATION order by LOCATION";
            return CurrDb.FindList<SelectDto<string>>(sql);
        }

        public IEnumerable<SelectDto<string>> GetWarehouseSelect(string factory)
        {
            string sql = $@"select warehouse as Value from ccmrpt.CCMMES_SPARE_PRICE_V  where FACTORY='{factory}'
                            group by warehouse order by warehouse";
            return CurrDb.FindList<SelectDto<string>>(sql);
        }
        #endregion
        #region 设备良率
        /// <summary>
        /// 一次良率
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public IEnumerable<MacGoodRateDto> GetMacGoodRate(string starttime, string endtime)
        {
            /*string sql = $@"	select a.FACTORY Factroy,a.PDATE PDate,trunc( (a.INPUT-b.INPUT)/a.INPUT*100,2) as Rate
	                            from
			                            (select FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd')  PDATE,SUM(INPUT) INPUT
			                            from ccmrpt.ccmmes_input_for_eap_v
			                            where 
			                            PDATE>to_date('{starttime}','yyyy-mm-dd hh24:mi:ss')
			                            and PDATE<=to_date('{endtime}','yyyy-mm-dd hh24:mi:ss')
			                            GROUP BY FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd') 
			                            ORDER BY FACTORY) a
			                            LEFT JOIN 
			                            (select FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd')  PDATE,SUM(first_defect_qty) INPUT
			                            from ccmrpt.ccmmes_first_defect_for_eap_v
			                            where 
			                            PDATE>to_date('{starttime}','yyyy-mm-dd hh24:mi:ss')
			                            and PDATE<=to_date('{endtime}','yyyy-mm-dd hh24:mi:ss')
			                            GROUP BY FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd') 
			                            ORDER BY FACTORY) b
			                            on a.FACTORY=b.FACTORY and a.PDATE=b.PDATE";*/
            string sql = $@"select FACTORY Factroy,PLANT Plant,FLOOR Floor,TO_CHAR(PDATE,'yyyy-MM-dd') PDate,SUM(INPUT) Input,SUM(OUTPUT) Output FROM CCMEES.EES_PDATE_GOOD_RATE2_V where FLOOR like '%EOL' and PLANT='A1' and PDATE>to_date('{starttime}','yyyy-mm-dd hh24:mi:ss')
			                            and PDATE<=to_date('{endtime}','yyyy-mm-dd hh24:mi:ss')  GROUP BY FACTORY,PLANT,FLOOR,PDATE ";
            var data= CurrDb.FindList<MacGoodRateDto>(sql);
            foreach(var i in data)
            {
                i.Input = i.Input > 0 ? i.Input : 1;
                i.Rate = (Math.Round((i.Output / i.Input), 4) * 100).ToString();
            }
            return data;
        }
        /// <summary>
        /// 首件
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public IEnumerable<MacGoodRateDto> GetShouJian(string starttime, string endtime)
        {
            /*string sql = $@"SELECT
	a.园区 Factroy,
	TO_CHAR( a.日期, 'yyyy-MM-dd' ) PDATE,
	nvl(round((a.合格数+b.合格数)/(a.count+b.count),4)*100, 0 ) Rate  
FROM(select 园区, 日期, nvl(合格数, 0) 合格数, 合格率，round(nvl(合格数, 0) / nvl(合格率, 1)) count from ccmrpt.IPQC_SHOUJIAN_OKRATE_V t where 班次 = '白班') a INNER JOIN(select 园区, 日期, nvl(合格数, 0)合格数, 合格率，round(nvl(合格数, 0) / nvl(合格率, 1)) count from ccmrpt.IPQC_SHOUJIAN_OKRATE_V t where 班次 = '晚班') b on a.园区 = b.园区 and a.日期 = b.日期  WHERE

    a.日期 > to_date('2021-10-29', 'yyyy-mm-dd hh24:mi:ss')

    AND a.日期 <= to_date('2021-11-05', 'yyyy-mm-dd hh24:mi:ss')
ORDER BY

    a.日期";*/
            string sql = $@"SELECT
	园区 Factroy,
	TO_CHAR(日期, 'yyyy-MM-dd' ) PDATE,
	nvl(SUM( 合格数 ) ,0) Input,
	nvl(SUM( 不合格数 ) ,0)Output
FROM
	ccmrpt.IPQC_SHOUJIAN_OKRATE_V t 
WHERE
园区 like '未来城%' and 日期 > to_date('{starttime}', 'yyyy-mm-dd hh24:mi:ss')

    AND 日期 <= to_date('{endtime}', 'yyyy-mm-dd hh24:mi:ss')
group by
	园区,日期";

            var data = CurrDb.FindList<MacGoodRateDto>(sql);
            foreach (var i in data)
            {
                i.Output = i.Input+i.Output > 0 ? i.Input+i.Output : 1;
                i.Rate = (Math.Round((i.Input / i.Output), 4) * 100).ToString();
            }
            return data;
        }

        public IEnumerable<MacGoodRateDto> GetMacGoodDetai(string starttime, string endtime)
        {
            string sql = $@"	select a.FACTORY Factroy,a.PDATE,a.PART_NAME partName,a.STEP_NAME stepName,trunc( (a.INPUT-b.INPUT)/a.INPUT*100,4) as Rate
	                            from
			                            (select FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd')  PDATE,PART_NAME,STEP_NAME,SUM(INPUT) INPUT
			                            from ccmmes_input_for_eap_v
			                            where 
			                            PDATE>to_date('{starttime}','yyyy-mm-dd hh24:mi:ss')
			                            and PDATE<=to_date('{endtime}','yyyy-mm-dd hh24:mi:ss')
			                            GROUP BY FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd') ,PART_NAME,STEP_NAME
			                            ORDER BY FACTORY) a
			                            LEFT JOIN 
			                            (select FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd')  PDATE,PART_NAME,STEP_NAME,SUM(first_defect_qty) INPUT
			                            from ccmmes_first_defect_for_eap_v
			                            where 
			                            PDATE>to_date('{starttime}','yyyy-mm-dd hh24:mi:ss')
			                            and PDATE<to_date('{endtime}','yyyy-mm-dd hh24:mi:ss')
			                            GROUP BY FACTORY,TO_CHAR(PDATE,'yyyy-MM-dd') ,PART_NAME,STEP_NAME
			                            ORDER BY FACTORY) b
			                            on a.FACTORY=b.FACTORY 
																	and a.PDATE=b.PDATE
																	and a.STEP_NAME = b.STEP_NAME
																	and a.PART_NAME = b.PART_NAME";
            return CurrDb.FindList<MacGoodRateDto>(sql);
        }

        public int GetMacGoodDetailCount(string filter, string starttime, string endtime)
        {
            string errorinfo = string.Empty;
            string date = $",max(CASE WHEN PDate='{starttime}' THEN Rate ELSE 0 END) as \"{starttime}\"";
            var sql = $@"select  Count(1) Count from (SELECT
				factroy 园区,
				Plant 厂房,
				FLOOR 楼层,
				RECIPE 机种,
				WORK_PROCESS 工序
				{date}
            FROM
                (
                SELECT

                    FACTORY Factroy,
                    PLANT Plant,
                    FLOOR Floor,
                    RECIPE,
                    WORK_PROCESS,
                    TO_CHAR(PDATE, 'yyyy-MM-dd') PDate,
                    SUM(INPUT) Input,
                    SUM(OUTPUT) Output,
                    ROUND(
                        SUM(input) / DECODE(SUM(input) + SUM(OUTPUT), 0, 1, SUM(input) + SUM(OUTPUT)) * 100,
                        2
                    ) rate

                FROM

                    CCMEES.EES_PDATE_GOOD_RATE2_V

                WHERE 1=1

                    AND FLOOR LIKE '%EOL'

                    AND PDATE > to_date('{starttime}', 'yyyy-mm-dd hh24:mi:ss')

                    AND PDATE <= to_date('{endtime}', 'yyyy-mm-dd hh24:mi:ss')
{filter}

                GROUP BY

                    FACTORY,
                    PLANT,
                    FLOOR,
                    PDATE,
                    RECIPE,
                    WORK_PROCESS
                )

            GROUP BY

                Factroy,
				PLANT,
				FLOOR,
				RECIPE,
				WORK_PROCESS )
where 1=1 
                       ";
            var entities = CurrDb.FindList<MacGoodRateDto>(sql).FirstOrDefault();
            if (entities != null)
            {
                return entities.Count;
            }
            return 0;
        }
        public DataTable GetMacGoodDetail(string starttime, string endtime, int start, int length, string order, string sort, string filter)
        {
            DateTime sdtime = Convert.ToDateTime(starttime);
            DateTime edtime = Convert.ToDateTime(endtime);
            int days = edtime.Subtract(sdtime).Days;
            string pdate = "";
            for (int i = 0; i <= days; i++)
            {
                var date = sdtime.AddDays(i).ToString("yyyy-MM-dd");
                pdate += $" ,max(CASE WHEN PDate='{date}' THEN NVL(Rate,0) ELSE 0 END) as \"{ date}\" ";
            }
            /*string sql = $@"SELECT *

            FROM (
		        select t.*,ROWNUM AS rowno from
		        (
			        select  
				        Factroy 园区,partName 机种,stepName 工序 {pdate}
                    FROM(
                            select a.FACTORY Factroy, a.PDATE PDate, a.PART_NAME partName, a.STEP_NAME stepName, trunc((a.INPUT - b.INPUT) / a.INPUT * 100, 2) as Rate
                            from
                                (select FACTORY, TO_CHAR(PDATE, 'yyyy-MM-dd')  PDATE, PART_NAME, STEP_NAME, SUM(INPUT) INPUT
                                from ccmrpt.ccmmes_input_for_eap_v
                                where
                                PDATE > to_date('{starttime}', 'yyyy-mm-dd hh24:mi:ss')
                                and PDATE <= to_date('{endtime}', 'yyyy-mm-dd hh24:mi:ss') {filter}
                                GROUP BY FACTORY, TO_CHAR(PDATE, 'yyyy-MM-dd'), PART_NAME, STEP_NAME
                                ) a
                                LEFT JOIN
                                (select FACTORY, TO_CHAR(PDATE, 'yyyy-MM-dd')  PDATE, PART_NAME, STEP_NAME, SUM(first_defect_qty) INPUT
                                from ccmrpt.ccmmes_first_defect_for_eap_v
                                where
                                PDATE > to_date('{starttime}', 'yyyy-mm-dd hh24:mi:ss')
                                and PDATE < to_date('{endtime}', 'yyyy-mm-dd hh24:mi:ss')
                                {filter}
                                GROUP BY FACTORY, TO_CHAR(PDATE, 'yyyy-MM-dd'), PART_NAME, STEP_NAME
                                ) b
                                on a.FACTORY = b.FACTORY
                                and a.PDATE = b.PDATE
                                and a.STEP_NAME = b.STEP_NAME
                                and a.PART_NAME = b.PART_NAME
                                )tt
                            GROUP BY Factroy, partName, stepName
                            ORDER BY Factroy, partName, stepName		
				        ) t
                    ) table_alias
                    WHERE table_alias.rowno between { start - 1} and {length} ";*/
            string sql = $@"SELECT
	* 
FROM
	(
	SELECT
		t.*,
		ROWNUM AS rowno 
	FROM
			(
			SELECT
				factroy 园区,
				Plant 厂房,
				FLOOR 楼层,
				RECIPE 机种,
				WORK_PROCESS 工序
				{pdate}
            FROM
                (
                SELECT

                    FACTORY Factroy,
                    PLANT Plant,
                    FLOOR Floor,
                    RECIPE,
                    WORK_PROCESS,
                    TO_CHAR(PDATE, 'yyyy-MM-dd') PDate,
                    SUM(INPUT) Input,
                    SUM(OUTPUT) Output,
                    ROUND(
                        SUM(input) / DECODE(SUM(input) + SUM(OUTPUT), 0, 1, SUM(input) + SUM(OUTPUT)) * 100,
                        2
                    ) rate

                FROM

                    CCMEES.EES_PDATE_GOOD_RATE2_V

                WHERE 1=1
                    AND FLOOR LIKE '%EOL'

                    AND PDATE > to_date('{starttime}', 'yyyy-mm-dd hh24:mi:ss')

                    AND PDATE <= to_date('{endtime}', 'yyyy-mm-dd hh24:mi:ss')
                    {filter}
                GROUP BY

                    FACTORY,
                    PLANT,
                    FLOOR,
                    PDATE,
                    RECIPE,
                    WORK_PROCESS
                )

            GROUP BY

                Factroy,
				PLANT,
				FLOOR,
				RECIPE,
				WORK_PROCESS
ORDER BY
		Factroy,
		PLANT,
		FLOOR,
		RECIPE,
		WORK_PROCESS
			) t
		) table_alias
    WHERE table_alias.rowno between { start - 1} and {length}";
            DataTable dt = CurrDb.FindTable(sql);
            dt.Columns.Add("FactoryRowSpan");
            dt.Columns.Add("PlantRowSpan");
            dt.Columns.Add("FloorRowSpan");
            dt.Columns.Add("PartNameRowSpan");

            var query = dt.AsEnumerable().GroupBy(c => c.Field<string>("园区"));
            foreach (var model in query)
            {
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i]["园区"].ToString() == model.Key)
                    {
                        dt.Rows[i]["FactoryRowSpan"] = model.Count();
                        var plantGroup= dt.AsEnumerable().Where(l => l.Field<string>("园区")==model.Key).GroupBy( c=> c.Field<string>("厂房"));
                        foreach(var j in plantGroup)
                        {
                            if (dt.Rows[i]["厂房"].ToString() == j.Key)
                            {
                                dt.Rows[i]["PlantRowSpan"] = j.Count();
                                var floorGroup = dt.AsEnumerable().Where((l => l.Field<string>("园区") == model.Key && l.Field<string>("厂房") == j.Key)).GroupBy(c => c.Field<string>("楼层"));
                                foreach(var k in floorGroup)
                                {
                                    if (dt.Rows[i]["楼层"].ToString() == k.Key)
                                    {
                                        dt.Rows[i]["FloorRowSpan"] = k.Count();
                                        var parkGroup = dt.AsEnumerable().Where((l => l.Field<string>("园区") == model.Key && l.Field<string>("厂房") == j.Key && l.Field<string>("楼层") == k.Key)).GroupBy(c => c.Field<string>("机种"));
                                        foreach (var it in parkGroup)
                                        {
                                            if (dt.Rows[i]["机种"].ToString() == it.Key)
                                            {
                                                dt.Rows[i]["PartNameRowSpan"] = it.Count();
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        
                    }
                }
            }
            if (dt == null || dt.Rows.Count <= 0)
                return null;
            return dt;
        }

        

        public IEnumerable<SelectDto<string>> GetFactory()
        {
            string sql = $@"select DISTINCT(园区) Value FROM CCMRPT.IPQC_SHOUJIAN_OKRATE_V";
            var data = CurrDb.FindList<SelectDto<string>>(sql);

            return data;
        }

        public List<MacGoodRateDto> GetShouJianDatil(string factory, string shift, DateTime? start, DateTime? endtime)
        {
            MacGoodRateDto total = new MacGoodRateDto() { Input = 0, Count = 0, Output = 0,goal= "99.00" };
            string sql = $@"SELECT
	园区 Factroy,
	
	TO_CHAR(日期, 'yyyy-MM-dd' ) PDATE,
	nvl(SUM( 合格数 ) ,0) Input,
	nvl(SUM( 不合格数 ) ,0)Output
FROM
	ccmrpt.IPQC_SHOUJIAN_OKRATE_V t 
WHERE 1=1
";
            if (!string.IsNullOrEmpty(factory))
                sql += $@" and 园区='{factory}'";
            if (!string.IsNullOrEmpty(shift))
                sql += $@" and 班次='{shift}'";
            if (start.HasValue)
                sql += $@" and 日期>= TO_DATE('{start.Value.ToString("yyyy-MM-dd ")}','yyyy-mm-dd hh24:mi:ss')";
            if (endtime.HasValue)
                sql += $@" and 日期<= TO_DATE('{endtime.Value.ToString("yyyy-MM-dd ")}','yyyy-mm-dd hh24:mi:ss')";
            sql += $@"group by
	园区,日期
ORDER BY 园区,日期 desc";
            var data = CurrDb.FindList<MacGoodRateDto>(sql).ToList();
            foreach(var item in data)
            {
                item.Count = Convert.ToInt32(item.Input + item.Output);
                item.goal = "99.00";
                item.Rate = (Math.Round((item.Input / (item.Count>0?item.Count:1)), 4) * 100).ToString();
                total.Output += item.Output;
                total.Input += item.Input;
                total.Count += item.Count;
            }
          
            total.Factroy = "total";
            total.Rate = (Math.Round((total.Input / (total.Count > 0 ? total.Count : 1)), 4) * 100).ToString();
            data.Add(total);
           data.Reverse();
            return (data);
        }

        public IEnumerable<SelectDto<string>> GetMultipleFactorySelects(string filter)
        {
            var sql = $@"select distinct FACTORY value,FACTORY label from  CCMEES.EES_PDATE_GOOD_RATE2_V";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultiplePlantSelects(string filter)
        {
            var sql = $@"select distinct Plant value,Plant label from CCMEES.EES_PDATE_GOOD_RATE2_V where FLOOR like '%EOL' {filter}";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultipleFloorSelects(string filter)
        {
            var sql = $@"select distinct FLOOR value,FLOOR label from CCMEES.EES_PDATE_GOOD_RATE2_V where FLOOR like '%EOL'  {filter}";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultiplePartNameSelects(string filter)
        {
            var sql = $@"select distinct RECIPE value,RECIPE label from CCMEES.EES_PDATE_GOOD_RATE2_V where FLOOR like '%EOL' and RECIPE is not null {filter}";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultipleStepNameSelects(string filter)
        {
            var sql = $@"select distinct WORK_PROCESS value,WORK_PROCESS label from CCMEES.EES_PDATE_GOOD_RATE2_V where WORK_PROCESS is NOT NULL and FLOOR like '%EOL'";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }

        public List<MEAttendance> GetMEAttendance(DateTime? time)
        {
            string sql = $@"SELECT
	DEPT_NAME DeptName,
	ATTEND_DATE Time,
	EMP_TYPE EmpType,
	SUM( ONWORK_NUM ) OnWorkNum,
	SUM( ONJOB_NUM ) OnJobNum,
	round( SUM( ONJOB_NUM ) / SUM( ONWORK_NUM ), 4 ) * 100 Rate 
FROM
	SCOTT.V_SUM_ATTEND_RATE_CCM 
WHERE
	DEPT_NAME IN ('影像设备ME一课', '影像设备ME三课', '影像设备ME二课', '影像设备ME四课', '影像设备ME试产课', '影像设备资源管理课' ) 
		AND EMP_TYPE = '员工' and ATTEND_DATE=TO_DATE('{time.Value.ToString("yyyy-MM-dd ")}','yyyy-mm-dd hh24:mi:ss')
	GROUP BY
		DEPT_NAME,
		ATTEND_DATE,
		EMP_TYPE 
ORDER BY
	decode(DEPT_NAME,'影像设备ME一课', 1,'影像设备ME二课',2, '影像设备ME三课',3, '影像设备ME四课',4, '影像设备ME试产课',5, '影像设备资源管理课',6,'影像设备改善课',7,'影像设备部',8)";
            return CurrDb.FindList<MEAttendance>(sql).ToList();
        }

        public List<MEAttendance> GetMEAttendanceDatil(string deptname, string shift, string Type, DateTime? start)
        {
            string str = "";
            string sql = $@"SELECT
	DEPT_NAME DeptName,
	ATTEND_DATE Time,
	SUM( ONWORK_NUM ) OnWorkNum,
	SUM( ONJOB_NUM ) OnJobNum,
	round( SUM( ONJOB_NUM ) / SUM( ONWORK_NUM ), 4 ) * 100 Rate 
FROM
	SCOTT.V_SUM_ATTEND_RATE_CCM 
WHERE 1=1 ";
            if (!string.IsNullOrEmpty(deptname))
            {
                str += $@" and DEPT_NAME in ('{deptname}')";
            }
            else
            {
                str += $@" and DEPT_NAME IN ('影像设备ME一课', '影像设备ME三课', '影像设备ME二课', '影像设备ME四课', '影像设备ME试产课', '影像设备资源管理课','影像设备改善课','影像设备部')  ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                str += $@" and  WORKTYPE='{shift}'";
            }
            if (!string.IsNullOrEmpty(Type))
            {
                str += $@" and  EMP_TYPE = '{Type}'";
            }
            if (start.HasValue)
                str += $@" and ATTEND_DATE= TO_DATE('{start.Value.ToString("yyyy-MM-dd ")}','yyyy-mm-dd hh24:mi:ss')";
           
            sql +=str+ $@"GROUP BY
		DEPT_NAME,
		ATTEND_DATE
ORDER BY
	decode(DEPT_NAME,'影像设备ME一课', 1,'影像设备ME二课',2, '影像设备ME三课',3, '影像设备ME四课',4, '影像设备ME试产课',5, '影像设备资源管理课',6,'影像设备改善课',7,'影像设备部',8)";
            var data= CurrDb.FindList<MEAttendance>(sql).ToList();
            str = $@"SELECT
	ATTEND_DATE Time,
	SUM( ONWORK_NUM ) OnWorkNum,
	SUM( ONJOB_NUM ) OnJobNum,
	round( SUM( ONJOB_NUM ) / SUM( ONWORK_NUM ), 4 ) * 100 Rate 
FROM
	SCOTT.V_SUM_ATTEND_RATE_CCM  where 1=1" + str + $@"GROUP BY
		ATTEND_DATE";
            var list = CurrDb.FindList<MEAttendance>(str).FirstOrDefault();
            if (list != null)
                list.DeptName = "小计";
                data.Add(list);
            return data;
        }
        #endregion
    }
}