<?php
session_start();
header('Content-Type: text/html; charset=utf-8');
//echo chr(239).chr(187).chr(191);
//require_once("../settings.php");

$dbset = json_decode($_SESSION['settingsApp'],true);
$DB = new mysqli ($dbset['DB']['DB_HOST'],$dbset['DB']['DB_USER'],$dbset['DB']['DB_PASS'],$dbset['DB']['DB_NAME']);
$DB->query("SET CHARSET SET utf8");
$DB->query("SET NAMES 'utf8'");
if (mysqli_connect_errno()) {
	echo "Error connecting to DB";
	exit;
}

 //-----------------------------------Получение результатов по стоянкам---------------------------------------------


if ($_POST['func']=='getResult')
{
    $_SESSION['columnPromo'] = array();
    $i = 5;
    $dateFrom = $_POST['dateFrom'];
    $dateTo = $_POST['dateTo'];
    $ObjReport['model'] = '["result_1","result_2","result_3","result_4",';
    $ObjReport['tableRezult'] = "[{text: 'Отчёт с ".$dateFrom." по ".$dateTo."',columns : [";
    $ObjReport['tableRezult'] .= "{text : 'Участие в супермаркетах',dataIndex : 'result_1',width: 100,sortable: true,hideable: false},";
    $ObjReport['tableRezult'] .= "{text : 'Участие в гипермаркетах',dataIndex : 'result_2',width: 100,sortable: true,hideable: false},";
    $ObjReport['tableRezult'] .= "{text : 'Наименование',dataIndex : 'result_3',width: 100,sortable: true,hideable: false},";
    $ObjReport['tableRezult'] .= "{text : 'код ЕТС',dataIndex : 'result_4',width: 100,sortable: true,hideable: false},";
    $listTP = '';
    $ObjReport['store'] = '[';
    $query = "SELECT id FROM ms_promo";
    $stmt = $DB->prepare($query);
    $stmt->execute();
    $stmt->bind_result($id);
    //echo $query;
    while ($stmt->fetch()) 
    {
       $listTP .= $id.',';
    }
    $listTP = substr($listTP, 0, strlen($listTP)-1);  
    $query = "SELECT address,id_promo FROM ms_trade_point WHERE id_promo in (".$listTP.") ORDER BY id_promo";
    //echo $query;
    $stmt = $DB->prepare($query);
    $stmt->execute();
    $stmt->bind_result($address,$id_promo);
    while ($stmt->fetch()) 
    {
       $address = str_replace("'"," ",$address); 
       $_SESSION['columnPromo'][] = $address;       
       $ObjReport['model'] .= '"result_'.$i.'",';
       $_SESSION['arrPromo'][$id_promo]['result_'.$i] = 1; 
       $ObjReport['tableRezult'] .= "{text : '".$address."',dataIndex : 'result_".$i."',width: 100,sortable: true,hideable: false},"; 
       $i++;   
    }
    
    $ObjReport['model'] = substr($ObjReport['model'], 0, strlen($ObjReport['model'])-1);  
    $ObjReport['model'] .= ']';
    $ObjReport['countColumn'] = $i-1;
    $_SESSION['countColumn'] = $i-1;
    $ObjReport['tableRezult'] = substr($ObjReport['tableRezult'], 0, strlen($ObjReport['tableRezult'])-1);
    $ObjReport['tableRezult'] .= "]}]";
    
    $ObjReport['store']=substr($ObjReport['store'], 0, strlen($ObjReport['store'])-1);
    $ObjReport['store'] .= ']';
    echo  json_encode($ObjReport);      
}
 
 
 if ($_GET['func']=='getData')
{
    $dateFrom = $_GET['dateFrom'];
    $dateTo = $_GET['dateTo'];
    $wherePhoto = $_GET['wherePhoto'];
    $idTPList = '0,';
    $query = "SELECT id FROM ms_trade_point WHERE 1 " . $wherePhoto;
    $stmt = $DB->prepare($query);
    $stmt->execute();
    $stmt->bind_result($idTP);
    while ($stmt->fetch()) 
    {
        $idTPList .= $idTP.',';
    }
    $idTPList = substr($idTPList, 0, strlen($idTPList)-1);
    
    $i = 0;
   // $query = "select v.id, tp.name,tp.address,n.name FROM ms_visit v LEFT JOIN ms_trade_point tp ON v.id_trade_point = tp.id LEFT JOIN ms_network n ON tp.id_network = n.id WHERE v.date >= '".$dateFrom."' AND v.date <= '".$dateTo."' AND photo = 1 AND id_trade_point in (".$idTPList.")";
    $query = "SELECT id,name_promo,code,sm,gm FROM ms_promo";
    $stmt = $DB->prepare($query);
    $stmt->execute();
    $stmt->bind_result($id,$name,$code,$sm,$gm);
    while ($stmt->fetch()) 
    { 
        if ($sm == 1) {$sm = "Да";}
        else {$sm = "Нет";}
        
        if ($gm == 1) {$gm = "Да";}
        else {$gm = "Нет";}  
        
        $dataPhoto [$i]= 
        array
        (
                'result_1'=>$sm,
                'result_2'=>$gm,      
                'result_3'=>$name,      
                'result_4'=>$code
        );
        $i++;
    }
    
    $i = 0;
    foreach ($_SESSION['arrPromo'] as $key => $value)
    {
        //echo $key;
        foreach ($_SESSION['arrPromo'][$key] as $key1 => $value1)
        {
            $dataPhoto[$i][$key1] = $value1;
           // echo $key1;   
        }
        
        $i++;
    }
    
     echo'{rows:'.json_encode($dataPhoto).'}';
}
    
