C# DataBaseUtility.CloseConnection方法代码示例

        // Get AreaManager AssignStore Information
        public ArrayList GetAreaMngAssignStore(int userId)
        {
            ArrayList assigneStore = new ArrayList();

            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            try
            {

                string query = "select su.Id,s.StoreName,s.Id from dbo.StoreUser su, dbo.Store s where s.Id = su.StoreId and su.UserId = " + SQLUtility.getInteger(userId) + " ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    int uId = ValidationUtility.ToInteger(reader[0].ToString());
                    string storeName = reader[1].ToString();
                    int sId = ValidationUtility.ToInteger(reader[2].ToString());
                    StoreDTO dto = new StoreDTO { Id = uId, StoreName = storeName, StoreId = sId };
                    assigneStore.Add(dto);
                }

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

            }
            catch (Exception ex)
            {

                log.Error(" Exception in  GetAreaMngAssignStore Method ", ex);
            }
            finally
            {
                db.CloseConnection(con);
            }

            return assigneStore;
        }

        // Get  Record sales ammount from local database
        public RecordSalesDTO GetRecordSaleByStoreID(int storeId, string weekOfDay)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            RecordSalesDTO dto = null;

            try
            {

                string query = " select SalesAmount,BusinessDate from dbo.RecordSales where StoreId = " + SQLUtility.getInteger(storeId) + " and DayOfWeek = " + SQLUtility.getString(weekOfDay) + " ";
                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    dto = new RecordSalesDTO();
                    dto.SalesAmount = ValidationUtility.ToDouble(reader["SalesAmount"].ToString());
                    dto.BusinessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString());
                }

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

            }
            catch (Exception ex)
            {

                log.Error("Exception in GetRecordSaleByStoreID method ", ex);
            }
            finally
            {
                db.CloseConnection(con);
            }

            return dto;
        }

        // Method is use for Get perday total sales amount day
        public bool IsCurrentDateSaleIsExsist(int storeId, DateTime currentDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            bool isCurrentDateSale = false;

            try
            {

                string query = "  select * from dbo.PerdaySales where BusinessDate = '" + currentDate.ToString("yyyy/MM/dd") + "' and StoreId = " + SQLUtility.getInteger(storeId) + " ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    isCurrentDateSale = true;

                }

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

            }
            catch (Exception ex)
            {
                log.Error("Exception in IsCurrentDateSaleIsExsist method  ", ex);
            }
            finally
            {
                db.CloseConnection(con);
            }

            return isCurrentDateSale;
        }

//.........这里部分代码省略.........

            DateTime weekStartDate = GetActualWeekStartDate(businessDate);
            DateTime secondDate = weekStartDate.AddDays(1);
            DateTime thirdDate = weekStartDate.AddDays(2);
            DateTime fourDate = weekStartDate.AddDays(3);
            DateTime fiveDate = weekStartDate.AddDays(4);
            DateTime sixDate = weekStartDate.AddDays(5);
            DateTime sevenDate = weekStartDate.AddDays(6);

            ArrayList DateList = new ArrayList();
            DateList.Add(weekStartDate);
            DateList.Add(secondDate);
            DateList.Add(thirdDate);
            DateList.Add(fourDate);
            DateList.Add(fiveDate);
            DateList.Add(sixDate);
            DateList.Add(sevenDate);

            try
            {
                string query = null;

                con = db.OpenConnection();

                foreach (DateTime date in DateList)
                {
                    query = " select * from dbo.PerdaySales where StoreId  in (select StoreId  from dbo.StoreUser where UserId =" + SQLUtility.getInteger(userId) + ") and BusinessDate='" + date.ToString("yyyy/MM/dd") + "'";

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

                    SqlDataReader reader = comm.ExecuteReader();

                    if (reader.Read())
                    {
                        int id = ValidationUtility.ToInteger(reader[0].ToString());
                        int sid = ValidationUtility.ToInteger(reader[1].ToString());
                        int openingInformationId = ValidationUtility.ToInteger(reader[2].ToString());
                        double salesAmount = ValidationUtility.ToDouble(reader[4].ToString());
                        string day = reader[5].ToString();

                        if (openingInformationId == 0 && !ValidationUtility.IsEqual(day, DateTime.Now.DayOfWeek.ToString()))
                        {
                            PerdaySalesDTO dto = new PerdaySalesDTO { Id = id, StoreId = sid, OpeningInformationId = openingInformationId, BusinessDate = date, SalesAmountString = "N/A", WeekOfDay = day };

                            list.Add(dto);
                        }
                        else
                        {
                            PerdaySalesDTO dto = new PerdaySalesDTO { Id = id, StoreId = sid, OpeningInformationId = openingInformationId, BusinessDate = date, SalesAmountString = salesAmount.ToString(), WeekOfDay = day };

                            list.Add(dto);
                        }

                    }
                    else
                    {
                        reader.Close();
                        comm.Dispose();
                        PerdaySalesDTO dto = null;
                        if (date<=DateTime.Now)
                        {
                            int id = GetCurrentDateOpeningInfoId(date, connectionString);

                            if (id == 0)
                            {
                                dto = new PerdaySalesDTO { SalesAmountString = "N/A" };
                                list.Add(dto);
                            }
                            else
                            {
                                dto = new PerdaySalesDTO { SalesAmountString = "0" };
                                list.Add(dto);
                            }
                        }
                        else
                        {
                            dto = new PerdaySalesDTO { SalesAmountString = "0" };
                            list.Add(dto);
                        }

                    }

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

                }

            }
            catch (Exception ex)
            {
                log.Error("Exception in GetPerdaySales method  ", ex);

            }
            finally
            {
                db.CloseConnection(con);
            }

            return list;
        }

