Solved

need help with creating a page breaking invoice.

Posted on 2004-09-17
2
203 Views
Last Modified: 2013-12-24
I am trying to create an invoice that will break at a specific time or recordcount.

here  is the format that I am looking for
------------------------------------------------------------------------------------------
                                              title
                                                                                                       image
name                                                                                   myconame and address
(membernumber hidden)
address1
city, state zip
( this info is for the parent co. this co can also have child companies assigned to it.)

<hr>

duedate
                                             subtitle

truememrnumber               company name                                   amountdue
ex:  1234 A                        fullname
                                        address1
                                        address2 (if not blank)
                                        city state zip

truememrnumber               company name                                   amountdue
ex:  1234 b                        fullname
                                        address1
                                        address2 (if not blank)
                                        city state zip

truememrnumber               company name                                   amountdue
ex:  1234 c                        fullname
                                        address1
                                        address2 (if not blank)
                                        city state zip

now after the last record has been found the following footer info would be shown
total amount due ..............................................totalsumof amounts due
some disclaimer legal mumbo jumbo
some disclaimer legal mumbo jumbo
some disclaimer legal mumbo jumbo
some disclaimer legal mumbo jumbo
some disclaimer legal mumbo jumbo
some disclaimer legal mumbo jumbo
some disclaimer legal mumbo jumbo

now the code that I have been using is the following but can't seem to get the the break and the looping right.:

<cfquery name="getParents" datasource="uspoultry">
SELECT     Contacts.Company, Contacts.FullName, Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State, Contacts.zip, Contacts.country,
                      members.membernumber, members.memberletter, members.TrueMemNumber, mem_Payments.Amount, mem_Payments.memyear,
                      members.RecordType
FROM         Contacts INNER JOIN
                      members ON Contacts.Cid = members.Cid INNER JOIN
                      mem_Payments ON Contacts.Cid = mem_Payments.CID
WHERE     (mem_Payments.memyear = '2004') AND (members.memberletter = N'a') AND (members.RecordType = N'm')
ORDER BY members.TrueMemNumber, Contacts.Company
</cfquery>
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td colspan="4" align="center"><strong>INVOICE</strong></td>
      </tr>
  <tr>
 
    <td align="center" COLSPAN="2">&nbsp;</td>
    <td width="12%" align="center">&nbsp;</td>
    <td width="29%" align="center"><img src="../images/1L4nowords.gif" width="50" height="50"></td>
  </tr><!--- Loop for the primary Records A --->
  <cfloop query="getparents">
  <cfoutput><tr>
    <td width="32%" align="left" valign="top">#Company#<br>
#FullName#<br>
#Address1#<br>
<cfif Address2 neq ''>#address2#<br></cfif>
#City#, #Ucase(State)# #zip#<cfif Country NEQ 'USA'><br> #Ucase(Country)#</cfif>
</cfoutput>
</td>
    <td width="27%">&nbsp;</td>
    <td colspan="2" align="right" valign="top"><div align="right" class="style1">U.S. Poultry &amp; Egg Association <br>
        1530 Cooledge Road<br>
        Tucker, GA 30084-7303<br>
        770.493.9401<br>
        Fax: 770.493.9257<br>
        www.poultryegg.org<br>
        EIN:  58-0704657
      </div></td>
  </tr>
  <tr><td colspan="6"><hr color="000000" ></td></tr>
  <!--- Query for the child records of the parents --->
  <cfquery name="getchild" datasource="uspoultry">
  SELECT     Contacts.Company, Contacts.FullName, Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State, Contacts.zip, Contacts.country,
                      members.membernumber, members.memberletter, members.TrueMemNumber, mem_Payments.Amount, mem_Payments.memyear,
                      members.RecordType
FROM         Contacts INNER JOIN
                      members ON Contacts.Cid = members.Cid INNER JOIN
                      mem_Payments ON Contacts.Cid = mem_Payments.CID
WHERE     (mem_Payments.memyear = '2004') AND (members.memberletter = N'a') AND (members.RecordType = N'm') and (members.membernumber = '#getparents.membernumber#')
ORDER BY members.TrueMemNumber, Contacts.Company
    </cfquery>
      <cfloop query="getchild">
      <cfoutput>
      <tr>
      <td colspan="2">
      #truememnumber#
      </td>
      <td width="32%" align="left" valign="top">#Company#<br>
