Example #1
0
 public void InsertClient(Client client)
 {
     throw new NotImplementedException();
 }
Example #2
0
 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;
 }
Example #3
0
 public void SaveClient(Client client)
 {
     throw new NotImplementedException();
 }
Example #4
0
        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);
        }
Example #5
0
        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();
                    }
                }
            }
        }
Example #6
0
        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);
        }
Example #7
0
        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;
        }
Example #8
0
        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;
        }
Example #9
0
 public void InsertClient(Client client)
 {
     _dal.InsertClient(client);
 }
Example #10
0
 public void SaveClient(Client client)
 {
     _dal.SaveClient(client);
 }