IcsFromERPJob/ICSSoft.FromERP/Data/ICSHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;


using System.Threading.Tasks;
using System.Security.Cryptography;
//using NFine.Data.Extensions;


namespace ICSSoft.FromERP
{
    public  class ICSHelper
    {
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public static string GetConnectString()
        {
            try
            {
                string connectionString = GetConfigString("SysConnectionString");
                return connectionString;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static string GetPLMConnectString()
        {
            try
            {
                string connectionString = GetConfigString("SysPlmConnectionString");
                return connectionString;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static string GetERPConnectString()
        {
            try
            {
                string connectionString = GetConfigString("SysErpConnectionString");
                return connectionString;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static string GetConfigString(string name)
        {
            try
            {
                Configuration config = GetConfig();
                string configString = config.ConnectionStrings.ConnectionStrings[name].ConnectionString.ToString();
                return configString;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static Dictionary<string, string> GetConfigString()
        {
            try
            {
                Dictionary<string, string> dictionary = new Dictionary<string, string>();
                Configuration config = GetConfig();
                var settings = config.AppSettings.Settings;
                foreach (var key in settings.AllKeys)
                {
                    string value = settings[key].Value.ToString();
                    dictionary.Add(key.ToString(), value);
                }
                return dictionary;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static Configuration GetConfig()
        {
            Assembly assembly = Assembly.GetCallingAssembly();
            string path = string.Format("{0}.config", assembly.Location);
            if (!File.Exists(path))
            {
                throw new FileNotFoundException(path + "路径下的文件未找到！");
            }
            try
            {
                ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
                configFile.ExeConfigFilename = path;
                Configuration config = ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
                return config;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static void Log(string name)
        {
            string procName = Process.GetCurrentProcess().ProcessName;
            using (PerformanceCounter pc = new PerformanceCounter("Process", "Private Bytes", procName))
            {
                log.Warn(name + "-当前程序内存占用：" + pc.NextValue());
            }
            long memoryUsed = GC.GetTotalMemory(true);
            log.Warn(name + "-内存占用：" + memoryUsed);
        }

        /// <summary>
        /// BS MES
        /// </summary>
        /// <param name="Namespace"></param>
        /// <param name="ClassName"></param>
        /// <param name="TenantId"></param>
        /// <param name="sql"></param>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        public static string Time(string Namespace, string ClassName, string TenantId, string sql, string TempTableName)
        {
            try
            {
                string value = @"DECLARE @LastTime datetime='2000-01-01'
                                            --同步时间表不存在插入记录
                                            IF NOT EXISTS(SELECT ID FROM ICSERPTime WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}')
	                                            BEGIN
		                                            INSERT INTO ICSERPTime VALUES('{0}','{1}',@LastTime
                                                                                ,'','','{2}'
                                                                                ,@LastTime,'job',@LastTime,'job','job','job')
	                                            END
                                            --取出最后同步日期
                                            SELECT @LastTime=ModifyDate FROM ICSERPTime WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}' 
                                            --根据时间过滤所需数据放入临时表，必须要有MTIME字段
                                            {3}
                                            --从临时表取出最后修改日期
                                            SELECT @LastTime=MAX(MTIME) FROM {4} 
                                            --更新时间表同步日期
                                             UPDATE ICSERPTime SET ModifyDate=@LastTime,LastModificationTime=GETDATE(),LastModifierUserName='job'  ,LastModifierUserId='job'
WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}'
                                            ";
                return string.Format(value, Namespace, ClassName, TenantId, sql, TempTableName);
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }
        }

        /// <summary>
        /// CS MES
        /// </summary>
        /// <param name="Namespace"></param>
        /// <param name="Class"></param>
        /// <param name="WorkPoint"></param>
        /// <param name="sql"></param>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        public static string Time4Old(string Namespace, string Class, string WorkPoint, string sql, string TempTableName)
        {
            try
            {
                string value = @"DECLARE @LastTime datetime='2000-01-01'
                                            --同步时间表不存在插入记录
                                            IF NOT EXISTS(SELECT ID FROM ICSERPTime WHERE Namespace='{0}' AND Class='{1}' AND WorkPoint='{2}')
	                                            BEGIN
		                                            INSERT INTO ICSERPTime VALUES('{0}','{1}',CONVERT(VARCHAR(10),@LastTime,23),CONVERT(VARCHAR(10),@LastTime,24),GETDATE(),'{2}',@LastTime)
	                                            END
                                            --取出最后同步日期
                                            SELECT @LastTime=DateTime FROM ICSERPTime WHERE Namespace='{0}' AND Class='{1}' AND WorkPoint='{2}' 
                                            --根据时间过滤所需数据放入临时表，必须要有MTIME字段
                                            {3}
                                            --从临时表取出最后修改日期
                                            SELECT @LastTime=MAX(MTIME) FROM {4} 
                                            --更新时间表同步日期
                                            UPDATE ICSERPTime SET Date=CONVERT(VARCHAR(10),@LastTime,23),Time=CONVERT(VARCHAR(10),@LastTime,24),DateTime=@LastTime,MTIME=GETDATE()  WHERE Namespace='{0}' AND Class='{1}' AND WorkPoint='{2}'
                                            ";
                return string.Format(value, Namespace, Class, WorkPoint, sql, TempTableName);
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }
        }

        public static string InsertSQL(string TableName, Dictionary<string, string> values)
        {
            try
            {
                string col = string.Empty;
                string value = string.Empty;
                foreach (var item in values)
                {
                    col += item.Key + ",";
                    value += item.Value + ",";
                }
                if (!string.IsNullOrWhiteSpace(value))
                {
                    return string.Format("INSERT INTO  {0} ({1}) SELECT {2} FROM ", TableName, col.TrimEnd(','), value.TrimEnd(','));
                }
                return value;
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }
        }
        public static string UpdateSQL(string TableName, Dictionary<string, string> values)
        {
            try
            {
                string value = string.Empty;
                foreach (var item in values)
                {
                    value += item.Key +"="+ item.Value + ",";
                }
                if (!string.IsNullOrWhiteSpace(value))
                {
                    return string.Format("UPDATE {0} SET {1} FROM ", TableName, value.TrimEnd(','));
                }
                return "";
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }
        }

        public static void ExecuteDate(string conStr, string sql)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    con.Open();
                    try
                    {
                        Dictionary<string, string> dictionary = new Dictionary<string, string>();
                        using (SqlTransaction tran = con.BeginTransaction())
                        {
                            using (SqlCommand command = new SqlCommand())
                            {
                                command.Connection = con;
                                command.Transaction = tran;
                                command.CommandTimeout = 100;
                                command.CommandText = sql;
                                try
                                {
                                    int result = command.ExecuteNonQuery();
                                    tran.Commit();
                                }
                                catch (Exception ex)
                                {
                                    tran.Rollback();
                                    log.Error( ex.ToString() + Environment.NewLine + "异常SQL：" + Environment.NewLine + sql);
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        log.Error(ex.ToString());
                        throw ex;
                    }
                    finally
                    {
                        if (con.State == ConnectionState.Open)
                            con.Close();
                        con.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw ex;
            }
        }

        /// <summary>  
        /// 获取拼音  
        /// </summary>  
        /// <param name="str"></param>  
        /// <returns></returns>  
        public static string GetPYString(string str)
        {
            string tempStr = "";
            foreach (char c in str)
            {
                if ((int)c >= 33 && (int)c <= 126)
                {//字母和符号原样保留     
                    tempStr += c.ToString();
                }
                else
                {//累加拼音声母     
                    tempStr += GetPYChar(c.ToString());
                }
            }
            return tempStr;
        }
        ///      
        /// 取单个字符的拼音声母     
        ///      
        /// 要转换的单个汉字     
        /// 拼音声母     
        public static string GetPYChar(string c)
        {
            byte[] array = new byte[2];
            array = System.Text.Encoding.Default.GetBytes(c);
            int i = (short)(array[0] - '\0') * 256 + ((short)(array[1] - '\0'));
            if (i < 0xB0A1) return "*";
            if (i < 0xB0C5) return "a";
            if (i < 0xB2C1) return "b";
            if (i < 0xB4EE) return "c";
            if (i < 0xB6EA) return "d";
            if (i < 0xB7A2) return "e";
            if (i < 0xB8C1) return "f";
            if (i < 0xB9FE) return "g";
            if (i < 0xBBF7) return "h";
            if (i < 0xBFA6) return "j";
            if (i < 0xC0AC) return "k";
            if (i < 0xC2E8) return "l";
            if (i < 0xC4C3) return "m";
            if (i < 0xC5B6) return "n";
            if (i < 0xC5BE) return "o";
            if (i < 0xC6DA) return "p";
            if (i < 0xC8BB) return "q";
            if (i < 0xC8F6) return "r";
            if (i < 0xCBFA) return "s";
            if (i < 0xCDDA) return "t";
            if (i < 0xCEF4) return "w";
            if (i < 0xD1B9) return "x";
            if (i < 0xD4D1) return "y";
            if (i < 0xD7FA) return "z";
            return "*";
        }

        /// <summary>
        /// 返回一个数据表
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">参数</param>
        /// <param name="strTableName">数据表名</param>
        /// <param name="bIsLoadStru">是否加载数据表结构</param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string  connection, string cmdText)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connection))
                {
                    con.Open();
                    try
                    {
                        Dictionary<string, string> dictionary = new Dictionary<string, string>();
                       
                            using (SqlCommand command = new SqlCommand())
                            {
                                command.CommandText = cmdText;
                              
                                DataSet ds = new DataSet();
                                    SqlDataAdapter da = new SqlDataAdapter(command);
                                da.SelectCommand.Connection = con; //加上这个
                                                                    //da.FillSchema(ds, SchemaType.Source);

                                da.Fill(ds);
                      return ds.Tables[0];
                            }
                     
                       
                    }
                    catch (Exception ex)
                    {
                        log.Error(ex.ToString());
                        throw ex;
                    }
                    finally
                    {
                        if (con.State == ConnectionState.Open)
                            con.Close();
                        con.Dispose();
                    }
                }
               
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw ex;
            }

           

        }

        /// <summary>
        /// BS MES
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public static DataTable GetERPDB(string conStr)
        {
            try
            {
                string value = @"SELECT *  FROM SysWorkPoint";
                return ExecuteTable(conStr, value);
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }
        }

        /// <summary>
        /// CS MES
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public static DataTable GetOldERPDB(string conStr)
        {
            try
            {
                string value = @"
SELECT *  FROM Sys_WorkPoint";
                return ExecuteTable(conStr, value);
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }
        }

        public static object ExecuteScalar(string connString, string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                object obj = null;
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    //if (spArr.Length > 0)
                    //    cmd.Parameters.AddRange(spArr.SetDBNull());
                     obj = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    log.Error(ex.ToString());
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    //conn.Dispose();
                }
                return obj;
            }
        }

        public static DateTime GetTime(string Conn, string Namespace, string ClassName, string TenantId)
        {

            try
            {
                string value = @"DECLARE @LastTime datetime='2000-01-01'
                                            --同步时间表不存在插入记录
                                            IF NOT EXISTS(SELECT ID FROM ICSERPTime WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}')
	                                            BEGIN
		                                            INSERT INTO ICSERPTime VALUES('{0}','{1}',@LastTime
                                                                                ,'','','{2}'
                                                                                ,@LastTime,'job',@LastTime,'job','job','job')
	                                            END
                                            --取出最后同步日期
                                            SELECT @LastTime=ModifyDate FROM ICSERPTime WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}' 
                                            --根据时间过滤所需数据放入临时表，必须要有MTIME字段
                                            Select @LastTime
                                            ";
                return ExecuteScalar(Conn, string.Format(value, Namespace, ClassName, TenantId)).ToDateTime();
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }

        }

        public static void UpdateTime(string Conn, string Namespace, string ClassName, string TenantId, DateTime time)
        {

            try
            {
                string value = @"DECLARE @LastTime datetime='2000-01-01'
                                            --同步时间表不存在插入记录
                                            IF NOT EXISTS(SELECT ID FROM ICSERPTime WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}')
	                                            BEGIN
		                                            INSERT INTO ICSERPTime VALUES('{0}','{1}',@LastTime
                                                                                ,'','','{2}'
                                                                                ,@LastTime,'job',@LastTime,'job','job','job')
	                                            END
                                            select @LastTime='{3}'
                                            --取出最后同步日期
                                UPDATE ICSERPTime SET ModifyDate=@LastTime,LastModificationTime=GETDATE(),LastModifierUserName='job'  ,LastModifierUserId='job'
                                WHERE Namespace='{0}' AND ClassName='{1}' AND TenantId='{2}'
                                            ";
                ExecuteScalar(Conn, string.Format(value, Namespace, ClassName, TenantId, time.ToStringBz()));
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                throw;
            }

        }

        public static void ExecuteDateNew(string conStr, string sql)
        {

            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                try
                {
                    Dictionary<string, string> dictionary = new Dictionary<string, string>();
                    using (SqlTransaction tran = con.BeginTransaction())
                    {
                        using (SqlCommand command = new SqlCommand())
                        {
                            command.Connection = con;
                            command.Transaction = tran;
                            command.CommandTimeout = 100;
                            command.CommandText = sql;
                            try
                            {
                                int result = command.ExecuteNonQuery();
                                tran.Commit();
                            }
                            catch (Exception ex)
                            {
                                tran.Rollback();
                                throw ex;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Error(ex.ToString() + Environment.NewLine + "异常SQL：" + Environment.NewLine + sql);
                    throw ex;
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                    con.Dispose();
                }
            }
        }

        public static string ApiSign(string app_id, string app_secret, long timestamp)
        {
            return ToMd5($"app_id{app_id}app_secret{app_secret}_timestamp{timestamp}");
        }

        /// <summary>
        /// Md5
        /// </summary>
        /// <param name="txt"></param>
        /// <returns></returns>
        public static string ToMd5(string txt)
        {
            byte[] sor = Encoding.UTF8.GetBytes(txt);
            MD5 md5 = MD5.Create();
            byte[] result = md5.ComputeHash(sor);
            StringBuilder strbul = new StringBuilder(40);
            for (int i = 0; i < result.Length; i++)
            {
                //加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
                strbul.Append(result[i].ToString("x2"));
            }
            return strbul.ToString();
        }

       

    }


 

}