public function calcPayables($local_type, $local_ff, $local_id, $local_jahr, $local_prefix) { $counter = 0; //reset of the existing values -> filled with value in w_endwert default zero if values from former periods, they will be filled in foreach ($this->Perioden as $key => $value) { $my_jahr = $this->getYearByMonth($key); $current_field = "RLIQXXX" . $my_jahr . "XXX" . $key . "XXX" . $local_type . "XXX" . $local_ff . "XXX" . $local_id; $follow_value_record = WerteRecord::finder()->findBySql("SELECT w_endwert FROM tt_werte WHERE idtm_struktur = '" . $local_id . "' AND idta_feldfunktion = '" . $local_ff . "' AND w_jahr = '" . $my_jahr . "' AND w_monat = '" . $key . "' AND w_id_variante = '" . $this->GLOBALVARIANTE . "' LIMIT 1"); $this->page->ACTPanel->FindControl($current_field)->Text = number_format($follow_value_record->w_endwert, $this->NumberOfDigits, '.', ''); } //I need to reset the values for the following Period if ($this->ResetCalcpayables == 0) { $cleanyear = $my_jahr + 1; $ResetRecords = WerteRecord::finder()->findAllBySql("SELECT * FROM tt_werte WHERE idtm_struktur = '" . $local_id . "' AND idta_feldfunktion = '" . $local_ff . "' AND w_jahr = '" . $cleanyear . "' AND w_id_variante = '" . $this->GLOBALVARIANTE . "'"); foreach ($ResetRecords as $ResetRecord) { $ResetRecord->w_endwert = 0; $ResetRecord->save(); } $this->ResetCalcpayables++; } foreach ($this->Perioden as $key => $value) { $counter++; // hier erhoehen wir die info, dass die erste periode vorbei ist if ($key < 10000) { $my_jahr = $this->getYearByMonth($key); $current_field = "RLIQXXX" . $my_jahr . "XXX" . $key . "XXX" . $local_type . "XXX" . $local_ff . "XXX" . $local_id; $current_value = number_format($this->page->ACTPanel->FindControl($current_field)->Text, $this->NumberOfDigits, '.', ''); $days_for_factor = 360 / $this->getNumberPerIntern($my_jahr); //here we get the value of the field, that contains the value for the base factor $tresult = CollectorRecord::finder()->findBySql("SELECT col_idtafeldfunktion,col_operator FROM ta_collector INNER JOIN ta_feldfunktion ON ta_collector.col_idtafeldfunktion = ta_feldfunktion.idta_feldfunktion WHERE ta_collector.idta_feldfunktion = '" . $local_ff . "' AND ff_type='3' LIMIT 1"); //3 ist der struktursammler $base_field = "RLIQXXX" . $my_jahr . "XXX" . $key . "XXX" . $local_type . "XXX" . $tresult->col_idtafeldfunktion . "XXX" . $local_id; $base_value = number_format($this->page->ACTPanel->FindControl($base_field)->Text, $this->NumberOfDigits, '.', ''); $factor_per_day = $base_value / $days_for_factor; $ttresult = CollectorRecord::finder()->findBySql("SELECT col_idtafeldfunktion,col_operator FROM ta_collector INNER JOIN ta_feldfunktion ON ta_collector.col_idtafeldfunktion = ta_feldfunktion.idta_feldfunktion WHERE ta_collector.idta_feldfunktion = '" . $local_ff . "' AND ff_type<>'3' LIMIT 1"); //3 ist der struktursammler $day_field = "RLIQXXX" . $my_jahr . "XXX" . $key . "XXX" . $local_type . "XXX" . $ttresult->col_idtafeldfunktion . "XXX" . $local_id; $day_value = $this->page->ACTPanel->FindControl($day_field)->Text; $temp_compare = $day_value / $days_for_factor; $untergrenze = 0; //der untere laufer $obergrenze = 1; //der obere laufwert $monat = $key; for ($ii = 0; $ii < 10000; $ii++) { if ($temp_compare >= $untergrenze and $temp_compare < $obergrenze) { $tmpcurrent_field = "RLIQXXX" . $my_jahr . "XXX" . $monat . "XXX" . $local_type . "XXX" . $local_ff . "XXX" . $local_id; if ($this->page->ACTPanel->FindControl($tmpcurrent_field)) { $tmpcurrent_value = number_format($this->page->ACTPanel->FindControl($tmpcurrent_field)->Text, $this->NumberOfDigits, '.', ''); } else { $tmpcurrent_value = 0; } $faktor_periode = $obergrenze - $temp_compare; $valuefillin = $faktor_periode * $base_value; $counter == 1 ? '' : ($valuefillin += $tmpcurrent_value); $this->update_w_wert($my_jahr, $monat, $local_type, $local_ff, $local_id, $valuefillin); $this->page->ACTPanel->FindControl($tmpcurrent_field)->Text = number_format($valuefillin, $this->NumberOfDigits, '.', ''); if ($faktor_periode < 1 and $faktor_periode > 0) { $monat++; $target_year = $this->getYearByMonth($monat); //this is new because a value needs to be passed to the following year $valuefillin = (1 - $faktor_periode) * $base_value; $follow_field = "RLIQXXX" . $target_year . "XXX" . $monat . "XXX" . $local_type . "XXX" . $local_ff . "XXX" . $local_id; if ($target_year == $my_jahr) { $follow_value = number_format($this->page->ACTPanel->FindControl($follow_field)->Text, $this->NumberOfDigits, '.', ''); $counter == 1 ? '' : ($valuefillin += $follow_value); $this->update_w_wert($target_year, $monat, $local_type, $local_ff, $local_id, $valuefillin); $this->page->ACTPanel->FindControl($follow_field)->Text = number_format($valuefillin, $this->NumberOfDigits, '.', ''); break; } else { $follow_value_record = WerteRecord::finder()->findBySql("SELECT idtt_werte FROM tt_werte WHERE idtm_struktur = '" . $local_id . "' AND idta_feldfunktion = '" . $local_ff . "' AND w_jahr = '" . $target_year . "' AND w_monat = '" . $monat . "' AND w_id_variante = '" . $this->GLOBALVARIANTE . "' LIMIT 1"); if (count($follow_value_record) > 0) { $MyWerteRecord = WerteRecord::finder()->findByidtt_werte($follow_value_record->idtt_werte); $follow_value = $MyWerteRecord->w_endwert * 1; $MyWerteRecord->w_endwert = $valuefillin; $MyWerteRecord->save(); break; } else { $tmp = 0; $NEWWerteRecord = new WerteRecord(); $NEWWerteRecord->w_jahr = $target_year; $NEWWerteRecord->w_monat = $monat; $this->getInitialValue($local_ff, StrukturRecord::finder()->findByPK($local_id)->idtm_stammdaten, $monat, $this->GLOBALVARIANTE, $tmp); $NEWWerteRecord->w_wert = $tmp; $NEWWerteRecord->w_endwert = $valuefillin; $NEWWerteRecord->idta_feldfunktion = $local_ff; $NEWWerteRecord->idtm_struktur = $local_id; $NEWWerteRecord->w_id_variante = $this->GLOBALVARIANTE; $NEWWerteRecord->save(); break; } } } } $untergrenze++; $obergrenze++; $monat++; } $this->check_collector($local_ff, $current_field, $local_prefix, $key, $local_jahr, $local_id); } } $this->update_w_wert($local_jahr, $local_jahr, $local_type, $local_ff, $local_id, $this->sum_up($local_jahr, $local_jahr, $local_ff, $local_id)); $resultField = "RLIQXXX" . $local_jahr . "XXX" . $local_jahr . "XXX" . $local_type . "XXX" . $local_ff . "XXX" . $local_id; $this->page->ACTPanel->FindControl($resultField)->Text = number_format($this->sum_up($local_jahr, $local_jahr, $local_ff, $local_id), $this->NumberOfDigits, '.', ''); //hier muss die berechnung der erroeffnungsbilanz hin }
public function FCRunSeasonsButtonClicked($sender, $param) { //Step One, find all relevant IDs $FieldstToChange = array(); //inside this array, the fields that needed to be changed are listed $FieldsToKeep = array(); //inside this array, the fields that needed to be keept are listed $idta_variante = $this->Request['idta_variante']; $to_idta_variante = $this->for_idta_variante->Text; $this->load_ta_perioden($this->for_from_idta_periode->Text, $this->for_to_idta_periode->Text); $idta_struktur_type = FeldfunktionRecord::finder()->findByidta_feldfunktion($this->for_idta_feldfunktion->Text)->idta_struktur_type; $idta_feldfunktion = $this->for_idta_feldfunktion->Text; $FieldsToChange[] = $idta_feldfunktion; //this value needs always to be included $this->load_all_cats(); $SQLINCondition = $this->subCategory_Inlist($this->subcats, $this->for_idtm_struktur->Text); //the two must be replaced with the value from the usermanager $sql = "SELECT idtm_struktur FROM tm_struktur WHERE idtm_struktur IN (" . $SQLINCondition . ") AND idta_struktur_type = " . $idta_struktur_type; //here I recieve the array of values containing the elements to be changed $ElementsToChange = StrukturRecord::finder()->findAllBySQL($sql); //before the change can start, I need to identify the affected rows $FieldsToChangeBrutto = CollectorRecord::finder()->findAllBycol_idtafeldfunktion($idta_feldfunktion); foreach ($FieldsToChangeBrutto as $TmpField) { $FieldsToChange[] = $TmpField->idta_feldfunktion; } $FieldsToKeepBrutto = FeldfunktionRecord::finder()->findAllByidta_struktur_type($idta_struktur_type); foreach ($FieldsToKeepBrutto as $MyTmpField) { if (!in_array($MyTmpField->idta_feldfunktion, $FieldsToChange)) { $FieldsToKeep[] = $MyTmpField->idta_feldfunktion; } } foreach ($ElementsToChange as $Element) { foreach ($this->Perioden as $key => $value) { foreach ($FieldsToChange as $Field) { $year_idta_periode = $this->getYearByMonth($key); $sqlYEAR = "SELECT w_wert FROM tt_werte WHERE w_jahr = " . $year_idta_periode . " AND w_monat = " . $key . " AND idtm_struktur = " . $Element->idtm_struktur . " AND w_id_variante = " . $idta_variante . " AND idta_feldfunktion = " . $Field; $YEARValue = WerteRecord::finder()->findBySQL($sqlYEAR)->w_wert; $newValue = $YEARValue * (1 + $this->for_for_faktor->Text / 100); //$sqlMONTH = "SELECT w_wert FROM tt_werte WHERE w_jahr = ".$year_idta_periode." AND w_monat = ".PeriodenRecord::finder()->findByPK($Season->idta_periode)->per_intern." AND idtm_struktur = ".$Element->idtm_struktur." AND w_id_variante = ".$idta_variante." AND idta_feldfunktion = ".$Field; $year_to_idta_periode = $this->getYearByMonth($value); if (count(WerteRecord::finder()->find('w_jahr = ? AND w_monat = ? AND idtm_struktur = ? AND w_id_variante = ? AND idta_feldfunktion = ?', $year_to_idta_periode, $value, $Element->idtm_struktur, $to_idta_variante, $Field))) { $RecordToUpdate = WerteRecord::finder()->find('w_jahr = ? AND w_monat = ? AND idtm_struktur = ? AND w_id_variante = ? AND idta_feldfunktion = ?', $year_to_idta_periode, $value, $Element->idtm_struktur, $to_idta_variante, $Field); if ($RecordToUpdate->w_wert != $newValue) { $RecordToUpdate->w_wert = $newValue; $RecordToUpdate->save(); } } else { $RecordToWrite = new WerteRecord(); $RecordToWrite->w_jahr = $year_to_idta_periode; $RecordToWrite->w_monat = $value; $RecordToWrite->idtm_struktur = $Element->idtm_struktur; $RecordToWrite->w_id_variante = $to_idta_variante; $RecordToWrite->w_wert = $newValue; $RecordToWrite->idta_feldfunktion = $Field; $RecordToWrite->w_dimkey = $this->build_DIMKEY($Element->idtm_struktur); $RecordToWrite->save(); } } foreach ($FieldsToKeep as $Field) { $year_idta_periode = $this->getYearByMonth($key); $sqlYEAR = "SELECT w_wert FROM tt_werte WHERE w_jahr = " . $year_idta_periode . " AND w_monat = " . $key . " AND idtm_struktur = " . $Element->idtm_struktur . " AND w_id_variante = " . $idta_variante . " AND idta_feldfunktion = " . $Field; $YEARValue = WerteRecord::finder()->findBySQL($sqlYEAR)->w_wert; $newValue = $YEARValue; //$sqlMONTH = "SELECT w_wert FROM tt_werte WHERE w_jahr = ".$year_idta_periode." AND w_monat = ".PeriodenRecord::finder()->findByPK($Season->idta_periode)->per_intern." AND idtm_struktur = ".$Element->idtm_struktur." AND w_id_variante = ".$idta_variante." AND idta_feldfunktion = ".$Field; $year_to_idta_periode = $this->getYearByMonth($value); if (count(WerteRecord::finder()->find('w_jahr = ? AND w_monat = ? AND idtm_struktur = ? AND w_id_variante = ? AND idta_feldfunktion = ?', $year_to_idta_periode, $value, $Element->idtm_struktur, $to_idta_variante, $Field))) { $RecordToUpdate = WerteRecord::finder()->find('w_jahr = ? AND w_monat = ? AND idtm_struktur = ? AND w_id_variante = ? AND idta_feldfunktion = ?', $year_to_idta_periode, $value, $Element->idtm_struktur, $to_idta_variante, $Field); $RecordToUpdate->w_wert = $newValue; $RecordToUpdate->save(); } else { $RecordToWrite = new WerteRecord(); $RecordToWrite->w_jahr = $year_to_idta_periode; $RecordToWrite->w_monat = $value; $RecordToWrite->idtm_struktur = $Element->idtm_struktur; $RecordToWrite->w_id_variante = $to_idta_variante; $RecordToWrite->w_wert = $newValue; $RecordToWrite->idta_feldfunktion = $Field; $RecordToWrite->w_dimkey = $this->build_DIMKEY($Element->idtm_struktur); $RecordToWrite->save(); } } } } }
public function calcPayables($local_ff, $local_id, $local_jahr) { $counter = 0; //reset of the existing values -> filled with value in w_endwert default zero if values from former periods, they will be filled in foreach ($this->Perioden as $tmpPeriode) { $my_jahr = $this->getYearByMonth($tmpPeriode[0]); $follow_value_record = WerteRecord::finder()->findBySql("SELECT w_endwert FROM tt_werte WHERE idtm_struktur = '" . $local_id . "' AND idta_feldfunktion = '" . $local_ff . "' AND w_jahr = '" . $my_jahr . "' AND w_monat = '" . $tmpPeriode[0] . "' AND w_id_variante = '" . $this->GLOBALVARIANTE . "' LIMIT 1"); $this->update_w_wert($my_jahr, $tmpPeriode[0], $local_ff, $local_id, $follow_value_record->w_endwert); } //I need to reset the values for the following Period if ($this->ResetCalcpayables == 0) { $cleanyear = $my_jahr + 1; $ResetRecords = WerteRecord::finder()->findAllBySql("SELECT * FROM tt_werte WHERE idtm_struktur = '" . $local_id . "' AND idta_feldfunktion = '" . $local_ff . "' AND w_jahr = '" . $cleanyear . "' AND w_id_variante = '" . $this->GLOBALVARIANTE . "'"); foreach ($ResetRecords as $ResetRecord) { $ResetRecord->w_endwert = 0; $ResetRecord->save(); } $this->ResetCalcpayables++; } foreach ($this->Perioden as $tmpPeriode) { $counter++; // hier erhoehen wir die info, dass die erste periode vorbei ist if ($tmpPeriode[0] < 10000) { $key = $tmpPeriode[0]; $my_jahr = $this->getYearByMonth($tmpPeriode[0]); $CurrentString = $my_jahr . "xxx" . $tmpPeriode[0] . "xxx" . $local_ff . "xxx" . $local_id; $current_value = $this->TTWERTE[$CurrentString]; $days_for_factor = 360 / $this->getNumberPerIntern($my_jahr); //DB: 30 echo $days_for_factor; //here we get the value of the field, that contains the value for the base factor $tresult = CollectorRecord::finder()->findBySql("SELECT col_idtafeldfunktion,col_operator FROM ta_collector INNER JOIN ta_feldfunktion ON ta_collector.col_idtafeldfunktion = ta_feldfunktion.idta_feldfunktion WHERE ta_collector.idta_feldfunktion = '" . $local_ff . "' AND ff_type='3' LIMIT 1"); //3 ist der struktursammler $BaseString = $my_jahr . "xxx" . $tmpPeriode[0] . "xxx" . $tresult->col_idtafeldfunktion . "xxx" . $local_id; $base_value = $this->TTWERTE[$BaseString]; $factor_per_day = $base_value / $days_for_factor; $ttresult = CollectorRecord::finder()->findBySql("SELECT col_idtafeldfunktion,col_operator FROM ta_collector INNER JOIN ta_feldfunktion ON ta_collector.col_idtafeldfunktion = ta_feldfunktion.idta_feldfunktion WHERE ta_collector.idta_feldfunktion = '" . $local_ff . "' AND ff_type<>'3' LIMIT 1"); //3 ist der struktursammler $DayString = $my_jahr . "xxx" . $tmpPeriode[0] . "xxx" . $ttresult->col_idtafeldfunktion . "xxx" . $local_id; $day_value = $this->TTWERTE[$DayString]; $temp_compare = $day_value / $days_for_factor; $untergrenze = 0; //der untere laufer $obergrenze = 1; //der obere laufwert $monat = $tmpPeriode[0]; //print_r("O:".$obergrenze."U:".$untergrenze."V:".$temp_compare); for ($ii = 0; $ii <= count($this->Perioden); $ii++) { if ($temp_compare >= $untergrenze and $temp_compare < $obergrenze) { $TmpCurrentString = $my_jahr . "xxx" . $monat . "xxx" . $local_ff . "xxx" . $local_id; if (isset($this->TTWERTE[$TmpCurrentString])) { $tmpcurrent_value = $this->TTWERTE[$TmpCurrentString]; } else { $tmpcurrent_value = 0; } $faktor_periode = $obergrenze - $temp_compare; $valuefillin = $faktor_periode * $base_value; $counter == 1 ? '' : ($valuefillin += $tmpcurrent_value); $this->update_w_wert($my_jahr, $monat, $local_ff, $local_id, $valuefillin); if ($faktor_periode < 1 and $faktor_periode > 0) { $monat++; $target_year = $this->getYearByMonth($monat); //this is new because a value needs to be passed to the following year $valuefillin = (1 - $faktor_periode) * $base_value; $FollowString = $target_year . "xxx" . $monat . "xxx" . $local_ff . "xxx" . $local_id; if ($target_year == $my_jahr) { $follow_value = $this->TTWERTE[$FollowString]; $counter == 1 ? '' : ($valuefillin += $follow_value); $this->update_w_wert($my_jahr, $monat, $local_ff, $local_id, $valuefillin); break; } else { $follow_value_record = WerteRecord::finder()->findBySql("SELECT idtt_werte FROM tt_werte WHERE idtm_struktur = '" . $local_id . "' AND idta_feldfunktion = '" . $local_ff . "' AND w_jahr = '" . $target_year . "' AND w_monat = '" . $monat . "' AND w_id_variante = '" . $this->GLOBALVARIANTE . "' LIMIT 1"); if (count($follow_value_record) > 0) { $MyWerteRecord = WerteRecord::finder()->findByidtt_werte($follow_value_record->idtt_werte); $follow_value = $MyWerteRecord->w_endwert * 1; $MyWerteRecord->w_endwert = $valuefillin; $MyWerteRecord->save(); break; } else { $tmp = 0; $NEWWerteRecord = new WerteRecord(); $NEWWerteRecord->w_jahr = $target_year; $NEWWerteRecord->w_monat = $monat; $this->getInitialValue($local_ff, StrukturRecord::finder()->findByPK($local_id)->idtm_stammdaten, $monat, $this->GLOBALVARIANTE, $tmp); $NEWWerteRecord->w_wert = $tmp; $NEWWerteRecord->w_endwert = $valuefillin; $NEWWerteRecord->idta_feldfunktion = $local_ff; $NEWWerteRecord->idtm_struktur = $local_id; $NEWWerteRecord->w_id_variante = $this->GLOBALVARIANTE; $NEWWerteRecord->save(); break; } } } } $untergrenze++; $obergrenze++; $monat++; } $this->check_collector($local_ff, $key, $local_jahr, $local_id); } } //echo "Sp6x"; $this->update_w_wert($local_jahr, $local_jahr, $local_ff, $local_id, $this->sum_up($local_jahr, $local_jahr, $local_ff, $local_id)); }