package Opals::Tb_Record;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(

    tb_defRecordIndex_getList
    tb_defItemIndex_getList
    tb_defIndex_update

    tb_barcode_getMaxValue
    tb_maxBarcodeDup
    tb_barcode_getList
    tb_isbn_getList
    tb_title_getList

    tb_record_idGen
    tb_record_add
    tb_record_update
    tb_record_delete
    tb_record_setFlagUpdating

    tb_record_findByRId
    tb_record_findByRId_brief
    tb_record_findByRId4Merge
    tb_record_items_deleteAll

    tb_itemType_getList

    tb_item_add
    tb_item_update
    tb_item_findByRId
    tb_item_findByBarcode
    tb_item_setFlagUpdating
    tb_item_delete
    tb_item_deleteByBarcode
    
    tb_subfieldCode

    tb_isBarcodeExist
    tb_barcode_getMaxDup
   
    mapData2SubfieldCode
    tb_record_index_map_010i_900e

    tb_createNextBiggerBc

);

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;
use Text::CSV_XS;

use Digest::SHA qw(
    sha1_base64
    sha1_hex
);

use Time::localtime;
my $tm = localtime;
my $dateToday = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday, $tm->hour, $tm->min, $tm->sec);


#******************************************************************
#               section : Record/Item DEFINITION 
#******************************************************************

sub tb_defRecordIndex_getList{

    my ($dbh, $order ) = @_;
    my @retList;
    my $sql = "select tb.*, eq.dataType, ";
    $sql .= " if(eq.dataType = 'Number', 1, 0) as number,   if(eq.dataType = 'Checkbox', 1, 0) as checkbox ,";
    $sql .= " if(eq.dataType = 'GradeLevel', 1, 0) as gradeLevel " ;
    $sql .= " from tb_index_map as tb " ;
    $sql .= " inner join eq_fieldDataType as eq on tb.fieldType = eq.id " ;
    $sql .= " where tb.showed = 1" ;

    if ($order){
        $sql .= " order by displayOrder";
    }
    else{
        $sql .= " order by required desc, fieldId";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub tb_defItemIndex_getList{

    my ($dbh) = @_;
    my @retList;
    my $sql = "select * from tb_index_map where fieldId = '852'";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;

}

sub tb_defIndex_update{

    my ($dbh, $selected, $fieldId, $subfield ) =  @_;
    return -1 if($fieldId eq '' && $subfield eq '');
    my $sql = "update tb_index_map ";
      $sql .= " set selected = ? where fieldId = ? && subfield = ? && required = '0' ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($selected, $fieldId, $subfield);
    $sth->finish;

}


sub tb_barcode_getMaxValue {

    my ($dbh, $rid) = @_;
    my $sql = "select MAX(convert(barcode,unsigned)) from tb_items where barcode not regexp '^\_'";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $retVal = $sth->fetchrow_array();
    $sth->finish;
    return $retVal?$retVal:0;

}

#return duplicate maxBarcode in form "DUP_barcode_00X"
sub tb_maxBarcodeDup {
    
    my ($dbh, $barcode) = @_;

    my $sql_bc = $barcode;
    $sql_bc =~ s/([*+?])/\\\\$1/g;
    $sql_bc .= '_';

    my ($maxDupBarcode) = $dbh->selectrow_array(<<_STH_);
select  max(barcode)
from    tb_items
where   barcode regexp '^DUP_$sql_bc'
_STH_

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

        $maxDupBarcode .= $dupCount;
    }
    else {
        $maxDupBarcode = 'DUP_'.$barcode.'_000';
    }
    return $maxDupBarcode;
}

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

    my ($maxDupBarcode) = $dbh->selectrow_array(<<_STH_);
select max(barcode) 
from tb_items 
where barcode regexp '^DUP_$sql_bc' && updating ='0'
_STH_
    if ($maxDupBarcode && $maxDupBarcode =~ s/([\d]{3})$//) {
        my $dupCount = $1;
        $dupCount =~ s/^0{1,2}//;
        $dupCount++;
        $dupCount = sprintf("%0.3d", $dupCount);

        $maxDupBarcode .= $dupCount;
    }
    else {
        $maxDupBarcode = 'DUP_'.$bc.'_000';
    }
    return $maxDupBarcode;
}


