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 System.Data.SqlClient;
using System.Data.OleDb;

namespace Lskj_SyncAccessData
{
    public partial class FrmMain : Form
    {
        /// <summary>
        /// 设置配置文件
        /// </summary>
        private string iniFile = "AccConfig.ini";
        /// <summary>
        /// 本地Access数据库文件名
        /// </summary>
        private string accdbConn;
        private OleDbConnection conn;
        /// <summary>
        /// 数据库名
        /// </summary>
        public string DataBase;
        /// <summary>
        /// 数据库表名
        /// </summary>
        public string tableName;
        /// <summary>
        /// 服务器名
        /// </summary>
        private string serverName;
        /// <summary>
        /// 用户名
        /// </summary>
        private string userName;
        /// <summary>
        /// 密码
        /// </summary>
        private string usePass;
        /// <summary>
        /// 间隔时间
        /// </summary>
        private int times = 60000;
        /// <summary>
        /// AccessDt
        /// </summary>
        private DataTable accesDt;


        private System.Timers.Timer timer = new System.Timers.Timer();

        public FrmMain()
        {
            InitializeComponent();
        }

        /// <summary>
        /// <para>说明：初始化</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期：2020-11-07 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="item">The item.</param>
        protected override void OnLoad(EventArgs e)
        {
            if (DBConfig.Instance.CreateConnection())
            {
                this.IniComboxBind();
                //读取ini文件里面的信息
                this.txtServerName.Text = IniHelper.Read(iniFile, "serverName");
                this.txtDBName.Text = IniHelper.Read(iniFile, "dataBase");
                this.txtUserName.Text = IniHelper.Read(iniFile, "userName");
                this.txtPass.Text = IniHelper.Read(iniFile, "userPass");
                this.txtFilePath.Text = IniHelper.Read(iniFile, "filePath");
                this.txtTime.Text = IniHelper.Read(iniFile, "time");
                this.accesTabName.Text = IniHelper.Read(iniFile, "accesTabName");
                this.TargetTab.SelectedValue = IniHelper.Read(iniFile, "TargetTab");
                //设置timer
                //timer.Interval = 10000;
                //设置是否重复计时，如果该属性设为False,则只执行timer_Elapsed方法一次。
                timer.AutoReset = true;
                timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
            }
        }

        #region 事件相关
        /// <summary>
        /// <para>说明：开始按钮点击</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期：2020-11-27 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        private void OnStartClick(object sender, EventArgs e)
        {
            DBConfig.Instance.ServerName = serverName = this.txtServerName.Text;
            DBConfig.Instance.DataBase = DataBase = this.txtDBName.Text;
            userName = this.txtUserName.Text;
            usePass = this.txtPass.Text;

            if (!string.IsNullOrEmpty(this.txtTime.Text))
            {
                times = Convert.ToInt32(this.txtTime.Text) * 60 * 1000;
            }
            if (string.IsNullOrEmpty(serverName))
            {
                MessageBox.Show("服务器地址不能为空！");
                this.txtServerName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(DataBase))
            {
                MessageBox.Show("数据库不能为空！");
                this.txtDBName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(userName))
            {
                MessageBox.Show("用户名不能为空！");
                this.txtUserName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(usePass))
            {
                MessageBox.Show("密码不能为空！");
                this.txtPass.Focus();
                return;
            }
            if (string.IsNullOrEmpty(this.txtFilePath.Text))
            {
                MessageBox.Show("Access文件路劲不能为空！");
                this.txtFilePath.Focus();
                return;
            }
            if (string.IsNullOrEmpty(this.accesTabName.Text))
            {
                MessageBox.Show("同步表不能为空！");
                this.accesTabName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(this.TargetTab.Text))
            {
                MessageBox.Show("目标表不能为空！");
                this.TargetTab.Focus();
                return;
            }


            IniHelper.Write(iniFile, "serverName", serverName);
            IniHelper.Write(iniFile, "dataBase", DataBase);
            IniHelper.Write(iniFile, "userName", userName);
            IniHelper.Write(iniFile, "userPass", usePass);
            IniHelper.Write(iniFile, "filePath", this.txtFilePath.Text);
            IniHelper.Write(iniFile, "time", times / 60000 + "");
            IniHelper.Write(iniFile, "accesTabName", this.accesTabName.Text);

            timer.Interval = times;
            timer.Enabled = true;
            this.txt_log.Text = "";
            RefreshLog(DateTime.Now + ":定时器启动中，请稍后\r\n");

            try
            {
                accdbConn = string.Format("provider=microsoft.ace.oledb.12.0;Data Source={0}", this.txtFilePath.Text);
                conn = AccessDBConfig.Instance.CreateConnection(@accdbConn);
                //if (conn != null)
                //{
                //    if (!AccessDBConfig.Instance.checkField("CODE", "isSync"))
                //    {
                //        string sqlValue = "alter table CODE add isSync bit default 0";
                //        AccessDBConfig.Instance.ExecuteSql(sqlValue);
                //        if (AccessDBConfig.Instance.checkField("CODE", "isSync"))
                //        {
                //            RefreshLog(DateTime.Now + ":Access数据库中表CODE字段isSync添加成功\r\n");
                //        }
                //    }
                //}
                //conn.Close();
            }
            catch (Exception ee)
            {
                RefreshLog(DateTime.Now + ee.Message + "\r\n");
            }
        }

