src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 990 3131cc6e4980
child 1057 dcbe50c4b7bb
permissions -rw-r--r--
Removed old frame parameter of windowTableId
<?xml version="1.0" encoding="UTF-8" ?>
<!--
    *************************************************************************
    * The contents of this file are subject to the Openbravo  Public  License
    * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
    * Version 1.1  with a permitted attribution clause; you may not  use this
    * file except in compliance with the License. You  may  obtain  a copy of
    * the License at http://www.openbravo.com/legal/license.html 
    * Software distributed under the License  is  distributed  on  an "AS IS"
    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    * License for the specific  language  governing  rights  and  limitations
    * under the License. 
    * The Original Code is Openbravo ERP. 
    * The Initial Developer of the Original Code is Openbravo SL 
    * All portions are Copyright (C) 2001-2006 Openbravo SL 
    * All Rights Reserved. 
    * Contributor(s):  ______________________________________.
    ************************************************************************
    -->



<SqlClass name="ReportProjectProfitabilityData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
        SELECT P.NAME AS PROJECTNAME, P.DATECONTRACT AS INITDATE, BPRESP.NAME AS RESPONSIBLE,
          P.AD_ORG_ID AS ORG, BPCLIENT.NAME AS PARTNER, ORG.NAME AS ORGNAME,PT.NAME AS PROJECTTYPE,
          COALESCE(P.SERVREVENUE,0) AS PLANREVENUE, COALESCE(P.SERVCOST,0) AS PLANCOST, 
          COALESCE(P.EXPREINVOICING,0) AS PLANREINVOICING, COALESCE(P.EXPEXPENSES,0) AS PLANEXPENSES,
          COALESCE(SERREV.AMOUNT,0) AS REALREVENUE, COALESCE(COST.COST,0) AS REALCOST, 
          COALESCE(EXPREI.AMOUNT,0) AS REALREINVOICED, COALESCE(EXPEXP.AMOUNT,0) AS REALEXPENSES,
          COALESCE(COLLECTED.AMOUNT,0) AS COLLECTED, '' AS NODE_ID, '' AS ISSUMMARY
        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_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(C_BPartner.c_bpartner_id,  s_timeexpense.DATEREPORT), s_timeexpense.DATEREPORT)) AS cost
           FROM S_TimeExpenseLine , S_TimeExpense, C_BPartner
           WHERE S_TimeExpense.S_TimeExpense_ID = S_TimeExpenseLine.S_TimeExpense_ID
           AND S_TimeExpense.C_BPartner_ID = C_BPartner.C_BPartner_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
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
           FROM (
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt ELSE ila.amt END AS amount
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
             M_Product p
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
             AND il.M_Product_ID = p.M_Product_ID
             AND i.issotrx = 'Y'
             AND i.docStatus IN ('CO', 'CL')
	         AND 2=2
             AND p.producttype = 'S') AAA
           GROUP BY c_Project) serrev ON p.C_Project_ID = serrev.C_Project
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
           FROM (
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt ELSE ila.amt END AS amount
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
             M_Product p
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
             AND il.M_Product_ID = p.M_Product_ID
             AND i.issotrx = 'Y'
             AND i.docStatus IN ('CO', 'CL')
	         AND 3=3
             AND p.producttype <> 'S') BBB
           GROUP BY c_Project) exprei ON p.C_Project_ID = exprei.C_Project
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
           FROM (
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt ELSE ila.amt END AS amount
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
             M_Product p
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
           AND il.M_Product_ID = p.M_Product_ID
           AND i.docStatus IN ('CO', 'CL')
	   	   AND 4=4
           AND i.issotrx = 'N') CCC
           GROUP BY c_Project) expexp ON p.C_Project_ID = expexp.C_Project
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
           FROM (
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt*C_Invoice_Percentage_Paid(i.C_Invoice_ID) ELSE ila.amt*C_Invoice_Percentage_Paid(i.C_Invoice_ID) END AS amount
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
             M_Product p
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
           AND il.M_Product_ID = p.M_Product_ID
           AND i.docStatus IN ('CO', 'CL')
	   	   AND 5=5
             AND i.issotrx = 'Y') DDD
           GROUP BY c_Project) collected ON p.C_Project_ID = collected.C_Project
        WHERE p.C_BPartner_ID = bpclient.C_BPartner_ID
          AND p.AD_Org_ID = org.AD_Org_ID
          AND p.AD_Org_ID IN ('1')
          AND p.AD_Client_ID IN ('1')
          AND 6=6
        ORDER BY orgname, partner, initdate
      ]]>
    </Sql>
    <Parameter name="datefrom2" optional="true" after="AND 1=1"><![CDATA[ AND s_timeexpense.DATEREPORT >= to_date(?) ]]></Parameter>
    <Parameter name="dateto2" optional="true" after="AND 1=1"><![CDATA[ AND s_timeexpense.DATEREPORT  <= to_date(?) ]]></Parameter>
    <Parameter name="datefrom2" optional="true" after="AND 2=2"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
    <Parameter name="dateto2" optional="true" after="AND 2=2"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
    <Parameter name="datefrom2" optional="true" after="AND 3=3"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
    <Parameter name="dateto2" optional="true" after="AND 3=3"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
    <Parameter name="datefrom2" optional="true" after="AND 4=4"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
    <Parameter name="dateto2" optional="true" after="AND 4=4"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
    <Parameter name="datefrom2" optional="true" after="AND 5=5"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
    <Parameter name="dateto2" optional="true" after="AND 5=5"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
    <Parameter name="org" optional="true" type="replace" after="AND p.AD_Org_ID IN (" text="'1'"/>
    <Parameter name="client" optional="true" type="replace" after="AND p.AD_Client_ID IN (" text="'1'"/>
    <Parameter name="dateFrom" optional="true" after="AND 6=6"><![CDATA[ AND p.Datecontract > to_date(?) ]]></Parameter>
    <Parameter name="dateTo" optional="true" after="AND 6=6"><![CDATA[ AND p.Datecontract < to_date(?) ]]></Parameter>
    <Parameter name="projecttype" optional="true" after="AND 6=6"><![CDATA[ AND p.C_Projecttype_ID = TO_NUMBER(?) ]]></Parameter>
    <Parameter name="project" optional="true" after="AND 6=6"><![CDATA[ AND p.C_Project_ID = TO_NUMBER(?) ]]></Parameter>
    <Parameter name="Responsible" optional="true" after="AND 6=6"><![CDATA[ AND p.Responsible_ID = TO_NUMBER(?) ]]></Parameter>
    <Parameter name="Partner" optional="true" after="AND 6=6"><![CDATA[ AND p.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
  </SqlMethod>
  <SqlMethod name="set" type="constant" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql></Sql>
    <Parameter name="planrevenue"/>
    <Parameter name="plancost"/>
    <Parameter name="planreinvoicing"/>
    <Parameter name="planexpenses"/>
    <Parameter name="realrevenue"/>
    <Parameter name="realcost"/>
    <Parameter name="realreinvoiced"/>
    <Parameter name="realexpenses"/>
    <Parameter name="collected"/>
    <Parameter name="org"/>
    <Parameter name="partner"/>
  </SqlMethod>
    <SqlMethod name="selectOrg" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT AD_TREENODE.NODE_ID, AD_ORG.ISSUMMARY
      FROM AD_CLIENTINFO, AD_TREENODE, AD_ORG
      WHERE AD_CLIENTINFO.AD_TREE_ORG_ID = AD_TREENODE.AD_TREE_ID
      AND AD_TREENODE.NODE_ID = AD_ORG.AD_ORG_ID
      AND AD_TREENODE.PARENT_ID = TO_NUMBER(?)
      AND AD_CLIENTINFO.AD_CLIENT_ID = TO_NUMBER(?)
      ORDER BY seqno
      ]]>
    </Sql>
    <Parameter name="adOrgId"/>
    <Parameter name="adClientId"/>
  </SqlMethod>
</SqlClass>