LPLocalhostMES/LocalhostMES/DataBase/DatabaseHelper.cs

using LocalhostMES.Core;
using LocalhostMES.Enums;
using LocalhostMES.Models;
using SqlSugar;
using System;
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 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 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 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;
        }

        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
    }
}