JLH/JLHHJSvr/Helper/LockHelper.cs

using JLHHJSvr.BLL;
using JLHHJSvr.Com.Model;
using JLHHJSvr.LJException;
using LJLib.DAL.SQL;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using NPOI.SS.Formula;
using System.Web.Configuration;
using LJLib.Net.SPI.Com;
using LJLib.SQLEX;
using JLHHJSvr.Com;
using System.Diagnostics;

namespace JLHHJSvr.Helper
{
    internal class LockHelper : HelperBase
    {
        public static bool PreCheckBillLock<T>(ILJLockRequest<T> request, out string arg_msg)
        where T : LJResponse, new()
        {
            arg_msg = string.Empty;
            var tokendata = BllHelper.GetToken(request.token);

            if (request.billid <= 0)
            {
                arg_msg = $"单据ID参数不合法({request.billid})";
                return false;
            }

            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                return CheckIsLock(cmd, request.keyword, request.billid, tokendata.username, out arg_msg);
            }
        }
        /// <summary>
        /// 加锁单据
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="billid"></param>
        public static void LockBill(SqlCommand cmd, string keyword, int billid, string billcode,string opemp, int lockMinutes = 30)
        {
            //return;
            try
            {
                cmd.CommandText = "sp_lock_record";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@keyword", keyword);
                cmd.Parameters.AddWithValue("@billid", billid);
                cmd.Parameters.AddWithValue("@billcode", billcode);
                cmd.Parameters.AddWithValue("@opemp", opemp);
                cmd.Parameters.AddWithValue("@lockMinutes", lockMinutes);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var arg_rslt = Convert.ToInt32(reader["arg_rslt"]);
                        var arg_msg = Convert.ToString(reader["arg_msg"]);

                        //if (arg_rslt != 1) throw new LJCommonException(arg_msg);
                    }
                }
                RebuildBillRelation(cmd, keyword, billid);
            }
            finally
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Empty;
            }
        }

        /// <summary>
        /// 解锁单据
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="billid"></param>
        public static void UnLockBill(SqlCommand cmd, string keyword, int billid, string opemp, byte is_admin)
        {
            //return;
            try
            {
                cmd.CommandText = "sp_unlock_record";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@keyword", keyword);
                cmd.Parameters.AddWithValue("@billid", billid);
                cmd.Parameters.AddWithValue("@opemp", opemp);
                cmd.Parameters.AddWithValue("@is_admin", is_admin);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var arg_rslt = Convert.ToInt32(reader["arg_rslt"]);
                        var arg_msg = Convert.ToString(reader["arg_msg"]);

                        //if (arg_rslt != 1) throw new LJCommonException(arg_msg);
                    }
                }
                DeleteBillRelation(cmd, keyword, billid);
            }
            finally
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Empty;
            }
        }
        public static void ClearExpireLock(SqlCommand cmd)
        {
            try
            {
                cmd.CommandText = "DELETE FROM u_lock_table WHERE expire_time < GETDATE()";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Trace.Write(ex);
            }
        }
        /// <summary>
        /// 生成锁提示信息
        /// </summary>
        private static string GetLockMessage(SqlCommand cmd, string keyword, string billcode, string opEmp)
        {
            string billName = BillNames.GetBillCnName(keyword);
            return $"{billName}【{billcode}】被用户【{opEmp}】编辑中。";
        }
        #region
        /// <summary>
          /// DFS方式检查单据及其依赖的锁
          /// </summary>
        public static u_lock_table CheckLockAll(SqlCommand cmd, string keyword, int billid, string opemp)
        {
            var visited = new Dictionary<string, int>(); // 防止循环依赖
            return GetLockBill(cmd, keyword, billid, opemp, visited);
        }
        /// <summary>
        /// 检查单据是否上锁
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="keyword"></param>
        /// <param name="billid"></param>
        /// <param name="opemp"></param>
        /// <param name="arg_msg"></param>
        /// <returns></returns>
        public static bool CheckIsLock(SqlCommand cmd, string keyword, int billid, string opemp,out string arg_msg)
        {
            return CheckIsLockPro(cmd,keyword, billid, opemp, out arg_msg);
            arg_msg = string.Empty;
            //return false;
            var item = CheckLockAll(cmd, keyword, billid, opemp);
            if (item.Code == LockCheckResultCode.LockedByOther)
            {
                arg_msg = item.log_msg;
                return true;
            }
            return !(item.Code == LockCheckResultCode.Success || item.Code == LockCheckResultCode.LockedBySelf || item.Code == LockCheckResultCode.Expired);
        }
        /// <summary>
        /// DFS方式检查单据及依赖链上的锁
        /// </summary>
        private static u_lock_table GetLockBill(SqlCommand cmd, string keyword, int billid, string opemp, Dictionary<string, int> visited)
        {
            var key = $"{keyword}:{billid}";
            if (visited.TryGetValue(key, out var state))
            {
                if (state == 1) throw new InvalidOperationException($"检测到循环依赖: {key}");
                if (state == 2) return new u_lock_table { keyword = keyword, billid = billid, Code = LockCheckResultCode.Success };
            }
            visited[key] = 1; // 标记正在访问

            //
            var mainLock = new u_lock_table { keyword = keyword, billid = billid };
            DbSqlHelper.SelectOne(cmd,"u_lock_table","keyword = @keyword AND billid = @billid AND is_locked = 1",
                new Dictionary<string, object> { { "@keyword", keyword }, { "@billid", billid } },
                mainLock, "keyword,billid,billcode,opemp,is_locked,lock_time,expire_time");

            //if (mainLock.expire_time < DateTime.Now)
            //{ 
            //    mainLock.Code = LockCheckResultCode.Expired;
            //    return mainLock;
            //}
            if (mainLock.is_locked == 1)
            {
                mainLock.Code = mainLock.opemp.Equals(opemp) ? LockCheckResultCode.LockedBySelf : LockCheckResultCode.LockedByOther;
                mainLock.log_msg = GetLockMessage(cmd, keyword, mainLock.billcode, mainLock.opemp);
                return mainLock;
            }
            visited[key] = 2; // 标记完成

            // 
            var dependencies = BuildDependencies(cmd, keyword, billid);
            foreach (var dep in dependencies)
            {
                var depResult = GetLockBill(cmd, dep.keyword, dep.billid, opemp, visited);
                if (depResult.Code == LockCheckResultCode.LockedBySelf || depResult.Code == LockCheckResultCode.LockedByOther) return depResult;
            }

            mainLock.Code = LockCheckResultCode.Success;
            return mainLock;
        }
        /// <summary>
        /// 根据单据类型构造依赖关系
        /// </summary>
        private static List<u_lock_table> BuildDependencies(SqlCommand cmd, string keyword, int billid)
        {
            var dependencies = new List<u_lock_table>();

            if (BillNames.MattressQuote.Equals(keyword))
            {
                dependencies.Add(new u_lock_table { keyword = BillNames.MattressInterface, billid = billid });

                var bednetList = GetHelper<MattressHelper>(cmd)
                    .GetMattressMxMtrl(billid, new List<string> { "u_mattress_formula.formulatype = 99" });
                foreach (var bednet in bednetList)
                {
                    dependencies.Add(new u_lock_table { keyword = BillNames.BedNetQuote, billid = bednet.mtrlid.Value });
                }
            }
            else if (BillNames.MattressInterface.Equals(keyword))
            {
                dependencies.Add(new u_lock_table { keyword = BillNames.MattressQuote, billid = billid });
            }
            else if (BillNames.BedNetQuote.Equals(keyword))
            {
                var mattressList = GetHelper<MattressHelper>(cmd)
                    .GetMattressMxMtrl(0, new List<string> { $"u_mattress_formula.formulatype = 99 AND vv_bednet.bednetid = {billid}" });
                var idsList = mattressList.GroupBy(t => t.mattressid.Value).Select(t => t.Key).ToList();
                foreach (var id in idsList)
                {
                    dependencies.Add(new u_lock_table { keyword = BillNames.MattressQuote, billid = id });
                }
            }

            return dependencies;
        }
        #endregion

        #region FAST
        /// <summary>
        /// 重建单据依赖关系（递归方式）
        /// </summary>
        public static void RebuildBillRelation(SqlCommand cmd,string keyword,int billid)
        {
            // 递归构建关系
            var visited = new HashSet<string>();
            BuildRelationRecursive(cmd, keyword, billid, visited, keyword, billid);
        }

        private static void DeleteBillRelation(SqlCommand cmd, string keyword, int billid)
        {
            cmd.CommandText = @"
        DELETE FROM u_bill_relation
        WHERE src_keyword = @keyword
          AND src_billid  = @billid";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@keyword", keyword);
            cmd.Parameters.AddWithValue("@billid", billid);
            cmd.ExecuteNonQuery();
        }

        private static void BuildRelationRecursive(SqlCommand cmd,string curKeyword,int curBillid,HashSet<string> visited,string rootKeyword,int rootBillid)
        {
            var key = $"{curKeyword}:{curBillid}";
            if (!visited.Add(key)) return; // 防止循环爆栈

            var deps = BuildDependencies(cmd, curKeyword, curBillid);
            foreach (var dep in deps)
            {
                // 1. 写入关系表
                InsertRelation(cmd,rootKeyword,rootBillid,dep.keyword,dep.billid);
                // 2. 继续递归
                BuildRelationRecursive(cmd,dep.keyword,dep.billid,visited,rootKeyword,rootBillid);
            }
        }

        private static void InsertRelation(SqlCommand cmd,string srcKeyword,int srcBillid,string dstKeyword,int dstBillid)
        {
            cmd.CommandText = @"
        IF NOT EXISTS (
            SELECT 1 FROM u_bill_relation 
            WHERE src_keyword=@src_keyword
              AND src_billid=@src_billid
              AND dst_keyword=@dst_keyword
              AND dst_billid=@dst_billid
        )
        INSERT INTO u_bill_relation
        (src_keyword, src_billid, dst_keyword, dst_billid)
        VALUES
        (@src_keyword, @src_billid, @dst_keyword, @dst_billid);";
            cmd.Parameters.Clear();
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@src_keyword", srcKeyword);
            cmd.Parameters.AddWithValue("@src_billid", srcBillid);
            cmd.Parameters.AddWithValue("@dst_keyword", dstKeyword);
            cmd.Parameters.AddWithValue("@dst_billid", dstBillid);
            cmd.ExecuteNonQuery();
        }

        public static bool CheckIsLockPro(SqlCommand cmd,string keyword,int billid,string opemp,out string msg)
        {
            msg = "";
            cmd.CommandText = @"SELECT TOP 1 keyword
	                                ,billid
	                                ,billcode
	                                ,opemp
                                FROM u_lock_table
                                WHERE is_locked = 1
	                                AND opemp <> @opemp
	                                AND (
		                                (
			                                keyword = @keyword
			                                AND billid = @billid
			                                )
		                                OR EXISTS (
			                                SELECT 1
			                                FROM u_bill_relation
			                                WHERE src_keyword = @keyword
				                                AND src_billid = @billid
				                                AND dst_keyword = u_lock_table.keyword
				                                AND dst_billid = u_lock_table.billid
			                                )
		                                )";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@keyword", keyword);
            cmd.Parameters.AddWithValue("@billid", billid);
            cmd.Parameters.AddWithValue("@opemp", opemp);
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    msg = $"单据被用户【{reader["opemp"]}】编辑中";
                    return true;
                }
            }
            return false;
        }
        #endregion
    }
}