//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());
}
