public string[] setStudentActivityData(createStudentActivity StudentData, List<string> DelParticipantsID, List<string> NewParticipantsValue)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "UPDATE ActivitiesRecords SET actName=@actName, actDate=@actDate, actresponsible1=@actresponsible1, actresponsible2=@actresponsible2, "+
"actresponsible3=@actresponsible3, UpFileBy=@UpFileBy, UpFileDate=getDate() " +
"WHERE actID=@actID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentData.ID);
cmd.Parameters.Add("@actDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.eventDate);
cmd.Parameters.Add("@actName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.eventName);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
for (int i = 1; i <= 3; i++)
{
string staffID = "";
if (StudentData.eventStaffList.Count >= i)
{
staffID = StudentData.eventStaffList[i - 1];
}
cmd.Parameters.Add("@actresponsible" + i, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(staffID);
}
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (DelParticipantsID.Count > 0)
{
for (int i = 0; i < DelParticipantsID.Count; i++)
{
sql = "UPDATE ActivityParticipation SET isDeleted=1, UpFileBy=@UpFileBy, UpFileDate=getDate() " +
"WHERE ID=@ID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(DelParticipantsID[i]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.ExecuteNonQuery().ToString();
}
}
if (NewParticipantsValue.Count > 0)
{
for (int i = 0; i < NewParticipantsValue.Count; i++)
{
string ParticipationID = "";
sql = "SELECT ID FROM ActivityParticipation WHERE isDeleted=1 AND actID=@actID AND StudentID=@StudentID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentData.ID);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewParticipantsValue[i]);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ParticipationID = dr["ID"].ToString();
}
dr.Close();
if (ParticipationID.Length > 0)
{
sql = "UPDATE ActivityParticipation SET isDeleted=0, UpFileBy=@UpFileBy, UpFileDate=getDate() " +
"WHERE ID=@ID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(ParticipationID);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.ExecuteNonQuery().ToString();
}
else
{
sql = "INSERT INTO ActivityParticipation (actID, StudentID, CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@actID, @StudentID, @CreateFileBy, @UpFileBy, getDate())";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentData.ID);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewParticipantsValue[i]);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.ExecuteNonQuery().ToString();
}
}
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] createStudentActivityData(createStudentActivity StudentData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO ActivitiesRecords (Unit, actName, actDate, actresponsible1, actresponsible2, actresponsible3, CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@Unit, @actName, @actDate, @actresponsible1, @actresponsible2, @actresponsible3, @CreateFileBy, @UpFileBy, getDate())";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2].ToString());
cmd.Parameters.Add("@actDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.eventDate);
cmd.Parameters.Add("@actName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.eventName);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
for (int i = 1; i <= 3; i++)
{
string staffID = "";
if (StudentData.eventStaffList.Count >= i)
{
staffID = StudentData.eventStaffList[i - 1];
}
cmd.Parameters.Add("@actresponsible" + i, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(staffID);
}
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (returnValue[0] != "0")
{
Int64 Column = 0;
sql = "select IDENT_CURRENT('ActivitiesRecords') AS cID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Column = Int64.Parse(dr["cID"].ToString());
}
dr.Close();
List<int> itemValue = new List<int>();
for (int i = 0; i < StudentData.Participants.Count; i++)
{
sql = "INSERT INTO ActivityParticipation (actID, StudentID, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " +
"VALUES (@actID, @StudentID, @CreateFileBy, getDate(), @UpFileBy, getDate())";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actID", SqlDbType.Int).Value = Column;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.Participants[i][1]);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
itemValue.Add(cmd.ExecuteNonQuery());
}
returnValue[0] = Column.ToString();
returnValue[1] = Convert.ToInt32(!itemValue.Contains(0)).ToString();
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public createStudentActivity getStudentActivityData(string ID)
{
createStudentActivity returnValue = new createStudentActivity();
returnValue.eventStaffList = new List<string>();
returnValue.Participants = new List<string[]>();
StaffDataBase sDB = new StaffDataBase();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM ActivitiesRecords WHERE isDeleted=0 AND actID=@actID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["actID"].ToString();
returnValue.caseUnit = dr["Unit"].ToString();
returnValue.eventName = dr["actName"].ToString();
returnValue.eventDate = DateTime.Parse(dr["actDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.eventStaffList.Add(dr["actresponsible1"].ToString());
returnValue.eventStaffList.Add(dr["actresponsible2"].ToString());
returnValue.eventStaffList.Add(dr["actresponsible3"].ToString());
List<string> CreateFileName = sDB.getStaffDataName(dr["CreateFileBy"].ToString());
returnValue.creatFileName = CreateFileName[1];
}
dr.Close();
sql = "SELECT a.ID,a.StudentID,b.StudentName FROM ActivityParticipation a left join StudentDatabase b on a.StudentID=b.StudentID " +
"WHERE a.isDeleted=0 AND actID=@actID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string[] addValue = new string[3];
addValue[0] = dr["ID"].ToString();
addValue[1] = dr["StudentID"].ToString();
addValue[2] = dr["StudentName"].ToString();
returnValue.Participants.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
return returnValue;
}
public string[] createStudentActivityData(createStudentActivity StudentData)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[2]) == 1)
{
return SDB.createStudentActivityData(StudentData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public string[] setStudentActivityData(createStudentActivity StudentData, List<string> DelParticipantsID, List<string> NewParticipantsValue)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[1]) == 1)
{
return SDB.setStudentActivityData(StudentData, DelParticipantsID, NewParticipantsValue);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public createStudentActivity getStudentActivityData(string ID)
{
CaseDataBase SDB = new CaseDataBase();
createStudentActivity returnValue = new createStudentActivity();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
returnValue=SDB.getStudentActivityData(ID);
}
else
{
returnValue.checkNo = _noRole;
returnValue.errorMsg = _errorMsg;
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (returnValue.caseUnit != UserFile[2] && int.Parse(SDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}
