#!/usr/bin/perl 

use lib "/www/opals/module";
use Opals::Context("/etc/opals/conf/ztest");

use Textbook::Teachers qw(
    teacher_find
    teacher_findByCourseSectionId
);

use Textbook::Students qw(
    student_find
    student_findById
    student_getListByCourse
    student_getListByCourseSection

);

use Textbook::Search qw(
    search_student
    search_teacher
    search_course
    search_homeroom
);

use Textbook::Books qw(
    bookList_forCourse
    bookList_forTeacher
    bookList_forStudent
    bookList_forHomeroom
    book_TotalAvailable
);

use Textbook::Preparation qw(
    getPreparation_teacher
    getPreparation_student
    getPreparation_course
    getPreparation_homeroom
);

use Opals::Equipment qw(
    
    eq_defRecord_getList
    eq_defRecord_getLists 
    eq_defItem_getList
    eq_defItem_getLists

    eq_def_addUpdate
    eq_def_delete
    eq_def_find
    
    eq_record_add
    eq_record_deleteByFid
    eq_record_find
    eq_record_findByRId
    eq_record_findByOffset
    eq_record_idGen
    eq_record_updateFieldName
    
    eq_item_add
    eq_item_deleteByFid
    eq_item_findByRId

);

#use strict;
my $dbh = Opals::Context->dbh();;
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...

 my @orCond = (
        {3 => 'textbook%' } ,
        {k => 'textbook%' }
    );
foreach my $c (@orCond){
    print $c, "\n";
    foreach $cc (keys %{$c}){
        print  $cc  , "=>" , $c->{$cc} , "\t ";
    }
    print "\n";
}
print "\n";

=item
my $rec = test_getRecInfoCSV ($dbh, 24);
#print $rec->{'245_a'} , "\n";
my $map = tb_record_index_map_010i_900e();

foreach my $field (sort {$a cmp  $b}  keys %{$map}){
        my $tag = $map->{$field}->{'tag'};
        my $sf =  $map->{$field}->{'subfield'};
        my $label = $map->{$field}->{'label'};
        my $sfield = $tag . "_" . $sf;
        print  $label , ":\t" ,$sfield , " = " , $rec->{$sfield} , "\n";

}
foreach $h ($rec->{'itemList'}){
    print $h->{'barcode'} , "\n" ;
}




my $mapRecord = {

    eqName  => {
                    245 => {a => 1 }, 
               },
    1       => {
                    100 => {a => 1},
                },
    3       => {    245 => { b=>1},
            },
    36      => {
                    300 => {a => 1, e => 2},
        },
};

my $mapItem = {
    barcode => { 852 => {p=>1}},
    itemType=> { itemType => {'EQMT' => 1}},
    6       => { 852 => {9 => 1,}},
    11      => { 652 => {c => 1,}},

};

print "Record :\n";
foreach my $id (sort keys %{$mapRecord}){
    print $id , "=>{";
    foreach my $tag ( keys %{$mapRecord->{$id}}){
        foreach my $sf (sort {$mapRecord->{$id}{$tag}{$a} <=> $mapRecord->{$id}{$tag}{$b} } keys %{$mapRecord->{$id}{$tag}}){
            print $tag , " ";
            print $sf, ",";
        }

        print "} \t";
    }
}
print "\n";
print "Item :\n";
foreach my $id (sort keys %{$mapItem}){
    print $id , "=>";
    foreach my $tag ( keys %{$mapItem->{$id}}){
        foreach my $sf (sort  {$mapItem->{$id}{$tag}{$a} <=> $mapItem->{$id}{$tag}{$b} } keys %{$mapItem->{$id}{$tag}}){
            print $tag , " ";
            print $sf, ",";
        }
        print " \t";
    }
}
print "\n";
=cut


=item
my %table;
    my ($city,$state) = ('Chicago', 'Illinois');
    push @{$table{$state}},$city;
    ($city,$state) = ('New York', 'New York');
    push @{$table{$state}},$city;
    ($city,$state) = ('Albany', 'New York');
    push @{$table{$state}},$city;
    my ($city,$state) = ('SpringField', 'Illinois');
    push @{$table{$state}},$city;
    my ($city,$state) = ('Trenton', 'New Jersey');
    push @{$table{$state}},$city;
    my ($city,$state) = ('Evanston', 'Illinois');
    push @{$table{$state}},$city;

