现金流量还原表获取报表数据

using Kingdee.BOS.App;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Orm.DataEntity;
using System;
using Kingdee.BOS.Contracts.Report;
using System.ComponentModel;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS;
using System.Collections;
using Kingdee.BOS.Core.Util;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS.Core.List;
using VTR.ZHX.Plugin.Common;
using System.Data;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Model.ReportFilter;
using Kingdee.BOS.Core.CommonFilter;


namespace JN.K3.YDL.App.Report
{
    [Description("现金流量还原表"), Kingdee.BOS.Util.HotUpdate]
    public class CashFlowRestoreRpt : SysReportBaseService
    {
        public override void Initialize()
        {
            base.Initialize();
            base.ReportProperty.IdentityFieldName = "FIDENTITYID";
            base.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            base.ReportProperty.IsGroupSummary = true;    //报表是否支持分组汇总
            this.ReportProperty.IsUIDesignerColumns = true;

            SetDecimalControl("FAmount");
            SetDecimalControl("F_VTR_SumReableBill");
            SetDecimalControl("F_VTR_SumPayableBill");
            SetDecimalControl("F_VTR_SumAmount");
        }

        /// <summary>
        /// 设置精度控制
        /// </summary>
        /// <param name="byDecimalControlFieldName">被控制字段名</param>
        private void SetDecimalControl(string byDecimalControlFieldName)
        {
            DecimalControlField field = new DecimalControlField
            {
                ByDecimalControlFieldName = byDecimalControlFieldName,
                DecimalControlFieldName = "F_VTR_Precision"//精度
            };
            if (base.ReportProperty.DecimalControlFieldList == null)
                base.ReportProperty.DecimalControlFieldList = new List<DecimalControlField> { field };
            else
                base.ReportProperty.DecimalControlFieldList.Add(field);
        }

        /// <summary>
        /// 设置报表标题
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            ReportTitles titles = new ReportTitles();
            //账簿
            List<string> bookNames = new List<string>();
            var F_VTR_ACCTBOOKIDS = customFilter["F_VTR_ACCTBOOKIDS"] as DynamicObjectCollection;
            if (F_VTR_ACCTBOOKIDS != null && F_VTR_ACCTBOOKIDS.Count > 0)
            {
                foreach (var F_VTR_ACCTBOOKID in F_VTR_ACCTBOOKIDS)
                {
                    string bookName = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS"].GetProperty("Name");
                    bookNames.Add(bookName);
                }
            }
            titles.AddTitle("FACCTBOOKID", string.Join("/", bookNames));

            //期间
            string titleValue = "";
            var FindType = customFilter["FindType"].GetString();
            if (FindType == "0")
            {
                var FSTARTYEAR = customFilter["STARTYEAR"].GetString();
                var FSTARTPERIOD = customFilter["STARTPERIOD"].GetString();
                var FENDYEAR = customFilter["ENDYEAR"].GetString();
                var FENDPERIOD = customFilter["ENDPERIOD"].GetString();
                titleValue = FSTARTYEAR + "-" + FSTARTPERIOD + "至" + FENDYEAR + "-" + FENDPERIOD;
            }
            else
            {
                var startDate = Convert.ToDateTime(customFilter["STARTDATE"]);
                var endDate = Convert.ToDateTime(customFilter["ENDDATE"]);
                titleValue = startDate.ToString("yyyy-MM-dd") + "至" + endDate.ToString("yyyy-MM-dd");
            }
            titles.AddTitle("FPERIOD", titleValue);

            //币别
            string CURRENCYID = customFilter["CURRENCYID"].GetString();
            if (CURRENCYID == "0")
            {
                titles.AddTitle("FCURRENCYID", "综合本位币");
            }
            else
            {
                var BD_Currency = Synthesize.GetSingleData(this.Context, "BD_Currency", customFilter["CURRENCYID"].GetString());
                if (BD_Currency != null)
                {
                    titleValue = BD_Currency["Name"].GetString();
                    titles.AddTitle("FCURRENCYID", titleValue);
                }
            }
            return titles;
        }

