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; }