' ============================================= ' Repsly Promotion Export Process ' Created: 2025-01-XX ' Purpose: Export promotions to Repsly API (using Schedule endpoint) ' - Query active promotions from dwfPromo ' - Check queue table for new/updated promotions ' - Map Rep Name to Rep Code ' - Generate XLSX file ' - Upload to FTP ' - Push to Repsly API (Schedule endpoint) ' - 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 RepslyPromotionExport_Process : Inherits Export_Process Private repNameToCode As Dictionary(Of String, String) ' Cache for Rep Name → Code mapping ' ============================================= ' Constructor ' ============================================= Sub New(ExportProcess As Export_Process) MyBase.New(ExportProcess.Name, ExportProcess.Filename, ExportProcess.Path, ExportProcess.SQL) Me.barProcess = ExportProcess.barProcess repNameToCode = New Dictionary(Of String, String)() End Sub ' ============================================= ' Override Run Method ' ============================================= Public Shadows Sub Run() '1. Log start '2. Load Rep Name → Code mapping '3. Initialize queue (add new/updated promotions) '4. Query promotions to process '5. Generate XLSX file '6. Upload to FTP '7. Push to Repsly API '8. Update queue table '9. Log complete Me.LogProcessStart() Try '2. Load Rep Name → Code mapping LoadRepMapping() '3. Initialize queue - add new/updated promotions InitializeQueue() '4. Query promotions to process Dim promotionsToProcess As DataSet = GetPromotionsToProcess() Me.intRecordsTotal = promotionsToProcess.Tables(0).Rows.Count If Me.intRecordsTotal = 0 Then WriteLog("info", Me.Name, "Process", "No promotions to process") Me.LogProcessCompleted() Return End If Me.setProgress() '5. Generate XLSX file Dim xlsxFile As String = My.Settings.dirExports & Me.Filename If Not GenerateXLSXFile(promotionsToProcess, xlsxFile) Then WriteLog("error", Me.Name, "Process", "Failed to generate XLSX file") Me.LogProcessCompleted() Return End If '6. 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 '7. Push to Repsly API Dim apiClient As New RepslyAPIClient(Me.Name) PushToRepslyAPI(promotionsToProcess, apiClient) Catch ex As Exception WriteLog("error", Me.Name, "Process Exception", ex.Message & " :: " & ex.StackTrace) Finally Me.LogProcessCompleted() End Try End Sub ' ============================================= ' Load Rep Name → Code Mapping ' ============================================= Private Sub LoadRepMapping() Try WriteLog("info", Me.Name, "Load Rep Mapping", "Loading Rep Name → Code mapping from Repsly...") Dim apiClient As New RepslyAPIClient(Me.Name) Dim responseBody As String = apiClient.GetAsync("/export/representatives") ' Parse JSON response Dim jsonResponse As Object = JsonConvert.DeserializeObject(responseBody) Dim representatives As Newtonsoft.Json.Linq.JArray = jsonResponse("Representatives") repNameToCode.Clear() For Each rep As Newtonsoft.Json.Linq.JObject In representatives Dim repName As String = rep("Name").ToString().Trim() Dim repCode As String = rep("Code").ToString().Trim() If Not String.IsNullOrEmpty(repName) AndAlso Not String.IsNullOrEmpty(repCode) Then repNameToCode(repName) = repCode End If Next WriteLog("info", Me.Name, "Load Rep Mapping", "Loaded " & repNameToCode.Count & " rep mappings") Catch ex As Exception WriteLog("error", Me.Name, "Load Rep Mapping Exception", ex.Message & " :: " & ex.StackTrace) ' Continue - will use Rep Name if code not found End Try End Sub ' ============================================= ' Initialize Queue - Add new/updated promotions ' ============================================= Private Sub InitializeQueue() Try WriteLog("info", Me.Name, "Initialize Queue", "Checking for new/updated promotions...") ' Add new promotions (active and not in queue) Dim sqlNew As String = "INSERT INTO sysCronJobPromotions (PromoID, refActivityID, Status, LastUpdated) " & "SELECT p.PromoID, p.refActivityID, 'Pending', GETDATE() " & "FROM dwfPromo p " & "INNER JOIN promoActivity a ON p.refActivityID = a.ActivityID " & "WHERE a.blnActive = 1 " & "AND p.PromoID NOT IN (SELECT PromoID FROM sysCronJobPromotions)" Dim xdb As New db xdb.doQuery(sqlNew) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " new promotion(s) added to queue") End If ' Mark updated promotions (dtStamp changed since LastProcessed) ' Note: 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 sysCronJobPromotions " & "SET Status = 'Updated', LastUpdated = GETDATE() " & "FROM sysCronJobPromotions q " & "INNER JOIN dwfPromo p ON q.PromoID = p.PromoID " & "INNER JOIN promoActivity a ON p.refActivityID = a.ActivityID " & "WHERE a.blnActive = 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) OR " & " (LEN(a.dtStamp) = 12 AND " & " ISNUMERIC(a.dtStamp) = 1 AND " & " CONVERT(datetime, " & " LEFT(a.dtStamp, 4) + '-' + " & " SUBSTRING(a.dtStamp, 5, 2) + '-' + " & " SUBSTRING(a.dtStamp, 7, 2) + ' ' + " & " SUBSTRING(a.dtStamp, 9, 2) + ':' + " & " SUBSTRING(a.dtStamp, 11, 2) + ':00', 120) > q.LastProcessed))" xdb.doQuery(sqlUpdated) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " promotion(s) marked as updated") End If ' Mark inactive promotions (blnActive changed to 0) Dim sqlInactive As String = "UPDATE sysCronJobPromotions " & "SET Status = 'Inactive', LastUpdated = GETDATE() " & "FROM sysCronJobPromotions q " & "INNER JOIN promoActivity a ON q.refActivityID = a.ActivityID " & "WHERE a.blnActive = 0 " & "AND q.Status IN ('Pending', 'Completed', 'Processing')" xdb.doQuery(sqlInactive) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " promotion(s) marked as inactive") End If ' Reset failed promotions for retry Dim sqlRetry As String = "UPDATE sysCronJobPromotions " & "SET Status = 'Pending', ErrorLog = NULL " & "WHERE Status = 'Failed' " & "AND PromoID IN (SELECT p.PromoID FROM dwfPromo p " & " INNER JOIN promoActivity a ON p.refActivityID = a.ActivityID " & " WHERE a.blnActive = 1)" xdb.doQuery(sqlRetry) If xdb.intRows > 0 Then WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " failed promotion(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 Promotions to Process ' ============================================= Private Function GetPromotionsToProcess() As DataSet ' Query promotions that need to be processed ' Maps to Schedule endpoint fields Dim sql As String = "SELECT " & "q.QueueID, " & "q.PromoID, " & "p.strCustomerNo AS AccountNo, " & "ISNULL(dc.strCustomerName, '') AS AccountName, " & "p.strRep AS Rep, " & "p.strDate AS Date, " & "p.strActivityName, " & "p.strActivityType, " & "ISNULL(b.strBrand, '') AS Brand, " & "p.strRegion AS Region, " & "p.strChannelPrimary AS Channel, " & "p.strSegments AS Segment, " & "p.strRepManager AS Manager, " & "p.strPeriod AS Period, " & "p.strDuration AS Duration, " & "ISNULL(p.strChannelSegmentL3, '') AS ChannelSegmentL3 " & "FROM sysCronJobPromotions q " & "INNER JOIN dwfPromo p ON q.PromoID = p.PromoID " & "INNER JOIN promoActivity a ON p.refActivityID = a.ActivityID " & "LEFT JOIN dwdCustomer dc ON p.strCustomerNo = dc.strCustomerNo " & "LEFT JOIN mstBrand b ON p.refBrandID = b.BrandID " & "WHERE q.Status IN ('Pending', 'Updated') " & "AND a.blnActive = 1 " & "ORDER BY q.QueueID" Return db.doQuery(sql) End Function ' ============================================= ' Format Date for Repsly (YYYY-MM-DD) ' ============================================= Private Function FormatDateForRepsly(dateStr As String) As String Try If String.IsNullOrEmpty(dateStr) Then Return "" ' Handle different date formats Dim dt As DateTime If DateTime.TryParse(dateStr, dt) Then Return dt.ToString("yyyy-MM-dd") ElseIf dateStr.Length = 8 AndAlso IsNumeric(dateStr) Then ' Format: YYYYMMDD Return dateStr.Substring(0, 4) & "-" & dateStr.Substring(4, 2) & "-" & dateStr.Substring(6, 2) ElseIf dateStr.Contains("/") Then ' Format: YYYY/MM/DD or DD/MM/YYYY Dim parts() As String = dateStr.Split("/"c) If parts.Length = 3 Then If parts(0).Length = 4 Then ' YYYY/MM/DD Return parts(0) & "-" & parts(1).PadLeft(2, "0"c) & "-" & parts(2).PadLeft(2, "0"c) Else ' DD/MM/YYYY Return parts(2) & "-" & parts(1).PadLeft(2, "0"c) & "-" & parts(0).PadLeft(2, "0"c) End If End If End If Return dateStr ' Return as-is if can't parse Catch ex As Exception WriteLog("warning", Me.Name, "Format Date", "Error formatting date: " & dateStr & " - " & ex.Message) Return dateStr End Try End Function ' ============================================= ' Build VisitNote from Promotion Data ' ============================================= Private Function BuildVisitNote(dr As DataRow) As String Dim note As New System.Text.StringBuilder() note.Append("Promotion: " & db.nz(dr("strActivityName"), "").ToString()) note.Append(" | Type: " & db.nz(dr("strActivityType"), "").ToString()) note.Append(" | Brand: " & db.nz(dr("Brand"), "").ToString()) note.Append(" | Region: " & db.nz(dr("Region"), "").ToString()) note.Append(" | Channel: " & db.nz(dr("Channel"), "").ToString()) note.Append(" | Segment: " & db.nz(dr("Segment"), "").ToString()) note.Append(" | Manager: " & db.nz(dr("Manager"), "").ToString()) note.Append(" | Account: " & db.nz(dr("AccountName"), "").ToString()) note.Append(" | Period: " & db.nz(dr("Period"), "").ToString()) note.Append(" | Duration: " & db.nz(dr("Duration"), "").ToString()) If Not String.IsNullOrEmpty(db.nz(dr("ChannelSegmentL3"), "").ToString()) Then note.Append(" | ChannelSegmentL3: " & db.nz(dr("ChannelSegmentL3"), "").ToString()) End If Return note.ToString() End Function ' ============================================= ' Get Rep Code from Rep Name ' ============================================= Private Function GetRepCode(repName As String) As String If String.IsNullOrEmpty(repName) Then Return "" ' Try exact match first If repNameToCode.ContainsKey(repName) Then Return repNameToCode(repName) End If ' Try case-insensitive match For Each kvp As KeyValuePair(Of String, String) In repNameToCode If String.Equals(kvp.Key, repName, StringComparison.OrdinalIgnoreCase) Then Return kvp.Value End If Next ' Not found - log warning and return name (API might accept it) WriteLog("warning", Me.Name, "Rep Code Mapping", "Rep code not found for: " & repName & " - using name as code") Return repName 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) ' Create DataTable with Schedule fields Dim dtSchedule As New DataTable() dtSchedule.Columns.Add("ScheduleCode", GetType(String)) dtSchedule.Columns.Add("ClientCode", GetType(String)) dtSchedule.Columns.Add("UserID", GetType(String)) dtSchedule.Columns.Add("ScheduledDate", GetType(String)) dtSchedule.Columns.Add("RepeatEveryWeeks", GetType(Integer)) dtSchedule.Columns.Add("DueDate", GetType(Boolean)) dtSchedule.Columns.Add("ScheduledTime", GetType(String)) dtSchedule.Columns.Add("ScheduledDuration", GetType(Integer)) dtSchedule.Columns.Add("VisitNote", GetType(String)) dtSchedule.Columns.Add("ExternalID", GetType(String)) dtSchedule.Columns.Add("Active", GetType(Boolean)) ' Convert promotion data to schedule format For Each dr As DataRow In ds.Tables(0).Rows Dim newRow As DataRow = dtSchedule.NewRow() newRow("ScheduleCode") = "PROMO-" & dr("PromoID").ToString() newRow("ClientCode") = db.nz(dr("AccountNo"), "").ToString() newRow("UserID") = GetRepCode(db.nz(dr("Rep"), "").ToString()) newRow("ScheduledDate") = FormatDateForRepsly(db.nz(dr("Date"), "").ToString()) newRow("RepeatEveryWeeks") = 0 ' Non-recurring newRow("DueDate") = False newRow("ScheduledTime") = "" ' Any time newRow("ScheduledDuration") = 60 ' Default 60 minutes newRow("VisitNote") = BuildVisitNote(dr) newRow("ExternalID") = dr("PromoID").ToString() newRow("Active") = True dtSchedule.Rows.Add(newRow) Next Dim excelExport As New ExcelExport(dtSchedule, 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 ' ============================================= Private Sub PushToRepslyAPI(ds As DataSet, apiClient As RepslyAPIClient) Try WriteLog("info", Me.Name, "Push to API", "Pushing " & ds.Tables(0).Rows.Count & " promotion(s) to Repsly API") ' Build schedules array for bulk import Dim schedulesList As New List(Of Object) For Each dr As DataRow In ds.Tables(0).Rows Dim schedule As New Dictionary(Of String, Object) schedule("ScheduleCode") = "PROMO-" & dr("PromoID").ToString() schedule("ClientCode") = db.nz(dr("AccountNo"), "").ToString() schedule("UserID") = GetRepCode(db.nz(dr("Rep"), "").ToString()) schedule("ScheduledDate") = FormatDateForRepsly(db.nz(dr("Date"), "").ToString()) schedule("RepeatEveryWeeks") = 0 schedule("DueDate") = False schedule("ScheduledTime") = "" schedule("ScheduledDuration") = 60 schedule("VisitNote") = BuildVisitNote(dr) schedule("ExternalID") = dr("PromoID").ToString() schedule("Active") = True schedulesList.Add(schedule) Next ' Create JSON payload for bulk import Dim payload As New Dictionary(Of String, Object) payload("Schedules") = schedulesList Dim jsonData As String = JsonConvert.SerializeObject(payload) ' Call API Dim response As RepslyAPIResponse = apiClient.PostAsync("/import/scheduleList", 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 If response.Code = 0 Then ' Success - mark as completed since API accepted the import ' Note: Repsly processes imports asynchronously, but if API returns success (Code = 0), ' the data was accepted and will be processed. We mark as completed here because: ' 1. The cronjob runs once daily and won't check status again until next run ' 2. If the API returns success, the import was accepted and will be processed ' 3. We can verify success by querying the Repsly schedule export API For Each dr As DataRow In ds.Tables(0).Rows ' Store individual schedule JSON for this queue item (for debugging) Dim scheduleJson As String = "" Try ' Find the schedule in the list that matches this queue item Dim promoID As String = dr("PromoID").ToString() For Each scheduleDict As Dictionary(Of String, Object) In schedulesList If scheduleDict("ExternalID").ToString() = promoID Then scheduleJson = JsonConvert.SerializeObject(scheduleDict) Exit For End If Next Catch ' If individual schedule extraction fails, use the full payload scheduleJson = jsonData End Try ' Check if RequestPayload column exists, if not, we'll store in ErrorLog as fallback Dim sqlUpdate As String = "UPDATE sysCronJobPromotions " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " RequestPayload = '" & db.CleanString(scheduleJson) & "', " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() ' Try to update with RequestPayload column, if it doesn't exist, catch and use ErrorLog Try db.doQuery(sqlUpdate) Catch ex As Exception ' Fallback: if RequestPayload column doesn't exist, use ErrorLog If ex.Message.Contains("Invalid column name 'RequestPayload'") Then sqlUpdate = "UPDATE sysCronJobPromotions " & "SET Status = 'Completed', " & " ImportJobID = '" & db.CleanString(importJobID) & "', " & " LastProcessed = GETDATE(), " & " LastUpdated = GETDATE(), " & " ErrorLog = 'RequestPayload: ' + '" & db.CleanString(scheduleJson) & "', " & " 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 ' Re-throw if it's a different error End If End Try Next WriteLog("info", Me.Name, "Push to API", "Success: Import job accepted with ID: " & importJobID & ". " & ds.Tables(0).Rows.Count & " promotion(s) marked as Completed.") Else ' Failed - mark as failed For Each dr As DataRow In ds.Tables(0).Rows Dim sqlUpdate As String = "UPDATE sysCronJobPromotions " & "SET Status = 'Failed', " & " ErrorLog = '" & db.CleanString(response.Message) & "', " & " dtLastEdit = GETDATE() " & "WHERE QueueID = " & dr("QueueID").ToString() db.doQuery(sqlUpdate) Next WriteLog("error", Me.Name, "Push to API", "Failed: " & response.Message) End If Catch ex As Exception WriteLog("error", Me.Name, "Push to API Exception", ex.Message & " :: " & ex.StackTrace) End Try End Sub End Class