Пример #1
0
        //Update statement
        public void UpdateSmsStatusToSent(string id)
        {
            string query = "UPDATE sms SET sent=1 WHERE id=" + id;

            try
            {
                //Open connection
                if (this.OpenConnection() == true)
                {
                    //create mysql command
                    MySqlCommand cmd = new MySqlCommand();
                    //Assign the query using CommandText
                    cmd.CommandText = query;
                    //Assign the connection using Connection
                    cmd.Connection = connection;

                    //Execute query
                    cmd.ExecuteNonQuery();

                    //close connection
                    this.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                ServiceLog.WriteErrorLog(ex);
                //close Connection
                this.CloseConnection();
            }
        }
Пример #2
0
        public List <Message> Select_SMSPending_Type_ConnectionIssue()
        {
            try
            {
                var list = new List <Message>();

                string query = @"
                    select sms.id, sms.hostId,h.name as hostname, DATE_FORMAT(sms.createddate, "" %d-%m-%Y %T"") as createddate, sms_type.message
                    from sms join sms_type on sms.type = sms_type.id and sms_type.allowsendsms = 1
                    join host h on h.id = sms.hostId and h.allow_send_sms=1
                    where sms.sent = 0 and sms.sms_groupId= " + SMSGroup.GROUP_CONNECTION_ISSUE + @"
                    order by sms.createddate asc
                ";

                //Create a list to store the result
                //Open connection
                if (this.OpenConnection() == true)
                {
                    //Create Command
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    //Create a data reader and Execute the command
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    //Read the data and store them in the list
                    while (dataReader.Read())
                    {
                        list.Add(new Message()
                        {
                            id          = dataReader["id"] + "",
                            hostId      = dataReader["hostId"] + "",
                            message     = dataReader["message"] + "",
                            hostname    = dataReader["hostname"] + "",
                            createddate = dataReader["createddate"] + ""
                        });
                    }

                    //close Data Reader
                    dataReader.Close();

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return(list);
                }
                else
                {
                    return(list);
                }
            }
            catch (Exception ex)
            {
                ServiceLog.WriteErrorLog(ex);
                //close Connection
                this.CloseConnection();
                return(new List <Message>());
            }
        }
Пример #3
0
        public List <People> Select_PeopleToSend()
        {
            try
            {
                var list = new List <People>();

                string query = @"
                    select uh.hostId, uh.userId, u.phone
                    from user_host uh join user u on uh.userId = u.Id 
                    join host h on h.id = uh.hostId
                    where u.status = 1 and sendsms = 1 and h.allow_send_sms=1
                    order by uh.hostId
                ";

                //Create a list to store the result
                //Open connection
                if (this.OpenConnection() == true)
                {
                    //Create Command
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    //Create a data reader and Execute the command
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    //Read the data and store them in the list
                    while (dataReader.Read())
                    {
                        list.Add(new People()
                        {
                            hostId = dataReader["hostId"] + "",
                            userId = dataReader["userId"] + "",
                            phone  = dataReader["phone"] + ""
                        });
                    }

                    //close Data Reader
                    dataReader.Close();

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return(list);
                }
                else
                {
                    return(list);
                }
            }
            catch (Exception ex)
            {
                ServiceLog.WriteErrorLog(ex);
                //close Connection
                this.CloseConnection();
                return(new List <People>());
            }
        }
Пример #4
0
        /// <summary>
        /// Open conn
        /// </summary>
        /// <returns></returns>
        public bool CloseConnection()
        {
            try
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                ServiceLog.WriteErrorLog(ex);
                return(false);
            }

            return(true);
        }
Пример #5
0
        /// <summary>
        /// Open conn
        /// </summary>
        /// <returns></returns>
        public bool OpenConnection()
        {
            try
            {
                if (connection.State == ConnectionState.Open)
                {
                    return(true);
                }

                connection.Open();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                ServiceLog.WriteErrorLog(ex);
                return(false);
            }

            return(true);
        }
