|
|
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(); }
}
}
|
