using System; using System.Text; using System.Threading.Tasks; using System.Collections.Generic; using System.Linq; using Microsoft.EntityFrameworkCore; using rockfishCore.Models; using System.Net; using System.Net.Http; using System.Net.Http.Headers; //using System.Xml; using System.Xml.Linq; using System.IO; namespace rockfishCore.Util { //Import fogbugz stuff into our own db //called by schema update 7 //does not modify FB at all //can be called any time, won't re-import public static class FBImporter { public const long LAST_EXISTING_CASE_IN_FOGBUGZ = 3315; public static void Import(rockfishContext ct) { //fogbugz api token, can get it by using the FB UI, open dev tools, be logged in, do something and watch the token get sent in the network traffic string sToken = "jvmnpkrvc1i7hc6kn6dggqkibktgcc"; long nCase = 0; bool bDone = false; while (!bDone) { //increment until we reach the end (case 3309 as of now) nCase++; if (nCase > LAST_EXISTING_CASE_IN_FOGBUGZ) { bDone = true; break; } //no need for this, we wipe clean before every import now // //do we already have this record? // if (ct.RfCase.Any(e => e.Id == nCase)) // { // continue; // } fbCase fb = getCase(nCase, sToken); //copy over to db portCase(fb, ct); } } private static Dictionary dictProjects = new Dictionary(); /////////////////////////////////////////////// //Port a single case over to Rockfish // private static void portCase(fbCase f, rockfishContext ct) { long lProjectId = 0; if (f.noRecord) { //save as *DELETED* record //Only setting required fields RfCase c = new RfCase(); c.Priority = 5; c.Title = "deleted from FogBugz"; c.RfCaseProjectId = 1;//has to be something ct.RfCase.Add(c); ct.SaveChanges(); } else { if (dictProjects.ContainsKey(f.project)) { lProjectId = dictProjects[f.project]; } else { //need to insert it RfCaseProject t = new RfCaseProject(); t.Name = f.project; ct.RfCaseProject.Add(t); ct.SaveChanges(); dictProjects.Add(f.project, t.Id); lProjectId = t.Id; } RfCase c = new RfCase(); c.DtClosed = f.closed; c.DtCreated = f.created; c.Notes = f.notes; c.Priority = f.priority; if (c.Priority > 5) c.Priority = 5; c.Title = f.title; c.RfCaseProjectId = lProjectId; ct.RfCase.Add(c); ct.SaveChanges(); if (f.attachments.Count > 0) { foreach (fbAttachment att in f.attachments) { RfCaseBlob blob = new RfCaseBlob(); blob.File = att.blob; blob.Name = att.fileName; blob.RfCaseId = c.Id; ct.RfCaseBlob.Add(blob); ct.SaveChanges(); } } } // Console.WriteLine("***************************************************************************"); Console.WriteLine("~~~~~~~~~~~~~~~~~~~~ PORTCASE: " + f.id); } /////////////////////////////////////////////// //fetch a single case // private static fbCase getCase(long caseNumber, string sToken) { //Use this url //https://fog.ayanova.com/api.asp?cmd=search&cols=ixBug,ixBugParent,sTitle,sProject,ixPriority, sPriority,fOpen,ixStatus,sStatus,dtOpened,dtResolved,dtClosed,ixRelatedBugs,events&token=jvmnpkrvc1i7hc6kn6dggqkibktgcc&q=3308 //TEST TEST TEST // caseNumber = 3279; string url = "https://fog.ayanova.com/api.asp?cmd=search&cols=ixBug,ixBugParent,sTitle,sProject,ixPriority,sPriority,fOpen,ixStatus,sStatus,dtOpened,dtResolved,dtClosed,ixRelatedBugs,events&token=" + sToken + "&q=" + caseNumber.ToString(); var httpClient = new HttpClient(); var result = httpClient.GetAsync(url).Result; var stream = result.Content.ReadAsStreamAsync().Result; var x = XElement.Load(stream); fbCase f = new fbCase(); //are we done? string scount = (from el in x.DescendantsAndSelf("cases") select el).First().Attribute("count").Value; if (scount == "0") { f.noRecord = true; return f; } //Got record, process it... f.title = getValue("sTitle", x); f.project = getValue("sProject", x); f.id = getValue("ixBug", x); f.priority = int.Parse(getValue("ixPriority", x)); f.created = Util.DateUtil.ISO8601StringToEpoch(getValue("dtOpened", x)); f.closed = Util.DateUtil.ISO8601StringToEpoch(getValue("dtClosed", x)); //string DTB4 = getValue("dtOpened", x); //string DTAFTER = Util.DateUtil.ISO8601StringToLocalDateTime(DTB4); //NOTES / ATTACHMENTS StringBuilder sbNotes = new StringBuilder(); //events var events = (from el in x.Descendants("event") select el).ToList(); bool bFirstNoteAdded = false; foreach (var e in events) { string sNote = getValue("s", e); if (!string.IsNullOrWhiteSpace(sNote)) { if (bFirstNoteAdded) { sbNotes.AppendLine("===================="); } bFirstNoteAdded = true; sbNotes.Append(Util.DateUtil.ISO8601StringToLocalDateTime(getValue("dt", e))); sbNotes.Append(" "); sbNotes.AppendLine(getValue("evtDescription", e)); sbNotes.AppendLine("____________________"); sbNotes.AppendLine(sNote); } //GET ATTACHMENTS var attaches = (from l in e.Descendants("attachment") select l).ToList(); foreach (var a in attaches) { fbAttachment fbat = new fbAttachment(); fbat.fileName = getValue("sFileName", a); string fileUrl = "https://fog.ayanova.com/" + System.Net.WebUtility.HtmlDecode(getValue("sURL", a)) + "&token=" + sToken; ///// using (var aclient = new HttpClient()) { var aresponse = aclient.GetAsync(fileUrl).Result; if (aresponse.IsSuccessStatusCode) { // by calling .Result you are performing a synchronous call var responseContent = aresponse.Content; // by calling .Result you are synchronously reading the result var astream = responseContent.ReadAsStreamAsync().Result; MemoryStream ams = new MemoryStream(); astream.CopyTo(ams); fbat.blob = ams.ToArray(); } } ///// //bugbug: Looks like this is always the same size, maybe it's getting an error page? // result = httpClient.GetAsync(url).Result; // stream = result.Content.ReadAsStreamAsync().Result; // MemoryStream ms = new MemoryStream(); // stream.CopyTo(ms); // fbat.blob = ms.ToArray(); f.attachments.Add(fbat); } }//bottom of events loop if (sbNotes.Length > 0) { f.notes = sbNotes.ToString(); } return f; } ///////////////////////////////// // Get header element value // private static string getValue(string sItem, XElement x) { return (string)(from el in x.Descendants(sItem) select el).First(); } ///////////////////////////////////// //dto object public class fbCase { public fbCase() { attachments = new List(); } public bool noRecord { get; set; } public string id { get; set; } public string title { get; set; } public string project { get; set; } public int priority { get; set; } public string notes { get; set; } public long? created { get; set; } public long? closed { get; set; } public List attachments { get; set; } } public class fbAttachment { public string fileName { get; set; } public byte[] blob { get; set; } } //eoc } //eons } /* 3308 0 1 true 20 2017-08-06T19:11:54Z 3240 18150 1 3 0
2017-08-06T19:11:54Z
false false false false false And porting all existing data to rf including old closed cases etc.
Also must be searchable, generate case numbers, have category / project, and priority, accept file attachments like screenshots etc.
]]> 18151 3 3 3
2017-08-06T19:11:55Z
false false false false false 18152 2 3 0
2017-08-07T21:18:55Z
false false false false false https://fog.ayanova.com/api.asp?cmd=search&cols=ixBug,sTitle,fOpen,ixStatus,dtOpened,dtResolved,dtClosed,ixRelatedBugs,events&token=jvmnpkrvc1i7hc6kn6dggqkibktgcc&q=3240

