--- Begin Message ---
Chris Beggy wrote:
>On 05 Jun 2002, Ted Petit <[EMAIL PROTECTED]> wrote:
>
>Are you interested in sharing your patches? I realize that you
>you are very busy with the biz!
>
>Thanks.
>
>Chris
>
Chris,
For some reason, I cannot post to sql-ledger. This is probably because
my ISP is allowing a spammer to operate and the sql-ledger site is
bouncing my e-mails.
I have attached the write_checks stuff.
Could you do me a favor and post this on sql-ledger for me?
Thanks,
Ted
OK - so I forgot the attachment. It must be past lunchtime.
INTRODUCTION
The write_checks subroutine uses the checks.tex template which is formatted for
Quickbooks
style checks. (Check at the top and two stubs underneath. The best place to order them
is from BJ's)
The routine is not yet ready for prime time due to the following caveats:
1. I created a 'next_check_number' field in the 'defaults' table but haven't
yet implemented it.
2. I should use the data displayed and enterable by the user in the 'Process
Payments' screen
rather than retrieving the data a second time from the database. I think I
did this for testing only
but haven't gotten back to it yet.
3. After selecting:
AP -> Payments -> (fill in 'Next Check Number' field) -> Continue, you
must select 'Write Checks' prior to 'Process Payments' otherwise, all of
the check
data will be lost.
4. The procedure in Note 3 allows you to print on plain paper to check
accuracy, alignment, etc.
and then use the back button to print the actual checks. This is the only
reason I did not auto-
matically execute 'Process Payments' after printing the checks.
5. You must use the back button on your browser to 'Process Payments' after
printing checks.
There should be a selection to 'Process Payments' in the subsequent Status
window so that
people such as myself do not forget this rather important step. (Yes, I
have)
__________________________________________________________________________________________________________________________
SECTION 1 Changes needed for AP.pm "payments" to work with write_checks
#this is the modified SELECT statement in AP.pm "payments" subroutine
#I do this because I sub-total all of the same vendors for the check amount.
###############################################################################################
#
#
# NOTE: THE ONLY CHANGE NEEDED IS IN THE ORDER BY CLAUSE (first "v.name" then
"$form->{sort})"#
# DO NOT CHANGE THE BODY OF THE SELECT STATEMENT AS IT INCORPORATES CHANGES NEEDED BY
DB2 #
# AND MY BUSINESS SPECIFIC LOGIC
#
#
#
###############################################################################################
$query = qq|SELECT a.id, a.invnumber, a.transdate, a.datepaid, a.amount,
a.paid, a.ordnumber, v.name, a.invoice, a.curr AS currency,
v.vendortype, v.id AS vendor_id, CURRENT DATE AS currentdate, (SELECT
ponumber FROM
oe WHERE oe.ordnumber = a.ordnumber) AS ponumber,(SELECT vin_number FROM
oe WHERE oe.ordnumber = a.ordnumber) AS vin_number
FROM ap a,vendor v, defaults d
WHERE a.vendor_id = v.id|;
my $invnumber = $form->like(lc $form->{invnumber});
my $ordnumber = $form->like(lc $form->{ordnumber});
$query .= " AND lower(a.invnumber) LIKE '$invnumber'" if $form->{invnumber};
$query .= " AND lower(a.ordnumber) LIKE '$ordnumber'" if $form->{ordnumber};
$query .= " AND a.vendor_id = $form->{vendor_id}" if ($form->{vendor_id});
$query .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
$query .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
$query .= " AND a.\"1099\" = '1'" if ($form->{vendor1099});
$query .= " AND a.amount <> a.paid" if ($form->{open} eq "Y");
$query .= " ORDER by v.name, $form->{sort}";
________________________________________________________________________________________________
SECTION 2 Changes in ap.pl search sub-routine
#the changes are towards the end of this section and highlighted with #'s
#the only change really needed, is the addition of a next_check_number field
#so that the routine will automatically fill-in the source field
#(which I have re-named 'Check No.")
sub search {
# setup vendor selection
$form->all_vc(\%myconfig, "vendor");
my $selection = "<option selected>" . $locale->text('All Vendors') ."\n";
foreach $ref (@{ $form->{all_vendor} }) {
$selection .= "<option>$ref->{name}--$ref->{id}\n";
}
if ($form->{nextsub} eq "payments") {
$form->{title} = $locale->text('Process Payments');
$listopen = qq|
<input type=hidden name=sort value=invnumber>
<input type=hidden name=open value=Y>|;
}
if ($form->{nextsub} eq "ap_transactions") {
$form->{title} = $locale->text('AP Transactions');
$listopen = qq|
<input type=hidden name=sort value=transdate>
<tr>
<th align=right>|.$locale->text('Include in Report').qq|</th>
<td colspan=3>
<input name=open class=checkbox type=checkbox value=Y
checked> |.$locale->text('Open').qq|
<input name=closed class=checkbox type=checkbox
value=Y> |.$locale->text('Closed').qq|
<br>
<input name="l_invnumber" class=checkbox type=checkbox value=Y
checked> |.$locale->text('Invoice Number').qq|
<input name="l_ordnumber" class=checkbox type=checkbox
value=Y> |.$locale->text('Order Number').qq|
<input name="l_name" class=checkbox type=checkbox value=Y
checked> |.$locale->text('Vendor').qq|
<input name="l_transdate" class=checkbox type=checkbox value=Y
checked> |.$locale->text('Invoice Date').qq|
<input name="l_netamount" class=checkbox type=checkbox
value=Y> |.$locale->text('Net Amount').qq|
<input name="l_tax" class=checkbox type=checkbox
value=Y> |.$locale->text('Tax').qq|
<input name="l_amount" class=checkbox type=checkbox value=Y
checked> |.$locale->text('Total').qq|
<input name="l_datepaid" class=checkbox type=checkbox
value=Y> |.$locale->text('Date Paid').qq|
<input name="l_paid" class=checkbox type=checkbox value=Y
checked> |.$locale->text('Paid').qq|
<input name="l_duedate" class=checkbox type=checkbox
value=Y> |.$locale->text('Due Date').qq|
<input name="l_due" class=checkbox type=checkbox
value=Y> |.$locale->text('Due').qq|
<input name="l_subtotal" class=checkbox type=checkbox
value=Y> |.$locale->text('Subtotal').qq|
</td>
</tr>
|;
}
$form->header;
print qq|<h2>$form->{title}</h2>
<form method=post action=$form->{script}>
<table border=1>
<tr><td>
<table>
<tr>
<th align=right>|.$locale->text('Vendor').qq|</th>
<td colspan=3><select name=vendor>$selection</select></td>
</tr>
<tr>
<th align=right>|.$locale->text('Invoice Number').qq|</th>
<td colspan=3><input name=invnumber size=20></td>
</tr>
<tr>
<th align=right>|.$locale->text('Order Number').qq|</th>
<td colspan=3><input name=ordnumber size=20></td>
</tr>
<tr>
<th align=right>|.$locale->text('From').qq|<br>($myconfig{dateformat})</th>
<td><input name=transdatefrom size=11></td>
<th align=right>|.$locale->text('To').qq|</th>
<td><input name=transdateto size=11></td>
</tr>
#################################################################################################
<tr>
<th align=right>|.$locale->text('1099 Vendors Only').qq|</th>
<th colspan=3 align=left><input name=vendor1099 class=checkbox type=checkbox
value=Y> </th>
</tr>
<tr>
<th align=right>|.$locale->text('Next Check Number').qq|</th>
<td><input name=next_check_number size=11 value=$form->{next_check_number}></td>
</tr>
#################################################################################################
$listopen
</table>
</td></tr>
</table>
<p>
<input type=hidden name=nextsub value=$form->{nextsub}>
<input type=hidden name=path value=$form->{path}>
<input type=hidden name=login value=$form->{login}>
<input type=hidden name=password value=$form->{password}>
<input class=submit type=submit name=action value="|.$locale->text('Continue').qq|">
</form>
<p>|.$locale->text('To display all transactions, leave fields blank')
.qq|
</body>
</html>
|;
}
________________________________________________________________________________________________
SECTION 3 Changes needed to ap.pl "payments" to sort and total for write_checks
#this is a modified ap.pl "payments" subroutine.
#I check to see if the $previousvendorname is the same as the current vendor name.
#if it is not, then I write a Total amunt and then draw a horizontal rule under this
vendor's subsection
#NOTE: ponumber, vendortype, Salesperson and Installer fields are specific to my
business
sub payments {
# split vendor
($vendor, $form->{vendor_id}) = split(/--/, $form->{vendor});
AP->payments(\%myconfig, \%$form);
# construct href
$href =
"$form->{script}?path=$form->{path}&action=payments&login=$form->{login}&password=$form->{password}&transdatefrom=$form->{transdatefrom}&transdateto=$form->{transdateto}&open=$form->{open}&invnumber="
. $form->escape($form->{invnumber}) . "&ordnumber=" .
$form->escape($form->{ordnumber}) . "&vendor=" . $form->escape($form->{vendor});
@column_index = $form->sort_columns(qw(invnumber transdate ordnumber name ponumber
amount exchangerate source vendortype));
$column_header{transdate} = qq|<th><a class=listheading
href=$href&sort=transdate><font
color=ffffff>|.$locale->text('Date').qq|</font></a></th>|;
$column_header{invnumber} = qq|<th><a class=listheading
href=$href&sort=invnumber><font
color=ffffff>|.$locale->text('Invoice').qq|</font></a></th>|;
$column_header{ordnumber} = qq|<th><a class=listheading
href=$href&sort=ordnumber><font
color=ffffff>|.$locale->text('Order').qq|</font></a></th>|;
$column_header{ponumber} = qq|<th><font color=ffffff>|.$locale->text('PO
Number').qq|</font></a></th>|;
$column_header{name} = qq|<th><a class=listheading href=$href&sort=name><font
color=ffffff>|.$locale->text('Vendor').qq|</font></a></th>|;
$column_header{amount} = qq|<th><font
color=ffffff>|.$locale->text('Amount').qq|</font></th>|;
$column_header{source} = "<th><font color=ffffff>".$locale->text('Check
No.')."</font></th>";
$column_header{exchangerate} = "<th><font color=ffffff>".$locale->text('Exchange
Rate')."</font></th>";
$column_header{vendortype} = "<th><font color=ffffff>".$locale->text('Vendor
Type')."</font></th>";
$form->{title} = $locale->text('Process Payments');
$form->header;
print qq|
<h2>$form->{title}</h2>
<form method=post action=$form->{script}>
<input type=hidden name=fxgain_accno value=$form->{fxgain_accno}>
<input type=hidden name=fxloss_accno value=$form->{fxloss_accno}>
<input type=hidden name=save_vendor value="$form->{vendor}">
<input type=hidden name=save_check_number value=$form->{next_check_number}>
<table border=0>
<tr class=listheading bgcolor=336666>|;
map { print "\n$column_header{$_}" } @column_index;
print qq|
</tr>
|;
# construct and escape callback
$vendor = $form->{vendor};
$vendor =~ s/&/_/g;
$callback =
$form->escape("$form->{script}?path=$form->{path}&action=payments&transdatefrom=$form->{transdatefrom}&transdateto=$form->{transdateto}&open=$form->{open}&login=$form->{login}&password=$form->{password}&sort=$form->{sort}&vendor=$vendor&invnumber=$form->{invnumber}&ordnumber=$form->{ordnumber}");
# build the popup menu for the accounts
foreach $key (sort keys %{$form->{paidaccount}}) {
$paidaccounts .=
qq|<option>$form->{paidaccount}{$key}{accno}--$form->{paidaccount}{$key}{description}\n|;
}
$previousvendorname="";
$hr = "";
$totalamount="";
$next_check_number="";
foreach $ap (@{ $form->{AP} }) {
$i++;
$j++; $j %= 2;
print "<tr class=listrow$j>";
$column_data{transdate} = qq|<td><input type=hidden name="transdate_$i"
value=$ap->{transdate}>$ap->{transdate}</td>|;
if ($ap->{invoice}) {
$column_data{invnumber} = "<td><a
href=ir.pl?path=$form->{path}&action=edit&id=$ap->{id}&login=$form->{login}&password=$form->{password}&callback=$callback>$ap->{invnumber}</a></td>";
} else {
$column_data{invnumber} = "<td><a
href=$form->{script}?path=$form->{path}&action=edit&id=$ap->{id}&login=$form->{login}&password=$form->{password}&callback=$callback>$ap->{invnumber}</a></td>";
}
if ($previousvendorname eq $ap->{name} || !$previousvendorname) {
$hr = "";
$totalamount += $ap->{amount};
} else {
$hr = "<b>Total Amount:</b> \$".$form->format_amount(\%myconfig, $totalamount,
2)."<hr size=3 noshade>";
$totalamount= $ap->{amount};
$form->{next_check_number}++;
}
$previousvendorname = $ap->{name};
print qq|
<tr><td colspan=8>|.$hr.qq|</td></tr>
|;
if ($ap->{vendortype} eq "S") {
$ap->{vendortypetext} = "Salesperson";
} elsif ($ap->{vendortype} eq "I") {
$ap->{vendortypetext} = "Installer";
} else {
$ap->{vendortypetext} = "Standard";
}
$amount = $form->format_amount(\%myconfig, $ap->{amount} / $ap->{exchangerate}, 2,
" ");
$column_data{ordnumber} = "<td>$ap->{ordnumber} </td>";
$column_data{ponumber} = "<td>$ap->{ponumber} </td>";
$column_data{name} = "<td>$ap->{name}</td>";
$column_data{amount} = "<td align=left>$amount</td>";
$column_data{source} = "<td> </td>";
$column_data{exchangerate} = "<td>$ap->{currency} </td>";
$column_data{vendortype} = "<td align=center>$ap->{vendortypetext} </td>";
map { print "$column_data{$_}\n" } @column_index;
print qq|
</tr>
<tr>
|;
# list all the payments
$totalpaid = 0;
$column_data{invnumber} = qq|<th>|.$locale->text('Payments').qq|</th>|;
$column_data{ordnumber} = qq|<td> </td>|;
foreach $ref (@{ $ap->{payments} }) {
$j++; $j %= 2;
print "<tr class=listrow$j>";
$column_data{transdate} = qq|<td>$ref->{transdate}</td>|;
$column_data{name} =
qq|<td>$form->{paidaccount}{$ref->{accno}}{description}</td>|;
$column_data{amount} = qq|<td align=right>|.$form->format_amount(\%myconfig,
$ref->{amount}, 2, " ").qq|</td>|;
$column_data{source} = qq|<td>$ref->{source} </td>|;
$column_data{exchangerate} = qq|<td> </td>|;
map { print "\n$column_data{$_}" } @column_index;
print qq|</tr>\n|;
$column_data{invnumber} = qq|<td> </td>|;
$totalpaid += $ref->{amount};
}
print "</tr><tr>\n";
# arrange data for payment input
$column_data{transdate} = qq|<td><input name="datepaid_$i" size=11
value=$ap->{currentdate}></td>|;
$column_data{name} = qq|<td><select name="accno_$i">$paidaccounts</select></td>|;
$column_data{amount} = qq|<td align=right><input name="paid_$i" size=9
value=|.$form->format_amount(\%myconfig, $ap->{amount}, 2).qq|></td>|;
$column_data{source} = qq|<td><input name="source_$i" size=10
value=$form->{next_check_number}></td>|;
$column_data{exchangerate} = qq|<td><input name="exchangerate_$i" size=6></td>|;
$column_data{vendortype} = qq|<td> </td>|;
$column_data{ponumber} = qq|<td> </td>|;
map { print "\n$column_data{$_}" } @column_index;
print qq|
</tr>
<input type=hidden name="currency_$i" value=$ap->{currency}>
<input type=hidden name="iexch_$i" value=$ap->{exchangerate}>
<input type=hidden name="totalpaid_$i" value=$totalpaid>
<input type=hidden name="id_$i" value=$ap->{id}>
<input type=hidden name="AP_$i" value=$ap->{AP_account}>
|;
}
print qq|
</table>
<input type=hidden name=rowcount value=$i>
<input type=hidden name=path value=$form->{path}>
<input type=hidden name=login value=$form->{login}>
<input type=hidden name=password value=$form->{password}>
<b>Total Amount: </b>\$|.$form->format_amount(\%myconfig, $totalamount, 2).qq|
<p>
<input class=submit type=submit name=action value="|.$locale->text('Process
Payments').qq|">
 <input class=submit type=submit name=action value="|.$locale->text('Write
Checks').qq|">
</form>
<p>|.$locale->text('Click on table headers to sort table').qq|
<br>|.$locale->text('Click on invoice number to edit transaction')
.qq|
</body>
</html>
|;
}
___________________________________________________________________________________________________________________
SECTION 4 write_checks sub-routine
#this section goes in ap.pl
sub write_checks {
# split vendor
($vendor, $form->{vendor_id}) = split(/--/, $form->{save_vendor});
$form->{sort} = "invnumber";
$form->{open} = "Y";
AP->payments(\%myconfig, \%$form);
$form->{type}="checks";
$form->{format}="postscript";
$form->{templates} = "$myconfig{templates}";
$form->{IN} = "$form->{type}.tex";
$form->{contenttype} = "";
$form->{media}="printer";
$form->{printer} = "lpr " unless $form->{printer};
$form->{OUT} = "|$form->{printer}";
$form->{amount} = 0;
$form->{checkdate} = $form->check_date;
$previousvendorname = "";
$start_check_number = $form->{save_check_number};
foreach $ap (@{ $form->{AP} }) {
$i++;
$j++; $j %= 2;
if ($previousvendorname eq "") {
$form->{name} = $ap->{name};
$form->{vendor_id} = $ap->{vendor_id};
IR->vendor_details(\%myconfig, \%$form);
if ($ap->{vendortype} eq 'I' || $ap->{vendortype} eq 'S') {
$form->{legend} = "Invoice No./PO No./VIN (last 6 digits)/Amount";
} else {
$form->{legend} = "Invoice No./Amount";
}
}
if ($previousvendorname eq $ap->{name} || $previousvendorname eq "") {
$form->{amount} += $ap->{amount};
if ($ap->{vendortype} eq 'I' || $ap->{vendortype} eq 'S') {
$form->{sourceinfo} .=
$ap->{invnumber}."/".$ap->{ponumber}."/".$ap->{vin_number}."/".$ap->{amount};
} else {
$form->{sourceinfo} .=
$ap->{invnumber}."/".$form->format_amount(\%myconfig, $ap->{amount} /
$ap->{exchangerate}, 2);
if ($i % 4 == 0) {
$form->{sourceinfo} .= "\\\\";
} else {
$form->{sourceinfo} .= " ** ";
}
}
} else {
$form->{amount} = $form->format_amount(\%myconfig, $form->{amount} /
$ap->{exchangerate}, 2);
my @dollars = split /\./, $form->{amount};
my $amt = new Lingua::EN::Numbers($dollars[0]);
$form->{amount_text} = $amt->get_string()." and ";
$form->{amount_cents} = $dollars[1]."/100";
$form->parse_template(\%myconfig, $userspath);
$form->{sourceinfo} = "";
$form->{name} = $ap->{name};
$form->{vendor_id} = $ap->{vendor_id};
IR->vendor_details(\%myconfig, \%$form);
if ($ap->{vendortype} eq 'I' || $ap->{vendortype} eq 'S') {
$form->{legend} = "Invoice No./PO No./VIN (last 6
digits)/Amount";
} else {
$form->{legend} = "Invoice No./Amount";
}
if ($ap->{vendortype} eq 'I' || $ap->{vendortype} eq 'S') {
$form->{sourceinfo} .=
$ap->{invnumber}."/".$ap->{ponumber}."/".$ap->{vin_number}."/".$ap->{amount};
} else {
$form->{sourceinfo} .=
$ap->{invnumber}."/".$form->format_amount(\%myconfig, $ap->{amount} /
$ap->{exchangerate}, 2);
}
$form->{amount} = $ap->{amount};
$form->{save_check_number}++;
}
$previousvendorname = $ap->{name};
}
# get last check
$form->{amount} = $form->format_amount(\%myconfig, $form->{amount}, 2);
my @dollars = split /\./, $form->{amount};
my $amt = new Lingua::EN::Numbers($dollars[0]);
$form->{amount_text} = $amt->get_string()." and ";
$form->{amount_cents} = $dollars[1]."/100";
$form->parse_template(\%myconfig, $userspath);
IR->vendor_details(\%myconfig, \%$form);
# if we got back here and we sent
# to the printer or email let them know
$form->header;
$msg = 'Checks '.$start_check_number.' to '.$form->{save_check_number}.' printed';
print qq|
<h2><font color=blue>|.$locale->text('Status!').qq|</font></h2> <h4>
$form->{label} $form->{"${inv}number"} $msg </h4>
</body>
</html>
|;
}
_____________________________________________________________________________________________________________________________________________________________
SECTION 5 checks.tex
\documentclass[twoside]{scrartcl}
\usepackage[frame]{xy}
\usepackage{tabularx}
\setlength{\voffset}{-1.2cm}
\setlength{\hoffset}{-2.0cm}
\setlength{\topmargin}{0cm}
\setlength{\headheight}{0.0cm}
\setlength{\headsep}{1cm}
\setlength{\topskip}{0pt}
\setlength{\oddsidemargin}{1.0cm}
\setlength{\evensidemargin}{1.0cm}
\setlength{\textwidth}{19.2cm}
\setlength{\textheight}{24.5cm}
\setlength{\footskip}{1cm}
\setlength{\parindent}{0pt}
\renewcommand{\baselinestretch}{1}
\begin{document}
\fontfamily{cmss}\fontsize{12pt}{12pt}\selectfont
\begin{tabular}{lr}
\vspace{0.9cm}
\hspace{15.5cm}
& <%checkdate%> \\
\vspace{0.5cm}\hspace{0.6cm}
<%name%> & \textbf{**}<%amount%> \\
\multicolumn{1}{l}
\textbf{**}<%amount_text%><%amount_cents%> \\
\end{tabular}
\vspace{1cm}
\fontfamily{cmss}\fontsize{12pt}{12pt}\selectfont
<%name%> \\
<%addr1%> \\
<%addr2%>, <%addr3%> <%addr4%>
\vspace{2.5cm}
\begin{tabular}{l@{\hspace{10.5cm}}r}
<%name%> & \textbf{**}<%amount%>
\end{tabular}
\vspace{0.5cm}
\begin{tabular}{l}
<%legend%>\\
<%sourceinfo%>
\end{tabular}
\vspace{8.5cm}
\begin{tabular}{l@{\hspace{10.5cm}}r}
<%name%> & \textbf{**}<%amount%>
\end{tabular}
\vspace{0.5cm}
\begin{tabular}{l}
<%legend%>\\
<%sourceinfo%>
\end{tabular}
<%if notes%>
<%notes%>
<%end if%>
\end{document}
--- End Message ---
