Galileo-Reconciliation-(Missing-Posts)¶
Last transaction timestamp: 2022-08-07 15:43:59.000
-
Run the query in Snowflake. Check the last time we pushed transactions and update the timestamp filter in the query:
select DISTINCT T.* from "DW_MAJORITY"."DBT"."GalileoTransactions" AS galileo INNER JOIN (SELECT pot.* FROM "DW_STAGE_BANK"."S3"."GalileoAuthorizedTransactions" AS aut FULL OUTER JOIN "DW_STAGE_BANK"."S3"."GalileoPostedTransactions" AS pot ON aut."NetworkCode" = pot."NetworkCode" AND aut."AuthorizationCode" = pot."AuthorizationCode" FULL OUTER JOIN (SELECT mt.* FROM "DW4_PROD"."DBT"."Majority_FactTransaction" AS mt LEFT JOIN "DW4_PROD"."DBT"."Majority_DimLedger" AS ml ON ml."LedgerId" = mt."LedgerAccountId" AND ml."IsCurrent" = 1 WHERE ml."LedgerType" = 'Galileo'-- AND mt."TransactionTime" <= $rdfLastDateTime -- only transactions in RDF time range AND NOT (mt."Status" = 'PreAuth' AND SUBSTR(mt."AuthorizationCode", 0, 2) = 'TH') ) AS t ON t."AuthorizationCode" = CONCAT(SUBSTR(pot."TransactionType", 0, 2), '=', pot."AuthorizationCode") -- TransactionAuthCode == 'ActType=AuthCode' OR t."AuthorizationCode" = CONCAT(COALESCE(aut."NetworkCode", pot."NetworkCode"), '=', COALESCE(aut."AuthorizationCode", pot."AuthorizationCode")) -- TransactionAuthCode == 'NetCode=AuthCode' OR t."ExternalTransactionId" = CASE WHEN pot."NetworkCode" IS NULL THEN CONCAT(SUBSTR(pot."TransactionType", 0, 2), '=', pot."AuthorizationCode") ELSE NULL END -- ExternalTransactionId == 'ActType=AuthCode' like PMVL OR t."ExternalTransactionId" = CASE WHEN SUBSTR(pot."TransactionType", 0, 2) = 'PM' THEN CONCAT(SUBSTR(pot."TransactionType", 0, 2), '=', pot."AuthorizationCode") ELSE NULL END -- same as previous, but only PMs OR t."AuthorizationCode" = CASE WHEN COALESCE(aut."NetworkCode", pot."NetworkCode") IN ('I', 'E') THEN CONCAT('V=', COALESCE(aut."AuthorizationCode", pot."AuthorizationCode")) ELSE NULL END -- TransactionAuthCode == 'V=AuthCode' if network is I or E (Visa subnet bug) WHERE (aut."AuthorizationResponse" IS NULL OR aut."AuthorizationResponse" IN ('00', '10', '87')) -- only successful auths, except AVS check AND NOT (aut."S3FileName" IS NOT NULL AND pot."S3FileName" IS NULL AND t."TransactionId" IS NULL AND aut."TransactionAmount" = 0) -- not zero amount auth AND NOT (t."TransactionId" IS NOT NULL AND aut."S3FileName" IS NULL AND pot."S3FileName" IS NULL AND t."Status" = 'Cancelled') -- not cancelled transactions AND (t."TransactionId" IS NULL AND pot."S3FileName" IS NOT NULL) -- missing posted transactions ) AS T ON galileo."AuthorizationCode" = T."AuthorizationCode" WHERE galileo."MajorityTransactionId" IS NULL AND "RdfTransactionTime" > '2022-06-07T09:13:42.000' -
Save the query results in CSV and convert it to excaped JSON:
namespace Minority.Galileo.Tools.RdfToCurl { #region Namespace Imports using System; using System.ComponentModel; using System.ComponentModel.DataAnnotations; using System.Globalization; using System.IO; using System.Text; using CsvHelper; using Newtonsoft.Json; #endregion internal class Program { private static void Main(string[] args) { var filePath = @"C:\Users\roman\Downloads\2022-04-06 Missing Posts.csv"; using (var reader = new StreamReader(filePath)) { using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture)) { var records = csv.GetRecords<ReconcilePostedTransactionRequest>(); var stringBuilder = new StringBuilder(); foreach (var record in records) { if (record.MerchantCategoryCode == 0) { record.MerchantCategoryCode = null; } if (string.IsNullOrWhiteSpace(record.MerchantCountryCode) || string.Equals(record.MerchantCountryCode, "0", StringComparison.InvariantCultureIgnoreCase)) { record.MerchantCountryCode = null; } if (string.IsNullOrWhiteSpace(record.ExternalTransactionId)) record.ExternalTransactionId = null; if (string.IsNullOrWhiteSpace(record.CardIdentifier) || string.Equals(record.CardIdentifier, "0", StringComparison.InvariantCultureIgnoreCase)) record.CardIdentifier = null; if (string.IsNullOrWhiteSpace(record.MerchantDescription)) record.MerchantDescription = null; if (string.IsNullOrWhiteSpace(record.MerchantNumber)) record.MerchantNumber = null; if (string.IsNullOrWhiteSpace(record.NetworkCode)) record.NetworkCode = null; if (string.IsNullOrWhiteSpace(record.TransactionCurrencyCode)) record.TransactionCurrencyCode = null; if (string.IsNullOrWhiteSpace(record.TransactionType)) record.TransactionType = null; if (string.IsNullOrWhiteSpace(record.UserData1)) record.UserData1 = null; if (string.IsNullOrWhiteSpace(record.UserData2)) record.UserData2 = null; var json = JsonConvert.SerializeObject( record, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore, DefaultValueHandling = DefaultValueHandling.Ignore }); json = JsonConvert.ToString(json); json = json.Substring(1, json.Length - 2); // var curlString = $"k exec -it $podName -- curl -d '{json}' -H \"Content-Type: application/json\" -X POST http://localhost:32123/reconcile/posted"; stringBuilder.AppendLine(json); //stringBuilder.AppendLine(); Console.WriteLine(json); } File.WriteAllText("output.curl", stringBuilder.ToString()); } } } /// <summary> /// Reconcile Posted Transaction Request /// </summary> public sealed class ReconcilePostedTransactionRequest { /// <summary> /// Galileo Auth ID. Unique for a network and sub-network combination. Example: 13371854. /// </summary> [Required] [JsonProperty("AuthorizationCode")] public int AuthorizationCode { get; set; } /// <summary> /// Card Id /// </summary> [JsonProperty("CardIdentifier", NullValueHandling = NullValueHandling.Ignore)] public string CardIdentifier { get; set; } /// <summary> /// External Transaction Id /// </summary> [JsonProperty("ExternalTransactionId", NullValueHandling = NullValueHandling.Ignore)] public string ExternalTransactionId { get; set; } /// <summary> /// Galileo Account Id /// </summary> [JsonProperty("GalileoAccountId", NullValueHandling = NullValueHandling.Ignore)] public int GalileoAccountId { get; set; } /// <summary> /// Merchant Category Code /// </summary> [JsonProperty("MerchantCategoryCode", NullValueHandling = NullValueHandling.Ignore)] public int? MerchantCategoryCode { get; set; } /// <summary> /// The Merchant Country. 3 digit country code (ISO 3166-1). Nullable. Example: "840". /// </summary> [JsonProperty("MerchantCountryCode", NullValueHandling = NullValueHandling.Ignore)] public string MerchantCountryCode { get; set; } /// <summary> /// The Merchant Name & Location /// </summary> [JsonProperty("MerchantDescription", NullValueHandling = NullValueHandling.Ignore)] public string MerchantDescription { get; set; } /// <summary> /// Merchant Id /// </summary> [JsonProperty("MerchantNumber", NullValueHandling = NullValueHandling.Ignore)] public string MerchantNumber { get; set; } /// <summary> /// Network, e.g. VISA, MasterCard /// </summary> [JsonProperty("NetworkCode", NullValueHandling = NullValueHandling.Ignore)] public string NetworkCode { get; set; } /// <summary> /// Transaction Post Date /// </summary> [JsonProperty("PostDate")] public DateTime PostDate { get; set; } /// <summary> /// Galileo Payment Reference Number for the account. /// </summary> [Required] [JsonProperty("Prn")] public string Prn { get; set; } /// <summary> /// Product Id /// </summary> [JsonProperty("ProductId", NullValueHandling = NullValueHandling.Ignore)] public int ProductId { get; set; } /// <summary> /// Amount /// </summary> [JsonProperty("TransactionAmount", NullValueHandling = NullValueHandling.Ignore)] public decimal TransactionAmount { get; set; } /// <summary> /// Currency /// </summary> [JsonProperty("TransactionCurrencyCode", NullValueHandling = NullValueHandling.Ignore)] public string TransactionCurrencyCode { get; set; } /// <summary> /// Transaction time /// </summary> [Required] [JsonProperty("TransactionTime")] public DateTime TransactionTime { get; set; } /// <summary> /// Transaction type /// </summary> [Required] [StringLength(5, MinimumLength = 3)] [JsonProperty("TransactionType")] public string TransactionType { get; set; } /// <summary> /// Program Id? /// </summary> [JsonProperty("UniqueProgramId", NullValueHandling = NullValueHandling.Ignore)] public int UniqueProgramId { get; set; } /// <summary> /// User Data 1 /// </summary> [JsonProperty("UserData1", NullValueHandling = NullValueHandling.Ignore)] public string UserData1 { get; set; } /// <summary> /// User Data 2 /// </summary> [JsonProperty("UserData2", NullValueHandling = NullValueHandling.Ignore)] public string UserData2 { get; set; } } } } -
Connect VPN and run the following PowerShell script to curl internal endpoint in Galileo Service:
$podId = "minority-galileo-service-5596bc849-fmflv" $transactions = Get-Content "C:\Projects\cde-galileo\Minority\Minority.Galileo\Minority.Galileo.ToolsRdfToCurl\bin\Debug\netcoreapp2.2\output.curl" $i=0 for(;$i -lt $transactions.Count;$i++) { $transaction = $transactions[$i] $cmdText = "k exec -it $podId -- curl -d '$transaction' -H `"Content-Type: application/json`" -X POST http://localhost:32123/reconcile/posted" Write-Host $cmdText Invoke-Expression $cmdText }