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

1 view

Skip to first unread message

subve...@svn.freemedsoftware.org

unread,

Jul 29, 2010, 3:58:19 AM7/29/10

to freemed-d...@googlegroups.com

Author: sadnan
Date: 2010-07-29 03:58:19 -0400 (Thu, 29 Jul 2010)
New Revision: 5759

Modified:
branches/bmas-staging/data/report/MonthlyCashCollectionSummary_en_US.jrxml
branches/bmas-staging/data/report/MonthlyClientBalanceDue_en_US.jrxml
branches/bmas-staging/data/report/sub_DailyAdjustmentJournal_en_US.jasper
branches/bmas-staging/data/report/sub_DailyAdjustmentJournal_en_US.jrxml
branches/bmas-staging/data/report/sub_DailyCashReceiptJournal_en_US.jasper
branches/bmas-staging/data/report/sub_DailyCashReceiptJournal_en_US.jrxml
branches/bmas-staging/data/report/sub_DailyEndOfDayProcessingSummary_en_US.jasper
branches/bmas-staging/data/report/sub_DailyEndOfDayProcessingSummary_en_US.jrxml
branches/bmas-staging/data/schema/mysql/reporting/report_DailyAdjustmentJournal.sql
branches/bmas-staging/data/schema/mysql/reporting/report_DailyCashReceiptJournal.sql
branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyCashCollectionSummary.sql
branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyClientBalanceDue.sql
Log:
Fixed date format in the reports and other small issues

Modified: branches/bmas-staging/data/report/MonthlyCashCollectionSummary_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/MonthlyCashCollectionSummary_en_US.jrxml 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/report/MonthlyCashCollectionSummary_en_US.jrxml 2010-07-29 07:58:19 UTC (rev 5759)
@@ -208,7 +208,7 @@
</background>
<pageHeader>
<band height="70" splitType="Stretch">
- <textField pattern="dd/MM/yyyy HH:mm">
+ <textField pattern="MM/dd/yyyy">
<reportElement positionType="Float" x="11" y="4" width="93" height="43"/>
<textElement>

@@ -216,14 +216,14 @@
<textFieldExpression class="java.util.Date"><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
<staticText>
- <reportElement positionType="Float" x="239" y="25" width="232" height="21"/>
+ <reportElement positionType="Float" x="276" y="21" width="232" height="21"/>
<textElement textAlignment="Center" verticalAlignment="Middle">

</textElement>
<text><![CDATA[Collections Summary Report]]></text>
</staticText>
<staticText>
- <reportElement positionType="Float" x="163" y="47" width="100" height="20"/>
+ <reportElement positionType="Float" x="233" y="42" width="100" height="20"/>
<textElement verticalAlignment="Middle">

</textElement>
@@ -237,28 +237,28 @@
<textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<staticText>
- <reportElement positionType="Float" x="338" y="46" width="52" height="21"/>
+ <reportElement positionType="Float" x="408" y="41" width="52" height="21"/>
<textElement verticalAlignment="Middle">

</textElement>
<text><![CDATA[through]]></text>
</staticText>
<textField pattern="MM/dd/yyyy">
- <reportElement x="390" y="47" width="100" height="20"/>
+ <reportElement x="460" y="42" width="100" height="20"/>
<textElement verticalAlignment="Middle">

</textElement>
<textFieldExpression class="java.util.Date"><![CDATA[$P{param1}]]></textFieldExpression>
</textField>
<textField pattern="MM/dd/yyyy">
- <reportElement x="263" y="47" width="75" height="20"/>
+ <reportElement x="333" y="42" width="75" height="20"/>
<textElement textAlignment="Left" verticalAlignment="Middle">

</textElement>
<textFieldExpression class="java.util.Date"><![CDATA[$P{param0}]]></textFieldExpression>
</textField>
<textField>
- <reportElement x="239" y="4" width="232" height="20"/>
+ <reportElement x="276" y="0" width="232" height="20"/>
<textElement textAlignment="Center">

</textElement>

