<?php

//inbox_fetch.php

include('admin/database_connection.php');
session_start();
//$columns = array('letter_id', 'received_method', 'institute_id', 'letter_no', 'letter_date', 'letter_topic');
$columns = array(
    0 => 'Index',   
    1 => 'letter_id',
    2 => 'received_method',
    3 => 'institute_name_en',
    4 => 'letter_no',
    5 => 'letter_topic',
    6 => 'upload_document_name',
    7 => 'approved_date',
    8 => 'subject_details',
    9 => 'status',
    10 => 'view_letter',
    11 => 'print_button',
    12 => 'action_button',
    13 => 'reply_button'
);
//echo $_SESSION["emms"]; die();

$query = '';

$output = array();

$query = "
SELECT * FROM mail_letter_details
INNER JOIN lgd_institute ON lgd_institute.institute_id = mail_letter_details.institute_id
INNER JOIN tbl_receiving_institute ON tbl_receiving_institute.receiving_letter_id = mail_letter_details.letter_id
LEFT JOIN mail_subject ON mail_subject.subject_id = tbl_receiving_institute.letter_subject
LEFT JOIN tbl_user ON tbl_user.user_id = mail_subject.subject_officer_id
LEFT JOIN office_section ON office_section.section_id = mail_subject.subject_section_id 
";
$query .=' WHERE mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" AND tbl_receiving_institute.letter_action=3';

if($_SESSION["emms"]==1){
    $query .=' AND tbl_user.user_id = "'.$_SESSION["user_id"].'"';
}


if($_POST["is_date_search"] == "yes")
{
    $query .= 'AND approved_date BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" ';
}

if(isset($_POST["search"]["value"]))
{
    $searchValue = $_POST["search"]["value"];
//     $query .= ' AND (mail_letter_details.letter_id LIKE "%'.$searchValue.'%" )';
//     $query .= 'OR (mail_letter_details.letter_topic LIKE "%'.$searchValue.'%" )';

//    $query .= ' AND (mail_letter_details.letter_id LIKE "%'.$searchValue.'%"
//                OR mail_letter_details.letter_topic LIKE "%'.$searchValue.'%" )';

    $query .= ' AND (mail_letter_details.letter_id LIKE "%'.$searchValue.'%" 
                OR lgd_institute.institute_name_en LIKE "%'.$searchValue.'%" 
                OR mail_letter_details.approved_date LIKE "%'.$searchValue.'%" 
                OR lgd_institute.institute_name_si LIKE "%'.$searchValue.'%" 
                OR mail_letter_details.letter_no LIKE "%'.$searchValue.'%" 
                OR tbl_user.user_name_with_ini LIKE "%'.$searchValue.'%" 
                OR mail_subject.subject_description LIKE "%'.$searchValue.'%" 
                OR mail_subject.subject_code LIKE "%'.$searchValue.'%" 
                OR mail_letter_details.letter_topic LIKE "%'.$searchValue.'%" )';


//$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_id  LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && lgd_institute.institute_name_en LIKE "%'.$_POST["search"]["value"].'%" ) ';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && lgd_institute.institute_name_si  LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_no LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_date LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_topic LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_content LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_subject.subject_code LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_subject.subject_description LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && office_section.section_name LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && lgd_employee.emp_name_with_initials LIKE "%'.$_POST["search"]["value"].'%" )';



//    $query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_id  LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && lgd_institute.institute_name_en LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && lgd_institute.institute_name_si  LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_no LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_date LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_topic LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_letter_details.letter_content LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_subject.subject_code LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && mail_subject.subject_description LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && office_section.section_name LIKE "%'.$_POST["search"]["value"].'%" ) OR';
//	$query .= '(mail_letter_details.approved_status =1 && tbl_receiving_institute.receiving_institute_id = "'.$_SESSION["attached_institute_id"].'" && lgd_employee.emp_name_with_initials LIKE "%'.$_POST["search"]["value"].'%" )';

