{
  "name": "wpa/productivity_report/time_period_before.json",
  "database": "4d",
  "parameter": {
    "show_sql": false,
    "track_time_type": "OA",
    "start_date2": "",
    "start_date": "",
    "load_group": [],
    "production_lot": [],
    "subqueryXX": "SELECT work_load_group_id, production_lot, work_phase_est_id, SUM(work_time__amount) FROM work_phase_actual WHERE date_ <= '{{start_date}}' AND (date_ <> '{{start_date}}' OR (end_time < '12:00:00' AND shift_number >= 3)) AND (date_ <> '{{start_date2}}' OR end_time < '12:00:00' OR shift_number > 1) AND work_load_group_id IN ({{load_group}}) AND production_lot IN ({{production_lot}}) GROUP BY work_load_group_id, production_lot, work_phase_est_id"
  },
  "query": [
    ["wpa_date_", "<=", "start_date"], "and",
    ["wpa_type_", "<>", "track_time_type"], "and",
    ["take_shift_in_start_date"], "and",
    ["remove_previous_day_shift"], "and",
    ["wpa_work_load_group_id", "in", "load_group"], "and",
    ["wpa_production_lot", "in", "production_lot"]
  ],
  "set": {
    "take_shift_in_start_date": [
       ["wpa_date_", "<>", "start_date"], "or", "shift_in_start_date"
    ],
    "shift_in_start_date": [
      ["wpa_end_time", "<", "12:00:00"], "and",
      ["wpa_shift_number", ">=", 3]
    ],
    "remove_previous_day_shift": [
       ["wpa_date_", "<>", "start_date2"], "or",
       ["wpa_end_time", "<", "12:00:00"], "or",
       ["wpa_shift_number", ">", 1]
    ]
  },
  "aggregate": {
    "sum": [
      "wpa_work_time__amount"
    ]
  },
  "field": [
    "wpa_work_load_group_id",
    "wpa_production_lot",
    "wpa_work_phase_est_id",
    "wpa_work_time__amount"
  ]
}
