创建简单账表

using Kingdee.BOS.Core.Metadata;using Kingdee.BOS.Core.Report;using Kingdee.BOS.Core.SqlBuilder;using Kingdee.BOS.Orm.DataEntity;using Kingdee.BOS;using Kingdee.BOS.Util;using System;using System.Collections.Generic;using System.ComponentModel;using System.Linq;using System.Text;using System.Threading.Tasks;using Kingdee.BOS.Contracts.Report;using Kingdee.BOS.Contracts;using Kingdee.BOS.App.Data;namespace BeiHuiPlugln{[Description("销售出库单月报--简单账表")][HotUpdate]public class SalOutInStockMonthlyReportPlugln : SysReportBaseService{//初始化public override void Initialize(){base.Initialize();// 简单账表类型：普通、树形、分页this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;this.IsCreateTempTableByPlugin = true;//是否分组汇总this.ReportProperty.IsGroupSummary = true;}//获取过滤条件信息(构造单据信息)public override ReportTitles GetReportTitles(IRptParams filter){ReportTitles reportTitles = new ReportTitles();DynamicObject customFilter = filter.FilterParameter.CustomFilter;if (customFilter != null){string multiOrgnNameValues = this.GetMultiOrgnNameValues(customFilter["F_PAEZ_OrgId"].ToString());string startValue = (customFilter["FOrderStartDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FOrderStartDate"]).ToString("yyyy-MM-dd");string endValue = (customFilter["FOrderEndDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FOrderEndDate"]).ToString("yyyy-MM-dd");string BillNo = (Convert.ToString(customFilter["F_PoOrderBillNo"]) == "") ? string.Empty : customFilter["F_PoOrderBillNo"].ToString();string baseDataNameValue = this.GetBaseDataNameValue(customFilter["F_PAEZ_WLID"] as DynamicObjectCollection);string baseDataNameValue3 = this.GetBaseDataNameValue(customFilter["F_PAEZ_SuppId"] as DynamicObjectCollection);reportTitles.AddTitle("F_PAEZ_Org", multiOrgnNameValues);reportTitles.AddTitle("F_PAEZ_Date", startValue + "-" + endValue);reportTitles.AddTitle("F_PAEZ_BillNo", BillNo);reportTitles.AddTitle("F_PAEZ_WLName", baseDataNameValue);reportTitles.AddTitle("F_PAEZ_GYS", baseDataNameValue3);}return reportTitles;}//组织名称private string GetMultiOrgnNameValues(string orgIdStrings){List<string> list = new List<string>();string result = string.Empty;if (orgIdStrings.Trim().Length > 0){IQueryService service = Kingdee.BOS.Contracts.ServiceFactory.GetService<IQueryService>(base.Context);QueryBuilderParemeter para = new QueryBuilderParemeter{FormId = "ORG_Organizations",SelectItems = SelectorItemInfo.CreateItems("FNAME"),FilterClauseWihtKey = string.Format(" FORGID IN ({0}) AND FLOCALEID={1}", orgIdStrings, base.Context.UserLocale.LCID)};DynamicObjectCollection dynamicObjectCollection = service.GetDynamicObjectCollection(base.Context, para, null);foreach (DynamicObject current in dynamicObjectCollection){list.Add(current["FNAME"].ToString());}if (list.Count > 0){result = string.Join(",", list.ToArray());}}return result;}//基础资料名称private string GetBaseDataNameValue(DynamicObjectCollection dyobj){string name = "";foreach (DynamicObject dynbj in dyobj){if (dynbj != null || !dynbj.DynamicObjectType.Properties.Contains("Name")){DynamicObject dynbj2 = (DynamicObject)dynbj[2];name = name + ",'" + dynbj2["Name"].ToString() + "'";}}if (name.Length > 0){name = name.Substring(1, name.Length - 1);}return name;}//设置单据列public override ReportHeader GetReportHeaders(IRptParams filter){ReportHeader header = new ReportHeader();ReportHeader headerch = new ReportHeader();// 编号header.AddChild("FOrgname", new LocaleValue("标题一&业务组织", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);header.AddChild("FSuppName", new LocaleValue("标题一&客户", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);header.AddChild("FDocumentStatus", new LocaleValue("标题一&状态", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);header.AddChild("FDate", new LocaleValue("标题一&日期", this.Context.UserLocale.LCID), SqlStorageType.SqlDatetime);header.AddChild("FBillNo", new LocaleValue("标题一&单据编号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);header.AddChild("FMaterialName", new LocaleValue("标题二&物料", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);header.AddChild("FQty", new LocaleValue("标题二&数量", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);header.AddChild("FUnitName", new LocaleValue("标题二&单位", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);header.AddChild("FTAXPRICE", new LocaleValue("标题二&含税价", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);header.AddChild("FALLAMOUNT", new LocaleValue("标题二&价税合计", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);return header;}//创建临时报表public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName){string Filter = GetFilterWhere(filter);string seqFld = string.Format(base.KSQL_SEQ, OrderColumn(filter));// 取数SQL// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计 ,t1M_L.FNAME as FMaterialNamestring sql = string.Format(@"/*dialect*/ select FID, FENTRYID,FPURCHASEORGID,FOrgname,FSuppName,FBILLNO,FDate,FDOCUMENTSTATUS ,FLOCALCURRID,FMATERIALID,FMaterialName,FQTY,FUnitName,FTAXPRICE,FALLAMOUNT, {0} into {1} from ( select t0.FID, t1.FENTRYID,t0.FPURCHASEORGID,t1UO_L.FNAME FOrgname,SUPP_L.FNAME FSuppName ,t0.FBILLNO ,t0.FDate ,t0.FDOCUMENTSTATUS ,t2.FLOCALCURRID ,t1.FMATERIALID ,t1M_L.FNAME as FMaterialName ,t1.FQTY ,t1U_L.FNAME as FUnitName ,t1f.FTAXPRICE ,t1f.FALLAMOUNT from T_PUR_POORDER t0 inner join T_PUR_POORDERFIN t2 on (t0.FID = t2.FID) left join T_BD_CURRENCY t20 on (t2.FLOCALCURRID = t20.FCURRENCYID) inner join T_PUR_POORDERENTRY t1 on (t0.FID = t1.FID) left join T_BD_MATERIAL_L t1M_L on (t1.FMATERIALID = t1m_l.FMATERIALID and t1M_L.FLOCALEID = 2052) inner join T_PUR_POORDERENTRY_F t1F on (t1.FENTRYID = t1f.FENTRYID) left join T_BD_UNIT t1U on (t1f.FPRICEUNITID = t1u.FUNITID) left join T_BD_UNIT_L t1U_L on (t1U.FUNITID = t1U_L.FUNITID and t1U_L.FLOCALEID = 2052) left join T_ORG_ORGANIZATIONS_L t1UO_L on (t0.FPURCHASEORGID = t1UO_L.FORGID and t1UO_L.FLOCALEID = 2052) left join T_BD_SUPPLIER_L SUPP_L on (t0.FSUPPLIERID = SUPP_L.FSUPPLIERID and SUPP_L.FLOCALEID = 2052) ) t1 {2} ",seqFld,tableName, Filter);DBUtils.ExecuteDynamicObject(this.Context, sql);}获取过滤条件private string GetFilterWhere(IRptParams filter){DynamicObject customFilter = filter.FilterParameter.CustomFilter;StringBuilder strwhere = new StringBuilder();strwhere.AppendLine("Where 1=1 ");//组织string org = string.IsNullOrWhiteSpace(customFilter["F_PAEZ_OrgId"].ToString())? " " : string.Format(" AND FPURCHASEORGID IN ({0}) ", Convert.ToString(customFilter["F_PAEZ_OrgId"]));strwhere.AppendLine(org);//日期string startValue = (customFilter["FOrderStartDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FOrderStartDate"]).ToString("yyyy-MM-dd");string endValue = (customFilter["FOrderEndDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FOrderEndDate"]).ToString("yyyy-MM-dd");strwhere.AppendLine(string.Format(" AND FDate>='{0}' and FDate<='{1}' ", startValue, endValue));//物料string baseDataNameValue = this.GetBaseDataNameValue(customFilter["F_PAEZ_WLID"] as DynamicObjectCollection);string MaterailName = string.IsNullOrWhiteSpace(baseDataNameValue)? " " : string.Format(" AND FMaterialName IN ({0}) ", baseDataNameValue);strwhere.AppendLine(MaterailName);//单据编号string BillNo = (Convert.ToString(customFilter["F_PoOrderBillNo"]) == "") ? " " : " AND FBILLNO='" + customFilter["F_PoOrderBillNo"].ToString() + "' ";strwhere.AppendLine(BillNo);//供应商string baseSuppName = this.GetBaseDataNameValue(customFilter["F_PAEZ_SuppId"] as DynamicObjectCollection);string SuppName = string.IsNullOrWhiteSpace(baseSuppName)? " " : string.Format(" AND FSuppName IN ({0}) ", baseSuppName);strwhere.AppendLine(SuppName);string text2 = string.IsNullOrWhiteSpace(filter.FilterParameter.FilterString) ? " " : " AND " + filter.FilterParameter.FilterString.Replace("{ts", "").Replace("}", "");strwhere.AppendLine(text2);return strwhere.ToString();}/// 设置汇总列信息public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter){var result = base.GetSummaryColumnInfo(filter);result.Add(new SummaryField("FQty", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));result.Add(new SummaryField("FALLAMOUNT", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));return result;}///排序语句private string OrderColumn(IRptParams filter){string OrderBy = "";string datasort = Convert.ToString(filter.FilterParameter.SortString);//排序if (datasort != ""){OrderBy = " " + datasort + " ";}else{OrderBy = " FID ";}return OrderBy;}}}