<?php

class import {

    private $delimiter = ';';

    public function importPayment($supplier_id, $filename) {
        $supplier = Doctrine::getTable('Supplier')->find($supplier_id);
        if (!is_object($supplier)) {
            echo "Dodavatel neexistuje.";
            return false;
        }

        $lines = $this->loadLines($filename);
        if (count($lines) < 2) {
            echo "Soubor neobsahuje data.";
            return false;
        }

        $mapArr1 = $this->getMap($supplier_id, false);
        $mapArr2 = $this->getMap($supplier_id);
        $defArr = $this->csv2def($lines);

        $processed = 0;

        for($i=1; $i < count($lines); ++$i) {
            $row = $this->parseRow($lines[$i]);
            $row = $this->cleanRow($row);

            $query = Doctrine::getTable('Attachment')
                        ->createQuery()
                        ->where('supplier_code = ?', $row[$defArr[$mapArr1['payment']['supplier_code']]])
                        ->orderBy('id DESC')
                        ->limit(1)
                        ->execute();
            if (count($query) == 1) {
                $attachment = $query[0];
            }

            if (is_object($attachment)) {
                $payment = new Payment();
                $payment->Attachment = $attachment;
                $this->valNotNull($payment, 'price', $this->processVal('price', $row[$defArr[$mapArr1['payment']['price']]]));
                $this->valNotNull($payment, 'code', $this->processVal('code', $row[$defArr[$mapArr1['payment']['code']]]));
                $this->valNotNull($payment, 'bank_code', $this->processVal('bank_code', $row[$defArr[$mapArr1['payment']['bank_code']]]));
                $this->valNotNull($payment, 'payment_date', $this->processVal('payment_date', $row[$defArr[$mapArr1['payment']['payment_date']]]));
                $this->valNotNull($payment, 'payment_type', $this->processVal('payment_type', $row[$defArr[$mapArr1['payment']['payment_type']]]));
                $this->valNotNull($payment, 'update_date', $this->processVal('update_date', $row[$defArr[$mapArr1['payment']['update_date']]]));
                $this->valNotNull($payment, 'handover_date', $this->processVal('handover_date', $row[$defArr[$mapArr1['payment']['handover_date']]]));
                $this->valNotNull($payment, 'note', $this->processVal('note', $row[$defArr[$mapArr1['payment']['note']]]));

                try {
                    $payment->save();
                } catch(Exception $e) {
//                    echo $e->getMessage();
//                    var_dump($debt->getData());exit;
                    throw new Exception('Chyba na řádku: '.$i.'. Předchozí řádky se naimportovali vpořádku.');
                }

                ++$processed;
            }

        }

        if ($processed > 0) {
            ApplicationLogger::logWithUserAnnounce(array('module' => 'TASK', 'action' => 'import_balance'), 'Card', 'import_balance', array('count' => $processed), array(
                array('credentials' => 'card_supervisor', 'type' => array('dashboard', 'email'))
              ));
            return true;
        }

        return false;

    }

    public function importBalance($supplier_id, $filename) {

        $supplier = Doctrine::getTable('Supplier')->find($supplier_id);
        if (!is_object($supplier)) {
            echo "Dodavatel neexistuje.";
            return false;
        }

        $lines = $this->loadLines($filename);
        if (count($lines) < 2) {
            echo "Soubor neobsahuje data.";
            return false;
        }

        $mapArr1 = $this->getMap($supplier_id, false);
        $mapArr2 = $this->getMap($supplier_id);
        $defArr = $this->csv2def($lines);

        $processed = 0;
        for($i=1; $i < count($lines); ++$i) {
            $row = $this->parseRow($lines[$i]);
            $row = $this->cleanRow($row);

            $query = Doctrine::getTable('Attachment')
                        ->createQuery()
                        ->where('supplier_code = ?', $row[$defArr[$mapArr1['debt']['supplier_code']]])
                        ->orderBy('id DESC')
                        ->limit(1)
                        ->execute();     
            if (count($query) == 1) {
                $attachment = $query[0];
            }

            if (is_object($attachment)) {
                $debt = new Debt();
                $debt->Attachment = $attachment;
                $this->valNotNull($debt, 'balance', $this->processVal('balance', $row[$defArr[$mapArr1['debt']['balance']]]));
                $this->valNotNull($debt, 'payment', $this->processVal('payment', $row[$defArr[$mapArr1['debt']['payment']]]));
                $this->valNotNull($debt, 'insurance', $this->processVal('insurance', $row[$defArr[$mapArr1['debt']['insurance']]]));
                $this->valNotNull($debt, 'fee', $this->processVal('fee', $row[$defArr[$mapArr1['debt']['fee']]]));
                $this->valNotNull($debt, 'payed', $this->processVal('payed', $row[$defArr[$mapArr1['debt']['payed']]]));
                $this->valNotNull($debt, 'update_date', $this->processVal('update_date', $row[$defArr[$mapArr1['debt']['update_date']]]));

                try {
                    $debt->save();
                } catch(Exception $e) {
//                    echo $e->getMessage();
//                    var_dump($debt->getData());exit;
                    throw new Exception('Chyba na řádku: '.$i.'. Předchozí řádky se naimportovali vpořádku.');
                }

                ++$processed;
            } 

        }

        if ($processed > 0) {
            ApplicationLogger::logWithUserAnnounce(array('module' => 'TASK', 'action' => 'import_balance'), 'Card', 'import_balance', array('count' => $processed), array(
                array('credentials' => 'card_supervisor', 'type' => array('dashboard', 'email'))
              ));

            return true;
        }

        return false;
    }

