Useful RMS DB Intergration C#/C++ SQL

Discussion in 'Microsoft Point Of Sale' started by cmoski, Sep 25, 2012.

  1. cmoski

    cmoski

    Joined:
    Jun 7, 2012
    Messages:
    1
    Likes Received:
    0
    Useful RMS DB Intergration C#/C++ (Sales, Trending Data)

    Integrating with the RMS database in C#? Looking for a few code snippets to automate export to sales analysis services (such as Lokad?)

    Here are a few useful code snippits..

    This will return all orders since the beginning of time with ItemLookupCode, OrderDate and Quantity:
    Code:
       string ordersMSSQL = "SELECT Item.ItemLookupCode As Item_Id, Time As OrderDate, TransactionEntry.Quantity As Quantity\n" +
             "FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber\n" +
             "INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber\n" +
             "LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID\n" +
             "LEFT JOIN   Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID\n" +
             "LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID\n" +
             "LEFT JOIN   Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID\n" +
             "LEFT JOIN   ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID\n" +
             "LEFT JOIN   ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID\n" +
             "LEFT JOIN   ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID\n" +
             "LEFT JOIN	Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID\n" +
             "LEFT JOIN   Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID\n" +
             "LEFT JOIN   Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID\n" +
             "LEFT JOIN   QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID\n" +
             "WHERE Department.ID IS NOT NULL ORDER BY TransactionEntry.Quantity Asc";
    This will show you the number of items you have on order:
    Code:
                string sqlItemsOnHand = "SELECT ItemID,ItemLookupCode,Description,SUM(QuantityOrdered - QuantityReceivedToDate) AS OnOrderQty FROM PurchaseOrderEntry\n"+
                    "LEFT JOIN PurchaseOrder ON PurchaseOrderEntry.PurchaseOrderID = PurchaseOrder.ID \n"+
                    "AND PurchaseOrderEntry.StoreID = PurchaseOrder.StoreID \n"+
                    "LEFT JOIN   Item WITH(NOLOCK) ON ItemID = Item.ID\n"+
                    "WHERE PurchaseOrder.Status <> 2 AND (PurchaseOrder.POType <> 3 AND PurchaseOrder.POType <> 5)\n"+
                    "GROUP BY ItemID,Description,ItemLookupCode\n";
    This will grab the average lead time per item over the last backCount number of months:
    Code:
                string ordersMSSQL = "SELECT Item.[ItemLookupCode],PurchaseOrderEntry.ItemDescription AS Description, Avg(DATEDIFF(DAY,RequiredDate,DateTransferred)) As Diff, Min(DATEDIFF(DAY,RequiredDate,DateTransferred)) As Min,Max(DATEDIFF(DAY,RequiredDate,DateTransferred)) As Max\n" +
                    "From InventoryTransferLog \n" +
                    "LEFT OUTER JOIN Item on Item.ID = InventoryTransferLog.ItemID\n" +
                    "LEFT OUTER JOIN PurchaseOrderEntry ON InventoryTransferLog.ReferenceEntryID = PurchaseOrderEntry.ID\n" +
                    "LEFT OUTER JOIN Serial ON Serial.ID = InventoryTransferLog.DetailID\n" +
                    "LEFT OUTER JOIN PurchaseOrder ON PurchaseOrderEntry.PurchaseOrderID = PurchaseOrder.ID\n" +
                    "WHERE RequiredDate > DATEADD(month,"+backCount.ToString()+",GETDATE()) AND ItemLookupCode IS NOT NULL\n" +
                    "GROUP BY ItemDescription, ItemLookupCode";
    This will dump the items database as compatible with Lokad sales tools creating product categories as a unique tag including stock on hand:
    Code:
    string ordersMSSQL = "SELECT Item.ItemLookupCode As Id, Item.Description as LabelProductName, Category.Name As TagLabelProductCategory, Quantity As StockOnHand FROM dbo.Item\n" +
                    "LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID\n" +
                    "LEFT JOIN   SupplierList WITH(NOLOCK) ON Item.ID = SupplierList.ItemID\n" +
                    "ORDER BY Description ASC";
    This will GetSalesByDepartmentDate (put this in a daily sales screensaver):
    Code:
                   public DataTable GetSalesByDepartmentDate(int Day = 0, int Month = 0, int Year = 0)
            {
                DataTable curSales = new DataTable();
                string sqlSelect = "SELECT Department.Name, Sum(TransactionEntry.Quantity) As Quantity FROM TransactionEntry WITH(NOLOCK)\n" +
                "INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber\n" +
                "LEFT JOIN Item  WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID\n" +
                "LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID\n" +
                "LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID\n" +
                "WHERE DATEPART(YEAR, TIME) = DATEPART(YEAR,Getdate()) AND\n" +
                "DATEPART(MONTH,TIME) = DATEPART(MONTH,Getdate()) AND\n" +
                "DATEPART(DAY, TIME) = DATEPART(DAY,Getdate())\n" +
                "GROUP BY Department.Name\n";
                m_sqlConnectionRMS.Open();
                try
                {
                    SqlCommand cmdIns = new SqlCommand(sqlSelect, m_sqlConnectionRMS);
                    SqlDataReader dr = cmdIns.ExecuteReader();
                    curSales.Load(dr);
                }
                catch (Exception e)
                {
                    m_sqlConnectionRMS.Close();
                }
                m_sqlConnectionRMS.Close();
                return curSales;
            }
    
    Here is a function to insert XML order data (pre-generated) into the internet orders table:
    Code:
     public void insertXMLToRMSTable(string date_created, string date_updated, int orderid, string xmlData)
            {
                string sqlIns = "INSERT INTO Exchange (DateCreated,LastUpdated,ProcessorCode,Data,Status ,Comment) VALUES (@date_created,@date_updated, 'YahooStore', @data, 0, @comment)";
                m_sqlConnectionRMS.Open();
                try
                {
                    SqlCommand cmdIns = new SqlCommand(sqlIns, m_sqlConnectionRMS);
                    cmdIns.Parameters.AddWithValue("@date_created", date_created);
                    cmdIns.Parameters.AddWithValue("@date_updated", date_updated);
                    cmdIns.Parameters.AddWithValue("@data", xmlData);
                    cmdIns.Parameters.AddWithValue("@comment", "WebOrder: " + orderid + " Created: " + date_created + " Updated: " + date_updated);
                    cmdIns.ExecuteNonQuery();
                    cmdIns.Dispose();
                    cmdIns = null;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.ToString(), ex);
                }
                finally
                {
                    m_sqlConnectionRMS.Close();
                }
            }
    Finally, a code example of exporting tranding information by category:
    Code:
         private string SQLStoredProcedure_AggroCategory(string where, string title = "")
            {
                string sqlSelect = "SELECT CAST(CAST(DATEPART(Month, Time) AS VARCHAR(2)) + CAST('/' AS VARCHAR(1)) + CAST('1/' AS VARCHAR(2)) + CAST(DATEPART(YEAR, Time) AS VARCHAR(4))As DateTime) As DateTime,\n" +
    "Category.Name As Description,Sum(TransactionEntry.Quantity) as QSum, \n" +
    "DATEPART(Month, Time) As Month, DATEPART(YEAR, Time) As Year \n" +
     "FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber \n" +
             "INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber\n" +
             "LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID \n" +
             "LEFT JOIN   Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID \n" +
             "LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID \n" +
             "LEFT JOIN   Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID \n" +
             "LEFT JOIN   ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID \n" +
             "LEFT JOIN   ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID\n" +
             "LEFT JOIN   ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID\n" +
             "LEFT JOIN	Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID\n" +
             "LEFT JOIN   Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID\n" +
             "LEFT JOIN   Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID\n" +
             "LEFT JOIN   QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID\n";
                if (where.Length > 2)
                    sqlSelect += "WHERE " + where + " AND (Department.ID IS NOT NULL) AND (Item.Description IS NOT NULL) \n";
                else
                    sqlSelect += "WHERE (Department.ID IS NOT NULL) AND (Item.Description IS NOT NULL) \n";
    
    
                sqlSelect += "GROUP BY DATEPART(YEAR, Time),DATEPART(Month, Time), Category.Name\n" +
                "ORDER BY Year, Month, QSum DESC\n";
                return sqlSelect;
            }
    
     
    Last edited: Sep 25, 2012
    cmoski, Sep 25, 2012
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.