public OffersModelList UpgradeOfferDetails(string userID, string customerType = "")
        {
            int activetype = 1;

            if (customerType == "inactive")
            {
                activetype = 0;
            }

            OffersModelList _OffersModelList = new OffersModelList();
            _OffersModelList.OffersList = new List<OffersModel>();
            string Sqlconnectionstring = ConfigurationManager.ConnectionStrings["HDCConnection"].ConnectionString;
            try
            {
                SqlConnection sqlcon = new SqlConnection(Sqlconnectionstring);
                sqlcon.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT
                                                         A.CustUsageID AS CustUsageID
                                                          ,[Cust_FirstName]
                                                          ,[Cust_LastName]
                                                          ,[DataImage]
                                                          ,TariffDescription
                                                          ,TariffImage
                                                          ,TariffCost
                                                          ,serviceType
                                                      FROM [dbo].Tbl_CustUsage A inner join
                                                      [dbo].Tbl_Tariffmaster B On A.TariffID = B.TariffID
                                                      Where A.userID = '" + userID + "' and A.isactive='" + activetype + "'", sqlcon);

                SqlDataReader SqlDR = cmd.ExecuteReader();

                while (SqlDR.Read())
                {
                    OffersModel _OffersModel = new OffersModel();
                    _OffersModel.ProductID = SqlDR["CustUsageID"].ToString();
                    _OffersModel.Description = SqlDR["TariffDescription"].ToString();
                    _OffersModel.Cost = SqlDR["TariffCost"].ToString();
                    _OffersModel.Image = SqlDR["TariffImage"].ToString();
                    _OffersModelList.ServiceType = SqlDR["serviceType"].ToString();
                    _OffersModelList.OffersList.Add(_OffersModel);
                }
               sqlcon.Close();
            }
            catch (Exception ex)
            {

            }
            return _OffersModelList;
        }
        public OffersModelList GetUserProfile(string userID)
        {
            int activetype = 1;
            OffersModelList _OffersModelList = new OffersModelList();
            _OffersModelList.OffersList = new List<OffersModel>();
            string Sqlconnectionstring = ConfigurationManager.ConnectionStrings["HDCConnection"].ConnectionString;
            try
            {
                SqlConnection sqlcon = new SqlConnection(Sqlconnectionstring);
                sqlcon.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT [CustUsageID]
                                                          ,[Cust_FirstName]
                                                          ,[Cust_LastName]

                                                          ,[DataImage]

                                                          ,A.[TariffID]
                                                          ,B.TariffDescription
                                                          ,B.TariffImage
                                                          ,B.TariffCost
                                                          ,B.serviceType
                                                      FROM [dbo].[Tbl_CustUsage] A
                                                      inner join [dbo].[tbl_TariffMaster] B On A.TariffID = B.TariffID
                                                      Where A.userID = '" + userID + "' and A.isactive='" + activetype + "'", sqlcon);

                SqlDataReader SqlDR = cmd.ExecuteReader();

                while (SqlDR.Read())
                {
                    OffersModel _OffersModel = new OffersModel();
                    _OffersModel.ProductID = SqlDR["TariffID"].ToString();
                    _OffersModel.Description = SqlDR["TariffDescription"].ToString();
                    _OffersModel.Cost = SqlDR["TariffCost"].ToString();
                    _OffersModel.Image = SqlDR["TariffImage"].ToString();
                    _OffersModelList.ServiceType = SqlDR["serviceType"].ToString();
                    _OffersModelList.OffersList.Add(_OffersModel);
                }
                SqlDR.Close();
                SqlCommand cmd1 = new SqlCommand(@"CREATE TABLE #tmpCust (CustomerID INT,Cust_FirstName nCHAR(10),Cust_LastName nCHAR(10),strStatus VARCHAR(50))

                                                INSERT INTO #tmpCust(CustomerID,Cust_FirstName,Cust_LastName)
                                                SELECT CustomerID,Cust_FirstName,Cust_LastName FROM
                                                (
                                                SELECT  [CustomerID] , A.Cust_FirstName,A.Cust_LastName
             	                                                ,SUM(B.TariffCost) AS TariffCost
                                                FROM [dbo].[Tbl_CustUsage] A
                                                inner join [dbo].[tbl_TariffMaster] B On A.TariffID = B.TariffID
                                                group by A.customerID, A.Cust_FirstName,A.Cust_LastName
                                                ) A
                                                ORDER BY A.TariffCost DESC

                                                UPDATE TOP (25) PERCENT tmp SET tmp.strStatus = 'Gold'
                                                FROM #tmpCust tmp WHERE tmp.strStatus IS NULL

                                                UPDATE TOP (25) PERCENT tmp SET tmp.strStatus = 'Silver'
                                                FROM #tmpCust tmp WHERE tmp.strStatus IS NULL

                                                UPDATE TOP (25) PERCENT tmp SET tmp.strStatus = 'Platinum'
                                                FROM #tmpCust tmp WHERE tmp.strStatus IS NULL

                                                UPDATE TOP (25) PERCENT tmp SET tmp.strStatus = 'Bronze'
                                                FROM #tmpCust tmp WHERE tmp.strStatus IS NULL

                                                SELECT * FROM #tmpCust where CustomerID=(Select DISTINCT CustomerID  from Tbl_Login Where  UserID= '" + userID + "')  DROP TABLE #tmpCust", sqlcon);

                SqlDataReader SqlDr1 = cmd1.ExecuteReader();
                while (SqlDr1.Read())
                {
                    _OffersModelList.CustomerType = SqlDr1["strStatus"].ToString();
                }
                SqlDr1.Close();
                sqlcon.Close();
            }
            catch (Exception ex)
            {

            }
            return _OffersModelList;
        }
        public OffersModelList GetUserTarrif(string userID)
        {
            OffersModelList _OffersModelList = new OffersModelList();
            OffersModel _OffersModel = new OffersModel();
            string Sqlconnectionstring = ConfigurationManager.ConnectionStrings["HDCConnection"].ConnectionString;
            try
            {
                SqlConnection sqlcon = new SqlConnection(Sqlconnectionstring);
                sqlcon.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT [Cust_FirstName]
                                                          ,[Cust_LastName]
                                                          ,[DataImage]
                                                          ,A.[TariffID]
                                                          ,B.TariffDescription
                                                          ,B.TariffImage
                                                          ,B.TariffCost
                                                          ,B.serviceType
                                                      FROM [dbo].[Tbl_CustUsage] A
                                                      inner join [dbo].[tbl_TariffMaster] B On A.TariffID = B.TariffID
                                                      Where A.CustomerID = " + userID + "", sqlcon);
                SqlDataReader SqlDR = cmd.ExecuteReader();

                while (SqlDR.Read())
                {
                    _OffersModel.ProductID = SqlDR["TariffID"].ToString();
                    _OffersModel.Description = SqlDR["TariffDescription"].ToString();
                    _OffersModel.Cost = SqlDR["TariffCost"].ToString();
                    _OffersModel.Image = SqlDR["TariffImage"].ToString();
                    _OffersModelList.ServiceType = SqlDR["serviceType"].ToString();
                    _OffersModelList.CustomerType = SqlDR["customerType"].ToString();
                    _OffersModelList.OffersList.Add(_OffersModel);
                }
                sqlcon.Close();
            }
            catch (Exception ex)
            {

            }
            return _OffersModelList;
        }