public void InsertClient(Client client)
{
throw new NotImplementedException();
}
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 void SaveClient(Client client)
{
throw new NotImplementedException();
}
public void SaveClient(Client client)
{
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
var sql = @"Update Clients SET FirstName = @fname
,LastName = @lname
,Email = @email
,Address = @address
,AFM = @afm
,DOY = @doy
,AdministrationOffice=@adminOffice
,Title = @title
,DefaultPrice = @price
,January = @jan
,February = @feb
,March = @mar
,April = @apr
,May = @may
,June = @jun
,July = @jul
,August = @aug
,September = @sep
,October = @oct
,November = @nov
,December = @dec WHERE Id=@id";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@id", client.Id);
cmd.Parameters.AddWithValue("@lname", client.LastName ?? "");
cmd.Parameters.AddWithValue("@fname", client.FirstName ?? "");
cmd.Parameters.AddWithValue("@email", client.Email ?? "");
cmd.Parameters.AddWithValue("@adminOffice", client.AdministrationOffice ?? "");
cmd.Parameters.AddWithValue("@address", client.Address ?? "");
cmd.Parameters.AddWithValue("@afm", client.AFM ?? "");
cmd.Parameters.AddWithValue("@doy", client.DOY ?? "");
cmd.Parameters.AddWithValue("@title", client.Title ?? "");
cmd.Parameters.AddWithValue("@price", client.DefaultPrice);
cmd.Parameters.AddWithValue("@jan", client.January);
cmd.Parameters.AddWithValue("@feb", client.February);
cmd.Parameters.AddWithValue("@mar", client.March);
cmd.Parameters.AddWithValue("@apr", client.April);
cmd.Parameters.AddWithValue("@may", client.May);
cmd.Parameters.AddWithValue("@jun", client.June);
cmd.Parameters.AddWithValue("@jul", client.July);
cmd.Parameters.AddWithValue("@aug", client.August);
cmd.Parameters.AddWithValue("@sep", client.September);
cmd.Parameters.AddWithValue("@oct", client.October);
cmd.Parameters.AddWithValue("@nov", client.November);
cmd.Parameters.AddWithValue("@dec", client.December);
cmd.ExecuteNonQuery();
}
SaveClientAlerts(client);
}
private void SaveClientAlerts(Client client)
{
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
const string delete = "Delete from Alerts where ClientId=@cid";
var cmdDelete = new SqlCommand(delete, con);
cmdDelete.Parameters.AddWithValue("@cid", client.Id);
cmdDelete.ExecuteNonQuery();
const string sql = "Insert into Alerts (ClientId, Name, Description, Date) Values (@cid, @name, @description, @date)";
if (client.Alerts != null)
{
foreach (var it in client.Alerts)
{
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@cid", client.Id);
cmd.Parameters.AddWithValue("@name", it.Name ?? "");
cmd.Parameters.AddWithValue("@description", it.Description ?? "");
cmd.Parameters.AddWithValue("@date", it.Date);
cmd.ExecuteNonQuery();
}
}
}
}
public void InsertClient(Client client)
{
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
const string sql = "Insert Into Clients (IndexNumber, FirstName, LastName, Email, Address, AdministrationOffice, AFM, DOY, Title,DefaultPrice,January,February,March,April,May,June,July,August,September,October,November,December) " +
"Values (@indexnumber, @fname, @lname, @email, @address, @adminoffice, @afm, @doy, @title, @defaultPrice, @jan, @feb, @mar, @apr, @may, @jun, @jul, @aug, @sep, @oct, @nov, @dec)";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@lname", client.LastName ?? "");
cmd.Parameters.AddWithValue("@indexnumber", client.IndexNumber);
cmd.Parameters.AddWithValue("@email", client.Email ?? "");
cmd.Parameters.AddWithValue("@adminoffice", client.AdministrationOffice ?? "");
cmd.Parameters.AddWithValue("@fname", client.FirstName ?? "");
cmd.Parameters.AddWithValue("@address", client.Address ?? "");
cmd.Parameters.AddWithValue("@afm", client.AFM ?? "");
cmd.Parameters.AddWithValue("@doy", client.DOY ?? "");
cmd.Parameters.AddWithValue("@title", client.Title ?? "");
cmd.Parameters.AddWithValue("@defaultPrice", Convert.ToDecimal(client.DefaultPrice));
cmd.Parameters.AddWithValue("@jan", client.January);
cmd.Parameters.AddWithValue("@feb", client.February);
cmd.Parameters.AddWithValue("@mar", client.March);
cmd.Parameters.AddWithValue("@apr", client.April);
cmd.Parameters.AddWithValue("@may", client.May);
cmd.Parameters.AddWithValue("@jun", client.June);
cmd.Parameters.AddWithValue("@jul", client.July);
cmd.Parameters.AddWithValue("@aug", client.August);
cmd.Parameters.AddWithValue("@sep", client.September);
cmd.Parameters.AddWithValue("@oct", client.October);
cmd.Parameters.AddWithValue("@nov", client.November);
cmd.Parameters.AddWithValue("@dec", client.December);
cmd.ExecuteNonQuery();
const string sqlSelectIndex = "Select * from Clients where IndexNumber >= @indexnumber";
var cmdSelectIndex = new SqlCommand(sqlSelectIndex, con);
cmdSelectIndex.Parameters.AddWithValue("@indexnumber", client.IndexNumber);
var rdr = cmdSelectIndex.ExecuteReader();
var indexes = new Dictionary<int, int>();
using (rdr)
{
while (rdr.Read())
{
indexes.Add(Convert.ToInt32(rdr["Id"]), Convert.ToInt32(rdr["IndexNumber"]) + 1);
}
}
const string strMaxIndexNumber = "SELECT MAX(Id) from Clients";
var cmdMaxIndexNumber = new SqlCommand(strMaxIndexNumber, con);
var maxIndex = cmdMaxIndexNumber.ExecuteScalar();
client.Id = Convert.ToInt32(maxIndex);
foreach (var key in indexes.Keys)
{
if (key != Convert.ToInt32(maxIndex))
{
const string sqlUpdateIndex = "Update Clients set IndexNumber=@indexnumbernew where Id = @id";
var cmdUpdateIndex = new SqlCommand(sqlUpdateIndex, con);
cmdUpdateIndex.Parameters.AddWithValue("@id", key);
cmdUpdateIndex.Parameters.AddWithValue("@indexnumbernew", indexes[key]);
cmdUpdateIndex.ExecuteNonQuery();
}
}
}
SaveClientAlerts(client);
}
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 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;
}
public void InsertClient(Client client)
{
_dal.InsertClient(client);
}
public void SaveClient(Client client)
{
_dal.SaveClient(client);
}