private void LoadMoneySourcesList()
{
using (TenderContext tc = new TenderContext())
{
sources = (from rec in tc.BalanceChanges.ToList()
where ((rec.Estimate.TenderYearId == year.Id) && (rec.DateOfReceiving <= DateTime.Now))
group rec by rec.MoneySource into g1
select new
{
Source = g1.Key, Sum = g1.Sum(p => p.PrimaryKekvSum)
} into s1
where s1.Sum > 0
orderby s1.Source.ViewPriority
select s1.Source).ToList();
sourcesNum = sources.Count;
}
}
public ActionResult DatailInformation()
{
Tender tender = new Tender();
int id = Convert.ToInt32(RouteData.Values["id"]);
ViewBag.Message = RouteData.Values["id"];
using (Models.TenderContext db = new TenderContext())
{
var tenders = from t in db.Tenders
where t.Id == id
select t;
foreach (Tender t in tenders)
{
tender = t;
}
}
return(View("Index", tender));
}
public static List <EstimateMoneyOnSourceTable> GetEstimateMoneyTable(Estimate currentEstimate,
IList <KekvCode> kekvsList,
string[] monthes,
Func <BalanceChanges, bool> filter,
Action <EstimateMoneyOnSourceTable> handler = null)
{
List <EstimateMoneyOnSourceTable> resultList = new List <EstimateMoneyOnSourceTable>();
using (TenderContext tc = new TenderContext())
{
var allEstimatesRecords = (from item in tc.BalanceChanges.ToList()
where (item.EstimateId == currentEstimate.Id) && filter(item)
group item by item.MoneySource).ToList();
foreach (var rec in allEstimatesRecords)
{
EstimateMoneyOnSourceTable record = new EstimateMoneyOnSourceTable();
record.Source = rec.Key;
record.PrimarySumValues = new decimal[kekvsList.Count, monthes.Length];
record.SecondarySumValues = new decimal[kekvsList.Count, monthes.Length];
foreach (var item in rec)
{
int rowIndex = kekvsList.IndexOf(item.PrimaryKekv);
int columnIndex = item.DateOfReceiving.Month - 1;
record.PrimarySumValues[rowIndex, columnIndex] = item.PrimaryKekvSum;
record.SecondarySumValues[rowIndex, columnIndex] = item.SecondaryKekvSum;
}
resultList.Add(record);
if (handler != null)
{
handler(record);
}
}
}
return(resultList);
}
private void LoadMoneySourcesList()
{
using (TenderContext tc = new TenderContext())
{
if (est != null)
{
sources = tc.BalanceChanges.Where(p => p.EstimateId == est.Id)
.GroupBy(p => p.MoneySource)
.Select(p => p.Key)
.OrderBy(p => p.ViewPriority)
.ToList();
}
else
{
sources = tc.BalanceChanges.Where(p => p.Estimate.TenderYearId == year.Id)
.GroupBy(p => p.MoneySource)
.Select(p => p.Key)
.OrderBy(p => p.ViewPriority)
.ToList();
}
sourcesNum = sources.Count;
}
}
protected override void WriteDataToFile()
{
List <TenderPlanItemsTableEntry> resultList = null;
List <TenderPlanRecord> allPlanRecords = null;
using (TenderContext tc = new TenderContext())
{
string estimateName = string.Empty;
if (estFilter.Id > 0)
{
estimateName = estFilter.Name;
allPlanRecords = (from r in tc.TenderPlanRecords
where r.EstimateId == estFilter.Id
select r).ToList();
}
else
{
estimateName = "Всі кошториси від початку року";
allPlanRecords = (from r in tc.TenderPlanRecords
where r.Estimate.TenderYearId == year.Id
select r).ToList();
}
xlWorksheet.get_Range(yearCell).Value = string.Format("на {0} рік", year.Year);
xlWorksheet.get_Range(estimateNameCell).Value = string.Format("Кошторис: \"{0}\"", estimateName);
xlWorksheet.get_Range(dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
if (isNewSystem)
{
resultList = (from r in allPlanRecords.ToList()
select new TenderPlanItemsTableEntry
{
Kekv = r.PrimaryKekv,
Dk = r.Dk,
MoneyOnCode = r.PlannedSum,
RelatedTenderPlanRecord = r,
Estimate = r.Estimate,
RegisteredByContracts = r.RegisteredContracts.Sum(p => p.Sum),
UsedByContracts = r.RegisteredContracts.Sum(p => p.UsedMoney),
ContractsMoneyRemain = r.RegisteredContracts.Sum(p => p.MoneyRemain)
}).ToList();
}
else
{
resultList = (from r in allPlanRecords
select new TenderPlanItemsTableEntry
{
Kekv = r.SecondaryKekv,
Dk = r.Dk,
MoneyOnCode = r.PlannedSum,
RelatedTenderPlanRecord = r,
Estimate = r.Estimate,
RegisteredByContracts = r.RegisteredContracts.Sum(p => p.Sum),
UsedByContracts = r.RegisteredContracts.Sum(p => p.UsedMoney),
ContractsMoneyRemain = r.RegisteredContracts.Sum(p => p.MoneyRemain)
}).ToList();
}
if (kekvFilter.Id > 0)
{
resultList = resultList.Where(p => p.Kekv.Id == kekvFilter.Id).ToList();
}
if (resultList.Count == 0)
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = "Записи в річному плані відсутні";
}
var groupedByKekvResult = resultList.GroupBy(p => p.Kekv);
foreach (var kekv in groupedByKekvResult.OrderBy(p => p.Key.Code))
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value =
string.Format("{0} - {1}", kekv.Key.Code, kekv.Key.Name);
kekvsHeaders.Add(currentRowNumber);
int dkCodeNum = 0;
foreach (var code in kekv)
{
currentRowNumber++;
int dkCodeIndex = currentRowNumber;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Font.Size = 11;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = (dkCodeNum + 1).ToString();
string dkCodeName = string.Format("{0} ({1})",
code.Dk.FullName,
code.RelatedTenderPlanRecord.ConcreteName);
if (!string.IsNullOrWhiteSpace(code.RelatedTenderPlanRecord.ProtocolNum))
{
dkCodeName = string.Format("{0}\n Затверджено протоколом № {1} від {2} року",
dkCodeName,
code.RelatedTenderPlanRecord.ProtocolNum,
code.RelatedTenderPlanRecord.ProtocolDate.ToShortDateString());
}
if (code.RelatedTenderPlanRecord.CodeRepeatReason != null)
{
dkCodeName = string.Format("{0}\nОбгрунтування повторення коду: {1}", dkCodeName, code.RelatedTenderPlanRecord.CodeRepeatReason);
}
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = dkCodeName;
int estimateRowBeginIndex = dkCodeName.IndexOf('\n') + 1;
if (estimateRowBeginIndex > 0)
{
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString())
.get_Characters(estimateRowBeginIndex).Font.Size = 9;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString())
.get_Characters(estimateRowBeginIndex).Font.Italic = true;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString())
.get_Characters(estimateRowBeginIndex).Font.Bold = false;
}
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Value = code.MoneyOnCode;
xlWorksheet.get_Range(procedureNameColumnLetter + currentRowNumber.ToString()).Value = TenderPlanRecord.GetProcedureName(code.RelatedTenderPlanRecord.ProcedureType);
xlWorksheet.get_Range(plannedPeriodColumnLetter + currentRowNumber.ToString()).Value =
string.Format("{0} {1} року", monthes[code.RelatedTenderPlanRecord.TenderBeginDate.Month - 1], code.RelatedTenderPlanRecord.TenderBeginDate.Year);
List <Contract> contractsOnCode = code.RelatedTenderPlanRecord.RegisteredContracts.ToList();
int firstCodeContract = currentRowNumber + 1;
foreach (var contract in contractsOnCode)
{
currentRowNumber++;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Font.Size = 10;
string contractDescription = string.Empty;
if (!string.IsNullOrWhiteSpace(contract.Description))
{
contractDescription = string.Format("\n({0}),", contract.Description);
}
string contractName = string.Format("Договір {0},{1}\n{2}", contract.FullName, contractDescription, contract.Contractor.ShortName);
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = contractName;
xlWorksheet.get_Range(registedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Value = contract.Sum;
xlWorksheet.get_Range(usedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Value = contract.UsedMoney;
xlWorksheet.get_Range(contractRemainColumnLetter + currentRowNumber.ToString()).Value = contract.MoneyRemain;
}
if (contractsOnCode.Count > 0)
{
xlWorksheet.get_Range(registedByContractsMoneyColumnLetter + dkCodeIndex.ToString()).Formula =
string.Format("=SUM({0}{1}:{0}{2})", registedByContractsMoneyColumnLetter, firstCodeContract, currentRowNumber);
xlWorksheet.get_Range(usedByContractsMoneyColumnLetter + dkCodeIndex.ToString()).Formula =
string.Format("=SUM({0}{1}:{0}{2})", usedByContractsMoneyColumnLetter, firstCodeContract, currentRowNumber);
xlWorksheet.get_Range(contractRemainColumnLetter + dkCodeIndex.ToString()).Formula =
string.Format("=SUM({0}{1}:{0}{2})", contractRemainColumnLetter, firstCodeContract, currentRowNumber);
}
else
{
xlWorksheet.get_Range(registedByContractsMoneyColumnLetter + dkCodeIndex.ToString()).Value =
xlWorksheet.get_Range(usedByContractsMoneyColumnLetter + dkCodeIndex.ToString()).Value =
xlWorksheet.get_Range(contractRemainColumnLetter + dkCodeIndex.ToString()).Value = 0;
}
xlWorksheet.get_Range(dkCodeRemainColumnLetter + dkCodeIndex.ToString()).Formula =
string.Format("={1}{0} - {2}{0}", dkCodeIndex, plannedMoneyColumnLetter, registedByContractsMoneyColumnLetter);
dkCodesInKekv.Add("{0}" + dkCodeIndex);
dkCodeNum++;
}
currentRowNumber++;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО";
string kekvSumFormula = "=SUM(" + string.Join(",", dkCodesInKekv) + ")";
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(kekvSumFormula, plannedMoneyColumnLetter);
xlWorksheet.get_Range(registedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(kekvSumFormula, registedByContractsMoneyColumnLetter);
xlWorksheet.get_Range(usedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(kekvSumFormula, usedByContractsMoneyColumnLetter);
xlWorksheet.get_Range(contractRemainColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(kekvSumFormula, contractRemainColumnLetter);
xlWorksheet.get_Range(dkCodeRemainColumnLetter + currentRowNumber.ToString()).Formula =
string.Format("={1}{0} - {2}{0}", currentRowNumber, plannedMoneyColumnLetter, registedByContractsMoneyColumnLetter);
dkCodesInKekv.Clear();
kekvsList.Add("{0}" + currentRowNumber);
currentRowNumber++;
}
if (groupedByKekvResult.Count() > 0)
{
currentRowNumber++;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО ЗА РІК";
if (kekvsList.Count > 0)
{
string sumFormula = "=SUM(" + string.Join(",", kekvsList) + ")";
string sst = string.Format(sumFormula, plannedMoneyColumnLetter);
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(sumFormula, plannedMoneyColumnLetter);
xlWorksheet.get_Range(registedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(sumFormula, registedByContractsMoneyColumnLetter);
xlWorksheet.get_Range(usedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(sumFormula, usedByContractsMoneyColumnLetter);
xlWorksheet.get_Range(contractRemainColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(sumFormula, contractRemainColumnLetter);
}
else
{
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Value =
xlWorksheet.get_Range(registedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Value =
xlWorksheet.get_Range(usedByContractsMoneyColumnLetter + currentRowNumber.ToString()).Value =
xlWorksheet.get_Range(contractRemainColumnLetter + currentRowNumber.ToString()).Value = 0;
}
xlWorksheet.get_Range(dkCodeRemainColumnLetter + currentRowNumber.ToString()).Formula =
string.Format("={1}{0} - {2}{0}", currentRowNumber, plannedMoneyColumnLetter, registedByContractsMoneyColumnLetter);
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(), dkCodeRemainColumnLetter + currentRowNumber.ToString());
foreach (var kekvHeader in kekvsHeaders)
{
DrawTableBorders(numColumnLetter + kekvHeader.ToString(), dkCodeRemainColumnLetter + kekvHeader.ToString());
}
}
else
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
dkCodeRemainColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = "Записи в річному плані відсутні";
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(), dkCodeRemainColumnLetter + currentRowNumber.ToString());
}
}
}
// Отчёт по годовым поступлениям по смете
protected override void WriteDataToFile()
{
Dictionary <KekvCode, List <string> > EstimateKekvTotals = new Dictionary <KekvCode, List <string> >();
using (TenderContext tc = new TenderContext())
{
tc.Estimates.Attach(est);
xlWorksheet.get_Range(yearCell).Value = string.Format("на {0} рік", est.Year.Year);
xlWorksheet.get_Range(estimateNameCell).Value = string.Format("Кошторис: \"{0}\"", est.Name);
xlWorksheet.get_Range(dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
var mList = (from item in est.Changes.ToList()
where (item.PrimaryKekvSum > 0)
group item by new { item.MoneySource, item.PrimaryKekv, item.DateOfReceiving.Month } into g1
select new
{
Source = g1.Key.MoneySource,
Kekv = g1.Key.PrimaryKekv,
Month = g1.Key.Month,
Sum = g1.Sum(p => p.PrimaryKekvSum)
} into s1
group s1 by s1.Source into g2
from g3Item in
(from item in g2
group item by item.Kekv)
group g3Item by g2.Key into g3
orderby g3.Key.ViewPriority
select g3).ToList();
foreach (var source in mList)
{
headersList.Add(currentRowNumber);
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString(),
yearTotalColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = source.Key.Name;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
currentRowNumber++;
int firstKekvRowNumber = currentRowNumber;
foreach (var kekv in source.OrderBy(p => p.Key.Code))
{
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = kekv.Key.Code;
decimal[] monthesRemain = new decimal[12];
int monthCount = kekv.Count();
foreach (var monthSum in kekv)
{
monthesRemain[monthSum.Month - 1] = monthSum.Sum;
}
if (!EstimateKekvTotals.ContainsKey(kekv.Key))
{
EstimateKekvTotals.Add(kekv.Key, new List <string>());
}
EstimateKekvTotals[kekv.Key].Add("{0}" + currentRowNumber);
for (int i = 0; i < monthesRemain.Length; i++)
{
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Value = monthesRemain[i];
}
// Годовой итог по КЕКВ
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Formula
= string.Format("=SUM({1}{0}:{2}{0})",
currentRowNumber,
monthColumnLetters[0],
monthColumnLetters[monthColumnLetters.Count() - 1]);
currentRowNumber++;
}
// Строка с итогами источнику финансирования
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО";
for (int i = 0; i < 12; i++)
{
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Formula
= string.Format("=SUM({0}{1}:{0}{2})", monthColumnLetters[i], firstKekvRowNumber, currentRowNumber - 1);
}
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Formula
= string.Format("=SUM({0}{1}:{0}{2})", yearTotalColumnLetter, firstKekvRowNumber, currentRowNumber - 1);
currentRowNumber++;
}
// Итоги по смете
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString(),
yearTotalColumnLetter + currentRowNumber.ToString()).Merge();
currentRowNumber++;
headersList.Add(currentRowNumber);
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString(),
yearTotalColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО ПО КОШТОРИСУ";
currentRowNumber++;
int totalKekvRowIndex = currentRowNumber;
foreach (var kekv in EstimateKekvTotals.OrderBy(p => p.Key.Code))
{
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = kekv.Key.Code;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
string formulaString = "=SUM(" + string.Join(",", kekv.Value) + ")";
for (int i = 0; i < monthColumnLetters.Length; i++)
{
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Formula =
string.Format(formulaString, monthColumnLetters[i]);
}
// Годовой итог по КЕКВ
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Value
= string.Format("=SUM({1}{0}:{2}{0})",
currentRowNumber,
monthColumnLetters[0],
monthColumnLetters[monthColumnLetters.Count() - 1]);
currentRowNumber++;
}
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО";
for (int i = 0; i < 12; i++)
{
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(monthColumnLetters[i] + currentRowNumber.ToString()).Formula
= string.Format("=SUM({0}{1}:{0}{2})", monthColumnLetters[i], totalKekvRowIndex, currentRowNumber - 1);
}
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(yearTotalColumnLetter + currentRowNumber.ToString()).Formula
= string.Format("=SUM({0}{1}:{0}{2})", yearTotalColumnLetter, totalKekvRowIndex, currentRowNumber - 1);
}
// Рисуем границы таблицы
DrawTableBorders(kekvColumnLetter + (startRowNumber - 1).ToString(), yearTotalColumnLetter + currentRowNumber);
// Рисуем границы заголовков
foreach (var header in headersList)
{
DrawTableBorders(kekvColumnLetter + header.ToString(), yearTotalColumnLetter + header.ToString());
}
}
protected override void WriteDataToFile()
{
// Формируем таблицу с текущими остатками
SetActiveSheet(moneyRemainsSheetNumber);
xlWorksheet.get_Range(dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
// Указываем источники финансирования
for (int i = 0; i < sourcesNum; i++)
{
xlWorksheet.get_Range(lastMoneySourceColumnLetter + (moneyRemainsBeginRowNumber - 1).ToString()).Font.Bold = true;
xlWorksheet.get_Range(lastMoneySourceColumnLetter + (moneyRemainsBeginRowNumber - 1).ToString()).Value = sources[i].Name;
if (i != (sourcesNum - 1))
{
lastMoneySourceColumnLetter = GetNextColumnLetter(lastMoneySourceColumnLetter);
}
else
{
totalMoneySourceColumnLetter = GetNextColumnLetter(lastMoneySourceColumnLetter);
}
}
xlWorksheet.get_Range(totalMoneySourceColumnLetter + (moneyRemainsBeginRowNumber - 1).ToString()).Font.Bold = true;
xlWorksheet.get_Range(totalMoneySourceColumnLetter + (moneyRemainsBeginRowNumber - 1).ToString()).Value = "ВСЬОГО";
xlWorksheet.get_Range(firstMoneySourceColumnLetter + (moneyRemainsBeginRowNumber - 2).ToString(),
totalMoneySourceColumnLetter + (moneyRemainsBeginRowNumber - 2).ToString()).Merge();
using (TenderContext tc = new TenderContext())
{
// Получаем список остатков сгруппированных по сметам затем по КЕКВ
List <EstimateMoneyRemain> moneyRemainsList = (from rec in tc.BalanceChanges.ToList()
where (rec.Estimate.TenderYearId == year.Id) && (rec.DateOfReceiving <= DateTime.Now)
group rec by new { rec.Estimate, rec.PrimaryKekv, rec.MoneySource } into g1
select new
{
Estimate = g1.Key.Estimate,
Kekv = g1.Key.PrimaryKekv,
MoneySource = g1.Key.MoneySource,
Sum = g1.Sum(p => p.PrimaryKekvSum)
} into s1
// Выбираем только те источники по которым есть средства
where (s1.Sum > 0)
// Двойная группировка результата - сначала по смете, затем по КЕКВ
group s1 by s1.Estimate into g2
from rec2 in
(from rec3 in g2
group rec3 by rec3.Kekv)
group rec2 by g2.Key into g3
select new EstimateMoneyRemain
{
Estimate = g3.Key,
KekvRemainsList = g3.Select(p => new KekvRemainAtMoneySource
{
Kekv = p.Key,
Remains = GetMoneySourceSpendingRow(sources,
p.Select(k => new MoneySourceSpending
{
Source = k.MoneySource,
Sum = k.Sum
}).ToList())
}).ToList()
}).ToList();
currentRowIndex = moneyRemainsBeginRowNumber;
foreach (var item in moneyRemainsList)
{
WriteCaption(currentRowIndex, item.Estimate.Name);
estimateNameRows.Add(currentRowIndex);
currentRowIndex++;
int estimateBeginRowIndex = currentRowIndex;
foreach (var kekv in item.KekvRemainsList.OrderBy(p => p.Kekv.Code))
{
if (!kekvs.ContainsKey(kekv.Kekv))
{
kekvs.Add(kekv.Kekv, new List <string>());
}
kekvs[kekv.Kekv].Add("{0}" + currentRowIndex);
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString()).Value = kekv.Kekv.Code;
currentMoneySourceLetter = firstMoneySourceColumnLetter;
foreach (var remain in kekv.Remains)
{
xlWorksheet.get_Range(currentMoneySourceLetter + currentRowIndex.ToString()).Value = remain;
currentMoneySourceLetter = GetNextColumnLetter(currentMoneySourceLetter);
}
FillTotalMoneyColumn();
currentRowIndex++;
}
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString(),
totalMoneySourceColumnLetter + currentRowIndex.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString()).Value = "ВСЬОГО";
currentMoneySourceLetter = firstMoneySourceColumnLetter;
foreach (var source in sources)
{
xlWorksheet.get_Range(currentMoneySourceLetter + currentRowIndex.ToString()).Formula =
string.Format("=SUM({0}{1}:{0}{2})", currentMoneySourceLetter, estimateBeginRowIndex, (currentRowIndex - 1));
currentMoneySourceLetter = GetNextColumnLetter(currentMoneySourceLetter);
}
FillTotalMoneyColumn();
currentRowIndex++;
}
// Выводим итог по всем сметам
WriteCaption(currentRowIndex, "ЗАГАЛЬНИЙ ЗАЛИШОК");
estimateNameRows.Add(currentRowIndex);
currentRowIndex++;
int totalsStartRowNumber = currentRowIndex;
foreach (var kekv in kekvs.OrderBy(p => p.Key.Code))
{
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString(),
totalMoneySourceColumnLetter + currentRowIndex.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString()).Value = kekv.Key.Code;
string kekvSumFormulaString = "=SUM(" + string.Join(",", kekv.Value) + ")";
currentMoneySourceLetter = firstMoneySourceColumnLetter;
foreach (var source in sources)
{
xlWorksheet.get_Range(currentMoneySourceLetter + currentRowIndex.ToString()).Formula =
string.Format(kekvSumFormulaString, currentMoneySourceLetter);
currentMoneySourceLetter = GetNextColumnLetter(currentMoneySourceLetter);
}
FillTotalMoneyColumn();
currentRowIndex++;
}
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString(),
totalMoneySourceColumnLetter + currentRowIndex.ToString()).Font.Bold = true;
xlWorksheet.get_Range(kekvColumnLetter + currentRowIndex.ToString()).Value = "ВСЬОГО";
currentMoneySourceLetter = firstMoneySourceColumnLetter;
foreach (var source in sources)
{
xlWorksheet.get_Range(currentMoneySourceLetter + currentRowIndex.ToString()).Formula =
string.Format("=SUM({0}{1}:{0}{2})", currentMoneySourceLetter, totalsStartRowNumber, (currentRowIndex - 1));
currentMoneySourceLetter = GetNextColumnLetter(currentMoneySourceLetter);
}
FillTotalMoneyColumn();
xlWorksheet.get_Range(kekvColumnLetter + documentHeaderRowNumber.ToString(),
totalMoneySourceColumnLetter + documentHeaderRowNumber.ToString()).Merge();
xlWorksheet.get_Range(kekvColumnLetter + documentHeaderRowNumber.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorksheet.get_Range(kekvColumnLetter + documentHeaderRowNumber.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorksheet.get_Range(dateCell, totalMoneySourceColumnLetter + dateCell.Last().ToString()).Merge();
// Рисуем границы
DrawTableBorders(kekvColumnLetter + (moneyRemainsBeginRowNumber - 2).ToString(),
totalMoneySourceColumnLetter + currentRowIndex.ToString());
foreach (var est in estimateNameRows)
{
DrawTableBorders(kekvColumnLetter + est.ToString(), totalMoneySourceColumnLetter + est.ToString());
}
// Формируем таблицу с новыми счетами
SetActiveSheet(invoiceSheetNumber);
xlWorksheet.get_Range(dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
List <Invoice> newInvoices = (from rec in tc.Invoices.ToList()
where ((rec.Contract.RecordInPlan.Estimate.TenderYearId == year.Id) && (rec.Status == PaymentStatus.New))
orderby rec.IsCredit descending
select rec).ToList();
currentRowIndex = invoiceBeginRowNumber;
if (newInvoices.Count == 0)
{
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString(),
creditColumnLetter + currentRowIndex.ToString()).Merge();
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString(),
creditColumnLetter + currentRowIndex.ToString()).Font.Bold = true;
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString(),
creditColumnLetter + currentRowIndex.ToString()).Font.Italic = true;
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString()).Value = "Нові рахунки на оплату відсутні";
}
else
{
currentRowIndex--;
for (int i = 0; i < newInvoices.Count; i++)
{
currentRowIndex++;
xlWorksheet.get_Range(invoiceNumColumnLetter + currentRowIndex.ToString()).Value = (i + 1).ToString();
xlWorksheet.get_Range(contractorColumnLetter + currentRowIndex.ToString()).Value = newInvoices[i].Contract.Contractor.ShortName;
string contractName = string.Format("Договір № {0} від {1} року",
newInvoices[i].Contract.Number, newInvoices[i].Contract.SignDate.ToShortDateString());
if (!string.IsNullOrWhiteSpace(newInvoices[i].Contract.Description))
{
contractName += string.Format(",\n({0})", newInvoices[i].Contract.Description);
}
xlWorksheet.get_Range(contractColumnLetter + currentRowIndex.ToString()).Value = contractName;
string invoiceName = string.Format("Рахунок/Акт № {0} від {1} року", newInvoices[i].Number, newInvoices[i].Date.ToShortDateString());
if (!string.IsNullOrWhiteSpace(newInvoices[i].Description))
{
contractName += string.Format(",\n({0})", newInvoices[i].Description);
}
xlWorksheet.get_Range(invoiceColumnLetter + currentRowIndex.ToString()).Value = invoiceName;
xlWorksheet.get_Range(invoiceSumColumnLetter + currentRowIndex.ToString()).Value = newInvoices[i].Sum;
xlWorksheet.get_Range(creditColumnLetter + currentRowIndex.ToString()).Value = newInvoices[i].IsCredit ? "БОРГ" : string.Empty;
}
}
DrawTableBorders(invoiceNumColumnLetter + (invoiceBeginRowNumber - 1), creditColumnLetter + currentRowIndex.ToString());
}
}
public TenderTaskManagementService(TenderContext context)
{
this._entityRepository = new EntityRepository <TenderTask>(context);
}
// Смета с фактическими тратами по месяцам
protected override void WriteDataToFile()
{
using (TenderContext tc = new TenderContext())
{
string estimateName = string.Empty;
if (est != null)
{
estimateName = est.Name;
}
else
{
estimateName = "Всі кошториси від початку року";
}
xlWorksheet.get_Range(numColumnLetter + yearCell).Value = string.Format("на {0} рік", year.Year);
xlWorksheet.get_Range(numColumnLetter + estimateNameCell).Value = string.Format("Кошторис: \"{0}\"", estimateName);
xlWorksheet.get_Range(numColumnLetter + dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
// Указываем источники финансирования
for (int i = 0; i < sourcesNum; i++)
{
xlWorksheet.get_Range(lastMoneySourceColumnLetter + beginRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(lastMoneySourceColumnLetter + beginRowNumber.ToString()).Value = sources[i].Name;
if (i != (sourcesNum - 1))
{
lastMoneySourceColumnLetter = GetNextColumnLetter(lastMoneySourceColumnLetter);
}
else
{
totalMoneySourceColumnLetter = GetNextColumnLetter(lastMoneySourceColumnLetter);
}
}
xlWorksheet.get_Range(totalMoneySourceColumnLetter + beginRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(totalMoneySourceColumnLetter + beginRowNumber.ToString()).Value = "ВСЬОГО";
xlWorksheet.get_Range(moneySourceSpendingBeginColumn + (beginRowNumber - 1).ToString(),
totalMoneySourceColumnLetter + (beginRowNumber - 1).ToString()).Merge();
List <EstimateMonthSpending> monthSpendings = GetEstimateMonthesReportList();
currentRowNumber++;
for (int i = 0; i < monthSpendings.Count; i++)
{
monthesHeaders.Add(currentRowNumber);
WriteCaption(string.Format("{0} {1} року", monthes[i], year.Year));
foreach (var kekv in monthSpendings[i].KekvRows)
{
int k = 0;
int spendingStartRow = currentRowNumber;
foreach (var spending in kekv.SpendingRows)
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = (k + 1).ToString();
xlWorksheet.get_Range(dateColumnLetter + currentRowNumber.ToString()).Value = spending.Date.ToShortDateString();
xlWorksheet.get_Range(kekvColumnLetter + currentRowNumber.ToString()).Value = spending.Kekv.Code;
xlWorksheet.get_Range(documentColumnLetter + currentRowNumber.ToString()).Value = spending.SpendingDescription;
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString()).Value = spending.Contract;
// Выделяем курсивом другие траты (зарплата, налоги и т.п.)
if (spending.Type == BalanceChangeType.PlannedSpending)
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
totalMoneySourceColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
}
currentSourceColumnLetter = moneySourceSpendingBeginColumn;
for (int j = 0; j < spending.SpendingList.Length; j++)
{
xlWorksheet.get_Range(currentSourceColumnLetter + currentRowNumber.ToString()).Value = spending.SpendingList[j];
currentSourceColumnLetter = GetNextColumnLetter(currentSourceColumnLetter);
}
xlWorksheet.get_Range(totalMoneySourceColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(totalMoneySourceColumnLetter + currentRowNumber.ToString()).Formula
= string.Format("=SUM({1}{0}:{2}{0})", currentRowNumber, moneySourceSpendingBeginColumn, lastMoneySourceColumnLetter);
k++;
currentRowNumber++;
}
// Итоги по КЕКВ
if (!estimateKekvSpendingTotals.ContainsKey(kekv.Kekv))
{
estimateKekvSpendingTotals.Add(kekv.Kekv, new List <string>());
}
if (!estimateKekvPlannedMoneyTotals.ContainsKey(kekv.Kekv))
{
estimateKekvPlannedMoneyTotals.Add(kekv.Kekv, new List <string>());
}
if (!estimateKekvMoneyRemainTotals.ContainsKey(kekv.Kekv))
{
estimateKekvMoneyRemainTotals.Add(kekv.Kekv, new List <string>());
}
// Итоги по КЕКВ
string kekvSpendingFormula = null;
string prevMonthKekvRemainFormula = null;
decimal[] plannedSpending = kekv.PlannedMoneysRow.SpendingList;
if (kekv.SpendingRows.Count > 0)
{
kekvSpendingFormula = "=SUM({0}" + spendingStartRow + ":{0}" + (currentRowNumber - 1) + ")";
}
if ((i > 0) && (estimateKekvMoneyRemainTotals[kekv.Kekv].Count > 0))
{
prevMonthKekvRemainFormula = "=" + estimateKekvMoneyRemainTotals[kekv.Kekv].Last();
}
WriteTotals(TotalsType.KekvTotal, kekv.Kekv, kekvSpendingFormula,
prevMonthKekvRemainFormula, null, plannedSpending);
currentRowNumber++;
}
// Итоги за месяц
string totalSpendingFormula = null;
if (monthSpendingCells.Count > 0)
{
totalSpendingFormula = "=SUM(" + string.Join(",", monthSpendingCells) + ")";
}
string totalPlannedMoney = null;
decimal[] plannedMoneyRow = new decimal[sourcesNum];
if (monthPlannedMoneyCells.Count > 0)
{
totalPlannedMoney = "=SUM(" + string.Join(",", monthPlannedMoneyCells) + ")";
}
string moneyFromLastMonth = null;
if ((i > 0) && (lastMonthRemainCells.Count > 0))
{
moneyFromLastMonth = "=SUM(" + string.Join(",", lastMonthRemainCells) + ")";
}
WriteTotals(TotalsType.PeriodTotal, null, totalSpendingFormula,
moneyFromLastMonth, totalPlannedMoney, plannedMoneyRow);
monthSpendingCells.Clear();
monthPlannedMoneyCells.Clear();
lastMonthRemainCells.Clear();
}
if (monthSpendings.Count > 0)
{
monthesHeaders.Add(currentRowNumber);
// Итоги за ГОД
WriteCaption(string.Format("ПІДСУМКОВА ІНФОРМАЦІЯ ЗА {0} РІК", year.Year));
// По КЕКВ
foreach (var kekv in estimateKekvSpendingTotals.Keys.OrderBy(p => p.Code))
{
string totalKekvSpendingFormula = "=SUM(" + string.Join(",", estimateKekvSpendingTotals[kekv]) + ")";
string totalPlannedKekvMoney = "=SUM(" + string.Join(",", estimateKekvPlannedMoneyTotals[kekv]) + ")";
WriteTotals(TotalsType.KekvTotal, kekv, totalKekvSpendingFormula,
null, totalPlannedKekvMoney, null);
currentRowNumber++;
}
// ВСЕГО
string yearTotalSpendingFormula = "=SUM(" + string.Join(",", monthSpendingCells) + ")";
string yearTotalPlannedMoney = "=SUM(" + string.Join(",", monthPlannedMoneyCells) + ")";
WriteTotals(TotalsType.PeriodTotal, null, yearTotalSpendingFormula,
null, yearTotalPlannedMoney, null);
// Рисуем границы таблицы
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(),
totalMoneySourceColumnLetter + currentRowNumber.ToString());
// Рисуем границы ячеек с названиями месяцев
foreach (var cell in monthesHeaders)
{
DrawTableBorders(numColumnLetter + cell.ToString(), totalMoneySourceColumnLetter + cell.ToString());
}
}
else
{
WriteCaption("Витраты відсутні");
// Рисуем границы таблицы
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(),
totalMoneySourceColumnLetter + currentRowNumber.ToString());
}
// Центрируем заголовок отчёта
xlWorksheet.get_Range(numColumnLetter + reportTitleRow, totalMoneySourceColumnLetter + reportTitleRow).Merge();
xlWorksheet.get_Range(numColumnLetter + yearCell, totalMoneySourceColumnLetter + yearCell).Merge();
xlWorksheet.get_Range(numColumnLetter + estimateNameCell, totalMoneySourceColumnLetter + estimateNameCell).Merge();
xlWorksheet.get_Range(numColumnLetter + dateCell, totalMoneySourceColumnLetter + dateCell).Merge();
}
}
private List <EstimateMonthSpending> GetEstimateMonthesReportList()
{
using (TenderContext tc = new TenderContext())
{
List <BalanceChanges> estimateBalanceChanges;
if (est != null)
{
estimateBalanceChanges = tc.BalanceChanges.Where(p => p.EstimateId == est.Id).ToList();
}
else
{
estimateBalanceChanges = tc.BalanceChanges.Where(p => p.Estimate.TenderYearId == year.Id).ToList();
}
// Затраты по счетам
List <EstimateSpendingRow> invoices;
// Запланированные траты
List <EstimateSpendingRow> plannedSpending;
// Деньги поступившие по смете за месяц
List <EstimateSpendingRow> estimateMoneyOnMonth;
// Список КЕКВ с остатками на начало месяца
List <EstimateSpendingRow> kekvsWithRemainOnMonthBegin;
if (isNewSystem)
{
invoices = (from spen in estimateBalanceChanges
where (spen.InvoiceId != null)
group spen by new { spen.PrimaryKekv, spen.Invoice, spen.DateOfReceiving } into g1
select new EstimateSpendingRow
{
Date = g1.Key.DateOfReceiving,
Kekv = g1.Key.PrimaryKekv,
SpendingDescription = string.Format("Рахунок {0} від {1} року{2}",
g1.Key.Invoice.Number,
g1.Key.Invoice.Date.ToShortDateString(),
string.IsNullOrWhiteSpace(g1.Key.Invoice.Description) ? string.Empty : string.Format(",\n({0})", g1.Key.Invoice.Description)),
Contract = string.Format("Договір № {0} від {1} року,{2}\n{3}",
g1.Key.Invoice.Contract.Number,
g1.Key.Invoice.Contract.SignDate.ToShortDateString(),
string.IsNullOrWhiteSpace(g1.Key.Invoice.Contract.Description) ? string.Empty : string.Format("\n({0}),", g1.Key.Invoice.Contract.Description),
g1.Key.Invoice.Contract.Contractor.ShortName),
Type = BalanceChangeType.InvoiceSpending,
SpendingList = GetMoneySourceSpendingRow(sources, g1.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = -p.PrimaryKekvSum
}).ToList())
}).ToList();
plannedSpending = (from spen in estimateBalanceChanges
where (spen.PlannedSpendingId != null)
group spen by new { spen.PrimaryKekv, spen.PlannedSpending, spen.DateOfReceiving } into g1
select new EstimateSpendingRow
{
Date = g1.Key.DateOfReceiving,
Kekv = g1.Key.PrimaryKekv,
SpendingDescription = g1.Key.PlannedSpending.Description,
Contract = "-",
Type = BalanceChangeType.PlannedSpending,
SpendingList = GetMoneySourceSpendingRow(sources, g1.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = -p.PrimaryKekvSum
}).ToList())
}).ToList();
estimateMoneyOnMonth = (from spen in estimateBalanceChanges
where (spen.PlannedSpendingId == null) && (spen.InvoiceId == null) && (spen.PrimaryKekvSum > 0)
group spen by new { spen.PrimaryKekv, spen.DateOfReceiving.Month } into g1
select new EstimateSpendingRow
{
Date = new DateTime(year.Year, g1.Key.Month, 1),
Kekv = g1.Key.PrimaryKekv,
SpendingDescription = EstimateSpendingRow.PLANNED_MONEY_TEXT,
Contract = "-",
Type = BalanceChangeType.PlannedMoney,
SpendingList = GetMoneySourceSpendingRow(sources, g1.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = p.PrimaryKekvSum
}).ToList())
}).ToList();
}
else
{
invoices = (from spen in estimateBalanceChanges
where (spen.InvoiceId != null)
group spen by new { spen.SecondaryKekv, spen.Invoice, spen.DateOfReceiving } into g1
select new EstimateSpendingRow
{
Date = g1.Key.DateOfReceiving,
Kekv = g1.Key.SecondaryKekv,
SpendingDescription = string.Format("Рахунок {0} від {1} року{2}",
g1.Key.Invoice.Number,
g1.Key.Invoice.Date.ToShortDateString(),
string.IsNullOrWhiteSpace(g1.Key.Invoice.Description) ? string.Empty : string.Format(",\n({0}),", g1.Key.Invoice.Description)),
Contract = string.Format("Договір № {0} від {1} року,{2}\n{3}",
g1.Key.Invoice.Contract.Number,
g1.Key.Invoice.Contract.SignDate.ToShortDateString(),
string.IsNullOrWhiteSpace(g1.Key.Invoice.Contract.Description) ? string.Empty : string.Format("\n({0}),", g1.Key.Invoice.Contract.Description),
g1.Key.Invoice.Contract.Contractor.ShortName),
Type = BalanceChangeType.InvoiceSpending,
SpendingList = GetMoneySourceSpendingRow(sources, g1.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = -p.SecondaryKekvSum
}).ToList())
}).ToList();
plannedSpending = (from spen in estimateBalanceChanges
where (spen.PlannedSpendingId != null)
group spen by new { spen.SecondaryKekv, spen.PlannedSpending, spen.DateOfReceiving } into g1
select new EstimateSpendingRow
{
Date = g1.Key.DateOfReceiving,
Kekv = g1.Key.SecondaryKekv,
SpendingDescription = g1.Key.PlannedSpending.Description,
Contract = "-",
Type = BalanceChangeType.PlannedSpending,
SpendingList = GetMoneySourceSpendingRow(sources, g1.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = -p.SecondaryKekvSum
}).ToList())
}).ToList();
estimateMoneyOnMonth = (from spen in estimateBalanceChanges
where (spen.PlannedSpendingId == null) && (spen.InvoiceId == null) && (spen.SecondaryKekvSum > 0)
group spen by new { spen.SecondaryKekv, spen.DateOfReceiving.Month } into g1
select new EstimateSpendingRow
{
Date = new DateTime(year.Year, g1.Key.Month, 1),
Kekv = g1.Key.SecondaryKekv,
SpendingDescription = EstimateSpendingRow.PLANNED_MONEY_TEXT,
Contract = "-",
Type = BalanceChangeType.PlannedMoney,
SpendingList = GetMoneySourceSpendingRow(sources, g1.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = p.SecondaryKekvSum
}).ToList())
}).ToList();
}
List <EstimateSpendingRow> allSpendings = invoices.Union(plannedSpending)
.Union(estimateMoneyOnMonth)
.ToList();
List <EstimateMonthSpending> result = new List <EstimateMonthSpending>();
for (int i = 1; i <= 12; i++)
{
if (isNewSystem)
{
kekvsWithRemainOnMonthBegin = (from spen in estimateBalanceChanges
where (spen.DateOfReceiving.Month <= i)
group spen by new { spen.PrimaryKekv, spen.DateOfReceiving.Month } into g1
select new { Kekv = g1.Key.PrimaryKekv, Sum = g1.Sum(p => p.PrimaryKekvSum) } into s1
where s1.Sum > 0
select new EstimateSpendingRow
{
Kekv = s1.Kekv,
Date = new DateTime(Convert.ToInt32(year.Year), i, 1),
Type = BalanceChangeType.RemainFromPrevMonth,
}).ToList();
}
else
{
kekvsWithRemainOnMonthBegin = (from spen in estimateBalanceChanges
where (spen.DateOfReceiving.Month <= i)
group spen by new { spen.SecondaryKekv, spen.DateOfReceiving.Month } into g1
select new { Kekv = g1.Key.SecondaryKekv, Sum = g1.Sum(p => p.SecondaryKekvSum) } into s1
where s1.Sum > 0
select new EstimateSpendingRow
{
Kekv = s1.Kekv,
Date = new DateTime(Convert.ToInt32(year.Year), i, 1),
Type = BalanceChangeType.RemainFromPrevMonth,
}).Distinct().ToList();
}
EstimateMonthSpending month = new EstimateMonthSpending();
month.KekvRows = (from item in allSpendings.Union(kekvsWithRemainOnMonthBegin)
where (item.Date.Month == i)
group item by item.Kekv into g1
orderby g1.Key.Code
select new GroupedByKekvEstimateSpendingRow
{
Kekv = g1.Key,
SpendingRows = g1.Where(k => (k.Type == BalanceChangeType.InvoiceSpending) || (k.Type == BalanceChangeType.PlannedSpending)).ToList(),
PlannedMoneysRow = g1.Where(p => (p.Date.Month == i) && (p.Type == BalanceChangeType.PlannedMoney))
.DefaultIfEmpty(new EstimateSpendingRow
{
Kekv = g1.Key,
SpendingList = new decimal[sourcesNum]
}).First()
}).ToList();
result.Add(month);
}
return(result);
}
}
protected override void WriteDataToFile()
{
using (TenderContext tc = new TenderContext())
{
string estimateName = string.Empty;
List <Contract> contractsList = null;
if (estFilter.Id > 0)
{
estimateName = estFilter.Name;
contractsList = tc.Contracts.Where(p => p.RecordInPlan.EstimateId == estFilter.Id).ToList();
}
else
{
estimateName = "Всі кошториси від початку року";
contractsList = tc.Contracts.Where(p => p.RecordInPlan.Estimate.TenderYearId == year.Id).ToList();
}
if (contractorFilter.Id > 0)
{
contractsList = contractsList.Where(p => p.ContractorId == contractorFilter.Id).ToList();
}
xlWorksheet.get_Range(numColumnLetter + yearCell).Value = string.Format("на {0} рік", year.Year);
xlWorksheet.get_Range(numColumnLetter + estimateNameCell).Value = string.Format("Кошторис: \"{0}\"", estimateName);
xlWorksheet.get_Range(numColumnLetter + dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
// Указываем источники финансирования
for (int i = 0; i < sourcesNum; i++)
{
xlWorksheet.get_Range(lastMoneySourceColumnLetter + beginRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(lastMoneySourceColumnLetter + beginRowNumber.ToString()).Value = sources[i].Name;
if (i == (sourcesNum - 1))
{
totalMoneySourceColumnLetter = GetNextColumnLetter(lastMoneySourceColumnLetter);
}
else
{
lastMoneySourceColumnLetter = GetNextColumnLetter(lastMoneySourceColumnLetter);
}
}
xlWorksheet.get_Range(totalMoneySourceColumnLetter + beginRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(totalMoneySourceColumnLetter + beginRowNumber.ToString()).Value = "ВСЬОГО";
xlWorksheet.get_Range(firstMoneySourceColumnLetter + (beginRowNumber - 1).ToString(),
totalMoneySourceColumnLetter + (beginRowNumber - 1).ToString()).Merge();
// Столбец с остатком по договору
contractRemainColumnLetter = GetNextColumnLetter(totalMoneySourceColumnLetter);
xlWorksheet.get_Range(contractRemainColumnLetter + (beginRowNumber - 1).ToString(),
contractRemainColumnLetter + beginRowNumber.ToString()).Merge();
xlWorksheet.get_Range(contractRemainColumnLetter + (beginRowNumber - 1).ToString()).Font.Bold = true;
xlWorksheet.get_Range(contractRemainColumnLetter + (beginRowNumber - 1).ToString()).Value = "Залишок";
List <GroupedByKekvContracts> groupedByKekvContracts = null;
if (isNewSystem)
{
groupedByKekvContracts = (from contr in contractsList
group contr by contr.RecordInPlan.PrimaryKekv into g1
select new GroupedByKekvContracts
{
Kekv = g1.Key,
Contracts = g1.OrderBy(p => p.Contractor).ToList()
}).ToList();
}
else
{
groupedByKekvContracts = (from contr in contractsList
group contr by contr.RecordInPlan.SecondaryKekv into g1
select new GroupedByKekvContracts
{
Kekv = g1.Key,
Contracts = g1.OrderBy(p => p.Contractor).ToList()
}).ToList();
}
if (kekvFilter.Id > 0)
{
groupedByKekvContracts = groupedByKekvContracts.Where(p => p.Kekv.Id == kekvFilter.Id).ToList();
}
if (groupedByKekvContracts.Count == 0)
{
currentRowNumber++;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = "Договори відсутні";
}
foreach (var kekv in groupedByKekvContracts)
{
currentRowNumber++;
kekvsHeaders.Add(currentRowNumber);
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value =
string.Format("{0} - {1}", kekv.Kekv.Code, kekv.Kekv.Name);
currentRowNumber++;
int contractNum = 0;
foreach (var contract in kekv.Contracts)
{
int currentContractRowIndex = currentRowNumber;
contractsInKekv.Add("{0}" + currentContractRowIndex);
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Font.Size = 11;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = (contractNum + 1).ToString();
xlWorksheet.get_Range(contractorColumnLetter + currentRowNumber.ToString()).Value = contract.Contractor.ShortName;
string contractName = contract.FullName;
if (!string.IsNullOrWhiteSpace(contract.Description))
{
contractName += string.Format(",\n{0}", contract.Description);
}
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString()).Value = contractName;
xlWorksheet.get_Range(contractSumColumnLetter + currentRowNumber.ToString()).Value = contract.Sum;
int firstInvoiceRowIndex = currentRowNumber + 1;
foreach (var invoice in contract.Invoices)
{
currentRowNumber++;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Font.Size = 10;
string invoiceName = string.Format("Рахунок/Акт № {0} від {1} року", invoice.Number, invoice.Date);
if (!string.IsNullOrWhiteSpace(invoice.Description))
{
invoiceName += string.Format(",\n({0})", invoice.Description);
}
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString()).Value = invoiceName;
decimal[] spending = null;
if (isNewSystem)
{
spending = GetMoneySourceSpendingRow(sources, invoice.Changes
.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = -p.PrimaryKekvSum
}).ToList());
}
else
{
spending = GetMoneySourceSpendingRow(sources, invoice.Changes
.Select(p => new MoneySourceSpending
{
Source = p.MoneySource,
Sum = -p.SecondaryKekvSum
}).ToList());
}
currentSourceColumnLetter = firstMoneySourceColumnLetter;
foreach (var sum in spending)
{
xlWorksheet.get_Range(currentSourceColumnLetter + currentRowNumber.ToString()).Value = sum;
currentSourceColumnLetter = GetNextColumnLetter(currentSourceColumnLetter);
}
xlWorksheet.get_Range(totalMoneySourceColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(totalMoneySourceColumnLetter + currentRowNumber.ToString()).Formula
= string.Format("=SUM({1}{0}:{2}{0})", currentRowNumber, firstMoneySourceColumnLetter, lastMoneySourceColumnLetter);
}
if (contract.Invoices.Count > 0)
{
currentSourceColumnLetter = firstMoneySourceColumnLetter;
for (int i = 0; i < sourcesNum; i++)
{
xlWorksheet.get_Range(currentSourceColumnLetter + currentContractRowIndex.ToString()).Formula =
string.Format("=SUM({0}{1}:{0}{2})", currentSourceColumnLetter, firstInvoiceRowIndex, currentRowNumber);
currentSourceColumnLetter = GetNextColumnLetter(currentSourceColumnLetter);
}
}
else
{
currentSourceColumnLetter = firstMoneySourceColumnLetter;
for (int i = 0; i < sourcesNum; i++)
{
xlWorksheet.get_Range(currentSourceColumnLetter + currentContractRowIndex.ToString()).Value = 0;
currentSourceColumnLetter = GetNextColumnLetter(currentSourceColumnLetter);
}
}
xlWorksheet.get_Range(totalMoneySourceColumnLetter + currentContractRowIndex.ToString()).Font.Bold = true;
xlWorksheet.get_Range(totalMoneySourceColumnLetter + currentContractRowIndex.ToString()).Formula =
string.Format("=SUM({1}{0}:{2}{0})", currentContractRowIndex, firstMoneySourceColumnLetter, lastMoneySourceColumnLetter);
xlWorksheet.get_Range(contractRemainColumnLetter + currentContractRowIndex.ToString()).Font.Bold = true;
xlWorksheet.get_Range(contractRemainColumnLetter + currentContractRowIndex.ToString()).Formula =
string.Format("={1}{0} - {2}{0}", currentContractRowIndex, contractSumColumnLetter, totalMoneySourceColumnLetter);
contractNum++;
}
currentRowNumber++;
kekvsTotals.Add("{0}" + currentRowNumber);
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО ЗА КЕКВ";
string kekvTotalsFormulaString = "=SUM(" + string.Join(",", contractsInKekv) + ")";
WriteTotalsInCurrentRow(kekvTotalsFormulaString);
contractsInKekv.Clear();
}
currentRowNumber++;
if (groupedByKekvContracts.Count > 0)
{
currentRowNumber++;
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(contractColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО ЗА РІК";
string yearTotalsFormulaString = "=SUM(" + string.Join(",", kekvsTotals) + ")";
WriteTotalsInCurrentRow(yearTotalsFormulaString);
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(), contractRemainColumnLetter + currentRowNumber.ToString());
foreach (var kekvHeader in kekvsHeaders)
{
DrawTableBorders(numColumnLetter + kekvHeader.ToString(), contractRemainColumnLetter + kekvHeader.ToString());
}
}
else
{
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(), contractRemainColumnLetter + currentRowNumber.ToString());
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
contractRemainColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = "Інформація по договорам відсутня";
}
// Выравниваем заголовки отчёта
xlWorksheet.get_Range(numColumnLetter + reportTitleRow, contractRemainColumnLetter + reportTitleRow).Merge();
xlWorksheet.get_Range(numColumnLetter + yearCell, contractRemainColumnLetter + yearCell).Merge();
xlWorksheet.get_Range(numColumnLetter + estimateNameCell, contractRemainColumnLetter + estimateNameCell).Merge();
xlWorksheet.get_Range(numColumnLetter + dateCell, contractRemainColumnLetter + dateCell).Merge();
}
}
protected override void WriteDataToFile()
{
List <TenderPlanItemsTableEntry> planRecords = null;
List <TenderPlanRecord> allPlanRecords = null;
string freeCellLetter = GetNextColumnLetter(plannedMoneyColumnLetter);
xlWorksheet.get_Range(freeCellLetter + currentRowNumber).ColumnWidth =
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber).ColumnWidth + xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber).ColumnWidth;
using (TenderContext tc = new TenderContext())
{
string estimateName = string.Empty;
if (estFilter.Id > 0)
{
estimateName = estFilter.Name;
allPlanRecords = (from r in tc.TenderPlanRecords
where r.EstimateId == estFilter.Id
select r).ToList();
}
else
{
estimateName = "Всі кошториси від початку року";
allPlanRecords = (from r in tc.TenderPlanRecords
where r.Estimate.TenderYearId == year.Id
select r).ToList();
}
xlWorksheet.get_Range(yearCell).Value = string.Format("на {0} рік", year.Year);
xlWorksheet.get_Range(estimateNameCell).Value = string.Format("Кошторис: \"{0}\"", estimateName);
xlWorksheet.get_Range(dateCell).Value = string.Format("Інформація станом на {0} року", DateTime.Now.ToShortDateString());
if (isNewSystem)
{
planRecords = (from r in allPlanRecords.ToList()
select new TenderPlanItemsTableEntry
{
Kekv = r.PrimaryKekv,
Dk = r.Dk,
MoneyOnCode = r.PlannedSum,
RelatedTenderPlanRecord = r,
Estimate = r.Estimate
}).ToList();
}
else
{
planRecords = (from r in allPlanRecords
select new TenderPlanItemsTableEntry
{
Kekv = r.SecondaryKekv,
Dk = r.Dk,
MoneyOnCode = r.PlannedSum,
RelatedTenderPlanRecord = r,
Estimate = r.Estimate
}).ToList();
}
if (kekvFilter.Id > 0)
{
planRecords = planRecords.Where(p => p.Kekv.Id == kekvFilter.Id).ToList();
}
if (planRecords.Count == 0)
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = "Записи в річному плані відсутні";
}
var groupedByKekvResult = planRecords.GroupBy(p => p.Kekv);
foreach (var kekv in groupedByKekvResult.OrderBy(p => p.Key.Code))
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value =
string.Format("{0} - {1}", kekv.Key.Code, kekv.Key.Name);
kekvsHeaders.Add(currentRowNumber);
int dkCodeNum = 0;
foreach (var code in kekv)
{
currentRowNumber++;
int dkCodeIndex = currentRowNumber;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Font.Size = 11;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = (dkCodeNum + 1).ToString();
xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString()).Font.Bold = true;
string dkCodeName = string.Format("{0} ({1})",
code.Dk.FullName,
code.RelatedTenderPlanRecord.ConcreteName);
if (!string.IsNullOrWhiteSpace(code.RelatedTenderPlanRecord.ProtocolNum))
{
dkCodeName = string.Format("{0}\n Затверджено протоколом № {1} від {2} року",
dkCodeName,
code.RelatedTenderPlanRecord.ProtocolNum,
code.RelatedTenderPlanRecord.ProtocolDate.ToShortDateString());
}
if (code.RelatedTenderPlanRecord.CodeRepeatReason != null)
{
dkCodeName = string.Format("{0}\nОбгрунтування повторення коду: {1}", dkCodeName, code.RelatedTenderPlanRecord.CodeRepeatReason);
}
xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString()).Value = dkCodeName;
int estimateRowBeginIndex = dkCodeName.IndexOf('\n') + 1;
if (estimateRowBeginIndex > 0)
{
xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString())
.get_Characters(estimateRowBeginIndex).Font.Size = 9;
xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString())
.get_Characters(estimateRowBeginIndex).Font.Italic = true;
xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString())
.get_Characters(estimateRowBeginIndex).Font.Bold = false;
}
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString()).RowHeight = xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString()).RowHeight;
xlWorksheet.get_Range(freeCellLetter + currentRowNumber.ToString()).Cut(xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString()));
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString(),
dkCodeColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString()).WrapText = true;
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
xlWorksheet.get_Range(procedureNameColumnLetter + currentRowNumber.ToString()).Value = TenderPlanRecord.GetProcedureName(code.RelatedTenderPlanRecord.ProcedureType);
xlWorksheet.get_Range(plannedPeriodColumnLetter + currentRowNumber.ToString()).Value =
string.Format("{0} {1} року", monthes[code.RelatedTenderPlanRecord.TenderBeginDate.Month - 1], code.RelatedTenderPlanRecord.TenderBeginDate.Year);
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Value = code.MoneyOnCode;
List <TenderPlanRecordChange> changesList = code.RelatedTenderPlanRecord.Changes.OrderByDescending(p => p.DateOfChange).ToList();
int firstChangeRowNumber = currentRowNumber + 1;
foreach (var change in changesList)
{
currentRowNumber++;
xlWorksheet.get_Range(dateOfCodeChangeColumnLetter + currentRowNumber.ToString()).Value = change.DateOfChange.ToShortDateString();
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = change.Description;
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Value = change.ChangeOfSum;
}
dkCodesInKekv.Add("{0}" + dkCodeIndex);
dkCodeNum++;
}
currentRowNumber++;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО";
string kekvSumFormula = "=SUM(" + string.Join(",", dkCodesInKekv) + ")";
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(kekvSumFormula, plannedMoneyColumnLetter);
dkCodesInKekv.Clear();
kekvsList.Add("{0}" + currentRowNumber);
currentRowNumber++;
}
if (groupedByKekvResult.Count() > 0)
{
currentRowNumber++;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(dkCodeColumnLetter + currentRowNumber.ToString()).Value = "ВСЬОГО ЗА РІК";
if (kekvsList.Count > 0)
{
string sumFormula = "=SUM(" + string.Join(",", kekvsList) + ")";
string sst = string.Format(sumFormula, plannedMoneyColumnLetter);
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Formula =
string.Format(sumFormula, plannedMoneyColumnLetter);
}
else
{
xlWorksheet.get_Range(plannedMoneyColumnLetter + currentRowNumber.ToString()).Value = 0;
}
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(), plannedMoneyColumnLetter + currentRowNumber.ToString());
foreach (var kekvHeader in kekvsHeaders)
{
DrawTableBorders(numColumnLetter + kekvHeader.ToString(), plannedMoneyColumnLetter + kekvHeader.ToString());
}
}
else
{
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString(),
plannedMoneyColumnLetter + currentRowNumber.ToString()).Merge();
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Bold = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Font.Italic = true;
xlWorksheet.get_Range(numColumnLetter + currentRowNumber.ToString()).Value = "Записи в річному плані відсутні";
DrawTableBorders(numColumnLetter + (beginRowNumber - 1).ToString(), plannedMoneyColumnLetter + currentRowNumber.ToString());
}
}
}
public async Task <ActionResult> Index(int page = 1)
{
List <Tender> tenders = new List <Tender>();
using (TenderContext db = new TenderContext())
{
//db.Tenders.Add(new Tender()
//{
// Id = 1,
// Subject = "Item 1",
// Price = 10,
// Currency = "Доллар",
// Date = DateTime.Now,
// Start = new DateTime(2017, 10, 30),
// End = new DateTime(2018, 12, 30),
// Category = "Химическая продукция",
// Kind = "Открытые торги",
// Organizer = "Энергоатом"
//});
//db.Tenders.Add(new Tender()
//{
// Id = 2,
// Subject = "Item 2",
// Price = 5,
// Currency = "Евро",
// Date = DateTime.Now,
// Start = new DateTime(2018, 10, 30),
// End = new DateTime(2018, 12, 30),
// Category = "Химическая продукция",
// Kind = "Открытые торги",
// Organizer = "Энергоатом"
//});
//db.Tenders.Add(new Tender()
//{
// Id = 3,
// Subject = "Item 3",
// Price = 50,
// Currency = "Гривна",
// Date = DateTime.Now,
// Start = new DateTime(2018, 10, 30),
// End = new DateTime(2018, 12, 30),
// Category = "Химическая продукция",
// Kind = "Открытые торги",
// Organizer = "Энергоатом"
//});
//db.Tenders.Add(new Tender()
//{
// Id = 1,
// Subject = "Item 4",
// Price = 10,
// Currency = "Доллар",
// Date = DateTime.Now,
// Start = new DateTime(2018, 10, 30),
// End = new DateTime(2018, 12, 30),
// Category = "Печатная продукция",
// Kind = "Открытые торги",
// Organizer = "Киевский метрополитен"
//});
//db.Tenders.Add(new Tender()
//{
// Id = 2,
// Subject = "Item 5",
// Price = 5,
// Currency = "Евро",
// Date = DateTime.Now,
// Start = new DateTime(2018, 10, 30),
// End = new DateTime(2018, 12, 30),
// Category = "Химическая продукция",
// Kind = "Открытые торги",
// Organizer = "Энергоатом"
//});
//db.Tenders.Add(new Tender()
//{
// Id = 3,
// Subject = "Item 6",
// Price = 50,
// Currency = "Гривна",
// Date = DateTime.Now,
// Start = new DateTime(2018, 10, 30),
// End = new DateTime(2018, 12, 30),
// Category = "Печатная продукция",
// Kind = "Закрытые торги",
// Organizer = "Укрэнерго"
//});
//db.SaveChanges();
foreach (Tender t in db.Tenders)
{
tenders.Add(t);
}
ViewBag.sortOrder = tenders;
}
int pageSize = 3;
IEnumerable <Tender> tendersPerPages = tenders.Skip((page - 1) * pageSize).Take(pageSize);
PageInfo pageInfo = new PageInfo {
PageNumber = page, PageSize = pageSize, TotalItems = tenders.Count
};
IndexViewModel ivm = new IndexViewModel {
PageInfo = pageInfo, Tenders = tendersPerPages
};
return(View(ivm));
}
public ActionResult _TenderView(int?page, string organizer, string kind, string category, string search, string sort, DateTime?dateFrom, DateTime?dateTo)
{
TenderContext db = new TenderContext();
var tenders = db.Tenders.ToList();
tenders = tenders.Where(item =>
((string.IsNullOrEmpty(organizer) || organizer == "Все") || item.Organizer.Equals(organizer)) &&
((string.IsNullOrEmpty(kind) || kind == "Все") || item.Kind.Equals(kind)) &&
((string.IsNullOrEmpty(category) || category == "Все") || item.Category.Equals(category))
).ToList();
if (!string.IsNullOrEmpty(search))
{
var tendersSub = tenders.Where(a => a.Subject != null && a.Subject.Contains(search)).ToList();
var tendersDes = tenders.Where(a => a.Desciption != null && a.Desciption.Contains(search)).ToList();
tenders = tendersSub;
tenders.AddRange(tendersDes);
}
if (dateFrom != null)
{
tenders = tenders.Where(item => item.Date.Date >= dateFrom).ToList();
}
if (dateTo != null)
{
tenders = tenders.Where(item => item.Date.Date <= dateTo).ToList();
}
//Фильтры: Date, Category, Price
if (!string.IsNullOrEmpty(sort))
{
if (sort == "Date")
{
tenders = tenders.OrderBy(a => a.Date).ToList();
}
if (sort == "Price")
{
tenders = tenders.OrderBy(a => a.Price).ToList();
}
if (sort == "Category")
{
tenders = tenders.OrderBy(a => a.Category).ToList();
}
}
int pageSize = tenders.Count;
int current = page ?? 1;
IEnumerable <Tender> tendersPerPages = tenders.Skip((1 - 1) * pageSize).Take(pageSize);
PageInfo pageInfo = new PageInfo {
PageNumber = 1, PageSize = pageSize, TotalItems = tenders.Count
};
IndexViewModel ivm = new IndexViewModel {
PageInfo = pageInfo, Tenders = tendersPerPages
};
ViewBag.sortOrder = tenders;
return(PartialView(ivm));
}