if ($_POST['func']=='export_xls')
{
            $ArrayOfData = $_POST['ColumnsArray'];
            require_once '../classes/PHPExcel.php';
            include_once '../classes/PHPExcel/IOFactory.php'; 
            $BoldFont = array
            (
                'font'=>array
                (
                    'name'=>'Arial Cyr',
                    'size'=>'10',
                    'bold'=>true
                )
            );
            
            $Center = array
            (
                'alignment'=>array
                (
                    'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'=>PHPExcel_Style_Alignment::VERTICAL_TOP
                )
            ); 
            $ObjPHPExcel = new PHPExcel();
            
           
            $Column='A';
            $i=0;    
            $Sheet = $ObjPHPExcel->getActiveSheet();
            $Sheet->getStyle("A1")->getFont()->setSize(20);
            $Sheet->getRowDimension('1')->setRowHeight(25);
            $Sheet->setCellValueByColumnAndRow(0,1,'Еженедельный отчёт "Доля в категории"');  
            $Sheet->setTitle('Доля в категории');
           /* while ($Fields[$i])
            {
                $Sheet->getColumnDimension($Column)->setWidth(25);
                $i++;
                $Column++;
            } */
            $i=0; 
            while ($i<=count($_SESSION['columnPromo']))
            {
                $Sheet->getColumnDimension($Column)->setWidth(25);
                if ($Column!='A') 
                {
                    $Sheet->getStyle($Column.'2')->getAlignment()->setTextRotation(90);
                    $Sheet->getColumnDimension($Column)->setWidth(10);
                    $Sheet->getRowDimension('2')->setRowHeight(100);   
                } 
                $Sheet->setCellValueByColumnAndRow($i,2,$_SESSION['columnPromo'][$i]);
                $Column++;
                $i++;
            }
            
            $i=0;
            $RowNumber=3;
            while ($ArrayOfData[$i])
            {         
                $j=0;
                $ArrayOfData[$i]=substr($ArrayOfData[$i], 0, strlen($ArrayOfData[$i])-2);
                $DataColumns=explode('/n',$ArrayOfData[$i]);
                while ($j<count($DataColumns))
                {
                    $Sheet->setCellValueByColumnAndRow($j,$RowNumber,$DataColumns[$j]);
                    $j++;
                }               
                $RowNumber++;
                $i++;  
            }  
            $ObjWriter = PHPExcel_IOFactory::createWriter($ObjPHPExcel, 'Excel2007');
            header('Content-Type: application/vnd.ms-excel Content-Type: text/html; charset=utf-8');
            if(file_exists("../report/Promo.xlsx"))
            {
                unlink("../report/CPromo.xlsx");
            }
            $ObjWriter->save('../report/Promo.xlsx');
            print_r($_SESSION['columnPromo']);
}
 
if ($_GET['func']=='save_export_xls')
{
    $File = ('../report/Promo.xlsx');
    if (file_exists($File)) 
    {
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename='.basename($File));
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($File));
        ob_clean();
        flush();
        readfile($File);
        exit;
    }
} 
      
      
if ($_POST['func']=='export_csv')
{
    $Title='По остановкам';
    $Data=$_POST['ColumnsArray'];  
    switch ($StopReport)
    {
        case 1:$Fields=array('Дата','Адрес','Начало движения','Конец стоянки','Время стоянки','Координаты'); break;
        case 2:$Fields=array('Дата','Автомобиль','Водитель','Сотрудник','Маршрут','Время начала / конца маршрута','Время в пути, итого','Время в пути в нерабочее время','Время в пути в выходные дни','Пробег, км','Время начала / конца стоянки','Продолжительность стоянки'); break;
    }
    $ObjReportStop->ExportCSV($Fields,$Title,$Data);  
}
 
if ($_GET['func']=='save_export_csv')
{
    $ObjReportStop->SaveCSV();
}
 
