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