private List <AkunPendaftaran> GetAllWith(string jalur)
{
string sqlQuery = @"SELECT ap.Id, ap.NoPendaftaran, ap.JalurPendaftaran, cs.NamaLengkap, ra.*
FROM CalonSiswa cs JOIN AkunPendaftaran ap ON cs.Id=ap.CalonSiswaId
FULL JOIN RangkumanTesAkademik ra ON ap.Id = ra.AkunPendaftaranId
WHERE JalurPendaftaran=@Jalur AND Status='Sudah Ujian'";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
var listAkun = connection.Query <AkunPendaftaran, CalonSiswa, RangkumanTesAkademik, AkunPendaftaran>(
sql: sqlQuery,
map: (ap, cs, ra) =>
{
ap.CalonSiswa = cs;
ap.Rekap = ra;
return(ap);
},
splitOn: "NamaLengkap, AkunPendaftaranId",
param: new { Jalur = jalur })
.ToList();
return(listAkun);
}
}
private void MutasiKeluar(MutasiKeluar mutasi)
{
string sqlInsertMutasi = @"INSERT INTO MutasiKeluar(SiswaId, Tujuan, Alasan, TanggalKeluar)
VALUES(@SiswaId, @Tujuan, @Alasan, @TanggalKeluar)";
string sqlUpdateStatusSiswa = @"UPDATE Siswa SET Status = 'Keluar'
WHERE Id = @SiswaId";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
using (var trans = connection.BeginTransaction())
{
try
{
connection.Execute(sql: sqlInsertMutasi, param: mutasi, transaction: trans);
connection.Execute(sql: sqlUpdateStatusSiswa, param: new { SiswaId = mutasi.SiswaId }, transaction: trans);
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
}
}
private bool IsExistInStaff(string column, string value)
{
string sqlQuery = $"SELECT {column} FROM Staff WHERE {column} = @Value";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
string result = connection.QueryFirstOrDefault <string>(sql: sqlQuery, param: new { Value = value });
return(result != null);
}
}
public void AddSiswaToKelas(int idSiswa, int idKelas)
{
string sqlQueryUpdateSiswa = @"UPDATE Siswa SET KelasId = @KelasId WHERE Id = @SiswaId";
string sqlQueryUpdateKelas = @"UPDATE Kelas SET JumlahSiswa = (JumlahSiswa+1) WHERE Id = @KelasId";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
using (var trans = connection.BeginTransaction())
{
try
{
connection.Execute(sql: sqlQueryUpdateSiswa, param: new { SiswaId = idSiswa }, transaction: trans);
connection.Execute(sql: sqlQueryUpdateKelas, param: new { KelasId = idKelas }, transaction: trans);
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
}
}
private int GetCalonSiswaId(string noPendaftaran)
{
string sqlQuery = @"SELECT CalonSiswaId FROM AkunPendaftaran WHERE NoPendaftaran = @NoPendaftaran";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
int calonSiswaId = connection.QueryFirstOrDefault <int>(sql: sqlQuery, param: new { NoPendaftaran = noPendaftaran });
return(calonSiswaId);
}
}
private void UpdateFinishStatusUjian(int akunId, int soalId)
{
string sqlQuery = @"UPDATE Ujian SET IsSelesai=1 WHERE AkunPendaftaranId = @AkunPendaftaranId AND SoalId = @SoalId";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
connection.Execute(sql: sqlQuery, param: new { AkunPendaftaranId = akunId, SoalId = soalId });
}
}
public void InsertCalonSiswa(CalonSiswa newCalonSiswa)
{
string sqlInsertCalonSiswa = @"INSERT INTO CalonSiswa(Nik, Nisn, NamaLengkap)
VALUES(@Nik, @Nisn, @NamaLengkap)";
using (var connection = new SqlConnection(_connectionHelper.GetConnectionString()))
{
connection.Open();
connection.Execute(sql: sqlInsertCalonSiswa, param: newCalonSiswa);
}
}