//.........这里部分代码省略.........
                             + " dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc   where  "
                             + " sm.MaintenanceCategoryId = mc.Id  order by sm.Id desc ";

                }
                else
                {
                    //query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
                    //         + "  where  sm.MaintenanceCategoryId = mc.Id and sm.StoreId in (select Id from dbo.StoreUser where UserId=" + SQLUtility.getInteger(userId) + " )  order by sm.Id desc ";

                    //query = "select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , s.Id,s.StoreName "
                    //        + "   from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc ,dbo.StoreUser su, dbo.Store s "
                    //        + " where  sm.MaintenanceCategoryId = mc.Id and su.Id = sm.StoreId and s.Id = su.StoreId and "
                    //        + "  sm.StoreId in (select su.Id from dbo.StoreUser where su.UserId = " + SQLUtility.getInteger(userId) + ") order by sm.Id desc    ";

                    query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , sm.StoreId "
                             + "  from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
                             + " where  sm.MaintenanceCategoryId = mc.Id and  "
                             + " sm.StoreId in (select su.StoreId from dbo.StoreUser su where su.UserId = " + SQLUtility.getInteger(userId) + ") order by sm.Id desc  ";

                }
                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    DateTime resolvDate = new DateTime();

                    int id = ValidationUtility.ToInteger(reader[0].ToString());
                    string des = reader[1].ToString();
                    string status = reader[2].ToString();
                    DateTime creatDate = ValidationUtility.ToDate(reader[3].ToString());

                    int cId = ValidationUtility.ToInteger(reader[5].ToString());
                    string item = reader[6].ToString();
                    int rank = ValidationUtility.ToInteger(reader[7].ToString());

                    int storeId = ValidationUtility.ToInteger(reader[8].ToString());

                    //string storeName = reader[9].ToString();

                    MaintenanceDTO dto = null;
                    if (ValidationUtility.IsEqual("resolved", status))
                    {
                        resolvDate = ValidationUtility.ToDate(reader[4].ToString());

                        dto = new MaintenanceDTO
                        {
                            Id = id,
                            Description = des,
                            Status = status,
                            CreateDateTimeInString = creatDate.ToString("MMM/dd/yyyy"),
                            ResolveDateTimeInString = resolvDate.ToString("MMM/dd/yyyy"),
                            CategoryId = cId,
                            ItemName = item,
                            Rank = rank,
                            StoreId = storeId,
                            //StoreName = storeName
                        };
                    }

                    else
                    {
                        dto = new MaintenanceDTO
                        {
                            Id = id,
                            Description = des,
                            Status = status,
                            CreateDateTimeInString = creatDate.ToString("MMM/dd/yyyy"),
                            ResolveDateTimeInString = "",
                            CategoryId = cId,
                            ItemName = item,
                            Rank = rank,
                            StoreId = storeId,
                            //StoreName = storeName
                        };
                    }

                    list.Add(dto);

                }

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

            }
            catch (Exception ex)
            {
                log.Error(" Exception in  GetMaintenanceList Method ", ex);

            }
            finally
            {
                db.CloseConnection(con);
            }

            return list;
        }

        public int GetCategoryId(string itemName)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            int categoryId = 0;

            try
            {
                string query = "select Id from dbo.MaintenanceCategory where ItemName = " + SQLUtility.getString(itemName) + " ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    categoryId = ValidationUtility.ToInteger(reader[0].ToString());
                }

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return categoryId;
        }

        public double GetMonthlyEmpBonus(DateTime monthStartDate, DateTime monthEndDate)
        {
            double totalBonus = 0;

            DataBaseUtility db = new DataBaseUtility();
            SqlConnection conn = null;

            try
            {
                string query = "select SUM(BonusAmount) from dbo.EmployeeBonus where BusinessDate>='" + SQLUtility.FormateDateYYYYMMDD(monthStartDate) + "' and BusinessDate<='" + SQLUtility.FormateDateYYYYMMDD(monthEndDate) + "' ";

                conn = db.OpenConnection();

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

                while (reader.Read())
                {
                    totalBonus = ValidationUtility.ToDouble(reader[0].ToString());
                }

                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {

                log.Error("Exception in  GetManagerBonus", ex);
            }
            finally
            {
                db.CloseConnection(conn);
            }

            return totalBonus;
        }

        //For General Manager
        public ArrayList GetGeneralManagerAssignAreaManagerList()
        {
            ArrayList amId = new ArrayList();

            DataBaseUtility db = new DataBaseUtility();
            SqlConnection conn = null;

            try
            {
                string query = "select AreaManagerId from dbo.AssignAreaManagerInfo";

                conn = db.OpenConnection();

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

                while (reader.Read())
                {
                    amId.Add(ValidationUtility.ToInteger(reader[0].ToString()));

                }

                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {

                log.Error("Exception in  GetManagerBonus", ex);
            }
            finally
            {
                db.CloseConnection(conn);
            }

            return amId;
        }

        // This method is use for check employee is exsist in  our local db or it new employee
        public EmployeeInfoDTO GetEmployeeInfo(int storeId, int empId)
        {
            EmployeeInfoDTO dto = null;

            SqlConnection con = null;

            DataBaseUtility db = new DataBaseUtility();

            try
            {
                string query = "select * from dbo.EmployeeInfo where EmpId = " + SQLUtility.getInteger(empId) + "  and  storeId = " + SQLUtility.getInteger(storeId) + " ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    dto = new EmployeeInfoDTO();

                    dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString());

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

            }
            catch (Exception ex)
            {
                log.Error("Exception in  IsEmployeExsist Method ", ex);
            }

            finally
            {
                db.CloseConnection(con);
            }

            return dto;
        }

        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;
        }

        //Check data exit or not into the local database
        public bool IsWeeklyPaperExistIntoLocalDb(int storeId, DateTime weekStartDay)
        {
            bool isExist = false;
            DataBaseUtility db = new DataBaseUtility();
            SqlConnection conn = null;
            try
            {
                conn = db.OpenConnection();
                string query = "select * from dbo.WeeklyPaperWork where StoreId=" + SQLUtility.getInteger(storeId) + " and WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
                SqlCommand comm = db.getSQLCommand(query, conn);
                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    isExist = true;
                }
                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {

                log.Error("Exception into IsWeeklyPaperExistIntoLocalDb method", ex);
            }
            finally
            {
                db.CloseConnection(conn);
            }

            return isExist;
        }

        public bool IsAllWeeklyPaperYearWeekRecordExsist(ArrayList storeList, DateTime date)
        {
            bool isAllWeekExsist = false;

            StoreDTO dto = (StoreDTO)storeList[0];

            date = date.AddYears(-1);

            int totalWeekOfYear = ValidationUtility.YearWeekCount(date);

            DateTime yearStartDate = ValidationUtility.YearWeekStartDate(date);
            DateTime yearEndDate = ValidationUtility.GetActualWeekStartDate(new DateTime(date.Year, 12, 31));

            DataBaseUtility db = new DataBaseUtility();
            SqlConnection conn = null;
            try
            {

                //string query = "  select COUNT(*) from dbo.GoalsYTD gy where gy.Year=" + SQLUtility.getInteger(yearStartDate.Year) + " "
                //                 + " and gy.WeekStartDate>='" + SQLUtility.FormateDateYYYYMMDD(yearStartDate) + "' and WeekEndDate<='" + SQLUtility.FormateDateYYYYMMDD(yearEndDate.AddDays(6)) + "' "
                //                   + " and StoreId=" + SQLUtility.getInteger(dto.Id) + "";

                string query = "select count(*) from dbo.WeeklyPaperWork "
                                 + " where WeekStartDate>='" + SQLUtility.FormateDateYYYYMMDD(yearStartDate) + "' and WeekStartDate<='" + SQLUtility.FormateDateYYYYMMDD(yearEndDate) + "' "
                                     + " and StoreId = " + SQLUtility.getInteger(dto.Id) + "";

                conn = db.OpenConnection();
                SqlCommand comm = db.getSQLCommand(query, conn);
                SqlDataReader reader = comm.ExecuteReader();

                int weekCount = 0;

                if (reader.Read())
                {
                    weekCount = ValidationUtility.ToInteger(reader[0].ToString());

                    if (totalWeekOfYear == weekCount)
                    {
                        isAllWeekExsist = true;
                    }

                }

                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {

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

            finally
            {
                db.CloseConnection(conn);
            }

            return isAllWeekExsist;
        }

        /*
         *Get WeeklyPaper Work Info Id based
         */
        public WeeklyPaperworkDTO GetWeeklyPaperWorkOfWeek(int id)
        {
            WeeklyPaperworkDTO dto = null;
            DataBaseUtility db = new DataBaseUtility();
            SqlConnection conn = null;
            try
            {

                conn = db.OpenConnection();
                //string query = " select SUM(CostPercent) as CostPercent ,SUM(LaborCostPercent) as LaborCostPercent  from dbo.WeeklyPaperWork "
                //                + " where WeekStartDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDay.AddDays(6)) + "'";

                String query = " select StoreId,NetSales,StoreTransfer,PFG1,PFG2,Coke,LaborCost,WeekStartDate from dbo.WeeklyPaperWork  where Id=" + SQLUtility.getInteger(id) + " ";

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

                if (reader.Read())
                {
                    dto = new WeeklyPaperworkDTO();
                    dto.StoreId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
                    double netSales = ValidationUtility.ToDouble(reader["NetSales"].ToString());
                    double storeTransfer = ValidationUtility.ToDouble(reader["StoreTransfer"].ToString());
                    double pfg1 = ValidationUtility.ToDouble(reader["PFG1"].ToString());
                    double pfg2 = ValidationUtility.ToDouble(reader["PFG2"].ToString());
                    double coke = ValidationUtility.ToDouble(reader["Coke"].ToString());
                    double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
                    dto.WeekStartDate = ValidationUtility.ToDate(reader["WeekStartDate"].ToString());

                    double totalPFG = pfg1 + pfg2;

                    double totalCost = storeTransfer + totalPFG + coke;

                    double costPercent = ValidationUtility.IsNan(totalCost / netSales);

                    double laborCostPercent = ValidationUtility.IsNan(laborCost / netSales);

                    dto.CostPercent = costPercent;
                    dto.LaborCostPercent = laborCostPercent;

                }
                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {

                log.Error("Exception into IsWeeklyPaperExistIntoLocalDb method", ex);
            }
            finally
            {
                db.CloseConnection(conn);
            }

            return dto;
        }

//.........这里部分代码省略.........

                        double storeTransfer = ValidationUtility.ToDouble(reader["StoreTransfer"].ToString());
                        double pfg1 = ValidationUtility.ToDouble(reader["PFG1"].ToString());
                        double pfg2 = ValidationUtility.ToDouble(reader["PFG2"].ToString());
                        // double totalPFG = ValidationUtility.ToDouble(reader["TotalPFG"].ToString());

                        double totalPFG = pfg1 + pfg2;
                        double coke = ValidationUtility.ToDouble(reader["Coke"].ToString());
                        //    double totalCost = ValidationUtility.ToDouble(reader["TotalCost"].ToString());

                        double totalCost = storeTransfer + totalPFG + coke;
                        // double costPercent = ValidationUtility.ToDouble(reader["CostPercent"].ToString());

                        double costPercent = totalCost / netSales;

                        costPercent = ValidationUtility.IsNan(costPercent);

                        double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
                        // double laborCostPercent = ValidationUtility.ToDouble(reader["LaborCostPercent"].ToString());
                        double laborCostPercent = laborCost / netSales;
                        laborCostPercent = ValidationUtility.IsNan(laborCostPercent);

                        // double royalty = ValidationUtility.ToDouble(reader["Royalty"].ToString());

                        double royalty = netSales * 0.08;
                        //   double fAF = ValidationUtility.ToDouble(reader["FAF"].ToString());
                        double fAF = netSales * 0.045;

                        //  double costAndLaborCostPercent = ValidationUtility.ToDouble(reader["CostAndLaborCostPercent"].ToString());
                        double costAndLaborCostPercent = laborCostPercent + costPercent;

                        double adjTax = ValidationUtility.ToDouble(reader["AdjTax"].ToString());
                        double gcRedeem = ValidationUtility.ToDouble(reader["GCRedeem"].ToString());

                        //  double gcDifference = ValidationUtility.ToDouble(reader["GCDifference"].ToString());

                        double gcDifference = gcRedeem - giftCardSales;

                        double taxPercent = ValidationUtility.ToDouble(reader["TaxPercent"].ToString());
                        //  double actualSalesTaxper = ValidationUtility.ToDouble(reader["ActualSalesTaxper"].ToString());

                        double actualSalesTaxper = netSales * taxPercent;
                        // double differenceOfSalesTax = ValidationUtility.ToDouble(reader["DifferenceOfSalesTax"].ToString());
                        double differenceOfSalesTax = actualSalesTaxper - adjTax;

                        double nonTaxableSale = ValidationUtility.ToDouble(reader["NonTaxableSale"].ToString());
                        double weekStartDate = ValidationUtility.ToDouble(reader["WeekStartDate"].ToString());

                        WeeklyPaperworkDTO weeklyPaperworkDTO = new WeeklyPaperworkDTO
                        {
                            StoreNumber = storeNumber,
                            Id = id,
                            StoreId = sId,
                            NetSales = netSales,
                            GiftCardSales = giftCardSales,
                            Ar = ar,
                            PaidOuts = paidOuts,
                            PettyExpense = pettyExpense,
                            Total = total,
                            StoreTransfer = storeTransfer,
                            Pfg1 = pfg1,
                            Pfg2 = pfg2,
                            TotalPFG = totalPFG,
                            Coke = coke,
                            TotalCost = totalCost,
                            CostPercent = costPercent,
                            LaborCost = laborCost,
                            LaborCostPercent = laborCostPercent,
                            Royalty = royalty,
                            Faf = fAF,
                            CostAndLaborCostPercent = costAndLaborCostPercent,
                            AdjTax = adjTax,
                            GcRedeem = gcRedeem,
                            GcDifference = gcDifference,
                            WeekStartDate = weekStartDay,
                            TaxPercent = taxPercent,
                            ActualSalesTaxper = actualSalesTaxper,
                            DifferenceOfSalesTax = differenceOfSalesTax,
                            NonTaxableSale = nonTaxableSale
                        };
                        list.Add(weeklyPaperworkDTO);

                    }

                    reader.Close();
                    comm.Dispose();
                }
                catch (Exception ex)
                {
                    log.Error(" Exception in GetWeeklyPaperListFromLocalDB Method  ", ex);
                }
                finally
                {
                    db.CloseConnection(conn);
                }

            }

            return list;
        }

        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;
        }