using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Xml.Linq;

namespace ExagoSchedulerQueue {
    public class SchedulerQueue {

        private static string CallingProcess = ConfigurationManager.AppSettings["CallingProcess"];
        private static string ConfigDirectory = ConfigurationManager.AppSettings["ConfigDirectory"];
        private static string ConfigDirectory_PROD = ConfigurationManager.AppSettings["ConfigDirectory_PROD"];
        private static string DatabaseConnectionString = ConfigurationManager.AppSettings["DatabaseConnectionString"];
        private static string DpoConnectionStringTemplate = ConfigurationManager.AppSettings["DpoConnectionStringTemplate"];
        private static string DpoDbServerIp = ConfigurationManager.AppSettings["DpoDbServerIp"];
        private static string DpoDbServerIp_PROD = ConfigurationManager.AppSettings["DpoDbServerIp_PROD"];
        private static string DpoDbPassword = ConfigurationManager.AppSettings["DpoDbPassword"];
        private static string DpoDbPassword_PROD = ConfigurationManager.AppSettings["DpoDbPassword_PROD"];
        private static string EnvDir = Environment.GetEnvironmentVariable("EXAGO_ENVDIR");
        private static string LogPath = ConfigurationManager.AppSettings["LogPath"];
        private static string HostName = System.Net.Dns.GetHostName();
        private static string LogFn = String.Format(@"{0}\SchedulerQueue_" + CallingProcess + "_" +  HostName + ".txt", LogPath);
        private static object LogLockObject = new object();

        private static void LogHeader(string method) {
            Log(method + " from " + CallingProcess + " on " + EnvDir);
        }

        // ---------------- EXAGO SCHEDULER QUEUE METHODS (begin) ------------------------

