コード例 #1
0
ファイル: AttendanceImport.cs プロジェクト: liamning/PF_New
    //public List<AttendanceInfo> Import_Nike(Stream file, string clientCode, int BU)
    //{
    //    List<AttendanceInfo> attendanceList = new List<AttendanceInfo>();
    //    List<AttendanceInfo> invalidAttendanceList = new List<AttendanceInfo>();
    //    Attendance attendanceObj = new Attendance();
    //    AttendanceInfo tempAttendance;

    //    XSSFWorkbook hssfwb = new XSSFWorkbook(file);
    //    ISheet sheet = hssfwb.GetSheet("Export to PDF");
    //    IRow currentRow;
    //    //ICell currentCell;

    //    string dateFormat = "MM/dd/yyyy";
    //    DateTime startDate = DateTime.MaxValue;
    //    /// string datetimeFormat = "yyyy-MM-dd HH:mm";


    //    string[] workerInfo;



    //    return attendanceList;

    // }

    public void Save(List <AttendanceEntry> attendanceEntries, string clientCode, int BU)
    {
        List <AttendanceInfo> attendanceList        = new List <AttendanceInfo>();
        List <AttendanceInfo> invalidAttendanceList = new List <AttendanceInfo>();
        Attendance            attendanceObj         = new Attendance();
        AttendanceInfo        tempAttendance;
        Worker workerObj = new Worker();

        int        row           = 0;
        DateTime   startDate     = DateTime.MinValue;
        WorkerInfo tmpWorkerInfo = null;

        foreach (var info in attendanceEntries)
        {
            ++row;
            tmpWorkerInfo = workerObj.CheckWorkerWithBU(clientCode, BU, info.WorkerID);
            if (tmpWorkerInfo == null)
            {
                throw new Exception(string.Format("Worker in row {0} is not valid", row));
            }
            tempAttendance               = new AttendanceInfo();
            tempAttendance.WorkerID      = info.WorkerID;
            tempAttendance.Gender        = tmpWorkerInfo.Gender;
            tempAttendance.PositionGrade = tmpWorkerInfo.PositionGrade;

            tempAttendance.Client = clientCode;
            tempAttendance.BU     = BU;

            tempAttendance.AttendanceDate = info.AttendanceDate;
            tempAttendance.TimeSlot       = info.Timeslot;
            tempAttendance.TimeIn         = DateTime.ParseExact(info.AttendanceDate.ToString(GlobalSetting.DateFormat) + " " + info.TimeIn + ":00", GlobalSetting.DateTimeFormat, CultureInfo.InvariantCulture);
            tempAttendance.TimeOut        = DateTime.ParseExact(info.AttendanceDate.ToString(GlobalSetting.DateFormat) + " " + info.TimeOut + ":00", GlobalSetting.DateTimeFormat, CultureInfo.InvariantCulture);
            tempAttendance.Hours          = info.TotalHours;

            startDate = startDate > tempAttendance.AttendanceDate.Value ? tempAttendance.AttendanceDate.Value : startDate;

            attendanceList.Add(tempAttendance);
        }

        attendanceObj.BatchInsert(attendanceList);
    }
