public int updateAttendanceRecord(DataSet.DsPSMS.ST_ATTENDANCE_DATARow adr)
{
if (adr == null)
{
return(-1);
}
int result = 0;
string query = "UPDATE ST_ATTENDANCE_DATA SET";
if (Convert.ToInt16(adr.MORNING) == 1)
{
query += " MORNING = '" + 1 + "'";
}
else
{
query += " MORNING = '" + 0 + "'";
}
if (Convert.ToInt16(adr.EVENING) == 1)
{
query += ", EVENING = '" + 1 + "'";
}
else
{
query += ", EVENING = '" + 0 + "'";
}
query += " WHERE STUDENT_ID= '" + adr.STUDENT_ID + "'";
query += " AND ATTENDANCE_DATE= '" + adr.ATTENDANCE_DATE.ToString() + "';";
SqlCommand cmd = new SqlCommand(query, conn);
result = cmd.ExecuteNonQuery();
return(result);
}
public DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable selectAttendanceByDate(DataSet.DsPSMS.ST_ATTENDANCE_DATARow dr)
{
if (dr == null)
{
return(null);
}
string query = "SELECT * FROM ST_ATTENDANCE_DATA ";
string where = "";
if (dr.STUDENT_ID != null)
{
where += " STUDENT_ID = '" + dr.STUDENT_ID + "'";
}
if (dr.ATTENDANCE_DATE != null)
{
if (where.Length > 0)
{
where += " AND ATTENDANCE_DATE= '" + dr.ATTENDANCE_DATE.ToString() + "'";
}
}
if (where.Length > 0)
{
query += " WHERE " + where;
}
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable dt = new DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable();
da.Fill(dt);
return(dt);
}
public int saveAttendanceRecord(DataSet.DsPSMS.ST_ATTENDANCE_DATARow adr, out string msg)
{
int resultDt = 0;
if (adr == null)
{
msg = "data in empty";
return(resultDt);
}
try
{
db.Open();
db.insertAttendanceRecord(adr);
msg = "record complete";
return(resultDt);
}
catch
{
msg = "error has occurred when insert data";
return(resultDt);
}
finally
{
db.Close();
}
}
public DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable getAttendanceByDate(DataSet.DsPSMS.ST_ATTENDANCE_DATARow dr)
{
if (dr == null)
{
return(null);
}
try
{
db.Open();
DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable dt = db.selectAttendanceByDate(dr);
if (dt != null && (dt.Rows.Count > 0))
{
return(dt);
}
else
{
return(null);
}
}
catch
{
return(null);
}
finally
{
db.Close();
}
}
public DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable selectAttendanceList(DataSet.DsPSMS.ST_ATTENDANCE_DATARow dr)
{
DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable result = new DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable();
try
{
db.Open();
result = db.selectAttendanceList(dr);
}
catch
{
return(null);
}
finally
{
db.Close();
}
return(result);
}
private bool checkAldyRecord()
{
bool alreadyExist = false;
for (int i = 0; i < gridViewAttendance.Rows.Count; i++)
{
DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable result = new DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable();
DataSet.DsPSMS.ST_ATTENDANCE_DATARow drr = result.NewST_ATTENDANCE_DATARow();
drr.STUDENT_ID = gridViewAttendance.Rows[i].Cells[0].Text;
drr.ATTENDANCE_DATE = attendDate.Text;
result = attService.getAttendanceByDate(drr);
if (result != null && result.Rows.Count > 0)
{
alreadyExist = true;
}
else
{
alreadyExist = false;
}
}
return(alreadyExist);
}
public DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable selectAttendanceList(DataSet.DsPSMS.ST_ATTENDANCE_DATARow dr)
{
string query = "SELECT * FROM ST_ATTENDANCE_DATA WHERE STUDENT_ID IN (" + dr.STUDENT_ID + ")";
if (dr.ATTENDANCE_DATE.Substring(0, 2) != "00")
{
query += " AND DAY(ATTENDANCE_DATE) LIKE " + dr.ATTENDANCE_DATE.Substring(0, 2);
}
if (dr.ATTENDANCE_DATE.Substring(3, 2) != "00")
{
query += " AND MONTH(ATTENDANCE_DATE) LIKE " + dr.ATTENDANCE_DATE.Substring(3, 2);
}
if (dr.ATTENDANCE_DATE.Substring(6, 4) != "0000")
{
query += " AND YEAR(ATTENDANCE_DATE) LIKE " + dr.ATTENDANCE_DATE.Substring(6, 4);
}
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable dt = new DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable();
da.Fill(dt);
return(dt);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
if (!checkValidation())
{
studentRow = new DataSet.DsPSMS.ST_STUDENT_DATADataTable().NewST_STUDENT_DATARow();
stdResult = new DataSet.DsPSMS.ST_STUDENT_DATADataTable();
studentRow.GRADE_ID = ddlGrade.Text;
studentRow.ROOM_ID = ddlClass.Text;
studentRow.STUDENT_NAME = txtName.Text;
stdResult = attService.selectStudentId(studentRow);
attRow = new DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable().NewST_ATTENDANCE_DATARow();
attResult = new DataSet.DsPSMS.ST_ATTENDANCE_DATADataTable();
if (ddlDay.SelectedIndex != 0)
{
attRow.ATTENDANCE_DATE = ddlDay.Text + "/";
}
else
{
attRow.ATTENDANCE_DATE = "00/";
}
if (ddlMonth.SelectedIndex != 0)
{
attRow.ATTENDANCE_DATE += ddlMonth.Text + "/";
}
else
{
attRow.ATTENDANCE_DATE += "00/";
}
if (ddlYear.SelectedIndex != 0)
{
attRow.ATTENDANCE_DATE += ddlYear.Text;
}
else
{
attRow.ATTENDANCE_DATE += "0000";
}
if (stdResult.Count > 0)
{
if (stdResult.Count == 1)
{
attRow.STUDENT_ID = stdResult.Rows[0]["STUDENT_ID"].ToString();
}
else
{
attRow.STUDENT_ID = "";
for (int i = 0; i < stdResult.Count; i++)
{
attRow.STUDENT_ID += stdResult.Rows[i]["STUDENT_ID"] + ",";
}
attRow.STUDENT_ID = attRow.STUDENT_ID.Substring(0, attRow.STUDENT_ID.Length - 1);
}
attResult = attService.selectAttendanceList(attRow);
}
if (attResult.Count == 0)
{
ModelState.AddModelError(string.Empty, "Data does not exist!");
}
gvAttendanceList.DataSource = attResult;
gvAttendanceList.DataBind();
}
}
public DataSet.DsPSMS.ATTENDANCE_RESULTDataTable selectAttendanceData(DataSet.DsPSMS.ST_STUDENT_DATARow sr, DataSet.DsPSMS.ST_ATTENDANCE_DATARow dr, out string msg)
{
DataSet.DsPSMS.ATTENDANCE_RESULTDataTable result = new DataSet.DsPSMS.ATTENDANCE_RESULTDataTable();
if (dr == null)
{
msg = "data is empty";
return(null);
}
try
{
db.Open();
result = db.selectAttendanceData(sr, dr);
if (result != null)
{
msg = result.Rows.Count + " day found";
}
else
{
result = null;
msg = "attendance not found";
}
}
catch
{
msg = "error has occure when insert data";
return(null);
}
finally
{
db.Close();
}
return(result);
}
