#!/usr/bin/perl

use DBI;
use CGI qw(:cgi);

use feature qw(switch);
no warnings qw(experimental::smartmatch);

##############################################################################
#                                                                            #
# configuration options are kept in a separate file                          #
#                                                                            #
##############################################################################

do "./f20_budget_config.pl";

##############################################################################
#                                                                            #
#  main CGI loop                                                             #
#                                                                            #
##############################################################################

$CGI::DISABLE_UPLOADS   = 0;
$CGI::POST_MAX          = '10000000';

$q = new CGI;

$action=$q->param("action");

$password=$q->param('password');
$askpassword=$q->param('askpassword');

$delete_id=$q->param("delete_id");
$edit_id=$q->param("edit_id");
$budget_id=$q->param("budget_id");
$agent_id=$q->param("agent_id");
$edit_agent_id=$q->param("edit_agent_id");

$new_auto_name=$q->param('new_auto_name');
$new_auto_name =~ s/\'/\'\'/g;
$new_auto_location=$q->param('new_auto_location');
$new_auto_location =~ s/\'/\'\'/g;
$new_auto_remarks=$q->param('new_auto_remarks');
$new_auto_remarks =~ s/\'/\'\'/g;
$new_auto_url=$q->param('new_auto_url');
$new_auto_url =~ s/\'/\'\'/g;

$new_name=$q->param('new_name');
$new_name =~ s/\'/\'\'/g;
$new_codename=$q->param('new_codename');
$new_codename =~ s/\'/\'\'/g;
$new_pseudo=$q->param('new_pseudo');
$new_pseudo =~ s/\'/\'\'/g;
$new_firstname=$q->param('new_firstname');
$new_firstname =~ s/\'/\'\'/g;
$new_lastname=$q->param('new_lastname');
$new_lastname =~ s/\'/\'\'/g;
$new_lastname=$q->param('new_lastname');
$new_fullname =~ s/\'/\'\'/g;
$new_fullname=$q->param('new_fullname');

$new_agent=$q->param('new_agent');
$new_location=$q->param('new_location');
$new_location =~ s/\'/\'\'/g;
$new_remarks=$q->param('new_remarks');
$new_remarks =~ s/\'/\'\'/g;
$new_url=$q->param('new_url');
$new_url =~ s/\'/\'\'/g;

$new_amount=$q->param('new_amount');
$new_amount = sprintf "%.2f",$new_amount; ## 2 decimal places

$new_url=$q->param("new_url");
#$new_url=~ s/http\:\/\///g;               # don't remove http://

$new_item = sanitise($q->param("new_item"));
$new_labour = sanitise($q->param("new_labour"));
$new_resource = sanitise($q->param("new_resource"));
$insert_labour = sanitise($q->param("insert_labour"));
$insert_resources = sanitise($q->param("insert_resources"));
$new_status = sanitise($q->param("new_status"));
$new_lstatus = sanitise($q->param("new_lstatus"));
$new_rstatus = sanitise($q->param("new_rstatus"));
$new_notes = sanitise($q->param("new_notes"));
$type = sanitise($q->param("type"));

for ( $i=0; $i<=50; $i+=1) {
    $new_status{$i} = sanitise($q->param("new_status$i"));
    $type{$i} = sanitise($q->param("type$i"));
    $new_item{$i} = sanitise($q->param("new_item$i"));
    $new_resource{$i} = sanitise($q->param("new_resource$i"));
    $new_labour{$i} = sanitise($q->param("new_labour$i"));
    $new_status{$i} = sanitise($q->param("new_status$i"));
    $new_lstatus{$i} = sanitise($q->param("new_lstatus$i"));
    $new_rstatus{$i} = sanitise($q->param("new_rstatus$i"));

    $new_firstname{$i} = sanitise($q->param("new_firstname$i"));
    $new_lastname{$i} = sanitise($q->param("new_lastname$i"));
    $new_codename{$i} = sanitise($q->param("new_codename$i"));

    $cost{$i}=~ s/\€/@euro/g;
    $cost{$i} = $q->param("cost$i");
    $new_cost{$i}=~ s/\€/@euro/g;
    $new_cost{$i} = $q->param("new_cost$i");
    $currency{$i} = $q->param("currency$i");
}


($second, $minute, $hour, $currentdayofmonth, $month, $year, $weekday, $dayofyear, $IsDST) = localtime(time);
$currentmonth=$month+1;
$currentyear=$year+'1900';

$new_day=$q->param("'new_day");
$new_month=$q->param("new_month");
$new_year=$q->param("new_year");

$new_date=$q->param('new_year') . $q->param("new_month") . $q->param("new_day");
$new_start_date=$q->param('new_start_year') . $q->param("new_start_month") . $q->param("new_start_day");
$new_end_date=$q->param('new_end_year') . $q->param("new_end_month") . $q->param("new_end_day");
$new_print_date=$q->param('new_print_year') . $q->param("new_print_month") . $q->param("new_print_day");
$new_deliver_day=$q->param("new_deliver_day");
$new_deliver_date=$q->param('new_deliver_year') . $q->param("new_deliver_month") . $q->param("new_deliver_day");


if ($new_name eq '') {
    $new_name=$new_auto_name;
}
if ($new_location eq '') {
    $new_location=$new_auto_location;
}
if ($new_remarks eq '') {
    $new_remarks=$new_auto_remarks;
}
if ($new_url eq '') {
    $new_remarks=$new_auto_url;
}

given ($action) {
    when('select_budget') {&select_budget; }
    when('add_form') {&add_form; }
    when('add_budget') {&add_budget; }
    when('add_budget_information') {&add_budget_information; }
    when('prepare_agents') {&prepare_agents; }
    when('input_agents') {&input_agents; }
    when('edit_agent') {&edit_agent; }
    when('update_agent') {&update_agent; }
    when('edit_form') {&edit_form; }
    when('edit_select_form') {&edit_select_form; }
    when('edit_budget') {&edit_budget; }
    when('list_budgets') {&list_budgets; }
    when('prepare_budget') {&prepare_budget; }
    when('prepare_money') {&prepare_money; }
    when('input_budget') {&input_budget; }
    when('input_money') {&input_money; }
    when('prepare_expenditure') {&prepare_expenditure; }
    when('input_expenditure') {&input_expenditure; }
    when('compile_budget') {&compile_budget; }
    when('display_budgets') {&display_budgets; }
    when('present_budget') {&present_budget; }
    when('delete_form') {&delete_form; }
    when('delete_budget') {&delete_budget; }
    when('select_budget') {&select_budget; }
    when('upload_image') {&upload_image; }
    #default {&display_budgets; }
    default {
	&select_budget;
    }
}
exit;

# saniti[sz]e parameters and  sql inputs as needed
sub sanitise {
    my $string = shift;
    $string =~ s/\'/\'\'/g;        # convert ' to ''
    $string =~ s/\x92/&rsquo;/g;   # convert "RIGHT SINGLE QUOTATION MARK" to html
    return $string;
}

##############################################################################
#                                                                            #
# select budget                                                              #
#                                                                            #
##############################################################################

sub select_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT DISTINCT name from budgets ORDER BY id DESC);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    my $sql2 = qq( SELECT DISTINCT firstname, lastname from agents ORDER BY firstname);
    my $sth2 = $dbh->prepare($sql2);
    $sth2->execute;

    &print_header;

    print <<END;
 <center>
 <table width="700" cellspacing="20" cellpadding="0" bgcolor="#cccccc">
  <tr><td><table  width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">
  <tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000">
   <b>feral budget generator: enter agent </b></td></tr>
  <tr><td height="20">&nbsp;</td></tr>
  <tr><td colspan="1">budget:</td>
  <FORM ACTION="$script_name"METHOD="GET">
  <input type="hidden" name="action" value="prepare_budget">
END

    ## name budget

    print(qq(<td height="10" colspan="2"><select name="new_name">));
    while (my $record = $sth->fetchrow_hashref) {
	if ($record->{name} eq 'f20') {
	    print(qq(<option>$record->{name}</option>\n));
	}
    }
    print(qq(</select></td></tr>\n));

    ## enter agent code

    print <<END;
<tr><td colspan="1">enter your agent codename:</td>
  <FORM ACTION="$script_name"METHOD="GET">
  <td colspan="3">
   <input type="text" name="new_codename" value="" size="5" maxsize="5">
  </td></tr>
  <tr><td colspan="2">
    <INPUT TYPE="submit" VALUE="ENTER"></td></tr>
  </td></tr>
  </FORM>
  </td></tr></table></table>
END
    &print_footer;
    $dbh->disconnect;

}


##############################################################################
#                                                                            #
#  prepare budget                                                            #
#                                                                            #
##############################################################################

