Changeset 550


Ignore:
Timestamp:
Jul 7, 2013, 7:37:48 PM (11 years ago)
Author:
chronos
Message:
  • Upraveno: Přepracována třída pro import plateb přes Fio API. Nyní se načítají položky přes CSV formát.
  • Přidáno: Import z Fio do tabulky FinanceBankImport a odsud pak pokud je nalezen variabilní symbol tak do FinanceOperation.
Location:
trunk
Files:
11 edited

Legend:

Unmodified
Added
Removed

  • trunk/Common/Global.php

    r548 r550  
    5858 
    5959  date_default_timezone_set('Europe/Prague');
     60  mb_internal_encoding("UTF-8");
    6061  $ScriptTimeStart = GetMicrotime();
    6162 

  • trunk/Common/Version.php

    r549 r550  
    11<?php
    22
    3 $Revision = 549; // Subversion revision
    4 $DatabaseRevision = 549; // SQL structure revision
    5 $ReleaseTime = '2013-07-05';
     3$Revision = 550; // Subversion revision
     4$DatabaseRevision = 550; // SQL structure revision
     5$ReleaseTime = '2013-07-07';
    66

  • trunk/Modules/Finance/Finance.php

    r549 r550  
    420420        'LoginPassword' => array('Type' => 'String', 'Caption' => 'Přihlašovací heslo', 'Default' => ''),
    421421        'Operations' => array('Type' => 'TFinanceOperationListAccount', 'Caption' => 'Operace', 'Default' => ''),
     422        'LastImportDate' => array('Type' => 'Date', 'Caption' => 'Datum posledního importu', 'Default' => ''),
     423        'LastImportId' => array('Type' => 'String', 'Caption' => 'Id posledního importu', 'Default' => ''),
    422424      ),
    423425      'ItemActions' => array(

  • trunk/Modules/FinanceBankAPI/FileImport.php

    r548 r550  
    33include_once(dirname(__FILE__).'/../../Common/Global.php');
    44
    5 class FileImport
     5class BankImport
    66{
    77  var $Database;
    8   var $BankAccountId;
     8  var $BankAccount;
    99 
    1010  function __construct($Database)
    … …  
    1919  function ImportFile($Content, $Ext)
    2020  {   
     21  }
     22 
     23  function PairOperations()
     24  {
     25    $DbResult = $this->Database->select('FinanceBankImport', '*', 'FinanceOperation IS NULL');
     26    while($DbRow = $DbResult->fetch_assoc())
     27    {
     28      if(is_numeric($DbRow['VariableSymbol']))
     29      {
     30        $DbResult2 = $this->Database->select('Subject', 'Id', 'Id='.$DbRow['VariableSymbol']);
     31        if($DbResult2->num_rows == 1)
     32        {
     33          $DbRow2 = $DbResult2->fetch_assoc();
     34          $DbResult3 = $this->Database->insert('FinanceOperation', array('Subject' => $DbRow2['Id'], 'Cash' => 0,
     35            'Value' => $DbRow['Value'], 'Taxable' => 1, 'BankAccount' => $DbRow['BankAccount'], 'Network' => 1,
     36            'Time' => $DbRow['Time'], 'Text' => $DbRow['Description']));
     37          $this->Database->update('FinanceBankImport', 'Id='.$DbRow['Id'], array('FinanceOperation' => $this->Database->insert_id));
     38        }
     39      }
     40    }
    2141  }
    2242}
    … …  
    4565    if(isset($Import))
    4666    {
    47       $Import->BankAccountId = $BankAccount['Id'];
     67      $Import->BankAccount = $BankAccount;
    4868      $Output .= $Import->Import();
     69      $Import->PairOperations();
    4970    }
    5071    return($Output);
    … …  
    100121      else if($Bank['Code'] == '0300') $Import = new ImportPS($this->Database);
    101122      else $Output = $this->SystemMessage('Nepodporované API', 'Pro zvolenou banku není import podporován');
    102     $Import->BankAccountId = $BankAccount['Id'];
     123    $Import->BankAccount = $BankAccount;
    103124    $Output .= $Import->ImportFile($File->GetContent(), $File->GetExt());
    104125   

  • trunk/Modules/FinanceBankAPI/FinanceBankAPI.php

    r548 r550  
    4646        'Identification' => array('Type' => 'String', 'Caption' => 'Kód operace', 'Default' => ''),
    4747        'AccountNumber' => array('Type' => 'String', 'Caption' => 'Číslo účtu', 'Default' => ''),
     48        'BankCode' => array('Type' => 'String', 'Caption' => 'Kód banky', 'Default' => ''),
    4849        'VariableSymbol' => array('Type' => 'String', 'Caption' => 'Variabilní symbol', 'Default' => ''),
    4950        'ConstantSymbol' => array('Type' => 'String', 'Caption' => 'Konstantní symbol', 'Default' => ''),

  • trunk/Modules/FinanceBankAPI/Fio.php

    r548 r550  
    5151      foreach($Response as $Index => $Line)
    5252      {
    53         if(($Index == 0) and (substr($Line, 0, strlen(GPC_TYPE_REPORT)) != GPC_TYPE_REPORT)) $this->NoValidDataError($Response);
     53        if(($Index == 0) and (substr($Line, 0, strlen(GPC_TYPE_REPORT)) != GPC_TYPE_REPORT)) $this->NoValidDataError($Response);
    5454        $GPCLine = $GPC->ParseLine($Line);
    5555        if($GPCLine != NULL) $Result[] = $GPCLine;

  • trunk/Modules/FinanceBankAPI/FioAPI.php

    r548 r550  
    44
    55include('GPC.php');
     6
     7function RemoveComma($Text)
     8{
     9  if((mb_strlen($Text) >= 2) and ($Text[0] == '"') and (mb_substr($Text, -1, 1) == '"')) return(mb_substr($Text, 1, -1));
     10    else return($Text);
     11}
    612 
    713class FioAPI
    … …  
    915  var $Token;
    1016  var $Encoding;
     17  var $Format;
    1118 
    1219  function __construct()
    1320  {
    1421    $this->Encoding = 'utf-8';
     22    $this->Format = 'csv';
    1523  }
    1624 
    … …  
    2028 
    2129    // URL format: https://www.fio.cz/ib_api/rest/periods/{token}/{datum od}/{datum do}/transactions.{format}
    22     echo('1');
    23     $fp = fsockopen('ssl://www.fio.cz', 443, $errno, $errstr, 30);
    24     echo('2');
    25     if(!$fp)
     30    // Send request
     31    $RequestURL = '/ib_api/rest/periods/'.$this->Token.'/'.
     32      date('Y-m-d', $TimeFrom).'/'.date('Y-m-d', $TimeTo).'/transactions.'.$this->Format;
     33    echo($RequestURL);
     34    $Response = file_get_contents('https://www.fio.cz'.$RequestURL);
     35    if($Response == FALSE)
    2636    {
    27       throw new Exception('Connection error: '.$errstr);
     37      throw new Exception('Connection error');
    2838    } else
    2939    {
    30       // Send request
    31       $RequestURL = '/ib_api/rest/periods/'.$this->Token.'/'.
    32         date('Y-m-d', $TimeFrom).'/'.date('Y-m-d', $TimeTo).'/transactions.gpc';
    33       $Request = "GET ".$RequestURL." HTTP/1.1\r\n";
    34       $Request .= "Host: www.fio.cz\r\n";
    35       $Request .= "User-Agent: PHP Script\r\n";
    36       $Request .= "Content-Type: text/html\r\n";
    37       $Request .= "Connection: Close\r\n\r\n";
    38       fwrite($fp, $Request);
    39  
    40       // Read response
    41       echo('3');
    42       $Response = array();
    43       while(!feof($fp))
    44         $Response .= trim(fgets($fp, 1024))."\n";
    45       fclose($fp);     
    46       echo('4');
    47       $Response = iconv('windows-1250', $this->Encoding, $Response);
     40      echo($Response);
     41      if($this->Format == 'gpc') $Response = iconv('windows-1250', $this->Encoding, $Response);
    4842      $Response = explode("\n", $Response);
    49  
    50       // Strip HTTP header
    51       while($Response[0] != '') array_shift($Response);
    52       array_shift($Response); // Remove empty line
    53       //echo(implode("\n", $Response));
    54  
    55       // Parse all GPC lines
    56       $GPC = new GPC();
    57       $Result = array();
    58       foreach($Response as $Index => $Line)
     43     
     44      if($this->Format == 'gpc')
     45      {       
     46        // Parse all GPC lines
     47        $GPC = new GPC();
     48        $Result = array();
     49        foreach($Response as $Index => $Line)
     50        {
     51          if(($Index == 0) and (substr($Line, 0, strlen(GPC_TYPE_REPORT)) != GPC_TYPE_REPORT)) $this->NoValidDataError($Response);
     52          $GPCLine = $GPC->ParseLine($Line);
     53          if($GPCLine != NULL) $Result[] = $GPCLine;
     54        }
     55      } else
     56      if($this->Format == 'csv')
    5957      {
    60         if(($Index == 0) and (substr($Line, 0, strlen(GPC_TYPE_REPORT)) != GPC_TYPE_REPORT)) $this->NoValidDataError($Response);
    61         $GPCLine = $GPC->ParseLine($Line);
    62         if($GPCLine != NULL) $Result[] = $GPCLine;
     58        $Result = array(
     59          'Items' => array(),
     60        );
     61       
     62        // CVS header
     63        while($Response[0] != '')
     64        {
     65          $Line = explode(';', $Response[0]);
     66          if($Line[0] == 'accountId') $Result['AccountNumber'] = $Line[0];
     67          else if($Line[0] == 'bankId') $Result['BankId'] = $Line[0];
     68          else if($Line[0] == 'currency') $Result['Currency'] = $Line[0];
     69          else if($Line[0] == 'iban') $Result['IBAN'] = $Line[0];
     70          else if($Line[0] == 'bic') $Result['BIC'] = $Line[0];
     71          else if($Line[0] == 'openingBalance') $Result['OpeningBalance'] = $Line[0];
     72          else if($Line[0] == 'closingBalance') $Result['ClosingBalance'] = $Line[0];
     73          else if($Line[0] == 'dateStart') $Result['DateStart'] = $Line[0];
     74          else if($Line[0] == 'dateEnd') $Result['DateEnd'] = $Line[0];
     75          else if($Line[0] == 'idFrom') $Result['IdFrom'] = $Line[0];
     76          else if($Line[0] == 'idTo') $Result['IdTo'] = $Line[0];           
     77          array_shift($Response);
     78        }
     79        array_shift($Response); // Remove empty line
     80
     81        if($Response[0] != 'ID pohybu;Datum;Objem;Měna;Protiúčet;Název protiúčtu;Kód banky;Název banky;KS;VS;SS;Uživatelská identifikace;Zpráva pro příjemce;Typ;Provedl;Upřesnění;Komentář;BIC;ID pokynu'
     82          ) throw new Exception('Unsupported CSV header');
     83        array_shift($Response);
     84        array_pop($Response);
     85        foreach($Response as $Index => $Line)
     86        {
     87          $Line = explode(';', $Line);
     88          $Date = explode('.', $Line[1]);
     89          $Date = mktime(0, 0, 0, $Date[1], $Date[0], $Date[2]);
     90          $NewRecord = array('ID' => $Line[0], 'Date' => $Date, 'Value' => $Line[2], 'CurrencyCode' => $Line[3],
     91            'OffsetAccount' => $Line[4], 'OffsetAccountName' => $Line[5], 'BankCode' => $Line[6], 'BankName' => RemoveComma($Line[7]),
     92            'ConstantSymbol' => $Line[8], 'VariableSymbol' => $Line[9], 'SpecificSymbol' => $Line[10],
     93            'UserIdent' => RemoveComma($Line[11]), 'Message' => RemoveComma($Line[12]), 'Type' => RemoveComma($Line[13]),
     94            'User' => RemoveComma($Line[14]), 'Details' => RemoveComma($Line[15]), 'Comment' => RemoveComma($Line[16]),
     95            'BIC' => $Line[17], 'OrderID' => $Line[18]);
     96          $Result['Items'][] = $NewRecord;         
     97        }
    6398      }
    6499      return($Result);

  • trunk/Modules/FinanceBankAPI/GPC.php

    r548 r550  
    99  { 
    1010    $Line = ' '.$Line;
    11     $Type = substr($Line, 1, 3);                                                   
     11    $Type = mb_substr($Line, 1, 3);                                                   
    1212 
    1313    if($Type == GPC_TYPE_REPORT)
    … …  
    1616      (
    1717        'Type' => GPC_TYPE_REPORT,
    18         'AccountNumber' => substr($Line, 4, 16),
    19         'AccountName' => trim(substr($Line, 20, 20)),
    20         'OldBalanceDate' => mktime(0, 0, 0, substr($Line, 42, 2), substr($Line, 40, 2), '20'.substr($Line, 44, 2)),
    21         'OldBalanceValue' => (substr($Line, 60, 1).substr($Line, 46, 14)) / 100,
    22         'NewBalanceValue' => (substr($Line, 75, 1).substr($Line, 61, 14)) / 100,
    23         'DebitValue' => (substr($Line, 90, 1).substr($Line, 76, 14)) / 100,   
    24         'CreditValue' => (substr($Line, 105, 1).substr($Line, 91, 14)) / 100,   
    25         'SequenceNumber' => intval(substr($Line, 106, 3)),
    26         'Date' => mktime(0, 0, 0, substr($Line, 111, 2), substr($Line, 109, 2), '20'.substr($Line, 113, 2)),
     18        'AccountNumber' => mb_substr($Line, 4, 16),
     19        'AccountName' => trim(mb_substr($Line, 20, 20)),
     20        'OldBalanceDate' => mktime(0, 0, 0, mb_substr($Line, 42, 2), mb_substr($Line, 40, 2), '20'.mb_substr($Line, 44, 2)),
     21        'OldBalanceValue' => (mb_substr($Line, 60, 1).mb_substr($Line, 46, 14)) / 100,
     22        'NewBalanceValue' => (mb_substr($Line, 75, 1).mb_substr($Line, 61, 14)) / 100,
     23        'DebitValue' => (mb_substr($Line, 90, 1).mb_substr($Line, 76, 14)) / 100,   
     24        'CreditValue' => (mb_substr($Line, 105, 1).mb_substr($Line, 91, 14)) / 100,   
     25        'SequenceNumber' => intval(mb_substr($Line, 106, 3)),
     26        'Date' => mktime(0, 0, 0, mb_substr($Line, 111, 2), mb_substr($Line, 109, 2), '20'.mb_substr($Line, 113, 2)),
    2727        //'DataAlignment' => substr($Line, 115, 14),
    2828        'CheckSum' => sha1(md5($Line).$Line),
    … …  
    3434      (
    3535        'Type' => GPC_TYPE_ITEM,
    36         'AccountNumber' => substr($Line, 4, 16),
    37         'OffsetAccount' => substr($Line, 20, 16),
    38         'RecordNumber' => substr($Line, 36, 13),
    39         'Value' => substr($Line, 49, 12) / 100,
    40         'Code' => substr($Line, 61, 1),   
    41         'VariableSymbol' => intval(substr($Line, 62, 10)),
    42         'BankCode' => substr($Line, 74, 4),
    43         'ConstantSymbol' => intval(substr($Line, 78, 4)),
    44         'SpecificSymbol' => intval(substr($Line, 82, 10)),
    45         'Valut' => substr($Line, 92, 6),
    46         'ClientName' => substr($Line, 98, 20),
     36        'AccountNumber' => mb_substr($Line, 4, 16),
     37        'OffsetAccount' => mb_substr($Line, 20, 16),
     38        'RecordNumber' => mb_substr($Line, 36, 13),
     39        'Value' => mb_substr($Line, 49, 12) / 100,
     40        'Code' => mb_substr($Line, 61, 1),   
     41        'VariableSymbol' => intval(mb_substr($Line, 62, 10)),
     42        'BankCode' => mb_substr($Line, 74, 4),
     43        'ConstantSymbol' => intval(mb_substr($Line, 78, 4)),
     44        'SpecificSymbol' => intval(mb_substr($Line, 82, 10)),
     45        'Valut' => mb_substr($Line, 92, 6),
     46        'ClientName' => mb_substr($Line, 98, 20),
    4747        //'Zero' => substr($Line, 118, 1),
    48         'CurrencyCode' => substr($Line, 119, 4),
    49         'DueDate' => mktime(0, 0, 0, substr($Line, 125, 2), substr($Line, 123, 2), substr($Line, 127, 2)),
     48        'CurrencyCode' => mb_substr($Line, 119, 4),
     49        'DueDate' => mktime(0, 0, 0, mb_substr($Line, 125, 2), mb_substr($Line, 123, 2), mb_substr($Line, 127, 2)),
    5050        'CheckSum' => sha1(md5($Line).$Line),
    5151      );

  • trunk/Modules/FinanceBankAPI/ImportFio.php

    r548 r550  
    33include('FioAPI.php');
    44 
    5 class ImportFio extends FileImport
     5class ImportFio extends BankImport
    66{
    77  function Import()
    8   {
    9     $DbResult = $this->Database->select('FinanceBankAccount', '*', 'Id='.$this->BankAccountId);   
    10     $BankAccount = $DbResult->fetch_assoc();
    11    
     8  {   
    129    $Fio = new FioAPI();
    13     $Fio->Token = $BankAccount['LoginName'];
    14     $Records = $Fio->Import(time() - 3600 * 24 * 31 * 2, time());
    15     foreach($Records as $Record)
     10    $Fio->Token = $this->BankAccount['LoginName'];
     11    if($this->BankAccount['LastImportDate'] == '') $PeriodStart = time();
     12      else $PeriodStart = MysqlDateToTime($this->BankAccount['LastImportDate']);
     13    $PeriodEnd = time();
     14    $Records = $Fio->Import($PeriodStart, $PeriodEnd);
     15    print_r($Records);
     16    //die();
     17    $Output = '<table>';
     18      //$Output .= '<tr>';
     19      //$Output .= '<td>Jméno účtu: '.$Records['AccountName'].'</td>';
     20        //$Output .= '<td>Číslo účtu: '.$Records['AccountNumber'].'</td>';
     21        //$Output .= '<td>Ke dni '.date('j.n.Y', $Records['DateStart']).' je stav účtu '.$Records['OpeningBalance'].' Kč</td>';
     22        //$Output .= '<td>Ke dni '.date('j.n.Y', $Records['DateEnd']).' je stav účtu '.$Records['ClosingBalance'].' Kč</td>';
     23        //$Output .= '<td>Suma příjmů: '.$Records['CreditValue'].' Kč</td>';
     24        //$Output .= '<td>Suma výdajů: '.$Records['DebitValue'].' Kč</td>';     
     25      //$Output .= '</tr>';
     26    foreach($Records['Items'] as $Record)
    1627    {
    17       echo('<tr>');
    18       if($Record['Type'] == GPC_TYPE_REPORT)
    19       {
    20         echo('<td>Jméno účtu: '.$Record['AccountName'].'</td>');
    21         echo('<td>Číslo účtu: '.$Record['AccountNumber'].'</td>');
    22         echo('<td>Ke dni '.date('j.n.Y', $Record['OldBalanceDate']).' je stav účtu '.$Record['OldBalanceValue'].' Kč</td>');
    23         echo('<td>Ke dni '.date('j.n.Y', $Record['Date']).' je stav účtu '.$Record['NewBalanceValue'].' Kč</td>');
    24         echo('<td>Suma příjmů: '.$Record['CreditValue'].' Kč</td>');
    25         echo('<td>Suma výdajů: '.$Record['DebitValue'].' Kč</td>');     
    26       } else
    27       if($Record['Type'] == GPC_TYPE_ITEM)
    28       {
    29         $this->Database->insert('FinanceBankImport', array('Time' => TimeToMysqlDate($Record['DueDate']),
    30           'BankAccount' => $this->BankAccount, 'Value' => $Record['Value'],
     28      $DbResult = $this->Database->select('FinanceBankImport', 'ID', 'Identification='.$Record['ID']);
     29      if($DbResult->num_rows == 0)
     30      {     
     31      $Output .= '<tr>';
     32        $this->Database->insert('FinanceBankImport', array('Time' => TimeToMysqlDate($Record['Date']),
     33          'BankAccount' => $this->BankAccount['Id'], 'Value' => $Record['Value'],
    3134          'SpecificSymbol' => $Record['SpecificSymbol'], 'VariableSymbol' => $Record['VariableSymbol'],
    32           'ConstantSymbol' => $Record['ConstantSymbol'], 'Currency' => $this->Currency,
    33           ));
    34         echo('<td>'.$Record['OffsetAccount'].'</td>');
    35         echo('<td>'.$Record['BankCode'].'</td>');
    36         echo('<td>'.$Record['ClientName'].'</td>');
     35          'ConstantSymbol' => $Record['ConstantSymbol'], 'Currency' => $this->BankAccount['Currency'],
     36          'Identification' => $Record['ID'], 'AccountNumber' => $Record['OffsetAccount'],
     37          'BankCode' => $Record['BankCode'], 'Description' => $Record['Type'], 'OffsetAccountName' => $Record['UserIdent']));
     38      $Output .= '<td>'.$Record['OffsetAccount'].'</td>';
     39      $Output .= '<td>'.$Record['BankCode'].'</td>';
     40      $Output .= '<td>'.$Record['UserIdent'].'</td>';
     41      $Output .= '</tr>';
    3742      }
    3843    }
     44    $Output .= '</table>';   
     45    $this->Database->Update('FinanceBankAccount', 'Id='.$this->BankAccount['Id'], array('LastImportDate' => TimeToMysqlDate($PeriodEnd)));
     46    return($Output);
    3947  }
    4048}

  • trunk/Modules/FinanceBankAPI/ImportPS.php

    r548 r550  
    11<?php
    22
    3 class ImportPS extends FileImport
     3class ImportPS extends BankImport
    44{
    55  function ImportFile($Content, $Ext)

  • trunk/admin/Updates.php

    r549 r550  
    314314}
    315315
    316 function UpdateTo535($Manager)
     316function UpdateTo549($Manager)
    317317{
    318318  $Manager->Execute("ALTER TABLE `FinanceOperation` ADD `Generate` INT NOT NULL DEFAULT '0',
    … …  
    320320  $Manager->Execute("ALTER TABLE `FinanceInvoice` ADD `Generate` INT NOT NULL DEFAULT '0',
    321321ADD INDEX ( `Generate` ) ;");
     322}
     323
     324function UpdateTo550($Manager)
     325{ 
     326  $Manager->Execute('ALTER TABLE `FinanceBankAccount` ADD `LastImportId` VARCHAR( 255 ) NOT NULL ;');
     327  $Manager->Execute('ALTER TABLE `FinanceBankAccount` ADD `LastImportDate` DATE NULL ;');
     328  $Manager->Execute('ALTER TABLE `FinanceBankImport` ADD `BankCode` VARCHAR( 255 ) NOT NULL AFTER `AccountNumber` ;');
    322329}
    323330
    … …  
    339346  527 => array('Revision' => 535, 'Function' => 'UpdateTo535'),
    340347  535 => array('Revision' => 549, 'Function' => 'UpdateTo549'),
     348  549 => array('Revision' => 550, 'Function' => 'UpdateTo550'),
    341349);
Note: See TracChangeset for help on using the changeset viewer.