UCCXtoSQL

LogFile.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Configuration;

namespace UCCXtoSQL
{
    public static class LogFile
    {
        public static void write(string logMessage)
        {
            string message = string.Empty;
            string logFileLocation = @"C:\debug\UCCXtoSQL.log";
            StreamWriter logWriter;

            message = string.Format("{0}: {1}", DateTime.Now, logMessage);

            if (!File.Exists(logFileLocation))
            {
                logWriter = new StreamWriter(logFileLocation);
            }
            else
            {
                logWriter = File.AppendText(logFileLocation);
            }

            logWriter.WriteLine(message);

            logWriter.Close();
        }   
    }

    

}

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace UCCXtoSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            getData();
        }

        static void getData()
        {
            string connString = @"Data Source=uccx-pri\crssql;Initial Catalog=db_cra;Integrated Security=SSPI;";
            string sql2k5ConnString = @"Data Source=sql2k5;Initial Catalog=db_cra;User Id=sqlsupport;Password=blahblahblah;";
            string procedure = @"sp_csq_activity";
            int id = 0;
            //string queueName = @"|CSQ-SHG01";

            SqlConnection conn = new SqlConnection(connString);
            SqlConnection sql2k5conn = new SqlConnection(sql2k5ConnString);

            DataTable csq = getCSQTable(sql2k5conn);

            foreach (DataRow row in csq.Rows)
            {
                id++;
                string name = row["CSQName"].ToString();
                string open = row["CSQOpen"].ToString();
                string close = row["CSQClose"].ToString();
                
                string format = "yyyy-MM-dd ";
                DateTime today = DateTime.Now.Date.AddDays(-1);

                Console.WriteLine(id + " " + name);
                LogFile.write(id + " " + name);
 
                string paramstart = (today.ToString(format) + open);
                string paramend = (today.ToString(format) + close);

                Console.WriteLine("Queue open: " + paramstart);
                Console.WriteLine("Queue close: " + paramend);

                //var Open = TimeSpan.Parse(row["CSQOpen"].ToString());
                //string statsDate = DateTime.Now.ToShortDateString(); 
                //string newDateTime = statsDate + Open;
                //Console.WriteLine("Converted " + newDateTime);

                csqActivity(paramstart, paramend, procedure, name, conn, sql2k5conn);
            }
        }

        private static void csqActivity(string pstart, string pend, string procedure, string queueName, SqlConnection conn, SqlConnection sql2k5conn)
        {
            try
            {
                queueName = @"|" + queueName;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(procedure, conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                //da.SelectCommand.Parameters.Add(new SqlParameter("@starttime", "2012-11-08 08:30:00"));
                //da.SelectCommand.Parameters.Add(new SqlParameter("@endtime", "2012-11-08 17:15:00"));
                da.SelectCommand.Parameters.Add(new SqlParameter("@starttime", pstart));
                da.SelectCommand.Parameters.Add(new SqlParameter("@endtime", pend));
                da.SelectCommand.Parameters.Add(new SqlParameter("@csqlist", queueName));

                DataSet ds = new DataSet();
                da.Fill(ds, "result_name");

                DataTable dt = ds.Tables["result_name"];

                foreach (DataRow row in dt.Rows)
                {
                    //Console.WriteLine(row["CSQ_Name"]);
                    Console.WriteLine("Queue: {0} - Presented: {1}", row["CSQ_Name"], row["Calls_Presented"]);
                    LogFile.write("Queue: " + row["CSQ_Name"]);
                    LogFile.write("Presented: " + row["Calls_Presented"]);
                    InsertRecord(sql2k5conn, row, pstart, pend, queueName);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                LogFile.write("Error: " + e);
            }
            finally
            {
                Console.WriteLine("Done");
                conn.Close();
            }
        }

        private static void InsertRecord(SqlConnection sql2k5conn, DataRow row, string start, string end, string queue)
        {
            try
            {
                DateTime startdate = Convert.ToDateTime(start);
                DateTime endDate = Convert.ToDateTime(end);

                sql2k5conn.Open();
                SqlCommand sql2k5comm = new SqlCommand("insert_csq_activity", sql2k5conn);
                sql2k5comm.CommandTimeout = 0;
                sql2k5comm.CommandType = CommandType.StoredProcedure;
                //sql2k5comm.Parameters.Add(new SqlParameter(/*PARAMNAME*/,/*PARAM*/);
                sql2k5comm.Parameters.Add(new SqlParameter("@CSQ_Name", row["CSQ_Name"]));
                //sql2k5comm.Parameters.Add(new SqlParameter("@CSQ_Name", queue));
                sql2k5comm.Parameters.Add(new SqlParameter("@Call_Skills", row["Call_Skills"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Calls_Presented", row["Calls_Presented"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Avg_Queue_Time", row["Avg_Queue_Time"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Max_Queue_Time", row["Max_Queue_Time"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Calls_Handled", row["Calls_Handled"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Avg_Speed_Answer", row["Avg_Speed_Answer"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Avg_Handle_Time", row["Avg_Handle_Time"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Max_Handle_Time", row["Max_Handle_Time"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Calls_Abandoned", row["Calls_Abandoned"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Avg_Time_Abandon", row["Avg_Time_Abandon"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Max_Time_Abandon", row["Max_Time_Abandon"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Avg_Calls_Abandoned", row["Avg_Calls_Abandoned"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Max_Calls_Abandoned", row["Max_Calls_Abandoned"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Calls_Dequeued", row["Calls_Dequeued"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Avg_Time_Dequeue", row["Avg_Time_Dequeue"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Max_Time_Dequeue", row["Max_Time_Dequeue"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@Calls_Handled_by_Other", row["Calls_Handled_by_Other"]));
                sql2k5comm.Parameters.Add(new SqlParameter("@CSQ_StartDateTime", startdate));
                sql2k5comm.Parameters.Add(new SqlParameter("@CSQ_EndDateTime", endDate));
                sql2k5comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                LogFile.write("Error: " + e);
            }
            finally
            {
                sql2k5conn.Close();
            }
        }

        static DataTable getCSQTable(SqlConnection connection)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand("get_csqnames", connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet ds = new DataSet();
            da.Fill(ds, "csqTable");

            DataTable dt = ds.Tables["csqTable"];
            return dt;
        }

       
    }
}