        // Called from the Exago UI to populate the jobs in the Schedule Manager 
        public static string[] GetJobList(string viewLevel, string companyId, string userId) {
            LogHeader("GetJobList(\"" + viewLevel + "\",\"" + companyId + "\",\"" + userId + "\")"); 
            List<string> jobXmlList = new List<string>();
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.GetJobList",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ViewLevel", viewLevel);
                    cmd.Parameters.AddWithValue("@CompanyId", companyId);
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    var dr = cmd.ExecuteReader();
                    while (dr.Read()) {
                        XElement stored = XDocument.Parse((string)dr["Xml"]).Element("webreports");
                        stored.Element("config").Remove();
                        stored.Element("report").Remove();
                        ReconstructJobInfo(stored, dr);
                        var xml = stored.ToString();
                        jobXmlList.Add(xml);
                    }
                    dr.Close();
                }
                cn.Close();
            }
            Log("GetJobList() completed");
            return jobXmlList.ToArray();
        }

        // Called from the Exago UI Schedule Manager to get the full job XML data for a job.
        public static string GetJobData(string jobId) {
            LogHeader("GetJobData(\"" + jobId + "\")");
            var jobXml = "";
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.GetJobData",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@JobId", jobId);
                    var dr = cmd.ExecuteReader();
                    while (dr.Read()) {
                        jobXml = ReconstructJob(dr);
                    }
                    dr.Close();
                }
                cn.Close();
            }
            Log("GetJobData() completed");
            return jobXml;
        }

        // Called from the Exago UI when a report is deleted.
        public static void DeleteReport(string reportId) {
            LogHeader("DeleteReport(\"" + reportId + "\")");
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.DeleteReport",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ReportId", reportId);
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            Log("DeleteReport() completed");
        }

        // Called from the Exago UI when a report is renamed.
        public static void RenameReport(string reportId, string reportName) {
            LogHeader("RenameReport(\"" + reportId + "\",\"" + reportName + "\")");
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.RenameReport",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ReportId", reportId);
                    cmd.Parameters.AddWithValue("@ReportName", reportName);
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            Log("RenameReport() completed");
        }

        // Called from the Exago UI when a report is updated.
        public static void UpdateReport(string reportId, string reportXml) {
            LogHeader("UpdateReport(\"" + reportId + "\",\"" + reportXml.Substring(0,20) + "\")");
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.UpdateReport",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ReportId", reportId);
                    cmd.Parameters.AddWithValue("@ReportXml", reportXml);
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            Log("UpdateReport() completed");
        }

        // Called from the Exago UI Scheduler Manager in response to a click on the Flush button.
        public static void Flush(string viewLevel, string companyId, string userId) {
            LogHeader("Flush(\"" + viewLevel + "\",\"" + companyId + "\",\"" + userId + "\")");
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.Flush",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ViewLevel", viewLevel);
                    cmd.Parameters.AddWithValue("@CompanyId", companyId);
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            Log("FlushReport() completed");
        }

        // Called from scheduler services to indicate when a specific service starts.
        public static void Start(string serviceName) {
            LogHeader("Start(\"" + serviceName +  "\")");
            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open(); 
                using (SqlCommand cmd = new SqlCommand("dbo.Start",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceName", serviceName);
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            Log("Start() completed");
        }

        // Called from the scheduler services to return the next job to execute.
        public static string GetNextExecuteJob(string serviceName) {
            LogHeader("GetNextExecuteJob(\"" + serviceName + "\")");
            string jobXml = null;
            string jobId = "";

            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("GetNextExecuteJob", cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceName", serviceName);
                    var dr = cmd.ExecuteReader();
                    if (dr.Read()) {
                        jobXml = ReconstructJob(dr);
                        jobId = ((Guid)dr["JobId"]).ToString();
                    }
                    
                    dr.Close();
                }
                cn.Close();
            }
            Log("GetNextExecuteJob() completed: "+jobId);
            return jobXml; 
        }

        // Called from both the scheduler services and the Exago UI to save the job. 
        public static string SaveJob(string jobXml) {

            //System.Diagnostics.Debugger.Launch();

            LogHeader("SaveJob(\"" + jobXml.Substring(0,20) + "\")");

            // there are more updates than inserts, so we'll try to update first
            XElement stored = XDocument.Parse(jobXml).Element("webreports");
            XElement storedSchedule = stored.Element("schedule");
            XElement storedJobInfo = stored.Element("jobinfo");
            XElement storedConfig = stored.Element("config");

            if (!storedSchedule.Elements("email_to").Any() 
                && !storedSchedule.Elements("email_cc").Any() 
                && !storedSchedule.Elements("email_bcc").Any())
            {
                // an absence of email_to elements will happen when using SmartAnalytics
                // to update an Easy Report schedule.  The following was added to make
                // sure the stored information isn't lost.  DKK
                // UPDATE - a filter should prevent this, but this will stay to be safe
                string jobId = storedJobInfo.Element("JobId").Value;
                string savedXml = GetJobData(jobId);
                if (savedXml != "") {
                    XElement saved = XDocument.Parse(savedXml).Element("webreports");
                    XElement savedSchedule = saved.Element("schedule");
                    storedSchedule.Elements("email_to").Remove();
                    storedSchedule.Elements("email_cc").Remove();
                    storedSchedule.Elements("email_bcc").Remove();
                    storedSchedule.Elements("email_subject").Remove();
                    storedSchedule.Elements("email_body").Remove();
                    storedSchedule.Add(savedSchedule.Elements("email_to"));
                    storedSchedule.Add(savedSchedule.Elements("email_cc"));
                    storedSchedule.Add(savedSchedule.Elements("email_bcc"));
                    storedSchedule.Add(savedSchedule.Elements("email_subject"));
                    storedSchedule.Add(savedSchedule.Elements("email_body"));
                }
            }


            //File.AppendAllText(LogPath + @"\" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".xml", stored.ToString());

            // clear large DPO data definitions that are added in dynamically  
            var entities = from entity in storedConfig.Elements("entity") select entity;
            foreach (var entity in entities) {
                if (entity.Element("entity_name").Value == "Donor"
                    || entity.Element("entity_name").Value == "Gift"
                    || entity.Element("entity_name").Value == "Other"
                    || entity.Element("entity_name").Value == "Contact"
                    || entity.Element("entity_name").Value == "Address"
                ) {
                    entity.Element("sql_stmt").Value = "";
                    entity.Elements("column_metadata").Remove();
                }
            }

            jobXml = stored.ToString(); // get pruned results

            string companyId = "";
            string userId = "";
            bool is_dpo = false;
            string ScheduleEnvironment = EnvDir;

            //ConfigParameter("companyId")
            //var cid = storedConfig.Elements("parameter").Where(x => x.Element("id").Value == "companyId").Single().Element("value").Value; 
            var parms = from parm in storedConfig.Elements("parameter") select parm;
            foreach (var parm in parms) {
                if (parm.Element("id").Value == "companyId") {
                    companyId = parm.Element("value").Value;  // companyId
                }
                if (parm.Element("id").Value == "userId") {
                    userId = parm.Element("value").Value;  // userId
                }
                if (parm.Element("id").Value == "DonorSelectionFilter") {
                    is_dpo = true;
                }
                if (parm.Element("id").Value == "ScheduleEnvironment") {
                    ScheduleEnvironment = parm.Element("value").Value; // _DEV, _QA, _PROD
                }
            }

            string DpoprodConnectionString = "";
            if (ScheduleEnvironment == "_PROD") {
                DpoprodConnectionString = DpoConnectionStringTemplate
                .Replace("__SERVER__", DpoDbServerIp_PROD)
                .Replace("__DATABASE__", "dpoprod")
                .Replace("__PASSWORD__", DpoDbPassword_PROD);
            }
            else {
                DpoprodConnectionString = DpoConnectionStringTemplate
                .Replace("__SERVER__", DpoDbServerIp)
                .Replace("__DATABASE__", "dpoprod")
                .Replace("__PASSWORD__", DpoDbPassword);
            }

            string db_server_ip = "";

            if (is_dpo && companyId != "" && userId != "") {
                using (SqlConnection cn = new SqlConnection(DpoprodConnectionString)) {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("", cn)) {
                        cmd.CommandText 
                            = "SELECT db_server_ip FROM dpo.dpcompany c "
                            + "WHERE c.company_db = @company_db ";
                        cmd.Parameters.AddWithValue("@company_db", companyId);
                        var dr = cmd.ExecuteReader();
                        if (dr.Read()) {
                            db_server_ip = (string)dr["db_server_ip"];
                        }
                        dr.Close();
                    }
                    cn.Close();
                }
            }

            using (SqlConnection cn = new SqlConnection(DatabaseConnectionString)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.SaveJob",cn)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@JobId", storedJobInfo.Element("JobId").Value);
                    cmd.Parameters.AddWithValue("@NextExecuteDate", storedJobInfo.Element("NextExecuteDate").Value);
                    cmd.Parameters.AddWithValue("@LastExecuteDate", storedJobInfo.Element("LastExecuteDate").Value);
                    cmd.Parameters.AddWithValue("@Status", storedJobInfo.Element("Status").Value);
                    cmd.Parameters.AddWithValue("@DbServerIp", db_server_ip);
                    cmd.Parameters.AddWithValue("@CompanyId", storedSchedule.Element("company_id").Value);
                    cmd.Parameters.AddWithValue("@UserId", storedSchedule.Element("user_id").Value);
                    cmd.Parameters.AddWithValue("@ScheduleName", storedSchedule.Element("schedule_name").Value);
                    cmd.Parameters.AddWithValue("@ReportName", storedSchedule.Element("report_name").Value);
                    cmd.Parameters.AddWithValue("@RecurrencePattern", storedSchedule.Element("recurrence_pattern").Value);
                    var scheduleDay = "";
                    if (storedSchedule.Elements("weekly_sunday").Any() 
                      && storedSchedule.Element("weekly_sunday").Value == "True") {
                        scheduleDay = "Sunday";
                    }
                    else if (storedSchedule.Elements("weekly_monday").Any() 
                      && storedSchedule.Element("weekly_monday").Value == "True") {
                        scheduleDay = "Monday";
                    }
                    else if (storedSchedule.Elements("weekly_tuesday").Any()
                      && storedSchedule.Element("weekly_tuesday").Value == "True") {
                        scheduleDay = "Tuesday";
                    }
                    else if (storedSchedule.Elements("weekly_wednesday").Any()
                      && storedSchedule.Element("weekly_wednesday").Value == "True") {
                        scheduleDay = "Wednesday";
                    }
                    else if (storedSchedule.Elements("weekly_thursday").Any()
                      && storedSchedule.Element("weekly_thursday").Value == "True") {
                        scheduleDay = "Thursday";
                    }
                    else if (storedSchedule.Elements("weekly_friday").Any()
                      && storedSchedule.Element("weekly_friday").Value == "True") {
                        scheduleDay = "Friday";
                    }
                    else if (storedSchedule.Elements("weekly_saturday").Any()
                      && storedSchedule.Element("weekly_saturday").Value == "True") {
                        scheduleDay = "Saturday";
                    }
                    cmd.Parameters.AddWithValue("@ScheduleDay", scheduleDay);
                    if (storedSchedule.Element("immediate").Value != "True") {
                        cmd.Parameters.AddWithValue("@ScheduleTime", storedSchedule.Element("schedule_time").Value);
                    }
                    else {
                        cmd.Parameters.AddWithValue("@ScheduleTime", "");
                    }
                    string[] email_to_arr = storedSchedule.Elements("email_to").Select(h => (string)h).ToArray();
                    string email_to_string = string.Join(",", email_to_arr);
                    cmd.Parameters.AddWithValue("@EmailTo", email_to_string);
                    cmd.Parameters.AddWithValue("@EmailSubject", storedSchedule.Element("email_subject").Value);
                    cmd.Parameters.AddWithValue("@EmailBody", storedSchedule.Element("email_body").Value);
                    cmd.Parameters.AddWithValue("@ReportId", stored.Element("report").Element("main").Element("id").Value);
                    cmd.Parameters.AddWithValue("@ReportXml", stored.Element("report").ToString());
                    cmd.Parameters.AddWithValue("@Xml", jobXml);
                    cmd.Parameters.AddWithValue("@Environment", ScheduleEnvironment);
                    string outputMode = stored.Element("report").Element("main").Element("output_mode").Value;
                    string attachmentFormat;
                    if (outputMode == "1") attachmentFormat = "Excel";
                    else if (outputMode == "2") attachmentFormat = "PDF";
                    else if (outputMode == "3") attachmentFormat = "RTF";
                    else if (outputMode == "4") attachmentFormat = "CSV";
                    else attachmentFormat = "?";
                    cmd.Parameters.AddWithValue("@AttachmentFormat", attachmentFormat);
                    cmd.Parameters.AddWithValue("@AttachmentPassword", storedSchedule.Element("password").Value.Trim());
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            Log("SaveJob() completed: "
                + storedJobInfo.Element("JobId").Value + " "
                + storedJobInfo.Element("Status").Value);

            return storedJobInfo.Element("JobId").Value;
        }

        // ---------------- EXAGO SCHEDULER QUEUE METHODS (end) ------------------------
        private static void Log(string info) {
            /*
            return;
            */
            lock (LogLockObject) {
                //try {
                    File.AppendAllText(LogFn, DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss.fff "));
                    File.AppendAllText(LogFn, info);
                    File.AppendAllText(LogFn, Environment.NewLine);
                //}
                //catch { }
            }
        }
        private static void ReconstructJobInfo(XElement stored, SqlDataReader job) {
            //System.Diagnostics.Debugger.Launch();
            Log(">>> ReconstructJobInfo");
            XElement storedSchedule = stored.Element("schedule");
            XElement storedJobInfo = stored.Element("jobinfo");

            storedJobInfo.Element("JobId").Value = ((Guid)job["JobId"]).ToString();
            storedJobInfo.Element("NextExecuteDate").Value = ((DateTime)job["NextExecuteDate"]).ToString("s");
            storedJobInfo.Element("Status").Value = job["Status"].ToString();
            storedSchedule.Element("report_name").Value = (string)job["ReportName"];
            storedSchedule.Element("schedule_name").Value = (string)job["ScheduleName"];
            storedSchedule.Element("company_id").Value = (string)job["CompanyId"];
            storedSchedule.Element("user_id").Value = (string)job["UserId"];
            storedSchedule.Element("recurrence_pattern").Value = (string)job["RecurrencePattern"];
            var ScheduleDay = (string)job["ScheduleDay"];
            if (ScheduleDay == "Sunday") {
                storedSchedule.Element("weekly_sunday").Value = "True";
            }
            else if (ScheduleDay == "Monday") {
                storedSchedule.Element("weekly_monday").Value = "True";
            }
            else if (ScheduleDay == "Tuesday") {
                storedSchedule.Element("weekly_tuesday").Value = "True";
            }
            else if (ScheduleDay == "Wednesday") {
                storedSchedule.Element("weekly_wednesday").Value = "True";
            }
            else if (ScheduleDay == "Thursday") {
                storedSchedule.Element("weekly_thursday").Value = "True";
            }
            else if (ScheduleDay == "Friday") {
                storedSchedule.Element("weekly_friday").Value = "True";
            }
            else if (ScheduleDay == "Saturday") {
                storedSchedule.Element("weekly_saturday").Value = "True";
            }
            if (storedSchedule.Elements("schedule_time").Any()) { // immediate schedules don't have this field
                storedSchedule.Element("schedule_time").Value = ((TimeSpan)job["ScheduleTime"]).ToString(@"h\:m");
            }

            if (storedSchedule.Elements("email_to").Any()) {
                storedSchedule.Elements("email_to").Remove();
            }
            
            char[] delimiters = { ',', ';' };
            var email_to_arr = job["EmailTo"].ToString().Split(delimiters);
            foreach (string email_to in email_to_arr) {
                var email_to_xml = new XElement("email_to");
                email_to_xml.Value = email_to;
                storedSchedule.Add(email_to_xml);
            }

            storedSchedule.Element("email_subject").Value = (string)job["EmailSubject"];
            storedSchedule.Element("email_body").Value = (string)job["EmailBody"];

            storedSchedule.Element("password").Value = (string)job["AttachmentPassword"];

            Log("<<< ReconstructJobInfo");
        }
        private static string ReconstructJob(SqlDataReader job) {
            //Log(">>> ReconstructJob()");
            XElement stored = XDocument.Parse((string)job["Xml"]).Element("webreports");
            XElement storedReport = XDocument.Parse((string)job["ReportXml"]).Element("report");
            XElement storedConfig = stored.Element("config");
            XElement storedSchedule = stored.Element("schedule");
            XElement storedJobInfo = stored.Element("jobinfo");

            stored.Element("report").ReplaceWith(storedReport);

            ReconstructJobInfo(stored, job); 

            if (storedJobInfo.Element("Type").Value != "WebReport") {
                // not a web report 
                return stored.ToString();
            }

            string attachmentFormat = (string)job["AttachmentFormat"];
            string outputMode;
            if (attachmentFormat == "Excel") outputMode = "1";
            else if (attachmentFormat == "PDF") outputMode = "2";
            else if (attachmentFormat == "RTF") outputMode = "3";
            else if (attachmentFormat == "CSV") outputMode = "4";
            else outputMode = "?";
            stored.Element("report").Element("main").Element("output_mode").Value = outputMode;

            string companyId = "";
            string userId = "";
            bool is_dpo = false;

            companyId = (string)job["CompanyId"];
            userId = (string)job["UserId"];

            XElement target = new XElement("webreports");

            if ((string)job["Environment"] == "_PROD" && EnvDir == "_QA") {
                // we need to fix the ServerEvent Reference in the config to run locally.
                var datasources = from datasource in storedConfig.Elements("datasource") select datasource;
                foreach (var datasource in datasources) {
                    if (datasource.Element("name").Value == "ExagoServerEvents") {
                        datasource.Element("dataconnstr").Value =
@"Assembly=D:\Program Files\ExagoServerEvents\bin\Release\ExagoServerEvents.dll;class=ExagoServerEvents.Methods";

                    }
                }
            }
            if ((string)job["Environment"] == "_PROD" && EnvDir == "__dkillough") {
                // we need to fix the ServerEvent Reference in the config to run locally.
                var datasources = from datasource in storedConfig.Elements("datasource") select datasource;
                foreach (var datasource in datasources) {
                    if (datasource.Element("name").Value == "ExagoServerEvents") {
                        datasource.Element("dataconnstr").Value =
@"Assembly=C:\Local\TFS\DPO\Development\Dave\Main\Exago\ExagoServerEvents\bin\Debug\ExagoServerEvents.dll;class=ExagoServerEvents.Methods";
 
                    }
                }
            }

            var parms = from parm in storedConfig.Elements("parameter") select parm;
            foreach (var parm in parms) {
                if (parm.Element("id").Value == "companyId") {
                    parm.Element("value").Value = companyId;  // companyId
                }
                if (parm.Element("id").Value == "userId") {
                    parm.Element("value").Value = userId;  // userId
                }
                if (parm.Element("id").Value == "DonorSelectionFilter") {
                    is_dpo = true;
                }
            }
            if (companyId == "" || userId == "") {
                // missing company/user
                return stored.ToString(); 
            }

            if (is_dpo) {
                // consider caching....  

                XElement defaultConfig;

                if ((string)job["Environment"] == "_PROD" && EnvDir != "_PROD") { // test against PROD databases
                    defaultConfig = XDocument.Load(ConfigDirectory_PROD + @"\WebReports_DPO_Next.xml").Element("webreports");
                }
                else {
                    defaultConfig = XDocument.Load(ConfigDirectory + @"\WebReports_DPO.xml").Element("webreports");
                }
                var reportpath = defaultConfig.Element("general").Element("reportpath");
                reportpath.Value = reportpath.Value.Replace(companyId, "__defaults"); 
                defaultConfig.Name = "config"; // so we can embed in the QueueApiJob object 
                XElement targetConfig = new XElement("config");

                targetConfig.Add(defaultConfig.Element("general")); // default configuration
                targetConfig.Add(storedConfig.Elements("datasource")); // stored datasources
                targetConfig.Add(storedConfig.Elements("parameter")); // stored parameters  
                targetConfig.Add(storedConfig.Elements("entity")); // entities (need current schema)  consider eliminating non-used entities
                targetConfig.Add(storedConfig.Elements("join")); // joins (as stored)
                targetConfig.Add(storedConfig.Elements("role")); // roles (as stored)
                targetConfig.Add(defaultConfig.Elements("function")); // functions
                targetConfig.Add(defaultConfig.Elements("serverevent")); // current server events  

                string DpoprodConnectionString;
                if ((string)job["Environment"] == "_PROD" && EnvDir != "_PROD") { // test against PROD databases
                    DpoprodConnectionString = DpoConnectionStringTemplate
                        .Replace("__SERVER__", DpoDbServerIp_PROD)
                        .Replace("__DATABASE__", "dpoprod")
                        .Replace("__PASSWORD__", DpoDbPassword_PROD);
                }
                else {
                    DpoprodConnectionString = DpoConnectionStringTemplate
                        .Replace("__SERVER__", DpoDbServerIp)
                        .Replace("__DATABASE__", "dpoprod")
                        .Replace("__PASSWORD__", DpoDbPassword);
                }
                string db_server_ip = "";
                string sqlQuery
                    = "SELECT db_server_ip FROM dpo.dpcompany c "
                    + "WHERE c.company_db = @company_db ";

                using (SqlConnection cn = new SqlConnection(DpoprodConnectionString)) {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, cn)) {
                        cmd.Parameters.AddWithValue("@company_db", companyId);
                        var dr = cmd.ExecuteReader();
                        if (dr.Read()) {
                            db_server_ip = (string)dr["db_server_ip"];
                        }
                        dr.Close();
                    }
                    cn.Close();
                }
                if (db_server_ip == "") {
                    return stored.ToString();
                }

                string ClientConnectionString;
                if ((string)job["Environment"] == "_PROD" && EnvDir != "_PROD") { // test against PROD databases
                    ClientConnectionString = DpoConnectionStringTemplate
                        .Replace("__SERVER__", db_server_ip)
                        .Replace("__DATABASE__", companyId)
                        .Replace("__PASSWORD__", DpoDbPassword_PROD);

                    var datasources = from datasource in targetConfig.Elements("datasource") select datasource;
                    foreach (var datasource in datasources) {
                        if (datasource.Element("name").Value == "dpoclient") {
                            datasource.Element("dataconnstr").Value = ClientConnectionString; 
                        }
                    }

                }
                else {
                    ClientConnectionString = DpoConnectionStringTemplate
                        .Replace("__SERVER__", db_server_ip)
                        .Replace("__DATABASE__", companyId)
                        .Replace("__PASSWORD__", DpoDbPassword);
                }

                using (SqlConnection cn = new SqlConnection(ClientConnectionString)) {
                    cn.Open();
                    var entities = from entity in targetConfig.Elements("entity") select entity;
                    foreach (var entity in entities) {
                        if (entity.Element("entity_name").Value == "Donor") {
                            RefreshEntity(entity, cn, "Donor", "dp", "DONOR_ID");
                        }
                        else if (entity.Element("entity_name").Value == "Gift") {
                            RefreshEntity(entity, cn, "Gift", "dpgift", "GIFT_ID");
                        }
                        else if (entity.Element("entity_name").Value == "Other") {
                            RefreshEntity(entity, cn, "Other", "dpotherinfo", "OTHER_ID");
                        }
                        else if (entity.Element("entity_name").Value == "Contact") {
                            RefreshEntity(entity, cn, "Contact", "dpcontact", "CONTACT_ID");
                        }
                        else if (entity.Element("entity_name").Value == "Address") {
                            RefreshEntity(entity, cn, "Address", "dpaddress", "ADDRESS_ID");
                        }
                    }
                    cn.Close();
                }
                targetConfig.Element("general").Element("dateformat").Value
                    = storedConfig.Element("general").Element("dateformat").Value;

                targetConfig.Element("general").Element("currencysymbol").Value
                    = storedConfig.Element("general").Element("currencysymbol").Value;

                targetConfig.Element("general").Element("servertimezoneoffset").Value
                    = storedConfig.Element("general").Element("servertimezoneoffset").Value;
                if ((string)job["Environment"] == "_PROD" && EnvDir != "_PROD") { // remote test - use __defaults
                    targetConfig.Element("general").Element("reportpath").Value += @"\__defaults";
                }
                else {
                    targetConfig.Element("general").Element("reportpath").Value += @"\" + companyId;
                }

                if (storedConfig.Elements("api").Any()) {
                    targetConfig.Add(storedConfig.Element("api"));
                }
                if (storedConfig.Elements("calculated").Any()) {
                    targetConfig.Add(storedConfig.Element("calculated"));
                }

                target.Add(targetConfig);
                target.Add(storedReport);
                target.Add(storedSchedule);
                target.Add(storedJobInfo);

                return target.ToString();

            }
            //Log("<<< ReconstructJob()");
            return stored.ToString();
        }
        private static void RefreshEntity(XElement entity, SqlConnection cn, string screen, string table, string key) {
            
            entity.Element("category").Value = "Screens";
            var sqlQuery = "dp_exago_entity";
            using (SqlCommand cmd = new SqlCommand(sqlQuery, cn)) {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@screen", screen);
                cmd.Parameters.AddWithValue("@table", table);
                cmd.Parameters.AddWithValue("@key", key);
                var dr = cmd.ExecuteReader();
                while (dr.Read()) {
                    entity.Element("sql_stmt").Value = dr["SqlStmt"].ToString();
                }
                dr.NextResult();
                entity.Elements("column_metadata").Remove();
                while (dr.Read()) {
                    entity.Add(
                        new XElement("column_metadata",
                            new XElement("col_name", (string)dr["name"]),
                            new XElement("col_type", (string)dr["data_type"]),
                            new XElement("col_alias", ((string)dr["prompt"]).Trim())
                        )
                    );
                }
                dr.Close();
            }
        }


    }
}