        /// <summary>
        /// 构建报表SQL以及临时表
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="tableName"></param>
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            //设置排序
            SetSort(filter);
            //插入现金流项目金额数据
            InsertInitData(filter, tableName);
            //插入现金流项目外部金额数据
            InsertExternalCashFlowAmount(filter, tableName);
            //更新应收应付票据金额
            UpdateRePayableBillAmount(filter, tableName);
            //更新累计金额
            UpdateSumAmount(tableName);
        }

        /// <summary>
        /// 插入现金流项目金额数据
        /// </summary>
        /// <param name="tableName"></param>
        private void InsertInitData(IRptParams filter, string tableName)
        {
            var tmpTables = GetKingdeeCashFlowRptDataTable(filter);
            List<string> selectSqls = new List<string>();
            foreach (var tmpTable in tmpTables)
            {
                string tmpSql = string.Format(@"SELECT *
                                                FROM   {0}
                                                WHERE  fidentityid < (SELECT fidentityid
                                                                      FROM   {0}
                                                                      WHERE  FItemTypeDesc = '补充资料：')  ", tmpTable);
                selectSqls.Add(tmpSql);
            }
            string selectSql = string.Join(" UNION ALL ", selectSqls);
            string sql = string.Format(@"/*dialect*/ 
                                       SELECT *,CAST(0 AS DECIMAL(38, 10)) as F_VTR_SumReableBill , 
                                                CAST(0 AS DECIMAL(38, 10)) as F_VTR_SumPayableBill , 
                                                CAST(0 AS DECIMAL(38, 10)) as F_VTR_SumAmount , 
                                                2 as F_VTR_Precision,
                                                {0} INTO {1} FROM   (   SELECT  FItemTypeDesc,
                                                                                FItemID,
                                                                                FItemNo,
                                                                                fcashitem,
                                                                                CONVERT(FLOAT, fidentityid) AS F_VTR_Seq,
                                                                                isnull(SUM(FAmount),0)      AS FAmount
                                                                        FROM   ( {2} ) t
                                                                        GROUP  BY FItemTypeDesc,
                                                                                    FItemID,
                                                                                    FItemNo,
                                                                                    fcashitem,
                                                                                    fidentityid 
                                                                    ) t ", base.KSQL_SEQ, tableName, selectSql);
            DBUtils.Execute(this.Context, sql);
        }

        /// <summary>
        /// 设置排序
        /// </summary>
        /// <param name="filter"></param>
        private void SetSort(IRptParams filter)
        {
            string args = "F_VTR_Seq";
            if (!string.IsNullOrWhiteSpace(filter.FilterParameter.SortString))
            {
                args = filter.FilterParameter.SortString;
            }
            base.KSQL_SEQ = string.Format(base.KSQL_SEQ, args);//排序字段FIDENTITYID
        }

        /// <summary>
        /// 获取过滤条件
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        private string GetFilterString(IRptParams filter)
        {
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            StringBuilder stringBuilder = new StringBuilder();
            //多个账簿
            List<string> ACCTBOOKIDS = new List<string>();
            var F_VTR_ACCTBOOKIDS = customFilter["F_VTR_ACCTBOOKIDS"] as DynamicObjectCollection;
            if (F_VTR_ACCTBOOKIDS != null && F_VTR_ACCTBOOKIDS.Count > 0)
            {
                foreach (var F_VTR_ACCTBOOKID in F_VTR_ACCTBOOKIDS)
                {
                    string F_VTR_ACCTBOOKIDS_Id = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS_Id"].GetString();
                    ACCTBOOKIDS.Add(F_VTR_ACCTBOOKIDS_Id);
                }
                stringBuilder.AppendLine(string.Format(" FACCOUNTBOOKID IN ({0})", string.Join(",", ACCTBOOKIDS)));
            }
            //期间
            var FindType = customFilter["FindType"].GetString();
            if (FindType == "0")
            {
                var FSTARTYEAR = customFilter["STARTYEAR"].GetString();
                var FSTARTPERIOD = customFilter["STARTPERIOD"].GetString();
                var startDate = Convert.ToDateTime(FSTARTYEAR + "-" + FSTARTPERIOD + "-1");
                var FENDYEAR = customFilter["ENDYEAR"].GetString();
                var FENDPERIOD = customFilter["ENDPERIOD"].GetString();
                var endDate = Convert.ToDateTime(FENDYEAR + "-" + FENDPERIOD + "-1").AddMonths(1).AddDays(-1);
                stringBuilder.AppendLine(string.Format(" AND FDATE BETWEEN '{0}' AND '{1}' ", startDate, endDate));
            }
            else
            {
                var startDate = Convert.ToDateTime(customFilter["STARTDATE"]);
                var endDate = Convert.ToDateTime(customFilter["ENDDATE"]);
                stringBuilder.AppendLine(string.Format(" AND FDATE BETWEEN '{0}' AND '{1}' ", startDate, endDate));
            }
            //币别
            var FCURRENCYID = customFilter["CURRENCYID"].GetString();
            if (FCURRENCYID != "")
                stringBuilder.AppendLine(string.Format(" AND FCURRENCYID = {0} ", FCURRENCYID));

            //是否包括未过账凭证
            var FIncNotPost = Convert.ToBoolean(customFilter["IncludeNotPost"]);
            if (!FIncNotPost)
                stringBuilder.AppendLine(string.Format(" AND FPOSTERID <> 0 "));//只统计过账凭证


            //过滤界面条件页签
            if (!string.IsNullOrWhiteSpace(filter.FilterParameter.FilterString))
                stringBuilder.AppendLine(" AND " + filter.FilterParameter.FilterString);
            return stringBuilder.ToString();
        }


        /// <summary>
        /// 获取金蝶现金流量表数据
        /// </summary>
        public List<string> GetKingdeeCashFlowRptDataTable(IRptParams filter)
        {
            List<string> tmpTableNames = new List<string>();
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            var F_VTR_ACCTBOOKIDS = customFilter["F_VTR_ACCTBOOKIDS"] as DynamicObjectCollection;
            if (F_VTR_ACCTBOOKIDS != null && F_VTR_ACCTBOOKIDS.Count > 0)
            {
                ISysReportService sysReportService = ServiceFactory.GetSysReportService(this.Context);
                var reportMetadata = FormMetaDataCache.GetCachedFormMetaData(this.Context, "GL_Rpt_CashFlow");//标准现金流量表
                foreach (var F_VTR_ACCTBOOKID in F_VTR_ACCTBOOKIDS)//多个账簿
                {
                    customFilter["ACCTBOOKID_Id"] = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS_Id"];
                    customFilter["ACCTBOOKID"] = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS"];
                    ReportServiceParameter reportServiceParam = new ReportServiceParameter();
                    reportServiceParam.RptFilterParams = filter;
                    reportServiceParam.Context = this.Context;
                    reportServiceParam.PageId = Guid.NewGuid().ToString();
                    reportServiceParam.BusinessInfo = reportMetadata.BusinessInfo;
                    var result = sysReportService.GetReportData(reportServiceParam); //调用服务、查询报表，简单账表 
                    if (result != null && result.DataSource != null)
                        tmpTableNames.Add(result.DataSource.TableName);
                }
            }
            return tmpTableNames;
        }

        /// <summary>
        /// 获取金蝶现金流量查询明细数据
        /// </summary>
        public string GetKingdeeCashFlowRptDetailDataTable(IRptParams filter)
        {
            string tmpTableName = "";
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            var F_VTR_ACCTBOOKIDS = customFilter["F_VTR_ACCTBOOKIDS"] as DynamicObjectCollection;
            if (F_VTR_ACCTBOOKIDS != null && F_VTR_ACCTBOOKIDS.Count > 0)
            {
                ISysReportService sysReportService = ServiceFactory.GetSysReportService(this.Context);
                IPermissionService permissionService = ServiceFactory.GetPermissionService(this.Context);
                var filterMetadata = FormMetaDataCache.GetCachedFilterMetaData(this.Context);
                var reportMetadata = FormMetaDataCache.GetCachedFormMetaData(this.Context, "GL_Rpt_CashflowQuery");
                var reportfilterMetadata = FormMetaDataCache.GetCachedFormMetaData(this.Context, "GL_Rpt_CashflowQueryFilter");//现金流量查询过滤条件
                var reportfilterServiceProvider = reportfilterMetadata.BusinessInfo.GetForm().GetFormServiceProvider();
                var model = new SysReportFilterModel();
                model.SetContext(this.Context, reportfilterMetadata.BusinessInfo, reportfilterServiceProvider);
                model.FormId = reportfilterMetadata.BusinessInfo.GetForm().Id;
                model.FilterObject.FilterMetaData = filterMetadata;
                model.InitFieldList(reportMetadata, reportfilterMetadata);
                model.GetSchemeList();
                model.LoadDefaultScheme();//加载默认方案
                var filterParameter = model.GetFilterParameter();
                //这里填写普通过滤方案，填充实体信息
                DynamicObject fi1terObj = filterParameter.CustomFilter;
                fi1terObj["CURRENCYID"] = customFilter["CURRENCYID"];
                fi1terObj["FFilteByCashflowCurrency"] = true;//按流量币别过滤
                fi1terObj["STARTYEAR"] = customFilter["STARTYEAR"];
                fi1terObj["STARTPERIOD"] = customFilter["STARTPERIOD"];
                fi1terObj["ENDYEAR"] = customFilter["ENDYEAR"];
                fi1terObj["ENDPERIOD"] = customFilter["ENDPERIOD"];
                fi1terObj["NOTPOSTVOUCHER"] = customFilter["IncludeNotPost"];
                var ACCTBOOKID = fi1terObj["ACCTBOOKID"] as DynamicObjectCollection;
                foreach (var F_VTR_ACCTBOOKID in F_VTR_ACCTBOOKIDS)//多个账簿
                {
                    DynamicObject newBook = new DynamicObject(ACCTBOOKID.DynamicCollectionItemPropertyType);
                    newBook["ACCTBOOKID_Id"] = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS_Id"];
                    newBook["ACCTBOOKID"] = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS"];
                    ACCTBOOKID.Add(newBook);
                }
                int index = filterParameter.ColumnInfo.Count + 1;
                ColumnField columnField = new ColumnField(new LocaleValue("核算维度编码"), "FDETAILNUMBER", index, 100, 100, true, true);
                columnField.FieldName = "FDETAILNUMBER";
                filterParameter.ColumnInfo.Add(columnField);
                index = filterParameter.ColumnInfo.Count + 1;
                columnField = new ColumnField(new LocaleValue("核算维度名称"), "FDETAILNAME", index, 100, 100, true, true);
                columnField.FieldName = "FDETAILNAME";
                filterParameter.ColumnInfo.Add(columnField);
                //构建过滤参数
                RptParams rptParams = new RptParams();
                rptParams.FormId = "GL_Rpt_CashflowQuery";
                rptParams.EndRow = 200;
                rptParams.StartRow = 1;
                rptParams.FilterParameter = filterParameter;
                rptParams.CustomParams.Add("OpenParameter", new Dictionary<string, object>());
                //构建报表参数
                ReportServiceParameter reportServiceParam = new ReportServiceParameter();
                reportServiceParam.RptFilterParams = rptParams;
                reportServiceParam.Context = this.Context;
                reportServiceParam.PageId = Guid.NewGuid().ToString();
                reportServiceParam.BusinessInfo = reportMetadata.BusinessInfo;
                var result = sysReportService.GetReportData(reportServiceParam); //调用服务、查询报表，简单账表 
                if (result != null && result.DataSource != null)
                    tmpTableName = result.DataSource.TableName;
            }
            return tmpTableName;
        }

        /// <summary>
        /// 插入外部累计现金及电汇金额
        /// </summary>
        public void InsertExternalCashFlowAmount(IRptParams filter, string tableName)
        {
            Dictionary<string, string> ExternalCFItems = new Dictionary<string, string>();
            ExternalCFItems.Add("CI01.01.01", "　　其中：外部销售商品、提供劳务收到的现金");
            ExternalCFItems.Add("CI01.01.03", "　　其中：收到外部其他与经营活动有关的现金");
            ExternalCFItems.Add("CI01.02.01", "　　其中：外部购买商品、接受劳务支付的现金");
            ExternalCFItems.Add("CI01.02.04", "　　其中：支付外部其他与经营活动有关的现金");
            ExternalCFItems.Add("CI02.01.01", "　　其中：收回外部投资所收到的现金");
            ExternalCFItems.Add("CI02.01.02", "　　其中：取得外部投资收益所收到的现金");
            ExternalCFItems.Add("CI02.01.05", "　　其中：收到其他外部与投资活动有关的现金");
            ExternalCFItems.Add("CI02.02.01", "　　其中：外部购建固定资产、无形资产和其他长期资产支付的现金");
            ExternalCFItems.Add("CI02.02.02", "　　其中：外部投资所支付的现金");
            ExternalCFItems.Add("CI02.02.04", "　　其中：支付其他外部与投资活动有关的现金");
            ExternalCFItems.Add("CI03.01.01", "　　其中：吸收外部投资所收到的现金");
            ExternalCFItems.Add("CI03.01.02", "　　其中：取得外部借款所收到的现金");
            ExternalCFItems.Add("CI03.01.03", "　　其中：收到外部其他与筹资活动有关的现金");
            ExternalCFItems.Add("CI03.02.01", "　　其中：偿还外部债务支付的现金");
            ExternalCFItems.Add("CI03.02.02", "　　其中：外部分配股利、利润或偿付利息支付的现金");
            ExternalCFItems.Add("CI03.02.03", "　　其中：支付外部其他与筹资活动有关的现金");

            var tmpTableName = GetKingdeeCashFlowRptDetailDataTable(filter);
            if (tmpTableName != "")
            {
                string sql = string.Format(@"/*dialect*/
                                                SELECT FNAME
                                                FROM   T_ORG_ORGANIZATIONS,
                                                       T_ORG_ORGANIZATIONS_L
                                                WHERE  T_ORG_ORGANIZATIONS_L.FLOCALEID = 2052
                                                       AND T_ORG_ORGANIZATIONS.FORGID = T_ORG_ORGANIZATIONS_L.FORGID
                                                       AND T_ORG_ORGANIZATIONS.FFORBIDSTATUS = 'A'
                                                       AND T_ORG_ORGANIZATIONS.FORGID <> 1");
                var dataOrgNames = DBUtils.ExecuteDynamicObject(this.Context, sql);//集团所有公司名称
                if (dataOrgNames.Count > 0)
                {
                    string filerSQL = "";
                    foreach (var dataOrgName in dataOrgNames)
                    {
                        filerSQL += string.Format(" AND FDETAILNAME NOT LIKE '%{0}%' ", dataOrgName["FNAME"].GetString());//核算维度名称不包含公司名称,判定为外部
                    }
                    //查询现金流项目的外部金额
                    DynamicObject customFilter = filter.FilterParameter.CustomFilter;
                    string CURRENCYID = customFilter["CURRENCYID"].GetString();
                    string amountKey = "FCashflowAmountFor"; //原币金额
                    if (CURRENCYID == "0")//综合本位币
                        amountKey = "FCashflowAmount"; //本位币金额
                    sql = string.Format(@"/*dialect*/
                                                SELECT FCFItemNo,
                                                       SUM({2}) AS FCashflowAmountFor
                                                FROM   {0}
                                                WHERE  FCFitemNo <> ''  {1}
                                                GROUP  BY FCFItemNo ", tmpTableName, filerSQL, amountKey);
                    var dataItemAmounts = DBUtils.ExecuteDynamicObject(this.Context, sql);
                    foreach (var ExternalCFItem in ExternalCFItems)
                    {
                        string itemNo = ExternalCFItem.Key;
                        string itemNoEx = itemNo + ".01";//外部项目编码
                        string itemNameEx = ExternalCFItem.Value;//外部项目名称
                        decimal FCashflowAmountFor = 0;
                        var tmp = dataItemAmounts.Where(p => p["FCFItemNo"].GetString() == itemNo);
                        if (tmp != null && tmp.Count() > 0)
                        {
                            FCashflowAmountFor = tmp.First()["FCashflowAmountFor"].GetDecimal();
                        }
                        //计算序号
                        double newSeq = 0;
                        sql = string.Format(@"/*dialect*/ select F_VTR_SEQ  from  {0} where FItemNo = '{1}'", tableName, itemNo); //查询原项目的序号
                        var dataSeq = DBUtils.ExecuteDynamicObject(this.Context, sql);
                        if (dataSeq != null && dataSeq.Count > 0)
                        {
                            double F_VTR_SEQ = Convert.ToDouble(dataSeq.First()["F_VTR_SEQ"]);
                            newSeq = F_VTR_SEQ + 0.1;
                        }
                        sql = string.Format("INSERT INTO {0} (FCashItem,FAmount,F_VTR_Seq,F_VTR_Precision,FItemID,FItemNo,F_VTR_SumReableBill,F_VTR_SumPayableBill) VALUES ('{1}',{2},{3},2,0,'{4}',0,0)", tableName, itemNameEx, FCashflowAmountFor, newSeq, itemNoEx);
                        DBUtils.Execute(this.Context, sql);
                    }

                    //重算FIDENTITYID
                    sql = string.Format(@"/*dialect*/   UPDATE a SET FIDENTITYID = b.newSeq
                                                        FROM   {0} a,
                                                               (SELECT F_VTR_SEQ,
                                                                       ROW_NUMBER()
                                                                         OVER(
                                                                           ORDER BY F_VTR_SEQ) newSeq
                                                                FROM   {0}) b
                                                        WHERE  a.F_VTR_SEQ = b.F_VTR_SEQ ", tableName);
                    DBUtils.Execute(this.Context, sql);

                }
            }
        }

        /// <summary>
        /// 获取票据流量表-单体报表数据
        /// </summary>
        public List<string> GetInvoiceFlowRptDataTable(IRptParams filter)
        {
            var newFilter = (IRptParams)Kingdee.BOS.Util.ObjectUtils.CreateCopy(filter);
            List<string> tmpTableNames = new List<string>();
            DynamicObject customFilter = newFilter.FilterParameter.CustomFilter;
            var F_VTR_ACCTBOOKIDS = customFilter["F_VTR_ACCTBOOKIDS"] as DynamicObjectCollection;
            if (F_VTR_ACCTBOOKIDS != null && F_VTR_ACCTBOOKIDS.Count > 0)
            {
                ISysReportService sysReportService = ServiceFactory.GetSysReportService(this.Context);
                var reportMetadata = FormMetaDataCache.GetCachedFormMetaData(this.Context, "VLEU_GL_Rpt_InvoiceFlow");//票据流量表-单体
                foreach (var F_VTR_ACCTBOOKID in F_VTR_ACCTBOOKIDS)//多个账簿
                {
                    customFilter["ACCTBOOKID_Id"] = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS_Id"];
                    customFilter["ACCTBOOKID"] = F_VTR_ACCTBOOKID["F_VTR_ACCTBOOKIDS"];
                    ReportServiceParameter reportServiceParam = new ReportServiceParameter();
                    reportServiceParam.RptFilterParams = newFilter;
                    reportServiceParam.Context = this.Context;
                    reportServiceParam.PageId = Guid.NewGuid().ToString();
                    reportServiceParam.BusinessInfo = reportMetadata.BusinessInfo;
                    var result = sysReportService.GetReportData(reportServiceParam); //调用服务、查询报表，简单账表 
                    if (result != null && result.DataSource != null)
                        tmpTableNames.Add(result.DataSource.TableName);
                }
            }
            return tmpTableNames;
        }

        /// <summary>
        /// 更新累计应收、应付票据金额
        /// </summary>
        /// <param name="tableName"></param>
        public void UpdateRePayableBillAmount(IRptParams filter, string tableName)
        {
            var tmpTables = GetInvoiceFlowRptDataTable(filter);
            List<string> selectSqls = new List<string>();
            foreach (var tmpTable in tmpTables)
            {
                string tmpSql = string.Format(@"SELECT *
                                                FROM   {0} ", tmpTable);
                selectSqls.Add(tmpSql);
            }
            string selectSql = string.Join(" UNION ALL ", selectSqls);
            string sql = string.Format(@"/*dialect*/ 
                                        UPDATE a
                                        SET    a.F_VTR_SumReableBill = ISNULL(t.F_VTR_SumReableBill, 0),
                                               a.F_VTR_SumPayableBill = ISNULL(t.F_VTR_SumPayableBill, 0)
                                        FROM   {0} a,
                                               (SELECT FItemNo,
                                                       SUM(F_VTR_SUM_YSINVOICE) AS F_VTR_SumReableBill,
                                                       SUM(F_VTR_SUM_YFINVOICE) AS F_VTR_SumPayableBill
                                                FROM   ({1}) t1
                                                GROUP  BY FItemNo) t
                                        WHERE  a.FItemNo = t.FItemNo
                                               AND a.FItemNo <> ''", tableName, selectSql);
            DBUtils.Execute(this.Context, sql);


        }

        /// <summary>
        /// 更新累计金额
        /// </summary>
        public void UpdateSumAmount(string tableName)
        {
            string sql = string.Format(@"/*dialect*/ 
                                        UPDATE {0} SET F_VTR_SumAmount = isnull(FAmount,0) + isnull(F_VTR_SumReableBill,0) + isnull(F_VTR_SumPayableBill,0)", tableName);
            DBUtils.Execute(this.Context, sql);
        }

    }
}