using Demo.BillView.PRD;
|
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("提料计划单简单账表(采购订单维度new)")]
|
[HotUpdate]
|
/// <summary>
|
/// 提料计划单简单账表
|
/// </summary>
|
public class prd_ExtractionPlanDetailPurOrderNew : SysReportBaseService
|
{
|
string TableName = "";
|
string FOrgId = "";
|
string FBeginDate = "";
|
string FEndDate = "";
|
string FMATERIALID = "";
|
string FSUPPLIERID = "";
|
string FXQD = "";
|
string FPURORDERNO = "";
|
//初始化
|
public override void Initialize()
|
{
|
base.Initialize();
|
// 简单账表类型：普通、树形、分页
|
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
|
this.IsCreateTempTableByPlugin = true;
|
//取代码中配置的列
|
this.ReportProperty.IsUIDesignerColumns = false;
|
//是否分组汇总
|
this.ReportProperty.IsGroupSummary = true;
|
}
|
//设置单据列
|
public override ReportHeader GetReportHeaders(IRptParams filter)
|
{
|
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("FSUMQTY", new LocaleValue("合计", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
//采购订单号、需求单号（销售订单号），采购订单表头摘要，采购订单表体备注，采购员
|
header.AddChild("FHPOOrderBillNo", new LocaleValue("采购订单号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FSalOrderNo", new LocaleValue("需求单号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("F_QIMB_NOTE", new LocaleValue("摘要", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FNOTE", new LocaleValue("备注", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
header.AddChild("FCgddCreaterNmae", 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("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);
|
string sql = string.Format(@"
|
/*DIALECT*/
|
SELECT DISTINCT A.FHDATE FROM CG_PODEMANDPLANBILLMAIN A
|
JOIN CG_PODEMANDPLANBILLSUB B ON A.FID = B.FID
|
WHERE (A.FHPURCHASEORGID = {0} OR {0} = 0)
|
AND (A.FSUPPLIERID = {1} OR {1} = 0)
|
AND (B.FHMATERID = {2} OR {2} = 0)
|
AND DATEDIFF(D,'{3}',FHDATE)>= 0
|
AND DATEDIFF(D,'{4}',FHDATE)<= 0
|
AND (B.FSALORDERNO = '{5}' OR '{5}' = '')
|
AND (B.FHPOORDERBILLNO = '{6}' OR '{6}' = '')
|
AND DATEDIFF(D,FHDATE,GETDATE())>0
|
", FOrgId, FSUPPLIERID, FMATERIALID, FBeginDate, FEndDate, FXQD, FPURORDERNO);
|
//LogService.Write(sql);
|
DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql);
|
DataTable dt = ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
header.AddChild(Convert.ToDateTime(dr[0]).ToString("yyyy-MM-dd"), new LocaleValue(Convert.ToDateTime(dr[0]).ToString("yyyy-MM-dd") + "", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
|
}
|
}
|
header.AddChild("PlanSum", new LocaleValue("提料计划合计", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
sql = string.Format(@"
|
/*DIALECT*/
|
SELECT DISTINCT A.FHDATE FROM CG_PODEMANDPLANBILLMAIN A
|
JOIN CG_PODEMANDPLANBILLSUB B ON A.FID = B.FID
|
WHERE (A.FHPURCHASEORGID = {0} OR {0} = 0)
|
AND (A.FSUPPLIERID = {1} OR {1} = 0)
|
AND (B.FHMATERID = {2} OR {2} = 0)
|
AND DATEDIFF(D,'{3}',FHDATE)>= 0
|
AND DATEDIFF(D,'{4}',FHDATE)<= 0
|
AND (B.FSALORDERNO = '{5}' OR '{5}' = '')
|
AND (B.FHPOORDERBILLNO = '{6}' OR '{6}' = '')
|
AND DATEDIFF(D,FHDATE,GETDATE())<=0
|
", FOrgId, FSUPPLIERID, FMATERIALID, FBeginDate, FEndDate, FXQD, FPURORDERNO);
|
DataSet _ds = DBServiceHelper.ExecuteDataSet(Context, sql);
|
dt = _ds.Tables[0];
|
if (dt.Rows.Count > 0)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
header.AddChild(Convert.ToDateTime(dr[0]).ToString("yyyy-MM-dd"), new LocaleValue(Convert.ToDateTime(dr[0]).ToString("yyyy-MM-dd"), this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
|
}
|
}
|
return header;
|
}
|
//创建临时报表
|
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
|
{
|
base.BuilderReportSqlAndTempTable(filter, tableName);
|
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
|
FOrgId = Convert.ToString(customFilter["FOrgId_Id"]);
|
FBeginDate = Convert.ToString(customFilter["FBeginConveyDate"]);
|
FEndDate = Convert.ToString(customFilter["FEndConveyDate"]);
|
FMATERIALID = Convert.ToString(customFilter["FMATERIALID_Id"]);
|
FSUPPLIERID = Convert.ToString(customFilter["FSUPPLIERID_Id"]);
|
FXQD = Convert.ToString(customFilter["FXQD"]);
|
FPURORDERNO = Convert.ToString(customFilter["FPURORDERNO"]);
|
TableName = tableName;
|
// 取数SQL
|
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计 ,t1M_L.FNAME as FMaterialName
|
string sql = string.Format(@"/*dialect*/exec 提料计划报表CS '{0}','{1}', '{2}','{3}', '{4}','{5}','{6}','{7}'", tableName, FOrgId, FBeginDate, FEndDate, FMATERIALID, FSUPPLIERID, FXQD, FPURORDERNO);
|
LogService.Write("计划报表sql：" + sql);
|
DBServiceHelper.Execute(this.Context, sql);
|
|
}
|
/// 设置汇总列信息
|
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;
|
}
|
}
|
}
|
