DeviceManager/DeviceRepair.DataAccess/FormAccess.cs

using DeviceRepair.Models;
using DeviceRepair.Models.History;
using DeviceRepair.Models.Preserve;
using DeviceRepair.Models.View;
using DeviceRepair.Utils;
using NLog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;

namespace DeviceRepair.DataAccess
{
    public class FormAccess : DbContext<MaintenanceFormVersionInfo>
    {
        private static readonly Logger log = LogManager.GetCurrentClassLogger();

        private static FormAccess manager;
        public static FormAccess Instance
        {
            get
            {
                if (manager == null)
                    manager = new FormAccess();
                return manager;
            }
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public APIResponseData InsertSingle(MaintenanceFormVersionInfo Item, HeaderModel Operation)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };
            try
            {
                if (Item == null)
                    throw new Exception("传入的点检表新增对象不能为空！");

                DateTime CurrentDate = DateTime.Now;

                db.ChangeDatabase("main");
                Item.CreatDate = CurrentDate;
                Item.CreatUser = Operation.Operator;

                MaintenanceFormVersionInfo CurrentData = CurrentDb.AsInsertable(Item).ExecuteReturnEntity();
                if (CurrentData.AutoID > 0)
                {
                    apiResponseData.Code = 1;
                    apiResponseData.Message = "";
                    apiResponseData.Data = CurrentData;


                    db.ChangeDatabase("log");
                    FormLogInfo log = new FormLogInfo
                    {
                        FormID = CurrentData.AutoID,
                        FormName = Item.FormName,
                        FormVersionRev = Item.VersionRev,
                        OperationComputer = Operation.ClientName,
                        OperationDate = CurrentDate,
                        OperationIP = Operation.IPAddress,
                        OperationType = "新增",
                        OperationUser = Operation.Operator,
                        OperationUserName = Operation.OperatorName
                    };
                    db.Insertable(log).ExecuteCommand();
                }
            }
            catch (SqlSugarException ex)
            {
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            catch (Exception ex)
            {
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            return apiResponseData;
        }

        /// <summary>
        /// 判断数据是否存在
        /// </summary>
        /// <param name="maintenance"></param>
        /// <returns></returns>
        public APIResponseData DataIsExists(MaintenanceFormVersionInfo maintenance)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };
            try
            {
                db.ChangeDatabase("main");
                bool IsExists = db.Queryable<MaintenanceFormVersionInfo>().Any(x => //x.FormName.Equals(maintenance.FormName, StringComparison.CurrentCultureIgnoreCase) &&
                 x.VersionCode.Equals(maintenance.VersionCode, StringComparison.CurrentCultureIgnoreCase) &&
                 x.VersionRev.Equals(maintenance.VersionRev, StringComparison.CurrentCultureIgnoreCase));

                apiResponseData.Code = 1;
                apiResponseData.Data = IsExists;
                apiResponseData.Message = "点检表已存在！";
            }
            catch (SqlSugarException ex)
            {
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            catch (Exception ex)
            {
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            return apiResponseData;
        }

        /// <summary>
        /// 获取单条数据
        /// </summary>
        /// <param name="PrimaryKey"></param>
        /// <returns></returns>
        public new APIResponseData GetSingle(int PrimaryKey)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };
            try
            {
                if (PrimaryKey <= 0)
                    throw new Exception("点检表主键编号不能小于等于0！");

                db.ChangeDatabase("main");
                var Data = db.Queryable<MaintenanceFormVersionInfo>().First(x => x.AutoID == PrimaryKey);
                apiResponseData.Code = 1;
                apiResponseData.Data = Data;
                apiResponseData.Message = "";
            }
            catch (SqlSugarException e)
            {
                log.Error(e);
                return new APIResponseData { Code = -1, Message = e.Message };
            }
            catch (Exception ex)
            {
                log.Error(ex);
                throw new Exception(ex.Message);
            }
            return apiResponseData;
        }

