public int GetAllStudentLessonAttendance(int batchId, string bgroup, int lessonId, string userID)
{
int retVal = 0;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT COUNT(`id`) FROM `studentlessonattendance` WHERE `batchid` = @batchid AND DATE(createdate) = DATE(NOW()) AND lessonid = @lessonId AND bgroup = @bgroup";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@batchid", batchId);
cmd.Parameters.AddWithValue("@lessonId", lessonId);
cmd.Parameters.AddWithValue("@bgroup", bgroup);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
StudentLessonAttendance Mod = new StudentLessonAttendance();
retVal = dr.GetInt32(0);
}
con.Close();
return(retVal);
}
public string GetAllStudentLessonIDAttendance(int batchId, DateTime attenDate, string userID)
{
string retVal = "";
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT DISTINCT lessonid FROM `studentlessonattendance` WHERE `batchid` = @batchid AND DATE(createdate) = DATE(@attenDate) AND lessonid != 0 ";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@batchid", batchId);
cmd.Parameters.AddWithValue("@attenDate", attenDate);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
StudentLessonAttendance Mod = new StudentLessonAttendance();
retVal += dr.GetInt32(0) + ",";
}
con.Close();
return(retVal == "" ? "''" : retVal.TrimEnd(','));
}
public List <StudentLessonAttendance> GetAllStudentLessonAttendance(string userID)
{
List <StudentLessonAttendance> retVal = new List <StudentLessonAttendance>();
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT `id`, `studuserid`, `batchid`, `bgroup`,lessonid, `present`, `createdate`, `modifydate` FROM `studentlessonattendance` ";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
StudentLessonAttendance Mod = new StudentLessonAttendance();
Mod.ID = dr.GetInt32(0);
Mod.StuduserId = dr.GetString(1);
Mod.BatchId = dr.GetInt32(2);
Mod.Bgroup = dr.GetString(3);
Mod.LessonId = dr.GetInt32(4);
Mod.Present = dr.GetInt32(5);
Mod.DateCreted = dr.GetDateTime(6);
Mod.LastModified = dr.GetDateTime(7);
retVal.Add(Mod);
}
con.Close();
return(retVal);
}
public bool AddStudentLessonAttendance(StudentLessonAttendance fp, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "INSERT INTO `studentlessonattendance`(`studuserid`, `batchid`, `bgroup`,lessonid, `present`, `createdate`, `modifydate`) VALUES (@studuserid,@batchid,@bgroup,@lessonid,@present,@createdate,@modifydate)";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@studuserid", fp.StuduserId);
cmd.Parameters.AddWithValue("@batchid", fp.BatchId);
cmd.Parameters.AddWithValue("@present", fp.Present);
cmd.Parameters.AddWithValue("@createdate", fp.DateCreted);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup);
cmd.Parameters.AddWithValue("@lessonid", fp.LessonId);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
public bool UpdateStudentLessonAttendance(StudentLessonAttendance fp, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "UPDATE `studentlessonattendance` SET `studuserid`=@studuserid,`batchid`=@batchid,lessonid = @lessonid,`present`=@present,`modifydate`=@modifydate,bgroup = @bgroup WHERE id = @id";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@studuserid", fp.StuduserId);
cmd.Parameters.AddWithValue("@batchid", fp.BatchId);
cmd.Parameters.AddWithValue("@present", fp.Present);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup);
cmd.Parameters.AddWithValue("@id", fp.ID);
cmd.Parameters.AddWithValue("@lessonid", fp.LessonId);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
