Page History

...

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:

Code Block

language	sql

Product LEFT OUTER JOIN ProntoStockWarehouse 
on ProductCode = StockCode

ProntoSalesOrder

Sales Order header table. Stores both carts online and integrated orders from ERP

SoOrderNo
SoBoSuffix

Customer:

Code Block

language	sql

ProntoSalesOrder LEFT OUTER JOIN Customer 
on ProntoSalesOrder.socustcode = Customer.Code

User:

Code Block

language	sql

ProntoSalesOrder LEFT OUTER JOIN [User]
on ProntoSalesOrder.souseridcode= [User].EmailAddress

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:

Code Block

language	sql

ProntoSalesOrderline LEFT OUTER JOIN 
ProntoSalesOrder on ProntoSalesOrderline.SoOrderNo = 
ProntoSalesOrder.SoOrderNo
AND 
ProntoSalesOrderline.SoBoSuffix = 
ProntoSalesOrder.SoBoSuffix

Product:

Code Block

language	sql

ProntoSalesOrderline LEFT OUTER JOIN 
product on ProntoSalesOrderline.StockCode = 
Product.ProductCode

CustomerPayments

Payments received either associated with a order or could also be associated with account payments

RowId

ProntoSalesOrder:

Code Block

language	sql

CustomerPayments LEFT OUTER JOIN Prontosalesorder 
on CustomerPayments.OrderNumber = 
ProntoSalesOrder.SoOrderNo

User:

Code Block

language	sql

CustomerPayments LEFT OUTER JOIN [User]
on CustomerPayments.UserName = 
[user].emailAddress

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:

Code Block

language	sql

ProntoSalesOrder LEFT OUTER JOIN [User]
on ProntoSalesOrder.souseridcode= [User].EmailAddress

Customer:

Code Block

language	sql

[User] LEFT OUTER JOIN Customer on 
[User].CustomerCode = Customer.Code

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
SessionStart

User:

Code Block

language	sql

UserSession LEFT OUTER JOIN [User] on UserSession.EmailAddress = 
[User].Emailaddress

Encourage to put a date range on the extract of this as it can be large file

UserSessionLog

Detailed information on user session

EmailAddress
SessionStart
Seq

UserSession:

Code Block

language	sql

UserSessionLog LEFT OUTER JOIN UserSession on
UserSession.EmailAddress = 
UserSessionLog.Emailaddress

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 EventQty = Qty EventAmount = Qty * Price
ProductOutOfCart	A product being taken out of the cart	EventKey1 = ProductCode
ProductSearch	A search of products	EventKey1 = Search Key EventQty = Number of results returned
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 EventQty = Milliseconds to create the page

...

Help

Content by Label

showLabels	false
max	5
spaces	KB
showSpace	false
sort	modified
reverse	true
type	page
cql	label = "reporting" in ("report-creation","field-groups","generating-reports") and type = "page" and space = "KB"
labels	reporting

Space shortcuts

Page tree

Versions Compared

Old Version 1

New Version 2

Key

UserSessionLog Event Types

Related

Help