r5756 - in branches/bmas-staging/data: report schema/mysql/reporting

0 views

Skip to first unread message

subve...@svn.freemedsoftware.org

unread,

Jul 28, 2010, 9:34:42 AM7/28/10

to freemed-d...@googlegroups.com

Author: sadnan
Date: 2010-07-28 09:34:42 -0400 (Wed, 28 Jul 2010)
New Revision: 5756

Modified:
branches/bmas-staging/data/report/MonthlyMasterJournalDetail_en_US.jrxml
branches/bmas-staging/data/report/MonthlyMasterJournalSummary_en_US.jrxml
branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalDetail.sql
branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalSummary.sql
Log:

Modified: branches/bmas-staging/data/report/MonthlyMasterJournalDetail_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/MonthlyMasterJournalDetail_en_US.jrxml 2010-07-28 12:26:10 UTC (rev 5755)
+++ branches/bmas-staging/data/report/MonthlyMasterJournalDetail_en_US.jrxml 2010-07-28 13:34:42 UTC (rev 5756)
@@ -8,7 +8,7 @@
<parameter name="param1" class="java.util.Date"/>
<parameter name="param2" class="java.lang.Integer"/>
<queryString>
- <![CDATA[call report_MonthlyMasterJournalDetail_en_US($P{param0} ,$P{param1},$P{param2});]]>
+ <![CDATA[call report_MonthlyMasterJournalDetail_en_US('2010-01-01','2010-07-31',1);]]>
</queryString>
<field name="TypeID" class="java.lang.Long">
<fieldDescription><![CDATA[]]></fieldDescription>
@@ -37,7 +37,7 @@
<field name="Units" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="Deffered Amount" class="java.lang.Double">
+ <field name="Deferred Amount" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
<field name="Actual Total" class="java.lang.Double">
@@ -413,7 +413,7 @@
<textField pattern="###0.00">
<reportElement style="Detail" x="480" y="0" width="50" height="15"/>
<textElement textAlignment="Right"/>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{Deffered Amount}]]></textFieldExpression>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{Deferred Amount}]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
<reportElement style="Detail" x="534" y="1" width="50" height="15"/>

Modified: branches/bmas-staging/data/report/MonthlyMasterJournalSummary_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/MonthlyMasterJournalSummary_en_US.jrxml 2010-07-28 12:26:10 UTC (rev 5755)
+++ branches/bmas-staging/data/report/MonthlyMasterJournalSummary_en_US.jrxml 2010-07-28 13:34:42 UTC (rev 5756)
@@ -4,57 +4,99 @@
<style name="SubTitle" isDefault="false" forecolor="#666666" fontName="Arial" fontSize="18"/>
<style name="Column header" isDefault="false" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
<style name="Detail" isDefault="false" fontName="Arial" fontSize="12"/>
- <queryString language="SQL">
- <![CDATA[call report_MonthlyMasterJournalSummary_en_US('2010-01-01','2010-07-20',1)]]>
+ <parameter name="param0" class="java.util.Date"/>
+ <parameter name="param1" class="java.util.Date"/>
+ <parameter name="param2" class="java.lang.Integer"/>
+ <queryString>
+ <![CDATA[call report_MonthlyMasterJournalSummary_en_US($P{param0} ,$P{param1},$P{param2})]]>
</queryString>
- <field name="Type" class="java.lang.String">
+ <field name="type" class="java.lang.Long">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="TypeID" class="java.lang.Long">
+ <field name="3rd Party" class="java.lang.String">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="InsType" class="java.lang.Long">
+ <field name="sales_intake" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="ThirdParty" class="java.lang.String">
+ <field name="sales_labfee" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="Intake" class="java.lang.Long">
+ <field name="sales_discharge" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="Lab Fees" class="java.lang.Double">
+ <field name="sales_methadone" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="Discharge" class="java.lang.Long">
+ <field name="sales_other" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="METHODONE" class="java.lang.Double">
+ <field name="sales_allw_intake" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <field name="Other" class="java.lang.Double">
+ <field name="sales_allw_labfee" class="java.lang.Double">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
- <group name="ThirdParty">
- <groupExpression><![CDATA[$F{ThirdParty}]]></groupExpression>
- <groupHeader>
- <band height="20"/>
- </groupHeader>
- <groupFooter>
- <band height="5">
- <line>
- <reportElement positionType="FixRelativeToBottom" x="0" y="1" width="760" height="1"/>
- <graphicElement>
- <pen lineWidth="0.5" lineColor="#999999"/>
- </graphicElement>
- </line>
- </band>
- </groupFooter>
- </group>
+ <field name="sales_allw_discharge" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="sales_allw_methadone" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="sale_all_other" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_intake" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_labfee" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_discharge" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_methadone" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_other" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_allw_intake" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_allw_labfee" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_allw_discharge" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsales_allw_methadone" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="dfsale_all_other" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <field name="cash_collected" class="java.lang.Double">
+ <fieldDescription><![CDATA[]]></fieldDescription>
+ </field>
+ <variable name="defrSalesTotal" class="java.lang.Double" resetType="None">
+ <variableExpression><![CDATA[$F{dfsales_intake}.doubleValue()+$F{dfsales_labfee}.doubleValue()+$F{dfsales_discharge}.doubleValue()+$F{dfsales_methadone}.doubleValue()+$F{dfsales_other}.doubleValue()]]></variableExpression>
+ <initialValueExpression><![CDATA[]]></initialValueExpression>
+ </variable>
+ <variable name="defrAllwTotal" class="java.lang.Double" resetType="None">
+ <variableExpression><![CDATA[0.0-$F{dfsales_allw_intake}.doubleValue()-$F{dfsales_allw_labfee}.doubleValue()-$F{dfsales_allw_discharge}.doubleValue()-$F{dfsales_allw_methadone}.doubleValue()-$F{dfsale_all_other}.doubleValue()]]></variableExpression>
+ </variable>
+ <variable name="salesTotal" class="java.lang.Double" resetType="None">
+ <variableExpression><![CDATA[$F{sales_intake}.doubleValue()+$F{sales_labfee}.doubleValue()+$F{sales_discharge}.doubleValue()+$F{sales_methadone}.doubleValue()+$F{sales_other}.doubleValue()]]></variableExpression>
+ </variable>
+ <variable name="allwTotal" class="java.lang.Double" resetType="None">
+ <variableExpression><![CDATA[0.0-$F{sales_allw_intake}.doubleValue()-$F{sales_allw_labfee}.doubleValue()-$F{sales_allw_discharge}.doubleValue()-$F{sales_allw_methadone}.doubleValue()-$F{sale_all_other}.doubleValue()]]></variableExpression>
+ </variable>
<background>
<band splitType="Stretch"/>
</background>
<title>
- <band height="90" splitType="Stretch"/>
+ <band splitType="Stretch"/>
</title>
<pageHeader>
<band height="90" splitType="Stretch">
@@ -63,7 +105,7 @@
<textElement textAlignment="Center" verticalAlignment="Middle">

