public static List <string> GetTrackingNumbersByItemFulfillment(string itemFulfillmentId) { using (var conn = new SqlConnection(NetSuiteHelper.GetAthenaDBConnectionString(dbName))) { try { conn.Open(); string query = @" SELECT TRACKING_NUMBER FROM NetSuite.data.SHIPMENT_PACKAGES WHERE ITEM_FULFILLMENT_ID = @itemFulfillmentId AND TRACKING_NUMBER is not null"; List <string> trackingNumbers = conn.Query <string>(query, new { itemFulfillmentId }, commandTimeout: 500).ToList(); conn.Close(); return(trackingNumbers); } catch (Exception ex) { var errorMessage = "Error in GetTrackingNumbersByItemFulfillment."; Log.Error(errorMessage, ex); throw new Exception(errorMessage); } } }
public static IEnumerable <IGrouping <string, ItemFulfillmentLine> > GetItemFulfillmentsNotImported(string netsuiteSalesOrderId, List <string> importedItemFulfillmentIds) { var getItemFulfillmentsNotImportedRetryPolicy = Policy.Handle <SqlException>() .WaitAndRetry(4, _ => TimeSpan.FromSeconds(30), (ex, ts, count, context) => { string errorMessage = "Error in GetItemFulfillmentsNotImported"; Log.Warning(ex, $"{errorMessage} . retrying..."); if (count == 4) { Log.Error(ex, errorMessage); } }); return(getItemFulfillmentsNotImportedRetryPolicy.Execute(() => { using (var conn = new SqlConnection(NetSuiteHelper.GetAthenaDBConnectionString(dbName))) { conn.Open(); string query = @" SELECT itfil.TRANSACTION_ID ItemFulfillmentId , (itfilLine.ITEM_COUNT * -1) Quantity , itfilLine.KIT_ID KitId , item.SKU , itfil.ACTUAL_SHIPPING_CARRIER Carrier , CASE WHEN soLine.NEST_PRO_PERSONAL_ITEM = 'F' THEN 'false' ELSE 'true' end as 'IsPersonal' FROM [NetSuite].[data].[TRANSACTIONS] itfil JOIN [NetSuite].[data].[TRANSACTION_LINES] itfilLine ON itfil.TRANSACTION_ID = itfilLine.TRANSACTION_ID AND itfil.TRANSACTION_TYPE = 'Item Fulfillment' JOIN [NetSuite].[data].[TRANSACTION_LINKS] link ON itfil.TRANSACTION_ID = link.APPLIED_TRANSACTION_ID AND itfilLine.TRANSACTION_LINE_ID = link.APPLIED_TRANSACTION_LINE_ID JOIN [NetSuite].[data].[TRANSACTIONS] so ON so.TRANSACTION_ID = link.ORIGINAL_TRANSACTION_ID JOIN [NetSuite].[data].[TRANSACTION_LINES] soLine ON soLine.TRANSACTION_ID = so.TRANSACTION_ID AND so.TRANSACTION_TYPE = 'Sales Order' AND soLine.TRANSACTION_LINE_ID = link.ORIGINAL_TRANSACTION_LINE_ID JOIN [NetSuite].[data].[ITEMS] item ON item.ITEM_ID = itfilLine.ITEM_ID WHERE so.TRANSACTION_ID = @netsuiteSalesOrderId AND itfil.TRANSACTION_ID not in @importedItemFulfillmentIds"; List <ItemFulfillmentLine> itemFulfillments = conn.Query <ItemFulfillmentLine>( query, new { netsuiteSalesOrderId, importedItemFulfillmentIds }, commandTimeout: 500).ToList(); conn.Close(); var groupedItemFulfillments = itemFulfillments.GroupBy(itfil => itfil.ItemFulfillmentId); return groupedItemFulfillments; } })); }
public static int GetSumOfKitMembers(int kitId) { var retryPolicy = Policy.Handle <SqlException>() .WaitAndRetry(4, _ => TimeSpan.FromSeconds(30), (ex, ts, count, context) => { string errorMessage = "Error in GetKitMembers"; Log.Warning(ex, $"{errorMessage} . retrying..."); if (count == 4) { Log.Error(ex, errorMessage); } }); return(retryPolicy.Execute(() => { using (var conn = new SqlConnection(NetSuiteHelper.GetAthenaDBConnectionString(dbName))) { try { conn.Open(); string query = @" SELECT SUM(itgp.QUANTITY) SumOfKitItems FROM NetSuite.data.ITEMS it JOIN NetSuite.data.ITEM_GROUP itgp ON it.ITEM_ID = itgp.PARENT_ID WHERE it.ITEM_ID = @kitId GROUP BY itgp.PARENT_ID"; int sumOfKitItems = conn.QuerySingle <int>(query, new { kitId }, commandTimeout: 500); return sumOfKitItems; } catch (Exception ex) { var errorMessage = "Error in GetKitMembers."; Log.Error(errorMessage, ex); throw; } } })); }
public static string GetSKUOfParentKit(int?kitId) { var retryPolicy = Policy.Handle <SqlException>() .WaitAndRetry(4, _ => TimeSpan.FromSeconds(30), (ex, ts, count, context) => { string errorMessage = "Error in GetSKUOfParentKit"; Log.Warning(ex, $"{errorMessage} . retrying..."); if (count == 4) { Log.Error(ex, errorMessage); } }); return(retryPolicy.Execute(() => { using (var conn = new SqlConnection(NetSuiteHelper.GetAthenaDBConnectionString(dbName))) { try { conn.Open(); string query = @" SELECT SKU FROM NetSuite.data.ITEMS WHERE ITEM_ID = @kitId"; string kitSKU = conn.QuerySingle <string>(query, new { kitId }, commandTimeout: 500); return kitSKU; } catch (Exception ex) { Log.Error(ex, "Error in GetSKUOfParentKit."); throw; } } })); }
public static string GetNetSuiteItemIdBySku(string sku) { using (SqlConnection connection = new SqlConnection(NetSuiteHelper.GetAthenaDBConnectionString(dbName))) { try { connection.Open(); string query = $"SELECT [ITEM_ID] FROM [HMWallaceDATA].[dbo].[NETSUITEITEMS] WHERE [FULL_NAME] = '{sku}'"; netsuiteItemId = connection.QuerySingle <String>(query); connection.Close(); } catch (Exception ex) { var itemIdErrormessage = $"Error in GetNetSuiteItemIdBySku. Item SKU {sku} was not found. Error: {ex}"; Log.Error(itemIdErrormessage); throw new Exception(itemIdErrormessage); } return(netsuiteItemId); } }