年度产销复盘BUG修改

hongji.li

2023-10-30 3bd62f5f68dfab21885d9b3f6c5d4e4defadae42

 _Main/BL/Type_GlobalOTDSOP/Method_CapacityAndSaleBudgeCompare.qbl

@@ -11,35 +11,22 @@
  Description: 'todo : mappingParent改为mpSync, random删了'
  TextBody:
  [*
    // yypsybs Sep-18-2023 (created)
    // ====不选时默认全选====
    if( years.Size() = 0 ) {
      years := selectsortedset( this, CapacityAndSaleBudgeFilterYear, item, item.YearNo() );
    if ( years.Size() = 0 ) {
      years := selectset( this, CapacityAndSaleBudgeFilterYear, tempCASBFY, tempCASBFY.YearNo() = Date::ActualDate().Year() );
    }
    if( months.Size() = 0 ) {
    if ( months.Size() = 0 ) {
      months := selectsortedset( this, CapacityAndSaleBudgeFilterMonth, item, item.MonthNo() );
    }
    traverse( macroPlans, Elements, item ) {
      debuginfo( "scenario : " + item.ScenarioName() );
    }
    traverse( items, Elements, item ) {
      debuginfo( "item : " + item.ItemName() );
    }
    traverse( years, Elements, item ) {
      debuginfo( "year : " + [String]item.YearNo() );
    }
    traverse( months, Elements, item ) {
      debuginfo( "month : " + [String]item.MonthNo() );
    }
    // ====清理旧数据====
    
    this.CapacityAndSaleBudgeCompareItemRow( relflush );
    this.CapacityAndSaleBudgeCompareItemColumn( relflush );
    // ====汇总所有出现的产品====
    historyData := selectset( otdTable, Global_MappingAnnualBudgetData, item, true );
    debuginfo( "historyData : " + [String]historyData.Size() );
    
    traverse( historyData, Elements, one ) {
      productCode := one.ProductID();
    // ====汇总所有出现的产品====
    targetglobal_MappingAnnualBudgetData := selectset( otdTable, Global_MappingAnnualBudgetData, item, true );
    
    traverse ( targetglobal_MappingAnnualBudgetData, Elements, gmabd ) {
      productCode := gmabd.ProductID();
    //  Global_MappingOperationBOM::CreateTestData( otdTable, "事业部" + [String](productCode.Length() mod 3), "面板基地" + [String](productCode.Length() mod 4), productCode );
    //  boms := selectset( otdTable, Global_MappingOperationBOM, bom, bom.ProductCode() = productCode );
    //  if( boms.Size() > 0 ) {
@@ -47,17 +34,16 @@
    //    businessType := bom.BusinessType();
    //    placeOfProductionOfArray := bom.OrganCode();
        // 相同面板基地和事业部的放一行
        row := CapacityAndSaleBudgeCompareItemRow::CreateIfNotExist( this, one.BusinessType(), one.OrgCodeFromBom() );
        row := CapacityAndSaleBudgeCompareItemRow::CreateIfNotExist( this, gmabd.BusinessType(), gmabd.OrganCode() );
        // 记录每行包含哪些product
        CapacityAndSaleBudgeCompareItemRowProduct::CreateIfNotExist( row, productCode );
    //  } else {
    //    debuginfo( "no boms for product : " + productCode );
    //  }
    }
    debuginfo( "CapacityAndSaleBudgeCompareItemRow : " + [String]selectset( this, CapacityAndSaleBudgeCompareItemRow, item, true ).Size() );
    
    // ====从左向右生成列头与内容====
    // 事业部，面板基地
    debuginfo( "dealing businessType and placeOfProductionOfArray" )
    columnBusinessType := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "事业部" );
    columnPlaceOfProductionOfArray := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板基地" );
    traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
@@ -66,100 +52,159 @@
      cell := columnPlaceOfProductionOfArray.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := row.PlaceOfProductionOfArray() );
      cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    }
    
    // 面板分配量 - 年度预算 (sheets)
    // 格式： 面板分配量 - 年 - 月（1-12 & total） - 年度预算/scenarioName
    debuginfo( "dealing 面板分配量" )
    if( CapacityAndSaleBudgeFilterItem::Contains( items, "面板分配量" ) ) {
      traverse( years, Elements, year ) {
        debuginfo( "面板分配量" + [String]year.YearNo() + "年" );
    if ( CapacityAndSaleBudgeFilterItem::Contains( items, "面板分配量" ) ) {
      traverse ( years, Elements, year ) {
        // 历史数据
        debuginfo( "面板分配量" + [String]year.YearNo() + "年历史数据" );
        traverse( months, Elements, month ) {
          columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板分配量-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-年度预算" );
          traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
            cellReal := Global_MappingForecast::GetQuantityByMonth( row.GetProductCodes(), otdTable, DateTime::Now().Year(), month.MonthNo() );
            cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
            cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //    traverse ( months, Elements, month ) {
    //      columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板分配量-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-年度预算" );
    //      traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //        cellReal := Global_MappingForecast::GetQuantityByMonth( row.GetProductCodes(), otdTable, DateTime::Now().Year(), month.MonthNo() );
    //        cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //        cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //      }
    //    }
    //    columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板分配量-" + [String]year.YearNo() + "年-total-年度预算" );
    //    traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //      cellReal := Global_MappingForecast::GetQuantityByYear( row.GetProductCodes(), otdTable, year.YearNo() );
    //      cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //      cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //    }
        
        traverse ( this, CapacityAndSaleBudgeCompareItemRow, casbcir ) {
          total := 0.0;
          traverse ( months, Elements, m ) {
            columnName  := "面板分配量-" + [String]year.YearNo() + "年-" + [String]m.MonthNo() + "月-年度预算";
            columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, columnName );
            cellReal    := Global_MappingForecast::GetQuantityByMonth( casbcir.GetProductCodes(), otdTable, DateTime::Now().Year(), m.MonthNo() );
            cell        := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
            total       := total + cellReal;
            cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
          }
          columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板分配量-" + [String]year.YearNo() + "年-total-年度预算" );
          cell       := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]total );
          columnYear.TotalIdentification( year.YearNo() );
          cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
        }
        columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板分配量-" + [String]year.YearNo() + "年-total-年度预算" );
        traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
          cellReal := Global_MappingForecast::GetQuantityByYear( row.GetProductCodes(), otdTable, year.YearNo() );
          cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
          cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
        }
        debuginfo( "面板分配量" + [String]year.YearNo() + "年S&OP数据" );
        
        // S&OP数据
        traverse( macroPlans, Elements, macroPlan ) {
          traverse( months, Elements, month ) {
            columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, 
                                                                                    "面板分配量-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-" + macroPlan.ScenarioName() );
            traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
              cellReal := Product_MP::GetNewSupplyByMonth( row.GetProductCodes(), macroPlan, year.YearNo(), month.MonthNo() );
              cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
              cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //    traverse ( macroPlans, Elements, macroPlan ) {
    //      traverse ( months, Elements, month ) {
    //        columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, 
    //                                                                                "面板分配量-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-" + macroPlan.ScenarioName() );
    //        traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //          cellReal := Product_MP::GetNewSupplyByMonth( row.GetProductCodes(), macroPlan, year.YearNo(), month.MonthNo() );
    //          cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //          cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //        }
    //      }
    //      columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, 
    //                                                                             "面板分配量-" + [String]year.YearNo() + "年-total-" + macroPlan.ScenarioName() );
    //      traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //        cellReal := Product_MP::GetNewSupplyByYear( row.GetProductCodes(), macroPlan, year.YearNo() );
    //        cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //        cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //      }
    //    }
        traverse ( macroPlans, Elements, mp ) {
          traverse ( this, CapacityAndSaleBudgeCompareItemRow, casbcir ) {
            total := 0.0;
            traverse ( months, Elements, m ) {
              columnName  := "面板分配量-" + [String]year.YearNo() + "年-" + [String]m.MonthNo() + "月-" + mp.ScenarioName();
              columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, columnName );
              cellReal    := Product_MP::GetNewSupplyByMonth( casbcir.GetProductCodes(), mp, year.YearNo(), m.MonthNo() );
              cell        := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
              total       := total + cellReal;
              cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
            }
          }
          columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, 
                                                                                 "面板分配量-" + [String]year.YearNo() + "年-total-" + macroPlan.ScenarioName() );
          traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
            cellReal := Product_MP::GetNewSupplyByYear( row.GetProductCodes(), macroPlan, year.YearNo() );
            cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
            cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
            columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "面板分配量-" + [String]year.YearNo() + "年-total-" + mp.ScenarioName() );
            cell       := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]total );
            columnYear.TotalIdentification( year.YearNo() );
            cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
          }
        }
      }
    }
    
    // 年度销售额
    debuginfo( "dealing 销售额" )
    if( CapacityAndSaleBudgeFilterItem::Contains( items, "销售额" ) ) {
      traverse( years, Elements, year ) {
        debuginfo( "销售额" + [String]year.YearNo() + "年" );
    if ( CapacityAndSaleBudgeFilterItem::Contains( items, "销售额" ) ) {
      traverse ( years, Elements, year ) {
        // 历史数据
        debuginfo( "销售额" + [String]year.YearNo() + "年历史数据" );
        traverse( months, Elements, month ) {
          columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-年度预算" );
          traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
            cellReal := Global_MappingAnnualBudgetData::GetSaleByMonth( row.GetProductCodes(), otdTable, year.YearNo(), month.MonthNo() );
            cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
            cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //    traverse ( months, Elements, month ) {
    //      columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-年度预算" );
    //      traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //        cellReal := Global_MappingAnnualBudgetData::GetSaleByMonth( row.GetProductCodes(), otdTable, year.YearNo(), month.MonthNo() );
    //        cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //        cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //      }
    //    }
    //    columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-年度预算" );
    //    traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //      cellReal := Global_MappingAnnualBudgetData::GetSaleByYear( row.GetProductCodes(), otdTable, year.YearNo() );
    //      cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //      cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //    }
        traverse ( this, CapacityAndSaleBudgeCompareItemRow, casbcir ) {
          total := 0.0;
          traverse ( months, Elements, m ) {
            columnName  := "对外销售额-" + [String]year.YearNo() + "年-" + [String]m.MonthNo() + "月-年度预算";
            columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, columnName );
            cellReal    := Global_MappingAnnualBudgetData::GetSaleByMonth( casbcir.GetProductCodes(), otdTable, year.YearNo(), m.MonthNo() );
            cell        := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
            total       := total + cellReal;
            cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
          }
          columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-total-年度预算" );
          cell       := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]total );
          columnYear.TotalIdentification( year.YearNo() );
          cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
        }
        columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-年度预算" );
        traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
          cellReal := Global_MappingAnnualBudgetData::GetSaleByYear( row.GetProductCodes(), otdTable, year.YearNo() );
          cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
          cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
        }
        debuginfo( "销售额" + [String]year.YearNo() + "年历史数据S&OP数据" );
        
        // S&OP数据
        traverse( macroPlans, Elements, macroPlan ) {
          traverse( months, Elements, month ) {
            columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-" + macroPlan.ScenarioName() );
            traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
              cellReal := Forecast::GetFulfilledBalanceByMonth( row.GetProductCodes(), macroPlan, year.YearNo(), month.MonthNo() );
              cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
              cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //    traverse ( macroPlans, Elements, macroPlan ) {
    //      traverse ( months, Elements, month ) {
    //        columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-" + [String]month.MonthNo() + "月-" + macroPlan.ScenarioName() );
    //        traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //          cellReal := Forecast::GetFulfilledBalanceByMonth( row.GetProductCodes(), macroPlan, year.YearNo(), month.MonthNo() );
    //          cell := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //          cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //        }
    //      }
    //      columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-total-" + macroPlan.ScenarioName() );
    //      traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
    //        // QID 23
    //        cellReal := Forecast::GetFulfilledBalanceByYear( row.GetProductCodes(), macroPlan, year.YearNo() );
    //        cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
    //        cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
    //      }
    //    }
        traverse ( macroPlans, Elements, mp ) {
          traverse ( this, CapacityAndSaleBudgeCompareItemRow, casbcir ) {
            total := 0.0;
            traverse ( months, Elements, m ) {
              columnName  := "对外销售额-" + [String]year.YearNo() + "年-" + [String]m.MonthNo() + "月-" + mp.ScenarioName();
              columnMonth := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, columnName );
              cellReal    := Forecast::GetFulfilledBalanceByMonth( casbcir.GetProductCodes(), mp, year.YearNo(), m.MonthNo() );
              cell        := columnMonth.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
              total       := total + cellReal;
              cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
            }
          }
          columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-total-" + macroPlan.ScenarioName() );
          traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
            // QID 23
            cellReal := Forecast::GetFulfilledBalanceByYear( row.GetProductCodes(), macroPlan, year.YearNo() );
            cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]cellReal );
            cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
            columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "对外销售额-" + [String]year.YearNo() + "年-total-" + mp.ScenarioName() );
            cell       := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]total );
            columnYear.TotalIdentification( year.YearNo() );
            cell.CapacityAndSaleBudgeCompareItemRow( relset, casbcir );
          }
        }
      }
    }
    
    // 大张盈利额
    debuginfo( "dealing 大张盈利额" )
    traverse( years, Elements, year ) {
    traverse ( years, Elements, year ) {
      // 历史数据
      columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "大张盈利额-" + [String]year.YearNo() + "年-年度预算" );
      traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
        // todo 
        // QID 23
      traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
        cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]Number::Random( 0, 0 ) );
        cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
      }
