public static bool ValidateUserPWD(String strLoginName, String strPwd)
{
//if (strPwd != "1")
//{
// strPwd = Encrypt(strPwd);
//}
// and (vvv = '' or vvv > getdate())
//String strSql = "SELECT a.[LoginPwd] FROM UM_User as a INNER JOIN UM_UserSystem as b ON a.LoginName = b.UserID";
//strSql = strSql + String.Format(" Where a.LoginName='{0}' And b.SystemID like '%" + GAccount.SystemID + "%'", strLoginName, GAccount.SystemID);
String strSql = "SELECT a.UserPwd FROM UM_UserNew as a INNER JOIN UM_UserSystem as b ON a.UserId = b.UserId";
strSql = strSql + String.Format(" Where a.UserLogin='******' And b.SystemID like '%" + GAccount.SystemID + "%' and (b.Validate = '' or b.Validate is NULL or b.Validate > '" + DateTime.Now + "')", strLoginName, GAccount.SystemID);
String pwd = GFun.SafeToString(SQLBase.ExecuteScalar(strSql, AccountCnn));
if (pwd == strPwd)
{
//GLog.LogInfo("用户名密码正确登录成功！");
return(true);
}
return(false);
}
public static DataTable GetTestItems(string TID)
{
string sql = "select ItemID,ItemContent from tk_ConfigTestingItem where TID=" + TID;
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static DataTable GetMandateInfoByYYCode(string YYCode)
{
string sql = "select MCode, ClienName, ClienTel, ClienAddress, PostalCode, ProName, SourceWay, SamplePeople, SampleTime, Manufacturer, Document, Secrecy, TestingBasis, TestingItems, DemandFinishDate,Charge, PickupMethod, MailingAddress, Remark, SampleDisposition from MandateInfo where yycode='" + YYCode + "'";
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static DataTable GetClienInfo(string ClienName)
{
string sql = "select distinct UserName,Tel,Address,PostalCode from ClientInfo where UserName = '******'";
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static bool InsertMandate(MandateInfo mandate, List <SampleInfo> sampleList, ref string err)
{
string MandateSql = GSqlSentence.GetInsertInfoByD <MandateInfo>(mandate, "MandateInfo");
string sampleSql = GSqlSentence.GetInsertByList <SampleInfo>(sampleList, "SampleInfo");
List <TestItem> testItemList = GetItemIDs(mandate.TestingItems, mandate.YYCode);
string testItemSql = GSqlSentence.GetInsertByList <TestItem>(testItemList, "TestItem");
string sql = "select count(*) from MandateInfo where YYCode='" + mandate.YYCode + "'";
try
{
if ((int)SQLBase.ExecuteScalar(sql) > 0)
{
err = "该委托单已添加";
return(false);
}
if (SQLBase.ExecuteNonQuery(MandateSql) > 0)
{
if (!UpdateYYCode(mandate.YYCode))
{
err = "预约号更新失败";
return(false);
}
if (sampleList.Count > 0)
{
if (SQLBase.ExecuteNonQuery(sampleSql) <= 0)
{
err = "样品信息添加失败";
return(false);
}
}
if (testItemList.Count > 0)
{
if (SQLBase.ExecuteNonQuery(testItemSql) <= 0)
{
err = "检测项目添加失败";
return(false);
}
}
string content = "添加委托单信息";
if (!insertLog(mandate.YYCode, content))
{
err = "更新日志失败";
return(false);
}
return(true);
}
err = "委托信息添加失败";
}
catch (Exception e)
{
err = e.Message;
}
return(false);
}
public static bool UpdateMcode(string type, string MCode)
{
string year = DateTime.Now.ToString("yyyy");
string MaxValue;
if (type == "F" || type == "B")
{
MaxValue = MCode.Substring(6);
}
else
{
MaxValue = MCode.Substring(7);
}
string sql = "select count(*) from tk_ConfigMIDRecord where Year='" + year + "' and type ='" + type + "'";
int count = (int)SQLBase.ExecuteScalar(sql);
if (count > 0)
{
sql = "update tk_ConfigMIDRecord set MaxValue =" + MaxValue + " where Year='" + year + "' and type='" + type + "'";
}
else
{
sql = "insert into tk_ConfigMIDRecord(year,type,MaxValue) values ('" + year + "','" + type + "'," + MaxValue + ")";
}
if (SQLBase.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public static DataTable GetFatherTestType()
{
string sql = "select distinct FatherTestType from tk_ConfigTestType";
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static DataTable GetMandateChargeInfo(string where)
{
string sql = "select a.YYCode,MCode,charge-isnull(b.Amount,0) charge,ClienName from mandateInfo a left join (select YYCode,isnull(sum(Amount),0)Amount from ConsumptionInfo group by yycode)b on a.YYCode = b.YYCode where charge-isnull(b.Amount,0)>0 and MCode<>'' and state<>-1" + where;
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static DataTable GetTestType(string FatherTestType)
{
string sql = "select TID,ChildTestType FROM tk_ConfigTestType where FatherTestType='" + FatherTestType + "'";
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static double GetPayMoney(string ClienName)
{
string sql = "select Money from ClientPayInfo where ClienName='" + ClienName + "'";
double Amount = Convert.ToDouble(SQLBase.ExecuteScalar(sql));
return(Amount);
}
public static DataTable GetTestType()
{
string sql = "select TID,ChildTestType FROM tk_ConfigTestType";
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static DataTable getUserNamebyLoginName(string LoginName)
{
string str = "select UserId,UserName from UM_UserNew where UserLogin = '******'";
DataTable dt = SQLBase.FillTable(str, AccountCnn);
return(dt);
}
public static string GetSPid(string folderBack)
{
string[] arr = folderBack.Split('/');
Acc_Account account = GAccount.GetAccountInfo();
int unitId = Convert.ToInt16(account.UnitID);
string strPID = "";
string strYMD = DateTime.Now.ToString("yyMMdd");
string strSelPID = "select SPID, SPidNo from [" + arr[0] + "].." + arr[6] + " where DateRecord='" + strYMD + "'";
DataTable dtPMaxID = SQLBase.FillTable(strSelPID);
int intNewID = 0;
if (dtPMaxID == null)
{
return(strPID);
}
if (dtPMaxID.Rows.Count == 0)
{
string strInsertID = "insert into [" + arr[0] + "].." + arr[6] + " (SPID,SPidNo,DateRecord) values('P',0,'" + strYMD + "')";
SQLBase.ExecuteNonQuery(strInsertID);
intNewID = 0;
}
else
{
intNewID = Convert.ToInt32(dtPMaxID.Rows[0]["SPidNo"]);
}
intNewID++;
string strUpdateID = "update [" + arr[0] + "].." + arr[6] + " set SPidNo='" + intNewID + "' where DateRecord ='" + strYMD + "'";
SQLBase.ExecuteNonQuery(strUpdateID);
strPID = dtPMaxID.Rows[0]["SPID"].ToString() + DateTime.Now.ToString("yyMMdd") + GFun.GetNum(unitId, 4) + GFun.GetNum(intNewID, 4);
return(strPID);
}
private static DataTable GetAccount(string a_strLoginName, string a_strSysID, ref string a_strErr)
{
try
{
SqlParameter[] sqlPar = new SqlParameter[]
{
new SqlParameter("@LoginName", a_strLoginName),
new SqlParameter("@SystemID", a_strSysID)
};
//string strGetAccount = "SELECT a.ID,a.UserName,a.UnitID,b.RightCodes,c.UnitCode ,c.UnitName,c.UnitBrief,c.HigherUnitID,UserFunc,b.UserRole,c.UnitOneWord,a.UserPhone,d.FunctionsCode,a.Branch,e.Provice + '/' +e.City + '/'+e.District as BranchPCD, ";
//strGetAccount += "f.Name + g.Name + h.Name + e.Street + e.BranchName as BranchName ";
//strGetAccount += "FROM UM_User as a INNER JOIN UM_UserSystem as b ON a.LoginName = b.UserID inner join UM_Unit as c on a.UnitID=c.UnitCode inner join UM_UnitFunctions as d on a.UnitID = d.UnitID ";
//strGetAccount += "inner join UM_Branch e on a.Branch = e.BranchID ";
//strGetAccount += "inner join [CGMS]..tk_ConfigAddr_Provice f on e.Provice = f.ProvinceID ";
//strGetAccount += "inner join [CGMS]..tk_ConfigAddr_City g on e.City = g.CityID ";
//strGetAccount += "inner join [CGMS]..tk_ConfigAddr_District h on e.District = h.DistrictID ";
//strGetAccount += "where a.LoginName=@LoginName and b.SystemID like '%' + @SystemID + '%'";
string strGetAccount = "SELECT a.UserId,a.UserName,a.DeptId,b.RightCodes,c.DeptName,";
strGetAccount += "b.UserFunc,b.UserRole,a.MobilePhone,a.roleNames,c.Path,a.ExJob";
strGetAccount += " FROM UM_UserNew as a";
strGetAccount += " INNER JOIN UM_UserSystem as b ON a.UserId = b.UserId";
strGetAccount += " inner join UM_UnitNew as c on a.DeptId=c.DeptId";
strGetAccount += " where a.UserLogin=@LoginName and b.SystemID like '%' + @SystemID + '%'";
DataTable dtAccount = SQLBase.FillTable(strGetAccount, CommandType.Text, sqlPar, AccountCnn);
return(dtAccount);
}
catch (SqlException ex)
{
a_strErr = ex.Message;
return(null);
}
}
// 获取表格数据???? 流量点 还有示值误差对应的字段名核对
public static DataSet getMeterImg(string arr)
{
string strSql = "";
string[] Info = arr.ToString().Split('@');
string RID = Info[0].ToString();
// string MeterID = Info[0].ToString();
string Types = Info[2].ToString();
//-- 流量点 维修前示值误差 维修后示值误差 检测方式
strSql += " select ISNULL(a1,0) Flow,ISNULL(a2,0) PreData,isnull(a3,0) AftData,'" + Types + "' as Types from (";
strSql += " select a.Qmin as a1,a.Avg_Qmin as a2,b.Avg_Qmin as a3 from tk_CheckData a ";
strSql += " left join (select * from tk_CheckData where RepairType='维修检测' and validate='是') b on a.RID=b.RID ";
strSql += " where a.RepairType='进厂检测' and a.RID='" + RID + "' ";
strSql += " union all(select a.[0.1Qmax],a.[Avg_0.1Qmax],b.[Avg_0.1Qmax] from tk_CheckData a ";
strSql += " left join (select * from tk_CheckData where RepairType='维修检测' and validate='是') b on a.RID=b.RID ";
strSql += " where a.RepairType='进厂检测' and a.RID='" + RID + "') ";
strSql += " union all(select a.[0.2Qmax],a.[Avg_0.2Qmax],b.[Avg_0.2Qmax] from tk_CheckData a ";
strSql += " left join (select * from tk_CheckData where RepairType='维修检测' and validate='是') b on a.RID=b.RID ";
strSql += " where a.RepairType='进厂检测' and a.RID='" + RID + "') ";
strSql += " union all(select a.[0.25Qmax],a.[Avg_0.25Qmax],b.[Avg_0.25Qmax] from tk_CheckData a ";
strSql += " left join (select * from tk_CheckData where RepairType='维修检测' and validate='是') b on a.RID=b.RID ";
strSql += " where a.RepairType='进厂检测' and a.RID='" + RID + "' )) k ";
DataSet ds = SQLBase.FillDataSet(strSql, CommandType.Text, null, "FlowMeterDBCnn");
return(ds);
}
public static DataTable GetPayId(string PayCompany)
{
string sql = "select PayId from PayInfo b where PayType='2' and (select isnull(sum(Amount),0) from ConsumptionInfo a where a.PayId=b.PayId and type!='2')<PayMoney and PayCompany='" + PayCompany + "'";
DataTable dt = SQLBase.FillTable(sql);
return(dt);
}
public static string GetNewPid()
{
string strCID = "";
string strYMD = DateTime.Now.ToString("yyyyMMdd");
string strSelPID = "select * from PurchaseApplication where DateRecord='" + strYMD + "'";
DataTable dtPMaxID = SQLBase.FillTable(strSelPID, "MainPP");
int intNewID = 0;
if (dtPMaxID == null)
{
return(strCID);
}
if (dtPMaxID.Rows.Count == 0)
{
string strInsertID = "insert into PIDNo (PID,PidNo,DateRecord) values('S',0,'" + strYMD + "')";
SQLBase.ExecuteNonQuery(strInsertID, "SalesDBCnn");
intNewID = 0;
}
else
{
intNewID = Convert.ToInt32(dtPMaxID.Rows[0]["PidNo"]);
}
intNewID++;
string str = "select PID, PidNo,DateRecord from PIDNo where DateRecord='" + strYMD + "'";
dtPMaxID = SQLBase.FillTable(strSelPID, "SalesDBCnn");
strCID = dtPMaxID.Rows[0]["CID"].ToString() + DateTime.Now.ToString("yyyyMMdd") + GFun.GetNum(intNewID, 3);
return(strCID);
}
public static DataTable GetConfigContent()
{
string strSql = "select distinct Type,TypeDesc as ss from tk_ConfigContent where Type='Psource' or Type='Design' or Type='PayType' or Type = 'JQType'";
DataTable dt = SQLBase.FillTable(strSql, "MainProject");
return(dt);
}
public static DataTable getFile(string id)
{
string strSql = "select ID,FileName,FileInfo from tk_UserAptitude where ID = '" + id + "'";
DataTable dt = SQLBase.FillTable(strSql, "SupplyCnn");
return(dt);
}
public static DataTable GetAppContent(string data, string table)
{
string str = "select SID,Text from [" + data + "].." + table + "";
DataTable dt = SQLBase.FillTable(str);
return(dt);
}
public static DataTable getAppType(string Type)
{
string strSql = "select SID,Text from [BGOI_BasMan]..tk_ConfigApp where Type = '" + Type + "' and XID < '3'";
DataTable dt = SQLBase.FillTable(strSql);
return(dt);
}
public static DevicsBas getDevicsByID(string id)
{
DevicsBas Bas = new DevicsBas();
string str = "select * from tk_DevicsBas where ECode = '" + id + "'";
DataTable dt = SQLBase.FillTable(str, "SupplyCnn");
if (dt.Rows.Count > 0)
{
Bas.StrControlCode = dt.Rows[0]["ControlCode"].ToString();
Bas.StrEname = dt.Rows[0]["Ename"].ToString();
Bas.StrManufacturer = dt.Rows[0]["Manufacturer"].ToString();
Bas.StrFactoryNumber = dt.Rows[0]["FactoryNumber"].ToString();
Bas.StrSpecification = dt.Rows[0]["Specification"].ToString();
Bas.StrDevicsType = dt.Rows[0]["DevicsType"].ToString();
if (dt.Rows[0]["FactoryDate"].ToString() != "")
{
Bas.StrFactoryDate = Convert.ToDateTime(dt.Rows[0]["FactoryDate"]).ToString("yyyy-MM-dd");
}
Bas.StrPrecision = dt.Rows[0]["Precision"].ToString();
Bas.StrTracingType = dt.Rows[0]["TracingType"].ToString();
Bas.StrClrange = dt.Rows[0]["Clrange"].ToString();
Bas.StrCycleType = dt.Rows[0]["CycleType"].ToString();
Bas.StrCycle = dt.Rows[0]["Cycle"].ToString();
Bas.StrCheckCompany = dt.Rows[0]["CheckCompany"].ToString();
Bas.StrRemark = dt.Rows[0]["Remark"].ToString();
}
return(Bas);
}
public static DataTable getState()
{
string strSql = "select StateId,name from tk_ConfigState where Type = 'Equip'";
DataTable dt = SQLBase.FillTable(strSql, "SupplyCnn");
return(dt);
}
public static DataTable GetConfigContByUnit(string UnitID)
{
string strSql = "select SID,Text from tk_ConfigBussinessType where Unit like '%," + UnitID + ",%'";
DataTable dt = SQLBase.FillTable(strSql, "SupplyCnn");
return(dt);
}
public static DataTable GetSpecifications()
{
string str = "select distinct Specifications from BGOI_Produce.dbo.[tk_Product_PlanDetail]";
DataTable dt = SQLBase.FillTable(str, "MainProduce");
return(dt);
}
public static DataTable GetPlanYear()
{
string sql = "select distinct Plannedyear from dbo.tk_Product_Plan order by Plannedyear desc";
DataTable dt = SQLBase.FillTable(sql, "MainProduce");
return(dt);
}
public static DataTable GetZD()
{
string str = "select distinct a.ProductID,B.ProName,b.Spec,isnull(c.finishCount,0)finishCount,isnull(c.HalfCount,0)HalfCount,isnull(c.OnlineCount,0)OnlineCount,isnull(d.num,0) xdnum,isnull(e.lj,0) lj from tk_ProductionOfFinishedGoods aa left join tk_ProFinishDefine a on aa.PID = A.ProductID left join tk_ProductInfo b on a.ProductID = b.PID left join tk_StockRemain c on a.ProductID = c.ProductID LEFT JOIN (select ProductID ,sum(OrderNum-ShipmentNum)num from BGOI_Sales.dbo.Orders_DetailInfo group by ProductID) d on a.ProductID = d.ProductID left join (select KSC.ProductID,min(FinishCount/Number)lj from tk_ProFinishDefine KSC LEFT JOIN tk_StockRemain KC ON KSC.PartPID = KC.ProductID where Number>0 group by KSC.ProductID) e on a.ProductID = e.ProductID where a.ValiDate='v' AND aa.ValiDate ='v' and UnitID ='" + GAccount.GetAccountInfo().UnitID + "'";
DataTable dt = SQLBase.FillTable(str, "MainInventory");
return(dt);
}
public static string ProJHNum(string JHID)
{
string strid = "";
string Num = "";
string sqlstr = "Select MAX(DID) From [BGOI_Produce].[dbo].[tk_Product_PlanDetail]";
DataTable dt = SQLBase.FillTable(sqlstr);
if (dt != null && dt.Rows.Count > 0)
{
strid = dt.Rows[0][0].ToString();
string strN = strid.Substring(0, 20);
if (strid == "" || strid == null || strN != JHID)
{
Num = JHID + "-" + "01";
}
else
{
int IntNum = Convert.ToInt32(strid.Substring(strid.Length - 2, 2));
if (IntNum < 9)
{
Num = JHID + "-" + "0" + (IntNum + 1).ToString();
}
else
{
Num = JHID + "-" + (IntNum + 1).ToString();
}
}
}
return(Num);
}
public static UIDataTable LoadRepalceAnaly(string where)
{
ArrayList Repalce = new ArrayList();
Repalce.Add("机芯总成"); Repalce.Add("磁耦合"); Repalce.Add("主轴轴承");
Repalce.Add("叶轮"); Repalce.Add("油泵"); Repalce.Add("机械表头");
Repalce.Add("前导流"); Repalce.Add("高频头");
SQLTrans sqlTrans = new SQLTrans();
sqlTrans.Open("FlowMeterDBCnn");
UIDataTable instData = new UIDataTable();
DataTable dt = new DataTable();
dt.Columns.Add("零件", typeof(System.String));
dt.Columns.Add("问题数量", typeof(System.String));
dt.Columns.Add("所占比例", typeof(System.String));
string sqlnum = "select Count(*) from tk_RepairDevice where DeviceName in ('机芯总成','磁耦合','主轴轴承','叶轮','油泵','机械表头','前导流','高频头')";
var num = SQLBase.FillTable(sqlnum, "FlowMeterDBCnn").Rows[0][0];
for (int i = 0; i < Repalce.Count; i++)
{
string rid = "";
if (where != "")
{
rid = "and RID in (select RID from tk_RepairCard where 1=1" + where + ")";
}
string sql = "select Count(*) '数量' from tk_RepairDevice where DeviceName='" + Repalce[i] + "'" + rid;
DataTable DO_Order = SQLBase.FillTable(sql, "FlowMeterDBCnn");
for (int j = 0; j < DO_Order.Rows.Count; j++)
{
DataRow newRow = dt.NewRow();
newRow["零件"] = Repalce[i];
newRow["问题数量"] = DO_Order.Rows[0]["数量"];
if (num.ToString() != "0")
{
var s = 0.00;
if (DO_Order.Rows[0]["数量"].ToString() == "0")
{
newRow["所占比例"] = "0.00%";
}
else
{
s = Convert.ToDouble(DO_Order.Rows[0]["数量"]) / Convert.ToDouble(num.ToString()) * 100;
newRow["所占比例"] = Math.Round(s, 2).ToString() + "%";
}
}
else
{
newRow["所占比例"] = "0.00%";
}
dt.Rows.Add(newRow);
}
}
instData.DtData = dt;
return(instData);
}
//
public static System.Data.DataTable GetBasicContent()
{
Acc_Account account = GAccount.GetAccountInfo();
string strSql = "select distinct Type as SID,TypeDesc as Text from tk_ConfigContent where validate='v' ";
DataTable dt = SQLBase.FillTable(strSql, "FlowMeterDBCnn");
return(dt);
}