print "\$table{'Illinois'} = " , $table{'Illinois'}  , "\n"  ;

print "test 1: ",   @{$table{'Illinois'}} , "\n\n";
push @{$table{'Illinois'}},'testing';
print "test 2: ",   @{$table{'Illinois'}} , "\n\n";


foreach my $state ( sort keys %table){
    print "$state : ";
    my @cities = @{$table{$state}};
    print join ',' , sort(@cities);
    print ".\n";
}
cut
=item
my @a = (
    [1,2,3],
    [4,5,6],
    [7,8,9],
);
print "[1,2,3]\n";
print "[4,5,6]\n";
print "[7,8,9]\n";

print  "\$a[1] = " ,  $a[1] , "\n";
print "\@{\$a[1]} = " ,  @{$a[1]} , "\n";
print "\$a[1]->[2] = " , $a[1]->[2] , "\n";
print "\$a[1][2] = " , $a[1][2] , "\n";
=cut

#test_KKK();
#test_2();
#test_eq_item_findByRId();
#testSearchTeacherCourseSectionId();
#testGetPreparation_teacher();
#testGetPreparation_homeroom();
#testGetPreparation_course();
# Codes end.


exit 0;

################################################################################
sub tb_record_findByRId_brief{

    my ($dbh, $rid) = @_;
    return if $rid eq '';

    my $sql = "select fId, fVal from tb_records where rid = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    my $recordInfo;
    while (my $rec = $sth->fetchrow_hashref) {
        if (! $recordInfo->{$rec->{'fId'}}){
            $recordInfo->{$rec->{'fId'}} = $rec->{'fVal'};
        }
    }
    $sth->finish;

    return $recordInfo;
}

sub eq_maxBarcodeDeleted {
    my ($dbh,$barcode) = @_;
     my $sql_bc = $barcode;
    $sql_bc =~ s/([*+?])/\\\\$1/g;
    #$sql_bc .= '_';

    my ($maxBarcode) = $dbh->selectrow_array(<<_STH_);
select  max(barcode)
from    eq_items
where   barcode regexp '$sql_bc'
_STH_

    if ($maxBarcode && $maxBarcode =~ s/([\d]{3})$//) {
        my $dupCount = $1;
        $dupCount =~ s/^0{1,2}//;
        $dupCount++;
        $dupCount = sprintf("%0.3d", $dupCount);

        $maxBarcode .= $dupCount;
    }
    else {
        $maxBarcode = '___'.$barcode.'_000';
    }
    return $maxBarcode;
}


sub record_findByRId4Merge {

    my ($dbh,$rid) = @_;
    my $sql = " SELECT * from tb_records where rid = ? && deleted <> '1' order by fId";
    my $sth = $dbh->prepare($sql);
    my $record;
    my $tmpHash;

    $sth->execute($rid);
    while( my $rec = $sth->fetchrow_hashref){
        push @{$tmpHash->{$rec->{'fId'}}}, { 
                    fVal => $rec->{'fVal'},
                };
       $record->{$rec->{'fId'}} = {
           fVal => $rec->{'fVal'},
           fVals=> $tmpHash->{$rec->{'fId'}},
       };
    }
    my @itemList;
    $sql ="SELECT * FROM tb_items where rid = ? && deleted <> '1' order by length(barcode) , barcode ";
    $sth = $dbh->prepare($sql);
    $sth->execute($rid);
     while (my $it = $sth->fetchrow_hashref()){
        push @itemList, {
            rid         => $it->{'rid'},
            barcode     => $it->{'barcode'},
            available   => $it->{'available'},
            typeId      => $it->{'typeId'},
            locationCode=> $it->{'locationCode'},
            price       => $it->{'price'},
            classNo     => $it->{'classNumber'},
            acqDate     => $it->{'acquisitionDate'},
            poNo        => $it->{'PONumber'},
            distributor => $it->{'distributor'},
            regionCode  => $it->{'regionCode'},
            districtCode=> $it->{'districtCode'},
            buildingCode=> $it->{'buildingCode'},
            importDate  => $it->{'importDate'},
        };
    }
    $sth->finish;
    return ($record, \@itemList );    

}

