#!/usr/bin/perl
print "Content-type: text/html\n\n";
$long = $ENV{'QUERY_STRING'};
if ($long) {
@pairs=split(/&/,$long);
foreach $item(@pairs) {
($name,$content)=split (/=/,$item,2);
$content=~tr/+/ /;
$content=~ s/%(..)/pack("c",hex($1))/ge;
if ($INPUT{$name}) { $INPUT{$name} = $INPUT{$name}.",".$content; }
else { $INPUT{$name} = $content; }
}
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
if ($INPUT{$name}) { $INPUT{$name} = $INPUT{$name}.",".$value; }
else { $INPUT{$name} = $value; }
}
}
if ($INPUT{action} eq "addclient") { &addclient; }
elsif ($INPUT{action} eq "showclients") { &showclients; }
elsif ($INPUT{action} eq "clientsearch") { &clientsearch; }
elsif ($INPUT{action} eq "doaddclient") { &doaddclient; }
elsif ($INPUT{action} eq "editclient") { &editclient; }
elsif ($INPUT{action} eq "viewclient") { &viewclient; }
elsif ($INPUT{action} eq "doeditclient") { &doeditclient; }
elsif ($INPUT{action} eq "addproperty") { &addproperty; }
elsif ($INPUT{action} eq "addpropertyset") { &addpropertyset; }
elsif ($INPUT{action} eq "doaddproperty") { &doaddproperty; }
elsif ($INPUT{action} eq "editproperty") { &editproperty; }
elsif ($INPUT{action} eq "doeditproperty") { &doeditproperty; }
elsif ($INPUT{action} eq "viewproperty") { &viewproperty; }
elsif ($INPUT{action} eq "showproperties") { &showproperties; }
elsif ($INPUT{action} eq "logwork") { &logwork; }
elsif ($INPUT{action} eq "logworkset") { &logworkset; }
elsif ($INPUT{action} eq "dologworkset") { &dologworkset; }
elsif ($INPUT{action} eq "logworkview") { &logworkview; }
elsif ($INPUT{action} eq "logwork_edit") { &logwork_edit; }
elsif ($INPUT{action} eq "createinvoice") { &createinvoice; }
elsif ($INPUT{action} eq "createinvoiceset") { &createinvoiceset; }
elsif ($INPUT{action} eq "readnewinvoice") { &readnewinvoice; }
elsif ($INPUT{action} eq "viewinvoice") { &viewinvoice; }
elsif ($INPUT{action} eq "viewinvoiceryan") { &viewinvoiceryan; }
elsif ($INPUT{action} eq "viewinvoicerange") { &viewinvoicerange; }
elsif ($INPUT{action} eq "printrange") { &printrange; }
elsif ($INPUT{action} eq "searchinvoices") { &searchinvoices; }
elsif ($INPUT{action} eq "changeinvoice") { &changeinvoice; }
elsif ($INPUT{action} eq "whileloop") { &whileloop; }
elsif ($INPUT{action} eq "whileprint") { &whileprint; }
elsif ($INPUT{action} eq "invoicetotals") { &invoicetotals; }
else { &showclients; }
## ADDED 1-4-2014 RPO
sub logwork_edit {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
if ($INPUT{doedit} eq "yes") {
my $kasth = $dbh->do("update reporting set worktype = '$INPUT{worktype}', date = '$INPUT{date}', price = '$INPUT{price}', notes = '$INPUT{notes}' WHERE reportid = '$INPUT{reportid}'");
$dbh->disconnect;
$printmessage = "Updated Success!";
&logworkview;
exit 0;
}
if ($INPUT{dodelete} eq "yes") {
my $kasth = $dbh->do("DELETE from reporting WHERE reportid = '$INPUT{reportid}'");
$dbh->disconnect;
$printmessage = "Updated Success!";
&logworkview;
exit 0;
}
print qq~
Client Management System
~;
&header;
print qq~
View Pending Logged Completed Work (Not Invoiced) Edit The Following File
~;
my $casth = $dbh->prepare("select description from pulldowns where type = 'worktype'");
$casth->execute;
my $aasth = $dbh->prepare("select reportid,propertyid,clientid,status,worktype,date,price,notes from reporting WHERE status = 'pending' AND reportid = '$INPUT{reportid}' order by reportid DESC");
$aasth->execute;
while (($reportid,$propertyid,$clientid,$status,$worktype,$date,$price,$notes) = $aasth->fetchrow_array()) {
print qq~
~;
}
print qq~
~;
&footer;
### END SUB
}
sub logworkview {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
print qq~
Client Management System
~;
&header;
print qq~
View Pending Logged Completed Work (Not Invoiced)
Report ID Property ID Client ID Status Worktype Date Price Notes
~;
$row_count = "1";
my $aasth = $dbh->prepare("select reportid,propertyid,clientid,status,worktype,date,price,notes from reporting WHERE status = 'pending' order by reportid DESC");
$aasth->execute;
while (($reportid,$propertyid,$clientid,$status,$worktype,$date,$price,$notes) = $aasth->fetchrow_array()) {
if ($row_count eq "2") {
$showbgcolor = "FFFF99";
$row_count = "1";
}
else {
$showbgcolor = "white";
$row_count++;
}
print qq~
$reportid $propertyid $clientid $status $worktype $date $price $notes Edit
~;
}
print qq~
~;
&footer;
### END SUB
}
sub printrange {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select invoiceid from invoices order by invoiceid DESC");
$aasth->execute;
while (($invoiceids) = $aasth->fetchrow_array()) {
$printinvoiceids .= "$invoiceids \n";
}
print <<"STARTOFTHEPAGE";
Lawn Management System
STARTOFTHEPAGE
&header;
print qq~
Print All Invoices by Range
Select Range to Print Below
~;
&footer;
}
sub invoicetotals {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
if ($INPUT{the_year}) {
$the_year = "$INPUT{the_year}";
$the_year_display = "20$the_year";
$the_year_dd = "$the_year_display ";
}
else {
$the_year = "16";
$the_year_display = "20$the_year";
$the_year_dd = "2016 ";
}
my $aasth = $dbh->prepare("select totalprice,STATUS from invoices where STATUS = 'pending' AND invoicedate like '%$the_year'");
$aasth->execute;
my $basth = $dbh->prepare("select totalprice,STATUS from invoices where STATUS = 'paid' AND invoicedate like '%$the_year'");
$basth->execute;
my $casth = $dbh->prepare("select price,status from reporting where status = 'pending'");
$casth->execute;
print <<"STARTOFTHEPAGE";
Lawn Management System
STARTOFTHEPAGE
&header;
print qq~
Lawn Profits for $the_year_display
Total Pending Invoices for $the_year_display
~;
while (($totalprice,$STATUS) = $aasth->fetchrow_array()) {
$itotalprice = ($itotalprice+$totalprice);
}
$itotalprice = sprintf("%.2f",$itotalprice);
$dbh->disconnect;
print qq~
\$$itotalprice
Total Uninvoiced Pending Logged Work - All Years
~;
while (($utotalprice,$uSTATUS) = $casth->fetchrow_array()) {
$iutotalprice = ($iutotalprice+$utotalprice);
}
$iutotalprice = sprintf("%.2f",$iutotalprice);
$dbh->disconnect;
print qq~
\$$iutotalprice
Total Paid Invoices for $the_year_display
~;
while (($ctotalprice,$cSTATUS) = $basth->fetchrow_array()) {
$ictotalprice = ($ictotalprice+$ctotalprice);
}
$ictotalprice = sprintf("%.2f",$ictotalprice);
$dbh->disconnect;
print qq~
\$$ictotalprice
~;
&footer;
}
sub whileprint {
$counter = 0;
while ($counter < 70) {
print "Line #$counter Completed on 8/6/03Client Management System
STARTOFTHEPAGE
&header;
print qq~
Showing All $INPUT{status} Invoices Mark As PAID -- ";
}
elsif ($INPUT{status} eq "paid") {
$markaspaid = "Archive Invoice -- ";
}
elsif ($INPUT{status} eq "archive") {
$markaspaid = "Archived -- ";
}
else {
$markaspaid = "None";
}
print qq~
$markaspaidINVOICE $invoiceid - $invoicedate - $billtoname - \$$totalprice
~;
&footer;
}
sub changeinvoice {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $kasth = $dbh->do("update invoices set status = '$INPUT{type}' WHERE invoiceid = '$INPUT{invoiceid}'");
$dbh->disconnect;
$printmessage = "Updated Success!";
&searchinvoices;
}
sub viewinvoice {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from invoices where invoiceid = '$INPUT{invoiceid}' AND status = '$INPUT{status}'");
$aasth->execute;
while (($invoiceid,$propertyid,$clientid,$invoicedate,$billtoname,$billtoaddress,$billtoaddress2,$billtocity,$billtostate,$billtozip,$worktype_1,$address_1,$date_1,$price_1,$notes_1,$worktype_2,$address_2,$date_2,$price_2,$notes_2,$worktype_3,$address_3,$date_3,$price_3,$notes_3,$worktype_4,$address_4,$date_4,$price_4,$notes_4,$worktype_5,$address_5,$date_5,$price_5,$notes_5,$worktype_6,$address_6,$date_6,$price_6,$notes_6,$worktype_7,$address_7,$date_7,$price_7,$notes_7,$worktype_8,$address_8,$date_8,$price_8,$notes_8,$totalprice,$status) = $aasth->fetchrow_array()) {
if ($notes_1) {
$notes_1 = "( $notes_1 )";
}
if ($notes_2) {
$notes_2 = "( $notes_2 )";
}
if ($notes_3) {
$notes_3 = "( $notes_3 )";
}
if ($notes_4) {
$notes_4 = "( $notes_4 )";
}
if ($notes_5) {
$notes_5 = "( $notes_5 )";
}
if ($notes_6) {
$notes_6 = "( $notes_6 )";
}
if ($notes_7) {
$notes_7 = "( $notes_7 )";
}
if ($notes_8) {
$notes_8 = "( $notes_8 )";
}
if ($price_1) {
$price_1 = "\$$price_1 \$$price_2 \$$price_3 \$$price_4 \$$price_5 \$$price_6 \$$price_7 \$$price_8 $worktype_1 $notes_1";
}
if ($worktype_2) {
$worktype_2 = "---> $worktype_2 $notes_2";
}
if ($worktype_3) {
$worktype_3 = "---> $worktype_3 $notes_3";
}
if ($worktype_4) {
$worktype_4 = "---> $worktype_4 $notes_4";
}
if ($worktype_5) {
$worktype_5 = "---> $worktype_5 $notes_5";
}
if ($worktype_6) {
$worktype_6 = "---> $worktype_6 $notes_6";
}
if ($worktype_7) {
$worktype_7 = "---> $worktype_7 $notes_7";
}
if ($worktype_8) {
$worktype_8 = "---> $worktype_8 $notes_8";
}
if ($address_1) {
$address_1 = "Service Performed At $address_1";
}
if ($address_2) {
$address_2 = "Service Performed At $address_2";
}
if ($address_3) {
$address_3 = "Service Performed At $address_3";
}
if ($address_4) {
$address_4 = "Service Performed At $address_4";
}
if ($address_5) {
$address_5 = "Service Performed At $address_5";
}
if ($address_6) {
$address_6 = "Service Performed At $address_6";
}
if ($address_7) {
$address_7 = "Service Performed At $address_7";
}
if ($address_8) {
$address_8 = "Service Performed At $address_8";
}
print qq~
Services Invoice
7101 S. Cowan Road
Muncie, Indiana 47302
Phone 765-808-0990
DATE: $invoicedate
INVOICE # $invoiceid
Bill To:
$billtoname
DESCRIPTION
AMOUNT
$address_1 $date_1
$price_1
TOTAL
\$$totalprice
Please make all checks payable to John Jett Jr.
Thank you for your business!
~;
}
}
sub viewinvoiceryan {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from invoices where invoiceid = '$INPUT{invoiceid}' AND status = '$INPUT{status}'");
$aasth->execute;
while (($invoiceid,$propertyid,$clientid,$invoicedate,$billtoname,$billtoaddress,$billtoaddress2,$billtocity,$billtostate,$billtozip,$worktype_1,$address_1,$date_1,$price_1,$notes_1,$worktype_2,$address_2,$date_2,$price_2,$notes_2,$worktype_3,$address_3,$date_3,$price_3,$notes_3,$worktype_4,$address_4,$date_4,$price_4,$notes_4,$worktype_5,$address_5,$date_5,$price_5,$notes_5,$worktype_6,$address_6,$date_6,$price_6,$notes_6,$worktype_7,$address_7,$date_7,$price_7,$notes_7,$worktype_8,$address_8,$date_8,$price_8,$notes_8,$totalprice,$status) = $aasth->fetchrow_array()) {
if ($notes_1) {
$notes_1 = "( $notes_1 )";
}
if ($notes_2) {
$notes_2 = "( $notes_2 )";
}
if ($notes_3) {
$notes_3 = "( $notes_3 )";
}
if ($notes_4) {
$notes_4 = "( $notes_4 )";
}
if ($notes_5) {
$notes_5 = "( $notes_5 )";
}
if ($notes_6) {
$notes_6 = "( $notes_6 )";
}
if ($notes_7) {
$notes_7 = "( $notes_7 )";
}
if ($notes_8) {
$notes_8 = "( $notes_8 )";
}
if ($price_1) {
$price_1 = "\$$price_1 \$$price_2 \$$price_3 \$$price_4 \$$price_5 \$$price_6 \$$price_7 \$$price_8 $worktype_1 $notes_1";
}
if ($worktype_2) {
$worktype_2 = "---> $worktype_2 $notes_2";
}
if ($worktype_3) {
$worktype_3 = "---> $worktype_3 $notes_3";
}
if ($worktype_4) {
$worktype_4 = "---> $worktype_4 $notes_4";
}
if ($worktype_5) {
$worktype_5 = "---> $worktype_5 $notes_5";
}
if ($worktype_6) {
$worktype_6 = "---> $worktype_6 $notes_6";
}
if ($worktype_7) {
$worktype_7 = "---> $worktype_7 $notes_7";
}
if ($worktype_8) {
$worktype_8 = "---> $worktype_8 $notes_8";
}
if ($address_1) {
$address_1 = "Service Performed At $address_1";
}
if ($address_2) {
$address_2 = "Service Performed At $address_2";
}
if ($address_3) {
$address_3 = "Service Performed At $address_3";
}
if ($address_4) {
$address_4 = "Service Performed At $address_4";
}
if ($address_5) {
$address_5 = "Service Performed At $address_5";
}
if ($address_6) {
$address_6 = "Service Performed At $address_6";
}
if ($address_7) {
$address_7 = "Service Performed At $address_7";
}
if ($address_8) {
$address_8 = "Service Performed At $address_8";
}
print qq~
Services Invoice
Ryan Orr Lawn Mowing
7101 S. Cowan Road
Muncie, Indiana 47302
Phone 765-808-0990
DATE: $invoicedate
INVOICE # $invoiceid
Bill To:
$billtoname
DESCRIPTION
AMOUNT
$address_1 $date_1
$price_1
TOTAL
\$$totalprice
� Please make all checks payable to Ryan Orr
THANK YOU FOR YOUR
BUSINESS!
~;
}
}
sub viewinvoicerange {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from invoices where invoiceid BETWEEN '$INPUT{startinvoice}' AND '$INPUT{endinvoice}'");
$aasth->execute;
while (($invoiceid,$propertyid,$clientid,$invoicedate,$billtoname,$billtoaddress,$billtoaddress2,$billtocity,$billtostate,$billtozip,$worktype_1,$address_1,$date_1,$price_1,$notes_1,$worktype_2,$address_2,$date_2,$price_2,$notes_2,$worktype_3,$address_3,$date_3,$price_3,$notes_3,$worktype_4,$address_4,$date_4,$price_4,$notes_4,$worktype_5,$address_5,$date_5,$price_5,$notes_5,$worktype_6,$address_6,$date_6,$price_6,$notes_6,$worktype_7,$address_7,$date_7,$price_7,$notes_7,$worktype_8,$address_8,$date_8,$price_8,$notes_8,$totalprice,$status) = $aasth->fetchrow_array()) {
if ($notes_1) {
$notes_1 = "( $notes_1 )";
}
if ($notes_2) {
$notes_2 = "( $notes_2 )";
}
if ($notes_3) {
$notes_3 = "( $notes_3 )";
}
if ($notes_4) {
$notes_4 = "( $notes_4 )";
}
if ($notes_5) {
$notes_5 = "( $notes_5 )";
}
if ($notes_6) {
$notes_6 = "( $notes_6 )";
}
if ($notes_7) {
$notes_7 = "( $notes_7 )";
}
if ($notes_8) {
$notes_8 = "( $notes_8 )";
}
if ($price_1) {
$price_1 = "\$$price_1 \$$price_2 \$$price_3 \$$price_4 \$$price_5 \$$price_6 \$$price_7 \$$price_8 $worktype_1 $notes_1";
}
if ($worktype_2) {
$worktype_2 = "---> $worktype_2 $notes_2";
}
if ($worktype_3) {
$worktype_3 = "---> $worktype_3 $notes_3";
}
if ($worktype_4) {
$worktype_4 = "---> $worktype_4 $notes_4";
}
if ($worktype_5) {
$worktype_5 = "---> $worktype_5 $notes_5";
}
if ($worktype_6) {
$worktype_6 = "---> $worktype_6 $notes_6";
}
if ($worktype_7) {
$worktype_7 = "---> $worktype_7 $notes_7";
}
if ($worktype_8) {
$worktype_8 = "---> $worktype_8 $notes_8";
}
if ($address_1) {
$address_1 = "Service Performed At $address_1";
}
if ($address_2) {
$address_2 = "Service Performed At $address_2";
}
if ($address_3) {
$address_3 = "Service Performed At $address_3";
}
if ($address_4) {
$address_4 = "Service Performed At $address_4";
}
if ($address_5) {
$address_5 = "Service Performed At $address_5";
}
if ($address_6) {
$address_6 = "Service Performed At $address_6";
}
if ($address_7) {
$address_7 = "Service Performed At $address_7";
}
if ($address_8) {
$address_8 = "Service Performed At $address_8";
}
print qq~
Services Invoice
�
300 E. Highland
Muncie, Indiana 47303
Phone 765.288-1826
DATE:� $invoicedate
INVOICE # $invoiceid
Bill To:
$billtoname
DESCRIPTION
AMOUNT
$address_1 $date_1
$price_1
TOTAL
\$$totalprice
� Please make all checks payable to John Jett Jr.
Thank you for your business!
~;
}
}
sub readnewinvoice {
if ($INPUT{rids}) {
$updaterids = "\(";
@rids = split(/\,/,$INPUT{rids});
foreach $place (@rids) {
$updaterids .= "reportid = \'$place\' OR ";
}
$updaterids .= "reportid = \'IHAVENOIDEA\'\)";
}
else {
$updaterids = "reportid LIKE \'\%\'";
}
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $oasth = $dbh->do("insert into invoices values('','$INPUT{propertyid}','$INPUT{clientid}','$INPUT{invoicedate}','$INPUT{billtoname}','$INPUT{billtoaddress}','$INPUT{billtoaddress2}','$INPUT{billtocity}','$INPUT{billtostate}','$INPUT{billtozip}','$INPUT{worktype_1}','$INPUT{address_1}','$INPUT{date_1}','$INPUT{price_1}','$INPUT{notes_1}','$INPUT{worktype_2}','$INPUT{address_2}','$INPUT{date_2}','$INPUT{price_2}','$INPUT{notes_2}','$INPUT{worktype_3}','$INPUT{address_3}','$INPUT{date_3}','$INPUT{price_3}','$INPUT{notes_3}','$INPUT{worktype_4}','$INPUT{address_4}','$INPUT{date_4}','$INPUT{price_4}','$INPUT{notes_4}','$INPUT{worktype_5}','$INPUT{address_5}','$INPUT{date_5}','$INPUT{price_5}','$INPUT{notes_5}','$INPUT{worktype_6}','$INPUT{address_6}','$INPUT{date_6}','$INPUT{price_6}','$INPUT{notes_6}','$INPUT{worktype_7}','$INPUT{address_7}','$INPUT{date_7}','$INPUT{price_7}','$INPUT{notes_7}','$INPUT{worktype_8}','$INPUT{address_8}','$INPUT{date_8}','$INPUT{price_8}','$INPUT{notes_8}','$INPUT{totalprice}','$INPUT{status}')");
my $kasth = $dbh->do("update reporting set status = 'invoiced' WHERE clientid = '$INPUT{clientid}' AND $updaterids");
$dbh->disconnect;
print qq~
Creating New Invoice
Please wait, While your new invoice is saved Click Here .Client Management System
STARTOFTHEPAGE
&header;
while (($customerid,$firstname,$lastname,$company) = $aasth->fetchrow_array()) {
if ($firstname eq "" && $lastname eq "") {
$printname = "$company";
}
else {
if ($company eq "N/A") {
$printname = "$firstname $lastname";
}
else {
$printname = "$firstname $lastname, $company";
}
}
my $easth = $dbh->prepare("select COUNT(reportid) from reporting where clientid = '$customerid' and status = 'pending'");
$easth->execute;
while (($reportidnumber) = $easth->fetchrow_array()) {
$totalreportidnumber = "$reportidnumber";
if ($reportidnumber > 1) {
$item = "Items"
}
else {
$item = "Item";
}
}
print qq~
Create Invoice For $printname $totalreportidnumber $item To Be Invoiced
~;
&footer;
}
sub createinvoice {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select customerid,firstname,lastname,company from clients");
$aasth->execute;
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
Create Invoice $printname - (Found $numberfound $property to invoice on (?$pnumberfound) properties)
~;
&footer;
}
sub dologworkset {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $oasth = $dbh->do("insert into reporting values('','$INPUT{propertyid}','$INPUT{clientid}','$INPUT{status}','$INPUT{date}','$INPUT{price}','$INPUT{worktype}','$INPUT{notes}')");
$dbh->disconnect;
print qq~
Logging Completed Work
Please wait, While your work log is saved Click Here .Client Management System
STARTOFTHEPAGE
&header;
print qq~
Log Completed Work
~;
print qq~
$printname
~;
&footer;
}
}
sub logwork {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select propertyid,clientid,address,city,state,zip from properties where status = 'a'");
$aasth->execute;
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
Log Completed Work $printname - $address $city, $state $zip
~;
&footer;
}
sub doaddproperty {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $oasth = $dbh->do("insert into properties values('','$INPUT{clientid}','$INPUT{address}','$INPUT{address2}','$INPUT{city}','$INPUT{state}','$INPUT{zip}','$INPUT{directions}','$INPUT{notes}','$INPUT{nmowprice}','$INPUT{imowprice}','$INPUT{status}',now())");
$dbh->disconnect;
print qq~
Adding Contact To Database
Please wait, Processing Click Here .Adding Contact To Database
Please wait, Processing Click Here .update clients set firstname = '$INPUT{firstname}' , lastname = '$INPUT{lastname}' , company = '$INPUT{company}' , address = '$INPUT{address}' , address2 = '$INPUT{address2}' , city = '$INPUT{city}' , state = '$INPUT{state}' , zip = '$INPUT{zip}' , workphone1 = '$INPUT{workphone1}' , workphone2 = '$INPUT{workphone2}' , workphone3 = '$INPUT{workphone3}' , homephone1 = '$INPUT{homephone1}' , homephone2 = '$INPUT{homephone2}' , homephone3 = '$INPUT{homephone3}' , mobilephone1 = '$INPUT{mobilephone1}' , mobilephone2 = '$INPUT{mobilephone2}' , mobilephone3 = '$INPUT{mobilephone3}' , referredby = '$INPUT{referredby}' , information = '$INPUT{information}' WHERE customerid = '7' Updated Client $INPUT{firstname} $INPUT{lastname} ok View Clients | Edit $INPUT{firstname} $INPUT{lastname} | View $INPUT{firstname} $INPUT{lastname}
Display Update SQL: Client Management System
STARTOFTHEPAGE
&header;
print qq~
View Client $printname
Customer ID $customerid
Firstname $firstname
Lastname $lastname
Company $company
Address $address
Address2 $address2
City, State, Zip $city,$state $zip
Work Phone
$workphone1-$workphone2-$workphone3
Home Phone
$homephone1-$homephone2-$homephone3
Mobile Phone
$mobilephone1-$mobilephone2-$mobilephone3
Properties Under Account
~;
while (($propertyid,$clientid,$address,$address2,$city,$state,$zip,$directions,$notes,$imowprice,$smowprice,$status,$dateadded) = $basth->fetchrow_array()) {
$propertyfound = "yes";
if ($status eq "a") {
$status = "Active | ";
}
elsif ($status eq "n") {
$status = "Not Active | ";
}
else {
$status = "Bad Database Contact Ryan Orr NOW! | ";
}
print "$status$address $city $state $zip | Edit | View No Properties Added!
Referred By
$referredby
Customer Notes
$information
Date Added $datejoined
~;
&footer;
}
}
sub editclient {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from clients WHERE customerid = '$INPUT{customerid}'");
$aasth->execute;
while (($customerid,$firstname,$lastname,$company,$address,$address2,$city,$state,$zip,$workphone1,$workphone2,$workphone3,$homephone1,$homephone2,$homephone3,$mobilephone1,$mobilephone2,$mobilephone3,$referredby,$information,$datejoined) = $aasth->fetchrow_array()) {
$referredby = "$referredby ";
if ($firstname eq "" && $lastname eq "") {
$printname = "$company";
}
else {
if ($company eq "N/A") {
$printname = "$firstname $lastname";
}
else {
$printname = "$firstname $lastname, $company";
}
}
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
~;
&footer;
}
}
sub addclient {
print <<"STARTOFTHEPAGE";
Add New Property For $INPUT{client}
STARTOFTHEPAGE
&header;
print qq~
~;
&footer;
}
sub showclients {
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
Droopys Lawn & Maintenance Client List
Customer ID
Customer Name
Company
\# Of Properties
Actions
~;
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from clients");
$aasth->execute;
$startcountingit = "1";
while (($customerid,$firstname,$lastname,$company,$address,$address2,$city,$state,$zip,$workphone1,$workphone2,$workphone3,$homephone1,$homephone2,$homephone3,$mobilephone1,$mobilephone2,$mobilephone3,$referredby,$information,$datejoined) = $aasth->fetchrow_array()) {
my $basth = $dbh->prepare("select COUNT(clientid) from properties where clientid = '$customerid'");
$basth->execute;
if ($startcountingit eq "2") {
$showbgcolor = "FFFF99";
$startcountingit = "1";
}
else {
$showbgcolor = "white";
$startcountingit++;
}
while (($countnumber) = $basth->fetchrow_array()) {
print qq~
$customerid
$firstname $lastname
$company
$countnumber
View ..|.. Edit
~;
}
}
$dbh->disconnect;
print qq~
~;
&footer;
}
sub clientsearch {
print <<"STARTOFTHEPAGE";
Client Search Results
STARTOFTHEPAGE
&header;
print qq~
Client Search Results
Customer ID
Customer Name
Company
Actions
~;
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("SELECT * FROM clients WHERE $INPUT{type} LIKE '%$INPUT{find}%'");
$aasth->execute;
while (($customerid,$firstname,$lastname,$company,$address,$address2,$city,$state,$zip,$workphone1,$workphone2,$workphone3,$homephone1,$homephone2,$homephone3,$mobilephone1,$mobilephone2,$mobilephone3,$referredby,$information,$datejoined) = $aasth->fetchrow_array()) {
$foundsearch = "Yes";
if ($startcountingit eq "2") {
$showbgcolor = "FFFF99";
$startcountingit = "1";
}
else {
$showbgcolor = "white";
$startcountingit++;
}
print qq~
$customerid
$firstname $lastname
$company
View ..|.. Edit
~;
}
$dbh->disconnect;
unless ($foundsearch eq "Yes") {
print "Cannot Find Your Search For $INPUT{find} !
~;
&footer;
}
sub addproperty {
print <<"STARTOFTHEPAGE";
Add New Property
STARTOFTHEPAGE
&header;
print qq~
Add New Property
~;
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from clients");
$aasth->execute;
$startcountingit = "1";
$starttablecount = "1";
while (($customerid,$firstname,$lastname,$company,$address,$address2,$city,$state,$zip,$workphone1,$workphone2,$workphone3,$homephone1,$homephone2,$homephone3,$mobilephone1,$mobilephone2,$mobilephone3,$referredby,$information,$datejoined) = $aasth->fetchrow_array()) {
if ($firstname eq "" && $lastname eq "") {
$printname = "$company";
}
else {
if ($company eq "N/A") {
$printname = "$firstname $lastname";
}
else {
$printname = "$firstname $lastname, $company";
}
}
if ($startcountingit eq "2") {
$showbgcolor = "FFFF99";
$startcountingit = "1";
}
else {
$showbgcolor = "white";
$startcountingit++;
}
print qq~
$printname
~;
}
$dbh->disconnect;
print qq~
~;
&footer;
}
sub addpropertyset {
print <<"STARTOFTHEPAGE";
Add New Property For $INPUT{client}
STARTOFTHEPAGE
&header;
print qq~
Add New Property
~;
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from clients where customerid = '$INPUT{client}'");
$aasth->execute;
$starttablecount = "1";
while (($customerid,$firstname,$lastname,$company,$address,$address2,$city,$state,$zip,$workphone1,$workphone2,$workphone3,$homephone1,$homephone2,$homephone3,$mobilephone1,$mobilephone2,$mobilephone3,$referredby,$information,$datejoined) = $aasth->fetchrow_array()) {
if ($firstname eq "" && $lastname eq "") {
$printname = "$company";
}
else {
if ($company eq "N/A") {
$printname = "$firstname $lastname";
}
else {
$printname = "$firstname $lastname, $company";
}
}
if ($startcountingit eq "2") {
$showbgcolor = "FFFF99";
$startcountingit = "1";
}
else {
$showbgcolor = "white";
$startcountingit++;
}
if ($address2 eq "") {
$address2 = "";
}
else {
$address2 = "$address2
$printname
~;
}
$dbh->disconnect;
print qq~
~;
&footer;
}
sub editproperty {
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from properties WHERE propertyid = '$INPUT{propertyid}'");
$aasth->execute;
my $casth = $dbh->prepare("select customerid,firstname,lastname,company from clients");
$casth->execute;
while (($propertyid,$clientid,$address,$address2,$city,$state,$zip,$directions,$notes,$nmowprice,$imowprice,$status,$dateadded) = $aasth->fetchrow_array()) {
if ($status eq "a") {
$dstatus = "Active (Servicing Property)";
}
elsif ($status eq "n") {
$dstatus = "Non-Active (Services)";
}
else {
$dstatus = "BAD SETUP CONTACT RYAN";
}
$status = "$dstatus ";
my $basth = $dbh->prepare("select * from clients WHERE customerid = '$clientid'");
$basth->execute;
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
~;
&footer;
}
}
sub doeditproperty {
$tempprintsql = "update properties set clientid = '$INPUT{clientid}' , address = '$INPUT{address}' , address2 = '$INPUT{address2}' , city = '$INPUT{city}' , state = '$INPUT{state}' , zip = '$INPUT{zip}' , directions = '$INPUT{directions}' , notes = '$INPUT{notes}' , nmowprice = '$INPUT{nmowprice}' , imowprice = '$INPUT{imowprice}' , status = '$INPUT{status}' Updated property $INPUT{Address} Successful View Clients | Edit $address again
Display Update SQL: Active ";
}
else {
$status = "Not Active ";
}
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
View Property $address
Customer ID $clientid
Address $address
Address2 $address2
City, State, Zip $city,$state $zip
Directions $directions
Notes $notes
Pricing Initial $imowprice
Status
$status
Date Added $dateadded
Invoice History
~;
$oasth = $dbh->prepare("select * from invoices where propertyid = ?");
$oasth->execute($INPUT{propertyid});
while ($data_ref = $oasth->fetchrow_hashref()) {
%db = %$data_ref;
$found = "yes";
print qq~
INVOICE $db{invoiceid} - $db{invoicedate} - \$$db{totalprice} No Invoices Found
~;
}
print qq~
~;
&footer;
}
}
sub showproperties {
if ($INPUT{status} eq "a") {
$status = "WHERE status = 'a'";
$statusname = "Active";
}
elsif ($INPUT{status} eq "n") {
$status = "WHERE status = 'n'";
$statusname = "Non-Active";
}
else {
$status = "";
$statusname = "All";
}
print <<"STARTOFTHEPAGE";
Client Management System
STARTOFTHEPAGE
&header;
print qq~
$statusname Property List
Address
City
State
Zip
Actions
~;
$d_name = "orrnet_droopylawn";
$d_username = "orrnet_droopylaw";
$d_pass = "1687Secure";
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->prepare("select * from properties $status");
$aasth->execute;
$startcountingit = "1";
while (($propertyid,$clientid,$address,$address2,$city,$state,$zip) = $aasth->fetchrow_array()) {
if ($startcountingit eq "2") {
$showbgcolor = "FFFF99";
$startcountingit = "1";
}
else {
$showbgcolor = "white";
$startcountingit++;
}
print qq~
$address
$city
$state
$zip
View ..|.. Edit
~;
}
$dbh->disconnect;
print qq~
~;
&footer;
}
sub header {
open(HEADER,"includes/mainpage.txt");
@header = ;
close(HEADER);
foreach $line (@header) {
$line =~ s/\$navbarcode/$navbarcode/g;
print "$line";
}
}
sub footer {
open(FOOTER,"includes/footer.txt");
@footer = ;
close(FOOTER);
foreach $line (@footer) {
print "$line";
}
}
sub commas {
local($_)=@_;
1 while s/(.*\d)(\d\d\d)/$1,$2/;
$_;
}
