<?xml version="1.0" encoding="UTF-8"?>
<action-sequence> 
  <title/>
  <version>1</version>
  <logging-level>ERROR</logging-level>
  <documentation> 
    <author/>  
    <description>Empty blank action sequence document</description>  
    <help/>  
    <icon/> 
  </documentation>

  <inputs> 
    <TERRITORY type="string"> 
      <sources> 
        <request>TERRITORY</request> 
      </sources>  
      <default-value>NA</default-value> 
    </TERRITORY>  
    <PRODUCTLINE type="string"> 
      <sources> 
        <request>PRODUCTLINE</request> 
      </sources>  
      <default-value>NULL</default-value> 
    </PRODUCTLINE> 
  </inputs>

  <outputs> 
    <swresult type="result-set"/> 
  </outputs>

  <resources/>
  
  <actions> 
    <actions> 
      <condition><![CDATA[PRODUCTLINE != "NULL"]]></condition>  
      <action-definition> 
        <component-name>SQLLookupRule</component-name>
        <action-type>SQL Query</action-type>
        <action-inputs> 
          <territory type="string"/>  
          <productline type="string"/>  
          <PRODUCTLINE type="string"/>  
          <TERRITORY type="string"/> 
        </action-inputs>
        <action-outputs> 
          <query-result type="result-set" mapping="swresult"/> 
        </action-outputs>
        <component-definition> 
          <jndi>SampleData</jndi>  
          <query><![CDATA[SELECT CONCAT(CONCAT( YEAR(ORDERS.ORDERDATE),'-' ),MONTH(ORDERS.ORDERDATE)) AS TIME, SUM(ORDERDETAILS.QUANTITYORDERED*ORDERDETAILS.PRICEEACH) SOLD_PRICE 
FROM ORDERS 
INNER JOIN ORDERDETAILS ON ORDERS.ORDERNUMBER = ORDERDETAILS.ORDERNUMBER 
INNER JOIN PRODUCTS ON ORDERDETAILS.PRODUCTCODE =PRODUCTS.PRODUCTCODE  
INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERNUMBER =CUSTOMERS.CUSTOMERNUMBER  
INNER JOIN EMPLOYEES ON CUSTOMERS.SALESREPEMPLOYEENUMBER = EMPLOYEES.EMPLOYEENUMBER
INNER JOIN OFFICES ON EMPLOYEES.OFFICECODE=OFFICES.OFFICECODE

WHERE PRODUCTS.PRODUCTLINE={PREPARE:PRODUCTLINE} AND OFFICES.TERRITORY={PREPARE:TERRITORY}

GROUP BY
CONCAT( MONTH(ORDERS.ORDERDATE), YEAR(ORDERS.ORDERDATE))]]></query> 
        </component-definition> 
      </action-definition>
 
    </actions>
  
    <actions> 
      <condition><![CDATA[PRODUCTLINE == "NULL"]]></condition>  
      <action-definition> 
        <component-name>SQLLookupRule</component-name>
        <action-type>SQL Query</action-type>
        <action-inputs> 
          <TERRITORY type="string"/> 
        </action-inputs>
        <action-outputs> 
          <query-result type="result-set" mapping="swresult"/> 
        </action-outputs>
        <component-definition> 
          <jndi>SampleData</jndi>  
          <query><![CDATA[SELECT CONCAT(CONCAT( YEAR(ORDERS.ORDERDATE),'-' ),MONTH(ORDERS.ORDERDATE)) AS TIME, SUM(ORDERDETAILS.QUANTITYORDERED*ORDERDETAILS.PRICEEACH) SOLD_PRICE 
FROM ORDERS 
INNER JOIN ORDERDETAILS ON ORDERS.ORDERNUMBER = ORDERDETAILS.ORDERNUMBER 
INNER JOIN PRODUCTS ON ORDERDETAILS.PRODUCTCODE =PRODUCTS.PRODUCTCODE  
INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERNUMBER =CUSTOMERS.CUSTOMERNUMBER  
INNER JOIN EMPLOYEES ON CUSTOMERS.SALESREPEMPLOYEENUMBER = EMPLOYEES.EMPLOYEENUMBER
INNER JOIN OFFICES ON EMPLOYEES.OFFICECODE=OFFICES.OFFICECODE

WHERE OFFICE.TERRITORY='{TERRITORY}'

GROUP BY
CONCAT( MONTH(ORDERS.ORDERDATE), YEAR(ORDERS.ORDERDATE))]]></query> 
        </component-definition> 
      </action-definition>
 
    </actions>
 
  </actions> 
</action-sequence>