using Kingdee.BOS;
|
using Kingdee.BOS.App.Data;
|
using Kingdee.BOS.Contracts;
|
using Kingdee.BOS.Contracts.Report;
|
using Kingdee.BOS.Core.Metadata;
|
using Kingdee.BOS.Core.Report;
|
using Kingdee.BOS.Core.SqlBuilder;
|
using Kingdee.BOS.Orm.DataEntity;
|
using Kingdee.BOS.ServiceHelper;
|
using Kingdee.BOS.Util;
|
using System;
|
using System.Collections.Generic;
|
using System.ComponentModel;
|
using System.Data;
|
using System.Text;
|
|
namespace Demo.Report.PRD
|
{
|
[Description("提料计划单简单账表")]
|
[HotUpdate]
|
/// <summary>
|
/// 提料计划单简单账表
|
/// </summary>
|
public class prd_ExtractionPlanDetail : SysReportBaseService
|
{
|
string date = "";
|
string TableName = "";
|
//初始化
|
public override void Initialize()
|
{
|
base.Initialize();
|
// 简单账表类型：普通、树形、分页
|
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
|
this.IsCreateTempTableByPlugin = true;
|
//取代码中配置的列
|
this.ReportProperty.IsUIDesignerColumns = false;
|
//是否分组汇总
|
this.ReportProperty.IsGroupSummary = true;
|
}
|
//组织名称
|
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;
|
}
|
|
/*
|
//获取过滤条件信息(构造单据信息)
|
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;
|
}
|
|
*/
|
//基础资料名称
|
|
//设置单据列
|
public override ReportHeader GetReportHeaders(IRptParams filter)
|
{
|
|
//更新总拖期数量 和 总提料数量
|
List<string> sqlList = new List<string>();
|
string sql = string.Format(@"/*dialect*/ update a set a.TQSum = b.FHQTY from {0} a
|
join (
|
select SUM(b.FHQTY)FHQTY, FIDENTITYID from {0} c
|
join Cg_PODemandPlanBillMain a on c.FSUPPLIERID = a.FSUPPLIERID and c.FHPURCHASEORGID = a.FHPURCHASEORGID
|
join(select SUM(FHQTY)FHQTY, FHMATERID, FID from Cg_PODemandPlanBillSub GROUP BY FHMATERID, FID) b on a.FID = B.FID and c.FHMATERID = b.FHMATERID
|
where {1} and datediff(d, FHDATE, getdate())> 0
|
group by FIDENTITYID
|
) b on a.FIDENTITYID = b.FIDENTITYID",
|
TableName, date);
|
//DBServiceHelper.Execute(Context, sql);
|
sqlList.Add(sql);
|
sql = string.Format(@"/*dialect*/ update a set a.PlanSum = b.FHQTY from {0} a
|
join (
|
select SUM(b.FHQTY)FHQTY,FIDENTITYID from {0} c
|
join Cg_PODemandPlanBillMain a on c.FSUPPLIERID = a.FSUPPLIERID and c.FHPURCHASEORGID = a.FHPURCHASEORGID
|
join (select SUM(FHQTY)FHQTY, FHMATERID, FID from Cg_PODemandPlanBillSub GROUP BY FHMATERID,FID) b on a.FID = B.FID and c.FHMATERID = b.FHMATERID
|
where {1} and datediff(d,FHDATE,getdate())<=0
|
group by FIDENTITYID
|
) b on a.FIDENTITYID=b.FIDENTITYID",
|
TableName, date);
|
//DBServiceHelper.Execute(Context, sql);
|
sqlList.Add(sql);
|
DBServiceHelper.Execute(Context,string.Join(";", sqlList));
|
|
ReportHeader header = new ReportHeader();
|
header.AddChild("FRogNUMBER", new LocaleValue("供应商信息&采购组织", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FNAME", new LocaleValue("供应商信息&供应商", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
|
header.AddChild("FNUMBER", new LocaleValue("物料信息&物料", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FMATERIALNAME", new LocaleValue("物料信息&物料名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FSPECIFICATION", new LocaleValue("物料信息&物料规格", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FBASEUNIT", new LocaleValue("物料信息&单位", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FPROUNIT", new LocaleValue("物料信息&计量单位", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FQTY", new LocaleValue("物料信息&库存数量", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FTRANSITQTY", new LocaleValue("物料信息&在途数量", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
|
header.AddChild("TQSum", new LocaleValue("提料拖期明细&提料拖期数量", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
DataSet ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,23) FHDATE from Cg_PODemandPlanBillMain where " + date + " and datediff(d,FHDATE,getdate())>0 ");
|
DataTable dt = ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
header.AddChild(dr[0].ToString(), new LocaleValue("提料拖期明细&" + dr[0].ToString() + "", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
|
}
|
}
|
header.AddChild("PlanSum", new LocaleValue("计划明细&合计", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
DataSet _ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,23) FHDATE from Cg_PODemandPlanBillMain where " + date + " and datediff(d,FHDATE,getdate())<=0");
|
dt = _ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
header.AddChild(dr[0].ToString(), new LocaleValue("计划明细&" + dr[0].ToString() + "", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
|
}
|
}
|
return header;
|
}
|
//创建临时报表
|
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
|
{
|
TableName = tableName;
|
string Filter = GetFilterWhere(filter);
|
string seqFld = string.Format(base.KSQL_SEQ, OrderColumn(filter));
|
|
// 取数SQL
|
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计 ,t1M_L.FNAME as FMaterialName
|
string sql = string.Format(@"/*dialect*/exec 提料计划报表 '{0}','{1}'", tableName, Filter.Trim());
|
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 startValue = (customFilter["FBeginConveyDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FBeginConveyDate"]).ToString("yyyy/MM/d");
|
string endValue = (customFilter["FEndConveyDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FEndConveyDate"]).ToString("yyyy/MM/d");
|
strwhere.AppendLine(string.Format(" AND FHDate>=''{0}'' and FHDate<=''{1}'' ", startValue, endValue));
|
date = string.Format(" FHDate>='{0}' and FHDate<='{1}' ", startValue, endValue);
|
//物料
|
var baseDataNameValue = customFilter["FMATERIALID"];
|
string MaterailName = baseDataNameValue == null
|
? " " : string.Format(" AND B.FHMATERID IN ({0}) ", (customFilter["FMATERIALID"] as DynamicObject)["Id"].ToString());
|
strwhere.AppendLine(MaterailName);
|
//组织
|
var org = customFilter["FOrgId"] == null
|
? " " : string.Format(" AND FHPURCHASEORGID IN ({0}) ", (customFilter["FOrgId"] as DynamicObject)["Id"].ToString());
|
strwhere.AppendLine(org);
|
//供应商
|
var baseSuppName = customFilter["FSUPPLIERID"];
|
string SuppName = baseSuppName == null
|
? " " : string.Format(" AND a.FSUPPLIERID IN ({0}) ", (customFilter["FSUPPLIERID"] as DynamicObject)["Id"].ToString());
|
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(dr[0].ToString(), 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 = " a.FID ";
|
}
|
return OrderBy;
|
}
|
}
|
}
|
