예제 #1
0
        //
        // GET: /Api/
        public ActionResult Index()
        {
            ArrayList al = new ArrayList();
            Dictionary<int, string> connectionString = (Dictionary<int, string>)Session["ConnectionString"];

            string storeNameConnectionString = "";

            if (connectionString.ContainsKey(1))
            {
                storeNameConnectionString = connectionString[1];
            }

            SqlConnection con = new SqlConnection(storeNameConnectionString);
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(storeNameConnectionString);

            try
            {

                string query = " SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN " + " sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc.name,ta.name ORDER BY sc.name,ta.name ";

                DataTable tableName = db.FetchData(query);

                //  String query = "select count(*) from ";

                foreach (DataRow rd in tableName.Rows)
                {
                    string tName = (string)rd[0];

                    int tCt = Convert.ToInt32(rd[1].ToString());

                    if (tCt > 0)
                    {
                        al.Add(tName);
                    }

                }

            }
            catch (Exception ex)
            {

            }

            finally
            {
               db.CloseConnection(con);
            }

            if (al != null && al.Count > 0)
            {
                return View(al);
            }
            else
            {
                ViewBag.Message = " Exception occured in fetching data ";
                return View();
            }
        }
예제 #2
0
        public ActionResult Data(string id)
        {
            string tName = id.Replace("-", ".");

            //String id = "dbo.OrderPayments";

               DataTable tableName=null;

            Dictionary<int, string> connectionString = (Dictionary<int, string>)Session["ConnectionString"];

            string storeNameConnectionString = "";

            if (connectionString.ContainsKey(1))
            {
                storeNameConnectionString = connectionString[1];
            }

            SqlConnection con = new SqlConnection(storeNameConnectionString);
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(storeNameConnectionString);

            try
            {

                 string query = "SELECT * from " + tName;

                 tableName = db.FetchData(query);

            }
            catch (Exception ex)
            {

            }
            finally
            {
                db.CloseConnection(con);
            }

              return View(tableName);
        }
        public DataTable GetSalesItemizationData(DateTime dateTime, string connetionStringName)
        {
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);

            // This procedure set 7 day data
            DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
            DateTime weekEndDate = weekStartDate.AddDays(6);

            SqlParameter[] sqlParams = new SqlParameter[] {
            new SqlParameter("@StartDate",weekStartDate ),
            new SqlParameter("@EndDate",weekEndDate ),};

            db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);

            // This procedure is use for get SalesItemization data
            SqlParameter[] salesItemizationSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable salesItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_SalesItemization_SelectStoredProcedure", salesItemizationSqlParams);

            return salesItemizationDataTable;

            // Trhow ;
        }
        // This method is use for get Employee Information from client store
        public ArrayList GetEmpInfo(string connectionString, DateTime bussinessDate)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
            SqlConnection con = null;

            ArrayList list = new ArrayList();

            try
            {

                //string query = "    select e.EmployeeId, er.EmployeeRoleId , e.FirstName,e.LastName from   dbo.Employees e ,dbo.EmployeeRoles er "
                //               + "  where er.EmployeeRoleId = e.EmployeeId  and  (er.EmployeeRoleId=5 or er.EmployeeRoleId=6)  and     e.EmployeeId "
                //            + " in (select EmployeeId from dbo.UserClocking where OpeningInformationId in(select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + bussinessDate.ToString("yyyy/MM/dd") + "'))";

                string query = "select e.EmployeeId,ere.EmployeeRole_Id,e.FirstName,e.LastName from   dbo.Employees e ,dbo.EmployeeRoleEmployees ere "
                                 + " where ere.Employee_Id = e.EmployeeId  and  ere.EmployeeRole_Id in (5,6)  and ere.Employee_Id  "
                                 + " in (select EmployeeId from dbo.UserClocking where OpeningInformationId in(select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + bussinessDate.ToString("yyyy/MM/dd") + "'))";

                con = remoteDataBase.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    EmpBonusDTO dto = new EmpBonusDTO();
                    dto.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
                    dto.EmployeeRoleId = ValidationUtility.ToInteger(reader[1].ToString());
                    dto.FirstName = reader[2].ToString();
                    dto.LastName = reader[3].ToString();
                    dto.BusinessDate = bussinessDate;

                    list.Add(dto);
                }

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

            }
            catch (Exception ex)
            {

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

            return list;
        }
