<?php
include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once (PATH_TO_ROOT.'CRM/class/class.logger.php');
include_once(PATH_TO_ROOT."CRM/class/class.checkString.php");
include_once (PATH_TO_ROOT."CRM/class/class.Permissions.php");

$permissions = new Permissions($db);
$Logger = new LoggerX($db);

include_once(PATH_TO_ROOT."CRM/class/class.Language-Pack.php");
$LangPack = new LanguagePack($db, $aLanguage);

//ini_set("display_errors",1);

$stanje[1] = $LangPack->GetLabel("CRM_order_grid", "state_ordered","Naročeno");
$stanje[2] = $LangPack->GetLabel("CRM_order_grid", "state_delivered","Dostava");
$stanje[3] = $LangPack->GetLabel("CRM_order_grid", "state_change_date","Sprememba_datuma");
$stanje[4] = $LangPack->GetLabel("CRM_order_grid", "state_storno_team","Storno ekipa");
$stanje[5] = $LangPack->GetLabel("CRM_order_grid", "state_storno_delivery","Storno dostava");
$stanje[6] = $LangPack->GetLabel("CRM_order_grid", "state_product_return","Vračilo izdelka");
$stanje[7] = $LangPack->GetLabel("CRM_order_grid", "state_credit_denial","Zavrnitev kredita");
$stanje[99] = $LangPack->GetLabel("CRM_order_grid", "state_reclamation","Reklamacija");

