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;
}
