public List <JadwalHariPerJamModel> GetDetil(string kodeDokter, int hari, string jamMulai)
{
List <JadwalHariPerJamModel> retVal = null;
string kodeJadwal = GetId(kodeDokter, hari, jamMulai);
string sSql = @"
SELECT aa.fs_jam, aa.fn_durasi, aa.fn_max,
aa.fn_booked
FROM ta_jadwal_hari_per_jam aa
WHERE fs_kd_jadwal = @KodeJadwal
ORDER BY cc.fs_nm_layanan, bb.fs_nm_dokter ";
using (SqlConnection conn = new SqlConnection(_connString))
using (SqlCommand cmd = new SqlCommand(sSql, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@KodeJadwal", kodeJadwal);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
retVal = new List <JadwalHariPerJamModel>();
while (dr.Read())
{
var item = new JadwalHariPerJamModel();
item.Jam = dr["fs_jam"].ToString();
item.Durasi = Convert.ToInt16(dr["fn_durasi"].ToString());
item.Max = Convert.ToInt16(dr["fn_max"].ToString());
item.Booked = Convert.ToInt16(dr["fn_booked"].ToString());
retVal.Add(item);
}
}
}
return(retVal);
}
public JadwalHariModel GetById(string id)
{
JadwalHariModel retVal = null;
string sSql = @"
SELECT aa.fs_kd_dokter, aa.fs_kd_layanan, aa.fn_hari,
aa.fs_jam_mulai, aa.fs_jam_selesai,
ISNULL(bb.fs_nm_dokter, ' ') fs_nm_dokter,
ISNULL(cc.fs_nm_layanan, ' ') fs_nm_layanan
FROM ta_jadwal_hari aa
LEFT JOIN ta_dokter bb ON aa.fs_kd_dokter = bb.fs_kd_dokter
LEFT JOIN ta_layanan cc ON aa.fs_kd_layanan = cc.fs_kd_layanan
WHERE fs_kd_jadwal = @Kode ";
using (SqlConnection conn = new SqlConnection(_connString))
using (SqlCommand cmd = new SqlCommand(sSql, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@Kode", id);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
retVal = new JadwalHariModel();
retVal.Kode = id;
retVal.KodeDokter = dr["fs_kd_dokter"].ToString();
retVal.NamaDokter = dr["fs_nm_dokter"].ToString();
retVal.KodeLayanan = dr["fs_kd_layanan"].ToString();
retVal.NamaLayanan = dr["fs_nm_layanan"].ToString();
retVal.Hari = Convert.ToInt16(dr["fn_hari"].ToString());
retVal.JamMulai = dr["fs_jam_mulai"].ToString();
retVal.JamSelesai = dr["fs_jam_selesai"].ToString();
}
dr.Close();
// query detil
sSql = @"
SELECT fs_jam, fn_durasi, fn_max, fn_booked
FROM ta_jadwal_hari_jam
WHERE fs_kd_jadwal = @Kode ";
cmd.CommandText = sSql;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Kode", id);
SqlDataReader dr2 = cmd.ExecuteReader();
if (dr2.HasRows)
{
retVal.JadwalPerJams = new List <JadwalHariPerJamModel>();
while (dr2.Read())
{
JadwalHariPerJamModel item = new JadwalHariPerJamModel();
item.Jam = dr2["fs_jam"].ToString();
item.Durasi = Convert.ToInt16(dr2["fn_durasi"].ToString());
item.Max = Convert.ToInt16(dr2["fn_max"].ToString());
item.Booked = Convert.ToInt16(dr2["fn_booked"].ToString());
retVal.JadwalPerJams.Add(item);
}
}
}
return(retVal);
}