public int setCoursePlanTemplate(setCoursePlan CoursePlanTemplate)
{
int returnValue = 0;
Int64 Column = 0;
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "";
SqlCommand cmd = new SqlCommand();
for (int i = 0; i < CoursePlanTemplate.targetDomain.Count; i++)
{
if (CoursePlanTemplate.targetDomain[i].LongColumn != 0)
{
sql = "UPDATE CoursePlanTemplateLong SET TargetContent=@TargetContent WHERE CPTLID=@CPTLID AND CPTID=@CPTID AND isDeleted=0";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@CPTLID", SqlDbType.BigInt).Value = CoursePlanTemplate.targetDomain[i].LongColumn;
cmd.Parameters.Add("@CPTID", SqlDbType.BigInt).Value = CoursePlanTemplate.targetDomain[i].ISPColumn;
cmd.Parameters.Add("@TargetContent", SqlDbType.NVarChar).Value = CoursePlanTemplate.targetDomain[i].Content;
cmd.ExecuteNonQuery();
returnValue=this.setShortISPCoursePlanTemplate(CoursePlanTemplate.targetDomain[i].ShortTarget);
}
else if (CoursePlanTemplate.targetDomain[i].Content.Length > 0)
{
sql = "INSERT INTO CoursePlanTemplateLong(Domain, TargetContent, CPTID) VALUES (@Domain, @TargetContent, @CPTID)";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Domain", SqlDbType.TinyInt).Value = CoursePlanTemplate.targetDomain[i].Domain;
cmd.Parameters.Add("@CPTID", SqlDbType.BigInt).Value = CoursePlanTemplate.targetDomain[i].ISPColumn;
cmd.Parameters.Add("@TargetContent", SqlDbType.NVarChar).Value = CoursePlanTemplate.targetDomain[i].Content;
returnValue = cmd.ExecuteNonQuery();
if (returnValue != 0)
{
sql = "select IDENT_CURRENT('CoursePlanTemplateLong') AS ISPLong";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Column = Int64.Parse(dr["ISPLong"].ToString());
}
dr.Close();
returnValue = this.createGroupISPShort(CoursePlanTemplate.Column, Column, CoursePlanTemplate.targetDomain[i].ShortTarget);
}
}
}
Sqlconn.Close();
}
catch (Exception e)
{
string item = e.Message;
returnValue = -1;
}
}
return returnValue;
}
public setCoursePlan getCoursePlan(string CPTID)
{
setCoursePlan returnValue = new setCoursePlan();
returnValue.targetDomain = new List<ISPLong>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT CoursePlanTemplate.*,StaffDatabase.StaffName,ClassName.ClassIDName,CourseName.CourseIDName " +
"FROM CoursePlanTemplate INNER JOIN StaffDatabase ON CoursePlanTemplate.TeacherID=StaffDatabase.StaffID " +
"INNER JOIN ClassName ON CoursePlanTemplate.ClassID=ClassName.ClassID "+
"INNER JOIN CourseName ON CoursePlanTemplate.CourseNameID=CourseName.CourseID "+
"WHERE CoursePlanTemplate.isDeleted=0 AND CPTID=@ID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = CPTID;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.Column = Int64.Parse(dr["CPTID"].ToString());
returnValue.unitNum = int.Parse(dr["Unit"].ToString());
returnValue.courseIDName = dr["CourseIDName"].ToString();
returnValue.courseID = int.Parse(dr["CourseNameID"].ToString());
returnValue.classIDName = dr["ClassIDName"].ToString();
returnValue.classID = int.Parse(dr["ClassID"].ToString());
returnValue.teacherIDName = dr["StaffName"].ToString();
returnValue.teacherID = int.Parse(dr["TeacherID"].ToString());
returnValue.startPlanDate=DateTime.Parse(dr["StartPeriod"].ToString());
returnValue.endPlanDate=DateTime.Parse(dr["EndPeriod"].ToString());
returnValue.targetDomain = new List<ISPLong>();
}
dr.Close();
sql = "SELECT CoursePlanTemplateLong.CPTLID, CoursePlanTemplateLong.CPTID, CoursePlanTemplateLong.TargetContent, CoursePlanTemplateLong.Domain " +
"FROM CoursePlanTemplate INNER JOIN CoursePlanTemplateLong ON CoursePlanTemplate.CPTID=CoursePlanTemplateLong.CPTID "+
"WHERE CoursePlanTemplate.isDeleted=0 AND CoursePlanTemplateLong.isDeleted=0 AND CoursePlanTemplate.CPTID=@ID ORDER BY CoursePlanTemplateLong.CPTLID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Int64.Parse(CPTID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ISPLong ItemLong = new ISPLong();
ItemLong.ShortTarget = new List<ISPShort>();
ItemLong.ISPColumn = Int64.Parse(dr["CPTID"].ToString());
ItemLong.LongColumn = Int64.Parse(dr["CPTLID"].ToString());
ItemLong.Domain = int.Parse(dr["Domain"].ToString());
ItemLong.Content = dr["TargetContent"].ToString();
ItemLong.ShortTarget = this.getTeachModelShort(Int64.Parse(CPTID), Int64.Parse(ItemLong.LongColumn.ToString()));
returnValue.targetDomain.Add(ItemLong);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.Column=-1;
returnValue.courseIDName=e.Message.ToString();
}
}
return returnValue;
}
public int setCoursePlanTemplate(setCoursePlan CoursePlanTemplate)
{
TeachDataBase tDB = new TeachDataBase();
return tDB.setCoursePlanTemplate(CoursePlanTemplate);
}