</textElement>
- <text><![CDATA[Master Journal Detail Report]]></text>
+ <text><![CDATA[Master Journal Summary Report]]></text>
</staticText>
<staticText>
<reportElement positionType="Float" x="399" y="40" width="34" height="21"/>
@@ -100,6 +142,20 @@
</textElement>
<textFieldExpression class="java.util.Date"><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
+ <textField pattern="MM/dd/yyyy">
+ <reportElement x="324" y="40" width="75" height="20"/>
+ <textElement textAlignment="Left" verticalAlignment="Middle">
+ 
+ </textElement>
+ <textFieldExpression class="java.util.Date"><![CDATA[$P{param0}]]></textFieldExpression>
+ </textField>
+ <textField pattern="MM/dd/yyyy">
+ <reportElement x="433" y="40" width="75" height="20"/>
+ <textElement textAlignment="Left" verticalAlignment="Middle">
+ 
+ </textElement>
+ <textFieldExpression class="java.util.Date"><![CDATA[$P{param1}]]></textFieldExpression>
+ </textField>
</band>
</pageHeader>
<columnHeader>
@@ -110,50 +166,47 @@
<text><![CDATA[Type]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="80" y="19" width="117" height="15"/>
+ <reportElement style="Column header" x="100" y="19" width="117" height="15"/>
<textElement/>
<text><![CDATA[3rd Party]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="211" y="19" width="61" height="15"/>
+ <reportElement style="Column header" x="223" y="19" width="61" height="15"/>
<textElement textAlignment="Right"/>
<text><![CDATA[Intake]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="281" y="19" width="61" height="15"/>
+ <reportElement style="Column header" x="293" y="19" width="61" height="15"/>
<textElement textAlignment="Right"/>
<text><![CDATA[Lab Fees]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="351" y="19" width="61" height="15"/>
+ <reportElement style="Column header" x="363" y="19" width="61" height="15"/>
<textElement textAlignment="Right"/>
<text><![CDATA[Discharge]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="418" y="19" width="69" height="15"/>
+ <reportElement style="Column header" x="430" y="19" width="69" height="15"/>
<textElement textAlignment="Right"/>
<text><![CDATA[Methadone]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="494" y="19" width="61" height="15"/>
+ <reportElement style="Column header" x="506" y="19" width="61" height="15"/>
<textElement textAlignment="Right"/>
<text><![CDATA[Other]]></text>
</staticText>
<staticText>
- <reportElement style="Column header" x="564" y="19" width="61" height="15"/>
+ <reportElement style="Column header" x="575" y="19" width="50" height="15"/>
<textElement textAlignment="Right"/>
<text><![CDATA[Total]]></text>
</staticText>
- <line>
- <reportElement x="216" y="14" width="167" height="1"/>
- </line>
<staticText>
<reportElement style="Column header" x="391" y="0" width="42" height="15"/>
<textElement textAlignment="Center"/>
<text><![CDATA[Sales]]></text>
</staticText>
<line>
- <reportElement x="425" y="14" width="200" height="1"/>
+ <reportElement x="223" y="17" width="402" height="1"/>
</line>
<staticText>
<reportElement style="Column header" x="633" y="4" width="55" height="30"/>
@@ -168,85 +221,289 @@
</band>
</columnHeader>
<detail>
- <band height="16" splitType="Stretch">
+ <band height="105" splitType="Stretch">
+ <staticText>
+ <reportElement x="0" y="7" width="88" height="15"/>
+ <textElement>
+ 
+ </textElement>
+ <text><![CDATA[Defr Sales]]></text>
+ </staticText>
+ <staticText>
+ <reportElement x="0" y="22" width="88" height="15"/>
+ <textElement>
+ 
+ </textElement>
+ <text><![CDATA[Defr Allowances]]></text>
+ </staticText>
+ <staticText>
+ <reportElement x="0" y="43" width="88" height="15"/>
+ <textElement>
+ 
+ </textElement>
+ <text><![CDATA[Sales]]></text>
+ </staticText>
+ <staticText>
+ <reportElement x="0" y="58" width="88" height="15"/>
+ <textElement>
+ 
+ </textElement>
+ <text><![CDATA[Allowances]]></text>
+ </staticText>
<textField>
- <reportElement style="Detail" x="0" y="0" width="61" height="15"/>
- <textElement/>
- <textFieldExpression class="java.lang.String"><![CDATA[$F{Type}]]></textFieldExpression>
+ <reportElement x="100" y="7" width="117" height="15"/>
+ <textElement>
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.String"><![CDATA[$F{3rd Party}]]></textFieldExpression>
</textField>
- <textField>
- <reportElement style="Detail" isPrintRepeatedValues="false" x="80" y="0" width="117" height="15"/>
- <textElement/>
- <textFieldExpression class="java.lang.String"><![CDATA[$F{ThirdParty}]]></textFieldExpression>
+ <textField pattern="###0.00">
+ <reportElement x="224" y="7" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_intake}]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
- <reportElement style="Detail" x="219" y="0" width="61" height="15"/>
- <textElement textAlignment="Right"/>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{Intake}]]></textFieldExpression>
+ <reportElement x="294" y="7" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_labfee}]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
- <reportElement style="Detail" x="289" y="0" width="61" height="15"/>
- <textElement textAlignment="Right"/>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{Lab Fees}]]></textFieldExpression>
+ <reportElement x="364" y="7" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_discharge}]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
- <reportElement style="Detail" x="359" y="0" width="61" height="15"/>
- <textElement textAlignment="Right"/>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{Discharge}]]></textFieldExpression>
+ <reportElement x="431" y="7" width="69" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_methadone}]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
- <reportElement style="Detail" x="426" y="0" width="69" height="15"/>
- <textElement textAlignment="Right"/>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{METHODONE}]]></textFieldExpression>
+ <reportElement x="507" y="7" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_other}]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
- <reportElement style="Detail" x="502" y="0" width="61" height="15"/>
- <textElement textAlignment="Right"/>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{Other}]]></textFieldExpression>
+ <reportElement x="224" y="22" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_allw_intake}.doubleValue()!=0?$F{dfsales_allw_intake}.doubleValue()*-1.0:0.0]]></textFieldExpression>
</textField>
<textField pattern="###0.00">
- <reportElement x="572" y="0" width="61" height="15"/>
+ <reportElement x="294" y="22" width="61" height="15"/>
<textElement textAlignment="Right">

