public static WeekOrderDto GetMapDto(IUnitOfWorkAsync unitOfWork, WeekYearDto wyDto, bool needfact = true)
{
ApplicationDbContext context = unitOfWork.GetContext();
MenuForWeek menuForWeek = unitOfWork.RepositoryAsync <MenuForWeek>().GetWeekMenuByWeekYear(wyDto);
if (menuForWeek == null)
{
return(null);
}
List <WeekOrderMenu> weekOrderMenus = unitOfWork.RepositoryAsync <WeekOrderMenu>()
.OrdersMenuByWeekYear(wyDto).Where(word => word.User.IsExisting).OrderBy(wo => wo.User.LastName).ToList();
return(new WeekOrderDto
{
WeekYearDto = wyDto,
SuCanChangeOrder = menuForWeek.SUCanChangeOrder,
UserWeekOrders =
weekOrderMenus.Select(woDto => UserWeekOrderDto.MapDto(context, woDto)).ToList(),
DayNames = context.GetDayNames(wyDto, true).Result,
WeekDishPrices = context.GetWeekDishPrices(wyDto).Result,
SummaryDishQuantities = context.GetFactSumWeekUserCounts(wyDto).Result,
AllDayNames = context.GetDayNames(wyDto).Result
});
}
public static string GetFactOrdersExcelFileWeekYearDto(this IRepositoryAsync <WeekOrderMenu> repository,
ForExcelDataDto feDto)
{
string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
List <WeekOrderMenu> weekOrderMenus = repository.OrdersMenuByWeekYear(feDto.WeekYear);
List <UserWeekOrderDto> userWeekOrders =
weekOrderMenus.Select(woDto => UserWeekOrderDto.MapDto(repository.Context, woDto)).ToList();
string[] dayNames = repository.Context.GetDayNames(feDto.WeekYear, true).Result;
double[] weekDishPrices = repository.Context.GetWeekDishPrices(feDto.WeekYear).Result;
double[] summaryDishQuantities = repository.Context.GetFactSumWeekUserCounts(feDto.WeekYear).Result;
WorkingWeek workWeek = repository.GetRepositoryAsync <MenuForWeek>().WorkWeekByWeekYear(feDto.WeekYear);
int workDayCount = workWeek.WorkingDays.Count(wd => wd.IsWorking);
int catLength = repository.GetRepositoryAsync <DishType>().GetAll().Count;
int dishcount = workDayCount * catLength;
int orderscount = userWeekOrders.Count;
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
//document.Workbook.Worksheets.DeleteAll();
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.Add("Заявки фактические");
string titlerang = String.Format("A1:{0}1", GetExcelColumnName(dishcount + 3));
Range range = worksheet.Range(titlerang);
range.Merge();
worksheet.Cell("A1").MergedWithCell.Value = "Заявки фактические " + feDto.DataString;
range.AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Cell(2, 0).Value = "№";
worksheet.Range("A2:A5").Merge();
worksheet.Cell(2, 1).Value = "Ф.И.О.";
worksheet.Range("B2:B5").Merge();
worksheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Centered;
string str;
string colname;
string colname_2;
string endcolname = GetExcelColumnName(dishcount + 3);
string allstr = string.Format("A{0}:{2}{1}", 1, userWeekOrders.Count + 6, endcolname);
colname = GetExcelColumnName(dishcount + 2);
worksheet.Cell(2 + 1, 3).Value = "Цена за одну порцию, грн";
str = String.Format("C4:{0}4", colname);
worksheet.Range(str).Merge();
worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;
int i = dishcount + 3;
colname = GetExcelColumnName(i);
worksheet.Cell(1, i - 1).Value = "Стоимость заказа за неделю";
str = String.Format("{0}2:{1}5", colname, colname);
worksheet.Range(str).Merge();
worksheet.Range(str).Wrap = true;
worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Columns[i - 1].Width = 100;
worksheet.Cell(1, i - 1).ShrinkToFit = true;
i = userWeekOrders.Count + 5;
worksheet.Cell(i, 0).Value = "Всего заказано";
str = String.Format("A{0}:B{1}", i + 1, i + 1);
worksheet.Range("A2:B5").AlignmentHorizontal = AlignmentHorizontal.Right;
worksheet.Range(str).Merge();
worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;
for (int j = 0; j < dishcount; j++)
{
worksheet.Cell(4, 2 + j).Value = weekDishPrices[j];
if ((j) % 4 != 0)
{
worksheet.Cell(4, 2 + j).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(4, 2 + j).RightBorderStyle = LineStyle.Thin;
}
i = 0;
for (int[] j = { 0 }; j[0] < workDayCount; j[0]++)
{
colname = GetExcelColumnName(j[0] * catLength + 3);
colname_2 = GetExcelColumnName(j[0] * catLength + 6);
var elementAtOrDefault = workWeek.WorkingDays.Where(wd => wd.IsWorking).ElementAtOrDefault(j[0]);
if (elementAtOrDefault != null)
{
worksheet.Cell(1, j[0] * catLength + 3).Value = elementAtOrDefault.DayOfWeek.Name;
}
str = String.Format("{0}2:{1}2", colname, colname_2);
worksheet.Range(str).Merge();
}
i = 2;
for (int j = 0; j < workDayCount; j++)
{
for (int k = 0; k < catLength; k++)
{
colname = GetExcelColumnName(2 + 1 + j * catLength + k);
worksheet.Cell(2, 2 + j * catLength + k).Value = dishCategories[k];
worksheet.Range(colname + "3").Rotation = 90;
if ((k) % 4 != 0)
{
worksheet.Cell(2, 2 + j * catLength + k).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(2, 2 + j * catLength + k).RightBorderStyle = LineStyle.Thin;
}
}
str = string.Format("A1:{0}5", GetExcelColumnName(dishcount + 3));
i = 5;
Color contentColor = Color.FromArgb(224, 232, 241);
Color nullColor = Color.FromArgb(6, 84, 156);
for (int j = 0; j < userWeekOrders.Count; j++)
{
var itsevenrow = (i + j) % 2 != 0;
UserWeekOrderDto userweekorder = userWeekOrders[j];
worksheet.Cell(i + j, 0).Value = j + 1;
worksheet.Cell(i + j, 1).Value = userweekorder.UserName;
if (itsevenrow)
{
worksheet.Cell(i + j, 0).FillPattern = PatternStyle.Solid;
worksheet.Cell(i + j, 0).FillPatternForeColor = contentColor;
worksheet.Cell(i + j, 1).FillPattern = PatternStyle.Solid;
worksheet.Cell(i + j, 1).FillPatternForeColor = contentColor;
}
for (int k = 0; k < dishcount + 1; k++)
{
var celval = userweekorder.UserWeekOrderDishes[k];
if (celval != 0.00)
{
worksheet.Cell(i + j, k + 2).Value = celval;
}
if (itsevenrow)
{
worksheet.Cell(i + j, k + 2).FillPattern = PatternStyle.Solid;
worksheet.Cell(i + j, k + 2).FillPatternForeColor = contentColor;
}
if ((k) % 4 != 0)
{
worksheet.Cell(i + j, k + 2).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(i + j, k + 2).RightBorderStyle = LineStyle.Thin;
}
}
i = userWeekOrders.Count + 5;
Color evcolor = Color.FromArgb(68, 240, 196);
for (int j = 0; j < workDayCount; j++)
{
for (int k = 0; k < catLength; k++)
{
Cell curCell = worksheet.Cell(i, j * catLength + k + 2);
curCell.Value = summaryDishQuantities[j * catLength + k];
if (j % 2 == 0)
{
curCell.FillPattern = PatternStyle.Solid;
curCell.FillPatternForeColor = evcolor;
}
if ((k) % 4 != 0)
{
worksheet.Cell(i, j * catLength + k + 2).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(i, j * catLength + k + 2).RightBorderStyle = LineStyle.Thin;
}
}
worksheet.Cell(i, dishcount + 2).Value = userWeekOrders.Sum(uo => uo.UserWeekOrderDishes[dishcount]);
string headerstr = string.Format("C{0}:{2}{1}", 1, 3, endcolname);
worksheet.Range(headerstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
string headerusnamesstr = string.Format("A{0}:B{1}", 1, 5);
worksheet.Range(headerusnamesstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Range(headerusnamesstr).AlignmentVertical = AlignmentVertical.Centered;
string usernames = string.Format("A{0}:B{1}", 6, userWeekOrders.Count + 5);
worksheet.Range(usernames).AlignmentHorizontal = AlignmentHorizontal.Left;
string userquantistr = string.Format("C{0}:{2}{1}", 5, userWeekOrders.Count + 6, endcolname);
//worksheet.Range(userquantistr).NumberFormatString = "#.#";
worksheet.Range(userquantistr).AlignmentHorizontal = AlignmentHorizontal.Centered;
string sumcol = string.Format("{0}{1}:{2}{3}", endcolname, 5, endcolname, userWeekOrders.Count + 6);
worksheet.Range(sumcol).NumberFormatString = "#,##0.00";
worksheet.Range(sumcol).AlignmentHorizontal = AlignmentHorizontal.Centered;
for (int j = 0; j < userWeekOrders.Count + 6; j++)
{
worksheet.Rows[j].Height = (uint)((j != 2) ? 35 : 150);
worksheet.Rows[j].AlignmentVertical = AlignmentVertical.Centered;
}
worksheet.Range(allstr).Font = new Font("Arial", 13, FontStyle.Bold);
worksheet.Columns[0].Width = 40;
worksheet.Columns[1].Width = 250;
//worksheet.Columns[1].AutoFit();
//string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
// @"ACSDining.Web\ExcelFiles\ЗаявкиФакт.xls";
string pathstr = string.Format("~/ExcelFiles/ЗаявкиФакт_{0}.xls", YearWeekHelp.GetWeekTitle(repository.GetRepositoryAsync <MenuForWeek>(), feDto.WeekYear));
string _path = HostingEnvironment.MapPath(pathstr);
if (File.Exists(_path))
{
File.Delete(_path);
}
document.SaveAs(_path);
// Close document
document.Close();
return(_path);
}
