LPLocalhostMES/LocalhostMES/DataBase/DatabaseHelper.cs

using LocalhostMES.Core;
using LocalhostMES.Enums;
using LocalhostMES.Models;
using SqlSugar;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
using static Org.BouncyCastle.Pqc.Crypto.Utilities.PqcOtherInfoGenerator;

namespace LocalhostMES.DataBase
{
    public class DatabaseHelper
    {
        private static void NotifyChanged(MesDataScope scope)
        {
            MesDataChangedNotifier.Raise(scope);
        }

        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
        {
            //192.168.1.100
            ConnectionString = "Database=LingPaoProject; Data Source=127.0.0.1; Port=3306;User Id=root; Password=root;Charset=utf8;AllowPublicKeyRetrieval=True;SslMode=None;",//连接符字串
            DbType = SqlSugar.DbType.MySql,//数据库类型
            IsAutoCloseConnection = true //不设成true要手动close
        },
         db =>
         {

             db.Aop.OnLogExecuting = (sql, pars) =>
             {
                 Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
             };

         });

        public static void CreataDataTable()
        {
            try
            {

                Db.CodeFirst.InitTables<SnInfo>();
                Db.CodeFirst.InitTables<BindRecord>();
                Db.CodeFirst.InitTables<ProcessRecord>();
                Db.CodeFirst.InitTables<ParkingLot>();
                Db.CodeFirst.InitTables<WorkOrderInfo>();
                Db.CodeFirst.InitTables<LocalhostPartInfo>();
                EnsureLocalhostPartInfoColumns();
                // 您的数据库操作代码
            }
            catch ( AggregateException ex )
            {
                // 遍历所有内部异常
                foreach ( var inner in ex.InnerExceptions )
                {
                    Console.WriteLine($"内部异常: {inner.Message}");
                    Console.WriteLine(inner.StackTrace);
                }
                // 或者只取第一个
                // var actualException = ex.InnerException;
            }
            catch ( SqlSugarException ex )
            {
                Console.WriteLine($"错误消息：{ex.Message}");
                Console.WriteLine($"内部异常：{ex.InnerException?.Message}");
                Console.WriteLine($"堆栈：{ex.StackTrace}");
                // 也可以记录日志
            }
            catch ( Exception e )
            {


            }



        }

        private static void EnsureLocalhostPartInfoColumns()
        {
            Db.Ado.ExecuteCommand("ALTER TABLE `LocalhostPartInfo` ADD COLUMN IF NOT EXISTS `MaterialCode` varchar(128) NULL");
            Db.Ado.ExecuteCommand("ALTER TABLE `LocalhostPartInfo` ADD COLUMN IF NOT EXISTS `StationCode` varchar(128) NULL");
            Db.Ado.ExecuteCommand("ALTER TABLE `LocalhostPartInfo` ADD COLUMN IF NOT EXISTS `PositionCode` varchar(128) NULL");
            Db.Ado.ExecuteCommand("ALTER TABLE `LocalhostPartInfo` ADD COLUMN IF NOT EXISTS `PartInfoName` varchar(200) NULL");
        }

        public static int GetProductProductionRecords(string sn)
        {
            try
            {
                return Db.Queryable<ProductionToStation>().Where(it => it.SN == sn).ToList().Count;
            }
            catch ( Exception ex )
            {
                LogHelper.WriteLogError("获取一条产品的生产记录至数据库时出错！", ex);

            }
            return 0;
        }
        public static List<WorkOrderInfo> SelectWorkOrderInfo(string workOrderNo = null)
        {
            if ( workOrderNo != null )
            {
                var single = Db.Queryable<WorkOrderInfo>()
                    .Where(it => it.WorkOrderNo == workOrderNo)
                    .Select(it => new WorkOrderInfo
                    {
                        OrderNo = it.OrderNo,
                        LineCode = it.LineCode,
                        CreateTime = it.CreateTime,
                        EndTime = it.EndTime,
                        IsLocalhost = it.IsLocalhost,
                        MaterialCode = it.MaterialCode,
                        MaterialName = it.MaterialName,
                        PlannedQuantity = it.PlannedQuantity,
                        StartTime = it.StartTime,
                        Status = it.Status,
                        WorkOrderNo = it.WorkOrderNo,
                        CompletedQuantity = SqlFunc.Subqueryable<SnInfo>().Where(s => s.WorkOrderNo == it.WorkOrderNo&&s.IsUsed).Count()
                    }).ToList();
                NormalizeWorkOrderStatus(single);
                return single;
            }
            else
            {
                var list = Db.Queryable<WorkOrderInfo>().
                    Select(it=>new WorkOrderInfo
                    {
                        OrderNo=it.OrderNo,
                        LineCode=it.LineCode,
                        CreateTime=it.CreateTime,
                        EndTime=it.EndTime,
                        IsLocalhost=it.IsLocalhost,
                        MaterialCode=it.MaterialCode,
                        MaterialName=it.MaterialName,
                        PlannedQuantity=it.PlannedQuantity,
                        StartTime=it.StartTime,
                        Status=it.Status,
                        WorkOrderNo=it.WorkOrderNo,
                        CompletedQuantity=SqlFunc.Subqueryable<SnInfo>().Where(s=>s.WorkOrderNo==it.WorkOrderNo && s.IsUsed).Count()
                    }).ToList();
                NormalizeWorkOrderStatus(list);
                return list;
            }
        }