if ($_POST['func']=='export_print')
{
    $Title='По остановкам';
    $Data=$_POST['ColumnsArray'];  
    switch ($StopReport)
    {
        case 1:$Fields=array('Дата','Адрес','Начало движения','Конец стоянки','Время стоянки','Координаты'); break;
        case 2:$Fields=array('Дата','Автомобиль','Водитель','Сотрудник','Маршрут','Время начала / конца маршрута','Время в пути, итого','Время в пути в нерабочее время','Время в пути в выходные дни','Пробег, км','Время начала / конца стоянки','Продолжительность стоянки'); break;
    }
    $ObjReportStop->ExportPrint($Fields,$Title,$Data);
}
 
if ($_GET['func']=='save_export_print')
{
    $ObjReportStop->SavePrint();
}

if ($_POST['func']=='getGeolocation')
{
    $coords = $_POST['loaction'];
    $ObjReportStop->GetGeolocation($coords);
   echo $ObjReportStop->AdressStopReport;
   /* sleep(1);
    echo 'Тестовая улица';*/
}

if ($_GET['func']=='tradePoint')
{
    $i=0;
    
    $where = "";
    if ($_SESSION['networkIDPromo'])
    {
        $where .= " AND tp.id_network = ".$_SESSION['networkIDPromo'];
    }
    
    if ($_SESSION['cityIDPromo'])
    {
        $where .= " AND tp.id_city = '".$_SESSION['cityIDPromo']."'";    
    }
    
    if ($_SESSION['regionIDPromo'])
    {
        $where .= " AND tp.id_region = ".$_SESSION['regionIDPromo'];    
    }
    //$query = "SELECT id,address FROM ms_trade_point WHERE address LIKE '%".$_GET['comboData']."%' ORDER BY address LIMIT 500";
    $query = "SELECT tp.id,tp.address FROM ms_trade_point tp WHERE tp.address LIKE '%".$_GET['comboData']."%' ".$where."  GROUP BY tp.id LIMIT 500";
	// $query = "SELECT t.id,t.name FROM ms_trade_point t WHERE t.id IN (SELECT tp.id FROM ms_trade_point tp WHERE UPPER(tp.name) LIKE '%".$_GET['comboData']."%' ".$where."  GROUP BY tp.id) ORDER BY t.name LIMIT 500";
    $stmt = $DB->prepare($query);
    $stmt->execute();       
    $stmt->bind_result($id,$name);
    while ($stmt->fetch()) 
    {
        $tradePoint [$i]= 
        array
        (
                'id'=>$id,
                'addressTP'=>$name      
        );
        $i++;
    } 
       
    $stmt->close();
    $DB->close();
     

    echo'{rows:'.json_encode($tradePoint).'}';
} 


if ($_GET['func']=='city')
{
    $i=0;
  $where = "";
  if ($_SESSION['networkIDPromo'])
  {
    $where .= " AND tp.id_network = ".$_SESSION['networkIDPromo'];
  }
  
  if ($_SESSION['addressIDPromo'])
  {
    $where .= " AND tp.id = ".$_SESSION['addressIDPromo'];    
  }
  
  if ($_SESSION['regionIDPromo'])
  {
    $where .= " AND tp.id_region = ".$_SESSION['regionIDPromo'];    
  }
    $query = "SELECT c.id,c.name FROM ms_trade_point tp LEFT JOIN ms_city c ON tp.id_city = c.id WHERE c.name LIKE '%".strtoupper($_GET['comboData'])."%' ".$where."  GROUP BY tp.id_city LIMIT 500";
	//$query = "SELECT ct.id,ct.name  FROM ms_city ct WHERE ct.id IN (SELECT c.id FROM ms_trade_point tp LEFT JOIN ms_city c ON tp.id_city = c.id WHERE UPPER(c.name) LIKE '%".strtoupper($_GET['comboData'])."%' ".$where." GROUP BY c.id)  ORDER BY ct.name LIMIT 500";
    //echo $query;
   // $query = "SELECT id,name FROM ms_city WHERE  ORDER BY name LIMIT 500";
    $stmt = $DB->prepare($query);
    $stmt->execute();       
    $stmt->bind_result($id,$name);
    while ($stmt->fetch()) 
    {
        $city [$i]= 
        array
        (
                'id'=>$id,
                'name'=>$name      
        );
        $i++;
    } 
       
    $stmt->close();
    $DB->close();
     

    echo'{rows:'.json_encode($city).'}';
}

