Ignore:
Timestamp:
Mar 3, 2013, 1:03:25 PM (12 years ago)
Author:
chronos
Message:
  • Upraveno: Zobecněn proces aktualizace dat tabulek při přechodu na nové období. Nyní se používá pole Action jakožto akce přidat, změnit, odebrat a ReplaceId jakožto id položky, které se operace týká.
  • Přidáno: Podpora pro uživatelské akce v seznamech v sekci Správa dat.
  • Upraveno: Řešení procesu generování nových faktur a plateb zákazníků při přechodu na nové období.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/finance/manage.php

    r486 r497  
    7171        $Output .= '<a href="?Operation=ConvertPDFToFile">Převést data z databáze do souborů</a><br />';
    7272        $Output .= '<a href="?Operation=Bills">Správa dokladů</a><br />';
    73         $Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
     73        //$Output .= '<a href="?Operation=Recalculate">Přepočet financí</a><br />';
    7474        $Output .= '<a href="?Operation=ShowMonthlyPayment">Měsíční vyúčtování</a><br />';
    75         $Output .= '<a href="clenove.php">Seznam členů</a><br />';
     75        //$Output .= '<a href="clenove.php">Seznam členů</a><br />';
    7676        $Output .= '<a href="zivnost.php">Živnost</a><br />';
    7777        $Output .= '<a href="?Operation=GenerateBills">Generovat chybějící doklady</a><br />';
    … …  
    135135      $DbResult = $this->Database->select('FinanceInvoice', '*', 'Subject='.$Subject.
    136136      ' AND TimePayment IS NULL AND Value > 0 ORDER BY TimeCreation LIMIT 1');
    137       //echo($Database->error);
    138137      if($DbResult->num_rows > 0)
    139138      {
    … …  
    147146            'TimeCreation' => TimeToMysqlDateTime($LastInsertTime),
    148147            'CashFlowId' => $OpenedClaim['Id'], 'Direction' => 'In'));
    149           //echo($Database->LastQuery);
    150148        } else break;
    151149      } else break;
    … …  
    153151  }
    154152
    155   function InsertLiability($Subject, $Value, $TimeCreation, $TimeDue, $Text,
     153  function InsertInvoice($Subject, $TimeCreation, $TimeDue, $Items,
    156154    $DocumentLine, $PeriodFrom, $PeriodTo)
    157155  {
    … …  
    159157
    160158    $BillCode = $this->System->Modules['Finance']->GetNextDocumentLineNumber($DocumentLine);
     159    $SumValue = 0;
     160    foreach($Items as $Item)
     161      $SumValue = $SumValue + $Item['Price'];
    161162    $this->Database->insert('FinanceInvoice', array(
    162163      'Subject' => $Subject, 'TimeCreation' => TimeToMysqlDateTime($TimeCreation),
    163       'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $Value, 'BillCode' => $BillCode,
     164      'TimeDue' => TimeToMysqlDateTime($TimeDue), 'Value' => $SumValue, 'BillCode' => $BillCode,
    164165      'PeriodFrom' => TimeToMysqlDate($PeriodFrom), 'PeriodTo' => TimeToMysqlDate($PeriodTo)));
    165166    $InvoiceId = $this->Database->insert_id;
    166     $this->Database->insert('FinanceInvoice', array('FinanceInvoice' => $InvoiceId,
    167       'Description' => $Text, 'Price' => $Value, 'Quantity' => 1, 'VAT' => 0));
    168     $Output = '.'; //$this->Database->LastQuery.'<br />';
     167    foreach($Items as $Item)
     168    $this->Database->insert('FinanceInvoiceItem', array('FinanceInvoice' => $InvoiceId,
     169      'Description' => $Item['Description'], 'Price' => $Item['Price'], 'Quantity' => $Item['Quantity'], 'VAT' => $Item['VAT']));
    169170    //$LastInsertTime = $Time;
    170171    //$this->CheckAdvancesAndLiabilities($Subject);
    171     return($Output);
     172    return($InvoiceId);
    172173  }
    173174
    … …  
    256257    $Form = new Form('NewInvoice');
    257258    $Form->LoadvaluesFromForm();
    258     //print_r($Form->Values);
    259     $this->InsertLiability($Form->Values['Subject'], $Form->Values['Value'],
    260       $Form->Values['TimeCreation'], $Form->Values['TimeDue'], $Form->Values['Text'],
     259    $Items = array(array('Price' => $Form->Values['Value'],
     260      'Description' => $Form->Values['Text'], 'Quantity' => 1, 'VAT' => 21));
     261    $LastId = $this->InsertInvoice($Form->Values['Subject'], 
     262      $Form->Values['TimeCreation'], $Form->Values['TimeDue'], $Items,
    261263      $Form->Values['DocumentLine'], NULL, NULL);
    262     $LastId = $this->Database->insert_id;
    263264    $DbResult = $this->Database->select('FinanceInvoice', 'BillCode', 'Id='.$LastId);
    264265    $DbRow = $DbResult->fetch_assoc();
    … …  
    304305      'MemberPayment.Cash AS Cash, '.
    305306      '(SELECT GROUP_CONCAT(Service.Name) FROM ServiceCustomerRel LEFT JOIN Service '.
    306       'ON Service.Id=ServiceCustomerRel.Service WHERE ServiceCustomerRel.Customer=Member.Id AND ServiceCustomerRel.Period=1) AS ServicesNextMonth, '.
     307      'ON Service.Id=ServiceCustomerRel.Service WHERE ServiceCustomerRel.Customer=Member.Id AND '.
     308      'ServiceCustomerRel.Action IS NULL) AS ServicesNextMonth, '.
    307309      'UNIX_TIMESTAMP(Member.BillingPeriodLastDate) AS LastDate, Subject.Name AS SubjectName, '.
    308310      'FinanceBillingPeriod.Name AS BillingPeriodName '.
    … …  
    350352  }
    351353 
     354  function ProduceInvoices()
     355  {
     356    $Output = '';
     357   
     358    // Generuj účetní položky
     359    $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');
     360    while($Member = $DbResult->fetch_assoc())
     361    {
     362      $Output .= $Member['SubjectName'].': ';
     363      $Period = $this->GetBillingPeriod($Member['BillingPeriodNext']);
     364      if($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'])
     365      {
     366        $this->Database->update('Member', 'Id='.$Member['Id'],
     367            array('BillingPeriod' => $Member['BillingPeriodNext']));
     368        $Member['BillingPeriod'] = $Member['BillingPeriodNext'];
     369      }
     370      $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
     371      if(($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']) and
     372          ($Member['Blocked'] == 0))
     373      {
     374        $InvoiceItems = array();
     375        $MonthlyTotal = 0;
     376        $DbResult2 = $this->Database->query('SELECT Service.* FROM ServiceCustomerRel LEFT JOIN Service '.
     377          'ON Service.Id=ServiceCustomerRel.Service WHERE (ServiceCustomerRel.Customer='.
     378          $Member['Id'].') AND (ServiceCustomerRel.Action IS NULL)');
     379        while($Service = $DbResult2->fetch_assoc())
     380        {
     381          $InvoiceItems[] = array('Description' => $Service['Name'], 'Price' => $Service['Price'],
     382              'Quantity' => $Period['MonthCount'], 'VAT' => 21);
     383          $MonthlyTotal += $Service['Price'];
     384        }
     385        if($Member['Hire'] != 0)
     386        {
     387          $Output .= '<tr><td>Nájem</td><td>'.(-$Member['Hire']).'</td></tr>';
     388          $MonthlyTotal -= $Member['Hire'];
     389        }
     390        if($Member['MonthlyPlus'] != 0)
     391        {
     392          $MonthlyTotal -= $Member['MonthlyPlus'];
     393        }
     394        $PayPerPeriod = $MonthlyTotal * $Period['MonthCount'];
     395   
     396        $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
     397        $Output .= $TimePeriodText.': '.$MonthlyTotal.' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
     398        $this->InsertInvoice($Member['Subject'], 'NOW()', 'DATE_ADD(NOW(), INTERVAL '.INVOICE_DUE_DAYS.' DAY)',
     399          $InvoiceItems, INVOICE_OUT_DOC_LINE, $Period['From'], $Period['To']);
     400   
     401        $Output .= $this->SendPaymentEmail($Member['Id']);
     402        $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
     403      } else $Output .= '<br />';
     404    } 
     405    return($Output);
     406  }
     407 
     408  function TableUpdatePeriod($Table)
     409  {
     410    $DbResult = $this->Database->select($Table, '*', '`Action` IS NOT NULL');
     411    while($Service = $DbResult->fetch_array())
     412    {
     413      if($Service['Action'] == 'add')
     414      {
     415        unset($Service['Id']);
     416        unset($Serivce['ReplaceId']);
     417        unset($Serivce['Action']);
     418        $this->Database->insert($Table, $Service);
     419      } else
     420      if($Service['Action'] == 'modify')
     421      {
     422        unset($Service['Id']);
     423        unset($Service['Action']);
     424        $ReplaceId = $Service['ReplaceId'];
     425        unset($Service['ReplaceId']);
     426        $this->Database->update($Table, 'Id='.$ReplaceId, $Service);
     427      } else
     428      if($Service['Action'] == 'delete')
     429      {
     430        $this->Database->delete($Table, '`Id`='.$Service['ReplaceId']);
     431      }
     432    }
     433    $this->Database->delete($Table, '`Action` IS NOT NULL');
     434  } 
     435 
    352436  function ProcessMonthlyPayment()
    353437  {
    … …  
    363447    $Output -= 'Datum: '.date('j.n.Y').'<br />';
    364448   
    365     $Datum = explode('-', $Overall['Date']);
    366     $Mesic = date('m') + 0;
     449    $DateParts = explode('-', $Overall['Date']);
     450    $MonthLast = $DateParts[1];
     451    $MonthCurrent = date('m') + 0;
    367452
    368453    $Output .= $Finance->RecalculateSegmentParameters();
    … …  
    402487
    403488    // Zkontrolovat odečtení měsíčního poplatku
    404     //$Mesic = '1';
    405     $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.round($Datum[1]).' Aktuální měsíc-'.$Mesic.'<br />';
    406     if($Mesic != $Datum[1])
     489    $Output .= 'Kontrola odečtení poplatků: Poslední měsíc-'.$MonthLast.' Aktuální měsíc-'.$MonthCurrent.'<br />';
     490    //if($MonthCurrent != $MonthLast)
    407491    {
    408492      $Output .= 'Odečítám měsíční poplatek...<br />';
    409 
    410       // Generuj účetní položky
    411       $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');
    412       while($Member = $DbResult->fetch_assoc())
    413       {
    414         $Output .= $Member['SubjectName'].': ';
    415         //$Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'] = MysqlDateTimeToTime('2009-04-01 1:1:1');
    416         $Period = $this->GetBillingPeriod($Member['BillingPeriodNext']);
    417         if($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)'])
    418         {
    419           $this->Database->update('Member', 'Id='.$Member['Id'],
    420             array('BillingPeriod' => $Member['BillingPeriodNext']));
    421           $Member['BillingPeriod'] = $Member['BillingPeriodNext'];
    422         }
    423         $Period = $this->GetBillingPeriod($Member['BillingPeriod']);
    424         if(($Period['From'] > $Member['UNIX_TIMESTAMP(Member.BillingPeriodLastDate)']) and ($Member['InternetTariffCurrentMonth'] != TARIFF_FREE) and ($PayPerPeriod > 0) and
    425         ($Member['Blocked'] == 0))
    426         {
    427           $BillItems = array();
    428           $MonthlyTotal = 0;
    429           $DbResult2 = $this->Database->query('(SELECT Service.* FROM ServiceCustomerRel LEFT JOIN Service '.
    430             'ON Service.Id=ServiceCustomerRel.Service WHERE ServiceCustomerRel.Customer='.$Member['Id'].') AND (ServiceCustomerRel.Period=0)');
    431           while($Service = $DbResult2->fetch_assoc())
    432           {
    433             $BillItems[] = array('Description' => $Service['Name'], 'Price' => $Service['Price'],
    434             'Quantity' => $Period['MonthCount']);
    435             $MonthlyTotal += $ServicePrice['Price'];
    436             $this->Database->update('ServiceCustomerRel', 'Id='); 
    437           }
    438           if($Member['Hire'] != 0)
    439           {
    440             $Output .= '<tr><td>Nájem</td><td>'.(-$Member['Hire']).'</td></tr>';
    441             $MonthlyTotal -= $Member['Hire'];
    442           }
    443           if($Member['MonthlyPlus'] != 0)
    444           {
    445             $MonthlyTotal -= $Member['MonthlyPlus'];
    446           }
    447           $PayPerPeriod = $MonthlyTotal * $Period['MonthCount'];
    448    
    449 //echo($Mesic.'%'.$MonthCount.'='.($Mesic % $MonthCount).' ');                 
    450           $TimePeriodText = date('j.n.Y', $Period['From']).' - '.date('j.n.Y', $Period['To']);
    451           $Output .= $TimePeriodText.': '.$MonthlyTotal.' * '.$Period['MonthCount'].' = '.$PayPerPeriod.'<br />';
    452           $BillCode = $Finance->GetNextDocumentLineNumber(INVOICE_OUT_DOC_LINE);
    453           $BillId = $this->System->Modules['Bill']->CreateBill($Member['Subject'],
    454             $BillItems, time(), time() + 3600 * 24 * INVOICE_DUE_DAYS, $BillCode, $Period['From'], $Period['To']);
    455           $this->Database->insert('FinanceInvoice', array(
    456             'Subject' => $Member['Subject'], 'TimeCreation' => 'NOW()',
    457             'TimeDue' => 'DATE_ADD(NOW(), INTERVAL '.INVOICE_DUE_DAYS.' DAY)',             
    458             'Bill' => $BillId, 'BillCode' => $BillCode));
    459           $InvoiceId = $this->Database->insert_id;
    460           $this->Database->insert('FinanceInvoiceItem', array('FinanceInvoice' => $InvoiceId,
    461             'Value' => $PayPerPeriod, 'Text' => 'Vyúčtování služeb za období '.$TimePeriodText,
    462            'Quantity' => 1, 'VAT' => 0));
    463          
    464           $Output .= $this->SendPaymentEmail($Member['Id']);
    465           $this->Database->update('Member', 'Id='.$Member['Id'], array('BillingPeriodLastDate' => TimeToMysqlDateTime($Period['To'])));
    466         } else $Output .= '<br />';
    467       }
     493      $this->ProduceInvoices();   
    468494     
    469495      // Update finance charge
    470496      $Output .= 'Měním aktuální parametry sítě...<br>';
    471       $this->Database->delete('FinanceCharge', 'Period = 0');
    472       $DbResult = $this->Database->select('FinanceCharge', '*', 'Period = 1');
    473       $Charge = $DbResult->fetch_assoc();
    474       //print_r($Charge);
    475       $DbResult = $this->Database->insert('FinanceCharge', array('Period' => 0, 'Internet' => $Charge['Internet'], 'InternetSpeed' => $Charge['InternetSpeed'], 'InternetSpeedReserve' => $Charge['InternetSpeedReserve'], 'AdministrationPerUser' => $Charge['AdministrationPerUser'], 'kWh' => $Charge['kWh'], 'BaseSpeedElement' => $Charge['BaseSpeedElement'], 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice']));
     497      $this->TableUpdatePeriod('FinanceCharge');
    476498
    477499      $Output .= 'Přidávám měsíční přehled...<br />';
    478       $this->Database->insert('FinanceMonthlyOverall', array('Date' => 'NOW()', 'Money' => $Finance->Internet, 'kWh' => $Finance->kWh, 'Administration' => $Finance->Sprava, 'AdministrationTotal' => $SpravaCelkem, 'ConsumptionTotal' => $TotalConsumptionCost, 'TotalPaid' => $Finance->TotalPaid, 'BaseTariffPrice' => $Charge['BaseTariffPrice'], 'TopTariffPrice' => $Charge['TopTariffPrice'], 'MemberCount' => $Finance->InternetUsers));
     500      $this->Database->insert('FinanceMonthlyOverall', array('Date' => 'NOW()',
     501        'Money' => $Finance->Internet, 'kWh' => $Finance->kWh,
     502        'Administration' => $Finance->Sprava, 'AdministrationTotal' => $SpravaCelkem,
     503        'ConsumptionTotal' => $TotalConsumptionCost, 'TotalPaid' => $Finance->TotalPaid,
     504        'BaseTariffPrice' => $Charge['BaseTariffPrice'],
     505        'TopTariffPrice' => $Charge['TopTariffPrice'], 'MemberCount' => $Finance->InternetUsers));
    479506
    480507      // Update services
    481       $Output .= 'Měním aktuální tarify....<br>';
    482       $DbResult = $this->Database->select('Service', '*', '`ReplaceId` IS NOT NULL');
    483       while($Service = $DbResult->fetch_array())
    484       {
    485         $Service['Id'] = $Service['ReplaceId'];
    486         $Service['ReplaceId'] = '';
    487         $this->Database->update('Service', 'Id='.$Service['ReplaceId'], $Service);
    488       }
    489       $this->Database->delete('Service', '`ReplaceId` IS NOT NULL');
    490 
     508      $Output .= 'Aktualizuji služby....<br>';
     509      $this->TableUpdatePeriod('Service');   
     510      // Update customer service selections
     511      $Output .= 'Aktualizuji výběr služeb zákazníků....<br>';
     512      $this->TableUpdatePeriod('ServiceCustomerRel');     
     513     
    491514      $Finance->RecalculateMemberPayment();
    492515      //CreateMonthlyOverallBill();
Note: See TracChangeset for help on using the changeset viewer.