public function getUoms($properties = null, $facility_id, $dcTable = null, $locked = false) { $uoms = []; $model = null; $withs = []; $i = 0; $selectData = false; $rs = []; foreach ($properties as $property) { $columnName = is_array($property) && array_key_exists('data', $property) ? $property['data'] : $property->data; switch ($columnName) { case 'PRESS_UOM': $withs[] = 'CodePressUom'; $uoms[] = ['id' => 'CodePressUom', 'targets' => $i, 'COLUMN_NAME' => 'PRESS_UOM']; break; case 'TEMP_UOM': $withs[] = 'CodeTempUom'; $uoms[] = ['id' => 'CodeTempUom', 'targets' => $i, 'COLUMN_NAME' => 'TEMP_UOM']; break; case 'FL_POWR_UOM': case 'EU_POWR_UOM': $withs[] = 'CodePowerUom'; $uoms[] = ['id' => 'CodePowerUom', 'targets' => $i, 'COLUMN_NAME' => 'FL_POWR_UOM']; break; case 'FL_ENGY_UOM': case 'EU_ENGY_UOM': $withs[] = 'CodeEnergyUom'; $uoms[] = ['id' => 'CodeEnergyUom', 'targets' => $i, 'COLUMN_NAME' => 'FL_ENGY_UOM']; break; case 'FL_MASS_UOM': case 'EU_MASS_UOM': $withs[] = 'CodeMassUom'; $uoms[] = ['id' => 'CodeMassUom', 'targets' => $i, 'COLUMN_NAME' => 'FL_MASS_UOM']; break; case 'VOL_UOM': case 'FL_VOL_UOM': case 'EU_VOL_UOM': $withs[] = 'CodeVolUom'; $uoms[] = ['id' => 'CodeVolUom', 'targets' => $i, 'COLUMN_NAME' => 'FL_VOL_UOM']; break; case 'EU_STATUS': $selectData = ['id' => 'EuStatus', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = collect([(object) ['ID' => -1, 'NAME' => '(Auto)'], (object) ['ID' => 1, 'NAME' => 'Online'], (object) ['ID' => 0, 'NAME' => 'Offline']]); $rs[] = $selectData; break; case 'ALLOC_TYPE': $selectData = ['id' => 'CodeAllocType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeAllocType::all(); $rs[] = $selectData; break; case 'TEST_METHOD': $selectData = ['id' => 'CodeTestingMethod', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeTestingMethod::all(); $rs[] = $selectData; break; case 'TEST_USAGE': $selectData = ['id' => 'CodeTestingUsage', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeTestingUsage::all(); $rs[] = $selectData; break; case 'EVENT_TYPE': $selectData = ['id' => 'CodeEventType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeEventType::all(); $rs[] = $selectData; break; case 'SRC_TYPE': $selectData = ['id' => 'CodeQltySrcType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeQltySrcType::all(); $rs[] = $selectData; break; case 'PRODUCT_TYPE': $selectData = ['id' => 'CodeProductType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeProductType::all(); $rs[] = $selectData; break; case 'DEFER_REASON': $selectData = ['id' => 'CodeDeferReason', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeDeferReason::all(); $rs[] = $selectData; break; case 'DEFER_STATUS': $selectData = ['id' => 'CodeDeferStatus', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeDeferStatus::all(); $rs[] = $selectData; break; case 'CODE1': $selectData = ['id' => 'CodeDeferCode1', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeDeferCode1::all(); $rs[] = $selectData; break; case 'DEFER_CATEGORY': $selectData = ['id' => 'CodeDeferCategory', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeDeferCategory::all(); $rs[] = $selectData; break; case 'DEFER_GROUP_TYPE': $selectData = ['id' => 'CodeDeferGroupType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeDeferGroupType::all(); $rs[] = $selectData; break; case 'TICKET_TYPE': $selectData = ['id' => 'CodeTicketType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeTicketType::all(); $rs[] = $selectData; break; case 'TARGET_TANK': $selectData = ['id' => 'Tank', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = Tank::where('FACILITY_ID', $facility_id)->get(); $rs[] = $selectData; break; case 'CARRIER_ID': case 'PD_TRANSIT_CARRIER_ID': case 'CONNECTING_CARRIER': if ($dcTable == \App\Models\PdCargoNomination::getTableName() && !$locked) { break; } $selectData = ['id' => 'PdTransitCarrier', 'targets' => $i, 'COLUMN_NAME' => $columnName]; if ($dcTable == \App\Models\RunTicketFdcValue::getTableName() || $dcTable == \App\Models\RunTicketValue::getTableName()) { $selectData['data'] = PdTransitCarrier::where('TRANSIT_TYPE', 1)->get(); } else { $selectData['data'] = PdTransitCarrier::all(); } $rs[] = $selectData; break; case 'BA_ID': if ($dcTable != Personnel::getTableName()) { $selectData = ['id' => 'BaAddress', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = BaAddress::all(); $rs[] = $selectData; } break; case 'SEVERITY_ID': $selectData = ['id' => 'CodeSafetySeverity', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeSafetySeverity::where('ACTIVE', 1)->orderBy('ORDER')->orderBy('ID')->get(); $rs[] = $selectData; break; case 'STATUS': $selectData = ['id' => 'CodeCommentStatus', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeCommentStatus::where('ACTIVE', 1)->orderBy('ORDER')->orderBy('ID')->get(); $rs[] = $selectData; break; case 'OFFLINE_REASON_CODE': $selectData = ['id' => 'CodeEqpOfflineReason', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeEqpOfflineReason::where('ACTIVE', 1)->orderBy('ORDER')->orderBy('ID')->get(); $rs[] = $selectData; break; case 'EQP_FUEL_CONS_TYPE': $selectData = ['id' => 'CodeEqpFuelConsType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeEqpFuelConsType::where('ACTIVE', 1)->orderBy('ORDER')->orderBy('ID')->get(); $rs[] = $selectData; break; case 'EQP_GHG_REL_TYPE': $selectData = ['id' => 'CodeEqpGhgRelType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeEqpGhgRelType::where('ACTIVE', 1)->orderBy('ORDER')->orderBy('ID')->get(); $rs[] = $selectData; break; case 'EQP_GHG_UOM': case 'EQP_CONS_UOM': $selectData = ['id' => 'CodeVolUom', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeVolUom::all(); $rs[] = $selectData; break; case 'TYPE': $selectData = ['id' => 'CodePersonnelType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodePersonnelType::all(); $rs[] = $selectData; break; case 'TITLE': $selectData = ['id' => 'CodePersonnelTitle', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodePersonnelTitle::all(); $rs[] = $selectData; break; case 'SYSTEM_ID': $selectData = ['id' => 'IntSystem', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = IntSystem::all(); $rs[] = $selectData; break; case 'FREQUENCY': $selectData = ['id' => 'CodeReadingFrequency', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeReadingFrequency::all(); $rs[] = $selectData; break; case 'ALLOW_OVERRIDE': $selectData = ['id' => 'CodeBoolean', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeBoolean::all(); $rs[] = $selectData; break; case 'FLOW_PHASE': $selectData = ['id' => 'CodeFlowPhase', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = CodeFlowPhase::all(); $rs[] = $selectData; break; case 'REQUEST_UOM': case 'NOMINATION_UOM': case 'REQUEST_QTY_UOM': case 'SCHEDULE_UOM': case 'ATTRIBUTE_UOM': case 'LOAD_UOM': case 'QTY_UOM': case 'ITEM_UOM': $selectData = ['id' => 'PdCodeMeasUom', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeMeasUom::all(); $rs[] = $selectData; break; case 'PRIORITY': $selectData = ['id' => 'PdCodeCargoPriority', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeCargoPriority::all(); $rs[] = $selectData; break; case 'QUANTITY_TYPE': $selectData = ['id' => 'PdCodeCargoQtyType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeCargoQtyType::all(); $rs[] = $selectData; break; case 'LIFTING_ACCT': case 'LIFTING_ACCOUNT': $selectData = ['id' => 'PdLiftingAccount', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdLiftingAccount::all(); $rs[] = $selectData; break; case 'CONTRACT_ID': $selectData = ['id' => 'PdContract', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdContract::all(); $rs[] = $selectData; break; case 'STORAGE_ID': $selectData = ['id' => 'Storage', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\Storage::where('FACILITY_ID', $facility_id)->get(); $rs[] = $selectData; break; case 'REQUEST_TOLERANCE': $selectData = ['id' => 'PdCodeQtyAdj', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeQtyAdj::all(); $rs[] = $selectData; break; case 'ADJUSTABLE_TIME': case 'NOMINATION_ADJ_TIME': $selectData = ['id' => 'PdCodeTimeAdj', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeTimeAdj::all(); $rs[] = $selectData; break; case 'INCOTERM': $selectData = ['id' => 'PdCodeIncoterm', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeIncoterm::all(); $rs[] = $selectData; break; case 'TRANSIT_TYPE': $selectData = ['id' => 'PdCodeTransitType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeTransitType::all(); $rs[] = $selectData; break; /* case 'ACTIVITY_NAME' : $selectData = ['id'=>'ID','targets'=>$i,'COLUMN_NAME'=>'NAME']; $sql = ""; $sql .= " SELECT ID, NAME FROM pd_code_load_activity a where exists (select 1 from PD_CARGO_LOAD b join TERMINAL_TIMESHEET_DATA c ON ( b.ID = c.PARENT_ID AND c.IS_LOAD = 1 ) where c.ACTIVITY_ID = a.ID)"; $sql .= " union all"; $sql .= " SELECT ID, NAME FROM pd_code_load_activity a where exists (select 1 from PD_CARGO_UNLOAD b join TERMINAL_TIMESHEET_DATA c ON ( b.ID = c.PARENT_ID AND c.IS_LOAD = 1 ) where c.ACTIVITY_ID = a.ID)"; $tmp = \DB::select($sql); $selectData['data'] = $tmp; $rs[] = $selectData; break; */ /* case 'ACTIVITY_NAME' : $selectData = ['id'=>'ID','targets'=>$i,'COLUMN_NAME'=>'NAME']; $sql = ""; $sql .= " SELECT ID, NAME FROM pd_code_load_activity a where exists (select 1 from PD_CARGO_LOAD b join TERMINAL_TIMESHEET_DATA c ON ( b.ID = c.PARENT_ID AND c.IS_LOAD = 1 ) where c.ACTIVITY_ID = a.ID)"; $sql .= " union all"; $sql .= " SELECT ID, NAME FROM pd_code_load_activity a where exists (select 1 from PD_CARGO_UNLOAD b join TERMINAL_TIMESHEET_DATA c ON ( b.ID = c.PARENT_ID AND c.IS_LOAD = 1 ) where c.ACTIVITY_ID = a.ID)"; $tmp = \DB::select($sql); $selectData['data'] = $tmp; $rs[] = $selectData; break; */ case 'CARGO_ID': $selectData = ['id' => 'PdCargo', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCargo::all(); $rs[] = $selectData; break; case 'BERTH_ID': $selectData = ['id' => 'PdBerth', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdBerth::all(); $rs[] = $selectData; break; case 'CARGO_STATUS': $selectData = ['id' => 'PdCodeCargoStatus', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeCargoStatus::all(); $rs[] = $selectData; break; case 'CONTRACT_TYPE': case 'CONTACT_TYPE': $selectData = ['id' => 'PdCodeContractType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeContractType::all(); $rs[] = $selectData; break; case 'CONTRACT_PERIOD': $selectData = ['id' => 'PdCodeContractPeriod', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeContractPeriod::all(); $rs[] = $selectData; break; case 'CONTRACT_EXPENDITURE': $selectData = ['id' => 'PdContractExpenditure', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdContractExpenditure::all(); $rs[] = $selectData; break; case 'CONTRACT_TEMPLATE': $selectData = ['id' => 'PdContractTemplate', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdContractTemplate::all(); $rs[] = $selectData; break; case 'DEMURRAGE_EBO': $selectData = ['id' => 'PdCodeDemurrageEbo', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeDemurrageEbo::all(); $rs[] = $selectData; break; case 'SURVEYOR_BA_ID': $selectData = ['id' => 'BaAddress', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\BaAddress::where('SOURCE', 15)->get(); $rs[] = $selectData; break; case 'WITNESS_BA_ID1': case 'WITNESS_BA_ID2': $selectData = ['id' => 'BaAddress', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\BaAddress::where('SOURCE', 4)->get(); $rs[] = $selectData; break; case 'ACTIVITY_ID': case 'ACTIVITY_NAME': $selectData = ['id' => 'PdCodeLoadActivity', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeLoadActivity::all(); $rs[] = $selectData; break; case 'VOYAGE_ID': $selectData = ['id' => 'PdVoyage', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdVoyage::all(); $rs[] = $selectData; break; case 'DEPART_PORT': case 'NEXT_DESTINATION_PORT': case 'PORT_ID': case 'ULLAGE_PORT': case 'ORIGIN_PORT': case 'DESTINATION_PORT': $selectData = ['id' => 'PdPort', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdPort::all(); $rs[] = $selectData; break; case 'FLOW_ID': $selectData = ['id' => 'Flow', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\Flow::where("FACILITY_ID", '=', $facility_id)->get(); $rs[] = $selectData; break; case 'MEASURED_ITEM': $selectData = ['id' => 'PdCodeMeasItem', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeMeasItem::all(); $rs[] = $selectData; break; case 'FORMULA_ID': $selectData = ['id' => 'Formula', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\Formula::where("GROUP_ID", '=', 7)->get(); $rs[] = $selectData; break; case 'PROGRAM_TYPE': $selectData = ['id' => 'PdCodeProgramType', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeProgramType::all(); $rs[] = $selectData; break; case 'RUN_FREQUENCY': $selectData = ['id' => 'PdCodeRunFrequency', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeRunFrequency::all(); $rs[] = $selectData; break; case 'ADJUST_CODE': $selectData = ['id' => 'PdCodeLiftAcctAdj', 'targets' => $i, 'COLUMN_NAME' => $columnName]; $selectData['data'] = \App\Models\PdCodeLiftAcctAdj::all(); $rs[] = $selectData; break; } $i++; } if (count($withs) > 0) { $model = StandardUom::with($withs)->where('ID', $facility_id)->first(); if ($model == null) { $model = Facility::with($withs)->where('ID', $facility_id)->first(); } } // \DB::enableQueryLog(); if ($model != null) { foreach ($uoms as $key => $uom) { $uom['data'] = $model->{$uom}['id']; $uoms[$key] = $uom; $rs[] = $uom; } } return $rs; // \Log::info(\DB::getQueryLog()); }
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $accountId = $postData['PdLiftingAccount']; $date_end = array_key_exists('date_end', $postData) ? $postData['date_end'] : null; if ($date_end) { $date_end = \Helper::parseDate($date_end); } // \DB::enableQueryLog(); // \Log::info(\DB::getQueryLog()); $pdCargo = PdCargo::getTableName(); $pdCargoNomination = PdCargoNomination::getTableName(); $shipCargoBlmr = ShipCargoBlmr::getTableName(); $flowDataValue = FlowDataValue::getTableName(); $flow = Flow::getTableName(); $pdLiftingAccount = PdLiftingAccount::getTableName(); $query = ShipCargoBlmr::join($pdCargo, function ($query) use($shipCargoBlmr, $accountId, $pdCargo) { $query->on("{$pdCargo}.ID", '=', "{$shipCargoBlmr}.CARGO_ID")->where("{$pdCargo}.LIFTING_ACCT", '=', $accountId); })->whereNotNull("{$shipCargoBlmr}.DATE_TIME")->whereDate("{$shipCargoBlmr}.DATE_TIME", '>=', $occur_date)->whereDate("{$shipCargoBlmr}.DATE_TIME", '<=', $date_end)->select("{$shipCargoBlmr}.CARGO_ID", "{$pdCargo}.NAME as cargo_name", "{$shipCargoBlmr}.DATE_TIME as xdate", \DB::raw("null as nom_qty"), "{$shipCargoBlmr}.ITEM_VALUE as b_qty"); $cquery = PdCargoNomination::join($pdCargo, function ($query) use($pdCargoNomination, $accountId, $pdCargo) { $query->on("{$pdCargo}.ID", '=', "{$pdCargoNomination}.CARGO_ID")->where("{$pdCargo}.LIFTING_ACCT", '=', $accountId); })->whereDate("{$pdCargoNomination}.NOMINATION_DATE", '>=', $occur_date)->whereDate("{$pdCargoNomination}.NOMINATION_DATE", '<=', $date_end)->select("{$pdCargoNomination}.CARGO_ID", "{$pdCargo}.NAME as cargo_name", "{$pdCargoNomination}.NOMINATION_DATE as xdate", "{$pdCargoNomination}.NOMINATION_QTY as nom_qty", \DB::raw("null as b_qty")); $query->union($cquery); $xquery = \DB::table(\DB::raw("({$query->toSql()}) as x"))->select('x.CARGO_ID', 'x.cargo_name', 'x.xdate', \DB::raw('sum(x.nom_qty) as n_qty'), \DB::raw('sum(x.b_qty) as b_qty'))->addBinding($query->getBindings())->groupBy('x.xdate')->groupBy('x.CARGO_ID'); $xxquery = \DB::table(\DB::raw("({$xquery->toSql()}) as x"))->select('x.cargo_name', 'x.xdate', 'x.n_qty', 'x.b_qty', \DB::raw('null as flow_name'), \DB::raw('null as flow_qty'), \DB::raw('-ifnull(x.b_qty,x.n_qty) as cal_qty'))->addBinding($xquery->getBindings()); $flowquery = FlowDataValue::join($flow, "{$flowDataValue}.FLOW_ID", '=', "{$flow}.ID")->join($pdLiftingAccount, function ($query) use($pdLiftingAccount, $accountId, $flow) { $query->on("{$pdLiftingAccount}.PROFIT_CENTER", '=', "{$flow}.COST_INT_CTR_ID")->where("{$pdLiftingAccount}.ID", '=', $accountId); })->whereDate("{$flowDataValue}.OCCUR_DATE", '>=', $occur_date)->whereDate("{$flowDataValue}.OCCUR_DATE", '<=', $date_end)->select(\DB::raw("null as cargo_name"), "{$flowDataValue}.OCCUR_DATE as xdate", \DB::raw("null as n_qty"), \DB::raw("null as b_qty"), \DB::raw("concat({$flow}.name,' (',round({$pdLiftingAccount}.INTEREST_PCT),'%)') as flow_name"), \DB::raw("round({$flowDataValue}.FL_DATA_GRS_VOL*{$pdLiftingAccount}.INTEREST_PCT/100,3) as flow_qty"), \DB::raw("round({$flowDataValue}.FL_DATA_GRS_VOL*{$pdLiftingAccount}.INTEREST_PCT/100,3) as cal_qty"))->groupBy("{$flowDataValue}.OCCUR_DATE")->groupBy("{$flow}.ID"); $xxquery->union($flowquery); $xxxquery = \DB::table(\DB::raw("({$xxquery->toSql()}) as x"))->select('x.*', \DB::raw('case when x.b_qty is null then x.n_qty else null end as n_qty'), \DB::raw('sum(x.flow_qty) as flow_qty'), \DB::raw('ifnull(sum(x.cal_qty),0) cal_qty'), \DB::raw("'' as UOM"))->addBinding($xxquery->getBindings())->groupBy("x.xdate")->groupBy("x.cargo_name")->groupBy("x.flow_name"); $dataSet = $xxxquery->get(); $month = ""; $balance = 0; foreach ($dataSet as $key => $item) { $date = Carbon::parse($item->xdate); $monthOfItem = $date->month; //$item->xdate; if ($month != $monthOfItem) { $monthData = PdLiftingAccountMthData::where("LIFTING_ACCOUNT_ID", $accountId)->whereMonth('BALANCE_MONTH', '=', $monthOfItem)->select("BAL_VOL")->first(); $balance = $monthData != null ? $monthData->BAL_VOL : 0; $month = $monthOfItem; } $balance += $item->cal_qty; $item->cal_qty = $balance; $dataSet[$key] = $item; } /* $dataSet = $dataSet->each(function ($item, $key) use ($accountId,&$month,&$balance){ }); */ return ['dataSet' => $dataSet]; /* $sSQL="select x.xdate, DATE_FORMAT(x.xdate,'%m/%d/%Y') sdate, DATE_FORMAT(x.xdate,'%m/01/%Y') xmonth, x.cargo_name, case when x.b_qty is null then x.n_qty else null end n_qty, x.b_qty b_qty, x.flow_name, sum(x.flow_qty) flow_qty, ifnull(sum(x.cal_qty),0) cal_qty from( select x.cargo_name, x.xdate, x.n_qty, x.b_qty, null flow_name, null flow_qty, -ifnull(x.b_qty,x.n_qty) cal_qty from( SELECT x.cargo_name, x.xdate, sum(x.nom_qty) n_qty, sum(x.b_qty) b_qty from( select a.cargo_id, b.name cargo_name, a.NOMINATION_DATE xdate, a.NOMINATION_QTY nom_qty, null b_qty from pd_cargo_nomination a, pd_cargo b where a.cargo_id=b.id and b.LIFTING_ACCT=$accountId and a.NOMINATION_DATE between '$date_from' and '$date_to' union all select a.cargo_id, b.name cargo_name, date(a.DATE_TIME) xdate, null nom_qty, a.ITEM_VALUE b_qty from ship_cargo_blmr a, pd_cargo b where a.cargo_id=b.id and b.LIFTING_ACCT=$accountId and a.DATE_TIME is not null and date(a.DATE_TIME) between '$date_from' and '$date_to' ) x group by x.xdate,x.cargo_id ) x union all select x.cargo_name, x.xdate, null n_qty, null b_qty, x.flow_name, x.flow_qty, x.flow_qty cal_qty from( select null cargo_name, a.occur_date xdate, concat(b.name,' (',round(d.INTEREST_PCT),'%)') flow_name, round(a.FL_DAY_GRS_VOL*d.INTEREST_PCT/100,3) flow_qty from flow_day_value a, flow b, pd_lifting_account d where d.id=$accountId and d.PROFIT_CENTER=b.COST_INT_CTR_ID and b.id=a.FLOW_ID #and b.DISP in ('PROD_OIL','IMPORT_OIL') and a.OCCUR_DATE between '$date_from' and '$date_to' group by a.occur_date,b.id ) x ) x group by x.xdate,x.cargo_name,x.flow_name"; */ }
public function PdLiftingAccount($option = null) { return PdLiftingAccount::where("STORAGE_ID", $this->ID)->get(); }