コード例 #2
0
ファイル: AttendanceImport.cs プロジェクト: liamning/PF_New
    public List <AttendanceInfo> Import_Zara(Stream file, string clientCode, int BU)
    {
        //get mapping table of desc to timslot
        Dictionary <string, string> descToTimeslot = new TimeslotMapping().GetDescToTimeslot();

        List <AttendanceInfo> attendanceList        = new List <AttendanceInfo>();
        List <AttendanceInfo> invalidAttendanceList = new List <AttendanceInfo>();
        Attendance            attendanceObj         = new Attendance();
        AttendanceInfo        tempAttendance;

        string[] workerInfo;

        XSSFWorkbook hssfwb = new XSSFWorkbook(file);
        // ISheet sheet = hssfwb.GetSheet("每日出勤表");
        ISheet sheet = hssfwb.GetSheetAt(0);
        IRow   currentRow;
        //ICell currentCell;

        string dateFormat     = "yyyy-MM-dd";
        string datetimeFormat = "yyyy-MM-dd HH:mm";

        DateTime firstAttendanceDate = DateTime.MinValue;

        //get the week day
        currentRow = sheet.GetRow(3); //the day of month stored in row 4
        Dictionary <DateTime, int> dateColMapping = new Dictionary <DateTime, int>();
        DateTime workingDate = DateTime.MaxValue;

        for (int col = 2; col < currentRow.LastCellNum - 1; col = col + 2)
        {
            System.Diagnostics.Debug.WriteLine(col);
            if (currentRow.GetCell(col) == null)
            {
                continue;
            }

            if (currentRow.GetCell(col).CellType == CellType.Numeric)
            {
                dateColMapping.Add(currentRow.GetCell(col).DateCellValue, col);
                workingDate = currentRow.GetCell(col).DateCellValue;
            }
            else if (!string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue))
            {
                if (currentRow.GetCell(col).StringCellValue == "Grand Total")
                {
                    break;
                }
                workingDate = DateTime.ParseExact(currentRow.GetCell(col).StringCellValue, dateFormat, CultureInfo.InvariantCulture);
                dateColMapping.Add(workingDate, col);
            }
            else if (string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue))
            {
                break;
            }
        }


        //get the working hours from excel file: data row start at row 8
        for (int row = 7; row < sheet.LastRowNum; row++)
        {
            System.Diagnostics.Debug.WriteLine(row);
            currentRow = sheet.GetRow(row);
            if (currentRow != null) //null is when the row only contains empty cells
            {
                if (currentRow.GetCell(0) == null || string.IsNullOrEmpty(currentRow.GetCell(0).StringCellValue))
                {
                    break;
                }

                foreach (DateTime attendanceDate in dateColMapping.Keys)
                {
                    System.Diagnostics.Debug.WriteLine(attendanceDate.ToString("ddMMyyyy"));
                    if (currentRow.GetCell(dateColMapping[attendanceDate]) == null)
                    {
                        continue;
                    }

                    tempAttendance        = new AttendanceInfo();
                    tempAttendance.Client = clientCode;
                    tempAttendance.BU     = BU;

                    tempAttendance.AttendanceDate = attendanceDate;
                    tempAttendance.ClientStaffNo  = currentRow.GetCell(0).StringCellValue;

                    if (tempAttendance.ClientStaffNo == "Grand Total")
                    {
                        break;
                    }
                    if (!descToTimeslot.ContainsKey(currentRow.GetCell(1).StringCellValue))
                    {
                        tempAttendance.Remarks = string.Format("Unknown timeslot: {0}", currentRow.GetCell(1).StringCellValue);
                        invalidAttendanceList.Add(tempAttendance);
                        continue;
                    }
                    tempAttendance.TimeSlot = descToTimeslot[currentRow.GetCell(1).StringCellValue];
                    tempAttendance.Hours    = currentRow.GetCell(dateColMapping[attendanceDate]).NumericCellValue;

                    //after 22:00
                    if (currentRow.GetCell(dateColMapping[attendanceDate] + 1) != null)
                    {
                        tempAttendance.OTHours = currentRow.GetCell(dateColMapping[attendanceDate] + 1).NumericCellValue;
                    }
                    else
                    {
                        tempAttendance.OTHours = 0;
                    }

                    if (tempAttendance.Hours == 0)
                    {
                        continue;
                    }


                    //get WorkerID /Gender/ PositionGrade
                    workerInfo = attendanceObj.GetWorkderID(clientCode, BU, tempAttendance.ClientStaffNo);
                    if (workerInfo == null)
                    {
                        tempAttendance.Remarks = "Worker Not Found";
                        invalidAttendanceList.Add(tempAttendance);
                        continue;
                    }
                    else
                    {
                        tempAttendance.WorkerID      = workerInfo[0];
                        tempAttendance.Gender        = workerInfo[1];
                        tempAttendance.PositionGrade = workerInfo[2];
                    }

                    attendanceList.Add(tempAttendance);
                }
            }
        }


        if (invalidAttendanceList.Count == 0)
        {
            return(attendanceObj.BatchInsert(attendanceList));
        }
        else
        {
            return(invalidAttendanceList);
        }
    }
