#!/usr/bin/perl -w

use CGI;
use CGI::Carp('fatalsToBrowser');
use DBI;
use strict;
#require "workorder_common.pl";

my $ip = $ENV{'REMOTE_ADDR'};

my $dbh    = "";
my $q      = CGI->new();
my $id     = $q->param('id');
my $action = $q->param('action');
my $skip   = $q->param('skip');
my $sort   = $q->param('sort');
   $sort   = "" unless $sort;
my $sort2;

# Sanitize input
$id   =~ s/[^0-9]//g;  # nix anything not a number
$skip =~ s/[^0-9]//g;  # nix anything not a number
$skip = 0 unless $skip;

db_connect();

if($action eq "edit"){
	header();
	edit_item($id);
	footer();

}elsif($action eq "commit"){
	commit_item();

}elsif($action eq "delete"){
	delete_item($id);

}else{
	$action = "list";
	header();
	list_items();
	footer();
}



#----------------------------------------------------------------------------------
sub list_items {

  my ($id, $name) = "";
  my $statement = "SELECT id, name FROM location";

  # sort clause
  if($sort){ 
	if($sort =~ /_desc$/){
		$sort2 = $sort;
		$sort2 =~ s/_desc$//;
		$statement .= " ORDER BY $sort2 DESC"; 
	}else{
		$statement .= " ORDER BY $sort"; 
	}
  }

  #------------------------------------------------
  # Paging through rows
  #------------------------------------------------
  # Get a count of the total records, for paging links
  my ($counter) = $dbh->selectrow_array("SELECT count(*) FROM location");

  my $rows_per_page = 20;
  my $skip_next     = $skip + $rows_per_page;
  my $skip_prev     = 0;
  if($skip > $rows_per_page){ $skip_prev = $skip - $rows_per_page; }

  my $url_next = "";
  my $url_prev = "";

  # Skip records - don't skip more than what exists, right?
  if($rows_per_page >= $counter){
	# We reached the end already
	$url_next   = "";
	$url_prev   = "";

  }elsif($skip==0){

	$url_prev   = "";  # no previous recs exist becuz we're at the beginning
	$url_next   = "<a href='location.pl?action=list&sort=$sort&skip=$skip_next'> More >> </a>";

  }elsif($skip + $rows_per_page >= $counter){
	# We reached the end already
	$url_next   = "";
	$url_prev   = "<a href='location.pl?action=list&sort=$sort&skip=$skip_prev'> << More</a>";

  }elsif($skip < $counter){
	# Everything's beautiful, baby -- the world is orderly
	$url_next   = "<a href='location.pl?action=list&sort=$sort&skip=$skip_next'> More >> </a>";
	$url_prev   = "<a href='location.pl?action=list&sort=$sort&skip=$skip_prev'> << More</a>";
  }
  $statement .= " LIMIT $skip,$rows_per_page";

  #die $statement;
  my $sth = $dbh->prepare($statement);
  $sth->execute;

  my $td_class = "tablerow1";
  my $tableheader = <<TABLEHEADER;
<table CELLSPACING=1 CELLPADDING=5 BORDER=0>

<tr>
	<td class=tableheader><a href='location.pl?action=list&sort=id'>id</a></td>
	<td class=tableheader><a href='location.pl?action=list&sort=name'>Name</a></td>
</tr>

TABLEHEADER
  if($sort) { $tableheader =~ s/(sort=$sort)/$1_desc/; }
  print $tableheader;

  while ( ($id, $name) = $sth->fetchrow_array){
	print <<DONE;

		
<tr>
	<td nowrap class=$td_class><a href='location.pl?action=edit&id=$id'>$id</a></td>
	<td nowrap class=$td_class>$name</td>
</tr>

DONE

	  if($td_class eq "tablerow1"){ $td_class = "tablerow2"; }else { $td_class = "tablerow1"; }

  }
  print "</table>\n";
  print <<SKIP;
<table width=100%>
<tr>
  <td>$url_prev</td>
  <td align=right>$url_next</td>
</tr>
</table>
SKIP


}
#----------------------------------------------------------------------------------
sub edit_item {

  my $this_id = shift;
  my $statement = "SELECT id, name FROM location WHERE id=$this_id";

  my ($id, $name);

  if($this_id > 0){

	  ($id, $name) = $dbh->selectrow_array($statement);
  }

	
	
	my $edit_form = <<DONE;
	
<form name="editform" action="location.pl" method="post" onSubmit="return checkData()">
<input type="hidden" name="action" value="commit">
<table cellspacing="1" cellpadding="5" border="0"><tr>
	<td align="right" class="tablerow1"><b>id</b></td>
	<td valign="top" class="tablerow1"><input type="hidden" name="id" value="$id">$id</td>

<tr>
	<td align="right" class="tablerow2"><b>Name</b></td>
	<td valign="top" class="tablerow2"><input type="text" name="name" size="30" value="$name"></td>

<tr><td></td><td><input type="submit" name="Submit"></td></tr>
</table>
</form>
	
DONE
print $edit_form;
print qq[<p><a href="location.pl?action=delete&id=$this_id">Delete</a></p>\n] if $this_id > 0;  

}
#----------------------------------------------------------------------------------
sub commit_item {

my $name                  = $q->param('name'); $name = $dbh->quote($name);


  my $statement;
  if($id > 0){
	$statement = "UPDATE location SET 
name                  = $name
 WHERE id=$id";
  }else{
	$statement = "INSERT INTO location (name) VALUES ($name)";
  }

  #die $statement;
  $dbh->do($statement);
  print $q->redirect("location.pl?action=list");


}
#----------------------------------------------------------------------------------
sub delete_item {

  my $id = shift;
  $dbh->do("DELETE FROM location WHERE id=$id");
  print $q->redirect("location.pl?action=list");

}

#SELECT_SUBS#
#LOOKUP_SUBS#


#--------------------------------------------------------------------------------
sub header {

  print <<DONE;
Content-type: text/html

<html><head>
<style type="text/css">

body {background-color : #ffffff; font-family: helvetica, arial,sans-serif; }
p    { font-size: 12px; }
h1   { color:#999999; font-size: 18px;}
h2   { color:#777777; font-size: 16px;}
h3   { color:#000000; font-size: 12px;}

a         {  }
a:link    { color: #000099; text-decoration:none;}
a:visited { color: #000099; }
a:hover   { color: #0000FF; text-decoration:underline;}
a:active  { color: #0000FF; }

td { font-size: 12px; }

.tableheader   { background-color: #dddddd; vertical-align: top;  font-weight: bold; white-space: nowrap; }
.tableheader a { color: #000000; text-decoration:none; }
.tableheader a:hover { text-decoration:underline; }
.tablerow1     { background-color: #e0e0e0; vertical-align: top; }
.tablerow2     { background-color: #eeeeee; vertical-align: top; }

</style>

<script language="JavaScript">
<!--

function checkData (){
	// Put any client-side validation routines in here
	//if(document.addform.name.value.length==0){
	//	alert("You must enter a name.");
	//	return false;
	//}
}
//-->
</script>

<title>workorder - location</title>

</head>

<body>
<h1>workorder - location</h1>

<p>
<a href="../">Home</a> |
<a href="location.pl?action=list">List All Locations</a> |
<a href="location.pl?action=edit">Add New Location</a>
</p>
DONE
}



#----------------------------------------------------------------------------------
sub footer {
  print "</body></html>";

}
#----------------------------------------------------------------------------------
sub db_connect {
  $dbh = DBI->connect('DBI:mysql:workorder', 'workorder', 'workorder', {PrintError => 0,RaiseError => 1});

}

