/// <summary>
/// 查询项目计划任务表
/// </summary>
public void QueryProjectPlanTask(DataTable queryDataTable, string projectNoStr, string taskNoStr, string ProjectUserStr, string commonStr, bool noCompleteBool, bool nullTable)
{
string sqlStr = " 1=1";
if (projectNoStr != "")
{
sqlStr += string.Format(" and task.ProjectNo='{0}'", projectNoStr);
}
if (taskNoStr != "")
{
sqlStr += string.Format(" and task.TaskNo={0}", taskNoStr);
}
if (ProjectUserStr != "")
{
sqlStr += string.Format(" and BS_ProjectUser.UserId={0}", ProjectUserStr);
}
if (commonStr != "")
{
sqlStr += string.Format(" and (task.ProjectNo like '%{0}%' or task.PlanTaskText like '%{0}%' or task.Remark like '%{0}%')", commonStr);
}
if (noCompleteBool)
{
sqlStr += string.Format(" and PlanTaskStatus!=3");
}
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select task.*, BS_ProjectUser.UserId, PM_ProjectTaskType.TaskText, BS_ProjectUser.IsReplace from PM_ProjectPlanTask as task left join BS_ProjectUser on task.ProjectUser = BS_ProjectUser.AutoId left join PM_ProjectTaskType on task.TaskNo = PM_ProjectTaskType.AutoId where {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询采购任务分配信息
/// </summary>
public void QueryPrReqListDistribution(DataTable queryDataTable, string beginRequirementDateStr, string endRequirementDateStr, string projectNoStr, int codeIdInt, int arrangementIdInt, string commonStr, bool containDistribution, bool IsPrToPo)
{
string sqlStr = " ReqState in (2) and List.AutoId not in (select IsNull(PrListAutoId, 0) from PUR_OrderList)";
if (beginRequirementDateStr != "")
{
sqlStr += string.Format(" and RequirementDate between '{0}' and '{1}'", beginRequirementDateStr, endRequirementDateStr);
}
if (projectNoStr != "")
{
sqlStr += string.Format(" and ProjectNo='{0}'", projectNoStr);
}
if (codeIdInt != 0)
{
sqlStr += string.Format(" and List.CodeId={0}", codeIdInt);
}
if (arrangementIdInt != 0)
{
sqlStr += string.Format(" and IsNull(List.Arrangement, 0)={0}", arrangementIdInt);
}
if (commonStr != "")
{
sqlStr += string.Format(" and (List.PrReqNo like '%{0}%' or StnNo like '%{0}%' or PrReqListRemark like '%{0}%' or ProjectNo like '%{0}%' or List.CodeFileName like '%{0}%' or CodeName like '%{0}%')", commonStr);
}
if (!containDistribution)
{
sqlStr += string.Format(" and IsNull(ArrangementFlat, 0) = 0");
}
if (!IsPrToPo)
{
sqlStr += " and List.AutoId not in (select PRListId from PUR_PRPO)";
}
sqlStr = string.Format("select List.*, ProjectNo, StnNo from PUR_PrReqList as List left join PUR_PrReqHead as Head on List.PrReqNo = Head.PrReqNo left join SW_PartsCode as Parts on List.CodeId = Parts.AutoId where {0} order by List.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询请购单表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="prReqNoStr">请购单号</param>
public void QueryPrReqHead(DataTable queryDataTable, string prReqNoStr, string reqDateBeginStr, string reqDateEndStr, string reqDepStr, string purCategoryStr, string applicantStr, string projectNoStr, string commonStr)
{
string sqlStr = " ReqState in (2) and IsNull(isEnd, 0)=0 ";
if (prReqNoStr != "")
{
sqlStr += string.Format(" and PrReqNo like '%{0}%'", prReqNoStr);
}
if (reqDateBeginStr != "")
{
sqlStr += string.Format(" and ReqDate between '{0}' and '{1}'", reqDateBeginStr, reqDateEndStr);
}
if (reqDepStr != "")
{
sqlStr += string.Format(" and ReqDep='{0}'", reqDepStr);
}
if (purCategoryStr != "")
{
sqlStr += string.Format(" and PurCategory='{0}'", purCategoryStr);
}
if (applicantStr != "")
{
sqlStr += string.Format(" and Applicant='{0}'", applicantStr);
}
if (projectNoStr != "")
{
sqlStr += string.Format(" and ProjectNo='{0}'", projectNoStr);
}
if (commonStr != "")
{
sqlStr += string.Format(" and (PrReqNo like '%{0}%' or ProjectNo like '%{0}%' or StnNo like '%{0}%' or PrReqRemark like '%{0}%')", commonStr);
}
sqlStr = string.Format("select * from PUR_PrReqHead where {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询生产计划Bom的树类型信息
/// </summary>
public void QueryProductionScheduleBomTree(DataTable queryDataTable, string salesOrderNoStr)
{
string sqlStr = "";
sqlStr = string.Format("select * from V_PB_ProductionScheduleBom_Tree where SalesOrderNo = '{0}' {1} Order by ReId", salesOrderNoStr, sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询采购国内结账信息
/// </summary>
public void QuerySettlementHead(DataTable queryDataTable, string settlementNoStr)
{
//string sqlStr = string.Format("select PUR_SettlementHead.*, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat from PUR_SettlementHead left join PUR_ApprovalType on PUR_SettlementHead.ApprovalType = PUR_ApprovalType.TypeNo where SettlementNo = '{0}'", settlementNoStr);
string sqlStr = string.Format("select Head.AutoId, Head.SettlementNo as OrderHeadNo, Head.SettlementDate as OrderHeadDate, Head.ReqDep, Head.WarehouseState as ReqState, Head.ApprovalType, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat, Head.SettlementNo, Head.WarehouseState from PUR_SettlementHead as Head left join PUR_ApprovalType on Head.ApprovalType = PUR_ApprovalType.TypeNo where SettlementNo = '{0}'", settlementNoStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询出库单信息
/// </summary>
public void QueryWarehouseReceiptHead(DataTable queryDataTable, string wrHeadNoStr)
{
//string sqlStr = string.Format("select INV_WarehouseReceiptHead.*, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat from INV_WarehouseReceiptHead left join PUR_ApprovalType on INV_WarehouseReceiptHead.ApprovalType = PUR_ApprovalType.TypeNo where WarehouseReceipt = '{0}'", wrHeadNoStr);
string sqlStr = string.Format("select Head.AutoId, Head.WarehouseReceipt as OrderHeadNo, Head.WarehouseReceiptDate as OrderHeadDate, Head.ReqDep, Head.WarehouseState as ReqState, Head.ApprovalType, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat, Head.WarehouseReceipt, Head.WarehouseState from INV_WarehouseReceiptHead as Head left join PUR_ApprovalType on Head.ApprovalType = PUR_ApprovalType.TypeNo where WarehouseReceipt = '{0}'", wrHeadNoStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询设计Bom的树类型信息
/// </summary>
public void QueryDesignBomTree(DataTable queryDataTable, string salesOrderNoStr, int IsUse)
{
string sqlStr = "";
if (IsUse >= 0)
{
sqlStr += string.Format(" and IsUse = {0}", IsUse);
}
sqlStr = string.Format("select * from V_PB_DesignBom_Tree where SalesOrderNo = '{0}' {1} Order by ReId", salesOrderNoStr, sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询请购单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="prReqNoStr">请购单号</param>
public void QueryPrReqList(DataTable queryDataTable, string prReqNoStr)
{
string sqlStr = "";
if (prReqNoStr != "")
{
sqlStr += string.Format(" and PrReqNo='{0}'", prReqNoStr);
}
sqlStr = string.Format("select PrReq.*, Parts.CodeName, PrReq.Qty-PrReq.OrderCount as Overplus from V_PUR_PrReqList_Order as PrReq left join SW_PartsCode as Parts on PrReq.CodeFileName = Parts.CodeFileName where PrReq.Qty>PrReq.OrderCount {0} order by PrReq.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询工位子表信息
/// </summary>
public void QueryStnSummaryList(DataTable queryDataTable, string ssNoStr)
{
string sqlStr = " 1=1";
if (ssNoStr != "")
{
sqlStr += string.Format(" and SSNo = '{0}'", ssNoStr);
}
sqlStr = string.Format("select * from SA_StnSummaryList where {0} Order By AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询生产计划单BOM信息
/// </summary>
public void QueryProductionScheduleBom(DataTable queryDataTable, string psNoStr, bool nullTable)
{
string sqlStr = string.Format(" and PsNo='{0}'", psNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select ps.*, pc.CodeName from PB_ProductionScheduleBom as ps left join SW_PartsCode as pc on ps.CodeFileName = pc.CodeFileName where 1=1 {0} order by ps.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询供货明细的全部信息（主表和子表关联的信息）
/// </summary>
public void QueryDeliveryDetail_AllInfo(DataTable queryDataTable, string smNoStr)
{
string sqlStr = " 1=1";
if (smNoStr != "")
{
sqlStr += string.Format(" and SMNo = '{0}'", smNoStr);
}
sqlStr = string.Format("select dd.AutoId, dd.SMNo, dd.DeliveryText, dd.FunctionDesc, dd.DeliveryQty, dd.Unit, dd.Amount, dd.Creator, md.MaterialName, md.MaterialBrand, md.MaterialDesc, md.MaterialCate, md.MaterialQty as MatQty, md.Unit as MatUnit, md.Amount as MatAmount from SA_DeliveryDetail as dd left join SA_MaterialDetail as md on dd.AutoId = md.DeliveryDetailNO where {0} order by dd.SMNo, dd.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询工单明细表
/// </summary>
public void QueryProductionPlanList(DataTable queryDataTable, string planNoStr)
{
string sqlStr = "";
if (planNoStr != "")
{
sqlStr += string.Format(" and PlanNo='{0}'", planNoStr);
}
sqlStr = string.Format("select PPList.*, Case When ISNULL(LevelCodeId, 0) = 0 then pc.CodeFileName else levelpc.CodeFileName end as CodeFileName, Case When ISNULL(LevelCodeId, 0) = 0 then pc.CodeName else levelpc.CodeName end as CodeName, PPList.Qty - PPList.WarehouseReceiptCount as Overplus from V_PB_ProductionPlanList_WarehouseReceipt as PPList left join SW_PartsCode as pc on PPList.CodeId = pc.AutoId left join SW_PartsCode as levelpc on PPList.LevelCodeId = levelpc.AutoId where 1 = 1 {0} order by PPList.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询请购单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="prReqNoStr">请购单号</param>
public void QueryPrReqList(DataTable queryDataTable, string prReqNoStr)
{
string sqlStr = "";
if (prReqNoStr != "")
{
sqlStr += string.Format(" and PrReq.PrReqNo='{0}'", prReqNoStr);
}
sqlStr = string.Format("select PrReq.*, Parts.CodeName, PUR_PrReqHead.ProjectNo, PUR_PrReqHead.StnNo, case when (select COUNT(*) from PUR_PIPR where PRListId = PrReq.AutoId) > 0 then 1 else 0 end as IsInquiry from PUR_PrReqList as PrReq left join SW_PartsCode as Parts on PrReq.CodeId = Parts.AutoId left join PUR_PrReqHead on PrReq.PrReqNo = PUR_PrReqHead.PrReqNo where 1=1 {0} order by PrReq.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询销售结账单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="settlementNoStr">结账单号</param>
public void QuerySettlementList(DataTable queryDataTable, string settleAccountNoStr, bool nullTable)
{
string sqlStr = string.Format(" and SettleAccountNo = '{0}'", settleAccountNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select SA_SettleAccountsList.* from SA_SettleAccountsList where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询出库单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="warehouseReceiptStr">出库单号</param>
public void QueryWarehouseReceiptList(DataTable queryDataTable, string warehouseReceiptStr, bool nullTable)
{
string sqlStr = string.Format(" and WarehouseReceipt='{0}'", warehouseReceiptStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select INV_WarehouseReceiptList.*, SW_PartsCode.CodeName, BS_ProjectList.ProjectNo from INV_WarehouseReceiptList left join SW_PartsCode on INV_WarehouseReceiptList.CodeFileName = SW_PartsCode.CodeFileName left join BS_ProjectList on INV_WarehouseReceiptList.ProjectName = BS_ProjectList.ProjectName where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询采购订单明细表
/// </summary>
public void QueryOrderList(DataTable queryDataTable, string orderHeadNoStr)
{
string sqlStr = "";
if (orderHeadNoStr != "")
{
sqlStr += string.Format(" and OrderHeadNo='{0}'", orderHeadNoStr);
}
sqlStr = string.Format("select OList.*, Parts.CodeName, OList.Qty-OList.WarehouseWarrentCount as Overplus from V_PUR_OrderList_WarehouseWarrent as OList left join SW_PartsCode as Parts on OList.CodeFileName = Parts.CodeFileName where OList.Qty>OList.WarehouseWarrentCount {0} order by OList.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询采购订单明细表
/// </summary>
public void QueryWWList(DataTable queryDataTable, string wwHeadNoStr)
{
string sqlStr = "";
if (wwHeadNoStr != "")
{
sqlStr += string.Format(" and WarehouseWarrant='{0}'", wwHeadNoStr);
}
sqlStr = string.Format("select WWList.*, Parts.CodeName, WWList.Qty-WWList.SettlementCount as Overplus, Olist.Unit, Olist.Tax from V_INV_WarehouseWarrentList_Settlement as WWList left join SW_PartsCode as Parts on WWList.CodeId = Parts.AutoId left join PUR_OrderList as Olist on Olist.AutoId = WWList.PoListAutoId where WWList.Qty>WWList.SettlementCount {0} order by WWList.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询工位和模块关系
/// </summary>
public void QueryStnListModule(DataTable queryDataTable, int stnSummaryListIdInt)
{
string sqlStr = " 1=1";
if (stnSummaryListIdInt != 0)
{
sqlStr += string.Format(" and stn.StnSummaryListId = {0}", stnSummaryListIdInt);
}
sqlStr = string.Format("select stn.AutoId, stn.StnSummaryListId, modu.SMNo, modu.FunctionDesc, modu.FunctionDetail from SA_StnSummaryListModule as stn left join SA_StnModule as modu on stn.StnModuleId = modu.SMNo where {0} Order by stn.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询询价单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="piHeadNoStr">询价单号</param>
public void QueryInquiryList(DataTable queryDataTable, string piHeadNoStr, bool nullTable)
{
string sqlStr = string.Format(" and PIHeadNo='{0}'", piHeadNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select PUR_InquiryList.*, SW_PartsCode.CodeFileName, SW_PartsCode.CodeName from PUR_InquiryList left join SW_PartsCode on PUR_InquiryList.CodeId = SW_PartsCode.AutoId where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询询价单明细和PR明细关系表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="piHeadNoStr">询价单号</param>
public void QueryPIPR_Relation(DataTable queryDataTable, string piHeadNoStr, bool nullTable)
{
string sqlStr = string.Format(" and PIHeadNo='{0}'", piHeadNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select PUR_PIPR.*, PUR_PrReqList.PrReqNo, PUR_PrReqList.CodeId, PUR_PrReqHead.ProjectNo, PUR_PrReqHead.StnNo, PUR_PrReqList.Qty as MaxPRQty from PUR_PIPR left join PUR_PrReqList on PUR_PIPR.PRListId = PUR_PrReqList.AutoId left join PUR_PrReqHead on PUR_PrReqList.PrReqNo = PUR_PrReqHead.PrReqNo where PIListId in (select AutoId from PUR_InquiryList where 1=1 {0})", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询采购结账单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="settlementNoStr">结账单号</param>
public void QuerySettlementList(DataTable queryDataTable, string settlementNoStr, bool nullTable)
{
string sqlStr = string.Format(" and SettlementNo='{0}'", settlementNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select PUR_SettlementList.*, SW_PartsCode.CodeName from PUR_SettlementList left join SW_PartsCode on PUR_SettlementList.CodeId = SW_PartsCode.AutoId where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询退货单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="returnedGoodsReportNoStr">退货单号</param>
public void QueryReturnedGoodsReportList(DataTable queryDataTable, string returnedGoodsReportNoStr, bool nullTable)
{
string sqlStr = string.Format(" and ReturnedGoodsReportNo='{0}'", returnedGoodsReportNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select INV_ReturnedGoodsReportList.*, SW_PartsCode.CodeName, BS_ProjectList.ProjectNo from INV_ReturnedGoodsReportList left join SW_PartsCode on INV_ReturnedGoodsReportList.CodeFileName = SW_PartsCode.CodeFileName left join BS_ProjectList on INV_ReturnedGoodsReportList.ProjectName = BS_ProjectList.ProjectName where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询库存移动单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="inventoryMoveNoStr">移动单号</param>
public void QueryInventoryMoveList(DataTable queryDataTable, string inventoryMoveNoStr, bool nullTable)
{
string sqlStr = string.Format(" and InventoryMoveNo='{0}'", inventoryMoveNoStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select INV_InventoryMoveList.*, SW_PartsCode.CodeName from INV_InventoryMoveList left join SW_PartsCode on INV_InventoryMoveList.CodeFileName = SW_PartsCode.CodeFileName where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询预算外入库单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="specialWarehouseWarrantStr">预算外入库单号</param>
public void QuerySpecialWarehouseWarrantList(DataTable queryDataTable, string specialWarehouseWarrantStr, bool nullTable)
{
string sqlStr = string.Format(" and SpecialWarehouseWarrant='{0}'", specialWarehouseWarrantStr);
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select INV_SpecialWarehouseWarrantList.*, SW_PartsCode.CodeName, BS_ProjectList.ProjectNo from INV_SpecialWarehouseWarrantList left join SW_PartsCode on INV_SpecialWarehouseWarrantList.CodeId = SW_PartsCode.AutoId left join BS_ProjectList on INV_SpecialWarehouseWarrantList.ProjectName = BS_ProjectList.ProjectName where 1=1 {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询Bom零件的库存数量
/// </summary>
public DataTable QueryBomWarehouseNowInfo(string codeFileNameStr, string repertoryNoStr)
{
string repNoSQLStr = "";
if (repertoryNoStr != "")
{
repNoSQLStr = string.Format(" and RepertoryNo = '{0}'", repertoryNoStr);
}
string sqlStr = string.Format("select bom.*, SW_PartsCode.CodeName, SW_PartsCode.AutoId as PCAutoId, SW_PartsCode.FilePath, SW_PartsCode.Brand, SW_PartsCode.CatgName, SW_PartsCode.CodeSpec, SW_PartsCode.Unit, BS_BomMaterieState.MaterieStateText, (select IsNull(SUM(Qty), 0) from INV_WarehouseNowInfo where INV_WarehouseNowInfo.CodeFileName = bom.CodeFileName {1} ) as WarehouseQty from F_BomMateriel_TreeRelation('{0}') as bom left join SW_PartsCode on bom.CodeFileName = SW_PartsCode.CodeFileName left join BS_BomManagement on bom.CodeFileName = BS_BomManagement.MaterielNo left join BS_BomMaterieState on BS_BomMaterieState.MaterieStateId = BS_BomManagement.MaterieStateId Order by CodeFileName", codeFileNameStr, repNoSQLStr);
return(BaseSQL.Query(sqlStr).Tables[0]);
}
/// <summary>
/// 查询包含某个零件的设计Bom的树类型信息
/// </summary>
public void QueryDesignBomTree_CodeFileName(DataTable queryDataTable, string salesOrderNoStr, Dictionary <int, string> codeIdList)
{
string codeIdStr = "";
foreach (int codeId in codeIdList.Keys)
{
codeIdStr += string.Format("{0},", codeId);
}
string sqlStr = string.Format("select * from V_PB_DesignBom_Tree where SalesOrderNo = '{0}' and PbBomNo in (select PbBomNo from PB_DesignBomList where IsNull(CodeId, 0) in ({1}) or IsNull(LevelCodeId, 0) in ({1})) Order by ReId", salesOrderNoStr, codeIdStr.Substring(0, codeIdStr.Length - 1));
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询生产计划信息
/// </summary>
public void QueryProductionScheduleBom(DataTable queryDataTable, string salesOrderNoStr, int bomListAutoId)
{
string sqlStr = string.Format(" SalesOrderNo = '{0}' and RemainQty > 0", salesOrderNoStr);
if (bomListAutoId != 0)
{
sqlStr += string.Format(" and BomListAutoId = {0}", bomListAutoId);
}
sqlStr = string.Format("select bom.AutoId, bom.BomListAutoId, bom.MaterielNo as CodeFileName, CodeName, PbBomNo, RemainQty, PlanDate, bom.IsBuy, PrReqNo, case when IsNull(absorb.AutoId, 0) = 0 then 0 else 1 end IsAbsorb from PB_ProductionScheduleBom as bom left join SW_PartsCode as pc on bom.MaterielNo = pc.CodeFileName left join PB_ScheduleAbsorbe as absorb on bom.AutoId = absorb.MainAutoId where {0} Order by bom.AutoId", sqlStr);
//sqlStr = string.Format("select bom.AutoId, bom.BomListAutoId, bom.MaterielNo as CodeFileName, CodeName, PbBomNo, RemainQty, PlanDate, IsAll from PB_ProductionScheduleBom as bom left join SW_PartsCode as pc on bom.MaterielNo = pc.CodeFileName where {0} union all select bom.AutoId, bom.BomListAutoId, bom.MaterielNo as CodeFileName, CodeName, PbBomNo, RemainQty, PlanDate, IsAll from PB_ProductionScheduleBom as bom left join SW_PartsCode as pc on bom.MaterielNo = pc.CodeFileName where bom.BomListAutoId in (select AbsorbedAutoId from DesignBomListAbsorbe where MainAutoId = {1})", sqlStr, bomListAutoId);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 检测数据库中生产计划单状态是否可以操作
/// </summary>
public bool CheckPSState(DataTable headTable, string psNoListStr, bool checkNoApprover, bool checkApprover, bool checkClosed, bool checkApproverBetween)
{
string sqlStr = string.Format("select PsNo, PsState from PB_ProductionSchedule where PsNo in ({0})", psNoListStr);
DataTable tmpTable = BaseSQL.Query(sqlStr).Tables[0];
for (int i = 0; i < tmpTable.Rows.Count; i++)
{
int reqState = DataTypeConvert.GetInt(tmpTable.Rows[i]["PsState"]);
switch (reqState)
{
case 1:
if (checkNoApprover)
{
MessageHandler.ShowMessageBox(string.Format("生产计划单[{0}]未审批，不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["PsNo"])));
headTable.RejectChanges();
return(false);
}
break;
case 2:
if (checkApprover)
{
MessageHandler.ShowMessageBox(string.Format("生产计划单[{0}]已经审批，不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["PsNo"])));
headTable.RejectChanges();
return(false);
}
break;
case 3:
if (checkClosed)
{
MessageHandler.ShowMessageBox(string.Format("生产计划单[{0}]已经关闭，不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["PsNo"])));
headTable.RejectChanges();
return(false);
}
break;
case 4:
if (checkApproverBetween)
{
MessageHandler.ShowMessageBox(string.Format("生产计划单[{0}]已经审批中，不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["PsNo"])));
headTable.RejectChanges();
return(false);
}
break;
}
}
return(true);
}
/// <summary>
/// 查询基础功能模块信息
/// </summary>
public void QueryStnModule(DataTable queryDataTable, string beginDateStr, string endDateStr, string commonStr)
{
string sqlStr = " 1=1";
if (beginDateStr != "")
{
sqlStr += string.Format(" and GetTime between '{0}' and '{1}'", beginDateStr, endDateStr);
}
if (commonStr != "")
{
sqlStr += string.Format(" and (SMNo like '%{0}%' or FunctionDesc like '%{0}%' or FunctionDetail like '%{0}%')", commonStr);
}
sqlStr = string.Format("Select * from SA_StnModule where {0} Order By SMNo", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询请购单明细表
/// </summary>
/// <param name="queryDataTable">要查询填充的数据表</param>
/// <param name="prReqNoStr">请购单号</param>
public void QueryPrReqList(DataTable queryDataTable, string prReqNoStr)
{
string sqlStr = "";
if (prReqNoStr != "")
{
sqlStr += string.Format(" and PrReqNo='{0}'", prReqNoStr);
}
if (!SystemInfo.PrListDistributionAllHandle)
{
sqlStr += string.Format(" and IsNull(Arrangement, {0})={0}", SystemInfo.user.AutoId);
}
sqlStr = string.Format("select PrReq.*, Parts.CodeName, PrReq.Qty-PrReq.OrderCount as Overplus from V_PUR_PrReqList_Order as PrReq left join SW_PartsCode as Parts on PrReq.CodeId = Parts.AutoId where PrReq.Qty>PrReq.OrderCount {0} order by PrReq.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}