예제 #5
0
        public double GetPerDaySalesAmount(string connectionString, DateTime bussinessDate)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
            SqlConnection con = null;

            double perDaySaleAmount = 0;

            try
            {

                string query = " select  SUM(PaymentAmount)   as amount  from dbo.OrderPayments where  "
                               + " OrderHeaderId  in ( select OrderHeaderId from dbo.OrderHeaders where OpeningInformationId in  "
                               + " (select OpeningInformationId from OpeningInformation where BusinessDate = '" + bussinessDate.ToString("yyyy/MM/dd") + "')) ";

                con = remoteDataBase.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    perDaySaleAmount = ValidationUtility.ToDouble(reader[0].ToString());

                }

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

            }
            catch (Exception ex)
            {

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

            return perDaySaleAmount;
        }
예제 #6
0
        public ArrayList GetData(string storeNameConnectionString)
        {
            ArrayList list = new ArrayList();

            StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);

            SqlConnection con = null;

            try
            {

                StringBuilder itemName = new StringBuilder();

                //string query = "select   op.PaymentAmount  as LastSaleAmount ,  oh.InsertedDate as  LastSaleTime ,oh.TaxAmount as Tax   from dbo.OrderHeaders oh  ,dbo.OrderPayments op "
                //                + " where op.OrderHeaderId = oh.OrderHeaderId and  oh.InsertedDate in (select MAX(InsertedDate) from  OrderHeaders) ";

                String query = " select mi.MenuItemName as ItemName,op.PaymentAmount as LastSaleAmount,oh.InsertedDate as  LastSaleTime,oh.TaxAmount as Tax  from  Staging.MenuItems mi,dbo.OrderPayments op ,dbo.OrderHeaders oh   where  MenuItemId  in ( select oip.MenuItemId from dbo.OrderHeaders oh,dbo.OrderItemsPaid oip where oh.OrderHeaderId =oip.OrderHeaderId and oh.InsertedDate in (select MAX(InsertedDate) from  OrderHeaders) ) "
                                + " and op.OrderHeaderId = oh.OrderHeaderId and  oh.InsertedDate in (select MAX(InsertedDate) from  OrderHeaders) ";

                con = srdbu.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();
                double LastSaleAmountWithTax = 0.0;
                double tax = 0.0;
                DateTime lastSaledate=new DateTime();

                while (reader.Read())
                {

                    itemName.Append(reader["ItemName"].ToString() +" "+"<br />"+" ");

                 //   itemName.Append(",");

                    //list.Add(reader["ItemName"].ToString());
                   // double withTax = ValidationUtility.ToDouble(reader["LastSaleAmount"]);
                     LastSaleAmountWithTax = ValidationUtility.ToDouble(reader["LastSaleAmount"].ToString());

                     tax = ValidationUtility.ToDouble(reader["Tax"].ToString());

                     lastSaledate = ValidationUtility.ToDate(reader["LastSaleTime"].ToString());

                }

                list.Add(itemName.ToString().Remove(itemName.ToString().Trim().Length-1));
                list.Add(LastSaleAmountWithTax);
                list.Add(tax);
                list.Add(lastSaledate);
                reader.Close();
                comm.Dispose();

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

            finally
            {
                srdbu.CloseConnection(con);
            }

            return list;
        }
