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