XS202306098555/ExtraUI/ReadCheckRecordWindowModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.IO;
using AGV_WPF_Global;
using System.Data;
using DataServices;
using System.Data.OleDb;
using AGV_WPF.Commands;

namespace AGV_WPF.ExtraUI
{
    public class ReadCheckRecordWindowModel
    {
        public ReadCheckRecordWindowModel(Window w)
        {
            recordWindow = w as ReadCheckRecordWindow;
            recordWindow.DateChanged += new RoutedEventHandler(recordWindow_DateChanged);
            recordWindow.Closed += new EventHandler(recordWindow_Closed);
            CheckChangedCommand = new DelegateCommand();
            CheckChangedCommand.ExecuteAction = new Action<object>(this.CheckChanged);
            ClearRecordCommand = new DelegateCommand();
            ClearRecordCommand.ExecuteAction = new Action<object>(this.ClearRecord);
            for (int i = 0; i < MainWindow.mainWindow.AGVNUM_MAX;i++ )
            {
                recordWindow.comboBoxCarID.Items.Add(string.Format("{0}", i + 1));
            }
        }
        //用于删除临时文件
        void recordWindow_Closed(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(tempFile))//如果临时文件路径不为空则执行删除
                {
                    File.Delete(tempFile);
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        ExcelDataBase excelData;
        string tempFile;//临时文件路径
        public Visibility IsVisible 
        {
            get
            {
                if (GlobalPara.IsManager)
                    return Visibility.Visible;
                else
                    return Visibility.Hidden;
            }
        }
        /// <summary>
        /// 加载排行数据
        /// </summary>
        /// <param name="date">待排行文件</param>
        /// <param name="type">排行类型，AGV和站点</param>
        /// <param name="kind">筛选类型，0为无效，1为漏卡，2为掉线，3为漏卡掉线</param>
        public void LoadRankData(string date,string type,int kind)
        {
            string dateFile = string.Format(@"{0}\{1}.xls", GlobalPara.linkDirectory, date);//获取对应日期源文件
            string currentDate = DateTime.Now.ToString("yyyy-MM-dd");
            if (date.Equals(currentDate))//如果查询的是当天，则先复制到临时文件中
            {
                if (File.Exists(dateFile))
                {
                    
                    tempFile = string.Format(@"{0}\{1}.xls", System.IO.Path.GetTempPath(), date);
                    if (!File.Exists(tempFile))
                    {
                        System.IO.File.Copy(dateFile, tempFile);
                    }
                    excelData = new ExcelDataBase(tempFile);
                    try
                    {
                        string sql="";
                        if (GlobalPara.IsManager)//管理员可查看所有信息
                        {
                            if (type == "AGV")
                            {
                                if (kind == 1)
                                {
                                    sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "漏读");
                                }
                                else if (kind == 2)
                                {
                                    sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "掉线");
                                }
                                else if (kind == 3)
                                {
                                    sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' or [记录信息] like '%{4}%'group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "漏读","掉线");
                                }
                                else
                                    return;
                            }
                            else if(type == "站点")
                            {
                                if (kind == 1)
                                {
                                    sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "漏读");
                                }
                                else if (kind == 2)
                                {
                                    sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "掉线");
                                }
                                else if (kind == 3)
                                {
                                    sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' or [记录信息] like '%{4}%'group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "掉线","漏读");
                                }
                                else
                                    return;
                            }
                            else
                            {
                                return;
                            }
                        }
                        else
                        {
                            if (type == "AGV")
                            {
                                if (kind == 1)
                                { sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "漏读"); }
                                else
                                    return;
                            }
                            else if (type == "站点")
                            {
                                if (kind == 1)
                                { sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "漏读"); }
                                else
                                    return;
                            }
                            else
                            {
                                return;
                            }
                        }
                        DataTable table = (DataTable)excelData.Execute(sql);
                        recordWindow.listBox1.ItemsSource = table.DefaultView;
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                else
                {
                    recordWindow.listBox1.ItemsSource = null;
                    return;
                }
            }
            else//否则直接读取
            {
                
                if (File.Exists(dateFile))
                {
                    excelData = new ExcelDataBase(dateFile);
                    try
                    {
                        string sql = "";
                        if (GlobalPara.IsManager)//管理员可查看所有信息
                        {
                            if (type == "AGV")
                            {
                                if (kind == 1)
                                {
                                    sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "漏读");
                                }
                                else if (kind == 2)
                                {
                                    sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "掉线");
                                }
                                else if (kind == 3)
                                {
                                    sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' or [记录信息] like '%{4}%'group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "漏读", "掉线");
                                }
                                else
                                    return;
                            }
                            else if (type == "站点")
                            {
                                if (kind == 1)
                                {
                                    sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "漏读");
                                }
                                else if (kind == 2)
                                {
                                    sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "掉线");
                                }
                                else if (kind == 3)
                                {
                                    sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' or [记录信息] like '%{4}%'group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "掉线", "漏读");
                                }
                                else
                                    return;
                            }
                            else
                            {
                                return;
                            }
                        }
                        else
                        {
                            if (type == "AGV")
                            {
                                if (kind == 1)
                                { sql = string.Format("select [AGV编号] as {0},count([AGV编号]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [AGV编号] order by count([AGV编号]) desc", "Name", "Counts", GlobalPara.readCheckRecordName, "漏读"); }
                                else
                                    return;
                            }
                            else if (type == "站点")
                            {
                                if (kind == 1)
                                { sql = string.Format("select [应读站点] as {0},count([应读站点]) as {1} from [{2}$] where [记录信息] like '%{3}%' group by [应读站点] order by count([应读站点]) desc", "Name1", "Counts", GlobalPara.readCheckRecordName, "漏读"); }
                                else
                                    return;
                            }
                            else
                            {
                                return;
                            }
                        }
                        DataTable table = (DataTable)excelData.Execute(sql);
                        recordWindow.listBox1.ItemsSource = table.DefaultView;
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                }
                else
                {
                    recordWindow.listBox1.ItemsSource = null;
                    return;
                }
            }

        }
        /// <summary>
        /// 加载指定日期记录
        /// </summary>
        /// <param name="date">指定日期</param>
        /// <param name="carId">待筛选的AGV编号，默认0为筛选全部</param>
        public void LoadExcelData(string date, int carId=0)
        {
            string dateFile = string.Format(@"{0}\{1}.xls", GlobalPara.linkDirectory, date);//获取对应日期源文件
            string currentDate = DateTime.Now.ToString("yyyy-MM-dd");
            if (date.Equals(currentDate))//如果查询的是当天，则先复制到临时文件中
            {
                if (File.Exists(dateFile))
                {
                    
                    tempFile = string.Format(@"{0}\{1}.xls", System.IO.Path.GetTempPath(), date);
                    if (!File.Exists(tempFile))
                    {
                        System.IO.File.Copy(dateFile, tempFile);
                    }
                    excelData = new ExcelDataBase(tempFile);

                    try
                    {
                        string sql;
                        if (GlobalPara.IsManager)//管理员可查看所有信息
                        {
                            sql = string.Format("select * from [{0}$]", GlobalPara.readCheckRecordName);
                            if (carId > 0)
                                sql = string.Format("{0} where [AGV编号]={1}", sql, carId);
                        }
                        else
                        {
                            sql = string.Format("select * from [{0}$] where [记录信息] not like '%掉线%' and [记录信息] not like '%连接%'", GlobalPara.readCheckRecordName);
                            if (carId > 0)
                                sql = string.Format("{0} and [AGV编号]={1}", sql, carId);
                        }
                        DataTable table = (DataTable)excelData.Execute(sql);
                        recordWindow.dataGrid1.ItemsSource = table.DefaultView;
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                else
                {
                    //MessageBox.Show("无对应记录");
                    recordWindow.dataGrid1.ItemsSource = null;
                    return;
                }
            }
            else//否则直接读取
            {
                
                if (File.Exists(dateFile))
                {
                    excelData = new ExcelDataBase(dateFile);
                    try
                    {
                        string sql;
                        if (GlobalPara.IsManager)//管理员可查看所有信息
                        {
                            sql = string.Format("select * from [{0}$]", GlobalPara.readCheckRecordName);
                            if (carId > 0)
                                sql = string.Format("{0} where [AGV编号]={1}", sql, carId);
                        }
                        else
                        {
                            sql = string.Format("select * from [{0}$] where [记录信息] not like '%掉线%' and [记录信息] not like '%连接%'", GlobalPara.readCheckRecordName);
                            if (carId > 0)
                                sql = string.Format("{0} and [AGV编号]={1}", sql, carId);
                        }
                        DataTable table = (DataTable)excelData.Execute(sql);
                        recordWindow.dataGrid1.ItemsSource = table.DefaultView;
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                }
                else
                {
                    //MessageBox.Show("无对应记录");
                    recordWindow.dataGrid1.ItemsSource = null;
                    return;
                }
            }
            
        }
        void recordWindow_DateChanged(object sender, RoutedEventArgs e)
        {
            string date = recordWindow.calendar1.SelectedDate.Value.ToString("yyyy-MM-dd");//获取指定格式日期字符串
            LoadExcelData(date);
        }
        /// <summary>
        /// 清空记录
        /// </summary>
        /// <param name="parameter"></param>
        public void ClearRecord(object parameter)
        {
            if (MessageBox.Show("确定要清除之前所有记录？", "警告", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.No)
                return;
            string currentDateFile = string.Format(@"{0}.xls",DateTime.Now.ToString("yyyy-MM-dd"));
            try
            {
                
                var files = from file in Directory.EnumerateFiles(string.Format(@"{0}\", GlobalPara.linkDirectory), "*.xls") where file.ToLower().Contains(currentDateFile) != true select file;
                foreach (string f in files)
                {
                    File.Delete(f);
                    //Console.WriteLine(f);
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        /// <summary>
        /// 窗口Check动作处理
        /// </summary>
        /// <param name="parameter"></param>
        public void CheckChanged(object parameter)
        {
            int kind = 0;
            if (recordWindow.checkBox1.IsChecked.Value && recordWindow.checkBox2.IsChecked.Value)
            {
                kind = 3;
            }
            else if (recordWindow.checkBox1.IsChecked.Value)
            {
                kind = 1;
            }
            else if (recordWindow.checkBox2.IsChecked.Value)
            {
                kind = 2;
            }

            if (recordWindow.calendar1.SelectedDate != null)
            {
                LoadRankData(recordWindow.calendar1.SelectedDate.Value.ToString("yyyy-MM-dd"), recordWindow.comboBoxType.Text, kind);
                
            }
            else
            {
                LoadRankData(DateTime.Now.ToString("yyyy-MM-dd"), recordWindow.comboBoxType.Text, kind);
                
            }
        }
        /// <summary>
        /// 关联窗口
        /// </summary>
        ReadCheckRecordWindow recordWindow;
        List<TestClass> list = new List<TestClass>();
        public DelegateCommand CheckChangedCommand { get; set; }
        public DelegateCommand ClearRecordCommand { get; set; }
    }
    public class TestClass
    {
        public int Number { get; set; }
        public string Name { get; set; }
        public int Counts { get; set; }
    }
}