public override Kingdee.BOS.Core.Report.ReportHeader GetReportHeaders(Kingdee.BOS.Core.Report.IRptParams filter)
{
ReportHeader header = base.GetReportHeaders(filter);
header.AddChild("FAuditDate", new LocaleValue("审核日期"), SqlStorageType.SqlSmalldatetime, true);
return(header);
}
//public override void Initialize()
//{
// base.Initialize();
// DecimalControlField field = new DecimalControlField
// {
// ByDecimalControlFieldName = "FJNUnitEnzyme",
// DecimalControlFieldName = "FSECPRECISION"
// };
// base.ReportProperty.DecimalControlFieldList.Add(field);
// DecimalControlField field2 = new DecimalControlField
// {
// ByDecimalControlFieldName = "FJNTonProperty",
// DecimalControlFieldName = "FSECPRECISION"
// };
// base.ReportProperty.DecimalControlFieldList.Add(field2);
//}
public override Kingdee.BOS.Core.Report.ReportHeader GetReportHeaders(Kingdee.BOS.Core.Report.IRptParams filter)
{
ReportHeader reportHeader = base.GetReportHeaders(filter);
reportHeader.AddChild("FJNUnitEnzyme", new LocaleValue("单位酶活量", base.Context.UserLocale.LCID));
reportHeader.AddChild("FJNTonProperty", new LocaleValue("标吨", base.Context.UserLocale.LCID));
return(reportHeader);
}
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);
//对标准报表所查询的数据进行加工
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("/*dialect*/ ");
sqlBuilder.AppendLine(" SELECT ");
sqlBuilder.AppendLine(" T1.* ");
sqlBuilder.AppendLine(" ,CASE ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_Payable' THEN T2.FAPPROVEDATE ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' THEN T3.FAPPROVEDATE ");
sqlBuilder.AppendLine(" ELSE NULL ");
sqlBuilder.AppendLine(" END FAUDITDATE --审核日期 ");
sqlBuilder.AppendLine(" INTO " + tableName + " ");
sqlBuilder.AppendLine(" FROM " + temp + " T1 ");
sqlBuilder.AppendLine(" --应付单 ");
sqlBuilder.AppendLine(" LEFT JOIN T_AP_PAYABLE T2 ");
sqlBuilder.AppendLine(" ON T2.FID = T1.FID ");
sqlBuilder.AppendLine(" --其他应付单 ");
sqlBuilder.AppendLine(" LEFT JOIN T_AP_OTHERPAYABLE T3 ");
sqlBuilder.AppendLine(" ON T3.FID = T1.FID ");
sqlBuilder.AppendLine(" WHERE 1 = 1 ");
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (filter.FilterParameter.CustomFilter != null)
{
//起始日期
beginAuditDate = Convert.ToDateTime(dyFilter["FBeginAuditDate_Filter"]);
//截止日期
endAuditDate = Convert.ToDateTime(dyFilter["FEndAuditDate_Filter"]);
if (beginAuditDate != DateTime.MinValue && endAuditDate != DateTime.MinValue)
{
sqlBuilder.AppendLine(" AND CASE WHEN T1.FFORMID = 'AP_Payable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T2.FAPPROVEDATE) >= 0 AND DATEDIFF(DAY, T2.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T3.FAPPROVEDATE) >= 0 AND DATEDIFF(DAY, T3.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ELSE 0 END = 1 ");
}
if (beginAuditDate != DateTime.MinValue && endAuditDate == DateTime.MinValue)
{
sqlBuilder.AppendLine(" AND CASE WHEN T1.FFORMID = 'AP_Payable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T2.FAPPROVEDATE) >= 0 THEN 1 ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T3.FAPPROVEDATE) >= 0 THEN 1 ELSE 0 END = 1 ");
}
if (beginAuditDate == DateTime.MinValue && endAuditDate != DateTime.MinValue)
{
sqlBuilder.AppendLine(" AND CASE WHEN T1.FFORMID = 'AP_Payable' AND DATEDIFF(DAY, T2.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' AND DATEDIFF(DAY, T3.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ELSE 0 END = 1 ");
}
}
DBUtils.Execute(this.Context, sqlBuilder.ToString());
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//把之前账表的数据放入新建的临时表
string strTempTable = AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
base.BuilderReportSqlAndTempTable(filter, strTempTable);
string strSql = string.Format(@"/*dialect*/select t1.*,case when ISNULL(t1.FBASEQTY,0)<>0 and t2.FIsMeasure='1' then CONVERT(varchar(50),CONVERT(float,CAST(t1.FSECQTY/t1.FBASEQTY as decimal(18,4)))) else '' end FJNUnitEnzyme,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then CONVERT(varchar(50),CONVERT(float,CAST(t1.FSECQTY/(t2.FJNTonProperty*1000) as decimal(18,4)))) else '' end FJNTonProperty
into {0} from {1} as t1
join T_BD_MATERIAL t2 on t1.FMaterialId=t2.FMaterialId", tableName, strTempTable);
DBUtils.Execute(this.Context, strSql);
AppServiceContext.DBService.DeleteTemporaryTableName(this.Context, new string[] { strTempTable });
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//把之前账表的数据放入新建的临时表
string strTempTable = Kingdee.K3.FIN.App.Core.AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
base.BuilderReportSqlAndTempTable(filter, strTempTable);
string strSql = string.Format(@"/*dialect*/select t1.*,
case when (ISNULL(t1.FBaseJCQty,0)<>0 or ISNULL(t1.FBaseOutQty,0)<>0 or ISNULL(t1.FBaseInQty,0)<>0) and t2.FIsMeasure='1' then
(case when ISNULL(t1.FBaseJCQty,0)<>0 then t1.FSecJCQty/t1.FBaseJCQty else
(case when ISNULL(t1.FBaseOutQty,0)<>0 then t1.FSecOutQty/t1.FBaseOutQty else
(case when ISNULL(t1.FBaseInQty,0)<>0 then t1.FSecInQty/t1.FBaseInQty end ) end) end )
else 0 end FJNUnitEnzyme,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECQCQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_QC,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECINQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_SR,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECOUTQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_FC,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECJCQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_JC
into {0} from {1} as t1
join T_BD_MATERIAL t2 on t1.FMaterialId=t2.FMaterialId", tableName, strTempTable);
DBUtils.Execute(this.Context, strSql);
Kingdee.K3.FIN.App.Core.AppServiceContext.DBService.DeleteTemporaryTableName(this.Context, new string[] { strTempTable });
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//把之前账表的数据放入新建的临时表
string strTempTable = AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
/* List<BaseDataTempTable> baseDataTempTable = filter.BaseDataTempTable;
*
* if ((base.CacheDataList != null) && (base.CacheDataList.Count != 0))
* {
* base.ReBuildCurPageTable();
* string strAnd = string.Empty;
* base.SetFilter(filter);
* DataRow row = base.CacheDataList[filter.CurrentPosition];
* if (base.isSplitPageByOwner)
* {
* base.pagerParm = new StockRptPager(Convert.ToInt64(row["FSTOCKORGID"]), row["FOWNERTYPEID"].ToString(), Convert.ToInt64(row["FOWNERID"]));
* }
* else
* {
* base.pagerParm = new StockRptPager(Convert.ToInt64(row["FSTOCKORGID"]));
* }
* string invGroupField = this.GetInvGroupField(filter, ref strAnd);
* List<SqlObject> lstSql = new List<SqlObject>();
* List<SqlObject> updateIOPriceSql = this.GetUpdateIOPriceSql(filter);
* if ((updateIOPriceSql != null) && (updateIOPriceSql.Count > 0))
* {
* lstSql.AddRange(updateIOPriceSql);
* }
* this.GetInsertDataSql(lstSql, invGroupField, baseDataTempTable);
* this.GetUpdatePricePrecSql(lstSql);
* this.GetUpdateExtDataDataSql(lstSql);
* base.SetMoreFilterFormat();
* foreach (SqlObject obj2 in lstSql)
* {
* DBUtils.Execute(base.Context, obj2.Sql, obj2.Param);
* }
* lstSql.Clear();
* base.GetUpdateStkQtySql(lstSql);
* updateIOPriceSql = this.GetUpdateQcPriceSql(filter);
* if ((updateIOPriceSql != null) && (updateIOPriceSql.Count > 0))
* {
* lstSql.AddRange(updateIOPriceSql);
* }
* this.GetSumRptUpdteQcAmount(lstSql);
* this.GetUpdateIoBaseAmountSql(lstSql);
* base.GetUpdateSql(lstSql);
* this.GetJcQtyPriceAmountSql(lstSql);
* this.GetDeleteSql(lstSql);
* base.GetPreFormatSql(lstSql);
* foreach (SqlObject obj3 in lstSql)
* {
* DBUtils.Execute(base.Context, obj3.Sql, obj3.Param);
* }
* this.SetRptDate(tableName);
*
* }*/
this.baseBuilderReportSqlAndTempTable(filter, strTempTable);
string strSql = string.Format(@"/*dialect*/select t1.*,
case when (ISNULL(t1.FBaseJCQty,0)<>0 or ISNULL(t1.FBaseOutQty,0)<>0 or ISNULL(t1.FBaseInQty,0)<>0) and t2.FIsMeasure='1' then
(case when ISNULL(t1.FBaseJCQty,0)<>0 then t1.FSecJCQty/t1.FBaseJCQty else
(case when ISNULL(t1.FSecOutQty,0)<>0 then t1.FSecOutQty/t1.FBaseOutQty else
(case when ISNULL(t1.FBaseInQty,0)<>0 then t1.FSecInQty/t1.FBaseInQty end ) end) end )
else 0 end FJNUnitEnzyme,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECQCQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_QC,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECINQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_SR,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECOUTQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_FC,
case when ISNULL(t2.FJNTonProperty,0)<>0 and t2.FIsMeasure='1' then t1.FSECJCQTY/(t2.FJNTonProperty*1000) else 0 end FJNTonProperty_JC
into {0} from {1} as t1
join T_BD_MATERIAL t2 on t1.FMaterialId=t2.FMaterialId", tableName, strTempTable);
DBUtils.Execute(this.Context, strSql);
this.dropTemplateTable(this.Context, strTempTable);
//AppServiceContext.DBService.DeleteTemporaryTableName(this.Context, new string[] { strTempTable });
}
