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