<?php if (! defined('BASEPATH')) exit('No direct script access allowed');

class Report_m extends CI_Model
{
  public function __construct()
  {
    parent::__construct();
    $this->load->database();
  }
  
   public function get_overdue_report_details()
  {
    $usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {
      // Use double quotes for the SQL query and escape the column alias
      $query = $this->db->query("
            SELECT 
                ROW_NUMBER() OVER (ORDER BY issues.date_due) AS `S_No`, 
                borrowers.cardnumber AS `ID_No`, 
                borrowers.surname AS `Name`, 
                 COALESCE(borrower_attributes.attribute,'') AS `Designation`,              
                items.barcode AS `Acc_No`, 
                biblio.title AS `Title`, 
                biblio.author AS `Author`, 
                DATE_FORMAT(issues.issuedate, '%d/%m/%Y') AS `Issue_Date`, 
                DATE_FORMAT(issues.date_due, '%d/%m/%Y') AS `Due_Date`, 
                DATEDIFF(CURDATE(), issues.date_due) AS `Overdue_Days`              
            FROM issues
            LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
            LEFT JOIN borrower_attributes ON borrowers.borrowernumber = borrower_attributes.borrowernumber
            LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
            LEFT JOIN items ON issues.itemnumber = items.itemnumber
            LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
            LEFT JOIN biblio_metadata ON biblio.biblionumber = biblio_metadata.biblionumber
            LEFT JOIN accountlines ON accountlines.issue_id = issues.issue_id
            WHERE DATEDIFF(CURDATE(), issues.date_due) > 0
            group by items.barcode
            ORDER BY issues.date_due
        ");
      return $query->result();
    }
  }
  
/**
 * Normalize accession status filter input to a clean string list.
 */
protected function normalize_accession_statuses($status)
{
    if (!is_array($status)) {
        $status = array($status);
    }
    $statuses = array();
    foreach ($status as $s) {
        $s = trim((string) $s);
        if ($s !== '') {
            $statuses[] = $s;
        }
    }
    if (empty($statuses)) {
        $statuses = array('ALL');
    }
    return $statuses;
}

/**
 * Shared FROM/WHERE for accession register (status multi-filter).
 * @return array{0:string,1:array,2:string} sql_from_where, params, status_expr
 */
protected function accession_register_from_where($status = 'ALL')
{
    $statuses = $this->normalize_accession_statuses($status);

    $include_all = false;
    foreach ($statuses as $s) {
        if (strcasecmp($s, 'ALL') === 0) {
            $include_all = true;
            break;
        }
    }

    $status_expr = "IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '')";

    $sql = "
            FROM items
            LEFT JOIN biblio USING (biblionumber)
            LEFT JOIN biblioitems USING (biblionumber)
            LEFT JOIN biblio_metadata USING (biblionumber)
            LEFT JOIN aqorders_items aoi
                ON aoi.itemnumber = items.itemnumber
            LEFT JOIN aqorders ao
                ON ao.ordernumber = aoi.ordernumber
            WHERE items.itype IN ('BK', 'REF')
        ";

    $params = array();
    if (!$include_all) {
        $include_blank = false;
        $exact = array();
        foreach ($statuses as $s) {
            if (strcasecmp($s, '__BLANK__') === 0) {
                $include_blank = true;
            } else {
                $exact[] = $s;
            }
        }

        $clauses = array();
        if ($include_blank) {
            $clauses[] = "TRIM({$status_expr}) = ''";
        }
        if (!empty($exact)) {
            $placeholders = implode(', ', array_fill(0, count($exact), '?'));
            $clauses[] = "TRIM({$status_expr}) IN ({$placeholders})";
            foreach ($exact as $val) {
                $params[] = $val;
            }
        }

        if (!empty($clauses)) {
            $sql .= ' AND (' . implode(' OR ', $clauses) . ') ';
        }
    }

    return array($sql, $params, $status_expr);
}

/**
 * Optional free-text search across key accession columns.
 * @return array{0:string,1:array}
 */
protected function accession_register_search_clause($search, $status_expr)
{
    $search = trim((string) $search);
    if ($search === '') {
        return array('', array());
    }
    $like = '%' . $this->db->escape_like_str($search) . '%';
    $clause = "
        AND (
            items.barcode LIKE ?
            OR IFNULL(biblio.title, '') LIKE ?
            OR IFNULL(biblio.author, '') LIKE ?
            OR TRIM({$status_expr}) LIKE ?
            OR IFNULL(items.location, '') LIKE ?
        )
    ";
    return array($clause, array($like, $like, $like, $like, $like));
}

public function get_accession_register_count($status = 'ALL', $search = '')
{
    if (!$this->session->userdata('loggedin')) {
        return 0;
    }

    list($from_where, $params, $status_expr) = $this->accession_register_from_where($status);
    list($search_sql, $search_params) = $this->accession_register_search_clause($search, $status_expr);
    $params = array_merge($params, $search_params);

    $sql = "SELECT COUNT(*) AS cnt " . $from_where . $search_sql;
    $query = empty($params)
        ? $this->db->query($sql)
        : $this->db->query($sql, $params);

    if (!$query) {
        return 0;
    }
    $row = $query->row();
    return $row ? (int) $row->cnt : 0;
}

/**
 * Paginated accession rows for DataTables (server-side).
 */
public function get_accession_register_page($status = 'ALL', $start = 0, $length = 25, $search = '')
{
    if (!$this->session->userdata('loggedin')) {
        return array();
    }

    $start = max(0, (int) $start);
    $length = (int) $length;
    if ($length < 1) {
        $length = 25;
    }
    if ($length > 500) {
        $length = 500;
    }

    list($from_where, $params, $status_expr) = $this->accession_register_from_where($status);
    list($search_sql, $search_params) = $this->accession_register_search_clause($search, $status_expr);
    $params = array_merge($params, $search_params);

    $sql = "
            SELECT
                items.dateaccessioned AS `Date`,
                items.barcode AS `Accession_Number`,
                IFNULL(biblio.author, '') AS `Author_1`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
                    ''
                ) AS `Author_2`,
                biblio.title AS `Title`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
                    ''
                ) AS `Sub_Title`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
                    ''
                ) AS `Edition`,
                biblioitems.publishercode AS `Publisher`,
                biblioitems.place AS `Place_of_Pub`,
                biblio.copyrightdate AS `Year`,
                biblioitems.isbn AS `ISBN`,
                biblioitems.pages AS `Pages`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
                    ''
                ) AS `Vol`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
                    ''
                ) AS `Enc_Mat`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
                    ''
                ) AS `Book_Supplier_Name`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
                    ''
                ) AS `Invoice_No`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
                    ''
                ) AS `Invoice_Date`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
                    ''
                ) AS `Curr_Code`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
                    ''
                ) AS `Price`,
                items.price AS `Price_INR`,
                IFNULL(IF(ao.discount IS NULL OR TRIM(CAST(ao.discount AS CHAR)) = '', '', CONCAT(ao.discount, '%')), '') AS `Dis`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
                    ''
                ) AS `Nature`,
                {$status_expr} AS `Status`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
                    ''
                ) AS `Remarks`
            " . $from_where . $search_sql . "
            ORDER BY
                CAST(items.barcode AS UNSIGNED),
                items.barcode
            LIMIT " . (int) $length . " OFFSET " . (int) $start . "
        ";

    $query = empty($params)
        ? $this->db->query($sql)
        : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
}

public function get_accession_register_report($status = 'ALL')
{
    if (!$this->session->userdata('loggedin')) {
        return array();
    }

    list($from_where, $params, $status_expr) = $this->accession_register_from_where($status);

    $sql = "
            SELECT
                items.dateaccessioned AS `Date`,
                items.barcode AS `Accession_Number`,
                IFNULL(biblio.author, '') AS `Author_1`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
                    ''
                ) AS `Author_2`,
                biblio.title AS `Title`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
                    ''
                ) AS `Sub_Title`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
                    ''
                ) AS `Edition`,
                biblioitems.publishercode AS `Publisher`,
                biblioitems.place AS `Place_of_Pub`,
                biblio.copyrightdate AS `Year`,
                biblioitems.isbn AS `ISBN`,
                biblioitems.pages AS `Pages`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
                    ''
                ) AS `Vol`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
                    ''
                ) AS `Enc_Mat`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
                    ''
                ) AS `Book_Supplier_Name`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
                    ''
                ) AS `Invoice_No`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
                    ''
                ) AS `Invoice_Date`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
                    ''
                ) AS `Curr_Code`,
                IFNULL(
                    ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
                    ''
                ) AS `Price`,
                items.price AS `Price_INR`,
                IFNULL(IF(ao.discount IS NULL OR TRIM(CAST(ao.discount AS CHAR)) = '', '', CONCAT(ao.discount, '%')), '') AS `Dis`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
                    ''
                ) AS `Nature`,
                {$status_expr} AS `Status`,
                IFNULL(
                    ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
                    ''
                ) AS `Remarks`
            " . $from_where . "
            ORDER BY
                CAST(items.barcode AS UNSIGNED),
                items.barcode
        ";

    $query = empty($params)
        ? $this->db->query($sql)
        : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
}

/**
 * Distinct accession Status values (items 999$S) for filter dropdown.
 */
public function get_accession_status_options()
{
    if (!$this->session->userdata('loggedin')) {
        return array();
    }

    $sql = "
        SELECT DISTINCT TRIM(
            IFNULL(
                ExtractValue(
                    items.more_subfields_xml,
                    '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'
                ),
                ''
            )
        ) AS Status
        FROM items
        WHERE items.itype IN ('BK', 'REF')
        ORDER BY Status ASC
    ";
    $query = $this->db->query($sql);
    if (!$query) {
        return array();
    }
    $out = array();
    foreach ($query->result() as $row) {
        $val = isset($row->Status) ? trim((string) $row->Status) : '';
        if ($val === '') {
            continue;
        }
        $out[] = $val;
    }
    return $out;
}

public function get_issue_report($fdate, $tdate = null, $category = 'All')
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    if ($tdate === null || $tdate === '') {
      $tdate = $fdate;
    }

    if (!is_array($category)) {
      $category = array($category);
    }
    $categories = array();
    foreach ($category as $c) {
      $c = trim((string) $c);
      if ($c !== '') {
        $categories[] = $c;
      }
    }
    if (empty($categories)) {
      $categories = array('All');
    }

    $include_all = false;
    foreach ($categories as $c) {
      if (strcasecmp($c, 'All') === 0) {
        $include_all = true;
        break;
      }
    }

    $fdate_sql = $this->db->escape($fdate);
    $tdate_sql = $this->db->escape($tdate);

    $cat_filter = '';
    $params = array();
    if (!$include_all) {
      $placeholders = implode(', ', array_fill(0, count($categories), '?'));
      $cat_filter = " AND borrowers.categorycode IN ({$placeholders}) ";
      $params = $categories;
    }

    $sql = "SELECT 
  ROW_NUMBER() OVER (ORDER BY CAST(AccNo AS UNSIGNED) ASC) AS `S_No`,
  IDNo AS `ID_No`,
  Name,
  Designation,
  AccNo AS `Acc_No`,
  CASE 
    WHEN issue_date IS NOT NULL THEN DATE_FORMAT(issue_date, '%d/%m/%Y') 
  END AS `Issue_Date`,
  DATE_FORMAT(issue_date_due, '%d/%m/%Y') AS `Due_Date`
