Pricemaps PHYSICAL Table Structure
This page contains technical detail about the physical database structure. It is not required for normal day to day operation of the Point Of Sale.
|pmid||long||Primary Key. Not zero||100/E||A unique internal id assigned to this row.|
|physkey||string,44 zerolen||Distributed Primary Key||117/s||Unique key allocated to this record for all time|
|priority||number||105/E||Application priority, used as a tie breaker if multiple rows can match. Larger numbers indicate higher priority.|
|enddt||date||106/s||End date that this record will no longer be selectable. Only the date part of this field is used, any time supplied is ignored.|
|startdt||date||107/s||Start date for this record. It cannot be selected until this date. Only the date part of this field is used, any time supplied is ignored.|
|unit_price||money||108/s||Absolulte price to set too.|
|pricepct||double||109/s||Discount to be applied to current price|
|comments||string, 140 zerolen||112/s||Internal comments. This are not shown to customers|
|dow||string:7||113/s||Day of week mask. Used to set pricemap entries that should only apply on certain days of the week.
If a DOW mask is present (not empty), all days default to off unless present and enabled. To enable a field place a Y (or 'y' or '1') into its day position. Sunday is the first field, followed by Monday. We recommend capital Y and N as the mask characters for maximum reliability
Mask: YY - only allow on Sunday and Monday
|timestart||datetime||114/s||Sets a start time for this pricing. Only the time component is used, and date is ignored.|
|timeend||datetime||115/s||Sets an end time for this pricing. Only the time component is used, and date is ignored.|
|cflags||long||118/E||Control flags. A bitmask.
bit 1 is set for disabled/deleted records. This bit should only be set exceptionally, records outside start/end dates are already ignored.
bit 5 (16)
Optional Product variant this can apply to. If variants are enabled the following rules are used
IndexingA unique primary key should exist on the pmid field.
The fields pid, depid, cid and locid should contain a specific value, or 0 to wildcard that field. So to set pid=65 in locid=67, you would also set depid and cid fields to zero.
If timestart and timeend are both specified, the timeend must be later than timestart, without wrapping. You cannot have a start time of 22:00 and an end time of 02:00 (2am the next morning). If you wish to cross midnight, two records are required, one with a start time of 22:00, and the other with an end time of 02:00
If unit_price and pricepct are both defined, unit_price takes priority and pricepct will be ignored
The POS uses the following style of SQL query to read this table when a product is being priced onto a sale.
select ... from pricemaps where pid in (0,PID) and depid in (0,DEPID) and cid in (0, CID) and locid in (0, CURRENT-LOCATION) order by priority descEach record is then further checked against start and end dates and times.