@@ -173,19 +218,17 @@
      }
    }
    // 产品盈利额
    debuginfo( "dealing 产品盈利额" )
    traverse( years, Elements, year ) {
    traverse ( years, Elements, year ) {
      // 历史数据
      columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "产品盈利额-" + [String]year.YearNo() + "年-年度预算" );
      traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
        // todo
      traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
        cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]Number::Random( 0, 0 ) );
        cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
      }
      // S&OP数据
      traverse( macroPlans, Elements, macroPlan ) {
      traverse ( macroPlans, Elements, macroPlan ) {
        columnYear := CapacityAndSaleBudgeCompareItemColumn::CreateIfNotExist( this, "产品盈利额-" + [String]year.YearNo() + "年-" + macroPlan.ScenarioName() );
        traverse( this, CapacityAndSaleBudgeCompareItemRow, row ) {
        traverse ( this, CapacityAndSaleBudgeCompareItemRow, row ) {
          // todo
          cell := columnYear.CapacityAndSaleBudgeCompareItemCell( relnew, CellContent := [String]Number::Random( 0, 0 ) );
          cell.CapacityAndSaleBudgeCompareItemRow( relset, row );
@@ -194,7 +237,6 @@
    }
    
    // 设置rowNo和columnNo
    debuginfo( "set rowNo and columnNo" );
    rowNo := 1;
    rowSorted := selectsortedset( this, CapacityAndSaleBudgeCompareItemRow, item, item.PlaceOfProductionOfArray() + "_" + item.BusinessType() );
    traverse( rowSorted, Elements, item ) {
@@ -206,6 +248,5 @@
      item.ColumnNo( columnNo );
      columnNo := columnNo + 1;
    }
    debuginfo( "done" );
  *]
}