<%@ WebHandler Language="C#" Class="FaultDiagnoseInfo" %>
using System;
using System.Web;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
public class FaultDiagnoseInfo : IHttpHandler {
public static string SqlconnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BasicDataConnInfo"].ConnectionString;
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
string StrResult = string.Empty;
if (!string.IsNullOrEmpty(context.Request["OperationType"]))
{
string OperationType = context.Request["OperationType"].ToString();
string KeyWord =FilterInputData( context.Request["KeyWord"]);
string DocNo = FilterInputData(context.Request["DocNo"]);
string From = context.Request["From"];
switch (OperationType)
{
case "GetCount":
StrResult = GetCount(KeyWord, DocNo,From);
break;
case "InitData":
StrResult = InitData(context, KeyWord, DocNo,From);
break;
default:
break;
}
}
context.Response.Write(StrResult);
}
/// <summary>
/// 获取记录总条数
/// </summary>
private string GetCount(string KeyWord, string DocNo,string From)
{
StringBuilder sbsql = new StringBuilder();
sbsql.Append("select count(0) from Tbl_Wsi_FaultDiagnoseInfo where 1=1 ");
//按关键字查询（标题）
if(!string.IsNullOrEmpty(KeyWord))
{
sbsql.Append(" and DiagnoseTitle like '%"+KeyWord+"%'");
}
//按文档编号查询
if(!string.IsNullOrEmpty(DocNo))
{
sbsql.Append(" and DiagnoseCode like '%" + DocNo + "%'");
}
if (From == "Idea")
{
sbsql.Append(" and ScopeFlag in (0,1) and ApploveStatus=2 ");
}
if (From == "Think")
{
sbsql.Append(" and ScopeFlag in (0,2) and ApploveStatus=2 ");
}
string rowCount = SqlHelper.ExecuteScalar(SqlconnectionString, CommandType.Text, sbsql.ToString()).ToString();
return "{\"total\":" + rowCount + "}";
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <returns></returns>
public string InitData(HttpContext context, string KeyWord, string DocNo,string From)
{
//具体的页面
int pageIndex;
if (!int.TryParse(context.Request["pageIndex"], out pageIndex))
{
pageIndex = 1;
}
//页面显示条数
int PageSize = Convert.ToInt32(context.Request["pageSize"]);
string whereby = "";
//按关键字查询（标题）
if (!string.IsNullOrEmpty(KeyWord))
{
whereby = " and DiagnoseTitle like '%" + KeyWord + "%'";
}
//按文档编号查询
if (!string.IsNullOrEmpty(DocNo))
{
whereby = " and DiagnoseCode like '%" + DocNo + "%'";
}
if (From == "Idea")
{
whereby += " and ScopeFlag in (0,1) and ApploveStatus=2 ";
}
if (From == "Think")
{
whereby += " and ScopeFlag in (0,2) and ApploveStatus=2 ";
}
int count;
DataTable dt = GetAllData(PageSize, pageIndex, whereby, out count);
return DataTable2Json(dt, count);
}
/// <summary>
/// dataTable转换成Json格式
/// </summary>
/// <param name="dt">dataTable</param>
/// <returns>Json</returns>
public string DataTable2Json(System.Data.DataTable dt, int Total)
{
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"total\":" + Total.ToString() + ",\"rows");
jsonBuilder.Append("\":[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append("\":\"");
jsonBuilder.Append(JsonCharFilter(dt.Rows[i][j].ToString()));
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return jsonBuilder.ToString();
}
/// <summary>
/// 过滤Json字符串
/// </summary>
public static string JsonCharFilter(string sourceStr)
{
sourceStr = sourceStr.Replace("\\", "\\\\");
sourceStr = sourceStr.Replace("\"", "“");
sourceStr = sourceStr.Replace("\\", "/");
sourceStr = sourceStr.Replace("\b", "\\\b");
sourceStr = sourceStr.Replace("\t", "\\\t");
sourceStr = sourceStr.Replace("\n", "\\\n");
sourceStr = sourceStr.Replace("\n", "\\\n");
sourceStr = sourceStr.Replace("\f", "\\\f");
sourceStr = sourceStr.Replace("\r", "\\\r");
return sourceStr.Replace("\"", "\\\"");
}
public bool IsReusable {
get {
return false;
}
}
/// <summary>
/// 获得所有的故障诊断信息
/// </summary>
/// <param name="PageSize"></param>
/// <param name="PageNo"></param>
/// <param name="where"></param>
/// <param name="RecordCount"></param>
/// <param name="typeid"></param>
/// <returns></returns>
public DataTable GetAllData(int PageSize, int PageNo, string whereby, out int RecordCount)
{
string sql_Count = @" SELECT count(0) FROM Tbl_Wsi_FaultDiagnoseInfo a INNER JOIN
Tbl_Wsi_FaultDiagnoseSortInfo b ON a.DiagnoseType=b.EditId WHERE 1=1 ";
string sql = @" a.EditId,a.DiagnoseTitle,a.DiagnoseCode,a.DiagnoseType,CONVERT(varchar(100), a.UpdateTime, 23) AS UpdateTime,b.DiagnoseType AS DiagnoseTypeName
FROM Tbl_Wsi_FaultDiagnoseInfo a INNER JOIN Tbl_Wsi_FaultDiagnoseSortInfo b ON a.DiagnoseType=b.EditId WHERE 1=1 ";
if (whereby != string.Empty)
{
sql += whereby;
sql_Count += whereby;
}
///返回条数
RecordCount = int.Parse(SqlHelper.ExecuteScalar(SqlconnectionString, CommandType.Text, sql_Count).ToString());
//////////////得到datatable
SqlParameter[] arParams ={
new SqlParameter("@strSQL",SqlDbType.NVarChar,4000,ParameterDirection.Input,true,0,0,null,DataRowVersion.Current,sql),
new SqlParameter("@strOrder",SqlDbType.NVarChar,100,ParameterDirection.Input,true,0,0,null,DataRowVersion.Current,"a.UpdateTime desc"),
new SqlParameter("@PageSize",SqlDbType.Int,8,ParameterDirection.Input,true,0,0,null,DataRowVersion.Current,PageSize),
new SqlParameter("@PageIndex",SqlDbType.Int,8,ParameterDirection.Input,true,0,0,null,DataRowVersion.Current,PageNo)
};
return SqlHelper.ExecuteDataset(SqlconnectionString, CommandType.StoredProcedure, "sp_Wsi_Paging", arParams).Tables[0];
}
/// <summary>
/// 过滤用户输入的非法字符串
/// </summary>
/// <param name="strInputData">待过滤字符串</param>
/// <returns>过滤后的字符串</returns>
public string FilterInputData(string strInputData)
{
if (strInputData == null)
{
return string.Empty;
}
else
{
//删除前台脚本
strInputData = Regex.Replace(strInputData, @"<script[^>]*?>.*?</script>", "", RegexOptions.IgnoreCase);
//删除HTML
strInputData = Regex.Replace(strInputData, @"<(.[^>]*)>", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"([\r\n])[\s]+", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"-->", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"<!--.*", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(quot|#34);", "\"", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(amp|#38);", "&", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(lt|#60);", "<", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(gt|#62);", ">", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(nbsp|#160);", " ", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(iexcl|#161);", "\xa1", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(cent|#162);", "\xa2", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(pound|#163);", "\xa3", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&(copy|#169);", "\xa9", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, @"&#(\d+);", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "xp_cmdshell", "", RegexOptions.IgnoreCase);
//删除与数据库相关的词
strInputData = Regex.Replace(strInputData, "select", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "insert", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "delete from", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "count''", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "drop table", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "truncate", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "asc", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "mid", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "char", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "xp_cmdshell", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "exec master", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "net localgroup administrators", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "and", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "net user", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "or", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "net", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "-", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "delete", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "drop", "", RegexOptions.IgnoreCase);
strInputData = Regex.Replace(strInputData, "script", "", RegexOptions.IgnoreCase);
// 删除特殊字符
strInputData = strInputData.Replace("<", "");
strInputData = strInputData.Replace(">", "");
strInputData = strInputData.Replace("*", "");
strInputData = strInputData.Replace("-", "");
strInputData = strInputData.Replace("?", "");
strInputData = strInputData.Replace("'", "''");
strInputData = strInputData.Replace(",", "");
strInputData = strInputData.Replace("/", "");
strInputData = strInputData.Replace(";", "");
strInputData = strInputData.Replace("*/", "");
strInputData = strInputData.Replace("\r\n", "");
strInputData = strInputData.Trim();
return strInputData;
}
}
}
