Пример #1
0
        public Services GetService(int id)
        {
            Services ins = new Services();

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            SqlCommand cmdI;

            //...SQL Commands...
            cmdI = new SqlCommand("SELECT * FROM Services WHERE ServiceId =" + id, con);
            cmdI.Connection.Open();
            SqlDataReader drI = cmdI.ExecuteReader();

            //...Retrieve Data...
            if (drI.HasRows)
            {
                while (drI.Read())
                {
                    ins.ServiceId = Convert.ToInt32(drI["ServiceId"]);
                    ins.Service = drI["Service"].ToString();
                    ins.Query = drI["Query"].ToString();
                    ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
                    ins.ClientId = Convert.ToInt32(drI["ClientId"]);
                    ins.DeviceUserId = Convert.ToInt32(drI["DeviceUserId"]);

                }
            }
            drI.Close();
            con.Close();

            return ins;
        }
Пример #2
0
        public Services AddServices(Services ins)
        {
            string strTrx = "AddService";

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            con.Open();

            //...Command Interface...
            SqlCommand cmdI = con.CreateCommand();
            SqlTransaction trx;
            trx = con.BeginTransaction(strTrx);
            cmdI.Connection = con;
            cmdI.Transaction = trx;

            try
            {
                //...Insert Record...
                cmdI.CommandText = "f_Admin_Insert_Service";
                cmdI.CommandType = System.Data.CommandType.StoredProcedure;
                cmdI.Parameters.AddWithValue("@ClientId", ins.ClientId);
                cmdI.Parameters.AddWithValue("@DeviceUserId", ins.DeviceUserId);
                cmdI.Parameters.AddWithValue("@Service", ins.Service);
                cmdI.Parameters.AddWithValue("@Query", ins.Query);
                cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate);                                                            // int,
                // int

                //...Return new ID
                ins.ServiceId = (int)cmdI.ExecuteScalar();

                trx.Commit();
                cmdI.Connection.Close();
            }
            catch (SqlException ex)
            {
                if (trx != null) trx.Rollback();
                //throw ex;
                //Console.WriteLine("Rollback Exception Type: {0}", ex.GetType());
                //Console.WriteLine("  Message: {0}", ex.Message);
            }
            finally
            {
                //Check for close and respond accordingly
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
                //Clean up
                con.Dispose();
                cmdI.Dispose();
                trx.Dispose();
            }
            return ins;
        }
Пример #3
0
        public Services UpdateServices(Services ins)
        {
            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            con.Open();
            SqlCommand cmdI = con.CreateCommand();
            cmdI.Connection = con;

            //...Update Record...
            cmdI.Parameters.Clear();
            cmdI.CommandText = "f_Admin_Update_Services";
            cmdI.CommandType = System.Data.CommandType.StoredProcedure;
            cmdI.Parameters.AddWithValue("@ServiceId", ins.ServiceId);
            cmdI.Parameters.AddWithValue("@ClientId", ins.ClientId);
            cmdI.Parameters.AddWithValue("@DeviceUserId", ins.DeviceUserId);
            cmdI.Parameters.AddWithValue("@Service", ins.Service);
            cmdI.Parameters.AddWithValue("@Query", ins.Query);
            cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate);
            cmdI.ExecuteNonQuery();
            cmdI.Connection.Close();

            return ins;
        }
Пример #4
0
        public List<Services> GetServices(int ClientId)
        {
            List<Services> lsServicesList = new List<Services>();
            Services ins;

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            SqlCommand cmdI;

            //...SQL Commands...
            cmdI = new SqlCommand("SELECT S.*, D.Name, D.Surname, D.Phone, D.Email FROM Services S INNER JOIN DeviceUsers D ON S.DeviceUserId = D.DeviceUserId WHERE S.ClientId=" + ClientId, con);
            cmdI.Connection.Open();
            SqlDataReader drI = cmdI.ExecuteReader();

            //...Retrieve Data...
            if (drI.HasRows)
            {
                while (drI.Read())
                {
                    ins = new Services();
                    ins.ServiceId = Convert.ToInt32(drI["ServiceId"]);
                    ins.Service = drI["Service"].ToString();
                    ins.Query = drI["Query"].ToString();
                    ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
                    ins.ClientId = Convert.ToInt32(drI["ClientId"]);
                    ins.DeviceUserId = Convert.ToInt32(drI["DeviceUserId"]);
                    ins.UserName = drI["Name"].ToString() + " " + drI["Surname"].ToString();
                    ins.Phone = drI["Phone"].ToString();
                    ins.Email = drI["Email"].ToString();
                    lsServicesList.Add(ins);
                }
            }
            drI.Close();
            con.Close();

            return lsServicesList;
        }
Пример #5
0
        public ActionResult RequestService(int DeviceUserId, string ServiceRequest, string Query)
        {
            DeviceUserRepository DevURep = new DeviceUserRepository();
            ServicesRepository ServRep = new ServicesRepository();

            Services service = new Services();
            DeviceUser user = DevURep.GetDeviceUser(DeviceUserId);

            service.ClientId = user.ClientId;
            service.DeviceUserId = DeviceUserId;
            service.Service = ServiceRequest;
            service.Query = Query;
            service.ModifiedDate = DateTime.Now;

            StringBuilder w = new StringBuilder();
            w.Append(user.Name).Append(" ").Append(user.Surname).Append("\n");
            w.Append("Address: ").Append(user.Address).Append("\n");
            w.Append("Contact Details: Phone - ").Append(user.Phone).Append(" Email - ").Append(user.Email).Append("\n");
            w.Append("Request: ").Append(service.Service);
            w.Append("\n").Append(service.Query);

            Services ins = ServRep.AddServices(service);

            if (ins.ServiceId != 0)
            {
                //Send email
                Functions f = new Functions();

                //Get Client Service Mail List
                List<Settings> mails = setRep.GetSettings("email", user.ClientId);

                if (mails.Count >= 1)
                {
                    StringBuilder maillist = new StringBuilder();

                    foreach (Settings setting in mails)
                    {
                        maillist.Append(setting.Value).Append(",");
                    }
                    maillist.Remove(maillist.Length - 1, 1);

                    f.SendEmail(w.ToString(), maillist.ToString(), service.Service);
                }
                return Content("Success", "text/html");
            }
            else
            {
                return Content("Fail", "text/html");
            }
        }