        /// <summary>
        /// <para>说明：测试数据库连接</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期： </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        private void OnConnectClick(object sender, EventArgs e)
        {
            bool isSave = false;
            string serverName = this.txtServerName.Text.Trim();
            string dbName = this.txtDBName.Text.Trim();
            userName = this.txtUserName.Text;
            usePass = this.txtPass.Text;

            if (string.IsNullOrEmpty(serverName))
            {
                MessageBox.Show("服务器地址不能为空！");
                this.txtServerName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(dbName))
            {
                MessageBox.Show("数据库不能为空！");
                this.txtDBName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(userName))
            {
                MessageBox.Show("用户名不能为空！");
                this.txtUserName.Focus();
                return;
            }
            if (string.IsNullOrEmpty(usePass))
            {
                MessageBox.Show("密码不能为空！");
                this.txtPass.Focus();
                return;
            }
            if (string.IsNullOrEmpty(this.txtFilePath.Text))
            {
                MessageBox.Show("Access文件路劲不能为空！");
                this.txtFilePath.Focus();
                return;
            }
            DBConfig.Instance.ServerName = serverName;
            DBConfig.Instance.DataBase = dbName;
            // 测试数据库连接
            try
            {
                if (DBConfig.Instance.CreateConnection())
                {
                    if (isSave)
                    {
                        //DBConfig.Instance.WriteConfig();
                        this.DialogResult = DialogResult.OK;
                        this.Close();
                    }
                    else
                    {
                        MessageBox.Show("连接成功");
                    }
                }
                else
                {
                    MessageBox.Show("无法连接服务器,请检查网络");
                }
            }
            catch (Exception)
            {
                MessageBox.Show("无法连接服务器,请检查网络");
            }
        }

        /// <summary>
        /// <para>说明：定时器事件</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期：2020-11-27 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        private void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            if (this.txt_log.Lines.Count() > 1000)
            {
                this.txt_log.Text = "";
            }
            SynchronizeData();
            timer.Enabled = true;
        }

        /// <summary>
        /// <para>说明：选取文件并返回路径</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期： </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <returns>System.String.</returns>
        private void OnOpenClick(object sender, EventArgs e)
        {
            OpenFileDialog openDialog = new OpenFileDialog();
            if (DialogResult.OK == openDialog.ShowDialog())
            {
                string filename = openDialog.FileName;
                this.txtFilePath.Text = filename;
            }
        }

        /// <summary>
        /// <para>说明：暂停按钮点击</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期：2020-11-27 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        private void OnSuspendClick(object sender, EventArgs e)
        {
            timer.Enabled = false;
        }

