public override object Do(object obj) { GetPurchaseOrderBP bpObj = (GetPurchaseOrderBP)obj; if (bpObj == null) { return(null); } List <PurchaseOrderDTO> dtoList = new List <PurchaseOrderDTO>(); DataTable dt = GetPurchaseOrder(bpObj); SetDataTable(dt, dtoList); return(dtoList); }
/// <summary> /// 根据条传入件获取可预付款的采购订单 /// </summary> /// <param name="bpObj"></param> private DataTable GetPurchaseOrder(GetPurchaseOrderBP bpObj) { string sql = GetSqlStr(bpObj); return(GetDataTable(sql)); }
/// <summary> /// 拼接SQL查询语句 /// </summary> /// <param name="bpObj"></param> /// <returns></returns> private string GetSqlStr(GetPurchaseOrderBP bpObj) { StringBuilder sb = new StringBuilder(); sb.Append(" select * from ("); sb.Append(" select C.Name as DocumentType,A.DocNo,A.ID,"); sb.Append(" E.ID as POOrg,E.Code as POOrg_Code,F.Name as POOrg_Name,"); sb.Append(" G.ID as SrcOrg,G.Code as SrcOrg_Code,H.Name as SrcOrg_Name,"); sb.Append(" I.ID as Currency,I.Code as Currency_Code,J.Name as Currency_Name,"); sb.Append(" I.Symbol,max(i.MoneyRound_Precision) as MoneyRound_Precision,"); sb.Append(" max(I.MoneyRound_RoundType) as MoneyRound_RoundType,max(I.MoneyRound_RoundValue) as MoneyRound_RoundValue,"); sb.Append(" K.ID as Dept,K.Code as Dept_Code,K1.Name as Dept_Name,L.ID as Buyer,L.Code as Buyer_Code,L1.Name as Buyer_Name,"); sb.Append(" max(A.BusinessDate) as BusinessDate,A.Supplier_Supplier,A.Supplier_Code,D.Supplier_Name,A2.Description as Meno,"); sb.Append(" isnull(SUM(B.TotalMnyTC),0) as TotalMoney,"); //--总金额 sb.Append(" SUM(case when A.DescFlexField_PrivateDescSeg3='' or A.DescFlexField_PrivateDescSeg3 IS NULL THEN '0' ELSE Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg3) END) as TotalPreMoney,"); //--已制预付单金额 sb.Append(" SUM(case when A.DescFlexField_PrivateDescSeg4='' or A.DescFlexField_PrivateDescSeg4 IS NULL THEN '0' ELSE Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg4) END) as TotalPreVerMoney,"); //--预付已核销金额 sb.Append(" SUM(case when B.DescFlexSegments_PrivateDescSeg8='' or B.DescFlexSegments_PrivateDescSeg8 IS NULL THEN '0' ELSE Convert(decimal(18,9),B.DescFlexSegments_PrivateDescSeg8) END) as TotalPayMoney,"); //--已制付款通知单金额 sb.Append(" SUM(case when B.DescFlexSegments_PrivateDescSeg9='' or B.DescFlexSegments_PrivateDescSeg9 IS NULL THEN '0' ELSE Convert(decimal(18,9),B.DescFlexSegments_PrivateDescSeg9) END) as TotalPayRedMoney,"); //--付款通知单红冲金额 sb.Append(" SUM(case when A.DescFlexField_PrivateDescSeg5='' or A.DescFlexField_PrivateDescSeg5 IS NULL THEN '0' ELSE Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg5) END) as TotalPreRedMoney,"); //--预付单红冲金额 sb.Append(" SUM(case when A.DescFlexField_PrivateDescSeg6='' or A.DescFlexField_PrivateDescSeg6 IS NULL THEN '0' ELSE Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg6) END) as TotalPayMoveMoney,"); //--预付款挪出金额 sb.Append(" SUM(case when A.DescFlexField_PrivateDescSeg7='' or A.DescFlexField_PrivateDescSeg7 IS NULL THEN '0' ELSE Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg7) END) as TotalPayInitMoney"); //--预付款挪入金额 //sb.Append(" isnull(Sum(Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg4)),0) as TotalPreVerMoney,");//--预付已核销金额 //sb.Append(" isnull(Sum(Convert(decimal(18,9),B.DescFlexSegments_PrivateDescSeg8)),0) as TotalPayMoney,");//--已制付款通知单金额 //sb.Append(" isnull(Sum(Convert(decimal(18,9),B.DescFlexSegments_PrivateDescSeg9)),0) as TotalPayRedMoney,");//--付款通知单红冲金额 //sb.Append(" isnull(Sum(Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg5)),0) as TotalPreRedMoney,");//--预付单红冲金额 //sb.Append(" isnull(Sum(Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg6)),0) as TotalPayMoveMoney,");//--预付款挪出金额 //sb.Append(" isnull(Sum(Convert(decimal(18,9),A.DescFlexField_PrivateDescSeg7)),0) as TotalPayInitMoney");//--预付款挪入金额 sb.Append(" from PM_PurchaseOrder A"); sb.Append(" left join PM_POLine B on A.ID=B.PurchaseOrder"); sb.Append(" left join PM_PODocType_Trl C on A.DocumentType=C.ID and C.SysMLFlag='zh-CN'"); sb.Append(" left join PM_PurchaseOrder_Trl D on A.ID=D.ID and D.SysMLFlag='zh-CN'"); sb.Append(" left join PM_POMemo A1 on A.ID=A1.PurchaseOrder"); sb.Append(" left join PM_POMemo_Trl A2 on A2.ID=A1.ID and A2.SysMLFlag='zh-CN'"); sb.Append(" left join Base_Organization E on A.DescFlexField_PrivateDescSeg2=E.Code"); sb.Append(" left join Base_Organization_Trl F on E.ID=F.ID and F.SysMLFlag='zh-CN'"); sb.Append(" left join Base_Organization G on A.Org=G.ID"); sb.Append(" left join Base_Organization_Trl H on H.ID=G.ID and H.SysMLFlag='zh-CN'"); sb.Append(" left join Base_Currency I on A.TC=I.ID"); sb.Append(" left join Base_Currency_Trl J on I.ID=J.ID and J.SysMLFlag='zh-CN'"); sb.Append(" left join CBO_Department K on A.PurDept=K.ID"); sb.Append(" left join CBO_Department_Trl K1 on A.PurDept=K1.ID and K1.SysMLFlag='zh-CN'"); sb.Append(" left join CBO_Operators L on A.PurOper=L.ID"); sb.Append(" left join CBO_Operators_Trl L1 on A.PurOper=L1.ID AND L1.SysMLFlag='zh-CN'"); sb.Append(" where A.Status=2"); #region 查询条件 if (!string.IsNullOrEmpty(bpObj.Supplier_Code))//供应商编码 { sb.Append(" and A.Supplier_Code='" + bpObj.Supplier_Code + "'"); } if (!string.IsNullOrEmpty(bpObj.POOrgCode))//采购组织编码 { sb.Append(" and A.DescFlexField_PrivateDescSeg2='" + bpObj.POOrgCode + "'"); } if (bpObj.SrcOrg > 0L)//来源组织 { sb.Append(" and A.Org=" + bpObj.SrcOrg.ToString()); } //单号从有录入,单号到未录入,按模糊查询 if (string.IsNullOrEmpty(bpObj.PODocNoFrom) == false && string.IsNullOrEmpty(bpObj.PODocNoTo) == true) { sb.Append(" and A.DocNo like '%" + bpObj.PODocNoFrom + "%'"); } //单号从有录入,单号到有录入,按范围查询 if (string.IsNullOrEmpty(bpObj.PODocNoFrom) == false && string.IsNullOrEmpty(bpObj.PODocNoTo) == false) { sb.Append(" and A.DocNo >= '" + bpObj.PODocNoFrom + "' and A.DocNo <= '" + bpObj.PODocNoTo + "'"); } //日期从 if (bpObj.BusDateFrom > DateTime.MinValue) { sb.Append(" and A.BusinessDate >= '" + bpObj.BusDateFrom + "'"); } //日期到 if (bpObj.BusDateTo > DateTime.MinValue) { sb.Append(" and A.BusinessDate <= '" + bpObj.BusDateTo + "'"); } if (bpObj.Currency > 0L) { sb.Append(" and A.TC=" + bpObj.Currency.ToString()); } if (!string.IsNullOrEmpty(bpObj.Buyer_Code))//采购员编码 { sb.Append(" and L.Code='" + bpObj.Buyer_Code + "'"); } if (!string.IsNullOrEmpty(bpObj.Dept_Code))//部门编码 { sb.Append(" and K.Code='" + bpObj.Dept_Code + "'"); } #endregion sb.Append(" group by C.Name,A.DocNo,A.ID,E.ID,E.Code,F.Name,G.ID,G.Code,H.Name,"); sb.Append(" I.ID,I.Code,J.Name,I.Symbol,K.ID,K.Code,K1.Name,L.ID,L.Code,L1.Name,"); sb.Append(" A.Supplier_Supplier,A.Supplier_Code,D.Supplier_Name,A2.Description"); sb.Append(" ) as A"); sb.Append(" where A.TotalMoney-A.TotalPreMoney-A.TotalPayMoney+A.TotalPreVerMoney+A.TotalPayRedMoney+A.TotalPreRedMoney+A.TotalPayMoveMoney-A.TotalPayInitMoney>0"); return(sb.ToString()); }