<?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");
require_once (PATH_TO_ROOT."CRM/_lib/excel/PHPExcel.php");

require_once(PATH_TO_ROOT.'TCPDF/config/lang/eng.php');
require_once(PATH_TO_ROOT.'TCPDF/tcpdf.php');
//ini_set("display_errors", 1);

$Logger = new LoggerX($db);

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


if($_POST['action'] == "update"){
	$datefield = StringChecker::Check($_POST['field'])."_date";
	$sql = "UPDATE ".WARPIT_CRM."._visitStatistic SET ".StringChecker::Check($_POST['field'])." = '".StringChecker::Check($_POST['value'])."', $datefield = NOW() WHERE id = ".StringChecker::Check($_POST['id']);
	$Logger->Log('sprememba_'.StringChecker::Check($_POST['field']), 'single-field-update', StringChecker::Check($_POST['field']), WARPIT_CRM."._visitStatistic", StringChecker::Check($_POST['oldValue']), addslashes($sql));
	$db->SQLexecute($sql);
	
	echo "{ success : true, sql: '".$sql."'}";

} elseif($_POST['action'] == "get"){
		$filter[] = "order_status = 2";
		$filter[] = "activityId = 5";
		$check = $_POST['dateCheck'];
        if($check == null)
            $check = 0;
		if($_POST['time_from'] != null && $_POST['time_to'] != null){
			
			$filter[] =  "(STR_TO_DATE('".StringChecker::Check($_POST['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 3 MONTH) AND month3Check = '".$check."' AND STR_TO_DATE('".StringChecker::Check($_POST['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 3 MONTH) AND month3Check = '".$check."') OR
							    (STR_TO_DATE('".StringChecker::Check($_POST['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 6 MONTH) AND month6Check = '".$check."' AND   STR_TO_DATE('".StringChecker::Check($_POST['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 6 MONTH) AND month6Check = '".$check."') OR
							    (STR_TO_DATE('".StringChecker::Check($_POST['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 9 MONTH) AND month9Check = '".$check."' AND STR_TO_DATE('".StringChecker::Check($_POST['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 9 MONTH) AND month9Check = '".$check."') OR
							    (STR_TO_DATE('".StringChecker::Check($_POST['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 15 MONTH) AND month15Check = '".$check."') AND STR_TO_DATE('".StringChecker::Check($_POST['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 15 MONTH) AND month15Check = '".$check."'";		
		}
		
										
		
		
		($_POST['firstname'] != null ? $filter[] =  "firstname LIKE '".StringChecker::Check($_POST['firstname'])."%'" : "")." ";
		($_POST['surname'] != null ? $filter[] = "surname LIKE '".StringChecker::Check($_POST['surname'])."%'" : "")." ";
		
		if($_POST['showLate'] == "true"){
			$arr = array();
			$sql = "SELECT  DISTINCT(v.id_tel), 
						v.id, 
						address, 
						marital_status, 
						birth_date, 
						email, 
						phone1, 
						firstname, 
						surname, 
						gender, 
						post_code, 
						city, 
						datum_dostave,
						product_amount, 
						article_id, 
						product_price,
						month3Check,
						month6Check,
						month9Check,
						month15Check,
						month3ReturnCheck,
						month6ReturnCheck,
						month9ReturnCheck,
						month15ReturnCheck 
					FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN 
					".WARPIT_CRM."._customerActivities a 
					ON a.customerId = v.id WHERE ".implode(" AND ", $filter);
			$res = $db->SQLexecute($sql);
			while($row = $db->fetchAssoc($res)){
				$arr[] = array("id" => $row['id'], 
								"month3Check" => $row['month3Check'],
								"month6Check" => $row['month6Check'], 
								"month9Check" => $row['month9Check'], 
								"month15Check" => $row['month15Check'],
								"month3Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 3months")),
								"month6Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 6months")),
								"month9Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 9months")),
								"month15Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 15months")));
			}
			$now = date("d.m.Y");
			$ids = array();
			foreach ($arr as $key => $value) {
				if(strtotime($now) > strtotime($value['month3Date']) && !$value['month3Check'])
					$ids[] = $value['id'];
				if(strtotime($now) > strtotime($value['month6Date']) && !$value['month6Check'])
					$ids[] = $value['id'];
				if(strtotime($now) > strtotime($value['month9Date']) && !$value['month9Check'])
					$ids[] = $value['id'];
				if(strtotime($now) > strtotime($value['month15Date']) && !$value['month15Check'])
					$ids[] = $value['id'];
			}
			$ids = array_unique($ids);
		if(!empty($ids)){
			$sql = "SELECT  DISTINCT(v.id_tel), 
						v.id, 
						address, 
						marital_status, 
						birth_date, 
						email, 
						phone1, 
						firstname, 
						surname, 
						gender, 
						post_code, 
						city, 
						datum_dostave,
						product_amount, 
						article_id, 
						product_price,
						month3Check,
						month6Check,
						month9Check,
						month15Check,
						month3ReturnCheck,
						month6ReturnCheck,
						month9ReturnCheck,
						month15ReturnCheck 
					FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN 
					".WARPIT_CRM."._customerActivities a 
					ON a.customerId = v.id 
					WHERE v.id IN (".implode(", ",$ids).") 
					LIMIT ". StringChecker::Check($_POST['start']). ", ".StringChecker::Check($_POST['limit']);
			$rows = "SELECT  DISTINCT(v.id_tel), 
						v.id, 
						address, 
						marital_status, 
						birth_date, 
						email, 
						phone1, 
						firstname, 
						surname, 
						gender, 
						post_code, 
						city, 
						datum_dostave,
						product_amount, 
						article_id, 
						product_price,
						month3Check,
						month6Check,
						month9Check,
						month15Check,
						month3ReturnCheck,
						month6ReturnCheck,
						month9ReturnCheck,
						month15ReturnCheck 
					 FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN 
					 ".WARPIT_CRM."._customerActivities a 
					 ON a.customerId = v.id  
					 WHERE v.id IN (".implode(", ",$ids).")";
			$res = $db->SQLexecute($sql);
			while($row = $db->fetchAssoc($res)){
				if($row['article_id'] != 0){
					$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
					$rez = $db->SQLexecute($sql);
					$zad = $db->fetchAssoc($rez);
					$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
				}
				$row['month3Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 3months"));
				$row["month6Date"] = date("d.m.Y",strtotime($row['datum_dostave']." + 6months"));
				$row['month9Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 9months"));
				$row['month15Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 15months"));
				$data[] = $row;
			}
			$results['success'] = true;
			$results['results'] = $db->NumRows($db->SQLexecute($rows));
			$results['rows'] = $data != null ? $data : "";
		}else{
			$results['success'] = true;
			$results['results'] = 0;
			$results['rows'] = "";
		}
			
		echo json_encode($results);
	}else{
		$sql = "SELECT DISTINCT(v.id_tel), 
						v.id, 
						address, 
						marital_status, 
						birth_date, 
						email, 
						phone1, 
						firstname, 
						surname, 
						gender, 
						post_code, 
						city, 
						datum_dostave,
						product_amount, 
						article_id, 
						product_price,
						month3Check,
						month6Check,
						month9Check,
						month15Check,
						month3ReturnCheck,
						month6ReturnCheck,
						month9ReturnCheck,
						month15ReturnCheck 
				FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN 
				".WARPIT_CRM."._customerActivities a 
				ON a.customerId = v.id 
			 WHERE ".implode(" AND ",$filter);
		//echo "/*$sql*/";
		$res = $db->SQLexecute($sql);
		while($row = $db->fetchAssoc($res)){
			if($row['article_id'] != 0){
				$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
				$rez = $db->SQLexecute($sql);
				$zad = $db->fetchAssoc($rez);
				$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
			}
			$row['month3Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 3months"));
			$row["month6Date"] = date("d.m.Y",strtotime($row['datum_dostave']." + 6months"));
			$row['month9Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 9months"));
			$row['month15Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 15months"));
			$data[] = $row;
		}
		$results['success'] = true;
		$results['results'] = sizeof($data);
		$results['rows'] = $data != null ? $data : "";
		echo json_encode($results);
	}
}elseif($_POST['action'] == "submit"){
	$date = StringChecker::Check($_POST['date']);
	$comment = StringChecker::Check($_POST['comment']);
	$id = StringChecker::Check($_POST['id']);
	$sql = "UPDATE ".WARPIT_CRM."._visitStatistic SET add_payment_date = STR_TO_DATE ('".$date."','%d.%m.%Y'), add_payment_comment = '".$comment."' WHERE id=".$id;
	$db->SQLexecute($sql);
}elseif($_GET['action'] == "print"){
	if(!StringChecker::Check(isset($_GET['history']))){
		$filter[] = "order_status = 2";
		$filter[] = "activityId = 5";
		$check = $_GET['dateChecked'];
		($_GET['firstname'] != null ? $filter[] = "firstname LIKE '".StringChecker::Check($_GET['firstname'])."%'" : "");
        ($_GET['surname'] != null ? $filter[] = "surname LIKE '".StringChecker::Check($_GET['surname'])."%'" : "");
        
        
        if($_GET['time_from'] != null && $_GET['time_to'] != null){
            
            $filter[] =  "(STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 3 MONTH) AND month3Check = '".$check."' AND STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 3 MONTH) AND month3Check = '".$check."') OR
                                (STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 6 MONTH) AND month6Check = '".$check."' AND   STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 6 MONTH) AND month6Check = '".$check."') OR
                                (STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 9 MONTH) AND month9Check = '".$check."' AND STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 9 MONTH) AND month9Check = '".$check."') OR
                                (STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 15 MONTH) AND month15Check = '".$check."') AND STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 15 MONTH) AND month15Check = '".$check."'";       
        }
		
		if($_GET['showLate'] == "true"){
			$arr = array();
			$sql =   "   SELECT * 
			             FROM ".WARPIT_CRM."._visitStatistic v 
			             LEFT JOIN ".WARPIT_CRM."._customerActivities a 
			             ON a.customerId = v.id 
			             WHERE ".implode(" AND ", $filter);
			$res = $db->SQLexecute($sql);
			$ids = array();	
			$now = date("d.m.Y");
			while($row = $db->fetchAssoc($res)){
				if(strtotime($now) > strtotime($row['month3Date']) && !$row['month3Check'])
					$ids[] = $row['id'];
				if(strtotime($now) > strtotime($row['month6Date']) && !$row['month6Check'])
					$ids[] = $row['id'];
				if(strtotime($now) > strtotime($row['month9Date']) && !$row['month9Check'])
					$ids[] = $row['id'];
				if(strtotime($now) > strtotime($row['month15Date']) && !$row['month15Check'])
					$ids[] = $row['id'];
			}
			$ids = array_unique($ids);
			if(!empty($ids)){
				$sql = "SELECT * FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN ".WARPIT_CRM."._customerActivities a ON a.customerId = v.id WHERE v.id IN (".implode(", ",$ids).")";
				$res = $db->SQLexecute($sql);
				while($row = $db->fetchAssoc($res)){
					if($row['article_id'] != 0){
						$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
						$rez = $db->SQLexecute($sql);
						$zad = $db->fetchAssoc($rez);
						$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
					}
					if($row['gender'] == 1){
						$row['gender'] = $LangPack->GetLabel("CRM_all", "male_gender_title","Moški");
					}elseif($row['gender'] == 2){
						$row['gender'] = $LangPack->GetLabel("CRM_all", "female_gender_title","Ženski");
					}elseif($row['gender'] == 0){
						$row['gender'] = "";
					}if($row['birth_date'] == "0000-00-00"){
						$row['birth_date'] = "";
					}if($row['marital_status'] == 2){
						$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_maried_title","Poročen");
					}elseif($row['marital_status'] == 1){
						$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_single_title","Samski");
					}elseif($row['marital_status'] == 0){
						$row['marital_status'] = "";
					}
					if(strtotime($now) > strtotime($row['month3Date'])){
						$row['color'] = "efc6c6";
					}
					if(strtotime($now) > strtotime($row['month6Date'])){
						$row['color'] = "efc6c6";
					}
					if(strtotime($now) > strtotime($row['month9Date'])){
						$row['color'] = "efc6c6";
					}
					if(strtotime($now) > strtotime($row['month15Date'])){
						$row['color'] = "efc6c6";
					}
					$data[] = $row;
				}
			}
		}else{
			$sql =   "   SELECT * 
			             FROM ".WARPIT_CRM."._visitStatistic v 
		                 LEFT JOIN ".WARPIT_CRM."._customerActivities a 
		                 ON a.customerId = v.id  
		                 WHERE ".implode(" AND ",$filter);
                         
			$res = $db->SQLexecute($sql);
			$now = date("d.m.Y");
			while($row = $db->fetchAssoc($res)){
				if($row['article_id'] != 0){
					$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
					$rez = $db->SQLexecute($sql);
					$zad = $db->fetchAssoc($rez);
					$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
				}
				if($row['gender'] == 1){
					$row['gender'] = $LangPack->GetLabel("CRM_all", "male_gender_title","Moški");
				}elseif($row['gender'] == 2){
					$row['gender'] = $LangPack->GetLabel("CRM_all", "female_gender_title","Ženski");
				}elseif($row['gender'] == 0){
					$row['gender'] = "";
				}if($row['birth_date'] == "0000-00-00"){
					$row['birth_date'] = "";
				}if($row['marital_status'] == 2){
					$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_maried_title","Poročen");
				}elseif($row['marital_status'] == 1){
					$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_single_title","Samski");
				}elseif($row['marital_status'] == 0){
					$row['marital_status'] = "";
				}			
				$data[] = $row;			
			}
		}
	}else{
		$printId = StringChecker::Check($_GET['print_id']);
		$sql = "SELECT * FROM ".WARPIT_CRM."._actionLogger a JOIN ".WARPIT_CRM."._visitStatistic v ON v.id_tel = a.id_tel WHERE id_print = ".$printId." AND id_action = 2";
		$res = $db->SQLexecute($sql);
		while($row = $db->fetchAssoc($res)){
			if($row['article_id'] != 0){
				$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
				$rez = $db->SQLexecute($sql);
				$zad = $db->fetchAssoc($rez);
				$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
			}
			if($row['gender'] == 1){
				$row['gender'] = $LangPack->GetLabel("CRM_all", "male_gender_title","Moški");
			}elseif($row['gender'] == 2){
				$row['gender'] = $LangPack->GetLabel("CRM_all", "female_gender_title","Ženski");
			}elseif($row['gender'] == 0){
				$row['gender'] = "";
			}if($row['birth_date'] == "0000-00-00"){
				$row['birth_date'] = "";
			}if($row['marital_status'] == 2){
				$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_maried_title","Poročen");
			}elseif($row['marital_status'] == 1){
				$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_single_title","Samski");
			}elseif($row['marital_status'] == 0){
				$row['marital_status'] = "";
			}		
			$data[] = $row;
		}
	}
	$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
		
	// set default header data	
	// set header and footer fonts
	
	$pdf->setPrintFooter(false);
	$pdf->setPrintHeader(false);
	//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, '', '');
	
	// set default monospaced font
	$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
	$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
	
	//set margins
	$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_DELOVENE_POGODBE_DESNO);
	
	//set auto page breaks
	$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
	
	//set image scale factor
	$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
	
	//set some language-dependent strings
	$lg = Array();
	$lg['a_meta_charset'] = 'UTF-8';
	$lg['a_meta_language'] = 'slv';
	$lg['w_page'] = 'Stran';
	$pdf->setLanguageArray($lg);
	$pdf->setHeaderTemplateAutoreset(TRUE);
	// ---------------------------------------------------------
	
	// set default font subsetting mode
	$pdf->setFontSubsetting(true);
	
	// Set font
	// dejavusans is a UTF-8 Unicode font, if you only need to
	// print standard ASCII chars, you can use core fonts like
	// helvetica or times to reduce file size.
	$pdf->SetFont('arial', '', PDF_DELOVENE_POGODBE_FONTA, '', true);
	$sql = "SELECT MAX(id_print) as max FROM ".WARPIT_CRM."._actionLogger";
	$rez = $db->SQLexecute($sql);
	$dat = $db->fetchAssoc($rez);
	$max = intval($dat['max'])+1;
	// Add a page
	// This method has several options, check the source code documentation for more information.
