package dblib;

use Mysql;
use CGI qw(:standard);
use CGI::Carp qw/fatalsToBrowser/;


$db;
$mode;
$recordset;
$currentrow = 0;
%record;
$form;
%modecolors;
$currentquery;

sub connect
{
  my @args	= @_;
  shift @args;
  my $host		= $args[0];
  my $database	= $args[1];
  my $user		= $args[2];
  my $password	= $args[3];

  $db = Mysql->connect($host, $database, $user, $password) or die "$!\n ".Mysql->errmsg;
}

sub start
{
  my @args = @_;
  shift @args;
  $form = param('form') or $form = $args[0];
  $currentquery = param('query');
  if( !($currentrow = param('row'))){ $currentrow = 0; }

  my $mc = $db->query("select `mode`, `color` from `APP_MODES`;") or die $db->errmsg;
  while(my @row = $mc->fetchrow)
  {
    $modecolors{$row[0]} = $row[1];
  }

  my @params = param();

  if(param('action') eq "ADD")
  {
    $mode = "NEW";
  }

  elsif(param('action') eq "EDIT")
  {
    $mode = "EDIT";
    my $query;
    ($query = $currentquery) ||
    ($query = "select * from '".
	$db->query("select `source` from `APP_FORMS` where `FORM` = '$form';")->fetchrow."';"
	 or die "$!\n $db->errmsg");
    &query(0, $query);
  }

  elsif(param('action') eq "REPORT")
  {
    $mode = "REPORT";
#    my $query;
#    grep
#    {
#      if($_ =~ /QUERY_${form}_(.+)/ && param($_) ne "")
#      {
#        $query .= " AND $1 like '".param("QUERY_".$form."_".$1)."'";
#      }
#    } @params;
#    $query =~ s/^ AND/ WHERE/;
#    $query = "select * from ".
#	$db->query("select source from APP_FORMS where FORM = '$form';")->fetchrow.$query.";" or die "$!\n $db->errmsg";
    if(! $currentquery)
    {
      $currentquery = "select * from '".
	$db->query("select `source' from `APP_FORMS` where `FORM` = '$form';")->fetchrow."';"
	 or die "$!\n $db->errmsg";
    }
    &query(0, $currentquery);
  }

  elsif(param('action') eq "EXECUTE_QUERY")
  {
    $mode = "REPORT";
    grep
    {
      if($_ =~ /QUERY_${form}_(.+)/ && param($_) ne "")
      {
	my $temp1 = $1;
	my $temp = param("QUERY_".$form."_".$temp1);
	$temp =~ s/'/\\'/g;
	if($temp eq 'IS NULL')
	{
	  $currentquery .= " AND (`$temp1` IS NULL OR `$temp1` = '' OR `$temp1` like 0)";
	}
	elsif($temp eq 'IS NOT NULL')
	{
	  $currentquery .= " AND (`$temp1` IS NOT NULL AND `$temp1` != '' AND `$temp1` NOT like 0)";
	}
	else
	{
          $currentquery .= " AND `$temp1` like '$temp'";
	}
      }
    } @params;
    $currentquery =~ s/^ AND/ WHERE/;
    $currentquery = "select * from ".
	$db->query("select `source` from `APP_FORMS` where `FORM` = '$form';")->fetchrow.$currentquery.";" or die "$!\n $db->errmsg";
    &query(0, $currentquery);
  }

  elsif(param('action') eq "SAVENEW")
  {
    $mode = "NEW";
    my $table = $db->query("select `source` from `APP_FORMS` where `FORM` = '$form';")->fetchrow or die "$!\n $db->errmsg";
    my $fields, $values;
    grep
    {
      if($_ =~ /${form}_(.+)/)
      {
	my $field = $1;
	my $value = param($form."_".$field);
        $field =~ s/'/\\'/g;
        $value =~ s/'/\\'/g;
        $fields .= ", `$field`";
        $values .= ", '$value'";
      }
    } @params;
    $fields =~ s/^, //;
    $values =~ s/^, //;
    my $query = "insert into `$table` ($fields) values ($values);";
    $db->query($query)
      or die "\"$query\" $!\n ".$db->errmsg;
    my %rec = $db->query("select `SOURCE`, `PRIMARY_FIELD` from `APP_FORMS` where `FORM` = '$form';")->fetchhash
      or die "$!\n ".$db->errmsg;
#    $query = "select * from ".$rec{'SOURCE'}.";" or die "$!\n $db->errmsg";
#    &query(0, $query);
#    &query(0, $currentquery);
#    my @records = $recordset->fetchcol($rec{'PRIMARY_FIELD'});
#    for($currentrow = 0;$records[$currentrow] ne param($form."_".$rec{"PRIMARY_FIELD"}) && $currentrow < @records; $currentrow++){}
  }

  elsif(param('action') eq "DELETE")
  {
    $mode = "EDIT";
    my %rec = $db->query("select `SOURCE`, `PRIMARY_FIELD` from `APP_FORMS` where `FORM` = '$form';")->fetchhash
      or die "$!\n $db->errmsg";
    my $key = param('key');
    my $tmp = chr(160);
    $key =~ s/$tmp/ /g;
    my $query = "delete from `$rec{'SOURCE'}` where `$rec{'PRIMARY_FIELD'}` = '$key';";
    $db->query($query)
      or die "\"$query\" $!\n ".$db->errmsg;
#    $query = "select * from ".$rec{'SOURCE'}.";" or die "$!\n $db->errmsg";
#    &query(0, $query);
    &query(0, $currentquery);
  }

  elsif(param('action') eq "SAVEEDIT")
  {
    $mode = "EDIT";
    my %rec = $db->query("select `SOURCE`, `PRIMARY_FIELD` from `APP_FORMS` where `FORM` = '$form';")->fetchhash
      or die "$!\n $db->errmsg";
    my $values;
    grep
    {
      if($_ =~ /${form}_(.+)/)
      {
        my $value = $1;
        my $temp = param($form."_".$value);
	$temp =~ s/\\/\\\\/g;
	$temp =~ s/'/\\'/g;
        $values .= ", `$value`='$temp'";
      }
    } @params;
    $values =~ s/^, //;
    my $key = param('key');
    my $tmp = chr(160);
    $key =~ s/$tmp/ /g;
    $key =~ s/'/\\'/g;
    my $query = "update `$rec{'SOURCE'}` set $values where `$rec{'PRIMARY_FIELD'}`='$key';";
    $db->query($query)
      or die "\"$query\" $!\n ".$db->errmsg;
    &query(0, $currentquery);
    $key =~ s/\\'/'/g;
    $currentrow = 0;
    my @record;
    while(($currentrow < $recordset->numrows) && ($record[$rec{'PRIMARY_FIELD'}] ne $key))
    {
      @record = $recordset->fetchrow;
      $currentrow++;
    }
    $currentrow--;
    $recordset->dataseek(0) or die "\"$query\"\n$!\n ".$db->errmsg;
  }

  else
  {
    $mode = "QUERY";
  }

  my $start = showform(0, $form);
  return $start;
}

