<?php
defined('BASEPATH') OR exit('No direct script access allowed');
date_default_timezone_set('Asia/Kolkata');

class Json_data extends CI_Controller {
    
    public function __construct()
    {
        parent::__construct();
        
        $this->load->library('session');
        
        $this->load->library('excel');
        $this->load->library('form_validation');
        $this->load->helper('form');
        $this->load->helper('url');
        $this->load->helper('html');
        $this->load->helper('sms');
        $this->load->model('admin_db');   
        
        $action_method = $this->router->fetch_method();
        if(!is_numeric($this->session->userdata('admin_id')) &&  !($action_method=='index' || $action_method=='admin_login')){
            redirect(base_url());
            exit;
        }
        
    }

    public function index()
    {
        $this->load->view('login/header');
        $this->load->view('login/index');
        $this->load->view('login/footer');
    }
    
    public function json_data_records()
    {
        $json_type = $this->input->get('json_type');
        $user_type = $this->session->userdata('user_type_id');
        $logged_in_admin_id = $this->session->userdata('admin_id');

        $requestData = $_POST;

        $requestData['search']['value'] = trim($requestData['search']['value']);

        // User Privileges List
        if ($json_type == 'user_privileges') {
            
            $active_status = $this->input->get('active_status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_user_types WHERE status = ".$active_status, true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'user_privilege',
                3 => 'status',
                4 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * FROM tbl_user_types WHERE status = ".$active_status." AND (user_privilege LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * FROM tbl_user_types WHERE status = ".$active_status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["user_privilege"];
                    
                    if($value["status"] == 1){
                        $status = '<span class="label label-success">Active</span>';
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="Inactive Privilege" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_user_privilege_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactive </button>';
                    }
                    if($value["status"] == 2){
                        $status = '<span class="label label-danger">Inactive</span>';
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="Active Privilege" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_user_privilege_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Active </button>';
                    }
                    $nestedData[] = $status;
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // System Users List
        if ($json_type == 'system_users') {
            
            $user_status = $this->input->get('user_status');
            $privilege_status = $this->input->get('privilege_status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT au.id FROM tbl_admin_user au JOIN tbl_user_types ut ON au.user_type_id = ut.id WHERE au.status = ".$user_status." AND ut.status = ".$privilege_status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'au.id',
                1 => 'au.id',
                2 => 'contact_name',
                3 => 'contact_number',
                4 => 'user_privilege',
                5 => 'username',
                6 => 'au.status',
                7 => 'ut.status',
                8 => 'au.id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT au.id, contact_name, contact_number, user_privilege, username, au.status AS user_status, ut.status AS privilege_status ";
                $sql .= "FROM tbl_admin_user au JOIN tbl_user_types ut ON au.user_type_id = ut.id ";
                $sql .= "WHERE au.status = ".$user_status." AND ut.status = ".$privilege_status." ";
                $sql .= "AND (au.id LIKE '%" . $requestData['search']['value'] . "%' OR contact_name LIKE '%" . $requestData['search']['value'] . "%' OR contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR user_privilege LIKE '%" . $requestData['search']['value'] . "%' OR username LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY au.id Order by au.id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT au.id, contact_name, contact_number, user_privilege, username, au.status AS user_status, ut.status AS privilege_status ";
                $sql .= "FROM tbl_admin_user au JOIN tbl_user_types ut ON au.user_type_id = ut.id ";
                $sql .= "WHERE au.status = ".$user_status." AND ut.status = ".$privilege_status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["contact_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["user_privilege"];
                    $nestedData[] = $value["username"];
                    
                    if($value["user_status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Active</span>';
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View User" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_user?user_id=' . $value['id'] . '\'"> View </button>'
                                        . '<button data-toggle="tooltip" title="Inactive User" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_user_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate </button>'
                                        . '<button data-toggle="tooltip" title="Delete User" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_user_status?id=' . $value['id'] . '\&status=3\'"> Delete</button>';
                    }
                    if($value["user_status"] == 2){
                        $lbl_user_status = '<span class="label label-danger">Inactive</span>';
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="View User" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_user?user_id=' . $value['id'] . '\'"> View </button>'
                                        . '<button data-toggle="tooltip" title="Active User" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_user_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate </button>';
                    }
                    
                    if($value["privilege_status"] == 1){
                        $lbl_privilege_status = '<span class="label label-success">Active</span>';
                    }
                    if($value["privilege_status"] == 2){
                        $lbl_privilege_status = '<span class="label label-danger">Inactive</span>';
                    }
                    
                    if($privilege_status == 2){
                        $action_button = '<button data-toggle="tooltip" title="View User" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_user?user_id=' . $value['id'] . '\'"> View </button>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    $nestedData[] = $lbl_privilege_status;
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Customers List
        if ($json_type == 'customers') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_customers WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'customer_name',
                3 => 'contact_number',
                4 => 'customer_type',
                5 => 'status',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT id, customer_name, contact_number, customer_type, status ";
                $sql .= "FROM tbl_customers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR customer_name LIKE '%" . $requestData['search']['value'] . "%' OR contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR customer_type LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY id Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT id, customer_name, contact_number, customer_type, status ";
                $sql .= "FROM tbl_customers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["customer_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Active</span>';
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Customer" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_customer?customer_id=' . $value['id'] . '\'"> View </button>'
                                       . '<button data-toggle="tooltip" title="Inactive Customer" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_customer_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate </button>'
                                       . '<button data-toggle="tooltip" title="Delete Customer" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_customer_status?id=' . $value['id'] . '\&status=3\'"> Delete </button>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-danger">Inactive</span>';
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="View Customer" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_customer?customer_id=' . $value['id'] . '\'"> View </button>'
                                       . '<button data-toggle="tooltip" title="Active Customer" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_customer_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate </button>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Hold-On</span>';
                        $action_button = '<button data-toggle="tooltip" title="View Customer" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_customer?customer_id=' . $value['id'] . '\'"> View </button>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Sales List
        if ($json_type == 'sales') {
            
            $status = $this->input->get('status');
            
            if($status == 'all'){
                $status_sql = 'WHERE s.status NOT IN (6) AND s.sale_category = "Normal"';
            }else{
                $status_sql = 'WHERE s.status = '.$status.' AND s.sale_category = "Normal"';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_sales s ".$status_sql."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                4 => 'sale_type',
                5 => 'status',
                6 => 'status',
                7 => 'total_amount',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.total_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "".$status_sql." ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.total_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "".$status_sql." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    if($value['status'] != 1){
                        $system_date = date('Y-m-d');
                        $invoice_date = date("Y-m-d", strtotime($value['date']));
                        $date1=date_create($invoice_date);
                        $date2=date_create($system_date);
                        $diff=date_diff($date1,$date2); 
                        $no_of_days = $diff->format("%a");
                        if($no_of_days >= 7){
                            $pulse_effect = "style='background-color: #ff00003d;'"; 
                        }else{
                            $pulse_effect = '';
                        }
                        $credit_days = $diff->format("%a Days");
                        
                    }else{
                        $no_of_days = 0;
                        $pulse_effect = '';
                        $credit_days = '-';
                    }
                    
                    $nestedData[] = $no_of_days;
                    $nestedData[] = $credit_days;
                                        
                    $total_amount = $value["total_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $action_button = '<button data-toggle="tooltip" title="View Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_sale?sale_id=' . $value['id'] . '\'"> View</button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Returned Sales List
        if ($json_type == 'returned_sales') {
            
            $sale_id = $this->input->get('sale_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_return_sales WHERE sale_id = ".$sale_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'amount',
                4 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_return_sales ";
                $sql .= "WHERE sale_id = ".$sale_id." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_return_sales ";
                $sql .= "WHERE sale_id = ".$sale_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                                        
                    $total_amount = $value["amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $nestedData[] = '<button data-toggle="tooltip" title="View Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_returned_sale?return_sale_id=' . $value['id'] . '\'"> View</button>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Debtors List
        if ($json_type == 'debtors') {
            
            $type = $this->input->get('type');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_sales WHERE status IN (2,3) AND sale_type = ".$type." GROUP BY customer_id", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'customer_name',
                3 => 'contact_number',
                4 => 'customer_type',
                5 => 'status',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.*, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, SUM(balance_amount) as tot_bal_amount  ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3) AND c.status != 3 AND sale_type = ".$type." ";
                $sql .= "AND (c.id LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' OR c.contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR c.customer_type LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY customer_id Order by c.customer_name ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.*, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, SUM(balance_amount) as tot_bal_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3) AND c.status != 3 AND sale_type = ".$type." ";
                $sql .= " GROUP BY customer_id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["customer_type"];
                    
                    if($value["customer_status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Active</span>';
                    }
                    if($value["customer_status"] == 2){
                        $lbl_user_status = '<span class="label label-danger">Inactive</span>';
                    }
                    if($value["customer_status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Hold-On</span>';
                        
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $tot_bal_amount = $value["tot_bal_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $tot_bal_amount = 'Rs. '.number_format($tot_bal_amount,2,'.',',');
                        $debtor_type = 'credit';
                    }else if($value["sale_type"] == "Gold Sale"){
                        $tot_bal_amount = $tot_bal_amount.' g';
                        $debtor_type = 'gold';
                        
                    }
                    
                    $nestedData[] = $tot_bal_amount;
                    
                    $action_button = '<button data-toggle="tooltip" title="View DXB Debtors" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_debtor?customer_id=' . $value['customer_id'] .'\'"> View </button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Bank Accounts List 
        if ($json_type == 'bank_accounts') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_bank_accounts WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'account_type',
                3 => 'account_name',
                4 => 'bank_name',
                5 => 'account_number',
                6 => 'bank_balance',
                8 => 'created_on',
                9 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT ba.*, b.bank_name ";
                $sql .= "FROM tbl_bank_accounts ba JOIN tbl_banks b ON b.id = ba.bank_id ";
                $sql .= "WHERE ba.status = ".$status." ";
                $sql .= "AND (ba.id LIKE '%" . $requestData['search']['value'] . "%' OR account_type LIKE '%" . $requestData['search']['value'] . "%' OR account_name LIKE '%" . $requestData['search']['value'] . "%' OR bank_name LIKE '%" . $requestData['search']['value'] . "%' OR account_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR bank_balance LIKE '%" . $requestData['search']['value'] . "%' OR created_on LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY ba.id Order by ba.id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT ba.*, b.bank_name ";
                $sql .= "FROM tbl_bank_accounts ba JOIN tbl_banks b ON b.id = ba.bank_id ";
                $sql .= "WHERE ba.status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["account_type"];
                    $nestedData[] = $value["account_name"];
                    $nestedData[] = $value["bank_name"];
                    $nestedData[] = $value["account_number"];
                    $nestedData[] = $total_amount = 'Rs. '.number_format($value["bank_balance"],2,'.',',');
                    
                    if($value["status"] == 1){
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Account Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_bank_account?account_id=' . $value['id'] . '\'"> View </button>'
                                        . '<button data-toggle="tooltip" title="Inactive Account" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_bank_account_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate</button>';
                                        
                    }
                    if($value["status"] == 2){
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="Active Account" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_bank_account_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate</button>';
                    }
                    
                    $nestedData[] = $value["created_on"];
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Bank Account Logs List 
        if ($json_type == 'bank_account_logs') {
            
            $account_id = $this->input->get('account_id');
            $account_type = $this->input->get('account_type');
            
            if($account_type == 'Dxb'){
                $log_table = 'tbl_bank_account_logs';
            }elseif($account_type == 'Box'){
                $log_table = 'tbl_box_bank_account_logs';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM ".$log_table." WHERE account_id = ".$account_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'added_on',
                3 => 'description',
                4 => 'amount',
                5 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM ".$log_table." ";
                $sql .= "WHERE account_id = ".$account_id." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR added_on LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR description LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM ".$log_table." ";
                $sql .= "WHERE account_id = ".$account_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = date("Y-m-d", strtotime($value["added_on"]));
                    $nestedData[] = $value["description"];
                    
                    if($value['action_type'] == 'Credited'){
                        $icon = '<i class="fa fa-plus-square" style="color:green"></i>';
                    }else if($value['action_type'] == 'Debited'){
                        $icon = '<i class="fa fa-minus-square" style="color:red"></i>';
                    }
                    $nestedData[] = $total_amount = 'Rs. '.number_format($value["amount"],2,'.',',').' '.$icon;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Customer Overall DXB Sale List
        if ($json_type == 'customer_dxb_sales') {
            
            $customer_id = $this->input->get('customer_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_sales WHERE customer_id = ".$customer_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'sale_type',
                4 => 'status',
                5 => 'total_amount',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= "AND (invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR sale_type LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR status LIKE '%" . $requestData['search']['value'] . "%' OR total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $action_button = '<button data-toggle="tooltip" title="View Sale" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_sale?sale_id=' . $value['id'] . '\'"> View</button>';
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $total_amount = $value["total_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = $total_amount.' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Customer Overall Payment List
        if ($json_type == 'customer_payments') {
            
            $customer_id = $this->input->get('customer_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_payments WHERE customer_id = ".$customer_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'payment_method',
                4 => 'total_amount',
                5 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_payments ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR payment_method LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_payments ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["payment_method"];
                    $total_amount = $value["amount"];
                    
                    if($value["payment_method"] != "Gold"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else {
                        $total_amount = $total_amount.' g';
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $action_button = '<button data-toggle="tooltip" title="View Payment Details" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_payments?payment_id=' . $value['id'] . '\'"> View</button>';
                                        
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Customer Overall DXB Return List
        if ($json_type == 'customer_dxb_returns') {
            
            $customer_id = $this->input->get('customer_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_return_sales WHERE customer_id = ".$customer_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'sale_type',
                4 => 'status',
                5 => 'amount',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_return_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= "AND (invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR sale_type LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR status LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_return_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    
                    $action_button = '<button data-toggle="tooltip" title="View Return Sale" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_returned_sale?return_sale_id=' . $value['id'] . '\'"> View</button>';
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $total_amount = $value["amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = $total_amount.' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Cheques List
        if ($json_type == 'cheques') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_cheques WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'chq.id',
                1 => 'chq.id',
                2 => 'customer_name',
                3 => 'contact_number',
                4 => 'bank_name',
                5 => 'deposit_date',
                6 => 'cheque_amount',
                7 => 'chq.id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT chq.*, chq.id as cheque_id, c.customer_name, c.contact_number, b.bank_name ";
                $sql .= "FROM tbl_cheques chq LEFT JOIN tbl_customers c ON c.id = chq.customer_id ";
                $sql .= "JOIN tbl_banks b ON b.id = chq.bank_id ";
                $sql .= "WHERE chq.status = ".$status." ";
                $sql .= "AND (chq.id LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' OR c.contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR b.bank_name LIKE '%" . $requestData['search']['value'] . "%' OR chq.deposit_date LIKE '%" . $requestData['search']['value'] . "%') OR chq.cheque_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by chq.id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT chq.*, chq.id as cheque_id, c.customer_name, c.contact_number, b.bank_name ";
                $sql .= "FROM tbl_cheques chq LEFT JOIN tbl_customers c ON c.id = chq.customer_id ";
                $sql .= "JOIN tbl_banks b ON b.id = chq.bank_id ";
                $sql .= "WHERE chq.status = ".$status." ";
                $sql .= "ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["bank_name"];
                    $nestedData[] = $value["deposit_date"];
                    $nestedData[] = 'Rs. '.number_format($value["cheque_amount"],2,'.',',');
                    
                    $action_button = '<button data-toggle="tooltip" title="View Cheque" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_cheque?cheque_id=' . $value['cheque_id'] . '\'"> View </button>';
                    
                    if($value["status"] == 'Pending' && $value["deposit_date"] <= date('Y-m-d')){
                        $action_button .= '<button data-original-title="Submit This Cheque" class="btn default btn-xs green-stripe" data-toggle="modal" data-target="#submit_this_cheque" onclick="openSubmitCheque(' . $value['cheque_id'] . ')"  id="' . $value['cheque_id'] . '"> Submit Cheque </button>';
                    }
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Stock Item List 
        if ($json_type == 'stock_items') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_stock_items WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'item_type',
                3 => 'item_category',
                4 => 'item_code',
                5 => 'item_name',
                6 => 'description',
                7 => 'status',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT si.*, sic.category_name as item_category ";
                $sql .= "FROM tbl_stock_items si LEFT JOIN tbl_stock_item_categories sic ON sic.id = si.category_id ";
                $sql .= "WHERE si.status = ".$status." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR item_code LIKE '%" . $requestData['search']['value'] . "%' OR item_name LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT si.*, sic.category_name as item_category ";
                $sql .= "FROM tbl_stock_items si LEFT JOIN tbl_stock_item_categories sic ON sic.id = si.category_id ";
                $sql .= "WHERE si.status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["item_type"];
                    $nestedData[] = $value["item_category"];
                    $nestedData[] = $value["item_code"];
                    $nestedData[] = $value["item_name"];  
                    $nestedData[] = $value["description"];                    
                    
                    if($value["status"] == 1){
                        $lbl_account_status = '<span class="label label-success">Active</span>';
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Item Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_stock_item?item_id=' . $value['id'] . '\'"> View </button>'
                                        . '<button data-toggle="tooltip" title="Inactive Item" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_stock_item_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate</button>'
                                        . '<button data-toggle="tooltip" title="Delete Item" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_stock_item_status?id=' . $value['id'] . '\&status=3\'"> Delete</button>';
                                        
                    }
                    if($value["status"] == 2){
                        $lbl_account_status = '<span class="label label-danger">Inactive</span>';
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="Active Item" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_stock_item_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate</button>';
                    }
                    
                    $nestedData[] = $lbl_account_status;
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Rate Item Logs List 
        if ($json_type == 'rate_item_logs') {
            
            $rate_id = $this->input->get('rate_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_rate_logs WHERE rate_id = ".$rate_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'status',
                2 => 'rate_amount',
                3 => 'description',
                4 => 'contact_name',
                5 => 'added_on',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT rl.*, au.contact_name ";
                $sql .= "FROM tbl_rate_logs rl JOIN tbl_admin_user au ON au.id = rl.added_by ";
                $sql .= "WHERE rate_id = ".$rate_id." ";
                $sql .= "AND (rl.id LIKE '%" . $requestData['search']['value'] . "%' OR status LIKE '%" . $requestData['search']['value'] . "%' rate_amount LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR added_by LIKE '%" . $requestData['search']['value'] . "%' OR added_on LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT rl.*, au.contact_name ";
                $sql .= "FROM tbl_rate_logs rl JOIN tbl_admin_user au ON au.id = rl.added_by ";
                $sql .= "WHERE rate_id = ".$rate_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["status"];
                    $nestedData[] = 'Rs. '.number_format($value["rate_amount"],2,'.',',');
                    $nestedData[] = $value["description"];
                    $nestedData[] = $value["contact_name"];
                    $nestedData[] = $value["added_on"];
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Stock Logs List 
        if ($json_type == 'stock_logs') {
            
            $stock = $this->input->get('stock');
            
            if($stock == 'DXB Cash'){
                $table_name = 'tbl_cash_logs';
            }else if($stock == 'DXB 22K Gold'){
                $table_name = 'tbl_22k_gold_logs';
            }else if($stock == 'DXB 24K Gold'){
                $table_name = 'tbl_24k_gold_logs';
            }elseif($stock == 'BOX Cash'){
                $table_name = 'tbl_box_cash_logs';
            }else if($stock == 'BOX 22K Gold'){
                $table_name = 'tbl_box_22k_gold_logs';
            }else if($stock == 'BOX 24K Gold'){
                $table_name = 'tbl_box_24k_gold_logs';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM ".$table_name."", true);
            $totalFiltered = $totalData;
            
            if($stock == 'DXB Cash' || $stock == 'BOX Cash'){
                $columns = array(
                    0 => 'id',
                    1 => 'id',
                    2 => 'item_type',
                    3 => 'description',
                    4 => 'amount',
                    5 => 'added_by',
                    6 => 'action_date',
                );
            }else{
                $columns = array(
                    0 => 'id',
                    1 => 'id',
                    2 => 'item_type',
                    3 => 'description',
                    4 => 'amount',
                    5 => 'added_by',
                    6 => 'added_on',
                );
            }

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT sl.*, au.id as admin_id, au.contact_name, ut.user_privilege ";
                $sql .= "FROM ".$table_name." sl JOIN tbl_admin_user au ON au.id = sl.added_by ";
                $sql .= "JOIN tbl_user_types ut ON ut.id = au.user_type_id ";
                $sql .= "WHERE (sl.id LIKE '%" . $requestData['search']['value'] . "%' OR REPLACE(item_type, '_', ' ') LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR contact_name LIKE '%" . $requestData['search']['value'] . "%' OR user_privilege LIKE '%" . $requestData['search']['value'] . "%' OR sl.added_on LIKE '%" . $requestData['search']['value'] . "%') ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT sl.*, au.id as admin_id, au.contact_name, ut.user_privilege ";
                $sql .= "FROM ".$table_name." sl JOIN tbl_admin_user au ON au.id = sl.added_by ";
                $sql .= "JOIN tbl_user_types ut ON ut.id = au.user_type_id ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    
                    $action_type = str_replace("_"," ",$value["item_type"]);
                    $nestedData[] = ucwords($action_type);
                    
                    $nestedData[] = $value["description"];
                    
                    if($action_type == 'DXB Cash Balance' || $action_type == 'BOX Cash Balance' || $action_type == '22K Gold Balance' || $action_type == '24K Gold Balance'){
                        if($value['action_type'] == 'Debited'){
                            $icon = '<i class="fa fa-plus-square" style="color:green"></i>';
                        }else if($value['action_type'] == 'Credited'){
                            $icon = '<i class="fa fa-minus-square" style="color:red"></i>';
                        }
                    }else{
                        if($value['action_type'] == 'Credited'){
                            $icon = '<i class="fa fa-plus-square" style="color:green"></i>';
                        }else if($value['action_type'] == 'Debited'){
                            $icon = '<i class="fa fa-minus-square" style="color:red"></i>';
                        }
                    }
                    
                    if($stock == 'DXB Cash' || $stock == 'BOX Cash'){
                        $amount = 'Rs. '.number_format($value["amount"],2,'.',',').' '.$icon;
                        $action_date = $value["action_date"];
                    }else{
                        $amount = number_format($value["amount"],3,'.',',').' g'.' '.$icon;
                        $action_date = $value["added_on"];
                    }
                    
                    $nestedData[] = $amount;
                    $nestedData[] = $value["contact_name"].' - '.$value["user_privilege"];
                    $nestedData[] = $action_date;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Expense Category List 
        if ($json_type == 'expense_categories') {

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_expense_categories WHERE status = 1", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'category_name',
                3 => 'description',
                4 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_expense_categories ";
                $sql .= "WHERE status = 1 ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR category_name LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_expense_categories ";
                $sql .= "WHERE status = 1 ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["category_name"];
                    $nestedData[] = $value["description"];  
                    
                    $action_button = '<button data-toggle="tooltip" title="View Category Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_expense_category?category_id=' . $value['id'] . '\'"> View </button>'
                                   . '<button data-toggle="tooltip" title="Delete Category" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_expense_category_status?id=' . $value['id'] . '\&status=2\'"> Delete</button>';
                                        
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Expense List 
        if ($json_type == 'expenses') {

            $totalData = $this->admin_db->get_custom_table_data("SELECT e.id FROM tbl_expenses e JOIN tbl_expense_categories ec ON ec.id = e.category_id", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'expense_type',
                4 => 'category_name',
                5 => 'payment_method',
                6 => 'amount',
                7 => 'status',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT e.*, ec.category_name, ec.description AS category_description ";
                $sql .= "FROM tbl_expenses e JOIN tbl_expense_categories ec ON ec.id = e.category_id ";
                $sql .= "AND (e.id LIKE '%" . $requestData['search']['value'] . "%' OR category_name LIKE '%" . $requestData['search']['value'] . "%' OR payment_method LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR payment_method LIKE '%" . $requestData['search']['value'] . "%' OR e.status LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT e.*, ec.category_name, ec.description AS category_description ";
                $sql .= "FROM tbl_expenses e JOIN tbl_expense_categories ec ON ec.id = e.category_id ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    
                    if($value["expense_type"] == 'Dxb'){
                        $expense_type = 'Dxb Expense';
                    }elseif($value["expense_type"] == 'Box'){
                        $expense_type = 'Box Expense';
                    }else{
                        $expense_type = '-';
                    }
                    
                    $nestedData[] = $expense_type;
                    $nestedData[] = $value["category_name"];
                    $nestedData[] = $value["payment_method"];  
                    $nestedData[] = 'Rs. '.number_format($value["amount"],2,'.',',');
                    
                    if($value["status"] == 'Pending'){
                        $lbl_status = '<span class="label label-warning">Pending</span>';
                    }
                    if($value["status"] == 'Approved'){
                        $lbl_status = '<span class="label label-success">Approved</span>';
                    }
                    if($value["status"] == 'Rejected'){
                        $lbl_status = '<span class="label label-danger">Inactive</span>';
                    }
                    
                    $nestedData[] = $lbl_status;
                    $action_button = '<button data-toggle="tooltip" title="View Expense Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_expense?expense_id=' . $value['id'] . '\'"> View </button>';
                                        
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Creditors List
        if ($json_type == 'creditors') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_creditors WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'creditor_name',
                3 => 'contact_number',
                4 => 'status',
                5 => 'amount',
                6 => 'bal_amount',
                7 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_creditors ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= "AND (creditor_name LIKE '%" . $requestData['search']['value'] . "%' OR contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR creditor_type LIKE '%" . $requestData['search']['value'] . "%' OR bal_amount LIKE '%" . $requestData['search']['value'] . "%' OR una_bal_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY id Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_creditors ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["creditor_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["creditor_type"];
                    
                    if($value["status"] == 1){
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Creditor" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_creditor?creditor_id=' . $value['id'] . '\'"> View </button>'
                                        . '<button data-toggle="tooltip" title="Inactive Creditor" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_creditor_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate </button>'
                                        . '<button data-toggle="tooltip" title="Delete Creditor" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_creditor_status?id=' . $value['id'] . '\&status=3\'"> Delete </button>';
                    }
                    if($value["status"] == 2){
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="Active Creditor" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_creditor_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate </button>';
                    }
                    
                    $nestedData[] = 'Rs. '.number_format($value["bal_amount"],2,'.',',');
                    $nestedData[] = 'Rs. '.number_format($value["una_bal_amount"],2,'.',',');                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // approvals List
        if ($json_type == 'approvals') {
            
            $status = $this->input->get('status');
            
            if($user_type == 3){
                $sql_item_type = " AND item_type IN ('Dxb Sale','Box Sale','Return Dxb Sale','Dxb Purchase','Box Purchase')";
                
            }else{
                $sql_item_type = "";
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_approvals WHERE status = ".$status.$sql_item_type."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'added_on',
                3 => 'id',
                4 => 'item_type',
                5 => 'amount',
                6 => 'reason',
                7 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_approvals ";
                $sql .= "WHERE status = ".$status.$sql_item_type." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR item_type LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR reason LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY id Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_approvals ";
                $sql .= "WHERE status = ".$status.$sql_item_type." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {
                    
                    $nestedData = array();
                            
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = date("Y-m-d", strtotime($value['added_on']));

                    if($value["item_type"] == 'Sale' || $value["item_type"] == 'Dxb Sale'){
                        $sale_details = $this->admin_db->get_sale_by_id($value["item_id"]);
                        $invoice_number = $sale_details->invoice_num;
                    }elseif($value["item_type"] == 'Box Sale'){
                        $sale_details = $this->admin_db->get_box_sale_by_id($value["item_id"]);
                        $invoice_number = $sale_details->invoice_num;
                    }elseif($value["item_type"] == '24K Sale'){
                        $sale_details = $this->admin_db->get_24k_sale_by_id($value["item_id"]);
                        $invoice_number = $sale_details->invoice_num;
                    }elseif($value["item_type"] == 'Return Dxb Sale'){
                        $sale_details = $this->admin_db->get_returned_sale_details_by_id($value["item_id"]);
                        $invoice_number = $sale_details->invoice_num;
                    }elseif($value["item_type"] == 'Dxb Purchase' || $value["item_type"] == 'Box Purchase'){
                        $purchase_details = $this->admin_db->get_purchase_details_by_id($value["item_id"]);
                        $invoice_number = $purchase_details->invoice_num;
                    }else{
                        $invoice_number = '';
                    }

                    $nestedData[] = $invoice_number;
                    $nestedData[] = $value["item_type"];

                    if($value["item_type"] == '24K Rate Change'){
                        $amount = 'Rs. '.number_format($value["amount"],2,'.',',');
                    }else{
                        $amount = $value["amount"];
                    }

                    $nestedData[] = $amount;
                    $nestedData[] = $value["reason"];

                    if($value["item_type"] == '24K Rate Change'){
                        $view_btn = '<button data-toggle="tooltip" title="View 24K Rate" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_rate?rate_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'Expense'){
                        $view_btn = '<button data-toggle="tooltip" title="View Expense" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_expense?expense_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'Dxb Sale'){
                        $view_btn = '<button data-toggle="tooltip" title="View Dxb Sale" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_sale?sale_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'Box Sale'){
                        $view_btn = '<button data-toggle="tooltip" title="View Box Sale" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_box_sale?box_sale_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'UNA Purchase'){
                        $view_btn = '<button data-toggle="tooltip" title="View UNA" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_una?una_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == '24K Sale'){
                        $view_btn = '<button data-toggle="tooltip" title="View 24K Sale" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_24k_sale?24k_sale_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'Return Dxb Sale'){
                        $view_btn = '<button data-toggle="tooltip" title="View Return Dxb Sale" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_returned_sale?return_sale_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'Dxb Purchase'){
                        $view_btn = '<button data-toggle="tooltip" title="View Dxb Purchase" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_purchase?purchase_id=' . $value['item_id'] . '\'"> View </button>';

                    }else if($value["item_type"] == 'Box Purchase'){
                        $view_btn = '<button data-toggle="tooltip" title="View Box Purchase" class="btn default btn-sm blue-stripe" onclick="location.href = \'' . base_url() . 'view_purchase?purchase_id=' . $value['item_id'] . '\'"> View </button>';

                    }

                    if($value["status"] == 'Pending'){
                        $action_button = '<button data-toggle="tooltip" title="Approve" class="btn default btn-sm green-stripe" onclick="location.href = \'' . base_url() . 'change_approval_status?id=' . $value['id'] . '\&status=Approved\&type='.$value["item_type"].'\'"> Approve </button>'
                                       . '<button data-toggle="tooltip" title="Reject" class="btn default btn-sm red-stripe" onclick="location.href = \'' . base_url() . 'change_approval_status?id=' . $value['id'] . '\&status=Rejected\&type='.$value["item_type"].'\'"> Reject </button>'
                                       . $view_btn;
                    }
                    if($value["status"] != 'Pending'){
                        $action_button = $view_btn;
                    }

                    $nestedData[] = $action_button;

                    $data[] = $nestedData;
                        
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // una List
        if ($json_type == 'una') {
            
            $status = $this->input->get('status');
            
            if($status == 'all'){
                $status_query = '';
            }else if($status == '1'){
                $status_query = 'WHERE u.status = 1';
            }else if($status == '2'){
                $status_query = 'WHERE u.status IN (2,3)';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_una u ".$status_query."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'creditor_name',
                4 => 'una_rate',
                5 => 'lkr_amount',
                6 => 'recipient',
                7 => 'status',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT u.*, c.creditor_name ";
                $sql .= "FROM tbl_una u JOIN tbl_creditors c ON u.creditor_id = c.id ";
                $sql .= "".$status_query." ";
                $sql .= "AND (u.id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR creditor_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR status LIKE '%" . $requestData['search']['value'] . "%' OR recipient LIKE '%" . $requestData['search']['value'] . "%' ) ";
                $sql .= "GROUP BY u.id";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT u.*, c.creditor_name ";
                $sql .= "FROM tbl_una u JOIN tbl_creditors c ON u.creditor_id = c.id ";
                $sql .= "".$status_query." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["creditor_name"];
                    $nestedData[] = 'Rs. '.number_format($value["una_rate"],2,'.',',');
                    $nestedData[] = 'Rs. '.number_format($value["lkr_amount"],2,'.',',');
                    $nestedData[] = $value["recipient"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $action_button = '<button data-toggle="tooltip" title="View UNA Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_una?una_id=' . $value['id'] . '\'"> View </button>';
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // creditor accounts List
        if ($json_type == 'creditor_accounts') {
            
            $creditor_id = $this->input->get('creditor_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_creditor_accounts WHERE creditor_id = ".$creditor_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'item_type',
                4 => 'amount',
                5 => 'description',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT ca.*, c.creditor_name ";
                $sql .= "FROM tbl_creditor_accounts ca JOIN tbl_creditors c ON ca.creditor_id = c.id ";
                $sql .= "WHERE creditor_id = ".$creditor_id." ";
                $sql .= "AND (ca.id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR item_type LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR amount LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%' ) ";
                $sql .= "GROUP BY u.id";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT ca.*, c.creditor_name ";
                $sql .= "FROM tbl_creditor_accounts ca JOIN tbl_creditors c ON ca.creditor_id = c.id ";
                $sql .= "WHERE creditor_id = ".$creditor_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["item_type"];
                    $nestedData[] = 'Rs. '.number_format($value["amount"],2,'.',',');
                    $nestedData[] = $value["description"];
                    
                    $action_button = '<button data-original-title="View Credit Details" class="btn default btn-xs blue-stripe" data-toggle="modal" data-target="#open_creditor_accounts" onclick="openCreditorAccounts(' . $value['id'] . ')"  id="' . $value['id'] . '"> View </button>';
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // creditor payments List
        if ($json_type == 'creditor_payments') {
            
            $creditor_id = $this->input->get('creditor_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_creditor_payments WHERE creditor_id = ".$creditor_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'payment_method',
                4 => 'amount',
                5 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT cp.*, c.creditor_name ";
                $sql .= "FROM tbl_creditor_payments cp JOIN tbl_creditors c ON cp.creditor_id = c.id ";
                $sql .= "FROM tbl_una_payments up JOIN tbl_creditors c ON up.creditor_id = c.id ";
                $sql .= "WHERE creditor_id = ".$creditor_id." ";
                $sql .= "AND (cp.id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR payment_method LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR amount LIKE '%" . $requestData['search']['value'] . "%' ) ";
                $sql .= "GROUP BY u.id";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT cp.*, c.creditor_name ";
                $sql .= "FROM tbl_creditor_payments cp JOIN tbl_creditors c ON cp.creditor_id = c.id ";
                $sql .= "WHERE creditor_id = ".$creditor_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["payment_method"];
                    $nestedData[] = 'Rs. '.number_format($value["amount"],2,'.',',');
                    
                    $action_button = '<button data-original-title="View Payment Details" class="btn default btn-xs blue-stripe" data-toggle="modal" data-target="#open_creditor_payments" onclick="openCreditorPayments(' . $value['id'] . ')"  id="' . $value['id'] . '"> View </button>';
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Una Payment List
        if ($json_type == 'una_payments') {
            
            $una_id = $this->input->get('una_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_una_payments WHERE una_id = ".$una_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'amount',
                4 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_una_payments ";
                $sql .= "WHERE una_id = ".$una_id." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%' ) ";
                $sql .= "GROUP BY id";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_una_payments ";
                $sql .= "WHERE una_id = ".$una_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = 'Rs. '.number_format($value["amount"],2,'.',',');
                    
                    $action_button = '<button data-toggle="tooltip" title="View Payment Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_una_payments?una_payment_id=' . $value['id'] . '\'"> View </button>';
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Daily Audit List 
        if ($json_type == 'daily_audits') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_daily_audits WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'cash_balance',
                4 => 'gold_balance',
                5 => 'description',
                5 => 'contact_name',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT da.*, au.contact_name ";
                $sql .= "FROM tbl_daily_audits da JOIN tbl_admin_user au ON au.id = da.added_by ";
                $sql .= "WHERE da.status = ".$status." ";
                $sql .= "AND (da.id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR cash_balance LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR gold_balance LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%') OR contact_name LIKE '%" . $requestData['search']['value'] . "%') ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT da.*, au.contact_name ";
                $sql .= "FROM tbl_daily_audits da JOIN tbl_admin_user au ON au.id = da.added_by ";
                $sql .= "WHERE da.status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = 'Rs. '.number_format($value["cash_balance"],2,'.',','); 
                    $nestedData[] = number_format($value["gold_balance"],3,'.',',').' g';
                    
                    if(strlen($value["description"]) > 50){
                        $description = substr($value["description"], 0, 50) . '...';
                        
                    }else{
                        $description = $value["description"];
                        
                    }
                    
                    $nestedData[] = $description; 
                    $nestedData[] = $value["contact_name"];                       
                    
                    $action_button = '<button data-toggle="tooltip" title="View Daily Audit Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_daily_audit?audit_id=' . $value['id'] . '\'"> View </button>';
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Other Income List 
        if ($json_type == 'other_income') {

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_other_income", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'other_income',
                4 => 'payment_method',
                5 => 'amount',
                6 => 'status',
                7 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_other_income ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR other_income LIKE '%" . $requestData['search']['value'] . "%' OR payment_method LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR status LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_other_income ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["other_income"];
                    $nestedData[] = $value["payment_method"];  
                    $nestedData[] = 'Rs. '.number_format($value["amount"],2,'.',',');
                    
                    if($value["status"] == 1){
                        $lbl_income_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_income_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_income_status = '<span class="label label-danger">Cheque Returned</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_income_status = '<span class="label label-danger">Cheque Cancelled</span>';
                    }
                    
                    $nestedData[] = $lbl_income_status;
                    $action_button = '<button data-toggle="tooltip" title="View Expense Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_other_income?other_income_id=' . $value['id'] . '\'"> View </button>';
                                        
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Box Sales List
        if ($json_type == 'box_sales') {
            
            $status = $this->input->get('status');
            
            if($status == 'all'){
                $status_sql = 'WHERE s.status NOT IN (6)';
            }else{
                $status_sql = 'WHERE s.status = '.$status.'';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_box_sales s ".$status_sql."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                4 => 'sale_type',
                5 => 'status',
                6 => 'status',
                7 => 'total_amount',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.total_amount ";
                $sql .= "FROM tbl_box_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "".$status_sql." ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.total_amount ";
                $sql .= "FROM tbl_box_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "".$status_sql." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    if($value['status'] != 1){
                        $system_date = date('Y-m-d');
                        $invoice_date = date("Y-m-d", strtotime($value['date']));
                        $date1=date_create($invoice_date);
                        $date2=date_create($system_date);
                        $diff=date_diff($date1,$date2); 
                        $no_of_days = $diff->format("%a");
                        if($no_of_days >= 7){
                            $pulse_effect = "style='background-color: #ff00003d;'"; 
                        }else{
                            $pulse_effect = '';
                        }
                        $credit_days = $diff->format("%a Days");
                        
                    }else{
                        $no_of_days = 0;
                        $pulse_effect = '';
                        $credit_days = '-';
                    }
                    
                    $nestedData[] = $no_of_days;
                    $nestedData[] = $credit_days;
                                        
                    $total_amount = $value["total_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $action_button = '<button data-toggle="tooltip" title="View Box Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_box_sale?box_sale_id=' . $value['id'] . '\'"> View</button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Returned Box Sales List
        if ($json_type == 'returned_box_sales') {
            
            $box_sale_id = $this->input->get('box_sale_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_box_return_sales WHERE box_sale_id = ".$box_sale_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'amount',
                4 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_box_return_sales ";
                $sql .= "WHERE box_sale_id = ".$box_sale_id." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_box_return_sales ";
                $sql .= "WHERE box_sale_id = ".$box_sale_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                                        
                    $total_amount = $value["amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $nestedData[] = '<button data-toggle="tooltip" title="View Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_returned_box_sale?return_box_sale_id=' . $value['id'] . '\'"> View</button>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Box Debtors List
        if ($json_type == 'box_debtors') {
            
            $type = $this->input->get('type');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_box_sales WHERE status IN (2,3) AND sale_type = ".$type." GROUP BY customer_id", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'customer_name',
                3 => 'contact_number',
                4 => 'customer_type',
                5 => 'status',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.*, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, SUM(balance_amount) as tot_bal_amount  ";
                $sql .= "FROM tbl_box_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3) AND sale_type = ".$type." ";
                $sql .= "AND (c.id LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' OR c.contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR c.customer_type LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY customer_id Order by c.customer_name ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.*, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, SUM(balance_amount) as tot_bal_amount ";
                $sql .= "FROM tbl_box_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3) AND sale_type = ".$type." ";
                $sql .= " GROUP BY customer_id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["customer_type"];
                    
                    if($value["customer_status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Active</span>';
                    }
                    if($value["customer_status"] == 2){
                        $lbl_user_status = '<span class="label label-danger">Inactive</span>';
                    }
                    if($value["customer_status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Hold-On</span>';
                        
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $tot_bal_amount = $value["tot_bal_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $tot_bal_amount = 'Rs. '.number_format($tot_bal_amount,2,'.',',');
                        $debtor_type = 'credit';
                    }else if($value["sale_type"] == "Gold Sale"){
                        $tot_bal_amount = $tot_bal_amount.' g';
                        $debtor_type = 'gold';
                        
                    }
                    
                    $nestedData[] = $tot_bal_amount;
                    
                    $action_button = '<button data-toggle="tooltip" title="View Box Debtors" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_box_debtor?customer_id=' . $value['customer_id'] .'\'"> View </button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Customer Overall Box Sale List
        if ($json_type == 'customer_box_sales') {
            
            $customer_id = $this->input->get('customer_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_box_sales WHERE customer_id = ".$customer_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'sale_type',
                4 => 'status',
                5 => 'total_amount',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_box_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= "AND (invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR sale_type LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR status LIKE '%" . $requestData['search']['value'] . "%' OR total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_box_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $action_button = '<button data-toggle="tooltip" title="View Box Sale" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_box_sale?box_sale_id=' . $value['id'] . '\'"> View</button>';
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $total_amount = $value["total_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = $total_amount.' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // 24K Customers List
        if ($json_type == '24k_customers') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_24k_customers WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'supplier_name',
                3 => 'contact_number',
                4 => 'dxb_gold_tot_amount',
                5 => 'dxb_balance_amount',
                6 => 'box_gold_tot_amount',
                7 => 'box_balance_amount',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT id, customer_name, contact_number, status, dxb_gold_tot_amount, box_gold_tot_amount, dxb_balance_amount, box_balance_amount ";
                $sql .= "FROM tbl_24k_customers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR supplier_name LIKE '%" . $requestData['search']['value'] . "%' OR contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR gold_tot_amount LIKE '%" . $requestData['search']['value'] . "%' OR cash_tot_amount LIKE '%" . $requestData['search']['value'] . "%' OR balance_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY id Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT id, customer_name, contact_number, status, dxb_gold_tot_amount, box_gold_tot_amount, dxb_balance_amount, box_balance_amount ";
                $sql .= "FROM tbl_24k_customers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["contact_number"];
                    
                    if($value["status"] == 1){
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Customer" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_24k_customer?24k_customer_id=' . $value['id'] . '\'"> View </button>'
                                        . '<button data-toggle="tooltip" title="Inactive Customer" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_24k_customer_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate </button>'
                                        . '<button data-toggle="tooltip" title="Delete Customer" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_24k_customer_status?id=' . $value['id'] . '\&status=3\'"> Delete </button>';
                    }
                    if($value["status"] == 2){
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="View Supplier" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_24k_customer?24k_customer_id=' . $value['id'] . '\'"> View </button>'
                                        .'<button data-toggle="tooltip" title="Active Supplier" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_24k_customer_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate </button>';
                    }
                    
                    $nestedData[] = number_format($value["dxb_gold_tot_amount"],3,'.',',').' g';
                    $nestedData[] = 'Rs. '.number_format($value["dxb_balance_amount"],2,'.',',');
                    $nestedData[] = number_format($value["box_gold_tot_amount"],3,'.',',').' g';
                    $nestedData[] = 'Rs. '.number_format($value["box_balance_amount"],2,'.',',');
                    
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        //  24K Customer Sales List
        if ($json_type == '24k_customer_sales') {
            
            $customer_id = $this->input->get('customer_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_24k_sales WHERE customer_id = ".$customer_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'sale_category',
                4 => 'status',
                5 => 'total_amount',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_24k_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= "AND (invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR sale_type LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR status LIKE '%" . $requestData['search']['value'] . "%' OR total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_24k_sales ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["sale_category"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    $action_button = '<button data-toggle="tooltip" title="View 24K Sale" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_24k_sale?24k_sale_id=' . $value['id'] . '\'"> View</button>';
                    
                    $nestedData[] = 'Rs. '.number_format($value["total_amount"],2,'.',',');
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // 24K Customer Payment List
        if ($json_type == '24k_customer_payments') {
            
            $customer_id = $this->input->get('customer_id');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_24k_payments WHERE customer_id = ".$customer_id."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'date',
                3 => 'payment_method',
                4 => 'total_amount',
                5 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_24k_payments ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR payment_method LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_24k_payments ";
                $sql .= "WHERE customer_id = ".$customer_id." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["payment_method"];
                    $total_amount = $value["amount"];
                    
                    if($value["payment_method"] != "Gold"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else {
                        $total_amount = $total_amount.' g';
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    $action_button = '<button data-toggle="tooltip" title="View Payment Details" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_24k_payments?payment_id=' . $value['id'] . '\'"> View</button>';
                                        
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // 24K Sales List
        if ($json_type == '24k_sales') {
            
            $status = $this->input->get('status');
            
            if($status == 'all'){
                $status_sql = 'WHERE s.status NOT IN (5)';
            }else{
                $status_sql = 'WHERE s.status = '.$status.'';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_24k_sales s ".$status_sql."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                6 => 'status',
                5 => 'status',
                6 => 'total_amount',
                7 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.total_amount ";
                $sql .= "FROM tbl_24k_sales s JOIN tbl_24k_customers c ON c.id = s.customer_id ";
                $sql .= "".$status_sql." ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.total_amount ";
                $sql .= "FROM tbl_24k_sales s JOIN tbl_24k_customers c ON c.id = s.customer_id ";
                $sql .= "".$status_sql." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    if($value['status'] != 1){
                        $system_date = date('Y-m-d');
                        $invoice_date = date("Y-m-d", strtotime($value['date']));
                        $date1=date_create($invoice_date);
                        $date2=date_create($system_date);
                        $diff=date_diff($date1,$date2); 
                        $no_of_days = $diff->format("%a");
                        if($no_of_days >= 7){
                            $pulse_effect = "style='background-color: #ff00003d;'"; 
                        }else{
                            $pulse_effect = '';
                        }
                        $credit_days = $diff->format("%a Days");
                        
                    }else{
                        $no_of_days = 0;
                        $pulse_effect = '';
                        $credit_days = '-';
                    }
                    
                    $nestedData[] = $no_of_days;
                    $nestedData[] = $credit_days;
                    
                    $nestedData[] = $total_amount = 'Rs. '.number_format($value["total_amount"],2,'.',',');
                    
                    $action_button = '<button data-toggle="tooltip" title="View 24K Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_24k_sale?24k_sale_id=' . $value['id'] . '\'"> View</button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // DXB Sales Report List
        if ($json_type == 'dxb_sales_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_sales s WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                4 => 'sale_type',
                5 => 'status',
                6 => 'status',
                7 => 'balance_amount',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.balance_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id Order by s.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.balance_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    if($value['status'] != 1){
                        $system_date = date('Y-m-d');
                        $invoice_date = date("Y-m-d", strtotime($value['date']));
                        $date1=date_create($invoice_date);
                        $date2=date_create($system_date);
                        $diff=date_diff($date1,$date2); 
                        $no_of_days = $diff->format("%a");
                        if($no_of_days >= 7){
                            $pulse_effect = "style='background-color: #ff00003d;'"; 
                        }else{
                            $pulse_effect = '';
                        }
                        $credit_days = $diff->format("%a Days");
                        
                    }else{
                        $no_of_days = 0;
                        $pulse_effect = '';
                        $credit_days = '-';
                    }
                    
                    $nestedData[] = $no_of_days;
                    $nestedData[] = $credit_days;
                                        
                    $total_amount = $value["balance_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;

                    $nestedData[] = '<a href="' . base_url() . 'view_sale?sale_id=' . $value['id'] . '" target="_blank" title="View Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // BOX Sales Report List
        if ($json_type == 'box_sales_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_box_sales s WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                4 => 'sale_type',
                5 => 'status',
                6 => 'status',
                7 => 'balance_amount',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.balance_amount ";
                $sql .= "FROM tbl_box_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id Order by s.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.balance_amount ";
                $sql .= "FROM tbl_box_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    if($value['status'] != 1){
                        $system_date = date('Y-m-d');
                        $invoice_date = date("Y-m-d", strtotime($value['date']));
                        $date1=date_create($invoice_date);
                        $date2=date_create($system_date);
                        $diff=date_diff($date1,$date2); 
                        $no_of_days = $diff->format("%a");
                        if($no_of_days >= 7){
                            $pulse_effect = "style='background-color: #ff00003d;'"; 
                        }else{
                            $pulse_effect = '';
                        }
                        $credit_days = $diff->format("%a Days");
                        
                    }else{
                        $no_of_days = 0;
                        $pulse_effect = '';
                        $credit_days = '-';
                    }
                    
                    $nestedData[] = $no_of_days;
                    $nestedData[] = $credit_days;
                                        
                    $total_amount = $value["balance_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;

                    $nestedData[] = '<a href="' . base_url() . 'view_box_sale?box_sale_id=' . $value['id'] . '" target="_blank" title="View Box Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // 24K Sales Report List
        if ($json_type == '24k_sales_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_24k_sales s WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                6 => 'status',
                5 => '24k_rate',
                6 => 'total_amount',
                7 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.24k_rate, s.balance_amount, s.total_amount ";
                $sql .= "FROM tbl_24k_sales s JOIN tbl_24k_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.24k_rate LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id Order by s.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.24k_rate, s.balance_amount, s.total_amount ";
                $sql .= "FROM tbl_24k_sales s JOIN tbl_24k_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    $nestedData[] = 'Rs. '.number_format($value["24k_rate"],2,'.',',');
                    
                    $nestedData[] = $total_amount = 'Rs. '.number_format($value["total_amount"],2,'.',',');

                    $nestedData[] = '<a href="' . base_url() . 'view_24k_sale?24k_sale_id=' . $value['id'] . '" target="_blank" title="View 24K Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // DXB Debtors Detailed Report List
        if ($json_type == 'dxb_debtors_detailed_report') {
            
            $date_as_of = $this->input->post('date_as_of');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_sales s WHERE s.status IN (2,3,4) AND (date <= '".$date_as_of."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'customer_name',
                4 => 'sale_type',
                5 => 'status',
                6 => 'status',
                7 => 'balance_amount',
                8 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.sale_category, s.description, s.balance_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3,4) AND (date <= '".$date_as_of."') ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY s.id Order by s.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.invoice_num, c.customer_name, s.status, s.sale_type, s.sale_category, s.description, s.balance_amount ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3,4) AND (date <= '".$date_as_of."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    
                    if($value['sale_category'] == 'Normal'){
                        $ref_no = $value['invoice_num'];
                    }else if($value['sale_category'] == 'Old Debts'){
                        $ref_no = $value['description'];
                    }
                    
                    $nestedData[] = $ref_no;
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["sale_type"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_user_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_user_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 5){
                        $lbl_user_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 6){
                        $lbl_user_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    
                    if($value['status'] != 1){
                        $invoice_date = date("Y-m-d", strtotime($value['date']));
                        $date1=date_create($invoice_date);
                        $date2=date_create($date_as_of);
                        $diff=date_diff($date1,$date2); 
                        $no_of_days = $diff->format("%a");
                        if($no_of_days >= 7){
                            $pulse_effect = "style='background-color: #ff00003d;'"; 
                        }else{
                            $pulse_effect = '';
                        }
                        $credit_days = $diff->format("%a Days");
                        
                    }else{
                        $no_of_days = 0;
                        $pulse_effect = '';
                        $credit_days = '-';
                    }
                    
                    $nestedData[] = $no_of_days;
                    $nestedData[] = $credit_days;
                                        
                    $total_amount = $value["balance_amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;

                    $nestedData[] = '<a href="' . base_url() . 'view_sale?sale_id=' . $value['id'] . '" target="_blank" title="View Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // DXB Debtors Aging Report List
        if ($json_type == 'dxb_debtors_aging_report') {
            
            $sale_type = $this->input->post('sale_type');
            $date_as_of = $this->input->post('date_as_of');
            
            $one_week = date('Y-m-d',strtotime($date_as_of . "-7 days"));
            $two_weeks = date('Y-m-d',strtotime($date_as_of . "-14 days"));
            $four_weeks = date('Y-m-d',strtotime($date_as_of . "-30 days"));
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_sales s WHERE s.status IN (2,3,4) AND s.sale_type = '".$sale_type."' AND date <= '".$date_as_of."'", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'customer_name',
                2 => 'id',
                3 => 'id',
                4 => 'id',
                5 => 'id',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT s.id, s.date, s.balance_amount, c.customer_name, s.customer_id,";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$one_week."' AND '".$date_as_of."')) AS '0-7', ";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$two_weeks."' AND '".$one_week."')) AS '8-14', ";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$four_weeks."' AND '".$two_weeks."')) AS '15-30', ";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date < '".$four_weeks."')) AS 'above_30' ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3,4) AND s.sale_type = '".$sale_type."' AND date <= '".$date_as_of."' ";
                $sql .= "AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR s.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR s.total_amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY c.id Order by s.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT s.id, s.date, s.balance_amount, c.customer_name, s.customer_id,";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$one_week."' AND '".$date_as_of."')) AS '0-7', ";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$two_weeks."' AND '".$one_week."')) AS '8-14', ";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$four_weeks."' AND '".$two_weeks."')) AS '15-30', ";
                $sql .= "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date < '".$four_weeks."')) AS 'above_30' ";
                $sql .= "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE s.status IN (2,3,4) AND s.sale_type = '".$sale_type."' AND date <= '".$date_as_of."' ";
                $sql .= "GROUP BY c.id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["customer_name"];
                    
                    if($sale_type == 'Credit Sale'){
                        $nestedData[] = 'Rs. '.number_format($value["0-7"],2,'.',',');
                        $nestedData[] = 'Rs. '.number_format($value["8-14"],2,'.',',');
                        $nestedData[] = 'Rs. '.number_format($value["15-30"],2,'.',',');
                        $nestedData[] = 'Rs. '.number_format($value["above_30"],2,'.',',');
                    }else if($sale_type == 'Gold Sale'){
                        $nestedData[] = number_format($value["0-7"],3,'.',',').' g';
                        $nestedData[] = number_format($value["8-14"],3,'.',',').' g';
                        $nestedData[] = number_format($value["15-30"],3,'.',',').' g';
                        $nestedData[] = number_format($value["above_30"],3,'.',',').' g';
                        
                    }

                    $nestedData[] = '<a href="' . base_url() . 'view_customer?customer_id=' . $value['customer_id'] . '" target="_blank" title="View Customer" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // DXB Cash Logs Report List
        if ($json_type == 'dxb_cash_logs_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_cash_logs WHERE (action_date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'item_type',
                3 => 'description',
                4 => 'amount',
                5 => 'added_by',
                6 => 'action_date',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];
            
            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT cl.*, au.id as admin_id, au.contact_name, ut.user_privilege ";
                $sql .= "FROM tbl_cash_logs cl JOIN tbl_admin_user au ON au.id = cl.added_by ";
                $sql .= "JOIN tbl_user_types ut ON ut.id = au.user_type_id ";
                $sql .= "WHERE (action_date BETWEEN '".$from_date."' AND '".$to_date."') AND (cl.id LIKE '%" . $requestData['search']['value'] . "%' OR REPLACE(item_type, '_', ' ') LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR contact_name LIKE '%" . $requestData['search']['value'] . "%' OR user_privilege LIKE '%" . $requestData['search']['value'] . "%' OR cl.added_on LIKE '%" . $requestData['search']['value'] . "%') ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT cl.*, au.id as admin_id, au.contact_name, ut.user_privilege ";
                $sql .= "FROM tbl_cash_logs cl JOIN tbl_admin_user au ON au.id = cl.added_by ";
                $sql .= "JOIN tbl_user_types ut ON ut.id = au.user_type_id ";
                $sql .= "WHERE (action_date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    
                    $action_type = str_replace("_"," ",$value["item_type"]);
                    $nestedData[] = ucwords($action_type);
                    
                    $nestedData[] = $value["description"];
                    
                    if($value['action_type'] == 'Debited'){
                        $icon = '<i class="fa fa-minus-square" style="color:red"></i>';
                    }else if($value['action_type'] == 'Credited'){
                        $icon = '<i class="fa fa-plus-square" style="color:green"></i>';
                    }
                    
                    $nestedData[] = 'Rs. '.number_format($value["amount"],2,'.',',').' '.$icon;
                    $nestedData[] = $value["contact_name"].' - '.$value["user_privilege"];
                    $nestedData[] = $value["action_date"];
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // All Returned Sales List 
        if ($json_type == 'all_returned_sales') {
             
            $type = $this->input->get('type');
            $sale_type = $this->input->get('sale_type');
            
            if($type == 'dxb'){
                $table_name = 'tbl_return_sales';
                $join_table = 'tbl_sales';
                $join_key = 'sale_id';
            }else if($type == 'box'){
                $table_name = 'tbl_box_return_sales';
                $join_table = 'tbl_box_sales';
                $join_key = 'box_sale_id';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM ".$table_name." WHERE sale_type = ".$sale_type."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'date',
                2 => 'r.invoice_num',
                3 => 's.invoice_num',
                4 => 'customer_name',
                5 => 'amount',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT r.id, r.invoice_num as return_invoice_num, r.description, s.id as sale_id ,s.invoice_num as sale_invoice_num, r.date, r.amount, r.sale_type, c.id as customer_id, c.customer_name ";                
                $sql .= "FROM ".$table_name." r JOIN ".$join_table." s ON r.".$join_key." = s.id ";
                $sql .= "JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE r.sale_type = ".$sale_type." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR date LIKE '%" . $requestData['search']['value'] . "%' OR amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT r.id, r.invoice_num as return_invoice_num, r.description, s.id as sale_id ,s.invoice_num as sale_invoice_num, r.date, r.amount, r.sale_type, c.id as customer_id, c.customer_name ";                
                $sql .= "FROM ".$table_name." r JOIN ".$join_table." s ON r.".$join_key." = s.id ";
                $sql .= "JOIN tbl_customers c ON c.id = s.customer_id ";
                $sql .= "WHERE r.sale_type = ".$sale_type." ";
                $sql .= "ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["return_invoice_num"];
                    
                    if($type == 'dxb'){
                        $nestedData[] = '<a href="' . base_url() . 'view_sale?sale_id=' . $value['sale_id'] . '" target="_blank">'.$value["sale_invoice_num"].'</a>';
                    }else if($type == 'box'){
                        $nestedData[] = '<a href="' . base_url() . 'view_box_sale?box_sale_id=' . $value['sale_id'] . '" target="_blank">'.$value["sale_invoice_num"].'</a>';
                    }
                    
                    $nestedData[] = '<a href="' . base_url() . 'view_customer?customer_id=' . $value['customer_id'] . '" target="_blank">'.$value["customer_name"].'</a>';
                                        
                    $nestedData[] = $value["description"];
                    $total_amount = $value["amount"];
                    
                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format($total_amount,2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format($total_amount,3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;
                    
                    
            
                    if($type == 'dxb'){
                        $nestedData[] = '<button data-toggle="tooltip" title="View Returned DXB Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_returned_sale?return_sale_id=' . $value['id'] . '\'"> View</button>';
                    }else if($type == 'box'){
                        $nestedData[] = '<button data-toggle="tooltip" title="View Returned Box Sale" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_returned_box_sale?return_box_sale_id=' . $value['id'] . '\'"> View</button>';
                    }
                    
                    
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Stock Item Category List 
        if ($json_type == 'stock_item_categories') {

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_stock_item_categories WHERE status = 1", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'category_name',
                3 => 'description',
                4 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_stock_item_categories ";
                $sql .= "WHERE status = 1 ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR category_name LIKE '%" . $requestData['search']['value'] . "%' OR description LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT * ";
                $sql .= "FROM tbl_stock_item_categories ";
                $sql .= "WHERE status = 1 ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["category_name"];
                    $nestedData[] = $value["description"];  
                    
                    $action_button = '<button data-toggle="tooltip" title="View Category Details" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_stock_item_category?category_id=' . $value['id'] . '\'"> View </button>'
                                   . '<button data-toggle="tooltip" title="Delete Category" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_stock_item_category_status?id=' . $value['id'] . '\&status=2\'"> Delete</button>';
                                        
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Suppliers List
        if ($json_type == 'suppliers') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_suppliers WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'supplier_name',
                3 => 'contact_number',
                4 => 'tel_number',
                5 => 'status',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT id, supplier_name, contact_number, tel_number, status ";
                $sql .= "FROM tbl_suppliers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR supplier_name LIKE '%" . $requestData['search']['value'] . "%' OR contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR tel_number LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY id Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT id, supplier_name, contact_number, tel_number, status ";
                $sql .= "FROM tbl_suppliers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["supplier_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["tel_number"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Active</span>';
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Supplier" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_supplier?supplier_id=' . $value['id'] . '\'"> View </button>'
                                       . '<button data-toggle="tooltip" title="Inactive Supplier" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_supplier_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate </button>'
                                       . '<button data-toggle="tooltip" title="Delete Supplier" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_supplier_status?id=' . $value['id'] . '\&status=3\'"> Delete </button>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-danger">Inactive</span>';
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="View Supplier" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_supplier?supplier_id=' . $value['id'] . '\'"> View </button>'
                                       . '<button data-toggle="tooltip" title="Active Supplier" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_supplier_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate </button>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Carriers List
        if ($json_type == 'carriers') {
            
            $status = $this->input->get('status');

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_carriers WHERE status = ".$status."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'id',
                2 => 'carrier_name',
                3 => 'contact_number',
                4 => 'tel_number',
                5 => 'status',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT id, carrier_name, contact_number, tel_number, status ";
                $sql .= "FROM tbl_carriers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= "AND (id LIKE '%" . $requestData['search']['value'] . "%' OR carrier_name LIKE '%" . $requestData['search']['value'] . "%' OR contact_number LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR tel_number LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY id Order by id ASC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT id, carrier_name, contact_number, tel_number, status ";
                $sql .= "FROM tbl_carriers ";
                $sql .= "WHERE status = ".$status." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["carrier_name"];
                    $nestedData[] = $value["contact_number"];
                    $nestedData[] = $value["tel_number"];
                    
                    if($value["status"] == 1){
                        $lbl_user_status = '<span class="label label-success">Active</span>';
                        $change_status = 2;
                        $action_button = '<button data-toggle="tooltip" title="View Carrier" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_carrier?carrier_id=' . $value['id'] . '\'"> View </button>'
                                       . '<button data-toggle="tooltip" title="Inactive Carrier" class="btn default btn-xs yellow-stripe" onclick="location.href = \'' . base_url() . 'change_carrier_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Inactivate </button>'
                                       . '<button data-toggle="tooltip" title="Delete Carrier" class="btn default btn-xs red-stripe" onclick="location.href = \'' . base_url() . 'change_carrier_status?id=' . $value['id'] . '\&status=3\'"> Delete </button>';
                    }
                    if($value["status"] == 2){
                        $lbl_user_status = '<span class="label label-danger">Inactive</span>';
                        $change_status = 1;
                        $action_button = '<button data-toggle="tooltip" title="View Carrier" class="btn default btn-xs blue-stripe" onclick="location.href = \'' . base_url() . 'view_carrier?carrier_id=' . $value['id'] . '\'"> View </button>'
                                       . '<button data-toggle="tooltip" title="Active Carrier" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'change_carrier_status?id=' . $value['id'] . '\&status=' . $change_status . '\'"> Activate </button>';
                    }
                    
                    $nestedData[] = $lbl_user_status;
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Purchases List
        if ($json_type == 'purchases') {
            
            $status = $this->input->get('status');
            
            if($status == 'all'){
                $status_sql = '';
            }else{
                $status_sql = 'WHERE p.status = '.$status.'';
            }

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_purchases p ".$status_sql."", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'date',
                3 => 'carrier_name',
                4 => 'status',
                5 => 'payment_status',
                6 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT p.id, p.date, p.invoice_num, c.carrier_name, p.status, p.payment_status ";
                $sql .= "FROM tbl_purchases p JOIN tbl_carriers c ON c.id = p.carrier_id ";
                $sql .= "".$status_sql." ";
                $sql .= "AND (p.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR p.date LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR p.status LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY p.id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT p.id, p.date, p.invoice_num, c.carrier_name, p.status, p.payment_status ";
                $sql .= "FROM tbl_purchases p JOIN tbl_carriers c ON c.id = p.carrier_id ";
                $sql .= "".$status_sql." ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["carrier_name"];
                    
                    if($value["status"] == 1){
                        $lbl_purchase_status = '<span class="label label-success">Approved</span>';
                    }
                    if($value["status"] == 2){
                        $lbl_purchase_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["status"] == 3){
                        $lbl_purchase_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["status"] == 4){
                        $lbl_purchase_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_purchase_status;
                    
                    if($value["payment_status"] == 1){
                        $lbl_payment_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["payment_status"] == 2){
                        $lbl_payment_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["payment_status"] == 3){
                        $lbl_payment_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    
                    $nestedData[] = $lbl_payment_status;
                    
                    $action_button = '<button data-toggle="tooltip" title="View Purchase" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_purchase?purchase_id=' . $value['id'] . '\'"> View</button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }

        // Pending Purchase Invoices List
        if ($json_type == 'pending_purchase_invoices') {

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_purchase_suppliers WHERE status = 2", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'invoice_num',
                2 => 'supplier_name',
                3 => 'invoice_weight',
                4 => 'actual_weight',
                5 => 'pending_weight',
                6 => 'received_weight',
                7 => 'id',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];

            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT ps.*, s.supplier_name ";
                $sql .= "FROM tbl_purchase_suppliers ps JOIN tbl_suppliers s ON s.id = ps.supplier_id ";
                $sql .= "WHERE ps.status = 2 ";
                $sql .= "AND (invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR s.supplier_name LIKE '%" . $requestData['search']['value'] . "%' OR invoice_weight LIKE '%" . $requestData['search']['value'] . "%' ";
                $sql .= "OR actual_weight LIKE '%" . $requestData['search']['value'] . "%' OR pending_weight LIKE '%" . $requestData['search']['value'] . "%' OR received_weight LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "GROUP BY ps.id ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT ps.*, s.supplier_name ";
                $sql .= "FROM tbl_purchase_suppliers ps JOIN tbl_suppliers s ON s.id = ps.supplier_id ";
                $sql .= "WHERE ps.status = 2 ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["supplier_name"];
                    $nestedData[] = number_format($value["invoice_weight"],3,'.',',').' g';
                    $nestedData[] = number_format($value["actual_weight"],3,'.',',').' g';
                    $nestedData[] = number_format($value["pending_weight"],3,'.',',').' g';
                    $nestedData[] = number_format($value["received_weight"],3,'.',',').' g';
                    
                    $action_button = '<button data-toggle="tooltip" title="View Purchase" class="btn default btn-xs green-stripe" onclick="location.href = \'' . base_url() . 'view_purchase?purchase_id=' . $value['purchase_id'] . '\'"> View</button>';
                    $nestedData[] = $action_button;
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // DXB Sales Return Report List
        if ($json_type == 'dxb_sales_return_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT rsi.id FROM tbl_return_sales rs JOIN tbl_return_sale_items rsi ON rs.id = rsi.return_sale_id WHERE (date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'date',
                2 => 'invoice_num',
                3 => 'sale_type',
                4 => 'customer_name',
                5 => 'item_name',
                6 => 'sale_status',
                7 => 'sales_grams',
                8 => '24k_rate',
                9 => 'selling_rate',
                10 => 'amount',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];
            
            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT rsi.id, rs.id as return_sale_id, rs.date, rs.invoice_num, rs.sale_type, c.customer_name, rsi.item_name, s.status as sale_status, rsi.sales_grams, rs.24k_rate, rsi.selling_rate, rsi.amount ";
                $sql .= "FROM tbl_return_sale_items rsi JOIN tbl_return_sales rs ON rs.id = rsi.return_sale_id ";
                $sql .= "JOIN tbl_sales s ON s.id = rs.sale_id ";
                $sql .= "JOIN tbl_customers c ON c.id = rs.customer_id ";
                $sql .= "WHERE (rs.date BETWEEN '".$from_date."' AND '".$to_date."') AND (rs.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR rs.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' OR si.item_name LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR rsi.sales_grams LIKE '%" . $requestData['search']['value'] . "%' OR rs.24k_rate LIKE '%" . $requestData['search']['value'] . "%' OR rsi.selling_rate LIKE '%" . $requestData['search']['value'] . "%' OR rsi.amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by rsi.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT rsi.id, rs.id as return_sale_id, rs.date, rs.invoice_num, rs.sale_type, c.customer_name, rsi.item_name, s.status as sale_status, rsi.sales_grams, rs.24k_rate, rsi.selling_rate, rsi.amount ";
                $sql .= "FROM tbl_return_sale_items rsi JOIN tbl_return_sales rs ON rs.id = rsi.return_sale_id ";
                $sql .= "JOIN tbl_sales s ON s.id = rs.sale_id ";
                $sql .= "JOIN tbl_customers c ON c.id = rs.customer_id ";
                $sql .= "WHERE (rs.date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["sale_type"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["item_name"];

                    if($value["sale_status"] == 1){
                        $lbl_sale_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["sale_status"] == 2){
                        $lbl_sale_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["sale_status"] == 3){
                        $lbl_sale_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["sale_status"] == 4){
                        $lbl_sale_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["sale_status"] == 5){
                        $lbl_sale_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["sale_status"] == 6){
                        $lbl_sale_status = '<span class="label label-danger">Deleted</span>';
                    }

                    $nestedData[] = $lbl_sale_status;
                    $nestedData[] = number_format(($value["sales_grams"] != '' ? $value["sales_grams"] : 0),3,'.',',').' g';
                    $nestedData[] = 'Rs. '.number_format(($value["24k_rate"] != '' ? $value["24k_rate"] : 0),2,'.',',');
                    $nestedData[] = 'Rs. '.number_format(($value["selling_rate"] != '' ? $value["selling_rate"] : 0),2,'.',',');

                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format(($value["amount"] != '' ? $value["amount"] : 0),2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format(($value["amount"] != '' ? $value["amount"] : 0),3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;

                    $nestedData[] = '<a href="' . base_url() . 'view_returned_sale?return_sale_id=' . $value['return_sale_id'] . '" target="_blank" title="View Return Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // BOX Sales Return Report List
        if ($json_type == 'box_sales_return_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT rsi.id FROM tbl_box_return_sales rs JOIN tbl_box_return_sale_items rsi ON rs.id = rsi.box_return_sale_id WHERE (date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'date',
                2 => 'invoice_num',
                3 => 'sale_type',
                4 => 'customer_name',
                5 => 'item_name',
                6 => 'sale_status',
                7 => 'sales_grams',
                8 => '24k_rate',
                9 => 'selling_rate',
                10 => 'amount',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];
            
            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT rsi.id, rs.id as box_return_sale_id, rs.date, rs.invoice_num, rs.sale_type, c.customer_name, rsi.item_name, s.status as sale_status, rsi.sales_grams, rs.24k_rate, rsi.selling_rate, rsi.amount ";
                $sql .= "FROM tbl_box_return_sale_items rsi JOIN tbl_box_return_sales rs ON rs.id = rsi.box_return_sale_id ";
                $sql .= "JOIN tbl_box_sales s ON s.id = rs. box_sale_id ";
                $sql .= "JOIN tbl_customers c ON c.id = rs.customer_id ";
                $sql .= "WHERE (rs.date BETWEEN '".$from_date."' AND '".$to_date."') AND (rs.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR rs.sale_type LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' OR si.item_name LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR rsi.sales_grams LIKE '%" . $requestData['search']['value'] . "%' OR rs.24k_rate LIKE '%" . $requestData['search']['value'] . "%' OR rsi.selling_rate LIKE '%" . $requestData['search']['value'] . "%' OR rsi.amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by rsi.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT rsi.id, rs.id as box_return_sale_id, rs.date, rs.invoice_num, rs.sale_type, c.customer_name, rsi.item_name, s.status as sale_status, rsi.sales_grams, rs.24k_rate, rsi.selling_rate, rsi.amount ";
                $sql .= "FROM tbl_box_return_sale_items rsi JOIN tbl_box_return_sales rs ON rs.id = rsi.box_return_sale_id ";
                $sql .= "JOIN tbl_box_sales s ON s.id = rs. box_sale_id ";
                $sql .= "JOIN tbl_customers c ON c.id = rs.customer_id ";
                $sql .= "WHERE (rs.date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["sale_type"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["item_name"];

                    if($value["sale_status"] == 1){
                        $lbl_sale_status = '<span class="label label-success">Payment Completed</span>';
                    }
                    if($value["sale_status"] == 2){
                        $lbl_sale_status = '<span class="label label-warning">Payment Incomplete</span>';
                    }
                    if($value["sale_status"] == 3){
                        $lbl_sale_status = '<span class="label label-danger">Payment Pending</span>';
                    }
                    if($value["sale_status"] == 4){
                        $lbl_sale_status = '<span class="label label-info">Pending Approval</span>';
                    }
                    if($value["sale_status"] == 5){
                        $lbl_sale_status = '<span class="label label-danger">Rejected</span>';
                    }
                    if($value["sale_status"] == 6){
                        $lbl_sale_status = '<span class="label label-danger">Deleted</span>';
                    }
                    
                    $nestedData[] = $lbl_sale_status;
                    $nestedData[] = number_format(($value["sales_grams"] != '' ? $value["sales_grams"] : 0),3,'.',',').' g';
                    $nestedData[] = 'Rs. '.number_format(($value["24k_rate"] != '' ? $value["24k_rate"] : 0),2,'.',',');
                    $nestedData[] = 'Rs. '.number_format(($value["selling_rate"] != '' ? $value["selling_rate"] : 0),2,'.',',');

                    if($value["sale_type"] == "Credit Sale"){
                        $total_amount = 'Rs. '.number_format(($value["amount"] != '' ? $value["amount"] : 0),2,'.',',');
                    }else if($value["sale_type"] == "Gold Sale"){
                        $total_amount = number_format(($value["amount"] != '' ? $value["amount"] : 0),3,'.',',').' g';
                        
                    }
                    
                    $nestedData[] = $total_amount;

                    $nestedData[] = '<a href="' . base_url() . 'view_returned_box_sale?return_box_sale_id=' . $value['box_return_sale_id'] . '" target="_blank" title="View Return Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
        
        // DXB Fixing History Report List
        if ($json_type == 'dxb_fixing_history_report') {
            
            $from_date = $this->input->post('from_date');
            $to_date = $this->input->post('to_date');
            
            $status_sql = '';

            $totalData = $this->admin_db->get_custom_table_data("SELECT id FROM tbl_payments WHERE `payment_type` LIKE 'Fixing' AND (date BETWEEN '".$from_date."' AND '".$to_date."')", true);
            $totalFiltered = $totalData;

            $columns = array(
                0 => 'id',
                1 => 'date',
                2 => 'invoice_num',
                3 => 'customer_name',
                4 => 'payment_method',
                5 => 'bank_name',
                6 => 'cheque_number',
                8 => 'cheque_deposit_date',
                9 => 'amount',
            );

            $cols = $requestData['columns'][1];
            $searchVal = $cols['search']['value'];
            
            if (!empty($requestData['search']['value']) || !empty($searchVal)) {
                $sql = "SELECT p.id, p.date, s.id as sale_id, s.invoice_num, c.customer_name, p.payment_method, b.bank_name, p.cheque_number, p.cheque_deposit_date, p.amount ";
                $sql .= "FROM tbl_payments p JOIN tbl_payment_items pi ON p.id = pi.payment_id ";
                $sql .= "JOIN tbl_payment_fixing pf ON pf.payment_item_id = pi.id ";
                $sql .= "JOIN tbl_sales s ON s.id = pi.sale_id ";
                $sql .= "LEFT OUTER JOIN  tbl_customers c ON c.id = pf.customer_id ";
                $sql .= "LEFT OUTER JOIN  tbl_banks b ON b.id = p.bank_id ";
                $sql .= "WHERE (p.date BETWEEN '".$from_date."' AND '".$to_date."') AND (s.invoice_num LIKE '%" . $requestData['search']['value'] . "%' OR c.customer_name LIKE '%" . $requestData['search']['value'] . "%' OR si.item_name LIKE '%" . $requestData['search']['value'] . "%' OR s.status LIKE '%" . $requestData['search']['value'] . "%' OR p.payment_method LIKE '%" . $requestData['search']['value'] . "%' OR b.bank_name LIKE '%" . $requestData['search']['value'] . "%' OR p.cheque_number LIKE '%" . $requestData['search']['value'] . "%' OR p.cheque_deposit_date LIKE '%" . $requestData['search']['value'] . "%' OR p.amount LIKE '%" . $requestData['search']['value'] . "%') ";
                $sql .= "Order by p.id DESC";
                $totalFiltered = $dataRows = $this->admin_db->get_custom_table_data($sql, true);
            } else {
                $sql = "SELECT p.id, p.date, s.id as sale_id, s.invoice_num, c.customer_name, p.payment_method, b.bank_name, p.cheque_number, p.cheque_deposit_date, p.amount ";
                $sql .= "FROM tbl_payments p JOIN tbl_payment_items pi ON p.id = pi.payment_id ";
                $sql .= "JOIN tbl_payment_fixing pf ON pf.payment_item_id = pi.id ";
                $sql .= "JOIN tbl_sales s ON s.id = pi.sale_id ";
                $sql .= "LEFT OUTER JOIN  tbl_customers c ON c.id = pf.customer_id ";
                $sql .= "LEFT OUTER JOIN  tbl_banks b ON b.id = p.bank_id ";
                $sql .= "WHERE (p.date BETWEEN '".$from_date."' AND '".$to_date."') ";
                $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
            }

            $data = array();
            $dataRows = $this->admin_db->get_custom_table_data($sql);
            if ($dataRows != false) {                    
                foreach ($dataRows as $row => $value) {

                    $nestedData = array();
                    /* DANGER : Dont change the order */
                    $nestedData[] = $value["id"];
                    $nestedData[] = $value["date"];
                    $nestedData[] = $value["invoice_num"];
                    $nestedData[] = $value["customer_name"];
                    $nestedData[] = $value["payment_method"];
                    $nestedData[] = $value["bank_name"];
                    $nestedData[] = $value["cheque_number"];
                    $nestedData[] = $value["cheque_deposit_date"];

                    $nestedData[] = 'Rs. '.number_format(($value["amount"] != '' ? $value["amount"] : 0),2,'.',',');

                    $nestedData[] = '<a href="' . base_url() . 'view_sale?sale_id=' . $value['sale_id'] . '" target="_blank" title="View Sale" class="btn default btn-xs blue-stripe">View</a>';
                    
                    $data[] = $nestedData;
                }
            } else {
                $dataRows = array();
            }

            $json_data = array(
                "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
                "recordsTotal" => intval($totalData), // total number of records
                "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data" => $data, // total data array
                "res" => $requestData,
                "sql" => $sql,
            );

            echo json_encode($json_data);

        }
    }
             
}
