private void WriteUnsualInfo(List <AttendanceInfo> unsualInfoList, List <PersonInfo> noShowList, Excel.Worksheet sheet) { int rowIndex = 1; int colIndex = 1; // unsual attendance foreach (var nextInfo in unsualInfoList) { PersonInfo personInfo = PersonInfoRepo.GetPersonInfo(nextInfo.Name); if (personInfo == null) { Trace.WriteLine("cannot find " + nextInfo.Name); continue; } // check if the person has left //if (!string.IsNullOrWhiteSpace(personInfo.LeaveDate) // && (personInfo.LeaveDate != "长期")) //{ // Trace.WriteLine(personInfo.Name + " has left at " + personInfo.LeaveDate); // continue; //} rowIndex++; // from row #2 colIndex = 1; this.WriteUnsualAttendanceRow(sheet, rowIndex, colIndex, nextInfo, personInfo); } // foreach (var nextInfo in unsualInfoList) // no show list foreach (var nextInfo in noShowList) { rowIndex++; colIndex = 1; AttendanceInfo attendanceInfo = new AttendanceInfo(nextInfo.Name, string.Empty, string.Empty, string.Empty, string.Empty); attendanceInfo.State = AttendanceState.NoShow; this.WriteUnsualAttendanceRow(sheet, rowIndex, colIndex, attendanceInfo, nextInfo); } // foreach (var nextInfo in noShowList) }
private void WriteFinalSettlement(Excel.Worksheet sheet) { int rowIndex = 1; int colIndex = 1; #region header Excel.Range objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "序号"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "公司名称"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "人力成本归集口径"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "参与项目名称"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "所属中心"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "人员姓名"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "人员级别"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "单价\r\n(万元/人月)"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "绩效系数"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "项目经理"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "数量\r\n(人月)"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "结算金额\r\n(元)"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "外包形式"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; #endregion // header #region rows int errCounter = 0; List <string> nameList = SettleUtil.GetNameList(); foreach (var nextName in nameList) { rowIndex++; // from row #2 colIndex = 1; PersonInfo personInfo = PersonInfoRepo.GetPersonInfo(nextName); if (personInfo == null) { //Debug.Assert(false, nextInfo.Name + " dos not exist"); //Trace.WriteLine("cannot find " + nextName); if (errCounter++ < 10) { MessageBox.Show("cannot find " + nextName); } if (errCounter == 10) { MessageBox.Show("Too many names (>10) cannot be found!"); } continue; } double unitPrice = 0; double workLoad = 0; double performance = 0; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "序号"; objRange.Value = string.Empty; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "公司名称"; objRange.Value = personInfo.Company; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "人力成本归集口径"; objRange.Value = personInfo.CostProject; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "参与项目名称"; objRange.Value = string.IsNullOrWhiteSpace(personInfo.System) ? personInfo.BizProject : personInfo.System; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "所属中心"; objRange.Value = personInfo.Department; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "人员姓名"; objRange.Value = personInfo.Name; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "人员级别"; objRange.Value = personInfo.Rank; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "单价:万元/月"; if (personInfo.WorkType == "人力") { unitPrice = SettleUtil.GetUnitPrice(personInfo.Company, personInfo.Department.Contains("测试") ? "测试" : "开发", personInfo.Rank); } objRange.Value = unitPrice; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "绩效系数"; performance = SettleUtil.GetPerformance(nextName); objRange.Value = performance; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "项目经理"; objRange.Value = personInfo.Manager; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "数量:月"; workLoad = SettleUtil.GetSettlementMonth(personInfo.Name); objRange.Value = workLoad; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "结算金额:元"; objRange.Value = string.Format("{0:0.00}", unitPrice * 10000 * performance * workLoad); objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "备注"; objRange.Value = personInfo.WorkType; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } // foreach (var nextName in nameList) #endregion // rows }
private void WriteWorkLoadTotally(List <WorkloadInfo> workloadList, Excel.Worksheet sheet) { int rowIndex = 1; int colIndex = 1; #region Header Excel.Range objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "姓名"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "所属公司"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "项目组"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "所属系统"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "主管项目经理"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "所属中心"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "结算人月"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //sheet.Columns[colIndex - 1].Numberformat = "@"; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "迟到折算旷工天数"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "加班小时数"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; objRange.Value = "备注"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; #endregion // Header #region rows foreach (var nextInfo in workloadList) { rowIndex++; // from row #2 colIndex = 1; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "姓名"; objRange.Value = nextInfo.Name; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; PersonInfo personInfo = PersonInfoRepo.GetPersonInfo(nextInfo.Name); if (personInfo == null) { //Debug.Assert(false, nextInfo.Name + " dos not exist"); Trace.WriteLine("cannot find " + nextInfo.Name); continue; } objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "所属公司"; objRange.Value = personInfo.Company; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "项目组"; objRange.Value = personInfo.BizProject; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "所属系统"; objRange.Value = personInfo.System; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "主管项目经理"; objRange.Value = personInfo.Manager; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "所属中心"; objRange.Value = personInfo.Department; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "结算人月"; objRange.Value = string.Format("{0:0.00}", nextInfo.PayStaffMonth); objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "迟到折算旷工天数"; objRange.Value = nextInfo.LateTimes.ToString(); objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "加班小时数"; objRange.Value = nextInfo.OTHours.ToString(); objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; objRange = sheet.Cells[rowIndex, colIndex++]; //objRange.Value = "备注"; objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } // foreach (var nextInfo in workloadList) #endregion // rows }