public WorkHourItem Clone() { WorkHourItem other = new WorkHourItem(); other.Name = this.Name; other.EnterDate = this.EnterDate; other.LeaveDate = this.LeaveDate; other.Month = this.Month; other.Department = this.Department; other.System = this.System; other.StaffMonth = this.StaffMonth; return(other); }
public static void CalcStaffMonth(Microsoft.Office.Interop.Excel.Worksheet sheet) { WORK_HOUR_ITEM_LIST.Clear(); int nameColumnIndex = 0; int enterColumnIndex = 0; int leaveColumnIndex = 0; int departmentColumnIndex = 0; int monthStartColumnIndex = 0; DateTime[] months = new DateTime[12 * 3]; #region locate column index for (int i = 1; i < 10; i++) { string cellValue = sheet.Cells[1, i].Value != null ? sheet.Cells[1, i].Value.ToString() : string.Empty; if (cellValue == "员工姓名") { nameColumnIndex = i; } else if (cellValue == "入场日期") { enterColumnIndex = i; } else if (cellValue == "离场日期") { leaveColumnIndex = i; } else if (cellValue == "所属中心") { departmentColumnIndex = i; } else if (monthStartColumnIndex == 0) { try { Convert.ToDateTime(cellValue); monthStartColumnIndex = i; for (int j = 0; j < 12 * 3; j++) { int validColNum = monthStartColumnIndex + j - j % 3; months[j] = Convert.ToDateTime(sheet.Cells[1, validColNum].Value); } } catch (Exception) { Trace.WriteLine("not valid date format: " + cellValue); } } } // for (int i = 1; i < 10; i++) #endregion // locate column index #region read date from sheet for (int rowIndex = 2; rowIndex < 500; rowIndex++) { WorkHourItem masterItem = new WorkHourItem(); // name masterItem.Name = sheet.Cells[rowIndex, nameColumnIndex].Value; if (string.IsNullOrWhiteSpace(masterItem.Name)) { break; // reach the end of list } // enter date try { masterItem.EnterDate = Convert.ToDateTime(sheet.Cells[rowIndex, enterColumnIndex].Value); } catch (Exception) { MessageBox.Show("invalid date format. " + masterItem.Name + " " + sheet.Cells[rowIndex, enterColumnIndex].Value); } // leave date try { masterItem.LeaveDate = Convert.ToDateTime(sheet.Cells[rowIndex, leaveColumnIndex].Value); } catch (Exception) { MessageBox.Show("invalid date format. " + masterItem.Name + " " + sheet.Cells[rowIndex, leaveColumnIndex].Value); } // department masterItem.Department = sheet.Cells[rowIndex, departmentColumnIndex].Value; // system name for (int colIndex = monthStartColumnIndex; colIndex < monthStartColumnIndex + 12 * 3; colIndex++) { WorkHourItem item = masterItem.Clone(); item.System = sheet.Cells[rowIndex, colIndex].Value; if (string.IsNullOrWhiteSpace(item.System)) { continue; } item.Month = months[colIndex - monthStartColumnIndex]; WORK_HOUR_ITEM_LIST.Add(item); } } // for (int i = 0; i < 500; i++) #endregion // read date from sheet Dictionary <string, List <WorkHourItem> > nameMonthMap = new Dictionary <string, List <WorkHourItem> >(); Dictionary <string, WorkHourItem> systemMap = new Dictionary <string, WorkHourItem>(); List <WorkHourItem> removalItemList = new List <WorkHourItem>(); #region calculate staff month // // build the name-month map foreach (var nextItem in WORK_HOUR_ITEM_LIST) { string key = string.Format("{0}.{1}", nextItem.Name, nextItem.Month); if (!nameMonthMap.ContainsKey(key)) { nameMonthMap[key] = new List <WorkHourItem>(); } nameMonthMap[key].Add(nextItem); } // // calculate the work hours for each system foreach (var nextKey in nameMonthMap.Keys) { double averageStaffMonth = Math.Round((double)1 / nameMonthMap[nextKey].Count, 2); systemMap.Clear(); for (int i = 0; i < nameMonthMap[nextKey].Count; i++) { WorkHourItem nextItem = nameMonthMap[nextKey][i]; // calculate the staff month if (i == nameMonthMap[nextKey].Count - 1) // the last one { nextItem.StaffMonth = Math.Round(1 - averageStaffMonth * (nameMonthMap[nextKey].Count - 1), 2); } else { nextItem.StaffMonth = averageStaffMonth; } // check duplicate system if (systemMap.ContainsKey(nextItem.System)) { systemMap[nextItem.System].StaffMonth += nextItem.StaffMonth; removalItemList.Add(nextItem); } else { systemMap[nextItem.System] = nextItem; } } // for (int i = 0; i < nameMonthMap[nextKey].Count; i++) } // foreach (var nextKey in nameMonthMap.Keys) // // remove the duplicat items foreach (var nextItem in removalItemList) { WORK_HOUR_ITEM_LIST.Remove(nextItem); } // // sort by system, name, month WORK_HOUR_ITEM_LIST.Sort((x, y) => { if (x.System != y.System) { return(x.System.CompareTo(y.System)); } if (x.Name != y.Name) { return(x.Name.CompareTo(y.Name)); } return(x.Month.CompareTo(y.Month)); }); #endregion // calculate staff month }