When writing creating reports in the Report Writer its important , you need to know where how data is stored in the applicationdatabase. This is a guide to commonly used tables.
Table Name | Purpose | Keys | Joining | Comments | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Product | Storing product data integrated from ERP with additional fields that might only be stored online | ProductCode | To get all items that are shown on the website use the where clause: itemvalid = 1 Generally field names from ERP will be the same online but without the dashes. | |||||||||||
ProntoStockWarehouse | A mirror of the PRONTO ERP table stock-warehouse-detail | StockCode WhseCode | Product:
| |||||||||||
ProntoSalesOrder | Sales Order header table. Stores both carts online and integrated orders from ERP | SoOrderNo | Customer:
User:
| Filter on orders that were created online and have been integrated to ERP: ConfirmedbyERP = 1 Filter on orders that got created in ERP: fwcreatedby = 'WS' Filter on carts in progress: fwcreatedby != 'WS' and soordertotalpackages > 0 Filter on Ready to Integrate: sendrequired = -1 | ||||||||||
ProntoSalesOrderLine | Sales order lines | SoOrderNo SoBoSuffix SolLineSeq | ProntoSalesOrder:
Product:
| |||||||||||
CustomerPayments | Payments received either associated with a order or could also be associated with account payments | RowId | ProntoSalesOrder:
User:
| Status: 1 = ready to integrate 2 = integrated 9 = payment failed Also note for account payments the OrderNumber will be 0 so if you are joining to ProntoSalesOrder suggest to use a LEFT OUTER JOIN instead of LEFT OUTER JOIN | ||||||||||
User | All users | EmailAddress | ProntoSalesOrder:
Customer:
| Note that all use of User table must use [User] and not User Best to ignore guest users which will have emailaddress like 'guest%' | ||||||||||
UserSession | Top level details of every session | EmailAddress | User:
| Encourage to put a date range on the extract of this as it can be large file | ||||||||||
UserSessionLog | Detailed information on user session | EmailAddress | UserSession:
| This table is only stored for maximum of 4 weeks due to its size Filter on EventType to get details. See below for UserSessionLog Event Types |
UserSessionLog Event Types
Event | Description | Other Data |
---|---|---|
CategoryView | A view of a category | EventKey1 = CategoryCode Description = Category Name |
OrderPlaced | The placing of an order | EventAmount = Order Value |
PageView | The viewing of a page | EventKey1 = Pagename and role E.G: (PUBLICR) ProductDisplay.aspx means PUBLICR role and productdisplay.aspx is the name of the page |
ProductInCart | A product being placed in the cart | EventKey1 = ProductCode |
ProductOutOfCart | A product being taken out of the cart | EventKey1 = ProductCode |
ProductSearch | A search of products | EventKey1 = Search Key |
ProductSold | Sale of a product | EventKey1 = ProductCode EventQty = Qty EventAmount = Qty * Price |
ProductView | Detailed view of a product | EventKey1 = ProductCode EventAmount = Price |
Trace | Trace of milliseconds to create the page | Description = PageName |
Related
...
Resources
Content by Label | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|