package Opals::Tb_Search;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(

    search_record
    search_record_byBarcode
    search_record_byCallNumber
    search_record_byField
    search_record_byRId
    search_record_2Prt

    search_rpt_getItemListByStatus
);


# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

use Opals::Tb_Record qw(

    tb_record_findByRId
    tb_item_findByBarcode

);


my $sql = "select rTitle.rid as rid, rTitle.fVal as title, 
	    (select GROUP_CONCAT(rAuthor.fVal order by rAuthor.fVal desc separator ';') from tb_records as rAuthor 
		    where  rAuthor.fId = '100_a' && rAuthor.rid = rTitle.rid) as author ,
	    (select GROUP_CONCAT(rISBN.fVal) from tb_records as rISBN 
		    where  rISBN.fId = '020_a' && rISBN.rid = rTitle.rid) as isbn,
	    (select  GROUP_CONCAT(rPubDate.fVal) from tb_records as rPubDate 
		    where  rPubDate.fId = '260_c' && rPubDate.rid = rTitle.rid) as pubDate
        from tb_records  as rTitle 
        where rTitle.fId = '245_a' && rTitle.deleted <> '1' ";


sub search_record_byBarcode{

    my ($dbh, $barcode ,$offset, $pSize, $sortOrder, $sortAttr) = @_;
    $barcode =~ s/\*/\%/g; 
    my $sqlRId = " SELECT distinct rid from tb_items where barcode like '$barcode' order by rid ";
    my $sqlCount = "select COUNT(distinct (rid)) from tb_items where barcode like '$barcode' order by rid" ;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    my $rCount = 0;
        ( $offset >= 0 )|| ( $offset = 0 );
        ( $pSize > 0 )  || ( $pSize = 1 );
    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    my @recordList = ();
    my @rIdList = ();
    my $sortKey = 'title';
    if ($sortAttr eq '100_a')       {
        $sortKey = 'author';
    }
    elsif ($sortAttr eq '020_a'){
        $sortKey = 'isbn';
    }
    elsif($sortAttr eq '260_c'){
        $sortKey = 'pubDate';
    }
    my $rOrder = $offset;
    if ($resultSize > 0 ){
        my $sth = $dbh->prepare($sqlRId);
        $sth->execute();
        while( my ($rid) = $sth->fetchrow_array()){
            push @rIdList,  $rid;
        }
        my $rids = join (',',@rIdList);
        my $query = $sql;
        $query .= ' && rTitle.rid in (' . $rids . ')';
         if ($sortKey eq 'title'){
            $sortKey = " TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FROM TRIM( LEADING 'the ' FROM LOWER( title) ) ) )" ;  
        }

         if ($sortOrder && $sortOrder ==2){
            $query .= " order by $sortKey desc ";
        }
        else{
            $query .= " order by $sortKey asc ";
        }
 
        $query .=  " LIMIT $offset , $availRange  ";
        $sth = $dbh->prepare($query);
        $sth->execute();
        while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
             push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                pubDate     => $pubDate,
                rsPos       => $rOrder,
                resultOrder => $rOrder,
            };
            $rOrder++;
        }
        $sth->finish;
    }
    return ($resultSize, \@recordList);
}

