Changeset 726


Ignore:
Timestamp:
Jan 5, 2015, 10:01:40 PM (10 years ago)
Author:
chronos
Message:
  • Modified: Added time designation for scheduled table change actions. This applies to tables Service, ServiceCustomerRel and FinanceCharge. Now time of taking effect is not determined just by customer pay period but by more generally by specified time.
Location:
trunk
Files:
7 edited

Legend:

Unmodified
Added
Removed
  • trunk/Application/Version.php

    r725 r726  
    11<?php
    22
    3 $Revision = 725; // Subversion revision
    4 $DatabaseRevision = 725; // SQL structure revision
    5 $ReleaseTime = strtotime('2015-01-04');
     3$Revision = 726; // Subversion revision
     4$DatabaseRevision = 726; // SQL structure revision
     5$ReleaseTime = strtotime('2015-01-05');
  • trunk/Common/Setup/Updates.php

    r725 r726  
    10331033}
    10341034
    1035 /*
    1036 function UpdateTo719($Manager)
    1037 {       
    1038   $Manager->Execute('ALTER TABLE `FinanceOperation` DROP FOREIGN KEY `FinanceOperation_ibfk_1`;');
    1039   $Manager->Execute('ALTER TABLE `FinanceOperation` CHANGE `Subject` `SubjectFrom` INT(11) NULL DEFAULT "";');
    1040   $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `SubjectTo` INT NULL AFTER `SubjectFrom`, ADD INDEX (`SubjectTo`) ;');
    1041   $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY (`SubjectFrom`) REFERENCES `Subject`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
    1042   $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY (`SubjectTo`) REFERENCES `Subject`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
    1043   // Set target subject to 1 = company subject id
    1044   $Manager->Execute('UPDATE `FinanceOperation` SET `SubjectTo` = 1;');
    1045   $Manager->Execute('UPDATE `FinanceOperation` SET `SubjectTo` = `SubjectFrom` WHERE `Value` < 0');
    1046   $Manager->Execute('UPDATE `FinanceOperation` SET `SubjectFrom` = 1,`Value`= -`Value`  WHERE `Value` < 0');
    1047    
    1048 }
    1049 */
    1050 
    10511035function UpdateTo719($Manager)
    10521036{       
     
    11631147        }
    11641148        $DbResult = $Manager->Execute('UPDATE `FinanceInvoiceItem` SET `Description` = "'.$SearchText.'" WHERE `Description` LIKE "'.$SearchText.' za období%";');
     1149}
     1150
     1151function UpdateTo726($Manager)
     1152{
     1153        $Manager->Execute('ALTER TABLE `ServiceCustomerRel` CHANGE `Action` `ChangeAction` ENUM("add","modify","remove") CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;');
     1154        $Manager->Execute('ALTER TABLE `ServiceCustomerRel` ADD `ChangeTime` DATETIME NULL AFTER `ChangeAction`;');
     1155        $Manager->Execute('ALTER TABLE `ServiceCustomerRel` DROP FOREIGN KEY `ServiceCustomerRel_ibfk_4`;');
     1156        $Manager->Execute('ALTER TABLE `ServiceCustomerRel` CHANGE `ReplaceId` `ChangeReplaceId` INT(11) NULL DEFAULT NULL;');
     1157        $Manager->Execute('ALTER TABLE `ServiceCustomerRel` ADD FOREIGN KEY (`ChangeReplaceId`) REFERENCES `ServiceCustomerRel`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
     1158
     1159        $Manager->Execute('ALTER TABLE `Service` CHANGE `Action` `ChangeAction` ENUM("add","modify","remove") CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;');
     1160        $Manager->Execute('ALTER TABLE `Service` ADD `ChangeTime` DATETIME NULL AFTER `ChangeAction`;');
     1161        $Manager->Execute('ALTER TABLE `Service` DROP FOREIGN KEY `Service_ibfk_2`;');
     1162        $Manager->Execute('ALTER TABLE `Service` CHANGE `ReplaceId` `ChangeReplaceId` INT(11) NULL DEFAULT NULL;');
     1163        $Manager->Execute('ALTER TABLE `Service` ADD FOREIGN KEY (`ChangeReplaceId`) REFERENCES `Service`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
     1164
     1165        $Manager->Execute('ALTER TABLE `FinanceCharge` CHANGE `Action` `ChangeAction` ENUM("add","modify","remove") CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;');
     1166        $Manager->Execute('ALTER TABLE `FinanceCharge` ADD `ChangeTime` DATETIME NULL AFTER `ChangeAction`;');
     1167        $Manager->Execute('ALTER TABLE `FinanceCharge` DROP FOREIGN KEY `FinanceCharge_ibfk_1`;');
     1168        $Manager->Execute('ALTER TABLE `FinanceCharge` CHANGE `ReplaceId` `ChangeReplaceId` INT(11) NULL DEFAULT NULL;');
     1169        $Manager->Execute('ALTER TABLE `FinanceCharge` ADD FOREIGN KEY (`ChangeReplaceId`) REFERENCES `FinanceCharge`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
     1170       
     1171        $Manager->Execute('ALTER TABLE `MemberPayment` DROP `NetworkDevice`;');
    11651172}
    11661173
     
    12251232        720 => array('Revision' => 722, 'Function' => 'UpdateTo722'),
    12261233        722 => array('Revision' => 725, 'Function' => 'UpdateTo725'),
     1234        725 => array('Revision' => 726, 'Function' => 'UpdateTo726'),
    12271235    ));
    12281236  }
  • trunk/Modules/Customer/Customer.php

    r723 r726  
    8282                'Storage' => array('Type' => 'Integer', 'Caption' => 'Úložiště', 'Default' => '0', 'Suffix' => 'GB'),
    8383                'CPUCount' => array('Type' => 'Integer', 'Caption' => 'Počet jader', 'Default' => '0', 'Suffix' => ''),
    84                 'Action' => array('Type' => 'TActionEnum', 'Caption' => 'Změna období', 'Default' => '', 'Null' => true),
    85                 'ReplaceId' => array('Type' => 'TService', 'Caption' => 'Cílová položka', 'Default' => '', 'Null' => true),
     84                'ChangeAction' => array('Type' => 'TActionEnum', 'Caption' => 'Změna - akce', 'Default' => '', 'Null' => true),
     85                'ChangeTime' => array('Type' => 'DateTime', 'Caption' => 'Změna - čas', 'Default' => '', 'Null' => true),
     86                'ChangeReplaceId' => array('Type' => 'TService', 'Caption' => 'Změna - položka', 'Default' => '', 'Null' => true),
    8687                'CustomerRel' => array('Type' => 'TServiceCustomerRelListService', 'Caption' => 'Placení zákazníky', 'Default' => ''),
    8788        ),
     
    101102        'Service' => array('Type' => 'TService', 'Caption' => 'Služba', 'Default' => ''),
    102103        'Customer' => array('Type' => 'TMember', 'Caption' => 'Zákazník', 'Default' => ''),
    103         'Action' => array('Type' => 'TActionEnum', 'Caption' => 'Změna období', 'Default' => '', 'Null' => true),
    104         'ReplaceId' => array('Type' => 'TServiceCustomerRel', 'Caption' => 'Cílová položka', 'Default' => '', 'Null' => true),
     104        'ChangeAction' => array('Type' => 'TActionEnum', 'Caption' => 'Změna - akce', 'Default' => '', 'Null' => true),
     105                'ChangeTime' => array('Type' => 'DateTime', 'Caption' => 'Změna - čas', 'Default' => '', 'Null' => true),
     106        'ChangeReplaceId' => array('Type' => 'TServiceCustomerRel', 'Caption' => 'Změna - položka', 'Default' => '', 'Null' => true),
    105107      ),
    106108    ));
  • trunk/Modules/Finance/Finance.php

    r725 r726  
    4646
    4747    // Period parameter is not used as it have to be determined from item replacement
    48     $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE `ReplaceId` IS NULL LIMIT 1');
     48    $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1');
    4949    $Row = $DbResult->fetch_array();
    5050    $this->kWh = $Row['kWh'];
     
    115115    while($Member = $DbResult->fetch_assoc())
    116116    {
    117       $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(-SUM(Value*Direction), 0) FROM FinanceOperation '.
    118           'WHERE Subject='.$Member['Subject'].') + (SELECT COALESCE(-SUM(Value*Direction), 0) FROM FinanceInvoice '.
     117      $DbResult2 = $this->Database->query('SELECT ((SELECT COALESCE(SUM(Value*Direction), 0) FROM FinanceOperation '.
     118          'WHERE Subject='.$Member['Subject'].') - (SELECT COALESCE(SUM(Value*Direction), 0) FROM FinanceInvoice '.
    119119          'WHERE Subject='.$Member['Subject'].')) AS Cash');
    120120      $Cash = $DbResult2->fetch_row();
     
    129129      $DbResult2 = $this->Database->query('SELECT SUM(`Service`.`Price`) AS `Price` '.
    130130          'FROM `ServiceCustomerRel` LEFT JOIN '.
    131           '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`='.
    132           $Member['Id'].' AND `ServiceCustomerRel`.`Action` IS NULL');
     131          '`Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` WHERE (`ServiceCustomerRel`.`Customer`='.
     132          $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
    133133      $DbRow = $DbResult2->fetch_assoc();
    134134      $Monthly = 0;
     
    149149      }
    150150      $this->Database->insert('MemberPayment', array('Member' => $Member['Id'],
    151           'NetworkDevice' => $NetworkDevice, 'MonthlyInternet' => $MonthlyInet,
     151          'MonthlyInternet' => $MonthlyInet,
    152152          'MonthlyTotal' => $Monthly, 'MonthlyConsumption' => $this->W2Kc($Consumption),
    153153          'Cash' => $Cash, 'MonthlyPlus' => $this->W2Kc($ConsumptionPlus)));
     
    430430        'BaseTariffPrice' => array('Type' => 'Integer', 'Caption' => 'Základní cena tarifu', 'Default' => '0', 'Suffix' => 'Kč'),
    431431        'TopTariffPrice' => array('Type' => 'Integer', 'Caption' => 'Nejvyšší cena tarifu', 'Default' => '0', 'Suffix' => 'Kč'),
    432         'Action' => array('Type' => 'TActionEnum', 'Caption' => 'Změna období', 'Default' => '', 'Null' => true),
    433         'ReplaceId' => array('Type' => 'TFinanceCharge', 'Caption' => 'Cílová položka', 'Default' => '0', 'Null' => true),
     432        'ChangeAction' => array('Type' => 'TActionEnum', 'Caption' => 'Změna - akce', 'Default' => '', 'Null' => true),
     433                'ChangeTime' => array('Type' => 'DateTime', 'Caption' => 'Změna - čas', 'Default' => '', 'Null' => true),
     434        'ChangeReplaceId' => array('Type' => 'TFinanceCharge', 'Caption' => 'Změna - položka', 'Default' => '0', 'Null' => true),
    434435      ),
    435436    ));
  • trunk/Modules/Finance/Manage.php

    r724 r726  
    5454      '(SELECT GROUP_CONCAT(`Service`.`Name`) FROM `ServiceCustomerRel` LEFT JOIN `Service` '.
    5555      'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` WHERE `ServiceCustomerRel`.`Customer`=`Member`.`Id` AND '.
    56       '`ServiceCustomerRel`.`Action` IS NULL) AS `ServicesNextMonth`, '.
     56      '`ServiceCustomerRel`.`ChangeAction` IS NULL) AS `ServicesNextMonth`, '.
    5757      'UNIX_TIMESTAMP(`Member`.`BillingPeriodLastDate`) AS `LastDate`, `Subject`.`Name` AS `SubjectName`, '.
    5858      '`FinanceBillingPeriod`.`Name` AS `BillingPeriodName` '.
     
    156156          'ON `Service`.`Id`=`ServiceCustomerRel`.`Service` '.
    157157          'WHERE (`ServiceCustomerRel`.`Customer`='.
    158           $Member['Id'].') AND (`ServiceCustomerRel`.`Action` IS NULL) ');
     158          $Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL) ');
    159159        while($Service = $DbResult2->fetch_assoc())
    160160        {
     
    192192  }
    193193
    194   function TableUpdatePeriod($Table)
    195   {
    196     $DbResult = $this->Database->select($Table, '*', '`Action` IS NOT NULL');
     194  function TableUpdateChanges($Table)
     195  {
     196        $Time = time();
     197    $DbResult = $this->Database->select($Table, '*', '(`ChangeAction` IS NOT NULL) AND '.
     198        '(`ChangeTime` <= "'.TimeToMysqlDateTime($Time).'") ORDER BY `ChangeTime` ASC');
    197199    while($Service = $DbResult->fetch_array())
    198200    {
    199       if($Service['Action'] == 'add')
     201      if($Service['ChangeAction'] == 'add')
    200202      {
    201203        unset($Service['Id']);
    202         unset($Service['ReplaceId']);
    203         unset($Service['Action']);
     204        unset($Service['ChangeReplaceId']);
     205        unset($Service['ChangeAction']);
    204206        $this->Database->insert($Table, $Service);
    205207      } else
    206       if($Service['Action'] == 'modify')
     208      if($Service['ChangeAction'] == 'modify')
    207209      {
    208210        unset($Service['Id']);
    209         unset($Service['Action']);
    210         $ReplaceId = $Service['ReplaceId'];
    211         unset($Service['ReplaceId']);
     211        unset($Service['ChangeAction']);
     212        $ReplaceId = $Service['ChangeReplaceId'];
     213        unset($Service['ChangeReplaceId']);
    212214        $this->Database->update($Table, 'Id='.$ReplaceId, $Service);
    213215      } else
    214       if($Service['Action'] == 'delete')
     216      if($Service['ChangeAction'] == 'delete')
    215217      {
    216218        $this->Database->delete($Table, '`Id`='.$Service['ReplaceId']);
    217219      }
    218220    }
    219     $this->Database->delete($Table, '`Action` IS NOT NULL');
    220   }
    221 
     221    $this->Database->delete($Table, '(`ChangeAction` IS NOT NULL) AND (`ChangeTime` <= "'.TimeToMysqlDateTime($Time).'")');
     222  }
     223
     224  function ProcessTableUpdates()
     225  {
     226        // Update finance charge
     227        $Output = 'Měním aktuální parametry sítě...<br>';
     228        $this->TableUpdateChanges('FinanceCharge');
     229       
     230        // Update services
     231        $Output .= 'Aktualizuji služby....<br>';
     232        $this->TableUpdateChanges('Service');
     233       
     234        // Update customer service selections
     235        $Output .= 'Aktualizuji výběr služeb zákazníků....<br>';
     236        $this->TableUpdateChanges('ServiceCustomerRel');
     237
     238        return($Output);
     239  }
     240 
    222241  function ProcessMonthlyPayment()
    223242  {
     
    225244    $Output = '';
    226245
     246    $Output .= $this->ProcessTableUpdates();
     247   
    227248    $Finance = &$this->System->Modules['Finance'];
    228249    $Finance->LoadMonthParameters(0);
    229250
    230251    // Načti poslední měsíční přehled a nastavení
    231     $DbResult = $this->Database->select('FinanceMonthlyOverall', '*', '1 ORDER BY Date DESC LIMIT 1');
     252    $DbResult = $this->Database->select('FinanceMonthlyOverall', '*', '1 ORDER BY `Date` DESC LIMIT 1');
    232253    $Overall = $DbResult->fetch_array();
    233254
    234     $Output -= 'Datum: '.date('j.n.Y').'<br />';
     255    $Output .= 'Datum: '.date('j.n.Y').'<br />';
    235256
    236257    $DateParts = explode('-', $Overall['Date']);
     
    240261    $Output .= $Finance->RecalculateMemberPayment();
    241262
    242     $DbResult = $this->Database->query('SELECT SUM(NetworkDevice) FROM MemberPayment');
    243     $Row = $DbResult->fetch_row();
    244     $TotalMemberDeviceCost = $Row[0];
    245     $Output .= '), Členové('.$TotalMemberDeviceCost.')<br />';
    246 
    247     $DbResult = $this->Database->query('SELECT SUM(Cash) FROM MemberPayment');
     263    $DbResult = $this->Database->query('SELECT SUM(`Cash`) FROM `MemberPayment`');
    248264    $Row = $DbResult->fetch_row();
    249265    $TotalMemberCash = $Row[0];
    250266    $Output .= 'Stav pokladny: Členové('.$TotalMemberCash.')';
    251267
    252     $DbResult = $this->Database->query('SELECT SUM(Product.Consumption) AS Consumption FROM StockSerialNumber '.
    253       'JOIN Product ON StockSerialNumber.Product = Product.Id WHERE (StockSerialNumber.TimeElimination IS NULL) '.
    254         'AND (StockSerialNumber.Segment IS NOT NULL)');
     268    $DbResult = $this->Database->query('SELECT SUM(`Product`.`Consumption`) AS `Consumption` FROM `StockSerialNumber` '.
     269      'JOIN `Product` ON `StockSerialNumber`.`Product` = `Product`.`Id` WHERE (`StockSerialNumber`.`TimeElimination` IS NULL) ');
    255270    $Row = $DbResult->fetch_row();
    256271    $TotalConsumption = $Row[0];
     
    266281      $Output .= 'Odečítám měsíční poplatek...<br />';
    267282      $Output .= $this->ProduceInvoices();
    268 
    269       // Update finance charge
    270       $Output .= 'Měním aktuální parametry sítě...<br>';
    271       $this->TableUpdatePeriod('FinanceCharge');
    272 
     283     
    273284      $Output .= 'Přidávám měsíční přehled...<br />';
    274       $DbResult = $this->Database->query('SELECT * FROM FinanceCharge WHERE Action IS NULL LIMIT 1');
     285      $DbResult = $this->Database->query('SELECT * FROM `FinanceCharge` WHERE (`ChangeAction` IS NULL) LIMIT 1');
    275286      $Charge = $DbResult->fetch_assoc();
    276287      $this->Database->insert('FinanceMonthlyOverall', array('Date' => 'NOW()',
     
    281292        'TopTariffPrice' => $Charge['TopTariffPrice'], 'MemberCount' => $Finance->InternetUsers));
    282293
    283       // Update services
    284       $Output .= 'Aktualizuji služby....<br>';
    285       $this->TableUpdatePeriod('Service');
    286      
    287       // Update customer service selections
    288       $Output .= 'Aktualizuji výběr služeb zákazníků....<br>';
    289       $this->TableUpdatePeriod('ServiceCustomerRel');
    290 
    291294      $Finance->RecalculateMemberPayment();
    292295      //CreateMonthlyOverallBill();
     
    335338      $DbResult = $this->Database->query('SELECT GROUP_CONCAT(Service.Name) AS Name FROM ServiceCustomerRel LEFT JOIN Service '.
    336339        'ON Service.Id=ServiceCustomerRel.Service WHERE (ServiceCustomerRel.Customer='.$Member['Id'].') '.
    337         'AND ServiceCustomerRel.Action IS NULL');
     340        'AND ServiceCustomerRel.ChangeAction IS NULL');
    338341      $Service = $DbResult->fetch_assoc();
    339342      $Content .= '<strong>'.$Service['Name'].'</strong><br />'."\n".
  • trunk/Modules/Finance/UserState.php

    r724 r726  
    99  function ShowFinanceOperation($Subject)
    1010  {
    11         $UserOperationTableQuery = '((SELECT Text, Time, (Value*Direction) AS Value, File, BillCode, NULL AS PeriodFrom, NULL AS PeriodTo '.
    12           'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
    13                 '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
    14                 'Time, -(Value*Direction) as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')))';
     11        $UserOperationTableQuery = '((SELECT `Text`, `Time`, (`Value`*`Direction`) AS `Value`, `File`, `BillCode`, NULL AS `PeriodFrom`, NULL AS `PeriodTo` '.
     12          'FROM `FinanceOperation` WHERE (`Subject`='.$Subject['Id'].')) UNION ALL '.
     13                '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ",") FROM `FinanceInvoiceItem` WHERE `FinanceInvoice`=`FinanceInvoice`.`Id`) AS `Text`, '.
     14                '`Time`, -(`Value`*`Direction`) AS `Value`, `File`, `BillCode`, `PeriodFrom`, `PeriodTo` FROM `FinanceInvoice` WHERE (`Subject`='.$Subject['Id'].')))';
    1515
    1616    $Output = '<div style="text-align:center">Výpis finančních operací</div>';
    17         $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$UserOperationTableQuery.' AS T1');
     17        $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$UserOperationTableQuery.' AS `T1`');
    1818        $DbRow = $DbResult->fetch_row();
    1919        $PageList = GetPageList($DbRow[0]);
     
    3333        $Output .= $Order['Output'];
    3434
    35         $StateQuery = 'SELECT SUM(T2.Value) FROM '.$UserOperationTableQuery.
    36           ' AS T2 WHERE T2.Time <= T1.Time ';
    37         $Query = 'SELECT *, ('.$StateQuery.') AS State FROM '.$UserOperationTableQuery.' AS T1 '.$Order['SQL'].$PageList['SQLLimit'];
     35        $StateQuery = 'SELECT SUM(`T2`.`Value`) FROM '.$UserOperationTableQuery.
     36          ' AS `T2` WHERE `T2`.`Time` <= `T1`.`Time` ';
     37        $Query = 'SELECT *, ('.$StateQuery.') AS `State` FROM '.$UserOperationTableQuery.' AS `T1` '.$Order['SQL'].$PageList['SQLLimit'];
    3838
    3939        $DbResult = $this->Database->query($Query);
     
    7878      if(!$this->System->User->CheckPermission('Finance', 'DisplaySubjectState')) return('Nemáte oprávnění');
    7979      $UserId = $this->System->User->User['Id'];
    80       $DbResult = $this->Database->query('SELECT Customer FROM UserCustomerRel WHERE User='.$UserId.' LIMIT 1');
     80      $DbResult = $this->Database->query('SELECT `Customer` FROM `UserCustomerRel` WHERE `User`='.$UserId.' LIMIT 1');
    8181      if($DbResult->num_rows > 0)
    8282      {
     
    8787       
    8888    // Load customer info
    89     $DbResult = $this->Database->query('SELECT * FROM Member WHERE Id='.$CustomerId);
     89    $DbResult = $this->Database->query('SELECT * FROM `Member` WHERE `Id`='.$CustomerId);
    9090    if($DbResult->num_rows == 1)
    9191    {
     
    9595   
    9696    // Load subject info
    97     $DbResult = $this->Database->query('SELECT * FROM Subject WHERE Id='.$Customer['Subject']);
     97    $DbResult = $this->Database->query('SELECT * FROM `Subject` WHERE `Id`='.$Customer['Subject']);
    9898    if($DbResult->num_rows == 1)
    9999    {   
     
    105105
    106106    // Account state
    107     $UserOperationTableQuery = '((SELECT Text, Time, (Value*Direction) AS Value, File, BillCode, NULL AS PeriodFrom, NULL AS PeriodTo '.
    108       'FROM FinanceOperation WHERE (Subject='.$Subject['Id'].')) UNION ALL '.
    109       '(SELECT (SELECT GROUP_CONCAT(Description SEPARATOR ",") FROM FinanceInvoiceItem WHERE FinanceInvoice=FinanceInvoice.Id) AS Text, '.
    110       'Time, -(Value*Direction) as Value, File, BillCode, PeriodFrom, PeriodTo FROM FinanceInvoice WHERE (Subject='.$Subject['Id'].')) ORDER BY Time DESC) AS T1';
    111     $DbResult = $this->Database->query('SELECT SUM(T1.Value) AS Total FROM '.$UserOperationTableQuery);
     107    $UserOperationTableQuery = '((SELECT `Text`, `Time`, (`Value`*`Direction`) AS `Value`, `File`, `BillCode`, NULL AS `PeriodFrom`, NULL AS `PeriodTo` '.
     108      'FROM `FinanceOperation` WHERE (`Subject`='.$Subject['Id'].')) UNION ALL '.
     109      '(SELECT (SELECT GROUP_CONCAT(`Description` SEPARATOR ",") FROM `FinanceInvoiceItem` WHERE `FinanceInvoice`=`FinanceInvoice`.`Id`) AS `Text`, '.
     110      '`Time`, -(`Value`*`Direction`) AS `Value`, `File`, `BillCode`, `PeriodFrom`, `PeriodTo` FROM `FinanceInvoice` WHERE (`Subject`='.$Subject['Id'].')) ORDER BY `Time` DESC) AS `T1`';
     111    $DbResult = $this->Database->query('SELECT SUM(`T1`.`Value`) AS `Total` FROM '.$UserOperationTableQuery);
    112112    $DbRow = $DbResult->fetch_array();
    113113    $Total = $DbRow['Total'];
     
    133133    $Output .= 'Rozpis měsíčního poplatku:<br><table class="WideTable">'.
    134134      '<tr><th>Služba</th><th>Cena [Kč]</th></tr>';
    135     $DbResult = $this->Database->query('SELECT Service.Name, Service.Price FROM ServiceCustomerRel '.
    136       'LEFT JOIN Service ON Service.Id=ServiceCustomerRel.Service '.
    137       'WHERE (ServiceCustomerRel.Customer='.$Customer['Id'].') AND (ServiceCustomerRel.Action IS NULL)');
     135    $DbResult = $this->Database->query('SELECT `Service`.`Name`, `Service`.`Price` FROM `ServiceCustomerRel` '.
     136      'LEFT JOIN `Service` ON `Service`.`Id`=`ServiceCustomerRel`.`Service` '.
     137      'WHERE (`ServiceCustomerRel`.`Customer`='.$Customer['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL)');
    138138    while($DbRow = $DbResult->fetch_assoc())
    139139    {
  • trunk/Modules/NetworkConfigRouterOS/Generators/Queue.php

    r724 r726  
    5050$InetInterface = $Config['MainRouter']['InetInterface'];
    5151
    52 $DbResult = $System->Database->select('Service', '*', '(`ReplaceId` IS NULL) AND (`Id`='.TARIFF_FREE.')');
     52$DbResult = $System->Database->select('Service', '*', '(`ChangeAction` IS NULL) AND (`Id`='.TARIFF_FREE.')');
    5353if($DbResult->num_rows == 1)
    5454{
     
    7979  $DbResult4 = $System->Database->query('SELECT `Service`.*, `ServiceCustomerRel`.`Id` AS `RelId` FROM `ServiceCustomerRel` '.
    8080    'JOIN `Service` ON `Service`.`Id` = `ServiceCustomerRel`.`Service` '.
    81     'WHERE (`ServiceCustomerRel`.`Customer` = '.$Member['Id'].') AND (`ServiceCustomerRel`.`Action` IS NULL) '.
     81    'WHERE (`ServiceCustomerRel`.`Customer` = '.$Member['Id'].') AND (`ServiceCustomerRel`.`ChangeAction` IS NULL) '.
    8282    'AND (`Service`.`InternetSpeedMax` > 0) AND (`Service`.`InternetSpeedMin` > 0)');
    8383  while($Service = $DbResult4->fetch_assoc())
Note: See TracChangeset for help on using the changeset viewer.