public static void MonthCashReportMinsk()
{
using (var bc = new BusinessContext())
{
var minDate = bc.GetParameterValue <DateTime?>(ParameterType.MinDateCostChange);
var maxDate = bc.GetParameterValue <DateTime?>(ParameterType.MaxDateCostChange);
if (!minDate.HasValue || !maxDate.HasValue)
{
return;
}
DateTime prevDate = DateTime.MinValue;
DirectoryRCPercentage[] rcPercentages = null;
for (DateTime date = minDate.Value.Date; date <= maxDate.Value.Date; date = date.AddDays(1))
{
using (var ep = new ExcelPackage())
{
var name = "Итого";
if (!ep.Workbook.Worksheets.Select(ws => ws.Name).Contains(name))
{
ep.Workbook.Worksheets.Add(name);
}
var sheet = ep.Workbook.Worksheets.First(ws => ws.Name == name);
var colorTransparent = Color.Transparent;
if (sheet.Cells[1, 1].Value == null)
{
Helpers.CreateCell(sheet, 1, 1, "ЦО", null, 12, true, ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, 1, 2, "Валюта", null, 12, true, ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, 1, 3, "Приход", null, 12, true, ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, 1, 4, "Расход", null, 12, true, ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
}
sheet.Cells[1, 1, 1, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
int indexRow = 3;
var costs = bc.GetInfoCosts(date).ToList();
if (!costs.Any())
{
continue;
}
var totalSums = new Dictionary <Currency, Balance>();
var currencies =
Enum.GetNames(typeof(Currency)).Select(x => (Currency)Enum.Parse(typeof(Currency), x)).ToArray();
foreach (var currency in currencies)
{
totalSums[currency] = new Balance();
}
if (prevDate.Month != date.Month)
{
rcPercentages = bc.GetRCPercentages(date.Year, date.Month)
.OrderByDescending(x => x.Percentage).ToArray();
}
prevDate = date;
var distinctedRCs = costs.Select(c => c.DirectoryRC).Distinct().ToArray();
var sortedRCs = new List <DirectoryRC>();
foreach (var rcPercentage in rcPercentages)
{
var rc = distinctedRCs.FirstOrDefault(x => x.Id == rcPercentage.DirectoryRCId);
if (rc != null)
{
sortedRCs.Add(rc);
}
}
foreach (var rc in sortedRCs)
{
var costsRcCurrencies = costs.Where(c => c.DirectoryRC.Id == rc.Id).GroupBy(x => x.Currency);
bool isFirst = false;
int countCurrenciesPerRC = 0;
foreach (var costsCurrency in costsRcCurrencies)
{
countCurrenciesPerRC++;
double summIncoming = 0;
double summExpence = 0;
foreach (var cost in costsCurrency)
{
summIncoming += cost.IsIncoming ? cost.Summ : 0;
summExpence += !cost.IsIncoming ? cost.Summ : 0;
}
totalSums[costsCurrency.Key].Expence += summExpence;
totalSums[costsCurrency.Key].Incoming += summIncoming;
if (!isFirst)
{
isFirst = true;
Helpers.CreateCell(sheet, indexRow, 1, rc.Name, null, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
}
Helpers.CreateCell(sheet, indexRow, 2, costsCurrency.Key.ToString(), null, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 3, summIncoming.ToString("N2"), null, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 4, summExpence.ToString("N2"), null, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
indexRow++;
}
sheet.Cells[indexRow - countCurrenciesPerRC, 1, indexRow - 1, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
indexRow++;
}
bool isFirstTotal = false;
int countTotals = 0;
foreach (var totals in totalSums.Where(x => x.Value.Expence > 0 || x.Value.Incoming > 0))
{
countTotals++;
if (!isFirstTotal)
{
isFirstTotal = true;
Helpers.CreateCell(sheet, indexRow, 1, "Итого", null, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
}
Helpers.CreateCell(sheet, indexRow, 2, totals.Key.ToString(), null, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 3, totals.Value.Incoming.ToString("N2"), null, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 4, totals.Value.Expence.ToString("N2"), null, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
indexRow++;
}
sheet.Cells[indexRow - countTotals, 1, indexRow - 1, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
indexRow += 2;
DateTime startDate = date;
int counter = 0;
double?minskSum = null;
while (!minskSum.HasValue)
{
counter++;
minskSum = bc.GetTotalEqualCashSafeToMinsks(startDate);
if (!minskSum.HasValue)
{
startDate = startDate.AddMonths(-1);
}
if (counter > 12)
{
break;
}
}
if (minskSum.HasValue)
{
var costsToPeriod = bc.GetInfoCosts(new DateTime(startDate.Year, startDate.Month, 1), date).ToArray();
var costsSum = costsToPeriod
.Where(x => x.Currency == Currency.RUR)
.Sum(x => x.IsIncoming ? x.Summ : -x.Summ);
Helpers.CreateCell(sheet, indexRow, 1, indexRow, 2, "Итого касса на " + date.ToShortDateString(), null, 12, true,
ExcelHorizontalAlignment.Left, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 3, (minskSum + costsSum) + " RUR", null, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
indexRow++;
foreach (
var totals in totalSums.Where(x => x.Key != Currency.RUR && (x.Value.Expence > 0 || x.Value.Incoming > 0)))
{
double sum = costsToPeriod.Where(x => x.Currency == totals.Key)
.Sum(x => x.IsIncoming ? x.Summ : -x.Summ);
if (sum == 0)
{
continue;
}
Helpers.CreateCell(sheet, indexRow, 3, sum + " " + totals.Key, null, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
indexRow++;
}
}
sheet.Column(1).Width = Helpers.PixelsToInches(200);
sheet.Column(2).Width = Helpers.PixelsToInches(200);
sheet.Column(3).Width = Helpers.PixelsToInches(200);
sheet.Column(4).Width = Helpers.PixelsToInches(200);
name = "Касса за " + date.ToShortDateString();
sheet = Helpers.GetSheet(ep, name);
Helpers.CreateCell(sheet, 1, 1, "Статья затрат", colorTransparent, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.Thick);
Helpers.CreateCell(sheet, 1, 2, "ЦО", colorTransparent, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.Thick);
Helpers.CreateCell(sheet, 1, 3, "Приход", colorTransparent, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.Thick);
Helpers.CreateCell(sheet, 1, 4, "Расход", colorTransparent, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.Thick);
Helpers.CreateCell(sheet, 1, 5, "Описание", colorTransparent, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.Thick);
indexRow = 2;
var colorGray = Color.LightGray;
double maxLengthNote = 0;
foreach (var rc in sortedRCs)
{
double summIncoming = 0;
double summExpence = 0;
int firstIndexRow = indexRow;
foreach (var cost in costs.Where(c => c.DirectoryRC.Id == rc.Id).OrderBy(c => c.Date))
{
Helpers.CreateCell(sheet, indexRow, 1, cost.DirectoryCostItem.Name, colorTransparent, 11, false,
ExcelHorizontalAlignment.Left, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 2, cost.DirectoryRC.Name, colorTransparent, 11, false,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 3, cost.IsIncoming ? cost.Incoming : null, colorTransparent, 11, false,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 4, !cost.IsIncoming ? cost.Expense : null, colorTransparent, 11, false,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 5, cost.ConcatNotes, colorTransparent, 11, false,
ExcelHorizontalAlignment.Left, ExcelBorderStyle.None);
FormattedText formattedText = new FormattedText(cost.ConcatNotes, CultureInfo.CurrentCulture,
FlowDirection.LeftToRight,
new Typeface("Courier New"), 11, Brushes.Black);
if (maxLengthNote < formattedText.Width)
{
maxLengthNote = formattedText.Width;
}
summIncoming += cost.IsIncoming ? cost.Summ : 0;
summExpence += !cost.IsIncoming ? cost.Summ : 0;
indexRow++;
}
Helpers.CreateCell(sheet, indexRow, 1, "Итого", colorGray, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 2, null, colorGray, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 3, summIncoming.ToString("c"), colorGray, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 4, summExpence.ToString("c"), colorGray, 12, true,
ExcelHorizontalAlignment.Center, ExcelBorderStyle.None);
Helpers.CreateCell(sheet, indexRow, 5, null, colorGray, 12, true, ExcelHorizontalAlignment.Center,
ExcelBorderStyle.None);
sheet.Cells[firstIndexRow, 1, indexRow - 1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thick);
sheet.Cells[firstIndexRow, 2, indexRow - 1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thick);
sheet.Cells[firstIndexRow, 3, indexRow - 1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thick);
sheet.Cells[firstIndexRow, 4, indexRow - 1, 4].Style.Border.BorderAround(ExcelBorderStyle.Thick);
sheet.Cells[firstIndexRow, 5, indexRow - 1, 5].Style.Border.BorderAround(ExcelBorderStyle.Thick);
indexRow++;
}
sheet.Column(1).Width = Helpers.PixelsToInches(100);
sheet.Column(2).Width = Helpers.PixelsToInches(150);
sheet.Column(3).Width = Helpers.PixelsToInches(100);
sheet.Column(4).Width = Helpers.PixelsToInches(100);
sheet.Column(5).Width = Helpers.PixelsToInches(maxLengthNote * 1.5);
string pathDirectoryMonth = date.ToString("MM.yyyy");
string fileName = Path.Combine(_pathCashReport, pathDirectoryMonth, "Касса за " + date.ToString("dd.MM.yyyy HH-mm-ss") + ".xlsx");
if (!Directory.Exists(Path.GetDirectoryName(fileName)))
{
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
}
ep.SaveAs(new FileInfo(fileName));
}
}
bc.EditParameter <DateTime?>(ParameterType.MinDateCostChange, null);
bc.EditParameter <DateTime?>(ParameterType.MaxDateCostChange, null);
}
}
private static void AddingWorkers(BusinessContext bc)
{
var doc = XDocument.Load(PATH_TABEL_WORKERS);
var worksheets = doc.Root.Elements("{urn:schemas-microsoft-com:office:spreadsheet}Worksheet").ToList();
var lastDate = DateTime.MinValue;
foreach (var worksheet in worksheets.Where(w => w.Attribute("{urn:schemas-microsoft-com:office:spreadsheet}Name").Value != "Шаблон"))
{
string[] date = worksheet.Attribute("{urn:schemas-microsoft-com:office:spreadsheet}Name").Value.Split('.');
int month = int.Parse(date[0]);
int year = int.Parse("20" + date[1]);
//if (year != 2013 || year == 2013 && month != 11)
//{
// continue;
//}
//if (year != 2014)
//{
// continue;
//}
Debug.WriteLine(year + " " + month);
var workers = new List <DirectoryWorker>();
var rows = worksheet.Elements("{urn:schemas-microsoft-com:office:spreadsheet}Table").Elements("{urn:schemas-microsoft-com:office:spreadsheet}Row").ToList();
for (int row = 3; row < rows.Count; row += 2)
{
var cellRegularDays = worksheet.Element("{urn:schemas-microsoft-com:office:spreadsheet}Table").Elements("{urn:schemas-microsoft-com:office:spreadsheet}Row").ToList()[row].Elements("{urn:schemas-microsoft-com:office:spreadsheet}Cell").ToList();
var cellOverTimeDays = worksheet.Element("{urn:schemas-microsoft-com:office:spreadsheet}Table").Elements("{urn:schemas-microsoft-com:office:spreadsheet}Row").ToList()[row + 1].Elements("{urn:schemas-microsoft-com:office:spreadsheet}Cell").ToList();
string[] workerFio = cellRegularDays[1].Value.Split(' ');
string lastName = workerFio[0].Trim();
string firstName = workerFio[1].Trim();
string midName = "";
if (workerFio.Count() == 3)
{
midName = workerFio[2].Trim();
}
int indexPostName = 0;
string postName = "";
for (int i = 0; i < cellOverTimeDays.Count; i++)
{
postName = cellOverTimeDays[i].Value.ToLower();
switch (postName)
{
case "заведующий складом":
case "зав. складом":
postName = "ЗавСкладом";
break;
case "зам. зав. складом":
postName = "ЗамЗавСкладом";
break;
case "карщик-кладовщик":
postName = "КарщикКладовщик";
break;
case "оклейщица":
postName = "Оклейщик";
break;
case "бригадир-оклейщик":
postName = "БригадирОклейщик";
break;
}
if (postName != "")
{
postName = postName[0].ToString().ToUpper() + postName.Substring(1);
if (bc.ExistsDirectoryPost(postName))
{
indexPostName = i;
break;
}
}
}
Debug.WriteLine(lastName + " " + firstName + " " + midName + " : " + postName);
var worker = bc.GetDirectoryWorker(lastName, firstName);
int day = 0;
if (worker == null)
{
day = 1;
if (cellRegularDays[3].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data") == null || cellRegularDays[3].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value == "" || cellRegularDays[3].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value == "0")
{
for (int i = 4; i < cellRegularDays.Count; i++)
{
if (cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data") != null &&
(cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value != "" &&
cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value != "0"))
{
day = i - 2;
break;
}
if (cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data") == null ||
cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value == "")
{
bool isBreak = false;
for (int j = i + 1; j < cellRegularDays.Count; j++)
{
if (cellRegularDays[j].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data") != null && (
cellRegularDays[j].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value != "" &&
cellRegularDays[j].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value != "0"))
{
day = j - 2;
isBreak = true;
break;
}
}
if (isBreak)
{
break;
}
}
}
}
var currentPost = new CurrentPost
{
ChangeDate = new DateTime(year, month, day),
DirectoryPost = bc.GetDirectoryPost(postName),
};
worker = bc.AddDirectoryWorker(lastName, firstName, midName, Gender.Male, DateTime.Now, "1", "1", "1", new DateTime(year, month, day), null, currentPost);
day--;
}
var infoMonth = new InfoMonth
{
Date = new DateTime(year, month, 1),
BirthDays = 500
};
worker.InfoMonthes.Add(infoMonth);
int overTimeCounter = 0;
bool isIndexOverTime = false;
int countDaysInMonth = DateTime.DaysInMonth(year, month);
for (int i = 3 + day; i < countDaysInMonth + 3; i++)
{
var workerDate = new DateTime(year, month, day + 1);
if (worker.FireDate != null)
{
break;
}
var infoDate = new InfoDate
{
Date = workerDate,
DescriptionDay = DescriptionDay.Был
};
if (cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data") != null)
{
string value = cellRegularDays[i].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value;
if (value != "")
{
double result;
if (double.TryParse(value, out result))
{
infoDate.CountHours = result;
}
if (!isIndexOverTime)
{
if (cellOverTimeDays[i + indexPostName - 1 - overTimeCounter].Attribute("{urn:schemas-microsoft-com:office:spreadsheet}Index") != null)
{
overTimeCounter++;
isIndexOverTime = true;
}
}
else
{
isIndexOverTime = false;
}
if (!isIndexOverTime && cellOverTimeDays[i + indexPostName - 1 - overTimeCounter].Value != "")
{
if (double.TryParse(cellOverTimeDays[i + indexPostName - 1 - overTimeCounter].Element("{urn:schemas-microsoft-com:office:spreadsheet}Data").Value.Replace(".", ","), out result))
{
infoDate.CountHours += result;
}
}
if (infoDate.CountHours == 0)
{
if (!bc.IsWeekend(workerDate))
{
infoDate.DescriptionDay = DescriptionDay.С;
}
infoDate.CountHours = null;
}
worker.InfoDates.Add(infoDate);
}
}
else
{
if (!bc.IsWeekend(workerDate))
{
infoDate.DescriptionDay = DescriptionDay.С;
}
infoDate.CountHours = null;
}
var workerFire = _workersFire.FirstOrDefault(w => w.FirstName == worker.FirstName && w.LastName == worker.LastName);
if (workerFire != null)
{
if (workerDate.Date == workerFire.FireDate.Date)
{
worker.FireDate = workerFire.FireDate;
_workersFire.Remove(workerFire);
}
}
day++;
if (lastDate.Date < workerDate.Date)
{
lastDate = workerDate;
}
}
string fio = "";
string[] fioMass = null;
do
{
if (rows.Count() <= row + 2)
{
break;
}
var cells3 = worksheet.Element("{urn:schemas-microsoft-com:office:spreadsheet}Table").Elements("{urn:schemas-microsoft-com:office:spreadsheet}Row").ToList()[row + 2].Elements("{urn:schemas-microsoft-com:office:spreadsheet}Cell").ToList();
if (cells3.Count() > 1)
{
fio = cells3[1].Value;
fioMass = fio.Split(' ');
}
else
{
fio = "";
fioMass = null;
}
if (fio == "" || bc.ExistsDirectoryPost(fio) || fioMass == null || fioMass.Count() < 2)
{
row++;
}
} while (fio == "" || bc.ExistsDirectoryPost(fio) || fioMass == null || fioMass.Count() < 2);
workers.Add(worker);
}
bc.SaveChanges();
}
bc.EditParameter(ParameterType.LastDate, lastDate.ToString());
}
