using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data; // State variables 
using System.Globalization;
using System.Data.SqlClient; // Date
using System.IO;
using System.Text.RegularExpressions;
using System.Linq.Expressions;

namespace ADO.NETCheck
{
    class Program
    {
        //用户输入值
        string userInput;
        //入口
        static void Main(string[] args)
        {
            Program p = new Program();
            p.InitDB();
            bool flag = true;
            while (flag) 
            {
           
                if (p.MenuReturn() == true)
                {
                    p.Action();
                }
                else
                {
                    flag = false;
                }
            }
        }
        //返回菜单的选择结果
        private bool MenuReturn()
        {
            //输入1添加员工信息——a
            Console.WriteLine("Enter a to add employee info——a");
            //输入2查询工资金额为指定金额的职工工号和姓名——b
            Console.WriteLine("Enter b to check the employee with the specific wage——b");
            //输入3查询出勤率为0的职工姓名和工号——c
            Console.WriteLine("Enter c to check the employee whose attendance=0——c");
            //输入4查询出勤率为10并且工资金额小于2500的职工信息——d
            Console.WriteLine("Enter d to check the employee whose attendance=10 and wage<=2500——d");
            //请输入（输入‘a’、‘b’、‘c’、‘d’以外的字符将退出程序）
            Console.Write("Please enter (words other than 'a','b','c','d' means quit):");
            string reg = "^[a,b,c,d]$";
            //ReadLine会自动从输入中刨除回车
            userInput = Console.ReadLine();
            if (Regex.IsMatch(userInput.ToString(), @reg))
            {
                return true;
            }
            else 
            {
                return false;
            }
        }
        //判断用户选择的操作
        private void Action()
        {
            if (userInput == "a") 
            {
                AddEmployeeInfo();
            }
            if (userInput == "b") 
            {
                Console.Write("Please enter the wage you want to check:");
                string wageLimit = Console.ReadLine();
                checkWage(wageLimit);
            }
            if (userInput == "c") 
            {
                Console.Write("Please enter the attendance number you want to check:");
                string attNumString = Console.ReadLine();
                int attNum = int.Parse(attNumString);
                checkAttend(attNum);
            }
            if (userInput == "d") 
            {
                checkAttendAndWage();
            }
            
            Console.ReadKey();
        }
        //插入相关记录——a
        private void AddEmployeeInfo()
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            //生命周期开始自动回收
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                //读取文件中的employee信息
                string employeeReadPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\employees.txt";
                var lines = File.ReadLines(@employeeReadPath);
                List<string> txt = new List<string>();
                foreach (var line in lines)
                {
                    txt.Add(line);
                }
                txt.ForEach(t =>
                {
                    string[] keyValues = t.Split(',');
                    //一定要注意严格遵守VALUES('','','')的格式不要忘记加单引号''
                    command.CommandText += "INSERT INTO Employee VALUES ('" + keyValues[0] + "','" + keyValues[1] + "','" + keyValues[2] + "','" + keyValues[3] + "','" + keyValues[4] + "') ";
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                    }
                });
                //读取文件中的wages信息
                command.CommandText = null;
                string wagesReadPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\wages.txt";
                lines = File.ReadLines(@wagesReadPath);
                List<string> txt1 = new List<string>();
                foreach (var line in lines)
                {
                    txt1.Add(line);
                }
                txt1.ForEach(t =>
                {
                    string[] keyValues = t.Split(',');
                    //一定要注意严格遵守VALUES('','','')的格式不要忘记加单引号''
                    command.CommandText += "INSERT INTO Wage VALUES ('" + keyValues[0] + "','" + keyValues[1] + "') ";
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                    }
                });
                //读取文件中的attendances信息
                command.CommandText = null;
                string attendReadPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\attendances.txt";
                lines = File.ReadLines(@attendReadPath);
                List<string> txt2 = new List<string>();
                foreach (var line in lines)
                {
                    txt2.Add(line);
                }
                txt2.ForEach(t =>
                {
                    string[] keyValues = t.Split(',');
                    //一定要注意严格遵守VALUES('','','')的格式不要忘记加单引号''
                    command.CommandText += "INSERT INTO Attend VALUES ('" + keyValues[0] + "','" + keyValues[1] + "','" + keyValues[2] + "') ";
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                    }
                });
                connection.Close();
            }
        }
        //查询工资金额为指定金额的职工工号和姓名——b
        private void checkWage(string wage)
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select Employee.Num,Employee.Name from Employee left join Attend on(Attend.Num = Employee.Num) left join Wage on(Wage.No = Attend.No) where (Wage.Amount = '" + wage + "')";
                try
                {
                    SqlCheck(command);
                }
                catch (Exception ex)
                {
                    WriteLog(ex);
                }
                connection.Close();
            }
        }
        //查询出勤率为0的职工姓名和工号——c
        private void checkAttend(int att)
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select Employee.Num,Employee.Name from Employee join Attend on(Attend.Num = Employee.Num) where (Attend.Attendance = '" + att + "')";
                try
                {
                    SqlCheck(command);           
                }
                catch (Exception ex)
                {
                    WriteLog(ex);
                }
                connection.Close();
            }
        }
        //查询出勤率为10并且工资金额小于2500的职工信息——d
        private void checkAttendAndWage()
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select * from Employee left join Attend on(Attend.Num = Employee.Num) left join Wage on(Wage.No = Attend.No) where (Wage.Amount < 2500 AND Attend.Attendance = 10)";
                try
                {
                    SqlCheck(command);
                }
                catch (Exception ex)
                {
                    WriteLog(ex);
                }
                connection.Close();
            }
        }
        //数据库查询并返回结果的方法
        private void SqlCheck(SqlCommand command)
        {
            //执行查询并将查询结果填充到数据集
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = command;
            DataSet ds = new DataSet();
            sqlDA.Fill(ds);
            if (ds.Tables[0].Rows.Count != 0)
            {
                //遍历并打印数据集
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        Console.Write(dc + ":");
                        Console.WriteLine(dr[dc].ToString());
                    }
                }
            }
            else
            {
                Console.WriteLine("No suitable result.");
            }
        }
        //初始化数据库方法
        public void InitDB()
        {
            try
            {
                string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "create table Employee(Num int NOT NULL,Name varchar(50),Sex varchar(50),Age int,Department varchar(50)) ";
                command.CommandText += "create table Wage(No int NOT NULL,Amount money) ";
                command.CommandText += "create table Attend(Num int NOT NULL,No int,Attendance int) ";
                //Add primary key.
                command.CommandText += "alter table Employee add constraint PK_Num primary key(Num) ";
                command.CommandText += "alter table Wage add constraint PK_No primary key(No) ";
                command.CommandText += "alter table Attend add constraint PK_NumAttend primary key(Num) ";
                //Add foreign key.
                command.CommandText += "alter table Attend add constraint FK_Num foreign key(Num) references Employee(Num) ";
                command.CommandText += "alter table Attend add constraint FK_No foreign key(No) references Wage(No) ";
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex) 
            {
                WriteLog(ex);
            }
        }
        //异常log打印方法
        public void WriteLog(Exception ex) 
        {
            string logUrl = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\ADOlog.txt";
            if (File.Exists(@logUrl))
            {
                FileStream fs = new FileStream(logUrl, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);
                try
                {           
                    sw.Write(ex);
                }
                catch (Exception ex1)
                {
                    WriteLog(ex1);
                }
                finally
                {
                    sw.Close();
                    fs.Close();
                }
            }
            else
            {
                FileStream fs = new FileStream(logUrl, FileMode.CreateNew);
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);
                try
                {
                    sw.Write(ex);
                }
                catch (Exception ex1)
                {
                    WriteLog(ex1);
                }
                finally
                {
                    sw.Close();
                    fs.Close();
                }
            }
        }
    }
}