using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using System.Collections;
using System.IO;

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

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

            string sql3 = "select casic_loginfo.\"LOGID\",casic_loginfo.\"LOGDATE\","
            + "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";
            DataTable table = OledbHelper.QueryTable(sql3);
           // MessageBox.Show(sql+"==");

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

            this.dataGridView1.Columns[0].HeaderText = "日志编号";
            this.dataGridView1.Columns[1].HeaderText = "日志日期";
            this.dataGridView1.Columns[2].HeaderText = "用户";
            this.dataGridView1.Columns[3].HeaderText = "操作";
            this.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\",casic_loginfo.\"LOGDATE\","
                +"casic_loginfo.\"LOGUSERNAME\",casic_funclist.\"OPERNAME\",casic_loginfo.\"NT\""
                + " from casic_loginfo join casic_funclist on casic_loginfo.\"LOGOPER\"= casic_funclist.\"OPERID\"";

            string user = (string)(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;

                    this.dataGridView1.Columns[0].HeaderText = "日志编号";
                    this.dataGridView1.Columns[1].HeaderText = "日志日期";
                    this.dataGridView1.Columns[2].HeaderText = "用户";
                    this.dataGridView1.Columns[3].HeaderText = "操作";
                    this.dataGridView1.Columns[4].HeaderText = "备注";
                }
                else
                {
                    dataGridView1.DataSource = table;
                    MessageBox.Show("没有找到任何数据！", "提示"); 
                }
            }
            catch (Exception 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.CreatePrompt = true;
                dlg.Title = "保存为Excel文件";
                dlg.FileName = "日志-" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    Stream myStream;
                    myStream = dlg.OpenFile();
                    StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                    string columnTitle = "";
                    try
                    {
                        sw.WriteLine("内容：操作日志；日期：" + DateTime.Now.ToString("yyyy-MM-dd"));

                        //写入列标题   
                        for (int i = 0; i < dataGridView1.ColumnCount; i++)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView1.Columns[i].HeaderText;
                        }
                        sw.WriteLine(columnTitle);

                        //写入列内容   
                        for (int j = 0; j < dataGridView1.Rows.Count; j++)
                        {
                            string columnValue = "";
                            for (int k = 0; k < dataGridView1.Columns.Count; k++)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView1.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                            }

                            sw.WriteLine(columnValue);
                        }
                        sw.Close();
                        myStream.Close();
                        if (MessageBox.Show("导出Excel文件成功！是否打开？", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            System.Diagnostics.Process.Start(dlg.FileName);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                    finally
                    {
                        sw.Close();
                        myStream.Close();
                    }
                }
            }

        }

        private void userLists_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

    }
}