public dynamic GetUserBetPointsDetails(int userID) { oCon.Open(); string fetchQuery = "SELECT * FROM view_userdetails WHERE UserID = " + userID; List <UserPointsModel> userbets = new List <UserPointsModel>(); oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand(fetchQuery, oCon); cmd.ExecuteNonQuery(); oMySQLData.MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { UserPointsModel userbet = new UserPointsModel(); userbet.UserID = Convert.ToInt32(reader["UserID"]); userbet.TournamentID = Convert.ToInt32(reader["TournamentID"]); userbet.TotalPoints = Convert.ToInt32(reader["TotalBetPoints"]); userbet.TournamentPoints = Convert.ToInt32(reader["TournamentPoints"]); userbets.Add(userbet); } oCon.Close(); return(userbets); }
public async Task AddUpdate(AssessmentCalculationModel model) { Thread.CurrentThread.CurrentCulture = culture; UserPointsModel pointModel = new UserPointsModel(); try { using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)) { using (SqlConnection connection1 = new SqlConnection(connetionString)) { await connection1.OpenAsync(); AssessmentCalculations calculations = new AssessmentCalculations(); await calculations.StarAsync(model); await SaveForm(connection1, model, model.idForm); await SaveAnswers(connection1, model, model.idForm); await SavePoints(connection1, model, model.idForm); //await GetPointUsers(connection1, model.email, pointModel); } scope.Complete(); } } catch (TransactionAbortedException ex) { Console.WriteLine(ex.Message); Console.ReadKey(); } catch (ApplicationException ex) { Console.WriteLine(ex.Message); Console.ReadKey(); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.ReadKey(); } }
private async Task GetPointUsers(SqlConnection connection1, string email, UserPointsModel pointModel) { List <int> health = new List <int>(); List <int> lifestyle = new List <int>(); List <int> biodata = new List <int>(); List <int> mobility = new List <int>(); List <int> fitness = new List <int>(); List <int> total = new List <int>(); string strcommand = "SELECT health,lifestyle,biodata,mobility,fitness,total" + "FROM AssessmentsScore " + "where datescore = @datescore "; /* * * string strcommand = "SELECT f.email,f.conversionTotal,f.datelocal, " + * "f.health,f.lifestyle,f.biodata,f.mobility,f.fitness " + * "FROM " + * "( " + * "select email, max(datelocal) as local " + * "from Assessments " + * "group by email " + * " ) " + * "as x inner join Assessments as f on f.email = x.email and f.datelocal = x.local and (f.status is null or f.status = '') " + * "order by f.conversionTotal "; * * * SqlCommand command1 = new SqlCommand(strcommand, connection1); * * SqlDataReader reader = await command1.ExecuteReaderAsync(); * * List<int> Health = new List<int>(); * List<int> Lifestyle = new List<int>(); * List<int> Biodata = new List<int>(); * List<int> Mobility = new List<int>(); * List<int> Fitness = new List<int>(); * * int HealthP = 0; * int LifestyleP = 0; * int BiodataP = 0; * int MobilityP = 0; * int FitnessP = 0; * * int rank = 0; * int count = 1; * int totalPoints = 0; * DateTime date = new DateTime(); * int min = 0; * int max = 0; * bool first = false; * * while (await reader.ReadAsync()) * { * int value = reader.GetInt32(1); * if (!first) * { * min = value; * first = true; * } * max = value; * if (email.ToUpper() == reader.GetString(0).ToUpper()) * { * rank = count; * totalPoints = value; * date = reader.GetDateTime(2); * * HealthP = reader.GetInt32(3) * -1 + 100; * LifestyleP = reader.GetInt32(4) * -1 + 100; * MobilityP = reader.GetInt32(5) * -1 + 100; * BiodataP = reader.GetInt32(6) * -1 + 100; * FitnessP = reader.GetInt32(7) * -1 + 100; * * } * * Health.Add(reader.GetInt32(3) * -1 + 100); * Lifestyle.Add(reader.GetInt32(4) * -1 + 100); * Mobility.Add(reader.GetInt32(5) * -1 + 100); * Biodata.Add(reader.GetInt32(6) * -1 + 100); * Fitness.Add(reader.GetInt32(7) * -1 + 100); * * count++; * } * count--; * * reader.Close(); * * strcommand = "SELECT firstname, lastname " + * "FROM users " + * "where UPPER(email) = @email "; * * command1 = new SqlCommand(strcommand, connection1); * * SqlParameter parameter = new SqlParameter("@email", SqlDbType.VarChar); * parameter.Value = email.ToUpper(); * command1.Parameters.Add(parameter); * * reader = await command1.ExecuteReaderAsync(); * * string firstName = ""; * string LastName = ""; * * while (await reader.ReadAsync()) * { * firstName = reader.GetString(0); * LastName = reader.GetString(1); * } * reader.Close(); * * pointModel.first = firstName; * pointModel.last = LastName; * pointModel.points = totalPoints; * pointModel.totalUsers = count; * double rankPoints = ((double)rank / (double)count) * 100; * pointModel.avarage = (Int32)Math.Truncate(rankPoints); * if (pointModel.avarage == 0) * { * pointModel.avarage = 1; * } * pointModel.date = date; * pointModel.min = min; * pointModel.max = max; * * Health.Sort(); * rank = Health.IndexOf(HealthP) + 1; * rankPoints = ((double)rank / (double)count) * 100; * pointModel.Health = (Int32)Math.Truncate(rankPoints); * if (pointModel.Health == 0) * { * pointModel.Health = 1; * } * * Lifestyle.Sort(); * rank = Lifestyle.IndexOf(LifestyleP) + 1; * rankPoints = ((double)rank / (double)count) * 100; * pointModel.Lifestyle = (Int32)Math.Truncate(rankPoints); * if (pointModel.Lifestyle == 0) * { * pointModel.Lifestyle = 1; * } * * Biodata.Sort(); * rank = Biodata.IndexOf(BiodataP) + 1; * rankPoints = ((double)rank / (double)count) * 100; * pointModel.Biodata = (Int32)Math.Truncate(rankPoints); * if (pointModel.Biodata == 0) * { * pointModel.Biodata = 1; * } * * Mobility.Sort(); * rank = Mobility.IndexOf(MobilityP) + 1; * rankPoints = ((double)rank / (double)count) * 100; * pointModel.Mobility = (Int32)Math.Truncate(rankPoints); * if (pointModel.Mobility == 0) * { * pointModel.Mobility = 1; * } * * Fitness.Sort(); * rank = Fitness.IndexOf(FitnessP) + 1; * rankPoints = ((double)rank / (double)count) * 100; * pointModel.Fitness = (Int32)Math.Truncate(rankPoints); * if (pointModel.Fitness == 0) * { * pointModel.Fitness = 1; * } */ }