This is for case 3240 (q=3240) and the token I got from just viewing the network traffic using Fogbugz (which works fine).


The response is an xml document with a case/cases/case header and then an events collection under that which contains all the text added and other events like chaging the title etc which we don't care about.  Also it contains urls for attachments to download and can use the url directly to get the attachment.

]]> 18153 2 3 0
2017-08-07T21:21:02Z
Where cases count=0 and would normally be one and the cases branch would have the one case fetched.]]> false false false false false
The procedure will be to start at 1 and fetch every case +1 until we get a response like this:
<?xml version="1.0" encoding="UTF-8"?><response><cases count="0"></cases></response>

Where cases count=0 and would normally be one and the cases branch would have the one case fetched.]]> 18154 2 3 0
2017-08-07T21:24:48Z
false false false false false
PROJECTS - all of these in a table

CASES - every case:

CASE HEADER:
Case # (id), Title, project, priority
  CASE EVENTS:
    - s (string of text (CDATA))
    - Any attachments

]]> 18155 2 3 0
2017-08-07T21:34:27Z
false false false false false 18156 2 3 0
2017-08-07T21:37:37Z
false false false false false
New db tables:
CASE
CASEPROJECT
CASEBLOB

CASE Fields:
=-=-=-=-=-=-
ID (integer not null, case # autoincrement),
TITLE text not null,
PROJECT (fk CASEPROJECT not null),
PRIORITY (integer 1-5 not null),
NOTES (text nullable),
CREATED (date time as integer, nullable),
CLOSED (date time as integer, nullable, also serves as closed indicator or open indicator if null),
RELEASEVERSION (text nullable, public release version that fixed the item, i.e. if it was qboi 7.5 patch 1 then this would be 7.5.1),
RELEASENOTES (text nullable, single string of text that serves as customer description of fix).

CASEPROJECT fields:
=-=-=-=-=-=-=-=-=-=-
id (integer not null pk autoincrement),
name (text not null, project name i.e. QBOI)

CASEBLOB fields
id (integer not null pk autoincrement)
CASEID (fk not null link to case)
FILE (blob not null)

]]> 18157 2 3 0
2017-08-07T22:13:24Z
false false false false false 18158 2 3 0
2017-08-07T22:13:32Z
false false false false false 18159 2 3 0
2017-08-07T22:29:29Z
false false false false false 18160 2 3 0
2017-08-07T22:49:22Z
false false false false false */