Modified: branches/bmas-staging/data/report/MonthlyClientBalanceDue_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/MonthlyClientBalanceDue_en_US.jrxml 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/report/MonthlyClientBalanceDue_en_US.jrxml 2010-07-29 07:58:19 UTC (rev 5759)
@@ -48,7 +48,7 @@
<variable name="totalCredits" class="java.lang.Double" resetType="Group" resetGroup="Source" calculation="Sum">
<variableExpression><![CDATA[$F{Credit}]]></variableExpression>
</variable>
- <variable name="finalDebit" class="java.lang.Double" resetType="Column" calculation="Sum">
+ <variable name="finalDebit" class="java.lang.Double" calculation="Sum">
<variableExpression><![CDATA[$F{Debit}]]></variableExpression>
</variable>
<variable name="finalCredit" class="java.lang.Integer" resetType="Column" calculation="Sum">
@@ -144,7 +144,7 @@
</title>
<pageHeader>
<band height="70" splitType="Stretch">
- <textField pattern="dd/MM/yyyy HH:mm">
+ <textField pattern="MM/dd/yyyy">
<reportElement positionType="Float" x="0" y="0" width="93" height="43"/>
<textElement>

@@ -159,21 +159,21 @@
<textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
- <reportElement stretchType="RelativeToBandHeight" x="163" y="28" width="391" height="38" isPrintWhenDetailOverflows="true"/>
+ <reportElement stretchType="RelativeToBandHeight" x="183" y="21" width="391" height="38" isPrintWhenDetailOverflows="true"/>
<textElement textAlignment="Center" verticalAlignment="Middle">

</textElement>
<textFieldExpression class="java.lang.String"><![CDATA["Client Balance Summary by Third Party for All Clients For the Period Ending "]]></textFieldExpression>
</textField>
<textField>
- <reportElement x="240" y="1" width="208" height="20"/>
+ <reportElement x="183" y="1" width="391" height="20"/>
<textElement textAlignment="Center">

</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{Facility Name}]]></textFieldExpression>
</textField>
<textField pattern="MM/dd/yyyy">
- <reportElement x="398" y="47" width="88" height="20"/>
+ <reportElement x="418" y="40" width="88" height="20"/>
<textElement textAlignment="Center">

</textElement>
@@ -363,7 +363,7 @@
</textElement>
<textFieldExpression class="java.lang.Double"><![CDATA[$V{finalDebit}]]></textFieldExpression>
</textField>
- <textField pattern="###0.00">
+ <textField evaluationTime="Report" pattern="###0.00">
<reportElement x="477" y="10" width="77" height="20">
<printWhenExpression><![CDATA[$V{finalCredit}!=0]]></printWhenExpression>
</reportElement>

Modified: branches/bmas-staging/data/report/sub_DailyAdjustmentJournal_en_US.jasper
===================================================================
(Binary files differ)

Modified: branches/bmas-staging/data/report/sub_DailyAdjustmentJournal_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/sub_DailyAdjustmentJournal_en_US.jrxml 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/report/sub_DailyAdjustmentJournal_en_US.jrxml 2010-07-29 07:58:19 UTC (rev 5759)
@@ -208,7 +208,7 @@
</title>
<pageHeader>
<band height="70" splitType="Stretch">
- <textField pattern="MM/dd/yyyy HH:mm">
+ <textField pattern="MM/dd/yyyy">
<reportElement positionType="Float" x="1" y="0" width="93" height="43"/>
<textElement>

@@ -308,7 +308,7 @@
</textField>
<textField>
<reportElement style="Detail" x="223" y="0" width="51" height="15"/>
- <textElement textAlignment="Right"/>
+ <textElement/>
<textFieldExpression class="java.lang.Long"><![CDATA[$F{Trans Num}]]></textFieldExpression>
</textField>
<textField pattern="MM/dd/yyyy">

Modified: branches/bmas-staging/data/report/sub_DailyCashReceiptJournal_en_US.jasper
===================================================================
(Binary files differ)

Modified: branches/bmas-staging/data/report/sub_DailyCashReceiptJournal_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/sub_DailyCashReceiptJournal_en_US.jrxml 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/report/sub_DailyCashReceiptJournal_en_US.jrxml 2010-07-29 07:58:19 UTC (rev 5759)
@@ -220,7 +220,7 @@
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
- <textField pattern="MM/dd/yyyy HH:mm">
+ <textField pattern="MM/dd/yyyy">
<reportElement positionType="Float" x="0" y="0" width="93" height="43"/>
<textElement>

Modified: branches/bmas-staging/data/report/sub_DailyEndOfDayProcessingSummary_en_US.jasper
===================================================================
(Binary files differ)

