///// <summary> ///// Returns DataTable of MessageGroups from iPhone backup file. ///// </summary> ///// <param name="dbFile"></param> ///// <returns></returns> //private List<iPhoneBackup.MessageGroup> getMessageGroupsFromFile(string dbFile) //{ // //DataTable dt = new DataTable(); // //dt.Columns.Add("Value", typeof(string)); // //dt.Columns.Add("Display", typeof(string)); // //dt.DefaultView.Sort = "Value ASC"; // List<iPhoneBackup.MessageGroup> groups = new List<iPhoneBackup.MessageGroup>(); // if (dbFile != null) // { // // open SQLite data file // Stopwatch sw = Stopwatch.StartNew(); // SQLiteConnection m_dbConnection; // m_dbConnection = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Read Only=True;FailIfMissing=True;"); // m_dbConnection.Open(); // // select the data // // joins chat_handle_join to handle on ch.handle_id = h.ROWID // // joins chat_handle_join to chat_message_join via where clause (chj.chat_id = cmj.chat_id) grouped on chj.chat_id // // joins chat_message_join to message on cmj.message_id = m.ROWID // // joins message to handle on m.handle_id = h.ROWID // // h.ROWID <- chj.handle_id,chj.chat_id -> cmj.chat_id,cmj.message_id -> m.ROWID,m.handle_id -> h.ROWID // string sql = "SELECT DISTINCT (SELECT GROUP_CONCAT(h.id) FROM chat_handle_join ch INNER JOIN handle h on h.ROWID = ch.handle_id " + // "WHERE ch.chat_id = cm.chat_id Group By ch.chat_id) as chatgroup, cm.chat_id FROM chat_message_join cm " + // "INNER JOIN message m ON cm.message_id = m.ROWID INNER JOIN handle h ON m.handle_id = h.ROWID ORDER BY chatgroup;"; // //sql = //"SELECT " + // // "SELECT Distinct GROUP_CONCAT(h.id) As chatgroup " + // // "FROM chat_handle_join chj " + // // "INNER JOIN handle h On h.ROWID = chj.handle_id " + // // //"WHERE chj.chat_id = cmj.chat_id " + // // "Inner Join chat_message_join cmj On cmj.chat_id = chj.chat_id " + // // "GROUP BY chj.chat_id " + // // //"FROM chat_message_join cmj " + // // //"INNER JOIN message m ON cmj.message_id = m.ROWID " + // // //"INNER JOIN handle h ON m.handle_id = h.ROWID " + // // "ORDER BY chatgroup;"; // // these are all the groups, even those with no messages // // it's at least 3 times faster // //sql = "SELECT GROUP_CONCAT(h.id) as ChatGroup " + // // "From chat_handle_join chj " + // // "Inner Join handle h on h.ROWID = chj.handle_id " + // // "Group By chj.chat_id " + // // "Order By ChatGroup;"; // SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); // SQLiteDataReader row = command.ExecuteReader(); // while (row.Read()) // { // if (row["chatgroup"].ToString().Trim() != "") // { // List<string> ids = (row["chatgroup"] as string).Split(",".ToCharArray()).ToList(); // //ids.Sort(); // // add and prettify US 11-digit phone numbers // groups.Add(new iPhoneBackup.MessageGroup() { ChatId = (long)row["chat_id"], Ids = ids }); // //dt.Rows.Add(row["chatgroup"] as string, Regex.Replace(row["chatgroup"].ToString(), @"\+1(\d{3})(\d{3})(\d{4})\b", "($1)$2-$3")); // } // } // row.Close(); // m_dbConnection.Close(); // TraceInformation("groups: {0} ms", sw.ElapsedMilliseconds); // } // return groups; //} //string Reorder(string s) //{ // List<string> t = s.Split(",".ToCharArray()).ToList(); // t.Sort(); // return string.Join(", ", t); //} /// <summary> /// /// </summary> private void PreviewGroup(iPhoneBackup.MessageGroup group) { if (_backup == null) { return; } //bool isGroupMessage = group.Ids.Count > 1; lbPreview.Items.Clear(); Stopwatch sw = Stopwatch.StartNew(); List <iPhoneBackup.Message> messages = _backup.GetMessages(group.ChatId); TraceInformation("messages: {0} ms", sw.ElapsedMilliseconds); lbPreview.Items.Add(string.Format("Group {0}: {1}", group.ChatId, group.ToString())); // fields: date, service, direction, id, text, filereflist List <string> list = new List <string>(messages.Count); foreach (var m in messages) { if (m.Incoming) { list.Add("< " + m.Text); } else { list.Add("> " + m.Text); } } lbPreview.Items.AddRange(list.ToArray()); }
/// <summary> /// Export all messages for MessageGroup in backup file into HTML file. (THREAD) /// </summary> private void exportHTMLForMessageGroup(iPhoneBackup.MessageGroup group) { StringBuilder sb = new StringBuilder(); //string group = string.Join(",", grp.Ids); //bool isGroupMessage = group.Ids.Count > 1; // (group.Contains(",")) ? true : false; // query database if (dbFile != null) { // open SQLite data file SQLiteConnection m_dbConnection; m_dbConnection = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Read Only=True;FailIfMissing=True;"); m_dbConnection.Open(); int totalMessages = 1; Stopwatch sw = Stopwatch.StartNew(); List <iPhoneBackup.Message> msgs = _backup.GetMessages(group.ChatId); List <iPhoneBackup.MessageGroup> chats = _backup.GetChats(); iPhoneBackup.MessageGroup chat; totalMessages = msgs.Count; TraceInformation("get messages: {0} ms", sw.ElapsedMilliseconds); sw.Restart(); sb.AppendLine(iPhoneSMS.Properties.Resources.HTMLHeaders); // add html headers sb.AppendLine("<BODY>"); sb.AppendFormat("<H1>Messages from {0}</H1>\n", group.ToString()); sb.AppendFormat("<H2>as of {0}</H2>\n", dbFileDate); sb.AppendLine("<DIV id=\"messages\">"); // fields: date, service, direction, id, text, filereflist int i = 0; string mFile; foreach (iPhoneBackup.Message row in msgs) { string content = row.Text; // (string)row["text"]; sb.AppendFormat("<DIV class=\"message message-{0}-{1}\">\n", row.Incoming ? "RCVD" : "SENT", row.Type); // row["direction"], row["service"]); sb.AppendFormat("<DIV class=\"timestamp-placeholder\"></DIV><DIV class=\"timestamp\">{0}</DIV>\n", row.Timestamp); // row["date"]); //Assert(isGroupMessage == row.IsGroup); chat = chats.Find(q => q.ChatId == row.ChatId); if (chat.Ids.Count > 1) // it's a group chat { sb.AppendFormat("<DIV class=\"sender\">{0}</DIV>\n", _iPhone.IdToName(row.Sender)); // row["id"]); } // replace image placeholders () with image files //if (row["filereflist"].ToString().Length > 0) if (row.Attachments != null && row.Attachments.Count > 0) { //List<string> mediaFileList = row["filereflist"].ToString().Split(',').ToList(); foreach (var mediaFile in row.Attachments) { mFile = mediaFile.FileName; if (mFile.StartsWith("~")) { mFile = "MediaDomain-" + mFile.Substring(2); } else { mFile = "MediaDomain-" + mFile.Substring(12); } mFile = MiscUtil.getSHAHash(mFile); mFile = mFile.Substring(0, 2) + "\\" + mFile; mFile = Path.Combine(dbFileDir, mFile); string replace = null; // get extension of mediaFile switch (mediaFile.FileName.Substring(mediaFile.FileName.LastIndexOf('.')).ToLower()) { // image case ".jpeg": case ".jpg": case ".png": replace = string.Format("<img src=\"{0}\"><!-- {1}//-->", mFile, mediaFile); break; case ".mov": replace = string.Format("<video controls='controls' width='300' name='video' src='{0}'></video>", mFile); break; case ".vcf": string contents = File.ReadAllText(mFile); int j = contents.IndexOf("FN:"); int k = contents.IndexOf("\r", j + 3); contents = contents.Substring(j + 3, k - j - 3); replace = "Contact: " + contents; break; case ".pluginpayloadattachment": replace = string.Format("<p>Link: {0}</p>", mFile); replace = content; break; default: replace = ""; break; } // do switch statement for replacement string Regex rgx = new Regex(@"\[MEDIA\]"); content = rgx.Replace(content, replace, 1); } } sb.AppendFormat("<DIV class=\"content\">{0}</DIV>\n", content); sb.AppendLine("</DIV>"); i++; backgroundWorker1.ReportProgress(i * 100 / totalMessages); } sb.AppendLine("</DIV>"); sb.AppendLine("</BODY>"); sb.AppendLine("</HTML>"); TraceInformation("Creating html: {0} ms", sw.ElapsedMilliseconds); //set.Dispose(); //adpt.Dispose(); m_dbConnection.Close(); } // regex replacements // REGEX: change phone number format (+12257490000 => (225)749-0000) string htmlOutput = sb.ToString(); htmlOutput = Regex.Replace(htmlOutput, @"\+1(\d{3})(\d{3})(\d{4})\b", "($1) $2-$3"); // REGEX: change date format (2015-01-01 00:00 => 01/01/2015 12:00am) htmlOutput = Regex.Replace(htmlOutput, @"(\d{4})-(\d{2})-(\d{2})\s(\d{2}):(\d{2}):(\d{2})", delegate(Match match) { int hour = int.Parse(match.ToString().Substring(11, 2)); string suffix = (hour < 12) ? "am" : "pm"; if (hour == 0) { hour += 12; } else if (hour > 12) { hour -= 12; } string replace = "$2/$3/$1 " + hour + ":$5" + suffix; return(match.Result(replace)); }); // output html data string file = Path.GetTempFileName() + ".html"; File.WriteAllText(file, htmlOutput); ShowBrowser(file); return; }