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 void WillReturnStateAbbreviation()
{
string stateName = "District of Columbia";
string stateAbbreviation = NetSuiteHelper.GetStateByName(stateName);
Assert.Equal("DC", stateAbbreviation);
}
private static OrderToImport GetSalesOrderValues(string salesOrderId)
{
RestClient client = new RestClient(orderImporterSpecControllerUrl);
RestRequest request = BigCommerceHelper.CreateNewGetRequest();
NetSuiteHelper.SetNetSuiteTestParameters(request, salesOrderId);
IRestResponse response = client.Execute(request);
OrderToImport parsedResponse = JsonConvert.DeserializeObject <OrderToImport>(response.Content);
return(parsedResponse);
}
public static void SetShippingInformation(ShippingAddress shippingAddress, OrderToImport request)
{
request.ShippingFirstName = shippingAddress.first_name;
request.ShippingLastName = shippingAddress.last_name;
request.ShippingCompany = shippingAddress.company;
request.ShippingLine1 = shippingAddress.street_1;
request.ShippingLine2 = shippingAddress.street_2;
request.ShippingCity = shippingAddress.city;
request.ShippingState = NetSuiteHelper.GetStateByName(shippingAddress.state);
request.ShippingZip = shippingAddress.zip;
request.ShippingCountry = shippingAddress.country_iso2;
request.ShippingPhone = shippingAddress.phone;
request.ShippingMethodName = BigCommerceHelper.GetShippingMethodName(shippingAddress.shipping_method);
}
public static string ImportOrderToNetSuite(OrderToImport importOrderRequest)
{
var client = new RestClient(orderImporterRestletUrl);
var jsonRequest = JsonConvert.SerializeObject(importOrderRequest);
var request = NetSuiteHelper.CreateNewRestletRequest(jsonRequest, orderImporterRestletUrl, "OrderImporter");
var orderImporterRestletResponse = client.Execute(request);
var parsedNetSuiteResponse = JsonConvert.DeserializeObject <ImportOrderResponse>(orderImporterRestletResponse.Content);
Log.Information("Netsuite Order Importer response {@parsedNetSuiteResponse}", parsedNetSuiteResponse);
if (parsedNetSuiteResponse.error != null)
{
throw new Exception($"Error: {parsedNetSuiteResponse.error}");
}
return(parsedNetSuiteResponse.salesOrderRecordId);
}
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 void SetBillingInformation(Order order, OrderToImport request)
{
int googleParentAccountId = 18054032;
request.ParentAccountId = googleParentAccountId;
request.Email = order.billing_address.email;
request.PhoneNumber = order.billing_address.phone;
request.Department = b2bDepartmentId;
request.IPAddress = order.ip_address;
request.UserTypeId = generalContractor;
request.BillingFirstName = order.billing_address.first_name;
request.BillingLastName = order.billing_address.last_name;
request.BillingLine1 = order.billing_address.street_1;
request.BillingLine2 = order.billing_address.street_2;
request.BillingCity = order.billing_address.city;
request.BillingState = NetSuiteHelper.GetStateByName(order.billing_address.state);
request.BillingZip = order.billing_address.zip;
request.BillingCountry = order.billing_address.country_iso2;
request.Company = order.billing_address.company;
}
public void WillCreateNetSuiteRequest()
{
BigCommerceController.customerId = 2;
Order order = CreateFakeBigCommerceOrder();
ShippingAddress shippingAddress = CreateFakeShippingAddress();
OrderToImport netsuiteRequest = NetSuiteController.CreateNetSuiteRequest(order, shippingAddress);
Assert.Equal(order.billing_address.email, netsuiteRequest.Email);
Assert.Equal(order.billing_address.phone, netsuiteRequest.PhoneNumber);
Assert.Equal(NetSuiteController.b2bDepartmentId, netsuiteRequest.Department);
Assert.Equal(order.ip_address, netsuiteRequest.IPAddress);
Assert.Equal("BB1-866", netsuiteRequest.NestProId);
Assert.Equal($"NP{order.id}", netsuiteRequest.SiteOrderNumber);
Assert.Equal(order.payment_provider_id, netsuiteRequest.AltOrderNumber);
Assert.Equal(NetSuiteController.micrositeId, netsuiteRequest.Microsite);
Assert.Equal(NetSuiteController.registered, netsuiteRequest.CheckoutTypeId);
Assert.Equal(Convert.ToDouble(order.base_shipping_cost), netsuiteRequest.SH);
Assert.Equal(order.billing_address.first_name, netsuiteRequest.BillingFirstName);
Assert.Equal(order.billing_address.last_name, netsuiteRequest.BillingLastName);
Assert.Equal(order.billing_address.street_1, netsuiteRequest.BillingLine1);
Assert.Equal(order.billing_address.street_2, netsuiteRequest.BillingLine2);
Assert.Equal(order.billing_address.city, netsuiteRequest.BillingCity);
Assert.Equal(NetSuiteHelper.GetStateByName(order.billing_address.state), netsuiteRequest.BillingState);
Assert.Equal(order.billing_address.zip, netsuiteRequest.BillingZip);
Assert.Equal(order.billing_address.country_iso2, netsuiteRequest.BillingCountry);
Assert.Equal(NetSuiteController.generalContractor, netsuiteRequest.UserTypeId);
Assert.Equal(shippingAddress.first_name, netsuiteRequest.ShippingFirstName);
Assert.Equal(shippingAddress.last_name, netsuiteRequest.ShippingLastName);
Assert.Equal(shippingAddress.street_1, netsuiteRequest.ShippingLine1);
Assert.Equal(shippingAddress.street_2, netsuiteRequest.ShippingLine2);
Assert.Equal(shippingAddress.city, netsuiteRequest.ShippingCity);
Assert.Equal(NetSuiteHelper.GetStateByName(shippingAddress.state), netsuiteRequest.ShippingState);
Assert.Equal(shippingAddress.zip, netsuiteRequest.ShippingZip);
Assert.Equal(shippingAddress.country_iso2, netsuiteRequest.ShippingCountry);
Assert.Equal(BigCommerceHelper.GetShippingMethodName(shippingAddress.shipping_method), netsuiteRequest.ShippingMethodName);
}
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);
}
}
