YXHY_WCS_YL/ControlMonitorClient/FrmDeviceErrorLog.cs

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
                    cbField.ValueMember = "name";
                    cbField.DisplayMember = "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)
            { 

            }

        }
    }
}