        /// <summary>
        /// 根据关键字筛选
        /// </summary>
        /// <param name="filterValue"></param>
        /// <returns></returns>
        public APIResponseData GetQuery(string filterValue)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };
            try
            {
                db.ChangeDatabase("main");
                var exp = Expressionable.Create<MaintenanceFormVersionInfo>();
                exp.And(x => x.FormBelong == "PM");

                if (!string.IsNullOrEmpty(filterValue))
                {
                    exp.And(x => x.FormName.Contains(filterValue) || x.Remarks.Contains(filterValue) || x.VersionCode.Contains(filterValue));
                }

                List<MaintenanceFormVersionInfo> Datas = CurrentDb.AsQueryable().Where(exp.ToExpression()).ToList();
                apiResponseData.Code = 1;
                apiResponseData.Data = Datas;
                apiResponseData.Message = "";
            }
            catch (SqlSugarException e)
            {
                log.Error(e);
                return new APIResponseData { Code = -1, Message = e.Message };
            }
            catch (Exception ex)
            {
                log.Error(ex);
                throw new Exception(ex.Message);
            }
            return apiResponseData;
        }

        /// <summary>
        /// 单行数据翻转当前状态(启用/禁用)
        /// </summary>
        /// <param name="AutoID"></param>
        /// <param name="Operation"></param>
        /// <returns></returns>
        public APIResponseData ChangeStauts(int AutoID, HeaderModel Operation)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };

            try
            {
                db.ChangeDatabase("main");
                MaintenanceFormVersionInfo item = CurrentDb.GetSingle(x => x.AutoID == AutoID);
                if (item == null)
                    throw new Exception($"未能获取到主键编号为【{AutoID}】的数据！");

                item.ChangeUser = Operation.Operator;
                item.ChangeDate = DateTime.Now;
                item.FormStatus = !item.FormStatus;

                bool result = CurrentDb.Update(item);
                if (result)
                {
                    FormLogInfo log = new FormLogInfo
                    {
                        FormID = item.AutoID,
                        FormName = item.FormName,
                        FormVersionRev = item.VersionRev,
                        OperationUser = Operation.Operator,
                        OperationUserName = Operation.OperatorName,
                        OperationComputer = Operation.ClientName,
                        OperationIP = Operation.IPAddress,
                        OperationType = item.FormStatus ? "锁定" : "解锁"
                    };
                    db.ChangeDatabase("log");
                    db.Insertable(log).ExecuteCommand();
                }
                apiResponseData.Code = result ? 1 : 0;
                apiResponseData.Message = result ? "" : "操作失败！";
            }
            catch (SqlSugarException e)
            {
                log.Error(e);
                return new APIResponseData { Code = -1, Message = e.Message };
            }
            catch (Exception ex)
            {
                log.Error(ex);
                throw new Exception(ex.Message);
            }
            return apiResponseData;
        }

        /// <summary>
        /// 获取设备信息树形结构 -- 按名称分类
        /// </summary>
        /// <returns></returns>
        public APIResponseData GetDeviceInformationTreeView(string LoginCode)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = "数据库通信失败！" };
            try
            {
                List<DeviceInformationInfo> devs = DeviceAccess.Instance.GetDevsByLoginAuths(LoginCode);
                if (devs.Count == 0)
                    return apiResponseData;

                db.ChangeDatabase("main");
                string[] EquipmentIds = devs.Select(x => x.EquipmentID).ToArray();
                List<View_AssignMaintenanceFormTree> lst = db.Queryable<View_AssignMaintenanceFormTree>()
                    .Where(x => SqlFunc.ContainsArray(EquipmentIds, x.EquipmentID)).ToList();
                apiResponseData.Code = 1;
                apiResponseData.Message = "";
                apiResponseData.Data = lst;
            }
            catch (SqlSugarException ex)
            {
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            catch (Exception ex)
            {
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            return apiResponseData;
        }

        /// <summary>
        /// 分配点检表到指定设备
        /// </summary>
        /// <param name="lst"></param>
        /// <returns></returns>
        public APIResponseData AssigningFormToDevices(List<SubmitAssignFormsToDevices> lst, HeaderModel Operation)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = "数据库通信失败！" };
            if (lst == null)
                throw new Exception("待修改的对象为空！");
            try
            {
                DateTime CurrentDate = DateTime.Now;

                db.ChangeDatabase("main");
                db.BeginTran();

                bool execSuccess = true;

                //解除绑定
                if (lst.Any(x => x.OperationType == EnumOperationType.UnBind))
                {
                    int[] editData = lst.Where(x => x.OperationType == EnumOperationType.UnBind).Select(x => x.EquipmentAutoID).ToArray();
                    execSuccess = db.Updateable<DeviceInformationInfo>()
                        .UpdateColumns(x => new { x.MaintenanceFormVersion, x.ChangeDate, x.ChangeUser })
                        .ReSetValue(x => x.MaintenanceFormVersion == 0)
                        .ReSetValue(x => x.ChangeUser == Operation.Operator)
                        .ReSetValue(x => x.ChangeDate == CurrentDate)
                        .Where(x => editData.Contains(x.AutoID)).ExecuteCommand() > 0;
                }

                //新增绑定
                if (execSuccess && lst.Any(x => x.OperationType == EnumOperationType.Bind))
                {
                    int[] editData = lst.Where(x => x.OperationType == EnumOperationType.Bind).Select(x => x.EquipmentAutoID).ToArray();
                    execSuccess = db.Updateable<DeviceInformationInfo>()
                        .UpdateColumns(x => new { x.MaintenanceFormVersion, x.ChangeDate, x.ChangeUser })
                        .ReSetValue(x => x.MaintenanceFormVersion == lst[0].FormAutoID)
                        .ReSetValue(x => x.ChangeUser == Operation.Operator)
                        .ReSetValue(x => x.ChangeDate == CurrentDate)
                        .Where(x => editData.Contains(x.AutoID)).ExecuteCommand() > 0;
                }

                if (execSuccess)
                {
                    apiResponseData.Code = 1;
                    apiResponseData.Message = "";
                    db.CommitTran();
                }


                db.ChangeDatabase("log");
                List<DeviceHistory> logs = new List<DeviceHistory>();

                foreach (SubmitAssignFormsToDevices item in lst)
                {
                    logs.Add(new DeviceHistory
                    {
                        FormAutoID = item.FormAutoID,
                        FormCode = item.VersionCode,
                        FormName = item.FormName,
                        FormRev = item.VersionRev,
                        EquipmentAutoID = item.EquipmentAutoID,
                        EquipmentID = item.EquipmentID,
                        EquipmentName = item.EquipmentName,
                        Specification = item.Specification,
                        OperationComputer = Operation.ClientName,
                        OperationDate = CurrentDate,
                        OperationIP = Operation.IPAddress,
                        OperationType = item.OperationType == EnumOperationType.Bind ? "绑定" : "解除绑定",
                        OperationUser = Operation.Operator,
                        OperationUserName = Operation.OperatorName
                    });
                }

                db.Insertable(logs).ExecuteCommand();
            }
            catch (SqlSugarException ex)
            {
                db.RollbackTran();
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            catch (Exception ex)
            {
                db.RollbackTran();
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            return apiResponseData;
        }

        /// <summary>
        /// 修改备注
        /// </summary>
        /// <returns></returns>
        public APIResponseData ChangeRemark(MaintenanceFormVersionInfo Item, HeaderModel Operation)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = "数据库通信失败！" };
            if (Item == null)
                throw new Exception("待修改的对象为空！");

            try
            {
                DateTime CurrentDate = DateTime.Now;

                db.ChangeDatabase("main");
                db.BeginTran();

                MaintenanceFormVersionInfo oItem = CurrentDb.GetById(Item.AutoID);

                if (oItem == null)
                    throw new Exception($"找不到主键编号为【{Item.AutoID}】的点检表信息！");

                bool IsSuccess = db.Updateable<MaintenanceFormVersionInfo>()
                    .UpdateColumns(x => new { x.Remarks, x.ChangeDate, x.ChangeUser })
                    .ReSetValue(x => x.Remarks == Item.Remarks)
                    .ReSetValue(x => x.ChangeDate == CurrentDate)
                    .ReSetValue(x => x.ChangeUser == Operation.Operator)
                    .Where(x => x.AutoID == Item.AutoID).ExecuteCommandHasChange();

                db.CommitTran();

                if (IsSuccess)
                {
                    apiResponseData.Code = 1;
                    apiResponseData.Message = "";

                    db.ChangeDatabase("log");
                    MaintenanceFormVersionHistoryInfo log = new MaintenanceFormVersionHistoryInfo
                    {
                        ClientMac = Operation.ClientMac,
                        ClientName = Operation.ClientName,
                        CreatDate = CurrentDate,
                        CreatUser = Operation.Operator,
                        FormName = oItem.FormName,
                        FormPath = oItem.FormPath,
                        FormStatus = oItem.FormStatus,
                        Guid = new Guid(),
                        IPAddress = Operation.IPAddress,
                        MaintenanceFormVersionAutoID = oItem.AutoID,
                        Remarks = oItem.Remarks,
                        VersionCode = oItem.VersionCode,
                        VersionRev = oItem.VersionRev
                    };
                    db.Insertable(log).ExecuteCommand();
                }
            }
            catch (SqlSugarException ex)
            {
                db.RollbackTran();
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            catch (Exception ex)
            {
                db.RollbackTran();
                log.Error(ex);
                apiResponseData.Code = -1;
                apiResponseData.Message = ex.Message;
            }
            return apiResponseData;
        }

        /// <summary>
        /// 获取AM的点检表数据
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public APIResponseData GetQuery(MaintenanceFormFilter filter)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };
            try
            {
                db.ChangeDatabase("main");
                string FormBelong = filter.FormBelong.ToString();
                var exp = Expressionable.Create<MaintenanceFormVersionInfo>();
                exp.And(x => x.FormBelong == FormBelong);

                if (!filter.FilterText.IsNull())
                {
                    string filterValue = filter.FilterText.Trim();
                    exp.And(x => x.FormName.Contains(filterValue) || x.Remarks.Contains(filterValue) || x.VersionCode.Contains(filterValue));
                }

                List<MaintenanceFormVersionInfo> Datas = CurrentDb.AsQueryable().Where(exp.ToExpression()).ToList();
                apiResponseData.Code = 1;
                apiResponseData.Data = Datas;
                apiResponseData.Message = "";
            }
            catch (SqlSugarException e)
            {
                log.Error(e);
                return new APIResponseData { Code = -1, Message = e.Message };
            }
            catch (Exception ex)
            {
                log.Error(ex);
                throw new Exception(ex.Message);
            }
            return apiResponseData;
        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public APIResponseData StatusChange(MaintenanceFormFilter filter)
        {
            APIResponseData apiResponseData = new APIResponseData { Code = -1, Message = $"数据库通信失败！" };
            try
            {
                db.ChangeDatabase("main");
                MaintenanceFormVersionInfo CurrentData = CurrentDb.GetById(filter.PrimaryKey);
                if (CurrentData == null)
                {
                    throw new Exception("未能找到当前的数据,请刷新后再试!");
                }

                CurrentData.FormStatus = filter.FormStatus;
                if (CurrentDb.Update(CurrentData))
                {
                    apiResponseData.Code = 1;
                    apiResponseData.Data = null;
                    apiResponseData.Message = "";
                }
            }
            catch (SqlSugarException e)
            {
                log.Error(e);
                return new APIResponseData { Code = -1, Message = e.Message };
            }
            catch (Exception ex)
            {
                log.Error(ex);
                throw new Exception(ex.Message);
            }
            return apiResponseData;
        }
    }
}