sub prepare_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT * from budgets WHERE name="$new_name" );
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    ## identify agent by codename
    my $sql2 = qq( SELECT * from agents where codename ="$new_codename" );
    my $sth2 = $dbh->prepare($sql2);
    $sth2->execute;

    while (my $record = $sth->fetchrow_hashref) {
	if (my $record2 = $sth2->fetchrow_hashref) {

	    my $sql3 = qq( SELECT * from finance LEFT JOIN agents ON finance.agent_id=agents.id WHERE finance.budget_id='$record->{id}' AND finance.agent_id ='$record2->{id}' );
	    my $sth3 = $dbh->prepare($sql3);
	    $sth3->execute;

	    &print_header;

	    print <<END;
<center>
      <table width="1000" cellspacing="20" cellpadding="0" bgcolor="#ffffff">
      <tr><td><table width="900" cellspacing="1" cellpadding="2" bgcolor="#ffffff" border="0">
      <tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>
      <tr><td height="10" class="medbig" colspan="5"><b> Instructions</td></tr>
      <tr><td height="10" class="" colspan="2"><td align="center" valign="top"><BR>

<img src="$feral_logo"></td>

      <td><table width="500" border="0"<tr><td class="serif">
      Enter your budget requests &amp; contributions. After entering your items will then be able to view the budget as a whole, as it takes shape. You can return to edit your entries at any time. <BR><BR></td></tr></table>

      <tr><table width="1000" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0"><td colspan="1">budget name:</td>

      <FORM ACTION="$script_name"METHOD="GET">

      <input type="hidden" name="action" value="input_budget">
      <input type="hidden" name="budget_id" value="$record->{id}">
      <input type="hidden" name="agent_id" value="$record2->{id}">
      <input type="hidden" name="new_codename" value="$new_codename">

      <td height="10" colspan="2" class="medbig">

      <b>$record->{name}</b></td><tr>

      <tr><td colspan="1">your name:</td>
      <td height="10" colspan="3" class="medbig">
        $record2->{firstname} $record2->{lastname}
      </td></tr>

      <tr><td colspan="8" height="20" bgcolor="#fff000" >Financial budget elements</td><td></td></tr>;

      <tr><td colspan="1" class="medbig">
       <b>request money</b></td><td colspan="3">
         your monetary requests from the f20 budget (all sums in &euro;)</td></tr>
       </td></tr>
      <tr><td>
END

	    my $sql4 = qq( SELECT * from expenditure where budget_id='$record->{id}' AND agent_id ='$record2->{id}' );
	    my $sth4 = $dbh->prepare($sql4);
	    $sth4->execute;

	    for ($i=1;$i<=5;$i++) {
		while (my $record4 = $sth4->fetchrow_hashref) {
		    print <<END;
          <tr><td colspan="2">$i.$currency
	  <input type ="text" name="new_cost$i" value="$record4->{cost}" size="2" maxsize="5">
	  </td>
           <td><select name="type$i">
END
		    @cats = ('select category', 'fee', 'materials', 'misc');
		    foreach $cat(@cats) {
			print(qq(<option));
			if ($cat eq $record4->{type}) {
			    print(qq( selected));
			}
			print(qq(>$cat</option>));
		    }
		    print(qq(</select></td>));
		    print(qq(<td colspan="3">item <input type="text" name="new_item$i" value="$record4->{item}" size="70" maxsize="120"></td></tr>));
		    $i++
		}

		if ($i<=5) {

		    print <<END;
	  <tr><td colspan="2">$i.&euro;
	  <input type ="text" name="new_cost$i" size="2" maxsize="5"></td>
	  <td><select name="type$i">
END
		    @cats = ('select category', 'fee', 'materials', 'misc');
		    foreach $cat(@cats) {
			print(qq(<option));
			if ($cat eq $record4->{type}) {
			    print(qq( selected));
			}
			print(qq(>$cat</option>));
		    }
		    print(qq(</select></td>));

		    print(qq(<td colspan="3">item <input type="text" name="new_item$i" value="$record4->{item}" size="70" maxsize="100"></td></tr>));
		}
	    }

	    print(qq(</td></tr></table>\n));

	    print(qq(<tr><table width="950" cellspacing="1" cellpadding="2" bgcolor="#ffffff" border="0"><td colspan="1"></td> ));
	    print(qq(<tr><td colspan="1" class="medbig"><b>&/or input money</b></td><td colspan="8">your monetary contribution to the budget (direct payments for materials/services, donations to f20, as a sum total) </td></tr>));
	    print(qq(<tr><td colspan="1">&euro;<input type ="text" name="new_amount" ));
	    while (my $record3 = $sth3->fetchrow_hashref) {
		print(qq(value="$record3->{amount}" ));
	    }
	    print(qq( size="3" maxsize="5"></td></tr>\n));


	    ## resources section

	    print(qq(<tr><td colspan="8" height="30">&nbsp;</td></tr>\n));
	    print(qq(<tr><td colspan="8" height="20" bgcolor="#fff000" >Non-financial budget elements</td><td></td></tr>\n));
	    print(qq(<tr><td colspan="1" class="medbig"><b>input labour</b></td><td colspan="8">things you commit to doing, non-remunerated. (check the box if this labour has already been deployed) </td></tr>));


              my $sql5 = qq( SELECT * from nonfinance where budget_id=$record->{id} AND agent_id =$record2->{id} AND kind='labour' );
              my $sth5 = $dbh->prepare($sql5);
              $sth5->execute;

              my $sqlCL = qq( SELECT count(id) AS count from nonfinance where budget_id=$record->{id} AND agent_id =$record2->{id} AND kind='labour' );
              my $sthCL = $dbh->prepare($sqlCL);
              $sthCL->execute;

             while (my $recordCL = $sthCL->fetchrow_hashref) {
             my $t = $recordCL->{count};

                for ($i=1;$i<=$t;$i++) {
                      while (my $record5 = $sth5->fetchrow_hashref) {
                           print "<tr><td colspan=\"1\"><b>$i.</b></td>\n";
                           print "<td><input type=\"checkbox\" NAME=\"new_lstatus$i\" VALUE=\"delivered\"";
                           if ($record5->{status} eq 'delivered') { print "CHECKED"};
                           print "></td>\n";
                           print "<td colspan=\"3\"><input type=\"text\" name=\"new_labour$i\" value=\"$record5->{item}\" size=\"100\" maxsize=\"200\"></td></tr>\n";
                           $i++
                      }
                  }

            print "<tr><td colspan=\"4\"><input type=\"text\" name=\"insert_labour\" size=1 maxsize=2 value=\"\">";
            print " enter a number to add budget lines </td></tr>\n";

           }


	    print(qq(<tr><td colspan="8" height="20">&nbsp;</td></tr>\n));
	    print(qq(<tr><td colspan="1" class="medbig"><b>input resources</b></td><td colspan="8">materials, spaces, tools, previous work etc (check the box if your resource has already been deployed) </td></tr>));

              my $sql5 = qq( SELECT * from nonfinance where budget_id=$record->{id} AND agent_id =$record2->{id} AND kind='resources' );
              my $sth5 = $dbh->prepare($sql5);
              $sth5->execute;


              my $sqlCR = qq( SELECT count(id) AS count from nonfinance where budget_id=$record->{id} AND agent_id =$record2->{id} AND kind='resources' );
              my $sthCR = $dbh->prepare($sqlCR);
              $sthCR->execute;

             while (my $recordCR = $sthCR->fetchrow_hashref) {
             my $t = $recordCR->{count};

                for ($i=1;$i<=$t;$i++) {
                      while (my $record5 = $sth5->fetchrow_hashref) {
                           print "<tr><td colspan=\"1\"><b>$i.</b></td>\n";
                           print "<td><input type=\"checkbox\" NAME=\"new_rstatus$i\" VALUE=\"delivered\"";
                           if ($record5->{status} eq 'delivered') { print "CHECKED"};
                           print "></td>\n";
                           print "<td colspan=\"3\"><input type=\"text\" name=\"new_resource$i\" value=\"$record5->{item}\" size=\"100\" maxsize=\"200\"></td></tr>\n";
                           $i++
                      }
                  }

            print "<tr><td colspan=\"4\"><input type=\"text\" name=\"insert_resources\" size=1 maxsize=2 value=\"\">";
            print " enter a number to add budget lines</td></tr>\n";
            }

	    my $sql6 = qq( SELECT * from nonfinance where budget_id=$record->{id} AND agent_id =$record2->{id} AND kind='notes');
	    my $sth6 = $dbh->prepare($sql6);
	    $sth6->execute;

	    print(qq(<tr><td colspan="8" height="20">&nbsp;</td></tr>\n));
       	    print(qq(<tr><td colspan="1" class="medbig"><b>notes</b></td><td colspan="8">for your own reference only, these notes will not show up in the shared budget</td></tr>\n));
            print(qq(<tr><td colspan=4 align="left"><TEXTAREA NAME="new_notes" ROWS="6" COLS="110" WRAP=SOFT>));
            while (my $record6 = $sth6->fetchrow_hashref) {
               print(qq($record6->{item}));
           }
            print (qq(</textarea></td></tr>\n));
	       print(qq(<tr><td colspan="1"></td><td colspan="1"></td><td colspan="1"><INPUT TYPE="submit" VALUE="SUBMIT"></td></tr>\n));
	       print(qq(</td></tr>\n));
	       print(qq(</FORM>\n));
	       print(qq(<tr><td height="10"></td></tr>\n));
	       print(qq(</td></tr></table></table>\n));

	} else {
	    & print_header;
	    print(qq(codename not recognised. please retry\n));
	}
    }
    &print_footer;
    $dbh->disconnect;
}


