/// <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>
/// 得到最大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>
/// 获取库存管理方式
///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>
///// 即时发送
///// </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>
/// 获取规格转换值
/// </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>
/// 获取审批记录 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>
/// 获取仓库开始日期和结束日期 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>
/// 获取分公司编码
/// 张镇波 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());
}
}
/// <summary>
/// 获取tag1值 2014-4-23 zhang
/// </summary>
/// <param name="tableName"></param>
/// <param name="key"></param>
/// <param name="value"></param>
/// <returns></returns>
public static string GetTag1(string tableName, string key, int value)
{
string sql = " SELECT Tag1 FROM " + tableName + " WHERE " + key + " = " + value.ToString();
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
else
{
return("");
}
}
/// <summary>
/// 获取单据的当前步骤
/// 张镇波
/// 2013-5-9 修改
/// </summary>
/// <param name="mName"></param>
/// <param name="level"></param>
/// <returns></returns>
public static string GetApproveState(string mName, int level)
{
string sql = " SELECT flName FROM dbo.B_FlowLevelView AS bflv WHERE mName = '" + mName + "' AND flLevel = " + level.ToString();
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
if (level > 0)
{
return("审批通过");
}
return("未提交");
}
/// <summary>
/// 获取数据库字段的小数位数
/// 张镇波 2013-5-15
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <returns></returns>
public static int GetColumnsXscale(string table, string column)
{
try
{
string sql = "SELECT c.xscale FROM sys.syscolumns AS c INNER JOIN sys.sysobjects AS o ON c.id = o.id"
+ " WHERE o.type = 'U' AND o.name ='" + table + "' AND c.name = '" + column + "'";
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0].ToString()));
}
return(-1);
}
catch
{
return(-1);
}
}
/// <summary>
/// 得到最大ID(没有Parent的情况) ,位数自定，2013-2-2修改 张
/// </summary>
public static string GetMaxIdNoParentCount(string tableName, string filedName, int Count)
{
string sql = "declare @num varchar(" + Count + "); select @num= right('0000000000',"
+ Count + " -LEN(cast(MAX(" + filedName + ")+1 as char(" + Count.ToString() + "))))"
+ "+ cast(MAX(" + filedName + ")+1 as char(" + Count.ToString() + ")) from "
+ tableName + " where isnumeric(" + filedName + ")=1 AND LEN(" + filedName + ") = " + Count.ToString()
+ " ;if @num is null set @num=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-6
/// </summary>
/// <param name="num"></param>
/// <returns></returns>
public static string GetExcelFormRemark(string num)
{
try
{
string sql = " SELECT TOP 1 efRemark FROM dbo.B_ExcelForm AS bef WHERE efValid = 1 AND efNum = '" + num + "'";
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
else
{
return("");
}
}
catch
{
return("");
}
}
/// <summary>
/// 获取出入库类型对应的结算方式，-99表示获取数据异常，-1表示不入账
/// 张镇波 2013-5-22
/// </summary>
/// <param name="iotID"></param>
/// <returns></returns>
public static int GetInOutTypeSettle(int iotID)
{
try
{
string sql = " SELECT iotSettle FROM B_InOutType WHERE iotID = " + iotID.ToString();
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0][0].ToString()));
}
else
{
return(-99);
}
}
catch
{
return(-99);
}
}
/// <summary>
/// 获取菜单是否设置了审批流程
/// 2013-8-5 张
/// </summary>
/// <param name="mName"></param>
/// <returns></returns>
public static bool NeedApprove(string mName)
{
try
{
string sql = " SELECT TOP 1 1 FROM B_FlowMain WHERE mName = '" + mName + "'";
DataTable dt = new DataTable();
dt = YKDataClass.getDataTable(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
/// <summary>
/// 获取审批流程的最大等级
/// 张镇波 2013-5-17
/// </summary>
/// <param name="mName"></param>
/// <returns></returns>
public static int GetFlowMaxLevel(string mName)
{
try
{
string sql = "SELECT MAX(flLevel) FROM dbo.B_FlowLevelView WHERE mName = '" + mName + "'";
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>
/// 得到企业信息
/// </summary>
/// <returns></returns>
public static DataTable GetFactoryName()
{
string sql = "select * from B_factoryname";
return(YKDataClass.getDataTable(sql));
}
public static System.Data.DataTable GetFactoryNameToTable()
{
string sqlstr = "SELECT top 1 * FROM B_FactoryName";
return(YKDataClass.getDataTable(sqlstr));
}
/// <summary>
/// 根据名称和规格获取编码和ID
/// </summary>
/// <param name="mfName"></param>
/// <param name="mfSpec"></param>
/// <returns></returns>
public static DataTable GetmfID(string mfName, string mfSpec)
{
string sql = "SELECT mfID,mfNum,mfDensity FROM B_MaterialFile WHERE mfName = '" + mfName + "' AND mfSpec = '" + mfSpec + "'";
return(YKDataClass.getDataTable(sql));
}