sub tb_subfieldCode {

    my @dataHeader = qw(
        245_a 
        245_b 
        020_a 
        100_a
        490_a
        490_v
        300_a
        300_w
        650_a
        250_a
        260_b
        260_c
        260_a
        010_a
        500_a
        856_u
        521_a
        900_a
        900_b
        900_c
        barcode 
        price
        itemtype
    );
     
    return @dataHeader;
}

#return temporay maxBarcode in form "TMP_Barcode_00X"
sub tb_maxBarcodeTmp {
    my ($dbh) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  max(barcode)
from    tb_items
where   barcode regexp '^TMP_[0-9]+\$'
_STH_
    $sth->execute;
    my ($barcode_tmp) = $sth->fetchrow_array;
    $sth->finish;
    if ($barcode_tmp) {
        $barcode_tmp =~ s/TMP\_([\d]*)/$1/;
        $barcode_tmp =~ s/^0*//g;
    }
    $barcode_tmp = 0 unless ($barcode_tmp);
    $barcode_tmp++;
    $barcode_tmp = 'TMP_' . sprintf("%0.12d", $barcode_tmp);
    return $barcode_tmp;
}



sub tb_record_idGen {
    
    my ($dbh) = @_;
#    my $checkSum = sha1_base64($sec .$min . $hour . $mday . $mon . $year . $wday . $yday . $isdst . $rand);
    my $checkSum = `date +\%Y\%m\%d\%H\%M\%S\%N`;
    chomp $checkSum;
    
    my $sql = "insert into opl_idGen set type = 'textbook', checkSum=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($checkSum);
    $sth->finish;

    $sql = "select id from opl_idGen where type = ? and checkSum=? ";
    $sth = $dbh->prepare($sql);
    $sth->execute('textbook',$checkSum);
    my ($id) = $sth->fetchrow_array();
    $sth->finish;
    return $id;
}


sub tb_record_add{
    my ($dbh, $rid, $fId, $fVal) = @_;
    return -1 if ($rid eq '');
    my($sql, $sth, $id);
    $sql = "insert into tb_records set rid=?, fId=?, fVal=?";    
    $sth = $dbh->prepare($sql);
    my $result = $sth->execute($rid,$fId,$fVal);
    $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    #if ($result > 0 && $id > 0)
             
    return $id;

}

sub tb_record_update{
 
    my ($dbh, $rid, $fId) = @_;
    return -1 if ($rid eq '');
    my($sql, $sth, $id);
    $sql = "update tb_records set fVal= now() where rid = ? && fId = ? limit 1";    
    $sth = $dbh->prepare($sql);
    my $result = $sth->execute($rid,$fId);
    $sth->finish;

}

sub tb_record_delete{

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

    my $sql = "delete from tb_records where rid = ? ";
    $sql .= " && updating = '1' " if ($flag);
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    $sth->finish;
    return $rid;

}

sub tb_record_findByRId{
    my ($dbh, $rid, $sortVal ) = @_;
    return -1 if ($rid eq '');
    my @recordInfo = ();
    my $sql = "SELECT r.*, m.* 
               FROM tb_records as r 
               right join tb_index_map as m on r.fId = concat(m.fieldId, '_', m.subfield) && r.rid = ?  && r.deleted <> '1'
               where m.showed = 1  
               order by displayOrder ";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    my $myHash;
    my $order = 0;
    my $rec_info;
    while( my $rec = $sth->fetchrow_hashref){
        if (!$myHash->{$rec->{'fId'}}){
            $order = 0;
        }
        else{
            $order++;
        }
        push @{$myHash->{$rec->{'fId'}}}, { 
            fVal        => $rec->{'fVal'},
            fOrder      => $order,
            url_link    => ($rec->{'fieldType'} eq '3')?1:0,
            number      => ($rec->{'fieldType'} eq '4')?1:0,
            checkbox    => ($rec->{'fieldType'} eq '5')?1:0,
        };

        $rec_info->{$rec->{'fId'}} = {
            rid         => $rid,
            fId         => $rec->{'fId'},
            fVal        => $rec->{'fVal'},
            nfVal       => ($rec->{'fVal'})? 1:0,
            fVals       => $myHash->{$rec->{'fId'}},
            nfVals      => scalar(@{$myHash->{$rec->{'fId'}}}) > 0 ? scalar(@{$myHash->{$rec->{'fId'}}}) :0,
            fName       => $rec->{'fieldName'},
            repeatable  => $rec->{'repeatable'},
            required    => $rec->{'required'},
            displayOrder=> $rec->{'displayOrder'},
            url_link    => ($rec->{'fieldType'} eq '3')?1:0,
            number      => ($rec->{'fieldType'} eq '4')?1:0,
            checkbox    => ($rec->{'fieldType'} eq '5')?1:0,
            gradeLevel  => ($rec->{'fId'} eq '900_a')?1:0,
            f520a       => ($rec->{'fId'} eq '520_a')?1:0,
            fv520a_short => substr($rec->{'fVal'},0 ,50)
        };
    }
    $sth->finish;
    my @uniqueRecInfo = ();
    my @sortRecInfo = ();
     
    foreach my $fid (sort keys %{$rec_info}) {
        push @uniqueRecInfo, $rec_info->{$fid};
    }

    if ($sortVal eq  '1' ){
        @sortRecInfo =  sort { $$a{fId} cmp $$b{fId} }  @uniqueRecInfo;
    }
    else{
        @sortRecInfo =  sort { $$a{displayOrder} <=> $$b{displayOrder} }  @uniqueRecInfo;
    }
    return ($rid, \@sortRecInfo); 
}

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 tb_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 tb_record_setFlagUpdating{
    my ($dbh, $rid) = @_;
    
    return -1 if ($rid eq '');
    my $sql = "update tb_records set updating = '1' where rid = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    $sth->finish;
    return $rid;
}

