//Add Employee Clocking Information
 public void AddEmpClockingInfo(int empTrackerId, EmployeeClockingDTO employeeClockingDTO)
 {
     DataBaseUtility db = new DataBaseUtility();
     string query = "INSERT INTO [dbo].[EmployeeClocking]([EmployeeTrackerId],[ClockFunctionTypeId],[ClockingTime],[MinutesWorked],[CreatedDateTime],[LastUpdateDateTime]) "
                 + " VALUES(" + SQLUtility.getInteger(empTrackerId) + "," + SQLUtility.getInteger(employeeClockingDTO.ClockFunctionTypeId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeClockingDTO.ClockingTime) + "', "
                 + " " + SQLUtility.getInteger(employeeClockingDTO.MinutesWorked) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
     db.ExecuteUpdate(query);
 }
        // Employee  Clocking Information of each business day for every night
        public ArrayList GetEmployeClocking(int empId, DateTime businessDate, string connetionString)
        {
            ArrayList list = new ArrayList();

            StoreRemoteDataBaseUitility remoteDB = new StoreRemoteDataBaseUitility(connetionString);

            SqlConnection con = null;

            try
            {

                string query = "select EmployeeId,ClockFunctionTypeId,ClockingTime,MinutesWorked from dbo.UserClocking   where  ClockingTime  "
                               + "between '" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(businessDate.AddDays(1)) + "' and EmployeeId = " + SQLUtility.getInteger(empId) + " ";

                con = remoteDB.OpenConnection();

                SqlCommand comm = remoteDB.getSQLCommand(query, con);

                SqlDataReader reader = comm.ExecuteReader();

                EmployeeClockingDTO employeeClockingDTO = null;

                while (reader.Read())
                {
                    employeeClockingDTO = new EmployeeClockingDTO();
                    employeeClockingDTO.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
                    employeeClockingDTO.ClockFunctionTypeId = ValidationUtility.ToInteger(reader[1].ToString());

                    employeeClockingDTO.ClockingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));

                    // employeeClockingDTO.ClockingTime = ValidationUtility.ToDate(reader[2].ToString());
                    employeeClockingDTO.MinutesWorked = ValidationUtility.ToInteger(reader[3].ToString());
                    employeeClockingDTO.BusinessDate = businessDate;
                    list.Add(employeeClockingDTO);

                }

                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                log.Error("Exception in GetEmployeClocking Method ", ex);
            }

            finally
            {
                remoteDB.CloseConnection(con);
            }

            return list;
        }
        //Get Employee Clocking Time
        public ArrayList GetEmpClockingTime(EmployeeInfoDTO employeeInfoDTO, DateTime selectDate)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(employeeInfoDTO.ConnectionString);

            DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);

            SqlConnection conn = null;
            ArrayList list = new ArrayList();

            try
            {
                conn = remoteDataBase.OpenConnection();
                for (int i = 0; i < 7; i++)
                {

                    string query = "select EmployeeId,ClockFunctionTypeId,ClockingTime,MinutesWorked from dbo.UserClocking   where  ClockingTime  "
                                   + "between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(i)) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(i + 1)) + "' and EmployeeId = " + SQLUtility.getInteger(employeeInfoDTO.EmpId) + " ";

                    SqlCommand comm = remoteDataBase.getSQLCommand(query, conn);
                    SqlDataReader reader = comm.ExecuteReader();
                    EmployeeClockingDTO employeeClockingDTO = null;
                    while (reader.Read())
                    {
                        employeeClockingDTO = new EmployeeClockingDTO();
                        employeeClockingDTO.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
                        employeeClockingDTO.ClockFunctionTypeId = ValidationUtility.ToInteger(reader[1].ToString());
                        employeeClockingDTO.ClockingTime = ValidationUtility.ToDate(reader[2].ToString());
                        employeeClockingDTO.MinutesWorked = ValidationUtility.ToInteger(reader[3].ToString());
                        employeeClockingDTO.BusinessDate = weekStartDate.AddDays(i);
                        list.Add(employeeClockingDTO);

                    }
                    //if (EmployeeClockingDTO==null)
                    //{

                    //    EmployeeClockingDTO = new EmployeeClockingDTO();
                    //    EmployeeClockingDTO.BusinessDate = weekStartDate.AddDays(i);

                    //    list.Add(EmployeeClockingDTO);
                    //}

                    reader.Close();
                    comm.Dispose();

                }
            }
            catch (Exception ex)
            {

                log.Error("Exception in GetEmpClockingTime Method  ", ex);
            }
            finally
            {
                remoteDataBase.CloseConnection(conn);
            }

            return list;
        }
        public List<EmployeeClockingDTO> GetClockingList(int trakId)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection conn = null;

            List<EmployeeClockingDTO> empClokingList = new List<EmployeeClockingDTO>();

            try
            {
                string query = "select id,ClockingTime,ClockFunctionTypeId,MinutesWorked from EmployeeClocking where EmployeeTrackerId=" + SQLUtility.getInteger(trakId) + " ";

                conn = db.OpenConnection();

                SqlCommand comm = db.getSQLCommand(query, conn);

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    int id = ValidationUtility.ToInteger(reader["Id"].ToString());

                    DateTime clokingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));

                    //  DateTime clokingTime = ValidationUtility.ToDate(reader["ClockingTime"].ToString());

                    int funtion = ValidationUtility.ToInteger(reader["ClockFunctionTypeId"].ToString());

                    int minutesWork = ValidationUtility.ToInteger(reader["MinutesWorked"].ToString());

                    EmployeeClockingDTO dto = new EmployeeClockingDTO { Id = id, ClockFunctionTypeId = funtion, ClockingTimeToString = clokingTime.ToString("hh:mm tt"), MinutesWorked = minutesWork };

                    empClokingList.Add(dto);
                }
                reader.Close();
                comm.Dispose();

            }
            catch (Exception ex)
            {

                log.Error("Exception in GetEmployeeTrackingList Method", ex);
            }

            finally
            {
                db.CloseConnection(conn);
            }

            return empClokingList;
        }
