JLH/JLHHJSvr/BLL/BllHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.IO.Compression;
using JLHHJSvr.Com.Model;
using JLHHJSvr.DBA.DBModle;
using JLHHJSvr.LJException;
using LJLib;
using LJLib.DAL.SQL;
using LJLib.Tools.DEncrypt;

namespace JLHHJSvr.BLL
{
    internal static class BllHelper
    {
        private static LJCache<string, TokenData> _tokens = new LJCache<string, TokenData>(){DefaultAddMinutes = 120};

        /// <summary>
        /// TODO: 保存Token信息, 登录成功后绑定token与tokendata
        /// </summary>
        /// <param name="token"></param>
        /// <param name="tokendata"></param>
        public static void SetToken(string token, TokenData tokendata)
        {
            _tokens[token] = tokendata;
        }

        /// <summary>
        /// TODO: 带token请求是通过本方法获取tokendata
        /// </summary>
        /// <param name="token"></param>
        /// <returns>tokendata</returns>
        public static TokenData GetToken(string token)
        {
            if (_tokens.ContainsKey(token))
            {
                return _tokens[token];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// TODO: 获取ID
        /// </summary>
        /// <param name="cmd">数据库连接,事务</param>
        /// <param name="key">关联字</param>
        /// <param name="step">增幅,默认1</param>
        /// <returns>新ID上限</returns>
        public static int GetID(SqlCommand cmd, string key, int step = 1)
        {
            int rslt = 0;
            cmd.CommandText = "UPDATE Sys_scIdentity SET ScIdentityno = ScIdentityno + @step, @curid = ScIdentityno + @step WHERE Tablename = @idkey";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@idkey", SqlDbType.VarChar).Value = key;
            cmd.Parameters.Add("@step", SqlDbType.Int).Value = step;
            cmd.Parameters.Add("@curid", SqlDbType.Int).Direction = ParameterDirection.Output;
            int nrows = cmd.ExecuteNonQuery();
            if (nrows == 0)
            {
                rslt = 10 + step;
                cmd.CommandText = "INSERT INTO Sys_scIdentity(Tablename, ScIdentityno) VALUES(@idkey, @curid)";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@idkey", SqlDbType.VarChar).Value = key;
                cmd.Parameters.Add("@curid", SqlDbType.Int).Value = rslt;
                cmd.ExecuteNonQuery();
            }
            else
            {
                rslt = Convert.ToInt32(cmd.Parameters["@curid"].Value);
            }
            return rslt;
        }

        /// <summary>
        /// 初始化超级用户
        /// </summary>
        /// <param name="constr">数居库连接字符串</param>
        public static void InitUser(string constr)
        {
            using (var con = new SqlConnection(constr))
            using (var cmd = con.CreateCommand())
            {
                con.Open();
                using (cmd.Transaction = con.BeginTransaction())
                {
                    try
                    {
                        var user = new st_user {userid = 11};
                        if (DbSqlHelper.SelectOne(cmd, user, "usercode") != 1)
                        {
                            var id = GetID(cmd, "st_user");
                            user.userid = id;
                            user.usercode = "super";
                            user.username = "超级用户";
                            user.psw = DESEncrypt.Encrypt("super", "BC493812B6664BECBF44C21C3BB043C4");
                            user.sex = "男";
                            user.tel = string.Empty;
                            user.dscrp = string.Empty;
                            user.opemp = "初始化生成";
                            user.opdate = DateTime.Now;
                            user.modemp = "初始化生成";
                            user.moddate = DateTime.Now;
                            DbSqlHelper.InsertOrUpdate(cmd, user, "userid,usercode,username,psw,sex,tel,dscrp,opemp,opdate,modemp,moddate");
                            var powers = new Power().GetAllPowers();
                            var userPower = new st_user_power { userid = user.userid };
                            foreach (var power in powers)
                            {
                                userPower.funid = power.funid;
                                DbSqlHelper.Insert(cmd, userPower, "userid, funid");
                            }
                        }
                        cmd.Transaction.Commit();
                    }
                    catch (Exception e)
                    {
                        cmd.Transaction.Rollback();
                        Trace.Write("初始化super用户数据失败："+e.ToString());
                    }
                }
            }
        }



        /// <summary>
        /// 获取用户自定义值
        /// </summary>
        /// <param name="empid">用户ID</param>
        /// <param name="dwname">窗口名</param>
        /// <param name="itemname">表格名</param>
        /// <param name="defaultvalue">默认值</param>
        /// <returns></returns>
        public static string GetValue(SqlCommand cmd, int empid, string dwname, string itemname, string defaultvalue, bool compress = false)
        {
            try
            {
                cmd.CommandText = "SELECT itemvalue FROM sys_user_filestring WHERE empid = @empid AND dwname = @dwname AND itemname = @itemname";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@empid", empid);
                cmd.Parameters.AddWithValue("@dwname", dwname ?? string.Empty);
                cmd.Parameters.AddWithValue("@itemname", itemname ?? string.Empty);
                var val = defaultvalue;
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        val = Convert.ToString(reader["itemvalue"]);
                    }
                }

                if (compress)
                {
                    val = Uncompress(val);
                }
                else if (val.StartsWith("H4sIAAAA"))
                {
                    val = Uncompress(val);
                    SetValue(cmd, empid, dwname, itemname, val);
                }
                return val;
            }
            catch (Exception ex)
            {
                Trace.Write(ex);
                return defaultvalue;
            }
        }


