public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表
IDBService dbService = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
temps = dbService.CreateTemporaryTableName(this.Context, 1);
string temp = temps[0];
//调用基类方法，获取初步查询结果到临时表
base.BuilderReportSqlAndTempTable(filter, temp);
//对标准报表所查询的数据进行加工
string sql = string.Format(@"/*dialect*/
SELECT
T1.*
,T2.FOLDNUMBER --旧物料编码
,T2.FCUSTPRODUCTNO FCUSTMATNO --客户货号
,T2.FQYPACKINGQTY FPACKINGQTY --装箱量
,CASE
WHEN T2.FQYPACKINGQTY = 0 THEN 0
ELSE CEILING(T1.FSTOCKJCQTY / T2.FQYPACKINGQTY)
END FJCCARTONQTY --结存箱数 = 结存数量(库存) / 装箱量
INTO {0}
FROM {1} T1
LEFT JOIN T_BD_MATERIAL T2
ON T1.FMATERIALID = T2.FMATERIALID
", tableName, temp);
DBUtils.Execute(this.Context, sql);
}
protected override DataTable GetReportData(string tablename, Kingdee.BOS.Core.Report.IRptParams filter)
{
DataTable dtPageData = base.GetReportData(tablename, filter);
for (int i = 0; i < dtPageData.Rows.Count; i++)
{
//结存数量
decimal JCQty = Convert.ToDecimal(dtPageData.Rows[i]["FSTOCKJCQTY"]);
//装箱量
decimal packingQty = Convert.ToDecimal(dtPageData.Rows[i]["FPACKINGQTY"]);
//结存箱数
dtPageData.Rows[i]["FJCCARTONQTY"] = packingQty == 0M ? 0M : Math.Ceiling(JCQty / packingQty);
}
return(dtPageData);
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表，用于存放自己的数据
IDBService dbservice = ServiceHelper.GetService <IDBService>();
materialRptTableNames = dbservice.CreateTemporaryTableName(this.Context, 1);
string strTable = materialRptTableNames[0];
//调用基类的方法，获取初步查询的结果到临时表
base.BuilderReportSqlAndTempTable(filter, strTable);
//对初步查询的结果进行处理，然后写回基类默认的存储查询结果临时表
string strSql = string.Format(@"SELECT t1.*,
CASE
WHEN T1.FFORMID = 'STK_MisDelivery' THEN MIS.FPICKERID
WHEN T1.FFORMID = 'PRD_PickMtrl' THEN MTRL.FPICKERID
WHEN T1.FFORMID = 'PRD_FeedMtrl' THEN FEED.FPICKERID
ELSE NULL
END FPICKERID, --领料人
CASE
WHEN T1.FFORMID = 'PRD_ReturnMtrl' THEN RET.FRETURNERID
ELSE NULL
END FRETURNERID, ----退料人
CASE
WHEN T1.FFORMID = 'PRD_PickMtrl' THEN MTRL.F_KD_WLBM
WHEN T1.FFORMID = 'PRD_FeedMtrl' THEN FEED.F_KD_BASE
WHEN T1.FFORMID = 'PRD_ReturnMtrl' THEN RET.F_KD_WLBM
ELSE NULL
END F_Ls_Material, --主产品物料
CASE
WHEN T1.FFORMID = 'PRD_PickMtrl' THEN MTRL.F_kd_CPPJ
WHEN T1.FFORMID = 'PRD_FeedMtrl' THEN FEED.F_kd_Text
WHEN T1.FFORMID = 'PRD_ReturnMtrl' THEN RET.F_kd_CPPH
ELSE NULL
END F_LS_CPPH --主产品批号
into {0}
FROM {1} T1
LEFT JOIN T_STK_MISDELIVERY MIS
ON T1.FBILLID = MIS.FID
LEFT JOIN T_PRD_PICKMTRL MTRL
ON MTRL.FID = T1.FBILLID
LEFT JOIN T_PRD_FEEDMTRL FEED
ON T1.FBILLID = FEED.FID
LEFT JOIN T_PRD_RETURNMTRL RET
ON T1.FBILLID = RET.FID", tableName, strTable);
DBUtils.Execute(this.Context, strSql);
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表
IDBService dbService = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
temps = dbService.CreateTemporaryTableName(this.Context, 1);
string temp = temps[0];
//调用基类方法，获取初步查询结果到临时表
base.BuilderReportSqlAndTempTable(filter, temp);
//对标准报表所查询的数据进行加工
string sql = string.Format(@"/*dialect*/
SELECT
T1.*
,T2.FCUSTPRODUCTNO FCUSTMATNO --客户货号
INTO {0}
FROM {1} T1
LEFT JOIN T_BD_MATERIAL T2
ON T1.FMATERIALBASEID = T2.FMATERIALID
", tableName, temp);
DBUtils.Execute(this.Context, sql);
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
//ReportCommon.AddTableColumn(base.Context, tableName);
this.UpdateExpandFieldbiaodun(tableName);
}
