You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
213 lines
8.8 KiB
213 lines
8.8 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Drawing;
|
|
using System.Text;
|
|
using System.Windows.Forms;
|
|
using System.IO;//20161121
|
|
using DBFactory;
|
|
namespace wcfControlMonitorClient
|
|
{
|
|
/// <summary>
|
|
/// Creator:Richard.liu
|
|
/// 设备故障查询
|
|
/// </summary>
|
|
public partial class FrmDeviceErrorLog : Form
|
|
{
|
|
private static FrmDeviceErrorLog _formInstance;
|
|
|
|
public static FrmDeviceErrorLog FormInstance
|
|
{
|
|
get
|
|
|
|
{
|
|
if (_formInstance == null)
|
|
{
|
|
_formInstance = new FrmDeviceErrorLog();
|
|
isFirstShow = false;
|
|
}
|
|
return _formInstance;
|
|
}
|
|
set { _formInstance = value; }
|
|
}
|
|
DBOperator dbo = CStaticClass.dbo;
|
|
static bool isFirstShow = false;//20130117
|
|
DataSet ds = new DataSet();//20161121
|
|
public FrmDeviceErrorLog()
|
|
{
|
|
|
|
InitializeComponent();
|
|
this.dateTimePickerBegin.Value = DateTime.Today;
|
|
this.dateTimePickerEnd.Value = DateTime.Today;
|
|
_formInstance = this;//20101028
|
|
|
|
}
|
|
|
|
private void btQuery_Click(object sender, EventArgs e)
|
|
{
|
|
try
|
|
{
|
|
string searchstr = string.Empty;
|
|
|
|
if (this.cbField.Text.Trim().Length != 0 && this.tbContent.Text.Trim().Length != 0)
|
|
{
|
|
searchstr = string.Format(" And {0} like '%{1}%'", cbField.Text.Trim(), tbContent.Text.Trim());
|
|
}
|
|
|
|
//string sql = "SELECT * FROM V_manage_task_bak WHERE (开始时间 >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss") + "') AND (开始时间 <= '" + dateTimePicker2.Value.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss") + "')";
|
|
string sql = string.Format("SELECT * FROM V_Device_Error_log WHERE (发生时间 >= '{0}') AND (发生时间 <= '{1}' {2})", dateTimePickerBegin.Value.ToString("yyyy-MM-dd HH:mm:ss"), dateTimePickerEnd.Value.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss"), searchstr);
|
|
ds = dbo.ExceSQL(sql);
|
|
DataView dv = ds.Tables[0].DefaultView;
|
|
//DataView dv = dbo.ExceSQL(sql).Tables[0].DefaultView;
|
|
this.dataGridView1.DataSource = dv;
|
|
this.labelCounts.Text = string.Format("共{0}行", dv.Count);
|
|
}
|
|
catch(Exception ex )
|
|
{
|
|
MessageBox.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
|
}
|
|
}
|
|
|
|
private void btNullQuery_Click(object sender, EventArgs e)
|
|
{
|
|
if (MessageBox.Show("您确认要清空数据库内记录的所有设备的历史报警吗？", "操作提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
|
|
{
|
|
return;
|
|
}
|
|
dbo.ExceSQL("delete from T_Base_Device_Error_Log");
|
|
}
|
|
|
|
private void FrmDeviceErrorLog_Load(object sender, EventArgs e)
|
|
{
|
|
if (isFirstShow==false)
|
|
{
|
|
try
|
|
{
|
|
DataView dvs = dbo.ExceSQL("Select name from syscolumns Where ID=OBJECT_ID('V_Device_Error_log') ORDER BY colorder").Tables[0].DefaultView;
|
|
//cbstartposition select COLUMN_NAME FROM user_col_comments WHERE TABLE_NAME='V_Device_Error_log'
|
|
|
|
// add for YB2 oracle
|
|
//DataView dvs = dbo.ExceSQL("select COLUMN_NAME FROM user_col_comments WHERE TABLE_NAME='V_DEVICE_ERROR_LOG' ").Tables[0].DefaultView;
|
|
cbField.ValueMember = "COLUMN_NAME";
|
|
cbField.DisplayMember = "COLUMN_NAME";
|
|
cbField.DataSource = dvs;
|
|
cbField.SelectedIndex = -1;
|
|
////string sql = string.Format("Delete from T_Manage_Task_BAK where FBEGTIME ='-' or ABS(datediff(day,FBEGTIME,getdate()))>{0}", CStaticClass.SaveDays);
|
|
////dbo.ExceSQL(sql);
|
|
////string sql = string.Format("Delete from T_Base_Device_Error_Log where ABS(datediff(day,F_DateTime,getdate()))>{0}", 180);
|
|
//string sql = string.Format("Delete from T_Base_Device_Error_Log where CONVERT (varchar(19) ,F_DateTime) < dateadd(day,{0},getdate())", -60);
|
|
//dbo.ExceSQL(sql);
|
|
isFirstShow = true;
|
|
}
|
|
catch(Exception ex )
|
|
{
|
|
throw ex;
|
|
|
|
}
|
|
}
|
|
}
|
|
public void WriteExcel(DataSet ds, string path)//20161121
|
|
{
|
|
try
|
|
{
|
|
long totalCount = ds.Tables[0].Rows.Count;
|
|
//labelstatus.Text = "... ";
|
|
//labelstatus.Text = "Total " + totalCount + " Rows";
|
|
//Thread.Sleep(1000);
|
|
long rowRead = 0;
|
|
float percent = 0;
|
|
|
|
StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("UTF-8"));//英文系统不会乱码 ，分隔符是,
|
|
//StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("GB2312"));//分隔符是\t
|
|
StringBuilder sb = new StringBuilder();
|
|
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
|
|
{
|
|
sb.Append(ds.Tables[0].Columns[k].ColumnName.ToString() + ",");//"\t"
|
|
}
|
|
sb.Append(Environment.NewLine);
|
|
|
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
{
|
|
rowRead++;
|
|
percent = ((float)(100 * rowRead)) / totalCount;
|
|
//Pbar.Maximum = (int)totalCount;
|
|
//Pbar.Value = (int)rowRead;
|
|
//labelstatus.Text = string.Format("{0} Rows,{1}% Completed...", totalCount, percent.ToString("0.00"));// "正在写入[" + percent.ToString("0.00") + "%]...的数据";
|
|
System.Windows.Forms.Application.DoEvents();
|
|
|
|
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
|
|
{
|
|
sb.Append(ds.Tables[0].Rows[i][j].ToString() + ",");
|
|
}
|
|
sb.Append(Environment.NewLine);
|
|
}
|
|
sw.Write(sb.ToString());
|
|
sw.Flush();
|
|
sw.Close();
|
|
string mes = string.Format("{0} 行数据已经成功导出! ", totalCount);
|
|
MessageBox.Show(mes, "提示", MessageBoxButtons.OK,
|
|
MessageBoxIcon.Asterisk);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|
}
|
|
}
|
|
private void btExport_Click(object sender, EventArgs e)//20161121
|
|
{
|
|
try
|
|
{
|
|
int rowscount;
|
|
if (ds == null)
|
|
{
|
|
return;
|
|
}
|
|
rowscount = ds.Tables[0].DefaultView.Count;
|
|
if (rowscount == 0)
|
|
{
|
|
//MessageBox.Show("Can not find the In/Out Records before the date you selected!", "Prompt Message", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
|
|
return;
|
|
}
|
|
string localFilePath;
|
|
string saveFilepath = "D:\\";
|
|
|
|
saveFileDialog1.Title = "Save File As...";
|
|
saveFileDialog1.FileName = string.Format("{0}_{1}.xls", "AlarmRecords",
|
|
DateTime.Now.ToString("yyyyMMddHHmmss"));
|
|
saveFileDialog1.Filter = "Excel97-2003 (*.xls)|*.xls|All Files (*.*)|*.*";
|
|
|
|
if (System.IO.Directory.Exists(saveFilepath)) //文件目录是否存在
|
|
{
|
|
saveFileDialog1.InitialDirectory = saveFilepath;
|
|
}
|
|
|
|
saveFileDialog1.RestoreDirectory = true;
|
|
//saveFileDialog1.CheckFileExists = true;
|
|
saveFileDialog1.CheckPathExists = true;
|
|
saveFileDialog1.OverwritePrompt = true;
|
|
|
|
if (this.saveFileDialog1.ShowDialog() == DialogResult.OK)
|
|
{
|
|
|
|
localFilePath = saveFileDialog1.FileName.ToString();
|
|
|
|
|
|
if (rowscount > 1048576)
|
|
{
|
|
MessageBox.Show("数据超过了导出的最大行数1048576!请重新选择日期查询后导出!", "提示", MessageBoxButtons.OK,
|
|
MessageBoxIcon.Asterisk);
|
|
return;
|
|
}
|
|
WriteExcel(ds, localFilePath);
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|