using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Net;
using System.Data.SqlClient;


namespace GlobalConnectStaff
{
    class Program
    {

        static void Main(string[] args)
        {
            gcstaffDataContext dc = new gcstaffDataContext();
            string strFileName = "globalconnectstaff.csv";
            string path = @"C:\GlobalConnectStaff";
            StreamWriter sw = File.CreateText(path + "\\" + strFileName);

            var staff = (from a in dc.usps_employees
                         join b in dc.usps_job_positions on a.EMPLOYEE_ID equals b.EMPLOYEE_ID
                         select new
                         {
                             b.EMPLOYEE_ID,
                             b.FULL_NAME,
                             b.FIRST_NAME,
                             b.LAST_NAME,
                             b.SEX,
                             a.PHONE,
                             a.PHONE_IN_DISTRICT,
                             b.BUILDING,
                             a.EMAIL_ADDRESS
                         }).Distinct();


            List<GcStaff> gcstaff = new List<GcStaff>();

            foreach (var s in staff)
            {
                gcstaff.Add(new GcStaff
                {
                    EmpId = Convert.ToInt32(s.EMPLOYEE_ID),
                    FullName = s.FULL_NAME,
                    First_Name = s.FIRST_NAME,
                    Last_Name = s.LAST_NAME,
                    Gender = s.SEX,
                    Phone = s.PHONE,
                    Phone2 = s.PHONE_IN_DISTRICT,
                    Building = s.BUILDING,
                    Email = s.EMAIL_ADDRESS
                });

            }

            string csvStaff = LinqToCSV.ToCsv<GcStaff>(gcstaff);
            sw.WriteLine("Employee_Reference_#,Full_Name,Fname,Lname,Gender,Phone #1, Phone #2, Location, Email_Address");
            sw.Write(csvStaff);

        }


    }
    public static class LinqToCSV
    {
        public static string ToCsv<T>(this IEnumerable<T> items)
            where T : class
        {
            var csvBuilder = new StringBuilder();
            var properties = typeof(T).GetProperties();
            foreach (T item in items)
            {
                string line = string.Join(",", properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
                csvBuilder.AppendLine(line);
            }
            return csvBuilder.ToString();
        }

        private static string ToCsvValue<T>(this T item)
        {
            if (item == null) return "\"\"";

            if (item is string)
            {
                return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
            }
            double dummy;
            if (double.TryParse(item.ToString(), out dummy))
            {
                return string.Format("{0}", item);
            }
            return string.Format("\"{0}\"", item);
        }
    }

}