sub query
{
  my @args = @_;
  shift @args;
  my $query = $args[0];

  $recordset = $db->query($query) or die "\"$query\"\n$!\n ".$db->errmsg;
}

sub showform
{ # showform("formname")

  my @args = @_;
  shift @args;
  my $form = $args[0];

  my $showform;

  if($mode eq "EDIT")
  {
    $recordset->dataseek($currentrow);
    %record = $recordset->fetchhash;
  }

  my %form = $db->query("SELECT * FROM `APP_FORMS` WHERE `FORM` = \"$form\";")->fetchhash or die "$!\n $db->errmsg";

  my $formelements;
  if($mode eq "REPORT")
  {
    $formelements = $db->query("SELECT * FROM `APP_REPORT_ELEMENTS` WHERE `FORM` = '$form{'FORM'}';") or die "$!\n ".$db->errmsg;
  }
  else
  {
    $formelements = $db->query("SELECT * FROM `APP_FORM_ELEMENTS` WHERE `FORM` = '$form{'FORM'}';") or die "$!\n ".$db->errmsg;
  }

  #build form header
  $showform .= "<form name=\"$form{'FORM'}\">\n<table border='1' bgcolor='$modecolors{$mode}'><tr><td>$mode $form{'FORM'}</td>\n";
  if($mode eq "EDIT")
  {
    $showform .= &showformelement("", "RECORD_SELECT", $form{'PRIMARY_FIELD'}, "", "LEFT", 15, 1, "Record: ", "", "");
  }
  $showform .= "</tr>\n<tr><td colspan='3'>".&buttonbar(0, $form)."</td></tr>".
	"<tr><td colspan='3'>\n<table width='100%'>\n";

  if($mode eq "REPORT")
  { # iterate through results, drawing labels

    $showform .= "<tr bgcolor='#dddddd'>\n";
    for(my $x=0;$x < $formelements->numrows;$x++)
    {
      my %formelement = $formelements->fetchhash;
      $showform .= &showformelement("", "LABEL", "", "", $formelement{'ALIGN'}, 0, 0, $formelement{'LABEL'}, "", "");
    }
    $formelements->dataseek(0) or die $!." ".$db->errmsg;
    $showform .= "</tr>\n";

    for(my $y;$y < $recordset->numrows;$y++)
    {
#      $currentrow = $y;
      %record = $recordset->fetchhash or die $!." ".$db->errmsg;

      $showform .= "<tr>\n";
      for(my $x=0;$x < $formelements->numrows;$x++)
      {
        my %formelement = $formelements->fetchhash;
        $showform .= &showformelement("", "LABEL", $formelement{'FIELD'}, $formelement{'SOURCE'}, $formelement{'ALIGN'}, 0, 0, "", $formelement{'FORM'}, "THIS", $y);
      }
      $formelements->dataseek(0) or die $!." ".$db->errmsg;
      $showform .= "</tr>\n";
    }
    $recordset->dataseek(0) or die $!." ".$db->errmsg;
  }
  else
  { #iterate through width/height, drawing form elements
    for(my $y=0;$y < $form{'HEIGHT'};$y++)
    {
      $showform .= "<tr>\n";
      for(my $x=0;$x < $form{'WIDTH'};$x++)
      {
	$formelements->dataseek(0) or die $!." ".$db->errmsg;
        for(my $r=0;$r < $formelements->numrows;$r++)
	{
	  my %formelement = $formelements->fetchhash or die $!." ".$db->errmsg;
          if((($y * $form{'WIDTH'}) + $x) == $formelement{"POSITION"})
          {
            $showform .= &showformelement("", $formelement{'TYPE'}, $formelement{'FIELD'}, $formelement{'SOURCE'}, $formelement{'ALIGN'}, $formelement{'WIDTH'}, $formelement{'HEIGHT'}, $formelement{'LABEL'}, $formelement{'LINKED_FORM'}, $formelement{'TARGET'});
          }
        }
      }
      $showform .= "</tr>\n";
    }
  }
  #build form footer
  $showform .= "</table></td><tr><td colspan='2'>$form{'DESCRIPTION'}</td></tr></table>\n</form>";

  return $showform;
}

