<?php  
require_once '../config.php';
// Sorting for Assignment Comppleted
if($_POST['formData']['action'] =='assignment_complete_table_sort' )
{
  $output = '';  
  $order = $_POST['formData']["sortorder"]; 
  $sort_by = $_POST['formData']["sortby"];
  if($order == 'desc')  
  {  
      $order = 'asc';  
  }  
  else  
  {  
      $order = 'desc';  
  }

  if($sort_by =="")
  {
      $sort_by = " assignments.id";
  }
  $search = $_POST['formData']['searchText'];  
  
  $where ="";
  
  if($search!=""){
    $where = " and assignments.assignment_no LIKE '%$search%'  "; 
  }

$sql = "SELECT assignments.* , users.name FROM assignments inner join users on users.id = assignments.user_id WHERE assignments.assignment_status_id = 2 AND assignments.deleted=0 ".$where." ORDER BY ".$sort_by." ".$order;

 $result = $conn->query($sql);

 $output .= '  
 <table class="table table-striped table-sm table-bordered">  
      <tr>  
            <th>Action</th>
           <th><a class="column_sort_complete" id="assignment_no" data-order="'.$order.'" href="javascript:;">Folder Index</a> </th>  
           <th><a class="column_sort_complete" id=no_of_record " data-order="'.$order.'" href="javascript:;">Data Count</a></th>  
           <th><a class="column_sort_complete" id="name" data-order="'.$order.'" href="javascript:;">Completed By</a></th>  
           <th><a class="column_sort_complete" id="assignment_date" data-order="'.$order.'" href="javascript:;">Assigned Date</a></th>  
           <th><a class="column_sort_complete" id="submit_date" data-order="'.$order.'" href="javascript:;">Completed Date</a></th>  
      </tr>  
 ';  
 while($row = $result->fetch_assoc()) 
 {  
      $output .= '  
      <tr>  
           <td><input type="checkbox" class="chk_doc_no" name="doc_no[]" value="'.$row['assignment_no'].'" file_count="'. $row['no_of_record'].'"  /></td>
          <td><a href="show_doc.php?assignment_no='.$row['assignment_no'] .'" target="_blank" > '.$row['assignment_no'] .'</a> </td>
            
           <td>' . $row["no_of_record"] . '</td>  
           <td>' . $row["name"] . '</td>  
           <td>' . date('m/d/Y ', strtotime($row['assignment_date'])) . '</td> 
           <td>' . date('m/d/Y ', strtotime($row['submit_date'])) . '</td>  
      </tr>  
      ';  
 }  
 $output .= '</table>';  
 echo $output;  
}




// Sorting for Assignment Deleted

if($_POST['formData']['action'] =='assignment_delete_table_sort' )
{

  $output = '';  
  $order = $_POST['formData']["sortorder"]; 
  $sort_by = $_POST['formData']["sortby"];
  if($order == 'desc')  
  {  
      $order = 'asc';  
  }  
  else  
  {  
      $order = 'desc';  
  }

  if($sort_by =="")
  {
      $sort_by = " assignments.id";
  }
  $search = $_POST['formData']['searchText'];  
  
  $where ="";
  
  if($search!=""){
    $where = " and assignments.assignment_no LIKE '%$search%'  "; 
  }

$sql = "SELECT assignments.* , users.name FROM assignments inner join users on users.id = assignments.user_id WHERE assignments.assignment_status_id = 2 AND assignments.deleted=0 AND  assignments.submit_date <= '".date('Y-m-d', strtotime('-3 days'))."' ".$where." ORDER BY ".$sort_by." ".$order;

 $result = $conn->query($sql);

 $output .= '  
 <table class="table table-striped table-sm table-bordered">  
      <tr>  
            <th>Action</th>
           <th><a class="column_sort_delete" id="assignment_no" data-order="'.$order.'" href="javascript:;">Folder Index</a> </th>  
           <th><a class="column_sort_delete" id=no_of_record " data-order="'.$order.'" href="javascript:;">Data Count</a></th>  
           <th><a class="column_sort_delete" id="name" data-order="'.$order.'" href="javascript:;">Completed By</a></th>  
           <th><a class="column_sort_delete" id="assignment_date" data-order="'.$order.'" href="javascript:;">Assigned Date</a></th>  
           <th><a class="column_sort_delete" id="submit_date" data-order="'.$order.'" href="javascript:;">Completed Date</a></th>  
      </tr>  
 ';  
 while($row = $result->fetch_assoc()) 
 {  
      $output .= '  
      <tr>  
           <td><input type="checkbox" class="chk_doc_no1" name="doc_no[]" value="'.$row['assignment_no'].'" file_count="'. $row['no_of_record'].'"  /></td>
          <td>'.$row['assignment_no'] .' </td>
            
           <td>' . $row["no_of_record"] . '</td>  
           <td>' . $row["name"] . '</td>  
           <td>' . date('m/d/Y ', strtotime($row['assignment_date'])) . '</td> 
           <td>' . date('m/d/Y ', strtotime($row['submit_date'])) . '</td>  
      </tr>  
      ';  
 }  
 $output .= '</table>';  
 echo $output;  
}