##############################################################################
#                                                                            #
#  input budget: input individual budet items - money & resources            #
#                                                                            #
##############################################################################

sub input_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="20" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table  width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));

    ## delete expenditure line for agent + budget, if exists
    my $sqld = qq( DELETE from expenditure where budget_id="$budget_id" AND agent_id="$agent_id");
    my $sthd= $dbh->prepare($sqld);
    $sthd->execute;

    ## input new expenditure lines for agent

    for ($i=1; $i<=8; $i++) {
	if ($new_item{$i} ne '') {
	    my $sql = (qq{INSERT INTO expenditure (budget_id, agent_id, status, type, item, cost, currency) VALUES ($budget_id, $agent_id, '$new_status{$i}', '$type{$i}', '$new_item{$i}', $new_cost{$i}, '&euro;')});
	    my $sth = $dbh->prepare($sql);
	    $sth->execute;
	}
    }

    ## read in finance for agent

    my $sql = qq( SELECT * from finance LEFT JOIN agents ON finance.agent_id=agents.id
              LEFT JOIN budgets ON finance.budget_id = budgets.id WHERE finance.budget_id="$budget_id" AND finance.agent_id="$agent_id");
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    #while (my $record = $sth->fetchrow_hashref) {
    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator: $record->{name}</b></td></tr>\n));

    ## delete finance line for agent + budget, if exists
    my $sqld = qq( DELETE from finance where budget_id="$budget_id" AND agent_id="$agent_id");
    my $sthd= $dbh->prepare($sqld);
    $sthd->execute;

    ## input money into finance
    #   if ($new_amount ne '') {
    if ($new_amount eq '') {
	$new_amount=0;
    }
    my $sqli = (qq{INSERT INTO finance (budget_id, agent_id, amount) VALUES ($budget_id, $agent_id, $new_amount)});
    my $sthi = $dbh->prepare($sqli);
    $sthi->execute;

    ## delete nonfinance line for agent + budget, if exists
    my $sqld = qq( DELETE from nonfinance where budget_id="$budget_id" AND agent_id="$agent_id");
    my $sthd= $dbh->prepare($sqld);
    $sthd->execute;

    ## input labour into nonfinance for agent

    for ($i=1; $i<=8; $i++) {
	if ($new_labour{$i} ne '') {
	    my $sqll = (qq{INSERT INTO nonfinance (budget_id, agent_id, item, kind, status) VALUES ($budget_id, $agent_id, "$new_labour{$i}", "labour", "$new_lstatus{$i}")});
	    my $sthl = $dbh->prepare($sqll);
	    $sthl->execute;
	}
    }

    ## input resources into nonfinance for agent
    for ($i=1; $i<=8; $i++) {
	if ($new_resource{$i} ne '') {
	    my $sqlr = (qq{INSERT INTO nonfinance (budget_id, agent_id, item, kind, status) VALUES ($budget_id, $agent_id, "$new_resource{$i}", "resources", "$new_rstatus{$i}")});
	    my $sthr = $dbh->prepare($sqlr);
	    $sthr->execute;
	}
    }


## add new budget lines for resources or labour ***

if (param('insert_labour') ne '') {
#if ('$insert_labour' ne '') {
            for ($i=1; $i<=$insert_labour; $i++) {
            my $sqlIL = (qq{INSERT INTO nonfinance (budget_id, agent_id, item, kind, status) VALUES ('$budget_id','$agent_id','', 'labour', '')});
            my $sthIL = $dbh->prepare($sqlIL);
            $sthIL->execute;
            }

}

if (param('insert_resources') ne '') {
#if ('$insert_resources' ne '') {
            for ($i=1; $i<=$insert_resources; $i++) {
            my $sqlIR = (qq{INSERT INTO nonfinance (budget_id, agent_id, item, kind, status) VALUES ('$budget_id','$agent_id','', 'resources', '')});
            my $sthIR = $dbh->prepare($sqlIR);
            $sthIR->execute;
            }
}

    ## input notes nonfinance for agent
    if ($new_notes ne '') {
	    my $sqln = (qq{INSERT INTO nonfinance (budget_id, agent_id, item, kind) VALUES ($budget_id, $agent_id, "$new_notes", "notes")});
	    my $sthn = $dbh->prepare($sqln);
	    $sthn->execute;
    }

    ## read back in to get these values from the db, not from the cgi

    my $sqlv = (qq{SELECT DISTINCT item, cost, expenditure.currency, type, fullname from expenditure RIGHT JOIN agents on expenditure.agent_id=agents.id LEFT JOIN finance on expenditure.agent_id=finance.agent_id
                          WHERE expenditure.agent_id=$agent_id AND expenditure.budget_id=$budget_id});
    my $sthv = $dbh->prepare($sqlv);
    $sthv->execute;
    my $sqln = (qq{ SELECT fullname from agents where id=$agent_id});
    my $sthn = $dbh->prepare($sqln);
    $sthn->execute;

    while (my $recordn = $sthn->fetchrow_hashref) {
	print(qq(<tr><td colspan="1" width="" height="10"class="medbig">$projectname: <b>$recordn->{fullname}</b></td></tr> ));
    }

    #    print(qq(<tr><td colspan="4"><b>your PROPOSITION:</b> ));
    while (my $recordv = $sthv->fetchrow_hashref) {
	if ($recordv->{item} ne '') {
	    print(qq(<tr><td>$recordv->{$currency} $recordv->{$cost} $recordv->{$item} $recordv->{$type}</td></tr>\n));
	}
    }

    print(qq(<tr><td></td></tr>\n));
    print(qq(<tr><td></td></tr>\n));

    $sth->execute;
    while (my $record = $sth->fetchrow_hashref) {

	print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
	print(qq(<input type="hidden" name="action" value="prepare_budget">\n));
	print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
	print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
	print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
	print(qq(<input type="hidden" name="new_agent" value="$record->{fullname}">\n));
	print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
	print(qq(<tr><td colspan="1"><INPUT TYPE="submit" VALUE="return to edit your contribution"></td></tr>\n));
	print(qq(</FORM>\n));

	print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
	print(qq(<input type="hidden" name="action" value="compile_budget">\n));
	print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
	print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
	print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
	print(qq(<input type="hidden" name="new_agent" value="$record->{fullname}">\n));
	print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
	print(qq(<tr><td colspan="1"><INPUT TYPE="submit" VALUE="go to view the budget as a whole"></td></tr>\n));
	print(qq(</td></tr></table>\n));
	print(qq(</FORM>\n));
    }
    &print_footer;
}

###############################################################################
# input money

sub input_money {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="20" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table  width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));


    ## read in finance for agent+ budget

    my $sql = qq( SELECT * from finance LEFT JOIN agents ON finance.agent_id=agents.id
              LEFT JOIN budgets ON finance.budget_id = budgets.id WHERE finance.budget_id="$budget_id" AND finance.agent_id="$agent_id");
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    while (my $record = $sth->fetchrow_hashref) {
	if ($record->{id} ne '') {

	    ## delete finance line for agent + budget, if exists
	    my $sqld = qq( DELETE from finance where budget_id="$budget_id" AND agent_id="$agent_id");
	    my $sthd= $dbh->prepare($sqld);
	    $sthd->execute;
	}
    }

    ## input money into finance
    if ($new_amount eq '') {
	$new_amount=0;
    }
    my $sqli = (qq{INSERT INTO finance (budget_id, agent_id, amount) VALUES ($budget_id, $agent_id, $new_amount)});
    my $sthi = $dbh->prepare($sqli);
    $sthi->execute;
    #}
    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator: $record->{name}</b></td></tr>\n));
    print(qq(<tr><td colspan="1" width="">your name:</td> ));
    print(qq(<td height="10" colspan="3" class="medbig">$new_fullname</td></tr>\n));
    print(qq(</td></tr>\n));

    print(qq(<tr><td colspan="1">your monetary contribution:</td> ));
    print(qq(<td height="10" colspan="3" class="medbig">&euro;$new_amount</td></tr>\n));
    print(qq(</td></tr>\n));

    print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="compile_budget">\n));
    print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
    print(qq(<input type="hidden" name="new_agent" value="$record->{fullname}">\n));
    print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
    print(qq(<tr><td colspan="1"><INPUT TYPE="submit" VALUE="go to view the budget as a whole"></td></tr>\n));
    print(qq(</td></tr></table>\n));
    print(qq(</FORM>\n));

    &print_footer;

}

##############################################################################
#                                                                            #
# compile budget: lay out the whole budget as it assembles                   #
#                                                                            #
##############################################################################

