private ExcelWorksheet WriteDepartmentLabel(ExcelWorksheet sheet, Department department)
{
#region Department Name
Row++;
ExcelRange range_departmentName = sheet.Cells[Row, 1, Row, NumberOfColumns];
range_departmentName.Merge = true;
range_departmentName.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Italic));
range_departmentName.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
range_departmentName.Value = department.Name;
range_departmentName.Style.Font.Color.SetColor(Color.White);
range_departmentName.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range_departmentName.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 140, 186));
#endregion
return sheet;
}
private ExcelWorksheet WriteDepartmentData(ExcelWorksheet sheet,
Department department, IEnumerable<Salary> salaries)
{
int column = 0;
#region Department Name
Row++;
ExcelRange range_departmentName = sheet.Cells[Row, 1, Row, NUM_COLUMNS];
range_departmentName.Merge = true;
range_departmentName.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Italic));
range_departmentName.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
range_departmentName.Value = department.Name;
#endregion
#region Department salaries
Row++;
column = 0;
sheet.Cells[Row, ++column].Value = "Teaching / Research";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[2]-RC[1]";
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 1)
.Sum(s => s.PromotionAmount);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 1)
.Sum(s => s.TotalAmount);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 1)
.Sum(s => s.MeritIncrease);
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-2] = 0, 0, RC[-1]/RC[-2])";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[-2]+RC[-4]";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-6] = 0, 0, RC[-1]/RC[-6])";
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 1)
.Sum(s => s.SpecialIncrease);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 1)
.Sum(s => s.NewTotalAmount);
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-7] = 0, 0, RC[-1]/RC[-7]-1)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-10] = 0, 0, (RC[-5]+RC[-3])/RC[-10])";
Row++;
column = 0;
sheet.Cells[Row, ++column].Value = "Research Faculty";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[2]-RC[1]";
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 3)
.Sum(s => s.PromotionAmount);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 3)
.Sum(s => s.TotalAmount);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 3)
.Sum(s => s.MeritIncrease);
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-2] = 0, 0, RC[-1]/RC[-2])";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[-2]+RC[-4]";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-6] = 0, 0, RC[-1]/RC[-6])";
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 3)
.Sum(s => s.SpecialIncrease);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 3)
.Sum(s => s.NewTotalAmount);
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-7] = 0, 0, RC[-1]/RC[-7]-1)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-10] = 0, 0, (RC[-5]+RC[-3])/RC[-10])";
Row++;
ExcelRange range_subsetData = sheet.Cells[Row, 1, Row + 1, NUM_COLUMNS];
range_subsetData.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Regular));
range_subsetData.Style.Font.Color.SetColor(Color.Black);
range_subsetData.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range_subsetData.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(200, 200, 200));
column = 0;
sheet.Cells[Row, ++column].Value = "Admin / Professional";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[2]-RC[1]";
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 2)
.Sum(s => s.PromotionAmount);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 2)
.Sum(s => s.TotalAmount);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 2)
.Sum(s => s.MeritIncrease);
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-2] = 0, 0, RC[-1]/RC[-2])";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[-2]+RC[-4]";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-6] = 0, 0, RC[-1]/RC[-6])";
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 2)
.Sum(s => s.SpecialIncrease);
sheet.Cells[Row, ++column].Value = salaries
.Where(s => s.FacultyTypeId == 2)
.Sum(s => s.NewTotalAmount);
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-7] = 0, 0, RC[-1]/RC[-7]-1)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-10] = 0, 0, (RC[-5]+RC[-3])/RC[-10])";
Row++;
column = 0;
sheet.Cells[Row, ++column].Value = "T/R and Research";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[2]-RC[1]";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-2] = 0, 0, RC[-1]/RC[-2])";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[-2]+RC[-4]";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-6] = 0, 0, RC[-1]/RC[-6])";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-7] = 0, 0, RC[-1]/RC[-7]-1)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-10] = 0, 0, (RC[-5]+RC[-3])/RC[-10])";
Row++;
ExcelRange range_summaryData = sheet.Cells[Row, 1, Row, NUM_COLUMNS];
range_summaryData.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Regular));
range_summaryData.Style.Font.Color.SetColor(Color.Black);
range_summaryData.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range_summaryData.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(155, 194, 230));
column = 0;
sheet.Cells[Row, ++column].Value = "Department Total";
sheet.Cells[Row, ++column].FormulaR1C1 = "RC[2]-RC[1]";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-4]C,R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-4]C,R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-4]C,R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-2] = 0, 0, RC[-1]/RC[-2])";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-4]C,R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-6] = 0, 0, RC[-1]/RC[-6])";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-4]C,R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(R[-4]C,R[-3]C,R[-2]C)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-7] = 0, 0, RC[-1]/RC[-7]-1)";
sheet.Cells[Row, ++column].FormulaR1C1 = "IF(RC[-10] = 0, 0, (RC[-5]+RC[-3])/RC[-10])";
#endregion
return sheet;
}
private ExcelWorksheet WriteDepartmentData(ExcelWorksheet sheet,
Department department, IEnumerable<Salary> salaries)
{
int column = 0;
#region Department Name
Row++;
ExcelRange range_departmentName = sheet.Cells[Row, 1, Row, NUM_COLUMNS];
range_departmentName.Merge = true;
range_departmentName.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Italic));
range_departmentName.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
range_departmentName.Value = department.Name;
#endregion
#region Department salaries
if (salaries.Count() == 0)
{
Row++;
ExcelRange range_departmentData = sheet.Cells[Row, 1, Row, NUM_COLUMNS];
range_departmentData.Merge = true;
range_departmentData.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Regular));
range_departmentData.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
range_departmentData.Value = "No records";
}
else
{
var summary = GetMeritSummary(salaries);
Row++;
column = 0;
sheet.Cells[Row, ++column].Value = summary["max_increase"];
sheet.Cells[Row, ++column].Value = summary["max_increase_percentage"];
sheet.Cells[Row, ++column].Value = summary["median"];
sheet.Cells[Row, ++column].Value = summary["mean"];
sheet.Cells[Row, ++column].Value = summary["min_increase"];
sheet.Cells[Row, ++column].Value = summary["min_increase_percentage"];
}
#endregion
return sheet;
}
private bool IsAuthorizedToModifyDepartment(Department department)
{
if (HttpContext.Current.User.IsInRole("manage-all"))
{
return true;
}
return false;
}
private ExcelWorksheet WriteDepartmentData(ExcelWorksheet sheet, Department department,
IEnumerable<Salary> salaries)
{
int column = 0;
#region Department Name
Row++;
ExcelRange range_departmentName = sheet.Cells[Row, 1, Row, NUM_COLUMNS];
range_departmentName.Merge = true;
range_departmentName.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Italic));
range_departmentName.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
range_departmentName.Value = department.Name;
#endregion
#region Department salaries
if (salaries.Count() == 0)
{
Row++;
ExcelRange range_departmentData = sheet.Cells[Row, 1, Row, NUM_COLUMNS];
range_departmentData.Merge = true;
range_departmentData.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Regular));
range_departmentData.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
range_departmentData.Value = "No records";
}
else
{
foreach (var salary in salaries)
{
Row++;
column = 0;
sheet.Cells[Row, ++column].Value = salary.Person.FullName;
sheet.Cells[Row, ++column].Value = salary.Person.StatusType.Name;
sheet.Cells[Row, ++column].Value = salary.RankType.Name;
sheet.Cells[Row, ++column].Value = salary.AppointmentType.Name;
sheet.Cells[Row, ++column].Value = salary.FullTimeEquivalent;
sheet.Cells[Row, ++column].Value = salary.BaseAmount;
sheet.Cells[Row, ++column].Value = salary.AdminAmount;
sheet.Cells[Row, ++column].Value = salary.EminentAmount;
sheet.Cells[Row, ++column].Value = salary.PromotionAmount;
sheet.Cells[Row, ++column].FormulaR1C1 = "SUM(RC[-4]:RC[-1])";
sheet.Cells[Row, ++column].Value = salary.MeritIncrease;
sheet.Cells[Row, ++column].Value = salary.SpecialIncrease;
sheet.Cells[Row, ++column].Value = salary.EminentIncrease;
sheet.Cells[Row, ++column].FormulaR1C1 =
"RC[-6]+(RC[-6]/RC[-4])*(RC[-3]+RC[-2])+RC[-1]";
sheet.Cells[Row, ++column].FormulaR1C1 =
"RC[-9]+RC[-8]+RC[-7]+RC[-6]+RC[-4]+RC[-3]+RC[-2]";
sheet.Cells[Row, ++column].FormulaR1C1 =
"RC[-1]/RC[-6]-1";
}
}
#endregion
return sheet;
}