Пример #6
0
        public bool SendMessage(string PhoneNumber, string Message)
        {
            try
            {
                var response = SMSAPI.SendSMS(PhoneNumber, Message, "");
                if (response.code != 0)
                {
                    var errMsg = "Error code: " + response.code + "; Message: " + response.msg;
                    ServiceLog.WriteErrorLog(errMsg);
                    return(false);
                }
            }
            catch (Exception ex)
            {
                ServiceLog.WriteErrorLog(ex);
                return(false);
            }

            return(true);
        }
Пример #7
0
        protected override void OnStart(string[] args)
        {
            hasDataStatus = new Hashtable();
            // Load config
            config = FileHelper.ReadConfigFile();
            if (config.Exception != null)
            {
                ServiceLog.WriteErrorLog(config.Exception);
                // Stop service
                OnStop();
            }

            // Try to connect to DB
            mySql = new MySqlHelper(config.MySql.Server, config.MySql.User, config.MySql.Pass, config.MySql.DBName, config.MySql.SSL);

            sms = new SmsHelper(config.SmsApi.APIEndpoint, config.SmsApi.User, config.SmsApi.Pass);

            timeDelay = new Timer();
            this.timeDelay.Interval = config.Interval;
            this.timeDelay.Elapsed += new ElapsedEventHandler(WorkProcess);
            timeDelay.Enabled       = true;
            ServiceLog.WriteErrorLog("SMS service started");
        }
Пример #8
0
        public void SendSMS_Warning()
        {
            // Try to connect to DB
            var mySql = new MySqlHelper(config.MySql.Server, config.MySql.User, config.MySql.Pass, config.MySql.DBName, config.MySql.SSL);

            // Get data and send message
            // Try to send a message
            // get all people to send sms
            var lstPeople = mySql.Select_PeopleToSend();

            // ServiceLog.WriteErrorLog("lstPeople:" + lstPeople.Count);
            if (lstPeople != null && lstPeople.Count > 0)
            {
                var lstSms = mySql.Select_SMSPending_Type_Warning();
                // ServiceLog.WriteErrorLog("lstSms:" + lstSms.Count);
                if (lstSms != null && lstSms.Count > 0)
                {
                    //Sending message...
                    foreach (var itemSms in lstSms)
                    {
                        var people_Filter = lstPeople.Where(o => o.hostId == itemSms.hostId).ToList();
                        var people_Filter_phone_number = new List <string>();

                        if (people_Filter != null && people_Filter.Count > 0)
                        {
                            people_Filter_phone_number = people_Filter.Select(o => o.phone).ToList();
                        }

                        // List default
                        if (config.SmsApi.ListPeopleByPhoneNumber != null && config.SmsApi.ListPeopleByPhoneNumber.Count > 0)
                        {
                            people_Filter_phone_number.AddRange(config.SmsApi.ListPeopleByPhoneNumber);
                        }

                        // Distinct
                        people_Filter_phone_number = people_Filter_phone_number.Distinct().ToList();

                        if (people_Filter_phone_number != null && people_Filter_phone_number.Count > 0)
                        {
                            itemSms.message = convertToUnSign(itemSms.hostname + ": " + itemSms.message) + " - luc " + itemSms.createddate;

                            // List phone
                            var listPhone = string.Join(",", people_Filter_phone_number);
                            ServiceLog.WriteErrorLog(string.Format("[GROUP=" + SMSGroup.GROUP_WARNING + "] SMS sending...: HostId: {0}; SMSId: {1}; List Phone number: {2}", itemSms.hostId, itemSms.id, listPhone));

                            // send each person
                            foreach (var phoneNumber in people_Filter_phone_number)
                            {
                                // Send message
                                if (sms.SendMessage(phoneNumber, itemSms.message))
                                {
                                    // Update message record to sent
                                    mySql.UpdateSmsStatusToSent(itemSms.id);
                                }
                            }
                        } // End if (people_Filter != null && people_Filter.Count > 0)
                    }     // End foreach (var itemSms in lstSms)
                }         // End if (lstSms != null && lstSms.Count > 0)
            }

            // Set status
            hasDataStatus[strWarning] = true;
        }
Пример #9
0
 private void LogService(string content)
 {
     ServiceLog.WriteErrorLog(content);
 }