    public function importCard($supplier_id, $filename) {


        $supplier = Doctrine::getTable('Supplier')->find($supplier_id);
        if (!is_object($supplier)) {
            echo "Dodavatel neexistuje.";
            return false;
        }

        $lines = $this->loadLines($filename);
        if (count($lines) < 2) {
            echo "Soubor neobsahuje data.";
            return false;
        }
        // pole pro sber jiz importovanych karet
        $cardArr = array();


        $mapArr1 = $this->getMap($supplier_id, false);
        $mapArr2 = $this->getMap($supplier_id);
        $defArr = $this->csv2def($lines);
        for($i=1; $i < count($lines); ++$i) {
            $row = $this->parseRow($lines[$i]);
            $row = $this->cleanRow($row);  // Whiski - cleanRow není potřeba, nicméně otestovat

            /*
             * vytvoreni/znovupouziti kmenove karty
             */
            if (!empty($cardArr[$row[$defArr[$mapArr1['card']['client_code']]]])) {
                $card = $cardArr[$row[$defArr[$mapArr1['card']['client_code']]]];
            } else {
                $card = new Card();
                $card->supplier_id = $supplier_id;
                foreach($mapArr1['card'] AS $col=>$trans) {
                    $val = $this->processVal($col, $row[$defArr[$trans]]);
                    if (!empty($val))
                    {
                        $card->$col = $val;
//                        var_dump(array($col => $val));
                    }
                }

                if (empty($card->client_name))
                    continue;

                try {
                    $card->save();
                } catch(Exception $e) {
                    throw new Exception('Chyba na řádku: '.$i.'. Předchozí řádky se naimportovali vpořádku.');
//                    echo $e->getMessage();
//                    var_dump($card->getData());exit;
                }


                $regal_count = Doctrine::getTable('Regal')->checkRelatedForObject($card, $card->id);
                if ($regal_count) {
                    $card->regal_connected = 1;
                    $card->save();
                }

                $cardArr[$row[$defArr[$mapArr1['card']['client_code']]]] = $card;
            }

            /*
             * pripojeni smluv
             */
            $attachment = new Attachment();
            $attachment->card_id = $card->id;

            foreach($mapArr1['attachment'] AS $col=>$trans) {
                @$val = $this->processVal($col, $row[$defArr[$trans]]);
                if (!empty($val))
                    $attachment->$col = $val;
            }
            //var_dump($attachment->getData());exit;

            try {
                $attachment->save();
            } catch(Exception $e) {

            }

            $regal_count = Doctrine::getTable('Regal')->checkRelatedForObject($attachment, $card->id);
            if ($regal_count && ($card->regal_connected == 0)) {
                $card->regal_connected = 1;
                $card->save();
            }
        }

        if (count($cardArr) > 0) {
            ApplicationLogger::logWithUserAnnounce(array('module' => 'TASK', 'action' => 'import_card'), 'Card', 'import_card', array('count' => count($cardArr)), array(
                array('credentials' => 'card_supervisor', 'type' => array('dashboard', 'email'))
              ));
        }

        return count($cardArr);

    }


    private function loadLines($filename) {
        $lines = file($filename);
        for($i=0; $i<count($lines); ++$i) {
//            if (function_exists('recode')) {
//                $lines[$i] = recode('cp1250..utf8', $lines[$i]);
//            } else {
//                // Whiski - pozor ve Windows - následující nefunguje a navíc přidá do $lines
//                // na začátek řádku další ", takže se pak nepodaří ani $this->parseRow();
//                $lines[$i] = trim(`echo "{$lines[$i]}" | recode cp1250..utf8`);
//            }

            $lines[$i] = $this->win2utf($lines[$i]);

        }
        return $lines;
    }

