xquery version "3.0"; 
import module namespace datetime = "http://exist-db.org/xquery/datetime";
element Orders
{
	for $order in collection( '/db/bagco/orders' )/Order
		[
			not(OrderStatusID='10') and
			( OrderType=('Standard','PlainStock' ) ) and 
			( OrderDate >= xs:date('2024-09-24') and OrderDate <= xs:date('2024-09-25') )
		]
	let $customer := doc( concat( '/db/bagco/customers/', $order/CustomerID, '.xml' ) )/Customer
	let $pms := doc( '/db/bagco/refdata/PrintMethods.xml' )/PrintMethods/PrintMethod
	order by $customer/CustomerName, $order/OrderDate, $order/@OrderID
	return element Order
	{
	    element CustomerID { data( $order/CustomerID ) },
	    element CustomerName { data( $customer/CustomerName ) },
	    element OrderID { data( $order/@OrderID ) },
	    element OrderDate { datetime:format-date( $order/OrderDate, 'd MMM yyyy' ) },
	    element Products
	    {
	    	for $prod in $order/Products/Product
	    	let $product := collection( "/db/bagco/products" )/ProductGroup/Product[@ProductID=$prod/ProductID]
	    	order by $prod/ProductID
	    	return element Product
	    	{
	    		element ProductID { data( $prod/ProductID ) },
	    		element Quantity { data( $prod/Quantity ) },
				element UnitPrice   
	            { 
	                if ( exists( $prod/UnitPrice ) and string-length( $prod/UnitPrice ) > 0 )
	            	then format-number( $prod/UnitPrice, '#00.00' )
	            	else 0
	            },
	            element TotalUnitPrice   
	            { 
	                if ( exists( $prod/TotalUnitPrice ) and string-length( $prod/TotalUnitPrice ) > 0 )
	            	then format-number( $prod/TotalUnitPrice, '#00.00' )
	            	else 0
	            }
	        }
	    },
	    element PrintCharges
	    {
	        for $print in $order/Products/Product/PrintMethods/PrintMethod/PrintLines/PrintLine
	        return element PrintCharge
	        {
            	element LogoName { data( $print/../../../../../../Logos/Logo[@LogoNumber=$print/LogoNumber]/LogoName ) },
	            element PrintMethodName { data( $pms[@PrintMethodID=$print/../../PrintMethodID]/PrintMethodName ) },
	            element ProductID { data( $print/../../../../ProductID ) },
	            element Quantity { data( $print/Quantity ) },
	            element PrintCharge
	            {
	                if ( exists( $print/PrintCharge ) and string-length( $print/PrintCharge ) > 0 )
	                then format-number( $print/PrintCharge, '#00.00' )
	            	else 0
	            },
	            element TotalPrintCharge
	            {
	                if ( exists( $print/TotalPrintCharge ) and string-length( $print/TotalPrintCharge ) > 0 )
	                then format-number( $print/TotalPrintCharge, '#00.00' )
	                else 0 
	            }
	        }
	    },
	    element SetupCharges
	    {
        	for $print in $order/Products/Product/PrintMethods/PrintMethod/PrintLines/PrintLine
	        order by $print/SharedSetup
	        return element SetupCharge
	        {
	            (
                	if ( $print/../../../../../../Logos/Logo[@LogoNumber=$print/LogoNumber]/RepeatOrder = 'Yes' )
					then element RepeatOrderID { concat( 'Repeat Screen - ', data( $order/RepeatOrderID ) ) }
					else ()
	            ),
				element ProductID { data( $print/../../../../ProductID ) },
	            element SetupText
	            {
	            	data( $pms[@PrintMethodID=$print/../../PrintMethodID]/DisplayName )
	            },
	            element SetupCharge
	            {
	                if ( exists( $print/SetupCharge ) and string-length( $print/SetupCharge ) > 0 )
	                then format-number( sum($print/SetupCharge), '#00.00' )
	                else 0 
	            }
	        }
	    },
	    element Extras
	    {
	        for $extra in $order/Products/Product
	        	[exists( ExtrasCharge ) and string-length( ExtrasCharge ) > 0 and xs:float( ExtrasCharge ) > 0]
	        return element Extra
	        {
	            element ProductID { data( $extra/ProductID ) },
	            element ExtrasItems
	            {
	                string-join
	                (
	                    (
	                        if ( $extra/Extras/RemoveNotepad = 'Yes' ) then 'Remove notepad' else (),
	                        if ( $extra/Extras/InsertNotepad = 'Yes' ) then 'Insert notepad' else (),
	                        if ( $extra/Extras/InsertPen = 'Yes' ) then 'Insert pen' else (),
	                        if ( $extra/Extras/Polybag = 'Yes' ) then 'Polybag' else (),
	                        string-join( $extra/CustomExtras/CustomExtra/CustomExtraName, ', ' )
	                    ),
	                    ', '
	                )
	            },
	            element Quantity { data( $extra/Quantity ) },
	            element ExtrasCharge { format-number( $extra/ExtrasCharge, '#00.00' ) }
	        }
	    },
	    (
		    if ( $order/ExpressOrder = 'Yes' )
			then element Expresses
				    {
				        for $express in $order/Products/Product
				        return element Express
				        {
				            element ProductID { data( $express/ProductID ) },
				            element ExpressOption
				            {
				            	switch ( $express/Extras/ExpressCharge )
				            		case '30' return '3 Day Delivery'
									case '50' return '2 Day Delivery'
									default return 'Express Delivery'
				            },
				            element ExpressCharge 
				            { 
					            if ( string-length( $express/Extras/ExpressCharge ) > 0 )
				            	then format-number( $express/Extras/ExpressCharge, '#00.00' )
				            	else '0.00' 
				            }
				        }
				    }
			else ()
		),
	    element CarriageCharges
	    {
	        for $car in $order/DeliveryDetails/Delivery
			let $cc := collection( '/db/bagco/carriers' )/Carrier[@CarrierID=$car/CarrierID]
			return element CarriageCharge
			{
            	element DeliveryOption 
				{ 
                	concat( 
                    	        data( $cc/Name ), ' - ', 
								data( $cc/DeliveryOptions/DeliveryOption[@DeliveryOptionID=$car/DeliveryOptionID]/Description ) ) 
				},	        
				element CarriageCost
		        {
                    if ( exists( $car/CarriageCost ) and string-length( $car/CarriageCost) > 0 ) 
                    then format-number( $car/CarriageCost, '#00.00' )
                    else 0
		        }
			}
	    }
	}
}