コード例 #3
0
ファイル: AttendanceImport.cs プロジェクト: liamning/PF
    public void Import_Zara(Stream file, string clientCode)
    {
        List <AttendanceInfo> attendanceList = new List <AttendanceInfo>();
        Attendance            attendanceObj  = new Attendance();
        AttendanceInfo        tempAttendance;

        XSSFWorkbook hssfwb = new XSSFWorkbook(file);
        ISheet       sheet  = hssfwb.GetSheet("每日出勤表");
        IRow         currentRow;
        //ICell currentCell;

        //Newtonsoft.Json.Converters.IsoDateTimeConverter IsoDateTimeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter { DateTimeFormat = "yyyy-MM-dd" };
        string dateFormat     = "yyyy-MM-dd";
        string datetimeFormat = "yyyy-MM-dd HH:mm";

        for (int row = 1; row <= sheet.LastRowNum; row++)
        {
            System.Diagnostics.Debug.WriteLine(row);

            currentRow = sheet.GetRow(row);
            if (currentRow != null) //null is when the row only contains empty cells
            {
                //考勤日期	小組  員工編號	員工姓名	班次	扣減工時   (分鐘)	上班	下班	考勤工時	考勤工時2	加班	加班2	Total hrs	備注	hrs	22:00後
                if (currentRow.GetCell(0) == null || string.IsNullOrEmpty(currentRow.GetCell(0).StringCellValue))
                {
                    break;
                }
                if (currentRow.GetCell(2) == null)
                {
                    continue;
                }

                tempAttendance                 = new AttendanceInfo();
                tempAttendance.Client          = clientCode;
                tempAttendance.AttendanceDate  = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue, dateFormat, CultureInfo.InvariantCulture);
                tempAttendance.Store           = currentRow.GetCell(1).StringCellValue;
                tempAttendance.ClientStaffNo   = currentRow.GetCell(2).StringCellValue;
                tempAttendance.ClientStaffName = currentRow.GetCell(3).StringCellValue;
                tempAttendance.Remarks         = currentRow.GetCell(4).StringCellValue;

                if (currentRow.GetCell(6).CellType == CellType.Numeric)
                {
                    tempAttendance.TimeIn = currentRow.GetCell(6).DateCellValue;
                }
                else if (!string.IsNullOrEmpty(currentRow.GetCell(6).StringCellValue))
                {
                    tempAttendance.TimeIn = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue + " " + getTimeInExceptionInput(currentRow.GetCell(6).StringCellValue), datetimeFormat, CultureInfo.InvariantCulture);
                }

                if (currentRow.GetCell(7).CellType == CellType.Numeric)
                {
                    tempAttendance.TimeIn = currentRow.GetCell(7).DateCellValue;
                }
                else if (!string.IsNullOrEmpty(currentRow.GetCell(7).StringCellValue))
                {
                    tempAttendance.TimeOut = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue + " " + getTimeInExceptionInput(currentRow.GetCell(7).StringCellValue), datetimeFormat, CultureInfo.InvariantCulture);
                }

                tempAttendance.TotalHours = currentRow.GetCell(12).NumericCellValue;

                attendanceList.Add(tempAttendance);
            }
        }

        attendanceObj.BatchInsert(attendanceList);
    }