//$fields = array('firstname', 'surname', 'gender', 'post_code', 'city', 'marital_status', 'birth_date', 'email', 'phone1', 'productName', 'product_price', 'month3Date', 'month3Check', 'month6Date', 'month6Check', 'month9Date', 'month9Check', 'month15Date', 'month15Check');	
	foreach($data as $item){
		$sql = "SELECT * FROM ".WARPIT_CRM."._actionLogger WHERE id_project = ".$item['id_project']." AND id_tel = (".$item['id_tel'].") AND id_action = 2";
		FB::info($sql);
		$res = $db->SQLexecute($sql);
		if($db->NumRows($res) == 0){
			
			$sql = "INSERT INTO ".WARPIT_CRM."._actionLogger (id_project, id_filter, id_tel, id_action, actionDate, id_print) VALUES ('".$item['id_project']."', 0, ".$item['id_tel'].", 2, NOW(), $max)";
			$Logger->Log('dodajanje_v_actionLogger', 'multiple-filed-insert', 'id_project, id_filter, id_tel, id_action, actionDate, id_print', 
	  			  		  WARPIT_CRM."._actionLogger", " ", addslashes($sql));
			$db->SQLexecute($sql);
		}
		$pdf->AddPage();
			//$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$html = <<<EOD
		{$item['firstname']} {$item['surname']}<br>
		{$item['address']}<br>
		{$item['post_code']}, {$item['city']}<br>
EOD;
	$pdf->writeHTMLCell($w=0, $h=0, $x=PDF_DELOVENE_POGODBE_LEVO, $y=PDF_DELOVENE_POGODBE_ZGORAJ, $html, $border=0, $ln=1, $fill=0, $reseth=true, $align='', $autopadding=true);
}
	 
	// Close and output PDF document
	// This method has several options, check the source code documentation for more information.
	
	$pdf->Output($fileName, 'I');
}elseif($_POST['action'] == "getHistory"){
	$sql = "SELECT id_print, actionDate, COUNT(id) as c FROM ".WARPIT_CRM."._actionLogger WHERE id_action = 2 GROUP BY id_print LIMIT ".$_POST['start'].", ".$_POST['limit'];
	$rows = "SELECT id_print, actionDate, COUNT(id) as c FROM ".WARPIT_CRM."._actionLogger WHERE id_action = 2 GROUP BY id_print";
	$res = $db->SQLexecute($sql);
	while($row = $db->fetchAssoc($res)){
		$row['count'] = $row['c'];
		$row['actionDate'] = date_format(new DateTime($row['actionDate']),"d.m.Y H:i:s");
		$data[] = $row;
	}
	$r['success'] = true;
	$r['results'] = $db->NumRows($db->SQLexecute($rows));
	$r['rows'] = $data != null ? $data : "";
	echo json_encode($r);
}elseif($_GET['action'] == "export"){
	$filter[] = "order_status = 2";
	$filter[] = "activityId = 5";
	$check = $_GET['dateChecked'];
    ($_GET['firstname'] != null ? $filter[] = "firstname LIKE '".StringChecker::Check($_GET['firstname'])."%'" : "");
    ($_GET['surname'] != null ? $filter[] = "surname LIKE '".StringChecker::Check($_GET['surname'])."%'" : "");
    
    
    if($_GET['time_from'] != null && $_GET['time_to'] != null){
        
        $filter[] =  "(STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 3 MONTH) AND month3Check = '".$check."' AND STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 3 MONTH) AND month3Check = '".$check."') OR
                            (STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 6 MONTH) AND month6Check = '".$check."' AND   STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 6 MONTH) AND month6Check = '".$check."') OR
                            (STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 9 MONTH) AND month9Check = '".$check."' AND STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 9 MONTH) AND month9Check = '".$check."') OR
                            (STR_TO_DATE('".StringChecker::Check($_GET['time_from'])."', '%d.%m.%Y') <= DATE(datum_dostave + INTERVAL 15 MONTH) AND month15Check = '".$check."') AND STR_TO_DATE('".StringChecker::Check($_GET['time_to'])."', '%d.%m.%Y') >= DATE(datum_dostave + INTERVAL 15 MONTH) AND month15Check = '".$check."'";       
    }
        
	if($_GET['showLate'] == "true"){
		$arr = array();
		$sql = "SELECT * FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN ".WARPIT_CRM."._customerActivities a ON a.customerId = v.id WHERE ".implode(" AND ", $filter);
		$res = $db->SQLexecute($sql);
		while($row = $db->fetchAssoc($res)){
			$arr[] = array("id" => $row['id'], 
							"month3Check" => $row['month3Check'],
							"month6Check" => $row['month6Check'], 
							"month9Check" => $row['month9Check'], 
							"month15Check" => $row['month15Check'],
							"month3Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 3months")),
							"month6Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 6months")),
							"month9Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 9months")),
							"month15Date" => date("d.m.Y",strtotime($row['datum_dostave']." + 15months")));
		}
		$now = date("d.m.Y");
		$ids = array();
		foreach ($arr as $key => $value) {
			if(strtotime($now) > strtotime($value['month3Date']) && !$value['month3Check'])
				$ids[] = $value['id'];
			if(strtotime($now) > strtotime($value['month6Date']) && !$value['month6Check'])
				$ids[] = $value['id'];
			if(strtotime($now) > strtotime($value['month9Date']) && !$value['month9Check'])
				$ids[] = $value['id'];
			if(strtotime($now) > strtotime($value['month15Date']) && !$value['month15Check'])
				$ids[] = $value['id'];
		}
		$ids = array_unique($ids);
		if(!empty($ids)){
			$sql = "SELECT * FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN ".WARPIT_CRM."._customerActivities a ON a.customerId = v.id WHERE v.id IN (".implode(", ",$ids).")";
			$res = $db->SQLexecute($sql);
			while($row = $db->fetchAssoc($res)){
				if($row['article_id'] != 0){
					$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
					$rez = $db->SQLexecute($sql);
					$zad = $db->fetchAssoc($rez);
					$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
				}
				if($row['gender'] == 1){
					$row['gender'] = $LangPack->GetLabel("CRM_all", "male_gender_title","Moški");
				}elseif($row['gender'] == 2){
					$row['gender'] = $LangPack->GetLabel("CRM_all", "female_gender_title","Ženski");
				}elseif($row['gender'] == 0){
					$row['gender'] = "";
				}if($row['birth_date'] == "0000-00-00"){
					$row['birth_date'] = "";
				}if($row['marital_status'] == 2){
					$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_maried_title","Poročen");
				}elseif($row['marital_status'] == 1){
					$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_single_title","Samski");
				}elseif($row['marital_status'] == 0){
					$row['marital_status'] = "";
				}if($row['month3Check'] == 0){
					$row['month3Check'] = "";
				}if($row['month6Check'] == 0){
					$row['month6Check'] = "";
				}if($row['month9Check'] == 0){
					$row['month9Check'] = "";
				}if($row['month15Check'] == 0){
					$row['month15Check'] = "";
				}if($row['month3Check'] == 1){
					$row['month3Check'] = "X";
				}if($row['month6Check'] == 1){
					$row['month6Check'] = "X";
				}if($row['month9Check'] == 1){
					$row['month9Check'] = "X";
				}if($row['month15Check'] == 1){
					$row['month15Check'] = "X";
				}
				
				$row['month3Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 3months"));
				$row["month6Date"] = date("d.m.Y",strtotime($row['datum_dostave']." + 6months"));
				$row['month9Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 9months"));
				$row['month15Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 15months"));
				$row['color'] = "efc6c6";
				$data[] = $row;
			}
		}
	}else{
		$sql = "SELECT * FROM ".WARPIT_CRM."._visitStatistic v LEFT JOIN ".WARPIT_CRM."._customerActivities a ON a.customerId = v.id  WHERE ".implode(" AND ",$filter);
        
		$res = $db->SQLexecute($sql);
		$now = date("d.m.Y");
		while($row = $db->fetchAssoc($res)){
			if($row['article_id'] != 0){
				$sql = "SELECT product_name FROM ".WARPIT_CRM."._Products WHERE id = ".$row['article_id'];
				$rez = $db->SQLexecute($sql);
				$zad = $db->fetchAssoc($rez);
				$row['productName'] = $row['product_amount']."x ".$zad['product_name'];
			}
			if($row['gender'] == 1){
				$row['gender'] = $LangPack->GetLabel("CRM_all", "male_gender_title","Moški");
			}elseif($row['gender'] == 2){
				$row['gender'] = $LangPack->GetLabel("CRM_all", "female_gender_title","Ženski");
			}elseif($row['gender'] == 0){
				$row['gender'] = "";
			}if($row['birth_date'] == "0000-00-00"){
				$row['birth_date'] = "";
			}if($row['marital_status'] == 2){
				$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_maried_title","Poročen");
			}elseif($row['marital_status'] == 1){
				$row['marital_status'] = $LangPack->GetLabel("CRM_all", "marital_status_single_title","Samski");
			}elseif($row['marital_status'] == 0){
				$row['marital_status'] = "";
			}
			$row['month3Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 3months"));
			$row["month6Date"] = date("d.m.Y",strtotime($row['datum_dostave']." + 6months"));
			$row['month9Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 9months"));
			$row['month15Date'] = date("d.m.Y",strtotime($row['datum_dostave']." + 15months"));
			if(strtotime($now) > strtotime($row['month3Date'])){
				$row['color'] = "efc6c6";
			}
			if(strtotime($now) > strtotime($row['month6Date'])){
				$row['color'] = "efc6c6";
			}
			if(strtotime($now) > strtotime($row['month9Date'])){
				$row['color'] = "efc6c6";
			}
			if(strtotime($now) > strtotime($row['month15Date'])){
				$row['color'] = "efc6c6";
			}
			if($row['month3Check'] == 0){
				$row['month3Check'] = "";
			}if($row['month6Check'] == 0){
				$row['month6Check'] = "";
			}if($row['month9Check'] == 0){
				$row['month9Check'] = "";
			}if($row['month15Check'] == 0){
				$row['month15Check'] = "";
			}if($row['month3Check'] == 1){
				$row['month3Check'] = "X";
			}if($row['month6Check'] == 1){
				$row['month6Check'] = "X";
			}if($row['month9Check'] == 1){
				$row['month9Check'] = "X";
			}if($row['month15Check'] == 1){
				$row['month15Check'] = "X";
			}
			if($row['month3Check'] && $row['month3Check'] && $row['month9Check'] && $row['month15Check'])
				$row['color'] = "d6ffce";
			
			
			$data[] = $row;
			
			
		}
	}
	$abc = range("A","Z");
	
	$config['firstname'] = array("width" => "15", "title" => $LangPack->GetLabel("finance_export", "firstname_title","Ime"));
	$config['surname'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "surname_title","Priimek"));
	$config['gender'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "gender_title","Spol"));
	$config['post_code'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "post_code_title","Poštna številka"));
	$config['city'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "city_title","Mesto"));
	$config['marital_status'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "marital_status_title","Zakonski stan"));
	$config['birth_date'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "birth_date_title","Datum rojstva"));
	$config['email'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "email_title","Email"));
	$config['phone1'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "phone1_title","Telefon"));
	$config['productName'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "productName_title","Produkt"));
	$config['product_price'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "product_price_title","Cena (enega)"));
	$config['month3Date'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month3Date_title","3 mesce od dostave"));
	$config['month3Check'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month3Check_title","3 mescev"));
	$config['month6Date'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month6Date_title","6 mesce od dostave"));
	$config['month6Check'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month6Check_title","6 mescev"));
	$config['month9Date'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month9Date_title","9 mesce od dostave"));
	$config['month9Check'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month9Check_title","9 mescev"));
	$config['month15Date'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month15Date_title","15 mesce od dostave"));
	$config['month15Check'] = array("width" => "25", "title" => $LangPack->GetLabel("finance_export", "month15Check_title","15 mescev"));
	
	$fields = array('firstname', 'surname', 'gender', 'post_code', 'city', 'marital_status', 'birth_date', 'email', 'phone1', 'productName', 'product_price', 'month3Date', 'month3Check', 'month6Date', 'month6Check', 'month9Date', 'month9Check', 'month15Date', 'month15Check');
	
	$orderBy['firstname'] = 0;
	$orderBy['surname'] = 1;
	$orderBy['gender'] = 2;
	$orderBy['post_code'] = 3;
	$orderBy['city'] = 4;
	$orderBy['marital_status'] = 5;
	$orderBy['birth_date'] = 6;
	$orderBy['email'] = 7;
	$orderBy['phone1'] = 8;
	$orderBy['productName'] = 9;
	$orderBy['product_price'] = 10;
	$orderBy['month3Date'] = 11;
	$orderBy['month3Check'] = 12;
	$orderBy['month6Date'] = 13;
	$orderBy['month6Check'] = 14;
	$orderBy['month9Date'] = 15;
	$orderBy['month9Check'] = 16;
	$orderBy['month15Date'] = 17;
	$orderBy['month15Check'] = 18;
	
	$orderedStatus = array();
	foreach ($fields as $key => $value) {
		$orderedStatus[$orderBy[$value]] = $value;
	}
	ksort($orderedStatus);
	$orderedStatus = array_values($orderedStatus);
	
	
	$objPHPExcel = new PHPExcel();
	foreach ($orderedStatus as $key => $value) {
		if($key > 25){
			$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
		}
		else
			$letter = $abc[$key];
		
		$objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($config[$value]['width']);
		$objPHPExcel->getActiveSheet()->setCellValue($letter."1", $config[$value]['title']);	
		$objPHPExcel->getActiveSheet()->getStyle($letter."1")->getFont()->setBold(true);
	}
	$i=2;
	foreach( $data as $key1 => $value1 )
	{
		foreach ($orderedStatus as $key => $value) {
			if($key > 25){
				$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
			}
			else
				$letter = $abc[$key];
			
			$objPHPExcel->getActiveSheet()->setCellValue($letter.$i, $value1[$value]);
			if(isset($value1['color']))
				cellColor($letter.$i, $value1['color']);
			
		}
	    $i++;
	}
	$fileName = $LangPack->GetLabel("delivery_contract_export", "file_name","DeliveryContract_export");
	$fileName .= ".xls";
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment; filename="' . $fileName . '"');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
}
function cellColor($cells,$color){
    global $objPHPExcel;
    $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()
	    ->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
	    'startcolor' => array('rgb' => $color)
    ));
}
?>