C# DataBaseUtility.getSQLCommand方法代码示例

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

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

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

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

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

        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
                        };
//.........这里部分代码省略.........

        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());
//.........这里部分代码省略.........