예제 #7
0
        public ArrayList GetBreadControlData(DateTime dateTime, string connetionStringName)
        {
            ArrayList list = new ArrayList();
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);

            // This procedure set 7 day data
            DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
            DateTime weekEndDate = weekStartDate.AddDays(6);

            // This procedure is use for get BreadControl data
            SqlParameter[] breadControlSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable breadControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_BreadControl_SelectStoredProcedure", breadControlSqlParams);

            DataTable newBreadControlDataTable = new DataTable();
            newBreadControlDataTable = db.GenerateTransposedTable(breadControlDataTable);

            for (int i = 0; i < newBreadControlDataTable.Rows.Count; i++)
            {
                string businessDate = newBreadControlDataTable.Rows[i]["BusinessDate"].ToString();
                double day1 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(0).Date.ToString()].ToString());
                double day2 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(1).Date.ToString()].ToString());
                double day3 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(2).Date.ToString()].ToString());
                double day4 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(3).Date.ToString()].ToString());
                double day5 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(4).Date.ToString()].ToString());
                double day6 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(5).Date.ToString()].ToString());
                double day7 = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekStartDate.AddDays(6).Date.ToString()].ToString());
                double weekTotal = ValidationUtility.ToDouble(newBreadControlDataTable.Rows[i][weekEndDate.AddYears(1).Date.ToString()].ToString());

                SalesItemizationReportDTO salesItemizationReportDTO = new SalesItemizationReportDTO { ProductCategoryDescription = businessDate, Day1 = day1, Day2 = day2, Day3 = day3, Day4 = day4, Day5 = day5, Day6 = day6, Day7 = day7, WeekTotal = weekTotal };
                list.Add(salesItemizationReportDTO);

            }

            return list;
        }
        // This method is use for get Imployee info from server
        public ArrayList GetEmployeeInfo(string connectionString)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);

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

            try
            {
                //string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where  ere.Employee_Id = e.EmployeeId and  ere.EmployeeRole_Id in(5,6)";
                //string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where  ere.Employee_Id = e.EmployeeId and  ere.EmployeeRole_Id in(1,2)";

                string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where  ere.Employee_Id = e.EmployeeId and  ere.EmployeeRole_Id in(select EmployeeRoleId from dbo.EmployeeRoles)";
                conn = remoteDataBase.OpenConnection();
                SqlCommand comm = remoteDataBase.getSQLCommand(query, conn);
                SqlDataReader reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    EmployeeInfoDTO employeeInfoDTO = new EmployeeInfoDTO();
                    employeeInfoDTO.EmpId = ValidationUtility.ToInteger(reader[0].ToString());
                    employeeInfoDTO.EmpFirstName = reader[1].ToString();
                    employeeInfoDTO.EmpMiddleName = reader[2].ToString();
                    employeeInfoDTO.EmpLastName = reader[3].ToString();
                    list.Add(employeeInfoDTO);
                }
                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {

                log.Error("Exception in GetEmployeeInfo Method  ", ex);
            }
            finally
            {
                remoteDataBase.CloseConnection(conn);
            }
            return list;
        }
예제 #9
0
        public double GetSubWaySale(string query, string connetionStringName)
        {
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);

            SqlConnection con = null;

            double ammount = 0;

            try
            {
                con = db.OpenConnection();

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

                SqlDataReader reader = comd.ExecuteReader();

                if (reader.Read())
                {

                    if (!ValidationUtility.IsNull(reader["ammount"].ToString()))
                    {
                        ammount = Convert.ToDouble(reader["ammount"]);
                    }

                }
                reader.Close();

                comd.Dispose();
            }
            catch (Exception ex)
            {
                log.Error(" Exception in GetSubWaySale Method ", ex);
            }
            finally
            {
                db.CloseConnection(con);
            }

            return ammount;
        }
예제 #10
0
        public ArrayList GetUnitsItemizationData(DateTime dateTime, string connetionStringName)
        {
            ArrayList list = new ArrayList();
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);

            try
            {
                // This procedure set 7 day data
                DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
                DateTime weekEndDate = weekStartDate.AddDays(6);

                // This procedure is use for get UnitsItemization data
                SqlParameter[] unitsItemizationSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
                DataTable UnitsItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_UnitsItemization_SelectStoredProcedure", unitsItemizationSqlParams);

                for (int i = 0; i < UnitsItemizationDataTable.Rows.Count; i++)
                {
                    string productCategoryDescription = UnitsItemizationDataTable.Rows[i]["ProductCategoryDescription"].ToString();
                    double day1 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day1"].ToString());
                    double day2 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day2"].ToString());
                    double day3 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day3"].ToString());
                    double day4 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day4"].ToString());
                    double day5 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day5"].ToString());
                    double day6 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day6"].ToString());
                    double day7 = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["Day7"].ToString());
                    double weekTotal = ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[i]["WeekTotal"].ToString());

                    SalesItemizationReportDTO salesItemizationReportDTO = new SalesItemizationReportDTO { ProductCategoryDescription = productCategoryDescription, Day1 = day1, Day2 = day2, Day3 = day3, Day4 = day4, Day5 = day5, Day6 = day6, Day7 = day7, WeekTotal = weekTotal };
                    list.Add(salesItemizationReportDTO);

                }
            }
            catch (Exception ex)
            {

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

            return list;
        }
