<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Artikelverkauf_MS.xsl" csv-renderer="ArtikelverkaufCSV_MS.xsl">
	<title>Artikelverkauf</title>

	<report-parameter name="TENANT" type="integer" hidden="true">
		<preset>@TENANT_NO</preset>
	</report-parameter>
	<report-parameter name="COMPANY" type="integer" hidden="true">
		<preset>@COMPANY_NO</preset>
	</report-parameter>
	<report-parameter name="TODATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
		<report-parameter name="FROMDATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="BUSINESSUNIT" type="integer">
		<label>Filiale:</label>
		<input type="selectlist">
			<source type="dataset" name="businessunits">
			</source>
		</input>
	</report-parameter>


	<!--<report-result report-dataset="businessunits">
	</report-result> -->
		<report-result report-dataset="items">
	</report-result>
	

<report-result report-dataset="departmentCurrency">
	</report-result>

    <report-dataset name="departmentCurrency">
		<sql>
			select * from currencies c join departments d on c.tenant_no = d.tenant_no AND c.currency_cd = d.currency_cd  where d.tenant_no=@TENANT_NO and d.company_no=@COMPANY_NO and d.department_no =@DEPARTMENT_NO 
		</sql>
	</report-dataset>

	<report-dataset name="businessunits">
		<sql>
			select * from businessunits where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and businessunit_no=@BUSINESSUNIT_NO
		</sql>
	</report-dataset>

<report-transaction report-nm="productRevenue" report-transaction="de.timeglobe.reportsnew.reports.Artikelverkauf">
	</report-transaction>

	<report-dataset name="productRevenue">
		<sql>
			SELECT 
			 r_table.r_company_no r_company_no,
			  r_table.r_department_no r_department_no,
              r_table.r_item_cd r_item_cd,
              r_table.r_max_dt r_max_dt,
              r_table.r_total_products r_total_products,
              r_table.r_total_products_amount r_total_products_amount
            FROM(
              select
			  	  h.company_no r_company_no,
			  	  h.department_no r_department_no,
			      p.item_cd r_item_cd,
			      MAX ( cast(sales_inv_ts AS DATE)) r_max_dt,
			      sum(case when coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end) r_total_products,
			      sum(case when coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then -p.amount else p.amount end else 0 end) r_total_products_amount
	          from sales_inv_positions p 
			  join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
		 	  join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
		                                                           
		       	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint         
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
			 
			 	 AND @__BUSINESSUNIT_FILTER__h
			
				
			   and   coalesce(p.stockable, 0)=1
			  GROUP BY h.company_no, h.department_no, p.item_cd
			) r_table
			order by r_total_products_amount desc

		</sql>
	</report-dataset>

	
		<report-dataset name="items">
		<sql>
			SELECT * FROM items i where coalesce(stockable,0) = 1 AND tenant_no=@TENANT_NO 
				 AND @__DEPARTMENT_FILTER__i

		</sql>
	</report-dataset>
	
	<report-dataset name="item_suppliers">
		<sql>
			SELECT i.item_cd, c.contact_nm FROM item_suppliers i join contacts c on i.contact_no = c.contact_no where i.tenant_no=@TENANT_NO AND i.company_no = @COMPANY_NO AND i.department_no = @DEPARTMENT_NO
			order by i.item_cd ASC, c.contact_nm ASC

				  AND @__DEPARTMENT_FILTER__i
		</sql>
	</report-dataset> 
	
</report>