</textElement>
- <textFieldExpression class="java.lang.Double"><![CDATA[$F{Intake}+$F{Lab Fees}+$F{Discharge}+$F{METHODONE}+$F{Other}]]></textFieldExpression>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_allw_labfee}.doubleValue()!=0?$F{dfsales_allw_labfee}.doubleValue()*-1.0:0.0]]></textFieldExpression>
</textField>
- </band>
- </detail>
- <columnFooter>
- <band height="45" splitType="Stretch">
+ <textField pattern="###0.00">
+ <reportElement x="364" y="22" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_allw_discharge}.doubleValue()!=0?$F{dfsales_allw_discharge}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="431" y="22" width="69" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsales_allw_methadone}.doubleValue()!=0?$F{dfsales_allw_methadone}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="507" y="22" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{dfsale_all_other}.doubleValue()!=0?$F{dfsale_all_other}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
<line>
- <reportElement positionType="FixRelativeToBottom" x="0" y="3" width="555" height="1"/>
+ <reportElement positionType="FixRelativeToBottom" x="217" y="75" width="543" height="1"/>
<graphicElement>
<pen lineWidth="0.5" lineColor="#999999"/>
</graphicElement>
</line>
- </band>
- </columnFooter>
- <pageFooter>
- <band height="20" splitType="Stretch">
- <textField>
- <reportElement style="Column header" x="433" y="0" width="80" height="20"/>
+ <staticText>
+ <reportElement x="0" y="79" width="88" height="15"/>
+ <textElement>
+ 
+ </textElement>
+ <text><![CDATA[Net]]></text>
+ </staticText>
+ <textField pattern="###0.00">
+ <reportElement x="224" y="43" width="61" height="15"/>
<textElement textAlignment="Right">
- 
+ 
</textElement>
- <textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_intake}]]></textFieldExpression>
</textField>
- <textField evaluationTime="Report">
- <reportElement style="Column header" x="513" y="0" width="40" height="20"/>
- <textElement>
- 
+ <textField pattern="###0.00">
+ <reportElement x="294" y="43" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
</textElement>
- <textFieldExpression class="java.lang.String"><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_labfee}]]></textFieldExpression>
</textField>
- <textField pattern="EEEEE dd MMMMM yyyy">
- <reportElement style="Column header" x="0" y="0" width="197" height="20"/>
- <textElement>
- 
+ <textField pattern="###0.00">
+ <reportElement x="364" y="43" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
</textElement>
- <textFieldExpression class="java.util.Date"><![CDATA[new java.util.Date()]]></textFieldExpression>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_discharge}]]></textFieldExpression>
</textField>
+ <textField pattern="###0.00">
+ <reportElement x="431" y="43" width="69" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_methadone}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="507" y="43" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_other}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="224" y="58" width="61" height="14"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_allw_intake}.doubleValue()!=0?$F{sales_allw_intake}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="294" y="58" width="61" height="14"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_allw_labfee}.doubleValue()!=0?$F{sales_allw_labfee}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="364" y="58" width="61" height="14"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_allw_discharge}.doubleValue()!=0?$F{sales_allw_discharge}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="431" y="58" width="69" height="14"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_allw_methadone}.doubleValue()!=0?$F{sales_allw_methadone}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="507" y="58" width="61" height="14"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sale_all_other}.doubleValue()!=0?$F{sale_all_other}.doubleValue()*-1.0:0.0]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="224" y="79" width="60" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_intake}.doubleValue()-$F{sales_allw_intake}.doubleValue()]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="293" y="79" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_labfee}.doubleValue()-$F{sales_allw_labfee}.doubleValue()]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="363" y="79" width="60" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_discharge}.doubleValue()-$F{sales_allw_discharge}.doubleValue()]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="506" y="79" width="61" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_other}.doubleValue()-$F{sale_all_other}.doubleValue()]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="430" y="79" width="68" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{sales_methadone}.doubleValue()-$F{sales_allw_methadone}.doubleValue()]]></textFieldExpression>
+ </textField>
+ <line>
+ <reportElement x="0" y="103" width="760" height="1"/>
+ </line>
+ <textField pattern="###0.00">
+ <reportElement x="579" y="7" width="46" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$V{defrSalesTotal}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="579" y="22" width="46" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$V{defrAllwTotal}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="579" y="43" width="46" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$V{salesTotal}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="579" y="58" width="46" height="14"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$V{allwTotal}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="579" y="79" width="46" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$V{salesTotal}.doubleValue()+$V{allwTotal}.doubleValue()]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="633" y="79" width="55" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$F{cash_collected}]]></textFieldExpression>
+ </textField>
+ <textField pattern="###0.00">
+ <reportElement x="705" y="79" width="55" height="15"/>
+ <textElement textAlignment="Right">
+ 
+ </textElement>
+ <textFieldExpression class="java.lang.Double"><![CDATA[$V{salesTotal}.doubleValue()+$V{allwTotal}.doubleValue()-$F{cash_collected}.doubleValue()]]></textFieldExpression>
+ </textField>
</band>
+ </detail>
+ <columnFooter>
+ <band splitType="Stretch"/>
+ </columnFooter>
+ <pageFooter>
+ <band height="20" splitType="Stretch"/>
</pageFooter>
<summary>
<band splitType="Stretch"/>

