public static function calculateBeforeUpdateOrCreate(array &$attributes, array $values = []) { if (array_key_exists(config("constants.flowPhase"), $attributes) && array_key_exists(config("constants.euIdColumn"), $attributes) && array_key_exists("OCCUR_DATE", $attributes)) { $fdcValues = EnergyUnitDataFdcValue::where($attributes)->first(); $active_hrs = $fdcValues["ACTIVE_HRS"]; $flow_phase = $attributes[config("constants.flowPhase")]; $object_id = $attributes[config("constants.euIdColumn")]; $occur_date = $attributes['OCCUR_DATE']; $rowTest = static::getEUTest($object_id, $occur_date); $theoFields = CfgFieldProps::getConfigFields(static::getTableName())->get(); $theoFieldArray = array_column($theoFields->toArray(), 'COLUMN_NAME'); if ($rowTest && is_numeric($active_hrs)) { $rat = $active_hrs / 24; foreach ($theoFieldArray as $field) { $_v = null; if ($flow_phase == 1) { if ($field == 'EU_DATA_GRS_VOL') { $_v = $rat * $rowTest->EU_TEST_LIQ_HC_VOL; } if ($field == 'EU_DATA_GRS_MASS') { $_v = $rat * $rowTest->EU_TEST_LIQ_HC_MASS; } } else { if ($flow_phase == 2) { if ($field == 'EU_DATA_GRS_VOL') { $_v = $rat * $rowTest->EU_TEST_GAS_HC_VOL; } if ($field == 'EU_DATA_GRS_MASS') { $_v = $rat * $rowTest->EU_TEST_GAS_HC_MASS; } } else { if ($flow_phase == 21) { if ($field == 'EU_DATA_GRS_VOL') { $_v = $rat * $rowTest->EU_TEST_GAS_LIFT_VOL; } } else { if ($flow_phase == 5) { if ($field == 'EU_DATA_GRS_VOL') { $_v = $rat * $rowTest->EU_TEST_LIQ_1_VOL; } } else { if ($flow_phase == 3) { if ($field == 'EU_DATA_GRS_VOL') { $_v = $rat * $rowTest->EU_TEST_WTR_VOL; } if ($field == 'EU_DATA_GRS_MASS') { $_v = $rat * $rowTest->EU_WTR_MASS; } } } } } } $values[$field] = $_v; } } } return $values; }
public static function calculateBeforeUpdateOrCreate(array &$attributes, array $values = []) { if (array_key_exists("FLOW_ID", $attributes) && array_key_exists("OCCUR_DATE", $attributes)) { $object_id = $attributes["FLOW_ID"]; $occur_date = $attributes["OCCUR_DATE"]; $fields = CfgFieldProps::getConfigFields(FlowDataFdcValue::getTableName())->where('COLUMN_NAME', '!=', 'CTV')->get(); $theoFields = CfgFieldProps::getConfigFields(FlowDataTheor::getTableName())->get(); $fieldArray = array_column($fields->toArray(), 'COLUMN_NAME'); $theoFieldArray = array_column($theoFields->toArray(), 'COLUMN_NAME'); $fdcValues = FlowDataFdcValue::where(array(['FLOW_ID', $object_id], ['OCCUR_DATE', $occur_date]))->select($fieldArray)->first(); if ($fdcValues) { foreach ($theoFieldArray as $field) { if (!array_key_exists($field, $values)) { $values[$field] = $fdcValues->{$field}; } } } } return $values; }
public function getField($tablename) { $cfgFieldProps = CfgFieldProps::where('TABLE_NAME', '=', [$tablename])->where('USE_FDC', '=', '1')->orderBy('FIELD_ORDER', 'asc')->select('COLUMN_NAME', 'LABEL', 'FDC_WIDTH')->get(); $column = ""; $label = ""; $totalWidth = 0; foreach ($cfgFieldProps as $str) { $width = $str->FDC_WIDTH; $column .= "," . $str->COLUMN_NAME; $label .= "," . ($str->LABEL ? $str->LABEL : $str->COLUMN_NAME); if ($width > 0) { $width = $width + 18; } else { $width = $width + 118; } $totalWidth = $totalWidth + $width; } $column = substr($column, 1, strlen($column)); $listColumn = explode(',', $column); $label = substr($label, 1, strlen($label)); $listLabel = explode(',', $label); $obj = array(['listColumn' => $listColumn, 'listLabel' => $cfgFieldProps, 'totalWidth' => $totalWidth]); return $obj; }
public function getValueSurveillance(Request $request) { $data = $request->all(); $flow_phase = $data['flow_phase']; $vparam = $data['vparam']; $occur_date = Carbon::createFromFormat('m/d/Y', $data['occur_date'])->format('Y-m-d'); $ret = ""; $date_begin = $occur_date; $date_end = $occur_date; foreach ($vparam as $v) { $cell_id = $v['ID']; $object_type = $v['OBJECT_TYPE']; $object_id = $v['OBJECT_ID']; $conn_id = $v['CONN_ID']; $phase_config = $v['SUR_PHASE_CONFIG']; $su = $v['SU']; if ($object_type == 'ENERGY_UNIT') { $phase_configs = explode("!!", $phase_config); $phase0 = explode("@@", $phase_configs[0]); $phase1 = explode("/", $phase_configs[1]); $table = $phase1[0]; $field = $phase1[1]; if (!$field) { $field = "EU_DATA_GRS_VOL"; } if (!$table) { $table = "ENERGY_UNIT_DATA_VALUE"; } if (count($phase0) > 0) { $flow_phases = "-1"; $datas = array(); foreach ($phase0 as $a1) { $as2 = explode("^^", $a1); if ($as2[0]) { $flow_phases .= "," . $as2[0]; $datas[$as2[0]] = $as2; } } $table = strtolower($table); $table = str_replace(' ', '', ucwords(str_replace('_', ' ', $table))); $model = 'App\\Models\\' . $table; \DB::enableQueryLog(); $conditions = explode(',', $flow_phases); $tmps = $model::where(['EU_ID' => $object_id])->whereDate('OCCUR_DATE', '=', $occur_date)->whereIn('FLOW_PHASE', $conditions)->get([$field . ' AS FIELD_VALUE', 'FLOW_PHASE']); \Log::info(\DB::getQueryLog()); $arr = array(); foreach ($tmps as $tmp) { $value = $tmp->FIELD_VALUE; if (is_numeric($value)) { $arr[$tmp->FLOW_PHASE] = number_format($value, 2); } else { $arr[$tmp->FLOW_PHASE] = "--"; } } $cells_data["{$cell_id}"]["%SF"] = $arr; } } $field_tables = explode("@", $su); $label = ""; foreach ($field_tables as $field_table) { if (strpos($field_table, "TAG:") !== FALSE) { if ($conn_id > 0) { $conn_objs["{$conn_id}"][] = "{$cell_id}~{$field_table}"; } } else { if ($field_table) { $f = explode("/", $field_table); $table = $f[0]; $field = $f[1]; $label = CfgFieldProps::where(['TABLE_NAME' => $table, 'COLUMN_NAME' => $field])->select('LABEL')->first(); if (count($label) > 0) { $xlabel = $label->LABEL; } else { $xlabel = "{$table}/{$field}"; } $table = strtolower($table); $table = str_replace(' ', '', ucwords(str_replace('_', ' ', $table))); $model = 'App\\Models\\' . $table; $condition = array(); if ($object_type == 'FLOW') { $value = "--"; $condition['FLOW_ID'] = $object_id; } else { if ($object_type == 'TANK') { $value = "--"; $condition['TANK_ID'] = $object_id; } else { if ($object_type == 'STORAGE') { $value = "--"; $condition['STORAGE_ID'] = $object_id; } else { if ($object_type == 'EQUIPMENT') { $value = "--"; $condition['EQUIPMENT_ID'] = $object_id; } else { if ($object_type == 'ENERGY_UNIT') { $value = "--"; $condition['EU_ID'] = $object_id; $condition['FLOW_PHASE'] = $flow_phase; } else { $value = "--"; } } } } } \DB::enableQueryLog(); $values = $model::where($condition)->whereDate('OCCUR_DATE', '=', $occur_date)->SELECT([$field . ' AS FIELD_VALUE'])->first(); \Log::info(\DB::getQueryLog()); if (count($values) > 0) { $value = $values->FIELD_VALUE; } $cells_data["{$cell_id}"]["{$xlabel}"] = is_numeric($value) ? number_format($value, 2) : $value; } } } } /* * if(count($conn_objs)>0){ * //include("../interface/adodb/adodb.inc.php"); * foreach ($conn_objs as $conn_id => $objinfos){ * $cell_ids=array(); * $tagcondition=""; * foreach($objinfos as $objinfo){ * $os=explode("~", $objinfo); * $cell_id=$os[0]; * $tag=str_replace("@TAG:","",$os[1]); * $cells_data["$cell_id"]["$tag"]="--"; * if($cell_id && $tag){ * $cell_ids["$tag"]=$cell_id; * $tagcondition.=($tagcondition?" or ":"")."tag='$tag'"; * } * } * $sql="SELECT tt.tag,tt.TIME,tt.value * FROM [piarchive].[picomp] tt * inner join * ( * SELECT tag tagx,max(time) mtime * FROM [piarchive].[picomp] * WHERE ($tagcondition) * AND time BETWEEN '$date_begin' AND '$date_end' group by tag * ) grouped on tt.tag=grouped.tagx and tt.time=grouped.mtime * WHERE ($tagcondition) * AND value is not null * AND time BETWEEN '$date_begin' AND '$date_end'"; * * $connection = new \COM("ADODB.Connection") or die("Cannot start ADO"); * $row=getOneRow("select SERVER,USER_NAME,PASSWORD,`TYPE` from int_connection where id=$conn_id"); * $connection->Open("Provider=ihOLEDB.iHistorian.1;Initial Catalog=piarchive;Data Source=$row[SERVER];User ID =$row[USER_NAME];Password=$row[PASSWORD];"); * $result_set = $connection->Execute($sql); * while (!$result_set->EOF) * { * $tagID=$result_set->fields[0]->value; * $tagValue=$result_set->fields[2]->value; * $cell_id=$cell_ids["$tagID"]; * if($cell_id) $cells_data["$cell_id"]["$tagID"]=$tagValue; * $result_set->MoveNext(); * } * $result_set->Close(); * $connection->Close(); * } * } */ foreach ($cells_data as $cell_id => $cell_data) { $ret .= ($ret == "" ? "" : "#") . "{$cell_id}^"; foreach ($cell_data as $data_label => $data_value) { if ($data_label == "%SF") { $sv = ""; foreach ($data_value as $flow_phase => $phase_value) { $sv .= ($sv == "" ? "" : "%SV") . $flow_phase . "%SV" . $phase_value; } $ret .= "%SF^{$sv}" . "#" . "{$cell_id}^"; } else { $ret .= "{$data_label}: {$data_value}\n"; } } } return response()->json("ok{$ret}"); }
public function saveprop(Request $request) { $data = $request->all(); $vfield = $data['field']; $fields = explode(",", $vfield); $table = $data['table']; $param = []; if (isset($data['data_method'])) { $param['DATA_METHOD'] = $data['data_method']; $param['INPUT_ENABLE'] = $data['data_method']; } if (isset($data['formula'])) { $param['FORMULA'] = $data['formula']; } if (isset($data['input_type'])) { $param['INPUT_TYPE'] = $data['input_type']; } if (isset($data['data_format'])) { $param['VALUE_FORMAT'] = $data['data_format']; } if (isset($data['max_value'])) { $param['VALUE_MAX'] = $data['max_value']; } if (isset($data['min_value'])) { $param['VALUE_MIN'] = $data['min_value']; } if (isset($data['fdc_width'])) { $param['FDC_WIDTH'] = $data['fdc_width']; } if (isset($data['friendly_name']) && count($fields) == 1) { $param['LABEL'] = $data['friendly_name']; } $objectExtension = isset($data['objectExtension']) && count($data['objectExtension']) > 0 ? json_encode($data['objectExtension']) : null; $param['USE_FDC'] = $data['us_data']; $param['USE_DIAGRAM'] = $data['us_sr']; $param['USE_GRAPH'] = $data['us_gr']; $param['IS_MANDATORY'] = $data['is_mandatory']; $param['OBJECT_EXTENSION'] = $objectExtension; //\DB::enableQueryLog (); CfgFieldProps::where(['TABLE_NAME' => $table])->whereIn('COLUMN_NAME', $fields)->update($param); //\Log::info ( \DB::getQueryLog () ); return response()->json('OK'); }
public function getOriginProperties($dcTable) { $properties = CfgFieldProps::where('TABLE_NAME', '=', $dcTable)->where('USE_FDC', '=', 1)->orderBy('FIELD_ORDER')->get(['COLUMN_NAME as data', 'COLUMN_NAME as name', 'FDC_WIDTH as width', 'LABEL as title', "DATA_METHOD", "INPUT_ENABLE", 'INPUT_TYPE', 'VALUE_MIN', 'VALUE_FORMAT', 'ID', 'FIELD_ORDER', 'VALUE_MAX', 'OBJECT_EXTENSION']); return $properties; }
public function getProperty(Request $request) { $data = $request->all(); $result = array(); $model = 'App\\Models\\' . $data['table']; $tableName = $model::getTableName(); $tmp = CfgFieldProps::where(['USE_FDC' => 1, 'TABLE_NAME' => $tableName])->get(['COLUMN_NAME AS CODE', 'LABEL AS NAME']); if (count($tmp) > 0) { foreach ($tmp as $t) { if ($t->NAME == '' || is_null($t->NAME)) { $t->NAME = $t->CODE; } array_push($result, $t); } } return response()->json($result); }
public static function doFormula($tName, $keyfield, $keyvalues, $echo_only = false) { if (!$keyfield || !$keyvalues) { return false; } $mdl = "App\\Models\\{$tName}"; $tablename = $mdl::getTableName(); $formulas = CfgFieldProps::where('table_name', $tablename)->whereNotNull('FORMULA')->where('FORMULA', '<>', '')->select("COLUMN_NAME", "FORMULA")->get(); // $sSet=""; $values = []; foreach ($formulas as $row) { if ($row->FORMULA) { $ss = trim($row->FORMULA); $sWhere = ""; if (strpos($ss, '{') !== false) { $k1 = strpos($ss, '{'); $k2 = strpos($ss, '}', $k1); if ($k2 > $k1 + 1) { $sWhere = substr($ss, $k1 + 1, $k2 - $k1 - 1); } } if (strpos(strtoupper($ss), "HOURS") !== false && strpos(strtoupper($ss), "HOURS") == 0) { $ss = "time_to_sec(timediff" . substr($ss, 5) . ") / 3600"; } else { if (substr($ss, 0, 1) === "[") { $k2 = 0; $x_ss = ""; $i = 0; while (true) { $i++; if ($i > 100) { break; } $k1 = strpos($ss, '[', $k2); if ($k1 === false) { break; } $kx = $k2; $k2 = strpos($ss, ']', $k1); if ($k2 === false) { break; } if ($kx > 0 && $k1 > $kx) { $x_ss .= substr($ss, $kx + 1, $k1 - $kx - 1); } $i1 = strpos($ss, '(', $k1); if ($i1 === false) { break; } $i2 = strpos($ss, ')', $i1); if ($i2 === false) { break; } $s_table = substr($ss, $k1 + 1, $i1 - $k1 - 1); $x_where_fields = explode(',', substr($ss, $i1 + 1, $i2 - $i1 - 1)); $s_where = ""; foreach ($x_where_fields as $x_where_field) { $x_where_field_parts = explode('=', trim($x_where_field)); $is_raw_value = false; if (count($x_where_field_parts) > 1) { $v_val = $x_where_field_parts[1]; if (is_numeric($v_val) || substr($v_val, 0, 1) == "'") { $is_raw_value = true; } } if ($is_raw_value) { $s_where .= ($s_where ? " and " : "") . "`{$x_where_field_parts['0']}`={$v_val}"; } else { $s_where .= ($s_where ? " and " : "") . "`{$x_where_field_parts['0']}`=`{$tablename}`.`" . $x_where_field_parts[count($x_where_field_parts) - 1] . "`"; } } $s_select = substr($ss, $i2 + 1, $k2 - $i2 - 1); $x_ss .= "(select {$s_select} from {$s_table} where {$s_where} limit 1)"; } $ss = $x_ss; } } if ($sWhere) { // $sSet.=($sSet?",":"")."`$row->COLUMN_NAME`=(case when $sWhere then $ss else `$row->COLUMN_NAME` end)"; $values[$row->COLUMN_NAME] = \DB::raw("(case when {$sWhere} then {$ss} else `{$row->COLUMN_NAME}` end)"); } else { // $sSet.=($sSet?",":"")."`$row->COLUMN_NAME`=$ss"; $values[$row->COLUMN_NAME] = \DB::raw($ss); } } } // if($sSet) if (count($values) > 0) { if ($echo_only) { echo "SQL formular: {$values}"; } else { /* $ids = implode("','",$keyvalues); $sSQL="update $tablename set $sSet where `$keyfield` in ($ids)"; $result = \DB::update($sSQL); */ // $result = \DB::update('update ? set ? where ? in ?', [$tablename,$sSet,$keyfield,$keyvalues]); // error_log("<br>sSQL: $sSQL</br>", 3, "C:/xampp/htdocs/eb/log/hung.log"); // $result=mysql_query($sSQL) or die("fail: ".$sSQL."-> error:".mysql_error()); // \DB::enableQueryLog(); $updateRecords = $mdl::whereIn($keyfield, $keyvalues)->update($values); // \Log::info(\DB::getQueryLog()); if ($updateRecords) { return array_keys($values); } } } return true; }