//TypeCode,0省,1市,2区/县;pAreaCode父节点的AreaCode,查询子节点的条件;sAreaCode要选中的节点AreaCode,可输入或输出,addall 节点是否添加全部 public static string GetSSQ(string TypeCode, string pAreaCode) { DataTable dt = new DataTable(); string rstr = ""; SchSystem.BLL.SysArea bll = new SchSystem.BLL.SysArea(); string sqlstr = "TypeCode='" + TypeCode + "' "; if (TypeCode == "0")//取省份 { sqlstr += " and left(AreaCode,2)='" + pAreaCode.Substring(0, 2) + "'"; } else if (TypeCode == "1")//取城市 { sqlstr += " and left(AreaCode,4)='" + pAreaCode.Substring(0, 4) + "'"; } else if (TypeCode == "2")//取区县 { sqlstr += " and AreaCode='" + pAreaCode + "'"; } dt = bll.GetList("AreaName Name", sqlstr).Tables[0]; if (dt.Rows.Count > 0) { rstr = dt.Rows[0]["Name"].ToString(); } return(rstr); }
public static string GetArea(string AreaCode) { DataTable dt = new DataTable(); string rstr = "省|市|区"; SchSystem.BLL.SysArea bll = new SchSystem.BLL.SysArea(); string sqlstr = "(AreaCode='" + AreaCode.Substring(0, 2) + "0000' and TypeCode=0) or (AreaCode='" + AreaCode.Substring(0, 4) + "00' and TypeCode=1) or (AreaCode='" + AreaCode + "' and TypeCode=2)"; dt = bll.GetList("AreaName,TypeCode", sqlstr).Tables[0]; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["TypeCode"].ToString() == "0") { rstr = rstr.Replace("省", dt.Rows[i]["AreaName"].ToString()); } else if (dt.Rows[i]["TypeCode"].ToString() == "1") { rstr = rstr.Replace("市", dt.Rows[i]["AreaName"].ToString()); } else if (dt.Rows[i]["TypeCode"].ToString() == "2") { rstr = rstr.Replace("区", dt.Rows[i]["AreaName"].ToString()); } } } return(rstr); }
public static string GetDrpAreaStu(string TypeCode, string pAreaCode, ref string sAreaCode, bool addall) { DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); SchSystem.BLL.SysArea bll = new SchSystem.BLL.SysArea(); SchSystem.BLL.SchInfo bllsch = new SchSystem.BLL.SchInfo(); SchSystem.BLL.SchGradeInfo bllgrade = new SchSystem.BLL.SchGradeInfo(); SchSystem.BLL.SchClassInfo bllclass = new SchSystem.BLL.SchClassInfo(); string sqlstr = ""; if (TypeCode == "0")//取省份 { sqlstr = ""; } else if (TypeCode == "1" && pAreaCode != "")//取城市 { if (pAreaCode.Length == 6) { sqlstr = " and left(AreaCode,2)='" + pAreaCode.Substring(0, 2) + "'"; } else { sqlstr = " and AreaCode='" + Com.Public.SqlEncStr(pAreaCode) + "'"; } } else if (TypeCode == "2" && pAreaCode != "")//取区县 { if (pAreaCode.Length == 6) { sqlstr = " and left(AreaCode,4)='" + pAreaCode.Substring(0, 4) + "'"; } else { sqlstr = " and AreaCode='" + Com.Public.SqlEncStr(pAreaCode) + "'"; } } if (addall) { sb.Append("<option value=\"-1\" selected=\"selected\">全部</option>"); } if (TypeCode == "0" || TypeCode == "1" || TypeCode == "2") { //dt = bll.GetList("AreaCode ID,AreaName Name", "Stat=1 and TypeCode='" + TypeCode + "' " + sqlstr + " Order by AreaName").Tables[0]; dt = bll.GetList("AreaCode ID,AreaName Name", "TypeCode='" + Com.Public.SqlEncStr(TypeCode) + "' " + sqlstr + " Order by AreaName").Tables[0]; //获取全部省份 } else if (TypeCode == "3") //学校 { dt = bllsch.GetList("SchId ID,SchName Name", "Stat=1 and AreaNo='" + Com.Public.SqlEncStr(pAreaCode) + "' Order by SchName").Tables[0]; } else if (TypeCode == "4")//年级 { //普通老师 if (Com.Session.systype == "0") { if (Com.Public.IsUserVal(Com.Session.userrolestr, 2))//有查询权限,则全校 { dt = bllgrade.GetList("GradeId ID ,GradeName Name", "IsFinish=0 and SchId='" + Com.Public.SqlEncStr(pAreaCode) + "' Order by GradeCode").Tables[0]; } else { dt = bllgrade.GetList("GradeId ID ,GradeName Name", "IsFinish=0 and SchId='" + Com.Public.SqlEncStr(pAreaCode) + "' and (GradeId in (select GradeId FROM SchGradeUsers where UserName="******") or GradeId in (select GradeId FROM SchClassUserV where UserName="******")) Order by GradeCode").Tables[0]; } } else//学校超管 { dt = bllgrade.GetList("GradeId ID ,GradeName Name", "IsFinish=0 and SchId='" + Com.Public.SqlEncStr(pAreaCode) + "' Order by GradeCode").Tables[0]; } } else if (TypeCode == "5")//班级 { if (pAreaCode == "") { pAreaCode = "0"; } //普通老师 if (Com.Session.systype == "0") { if (Com.Public.IsUserVal(Com.Session.userrolestr, 2))//有查询权限,则全校 { dt = bllclass.GetList("ClassId ID, ClassName Name", "IsFinish=0 and GradeId='" + Com.Public.SqlEncStr(pAreaCode) + "' Order by ClassName").Tables[0]; } else { dt = bllclass.GetList("ClassId ID, ClassName Name", "IsFinish=0 and GradeId='" + Com.Public.SqlEncStr(pAreaCode) + "' and (GradeId in (select GradeId FROM SchGradeUsers where UserName="******") or ClassId in (select ClassId FROM SchClassUserV where UserName="******")) Order by ClassName").Tables[0]; } } else//学校超管 { dt = bllclass.GetList("ClassId ID, ClassName Name", "IsFinish=0 and GradeId='" + Com.Public.SqlEncStr(pAreaCode) + "' Order by ClassName").Tables[0]; } } if (dt.Rows.Count > 0) { if (sAreaCode == "")//输出第一个节点 { sAreaCode = dt.Rows[0]["ID"].ToString(); } for (int i = 0; i < dt.Rows.Count; i++) { if (sAreaCode == "" && i == 0) { sb.Append("<option value=\"" + dt.Rows[i]["ID"].ToString() + "\" selected=\"selected\">" + dt.Rows[i]["Name"].ToString() + "</option>"); } else { // selected="selected" if (dt.Rows[i]["ID"].ToString() == sAreaCode) { sb.Append("<option value=\"" + dt.Rows[i]["ID"].ToString() + "\">" + dt.Rows[i]["Name"].ToString() + "</option>"); } else { sb.Append("<option value=\"" + dt.Rows[i]["ID"].ToString() + "\">" + dt.Rows[i]["Name"].ToString() + "</option>"); } } } } return(sb.ToString()); }
/// <summary> /// /// </summary> /// <param name="TypeCode"></param> /// <param name="pAreaCode"></param> /// <param name="sAreaCode"></param> /// <param name="addall"></param> /// <param name="listoredit">區分是否為列表頁面和編輯頁面(1代表列表頁面;0代表編輯頁面)</param> /// <returns></returns> public static string GetDrpArea(string TypeCode, string pAreaCode, ref string sAreaCode, bool addall, string listoredit = "1") { DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); SchSystem.BLL.SysArea bll = new SchSystem.BLL.SysArea(); SchSystem.BLL.SchInfo bllsch = new SchSystem.BLL.SchInfo(); SchSystem.BLL.SchGradeInfo bllgrade = new SchSystem.BLL.SchGradeInfo(); SchSystem.BLL.SchClassInfo bllclass = new SchSystem.BLL.SchClassInfo(); string sqlstr = ""; if (TypeCode == "0")//取省份 { sqlstr = ""; } else if (TypeCode == "1")//取城市 { if (pAreaCode.Length == 6) { sqlstr = " and left(AreaCode,2)='" + pAreaCode.Substring(0, 2) + "'"; } else { sqlstr = " and AreaCode='" + pAreaCode + "'"; } } else if (TypeCode == "2")//取区县 { if (pAreaCode.Length == 6) { sqlstr = " and left(AreaCode,4)='" + pAreaCode.Substring(0, 4) + "'"; } else { sqlstr = " and AreaCode='" + pAreaCode + "'"; } } if (addall) { sb.Append("<option value=\"\" selected=\"selected\">全部</option>"); } if (TypeCode == "0" || TypeCode == "1" || TypeCode == "2") { //dt = bll.GetList("AreaCode ID,AreaName Name", "Stat=1 and TypeCode='" + TypeCode + "' " + sqlstr + " Order by AreaName").Tables[0]; dt = bll.GetList("AreaCode ID,AreaName Name", "TypeCode='" + TypeCode + "' " + sqlstr + " Order by AreaName").Tables[0]; //获取全部省份 } else if (TypeCode == "3") //学校 { dt = bllsch.GetList("SchId ID,SchName Name", "Stat=1 and AreaNo='" + pAreaCode + "' Order by SchName").Tables[0]; } else if (TypeCode == "4")//年级 { schids = pAreaCode; dt = bllgrade.GetList("GradeId ID ,GradeName Name", "IsFinish=0 and SchId='" + pAreaCode + "' Order by GradeCode").Tables[0]; } else if (TypeCode == "5")//班级 { string strWhere = ""; if (Com.Session.systype != "2") { strWhere += " and schid ='" + Com.Session.schid + "'"; } else { strWhere += " and schid ='" + schids + "'"; } dt = bllclass.GetList("ClassId ID, ClassName Name", "IsFinish=0 and GradeId='" + pAreaCode + "'" + strWhere + " Order by ClassName").Tables[0]; } if (dt.Rows.Count > 0) { if (addall && listoredit == "1") { sAreaCode = ""; } else { if (string.IsNullOrEmpty(sAreaCode)) { sAreaCode = dt.Rows[0]["ID"].ToString(); } } for (int i = 0; i < dt.Rows.Count; i++) { //if (sAreaCode == "" && i == 0) //{ // sb.Append("<option value=\"" + dt.Rows[i]["ID"].ToString() + "\" selected=\"selected\">" + dt.Rows[i]["Name"].ToString() + "</option>"); //} //else //{ // selected="selected" if (dt.Rows[i]["ID"].ToString() == sAreaCode) { sb.Append("<option value=\"" + dt.Rows[i]["ID"].ToString() + "\" selected=\"selected\">" + dt.Rows[i]["Name"].ToString() + "</option>"); } else { sb.Append("<option value=\"" + dt.Rows[i]["ID"].ToString() + "\">" + dt.Rows[i]["Name"].ToString() + "</option>"); } //} } } return(sb.ToString()); }