public string UpdateLotEx(LotsExtended lotsExtended)
{
if (DataManager.UpdateLotsExtended(lotsExtended) == 1)
{
return("Данные успешно сохранены");
}
else
{
return("Ошибка при сохранении");
}
}
public static List <LotsExtended> ParseTechSpec(string sourceFileName, string lotCode)
{
// Open file in excel
IExcelService excel = new ExcelService(sourceFileName);
// Check for right data structure
excel.SetSheetByIndex(1);
var procTest = excel.FindRow("Поручение");
if (procTest == 0)
{
return(null);
}
// Get sheets count
int sheetsCount = excel.GetSheetsCount();
List <LotsExtended> lotExList = new List <LotsExtended>();
// Parse tables for getting data
for (var curSheet = 2; curSheet == sheetsCount; curSheet++)
{
excel.SetSheetByIndex(curSheet);
var lotTest = excel.FindRow(lotCode);
if (lotTest != 0)
{
int rowCount = 4;
while (!string.IsNullOrEmpty(excel.GetCell(rowCount, "A")))
{
LotsExtended lotExItem = new LotsExtended()
{
serialnumber = Convert.ToInt32(excel.GetCell(rowCount, "A")),
name = excel.GetCell(rowCount, "B"),
unit = excel.GetCell(rowCount, "C"),
quantity = Convert.ToDecimal(excel.GetCell(rowCount, "D")),
price = Convert.ToDecimal(excel.GetCell(rowCount, "E")),
sum = Convert.ToDecimal(excel.GetCell(rowCount, "F")),
country = excel.GetCell(rowCount, "G"),
techspec = excel.GetCell(rowCount, "H"),
terms = excel.GetCell(rowCount, "I"),
paymentterm = excel.GetCell(rowCount, "J"),
dks = Convert.ToInt32(excel.GetCell(rowCount, "K")),
contractnumber = excel.GetCell(rowCount, "L"),
endprice = Convert.ToDecimal(excel.GetCell(rowCount, "M")),
endsum = Convert.ToDecimal(excel.GetCell(rowCount, "N"))
};
lotExList.Add(lotExItem);
rowCount++;
}
}
}
// Close excel
excel.CloseWorkbook();
excel.CloseExcel();
return(lotExList);
}
public override Order UpdateOrder(Order order)
{
if (order == null)
{
return(null);
}
// Get start params about excel file
int sheetsAllCount = excel.GetSheetsCount();
int sheetsVisCount = excel.GetSheetsCount(false);
// Open sheet with order & parse them into orderBO
int orderIndex = 0;
for (var i = 1; i < sheetsAllCount + 1; i++)
{
excel.SetSheetByIndex(i);
if (excel.CheckSheetVisibility())
{
orderIndex = i;
break;
}
}
if (excel.IsSheetOpened())
{
order.Initiator = excel.GetLabelValue("Полное наименование Заказчика");
var brokerCode = excel.GetLabelValue("Код Брокера Заказчика");
brokerCode = brokerCode.ToUpper().Replace("К", "K").Replace("А", "A");
switch (brokerCode.ToLower().Substring(0, 4))
{
case "altk": order.Auction.Broker = new Broker(brokerCode, "ТОО «Альта и К», Андреев В.И., Директор Казахстан, 050064, г.Алматы, мкрн.Думан - 2, дом 18, кв. 55 тел.: +7(727) 390 - 43 - 02 e - mail: info @altaik.kz"); break;
case "alta": order.Auction.Broker = new Broker("ALTA", "ТОО «Альтаир Нур»"); break;
}
order.Auction.ExchangeProvisionSize = excel.GetLabelValue("Размер Гарантийного обеспечения");
var regex = new Regex(@"[^\d,\.]*");
order.Auction.ExchangeProvisionSize = regex.Replace(order.Auction.ExchangeProvisionSize, "").Replace(".", ",");
lotName = Regex.Replace(excel.GetLabelValue("Наименование товара"), @"[Лл]от\s*№\d*:\s*", "");
}
// Open sheet(-s) with tech spec & parse them into orderBO
int lotCount = sheetsVisCount - 2;
int lotTechSpec = orderIndex + 1;
for (var i = 1; i < lotCount + 1; i++)
{
for (var iLot = lotTechSpec; iLot < sheetsAllCount + 1; iLot++)
{
excel.SetSheetByIndex(iLot);
if (excel.CheckSheetVisibility())
{
lotTechSpec = iLot + 1;
break;
}
}
if (excel.IsSheetOpened())
{
// Change title
var titleTechRowWord = excel.FindRow(DocumentTypeEnum.TechSpecs.GetName() + " к Ло");
if (titleTechRowWord != 0)
{
excel.SetCells(titleTechRowWord, "A", "Техническая спецификация к Лоту №" + i);
}
// Delete not needed header rows
if (string.IsNullOrEmpty(excel.GetCell(1, 1)))
{
for (var iRow = 1; iRow < 5; iRow++)
{
excel.DeleteRow(1);
}
}
// Delete not needed footer rows and find sum
var rowsCount = excel.GetRowsCount();
var row = 0;
if (string.IsNullOrEmpty(excel.GetCell(excel.GetRowsCount(), "F")))
{
for (var iRow = rowsCount; iRow > 0; iRow--)
{
if (!string.IsNullOrEmpty(excel.GetCell(iRow, "F")))
{
row = iRow;
lotStartPrice = excel.GetCell(iRow, "F");
try {
if (!string.IsNullOrEmpty(excel.GetCell(iRow - 1, "K")))
{
lotMinRequerments = excel.GetCell(iRow - 1, "K");
}
else
{
lotMinRequerments = excel.GetCell(iRow - 2, "K");
}
} catch { lotMinRequerments = excel.GetCell(iRow - 2, "K"); }
break;
}
}
for (var iRow = row + 1; iRow < rowsCount + 1; iRow++)
{
excel.DeleteRow(row + 1);
}
}
else
{
lotStartPrice = excel.GetCell(excel.GetRowsCount(), "F");
}
// Create lot item
var lot = new Lot {
Number = i.ToString(),
Name = lotName,
MinRequerments = lotMinRequerments,
StartPrice = lotStartPrice
};
if (order.Auction.Lots == null)
{
order.Auction.Lots = new ObservableCollection <Lot>();
}
order.Auction.Lots.Add(lot);
// For Creating LotsExtended
var iRowNum = excel.FindRow("Наименование");
bool isBusy = true;
int iCount = 1;
if (order.Auction.Lots[i - 1].LotsExtended == null)
{
order.Auction.Lots[i - 1].LotsExtended = new ObservableCollection <LotsExtended>();
}
while (isBusy)
{
if (!string.IsNullOrEmpty(excel.GetCell(iRowNum + iCount, 1)))
{
LotsExtended lotEx = new LotsExtended();
lotEx.serialnumber = iCount;
lotEx.name = excel.GetCell(iRowNum + iCount, 2);
lotEx.unit = excel.GetCell(iRowNum + iCount, 3);
lotEx.quantity = Convert.ToDecimal(excel.GetCell(iRowNum + iCount, 4).Replace(".", ","));
lotEx.price = Convert.ToDecimal(excel.GetCell(iRowNum + iCount, 5).Replace(".", ","));
lotEx.sum = Convert.ToDecimal(excel.GetCell(iRowNum + iCount, 6).Replace(".", ","));
lotEx.country = excel.GetCell(iRowNum + iCount, 7);
lotEx.techspec = excel.GetCell(iRowNum + iCount, 8);
lotEx.terms = excel.GetCell(iRowNum + iCount, 9);
lotEx.paymentterm = excel.GetCell(iRowNum + iCount, 10);
try {
lotEx.dks = Convert.ToInt32(Regex.Replace(excel.GetCell(iRowNum + iCount, 11).Replace("%", ""), "[^0-9]", ""));
} catch (Exception) { lotEx.dks = 0; }
lotEx.contractnumber = excel.GetCell(iRowNum + iCount, 12);
order.Auction.Lots[i - 1].LotsExtended.Add(lotEx);
}
else
{
isBusy = false;
}
iCount++;
}
}
}
order.Warranty = WARRANTY_LETER;
return(order);
}
public static Order ParseKaspiOrder(string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
return(null);
}
var excel = new ExcelService(fileName);
if (excel == null)
{
return(null);
}
Order order = new Order();
order.Auction = new Auction();
order.Auction.Lots = new System.Collections.ObjectModel.ObservableCollection <Lot>();
order.Auction.Lots.Add(new Lot()
{
LotsExtended = new System.Collections.ObjectModel.ObservableCollection <LotsExtended>()
});
bool result;
DateTime dateTime;
decimal decimalNumber;
double doubleNumber;
int intNumber;
try {
order.Organizer = excel.GetCell("5", "D");
order.Initiator = excel.GetCell("6", "D");
order.Auction.Lots[0].Name = excel.GetCell("7", "D");
order.Auction.Type = excel.GetCell("8", "D");
result = decimal.TryParse(excel.GetCell("14", "D"), out decimalNumber);
order.Auction.Lots[0].Quantity = result ? decimalNumber : 0;
order.Auction.Lots[0].Unit = excel.GetCell("15", "D");
result = decimal.TryParse(excel.GetCell("16", "D"), out decimalNumber);
order.Auction.Lots[0].Price = result ? decimalNumber : 0;
order.Auction.Comments = excel.GetCell("17", "D"); // currency
result = decimal.TryParse(excel.GetCell("18", "D"), out decimalNumber);
order.Auction.Lots[0].Step = result ? decimalNumber : 0;
order.Auction.Lots[0].DeliveryPlace = excel.GetCell("19", "D");
order.Auction.Lots[0].PaymentTerm = excel.GetCell("21", "D");
order.Auction.Lots[0].DeliveryTime = excel.GetCell("22", "D");
result = DateTime.TryParse(excel.GetCell("23", "D"), out dateTime);
order.Auction.Date = result ? dateTime : DateTime.Now;
result = decimal.TryParse(excel.GetCell("25", "D"), out decimalNumber);
order.Auction.Lots[0].Sum = result ? decimalNumber : 0;
result = double.TryParse(excel.GetCell("26", "D"), out doubleNumber);
order.Auction.InvoicePercent = result ? doubleNumber : 0;
int rowCount = 31;
while (!string.IsNullOrEmpty(excel.GetCell(rowCount, "B")) && !string.IsNullOrEmpty(excel.GetCell(rowCount, "D")))
{
LotsExtended lotsExtended = new LotsExtended();
result = Int32.TryParse(excel.GetCell(rowCount, "B"), out intNumber);
lotsExtended.serialnumber = result ? intNumber : 0;
lotsExtended.name = excel.GetCell(rowCount, "C");
lotsExtended.unit = excel.GetCell(rowCount, "D");
result = decimal.TryParse(excel.GetCell(rowCount, "E"), out decimalNumber);
lotsExtended.quantity = result ? decimalNumber : 0;
result = decimal.TryParse(excel.GetCell(rowCount, "F"), out decimalNumber);
lotsExtended.price = result ? decimalNumber : 0;
lotsExtended.sum = lotsExtended.quantity * lotsExtended.price;
lotsExtended.marka = excel.GetCell("9", "D");
lotsExtended.gost = excel.GetCell("10", "D");
lotsExtended.codeTNVD = excel.GetCell("11", "D");
lotsExtended.country = excel.GetCell("12", "D");
lotsExtended.factory = excel.GetCell("13", "D");
lotsExtended.terms = excel.GetCell("20", "D");
order.Auction.Lots[0].LotsExtended.Add(lotsExtended);
}
} catch {
return(null);
} finally {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook();
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
}
return(order);
}
public static ObservableCollection <LotsExtended> ParseKaspiTechSpec(string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
return(null);
}
var excel = new ExcelService(fileName);
if (excel == null)
{
return(null);
}
ObservableCollection <LotsExtended> lotsExtendedList = new ObservableCollection <LotsExtended>();
bool result;
decimal decimalNumber;
int intNumber;
try {
var rowCount = excel.FindRow("Наименование товара");
if (rowCount == null || rowCount == 0)
{
throw new Exception();
}
rowCount += 2;
while (!string.IsNullOrEmpty(excel.GetCell(rowCount, "C")) && !string.IsNullOrEmpty(excel.GetCell(rowCount, "E")))
{
LotsExtended lotsExtended = new LotsExtended();
result = Int32.TryParse(excel.GetCell(rowCount, "C"), out intNumber);
lotsExtended.serialnumber = result ? intNumber : 0;
lotsExtended.name = excel.GetCell(rowCount, "D");
lotsExtended.unit = excel.GetCell(rowCount, "E");
result = decimal.TryParse(excel.GetCell(rowCount, "F"), out decimalNumber);
lotsExtended.quantity = result ? decimalNumber : 0;
result = decimal.TryParse(excel.GetCell(rowCount, "H"), out decimalNumber);
lotsExtended.price = result ? decimalNumber : 0;
lotsExtended.sum = lotsExtended.quantity * lotsExtended.price;
lotsExtended.terms = excel.GetCell(rowCount, "K");
lotsExtendedList.Add(lotsExtended);
rowCount += 1;
}
} catch {
return(null);
} finally {
if (excel.IsWorkbookOpened())
{
excel.CloseWorkbook();
}
if (excel.IsExcelOpened())
{
excel.CloseExcel();
}
}
return(lotsExtendedList);
}
