<?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['columnShareCategory'] = array();
    $i = 2;
    $dateFrom = $_POST['dateFrom'];
    $dateTo = $_POST['dateTo'];
    $ObjReport['model'] = '["result_1",';
    $ObjReport['tableRezult'] = "[{text: 'Отчёт с ".$dateFrom." по ".$dateTo."',columns : [";
    $ObjReport['tableRezult'] .= "{text : 'Категория',dataIndex : 'result_1',width: 100,sortable: true,hideable: false},";
    $oldIdTradePoint = 0;
    $str = '';
    $_SESSION['columnShareCategory'][] = 'Категория';
    $query = "SELECT v.id,v.id_trade_point,tp.address FROM ms_visit v LEFT JOIN ms_trade_point tp ON v.id_trade_point = tp.id WHERE v.date >= '".$dateFrom."' AND v.date <= '".$dateTo."' ORDER BY v.id_trade_point";
    $stmt = $DB->prepare($query);
    $stmt->execute();
    $stmt->bind_result($idVisit,$idTradePoint,$address);
    while ($stmt->fetch()) 
    {
        if ($oldIdTradePoint != $idTradePoint)
        {
            $_SESSION['columnShareCategory'][] = $address;       
            $ObjReport['model'] .= '"result_'.$i.'",'; 
            $ObjReport['tableRezult'] .= "{text : '".$address."',dataIndex : 'result_".$i."',width: 150,sortable: true,hideable: false},"; 
            $i++;
        }
        $oldIdTradePoint = $idTradePoint;
        $str .= $idVisit.',';
        $arrTradepoint[$idVisit] = $idTradePoint;
       /* if ($i == 0)
        {
            $oldIdVisit = $idVisit;
        }*/
       //$arrVisit[$i] = $idVisit;
       //$arrTP[$i] = $idTradePoint;
         
    }
    
    $str = substr($str, 0, strlen($str)-1);  
    
    $ObjReport['model'] = substr($ObjReport['model'], 0, strlen($ObjReport['model'])-1);  
    $ObjReport['model'] .= ']';
    $ObjReport['countColumn'] = $i-1;
    $ObjReport['tableRezult'] = substr($ObjReport['tableRezult'], 0, strlen($ObjReport['tableRezult'])-1);
    $ObjReport['tableRezult'] .= "]}]";
    
    $oldIdCategory = 0;
    $oldIdTradePoint = 0;
    $ObjReport['store'] = '[';
    if ($str != '')
    {
        
        $query = "SELECT sr.id_category,c.name,sr.share_retail,sr.width,sr.id_visit,v.id_trade_point FROM ms_share_retail sr LEFT JOIN ms_category c ON sr.id_category = c.id LEFT JOIN ms_visit v ON sr.id_visit=v.id WHERE sr.id_visit IN (" . $str . ") ORDER BY sr.id_category";
        $stmt = $DB->prepare($query);
        $stmt->execute();
        $stmt->bind_result($id_category,$category,$share_retail,$width,$id_visit,$idTradePoint);
        while ($stmt->fetch()) 
        {
            
            if ($oldIdCategory != $id_category)
            {
                $i = 2;
                if ($oldIdCategory != 0)
                {
                    $ObjReport['store']=substr($ObjReport['store'], 0, strlen($ObjReport['store'])-1);
                    $ObjReport['store'] .= '},';
                           
                }
                $ObjReport['store'] .='{"result_1":"'.$category.'",';
            }
            
            if ($arrTradepoint[$id_visit] != $oldIdTradePoint)
            {
                 $ObjReport['store'] .='"result_'.$i.'":"'.$share_retail.'",'; 
                 $i++;       
            }       
            $oldIdCategory = $id_category;
            $oldIdTradePoint = $idTradePoint;
        }
        $ObjReport['store']=substr($ObjReport['store'], 0, strlen($ObjReport['store'])-1);
        $ObjReport['store'] .= '}';
    }
    $ObjReport['store'] .= ']';
    echo  json_encode($ObjReport);      
}

 
if ($_GET['func']=='getData')
{
    $i = 0;
    $data1 = 10;
    $data2 = 20;
    $query = "SELECT name FROM ms_category";
    $stmt = $DB->prepare($query);
    $stmt->execute();
    $stmt->bind_result($category);
    while ($stmt->fetch()) 
    {
        $dataPrice [$i]= 
        array
        (
                'result_1'=>$category,
                'result_2'=>$data1*($i+1).'%',      
                'result_3'=>$data2*($i+1).'%',      
                /*'result_4'=>"",      
                'result_5'=>"",      
                'result_6'=>$name,      
                'result_7'=>"",      
                'result_8'=>$brand,      
                'result_9'=>"1",      
                'result_10'=>"" */     
        );
        $i++;
    }

     echo'{rows:'.json_encode($dataPrice).'}';
}
 
    
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['columnShareCategory']))
            {
                $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['columnShareCategory'][$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/ShareCategory.xlsx"))
            {
                unlink("../report/CShareCategory.xlsx");
            }
            $ObjWriter->save('../report/ShareCategory.xlsx');
            print_r($_SESSION['columnShareCategory']);
}
 
if ($_GET['func']=='save_export_xls')
{
    $File = ('../report/ShareCategory.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['networkIDVisits'])
    {
        $where .= " AND tp.id_network = ".$_SESSION['networkIDVisits'];
    }
    
    if ($_SESSION['cityIDVisits'])
    {
        $where .= " AND tp.id_city = '".$_SESSION['cityIDVisits']."'";    
    }
    
    if ($_SESSION['regionIDVisits'])
    {
        $where .= " AND tp.id_region = ".$_SESSION['regionIDVisits'];    
    }
    //$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";
    $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['networkIDVisits'])
  {
    $where .= " AND tp.id_network = ".$_SESSION['networkIDVisits'];
  }
  
  if ($_SESSION['addressIDVisits'])
  {
    $where .= " AND tp.id = ".$_SESSION['addressIDVisits'];    
  }
  
  if ($_SESSION['regionIDVisits'])
  {
    $where .= " AND tp.id_region = ".$_SESSION['regionIDVisits'];    
  }
    $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";
    //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['addressIDVisits'])
    {
        $where .= " AND tp.id = ".$_SESSION['addressIDVisits'];    
    }
    
    if ($_SESSION['cityIDVisits'])
    {
        $where .= " AND tp.id_city = '".$_SESSION['cityIDVisits']."'";    
    }
    
    if ($_SESSION['regionIDVisits'])
    {
        $where .= " AND tp.id_region = ".$_SESSION['regionIDVisits'];    
    }
    //$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";
    $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['networkIDVisits'])
    {
        $where .= " AND tp.id_network = ".$_SESSION['networkIDVisits'];
    }
    
    if ($_SESSION['addressIDVisits'])
    {
        $where .= " AND tp.id = ".$_SESSION['addressIDVisits'];    
    }
    
    if ($_SESSION['cityIDVisits'])
    {
        $where .= " AND tp.id_city = '".$_SESSION['cityIDVisits']."'";    
    }
    //$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";

    //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['addressIDVisits'] = $_POST['idTP'];
}

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

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

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

if ($_GET['func'] == 'initData')
{
     $_SESSION['addressIDVisits'] = '';    
     $_SESSION['cityIDVisits'] = '';    
     $_SESSION['networkIDVisits'] = '';    
     $_SESSION['regionIDVisits'] = '';    
}
?>