// 	$query .= 'OR tbl_institute.institute_name LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.purchase_order_date LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.purchase_order_nic LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.purchase_order_total LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.approved_status LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR tbl_user.user_name LIKE "%'.$_POST["search"]["value"].'%" ';

// 	$query .= 'WHERE mail_letter_details.letter_id  LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR tbl_institute.institute_name LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.purchase_order_date LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.purchase_order_nic LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.purchase_order_total LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR mail_letter_details.approved_status LIKE "%'.$_POST["search"]["value"].'%" ';
// 	$query .= 'OR tbl_user.user_name LIKE "%'.$_POST["search"]["value"].'%" ';


}

if(isset($_POST['order']))
{
    //$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
    $query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY mail_letter_details.approved_date DESC ';
}

if($_POST['length'] != -1)
{
    $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

$filtered_rows = $statement->rowCount();
$i=1;
foreach($result as $row)
{
    $checked_status = '';
    if($row['checked_status'] == 'active')
    {
        $checked_status = '<span class="badge rounded-pill bg-success">Checked</span>';
    }
    else
    {
        $checked_status = '<span class="badge rounded-pill bg-danger">Not Checked</span>';
    }

    $status = '';
    if($row['letter_action'] == 0)
    {
        $status = '<span class="badge rounded-pill bg-secondary">No Action Yet</span>';
    }
    else if($row['letter_action'] == 1)
    {
        $status = '<span class="badge rounded-pill bg-warning">Pending</span>';
    }
    else if($row['letter_action'] == 2)
    {
        $status = '<span class="badge rounded-pill bg-danger">For further instructions</span>';
    }
    else if($row['letter_action'] == 3)
    {
        $status = '<span class="badge rounded-pill bg-success">Completed</span>';
    }
    else
    {

    }

    $received_method = '';
    if($row['received_method'] == 0)
    {
        $received_method = '<span class="badge rounded-pill bg-success">Digital</span>';
    }
    else if($row['received_method'] == 1)
    {
        $received_method = '<span class="badge rounded-pill bg-warning">Manual</span>';
    }
    else
    {

    }


    $sub_array = array();
    $sub_array[] = $i;
    $sub_array[] = $row['letter_id'];
    $sub_array[] = $received_method;
    $sub_array[] = $row['institute_name_en'];
    $sub_array[] = $row['letter_no'];
    //$sub_array[] = $row['letter_date'];
    $sub_array[] = $row['letter_topic'];
    if($row['upload_document_name'] != '')
    {
        $sub_array[] = '<a href="https://lgd.up.gov.lk//upload/letter/'.$row['upload_document_name'].'" target="_blank" class="btn btn-default btn-sm"><i class="fa fa-paperclip" aria-hidden="true"></i></a>';
    }
    else{
        $sub_array[] = '<a href="" class=""></a>';
    }
    $sub_array[] = $row['approved_date'];
    if($row['subject_code'] != '')
    {
        $sub_array[] = $row['subject_code'].' <br />- '.$row['subject_description'].' <br />- ('.$row['user_name_with_ini'].') <br />- <b>'.$row['section_name'].'</b>';
    }
    else{
        $sub_array[] = '-';
    }
    $sub_array[] = $status;
    $sub_array[] = '<button type="button" name="view_letter" class="btn btn-info btn-sm view_letter" id="'.$row["letter_id"].'"><i class="fa fa-eye" aria-hidden="true"></i></button>';
// 	if($row['approved_by'] != 0)
// 	{
// 	$sub_array[] = '<a href="letter_pdf.php?pdf=1&letter_id='.$row["letter_id"].'" class="btn btn-success btn-sm"><i class="fas fa-print" aria-hidden="true"></i></a>';
// 	}
// 	else{
// 	$sub_array[] = '<button type="button" name="" class="btn btn-secondary btn-sm" id=""><i class="fas fa-eye" aria-hidden="true"></i></button>';
// 	}
//	$sub_array[] = '<a href="print_letter5.php?pdf=1&letter_id='.$row["letter_id"].'" class="btn btn-success btn-sm"><i class="fas fa-print" aria-hidden="true"></i></a>';
    if($row["received_method"] == 0)
    {
        $sub_array[] = '<a href="print_letter.php?pdf=1&letter_id='.$row["letter_id"].'" class="btn btn-success btn-sm"><i class="fas fa-print" aria-hidden="true"></i></a>';
    }
    else if($row["received_method"] == 1)
    {
        $sub_array[] = '<button type="button" name="" class="btn btn-secondary btn-sm" id=""><i class="fas fa-print" aria-hidden="true"></i></button>';
    }
    if($_SESSION["emms"] == 3)
    {
        $sub_array[] = '<button type="button" name="action_top" class="btn btn-danger btn-sm action_top" id="'.$row["letter_id"].'">Action</button>';
    }
    else if($_SESSION["emms"] == 2)
    {
        $sub_array[] = '<button type="button" name="action_mid" class="btn btn-warning btn-sm action_mid" id="'.$row["letter_id"].'">Action</button>';
    }
    else if($_SESSION["emms"] == 1)
    {
        $sub_array[] = '<button type="button" name="action_user" class="btn btn-info btn-sm action_user" id="'.$row["letter_id"].'">Action</button>';
    }
    else{

    }
    //$sub_array[] = '<a href="letter_reply.php?letter_id='.$row["letter_id"].'&linked_letter_code='.$row["all_linked_letter_code"].'" target="_blank" class="btn btn-primary btn-sm">Reply</a>';
    if($_SESSION["attached_institute_id"] == 1)
    {
        $sub_array[] = '<a href="letter_reply_d.php?letter_id='.$row["letter_id"].'&linked_letter_code='.$row["all_linked_letter_code"].'" target="_blank" class="btn btn-primary btn-sm">Reply</a>';
    }
    else{
        $sub_array[] = '<a href="letter_reply.php?letter_id='.$row["letter_id"].'&linked_letter_code='.$row["all_linked_letter_code"].'" target="_blank" class="btn btn-primary btn-sm">Reply</a>';
    }
    $data[] = $sub_array;
    $i++;
}

$output = array(
    "draw"				=>	intval($_POST["draw"]),
    "recordsTotal"  	=>  get_total_all_records($connect),
    "recordsFiltered" 	=> 	$filtered_rows,
    "data"				=>	$data
);

function get_total_all_records($connect)
{
    // $statement = $connect->prepare("
    // SELECT * FROM mail_letter_details,tbl_receiving_institute
    // WHERE mail_letter_details.letter_id=tbl_receiving_institute.receiving_letter_id
    // AND tbl_receiving_institute.receiving_institute_id='".$_SESSION['attached_institute_id']."'
    // AND mail_letter_details.approved_status=1
    // ");
    
    if($_SESSION["emms"]==1){
        $query_total = "
        SELECT * FROM mail_letter_details,tbl_receiving_institute,mail_subject,tbl_user
        WHERE mail_letter_details.letter_id=tbl_receiving_institute.receiving_letter_id
        AND tbl_receiving_institute.receiving_institute_id='".$_SESSION['attached_institute_id']."'
        AND mail_letter_details.approved_status=1
        AND tbl_receiving_institute.letter_action=3
        AND mail_subject.subject_id = tbl_receiving_institute.letter_subject
        AND tbl_user.user_id = mail_subject.subject_officer_id
        AND tbl_user.user_id = '".$_SESSION["user_id"]."'
        ";
    }
    else {
        $query_total = "
        SELECT * FROM mail_letter_details,tbl_receiving_institute 
        WHERE mail_letter_details.letter_id=tbl_receiving_institute.receiving_letter_id
        AND tbl_receiving_institute.receiving_institute_id='".$_SESSION['attached_institute_id']."'
        AND mail_letter_details.approved_status=1
        AND tbl_receiving_institute.letter_action=3
        ";    
    }
    
    $statement = $connect->prepare($query_total);
    $statement->execute();
    return $statement->rowCount();
}

echo json_encode($output);

?>

