public List<Client> GetClients()
{
var clients = new List<Client>();
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
var sql = "Select * from Clients order by IndexNumber";
var cmd = new SqlCommand(sql, con);
var rdr = cmd.ExecuteReader();
using (rdr)
{
while (rdr.Read())
{
var client = new Client
{
Id = Convert.ToInt32(rdr["Id"].ToString()),
IndexNumber = Convert.ToInt32(rdr["IndexNumber"].ToString()),
FirstName = rdr["FirstName"].ToString(),
LastName = rdr["LastName"].ToString(),
Address = rdr["Address"].ToString(),
AFM = rdr["AFM"].ToString(),
DOY = rdr["DOY"].ToString(),
Title = rdr["Title"].ToString(),
Email = rdr["Email"].ToString(),
DefaultPrice = Convert.ToDecimal(rdr["DefaultPrice"]),
AdministrationOffice = rdr["AdministrationOffice"].ToString(),
January = Convert.ToBoolean(rdr["January"]),
February = Convert.ToBoolean(rdr["February"]),
March = Convert.ToBoolean(rdr["March"]),
April = Convert.ToBoolean(rdr["April"]),
May = Convert.ToBoolean(rdr["May"]),
June = Convert.ToBoolean(rdr["June"]),
July = Convert.ToBoolean(rdr["July"]),
August = Convert.ToBoolean(rdr["August"]),
September = Convert.ToBoolean(rdr["September"]),
October = Convert.ToBoolean(rdr["October"]),
November = Convert.ToBoolean(rdr["November"]),
December = Convert.ToBoolean(rdr["December"])
};
clients.Add(client);
}
}
foreach (var client in clients)
{
const string sqlAlerts = "Select * from Alerts where ClientId=@cid";
var cmdAlert = new SqlCommand(sqlAlerts, con);
cmdAlert.Parameters.AddWithValue("@cid", client.Id);
var rdrAlert = cmdAlert.ExecuteReader();
client.Alerts = new List<Alert>();
using (rdrAlert)
{
while (rdrAlert.Read())
{
var alert = new Alert
{
ClientId = client.Id,
Date = Convert.ToDateTime(rdrAlert["Date"]),
Description = rdrAlert["Description"].ToString(),
Name = rdrAlert["Name"].ToString()
};
client.Alerts.Add(alert);
}
}
}
}
return clients;
}
public List<Client> GetExpiredAlerts()
{
var con = new SqlConnection(_connectionString);
var result = new List<Client>();
using (con)
{
con.Open();
const string sql = "Select * from Alerts where Date < getDate()";
var cmd = new SqlCommand(sql, con);
var rdr = cmd.ExecuteReader();
var alerts = new List<Alert>();
using (rdr)
{
while (rdr.Read())
{
var alert = new Alert
{
ClientId = Convert.ToInt32(rdr["ClientId"]),
Name = rdr["Name"].ToString(),
Description = rdr["Description"].ToString(),
Date = Convert.ToDateTime(rdr["Date"])
};
alerts.Add(alert);
}
}
var clientIds = alerts.Select(x => x.ClientId).ToList();
foreach (var id in clientIds)
{
var sqlClients = "Select * from Clients where Id=@id";
var cmdClients = new SqlCommand(sqlClients, con);
cmdClients.Parameters.AddWithValue("@id", id);
var rdrClients = cmdClients.ExecuteReader();
using (rdrClients)
{
while (rdrClients.Read())
{
var client = new Client
{
Id = Convert.ToInt32(rdrClients["Id"].ToString()),
IndexNumber = Convert.ToInt32(rdrClients["IndexNumber"].ToString()),
FirstName = rdrClients["FirstName"].ToString(),
LastName = rdrClients["LastName"].ToString(),
Email = rdrClients["Email"].ToString(),
Address = rdrClients["Address"].ToString(),
AFM = rdrClients["AFM"].ToString(),
DOY = rdrClients["DOY"].ToString(),
Title = rdrClients["Title"].ToString(),
DefaultPrice = Convert.ToDecimal(rdrClients["DefaultPrice"]),
AdministrationOffice = rdrClients["AdministrationOffice"].ToString(),
January = Convert.ToBoolean(rdrClients["January"]),
February = Convert.ToBoolean(rdrClients["February"]),
March = Convert.ToBoolean(rdrClients["March"]),
April = Convert.ToBoolean(rdrClients["April"]),
May = Convert.ToBoolean(rdrClients["May"]),
June = Convert.ToBoolean(rdrClients["June"]),
July = Convert.ToBoolean(rdrClients["July"]),
August = Convert.ToBoolean(rdrClients["August"]),
September = Convert.ToBoolean(rdrClients["September"]),
October = Convert.ToBoolean(rdrClients["October"]),
November = Convert.ToBoolean(rdrClients["November"]),
December = Convert.ToBoolean(rdrClients["December"])
};
client.Alerts = alerts.Where(x => x.ClientId == id).ToList();
result.Add(client);
}
}
}
}
return result;
}
public Client GetClient(int clientId)
{
var client = new Client();
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
var sql = "Select * from Clients where Id=@cid";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@cid", clientId);
var rdr = cmd.ExecuteReader();
using (rdr)
{
while (rdr.Read())
{
client.AFM = rdr["AFM"].ToString();
client.DOY = rdr["DOY"].ToString();
client.Address = rdr["Address"].ToString();
client.FirstName = rdr["FirstName"].ToString();
client.LastName = rdr["LastName"].ToString();
client.Email = rdr["Email"].ToString();
client.Title = rdr["Title"].ToString();
client.DefaultPrice = Convert.ToDecimal(rdr["DefaultPrice"]);
client.January = Convert.ToBoolean(rdr["January"]);
client.February = Convert.ToBoolean(rdr["February"]);
client.March = Convert.ToBoolean(rdr["March"]);
client.April = Convert.ToBoolean(rdr["April"]);
client.May = Convert.ToBoolean(rdr["May"]);
client.June = Convert.ToBoolean(rdr["June"]);
client.July = Convert.ToBoolean(rdr["July"]);
client.August = Convert.ToBoolean(rdr["August"]);
client.September = Convert.ToBoolean(rdr["September"]);
client.October = Convert.ToBoolean(rdr["October"]);
client.November = Convert.ToBoolean(rdr["November"]);
client.December = Convert.ToBoolean(rdr["December"]);
client.Id = clientId;
}
}
const string sqlAlerts = "Select * from Alerts where ClientId=@cid";
var cmdAlert = new SqlCommand(sqlAlerts, con);
cmdAlert.Parameters.AddWithValue("@cid", clientId);
var rdrAlert = cmdAlert.ExecuteReader();
client.Alerts = new List<Alert>();
using (rdrAlert)
{
while (rdrAlert.Read())
{
var alert = new Alert
{
ClientId = clientId,
Date = Convert.ToDateTime(rdrAlert["Date"]),
Description = rdrAlert["Description"].ToString(),
Name = rdrAlert["Name"].ToString()
};
client.Alerts.Add(alert);
}
}
}
return client;
}