using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;namespace Fxtools
{
    public partial class frmDataSync : Form
    {
        public frmDataSync()
        {
            InitializeComponent();
        }
        Comm_ResultDAL dal = new Comm_ResultDAL();
      
        private void btnSync_Click(object sender, EventArgs e)
        {
            //var tempID = comboBox2.Text == "Comm_Result" ? "TempID" : "TempID2";
            var taskCount = Convert.ToInt32(comboBox3.Text);
            var sourceTb = $"TestData_temp.[dbo].{comboBox2.Text} WITH (NOLOCK)";
            SyncData2(taskCount, sourceTb);
            //SyncData1();
        }

        public delegate void InvokeShowMsg(RichTextBox rtb, string msg);
        public void showMsg(RichTextBox rtb, string msg)
        {
            //在线程里以安全方式调用控件
            if (rtb.InvokeRequired)
            {
                InvokeShowMsg _myinvoke = new InvokeShowMsg(showMsg);
                rtb.Invoke(_myinvoke, new object[] { rtb, msg });
            }
            else
            {

                if (rtb.Lines.Length > 600)
                {
                    rtb.Clear();
                }

                rtb.AppendText(DateTime.Now.ToString("MM/dd hh:mm:ss ") + msg + "\r\n");
                rtb.SelectionStart = rtb.GetFirstCharIndexFromLine(rtb.Lines.Length - 1);
                rtb.SelectionLength = 0;
                rtb.Focus();
                rtb.ScrollToCaret();

            }
        }

        private ManualResetEvent onOff = new ManualResetEvent(true);//这个一个开关
        private void SyncData2(int taskCount,string sourceTb)
        {
            this.btnWait.Enabled = true;
            this.btnSync.Enabled = false;
            this.comboBox1.Enabled = false;
            this.comboBox2.Enabled = false;
            this.comboBox3.Enabled = false;
            var TempIDSql = "select isnull(TempID,0) from  TestData_temp.[dbo].[Comm_ResultSyncID] ";
            int saveSyncID2 = Convert.ToInt32(PubUtils.GetValue(TempIDSql));
            int totalCount = Convert.ToInt32(PubUtils.GetValue($"select count(*) from {sourceTb} where id >= {saveSyncID2} "));
            int finishedCount = 0;
            int top = Convert.ToInt32(comboBox1.Text);
            var sourceMaxIDSql = $"select max(id) from {sourceTb}";
            var sourceMaxID = Convert.ToInt32(PubUtils.GetValue(sourceMaxIDSql));
            Task parent = new Task(async () =>
            {              
                showMsg(richTextBox1, $"任务开始......");
                DateTime startTotal = DateTime.Now;
                while (saveSyncID2 < sourceMaxID)
                {
                    onOff.WaitOne();
                    List<Task> Tasks = new List<Task>();
                    DateTime start = DateTime.Now;
                    for (int i = 0; i < taskCount; i++)
                    {
                        saveSyncID2 = Convert.ToInt32(PubUtils.GetValue(TempIDSql));
                        var sourceLst = dal.GetNextLst(top, saveSyncID2, sourceTb);//取数                        
                        Tasks.Add(Task.Run(() => RunBackUp(sourceLst)));

                        finishedCount += top;
                        //完成 给完成提示信息。
                        showMsg(richTextBox1, $"启动任务：{i}");

                        //保存SaveSyncID
                        saveSyncID2 = dal.GetCurrentID(top, sourceTb, saveSyncID2);
                        dal.SaveTempSyncID(saveSyncID2);                        
                        if (saveSyncID2 >= totalCount) { break; } //退出
                       
                        Thread.Sleep(100 * 1);                       
                    }
                    await Task.WhenAll(Tasks);
                    DateTime end = DateTime.Now;
                    var totalsencond = (end - start).TotalSeconds;
                    var usedTime = (end - startTotal).TotalMinutes;
                    var issusedCount = totalCount - finishedCount;
                    double totalTime = usedTime / finishedCount * issusedCount / 60 / 24;//预估剩余耗时
                    showMsg(richTextBox1, $"本批次比对耗时：{totalsencond} 秒, 已耗时：{usedTime} 分钟,已比较数：{finishedCount},待比较数：{issusedCount},预计还需耗时：{totalTime}天");
                    //Thread.Sleep(1000 * 1);
                }
                showMsg(richTextBox1, $"任务已完成");
            });
            parent.Start(); 
            //parent.ContinueWith((task) =>
            //{
            //    showMsg(richTextBox1, "主任务完成，完成时候的状态为：");
            //    showMsg(richTextBox1, string.Format("IsCanceled={0}\tIsCompleted={1}\tIsFaulted={2}", task.IsCanceled, task.IsCompleted, task.IsFaulted));
            //});
        }

        private void RunBackUp(List<Comm_ResultModel> sourceLst)
        {
            #region 取数,比较 
            var targetTb = $"TestData.[dbo].Comm_Result WITH  (NOLOCK) ";
            var targetTb1 = $"TestData.[dbo].Comm_Result_his1 WITH (NOLOCK) ";
            var targetTb3 = $"TestData.[dbo].Comm_Result_his3 WITH (NOLOCK) ";
            var saveLst = new List<Comm_ResultModel>();
            DateTime start = DateTime.Now;
            foreach (var item in sourceLst)
            {
                var isTb =  dal.Exists2(item, targetTb);
                var isTb1 = dal.Exists2(item, targetTb1);
                var isTb3 = dal.Exists2(item, targetTb3);
                if (!isTb && !isTb1 && !isTb3)
                { saveLst.Add(item); }
            }
          
            #endregion

            //保存到His2
            if (saveLst.Count > 0)
            {
                dal.BulkAdd(saveLst);
            }
        }

        private void SyncData1()
        {
          
            this.btnSync.Enabled = false;
            this.comboBox1.Enabled = false;
            this.comboBox2.Enabled = false;
          
            var top = Convert.ToInt32(comboBox1.Text);
            var sourceTb = $"TestData_temp.[dbo].{comboBox2.Text} WITH (NOLOCK)";
            var targetTb = $"TestData.[dbo].Comm_Result WITH  (NOLOCK) ";
            var targetTb1 = $"TestData.[dbo].Comm_Result_his1 WITH (NOLOCK) ";
            var targetTb3 = $"TestData.[dbo].Comm_Result_his3 WITH (NOLOCK) ";
            var TempIDSql = "select isnull(TempID,0) from  TestData_temp.[dbo].[Comm_ResultSyncID] ";
            var sourceMaxIDSql = $"select max(id) from {sourceTb}";
            var sourceMaxID = Convert.ToInt32(PubUtils.GetValue(sourceMaxIDSql));
            int finishedCount = 0;

            Task t = new Task(() =>
            {
                var saveSyncID = 0;
                showMsg(richTextBox1, "任务开始工作……");
                DateTime totalStart = DateTime.Now;
                while (saveSyncID < sourceMaxID)
                {
                    #region 获取下一轮要比较的开始ID
                    saveSyncID = Convert.ToInt32(PubUtils.GetValue(TempIDSql));
                    int rowCountSource = Convert.ToInt32(PubUtils.GetValue($"select count(*) from {sourceTb} where id >= {saveSyncID} "));
                    //var initSql = "drop table TestData_temp.[dbo].#tb1 drop table TestData_temp.[dbo].#tb2 ";
                    string initSql = "if exists (select 1 \n"
                                + "    from  sysobjects \n"
                                + "   where  id = object_id('#tb1') \n"
                                + "    and   type = 'U') \n"
                                + "drop table #tb1 \n"

                                + " \n"
                                + "if exists (select 1 \n"
                                + "    from  sysobjects \n"
                                + "   where  id = object_id('#tb2') \n"
                                + "    and   type = 'U') \n"
                                + "drop table #tb2 \n";
                    var sourceSql0 = $"select top {top} *  from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
                    var sourceSql  = $"select top {top} *  INTO #tb1 from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
                    var sourceSql1 = $"select top {top + 1} *  INTO #tb2 from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
                    var getIDSql = $" SELECT a.Id FROM #tb2 a LEFT JOIN #tb1 b ON a.id = b.id WHERE b.id IS NULL  ";
                    var currentIDSql = $"{initSql} {sourceSql} {sourceSql1} {getIDSql}";
                    var currentID = Convert.ToInt32(PubUtils.GetSingle(currentIDSql, dal.connectionString, null));
                    //var currentID = Convert.ToInt32(PubUtils.GetValue(currentIDSql)); 
                    #endregion

                    #region 取数,比较
                    var sourceLst = PubUtils.GetDataTable(sourceSql0, dal.connectionString).ToList<Comm_ResultModel>();
                    var saveLst = new List<Comm_ResultModel>();
                    DateTime start = DateTime.Now;
                    foreach (var item in sourceLst)
                    {
                        //if (item.Fg_Sn == "PD11HD5291A.P5480")
                        //{ //test point
                        //}
                        var isTb = dal.Exists2(item, targetTb);
                        var isTb1 = dal.Exists2(item, targetTb1);
                        var isTb3 = dal.Exists2(item, targetTb3);
                        if (!isTb && !isTb1 && !isTb3)
                        { saveLst.Add(item); }                       
                        finishedCount++;//完成数
                    }
                    if (sourceLst.Count == 0)
                    {
                        saveSyncID = sourceMaxID;//取不到数，退出循环。
                    }
                    #endregion

                    //保存到His2
                    if (saveLst.Count > 0)
                    {
                        dal.BulkAdd(saveLst);
                    }
                    //保存 currentID 到 Comm_ResultSyncID 
                    dal.SaveTempSyncID(currentID);

                    DateTime end = DateTime.Now;
                    var totalsencond = (end - start).TotalSeconds;
                    int issusedCount = rowCountSource - finishedCount;//待完成数
                    double usedTime = (end - totalStart).TotalMinutes;//已用时
                    double totalTime = usedTime / finishedCount * issusedCount / 60 / 24;//预估剩余耗时
                    //Console.WriteLine($"本次查询耗时：{totalsencond} 秒, 已耗时：{usedTime} 分钟,已完成数：{finishedCount},待完成数：{issusedCount},预计还需耗时：{totalTime}天");
                    showMsg(richTextBox1, $"本Top数比对耗时：{totalsencond} 秒, 已耗时：{usedTime} 分钟,已比较数：{finishedCount},待比较数：{issusedCount},预计还需耗时：{totalTime}天");
                }
            });
            t.Start();

            t.ContinueWith((task) =>
            {
                showMsg(richTextBox1, "任务完成，完成时候的状态为：");
                showMsg(richTextBox1, string.Format("IsCanceled={0}\tIsCompleted={1}\tIsFaulted={2}", task.IsCanceled, task.IsCompleted, task.IsFaulted));
            });
        }
        private void SyncData()
        {
            this.btnSync.Enabled = false;
            this.comboBox1.Enabled = false;
            this.comboBox2.Enabled = false;
            Comm_ResultDAL dal = new Comm_ResultDAL();
            var top = Convert.ToInt32(comboBox1.Text);
            var sourceTb = $"TestData_temp.[dbo].{comboBox2.Text} WITH (NOLOCK)";
            var targetTb = $"TestData.[dbo].Comm_Result WITH  (NOLOCK) ";
            var targetTb1 = $"TestData.[dbo].Comm_Result_his1 WITH (NOLOCK) ";
            var targetTb3 = $"TestData.[dbo].Comm_Result_his3 WITH (NOLOCK) ";
            var TempIDSql = "select isnull(TempID,0) from  TestData_temp.[dbo].[Comm_ResultSyncID] ";
            var sourceMaxIDSql = $"select max(id) from {sourceTb}";
            var sourceMaxID = Convert.ToInt32(PubUtils.GetValue(sourceMaxIDSql));
            int finishedCount = 0;

            Task t = new Task(() =>
            {
                var saveSyncID = 0;
                showMsg(richTextBox1, "任务开始工作……");
                DateTime totalStart = DateTime.Now;
                while (saveSyncID < sourceMaxID)
                {
                    #region 获取下一轮要比较的开始ID
                    saveSyncID = Convert.ToInt32(PubUtils.GetValue(TempIDSql));
                    int rowCountSource = Convert.ToInt32(PubUtils.GetValue($"select count(*) from {sourceTb} where id >= {saveSyncID} "));
                    //var initSql = "drop table TestData_temp.[dbo].#tb1 drop table TestData_temp.[dbo].#tb2 ";
                    string initSql = "if exists (select 1 \n"
                                + "    from  sysobjects \n"
                                + "   where  id = object_id('#tb1') \n"
                                + "    and   type = 'U') \n"
                                + "drop table #tb1 \n"

                                + " \n"
                                + "if exists (select 1 \n"
                                + "    from  sysobjects \n"
                                + "   where  id = object_id('#tb2') \n"
                                + "    and   type = 'U') \n"
                                + "drop table #tb2 \n";
                    var sourceSql0 = $"select top {top} *  from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
                    var sourceSql = $"select top {top} *  INTO #tb1 from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
                    var sourceSql1 = $"select top {top + 1} *  INTO #tb2 from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
                    var getIDSql = $" SELECT a.Id FROM #tb2 a LEFT JOIN #tb1 b ON a.id = b.id WHERE b.id IS NULL  ";
                    var currentIDSql = $"{initSql} {sourceSql} {sourceSql1} {getIDSql}";
                    var currentID = Convert.ToInt32(PubUtils.GetSingle(currentIDSql, dal.connectionString, null));
                    //var currentID = Convert.ToInt32(PubUtils.GetValue(currentIDSql)); 
                    #endregion

                    #region 取数,比较
                    var sourceLst = PubUtils.GetDataTable(sourceSql0, dal.connectionString).ToList<Comm_ResultModel>();
                    var saveLst = new List<Comm_ResultModel>();
                    foreach (var item in sourceLst)
                    {
                        //if (item.Fg_Sn == "PD11HD5291A.P5480")
                        //{ //test point
                        //}
                        DateTime start = DateTime.Now;
                        var isTb = dal.Exists2(item, targetTb);
                        var isTb1 = dal.Exists2(item, targetTb1);
                        var isTb3 = dal.Exists2(item, targetTb3);
                        if (!isTb && !isTb1 && !isTb3)
                        { saveLst.Add(item); }
                        DateTime end = DateTime.Now;
                        var totalsencond = (end - start).TotalSeconds;
                        finishedCount++;//完成数

                        int issusedCount = rowCountSource - finishedCount;//待完成数
                        double usedTime = (end - totalStart).TotalMinutes;//已用时
                        double totalTime = usedTime / finishedCount * issusedCount / 60 / 24;//预估剩余耗时
                        //Console.WriteLine($"本次查询耗时：{totalsencond} 秒, 已耗时：{usedTime} 分钟,已完成数：{finishedCount},待完成数：{issusedCount},预计还需耗时：{totalTime}天");
                        showMsg(richTextBox1, $"本次查询耗时：{totalsencond} 秒, 已耗时：{usedTime} 分钟,已比较数：{finishedCount},待比较数：{issusedCount},预计还需耗时：{totalTime}天");
                    }
                    if (sourceLst.Count == 0)
                    {
                        saveSyncID = sourceMaxID;//取不到数，退出循环。
                    }
                    #endregion

                    //保存到His2
                    if (saveLst.Count > 0)
                    {
                        dal.BulkAdd(saveLst);
                    }
                    //保存 currentID 到 Comm_ResultSyncID 
                    dal.SaveTempSyncID(currentID);
                }
            });
            t.Start();

            t.ContinueWith((task) =>
            {
                showMsg(richTextBox1, "任务完成，完成时候的状态为：");
                showMsg(richTextBox1, string.Format("IsCanceled={0}\tIsCompleted={1}\tIsFaulted={2}", task.IsCanceled, task.IsCompleted, task.IsFaulted));
            });
        }

        private void frmDataSync_Load(object sender, EventArgs e)
        {
            this.btnWait.Enabled = false;
        }

        private void btnWait_Click(object sender, EventArgs e)
        {
            this.btnSync.Enabled = false;
            if (btnWait.Text == "暂停")
            {
                onOff.Reset();
                btnWait.Text = "继续";
                showMsg(richTextBox1, "主任务已暂停,耐心等待子任务完成！");
            }
            else
            {
                onOff.Set();//运行线程继续
                btnWait.Text = "暂停";
                showMsg(richTextBox1, "主任务继续");
            }
        }
    }

    public class Comm_ResultDAL {
        public string connectionString = "Data Source=192.168.101.203;Initial Catalog=TestData_temp;User ID=sa;Password=pwd;Pooling=False";

        public int GetCurrentID(int top,string sourceTb,int saveSyncID) {

            string initSql = "if exists (select 1 \n"
                               + "    from  sysobjects \n"
                               + "   where  id = object_id('#tb1') \n"
                               + "    and   type = 'U') \n"
                               + "drop table #tb1 \n"

                               + " \n"
                               + "if exists (select 1 \n"
                               + "    from  sysobjects \n"
                               + "   where  id = object_id('#tb2') \n"
                               + "    and   type = 'U') \n"
                               + "drop table #tb2 \n";            
            var sourceSql = $"select top {top} *  INTO #tb1 from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
            var sourceSql1 = $"select top {top + 1} *  INTO #tb2 from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
            var getIDSql = $" SELECT a.Id FROM #tb2 a LEFT JOIN #tb1 b ON a.id = b.id WHERE b.id IS NULL  ";
            var currentIDSql = $"{initSql} {sourceSql} {sourceSql1} {getIDSql}";
            var currentID = Convert.ToInt32(PubUtils.GetSingle(currentIDSql, connectionString, null));
            return currentID;
        }
        public List<Comm_ResultModel> GetNextLst(int top,int saveSyncID, string sourceTb)
        {
            var sourceSql0 = $"select top {top} *  from  {sourceTb} where ID >= {saveSyncID} ORDER BY id";
            var lst = PubUtils.GetDataTable(sourceSql0, connectionString).ToList<Comm_ResultModel>();
            return lst;
        }
        public bool SaveTempSyncID(int tempID)
        {
            bool result = false;
            // to do
            var sql = $"update TestData_temp.[dbo].[Comm_ResultSyncID] set TempID = {tempID} ";
            result = PubUtils.ExeSql(sql)>0;
            return result;
        }        
        public bool Exists(Comm_ResultModel m,string tableName)
        {
            string Station_ID = m.Station_ID; string Wo_Part = m.Wo_Part; string Fg_Sn = m.Fg_Sn; string Pcba_Sn = m.Pcba_Sn; string TestData = m.TestData;
            StringBuilder strSql = new StringBuilder();
            strSql.Append($"select count(1) from {tableName} ");
            strSql.Append(" where Station_ID=@Station_ID and Wo_Part=@Wo_Part and Fg_Sn=@Fg_Sn and Pcba_Sn=@Pcba_Sn and TestData=@TestData ");
            SqlParameter[] parameters = {
                    new SqlParameter("@Station_ID", SqlDbType.VarChar,50),
                    new SqlParameter("@Wo_Part", SqlDbType.VarChar,50),
                    new SqlParameter("@Fg_Sn", SqlDbType.VarChar,100),
                    new SqlParameter("@Pcba_Sn", SqlDbType.VarChar,100),
                    new SqlParameter("@TestData", SqlDbType.VarChar,8000)};
            parameters[0].Value = Station_ID;
            parameters[1].Value = Wo_Part;
            parameters[2].Value = Fg_Sn;
            parameters[3].Value = Pcba_Sn;
            parameters[4].Value = TestData;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        public bool Exists2(Comm_ResultModel m, string tableName)
        {
            string Station_ID = m.Station_ID; string Wo_Part = m.Wo_Part; string Fg_Sn = m.Fg_Sn; string Pcba_Sn = m.Pcba_Sn; string TestData = m.TestData;
            string strSql = $"select top 1 ID from {tableName} where Station_ID='{Station_ID}' and Wo_Part='{Wo_Part}' " +           
                            $"and  isnull(Fg_Sn,'')='{Fg_Sn}' and isnull(Pcba_Sn,'')='{Pcba_Sn}' and isnull(TestData,'')='{TestData}' ";
            return PubUtils.IsExists(strSql) > 0;
        }
        public  void BulkAdd(List<Comm_ResultModel> lst)
        {
            DataTable dt = lst.CopyToDataTable<Comm_ResultModel>();
           
            string tableName = "TestData.[dbo].[Comm_Result_his2]";
            using (SqlConnection destinationConnection = new SqlConnection(connectionString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {                       
                        bulkCopy.DestinationTableName = tableName;//要插入的表的表名
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("Station_ID", "Station_ID");//映射字段名 DataTable列名 ,数据库 对应的列名  
                        bulkCopy.ColumnMappings.Add("Wo_nbr", "Wo_nbr");
                        bulkCopy.ColumnMappings.Add("Wo_Part", "Wo_Part");
                        bulkCopy.ColumnMappings.Add("Fg_Sn", "Fg_Sn");
                        bulkCopy.ColumnMappings.Add("Pcba_Sn", "Pcba_Sn");
                        bulkCopy.ColumnMappings.Add("Result", "Result");
                        bulkCopy.ColumnMappings.Add("Added", "Added");
                        bulkCopy.ColumnMappings.Add("TestData", "TestData");
                        bulkCopy.ColumnMappings.Add("Project_No", "Project_No");

                        bulkCopy.WriteToServer(dt);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {

                    }
                }
            }
        }
    }
    public class Comm_ResultModel
    { 
        public int ID { get; set; }
        public string Wo_nbr { get; set; }  
        public string Wo_Part { get; set; }    
        public string Fg_Sn { get; set; }
        public string Pcba_Sn { get; set; }
        public string Station_ID { get; set; }    
        public bool Result { get; set; }
        public string TestData { get; set; } 
        public string Project_NO { get; set; }
        public DateTime Added { get; set; }
    }
}