Beispiel #1
0
 public function nominate(Request $request)
 {
     $postData = $request->all();
     $cargo_id = $postData['cargoId'];
     $pdCargo = PdCargo::getTableName();
     $pdCargoNomination = PdCargoNomination::getTableName();
     /* $checkNominated		= PdCargo::join($pdCargoNomination,"$pdCargoNomination.CARGO_ID", '=', "$pdCargo.ID")
       	->where("$pdCargo.ID",'=',$cargo_id)
       	->select("$pdCargo.NAME")
       	->first(); */
     $pdCargoNomination = PdCargoNomination::firstOrNew(["CARGO_ID" => $cargo_id]);
     if ($pdCargoNomination) {
         if ($pdCargoNomination->exists) {
             $result = ['code' => 'EXIST', 'message' => "Cargo id {$cargo_id} has been nominated already."];
         } else {
             $cargo = PdCargo::find($cargo_id);
             if ($cargo) {
                 $values = ["CARGO_ID" => $cargo_id, "REQUEST_DATE" => $cargo->REQUEST_DATE, "REQUEST_QTY" => $cargo->REQUEST_QTY, "REQUEST_QTY_UOM" => $cargo->REQUEST_UOM];
                 $insertId = $pdCargoNomination->fill($values)->save();
                 if ($insertId > 0) {
                     $result = ['code' => 'DONE', 'message' => "Cargo id {$cargo_id} is nominated successfully!"];
                 } else {
                     $result = ['code' => 'ERROR', 'message' => "unsuccessfully!"];
                 }
             } else {
                 $result = ['code' => 'NOT_EXIST', 'message' => "Cargo id {$cargo_id} not exist!"];
             }
         }
     } else {
         $result = ['code' => 'ERROR', 'message' => "unsuccessfully!"];
     }
     return response()->json($result);
 }
Beispiel #2
0
 public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
 {
     $storage_id = $postData['Storage'];
     $date_end = $postData['date_end'];
     $date_end = \Helper::parseDate($date_end);
     $mdlName = $postData[config("constants.tabTable")];
     $mdl = "App\\Models\\{$mdlName}";
     $pdCargo = PdCargo::getTableName();
     $dataSet = $mdl::join($pdCargo, "{$dcTable}.CARGO_ID", '=', "{$pdCargo}.ID")->whereDate("{$dcTable}.DATE_UNLOAD", '>=', $occur_date)->whereDate("{$dcTable}.DATE_UNLOAD", '<=', $date_end)->where("{$pdCargo}.STORAGE_ID", '=', $storage_id)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.*")->get();
     return ['dataSet' => $dataSet];
 }
