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); }