예제 #11
0
        //Get Total FooT Long For Goal tab
        public ArrayList GetSalesItemizationFoodLong(DateTime dateTime, string connetionStringName)
        {
            ArrayList list = new ArrayList();

            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);

            try
            {

                DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
                DateTime weekEndDate = weekStartDate.AddDays(6);

                SqlParameter[] sqlParams = new SqlParameter[] {
            new SqlParameter("@StartDate",weekStartDate ),
            new SqlParameter("@EndDate",weekEndDate ),};

                db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);

                // This procedure is use for get CashControl data
                SqlParameter[] salesItemizationSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
                DataTable salesItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_SalesItemization_SelectStoredProcedure", salesItemizationSqlParams);

                list.Add(ValidationUtility.ToDouble(salesItemizationDataTable.Rows[0]["WeekTotal"].ToString()));

                // This procedure is use for get UnitsItemization data
                SqlParameter[] unitsItemizationSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
                DataTable UnitsItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_UnitsItemization_SelectStoredProcedure", unitsItemizationSqlParams);

                list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[10]["WeekTotal"].ToString()));
                list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[13]["WeekTotal"].ToString()));
                list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[14]["WeekTotal"].ToString()));
                list.Add(ValidationUtility.ToDouble(UnitsItemizationDataTable.Rows[15]["WeekTotal"].ToString()));

                // This procedure is use for get Labor data
                SqlParameter[] laborSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
                DataTable laborDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_Labor_SelectStoredProcedure", laborSqlParams);

                list.Add(ValidationUtility.ToDouble(laborDataTable.Rows[7]["Productivity"].ToString()));
                list.Add(ValidationUtility.ToDouble(laborDataTable.Rows[7]["HoursWorked"].ToString()));

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

            return list;
        }
예제 #12
0
        public DataSet GetControlSheet(DateTime dateTime, string connetionStringName)
        {
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connetionStringName);

            // This procedure set 7 day data
            DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(dateTime);
            DateTime weekEndDate = weekStartDate.AddDays(6);
            SqlParameter[] sqlParams = new SqlParameter[] {
            new SqlParameter("@StartDate",weekStartDate ),
            new SqlParameter("@EndDate",weekEndDate ),};

            db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);

            // This procedure is use for get CashControl data
            SqlParameter[] controlSheetSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable cashControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_CashControl_SelectStoredProcedure", controlSheetSqlParams);

            // This procedure is use for get SalesItemization data
            SqlParameter[] salesItemizationSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable salesItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_SalesItemization_SelectStoredProcedure", salesItemizationSqlParams);

            // This procedure is use for get BreadControl data
            SqlParameter[] breadControlSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable breadControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_BreadControl_SelectStoredProcedure", breadControlSqlParams);

            // This procedure is use for get UnitsItemization data
            SqlParameter[] unitsItemizationSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable UnitsItemizationDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_UnitsItemization_SelectStoredProcedure", unitsItemizationSqlParams);

            // This procedure is use for get Labor data
            SqlParameter[] laborSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable laborDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_Labor_SelectStoredProcedure", laborSqlParams);

            ArrayList list = new ArrayList();
            for (int i = 0; i <= 6; i++)
            {
                DateTime getDateRecord = weekStartDate.AddDays(i);

                //string innerQuery = " select sum(AmtSubwaySales) as ammount  from Report.OrdersSummary os, dbo.OpeningInformation oi where os.OpeningInformationId = oi.OpeningInformationId and oi.BusinessDate =  '" + getDateRecord.ToString("yyyy/MM/dd") + "' ";
                string innerQuery = " select AmtTotalSales from Report.OpeningInformationsSummary where OpeningInformationId = (select OpeningInformationId from  dbo.OpeningInformation where BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(getDateRecord) + "' ) ";

                list.Add(db.GetSubWaySale(innerQuery));

            }

            ArrayList list1 = new ArrayList();
            for (int i = 0; i <= 6; i++)
            {
                DateTime getDateRecord = weekStartDate.AddDays(i);

                string innerQuery = " select sum(AmtAdjustedUnitSales) as ammount  from Report.OrdersSummary os, dbo.OpeningInformation oi where os.OpeningInformationId = oi.OpeningInformationId and oi.BusinessDate =  '" + getDateRecord.ToString("yyyy/MM/dd") + "' ";

                list1.Add(db.GetSubWaySale(innerQuery));

            }

            ArrayList list2 = new ArrayList();
            for (int i = 0; i <= 6; i++)
            {
                DateTime getDateRecord = weekStartDate.AddDays(i);

                string innerQuery = " select sum(AmtDrinkSales) as ammount  from Report.OrdersSummary os, dbo.OpeningInformation oi where os.OpeningInformationId = oi.OpeningInformationId and oi.BusinessDate =  '" + getDateRecord.ToString("yyyy/MM/dd") + "' ";

                list2.Add(db.GetSubWaySale(innerQuery));

            }

            DataTable totalSubSaleTable = ConvertListToDataTable(list);
            DataTable totalUnitSaleTable = ConvertListToUnitTable(list1);
            DataTable totalDrinkSaleTable = ConvertListToDrinkTable(list2);

            DataSet dataSet = new DataSet();

            dataSet.Tables.Add(cashControlDataTable);
            dataSet.Tables.Add(salesItemizationDataTable);
            dataSet.Tables.Add(breadControlDataTable);
            dataSet.Tables.Add(UnitsItemizationDataTable);
            dataSet.Tables.Add(laborDataTable);
            dataSet.Tables.Add(totalSubSaleTable);
            dataSet.Tables.Add(totalUnitSaleTable);
            dataSet.Tables.Add(totalDrinkSaleTable);
            dataSet.Tables.Add(HeaderInformation(dateTime));

            return dataSet;
        }
