Esempio n. 1
0
        public ArrayList GetFoodItemList()
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            try
            {
                string query = "select Id,FoodName from dbo.FoodItem order by Id desc";

                //string query = "select Id,StoreId,BusinessDate,Item,ItemQuantity,LeftQuantity,OverQuantity from FoodOrder  WHERE BusinessDate BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "'";
                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {

                    int id = ValidationUtility.ToInteger(reader[0].ToString());
                    string foodName = reader[1].ToString();

                    FoodItemDTO dto = new FoodItemDTO { Id = id, FoodName = foodName };

                    list.Add(dto);

                }

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

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return list;
        }
Esempio n. 2
0
        //Code for Area manager
        // 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;
        }
        public static int GetActiveStoredId(int userId)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            int storId = 0;

            try
            {
                string query = " select Id from dbo.Store where Id in(select StoreId from StoreUser where UserId = " + SQLUtility.getInteger(userId) + ")  and IsStoreActive='true'";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

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

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return storId;
        }
Esempio n. 4
0
        // 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;
        }
Esempio n. 5
0
        public ArrayList GetPerdaySales(int userId, string connectionString, DateTime businessDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            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;
        }
        public ArrayList GetMaintenanceList(int userId)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            try
            {
                string query = "";

                if (userId == 0)
                {
                    //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 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 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  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;
        }
        //Get weekly paper list from local database list
        public ArrayList GetWeeklyPaperListFromLocalDB(ArrayList storeList, DateTime weekStartDay)
        {
            ArrayList list = new ArrayList();
            DataBaseUtility db = new DataBaseUtility();
            SqlConnection conn = null;

            foreach (StoreDTO storeDTO in storeList)
            {
                if (!IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, weekStartDay))
                {
                    ArrayList oneStoreList = new ArrayList();
                    oneStoreList.Add(storeDTO);
                    AddWeeklyPaperWork(weekStartDay, oneStoreList, true);

                }

                DateTime currentWeekStartDate = ValidationUtility.GetActualWeekStartDate(DateTime.Now);
                DateTime lastWeekStartDate = ValidationUtility.GetActualWeekStartDate(currentWeekStartDate.AddDays(-1));

                if (weekStartDay.Date >= lastWeekStartDate.Date)
                {
                    ArrayList oneStoreList = new ArrayList();
                    oneStoreList.Add(storeDTO);
                    AddWeeklyPaperWork(lastWeekStartDate, oneStoreList, false);

                }
                if (weekStartDay.Date.Equals(currentWeekStartDate.Date))
                {
                    ArrayList oneStoreList = new ArrayList();
                    oneStoreList.Add(storeDTO);
                    AddWeeklyPaperWork(currentWeekStartDate, oneStoreList, false);
                }

                try
                {

                    // string query = " select * from dbo.WeeklyPaperWork where StoreId=" + SQLUtility.getInteger(storeDTO.Id) + " and WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";

                    string query = "select s.StoreNumber, wpw.* from dbo.WeeklyPaperWork wpw,dbo.Store s where  wpw.StoreId=s.Id and  wpw.StoreId=" + SQLUtility.getInteger(storeDTO.Id) + " and wpw.WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
                    conn = db.OpenConnection();
                    SqlCommand comm = db.getSQLCommand(query, conn);
                    SqlDataReader reader = comm.ExecuteReader();

                    while (reader.Read())
                    {
                        int storeNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
                        int id = ValidationUtility.ToInteger(reader["Id"].ToString());
                        int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
                        double netSales = ValidationUtility.ToDouble(reader["NetSales"].ToString());
                        double giftCardSales = ValidationUtility.ToDouble(reader["GiftCardSales"].ToString());
                        double ar = ValidationUtility.ToDouble(reader["AR"].ToString());
                        double paidOuts = ValidationUtility.ToDouble(reader["PaidOuts"].ToString());
                        double pettyExpense = ValidationUtility.ToDouble(reader["PettyExpense"].ToString());
                        //    double total = ValidationUtility.ToDouble(reader["Total"].ToString());

                        double total = ar + paidOuts + pettyExpense;

                        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;
        }
        // Methos is use for check record is exsist for this week
        public bool IsWeekEmployeeTrakingExsist(int empId, int storeId, DateTime weekStartDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            bool isRecordExsist = false;

            SqlConnection con = null;

            try
            {

                string query = "select * from dbo.EmployeeTracker et where   EmployeeInfoId = (select Id from dbo.EmployeeInfo where EmpId=" + SQLUtility.getInteger(empId) + " and StoreId=" + SQLUtility.getInteger(storeId) + ") "
                                + " and BusinessDate between  '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "'  and  '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "'";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    isRecordExsist = true;
                }

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

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

            finally
            {
                db.CloseConnection(con);
            }

            return isRecordExsist;
        }
        public int CustomerComplaintCount(int storeId, DateTime startDate, DateTime endDate)
        {
            int count = 0;

            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            try
            {
                string query = " select COUNT(StoreId) from dbo.Complaints where StoreId=" + SQLUtility.getInteger(storeId) + " "
                             + " and ComplaintDate between '" + SQLUtility.FormateDateYYYYMMDD(startDate) + "'   and '" + SQLUtility.FormateDateYYYYMMDD(endDate) + "'";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

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

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return count;
        }
        // This method is use for get employee list from local database
        public ArrayList GetEmployeeList(int storeId)
        {
            ArrayList list = new ArrayList();

            SqlConnection con = null;

            DataBaseUtility db = new DataBaseUtility();

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

                string query = "select ei.*,s.StoreNumber from dbo.EmployeeInfo ei,dbo.Store s where  ei.StoreId=s.Id and    ei.StoreId =" + SQLUtility.getInteger(storeId) + "  ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {

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

                    int empId = ValidationUtility.ToInteger(reader["EmpId"].ToString());

                    string firstName = reader["EmpFirstName"].ToString();

                    int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());

                    int storeNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());

                    EmployeeInfoDTO dto = new EmployeeInfoDTO { Id = id, StoreId = sId, StoreNumber=storeNumber, EmpId = empId, EmpFirstName = firstName };

                    list.Add(dto);

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

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

            finally
            {
                db.CloseConnection(con);
            }

            return list;
        }
        //Manager
        public ArrayList GetManagerAssignStoreList()
        {
            ArrayList storeIdList = new ArrayList();

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

            try
            {
                string query = "select su.StoreId from dbo.StoreUser su,dbo.Users u  where  su.UserId=u.Id and u.RoleId=2";

                conn = db.OpenConnection();

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

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

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

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

            return storeIdList;
        }
        // This method is use for get employee id from EmployeeTracker table
        public EmployeeTrackerDTO GetEmployeeTracking(int empId, int storeId, DateTime businessDate)
        {
            EmployeeTrackerDTO dto = null;

            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            try
            {
                string query = " select * from dbo.EmployeeTracker et where EmployeeInfoId = (select Id from dbo.EmployeeInfo where EmpId=" + SQLUtility.getInteger(empId) + " and StoreId=" + SQLUtility.getInteger(storeId) + " )  "
                                + " and BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'  ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    dto = new EmployeeTrackerDTO();
                    dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString());

                }

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

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

            finally
            {
                db.CloseConnection(con);
            }

            return dto;
        }
Esempio n. 21
0
        public ManagerBonusDTO GetActualBounus(int storeId, DateTime startDate, DateTime endDate)
        {
            int totalWeekCount = ValidationUtility.GetNumOfWeekInMonth(startDate);

            if (totalWeekCount == 0)
            {
                totalWeekCount = 1;
            }

            ManagerBonusDTO managerBonusDTO = null;

            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            try
            {
                string query = "select * from dbo.ManagerBonus where StoreId = " + SQLUtility.getInteger(storeId) + "  and IsZeroBasis=" + SQLUtility.getString(false.ToString()) + "  "
                             + " and FirstDateOfMonth='" + SQLUtility.FormateDateYYYYMMDD(startDate) + "' and  LastDateOfMonth = '" + SQLUtility.FormateDateYYYYMMDD(endDate) + "' ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    int id = ValidationUtility.ToInteger(reader["Id"].ToString());
                    int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
                    double foodCost = ValidationUtility.ToDouble(reader["FoodCost"].ToString());
                    double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
                    double foodLaborCost = ValidationUtility.ToDouble(reader["FoodLaborCost"].ToString());
                    double salesIncrease = ValidationUtility.ToDouble(reader["SalesIncrease"].ToString());
                    double customerIndex = ValidationUtility.ToDouble(reader["CustomerIndex"].ToString());
                    double customerComplaint = ValidationUtility.ToDouble(reader["CustomerComplaint"].ToString());
                    double catering = ValidationUtility.ToDouble(reader["Catering"].ToString());
                    double subInspection = ValidationUtility.ToDouble(reader["SubInspection"].ToString());

                    managerBonusDTO = new ManagerBonusDTO
                    {
                        Id = id,
                        StoreId = sId,
                        FoodCost = foodCost / totalWeekCount,
                        LaborCost = laborCost / totalWeekCount,
                        FoodLaborCost = foodLaborCost / totalWeekCount,
                        SalesIncrease = salesIncrease / totalWeekCount,
                        CustomerIndex = customerIndex / totalWeekCount,
                        CustomerComplaint = customerComplaint,
                        Catering = catering,
                        SubInspection = subInspection
                    };

                }

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return managerBonusDTO;
        }
        //Get Employee Tracker id
        public int GetEmpTrackerId(int empInfoId, DateTime businessDate)
        {
            DataBaseUtility db = new DataBaseUtility();
            int id = 0;
            SqlConnection conn = null;
            try
            {
                conn = db.OpenConnection();

                string query = "select id from dbo.EmployeeTracker where BusinessDate='" + SQLUtility.FormateDateYYYYMMDDWtithTime(businessDate) + "' and EmployeeInfoId=" + SQLUtility.getInteger(empInfoId) + "";
                SqlCommand comm = db.getSQLCommand(query, conn);
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    id = ValidationUtility.ToInteger(reader[0].ToString());
                }
                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                log.Error("Exception in GetEmpTrackerId Method  ", ex);

            }
            finally
            {
                db.CloseConnection(conn);
            }

            return id;
        }
Esempio n. 23
0
        public ArrayList GetList()
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            try
            {

                string query = " select Id , ItemName,Rank from dbo.MaintenanceCategory ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    int id = ValidationUtility.ToInteger(reader[0].ToString());
                    string name = reader[1].ToString();
                    int rank = ValidationUtility.ToInteger(reader[2].ToString());

                    MaintenanceDTO dto = new MaintenanceDTO { Id = id, ItemName = name, Rank = rank };

                    list.Add(dto);

                }

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

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return list;
        }
        // Get All StoreList from local database
        public ArrayList GetStoreList()
        {
            ArrayList collection = new ArrayList();

            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;
            try
            {

                string query = "select Id,StoreNumber,StoreName,ConnectionString from dbo.Store ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    int id = ValidationUtility.ToInteger(reader["Id"].ToString());
                    int sNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
                    string strorName = reader["StoreName"].ToString();
                    string connectionString = reader["ConnectionString"].ToString();
                    StoreDTO dto = new StoreDTO { Id = id, StoreNumber = sNumber, StoreName = strorName, ConnectionString = connectionString };

                    collection.Add(dto);

                }

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

            finally
            {
                db.CloseConnection(con);

            }

            return collection;
        }
