예제 #1
0
        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;
        }
예제 #3
0
        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;
        }
예제 #4
0
        private bool IsAuthorizedToModifyDepartment(Department department)
        {
            if (HttpContext.Current.User.IsInRole("manage-all"))
            {
                return true;
            }

            return false;
        }
예제 #5
0
        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;
        }