Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        /// <summary>
        /// 根据条传入件获取可预付款的采购订单
        /// </summary>
        /// <param name="bpObj"></param>
        private DataTable GetPurchaseOrder(GetPurchaseOrderBP bpObj)
        {
            string sql = GetSqlStr(bpObj);

            return(GetDataTable(sql));
        }
Esempio n. 3
0
        /// <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());
        }