Form Inventory

Unit name: Forms.Inventory
Form name: InventoryForm
Old form name: Form69 (unit69)

Synopsis

Form to manage the inventory.

Functionalities

The form contains 2 grids, side by side, plus some action buttons
The first grid shows the available inventories (INVENTORY), filtered on :startdate
```
 (creationdate>=:StartDate)
```
By default StartDate equals today minus 300 days. Order by date.
The second grid shows the details of the selected inventory (INVENTORYDET) filtered on IDNO:
```
  (IDNO=:ID)  
```
Where ID is the ID of the selected inventory.
There is a button 'Add' to execute the Add action. (optional button)
There is a button 'Refresh' to execute the Refresh action. (optional button)
There is a button 'Delete record' to execute the delete action. (optional button)
There is a button 'Export' to execute Export action. (optional button)
There is a button 'Export differences' to execute the export action. (optional button)

Actions

Add

Always active.
When executed, uses the InventoryDetails form to fetch the following Parameters StartRow (IRow1) EndRow (IRow2) StartPlace (IPlace1) EndPlace (IPlace2) StartLevel (ILevel1) EndLevel (ILevel2)
Using the parameters, inserts a row into Inventory, (type N) makes it the current row. (post to server)
Updates detail grid.

Refresh

Action refreshes the data from the second grid.
Only active when there is an inventory.

Delete

Only active if there is a current line in the inventory detail grid?
When executed, asks the user for confirmation.
If the user confirms, deletes the current line in the inventory detail grid.

Export

Only active when an inventory was chosen.
When executed, exports as CSV the inventoryexport query: where
CustomHaving is empty.
ID is the ID of the currently selected inventory.
CustomFilter is a filter on location, depending on the inventory type: For type L, the filter is on the result of:
```
  Select distinct LOCATION from LOCATION WHERE (INVENTORYID=:ID)
```
if it is non-empty. If it is empty, CustomFilter is empty.

for type N, the filter is on :

  AND (RIGHT(LEFT(A.LOCATION, 7), 2)>=P1)
  AND (RIGHT(LEFT(A.LOCATION, 7), 2)<=P2)
  AND (RIGHT(LEFT(A.LOCATION, 4), 2)>=R1)
  AND (RIGHT(LEFT(A.LOCATION, 4), 2)<=R2)
  AND (RIGHT(A.LOCATION, 1)>=L1)
  AND (RIGHT(A.LOCATION, 1)<=L2)

Where P1, P2, R1, R2, L1 and L2 are the fields as they appear in the inventory table. (maybe check if it is easier to add Inventory table to SQL)

Export differences.

Only active when an inventory is chosen.
There is a button 'Export differences' which exports as CSV the same query as previous, with the difference that CustomHaving equals
```
HAVING A.QTY<>ISNULL(sum(b.qty), 0)
```
There is a button "Create difference for scanners". It executes the following server call: StockControl.CreateInventoryScannerList

Tables/Views

INVENTORY (use for inventory, where filter on CreationDate)
INVENTORYDET
INVENTORYEXPORT

WITH TempData AS (
SELECT
  artcode, name2, groupe, A.LOCATION,
  sum(qtyin-isnull(qtyout, 0)) as qty
FROM
  STOCTOTAL A
  INNER JOIN ARTICLE B ON (a.artcode=b.name)
GROUP BY
  artcode, name2, groupe, A.LOCATION
HAVING
  sum(qtyin-isnull(qtyout, 0))>0
ORDER BY
  artcode
)
SELECT
  a.Artcode, a.Qty as StockQTY, sum(b.qty) as ScanQty,
  '' as Description, A.LOCATION
from
  TempData A
  LEFT OUTER JOIN INVENTORYDET B ON
    (a.artcode=b.artcode)
    AND (A.LOCATION=B.LOCATION)
WHERE
  (isnull(a.artcode, '')<>'')
  AND (B.IDNO=:ID)
-- {CustomFilter}

GROUP BY
  a.artcode, a.name2, a.groupe , a.qty, A.LOCATION
-- {CustomHaving}
UNION
SELECT
  a.Artcode, '0' as StockQTY, sum(a.qty) as ScanQty,
  'Only in warehouse', A.LOCATION
FROM
  INVENTORYDET A
  INNER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
WHERE
  AND (A.IDNO=:ID)
  AND (A.artcode not in (select artcode from tmpData))
--  {CustomFilter}
GROUP BY
  a.artcode,  name2, groupe, A.LOCATION
ORDER BY
  A.LOCATION, a.artcode

Needed Service Calls

Reporting.PrepareReport

Needed for inventory grouped by article.

StockControl.CreateInventoryScannerList

Current inventory is passed on as ID.

Reports

InventoryByArticle
Parameter IDNO is the currently selected inventory
Macro CustomHaving is set when 'Only differences' is checked

Macro CustomFilter is built up from list of locations. For type L, this is the result of

Select distinct LOCATION from LOCATION WHERE (isnull(INVENTORYID, 0)=:IDNO)

For type F this is the result of

Select distinct LOCATION from INVENTORYDET WHERE (isnull(IDNO, 0)=:IDNO)

so the filter becomes

 AND A.LOCATION IN (FilterSQL);
 ```    
if the supplier filter is active, it is added to the custom filter.

* [InventoryByLocation](../reports/inventorybylocation.md)
  * Parameter **IDNO** is the currently selected inventory
  * Macro **CustomHaving** is set when 'Only differences' is checked
  * Macro **CustomLocationFilter** is built up from list of locations.
  For type **L**, this is the result of

Select distinct LOCATION from LOCATION WHERE (isnull(INVENTORYID, 0)=:IDNO)

  For type **F** this is the result of

Select distinct LOCATION from INVENTORYDET WHERE (isnull(IDNO, 0)=:IDNO)

so the filter becomes

AND A.LOCATION IN (FilterSQL); ```

Macro CustomSupplierFilter: if the supplier filter is active, it is passed on in this macro. If not, the macro is empty.
InventoryScanList
Parameter IDNO is the currently selected inventory
Macro CustomFilter contains the active filter at the top of the screen.