Modified: branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalDetail.sql
===================================================================
--- branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalDetail.sql 2010-07-28 12:26:10 UTC (rev 5755)
+++ branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalDetail.sql 2010-07-28 13:34:42 UTC (rev 5756)
@@ -44,29 +44,45 @@
"IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') AS 'dosstart', ",
"IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') AS 'dosend', ",
"p.procunits as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid !=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid !=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid !=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid !=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Date Total',",
"f.psrname AS 'Facility Name' ",
"FROM facility f,procrec p,payrec pr,coverage c, insco i ",
@@ -74,13 +90,14 @@
"pr.payreccat IN (1,2,7,9,12) AND ",
"p.proccurcovid !=0 AND ",
"p.id=pr.payrecproc AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
"c.id = p.proccurcovid AND ",
"i.id = c.covinsco AND ",
"p.procpos=",facID," AND ",
"p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- "HAVING ((TransactionDate>='",startDate,"' AND TransactionDate<='",endDate,"') OR ",
- "(MAX(p.procdt)>='",startDate,"' AND MAX(p.procdt)<='",endDate,"')) ) ",
- "UNION ",
+ " ) ",
+ "UNION ALL ",
"(SELECT ",
"1 as TypeID,",
"'Sales Allowance' AS 'Type',",
@@ -96,24 +113,28 @@
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid =0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
"(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid =0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
"(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid =0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
"(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
"WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proccurcovid =0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Date Total',",
"f.psrname AS 'Facility Name' ",
"FROM facility f,procrec p,payrec pr ",
@@ -121,16 +142,17 @@
"pr.payreccat IN (1,2,7,9,12) AND ",
"p.proccurcovid =0 AND ",
"p.id=pr.payrecproc AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
"p.procpos=",facID," AND ",
"p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- "HAVING ((TransactionDate>='",startDate,"' AND TransactionDate<='",endDate,"') OR ",
- "(MAX(p.procdt)>='",startDate,"' AND MAX(p.procdt)<='",endDate,"')) ) ",
- "UNION ",
+ " ) ",
+ "UNION ALL ",
"(SELECT ",
"2 as TypeID,",
"'Sales' AS 'Type',",
- "CASE WHEN (i.insconame IS NOT NULL) THEN '3rd Party Adj.' ELSE 'Maintenance' END AS 'Category', ",
- "CASE WHEN (i.insconame IS NOT NULL) THEN 1 ELSE 3 END AS cattypeid, ",
+ "'3rd Party Adj.' AS 'Category', ",
+ "1 AS cattypeid, ",
"IFNULL(i.insconame,'') AS thirdParty, ",
"pr.payrecdt AS 'TransactionDate',",
"CASE WHEN (i.insconame IS NOT NULL) ",
@@ -138,36 +160,57 @@
"CASE WHEN (i.insconame IS NOT NULL) ",
" THEN IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
"p.procunits as Units,",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(p1.procbalorig),0) FROM payrec pr1,procrec p1 ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(p1.procbalorig),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Date Total',",
"f.psrname AS 'Facility Name' ",
"FROM facility f,payrec pr,procrec p ",
"LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
"LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
"WHERE ",
- "pr.payrecproc = p.id AND pr.payreccat=5 AND ",
+ "pr.payrecproc = p.id AND pr.payreccat=5 AND p.proccurcovid!=0 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
"p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- "HAVING ((TransactionDate>='",startDate,"' AND TransactionDate<='",endDate,"') OR ",
- "(MAX(p.procdt)>='",startDate,"' AND MAX(p.procdt)<='",endDate,"')) ) ",
+ "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
+ ") ",
"UNION ALL ",
"(SELECT ",
"2 as TypeID,"
@@ -184,24 +227,28 @@
"p1.id=pr1.payrecproc AND ",
"p1.proclabcharges>0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
"(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proclabcharges>0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
"(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proclabcharges>0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
"(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
"WHERE pr1.payreccat=5 AND ",
"p1.id=pr1.payrecproc AND ",
"p1.proclabcharges>0 AND ",
"pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
"p1.procdtend<='",endDate,"' ) AS 'Date Total',",
"f.psrname AS 'Facility Name' ",
"FROM facility f,payrec pr,procrec p ",
@@ -209,51 +256,126 @@
"LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
"WHERE ",
"pr.payrecproc = p.id AND pr.payreccat=5 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
"p.proclabcharges>0 AND ",
"p.procpos=",facID," AND ",
"p.procpos=f.id GROUP BY TransactionDate ",
- "HAVING ((TransactionDate>='",startDate,"' AND TransactionDate<='",endDate,"') OR ",
- "(MAX(p.procdt)>='",startDate,"' AND MAX(p.procdt)<='",endDate,"')) ) ",
+ " ) ",
"UNION ALL ",
"(SELECT ",
+ "2 as TypeID,",
+ "'Sales' AS 'Type',",
+ "'Maintenance' AS 'Category', ",
+ "3 AS cattypeid, ",
+ "'' AS thirdParty, ",
+ "pr.payrecdt AS 'TransactionDate',",
+ "CASE WHEN (i.insconame IS NOT NULL) ",
+ " THEN IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosstart', ",
+ "CASE WHEN (i.insconame IS NOT NULL) ",
+ " THEN IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
+ "p.procunits as Units,",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid=0 AND "
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(p1.procbalorig),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid=0 AND "
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid=0 AND "
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid=0 AND "
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
+ "f.psrname AS 'Facility Name' ",
+ "FROM facility f,payrec pr,procrec p ",
+ "LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
+ "LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
+ "WHERE ",
+ "pr.payrecproc = p.id AND pr.payreccat=5 AND p.proccurcovid=0 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
+ "p.procpos=",facID," AND ",
+ "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
+ " ) ",
+ "UNION ALL ",
+ #3rd party
+ "(SELECT ",
"3 as TypeID,"
"'Cash Receipts' AS 'Type',"
- "CASE WHEN (pr.payreccat=0 AND pr.payreclink!=0 AND cp.cptcode!='80101' ) ",
- "Then '3rd Party Pmt' ",
- "WHEN (pr.payreccat=0 AND pr.payreclink=0 AND cp.cptcode!='80101' ) ",
- "THEN 'Cash' ",
- "WHEN (pr.payreccat=11 AND pr.payreclink!=0) ",
- "THEN 'Co-Pay Rcvd'",
- "WHEN (pr.payreccat=0 AND cp.cptcode='80101' ) ",
- "THEN 'UA/BSN' ELSE '' END ",
- "AS 'Category', ",
- "CASE WHEN (pr.payreccat=0 AND pr.payreclink!=0 AND cp.cptcode!='80101' ) ",
- "Then 1 ",
- "WHEN (pr.payreccat=0 AND pr.payreclink=0 AND cp.cptcode!='80101' ) ",
- "THEN 2 ",
- "WHEN (pr.payreccat=11 AND pr.payreclink!=0) ",
- "THEN 3 ",
- "WHEN (pr.payreccat=0 AND cp.cptcode='80101' ) ",
- "THEN 4 ELSE 5 END ",
- "AS 'cattypeid', ",
- "CASE WHEN (pr.payreccat=0 AND pr.payreclink!=0 AND cp.cptcode!='80101' ) ",
- "THEN i.insconame ELSE '' END AS thirdParty, ",
+ "'3rd Party Pmt' AS 'Category', ",
+ "1 AS 'cattypeid', ",
+ "i.insconame AS thirdParty, ",
"pr.payrecdtadd AS 'TransactionDate',",
"CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(p.procdt, '%m/%d/%Y'),'') ELSE '' END AS 'dosstart', ",
+ " THEN IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosstart', ",
"CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(p.procdtend, '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
+ " THEN IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
"1 as Units,",
- "CASE WHEN (p.procdt<='",endDate,"' AND p.procdtend>='",endDate,"' AND ",
- "pr.payreccat=0 AND pr.payreclink!=0 AND cp.cptcode!='80101') ",
- "THEN SUM(pr.payrecamt) ELSE 0 END ",
- "AS 'Deffered Amount',",
- "CASE WHEN (p.procdt<='",endDate,"' AND p.procdtend>='",endDate,"' AND ",
- "pr.payreccat=0 AND pr.payreclink!=0 AND cp.cptcode!='80101') ",
- "THEN 0 ELSE SUM(pr.payrecamt) END ",
- "AS 'Actual Total',",
- "pr.payrecamt AS 'Batch Total',",
- "pr.payrecamt AS 'Date Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink!=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "pr1.payreclink=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink!=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "pr1.payreclink=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink!=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "pr1.payreclink=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink!=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "pr1.payreclink=c1.id AND ",
+ "c1.covinsco=i1.id AND "
+ "i1.id=i.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
"f.psrname AS 'Facility Name' ",
"FROM facility f,payrec pr,procrec p ",
"LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
@@ -261,24 +383,180 @@
"LEFT OUTER JOIN cpt cp ON cp.id = p.proccpt ",
"WHERE ",
"pr.payrecproc=p.id AND ",
- "("
- "( ",
- "pr.payreccat=0 AND pr.payreclink!=0 ",
- ") ",
- " OR ",
- "(",
- "pr.payreccat=0 AND pr.payreclink=0 ",
- ") ",
- " OR ",
- "(",
- "pr.payreccat=11 AND pr.payreclink!=0 ",
- ") ",
- ") AND ",
+ "pr.payreccat=0 AND ",
+ "pr.payreclink!=0 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
+ "cp.id = p.proccpt AND ",
+ "cp.cptcode!='80101' AND "
"p.procpos=",facID," AND ",
"p.procpos=f.id GROUP BY TransactionDate ",
- "HAVING ((TransactionDate>='",startDate,"' AND TransactionDate<='",endDate,"') OR ",
- "(MAX(p.procdt)>='",startDate,"' AND MAX(p.procdt)<='",endDate,"')) ",
- ") ) data ORDER BY data.TypeID,data.cattypeid,data.thirdParty "
+ ")",
+
+ "UNION ALL ",
+ #CASH
+ "(SELECT ",
+ "3 as TypeID,"
+ "'Cash Receipts' AS 'Type',"
+ "'Cash' AS 'Category', ",
+ "2 AS 'cattypeid', ",
+ "'' AS thirdParty, ",
+ "pr.payrecdt AS 'TransactionDate',",
+ "'' AS 'dosstart', ",
+ "'' AS 'dosend', ",
+ "1 as Units,",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payreclink=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) 'Date Total',",
+ "f.psrname AS 'Facility Name' ",
+ "FROM facility f,payrec pr,procrec p,cpt cp ",
+ "WHERE ",
+ "pr.payrecproc=p.id AND ",
+ "cp.id = p.proccpt AND ",
+ "pr.payreccat=0 AND ",
+ "pr.payreclink=0 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
+ "cp.cptcode!='80101' AND "
+ "p.procpos=",facID," AND ",
+ "p.procpos=f.id GROUP BY TransactionDate ",
+ ") ",
+ "UNION ALL ",
+ #Copay
+ "(SELECT ",
+ "3 as TypeID,"
+ "'Cash Receipts' AS 'Type',"
+ "'Co-Pay Rcvd' AS 'Category', ",
+ "3 AS 'cattypeid', ",
+ "'' AS thirdParty, ",
+ "pr.payrecdt AS 'TransactionDate',",
+ "'' AS 'dosstart', ",
+ "'' AS 'dosend', ",
+ "1 as Units,",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=11 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=11 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=11 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=11 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) 'Date Total',",
+ "f.psrname AS 'Facility Name' ",
+ "FROM facility f,payrec pr,procrec p,cpt cp ",
+ "WHERE ",
+ "pr.payrecproc=p.id AND ",
+ "cp.id = p.proccpt AND ",
+ "pr.payreccat=11 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
+ "p.procpos=",facID," AND ",
+ "p.procpos=f.id GROUP BY TransactionDate ",
+ ") "
+ "UNION ALL ",
+ #UA/BSN
+ "(SELECT ",
+ "3 as TypeID,"
+ "'Cash Receipts' AS 'Type',"
+ "'UA/BSN' AS 'Category', ",
+ "4 AS 'cattypeid', ",
+ "'' AS thirdParty, ",
+ "pr.payrecdt AS 'TransactionDate',",
+ "'' AS 'dosstart', ",
+ "'' AS 'dosend', ",
+ "1 as Units,",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' ) AS 'Deferred Amount',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payreccat=0 AND ",
+ "pr1.payrecdt=pr.payrecdt AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode='80101' AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' ) 'Date Total',",
+ "f.psrname AS 'Facility Name' ",
+ "FROM facility f,payrec pr,procrec p,cpt cp ",
+ "WHERE ",
+ "pr.payrecproc=p.id AND ",
+ "cp.id = p.proccpt AND ",
+ "pr.payreccat=0 AND ",
+ "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
+ "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
+ "cp.cptcode='80101' AND "
+ "p.procpos=",facID," AND ",
+ "p.procpos=f.id GROUP BY TransactionDate ",
+ ") ) data ORDER BY data.TypeID,data.cattypeid,data.thirdParty,TransactionDate"
);
PREPARE s FROM @sql ;
EXECUTE s ;

