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
    {
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
        {
            //192.168.1.100
            ConnectionString = "Database=LingPaoProject; Data Source=192.168.1.26; 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>();
                // 您的数据库操作代码
            }
            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) 
            {
            
            
            }

            

        }

        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,bool islocalhost=false)
        {


            

            if ( workOrderNo != null )
            {
                return Db.Queryable<WorkOrderInfo>().Where(it => it.WorkOrderNo == workOrderNo).ToList();
            }
            else
            {
                return 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).Count() }).ToList();
            }
        }

        public static bool InsertWorkOrderInfo(WorkOrderInfo info)
        {

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

        public static bool UpdateWorkOrderInfo(WorkOrderInfo info)
        {

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

        public static bool DeleteWorkOrderInfo(WorkOrderInfo info)
        {

            if ( Db.Deleteable(info).Where(it => it.WorkOrderNo == info.WorkOrderNo).ExecuteCommand() == 1 )
            {
                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 )
            {
                return true;
            }
            return false;
        }

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

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

        public static bool UpdateSnInfo(SnInfo info)
        {

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

        public static bool DeleteSnInfo(SnInfo info)
        {

            if ( Db.Deleteable(info).Where(it => it.Sn == info.Sn).ExecuteCommand() == 1 )
            {
                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 )
            {
                return true;
            }
            return false;
        }

        public static bool UpdateBindRecord(BindRecord info)
        {

            if ( Db.Updateable(info).Where(it => it.Sn == info.Sn).ExecuteCommand() == 1 )
            {
                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 )
                {
                    return true;
                }
            }
            else
            {
                if ( Db.Deleteable<BindRecord>().ExecuteCommand() != 0 )
                {
                    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 )
            {
                return true;
            }
            return false;
        }

        public static bool UpdateProcessRecord(ProcessRecord info)
        {

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

        public static bool DeleteProcessRecord(string sn = null)
        {

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


        public static List<LocalhostPartInfo> SelectPartInfo(StationType StationCode =  StationType.OP10)
        {
            var query = Db.Queryable<LocalhostPartInfo>().Where(it => it.FormulaType == StationCode); ;
            return query.ToList();

        }
        public static bool InsertPartInfo(LocalhostPartInfo info)
        {

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

    

        public static bool DeletePartInfo(StationType StationCode, string MaterialCode)
        {

            if (Db.Deleteable<LocalhostPartInfo>().Where(it => it.FormulaType == StationCode&&it.MaterialCode==MaterialCode).ExecuteCommand() == 1)
            {
                return true;
            }
            return false;
        }

    


        public static List<ParkingLot> SelectParkingLot(StationType StationCode = StationType.OP10)
        {
            var query = Db.Queryable<ParkingLot>().Where(it => it.FormulaType == StationCode); ;
            return query.ToList();

        }
        public static bool InsertParkingLot(ParkingLot info)
        {

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



        public static bool DeleteParkingLot(StationType StationCode, int id)
        {

            if (Db.Deleteable<ParkingLot>().Where(it => it.FormulaType == StationCode && it.Id == id).ExecuteCommand() == 1)
            {
                return true;
            }
            return false;
        }
        

    }
}