Beispiel #3
0
 public function checkAndSave(&$values)
 {
     $cargo_id = $values['CARGO_ID'];
     $pdCargo = PdCargo::getTableName();
     $pdCargoNomination = PdCargoNomination::getTableName();
     $row = static::join($pdCargo, "{$pdCargoNomination}.CARGO_ID", '=', "{$pdCargo}.ID")->where("{$pdCargoNomination}.CARGO_ID", '=', $cargo_id)->select("{$pdCargo}.NAME")->first();
     if ($row == null) {
         $this->fill($values)->save();
         return $this;
     }
     throw new DataInputException("Cargo {$row->NAME} nominated already");
 }
 public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
 {
     $date_end = $postData['date_end'];
     $date_end = \Helper::parseDate($date_end);
     $storage_id = $postData['Storage'];
     $mdlName = $postData[config("constants.tabTable")];
     $mdl = "App\\Models\\{$mdlName}";
     $pdCargo = PdCargo::getTableName();
     //     	\DB::enableQueryLog();
     $dataSet = $mdl::join($pdCargo, function ($query) use($pdCargo, $storage_id, $dcTable) {
         $query->on("{$dcTable}.CARGO_ID", '=', "{$pdCargo}.ID")->where("{$pdCargo}.STORAGE_ID", '=', $storage_id);
     })->whereDate("{$dcTable}.SCHEDULE_DATE", '<=', $date_end)->whereDate("{$dcTable}.SCHEDULE_DATE", '>=', $occur_date)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.*")->orderBy("{$dcTable}")->get();
     //  		\Log::info(\DB::getQueryLog());
     $extraDataSet = $this->getExtraDataSet($dataSet);
     return ['dataSet' => $dataSet, 'extraDataSet' => $extraDataSet];
 }
 public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
 {
     $date_end = array_key_exists('date_end', $postData) ? $postData['date_end'] : null;
     if ($date_end) {
         $date_end = \Helper::parseDate($date_end);
     }
     $storageId = $postData['Storage'];
     $pd_voyage = PdVoyage::getTableName();
     $pd_cargo = PdCargo::getTableName();
     $pd_voyage_detail = PdVoyageDetail::getTableName();
     $column = array();
     $ObjColumn = $properties['properties'];
     foreach ($ObjColumn as $p) {
         array_push($column, "{$pd_voyage}.{$p->data}");
     }
     array_push($column, "{$pd_voyage_detail}.ID AS DT_RowId");
     array_push($column, "{$pd_voyage}.ID AS VOYAGE_ID");
     array_push($column, "{$pd_voyage_detail}.PARCEL_NO as MASTER_NAME");
     $dataSet = PdVoyage::join($pd_cargo, "{$pd_voyage}.CARGO_ID", '=', "{$pd_cargo}.ID")->join($pd_voyage_detail, "{$pd_voyage_detail}.VOYAGE_ID", '=', "{$pd_voyage}.ID")->where(["{$pd_cargo}.STORAGE_ID" => $storageId])->whereDate('SCHEDULE_DATE', '>=', $occur_date)->whereDate('SCHEDULE_DATE', '<=', $date_end)->orderBy("DT_RowId")->get($column);
     return ['dataSet' => $dataSet];
 }
 public function gen(Request $request)
 {
     try {
         $postData = $request->all();
         $result = \DB::transaction(function () use($postData) {
             $contract_id = $postData['contract_id'];
             $storage_id = $postData['PdContractQtyFormula'];
             $code1st = array_key_exists("code1st", $postData) ? $postData["code1st"] : 0;
             $year1 = array_key_exists("year1", $postData) ? $postData["year1"] : 0;
             $code2nd = array_key_exists("code2nd", $postData) ? $postData["code2nd"] : 0;
             $year = array_key_exists("year", $postData) ? $postData["year"] : 0;
             $month = array_key_exists("month", $postData) ? $postData["month"] : 0;
             $day = array_key_exists("day", $postData) ? $postData["day"] : "";
             $seq = array_key_exists("seq", $postData) ? $postData["seq"] : 0;
             $liftacc = array_key_exists("PdLiftingAccount", $postData) ? $postData["PdLiftingAccount"] : 0;
             $priority = array_key_exists("PdCodeCargoPriority", $postData) ? $postData["PdCodeCargoPriority"] : 0;
             $qtytype = array_key_exists("PdCodeCargoQtyType", $postData) ? $postData["PdCodeCargoQtyType"] : 0;
             $date1st = array_key_exists("date1st", $postData) ? $postData["date1st"] : 0;
             $avgqty = array_key_exists("avgqty", $postData) ? $postData["avgqty"] : 0;
             $uom = array_key_exists("PdCodeMeasUom", $postData) ? $postData["PdCodeMeasUom"] : 0;
             $adjtime = array_key_exists("PdCodeTimeAdj", $postData) ? $postData["PdCodeTimeAdj"] : 0;
             $tolerance = array_key_exists("PdCodeQtyAdj", $postData) ? $postData["PdCodeQtyAdj"] : 0;
             $qty = array_key_exists("qty", $postData) ? $postData["qty"] : 0;
             $n = strlen($seq);
             $num = $seq + 1 - 1;
             $count = 0;
             $x_qty = 0;
             if ($qty <= 0) {
                 return "Quantity value must be greater than zero";
             }
             if ($avgqty <= 0) {
                 return "Average quantity value must be greater than zero";
             }
             $date1st = \Helper::parseDate($date1st);
             $requestDate = $date1st;
             while (true) {
                 $x_qty += $avgqty;
                 $exit = $x_qty >= $qty;
                 $code = $code1st . $year1 . $code2nd . $year . $month . $day . str_pad($num, $n, '0', STR_PAD_LEFT);
                 PdCargo::where("CODE", $code)->delete();
                 PdCargo::insert(["CODE" => $code, "NAME" => $code, "LIFTING_ACCT" => $liftacc, "STORAGE_ID" => $storage_id, "REQUEST_DATE" => $requestDate, "REQUEST_QTY" => $avgqty, "REQUEST_UOM" => $uom, "PRIORITY" => $priority, "QUANTITY_TYPE" => $qtytype, "CONTRACT_ID" => $contract_id]);
                 $num++;
                 $requestDate = $requestDate->addMonths(6);
                 if ($exit) {
                     break;
                 }
             }
             return "Sucess";
         });
     } catch (\Exception $e) {
         $result = "could not generate cargo";
         \Log::info("\n---gen cargo--\nException wher run transation\n ");
         \Log::info($e->getMessage());
         \Log::info($e->getTraceAsString());
         // 			return response($e->getMessage(), 400);
     }
     return response()->json($result);
 }
