Changeset 327 for trunk


Ignore:
Timestamp:
Dec 17, 2011, 3:10:03 PM (13 years ago)
Author:
chronos
Message:
  • Upraveno: Tabulka FinanceTariff přetvořena tak, aby se bylo možné odkazovat z jiných tabulek přes standardní primární klíč. Doposud se odkazovalo na sloupec Tariff což dělalo paseku. Překlápění nových tarifů se nyní provádní namísto sloupce Period pomocí sloupece ReplaceId, kde u nových položek se nastaví Id tarifu, který bude v novém měsící aktualizován. Id tarifů nyní musí začínat od jedničky.
Location:
trunk
Files:
1 added
5 edited

Legend:

Unmodified
Added
Removed

  • trunk/database.php

    r323 r327  
    101101    foreach($Data as $Key => $Value)
    102102    {
    103             if(!in_array($Value, $this->Functions)) $Value = $this->PDO->quote($Value);
     103      if(!in_array($Value, $this->Functions)) $Value = $this->PDO->quote($Value);
    104104      $Values .= ', `'.$Key.'`='.$Value;
    105105    }

  • trunk/finance/clenove.php

    r324 r327  
    1010  {
    1111    $Finance = $this->System->Modules['Finance'];
    12     $this->System->Modules['Finance']->LoadTariffs(1);
     12    $this->System->Modules['Finance']->LoadTariffs();
    1313    if(!$this->System->Modules['User']->CheckPermission('Finance', 'SubjectList')) return('Nemáte oprávnění');
    1414

  • trunk/finance/finance.php

    r301 r327  
    2525  var $BillingPeriods;
    2626 
    27   function LoadTariffs($Period = 1)
     27  function LoadTariffs()
    2828  {
    2929    $this->Tariffs = array();
    30     $DbResult = $this->Database->select('FinanceTariff', '*', 'Period='.$Period.' ORDER BY Tariff, SpeedMax');
     30    $DbResult = $this->Database->select('FinanceTariff', '*', 'ReplaceId IS NULL ORDER BY SpeedMax');
    3131    while($Tariff = $DbResult->fetch_array())
    3232    {
    3333      $Tariff['SpeedMin'] = $Tariff['SpeedMin'] * 1024;
    3434      $Tariff['SpeedMax'] = $Tariff['SpeedMax'] * 1024;
    35       $this->Tariffs[$Tariff['Tariff']] = $Tariff;
     35      $this->Tariffs[$Tariff['Id']] = $Tariff;
    3636    } 
    3737  }
    3838 
    39   function RecalculateTariffs($Period = 1)
     39  function RecalculateTariffs()
    4040  {
    4141    $ResidualSpeed = $this->MaxSpeed * 1024;
    4242 
    43     $this->LoadTariffs($Period);
     43    $this->LoadTariffs();
    4444   
    4545    $Column = array('Current', 'Next');
    … …  
    4848    foreach($this->Tariffs as $Index => $Tariff)
    4949    {
    50       $DbResult = $this->Database->select('Member', 'COUNT(*)', 'InternetTariff'.$Column[$Period].'Month='.$Index.' AND (BillingPeriod > 1)');
     50      $DbResult = $this->Database->select('Member', 'COUNT(*)', 'InternetTariffCurrentMonth='.$Index.' AND (BillingPeriod > 1)');
    5151      $Row = $DbResult->fetch_row();
    5252      $this->Tariffs[$Index]['MemberCount'] = $Row[0];
    … …  
    8585      }
    8686      //echo('MinSpeed: '.$Tariff['SpeedMin'].'<br />');
    87       $this->Database->update('FinanceTariff', 'Tariff='.$Tariff['Tariff'], array('SpeedMin' => ($Tariff['SpeedMin'] / 1024), 'MemberCount' => $Tariff['MemberCount']));
    88     }
    89     $this->LoadTariffs($Period);
     87      $this->Database->update('FinanceTariff', 'Id='.$Tariff['Id'], array('SpeedMin' => ($Tariff['SpeedMin'] / 1024), 'MemberCount' => $Tariff['MemberCount']));
     88    }
     89    $this->LoadTariffs();
    9090  }
    9191
    … …  
    174174      while($ID != 0)
    175175      {
    176         $DbResult2 = $this->Database->query('SELECT * FROM network_segments WHERE id='.$ID);
     176        $DbResult2 = $this->Database->query('SELECT * FROM `network_segments` WHERE `id`='.$ID);
    177177        $Device = $DbResult2->fetch_assoc();
    178178        $NetworkDevice += $Device['price'] / $Device['users'];
    … …  
    212212  {
    213213    $Output = 'Aktualizuji parametry segmentů...<br />';
    214     $this->Database->query('UPDATE network_segments SET users = 0, users_overheads = 0');     // Vynulovat počty uživatelů
    215     $DbResult = $this->Database->query('SELECT * FROM network_segments');
     214    $this->Database->query('UPDATE `network_segments` SET `users` = 0, `users_overheads` = 0');     // Vynulovat počty uživatelů
     215    $DbResult = $this->Database->query('SELECT * FROM `network_segments`');
    216216    while($NetworkSegment = $DbResult->fetch_array())
    217217    {
    218218      //echo('Segment '.$Row['name'].'<br>');
    219       $DbResult2 = $this->Database->query('SELECT users FROM network_segments WHERE id='.$NetworkSegment['id']);
     219      $DbResult2 = $this->Database->query('SELECT `users` FROM `network_segments` WHERE `id`='.$NetworkSegment['id']);
    220220      $RowP = $DbResult2->fetch_array();
    221       $DbResult2 = $this->Database->query('SELECT users_overheads FROM network_segments WHERE id='.$NetworkSegment['id']);
     221      $DbResult2 = $this->Database->query('SELECT `users_overheads` FROM `network_segments` WHERE `id`='.$NetworkSegment['id']);
    222222      $RowP2 = $DbResult2->fetch_array();
    223223

  • trunk/finance/manage.php

    r325 r327  
    6363        $Output .= '<a href="?Operation=NewPaymentForm">Přidat novou platbu</a><br />';
    6464        $Output .= '<a href="?Operation=NewInvoiceForm">Přidat novou fakturu</a><br />';
    65         //$Output .= '<a href="?Operation=ImportOldData">Importovat stará data</a><br />';
    6665        $Output .= '<a href="?Operation=ConvertPDFToFile">Převést data z databáze do souborů</a><br />';
    6766        $Output .= '<a href="?Operation=Bills">Správa dokladů</a><br />';
    68         //$Output .= '<a href="?Operation=ConvertData">Převést tabulky</a><br />';
    6967        $Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
    7068        $Output .= '<a href="?Operation=MonthlyPayment">Měsíční vyúčtování</a><br />';
    … …  
    243241    return($Output);
    244242  }
    245 
    246   /*
    247   function ImportOldData()
    248   {
    249     $Output = '';
    250     $this->Database->query('TRUNCATE TABLE FinanceCashFlow');
    251     $this->Database->query('TRUNCATE TABLE FinanceClaimsLiabilities');
    252     $this->Database->query('TRUNCATE TABLE FinanceAdvances');
    253     $this->Database->query('TRUNCATE TABLE FinanceSmallAssets');
    254 
    255     // Move time of device price transformation
    256     $this->Database->query('UPDATE finance_operations SET date="2007-11-30" WHERE comment = "Jednorázový poplatek za společné zařízení"');
    257 
    258     // Transfer finance before era
    259     $DbResult = $this->Database->query('SELECT * FROM Subject');
    260     while($Subject = $DbResult->fetch_assoc())
    261     {
    262       $DbResult2 = $this->Database->query('SELECT SUM(money) FROM finance_operations WHERE user='.$Subject['Id'].' AND date < "2007-12-01"');
    263       $Row = $DbResult2->fetch_row();
    264       $TotalAdvance = $Row[0];
    265       //echo($User['second_name'].' '.$User['first_name'].' '.$TotalAdvance.'<br />');
    266       // Záloha
    267       if($TotalAdvance > 0)
    268       {
    269         $this->InsertMoney($Subject['Id'], $TotalAdvance, 0, 1, mktime(0, 0, 0, 12, 1, 2007), 'Přijatá záloha (z období před daňovou evidencí)');
    270       }
    271       // Dluh
    272       if($TotalAdvance < 0)
    273       {
    274         $this->InsertLiability($Subject['Id'], (-$Row[0]), mktime(0, 0, 0, 12, 1, 2007), 0, 'Připojení k síti (z období před daňovou evidencí)');
    275         //echo($Database->LastQuery.'<br />');
    276       }
    277     }
    278 
    279     // Transfer finance after era
    280     //  $DbResult = $Database->query('SELECT * FROM users WHERE role=2');
    281     //  while($User = $DbResult->fetch_array())
    282     //  {
    283     $DbResult2 = $this->Database->query('SELECT finance_operations.* FROM finance_operations JOIN Subject ON Subject.Id = finance_operations.user WHERE finance_operations.date >= "2007-12-01"');
    284     while($Operation = $DbResult2->fetch_assoc())
    285     {
    286       //echo($Operation['comment'].'<br />');
    287       if(substr($Operation['comment'], 0, 19) == 'Poplatek za měsíc')
    288       {
    289         $this->InsertLiability($Operation['user'], -$Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], 'Připojení k síti');
    290         $Output .= $Operation['user'].' '.$Operation['money'].' Připojení k síti<br />';
    291       } else
    292       if($Operation['comment'] == 'Vklad')
    293       {
    294         $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), 'Přijatá záloha');
    295         //echo($Operation['user'].' '.$Operation['money'].' Přijatá záloha<br />');
    296       } else
    297       if($Operation['comment'] == 'Internet')
    298       {
    299         $this->InsertLiability($Operation['user'], $Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], 'Měsíční paušál za Internet', MysqlDateToTime($Operation['date']));
    300         $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), 'Měsíční paušál za Internet');
    301       } else
    302       if($Operation['comment'] == 'Sociální pojištění')
    303       {
    304         $this->InsertLiability($Operation['user'], $Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], $Operation['comment'], MysqlDateToTime($Operation['date']));
    305         $this->InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), $Operation['comment']);
    306       } else
    307       {
    308         $DbResult = $this->Database->query('SELECT Id FROM Subject WHERE Id='.$Operation['user']);
    309         while($User = $DbResult->fetch_array())
    310         $this->InsertLiability($Operation['user'], -$Operation['money'], MysqlDateToTime($Operation['date']), $Operation['bill_id'], $Operation['comment'], MysqlDateToTime($Operation['date']));
    311         $Output .= $Operation['user'].' '.$Operation['comment'].' '.MysqlDateToTime($Operation['date']).'<br />';
    312 
    313 //        if(($Operation['role'] == 2))
    314        // {
    315           $Output .= 'A';
    316           $this->Database->insert('FinanceAdvances', array('Subject' => $Operation['user'], 'Value' => $Operation['money'], 'TimeCreation' => MysqlDateToTime($Operation['date']), 'CashFlowId' => 0, 'Direction' => 'In'));
    317           $this->CheckAdvancesAndLiabilities($Operation['user']);
    318         //} else InsertMoney($Operation['user'], $Operation['money'], 0, 1, MysqlDateToTime($Operation['date']), $Operation['comment']);
    319       }
    320     }
    321     //  }
    322 
    323     // Import small asset
    324     $Subject = 1;
    325 
    326     // Převod pro minulý rok
    327     $Time = mktime(0, 0, 0, 12, 10, 2007);
    328     $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id <> 73)');
    329     $Items = array();
    330     $TotalPrice = 0;
    331     while($Device = $DbResult->fetch_array())
    332     {
    333       //$Database->insert('FinanceSmallAsset', array());
    334       $Items[] = array('Description' => $Device['name'], 'Quantity' => $Device['count'], 'Price' => 0); //$Device['price']);
    335       $LastId = $Device['id'];
    336       $TotalPrice += $Device['price'] * $Device['count'];
    337       if($TotalPrice > 55000) break;
    338     }
    339     //print_r($Items);
    340     $BillId = $this->System->Modules['Bill']->CreateBill($Subject, $Items, $Time, $Time);
    341     $DbResult = $this->Database->query('UPDATE network_devices SET TimeEnlistment = "'.TimeToMysqlDateTime($Time).'" WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id <> 73) AND (id <= '.$LastId.')');
    342     //echo($Database->error);
    343     $this->InsertLiability($Subject, 0, $Time, $BillId, 'Nákup infrastruktury', $Time);
    344     //InsertMoney($Subject, 0, 0, 1, $Time, 'Nákup infrastruktury');
    345 
    346     //echo($LastId);
    347 
    348     // Převod tento rok
    349     $Time = mktime(0, 0, 0, 1, 14, 2008);
    350     $DbResult = $this->Database->query('SELECT * FROM network_devices WHERE (used <> 0) AND (id > '.$LastId.') AND date < "'.TimeToMysqlDateTime($Time).'"');
    351     $Items = array();
    352     $TotalPrice = 0;
    353     while($Device = $DbResult->fetch_array())
    354     {
    355       // $Database->insert('FinanceSmallAsset', array());
    356       $Items[] = array('Description' => $Device['name'], 'Quantity' => $Device['count'], 'Price' => 0); //$Device['price']);
    357       $TotalPrice += $Device['price'] * $Device['count'];
    358     }
    359     //print_r($Items);
    360     $BillId = $this->System->Modules['Bill']->CreateBill($Subject, $Items, $Time, $Time);
    361     $DbResult = $this->Database->query('UPDATE network_devices SET TimeEnlistment = "'.TimeToMysqlDateTime($Time).'" WHERE used <> 0 AND date < "'.TimeToMysqlDateTime($Time).'" AND (id > '.$LastId.')');
    362     $this->InsertLiability($Subject, 0, $Time, $BillId, 'Nákup infrastruktury', $Time);
    363     //InsertMoney($Subject, 0, 0, 1, $Time, 'Nákup infrastruktury');
    364 
    365 
    366     // Make absolute value in monthly overall 
    367     //$Database->query('UPDATE finance_monthly_overall SET total_paid = ABS(total_paid)');
    368     //$Database->query('UPDATE finance_monthly_overall SET member_count = (SELECT COUNT(*) FROM users WHERE users.role=2 AND users.membership_date < finance_monthly_overall.date)');
    369     return($Output);
    370   }
    371   */
    372243 
    373244  function ConvertPDFDataToFiles()
    … …  
    392263    }
    393264  }
    394 
    395   /*
    396   function ConvertData()
    397   {
    398     $Finance = $this->System->Modules['Finance'];
    399     $Output = '';
    400     $this->Database->query('TRUNCATE TABLE FinanceOperation');
    401     $this->Database->query('TRUNCATE TABLE FinanceClaimsLiabilities');
    402 
    403     // Move time of device price transformation
    404     $this->Database->query('UPDATE finance_operations SET date="2007-11-30" WHERE comment = "Jednorázový poplatek za společné zařízení"');
    405 
    406     // Transform old operations
    407     $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Source = '.$Finance->ExternalSubject.')');
    408     while($DbRow = $DbResult->fetch_assoc())
    409     {
    410       $this->Database->insert('FinanceOperation', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Destination'], 'Cash' => $DbRow['cash'], 'Value' => abs($DbRow['money']), 'Time' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Taxable' => $DbRow['Taxable'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
    411       $Output .= '.';
    412     }
    413     $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Destination = '.$Finance->ExternalSubject.')');
    414     while($DbRow = $DbResult->fetch_assoc())
    415     {
    416       $this->Database->insert('FinanceOperation', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Source'], 'Cash' => $DbRow['cash'], 'Value' => -abs($DbRow['money']), 'Time' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Taxable' => $DbRow['Taxable'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
    417       $Output .= '.';
    418     }
    419 
    420     // Transform old invoices
    421     $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Source = '.$Finance->MainSubject.')');
    422     while($DbRow = $DbResult->fetch_assoc())
    423     {
    424       $this->Database->insert('FinanceClaimsLiabilities', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Destination'], 'Value' => -abs($DbRow['money']), 'TimeCreation' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
    425       $Output .= 'FV '.$DbRow['comment'].'<br />';
    426     }
    427     $DbResult = $this->Database->query('SELECT * FROM finance_operations WHERE finance_operations.date >= "2007-12-01" AND (Destination = '.$Finance->MainSubject.')');
    428     while($DbRow = $DbResult->fetch_assoc())
    429     {
    430       $this->Database->insert('FinanceClaimsLiabilities', array('Id' => $DbRow['id'], 'Subject' => $DbRow['Source'], 'Value' => abs($DbRow['money']), 'TimeCreation' => $DbRow['date'], 'BillCode' => $DbRow['BillCode'], 'Text' => $DbRow['comment'], 'Bill' => $DbRow['bill_id']));
    431       $Output .= 'FP '.$DbRow['comment'].'<br />';
    432     }
    433 
    434     // Transfer finance before era
    435     $DbResult = $this->Database->query('SELECT * FROM Subject');
    436     while($Subject = $DbResult->fetch_assoc())
    437     {
    438       $DbResult2 = $this->Database->query('SELECT SUM(money) as money FROM finance_operations WHERE user='.$Subject['Id'].' AND date < "2007-12-01"');
    439       $DbRow2 = $DbResult2->fetch_assoc();
    440 
    441       $DbRow2['date'] = TimeToMysqlDateTime(mktime(0, 0, 0, 12, 1, 2007));
    442       if($DbRow2['money'] > 0)
    443       {
    444         $Comment = 'Přijatá záloha (z období před daňovou evidencí)';
    445         $this->Database->insert('FinanceOperation', array('Subject' => $Subject['Id'], 'Cash' => 0, 'Value' => abs($DbRow2['money']), 'Time' => $DbRow2['date'], 'Taxable' => 1, 'Text' => $Comment));
    446 } else
    447       {
    448         $Comment = 'Připojení k síti (z období před daňovou evidencí)';
    449         $this->Database->insert('FinanceClaimsLiabilities', array('Subject' => $Subject['Id'], 'Value' => abs($DbRow2['money']), 'TimeCreation' => $DbRow2['date'], 'Text' => $Comment));
    450       }
    451       $Output .= '#';
    452     }
    453 
    454   return($Output);
    455   }
    456   */
    457 
    458265 
    459266  function GetBillingPeriod($Period)
    … …  
    523330
    524331      // Generuj účetní položky
    525       $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal, UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject ON Subject.Id=Member.Subject');
     332  /*    $DbResult = $this->Database->query('SELECT Member.*, MemberPayment.MonthlyTotal, UNIX_TIMESTAMP(Member.BillingPeriodLastDate), Subject.Name AS SubjectName FROM MemberPayment JOIN Member ON Member.Id=MemberPayment.Member JOIN Subject ON Subject.Id=Member.Subject');
    526333      while($Member = $DbResult->fetch_assoc())
    527334      {
    … …  
    561368      $Output .= 'Přidávám měsíční přehled...<br />';
    562369      $this->Database->insert('finance_monthly_overall', array('date' => 'NOW()', 'money' => $Finance->Internet, 'kWh' => $Finance->kWh, 'administration' => $Finance->Sprava, 'administration_total' => $SpravaCelkem, 'consumption_total' => $TotalConsumptionCost, 'total_paid' => $Finance->TotalPaid, 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice'], 'member_count' => $Finance->InternetUsers));
    563 
     370*/
     371      // Update tarrifs
    564372      $Output .= 'Měním aktuální tarify....<br>';
    565       // Update tarrifs
    566       $this->Database->delete('FinanceTariff', 'Period=0');
    567       $DbResult = $this->Database->select('FinanceTariff', '*', 'Period = 1');
     373      $DbResult = $this->Database->select('FinanceTariff', '*', '`ReplaceId` IS NOT NULL');
    568374      while($Tariff = $DbResult->fetch_array())
    569375      {
    570         $this->Database->insert('FinanceTariff', array('Period' => 0, 'Name' => $Tariff['Name'], 'Tariff' => $Tariff['Tariff'], 'UploadAsymmetry' => $Tariff['UploadAsymmetry'], 'MemberCount' => $Tariff['MemberCount'], 'Group' => $Tariff['Group'], 'SpeedMin' => $Tariff['SpeedMin'], 'SpeedMax' => $Tariff['SpeedMax'], 'Price' => $Tariff['Price']));
     376        $this->Database->update('FinanceTariff', 'Id='.$Tariff['ReplaceId'], array('Name' => $Tariff['Name'],
     377          'UploadAsymmetry' => $Tariff['UploadAsymmetry'], 'MemberCount' => $Tariff['MemberCount'],
     378          'Group' => $Tariff['Group'], 'SpeedMin' => $Tariff['SpeedMin'],
     379          'SpeedMax' => $Tariff['SpeedMax'], 'Price' => $Tariff['Price']));
    571380      }
     381      $this->Database->delete('FinanceTariff', '`ReplaceId` IS NOT NULL');
    572382
    573383      $Finance->RecalculateMemberPayment();

  • trunk/nbproject

    • Property svn:ignore set to
      private
Note: See TracChangeset for help on using the changeset viewer.