using Newtonsoft.Json;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace DingDingMsg_MES
|
{
|
|
public class DBHelper
|
{
|
private SqlConnection MainCn;//创建连接对象
|
SqlTransaction MainTran;
|
public string sServer;
|
public string sDataBase;
|
public string sUser;
|
public string sPassword;
|
//开始事务
|
public void BeginTran()
|
{
|
if (!this.CnOpen())
|
return;
|
MainTran = MainCn.BeginTransaction();
|
}
|
//结束事务
|
public void Commit()
|
{
|
MainTran.Commit();
|
CnClose();
|
}
|
//回滚事务
|
public void RollBack()
|
{
|
MainTran.Rollback();
|
CnClose();
|
}
|
public DBHelper()
|
{
|
sServer = "47.96.97.237,15127";
|
sDataBase = "HX_LMESsys";
|
sUser = "HX_USER";
|
sPassword = "lc@841022";
|
////斯莫尔
|
//sServer = "30.10.7.6";
|
//sDataBase = "HX_LMESsys_test";
|
//sUser = "HX_USER";
|
//sPassword = "lc@841022";
|
}
|
|
private bool CnOpen()//OPEN数据库连接
|
{
|
if (sServer == "" || sServer == "没有找到！")
|
{
|
throw new Exception("错误的服务器名!" + System.Environment.CurrentDirectory);
|
}
|
if (sDataBase == "" || sDataBase == "没有找到！")
|
{
|
throw new Exception("错误的数据库名!");
|
}
|
if (MainCn == null)
|
{
|
MainCn = new SqlConnection("Data Source=" + sServer + ";DataBase=" + sDataBase + ";User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
|
}
|
if (MainCn.State == System.Data.ConnectionState.Closed)
|
{
|
try
|
{
|
MainCn.Open();
|
return true;
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message + "," + sServer + "," + sDataBase);
|
}
|
}
|
else
|
return true;
|
}
|
|
public void CnClose()//关闭数据库连接
|
{
|
if (MainCn != null)
|
MainCn.Close();
|
}
|
|
public void CnDispose()//释放资源
|
{
|
if (MainCn != null)
|
{
|
MainCn.Dispose();
|
MainCn = null;
|
}
|
}
|
|
/// <summary>
|
/// 执行SQL无返回值
|
/// </summary>
|
/// <param name="procName"></param>
|
/// <param name="sErrMessage"></param>
|
/// <returns></returns>
|
public void RunProc(string procName)
|
{
|
if (!this.CnOpen())
|
{
|
return;
|
//sErrMessage = "连接数据库失败！";
|
}
|
try
|
{
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandTimeout = 600;
|
cmd.Transaction = MainTran;
|
cmd.ExecuteNonQuery();
|
return;
|
}
|
catch (Exception e)
|
{
|
|
throw (e);
|
//return;
|
}
|
}
|
|
/// <summary>
|
/// 执行
|
/// </summary>
|
/// <param name="procName"></param>
|
/// <param name="sErrMessage"></param>
|
/// <returns></returns>
|
public void RunProc(string procName, ref string sErr)
|
{
|
if (!this.CnOpen())
|
{
|
sErr = "连接数据库失败！";
|
return;
|
|
}
|
try
|
{
|
//ClsPub.sSQLInfo = procName;
|
SqlCommand cmd = new SqlCommand(procName, MainCn);
|
cmd.CommandTimeout = 600;
|
cmd.Transaction = MainTran;
|
cmd.ExecuteNonQuery();
|
return;
|
}
|
catch (Exception e)
|
{
|
sErr = e.Message;
|
//ClsPub.sErrInfo = e.Message;
|
throw (e);
|
//return;
|
}
|
}
|
/// <summary>
|
/// 递入参数执行SQL并返回DATASET
|
/// </summary>
|
/// <param name="procName">执行语句</param>
|
/// <param name="prams">参数</param>
|
/// <param name="tbName">表名</param>
|
/// <param name="sErrMessage">异常信息</param>
|
/// <returns></returns>
|
public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
|
{
|
SqlDataAdapter dap = CreateDataAdaper(procName, prams);
|
if (dap == null)
|
return null;
|
DataSet ds = new DataSet();
|
try
|
{
|
//ClsPub.sSQLInfo = procName;
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
//ClsPub.sErrInfo = e.Message;
|
throw (e);
|
return null;
|
}
|
}
|
/// <summary>
|
/// 执行SQL并返回DATASET
|
/// </summary>
|
/// <param name="procName">执行语句</param>
|
/// <param name="tbName">表名</param>
|
/// <param name="sErrMessage">异常信息</param>
|
/// <returns></returns>
|
public DataSet RunProcReturn(string procName, string tbName)
|
{
|
SqlDataAdapter dap = CreateDataAdaper(procName, null);
|
DataSet ds = new DataSet();
|
try
|
{
|
//ClsPub.sSQLInfo = procName;
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
//ClsPub.sErrInfo = e.Message;
|
throw (e);
|
return null;
|
}
|
}
|
/// <summary>
|
/// 执行SQL并返回DATASET和错误信息
|
/// </summary>
|
/// <param name="procName">执行语句</param>
|
/// <param name="tbName">表名</param>
|
/// <param name="sErrMessage">异常信息</param>
|
/// <returns></returns>
|
public DataSet RunProcReturn(string procName, string tbName, ref string sErr)
|
{
|
SqlDataAdapter dap = CreateDataAdaper(procName, null);
|
DataSet ds = new DataSet();
|
try
|
{
|
//ClsPub.sSQLInfo = procName;
|
dap.Fill(ds, tbName);
|
return ds;
|
}
|
catch (Exception e)
|
{
|
sErr = e.Message;
|
//ClsPub.sErrInfo = e.Message;
|
return null;
|
}
|
}
|
|
private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams)
|
{
|
if (this.CnOpen())
|
{
|
SqlDataAdapter dap = new SqlDataAdapter(procName, MainCn);
|
dap.SelectCommand.CommandType = CommandType.Text;
|
dap.SelectCommand.CommandTimeout = 600;
|
dap.SelectCommand.Transaction = MainTran;
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
dap.SelectCommand.Parameters.Add(parameter);
|
}
|
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
|
ParameterDirection.ReturnValue, false, 0, 0,
|
string.Empty, DataRowVersion.Default, null));
|
return dap;
|
}
|
else
|
{
|
return null;
|
}
|
}
|
public static Int64 isLong(object message)
|
{
|
try
|
{
|
return Convert.ToInt64(message);
|
}
|
catch (Exception e)
|
{
|
return 0;
|
}
|
}
|
public static string isStrNull(object message)
|
{
|
try
|
{
|
if (message == null)
|
{
|
return "";
|
}
|
else
|
{
|
return message.ToString().Trim();
|
}
|
}
|
catch (Exception e)
|
{
|
return "";
|
}
|
}
|
|
public static int isInt(object message)
|
{
|
try
|
{
|
return Convert.ToInt16(message);
|
}
|
catch (Exception e)
|
{
|
return 0;
|
}
|
}
|
|
public Int64 CreateBillID_Prod(string BillCode, ref string sReturn)
|
{
|
try
|
{
|
string BillType = "";
|
DataSet Ds;
|
Int64 lID;
|
Ds = RunProcReturn("exec h_p_Xt_GetMaxBillID '" + BillCode + "'", "h_p_Xt_GetMaxBillID");
|
if (Ds == null || Ds.Tables[0].Rows.Count == 0)
|
{
|
lID = -1;
|
}
|
else
|
{
|
lID = DBHelper.isLong(Ds.Tables[0].Rows[0]["HBillNo"]);
|
}
|
CnClose();
|
CnDispose();
|
Ds = null;
|
return lID;
|
}
|
catch (Exception e)
|
{
|
return -2;
|
}
|
}
|
|
//得到最大单据号
|
public string CreateBillCode(string BillCode, ref string sReturn, bool Add)
|
{
|
Int64 BillCodeMode;
|
string Profix;
|
Int64 Glida;
|
Int32 CodeLen;
|
string sBillNo = "";
|
char c = Convert.ToChar("0");
|
string sKjYear = DateTime.Today.Year.ToString();
|
string sPeriod = DateTime.Today.Month.ToString();
|
string sDay = DateTime.Today.Day.ToString();
|
//得到单据号表 信息
|
DataSet Ds = new DataSet();
|
Ds = RunProcReturn("Select * from Gy_BillNumber where BillCode='" + BillCode.Trim() + "'", "Gy_BillNumber");
|
if (Ds.Tables[0].Rows.Count != 0)
|
{
|
BillCodeMode = DBHelper.isLong(Ds.Tables[0].Rows[0]["BillCodeMode"].ToString());
|
Profix = DBHelper.isStrNull(Ds.Tables[0].Rows[0]["Profix"].ToString());
|
Glida = DBHelper.isLong(Ds.Tables[0].Rows[0]["Glida"].ToString());
|
CodeLen = DBHelper.isInt(Ds.Tables[0].Rows[0]["CodeLen"].ToString());
|
}
|
else
|
{
|
return "";
|
}
|
//
|
string sStr;
|
switch (BillCodeMode)
|
{
|
case 0:
|
switch (Glida)
|
{
|
case 0://纯流水号
|
Ds = RunProcReturn("Select * from Gy_MaxNum where BillCode='" + BillCode.Trim() + "'", "Gy_MaxNum");
|
if (Ds.Tables[0].Rows.Count != 0)
|
{
|
sStr = new string(c, CodeLen - (Ds.Tables[0].Rows[0]["NowNumBer"].ToString()).Length);
|
sBillNo = Profix.Trim() + sStr + Ds.Tables[0].Rows[0]["NowNumBer"].ToString().Trim();
|
}
|
else //插入新记录
|
{
|
sStr = new string(c, CodeLen - 1);
|
RunProc("insert into Gy_MaxNum(BillCode,NowNumber) values('" + BillCode.Trim() + "',1)");
|
sBillNo = Profix.Trim() + sStr + 1;
|
}
|
if (Add == true)
|
{
|
RunProc("update Gy_Maxnum set NowNumBer=NowNumBer+1 where BillCode='" + BillCode.Trim() + "'");
|
}
|
return sBillNo;
|
|
case 1://根据 YY年+MM月 日 + 流水号 生成单据号 （日 采用了 仓库的字段）
|
sKjYear = sKjYear.Substring(2, 2);
|
sPeriod = "0" + sPeriod;
|
sPeriod = sPeriod.Substring(sPeriod.Length - 2, 2);
|
sDay = "";
|
Ds = RunProcReturn("Select * from Gy_MaxNum where BillCode='" + BillCode.Trim() + "' and KjYear='" + sKjYear + "' and Period='" + sPeriod + "' and whcode='" + sDay + "'", "Gy_MaxNum");
|
if (Ds.Tables[0].Rows.Count != 0)
|
{
|
sStr = new string(c, CodeLen - (Ds.Tables[0].Rows[0]["NowNumBer"].ToString()).Length);
|
sBillNo = Profix.Trim() + sKjYear + sPeriod + sDay + sStr + Ds.Tables[0].Rows[0]["NowNumBer"].ToString().Trim();
|
}
|
else //插入新记录
|
{
|
sStr = new string(c, CodeLen - 1);
|
RunProc("insert into Gy_Maxnum(BillCode,Kjyear,Period,whcode,NowNumber) values('" + BillCode.Trim() + "','" + sKjYear + "','" + sPeriod + "','" + sDay + "',1)");
|
sBillNo = Profix.Trim() + sKjYear + sPeriod + sDay + sStr + "1";
|
}
|
if (Add == true)
|
{
|
RunProc("update Gy_Maxnum set NowNumBer=NowNumBer+1 where BillCode='" + BillCode.Trim() + "' and KjYear='" + sKjYear + "' and Period='" + sPeriod + "' and whcode='" + sDay + "'");
|
}
|
return sBillNo;
|
case 2: //根据年+月+日 生成单据号
|
Ds = RunProcReturn("Select * from Gy_MaxNum where BillCode='" + BillCode.Trim() + "' and KjYear='" + sKjYear + "' and Period='" + sPeriod + "' and whcode='" + sDay + "'", "Gy_MaxNum");
|
if (Ds.Tables[0].Rows.Count != 0)
|
{
|
sStr = new string(c, CodeLen - (Ds.Tables[0].Rows[0]["NowNumBer"].ToString()).Length);
|
sBillNo = Profix.Trim() + sKjYear + DBHelper.isStrNull(("0" + sPeriod)).Substring(DBHelper.isStrNull(("0" + sPeriod)).Length - 2, 2) + DBHelper.isStrNull(("0" + sDay)).Substring(DBHelper.isStrNull(("0" + sDay)).Length - 2, 2) + sStr + Ds.Tables[0].Rows[0]["NowNumBer"].ToString().Trim();
|
}
|
else //插入新记录
|
{
|
sStr = new string(c, CodeLen - 1);
|
RunProc("insert into Gy_Maxnum(BillCode,Kjyear,Period,whcode,NowNumber) values('" + BillCode.Trim() + "','" + sKjYear + "','" + sPeriod + "','" + sDay + "',1)");
|
sBillNo = Profix.Trim() + sKjYear + DBHelper.isStrNull(("0" + sPeriod)).Substring(DBHelper.isStrNull(("0" + sPeriod)).Length - 2, 2) + DBHelper.isStrNull(("0" + sDay)).Substring(DBHelper.isStrNull(("0" + sDay)).Length - 2, 2) + sStr + 1;
|
}
|
if (Add == true)
|
{
|
RunProc("update Gy_Maxnum set NowNumBer=NowNumBer+1 where BillCode='" + BillCode.Trim() + "' and KjYear='" + sKjYear + "' and Period='" + sPeriod + "' and whcode='" + sDay + "'");
|
}
|
return sBillNo;
|
|
|
default:
|
return sBillNo;
|
|
}
|
|
case 1:
|
return sBillNo;
|
|
default:
|
return sBillNo;
|
|
}
|
CnClose();
|
CnDispose();
|
}
|
|
private static readonly object lockObj = new object();
|
//写日志
|
public static void CustomWriteLog(object obj, string FileName, string filePath = "Vlog", bool isAppend = true)
|
{
|
try
|
{
|
lock (lockObj)
|
{
|
filePath = $@"{filePath}\{FileName}.txt";
|
|
filePath = AppDomain.CurrentDomain.BaseDirectory + filePath;
|
|
if (!System.IO.Directory.Exists(Path.GetDirectoryName(filePath)))
|
{
|
System.IO.Directory.CreateDirectory(Path.GetDirectoryName(filePath));
|
}
|
|
bool fileExists = System.IO.File.Exists(filePath);
|
//不存在 则创建该文件
|
if (!fileExists)
|
{
|
System.IO.File.Create(filePath).Close();
|
}
|
|
using (StreamWriter writer = new StreamWriter(filePath, isAppend))
|
{
|
//存在的时候才写一行
|
if (fileExists && isAppend)
|
{
|
writer.WriteLine();
|
}
|
|
var content = obj is string ? obj : JsonConvert.SerializeObject(obj);
|
writer.WriteLine($"{DateTime.Now} {content}");
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
}
|
}
|
}
|
}
|
