CCS Point of Sale

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
guides:reporting:custom_report [2016/03/27 23:49]
markphillips2
guides:reporting:custom_report [2018/12/23 12:11] (current)
Line 139: Line 139:
 ===== Report for Oregon Medical Marijuana Sales DOB ===== ===== Report for Oregon Medical Marijuana Sales DOB =====
  
-SELECT OrderID,DOB  +SELECT OrderID,​DOB ​\\ 
-FROM tblproductOrders +FROM tblproductOrders\\ 
-LEFT JOIN tblclients on  +LEFT JOIN tblclients on \\ 
-tblclients.clientid=tblProductorders.clientid +tblclients.clientid=tblProductorders.clientid\\ 
-WHERE [CompletionDate] BETWEEN '​10/​01/​2015'​ AND '​3/​31/​2016'​+WHERE [CompletionDate] BETWEEN '​10/​01/​2015'​ AND '​3/​31/​2016'​\\ 
 +AND (ServiceType='​Rec Sale' OR ServiceType='​Med Sale' Or ServiceType='​Recommendation'​)
  
  
Line 150: Line 151:
 You will want to make sure that your Marijuana products are all departmentalized as "​Marijuana"​ this will ensure the report pulls only Marijuana Item Sales. ​ You will want to make sure that your Marijuana products are all departmentalized as "​Marijuana"​ this will ensure the report pulls only Marijuana Item Sales. ​
  
-SELECT CompletionDate,​Products.ProductName,​Quantity +SELECT CompletionDate,​Products.ProductName,​Quantity\\ 
-FROM [tblProductOrderdetails] as Details +FROM [tblProductOrderdetails] as Details\\ 
-INNER JOIN tblProducts as Products +INNER JOIN tblProducts as Products\\ 
-ON Products.ProductID=Details.ProductID +ON Products.ProductID=Details.ProductID\\ 
-INNER JOIN tblProducts as Departments +INNER JOIN tblProducts as Departments\\ 
-ON Departments.ProductID=Products.DepartmentID +ON Departments.ProductID=Products.DepartmentID\\ 
-WHERE [CompletionDate] BETWEEN '​10/​01/​2015'​ AND '​3/​31/​2016'​ +WHERE [CompletionDate] BETWEEN '​10/​01/​2015'​ AND '​3/​31/​2016'​\\ 
-AND [Products].[ProdModifierType]=1 +AND [Products].[ProdModifierType]=1\\ 
-AND Departments.ProductName='​Marijuana'​ +AND Departments.ProductName='​Marijuana'​\\ 
-ORDER BY [Products].[ProductName] ​+ORDER BY [Products].[ProductName] ​\\ 
 + 
 +===== Oregon Reporting - Group Sales by Item Type ===== 
 + 
 +Items must be '​Typed'​ by Subcategory according to the naming rules of Oregon. This applies to the report, as well as the requirement for Receipts.\\ 
 +Use the Subcategory Column - with the the Update Items Searched feature (as shown in the video) To assign the Subcategory the name of the type of each item, so that it will show on the receipts as dictated by the State\\ 
 + 
 +==== Identify your Patient'​s TypeID ==== 
 +Make sure that this report is pulling the right type of customers, your patients. Change the ClientType in the statement below to include the proper ID from your client type table.  
 +Run this statement from the Pivot Reporting module:\\ 
 +Select * FROM tblClientType\\ 
 +Then Identify which ClientTypeID is the one that is associated with your Patients. We have had people name their patient types different names, so this variable is something you'll need to verify.\\ 
 + 
 + 
 +==== Configuration==== 
 + 
 +Watch this video to get started and understand the following things you'll need to do and navigate. It will show you how to use the item search section to update and modify items like you will need to.\\ 
 +[[https://​www.youtube.com/​watch?​v=jqSnH5Yv7gc|Product Search and Update|]] 
 + 
 +1. Office - (Main toolbar) - Inventory - Product Search 
 +2. Search for the items you wish to update.  
 + 
 +==== Running the Report ==== 
 +Replace the date range where needed. 
 + 
 +SELECT [ClAccountNumber],​Subcategory.ProductName,​UnitName,​SUM(Quantity) \\ 
 +FROM [dbo].[tblProductOrderDetails] AS Details \\ 
 + 
 +LEFT JOIN [dbo].[tblProducts] AS Products \\ 
 +ON Products.ProductID = Details.ProductID \\ 
 + 
 +LEFT JOIN [dbo].[tblProducts] AS Subcategory \\ 
 +ON Subcategory.ProductID = Products.[productsubtypeID] \\ 
 + 
 +LEFT JOIN tblClients \\ 
 +ON tblClients.[ClientID]=Details.[ClientID] \\ 
 + 
 +WHERE [ClientType]=10 \\ 
 +AND [CompletionDate] BETWEEN '​06/​01/​2016'​ AND '​06/​30/​2016'​ \\ 
 +GROUP BY [ClAccountNumber],​Subcategory.ProductName,​UnitName \\ 
 +ORDER BY [ClAccountNumber],​Subcategory.ProductName,​[UnitName]\\ 
 + 
 +===== Quarterly Sales Taxes Grouped By Item Type ===== 
 + 
 +Change the date range:\\ 
 + 
 +SELECT SUM(LineTotal),​\\ 
 +Subcategory.ProductName,​UnitName,​SUM(Quantity),​ \\ 
 +Sum(UnitPrice*Quantity) AS NetSales, \\ 
 +SUM(LineTotal) AS TaxTotal, ​ \\ 
 +Sum(UnitPrice*Quantity) + SUM(LineTotal) AS GrossSales\\ 
 +FROM [dbo].[tblProductOrderDetails] AS Details\\ 
 +LEFT JOIN [dbo].[tblProducts] AS Products \\ 
 +ON Products.ProductID = Details.ProductID \\ 
 +LEFT JOIN [dbo].[tblProducts] AS Subcategory \\ 
 +ON Subcategory.ProductID = Products.[productsubtypeID] \\ 
 +WHERE\\ 
 +Details.ServiceType='​Rec Sale' \\ 
 +AND [LineTotal]<>​0\\ 
 +AND Details.[CompletionDate] BETWEEN '​07/​24/​2016'​ AND '​07/​25/​2016'​\\ 
 +GROUP BY Subcategory.ProductName,​UnitName \\ 
 +ORDER BY Subcategory.ProductName,​[UnitName]\\ 
  

Navigation

^From the Ground Up!| |[[Getting Started:]]| |[[Installation Guide:]]| |[[Pre Installation:]]| |[[Install CCS:]]| |[[Install New Database:]]| ^Guides| |[[Guides:]]| |[[FAQ:]]| |[[Trouble Shooting:]]| ^Credit Card Setup| |[[Data Cap Epay:]]| ^CCS Administration| |[[ Office:Office - General Management]]| ^CCS Terminal| |[[Office:Terminal]]| ^Hand Held Setup| |[[Hand Held:|Tablet]]| ^Kitchen Display| |[[KDS Software Setup:]]|