sub compile_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sqld = qq(
      SELECT * FROM nonfinance
      LEFT JOIN agents ON nonfinance.agent_id=agents.id
      LEFT JOIN budgets ON nonfinance.budget_id=budgets.id
      LEFT JOIN locations ON locations.structure=budgets.location
        WHERE nonfinance.budget_id=$budget_id
        AND nonfinance.status='delivered'
      ORDER BY nonfinance.item);
    my $sthd = $dbh->prepare($sqld);
    $sthd->execute;

    my $sqlol = qq(
      SELECT * FROM nonfinance
      LEFT JOIN agents on nonfinance.agent_id=agents.id
      LEFT JOIN budgets ON nonfinance.budget_id=budgets.id
      LEFT JOIN locations ON locations.structure=budgets.location
        WHERE nonfinance.budget_id=$budget_id
	AND nonfinance.status!='delivered'
	AND kind='labour'
      ORDER BY nonfinance.item);
    my $sthol = $dbh->prepare($sqlol);
    $sthol->execute;

    my $sqlor = qq(
      SELECT * FROM nonfinance
      LEFT JOIN agents on nonfinance.agent_id=agents.id
      LEFT JOIN budgets ON nonfinance.budget_id=budgets.id
      LEFT JOIN locations ON locations.structure=budgets.location
        WHERE nonfinance.budget_id=$budget_id
        AND nonfinance.status!='delivered'
        AND kind='resources'
      ORDER BY nonfinance.item);
    my $sthor = $dbh->prepare($sqlor);
    $sthor->execute;

    my $sqldl = qq(
      SELECT * FROM nonfinance
      LEFT JOIN agents on nonfinance.agent_id=agents.id
      LEFT JOIN budgets ON nonfinance.budget_id=budgets.id
      LEFT JOIN locations ON locations.structure=budgets.location
        WHERE nonfinance.budget_id=$budget_id
        AND nonfinance.status='delivered'
        AND kind='labour'
      ORDER BY nonfinance.item);
    my $sthdl = $dbh->prepare($sqldl);
    $sthdl->execute;

    my $sqldr = qq(
      SELECT * FROM nonfinance
      LEFT JOIN agents on nonfinance.agent_id=agents.id
      LEFT JOIN budgets ON nonfinance.budget_id=budgets.id
      LEFT JOIN locations ON locations.structure=budgets.location
        WHERE nonfinance.budget_id=$budget_id
        AND nonfinance.status='delivered'
        AND kind='resources'
      ORDER BY nonfinance.item);
    my $sthdr = $dbh->prepare($sqldr);
    $sthdr->execute;

    ## count potential deposits
    my $sqla = qq(
      SELECT COUNT(fullname) AS qty FROM agents
      WHERE budget_id=$budget_id
      AND status!='staff');
    my $stha = $dbh->prepare($sqla);
    $stha->execute;

    ## count deposits received
    my $sqlar = qq(
      SELECT COUNT(fullname) AS qty FROM agents
      WHERE budget_id=$budget_id
      AND status='deposit');
    my $sthar = $dbh->prepare($sqlar);
    $sthar->execute;

    my $sqlc = qq(
      SELECT COUNT(*) FROM nonfinance
      LEFT JOIN agents on nonfinance.agent_id=agent.id
      LEFT JOIN budgets ON nonfinance.budget_id=budgets.id
      LEFT JOIN locations ON locations.structure=budgets.location
        WHERE nonfinance.budget_id=$budget_id);
    my $sthc = $dbh->prepare($sqlc);
    $sthc->execute;

    my $sqlf = qq(
      SELECT * FROM finance WHERE budget_id=$budget_id
      ORDER by amount);
    my $sthf = $dbh->prepare($sqlf);
    $sthf->execute;

    my $sqlb = qq(
      SELECT *,  date_format(deliver_date,'%d-%m-%Y')
      AS end_date FROM budgets
      LEFT JOIN locations on budgets.location=locations.structure
      WHERE budgets.id=$budget_id);
    my $sthb = $dbh->prepare($sqlb);
    $sthb->execute;

    my $sqlfc = qq( SELECT COUNT(id) AS count FROM finance WHERE budget_id=$budget_id);
    my $sthfc = $dbh->prepare($sqlfc);
    $sthfc->execute;

    my $sqls = qq( SELECT SUM(amount) FROM finance WHERE budget_id=$budget_id);
    my $sths = $dbh->prepare($sqls);
    $sths->execute;

    my $sqlse = qq( SELECT SUM(cost) FROM expenditure WHERE budget_id=$budget_id);
    my $sthse = $dbh->prepare($sqlse);
    $sthse->execute;

    my $sqlr = qq(
      SELECT name, fullname, codename FROM finance
      LEFT JOIN agents ON finance.agent_id=agents.id
      LEFT JOIN budgets ON finance.budget_id = budgets.id
        WHERE finance.budget_id="$budget_id"
        AND finance.agent_id="$agent_id");
    my $sthr = $dbh->prepare($sqlr);
    $sthr->execute;

    my $sqlef = qq(
#     SELECT budget_id, item, cost, currency, type
      SELECT *
      FROM expenditure
      LEFT JOIN budgets on expenditure.budget_id=budgets.id
      LEFT JOIN agents on expenditure.agent_id=agents.id
        WHERE expenditure.budget_id = '$budget_id'
        AND type='fee'
      ORDER BY expenditure.item);
    my $sthef = $dbh->prepare($sqlef);
    $sthef->execute;

    my $sqlem = qq(
#      SELECT budget_id, item, cost, currency, type FROM expenditure
      SELECT *
      FROM expenditure
      LEFT JOIN budgets on expenditure.budget_id=budgets.id
      LEFT JOIN agents on expenditure.agent_id=agents.id
        WHERE expenditure.budget_id = '$budget_id'
        AND type='materials'
      ORDER BY expenditure.item);
    my $sthem = $dbh->prepare($sqlem);
    $sthem->execute;

    my $sqlex = qq(
#     SELECT budget_id, item, cost, currency, type FROM expenditure
      SELECT *
      FROM expenditure
      LEFT JOIN budgets on expenditure.budget_id=budgets.id
      LEFT JOIN agents on expenditure.agent_id=agents.id
        WHERE expenditure.budget_id = '$budget_id'
        AND type='misc'
      ORDER BY expenditure.item);
    my $sthex = $dbh->prepare($sqlex);
    $sthex->execute;

    $sthb->execute;
    my $recordb = $sthb->fetchrow_hashref;

    # page header
    &print_header;

    # budget header
    print <<END;
  <div class="header">
    <div class="headline-1">
      feral budget generator | | $website_url/$script_name
    </div>
    <div class="headline-2">
      <b>$projectname</b> | |
      $recordb->{location} | |
      budget adjustments to $currentdayofmonth-$currentmonth-$currentyear
    </div>
    <div class="headline-3">
      <img src="$feral_logo">
END
    if ($agent_id ne "") {
	while (my $recordfc = $sthfc->fetchrow_hashref) {
	    print <<END;
Contributing agents <b>[$recordfc->{count}]</b>. This multi-player budget will remain open for editing until <b>$recordb->{end_date}</b>.
END
	}
    }
    print <<END;
    </div>
  </div>
END

    # CGI parameter forms
    $sthr->execute;
    while (my $recordr = $sthr->fetchrow_hashref) {
	print<<END;
    <FORM ACTION="$script_name"METHOD="GET">
    <input type="hidden" name="action" value="prepare_budget">
    <input type="hidden" name="new_name" value="$recordr->{name}">
    <input type="hidden" name="agent_id" value="$agent_id">
    <input type="hidden" name="budget_id" value="$budget_id">
    <input type="hidden" name="new_agent" value="$recordr->{fullname}">
    <input type="hidden" name="new_codename" value="$recordr->{codename}">
    <INPUT TYPE="submit" VALUE="return to edit your contribution">
    </FORM>
END
    }

    if ($agent_id eq "") {
	print<<END;
    <FORM ACTION="$script_name"METHOD="GET">
    <input type="hidden" name="action" value="">
    <input type="hidden" name="new_name" value="$recordr->{name}">
    <input type="hidden" name="budget_id" value="$budget_id">
    <INPUT TYPE="submit" VALUE="log in">
    </FORM>
END
    } else {
	print<<END;
    <FORM ACTION="$script_name"METHOD="GET">
    <input type="hidden" name="action" value="prepare_expenditure">
    <input type="hidden" name="new_name" value="$recordr->{name}">
    <input type="hidden" name="budget_id" value="$budget_id">
    <input type="hidden" name="agent_id" value="$agent_id">
    </FORM>
END
    }

    # budget columns
    print <<END;
    <div id="budget" class="container">
      <div id="left" class="column">