コード例 #4
0
ファイル: AttendanceImport.cs プロジェクト: liamning/PF_New
    public List <AttendanceInfo> Import_Nike(Stream file, string clientCode, int BU)
    {
        //get mapping table of desc to timslot
        Dictionary <string, string> descToTimeslot = new TimeslotMapping().GetDescToTimeslot();

        List <AttendanceInfo> attendanceList        = new List <AttendanceInfo>();
        List <AttendanceInfo> invalidAttendanceList = new List <AttendanceInfo>();
        Attendance            attendanceObj         = new Attendance();
        AttendanceInfo        tempAttendance;

        string[] workerInfo;

        XSSFWorkbook hssfwb = new XSSFWorkbook(file);
        // ISheet sheet = hssfwb.GetSheet("每日出勤表");
        ISheet sheet = hssfwb.GetSheetAt(0);
        IRow   currentRow;
        //ICell currentCell;

        string dateFormat     = "yyyy-MM-dd";
        string datetimeFormat = "yyyy-MM-dd HH:mm";

        DateTime firstAttendanceDate = DateTime.MinValue;

        try
        {
            currentRow          = sheet.GetRow(1);
            firstAttendanceDate = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue, dateFormat, CultureInfo.InvariantCulture);
        }
        catch { }

        if (firstAttendanceDate != DateTime.MinValue)
        {
            for (int row = 1; row <= sheet.LastRowNum; row++)
            {
                //System.Diagnostics.Debug.WriteLine(row);

                currentRow = sheet.GetRow(row);
                if (currentRow != null) //null is when the row only contains empty cells
                {
                    //考勤日期	小組  員工編號	員工姓名	班次	扣減工時   (分鐘)	上班	下班	考勤工時	考勤工時2	加班	加班2	Total hrs	備注	hrs	22:00後
                    if (currentRow.GetCell(0) == null || string.IsNullOrEmpty(currentRow.GetCell(0).StringCellValue))
                    {
                        break;
                    }
                    if (currentRow.GetCell(2) == null)
                    {
                        continue;
                    }

                    tempAttendance = new AttendanceInfo();
                    System.Diagnostics.Debug.WriteLine(currentRow.GetCell(0).StringCellValue);

                    tempAttendance.ClientStaffNo = currentRow.GetCell(2).StringCellValue;

                    //get WorkerID /Gender/ PositionGrade
                    workerInfo = attendanceObj.GetWorkderID(clientCode, BU, tempAttendance.ClientStaffNo);
                    tempAttendance.AttendanceDate  = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue, dateFormat, CultureInfo.InvariantCulture);
                    tempAttendance.Client          = clientCode;
                    tempAttendance.BU              = BU;
                    tempAttendance.ClientStaffName = currentRow.GetCell(3).StringCellValue;
                    tempAttendance.Remarks         = currentRow.GetCell(4).StringCellValue;
                    tempAttendance.TimeSlot        = descToTimeslot[currentRow.GetCell(4).StringCellValue];
                    tempAttendance.Hours           = currentRow.GetCell(12).NumericCellValue;

                    if (workerInfo == null)
                    {
                        tempAttendance.Remarks = "Worker Not Found";
                        invalidAttendanceList.Add(tempAttendance);
                        continue;
                    }
                    else
                    {
                        tempAttendance.WorkerID      = workerInfo[0];
                        tempAttendance.Gender        = workerInfo[1];
                        tempAttendance.PositionGrade = workerInfo[2];
                    }


                    //time in
                    if (currentRow.GetCell(6).CellType == CellType.Numeric)
                    {
                        tempAttendance.TimeIn = currentRow.GetCell(6).DateCellValue;
                    }
                    else if (!string.IsNullOrEmpty(currentRow.GetCell(6).StringCellValue))
                    {
                        tempAttendance.TimeIn = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue + " " + getTimeInExceptionInput(currentRow.GetCell(6).StringCellValue), datetimeFormat, CultureInfo.InvariantCulture);
                    }

                    //time out
                    if (currentRow.GetCell(7).CellType == CellType.Numeric)
                    {
                        tempAttendance.TimeIn = currentRow.GetCell(7).DateCellValue;
                    }
                    else if (!string.IsNullOrEmpty(currentRow.GetCell(7).StringCellValue))
                    {
                        tempAttendance.TimeOut = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue + " " + getTimeInExceptionInput(currentRow.GetCell(7).StringCellValue), datetimeFormat, CultureInfo.InvariantCulture);
                    }


                    attendanceList.Add(tempAttendance);
                }
            }
        }
        else
        {
            //get the week day
            currentRow = sheet.GetRow(1);
            Dictionary <DateTime, int> dateColMapping = new Dictionary <DateTime, int>();
            DateTime workingDate = DateTime.MaxValue;
            for (int col = 3; col < currentRow.LastCellNum - 1; col++)
            {
                System.Diagnostics.Debug.WriteLine(col);
                if (currentRow.GetCell(col) == null)
                {
                    continue;
                }

                if (currentRow.GetCell(col).CellType == CellType.Numeric)
                {
                    dateColMapping.Add(currentRow.GetCell(col).DateCellValue, col);
                    workingDate = currentRow.GetCell(col).DateCellValue;
                }
                else if (!string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue))
                {
                    if (currentRow.GetCell(col).StringCellValue == "Grand Total")
                    {
                        break;
                    }
                    workingDate = DateTime.ParseExact(currentRow.GetCell(col).StringCellValue, dateFormat, CultureInfo.InvariantCulture);
                    dateColMapping.Add(workingDate, col);
                }
            }

            //get the working hours from excel file
            for (int row = 3; row < sheet.LastRowNum; row++)
            {
                System.Diagnostics.Debug.WriteLine(row);
                currentRow = sheet.GetRow(row);
                if (currentRow != null) //null is when the row only contains empty cells
                {
                    if (currentRow.GetCell(1) == null || string.IsNullOrEmpty(currentRow.GetCell(1).StringCellValue))
                    {
                        continue;
                    }

                    foreach (DateTime attendanceDate in dateColMapping.Keys)
                    {
                        System.Diagnostics.Debug.WriteLine(attendanceDate.ToString("ddMMyyyy"));
                        if (currentRow.GetCell(dateColMapping[attendanceDate]) == null)
                        {
                            continue;
                        }

                        tempAttendance        = new AttendanceInfo();
                        tempAttendance.Client = clientCode;
                        tempAttendance.BU     = BU;

                        tempAttendance.AttendanceDate = attendanceDate;
                        tempAttendance.ClientStaffNo  = currentRow.GetCell(1).StringCellValue;
                        if (!descToTimeslot.ContainsKey(currentRow.GetCell(2).StringCellValue))
                        {
                            tempAttendance.Remarks = string.Format("Unknown timeslot: {0}", currentRow.GetCell(2).StringCellValue);
                            invalidAttendanceList.Add(tempAttendance);
                            continue;
                        }
                        tempAttendance.TimeSlot = descToTimeslot[currentRow.GetCell(2).StringCellValue];
                        tempAttendance.Hours    = currentRow.GetCell(dateColMapping[attendanceDate]).NumericCellValue;

                        if (tempAttendance.Hours == 0)
                        {
                            continue;
                        }


                        //get WorkerID /Gender/ PositionGrade
                        workerInfo = attendanceObj.GetWorkderID(clientCode, BU, tempAttendance.ClientStaffNo);
                        if (workerInfo == null)
                        {
                            tempAttendance.Remarks = "Worker Not Found";
                            invalidAttendanceList.Add(tempAttendance);
                            continue;
                        }
                        else
                        {
                            tempAttendance.WorkerID      = workerInfo[0];
                            tempAttendance.Gender        = workerInfo[1];
                            tempAttendance.PositionGrade = workerInfo[2];
                        }

                        attendanceList.Add(tempAttendance);
                    }
                }
            }
        }


        if (invalidAttendanceList.Count == 0)
        {
            return(attendanceObj.BatchInsert(attendanceList));
        }
        else
        {
            return(invalidAttendanceList);
        }
    }
