Solved

need help with creating a page breaking invoice.

Posted on 2004-09-17
2
187 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
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 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