/// <summary>
/// gets unique file names from DB
/// <seealso cref="SharedClasses.PacketDB"/>
/// </summary>
/// <param name="date">date to check (if "NO_DATE" - it gets all file names</param>
/// <returns>List of PacketDB objects</returns>
public List <PacketDB> GetUniqueFileNames(String date)
{
string query;
if (date == "NO_DATE")
{
query = "SELECT user, date, fileName, oldFileName, fileHash, iType, id_Packet, id_files, ip, COUNT(*) as count FROM packet GROUP BY fileName ORDER BY id_Packet DESC";
}
else
{
query = String.Format("SELECT user, date, fileName, oldFileName, fileHash, iType, id_Packet, id_files, ip, COUNT(*) as count FROM packet WHERE DATE(date) = '{0}' GROUP BY fileName ORDER BY id_Packet DESC", date);
}
List <PacketDB> packets = new List <PacketDB>();
using (MySqlConnection conn = new MySqlConnection(connString))
using (MySqlCommand cmd = new MySqlCommand(query, conn))
{
conn.Open();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id_files = -666;
string oldFileName = "NULL";
if (!(reader["id_files"] is DBNull))
{
id_files = Convert.ToInt32(reader["id_files"]);
}
if (!(reader["oldFileName"] is DBNull))
{
oldFileName = reader["oldFileName"] + "";
}
PacketDB pack = new PacketDB(new Packet(reader["user"] + "",
DateTime.ParseExact(reader["date"] + "", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture),
reader["fileName"] + "", oldFileName, reader["fileHash"] + "", (WatcherInfoType)reader["iType"], 1),
Convert.ToInt32(reader["id_Packet"]),
Convert.ToInt32(id_files),
Convert.ToInt32(reader["count"]),
reader["ip"] + "");
packets.Add(pack);
}
}
}
return(packets);
}
/// <summary>
/// gets file revisions of given file name
/// </summary>
/// <param name="name">file name to check</param>
/// <returns>list of PacketDB objects</returns>
public List <PacketDB> GetFileRevisions(String name)
{
string query = String.Format("SELECT user, date, fileName, oldFileName, fileHash, iType, id_Packet, id_files, ip FROM packet WHERE fileName = '{0}' ORDER BY date DESC", name);
#if (DEBUG)
Console.WriteLine(query);
#endif
List <PacketDB> packets = new List <PacketDB>();
using (MySqlConnection conn = new MySqlConnection(connString))
using (MySqlCommand cmd = new MySqlCommand(query, conn))
{
conn.Open();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id_files = -666;
string oldFileName = "NULL";
if (!(reader["id_files"] is DBNull))
{
id_files = Convert.ToInt32(reader["id_files"]);
}
if (!(reader["oldFileName"] is DBNull))
{
oldFileName = reader["oldFileName"] + "";
}
PacketDB pack = new PacketDB(new Packet(reader["user"] + "",
DateTime.ParseExact(reader["date"] + "", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture),
reader["fileName"] + "", oldFileName, reader["fileHash"] + "", (WatcherInfoType)reader["iType"], 1),
Convert.ToInt32(reader["id_Packet"]),
Convert.ToInt32(id_files),
Convert.ToInt32(0),
reader["ip"] + "");
packets.Add(pack);
}
}
}
return(packets);
}