        ///// <summary>
        ///// <para>说明：初始化表按钮点击</para>
        ///// <para>创建人：钱雄</para>
        ///// <para>创建日期：2020-11-27 </para>
        ///// <para>修改人：</para>
        ///// <para>修改日期：</para>
        ///// <para>修改备注：</para>
        ///// <para>版本：1.0</para>
        ///// </summary>
        ///// <param name="sender">The sender.</param>
        ///// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        //private void OnInitTable(object sender, EventArgs e)
        //{
        //    string createSql;
        //    if (DBConfig.Instance.CreateConnection()) {
        //        localconnStr = DBConfig.Instance.GetConnection();
        //        string sqlValue = string.Format("select count(1) as result from sys.objects where name = 'P_SystemMSGSendTab'");
        //        string sqlValue1 = string.Format("select count(1) as result from sys.objects where name = 'P_SystemMSGSendListTab'");

        //        try {
        //            if (SqlHelper.ExecuteDataTable(sqlValue).Rows[0]["result"] + "" != "1")
        //            {
        //                createSql = "CREATE TABLE P_SystemMSGSendTab(id int IDENTITY(1,1) NOT NULL,msgID bigint NULL,ClientCode varchar(50) NULL,OpenID varchar(50) NULL,MsgInfo varchar(500) NULL,MsgLink varchar(max) NULL,CreateTime datetime NULL,SendTime datetime NULL,SendStatus int NULL,StatusMsg varchar(150) NULL,msgType varchar(50) NULL,msg_first varchar(500) NULL,msg_keyword1 [varchar](300) NULL,msg_keyword2 varchar(300) NULL,msg_keyword3 varchar(300) NULL,msg_keyword4 varchar(300) NULL, msg_keyword5 varchar(300) NULL,msg_remark varchar(500) NULL,isSync bit null, PRIMARY KEY CLUSTERED (id ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]";
        //                if (SqlHelper.ExecuteNonQuery(createSql) > 0)
        //                {
        //                    MessageBox.Show("表P_SystemMSGSendTab创建成功");
        //                }
        //            }
        //            if (SqlHelper.ExecuteDataTable(sqlValue1).Rows[0]["result"] + "" != "1")
        //            {
        //                createSql = "CREATE TABLE P_SystemMSGSendListTab(id int IDENTITY(1,1) NOT NULL,msgID bigint NULL,ClientCode varchar(50) NULL,OpenID varchar(50) NULL,MsgInfo varchar(500) NULL,MsgLink varchar(max) NULL,CreateTime datetime NULL,SendTime datetime NULL,SendStatus int NULL,StatusMsg varchar(150) NULL,msgType varchar(50) NULL,msg_first varchar(500) NULL,msg_keyword1 [varchar](300) NULL,msg_keyword2 varchar(300) NULL,msg_keyword3 varchar(300) NULL,msg_keyword4 varchar(300) NULL, msg_keyword5 varchar(300) NULL,msg_remark varchar(500) NULL,isSync bit null, PRIMARY KEY CLUSTERED (id ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]";
        //                if (SqlHelper.ExecuteNonQuery(createSql) > 0)
        //                {
        //                    MessageBox.Show("表P_SystemMSGSendListTab创建成功");
        //                }
        //            }
        //        }
        //        catch(Exception ex) {
        //            MessageBox.Show(ex.Message);
        //        }
        //    }
        //}
        #endregion

