public static List<ProjectVM> JsonFileDeserializer()
        {
            try
            {

                string jsonFilePath = System.Configuration.ConfigurationManager.AppSettings["jsonDataFilePath"];

                StreamReader myFile = new StreamReader(jsonFilePath);
                string myString = myFile.ReadToEnd();

                myFile.Close();

                var items = JsonConvert.DeserializeObject<List<ProductsApp.Models.Json.RootObject>>(myString);
                ProjectVM proVM;
                List<ProjectVM> list = new List<ProjectVM>();
                foreach (ProductsApp.Models.Json.RootObject r in items)
                {
                    proVM = new ProjectVM();
                    proVM.id = r.id;
                    proVM.name = r.name;
                    proVM.exit_date = r.exit_date;
                    proVM.project_cost = r.project_cost;
                    proVM.burndown = r.burndown;
                    proVM.producer_name = r.producer_name;
                    proVM.hosting_platform = r.hosting_platform;
                    proVM.status = r.status;
                    proVM.progress = r.progress;
                    //ProductsApp.Models.Json.Parent p = new Models.Json.Parent();
                    //proVM.parent = new Models.Parent();
                    //proVM.parent.id = p.id;
                    //proVM.parent.name = p.name;
                    //proVM.parent

                    proVM.live_url = r.live_url;
                    proVM.development_start_date = r.development_start_date;
                    proVM.launch_date = r.launch_date;
                    proVM.TotalTimeSpent = r.TotalTimeSpent;
                    proVM.EstimatedTime = r.EstimatedTime;
                    proVM.market = r.market;

                    list.Add(proVM);
                }

                return list;

            }
            catch
            {

                return null;
            }
        }
        public string putXMLData(string destinationUrl, string requestXml, ProjectVM provm)
        {
            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(destinationUrl);
                byte[] bytes;
                bytes = System.Text.Encoding.ASCII.GetBytes(requestXml);
                request.ContentType = "application/xml; encoding='utf-8'";
                request.ContentLength = bytes.Length;
                request.Method = "PUT";
                Stream requestStream = request.GetRequestStream();
                requestStream.Write(bytes, 0, bytes.Length);
                requestStream.Close();
                HttpWebResponse response;
                response = (HttpWebResponse)request.GetResponse();

                int statusCode = Convert.ToInt32(response.StatusCode);

                if (response.StatusCode == HttpStatusCode.OK || response.StatusCode == HttpStatusCode.Created)
                {

                    SqlQueries.Project_Main_Tbl insertUpdateProjectMainTable = new SqlQueries.Project_Main_Tbl();
                    string DBResult = insertUpdateProjectMainTable.InsertOrUpdate(provm);
                    if (DBResult == "Success")
                    {
                        return "Success";
                    }
                    else
                    {
                        Logger.Debug("AddProService.putXMLData: Sql DB Insert Failed: " + DBResult);
                        return "Api Server failure";
                    }
                }
                else if (statusCode == 404)
                {
                    Logger.Debug("AddProService.putXMLData: 404 status code redmine: Redmine Unreachable/Not Responding");
                    return "Redmine Unreachable/Not Responding";
                }
                else if (statusCode == 422)
                {
                    Logger.Debug("AddProService.putXMLData: 422 status code redmine : Data Invalid");
                    return "Data Invalid";
                }
                else
                {
                    Logger.Debug("AddProService.putXMLData: Unknown Status: " + statusCode.ToString());
                    return "Failed";
                }

            }
            catch (WebException we)
            {
                Logger.Error("AddProService.putXMLData " + we.ToString());
                return "Redmine Server Not Responding";
            }
            catch (Exception ex)
            {
                Logger.Error("AddProService.putXMLData " + ex.ToString());
                return "Api Server failure";

            }
        }
        public static string CreateUpdate(Project data)
        {
            string returnResult = string.Empty;
            try
            {

                if (data == null)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Null Project Data ");
                    returnResult = "Invalid Data Recieved";
                    return returnResult;
                }
                if (data.response_put && data.id == 0)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Can't Update Project With No Id");
                    returnResult = "Can't Update Project With No Id";
                    return returnResult;
                }

                string dateFromatDB = "dd-MMM-yyyy";
                string dateFromatData = "yyyy-MM-dd";
                string exit_date = string.Empty;
                double project_cost = 0;
                string producer_name = string.Empty;
                string market = string.Empty;
                string hosting_platform = string.Empty;
                string status = string.Empty;
                double progress = 0;
                string live_url = string.Empty;
                string development_start_date = string.Empty;
                string launch_date = string.Empty;
                string post_result = string.Empty;

                for (int i = 0; i < data.custom_fields.Count; i++)
                {
                    if (data.custom_fields[i].name == "Exit Date")
                        exit_date = data.custom_fields[i].value;
                    if (data.custom_fields[i].name == "Estimates Shared with Client")
                    {
                        Double.TryParse(data.custom_fields[i].value, out project_cost);
                        project_cost = project_cost * (Convert.ToDouble((ConfigurationSettings.AppSettings["cost"])));
                    }
                    if (data.custom_fields[i].name == "Producer")
                        producer_name = data.custom_fields[i].value;
                    if (data.custom_fields[i].name == "Market")
                        market = data.custom_fields[i].value;
                    if (data.custom_fields[i].name == "Project Technology")
                        hosting_platform = data.custom_fields[i].value;
                    if (data.custom_fields[i].name == "Live URL")
                        live_url = data.custom_fields[i].value;
                    if (data.custom_fields[i].name == "Development Start Date")
                        development_start_date = data.custom_fields[i].value;
                    if (data.custom_fields[i].name == "Launch Date")
                        launch_date = data.custom_fields[i].value;
                }
                //Parse datetime for DataValidation class
                #region Date validation
                DateTime exitDate;
                DateTime launchDate;
                DateTime startDate;
                bool isExitDateValid = DateTime.TryParseExact(exit_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out exitDate);
                bool isDevStartDateValid = DateTime.TryParseExact(development_start_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDate);
                bool isLaunchDateValid = DateTime.TryParseExact(launch_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out launchDate);

                if (!string.IsNullOrWhiteSpace(exit_date) && !isExitDateValid)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Invalid Exit Date Format");
                    returnResult = "Invalid Exit Date Format";
                    return returnResult;

                }
                if (!string.IsNullOrWhiteSpace(development_start_date) && !isDevStartDateValid)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Invalid Exit Date Format");
                    returnResult = "Invalid Start Date Format";
                    return returnResult;
                }

                if (!string.IsNullOrWhiteSpace(launch_date) && !isLaunchDateValid)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Invalid Exit Date Format");
                    returnResult = "Invalid Launch Date Format";
                    return returnResult;
                }

                if ((startDate > launchDate) || (launchDate > exitDate) || (startDate > exitDate))
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Start Date > Launch Date > Exit Date");
                    returnResult = "Start Date > Launch Date > Exit Date";
                    return returnResult;
                }
                #endregion

                exit_date = exitDate.ToString(dateFromatDB);
                launch_date = launchDate.ToString(dateFromatDB);
                development_start_date = startDate.ToString(dateFromatDB);

                ProjectVM provm = new ProjectVM();
                provm.id = data.id;
                provm.name = data.name;
                provm.exit_date = exit_date;
                provm.project_cost = project_cost;
                provm.producer_name = producer_name;
                provm.market = market;
                provm.hosting_platform = hosting_platform;
                provm.status = status;
                provm.progress = progress;
                provm.live_url = live_url;
                provm.development_start_date = development_start_date;
                provm.launch_date = launch_date;

                string requestUri = System.Configuration.ConfigurationManager.AppSettings["hostUrl"] + "projects.xml?key=" + System.Configuration.ConfigurationManager.AppSettings["apiKey"];

                AddProService aps = new AddProService();

                string XmlizedString = XmlFormater(data);

                String finalString = XmlizedString.Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "");

                if (data.response_put == false)
                {
                    post_result = aps.postXMLData(requestUri, finalString, provm);
                }
                else
                {
                    int idchange = data.id;
                    string requesturi_put = ConfigurationSettings.AppSettings["hostUrl"] + "projects/" + idchange + ".xml?key=" + ConfigurationSettings.AppSettings["apiKey"];
                    post_result = aps.putXMLData(requesturi_put, finalString, provm);

                }

                if (post_result == "Success")
                {
                    return "Success";
                }
                else
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Post Failed" + post_result);
                    return post_result;
                }

            }
            catch (Exception ex)
            {
                Logger.Error("CreateUpdateCampaign.CreateUpdate " + ex.ToString());
                returnResult = "Api Server Exception";
                return returnResult;
            }
        }
        public List<ProjectVM> GetAllProjects()
        {
            try
            {
                int limit = 100;
                int offset = 0;
                int totalCount = 1;

                List<ProjectVM> projectVM = new List<ProjectVM>();
                List<Project> project_list = new List<Project>();

                while (totalCount > offset)
                {
                    string url = ConfigurationSettings.AppSettings["hostUrl"] + "projects.json?limit=" + limit + "&offset=" + offset + "&key=" + ConfigurationSettings.AppSettings["apiKey"];
                    WebRequest request = WebRequest.Create(url);
                    WebResponse response = request.GetResponse();

                    var time = new Total_TimeService();
                    var issue = new IssueService();
                    string[] result;

                    using (Stream responseStream = response.GetResponseStream())
                    {
                        #region // Get single project
                        StreamReader reader = new StreamReader(responseStream, Encoding.UTF8);
                        string str = reader.ReadToEnd();

                        RootObject root =
                                        (RootObject)Newtonsoft.Json.JsonConvert.DeserializeObject<RootObject>(str);
                        project_list = root.projects;

                        totalCount = root.total_count;
                        offset = root.offset;
                        limit = root.limit;

                        ProjectVM proVM;
                        for (int i = 0; i < project_list.Count; i++, offset++)
                        {

                            if (project_list[i].custom_fields[2].value == "Campaign Factory" && project_list[i].status.ToString() == "1")
                            {
                                proVM = new ProjectVM();

                                proVM.id = project_list[i].id;

                                proVM.parent = project_list[i].parent;

                                proVM.TotalTimeSpent = time.GetTimePerProject(proVM.id);

                                try
                                {
                                    proVM.EstimatedTime = Convert.ToDouble(project_list[i].custom_fields[1].value);

                                }
                                catch
                                {
                                    proVM.EstimatedTime = 0.0;
                                }
                                if (project_list[i].custom_fields[9].value == "")
                                {
                                    proVM.exit_date = "NA";
                                }
                                else
                                {
                                    DateTime d = Convert.ToDateTime(project_list[i].custom_fields[9].value);
                                    proVM.exit_date = d.ToString("dd-MMM-yyyy");
                                }

                                if (project_list[i].custom_fields[10].value == "")
                                {
                                    proVM.development_start_date = "NA";
                                }
                                else
                                {
                                    DateTime d1 = Convert.ToDateTime(project_list[i].custom_fields[10].value);
                                    proVM.development_start_date = d1.ToString("dd-MMM-yyyy");
                                }

                                proVM.project_cost = (proVM.EstimatedTime) * (Convert.ToDouble((ConfigurationSettings.AppSettings["cost"])));

                                if (proVM.EstimatedTime < proVM.TotalTimeSpent)
                                    proVM.burndown = false;
                                else
                                    proVM.burndown = true;

                                try
                                {
                                    proVM.launch_date = Convert.ToDateTime(project_list[i].custom_fields[0].value).ToString("dd-MMM-yyyy");
                                }
                                catch
                                {
                                    proVM.launch_date = "";
                                }

                                proVM.producer_name = project_list[i].custom_fields[4].value;

                                proVM.market = project_list[i].custom_fields[5].value;

                                proVM.hosting_platform = project_list[i].custom_fields[3].value;
                                proVM.live_url = project_list[i].custom_fields[7].value;

                                result = issue.GetProgressPerProject(proVM.id);

                                proVM.status = result[0];
                                proVM.progress = Convert.ToDouble(result[1]);
                                proVM.name = project_list[i].name;

                                projectVM.Add(proVM);
                            }
                        }
                        #endregion
                    }

                }
                return projectVM;
            }
            catch (WebException we)
            {
                Logger.Error("ProjectService.GetAllProjects" + we.ToString());
                return null;
            }
            catch (Exception ex)
            {
                Logger.Error("ProjectService.GetAllProjects" + ex.ToString());
                return null;
            }
        }
        public static string CreateUpdate(Project data, string redmineUserId)
        {
            string returnResult = string.Empty;
            try
            {

                if (data == null)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Null Project Data ");
                    returnResult = "Invalid Data Recieved";
                    return returnResult;
                }
                if (data.response_put && data.id == 0)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Can't Update Project With No Id");
                    returnResult = "Can't Update Project With No Id";
                    return returnResult;
                }

                string dateFromatDB = "dd-MMM-yyyy";
                string dateFromatData = "yyyy-MM-dd";
                string exit_date = string.Empty;
                double project_cost = 0;
                string producer_name = string.Empty;
                string market = string.Empty;
                string hosting_platform = string.Empty;
                string status = string.Empty;
                double progress = 0;
                string live_url = string.Empty;
                string development_start_date = string.Empty;
                string launch_date = string.Empty;
                string post_result = string.Empty;
                string due_date = string.Empty;
                string image_url = string.Empty;
                string pending_with = string.Empty;
                double estimateTime = 0;
                string qa_url = string.Empty;
                if (data.custom_fields != null)
                {
                    for (int i = 0; i < data.custom_fields.Count; i++)
                    {
                        if (data.custom_fields[i].name == "Image")
                            image_url = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Exit Date")
                            exit_date = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Estimates Shared with Client")
                        {
                            Double.TryParse(data.custom_fields[i].value, out estimateTime);
                            //estimateTime=project_cost;
                            project_cost = estimateTime * (Convert.ToDouble((ConfigurationManager.AppSettings["cost"])));
                        }
                        if (data.custom_fields[i].name == "Due Date")
                            due_date = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "QA CName")
                            qa_url = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Producer")
                            producer_name = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Market")
                            market = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Project Technology")
                            hosting_platform = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Live URL")
                            live_url = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Development Start Date")
                            development_start_date = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Launch Date")
                            launch_date = data.custom_fields[i].value;
                        if (data.custom_fields[i].name == "Pending With")
                            pending_with = data.custom_fields[i].value;

                    }
                }
                //Parse datetime for DataValidation class
                #region Date validation
                DateTime exitDate;
                DateTime launchDate;
                DateTime startDate;
                DateTime dueDate;

                bool isExitDateValid = DateTime.TryParseExact(exit_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out exitDate);
                bool isDevStartDateValid = DateTime.TryParseExact(development_start_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDate);
                bool isLaunchDateValid = DateTime.TryParseExact(launch_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out launchDate);
                bool isDueDateValid = DateTime.TryParseExact(due_date, dateFromatData, CultureInfo.InvariantCulture, DateTimeStyles.None, out dueDate);

                if (!string.IsNullOrWhiteSpace(exit_date) && !isExitDateValid)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Invalid Exit Date Format");
                    returnResult = "Invalid Exit Date Format";
                    return returnResult;

                }
                if (!string.IsNullOrWhiteSpace(development_start_date) && !isDevStartDateValid)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Invalid Start Date Format");
                    returnResult = "Invalid Start Date Format";
                    return returnResult;
                }

                if (!string.IsNullOrWhiteSpace(launch_date) && !isLaunchDateValid)
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Invalid Launch Date Format");
                    returnResult = "Invalid Launch Date Format";
                    return returnResult;
                }

                if (((startDate > launchDate) || (launchDate > exitDate) || (startDate > exitDate)) && ((launch_date != "") && (exit_date != "")))
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Start Date < Launch Date < Exit Date");
                    returnResult = "Start Date < Launch Date < Exit Date";
                    return returnResult;
                }
                #endregion

                exit_date = exitDate.ToString(dateFromatDB);
                launch_date = launchDate.ToString(dateFromatDB);
                development_start_date = startDate.ToString(dateFromatDB);
                due_date = dueDate.ToString(dateFromatDB);

                ProjectVM provm = new ProjectVM();
                provm.id = data.id;
                provm.name = data.name;
                provm.exit_date = exit_date;
                provm.project_cost = project_cost;
                provm.producer_name = producer_name;
                provm.market = market;
                provm.hosting_platform = hosting_platform;
                provm.status = status;
                provm.progress = progress;
                provm.live_url = live_url;
                provm.development_start_date = development_start_date;
                provm.launch_date = launch_date;
                provm.EstimatedTime = estimateTime;
                provm.due_date = due_date;
                provm.image_url = image_url;
                provm.pending_with = pending_with;
                provm.qa_url = qa_url;

                string getUserApiKey = "Select [RedmineApiKey] from [Users] where [RedmineUserId] = '" + redmineUserId + "'";
                string UserApiKey = dc.GetSingleCell(getUserApiKey);

                string getPendingWith = "Select [pending_with] from [Project_Main] where [Project_Id] = '" + data.id + "'";
                string pendingWith = dc.GetSingleCell(getPendingWith);

                #region // Send mails on change of pending_with status
                if (data.response_put)
                {
                    if (!EmailService.EstimateToDevlopmentMovedMail(data.id.ToString(), data.name))
                        Logger.Debug("CreateUpdateCampaign.CreateUpdate: Unable to send EstimateToDevlopmentMovedMails ");

                    if ((pending_with.Trim().ToUpper() != pendingWith.Trim().ToUpper()) && !string.IsNullOrEmpty(pending_with))
                    {
                        if (!EmailService.EstimateToDevlopmentMovedMail(data.id.ToString() , data.name))
                            Logger.Debug("CreateUpdateCampaign.CreateUpdate: Unable to send EstimateToDevlopmentMovedMails ");
                    }
                }
                #endregion

                string requestUri = System.Configuration.ConfigurationManager.AppSettings["hostUrl"] + "projects.xml?key=" + UserApiKey;//System.Configuration.ConfigurationManager.AppSettings["apiKey"];

                AddProService aps = new AddProService();

                string XmlizedString = XmlFormater(data);

                String finalString = XmlizedString.Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "");

                if (data.response_put == false)
                {
                    post_result = aps.postXMLData(requestUri, finalString, provm);
                }
                else
                {
                    int idchange = data.id;
                    string requesturi_put = ConfigurationManager.AppSettings["hostUrl"] + "projects/" + idchange + ".xml?key=" + UserApiKey;// ConfigurationManager.AppSettings["apiKey"];
                    post_result = aps.putXMLData(requesturi_put, finalString, provm);
                    if (post_result != "Success")
                    {
                        Logger.Debug("CreateUpdateCampaign.CreateUpdate : requestUri " + requesturi_put);
                        Logger.Debug("CreateUpdateCampaign.CreateUpdate : xml " + finalString);

                    }
                }

                if (post_result == "Success")
                {
                    return "Success";
                }
                else
                {
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : Post Failed" + post_result);
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : requestUri " + requestUri);
                    Logger.Debug("CreateUpdateCampaign.CreateUpdate : xml " + finalString);
                    return post_result;
                }

            }
            catch (Exception ex)
            {
                Logger.Error("CreateUpdateCampaign.CreateUpdate " + ex.ToString());
                returnResult = "Api Server Exception";
                return returnResult;
            }
        }
        public HttpResponseMessage UpdateProjectDynamic(ProjectVM data)
        {
            string query = @"UPDATE [dbo].[Project_Main] SET "; ;

            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter param;

            if (data.id != 0)
            {
                //return ;//cant update project without id
                query += @" [Project_Id] = @Project_Id ";
                if (data.name != null)
                {
                    query += @",[Name] = @Name";
                    param = new SqlParameter("@Name", SqlDbType.VarChar) { Value = data.name };
                    list.Add(param);
                }
                if (data.exit_date != null)
                {
                    query += @",[Exit_Date] = @Exit_Date";
                    param = new SqlParameter("@Exit_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.exit_date).ToString("yyyy-MM-dd") };
                    list.Add(param);
                }
                if (data.project_cost != 0.0)
                {
                    query += @",[Project_Cost] = @Project_Cost";
                    param = new SqlParameter("@Project_Cost", SqlDbType.Decimal) { Value = data.project_cost };
                    list.Add(param);
                }
                if (data.producer_name != null)
                {
                    query += @",[Producer_Name] = @Producer_Name";
                    param = new SqlParameter("@Producer_Name", SqlDbType.VarChar) { Value = data.producer_name };
                    list.Add(param);
                }
                if (data.market != null)
                {
                    query += @",[Market] = @Market";
                    param = new SqlParameter("@Market", SqlDbType.VarChar) { Value = data.market };
                    list.Add(param);
                }
                if (data.hosting_platform != null)
                {
                    query += @",[Hosting_Platform] = @Hosting_Platform";
                    param = new SqlParameter("@Hosting_Platform", SqlDbType.VarChar) { Value = data.hosting_platform };
                    list.Add(param);
                }
                if (data.status != null)
                {
                    query += @",[Phase_Status] = @Phase_Status";
                    param = new SqlParameter("@Phase_Status", SqlDbType.VarChar) { Value = data.status };
                    list.Add(param);
                }
                if (data.progress != 0.0)
                {
                    query += @",[Progress] = @Progress";
                    param = new SqlParameter("@Progress", SqlDbType.Decimal) { Value = data.progress };
                    list.Add(param);
                }
                if (data.live_url != null)
                {
                    query += @",[Live_Url] = @Live_Url";
                    param = new SqlParameter("@Live_Url", SqlDbType.VarChar) { Value = data.live_url };
                    list.Add(param);
                }
                if (data.development_start_date != null)
                {
                    query += @",[Development_Start_Date] = @Development_Start_Date";
                    param = new SqlParameter("@Development_Start_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.development_start_date).ToString("yyyy-MM-dd") };
                    list.Add(param);
                }
                if (data.launch_date != null)
                {
                    query += @",[Launch_Date] = @Launch_Date";
                    param = new SqlParameter("@Launch_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.launch_date).ToString("yyyy-MM-dd") };
                    list.Add(param);
                }
                if (data.due_date != null)
                {
                    query += @",[due_date] = @due_date";
                    param = new SqlParameter("@due_date", SqlDbType.Date) { Value = Convert.ToDateTime(data.due_date).ToString("yyyy-MM-dd") };
                    list.Add(param);
                }
                if (data.pending_with != null)
                {
                    query += @",[pending_with] = @pending_with";
                    param = new SqlParameter("@pending_with", SqlDbType.VarChar) { Value = data.pending_with };
                    list.Add(param);
                }
                if (data.image_url != null)
                {
                    query += @",[image_url] = @image_url";
                    param = new SqlParameter("@image_url", SqlDbType.VarChar) { Value = data.image_url };
                    list.Add(param);
                }
                query += @" WHERE [Project_Id] = @Project_Id";
                param = new SqlParameter("@Project_Id", SqlDbType.BigInt) { Value = data.id };
                list.Add(param);
            }

            SqlParameter[] sqlParam = list.ToArray();

            DatabaseConnection dc = new DatabaseConnection();
            dc.InsertUpdateDeleteDynamic(query, sqlParam);

            //int id = 0;
            //string dateFormat = "yyyy-MM-dd";
            //string exit_date = string.Empty;
            //double project_cost = 0.0;
            //string producer_name = string.Empty;
            //string market = string.Empty;
            //string hosting_platform = string.Empty;
            //string status = string.Empty;
            //double progress = 0.0;
            //string live_url = string.Empty;
            //string development_start_date = string.Empty;
            //string launch_date = string.Empty;
            //string post_result = string.Empty;
            //string due_date = string.Empty;
            //string image_url = string.Empty;
            //double estimateTime = 0.0;

            //foreach (PropertyInfo property in properties)
            //{
            //    object propertyValue = property.GetValue(data, null);
            //    Type propType = property.PropertyType;
            //    string propName = property.Name;
            //    switch (propName)
            //    {
            //        case "id":

            //            break;
            //    }

            //    //if (propType == typeof(int))
            //    //{
            //    //    Int64 interger = Convert.ToInt64(propertyValue);
            //    //    if (interger != 0)
            //    //        listOfProperties += property.Name.ToString() + ",";
            //    //}
            //    //if (propType == typeof(double))
            //    //{
            //    //    double doubleVal = Convert.ToDouble(propertyValue);
            //    //    if (doubleVal != Double.MinValue)
            //    //        listOfProperties += property.Name.ToString() + ",";
            //    //}
            //    //if (propType == typeof(string))
            //    //{
            //    //    string stringVal = Convert.ToString(propertyValue);
            //    //    if (!string.IsNullOrEmpty(stringVal))
            //    //        listOfProperties += property.Name.ToString() + ",";
            //    //}
            //}

            return Request.CreateResponse(HttpStatusCode.OK, "");//"Authentication error!! Please try again");
        }
Пример #7
0
            /// <summary>
            /// <para>Update Campaign table </para>
            /// <para>returns "Project VM Null" string if parameter is null </para>
            /// <para>returns "Project ID Invalid" string if id is null </para>
            ///  <para>returns "Success" or "Fail" string on update  </para>
            ///  <para>returns string which contains Exception: for uncaught exception</para>
            /// </summary>
            /// <param name="proVM">PojectVM class</param>
            /// <returns>string value regarding status of update</returns>
            public string UpdateCampaignDynamicParam(ProjectVM data)
            {
                try
                {
                    string query = @"UPDATE [dbo].[Project_Main] SET "; ;

                    List<SqlParameter> list = new List<SqlParameter>();
                    SqlParameter param;

                    if (data.id != 0)
                    {
                        //return ;//cant update project without id
                        query += @" [Project_Id] = @Project_Id ";
                        if (!string.IsNullOrEmpty(data.name))
                        {
                            query += @",[Name] = @Name";
                            param = new SqlParameter("@Name", SqlDbType.VarChar) { Value = data.name };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.exit_date) && Convert.ToDateTime(data.exit_date) != DateTime.MinValue)
                        {
                            query += @",[Exit_Date] = @Exit_Date";
                            param = new SqlParameter("@Exit_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.exit_date).ToString("yyyy-MM-dd") };
                            list.Add(param);
                        }
                        if (data.project_cost != 0.0)
                        {
                            query += @",[Project_Cost] = @Project_Cost";
                            param = new SqlParameter("@Project_Cost", SqlDbType.Decimal) { Value = data.project_cost };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.producer_name))
                        {
                            query += @",[Producer_Name] = @Producer_Name";
                            param = new SqlParameter("@Producer_Name", SqlDbType.VarChar) { Value = data.producer_name };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.market))
                        {
                            query += @",[Market] = @Market";
                            param = new SqlParameter("@Market", SqlDbType.VarChar) { Value = data.market };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.hosting_platform))
                        {
                            query += @",[Hosting_Platform] = @Hosting_Platform";
                            param = new SqlParameter("@Hosting_Platform", SqlDbType.VarChar) { Value = data.hosting_platform };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.status))
                        {
                            query += @",[Phase_Status] = @Phase_Status";
                            param = new SqlParameter("@Phase_Status", SqlDbType.VarChar) { Value = data.status };
                            list.Add(param);
                        }
                        if (data.progress != 0.0)
                        {
                            query += @",[Progress] = @Progress";
                            param = new SqlParameter("@Progress", SqlDbType.Decimal) { Value = data.progress };
                            list.Add(param);
                        }
                        if (data.EstimatedTime != 0.0)
                        {
                            query += @",[EstimatedTime] = @EstimatedTime";
                            param = new SqlParameter("@EstimatedTime", SqlDbType.Decimal) { Value = data.EstimatedTime };
                            list.Add(param);
                        }
                        if (data.TotalTimeSpent != 0.0)
                        {
                            query += @",[TotalTimeSpent] = @TotalTimeSpent";
                            param = new SqlParameter("@TotalTimeSpent", SqlDbType.Decimal) { Value = data.TotalTimeSpent };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.live_url))
                        {
                            query += @",[Live_Url] = @Live_Url";
                            param = new SqlParameter("@Live_Url", SqlDbType.VarChar) { Value = data.live_url };
                            list.Add(param);
                        }
                        if (data.development_start_date != null && Convert.ToDateTime(data.development_start_date) != DateTime.MinValue)
                        {
                            query += @",[Development_Start_Date] = @Development_Start_Date";
                            param = new SqlParameter("@Development_Start_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.development_start_date).ToString("yyyy-MM-dd") };
                            list.Add(param);
                        }
                        if (data.launch_date != null && Convert.ToDateTime(data.launch_date) != DateTime.MinValue)
                        {
                            query += @",[Launch_Date] = @Launch_Date";
                            param = new SqlParameter("@Launch_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.launch_date).ToString("yyyy-MM-dd") };
                            list.Add(param);
                        }
                        if (data.due_date != null && Convert.ToDateTime(data.due_date) != DateTime.MinValue)
                        {
                            query += @",[due_date] = @due_date";
                            param = new SqlParameter("@due_date", SqlDbType.Date) { Value = Convert.ToDateTime(data.due_date).ToString("yyyy-MM-dd") };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.pending_with))
                        {
                            query += @",[pending_with] = @pending_with";
                            param = new SqlParameter("@pending_with", SqlDbType.VarChar) { Value = data.pending_with };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.image_url))
                        {
                            query += @",[image_url] = @image_url";
                            param = new SqlParameter("@image_url", SqlDbType.VarChar) { Value = data.image_url };
                            list.Add(param);
                        }
                        if (!string.IsNullOrEmpty(data.qa_url))
                        {
                            query += @",[qa_url] = @qa_url";
                            param = new SqlParameter("@qa_url", SqlDbType.VarChar) { Value = data.qa_url };
                            list.Add(param);
                        }

                        query += @" WHERE [Project_Id] = @Project_Id";
                        param = new SqlParameter("@Project_Id", SqlDbType.BigInt) { Value = data.id };
                        list.Add(param);
                    }

                    SqlParameter[] sqlParam = list.ToArray();

                    DatabaseConnection dc = new DatabaseConnection();
                    if (dc.InsertUpdateDeleteDynamic(query, sqlParam))
                        return "Success";
                    else
                        return "Failed";

                }
                catch (Exception ex)
                {
                    Logger.Error("SqlQueries.Project_Main_Tbl.UpdateCampaignDynamicParam : " + ex.ToString());
                    return "Exception: " + ex.ToString();
                }
            }