Esempio n. 25
0
        // Get Id from StoreUser table
        public int GetStoreId(int userId)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            int id = 0;

            try
            {
                string query = "select top 1 * from dbo.StoreUser where UserId = " + SQLUtility.getInteger(userId) + " order by Id desc ";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

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

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return id;
        }
        //Get TotalField Value
        public List<EmployeeTotalWorkDTO> GetTotalList(int empInfoId, DateTime weekStartDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection conn = null;

            List<EmployeeTotalWorkDTO> empTrakList = new List<EmployeeTotalWorkDTO>();

            try
            {
                string query = " select et.Id,et.ScheduleIn,et.ScheduleOut, et.BusinessDate,et.TotalTimeWorked from dbo.EmployeeTracker et where "
                                 + " et.BusinessDate between  '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + " ";

                conn = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                TimeSpan totalSheduleTimeDifference = new TimeSpan(0, 0, 0);
                TimeSpan totalClockingTime = new TimeSpan(0, 0, 0);
                TimeSpan totalDifferenceTime = new TimeSpan(0, 0, 0);

                while (reader.Read())
                {

                    int id = ValidationUtility.ToInteger(reader["Id"].ToString());
                    DateTime scheduleIn = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
                    DateTime scheduleOut = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));
                    TimeSpan totalTime = reader.GetTimeSpan(4);
                    totalClockingTime = totalClockingTime + totalTime;
                    //   var v = (scheduleOut - scheduleOut).Subtract;

                    TimeSpan sheduleTimeDifference = scheduleOut.Subtract(scheduleIn);

                    totalSheduleTimeDifference = totalSheduleTimeDifference + sheduleTimeDifference;

                    TimeSpan difference = sheduleTimeDifference - totalTime;

                    totalDifferenceTime = totalDifferenceTime + difference;

                }

                EmployeeTotalWorkDTO trakerDTO = new EmployeeTotalWorkDTO { TotalScheduleTime = totalSheduleTimeDifference.ToString(), TotalClockingTime = totalClockingTime.ToString(), TotalWorkTime = totalClockingTime.ToString(), TotalDifference = totalDifferenceTime.ToString() };

                empTrakList.Add(trakerDTO);
                reader.Close();
                comm.Dispose();

            }
            catch (Exception ex)
            {

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

            finally
            {
                db.CloseConnection(conn);
            }

            return empTrakList;
        }