        private static void NormalizeWorkOrderStatus(List<WorkOrderInfo> workOrders)
        {
            if ( workOrders == null || workOrders.Count == 0 )
            {
                return;
            }

            var changed = false;
            foreach ( var workOrder in workOrders )
            {
                if ( workOrder.PlannedQuantity > 0 &&
                    workOrder.CompletedQuantity >= workOrder.PlannedQuantity &&
                    workOrder.Status != "6" )
                {
                    workOrder.Status = "6";
                    Db.Updateable<WorkOrderInfo>()
                        .SetColumns(it => new WorkOrderInfo { Status = "6" })
                        .Where(it => it.WorkOrderNo == workOrder.WorkOrderNo)
                        .ExecuteCommand();
                    changed = true;
                }
            }

            if ( changed )
            {
                NotifyChanged(MesDataScope.WorkOrder);
            }
        }

        public static bool InsertWorkOrderInfo(WorkOrderInfo info)
        {

            if ( Db.Storageable(info).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.WorkOrder);
                return true;
            }
            return false;
        }

        public static bool UpdateWorkOrderInfo(WorkOrderInfo info)
        {

            if ( Db.Updateable(info).Where(it => it.WorkOrderNo == info.WorkOrderNo).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.WorkOrder);
                return true;
            }
            return false;
        }

        public static bool DeleteWorkOrderInfo(WorkOrderInfo info)
        {

            if ( Db.Deleteable(info).Where(it => it.WorkOrderNo == info.WorkOrderNo).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.WorkOrder);
                return true;
            }
            return false;
        }
        public static List<SnInfo> SelectSnInfo(string workOrderNo = null, bool needused = false, bool isused = false)
        {
            if ( workOrderNo != null )
            {
                if ( needused )
                {
                    return Db.Queryable<SnInfo>().Where(it => it.WorkOrderNo == workOrderNo && it.IsUsed == isused).ToList();
                }
                else
                {
                    return Db.Queryable<SnInfo>().Where(it => it.WorkOrderNo == workOrderNo).ToList();
                }
            }
            else
            {
                return Db.Queryable<SnInfo>().ToList();
            }
        }

        public static SnInfo SelectSnInfoForStation(string workOrderNo,StationType type)
        {
            if ( workOrderNo != null )
            {
               var query=Db.Queryable<SnInfo>();
                switch ( type )
                {
                    case StationType.OP10:
                        break;
                    case StationType.OP20L:
                    case StationType.OP20R:
                        query = query.Where(it => it.OP20State==false);
                        break;
                    case StationType.OP30L:
                    case StationType.OP30R:
                        query = query.Where(it => it.OP30State == false);
                        break;
                    case StationType.OP40L:
                    case StationType.OP40R:
                        query = query.Where(it => it.OP40State == false);
                        break;
                    case StationType.OP50L:
                    case StationType.OP50R:
                        query = query.Where(it => it.OP50State == false);
                        break;
                    case StationType.OP60L:
                    case StationType.OP60R:
                        query = query.Where(it => it.OP60State == false);
                        break;
                    case StationType.OP70L:
                    case StationType.OP70R:
                        query = query.Where(it => it.OP70State == false);
                        break;
                    case StationType.OP80L:
                    case StationType.OP80R:
                        query = query.Where(it => it.OP80State == false);
                        break;
                    default:
                        break;
                }
                List<SnInfo> snInfos= query.Where(it => it.WorkOrderNo == workOrderNo).OrderBy(it => it.GenerateTime).Take(1).ToList();
                if ( snInfos.Count != 0 ) 
                {
                    return snInfos[ 0 ];
                }
                return null;
            }
            else
            {
                return null;
            }
        }

        public static bool InsertSnInfo(SnInfo info)
        {

            if ( Db.Storageable(info).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.Sn);
                return true;
            }
            return false;
        }

        public static bool InsertSnInfos(List<SnInfo> info)
        {

            if ( Db.Storageable(info).ExecuteCommand() == info.Count )
            {
                NotifyChanged(MesDataScope.Sn);
                return true;
            }
            return false;
        }

        public static bool UpdateSnInfo(SnInfo info)
        {

            if ( Db.Updateable(info).Where(it => it.Sn == info.Sn).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.Sn);
                return true;
            }
            return false;
        }

        public static bool UpdateSnInfo(string sn,string workorder,StationType type)
        {
            var query= Db.Updateable<SnInfo>().Where(it => it.Sn == sn&&it.WorkOrderNo==workorder);

            switch ( type )
            {
                case StationType.OP10:
                    break;
                case StationType.OP20L:
                case StationType.OP20R:
                    query= query.SetColumns(it => it.OP20State == true);
                    break;
                case StationType.OP30L:
                case StationType.OP30R:
                    query = query.SetColumns(it => it.OP30State == false);
                    break;
                case StationType.OP40L:
                case StationType.OP40R:
                    query = query.SetColumns(it => it.OP40State == false);
                    break;
                case StationType.OP50L:
                case StationType.OP50R:
                    query = query.SetColumns(it => it.OP50State == false);
                    break;
                case StationType.OP60L:
                case StationType.OP60R:
                    query = query.SetColumns(it => it.OP60State == false);
                    break;
                case StationType.OP70L:
                case StationType.OP70R:
                    query = query.SetColumns(it => it.OP70State == false);
                    break;
                case StationType.OP80L:
                case StationType.OP80R:
                    query = query.SetColumns(it => it.OP80State == false);
                    break;
                default:
                    break;
            }
            if ( query.ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.Sn);
                return true;
            }
            return false;
        }

        public static bool DeleteSnInfo(SnInfo info)
        {

            if ( Db.Deleteable(info).Where(it => it.Sn == info.Sn).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.Sn);
                return true;
            }
            return false;
        }
        public static List<BindRecord> SelectBindRecord(string Sn = null, string PartNum = null, string StationCode = null)
        {
            var query = Db.Queryable<BindRecord>();

            // 动态添加查询条件
            if ( !string.IsNullOrWhiteSpace(Sn) )
            {
                query = query.Where(it => it.Sn == Sn);
            }

            if ( !string.IsNullOrWhiteSpace(PartNum) )
            {
                query = query.Where(it => it.PartNum == PartNum);
            }

            if ( !string.IsNullOrWhiteSpace(StationCode) )
            {
                query = query.Where(it => it.StationCode == StationCode);
            }

            return query.ToList();

        }
        public static bool InsertBindRecord(BindRecord info)
        {

            if ( Db.Storageable(info).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.BindRecord);
                return true;
            }
            return false;
        }

        public static bool UpdateBindRecord(BindRecord info)
        {

            if ( Db.Updateable(info).Where(it => it.Sn == info.Sn).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.BindRecord);
                return true;
            }
            return false;
        }

        public static bool DeleteBindRecord(string sn = null)
        {
            if ( sn != null )
            {
                if ( Db.Deleteable<BindRecord>().Where(it => it.Sn == sn).ExecuteCommand() == 1 )
                {
                    NotifyChanged(MesDataScope.BindRecord);
                    return true;
                }
            }
            else
            {
                if ( Db.Deleteable<BindRecord>().ExecuteCommand() != 0 )
                {
                    NotifyChanged(MesDataScope.BindRecord);
                    return true;
                }
            }
            return false;
        }

        public static List<ProcessRecord> SelectProcessRecord(string Sn = null, string StationCode = null)
        {
            var query = Db.Queryable<ProcessRecord>();

            // 动态添加查询条件
            if ( !string.IsNullOrWhiteSpace(Sn) )
            {
                query = query.Where(it => it.Sn == Sn);
            }



            if ( !string.IsNullOrWhiteSpace(StationCode) )
            {
                query = query.Where(it => it.Station == StationCode);
            }

            return query.ToList();

        }
        public static bool InsertProcessRecord(ProcessRecord info)
        {

            if ( Db.Storageable(info).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.ProcessRecord);
                return true;
            }
            return false;
        }

        public static bool UpdateProcessRecord(ProcessRecord info)
        {

            if ( Db.Updateable(info).Where(it => it.Sn == info.Sn).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.ProcessRecord);
                return true;
            }
            return false;
        }

        public static bool DeleteProcessRecord(string sn = null)
        {

            if ( Db.Deleteable<ProcessRecord>().Where(it => it.Sn == sn).ExecuteCommand() == 1 )
            {
                NotifyChanged(MesDataScope.ProcessRecord);
                return true;
            }
            return false;
        }

        public static List<LocalhostPartInfo> SelectPartInfo(StationType stationType)
        {
          return  Db.Queryable<LocalhostPartInfo>().Where(it => it.StationType == stationType).ToList();
        }
        public static List<LocalhostPartInfo> SelectPartInfo(string _materialCode, string stationCode)
        {
           return Db.Queryable<LocalhostPartInfo>().Where(it => it.StationCode == stationCode&&it.MaterialCode==_materialCode).ToList();          
        }
        public static bool InsertPartInfo(LocalhostPartInfo info)
        {
            var ok = Db.Storageable(info).ExecuteCommand() > 0;
            if ( ok )
            {
                NotifyChanged(MesDataScope.KeyPart);
            }
            return ok;
        }

        public static int InsertPartInfos(List<LocalhostPartInfo> infos)
        {
            if ( infos == null || infos.Count == 0 )
            {
                return 0;
            }

            var affected = Db.Storageable(infos).ExecuteCommand();
            if ( affected > 0 )
            {
                NotifyChanged(MesDataScope.KeyPart);
            }

            return affected;
        }

        public static bool DeletePartInfo(StationType stationType, string partNum)
        {
            var ok = Db.Deleteable<LocalhostPartInfo>()
                .Where(it => it.StationType == stationType && it.PartNum == partNum)
                .ExecuteCommand() >= 1;
            if ( ok )
            {
                NotifyChanged(MesDataScope.KeyPart);
            }

            return ok;
        }
        /// <summary>
        /// 零件
        /// </summary>
        /// <returns></returns>
        public static List<ParkingLot> SelectParkingLot()
        {
            return Db.Queryable<ParkingLot>().OrderBy(it => it.Id).ToList();
        }

        public static bool InsertParkingLot(ParkingLot info)
        {
            var ok = Db.Storageable(info).ExecuteCommand() > 0;
            if ( ok )
            {
                NotifyChanged(MesDataScope.ParkingLot);
            }

            return ok;
        }

        public static bool UpdateParkingLot(ParkingLot info)
        {
            var ok = Db.Updateable(info).Where(it => it.Id == info.Id).ExecuteCommand() == 1;
            if ( ok )
            {
                NotifyChanged(MesDataScope.ParkingLot);
            }

            return ok;
        }

        public static bool DeleteParkingLot(int id)
        {
            var ok = Db.Deleteable<ParkingLot>().Where(it => it.Id == id).ExecuteCommand() == 1;
            if ( ok )
            {
                NotifyChanged(MesDataScope.ParkingLot);
            }

            return ok;
        }

        #region 当前左右工位识别码

        public static OrderToMesInfo SelectWorkOrderInfo()
        {
            List<OrderToMesInfo> lilsts = Db.Queryable<OrderToMesInfo>().ToList();
            if ( lilsts.Count != 0 )
            {
                return lilsts[ 0 ];
            }
            else
            {
                return new OrderToMesInfo() { CreateTime = DateTime.Now, Id = 0, LeftCheckCode = "", RightCheckCode = "" };
            }
        }

        public static bool InsertWorkOrderInfo(OrderToMesInfo info)
        {

            if ( Db.Storageable(info).ExecuteCommand() == 1 )
            {
                return true;
            }
            return false;
        }

        public static bool UpdateWorkOrderInfo(OrderToMesInfo info, bool isleft = true)
        {
            if ( info.CurrMarkCode != string.Empty )
            {

                if ( Db.Updateable(info).UpdateColumns(it => new { it.CurrMarkCode }).Where(it => it.Id == info.Id).ExecuteCommand() == 1 )
                {
                    return true;
                }
                return false;
            }
            if ( isleft )
            {
                if ( Db.Updateable(info).UpdateColumns(it => new { it.CreateTime, it.LeftCheckCode }).Where(it => it.Id == info.Id).ExecuteCommand() == 1 )
                {
                    return true;
                }
            }
            else
            {
                if ( Db.Updateable(info).UpdateColumns(it => new { it.CreateTime, it.RightCheckCode }).Where(it => it.Id == info.Id).ExecuteCommand() == 1 )
                {
                    return true;
                }
            }
            return false;
        }

        #endregion
    }
}