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