/// <summary>
/// 获取所有的报警机组详细信息列表
/// </summary>
/// <returns></returns>
public string GetAllGZJZInfo(string gcname)
{
List <JZInfo> jzs = new List <JZInfo>();
string sql = "SELECT * FROM `web`.`机组表` where 状态>1 and 工厂名称='" + gcname + "'";
Dictionary <string, object> d = new Dictionary <string, object>();
DataTable dTable = data.GetTable(sql);
d.Add("total", data.GetTable(sql).Rows.Count);
for (int i = 0; i < dTable.Rows.Count; i++)
{
JZInfo jz = new JZInfo();
jz.Id = dTable.Rows[i]["机组编号"].ToString();
jz.Name = dTable.Rows[i]["机组名称"].ToString();
jz.Introduce = dTable.Rows[i]["机组描述"].ToString();
jz.JTName = dTable.Rows[i]["集团名称"].ToString();
jz.GCName = dTable.Rows[i]["工厂名称"].ToString();
jz.Boss = dTable.Rows[i]["负责人"].ToString();
jz.JW = dTable.Rows[i]["机组经纬度"].ToString();
jz.Status = int.Parse(dTable.Rows[i]["状态"].ToString());
jzs.Add(jz);
}
d.Add("rows", jzs);
return(JsonConvert.SerializeObject(d));
}
/// <summary>
/// 保存机组的信息
/// </summary>
/// <param name="jz"></param>
public bool Add(JZInfo jz)
{
string sql = "INSERT INTO `web`.`机组表`(`机组编号`, `机组名称`, `机组描述`, `集团名称`, `工厂名称`, `负责人`, `机组经纬度`) VALUES ('" +
jz.Id + "', '" +
jz.Name + "', '" +
jz.Introduce + "', '" +
jz.JTName + "', '" +
jz.GCName + "', '" +
jz.Boss + "', '" +
jz.JW + "');" +
"DROP TABLE IF EXISTS `" + jz.Id + "`;" +
"CREATE TABLE `" + jz.Id + "` " +
"(`ID` int(100) NOT NULL, " +
"`测试日期` date NOT NULL," +
"`测试时间` time DEFAULT NULL," +
"`备注` varchar(2) DEFAULT NULL," +
" PRIMARY KEY (`ID`, `测试日期`)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8 " +
"partition by range (to_days(`测试日期`))" +
"(partition P0 values less than (to_days('2019-01-01'))," +
"partition P1 values less than (to_days('2019-07-01'))," +
"partition P2 values less than (to_days('2020-01-01'))," +
"partition P3 values less than (to_days('2020-07-01'))," +
"partition P4 values less than (to_days('2021-01-01'))," +
"partition P5 values less than (to_days('2021-07-01'))," +
"partition P6 values less than (to_days('2022-01-01'))," +
"partition P7 values less than (to_days('2022-07-01'))," +
"partition P8 values less than (to_days('2023-01-01'))," +
"partition P9 values less than (to_days('2024-07-01'))," +
"partition P10 values less than (to_days('2025-01-01'))," +
"partition P11 values less than (to_days('2025-07-01')))";
return(data.ExecSql(sql));
}
/// <summary>
/// 获取所有的机组详细信息列表
/// </summary>
/// <returns></returns>
public string GetAllJZInfo(int page, int rows)
{
int start = (page - 1) * rows;
int end = page * rows;
List <JZInfo> jzs = new List <JZInfo>();
string sql = "SELECT * FROM `web`.`机组表`";
string where = "limit " + start + "," + end;
Dictionary <string, object> d = new Dictionary <string, object>();
//获取数据总数(注意是总数,不是一页中数据的条数)
DataTable dTable = data.GetTable(sql);
d.Add("total", data.GetTable(sql).Rows.Count);
//获取page页的数据
dTable = dTable.AsEnumerable().Skip((page - 1) * rows).Take(rows).CopyToDataTable();
for (int i = 0; i < dTable.Rows.Count; i++)
{
JZInfo jz = new JZInfo();
jz.Id = dTable.Rows[i]["机组编号"].ToString();
jz.Name = dTable.Rows[i]["机组名称"].ToString();
jz.Introduce = dTable.Rows[i]["机组描述"].ToString();
jz.JTName = dTable.Rows[i]["集团名称"].ToString();
jz.GCName = dTable.Rows[i]["工厂名称"].ToString();
jz.Boss = dTable.Rows[i]["负责人"].ToString();
jz.JW = dTable.Rows[i]["机组经纬度"].ToString();
jz.Status = int.Parse(dTable.Rows[i]["状态"].ToString());
jzs.Add(jz);
}
d.Add("rows", jzs);
return(JsonConvert.SerializeObject(d));
}
/// <summary>
/// 更新机组信息
/// </summary>
/// <param name="jz">机组信息</param>
/// <returns>更新是否成功</returns>
public bool Update(JZInfo jz)
{
string sql = "Update `web`.`机组表` SET " +
"`机组名称` = '" + jz.Name + "'," +
"`机组描述` = '" + jz.Introduce + "'," +
"`集团名称` = '" + jz.JTName + "'," +
"`工厂名称` = '" + jz.GCName + "'," +
"`负责人` = '" + jz.Boss + "'," +
"`机组经纬度` = '" + jz.JW + "'" + " WHERE `机组编号` = '" + jz.Id + "'";
return(data.ExecSql(sql));
}
/// <summary>
/// 保存机组的信息并创建测点表
/// </summary>
/// <param name="jz"></param>
public bool Add(JZInfo jz)
{
string sql = "INSERT INTO `web`.`机组表`(`机组编号`, `机组名称`, `机组描述`, `集团名称`, `工厂名称`, `负责人`, `机组经纬度`) VALUES ('" +
jz.Id + "', '" +
jz.Name + "', '" +
jz.Introduce + "', '" +
jz.JTName + "', '" +
jz.GCName + "', '" +
jz.Boss + "', '" +
jz.JW + "');" +
"DROP TABLE IF EXISTS `" + jz.Id + "`;" +
"CREATE TABLE `" + jz.Id + "` " +
"(`ID` bigint(100) NOT NULL, " +
"`测试日期` date DEFAULT NULL," +
"`测试时间` time DEFAULT NULL," +
"`备注` varchar(2) DEFAULT NULL," +
" PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(ID MOD 500000) partitions 300;";
return(data.ExecSql(sql));
}
/// <summary>
/// 保存机组的信息并创建测点表 JZHandler.ashx（2018.10.19创建）
/// </summary>
/// <param name="jz"></param>
public bool Add(JZInfo jz)
{
string sql = "INSERT INTO `web`.`机组表`(`机组编号`, `机组名称`, `机组描述`, `集团名称`, `工厂名称`, `负责人`, `机组经纬度`) VALUES ('" +
jz.Id + "', '" +
jz.Name + "', '" +
jz.Introduce + "', '" +
jz.JTName + "', '" +
jz.GCName + "', '" +
jz.Boss + "', '" +
jz.JW + "');" +
"DROP TABLE IF EXISTS `" + jz.Id + "`;" +
"CREATE TABLE `" + jz.Id + "` " +
"(`ID` bigint(100) NOT NULL, " +
"`测试日期` date DEFAULT NULL," +
"`测试时间` time DEFAULT NULL," +
"`备注` varchar(2) DEFAULT NULL," +
" PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(ID MOD 500000) partitions 300;" +
"DROP TABLE IF EXISTS `" + jz.Id + "F" + "`;" +
"CREATE TABLE `" + jz.Id + "F" + "` " +
"(`ID` bigint(20) DEFAULT NULL, `R_NAME` varchar(50) DEFAULT NULL,`T_POINT` int(11) DEFAULT NULL,`T_DATE` varchar(10) DEFAULT NULL,`T_TIME` varchar(10) DEFAULT NULL,`F_MAX` float DEFAULT NULL,`F_MIN` float DEFAULT NULL,`F_MEAN` float DEFAULT NULL,`F_RMS` float DEFAULT NULL,`F_BF` float DEFAULT NULL,`F_BX` float DEFAULT NULL,`F_CW` float DEFAULT NULL,`F_CQ` float DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
return(data.ExecSql(sql));
}
public void ProcessRequest(HttpContext context)
{
ResultInfo result = new ResultInfo();
JZInfo jz = new JZInfo();
string action = context.Request["action"].ToString();
context.Response.ContentType = "text/plain";
switch (action)
{
case "GetAll":
context.Response.Write(GetAll());
break;
case "GetJZState":
string jzid = context.Request.Form["JZId"];
context.Response.Write(GetJZState(jzid));
break;
case "GetGC":
string jt = context.Request["jt"];
string gc = context.Request["gc"];
context.Response.Write(GetJZ(jt, gc));
break;
case "Add":
try
{
jz.Id = context.Request.Form["Id"];
jz.Name = context.Request.Form["Name"];
jz.Introduce = context.Request.Form["Introduce"];
jz.JTName = context.Request.Form["JTName"];
jz.GCName = context.Request.Form["GCName"];
jz.Boss = context.Request.Form["Boss"];
jz.JW = context.Request.Form["JW"];
//保存数据到数据库中
result.Success = Add(jz);
result.Message = "插入机组信息" + ((result.Success == true) ? "成功" : "失败") + "!";
}
catch (Exception ex)
{
result.Success = false;
result.Message = "异常:" + ex.Message;
}
//返回客户端信息
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "GetAllJZId":
context.Response.Write(GetAllJZId());
break;
case "Update":
try
{
jz = new JZInfo();
jz.Id = context.Request.Form["Id"];
jz.Name = context.Request.Form["Name"];
jz.Introduce = context.Request.Form["Introduce"];
jz.JTName = context.Request.Form["JTName"];
jz.GCName = context.Request.Form["GCName"];
jz.Boss = context.Request.Form["Boss"];
jz.JW = context.Request.Form["JW"];
result.Success = Update(jz);
result.Message = "更新机组[" + jz.Id + "]信息" + ((result.Success == true) ? "成功" : "失败") + "!";
}
catch (Exception ex)
{
result.Success = false;
result.Message = "异常:" + ex.Message;
}
//返回客户端信息
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "Delete":
try
{
int count = 0;
string id = context.Request["Id"];
string[] ids = id.Split(',');
for (int i = 0; i < ids.Length; i++)
{
result.Success = Delete(ids[i]);
if (result.Success == false)
{
break;
}
count++;
}
result.Message = ((result.Success == true) ? "操作成功:" : "操作失败:") + "共删除" + count + "条信息!";
}
catch (Exception ex)
{
result.Success = false;
result.Message = "异常:" + ex.Message;
}
//返回客户端信息
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "GetAllJZInfo":
int page = int.Parse(context.Request.Form["page"]);
int rows = int.Parse(context.Request.Form["rows"]);
context.Response.Write(GetAllJZInfo(page, rows));
break;
case "StoreGCName":
gcname = context.Request.Form["GCName"].ToString();
if (gcname != null && gcname != "")
{
result.Success = true;
result.Message = "集团名称临时存储成功！";
}
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "GetAllGZJZInfo":
context.Response.Write(GetAllGZJZInfo(gcname));
break;
case "ToExcel":
string tableName = context.Request.Form["JZId"].ToString();
string startTime = context.Request.Form["Start"].ToString();
string endTime = context.Request.Form["End"].ToString();
string filepath = tableName + "-" + startTime + "-" + endTime + ".xls";
try
{
DataTable dTable = GetTermJZInfo(tableName, startTime, endTime);
// 进行Excel转换操作，并返回转换的文件下载链接
string urlPath = ExcelHelper.EntityListToExcel2003(dTable);
context.Response.ContentType = "text/plain";
context.Response.Write(urlPath); // 返回Json格式的内容
}
catch (Exception ex)
{
throw ex;
}
break;
default:
break;
}
}
public string tableName = Web.JZ.jz1.JZBH;//机组编号，机组运行记录表名称
public void ProcessRequest(HttpContext context)
{
ResultInfo result = new ResultInfo();
JZInfo jz = new JZInfo();
// context.Response.Write(context.Request["action"].ToString());
string action = context.Request["action"].ToString();
context.Response.ContentType = "text/plain";
switch (action)
{
case "GetJZCDInfo":
context.Response.Write(GetJZCDInfo());
break;
case "Q1":
context.Response.Write(GetCDVALUE(4));
break;
case "Q2":
context.Response.Write(GetCDVALUE(5));
break;
case "Q3":
context.Response.Write(GetCDVALUE(6));
break;
case "Q4":
context.Response.Write(GetCDVALUE(7));
break;
case "Q5":
context.Response.Write(GetCDVALUE(8));
break;
case "Q6":
context.Response.Write(GetCDVALUE(9));
break;
case "Q7":
context.Response.Write(GetCDVALUE(10));
break;
case "Q8":
context.Response.Write(GetCDVALUE(11));
break;
case "Qstatus":
context.Response.Write(Getstatus());
break;
case "newTime":
context.Response.Write(GetNewTime());
break;
case "newCdData":
string targetcdname = context.Request.Form["cdname"].ToString();
context.Response.Write(GetNewCdData(targetcdname));
break;
case "GetAll":
context.Response.Write(GetAll());
break;
case "GetJZState":
string jzid = context.Request.Form["JZId"];
context.Response.Write(GetJZState(jzid));
break;
case "GetGC":
string jt = context.Request["jt"];
string gc = context.Request["gc"];
context.Response.Write(GetJZ(jt, gc));
break;
case "Add":
try
{
jz.Id = context.Request.Form["Id"];
jz.Name = context.Request.Form["Name"];
jz.Introduce = context.Request.Form["Introduce"];
jz.JTName = context.Request.Form["JTName"];
jz.GCName = context.Request.Form["GCName"];
jz.Boss = context.Request.Form["Boss"];
jz.JW = context.Request.Form["JW"];
//保存数据到数据库中
result.Success = Add(jz);
result.Message = "插入机组信息" + ((result.Success == true) ? "成功" : "失败") + "!";
}
catch (Exception ex)
{
result.Success = false;
result.Message = "异常:" + ex.Message;
}
//返回客户端信息
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "GetAllJZId":
context.Response.Write(GetAllJZId());
break;
case "Update":
try
{
jz = new JZInfo();
jz.Id = context.Request.Form["Id"];
jz.Name = context.Request.Form["Name"];
jz.Introduce = context.Request.Form["Introduce"];
jz.JTName = context.Request.Form["JTName"];
jz.GCName = context.Request.Form["GCName"];
jz.Boss = context.Request.Form["Boss"];
jz.JW = context.Request.Form["JW"];
result.Success = Update(jz);
result.Message = "更新机组[" + jz.Id + "]信息" + ((result.Success == true) ? "成功" : "失败") + "!";
}
catch (Exception ex)
{
result.Success = false;
result.Message = "异常:" + ex.Message;
}
//返回客户端信息
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "Delete":
try
{
int count = 0;
string id = context.Request["Id"];
string[] ids = id.Split(',');
for (int i = 0; i < ids.Length; i++)
{
result.Success = Delete(ids[i]);
if (result.Success == false)
{
break;
}
count++;
}
result.Message = ((result.Success == true) ? "操作成功:" : "操作失败:") + "共删除" + count + "条信息!";
}
catch (Exception ex)
{
result.Success = false;
result.Message = "异常:" + ex.Message;
}
//返回客户端信息
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "GetAllJZInfo":
int page = int.Parse(context.Request.Form["page"]);
int rows = int.Parse(context.Request.Form["rows"]);
context.Response.Write(GetAllJZInfo(page, rows));
break;
case "StoreGCName":
gcname = context.Request.Form["GCName"].ToString();
if (gcname != null && gcname != "")
{
result.Success = true;
result.Message = "集团名称临时存储成功！";
}
context.Response.Write(JsonHelper <ResultInfo> .ObjectToJsonString(result));
break;
case "GetAllGZJZInfo":
context.Response.Write(GetAllGZJZInfo(gcname));
break;
case "ToExcel":
context.Response.ContentType = "text/plain";
DocInfo file = new DocInfo();
// string tableName = context.Request.Form["JZId"].ToString();
//jz1 myjz = new jz1();//实例化 非静态类
//string tableName =myjz.JZBH;
string startTime = context.Request.Form["Start"].ToString();
string endTime = context.Request.Form["End"].ToString();
string cdname = context.Request.Form["cdname"].ToString();
if (cdname == "" || cdname == "全部测点")
{
cdname = "allCd";
}
string filepath = tableName + "-" + cdname + "-" + startTime + "-" + endTime + ".csv";
// string JZBH = Web.File.FileHandler.jzidstr;
string path = HttpContext.Current.Request.MapPath("~/");
string dir = "/Files/" + tableName + "/";// /可能要去掉
string dirtocreate = path + Path.GetDirectoryName(dir);
if (!Directory.Exists(dirtocreate))
{
Directory.CreateDirectory(dirtocreate);
}
string fullDir = dir + filepath; //完整路径
try
{
// string sql = string.Format("SELECT ID,`测试日期`,`测试时间`,{3} FROM `{0}` where 测试日期 between '{1}' and '{2}' LIMIT 0,{4}", tableName, startTime, endTime, cdname,300);
string sql;
if (cdname == "allCd")
{
sql = string.Format("SELECT * FROM `{0}` where 测试日期 between '{1}' and '{2}' and ID>0 ", tableName, startTime, endTime);
}
else
{
sql = string.Format("SELECT ID,`测试日期`,`测试时间`,{3} FROM `{0}` where 测试日期 between '{1}' and '{2}' and ID>0 ", tableName, startTime, endTime, cdname);
}
DataTable dTable = data.GetTable(sql);
dTable.TableName = tableName;
SaveCSV(dTable, fullDir);
//file.JZId = tableName;
//file.Name = filepath;//文件名
//file.UploadTime = DateTime.Now.ToString();//文件上传时间
//file.Note = fullDir;//备注信息存储的是文件的物理地址
//file.Type = ".csv";
//string sqlinsert = "INSERT INTO `web`.`文档库`(`机组编号`, `文档名`, `上传时间`, `备注`, `类型`) VALUES ('"+file.JZId + "','" +file.Name + "','" +file.UploadTime + "', '" + file.Note + "', '" + file.Type + "')";
if (dTable.Rows.Count > 0)
{
result.Success = true;
}
else
{
result.Success = false;
}
// 进行Excel转换操作，并返回转换的文件下载链接
// string urlPath = ExcelHelper.EntityListToExcel2003(dTable);
// context.Response.ContentType = "text/plain";
context.Response.Write(fullDir); // 返回Json格式的内容
}
catch (Exception ex)
{
throw ex;
}
break;
case "GetJZCDData":
//string cdname1 = "定辊低速级行星齿";
cdname = context.Request.Form["cdname"];
context.Response.Write(GetJZCDData(cdname));
break;
case "GetJZCDfft":
cdname = context.Request.Form["cdname"];
context.Response.Write(GetJZCDfft(cdname));
break;
default:
break;
}
}