END

    print_title("FINANCIAL");

    print_subtitle("OUTGOINGS: / money requested or supplied");

    print_subtitle("Labour / paid");

    while (my $recordef = $sthef->fetchrow_hashref) {
	my $cost = $recordef->{cost};
	$display_cost = sprintf "%.2f",$cost;
	$display_cost =~ s/(\d)(?=(\d{3})+(\D|$))/$1\,/g; ## add commma
        $initials = get_initials($recordef);

	print <<END;
		<div class="budget-item">
		    <p class="item-name">$recordef->{item}</p>
		    <p class="item-amount">$recordef->{currency}$display_cost</p>
		</div>
END
    }

    print_subtitle("Resources / remunerated");

    while (my $recordem = $sthem->fetchrow_hashref) {
	my $cost = $recordem->{cost};
	$display_cost = sprintf "%.2f",$cost;
	$display_cost =~ s/(\d)(?=(\d{3})+(\D|$))/$1\,/g; ## add commma

	print_item("$recordem->{item}","$recordem->{currency}$display_cost");
    }

    print_subtitle("Miscellaneous");

    while (my $recordex = $sthex->fetchrow_hashref) {
	my $cost = $recordex->{cost};
	$display_cost = sprintf "%.2f",$cost;
	$display_cost =~ s/(\d)(?=(\d{3})+(\D|$))/$1\,/g; ## add commma

	print_item("$recordex->{item}", "$recordex->{currency}$display_cost");
    }

    print_subtitle("INCOME: / donations, contributions, sums, funds");

    while (my $recordf = $sthf->fetchrow_hashref) {
	print_item("", sprintf("&euro;%.2f", $recordf->{amount}));
    }

    ## actual paid deposits
    while (my $recordar = $sthar->fetchrow_hashref) {
	while (my @sum = $sths->fetchrow_array()) {
	    $deposits = $recordar->{qty}*50;
	    $running = $sum[0]+$deposits;
	    $display_running = sprintf "%.2f",$running;
	    $display_running =~ s/(\d)(?=(\d{3})+(\D|$))/$1\,/g; ## add commma

	    print_subtotal(" "); # spacing before running lines
	    print_subtotal("RUNNING total IN:", "&euro;$display_running");
	}
    }

    while (my @sumex = $sthse->fetchrow_array()) {
	$runningex = $sumex[0];
	$surplus = $running - $runningex;
	$display_runningex = sprintf "%.2f",$runningex;
	$display_runningex =~ s/(\d)(?=(\d{3})+(\D|$))/$1\,/g; ## add commma
	$display_surplus = sprintf "%.2f",$surplus;
	$display_surplus =~ s/(\d)(?=(\d{3})+(\D|$))/$1\,/g; ## add commma

	print_subtotal("RUNNING total OUT:","&euro;$display_runningex");
	print_subtotal(" "); # spacing between running & balance lines
	print_subtotal("BALANCE OF FINANCIAL TRADE:"," &euro;$display_surplus");
    }

    print <<END;
        </div>
        <div id="right" class="column">
END

    print_title("NONFINANCIAL");

    print_subtitle("Human and nonhuman budget contributions: / labour and resources, deployed &amp;/or offered, latent, awaiting activation");

    print_subtitle("Labour / deployed");

    $sthdl->execute;
    while (my $record = $sthdl->fetchrow_hashref) {
	$record->{item} =~ s/^([a-z])/\U$1/; ## capitalise first letter
	$initials = get_initials($record);
	print_item_nf("$record->{item} [$initials]");
    }

    print_subtitle("Resources / deployed");

    $sthdr->execute;
    while (my $record = $sthdr->fetchrow_hashref) {
	$record->{item} =~ s/^([a-z])/\U$1/; ## capitalise first letter
	$initials = get_initials($record);
	print_item_nf("$record->{item} [$initials]");
    }

    print_subtitle("Labour / offered");

    $sthol->execute;
    while (my $record = $sthol->fetchrow_hashref) {
	$record->{item} =~ s/^([a-z])/\U$1/; ## capitalise first letter
	$initials = get_initials($record);
	print_item_nf("$record->{item} [$initials]");
    }

    print_subtitle("Resources / offered");

    $sthor->execute;
    while (my $record = $sthor->fetchrow_hashref) {
	$record->{item} =~ s/^([a-z])/\U$1/; ## capitalise first letter
	$initials = get_initials($record);
	print_item_nf("$record->{item} [$initials]");
    }

    $dbh->disconnect;

    print(qq(</div></div>));
    &print_footer;
}


# capitalise the initials of a name from a record

sub get_initials {
    $record = shift;
    my $first=$record->{firstname} ;
    my $last=$record->{lastname} ;
    $first_init = substr($first, 0, 1);
    $last_init = substr($last, 0, 1);
    $first_init =~ s/^([a-z])/\U$1/;
    $last_init =~ s/^([a-z])/\U$1/;
    return "$first_init$last_init"
}


############################################################################
# prepare expenditure

sub prepare_expenditure {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT item, cost, currency, firstname, lastname, type FROM expenditure LEFT JOIN agents on expenditure.agent_id = agents.id WHERE expenditure.budget_id = $budget_id ORDER BY fullname);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    my $sqln = qq( SELECT name FROM budgets where id = $budget_id);
    my $sthn = $dbh->prepare($sqln);
    $sthn->execute;

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="0" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));

    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>\n));

    while (my $recordn = $sthn->fetchrow_hashref) {
	print(qq(<tr><td height="10" class="medbig" colspan="5"><b> Expenditure: $recordn->{name}</td></tr>));
    }

    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="input_expenditure">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));

    print(qq(<tr><td></td><td>agent</td><td>item</td><td colspan="2">cost</td><td>kind</td></tr>\n));

    for ($i=1;$i<=23;$i++) {
	while (my $record = $sth->fetchrow_hashref) {

	    my $cost = $record->{cost};
	    $dec_cost = sprintf "%.2f",$cost; ## 2 decimal places

	    my $first=$record->{firstname} ;
	    my $last=$record->{lastname} ;
	    $first_init = substr($first, 0, 1);
	    $last_init = substr($last, 0, 1);
	    $first_init =~ s/^([a-z])/\U$1/;	## capitalise first letter
	    $last_init =~ s/^([a-z])/\U$1/;	## capitalise first letter

	    print(qq(<tr><td><b>$i.</b></td><td>[$first_init$last_init] </td>\n));
	    print(qq(<td><input type="text" name="new_item$i" value="$record->{item}" size=80 maxsize=100></td>\n));
	    print(qq(<td><input type="text" name="cost$i" value="$dec_cost" size=6 maxsize=10></td>\n));
	    print(qq(<td><select name="currency$i">));

	    #       @currencies = ('&euro;', '&pound;', '$');
	    @currencies = ('&euro;');
	    foreach $currency(@currencies) {
		print(qq(<option));
		if ($currency eq $record->{currency}) {
		    print(qq( selected));
		}
		print(qq(>$currency</option>));
	    }
	    print(qq(</select></td></tr>\n));

	    $i++
	}
	print(qq(<tr><td><b>$i.</b></td><td></td>\n));
	print(qq(<td><input type="text" name="new_item$i" size=80 maxsize=100></td>\n));
	print(qq(<td><input type="text" name="cost$i" size=6 maxsize=10></td>\n));
	print(qq(<td><select name="currency$i">));

	#       @currencies = ('&euro;', '&pound;', '$');
	@currencies = ('&euro;');
	foreach $currency(@currencies) {
	    print(qq(<option));
	    if ($currency eq $record->{currency}) {
		print(qq( selected));
	    }
	    print(qq(>$currency</option>\n));
	}
	print(qq(</select></td></tr>\n));
    }
    print(qq(<tr><td><INPUT TYPE="submit" VALUE="submit"></td></tr>\n));
    print(qq(</FORM>\n));
    print(qq(<tr><td><BR><BR></td></tr>\n));
}


############################################################################
# input expenditure

sub input_expenditure {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    # clear previous entries
    my $sql_del = (qq{DELETE FROM expenditure WHERE budget_id=$budget_id});
    my $sth_del = $dbh->prepare($sql_del);
    $sth_del->execute;

    for ($i=1; $i<=25; $i++) {
	if ($new_item{$i} ne '') {
	    my $sql = (qq{INSERT INTO expenditure (budget_id, item, cost, currency) VALUES ($budget_id, '$new_item{$i}', $cost{$i}, '$currency{$i}')});
	    my $sth = $dbh->prepare($sql);
	    $sth->execute;
	}
    }

    # read back expenditure info

    my $sql2 = qq( SELECT budget_id, item, cost, currency FROM expenditure LEFT JOIN budgets on expenditure.budget_id=budgets.id WHERE expenditure.budget_id = '$budget_id' ORDER BY expenditure.id);
    my $sth2 = $dbh->prepare($sql2);
    $sth2->execute;

    &print_header;

    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="0" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));
    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>\n));
    print(qq(<tr><td height="10" class="medbig" colspan="5"><b> Expenditure</td></tr>));

    print(qq(<table width="700" cellspacing="1" cellpadding="0" bgcolor="#C0C0C0">\n));
    print(qq(<tr><td bgcolor="#ffffff"><b>item</b></td> <td bgcolor="#ffffff"><b>cost</b></td></tr> \n));
    while (my $record2 = $sth2->fetchrow_hashref) {
	my $cost = $record2->{cost};
	$dec_cost = sprintf "%.2f",$cost; ## 2 decimal places

	#        print(qq(<tr><td bgcolor="#ffffff">$record2->{item}</td> <td bgcolor="#ffffff">$record2->{currency}$record2->{cost}</td></tr> \n));
	print(qq(<tr><td bgcolor="#ffffff">$record2->{item}</td> <td bgcolor="#ffffff">$dec_cost</td></tr> \n));
    }
    print(qq(<tr><td></td></tr>\n));
    print(qq(</table>\n));

    print(qq(<table><tr><td height="10"></td></tr>\n));
    print(qq(</table>\n));

    my $sql = qq( SELECT * from finance LEFT JOIN agents ON finance.agent_id=agents.id
              LEFT JOIN budgets ON finance.budget_id = budgets.id WHERE finance.budget_id="$budget_id" AND finance.agent_id="$agent_id");
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    $sth->execute;
    while (my $record = $sth->fetchrow_hashref) {

	print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
	print(qq(<input type="hidden" name="action" value="prepare_expenditure">\n));
	print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
	print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
	print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
	print(qq(<input type="hidden" name="new_agent" value="$record->{fullname}">\n));
	print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
	print(qq(<table><tr><td colspan="1" height="20"><INPUT TYPE="submit" VALUE="return to edit expenditure"></td></tr>\n));
	print(qq(</FORM>\n));

	print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
	print(qq(<input type="hidden" name="action" value="compile_budget">\n));
	print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
	print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
	print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
	print(qq(<input type="hidden" name="new_agent" value="$record->{fullname}">\n));
	print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
	print(qq(<tr><td colspan="1" hegiht="10"><INPUT TYPE="submit" VALUE="go to view the budget as a whole"></td></tr>\n));
	print(qq(</td></tr></table>\n));
	print(qq(</FORM>\n));
    }

    #    print(qq(<br><br><a href="$script_name?action=prepare_expenditure&budget_id=$budget_id">add or ammend more expenditure items</a>\n));
    #   print(qq(<br><br><a href="$script_name?action=compile_budget&budget_id=$budget_id">got to view the budget as a whole</a>\n));
    print(qq(</center>\n));
    print(qq(</body>\n));
    print(qq(</html>\n));

    $dbh->disconnect;
}