if($_POST['action'] == "get"){
    
    $filter[] = "a.order_status = 2";
    ($_POST['tip_placila'] != null ? $filter[] = "a.tip_narocila = '".StringChecker::Check($_POST['tip_placila'])."'" : null);
    ($_POST['date_from'] != null ? $filter[] = "a.datum_dostave >= STR_TO_DATE('".StringChecker::Check($_POST['date_from'])."','%d.%m.%Y')" : null);
    ($_POST['date_to'] != null ? $filter[] = "a.datum_dostave <= STR_TO_DATE('".StringChecker::Check($_POST['date_to'])."','%d.%m.%Y')" : null);
    ($_POST['deliverer_id'] != null ? $filter[] = "a.deliverer_id = '".StringChecker::Check($_POST['deliverer_id'])."'" : null);
    ($_POST['team'] != null ? $filter[] = "p.presentation_team = '".$_POST['team']."'" : null);
    if ( $_SESSION['UserInfo']['user_type'] == 0 ) {
       
        if($permissions->hasCRMPermission(30)){
            //paging
            $sql = "SELECT 
                            a.order_creator_id, a.id, a.firstname, a.surname, a.post_code, a.city, a.address, a.phone1, a.phone2, a.delivery_finish_amount
                            a.avans_amount, a.avans_credit_amount, a.tip_narocila, a.product_price, a.product_amount,
                            b.product_name, p.presentation_date, p.presentation_time, p.presentation_location, p.presentation_city, p.presentation_team
                            c.avansCheck as check1, c.finalPayment as check2, c.avansComment as avansComment, c.finalPaymentComment 
                    FROM 
                        ".WARPIT_CRM."._PresentatorPermissions as p
                        RIGHT JOIN ".WARPIT_CRM."._visitStatistic as a
                        ON p.id_project = a.id_project 
                        AND p.cati_day = a.dan
                        AND p.cati_hour = a.ura
                        AND p.cati_location = a.cati_location  
                        LEFT JOIN ".WARPIT_CRM."._Products as b 
                        ON a.article_id = b.id
                        LEFT JOIN ".WARPIT_CRM."._checkingAccount as c
                        ON c.customerId = a.id";
            $sql .= " WHERE ".implode(" AND ", $filter);
        }else{
            //paging
            $sql = "SELECT 
                            a.order_creator_id, a.id, a.firstname, a.surname, a.post_code, a.city, a.address, a.phone1, a.phone2, a.delivery_finish_amount
                            a.avans_amount, a.avans_credit_amount, a.tip_narocila, a.product_price, a.product_amount,
                            b.product_name, p.presentation_date, p.presentation_time, p.presentation_location, p.presentation_city, p.presentation_team,  
                            c.avansCheck as check1, c.finalPayment as check2, c.avansComment as avansComment, c.finalPaymentComment 
                    FROM 
                        ".WARPIT_CRM."._PresentatorPermissions as p
                        RIGHT JOIN ".WARPIT_CRM."._visitStatistic as a
                        ON p.id_project = a.id_project 
                        AND p.cati_day = a.dan
                        AND p.cati_hour = a.ura
                        AND p.cati_location = a.cati_location  
                        LEFT JOIN ".WARPIT_CRM."._Products as b 
                        ON a.article_id = b.id
                        LEFT JOIN ".WARPIT_CRM."._checkingAccount as c
                        ON c.customerId = a.id
                    WHERE 
                        CONCAT(id_project,'-',ura,'-',dan,'-',cati_location) IN 
                                (SELECT CONCAT(id_project,'-',cati_hour,'-',cati_day,'-',cati_location) 
                                FROM 
                                    ".WARPIT_CRM."._PresentatorPermissions
                                WHERE 
                                    presentation_team IN
                                        (SELECT 
                                                id_group 
                                        FROM 
                                                ".DB_WARPIT_WEBCATI_BASE."._group_link 
                                        WHERE 
                                            id = ".$_SESSION['UserInfo']['id']."
                                        )
                                )";         
            $sql .= " AND ".implode(" AND ",$filter);
        }
    } else {
        //paging
        $sql =  "SELECT 
                        a.order_creator_id, a.id, a.firstname, a.surname, a.post_code, a.city, a.address, a.phone1, a.phone2, a.delivery_finish_amount,
                        a.avans_amount, a.avans_credit_amount, a.tip_narocila, a.product_price, a.product_amount,
                        b.product_name, p.presentation_date, p.presentation_time, p.presentation_location, p.presentation_city, p.presentation_team,
                        c.avansCheck as check1, c.finalPayment as check2, c.avansComment as avansComment, c.finalPaymentComment 
                 FROM 
                        ".WARPIT_CRM."._PresentatorPermissions as p
                        RIGHT JOIN ".WARPIT_CRM."._visitStatistic as a
                        ON p.id_project = a.id_project 
                        AND p.cati_day = a.dan
                        AND p.cati_hour = a.ura
                        AND p.cati_location = a.cati_location  
                        LEFT JOIN ".WARPIT_CRM."._Products as b 
                        ON a.article_id = b.id 
                        LEFT JOIN ".WARPIT_CRM."._checkingAccount as c
                        ON c.customerId = a.id";
        $sql .= " WHERE ".implode(" AND ",$filter);
    }
    $r['sql'] = $sql;
    
    $res = $db->SQLexecute($sql);
    while($row = $db->fetchAssoc($res)){
        if($row['order_creator_id']){
            $t = explode("_",$row['order_creator_id']);
            if($t[0] == "i"){
                $sqlTeam = "SELECT a.Firstname, a.Surname, b.idLab
                            FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer a
                            LEFT
                            JOIN ".DB_WARPIT_WEBCATI_BASE."._group_link gl ON gl.id = a.id
                            LEFT JOIN ".DB_WARPIT_WEBCATI_BASE."._groups b ON gl.id_group = b.id
                            WHERE a.id = '".$t[1]."'";
            }elseif($t[0] == "u"){
                $sqlTeam = "SELECT Firstname, Surname, 'Administrator' as idLab
                            FROM ".DB_WARPIT_MAIN."._user
                            WHERE id = '".$t[1]."'";
            }
            
            $resTeam = $db->SQLexecute($sqlTeam);
            $zadTeam = $db->fetchAssoc($resTeam);
        }else{
            $sqlTeam = "SELECT '' as Firstname, '' as Surname, idLab
                        FROM ".DB_WARPIT_WEBCATI_BASE."._groups
                        WHERE id = '".$row['presentation_team']."'";
                        $r['teamSql'][] = $sqlTeam;
            
            $resTeam = $db->SQLexecute($sqlTeam);
            $zadTeam = $db->fetchAssoc($resTeam);
        }
        
        $locationDate = date_format(new DateTime($row['presentation_date']), 'd.m.Y');
        $row['location'] = $row['presentation_location']."<br />".$row['presentation_city']."<br />".$locationDate." ".substr($row['presentation_time'], 0, 5)."";
        $row['team'] = "".$zadTeam['idLab']."<br />".$zadTeam['Firstname']." ".$zadTeam['Surname']."";
        $row['customer'] = "  ".$LangPack->GetLabel("CRM_order_grid", "purchaser_id","ID").": ".$row['id']."<br />
                                        ".$LangPack->GetLabel("CRM_order_grid", "purchaser_name","Ime").": ".$row['firstname']." ".$row['surname']."<br />
                                        ".$LangPack->GetLabel("CRM_order_grid", "purchaser_post_code","Pošta").": ".$row['post_code']."<br />
                                        ".$LangPack->GetLabel("CRM_order_grid", "purchaser_city","Mesto").": ".$row['city']."<br />
                                        ".$LangPack->GetLabel("CRM_order_grid", "purchaser_naslov","Naslov").": ".$row['address']."<br />
                                        ".$LangPack->GetLabel("CRM_order_grid", "purchaser_phone_1","Tel 1").": ".$row['phone1']."<br />
                                        ".$LangPack->GetLabel("CRM_order_grid", "purchaser_phone_2","Tel 2").": ".$row['phone2']."<br />";
        
        if ($row['avans_amount']){
            $row['avans_amount'] = $row['avans_amount']." ".MONEY_VALUE; 
            $row['avans_credit_amount'] = $row['avans_credit_amount']." ".MONEY_VALUE;
        }else {
            $row['avans_amount'] = " / ";
            $row['avans_credit_amount'] = " / ";
        }
        if($row['tip_narocila'] == 5){
            $row['product_price'] = round($row['product_amount'] * ($row['product_price']),2)." ".MONEY_VALUE;
        }else{
          $row['product_price'] = round($row['product_amount'] * $row['product_price'],2)." ".MONEY_VALUE;
        }
        $row['purchase_data'] =  $row['product_amount']." x ".$row['product_name'];
        
        $sql = "SELECT * FROM ".WARPIT_CRM."._statusLog WHERE id_order='".$row['id']."' ORDER BY status_date";
        $rez = $db->SQLexecute($sql);
        
        if ($db->NumRows($rez) > 0) {
            
            $row['status_history'] = "<table border=0><tr bgcolor=#757575><td style='padding:2px; color:white '>".$LangPack->GetLabel("CRM_order_grid", "change_history_date_label","datum")."</td>
                                          <td style='padding:2px; color:white'>".$LangPack->GetLabel("CRM_order_grid", "change_history_status_label","sprememba")."</td>
                                          <td style='padding:2px; color:white'>".$LangPack->GetLabel("CRM_order_grid", "change_history_note_label","opomba")."</td></tr>";
            
            while ($dat = $db->fetchAssoc($rez)) {
                $x = explode(" ",$dat['status_date']);
                $t = explode("-",$x[0]);
                $date = $t[2].".".$t[1].".".$t[0];
                if($dat['user_type'] >= 0){
                    $table = $dat['user_type'] == 1 ? DB_WARPIT_MAIN."._user" : DB_WARPIT_WEBCATI_BASE."._Interviewer";
                    $sql = "SELECT CONCAT_WS(' ', Firstname, Surname) as Name FROM ".$table." WHERE id = ".$dat['log_user'];
                    
                    $res3 = $db->SQLexecute($sql);
                    $dat3 = $db->fetchAssoc($res3);
                    $logUser = "(".$dat3['Name'].")";
                }
                if($dat['status_state'] == 3){
                    $tmpChangedDate = new DateTime($dat['status_changed_date']);
                    $changedDate = date_format($tmpChangedDate, 'd.m.Y');
                    
                    if($dat['status_comment'] != ""){
                        $row['status_history'] .= "<tr><td style='padding:5px; '>".$date."</td><td style='padding:5px; '>".$stanje[$dat['status_state']]."</td>
                                                       <td style='padding:5px; '>".$dat['status_comment']."<br />Spremenjeno na: ".$changedDate." ".$logUser."</td></tr>";  
                        
                    } else {
                        $row['status_history'] .= "<tr><td style='padding:5px; '>".$date."</td><td style='padding:5px; '>".$stanje[$dat['status_state']]."</td>
                                                       <td style='padding:5px; '>Spremenjeno na: ".$changedDate." ".$logUser."</td></tr>";
                    }
                        
                } else {
                    $row['status_history'] .= "<tr><td style='padding:5px; '>".$date."</td><td style='padding:5px; '>".$stanje[$dat['status_state']]."</td><td style='padding:5px; '>".$dat['status_comment']." ".$logUser."</td></tr>";   
                }
            }
            $row['status_history'] .= "</table>";
        }
        $row['delivery_finish_amount'] = $row['delivery_finish_amount']." ".MONEY_VALUE;
        $data[] = $row;
    }
    
    
    $r['success'] = true;
    $r['results'] = sizeof($data);
    $r['rows'] = $data != null ? $data : "";
    echo json_encode($r);
}elseif($_POST['action'] == "check"){
    $field = $_POST['field'] == 'check1' ? "avansCheck" : "finalPayment";
    $dateField = $_POST['field'] == 'check1' ? "avansCheckDate" : "finalPaymentCheckDate";
    $sql = "SELECT * FROM ".WARPIT_CRM."._checkingAccount WHERE customerId = ".$_POST['id'];
    $res = $db->SQLexecute($sql);
    if($db->NumRows($res) > 0){
        $sql = "UPDATE ".WARPIT_CRM."._checkingAccount SET ".$field." = ".$_POST['value'].", ".$dateField." = NOW() WHERE customerId = ".$_POST['id'];
        $db->SQLexecute($sql);
        $Logger->Log('checkingAccount', 'singe-filed-update', $field, 'WARPIT_CRM._checkingAccount', $_POST['value'] == 1 ? 0 : 1, $_POST['value']);
    }else{
        $sql = "INSERT INTO ".WARPIT_CRM."._checkingAccount 
                (customerId, userType, userId, $field, $dateField) 
                VALUES('".$_POST['id']."', '".$_SESSION['UserInfo']['user_type']."', '".$_SESSION['UserInfo']['id']."', 1, NOW())";
        $db->SQLexecute($sql);
        $Logger->Log('checkingAccount', 'insert-cheking-account', $field, 'WARPIT_CRM._checkingAccount', null, $sql);
    } 
    $r['sql'] = $sql;
    $r['success'] = true;
    echo json_encode($r);
}elseif($_POST['action'] == "uncheck"){
    if($_POST['field'] == 'check1'){
        $field = "avansCheck";   
        $commentField = "avansComment";
        $dateField = "avansUnCheckDate";
    }else{    
        $field = "finalPayment";
        $commentField = "finalPaymentComment";
        $dateField = "finalPaymentUnCheckDate";
    }
    $sql = "UPDATE ".WARPIT_CRM."._checkingAccount SET ".$dateField." = NOW(), ".$field." = ".$_POST['value'].", ".$commentField." = '".StringChecker::Check($_POST['opomba'])."' WHERE customerId = ".$_POST['id'];
    $db->SQLexecute($sql);
    $Logger->Log('checkingAccount', 'singe-filed-update', $field.", ".$commentField, 'WARPIT_CRM._checkingAccount', 1, $sql);    
    $r['sql'] = $sql;
    $r['success'] = true;
    echo json_encode($r);
}elseif($_POST['action'] == "getTeam"){
    
    $sql = "SELECT g.id, g.idLab FROM ".DB_WARPIT_WEBCATI_BASE."._groups g";
    $res = $db->SQLexecute($sql);
    while($row = $db->fetchAssoc($res)){
        $data[] = array("id" => $row['id'], "team" => $row['idLab']);
    }
    $results['success'] = true;
    $results['results'] = sizeof($data);
    $results['rows'] = $data != null ? $data : '';
    echo json_encode($results);
} elseif($_POST['action'] == "getDelivery"){
    $sql = "SELECT id, title FROM ".WARPIT_CRM."._deliverer WHERE deleted = 0 ORDER BY orderBy";
    $res = $db->SQLexecute($sql);
    while($row = $db->fetchAssoc($res)){
        $data[] = array("id" => $row['id'], "title" => $row['title']);
    }
    $results['success'] = true;
    $results['results'] = sizeof($data);
    $results['rows'] = $data != null ? $data : '';
    echo json_encode($results);
}elseif($_POST['action'] == "getOrderTypes"){
    $sql = "SELECT id, naziv_placila FROM ".WARPIT_CRM."._tipiPlacila WHERE deleted = 0 ORDER BY orderBy";
    $res = $db->SQLexecute($sql);
    while($row = $db->fetchAssoc($res)){
        $data[] = array("id" => $row['id'], "title" => $row['naziv_placila']);
    }
    $results['success'] = true;
    $results['results'] = sizeof($data);
    $results['rows'] = $data != null ? $data : '';
    echo json_encode($results);
}