everlight.eap/backend/DllEapDal/MachineChangeDal.cs

using Cksoft.Data;
using DllEapEntity;
using DllEapEntity.Dtos;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DllEapDal
{
    public class MachineChangeDal
    {
        private readonly IDatabase Db = null;
        public MachineChangeDal(IDatabase db)
        {
            Db = db;
        }

        public IEnumerable<MachineChange> Get(string factory, string plant, string floor, string pcode, string maccode, string macnum, DateTime? start, DateTime? end,
             out int total,
             int pageIndex = 1, int pageSize = 20)
        {
            var sql = "from  machinechangemst as t left JOIN machine as a on t.MacID=a.id   left JOIN factoryregion as b on a.RegionId=b.id inner JOIN factoryregion as c on b.ParentId=c.id inner JOIN factoryregion as d on c.ParentId=d.id inner JOIN factoryregion as e on d.ParentId=e.id inner JOIN mactprocess as f on a.id=f.MacID left JOIN machinenumber as g on a.id=g.macid left JOIN machinechangedetail as h on t.id=h.MstId and h.Type=1 LEFT JOIN machinechangedetail as j on t.id= j.MstId  and j.Type=-1 left join staff s on t.RecCode=s.fcode where 1=1 ";
            if (!string.IsNullOrEmpty(factory))
            {
                sql += $" and e.id={factory}";
            }
            if (!string.IsNullOrEmpty(plant))
            {
                sql += $" and d.id={plant}";
            }
            if (!string.IsNullOrEmpty(floor))
            {
                sql += $" and c.id={floor}";
            }
            if (!string.IsNullOrEmpty(pcode))
            {
                sql += $" and f.PCode ='{pcode}'";
            }
            if (!string.IsNullOrEmpty(maccode))
            {
                sql += $" and a.FCode like '%{maccode}%'";
            }
            if (!string.IsNullOrEmpty(macnum))
            {
                sql += $" and g.MacNumber like'%{macnum}%'";
            }
            if (start.HasValue)
            {
                sql += $" and t.EndDate > '{start.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }
            if (end.HasValue)
            {
                sql += $" and t.EndDate < '{end.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }
            sql += $" AND t.EndDate!='0001-01-01 00:00:00'ORDER BY t.RecTime DESC";

            var ssql = "select e.FName as Factory,d.FName as Plant , c.FName as Floor ,f.pcode as PCode,a.Fcode as MacCode,g.MacNumber as MacNum,t.RecTime as STime,h.Recipe as PreRecipe,j.Recipe as CurentRecipe,t.EndDate as ETime,t.RecCode,s.fname Name  " + sql;
            
            var datas = Db.FindList<MachineChange>(ssql+ $" limit {(pageIndex - 1) * pageSize},{pageSize}");
            var sql1 = "select count(1)" + sql;
            total = Convert.ToInt32(Db.FindObject(sql1));
            foreach (var item in datas)
            {
                item.Time = Math.Round((item.ETime - item.STime).TotalHours, 2);
                item.StandardTime = 12;
                item.ExtraTime = (item.Time - item.StandardTime) > 0 ? Math.Round((item.Time - item.StandardTime),2) : 0;
                item.STime = Convert.ToDateTime(item.STime.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            return datas;
        }

        public int delete(string code)
        {
            //return Db.DeleteFor<MachineNumber>(code);
            string sql = $@"delete from machinenumber where macid=(select id from machine where fcode='{code}')";
            return Db.ExecuteBySql(sql);
        }

        public ChartDto CreateCategory(string factory, string plant, string floor, string maccode, string macnum, string pcode, DateTime? start, DateTime? end)
        {
            string sql = $@"SELECT a.FCode MacCode, round(time_to_sec(timediff(t.EndDate,t.Date))/3600.00,2)  Time

FROM
	machinechangemst AS t
	LEFT JOIN machine AS a ON t.MacID = a.id
	LEFT JOIN factoryregion AS b ON a.RegionId = b.id
	INNER JOIN factoryregion AS c ON b.ParentId = c.id
	INNER JOIN factoryregion AS d ON c.ParentId = d.id
	INNER JOIN factoryregion AS e ON d.ParentId = e.id
	INNER JOIN mactprocess AS f ON a.id = f.MacID
	LEFT JOIN machinenumber AS g ON a.id = g.macid
WHERE 1 = 1 ";
            if (!string.IsNullOrEmpty(factory))
            {
                sql += $" and e.id={factory}";
            }
            if (!string.IsNullOrEmpty(plant))
            {
                sql += $" and d.id={plant}";
            }
            if (!string.IsNullOrEmpty(floor))
            {
                sql += $" and c.id={floor}";
            }
            if (!string.IsNullOrEmpty(pcode))
            {
                sql += $" and f.PCode = '{pcode}'";
            }
            if (!string.IsNullOrEmpty(maccode))
            {
                sql += $" and a.FCode like '%{maccode}%'";
            }
            if (!string.IsNullOrEmpty(macnum))
            {
                sql += $" and g.MacNumber like'%{macnum}%'";
            }
            if (start.HasValue) 
            {
                sql += $" and t.EndDate > '{start.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }
            if (end.HasValue)
            {
                sql += $" and t.EndDate < '{end.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }
            sql += $"AND t.EndDate != '0001-01-01 00:00:00' ORDER BY  Time desc limit 10";
            var data = Db.FindList<MachineChange>(sql);
            var chartDto = new ChartDto
            {
                text = "机台改机统计",
                legend = new string[] { "单次改机用时" },
                xdata = data.Select(c => c.MacCode).ToArray(),
                ydata = data.Select(c => (float)c.Time).ToArray()
            };
            return chartDto;
        }

        public void ChangeStart(MacStatus macStatus, string recipe)
        {

            MachineChangeMst mcm = new MachineChangeMst();
            string str = $@"Select ID from machine where FCode='{macStatus.MacCode}'";
            var data = Db.FindObject(str);
            mcm.MacId = (int)data;
            mcm.Date = macStatus.STime;
            mcm.RecCode = macStatus.RecCode;
            mcm.RecTime = macStatus.STime;
            int i = Db.InsertFor<MachineChangeMst>(mcm, mcm.RecCode);
            if (i > 0)
            {
                MachineChangeDetail mcd = new MachineChangeDetail();
                var msId = Db.FindObject($@"Select @@identity");
                mcd.MstId = Convert.ToInt32(msId);
                mcd.Type = 1;
                mcd.Recipe = recipe;
                mcd.Date = macStatus.STime;
                Db.InsertFor<MachineChangeDetail>(mcd, mcm.RecCode);
            }


        }

        public void ChangeEnd(MacStatus macStatus, string recipe)
        {
            string str = $@"SELECT StatusID from macstatus01 where MacCode='{ macStatus.MacCode}'";
            var data = Db.FindObject(str);
            if ((int)data == 204905)
            {
                string sql = $@"SELECT * from machinechangemst where Macid=(select id from machine where Fcode='{macStatus.MacCode}') and EndDate='0001-01-01 00:00:00'";
                List<MachineChangeMst> list = Db.FindList<MachineChangeMst>(sql).ToList();
                if (list == null || list.Count == 0)
                    return;
                MachineChangeMst mcm = new MachineChangeMst();
                mcm = list[0];
                mcm.ModCode = macStatus.RecCode;
                mcm.ModTime = macStatus.STime;
                mcm.EndDate = macStatus.STime;
                MachineChangeDetail mcd = new MachineChangeDetail();
                mcd.MstId = mcm.Id;
                mcd.Type = -1;
                mcd.Recipe = recipe;
                mcd.Date = macStatus.STime;
                Db.InsertFor<MachineChangeDetail>(mcd, macStatus.RecCode);
                Db.UpdateFor<MachineChangeMst>(mcm, macStatus.RecCode);
            }


        }
    }
}