sub test_record_getByRId {

    my ($dbh,$rid, $sort) = @_;
    return undef if ($rid eq '');

    my $sql = " SELECT * from tb_records_tmp where rid = ?";
    my $sth = $dbh->prepare($sql);
    my $record;
    $sth->execute($rid);
    while( my $rec = $sth->fetchrow_hashref){
        $record->{$rec->{'fId'}} = $rec->{'fVal'};
    }
    my @itemList;
    $sql ="SELECT * FROM tb_items_tmp where rid = ? && deleted <> '1' order by length(barcode) , barcode ";
    $sth = $dbh->prepare($sql);
    $sth->execute($rid);
     while (my $it = $sth->fetchrow_hashref()){
        push @itemList, {
            i           => $i,
            rid         => $it->{'rid'},
            barcode     => $it->{'barcode'},
            available   => $it->{'available'},
            typeId      => $it->{'typeId'},
            locationCode=> $it->{'locationCode'},
            price       => $it->{'price'},
            classNo     => $it->{'classNumber'},
            acqDate     => $it->{'acquisitionDate'},
            poNo        => $it->{'PONumber'},
            distributor => $it->{'distributor'},
            regionCode  => $it->{'regionCode'},
            districtCode=> $it->{'districtCode'},
            buildingCode=> $it->{'buildingCode'},
            importDate  => $it->{'importDate'},
        };
    }
    $sth->finish;
    return ($record, \@itemList );    
}


sub test_getRecInfoCSV {

    my ($dbh, $cid) = @_;
    my $mapHeader = "0,-1,2,1,-1,-1,-1,-1,-1,3,-1,-1";
    my @mapHeader = split (/,/,$mapHeader);
    my $sql = "select * from tb_csvDuplicate where cid = ? ";
    my $rec = $dbh->selectrow_hashref($sql, undef,$cid);
    my $data = $rec->{'content'};
    my @sfList = qw(
    245_a 
    245_b 
    020_a 
    100_a
    260_b
    260_c
    260_a
    250_a
    300_a
    barcode 
    price
    itemtype
);

    my @data = split(/\n/,$data);
    my $record ;
    #if (scalar(@data) > 0){
    my @first = split(/,/,@data[0]);
    my $map = ntb_record_index_map_010i_900e();
    print @first , "\n";
    #print "\n";
    foreach my $field (sort {$a cmp  $b}  keys %{$map}){
        my $tag = $map->{$field}->{'tag'};
        my $sf =  $map->{$field}->{'subfield'};
        my $sfield = $tag . "_" . $sf;
        print $sfield , "\t";
        #print "\n";
        my $val = "";
        for (my $i = 0; $i < scalar(@sfList); $i++){
            if ($sfList[$i] eq $sfield){
                $idx = $i;
                 if ($mapHeader[$idx] >= 0) {
                    $val = $first[$mapHeader[$idx]];
                }
                #print $mapHeader[$idx], "\t";
                last;
            }
        }
        $record->{$sfield} = $val;
        print "val: ",  $val , "\n";
    }

    print "\n";
    my @itemList = ();
    foreach my $holding (@data){
        my @holding = split(/,/,$holding);
         for (my $i = 0; $i < scalar(@sfList); $i++){
            if ($sfList[$i] eq 'barcode'){
                $idx = $i;
                 if ($mapHeader[$idx] >= 0) {
                    $val = $holding[$mapHeader[$idx]];
                    print "value: ", $val, "\n";
                    push @itemList, {
                        barcode => $val,
                       
                    }
                }
                last;
            }
        }

    }
    $record->{'itemList'} = \@itemList;
    return $record;

    
}