if ($_GET['func']=='employer')
{
    $i=0;
    $query = "SELECT id,first_name,last_name,middle_name FROM ms_user";
    $stmt = $DB->prepare($query);
    $stmt->execute();       
    $stmt->bind_result($id,$firstName,$lastName,$middleName);
    while ($stmt->fetch()) 
    {
        $city [$i]= 
        array
        (
                'id'=>$id,
                'name'=>$lastName.' '.$firstName.' '.$middleName      
        );
        $i++;
    } 
       
    $stmt->close();
    $DB->close();
     

    echo'{rows:'.json_encode($city).'}';
}


if ($_GET['func']=='network')
{
    $i=0;
    $where = "";
    if ($_SESSION['addressIDPromo'])
    {
        $where .= " AND tp.id = ".$_SESSION['addressIDPromo'];    
    }
    
    if ($_SESSION['cityIDPromo'])
    {
        $where .= " AND tp.id_city = '".$_SESSION['cityIDPromo']."'";    
    }
    
    if ($_SESSION['regionIDPromo'])
    {
        $where .= " AND tp.id_region = ".$_SESSION['regionIDPromo'];    
    }
    //$query = "SELECT id,name FROM ms_network WHERE name LIKE '%".$_GET['comboData']."%' ORDER BY name LIMIT 500";
     $query = "SELECT n.id,n.name FROM ms_trade_point tp LEFT JOIN ms_network n ON tp.id_network = n.id WHERE n.name LIKE '%".strtoupper($_GET['comboData'])."%' ".$where."  GROUP BY tp.id_network LIMIT 500";
	 //$query = "SELECT nt.id,nt.name FROM ms_network nt WHERE nt.id IN (SELECT n.id FROM ms_trade_point tp LEFT JOIN ms_network n ON tp.id_network = n.id WHERE UPPER(n.name) LIKE '%".strtoupper($_GET['comboData'])."%' ".$where."  ORDER BY n.name) ORDER BY nt.name LIMIT 500";
    $stmt = $DB->prepare($query);
    $stmt->execute();       
    $stmt->bind_result($id,$name);
    while ($stmt->fetch()) 
    {
        $city [$i]= 
        array
        (
                'id'=>$id,
                'name'=>$name      
        );
        $i++;
    } 
       
    $stmt->close();
    $DB->close();
     

    echo'{rows:'.json_encode($city).'}';
}

if ($_GET['func']=='region')
{
    $i=0;
    $where = "";
    if ($_SESSION['networkIDPromo'])
    {
        $where .= " AND tp.id_network = ".$_SESSION['networkIDPromo'];
    }
    
    if ($_SESSION['addressIDPromo'])
    {
        $where .= " AND tp.id = ".$_SESSION['addressIDPromo'];    
    }
    
    if ($_SESSION['cityIDPromo'])
    {
        $where .= " AND tp.id_city = '".$_SESSION['cityIDPromo']."'";    
    }
    //$query = "SELECT id,name FROM ms_region WHERE name LIKE '%".strtoupper($_GET['comboData'])."%' ORDER BY name LIMIT 500";
   $query = "SELECT r.id,r.name FROM ms_trade_point tp LEFT JOIN ms_region r ON tp.id_region = r.id WHERE r.name LIKE '%".strtoupper($_GET['comboData'])."%' ".$where."  GROUP BY tp.id_region LIMIT 500";
   //$query = "SELECT re.id,re.name FROM ms_region re WHERE re.id IN (SELECT r.id FROM ms_trade_point tp LEFT JOIN ms_region r ON tp.id_region = r.id WHERE UPPER(r.name) LIKE '%".strtoupper($_GET['comboData'])."%' ".$where."  GROUP BY r.id) ORDER BY re.name LIMIT 500";

    //echo $query;
    $stmt = $DB->prepare($query);
    $stmt->execute();       
    $stmt->bind_result($id,$name);
    while ($stmt->fetch()) 
    {
        $city [$i]= 
        array
        (
                'id'=>$id,
                'name'=>$name      
        );
        $i++;
    } 
       
    $stmt->close();
    $DB->close();
     

    echo'{rows:'.json_encode($city).'}';
}

if ($_POST['func'] == 'setAddress')
{
    $_SESSION['addressIDPromo'] = $_POST['idTP'];
}

if ($_POST['func'] == 'setCity')
{
    $_SESSION['cityIDPromo'] = $_POST['idCity'];
}

if ($_POST['func'] == 'setNetwork')
{
    $_SESSION['networkIDPromo'] = $_POST['idNetwork'];
}

if ($_POST['func'] == 'setRegion')
{
    $_SESSION['regionIDPromo'] = $_POST['idRegion'];
}

if ($_GET['func'] == 'initData')
{
     $_SESSION['addressIDPromo'] = '';    
     $_SESSION['cityIDPromo'] = '';    
     $_SESSION['networkIDPromo'] = '';    
     $_SESSION['regionIDPromo'] = '';    
}
?>