sub search_record_byCallNumber{

    my ($dbh, $classNumber ,$offset, $pSize, $sortOrder, $sortAttr) = @_;
    $classNumber =~ s/\*/\%/g; 
    my $sqlRId = " SELECT distinct rid from tb_items where classNumber regexp '$classNumber' order by rid ";
    my $sqlCount = "select COUNT(distinct (rid)) from tb_items where classNumber regexp '$classNumber' order by rid" ;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    my $rCount = 0;
        ( $offset >= 0 )|| ( $offset = 0 );
        ( $pSize > 0 )  || ( $pSize = 1 );
    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    my @recordList = ();
    my @rIdList = ();
    my $sortKey = 'title';
    if ($sortAttr eq '100_a')       {
        $sortKey = 'author';
    }
    elsif ($sortAttr eq '020_a'){
        $sortKey = 'isbn';
    }
    elsif($sortAttr eq '260_c'){
        $sortKey = 'pubDate';
    }
    my $rOrder = $offset;
    if ($resultSize > 0 ){
        my $sth = $dbh->prepare($sqlRId);
        $sth->execute();
        while( my ($rid) = $sth->fetchrow_array()){
            push @rIdList,  $rid;
        }
        my $rids = join (',',@rIdList);
        my $query = $sql;
        $query .= ' && rTitle.rid in (' . $rids . ')';
         if ($sortKey eq 'title'){
            $sortKey = " TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FROM TRIM( LEADING 'the ' FROM LOWER( title) ) ) )" ;  
        }

         if ($sortOrder && $sortOrder ==2){
            $query .= " order by $sortKey desc ";
        }
        else{
            $query .= " order by $sortKey asc ";
        }
 
        $query .=  " LIMIT $offset , $availRange  ";
        $sth = $dbh->prepare($query);
        $sth->execute();
        while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
             push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                pubDate     => $pubDate,
                rsPos       => $rOrder,
                resultOrder => $rOrder,
            };
            $rOrder++;
        }
        $sth->finish;
    }
    return ($resultSize, \@recordList);
}
    
sub search_record{
    
    my ($dbh, $sfCode, $kw, $offset, $pSize, $sortOrder, $sortAttr) = @_;
    my ($sqlCount, $sqlRId);
   
    $sqlCount = "select COUNT(distinct rid) from tb_records WHERE_CLAUSE " ;
    $sqlRId = "select distinct(rid) from tb_records WHERE_CLAUSE ";
    my @params = ();
    $kw =~ s/ / \+/g;
    if ($sfCode eq '000_a'){
        if ($kw eq '*'){
            $sqlCount   =~ s/WHERE_CLAUSE / where fId = '$sortAttr'/;
            $sqlRId        =~ s/WHERE_CLAUSE / where fId = '$sortAttr'/;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) /;
            $sqlRId        =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) /;
            push @params, '+' . $kw ;

        }
    }
    elsif($sfCode eq '12'){
         if ($kw eq '*'){
            $sqlCount   =~ s/WHERE_CLAUSE /  where rid > 0 /;
            $sqlRId        =~ s/WHERE_CLAUSE /  where rid > 0 /;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE / where rid =  $kw /;
            $sqlRId        =~ s/WHERE_CLAUSE / where rid = $kw /;
        }
    }
    elsif ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where fId = '$sfCode'/;
        $sqlRId        =~ s/WHERE_CLAUSE /where fId = '$sfCode'/;
    }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(?  IN BOOLEAN MODE) && fId = '$sfCode' /;
        $sqlRId        =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(?  IN BOOLEAN MODE) && fId = '$sfCode' /;
        push @params, '+' . $kw;

    }
    $sqlCount .= " && deleted <> '1' ";
    $sqlRId .= " && deleted <> '1' ";
    my $rCount = 0;
        ( $offset >= 0 )|| ( $offset = 0 );
        ( $pSize > 0 )  || ( $pSize = 1 );
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @params);
    
    my $availRange = ($resultSize < $pSize)? $resultSize: $resultSize - $offset + 1;

    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    my @recordList = ();
    my @rIdList = ();
    my $rOrder = $offset;
    my $sortKey = 'title';

    if ($sortAttr eq '100_a')       {
        $sortKey = 'author';
    }
    elsif ($sortAttr eq '020_a'){
        $sortKey = 'isbn';
    }
    elsif($sortAttr eq '260_c'){
        $sortKey = 'pubDate';
    }
    my $rids = "";

    if ($resultSize > 0){
        my $sth = $dbh->prepare($sqlRId);
        $sth->execute(@params);
        while( my ($rid) = $sth->fetchrow_array()){
            push @rIdList,  $rid;
        }
        $rids = join (',',@rIdList);
        my $query = $sql;
        $query .= ' && rTitle.rid in (' . $rids . ')';
        if ($sortKey eq 'title'){
            $sortKey = " TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FROM TRIM( LEADING 'the ' FROM LOWER( title) ) ) )" ;  
        }
        if ($sortOrder && $sortOrder == 1){
            $query .= " order by $sortKey desc ";
        }
        else{
            $query .= " order by $sortKey asc ";
        }

        $query .=  " LIMIT $offset , $availRange  ";

        $sth = $dbh->prepare($query);
        $sth->execute();
        while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
             push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                pubDate     => $pubDate,
                rOrder      => $rOrder+1,
                rsPos       => $rOrder,
                odd         => ($rOrder % 2)
            };
            $rOrder++;
        }
        $sth->finish;
    }
    return ($resultSize, \@recordList);
}

