public function loadData($contractId, $postData) { $pdContractYear = PdContractYear::getTableName(); $pdContractCalculation = PdContractCalculation::getTableName(); $contractYears = PdContractYear::join($pdContractCalculation, "{$pdContractYear}.CALCULATION_ID", '=', "{$pdContractCalculation}.ID")->where("{$pdContractYear}.CONTRACT_ID", '=', $contractId)->select("{$pdContractYear}.FORMULA_VALUE", "{$pdContractYear}.YEAR", "{$pdContractCalculation}.FORMULA_ID")->orderBy("{$pdContractYear}.YEAR", 'desc')->get(); $propertiesArray = [(object) ['data' => 'NAME', 'title' => 'Contract Quantity', 'width' => 150], (object) ['data' => 'DESCRIPTION', 'title' => 'Description', 'width' => 400]]; $pdContractQtyFormula = PdContractQtyFormula::getTableName(); $data = PdContractQtyFormula::select("ID as DT_RowId", "{$pdContractQtyFormula}.*")->get(); $years = $contractYears->groupBy("YEAR"); if ($years->count() > 0) { foreach ($years as $year => $set) { $yearField = "year{$year}"; $propertiesArray[] = (object) ['data' => $yearField, 'title' => "{$year}", 'DATA_METHOD' => 0, 'INPUT_TYPE' => 2]; $formulaGroups = $set->groupBy("FORMULA_ID"); $data = $data->each(function ($item, $key) use($yearField, $formulaGroups) { $item->{$yearField} = $formulaGroups[$item->ID][0]->FORMULA_VALUE; }); } } $properties = collect($propertiesArray); $results['properties'] = $properties; $results['dataSet'] = $data; $results['postData'] = $postData; return $results; }
function contract_attr($formulaId, $code, $year = '') { global $contractIdGlobal; global $yearGlobal; if ($year != '') { $year = $yearGlobal - 1; $sSQL = " SELECT a.FORMULA_VALUE FROM pd_contract_year a ,pd_contract_calculation b WHERE b.ID = a.CALCULATION_ID AND" . " a.CONTRACT_ID = " . $contractIdGlobal . " AND a.YEAR = '" . $year . "'" . " AND b.FORMULA_ID = '" . $formulaId . "'"; $pdContractYear = PdContractYear::getTableName(); $pdContractCalculation = PdContractCalculation::getTableName(); $contractYear = PdContractYear::join($pdContractCalculation, "{$pdContractYear}.CALCULATION_ID", '=', "{$pdContractCalculation}.ID")->where("{$pdContractYear}.CONTRACT_ID", '=', $contractIdGlobal)->where("{$pdContractYear}.YEAR", '=', $year)->where("{$pdContractCalculation}.FORMULA_ID", '=', $formulaId)->select("{$pdContractYear}.FORMULA_VALUE as ATTRIBUTE_VALUE")->first(); } else { $sSQL = " SELECT ATTRIBUTE_VALUE \n\t\t\t\tFROM pd_code_contract_attribute a\n\t\t\t\t,pd_contract_data b\n\t\t\t\tWHERE b.ATTRIBUTE_ID = a.ID AND" . " b.CONTRACT_ID = " . $contractIdGlobal . " \n\t\t\t\t\t\tAND a.CODE = '" . $code . "'"; $pdContractData = PdContractData::getTableName(); $pdCodeContractAttribute = PdCodeContractAttribute::getTableName(); $contractYear = PdCodeContractAttribute::join($pdContractData, "{$pdContractData}.ATTRIBUTE_ID", '=', "{$pdCodeContractAttribute}.ID")->where("{$pdCodeContractAttribute}.CODE", '=', $code)->where("{$pdContractData}.CONTRACT_ID", '=', $contractIdGlobal)->select("ATTRIBUTE_VALUE")->first(); } /* $result=mysql_query($sSQL) or die("fail: ".$sSQL."-> error:".mysql_error()); while($row=mysql_fetch_array($result)) { return $row['ATTRIBUTE_VALUE']; } */ if ($contractYear) { return $contractYear->ATTRIBUTE_VALUE; } return 0; }
public function calculate(Request $request) { $postData = $request->all(); $contract_id = $postData['contract_id']; $formula_id = $postData['PdContractQtyFormula']; $pdContractYear = PdContractYear::getTableName(); $pdContractCalculation = PdContractCalculation::getTableName(); $value = PdContractYear::join($pdContractCalculation, function ($query) use($pdContractYear, $pdContractCalculation, $formula_id) { $query->on("{$pdContractCalculation}.ID", '=', "{$pdContractYear}.CALCULATION_ID"); $query->where("{$pdContractCalculation}.FORMULA_ID", '=', $formula_id); })->where("{$pdContractYear}.CONTRACT_ID", $contract_id)->sum("{$pdContractYear}.FORMULA_VALUE"); return response()->json($value); }