public IEnumerable <IMessageItem> getMessages(string ChatName)
{
using (var sqlite_command = sqlite_connection.CreateCommand())
{
sqlite_command.CommandText =
"SELECT M.Z_PK, M.ZMESSAGESTATUS, M.ZMESSAGETYPE, M.ZMESSAGEDATE, M.ZPUSHNAME, " +
" M.ZTEXT, M.ZTOJID, M.ZFROMJID, C.ZCONTACTNAME, C.ZMEMBERJID " +
"FROM ZWAMESSAGE AS M " +
" LEFT JOIN ZWAGROUPMEMBER AS C ON M.ZGROUPMEMBER = C.Z_PK " +
"WHERE M.ZCHATSESSION = " + ChatName;
SQLiteDataReader sqlite_datareader = sqlite_command.ExecuteReader();
Messages = new List <IOSMessageItem>();
while (sqlite_datareader.Read())
{
var values = sqlite_datareader.GetValues();
var item = new IOSMessageItem();
item.Z_PK = int.Parse("0" + values["Z_PK"] + "");
item.ZMESSAGESTATUS = int.Parse("0" + values["ZMESSAGESTATUS"] + "");
item.ZMESSAGETYPE = int.Parse("0" + values["ZMESSAGETYPE"] + "");
item.ZMESSAGEDATE = TimeStampToDateTime(double.Parse("0" + values["ZMESSAGEDATE"], CultureInfo.InvariantCulture));
item.ZFROMJID = values["ZFROMJID"] + "";
item.ZPUSHNAME = values["ZPUSHNAME"] + "";
item.ZTEXT = values["ZTEXT"] + "";
item.ZTOJID = values["ZTOJID"] + "";
item.ZCONTACTNAME = values["ZCONTACTNAME"] + "";
item.ZMEMBERJID = values["ZMEMBERJID"] + "";
Messages.Add(item);
}
return(Messages.OrderBy(o => o.ZMESSAGEDATE).ToList());
}
}
public IEnumerable <IMessageItem> getMessages(string ChatName)
{
using (var sqlite_command = sqlite_connect.CreateCommand())
{
if (Msg == null)
{
if (!this.savedDB)
{
var trans = sqlite_connect.BeginTransaction();
try
{
sqlite_command.CommandText = "DROP TABLE IF EXISTS ZTMPMESSAGEVIEW";
sqlite_command.ExecuteNonQuery();
sqlite_command.CommandText = "CREATE TABLE ZTMPMESSAGEVIEW AS SELECT M.Z_PK," +
" M.ZMESSAGESTATUS," +
" M.ZMESSAGETYPE," +
" M.ZMESSAGEDATE," +
" M.ZPUSHNAME," +
" M.ZGROUPMEMBER," +
" '' AS ZTITLE," +
" '' AS ZMEDIALOCALPATH," +
" M.ZTEXT," +
" M.ZTOJID," +
" M.ZFROMJID," +
" O.ZCONTACTNAME," +
" O.ZMEMBERJID," +
" M.ZCHATSESSION," +
" M.ZMEDIAITEM," +
" '' AS fileID" +
" FROM " +
" (SELECT Z_PK," +
" ZMESSAGESTATUS," +
" ZMESSAGETYPE," +
" ZMESSAGEDATE," +
" ZPUSHNAME," +
" ZTEXT," +
" ZTOJID," +
" ZFROMJID," +
" ZCHATSESSION," +
" ZGROUPMEMBER," +
" '' AS ZMEDIAITEM" +
" FROM chatDB.ZWAMESSAGE" +
" WHERE ZMESSAGETYPE = 0 ) AS M" +
" LEFT JOIN chatDB.ZWAGROUPMEMBER AS O ON M.ZGROUPMEMBER = O.Z_PK" +
" UNION" +
" SELECT X.*," +
" Y.fileID" +
" FROM" +
" (SELECT M.Z_PK," +
" M.ZMESSAGESTATUS," +
" M.ZMESSAGETYPE," +
" M.ZMESSAGEDATE," +
" M.ZPUSHNAME," +
" M.ZGROUPMEMBER," +
" N.ZTITLE," +
" 'Message/' || N.ZMEDIALOCALPATH AS ZMEDIALOCALPATH," +
" M.ZTEXT," +
" M.ZTOJID," +
" M.ZFROMJID," +
" O.ZCONTACTNAME," +
" O.ZMEMBERJID," +
" M.ZCHATSESSION," +
" M.ZMEDIAITEM" +
" FROM" +
" (SELECT Z_PK," +
" ZMESSAGESTATUS," +
" ZMESSAGETYPE," +
" ZMESSAGEDATE," +
" ZPUSHNAME," +
" ZTEXT," +
" ZTOJID," +
" ZFROMJID," +
" ZCHATSESSION," +
" ZGROUPMEMBER," +
" ZMEDIAITEM" +
" FROM chatDB.ZWAMESSAGE" +
" WHERE ZMEDIAITEM IS NOT NULL ) AS M" +
" LEFT JOIN chatDB.ZWAGROUPMEMBER AS O ON M.ZGROUPMEMBER = O.Z_PK" +
" LEFT JOIN chatDB.ZWAMEDIAITEM AS N ON M.ZMEDIAITEM = N.Z_PK" +
" WHERE ZMESSAGETYPE IN (1, 2, 4, 8, 11,15)" +
" ORDER BY ZMEDIALOCALPATH) AS X" +
" LEFT JOIN" +
" (SELECT fileID," +
" relativePath" +
" FROM manifestDB.Files" +
" WHERE flags = 1" +
" AND DOMAIN LIKE 'AppDomainGroup-group.net.whatsapp%'" +
" AND relativePath LIKE 'Message/%'" +
" AND relativePath NOT LIKE '%thumb'" +
" ORDER BY relativePath) AS Y ON Y.relativePath = X.ZMEDIALOCALPATH";
sqlite_command.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
}
}
sqlite_command.CommandText = "SELECT * FROM ZTMPMESSAGEVIEW";
SQLiteDataReader sqlite_datareader = sqlite_command.ExecuteReader();
Msg = new Dictionary <int, List <IOSMessageItem> >();
while (sqlite_datareader.Read())
{
var values = sqlite_datareader.GetValues();
var item = new IOSMessageItem();
item.Z_PK = int.Parse("0" + values["Z_PK"] + "");
item.ZMESSAGESTATUS = int.Parse("0" + values["ZMESSAGESTATUS"] + "");
item.ZMESSAGETYPE = int.Parse("0" + values["ZMESSAGETYPE"] + "");
item.ZMESSAGEDATE = TimeStampToDateTime(double.Parse("0" + values["ZMESSAGEDATE"], CultureInfo.InvariantCulture));
item.ZFROMJID = values["ZFROMJID"] + "";
item.ZPUSHNAME = values["ZPUSHNAME"] + "";
item.ZTEXT = values["ZTEXT"] + "";
item.ZTOJID = values["ZTOJID"] + "";
item.ZCONTACTNAME = values["ZCONTACTNAME"] + "";
item.ZMEMBERJID = values["ZMEMBERJID"] + "";
item.CHATSESSIONID = int.Parse("0" + values["ZCHATSESSION"] + "");
item.ZTITLE = values["ZTITLE"] + "";
item.ZMEDIALOCALPATH = values["ZMEDIALOCALPATH"] + "";
//var relativePath = values["ZMEDIALOCALPATH"] + "";
var fileID = values["fileID"] + "";
if (!string.IsNullOrWhiteSpace(fileID))
{
var dir = fileID.Substring(0, 2);
item.localFilePath = this.backupDirectory + "\\" + dir + "\\" + fileID;
}
List <IOSMessageItem> message;
if (Msg.TryGetValue(item.CHATSESSIONID, out message))
{
message.Add(item);
}
else
{
message = new List <IOSMessageItem>();
message.Add(item);
Msg.Add(item.CHATSESSIONID, message);
}
}
}
List <IOSMessageItem> message1;
if (Msg.TryGetValue(int.Parse("0" + ChatName), out message1))
{
if (!string.IsNullOrWhiteSpace(searchText))
{
return(message1.Where(o => o.message.ToLower().Contains(searchText.ToLower())).OrderBy(o => o.ZMESSAGEDATE).ToList());
}
else
{
return(message1.OrderBy(o => o.ZMESSAGEDATE).ToList());
}
}
else
{
return(null);
}
}
}
