// GET api/executesql/5
public string Get(string id)
{
string sql = id;
DatabaseConnection dc = new DatabaseConnection();
if (dc.InsertUpdateDelete(sql))
return "Success";
else
return "Fail";
}
private static string CheckStatus(int responseCode, string projectName, string liveUrl, string projectId)
{
try
{
dc = new DatabaseConnection();
string checkIssueIdQuery = "Select Isssue_id , Enabled, Project_id from IssueStatusTracking where Project_id='" + projectId + "'";
DataTable dt = new DataTable();
dt = dc.GetDataTable(checkIssueIdQuery);
int issue = 0;
bool flag = false;
if (dt != null)
{
if (dt.Rows.Count > 0)
{
int.TryParse(dt.Rows[0]["Isssue_id"].ToString(), out issue);//IssueTrack[0] = issue id
bool.TryParse(dt.Rows[0]["Enabled"].ToString(), out flag);
}
}
if (responseCode != 200)//check ping status
{
if (issue <= 0)
{
//insert defect
//post call in redmine
string issueId = "";
issueId = CreateIssue(projectName, liveUrl, 1);
int createdIssueId = 0;
int.TryParse(issueId, out createdIssueId);
if (createdIssueId > 0)
{
string insertQuery = "Insert into IssueStatusTracking (Isssue_id, Enabled, Project_id) values ('" + issueId + "','" + true + "','" + projectId + "')";
dc.InsertUpdateDelete(insertQuery);
string watchersConfig = System.Configuration.ConfigurationManager.AppSettings["watchers"];
string[] watcherArray = watchersConfig.Split(',');
PostData.AddWatchers(issueId, watcherArray);
string[] data = { "Website ping failed: " + DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + " Url: " + liveUrl };
string[] element = { "notes" };
PostData.UpdateIssue(createdIssueId.ToString(), data, element);
}
}
else if (issue > 0 && flag == true)
{
//update notes in redmine for previous issue
string[] data = { "Website ping continues to fail: " + DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + " Url: " + liveUrl };
string[] element = { "notes" };
PostData.UpdateIssue(issue.ToString(), data, element);
}
else
{
string updateQuery = "Update IssueStatusTracking set Enabled='True' where Project_id='" + projectId + "'";
dc.InsertUpdateDelete(updateQuery);
string[] data = { "Website ping continues to fail: " + DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + " Url: " + liveUrl };
string[] element = { "notes" };
PostData.UpdateIssue(issue.ToString(), data, element);
}
}
else if (responseCode == 200 && flag == true)
{
//close issue in redmine
string[] data = { "5" };//status id for closed issue
string[] element = { "status_id" };
PostData.UpdateIssue(issue.ToString(), data, element);
//update issue in database
string updateQuery = "Update IssueStatusTracking set Enabled='False' where Project_id='" + projectId + "'";
dc.InsertUpdateDelete(updateQuery);
}
else if (responseCode == 200 && issue > 0 && flag == false)
{
//delete issueId in sql
string deleteIssueQuery = "Delete from IssueStatusTracking where Isssue_id='" + issue + "' and Enabled='false'";
dc.InsertUpdateDelete(deleteIssueQuery);
}
return "Completed";
}
catch (Exception ex)
{
ErrorLogging.WriteErrorLog("Exception CheckStatus: " + ex.ToString());
return "Failed";
}
}
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");
}
/// <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();
}
}
