private static DataTable GetStudentsByClass(OnlineResultModel onlineResult)
{
SqlParameter[] param =
{
new SqlParameter("@ClassID", onlineResult.ClassIDFK)
};
return(SqlHelper.ExecuteDataset(SqlHelper.Connect, CommandType.Text, "Select * from studentinfo where Classid=@Classid", param).Tables[0]);
}
public static bool CheckifRecordAlreadyInserted(OnlineResultModel onlineResult)
{
SqlParameter[] param =
{
new SqlParameter("@PublishDate", onlineResult.PublishDate),
new SqlParameter("@SIDFK", onlineResult.SIDFK),
new SqlParameter("@SeriesIDFK", Convert.ToInt64(onlineResult.SeriesIDFK))
};
return((int)SqlHelper.ExecuteScalar(SqlHelper.Connect, CommandType.Text, "select count(*) from OnlineResult where SIDFK=@SIDFK and PublishDate=@PublishDate and SeriesIDFK=@SeriesIDFK", param) > 0 ? true : false);
}
public static bool CheckifAnySeriesIsPublishedAlreadyOnDate(OnlineResultModel onlineResult)
{
SqlParameter[] param =
{
new SqlParameter("@PublishDate", onlineResult.PublishDate),
new SqlParameter("@ClassIDFK", Convert.ToInt64(onlineResult.ClassIDFK)),
new SqlParameter("@SubjectIDFK", Convert.ToInt64(onlineResult.SubjectIDFK))
};
return((int)SqlHelper.ExecuteScalar(SqlHelper.Connect, CommandType.Text, "select count(*) from QuestionSeries where ClassIDFK=@ClassIDFK and PublishDate=@PublishDate and IsPublished=1 and SubjectIDFK=@SubjectIDFK", param) > 0 ? true : false);
}
//publish series
public static int PublishSeries(OnlineResultModel onlineResult)
{
DataTable dtStd = GetStudentsByClass(onlineResult);
String[] Series = onlineResult.SeriesArray.Split(',');
int totalStudents = dtStd.Rows.Count;
int RecordsUpdated = 0;
if (totalStudents > 0)
{
foreach (DataRow drS in dtStd.Rows)
{
String SeriesIDFK = GetRandomSeries(Series);
SqlParameter[] param =
{
new SqlParameter("@SeriesIDFK", SeriesIDFK),
new SqlParameter("@SIDFK", drS["StudentID"].ToString()),
new SqlParameter("@PublishDate", onlineResult.PublishDate),
new SqlParameter("@TimeLeft", onlineResult.TimeDuration)
};
//check if already exists
OnlineResultModel olr = new OnlineResultModel();
olr.SIDFK = drS["StudentID"].ToString();
olr.PublishDate = onlineResult.PublishDate;
olr.SeriesIDFK = SeriesIDFK;
if (!CheckifRecordAlreadyInserted(olr))
{
if (SqlHelper.ExecuteNonQuery(SqlHelper.Connect, CommandType.Text, "Insert into OnlineResult (SIDFK,SeriesIDFK,PublishDate,TimeLeft) values(@SIDFK,@SeriesIDFK,@PublishDate,@TimeLeft)", param) > 0)
{
RecordsUpdated++;
}
}
}
if (RecordsUpdated > 0)
{
foreach (String serID in Series)
{
SqlParameter[] paramS =
{
new SqlParameter("@PublishDate", onlineResult.PublishDate),
new SqlParameter("@SeriesID", serID),
new SqlParameter("@TimeDuration", onlineResult.TimeDuration)
};
SqlHelper.ExecuteNonQuery(SqlHelper.Connect, CommandType.Text, "update QuestionSeries set PublishDate=@PublishDate,TimeDuration=@TimeDuration,IsPublished=1 where SID=@SeriesID", paramS);
}
}
}
return(RecordsUpdated);
}