Modified: branches/bmas-staging/data/report/sub_DailyEndOfDayProcessingSummary_en_US.jrxml
===================================================================
--- branches/bmas-staging/data/report/sub_DailyEndOfDayProcessingSummary_en_US.jrxml 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/report/sub_DailyEndOfDayProcessingSummary_en_US.jrxml 2010-07-29 07:58:19 UTC (rev 5759)
@@ -126,7 +126,7 @@
</title>
<pageHeader>
<band height="70" splitType="Stretch">
- <textField pattern="dd/MM/yyyy HH:mm">
+ <textField pattern="MM/dd/yyyy">
<reportElement positionType="Float" x="0" y="0" width="93" height="43"/>
<textElement>

Modified: branches/bmas-staging/data/schema/mysql/reporting/report_DailyAdjustmentJournal.sql
===================================================================
--- branches/bmas-staging/data/schema/mysql/reporting/report_DailyAdjustmentJournal.sql 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/schema/mysql/reporting/report_DailyAdjustmentJournal.sql 2010-07-29 07:58:19 UTC (rev 5759)
@@ -40,12 +40,12 @@
"IFNULL(i.insconame,'Others') AS Source, ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
- "0 AS 'Trans Num', ",
+ "pr.id AS 'Trans Num', ",
"pr.payrecdt AS 'Trans Date', ",
"pr.payrecamt AS 'Invoice Amount', ",
"0 AS 'Credit Amount', ",
"pr.payrecdescrip AS Reason, ",
- "CONCAT(prec.procdt,' - ',IFNULL(prec.procdtend,'')) AS 'Service Dates', ",
+ "CONCAT(DATE_FORMAT(prec.procdt, '%m/%d/%Y'),' - ',IFNULL(DATE_FORMAT(prec.procdtend, '%m/%d/%Y'),'')) AS 'Service Dates', ",
"f.psrname AS 'Facility Name', ",
"1 AS subtypeid, "
"i.id AS inscoid "
@@ -67,12 +67,12 @@
"'Not Assigned To Third Party' AS Source, ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
- "0 AS 'Trans Num', ",
+ "pr.id AS 'Trans Num', ",
"pr.payrecdt AS 'Trans Date', ",
"prec.proclabcharges AS 'Invoice Amount', ",
"0 AS 'Credit Amount', ",
"pr.payrecdescrip AS Reason, ",
- "CONCAT(prec.procdt,' - ',IFNULL(prec.procdtend,'')) AS 'Service Dates', ",
+ "CONCAT(DATE_FORMAT(prec.procdt, '%m/%d/%Y'),' - ',IFNULL(DATE_FORMAT(prec.procdtend, '%m/%d/%Y'),'')) AS 'Service Dates', ",
"f.psrname AS 'Facility Name', ",
"2 AS subtypeid, "
"0 AS inscoid "
@@ -94,12 +94,12 @@
"'Not Assigned To Third Party' AS Source, ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
- "0 AS 'Trans Num', ",
+ "pr.id AS 'Trans Num', ",
"pr.payrecdt AS 'Trans Date', ",
"pr.payrecamt AS 'Invoice Amount', ",
"0 AS 'Credit Amount', ",
"pr.payrecdescrip AS Reason, ",
- "CONCAT(prec.procdt,' - ',IFNULL(prec.procdtend,'')) AS 'Service Dates', ",
+ "CONCAT(DATE_FORMAT(prec.procdt, '%m/%d/%Y'),' - ',IFNULL(DATE_FORMAT(prec.procdtend, '%m/%d/%Y'),'')) AS 'Service Dates', ",
"f.psrname AS 'Facility Name', ",
"3 AS subtypeid, "
"0 AS inscoid "
@@ -121,12 +121,12 @@
"IFNULL(i.insconame,'Others') AS Source, ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
- "0 AS 'Trans Num', ",
+ "pr.id AS 'Trans Num', ",
"pr.payrecdt AS 'Trans Date', ",
"pr.payrecamt AS 'Invoice Amount', ",
"0 AS 'Credit Amount', ",
"pr.payrecdescrip AS Reason, ",
- "CONCAT(prec.procdt,' - ',IFNULL(prec.procdtend,'')) AS 'Service Dates', ",
+ "CONCAT(DATE_FORMAT(prec.procdt, '%m/%d/%Y'),' - ',IFNULL(DATE_FORMAT(prec.procdtend, '%m/%d/%Y'),'')) AS 'Service Dates', ",
"f.psrname AS 'Facility Name', ",
"1 AS subtypeid, "
"i.id AS inscoid "
@@ -148,12 +148,12 @@
"'Not Assigned to Third Party' AS Source, ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
- "0 AS 'Trans Num', ",
+ "pr.id AS 'Trans Num', ",
"pr.payrecdt AS 'Trans Date', ",
"pr.payrecamt AS 'Invoice Amount', ",
"0 AS 'Credit Amount', ",
"pr.payrecdescrip AS Reason, ",
- "CONCAT(prec.procdt,' - ',IFNULL(prec.procdtend,'')) AS 'Service Dates', ",
+ "CONCAT(DATE_FORMAT(prec.procdt, '%m/%d/%Y'),' - ',IFNULL(DATE_FORMAT(prec.procdtend, '%m/%d/%Y'),'')) AS 'Service Dates', ",
"f.psrname AS 'Facility Name', ",
"2 AS subtypeid, "
"0 AS inscoid "

