Deliveries

Report Deliveries

• Filename:
• Title: Delivery report
• Sample: Unit42

Synopsis

Formulier om lijst van deliveries af te drukken.

Opmerking: Het is mogelijk dat het moeilijk te doen is de subreport aan/uit te schakelen op basis van een parameter. In dat geval moeten er 2 extra parameters komen uit het scherm: * OrderCount met het resultaat van query OrderCount * OrderGroup met het resultaat van OrdersPerCountry of OrdersPerShippingMethod.

in dat geval vervallen de parameters ShowCountryCount en ShowShippingMethodCount.

SQL

Data: OrderCount

SELECT
  COUNT(DISTINCT AVIZNO) as TOrders
FROM
  Out G
  inner join OUTDETAILS A ON (G.AVIZ=A.AVIZNO)
  Inner join INVDETAILS D ON (A.INVOICENO=D.ID)
  LEFT OUTER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
  LEFT OUTER JOIN ARTICLECATEGORY C
    ON  (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)>=C.VOL1 )
    AND (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)<C.VOL2)
    AND (isnull(B.WEIGHT, 0)>=C.WEIGHT1)
    AND (isnull(B.WEIGHT, 0)<C.WEIGHT2)
    AND (C.CategoryID=:Category)
 WHERE
   (G.Filldate>=:StartDate) AND (G.Filldate<=:EndDate)
   and ISNULL(A.TRANSACTIONTYPE, 'X')='ORD'
--  {CustomFilter}

Data: OrdersPerCountry

used only if ShowCountryCount is true.

SELECT
  SELECT COUNT(DISTINCT AVIZNO), G.COUNTRY as TOrders
FROM
  Out G
  inner join OUTDETAILS A ON (G.AVIZ=A.AVIZNO)
  Inner join INVDETAILS D ON (A.INVOICENO=D.ID)
  LEFT OUTER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
  LEFT OUTER JOIN ARTICLECATEGORY C
    ON  (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)>=C.VOL1 )
    AND (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)<C.VOL2)
    AND (isnull(B.WEIGHT, 0)>=C.WEIGHT1)
    AND (isnull(B.WEIGHT, 0)<C.WEIGHT2)
    AND (C.CategoryID=:Category)
WHERE
   (G.Filldate>=:StartDate) AND (G.Filldate<=:EndDate)
   and ISNULL(A.TRANSACTIONTYPE, 'X')='ORD'
--  {CustomFilter}
GROUP BY
  G.COUNTRY
ORDER BY
  G.COUNTRY

Shown in label12 - te vervangen door subreport.

Data: OrdersPerShippingMethod

SELECT
   COUNT(DISTINCT AVIZNO),
   (CASE WHEN H.SHIPPING_METHOD='RM2ndClass'
    then 'RoyalMail' else 'YODEL' END) as TOrders
 FROM
  Out G
  INNER JOIN XML_OUT H ON H.OrderNo=G.AVIZ
  INNER JOIN OUTDETAILS A ON G.AVIZ=A.AVIZNO
  Inner join INVDETAILS D ON (A.INVOICENO=D.ID)
  LEFT OUTER JOIN ARTICLE B ON A.ARTCODE=B.NAME
  LEFT OUTER JOIN ARTICLECATEGORY C
    ON  (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)>=C.VOL1 )
    AND (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)<C.VOL2)
    AND (isnull(B.WEIGHT, 0)>=C.WEIGHT1)
    AND (isnull(B.WEIGHT, 0)<C.WEIGHT2)
    AND (C.CategoryID=:Category)
WHERE
  (G.Filldate>=:StartDate) AND (G.Filldate<=:EndDate)
  and ISNULL(A.TRANSACTIONTYPE, 'X')='ORD'
--  {CustomFilter}
GROUP BY
   (CASE WHEN H.SHIPPING_METHOD='RM2ndClass' then 'RoyalMail' else 'YODEL' END)
 ORDER BY
   TOrders

Shown in label12 - te vervangen door subreport 2.

Parameters

• StartDate (TDateTime)
• EndDate (TDateTime)
• Category (Integer)
• ShowCountryCount (Boolean)
• ShowShippingMethodCount (Boolean)
• CustomFilter (string, macro)

Accessible in forms

• InvoiceReports