这个基本包含了所有的特殊情况
- wrap 换行
- title显示在每页的固定位置, 第一页需要特殊处理
<DATA>
<PDF_TITLE Font="5" Size="16" ColorR="0" ColorG="0" ColorB="0" X="20" Y="-30" Text=""/>
<PDF_PAGE_SETTING UsePageDefault="1" PageTop="100" PageLeft="20" RowHeight="10" RowCount="40" PageCase="NOCASE" HideFirstPageHeaders="1" />
<CALC Funct="Add" Name="IsFirstPage" Var1="1" Var2="0"/>
<PDF_HEAD_FONT Font="5" Size="8" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_HEAD_FIELD Text="Policy Number/Insured" X="5" Y="-10" WordWrap="1" Width="60" Height="20"/>
<PDF_HEAD_FIELD Text="Txn" X="100" Y="-10"/>
<PDF_HEAD_FIELD Text="End #" X="120" Y="-10"/>
<PDF_HEAD_FIELD Text="Description" X="150" Y="-10"/>
<PDF_HEAD_FIELD Text="Acct Per'd" X="200" Y="-10"/>
<PDF_HEAD_FIELD Text="Eff Date/ Exp Date" X="250" Y="-10" WordWrap="1" Width="40" Height="20"/>
<PDF_HEAD_FIELD Text="Due Date" X="300" Y="-10"/>
<PDF_HEAD_FIELD Text="Audit NO." X="345" Y="-10"/>
<PDF_HEAD_FIELD Text="Premium" X="395" Y="-10"/>
<PDF_HEAD_FIELD Text="Broker Comm" X="450" Y="-10" WordWrap="1" Width="40" Height="20"/>
<PDF_HEAD_FIELD Text="Surcharges/ Taxes/ Fees" X="500" Y="-10" WordWrap="1" Width="45" Height="30"/>
<PDF_HEAD_FIELD Text="Total Billed" X="555" Y="-10"/>
<PDF_HEAD_FIELD Text="Amount Paid" X="610" Y="-10"/>
<PDF_HEAD_FIELD Text="Net Amount Due" X="670" Y="-10" WordWrap="1" Width="60" Height="30"/>
<PDF_HEAD_FIELD Text="Age(days)" X="720" Y="-10"/>
<!--
<XLS_TITLE Text="Monthly Account Current Statement"/>
<XLS_HEAD_FIELD Text="Line Num." Width="12"/>
<XLS_HEAD_FIELD Text="Agency" Width="12"/>
<XLS_HEAD_FIELD Text="MMYY" Width="12"/>
<XLS_HEAD_FIELD Text="Policy Number" Width="12"/>
<XLS_HEAD_FIELD Text="Effective Date" Width="14"/>
<XLS_HEAD_FIELD Text="Name" Width="5"/>
<XLS_HEAD_FIELD Text="Description" Width="50"/>
<XLS_HEAD_FIELD Text="Comm Rate" Width="5"/>
<XLS_HEAD_FIELD Text="Written Premium" Width="50"/>
<XLS_HEAD_FIELD Text="Commission" Width="50"/>
<XLS_HEAD_FIELD Text="Premium Due to Agency" Width="100"/>
<XLS_HEAD_FIELD Text="Due Date" Width="100"/>
-->
<OPEN_QUERY Name="Agency" Query="
select AA.*
,BrokerCode = T.sCT_Code
,BrokerName = T.sCT_Name
,BrokerAddress = T.sCT_Phys_Address1
,BrokerCityZIP = T.sCT_Phys_City + ' ' + T.sCT_Phys_State + ' ' + T.sCT_Phys_ZIP
from (
select distinct A.AgencyID
,TotalPremium = sum(A.Premium)
,TotalCommission = sum(A.BrokerComm)
,TotalFee = sum(A.SurchargeFee)
,TotalBilledAmount = sum(A.TotalBilled)
,TotalPaidAmount = sum(A.AmountPaid)
,TotalNetAmountDue = sum(A.NetAmountDue)
,Up30Total = sum(A.NetAmountDue)
,BT31_90Total = sum(A.NetAmountDue)
,GT90Total = sum(A.NetAmountDue)
,AllTotal = sum(A.NetAmountDue)+sum(A.NetAmountDue)+sum(A.NetAmountDue)
,A.MY_NO
from vw_monthly_agency_bill as A with ( nolock )
group by
A.AgencyID
,A.MY_NO
) as AA
join System_CompanyTree as T with ( nolock ) on AA.AgencyID = T.sCT_ID
where AA.MY_NO < year('#SProcessing_Date#') * 100 + month('#SProcessing_Date#')
"/>
<BEGIN Name="Agency"/>
<PDF_FIELD_FONT Font="5" Size="12" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="375" Y="-33" Align="CENTER" Text="Broker Statement Bill of Account Due As Of #SProcessing_Date#"/>
<PDF_FIELD_FONT Font="5" Size="10" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="540" Y="-20" Text="Remit check payment to:"/>
<PDF_FIELD_FONT Font="4" Size="10" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="540" Y="-10" Text="Allianz Global Risks US Insurance Company"/>
<PDF_FIELD Format="STRING" X="540" Y="0" Text="P. O. Box 2542"/>
<PDF_FIELD Format="STRING" X="540" Y="10" Text="Carol Stream, IL 60132-2542"/>
<PDF_FIELD_FONT Font="4" Size="10" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="5" Y="30" Text="#DAgency.BrokerName#"/>
<PDF_FIELD Format="STRING" X="5" Y="40" Text="#DAgency.BrokerAddress#"/>
<PDF_FIELD Format="STRING" X="5" Y="50" Text="#DAgency.BrokerCityZIP#"/>
<PDF_FIELD Format="STRING" X="250" Y="30" Text="Broker Code"/>
<PDF_FIELD Format="STRING" X="350" Y="30" Text="#DAgency.BrokerCode#"/>
<PDF_FIELD Format="STRING" X="250" Y="40" Text="Statement Number"/>
<PDF_FIELD Format="STRING" X="350" Y="40" Text="0"/>
<PDF_FIELD_FONT Font="5" Size="10" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="540" Y="30" Text="Allianz Customer Service"/>
<PDF_FIELD_FONT Font="4" Size="10" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="540" Y="40" Text="Contact: 1-800-527-5787"/>
<PDF_FIELD Format="STRING" X="540" Y="50" Text="Email: service@agcs.allianz.com"/>
<!--set header for first page-->
<IF_STAT Name="FP1" Exp1="#SIsFirstPage#" Oper="EQ" Exp2="1"/>
<PDF_FIELD_FONT Font="5" Size="8" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Text="Policy Number/Insured #SIsFirstPage#" X="5" Y="70" WordWrap="1" Width="85" Height="20"/>
<PDF_FIELD Text="Txn" X="100" Y="70"/>
<PDF_FIELD Text="End #" X="120" Y="70"/>
<PDF_FIELD Text="Description" X="150" Y="70"/>
<PDF_FIELD Text="Acct Per'd" X="200" Y="70"/>
<PDF_FIELD Text="Eff Date/ Exp Date" X="250" Y="70" WordWrap="1" Width="40" Height="20"/>
<PDF_FIELD Text="Due Date" X="300" Y="70"/>
<PDF_FIELD Text="Audit NO." X="345" Y="70"/>
<PDF_FIELD Text="Premium" X="395" Y="70"/>
<PDF_FIELD Text="Broker Comm" X="450" Y="70" WordWrap="1" Width="40" Height="20"/>
<PDF_FIELD Text="Surcharges/ Taxes/ Fees" X="500" Y="70" WordWrap="1" Width="45" Height="30"/>
<PDF_FIELD Text="Total Billed" X="555" Y="70"/>
<PDF_FIELD Text="Amount Paid" X="610" Y="70"/>
<PDF_FIELD Text="Net Amount Due" X="670" Y="70" WordWrap="1" Width="60" Height="30"/>
<PDF_FIELD Text="Age(days)" X="720" Y="70"/>
<IF_END Name="FP1" />
<PDF_INC_ROW NumOfRows="7"/>
<!--Hide the first page tile-->
<CALC Funct="Add" Name="IsFirstPage" Var1="0" Var2="0"/>
<OPEN_QUERY Name="Main" Query="
select A.AgencyID
,A.PolicyCode_InsuredName
,A.Txn
,A.End#
,A.Descption
,A.AcctPerd
,A.EffExPDate
,A.DueDate
,A.AuditNO
,A.Premium
,A.BrokerComm
,A.SurchargeFee
,A.TotalBilled
,A.AmountPaid
,A.NetAmountDue
,A.AgeDays
,A.MMYY
,A.MY_NO
from vw_monthly_agency_bill as A with (nolock)
where A.AgencyID = '#DAgency.AgencyID#'
order by
A.MY_NO
"/>
<BEGIN Name="Main"/>
<INC_ROW/>
<PDF_FIELD_FONT Font="4" Size="7" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="5" Y="20" Text="#DMain.PolicyCode_InsuredName#" WordWrap="1" Width="85" Height="999" TextHeight="RowInc"/>
<PDF_FIELD Format="STRING" X="100" Y="20" Text="#DMain.Txn#"/>
<PDF_FIELD Format="STRING" X="120" Y="20" Text="#DMain.End##"/>
<PDF_FIELD Format="STRING" X="150" Y="20" Text="#DMain.Descption#" WordWrap="1" Width="60" Height="20"/>
<PDF_FIELD Format="STRING" X="200" Y="20" Text="#DMain.AcctPerd#"/>
<PDF_FIELD Format="STRING" X="240" Y="20" Text="#DMain.EffExPDate#" WordWrap="1" Width="60" Height="20"/>
<PDF_FIELD Format="STRING" X="300" Y="20" Text="#DMain.DueDate#"/>
<PDF_FIELD Format="STRING" X="340" Y="20" Text="#DMain.AuditNO#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="415" Y="20" Align="right" Text="#DMain.Premium#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="465" Y="20" Align="right" Text="#DMain.BrokerComm#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="540" Y="20" Align="right" Text="#DMain.SurchargeFee#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="590" Y="20" Align="right" Text="#DMain.TotalBilled#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="650" Y="20" Align="right" Text="#DMain.AmountPaid#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="710" Y="20" Align="right" Text="#DMain.NetAmountDue#"/>
<PDF_FIELD Format="STRING" X="720" Y="20" Text="#DMain.AgeDays#"/>
<PDF_INC_ROW NumOfRows="#SRowInc#"/>
<!--
<XLS_FIELD Format="STRING" Text="#DMain.LineNumber#" Width="12"/>
<XLS_FIELD Format="STRING" Text="#SAgency#" Width="12"/>
<XLS_FIELD Format="STRING" Text="#DMain.MMYY#" Width="12"/>
<XLS_FIELD Format="STRING" Text="#DMain.aPD_PolicyCode#" Width="5"/>
<XLS_FIELD Format="STRING" Text="#DMain.PD_EffectiveDate#" Width="50"/>
<XLS_FIELD Format="STRING" Text="#DMain.InsuredName#" Width="100"/>
<XLS_FIELD Format="STRING" Text="#DMain.Description#" Width="12"/>
<XLS_FIELD Format="STRING" Text="#DMain.CommRate#%" Width="12"/>
<XLS_FIELD Format="STRING" Text="#DMain.Premium#" Width="50"/>
<XLS_FIELD Format="STRING" Text="#DMain.Commission#" Width="100"/>
<XLS_FIELD Format="STRING" Text="#DMain.DueAmt#" Width="50"/>
<XLS_FIELD Format="STRING" Text="#DMain.DueDate#" Width="100"/>
-->
<END GoTo="Main"/>
<CLOSE_QUERY Name="Main"/>
<PDF_INC_ROW/>
<PDF_INC_ROW/>
<PDF_INC_ROW/>
<PDF_FIELD_FONT Font="5" Size="7" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="300" Y="10" Text="Total Amount Due"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="420" Y="10" Align="right" Text="#DAgency.TotalPremium#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="470" Y="10" Align="right" Text="#DAgency.TotalCommission#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="550" Y="10" Align="right" Text="#DAgency.TotalFee#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="600" Y="10" Align="right" Text="#DAgency.TotalBilledAmount#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="660" Y="10" Align="right" Text="#DAgency.TotalPaidAmount#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="700" Y="10" Align="right" Text="#DAgency.TotalNetAmountDue#"/>
<PDF_INC_ROW/>
<PDF_INC_ROW/>
<PDF_INC_ROW/>
<PDF_INC_ROW/>
<PDF_FIELD_FONT Font="5" Size="8" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="5" Y="0" Text="Transaction (Txn)" Align="left"/>
<PDF_FIELD Format="STRING" X="380" Y="0" Text="0-30 Days Past Due" Align="right"/>
<PDF_FIELD Format="STRING" X="480" Y="0" Text="31-90 Days Past Due" Align="right"/>
<PDF_FIELD Format="STRING" X="580" Y="0" Text="Over 90 Days Past Due" Align="right"/>
<PDF_FIELD Format="STRING" X="650" Y="0" Text="Total" Align="right"/>
<PDF_INC_ROW/>
<PDF_FIELD_FONT Font="4" Size="7" ColorR="0" ColorG="0" ColorB="0"/>
<PDF_FIELD Format="STRING" X="5" Y="10" Text="INS"/>
<PDF_FIELD Format="STRING" X="5" Y="20" Text="CAN"/>
<PDF_FIELD Format="STRING" X="5" Y="30" Text="NEW"/>
<PDF_FIELD Format="STRING" X="5" Y="40" Text="END"/>
<PDF_FIELD Format="STRING" X="5" Y="50" Text="PRM"/>
<PDF_FIELD Format="STRING" X="125" Y="10" Text="Instalment Premium"/>
<PDF_FIELD Format="STRING" X="125" Y="20" Text="Cancellation Premium"/>
<PDF_FIELD Format="STRING" X="125" Y="30" Text="New Business Premium"/>
<PDF_FIELD Format="STRING" X="125" Y="40" Text="Endorsement Premium"/>
<PDF_FIELD Format="STRING" X="125" Y="50" Text="Premium"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="380" Y="10" Align="right" Text="#DAgency.Up30Total#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="480" Y="10" Align="right" Text="#DAgency.BT31_90Total#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="580" Y="10" Align="right" Text="#DAgency.GT90Total#"/>
<PDF_FIELD Format="FLOAT" FormatStr="%1.2m" ReplaceNull="$0.00" X="650" Y="10" Align="right" Text="#DAgency.AllTotal#"/>
<PDF_INC_ROW/>
<PDF_INC_ROW/>
<PDF_NEWPAGE HideHeader="1"/>
<CALC Funct="Add" Name="IsFirstPage" Var1="1" Var2="0"/>
<END GoTo="Agency"/>
<CLOSE_QUERY Name="Agency"/>
</DATA>