using System;

using System.Collections.Generic;

using System.IO;

using System.Text;

using System.Linq;

namespace Assignment {  

​

    // ######################## DATABASE TABLES ########################### 

    /*  

        [Table: Employees]

        ---------------------------------------------------------------

        ID  FirstName   LastName    StartDate   Type

        ---------------------------------------------------------------

        1   Andy        Tan         2017-10-21     1    

        2   Rose        Lee         2018-02-15     2

        3   Jimmy       Edison      2018-02-01     2

        4   Marry       Lim         2019-03-22     3

        5   Thomas      Ruth        2019-01-22     4

​

        [Table: EmployeeTypes]

        --------------------------------------------------------------- 

        ID  Description

        ---------------------------------------------------------------

        1   Contractor

        2   Permanent

        3   Agent

        4   Other   

    */          

    // ######################## APPLICATION ########################### 

    public class Program

    {       

        // DO NOT CHANGE THE CODE IN THE Main()

        public static void Main()

        {               

            RenderSeparator();

            Console.WriteLine("## Part 1: ##");

            Console.WriteLine("");

            RenderEmployeeSQLStatement();

            RenderSeparator();

            Console.WriteLine("## Part 2: ##");

            Console.WriteLine("");

            RenderEmployeeData();

            RenderSeparator();

        }

​

        // Warm-up exercise: Write a SQL statement to pull out the employee data

        private static void RenderEmployeeSQLStatement()

        {       

            // Requirements: Write a SQL statement to pull out the employee data. The SQL statement should fulfilled the conditions below:

            //  1. The first column is employee ID. Use EmployeeID SQL ALIAS.

            //  2. EXCLUDE the employee type = 'other'

            //  3. The result must ORDER BY Employee FullName 

            //  4. The FullName column is concats the "FirstName" and the "LastName" column.

​

            //  4. At the end, the SQL result should return the data below: 

            //              EmployeeID          FullName            StartDate       Type

            //          ===========================================================================================

            //              1                   Andy Tan            2017-10-21      Contractor

            //              3                   Jimmy Edison        2018-02-01      Permanent

            //              4                   Marry Lim           2019-03-22      Agent

            //              2                   Rose Lee            2018-02-15      Permanent           

            // Part 1: Exercise

            // 1. Construct the SQL string here. Use the best way to construct the SQL string (variable concatenation, StringBuilder...)                        

            // 2. Output to SQL string to console windows (Click on the 'Run' button on top of the screen to see the output):           

            Console.WriteLine("Happy December 2022!!! Write the Employee SQL query here.");         

        }

        // Render the employee table to the output windows

        private static void RenderEmployeeData()

        {               

            // Part 2: Exercise

            // 1. The EmployeeService class has a function GetEmployees. Create the instance and call the EmployeeService.GetEmployees function to get a list of the employee data.

            // 2. Use the Console.WriteLine to render the Employees data shown below 

                    /* 

                        Render the output in the table form:

​

                        Employee ID         FullName            StartDate       Type

                        ===========================================================================================

                        1                   Andy Tan            2017-10-21      Contractor

                        3                   Jimmy Edison        2018-02-01      Permanent

                        4                   Marry Lim           2019-03-22      Agent

                        2                   Rose Lee            2018-02-15      Permanent

                    */

            // == Tips and Tricks ==

            // 1. You can use "\t" to give a tab space. Eg: a\tb will give result "a    b"

            // 2. You can use "\n" or WriteLine for new line.

            // 3. Use List<Employee>

            Console.WriteLine("{output the table here}");           

        }

        // #################################        

        // ## DO NOT CHANGE THIS FUNCTION ##

        // #################################

        // You may call this function if you want.

        private static void RenderSeparator(int occurences = 100)

        {

            Console.WriteLine(" ");

            for (int i = 0; i <= occurences; i++) {

                Console.Write("═");             

            }

            Console.WriteLine("");

            Console.WriteLine("");

        }

    }

​

    // ############################################################# START #################################################################### 

    // ####################################### Employee Service (DO NOT MAKE ANY THE CHANGES HERE) ############################################

    // ########################################################################################################################################

    public class Employee

    {

        public int ID;

        public string FirstName;

        public string LastName;

        public string FullName;

        public string StartDate;

        public int Type;

        public string TypeDescription;

    }

​

    public class EmployeeService

    {

        public EmployeeService()

        {

        }

        private List<Employee> EmployeesData {

            get {

                return new List<Employee>() {

                    new Employee() { ID=1, FirstName = "Andy", LastName = "Andy", FullName = "Andy Tan", StartDate="2017-10-21", Type=1, TypeDescription = "Contractor"},

                    new Employee() { ID=2, FirstName = "Rose", LastName = "Lee", FullName = "Rose Lee", StartDate="2018-02-15", Type=2, TypeDescription = "Permanent"},

                    new Employee() { ID=3, FirstName = "Jimmy", LastName = "Edison", FullName = "Jimmy Edison", StartDate="2018-02-01", Type=2, TypeDescription = "Permanent"},

                    new Employee() { ID=4, FirstName = "Marry", LastName = "Lim", FullName = "Marry Lim", StartDate="2019-03-22", Type=3, TypeDescription = "Agent"}

                };

            }

        }

        /// The Employees data order by the employee FullName

        public List<Employee> GetEmployees()

        {

            List<Employee> employees = EmployeesData;           

            return employees.OrderBy(e => e.FullName).ToList();

        }           

    }

    // ############################################################## END ##################################################################### 

    // ####################################### Employee Service (DO NOT MAKE ANY THE CHANGES HERE) ############################################

    // ########################################################################################################################################

}

​