public void AddRest(Guid inventorizationId, Rests rest)
{
using (var conn = new NpgsqlConnection(_connectionString))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"INSERT INTO public.""Rests""(""Code"", ""Count"", ""Price"", ""InventorizationId"") VALUES(:Code, :Count, :Price, :InventorizationId)";
cmd.Parameters.Add(new NpgsqlParameter("Code", rest.Code));
cmd.Parameters.Add(new NpgsqlParameter("Count", rest.Count));
cmd.Parameters.Add(new NpgsqlParameter("Price", rest.Price));
cmd.Parameters.Add(new NpgsqlParameter("InventorizationId", inventorizationId));
cmd.ExecuteNonQuery();
}
}
_rests.Clear();
}
public void Report3Builds()
{
Report3Generator generator = new Report3Generator(AppDomain.CurrentDomain.BaseDirectory + @"\Templates\Report3.XLSX");
Fixture itemsFixture = new Fixture();
List <Item> data = itemsFixture.CreateMany <Item>(100).ToList();
List <Inventorization.Business.Model.Action> actions = data.SelectMany(x =>
{
var itemActions = itemsFixture.CreateMany <Inventorization.Business.Model.Action>(50).ToList();
return(itemActions.Select(i => { i.BarCode = x.Code; i.Quantity = 1; return i; }));
}).ToList();
Random rnd = new Random();
IEnumerable <Rests> rests = data.Select(x =>
{
var res = new Rests()
{
Code = x.Code,
Count = rnd.Next(1, 10),
InventorizationId = Guid.NewGuid()
};
return(res);
});
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
using (MemoryStream stream = generator.Generate(data, actions, rests.ToList()))
{
using (FileStream fs = File.Create(AppDomain.CurrentDomain.BaseDirectory + "\\" + Guid.NewGuid().ToString() + ".xlsx"))
{
fs.Write(stream.ToArray(), 0, (int)stream.Length);
fs.Close();
}
}
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
Console.WriteLine("RunTime " + elapsedTime);
}
public override MemoryStream Generate(List <Item> items, List <Model.Action> actions, List <Rests> rests)
{
using (XLWorkbook book = new XLWorkbook(pathToTemplate))
{
IXLWorksheet worksheet = book.Worksheets.Skip(1).First();
IEnumerable <IXLRow> dataRows = worksheet.Rows().Where(r => r.RowNumber() >= firstDataRowIndex);
dataRows.ForEach(dr => dr.Delete());
IXLRow currentDataRow = GetFirstDataRow(worksheet.Rows());
currentDataRow.InsertRowsBelow(items.Count);
IEnumerable <IGrouping <string, Model.Action> > grouppedActions = actions.GroupBy(x => x.BarCode);
int counter = 1;
var bunches = BuildBunches(items);
var lastBunch = bunches.Last();
foreach (var bunch in bunches)
{
var firstRowInBunch = currentDataRow;
foreach (Item item in bunch)
{
long? quantity = grouppedActions.FirstOrDefault(x => x.Key == item.Code)?.Sum(x => x.Quantity);
Rests itemRests = rests.FirstOrDefault(x => x.Code == item.Code);
SetRowData(worksheet, currentDataRow, item, itemRests, quantity, counter++);
currentDataRow = currentDataRow.RowBelow();
}
var restsInBunch = rests.Where(x => bunch.Any(b => b.Code == x.Code));
currentDataRow = SetSummary(worksheet, currentDataRow, bunch, restsInBunch, grouppedActions);
if (bunch != lastBunch)
{
currentDataRow.AddHorizontalPageBreak();
currentDataRow = currentDataRow.RowBelow();
}
worksheet.PageSetup.PrintAreas.Add(firstRowInBunch.Cell("A").Address, currentDataRow.Cell("X").Address);
}
currentDataRow = SetSummary(worksheet, currentDataRow, items, rests, grouppedActions);
MemoryStream result = new MemoryStream();
book.SaveAs(result);
result.Position = 0;
return(result);
}
}
private void BuildPatterns()
{
/// Phonemes must be ordered by string length, else there will be false
/// positive results which ends as wrong recognition
var vowels_for_pattern = Vowels.OrderByDescending(n => n.Length).ToList();
var consonants_for_pattern = Consonants.OrderByDescending(n => n.Length).ToList();
var rests_for_pattern = Rests.OrderByDescending(n => n.Length).ToList();
VowelPattern = $"{String.Join("|", vowels_for_pattern)}";
ConsonantPattern = $"{String.Join("|", consonants_for_pattern)}";
RestPattern = $"{String.Join("|", rests_for_pattern)}";
foreach (var shit in ". ? ( ) [ ] * \\".Split(' '))
{
VowelPattern = VowelPattern.Replace(shit, "\\" + shit);
ConsonantPattern = ConsonantPattern.Replace(shit, "\\" + shit);
RestPattern = RestPattern.Replace(shit, "\\" + shit);
}
VowelPattern = $"({VowelPattern})";
ConsonantPattern = $"({ConsonantPattern})";
RestPattern = $"({RestPattern})";
}
private void SetRowData(IXLWorksheet worksheet, IXLRow row, Item item, Rests rests, long?quantity, int index)
{
var numberCell = worksheet.Range(row.Cell("A"), row.Cell("B")).Merge(false);
numberCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var billCell = worksheet.Range(row.Cell("C"), row.Cell("E")).Merge(false);
billCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var nameCell = row.Cell("F");
nameCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var itemNumberCell = worksheet.Range(row.Cell("G"), row.Cell("H")).Merge(false);
itemNumberCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
row.Cell("I").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Range(row.Cell("J"), row.Cell("K")).Merge(false).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var priceCell = worksheet.Range(row.Cell("L"), row.Cell("M")).Merge(false);
priceCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
priceCell.DataType = XLCellValues.Number;
var inventNumberCell = row.Cell("N");
inventNumberCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Range(row.Cell("O"), row.Cell("P")).Merge(false).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var countFactCell = row.Cell("Q");
countFactCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var sumFactCell = worksheet.Range(row.Cell("R"), row.Cell("S")).Merge(false);
sumFactCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
sumFactCell.Style.NumberFormat.Format = "# ### ### ₽";
sumFactCell.DataType = XLCellValues.Number;
var countPlanCell = row.Cell("T");
countPlanCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var sumPlanCell = worksheet.Range(row.Cell("U"), row.Cell("W")).Merge(false);
sumPlanCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
sumPlanCell.Style.NumberFormat.Format = "# ### ### ₽";
sumPlanCell.DataType = XLCellValues.Number;
long?fact = quantity;
numberCell.Value = index++;
nameCell.Value = item.Name;
itemNumberCell.Value = item.ItemNumber;
if (rests != null)
{
if (rests.Price > default(decimal))
{
priceCell.Style.NumberFormat.Format = "# ### ### ₽";
priceCell.Value = rests.Price;
}
inventNumberCell.Value = item.Code;
if (fact.HasValue)
{
countFactCell.Value = fact.Value;
if (rests.Price != default(decimal))
{
sumFactCell.FormulaA1 = $"=Q{countFactCell.Address.RowNumber} * L{countFactCell.Address.RowNumber}";
}
}
countPlanCell.Value = rests?.Count;
if (rests.Price != default(decimal))
{
sumPlanCell.FormulaA1 = $"=T{countPlanCell.Address.RowNumber} * L{countPlanCell.Address.RowNumber}";
}
}
}
