package DB_Manage;
use strict;
# Module export stuff
BEGIN {
       use Exporter ();
       use vars qw(@ISA @EXPORT);
       @ISA=qw(Exporter);
       @EXPORT=qw(
            db_connect
            db_disconnect
            db_update
            db_insert
            db_delete
            );
}

sub db_connect{
    my ($this,$database,$dbhost,$dbuser,$dbpass) = @_;    
    use DBI;
    my $dbh=DBI->connect("DBI:mysql:$database:$dbhost",$dbuser,$dbpass) or die $DBI::err; 
    return $dbh;
}
	

sub db_update {
    my ($this,$dbh,$cgi,$tablename,$tablefields,$condition) = @_;
    my @fparts = ();
    my ($field,$val); 
    my (@fields);
    #my $dbh=$this->db_connect;
    @fields=split(/\|/,$tablefields);
    foreach $field(@fields){
    	 next if($field eq "id");   	 
         $val=$cgi->param("$field");
         push @fparts, "$field=" . $dbh->quote($val);
    }  
    my $vals = join ", ", @fparts;
    my $query = "UPDATE $tablename SET $vals where $condition";
    my $qdo = $dbh->do($query);  
    return $query; 
}

sub db_delete {
    my ($this,$dbh,$tablename,$condition) = @_;
    #my $dbh=$this->db_connect;
    my $query = "delete from $tablename where $condition";
    my $qdo = $dbh->do($query);
    return $query;
}

sub db_insert{
    my ($this,$dbh,$cgi,$tablename,$tablefields) = @_;
    my @fparts = ();
    my @kparts = ();
    my @fields = ();
    my ($field,$date,$val);
    #my $dbh=$this->db_connect;
    @fields=split(/\|/,$tablefields);
    foreach $field(@fields){
      if($field eq "date") {
    		$date=$date;
      }
      else{
    	       $val=$cgi->param("$field");    	       
      }
        push @fparts, $field;
        push @kparts, $dbh->quote($val);     
    }  
    my $fvals = join ", ", @fparts;
    my $kvals = join ", ", @kparts;
    my $query = "insert into $tablename($fvals) values ($kvals)";
    my $qdo = $dbh->do($query);  
    my $new_id=$dbh->{'mysql_insertid'};     
    return $new_id;
}


sub list_fields{
    my($this,$dbh,$tablename,$condition,$selected_fields,$page,$page_record)=@_;
    #my $dbh=$this->db_connect;
    my $qh_field;
    if((!$page) && $page<1) {
        $page=1;
    }
    if($condition eq "") {
        $condition=1;
    }
    my $offset=($page-1)*$page_record;       
    my @selected_fields=split(/\|/,$selected_fields);   
    my $total_qh_fields=@selected_fields;
    my $qh_fields=join(",",@selected_fields);
    my $qh="select count(id) as nums from $tablename where $condition";
    my $sth=$dbh->prepare($qh);
    $sth->execute();
    my $myrow2=$sth->fetchrow_hashref();
    my $total=$myrow2->{nums};
    $qh="select $qh_fields from $tablename where $condition limit $offset,$page_record";
    $sth=$dbh->prepare($qh);
    $sth->execute();
    my @row=();
    my $ref_row=[];
    while(my $myrow=$sth->fetchrow_hashref()){         
         @row=();
         foreach $qh_field(@selected_fields){
              push(@row,$myrow->{$qh_field});              
         }
         push(@$ref_row,[@row]);
   }
   return ($ref_row,$total,$offset);
}    

sub list_fields_hash{
    my($this,$dbh,$tablename,$condition,$selected_fields,$page,$page_record)=@_;
    #my $dbh=$this->db_connect;
    my $qh_field;
    if((!$page) && $page<1) {
        $page=1;
    }
    if($condition eq "") {
        $condition=1;
    }
    my $offset=($page-1)*$page_record;       
    my @selected_fields=split(/\|/,$selected_fields);   
    my $total_qh_fields=@selected_fields;
    my $qh_fields=join(",",@selected_fields);
    my $qh="select count(id) as nums from $tablename where $condition";
    my $sth=$dbh->prepare($qh);
    $sth->execute();
    my $myrow2=$sth->fetchrow_hashref();
    my $total=$myrow2->{nums};
    $qh="select $qh_fields from $tablename where $condition limit $offset,$page_record";
    $sth=$dbh->prepare($qh);
    $sth->execute();
    my $ref_row=[];
    while(my $myrow=$sth->fetchrow_hashref()){         
             push(@$ref_row,$myrow);                       
   }
   return ($ref_row,$total,$offset);
}    