    private function win2utf($s)
    {
        static $tbl = array("\x80"=>"\xe2\x82\xac","\x81"=>"","\x82"=>"\xe2\x80\x9a","\x83"=>"","\x84"=>"\xe2\x80\x9e","\x85"=>"\xe2\x80\xa6","\x86"=>"\xe2\x80\xa0","\x87"=>"\xe2\x80\xa1","\x88"=>"","\x89"=>"\xe2\x80\xb0","\x8a"=>"\xc5\xa0","\x8b"=>"\xe2\x80\xb9","\x8c"=>"\xc5\x9a","\x8d"=>"\xc5\xa4","\x8e"=>"\xc5\xbd","\x8f"=>"\xc5\xb9","\x90"=>"","\x91"=>"\xe2\x80\x98","\x92"=>"\xe2\x80\x99","\x93"=>"\xe2\x80\x9c","\x94"=>"\xe2\x80\x9d","\x95"=>"\xe2\x80\xa2","\x96"=>"\xe2\x80\x93","\x97"=>"\xe2\x80\x94","\x98"=>"","\x99"=>"\xe2\x84\xa2","\x9a"=>"\xc5\xa1","\x9b"=>"\xe2\x80\xba","\x9c"=>"\xc5\x9b","\x9d"=>"\xc5\xa5","\x9e"=>"\xc5\xbe","\x9f"=>"\xc5\xba","\xa0"=>"\xc2\xa0","\xa1"=>"\xcb\x87","\xa2"=>"\xcb\x98","\xa3"=>"\xc5\x81","\xa4"=>"\xc2\xa4","\xa5"=>"\xc4\x84","\xa6"=>"\xc2\xa6","\xa7"=>"\xc2\xa7","\xa8"=>"\xc2\xa8","\xa9"=>"\xc2\xa9","\xaa"=>"\xc5\x9e","\xab"=>"\xc2\xab","\xac"=>"\xc2\xac","\xad"=>"\xc2\xad","\xae"=>"\xc2\xae","\xaf"=>"\xc5\xbb","\xb0"=>"\xc2\xb0","\xb1"=>"\xc2\xb1","\xb2"=>"\xcb\x9b","\xb3"=>"\xc5\x82","\xb4"=>"\xc2\xb4","\xb5"=>"\xc2\xb5","\xb6"=>"\xc2\xb6","\xb7"=>"\xc2\xb7","\xb8"=>"\xc2\xb8","\xb9"=>"\xc4\x85","\xba"=>"\xc5\x9f","\xbb"=>"\xc2\xbb","\xbc"=>"\xc4\xbd","\xbd"=>"\xcb\x9d","\xbe"=>"\xc4\xbe","\xbf"=>"\xc5\xbc","\xc0"=>"\xc5\x94","\xc1"=>"\xc3\x81","\xc2"=>"\xc3\x82","\xc3"=>"\xc4\x82","\xc4"=>"\xc3\x84","\xc5"=>"\xc4\xb9","\xc6"=>"\xc4\x86","\xc7"=>"\xc3\x87","\xc8"=>"\xc4\x8c","\xc9"=>"\xc3\x89","\xca"=>"\xc4\x98","\xcb"=>"\xc3\x8b","\xcc"=>"\xc4\x9a","\xcd"=>"\xc3\x8d","\xce"=>"\xc3\x8e","\xcf"=>"\xc4\x8e","\xd0"=>"\xc4\x90","\xd1"=>"\xc5\x83","\xd2"=>"\xc5\x87","\xd3"=>"\xc3\x93","\xd4"=>"\xc3\x94","\xd5"=>"\xc5\x90","\xd6"=>"\xc3\x96","\xd7"=>"\xc3\x97","\xd8"=>"\xc5\x98","\xd9"=>"\xc5\xae","\xda"=>"\xc3\x9a","\xdb"=>"\xc5\xb0","\xdc"=>"\xc3\x9c","\xdd"=>"\xc3\x9d","\xde"=>"\xc5\xa2","\xdf"=>"\xc3\x9f","\xe0"=>"\xc5\x95","\xe1"=>"\xc3\xa1","\xe2"=>"\xc3\xa2","\xe3"=>"\xc4\x83","\xe4"=>"\xc3\xa4","\xe5"=>"\xc4\xba","\xe6"=>"\xc4\x87","\xe7"=>"\xc3\xa7","\xe8"=>"\xc4\x8d","\xe9"=>"\xc3\xa9","\xea"=>"\xc4\x99","\xeb"=>"\xc3\xab","\xec"=>"\xc4\x9b","\xed"=>"\xc3\xad","\xee"=>"\xc3\xae","\xef"=>"\xc4\x8f","\xf0"=>"\xc4\x91","\xf1"=>"\xc5\x84","\xf2"=>"\xc5\x88","\xf3"=>"\xc3\xb3","\xf4"=>"\xc3\xb4","\xf5"=>"\xc5\x91","\xf6"=>"\xc3\xb6","\xf7"=>"\xc3\xb7","\xf8"=>"\xc5\x99","\xf9"=>"\xc5\xaf","\xfa"=>"\xc3\xba","\xfb"=>"\xc5\xb1","\xfc"=>"\xc3\xbc","\xfd"=>"\xc3\xbd","\xfe"=>"\xc5\xa3","\xff"=>"\xcb\x99");
        return strtr($s, $tbl);
    }


