/// <summary>
/// 获取库存管理方式
///3-20 增加 zhang
/// </summary>
/// <param name="tag"></param>
/// <returns></returns>
public static int GetStockWay(string tag)
{
try
{
string sql = "";
if (tag == "原材料")
{
sql = "SELECT SetValue FROM dbo.B_ConfigureSys WHERE SetName = 'W_SteelNeedBarCode'";
}
else if (tag == "辅材")
{
sql = "SELECT SetValue FROM dbo.B_ConfigureSys WHERE SetName = 'W_HardwareNeedBarCode'";
}
else if (tag == "螺栓")
{
sql = "SELECT SetValue FROM dbo.B_ConfigureSys WHERE SetName = 'W_BoltNeedBarCode'";
}
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0].ToString()));
}
else
{
return(-1);
}
}
catch
{
return(-1);
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务,ArrayList数组方式实现。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
SqlConnection conn = YKDataClass.Connection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
conn.Close();
}
catch (SqlException E)
{
tx.Rollback();
conn.Close();
throw E;
}
}
///// <summary>
///// 即时发送
///// </summary>
///// <param name="eID">员工ID</param>
///// <param name="message">发送信息</param>
///// <returns></returns>
//public static bool Send(int eID, string message, string Type)
//{
// return true;
// //try
// //{
// // string ServerIP = (UserInfo .configSys .ContainsKey("B_FTPIP")) ? UserInfo .configSys["B_FTPIP"] .ToString() : "";
// // if (ServerIP == "")
// // {
// // return false;
// // }
// // DataTable dtIP = GetEmployeeIP(eID);
// // DataTable dtSelfPort = GetEmployeeIP(Com .UserInfo .eID);
// // string MySelfPort = dtSelfPort .Rows[0]["buPort"] .ToString();
// // if (dtIP != null && dtIP .Rows .Count > 0)
// // {
// // if (dtIP .Rows[0]["buIP"] .ToString() != "")
// // {
// // YKClassMsg msg = new YKClassMsg();
// // msg .Data = Encoding .Unicode .GetBytes(message);
// // msg .RID = Convert .ToString(eID);
// // msg .RIP = dtIP .Rows[0]["buIP"] .ToString();
// // msg .RPort = dtIP .Rows[0]["buPort"] .ToString();
// // msg .SID = Convert .ToString(Com .UserInfo .eID);
// // msg .SIP = YKGetClient .GetIP(); ;
// // msg .SPort = MySelfPort;
// // msg .msgID = Type;
// // Com .YKGetClient .Send(IPAddress .Parse(ServerIP), 9000, new YKClassSerializers() .SerializeBinary(msg) .ToArray());
// // }
// // }
// // return true;
// //}
// //catch (Exception e)
// //{
// // throw e;
// //}
//}
/// <summary>
/// 得到在线人员IP
/// </summary>
/// <param name="eID"></param>
/// <returns></returns>
public static DataTable GetEmployeeIP(int eID)
{
StringBuilder sbSql = new StringBuilder("SELECT ");
sbSql.Append("buIP,buPort FROM B_USERIP where eID=" + eID + " and Valid=1");
return(YKDataClass.getDataTable(sbSql.ToString()));
}
/// <summary>
/// 得到最大ID(有Parent的情况) ,位数自定，2013-2-2修改 张
/// </summary>
public static string GetMaxIdNoParentCount(string tableName, string filedName, string parent, int Count)
{
//Count = Count + parent.Length;
int sumCount = Count + parent.Length;
if (parent == "")
{
return(GetMaxIdNoParentCount(tableName, filedName, Count));
}
string sql = "declare @num varchar(" + sumCount + "); select @num='" + parent
+ "' +right('0000000000'," + Count + " - LEN(cast(MAX(substring(" + filedName + ","
+ Convert.ToString(parent.Length + 1) + ",len(" + filedName + ")-" + parent.Length + "))+1 as char(" + sumCount + "))))"
+ "+ cast(MAX(substring(" + filedName + "," + Convert.ToString(parent.Length + 1)
+ ",len(" + filedName + ")-" + parent.Length + "))+1 as char(" + sumCount + ")) from "
+ tableName + " where isnumeric(" + filedName + ")=1 and " + filedName + " like '" + parent + "%' AND LEN(" + filedName + ") = LEN('" + parent + "')+" + Count.ToString() + " ;if @num is null set @num='" + parent + "'+right('0000000000'," + Convert.ToString(Count - 1) + ")+'1';select @num";
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
else
{
return("");
}
}
/// <summary>
/// 判断操作人是否存在流程中
/// 2013-6-25 张
/// </summary>
/// <param name="table"></param>
/// <param name="key"></param>
/// <param name="value"></param>
/// <param name="eID"></param>
/// <returns></returns>
public static bool InFlow(string table, string key, int value, int eID)
{
string sql = " SELECT TOP 1 1 FROM dbo.B_ApproveMain AS bam INNER JOIN dbo.B_FlowApproveView AS bfa ON bam.fmID = bfa.fmID "
+ " WHERE amTable = '" + table + "' AND amKey = '" + key + "' AND amValue = " + value + " AND bfa.eID = " + eID.ToString();
return(YKDataClass.Exists(sql));
}
///<summary>执行多条SQL语句,实现数据库事务,并且最后一句有返回值</summary>
///<param name="htSql">泛型，SQL语句，用于包装插入顺序</param>
public static object SqlCommandTransForObject(List <string> htSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlTransaction txn = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
Hashtable ht = new Hashtable();
for (int i = 0; i < htSql.Count - 1; i++)
{
PrepareCommand(cmd, conn, txn, htSql[i], ht);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//conn.Close();
}
if (htSql.Count > 0)
{
cmd.CommandText = htSql[htSql.Count - 1];
object ob = cmd.ExecuteScalar();
txn.Commit();
conn.Close();
return(ob);
}
else
{
return(null);
}
}
catch { txn.Rollback(); conn.Close(); return(null); }
}
}
///<summary>执行多条SQL语句,实现数据库事务,如果有出错,则返回错误的行数</summary>
///<param name="htSql">泛型，SQL语句，用于包装插入顺序</param>
public static int SqlCommandTransInt(List <string> htSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlTransaction txn = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
int n = 0;
try
{
Hashtable ht = new Hashtable();
for (int i = 0; i < htSql.Count; i++)
{
PrepareCommand(cmd, conn, txn, htSql[i], ht);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
n++;
}
txn.Commit();
return(-1);
}
catch (Exception) { txn.Rollback(); return(n); }
finally
{
conn.Close();
}
}
}
/// <summary>
/// 得到电子公告信息
/// </summary>
/// <param name="eID"></param>
/// <returns></returns>
public static DataSet GetNewNotify(int eID)
{
Hashtable ht = new Hashtable();
ht.Add("@eID", eID);
return(YKDataClass.RunSqlProcForDataSet("SM_GetMessage", ht));
}
///<summary>执行多条SQL语句,实现数据库事务</summary>
///<param name="htSql">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
public static void SqlCommandTrans(Hashtable htSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlTransaction txn = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
foreach (DictionaryEntry de in htSql)
{
PrepareCommand(cmd, conn, txn, de.Key.ToString(), (SqlParameter[])de.Value);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//conn.Close();
}
txn.Commit();
}
catch { txn.Rollback(); throw; }
finally
{
conn.Close();
}
}
}
/// <summary>
/// 获取一张表的所有字段及类型长度
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static DataTable getTableColumns(string tableName)
{
string sql = "select a.name AS colName,a.length,b.name from syscolumns a INNER JOIN systypes b "
+ " ON a.xusertype=b.xusertype where a.id=object_id('" + tableName + "') ";
return(YKDataClass.getDataTable(sql));
}
//---------------------------------------------------------------------------------------
///<summary>执行一条计算查询结果语句,返回查询结果(object)</summary>
///<param name="strSql">计算查询结果语句</param>
///<returns>查询结果(object)</returns>
public static object GetSingle(string strSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlCommand cmd = new SqlCommand(strSql, conn))
{
try
{
object obj = cmd.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
return(null);
}
else
{
return(obj);
}
}
catch (SqlException e)
{
conn.Close();
throw e;
}
}
}
/// <summary>
/// 检查权限授权情况。返回true 则该菜单已经授权当然登录人允许操作
/// </summary>
/// <param name="alName">菜单类名，同权限表</param>
/// <param name="oldeName">原(离职人)姓名</param>
/// <returns>true / false 为true则该菜单已经授权当然登录人允许操作</returns>
public static bool CheckAuthorityEmpower(string alName, string oldeName)
{
if (UserInfo.eName.Equals(oldeName))
{
return(true);
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT TOP 1 1 FROM B_AuthorityEmpower m");
sb.Append(" INNER JOIN B_AuthorityEmpowerDetail d");
sb.Append(" ON d.aeID=m.aeID");
sb.Append(" WHERE d.isEmpower=1 AND m.[State]=99");
sb.Append(" AND d.alName='" + alName + "'");
sb.Append(" AND m.oldeName='" + oldeName + "'");
sb.Append(" AND m.neweID=" + UserInfo.eID);
if (YKDataClass.Exists(sb.ToString()))
{
return(true);
}
else
{
return(false);
}
}
}
///<summary>执行存储过程,实现数据库事务(2008.06.02 黄建忠修改)</summary>
///<param name="htSql">SQL语句的哈希表(key为sql语句,value是该语句的Hashtable)</param>
public static bool SqlCommandTrans2(Queue <YKParams <string, Hashtable> > htSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlTransaction txn = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int count = htSql.Count;
for (int i = 0; i < count; i++)
{
YKParams <string, Hashtable> prams = htSql.Dequeue();
if (prams.GetT.IndexOf(" ") > 0)
{
PrepareCommand(cmd, conn, txn, prams.GetT, prams.GetM);
}
else
{
PrepareCommand2(cmd, conn, txn, prams.GetT, prams.GetM);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
txn.Commit();
return(true);
}
catch { txn.Rollback(); return(false); }
finally
{
conn.Close();
}
}
}
/// <summary>
/// 运行带参数的sql语句,返回影响行数
/// </summary>
/// <param name="sql">带参数的sql语句</param>
/// <param name="ht">参数表</param>
/// <returns>影响行数</returns>
public static DataTable ExecuteParameterSqlForDatatable(string sql, Hashtable ht)
{
SqlConnection conn = YKDataClass.Connection();
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
if (ht != null)
{
foreach (string tKey in ht.Keys)
{
SqlParameter sp = new SqlParameter(tKey, ht[tKey]);
cmd.Parameters.Add(sp);
}
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
cmd.Parameters.Clear();
return(dt);
}
}
catch (SqlException e)
{ conn.Close(); throw e; }
}
/// <summary>
/// 获取规格转换值
/// </summary>
/// <param name="spec"></param>
/// <returns></returns>
public static decimal GetSpecValue(string spec)
{
string sql = " SELECT dbo.fGetSpecValue('" + spec + "')";
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
return(Convert.ToDecimal(dt.Rows[0][0]));
}
///<summary>执行多条SQL语句,实现数据库事务</summary>
///<param name="strSql">SQL语句</param>
public static bool SqlCommandTrans(string strSql)
{
SqlConnection conn = YKDataClass.Connection();
SqlTransaction txn = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand(strSql, conn, txn);
try { cmd.ExecuteNonQuery(); txn.Commit(); conn.Close(); return(true); }
catch (SqlException e) { txn.Rollback(); conn.Close(); throw e; }
}
/// <summary>
/// 判断是否设置了审批流程
/// </summary>
/// <returns></returns>
public static bool IfSetApproveFlow(string mName)
{
try
{
string sql = "SELECT TOP 1 1 FROM dbo.B_FlowMain AS bfm WHERE mName = '" + mName + "'";
return(YKDataClass.Exists(sql));
}
catch
{
return(false);
}
}
/// <summary>
/// 获取审批记录 3-14 修改 zhang
/// </summary>
/// <param name="table"></param>
/// <param name="key"></param>
/// <param name="value"></param>
/// <returns></returns>
public static DataTable GetApproveRecord(string table, string key, int value)
{
string sql = "SELECT bar.arID,bar.amID,bar.eID,bar.arTime,bar.arOpinion"
+ " ,CASE bar.arPass WHEN 1 THEN '同意' ELSE '不同意' END AS arPass"
+ " ,bar.flLevel,oe.eName"
+ " FROM dbo.B_ApproveRecord AS bar INNER JOIN dbo.OE_Employee AS oe ON bar.eID = oe.eID"
+ " INNER JOIN dbo.B_ApproveMain AS bam ON bar.amID = bam.amID"
+ " WHERE bam.amKey = '" + key + "' AND bam.amTable = '" + table + "' AND bam.amValue = " + value.ToString()
+ " AND arPass >= 0";
return(YKDataClass.getDataTable(sql));
}
///<summary>执行查询语句,返回SqlDataReader</summary>
///<param name="strSQL">查询语句</param>
///<returns>SqlDataReader</returns>
public static SqlDataReader SqlCommandForDataReader(string strSQL)
{
SqlConnection conn = YKDataClass.Connection();
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(dr);
}
catch (SqlException e) { conn.Close(); throw e; }
}
///<summary>执行SQL语句,返回记录数</summary>
///<param name="strSql">SQL语句</param>
///<returns>返回影响的记录数</returns>
public static int SqlCommand(string strSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlCommand cmd = new SqlCommand(strSql, conn))
{
cmd.CommandTimeout = 800;
try
{ return(cmd.ExecuteNonQuery()); }
catch (SqlException e)
{ conn.Close(); throw e; }
}
}
/// <summary>
/// 进行单据锁定
/// </summary>
/// <param name="tableName"></param>
/// <param name="col1name"></param>
/// <param name="col1"></param>
/// <param name="col2name"></param>
/// <param name="col2"></param>
/// <param name="idname"></param>
/// <param name="ID"></param>
/// <returns></returns>
public static bool setAudiorApprover(string tableName, string col1name, string col1, string col2name, string col2, string idname, int ID)
{
string sql = "UPDATE " + tableName + " set " + col1name + "='" + col1 + "'," + col2name + "='" + col2 + "' where " + idname + "=" + ID;
if (YKDataClass.SqlCommand(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获取服务器时间
/// </summary>
/// <returns></returns>
public static string getServerTime()
{
string sql = "select convert(varchar(50),getdate(),120)";
try
{
return(YKDataClass.GetSingle(sql).ToString());
//Service.Service service = new CS2008App.Service.Service();
//service.Url = Properties.Settings.Default.CS2008App_Service_Service;
//return service.Query(sql).Tables[0].Rows[0].ToString();
}
catch (Exception) { return(DateTime.Now.ToString()); }
}
/// <summary>
/// 获取仓库开始日期和结束日期 2012-8-30
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
public static String[] GetStartAndEndDate(string year, string month)
{
string[] array = new string[2];
int startDay = 0;
DataTable dt = new DataTable();
string dateStart = "";
string dateEnd = "";
string sql = "SELECT setValue FROM dbo.B_ConfigureSys WHERE setName = 'W_MonthStartDay'";
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count <= 0)
{
array[0] = "0";
array[1] = "0";
return(array);
}
else
{
startDay = Convert.ToInt32(dt.Rows[0][0].ToString());
if (startDay < 0)
{
startDay = -1 * startDay;
if (month == "1")
{
dateStart = Convert.ToString(Convert.ToInt32(year) - 1) + "-12-" + startDay.ToString();
dateEnd = Convert.ToDateTime(year + "-1-" + startDay.ToString()).AddDays(-1).ToString("yyyy-MM-dd");
}
else
{
dateStart = year + "-" + Convert.ToString(Convert.ToInt32(month) - 1) + "-" + startDay.ToString();
dateEnd = Convert.ToDateTime(year + "-" + month + "-" + startDay.ToString()).AddDays(-1).ToString("yyyy-MM-dd");
}
}
else
{
if (month == "12")
{
dateStart = year + "-12-" + startDay.ToString();
dateEnd = Convert.ToDateTime(Convert.ToString(Convert.ToInt32(year) + 1) + "-1-" + startDay.ToString()).AddDays(-1).ToString("yyyy-MM-dd");
}
else
{
dateStart = year + "-" + month + "-" + startDay.ToString();
dateEnd = Convert.ToDateTime(year + "-" + Convert.ToString(Convert.ToInt32(month) + 1) + "-" + startDay.ToString()).AddDays(-1).ToString("yyyy-MM-dd");
}
}
array[0] = dateStart;
array[1] = dateEnd;
}
return(array);
}
/// <summary>
/// 执行一条Sql语句，返回一个object
/// </summary>
/// <param name="sql">sql语句</param>
public static object SqlCommandForObject(string strSql)
{
SqlConnection conn = YKDataClass.Connection();
using (SqlCommand cmd = new SqlCommand(strSql, conn))
try
{
object ob = cmd.ExecuteScalar();
conn.Close();
return(ob);
}
catch (SqlException e)
{ conn.Close(); throw e; }
}
///<summary>执行存储过程,返回sqlDataReader</summary>
///<param name="storedProcName">存储过程名</param>
///<param name="parameters">存储过程参数</param>
public static SqlDataReader RunSqlProcForDataReader(string storedProcName, Hashtable ht)
{
SqlConnection conn = YKDataClass.Connection();
try
{
SqlCommand cmd = BuildQueryCommand(conn, storedProcName, ht);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader sr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(sr);
}
catch (SqlException e)
{ conn.Close(); throw e; }
}
/// <summary>
/// 锁定与解锁
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">主键名</param>
/// <param name="pkValue">主键值</param>
/// <param name="Auditor">锁定人</param>
/// <param name="AorU">1为锁定，-1为解锁</param>
/// <returns></returns>
public static int AuditAndUnAudit(string tableName, string pkName, int pkValue, string Auditor, int AorU)
{
string sql = "";
if (AorU == 1)
{
sql = "UPDATE " + tableName + " SET Locker = '" + Auditor + "' ,LockDate = GETDATE() WHERE " + pkName + " = " + pkValue.ToString();
}
else if (AorU == -1)
{
sql = "UPDATE " + tableName + " SET Locker = '' ,LockDate = '1900-1-1' WHERE " + pkName + " = " + pkValue.ToString();
}
return(YKDataClass.SqlCommand(sql));
}
public static bool TestConnection()
{
using (SqlConnection conn = YKDataClass.Connection())
{
if (conn == null)
{
return(false);
}
else
{
return(true);
}
}
}
///<summary>执行查询语句,返回DataSet(表名"table")</summary>
///<param name="strSql">查询语句</param>
///<returns>DataSet</returns>
public static DataSet Query(string strSql)
{
SqlConnection conn = YKDataClass.Connection();
DataSet ds = new DataSet();
try
{
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
da.Fill(ds);
conn.Close();
}
catch (SqlException e) { conn.Close(); throw e; }
return(ds);
}
///<summary>执行存储过程,不返回数据</summary>
///<param name="storedProcName">存储过程名</param>
///<param name="parameters">存储过程参数</param>
///<returns>SqlDataReader</returns>
public static string RunSqlProc(string storedProcName, Hashtable ht)
{
SqlConnection conn = YKDataClass.Connection();
try
{
SqlCommand cmd = BuildQueryCommand(conn, storedProcName, ht);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
return("");
}
catch (SqlException e)
{ conn.Close(); return(e.ToString()); }
}
/// <summary>
/// 获取分公司编码
/// 张镇波 2013-6-19
/// </summary>
/// <param name="company"></param>
/// <returns></returns>
public static string GetCompanyCode(string company)
{
string sql = " SELECT ccCode FROM B_CompanyCode WHERE ccName = '" + company + "'";
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return("");
}
else
{
return(dt.Rows[0][0].ToString());
}
}