        #region 方法相关
        /// <summary>
        /// 绑定下拉框值
        /// </summary>
        /// <param name="msg"></param>
        public void IniComboxBind()
        {
            DataTable comBoxTable = SqlHelper.ExecuteDataTable("select 'Mrp_EquipmentdockingTab' as dm,'磨边线、偏贴线' as mc union all select 'Mrp_EquipmentdockingTab_2' as dm,'实装线' as mc");
            TargetTab.DataSource = comBoxTable;
            TargetTab.ValueMember = "dm";
            TargetTab.DisplayMember = "mc";
        }
        /// <summary>
        /// 显示信息设置
        /// </summary>
        /// <param name="msg"></param>
        public void RefreshLog(string msg)
        {
            Invoke((EventHandler)delegate
            {
                this.txt_log.Text += msg;
                this.txt_log.Focus();//获取焦点
                this.txt_log.Select(this.txt_log.TextLength, 0);//光标定位到文本最后
                this.txt_log.ScrollToCaret();//滚动到光标处
            });
        }

        /// <summary>
        /// <para>说明：同步数据</para>
        /// <para>创建人：钱雄</para>
        /// <para>创建日期：2020-11-27 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        private void SynchronizeData()
        {
            timer.Enabled = false;
            string sqlValue = string.Empty;
            try
            {
                //数据库连接
                if (DBConfig.Instance.CreateConnection())
                {
                    sqlValue = string.Format("select count(1) as result from sys.objects where name = '{0}'", tableName);
                    string tabNamesql = string.Format("select name from MSysObjects  where type=1 and flags=0");
                    string result = SqlHelper.ExecuteDataTable(sqlValue).Rows[0]["result"] + "";
                    if (result == "1")
                    {
                        string[] TabNames = accesTabName.Text.Split(',');
                        if (TabNames.Length > 0)
                        {
                            DataTable dt = SqlHelper.ExecuteDataTable(string.Format("Select oid from {0}", tableName));
                            string ids = string.Empty;
                            foreach (DataRow dr in dt.Rows)
                            {
                                ids += dr["oid"] + ",";
                            }
                            ids = ids.TrimEnd(',');
                            foreach (string TabName in TabNames)
                            {
                                RefreshLog(DateTime.Now + ":数据获取中\r\n");
                                if (!string.IsNullOrEmpty(ids))
                                {
                                    sqlValue = string.Format("select * from {1} where id not in({0})", ids, TabName);
                                }
                                else
                                {
                                    sqlValue = string.Format("select * from {0}", TabName);
                                }
                                if (conn != null)
                                {
                                    try
                                    {
                                        accesDt = AccessDBConfig.Instance.ExecuteDataTable(sqlValue);
                                    }
                                    catch (Exception)
                                    {
                                        RefreshLog(DateTime.Now + "资源被占用，等待下一次同步\r\n");
                                    }
                                }
                                if (accesDt != null && accesDt.Rows.Count > 0)
                                {
                                    WriteDataToDB(accesDt, tableName);
                                }
                                else
                                {
                                    RefreshLog(DateTime.Now + "没有要插入的数据\r\n");
                                }
                            }
                        }
                        else
                        {
                            RefreshLog(DateTime.Now + "Access数据库中不存在表\r\n");
                        }

                    }
                    else
                    {
                        RefreshLog(DateTime.Now + "目标数据库没有对应表\r\n");
                    }
                    //timer.Enabled = true;
                }
                else
                {
                    RefreshLog(DateTime.Now + ":数据库连接失败\r\n");
                }
                //timer.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // <summary>
        /// 将DataTable中数据写入数据库中
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool WriteDataToDB(DataTable dt, string tableName)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return true;
            }
            string colNames = "";

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //if (i == dt.Columns.Count - 1) continue;
                if (dt.Columns[i].ColumnName == "ID")
                {
                    colNames += "oid" + ",";
                }
                else
                {
                    colNames += dt.Columns[i].ColumnName + ",";
                }
            }
            colNames += "operdate" + ",";
            colNames = colNames.TrimEnd(',');
            string cmd = "";
            string colValues;
            int count = 0;

            string cmdmode = string.Format("insert into {0} ({1}) values({{0}});", tableName, colNames);
            RefreshLog(DateTime.Now + "准备插入数据\r\n");
            string ids = string.Empty;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ids += dt.Rows[i][0] + ",";
                string sqlValue = string.Format("select oid from {0}", tableName);