sub tb_record_items_deleteAll{

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

    tb_record_update($dbh, $rid, '005');
    my $sql = "update tb_records  set deleted = '1' where rid = ? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);

    $sql = "update tb_items set available = 0, deleted = '1', 
        barcode = CONCAT('___', barcode, '_', $rid ) , modDate = now() where rid = ? ";
    $sth = $dbh->prepare($sql);
    $sth->execute($rid);

    $sth->finish;
    return $rid;
}

sub tb_itemType_getList{

    my ($dbh) = @_;
    my @itemTypeList = ();
    my $sth = $dbh->prepare(
        "select it.* from opl_itemType as it inner join opl_itemCategory as ic on it.itemCategory = ic.id where it.itemCategory  = 2");
    $sth->execute();
    my $order = 0;
    while(my $f = $sth->fetchrow_hashref()){
        push @itemTypeList, {
            order   => $order++,
            id      => $f->{'id'},
            desc    => $f->{'description'},
        }   
    }
    $sth->finish;
    return \@itemTypeList;
}

sub tb_item_add{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'rid'} eq '');
    if (tb_isBarcodeExist($dbh,  $params->{'barcode'})){
        my $maxDupBarcode = tb_maxBarcodeDup($dbh, $params->{'barcode'});
        $params->{'barcode'} = $maxDupBarcode;
    }
    my ($sql, $sth, $id);
        $sql = <<_SQL_;
            insert into tb_items set 
                rid=?, 
                barcode=?, 
                available=1,
                typeId=?, 
                locationCode=?, 
                price=?,
                classNumber=?,
                acquisitionDate=?,
                PONumber = ?,
                distributor = ?,
                regionCode = ?,
                districtCode = ?,
                buildingCode = ?,
                importDate=?,
                budgetCategory=?,
                vendorCatalogNumber=?

_SQL_
    $sth = $dbh->prepare($sql);
    $sth->execute(  $params->{'rid'}, 
                    $params->{'barcode'}, 
                    $params->{'typeId'},
                    $params->{'lCode'},
                    $params->{'price'},
                    $params->{'classno'},
                    $params->{'acqDate'},
                    $params->{'PONo'},
                    $params->{'distributor'},
                    $params->{'regionCode'},
                    $params->{'districtCode'},
                    $params->{'buildingCode'}, 
                    $params->{'importDate'},
                    $params->{'budgetCategory'} ,
                    $params->{'vendorCatalogNo'} );
    $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;
}
sub tb_item_update{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'rid'} eq '');
        my ($sql, $sth, $id);
        $sql = <<_SQL_;
            replace into tb_items set 
                rid=?, 
                barcode=?, 
                available=1,
                typeId=?, 
                locationCode=?, 
                price=?,
                classNumber=?,
                acquisitionDate=?,
                PONumber = ?,
                distributor = ?,
                regionCode = ?,
                districtCode = ?,
                buildingCode = ?,
                importDate=?,
                budgetCategory=?,
                vendorCatalogNumber=?,
                updating = 0

