public Model.BasicRotaryViewModel GetModelByGP_Students_Rotary_id(string GP_Students_Rotary_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from Basic_Rotary_View ");
strSql.Append(" where GP_Students_Rotary_id=@GP_Students_Rotary_id");
SqlParameter[] parameters =
{
new SqlParameter("@GP_Students_Rotary_id", SqlDbType.NVarChar, 50)
};
parameters[0].Value = GP_Students_Rotary_id;
Model.BasicRotaryViewModel model = new Model.BasicRotaryViewModel();
DataSet ds = db.RunDataSet(strSql.ToString(), parameters, "tbName");
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
public DataSet GetListByGP_Students_Rotary_id(string GP_Students_Rotary_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from Basic_Rotary_View ");
strSql.Append(" where GP_Students_Rotary_id=@GP_Students_Rotary_id");
SqlParameter[] parameters =
{
new SqlParameter("@GP_Students_Rotary_id", SqlDbType.NVarChar, 50)
};
parameters[0].Value = GP_Students_Rotary_id;
Model.BasicRotaryViewModel model = new Model.BasicRotaryViewModel();
return(db.RunDataSet(strSql.ToString(), parameters, "tbName"));
}
// #region GetPagedListBasic
// public List<Model.BasicRotaryViewModel> GetPagedListBasic(string training_base_code, string dept_code, string instructor, string instructor_tag, string name, string sex, string minzu, string high_education, string high_school, string identity_type, string send_unit, string collaborative_unit, string training_time, string plan_training_time,
// int pageIndex, int pageSize, out int rowCount, out int pageCount)
// {
// List<Model.BasicRotaryViewModel> list = null;
// if (sex == string.Empty || sex == "") sex = null;
// if (name == string.Empty || name == "") name = null;
// if (minzu == string.Empty || minzu == "") minzu = null;
// if (high_education == string.Empty || high_education == "") high_education = null;
// if (high_school == string.Empty || high_school == "") high_school = null;
// if (identity_type == string.Empty || identity_type == "") identity_type = null;
// if (send_unit == string.Empty || send_unit == "") send_unit = null;
// if (collaborative_unit == string.Empty || collaborative_unit == "") collaborative_unit = null;
// if (training_time == string.Empty || training_time == "") training_time = null;
// if (plan_training_time == string.Empty || plan_training_time == "") plan_training_time = null;
////name, sex, minzu, high_education, high_school, identity_type, send_unit, collaborative_unit, training_time, plan_training_time
// DataTable dt = db.RunPagedDataPro("GetPageList", " id,name,real_name,sex,age,datebirth,minzu,province,city,area,detail_address,id_number,telephon,mail,bk_school,bk_major,graduation_time,high_education,high_school,high_major,high_education_time,identity_type,send_unit,training_base_province_code,training_base_province_name,training_base_code,training_base_name,collaborative_unit,professional_base_name,professional_base_code,training_time,plan_training_time,writor,register_date", "Basic_Rotary_view", "training_base_code='" + training_base_code
// + "' and rotary_dept_code='" + dept_code + "' and instructor='" + instructor + "' and instructor_tag='" + instructor_tag
// + "' and real_name like ISNULL('%"+name+"%',real_name) and sex like ISNULL('%"+sex+"%',sex) and minzu like ISNULL('%"+minzu+"%',minzu)"
// + "and high_education like ISNULL('%" + high_education + "%',high_education) and high_school like ISNULL('%" + high_school + "%',high_school)"
// + "and identity_type like ISNULL('%" + identity_type + "%',identity_type) and send_unit like isnull('%" + send_unit + "%',send_unit)"
// + "and collaborative_unit like isnull('%" + collaborative_unit + "%',collaborative_unit) and training_time like isnull('%" + training_time + "%',training_time) and plan_training_time like isnull('%" + plan_training_time + "%',plan_training_time)"
// , "register_date desc", "id", pageIndex, pageSize, out rowCount, out pageCount);
// //将数据表转为泛型集合
// if (dt.Rows.Count > 0)
// {
// list = new List<BasicRotaryViewModel>();
// Model.BasicRotaryViewModel model = null;//声明实体对象
// foreach (DataRow dr in dt.Rows)
// {
// model = new Model.BasicRotaryViewModel();
// model.id = dr["id"].ToString();
// model.name = dr["name"].ToString();
// model.real_name = dr["real_name"].ToString();
// model.sex = dr["sex"].ToString();
// model.age = dr["age"].ToString();
// model.datebirth = dr["datebirth"].ToString();
// model.minzu = dr["minzu"].ToString();
// model.province = dr["province"].ToString();
// model.city = dr["city"].ToString();
// model.area = dr["area"].ToString();
// model.id_number = dr["id_number"].ToString();
// model.detail_address = dr["detail_address"].ToString();
// model.telephon = dr["telephon"].ToString();
// model.mail = dr["mail"].ToString();
// model.bk_major = dr["bk_major"].ToString();
// model.bk_school = dr["bk_school"].ToString();
// model.graduation_time = dr["graduation_time"].ToString();
// model.high_education = dr["high_education"].ToString();
// model.high_school = dr["high_school"].ToString();
// model.high_major = dr["high_major"].ToString();
// model.high_education_time = dr["high_education_time"].ToString();
// model.identity_type = dr["identity_type"].ToString();
// model.send_unit = dr["send_unit"].ToString();
// model.training_base_province_code = dr["training_base_province_code"].ToString();
// model.training_base_province_name = dr["training_base_province_name"].ToString();
// model.training_base_code = dr["training_base_code"].ToString();
// model.training_base_name = dr["training_base_name"].ToString();
// model.collaborative_unit = dr["collaborative_unit"].ToString();
// model.professional_base_code = dr["professional_base_code"].ToString();
// model.professional_base_name = dr["professional_base_name"].ToString();
// model.training_time = dr["training_time"].ToString();
// model.plan_training_time = dr["plan_training_time"].ToString();
// model.writor = dr["writor"].ToString();
// model.register_date = dr["register_date"].ToString();
// list.Add(model);
// }
// }
// return list;
// }
// #endregion
#region Basic分页
public List <Model.BasicRotaryViewModel> GetPagedListBasic(string training_base_code, string dept_code, string instructor_tag,
string name, string sex, string minzu, string high_education, string high_school, string identity_type, string send_unit, string collaborative_unit, string training_time, string plan_training_time,
int start, int end)
{
string sql = "select id,name,real_name, sex,age,datebirth,minzu,province,city, area,detail_address,id_number,telephon,mail,bk_school,bk_major,graduation_time,high_education, high_school, high_major,high_education_time,identity_type, send_unit,training_base_province_code, training_base_province_name,training_base_code,training_base_name,collaborative_unit,professional_base_name, professional_base_code,training_time, plan_training_time,writor,register_date from (select row_number() over(order by register_date desc) as num,* from Basic_Rotary_view where training_base_code='" + training_base_code + "' and rotary_dept_code='" + dept_code + "' and instructor_tag='" + instructor_tag + "'";
if (!string.IsNullOrEmpty(name))
{
sql += "and name = '" + name + "'";
}
if (!string.IsNullOrEmpty(sex))
{
sql += "and sex = '" + sex + "'";
}
if (!string.IsNullOrEmpty(minzu))
{
sql += "and minzu like '%" + minzu + "%'";
}
if (!string.IsNullOrEmpty(high_education))
{
sql += "and high_education = '" + high_education + "'";
}
if (!string.IsNullOrEmpty(high_school))
{
sql += "and high_school like '%" + high_school + "%'";
}
if (!string.IsNullOrEmpty(identity_type))
{
sql += "and identity_type = '" + identity_type + "'";
}
if (!string.IsNullOrEmpty(send_unit))
{
sql += "and send_unit like '%" + send_unit + "%'";
}
if (!string.IsNullOrEmpty(collaborative_unit))
{
sql += "and collaborative_unit like '%" + collaborative_unit + "%'";
}
if (!string.IsNullOrEmpty(training_time))
{
sql += "and training_time like '%" + training_time + "%'";
}
if (!string.IsNullOrEmpty(plan_training_time))
{
sql += "and plan_training_time like '%" + plan_training_time + "%'";
}
sql += ")as t where t.num>=@start and t.num<=@end";
sql += " group by id,name,real_name, sex,age,datebirth,minzu,province,city, area,detail_address,id_number,telephon,mail,bk_school,bk_major,graduation_time,high_education, high_school, high_major,high_education_time,identity_type, send_unit,training_base_province_code, training_base_province_name,training_base_code,training_base_name,collaborative_unit,professional_base_name, professional_base_code,training_time, plan_training_time,writor,register_date";
SqlParameter[] pars =
{
new SqlParameter("@start", SqlDbType.Int),
new SqlParameter("@end", SqlDbType.Int)
};
pars[0].Value = start;
pars[1].Value = end;
DataTable dt = db.RunDataTable(sql, pars);
List <BasicRotaryViewModel> list = null;
if (dt.Rows.Count > 0)
{
list = new List <BasicRotaryViewModel>();
Model.BasicRotaryViewModel model = null;//声明实体对象
foreach (DataRow dr in dt.Rows)
{
model = new Model.BasicRotaryViewModel();
model.id = dr["id"].ToString();
model.name = dr["name"].ToString();
model.real_name = dr["real_name"].ToString();
model.sex = dr["sex"].ToString();
model.age = dr["age"].ToString();
model.datebirth = dr["datebirth"].ToString();
model.minzu = dr["minzu"].ToString();
model.province = dr["province"].ToString();
model.city = dr["city"].ToString();
model.area = dr["area"].ToString();
model.id_number = dr["id_number"].ToString();
model.detail_address = dr["detail_address"].ToString();
model.telephon = dr["telephon"].ToString();
model.mail = dr["mail"].ToString();
model.bk_major = dr["bk_major"].ToString();
model.bk_school = dr["bk_school"].ToString();
model.graduation_time = dr["graduation_time"].ToString();
model.high_education = dr["high_education"].ToString();
model.high_school = dr["high_school"].ToString();
model.high_major = dr["high_major"].ToString();
model.high_education_time = dr["high_education_time"].ToString();
model.identity_type = dr["identity_type"].ToString();
model.send_unit = dr["send_unit"].ToString();
model.training_base_province_code = dr["training_base_province_code"].ToString();
model.training_base_province_name = dr["training_base_province_name"].ToString();
model.training_base_code = dr["training_base_code"].ToString();
model.training_base_name = dr["training_base_name"].ToString();
model.collaborative_unit = dr["collaborative_unit"].ToString();
model.professional_base_code = dr["professional_base_code"].ToString();
model.professional_base_name = dr["professional_base_name"].ToString();
model.training_time = dr["training_time"].ToString();
model.plan_training_time = dr["plan_training_time"].ToString();
model.writor = dr["writor"].ToString();
model.register_date = dr["register_date"].ToString();
list.Add(model);
}
}
return(list);
}
public List <Model.BasicRotaryViewModel> GetPagedListRotary(string training_base_code, string dept_code, string instructor, string instructor_tag, string name, string sex, string high_education, string identity_type, string send_unit, string collaborative_unit, string training_time, string plan_training_time, string rotary_begin_time, string rotary_end_time, string outdept_status,
int pageIndex, int pageSize, out int rowCount, out int pageCount)
{
List <Model.BasicRotaryViewModel> list = null;
if (sex == string.Empty || sex == "")
{
sex = null;
}
if (name == string.Empty || name == "")
{
name = null;
}
if (high_education == string.Empty || high_education == "")
{
high_education = null;
}
if (identity_type == string.Empty || identity_type == "")
{
identity_type = null;
}
if (send_unit == string.Empty || send_unit == "")
{
send_unit = null;
}
if (collaborative_unit == string.Empty || collaborative_unit == "")
{
collaborative_unit = null;
}
if (training_time == string.Empty || training_time == "")
{
training_time = null;
}
if (plan_training_time == string.Empty || plan_training_time == "")
{
plan_training_time = null;
}
if (rotary_begin_time == string.Empty || rotary_begin_time == "")
{
rotary_begin_time = null;
}
else
{
rotary_begin_time = CommonFunc.SafeGetDateTimeStringFromObjectByFormat(rotary_begin_time, "yyyy-MM");
}
if (rotary_end_time == string.Empty || rotary_end_time == "")
{
rotary_end_time = null;
}
else
{
rotary_end_time = CommonFunc.SafeGetDateTimeStringFromObjectByFormat(rotary_end_time, "yyyy-MM");
}
if (outdept_status == string.Empty || outdept_status == "")
{
outdept_status = null;
}
//+"and rotary_begin_time >= isnull('"+rotary_begin_time+"',rotary_begin_time) and rotary_end_time<=isnull('"+rotary_end_time+"',rotary_end_time)"
DataTable dt = db.RunPagedDataPro("GetPageList", "id,name,real_name,sex,high_education,high_major,rotary_begin_time,rotary_end_time," +
"training_base_code,training_base_name,professional_base_code,professional_base_name,rotary_dept_code,rotary_dept_name," +
"instructor,instructor_tag,GP_Students_Rotary_id,GP_Students_Rotary_writor,GP_Students_Rotary_register_date,outdept_status,outdept_application,questionnaire_status", "Basic_Rotary_view", "training_base_code='" + training_base_code
+ "' and rotary_dept_code='" + dept_code + "' and instructor='" + instructor + "' and instructor_tag='" + instructor_tag
+ "'and real_name like ISNULL('%" + name + "%',real_name) and sex like ISNULL('%" + sex + "%',sex)"
+ "and high_education like ISNULL('%" + high_education + "%',high_education)"
+ "and identity_type like ISNULL('%" + identity_type + "%',identity_type) and send_unit like isnull('%" + send_unit + "%',send_unit)"
+ "and collaborative_unit like isnull('%" + collaborative_unit + "%',collaborative_unit) and training_time like isnull('%" + training_time + "%',training_time) and plan_training_time like isnull('%" + plan_training_time + "%',plan_training_time)"
+ "and rotary_begin_time like isnull('%" + rotary_begin_time + "%',rotary_begin_time) and rotary_end_time like isnull('%" + rotary_end_time + "%',rotary_end_time)"
+ "and outdept_status like isnull('%" + outdept_status + "%',outdept_status)"
, "GP_Students_Rotary_register_date desc", "id", pageIndex, pageSize, out rowCount, out pageCount);
//将数据表转为泛型集合
if (dt.Rows.Count > 0)
{
list = new List <BasicRotaryViewModel>();
Model.BasicRotaryViewModel model = null;//声明实体对象
foreach (DataRow dr in dt.Rows)
{
model = new Model.BasicRotaryViewModel();
model.id = dr["id"].ToString();
model.name = dr["name"].ToString();
model.real_name = dr["real_name"].ToString();
model.sex = dr["sex"].ToString();
model.high_education = dr["high_education"].ToString();
model.high_major = dr["high_major"].ToString();
model.rotary_begin_time = dr["rotary_begin_time"].ToString();
model.rotary_end_time = dr["rotary_end_time"].ToString();
model.training_base_code = dr["training_base_code"].ToString();
model.training_base_name = dr["training_base_name"].ToString();
model.professional_base_code = dr["professional_base_code"].ToString();
model.professional_base_name = dr["professional_base_name"].ToString();
model.GP_Students_Rotary_id = dr["GP_Students_Rotary_id"].ToString();
model.rotary_dept_code = dr["rotary_dept_code"].ToString();
model.rotary_dept_name = dr["rotary_dept_name"].ToString();
model.instructor = dr["instructor"].ToString();
model.instructor_tag = dr["instructor_tag"].ToString();
model.GP_Students_Rotary_writor = dr["GP_Students_Rotary_writor"].ToString();
model.GP_Students_Rotary_register_date = dr["GP_Students_Rotary_register_date"].ToString();
model.outdept_status = dr["outdept_status"].ToString();
model.outdept_application = dr["outdept_application"].ToString();
model.questionnaire_status = dr["questionnaire_status"].ToString();
list.Add(model);
}
}
return(list);
}