Esempio n. 27
0
        public ArrayList GetRecodSaleList(int userId, DateTime selectedDate)
        {
            DateTime dt = Convert.ToDateTime(selectedDate);

               // DateTime weekStartDate =  GetActualWeekStartDate(DateTime.Now);

            DateTime weekStartDate = GetActualWeekStartDate(dt);

            weekStartDate = weekStartDate.AddDays(-1);

            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            try
            {

                string query = " select * from dbo.RecordSales where StoreId  in (select StoreId  from dbo.StoreUser where UserId = " + SQLUtility.getInteger(userId) + ")";

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    weekStartDate = weekStartDate.AddDays(1);
                    string weekDate = weekStartDate.ToString("MM/dd/yyyy");
                    RecordSalesDTO dto = null;
                    int id = ValidationUtility.ToInteger(reader[0].ToString());
                    int sid = ValidationUtility.ToInteger(reader[1].ToString());
                    string day = reader[2].ToString();
                    DateTime businessDate = ValidationUtility.ToDate(reader[3].ToString());
                    double salesAmount = ValidationUtility.ToDouble(reader[4].ToString());

                    //if (!ValidationUtility.IsEqual("01-01-0001 00:00:00", BusinessDate.ToString()) || !ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", BusinessDate.ToString()))
                    if (ValidationUtility.FormateDateYYYYMMDD(ValidationUtility.GetDefaultDate()).Equals(ValidationUtility.FormateDateYYYYMMDD(businessDate)))
                    {
                        dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" };
                        //dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = BusinessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() };
                    }
                    //else if (ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", BusinessDate.ToString()))
                    //{
                    //    dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" };
                    //   // dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = BusinessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() };
                    //}
                    else
                    {
                        dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = businessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() };
                       // dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" };
                    }

                    list.Add(dto);

                }

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

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

            return list;
        }
        //// Get Store List
        public List<EmployeeTrackerDTO> GetTrackingList(int empInfoId, DateTime weekStartDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection conn = null;

            List<EmployeeTrackerDTO> empTrakList = new List<EmployeeTrackerDTO>();

            try
            {
                string query = " select et.Id,et.ScheduleIn,et.ScheduleOut, et.BusinessDate,et.TotalTimeWorked from dbo.EmployeeTracker et where "
                                 + " et.BusinessDate between  '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + " ";

                conn = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    int isSchedulDay = 0;

                    int id = ValidationUtility.ToInteger(reader["Id"].ToString());
                    DateTime scheduleIn = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
                    DateTime scheduleOut = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));
                    DateTime businessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString());
                    TimeSpan totalTime = reader.GetTimeSpan(4);

                    //   var v = (scheduleOut - scheduleOut).Subtract;

                    TimeSpan sheduleTimeDifference = scheduleOut.Subtract(scheduleIn);

                    TimeSpan difference = sheduleTimeDifference - totalTime;

                    if (scheduleIn.Date.Equals(ValidationUtility.GetDefaultDate().Date))
                    {

                        isSchedulDay = 1;
                    }

                    if (scheduleIn.Date.Equals(ValidationUtility.GetDefaultDate().Date) && businessDate.Date < DateTime.Now.Date)
                    {
                        isSchedulDay = 2;
                    }

                    EmployeeTrackerDTO trakerDTO = new EmployeeTrackerDTO { Id = id, ScheduleInToString = scheduleIn.ToString("hh:mm tt"), ScheduleOutToString = scheduleOut.ToString("hh:mm tt"), IsDayscheduled = isSchedulDay, BusinessDateToString = businessDate.ToString("MM/dd/yyyy"), TotalTimeWorked = totalTime.ToString(), WorkDifference = difference.ToString(), BusinessDate = businessDate, ScheduleTimeDifference = sheduleTimeDifference.ToString() };

                    empTrakList.Add(trakerDTO);

                }

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

            }
            catch (Exception ex)
            {

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

            finally
            {
                db.CloseConnection(conn);
            }

            return empTrakList;
        }
