private void SaveToFile_button_Click(object sender, EventArgs e)
{
Misc.OtchMiddleMarks(Marks);
}
public static bool OtchMiddleMarks(MiddleMarks_class Marks, bool ShowErrorMessage = true, bool Open = true)
{
var WorkBook = ATMisc.GetGenericExcel("Средние концентрации за год.xls");
if (WorkBook == null)
{
return(false);
}
var Sheet = WorkBook.GetSheet("печать");
if (Sheet == null)
{
if (ShowErrorMessage)
{
MessageBox.Show("В шаблоне не найден лист с именем \"печать\"", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
var Exchange = new CellExchange_Class(Sheet);
if (Marks.PodrID == 0)
{
Exchange.AddExchange("{подразделение}", "Все подразделения", 5);
}
else
{
Exchange.AddExchange("{подразделение}", T.Podr.Rows.Get <string>(Marks.PodrID, C.Podr.ShrName), 5);
}
var ObjectName = (Marks.ObjectID == 0 ? T.Object.Rows.Get <string>(Marks.ObjectID, C.Object.Name) : "Все");
Exchange.AddExchange("{объект}", ObjectName, 5);
Exchange.AddExchange("{год}", Marks.Year.ToString(), 5);
var MarkNameIndex = new SColumn_struct(-1, null);
var VGIndex = new SColumn_struct(-1, null);
var EdTypeIndex = new SColumn_struct(-1, null);
var MiddlesMonthIndexes = new SColumn_struct[12];
var MiddlesQIndexes = new SColumn_struct[4];
var MiddleYearMarkIndex = new SColumn_struct(-1, null);
int MarkRowIndex = -1;
int VGRowIndex = -1;
{
int MarkNameRowIndex = -1;
int EdTypeRowIndex = -1;
var MiddlesMonthRowIndexes = new int[12];
var MiddlesQRowIndexes = new int[4];
int MiddleYearMarkRowIndex = -1;
var MiddlesMonthVGRowIndexes = new int[12];
var MiddlesQVGRowIndexes = new int[4];
Exchange.AddExchange("{показатель}", Cell =>
{
MarkNameIndex = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MarkNameRowIndex = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{выпуск}", Cell =>
{
VGIndex = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
VGRowIndex = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{едизм}", Cell =>
{
EdTypeIndex = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
EdTypeRowIndex = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Январь}", Cell =>
{
MiddlesMonthIndexes[0] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[0] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Февраль}", Cell =>
{
MiddlesMonthIndexes[1] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[1] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Март}", Cell =>
{
MiddlesMonthIndexes[2] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[2] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за 1 кв.}", Cell =>
{
MiddlesQIndexes[0] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesQRowIndexes[0] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Апрель}", Cell =>
{
MiddlesMonthIndexes[3] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[3] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Май}", Cell =>
{
MiddlesMonthIndexes[4] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[4] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Июнь}", Cell =>
{
MiddlesMonthIndexes[5] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[5] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за 2 кв.}", Cell =>
{
MiddlesQIndexes[1] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesQRowIndexes[1] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Июль}", Cell =>
{
MiddlesMonthIndexes[6] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[6] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Август}", Cell =>
{
MiddlesMonthIndexes[7] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[7] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Сентябрь}", Cell =>
{
MiddlesMonthIndexes[8] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[8] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за 3 кв.}", Cell =>
{
MiddlesQIndexes[2] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesQRowIndexes[2] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Октябрь}", Cell =>
{
MiddlesMonthIndexes[9] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[9] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Ноябрь}", Cell =>
{
MiddlesMonthIndexes[10] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[10] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за Декабрь}", Cell =>
{
MiddlesMonthIndexes[11] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesMonthRowIndexes[11] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за 4 кв.}", Cell =>
{
MiddlesQIndexes[3] = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddlesQRowIndexes[3] = Cell.RowIndex;
}, 1);
Exchange.AddExchange("{средний за За год}", Cell =>
{
MiddleYearMarkIndex = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
MiddleYearMarkRowIndex = Cell.RowIndex;
}, 1);
if (Marks.PodrID > 0)
{
Exchange.AddExchange("{должность ответственного}", T.People.Rows.Get <string>(RCache.PSG.GetPeopleID((data.PSG)T.Podr.Rows.Get_UnShow <uint>(Marks.PodrID, C.Podr.PSG)), C.People.Prfssn, C.Prfssn.Name), 5);
Exchange.AddExchange("{ФИО ответственного}", Misc.GetShortFIO(RCache.PSG.GetPeopleID((data.PSG)T.Podr.Rows.Get_UnShow <uint>(Marks.PodrID, C.Podr.PSG))), 5);
}
else
{
Exchange.AddExchange("{должность ответственного}", "", 5);
Exchange.AddExchange("{ФИО ответственного}", "", 5);
}
SetResp(Exchange, Marks.PodrID, data.TResp.LaboratoryProtokol);
if (MarkNameRowIndex == EdTypeRowIndex &&
EdTypeRowIndex == MiddlesMonthRowIndexes[0] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[1] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[2] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[3] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[4] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[5] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[6] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[7] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[8] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[9] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[10] &&
EdTypeRowIndex == MiddlesMonthRowIndexes[11] &&
EdTypeRowIndex == MiddlesQRowIndexes[0] &&
EdTypeRowIndex == MiddlesQRowIndexes[1] &&
EdTypeRowIndex == MiddlesQRowIndexes[2] &&
EdTypeRowIndex == MiddlesQRowIndexes[3] &&
EdTypeRowIndex == MiddleYearMarkRowIndex)
{
MarkRowIndex = EdTypeRowIndex;
}
else
{
if (ShowErrorMessage)
{
MessageBox.Show("Не все колонки показателей были найдены или не все из них расположены на одной строке.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
}
Sheet.ShiftRows(MarkRowIndex + 1, Sheet.LastRowNum, -1); //затираю метки
int RowIndex = MarkRowIndex;
for (int i = 0; i < Marks.MarkCount; i++)
{
var Mark = RCache.Marks[Marks.MarkID(i)];
if (Marks.GetMarkEnabled(i) && (Marks.YearSumm(i) > 0 || Mark.ShowZero))
{
Sheet.ShiftRows(RowIndex, Sheet.LastRowNum, 1);
var Row = Sheet.CreateRow(RowIndex++);
//выпуск
ATMisc.SetValue(Row, "", VGIndex.Index, VGIndex.Style);
//наименование
ATMisc.SetValue(Row, Marks.MarkName(i), MarkNameIndex.Index, MarkNameIndex.Style);
//едизм
ATMisc.SetValue(Row, Marks.EdName(i), EdTypeIndex.Index, EdTypeIndex.Style);
//месяцы
for (byte j = 0; j < MiddlesMonthIndexes.Length; j++)
{
ATMisc.SetValue(Row, Mark.GetRoundedVolume(Marks.MonthSumm(i, j)), MiddlesMonthIndexes[j].Index, MiddlesMonthIndexes[j].Style);
}
//кварталы
for (byte j = 0; j < MiddlesQIndexes.Length; j++)
{
ATMisc.SetValue(Row, Mark.GetRoundedVolume(Marks.QuartalSumm(i, j)), MiddlesQIndexes[j].Index, MiddlesQIndexes[j].Style);
}
//за год
ATMisc.SetValue(Row, Mark.GetRoundedVolume(Marks.YearSumm(i)), MiddleYearMarkIndex.Index, MiddleYearMarkIndex.Style);
}
}
string PodrName;
if (Marks.PodrID > 0)
{
PodrName = ' ' + T.Podr.Rows.Get <string>(Marks.PodrID, C.Podr.ShrName);
}
else
{
PodrName = "все";
}
if (!Directory.Exists(Application.StartupPath + "\\Отчеты"))
{
Directory.CreateDirectory(Application.StartupPath + "\\Отчеты");
}
return(SaveExcel(WorkBook, Application.StartupPath + "\\Отчеты\\СП " + PodrName + ", " + ObjectName + ", " + Marks.Year.ToString() + ".xls", Open, ShowErrorMessage));
}
/// <summary>Получить ответственных по типу ответственности для подразделения</summary>
static void SetResp(CellExchange_Class Exchange, uint PodrID, data.TResp RespType)
{
var Prffn = new SColumn_struct(-1, null);
var FIO = new SColumn_struct(-1, null);
int RowIndex;
{
int PrffnRowIndex = -1;
int FIORowIndex = -1;
Exchange.AddExchange("{Профессии ответственных по подразделению}", Cell =>
{
Prffn = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
PrffnRowIndex = Cell.RowIndex;
}, 5);
Exchange.AddExchange("{ФИО ответственных по подразделению}", Cell =>
{
FIO = new SColumn_struct(Cell.ColumnIndex, Cell.CellStyle);
FIORowIndex = Cell.RowIndex;
}, 5);
Exchange.Exchange(0, 50, 0, Exchange.sheet.LastRowNum + 1);
if (PrffnRowIndex == FIORowIndex && FIORowIndex < 0)
{
return;
}
if (PrffnRowIndex > -1 && FIORowIndex > -1 && FIORowIndex != PrffnRowIndex)
{
return;
}
RowIndex = PrffnRowIndex;
}
CellRangeAddress FIOMerge = null;
CellRangeAddress PrffnMerge = null;
G.Resp.QUERRY()
.SHOW.WHERE
.ARC(C.Resp.PodrPpl, C.PodrPpl.Podr).EQUI.BV(PodrID)
.AND.C(C.Resp.TResp, (uint)RespType)
.DO();
if (G.Resp.Rows.Count > 0)
{
for (int i = 0; i < Exchange.sheet.NumMergedRegions; i++)
{
var Merge = Exchange.sheet.GetMergedRegion(i);
if (Merge.FirstRow <= RowIndex && Merge.LastRow >= RowIndex)
{
if (Merge.FirstColumn <= FIO.Index && Merge.LastColumn >= FIO.Index)
{
FIOMerge = Merge;
}
else if (Merge.FirstColumn <= Prffn.Index && Merge.LastColumn >= Prffn.Index)
{
PrffnMerge = Merge;
}
}
}
Exchange.sheet.ShiftRows(RowIndex + 1, Exchange.sheet.LastRowNum, G.Resp.Rows.Count - 1);
G.Resp.Sort((it1, it2) =>
{
var ret1 = T.Resp.Rows.Get_UnShow <uint>(it1.ID, C.Resp.PodrPpl, C.PodrPpl.People, C.People.Prfssn, C.Prfssn.PnMean)
.CompareTo(T.Resp.Rows.Get_UnShow <uint>(it2.ID, C.Resp.PodrPpl, C.PodrPpl.People, C.People.Prfssn, C.Prfssn.PnMean));
return(ret1 == 0 ? it1.ID.CompareTo(it2.ID) : ret1);
});
if (Prffn.Index > -1)
{
var Row = Exchange.sheet.GetRow(RowIndex);
var Cell = Row.GetCell(Prffn.Index);
Cell.SetCellValue(G.Resp.Rows.Get <string>(0, C.Resp.PodrPpl, C.PodrPpl.People, C.People.Prfssn, C.Prfssn.Name));
}
if (FIO.Index > -1)
{
var Row = Exchange.sheet.GetRow(RowIndex);
var Cell = Row.GetCell(FIO.Index);
Cell.SetCellValue(Misc.GetShortFIO(G.Resp.Rows.Get_UnShow <uint>(0, C.Resp.PodrPpl, C.PodrPpl.People)));
}
for (int i = 1; i < G.Resp.Rows.Count; i++)
{
var Row = Exchange.sheet.CreateRow(RowIndex + i);
if (Prffn.Index > -1)
{
var Cell = Row.CreateCell(Prffn.Index);
Cell.CellStyle = Prffn.Style;
Cell.SetCellValue(G.Resp.Rows.Get <string>(i, C.Resp.PodrPpl, C.PodrPpl.People, C.People.Prfssn, C.Prfssn.Name));
if (PrffnMerge != null)
{
Exchange.sheet.AddMergedRegion(new CellRangeAddress(Row.RowNum, Row.RowNum, PrffnMerge.FirstColumn, PrffnMerge.LastColumn));
}
}
if (FIO.Index > -1)
{
var Cell = Row.CreateCell(FIO.Index);
Cell.CellStyle = FIO.Style;
Cell.SetCellValue(Misc.GetShortFIO(G.Resp.Rows.Get_UnShow <uint>(i, C.Resp.PodrPpl, C.PodrPpl.People)));
if (FIOMerge != null)
{
Exchange.sheet.AddMergedRegion(new CellRangeAddress(Row.RowNum, Row.RowNum, FIOMerge.FirstColumn, FIOMerge.LastColumn));
}
}
}
}
else
{
Exchange.sheet.ShiftRows(RowIndex + 1, Exchange.sheet.LastRowNum, G.Resp.Rows.Count - 1);
}
}