sub search_record_byField {

    my ($dbh, $fVal, $fId) = @_;
    my @recList = ();
    return \@recList if ($fVal eq '' || $fId  eq '');
    
    my $sql = " SELECT distinct (rid) from tb_records where fVal = ? && fId = ? && deleted <> '1'"; 
    my $sth = $dbh->prepare($sql);
    $sth->execute($fVal, $fId);
    while (my $rec = $sth->fetchrow_hashref){
        push @recList, $rec;  
    }
    $sth->finish;
    return \@recList;
}

sub search_record_byRId {

    my ($dbh, $rIdList) = @_;
    my @rIdList = split(/,/, $rIdList);
    my $resultSize = scalar(@rIdList);
    my @recordList = ();
    my $query = $sql;
    $query .= " && rTitle.rid in ($rIdList)";
    my $sortKey = '';
    my $sortOrder = "1";
  
    $query .=  " order by field(rTitle.rid, $rIdList) ";
    $query .=  " LIMIT 0 , $resultSize  ";

    my $sth = $dbh->prepare($query);
    $sth->execute();
    while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
         push @recordList,  {
            rid         => $recId,
            title       => $title,
            author      => $author,
            isbn        => $isbn,
            pubDate     => $pubDate,
        };
    }
    $sth->finish;
    return ($resultSize, \@recordList);

}

