public Task <string> PrintItemTable(Packliste packliste)
{
var path = CreatePdfItemTable(packliste);
//OpenPdf(path);
PrintPdf(path);
return(Task.FromResult("Printing successful"));
}
public async Task <bool> Update(int id, Packliste packliste)
{
// Update
using (var db = Connection)
{
const string sql = @"UPDATE [Packlistes] SET Destination = @Destination, PacklisteDate = @PacklisteDate, PacklisteNumber = @PacklisteNumber WHERE PacklisteId = @PacklisteId";
var result = await db.ExecuteAsync(sql, new { packliste.Destination, packliste.PacklisteDate, packliste.PacklisteNumber, PacklisteId = id }).ConfigureAwait(false);
return(result != 0);
}
}
public async Task <bool> Insert(Packliste packliste)
{
// Insert
using (var db = Connection)
{
const string sql = @"INSERT INTO [Packlistes] (Destination, PacklisteDate, PacklisteNumber) VALUES (@Destination, @PacklisteDate, @PacklisteNumber)";
var result = await db.ExecuteAsync(sql, new { packliste.Destination, packliste.PacklisteDate, packliste.PacklisteNumber }).ConfigureAwait(false);
return(result != 0);
}
}
public static void FromExcel(Action <Packliste, Exception> callback, FileInfo excelFile,
IDataService dataService)
{
var dateResult = DateTime.TryParse(excelFile.Name.Substring(0, 10), CultureInfo.InvariantCulture,
DateTimeStyles.None, out var packDate);
List <Item> items = null;
dataService.GetItems(((itemss, materials, exception) =>
{
if (exception != null)
{
throw exception;
}
items = itemss.ToList();
}));
if (!dateResult)
{
packDate = DateTime.Now;
}
using (var excel = new ExcelPackage(excelFile))
{
var worksheet = excel.Workbook.Worksheets[1];
var englishPackliste = worksheet.Cells.FirstOrDefault(c =>
string.Equals(c.Text, "number", StringComparison.InvariantCultureIgnoreCase))?.Address;
var packlisteData = GetPacklisteData(worksheet);
var packlisteItems = (List <ItemWithQty>)GetItems(items, packlisteData, dataService);
var results = packlisteItems.GroupBy(item => item.Item).Select(g =>
new ItemWithQty {
Item = g.First().Item, Quantity = g.Sum(i => i.Quantity)
}).ToList();
var packliste = new Packliste
{
ItemsWithQties = new ObservableCollection <ItemWithQty>(results),
PacklisteNumber = -1,
PacklisteDate = packDate,
Destination = "Tarm"
};
callback(packliste, null);
}
}
private static string CreateItemTable(Packliste packliste)
{
var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\temp.xlsx";
using (var excel = new ExcelPackage(new FileInfo(path)))
{
var worksheet = excel.Workbook.Worksheets.Add("Sheet 1");
worksheet.Cells[1, 1].Value = packliste.PacklisteDate;
worksheet.Cells[1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
worksheet.Cells[1, 2].Value = "Middle parts shipment";
worksheet.Cells[2, 1].Value = "Item number";
worksheet.Cells[2, 2].Value = "Quantity";
using (var range = worksheet.Cells[1, 1, 2, 2])
{
range.Style.Font.Bold = true;
range.Style.Border.BorderAround(ExcelBorderStyle.Medium);
range.Style.Border.Left.Style = ExcelBorderStyle.Medium;
range.Style.Border.Right.Style = ExcelBorderStyle.Medium;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
range.Style.Border.Top.Style = ExcelBorderStyle.Medium;
range.Style.Font.Bold = true;
range.Style.Font.Size = 20;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
worksheet.Column(1).Width = 30.51d;
worksheet.Column(2).Width = 43.13d;
worksheet.Row(1).Height = 63.75d;
worksheet.Row(2).Height = 28.5d;
var row = 3;
foreach (var data in packliste.ItemsWithQties)
{
worksheet.Cells[row, 1].Value = data.Item.ItemName;
worksheet.Cells[row, 2].Value = data.Quantity;
worksheet.Row(row).Height = 28.5d;
row++;
}
using (var range = worksheet.Cells[2, 1, row - 1, 2])
{
range.Style.Border.BorderAround(ExcelBorderStyle.Medium);
range.Style.Border.BorderAround(ExcelBorderStyle.Medium);
range.Style.Border.Left.Style = ExcelBorderStyle.Medium;
range.Style.Border.Right.Style = ExcelBorderStyle.Medium;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
range.Style.Border.Top.Style = ExcelBorderStyle.Medium;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.Font.Size = 14;
}
worksheet.PrinterSettings.PaperSize = ePaperSize.A4;
worksheet.PrinterSettings.Orientation = eOrientation.Portrait;
worksheet.PrinterSettings.HorizontalCentered = true;
worksheet.PrinterSettings.FitToPage = true;
worksheet.PrinterSettings.FitToWidth = 1;
worksheet.PrinterSettings.FitToHeight = 0;
excel.Save();
}
return(path);
}
private static string CreatePdfItemTable(Packliste packliste)
{
var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\temp.pdf";
var document = new Document();
var page = document.AddSection();
page.PageSetup.LeftMargin = 45;
page.PageSetup.RightMargin = 45;
page.PageSetup.PageFormat = PageFormat.A4;
var font = new Font("Arial", 22)
{
Bold = true
};
var headParagraph = page.AddParagraph();
headParagraph.Format.SpaceAfter = 12;
headParagraph.AddFormattedText($"{packliste.PacklisteDate:yyyy-MM-dd} \t\tMiddle parts shipment", font);
var table = page.AddTable();
table.Borders.Width = 0.5;
table.BottomPadding = 2;
table.TopPadding = 2;
var column = table.AddColumn("8.5cm");
column.Format.Alignment = ParagraphAlignment.Center;
column = table.AddColumn("6cm");
column.Format.Alignment = ParagraphAlignment.Center;
font.Size = 18;
font.Bold = true;
var row = table.AddRow();
row.Format.Alignment = ParagraphAlignment.Center;
row.Format.Font = font.Clone();
row.Cells[0].AddParagraph("Item number");
row.Cells[1].AddParagraph("Quantity");
font.Size = 16;
font.Bold = false;
foreach (var data in packliste.ItemsWithQties)
{
row = table.AddRow();
row.Format.Alignment = ParagraphAlignment.Center;
row.Format.Font = font.Clone();
row.Cells[0].AddParagraph(data.Item.ItemName);
row.Cells[1].AddParagraph($"{data.Quantity:N}");
}
var renderer = new PdfDocumentRenderer {
Document = document
};
renderer.RenderDocument();
renderer.Save(path);
return(path);
}
public static Task <(Packliste, string)> FromCOCs(FileInfo excelFile, IDataService dataService)
{
var cOCs = new List <COC>();
var packliste = new Packliste();
var cocNumbers = new List <int>();
var items = new List <ItemWithQty>();
var packlisteItems = new List <ItemWithQty>();
var dateResult = DateTime.TryParse(excelFile.Name.Substring(0, 10), CultureInfo.InvariantCulture,
DateTimeStyles.None, out var packDate);
if (!dateResult)
{
packDate = DateTime.Now;
}
dataService.GetCOCs(((cocs, exception) =>
{
if (exception != null)
{
throw exception;
}
cOCs = cocs.ToList();
}), packDate);
dataService.GetItemsWithQty(((itemWithQties, exception) =>
{
if (exception != null)
{
throw exception;
}
items = new List <ItemWithQty>(itemWithQties);
}));
using (var excel = new ExcelPackage(excelFile))
{
var worksheet = excel.Workbook.Worksheets[1];
var endRow = worksheet.Dimension.End.Row;
for (var i = 1; i <= endRow; i++)
{
var cocParse = int.TryParse(worksheet.Cells[i, 1].Value.ToString(), out var cocNumber);
if (cocParse == false)
{
continue;
}
var coc = cOCs.SingleOrDefault(c => c.CocNumber == cocNumber);
if (coc == null)
{
cocNumbers.Add(cocNumber);
continue;
}
var item = items.SingleOrDefault(s => s.ItemWithQtyId == coc.Item.ItemWithQtyId);
if (item == null)
{
return(Task.FromResult((packliste, $"Something went wrong: {coc.Item.Item}, {cocNumber}")));
}
packlisteItems.Add(item);
}
}
packliste.PacklisteDate = packDate;
packliste.Destination = "Tarm";
packliste.ItemsWithQties = new ObservableCollection <ItemWithQty>(packlisteItems);
packliste.PacklisteNumber = -1;
packliste.RawUsage = new ObservableCollection <MaterialAmount>(CalculateUsage(packlisteItems));
if (cocNumbers.Any())
{
var message = new StringBuilder("Missing COCs:\n");
foreach (var cocNumber in cocNumbers)
{
message.AppendLine(cocNumber.ToString());
}
return(Task.FromResult((packliste, message.ToString())));
}
return(Task.FromResult((packliste, "Success")));
}
public async Task <IActionResult> Put(int id, [FromBody] Packliste packingList)
{
var result = await _repository.Update(id, packingList).ConfigureAwait(false);
return(result ? new StatusCodeResult(200) : new StatusCodeResult(422));
}
public async Task <IActionResult> Post([FromBody] Packliste packingList)
{
var result = await _repository.Insert(packingList).ConfigureAwait(false);
return(result ? new StatusCodeResult(200) : new StatusCodeResult(422));
}