' ============================================= ' Repsly Product Export Process ' Created: 2025-01-XX ' Purpose: Export products to Repsly API ' - Repsly Active follows blnDataTransfer only (not mstProduct.blnActive) ' - Queue: new rows when blnDataTransfer = 1; re-queue when blnDataTransfer <> blnLastSentDataTransfer ' - Check queue table for new/updated products ' - Generate XLSX file ' - Upload to FTP ' - Push to Repsly API ' - Update queue table ' ============================================= Imports Superbowl_Daily_2.SystemFunctions Imports Superbowl_Daily_2.ProjectFunctions Imports Superbowl_Daily_2.db Imports Superbowl_Daily_2.Export_Process Imports Superbowl_Daily_2.ExcelExport Imports Superbowl_Daily_2.FTPClient Imports Superbowl_Daily_2.RepslyAPIClient Imports Newtonsoft.Json Imports System.IO Imports System.Collections.Generic Public Class RepslyProductExport_Process : Inherits Export_Process ' ============================================= ' Constructor ' ============================================= Sub New(ExportProcess As Export_Process) MyBase.New(ExportProcess.Name, ExportProcess.Filename, ExportProcess.Path, ExportProcess.SQL) Me.barProcess = ExportProcess.barProcess End Sub ' ============================================= ' Override Run Method ' ============================================= Public Shadows Sub Run() '1. Log start '2. Initialize queue (add new/updated products) '3. Query products to process '4. Generate XLSX file '5. Upload to FTP '6. Push to Repsly API '7. Update queue table '8. Log complete Me.LogProcessStart() Try '2. Initialize queue - add new/updated products InitializeQueue() '3. Query products to process Dim productsToProcess As DataSet = GetProductsToProcess() Me.intRecordsTotal = productsToProcess.Tables(0).Rows.Count If Me.intRecordsTotal = 0 Then WriteLog("info", Me.Name, "Process", "No products to process") Me.LogProcessCompleted() Return End If Me.setProgress() '4. Generate XLSX file Dim xlsxFile As String = My.Settings.dirExports & Me.Filename If Not GenerateXLSXFile(productsToProcess, xlsxFile) Then WriteLog("error", Me.Name, "Process", "Failed to generate XLSX file") Me.LogProcessCompleted() Return End If '5. Upload to FTP Dim ftpClient As New FTPClient(Me.Name) If Not ftpClient.UploadFile(xlsxFile, Me.Filename) Then WriteLog("error", Me.Name, "Process", "Failed to upload file to FTP") ' Continue anyway - API push can still work End If '6. Push to Repsly API Dim apiClient As New RepslyAPIClient(Me.Name) PushToRepslyAPI(productsToProcess, apiClient) '7. Update queue table (handled in PushToRepslyAPI) Catch ex As Exception WriteLog("error", Me.Name, "Process Exception", ex.Message & " :: " & ex.StackTrace) Finally Me.LogProcessCompleted() End Try End Sub ' ============================================= ' Initialize Queue - Add new/updated products ' ============================================= Private Sub InitializeQueue() Try WriteLog("info", Me.Name, "Initialize Queue", "Checking for new/updated products...") ' Add new products (blnDataTransfer = 1 and not in queue) Dim sqlNew As String = "INSERT INTO sysCronJobProducts (strProdCode, Status, LastUpdated) " & "SELECT strProdCode, 'Pending', GETDATE() " & "FROM mstProduct " & "WHERE blnDataTransfer = 1 " & "AND strProdCode NOT IN (SELECT strProdCode FROM sysCronJobProducts)" Dim xdb As New db xdb.doQuery(sqlNew) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " new product(s) added to queue") End If ' Mark updated products (dtStamp changed since LastProcessed) ' Note: mstProduct.dtStamp is nvarchar(12) in format YYYYMMDDHHMM (includes time) ' Convert format: 202511281535 -> 2025-11-28 15:35:00 ' SQL Server 2008 compatible (no TRY_CONVERT) - validate format first, then convert Dim sqlUpdated As String = "UPDATE sysCronJobProducts " & "SET Status = 'Updated', LastUpdated = GETDATE() " & "FROM sysCronJobProducts q " & "INNER JOIN mstProduct p ON q.strProdCode = p.strProdCode " & "WHERE p.blnDataTransfer = 1 " & "AND q.Status = 'Completed' " & "AND (q.LastProcessed IS NULL OR " & " (LEN(p.dtStamp) = 12 AND " & " ISNUMERIC(p.dtStamp) = 1 AND " & " CONVERT(datetime, " & " LEFT(p.dtStamp, 4) + '-' + " & " SUBSTRING(p.dtStamp, 5, 2) + '-' + " & " SUBSTRING(p.dtStamp, 7, 2) + ' ' + " & " SUBSTRING(p.dtStamp, 9, 2) + ':' + " & " SUBSTRING(p.dtStamp, 11, 2) + ':00', 120) > q.LastProcessed))" xdb.doQuery(sqlUpdated) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " product(s) marked as updated") End If ' Re-queue when current Data Transfer differs from last value sent to Repsly (requires blnLastSentDataTransfer; see script 15). Dim sqlDtResync As String = "UPDATE q " & "SET Status = 'Updated', LastUpdated = GETDATE() " & "FROM sysCronJobProducts q " & "INNER JOIN mstProduct p ON q.strProdCode = p.strProdCode " & "WHERE q.Status = 'Completed' " & "AND q.LastProcessed IS NOT NULL " & "AND (q.blnLastSentDataTransfer IS NULL OR q.blnLastSentDataTransfer <> p.blnDataTransfer)" Try xdb.doQuery(sqlDtResync) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " product(s) re-queued (Data Transfer out of sync with last Repsly push)") End If Catch exDt As Exception ' If script 15 not applied, fall back to re-queueing all Completed+DT off (noisier but functional). If exDt.Message.Contains("blnLastSentDataTransfer") Then WriteLog("warning", Me.Name, "Initialize Queue", "blnLastSentDataTransfer missing; run Database_Scripts/15_Add_LastSentDataTransfer_To_Product_Queue.sql — using legacy re-queue rule.") Dim sqlDtOffLegacy As String = "UPDATE q " & "SET Status = 'Updated', LastUpdated = GETDATE() " & "FROM sysCronJobProducts q " & "INNER JOIN mstProduct p ON q.strProdCode = p.strProdCode " & "WHERE q.Status = 'Completed' " & "AND p.blnDataTransfer = 0 " & "AND q.LastProcessed IS NOT NULL" xdb.doQuery(sqlDtOffLegacy) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " product(s) re-queued (legacy: Data Transfer off)") End If Else Throw End If End Try ' Reset failed products for retry (active syncs, or deactivation after a prior successful sync) Dim sqlRetry As String = "UPDATE q " & "SET Status = 'Pending', ErrorLog = NULL " & "FROM sysCronJobProducts q " & "INNER JOIN mstProduct p ON q.strProdCode = p.strProdCode " & "WHERE q.Status = 'Failed' " & "AND (p.blnDataTransfer = 1 OR (p.blnDataTransfer = 0 AND q.LastProcessed IS NOT NULL))" xdb.doQuery(sqlRetry) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " failed product(s) reset for retry") End If Catch ex As Exception WriteLog("error", Me.Name, "Initialize Queue Exception", ex.Message & " :: " & ex.StackTrace) End Try End Sub ' ============================================= ' Get Products to Process ' ============================================= Private Function GetProductsToProcess() As DataSet ' Query products that need to be processed ' Mapping per Repsly requirements: ' strProdCode -> Code ' Name = product name + package + size (e.g. "Southern Comfort Jam Jar 12x750") ' refBrandID -> ProductGroupCode ' strBrand -> ProductGroupName ' intSize -> CustomAttributes\Size ' intPack -> CustomAttributes\Package ' dblPrice -> UnitPrice ' blnDataTransfer -> Active (Repsly; Superbowl blnActive is not used for this field) ' strBottleBarcode -> EAN ' strCaseBarcode -> CustomAttributes\CBarcode ' strGiftboxBarcode -> CustomAttributes\GBarcode ' strTags -> Tag (pipe-delimited in DB, comma-separated for API/Excel) ' Type, Type2, Short Code, PriceSeg -> Ignore Dim sql As String = "SELECT " & "p.strProdCode AS Code, " & "ISNULL(p.strLongProdDesc, p.strProductDesc) + " & "CASE WHEN ISNULL(p.intPack, 0) > 0 OR ISNULL(p.intSize, 0) > 0 " & "THEN N' ' + CAST(ISNULL(p.intPack, 0) AS NVARCHAR(20)) + N'x' + CAST(ISNULL(p.intSize, 0) AS NVARCHAR(20)) " & "ELSE N'' END AS Name, " & "CAST(p.refBrandID AS VARCHAR) AS ProductGroupCode, " & "ISNULL(b.strBrand, '') AS ProductGroupName, " & "CAST(ISNULL(p.blnDataTransfer, 0) AS BIT) AS Active, " & "CAST(ISNULL(p.dblPrice, 0) AS DECIMAL(18,4)) AS UnitPrice, " & "ISNULL(p.strBottleBarcode, '') AS EAN, " & "CAST(ISNULL(p.intSize, 0) AS INT) AS Size, " & "CAST(ISNULL(p.intPack, 0) AS INT) AS Package, " & "ISNULL(p.strCaseBarcode, '') AS CBarcode, " & "ISNULL(p.strGiftboxBarcode, '') AS GBarcode, " & "REPLACE(LTRIM(RTRIM(ISNULL(p.strTags, ''))), '|', ', ') AS Tag, " & "q.QueueID, " & "q.strProdCode " & "FROM sysCronJobProducts q " & "INNER JOIN mstProduct p ON q.strProdCode = p.strProdCode " & "LEFT JOIN mstBrand b ON p.refBrandID = b.BrandID " & "WHERE q.Status IN ('Pending', 'Updated') " & "AND (p.blnDataTransfer = 1 OR (p.blnDataTransfer = 0 AND q.LastProcessed IS NOT NULL)) " & "ORDER BY q.QueueID" Return db.doQuery(sql) End Function ' ============================================= ' Generate XLSX File ' ============================================= Private Function GenerateXLSXFile(ds As DataSet, filePath As String) As Boolean Try WriteLog("info", Me.Name, "Generate XLSX", "Generating XLSX file: " & filePath) Dim excelExport As New ExcelExport(ds.Tables(0), filePath, Me, Me.Name) excelExport.Execute() If excelExport.blnError Then WriteLog("error", Me.Name, "Generate XLSX", "Failed: " & excelExport.Message) Return False Else WriteLog("info", Me.Name, "Generate XLSX", "Success: " & excelExport.intRecordsExported & " records exported") Return True End If Catch ex As Exception WriteLog("error", Me.Name, "Generate XLSX Exception", ex.Message & " :: " & ex.StackTrace) Return False End Try End Function ' ============================================= ' Push to Repsly API ' Processes products in batches of 200 (Repsly API limit) ' ============================================= Private Sub PushToRepslyAPI(ds As DataSet, apiClient As RepslyAPIClient) Try Dim totalProducts As Integer = ds.Tables(0).Rows.Count WriteLog("info", Me.Name, "Push to API", "Pushing " & totalProducts & " product(s) to Repsly API in batches of 200") ' Repsly API limit: 200 products per request Const BATCH_SIZE As Integer = 200 Dim batchNumber As Integer = 1 Dim totalBatches As Integer = CInt(Math.Ceiling(totalProducts / BATCH_SIZE)) Dim totalSuccess As Integer = 0 Dim totalFailed As Integer = 0 ' Process products in batches For batchStart As Integer = 0 To totalProducts - 1 Step BATCH_SIZE Dim batchEnd As Integer = Math.Min(batchStart + BATCH_SIZE - 1, totalProducts - 1) Dim batchSize As Integer = batchEnd - batchStart + 1 WriteLog("info", Me.Name, "Push to API", "Processing batch " & batchNumber & " of " & totalBatches & " (" & batchSize & " products)") ' Build products array for this batch Dim productsList As New List(Of Object) Dim batchRows As New List(Of DataRow) For i As Integer = batchStart To batchEnd Dim dr As DataRow = ds.Tables(0).Rows(i) batchRows.Add(dr) ' Build product dictionary Dim product As Dictionary(Of String, Object) = BuildProductDictionary(dr) productsList.Add(product) Next ' Create JSON payload for this batch Dim payload As New Dictionary(Of String, Object) payload("Products") = productsList Dim jsonData As String = JsonConvert.SerializeObject(payload) ' Call API for this batch Dim response As RepslyAPIResponse = apiClient.PostAsync("/import/productList", jsonData) ' Extract ImportJobID from response message Dim importJobID As String = "" If response.Code = 0 AndAlso response.Message.Contains("ID:") Then Dim idStart As Integer = response.Message.IndexOf("ID:") + 4 Dim idEnd As Integer = response.Message.IndexOf(" ", idStart) If idEnd = -1 Then idEnd = response.Message.Length importJobID = response.Message.Substring(idStart, idEnd - idStart).Trim() End If ' Update queue table for this batch If response.Code = 0 Then ' Success - mark as completed For Each dr As DataRow In batchRows UpdateQueueRecord(dr, "Completed", importJobID, BuildProductDictionary(dr)) Next totalSuccess += batchSize WriteLog("info", Me.Name, "Push to API", "Batch " & batchNumber & " completed successfully. ImportJobID: " & importJobID) Else ' Failed - mark as failed For Each dr As DataRow In batchRows UpdateQueueRecord(dr, "Failed", "", Nothing, response.Message) Next totalFailed += batchSize WriteLog("error", Me.Name, "Push to API", "Batch " & batchNumber & " failed: " & response.Message) End If batchNumber += 1 Next ' Summary log WriteLog("info", Me.Name, "Push to API", "Batch processing complete. Success: " & totalSuccess & ", Failed: " & totalFailed & " out of " & totalProducts & " total products") Catch ex As Exception WriteLog("error", Me.Name, "Push to API Exception", ex.Message & " :: " & ex.StackTrace) End Try End Sub ' ============================================= ' Build Product Dictionary from DataRow ' ============================================= Private Function BuildProductDictionary(dr As DataRow) As Dictionary(Of String, Object) Dim product As New Dictionary(Of String, Object) ' Required fields product("Code") = dr("Code").ToString() product("Name") = dr("Name").ToString() product("ProductGroupCode") = dr("ProductGroupCode").ToString() product("ProductGroupName") = dr("ProductGroupName").ToString() product("Active") = CBool(dr("Active")) product("UnitPrice") = CDec(dr("UnitPrice")) ' EAN (Bottle Barcode) If Not String.IsNullOrEmpty(dr("EAN").ToString()) Then product("EAN") = dr("EAN").ToString() End If ' CustomAttributes array (only include if at least one value exists) ' Format: [{"AttributeTitle": "Size", "Value": 750}, ...] Dim customAttrs As New List(Of Object) ' Size If Not IsDBNull(dr("Size")) AndAlso CInt(dr("Size")) > 0 Then Dim attr As New Dictionary(Of String, Object) attr("AttributeTitle") = "Size" attr("Value") = CInt(dr("Size")) customAttrs.Add(attr) End If ' Package If Not IsDBNull(dr("Package")) AndAlso CInt(dr("Package")) > 0 Then Dim attr As New Dictionary(Of String, Object) attr("AttributeTitle") = "Package" attr("Value") = CInt(dr("Package")) customAttrs.Add(attr) End If ' CBarcode (Case Barcode) If Not String.IsNullOrEmpty(dr("CBarcode").ToString()) Then Dim attr As New Dictionary(Of String, Object) attr("AttributeTitle") = "CBarcode" attr("Value") = dr("CBarcode").ToString() customAttrs.Add(attr) End If ' GBarcode (Giftbox Barcode) If Not String.IsNullOrEmpty(dr("GBarcode").ToString()) Then Dim attr As New Dictionary(Of String, Object) attr("AttributeTitle") = "GBarcode" attr("Value") = dr("GBarcode").ToString() customAttrs.Add(attr) End If ' Only add CustomAttributes if it has at least one value If customAttrs.Count > 0 Then product("CustomAttributes") = customAttrs End If ' Tag - mstProduct.strTags (pipe -> comma), same value used in Excel export Dim tagVal As String = If(IsDBNull(dr("Tag")), "", dr("Tag").ToString().Trim()) If tagVal.StartsWith(",") Then tagVal = tagVal.TrimStart(","c).Trim() If Not String.IsNullOrEmpty(tagVal) Then product("Tag") = tagVal End If Return product End Function ' ============================================= ' Update Queue Record ' ============================================= Private Sub UpdateQueueRecord(dr As DataRow, status As String, importJobID As String, Optional productDict As Dictionary(Of String, Object) = Nothing, Optional errorMessage As String = "") Try Dim productJson As String = "" If productDict IsNot Nothing Then productJson = JsonConvert.SerializeObject(productDict) End If Dim sqlUpdate As String = "" Dim lastSentBit As String = "" If status = "Completed" Then lastSentBit = If(CBool(dr("Active")), "1", "0") sqlUpdate = "UPDATE sysCronJobProducts " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " RequestPayload = '" & db.CleanString(productJson) & "', " & " blnLastSentDataTransfer = " & lastSentBit & ", " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() Else sqlUpdate = "UPDATE sysCronJobProducts " & "SET Status = 'Failed', " & " ErrorLog = '" & db.CleanString(errorMessage) & "', " & " LastUpdated = GETDATE(), " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() End If ' Try full Completed update (RequestPayload + blnLastSentDataTransfer); fall back if columns missing Try db.doQuery(sqlUpdate) Catch ex As Exception If status <> "Completed" Then Throw End If If ex.Message.Contains("blnLastSentDataTransfer") Then sqlUpdate = "UPDATE sysCronJobProducts " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " RequestPayload = '" & db.CleanString(productJson) & "', " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() Try db.doQuery(sqlUpdate) WriteLog("warning", Me.Name, "Push to API", "blnLastSentDataTransfer column not found; run script 15. Please run Database_Scripts/15_Add_LastSentDataTransfer_To_Product_Queue.sql") Catch ex2 As Exception If ex2.Message.Contains("Invalid column name 'RequestPayload'") Then sqlUpdate = "UPDATE sysCronJobProducts " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " ErrorLog = 'RequestPayload: ' + '" & db.CleanString(productJson) & "', " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() db.doQuery(sqlUpdate) WriteLog("warning", Me.Name, "Push to API", "RequestPayload column not found, stored in ErrorLog instead. Please run script 05_Add_RequestPayload_Column.sql") Else Throw End If End Try ElseIf ex.Message.Contains("Invalid column name 'RequestPayload'") Then sqlUpdate = "UPDATE sysCronJobProducts " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " ErrorLog = 'RequestPayload: ' + '" & db.CleanString(productJson) & "', " & " blnLastSentDataTransfer = " & lastSentBit & ", " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() Try db.doQuery(sqlUpdate) WriteLog("warning", Me.Name, "Push to API", "RequestPayload column not found, stored in ErrorLog instead. Please run script 05_Add_RequestPayload_Column.sql") Catch ex3 As Exception If ex3.Message.Contains("blnLastSentDataTransfer") Then sqlUpdate = "UPDATE sysCronJobProducts " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " ErrorLog = 'RequestPayload: ' + '" & db.CleanString(productJson) & "', " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() db.doQuery(sqlUpdate) WriteLog("warning", Me.Name, "Push to API", "RequestPayload stored in ErrorLog; blnLastSentDataTransfer missing — run script 15.") Else Throw End If End Try Else Throw End If End Try Catch ex As Exception WriteLog("error", Me.Name, "Update Queue Record Exception", "QueueID: " & dr("QueueID").ToString() & " - " & ex.Message) End Try End Sub End Class