public List <MENU_OBJ> GetMenu(params spParam[] listFilter)
{
List <MENU_OBJ> lidata = new List <MENU_OBJ>();
string sql = "SELECT * FROM AU_MENU";
string swhere = "";
SqlCommand cm = new SqlCommand();
foreach (var item in listFilter)
{
if (swhere != "")
{
swhere += " OR ";
}
if (item.data == null)
{
//cm.Parameters.Add("@" + f.Name, st);
//cm.Parameters["@" + f.Name].Value = DBNull.Value;
swhere += "[" + item.name + "]" + " is null";
}
else
{
if (item.searchtype == 0)
{
swhere += "[" + item.name + "]= @" + item.name;
cm.Parameters.Add(new SqlParameter("@" + item.name, item.data));
}
else
{
swhere += "[" + item.name + "] LIKE @" + item.name;
cm.Parameters.Add(new SqlParameter("@" + item.name, "%" + item.data + "%"));
}
}
}
if (swhere != "")
{
sql += " WHERE " + swhere;
}
sql += " ORDER BY SORT";
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
lidata = FillToOBJ(ds);
}
return(lidata);
}
public List <TIMETABLE_OBJ> getAll()
{
List <TIMETABLE_OBJ> lidata = new List <TIMETABLE_OBJ>();
string sql = "exec inthoikhoabieu @semester";
int time = 0;
SqlCommand cm = new SqlCommand();
DateTime today = DateTime.Today;
int todayMonth = today.Month;
DateTime setdate = new DateTime(2000, 8, 8);
int setdateMonth = setdate.Month;
if (todayMonth < setdateMonth)
{
time = 1;
}
else
{
time = 2;
}
sql += "=" + time;
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
foreach (DataRow dr in ds.Tables["Tmp"].Rows)
{
TIMETABLE_OBJ obj = new TIMETABLE_OBJ();
Type myTableObject = typeof(TIMETABLE_OBJ);
System.Reflection.PropertyInfo[] selectFieldInfo = myTableObject.GetProperties();
//set object value
foreach (System.Reflection.PropertyInfo info in selectFieldInfo)
{
if (info.Name != "_ID")
{
if (dr.Table.Columns.Contains(info.Name))
{
if (!dr.IsNull(info.Name))
{
info.SetValue(obj, dr[info.Name], null);
}
}
}
}
lidata.Add(obj);
}
}
return(lidata);
}
/// <summary>
/// Lấy danh sách khoa theo tên
/// Lấy toàn bộ danh sách nếu tên rỗng
/// </summary>
/// <returns></returns>
public List <FACULTY_OBJ> GetFacultyList(string name)
{
List <FACULTY_OBJ> lidata = new List <FACULTY_OBJ>();
string sql = "GetFacultyList N'" + name + "'";
SqlCommand cm = new SqlCommand();
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
lidata = FillToOBJ(ds);
}
return(lidata);
}
public List <LVEDUCATION_OBJ> getList()
{
List <LVEDUCATION_OBJ> lidata = new List <LVEDUCATION_OBJ>();
string sql = "SELECT * FROM LEVELEDUCATION";
SqlCommand cm = new SqlCommand();
sql += " ORDER BY CODEVIEW";
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
lidata = FillToOBJ(ds);
}
return(lidata);
}
public List <SPECIALITY_OBJ> searchSpeciality(string dataSearch)
{
List <SPECIALITY_OBJ> lidata = new List <SPECIALITY_OBJ>();
string sql = "SELECT * FROM SPECIALITY WHERE SPECIALITYNAME LIKE '%" + dataSearch + "%' OR CODEVIEW LIKE '%" + dataSearch + "%'";
SqlCommand cm = new SqlCommand();
sql += " ORDER BY CODEVIEW";
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
lidata = FillToOBJ(ds);
}
return(lidata);
}
public List <TBSUBJECT_OBJ> searchSubject(string dataSearch)
{
List <TBSUBJECT_OBJ> lidata = new List <TBSUBJECT_OBJ>();
string sql = "SELECT * FROM TABLESUBJECT WHERE SUBJECTNAME LIKE N'%" + dataSearch + "%' OR CODEVIEW LIKE N'%" + dataSearch + "%' AND PARENTCODE IS NOT NULL ";
SqlCommand cm = new SqlCommand();
sql += " ORDER BY CODEVIEW";
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
lidata = FillToOBJ(ds);
}
return(lidata);
}
public List <TTBTEACHER_OBJ> getAll(params spParam[] listFilter)
{
string sql = "exec tkbgiaovien @name";
SqlCommand cm = new SqlCommand();
List <TTBTEACHER_OBJ> lidata = new List <TTBTEACHER_OBJ>();
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
foreach (DataRow dr in ds.Tables["Tmp"].Rows)
{
TTBTEACHER_OBJ obj = new TTBTEACHER_OBJ();
Type myTableObject = typeof(TTBTEACHER_OBJ);
System.Reflection.PropertyInfo[] selectFieldInfo = myTableObject.GetProperties();
//set object value
foreach (System.Reflection.PropertyInfo info in selectFieldInfo)
{
if (info.Name != "_ID")
{
if (dr.Table.Columns.Contains(info.Name))
{
if (!dr.IsNull(info.Name))
{
info.SetValue(obj, dr[info.Name], null);
}
}
}
}
lidata.Add(obj);
}
}
return(lidata);
}
public USER_OBJ GetForLogin(AccountVM user)
{
USER_OBJ _user = new USER_OBJ();
string sql = @"SELECT * FROM AU_NGUOIDUNG WHERE USERNAME = @username";
SqlCommand com = new SqlCommand();
com.CommandText = sql;
com.CommandType = CommandType.Text;
com.Parameters.Add("@username", SqlDbType.VarChar).Value = user.Username;
SqlDataReader reader = db.getCommand(com);
if (reader != null)
{
var dataTable = new DataTable();
dataTable.Load(reader);
_user = FillToOBJ(dataTable.Rows[0]);
}
return(_user);
}
public List <DEPARTMENT_OBJ> getListByLvCode(string lveducationCode)
{
List <DEPARTMENT_OBJ> lidata = new List <DEPARTMENT_OBJ>();
string sql = "SELECT * FROM DEPARTMENT WHERE LEVELEDUCATIONCODE = " + "'" + lveducationCode + "'";
SqlCommand cm = new SqlCommand();
sql += " ORDER BY CODEVIEW";
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
lidata = FillToOBJ(ds);
}
return(lidata);
}
public List <EDUCATIONLEVEL_OBJ> getAll(params spParam[] listFilter)
{
List <EDUCATIONLEVEL_OBJ> lidata = new List <uni.EDUCATIONLEVEL_OBJ>();
string sql = "SELECT * FROM educationlevel";
string swhere = "";
SqlCommand cm = new SqlCommand();
foreach (var item in listFilter)
{
if (swhere != "")
{
swhere += " AND ";
}
if (item.data == null)
{
//cm.Parameters.Add("@" + f.Name, st);
//cm.Parameters["@" + f.Name].Value = DBNull.Value;
swhere += "[" + item.name + "]" + " is null";
}
else
{
if (item.searchtype == 0)
{
swhere += "[" + item.name + "]= @" + item.name;
cm.Parameters.Add(new SqlParameter("@" + item.name, item.data));
}
else
{
swhere += "[" + item.name + "] LIKE @" + item.name;
cm.Parameters.Add(new SqlParameter("@" + item.name, "%" + item.data + "%"));
}
}
}
if (swhere != "")
{
sql += " WHERE " + swhere;
}
cm.CommandText = sql;
cm.CommandType = CommandType.Text;
DataSet ds = new DataSet();
int ret = db.getCommand(ref ds, "Tmp", cm);
if (ret < 0)
{
return(null);
}
else
{
foreach (DataRow dr in ds.Tables["Tmp"].Rows)
{
EDUCATIONLEVEL_OBJ obj = new EDUCATIONLEVEL_OBJ();
Type myTableObject = typeof(EDUCATIONLEVEL_OBJ);
System.Reflection.PropertyInfo[] selectFieldInfo = myTableObject.GetProperties();
Type myObjectType = typeof(EDUCATIONLEVEL_OBJ.BusinessObjectID);
System.Reflection.PropertyInfo[] fieldInfo = myObjectType.GetProperties();
//set object value
foreach (System.Reflection.PropertyInfo info in selectFieldInfo)
{
if (info.Name != "_ID")
{
if (dr.Table.Columns.Contains(info.Name))
{
if (!dr.IsNull(info.Name))
{
info.SetValue(obj, dr[info.Name], null);
}
}
}
else
{
//set id value
EDUCATIONLEVEL_OBJ.BusinessObjectID objid;
objid = (EDUCATIONLEVEL_OBJ.BusinessObjectID)info.GetValue(obj, null);
foreach (System.Reflection.PropertyInfo info1 in fieldInfo)
{
if (dr.Table.Columns.Contains(info1.Name))
{
info1.SetValue(objid, dr[info1.Name], null);
}
}
info.SetValue(obj, objid, null);
}
}
lidata.Add(obj);
}
}
return(lidata);
}