sub showformelement
{ #showformelements(type, field, source, align, width, height, label, linked_form, target, record);
  my @args = @_;
  shift @args;
  my $type = $args[0];
  my $field = $args[1];
  my $source = $args[2];
  my $align = $args[3];
  my $width = $args[4];
  my $height = $args[5];
  my $label = $args[6];
  my $linked_form = $args[7];
  my $target = $args[8];
  my $record = $args[9];
  my $value;

  if($mode eq "EDIT" || $mode eq "REPORT")
  {
    $value = $record{$field};
  }
  else { $value = ""; }

  my $query = $db->query("SELECT * FROM `APP_FORM_ELEMENT_TYPES` WHERE `TYPE` = '$type';")
	or die "$!\n ".$db->errmsg;
  my %element_type = $query->fetchhash;
  my $showformelement;
  my $link = "";
  my $link_end = "";
  if($linked_form)
  {
    $link = "<a href='javascript:EDIT(\"$linked_form\",".($record+0).",$target);'>";
    $link_end = "</a>";
  }
  my $notlabel, $nottemp;
  my $temp = eval $element_type{'CODE'};# or die $!;
  if(!$label && !$temp) { $showformelement .= "<td colspan='2'></td>"; }
  if(!$label) { $notlabel = "colspan='2'"; }
  if(!$temp) { $nottemp = "colspan='2'"; }
  if($label) { $showformelement = "<td valign='top' $nottemp>".$link.$label.$link_end."</td>"; }
  if($temp) { $showformelement .= "<td valign='top' $notlabel>".$link.$temp.$link_end."</td>"; }
  return $showformelement;
}

