public function addyear(Request $request) { $postData = $request->all(); $contractId = $postData['PdContract']; $year = $postData['year']; $qltyFormulas = PdContractQtyFormula::all(); $formulaValues = \FormulaHelpers::getDataFormulaContract($qltyFormulas, $contractId, $year); $resultTransaction = \DB::transaction(function () use($qltyFormulas, $formulaValues, $contractId, $year) { $attributes = ['CONTRACT_ID' => $contractId]; $yAttributes = ['CONTRACT_ID' => $contractId, 'YEAR' => $year]; $yValues = ['CONTRACT_ID' => $contractId, 'YEAR' => $year]; // PdContractYear::where($yAttributes)->delete(); foreach ($qltyFormulas as $key => $qltyFormula) { $attributes['FORMULA_ID'] = $qltyFormula->ID; $values = $attributes; $calculation = PdContractCalculation::updateOrCreate($attributes, $values); /* $sql = "INSERT INTO pd_contract_calculation(FORMULA_ID,CONTRACT_ID) " . "VALUE(".$aryRequest['FORMULA_ID'.$id].",".$aryRequest['CONTRACT_ID'].")"; */ $formulaValue = (int) $formulaValues[$qltyFormula->ID]; $yAttributes['CALCULATION_ID'] = $calculation->ID; $yValues['CALCULATION_ID'] = $calculation->ID; $yValues['FORMULA_VALUE'] = $formulaValue; $contractYear = PdContractYear::updateOrCreate($yAttributes, $yValues); /* $val = (int) $aryValue[$formulaId[$key]]; // abc($id,$contractId) se thay bang cong thuc $sql2 = "INSERT INTO pd_contract_year(CALCULATION_ID,YEAR,FORMULA_VALUE,CONTRACT_ID) VALUE($id,$year,'$val',$contractId)"; $sql2=str_replace("''", "NULL", $sql2); */ } }); $results = $this->loadData($contractId, $postData); return response()->json($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); }