/// <summary>
/// 添加课程信息
/// </summary>
/// <param name="course">课程对象</param>
public void AddCourse(Courses course)
{
try
{
string sql = "INSERT INTO usta_Courses(courseNo,courseName,period,credit,courseSpeciality,termTag) VALUES(@CourseNo,@courseName,@period,@credit,@courseSpeciality,@termTag)";
SqlParameter[] parameters = {
new SqlParameter("@CourseNo", SqlDbType.NChar,20),
new SqlParameter("@courseName", SqlDbType.NChar,50),
new SqlParameter("@period", SqlDbType.NChar,50),
new SqlParameter("@credit", SqlDbType.Float,8),
new SqlParameter("@courseSpeciality", SqlDbType.NChar,50),
new SqlParameter("@termTag", SqlDbType.NVarChar,50),
};
parameters[0].Value = course.courseNo;
parameters[1].Value = course.courseName;
parameters[2].Value = course.period;
parameters[3].Value = course.credit;
parameters[4].Value = course.courseSpeciality;
parameters[5].Value = course.termTag;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
protected void AddSalary_Submit(object sender, EventArgs e)
{
DalOperationAboutSalaryEntry dal = new DalOperationAboutSalaryEntry();
DalOperationAboutTeacher dalt = new DalOperationAboutTeacher();
DalOperationAboutCourses dalc = new DalOperationAboutCourses();
string teacherNo = this.TeacherId.Value;
string termTag = this.SalaryTermTag.SelectedValue;
string salaryMonth = this.SalaryMonth.Value;
int teacherType = int.Parse(this.teacherType.Value.Trim());
int salaryEntryStatus = 0;
List<SalaryEntry> salaryEntries = dal.GetSalaryEntrys(teacherNo, termTag, salaryMonth, teacherType, salaryEntryStatus);
if (salaryEntries != null && salaryEntries.Count != 0)
{
Javascript.Alert("本月已为该教师添加过薪酬信息，请核对信息后再次录入!", Page);
}
else {
SalaryEntry salaryEntry = new SalaryEntry();
TeachersList teacher = new TeachersList
{
teacherNo = teacherNo
};
Courses course = new Courses
{
courseNo = this.CourseId.Value
};
salaryEntry.teacher = teacher;
salaryEntry.course = course;
salaryEntry.atCourseType = atCourseType.Value;
salaryEntry.termTag = termTag;
salaryEntry.salaryMonth = salaryMonth;
salaryEntry.teacherCostWithTax = float.Parse(this.TeacherSalaryCostWithTax.Text);
salaryEntry.teacherCostWithoutTax = float.Parse(this.TeacherSalaryCostWithoutTax.Text);
salaryEntry.teacherTotalCost = float.Parse(this.TeacherTotalCost.Text);
salaryEntry.SetSalaryInItemValueList(this.InSalaryItemValueList.Value, false);
salaryEntry.SetSalaryOutItemValueList(this.OutSalaryItemValueList.Value, false);
salaryEntry.memo = this.SalaryEntryMemo.Text.Trim();
salaryEntry.teacherType = teacherType;
dal.AddSalaryEntry(salaryEntry);
Javascript.RefreshParentWindow("/Administrator/SalaryManage.aspx?fragment=5", Page);
}
}
/// <summary>
/// 通过课程号获得课程，同时更新课程关注
/// </summary>
/// <param name="CourseNo">课程编号</param>
/// <param name="StudentNo">学号</param>
/// <returns>课程实体</returns>
public Courses GetCoursesByNo(string CourseNo, string classID, string termtag, string StudentNo)
{
Courses course = null;
string commandstring = "SELECT [courseNo] ,[courseName],[period] ,[credit] ,[courseSpeciality],[preCourse],[refferenceBooks] ,[termTag] ,[attachmentIds] ,[homePage],[courseAnswer] ,[teacherResume] ,[courseIntroduction],[examineMethod] ,[lessonTimeAndAddress],teachingPlan,bbsEmailAddress FROM [USTA].[dbo].[usta_Courses] WHERE courseNo=@courseNo";
SqlParameter[] parameters = new SqlParameter[1]{new SqlParameter("@courseNo",CourseNo)
};
SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text, commandstring, parameters);
if (dr.Read())
{
course = new Courses
{
courseNo = dr["courseNo"].ToString().Trim(),
courseName = dr["courseName"].ToString().Trim(),
period = dr["period"].ToString().Trim(),
credit =(dr["credit"].ToString().Trim().Length>0 ?float.Parse(dr["credit"].ToString().Trim()):0),
courseSpeciality = dr["courseSpeciality"].ToString().Trim(),
preCourse = dr["preCourse"].ToString().Trim(),
referenceBooks = dr["refferenceBooks"].ToString().Trim(),
attachmentIds = dr["attachmentIds"].ToString().Trim(),
termTag = dr["termTag"].ToString().Trim()
};
course.homePage = (dr["homePage"].ToString().Trim().Length == 0) ? "未添加" : dr["homePage"].ToString().Trim();
course.teacherResume = (dr["teacherResume"].ToString().Trim().Length == 0) ? "未添加" : dr["teacherResume"].ToString().Trim();
course.courseAnswer = (dr["courseAnswer"].ToString().Trim().Length == 0) ? "未添加" : dr["courseAnswer"].ToString().Trim();
course.courseIntroduction = (dr["courseIntroduction"].ToString().Trim().Length == 0) ? "未添加" : dr["courseIntroduction"].ToString().Trim();
course.examineMethod = (dr["examineMethod"].ToString().Trim().Length == 0) ? "未添加" : dr["examineMethod"].ToString().Trim();
course.lessonTimeAndAddress = (dr["lessonTimeAndAddress"].ToString().Trim().Length == 0) ? "未添加" : dr["lessonTimeAndAddress"].ToString().Trim();
course.teachingPlan = (dr["teachingPlan"].ToString().Trim().Length == 0) ? "未添加" : dr["teachingPlan"].ToString().Trim();
course.bbsEmaiAddress = (dr["bbsEmailAddress"].ToString().Trim().Length == 0) ? "未添加" : dr["bbsEmailAddress"].ToString().Trim();
}
dr.Close();
conn.Close();
return course;
}
/// <summary>
/// 按照课程编号查询课程
/// </summary>
/// <param name="courseNo">课程编号</param>
/// <returns>课程对象</returns>
public Courses FindCourseByNo(string courseNo,string classID,string termTag)
{
Courses course = null;
string sql = "SELECT courseNo,courseName,period,credit,courseSpeciality,termTag FROM usta_Courses where courseNo=@courseNo AND classID=@classID AND termTag=@termTag";
SqlParameter[] parameters = new SqlParameter[3]{
new SqlParameter("@courseNo",courseNo),
new SqlParameter("@classID",classID),
new SqlParameter("@termTag",termTag)
};
SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, parameters);
while (dr.Read())
{
course = new Courses();
course.courseNo = dr["courseNo"].ToString().Trim();
course.courseName = dr["courseName"].ToString().Trim();
course.period = dr["period"].ToString().Trim();
course.credit = (dr["credit"].ToString().Trim().Length > 0 ? float.Parse(dr["credit"].ToString()) : 0);
course.courseSpeciality = dr["courseSpeciality"].ToString().Trim();
course.termTag = dr["termTag"].ToString();
}
dr.Close();
conn.Close();
return course;
}
/// <summary>
/// 通过课程号得到课程的基本信息 返回一个课程实例
/// </summary>
/// <param name="CourseNo"></param>
/// <returns></returns>
public Courses GetCoursesByNo(String CourseNo, string termTag)
{
Courses course = null;
string commandString = "SELECT [courseNo] ,[courseName],[period] ,[credit] ,[courseSpeciality],[preCourse],[refferenceBooks] ,[termTag] ,[attachmentIds] ,[homePage],[courseAnswer] ,[teacherResume] ,[courseIntroduction],[examineMethod] ,[lessonTimeAndAddress],teachingPlan,bbsEmailAddress, [testHours] FROM [USTA].[dbo].[usta_Courses] WHERE CourseNo=@CourseNo AND termTag = @termTag";
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@CourseNo",CourseNo));
if(string.IsNullOrWhiteSpace(termTag)){
termTag = DalCommon.GetTermTag(conn);
}
else{
termTag = termTag.Trim();
}
parameters.Add(new SqlParameter("@termTag", termTag));
SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text, commandString, parameters.ToArray());
while (dr.Read())
{
course = new Courses
{
courseNo = dr["courseNo"].ToString().Trim(),
courseName = dr["courseName"].ToString().Trim(),
period = dr["period"].ToString().Trim(),
credit = (dr["credit"].ToString().Trim().Length > 0) ? float.Parse(dr["credit"].ToString().Trim()) : 0,
courseSpeciality = dr["courseSpeciality"].ToString().Trim(),
preCourse = dr["preCourse"].ToString().Trim(),
referenceBooks = dr["refferenceBooks"].ToString().Trim(),
attachmentIds = dr["attachmentIds"].ToString().Trim(),
termTag = dr["termTag"].ToString().Trim()
};
course.homePage = (dr["homePage"].ToString().Trim().Length == 0) ? "未添加" : dr["homePage"].ToString().Trim();
course.teacherResume = (dr["teacherResume"].ToString().Trim().Length == 0) ? "未添加" : dr["teacherResume"].ToString().Trim();
course.courseAnswer = (dr["courseAnswer"].ToString().Trim().Length == 0) ? "未添加" : dr["courseAnswer"].ToString().Trim();
course.courseIntroduction = (dr["courseIntroduction"].ToString().Trim().Length == 0) ? "未添加" : dr["courseIntroduction"].ToString().Trim();
course.examineMethod = (dr["examineMethod"].ToString().Trim().Length == 0) ? "未添加" : dr["examineMethod"].ToString().Trim();
course.lessonTimeAndAddress = (dr["lessonTimeAndAddress"].ToString().Trim().Length == 0) ? "未添加" : dr["lessonTimeAndAddress"].ToString().Trim();
course.teachingPlan = (dr["teachingPlan"].ToString().Trim().Length == 0) ? "未添加" : dr["teachingPlan"].ToString().Trim();
course.bbsEmaiAddress = (dr["bbsEmailAddress"].ToString().Trim().Length == 0) ? "未添加" : dr["bbsEmailAddress"].ToString().Trim();
course.TestHours = "0";
if (dr["testHours"] != null)
{
course.TestHours = dr["testHours"].ToString().Trim();
}
}
dr.Close();
conn.Close();
return course;
}
/// <summary>
/// 更新课程信息
/// </summary>
/// <param name="course">课程实体</param>
public void UpdateCourseByCourse(Courses course)
{
try
{
string sql = "UPDATE usta_Courses SET courseName=@courseName, period=@period, credit=@credit, courseSpeciality=@courseSpeciality, termTag=@termTag WHERE courseNo=@CourseNo";
SqlParameter[] parameters = {
new SqlParameter("@CourseNo", SqlDbType.NChar,20),
new SqlParameter("@courseName", SqlDbType.NChar,50),
new SqlParameter("@period", SqlDbType.NChar,50),
new SqlParameter("@credit", SqlDbType.Float,8),
new SqlParameter("@courseSpeciality", SqlDbType.NChar,50),
new SqlParameter("@termTag", SqlDbType.NVarChar,50)
};
parameters[0].Value = course.courseNo;
parameters[1].Value = course.courseName;
parameters[2].Value = course.period;
parameters[3].Value = course.credit;
parameters[4].Value = course.courseSpeciality;
parameters[5].Value = course.termTag;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
/// <summary>
/// 更新某个课程
/// </summary>
/// <param name="courses">课程对象</param>
public void UpdateCourses(Courses courses)
{
try
{
string commandstring = "UPDATE usta_Courses SET preCourse =@PreCourse,refferenceBooks =@RefferenceBooks,attachmentIds = @attachmentIds,[homePage] = @homePage,[examineMethod] = @examineMethod ,[courseAnswer]=@courseAnswer WHERE courseNo=@CourseNo AND classID=@classId AND termTag=@termtag";
SqlParameter[] parameters = {
new SqlParameter("@CourseNo", SqlDbType.NChar,20),
new SqlParameter("@PreCourse", SqlDbType.NVarChar,500),
new SqlParameter("@RefferenceBooks", SqlDbType.NVarChar,1000),
new SqlParameter("@attachmentIds", SqlDbType.NVarChar,200),
new SqlParameter("@homePage", SqlDbType.NVarChar,200),
new SqlParameter("@courseAnswer", SqlDbType.NVarChar,4000),
new SqlParameter("@examineMethod", SqlDbType.NVarChar,4000),
new SqlParameter("@classId", SqlDbType.NVarChar,100),
new SqlParameter("@termtag", SqlDbType.NVarChar,100),
};
parameters[0].Value = courses.courseNo;
parameters[1].Value = courses.preCourse;
parameters[2].Value = courses.referenceBooks;
parameters[3].Value = courses.attachmentIds;
parameters[4].Value = courses.homePage;
parameters[5].Value = courses.courseAnswer;
parameters[6].Value = courses.examineMethod;
parameters[7].Value = courses.classID;
parameters[8].Value = courses.termTag;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandstring, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
/// <summary>
///将Excel表的信息数据保存到数据库
/// </summary>
/// <param name="dt">文件名称</param>
/// <param name="tableType">表类型</param>
/// <returns>删除是否成功</returns>
public int DataTabletoDBTables(DataTable dt,string tableType)
{
if (tableType.Equals("学生信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StudentsList student = new StudentsList();
student.studentNo = dt.Rows[i][1].ToString().Trim();
student.studentName = dt.Rows[i][2].ToString().Trim();
student.studentSpeciality = dt.Rows[i][3].ToString().Trim();
student.classNo = dt.Rows[i][4].ToString().Trim();
student.mobileNo = dt.Rows[i][5].ToString().Trim();
student.emailAddress = dt.Rows[i][6].ToString().Trim();
student.remark = dt.Rows[i][7].ToString().Trim();
student.studentUserPwd = student.studentNo;//密码默认与学号相同
this.AddStudent(student);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("教师信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
TeachersList teacher = new TeachersList();
teacher.teacherNo = dt.Rows[i][1].ToString().Trim();
teacher.teacherName = dt.Rows[i][2].ToString().Trim();
teacher.emailAddress = dt.Rows[i][3].ToString().Trim();
teacher.officeAddress = dt.Rows[i][4].ToString().Trim();
teacher.remark = dt.Rows[i][5].ToString().Trim();
teacher.teacherUserPwd = teacher.teacherNo;//密码默认与教师编号相同
this.AddTeacher(teacher);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("助教信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
AssistantsList assistant = new AssistantsList();
assistant.assistantNo = dt.Rows[i][1].ToString().Trim();
assistant.assistantName = dt.Rows[i][2].ToString().Trim();
assistant.emailAddress = dt.Rows[i][3].ToString().Trim();
assistant.officeAddress = dt.Rows[i][4].ToString().Trim();
assistant.remark = dt.Rows[i][5].ToString().Trim();
assistant.assistantUserPwd = assistant.assistantNo;//密码默认与助教编号相同
this.AddAssistant(assistant);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("课程信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Courses course = new Courses();
course.courseNo = dt.Rows[i][1].ToString().Trim();
course.courseName= dt.Rows[i][2].ToString().Trim();
course.period =dt.Rows[i][3].ToString().Trim();
course.credit=float.Parse( dt.Rows[i][4].ToString().Trim());
course.courseSpeciality= dt.Rows[i][5].ToString();
course.termTag =dt.Rows[i][6].ToString().Trim();//学期标识
new DalOperationAboutCourses().AddCourse(course);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("考试安排"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ExamArrangeList examTime = new ExamArrangeList();
examTime.courseName= dt.Rows[i][1].ToString().Trim();
examTime.examArrangeTime =DateTime.Parse(dt.Rows[i][2].ToString().Trim());
examTime.examArrageAddress = dt.Rows[i][3].ToString().Trim();
examTime.remark =dt.Rows[i][4].ToString().Trim();
examTime.teacherName = dt.Rows[i][5].ToString();
examTime.courseNo= dt.Rows[i][6].ToString().Trim();
this.AddExamArrange(examTime);
}
return dt.Rows.Count;
}
}
return 0;
}
protected void btn_TeacherSalary_Click(object sender, EventArgs e)
{
DalOperationAboutTeacherSalary teacherSalaryDal = new DalOperationAboutTeacherSalary();
if (this.btn_TeacherSalary.Text == "添加")
{
string teacherNo = this.teacherNo.Value;
string termTag = this.TeacherSalary_TermTag.SelectedValue;
int teacherType = int.Parse(this.teacherType.Value.Trim());
List<TeacherSalary> teacherSalaries = teacherSalaryDal.GetTeacherSalarys(teacherNo, teacherType, termTag, 0);
if (teacherSalaries != null && teacherSalaries.Count != 0)
{
Javascript.Alert("本学期已为该教师添加过薪酬预算，请核对信息后再次录入！", Page);
}
else {
TeachersList teacherList = new TeachersList
{
teacherNo = this.teacherNo.Value
};
TeacherSalary salary = new TeacherSalary();
salary.teacher = teacherList;
if (Course_TR.Visible)
{
string atCourseType = this.atCourseType.Value;
Courses course = new Courses
{
courseNo = this.CourseId_hf.Value
};
salary.course = course;
salary.atCourseType = int.Parse(atCourseType);
salary.teachPeriod = int.Parse(this.teachPeriod.Text.Trim());
if (this.experPeriod.Text == null || this.experPeriod.Text.Trim().Length == 0)
{
salary.experPeriod = 0;
}
else
{
salary.experPeriod = int.Parse(this.experPeriod.Text.Trim());
}
}
if (BuildInSalaryItemValueList(salary)) {
salary.teacherType = int.Parse(this.teacherType.Value.Trim());
salary.termTag = TeacherSalary_TermTag.SelectedValue;
salary.memo = teacherSalary_Memo.Text.Trim();
using (TransactionScope scope = new TransactionScope())
{
try
{
teacherSalaryDal.AddTeacherSalary(salary);
AddSalaryEntryDefault(salary);
scope.Complete();
Javascript.RefreshParentWindow("/Administrator/SalaryManage.aspx?fragment=3", Page);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
Javascript.GoHistory(-1, "添加薪酬预算和月发放薪酬记录失败！", Page);
}
}
}
}
}
}
private void DataBindTeacherSalaryCourse(Courses course)
{
this.teachPeriod.Text = course.period;
this.experPeriod.Text = course.TestHours;
}
/// <summary>
/// 课程数据同步
/// </summary>
public void CourseDataSynchronize()
{
List<string> listCourseID = new List<string>();
List<string> listCourseName = new List<string>();
List<string> listNewSchoolYear = new List<string>();
List<string> listClassID = new List<string>();
List<int> listIsDelete = new List<int>();
List<string> listPeriod = new List<string>();
List<string> listTestHours = new List<string>();
List<string> listCredit = new List<string>();
List<string> listCourseIntroduction = new List<string>();
List<string> listDetailLocation = new List<string>();
List<string> listLessonTimeAndAddress = new List<string>();
List<string> listCourseSpeciality = new List<string>();
List<string> listTimeAndRoom = new List<string>();
SqlParameter[] parameters;
//先查基本信息
SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text,
"SELECT [TheoryHours],[TestHours],[Credit],[writer],[ObjectivesAndrequirements],[CoursesInfo],[KeyAndDifficult],"
+ "[TeachingMaterials],[MainContentsAndHoursAllocation],[ClassID],B.[CourseID],B.[CourseName],[DetailLocation],[TimeAndRoom],"
+ "[IsDelete],[NewSchoolYear],[Experimental] "
+ " FROM [Course] A,[CoursePlan] B "
+ " WHERE A.CourseID =B.CourseID AND isDelete=0;"
);
while (dr.Read())
{
listCourseID.Add(dr["CourseID"].ToString().Trim());
listCourseName.Add(dr["CourseName"].ToString().Trim());
listNewSchoolYear.Add(dr["NewSchoolYear"].ToString().Trim());
listClassID.Add(dr["ClassID"].ToString().Trim());
listIsDelete.Add(int.Parse(dr["isDelete"].ToString().Trim()));
listPeriod.Add(dr["TheoryHours"].ToString().Trim());
listTestHours.Add(dr["TestHours"].ToString().Trim());
listCredit.Add(dr["Credit"].ToString().Trim());
listCourseIntroduction.Add(dr["CoursesInfo"].ToString().Trim());
listDetailLocation.Add(dr["DetailLocation"].ToString().Trim());
}
dr.Close();
//再查上课时间
for (int i = 0; i < listCourseID.Count; i++)
{
string _timeAndRoom = string.Empty;
parameters = new SqlParameter[]{
new SqlParameter("@courseName",listCourseName[i]),
new SqlParameter("@termTag",listNewSchoolYear[i]),
new SqlParameter("@classID",listClassID[i])
};
dr = SqlHelper.ExecuteReader(conn, CommandType.Text,
"select [id],[ClassID],[CourseName],[StartStopWeek],[Week],[ClassTime],[Room],[Location],[Year],[ISExperimental]"
+ "from [CourseTimePlan] WHERE ClassID=@classID AND Year=@termTag AND CourseName=@courseName", parameters);
while (dr.Read())
{
_timeAndRoom += (dr["StartStopWeek"].ToString().Trim() + "_" + dr["Week"].ToString().Trim() + "_" + dr["ClassTime"].ToString().Trim() + "_" + dr["Room"].ToString().Trim() + "_" + dr["Location"].ToString().Trim() + "<br />");
}
dr.Close();
listTimeAndRoom.Add(_timeAndRoom);
}
//再查课程属性
for (int i = 0; i < listCourseID.Count; i++)
{
string _courseType = string.Empty;
parameters = new SqlParameter[]{
new SqlParameter("@courseName",listCourseName[i]),
new SqlParameter("@courseID",listCourseID[i])
};
dr = SqlHelper.ExecuteReader(conn, CommandType.Text,
"select [CourseType] from [TrainingPlanCourse] WHERE CourseID=@courseID AND CourseName=@courseName", parameters);
while (dr.Read())
{
_courseType = (dr["CourseType"].ToString().Trim());
}
dr.Close();
listCourseSpeciality.Add(_courseType);
}
conn.Close();
//此处需要自动导入上一学期相同课程的基本信息，以避免重复导入数据
using (TransactionScope scope = new TransactionScope())
{
try
{
for (int i = 0; i < listCourseID.Count; i++)
{
parameters = new SqlParameter[]{
new SqlParameter("@courseNo",listCourseID[i]),
new SqlParameter("@termTag",listNewSchoolYear[i]),
new SqlParameter("@classID",listClassID[i])
};
dr = SqlHelper.ExecuteReader(conn1, CommandType.Text,
"select [ObjectID],[courseNo],[courseName],[termTag] from [usta_Courses] WHERE courseNo=@courseNo AND termTag=@termTag AND classID=@classID", parameters);
bool isHasValue = false;
while (dr.Read())
{
isHasValue = true;
}
dr.Close();
if (!isHasValue)
{
bool isHasLastTermTag = false;
if (this.isHasNewTermtag && !string.IsNullOrEmpty(this.newTermtag))
{
Courses courses = new Courses();
parameters = new SqlParameter[]{
new SqlParameter("@termTag",this.newTermtag)
};
dr = SqlHelper.ExecuteReader(conn1, CommandType.Text,
"select top 1 [courseNo],[courseName],[period],[credit]," +
"[courseSpeciality],[preCourse],[refferenceBooks],[termTag]," +
"[attachmentIds],[homePage],[courseAnswer],[teacherResume],[courseIntroduction]," +
"[examineMethod],[lessonTimeAndAddress],[teachingPlan],[bbsEmailAddress],[ObjectID]," +
"[ClassID],[isDelete] from [usta_Courses] where termTag<@termTag order by termTag desc", parameters);
while (dr.Read())
{
isHasLastTermTag = true;
courses.courseNo = dr["courseNo"].ToString().Trim();
courses.courseName = dr["courseName"].ToString().Trim();
courses.period = dr["period"].ToString().Trim();
courses.credit = float.Parse(dr["credit"].ToString().Trim());
courses.courseSpeciality = dr["courseSpeciality"].ToString().Trim();
courses.preCourse = dr["preCourse"].ToString().Trim();
courses.refferenceBooks = dr["refferenceBooks"].ToString().Trim();
courses.termTag = dr["termTag"].ToString().Trim();
courses.attachmentIds = dr["attachmentIds"].ToString().Trim();
courses.homePage = dr["homePage"].ToString().Trim();
courses.teacherResume = dr["teacherResume"].ToString().Trim();
courses.courseIntroduction = dr["courseIntroduction"].ToString().Trim();
courses.examineMethod = dr["examineMethod"].ToString().Trim();
courses.lessonTimeAndAddress = dr["lessonTimeAndAddress"].ToString().Trim();
courses.teachingPlan = dr["teachingPlan"].ToString().Trim();
courses.bbsEmailAddress = dr["bbsEmailAddress"].ToString().Trim();
courses.objectID = dr["ObjectID"].ToString().Trim();
courses.classID = dr["ClassID"].ToString().Trim();
courses.isDelete = int.Parse(dr["isDelete"].ToString().Trim());
}
dr.Close();
if (isHasLastTermTag)
{
parameters = new SqlParameter[]{
new SqlParameter("@courseNo",listCourseID[i]),
new SqlParameter("@courseName",listCourseName[i]),
new SqlParameter("@period",listPeriod[i]),
new SqlParameter("@credit",listCredit[i]),
new SqlParameter("@courseSpeciality",listCourseSpeciality[i]),
new SqlParameter("@preCourse",courses.preCourse),
new SqlParameter("@refferenceBooks",courses.refferenceBooks),
new SqlParameter("@termTag",listNewSchoolYear[i]),
new SqlParameter("@attachmentIds",courses.attachmentIds),
new SqlParameter("@homePage",courses.homePage),
new SqlParameter("@courseAnswer",courses.courseAnswer),
new SqlParameter("@teacherResume",courses.teacherResume),
new SqlParameter("@courseIntroduction",listCourseIntroduction[i]),
new SqlParameter("@examineMethod",courses.examineMethod),
new SqlParameter("@lessonTimeAndAddress",listTimeAndRoom[i]),
new SqlParameter("@teachingPlan",courses.teachingPlan),
new SqlParameter("@ClassID",listClassID[i]),
new SqlParameter("@isDelete",courses.isDelete)
};
SqlHelper.ExecuteNonQuery(conn1, CommandType.Text,
"IINSERT INTO [usta_Courses] ([courseNo] ,[courseName] ,[period] ,[credit]," +
"[courseSpeciality] ,[preCourse] ,[refferenceBooks] ,[termTag] ,[attachmentIds] ,[homePage] ,[courseAnswer] ,[teacherResume] ," +
"[courseIntroduction] ,[examineMethod] ,[lessonTimeAndAddress] ,[teachingPlan] ," +
"[ClassID] ,[isDelete] ) VALUES (@courseNo ,@courseName,@period,@credit,@courseSpeciality,@preCourse,@refferenceBooks,@termTag,@attachmentIds,@homePage ,@courseAnswer,@teacherResume,@courseIntroduction,@examineMethod,@lessonTimeAndAddress,@teachingPlan,@ClassID,@isDelete);", parameters);
}
//若无上个学期则按正常同步即可
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into usta_Courses(");
strSql.Append("courseNo,courseName,period,credit,courseSpeciality,termTag,courseIntroduction,lessonTimeAndAddress,ClassID,isDelete,TestHours)");
strSql.Append(" values (");
strSql.Append("@courseNo,@courseName,@period,@credit,@courseSpeciality,@termTag,@courseIntroduction,@lessonTimeAndAddress,@ClassID,@isDelete,@TestHours)");
parameters = new SqlParameter[]{
new SqlParameter("@courseName", SqlDbType.NChar,50),
new SqlParameter("@period", SqlDbType.NChar,50),
new SqlParameter("@credit", SqlDbType.Float,8),
new SqlParameter("@courseSpeciality", SqlDbType.NVarChar,2000),
new SqlParameter("@courseIntroduction", SqlDbType.NText),
new SqlParameter("@lessonTimeAndAddress", SqlDbType.NVarChar,2000),
new SqlParameter("@isDelete", SqlDbType.Int,4),
new SqlParameter("@TestHours", SqlDbType.NVarChar,10),
new SqlParameter("@courseNo", SqlDbType.NChar,20),
new SqlParameter("@termTag", SqlDbType.NVarChar,50),
new SqlParameter("@ClassID", SqlDbType.NVarChar,50)};
parameters[0].Value = listCourseName[i];
parameters[1].Value = listPeriod[i];
parameters[2].Value = listCredit[i];
parameters[3].Value = listCourseSpeciality[i];
parameters[4].Value = listCourseIntroduction[i];
parameters[5].Value = listTimeAndRoom[i];
parameters[6].Value = listIsDelete[i];
parameters[7].Value = listTestHours[i];
parameters[8].Value = listCourseID[i];
parameters[9].Value = listNewSchoolYear[i];
parameters[10].Value = listClassID[i];
SqlHelper.ExecuteNonQuery(conn1, CommandType.Text,
strSql.ToString(), parameters);
}
}
//若无新学期标识则按正常同步即可
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into usta_Courses(");
strSql.Append("courseNo,courseName,period,credit,courseSpeciality,termTag,courseIntroduction,lessonTimeAndAddress,ClassID,isDelete,TestHours)");
strSql.Append(" values (");
strSql.Append("@courseNo,@courseName,@period,@credit,@courseSpeciality,@termTag,@courseIntroduction,@lessonTimeAndAddress,@ClassID,@isDelete,@TestHours)");
parameters = new SqlParameter[]{
new SqlParameter("@courseName", SqlDbType.NChar,50),
new SqlParameter("@period", SqlDbType.NChar,50),
new SqlParameter("@credit", SqlDbType.Float,8),
new SqlParameter("@courseSpeciality", SqlDbType.NVarChar,2000),
new SqlParameter("@courseIntroduction", SqlDbType.NText),
new SqlParameter("@lessonTimeAndAddress", SqlDbType.NVarChar,2000),
new SqlParameter("@isDelete", SqlDbType.Int,4),
new SqlParameter("@TestHours", SqlDbType.NVarChar,10),
new SqlParameter("@courseNo", SqlDbType.NChar,20),
new SqlParameter("@termTag", SqlDbType.NVarChar,50),
new SqlParameter("@ClassID", SqlDbType.NVarChar,50)};
parameters[0].Value = listCourseName[i];
parameters[1].Value = listPeriod[i];
parameters[2].Value = listCredit[i];
parameters[3].Value = listCourseSpeciality[i];
parameters[4].Value = listCourseIntroduction[i];
parameters[5].Value = listTimeAndRoom[i];
parameters[6].Value = listIsDelete[i];
parameters[7].Value = listTestHours[i];
parameters[8].Value = listCourseID[i];
parameters[9].Value = listNewSchoolYear[i];
parameters[10].Value = listClassID[i];
SqlHelper.ExecuteNonQuery(conn1, CommandType.Text,
strSql.ToString(), parameters);
}
}
//否则只同步当前学期非教学平台独有的数据项，独有的数据仍然保存
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update usta_Courses set ");
strSql.Append("courseName=@courseName,");
strSql.Append("period=@period,");
strSql.Append("credit=@credit,");
strSql.Append("courseSpeciality=@courseSpeciality,");
//strSql.Append("preCourse=@preCourse,");
//strSql.Append("refferenceBooks=@refferenceBooks,");
//strSql.Append("attachmentIds=@attachmentIds,");
//strSql.Append("homePage=@homePage,");
//strSql.Append("courseAnswer=@courseAnswer,");
//strSql.Append("teacherResume=@teacherResume,");
strSql.Append("courseIntroduction=@courseIntroduction,");
//strSql.Append("examineMethod=@examineMethod,");
strSql.Append("lessonTimeAndAddress=@lessonTimeAndAddress,");
//strSql.Append("teachingPlan=@teachingPlan,");
//strSql.Append("bbsEmailAddress=@bbsEmailAddress,");
strSql.Append("isDelete=@isDelete,");
strSql.Append("TestHours=@TestHours");
strSql.Append(" where courseNo=@courseNo AND termTag=@termTag AND classID=@classID");
parameters = new SqlParameter[]{
new SqlParameter("@courseName", SqlDbType.NChar,50),
new SqlParameter("@period", SqlDbType.NChar,50),
new SqlParameter("@credit", SqlDbType.Float,8),
new SqlParameter("@courseSpeciality", SqlDbType.NVarChar,2000),
//new SqlParameter("@preCourse", SqlDbType.NVarChar,500),
//new SqlParameter("@refferenceBooks", SqlDbType.NVarChar,1000),
//new SqlParameter("@attachmentIds", SqlDbType.NVarChar,200),
//new SqlParameter("@homePage", SqlDbType.NVarChar,200),
//new SqlParameter("@courseAnswer", SqlDbType.NVarChar,4000),
//new SqlParameter("@teacherResume", SqlDbType.NText),
new SqlParameter("@courseIntroduction", SqlDbType.NText),
//new SqlParameter("@examineMethod", SqlDbType.NVarChar,4000),
new SqlParameter("@lessonTimeAndAddress", SqlDbType.NVarChar,2000),
//new SqlParameter("@teachingPlan", SqlDbType.NText),
//new SqlParameter("@bbsEmailAddress", SqlDbType.NVarChar,500),
new SqlParameter("@isDelete", SqlDbType.Int,4),
new SqlParameter("@TestHours", SqlDbType.NVarChar,10),
new SqlParameter("@courseNo", SqlDbType.NChar,20),
new SqlParameter("@termTag", SqlDbType.NVarChar,50),
new SqlParameter("@ClassID", SqlDbType.NVarChar,50)};
parameters[0].Value = listCourseName[i];
parameters[1].Value = listPeriod[i];
parameters[2].Value = listCredit[i];
parameters[3].Value = listCourseSpeciality[i];
parameters[4].Value = listCourseIntroduction[i];
parameters[5].Value = listTimeAndRoom[i];
parameters[6].Value = listIsDelete[i];
parameters[7].Value = listTestHours[i];
parameters[8].Value = listCourseID[i];
parameters[9].Value = listNewSchoolYear[i];
parameters[10].Value = listClassID[i];
SqlHelper.ExecuteNonQuery(conn1, CommandType.Text,
strSql.ToString(), parameters);
}
}
scope.Complete();
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
throw ex;
}
}
conn1.Close();
}
/// <summary>
/// 根据工作薄的名称判断进行对应实体类数据的封装
/// </summary>
/// <param name="sheetName">工作薄名称</param>
/// <param name="list">具体实体类项目的集合</param>
/// <returns>返回封装后的实体类数据</returns>
public static object ReturnModelDataJudgeBySheetName(string sheetName, List<string> list)
{
object modelClassCorrelationSheet = null;
switch (sheetName)
{
case "Sheet1":
modelClassCorrelationSheet =
new TeachersList
{
teacherNo = list[0],
teacherName = list[1],
emailAddress = list[2],
officeAddress = list[3],
remark = list[4],
teacherUserPwd = list[5]
};
break;
case "Sheet2":
modelClassCorrelationSheet = new AssistantsList
{
assistantNo = list[0],
assistantName = list[1],
emailAddress = list[2],
officeAddress = list[3],
remark = list[4],
assistantUserPwd = list[5]
};
break;
case "Sheet3":
modelClassCorrelationSheet = new StudentsList
{
studentNo = list[0],
studentName = list[1],
studentSpeciality = list[2],
classNo = list[3],
mobileNo = list[4],
emailAddress = list[5],
remark = list[6],
studentUserPwd = list[7]
};
break;
case "Sheet4":
modelClassCorrelationSheet = new TermTags
{
termTag = list[0]
};
break;
case "Sheet5":
modelClassCorrelationSheet = new Courses
{
courseNo = list[0],
courseName = list[1],
period = list[2],
credit = float.Parse(list[3]),
courseSpeciality = list[4],
termTag = list[5],
preCourse = string.Empty,
referenceBooks = string.Empty,
attachmentIds = string.Empty
};
break;
case "Sheet6":
modelClassCorrelationSheet = new CoursesTeachersCorrelation
{
teacherNo = list[0],
courseNo = list[1]
};
break;
case "Sheet7":
modelClassCorrelationSheet = new CoursesAssistantsCorrelation
{
assistantNo = list[0],
courseNo = list[1]
};
break;
case "Sheet8":
modelClassCorrelationSheet = new CoursesStudentsCorrelation
{
studentNo = list[0],
courseNo = list[1]
};
break;
default:
break;
}
return modelClassCorrelationSheet;
}