//public ActionResult ConstantRating(String searchBy, String search, String Gender)
public ActionResult ConstantRating(Naba2 OBJ, String searchBy, DateTime Fromdate, DateTime Todate, string search)
{
//var obj = Rate.ConstantwithRating(search, search, Gender);
var obj = Rate.ConstantwithRating(searchBy, Fromdate, Todate, OBJ.Gender, OBJ.IsActive, search);
return(View(obj));
}
public static object ContestantDetails(Naba2 a, string FullName)
{
List <Naba2> NAB = new List <Naba2>();
using (NabayubakDBEntities db = new NabayubakDBEntities())
{
var ContestantList = db.Contestants.Where(x => x.Firstname.StartsWith(FullName)).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name
})
.ToList();
foreach (var item in ContestantList)
{
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
NAB.Add(ad);
}
return(NAB);
}
}
public static object GetAllContestant()
{
List <Naba2> NAB = new List <Naba2>();
using (NabayubakDBEntities db = new NabayubakDBEntities())
{
var obj = db.Contestants.Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.Gender,
y.Name,
x.DateOfBirth,
x.IsActive
}).ToList();
foreach (var item in obj)
{
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
NAB.Add(ad);
}
return(NAB);
}
}
public static object ConstantwithRating()
{
List <Naba2> NAB = new List <Naba2>();
using (NabayubakDBEntities db = new NabayubakDBEntities())
{
var obj = db.Contestants.Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.Gender,
y.Name,
x.DateOfBirth,
x.IsActive
}).ToList();
foreach (var item in obj)
{
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id).Count();
if (CheckContestent > 0)
{
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
return(NAB);
}
public static object ConstantwithRating(String searchBy, DateTime Fromdate, DateTime Todate, string Gender, bool?IsActive, string search)
{
List <Naba2> NAB = new List <Naba2>();
try {
using (NabayubakDBEntities db = new NabayubakDBEntities())
if (searchBy == "Gender" && IsActive == true)
{
var ContestantList = db.Contestants.Where(x => x.Gender == Gender && x.IsActive == IsActive).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name,
})
.ToList();
foreach (var item in ContestantList)
{
DateTime fromdate = Fromdate.Date;
DateTime todate = Todate.Date;
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count();
if (CheckContestent > 0)
{
if (Fromdate != null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count());
}
else if (Fromdate != null && Todate == null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Count());
}
else if (Fromdate == null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Count());
}
else
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
else if (searchBy == "Gender" && IsActive == false)
{
var ContestantList = db.Contestants.Where(x => x.Gender == Gender && x.IsActive == IsActive).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name,
})
.ToList();
foreach (var item in ContestantList)
{
DateTime fromdate = Fromdate.Date;
DateTime todate = Todate.Date;
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count();
if (CheckContestent > 0)
{
if (Fromdate != null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count());
}
else if (Fromdate != null && Todate == null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Count());
}
else if (Fromdate == null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Count());
}
else
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
else if (searchBy == "Name" && IsActive == true)
{
var ContestantList = db.Contestants.Where(x => x.Firstname.StartsWith(search) && x.IsActive == IsActive).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name
})
.ToList();
foreach (var item in ContestantList)
{
DateTime fromdate = Fromdate.Date;
DateTime todate = Todate.Date;
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count();
if (CheckContestent > 0)
{
if (Fromdate != null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count());
}
else if (Fromdate != null && Todate == null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Count());
}
else if (Fromdate == null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Count());
}
else
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
else if (searchBy == "Name" && IsActive == false)
{
var ContestantList = db.Contestants.Where(x => x.Firstname.StartsWith(search) && x.IsActive == IsActive).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name
})
.ToList();
foreach (var item in ContestantList)
{
DateTime fromdate = Fromdate.Date;
DateTime todate = Todate.Date;
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count();
if (CheckContestent > 0)
{
if (Fromdate != null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count());
}
else if (Fromdate != null && Todate == null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Count());
}
else if (Fromdate == null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Count());
}
else
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
else if (Fromdate != null && Todate != null && IsActive == true)
{
var ContestantList = db.Contestants.Where(x => x.IsActive == IsActive).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name,
})
.ToList();
foreach (var item in ContestantList)
{
DateTime fromdate = Fromdate.Date;
DateTime todate = Todate.Date;
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count();
if (CheckContestent > 0)
{
if (Fromdate != null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count());
}
else if (Fromdate != null && Todate == null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Count());
}
else if (Fromdate == null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
else if (Fromdate != null && Todate != null && IsActive == false)
{
var qab = db.ContestantRatings.Where(x => x.RateDate >= Fromdate && x.RateDate <= Todate).ToList();
var ContestantList = db.Contestants.Where(x => x.IsActive == IsActive).Join(db.Districts, x => x.DistrictId, y => y.Id, (x, y) => new
{
x.Id,
x.Firstname,
x.Lastname,
x.DateOfBirth,
x.Gender,
x.IsActive,
y.Name,
})
.ToList();
foreach (var item in ContestantList)
{
DateTime fromdate = Fromdate;
DateTime todate = Todate;
decimal sum = 0;
decimal count = 0;
var CheckContestent = db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count();
if (CheckContestent > 0)
{
if (Fromdate != null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate && x.RateDate <= todate).Count());
}
else if (Fromdate != null && Todate == null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate >= fromdate).Count());
}
else if (Fromdate == null && Todate != null)
{
sum = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Select(x => x.Rating).ToList().Sum());
count = Convert.ToDecimal(db.ContestantRatings.Where(x => x.ContestantId == item.Id && x.RateDate <= todate).Count());
}
decimal AverageRate = Convert.ToDecimal(sum / count);
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = AverageRate;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
else if (CheckContestent == 0)
{
int Ratercount = CheckContestent;
Naba2 ad = new Naba2();
ad.ConstantId = item.Id;
ad.District = item.Name;
ad.Dateofbirth = item.DateOfBirth.Value.ToShortDateString();
ad.FullName = item.Firstname + " " + item.Lastname;
ad.Gender = item.Gender;
ad.IsActive = item.IsActive;
ad.Averagerating = 0;
ad.Ratercount = Ratercount;
NAB.Add(ad);
}
}
}
return(NAB);
}
catch (Exception)
{
return(0);
}
}
public ActionResult List(Naba2 a, string FullName)
{
var obj = ExtraList1.ContestantDetails(a, FullName);
return(Json(obj, JsonRequestBehavior.AllowGet));
}