sub buttonbar
{
  my @args = @_;
  shift @args;
  my $form = $args[0];

  my $query = $db->query("select * from `APP_BUTTONS` ORDER BY `BUTTON_ORDER`;") or die "$!\n $db->errmsg";
  my %row;

  my $url;
#  foreach my $param (param())
#  {
#    if($param =~ /^QUERY_/) { $url .= "&$param=".param($param); }
#  }
#  $url = "&".$url;
#  $url =~ s/^&+/?/;
  $url = "?query=".&text2href($currentquery);
  $url =~ s/\\/\\\\/g;

  my $buttonbar = "<script language='javascript'>\n".
	"  function encode(text)\n".
	"  {\n".
	"    encoded = escape(text);\n".
	"    encoded = encoded.replace(/\\//g,'%2F');\n".
	"    encoded = encoded.replace(/\\?/g,'%3F');\n".
	"    encoded = encoded.replace(/\\=/g,'%3D');\n".
	"    encoded = encoded.replace(/\\+/g,'%2B');\n".
	"    encoded = encoded.replace(/&/g,'%26');\n".
	"    encoded = encoded.replace(/@/g,'%40');\n".
	"    return encoded;\n".
	"  }\n".
	"  NEW = '';\n".
	"  THIS = '_self';\n".
	"  function EDIT(form, row, target)\n".
	"  {\n".
	"    if(form == '$form')\n".
	"    { url = '$url&'; }\n".
	"    else\n".
	"    { url = '?'; }\n".
	"    url = url + 'form=' + form + '&action=EDIT&row=' + row;\n".
#	"    window.location = url;\n".
	"    window.open(url, target);\n".
	"  }\n".
	"  function EXECUTE_QUERY(form)\n".
	"  {\n".
	"    url = '?action=EXECUTE_QUERY&form=' + encode(form);\n".
	"    for(x=0; x < document.forms[form].elements.length; x++)\n".
	"    {\n".
	"      formelement = document.forms[form].elements[x];\n".
	"      url = url + '&QUERY_' + encode(formelement.name) + '=' + encode(formelement.value);\n".
	"    }\n".
	"    window.location = url;\n".
	"  }\n".
	"  function QUERY(form)\n".
	"  {\n".
	"    url = '?action=QUERY&form=' + encode(form);\n".
	"    window.location = url;\n".
	"  }\n".
	"  function ADD(form, extras)\n".
	"  {\n".
	"    if(form == '$form')\n".
	"    { url = '$url&'; }\n".
	"    else\n".
	"    { url = '?'; }\n".
	"    url = url + 'action=ADD&form=' + encode(form) + '&' + extras;\n".
	"    window.location = url;\n".
	"  }\n".
	"  function DELETE(form)\n".
	"  {\n".
        "    key = document.forms[form].record_select.options[document.forms[form].record_select.value].text;\n".
	"    url = '$url&action=DELETE&form=' + encode(form) + '&key=' + encode(key);\n".
        "    if(confirm('Are you sure you want to delete \"' + key + '\"?'))\n".
        "    {\n".
	"      window.location = url;\n".
        "    }\n".
	"  }\n".
	"  function SAVE(form)\n".
	"  {\n".
        "    mode = '$mode';\n".
	"    key = 0;\n".
        "    if(mode != 'NEW')\n".
	"    {\n".
        "      key = document.forms[form].record_select.options[document.forms[form].record_select.selectedIndex].text;\n".
	"    }\n".
	"    url = '$url&action=SAVE' + mode + '&form=' + encode(form) + '&key=' + encode(key);\n".
	"    for(x=0; x < document.forms[form].elements.length; x++)\n".
	"    {\n".
	"      formelement = document.forms[form].elements[x];\n".
	"      url = url + '&' + encode(formelement.name) + '=' + encode(formelement.value);\n".
	"    }\n".
        "    if(mode == 'NEW' || confirm('Are you sure you want to save changes to \"' + key + '\"?'))\n".
        "    {\n".
	"      window.location = url;\n".
        "    }\n".
	"  }\n".
	"  function REPORT(form)\n".
	"  {\n".
	"    url = '$url&form=' + form + '&action=REPORT';\n".
	"    window.location = url;\n".
	"  }\n".
	"</script>\n";

  $buttonbar .= "<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\"><tr>\n";

  while(%row = $query->fetchhash)
  {
    if($row{'MODE'} =~ /$mode/)
    {
      $buttonbar .= "<td bgcolor='#".$modecolors{$row{'BUTTON'}}.
	"'><a href=\"javascript:$row{'BUTTON'}('$form');\">$row{'DISPLAY'}</a></td>\n";
    }
  }

  $buttonbar .= "</tr></table>\n";
  return $buttonbar;
}

sub text2href {
  my $percent = sprintf "%%%x", ord "%";
  my $line = join "", @_;
  $line =~ s/%/$percent/g;

  while($line =~ /([^a-zA-Z0-9%\\\]])/){
    my $char = sprintf "%%%x", ord $1;
    $line =~ s/[$1]/$char/g;
  }
return $line;
}

sub getTimeString
{ # Takes a stamp and returns a string
  if($_[0] == 0){ return "Never"; }
  my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($_[0]);
  return ($year + 1900)."-".sprintf("%.2d",$mon)."-".sprintf("%.2d",$mday)." ".sprintf("%.2d",$hour).":".sprintf("%.2d",$min).":".sprintf("%.2d",$sec);
}

1;
