public virtual Items GetStatisticData()
{
Items allData = new Items();
List<ItemData> data = new List<ItemData>(this.GetAllItemsData());
allData.Rows = data;
List<ItemData> currentData = this.DBHelper.GetMainData(data, this.DBHelper.GetFilter(FilterDataId));
// Tutoka nacitame polozky, ktore neboli zahrnute v metode DBHelper.GetMainData
IEnumerable<ItemData> unprocessed = data.Where(d => string.IsNullOrEmpty(d.Name) || d.SKzId <= 0);
// Skusime nacitavat nespracovane data vo viacerych threadoch
// Toto mozno bude treba optimalizovat na nacitanie len urcitych nazvov naraz, teraz to ide po jednom
Parallel.ForEach(unprocessed, item => this.DBHelper.GetMainItemData(item));
// Nacitanie hmotnosti a skladovych zasob
this.DBHelper.UpdateMainData(data);
// Nacitanie spotreby pre aktualny rok
this.DBHelper.UpdateConsumption(data, this.DBHelper.GetFilter(this.FilterConsumptionId));
// A nakoniec nacitanie objednavok
allData.Orders = this.DBHelper.UpdateOrdered(data, this.DBHelper.GetFilter(this.FilterOrderedId));
return allData;
}
public Stream GenerateReport(string productCode, Items data)
{
try
{
//initial check for argument
if (string.IsNullOrEmpty(productCode) || productCode.Length != 3)
throw new ArgumentException("productCode");
//load template settings for specified product code
_settings = Serializator.Deserialize(Path.Combine(TemplateDirPath, string.Format("{0}.xml", productCode)));
// Opening the Excel template...
var fs = new FileStream(Path.Combine(TemplateDirPath, string.Format("{0}.xls", productCode)), FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
var templateWorkbook = new HSSFWorkbook(fs, true);
// Getting the worksheet by its name...
var sheet = (HSSFSheet)templateWorkbook.GetSheet(SheetName);
if (sheet == null) throw new NullReferenceException(string.Format("Excel sheet was not found! The sheet name: {0}.", SheetName));
//fill all data to excel template
FillSheetWithData(templateWorkbook, sheet, data);
// Forcing formula recalculation...
sheet.ForceFormulaRecalculation = true;
MemoryStream ms = new MemoryStream();
// Writing the workbook content to the FileStream...
templateWorkbook.Write(ms);
//NOTE: for testing purpose
string saveAllowed = System.Configuration.ConfigurationManager.AppSettings["SaveGeneratedFile"];
if (!string.IsNullOrEmpty(saveAllowed) &&
(saveAllowed.Equals("1") || saveAllowed.ToUpper().Equals("YES") || saveAllowed.ToUpper().Equals("TRUE")))
{
if (File.Exists(TestFile)) File.Delete(TestFile);
using (var fileStream = File.Create(TestFile))
{
ms.WriteTo(fileStream);
//templateWorkbook.Write(fileStream);
}
}
// Sending the server processed data back to the user computer...
return ms;
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Generating failed! Unexpected exception: {0}", ex.Message),
"Generating failed!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
private void FillSheetWithData(HSSFWorkbook workbook, HSSFSheet sheet, Items data)
{
//fill date of generating
SetValue(sheet, _settings.CreateDate, DateTime.Today);
//fill order dates and delivery dates for each order
FillOrdersDates(sheet, data.Orders);
//fill data cells
for (var i = 0; i < data.Rows.Count; i++)
{
var dataRow = data.Rows[i];
var row = CopyRow(workbook, sheet, _settings.FirstRow, _settings.FirstRow + i + 1);
//fill data from data contract first
SetRowStringValue(row, _settings.Values.Code, dataRow.Code);
SetRowStringValue(row, _settings.Values.Name, dataRow.Name);
SetRowNumericValue(row, _settings.Values.KgPerUnit, dataRow.KgPerUnit);
SetRowNumericValue(row, _settings.Values.Usage2010, dataRow.Usages.ContainsKey("2010") ? dataRow.Usages["2010"] : 0);
SetRowNumericValue(row, _settings.Values.Usage2011, dataRow.Usages.ContainsKey("2011") ? dataRow.Usages["2011"] : 0);
SetRowNumericValue(row, _settings.Values.OnStock, dataRow.OnStock);
for (var j = 0; j < _settings.Values.Ordered.Count; j++)
{
if (j == _settings.Orders.Count) throw new IndexOutOfRangeException("Orders count is bigger then template can process");
if (j >= data.Orders.Count)
{
SetRowNumericValue(row, _settings.Values.Ordered[j], 0);
continue;
}
var order = dataRow.Ordered.SingleOrDefault(o => o.Id == data.Orders[j].Id);
SetRowNumericValue(row, _settings.Values.Ordered[j], order != null ? order.Value : 0);
}
//fill formulas to other specific fields
foreach (var formula in _settings.Formulas)
SetRowFormula(row, formula.Cell, string.Format(formula.Value, _settings.FirstRow + i + 2));
}
//set total order sum at the end of the document
SetRowFormula((HSSFRow)sheet.GetRow(_settings.FirstRow + data.Rows.Count + 3),
_settings.OrderedSum.Cell,
string.Format(_settings.OrderedSum.Value, _settings.FirstRow + 2, _settings.FirstRow + data.Rows.Count + 1));
}
public byte[] GenerateReportContent(string productCode, Items data)
{
MemoryStream str = (MemoryStream)this.GenerateReport(productCode, data);
return str.ToArray();
}
public override Items GetStatisticData()
{
Items originalData = base.GetStatisticData();
Items data = new Items() { Orders = originalData.Orders };
List<ItemData> processed = new List<ItemData>();
foreach (ItemData item in originalData.Rows)
{
if (!string.IsNullOrWhiteSpace(item.Code) && item.Code.StartsWith(CODE_601))
{
processed.Add(item);
data.Rows.Add(item);
string[] parts = item.Code.Split(' ');
if (parts.Length > 1)
{
string common = parts[1];
IEnumerable<ItemData> partialData = originalData.Rows.Where(it => it.Code.Contains(common) &&
!it.Code.StartsWith(CODE_601));
foreach (ItemData partial in partialData)
{
processed.Add(partial);
item.OnStock += partial.OnStock;
//item.Ordered += partial.Ordered;
foreach (string year in partial.Usages.Keys)
{
if (item.Usages.ContainsKey(year))
item.Usages[year] += partial.Usages[year];
else
item.Usages.Add(year, partial.Usages[year]);
}
foreach (OrderedItem order in partial.Ordered)
{
OrderedItem old = item.Ordered.FirstOrDefault(ord=>ord.Id == order.Id);
if (old == null)
{
old = new OrderedItem() { Code = order.Code, Id = order.Id };
item.Ordered.Add(old);
}
old.Value += order.Value;
}
}
}
}
}
// Este pridame tie, ktore sa doteraz nespracovali, bude to zobrazene na konci statistiky
IEnumerable<ItemData> unprocessed = originalData.Rows.Where(he => !processed.Contains(he));
foreach(ItemData item in unprocessed)
data.Rows.Add(item);
return data;
}