Useful RMS DB Intergration C#/C++ SQL


Joined
Jun 7, 2012
Messages
1
Reaction score
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:
Ad

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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

S or C? to be a C, or not to be? 0
Schedule C and Wife's Credit Used 1
Colorado LLC use Schedule C? 5
schedule C 9
C-47 2
c=342? 5
Schedule C 1
1099-C 16

Top