                colValues = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    //if (j == dt.Columns.Count - 1) continue;
                    if (dt.Rows[i][j].GetType() == typeof(DBNull))
                    {
                        colValues += "NULL,";
                        continue;
                    }
                    if (dt.Columns[j].DataType == typeof(string))
                        colValues += string.Format("'{0}',", dt.Rows[i][j]);
                    else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))
                    {
                        colValues += string.Format("{0},", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(DateTime))
                    {
                        colValues += string.Format("cast('{0}' as datetime),", dt.Rows[i][j]);
                    }
                    else if (dt.Columns[j].DataType == typeof(bool))
                    {
                        colValues += string.Format("{0},", dt.Rows[i][j].ToString());
                    }
                    else
                        colValues += string.Format("'{0}',", dt.Rows[i][j]);
                }
                colValues += string.Format("cast('{0}' as datetime),", DateTime.Now);
                cmd += string.Format(cmdmode, colValues.TrimEnd(','));
                count++;
            }
            int ret = 0;
            try
            {
                if (count > 0)
                {
                    RefreshLog(DateTime.Now + ":数据插入中\r\n");
                    ret = SqlHelper.ExecuteNonQuery(cmd);
                    RefreshLog(DateTime.Now + ":成功插入" + ret + "条数据\r\n");

                    //try
                    //{
                    //    string updateValue = string.Format("Update code set isSync=1 where id in (select ID from code where isSync=0)");
                    //    int result = AccessDBConfig.Instance.ExecuteSql(updateValue);
                    //    if (result > 0)
                    //    {
                    //        RefreshLog(DateTime.Now + ":Access数据库中表CODE字段isSync修改成功\r\n");
                    //    }
                    //}
                    //catch (Exception e)
                    //{
                    //    RefreshLog(DateTime.Now + ":" + e.Message + "\r\n");
                    //}
                }
                //ids = ids.TrimEnd(',');
                //if (count == 0)
                //{
                //    RefreshLog(DateTime.Now + "没有要插入的数据\r\n");
                //}
            }
            catch (Exception e)
            {
                //写错误日志...
                string strOuput = string.Format("向数据库中写数据失败,错误信息:{0},异常{1}\n", e.Message, e.InnerException);
                RefreshLog(DateTime.Now + strOuput + "\r\n");
            }
            if (ret == -1)
            {
                return false;
            }
            return true;
        }
        #endregion

        private void TargetTab_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.tableName = this.TargetTab.SelectedValue+"";
        }

        ///// <summary>
        ///// <para>说明：创建数据库连接</para>
        ///// <para>创建人：钱雄</para>
        ///// <para>创建日期：2020-11-27 </para>
        ///// <para>修改人：</para>
        ///// <para>修改日期：</para>
        ///// <para>修改备注：</para>
        ///// <para>版本：1.0</para>
        ///// </summary>
        ///// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
        //public bool CreateConnection()
        //{
        //    connStr = GetConnection();
        //    if (SqlHelper._connection != null)
        //    {
        //        try
        //        {
        //            SqlHelper._connection.Close();
        //            SqlHelper._connection.Dispose();
        //        }
        //        catch (Exception)
        //        {
        //        }
        //        SqlHelper._connection = null;
        //    }
        //    try
        //    {
        //        SqlHelper._connection = new SqlConnection(connStr);
        //        SqlHelper._connection.Open();

        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //    return false;
        //}

        ///// <summary>
        ///// <para>说明：获取C#连接字符串</para>
        ///// <para>创建人：龚宇超</para>
        ///// <para>创建日期：2017-08-07 </para>
        ///// <para>修改人：</para>
        ///// <para>修改日期：</para>
        ///// <para>修改备注：</para>
        ///// <para>版本：1.0</para>
        ///// </summary>
        ///// <returns>System.String.</returns>
        //public string GetConnection()
        //{
        //    return string.Format(AESUtil.Decrypt(Connection1), serverName, DataBase);
        //}
    }
}