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 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); }
public static DataTable GetFatherTestType() { string sql = "select distinct FatherTestType from tk_ConfigTestType"; 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 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 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 GetClienInfo(string ClienName) { string sql = "select distinct UserName,Tel,Address,PostalCode from ClientInfo where UserName = '******'"; DataTable dt = SQLBase.FillTable(sql); 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 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 DataTable GetAppContent(string data, string table) { string str = "select SID,Text from [" + data + "].." + table + ""; DataTable dt = SQLBase.FillTable(str); return(dt); }
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 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 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 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 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 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 GetTestItems(string TID) { string sql = "select ItemID,ItemContent from tk_ConfigTestingItem where TID=" + TID; DataTable dt = SQLBase.FillTable(sql); 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 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 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 DataTable GetBasisAndItem(string SampleName) { string sql = " select c.TestingBasisId,c.TestingBasis,b.ItemId,b.ItemContent from tk_ConfigSampleName a left join tk_ConfigTestingItem b on a.ItemID LIKE '%,'+convert(varchar,b.ItemId)+',%' left join tk_ConfigBasis c on a.BasisID like '%,'+convert(varchar,c.TestingBasisId)+',%' where a.SampleName='" + SampleName + "'"; DataTable dt = SQLBase.FillTable(sql); return(dt); }
public static DataTable GetSampleInfo(string YYCode) { string sql = "select SampleName,Specification,Number,Brand,SampleCode,SampleState,TestingBasis,TestingItem from SampleInfo where YYCode ='" + YYCode + "' order by serialNumber"; DataTable dt = SQLBase.FillTable(sql); return(dt); }
public static OperationAnalysis getOperationAnalysis(int month) { string sql = "select top(1) OId, Year, Month, Overview, BusinesAnalysi, BusinesContent, PayableAnalysi, PayableContent, Experience, Other, Unit, CreateUser, CreateTime from OperationAnalysis where year=" + DateTime.Now.ToString("yyyy") + " and Month = " + month + " and unit='" + GAccount.GetAccountInfo().UnitName + "' order by CreateTime"; OperationAnalysis oa = new OperationAnalysis(); GSqlSentence.SetTValueD <OperationAnalysis>(oa, SQLBase.FillTable(sql).Rows[0]); return(oa); }
public static DataTable LoadPlanDatail(string JHID) { string str = "select " + "PID,Name,Specifications,Finishedproduct,finishingproduct,OnlineCount,Spareparts,notavailable,Total,plannumber,demandnumber,Remarks,DID,CreateUser,CreateTime " + "from [BGOI_Produce].dbo.[tk_Product_PlanDetail] where JHID='" + JHID + "' "; DataTable dt = SQLBase.FillTable(str, "MainProduce"); return(dt); }
// 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); }
public static DataTable GetConfigCont(string Type) { Acc_Account account = GAccount.GetAccountInfo(); string strSql = "select SID,Text from tk_ConfigContent where Type = '" + Type + "' and Validate = 'v' and UnitID = '" + account.UnitID + "'"; DataTable dt = SQLBase.FillTable(strSql, "SupplyCnn"); return(dt); }