コード例 #5
0
ファイル: AttendanceImport.cs プロジェクト: liamning/PF
    public void Import_Nike(Stream file, string clientCode)
    {
        List <AttendanceInfo> attendanceList = new List <AttendanceInfo>();
        Attendance            attendanceObj  = new Attendance();
        AttendanceInfo        tempAttendance;

        XSSFWorkbook hssfwb = new XSSFWorkbook(file);
        ISheet       sheet  = hssfwb.GetSheet("Export to PDF");
        IRow         currentRow;
        //ICell currentCell;

        string dateFormat = "MM/dd/yyyy";

        /// string datetimeFormat = "yyyy-MM-dd HH:mm";

        //get the week day
        currentRow = sheet.GetRow(1);
        Dictionary <DateTime, int> dateColMapping = new Dictionary <DateTime, int>();
        DateTime workingDate;

        for (int col = 4; col < currentRow.LastCellNum - 1; col++)
        {
            System.Diagnostics.Debug.WriteLine(col);
            if (currentRow.GetCell(col) == null)
            {
                continue;
            }

            if (currentRow.GetCell(col).CellType == CellType.Numeric)
            {
                dateColMapping.Add(currentRow.GetCell(col).DateCellValue, col);
            }
            else if (!string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue))
            {
                //if (currentRow.GetCell(col).StringCellValue == "Grand Total") break;
                workingDate = DateTime.ParseExact(currentRow.GetCell(col).StringCellValue, dateFormat, CultureInfo.InvariantCulture);
                dateColMapping.Add(workingDate, col);
            }
        }

        for (int row = 3; row < sheet.LastRowNum; row++)
        {
            System.Diagnostics.Debug.WriteLine(row);
            currentRow = sheet.GetRow(row);
            if (currentRow != null) //null is when the row only contains empty cells
            {
                if (currentRow.GetCell(1) == null || string.IsNullOrEmpty(currentRow.GetCell(1).StringCellValue))
                {
                    continue;
                }

                foreach (DateTime attendanceDate in dateColMapping.Keys)
                {
                    System.Diagnostics.Debug.WriteLine(attendanceDate.ToString("ddMMyyyy"));
                    if (currentRow.GetCell(dateColMapping[attendanceDate]) == null)
                    {
                        continue;
                    }

                    tempAttendance                = new AttendanceInfo();
                    tempAttendance.Client         = clientCode;
                    tempAttendance.AttendanceDate = attendanceDate;
                    tempAttendance.ClientStaffNo  = currentRow.GetCell(1).StringCellValue;
                    tempAttendance.TotalHours     = currentRow.GetCell(dateColMapping[attendanceDate]).NumericCellValue;

                    if (tempAttendance.TotalHours == 0)
                    {
                        continue;
                    }

                    attendanceList.Add(tempAttendance);
                }
            }
        }

        attendanceObj.BatchInsert(attendanceList);
    }