details: /erp/devel/pi/rev/0f75f9ed2c72
changeset: 8744:0f75f9ed2c72
user: Harikrishnan Raja <harikrishnan.raja <at> openbravo.com>
date: Tue Oct 26 13:06:52 2010 +0530
summary: Fixes Issue 10690: Expense Report and Project Profitability Reports
display hours even if time sheet lines are in days
diffstat:
src-db/database/sourcedata/AD_FIELD.xml |
2 +-
src-db/database/sourcedata/AD_MESSAGE.xml |
11 +++
src/org/openbravo/erpCommon/ad_reports/ReportExpense.java |
35 +++++++--
src/org/openbravo/erpCommon/ad_reports/ReportExpense_data.xsql |
20 ++++-
src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitabilityJR.java |
8 ++
src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql |
23 ++++-
6 files changed, 79 insertions(+), 20 deletions(-)
diffs (205 lines):
diff -r 8dba4df81fbb -r 0f75f9ed2c72 src-db/database/sourcedata/AD_FIELD.xml
--- a/src-db/database/sourcedata/AD_FIELD.xml Tue Oct 26 11:53:58 2010 +0530
+++ b/src-db/database/sourcedata/AD_FIELD.xml Tue Oct 26 13:06:52 2010 +0530
@@ -76628,7 +76628,7 @@
<!--6235--> <AD_COLUMN_ID><![CDATA[8169]]></AD_COLUMN_ID>
<!--6235--> <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED>
<!--6235--> <DISPLAYLENGTH><![CDATA[21]]></DISPLAYLENGTH>
-<!--6235--> <ISREADONLY><![CDATA[N]]></ISREADONLY>
+<!--6235--> <ISREADONLY><![CDATA[Y]]></ISREADONLY>
<!--6235--> <SEQNO><![CDATA[110]]></SEQNO>
<!--6235--> <ISSAMELINE><![CDATA[Y]]></ISSAMELINE>
<!--6235--> <ISFIELDONLY><![CDATA[N]]></ISFIELDONLY>
diff -r 8dba4df81fbb -r 0f75f9ed2c72 src-db/database/sourcedata/AD_MESSAGE.xml
--- a/src-db/database/sourcedata/AD_MESSAGE.xml Tue Oct 26 11:53:58 2010 +0530
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml Tue Oct 26 13:06:52 2010 +0530
@@ -29542,6 +29542,17 @@
<!--01C801B95D4A44E2956F9057C7E38A6A-->
<AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
<!--01C801B95D4A44E2956F9057C7E38A6A--></AD_MESSAGE>
+<!--02758CBBA66D42108380E648EF7C026E--><AD_MESSAGE>
+<!--02758CBBA66D42108380E648EF7C026E-->
<AD_MESSAGE_ID><![CDATA[02758CBBA66D42108380E648EF7C026E]]></AD_MESSAGE_ID>
+<!--02758CBBA66D42108380E648EF7C026E-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--02758CBBA66D42108380E648EF7C026E--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--02758CBBA66D42108380E648EF7C026E--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--02758CBBA66D42108380E648EF7C026E-->
<VALUE><![CDATA[NoConversionDayUom]]></VALUE>
+<!--02758CBBA66D42108380E648EF7C026E--> <MSGTEXT><![CDATA[Their is no
Conversion Rate defined for Day to Hour.]]></MSGTEXT>
+<!--02758CBBA66D42108380E648EF7C026E--> <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--02758CBBA66D42108380E648EF7C026E-->
<AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--02758CBBA66D42108380E648EF7C026E--></AD_MESSAGE>
+
<!--03C7B58E00F246E7BA06D153B3DB2D5B--><AD_MESSAGE>
<!--03C7B58E00F246E7BA06D153B3DB2D5B-->
<AD_MESSAGE_ID><![CDATA[03C7B58E00F246E7BA06D153B3DB2D5B]]></AD_MESSAGE_ID>
<!--03C7B58E00F246E7BA06D153B3DB2D5B-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
diff -r 8dba4df81fbb -r 0f75f9ed2c72
src/org/openbravo/erpCommon/ad_reports/ReportExpense.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportExpense.java Tue Oct 26
11:53:58 2010 +0530
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportExpense.java Tue Oct 26
13:06:52 2010 +0530
@@ -84,13 +84,14 @@
String strCurrencyId = vars.getGlobalVariable("inpCurrencyId",
"ReportExpense|currency",
strUserCurrencyId);
String strOutput = "html";
- if (vars.commandIn("PDF")){
+ if (vars.commandIn("PDF")) {
strOutput = "pdf";
- printPageDataPDF(request, response, vars, strDateFrom, strDateTo,
strcBpartnerId,
- strPartner, strProject, strExpense, strCurrencyId, strOutput);
-
- } else printPageDataHtml(request, response, vars, strDateFrom,
strDateTo, strcBpartnerId,
- strPartner, strProject, strExpense, strCurrencyId);
+ printPageDataPDF(request, response, vars, strDateFrom, strDateTo,
strcBpartnerId,
+ strPartner, strProject, strExpense, strCurrencyId, strOutput);
+
+ } else
+ printPageDataHtml(request, response, vars, strDateFrom, strDateTo,
strcBpartnerId,
+ strPartner, strProject, strExpense, strCurrencyId);
} else
pageError(response);
}
@@ -127,6 +128,14 @@
myMessage = Utility.translateError(this, vars, vars.getLanguage(),
ex.getMessage());
}
}
+ for (int i = 0; i < data1.length; i++) {
+ String count = ReportExpenseData.selectUOM(this, data1[i].cuomid);
+ if (Integer.parseInt(count) == 0) {
+ advisePopUp(request, response, "ERROR", Utility
+ .messageBD(this, "Error", vars.getLanguage()),
Utility.messageBD(this,
+ "NoConversionDayUom", vars.getLanguage()));
+ }
+ }
strConvRateErrorMsg = myMessage.getMessage();
// If a conversion rate is missing for a certain transaction, an error
// message window pops-up.
@@ -148,10 +157,10 @@
}
}
-private void printPageDataPDF(HttpServletRequest request, HttpServletResponse
response,
+ private void printPageDataPDF(HttpServletRequest request,
HttpServletResponse response,
VariablesSecureApp vars, String strDateFrom, String strDateTo, String
strcBpartnerId,
- String strPartner, String strProject, String strExpense, String
strCurrencyId, String strOutput) throws IOException,
- ServletException {
+ String strPartner, String strProject, String strExpense, String
strCurrencyId,
+ String strOutput) throws IOException, ServletException {
if (log4j.isDebugEnabled())
log4j.debug("Output: PDF");
String discard[] = { "sectionPartner" };
@@ -179,6 +188,14 @@
myMessage = Utility.translateError(this, vars, vars.getLanguage(),
ex.getMessage());
}
}
+ for (int i = 0; i < data1.length; i++) {
+ String count = ReportExpenseData.selectUOM(this, data1[i].cuomid);
+ if (Integer.parseInt(count) == 0) {
+ advisePopUp(request, response, "ERROR", Utility
+ .messageBD(this, "Error", vars.getLanguage()),
Utility.messageBD(this,
+ "NoConversionDayUom", vars.getLanguage()));
+ }
+ }
strConvRateErrorMsg = myMessage.getMessage();
// If a conversion rate is missing for a certain transaction, an error
// message window pops-up.
diff -r 8dba4df81fbb -r 0f75f9ed2c72
src/org/openbravo/erpCommon/ad_reports/ReportExpense_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportExpense_data.xsql Tue Oct
26 11:53:58 2010 +0530
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportExpense_data.xsql Tue Oct
26 13:06:52 2010 +0530
@@ -25,13 +25,13 @@
<Sql>
<![CDATA[
SELECT C_CURRENCY_ISOSYM(?) AS CONVISOSYM, CBE.NAME AS EMPLOYEE, (CASE
S_L.ISTIMEREPORT WHEN 'Y' THEN S_L.QTY ELSE 0 END) *
-
C_CURRENCY_CONVERT(C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(s.c_bpartner_id,
(CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE
END)), (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE
S_L.DATEEXPENSE END)),
- ?, ?, (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE
S_L.DATEEXPENSE END), NULL, S_L.AD_CLIENT_ID, S_L.AD_ORG_ID) AS COST,
+
C_Uom_Convert(C_CURRENCY_CONVERT(C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(s.c_bpartner_id,
(CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE
END)), (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE
S_L.DATEEXPENSE END)),
+ ?, ?, (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE
S_L.DATEEXPENSE END), NULL, S_L.AD_CLIENT_ID, S_L.AD_ORG_ID),C_UOM.C_UOM_ID,
(select C_UOM_CONVERSION.C_UOM_TO_ID from C_UOM_CONVERSION WHERE
C_UOM_CONVERSION.C_UOM_ID = C_UOM.C_UOM_ID),'Y') AS COST,
CBC.NAME AS NAME, (P.VALUE || ' - ' ||P.NAME) AS DESCR,
M_PRODUCT.NAME AS PRODUCTNAME, C_UOM.NAME AS UOMNAME,
- (CASE S_L.ISTIMEREPORT WHEN 'N' THEN S_L.QTY ELSE 0 END) AS QTY,
(CASE S_L.ISTIMEREPORT WHEN 'Y' THEN S_L.QTY ELSE 0 END) AS HORAS,
+ (CASE S_L.ISTIMEREPORT WHEN 'N' THEN S_L.QTY ELSE 0 END) AS QTY,
(CASE S_L.ISTIMEREPORT WHEN 'Y' THEN C_Uom_Convert(S_L.QTY,C_UOM.C_UOM_ID,
(select C_UOM_CONVERSION.C_UOM_TO_ID from C_UOM_CONVERSION WHERE
C_UOM_CONVERSION.C_UOM_ID = C_UOM.C_UOM_ID),'Y') ELSE 0 END) AS HORAS,
(CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE
S_L.DATEEXPENSE END) AS DATEEXPENSE,
(CASE WHEN S_L.ISTIMEREPORT ='N' THEN
C_CURRENCY_CONVERT(COALESCE(S_L.EXPENSEAMT,0), S_L.C_CURRENCY_ID, ?, (CASE WHEN
S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END), NULL,
S_L.AD_CLIENT_ID, S_L.AD_ORG_ID) ELSE 0 END) AS INVOICEPRICE,
- AD_MESSAGE_GET2(S.PROCESSED,?) AS PROCESSED, (CASE WHEN
S_L.DESCRIPTION IS NULL THEN S.DESCRIPTION ELSE S_L.DESCRIPTION END) AS
DESCRIPTION, S_L.S_TIMEEXPENSELINE_ID, S.DOCUMENTNO
+ AD_MESSAGE_GET2(S.PROCESSED,?) AS PROCESSED, (CASE WHEN
S_L.DESCRIPTION IS NULL THEN S.DESCRIPTION ELSE S_L.DESCRIPTION END) AS
DESCRIPTION, S_L.S_TIMEEXPENSELINE_ID, S.DOCUMENTNO,C_UOM.C_UOM_ID AS cUomId,
'' AS COUNT
FROM S_TIMEEXPENSE S, C_BPARTNER CBE, M_PRODUCT, C_UOM,
S_TIMEEXPENSELINE S_L
LEFT JOIN C_PROJECT P ON S_L.C_PROJECT_ID = P.C_PROJECT_ID
LEFT JOIN C_BPARTNER CBC ON S_L.C_BPARTNER_ID = CBC.C_BPARTNER_ID
@@ -71,6 +71,18 @@
]]></Sql>
<Parameter name="cBpartnerId"/>
</SqlMethod>
+
+ <SqlMethod name="selectUOM" type="preparedStatement" return="String"
default="">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ SELECT COUNT(C_UOM_CONVERSION.C_UOM_ID) AS COUNT FROM
C_UOM_CONVERSION,C_UOM
+ WHERE C_UOM_CONVERSION.C_UOM_ID = ?
+ AND C_UOM_CONVERSION.C_UOM_TO_ID = C_UOM.C_UOM_ID
+ AND C_UOM.UOMSYMBOL='h'
+ ]]></Sql>
+ <Parameter name="cUomId"/>
+ </SqlMethod>
<SqlMethod name="set" type="constant" return="multiple">
<SqlMethodComment></SqlMethodComment>
diff -r 8dba4df81fbb -r 0f75f9ed2c72
src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitabilityJR.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitabilityJR.java
Tue Oct 26 11:53:58 2010 +0530
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitabilityJR.java
Tue Oct 26 13:06:52 2010 +0530
@@ -145,6 +145,14 @@
}
strConvRateErrorMsg = myMessage.getMessage();
+ for (int i = 0; i < data.length; i++) {
+ String count = ReportExpenseData.selectUOM(this, data[i].cuomid);
+ if (Integer.parseInt(count) == 0) {
+ advisePopUp(request, response, "ERROR", Utility
+ .messageBD(this, "Error", vars.getLanguage()),
Utility.messageBD(this,
+ "NoConversionDayUom", vars.getLanguage()));
+ }
+ }
// If a conversion rate is missing for a certain transaction, an error
// message window pops-up.
if (!strConvRateErrorMsg.equals("") && strConvRateErrorMsg != null) {
diff -r 8dba4df81fbb -r 0f75f9ed2c72
src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql
---
a/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql
Tue Oct 26 11:53:58 2010 +0530
+++
b/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql
Tue Oct 26 13:06:52 2010 +0530
@@ -47,20 +47,20 @@
COALESCE(EXPEXP.AMOUNT,0) AS REALEXPENSES,
COALESCE(COLLECTED.AMOUNT,0) AS COLLECTED,
- '' AS NODE_ID, '' AS ISSUMMARY
+ '' AS NODE_ID, '' AS ISSUMMARY ,COST.cUomId AS cUomId, '' AS COUNT
FROM C_BPartner bpclient, AD_Org org,
C_Project p LEFT JOIN C_BPartner bpresp ON p.Responsible_ID =
bpresp.C_BPartner_ID
LEFT JOIN C_Projecttype pt ON pt.C_Projecttype_ID =
p.C_Projecttype_ID
LEFT JOIN (SELECT S_TimeExpenseLine.C_Project_ID,
- SUM((CASE S_TimeExpenseLine.IsTimereport WHEN 'Y' THEN
S_TimeExpenseLine.qty ELSE 0 END)*
-
C_CURRENCY_CONVERT(C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(C_BPartner.c_bpartner_id,
TO_DATE(COALESCE(S_TimeExpenseLine.DATEEXPENSE, s_timeexpense.DATEREPORT))),
TO_DATE(COALESCE(S_TimeExpenseLine.DATEEXPENSE, s_timeexpense.DATEREPORT))),
- ?, ?, TO_DATE(COALESCE(S_TimeExpenseLine.DATEEXPENSE,
s_timeexpense.DATEREPORT)), NULL, S_TimeExpenseLine.AD_CLIENT_ID,
S_TimeExpenseLine.AD_ORG_ID)) AS cost
- FROM S_TimeExpenseLine , S_TimeExpense, C_BPartner
+ SUM((CASE S_TimeExpenseLine.IsTimereport WHEN 'Y' THEN
C_Uom_Convert(S_TimeExpenseLine.qty,C_UOM.C_UOM_ID, (select
C_UOM_CONVERSION.C_UOM_TO_ID from C_UOM_CONVERSION WHERE
C_UOM_CONVERSION.C_UOM_ID = C_UOM.C_UOM_ID),'Y') ELSE 0
END)*C_CURRENCY_CONVERT(C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(C_BPartner.c_bpartner_id,
TO_DATE(COALESCE(S_TimeExpenseLine.DATEEXPENSE, s_timeexpense.DATEREPORT))),
TO_DATE(COALESCE(S_TimeExpenseLine.DATEEXPENSE, s_timeexpense.DATEREPORT))),
+ ?, ?, TO_DATE(COALESCE(S_TimeExpenseLine.DATEEXPENSE,
s_timeexpense.DATEREPORT)), NULL, S_TimeExpenseLine.AD_CLIENT_ID,
S_TimeExpenseLine.AD_ORG_ID)) AS cost,C_UOM.C_UOM_ID AS cUomId
+ FROM S_TimeExpenseLine , S_TimeExpense, C_BPartner, C_Uom
WHERE S_TimeExpense.S_TimeExpense_ID =
S_TimeExpenseLine.S_TimeExpense_ID
AND S_TimeExpense.C_BPartner_ID = C_BPartner.C_BPartner_ID
+ AND S_TimeExpenseLine.C_UOM_ID = C_UOM.C_UOM_ID
AND S_TimeExpense.Processed = 'Y'
AND 1=1
- GROUP BY S_TimeExpenseLine.C_Project_ID) cost ON p.C_Project_ID =
cost.C_Project_ID
+ GROUP BY S_TimeExpenseLine.C_Project_ID,C_UOM.C_UOM_ID) cost ON
p.C_Project_ID = cost.C_Project_ID
LEFT JOIN (SELECT c_Project, sum(amount) as amount
FROM (
@@ -290,4 +290,15 @@
<Parameter name="adOrgId"/>
<Parameter name="adClientId"/>
</SqlMethod>
+ <SqlMethod name="selectUOM" type="preparedStatement" return="String"
default="">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ SELECT COUNT(C_UOM_CONVERSION.C_UOM_ID) AS COUNT FROM
C_UOM_CONVERSION,C_UOM
+ WHERE C_UOM_CONVERSION.C_UOM_ID = ?
+ AND C_UOM_CONVERSION.C_UOM_TO_ID = C_UOM.C_UOM_ID
+ AND C_UOM.UOMSYMBOL='h'
+ ]]></Sql>
+ <Parameter name="cUomId"/>
+ </SqlMethod>
</SqlClass>
------------------------------------------------------------------------------
Nokia and AT&T present the 2010 Calling All Innovators-North America contest
Create new apps & games for the Nokia N8 for consumers in U.S. and Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
http://p.sf.net/sfu/nokia-dev2dev
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
