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