/// <summary> /// 弹出审批相关的single界面 /// 张镇波 2013-5-21 /// </summary> /// <param name="amID"></param> /// <returns></returns> public static BaseSingle GetSingleApprove(int amID) { try { string sql1 = "SELECT amValue,fmID FROM dbo.B_ApproveMain AS bam WHERE amID = " + amID.ToString(); DataTable dt1 = new DataTable(); dt1 = YKDataClass.getDataTable(sql1); int keyValue = Convert.ToInt32(dt1.Rows[0]["amValue"].ToString()); int fmID = Convert.ToInt32(dt1.Rows[0]["fmID"].ToString()); string sql2 = "SELECT bal.alSingle,alClass FROM dbo.B_FlowMain AS bfm " + " INNER JOIN dbo.B_AuthorityList AS bal ON bal.alName = bfm.mName WHERE fmID = " + fmID.ToString(); DataTable dt2 = new DataTable(); dt2 = YKDataClass.getDataTable(sql2); string alsingle = dt2.Rows[0]["alSingle"].ToString(); string alClass = dt2.Rows[0]["alClass"].ToString(); object[] obj = new object[1] { keyValue }; Assembly _assembly = Assembly.LoadFile(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\" + alClass + ".dll"); BaseSingle frm = _assembly.CreateInstance(alsingle, true, BindingFlags.CreateInstance, null, obj, null, null) as BaseSingle; return(frm); } catch { return(null); } }
/// <summary> /// 获取库存 /// </summary> /// <param name="lName"></param> /// <param name="mfSpec"></param> /// <returns></returns> public DataTable GetStock(string etname, string lName, string mfSpec) { string sql = " select lName,mfSpec,sLength,sWidth,sum(sCount) as sCount " + " from W_Stock s inner join B_MaterialFile mf on mf.mfID = s.mfID" + " where tag = '原材料' and etname = '" + etname + "' and lName = '" + lName + "' AND mfSpec = '" + mfSpec + "'" + " group by lName,mfSpec,sLength,sWidth"; return(YKDataClass.getDataTable(sql)); }
public static string GetdNameXMLFile(string dName) { string sqlstr = "SELECT TOP 1 [value] FROM B_CommonForCombox cfc WHERE ISNULL(cfc.valid,1)=1 AND cfc.setMemo='" + dName + "' AND cfc.setName='B_dNameXMLFile'"; object o = YKDataClass.GetSingle(sqlstr); if (o != null) { return(o.ToString()); } return(""); }
/// <summary> /// 保存设置 /// </summary> /// <param name="dic"></param> /// <returns></returns> public int SaveSet(Dictionary <string, string> dic) { string sql = " delete from N_NestSet "; foreach (var item in dic) { sql += " insert into N_NestSet (nsName,nsValue) values"; sql += "('" + item.Key + "','" + item.Value + "') "; } return(YKDataClass.SqlCommand(sql)); }
/// <summary> /// 获取材质规格列表 /// </summary> /// <returns></returns> public DataSet GetStockList() { string sql = ""; sql += " SELECT s.etName,s.lName,mf.mfSpec INTO #s FROM dbo.W_Stock s INNER JOIN dbo.B_MaterialFile mf ON s.mfID = mf.mfID " + " WHERE mf.mfName = '钢板' GROUP BY mf.mfSpec,s.etName,s.lName " + " ORDER BY s.etName,s.lName,mf.mfSpec " + " SELECT etName FROM #s GROUP BY etName ORDER BY etName " + " SELECT lName FROM #s GROUP BY lName ORDER BY CASE WHEN lName LIKE 'Q%' THEN 0 ELSE 1 END,lName " + " SELECT mfSpec FROM #s GROUP BY mfSpec ORDER BY dbo.fSortNumber(mfSpec) " + " DROP TABLE #s "; return(YKDataClass.Query(sql)); }
/// <summary> /// 获取设置 /// </summary> /// <returns></returns> public Dictionary <string, string> GetSet() { Dictionary <string, string> dic = new Dictionary <string, string>(); string sql = " select nsName,nsValue from N_NestSet"; DataTable dt = new DataTable(); dt = YKDataClass.getDataTable(sql); for (int i = 0; i < dt.Rows.Count; i++) { dic.Add(dt.Rows[i]["nsName"].ToString(), dt.Rows[i]["nsValue"].ToString()); } return(dic); }
/// <summary> /// 检查用户名登录 /// </summary> /// <param name="uname">用户名</param> /// <param name="password">密码</param> /// <returns></returns> public static DataSet checkuser(string uname, string pass) { string md5Pass = UserMd5(pass); string sqlstr = "select a.*,b.dNum,b.dName FROM OE_Employee a INNER JOIN B_Dept b ON a.dID=b.dID where " + " ( eNum='" + uname + "' or eName='" + uname + "') and (ePassword='******' or ePassword='******') " + " ORDER BY ISNULL(a.eValid,1) DESC"; DataSet ds = FZYK.Com.YKDataClass.Query(sqlstr); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return(null); } else { if (!ds.Tables[0].Rows[0]["ePassword"].ToString().Equals(md5Pass)) { sqlstr = "update OE_Employee set ePassword='******' where eID='" + ds.Tables[0].Rows[0]["eID"].ToString() + "'"; FZYK.Com.YKDataClass.SqlCommand(sqlstr); } DataTable dt = ds.Tables[0]; if (dt != null && dt.Rows.Count > 0) { FZYK.Com.UserInfo.eID = Convert.ToInt32(dt.Rows[0]["eID"]); FZYK.Com.UserInfo.eNum = dt.Rows[0]["eNum"].ToString(); FZYK.Com.UserInfo.dID = Convert.ToInt32(dt.Rows[0]["dID"]); FZYK.Com.UserInfo.dNum = dt.Rows[0]["dNum"].ToString(); FZYK.Com.UserInfo.dName = dt.Rows[0]["dName"].ToString(); FZYK.Com.UserInfo.eName = dt.Rows[0]["eName"].ToString(); FZYK.Com.UserInfo.eIBase = dt.Rows[0]["eIBase"].ToString(); FZYK.Com.UserInfo.eValid = Convert.ToString(dt.Rows[0]["eValid"]); //FZYK.Com.UserInfo.userInfo.eNum = DevCommon.getSubDDNum(); FZYK.Com.UserInfo.htRights = GetUserRights(FZYK.Com.UserInfo.eID, false); FZYK.Com.UserInfo.configSys = GetConfigureSys(); //DevCommon.insertLog(FZYK.Com.UserInfo.userInfo.eNum, DevCommon.userInfo.E_Name); FZYK.Com.UserInfo.xmlName = GetdNameXMLFile(FZYK.Com.UserInfo.dName); FZYK.Core.YKControlText.InitControlText(); Hashtable ht = new Hashtable(); ht.Add("@eID", Convert.ToInt32(dt.Rows[0]["eID"])); FZYK.Com.UserInfo.dsMessage = YKDataClass.RunSqlProcForDataSet("SM_GetMessage", ht); FZYK.Com.UserInfo.dsOALimit = GetLimitDate(FZYK.Com.UserInfo.eNum); } return(ds); } }
/// <summary> /// 按比重计算重量 2013-1-22 修改(采购仓库单位不同) /// 2013-4-23 修改,增加到货单的单位单独配置 /// 2013-6-21 修改,区别螺栓重量计算方法 /// 2014-6-17 增加法兰重量计算 /// </summary> /// /// <param name="module">模块代号</param> /// <param name="count">数量</param> /// <param name="length">长度</param> /// <param name="width">宽度</param> /// <param name="density">比重</param> /// <returns></returns> public static decimal ComputeWeightByDensityNew(string way, string mfID, string tag, string module, decimal count, decimal length, decimal width, decimal density) { string sql = " SELECT * FROM B_MaterialFile WHERE mfID = " + mfID; DataTable dt = YKDataClass.getDataTable(sql); string weightWay = "算法1(比重,尺寸,数量)"; decimal mfdensity = 0M; if (mfID != "0" || dt.Rows.Count > 0) { weightWay = dt.Rows[0]["mfWeightWay"].ToString(); mfdensity = Convert.ToDecimal(dt.Rows[0]["mfDensity"].ToString()); } string lengthUnit = ""; string weightUnit = ""; if (module == "C") { lengthUnit = UserInfo.configSys["C_LengthUnit"].ToString(); weightUnit = UserInfo.configSys["C_WeightUnit"].ToString(); } else if (module == "W") { lengthUnit = UserInfo.configSys["W_LengthUnit"].ToString(); weightUnit = UserInfo.configSys["W_WeightUnit"].ToString(); } else if (module == "GA") { lengthUnit = UserInfo.configSys["W_GoodsAcceptLengthUnit"].ToString(); weightUnit = UserInfo.configSys["W_GoodsAcceptWeightUnit"].ToString(); } //测试 //string lengthUnit = "mm"; //string weightUnit = "kg"; decimal weight = 0M; decimal unit = 0M; if (tag == "原材料" && way == "过磅") { weight = count * density; } else if (tag == "原材料" && weightWay != "算法2(比重,数量)") { //原材料按比重计算 if (lengthUnit.ToLower() == "mm") { length /= 1000; width /= 1000; } if (width == 0) { unit = length; } else if (length == 0) { unit = width; } else { unit = length * width; } weight = count * unit * mfdensity; if (weightUnit.ToLower() == "t") { weight /= 1000; } } else if (tag == "螺栓" || weightWay == "算法2(比重,数量)") { //螺栓数量*单重 weight = count * mfdensity; if (weightUnit.ToLower() == "t") { weight /= 1000; } } else { //辅材不计算重量 weight = 0; } return(weight); }
/// <summary> /// 得到到期提醒的数据 /// </summary> /// <returns></returns> public static DataSet GetLimitDate(string eNum) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); StringBuilder sbSql = new StringBuilder(); #region 得到培训证书到期数据 sbSql.Append("if exists(select 1 from B_EmployeeAuthorityView where eaIAudit=1 and eNum='" + eNum + "' and alName='OE_ProfessionCertificateValid')"); sbSql.Append(" select * from OE_EmployeeProfessionCertificateView where pccWarmDay>=0 and ((epValidDate<=dateadd(day,pccWarmDay,getdate()) and epValidDate>=convert(varchar(10),getdate(),120))"); sbSql.Append(" or (epCheckDate<=dateadd(day,pccWarmDay,getdate()) and epCheckDate>=convert(varchar(10),getdate(),120)))"); dt = YKDataClass.getDataTable(sbSql.ToString()); if (dt != null && dt.Rows.Count > 0) { dt.TableName = "OE_ProfessionCertificateValid"; foreach (DataRow dr in dt.Rows) { if (dr["epCertificationDate"] != null && dr["epCertificationDate"] != DBNull.Value && dr["epCertificationDate"].ToString() != "" && Convert.ToDateTime(dr["epCertificationDate"].ToString()).Year == 1900) { dr["epCertificationDate"] = DBNull.Value; } if (dr["epCheckDate"] != null && dr["epCheckDate"] != DBNull.Value && dr["epCheckDate"].ToString() != "" && Convert.ToDateTime(dr["epCheckDate"].ToString()).Year == 1900) { dr["epCheckDate"] = DBNull.Value; } if (dr["epValidDate"] != null && dr["epValidDate"] != DBNull.Value && dr["epValidDate"].ToString() != "" && Convert.ToDateTime(dr["epValidDate"].ToString()).Year == 1900) { dr["epValidDate"] = DBNull.Value; } if (dr["epUploadDate"] != null && dr["epUploadDate"] != DBNull.Value && dr["epUploadDate"].ToString() != "" && Convert.ToDateTime(dr["epUploadDate"].ToString()).Year == 1900) { dr["epUploadDate"] = DBNull.Value; } if (dr["WriteDate"] != null && dr["WriteDate"] != DBNull.Value && dr["WriteDate"].ToString() != "" && Convert.ToDateTime(dr["WriteDate"].ToString()).Year == 1900) { dr["WriteDate"] = DBNull.Value; } if (dr["LockDate"] != null && dr["LockDate"] != DBNull.Value && dr["LockDate"].ToString() != "" && Convert.ToDateTime(dr["LockDate"].ToString()).Year == 1900) { dr["LockDate"] = DBNull.Value; } } ds.Tables.Add(dt); } #endregion #region 转正提醒,提醒本人 sbSql.Clear(); sbSql.Append("SELECT "); //sbSql.Append("dbo.fSumWorkAgeHavingLeave(eID,2)workAge,"); sbSql.Append("null as workAge,"); sbSql.Append("dbo.fGetPY(OEEV.eName) as py,OEEV.[eID],OEEV.[dID],OEEV.[dNum],OEEV.[dName],OEEV.[eNum],OEEV.[eName],OEEV.[eCertificate],OEEV.[ePassword],OEEV.[eIDCard],OEEV.[eIBase],OEEV.[eSex],OEEV.[eValid],OEEV.[eTel],OEEV.[eMobile],OEEV.[eAddress],OEEV.[eBirthday],OEEV.[eIfMarry],OEEV.[eWorkValue],OEEV.[eCollege],OEEV.[eMemo],OEEV.[eEducation],OEEV.[ePostitle],OEEV.[eNation],OEEV.[eNativePlace],OEEV.[eDegree],OEEV.[eSpecialized],OEEV.[eEnterTime],OEEV.[eLevel] "); sbSql.Append(",eIfManager,eLeaveTime,eInsureNum,replace(dbo.fSumWorkAge(eBirthday,getdate(),0),'年','岁')eAge"); sbSql.Append(",eisID,eJoinDate,eTran,eRetireMoney,eRetireDate,eCode,eIfTwoWorker,eCompactLimit1,eCompactLimit2,eCompactLimit3"); sbSql.Append(",eGroupWorkAge,eHKCompany,eZQCompany,eSkillNum,eSmallGroupID,eSmallGroup,eBanZuID,eTryLimit"); sbSql.Append(" FROM OE_EmployeeView OEEV"); sbSql.Append(" where eID=" + Com.UserInfo.eID + " and ISNULL(eTryLimit,0)>0 and DATEADD(month,ISNULL(eTryLimit,0),eEnterTime)>DATEADD(day,-7,getdate())"); DataTable dtPosttive = YKDataClass.getDataTable(sbSql.ToString()); if (dtPosttive != null && dtPosttive.Rows.Count > 0) { dtPosttive.TableName = "OE_EmployeePosttive"; ds.Tables.Add(dtPosttive); } #endregion #region 保险提醒 #endregion return(ds); }
/// <summary> /// 登陆框中获取服务器和数据库下拉框 /// </summary> /// <returns></returns> public static DataSet getDBForCmb(string startUrl, string theUrl) { DataSet ds = new DataSet(); #region 获取初始连接字符串 try { XmlDocument xmlIPDoc = new XmlDocument(); xmlIPDoc.Load(startUrl + "\\UpdateList.xml"); if (theUrl == "url2") { XmlNode xsDBConnect = xmlIPDoc.SelectSingleNode("//sDBConnect2"); if (xsDBConnect != null) { //YKDataClass .ConnectionString = FZYK .Com .YKSysCom .Decrypt(xsDBConnect .InnerText); YKDataClass.ConnectionString = xsDBConnect.InnerText; } else { XmlNode xDBConnect = xmlIPDoc.SelectSingleNode("//DBConnect2"); if (xDBConnect != null) { YKDataClass.ConnectionString = xDBConnect.InnerText;//数据库连接字符串 } } } else { XmlNode xsDBConnect = xmlIPDoc.SelectSingleNode("//sDBConnect"); if (xsDBConnect != null) { //YKDataClass .ConnectionString = FZYK .Com .YKSysCom .Decrypt(xsDBConnect .InnerText); YKDataClass.ConnectionString = xsDBConnect.InnerText; } else { XmlNode xDBConnect = xmlIPDoc.SelectSingleNode("//DBConnect"); if (xDBConnect != null) { YKDataClass.ConnectionString = xDBConnect.InnerText;//数据库连接字符串 } } } string sqlstr = "select * from IPRecord;select * from DBrecord order by id desc;select convert(char(10),getdate(),120); "; ds = YKDataClass.Query(sqlstr); if (ds.Tables.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (ds.Tables[0].Rows[i]["isReadOnly"] != null && ds.Tables[0].Rows[i]["isReadOnly"].ToString().Equals("1")) { ds.Tables[0].Rows[i]["ServerIP"] = ds.Tables[0].Rows[i]["ServerIP"] + "(ReadOnly)"; } } } #endregion } catch (Exception) { } return(ds); }
public static void UpdateUserIP(int eID) { string sqlstr = "UPDATE B_UserIP SET Valid = 0 WHERE Valid=1 AND eID=" + eID; YKDataClass.SqlCommand(sqlstr); }
public static void InsertUserIP(int eID, string ip, int port) { string sqlstr = "UPDATE B_UserIP SET Valid = 0 WHERE Valid=1 AND eID=" + eID + "; INSERT INTO B_UserIP(eID,buIP,buPort,buDate,Valid) VALUES(" + eID + ",'" + ip + "'," + port + ",GETDATE(),1)"; YKDataClass.SqlCommand(sqlstr); }