sub search_record_2Prt{
    
    my ($dbh, $sfCode, $kw,$sortOrder, $sortAttr) = @_;
    my ($sqlCount, $sqlRId);
   
    $sqlCount = "select COUNT(distinct rid) from tb_records WHERE_CLAUSE " ;
    $sqlRId = "select distinct(rid) from tb_records WHERE_CLAUSE ";
    my @params = ();
    $kw =~ s/ / \+/g;
    if ($sfCode eq '000_a'){
        if ($kw eq '*'){
            $sqlCount   =~ s/WHERE_CLAUSE / where fId = '$sortAttr'/;
            $sqlRId        =~ s/WHERE_CLAUSE / where fId = '$sortAttr'/;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) /;
            $sqlRId        =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) /;
            push @params, '+' . $kw;

        }
    }
    elsif($sfCode eq '12'){
         if ($kw eq '*'){
            $sqlCount   =~ s/WHERE_CLAUSE /  where rid > 0 /;
            $sqlRId        =~ s/WHERE_CLAUSE /  where rid > 0 /;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE / where rid =  $kw /;
            $sqlRId        =~ s/WHERE_CLAUSE / where rid = $kw /;
        }
    }
    elsif ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where fId = '$sfCode'/;
        $sqlRId        =~ s/WHERE_CLAUSE /where fId = '$sfCode'/;
    }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) && fId = '$sfCode' /;
        $sqlRId        =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) && fId = '$sfCode' /;
        push @params, '+' . $kw;

    }
    $sqlCount .= " && deleted <> '1' ";
    $sqlRId .= " && deleted <> '1' ";
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @params);
    

    my @recordList = ();
    my @rIdList = ();
    my $rOrder = 0;
    my $sortKey = 'title';

    if ($sortAttr eq '100_a')       {
        $sortKey = 'author';
    }
    elsif ($sortAttr eq '020_a'){
        $sortKey = 'isbn';
    }
    elsif($sortAttr eq '260_c'){
        $sortKey = 'pubDate';
    }
    my $rids = "";
    if ($resultSize > 0){
        my $sth = $dbh->prepare($sqlRId);
        $sth->execute(@params);
        while( my ($rid) = $sth->fetchrow_array()){
            push @rIdList,  $rid;
        }
        $rids = join (',',@rIdList);
        my $query = $sql;
        $query .= ' && rTitle.rid in (' . $rids . ')';
        if ($sortKey eq 'title'){
            $sortKey = " TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FROM TRIM( LEADING 'the ' FROM LOWER( title) ) ) )" ;  
        }
        if ($sortOrder && $sortOrder == 1){
            $query .= " order by $sortKey desc ";
        }
        else{
            $query .= " order by $sortKey asc ";
        }

        $sth = $dbh->prepare($query);
        $sth->execute();
        while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
             push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                pubDate     => $pubDate,
                rOrder      => $rOrder+1,
                rsPos       => $rOrder,
                odd         => ($rOrder % 2)
            };
            $rOrder++;
        }
        $sth->finish;
    }
    return ($resultSize, \@recordList);
}
sub search_rpt_getItemListByStatus {

    my ($dbh,$offset,$pSize,$dateFrom,$dateTo,$sortAttr,$sortOrder,$status) = @_;
    my $itemStatus={2=>'damaged' ,3=>'lost',11=>'claimReturned',12=>'claimNeverLoaned'};
    my @itemList = ();
    my $sqlCount = " select count(*)
            from tb_items i1 inner join
            (select ist1.* from
                (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist1
                left outer join
                (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist2
            on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)
            as ist on i1.barcode=ist.barcode ";

    my $sql = "select i.rid,i.barcode,i.ondate, l.id as lid, u.lastname, u.firstname, u.userbarcode,u.uid  
from ( select i1.rid ,i1.barcode,ist.ondate,i1.price,i1.classNumber from tb_items i1 inner join 
    (select ist1.* from (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist1                 
        left outer join (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist2 
        on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)  as ist on i1.barcode=ist.barcode ) as i          
        inner join tb_records r on r.rid = i.rid          
        left outer join tb_loan l on l.barcode = i.barcode && l.id = (select max(id) from tb_loan where barcode=i.barcode) 
        left outer join tb_odl o on l.id = o.idloan && o.type='$itemStatus->{$status}'
        left outer join opl_user u on u.uid = l.uid";

    if ($sortAttr eq '245_a'){
        $sql .= " where r.fId = '$sortAttr' group by i.barcode order by fVal";
    } 
    elsif ($sortAttr eq 'recordId'){
        $sql .= " group by i.barcode order by r.rid";
    }
    elsif ($sortAttr eq 'userName'){
        $sql .= " group by i.barcode order by concat(u.lastname, u.firstname, fVal)";
    }
    elsif ($sortAttr eq 'ondate'){
        $sql .= " group by i.barcode order by i.ondate ";
    }
    else {
        $sql .= " group by i.barcode order by $sortAttr";
    }

    if ($sortOrder && $sortOrder eq 'desc' ){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    my @param = $status;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @param);
        
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );

    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    $sql .= " LIMIT $offset , $availRange ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($status);
    while(my ($rid,$barcode,$ondate,$idloan,$lname,$fname,$ubarcode,$uid) = $sth->fetchrow_array()){
        my $itemInfo =  tb_item_findByBarcode($dbh,$barcode);
        push @itemList, {
             rid         => $rid,
             title       => $itemInfo->{'title'},
             author      => $itemInfo->{'author'},
             #isbn        => $itemInfo->{'isbn'},
             #publisher   => $itemInfo->{'publisher'},
             #pubDate     => $itemInfo->{'pubDate'},
             price       => $itemInfo->{'price'},
             callnumber  => $itemInfo->{'classNumber'},
             barcode     => $barcode,
             ondate      => $ondate,
             firstname    => $fname || "",
             lastname    => $lname || "",
             uid         => $uid,
             ubarcode    => $ubarcode,
        };
    }         
    $sth->finish;
    return ({"found"=>$resultSize, itemList=>\@itemList});
}



1;