ZJYJ.FD.SlnMesnac/DB/PlcPointHelper.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using ZJ_BYD.Common;
using ZJ_BYD.Model;
using ZJ_BYD.Untils;
using ZJ_BYD.ViewModel;

namespace ZJ_BYD.DB
{
    public class PlcPointHelper
    {
        public PlcPointHelper() : base()
        {

        }

        /// <summary>
        /// 查询所有PLC点位
        /// </summary>
        /// <returns></returns>
        public static ISugarQueryable<T_PlcPoint> QueryPlcPoints()
        {
            return DBHelper.sqlSugarDb.Queryable<T_PlcPoint>();
        }

        /// <summary>
        /// 根据IdPLC点位
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static T_PlcPoint QueryPlcPointById(int id)
        {
            return DBHelper.sqlSugarDb.Queryable<T_PlcPoint>().First(m => m.Id == id);
        }

        /// <summary>
        /// 查询未删除的PLC点位
        /// </summary>
        /// <returns></returns>
        public static ISugarQueryable<T_PlcPoint> QueryActivePlcPoints()
        {
            return DBHelper.sqlSugarDb.Queryable<T_PlcPoint>().Where(m => !m.IsDeleted);
        }

        /// <summary>
        /// 查询未删除的PLC点位
        /// </summary>
        /// <returns></returns>
        public static ISugarQueryable<PcPointVM> QueryActiveCustomPlcPoints(string ipcId, string lineCode, string stationCode)
        {
            return DBHelper.sqlSugarDb.Queryable<T_PlcPoint>()
                .Where(m => m.IpcId == ipcId && m.LineCode == lineCode && m.StationCode == stationCode && m.IsSaveDb && m.IsActive && !m.IsDeleted && !string.IsNullOrWhiteSpace(m.ResultField))
                .Select(m => new PcPointVM
                {
                    PointName = m.PointName,
                    ResultField = m.ResultField,

                }).OrderBy(m => m.SortIndex);
        }

        /// <summary>
        /// 根据Ids删除PLC点位
        /// 逻辑删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static int DelPlcPointsByIds(List<int> ids)
        {
            return DBHelper.sqlSugarDb.Updateable<T_PlcPoint>()
                     .SetColumns(m => m.IsDeleted == true)
                     .SetColumns(m => m.UpdatedTime == DateTime.Now)
                     .SetColumns(m => m.UpdatedBy == CurrentUser.UserName)
                     .Where(m => ids.Contains(m.Id)).ExecuteCommand();
        }

        /// <summary>
        /// 根据Ids物理删除PLC点位
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static int DoDelPlcPointsByIds(List<int> ids)
        {
            return DBHelper.sqlSugarDb.Deleteable<T_PlcPoint>().Where(m => ids.Contains(m.Id)).ExecuteCommand();
        }

        /// <summary>
        /// 新增PLC点位
        /// </summary>
        /// <param name="t_PlcPoint"></param>
        /// <returns></returns>
        public static int AddPlcPoint(T_PlcPoint t_PlcPoint)
        {
            return DBHelper.sqlSugarDb.Insertable(t_PlcPoint).ExecuteCommand();
        }

        /// <summary>
        /// 修改PLC点位
        /// </summary>
        /// <param name="t_PlcPoint"></param>
        /// <returns></returns>
        public static int UpdatePlcPoint(T_PlcPoint t_PlcPoint)
        {
            return DBHelper.sqlSugarDb.Updateable(t_PlcPoint).ExecuteCommand();
        }

        /// <summary>
        /// 批量新增PLC点位
        /// </summary>
        /// <param name="t_PlcPoints"></param>
        /// <returns></returns>
        public static bool AddPlcPoints(List<T_PlcPoint> t_PlcPoints)
        {
            var result = DBHelper.sqlSugarDb.UseTran(() =>
            {
                foreach (var item in t_PlcPoints)
                {
                    AddPlcPoint(item);
                }
            });
            return result.IsSuccess;
        }

        public static DataTable GetStationData(string stationIndex, string startAddress, string len, string productSfcCode)
        {
            var stationSortIndex = int.Parse(stationIndex);
            var begingAddress = int.Parse(startAddress);
            var endAddress = begingAddress + int.Parse(len);
            var station = DBHelper.sqlSugarDb.Queryable<T_Station>().First(m => !m.IsDeleted && m.SortIndex == stationSortIndex);
            var newDb = DBHelper.GetConnStrByIp(station.IpcId.Split('-')[1].ToString());
            var cols = newDb.Queryable<T_PlcPoint>()
                      .Where(m => m.StationCode == station.StationCode
                             && m.SortIndex >= begingAddress && m.SortIndex < endAddress && !m.IsDeleted && m.IsActive).ToList();
            var sqlCol = "";
            if (cols.Count > 0)
            {
                for (int i = 0; i < cols.Count; i++)
                {
                    sqlCol += $"coalesce({cols[i].ResultField},'') as \"{cols[i].ResultField}\",";
                }
                var paras = new SugarParameter[] {
                                new SugarParameter("@stationcode", station.StationCode),
                                new SugarParameter("@productsfccode", productSfcCode.Replace("\r", ""))
                            };
                var sql = string.Format("select {0} stationcode,productsfccode from t_result where stationcode=@stationcode and productsfccode=@productsfccode order by createdtime desc LIMIT 1", sqlCol);
                var dt = newDb.Ado.GetDataTable(sql, paras);
                return dt;
            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// 查询PLC点位中已使用的列
        /// </summary>
        /// <param name="stationCode"></param>
        /// <returns></returns>
        public static List<string> GetUsedCol(string stationCode)
        {
            return DBHelper.sqlSugarDb.Queryable<T_PlcPoint>().Where(m => m.IpcId == Program.CurrentIpcId && m.LineCode == Program.CurrentLineCode
                                              && m.StationCode == stationCode && m.IsActive && !m.IsDeleted)
                   .Select(m => m.ResultField).ToList();
        }
    }
}