sub locate_start_rec_and_end_rec {
    my ($this,$page,$page_record,$total)=@_;
    my ($start_rec,$end_rec);
    if (($page eq "")||($page eq 1)){
        $start_rec=1;
        $end_rec=$page_record*1;
        if ($end_rec >$total){
            $end_rec=$total;
        }
        $page=1;
    }
    else {
        $start_rec=$page_record*($page-1)+1;
        $end_rec=$page_record*$page;
        if ($end_rec >$total){
            $end_rec=$total;
        }
    }
    $start_rec=0 if($total eq "0");
    return($start_rec,$end_rec);
}


sub list_pages {
    my ($this,$total,$page_record,$page,$page_href,$var,$pages)=@_;
    my $pageinfo;
    if(($page eq "") || $page<1) {
        $page=1;
    }
    if(($pages eq "") || $pages<1) {
        $pages=20;
    }
    my $page_href=$ENV{SCRIPT_NAME} if($page_href eq "");
    my $total_page=int($total/$page_record);         
    my $temp=$total_page*$page_record;
    if($temp ne $total){
         $total_page++;
    }
    my $rec_point=$page*$page_record;
    my $present_page=int($rec_point/$page_record)+1;
    if($rec_point%$page_record==0){
         $present_page=int($rec_point/$page_record);
    }
    my $present_page_tmp=$present_page-1;
    my $page_start_tmp=int($present_page_tmp/$pages); 
    my $page_start=$pages*$page_start_tmp;
    my $page_end_tmp=int($present_page_tmp/$pages)+1;
    my $page_end=$pages*$page_end_tmp;
    if ($page_start eq 0){
         $page_start=1;
    }
    if ($page_end > $total_page){
         $page_end=$total_page;
    } 
    my $pflag;
    $page_end=$page_end+1;
    for(my $i=$page_start;$i<=$page_end;$i++){
        if ($i <= $total_page) { 
             $pflag=1;   
             if (($i eq $page_end)&&($i ne ($total_page+1))){
                  $pageinfo .= " <a href='$page_href?p=$i&$var'> >> </a> " ;      
                  $pflag=0;
             }
             if (($i ne 1)&&($i eq $page_start)){
                  $pageinfo .= " <a href='$page_href?p=$i&$var'> << </a> " ;      
                  $pflag=0;
             }
             if ($pflag){
                  if ($i eq $present_page){
                      $pageinfo .= " <b>$i</b>  ";
                  }
                  else {
                      $pageinfo .= " <a href='$page_href?p=$i&$var'>$i</a> " ;      
                  }
             }
             $pflag=1;
                          
        }
        else {
             last;
        }
    }
    return ($pageinfo,$total_page);
}


sub make_hash_category {
	my ($this,$dbh,$table,$cat,$dir)=@_;
	#my $dbh=$this->db_connect;
	my $sql="select * from $table"."_category";
        my $sth=$dbh->prepare($sql);
        $sth->execute();         
        my %category;
        my %category_dir;
        $cat="cat" if($cat eq "");
        $dir="dir" if($dir eq "");
        while(my $myrow=$sth->fetchrow_hashref()){
              $category_dir{$myrow->{$cat}}=$myrow->{$dir};
              $category{$myrow->{$dir}}=$myrow->{$cat};
        }	
return (\%category,\%category_dir) ;
}



sub selectindex {
	my($this,$select_hash,$selected)=@_;
	my %cat=%$select_hash;
        chomp($selected);
	$selected=~ s/\+$//;
	my @selected_tmp=split(/\+/,$selected);
        my $i=0;
        my ($info,$ttt,$result);
	foreach $info (sort(keys (%cat))) {
	       my $flag=0;
	       foreach $ttt (@selected_tmp){
	               if($info eq $ttt){ 
	                  $flag=1;
	               }
	       }
               if($flag){               	    
	            $result.="<option value='$info' selected>$cat{$info}</option>\n";
	       }
	       else{	          
	           $result.="<option value='$info'>$cat{$info}</option>\n";
	       }
               $i++;
       }
       return $result;
}

sub db_disconnect{
    my($this,$dbh)=@_;
    $dbh->disconnect;
}

sub new {
    my $this={};
       shift;
       bless $this;
       return $this;
}


1;