sub test_KKK{
    my $sql = "select displayOrder, fieldId as tag, subfield, fieldName, repeatable from tb_index_map " ;
    $sql .= " where fieldId >= 100 && fieldId < 900 order by displayOrder ";
    
    my $hash = $dbh->selectall_hashref($sql,'displayOrder');
       foreach my $field (sort {$hash{$a} cmp $hash{$b}}  keys %$hash){
        print $field . " \t" ;
        #print $hash->{$field};
        foreach my $sf (sort keys %{$hash->{$field}}){
            print $sf . ':' . $hash->{$field}{$sf} . "\t";
        }
        print "\n";
    }
}
sub tb_record_index_map_010i_900e{
    
    my $sql = "select fieldId as tag, subfield, fieldName, repeatable,displayOrder from tb_index_map " ;
    $sql .= " where fieldId >= 010 && fieldId < 900 order by displayOrder ";
    my $hash;
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
         $hash->{$rec->{'displayOrder'}} = {
                'tag' => $rec->{'tag'} , 
                'subfield'=> $rec->{'subfield'}, 
                'repeatable' => $rec->{'repeatable'},
                'label' => $rec->{'fieldName'}
                };
    }
    return $hash;
}


sub test_eq_item_findByRId{

    my $ret = eq_item_findByRId($dbh,390);
    foreach my $r(@{$ret}) {
        print "item id:" . $r->{'iid'} ;
        print " fields:" . $r->{'fields'};
        print " @{$r->{'fields'}}\n";
        foreach my $f (@{$r->{'fields'}}){
            print $f->{'sfid'} . ' ' . $f->{'sfval'}  ;
        }
        print "\n";
    }
}


sub testSearchTeacherCourseSectionId{

    my $params = {courseCode=>'0330', sectionId=>'7',schoolYear=>'1'};
    my $ret = teacher_findByCourseSectionId($dbh, $params);

    print "teacherID: " . $ret->{'teacherId'};
}

sub testGetPreparation_homeroom{

    my $ret = getPreparation_homeroom($dbh, 123, 1);
    foreach my $prep(@$ret){
        print $prep->{'pid'}, "-" ,$prep->{'type'} ,"\n";
        foreach my $b(@{$prep->{'book2Prepare'}}){
            print $b->{'courseId'}, "\t";
            print $b->{'ISBN'}, "\n";
        }

    }
}
sub testGetPreparation_course{

    my $params= {courseId=>75,schoolYearId=>1};
    print "testGetPreparation_course\n";
    my $ret = getPreparation_course($dbh,75,1);

    foreach my $prep(@$ret){
        print $prep->{'pid'}, "-" ,$prep->{'type'} ,"\n";
        foreach my $b(@{$prep->{'book2Prepare'}}){
            print $b->{'sectionId'}, "\t";
            print $b->{'ISBN'}, "\t";
        }
    }
}

sub testGetPreparation_teacher{

    my $params= {teacherId=>1004356,schoolYearId=>1};
    print "testGetPreparation_teacher\n";
    my $ret = getPreparation_teacher($dbh,$params);

    foreach my $prep(@$ret){
        print $prep->{'pid'}, "-" ,$prep->{'type'} ,"\n";
        foreach my $b(@{$prep->{'book2Prepare'}}){
            print $b->{'courseId'}, "\t";
            print $b->{'sectionId'}, "\t";
            print $b->{'ISBN'}, "\t";
        }
    }
}


sub testGetPreparation_course{

    my $params= {courseId=>75,schoolYearId=>1};
    print "testGetPreparation_course\n";
    my $ret = getPreparation_course($dbh,75,1);

    foreach my $prep(@$ret){
        print $prep->{'pid'}, "-" ,$prep->{'type'} ,"\n";
        foreach my $b(@{$prep->{'book2Prepare'}}){
            print $b->{'sectionId'}, "\t";
            print $b->{'ISBN'}, "\t";
        }
    }
    
}

sub testGetPreparation_student{

    my $params={studentId=>419273, schoolYearId=>1};
    my $tmp = getPreparation_student($dbh, $params);
    print "here\n";
    print $tmp, "\n";
    print scalar (@{$tmp});
    foreach my $a(@$tmp){
        foreach my $b (@{$a->{'book2Prepare'}}){
            print "CourseId: $b->{'courseId'} - ISBN:  $b->{'ISBN'} - nBook(s):  $b->{'nDistributed'} ";
        }

    }
}

