public void ReportForecast(string filePath, bool appendFile, SqlString customerNumberFrom, SqlString customerNumberTo, SqlString brandCodeFrom, SqlString brandCodeTo, SqlString productCategoryCodeFrom, SqlString productCategoryCodeTo, ForecastReportGroupBy groupBy, string forecastMethods, int forecastYear)
{
var forecastReportCollection = new ForecastReport.ParameteredCollection(_companyCode, customerNumberFrom, customerNumberTo, brandCodeFrom, brandCodeTo, productCategoryCodeFrom, productCategoryCodeTo, groupBy, forecastMethods, forecastYear);
forecastReportCollection.Load();
var actualSalesReportCollection = new ActualSalesReport.ParameteredCollection(_companyCode, customerNumberFrom, customerNumberTo, brandCodeFrom, brandCodeTo, productCategoryCodeFrom, productCategoryCodeTo, groupBy, forecastMethods, forecastYear);
actualSalesReportCollection.Load();
using (var sw = new StreamWriter(filePath, appendFile))
{
sw.WriteLine(",,,,Actual vs Forecast Comparison Report,,,,,,,Report Date: {0:MM/dd/yyyy}", DateTime.Today);
sw.WriteLine();
sw.WriteLine("Company Code: {0}", _companyCode);
sw.WriteLine("Customer Number from: {0} to: {1}", customerNumberFrom, customerNumberTo);
sw.WriteLine("Brand Code from: {0} to: {1}", brandCodeFrom, brandCodeTo);
sw.WriteLine("Product Category Code from: {0} to: {1}", productCategoryCodeFrom, productCategoryCodeTo);
sw.WriteLine("Forecast Methods: {0}", forecastMethods);
sw.WriteLine("Forecast Year: {0}", forecastYear);
sw.WriteLine("Group by: {0}", groupBy);
sw.WriteLine();
var header = new StringBuilder();
if (groupBy == ForecastReportGroupBy.Brand)
{
header.Append("Brand");
}
else
{
header.Append("Item#");
}
header.Append(",").Append("Jan ").Append(forecastYear.ToString());
header.Append(",").Append("Feb ").Append(forecastYear.ToString());
header.Append(",").Append("Mar ").Append(forecastYear.ToString());
header.Append(",").Append("Apr ").Append(forecastYear.ToString());
header.Append(",").Append("May ").Append(forecastYear.ToString());
header.Append(",").Append("Jun ").Append(forecastYear.ToString());
header.Append(",").Append("Jul ").Append(forecastYear.ToString());
header.Append(",").Append("Aug ").Append(forecastYear.ToString());
header.Append(",").Append("Sep ").Append(forecastYear.ToString());
header.Append(",").Append("Oct ").Append(forecastYear.ToString());
header.Append(",").Append("Nov ").Append(forecastYear.ToString());
header.Append(",").Append("Dec ").Append(forecastYear.ToString());
header.Append(",Total ").Append(forecastYear.ToString());
sw.WriteLine(header.ToString());
sw.WriteLine();
var forecastReportGrandTotal = new int[13];
var actualSalesReportGrandTotal = new int[13];
var varianceReportGrandTotal = new int[13];
foreach (var forecastReport in forecastReportCollection)
{
WriteForecastVarianceCrossTab(sw, forecastReport, actualSalesReportCollection, forecastYear, ref forecastReportGrandTotal, ref actualSalesReportGrandTotal, ref varianceReportGrandTotal);
}
sw.WriteLine();
sw.WriteLine("Report Total:");
sw.Write("Actual");
for (var i = 0; i < 13; i++)
{
sw.Write(",{0}", actualSalesReportGrandTotal[i]);
}
sw.WriteLine();
sw.Write("SLSFR");
for (var i = 0; i < 13; i++)
{
sw.Write(",{0}", forecastReportGrandTotal[i]);
}
sw.WriteLine();
sw.Write("Variance");
for (var i = 0; i < 13; i++)
{
sw.Write(",{0}", varianceReportGrandTotal[i]);
}
sw.WriteLine();
}
}
private void WriteForecastVarianceCrossTab(StreamWriter sw, ForecastReport forecastReport, ActualSalesReport.ParameteredCollection actualSalesReportCollection, int forecastYear, ref int[] forecastReportGrandTotal, ref int[] actualSalesReportGrandTotal, ref int[] varianceReportGrandTotal)
{
if (forecastReport.GroupBy == ForecastReportGroupBy.Brand.ToString())
{
var brand = new Brand(forecastReport.GroupByCode);
brand.Load();
sw.WriteLine("{0} - {1}", forecastReport.GroupByCode, brand.BrandDescription);
}
else
{
sw.WriteLine(forecastReport.GroupByCode);
}
var varianceReportCollection = new int[12];
var forecastReportTotal = 0;
var actualSalesReportTotal = 0;
var varianceReportTotal = 0;
sw.Write("Actual");
for (var i = 0; i < 12; i++)
{
int actualSalesReportQuantity;
var actualSalesReport = actualSalesReportCollection[new ActualSalesReportKey(_companyCode, forecastReport.GroupByCode, new SqlDateTime(forecastYear, i + 1, 1))];
if (actualSalesReport != null && actualSalesReport.Quantity > SqlInt32.Zero)
{
actualSalesReportQuantity = actualSalesReport.Quantity.Value;
}
else
{
actualSalesReportQuantity = 0;
}
varianceReportCollection[i] = forecastReport.ForcastReportQuantityCollection[i].Value - actualSalesReportQuantity;
forecastReportTotal += forecastReport.ForcastReportQuantityCollection[i].IsNull ? 0 : forecastReport.ForcastReportQuantityCollection[i].Value;
actualSalesReportTotal += actualSalesReportQuantity;
varianceReportTotal += varianceReportCollection[i];
forecastReportGrandTotal[i] += forecastReport.ForcastReportQuantityCollection[i].IsNull ? 0 : forecastReport.ForcastReportQuantityCollection[i].Value;
actualSalesReportGrandTotal[i] += actualSalesReportQuantity;
varianceReportGrandTotal[i] += varianceReportCollection[i];
sw.Write(",{0}", actualSalesReportQuantity);
}
forecastReportGrandTotal[12] += forecastReportTotal;
actualSalesReportGrandTotal[12] += actualSalesReportTotal;
varianceReportGrandTotal[12] += varianceReportTotal;
sw.WriteLine(",{0}", actualSalesReportTotal);
sw.Write("SLSFR,{0},{1},{2},{3},{4},{5},", forecastReport.ForecastMonth01Quantity, forecastReport.ForecastMonth02Quantity, forecastReport.ForecastMonth03Quantity, forecastReport.ForecastMonth04Quantity, forecastReport.ForecastMonth05Quantity, forecastReport.ForecastMonth06Quantity);
sw.WriteLine("{0},{1},{2},{3},{4},{5},{6}", forecastReport.ForecastMonth07Quantity, forecastReport.ForecastMonth08Quantity, forecastReport.ForecastMonth09Quantity, forecastReport.ForecastMonth10Quantity, forecastReport.ForecastMonth11Quantity, forecastReport.ForecastMonth12Quantity, forecastReportTotal);
sw.Write("Variance");
for (var i = 0; i < 12; i++)
{
sw.Write(",{0}", varianceReportCollection[i]);
}
sw.WriteLine(",{0}", varianceReportTotal);
sw.WriteLine();
}
