# Package wide code, and package initialization stuff.
######################################################
#!/usr/local/bin/perl
 
package ProcSupForm;
use bpoStub;
use CGI;
use Pictures;
use HTMLTemplate;
use Release;
 
my $C = new CGI;
my $D = new bpoStub;
my $P = new Pictures;
my $T ;
my $R = new Release;
open LOG, ">>/home_dir/flachr/cgi.log";
 
sub new {
  my ($class) = @_;
  my $self = {};
  bless $self, $class;
  $self->getparam;  # This just grabs CGI params and stuffs them into self
  $self->{ScriptOnTheFly} = "";
  print LOG "FormID = $self->{FormID}\n";
  return bless $self, $class;
}

# Function used to process Form and populate database.
######################################################
# Note:  I have tried to keep lines short, but my res is 16X12, so I'm not sure they're short enough for some.
sub ProcSupForm {
  my ($self) = @_;
  my $FormID = $self->{FormID};
  my $OrderID = $self->{OrderID};
  my $sql;
  my @tables;
  my @rowsmap;
  my @rowsthere;
  my @rowscomp;
  my @rowstype;
  my $DBTable;
  my %Type;
  my $count1;
  my $count2;
  my $countt;
  my $countc;
  my %VarList;
  my $DBCompNum;
  my @columns;
  my @values;
  my $valuestext;
  my $columnstext;
  
  $sql = "select DBTable from FieldMap where 
  	   FormID = $FormID and 
  	   DBTable not in (\"Contact\",\"Company\",\"Orders\") 
  	   group by DBTable";
  @tables = $D->select($sql);
  for $countt ( 1 .. $#tables ) {
    $DBTable = $tables[$countt]->{DBTable};
    %Type = ();
    $sql = "select DBCompNum from FieldMap where 
    	     DBTable=\"$DBTable\" and 
    	     FormID = $FormID 
    	     group by DBCompNum";
    @rowscomp = $D->select($sql);
    for $countc ( 1 .. $#rowscomp ) {
      @columns = ("OrderID");
      @values = ($OrderID);
      $DBCompNum = $rowscomp[$countc]->{DBCompNum};
      if ( $DBCompNum != 0 ) {
  	 push @columns , "CompNumber";
  	 push @values , $DBCompNum;
      }
      $sql = "select * from FieldMap where 
              DBTable=\"$DBTable\" and 
              FormID = $FormID and 
              DBCompNum = $DBCompNum";
      @rowsmap = $D->select($sql);
      for $count2 ( 1 .. $#rowsmap ) {
        my $DBCol = $rowsmap[$count2]->{DBCol};
        my $PDFTag = $rowsmap[$count2]->{PDFTag};
        if ( $self->{$PDFTag} ) {
          if ( ! %Type ) {
            $sql = "select c.name as ColumnName, 
	             t.name as ColumnType 
	             from 
	             syscolumns c, 
	             systypes t 
	             where 
	             object_name(c.id)=\"$DBTable\" and 
	             c.usertype=t.usertype";
            @rowstype = $D->select($sql);
            for $count1 ( 1 .. $#rowstype) {
              $Type{$rowstype[$count1]->{'ColumnName'}} = $rowstype[$count1]->{'ColumnType'};
            }
          }
          push @columns, "$DBCol";
          if ( $Type{$DBCol} =~ /numeric|float|int|money/ ) {
            # Process Digit
            print LOG "variable $PDFTag, value $self->{$PDFTag}, type $Type{$DBCol}, This is numeric\n";
            push @values, $self->{$PDFTag};
          } else {
            # Process Text
            print LOG "variable $PDFTag, value $self->{$PDFTag}, type $Type{$DBCol}, This is text";
            push @values, "\"$self->{$PDFTag}\"";
          }
        }
      }
      if ( $DBCompNum != 0 ) {
        $sql = "select count(*) as numrows from $DBTable where 
                OrderID=$OrderID and 
                CompNumber = $DBCompNum";
      } else {
        $sql = "select count(*) as numrows from $DBTable where 
                OrderID=$OrderID";
      }
      @rowsthere = $D->select($sql);
      if ( $rowsthere[1]{numrows} < 1 ) {
        #Do Insert
        if ($columns[$#columns] ne "OrderID" && $columns[$#columns] ne "CompNumber") {
          $columnstext = join ",",@columns;
          $valuestext = join ",",@values;
          $sql = "insert into $DBTable($columnstext) values($valuestext)";
          print LOG $sql. "\n";
          $D->execute($sql);
        }
      } else {
        #Do Update
        my @update = ();
        for $count1 ( 0 .. $#columns ) {
          next if ($columns[$count1] eq 'OrderID' || $columns[$count1] eq 'CompNumber');
          push @update, join("",$columns[$count1],"=",$values[$count1]);
        }
        my $where = ( $DBCompNum == 0 ) ? "" : " and CompNumber = $DBCompNum";
        $sql = join "","update $DBTable set ",(join ', ', @update)," where 
                        OrderID = $OrderID",$where;
        print LOG $sql. "\n";
        $D->execute($sql);
      }
    }
  }
  if ( $self->{Process} eq "Submit" ) {
    # Update the Orders table to reflect the completion of the form by the Supplier
    # enabling the ability to view the form via PDF.   (NOT IMPLEMENTED)
    print LOG "The order was completed and submited\n";
    $self->{ScriptOnTheFly} = "alert('Form has been submitted');";
  } else {
    # Don't do anything... We already saved it.
    print LOG "The order was saved for later completion...'$self->{Process}'\n";
    $self->{ScriptOnTheFly} = "alert('Form has been saved.  
                                      Do not forget that you must return and complete form later');";
  }
}