#FullName#<br>
#Address1#<br>
<cfif Address2 neq ''>#address2#<br></cfif>
#City#, #Ucase(State)# #zip#
<cfif Country NEQ 'USA'>
<br> #Ucase(Country)#</cfif></td>
      </tr>
      </cfoutput>
      
      
      </cfloop>
  </cfloop>
</table>

Please EE come to the rescue again.

0
Comment
Question by:jriver12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
James Rodgers earned 500 total points
ID: 12088881
1) the code loks ok except that you don't need the second query, you are pulling the exact same info as you pulled in teh fisrt what you should do is run one query

<cfquery name="getDetails" datasource="uspoultry">
  SELECT     Contacts.Company, Contacts.FullName, Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State, Contacts.zip, Contacts.country,
                      members.membernumber, members.memberletter, members.TrueMemNumber, mem_Payments.Amount, mem_Payments.memyear,
                      members.RecordType
FROM         Contacts INNER JOIN
                      members ON Contacts.Cid = members.Cid INNER JOIN
                      mem_Payments ON Contacts.Cid = mem_Payments.CID
WHERE     (mem_Payments.memyear = '2004') AND (members.memberletter = N'a') AND (members.RecordType = N'm') and (members.membernumber = '#getparents.membernumber#')
ORDER BY members.TrueMemNumber, Contacts.Company
    </cfquery>
 and then in a cfouptut group by the top level fields
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td colspan="4" align="center"><strong>INVOICE</strong></td>
      </tr>
  <tr>
 
    <td align="center" COLSPAN="2">&nbsp;</td>
    <td width="12%" align="center">&nbsp;</td>
    <td width="29%" align="center"><img src="../images/1L4nowords.gif" width="50" height="50"></td>
  </tr>
<cfoutput query="getDetails" group="field1.field2,filed3">
    ><tr>
    <td width="32%" align="left" valign="top">#Company#<br>
#FullName#<br>
#Address1#<br>
<cfif Address2 neq ''>#address2#<br></cfif>
#City#, #Ucase(State)# #zip#<cfif Country NEQ 'USA'><br> #Ucase(Country)#</cfif>
</cfoutput>
</td>
    <td width="27%">&nbsp;</td>
    <td colspan="2" align="right" valign="top"><div align="right" class="style1">U.S. Poultry &amp; Egg Association <br>
        1530 Cooledge Road<br>
        Tucker, GA 30084-7303<br>
        770.493.9401<br>
        Fax: 770.493.9257<br>
        www.poultryegg.org<br>
        EIN:  58-0704657
      </div></td>
  </tr>
  <tr><td colspan="6"><hr color="000000" ></td></tr>
       
   <cfouput query="getDetails">
               <tr>
     <td colspan="2">
     #truememnumber#
     </td>
     <td width="32%" align="left" valign="top">#Company#<br>
#FullName#<br>
#Address1#<br>
<cfif Address2 neq ''>#address2#<br></cfif>
#City#, #Ucase(State)# #zip#
<cfif Country NEQ 'USA'>
<br> #Ucase(Country)#</cfif></td>
     </tr>
   </cfoutput>
</cfoutput>

</table>

2) page breaks are determined by the users local printer setting; if a user has a 1" margin their page breaks will be different than a user with a 1/2" margin look at teh css attribute pageBreakBefore and pageBreakAfter, they might be of use

3) you need an if inside the output to determine when to insert the css from 2 above and this will be dependant upon the page/invoice header for page one and how many llines still need to be printed, you can use queryName.currentRow to determine this
0
 
LVL 17

Expert Comment

by:anandkp
ID: 12092182
0

Featured Post

Schedule a Tour of the ATEN booth at InfoComm 2017

Tour the ATEN booth to see the the Latest Addition to the Modular Matrix Switch Series, New 4K HDMI Over IP Extender and more! Enter ATEN's Ultimate Giveaway Sweepstakes for a chance to win one of several great prizes, including an ATEN US7220 2-Port Thunderbolt 2 Sharing Switch!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question