Esempio n. 29
0
        public ArrayList GetBonusEmpName(int userId, string connectionString, DateTime businessDate)
        {
            DataBaseUtility db = new DataBaseUtility();

            SqlConnection con = null;

            ArrayList list = new ArrayList();

            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 EmployeeId, FirstName,LastName,BusinessDate from dbo.EmployeeBonus  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();

                    while (reader.Read())
                    {
                        int emoId = ValidationUtility.ToInteger(reader[0].ToString());
                        string firstName = reader[1].ToString();
                        string lastName = reader[2].ToString();
                        EmpBonusDTO dto = new EmpBonusDTO { EmployeeId = emoId, FirstName = firstName, LastName = lastName, BusinessDateStringType = date.ToString("MM/dd/yyyy") };

                        list.Add(dto);

                    }

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

                }

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

            }
            finally
            {
                db.CloseConnection(con);
            }

            return list;
        }
        // This method is use for check employee is exsist in  our local db or it new employee
        public bool IsEmployeExsist(EmployeeInfoDTO employeeInfoDTO, int storeId)
        {
            bool isEmployeeExsist = false;

            SqlConnection con = null;

            DataBaseUtility db = new DataBaseUtility();

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

                con = db.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

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

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

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

            finally
            {
                db.CloseConnection(con);
            }

            return isEmployeeExsist;
        }