_SQL_
    $sth = $dbh->prepare($sql);
    $sth->execute(  $params->{'rid'}, 
                    $params->{'barcode'}, 
                    $params->{'typeId'},
                    $params->{'lCode'},
                    $params->{'price'},
                    $params->{'classno'},
                    $params->{'acqDate'},
                    $params->{'PONo'},
                    $params->{'distributor'},
                    $params->{'regionCode'},
                    $params->{'districtCode'},
                    $params->{'buildingCode'}, 
                    $params->{'importDate'},
                    $params->{'budgetCategory'},
                    $params->{'vendorCatalogNo'});
    $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;
}

sub tb_item_findByRId{

    my ($dbh,$rid, $order) = @_;
    my @itemList;
    return undef if ($rid eq '');
    my $sql = " SELECT * FROM tb_items where rid = ? && deleted <> '1' && barcode not regexp '^\_' order by length(barcode) , barcode ";
    if ($order) {
        $sql .= $order ;
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    my $i =0;
    while (my $it = $sth->fetchrow_hashref()){
        $it->{'i'} = $i++;
        $it->{'order'} = $i;
        $it->{'vendorCatalogNo'} = ($it->{'vendorCatalogNumber'})? $it->{'vendorCatalogNumber'}:"";
        $it->{'poNo'}       = $it->{'PONumber'};
        $it->{'acqDate'}     = $it->{'acquisitionDate'};
        $it->{'classNo'}     = $it->{'classNumber'};
        push @itemList, $it;
    }
    $sth->finish;
    return \@itemList;
}

sub tb_item_findByBarcode{
    
    my ($dbh, $barcode) = @_;
    return undef if ($barcode eq '');

    my $itemInfo;
    my $sql = " SELECT * from tb_items where barcode = ? ";
    $itemInfo   = $dbh->selectrow_hashref($sql, undef, $barcode);
    if (!$itemInfo){
        return;
    }
    my ($recId, $recInfo) = tb_record_findByRId($dbh, $itemInfo->{'rid'});
    my ($title, $subTitle, $author, $isbn, $publisher, $pubDate);
    foreach my $f(@$recInfo){
        if ($f->{'fId'} eq '245_a'){
            $title = $f->{'fVal'};
        }
        elsif($f->{'fId'} eq '245_b'){
            $subTitle = $f->{'fVal'};
        }
        elsif ($f->{'fId'} eq '100_a'){
            foreach my $a(@{$f->{'fVals'}}){
                $author .= "-" .  $a->{'fVal'} ;
            }
        }
        elsif($f->{'fId'} eq '020_a'){
            $isbn = $f->{'fVal'};
        }
        elsif($f->{'fId'} eq '260_b'){
            $publisher = $f->{'fVal'};
        }
        elsif($f->{'fId'} eq '260_c'){
            $pubDate = $f->{'fVal'};
        }
    }
    $author =~ s/^-//g;
    $itemInfo->{'title'}    = $title;
    $itemInfo->{'subTitle'} = $subTitle;
    $itemInfo->{'author'}   = $author;
    $itemInfo->{'isbn'}     = $isbn;
    $itemInfo->{'publisher'}= $publisher;
    $itemInfo->{'pubDate'}  = $pubDate;

    return $itemInfo;
}

sub tb_item_setFlagUpdating{
    
    my ($dbh, $rid) = @_;
    return -1 if ($rid eq '');
    my $sql = "update tb_items set updating = '1', barcode = CONCAT('TMP_', barcode) where rid = ? && deleted <> '1'";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    $sth->finish;
    return $rid;
}


sub tb_item_delete{

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

    my $sql = "delete from tb_items where rid = ? ";
    $sql .= " && updating = '1' " if ($flag);
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    $sth->finish;
    return $rid;

}
sub tb_item_deleteByBarcode{

    my ($dbh, $rid, $barcode,$flag ) = @_;
    return -1 if ($rid eq '');

    my $sql = "delete from tb_items where rid = $rid && barcode = '$barcode'";
    $sql .= " && updating = '1' " if ($flag);
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->finish;
    return $rid;

}


sub tb_barcode_getList{

    my ($dbh ,$rid ) = @_;
    my @retList;
    my $i = 0;
    my $sql = "select barcode from tb_items where barcode not REGEXP '^___' && available <> '0' ";
    if (! $rid eq '') {
        $sql .= ' && rid <> ' . $rid ;
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $bc = $sth->fetchrow_hashref){
        $bc->{'i'} = $i++;
        push @retList, $bc;
    }
    $sth->finish;
    return \@retList;
}

sub tb_isbn_getList{

    my ($dbh ,$rid ) = @_;
    my @retList;
    my $i = 0;
    my $sql = "select distinct rid, trim(fVal) as isbn, deleted from tb_records where fId = '020_a' && deleted = '0'";
    if (! $rid eq '') {
        $sql .= ' && rid <> ' . $rid ;
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec  = $sth->fetchrow_hashref){
        $rec->{'i'} = $i++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub tb_title_getList{

    my ($dbh ,$rid ) = @_;
    my @retList;
    my $i = 0;
    my $sql = "select distinct rid, trim(fVal) as title, deleted from tb_records where fId = '245_a' && deleted = '0'";
    if (! $rid eq '') {
        $sql .= ' && rid <> ' . $rid ;
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec  = $sth->fetchrow_hashref){
        $rec->{'i'} = $i++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}


sub tb_isBarcodeExist{

    my($dbh,$bc)=@_;
    #return 1 if ($bc eq " ");
    my $sth = $dbh->prepare(<<_STH_);
select barcode from tb_items where barcode=?
_STH_
    $sth->execute($bc);
    my ($ret) = $sth->fetchrow_array;
    $sth->finish;
    if(!$ret){
        return 0;
    }
    return $ret; 
}


sub mapData2SubfieldCode{

    my ($rec, $hMap) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1 });
    $csv->parse($rec);
    my @field = $csv->fields();
    foreach my $f(@field) {
        $f =~ s/^\s+//;
        $f =~ s/\s+$//;
    }
    my $record;
    my @sfCode =  tb_subfieldCode();
    for ( my $i =0; $i < scalar(@{$hMap}); $i++){
        if ($hMap->[$i] >= 0 ){
            $record->{$sfCode[$i]} = $field[$hMap->[$i]];
        }
        else{
            $record->{$sfCode[$i]} = "";
        }
    }
    return $record;
}

sub tb_record_index_map_010i_900e{

    my ($dbh) = @_;
    
    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 checkDuplicateRecord {
    my ($dbh,$iid, $record,$csvData) = @_;
    my $sql = "SELECT count(distinct rid) from tb_records_tmp where fVal = ? && fId = ? && deleted <> 1"; 
    my $sth = $dbh->prepare($sql);
    $sth->execute($record->{'020_a'}, '020_a');
    my ($count) = $sth->fetchrow_array;
    $csvData =~ s/[\n]*$/\n/; 
    if ($count && $count > 0){
        $sth = $dbh->prepare(<<_STH_);
update tb_csvImport set dataDup = CONCAT(COALESCE(dataDup,''),?)
where   iid = $iid
_STH_
    $sth->execute($csvData);
    }
    else{
        $sth->finish;
        return 0;
    }
    $sth->finish;
    return 1;
}

sub tb_createNextBiggerBc {

     my ($dbh,$bc) = @_;
     my $prefix = "";
     my $bigBc = $bc;
        $bigBc =~ m/(^.*[\D]|^)([\d]+)$/;
        $prefix= $1;           
     my $tmp = $2;
     my $len = length($tmp);
     
    $tmp =~ s/^0+//;
    $tmp++;
    my $lenTmp = length($tmp);

    $bc = $prefix . sprintf("%s","0" x ($len - $lenTmp)) . $tmp;
    
=item
     if($tmp=~ m/(.*\D)(\d+)/){
        $tmp = $2;
        $prefix .=$1;
     }

     if ($len > 0){
         if ($len == 1){
            $bc = $prefix . sprintf("%01d",($tmp +1));}
         elsif ($len == 2){
            $bc = $prefix . sprintf("%02d",($tmp +1));}
         elsif ($len == 3){
            $bc = $prefix . sprintf("%03d",($tmp +1));}
         elsif ($len == 4){
            $bc = $prefix . sprintf("%04d",($tmp +1));}
        else {
             $bc = $prefix . sprintf("%05d",($tmp +1));}
     }
     else {
        $bc = $prefix . ($tmp +1);
     }
=cut
     return $bc;



}
 
1;