예제 #13
0
        public double GetYearSaleOfStore(int storeId, int year, string storeNameConnectionString)
        {
            DateTime startDateOfYear = new DateTime(year, 1, 1);
            DateTime endDateOfYear = new DateTime(year, 12, 31);

            double totalAmount = 0;

            StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);

            SqlConnection con = null;

            try
            {
                string query = "  select sum(ClosingGrandTotal-OpeningGrandTotal) as ammount  from dbo.OpeningInformation "
                                + " where BusinessDate >= '" + ValidationUtility.FormateDateYYYYMMDD(startDateOfYear) + "' and BusinessDate <= '" + ValidationUtility.FormateDateYYYYMMDD(endDateOfYear) + "' ";

                con = srdbu.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    totalAmount = ValidationUtility.ToDouble(reader["ammount"].ToString());
                }

                reader.Close();

                comm.Dispose();
            }
            catch (Exception ex)
            {
                log.Error(" Exception in GetYearSaleOfStore Method  ", ex);
            }
            finally
            {
                srdbu.CloseConnection(con);
            }

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

            DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);

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

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

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

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

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

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

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

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

                }
            }
            catch (Exception ex)
            {

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

            return list;
        }
예제 #15
0
        public ArrayList GetAllOpeningInformationId(string connectionString)
        {
            ArrayList idList = new ArrayList();
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
            SqlConnection con = null;

            try
            {
                string query = "select OpeningInformationId from dbo.OpeningInformation order by BusinessDate ";

                con = remoteDataBase.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

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

                    idList.Add(id);
                }

                reader.Close();

                comm.Dispose();
            }
            catch (Exception ex)
            {
                log.Error(" Exception  in GetAllOpeningInformationId method  ", ex);
            }

            finally
            {

                remoteDataBase.CloseConnection(con);
            }

            return idList;
        }
        // Employee  Clocking Information of each business day for every night
        public ArrayList GetEmployeClocking(int empId, DateTime businessDate, string connetionString)
        {
            ArrayList list = new ArrayList();

            StoreRemoteDataBaseUitility remoteDB = new StoreRemoteDataBaseUitility(connetionString);

            SqlConnection con = null;

            try
            {

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

                con = remoteDB.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                EmployeeClockingDTO employeeClockingDTO = null;

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

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

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

                }

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

            finally
            {
                remoteDB.CloseConnection(con);
            }

            return list;
        }
예제 #17
0
        // Get All Previous Sales of Day
        public DataTable GetPreviousSales(string connectionString, int opId)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);

            string query = " select (ClosingGrandTotal - OpeningGrandTotal) as PerDaySale , BusinessDate ,OpeningInformationId ,datename(dw,BusinessDate) as WeekOfDay  from OpeningInformation where OpeningInformationId = " + SQLUtility.getInteger(opId) + " ";

            DataTable dt = remoteDataBase.FetchData(query);

            return dt;
        }
예제 #18
0
        public int GetCurrentDateOpeningInfoId(DateTime businessDate, string connectionString)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);

            SqlConnection con = null;

            int opId = 0;

            try
            {

                string query = "select OpeningInformationId from OpeningInformation where BusinessDate = '" + businessDate.ToString("yyyy/MM/dd") + "' ";

                con = remoteDataBase.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

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

                }

                reader.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                log.Error("Exception in GetCurrentDateOpeningInfoId method  ", ex);
            }
            finally
            {
                remoteDataBase.CloseConnection(con);
            }

            return opId;
        }