Beispiel #7
0
 public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
 {
     $mdlName = $postData[config("constants.tabTable")];
     $mdl = "App\\Models\\{$mdlName}";
     $date_end = $postData['date_end'];
     $date_end = \Helper::parseDate($date_end);
     $storage_id = $postData['Storage'];
     $pdCargoLoad = PdCargoLoad::getTableName();
     $terminalTimesheetData = TerminalTimesheetData::getTableName();
     $pdCodeLoadActivity = PdCodeLoadActivity::getTableName();
     $pdBerth = PdBerth::getTableName();
     $pdCargo = PdCargo::getTableName();
     $pdCodeDemurrageEbo = PdCodeDemurrageEbo::getTableName();
     $pdCargoUnload = PdCargoUnload::getTableName();
     $demurrage = Demurrage::getTableName();
     $pdContractData = PdContractData::getTableName();
     $result = array();
     $aryMst = array();
     $lquery = PdCargoLoad::whereHas("TerminalTimesheetData", function ($query) use($terminalTimesheetData) {
         $query->where("{$terminalTimesheetData}.IS_LOAD", '=', 1);
     })->join($pdCargo, function ($query) use($pdCargo, $pdCargoLoad, $storage_id) {
         $query->on("{$pdCargo}.ID", '=', "{$pdCargoLoad}.CARGO_ID")->where("{$pdCargo}.STORAGE_ID", '=', $storage_id);
     })->leftJoin($pdBerth, "{$pdCargoLoad}.BERTH_ID", '=', "{$pdBerth}.ID")->leftJoin($pdCodeDemurrageEbo, "{$pdCargoLoad}.DEMURRAGE_EBO", '=', "{$pdCodeDemurrageEbo}.ID")->select("{$pdCargoLoad}.CARGO_ID", "{$pdCargoLoad}.BERTH_ID", "{$pdCargoLoad}.ID as ID", "{$pdCargoLoad}.DATE_LOAD", "{$pdCargoLoad}.DEMURRAGE_EBO", "{$pdBerth}.NAME as BERTH_NAME", "{$pdCargo}.NAME as CARGO_NAME", "{$pdCargo}.CONTRACT_ID", "{$pdCodeDemurrageEbo}.NAME as DEMURRAGE_EBO_NAME")->with(["TerminalTimesheetData" => function ($query) use($terminalTimesheetData) {
         $query->where("{$terminalTimesheetData}.IS_LOAD", '=', 1);
     }])->with(["Demurrage" => function ($query) use($terminalTimesheetData, $demurrage) {
         $query->where("{$demurrage}.ACTIVITY_ID", '=', "{$terminalTimesheetData}.ACTIVITY_ID");
     }])->with(["PdContractData" => function ($query) use($pdContractData, $pdCargo) {
         $query->where("{$pdContractData}.CONTRACT_ID", '=', "{$pdCargo}.CONTRACT_ID");
     }]);
     $ulquery = PdCargoUnload::whereHas("TerminalTimesheetData", function ($query) use($terminalTimesheetData) {
         $query->whereNull("{$terminalTimesheetData}.IS_LOAD")->orWhere("{$terminalTimesheetData}.IS_LOAD", '=', 0);
     })->join($pdCargo, function ($query) use($pdCargo, $pdCargoUnload, $storage_id) {
         $query->on("{$pdCargo}.ID", '=', "{$pdCargoUnload}.CARGO_ID")->where("{$pdCargo}.STORAGE_ID", '=', $storage_id);
     })->leftJoin($pdBerth, "{$pdCargoUnload}.BERTH_ID", '=', "{$pdBerth}.ID")->leftJoin($pdCodeDemurrageEbo, "{$pdCargoUnload}.DEMURRAGE_EBO", '=', "{$pdCodeDemurrageEbo}.ID")->select("{$pdCargoUnload}.CARGO_ID", "{$pdCargoUnload}.BERTH_ID", "{$pdCargoUnload}.ID as ID", "{$pdCargoUnload}.DATE_UNLOAD as DATE_LOAD", "{$pdCargoUnload}.DEMURRAGE_EBO", "{$pdBerth}.NAME as BERTH_NAME", "{$pdCargo}.NAME as CARGO_NAME", "{$pdCargo}.CONTRACT_ID", "{$pdCodeDemurrageEbo}.NAME as DEMURRAGE_EBO_NAME")->with(["TerminalTimesheetData" => function ($query) use($terminalTimesheetData) {
         $query->whereNull("{$terminalTimesheetData}.IS_LOAD")->orWhere("{$terminalTimesheetData}.IS_LOAD", '=', 0);
     }])->with(["Demurrage" => function ($query) use($terminalTimesheetData, $demurrage) {
         $query->where("{$demurrage}.ACTIVITY_ID", '=', "{$terminalTimesheetData}.ACTIVITY_ID");
     }])->with(["PdContractData" => function ($query) use($pdContractData, $pdCargo) {
         $query->where("{$pdContractData}.CONTRACT_ID", '=', "{$pdCargo}.CONTRACT_ID");
     }]);
     // 		$query		= $lquery->union($ulquery);//->orderBy("START_TIME","desc");
     $ldataSet = $lquery->get();
     $uldataSet = $ulquery->get();
     $dataSet = $ldataSet->merge($uldataSet);
     // 		$dataSet	= $uldataSet;
     $DT_RowId = 100;
     foreach ($dataSet as $key => $t) {
         $t->DT_RowId = $DT_RowId++;
         $timesheetDatas = $t->TerminalTimesheetData;
         $demurrages = $t->Demurrage;
         $pdContractDatas = $t->PdContractData;
         $rate = $pdContractDatas && $pdContractDatas->count() > 0 ? $pdContractDatas->first()->ATTRIBUTE_VALUE : 0;
         foreach ($timesheetDatas as $index => $tsheet) {
             $elapse_time = null;
             if ($tsheet->END_TIME && $tsheet->START_TIME) {
                 $endTime = $tsheet->END_TIME;
                 $startTime = $tsheet->START_TIME;
                 $elapse_time = $endTime->diffInHours($startTime);
             }
             $elapse_time = !$elapse_time || $elapse_time < 0 ? null : $elapse_time;
             $tsheet->ELAPSE_TIME = $elapse_time;
             $demurrage = $demurrages->where("ACTIVITY_ID", $tsheet->ACTIVITY_ID)->first();
             $tsheet->OVERRIDE_AMOUNT = $demurrage ? $demurrage->OVERRIDE_AMT : "";
             $amount = $elapse_time ? $elapse_time * $rate : null;
             $tsheet->AMOUNT = $amount && $amount != 0 ? $amount : "0.000";
             $tsheet->RATE_HOUR = $rate == 0 ? null : $rate;
             if ($index == 0) {
                 $t->TE_ID = $tsheet->ID;
                 $t->IS_LOAD = $tsheet->IS_LOAD;
                 $t->ACTIVITY_NAME = $tsheet->ACTIVITY_ID;
                 $t->START_TIME = $tsheet->getAttributes()["START_TIME"];
                 $t->END_TIME = $tsheet->getAttributes()["END_TIME"];
                 $t->ELAPSE_TIME = $tsheet->ELAPSE_TIME;
                 $t->OVERRIDE_AMOUNT = $tsheet->OVERRIDE_AMOUNT;
                 $t->RATE_HOUR = $tsheet->RATE_HOUR;
                 $t->AMOUNT = $tsheet->AMOUNT;
             }
         }
     }
     return ['dataSet' => $dataSet];
 }
 public function reset(Request $request)
 {
     $postData = $request->all();
     $id = $postData['nominationId'];
     $pdCargo = PdCargo::getTableName();
     $pdCargoNomination = PdCargoNomination::getTableName();
     $nomi_row = PdCargoNomination::find($id);
     if (!$nomi_row) {
         throw new DataInputException("cargo nomination id {$id} not existed");
     }
     $code = "OK";
     $message = "reset successfully";
     TerminalTimesheetData::whereIn('PARENT_ID', function ($query) use($id) {
         $query->select('ID')->from(PdCargoLoad::getTableName())->where('NOMINATION_ID', $id);
     })->where('IS_LOAD', 1)->delete();
     PdCargoLoad::where('NOMINATION_ID', $id)->delete();
     TerminalTimesheetData::whereIn('PARENT_ID', function ($query) use($id) {
         $query->select('ID')->from(PdCargoUnload::getTableName())->where('NOMINATION_ID', $id);
     })->where('IS_LOAD', 0)->delete();
     PdCargoUnload::where('NOMINATION_ID', $id)->delete();
     PdVoyageDetail::whereIn('VOYAGE_ID', function ($query) use($id) {
         $query->select('ID')->from(PdVoyage::getTableName())->where('NOMINATION_ID', $id);
     })->delete();
     PdVoyage::where('NOMINATION_ID', $id)->delete();
     PdCargoSchedule::where('NOMINATION_ID', $id)->delete();
     $nomi_row->CARGO_STATUS = 1;
     $nomi_row->save();
     $result = ['code' => $code, 'message' => $message];
     return response()->json($result);
 }
Beispiel #9
0
 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"; */
 }
Beispiel #10
0
 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());
 }