sub testStudent_getListByCourse{

     my $params={courseCode=>'0211', sectionId=>'1',schoolYear=>'1'};
     my $studentList =student_getListByCourse($dbh,$params);
     print $studentList, "\n";
     print keys %$studentList , "\n";
     my $a = $studentList->{1};
     print keys %$a, "\n";
     foreach my $b (@$a){
        print $b->{studentId}, "\n";
     }
}

sub testStudent_getListByCourseSection{

     my $params={courseCode=>'0211', sectionId=>'1',schoolYear=>'1'};
     my $studentList =student_getListByCourseSection($dbh,$params);
     print $studentList,  scalar(@$studentList), "\n";

     foreach my $a (@$studentList){
        print $a->{studentId}, "\n";
     }
}

sub testTeacherFind{
    my ($ln) = @_;
    my $params={lastName=>$ln};
    my $stdList =teacher_find($dbh,$params);
    foreach my $s(@$stdList){
        print "$s->{'teacherId'}: $s->{'firstName'} $s->{'lastName'}\n";
    }
}
#################################################################################
sub testTeacherCourse{
    my $params={teacherId=>'1004356'};
    my $ret =search_course($dbh,$params);
    my $cList = $ret->{'courseList'};
    foreach my $c(@$cList){
        print $c->{'courseId'}, "\t";
        print $c->{'courseCode'} , ":\t";
        my $sections = $c->{'sections'};
        foreach my $s(@$sections){
            print  $s->{'sectionId'}, "\t";
        }
        print "\n";
    
    }
}
##########################################################
sub testSearchStudent{
    #my $param={offset=>'0',count=>'5'};
    my $param={studentId=>'smith',name=>'smith joe'};
    my @stdList= search_student($dbh,$param);

    for( my $i=0; $i <scalar(@stdList)  ; $i++){
        
        print $i , "--",  @stdList[$i]->{"studentId"} , ", " , @stdList[$i]->{"firstName"} ,", " ,@stdList[$i]->{"lastName"},"\n"
    }
}
sub testStudentFind{
#    my ($sId) = ('412480')
    my $params={studentId=>'412480'};
    my $s =student_findById($dbh,'412480');
        print $s->{'studentId'} ." ," . $s->{'firstName'} ."," . $s->{'lastName'} ."\n";

   
    }

sub testSearchStudentCourse{
    my $params = {studentId=>'412480'};
    my @cList = search_course($dbh, $params);
    for (my $i=0; $i < scalar(@cList); $i++){
        print @cList[$i]->{"courseCode"}, @cList[$i]->{"courseName"},"\n";
        my $sections = @cList[$i]->{"sections"};
        foreach my $s(@$sections){
            print "\t section:", $s->{sectionId} . "\n";
        }

}
}
sub testSearchCourse_Homeroom{
    my $params = {homeroom=>'123'};
    #my $params =  {courseCode=>'0251'};
    my $result = search_course($dbh, $params);
    my $courseList = $result->{'courseList'};
    my $found = $result->{'found'};
    print $found;
    my $sections;
    foreach my $c(@$courseList){
        print  "\t course name: " , $c->{'courseName'}, "\n";
        $sections = $c->{'sections'};
        foreach my $s(@$sections){
            print "\t sectionId:", $s->{'sectionId'}, "\n";
        }
    } 
}

sub testbookList4Course{
    my $params= {courseCode=>'0111'};
    #my $params= {courseCode=>'9997'};

    my @bList=bookList_forCourse($dbh, $params);
    print courseCode;
    for(my $i = 0; $i< scalar(@bList); $i++) {
       print @bList[$i]->{'rid'},@bList[$i]->{'title'}, @bList[$i]->{'ISBN'},"\n"
    }
}

sub testSearchCourse_byCourseCode{
    my $params = {courseCode=>'0330'};
    my $result = search_course($dbh, $params);
    my $cResultList = $result->{'courseList'};
    foreach my $c(@$cResultList){
        print keys %$c, "\n";
        print values %$c, "\n";
        print "CourseId: " , $c->{'courseId'};
        print "CourseCode:",  $c->{'courseCode'};
        my $sections = $c->{sections};

        print "there are :",  scalar (@$sections), "\n";
        foreach my $s(@$sections){
            print keys %$s, "\n";
            print values %$s, "\n";
            print $s->{'sectionId'},"\n";

        }
    }
}