StringBuilder strSql = new StringBuilder();
StringBuilder strsql2005 = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
sqlcount.Append("select count(1) ");
sqlwhere.Append(" SELECT C.XMMC AS XBMC,A.SFZH,A.LXDH,H.LXRDH,H.YLJGBH, ");//需要查询的列
if (strLX == "4" || strLX == "5")
{
sqlwhere.Append(" H.HXDABH,H.JTCYBH,H.DCZ,H.DCRQ,H.CJRQ,H.CJRBH,H.CJR,A.MZBH, YLJGMC,A.GRDABH,A.XM,A.CSNYR,A.JMJKDAH,A.DAZT,H.XXDZ,A.CZLX,Z.XMMC+'-'+W.XMMC AS CZMC,H.SFHG ,X.QZSJ,AZ.JBMC,H.JKJDZT ");//,T.ZZDAH ");//需要查询的列 X.QZSJ,AZ.JBMC
}
else
{
sqlwhere.Append(" H.HXDABH,H.JTCYBH,H.DCZ,H.DCRQ,H.CJRQ,H.CJRBH,H.CJR,A.MZBH, YLJGMC,A.GRDABH,A.XM,A.CSNYR,A.JMJKDAH,A.DAZT,H.XXDZ,A.CZLX,Z.XMMC+'-'+W.XMMC AS CZMC,H.SFHG,A.CSNYR as QZSJ,A.XM as JBMC,H.JKJDZT ");//,T.ZZDAH ");
}
sqlwhere.Append(" FROM JMDA_HXDA_JBQK H with(nolock) JOIN JMDA_JTCY A with(nolock) ON A.JTCYBH = H.JTCYBH ");
sqlwhere.Append(" INNER JOIN SKJBXX_SJZDMX C with(nolock) ON C.FLBH = 'S101-01' AND A.XB = C.XMBH ");
sqlwhere.Append(" INNER JOIN SKJBXX_YLJG JG with(nolock) ON JG.YLJGBH=H.YLJGBH ");//社康基本信息_医疗机构表
sqlwhere.Append(" LEFT JOIN SKJBXX_SJZDMX Z with(nolock) ON Z.FLBH = 'JTCY_CZLX' AND A.CZLX =Z.XMBH ");
sqlwhere.Append("LEFT JOIN SKJBXX_SJZDMX W with(nolock) ON W.FLBH='JTCY_CZHK' AND A.CZHK=W.XMBH");
if (strLX == "3")
{
sqlwhere.Append(" LEFT join FNWCQBJ_JBQK F with(nolock) ON A.JTCYBH = F.JMJKDAH And F.DQZT='0' ");
}
else if (strLX == "4" || strLX == "5")
{
sqlwhere.Append(" LEFT JOIN MBGL_JBQKB M with(nolock) ON A.JTCYBH = M.JTCYBH AND M.DAZT='1' ");
sqlwhere.Append(" LEFT JOIN MBGL_JWS X with(nolock) ON M.MBDAH=X.MBDAH ");
sqlwhere.Append("LEFT JOIN SKJBXX_JBXXB AS AZ with(nolock) ON X.JBBH=AZ.JBBH ");
}
else if (strLX == "6")
{
sqlwhere.Append(" LEFT JOIN JSB_JBQK J with(nolock) ON A.JTCYBH = J.JTCYBH AND J.DAZT='1' ");
}
sqlwhere.Append(" WHERE 1=1 ");//档案状态
if (SFZH != "")
{
sqlwhere.Append(" AND A.SFZH = @SFZH ");
}
else
{
if (strJMJKDAH != "")//当个人档案编号不为空时，只根据这个来查询。
{
sqlwhere.Append(" AND A.GRDABH=@GRDABH ");
}
else
{
if (lag == "0")
{
sqlwhere.Append(" AND A.YLJGID = @YLJGBH ");
if (YHBH != "")
{
sqlwhere.Append(" AND H.CJRBH = @CJRBH ");
}
}
else if (lag == "1")
{
sqlwhere.Append(" AND A.YLJGID LIKE @YLJGBH ");
}
else if (lag == "2")
{
}
if (strXM != "")
{
sqlwhere.Append(" AND A.XM like @XM ");
}
if (BeginDate != Convert.ToDateTime("9999-12-31") && EndDate != Convert.ToDateTime("9999-12-31"))
{
sqlwhere.Append(" AND H.DCRQ >= @FCJRQ AND H.DCRQ <= @TCJRQ");
}
if (CSRQ != Convert.ToDateTime("9999-12-31"))
{
sqlwhere.Append(" AND A.CSNYR >= @CSNYR ");
}
if (CSRQE != Convert.ToDateTime("9999-12-31"))
{
sqlwhere.Append(" AND A.CSNYR <= @CSNYRE ");
}
if (strXb != "")
{
sqlwhere.Append(" AND A.XB = @XB ");
}
if (strHJDZ != null && strHJDZ != "")
{
sqlwhere.Append(" AND H.XXDZ like @HJDZ ");
}
if (strXZQ != "")
{
sqlwhere.Append(" AND A.XZQBH like @XZQ");
}
if (strGZDW != "")
{
sqlwhere.Append(" and H.GZDW = @strGZDW");
}
if (CZLX != "0" && CZLX != "")
{
sqlwhere.Append(" and A.CZLX = @CZLX");
}
if (CZHK != "0" && CZHK!="")
{
if (CZHK == "1")
sqlwhere.Append(" and A.CZHK='1'");
else
sqlwhere.Append(" and A.CZHK='0'");
}
}
sqlwhere.Append(" AND A.DAZT = 1 ");
switch (strLX)
{
case "1":
sqlwhere.Append(" and datediff(Year,A.CSNYR ,getdate())>=0 and datediff(Year,A.CSNYR ,getdate())<=6 "); //0-6岁儿童档案查询 条件
break;
case "2":
sqlwhere.Append(" and datediff(Year,A.CSNYR ,getdate())>=65 ");//65岁以上老年人档案查询 条件
break;
case "3":
sqlwhere.Append(" and F.BJSCBH != '' ");//孕产妇查询条件
break;
case "4":
sqlwhere.Append(" and X.JBBH='1' "); //慢病高血压 查询条件
break;
case "5":
sqlwhere.Append(" and X.JBBH='2' ");//慢病糖尿病查询条件
break;
case "6":
sqlwhere.Append(" and J.JSBDABH!='' ");//重性精神病查询条件
break;
default:
break;
}
}
sqlwhere.Append(" AND A.DAZT = 1 ");
strSql.Append(sqlwhere.ToString());
if (strPX == "1")
{
strSql.Append(" ORDER BY H.CJRQ DESC,A.XM asc ");
}
else if (strPX == "2")
{
strSql.Append(" ORDER BY A.GRDABH DESC ");
}
else if (strPX == "3")
{
strSql.Append(" ORDER BY csnyr asc ");
}
SqlParameter[] parameters = {
new SqlParameter("@GRDABH", SqlDbType.VarChar,20), //居民健康档案号
new SqlParameter("@YLJGBH", SqlDbType.VarChar,12), //医疗机构号
new SqlParameter("@XM", SqlDbType.VarChar,40),//姓名
new SqlParameter("@CSNYR", SqlDbType.DateTime),//出生日期
new SqlParameter("@CSNYRE", SqlDbType.DateTime),//出生日期搜索结束时间
new SqlParameter("@FCJRQ", SqlDbType.DateTime),//起始日期
new SqlParameter("@TCJRQ", SqlDbType.DateTime),//结束日期
new SqlParameter("@CJRBH", SqlDbType.VarChar,20),//用户编号
new SqlParameter("@SFZH", SqlDbType.VarChar, 20), //身份证号
new SqlParameter("@HJDZ", SqlDbType.VarChar,100),//详细地址
new SqlParameter("@XZQ",SqlDbType.VarChar,10),//行政区
new SqlParameter("@strGZDW",SqlDbType.VarChar,60), //工作单位
new SqlParameter("@XB",SqlDbType.VarChar,10),//性别
new SqlParameter("@CZLX",SqlDbType.Char,1), //户籍类型
new SqlParameter("@PageIndex",SqlDbType.Int,4),
new SqlParameter("@PageSize",SqlDbType.Int ,4)
};
parameters[0].Value = strJMJKDAH; //居民健康档案号
if (lag == "1")
{
parameters[1].Value = strYLJGBH + "%";//医疗机构号
}
else
{
parameters[1].Value = strYLJGBH;//医疗机构号
}
parameters[2].Value = strXM + "%"; //姓名
parameters[3].Value = CSRQ;//出生日期
parameters[4].Value = CSRQE;//出生日期搜索结束时间
parameters[5].Value = BeginDate; //起始日期
parameters[6].Value = EndDate;//结束日期
parameters[7].Value = YHBH;//用户编号
parameters[8].Value = SFZH;//身份证号码
parameters[9].Value = "%" + strHJDZ + "%";//详细地址
parameters[10].Value = strXZQ + "%";
parameters[11].Value = strGZDW; //工作单位
parameters[12].Value = strXb;
parameters[13].Value = CZLX; //户籍类型
parameters[14].Value = PageIndex;
parameters[15].Value = PageSize;
strsql2005.Append(sqlwhere.ToString());
sqlcount.Append(sqlwhere.ToString());
object intcount = DbHelperSQL.GetSingle(sqlcount.ToString(), parameters);
if (intcount == null)
RecordCount = 0;
else
RecordCount = int.Parse(intcount.ToString());
return DbHelperSQL.Query(strSql.ToString(), PageSize, PageIndex, parameters);
