public static IEnumerable <EventFassDetailParent> getPesertaListDesaScore(Guid eventid) { SqlConnection con = new SqlConnection(cs); SqlCommand cmd = new SqlCommand("Select distinct EventID,b.DesaID,NamaDesa,Sum(Skor) TotalScore from EventDetail a " + "join Peserta b on a.PesertaID=b.PesertaID join Desa c on c.desaid=b.desaid where EventID='" + eventid + "' " + "group by EventID,b.DesaID,NamaDesa order by TotalScore desc", con); if (con.State.Equals(ConnectionState.Closed)) { con.Open(); } List <EventFassDetailParent> newEvent = new List <EventFassDetailParent>(); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { EventFassDetailParent ev = new EventFassDetailParent(rd.GetGuid(0), rd.GetString(1), rd.GetString(2), rd.GetDouble(3)); newEvent.Add(ev); } rd.Close(); cmd = new SqlCommand("Select DesaID, NamaDesa from Desa where DesaID not in (Select distinct b.DesaID from EventDetail a " + "join Peserta b on a.PesertaID=b.PesertaID where EventID='" + eventid + "') and deleted=0", con); rd = cmd.ExecuteReader(); while (rd.Read()) { EventFassDetailParent ev = new EventFassDetailParent(eventid, rd.GetString(0), rd.GetString(1), 0); newEvent.Add(ev); } return(newEvent); }
public static IEnumerable <EventFassDetail> getPoinPenilaianByPeserta(EventFassDetailParent ev) { SqlConnection con = new SqlConnection(cs); SqlCommand cmd = new SqlCommand("Select EventID,a.PesertaID,a.FestivalID,a.PoinID, PoinPenilaian, Skor from EventDetail a " + "join FestivalDetail b on a.FestivalID=b.FestivalID and a.PoinID=b.PoinID where EventID='" + ev.eventID + "' and a.FestivalID='" + ev.festivalID + "' and a.PesertaID='" + ev.pesertaID + "' ", con); if (con.State.Equals(ConnectionState.Closed)) { con.Open(); } List <EventFassDetail> newEvent = new List <EventFassDetail>(); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { EventFassDetail newev = new EventFassDetail(rd.GetGuid(0), rd.GetGuid(1), rd.GetString(2), rd.GetInt32(3), rd.GetString(4), rd.GetDouble(5)); newEvent.Add(newev); } return(newEvent); }
public static IEnumerable <EventFassDetailParent> getPesertaListByFestival(FestivalClass fe, Guid eventid) { SqlConnection con = new SqlConnection(cs); SqlCommand cmd = new SqlCommand("Select distinct EventID,a.PesertaID,NamaPeserta, Usia,b.DesaID,NamaDesa,a.FestivalID, Sum(Skor) TotalScore from EventDetail a " + "join Peserta b on a.PesertaID=b.PesertaID join Desa c on c.desaid=b.desaid where EventID='" + eventid + "' and a.FestivalID='" + fe.festivalID + "' " + "group by EventID,a.PesertaID,NamaPeserta, Usia,b.DesaID,NamaDesa,a.FestivalID order by TotalScore desc, NamaPeserta asc", con); if (con.State.Equals(ConnectionState.Closed)) { con.Open(); } List <EventFassDetailParent> newEvent = new List <EventFassDetailParent>(); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { EventFassDetailParent ev = new EventFassDetailParent(rd.GetGuid(0), rd.GetGuid(1), rd.GetString(2), rd.GetString(3), rd.GetString(4), rd.GetString(5), rd.GetString(6), rd.GetDouble(7)); newEvent.Add(ev); } return(newEvent); }
public static IEnumerable <EventFassDetail> getPoinPenilaianByPeserta(EventFassDetailParent ev) { return(EventFassModel.getPoinPenilaianByPeserta(ev)); }