Пример #5
0
        public List<EmployeeClockingDTO> GetClockingListPerDay(int empInfoId,DateTime businessDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection conn = null;

            List<EmployeeClockingDTO> empClokingList = new List<EmployeeClockingDTO>();

            try
            {
                string query = " select ec.id,ec.ClockingTime,ec.ClockFunctionTypeId,ec.MinutesWorked from dbo.EmployeeClocking ec where "
                               + " ec.EmployeeTrackerId = (select Id from dbo.EmployeeTracker et where  et.BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' and  et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + ")";

                conn = db.OpenConnection();

                SqlCommand comm = db.getSQLCommand(query, conn);

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    int id = ValidationUtility.ToInteger(reader["Id"].ToString());

                    DateTime clokingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));

                 //   DateTime clokingTime = ValidationUtility.ToDate(reader["ClockingTime"].ToString());

                    int funtion = ValidationUtility.ToInteger(reader["ClockFunctionTypeId"].ToString());

                    int minutesWork = ValidationUtility.ToInteger(reader["MinutesWorked"].ToString());

                    EmployeeClockingDTO dto = new EmployeeClockingDTO { Id = id, ClockFunctionTypeId = funtion, ClockingTimeToString = clokingTime.ToString("hh:mm tt"), MinutesWorked = minutesWork };

                    empClokingList.Add(dto);
                }

                if (empClokingList.Count == 0 && businessDate.Date < DateTime.Now.Date)
                {
                    EmployeeClockingDTO dto = new EmployeeClockingDTO { ClockingTimeToString ="N/A" };

                    empClokingList.Add(dto);

                }

                reader.Close();
                comm.Dispose();

            }
            catch (Exception ex)
            {

                log.Error("Exception in GetEmployeeTrackingList Method", ex);
            }

            finally
            {
                db.CloseConnection(conn);
            }

            return empClokingList;
        }