############################################################################
# prepare agents

sub prepare_agents {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT * FROM agents WHERE budget_id = $budget_id ORDER BY lastname);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    my $sqlc = qq( SELECT COUNT(*) AS count FROM agents WHERE budget_id = $budget_id);
    my $sthc = $dbh->prepare($sqlc);
    $sthc->execute;

    my $sqln = qq( SELECT name FROM budgets where id = $budget_id);
    my $sthn = $dbh->prepare($sqln);
    $sthn->execute;

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="0" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));

    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>\n));

    while (my $recordn = $sthn->fetchrow_hashref) {
	print(qq(<tr><td height="10" class="medbig" colspan="5"><b> Agents: $recordn->{name}</td></tr>));
    }

    print(qq(<tr><td></td><td><b>firstname</b></td><td><b>lastname</b></td><td colspan="2"><b>codename</b></td><td></tr>\n));

    for ($i=1;$i<=50;$i++) {
	while (my $record = $sth->fetchrow_hashref) {
	    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
	    print(qq(<input type="hidden" name="action" value="edit_agent">\n));
	    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
	    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
	    print(qq(<input type="hidden" name="edit_agent_id" value="$record->{id}">\n));
	    print(qq(<input type="hidden" name="new_firstname" value="$record->{firstname}">\n));
	    print(qq(<input type="hidden" name="new_lastname" value="$record->{lastname}">\n));
	    print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
	    print(qq(<tr><td><INPUT TYPE="submit" VALUE="$i"></td>\n));
	    print(qq(<td>$record->{firstname}</td>\n));
	    print(qq(<td>$record->{lastname}</td>\n));
	    print(qq(<td>$record->{codename}</td>\n));
	    print(qq(</FORM>\n));
	    $i++
	}
    }

    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="input_agents">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));

    while (my $recordc = $sthc->fetchrow_hashref) {
	my $c = ($recordc->{count});
	for ($i=1+$c;$i<=50;$i++) {
	    print(qq(<tr><td><b>$i.</b></td>\n));
	    print(qq(<td><input type="text" name="new_firstname$i" size=20 maxsize=40></td>\n));
	    print(qq(<td><input type="text" name="new_lastname$i" size=20 maxsize=40></td>\n));
	    print(qq(<td><input type="text" name="new_codename$i" size=10 maxsize=10></td>\n));
	    #       $i++
	}
    }
    print(qq(<tr><td><INPUT TYPE="submit" VALUE="submit"></td></tr>\n));
    print(qq(</FORM>\n));
    print(qq(<tr><td><BR><BR></td></tr>\n));
}


############################################################################
# edit agent

sub edit_agent {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    # read in agent data

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="0" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));
    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>\n));
    print(qq(<tr><td height="10" class="medbig" colspan="5"><b> Edit agent</td></tr>));

    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="update_agent">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
    print(qq(<input type="hidden" name="edit_agent_id" value="$edit_agent_id">\n));

    print(qq(<td><input type="text" name="new_firstname" value="$new_firstname" size=20 maxsize=40></td>\n));
    print(qq(<td><input type="text" name="new_lastname" value="$new_lastname" size=20 maxsize=40></td>\n));
    print(qq(<td><input type="text" name="new_codename" value="$new_codename" size=10 maxsize=10></td>\n));
    print(qq(<tr><td><INPUT TYPE="submit" VALUE="submit"></td></tr>\n));
    print(qq(</FORM>\n));

    print(qq(</table>\n));

    print(qq(<table><tr><td height="10"></td></tr>\n));
    print(qq(</table>\n));

    print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="prepare_agents">\n));
    print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
    print(qq(<input type="hidden" name="new_agent" value="$record->{fullname}">\n));
    print(qq(<input type="hidden" name="new_codename" value="$record->{codename}">\n));
    print(qq(<table><tr><td colspan="1" height="20"><INPUT TYPE="submit" VALUE="return to agents overview"></td></tr>\n));
    print(qq(</FORM>\n));

    print(qq(</center>\n));
    print(qq(</body>\n));
    print(qq(</html>\n));

    $dbh->disconnect;
}

############################################################################
# update agent

sub update_agent {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = (qq{UPDATE agents set firstname='$new_firstname', lastname='$new_lastname', fullname='$new_firstname $new_lastname', codename='$new_codename' WHERE budget_id=$budget_id AND id=$edit_agent_id});
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    # read back agent info

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="0" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));
    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>\n));

    print(qq(<table><tr><td height="10"></td></tr>\n));
    print(qq(</table>\n));

    print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="prepare_agents">\n));
    print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
    print(qq(<table><tr><td colspan="1" height="20"><INPUT TYPE="submit" VALUE="return to agents overview"></td></tr>\n));
    print(qq(</FORM>\n));

    print(qq(</center>\n));
    print(qq(</body>\n));
    print(qq(</html>\n));

    $dbh->disconnect;
}


############################################################################
# input agents

sub input_agents {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    for ($i=1; $i<=50; $i++) {
	if ($new_firstname{$i} ne '') {
	    my $sql = (qq{INSERT INTO agents (budget_id, firstname, lastname, fullname, codename) VALUES ($budget_id, '$new_firstname{$i}', '$new_lastname{$i}', '$new_firstname{$i} $new_lastname{$i} ','$new_codename{$i}')});
	    #            my $sql = (qq{INSERT INTO agents (budget_id, firstname, lastname, codename) VALUES ($budget_id, '$new_firstname{i}', '$new_lastname{i}', '$new_codename{i}')});
	    my $sth = $dbh->prepare($sql);
	    $sth->execute;
	}
    }

    # read back agent info

    &print_header;
    print(qq(<center>\n));
    print(qq(<table width="700" cellspacing="0" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<tr><td><table width="700" cellspacing="5" cellpadding="2" bgcolor="#ffffff" border="0">\n));
    print(qq(<tr><td class="lge" align="center" bgcolor="#fff000" colspan="5"><font color="#000000"><b>feral budget generator</b></td></tr>\n));


    print(qq(<table><tr><td height="10"></td></tr>\n));
    print(qq(</table>\n));

    print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="prepare_agents">\n));
    print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
    print(qq(<input type="hidden" name="budget_id" value="$budget_id">\n));
    print(qq(<input type="hidden" name="agent_id" value="$agent_id">\n));
    print(qq(<table><tr><td colspan="1" height="20"><INPUT TYPE="submit" VALUE="return to agents overview"></td></tr>\n));
    print(qq(</FORM>\n));

    print(qq(</center>\n));
    print(qq(</body>\n));
    print(qq(</html>\n));

    $dbh->disconnect;
}

############################################################################
# add form

