<?php    
  session_start();
  header('Content-Type: text/html; charset=utf-8');
  //$role = $_SESSION['role'];
  //$id_user = $_SESSION['id'];
  //include "../settings.php";
  
  $instance_id = $_SESSION['client_id'];
  
  $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']);
  $db1 = new mysqli ($dbset['DB']['DB_HOST'],$dbset['DB']['DB_USER'],$dbset['DB']['DB_PASS'],$dbset['DB']['DB_NAME']);
  
  //$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
  $db->query("SET CHARSET SET utf8");
  $db->query("SET NAMES 'utf8'");
  if (mysqli_connect_errno()) {
    echo "Error connecting to DB";
    exit;
  }
  //-----------------------------------------show----------------------------------------------		

  if ($_POST['func']=='show')
  {
    if ($_POST['typeMonth'])
    {
      $month = $_POST['typeMonth'];
      $year = $_POST['typeYear'];
    }
    else
    {
      $month = date("m");
      $year = date("Y");
    }
    
    $name_of_days = array(1 =>"Пн", 2 =>"Вт", 3 =>"Ср", 4 =>"Чт", 5 =>"Пт", 6 =>"Сб", 7 =>"Вс");
    $month_stamp = mktime(0,0,0,$month,1,$year);
    $weekday = date("w",$month_stamp);
    if ($weekday==0) $weekday=7;
    $daysInMonth = date("t", mktime(0,0,0,$month,1,$year));

    $columns .='[
    {
    xtype: "rownumberer",
    width: 40,
    hideable: false,
    sortable: true
    },
    {   text : "<div style=text-align:center;font-weight:bold;>Пользователь</div>", 
    dataIndex : "name",
    width: 120,
    sortable: true,
    hideable: false
    },
    ';
    $fields="['id','name',";
    $day = 0;
    $dayID = 0;
    while ($day < $daysInMonth)
    {
      $j=$weekday;
      for ($i=$weekday; $i<$daysInMonth+$weekday; $i++)
      {
        $day++;
        $dayID++;
        if ($dayID<10){$dayID='0'.$dayID;}
        if ($name_of_days[$j]=="Сб" || $name_of_days[$j]=="Вс"){
          $columns .= '{text: "<div style=color:#FF0000;text-align:center;font-weight:bold;>'.$name_of_days[$j].'<br/>'.$day.'</div>", dataIndex:"'.$day.'", width:33, sortable: false, hideable: false, id: "'.$year.'-'.$month.'-'.$dayID.'-"},';
        }
        else{
          $columns .= '{text: "<div style=text-align:center;font-weight:bold;>'.$name_of_days[$j].'<br/>'.$day.'</div>", dataIndex:"'.$day.'", width:33, sortable: false, hideable: false, id: "'.$year.'-'.$month.'-'.$dayID.'-"},';
        }
        $date_str .='"'.$day.'":"",';
        $fields .="'".$day."',";

        $j++;
        if ($j>7) {$j=1;}
      }
    }
    $fields=substr($fields, 0, strlen($fields)-1);
    $columns=substr($columns, 0, strlen($columns)-1);
    $fields .= ']';
    $columns .= ']';
    $date_str=substr($date_str, 0, strlen($date_str)-1);
    $data='[';

    $i=0;
    $query = "SELECT `id`,`first_name`,`last_name`,`middle_name` FROM `ms_user` WHERE `instance_id` = ".$instance_id.";";
    if ($stmt = $db->prepare($query)){
      $stmt->execute();
      $stmt->bind_result($id, $firstName, $lastName, $middleName);
      while ($stmt->fetch()){
        $data_arr[$i] = "{'id':'".$id."','name':'".$firstName." ".$lastName." ".$middleName."',".$date_str."},";

        //$db1 = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);

        
        //$query = "SELECT mv.`id`,DATE(mv.`date`),mt.`complit` FROM `ms_visit` mv LEFT JOIN `ms_task` mt ON mv.`id`=mt.`id_visit` WHERE DATE(mv.`date`) BETWEEN '".$year."-".$month."-01' AND '".$year."-".$month."-".$daysInMonth."' AND mv.`id_user`=".$id.";";
        $query = "SELECT v.`id`,DATE(v.`date`),v.`close` FROM `ms_visit` v WHERE DATE(v.`date`) BETWEEN '".$year."-".$month."-01' AND '".$year."-".$month."-".$daysInMonth."' AND v.`id_user`=".$id." GROUP BY DATE(v.`date`);";
		//echo $query;
		if ($stmt1 = $db1->prepare($query)){
          $stmt1->execute();
          $stmt1->bind_result($bintTaskID, $dateDateTask, $state);

          while ($stmt1->fetch()){
            $arr = explode('-',$dateDateTask);
            $dateDateTask=$arr[2];

            if ($dateDateTask < 10){ $dateDateTask = substr($dateDateTask, -1, 1); }
            
			//print_r( $data_arr[$i] );
			//echo( $dateDateTask  );
            
			$data_arr[$i] = str_replace('"'.$dateDateTask.'":""','"'.$dateDateTask.'":"<div id='.$bintTaskID.'><img src=app/icons/tasks24.png width=24 height=24></div>"', $data_arr[$i]);
          }
          $stmt1->close();
          //$db1->close();
        }    
        $i++;
      }
    }

    $i=0;
    $res=count($data_arr);
    while ($i<$res)
    {
      $data .= $data_arr[$i];
      $i++;
    }
    $data = substr($data, 0, strlen($data)-1); 
    $data .=']';

	$db->close();
	$db1->close();
    echo trim(stripcslashes($columns.'/n'.$fields.'/n'.$data));
	exit;
  }
 
  if ($_POST['func']=='copyTask')
  {
    $i=0;
    $fromDate = $_POST['convertDateFrom'];
    $toDate = $_POST['convertDateTo'];
    $strInsert = '';
    $query = "SELECT date, description, id_trade_point, id_user FROM ms_visit WHERE date LIKE '".$fromDate."%' AND `instance_id` = ".$instance_id.";";
    $stmt = $db->prepare($query);
      $stmt->execute();
      $stmt->bind_result($date, $description, $id_trade_point, $id_user);
      while ($stmt->fetch()){
       $date = str_replace($fromDate,$toDate,$date); 
       $strInsert .= "('".$date."','".$description."',".$id_trade_point.",".$id_user."),";
      } 
      $strInsert = substr($strInsert, 0, strlen($strInsert)-1); 
      $query =  "INSERT INTO ms_visit (date,description,id_trade_point,id_user) VALUES " . $strInsert;
      echo $query;
      $stmt = $db->prepare($query);
      $stmt->execute();
      $stmt->close();
      $db->close();
    echo "ok";
   //echo'{rows:'.json_encode($Model).'}';
  }

  //---------------------------------------- combo show users ---------------------------------------------------------------------------------
  if ($_GET['func']=='showUsers')
  {
    $i=0;
    $query = "SELECT `id`,`first_name`,`last_name`,`middle_name` FROM `ms_user` WHERE `instance_id` = ".$instance_id.";";
    if ($stmt = $db->prepare($query)){
      $stmt->execute();
      $stmt->bind_result($id, $firstName, $lastName, $middleName);
      while ($stmt->fetch()){
        $Model [$i]= 
        array('id'=>$id,
          'name'=>$firstName.' '.$lastName.' '.$middleName
        );
        $i++;
      } 
      $stmt->close();
      $db->close();
    }
    echo'{rows:'.json_encode($Model).'}';
  }
  //---------------------------------------- combo show client ---------------------------------------------------------------------------------
  if ($_GET['func']=='showClient')
  {
    $i=0;
    $query = "SELECT id, name, id_city, address FROM ms_client WHERE `instance_id` = ".$instance_id.";";
    if ($stmt = $db->prepare($query)){
      $stmt->execute();
      $stmt->bind_result($id, $name, $varCity, $varAddress);
      while ($stmt->fetch()) {
        $Model [$i]= 
        array('id'=>$id,
              'name'=>$name,
              'address'=>$varCity.' '.$varAddress
        );
        $i++;
      } 
      $stmt->close();
      $db->close();
    }
    echo'{rows:'.json_encode($Model).'}';
  }
  //---------------------------------------- combo tt ---------------------------------------------------------------------------------
  if ($_GET['func']=='showTT')
  {
    $i=0;
    $query = "SELECT `id`,`name`,`address` FROM `ms_trade_point` WHERE `instance_id` = ".$instance_id."";
    if ($stmt = $db->prepare($query)){
      $stmt->execute();
      $stmt->bind_result($id, $name, $address);
      while ($stmt->fetch()) {
        $Model [$i]= 
        array('id'=>$id,
              'name'=>$name,
              'address'=>$address
        );
        $i++;
      } 
      $stmt->close();
      $db->close();
    }
    echo'{rows:'.json_encode($Model).'}';
  }















  //---------------------------------------- save task ---------------------------------------------------------------------------------
  if ($_POST['func']=='addedit')
  {
    $titleTasks=$_POST['titleTasks'];
    $username=$_POST['username'];
    $clientTasks=$_POST['clientTasks'];
    $idvis=$_POST['idvis'];
    $select = $_POST['select'];
    $priority = $_POST['priority'];

    if($select!=0){
      $query = "UPDATE `ms_tasks` SET `bodyTask`='".$titleTasks."', `clientID`=".$clientTasks.", priority='".$priority."' WHERE `taskID`=".$select.";";
    }else{
      //$query = "INSERT INTO `ms_visits` (`date_visit`,`decription_visit`,`id_trade_point`,`id_user`,`complit`) VALUES ('".$dateTasks.' '.date('H:i:s')."', '".$titleTasks."', ".$clientTasks.", ".$userTasks.", 0)";
      $query = "INSERT INTO `ms_tasks` (`bodyTask`,`clientID`,`complit`,`priority`,`visitID`) VALUES ('".$titleTasks."',".$clientTasks.",0,'".$priority."',".$idvis.");";
    }
    $stmt = $db->prepare($query);
    $stmt->execute();
    $stmt->close();
    $db->close();

    //echo '{"message":"succes","text":"Задача сохранена на <b>'.$dateTasks.'</b> для пользователя <b>'.$userTasks.'</b>}"';
    echo '{"success": true,"msg":"Задача на <b>'.$dateTasks.'</b> для клиента <b>'.$username.'</b> создана"}';


  }

  //---------------------------------------- Show Edit task---------------------------------------------------------------------------------

  if ($_GET['func']=='ShowEdit')
  {
    $dateCellTask=$_GET['dateCellTask'];
    $idUser=$_GET['idUser'];

    //$query ="SELECT bintTaskID, textBodyTask, state, varNameActive, varPriority, varNameClient, dateDateTask, CONCAT_WS(', ',varCity, varAddress) AS address FROM te_taskvisits LEFT JOIN te_client ON te_client.bintClientID = te_taskvisits.bintClientID WHERE te_taskvisits.dateDateTask='".$dateCellTask."' AND te_taskvisits.bintUserID=".$idUser;
    //$query ="SELECT mv.`id_visit`,mv.`decription_visit`,mv.`complit`,mt.`name_trade_point`,mt.`address` FROM `ms_visits` mv LEFT JOIN `ms_trade_point` mt ON mt.`id_trade_point`=mv.`id_trade_point` WHERE mv.`id_user`=".$idUser." AND DATE(mv.`date_visit`)='".$dateCellTask."' ORDER BY `name_trade_point` ASC";
    $query ="SELECT mv.`id_visit`,mt.`taskID`,mv.`decription_visit`,mt.`bodyTask`,mt.`complit`,mtp.`name_trade_point`,mtp.`address`,mtc.`varNameClient` FROM `ms_visits` mv LEFT JOIN (`ms_tasks` mt, `ms_trade_point` mtp, `ms_client` mtc ) ON (mv.`id_visit`=mt.`visitID` AND mv.`id_trade_point`=mtp.`id_trade_point` AND mt.`clientID`=mtc.`bintClientID`) WHERE mv.`id_user`=".$idUser." AND DATE(mv.`date_visit`)='".$dateCellTask."';";

    if ($stmt = $db->prepare($query)){
      $stmt->execute();
      $stmt->bind_result($id_visit, $taskID, $decription_visit, $bodyTask, $complit, $name_trade_point, $address, $varNameClient);

      $i=0;
      while ($stmt->fetch()) {
        if ($complit==0){$complit='Открыта';}else{$complit='Закрыта';};
        $Model[$i]=array(
          'id_visit'=>$id_visit,
          'taskID'=>$taskID,
          'decription_visit'=>$decription_visit,
          'bodyTask'=>$bodyTask,
          'trade_point'=>$name_trade_point.', '.$address,      
          'complit'=>$complit,
          'client'=>$varNameClient
        );
        $i++;
      }

      $stmt->close();
      $db->close();
    }
    echo '{rows:'.json_encode($Model).'}';
  }

  //---------------------------------------- Delete task---------------------------------------------------------------------------------
  if ($_POST['func']=='delete')
  {
    $id=$_POST['id'];

    //$query ="DELETE FROM `ms_visits` WHERE `id_visit`=".$id;
    $query ="DELETE FROM `ms_tasks` WHERE `taskID`=".$id;
    $stmt = $db->prepare($query);
    $stmt->execute();

    $stmt->close();
    $db->close();
  }


  //---------------------------------------- select for edit task-----------------------------------------------------------------------------

  if ($_POST['func']=='selectforedit')
  {

    $select=$_POST['select'];
    $i=0;

    if($select!=0){
      $query ="SELECT `bodyTask`,`clientID`,`priority` FROM `ms_tasks` WHERE `taskID`=".$select.";";
      //echo $query;
      if ($stmt = $db->prepare($query)){
        $stmt->execute();
        $stmt->bind_result($bodyTask, $clientID, $priority);
        while ($stmt->fetch()){
          $Model[$i]=array(
            'bodyTask'=>$bodyTask,
            'clientID'=>$clientID,
            'priority'=>$priority
          );
          $i++;
        }
        $stmt->close();
        $db->close();
      }

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
















  //---------------------------------------- save vis ---------------------------------------------------------------------------------
  if ($_POST['func']=='addeditvis')
  {
    $titleTasks=$_POST['titleTasks'];
    $dateTasks=$_POST['dateTasks'];
    $userTasks=$_POST['userTasks'];
    $username=$_POST['username'];
    $clientTasks=$_POST['clientTasks'];
    $select = $_POST['select'];

    if($select!=0){
      //$query = "UPDATE `ms_visits` SET `date_visit`='".$dateTasks.' '.date('H:i:s')."', `decription_visit`='".$titleTasks."', `id_trade_point`=".$clientTasks.", `id_user`=".$userTasks.", `complit`=0 WHERE `id_visit`=".$select.";";
    }else{
      $query = "INSERT INTO `ms_visits` (`date_visit`,`decription_visit`,`id_trade_point`,`id_user`, `date_change`) VALUES ('".$dateTasks.' '.date('H:i:s')."', '".$titleTasks."', ".$clientTasks.", ".$userTasks.", '".date('Y-m-d H:i:s')."');";
    }
    //echo $query;
    $stmt = $db->prepare($query);

    $stmt->execute();
    $id = $db->insert_id;
    $stmt->close();
    $db->close();

    //echo '{"message":"succes","text":"Задача сохранена на <b>'.$dateTasks.'</b> для пользователя <b>'.$userTasks.'</b>}"';
    echo '{"success": true,"msg":"Визит на <b>'.$dateTasks.'</b> для пользователя <b>'.$username.'</b> добавлен","id":"'.$id.'"}';


  }

  
  
  
  
  
  
  if ($_GET['func']=='showTP')
  {
    $i=0;
    $id = $_GET['id'];
	if(isset($id) || $id!=""){ $where = " WHERE tp.name LIKE '%".$id."%' AND `instance_id` = ".$instance_id." ORDER BY tp.name ASC"; }else{ $where = " WHERE `instance_id` = ".$instance_id." ORDER BY tp.name ASC LIMIT 0,100"; }
	
	//$query = "SELECT tp.id,number,tp.name,address,c.name,latitude,longitude FROM ms_trade_point tp LEFT JOIN ms_city c ON tp.id_city=c.id WHERE number IN ('1010300653','1010508825','1009800315','1015108363','1004804153','1015404285','1009800307','1010401083','1009800300','1010506644')";
	$query = "SELECT tp.id,number,tp.name,address,c.name,latitude,longitude FROM ms_trade_point tp LEFT JOIN ms_city c ON tp.id_city=c.id ".$where.";";
	
	//$query = "SELECT tp.id,number,tp.name,address,c.name,latitude,longitude FROM ms_trade_point tp LEFT JOIN ms_city c ON tp.id_city=c.id ".$where."; ";
    //echo $query;
	
	if ($stmt = $db->prepare($query))
    {
      $stmt->execute();
      $stmt->bind_result($id,$number,$name,$address,$city,$latitude,$longitude);
      while ($stmt->fetch())
      {
        $store[$i]= 
        array('id'=>$id,
          'number'=>$number,
          'name'=>$name.', '.$city.', '.$address,
          'address'=>$address,
          'city'=>$city,
          'latitude'=>$latitude,
          'longitude'=>$longitude
        );
        $i++;
      }
      $stmt->close();
      $db->close();
    }
    echo'{rows:'.json_encode($store).'}';
  }
  
  
  
  
  if ($_GET['func']=='boss'){
             $i=0;
        $query = "SELECT id, CONCAT_WS(' ',first_name, last_name, middle_name) AS FIO_B FROM ms_user WHERE `instance_id` = ".$instance_id.";";
		if ($stmt = $db->prepare($query)){
            $stmt->execute();
            $stmt->bind_result($id, $FIO_B);
            while ($stmt->fetch()) {
                  $boss_id [$i]= 
    array(
    
            'id'=>$id,
			      'FIO_B'=>$FIO_B
    );
    $i++;
            } 
            
            $stmt->close();
            $db->close();
        }

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

  
  
  
?>