private static void FillTechSpecSheet(IExcelService excel, Order orderItem, bool autoCounting)
{
int lotCount = 1;
foreach (var item in orderItem.Auction.SupplierOrders[0].lots)
{
// Create new sheet and copy header for more then one lot
if (lotCount > 1)
{
excel.AddSheet(string.Format("Тех. спец. по лоту №{0}", item.Number));
excel.SetSheetByIndex(lotCount);
excel.SetSheetByIndex(lotCount + 1);
excel.PasteRangeTo(1, "A", 3, "N", lotCount);
}
else
{
excel.SetSheetByIndex(lotCount + 1);
}
excel.ChangeSheetName(lotCount + 1, string.Format("Тех. спец. по лоту №{0}", item.Number));
// Header
excel.SetCells(1, "A", string.Format("Техническая спецификация к лоту №{0} - {1}", lotCount, item.Number));
// Formula variables
decimal endSum = 0;
if (orderItem.Auction.Procuratories != null && orderItem.Auction.Procuratories.Count > 0)
{
var procSum = orderItem.Auction.Procuratories.First(p => p.lotId == item.Id);
if (procSum != null)
{
endSum = procSum.MinimalPrice;
}
}
//autoCouting
if (autoCounting)
{
// Old KV
//decimal difference = item.Sum - endSum;
//decimal factor = difference / item.LotsExtended.Count;
//foreach (var subItem in item.LotsExtended) {
// subItem.endprice = subItem.price - factor / item.Quantity;
// subItem.endsum = subItem.sum - factor;
//}
// New
decimal difference = 100 - ((item.Sum - endSum) / (item.Sum / 100));
foreach (var subItem in item.LotsExtended)
{
subItem.endprice = subItem.price / 100 * difference;
subItem.endsum = subItem.endprice * subItem.quantity;
}
}
// Full sum
excel.SetCells(5, "F", item.LotsExtended.Sum(l => l.sum));
excel.SetCells(5, "N", item.LotsExtended.Sum(l => l.endsum));
// Content
int rowCount = 4;
foreach (var subItem in item.LotsExtended)
{
if (rowCount != 4)
{
excel.InsertRow(rowCount);
}
excel.SetCells(rowCount, "A", subItem.serialnumber);
excel.SetCells(rowCount, "B", subItem.name);
excel.SetCellWrapText(rowCount, "B", true);
excel.SetCells(rowCount, "C", subItem.unit);
excel.SetCells(rowCount, "D", subItem.quantity);
excel.SetCells(rowCount, "E", subItem.price);
excel.SetCells(rowCount, "F", subItem.sum);
excel.SetCells(rowCount, "G", subItem.country);
excel.SetCellWrapText(rowCount, "G", true);
excel.SetCells(rowCount, "H", subItem.techspec);
excel.SetCellWrapText(rowCount, "H", true);
excel.SetCells(rowCount, "I", subItem.terms);
excel.SetCellWrapText(rowCount, "I", true);
excel.SetCells(rowCount, "J", subItem.paymentterm);
excel.SetCellWrapText(rowCount, "J", true);
excel.SetCells(rowCount, "K", subItem.dks);
excel.SetCells(rowCount, "L", subItem.contractnumber);
excel.SetCells(rowCount, "M", subItem.endprice);
excel.SetCells(rowCount, "N", subItem.endsum);
for (var i = 1; i < 15; i++)
{
excel.SetCellFontVAlignment(rowCount, i, 2);
excel.SetCellBorder(rowCount, i);
}
rowCount++;
}
lotCount++;
}
}