// Sorting for Assignment_to 

if($_POST['formData']['action'] =='assignment_to_table_sort' )
{

  $output = '';  
  $order = $_POST['formData']["sortorder"]; 
  $sort_by = $_POST['formData']["sortby"];
  if($order == 'desc')  
  {  
      $order = 'asc';  
  }  
  else  
  {  
      $order = 'desc';  
  }

  if($sort_by =="")
  {
      $sort_by = " assignments.id";
  }
  $search = $_POST['formData']['searchText'];  
  
  $where ="";
  
  if($search!=""){
    $where = " and assignments.assignment_no LIKE '%$search%'  "; 
  }


 $sql  ="SELECT assignments.* , SUM(if(assignment_details.doc_title != '' OR assignment_details.doc_date != '' OR assignment_details.doc_description != '', 1, 0)) as completed_count  , users.name as user_name FROM assignments inner join users on users.id = assignments.user_id inner join assignment_details on assignment_details.assignment_id = assignments.id  WHERE assignments.assignment_status_id = 1 ".$where." group by assignments.id ORDER BY ".$sort_by." ".$order;
                              

 $result = $conn->query($sql);

 $output .= '  
 <table class="table table-striped table-sm table-bordered">  
      <tr>  
          <th>Action</th>
          <th><a class="column_sort_assign" id="assignment_no" data-order="'.$order.'" href="javascript:;">Folder Index</a> </th>  
          <th><a class="column_sort_assign" id=no_of_record " data-order="'.$order.'" href="javascript:;">Data Count</a></th>  
          <th>Completed Count</th>  
          <th><a class="column_sort_assign" id="name" data-order="'.$order.'" href="javascript:;">Assigned To</a></th>  
          <th><a class="column_sort_assign" id="assignment_date" data-order="'.$order.'" href="javascript:;">Assigned Date</a></th>  
          
      </tr>  
 ';  
 while($row = $result->fetch_assoc()) 
 {  
      $output .= '  
      <tr>  
          <td><input type="checkbox" class="chk_assign_id" name="assign_id[]" value="'.$row['id'].'" file_count="'.$row['no_of_record'].'"  /></td>
          
          <td>'.$row['assignment_no'].' </td>
          <td>' . $row["no_of_record"] . '</td>  
          <td>'. $row['completed_count'].'</td>
          <td>' . $row["user_name"] . '</td>  
          <td>' . date('m/d/Y ', strtotime($row['assignment_date'])) . '</td> 
         
      </tr>  
      ';  
 }  
 $output .= '</table>';  
 echo $output;  
}



// Sorting for New Assignment 

if($_POST['formData']['action'] =='new_assignment_table_sort' )
{

 $output = '';  
  $order = $_POST['formData']["sortorder"]; 
  $sort_by = $_POST['formData']["sortby"];
  if($order == 'desc')  
  {  
      $order = 'asc';  
  }  
  else  
  {  
      $order = 'desc';  
  }

  if($sort_by =="")
  {
      $sort_by = " assignments.id";
  }
  $search = $_POST['formData']['searchText'];  
  
  $where ="";
  
  if($search!=""){
    $where = " and assignments.assignment_no LIKE '%$search%'  "; 
  }

 $sql  ="SELECT * FROM assignments WHERE assignments.assignment_status_id = 1 group by assignments.id ".$where." ORDER BY ".$sort_by." ".$order;
                              

 $result = $conn->query($sql);

 $output .= '  
 <table class="table table-striped table-sm table-bordered">  
      <tr>  
          <th>Action</th>
          <th><a class="column_sort_assign" id="assignment_no" data-order="'.$order.'" href="javascript:;">Folder Index</a> </th>  
          <th><a class="column_sort_assign" id=no_of_record " data-order="'.$order.'" href="javascript:;">Data Count</a></th>  
         
          <th><a class="column_sort_assign" id="assignment_date" data-order="'.$order.'" href="javascript:;">Assigned Date</a></th>  
          
      </tr>  
 ';  
 while($row = $result->fetch_assoc()) 
 {  
      $output .= '  
      <tr>  
        <td><input type="checkbox" class="chk_doc_no" name="doc_no[]"value="'.$row['assignment_no'].'" file_count="'.$row['no_of_record'].'"/></td>
          <td>'.$row['assignment_no'].' </td>
          <td>' . $row["no_of_record"] . '</td>  
          <td>' . date('m/d/Y ', strtotime($row['assignment_date'])) . '</td> 
         
      </tr>  
      ';  
 }  
 $output .= '</table>';  
 echo $output;  
}

 ?>