Solved

need help with creating a page breaking invoice.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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