public List<DailyUsageBO> AllByMonth(string username)
{
List<DailyUsageBO> result = new List<DailyUsageBO>();
DailyUsageBO record;
CultureInfo provider = CultureInfo.InvariantCulture;
try
{
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("SELECT month, SUM(upload), SUM(download), SUM(total) FROM {0} GROUP BY month ORDER BY month", username),
DataBaseFactory.Instance.GetConnection()))
{
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
record = new DailyUsageBO();
record.Month = dr.GetString(0);
record.Upload = dr.GetDouble(1);
record.Download = dr.GetDouble(2);
record.Total = dr.GetDouble(3);
record.Day = DateTime.ParseExact(record.Month, "yyyyMM", provider);
result.Add(record);
}
}
}
}
catch
{
CreateTable(username);
}
return result;
}
public DailyUsageExportBO(DailyUsageBO usage, string username)
{
Username = username;
Day = usage.Day;
Month = usage.Month;
Download = usage.Download;
Upload = usage.Upload;
Total = usage.Total;
Period = usage.Period;
/*if (String.IsNullOrEmpty(usage.Period))
Period = usage.Period;
else
Period = usage.Period;*/
}
public DailyUsageExportBO(DailyUsageBO usage, string username)
{
Username = username;
Day = usage.Day;
Month = usage.Month;
Download = usage.Download;
Upload = usage.Upload;
Total = usage.Total;
Period = usage.Period;
/*if (String.IsNullOrEmpty(usage.Period))
* Period = usage.Period;
* else
* Period = usage.Period;*/
}
private void ReadDailyWiredUsage(string username, WiredDailyUsage usage, ref bool stop)
{
// Mettre à jours les données quotidienne
if (usage.Day.Date == DateTime.Now.Date)
{
Account.Account.CurrentDayUpload = usage.Upload;
Account.Account.CurrentDayDownload = usage.Download;
}
if (DataBaseFactory.Instance.IsAvailable)
{
DailyUsageBO dailyUsage = new DailyUsageBO()
{
Day = usage.Day,
Download = usage.Download,
Month = usage.Month,
Total = usage.Total,
Upload = usage.Upload,
Period = usage.Period
};
// Vérifier si le jours existe déjà dans la BD
DailyUsageBO data;
if (DailyUsageDAO.Instance.Exist(username, dailyUsage, out data))
{
// Vérifier si les données sont différente
if (dailyUsage.Download != data.Download || dailyUsage.Upload != data.Upload || dailyUsage.Total != data.Total)
DailyUsageDAO.Instance.Update(username, dailyUsage);
else if (!_forceUsageRefresh)
stop = true;
}
else
DailyUsageDAO.Instance.Insert(username, dailyUsage);
}
}
private DailyUsageBO ReadRecord(SqlCeDataReader reader)
{
DailyUsageBO result = new DailyUsageBO();
result.Day = reader.GetDateTime(0);
result.Month = reader.GetString(1);
result.Upload = reader.GetDouble(2);
result.Download = reader.GetDouble(3);
result.Total = reader.GetDouble(4);
if (!reader.IsDBNull(5))
result.Period = new Period(reader.GetString(5));
return result;
}
public void Update(string username, DailyUsageBO dailyUsage)
{
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("UPDATE {0} SET upload=@upload, download=@download, total=@total WHERE day=@day", username),
DataBaseFactory.Instance.GetConnection()))
{
cmd.Parameters.Add(new SqlCeParameter("@upload", SqlDbType.Float) { Value = dailyUsage.Upload });
cmd.Parameters.Add(new SqlCeParameter("@download", SqlDbType.Float) { Value = dailyUsage.Download });
cmd.Parameters.Add(new SqlCeParameter("@total", SqlDbType.Float) { Value = dailyUsage.Total });
cmd.Parameters.Add(new SqlCeParameter("@day", SqlDbType.DateTime) { Value = dailyUsage.Day });
cmd.ExecuteNonQuery();
}
}
public void Insert(string username, DailyUsageBO dailyUsage)
{
try
{
if (dailyUsage.Total != 0)
{
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("INSERT INTO {0} (day, month, upload, download, total, period) VALUES (@day, @month, @upload, @download, @total, @period)", username),
DataBaseFactory.Instance.GetConnection()))
{
cmd.Parameters.Add(new SqlCeParameter("@day", SqlDbType.DateTime) { Value = dailyUsage.Day });
cmd.Parameters.Add(new SqlCeParameter("@month", SqlDbType.NChar) { Value = dailyUsage.Day.Year.ToString() + dailyUsage.Day.Month.ToString().PadLeft(2, '0') });
cmd.Parameters.Add(new SqlCeParameter("@upload", SqlDbType.Float) { Value = dailyUsage.Upload });
cmd.Parameters.Add(new SqlCeParameter("@download", SqlDbType.Float) { Value = dailyUsage.Download });
cmd.Parameters.Add(new SqlCeParameter("@total", SqlDbType.Float) { Value = dailyUsage.Total });
cmd.Parameters.Add(new SqlCeParameter("@period", SqlDbType.NChar) { Value = dailyUsage.Period.ToString() });
cmd.ExecuteNonQuery();
}
}
}
catch (Exception sql)
{
if (!DataBaseFactory.Instance.TableExist(username))
{
CreateTable(username);
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("INSERT INTO {0} (day, month, upload, download, total, period) VALUES (@day, @month, @upload, @download, @total, @period)", username),
DataBaseFactory.Instance.GetConnection()))
{
cmd.Parameters.Add(new SqlCeParameter("@day", SqlDbType.DateTime) { Value = dailyUsage.Day });
cmd.Parameters.Add(new SqlCeParameter("@month", SqlDbType.NChar) { Value = dailyUsage.Day.Year.ToString() + dailyUsage.Day.Month.ToString().PadLeft(2, '0') });
cmd.Parameters.Add(new SqlCeParameter("@upload", SqlDbType.Float) { Value = dailyUsage.Upload });
cmd.Parameters.Add(new SqlCeParameter("@download", SqlDbType.Float) { Value = dailyUsage.Download });
cmd.Parameters.Add(new SqlCeParameter("@total", SqlDbType.Float) { Value = dailyUsage.Total });
cmd.Parameters.Add(new SqlCeParameter("@period", SqlDbType.NChar) { Value = dailyUsage.Period.ToString() });
cmd.ExecuteNonQuery();
}
}
else
throw sql;
}
}
public bool Exist(string username, DailyUsageBO usage, out DailyUsageBO data)
{
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("SELECT * FROM {0} WHERE day = @day", username),
DataBaseFactory.Instance.GetConnection()))
{
cmd.Parameters.Add(new SqlCeParameter("@day", SqlDbType.DateTime) { Value = usage.Day });
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
data = ReadRecord(dr);
return true;
}
}
}
data = null;
return false;
}