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