    private function csv2def($lines, $flip = true) {
        $defArr = $this->parseRow($lines[0]);
        foreach($defArr AS $idx=>$val)
            $defArr[$idx] = $this->cleanField($val);

        if ($flip === true)
            $defArr = array_flip($defArr);

        return $defArr;
    }
    
    private function csv_explode($str, $delim=',', $enclose='"', $preserve=false){
      $resArr = array(); 
      $n = 0; 
      $expEncArr = explode($enclose, $str); 
      foreach($expEncArr as $EncItem){ 
        if($n++%2){ 
          array_push($resArr, array_pop($resArr) . ($preserve?$enclose:'') . $EncItem.($preserve?$enclose:'')); 
        }else{ 
          $expDelArr = explode($delim, $EncItem); 
          array_push($resArr, array_pop($resArr) . array_shift($expDelArr)); 
          $resArr = array_merge($resArr, $expDelArr); 
        } 
      } 
      return $resArr; 
    }
    private function parseRow($row) {
        $rowArr = $this->csv_explode ($row, $this->delimiter);
        /*$in_str = false;
        $in_cell = true;
        $cell = '';
        $i = 0;
        while($i < strlen($row)) {
            $char = $row[$i];
            if ($char == $this->delimiter) {
                if ($in_str) {
                    $cell .= $char;
                } else {
                    $rowArr[] = $cell;
                    $cell = '';
                }
            } elseif ($char == '"') {
                if ($in_str) {
                    $in_str = false;
                } else {
                    $in_str = true;
                }
            } else {
                $cell .= $char;
            }
            ++$i;
        }
        $rowArr[] = $cell; */
        return $rowArr;
    }
    private function cleanField($field) {
        return trim(str_replace('"', '', $field));
    }
    private function cleanRow($row) {
        foreach($row AS $idx=>$val)
            $row[$idx] = $this->cleanField($val);
        return $row;
    }

