<?php

defined('_MAILING') or die('Restricted access');

set_time_limit(0);

$csv_komunikat = '';
$csverrorsdescdet = '';
$possible_tags = Array();
$id_tags = Array();
$dodano = 0;
$updated = 0;

if (isset($_POST['zapisz']) && !DEMO_MODE){
    
    $ignore_errors = isset($_POST['ignore_errors']) ? true : false;
    $update = isset($_POST['update']) ? true : false;
    
    $app->checkToken();

    // XLS column numbers
    $colEmail = (int) isset($_POST['colEmail']) ? (int) $_POST['colEmail'] -1 : 0;
    $colPerson = isset($_POST['colPerson']) ? (int) $_POST['colPerson'] -1 : -1;
    $colTag = isset($_POST['colTag']) ? (int) $_POST['colTag'] -1 : -1;
    $colMark = isset($_POST['colMark']) ? (int) $_POST['colMark'] -1 : -1;
    $colWebsite = isset($_POST['colWebsite']) ? (int) $_POST['colWebsite'] -1 : -1;
    $colf01 = isset($_POST['colf01']) ? (int) $_POST['colf01'] -1 : -1;
    $colf02 = isset($_POST['colf02']) ? (int) $_POST['colf02'] -1 : -1;
    $colf03 = isset($_POST['colf03']) ? (int) $_POST['colf03'] -1 : -1;
    $delimiter = isset($_POST['delimiter']) ? $_POST['delimiter'] : ",";
    $maxLineLength = (int) isset($_POST['maxlinelength']) > 0 ? (int) $_POST['maxlinelength'] : 256;

    $tags = $db->query(" select * from ".PREF."_recipient_tag order by name asc");
    foreach ($tags as $tag) {
        array_push($possible_tags, $tag['name']);
        array_push($id_tags, Array($tag['name'], $tag['id']));
    }
    
    @list($filenamecsv, $extensioncsv) = explode(".",$_FILES["file"]["name"]);

        if ($_FILES["file"]["error"] > 0) {
            $csv_komunikat = "Error: " . $_FILES["file"]["error"] . "<br>";
        } else {
            $csverrors = false;
            $row = 0;
            if (($handle = fopen($_FILES["file"]["tmp_name"], "r")) !== FALSE) {
                while (($data = fgetcsv($handle, $maxLineLength, $delimiter)) !== FALSE) {
                    
                    $num = count($data);
                    $row++;
                    

                    
                    if (! $ignore_errors) {
                        
                        // dane array
                        for ($c=0; $c < $num; $c++) {
                            list( $pole[$row] ) = explode(",", $data[$c]);
                            $dane[$row][$c] = $pole[$row];
                        }
                        
                        if ( !is_it_mail($dane[$row][$colEmail]) && $dane[$row][$colEmail] !='' ){
                            $csverrorsdescdet .= RECIPIENT_ROW . $row . RECIPIENT_WRONG_EMAIL."<b>".$dane[$row][$colEmail]."</b> <br/>";
                            $csverrors = true;
                        }

                        if (!$update && isset($dane[$row][$colEmail])) {
                            $checkDuplicates = $db->prepare(" select email from ".PREF."_recipient where email = '".$dane[$row][$colEmail]."' ");
                            $checkDuplicates->execute();
                            $emailCheck = $checkDuplicates->fetchAll();
                            foreach ($emailCheck as $duplicate) {
                                if ($duplicate['email'] != '') {
                                    $csverrorsdescdet .= RECIPIENT_ROW . $row . RECIPIENT_EMAIL_EXIST."<b>".$dane[$row][$colEmail]."</b> <br/>";
                                    $csverrors = true;
                                }
                            }
                        }

                        if ($dane[$row][$colEmail] =='') {
                            $csverrorsdescdet .= RECIPIENT_ROW . $row . RECIPIENT_EMAIL_LACK."<br/>";
                            $csverrors = true;
                        }

                        for ($x=1; $x < $row; $x++) {
                            if ($dane[$row][$colEmail] == $dane[$x][$colEmail]) {
                                $csverrorsdescdet .= RECIPIENT_ROW . $row . " <b>".$dane[$row][$colEmail]."</b>".RECIPIENT_EMAIL_IN_DB.$x."<br/>";
                                $csverrors = true;
                            }
                        }
                        
                        if ($colTag >= 0) {
                            if (isset($dane[$row][$colTag])) {
                                $csvTags = explode(" ", $dane[$row][$colTag]);
                                $csvTagsQty = count($csvTags);
                                $ids = '';
                                for ($t=0; $t < $csvTagsQty; $t++) {
                                    $csvTag = napraw($csvTags[$t]);
                                    if (!in_array($csvTag, $possible_tags)) {
                                        $csverrorsdescdet .= RECIPIENT_ROW . $row . RECIPIENT_TAG_NOT_EXIST."<b>".$csvTag."</b> <br/>";
                                        $csverrors = true;
                                    } else {
                                        foreach ($id_tags as $id_tag) {
                                            if ($id_tag[0] == $csvTag) {
                                                $ids .= $id_tag[1].',';
                                            }
                                        }
                                    }
                                }
                                $ids = substr($ids, 0, -1);
                                $dane[$row][$colTag] = $ids;
                            }
                        }
  
                    } else {
                        // ignore errors and insert what is possible

                        $email = $data[$colEmail];
                        if ($colPerson >= 0) {
                            $person = isset($data[$colPerson]) ? napraw($data[$colPerson]) : "" ;
                        } else {
                            $person = '';
                        }
                        if ($colTag >= 0) {
                            $tags = isset($data[$colTag]) ? napraw($data[$colTag]) : "" ;
                        } else {
                            $tags = '';
                        }
                        if ($colMark >= 0) {
                            $comment = isset($data[$colMark]) ? napraw($data[$colMark]) : "" ;
                        } else {
                            $comment = '';
                        }
                        if ($colWebsite >= 0) {
                            $website = isset($data[$colWebsite]) ? napraw($data[$colWebsite]) : "" ;
                        } else {
                            $website = '';
                        }
                        if ($colf01 >= 0) {
                            $f01 = isset($data[$colf01]) ? napraw($data[$colf01]) : "" ;
                        } else {
                            $f01 = '';
                        }
                        if ($colf02 >= 0) {
                            $f02 = isset($data[$colf02]) ? napraw($data[$colf02]) : "" ;
                        } else {
                            $f02 = '';
                        }
                        if ($colf03 >= 0) {
                            $f03 = isset($data[$colf03]) ? napraw($data[$colf03]) : "" ;
                        } else {
                            $f03 = '';
                        }

                        if (is_it_mail($email)) {

                            $sql = "
                                INSERT INTO ".PREF."_recipient (
                                    person,
                                    email,
                                    comment,
                                    website,
                                    f01,
                                    f02,
                                    f03
                                ) VALUES (
                                    :person,
                                    :email,
                                    :comment,
                                    :website,
                                    :f01,
                                    :f02,
                                    :f03
                                )
                            ";
                        if ($update) {
                            $sql .= "
                                ON DUPLICATE KEY UPDATE 
                                    person = :person,
                                    comment = :comment
                            ";
                        }
                            $email = trim($email);
                            $stmt = $db->prepare($sql);
                            $stmt->bindParam(':person', $person, PDO::PARAM_STR);
                            $stmt->bindParam(':email', $email, PDO::PARAM_STR);
                            $stmt->bindParam(':comment', $comment, PDO::PARAM_STR);
                            $stmt->bindParam(':website', $website, PDO::PARAM_STR);
                            $stmt->bindParam(':f01', $f01, PDO::PARAM_STR);
                            $stmt->bindParam(':f02', $f02, PDO::PARAM_STR);
                            $stmt->bindParam(':f03', $f03, PDO::PARAM_STR);
                            $stmt->execute();

                            $id_recipient = $db->lastInsertId();
                            
                        if ($id_recipient != 0) {
                            $dodano++;
                        }
                        
                        if ($update) {
                            $stmt = $db->prepare("
                                SELECT
                                    r.id
                                FROM 
                                    ".PREF."_recipient AS r
                                WHERE
                                    r.email=? 
                            ");
                            $stmt->bindParam(1, $email, PDO::PARAM_STR);
                            $stmt->execute();
                            $row = $stmt->fetchAll();
                            $id_recipient = $row[0]['id'];
                            
                            $sql = " DELETE FROM ".PREF."_recipient_tag_conn WHERE id_recipient = :id ";
                            $stmt = $db->prepare($sql);              
                            $stmt->bindParam(':id', $id_recipient, PDO::PARAM_INT);   
                            $stmt->execute();
                        }
                            
                            if ($tags !='' && $colTag >= 0) {
                                $arrayTags = explode(" ", $tags);
                                
                                foreach ($arrayTags as $tagName) {
                                    
                                    $stmt = $db->query(" select id from ".PREF."_recipient_tag where name='".$tagName."' ");
                                    $row = $stmt->fetchAll();
                                    if ( count($row) > 0 ) {
                                        $id_tag = $row[0]['id'];
                                        if ($id_tag !='') {
                                            $sql = "
                                                INSERT INTO ".PREF."_recipient_tag_conn (
                                                    id_recipient,
                                                    id_tag
                                                ) VALUES (
                                                    :id_recipient,
                                                    :id_tag
                                                )
                                            ";
                                            $stmt = $db->prepare($sql);
                                            $stmt->bindParam(':id_recipient', $id_recipient, PDO::PARAM_INT);    
                                            $stmt->bindParam(':id_tag', $id_tag, PDO::PARAM_INT);
                                            $stmt->execute();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                fclose($handle);
                if ($ignore_errors) {
                    
                    if ($update) {
                        $csv_komunikat .= " Affected ".$dodano." row(s)<br>";
                    } else {
                        $csv_komunikat .= "<br/>".RECIPIENT_CSV_ADDED."<b>".$dodano."</b>".RECIPIENT_CSV_ADDED_FROM."<b>".$_FILES['file']['name']."</b></br></br>";
                    }
                }
            }

            if (! $csverrors && ! $ignore_errors) {
                
                for ($x=1; $x < ($row+1); $x++) {
                    
                    if (is_it_mail($dane[$x][$colEmail])) {
                        
                        $email = napraw($dane[$x][$colEmail]);
                        if ($colPerson >= 0) {
                            $person = isset($dane[$x][$colPerson]) ? napraw($dane[$x][$colPerson]) : '';
                        } else {
                            $person = '';
                        }
                        if ($colTag >= 0) {
                            $tags = isset($dane[$x][$colTag]) ? explode(',', $dane[$x][$colTag]) : '';
                        } else {
                            $tags = '';
                        }
                        if ($colMark >= 0) {
                            $comment = isset($dane[$x][$colMark]) ? napraw($dane[$x][$colMark]) : '';
                        } else {
                            $comment = '';
                        }
                        if ($colWebsite >= 0) {
                            $website = isset($dane[$x][$colWebsite]) ? napraw($dane[$x][$colWebsite]) : '';
                        } else {
                            $website = '';
                        }
                        if ($colf01 >= 0) {
                            $f01 = isset($dane[$x][$colf01]) ? napraw($dane[$x][$colf01]) : '';
                        } else {
                            $f01 = '';
                        }
                        if ($colf02 >= 0) {
                            $f02 = isset($dane[$x][$colf02]) ? napraw($dane[$x][$colf02]) : '';
                        } else {
                            $f02 = '';
                        }
                        if ($colf03 >= 0) {
                            $f03 = isset($dane[$x][$colf03]) ? napraw($dane[$x][$colf03]) : '';
                        } else {
                            $f03 = '';
                        }
                        
                        $sql = "
                            INSERT INTO ".PREF."_recipient (
                                person,
                                email,
                                comment,
                                website,
                                f01,
                                f02,
                                f03
                            ) VALUES (
                                :person,
                                :email,
                                :comment,
                                :website,
                                :f01,
                                :f02,
                                :f03
                            )
                        ";
                        if ($update) {
                            $sql .= "
                                ON DUPLICATE KEY UPDATE 
                                    person = :person,
                                    comment = :comment
                            ";
                        }
                        
                        $email = trim($email);
                        $stmt = $db->prepare($sql);
                        $stmt->bindParam(':person', $person, PDO::PARAM_STR);
                        $stmt->bindParam(':email', $email, PDO::PARAM_STR);
                        $stmt->bindParam(':comment', $comment, PDO::PARAM_STR);
                        $stmt->bindParam(':website', $website, PDO::PARAM_STR);
                        $stmt->bindParam(':f01', $f01, PDO::PARAM_STR);
                        $stmt->bindParam(':f02', $f02, PDO::PARAM_STR);
                        $stmt->bindParam(':f03', $f03, PDO::PARAM_STR);
                        $stmt->execute();

                        $id_recipient = $db->lastInsertId();
                        
                        $dodano++;
                        
                        if ($update) {
                            $stmt = $db->prepare("
                                SELECT
                                    r.id
                                FROM 
                                    ".PREF."_recipient AS r
                                WHERE
                                    r.email=? 
                            ");
                            $stmt->bindParam(1, $email, PDO::PARAM_STR);
                            $stmt->execute();
                            $rowek = $stmt->fetchAll();
                            $id_recipient = $rowek[0]['id'];
                            
                            $sql = " DELETE FROM ".PREF."_recipient_tag_conn WHERE id_recipient = :id ";
                            $stmt = $db->prepare($sql);              
                            $stmt->bindParam(':id', $id_recipient, PDO::PARAM_INT);   
                            $stmt->execute(); 
                            $updated++;
                            
                        }
                        
                        if ($tags !='') {
                            foreach ($tags as $id_tag) {
                                if (! ((int)$id_recipient == 0 || (int)$id_tag == 0)) {
                                    $sql = "
                                        INSERT INTO ".PREF."_recipient_tag_conn (
                                            id_recipient,
                                            id_tag
                                        ) VALUES (
                                            :id_recipient,
                                            :id_tag
                                        )
                                    ";
                                    $stmt = $db->prepare($sql);
                                    $stmt->bindParam(':id_recipient', $id_recipient, PDO::PARAM_INT);    
                                    $stmt->bindParam(':id_tag', $id_tag, PDO::PARAM_INT);
                                    $stmt->execute();
                                }
                            }
                        }
                    }
                }
                if ($update) {
                    $csv_komunikat .= "<br/>Affected: <b>".$updated."</b> row(s)</br></br>";
                } else {
                    $csv_komunikat .= "<br/>".RECIPIENT_CSV_ADDED."<b>".$dodano."</b>".RECIPIENT_CSV_ADDED_FROM."<b>".$_FILES['file']['name']."</b></br></br>";
                }
                if ($ignore_errors) {
                    $csverrorsdescdet = '';
                }
            } else {
                $csv_komunikat .= "ERRORS: <b>".$_FILES['file']['name']."</b><br/>".$csverrorsdescdet;
            }
        }
}

function napraw($str) {
    $str = strtr($str, array(
        "'"  => "&#39;",
        "\"" => "&#34;"
    ));
    $str = preg_replace('!\s+!', ' ', $str);
    if ( $_POST['charset'] != '0' ) {
        $str = iconv($_POST['charset'],"UTF-8",$str);
    }
    return $str;
}

function is_it_mail($mm) {
    $pattern = "/[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})/i";
    if (!preg_match($pattern, $mm)) {
        return false;
    } else {
        return true;
    }
}

?>

<div class="page-title">
    <div class="title_left">
        <h3><?php echo EMAIL_ADRESSES;?> <small>(<?php echo RECIPIENT_IMPORT_CSV;?>)</small></h3>
    </div>
</div>
<div class="clearfix"></div>

<div class="row">
    <div class="col-md-12 col-sm-12 col-xs-12">
        <div class="x_panel">
            <div class="x_title">
                <h2><?php echo RECIPIENT_PREPARE_CSV;?></h2>
                <div class="clearfix"></div>
            </div>
            <div class="x_content">
                <div class="csv_upload">
                    <p><?php echo RECIPIENT_UPLOAD_CSV_TITLE;?> <a class="btn btn-primary btn-xs" href="example-recipients-import.csv"><?php echo RECIPIENT_UPLOAD_CSV_TEMPLATE;?></a>:</p>
                    <form class="form-horizontal" method="post" enctype="multipart/form-data" action="index.php?manage=recipient&action=import">
                        <ol>
                            <li><?php echo CSV_ST_1;?>
                                <ul>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='1' name='colEmail' required></code>: (<span class="red"><?php echo CSV_REQ;?></span>) <b><?php echo CSV_ADDR;?></b></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colPerson' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b><?php echo CSV_NAME;?></b></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colTag' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b><?php echo CSV_TAGS;?></b> - <?php echo CSV_TAGSDESC;?></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colMark' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b><?php echo CSV_COMMENT;?></b></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colWebsite' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b>f1</b></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colf01' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b>f1</b></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colf02' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b>f2</b></li>
                                    <li><?php echo CSV_COL_NO;?> <code><input class='importcsv' type='text' value='' name='colf03' ></code>: (<span class="green"><?php echo CSV_OPT;?></span>) <b>f3</b></li>
                                </ul>
                            </li>
                            <li><?php echo CSV_DESC1;?> (<code><input class='importcsv' type='text' value=',' name='delimiter' required></code> <?php echo CSV_DESC2;?>)(*.csv)
                                <ul>
                                    <li><?php echo CSV_MAXLINE;?>: <code><input class='importcsv' type='text' value='256' name='maxlinelength' required></code></li>
                                </ul>
                            </li>
                            <li><?php echo CSV_FORMDESC;?></li>
                        </ol>
                        <input type="hidden" name="csrf_token" value="<?php echo $app->getToken();?>">
                        <input type="hidden" name="zapisz" id="zapisz">
                        <hr>
                        
                        
                        <div class="form-group">
                            <div class="col-sm-2">
                                <label class="control-label" for="charset">source CSV charset</label>
                            </div>
                                <div class="col-sm-4">
                                    <select name="charset" class="form-control" id="charset">
                                        <option value="0" selected="selected"> - select CSV charset -</option>
                                        <option value="big5"     >Big5 Traditional Chinese</option>
                                        <option value="dec8"     >DEC West European</option>
                                        <option value="cp850"    >DOS West European</option>
                                        <option value="hp8"      >HP West European</option>
                                        <option value="koi8r"    >KOI8-R Relcom Russian</option>
                                        <option value="latin1"   >cp1252 West European</option>
                                        <option value="latin2"   >ISO 8859-2 Central European</option>
                                        <option value="swe7"     >7bit Swedish</option>
                                        <option value="ascii"    >US ASCII</option>
                                        <option value="ujis"     >EUC-JP Japanese</option>
                                        <option value="sjis"     >Shift-JIS Japanese</option>
                                        <option value="hebrew"   >ISO 8859-8 Hebrew</option>
                                        <option value="tis620"   >TIS620 Thai</option>
                                        <option value="euckr"    >EUC-KR Korean</option>
                                        <option value="koi8u"    >KOI8-U Ukrainian</option>
                                        <option value="gb2312"   >GB2312 Simplified Chinese</option>
                                        <option value="greek"    >ISO 8859-7 Greek</option>
                                        <option value="cp1250"   >Windows Central European</option>
                                        <option value="gbk"      >GBK Simplified Chinese</option>
                                        <option value="latin5"   >ISO 8859-9 Turkish</option>
                                        <option value="armscii8" >ARMSCII-8 Armenian</option>
                                        <option value="utf8"     >UTF-8 Unicode</option>
                                        <option value="ucs2"     >UCS-2 Unicode</option>
                                        <option value="cp866"    >DOS Russian</option>
                                        <option value="keybcs2"  >DOS Kamenicky Czech-Slovak</option>
                                        <option value="macce"    >Mac Central European</option>
                                        <option value="macroman" >Mac West European</option>
                                        <option value="cp852"    >DOS Central European</option>
                                        <option value="latin7"   >ISO 8859-13 Baltic</option>
                                        <option value="utf8mb4"  >UTF-8 Unicode</option>
                                        <option value="cp1251"   >Windows Cyrillic</option>
                                        <option value="utf16"    >UTF-16 Unicode</option>
                                        <option value="cp1256"   >Windows Arabic</option>
                                        <option value="cp1257"   >Windows Baltic</option>
                                        <option value="utf32"    >UTF-32 Unicode</option>
                                        <option value="binary"   >Binary pseudo charset</option>
                                        <option value="geostd8"  >GEOSTD8 Georgian</option>
                                        <option value="cp932"    >SJIS for Windows Japanese</option>
                                        <option value="eucjpms"  >UJIS for Windows Japanese</option>
                                    </select>
                                </div>
                        </div>
                        
                        
                        <div class='checkbox'>
                            <label>
                                <input class='flat' type='checkbox' name='ignore_errors' value='1' checked='checked'>&nbsp;<?php echo RECIPIENT_CSV_IGNORE_ERRORS;?>
                            </label>
                        </div>
                        <p>&nbsp;</p>
                        <div class='checkbox'>
                            <label>
                                <input class='flat' type='checkbox' name='update' value='1'>&nbsp;<?php echo RECIPIENT_CSV_UPDATE;?>
                            </label>
                        </div>
                        <hr>
                            <div class="col col-sm-12">
                                <label for="upload"><?php echo RECIPIENT_UPLOAD_CSV_NAME;?></label>
                            </div>
                            <div class="col col-sm-6">
                                <div class="input-group ">
                                    <span class="input-group-btn">
                                        <span class="btn btn-primary btn-file">
                                            <?php echo MEDIA_BROWSE;?> <input id="upload" class="form-control input-sm" name="file" type="file" size="100" placeholder=""/>
                                        </span>
                                    </span>
                                    <input id="upload_info" type="text" readonly="" class="form-control">
                                </div>
                            </div>
                            <div class="col col-sm-1">
                                <button type="submit" class="btn btn-primary "><?php echo MEDIA_UPLOAD;?></button>
                            </div>
                    </form>
                    <p>&nbsp;</p>
                    <p><?=$csv_komunikat?><?=$csverrorsdescdet?></p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                    <p>&nbsp;</p>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="components/recipient/view.recipient.import.csv.js"></script>

