private void GetActiveListing(MWSUserProfile profile)
{
List <DataTable> TableList = null;
ReportRequestHandler getReportHandler = new ReportRequestHandler(profile);
string sReportType = "_GET_MERCHANT_LISTINGS_DATA_";
string sPeriod = "_15_MINUTES_";
getReportHandler.ScheduleReportRequest(sReportType, sPeriod);
string sMechantID = profile.SSellerId;
DateTime startedDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, 0, 0);
DateTime endDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, 59, 59);
try
{
TableList = getReportHandler.GetScheduledReport(sReportType, startedDate, endDate);
foreach (DataTable InventoryTable in TableList)
{
if (InventoryTable != null & InventoryTable.Rows.Count > 0)
{
}
}
}
catch (Exception ex)
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetActiveListing() exception message:" + ex.Message);
}
}
public List <MWSUserProfile> GetAWSLoginProfile()
{
List <MWSUserProfile> profileList = new List <MWSUserProfile>();
SqlCommand cmd;
string query;
query = "SELECT * FROM [MWS].[dbo].[AWSLoginToken] order by MerchantID DESC";
cmd = new SqlCommand(query);
DataTable dTable = new DataTable();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = conn;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(dTable);
}
}
}
}
foreach (DataRow row in dTable.Rows)
{
string sAccessKeyID = row["AWSAccessKeyID"].ToString();
string sSecretKey = row["SecretKey"].ToString();
string sMerchantID = row["MerchantID"].ToString();
string sMarketplaceID = row["MarketplaceID"].ToString();
MWSUserProfile profile = new MWSUserProfile(sAccessKeyID, sSecretKey, sMarketplaceID, sMerchantID, "");
profileList.Add(profile);
}
return(profileList);
}
public workerThreadParameter(MWSUserProfile profile)
{
_profile = profile;
}
private void GetUpdatedInventoryData(MWSUserProfile profile)
{
List <DataTable> InventoryTableList = null;
ReportRequestHandler getReportHandler = new ReportRequestHandler(profile);
string sReportType = "_GET_FBA_MYI_ALL_INVENTORY_DATA_";
string sPeriod = "_15_MINUTES_";
getReportHandler.ScheduleReportRequest(sReportType, sPeriod);
string sMechantID = profile.SSellerId;
DateTime startedDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, 0, 0);
DateTime endDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, 59, 59);
try
{
InventoryTableList = getReportHandler.GetScheduledReport(sReportType, startedDate, endDate);
foreach (DataTable InventoryTable in InventoryTableList)
{
if (InventoryTable != null & InventoryTable.Rows.Count > 0)
{
SqlDataHandler fillDataHandler = new SqlDataHandler(_ConnectionString);
DataSet ds = new DataSet();
DataTable ProductAvailabilityTable = new DataTable();
ProductAvailabilityTable.Columns.Add("FNSKU", typeof(String));
ProductAvailabilityTable.Columns.Add("SellerSKU", typeof(String));
ProductAvailabilityTable.Columns.Add("ASIN", typeof(String));
ProductAvailabilityTable.Columns.Add("ProductName", typeof(String));
ProductAvailabilityTable.Columns.Add("Inbound", typeof(Int32));
ProductAvailabilityTable.Columns.Add("Fulfillable", typeof(Int32));
ProductAvailabilityTable.Columns.Add("Unfulfillable", typeof(Int32));
ProductAvailabilityTable.Columns.Add("Reserved", typeof(Int32));
ProductAvailabilityTable.Columns.Add("MerchantID", typeof(String));
foreach (DataRow row in InventoryTable.Rows)
{
DataRow ProductRow = ProductAvailabilityTable.NewRow();
ProductRow["FNSKU"] = row[1];
ProductRow["SellerSKU"] = row[0];
ProductRow["ASIN"] = row[2];
ProductRow["ProductName"] = row[3];
ProductRow["Inbound"] = Int32.Parse(row[16].ToString());
ProductRow["Fulfillable"] = Int32.Parse(row[10].ToString());
ProductRow["Unfulfillable"] = Int32.Parse(row[11].ToString());
ProductRow["Reserved"] = Int32.Parse(row[12].ToString());
ProductRow["MerchantID"] = sMechantID;
ProductAvailabilityTable.Rows.Add(ProductRow);
}
if (ProductAvailabilityTable.Rows.Count > 0)
{
ds.Tables.Add(ProductAvailabilityTable);
fillDataHandler.UpdateProductAvailabilityData("ProductAvailability", ds, profile.SSellerId);
}
fillDataHandler = null;
ProductAvailabilityTable = null;
ds = null;
}
}
}
catch (Exception ex)
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetUpdatedInventoryData() exception message:" + ex.Message);
}
}
private void GetUpdatedFulfilledShipmentsData(MWSUserProfile profile)
{
DataTable fulfilledShipmentsDataTable = null;
ReportRequestHandler getReportHandler = new ReportRequestHandler(profile);
string sReportType = "";
sReportType = "_GET_AMAZON_FULFILLED_SHIPMENTS_DATA_";
string sMechantID = profile.SSellerId;
DateTime reportStartDate = FULFILLED_SHIPMENTS_DATA_STARTDATE;
DateTime reportEndDate = FULFILLED_SHIPMENTS_DATA_ENDDATE;
try
{
SqlDataHandler fillDataHandler = new SqlDataHandler(_ConnectionString);
fulfilledShipmentsDataTable = getReportHandler.GenerateReport(sReportType, reportStartDate, reportEndDate);
if (null != fulfilledShipmentsDataTable)
{
DataTable fulfilledShipmentDataTableWithMerchantID = new DataTable();
foreach (DataColumn col in fulfilledShipmentsDataTable.Columns)
{
if (col.ColumnName.Contains("date"))
{
fulfilledShipmentDataTableWithMerchantID.Columns.Add(col.ColumnName, typeof(DateTime));
}
else
{
fulfilledShipmentDataTableWithMerchantID.Columns.Add(col.ColumnName, typeof(String));
}
}
fulfilledShipmentDataTableWithMerchantID.Columns.Add("MerchantID", typeof(String));
foreach (DataRow row in fulfilledShipmentsDataTable.Rows)
{
DataRow row_FulfilledShipmentData = fulfilledShipmentDataTableWithMerchantID.NewRow();
foreach (DataColumn col in fulfilledShipmentDataTableWithMerchantID.Columns)
{
if (col.ColumnName == "MerchantID")
{
row_FulfilledShipmentData[col.ColumnName] = sMechantID;
}
else if (col.ColumnName.Contains("date"))
{
DateTime tmpDateTime = AmazonTimeString2DateTime(row[col.ColumnName].ToString());
row_FulfilledShipmentData[col.ColumnName] = tmpDateTime;
}
else
{
row_FulfilledShipmentData[col.ColumnName] = row[col.ColumnName];
}
}
fulfilledShipmentDataTableWithMerchantID.Rows.Add(row_FulfilledShipmentData);
}
DataSet ds = new DataSet();
if (fulfilledShipmentDataTableWithMerchantID.Rows.Count > 0)
{
ds.Tables.Add(fulfilledShipmentDataTableWithMerchantID);
fillDataHandler.UpdateRawFulfilledShipmentsData(ds);
}
fillDataHandler = null;
fulfilledShipmentsDataTable = null;
ds = null;
}
}
catch (Exception ex)
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetUpdatedFulfilledShipmentsData() exception message:" + ex.Message);
}
Thread.Sleep(1000 * 60 * UPDATE_FULFILLED_SHIPMENTS_DATA_PERIOD);// wait XXX min
}
private void GetFBA_EstimatedFeeData(MWSUserProfile profile)
{
DataTable srcTable = null;
ReportRequestHandler getReportHandler = new ReportRequestHandler(profile);
string sReportType = "";
sReportType = "_GET_FBA_ESTIMATED_FBA_FEES_TXT_DATA_";
string sMechantID = profile.SSellerId;
DateTime reportEndDate = ESTIMATED_FBA_FEE_REPORT_ENDDATE;
DateTime reportStartDate = ESTIMATED_FBA_FEE_REPORT_STARTDATE;
try
{
SqlDataHandler fillDataHandler = new SqlDataHandler(_ConnectionString);
srcTable = getReportHandler.GenerateReport(sReportType, reportStartDate, reportEndDate);
if (null != srcTable)
{
DataTable tableWithMerchantID = new DataTable();
foreach (DataColumn col in srcTable.Columns)
{
tableWithMerchantID.Columns.Add(col.ColumnName, typeof(String));
}
tableWithMerchantID.Columns.Add("MerchantID", typeof(String));
tableWithMerchantID.Columns.Add("last_update_date", typeof(DateTime));
foreach (DataRow row in srcTable.Rows)
{
DataRow rowOrder_Data = tableWithMerchantID.NewRow();
foreach (DataColumn col in tableWithMerchantID.Columns)
{
if (col.ColumnName == "MerchantID")
{
rowOrder_Data[col.ColumnName] = sMechantID;
}
else if (col.ColumnName == "last_update_date")
{
rowOrder_Data[col.ColumnName] = DateTime.UtcNow;
}
else
{
rowOrder_Data[col.ColumnName] = row[col.ColumnName];
}
}
tableWithMerchantID.Rows.Add(rowOrder_Data);
}
DataSet ds = new DataSet();
if (tableWithMerchantID.Rows.Count > 0)
{
ds.Tables.Add(tableWithMerchantID);
fillDataHandler.UpdateRaw_FBA_EstimatedFee("Raw_FBA_Estimated_Fee", ds);
}
fillDataHandler = null;
srcTable = null;
ds = null;
}
else
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetFBA_EstimatedFeeData() no report data.");
}
}
catch (Exception ex)
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetFBA_EstimatedFeeData() exception message:" + ex.Message);
}
Thread.Sleep(1000 * 60 * UPDATE_ESTIMATED_FBA_FEE_PERIOD);
}
private List <string> GetTransactionData(MWSUserProfile profile)
{
string sReportType = "_GET_ALT_FLAT_FILE_PAYMENT_SETTLEMENT_DATA_";
string sMechantID = profile.SSellerId;
List <DataTable> srcTableList = null;
List <string> settlement_idList = new List <string>();
ReportRequestHandler getReportHandler = new ReportRequestHandler(profile);
DateTime reportEndDate = SETTLEMENT_PAYMENT_DATA_ENDDATE;
DateTime reportStartDate = SETTLEMENT_PAYMENT_DATA_ENDDATE.AddDays(-89);
try
{
srcTableList = getReportHandler.GetScheduledReport(sReportType, reportStartDate, reportEndDate);
SqlDataHandler fillDataHandler = new SqlDataHandler(_ConnectionString);
if (srcTableList.Count > 0)
{
DateTime defaultDateTime = new DateTime(1900, 1, 1);
foreach (DataTable srcTable in srcTableList)
{
if (null != srcTable && srcTable.Rows.Count > 0)
{
DataTable tableWithMerchantID = new DataTable();
foreach (DataColumn col in srcTable.Columns)
{
if (col.ColumnName.Contains("date"))
{
tableWithMerchantID.Columns.Add(col.ColumnName, typeof(DateTime));
}
else if (col.ColumnName == "total_amount" || col.ColumnName == "amount")
{
tableWithMerchantID.Columns.Add(col.ColumnName, typeof(float));
}
else if (col.ColumnName == "quantity_purchased")
{
tableWithMerchantID.Columns.Add(col.ColumnName, typeof(Int32));
}
else
{
tableWithMerchantID.Columns.Add(col.ColumnName, typeof(String));
}
}
tableWithMerchantID.Columns.Add("MerchantID", typeof(String));
tableWithMerchantID.Columns.Add("last_update_date", typeof(DateTime));
string sOrderID = "";
DateTime dtSettlementStartDate = defaultDateTime;
DateTime dtSettlementEndDate = defaultDateTime;
DateTime dtDepositDate = defaultDateTime;
DateTime dtPostedDate = defaultDateTime;
DateTime dtPostedDate_time = defaultDateTime;
foreach (DataRow row in srcTable.Rows)
{
DataRow rowSettlemnetData = tableWithMerchantID.NewRow();
sOrderID = row["order_id"].ToString();
rowSettlemnetData = tableWithMerchantID.NewRow();
foreach (DataColumn col in tableWithMerchantID.Columns)
{
if (col.ColumnName == "settlement_id")
{
if (!settlement_idList.Contains(row["settlement_id"].ToString()))
{
settlement_idList.Add(row["settlement_id"].ToString());
}
rowSettlemnetData[col.ColumnName] = row["settlement_id"].ToString();
}
else if (col.ColumnName == "posted_date_time")
{
if (!string.IsNullOrEmpty(row[col.ColumnName].ToString()))
{
string temp = row[col.ColumnName].ToString();
//2014-11-30 07:01:39
dtPostedDate_time = PSTTimeString2UTCDateTime(temp);
}
rowSettlemnetData[col.ColumnName] = dtPostedDate_time;
}
else
if (col.ColumnName == "settlement_start_date")
{
if (!string.IsNullOrEmpty(row[col.ColumnName].ToString()))
{
string temp = row[col.ColumnName].ToString();
//2014-11-30 07:01:39
dtSettlementStartDate = PSTTimeString2UTCDateTime(temp);
}
rowSettlemnetData[col.ColumnName] = dtSettlementStartDate;
}
else
if (col.ColumnName == "settlement_end_date")
{
if (!string.IsNullOrEmpty(row[col.ColumnName].ToString()))
{
string temp = row[col.ColumnName].ToString();
dtSettlementEndDate = PSTTimeString2UTCDateTime(temp);
}
rowSettlemnetData[col.ColumnName] = dtSettlementEndDate;
}
else
if (col.ColumnName == "deposit_date")
{
if (!string.IsNullOrEmpty(row[col.ColumnName].ToString()))
{
string temp = row[col.ColumnName].ToString();
dtDepositDate = PSTTimeString2UTCDateTime(temp);
}
rowSettlemnetData[col.ColumnName] = dtDepositDate;
}
else
if (col.ColumnName == "MerchantID")
{
rowSettlemnetData[col.ColumnName] = sMechantID;
}
else
if (col.ColumnName == "last_update_date")
{
rowSettlemnetData[col.ColumnName] = DateTime.UtcNow;
}
else
if (col.ColumnName == "posted_date")
{
if (!string.IsNullOrEmpty(row[col.ColumnName].ToString()))
{
string temp = row[col.ColumnName].ToString();
//2014-11-30 07:01:39
dtPostedDate = PSTTimeString2UTCDateTime(temp);
}
rowSettlemnetData[col.ColumnName] = dtPostedDate;
}
else
if (col.ColumnName == "total_amount" || col.ColumnName == "amount" || col.ColumnName == "quantity_purchased")
{
string temp = row[col.ColumnName].ToString();
if (string.IsNullOrEmpty(temp))
{
rowSettlemnetData[col.ColumnName] = 0;
}
else
{
rowSettlemnetData[col.ColumnName] = row[col.ColumnName];
}
}
else
{
rowSettlemnetData[col.ColumnName] = row[col.ColumnName];
}
}
tableWithMerchantID.Rows.Add(rowSettlemnetData);
}
DataSet ds = new DataSet();
if (tableWithMerchantID.Rows.Count > 0)
{
ds.Tables.Add(tableWithMerchantID);
fillDataHandler.UpdateRawSettlementPaymentData(ds);
}
ds = null;
}
else
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetTransactionData() no report data.");
}
}
}
fillDataHandler = null;
}
catch (Exception ex)
{
DebugLogHandler.DebugLogHandler.WriteLog(sLogPath, sClass, "GetTransactionData() exception message:" + ex.Message);
}
return(settlement_idList);
}