sub add_form {

    &print_header;
    print(qq(<center>\n));
    print(qq(<b> $projectname:</b> add a budget \n));

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT DISTINCT * FROM budgets ORDER BY id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    print(qq(<h3>&nbsp;</h3>\n));
    print(qq(\n));
    print(qq(<table  width="600" cellspacing="0" cellpadding="0" bgcolor="#efefef">\n));
    print(qq(<tr><td><b>Budget</b><td><b>Date</b></td><td><b></b></td></tr>));
    while (my $record = $sth->fetchrow_hashref) {
	print(qq(<tr><td>));
	print(qq(<a href="$script_name?action\=edit_form&edit_id=$record->{id}">$record->{name}</a> </td>));
	print(qq(<td>$record->{date}</td>));
	print(qq(<td></td>));
	print(qq(</tr>));
    }
    print(qq(<tr><td>&nbsp;</td></tr>\n));

    $dbh->disconnect;

    print(qq(<FORM ACTION="$script_name"METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="add_budget">\n));
    print(qq(<tr><td><b>add a new budget</b><br>\n));
    print(qq(<input type="text" name="new_name" size=60 maxsize=80><br></td>\n));
    print(qq(<td><b>date</b><br>));

    print(qq(<td><b></b><br>\n));

    print(qq(<tr><td>&nbsp;</td></tr>\n));
    #print(qq(<tr><td><b>password</b></td></tr>\n));
    #print(qq(<td><input type="password" name="password" size=10 value=""maxsize=20></td></tr>\n));
    print(qq(<tr><td><br><INPUT TYPE="submit" VALUE="add"></td></tr>\n));
    print(qq(</FORM>\n));
    print(qq(</table>\n));

    &print_footer;

    $dbh->disconnect;
}


###############################################################################
# add budget

sub add_budget {

    #if ($password eq $admin_password) {

    ### test if workshop exists already in database
    if ($new_name ne '') {

	$new_name_found='false';
	my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	    or die ("can't connect: $DBI::errstr");

	my $sql = qq( SELECT DISTINCT name FROM budgets);
	my $sth = $dbh->prepare($sql);
	$sth->execute;

	while (my $record = $sth->fetchrow_hashref) {
	    if ($record->{name} eq $new_name) {
		$new_name_found='true';
	    }
	}

	if ($new_name_found eq 'false') {

	    my $sql = (qq{INSERT INTO budgets (name) VALUES ('$new_name')});
	    my $sth = $dbh->prepare($sql);
	    $sth->execute;

	    my $sql2 = (qq{ SELECT * from budgets where name='$new_name'});
	    my $sth2 = $dbh->prepare($sql2);
	    $sth2->execute;

	    $dbh->disconnect;

	    &print_header;

	    while (my $record2 = $sth2->fetchrow_hashref) {
		print(qq(<center>\n));
		print(qq(<table  width="800" cellspacing="0" cellpadding="2" bgcolor="#efefef">\n));
		print(qq(<tr><td>budget added <a href="$script_name?action=edit_form&edit_id=$record2->{id}">$new_name</a></td></tr>\n));
		print(qq(</table>\n));
	    }
	    &print_footer;

	} else {
	    &print_header;
	    print(qq(duplicate budget entry<br>\n));
	    &print_footer;
	}
    } else {
	&print_header;
	print(qq(no budget entered<br>\n));
	&print_footer;
    }

    #} else {

    #&print_header;
    #print(qq(<h3>Wrong password !</h3><p>\n));
    #&print_footer;

    #    }
}


###############################################################################
# add budget information

sub add_budget_information {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");
    my $sql = (qq{UPDATE budgets SET location='$new_location', deliver_date='$new_deliver_date', remarks='$new_remarks',
              WHERE name='$new_name'});
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    $dbh->disconnect;
    &print_header;

    print(qq(<center>\n));
    print(qq(<b> $projectname:</b> $new_name added ));
    #print(qq($add_information \n));
    print(qq(<p><a href="$script_name?action=display_budgets">view all budgets</a></p>\n));
    print(qq(<p><a href="$script_name"></a></p>\n));

    &print_footer;
}

###############################################################################
# edit select form: select which budget to edit

sub edit_select_form {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT DISTINCT * FROM budgets ORDER BY id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    &print_header;

    print(qq(<center>\n));
    print(qq(<b> edit a budget <br> \n));
    print(qq(<table  width="500" cellspacing="0" cellpadding="0" bgcolor="#efefef">\n));
    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="edit_form">\n));
    while (my $record = $sth->fetchrow_hashref) {
	print(qq(<tr><td><input type="radio" name="edit_id" value="$record->{id}"</td> \n));
	print(qq( <td>$record->{name}\n));
    }

    print(qq(<tr><td colspan="2"><INPUT TYPE="submit" VALUE="edit budget"></td></tr>\n));
    print(qq(</FORM>\n));
    print(qq(</table>\n));

    $dbh->disconnect;

    &print_footer;

}

###############################################################################
# edit form: edit selected budget

sub edit_form {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT DISTINCT * FROM budgets WHERE id=$edit_id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    &print_header;

    print(qq(<center>\n));

    print(qq(<h3> $projectname: edit budget</h3>\n));
    #   print(qq(<img src="$image_url/thumb/$edit_id.jpg" width="100"border=0" alt="">\n));

    print(qq(<table width="800" cellspacing="0" cellpadding="2" bgcolor="#efefef">\n));
    print(qq(<tr><td bgcolor="#ffffff">&nbsp;</td></tr>\n));
    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="edit_budget">\n));
    print(qq(<input type="hidden" name="edit_id" value="$edit_id">\n));

    while (my $record = $sth->fetchrow_hashref) {

	print(qq(<tr><td class="mini"><i></i> <input type="text" name="new_name" value="$record->{name}"size=50 maxsize=100></td></tr>\n));
	print(qq(<tr><td class="mini"><input type="text" name="new_location" value="$record->{location}"size=50 maxsize=100> location</td></tr>\n));
	print(qq(<tr><td class="mini"><input type="text" name="new_url" value="$record->{url}"size=100 maxsize=100> url</td></tr>\n));

	print(qq(<tr><td><b><a href="$script_name?action=edit_field&edit_id=$edit_id&edit_field=remarks">Remarks:</a></b> When, where, who</td></tr>\n));
	print(qq(<tr><td class=""><TEXTAREA name="new_remarks" ROWS="16" COLS="120" WRAP=SOFT>$record->{remarks}</TEXTAREA></td></tr>\n));

	print(qq(<tr><td><b></b></td></tr>\n));
	#   print(qq(<tr><td><i>password</i></td></tr><tr><td><input type="password" name="password" size=10 value=""maxsize=20></td></tr>\n));
	print(qq(<tr><td><INPUT TYPE="submit" VALUE="edit budget"></td></tr>\n));
	print(qq(</FORM>\n));
	print(qq(</table>\n));
	print(qq(</table>\n));

    }
    &print_footer;
}

###############################################################################
# present budget: present singlebudget

sub present_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = qq( SELECT DISTINCT * FROM budgets WHERE id=$edit_id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    &print_header;

    #   print(qq(<center>\n));

    print(qq(<h3> $projectname: budget notes</h3>\n));
    #   print(qq(<img src="$image_url/thumb/$edit_id.jpg" width="100"border=0" alt="">\n));

    print(qq(<table width="1000" cellspacing="20" cellpadding="0" bgcolor="#ffffff">\n));
    print(qq(<input type="hidden" name="edit_id" value="$edit_id">\n));

    while (my $record = $sth->fetchrow_hashref) {

	$record->{remarks} =~ s/\n/\<br>/g;

	print(qq(<tr><td bgcolor="fff000" class="max"><i></i>$record->{name}, \n));
	print(qq($record->{location}</td><td>&nbsp;</td></tr>\n));
	print(qq(<tr><td class="med"><a href="$record->{url}">$record->{url}</a></td></tr>\n));
	#   print(qq(<tr><td height="20"><b></b></td></tr>\n));

	print(qq(<tr><td class="max"><b>Remarks:</b> When, where, who</td></tr>\n));
	print(qq(<tr><td class="med">$record->{remarks}</td></tr>\n));
	#   print(qq(<tr><td height="20"><b></b></td></tr>\n));


	#   print(qq(<tr><td><i>password</i></td></tr><tr><td><input type="password" name="password" size=10 value=""maxsize=20></td></tr>\n));
	print(qq(<tr><td></td></tr>\n));
	print(qq(</table>\n));
	print(qq(</table>\n));

    }
    &print_footer;
}


###############################################################################
# edit budget: edit budget results

sub edit_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    #if ($password eq $admin_password) {

    &print_header;

    ### update budgets table

    my $sql_update = (qq{UPDATE budgets SET name='$new_name', location='$new_location', url='$new_url', remarks='$new_remarks', deliver_date="$new_deliver_date" WHERE id='$edit_id'});
    my $sth_update = $dbh->prepare($sql_update);
    $sth_update->execute;


    ### display results

    my $sql = qq( SELECT * FROM budgets WHERE id = '$edit_id');
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    while (my $record = $sth->fetchrow_hashref) {

	print(qq(<center>\n));
	#       print(qq(<a href="$image_url$edit_id.jpg" border="0"><img src="$image_url/thumb/$edit_id.jpg" width="100"border=0" alt=""></a>\n));
	print(qq(<table  width="300" cellspacing="0" cellpadding="2" bgcolor="#efefef">\n));
	print(qq(<tr><td bgcolor="#ffffff">updated</td></tr>\n));
	print(qq(<tr><td><a href="$script_name?action=edit_form&edit_id=$edit_id">$record->{name}</a></td></tr>\n));
	print(qq(</table><br>));
    }

    $dbh->disconnect;
    &print_footer;

}

###############################################################################
# delete form: select a budget to delete

