source: trunk/Modules/Finance/Import.php

<?php

class PageFinanceImportPayment extends Page
{
  function __construct(System $System)
  {
    parent::__construct($System);
    $this->Title = 'Import plateb';
    $this->ParentClass = 'PageFinance';
  }

  function Show(): string
  {
    if (!ModuleUser::Cast($this->System->GetModule('User'))->User->CheckPermission('Finance', 'SubjectList')) return 'Nemáte oprávnění';
    if (array_key_exists('Operation', $_GET))
    {
      if ($_GET['Operation'] == 'prepare') return $this->Prepare();
      else if ($_GET['Operation'] == 'insert') return $this->Insert();
      else echo('Neplatná akce');
    } else
    {
      $Output = 'Vložte CSV data z SYLK exportu Poštovní spořitelny';
      $Output .= '<form action="?Operation=prepare" method="post">';
      $Output .= '<textarea name="Source" cols="80" rows="20"></textarea><br/>';
      $Output .= '<input type="submit" value="Analyzovat"/>';
      $Output .= '</form>';
      return $Output;
    }
  }

  function Prepare(): string
  {
    $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;
    $Finance->LoadMonthParameters(0);
    $Data = explode("\n", $_POST['Source']);
    foreach ($Data as $Key => $Value)
    {
      $Value = str_replace('\"', '"', $Value);
      $Data[$Key] = str_getcsv($Value, ',', '"', "\\");      
      foreach ($Data[$Key] as $Key2 => $Value2)
      {
        if (substr($Data[$Key][$Key2], 0, 2) == '\"')
          $Data[$Key][$Key2] = substr($Data[$Key][$Key2], 2, -2);
      }
    }
    $Header = array(
      0 => 'datum zaúčtování',
      1 => 'částka',
      2 => 'měna',
      3 => 'zůstatek',
      4 => 'konstantní symbol',
      5 => 'variabilní symbol',
      6 => 'specifický symbol',
      7 => 'označení operace',
      8 => 'název protiúčtu',
      9 => 'protiúčet',
      10 => 'poznámka',
      11 => '',
    );
    
    if ($Header != $Data[0]) {
      $Output = 'Nekompatibilní struktura CSV';
      print_r($Header);
      print_r($Data[0]);
    }
    else
    {
      array_shift($Data);
      $Automatic = '';
      $Output = '<form action="?Operation=insert" method="post">';
      $I = 0;
      foreach ($Data as $Key => $Value)
      {
        if (count($Value) <= 1) continue;
        if ($Value[9] == '') $Value[5] = 128; // Žádný účet => Poštovní spořitelna
        $Time = explode('.', $Value[0]);
        $Time = $Time[2].'-'.$Time[1].'-'.$Time[0];
        $Money = $Value[1];
        if (is_numeric($Value[5]))
        {
          $Subject = $Value[5] * 1;
          $DbResult = $this->Database->query('SELECT Id FROM Subject WHERE Id='.$this->Database->real_escape_string($Subject));
          if ($DbResult->num_rows == 0) $Subject = '? ('.($Value[5] * 1).')';
        } else
        {
          $Subject = '? ('.$Value[5].')';
        }
        if (!is_numeric($Subject))
        {
          $Mode = 'Ručně';
          $Style = 'style="background-color: LightPink;" ';
        } else
        {
          $Mode = 'Automaticky';
          $Style = '';
        }

        if ($Money < 0) $Text = 'Platba převodem';
          else $Text = 'Přijatá platba';
        $Automatic .= '<tr>'.
            //'<td>'.$Mode.'</td>'.
            '<td><input type="text" name="Date'.$I.'" value="'.$Time.'"/></td>'.
            '<td><input type="text" '.$Style.'name="Subject'.$I.'" value="'.$Subject.'"/></td>'.
            '<td>'.$Value[8].'</td>'.
            '<td><input type="text" name="Money'.$I.'" value="'.$Money.'"/></td>'.
            '<td><input type="text" name="Text'.$I.'" value="'.$Text.'"/></td>'.
            '<td><input type="text" name="Taxable'.$I.'" value="1"/></td>'.
            '<td><input type="text" name="Network'.$I.'" value="1"/></td>'.
            '</tr><tr><td colspan="7">'.implode(', ', $Value).'</td></tr>';
        $I++;
      }
      $Output .= '<table class="WideTable">'.
        '<tr>'.
        //'<th>Zpracování</th>'.
        '<th>Datum</th><th>Var. symbol</th><th>Protiúčet</th><th>Částka [Kč]</th><th>Text</th><th>Zdanitelné</th><th>Síť</th></tr>';
      $Output .= $Automatic.'</table>';
      $Output .= '<input type="hidden" name="ItemCount" value="'.$I.'"/>';
      $Output .= '<input type="submit" value="Zpracovat"/></form>';
    }
    return $Output;
  }

  function InsertMoney(string $Subject, string $Value, string $Cash, string $Taxable, string $Time, string $Text, array $Group): void
  {
    $Year = date('Y', $Time);
    $BillCode = ModuleDocument::Cast($this->System->GetModule('Document'))->GetNextDocumentLineNumberId($Group['DocumentLine'], $Year);
    // TODO: Fixed BankAccount=1, allow to select bank account for import
    $this->Database->insert('FinanceOperation', array('Text' => $Text,
      'Subject' => $Subject, 'Cash' => $Cash, 'ValueUser' => $Value, 'Value' => $Value * $Group['ValueSign'],
      'Time' => TimeToMysqlDateTime($Time), 'Taxable' => $Taxable, 'BillCode' => $BillCode,
      'BankAccount' => 1, 'Group' => $Group['Id']));
    // TODO: Update Value
  }

  function Insert(): string
  {
    $Finance = &ModuleFinance::Cast($this->System->GetModule('Finance'))->Finance;
    $Finance->LoadMonthParameters(0);
    $Output = '';

    for ($I = $_POST['ItemCount'] - 1; $I >= 0 ; $I--)
    {
      if ($_POST['Money'.$I] < 0)
      {
        $FinanceGroup = $Finance->GetFinanceGroupById(OPERATION_GROUP_ACCOUNT_OUT, 'FinanceOperationGroup');
      } else
      {
        $FinanceGroup = $Finance->GetFinanceGroupById(OPERATION_GROUP_ACCOUNT_OUT, 'FinanceOperationGroup');
      }
      $Date = explode('-', $_POST['Date'.$I]);
      $Date = mktime(0, 0, 0, $Date[1], $Date[2], $Date[0]);
      $this->InsertMoney($_POST['Subject'.$I], Abs($_POST['Money'.$I]),
        0, $_POST['Taxable'.$I], $Date, $_POST['Text'.$I], $FinanceGroup);
      $Output .= $I.', ';
      ModuleLog::Cast($this->System->GetModule('Log'))->NewRecord('Finance', 'NewPaymentInserted');
    }
    return $Output;
  }
}