        /// <summary>
        /// 设置用户自定义值
        /// </summary>
        /// <param name="empid">用户ID</param>
        /// <param name="dwname">窗口名</param>
        /// <param name="itemname">表格名</param>
        /// <param name="strvalue">保存值</param>
        /// <returns></returns>
        public static bool SetValue(SqlCommand cmd, int empid, string dwname, string itemname, string strvalue, bool compress = false)
        {
            try
            {
                var newvalue = strvalue;
                if (compress)
                {
                    newvalue = Compress(strvalue);
                }
                cmd.CommandText = "UPDATE sys_user_filestring SET itemvalue = @itemvalue WHERE empid = @empid AND dwname = @dwname AND itemname = @itemname";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@empid", empid);
                cmd.Parameters.AddWithValue("@dwname", dwname ?? string.Empty);
                cmd.Parameters.AddWithValue("@itemname", itemname ?? string.Empty);
                cmd.Parameters.AddWithValue("@itemvalue", newvalue);

                var nrows = cmd.ExecuteNonQuery();
                if (nrows == 0)
                {
                    cmd.CommandText = "INSERT INTO sys_user_filestring(empid, dwname, itemname, itemvalue) VALUES(@empid, @dwname, @itemname, @itemvalue)";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@empid", empid);
                    cmd.Parameters.AddWithValue("@dwname", dwname ?? string.Empty);
                    cmd.Parameters.AddWithValue("@itemname", itemname ?? string.Empty);
                    cmd.Parameters.AddWithValue("@itemvalue", newvalue);
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                Trace.Write(strvalue, "错误参数");
                Trace.Write(ex);
                return false;
            }
        }

        private static string Compress(string value)
        {
            using (var ms = new MemoryStream())
            using (var gzip = new GZipStream(ms, CompressionMode.Compress, true))
            using (var writer = new BinaryWriter(gzip))
            {
                writer.Write(value);
                writer.Flush();
                gzip.Close();
                var data = ms.ToArray();
                return Convert.ToBase64String(data);
            }
        }

        private static string Uncompress(string value)
        {
            var data = Convert.FromBase64String(value);
            using (var ms = new MemoryStream(data))
            using (var gzip = new GZipStream(ms, CompressionMode.Decompress, true))
            using (var reader = new BinaryReader(gzip))
            {
                return reader.ReadString();
            }
        }

        /// <summary>
        /// 删除某人的布局方案
        /// </summary>
        public static void delLayout(SqlCommand cmd, int empid, string dwname, string itemname)
        {
            cmd.CommandText = @"delete sys_user_filestring where empid = @empid and dwname = @dwname and  itemname = @itemname";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@empid", empid);
            cmd.Parameters.AddWithValue("@dwname", dwname);
            cmd.Parameters.AddWithValue("@itemname", itemname);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 获取ERP连接
        /// </summary>
        /// <returns></returns>
        /// <exception cref="LJCommonException"></exception>
        [Obsolete("禁用，不能直连ERP")]
        public static string GetERPConnectString(SqlCommand cmd)
        {
            var outerconnection = new u_outerconnection();
            // 获取ERP数据库连接信息
            if (DbSqlHelper.SelectOne(cmd, "u_outerconnection", "connectiontype = 101", null, outerconnection, "servername,databasename,logid,logpass") <= 0)
            {
                throw new LJCommonException("获取新数据库连接信息失败,请检查!");
            }

            var builder = new SqlConnectionStringBuilder();
            builder.DataSource = outerconnection.servername;
            builder.InitialCatalog = outerconnection.databasename;
            builder.UserID = outerconnection.logid;
            builder.Password = outerconnection.logpass;

            using (var con = new SqlConnection(builder.ConnectionString))
            {
                try
                {
                    con.Open();
                }
                catch (SqlException ex)
                {
                    throw new LJCommonException($"数据库连接失败: {ex.Message}");
                }
            }

            return builder.ConnectionString;
        }
    }
}