sub delete_form {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");
    my $sql = qq( SELECT DISTINCT * FROM budgets ORDER BY id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    &print_header;

    print(qq(<center>\n));
    print(qq(<b> delete a budget <br> \n));
    print(qq(<table  width="800" cellspacing="0" cellpadding="0" bgcolor="#efefef">\n));
    print(qq(<FORM ACTION="$script_name" METHOD="GET">\n));
    print(qq(<input type="hidden" name="action" value="delete_budget">\n));

    while (my $record = $sth->fetchrow_hashref) {
	#   print(qq(<input type="hidden" name="new_name" value="$record->{name}">\n));
	print(qq(<tr><td><input type="radio" name="delete_id" value="$record->{id}"</td> \n));
	print(qq( <td>$record->{name}\n));
	print(qq($record->{location}\n));
    }

    print(qq(<tr><td><b>password</b></td><td><input type="password" name="password" size=10 value=""maxsize=20></td></tr>\n));
    print(qq(<tr><td colspan="2"><INPUT TYPE="submit" VALUE="delete budget"></td></tr>\n));
    print(qq(</FORM>\n));
    print(qq(</table>\n));

    $dbh->disconnect;
    &print_footer;
}

###############################################################################
# delete budget: budget delete results

sub delete_budget {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");

    my $sql = (qq{SELECT * FROM budgets WHERE id = '$delete_id'});
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    my $sql2 = (qq{DELETE FROM budgets WHERE id = '$delete_id'});
    my $sth2 = $dbh->prepare($sql2);
    $sth2->execute;

    while (my $record = $sth->fetchrow_hashref) {

	&print_header;

	print(qq($record->{name} deleted\n));

    }

    &print_footer;
    $dbh->disconnect;

}

###############################################################################
# display budget: displays all budgets

sub display_budgets {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");
    my $sql = qq( SELECT DISTINCT * FROM budgets ORDER BY id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    &print_header;

    print(qq(<center>\n));
    print(qq(<h3> $projectname: all budgets: edit mode</h3>\n));

    print(qq(<table width="1000" cellspacing="2" cellpadding="2" bgcolor="#ffffff"">\n));
    print(qq(<tr><td bgcolor="#ffffff"><b>Budget</b></td><td bgcolor="#ffffff"><b>Location</b></td><td bgcolor="#ffffff"><b>Date</b></td><td bgcolor="#ffffff"><b>URL</b></td></tr>\n));
    while (my $record = $sth->fetchrow_hashref) {
	print(qq(<tr>\n));
	print(qq(<td bgcolor="#efefef"><a href="$script_name?action\=edit_form&edit_id=$record->{id}">$record->{name}</a></td>));
	print(qq(<td bgcolor="#efefef">$record->{location}</td>));
	print(qq(<td bgcolor="#efefef">$record->{deliver_date}</td>));
	print(qq(<td bgcolor="#efefef"><a href="$record->{url}">$record->{url}</a></td>));
	#   print(qq(<td bgcolor="#efefef"><img src="$image_url/thumb/$record->{id}.jpg" width="100"border="0" title="edit $record->{name}" alt=""></td>));
	print(qq(</td></tr>\n));
    }

    $dbh->disconnect;

    print(qq(</table>\n));
    &print_footer;
}


###############################################################################
# list budgets: list all budgets, non-edit mode

sub list_budgets {

    my $dbh = DBI->connect($db_conect,$db_user,$db_pass)
	or die ("can't connect: $DBI::errstr");
    my $sql = qq( SELECT DISTINCT * FROM budgets ORDER BY id);
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    &print_header;

    print(qq(<center>\n));
    print(qq(<h3> $projectname: all budgets: read-only</h3>\n));

    print(qq(<table width="1000" cellspacing="2" cellpadding="2" bgcolor="#ffffff"">\n));
    print(qq(<tr><td bgcolor="#ffffff"><b>Budget</b></td><td bgcolor="#ffffff"><b>Location</b></td><td bgcolor="#ffffff"><b>Date</b></td><td bgcolor="#ffffff"><b>URL</b></td></tr>\n));
    while (my $record = $sth->fetchrow_hashref) {
	print(qq(<tr>\n));
	print(qq(<td bgcolor="#efefef"><a href="$script_name?action\=present_budget&edit_id=$record->{id}">$record->{name}</a></td>));
	print(qq(<td bgcolor="#efefef">$record->{location}</td>));
	print(qq(<td bgcolor="#efefef">$record->{deliver_date}</td>));
	print(qq(<td bgcolor="#efefef"><a href="$record->{url}">$record->{url}</a></td>));
	#   print(qq(<td bgcolor="#efefef"><img src="$image_url/thumb/$record->{id}.jpg" width="100"border="0" title="edit $record->{name}" alt=""></td>));
	print(qq(</td></tr>\n));
    }

    $dbh->disconnect;

    print(qq(</table>\n));

    &print_footer;
}

##############################################################################################
# select start date subroutine

sub select_start_date {

    ### day
    print(qq(<td colspan="2"><select name="new_start_day">\n));
    for ( $i=1; $i<10; $i+=1) {
	print(qq(<option));
	if ($new_start_day eq "0$i") {
	    print(qq( selected));
	}
	if (($new_start_day eq "") && ($currentdayofmonth eq $i)) {
	    print(qq( selected));
	}
	print(qq(>0$i</option>\n));
    }
    for ( $i=10; $i<32; $i+=1) {
	print(qq(<option));
	if ($new_start_day eq $i) {
	    print(qq( selected));
	}
	if (($new_start_day eq "") && ($currentdayofmonth eq $i)) {
	    print(qq( selected));
	}
	print(qq(>$i</option>\n));
    }
    print(qq(</select>\n));

    ### month
    print(qq(<select name="new_start_month">\n));
    for ( $j=1; $j<10; $j+=1) {
	print(qq(<option));
	if ($new_start_month eq "0$j") {
	    print(qq( selected));
	}
	if (($new_start_month eq "") && ($currentmonth eq $j)) {
	    print(qq( selected));
	}
	print(qq(>0$j</option>\n));
    }
    for ( $j=10; $j<13; $j+=1) {
	print(qq(<option));
	if ($new_start_month eq $j) {
	    print(qq( selected));
	}
	if (($new_start_month eq "") && ($currentmonth eq $j)) {
	    print(qq( selected));
	}
	print(qq(>$j</option>\n));
    }
    print(qq(</select>\n));

    ### year
    print(qq(<select name="new_start_year">\n));
    for ( $j=2003; $j<2022; $j+=1) {
	print(qq(<option));
	if ($new_start_year eq $j) {
	    print(qq( selected));
	}
	if (($new_start_year eq "") && ($currentyear eq $j)) {
	    print(qq( selected));
	}
	print(qq(>$j</option>\n));
    }
    print(qq(</select></td></tr>\n));
}


##############################################################################
#                                                                            #
# printing and formatting                                                    #
#                                                                            #
##############################################################################


##  print a title

sub print_title {
    my $string = shift;
    print <<END;
	<div class="title">$string</div>
END
}

##  print a subtitle

sub print_subtitle {
    my $string = shift;
    print <<END;
	<div class="subtitle">$string</div>
END
}

##  print a budget subtotal line with a description and optional amount

sub print_subtotal {
    my $string = shift;
    my $amount = shift;
    if (not $amount) {$amount = "";}
    print <<END;
    		<div class="budget-subtotal">
		    <p class="subtotal-name">$string</p>
		    <p class="subtotal-amount">$amount</p>
		</div>
END
}

##  print financial budget item with a description and optional amount

sub print_item {
    my $string = shift;
    my $amount = shift;
    if (not $amount) {$amount = "";}
    print <<END;
		<div class="budget-item">
		    <p class="item-name">$string</p>
		    <p class="item-amount">$amount</p>
		</div>
END
}

##  print nonfinancial budget item with a description only

sub print_item_nf {
    my $string = shift;
    print <<END;
		<div class="budget-item">
		    <p class="item-non-financial">$string</p>
		</div>
END
}


##  print the page header

sub print_header {
    print <<END;
Content-type: text/html

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width">
        <title>feral budget generator</title>
        <link rel="stylesheet" href="$css_screen_url">
        <link rel="stylesheet" href="$css_print_url" media="print">
    </head>
    <body>
END
}


##  print a standard footer

sub print_footer {
    print <<END;
 </body>
</html>
END
}


## print the admin footer

sub adminfooter {

    print(qq(<tr><td bgcolor="ffffff" colspan="4"><br><a href="$script_name?action\=display_budgets">all budgets: edit</a></td></tr>\n));
    print(qq(<tr><td bgcolor="ffffff" colspan="4"><br><a href="$script_name?action\=list_budgets">all budgets: list</a></td></tr>\n));
    print(qq(<tr><td bgcolor="ffffff" colspan="4"><br><a href="$script_name?action\=add_form">add a budget</a> \&nbsp\; \|\n));
    print(qq(<a href="$script_name?action\=delete_form">delete a budget</a> \&nbsp\; \n));

    print(qq(</center>\n));
    print(qq(</body>\n));
    print(qq(</html>\n));

}