using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MSE_LIB
{
    public class MSE
    {
        public const string dbConnectionsTring = "SE0085MSE";

        public MSEConfigurationSPResultRecord InsertMSEConfigurationRecord
            (
            string AssemblyLineAbbrv,
            string MfgGate,
            int SchedRunRate,
            DateTime StartDate,
            DateTime EndDate,
            int PerformLevel1,
            int PerformLevel2
            )
        {
            SqlConnection dbMSE = null;
            SqlDataReader rdr = null;

            MSEConfigurationSPResultRecord ldReturnData = new MSEConfigurationSPResultRecord();
            try
            {

                // obtain connection string from application config file
                var connectionString = ConfigurationManager.ConnectionStrings[dbConnectionsTring].ConnectionString;

                // create and open a connection object
                dbMSE = new
                    SqlConnection(connectionString);
                dbMSE.Open();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand(
                    "sp_InsertUpdateRunRate", dbMSE);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add("@pAssemblyLineAbbrv", SqlDbType.VarChar).Value = AssemblyLineAbbrv == "" ? System.Convert.DBNull : AssemblyLineAbbrv;
                cmd.Parameters.Add("@pMfgGate", SqlDbType.VarChar).Value = MfgGate == "" ? System.Convert.DBNull : MfgGate;
                cmd.Parameters.Add("@pSchedBeginDate", SqlDbType.DateTime).Value = StartDate == null ? System.Convert.DBNull : StartDate;
                cmd.Parameters.Add("@pSchedEndDate", SqlDbType.DateTime).Value = EndDate == null ? System.Convert.DBNull : EndDate;
                cmd.Parameters.Add("@pSchedRunRate", SqlDbType.Int).Value = SchedRunRate == 0 ? System.Convert.DBNull : SchedRunRate;
                cmd.Parameters.Add("@pPerformLevel1", SqlDbType.Int).Value = PerformLevel1 == 0 ? System.Convert.DBNull : PerformLevel1;
                cmd.Parameters.Add("@pPerformLevel2", SqlDbType.Int).Value = PerformLevel2 == 0 ? System.Convert.DBNull : PerformLevel2;
               
                // execute the command
                rdr = cmd.ExecuteReader();

                // iterate through results, printing each to console
                while (rdr.Read())
                {
                    try
                    {
                        ldReturnData.ROW_COUNT = Int32.Parse
                            (rdr["ROW_COUNT"].ToString());

                    }
                    catch (FormatException e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
            }
            finally
            {
                if (dbMSE != null)
                {
                    dbMSE.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }

            return ldReturnData;
        }
        public List<UserRole> SelectUserRole(string User)
        {
            SqlConnection dbMSE = null;
            SqlDataReader rdr = null;
            List<UserRole> ldReturnData = new List<UserRole>();
            try
            {

                // obtain connection string from application config file
                var connectionString = ConfigurationManager.ConnectionStrings[dbConnectionsTring].ConnectionString;

                // create and open a connection object
                dbMSE = new SqlConnection(connectionString);
                dbMSE.Open();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("sp_SelectUserRole", dbMSE);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add("@User", SqlDbType.VarChar).Value = User;
                // execute the command
                rdr = cmd.ExecuteReader();

                // iterate through results, printing each to console
                while (rdr.Read())
                {
                    UserRole record = new UserRole();
                    try
                    {
                        record.Role = rdr["Role"].ToString();
                        ldReturnData.Add(record);

                    }
                    catch (FormatException e)
                    {
                        Console.WriteLine(e.Message);
                    }

                }
            }
            finally
            {
                if (dbMSE != null)
                {
                    dbMSE.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }

            return ldReturnData;
        }
        public List<ScheduleDetails> SelectScheduleDetails(string AssemblyLineAbbrv, string MfgGate, string Report)
        {
            SqlConnection dbMSE = null;
            SqlDataReader rdr = null;
            List<ScheduleDetails> ldReturnData = new List<ScheduleDetails>();
            try
            {

                // obtain connection string from application config file
                var connectionString = ConfigurationManager.ConnectionStrings[dbConnectionsTring].ConnectionString;

                // create and open a connection object
                dbMSE = new SqlConnection(connectionString);
                dbMSE.Open();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("sp_SelectScheduleRecord", dbMSE);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add("@pAssemblyLineAbbrv", SqlDbType.VarChar).Value = AssemblyLineAbbrv;
                cmd.Parameters.Add("@pMfgGate", SqlDbType.VarChar).Value = MfgGate;
                cmd.Parameters.Add("@pReport", SqlDbType.VarChar).Value = Report;
                // execute the command
                rdr = cmd.ExecuteReader();

                // iterate through results, printing each to console
                while (rdr.Read())
                {
                    ScheduleDetails record = new ScheduleDetails();
                    try
                    {
                        record.Q2C = rdr["Q2C"].ToString();
                        record.JobName = rdr["JobName"].ToString();
                        record.TargetDate = rdr["TargetDate"].ToString();
                        record.ShipDate = rdr["ShipDate"].ToString();
                        record.Released = rdr["Released"].ToString();
                        record.Queued = rdr["Queued"].ToString();
                        record.ActualStart = rdr["ActualStart"].ToString();
                        record.SER = rdr["SER"].ToString();
                        record.RunTime = rdr["RunTime"].ToString();
                        record.ElecClpx = rdr["ElecClpx"].ToString();
                        record.MechClpx = rdr["MechClpx"].ToString();
                        record.ScheduledStart = rdr["ScheduledStart"].ToString();
                        record.ScheduledFinish = rdr["ScheduledFinish"].ToString();
                        record.ActualFinish = rdr["ActualFinish"].ToString();
                        ldReturnData.Add(record);

                    }
                    catch (FormatException e)
                    {
                        Console.WriteLine(e.Message);
                    }

                }
            }
            finally
            {
                if (dbMSE != null)
                {
                    dbMSE.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }

            return ldReturnData;
        }
        public struct MSEConfigurationSPResultRecord
        {
            public int ROW_COUNT { get; set; }
        }
        public struct UserRole
        {
            public String Role { get; set; }
        }
        public struct ScheduleDetails
        {
            public string Q2C { get; set; }
            public string JobName { get; set; }
            public string TargetDate { get; set; }
            public string ShipDate { get; set; }
            public string Released { get; set; }
            public string Queued { get; set; }
            public string ActualStart { get; set; }
            public string SER { get; set; }
            public string RunTime { get; set; }
            public string ElecClpx { get; set; }
            public string MechClpx { get; set; }
            public string ScheduledStart { get; set; }
            public string ScheduledFinish { get; set; }
            public string ActualFinish { get; set; }
        }
    }
    
}