FROM (
  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    biblio.author AS Author,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    issues.issuedate AS issue_date,
    issues.date_due AS issue_date_due
  FROM issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN borrowers b2 ON b2.borrowernumber = issues.issuer_id
    LEFT JOIN items ON issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE 
    TRIM(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]')) IS NOT NULL
    AND DATE(issues.issuedate) BETWEEN {$fdate_sql} AND {$tdate_sql}
    {$cat_filter}

  UNION ALL

  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    biblio.author AS Author,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    old_issues.issuedate AS issue_date,
    old_issues.date_due AS issue_date_due
  FROM old_issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber 
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN borrowers b2 ON b2.borrowernumber = old_issues.issuer_id 
    LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE 
    TRIM(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]')) IS NOT NULL
    AND DATE(old_issues.issuedate) BETWEEN {$fdate_sql} AND {$tdate_sql}
    {$cat_filter}
) AS cte
GROUP BY AccNo, IDNo, Name, Designation, Title, Author, issue_date, issue_date_due
ORDER BY CAST(AccNo AS UNSIGNED) ASC";

    // Category placeholders appear twice (issues + old_issues).
    if (!$include_all) {
      $params = array_merge($params, $categories);
    }

    $query = empty($params)
      ? $this->db->query($sql)
      : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
  }

 public function get_return_report($fdate, $tdate = null, $category = 'All')
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    if ($tdate === null || $tdate === '') {
      $tdate = $fdate;
    }

    if (!is_array($category)) {
      $category = array($category);
    }
    $categories = array();
    foreach ($category as $c) {
      $c = trim((string) $c);
      if ($c !== '') {
        $categories[] = $c;
      }
    }
    if (empty($categories)) {
      $categories = array('All');
    }

    $include_all = false;
    foreach ($categories as $c) {
      if (strcasecmp($c, 'All') === 0) {
        $include_all = true;
        break;
      }
    }

    $fdate_sql = $this->db->escape($fdate);
    $tdate_sql = $this->db->escape($tdate);

    $cat_filter = '';
    $params = array();
    if (!$include_all) {
      $placeholders = implode(', ', array_fill(0, count($categories), '?'));
      $cat_filter = " AND borrowers.categorycode IN ({$placeholders}) ";
      $params = $categories;
    }

    $sql = "SELECT
  ROW_NUMBER() OVER (ORDER BY CAST(items.barcode AS UNSIGNED) ASC) AS `S_No`,
  borrowers.cardnumber AS `ID_No`,
  CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS `Name`,
  COALESCE(desig.attribute, '') AS `Designation`,
  items.barcode AS `Acc_No`,
  DATE_FORMAT(old_issues.date_due, '%d/%m/%Y') AS `Due_Date`,
  DATE_FORMAT(old_issues.returndate, '%d/%m/%Y') AS `Return_Date`
FROM old_issues
LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber
LEFT JOIN borrower_attributes desig
  ON borrowers.borrowernumber = desig.borrowernumber
  AND desig.code = 'DESIG'
LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
WHERE
  TRIM(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]')) IS NOT NULL
  AND DATE(old_issues.returndate) BETWEEN {$fdate_sql} AND {$tdate_sql}
  {$cat_filter}
