public string GetClientName(int ClientId) { AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(ClientId) == 0) { return(""); } var objClient = _db.Clients .Where(u => u.Id == ClientId) .Select(u => u.Name); var json = JsonConvert.SerializeObject(objClient, Formatting.Indented, new JsonSerializerSettings { ReferenceLoopHandling = ReferenceLoopHandling.Ignore }); return(json); }
public HttpResponseMessage addMarketBase([FromBody] JObject request) { if (request != null) { int clientID = 0; if (request["clientid"] != null) { string clientid = Convert.ToString(request["clientid"]); int.TryParse(clientid, out clientID); } AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(clientID) == 0) { return(Request.CreateResponse(HttpStatusCode.NoContent)); } string subId = request["subscriptionid"].ToString(); var MktId = request["mktbaseid"].ToList(); int subscriptionId = 0; int.TryParse(subId, out subscriptionId); for (int i = 0; i < MktId.Count; i++) { int mktBaseid = 0; int.TryParse(MktId[i].ToString(), out mktBaseid); int recCnt = dbContext.subscriptionMarket.Count(p => p.SubscriptionId == subscriptionId && p.MarketBaseId == mktBaseid); if (recCnt <= 0) { dbContext.subscription.Where(x => x.SubscriptionId == subscriptionId).FirstOrDefault().LastModified = DateTime.Now; dbContext.subscriptionMarket.Add(new SubscriptionMarket { SubscriptionId = subscriptionId, MarketBaseId = mktBaseid }); } } dbContext.SaveChanges(); } return(Request.CreateResponse(HttpStatusCode.Created)); }
public HttpResponseMessage updateSubscription([FromBody] JObject request) { if (request != null) { int clientID = 0; if (request["clientid"] != null) { string clientid = Convert.ToString(request["clientid"]); int.TryParse(clientid, out clientID); } AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(clientID) == 0) { return(Request.CreateResponse(HttpStatusCode.NoContent)); } string subId = request["subscriptionid"].ToString(); string fromDate = request["fromdate"].ToString(); string toDate = request["todate"].ToString(); int subscriptionId = 0; DateTime fDate, tDate; int.TryParse(subId, out subscriptionId); DateTime.TryParse(fromDate, out fDate); DateTime.TryParse(toDate, out tDate); Subscription sObj = dbContext.subscription.Where(p => p.SubscriptionId == subscriptionId).SingleOrDefault(); if (sObj != null) { sObj.StartDate = fDate; sObj.EndDate = tDate; sObj.LastModified = DateTime.Now; dbContext.SaveChanges(); } } return(Request.CreateResponse(HttpStatusCode.Created)); }
public DataTable LoginInfo(string userName, string pwd) { AuthController authController = new AuthController(); pwd = authController.GenerateHashedPassword(pwd); int passwordExpirationAge = 90; if (ConfigurationManager.AppSettings["PasswordExpirationAge"] != null) { passwordExpirationAge = Convert.ToInt32(ConfigurationManager.AppSettings["PasswordExpirationAge"]); } DataTable dataTable = new DataTable(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("[dbo].CheckUserLogin", conn)) { cmd.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@pwd", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@pwdAgeForNewPwd", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@pwdAgeForRegularPwd", SqlDbType.Int)); cmd.Parameters["@userName"].Value = userName; cmd.Parameters["@pwd"].Value = pwd; cmd.Parameters["@pwdAgeForNewPwd"].Value = 1; cmd.Parameters["@pwdAgeForRegularPwd"].Value = passwordExpirationAge; cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dataReader = cmd.ExecuteReader(); dataTable.Load(dataReader); } } return(dataTable); }
public HttpResponseMessage ChangePasswordByResetToken([FromBody] UserDetail userdeatil) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.ExpectationFailed); try { using (EverestPortalContext context = new EverestPortalContext()) { var result = context.ResetTokens.FirstOrDefault(x => x.Token == userdeatil.Token); if (result != null && result.user != null) { //if (result.ExpiryDate >= DateTime.Now && result.user.IsPasswordVerified) if (result.ExpiryDate >= DateTime.Now) { AuthController authController = new AuthController(); var passwordHashed = authController.GenerateHashedPassword(userdeatil.Password); var passwordHistoryExist = context.PasswordHistories.Where(x => x.UserID.Equals(result.user.UserID)) .OrderByDescending(x => x.CreatedDate).Take(12).FirstOrDefault(x => x.Password == passwordHashed); if (passwordHistoryExist != null || result.user.Password == passwordHashed) { response = Request.CreateResponse(HttpStatusCode.OK, new { isSuccess = false, message = " You cannot reuse one of your last 13 passwords" }); } else { context.PasswordHistories.Add( new Models.Security.PasswordHistory { UserID = result.user.UserID, Password = passwordHashed, CreatedDate = DateTime.Now }); result.user.Password = passwordHashed; result.user.FailedPasswordAttempt = 0; result.user.IsPasswordVerified = true; result.user.PasswordCreatedDate = DateTime.Now; result.ExpiryDate = DateTime.Now; context.SaveChanges(); response = Request.CreateResponse(HttpStatusCode.OK, new { isSuccess = true, message = "" }); } } else if (result.ExpiryDate < DateTime.Now) { response = Request.CreateResponse(HttpStatusCode.OK, new { isSuccess = false, message = "Your password reset link has been expired." }); } else { response = Request.CreateResponse(HttpStatusCode.OK, new { isSuccess = false, message = "Your account has not yet been verified. Please contact support." }); } } else { response = Request.CreateResponse(HttpStatusCode.OK, new { isSuccess = false, message = "User inactive or email address invalid." }); } } } catch (Exception ex) { } return(response); }
public async Task <HttpResponseMessage> EditMarketBase(int ClientId, int MarketBaseId) { AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(ClientId) == 0) { return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit this marketbase")); } HttpContent requestContent = Request.Content; var jsonContent = requestContent.ReadAsStringAsync().Result; // var marketBase = JsonConvert.DeserializeObject<MarketBase>(jsonContent); var marketBaseDetails = JsonConvert.DeserializeObject <MarketbaseDetails>(jsonContent); List <DimensionBaseMap> Dimension = marketBaseDetails.Dimension; //to get dimension seperatly. List <PxR> PxRCollection = marketBaseDetails.PxR; //to get PxR seperatly. MarketBase marketBase = marketBaseDetails.MarketBase; var identity = (ClaimsIdentity)User.Identity; int uid = Convert.ToInt32(identity.Claims.FirstOrDefault(c => c.Type == "userid").Value.ToString()); Guid Id = Guid.NewGuid(); if (jsonContent.Trim() != string.Empty) { MarketBase MBObj = null; using (var db = new EverestPortalContext()) { var oldBaseFilter = db.BaseFilters.Where(i => i.MarketBaseId == MarketBaseId).ToList(); if (oldBaseFilter != null) { db.BaseFilters.RemoveRange(oldBaseFilter); db.SaveChanges(); } MBObj = db.MarketBases.Where(i => i.Id == MarketBaseId).FirstOrDefault(); if (MBObj != null) { MBObj.BaseType = marketBase.BaseType; MBObj.Description = marketBase.Description; MBObj.DurationFrom = marketBase.DurationFrom; MBObj.DurationTo = marketBase.DurationTo; MBObj.Name = marketBase.Name; MBObj.Suffix = marketBase.Suffix; MBObj.LastSaved = DateTime.Now; foreach (BaseFilter fil in marketBase.Filters) { BaseFilter obj = fil; obj.MarketBaseId = MarketBaseId; MBObj.Filters.Add(obj); } MBObj.LastModified = DateTime.Now; MBObj.ModifiedBy = uid; } else { marketBase.GuiId = Id.ToString(); // marketBase.LastSaved = DateTime.Now; marketBase.LastModified = DateTime.Now; marketBase.ModifiedBy = uid; db.MarketBases.Add(marketBase); } db.SaveChanges(); //to save dimension base map await SaveDimensionBaseMap(MarketBaseId, Dimension); await SavePxRBaseMap(MarketBaseId, PxRCollection); } } var objClient = _db.MarketBases.Where(u => u.Id == MarketBaseId).ToList <MarketBase>().FirstOrDefault(); // place the request in queue var result = _db.Database.SqlQuery <string>("exec PutInMarketBaseQueue " + MarketBaseId + "," + uid).FirstOrDefault(); var json = JsonConvert.SerializeObject(result, Formatting.Indented, new JsonSerializerSettings { ReferenceLoopHandling = ReferenceLoopHandling.Ignore }); return(Request.CreateResponse(HttpStatusCode.OK, json)); }
public string GetSubscriptions(int clientid) { AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(clientid) == 0) { return(""); } List <SubscriptionMarket> lstsubmkt = new List <SubscriptionMarket>(); lstsubmkt = dbContext.subscriptionMarket.Where(q => q.subscription.ClientId == clientid).ToList(); List <ClientSubscriptionDTO> sublist = new List <ClientSubscriptionDTO>(); List <MarketBase> mktlist = new List <MarketBase>(); int subid = 0; int newSubid = 0; foreach (var r in lstsubmkt) { ClientSubscriptionDTO dto; Models.Subscription.SubscriptionDTO sDto; List <MarketBase> lstMkt; //MarketBase objMbase; subid = r.SubscriptionId; if (subid != newSubid) { dto = new ClientSubscriptionDTO(); sDto = new Models.Subscription.SubscriptionDTO(); dto.ClientId = r.subscription.ClientId; // dto.subscription = r.subscription; sDto.SubscriptionId = r.SubscriptionId; sDto.Name = r.subscription.Name; sDto.ClientId = r.subscription.ClientId; sDto.CountryId = r.subscription.CountryId; sDto.ServiceId = r.subscription.ServiceId; sDto.DataTypeId = r.subscription.DataTypeId; sDto.SourceId = r.subscription.SourceId; sDto.Country = r.subscription.country.Name; sDto.Service = r.subscription.service.Name; sDto.Data = r.subscription.dataType.Name; sDto.Source = r.subscription.source.Name; sDto.StartDate = r.subscription.StartDate; sDto.EndDate = r.subscription.EndDate; sDto.ServiceTerritoryId = r.subscription.ServiceTerritoryId; sDto.Active = r.subscription.Active; sDto.LastModified = r.subscription.LastModified; sDto.ModifiedBy = r.subscription.ModifiedBy; sDto.serviceTerritory = r.subscription.serviceTerritory; dto.subscription = sDto; var listOfmktid = lstsubmkt.Where(r1 => r1.SubscriptionId == subid && r1.subscription.EndDate.Year >= DateTime.Now.Year).Select(t => t.MarketBaseId); lstMkt = new List <MarketBase>(); lstMkt = lstsubmkt.Where(t2 => t2.SubscriptionId == subid).Select(t3 => t3.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList(); dto.MarketBase = lstMkt; sublist.Add(dto); //dto = new ClientSubscriptionDTO(); //dto.subscription = r.subscription; //var listOfmktid = lstsubmkt.Where(r1 => r1.SubscriptionId == subid && r1.subscription.EndDate.Year >= DateTime.Now.Year).Select(t => t.MarketBaseId); //lstMkt = new List<MarketBase>(); //lstMkt = lstsubmkt.Where(t2 => t2.SubscriptionId == subid).Select(t3 => t3.marketBase).ToList(); //dto.MarketBase = lstMkt; //sublist.Add(dto); } newSubid = r.SubscriptionId; } var json = JsonConvert.SerializeObject(sublist, Formatting.Indented, new JsonSerializerSettings { ReferenceLoopHandling = ReferenceLoopHandling.Ignore }); return(json); }
public HttpResponseMessage DownloadSubscriptions(int clientid) { AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(clientid) == 0) { return(new HttpResponseMessage(HttpStatusCode.NotFound)); } List <SubscriptionMarket> lstsubmkt = new List <SubscriptionMarket>(); List <ClientSubscriptionDTO> sublist = new List <ClientSubscriptionDTO>(); List <MarketBase> mktlist = new List <MarketBase>(); DataTable subdcriptionDT = new DataTable(); var objSub = dbContext.subscription.Where(u => u.ClientId == clientid && u.EndDate.Year >= DateTime.Now.Year).ToList(); if (objSub != null && objSub.Count > 0) { foreach (var sub in objSub) { ClientSubscriptionDTO dto = new ClientSubscriptionDTO(); Models.Subscription.SubscriptionDTO sDto = new Models.Subscription.SubscriptionDTO(); dto.ClientId = sub.ClientId; //sub.client.MarketDefinitions = new List<MarketDefinition>(); //dto.subscription = sub; sDto.SubscriptionId = sub.SubscriptionId; sDto.Name = sub.Name; sDto.ClientId = sub.ClientId; var cList = dbContext.Clients.Where(z => z.Id == sub.ClientId).ToList().FirstOrDefault(); dto.ClientName = cList.Name; //dto.ClientName = sDto.CountryId = sub.CountryId; sDto.ServiceId = sub.ServiceId; sDto.DataTypeId = sub.DataTypeId; sDto.SourceId = sub.SourceId; sDto.Country = sub.country.Name; sDto.Service = sub.service.Name; sDto.Data = sub.dataType.Name; sDto.Source = sub.source.Name; sDto.StartDate = sub.StartDate; sDto.EndDate = sub.EndDate; sDto.ServiceTerritoryId = sub.ServiceTerritoryId; sDto.Active = sub.Active; sDto.LastModified = sub.LastModified; sDto.ModifiedBy = sub.ModifiedBy; sDto.serviceTerritory = sub.serviceTerritory; dto.subscription = sDto; var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList(); if (submket != null && submket.Count() > 0) { dto.MarketBase = submket.Select(a => a.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList(); } //dto.subscription.client.MarketDefinitions = new List<MarketDefinition>(); sublist.Add(dto); ////subdcriptionDT = ListToDataTable(sublist); //string xmlString = SerializeXml(sublist); //DataSet ds = new DataSet("New_DataSet"); //using (System.Xml.XmlReader reader = System.Xml.XmlReader.Create(new StringReader(xmlString))) //{ // ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; // ds.ReadXml(reader); // subdcriptionDT = ds.Tables[0]; //} subdcriptionDT = BuildsubscriptionDataTable(sublist); //ClientSubscriptionDTO dto = new ClientSubscriptionDTO(); //dto.ClientId = sub.ClientId; //sub.client.MarketDefinitions = new List<MarketDefinition>(); //dto.subscription = sub; //var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList(); //if (submket != null && submket.Count() > 0) //{ // dto.MarketBase = submket.Select(a => a.marketBase).ToList(); //} //dto.subscription.client.MarketDefinitions = new List<MarketDefinition>(); //sublist.Add(dto); } } //foreach (var xx in sublist) //{ // xx.subscription.client.MarketDefinitions.RemoveRange(0, xx.subscription.client.MarketDefinitions.Count); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); MemoryStream stream = new MemoryStream(); stream = GetExcelStream(subdcriptionDT); // Reset Stream Position stream.Position = 0; result.Content = new StreamContent(stream); // Generic Content Header result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); return(result); }
public HttpResponseMessage GetClientSubscriptions(int clientid) { AuthController objAuth = new AuthController(); if (objAuth.CheckUserClients(clientid) == 0) { return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit subscription.")); } //List<SubscriptionMarket> lstsubmkt = new List<SubscriptionMarket>(); //List<ClientSubscriptionDTO> sublist = new List<ClientSubscriptionDTO>(); //List<MarketBase> mktlist = new List<MarketBase>(); //var objSub = dbContext.subscription.Where(u => u.ClientId == clientid && u.EndDate.Year >= DateTime.Now.Year).ToList(); //var subscriptionIdList = (from o in objSub select o.SubscriptionId).ToList(); //var subscriptionMarket = (from subMkt in dbContext.subscriptionMarket // where subscriptionIdList.Contains(subMkt.SubscriptionId) // select subMkt).ToList(); //var subhistory = (from sub in dbContext.Subscription_History // where sub.ClientId == clientid && sub.EndDate.Year >= DateTime.Now.Year // group sub by sub.SubscriptionId into tempGrp // let maxVersion = tempGrp.Max(x => x.Version) // select new // { // SubscriptionId = tempGrp.Key, // LastSaved = tempGrp.FirstOrDefault(y => y.Version == maxVersion).LastSaved, // MaxVer = maxVersion // }).ToList(); ////var subHistory = dbContext.Database.SqlQuery<Subscription_History>("select MarketDefId,max(Version) as [Version],Name,Description,ClientId,GuiId,DimensionId,ModifiedDate,UserId,IsSentToTDW,TDWTransferDate,TDWUserId,LastSaved from MarketDefinitions_History where ClientId=" + clientid + " group by MarketDefId,Name"); //if (objSub != null && objSub.Count > 0) //{ // foreach (var sub in objSub) // { // ClientSubscriptionDTO dto = new ClientSubscriptionDTO(); // Models.Subscription.SubscriptionDTO sDto = new Models.Subscription.SubscriptionDTO(); // dto.ClientId = sub.ClientId; // //sub.client.MarketDefinitions = new List<MarketDefinition>(); // //dto.subscription = sub; // sDto.SubscriptionId = sub.SubscriptionId; // sDto.Name = sub.Name; // sDto.ClientId = sub.ClientId; // sDto.CountryId = sub.CountryId; // sDto.ServiceId = sub.ServiceId; // sDto.DataTypeId = sub.DataTypeId; // sDto.SourceId = sub.SourceId; // sDto.Country = sub.country.Name; // sDto.Service = sub.service.Name; // sDto.Data = sub.dataType.Name; // sDto.Source = sub.source.Name; // sDto.StartDate = sub.StartDate; // sDto.EndDate = sub.EndDate; // sDto.ServiceTerritoryId = sub.ServiceTerritoryId; // sDto.Active = sub.Active; // sDto.LastModified = sub.LastModified; // sDto.ModifiedBy = sub.ModifiedBy; // sDto.serviceTerritory = sub.serviceTerritory; // var res = subhistory.FirstOrDefault(i => i.SubscriptionId == sub.SubscriptionId); // sDto.Submitted = (res == null || res.LastSaved != sDto.LastModified) ? "No" : "Yes"; // dto.subscription = sDto; // //var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList(); // var submket = subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId) // .Select(a => a.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList(); // if (submket != null && submket.Count() > 0) // { // dto.MarketBase = submket; // //dto.MarketBase = submket.Select(a => a.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList(); // } // //dto.subscription.client.MarketDefinitions = new List<MarketDefinition>(); // sublist.Add(dto); // //ClientSubscriptionDTO dto = new ClientSubscriptionDTO(); // //dto.ClientId = sub.ClientId; // //sub.client.MarketDefinitions = new List<MarketDefinition>(); // //dto.subscription = sub; // //var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList(); // //if (submket != null && submket.Count() > 0) // //{ // // dto.MarketBase = submket.Select(a => a.marketBase).ToList(); // //} // //dto.subscription.client.MarketDefinitions = new List<MarketDefinition>(); // //sublist.Add(dto); // } //} // New code var clientSubscription = dbContext.subscription .GroupJoin(dbContext.subscriptionMarket, s => s.SubscriptionId, m => m.SubscriptionId, (s, m) => new { S = s, M = m }) .SelectMany( m => m.M.DefaultIfEmpty() , (x, y) => new { Subscription = x.S, SubscriptionMarket = y }) .GroupJoin(dbContext.Subscription_History, s => s.Subscription.SubscriptionId, h => h.SubscriptionId, (s, h) => new { S = s, H = h }) .SelectMany(h => h.H.DefaultIfEmpty(), (x, y) => new { Subscription = x.S.Subscription, SubscriptionMarket = x.S.SubscriptionMarket, SubscriptionHistory = y }) .Where(u => u.Subscription.ClientId == clientid && u.Subscription.EndDate.Year >= DateTime.Now.Year) .Select(x => new { ClientId = x.Subscription.ClientId, ClientName = x.Subscription.Name, subscription = new Models.Subscription.SubscriptionDTO() { SubscriptionId = x.Subscription.SubscriptionId, Name = x.Subscription.Name, ClientId = x.Subscription.ClientId, CountryId = x.Subscription.CountryId, ServiceId = x.Subscription.ServiceId, DataTypeId = x.Subscription.DataTypeId, SourceId = x.Subscription.SourceId, Country = x.Subscription.country.Name, Service = x.Subscription.service.Name, Data = x.Subscription.dataType.Name, Source = x.Subscription.source.Name, StartDate = x.Subscription.StartDate, EndDate = x.Subscription.EndDate, ServiceTerritoryId = x.Subscription.ServiceTerritoryId, Active = x.Subscription.Active, LastModified = x.Subscription.LastModified, ModifiedBy = x.Subscription.ModifiedBy, serviceTerritory = x.Subscription.serviceTerritory, Submitted = null }, SubscriptionMarket = x.SubscriptionMarket, SubscriptionHistory = x.SubscriptionHistory }) .GroupBy(x => new { x.ClientId, x.ClientName, x.subscription }) .ToList(); var sublist = clientSubscription.Select(x => new ClientSubscriptionDTO() { ClientId = x.Key.ClientId, ClientName = x.Key.ClientName, subscription = new Models.Subscription.SubscriptionDTO() { SubscriptionId = x.Key.subscription.SubscriptionId, Name = x.Key.subscription.Name, ClientId = x.Key.subscription.ClientId, CountryId = x.Key.subscription.CountryId, ServiceId = x.Key.subscription.ServiceId, DataTypeId = x.Key.subscription.DataTypeId, SourceId = x.Key.subscription.SourceId, Country = x.Key.subscription.Country, Service = x.Key.subscription.Service, Data = x.Key.subscription.Data, Source = x.Key.subscription.Source, StartDate = x.Key.subscription.StartDate, EndDate = x.Key.subscription.EndDate, ServiceTerritoryId = x.Key.subscription.ServiceTerritoryId, Active = x.Key.subscription.Active, LastModified = x.Key.subscription.LastModified, ModifiedBy = x.Key.subscription.ModifiedBy, serviceTerritory = x.Key.subscription.serviceTerritory, Submitted = x.Select(y => y.SubscriptionHistory).First() == null ? "No" : (x.Select(y => y.SubscriptionHistory).OrderBy(o => o.Version).LastOrDefault().LastSaved < x.Key.subscription.LastModified ? "No" : "Yes") }, MarketBase = (x.Select(y => y.SubscriptionMarket).First() == null || x.Select(y => y.SubscriptionMarket.marketBase) == null) ? null : x.Select(y => GetMarketBase(y.SubscriptionMarket)).Distinct().ToList() }).ToList(); return(Request.CreateResponse(HttpStatusCode.OK, sublist)); }