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(); }