/// <summary>
/// 采购询价单主表的SQL
/// </summary>
public string QueryInquiryHead_SQL(string beginDateStr, string endDateStr, string bussinessBaseNoStr, string departmentNoStr, string projectNoStr, int codeIdInt, int creatorInt, string commonStr, bool nullTable)
{
string sqlStr = " 1=1";
if (beginDateStr != "")
{
sqlStr += BaseSQL.GetDateRegion_SingleColumn_WhereSql("OrderHeadDate", beginDateStr, endDateStr);
}
if (bussinessBaseNoStr != "")
{
sqlStr += string.Format(" and BussinessBaseNo='{0}'", bussinessBaseNoStr);
}
if (departmentNoStr != "")
{
sqlStr += string.Format(" and DepartmentNo='{0}'", departmentNoStr);
}
if (projectNoStr != "")
{
sqlStr += string.Format(" and ProjectNo='{0}'", projectNoStr);
}
if (codeIdInt != 0)
{
sqlStr += string.Format(" and PIHeadNo in (select PIHeadNo from PUR_InquiryList where CodeId = {0})", codeIdInt);
}
if (creatorInt != 0)
{
sqlStr += string.Format(" and Creator={0}", creatorInt);
}
if (commonStr != "")
{
sqlStr += string.Format(" and (PIHeadNo like '%{0}%' or ProjectNo like '%{0}%' or StnNo like '%{0}%' or PIRemark like '%{0}%' or PUR_InquiryHead.PIHeadNo in (select PIHeadNo from PUR_InquiryList join PUR_PIPR on PIListId = PUR_InquiryList.AutoId join PUR_PrReqList on PRListId = PUR_PrReqList.AutoId where PUR_PrReqList.PrReqNo like '%{0}%'))", commonStr);
}
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select * from PUR_InquiryHead where {0} order by AutoId", sqlStr);
return(sqlStr);
}
/// <summary>
/// 查询出库单表头的SQL
/// </summary>
public string QueryWarehouseReceiptHead_SQL(string beginDateStr, string endDateStr, string reqDepStr, int repertoryIdInt, int locationIdInt, string wrTypeNoStr, string manufactureNoStr, int warehouseStateInt, int creatorInt, int approverInt, string commonStr, bool nullTable)
{
string sqlStr = " 1=1";
if (beginDateStr != "")
{
//sqlStr += string.Format(" and WarehouseReceiptDate between '{0}' and '{1}'", beginDateStr, endDateStr);
sqlStr += BaseSQL.GetDateRegion_SingleColumn_WhereSql("WarehouseReceiptDate", beginDateStr, endDateStr);
}
if (reqDepStr != "")
{
sqlStr += string.Format(" and ReqDep='{0}'", reqDepStr);
}
if (repertoryIdInt != 0)
{
sqlStr += string.Format(" and RepertoryId={0}", repertoryIdInt);
}
if (locationIdInt != 0)
{
sqlStr += string.Format(" and RepertoryLocationId={0}", locationIdInt);
}
if (wrTypeNoStr != "")
{
sqlStr += string.Format(" and WarehouseReceiptTypeNo='{0}'", wrTypeNoStr);
}
if (manufactureNoStr != "")
{
sqlStr += string.Format(" and ManufactureNo='{0}'", manufactureNoStr);
}
if (warehouseStateInt != 0)
{
sqlStr += string.Format(" and WarehouseState={0}", warehouseStateInt);
}
if (creatorInt != 0)
{
sqlStr += string.Format(" and Creator={0}", creatorInt);
}
if (commonStr != "")
{
sqlStr += string.Format(" and (WarehouseReceipt like '%{0}%' or Remark like '%{0}%' or WarehouseReceipt in (select WarehouseReceipt from INV_WarehouseReceiptList where ProductionPlanListId in (Select AutoId from PB_ProductionPlanList where PlanNo like '%{0}%')))", commonStr);
}
if (approverInt >= 0)
{
if (approverInt == 0)
{
sqlStr += string.Format(" and WarehouseState in (1,4)");
}
else
{
//sqlStr = string.Format("select INV_WarehouseReceiptHead.* from INV_WarehouseReceiptHead left join PUR_ApprovalType on INV_WarehouseReceiptHead.ApprovalType = PUR_ApprovalType.TypeNo where {0} and INV_WarehouseReceiptHead.WarehouseState in (1, 4) and( (PUR_ApprovalType.ApprovalCat = 0 and exists (select * from (select top 1 * from F_WarehouseReceiptNoApprovalList(INV_WarehouseReceiptHead.WarehouseReceipt, INV_WarehouseReceiptHead.ApprovalType) Order by AppSequence) as minlist where Approver = {1})) or (PUR_ApprovalType.ApprovalCat = 1 and exists (select * from F_WarehouseReceiptNoApprovalList(INV_WarehouseReceiptHead.WarehouseReceipt, INV_WarehouseReceiptHead.ApprovalType) where Approver = {1}))) order by AutoId", sqlStr, approverInt);
sqlStr = string.Format("select Head.* from INV_WarehouseReceiptHead as Head left join PUR_ApprovalType on Head.ApprovalType = PUR_ApprovalType.TypeNo where {0} and Head.WarehouseState in (1, 4) and ((PUR_ApprovalType.ApprovalCat = 0 and exists (select * from(select top 1 * from F_OrderNoApprovalList(Head.WarehouseReceipt, Head.ApprovalType) Order by AppSequence) as minlist where Approver = {1})) or(PUR_ApprovalType.ApprovalCat = 1 and exists(select * from F_OrderNoApprovalList(Head.WarehouseReceipt, Head.ApprovalType) where Approver = {1}))) order by AutoId", sqlStr, approverInt);
return(sqlStr);
}
}
if (nullTable)
{
sqlStr += " and 1=2";
}
sqlStr = string.Format("select * from INV_WarehouseReceiptHead where {0} order by AutoId", sqlStr);
return(sqlStr);
}
