Form StockReport
- Unit name: Forms.StockReports
- Form name: StockReportsForm
- Old form name: unit124, Form124.
Synopsis
Form to print out stock reports.
Functionalities
This form allows to choose one of several reports:
- Stock report 1 StockReport1
- Stock report 2 StockReport2
- Expiring goods report. ExpiringGoods
- Dead articles PopularArticles
- Popular articles PopularArticles
- Empty locations EmptyLocations
- Expired Lots ExpiredLots
- Expiring Lots ExpiringLots
- Multiple locations MultipleLocations
- Articles - weights, dimensions ArticleVolumeWeights
- Articles with messages ArticlesWithMessages
- ADR Stock report ADRStockReport
- Norwex Disabled local articles with stock NRWDisabledLocalWithStock
- Norwex local articles without stock NRWLocalArticlesWithoutStock
For each of the reports, some options can be given. When a report is selected, the possible options for this report are shown.
A button 'print' will then prepare the selected report using the appropriate server call, and print the report. After the report is printed, the CleanStandardReport call is executed to clear the temporary table.
In a separate dropdown it allows to select exports 1. Export SP stock for TV SHOP (TVShopExport) 3. Export CSV order for SPIRITULISM (SPIRITULISMExport) 3. Norwex Global/Local Report (NorwexGlobalLocalExport) 3. Norwex NO+SE Report (NorwexNOSEExport) Below it, a filename can be selected. For the Norwex exports, a date can be selected. A button 'Export' will then create the export.
Filters
The printed data/exported data can be filtered in several ways:
Date filter
The form allows to select a stock date using a date picker (parameter StockDate in the report and preparation).
Data filter:
For some reports, the form allows to set up a filter on the following fields/expressions: 1. A.ArtCode 1. A.ExpiryDate 1. A.LotNo 1. A.StockCode 1. A.SUPPLIER 1. A.LOCATION 1. LEFT(A.LOCATION, 1) 1. RIGHT(A.LOCATION, 1)
Needed Service Calls
PrepareReport
To prepare stockreport1 and stockreport2 Parameters include: * Filter (as entered by user) * IncludeKitReservedStock (Boolean) * IncludeNotmalReservedStock (Boolean) * StockDate (TDateTime) * AssumeNorwex (Boolean)
For least/most popular articles the parameters are: * Supplier (string, optional), the selected code of a supplier * Period (integer) the period in days * Kind (string): one of least or most, depending on the chosen report.
CleanStandardReport
:JobID
Reports
Stock Report 1
The following options/filters are shown: 1. Stock date (date picker) for StockDate 2. Data filter (as explained above) 3. Checkbox: Include Reserved stock (normal orders) for IncludeNormalReservedStock 4. Checkbox: Include Reserved stock (kit orders) for IncludeKitReservedStock
The AssumeNorwex parameter to PrepareReport is false.
Stock Report 2
The following options/filters are shown: 1. Stock date (date picker) for StockDate 2. Data filter (as explained above) 3. Checkbox: Include Reserved stock (normal orders) for IncludeNormalReservedStock 4. Checkbox: Include Reserved stock (kit orders) for IncludeKitReservedStock 4. Checkbox: Use global codes (Norwex only) for AssumeNorwex
Expiring goods
The following options/filters are shown: 1. Stock date for StockDate (stockdate is 1+Selected date) 2. Data filter (as explained above) for macro CustomFilter. 3. Combobox with number of days (15,30,60,90,120, allow custom entry) for Days parameter.
Popular articles
The following options/filters are shown: 1. Supplier 2. Period: in days.
Empty locations
The following options are shown: 1. Checklistbox with possible first letters of locations The selected first letters are passed on in the LocationList macro.
Expired Lots
No options are needed.
Expiring Lots
The following options are shown: 1. combobox showing number of months (1..12, default 6)
- Passed on as Months parameter,
- Parameter MaxExpiryDate is calculated as IncMonth(Date,Months)
Multiple Locations
The following options are shown:
* Customer (list of suppliers)
* Locations filter:
1. Only A Locations
2. All locations
Articles - weights, dimensions
The following options are shown: * Supplier: (list of suppliers, optional, report parameter Supplier) Chosen Supplier (if any) is additionally passed as CustomFilter macro to the report with
AND (A.SUPPLIER=:Supplier)
Articles with messages
The following options are shown: * Supplier: (list of suppliers, optional, report parameter Supplier) Chosen Supplier (if any) is additionally passed as CustomFilter macro to the report with
AND (FSUPPLIER=:Supplier)
ADR Stock report
No filters or parameters are needed.
Tables
- SUPPLIERS Supplier list for popular articles or multiple locations (global lookup).
- LOCATION List of locations (global lookup).
Exports
TVShopExport
This export calls the PrepareReport servicecall with ExcludeSpareParts equal to True, and exports the result of the following query: (where JobID is)
SELECT
A.ARTCODE,
B.NAME2,
A.STOCKCODE,
sum(isnull(QTYSTOCK, 0)) as QTYSTOCK,
FROM
Printstock A INNER JOIN ARTICLE B ON (ARTCODE=NAME)
WHERE
(A.JobID=:JobID)
GROUP BY
A.ARTCODE, B.NAME2, A.STOCKCODE
HAVING
sum(isnull(QTYSTOCK, 0))>0
UNION
-- SPARE PARTS WITH ZERO STOCK
SELECT
NAME AS ARTCODE,
NAME2,
'SP' AS STOCKCODE,
0 as QTYSTOCK
FROM
ARTICLE A
WHERE
isnull(ACCESORIES, 'N')='Y'
AND NAME NOT IN
(SELECT DISTINCT ARTCODE FROM PRINTSTOCK WHERE PrintStock.JobID=:JOBID)
group by artcode having sum(isnull(QTYSTOCK, 0))>0)
ORDER BY ARTCODE
article description stockqty
------------------------------ ------------------------------ -----------
SPIRITULISMExport
The spiritulism export exports the result of the following query as a plain CSV file, fields separated by semicolons:
SELECT DISTINCT
ORDERNO as orderID,
DClientLastName as name,
DClientFirstName as firstname,
'' as company,
CAST(REPLACE(DClientAddress, dbo.GR_GetStreetNo_Hermes(DClientAddress) , '') as varchar(100)) as Street,
dbo.GR_GetStreetNo_Hermes(DClientAddress) AS Street_nr,
DClientTownCode as zip,
DClientTown as city,
IClientMail as email,
DClientPhone as telephone,
DClientCountry as country,
from
SPIR_ORDER A
WHERE
(RSystemDate=:StockDate)
ORDER BY
ORDERNO ASC
orderID;name;firstname;company;street;street_nr;zip;city;email;telephone;country;
NorwexGlobalLocalExport
The Norwex Global Local export exports the following SQL data to Excel: :StartDate and :EndDate are the first and last day of the month of the selected date.
SELECT
MONTH(OUTDETAILS.filldate) as MonthD,
RTRIM(Right(REPLACE(artcode, '.', ' '), 30)) as artcode_sl,
sum(CASE WHEN Country in ('DE', 'GE', 'GER') then QTY else 0 end) as DE_QTY,
sum(CASE WHEN Country in ('IE') then QTY else 0 end) as IE_QTY,
sum(CASE WHEN Country in ('UK') then QTY else 0 end) as UK_QTY,
sum(CASE WHEN Country in ('NO') then QTY else 0 end) as NO_QTY,
sum(CASE WHEN Country in ('SE', 'SWE', 'S') then QTY else 0 end) as SE_QTY,
sum(CASE WHEN Country not in ('DE', 'GE', 'GER', 'IE', 'UK', 'NO', 'SE', 'SWE', 'S')
THEN QTY ELSE 0 END) AS OTHER_QTY,
SUM(qty) as Total_QTY
FROM
OUTDETAILS
INNER JOIN OUT ON (AVIZNO=AVIZ)
WHERE
(LEFT(artcode, 2)='NW')
AND (ARTCODE like '%.%')
AND (LEFT(AVIZNO, 2)<>'M-')
AND (LEFT(AVIZNO, 2)<>'T-')
AND (LEFT(AVIZNO, 3)<>'COR')
AND (OUTDETAILS.FILLDATE BETWEEN :StartDate and :EndDate)
GROUP BY
MONTH(OUTDETAILS.filldate),
RTRIM(Right(REPLACE(artcode, '.', ' '), 30))
ORDER BY
MonthD desc, artcode_sl
NorwexNOSEExport
The Norwex NO+SE export exports the following SQL data to Excel: :StartDate is the selected date, and :EndDate is the selected date +1.
SELECT
SalesOrderNo, ShipmentNumber, A.ServiceCode, A.Shiptoname, A.Address1,
A.Address2, A.Street, A.AddtlAddress, A.ZipCode, A.City, A.CountryCode,
A.SpecificField1 AS Email, A.Phone,
A.SalesOrderreference, A.OrderDate, A.BlockNo, A.Pickinstruction,
A.Deliveryinstruction, A.FileName
FROM
NRW_OUT A
INNER JOIN OUT B ON (A.ShipmentNumber=B.AVIZ)
WHERE
(B.DATA>=:StartDate)
AND (B.DATA<:EndDate)
AND (A.COUNTRYCODE IN ('SE', 'NO'))
ORDER BY
A.ShipmentNumber ASC