public IcqAccount Get(int uin)
{
IcqAccount result = null;
try
{
using (var conn = new SqlConnection(_cnString))
{
using (var cmd = new SqlCommand("[dbo].[ICQ_ACCOUNT_GET_BY_UIN]", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@uin", uin);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
result = new IcqAccount();
result.Uin = dr.GetInt32(dr.GetOrdinal("UIN"));
result.Nickname = dr.GetString(dr.GetOrdinal("NICKNAME"));
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(result);
}
public void Add(IcqAccount account)
{
int rowCount = 0;
try
{
using (var conn = new SqlConnection(_cnString))
{
using (var cmd = new SqlCommand("[dbo].[ICQ_ACCOUNT_ADD]", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@uin", account.Uin);
cmd.Parameters.AddWithValue("@nick", account.Nickname);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
rowCount = dr.GetInt32(dr.GetOrdinal("ROWCOUNT"));
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public int LoadToDb(string qhfFilePath, out int totalMsgs)
{
var messages = new List <IcqMessage>();
var account = new IcqAccount();
using (QHFReader reader = new QHFReader(qhfFilePath))
{
account.Nickname = reader.Nick;
account.Uin = Convert.ToInt32(reader.Uin);
var message = new QHFMessage();
while (reader.GetNextMessage(message))
{
messages.Add(new IcqMessage(account.Uin, message.IsMy, message.Time, message.Text));
}
}
messages = messages.OrderBy(ks => ks.DateTime).ToList();
totalMsgs = messages.Count;
var loader = IcqMessageLoader.GetInstance(_cnString);
IEnumerable <IcqMessage> uploadedMessages = null;
var accountLoader = IcqAccountLoader.GetInstance(_cnString);
if (accountLoader.Get(account.Uin) == null)
{
accountLoader.Add(account);
}
else
{
if (messages.First().DateTime < loader.GetLatest(account).DateTime)
{
if (messages.Last().DateTime > loader.GetEarliest(account).DateTime)
{
uploadedMessages = loader.GetRange(account, messages.First().DateTime, messages.Last().DateTime);
}
}
}
int uploadCount = 0;
if (uploadedMessages == null)
{
uploadCount = loader.AddRange(messages);
}
else
{
var selectedMessages = messages.Where(m => !uploadedMessages.Any(um => um.Equals(m))).ToList();
uploadCount = loader.AddRange(selectedMessages);
}
return(uploadCount);
}
public int LoadTxtToDb(string txtFilePath)
{
if (Path.GetExtension(txtFilePath) != ".txt")
{
throw new IOException($"Файл \"{Path.GetFileName(txtFilePath)}\" не является текстовым.");
}
var account = new IcqAccount();
var qipMessages = QipTxtReader.ReadMessages(txtFilePath);
account.Uin = Convert.ToInt32(Path.GetFileNameWithoutExtension(txtFilePath));
account.Nickname = "";
List <IcqMessage> messages = qipMessages
.Select(message => new IcqMessage(account.Uin, message.IsMy, message.Time, message.Text))
.ToList();
var loader = IcqMessageLoader.GetInstance(_cnString);
IEnumerable <IcqMessage> uploadedMessages = null;
var accountLoader = IcqAccountLoader.GetInstance(_cnString);
if (accountLoader.Get(account.Uin) == null)
{
accountLoader.Add(account);
}
else
{
if (messages.First().DateTime < loader.GetLatest(account).DateTime)
{
if (messages.Last().DateTime > loader.GetEarliest(account).DateTime)
{
uploadedMessages = loader.GetRange(account, messages.First().DateTime, messages.Last().DateTime);
}
}
}
int uploadCount = 0;
if (uploadedMessages == null)
{
uploadCount = loader.AddRange(messages);
}
else
{
var selectedMessages = messages.Where(m => !uploadedMessages.Any(um => um.Equals(m)));
uploadCount = loader.AddRange(selectedMessages);
}
return(uploadCount);
}
public IEnumerable <IcqMessage> GetRange(IcqAccount account, DateTime from, DateTime to)
{
var result = new List <IcqMessage>();
try
{
using (var conn = new SqlConnection(_cnString))
{
using (var cmd = new SqlCommand("[dbo].[ICQ_HISTORY_GET_RANGE]", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@uin", account.Uin);
cmd.Parameters.AddWithValue("@from", from);
cmd.Parameters.AddWithValue("@to", to);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var msg = new IcqMessage();
msg.Id = dr.GetInt32(dr.GetOrdinal("MESSAGE_ID"));
msg.ContactUin = dr.GetInt32(dr.GetOrdinal("CONTACT_UIN"));
msg.IsMy = dr.GetBoolean(dr.GetOrdinal("IS_MY"));
msg.DateTime = dr.GetDateTime(dr.GetOrdinal("DATE"));
msg.Text = dr.GetString(dr.GetOrdinal("TEXT"));
result.Add(msg);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(result);
}
public IcqMessage GetLatest(IcqAccount account)
{
IcqMessage result = null;
try
{
using (var conn = new SqlConnection(_cnString))
{
using (var cmd = new SqlCommand("[dbo].[ICQ_HISTORY_GET_LATEST]", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@uin", account.Uin);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
result = new IcqMessage();
result.Id = dr.GetInt32(dr.GetOrdinal("MESSAGE_ID"));
result.ContactUin = dr.GetInt32(dr.GetOrdinal("CONTACT_UIN"));
result.IsMy = dr.GetBoolean(dr.GetOrdinal("IS_MY"));
result.DateTime = dr.GetDateTime(dr.GetOrdinal("DATE"));
result.Text = dr.GetString(dr.GetOrdinal("TEXT"));
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(result);
}
