private void InitSettingInfo()
{
SettingInfo = new PivotReportSettingInfo();
SettingField supplier = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FSupplier",
FieldName = "FSupplier",
Name = new LocaleValue("供应商")
}, 0);
SettingInfo.ColTitleFields.Add(supplier);
SettingInfo.SelectedFields.Add(supplier);
SettingField department = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FDepartment",
FieldName = "FDepartment",
Name = new LocaleValue("部门")
}, 0);
SettingInfo.RowTitleFields.Add(department);
SettingInfo.SelectedFields.Add(department);
SettingField material = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FMaterial",
FieldName = "FMaterial",
Name = new LocaleValue("物料")
}, 1);
SettingInfo.RowTitleFields.Add(material);
SettingInfo.SelectedFields.Add(material);
SettingField qty = PivotReportSettingInfo.CreateColumnSettingField(new DecimalField()
{
Key = "FQty",
FieldName = "FQty",
Name = new LocaleValue("数量"),
}, 0);
qty.SumType = 1;
SettingInfo.AggregateFields.Add(qty);
SettingInfo.SelectedFields.Add(qty);
SettingField amount = PivotReportSettingInfo.CreateColumnSettingField(new DecimalField()
{
Key = "FAmount",
FieldName = "FAmount",
Name = new LocaleValue("金额")
}, 1);
amount.SumType = 1;
SettingInfo.AggregateFields.Add(amount);
SettingInfo.SelectedFields.Add(amount);
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
List <string> lstNumber = new List <string>();
DynamicObjectCollection dyCollection = customFilter["F_FC_BaseMat"] as DynamicObjectCollection;
foreach (DynamicObject dobj in dyCollection)
{
//long matId = Convert.ToInt64(dobj[0]);
DynamicObject d = dobj["F_FC_BaseMat"] as DynamicObject;
lstNumber.Add(d["Number"].ToString());
}
string whereNumber = string.Join("','", lstNumber);
string[] Filter = GetFilterWherePeriod(filter);
base.SettingInfo = new PivotReportSettingInfo();
//string sql = string.Format(@"/*dialect*/ select ROW_NUMBER() OVER(ORDER BY a.物料编号,仓库) FIDENTITYID , a.*,b.可用库存总量,b.总库存量 into {0} from V_invstock_all a left join V_invstock_total b on a.物料编号=b.物料编号", tableName);
string sql = string.Format(@"/*dialect*/select FIDENTITYID ,t0.物料编号,t0.物料名称,仓库,合格库存,待检库存,订单占用,可用库存总量,总库存量,cast(isnull(t2.销售数量,0) as int) 销售总量,
case 工作天数 when 0 then 0 else cast(isnull(t2.销售数量,0) as int)/工作天数 end 日均销量,
case (cast(isnull(t2.销售数量,0) as int)/工作天数) when 0 then 0 else cast(可用库存总量/(cast(isnull(t2.销售数量,0) as int)/工作天数) as numeric(18,1)) end 库存可用天数,最初销售日期 into {0}
from
(
select ROW_NUMBER() OVER(ORDER BY a.物料编号,仓库) FIDENTITYID ,a.*,b.可用库存总量,b.总库存量,'A' ts
--,c.daycount
from V_invstock_all1 a left join V_invstock_total1 b on a.物料编号=b.物料编号
) t0
left join (
select top 100000000 FNUMBER,dbo.GetWorkDays(min(FDATE),GETDATE()) 工作天数,convert(varchar(10),min(FDATE),120) 最初销售日期 from
(
select b.FBILLNO,c.FNUMBER,b.FDOCUMENTSTATUS,b.FDATE
from T_SAL_OUTSTOCKENTRY a
left join T_SAL_OUTSTOCK b on a.FID=b.FID
left join T_BD_MATERIAL c on a.FMATERIALID=c.FMATERIALID
left join T_BD_CUSTOMER_L e on b.FCUSTOMERID=e.FCUSTID and e.FLOCALEID=2052
where b.FDOCUMENTSTATUS='C' and e.FNAME not in ('舒美个人护理用品(深圳)有限公司','舒芙雅生物科技有限公司','舒蕾个人护理用品有限公司','舒颜日化(武汉)有限公司')
) e group by FNUMBER order by FNUMBER
) t1
on t0.物料编号=t1.FNUMBER
left join
(
select
j.fnumber 物料编号,
e.fname 物料名称,
sum(a.FRealQty) 销售数量
from T_SAL_OUTSTOCKENTRY a
inner join T_SAL_OUTSTOCK b on a.fid=b.fid
--sum(a.FPriceQty) 销售数量
--from t_AR_receivableEntry a
--left join t_AR_receivable b on a.fid=b.fid
left join V_customer c on b.FCUSTOMERID=c.fcustid
left join T_BD_CUSTOMER d on c.FCUSTID=d.FCUSTID
LEFT JOIN T_BD_MATERIAL_L e on a.FMATERIALID=e.FMATERIALID
LEFT JOIN T_BD_MATERIAL j on a.FMATERIALID=j.FMATERIALID
where
b.FDOCUMENTSTATUS='C' and c.fname not in ('舒美个人护理用品(深圳)有限公司','舒芙雅生物科技有限公司','舒蕾个人护理用品有限公司','舒颜日化(武汉)有限公司')
group by j.fnumber,e.fname
) t2 on t0.物料编号=t2.物料编号 where t0.物料编号 in ('{1}')" , tableName, whereNumber);
DBUtils.ExecuteDynamicObject(this.Context, sql);
//构造透视表列
SettingField field0 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "物料编号",
FieldName = "物料编号",
Name = new LocaleValue("物料编号")
}, 0);
field0.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field0);
base.SettingInfo.SelectedFields.Add(field0);
SettingField field1 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "物料名称",
FieldName = "物料名称",
Name = new LocaleValue("物料名称")
}, 1);
field1.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field1);
base.SettingInfo.SelectedFields.Add(field1);
SettingField field20 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "最初销售日期",
FieldName = "最初销售日期",
Name = new LocaleValue("最初销售日期")
}, 2);
field20.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field20);
base.SettingInfo.SelectedFields.Add(field20);
SettingField field21 = PivotReportSettingInfo.CreateColumnSettingField(new DecimalField()
{
Key = "销售总量",
FieldName = "销售总量",
Name = new LocaleValue("销售总量")
}, 2);
field21.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field21);
base.SettingInfo.SelectedFields.Add(field21);
SettingField field22 = PivotReportSettingInfo.CreateColumnSettingField(new DecimalField()
{
Key = "日均销量",
FieldName = "日均销量",
Name = new LocaleValue("日均销量")
}, 3);
field22.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field22);
base.SettingInfo.SelectedFields.Add(field22);
SettingField field23 = PivotReportSettingInfo.CreateColumnSettingField(new DecimalField()
{
Key = "库存可用天数",
FieldName = "库存可用天数",
Name = new LocaleValue("库存可用天数")
}, 4);
field23.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field23);
base.SettingInfo.SelectedFields.Add(field23);
SettingField field3 = PivotReportSettingInfo.CreateColumnSettingField(new IntegerField()
{
Key = "可用库存总量",
FieldName = "可用库存总量",
Name = new LocaleValue("可用库存总量(在库+待检-订单占用)")
}, 20);
field3.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field3);
base.SettingInfo.SelectedFields.Add(field3);
SettingField field2 = PivotReportSettingInfo.CreateColumnSettingField(new IntegerField()
{
Key = "总库存量",
FieldName = "总库存量",
Name = new LocaleValue("总库存量(在库+待检)")
}, 30);
field2.IsShowTotal = false;
base.SettingInfo.RowTitleFields.Add(field2);
base.SettingInfo.SelectedFields.Add(field2);
SettingField field4 = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "合格库存",
FieldName = "合格库存",
Name = new LocaleValue("合格库存")
}, 0, GroupSumType.Sum, "N0");
//field4.SumType = 1;
// field5.IsShowTotal = false;
base.SettingInfo.AggregateFields.Add(field4);
base.SettingInfo.SelectedFields.Add(field4);
SettingField field5 = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "待检库存",
FieldName = "待检库存",
Name = new LocaleValue("待检库存")
}, 1, GroupSumType.Sum, "N0");
field5.SumType = 1;
// field5.IsShowTotal = false;
base.SettingInfo.AggregateFields.Add(field5);
base.SettingInfo.SelectedFields.Add(field5);
SettingField field6 = PivotReportSettingInfo.CreateDataSettingField(new IntegerField()
{
Key = "订单占用",
FieldName = "订单占用",
Name = new LocaleValue("订单占用")
}, 2, GroupSumType.Sum, "N0");
field6.SumType = 1;
// field5.IsShowTotal = false;
base.SettingInfo.AggregateFields.Add(field6);
base.SettingInfo.SelectedFields.Add(field6);
SettingField field7 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "仓库",
FieldName = "仓库",
Name = new LocaleValue("仓库")
}, 0);
base.SettingInfo.ColTitleFields.Add(field7);
base.SettingInfo.SelectedFields.Add(field7);
//
}
private void InitSettingInfo()
{
SettingInfo = new PivotReportSettingInfo();
SettingInfo.IsShowGrandTotal = true;
SettingField AreaFNAME = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FAreaNAME",
FieldName = "FAreaNAME",
Name = new LocaleValue("大区")
}, 0);
SettingInfo.RowTitleFields.Add(AreaFNAME);
SettingInfo.SelectedFields.Add(AreaFNAME);
SettingField CityFNAME = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FCityNAME",
FieldName = "FCityNAME",
Name = new LocaleValue("市场")
}, 1);
CityFNAME.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(CityFNAME);
SettingInfo.SelectedFields.Add(CityFNAME);
SettingField FCUSTOMERNAME = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FCUSTOMERNAME",
FieldName = "FCUSTOMERNAME",
Name = new LocaleValue("客户")
}, 1);
FCUSTOMERNAME.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(FCUSTOMERNAME);
SettingInfo.SelectedFields.Add(FCUSTOMERNAME);
SettingField FMATERIALGROUP = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FMATERIALGROUP",
FieldName = "FMATERIALGROUP",
Name = new LocaleValue("产品类别")
}, 1);
FMATERIALGROUP.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(FMATERIALGROUP);
SettingInfo.SelectedFields.Add(FMATERIALGROUP);
SettingField FMATERIALNUMBER = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FMATERIALNUMBER",
FieldName = "FMATERIALNUMBER",
Name = new LocaleValue("物料编码")
}, 1);
FMATERIALNUMBER.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(FMATERIALNUMBER);
SettingInfo.SelectedFields.Add(FMATERIALNUMBER);
SettingField FMATERIALNAME = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FMATERIALNAME",
FieldName = "FMATERIALNAME",
Name = new LocaleValue("物料名称")
}, 1);
FMATERIALNAME.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(FMATERIALNAME);
SettingInfo.SelectedFields.Add(FMATERIALNAME);
SettingField FTAXPRICE = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FTAXPRICE",
FieldName = "FTAXPRICE",
Name = new LocaleValue("标准出厂价")
}, 1);
FTAXPRICE.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(FTAXPRICE);
SettingInfo.SelectedFields.Add(FTAXPRICE);
SettingField YMDATE = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FYMDATE",
FieldName = "FYMDATE",
Name = new LocaleValue("年月")
}, 1);
YMDATE.IsShowTotal = false;
SettingInfo.ColTitleFields.Add(YMDATE);
SettingInfo.SelectedFields.Add(YMDATE);
SettingField FQTY = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "FQTY",
FieldName = "FQTY",
Name = new LocaleValue("要货数量")
}, 0, GroupSumType.Sum, "N10");
SettingInfo.AggregateFields.Add(FQTY);
SettingInfo.SelectedFields.Add(FQTY);
SettingField FAMOUNT = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "FAMOUNT",
FieldName = "FAMOUNT",
Name = new LocaleValue("要货数值")
}, 0, GroupSumType.Sum, "N10");
SettingInfo.AggregateFields.Add(FAMOUNT);
SettingInfo.SelectedFields.Add(FAMOUNT);
SettingField FREALQTY = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "FREALQTY",
FieldName = "FREALQTY",
Name = new LocaleValue("出货数量")
}, 0, GroupSumType.Sum, "N10");
SettingInfo.AggregateFields.Add(FREALQTY);
SettingInfo.SelectedFields.Add(FREALQTY);
SettingField FREALAMOUNT = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "FREALAMOUNT",
FieldName = "FREALAMOUNT",
Name = new LocaleValue("出货数值")
}, 0, GroupSumType.Sum, "N10");
SettingInfo.AggregateFields.Add(FREALAMOUNT);
SettingInfo.SelectedFields.Add(FREALAMOUNT);
SettingField FDeviation = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "FDeviation",
FieldName = "FDeviation",
Name = new LocaleValue("偏差")
}, 0, GroupSumType.Sum, "N10");
SettingInfo.AggregateFields.Add(FDeviation);
SettingInfo.SelectedFields.Add(FDeviation);
SettingField FDeviationrate = PivotReportSettingInfo.CreateDataSettingField(new DecimalField()
{
Key = "FDeviationrate",
FieldName = "FDeviationrate",
Name = new LocaleValue("偏差率")
}, 0, GroupSumType.Sum, "N10");
FDeviationrate.IsShowTotal = false;
SettingInfo.AggregateFields.Add(FDeviationrate);
SettingInfo.SelectedFields.Add(FDeviationrate);
}
private void InitSettingInfo()
{
SettingInfo = new PivotReportSettingInfo();
SettingField customer = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "AreaFNAME",
FieldName = "AreaFNAME",
Name = new LocaleValue("大区")
}, 0);
customer.IsShowTotal = false;
SettingInfo.RowTitleFields.Add(customer);
SettingInfo.SelectedFields.Add(customer);
SettingField materia = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "YMDATE",
FieldName = "YMDATE",
Name = new LocaleValue("年月")
}, 0);
SettingInfo.ColTitleFields.Add(materia);
SettingInfo.SelectedFields.Add(materia);
SettingField material = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FQTY",
FieldName = "FQTY",
Name = new LocaleValue("要货")
}, 1);
SettingInfo.AggregateFields.Add(material);
SettingInfo.SelectedFields.Add(material);
SettingField materia2 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "FREALQTY",
FieldName = "FREALQTY",
Name = new LocaleValue("出货")
}, 2);
SettingInfo.AggregateFields.Add(materia2);
SettingInfo.SelectedFields.Add(materia2);
SettingField materia3 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "Deviation",
FieldName = "Deviation",
Name = new LocaleValue("偏差")
}, 3);
SettingInfo.AggregateFields.Add(materia3);
SettingInfo.SelectedFields.Add(materia3);
SettingField materia4 = PivotReportSettingInfo.CreateColumnSettingField(new TextField()
{
Key = "Deviationrate",
FieldName = "Deviationrate",
Name = new LocaleValue("偏差率")
}, 4);
SettingInfo.AggregateFields.Add(materia4);
SettingInfo.SelectedFields.Add(materia4);
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
string strCreateTable = string.Format(@"
/*dialect*/SELECT TM.FID,TM.FBILLNO,TM.FCREATORID,TM.FCREATEDATE,TF.FALLAMOUNT,ml.FNAME as FMaterialName,TE.FENTRYID as FIDENTITYID INTO {0}
FROM T_PUR_POORDER TM
INNER JOIN T_PUR_POORDERENTRY_F TF ON TM.FID = TF.FID
INNER JOIN T_PUR_POORDERENTRY TE ON TM.FID = TE.FID
inner join T_BD_MATERIAL m on te.FMATERIALID = m.FMATERIALID
inner join T_BD_MATERIAL_L ml on m.FMATERIALID = ml.FMATERIALID
WHERE 1=1 ", tableName);
var custFilter = filter.FilterParameter.CustomFilter;
if (custFilter != null && custFilter.DynamicObjectType.Properties.Contains("F_PAEZ_Date"))
{
var date = custFilter["F_PAEZ_Date"];
if (date != null && !string.IsNullOrEmpty(date.ToString()))
{
string strDate = DateTime.Parse(date.ToString()).ToString("yyyy-MM-dd");
strCreateTable += string.Format(" AND TM.FCREATEDATE >= '{0}'", strDate);
}
}
//base.AfterCreateTempTable(tablename);
DBUtils.ExecuteDynamicObject(this.Context, strCreateTable);
DataTable reportSouce = DBUtils.ExecuteDataSet(this.Context, string.Format("SELECT * FROM {0}", tableName)).Tables[0];
this.SettingInfo = new PivotReportSettingInfo();
TextField field;
DecimalField fieldData;
//构造透视表列
//FID
field = new TextField();
field.Key = "FBILLNO";
field.FieldName = "FBILLNO";
field.Name = new LocaleValue("单据编号");
SettingField settingBillNo = PivotReportSettingInfo.CreateColumnSettingField(field, 0);
settingBillNo.IsShowTotal = false;
this.SettingInfo.RowTitleFields.Add(settingBillNo);
this.SettingInfo.SelectedFields.Add(settingBillNo);
field = new TextField();
field.Key = "FCREATORID";
field.FieldName = "FCREATORID";
field.Name = new LocaleValue("创建者");
SettingField settingCreateId = PivotReportSettingInfo.CreateColumnSettingField(field, 1);
this.SettingInfo.RowTitleFields.Add(settingCreateId);
this.SettingInfo.SelectedFields.Add(settingCreateId);
//构造行
field = new TextField();
field.Key = "FMaterialName";
field.FieldName = "FMaterialName";
field.Name = new LocaleValue("物料名称");
SettingField settingMaterial = PivotReportSettingInfo.CreateColumnSettingField(field, 0);
settingMaterial.IsShowTotal = false;
this.SettingInfo.ColTitleFields.Add(settingMaterial);
this.SettingInfo.SelectedFields.Add(settingMaterial);
//构造数据
fieldData = new DecimalField();
fieldData.Key = "FALLAMOUNT";
fieldData.FieldName = "FALLAMOUNT";
fieldData.Name = new LocaleValue("金额");
SettingField settingAmount = PivotReportSettingInfo.CreateDataSettingField(fieldData, 0, GroupSumType.Sum, "N3"); //N3表示3位小数
this.SettingInfo.AggregateFields.Add(settingAmount);
this.SettingInfo.SelectedFields.Add(settingAmount);
}