Пример #8
0
            /// <summary>
            /// <para>Insert or update Project_Main table </para>
            /// <para>returns "Project VM Null" string if parameter is null </para>
            /// <para>returns "Project ID Invalid" string if id is null </para>
            ///  <para>returns "Success" or "Fail" string on insert/update  </para>
            ///  <para>returns string which contains Exception: for uncaught exception</para>
            /// </summary>
            /// <param name="proVM">PojectVM class</param>
            /// <returns>string value regarding status of insert/update</returns>
            public string InsertOrUpdate(ProjectVM proVM)
            {
                try
                {
                    ProjectVM p = new ProjectVM();
                    if (proVM == null)
                        return "Project VM Null";

                    int arrayLength = 19;
                    #region  //paramNameSql
                    string[] paramNameSql = new string[arrayLength];
                    paramNameSql[0] = "@Project_Id";
                    paramNameSql[1] = "@EstimatedTime";
                    paramNameSql[2] = "@TotalTimeSpent";
                    paramNameSql[3] = "@Burndown";
                    paramNameSql[4] = "@Development_Start_Date";
                    paramNameSql[5] = "@Exit_Date";
                    paramNameSql[6] = "@Hosting_Platform";
                    paramNameSql[7] = "@Launch_Date";
                    paramNameSql[8] = "@Live_Url";
                    paramNameSql[9] = "@Market";
                    paramNameSql[10] = "@Name";
                    paramNameSql[11] = "@Producer_Name";
                    paramNameSql[12] = "@Progress";
                    paramNameSql[13] = "@Project_Cost";
                    paramNameSql[14] = "@Phase_Status";
                    paramNameSql[15] = "@pending_with";
                    paramNameSql[16] = "@due_date";
                    paramNameSql[17] = "@image_url";
                    paramNameSql[18] = "@qa_url";
                    #endregion

                    #region // sql dbtype array
                    SqlDbType[] dbType = new SqlDbType[arrayLength];
                    dbType[0] = SqlDbType.BigInt;//"@Project_Id";
                    dbType[1] = SqlDbType.Decimal;//"@EstimatedTime";
                    dbType[2] = SqlDbType.Decimal;//"@TotalTimeSpent";
                    dbType[3] = SqlDbType.Bit;// "@Burndown";
                    dbType[4] = SqlDbType.Date;// "@Development_Start_Date";
                    dbType[5] = SqlDbType.Date;// "@Exit_Date";
                    dbType[6] = SqlDbType.VarChar;//"@Hosting_Platform";
                    dbType[7] = SqlDbType.Date; // "@Launch_Date";
                    dbType[8] = SqlDbType.VarChar;// "@Live_Url";
                    dbType[9] = SqlDbType.VarChar;// "@Market";
                    dbType[10] = SqlDbType.VarChar; //"@Name";
                    dbType[11] = SqlDbType.VarChar;// "@Producer_Name";
                    dbType[12] = SqlDbType.Decimal;// "@Progress";
                    dbType[13] = SqlDbType.Decimal;// "@Project_Cost";
                    dbType[14] = SqlDbType.VarChar;// "@Phase_Status";
                    dbType[15] = SqlDbType.VarChar;// "@pending_with";
                    dbType[16] = SqlDbType.Date;// "@due_date";
                    dbType[17] = SqlDbType.VarChar;// "@image_url";
                    dbType[18] = SqlDbType.VarChar;//@qa_url;

                    #endregion

                    #region// data values & paramValue array

                    DataValidation dv = new DataValidation();
                    dv.id = proVM.id;
                    dv.name = proVM.name;
                    dv.exit_date = proVM.exit_date;
                    dv.project_cost = proVM.project_cost;
                    dv.burndown = proVM.burndown;
                    dv.producer_name = proVM.producer_name;
                    dv.market = proVM.market;
                    dv.hosting_platform = proVM.hosting_platform;
                    dv.phase_status = proVM.status;
                    dv.progress = proVM.progress;
                    dv.live_url = proVM.live_url;
                    dv.development_start_date = proVM.development_start_date;
                    dv.launch_date = proVM.launch_date;
                    dv.TotalTimeSpent = proVM.TotalTimeSpent;
                    dv.EstimatedTime = proVM.EstimatedTime;

                    dv.pending_with = proVM.pending_with;
                    dv.due_date = proVM.due_date;
                    dv.image_url = proVM.image_url;
                    dv.qa_url = proVM.qa_url;

                    string[] paramValue = new string[arrayLength];
                    paramValue[0] = dv.id.ToString();//"@Project_Id";
                    paramValue[1] = dv.EstimatedTime.ToString();// "@EstimatedTime";
                    paramValue[2] = dv.TotalTimeSpent.ToString();// "@TotalTimeSpent";
                    paramValue[3] = dv.burndown.ToString();// "@Burndown";
                    paramValue[4] = dv.development_start_date;// "@Development_Start_Date";
                    paramValue[5] = dv.exit_date;// "@Exit_Date";
                    paramValue[6] = dv.hosting_platform;// "@Hosting_Platform";
                    paramValue[7] = dv.launch_date;// "@Launch_Date";
                    paramValue[8] = dv.live_url;// "@Live_Url";
                    paramValue[9] = dv.market;// "@Market";
                    paramValue[10] = dv.name;// "@Name";
                    paramValue[11] = dv.producer_name;// "@Producer_Name";
                    paramValue[12] = dv.progress.ToString();// "@Progress";
                    paramValue[13] = dv.project_cost.ToString();// "@Project_Cost";
                    paramValue[14] = dv.phase_status;// "@Phase_Status";
                    paramValue[15] = dv.pending_with;// "@pending_with";
                    paramValue[16] = dv.due_date; // "@due_date";
                    paramValue[17] = dv.image_url; // "@image_url";
                    paramValue[18] = dv.qa_url;//

                    if (dv.id == 74)
                    {
                        string sr = string.Empty;
                    }
                    #endregion

                    string projectTableId = dc.GetSingleCell("Select Id from Project_Main where Project_Id = '" + dv.id + "'");
                    SqlParameter[] param = dc.MySqlParamCreate(arrayLength, paramNameSql, dbType, paramValue);

                    #region //sql query insert/update
                    if (string.IsNullOrWhiteSpace(projectTableId))
                    {
                        //insert into db
                        string insertQuery = @"INSERT INTO [dbo].[Project_Main] ([Project_Id], [EstimatedTime], [TotalTimeSpent]
                   ,[Burndown], [Development_Start_Date] ,[Exit_Date] ,[Hosting_Platform] ,[Launch_Date] ,[Live_Url] ,[Market]
                   ,[Name] ,[Producer_Name] ,[Progress] ,[Project_Cost] ,[Phase_Status] ,[pending_with] ,[due_date] ,[image_url],[qa_url] )
                 VALUES
                   (@Project_Id ,@EstimatedTime ,@TotalTimeSpent ,@Burndown ,@Development_Start_Date ,@Exit_Date ,@Hosting_Platform
                   ,@Launch_Date ,@Live_Url ,@Market ,@Name ,@Producer_Name ,@Progress ,@Project_Cost ,@Phase_Status ,@pending_with ,@due_date ,@image_url , @qa_url)";

                        if (dc.InsertUpdateDelete(insertQuery, param))
                            return "Success";
                        else
                        {
                            Logger.Debug("SqlQueries.Project_Main_Tbl.InsertOrUpdate: Failed To Insert Query");
                            return "Fail";
                        }
                    }
                    else
                    {
                        //update into row db

                        string updateQuery = @"UPDATE [dbo].[Project_Main]
                                               SET [EstimatedTime] = @EstimatedTime
                                                  ,[TotalTimeSpent] = @TotalTimeSpent
                                                  ,[Burndown] = @Burndown
                                                  ,[Development_Start_Date] = @Development_Start_Date
                                                  ,[Exit_Date] = @Exit_Date
                                                  ,[Hosting_Platform] = @Hosting_Platform
                                                  ,[Launch_Date] = @Launch_Date
                                                  ,[Live_Url] = @Live_Url
                                                  ,[Market] = @Market
                                                  ,[Name] = @Name
                                                  ,[Producer_Name] = @Producer_Name
                                                  ,[Progress] = @Progress
                                                  ,[Project_Cost] = @Project_Cost
                                                  ,[Phase_Status] = @Phase_Status
                                                  ,[pending_with] = @pending_with
                                                  ,[due_date] = @due_date
                                                  ,[image_url] = @image_url
                                                  ,[qa_url] = @qa_url
                                               WHERE [Id] = '" + projectTableId + "'";

                        if (dc.InsertUpdateDelete(updateQuery, param))
                            return "Success";
                        else
                        {
                            Logger.Debug("SqlQueries.Project_Main_Tbl.InsertOrUpdate: Failed To Update Query");
                            return "Fail";
                        }
                    }
                    #endregion

                }
                catch (Exception ex)
                {
                    Logger.Error("SqlQueries.Project_Main_Tbl.InsertOrUpdate : " + ex.ToString());
                    return "Exception: " + ex.ToString();
                }
            }
        public string postXMLData(string destinationUrl, string requestXml, ProjectVM provm)
        {
            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(destinationUrl);
                byte[] bytes;
                bytes = System.Text.Encoding.ASCII.GetBytes(requestXml);
                request.ContentType = "application/xml; encoding='utf-8'";
                request.ContentLength = bytes.Length;
                request.Method = "POST";
                Stream requestStream = request.GetRequestStream();
                requestStream.Write(bytes, 0, bytes.Length);
                requestStream.Close();
                HttpWebResponse response;
                response = (HttpWebResponse)request.GetResponse();

                int statusCode = Convert.ToInt32(response.StatusCode);

                if (response.StatusCode == HttpStatusCode.OK || response.StatusCode == HttpStatusCode.Created)
                {
                    int id = 0;

                    string[] strArr = response.Headers["Location"].ToString().Split('/');
                    int.TryParse(strArr[strArr.Length - 1], out id);//getting id of created project
                    provm.id = id;
                    List<string> userRole = null;

                    SqlQueries.Project_Main_Tbl insertUpdateProjectMainTable = new SqlQueries.Project_Main_Tbl();
                    string DBResult = insertUpdateProjectMainTable.InsertOrUpdate(provm);
                    if (DBResult == "Success")
                    {
                        AddUserToProjectMembership(id.ToString(), GetUsersFromGroup(out userRole), userRole);
                        return "Success";
                    }
                    else
                    {
                        Logger.Debug("AddProService.postXMLData: Sql DB Insert Failed: " + DBResult);
                        return "Api Server failure";
                    }
                }
                else if (statusCode == 404)
                {
                    Logger.Debug("AddProService.postXMLData: 404 status code redmine: Redmine Unreachable/Not Responding");
                    return "Redmine Unreachable/Not Responding";
                }
                else if (statusCode == 422)
                {
                    Logger.Debug("AddProService.postXMLData: 422 status code redmine : Data Invalid");
                    return "Data Invalid";
                }
                else
                {
                    Logger.Debug("AddProService.postXMLData: Unknown Status: " + statusCode.ToString());
                    return "Failed";
                }

            }
            catch (WebException we)
            {
                Logger.Error("AddProService.postXMLData " + we.ToString());
                return "Redmine Server Not Responding";
            }
            catch (Exception ex)
            {
                Logger.Error("AddProService.postXMLData " + ex.ToString());
                return "Api Server failure";

            }
        }