using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;

namespace Cyberpipe
{
    public partial class FrmLogOper : Office2007Form
    {
        public FrmLogOper()
        {
            InitializeComponent();

            //load userlist
            //loadUsers();
            //automatic search
            searchInit();
            userLists.Items.Add("");
            userLists.Items.Add("游客");
        }
        //初始化查询操作日志
        private void searchInit() {
            dataGridView1.ReadOnly = true;

            /*
            string sql3 = "select casic_loginfo.LOGID,to_char(casic_loginfo.LOGDATE,'YYYY-MM-DD hh24:mi:ss'),"
            + "casic_loginfo.LOGUSERNAME,casic_funclist.OPERNAME,casic_loginfo.NT from "
            + "casic_loginfo join casic_funclist on casic_loginfo.LOGOPER =casic_funclist.OPERID"
            + " where casic_loginfo.LOGUSERNAME ='" + Utility.userName
            + "' and casic_loginfo.LOGDATE between to_date('" + datetime1.Value.AddDays(-6).ToString("yyyy-MM-dd") + "','yyyy-MM-dd')"
            + " and to_date('" + datetime2.Value.AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-MM-dd') ";
          
            sql3 = sql3 + " order by casic_loginfo.LOGID desc";
            */

            string sql = "select logid,to_char(LOGDATE,'YYYY-MM-DD hh24:mi:ss'),LOGUSERNAME,LOGOPER "
                         + "from casic_loginfo";

            DataTable table = OledbHelper.QueryTable(sql);
           // MessageBox.Show(sql+"==");

            if (table == null)
            {
                MessageBox.Show("数据库中没有日志信息！", "提示");
                return;
            }
            dataGridView1.DataSource = table;

            dataGridView1.Columns[0].HeaderText = "日志编号";
            dataGridView1.Columns[1].HeaderText = "日志日期";
            dataGridView1.Columns[2].HeaderText = "用户";
            dataGridView1.Columns[3].HeaderText = "操作";
            //dataGridView1.Columns[4].HeaderText = "备注";
        }
        //加载用户列表
        private void loadUsers(){ 
            userLists.Items.Clear();

            userLists.Items.Add("全部");
            userLists.SelectedItem = "全部"; 
            string sql = "select casic_userinfotest.USERNAME from casic_userinfotest";

            DataTable table = OledbHelper.QueryTable(sql);

            if (table != null)
            {
                for (int i = 0; i < table.Rows.Count; i++) {
                    userLists.Items.Add(table.Rows[i][0].ToString());
                } 
            } 

        }
        //查询
        private void searchBtn_Click(object sender, EventArgs e)
        {
            int compare=DateTime.Compare(datetime1.Value,datetime2.Value);
            if (compare == 1)
            {
                MessageBox.Show("起始时间不能大于终止时间，请重新设置！", "提示"); 
            }
            else {

                string sql = "select casic_loginfo.LOGID,to_char(casic_loginfo.LOGDATE,'YYYY-MM-DD hh24:mi:ss'),"
                +"casic_loginfo.LOGUSERNAME,casic_funclist.OPERNAME,casic_loginfo.NT"
                + " from casic_loginfo join casic_funclist on casic_loginfo.LOGOPER= casic_funclist.OPERID";

            string user = userLists.Text;
            if (user != "")
            {
                sql = sql + " and casic_loginfo.LOGUSERNAME = '" + user + "'";
                sql = sql + " and casic_loginfo.LOGDATE between to_date('" 
                    + datetime1.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')"
                + " and to_date('" + datetime2.Value.AddDays(1).ToString("yyyy-MM-dd") 
                + "','yyyy-MM-dd')";
            }
            else {
                sql = sql + " and casic_loginfo.LOGDATE between to_date('" 
                    + datetime1.Value.ToString("yyyy-MM-dd") +"','yyyy-MM-dd')"
                   + " and to_date('" + datetime2.Value.AddDays(1).ToString("yyyy-MM-dd")
                   + "','yyyy-MM-dd')";
            }
            sql = sql + " order by casic_loginfo.LOGDATE,casic_loginfo.LOGID desc";
             
            try
            {
                DataTable table = OledbHelper.QueryTable(sql);
                if (table.Rows.Count > 0)
                {
                    dataGridView1.DataSource = table;
                    dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

                    dataGridView1.Columns[0].HeaderText = "日志编号";
                    dataGridView1.Columns[1].HeaderText = "日志日期";
                    dataGridView1.Columns[2].HeaderText = "用户";
                    dataGridView1.Columns[3].HeaderText = "操作";
                    dataGridView1.Columns[4].HeaderText = "备注";
                }
                else
                {
                    dataGridView1.DataSource = table;
                    MessageBox.Show("没有找到任何数据！", "提示"); 
                }
            }
            catch (Exception ex)
            {
                LogError.PublishError(ex);
                MessageBox.Show(ex.Message);
            }
            }
        }
        //导出excel
        private void exportBtn_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            { 
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Filter = "Excel files (*.xls)|*.xls";
                dlg.FilterIndex = 0;
                dlg.RestoreDirectory = true;
                dlg.Title = "保存为Excel文件";
                dlg.FileName = "日志-" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    ExpEXCEL.ExpToExcel(dataGridView1,dlg.FileName,"操作日志");
                }
            }

        }

    }
}