GROUP BY old_issues.issue_id
ORDER BY CAST(items.barcode AS UNSIGNED) ASC";

    $query = empty($params)
      ? $this->db->query($sql)
      : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
  }

  /**
   * Currently issued items (issues table only) with optional category filter.
   */
  public function get_current_checkout_report($category = 'All')
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    if (!is_array($category)) {
      $category = array($category);
    }
    $categories = array();
    foreach ($category as $c) {
      $c = trim((string) $c);
      if ($c !== '') {
        $categories[] = $c;
      }
    }
    if (empty($categories)) {
      $categories = array('All');
    }

    $include_all = false;
    foreach ($categories as $c) {
      if (strcasecmp($c, 'All') === 0) {
        $include_all = true;
        break;
      }
    }

    $cat_filter = '';
    $params = array();
    if (!$include_all) {
      $placeholders = implode(', ', array_fill(0, count($categories), '?'));
      $cat_filter = " AND b1.categorycode IN ({$placeholders}) ";
      $params = $categories;
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (ORDER BY issues.issuedate DESC, items.barcode) AS `S_No`,
          b1.cardnumber AS `ID_No`,
          CONCAT_WS(' ', b1.firstname, b1.surname) AS `Name`,
          COALESCE(desig.attribute, '') AS `Designation`,
          items.barcode AS `Acc_No`,
          DATE_FORMAT(issues.issuedate, '%d/%m/%Y') AS `Issue_Date`,
          DATE_FORMAT(issues.date_due, '%d/%m/%Y') AS `Due_Date`
      FROM issues
      LEFT JOIN borrowers b1
          ON b1.borrowernumber = issues.borrowernumber
      LEFT JOIN borrower_attributes desig
          ON b1.borrowernumber = desig.borrowernumber
          AND desig.code = 'DESIG'
      LEFT JOIN items
          ON items.itemnumber = issues.itemnumber
      WHERE 1 = 1
        {$cat_filter}
      ORDER BY issues.issuedate DESC, items.barcode
    ";

    $query = empty($params)
      ? $this->db->query($sql)
      : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
  }
  
  public function get_new_arrivals()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $query = $this->db->query("
      SELECT
          ROW_NUMBER() OVER (ORDER BY MIN(i.dateaccessioned) DESC, b.biblionumber) AS `S_No`,
          b.biblionumber,
          MIN(i.barcode) AS `Acc_No`,
          SUBSTRING_INDEX(MIN(m.isbn), ' ', 1) AS `isbn`,
          b.title AS `Title`,
          IFNULL(b.author, '') AS `Author`,
          MIN(i.itemcallnumber) AS `Class_No`,
          MIN(m.publishercode) AS `Publisher`,
          IFNULL(
              ExtractValue(MAX(bm.metadata), '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Subject`
      FROM items i
      LEFT JOIN biblioitems m
          ON i.biblioitemnumber = m.biblioitemnumber
      LEFT JOIN biblio b
          ON i.biblionumber = b.biblionumber
      LEFT JOIN biblio_metadata bm
          ON b.biblionumber = bm.biblionumber
      WHERE DATE_SUB(CURDATE(), INTERVAL 300 DAY) <= i.dateaccessioned
        AND m.isbn IS NOT NULL
        AND m.isbn != ''
        AND SUBSTRING_INDEX(m.isbn, ' ', 1) != ''
      GROUP BY
          b.biblionumber,
          b.title,
          b.author
      HAVING isbn != ''
      ORDER BY MIN(i.dateaccessioned) DESC, b.biblionumber
      LIMIT 40
    ");

    return $query ? $query->result() : array();
  }

  /**
   * Count rows for LAC Recommendations (Annexure-A).
   *
   * @param int|null $from_year Copyright year from (inclusive)
   * @param int|null $to_year   Copyright year to (inclusive)
   * @param string   $search    Optional title/author/isbn filter
   */
  public function count_lac_new_titles_report($from_date = null, $to_date = null, $search = '')
  {
    if (!$this->session->userdata('loggedin')) {
      return 0;
    }

    $where = $this->lac_date_where_sql($from_date, $to_date);
    $where .= $this->lac_search_where_sql($search);
    $query = $this->db->query("
      SELECT COUNT(*) AS `cnt`
      FROM suggestions s
      LEFT JOIN borrowers bo
          ON s.suggestedby = bo.borrowernumber
      WHERE 1=1
      {$where}
    ");

    if (!$query || !$query->row()) {
      return 0;
    }
    return (int) $query->row()->cnt;
  }

  /**
   * New titles for LAC Recommendations (Annexure-A) from Koha suggestions.
   * Recommendation columns are filled from suggestions.created_by (falls back to suggester userid).
   * Date filter uses suggestion created date.
   *
   * @param int|null    $limit
   * @param int         $offset
   * @param string|null $from_date Y-m-d
   * @param string|null $to_date   Y-m-d
   * @param string      $search
   */
  public function get_lac_new_titles_report($limit = null, $offset = 0, $from_date = null, $to_date = null, $search = '')
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $offset = max(0, (int) $offset);
    $where = $this->lac_date_where_sql($from_date, $to_date);
    $where .= $this->lac_search_where_sql($search);

    $created_expr = $this->lac_created_by_sql_expr();
    $sql = "
      SELECT
          IFNULL(s.author, '') AS `Author`,
          IFNULL(s.title, '') AS `Title`,
          IFNULL(s.publishercode, '') AS `Publisher`,
          IFNULL(COALESCE(NULLIF(TRIM(CAST(s.copyrightdate AS CHAR)), ''), YEAR(s.suggesteddate)), '') AS `Year`,
          '' AS `Subject`,
          IFNULL(s.isbn, '') AS `ISBN`,
          {$created_expr} AS `Created_By`
      FROM suggestions s
      LEFT JOIN borrowers bo
          ON s.suggestedby = bo.borrowernumber
      WHERE 1=1
      {$where}
      ORDER BY s.title, s.suggestionid
    ";

    if ($limit !== null) {
      $limit = max(1, (int) $limit);
      $sql .= ' LIMIT ' . $limit . ' OFFSET ' . $offset;
    }

    $query = $this->db->query($sql);
    if (!$query) {
      return array();
    }

    $rows = $query->result();
    $sno = $offset + 1;
    foreach ($rows as $row) {
      $row->S_No = $sno++;
    }
    return $rows;
  }

  /**
   * Prefer suggestions.created_by when present; else suggester userid / cardnumber.
   * @return string SQL expression
   */
  protected function lac_created_by_sql_expr()
  {
    $has_created = false;
    try {
      if ($this->db->table_exists('suggestions')) {
        $fields = $this->db->list_fields('suggestions');
        $has_created = is_array($fields) && in_array('created_by', $fields, true);
      }
    } catch (Exception $e) {
      $has_created = false;
    }

    if ($has_created) {
      return "IFNULL(NULLIF(TRIM(s.created_by), ''), IFNULL(NULLIF(TRIM(bo.userid), ''), IFNULL(bo.cardnumber, '')))";
    }
    return "IFNULL(NULLIF(TRIM(bo.userid), ''), IFNULL(bo.cardnumber, ''))";
  }

  /**
   * Build suggesteddate filter SQL for LAC suggestions.
   *
   * @param string|null $from_date Y-m-d
   * @param string|null $to_date   Y-m-d
   * @return string
   */
  protected function lac_date_where_sql($from_date, $to_date)
  {
    $from_date = trim((string) $from_date);
    $to_date = trim((string) $to_date);
    if ($from_date === '' || $to_date === '') {
      return '';
    }
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from_date) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $to_date)) {
      return '';
    }
    if ($from_date > $to_date) {
      $tmp = $from_date;
      $from_date = $to_date;
      $to_date = $tmp;
    }

    return ' AND DATE(s.suggesteddate)'
      . ' BETWEEN ' . $this->db->escape($from_date)
      . ' AND ' . $this->db->escape($to_date);
  }

  /**
   * Optional search filter for LAC DataTables (suggestions).
   *
   * @param string $search
   * @return string
   */
  protected function lac_search_where_sql($search)
  {
    $search = trim((string) $search);
    if ($search === '') {
      return '';
    }
    $esc = $this->db->escape_like_str($search);
    return " AND ("
      . "IFNULL(s.title,'') LIKE '%{$esc}%' ESCAPE '!'"
      . " OR IFNULL(s.author,'') LIKE '%{$esc}%' ESCAPE '!'"
      . " OR IFNULL(s.isbn,'') LIKE '%{$esc}%' ESCAPE '!'"
      . " OR IFNULL(s.publishercode,'') LIKE '%{$esc}%' ESCAPE '!'"
      . ")";
  }

  /**
   * Subject-wise catalogue items (itype BK) filtered by MARC 650$a.
   */
  public function get_subject_wise_report($subject)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $subject = trim((string) $subject);
    if ($subject === '') {
      return array();
    }

    $subject_expr = "IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'), '')";

    $sql = "
      SELECT
          ROW_NUMBER() OVER (
              ORDER BY CAST(items.barcode AS UNSIGNED), items.barcode
          ) AS `S_No`,
          items.dateaccessioned AS `Date`,
          items.barcode AS `Accession_Number`,
          IFNULL(biblio.author, '') AS `Author_1`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Author_2`,
          biblio.title AS `Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
              ''
          ) AS `Sub_Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Edition`,
          {$subject_expr} AS `Subject`,
          biblioitems.publishercode AS `Publisher`,
          biblioitems.place AS `Place_of_Pub`,
          biblioitems.publicationyear AS `Year`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.pages AS `Pages`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
              ''
          ) AS `Vol`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
              ''
          ) AS `Enc_Mat`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
              ''
          ) AS `Book_Supplier_Name`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
              ''
          ) AS `Invoice_No`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
              ''
          ) AS `Invoice_Date`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
              ''
          ) AS `Curr_Code`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Price`,
          items.price AS `Price_INR`,
          '' AS `Dis`,
          items.location AS `Location`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
              ''
          ) AS `Nature`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'),
              ''
          ) AS `Status`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
              ''
          ) AS `Remarks`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      WHERE items.itype = 'BK'
        AND {$subject_expr} LIKE ?
      ORDER BY
          CAST(items.barcode AS UNSIGNED),
          items.barcode
    ";

    $query = $this->db->query($sql, array('%' . $subject . '%'));
    return $query ? $query->result() : array();
  }

  /**
   * Book catalogue report (itype BK) — same fields as Subject Wise, no subject filter.
   */
  public function get_book_report()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $sql = "
      SELECT
          items.dateaccessioned AS `Date`,
          items.barcode AS `Accession_Number`,
          IFNULL(biblio.author, '') AS `Author_1`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Author_2`,
          biblio.title AS `Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
              ''
          ) AS `Sub_Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Edition`,
          biblioitems.publishercode AS `Publisher`,
          biblioitems.place AS `Place_of_Pub`,
          biblio.copyrightdate AS `Year`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.pages AS `Pages`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
              ''
          ) AS `Vol`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
              ''
          ) AS `Enc_Mat`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
              ''
          ) AS `Book_Supplier_Name`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
              ''
          ) AS `Invoice_No`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
              ''
          ) AS `Invoice_Date`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
              ''
          ) AS `Curr_Code`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Price`,
          items.price AS `Price_INR`,
          IFNULL(IF(ao.discount IS NULL OR TRIM(CAST(ao.discount AS CHAR)) = '', '', CONCAT(ao.discount, '%')), '') AS `Dis`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
              ''
          ) AS `Nature`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'),
              ''
          ) AS `Status`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
              ''
          ) AS `Remarks`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      LEFT JOIN aqorders_items aoi
          ON aoi.itemnumber = items.itemnumber
      LEFT JOIN aqorders ao
          ON ao.ordernumber = aoi.ordernumber
      WHERE items.itype = 'BK'
      ORDER BY
          CAST(items.barcode AS UNSIGNED),
          items.barcode
    ";

    $query = $this->db->query($sql);
    return $query ? $query->result() : array();
  }

  /**
   * Patent catalogue report (itype PATENT).
   */
  public function get_patent_report()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (
              ORDER BY CAST(REPLACE(items.barcode, 'PT', '') AS UNSIGNED)
          ) AS `S_No`,
          items.barcode AS `ACCNO`,
          IFNULL(biblio.author, 'NOT AVAILABLE') AS `AUTHOR`,
          biblio.title AS `TITLE`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"C\"]'),
              'NOT AVAILABLE'
          ) AS `COUNTRY`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
              'NOT AVAILABLE'
          ) AS `PATENT_NO`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'),
              'NOT AVAILABLE'
          ) AS `SUBJECT`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      WHERE items.itype = 'PATENT'
      ORDER BY CAST(REPLACE(items.barcode, 'PT', '') AS UNSIGNED)
    ";

    $query = $this->db->query($sql);
    return $query ? $query->result() : array();
  }

  /**
   * Application Notes catalogue report (itype AL).
   */
  public function get_application_notes_report()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (ORDER BY items.barcode) AS `S_No`,
          items.barcode AS `Accession_No`,
          biblioitems.publishercode AS `Publisher`,
          biblio.notes AS `General_Note`,
          biblio.title AS `Title`,
          ExtractValue(
              biblio_metadata.metadata,
              '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'
          ) AS `Subject`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      WHERE items.itype = 'AL'
      ORDER BY items.barcode
    ";

    $query = $this->db->query($sql);
    return $query ? $query->result() : array();
  }

  /**
   * Reports book catalogue report (itype REPORT).
   */
  public function get_reports_book_report()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (
              ORDER BY CAST(REPLACE(items.barcode, 'R', '') AS UNSIGNED)
          ) AS `S_No`,
          items.barcode AS `Accession_No`,
          ExtractValue(items.more_subfields_xml,
              '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'
          ) AS `REPORTNO`,
          biblio.title AS `TITLE`,
          biblio.subtitle AS `SUBTITLE`,
          biblioitems.pages AS `NOOFPAGES`,
          ExtractValue(items.more_subfields_xml,
              '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'
          ) AS `PERORGREPN`,
          ExtractValue(items.more_subfields_xml,
              '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'
          ) AS `REPORTTYPE`,
          ExtractValue(items.more_subfields_xml,
              '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'
          ) AS `PERORGID`,
          ExtractValue(items.more_subfields_xml,
              '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'
          ) AS `SPONBODYID`,
          ExtractValue(
              biblio_metadata.metadata,
              '//datafield[@tag=\"041\"]/subfield[@code=\"a\"]'
          ) AS `LANGID`,
          biblioitems.publishercode AS `PUBLISHER`,
          biblio.copyrightdate AS `REPDATE`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      WHERE items.itype = 'REPORT'
      ORDER BY CAST(REPLACE(items.barcode, 'R', '') AS UNSIGNED)
    ";

    $query = $this->db->query($sql);
    return $query ? $query->result() : array();
  }

  /**
   * Journals book catalogue report (itype JRL).
   */
  public function get_journals_book_report()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (
              ORDER BY CAST(REPLACE(items.barcode, 'J', '') AS UNSIGNED)
          ) AS `S_No`,
          items.barcode AS `ACC_NO`,
          biblio.title AS `TITLE`,
          biblio.part_number AS `VOLUME`,
          ExtractValue(
              biblio_metadata.metadata,
              '//datafield[@tag=\"310\"]/subfield[@code=\"a\"]'
          ) AS `PERIOD`,
          ExtractValue(
              biblio_metadata.metadata,
              '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'
          ) AS `YEAR`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      WHERE items.itype = 'JRL'
      ORDER BY CAST(REPLACE(items.barcode, 'J', '') AS UNSIGNED)
    ";

    $query = $this->db->query($sql);
    return $query ? $query->result() : array();
  }

  /**
   * EBook catalogue report (itype EB).
   */
  public function get_ebook_report()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $sql = "
      SELECT
          DATE_FORMAT(items.dateaccessioned, '%d-%m-%Y') AS `Accession_Date`,
          items.barcode AS `Accession_No`,
          biblio.title AS `Title`,
          CONCAT_WS(', ',
              NULLIF(biblio.author, ''),
              NULLIF(
                  ExtractValue(
                      biblio_metadata.metadata,
                      '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'
                  ),
                  ''
              )
          ) AS `Author`,
          NULLIF(
              ExtractValue(
                  biblio_metadata.metadata,
                  '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'
              ),
              ''
          ) AS `Subject`,
          biblioitems.pages AS `Pages`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.publishercode AS `Publisher`,
          biblio.copyrightdate AS `Year`,
          NULLIF(
              ExtractValue(
                  biblio_metadata.metadata,
                  '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'
              ),
              ''
          ) AS `File_URL`
      FROM items
      LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
      LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
      LEFT JOIN biblio_metadata ON items.biblionumber = biblio_metadata.biblionumber
      WHERE items.itype = 'EB'
      ORDER BY CAST(REGEXP_REPLACE(items.barcode, '[^0-9]', '') AS UNSIGNED), items.barcode
    ";

    $query = $this->db->query($sql);
    return $query ? $query->result() : array();
  }

  /**
   * Search catalogue by barcode — returns wide field set; empty columns filtered in UI.
   */
  public function get_search_catalogue_report($barcode)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $barcode = trim((string) $barcode);
    if ($barcode === '') {
      return array();
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (ORDER BY items.barcode) AS `S_No`,
          items.barcode AS `Accession_No`,
          items.itype AS `Item_Type`,
          biblio.title AS `Title`,
          CONCAT_WS(', ',
              NULLIF(biblio.author, ''),
              NULLIF(
                  ExtractValue(
                      biblio_metadata.metadata,
                      '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'
                  ),
                  ''
              )
          ) AS `Author`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'), '') AS `Subtitle`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS `Edition`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"b\"]'), '') AS `Publisher`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'), '') AS `Year`,
          IFNULL(biblioitems.place, '') AS `Place`,
          IFNULL(biblioitems.isbn, '') AS `ISBN`,
          IFNULL(biblioitems.pages, '') AS `Pages`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'), '') AS `Subject`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS `File_Path`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"y\"]'), '') AS `File_Name`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"710\"]/subfield[@code=\"a\"]'), '') AS `Standard`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"088\"]/subfield[@code=\"a\"]'), '') AS `Standard_No`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"310\"]/subfield[@code=\"a\"]'), '') AS `Period`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"C\"]'), '') AS `Country`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'), '') AS `Patent_No`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'), '') AS `Report_No`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'), '') AS `Report_Type`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'), '') AS `Org_ID`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '') AS `Sponsor`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'), '') AS `Invoice_No`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'), '') AS `Invoice_Date`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'), '') AS `Currency_Code`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"b\"]'), '') AS `Price_Foreign`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"c\"]'), '') AS `Currency`,
          items.price AS `Price_INR`,
          IFNULL(items.location, '') AS `Location`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'), '') AS `Nature`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '') AS `Status`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'), '') AS `Remarks`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'), '') AS `Supplier`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      WHERE items.barcode = ?
      ORDER BY items.barcode
    ";

    $query = $this->db->query($sql, array($barcode));
    return $query ? $query->result() : array();
  }

  public function get_itemtypes()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $query = $this->db->query("
      SELECT itemtype, description
      FROM itemtypes
      WHERE itemtype IS NOT NULL
        AND itemtype != ''
      ORDER BY description, itemtype
    ");

    return $query ? $query->result() : array();
  }

  public function get_top_books_report($fdate = null, $tdate = null, $limit = 10)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    if ($fdate === null || $fdate === '') {
      $fdate = date('Y-m-d', strtotime('-365 days'));
    }
    if ($tdate === null || $tdate === '') {
      $tdate = date('Y-m-d');
    }
    $limit = (int) $limit;
    if ($limit < 1) {
      $limit = 10;
    }

    $fdate_sql = $this->db->escape($fdate);
    $tdate_sql = $this->db->escape($tdate);

    $query = $this->db->query("SELECT
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS `RANK`,
    Barcode,
    Title,
    Author,
    COUNT(*) AS `Issue_Count`
FROM (
    SELECT
        i.barcode AS Barcode,
        b.title AS Title,
        b.author AS Author
    FROM issues iss
    INNER JOIN items i
        ON iss.itemnumber = i.itemnumber
    INNER JOIN biblio b
        ON i.biblionumber = b.biblionumber
    WHERE DATE(iss.issuedate) BETWEEN {$fdate_sql} AND {$tdate_sql}

    UNION ALL

    SELECT
        i.barcode AS Barcode,
        b.title AS Title,
        b.author AS Author
    FROM old_issues oi
    INNER JOIN items i
        ON oi.itemnumber = i.itemnumber
    INNER JOIN biblio b
        ON i.biblionumber = b.biblionumber
    WHERE DATE(oi.issuedate) BETWEEN {$fdate_sql} AND {$tdate_sql}
) AS books
GROUP BY
    Barcode,
    Title,
    Author
ORDER BY
    `Issue_Count` DESC
LIMIT {$limit}");

    return $query ? $query->result() : array();
  }

  public function get_top_users_report($fdate = null, $tdate = null, $limit = 10)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    if ($fdate === null || $fdate === '') {
      $fdate = date('Y-m-d', strtotime('-365 days'));
    }
    if ($tdate === null || $tdate === '') {
      $tdate = date('Y-m-d');
    }
    $limit = (int) $limit;
    if ($limit < 1) {
      $limit = 10;
    }

    $fdate_sql = $this->db->escape($fdate);
    $tdate_sql = $this->db->escape($tdate);

    $query = $this->db->query("SELECT
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS `RANK`,
    IDNo AS `ID_No`,
    Name,
    Designation,
    COUNT(*) AS `Count`
FROM (
    SELECT
        items.barcode AS AccNo,
        borrowers.cardnumber AS IDNo,
        CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
        COALESCE(desig.attribute, '') AS Designation
    FROM issues
        LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
        LEFT JOIN borrower_attributes desig
          ON borrowers.borrowernumber = desig.borrowernumber
          AND desig.code = 'DESIG'
        LEFT JOIN items ON issues.itemnumber = items.itemnumber
    WHERE DATE(issues.issuedate) BETWEEN {$fdate_sql} AND {$tdate_sql}

    UNION ALL

    SELECT
        items.barcode AS AccNo,
        borrowers.cardnumber AS IDNo,
        CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
        COALESCE(desig.attribute, '') AS Designation
    FROM old_issues
        LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber
        LEFT JOIN borrower_attributes desig
          ON borrowers.borrowernumber = desig.borrowernumber
          AND desig.code = 'DESIG'
        LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
    WHERE DATE(old_issues.issuedate) BETWEEN {$fdate_sql} AND {$tdate_sql}
) AS cte
GROUP BY IDNo, Name, Designation
ORDER BY COUNT(*) DESC
LIMIT {$limit}");

    return $query ? $query->result() : array();
  }
  
  public function get_single_book_report($barcode) {
  
  $usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) { 
    	$query = $this->db->query("
    	SELECT 
  ROW_NUMBER() OVER (ORDER BY COALESCE(issue_date, cte.returndate) ASC) AS 'S.No.',
  IDNo AS 'ID_No',
  Name,
  Designation,
  AccNo AS 'Acc_No',
  Title,
  CASE WHEN issue_date IS NOT NULL THEN DATE_FORMAT(issue_date, '%d/%m/%Y') END AS 'Issue_Date',
  CASE WHEN renewals_count IS NOT NULL THEN renewals_count ELSE cte.renewals_count END AS 'Renewal_Count',
  CASE WHEN lastreneweddate IS NOT NULL THEN DATE_FORMAT(lastreneweddate, '%d/%m/%Y') ELSE DATE_FORMAT(cte.lastreneweddate, '%d/%m/%Y') END AS 'lastreneweddate',
  CASE WHEN cte.returndate IS NULL THEN DATE_FORMAT(cte.issue_date_due, '%d/%m/%Y') ELSE DATE_FORMAT(cte.returndate, '%d/%m/%Y') END AS 'Due_Date',
  DATE_FORMAT(cte.returndate, '%d/%m/%Y') AS 'Return_Date',
  CASE WHEN cte.returndate IS NULL THEN 'Issued' ELSE 'Returned' END AS 'Current_Status'
FROM (
  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    issues.issuedate AS issue_date,
    issues.renewals_count AS renewals_count,
    issues.lastreneweddate AS lastreneweddate,
    issues.date_due AS issue_date_due,
    NULL AS returndate
  FROM issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN items ON issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE items.barcode = ".$this->db->escape($barcode)."

  UNION

  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    old_issues.issuedate AS issue_date,
    old_issues.renewals_count AS renewals_count,
    old_issues.lastreneweddate AS lastreneweddate,
    old_issues.date_due AS issue_date_due,
    old_issues.returndate AS Return_Date
  FROM old_issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber 
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE items.barcode = ".$this->db->escape($barcode)."
) AS cte
ORDER BY COALESCE(issue_date, cte.returndate) ASC    	
    	");
    }
    
    return $query->result();
  
  }
  
  public function get_single_user_report($cardnumber) {
  
  $usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) { 
    	$query = $this->db->query("
    	SELECT 
  ROW_NUMBER() OVER (ORDER BY COALESCE(issue_date, cte.returndate) ASC) AS 'S.No.',
  IDNo AS 'ID_No',
  Name,
  Designation,
  AccNo AS 'Acc_No',
  Title,
  CASE WHEN issue_date IS NOT NULL THEN DATE_FORMAT(issue_date, '%d/%m/%Y') END AS 'Issue_Date',
  CASE WHEN renewals_count IS NOT NULL THEN renewals_count ELSE cte.renewals_count END AS 'Renewal_Count',
  CASE WHEN lastreneweddate IS NOT NULL THEN DATE_FORMAT(lastreneweddate, '%d/%m/%Y') ELSE DATE_FORMAT(cte.lastreneweddate, '%d/%m/%Y') END AS 'lastreneweddate',
  CASE WHEN cte.returndate IS NULL THEN DATE_FORMAT(cte.issue_date_due, '%d/%m/%Y') ELSE DATE_FORMAT(cte.returndate, '%d/%m/%Y') END AS 'Due_Date',
  DATE_FORMAT(cte.returndate, '%d/%m/%Y') AS 'Return_Date',
  CASE WHEN cte.returndate IS NULL THEN 'Issued' ELSE 'Returned' END AS 'Current_Status'
FROM (
  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    issues.issuedate AS issue_date,
    issues.renewals_count AS renewals_count,
    issues.lastreneweddate AS lastreneweddate,
    issues.date_due AS issue_date_due,
    NULL AS returndate
  FROM issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN items ON issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE borrowers.cardnumber = ".$this->db->escape($cardnumber)."

  UNION

  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    old_issues.issuedate AS issue_date,
    old_issues.renewals_count AS renewals_count,
    old_issues.lastreneweddate AS lastreneweddate,
    old_issues.date_due AS issue_date_due,
    old_issues.returndate AS Return_Date
  FROM old_issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber 
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE borrowers.cardnumber = ".$this->db->escape($cardnumber)."
) AS cte
ORDER BY COALESCE(issue_date, cte.returndate) ASC    	
    	");
    }
    
    return $query->result();
  
  }
  
  public function get_categories()
{
$usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {
    $query = $this->db->query("
        SELECT categorycode,description
        FROM categories
        WHERE categorycode IS NOT NULL
          AND categorycode != ''
        ORDER BY categorycode
    ");
    }

    return $query->result();
}

/**
 * Distinct Retired/Transferred (borrower attribute RT) values for User Report filter.
 */
public function get_retired_transfer_options()
{
    if (!$this->session->userdata('loggedin')) {
        return array();
    }

    $query = $this->db->query("
        SELECT DISTINCT TRIM(IFNULL(attribute, '')) AS rt_value
        FROM borrower_attributes
        WHERE code = 'RT'
        ORDER BY rt_value ASC
    ");
    if (!$query) {
        return array();
    }
    $out = array();
    foreach ($query->result() as $row) {
        $val = isset($row->rt_value) ? trim((string) $row->rt_value) : '';
        if ($val === '') {
            continue;
        }
        $out[] = $val;
    }
    return $out;
}

public function get_user_report($category = 'All', $retired = 'All')
{
    if (!$this->session->userdata('loggedin')) {
        return array();
    }

    if (!is_array($category)) {
        $category = array($category);
    }
    if (!is_array($retired)) {
        $retired = array($retired);
    }

    $categories = array();
    foreach ($category as $c) {
        $c = trim((string) $c);
        if ($c !== '') {
            $categories[] = $c;
        }
    }
    if (empty($categories)) {
        $categories = array('All');
    }

    $retired_vals = array();
    foreach ($retired as $r) {
        $r = trim((string) $r);
        if ($r !== '') {
            $retired_vals[] = $r;
        }
    }
    if (empty($retired_vals)) {
        $retired_vals = array('All');
    }

    $include_all_categories = false;
    foreach ($categories as $c) {
        if (strcasecmp($c, 'All') === 0) {
            $include_all_categories = true;
            break;
        }
    }

    $include_all_retired = false;
    foreach ($retired_vals as $r) {
        if (strcasecmp($r, 'All') === 0) {
            $include_all_retired = true;
            break;
        }
    }

    $sql = "
        SELECT
            b.cardnumber,
            b.surname AS Name,
            b.phone AS INT_Phone,
            b.dateexpiry,
            b.dateenrolled,
            desig.attribute AS Designation,
            divi.attribute AS Division,
            rt.attribute AS Retired_Transferred,
            b.categorycode AS Category,
            cat.description AS Category_Description
        FROM borrowers b
        LEFT JOIN borrower_attributes desig
            ON b.borrowernumber = desig.borrowernumber
            AND desig.code = 'DESIG'
        LEFT JOIN borrower_attributes divi
            ON b.borrowernumber = divi.borrowernumber
            AND divi.code = 'DIV'
        LEFT JOIN borrower_attributes rt
            ON b.borrowernumber = rt.borrowernumber
            AND rt.code = 'RT'
        LEFT JOIN categories cat
            ON b.categorycode = cat.categorycode
        WHERE 1 = 1
    ";

    $params = array();

    if (!$include_all_categories) {
        $placeholders = implode(', ', array_fill(0, count($categories), '?'));
        $sql .= " AND b.categorycode IN ({$placeholders}) ";
        foreach ($categories as $c) {
            $params[] = $c;
        }
    }

    if (!$include_all_retired) {
        $include_blank = false;
        $exact = array();
        foreach ($retired_vals as $r) {
            if (strcasecmp($r, '__BLANK__') === 0) {
                $include_blank = true;
            } else {
                $exact[] = $r;
            }
        }
        $clauses = array();
        if ($include_blank) {
            $clauses[] = "TRIM(IFNULL(rt.attribute, '')) = ''";
        }
        if (!empty($exact)) {
            $placeholders = implode(', ', array_fill(0, count($exact), '?'));
            $clauses[] = "TRIM(IFNULL(rt.attribute, '')) IN ({$placeholders})";
            foreach ($exact as $val) {
                $params[] = $val;
            }
        }
        if (!empty($clauses)) {
            $sql .= ' AND (' . implode(' OR ', $clauses) . ') ';
        }
    }

    $sql .= ' ORDER BY b.surname, b.cardnumber ';

    $query = empty($params)
        ? $this->db->query($sql)
        : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
}

  /**
   * Single patron lookup (same columns as User Report) filtered by cardnumber.
   */
  public function get_search_user_report($cardnumber)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $cardnumber = trim((string) $cardnumber);
    if ($cardnumber === '') {
      return array();
    }

    $sql = "
        SELECT
            b.cardnumber,
            b.surname AS Name,
            b.phone AS INT_Phone,
            b.dateexpiry,
            b.dateenrolled,
            desig.attribute AS Designation,
            divi.attribute AS Division,
            rt.attribute AS Retired_Transferred,
            b.categorycode AS Category,
            cat.description AS Category_Description
        FROM borrowers b
        LEFT JOIN borrower_attributes desig
            ON b.borrowernumber = desig.borrowernumber
            AND desig.code = 'DESIG'
        LEFT JOIN borrower_attributes divi
            ON b.borrowernumber = divi.borrowernumber
            AND divi.code = 'DIV'
        LEFT JOIN borrower_attributes rt
            ON b.borrowernumber = rt.borrowernumber
            AND rt.code = 'RT'
        LEFT JOIN categories cat
            ON b.categorycode = cat.categorycode
        WHERE b.cardnumber = ?
        ORDER BY b.surname, b.cardnumber
    ";

    $query = $this->db->query($sql, array($cardnumber));
    return $query ? $query->result() : array();
  }
  
  public function get_publisher_year_wise_report ($year)
{
$usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {      
        $query = $this->db->query("
        SELECT
    items.barcode,
    items.dateaccessioned AS Date_of_Entry,
    items.itemcallnumber,
    biblio.title,
    biblio.author,
    items.itype,
    items.homebranch,
    biblio.copyrightdate AS Publisher_Year
FROM items
LEFT JOIN biblioitems
    ON items.biblioitemnumber = biblioitems.biblioitemnumber
LEFT JOIN biblio
    ON biblioitems.biblionumber = biblio.biblionumber
LEFT JOIN biblio_metadata
    ON biblioitems.biblionumber = biblio_metadata.biblionumber
WHERE biblio.copyrightdate = '$year'
ORDER BY items.barcode ASC");
        
        }
 return $query->result();
}

public function get_publisher_code()
{
$usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {
    $query = $this->db->query("
        SELECT DISTINCT publishercode
        FROM biblioitems
        WHERE publishercode IS NOT NULL
          AND publishercode != ''
        ORDER BY publishercode ASC
    ");
    }

    return $query->result();
}

public function get_publisher_person_wise_report($publisher_name)
{
    $usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {

        $sql = "
            SELECT 
                items.barcode,
                items.dateaccessioned AS Date_of_Entry,
                items.itemcallnumber,
                biblio.title,
                biblio.author,
                items.itype,
                items.homebranch,
                biblioitems.publishercode AS Publisher
            FROM items
            LEFT JOIN biblioitems 
                ON items.biblioitemnumber = biblioitems.biblioitemnumber
            LEFT JOIN biblio 
                ON biblioitems.biblionumber = biblio.biblionumber
            LEFT JOIN biblio_metadata 
                ON biblioitems.biblionumber = biblio_metadata.biblionumber
            WHERE biblioitems.publishercode LIKE ?
            ORDER BY items.barcode ASC
        ";

        // Add % for partial matching
        $query = $this->db->query(
            $sql,
            array('%' . $publisher_name . '%')
        );

        return $query->result();
    }

    return array();
}

  public function get_reservation_report($fdate = null, $tdate = null)
{
$usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {
        $date_filter = '';
        if ($fdate && $tdate) {
            $date_filter = ' WHERE DATE(r.reservedate) BETWEEN ' . $this->db->escape($fdate)
                . ' AND ' . $this->db->escape($tdate);
        }
        $query = $this->db->query("
        SELECT
    b.cardnumber AS cardnumber,
    CONCAT(b.surname, ', ', b.firstname) AS patron_name,
    COALESCE(desig.attribute, '') AS Designation,
    i.barcode,
    bib.title,
    bib.author,
    it.description AS item_type,
    r.reservedate,
    r.expirationdate,
    CASE
        WHEN r.found = 'W' THEN 'Waiting'
        WHEN r.found = 'T' THEN 'In Transit'
        WHEN r.found = 'F' THEN 'Filled'
        ELSE 'Pending'
    END AS hold_status
FROM reserves r
JOIN borrowers b
    ON r.borrowernumber = b.borrowernumber
LEFT JOIN borrower_attributes desig
    ON b.borrowernumber = desig.borrowernumber
    AND desig.code = 'DESIG'
JOIN biblio bib
    ON r.biblionumber = bib.biblionumber
LEFT JOIN items i
    ON r.itemnumber = i.itemnumber
LEFT JOIN itemtypes it
    ON i.itype = it.itemtype
" . $date_filter . "
ORDER BY r.reservedate DESC");
        
        }
 return $query->result();
}

  public function get_circulation_report($fdate, $tdate)
{
$usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {      
        $query = $this->db->query("
        SELECT 
  ROW_NUMBER() OVER (ORDER BY COALESCE(issue_date, cte.returndate) ASC) AS 'S_No',
  IDNo AS 'ID_No',
  Name,
  Designation,
  AccNo AS 'Acc_No',
  Title,
  CASE WHEN issue_date IS NOT NULL THEN DATE_FORMAT(issue_date, '%d/%m/%Y') END AS 'Issue_Date',
  CASE 
    WHEN cte.returndate IS NULL THEN DATE_FORMAT(cte.issue_date_due, '%d/%m/%Y') 
    ELSE DATE_FORMAT(cte.issue_date_due, '%d/%m/%Y') 
  END AS 'Due_Date',
  CASE 
    WHEN cte.returndate IS NOT NULL THEN DATE_FORMAT(cte.returndate, '%d/%m/%Y') 
    ELSE NULL 
  END AS 'Return_Date',
  CASE 
    WHEN cte.returndate IS NULL THEN 'Issued' 
    ELSE 'Returned' 
  END AS 'Current_Status'
FROM (
  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    issues.issuedate AS issue_date,
    issues.date_due AS issue_date_due,
    return_items.returndate AS returndate
  FROM issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN items ON issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
    LEFT JOIN old_issues return_items ON return_items.itemnumber = issues.itemnumber 
        AND return_items.borrowernumber = issues.borrowernumber
  WHERE issues.issuedate BETWEEN '$fdate' AND '$tdate'

  UNION

  SELECT 
    items.barcode AS AccNo,
    biblio.title AS Title,
    borrowers.cardnumber AS IDNo,
    CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS Name,
    COALESCE(desig.attribute, '') AS Designation,
    old_issues.issuedate AS issue_date,
    old_issues.date_due AS issue_date_due,
    old_issues.returndate AS returndate
  FROM old_issues 
    LEFT JOIN borrowers ON borrowers.borrowernumber = old_issues.borrowernumber 
    LEFT JOIN borrower_attributes desig
      ON borrowers.borrowernumber = desig.borrowernumber
      AND desig.code = 'DESIG'
    LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
    LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
  WHERE old_issues.issuedate BETWEEN '$fdate' AND '$tdate'
) AS cte
ORDER BY COALESCE(issue_date, cte.returndate) ASC");
        
        }
 return $query->result();
}

  /** @deprecated Use get_circulation_report() */
  public function get_checkout_report($fdate, $tdate)
  {
    return $this->get_circulation_report($fdate, $tdate);
  }
 
  
  public function get_so_report($basket_name)
  {
    $usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {
      // Use double quotes for the SQL query and escape the column alias
      $query = $this->db->query("
            SELECT
    IFNULL(ac.contractname, '') AS Contract_name,
    ao.ordernumber AS Order_number,
    ao.discount AS Discount,
    DATE_FORMAT(ao.entrydate, '%d-%m-%Y') AS Entry_date,
    bi.isbn AS ISBN,
    b.author AS Author,
    b.title AS Title,
    b.copyrightdate AS Publication_year,
    bi.publishercode AS Publisher,
    b.seriestitle AS Collection_title,
    ao.order_vendornote AS Note_for_vendor,
    ao.quantity AS Quantity,
    ao.rrp AS RRP,
    ab.deliveryplace AS Delivery_place,
    ab.billingplace AS Billing_place,
    ab.basketname AS Basket_name,

    MAX(CASE 
        WHEN c.currency = 'USD' THEN c.rate 
    END) AS USD_Rate,

    MAX(CASE 
        WHEN c.currency IN ('GBP', 'UKP') THEN c.rate 
    END) AS UKP_Rate,

    MAX(CASE 
        WHEN c.currency = 'EUR' THEN c.rate 
    END) AS EUR_Rate

FROM aqorders ao

LEFT JOIN biblio b
    ON ao.biblionumber = b.biblionumber

LEFT JOIN biblioitems bi
    ON ao.biblionumber = bi.biblionumber

LEFT JOIN aqbasket ab
    ON ao.basketno = ab.basketno

LEFT JOIN aqcontract ac
    ON ab.contractnumber = ac.contractnumber

LEFT JOIN currency c
    ON c.currency IN ('USD', 'GBP', 'UKP', 'EUR')

WHERE ab.basketname = '$basket_name'

GROUP BY
    ac.contractname,
    ao.ordernumber,
    ao.entrydate,
    bi.isbn,
    b.author,
    b.title,
    b.copyrightdate,
    bi.publishercode,
    b.seriestitle,
    ao.order_vendornote,
    ao.quantity,
    ao.rrp,
    ab.deliveryplace,
    ab.billingplace,
    ab.basketname

ORDER BY ao.ordernumber;
        ");
      return $query->result();
    }
  }
  
  public function get_crv_report($basket_name)
  {
    $usertype = $this->session->userdata('usertype');

    if ($this->session->userdata('loggedin')) {
      // Use double quotes for the SQL query and escape the column alias
      $query = $this->db->query("
             SELECT
                IFNULL(ac.contractname, '') AS Contract_name,
                ao.ordernumber AS Order_number,
                ao.discount AS Discount,
                DATE_FORMAT(ao.entrydate, '%d-%m-%Y') AS Entry_date,
                GROUP_CONCAT(DISTINCT i.barcode ORDER BY i.itemnumber SEPARATOR ', ') AS ACC_NO,
                bi.isbn AS ISBN,
                b.author AS Author,
                b.title AS Title,
                b.copyrightdate AS Publication_year,
                bi.publishercode AS Publisher,
                b.seriestitle AS Collection_title,
                ao.order_vendornote AS Note_for_vendor,
                ao.quantity AS Quantity,
                ao.rrp AS RRP,
                ab.deliveryplace AS Delivery_place,
                ab.billingplace AS Billing_place,
                ab.basketname AS Basket_Name,
                COALESCE(ao.invoice_currency, ao.currency) AS Currency_code,
                COALESCE(ao.invoice_unitprice, ao.unitprice) AS Cost,

                MAX(
                    CASE 
                        WHEN c.currency = 'USD' THEN c.rate 
                    END
                ) AS USD_Rate,

                MAX(
                    CASE 
                        WHEN c.currency IN ('GBP', 'UKP') THEN c.rate 
                    END
                ) AS UKP_Rate,

                MAX(
                    CASE 
                        WHEN c.currency = 'EUR' THEN c.rate 
                    END
                ) AS EUR_Rate

            FROM aqorders ao

            LEFT JOIN items i
                ON ao.biblionumber = i.biblionumber

            LEFT JOIN biblio b
                ON ao.biblionumber = b.biblionumber

            LEFT JOIN biblioitems bi
                ON ao.biblionumber = bi.biblionumber

            LEFT JOIN aqbasket ab
                ON ao.basketno = ab.basketno

            LEFT JOIN aqcontract ac
                ON ab.contractnumber = ac.contractnumber

            LEFT JOIN currency c
                ON c.currency IN ('USD', 'GBP', 'UKP', 'EUR')

            WHERE ab.basketname = '$basket_name'

            GROUP BY
                ao.ordernumber,
                ac.contractname,
                ao.discount,
                ao.entrydate,
                bi.isbn,
                b.author,
                b.title,
                b.copyrightdate,
                bi.publishercode,
                b.seriestitle,
                ao.order_vendornote,
                ao.quantity,
                ao.rrp,
                ab.deliveryplace,
                ab.billingplace,
                ab.basketname,
                ao.invoice_currency,
                ao.currency,
                ao.invoice_unitprice,
                ao.unitprice

            ORDER BY ao.ordernumber;
        ");
      return $query->result();
    }
  }

  public function get_institutional_repository()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $query = $this->db->query("
      SELECT
          i.barcode AS Barcode,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"a\"]'), '') AS Title,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"100\"]/subfield[@code=\"a\"]'), '') AS Author,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'), '') AS Publication_Year,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"b\"]'), '') AS Publication_Name,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS Period,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"a\"]'), '') AS Pages,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"y\"]'), '') AS PDF_Name,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS File_Path
      FROM biblio b
      JOIN biblio_metadata bm
          ON bm.biblionumber = b.biblionumber
      LEFT JOIN items i
          ON i.biblionumber = b.biblionumber
      WHERE i.itype = 'IR'
      ORDER BY LPAD(i.barcode, 10, '0')
    ");

    return $query ? $query->result() : array();
  }

  public function get_standards_data()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $query = $this->db->query("
      SELECT
          i.barcode AS Accession_No,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"710\"]/subfield[@code=\"a\"]'), '') AS STANDARDIN,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"088\"]/subfield[@code=\"a\"]'), '') AS STD_NO,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"a\"]'), '') AS Title,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS Document_No
      FROM biblio b
      JOIN biblio_metadata bm
          ON bm.biblionumber = b.biblionumber
      LEFT JOIN items i
          ON i.biblionumber = b.biblionumber
      WHERE i.itype = 'SD'
      ORDER BY LPAD(i.barcode, 10, '0')
    ");

    return $query ? $query->result() : array();
  }

  public function get_ereport_books()
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $query = $this->db->query("
      SELECT
          DATE_FORMAT(i.dateaccessioned, '%d-%m-%Y') AS Acc_Date,
          i.barcode AS Acc_No,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"a\"]'), '') AS Title,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"100\"]/subfield[@code=\"a\"]'), '') AS Author,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'), '') AS Copyright_Year,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"a\"]'), '') AS Pages,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"b\"]'), '') AS Publisher,
          IFNULL(ExtractValue(bm.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS File_Path
      FROM biblio b
      JOIN biblio_metadata bm
          ON bm.biblionumber = b.biblionumber
      LEFT JOIN items i
          ON i.biblionumber = b.biblionumber
      WHERE i.itype = 'ER'
      ORDER BY CAST(i.barcode AS UNSIGNED), i.barcode
    ");

    return $query ? $query->result() : array();
  }

  /**
   * Barcode range report — SELECT fields follow the catalogue profile for $itype.
   * From/To are full barcodes as entered by the user (e.g. J1–J50, PT1–PT50, 1001–1500).
   * Letter prefix is taken from the entered barcodes (not hardcoded by item type).
   */
  public function get_barcode_range_report($from_barcode, $to_barcode, $itype)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $this->load->helper('report');

    $from_barcode = trim((string) $from_barcode);
    $to_barcode = trim((string) $to_barcode);
    $itype = trim((string) $itype);
    if ($from_barcode === '' || $to_barcode === '' || $itype === '' || strtoupper($itype) === 'ALL') {
      return array();
    }

    $bounds = $this->barcode_range_bounds_from_full($from_barcode, $to_barcode);
    if ($bounds === null) {
      return array();
    }

    $profile = report_itype_profile($itype);
    $select = $this->barcode_range_select_sql($profile, $bounds['order']);
    $extra_joins = $this->barcode_range_extra_joins($profile);

    $params = $bounds['params'];
    $params[] = $itype;

    $sql = "
      SELECT
          {$select}
      FROM items
      LEFT JOIN biblio
          ON items.biblionumber = biblio.biblionumber
      LEFT JOIN biblioitems
          ON items.biblioitemnumber = biblioitems.biblioitemnumber
      LEFT JOIN biblio_metadata
          ON items.biblionumber = biblio_metadata.biblionumber
      {$extra_joins}
      WHERE items.barcode IS NOT NULL
        AND {$bounds['where']}
        AND items.itype = ?
      ORDER BY {$bounds['order']}
    ";

    $query = $this->db->query($sql, $params);
    return $query ? $query->result() : array();
  }

  /**
   * Optional joins for catalogue profiles (e.g. aqorders discount for books).
   */
  protected function barcode_range_extra_joins($profile)
  {
    if ($profile === 'book') {
      return "
        LEFT JOIN aqorders_items aoi
            ON aoi.itemnumber = items.itemnumber
        LEFT JOIN aqorders ao
            ON ao.ordernumber = aoi.ordernumber
      ";
    }
    return '';
  }

  /**
   * Build range WHERE/ORDER from full barcodes entered by the user.
   * Prefix = leading non-digit characters of From (e.g. J, R, PT); empty for numeric-only.
   *
   * @return array{where:string,order:string,params:array}|null
   */
  protected function barcode_range_bounds_from_full($from_barcode, $to_barcode)
  {
    $from_num = preg_replace('/\D+/', '', $from_barcode);
    $to_num = preg_replace('/\D+/', '', $to_barcode);
    if ($from_num === '' || $to_num === '') {
      return null;
    }

    $from_n = (int) $from_num;
    $to_n = (int) $to_num;
    if ($from_n > $to_n) {
      $tmp = $from_n;
      $from_n = $to_n;
      $to_n = $tmp;
    }

    $prefix = '';
    if (preg_match('/^([^0-9]+)/', $from_barcode, $m)) {
      $prefix = $m[1];
    }

    if ($prefix !== '') {
      $prefix_sql = $this->db->escape($prefix);
      $num_expr = "CAST(REPLACE(items.barcode, {$prefix_sql}, '') AS UNSIGNED)";
      return array(
        'where' => "items.barcode LIKE ? AND {$num_expr} BETWEEN ? AND ?",
        'order' => "{$num_expr}, items.barcode",
        'params' => array($prefix . '%', $from_n, $to_n),
      );
    }

    return array(
      'where' => "items.barcode REGEXP '^[0-9]+$' AND CAST(items.barcode AS UNSIGNED) BETWEEN ? AND ?",
      'order' => "CAST(items.barcode AS UNSIGNED), items.barcode",
      'params' => array($from_n, $to_n),
    );
  }

  /**
   * SELECT list for barcode-range profile (aliases match catalogue report columns).
   */
  protected function barcode_range_select_sql($profile, $order_sql = 'items.barcode')
  {
    $rn = "ROW_NUMBER() OVER (ORDER BY {$order_sql}) AS `S_No`";

    switch ($profile) {
      case 'book':
        return "
          items.dateaccessioned AS `Date`,
          items.barcode AS `Accession_Number`,
          IFNULL(biblio.author, '') AS `Author_1`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'), '') AS `Author_2`,
          biblio.title AS `Title`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'), '') AS `Sub_Title`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS `Edition`,
          biblioitems.publishercode AS `Publisher`,
          biblioitems.place AS `Place_of_Pub`,
          biblio.copyrightdate AS `Year`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.pages AS `Pages`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'), '') AS `Vol`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'), '') AS `Enc_Mat`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'), '') AS `Book_Supplier_Name`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'), '') AS `Invoice_No`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'), '') AS `Invoice_Date`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'), '') AS `Curr_Code`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'), '') AS `Price`,
          items.price AS `Price_INR`,
          IFNULL(IF(ao.discount IS NULL OR TRIM(CAST(ao.discount AS CHAR)) = '', '', CONCAT(ao.discount, '%')), '') AS `Dis`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'), '') AS `Nature`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '') AS `Status`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'), '') AS `Remarks`
        ";

      case 'patent':
        return "
          {$rn},
          items.barcode AS `ACCNO`,
          IFNULL(biblio.author, 'NOT AVAILABLE') AS `AUTHOR`,
          biblio.title AS `TITLE`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"C\"]'), 'NOT AVAILABLE') AS `COUNTRY`,
          IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'), 'NOT AVAILABLE') AS `PATENT_NO`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'), 'NOT AVAILABLE') AS `SUBJECT`
        ";

      case 'ir':
        return "
          {$rn},
          items.barcode AS `Barcode`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"a\"]'), '') AS `Title`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"100\"]/subfield[@code=\"a\"]'), '') AS `Author`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'), '') AS `Publication_Year`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"b\"]'), '') AS `Publication_Name`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS `Period`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"a\"]'), '') AS `Pages`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"y\"]'), '') AS `PDF_Name`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS `File_Path`
        ";

      case 'sd':
        return "
          {$rn},
          items.barcode AS `Accession_No`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"710\"]/subfield[@code=\"a\"]'), '') AS `STANDARDIN`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"088\"]/subfield[@code=\"a\"]'), '') AS `STD_NO`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"a\"]'), '') AS `Title`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS `Document_No`
        ";

      case 'er':
        return "
          DATE_FORMAT(items.dateaccessioned, '%d-%m-%Y') AS `Acc_Date`,
          items.barcode AS `Acc_No`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"a\"]'), '') AS `Title`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"100\"]/subfield[@code=\"a\"]'), '') AS `Author`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'), '') AS `Copyright_Year`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"a\"]'), '') AS `Pages`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"b\"]'), '') AS `Publisher`,
          IFNULL(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS `File_Path`
        ";

      case 'application_notes':
        return "
          {$rn},
          items.barcode AS `Accession_No`,
          biblioitems.publishercode AS `Publisher`,
          biblio.notes AS `General_Note`,
          biblio.title AS `Title`,
          ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]') AS `Subject`
        ";

      case 'reports_book':
        return "
          {$rn},
          items.barcode AS `Accession_No`,
          ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]') AS `REPORTNO`,
          biblio.title AS `TITLE`,
          biblio.subtitle AS `SUBTITLE`,
          biblioitems.pages AS `NOOFPAGES`,
          ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]') AS `PERORGREPN`,
          ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]') AS `REPORTTYPE`,
          ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]') AS `PERORGID`,
          ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]') AS `SPONBODYID`,
          ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"041\"]/subfield[@code=\"a\"]') AS `LANGID`,
          biblioitems.publishercode AS `PUBLISHER`,
          biblio.copyrightdate AS `REPDATE`
        ";

      case 'journals':
        return "
          {$rn},
          items.barcode AS `ACC_NO`,
          biblio.title AS `TITLE`,
          biblio.part_number AS `VOLUME`,
          ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"310\"]/subfield[@code=\"a\"]') AS `PERIOD`,
          ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]') AS `YEAR`
        ";

      case 'ebook':
        return "
          DATE_FORMAT(items.dateaccessioned, '%d-%m-%Y') AS `Accession_Date`,
          items.barcode AS `Accession_No`,
          biblio.title AS `Title`,
          CONCAT_WS(', ',
              NULLIF(biblio.author, ''),
              NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'), '')
          ) AS `Author`,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'), '') AS `Subject`,
          biblioitems.pages AS `Pages`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.publishercode AS `Publisher`,
          biblio.copyrightdate AS `Year`,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS `File_URL`
        ";

      case 'all':
      default:
        return "
          {$rn},
          items.barcode AS Accession_No,
          items.itype AS Item_Type,
          biblio.title AS Title,
          CONCAT_WS(', ',
              NULLIF(biblio.author, ''),
              NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'), '')
          ) AS Author,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'), '') AS Subtitle,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'), '') AS Edition,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"b\"]'), '') AS Publisher,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"260\"]/subfield[@code=\"c\"]'), '') AS Year,
          biblioitems.place AS Place,
          biblioitems.isbn AS ISBN,
          biblioitems.pages AS Pages,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'), '') AS Subject,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"u\"]'), '') AS File_Path,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"856\"]/subfield[@code=\"y\"]'), '') AS File_Name,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"710\"]/subfield[@code=\"a\"]'), '') AS Standard,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"088\"]/subfield[@code=\"a\"]'), '') AS Standard_No,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"310\"]/subfield[@code=\"a\"]'), '') AS Period,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"C\"]'), '') AS Country,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'), '') AS Patent_No,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'), '') AS Report_No,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'), '') AS Report_Type,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'), '') AS Org_ID,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '') AS Sponsor,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'), '') AS Invoice_No,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'), '') AS Invoice_Date,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'), '') AS Item_Currency,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"b\"]'), '') AS Price_Foreign,
          NULLIF(ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"c\"]'), '') AS Price_Currency,
          items.price AS Price_INR,
          items.location AS Location,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'), '') AS Nature,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '') AS Status,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'), '') AS Remarks,
          NULLIF(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'), '') AS Supplier
        ";
    }
  }

  /**
   * Items accessioned in a given calendar year — same columns as Accession Register.
   */
  public function get_accession_year_wise_report($year)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $year = (int) $year;
    if ($year < 1900 || $year > 2100) {
      return array();
    }

    $status_expr = "IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '')";

    $sql = "
      SELECT
          items.dateaccessioned AS `Date`,
          items.barcode AS `Accession_Number`,
          IFNULL(biblio.author, '') AS `Author_1`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Author_2`,
          biblio.title AS `Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
              ''
          ) AS `Sub_Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Edition`,
          biblioitems.publishercode AS `Publisher`,
          biblioitems.place AS `Place_of_Pub`,
          biblio.copyrightdate AS `Year`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.pages AS `Pages`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
              ''
          ) AS `Vol`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
              ''
          ) AS `Enc_Mat`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
              ''
          ) AS `Book_Supplier_Name`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
              ''
          ) AS `Invoice_No`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
              ''
          ) AS `Invoice_Date`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
              ''
          ) AS `Curr_Code`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Price`,
          items.price AS `Price_INR`,
          IFNULL(IF(ao.discount IS NULL OR TRIM(CAST(ao.discount AS CHAR)) = '', '', CONCAT(ao.discount, '%')), '') AS `Dis`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
              ''
          ) AS `Nature`,
          {$status_expr} AS `Status`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
              ''
          ) AS `Remarks`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      LEFT JOIN aqorders_items aoi
          ON aoi.itemnumber = items.itemnumber
      LEFT JOIN aqorders ao
          ON ao.ordernumber = aoi.ordernumber
      WHERE items.dateaccessioned IS NOT NULL
        AND YEAR(items.dateaccessioned) = ?
      ORDER BY
          items.dateaccessioned,
          CAST(items.barcode AS UNSIGNED),
          items.barcode
    ";

    $query = $this->db->query($sql, array($year));
    return $query ? $query->result() : array();
  }

  /**
   * Borrower name + designation (DESIG attribute) by cardnumber.
   *
   * @return object|null {Name, Designation, CardNumber}
   */
  public function get_borrower_name_designation($cardnumber)
  {
    if (!$this->session->userdata('loggedin')) {
      return null;
    }

    $cardnumber = trim((string) $cardnumber);
    if ($cardnumber === '') {
      return null;
    }

    $sql = "
      SELECT
          COALESCE(
              NULLIF(TRIM(CONCAT_WS(' ', borrowers.firstname, borrowers.surname)), ''),
              COALESCE(borrowers.surname, '')
          ) AS Name,
          COALESCE(desig.attribute, '') AS Designation,
          borrowers.cardnumber AS CardNumber
      FROM borrowers
      LEFT JOIN borrower_attributes desig
          ON borrowers.borrowernumber = desig.borrowernumber
          AND desig.code = 'DESIG'
      WHERE borrowers.cardnumber = ?
      LIMIT 1
    ";

    $query = $this->db->query($sql, array($cardnumber));
    return ($query && $query->num_rows() > 0) ? $query->row() : null;
  }

  /**
   * Patron photo as a data-URI for PDF / HTML preview (Koha borrower_image / patronimage).
   *
   * @param string $cardnumber
   * @return string Empty when no image
   */
  public function get_borrower_photo_data_uri($cardnumber)
  {
    if (!$this->session->userdata('loggedin')) {
      return '';
    }

    $cardnumber = trim((string) $cardnumber);
    if ($cardnumber === '') {
      return '';
    }

    $bn_q = $this->db->query(
      'SELECT borrowernumber FROM borrowers WHERE cardnumber = ? LIMIT 1',
      array($cardnumber)
    );
    if (!$bn_q || $bn_q->num_rows() === 0) {
      return '';
    }
    $borrowernumber = (int) $bn_q->row()->borrowernumber;
    if ($borrowernumber < 1) {
      return '';
    }

    $tables = array('borrower_image', 'patronimage', 'patronimages');
    foreach ($tables as $table) {
      if (!$this->db->table_exists($table)) {
        continue;
      }
      $img_q = $this->db->query(
        "SELECT mimetype, imagefile FROM `{$table}` WHERE borrowernumber = ? LIMIT 1",
        array($borrowernumber)
      );
      if (!$img_q || $img_q->num_rows() === 0) {
        continue;
      }
      $img = $img_q->row();
      $blob = isset($img->imagefile) ? $img->imagefile : null;
      if ($blob === null || $blob === '') {
        continue;
      }
      $mime = isset($img->mimetype) ? trim((string) $img->mimetype) : '';
      if ($mime === '' || stripos($mime, 'image/') !== 0) {
        $mime = 'image/jpeg';
      }
      return 'data:' . $mime . ';base64,' . base64_encode($blob);
    }

    return '';
  }

  /**
   * Weed Out report for a jumset of accession / barcode numbers.
   *
   * @param array $barcodes
   * @return array
   */
  public function get_weed_out_report(array $barcodes)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $clean = array();
    foreach ($barcodes as $b) {
      $b = trim((string) $b);
      if ($b !== '') {
        $clean[] = $b;
      }
    }
    $clean = array_values(array_unique($clean));
    if (empty($clean)) {
      return array();
    }

    $escaped = array();
    foreach ($clean as $b) {
      $escaped[] = $this->db->escape($b);
    }
    $in_list = implode(',', $escaped);
    $field_list = implode(',', $escaped);

    $sql = "
      SELECT
          items.barcode AS `Acc_No`,
          IFNULL(biblio.title, '') AS `Title`,
          IFNULL(biblio.author, '') AS `Author`,
          IFNULL(biblioitems.publishercode, '') AS `Publisher_Name`,
          IFNULL(biblio.copyrightdate, '') AS `Publisher_Year`,
          IFNULL(items.price, '') AS `Cost`,
          DATE_FORMAT(items.dateaccessioned, '%d-%m-%Y') AS `Acc_Date`,
          IFNULL(items.itype, '') AS `Item_Type`
      FROM items
      LEFT JOIN biblio
          ON items.biblionumber = biblio.biblionumber
      LEFT JOIN biblioitems
          ON items.biblioitemnumber = biblioitems.biblioitemnumber
      WHERE items.barcode IN ({$in_list})
      ORDER BY FIELD(items.barcode, {$field_list})
    ";

    $query = $this->db->query($sql);
    if (!$query) {
      return array();
    }

    $rows = $query->result();
    $sno = 1;
    foreach ($rows as $row) {
      $row->S_No = $sno++;
    }
    return $rows;
  }

  /**
   * Price / accession catalogue report (full item fields).
   *
   * @param float|int|string|null $start_price
   * @param float|int|string|null $end_price
   * @param string $itype Empty or ALL = all item types
   * @param int $limit
   * @param string $from_barcode Optional accession-from filter
   * @param string $to_barcode Optional accession-to filter
   * @return array
   */
  public function get_price_range_report($start_price, $end_price, $itype = '', $limit = 100, $from_barcode = '', $to_barcode = '')
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $limit = (int) $limit;
    if ($limit < 1) {
      $limit = 100;
    }
    if ($limit > 100000) {
      $limit = 100000;
    }

    $itype = trim((string) $itype);
    if (strtoupper($itype) === 'ALL') {
      $itype = '';
    }

    $start_raw = trim((string) $start_price);
    $end_raw = trim((string) $end_price);
    $has_start = ($start_raw !== '' && is_numeric($start_raw));
    $has_end = ($end_raw !== '' && is_numeric($end_raw));
    $start = $has_start ? (float) $start_raw : null;
    $end = $has_end ? (float) $end_raw : null;
    if ($has_start && $has_end && $start > $end) {
      $tmp = $start;
      $start = $end;
      $end = $tmp;
    }

    $from_barcode = trim((string) $from_barcode);
    $to_barcode = trim((string) $to_barcode);
    $bounds = null;
    if ($from_barcode !== '' && $to_barcode !== '') {
      $bounds = $this->barcode_range_bounds_from_full($from_barcode, $to_barcode);
      if ($bounds === null) {
        return array();
      }
    }

    $params = array();
    $where = array('1=1');

    if ($itype !== '') {
      $where[] = 'items.itype = ?';
      $params[] = $itype;
    }

    if ($bounds !== null) {
      $where[] = '(' . $bounds['where'] . ')';
      foreach ($bounds['params'] as $p) {
        $params[] = $p;
      }
    }

    if ($has_start) {
      $where[] = 'items.price >= ?';
      $params[] = $start;
    }
    if ($has_end) {
      $where[] = 'items.price <= ?';
      $params[] = $end;
    }

    $where_sql = implode("\n        AND ", $where);
    if ($bounds !== null) {
      $order_sql = $bounds['order'];
    } elseif ($has_start || $has_end) {
      $order_sql = 'items.price DESC, CAST(items.barcode AS UNSIGNED), items.barcode';
    } else {
      $order_sql = 'CAST(items.barcode AS UNSIGNED), items.barcode';
    }

    $sql = "
      SELECT
          ROW_NUMBER() OVER (ORDER BY {$order_sql}) AS `S_No`,
          items.dateaccessioned AS `Date`,
          items.barcode AS `Accession_Number`,
          IFNULL(biblio.author, '') AS `Author_1`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Author_2`,
          biblio.title AS `Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
              ''
          ) AS `Sub_Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Edition`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"650\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Subject`,
          biblioitems.publishercode AS `Publisher`,
          biblioitems.place AS `Place_of_Pub`,
          biblioitems.publicationyear AS `Year`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.pages AS `Pages`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
              ''
          ) AS `Vol`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
              ''
          ) AS `Enc_Mat`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
              ''
          ) AS `Book_Supplier_Name`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
              ''
          ) AS `Invoice_No`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
              ''
          ) AS `Invoice_Date`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
              ''
          ) AS `Curr_Code`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Price`,
          items.price AS `Price_INR`,
          '' AS `Dis`,
          items.location AS `Location`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
              ''
          ) AS `Nature`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'),
              ''
          ) AS `Status`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
              ''
          ) AS `Remarks`
      FROM items
      LEFT JOIN biblio
          ON items.biblionumber = biblio.biblionumber
      LEFT JOIN biblioitems
          ON items.biblioitemnumber = biblioitems.biblioitemnumber
      LEFT JOIN biblio_metadata
          ON items.biblionumber = biblio_metadata.biblionumber
      WHERE {$where_sql}
      ORDER BY {$order_sql}
      LIMIT {$limit}
    ";

    $query = empty($params)
        ? $this->db->query($sql)
        : $this->db->query($sql, $params);

    return $query ? $query->result() : array();
  }

  /**
   * Items accessioned between two dates (inclusive) — same columns as Accession Year Wise.
   *
   * @param string $from_date Y-m-d
   * @param string $to_date Y-m-d
   * @return array
   */
  public function get_accession_date_wise_report($from_date, $to_date)
  {
    if (!$this->session->userdata('loggedin')) {
      return array();
    }

    $from_date = trim((string) $from_date);
    $to_date = trim((string) $to_date);
    if ($from_date === '' || $to_date === '') {
      return array();
    }
    if ($from_date > $to_date) {
      $tmp = $from_date;
      $from_date = $to_date;
      $to_date = $tmp;
    }

    $status_expr = "IFNULL(ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"S\"]'), '')";

    $sql = "
      SELECT
          items.dateaccessioned AS `Date`,
          items.barcode AS `Accession_Number`,
          IFNULL(biblio.author, '') AS `Author_1`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"700\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Author_2`,
          biblio.title AS `Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"b\"]'),
              ''
          ) AS `Sub_Title`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"250\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Edition`,
          biblioitems.publishercode AS `Publisher`,
          biblioitems.place AS `Place_of_Pub`,
          biblio.copyrightdate AS `Year`,
          biblioitems.isbn AS `ISBN`,
          biblioitems.pages AS `Pages`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"245\"]/subfield[@code=\"n\"]'),
              ''
          ) AS `Vol`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"300\"]/subfield[@code=\"e\"]'),
              ''
          ) AS `Enc_Mat`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"B\"]'),
              ''
          ) AS `Book_Supplier_Name`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"I\"]'),
              ''
          ) AS `Invoice_No`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"A\"]'),
              ''
          ) AS `Invoice_Date`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"P\"]'),
              ''
          ) AS `Curr_Code`,
          IFNULL(
              ExtractValue(biblio_metadata.metadata, '//datafield[@tag=\"365\"]/subfield[@code=\"a\"]'),
              ''
          ) AS `Price`,
          items.price AS `Price_INR`,
          IFNULL(IF(ao.discount IS NULL OR TRIM(CAST(ao.discount AS CHAR)) = '', '', CONCAT(ao.discount, '%')), '') AS `Dis`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"N\"]'),
              ''
          ) AS `Nature`,
          {$status_expr} AS `Status`,
          IFNULL(
              ExtractValue(items.more_subfields_xml, '//datafield[@tag=\"999\"]/subfield[@code=\"R\"]'),
              ''
          ) AS `Remarks`
      FROM items
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN biblio_metadata USING (biblionumber)
      LEFT JOIN aqorders_items aoi
          ON aoi.itemnumber = items.itemnumber
      LEFT JOIN aqorders ao
          ON ao.ordernumber = aoi.ordernumber
      WHERE items.dateaccessioned IS NOT NULL
        AND DATE(items.dateaccessioned) BETWEEN ? AND ?
      ORDER BY
          items.dateaccessioned,
          CAST(items.barcode AS UNSIGNED),
          items.barcode
    ";

    $query = $this->db->query($sql, array($from_date, $to_date));
    return $query ? $query->result() : array();
  }

  /**
   * Budget expenditure summary for an accession date range.
   *
   * @param string $from_date Y-m-d
   * @param string $to_date Y-m-d
   * @param float $budget_allotted
   * @return object {Budget_Allotted, Amount_Utilized, Balance}
   */
  public function get_budget_expenditure_report($from_date, $to_date, $budget_allotted = 0)
  {
    $empty = (object) array(
      'Budget_Allotted' => (float) $budget_allotted,
      'Amount_Utilized' => 0,
      'Balance' => (float) $budget_allotted,
    );

    if (!$this->session->userdata('loggedin')) {
      return $empty;
    }

    $from_date = trim((string) $from_date);
    $to_date = trim((string) $to_date);
    if ($from_date === '' || $to_date === '') {
      return $empty;
    }
    if ($from_date > $to_date) {
      $tmp = $from_date;
      $from_date = $to_date;
      $to_date = $tmp;
    }

    $allotted = (float) $budget_allotted;
    $sql = "
      SELECT COALESCE(SUM(CAST(IFNULL(items.price, 0) AS DECIMAL(14,2))), 0) AS utilized
      FROM items
      WHERE items.dateaccessioned IS NOT NULL
        AND DATE(items.dateaccessioned) BETWEEN ? AND ?
    ";
    $query = $this->db->query($sql, array($from_date, $to_date));
    $utilized = 0.0;
    if ($query && $query->num_rows() > 0) {
      $utilized = (float) $query->row()->utilized;
    }

    return (object) array(
      'Budget_Allotted' => $allotted,
      'Amount_Utilized' => $utilized,
      'Balance' => $allotted - $utilized,
    );
  }
}
