//Enter id of tab you want to show eg: Live/estimate/development/archived
        public HttpResponseMessage GetJson(string id)
        {
            try
            {
                SqlQueries.Project_Main_Tbl sq = new SqlQueries.Project_Main_Tbl();
                DataTable dt = sq.CampaignTabs(id);
                if (dt == null)
                {
                    Logger.Debug("JsonDataController.GetJson: Sql Db Null Error");
                    return Request.CreateResponse(HttpStatusCode.InternalServerError, "No Data Found in Database");
                }
                if (dt.Rows.Count <= 0)
                {
                    Logger.Debug("JsonDataController.GetJson: Sql Db Empty Error");
                    return Request.CreateResponse(HttpStatusCode.InternalServerError, "No Data Found in Database");
                }

                string jsonString = JsonConvert.SerializeObject(dt);
                List<ProjectVM_Send> root = (List<ProjectVM_Send>)Newtonsoft.Json.JsonConvert.DeserializeObject<List<ProjectVM_Send>>(jsonString);

                return Request.CreateResponse(HttpStatusCode.OK, root);

            }
            catch
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, "Exception Occuered While Retrieving Data");
            }
            //return "value";
        }
        public List<string> SyncData()
        {
            List<string> listReturnValues = new List<string>();
            try
            {
                List<ProjectVM> projectList = new List<ProjectVM>();
                ProjectService getProjects = new ProjectService();
                projectList = getProjects.GetAllProjects();

                foreach (ProjectVM proVM in projectList)
                {
                    SqlQueries.Project_Main_Tbl mainTableIsertionObject = new SqlQueries.Project_Main_Tbl();

                    listReturnValues.Add(mainTableIsertionObject.InsertOrUpdate(proVM));

                }
                #region // delete project not in redmine
                DataTable dt = dc.GetDataTable("Select [Project_Id] from [dbo].[Project_Main] ");
                List<Int64> projectIdRedmine = new List<Int64>();
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < projectList.Count; i++)
                        {
                            projectIdRedmine.Add(projectList[i].id);
                        }
                        foreach (DataRow dr in dt.Rows)
                        {
                            //delete project which are not present
                            if (!projectIdRedmine.Contains(Convert.ToInt64(dr["Project_Id"])))
                            {
                                string deleteProjectRowQuery = "Delete from Project_Main where Project_Id = '" + dr["Project_Id"].ToString() + "'";
                                listReturnValues.Add("Rows Delted for ProjectId " + dr["Project_Id"].ToString() + ": " + dc.InsertUpdateDelete(deleteProjectRowQuery).ToString());
                            }
                        }

                    }
                }
                #endregion

                listReturnValues.Add("Sync Sucess");
                return listReturnValues;
            }
            catch (Exception ex)
            {
                Logger.Error("JsonToSQL.SyncData" + ex.ToString());
                listReturnValues.Add("Sync Failed:" + ex.ToString());
                return listReturnValues;

            }
        }
        // GET api/jsondata
        public HttpResponseMessage GetJson()
        {
            try
            {
                int redmineUserId ;
                AccountsServices ac = new AccountsServices();
                string auth = ac.CheckToken(Request);
                if (string.IsNullOrEmpty(auth))
                    return Request.CreateResponse(HttpStatusCode.Unauthorized, false);//"Unauthorized access");
                else if (auth.Contains("Exception"))
                    return Request.CreateResponse(HttpStatusCode.Unauthorized, false);//"Authentication error!! Please try again");
                else
                    int.TryParse(auth, out redmineUserId);

                SqlQueries.Project_Main_Tbl sq = new SqlQueries.Project_Main_Tbl();
                DataTable dt = sq.GetAllCampaigns(redmineUserId.ToString());

                if (dt == null)
                {
                    Logger.Debug("JsonDataController.GetJson: Sql Db Null Error");
                    return Request.CreateResponse(HttpStatusCode.OK, false);//"No Data Found in Database");
                }
                if (dt.Rows.Count <= 0)
                {
                    Logger.Debug("JsonDataController.GetJson: Sql Db Empty Error");
                    return Request.CreateResponse(HttpStatusCode.OK, false);//"No Data Found in Database");
                }

                string jsonString = JsonConvert.SerializeObject(dt);
                List<ProjectVM_Send> root = (List<ProjectVM_Send>)Newtonsoft.Json.JsonConvert.DeserializeObject<List<ProjectVM_Send>>(jsonString);

                return Request.CreateResponse(HttpStatusCode.OK, root);

            }
            catch
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, false);//"Exception Occuered While Retrieving Data");
            }
            //return JsonDeserializer.JsonFileDeserializer();
        }
        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 StartAsyncHttpResponse()
        {
            try
            {
                SqlQueries.Project_Main_Tbl selectDevTableObj = new SqlQueries.Project_Main_Tbl();
                DataTable dt = new DataTable();
                dt = selectDevTableObj.PingCheck();

                string[] urls = { "" };
                string[] projectIdToIgnoreFromPing = ConfigurationManager.AppSettings["projectsToIgnorePingCheck"].Replace(" ", "").Split(',');
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {

                        string returnStatus = string.Empty;
                        List<string> urlList = new List<string>();
                        List<string> projectNameList = new List<string>();
                        List<string> projectIdList = new List<string>();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //Uri url = null;
                            string urlData = dt.Rows[i]["live_url"].ToString();
                            string projectName = dt.Rows[i]["name"].ToString();
                            string projectId = dt.Rows[i]["id"].ToString();

                            if (!string.IsNullOrEmpty(urlData) && !projectIdToIgnoreFromPing.Contains(projectId.Trim()))
                            {
                                urlList.Add(urlData);
                                projectNameList.Add(projectName);
                                projectIdList.Add(projectId);
                            }

                        }
                        Task[] taskList = new Task[urlList.Count];
                        for (int i = 0; i < urlList.Count; i++)
                        {
                            var index = i;
                            //GetAsync(urlList[index], projectNameList[index], projectIdList[index]);
                            taskList[i] = Task.Factory.StartNew(() => (GetAsync(urlList[index], projectNameList[index], projectIdList[index])));
                        }

                        try
                        {
                            Task.WaitAll(taskList);

                            returnStatus = "Completed";
                        }
                        catch (AggregateException ae)
                        {
                            ErrorLogging.WriteErrorLog(ae);
                            returnStatus = ae.ToString();
                        }
                        catch (Exception ex)
                        {
                            ErrorLogging.WriteErrorLog(ex);
                            returnStatus = ex.ToString();
                        }
                        return returnStatus;

                    }
                    else
                        //return "Completed";
                        return "No Live Campaigns";
                }
                else
                    return "No Live Campaigns";
            }
            catch (Exception exe)
            {
                ErrorLogging.WriteErrorLog(exe);
                return exe.ToString();

            }
        }
        public List<string> SyncProjects()
        {
            List<string> listReturnValues = new List<string>();
            try
            {
                List<ProjectVM> projectList = new List<ProjectVM>();
                ProjectService getProjects = new ProjectService();
                projectList = getProjects.GetAllProjects();

                foreach (ProjectVM proVM in projectList)
                {
                    SqlQueries.Project_Main_Tbl mainTableIsertionObject = new SqlQueries.Project_Main_Tbl();
                    string mainTableResult = mainTableIsertionObject.InsertOrUpdate(proVM);
                    //if (mainTableResult == "Success")
                    //{
                    //    SqlQueries.Membership_Tbl membershipTblObj;

                    //    foreach (Membership mem in proVM.members)
                    //    {
                    //        foreach (Role role in mem.roles)
                    //        {
                    //            membershipTblObj = new SqlQueries.Membership_Tbl();

                    //            string membershipResult = membershipTblObj.InsertOrUpdateMembership(mem.id.ToString(), mem.user.id.ToString(), role.name, proVM.id.ToString());

                    //        }
                    //    }

                    //}
                    listReturnValues.Add("ProjectId = " + proVM.id.ToString() + " MainTable : " + mainTableResult);

                }
                #region // delete project not in redmine
                DataTable dt = dc.GetDataTable("Select [Project_Id] from [dbo].[Project_Main] ");
                List<Int64> projectIdRedmine = new List<Int64>();
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < projectList.Count; i++)
                        {
                            projectIdRedmine.Add(projectList[i].id);
                        }
                        foreach (DataRow dr in dt.Rows)
                        {
                            //delete project which are not present
                            if (!projectIdRedmine.Contains(Convert.ToInt64(dr["Project_Id"])))
                            {
                                string deleteProjectRowQuery = "Delete from [Memberships] where Project_Id= '" + dr["Project_Id"].ToString() + "' Delete from Project_Main where Project_Id = '" + dr["Project_Id"].ToString() + "' ";
                                listReturnValues.Add("Rows Delted for ProjectId " + dr["Project_Id"].ToString() + ": " + dc.InsertUpdateDelete(deleteProjectRowQuery).ToString());
                            }
                        }

                    }
                }
                #endregion

                listReturnValues.Add("Sync Sucess");
                return listReturnValues;
            }
            catch (Exception ex)
            {
                Logger.Error("JsonToSQL.SyncData" + ex.ToString());
                listReturnValues.Add("Sync Failed:" + ex.ToString());
                return listReturnValues;

            }
        }
        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";

            }
        }