    private function getMap($supplier_id, $flip = true) {
        $defArr = array(
            1 => array(
                'card' => array(
                    'client_code' => 'RČ/IČ klienta',
                    'client_name' => 'Název klienta',
                    'cell' => 'Mobilní tel.',
                    'address_street' => 'Pref.adresa - ulice',
                    'address_city' => 'Pref.adresa - město',
                    'address_zipcode' => 'Pref.adresa - PSČ',
                    'address_cell' => 'Pref.adresa - mobil',
                    'address_phone' => 'Pref.adresa - tel.',
                    'address_phone2' => 'Pref.adresa - tel. 2',
                    'altaddress_street' => 'Kor.adresa - ulice',
                    'altaddress_city' => 'Kor.adresa - město',
                    'altaddress_zipcode' => 'Kor.adresa - PSČ',
                    'altaddress_cell' => 'Kor.adresa - mobil',
                    'altaddress_phone' => 'Kor.adresa - tel.',
                    'altaddress_phone2' => 'Kor.adresa - tel. 2',
                    'income' => 'Čistý příjem',
                    'income_other' => 'Jiný příjem',
                    'income_partner' => 'Čistý příjem partnera',
                    'phone' => 'Náhradní telefon',
                    'phone_note' => 'Pozn. k telefonu',
                    'employer' => 'Zaměstnavatel',
                    'employer_position' => 'Zaměstnán jako',
                    'employer_phone' => 'Telefon do zaměstnání',
                    'employer_street' => 'Ulice zaměstnavatele',
                    'employer_city' => 'Město zaměstnavatele',
                    'employer_zipcode' => 'PSČ zaměstnavatele'
                ),
                'attachment' => array(
                    'supplier_code' => 'Smlouva',
                    'contract_type' => 'Popis',
                    'logic_state' => 'Logický stav',
                    'refund_type' => 'Způsob úhrady',
                    'item' => 'Předmět',
                    'dealer_name' => 'Dodavatel',
                    'color' => 'Barva',
                    'price' => 'Cena',
                    'numberplate' => 'RZ',
                    'serialnumber' => 'VIN',
                    'vintage' => 'Rok výroby',
                    'cert_info' => 'TP u nás',
                    'deposit' => 'Záloha',
                    'price_funding' => 'Financovaná částka',
                    'price_payment' => 'Splátka',
                    'lease_period' => 'Délka leasingu',
                    'lease_date' => 'Dat. podpisu',
                    'requisition_date' => 'Datum předání',
                    'update_date' => 'Datum aktualizace',
                    'agency_name' => 'Název agentury',
                    'agency_person' => 'Jméno oprávněné osoby',
                    'guarantee_code' => 'RČ/IČ ručitele',
                    'guarantee_name' => 'Název ručitele',
                    'guarantee_address_street' => 'Pref.adresa ručitele - ulice',
                    'guarantee_address_city' => 'Pref.adresa ručitele - město',
                    'guarantee_address_zipcode' => 'Pref.adresa ručitele - PSČ',
                    'guarantee_address_cell' => 'Pref.adresa ručitele - mobil',
                    'guarantee_address_phone' => 'Pref.adresa ručitele - tel.',
                    'guarantee_address_phone2' => 'Pref.adresa ručitele - tel. 2',
                    'guarantee_altaddress_street' => 'Kor.adresa ručitele - ulice',
                    'guarantee_altaddress_city' => 'Kor.adresa ručitele - město',
                    'guarantee_altaddress_zipcode' => 'Kor.adresa ručitele - PSČ',
                    'guarantee_altaddress_cell' => 'Kor.adresa ručitele - mobil',
                    'guarantee_altaddress_phone' => 'Kor.adresa ručitele - tel.',
                    'guarantee_altaddress_phone2' => 'Kor.adresa ručitele - tel. 2'
                ),
                'debt' => array(
                    'supplier_code' => 'Smlouva',
                    'logic_state' => 'Logický stav',
                    'balance' => 'Saldo celkem',
                    'payment' => 'Splátky celkem',
                    'insurance' => 'POV celkem',
                    'fee' => 'Poplatky celkem',
                    'payed' => 'Uhrazeno',
                    'update_date' => 'Datum aktualizace'
                ),
                'payment' => array(
                    'supplier_code' => 'Smlouva',
                    'logic_state' => 'Logický stav',
                    'price' => 'Částka',
                    'code' => 'Var. symbol',
                    'bank_code' => 'Bankovní převod',
                    'payment_date' => 'Datum zaúčtování',
                    'payment_type' => 'Typ platby',
                    'update_date' => 'Datum aktualizace',
                    'handover_date' => 'Datum předání',
                    'note' => 'Popis'
                )
            )
        );

        $retArr = $defArr[$supplier_id];
        if ($flip === true) {
            $retArr['card'] = array_flip($retArr['card']);
            $retArr['attachment'] = array_flip($retArr['attachment']);
        }

        return $retArr;
    }

    private function processVal($col, $val) {
        $val = trim($val);
        switch($col) {
            case 'price':
            case 'price_funding':
            case 'income':
            case 'income_other':
            case 'income_partner':
            case 'balance':
            case 'payment':
            case 'insurance':
            case 'fee':
            case 'payed':
                $val = str_replace('-', '', $val);
                $val = str_replace(',', '.', $val);
                $val = ereg_replace('\.$', '', $val);
                break;
            case 'lease_date':
            case 'requisition_date':
            case 'update_date':
            case 'payment_date':
            case 'handover_date':
                $val = date('Y-m-d', strtotime($val));
                break;
            case 'contract_type':
                switch($val) {
                    case 'Předáno externí agentuře k vymožení':
                        $val = 1;
                        break;
                    case 'Sekundární vymáhání':
                        $val = 2;
                        break;
                    default:
                        $val = 0;
                }
        }
        return $val;
    }

    private function valNotNull(&$record, $column, $val) {
        if (!empty($val))
            $record->$column = $val;
    }
}
?>