public virtual void ImportPhilatelicProductsFromXlsx(Stream stream)
{
var CurrentCustomer = _authenticationService.GetAuthenticatedCustomer();
string logError = string.Format("Lot Import: Beginning Lot Import by User: {0}.", CurrentCustomer.Username);
_logger.InsertLog(LogLevel.Information, logError);
//Get all of the specification attributes so you can get ids for spec attribute and option id later
bool optionExists = false;
bool specExists = false;
int specMaterialType = 0;
int specMaterialTypeDisplayOrder = 0;
int specFormat = 0;
int specFormatDisplayOrder = 0;
int specFormFactor = 0;
int specFormFactorDisplayOrder = 0;
int specUsageState = 0;
int specUsageStateDisplayOrder = 0;
int specGrade = 0;
int specGradeDisplayOrder = 0;
int specSymbolSummary = 0;
int specSymbolSummaryDisplayOrder = 0;
// int saleErrorCnt = 0;
//bool junctionExists = false;
AUSaleLotRecord sl = null;
AUSaleRecord csale = null;
var specAttributes = _specificationAttributeRepo.Table.ToList();
foreach (SpecificationAttribute sa in specAttributes)
{
//Names are kept with space between words for customer display purposes - internal fields remove space
switch (sa.Name) {
case "Material Type":
specMaterialType = sa.Id;
specMaterialTypeDisplayOrder = sa.DisplayOrder;
break;
case "Format":
specFormat = sa.Id;
specFormatDisplayOrder = sa.DisplayOrder;
break;
case "Form Factor":
specFormFactor = sa.Id;
specFormFactorDisplayOrder = sa.DisplayOrder;
break;
case "Usage State":
specUsageState = sa.Id;
specUsageStateDisplayOrder = sa.DisplayOrder;
break;
case "Grade":
specGrade = sa.Id;
specGradeDisplayOrder = sa.DisplayOrder;
break;
case "Symbol Summary":
specSymbolSummary = sa.Id;
specSymbolSummaryDisplayOrder = sa.DisplayOrder;
break;
default:
logError = string.Format("Lot Import: A product specification attribute was found that is not accounted for in the import: {0}/Id:{1}.", sa.Name, sa.Id);
_logger.InsertLog(LogLevel.Warning, logError);
break;
}
}
// ok, we can run the real code of the sample now
using (var xlPackage = new ExcelPackage(stream))
{
// get the first worksheet in the workbook
var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
if (worksheet == null)
throw new NopException("No Philatelic worksheet found");
int totalerrors = 0;
int recordsaddchanged = 0;
int recordsignored = 0;
//the columns
var properties = new[]
{
"ProductTypeId",
"ParentGroupedProductId",
"VisibleIndividually",
"Name",
"ShortDescription",
"FullDescription",
"VendorId",
"ProductTemplateId",
"ShowOnHomePage",
"MetaKeywords",
"MetaDescription",
"MetaTitle",
"SeName",
"AllowCustomerReviews",
"Published",
"SKU",
"ManufacturerPartNumber",
"Gtin",
"IsGiftCard",
"GiftCardTypeId",
"RequireOtherProducts",
"RequiredProductIds",
"AutomaticallyAddRequiredProducts",
"IsDownload",
"DownloadId",
"UnlimitedDownloads",
"MaxNumberOfDownloads",
"DownloadActivationTypeId",
"HasSampleDownload",
"SampleDownloadId",
"HasUserAgreement",
"UserAgreementText",
"IsRecurring",
"RecurringCycleLength",
"RecurringCyclePeriodId",
"RecurringTotalCycles",
"IsRental",
"RentalPriceLength",
"RentalPricePeriodId",
"IsShipEnabled",
"IsFreeShipping",
"ShipSeparately",
"AdditionalShippingCharge",
"DeliveryDateId",
"IsTaxExempt",
"TaxCategoryId",
"IsTelecommunicationsOrBroadcastingOrElectronicServices",
"ManageInventoryMethodId",
"UseMultipleWarehouses",
"WarehouseId",
"StockQuantity",
"DisplayStockAvailability",
"DisplayStockQuantity",
"MinStockQuantity",
"LowStockActivityId",
"NotifyAdminForQuantityBelow",
"BackorderModeId",
"AllowBackInStockSubscriptions",
"OrderMinimumQuantity",
"OrderMaximumQuantity",
"AllowedQuantities",
"AllowAddingOnlyExistingAttributeCombinations",
"DisableBuyButton",
"DisableWishlistButton",
"AvailableForPreOrder",
"PreOrderAvailabilityStartDateTimeUtc",
"CallForPrice",
"Price",
"OldPrice",
"ProductCost",
"SpecialPrice",
"SpecialPriceStartDateTimeUtc",
"SpecialPriceEndDateTimeUtc",
"CustomerEntersPrice",
"MinimumCustomerEnteredPrice",
"MaximumCustomerEnteredPrice",
"Weight",
"Length",
"Width",
"Height",
"CreatedOnUtc",
"CategoryIds",
"ManufacturerIds",
"Picture1",
"Picture2",
"Picture3",
"SaleNbr", //NJM: BEGIN OF PHILATELIC PORTION
"LotNbr",
"DisplayOrder",
"ConsignmentId",
"MaterialType",
"Country1",
"StateProvince1",
"Country2",
"StateProvince2",
"Country3",
"StateProvince3",
"Format",
"FormFactor",
"UsageState",
"Grade",
"ReserveAmt",
"EstimatedLowBidAmt",
"EstimatedHighBidAmt",
"EstimatedRealizationAmt",
"OpeningBidAmt",
"SymbolSummary",
"ReferenceId1",
"ReferenceNbr1",
"ReferenceValue1",
"ReferenceId2",
"ReferenceNbr2",
"ReferenceValue2",
"ReferenceId3",
"ReferenceNbr3",
"ReferenceValue3"
};
//_logger.InsertLog(LogLevel.Information, "Finished Import mapping");
int iRow = 2;
while (true)
{
totalerrors = 0; //reset initial edit-error count
bool allColumnsAreEmpty = true;
for (var i = 1; i <= properties.Length; i++)
if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString()))
{
allColumnsAreEmpty = false;
break;
}
if (allColumnsAreEmpty)
{
logError = string.Format("Lot Import: Ignoring empty row#{0}.", iRow);
_logger.InsertLog(LogLevel.Information, logError);
break;
}
//_logger.InsertLog(LogLevel.Information, "Import load data record:" + iRow.ToString());
int productTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductTypeId")].Value);
int parentGroupedProductId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ParentGroupedProductId")].Value);
bool visibleIndividually = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "VisibleIndividually")].Value);
string name = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Name")].Value);
string shortDescription = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ShortDescription")].Value);
string fullDescription = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "FullDescription")].Value);
int vendorId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "VendorId")].Value);
int productTemplateId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductTemplateId")].Value);
bool showOnHomePage = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ShowOnHomePage")].Value);
string metaKeywords = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MetaKeywords")].Value);
string metaDescription = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MetaDescription")].Value);
string metaTitle = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MetaTitle")].Value);
string seName = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SeName")].Value);
bool allowCustomerReviews = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowCustomerReviews")].Value);
bool published = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "Published")].Value);
string sku = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SKU")].Value);
string manufacturerPartNumber = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerPartNumber")].Value);
string gtin = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Gtin")].Value);
bool isGiftCard = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsGiftCard")].Value);
int giftCardTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "GiftCardTypeId")].Value);
bool requireOtherProducts = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "RequireOtherProducts")].Value);
string requiredProductIds = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "RequiredProductIds")].Value);
bool automaticallyAddRequiredProducts = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AutomaticallyAddRequiredProducts")].Value);
bool isDownload = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsDownload")].Value);
int downloadId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DownloadId")].Value);
bool unlimitedDownloads = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "UnlimitedDownloads")].Value);
int maxNumberOfDownloads = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MaxNumberOfDownloads")].Value);
int downloadActivationTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DownloadActivationTypeId")].Value);
bool hasSampleDownload = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "HasSampleDownload")].Value);
int sampleDownloadId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "SampleDownloadId")].Value);
bool hasUserAgreement = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "HasUserAgreement")].Value);
string userAgreementText = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "UserAgreementText")].Value);
bool isRecurring = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsRecurring")].Value);
int recurringCycleLength = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringCycleLength")].Value);
int recurringCyclePeriodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringCyclePeriodId")].Value);
int recurringTotalCycles = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringTotalCycles")].Value);
bool isRental = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsRental")].Value);
int rentalPriceLength = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RentalPriceLength")].Value);
int rentalPricePeriodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RentalPricePeriodId")].Value);
bool isShipEnabled = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsShipEnabled")].Value);
bool isFreeShipping = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsFreeShipping")].Value);
bool shipSeparately = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ShipSeparately")].Value);
decimal additionalShippingCharge = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "AdditionalShippingCharge")].Value);
int deliveryDateId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DeliveryDateId")].Value);
bool isTaxExempt = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsTaxExempt")].Value);
int taxCategoryId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "TaxCategoryId")].Value);
bool isTelecommunicationsOrBroadcastingOrElectronicServices = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsTelecommunicationsOrBroadcastingOrElectronicServices")].Value);
int manageInventoryMethodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ManageInventoryMethodId")].Value);
bool useMultipleWarehouses = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "UseMultipleWarehouses")].Value);
int warehouseId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "WarehouseId")].Value);
int stockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "StockQuantity")].Value);
bool displayStockAvailability = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayStockAvailability")].Value);
bool displayStockQuantity = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayStockQuantity")].Value);
int minStockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MinStockQuantity")].Value);
int lowStockActivityId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "LowStockActivityId")].Value);
int notifyAdminForQuantityBelow = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "NotifyAdminForQuantityBelow")].Value);
int backorderModeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "BackorderModeId")].Value);
bool allowBackInStockSubscriptions = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowBackInStockSubscriptions")].Value);
int orderMinimumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMinimumQuantity")].Value);
int orderMaximumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMaximumQuantity")].Value);
string allowedQuantities = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowedQuantities")].Value);
bool allowAddingOnlyExistingAttributeCombinations = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowAddingOnlyExistingAttributeCombinations")].Value);
bool disableBuyButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableBuyButton")].Value);
bool disableWishlistButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableWishlistButton")].Value);
bool availableForPreOrder = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AvailableForPreOrder")].Value);
DateTime? preOrderAvailabilityStartDateTimeUtc = null;
var preOrderAvailabilityStartDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "PreOrderAvailabilityStartDateTimeUtc")].Value;
if (preOrderAvailabilityStartDateTimeUtcExcel != null)
preOrderAvailabilityStartDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(preOrderAvailabilityStartDateTimeUtcExcel));
bool callForPrice = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "CallForPrice")].Value);
decimal price = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Price")].Value);
decimal oldPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "OldPrice")].Value);
decimal productCost = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductCost")].Value);
decimal? specialPrice = null;
var specialPriceExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPrice")].Value;
if (specialPriceExcel != null)
specialPrice = Convert.ToDecimal(specialPriceExcel);
DateTime? specialPriceStartDateTimeUtc = null;
var specialPriceStartDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceStartDateTimeUtc")].Value;
if (specialPriceStartDateTimeUtcExcel != null)
specialPriceStartDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceStartDateTimeUtcExcel));
DateTime? specialPriceEndDateTimeUtc = null;
var specialPriceEndDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceEndDateTimeUtc")].Value;
if (specialPriceEndDateTimeUtcExcel != null)
specialPriceEndDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceEndDateTimeUtcExcel));
bool customerEntersPrice = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "CustomerEntersPrice")].Value);
decimal minimumCustomerEnteredPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "MinimumCustomerEnteredPrice")].Value);
decimal maximumCustomerEnteredPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "MaximumCustomerEnteredPrice")].Value);
decimal weight = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Weight")].Value);
decimal length = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Length")].Value);
decimal width = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Width")].Value);
decimal height = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Height")].Value);
DateTime createdOnUtc = DateTime.FromOADate(Convert.ToDouble(worksheet.Cells[iRow, GetColumnIndex(properties, "CreatedOnUtc")].Value));
string categoryIds = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "CategoryIds")].Value);
string manufacturerIds = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerIds")].Value);
string picture1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Picture1")].Value);
string picture2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Picture2")].Value);
string picture3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Picture3")].Value);
//NJM: BEGIN OF PHILATELIC PORTION
string SaleNbr = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SaleNbr")].Value);
int LotNbr = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "LotNbr")].Value); //will only be processed if Sale present and found
int DisplayOrder = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayOrder")].Value); //will only be processed if Sale present and found
int ConsignmentId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ConsignmentId")].Value); //id field AUConsignmentID
string MaterialType = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MaterialType")].Value);
string Country1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Country1")].Value);
string StateProvince1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "StateProvince1")].Value);
string Country2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Country2")].Value);
string StateProvince2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "StateProvince2")].Value);
string Country3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Country3")].Value);
string StateProvince3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "StateProvince3")].Value);
string Format = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Format")].Value);
string FormFactor = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "FormFactor")].Value);
string UsageState = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "UsageState")].Value);
string Grade = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Grade")].Value);
decimal ReserveAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReserveAmt")].Value);
decimal EstimatedLowBidAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "EstimatedLowBidAmt")].Value);
decimal EstimatedHighBidAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "EstimatedHighBidAmt")].Value);
decimal EstimatedRealizationAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "EstimatedRealizationAmt")].Value);
decimal OpeningBidAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "OpeningBidAmt")].Value);
string SymbolSummary = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SymbolSummary")].Value);
int ReferenceId1 = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceId1")].Value); //id field AUConsignmentID
string ReferenceNbr1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceNbr1")].Value);
decimal ReferenceValue1 = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceValue1")].Value);
int ReferenceId2 = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceId2")].Value); //id field AUConsignmentID
string ReferenceNbr2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceNbr2")].Value);
decimal ReferenceValue2 = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceValue2")].Value);
int ReferenceId3 = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceId3")].Value); //id field AUConsignmentID
string ReferenceNbr3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceNbr3")].Value);
decimal ReferenceValue3 = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceValue3")].Value);
#region Initial AUConsignor Edits
//It's ok if sale is not provided, but if it is provided it must already exist in the system and can't create a 1-lot-2-active-sale situation
sl = null; //init existing matching junction from previous record
csale = null;
var currTime = System.DateTime.UtcNow;
if (!String.IsNullOrEmpty(SaleNbr))
{
//ensure that the AUSale exists before you store any product/lot
csale = _consignorService.GetSaleByNbr(SaleNbr);
if (csale == null)
{
logError = string.Format("Lot Import: Ignoring row because Sale can not be found row# {0}/ Sale# {1}. Please store the sale in the system and re-import the row", iRow, SaleNbr);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
else
{
if (csale.SaleIsAwarded)
{
logError = string.Format("Lot Import: This lot row#{0} can not be moved to a sale id/nbr: {1}/{2} that is awarded already.", iRow, csale.AUSaleID, csale.AUSaleNbr);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else
{
var l = _consignorService.GetLotBySku(sku);
if (l != null)
{
if (l.IsLotSold) //import changes can't be made to sold lots
{
logError = string.Format("Lot Import: This lot row#{0} is already sold in sale number {1}. Import changes can not be made to sold lots - row ignored", iRow, l.LotAwardSaleNbr);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else
{
// get the one ausalelot record that is not history:
var s = l.AUSaleRecords.FirstOrDefault(x => !x.IsHistory);
if (s != null)
{
if (s.AUSaleID != csale.AUSaleID) //trying to import to a different sale
{
//see if moving FROM an active or future sale and the lot has bids - No good
//if (s.SaleIsActive && csale.SaleIsActive) //imported lot can not be in 2 active sales. TODO: take sale is active out of junction
if ((!l.IsLotSold && s.SaleStartDateTime < currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0) ||
(!l.IsLotSold && s.SaleStartDateTime > currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0)
)
{
logError = string.Format("Lot Import: This lot row#{0} already exists in active or future sale id {1} and has bids. A lot can not be moved if it has active bids. You should remove the bids in saleId {1} manually and then re-import to move this lot to the new sale id {2}.", iRow, s.AUSaleID, csale.AUSaleID);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else if (csale.SaleIsAwarded)
{
logError = string.Format("Lot Import: This lot row#{0} can not be moved to a sale id/nbr: {1}/{2} that is awarded already.", iRow, csale.AUSaleID, csale.AUSaleNbr);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else
{
sl = s; //capture the existing junction for this sale-lot (already exists in another sale but ok to move). It will be used when sale-lot junction to be created/updated
}
}
else //(s.AUSaleID == csale.AUSaleID - lot already exixts in this sale so save the junction record
{
sl = s; //capture the existing junction for this sale-lot (already exists in import sale). It will be used when sale-lot junction to be created/updated
}
} //leave sl null if existing "non-history" salelot junction not found - indicates lot exists but is not in any sale. Since you've already found the import sale you're good to go
}
} //lot specified in import does not exist and sale specified is not awarded yet, so ok to import. sl is null because not associated yet
}
}
}
else //salenbr not specified in import - see if need to remove and if ok
{
var l = _consignorService.GetLotBySku(sku);
if (l != null)
{
if (l.IsLotSold) //import changes can't be made to sold lots
{
logError = string.Format("Lot Import: This lot row#{0} is already sold in sale number {1}. Import changes can not be made to sold lots - row ignored", iRow, l.LotAwardSaleNbr);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else
{
//need to check if lot is associated to an awarded sale or active/future sale with bids before you remove it
var s = l.AUSaleRecords.FirstOrDefault(x => !x.IsHistory);
if (s != null)
{
if ((!l.IsLotSold && s.SaleStartDateTime < currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0) ||
(!l.IsLotSold && s.SaleStartDateTime > currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0)
)
{
logError = string.Format("Lot Import: This lot row#{0} already exists in active or future sale id {1} and has bids. A lot can not be removed from sale as specified in the import. You should remove the bids in saleId {1} manually and then re-import to remove this lot.", iRow, s.AUSaleID);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else if (s.SaleIsAwarded)
{
logError = string.Format("Lot Import: This lot row#{0} can not be removed from a sale id/nbr: {1} that is awarded already.", iRow, s.AUSaleID, s.AUSaleRecord.AUSaleNbr);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
else
{
sl = s; //capture the existing junction for this sale-lot (already exists in another sale but ok to move). It will be used when sale-lot junction to be created/updated
}
} //ale number not provided in import lot exists but not associated to a sale so let it fly
}
} //sale number not provided in import, and lot does not exist yet so let it fly
}
//Consignment must be present and exist in the system
if (ConsignmentId != 0)
{
//ensure that the AUSale exists
var cconsignment = _consignorService.GetConsignmentById(ConsignmentId);
if (cconsignment == null)
{
logError = string.Format("Lot Import: Ignoring row because Consignment ID can not be found in system row# {0}/ ConsignmentId# {1}. Please store the consignment in the system and re-import the row", iRow, ConsignmentId);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
else
{
logError = string.Format("Lot Import: Consignment required - ignoring row because Consignment ID was not provided for row# {0}. Please record the consignment id and re-import the row", iRow);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
//Country1 must be present and exist in the system
if (!String.IsNullOrEmpty(Country1))
{
var ccountry1 = _consignorService.GetCountryByName(Country1.Trim());
if (ccountry1 == null)
{
logError = string.Format("Lot Import: Ignoring row because Country1 can not be found in system for row# {0}/ Country1: {1}. Please record the country and re-import the row", iRow, Country1);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
else //It's ok if StateProvince1 is not provided, but if it is provided it must already exist in the system for the Country1
if (!String.IsNullOrEmpty(StateProvince1))
{
//ensure that the StateProvince1 exists if it's given
var cstateprovince1 = _consignorService.GetStateProvinceByName(StateProvince1.Trim(), ccountry1.Id);
if (cstateprovince1 == null)
{
logError = string.Format("Lot Import: Ignoring row because StateProvince1 not found in system for row# {0}/ Country1: {1} / StateProvince1 {2}. Please store the StateProvince1 in the system and record on row and re-import", iRow, Country1, StateProvince1);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
}
else
{
logError = string.Format("Lot Import: Country1 required - Ignoring row because Country1 not present on row# {0}. Please store the country and record on row and re-import", iRow);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
if (!String.IsNullOrEmpty(StateProvince1))
{
logError = string.Format("Lot Import: Ignoring row because StateProvince1 provided for row with no Country1 row# {0}/ StateProvince1: {1}. Please provide Country1 and StateProvince1 on row and re-import", iRow, StateProvince1);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
}
//break;
}
//Its ok if Country2 not present but if it is it must exist in the system
//if (!String.IsNullOrEmpty(Country2.Trim()))
if (!String.IsNullOrEmpty(Country2))
{
var ccountry2 = _consignorService.GetCountryByName(Country2.Trim());
if (ccountry2 == null)
{
logError = string.Format("Lot Import: Ignoring row because Country2 can not be found in system for row# {0}/ Country2: {1}. Please record the country in the system and re-import the row", iRow, Country2);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
else //It's ok if StateProvince2 is not provided, but if it is provided it must already exist in the system for Country2
if (!String.IsNullOrEmpty(StateProvince2))
{
//ensure that the StateProvince1 exists if it's given
var cstateprovince2 = _consignorService.GetStateProvinceByName(StateProvince2.Trim(), ccountry2.Id);
if (cstateprovince2 == null)
{
logError = string.Format("Lot Import: Ignoring row because StateProvince2 not found in system for row# {0}/ Country2: {1} / StateProvince2 {2}. Please store the StateProvince3 in the system and record on row and re-import", iRow, Country2, StateProvince2);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
}
else
{
if (!String.IsNullOrEmpty(StateProvince2))
{
logError = string.Format("Lot Import: Ignoring row because StateProvince2 provided for row with no Country2 row# {0}/ StateProvince2: {1}. Please provide Country2 and StateProvince2 on row and re-import", iRow, StateProvince2);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
//Its ok if Country3 not present but if it is it must exist in the system
if (!String.IsNullOrEmpty(Country3))
{
var ccountry3 = _consignorService.GetCountryByName(Country2.Trim());
if (ccountry3 == null)
{
logError = string.Format("Lot Import: Ignoring row because Country3 can not be found in system for row# {0}/ Country3: {1}. Please record the country in the system and re-import the row", iRow, Country3);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
else //It's ok if StateProvince2 is not provided, but if it is provided it must already exist in the system for Country2
if (!String.IsNullOrEmpty(StateProvince3))
{
//ensure that the StateProvince1 exists if it's given
var cstateprovince3 = _consignorService.GetStateProvinceByName(StateProvince3.Trim(), ccountry3.Id);
if (cstateprovince3 == null)
{
logError = string.Format("Lot Import: Ignoring row because StateProvince3 not found in system for row# {0}/ Country3: {1} / StateProvince3 {2}. Please store the StateProvince3 in the system and record on row and re-import", iRow, Country3, StateProvince3);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
}
else
{
if (!String.IsNullOrEmpty(StateProvince3))
{
logError = string.Format("Lot Import: Ignoring row because StateProvince3 provided for row with no Country3 row# {0}/ StateProvince3: {1}. Please provide Country3 and StateProvince3 on row and re-import", iRow, StateProvince3);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
//_logger.InsertLog(LogLevel.Information, "Import finished loading data record:" + iRow.ToString() + "now get product by sku");
var product = new Product();
var lot = new AULotRecord();
if (String.IsNullOrEmpty(sku))
{
logError = string.Format("Lot Import: Ignoring row because Product SKU not provided for row#{0}. Please update with the correct SKU and re-import", iRow);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
else
{
//TODO: SKU is mandatory - document in spreadsheet
product = _productService.GetProductBySku(sku);
lot = _consignorService.GetLotBySku(sku);
//Log as error when product already exists but lot doesn't and vica versa. This indicates one somehow got deleted when the other didn't. This is also logged in audit
//if (product != null && lot == null) //don't do this check because it may be a brend new product
//{
// logError = string.Format("Lot Import: Ignoring row because Product SKU was found but Lot SKU was not for row#{0}/SKU:{1}. Please notify the System Admin to research", iRow, sku);
// _logger.InsertLog(LogLevel.Error, logError);
// totalerrors++;
// //break;
//}
//else if (product == null && lot != null)
if (product == null && lot != null)
{
logError = string.Format("Lot Import: Ignoring row because Lot SKU was found but Product SKU was not for row#{0}/SKU:{1}. Please notify the System Admin to research", iRow, sku);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
else if (product != null && lot == null)
{
logError = string.Format("Lot Import: Ignoring row because Lot SKU was not found but Product SKU was found for row#{0}/SKU:{1}. Please notify the System Admin to research", iRow, sku);
_logger.InsertLog(LogLevel.Error, logError);
totalerrors++;
//break;
}
}
//TODO: If Error consignment id provided in file for a combined lot or a split lot
//if any errors from above edit-controls, stop this row and go to next. Count this record as ignored.
if (totalerrors > 0)
{
recordsignored++;
//next product
iRow++;
continue; //skip record and go to next iteration
}
#endregion
bool newProduct = false;
if (product == null)
{
product = new Product();
lot = new AULotRecord(); //NJM
newProduct = true;
}
product.ProductTypeId = productTypeId;
product.ParentGroupedProductId = parentGroupedProductId;
product.VisibleIndividually = visibleIndividually;
product.Name = name;
product.ShortDescription = shortDescription;
product.FullDescription = fullDescription;
product.VendorId = vendorId;
product.ProductTemplateId = productTemplateId;
product.ShowOnHomePage = showOnHomePage;
product.MetaKeywords = metaKeywords;
product.MetaDescription = metaDescription;
product.MetaTitle = metaTitle;
product.AllowCustomerReviews = allowCustomerReviews;
product.Sku = sku;
product.ManufacturerPartNumber = manufacturerPartNumber;
product.Gtin = gtin;
product.IsGiftCard = isGiftCard;
product.GiftCardTypeId = giftCardTypeId;
product.RequireOtherProducts = requireOtherProducts;
product.RequiredProductIds = requiredProductIds;
product.AutomaticallyAddRequiredProducts = automaticallyAddRequiredProducts;
product.IsDownload = isDownload;
product.DownloadId = downloadId;
product.UnlimitedDownloads = unlimitedDownloads;
product.MaxNumberOfDownloads = maxNumberOfDownloads;
product.DownloadActivationTypeId = downloadActivationTypeId;
product.HasSampleDownload = hasSampleDownload;
product.SampleDownloadId = sampleDownloadId;
product.HasUserAgreement = hasUserAgreement;
product.UserAgreementText = userAgreementText;
product.IsRecurring = isRecurring;
product.RecurringCycleLength = recurringCycleLength;
product.RecurringCyclePeriodId = recurringCyclePeriodId;
product.RecurringTotalCycles = recurringTotalCycles;
product.IsRental = isRental;
product.RentalPriceLength = rentalPriceLength;
product.RentalPricePeriodId = rentalPricePeriodId;
product.IsShipEnabled = isShipEnabled;
product.IsFreeShipping = isFreeShipping;
product.ShipSeparately = shipSeparately;
product.AdditionalShippingCharge = additionalShippingCharge;
product.DeliveryDateId = deliveryDateId;
product.IsTaxExempt = isTaxExempt;
product.TaxCategoryId = taxCategoryId;
product.IsTelecommunicationsOrBroadcastingOrElectronicServices = isTelecommunicationsOrBroadcastingOrElectronicServices;
product.ManageInventoryMethodId = manageInventoryMethodId;
product.UseMultipleWarehouses = useMultipleWarehouses;
product.WarehouseId = warehouseId;
product.StockQuantity = stockQuantity;
product.DisplayStockAvailability = displayStockAvailability;
product.DisplayStockQuantity = displayStockQuantity;
product.MinStockQuantity = minStockQuantity;
product.LowStockActivityId = lowStockActivityId;
product.NotifyAdminForQuantityBelow = notifyAdminForQuantityBelow;
product.BackorderModeId = backorderModeId;
product.AllowBackInStockSubscriptions = allowBackInStockSubscriptions;
product.OrderMinimumQuantity = orderMinimumQuantity;
product.OrderMaximumQuantity = orderMaximumQuantity;
product.AllowedQuantities = allowedQuantities;
product.AllowAddingOnlyExistingAttributeCombinations = allowAddingOnlyExistingAttributeCombinations;
product.DisableBuyButton = disableBuyButton;
product.DisableWishlistButton = disableWishlistButton;
product.AvailableForPreOrder = availableForPreOrder;
product.PreOrderAvailabilityStartDateTimeUtc = preOrderAvailabilityStartDateTimeUtc;
product.CallForPrice = callForPrice;
product.Price = price;
product.OldPrice = oldPrice;
product.ProductCost = productCost;
product.SpecialPrice = specialPrice;
product.SpecialPriceStartDateTimeUtc = specialPriceStartDateTimeUtc;
product.SpecialPriceEndDateTimeUtc = specialPriceEndDateTimeUtc;
product.CustomerEntersPrice = customerEntersPrice;
product.MinimumCustomerEnteredPrice = minimumCustomerEnteredPrice;
product.MaximumCustomerEnteredPrice = maximumCustomerEnteredPrice;
product.Weight = weight;
product.Length = length;
product.Width = width;
product.Height = height;
product.Published = published;
product.CreatedOnUtc = createdOnUtc;
product.UpdatedOnUtc = DateTime.UtcNow;
//_logger.InsertLog(LogLevel.Information, "Import finished loading product info:" + iRow.ToString() + "now load lot info");
//NJM: start philatelic shit
lot.Sku = sku; //this is sequence nbr
lot.EstimatedLowBidAmt = EstimatedLowBidAmt;
lot.EstimatedHighBidAmt = EstimatedHighBidAmt;
lot.EstimatedRealizationAmt = EstimatedRealizationAmt;
lot.OpeningBidAmt = OpeningBidAmt;
lot.ReserveAmt = ReserveAmt;
//TODO: Need to make this transactional
//TODO - need additionalShippingCharge try catch here that lets the rest of the import worksheet and hasSampleDownload a good message - also need to undo whatever done. Need ability to rerun import for failed records
if (newProduct)
{
lot.CreatedByUsername = CurrentCustomer.Username;
lot.UpdatedByUsername = CurrentCustomer.Username;
lot.CreatedOnDateTime = System.DateTime.UtcNow;
lot.UpdatedOnDateTime = lot.CreatedOnDateTime;
lot.AUProductID = product.Id; //very important to set the lot product id to match the product!!!
lot.IsLotSold = false;
//keep as little time between operations as possible
// _logger.InsertLog(LogLevel.Information, "Inserting product:" + iRow.ToString());
_productService.InsertProduct(product);
// _logger.InsertLog(LogLevel.Information, "Inserting lot:" + iRow.ToString());
lot.AUProductID = product.Id;
//TODO: Make AULotRecord a view model so not constantly stuffing consignmentid
lot.ConsignmentID = ConsignmentId;
lot.LastBidAmt = 0; //do this to ensure nulls don't sneak in as used to determine if bids entered
lot.BidPostDateTime = new DateTime(1970, 01, 01);
_consignorService.InsertLot(lot);
}
else
{
lot.UpdatedByUsername = CurrentCustomer.Username;
lot.UpdatedOnDateTime = System.DateTime.UtcNow;
//keep as little time between operations as possible
_productService.UpdateProduct(product);
//TODO: Make AULotRecord a view model so not constantly stuffing consignmentid
lot.ConsignmentID = ConsignmentId;
_consignorService.UpdateLot(lot);
}
//search engine name
_urlRecordService.SaveSlug(product, product.ValidateSeName(seName, product.Name, true), 0);
//category mappings
if (!String.IsNullOrEmpty(categoryIds))
{
foreach (var id in categoryIds.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())))
{
if (product.ProductCategories.FirstOrDefault(x => x.CategoryId == id) == null)
{
//ensure that category exists
var category = _categoryService.GetCategoryById(id);
if (category != null)
{
var productCategory = new ProductCategory
{
ProductId = product.Id,
CategoryId = category.Id,
IsFeaturedProduct = false,
DisplayOrder = 1
};
_categoryService.InsertProductCategory(productCategory);
}
else
{
logError = string.Format("Lot Import: Category ID not found but storing lot anyway row#{0}/Id:{1}.", iRow, id);
_logger.InsertLog(LogLevel.Information, logError);
}
}
}
}
//manufacturer mappings
if (!String.IsNullOrEmpty(manufacturerIds))
{
foreach (var id in manufacturerIds.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())))
{
if (product.ProductManufacturers.FirstOrDefault(x => x.ManufacturerId == id) == null)
{
//ensure that manufacturer exists
var manufacturer = _manufacturerService.GetManufacturerById(id);
if (manufacturer != null)
{
var productManufacturer = new ProductManufacturer
{
ProductId = product.Id,
ManufacturerId = manufacturer.Id,
IsFeaturedProduct = false,
DisplayOrder = 1
};
_manufacturerService.InsertProductManufacturer(productManufacturer);
}
else
{
logError = string.Format("Lot Import: Manufacturer ID not found but storing lot anyway row#{0}/Id:{1}.", iRow, id);
_logger.InsertLog(LogLevel.Information, logError);
}
}
}
}
//pictures
foreach (var picturePath in new[] { picture1, picture2, picture3 })
{
if (String.IsNullOrEmpty(picturePath))
continue;
var mimeType = GetMimeTypeFromFilePath(picturePath);
var newPictureBinary = File.ReadAllBytes(picturePath);
var pictureAlreadyExists = false;
if (!newProduct)
{
//compare with existing product pictures
var existingPictures = _pictureService.GetPicturesByProductId(product.Id);
foreach (var existingPicture in existingPictures)
{
var existingBinary = _pictureService.LoadPictureBinary(existingPicture);
//picture binary after validation (like in database)
var validatedPictureBinary = _pictureService.ValidatePicture(newPictureBinary, mimeType);
if (existingBinary.SequenceEqual(validatedPictureBinary))
{
//the same picture content
pictureAlreadyExists = true;
break;
}
}
}
//OLD SIGNATURE FOR _pictureService.GetPictureSeName
//if (!pictureAlreadyExists)
//{
// product.ProductPictures.Add(new ProductPicture
// {
// Picture = _pictureService.InsertPicture(newPictureBinary, mimeType, _pictureService.GetPictureSeName(name), true),
// DisplayOrder = 1,
// });
// _productService.UpdateProduct(product);
//}
if (!pictureAlreadyExists)
{
product.ProductPictures.Add(new ProductPicture
{
Picture = _pictureService.InsertPicture(newPictureBinary, mimeType, _pictureService.GetPictureSeName(name)),
DisplayOrder = 1,
});
_productService.UpdateProduct(product);
}
}
//update "HasTierPrices" and "HasDiscountsApplied" properties
//NJM for performance - I think thiswill always default to 0 because there's nothing in the excel file
_productService.UpdateHasTierPricesProperty(product);
_productService.UpdateHasDiscountsApplied(product);
//*********************************************************************************************************************************
//NJM: AUConsignor begin philatelic lot relationships
//********************************************************************************************************************************
//TODO: REFACTOR SALE-LOT THIS SO CAN CALL FROMMANAGESALESPOPUP AS WELL
//For sale it looks like it ignores it if it can't find it. Need to implement auditlog of all imports
if (!String.IsNullOrEmpty(SaleNbr))
{
//ensure that the AUSale exists
//AUSaleLotRecord s1 = null;
//var sale = _consignorService.GetSaleByNbr(SaleNbr);
//TODO: PUT FOREIGN KEY CONSTRAINTS ON AUSALELOT
if (csale != null) //sale specified on import already retrived in overall edits above. If it is null you have other problems
{
if (sl == null) //no sale junction exists for this lot as determined in overall edits above. Just create a new salelot junction.
{
var saleLot = new AUSaleLotRecord
{
AUSaleID = csale.AUSaleID,
AULotID = lot.AULotID,
LotNbr = LotNbr, //load up LotNbr from the spreadsheet
Sku = sku,
SaleStartDateTime = csale.SaleStartDateTime,
SaleEndDateTime = csale.SaleEndDateTime,
IsHistory = false,
SaleIsAwarded = csale.SaleIsAwarded,
SaleIsActive = csale.SaleIsActive, //pull this indicator from the Sale record - don't load from spreadsheet
DisplayOrder = DisplayOrder //load up DisplayOrder from the spreadsheet
};
_consignorService.InsertSaleLot(saleLot);
}
else //Sale exists and the junction AUSaleLot record does as well. If juntion is to import sale (already exixts in sale), just update it with fresh sale info
//If junction is to a different sale you'll need to move to the new (import sale)
{
if (SaleNbr == sl.AUSaleRecord.AUSaleNbr) //import sale number is the same as the currently assigned sale number
{
sl.AUSaleID = csale.AUSaleID;
sl.AULotID = lot.AULotID;
sl.LotNbr = LotNbr;
sl.Sku = sku;
sl.SaleStartDateTime = csale.SaleStartDateTime;
sl.SaleEndDateTime = csale.SaleEndDateTime;
//sl.IsHistory = false; //don't touch this as whatever was there should remain there. Note: sl is only populated with the non-history junction for the lot
sl.SaleIsAwarded = csale.SaleIsAwarded;
sl.SaleIsActive = csale.SaleIsActive;
sl.DisplayOrder = DisplayOrder;
_consignorService.UpdateSaleLot(sl);
}
else //existing "non-history" salelot is to different sale than import sale - "MOVE" the sale lot
{
AUSaleLotRecord newSaleLot = new AUSaleLotRecord()
{
AUSaleID = csale.AUSaleID,
LotNbr = LotNbr, //load up LotNbr from the spreadsheet
SaleIsActive = csale.SaleIsActive, //pull this indicator from the Sale record - don't load from spreadsheet
DisplayOrder = DisplayOrder,
SaleStartDateTime = csale.SaleStartDateTime,
SaleEndDateTime = csale.SaleEndDateTime,
SaleIsAwarded = csale.SaleIsAwarded,
IsHistory = false,
AUProductId = lot.AUProductID,
Sku = lot.Sku
};
_consignorService.MoveSaleLot(sl, newSaleLot);
}
}
} //SaleNbr not found (csale == null) -- this will have been logged as an error in top edits
}
else //import sale number is null - need to see if need to remove lot
{
if (sl != null) //no import sale but lot exists in a sale, so remove it from the sale
{
_consignorService.DeleteSaleLotByLotId(lot.AULotID, sl.AUSaleID);
}
}
//%%%%%%%%%%%%%%need logic if in sale and removing sale
//NOTE: The above edits will stop process for this record if Consignment ID not found in system
//Consignment is required for an original lot. An original lot can only belong to one consignment. A combined lot can not belong to a consignment
//directly - it only belongs to consignments through it's relationship to the orginal lot.
if (ConsignmentId != 0)
{
//ensure that the AUSale exists
var consignment = _consignorService.GetConsignmentById(ConsignmentId);
if (consignment != null)
{
//TODO: mutiple imports that change the consignment between imports for a lot will not get rid of the old relationship
//if (lot.AUSaleRecords.FirstOrDefault(x => x.AUSaleID == sale.AUSaleID) == null) //%%%%Value cn not be null?????
//see if this lot was already associated to another consignment
var lc = lot.AUConsignmentRecords.FirstOrDefault(x => x.AUConsignmentID != ConsignmentId);
//if it was associated to a different consignment, you'll change the consignment assignment but warn them as well
if (lc != null)
{
logError = string.Format("Lot Import: This lot belonged to a different consignment than in the import - row#{0} belonged to consignmentId:{1} but was changed to consignmentId {2}. You should review the lot fees (if any) or else fees will be inherited from the new consignment.", iRow, lc.AUConsignmentID, consignment.AUConsignmentID);
_logger.InsertLog(LogLevel.Warning, logError);
int origConsignmentId = lc.AUConsignmentID;
lc.AUConsignmentID = consignment.Id;
_consignorService.UpdateConsignmentLot(lc, origConsignmentId);
}
else //lot was not already assigned to a different consignment
{
var cl = consignment.AULotRecords.FirstOrDefault(x => x.AULotID == lot.AULotID);
if (cl == null) //Consignment exists but the junction AUConsignmentLot record doesn't
{
var consignmentLot = new AUConsignmentLotRecord
{
AUConsignmentID = consignment.AUConsignmentID,
AULotID = lot.AULotID,
AULotRecord = lot,
AUConsignmentRecord = consignment,
};
_consignorService.InsertConsignmentLot(consignmentLot);
}
}
} //TODO: add abort here (and at Sale) because edit already determined consignment exists so something bad happened
}
//Junction Lot to Country1 (AUCountryLot)
if (!String.IsNullOrEmpty(Country1))
{
//ensure that the country1 exists, because you will add its relationship to lot as well as a specification attribute
var country = _consignorService.GetCountryByName(Country1.Trim());
if (country != null)
{
var query = from cl in _countrylotRepo.Table
where cl.AULotID == lot.AULotID && cl.AUCountryID == country.Id
select cl;
var clot = query.FirstOrDefault();
if (clot == null) //Country exists exists but the junction AUCountryLot record doesn't
{
var countryLot = new AUCountryLotRecord
{
AULotID = lot.AULotID,
AUCountryID = country.Id,
};
_consignorService.InsertCountryLot(countryLot);
}
}
//Junction Lot to StateProvince1 (AUStateProvinceLot) - can only happen if you have Country1
if (!String.IsNullOrEmpty(StateProvince1))
{
//ensure that the AUSale exists
var stateprovince = _consignorService.GetStateProvinceByName(StateProvince1.Trim(), country.Id);
if (stateprovince != null)
{
var query = from spl in _stateprovincelotRepo.Table
where spl.AULotID == lot.AULotID && spl.AUStateProvinceID == stateprovince.Id
select spl;
var splot = query.FirstOrDefault();
if (splot == null) //StateProvince exists exists but the junction AUStateProvinceLot record doesn't
{
var stateprovinceLot = new AUStateProvinceLotRecord
{
AULotID = lot.AULotID,
AUStateProvinceID = stateprovince.Id,
};
_consignorService.InsertStateProvinceLot(stateprovinceLot);
}
}
}
} //end Country1
//Junction Lot to Country2 (AUCountryLot)
if (!String.IsNullOrEmpty(Country2))
{
//ensure that the AUSale exists
var country = _consignorService.GetCountryByName(Country2.Trim());
if (country != null)
{
var query = from cl in _countrylotRepo.Table
where cl.AULotID == lot.AULotID && cl.AUCountryID == country.Id
select cl;
var clot = query.FirstOrDefault();
if (clot == null) //Country exists exists but the junction AUCountryLot record doesn't
{
var countryLot = new AUCountryLotRecord
{
AULotID = lot.AULotID,
AUCountryID = country.Id,
};
_consignorService.InsertCountryLot(countryLot);
}
}
//Junction Lot to StateProvince (AUStateProvinceLot) - can only happen if you have Country1
if (!String.IsNullOrEmpty(StateProvince2))
{
//ensure that the AUSale exists
var stateprovince = _consignorService.GetStateProvinceByName(StateProvince2.Trim(), country.Id);
if (stateprovince != null)
{
var query = from spl in _stateprovincelotRepo.Table
where spl.AULotID == lot.AULotID && spl.AUStateProvinceID == stateprovince.Id
select spl;
var splot = query.FirstOrDefault();
if (splot == null) //StateProvince exists exists but the junction AUStateProvinceLot record doesn't
{
var stateprovinceLot = new AUStateProvinceLotRecord
{
AULotID = lot.AULotID,
AUStateProvinceID = stateprovince.Id,
};
_consignorService.InsertStateProvinceLot(stateprovinceLot);
}
}
}
} //end Country2
//Junction Lot to Country3 (AUCountryLot)
if (!String.IsNullOrEmpty(Country3))
{
//ensure that the AUSale exists
var country = _consignorService.GetCountryByName(Country3.Trim());
if (country != null)
{
var query = from cl in _countrylotRepo.Table
where cl.AULotID == lot.AULotID && cl.AUCountryID == country.Id
select cl;
var clot = query.FirstOrDefault();
if (clot == null) //Country exists exists but the junction AUCountryLot record doesn't
{
var countryLot = new AUCountryLotRecord
{
AULotID = lot.AULotID,
AUCountryID = country.Id,
};
_consignorService.InsertCountryLot(countryLot);
}
}
//Junction Lot to StateProvince1 (AUStateProvinceLot) - can only happen if you have Country1
if (!String.IsNullOrEmpty(StateProvince3))
{
//ensure that the AUSale exists
var stateprovince = _consignorService.GetStateProvinceByName(StateProvince3.Trim(), country.Id);
if (stateprovince != null)
{
var query = from spl in _stateprovincelotRepo.Table
where spl.AULotID == lot.AULotID && spl.AUStateProvinceID == stateprovince.Id
select spl;
var splot = query.FirstOrDefault();
if (splot == null) //StateProvince exists exists but the junction AUStateProvinceLot record doesn't
{
var stateprovinceLot = new AUStateProvinceLotRecord
{
AULotID = lot.AULotID,
AUStateProvinceID = stateprovince.Id,
};
_consignorService.InsertStateProvinceLot(stateprovinceLot);
}
}
}
} //end Country3
//Start of product specification attributes
//specAttributes
//int specFormat = 0;
//int specFormFactor = 0;
//int specUsageState = 0;
//int specGrade = 0;
//SymbolSummary
//TODO: For all spec attributes inquire for AllowFiltering and ShowOnProductPage (use same method as display order? but need to investigate where these are stored)
if ((!String.IsNullOrEmpty(MaterialType)) || (!String.IsNullOrEmpty(Format)) || (!String.IsNullOrEmpty(FormFactor)) || (!String.IsNullOrEmpty(UsageState)) || (!String.IsNullOrEmpty(Grade)) || (!String.IsNullOrEmpty(SymbolSummary)))
{
var productSpecs = _specificationAttributeService.GetProductSpecificationAttributes(product.Id); //only get the produc specs one time if any of the spec attributes is filled in
//FormFactor
if (!String.IsNullOrEmpty(MaterialType))
{
optionExists = false;
specExists = false;
if (productSpecs.Count() > 0)
{
foreach (ProductSpecificationAttribute ps in productSpecs)
{
if (ps.SpecificationAttributeOption.SpecificationAttributeId == specMaterialType)
{
specExists = true;
if (ps.SpecificationAttributeOption.Name == MaterialType) //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
{
optionExists = true;
break;
}
}
}
}
if (!optionExists)
{
var specOption = GetSpecOptionByName(specMaterialType, MaterialType); //get the option id so can create products spec attribute below
if (specOption.Name != null)
{
var psa = new ProductSpecificationAttribute
{
//AttributeTypeId = attributeTypeId,
AttributeTypeId = (int)SpecificationAttributeType.Option,
SpecificationAttributeOptionId = specOption.Id,
ProductId = product.Id,
CustomValue = null,
AllowFiltering = true, //can do this since it's an "Option" attribute type
ShowOnProductPage = true,
DisplayOrder = specMaterialTypeDisplayOrder ////Proven that this needs to be the Spec Attribute display order - not the Option display order
//DisplayOrder = specOption.DisplayOrder
};
_specificationAttributeService.InsertProductSpecificationAttribute(psa);
}
else
{
logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/option", iRow, "Format", Format);
_logger.InsertLog(LogLevel.Warning, logError);
}
}
}
//Format
if (!String.IsNullOrEmpty(Format))
{
optionExists = false;
specExists = false;
if (productSpecs.Count() > 0)
{
foreach (ProductSpecificationAttribute ps in productSpecs)
{
if (ps.SpecificationAttributeOption.SpecificationAttributeId == specFormat)
{
specExists = true;
if (ps.SpecificationAttributeOption.Name == Format) //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
{
optionExists = true;
break;
}
}
}
}
if (!optionExists)
{
var specOption = GetSpecOptionByName(specFormat, Format); //get the option id so can create products spec attribute below
if (specOption.Name != null)
{
var psa = new ProductSpecificationAttribute
{
//AttributeTypeId = attributeTypeId,
AttributeTypeId = (int)SpecificationAttributeType.Option,
SpecificationAttributeOptionId = specOption.Id,
ProductId = product.Id,
CustomValue = null,
AllowFiltering = true, //can do this since it's an "Option" attribute type
ShowOnProductPage = true,
DisplayOrder = specFormatDisplayOrder ////Proven that this needs to be the Spec Attribute display order - not the Option display order
//DisplayOrder = specOption.DisplayOrder
};
_specificationAttributeService.InsertProductSpecificationAttribute(psa);
}
else
{
logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/option", iRow, "Format", Format);
_logger.InsertLog(LogLevel.Warning, logError);
}
}
}
//FormFactor
if (!String.IsNullOrEmpty(FormFactor))
{
optionExists = false;
specExists = false;
if (productSpecs.Count() > 0)
{
foreach (ProductSpecificationAttribute ps in productSpecs)
{
if (ps.SpecificationAttributeOption.SpecificationAttributeId == specFormFactor)
{
specExists = true;
if (ps.SpecificationAttributeOption.Name == FormFactor) //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
{
optionExists = true;
break;
}
}
}
}
if (!optionExists)
{
var specOption = GetSpecOptionByName(specFormFactor, FormFactor); //get the option id so can create products spec attribute below
if (specOption.Name != null)
{
var psa = new ProductSpecificationAttribute
{
//AttributeTypeId = attributeTypeId,
AttributeTypeId = (int)SpecificationAttributeType.Option,
SpecificationAttributeOptionId = specOption.Id,
ProductId = product.Id,
CustomValue = null,
AllowFiltering = true, //can do this since it's an "Option" attribute type
ShowOnProductPage = true,
DisplayOrder = specFormFactorDisplayOrder ////Proven that this needs to be the Spec Attribute display order - not the Option display order
//DisplayOrder = specOption.DisplayOrder
};
_specificationAttributeService.InsertProductSpecificationAttribute(psa);
}
else
{
logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/option", iRow, "FormFactor", FormFactor);
_logger.InsertLog(LogLevel.Warning, logError);
}
}
}
//UsageState
if (!String.IsNullOrEmpty(UsageState))
{
optionExists = false;
specExists = false;
if (productSpecs.Count() > 0)
{
foreach (ProductSpecificationAttribute ps in productSpecs)
{
if (ps.SpecificationAttributeOption.SpecificationAttributeId == specUsageState)
{
specExists = true;
if (ps.SpecificationAttributeOption.Name == UsageState) //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
{
optionExists = true;
break;
}
}
}
}
if (!optionExists)
{
var specOption = GetSpecOptionByName(specUsageState, UsageState); //get the option id so can create products spec attribute below
if (specOption.Name != null)
{
var psa = new ProductSpecificationAttribute
{
//AttributeTypeId = attributeTypeId,
AttributeTypeId = (int)SpecificationAttributeType.Option,
SpecificationAttributeOptionId = specOption.Id,
ProductId = product.Id,
CustomValue = null,
AllowFiltering = true, //can do this since it's an "Option" attribute type
ShowOnProductPage = true,
DisplayOrder = specUsageStateDisplayOrder ////Proven that this needs to be the Spec Attribute display order - not the Option display order
//DisplayOrder = specOption.DisplayOrder
};
_specificationAttributeService.InsertProductSpecificationAttribute(psa);
}
else
{
logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/otion", iRow, "UsageState", UsageState);
_logger.InsertLog(LogLevel.Warning, logError);
}
}
}
//Grade
if (!String.IsNullOrEmpty(Grade))
{
optionExists = false;
specExists = false;
if (productSpecs.Count() > 0)
{
foreach (ProductSpecificationAttribute ps in productSpecs)
{
if (ps.SpecificationAttributeOption.SpecificationAttributeId == specGrade)
{
specExists = true;
if (ps.SpecificationAttributeOption.Name == Grade) //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
{
optionExists = true;
break;
}
}
}
}
if (!optionExists)
{
var specOption = GetSpecOptionByName(specGrade, Grade); //get the option id so can create products spec attribute below
if (specOption.Name != null)
{
var psa = new ProductSpecificationAttribute
{
//AttributeTypeId = attributeTypeId,
AttributeTypeId = (int)SpecificationAttributeType.Option,
SpecificationAttributeOptionId = specOption.Id,
ProductId = product.Id,
CustomValue = null,
AllowFiltering = true, //can do this since it's an "Option" attribute type
ShowOnProductPage = true,
DisplayOrder = specGradeDisplayOrder ////Proven that this needs to be the Spec Attribute display order - not the Option display order
//DisplayOrder = specOption.DisplayOrder
};
_specificationAttributeService.InsertProductSpecificationAttribute(psa);
}
else
{
logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/otion", iRow, "Grade", Grade);
_logger.InsertLog(LogLevel.Warning, logError);
}
}
}
//SymbolSummary
if (!String.IsNullOrEmpty(SymbolSummary))
{
optionExists = false;
specExists = false;
if (productSpecs.Count() > 0)
{
foreach (ProductSpecificationAttribute ps in productSpecs)
{
if (ps.SpecificationAttributeOption.SpecificationAttributeId == specSymbolSummary)
{
specExists = true;
if (ps.SpecificationAttributeOption.Name == SymbolSummary) //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
{
optionExists = true;
break;
}
}
}
}
if (!optionExists)
{
var specOption = GetSpecOptionByName(specSymbolSummary, SymbolSummary); //get the option id so can create products spec attribute below
if (specOption.Name != null)
{
var psa = new ProductSpecificationAttribute
{
//AttributeTypeId = attributeTypeId,
AttributeTypeId = (int)SpecificationAttributeType.Option,
SpecificationAttributeOptionId = specOption.Id,
ProductId = product.Id,
CustomValue = null,
AllowFiltering = true, //can do this since it's an "Option" attribute type
ShowOnProductPage = true,
DisplayOrder = specSymbolSummaryDisplayOrder ////Proven that this needs to be the Spec Attribute display order - not the Option display order
//DisplayOrder = specOption.DisplayOrder
};
_specificationAttributeService.InsertProductSpecificationAttribute(psa);
}
else
{
logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/otion", iRow, "SymbolSummary", SymbolSummary);
_logger.InsertLog(LogLevel.Warning, logError);
}
}
}
} //end of product spec attributes
//end of doing crap - count record as added or changed
recordsaddchanged++;
//next product
iRow++;
}
logError = string.Format("Lot Import: End Lot Import by User: {0}. #Records added or changed: {1}. #Records ignored due to errors in row: {2}", CurrentCustomer.Username, recordsaddchanged, recordsignored);
_logger.InsertLog(LogLevel.Information, logError);
} //end of using excel package
} //end of class
//TODO: copy lot provide option to copy fees and/or current active sale.
/// <summary>
/// Create a copy of lot associated to product. The new lot will remain within the same consignment as the old lot but
/// we will not will not copy lot fees or any sale-lot or reference-lot junctions. The new lot will also remain within the same
/// country and stateprovince as the old lot.
/// </summary>
/// <param name="product">The product associated to the lot to copy</param>
/// <param name="newSKu">The new sku of product duplicate</param>
/// <returns>The new AULotRecord copy</returns>
///
public virtual AULotRecord CopyLot(Product origProduct, string newSku)
{
if (origProduct == null)
throw new ArgumentNullException("Original Product missing");
if (String.IsNullOrEmpty(newSku))
throw new ArgumentException("New Product sku is required");
var newProduct = _productService.GetProductBySku(newSku);
var origLot = _consignorService.GetLotByProductId(origProduct.Id);
if (origLot == null)
throw new ArgumentException("New Product sku is required");
var currDate = System.DateTime.UtcNow;
var currentCustomer = _authenticationService.GetAuthenticatedCustomer();
var newLot = new AULotRecord()
{
AUProductID = newProduct.Id,
Sku = newSku,
CreatedOnDateTime = currDate,
UpdatedOnDateTime = currDate, //make sure dates are the same on insert
CreatedByUsername = currentCustomer.Username,
UpdatedByUsername = currentCustomer.Username,
//TODO: Make this a view model - forcing this in though it doesn't hit the DB
ConsignmentID = 999999999
};
//_consignorService.InsertLot(newLot);
//TODO: MAKE InsertLot return the new lot record so can fully subsume data layer
//TODO: Make transaction
_lotRepo.Insert(newLot);
AULotLastBidChangeRecord lastbid = new AULotLastBidChangeRecord();
lastbid.AULotId = newLot.AULotID;
lastbid.AUProductId = newLot.AUProductID;
lastbid.BidderId = newLot.LastBidId;
lastbid.LastBidChangeDT = newLot.CreatedOnDateTime;
lastbid.LastMaxJobRunDT = newLot.CreatedOnDateTime;
_lotlastbidchangeRepo.Insert(lastbid);
//create junction record to track lot copies, splits,consolidates
CreateLotLot(origProduct.Id, newProduct.Id, origLot.AULotID, newLot.AULotID);
var newConsignmentLot = new AUConsignmentLotRecord()
{
AULotID = newLot.AULotID
};
foreach (var consignment in origLot.AUConsignmentRecords)
{
newConsignmentLot.AUConsignmentID = consignment.AUConsignmentID;
_consignmentlotRepo.Insert(newConsignmentLot);
}
var newCountryLot = new AUCountryLotRecord()
{
AULotID = newLot.AULotID
};
foreach (var country in origLot.AUCountryRecords)
{
newCountryLot.AUCountryID = country.AUCountryID;
_countrylotRepo.Insert(newCountryLot);
}
var newStateProvinceLot = new AUStateProvinceLotRecord()
{
AULotID = newLot.AULotID
};
foreach (var stateprovince in origLot.AUStateProvinceRecords)
{
newStateProvinceLot.AUStateProvinceID = stateprovince.AUStateProvinceID;
_stateprovincelotRepo.Insert(newStateProvinceLot);
}
return newLot;
}
/// <summary>
/// Inserts an AUStateProvinceLot
/// </summary>
/// <param name="stateprovincelot">AUStateProvinceLotRecord</param>
public virtual void InsertStateProvinceLot(AUStateProvinceLotRecord stateprovincelot)
{
if (stateprovincelot == null)
throw new ArgumentNullException("stateprovincelot");
//insert
_stateprovincelotRepo.Insert(stateprovincelot);
//TODO: ADD LOT CACHE CONTROL AND PUBLISHING
////clear cache
//_cacheManager.RemoveByPattern(PRODUCTS_PATTERN_KEY);
////event notification
//_eventPublisher.EntityInserted(product);
}