Modified: branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalSummary.sql
===================================================================
--- branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalSummary.sql 2010-07-28 12:26:10 UTC (rev 5755)
+++ branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyMasterJournalSummary.sql 2010-07-28 13:34:42 UTC (rev 5756)
@@ -32,536 +32,182 @@

CREATE PROCEDURE report_MonthlyMasterJournalSummary_en_US ( IN startDate DATE,IN endDate DATE,IN facID INT)
BEGIN
- SET @sql = CONCAT(
- "SELECT * FROM (",
- "(SELECT ",
- "1 as TypeID,",
- "'Sales Allowance' AS 'Type',",
- "'3rd Party Adj.' AS 'Category', ",
- "1 cattypeid, ",
- "IFNULL(i.insconame,'') AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') AS 'dosstart', ",
- "IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') AS 'dosend', ",
- "p.procunits as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid !=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid !=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid !=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid !=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,procrec p,payrec pr,coverage c, insco i ",
- "WHERE ",
- "pr.payreccat IN (1,2,7,9,12) AND ",
- "p.proccurcovid !=0 AND ",
- "p.id=pr.payrecproc AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "c.id = p.proccurcovid AND ",
- "i.id = c.covinsco AND ",
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- " ) ",
- "UNION ALL ",
- "(SELECT ",
- "1 as TypeID,",
- "'Sales Allowance' AS 'Type',",
- "'Non 3rd Party Adj.' AS 'Category', ",
- "2 cattypeid, ",
- "'' AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') AS 'dosstart', ",
- "IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') AS 'dosend', ",
- "p.procunits as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid =0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid =0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid =0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid =0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,procrec p,payrec pr ",
- "WHERE ",
- "pr.payreccat IN (1,2,7,9,12) AND ",
- "p.proccurcovid =0 AND ",
- "p.id=pr.payrecproc AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- " ) ",
- "UNION ALL ",
- "(SELECT ",
- "2 as TypeID,",
- "'Sales' AS 'Type',",
- "'3rd Party Adj.' AS 'Category', ",
- "1 AS cattypeid, ",
- "IFNULL(i.insconame,'') AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosstart', ",
- "CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
- "p.procunits as Units,",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid!=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(p1.procbalorig),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid!=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid!=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid!=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.proccurcovid=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p ",
- "LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
- "LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
- "WHERE ",
- "pr.payrecproc = p.id AND pr.payreccat=5 AND p.proccurcovid!=0 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- ") ",
- "UNION ALL ",
- "(SELECT ",
- "2 as TypeID,"
- "'Sales' AS 'Type',"
- "'Lab Fee' AS 'Category', ",
- "2 AS 'cattypeid', ",
- "'' AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "'' AS 'dosstart', ",
- "'' AS 'dosend', ",
- "1 as Units,",
- "(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proclabcharges>0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proclabcharges>0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proclabcharges>0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proclabcharges>0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p ",
- "LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
- "LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
- "WHERE ",
- "pr.payrecproc = p.id AND pr.payreccat=5 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "p.proclabcharges>0 AND ",
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY TransactionDate ",
- " ) ",
-
- "UNION ALL ",
- "(SELECT ",
- "2 as TypeID,",
- "'Sales' AS 'Type',",
- "'Maintenance' AS 'Category', ",
- "3 AS cattypeid, ",
- "'' AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosstart', ",
- "CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
- "p.procunits as Units,",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid=0 AND "
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(p1.procbalorig),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid=0 AND "
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid=0 AND "
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=5 AND ",
- "p1.id=pr1.payrecproc AND ",
- "p1.proccurcovid=0 AND "
- "pr1.payrecdt=pr.payrecdt AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p ",
- "LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
- "LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
- "WHERE ",
- "pr.payrecproc = p.id AND pr.payreccat=5 AND p.proccurcovid=0 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY thirdParty,TransactionDate ",
- " ) ",
-
- "UNION ALL ",
- #3rd party
- "(SELECT ",
- "3 as TypeID,"
- "'Cash Receipts' AS 'Type',"
- "'3rd Party Pmt' AS 'Category', ",
- "1 AS 'cattypeid', ",
- "i.insconame AS thirdParty, ",
- "pr.payrecdtadd AS 'TransactionDate',",
- "CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(MIN(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosstart', ",
- "CASE WHEN (i.insconame IS NOT NULL) ",
- " THEN IFNULL(DATE_FORMAT(MAX(p.procdt), '%m/%d/%Y'),'') ELSE '' END AS 'dosend', ",
- "1 as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink!=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "pr1.payreclink=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink!=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "pr1.payreclink=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink!=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "pr1.payreclink=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink!=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "pr1.payreclink=c1.id AND ",
- "c1.covinsco=i1.id AND "
- "i1.id=i.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p ",
- "LEFT OUTER JOIN coverage c ON c.id = p.proccurcovid ",
- "LEFT OUTER JOIN insco i ON i.id = c.covinsco ",
- "LEFT OUTER JOIN cpt cp ON cp.id = p.proccpt ",
- "WHERE ",
- "pr.payrecproc=p.id AND ",
- "pr.payreccat=0 AND ",
- "pr.payreclink!=0 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "cp.id = p.proccpt AND ",
- "cp.cptcode!='80101' AND "
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY TransactionDate ",
- ")",
-
- "UNION ALL ",
- #CASH
- "(SELECT ",
- "3 as TypeID,"
- "'Cash Receipts' AS 'Type',"
- "'Cash' AS 'Category', ",
- "2 AS 'cattypeid', ",
- "'' AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "'' AS 'dosstart', ",
- "'' AS 'dosend', ",
- "1 as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payreclink=0 AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode!='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p,cpt cp ",
- "WHERE ",
- "pr.payrecproc=p.id AND ",
- "cp.id = p.proccpt AND ",
- "pr.payreccat=0 AND ",
- "pr.payreclink=0 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "cp.cptcode!='80101' AND "
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY TransactionDate ",
- ") ",
-
- "UNION ALL ",
- #Copay
- "(SELECT ",
- "3 as TypeID,"
- "'Cash Receipts' AS 'Type',"
- "'Co-Pay Rcvd' AS 'Category', ",
- "3 AS 'cattypeid', ",
- "'' AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "'' AS 'dosstart', ",
- "'' AS 'dosend', ",
- "1 as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=11 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=11 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=11 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
- "WHERE pr1.payreccat=11 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p,cpt cp ",
- "WHERE ",
- "pr.payrecproc=p.id AND ",
- "cp.id = p.proccpt AND ",
- "pr.payreccat=11 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY TransactionDate ",
- ") "
-
- "UNION ALL ",
- #UA/BSN
- "(SELECT ",
- "3 as TypeID,"
- "'Cash Receipts' AS 'Type',"
- "'UA/BSN' AS 'Category', ",
- "4 AS 'cattypeid', ",
- "'' AS thirdParty, ",
- "pr.payrecdt AS 'TransactionDate',",
- "'' AS 'dosstart', ",
- "'' AS 'dosend', ",
- "1 as Units,",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend>'",endDate,"' ) AS 'Deffered Amount',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Actual Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) AS 'Batch Total',",
- "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
- "WHERE pr1.payreccat=0 AND ",
- "pr1.payrecdt=pr.payrecdt AND ",
- "pr1.payrecproc=p1.id AND ",
- "cp1.id = p1.proccpt AND ",
- "cp1.cptcode='80101' AND ",
- "p1.procpos=",facID," AND ",
- "p1.procdtend<='",endDate,"' ) 'Date Total',",
- "f.psrname AS 'Facility Name' ",
- "FROM facility f,payrec pr,procrec p,cpt cp ",
- "WHERE ",
- "pr.payrecproc=p.id AND ",
- "cp.id = p.proccpt AND ",
- "pr.payreccat=0 AND ",
- "( (pr.payrecdt >= '",startDate,"' AND pr.payrecdt <= '",endDate,"') OR ",
- "(p.procdtend >= '",startDate,"' AND p.procdtend <= '",endDate,"') ) AND ",
- "cp.cptcode='80101' AND "
- "p.procpos=",facID," AND ",
- "p.procpos=f.id GROUP BY TransactionDate ",
- ") ) data ORDER BY data.TypeID,data.cattypeid,data.thirdParty,TransactionDate"
-
+ SET @sql = CONCAT(
+ "SELECT * FROM ( ",
+ "(SELECT ",
+ "2 AS type,"
+ "i.insconame AS '3rd Party',",
+ "0 AS 'sales_intake', ",
+ "0 AS 'sales_labfee', ",
+ "0 AS 'sales_discharge', ",
+ "0 AS 'sales_methadone', ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1, coverage c1 ",
+ "WHERE ",
+ "pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "p1.procpos=1 AND ",
+ "c1.covinsco=i.id AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'sales_other', ",
+ "0 AS 'sales_allw_intake', ",
+ "0 AS 'sales_allw_labfee', ",
+ "0 AS 'sales_allw_discharge', ",
+ "0 AS 'sales_allw_methadone', ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1, coverage c1 ",
+ "WHERE ",
+ "pr1.payreccat IN (1,2,7,9,12) AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "p1.procpos=1 AND ",
+ "c1.covinsco=i.id AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'sale_all_other', ",
+ "0 AS 'dfsales_intake', ",
+ "0 AS 'dfsales_labfee', ",
+ "0 AS 'dfsales_discharge', ",
+ "0 AS 'dfsales_methadone', ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1, coverage c1 ",
+ "WHERE ",
+ "pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "p1.procpos=1 AND ",
+ "c1.covinsco=i.id AND ",
+ "p1.procdtend>'",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'dfsales_other', ",
+ "0 AS 'dfsales_allw_intake', ",
+ "0 AS 'dfsales_allw_labfee', ",
+ "0 AS 'dfsales_allw_discharge', ",
+ "0 AS 'dfsales_allw_methadone', ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1, coverage c1 ",
+ "WHERE ",
+ "pr1.payreccat IN (1,2,7,9,12) AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid!=0 AND ",
+ "p1.proccurcovid=c1.id AND ",
+ "p1.procpos=1 AND ",
+ "c1.covinsco=i.id AND ",
+ "p1.procdtend>'",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'dfsale_all_other', ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1,coverage c1,insco i1 ",
+ "WHERE ",
+ "pr1.payreccat=0 AND ",
+ "pr1.payreclink!=0 AND ",
+ "pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "cp1.cptcode!='80101' AND ",
+ "pr1.payreclink=c1.id AND ",
+ "c1.covinsco=i1.id AND ",
+ "i1.id=i.id AND ",
+ "p1.procpos=1 AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'cash_collected' ",
+ "FROM insco i)",
+ "UNION ALL ",
+ "(SELECT ",
+ "1 AS type,"
+ "'None' AS '3rd Party',",
+ "0 AS 'sales_intake', ",
+ "(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proclabcharges>0 AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'sales_labfee', ",
+ "0 AS 'sales_discharge', ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid=0 AND "
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'sales_methadone', ",
+ "0 AS 'sales_other', ",
+ "0 AS 'sales_allw_intake', ",
+ "0 AS 'sales_allw_labfee', ",
+ "0 AS 'sales_allw_discharge', ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid =0 AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'sales_allw_methadone', ",
+ "0 AS 'sale_all_other', ",
+ "0 AS 'dfsales_intake', ",
+ "(SELECT IFNULL(SUM(p1.proclabcharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proclabcharges>0 AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'dfsales_labfee', ",
+ "0 AS 'dfsales_discharge', ",
+ "(SELECT IFNULL(SUM(p1.proccharges),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat=5 AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid=0 AND "
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'dfsales_methadone', ",
+ "0 AS 'dfsales_other', ",
+ "0 AS 'dfsales_allw_intake', ",
+ "0 AS 'dfsales_allw_labfee', ",
+ "0 AS 'dfsales_allw_discharge', ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1 ",
+ "WHERE pr1.payreccat IN (1,2,7,9,12) AND ",
+ "p1.id=pr1.payrecproc AND ",
+ "p1.proccurcovid =0 AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend>'",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'dfsales_allw_methadone', ",
+ "0 AS 'dfsale_all_other', ",
+ "(SELECT IFNULL(SUM(pr1.payrecamt),0) FROM payrec pr1,procrec p1,cpt cp1 ",
+ "WHERE pr1.payrecproc=p1.id AND ",
+ "cp1.id = p1.proccpt AND ",
+ "(",
+ "(pr1.payreccat=0 AND pr1.payreclink=0) OR ",
+ "(pr1.payreccat=0 AND cp1.cptcode='80101') OR ",
+ "(pr1.payreccat=11) ",
+ ") AND ",
+ "p1.procpos=",facID," AND ",
+ "p1.procdtend<='",endDate,"' AND ",
+ "pr1.payrecdt >= '",startDate,"' AND ",
+ "pr1.payrecdt <= '",endDate,"' ",
+ ") AS 'cash_collected' )",
+ ") data WHERE ",
+ "data.sales_other!=0 OR ",
+ "data.sale_all_other!=0 OR ",
+ "data.dfsales_other!=0 OR ",
+ "data.dfsale_all_other!=0 OR ",
+ "data.cash_collected!=0 ORDER BY data.type"
);
PREPARE s FROM @sql ;
EXECUTE s ;

Reply all

Reply to author

Forward

0 new messages