예제 #19
0
        // Get Deposit Data from server
        public DataTable GetEachStoreDepositAmount(string connectionString, DateTime selectDate)
        {
            StoreRemoteDataBaseUitility db = new StoreRemoteDataBaseUitility(connectionString);

            // This procedure set 7 day data
            DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
            DateTime weekEndDate = weekStartDate.AddDays(6);
            SqlParameter[] sqlParams = new SqlParameter[] {
            new SqlParameter("@StartDate",weekStartDate ),
            new SqlParameter("@EndDate",weekEndDate ),};

            db.ExecuteSPDataTable("dbo.CalculateCashControlData", sqlParams);

            // This procedure is use for get CashControl data
            SqlParameter[] controlSheetSqlParams = new SqlParameter[] {
            new SqlParameter("@WeekEndingDate",weekEndDate ),};
            DataTable cashControlDataTable = db.ExecuteSPDataTable("Central.ControlSheetReport_CashControl_SelectStoredProcedure", controlSheetSqlParams);

            return cashControlDataTable;
        }
예제 #20
0
        // Get Record sales of year
        public DataTable GetWeekDayRecord(string connectionString, string weekDay, DateTime selectDate)
        {
            StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);

            string query = "select top 1 BusinessDate, datename(dw,BusinessDate) as WeekOfDay, (ClosingGrandTotal - OpeningGrandTotal) as PerDaySale "
                           + " from   dbo.OpeningInformation  where datename(dw,BusinessDate) = " + SQLUtility.getString(weekDay) + " "
                           + " and BusinessDate > DateADD(YEAR, -1, '" + selectDate.ToString("yyyy/MM/dd") + "')  and BusinessDate <= '" + selectDate.ToString("yyyy/MM/dd") + "' order by (ClosingGrandTotal - OpeningGrandTotal) desc";

            DataTable dt = remoteDataBase.FetchDataWithException(query);
            return dt;
        }
예제 #21
0
        //public double GetTotalAmountOfDayWithOutTax(string storeNameConnectionString)
        //{
        //    double amount = 0;
        //    StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);
        //    SqlConnection con = null;
        //    try
        //    {
        //        string query = " select sum(AmtTotalSales) as amount from Report.OrdersSummary where OpeningInformationId in (select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(DateTime.Now) + "') ";
        //        con = srdbu.OpenConnection();
        //        SqlCommand comm = srdbu.getSQLCommand(query, con);
        //        SqlDataReader reader = comm.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            amount = ValidationUtility.ToDouble(reader["amount"].ToString());
        //        }
        //        reader.Close();
        //        comm.Dispose();
        //    }
        //    catch (Exception ex)
        //    {
        //        log.Error(" Exception in  GetTotalAmountOfDayWithOutTax Method  ", ex);
        //    }
        //    finally
        //    {
        //        srdbu.CloseConnection(con);
        //    }
        //    return amount;
        //}
        public double GetTotalAmountWithTax(string storeNameConnectionString, DateTime businessDate )
        {
            double amount = 0;

            StoreRemoteDataBaseUitility srdbu = new StoreRemoteDataBaseUitility(storeNameConnectionString);

            SqlConnection con = null;

            try
            {
                //get adjusted proceeds
                //string query = " select  SUM(PaymentAmount)  as amount  from dbo.OrderPayments where "
                //                + " OrderHeaderId  in ( select OrderHeaderId from dbo.OrderHeaders where OpeningInformationId in  (select OpeningInformationId from dbo.OpeningInformation where BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "')) ";

                //Get Total Subway Sales
                string query = " select AmtTotalSales as amount from Report.OpeningInformationsSummary where OpeningInformationId = (select OpeningInformationId from  dbo.OpeningInformation where BusinessDate = '" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' ) ";

                con = srdbu.OpenConnection();

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

                SqlDataReader reader = comm.ExecuteReader();

                if (reader.Read())
                {
                    amount = ValidationUtility.ToDouble(reader["amount"].ToString());

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

            catch (Exception ex)
            {
                log.Error(" Exception in  GetTotalAmountWithTax Method  ", ex);
            }
            finally
            {
                srdbu.CloseConnection(con);

            }

            return amount;
        }