Modified: branches/bmas-staging/data/schema/mysql/reporting/report_DailyCashReceiptJournal.sql
===================================================================
--- branches/bmas-staging/data/schema/mysql/reporting/report_DailyCashReceiptJournal.sql 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/schema/mysql/reporting/report_DailyCashReceiptJournal.sql 2010-07-29 07:58:19 UTC (rev 5759)
@@ -36,12 +36,12 @@
"( SELECT ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
- "0 AS 'Trans Num' ,",
+ "pr.id AS 'Trans Num' ,",
"pr.payrecdt as 'Trans Date' ,",
"0 AS 'Void Amount',",
"pr.payrecamt AS 'Amount Received', ",
"pr.payrecdescrip AS Reason, ",
- "CONCAT(prec.procdt,' - ',IFNULL(prec.procdtend,'')) AS 'Service Dates', ",
+ "CONCAT(DATE_FORMAT(prec.procdt, '%m/%d/%Y'),' - ',IFNULL(DATE_FORMAT(prec.procdtend, '%m/%d/%Y'),'')) AS 'Service Dates', ",
"CASE ",
"WHEN (pr.payreccat IN (0,11) AND pr.payreclink!=0 AND cp.cptcode!='80101') THEN i.insconame ",
"WHEN (pr.payreccat IN (0,11) AND pr.payreclink=0 AND cp.cptcode!='80101') THEN 'Not Assigned To Third Party' ",

Modified: branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyCashCollectionSummary.sql
===================================================================
--- branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyCashCollectionSummary.sql 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyCashCollectionSummary.sql 2010-07-29 07:58:19 UTC (rev 5759)
@@ -34,6 +34,7 @@
BEGIN
SET @sql = CONCAT(
"SELECT ",
+ "CASE WHEN pr.payreclink=0 THEN 1 ELSE 2 END type,",
"pt.ptid AS patientid, ",
"pt.id AS patientnum,"
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1)) AS patientname, ",
@@ -74,7 +75,7 @@
"prec.procpos=f.id AND ",
"pr.payreccat IN ( 0, 11 ) AND ",
"pr.payrecdt>='",startDate,"' AND pr.payrecdt<='",endDate,"' "
- "GROUP BY pr.payrecpatient, pr.payreclink"
+ "GROUP BY pr.payrecpatient, pr.payreclink ORDER by type"
);
PREPARE s FROM @sql ;
EXECUTE s ;

Modified: branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyClientBalanceDue.sql
===================================================================
--- branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyClientBalanceDue.sql 2010-07-28 14:04:39 UTC (rev 5758)
+++ branches/bmas-staging/data/schema/mysql/reporting/report_MonthlyClientBalanceDue.sql 2010-07-29 07:58:19 UTC (rev 5759)
@@ -34,6 +34,7 @@
BEGIN
SET @sql = CONCAT(
"SELECT ",
+ "IFNULL(i.id,0) AS insid,"
"IFNULL(i.insconame,'Not Assigned To Third Party') AS Source, ",
"pt.id AS patientnum,",
"CONCAT(SUBSTRING(pt.ptlname FROM 1 FOR 3),', ',SUBSTRING(pt.ptfname FROM 1 FOR 1),' ',SUBSTRING(pt.ptmname FROM 1 FOR 1)) AS patientname, ",
@@ -63,7 +64,7 @@
"p.procdt<='",endDate,"' AND ",
"p.procpos=",facID," AND ",
"p.procpos=f.id ",
- "GROUP BY p.procpatient "
+ "GROUP BY p.procpatient ORDER BY insid,patientnum"
);
PREPARE s FROM @sql ;
EXECUTE s ;

Reply all

Reply to author

Forward

0 new messages