private void SaveJiraDataToDb(JiraPageInfo jiraData)
{
using (var con = new SQLiteConnection(SQLiteHelper.LocalDbConnectionString))
{
this.deleteJira(jiraData.JiraNumber);
string sqlstr = "insert into JiraPageInfo (JiraNumber,CreateTime,UpdateTime,LastestStatus,Title) values (@JiraNumber,@CreateTime,@UpdateTime,@LastestStatus,@Title)";
SQLiteParameter[] parameters =
{
new SQLiteParameter("@JiraNumber", jiraData.JiraNumber.ToUpper()),
new SQLiteParameter("@CreateTime", DateTime.Now),
new SQLiteParameter("@UpdateTime", DateTime.Now),
new SQLiteParameter("@LastestStatus", jiraData.LastestStatus),
new SQLiteParameter("@Title", jiraData.Title)
};
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString,
sqlstr, CommandType.Text, parameters);
foreach (var sitem in jiraData.StatusLogs)
{
sqlstr = "insert into StatusChangeItem (JiraNumber,ActionDateTime,NewValue,OldValue) values (@JiraNumber,@ActionDateTime,@NewValue,@OldValue)";
SQLiteParameter[] parasitem =
{
new SQLiteParameter("@JiraNumber", sitem.JiraNumber.ToUpper()),
new SQLiteParameter("@ActionDateTime", sitem.ActionDateTime),
new SQLiteParameter("@NewValue", sitem.NewValue),
new SQLiteParameter("@OldValue", sitem.OldValue)
};
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString,
sqlstr, CommandType.Text, parasitem);
}
}
}
private JiraPageInfo ConvertJiraPage(JToken jiradata)
{
var data = new JiraPageInfo();
data.JiraNumber = jiradata["key"].ToString();
data.Title = jiradata["fields"]["summary"].ToString();
data.LastestStatus = jiradata["fields"]["status"]["name"].ToString();
//data.CreateTime = DateTime.Now;
//data.UpdateTime = DateTime.Now;
data.StatusLogs = jiradata["changelog"]["histories"]
.SelectMany(x => x["items"].Where(xx => xx["field"].ToString() == "status")
.Select(item => new StatusChangeItem()
{
JiraNumber = jiradata["key"].ToString(),
ActionDateTime = DateTime.Parse(x["created"].ToString()),
OldValue = item["fromString"].ToString(),
NewValue = item["toString"].ToString()
})).ToList();
return(data);
}
private JiraPageInfo GetJiraDataFromDb(string jiranumbers)
{
JiraPageInfo jdata = null;
using (var con = new SQLiteConnection(SQLiteHelper.LocalDbConnectionString))
{
var dr = SQLiteHelper.ExecuteReader(con,
"select t1.Title,t1.JiraNumber,t1.CreateTime,t1.UpdateTime," +
"t2.OldValue,t2.NewValue,t2.ActionDateTime from JiraPageInfo t1 " +
"left join StatusChangeItem t2 on t1.JiraNumber=t2.JiraNumber " +
"where t1.JiraNumber = '" + jiranumbers + "'", CommandType.Text);
if (dr.Read())
{
var data = dr.IsDBNull(0) || dr.IsDBNull(1) || dr.IsDBNull(2) || dr.IsDBNull(3) || dr.IsDBNull(4) || dr.IsDBNull(5);
jdata = new JiraPageInfo();
jdata.Title = dr["Title"]?.ToString();
jdata.JiraNumber = dr["JiraNumber"]?.ToString();
jdata.CreateTime = (DateTime)dr["CreateTime"];
jdata.UpdateTime = (DateTime)dr["UpdateTime"];
if (!dr.IsDBNull(4))
{
var status = new StatusChangeItem();
status.OldValue = dr["OldValue"]?.ToString();
status.NewValue = dr["NewValue"]?.ToString();
status.ActionDateTime = (DateTime)dr["ActionDateTime"];
jdata.StatusLogs.Add(status);
while (dr.Read())
{
status = new StatusChangeItem();
status.OldValue = dr["OldValue"]?.ToString();
status.NewValue = dr["NewValue"]?.ToString();
status.ActionDateTime = (DateTime)dr["ActionDateTime"];
jdata.StatusLogs.Add(status);
}
}
}
}
return(jdata);
}
