public static Guid ConvertUnitNameToId(string unitname)
{
if (string.IsNullOrEmpty(unitname))
{
return Guid.Empty;
}
else
{
SqlServerUtility sql = new SqlServerUtility();
DataSet ds = sql.ExecuteSqlDataSet(string.Format("select distinct * from unitinfo where unitname like '%{0}%'", unitname));
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return new Guid(ds.Tables[0].Rows[0][0].ToString());
}
else
{
return Guid.Empty;
}
}
}
/// <summary>
/// ��ѧ��,ѧ��,ͳ�Ƹ�רҵ,�����IJ���������,����ͳ��
/// </summary>
/// <param name="year">ѧ��</param>
/// <param name="term">ѧ��</param>
/// <param name="professcode">רҵ����</param>
/// <param name="gradecode">�꼶����</param>
/// <param name="classcode">�༶����</param>
/// <returns></returns>
public DataSet GetTheMakeUpStdListByParam(string year, int term, string professcode, string gradecode, string classcode)
{
string sqlstr = " SELECT distinct Student.StdName,Student.Code, ClassInfo.ClassCode,ClassInfo.ClassName, "
+ " Score.YearNum,Score.Term,Course.Name,Score.TotalScore "
+ " FROM StdClass INNER JOIN "
+ " ClassInfo ON StdClass.ClassCode = ClassInfo.ClassCode INNER JOIN "
+ " Score ON ClassInfo.ClassCode = Score.ClassCode INNER JOIN "
+ " Course ON Score.CourseCode=Course.Code INNER JOIN "
+ " Student ON Score.StdCode=Student.Code ";
StringBuilder sb = new StringBuilder();
sb.Append(sqlstr);
sb.Append(" where Score.TotalScore<60.0 AND ");
sb.Append(" Score.YearNum='" + year.ToString() + "' AND ");
sb.Append(" Score.Term=" + term.ToString() + " AND ");
sb.Append(" ClassInfo.ProCode='" + professcode + "' AND ");
sb.Append(" ClassInfo.GradeCode='" + gradecode + "' AND ");
sb.Append(" Score.ClassCode='" + classcode + "'");
SqlServerUtility sqlutility = new SqlServerUtility();
DataSet ds = new DataSet();
ds = sqlutility.ExecuteSqlDataSet(sb.ToString());
ds.Tables[0].Columns[0].ColumnName = "����";
ds.Tables[0].Columns[1].ColumnName = "ѧ��";
ds.Tables[0].Columns[2].ColumnName = "�༶����";
ds.Tables[0].Columns[3].ColumnName = "�༶����";
ds.Tables[0].Columns[4].ColumnName = "ѧ��";
ds.Tables[0].Columns[5].ColumnName = "ѧ��";
ds.Tables[0].Columns[6].ColumnName = "�γ�";
ds.Tables[0].Columns[7].ColumnName = "�ɼ�";
return ds;
}
/// <summary>
/// ��ѧ��,ѧ��,ͳ�Ƹ�רҵ,�����IJ���������,����ͳ��
/// </summary>
/// <param name="year">ѧ��</param>
/// <param name="term">ѧ��</param>
/// <param name="professcode">רҵ����</param>
/// <param name="gradecode">�꼶����</param>
/// <returns>DataSet����,�Ѹ�����</returns>
public DataSet GetTheFailsByYearTermProfessAndGrade(string year, int term, string professcode, string gradecode)
{
string sqlstr = "SELECT distinct StaffInfo.Code,StaffInfo.Name,ClassInfo.ClassCode,ClassInfo.ClassName, "
+ " Score.YearNum, Score.Term, count(Distinct(Score.id)) as TotalNum"
+ " FROM StaffInfo INNER JOIN"
+ " TeacherClass ON StaffInfo.Code = TeacherClass.TeacherCode INNER JOIN"
+ " StdClass ON TeacherClass.ClassCode = StdClass.ClassCode INNER JOIN"
+ " ClassInfo ON StdClass.ClassCode = ClassInfo.ClassCode INNER JOIN"
+ " Score ON ClassInfo.ClassCode = Score.ClassCode";
StringBuilder sb = new StringBuilder();
sb.Append(sqlstr);
sb.Append(" where Score.TotalScore<60.0 AND ");
sb.Append(" Score.YearNum='" + year.ToString() + "' AND ");
sb.Append(" Score.Term=" + term.ToString() + " AND ");
sb.Append(" ClassInfo.ProCode='" + professcode + "' AND ");
sb.Append(" ClassInfo.GradeCode='" + gradecode + "' ");
sb.Append("group by StaffInfo.Name, StaffInfo.Code, ClassInfo.ClassName, ClassInfo.ClassCode, Score.YearNum, Score.Term,Score.Id");
SqlServerUtility sqlutility = new SqlServerUtility();
DataSet ds = new DataSet();
ds = sqlutility.ExecuteSqlDataSet(sb.ToString());
ds.Tables[0].Columns[0].ColumnName = "�������";
ds.Tables[0].Columns[1].ColumnName = "����������";
ds.Tables[0].Columns[2].ColumnName = "�༶����";
ds.Tables[0].Columns[3].ColumnName = "�༶����";
ds.Tables[0].Columns[4].ColumnName = "ѧ��";
ds.Tables[0].Columns[5].ColumnName = "ѧ��";
ds.Tables[0].Columns[6].ColumnName = "����������";
return ds;
}
/// <summary>
/// ��ѧ��,��ݷ���ѧ�������б�
/// </summary>
/// <param name="year"></param>
/// <param name="ProSetting"></param>
/// <returns></returns>
public DataSet GetStudentListByProSettingAndYear(int year, int ProSetting, bool IsHigh)
{
StringBuilder sb = new StringBuilder();
sb.Append(Sql_Group_Cs);
CY.GFive.Core.Business.AcademicYear ay = CY.GFive.Core.Business.AcademicYear.GetById(year);
sb.Append(" from [Student],[StudentEx],[Profess] where [Student].[Code]=[StudentEx].[StdCode] And [Student].[ProfessCode]=[Profess].[ProCode] "
+ " AND [Student].[AdmissDate]>= '" + ay.StartTime + "' AND [Student].[AdmissDate] <='" + ay.EndTime + "' AND [Profess].[ProSetting]=" + ProSetting);
if (IsHigh)
sb.Append(" AND ProfessCode in ( select ProCode from Profess where IsHighLevel = 1 )");
else
sb.Append(" AND ProfessCode in ( select ProCode from Profess where IsHighLevel = 0 )");
SqlServerUtility sqlutility = new SqlServerUtility();
DataSet ds = new DataSet();
ds = sqlutility.ExecuteSqlDataSet(sb.ToString());
return RenameDataSet(ds);
}
/// <summary>
/// �����,��ҵ���ַ��ؾ�ҵѧ������ϸ��Ϣ�б�
/// </summary>
/// <param name="year"></param>
/// <param name="enterprisename"></param>
/// <returns></returns>
public DataSet GetStudentListByEnterpriseAndYear(int year, string enterprisename, bool IsHigh)
{
StringBuilder sb = new StringBuilder();
sb.Append(Sql_Group_Cs);
CY.GFive.Core.Business.AcademicYear ay = CY.GFive.Core.Business.AcademicYear.GetById(year);
sb.Append(" from [Student],[StudentEx],[GraduationInfo] where [Student].[Code]=[StudentEx].[StdCode] And [Student].[Code]=[GraduationInfo].[StdCode] "
+ "AND EmployDate >= '" + ay.StartTime + "' AND EmployDate <='" + ay.EndTime + "' AND EntName like '%" + enterprisename + "%' ");
if (IsHigh)
sb.Append(" AND ProfessCode in ( select ProCode from Profess where IsHighLevel = 1 )");
else
sb.Append(" AND ProfessCode in ( select ProCode from Profess where IsHighLevel = 0 )");
SqlServerUtility sqlutility = new SqlServerUtility();
DataSet ds = new DataSet();
ds = sqlutility.ExecuteSqlDataSet(sb.ToString());
return RenameDataSet(ds);
}
//����ѧ�����ݰ�ָ��������ָ�����ͳ��,����ѧ����ϸ�����б�
/// <summary>
/// ����ѧ�����ݰ�ָ��������ָ�����ͳ��,����ѧ����ϸ����
/// </summary>
/// <param name="year"></param>
/// <param name="Area"></param>
/// <returns></returns>
public DataSet GetStudentIntoResidenceNameByYear(int year, string Area, bool IsHigh)
{
CY.GFive.Core.Business.AcademicYear ay = CY.GFive.Core.Business.AcademicYear.GetById(year);
StringBuilder sb = new StringBuilder();
sb.Append(Sql_Group_Cs);
sb.Append(" from [Student],[StudentEx] where [Student].[Code]=[StudentEx].[StdCode] And [Student].[AdmissDate] >= '" + ay.StartTime);
sb.Append("' AND [Student].[AdmissDate] <=' " + ay.EndTime + "' And [ResidenceName] like '%" + Area + "%'");
if (IsHigh)
sb.Append(" AND ProfessCode In ( select ProCode from Profess where IsHighLevel = 1 )");
else
sb.Append(" AND ProfessCode in ( select ProCode from Profess where IsHighLevel = 0 )");
SqlServerUtility sqlutility = new SqlServerUtility();
DataSet ds = new DataSet();
ds = sqlutility.ExecuteSqlDataSet(sb.ToString());
return RenameDataSet(ds);
}
public DataSet GetTeacherCourseList(string Teae)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@TeacherCode", SqlDbType.NVarChar, Teae);
StringBuilder sb = new StringBuilder();
sb.Append(SqlGetTeacherCourseList);
sb.Append("='" + Teae + "'");
DataSet ds = new DataSet();
ds = sql.ExecuteSqlDataSet(sb.ToString());
if (ds == null)
{
return null;
}
return ds;
}