C# eQMSMessage_FormApp.SMSView类代码示例

        // SMS - Set SMS Search Row

        #region SMS - Set SMS Search Row

        public DataTable GetDaoSetSMSSearchRow(SMSView smsview)
        {
            DataTable MySearchRow = new DataTable();
            DataSet dstsr = new DataSet();

            //TemperaryParameterList = new List<OracleParameter>();
            try
            {
                //// Query For Fault Monitoring
                //query = string.Format("select device_alaram_id,device_id,device_fault_code from tmsplaza.device_fault_ln_tnx where device_alaram_id=(select max(device_alaram_id) from tmsplaza.device_fault_ln_tnx)");

                SqlConnection MySqlConnection = new SqlConnection(ConnectionString);

                SqlCommand MySqlCommand = new SqlCommand("select q.queue_tnx_id,q.queue_department_id from tbl_customervisit_tnx c,tbl_queue_tnx q where c.visit_tnx_id=q.queu_visit_tnxid and [email protected] and cast(q.queue_datetime as date) = cast(getdate() as date) and [email protected]", MySqlConnection);

                MySqlCommand.Parameters.AddWithValue("@QueueNo", smsview.QueueNo);
                MySqlCommand.Parameters.AddWithValue("@QueueStatusID", smsview.QueueStatusID);

                //SqlCommand MySqlCommand = new SqlCommand("select q.queu_visit_tnxid,v.visit_queue_no,c.customer_mobile from tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c where v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(queue_datetime as date) = cast(getdate() as date) and queue_status_id=1 and sms_status_flag='N'", MySqlConnection);

                //MySqlCommand.Parameters.AddWithValue("@department_id", queueview.DepartmentID);

                MySqlConnection.Open();

                //SqlDataReader dr = MySqlCommand.ExecuteReader();

                //SqlDataReader dr = MySqlCommand.ExecuteReader();

                //DataTable MyStatusQueueNO = new DataTable();

                //MyStatusQueueNO.Load(dr);


                //MySearchRow.Load(dr);

                //sqlad.SelectCommand = cmd;
                MySqlCommand.ExecuteNonQuery();

                sqlad.SelectCommand = MySqlCommand;
                sqlad.Fill(dstsr);
                MySearchRow = dstsr.Tables[0];

                //MySqlConnection.Close();

                return MySearchRow;
            }
            catch (Exception exmsg)
            {
                throw new Exception("Error Occured While Retrieving Data From DataBase", exmsg);
            }
            // Executing Select Query 
            //return smsdbconnection.ExecuteSelectQuery(query, TemperaryParameterList);
        }

 public DataTable SetSMSStatusR(SMSView smsview)
 {
     SMSDAO smsdao = new SMSDAO();
     try
     {
         // Getting Data From Dao
         return smsdao.GetDaoSetSMSStatusR(smsview);
     }
     catch
     {
         throw;
     }
     finally
     {
         smsdao = null;
     }
 }

 public DataTable GetTotalWaitingMissedQueue(SMSView smsview)
 {
     SMSDAO smsdao = new SMSDAO();
     try
     {
         // Getting Data From Dao
         return smsdao.GetDaoTotalWaitingMissedQueue(smsview);
     }
     catch
     {
         throw;
     }
     finally
     {
         smsdao = null;
     }
 }

        public DataTable searchIncomingSMS(SMSView smsview)
        {
            string dattime = System.DateTime.Now.ToString();
            //insert into tbl_sms_mst(phone_number,incomingsms,incoming_sms_datetime)values(9002476871,'Test Message','2015-04-17 00:00:00')
            // select incomingsms from tbl_sms_mst where CONVERT(DATE, incoming_sms_datetime) = CONVERT(DATE, GETDATE())
            try
            {
                //// Query For Fault Monitoring
                //query = string.Format("select device_alaram_id,device_id,device_fault_code from tmsplaza.device_fault_ln_tnx where device_alaram_id=(select max(device_alaram_id) from tmsplaza.device_fault_ln_tnx)");

                SqlConnection MySqlConnection = new SqlConnection(ConnectionString);

                SqlCommand MySqlCommand = new SqlCommand("select sms_content,sms_phoneno from tbl_sms_tnx where CONVERT(DATE, sms_datetime) = CONVERT(DATE, GETDATE()) and sms_status_flag='I'", MySqlConnection);

                //MySqlCommand.Parameters.AddWithValue("@department_id", queueview.DepartmentID);

                MySqlConnection.Open();

                SqlDataReader dr = MySqlCommand.ExecuteReader();

                DataTable MySms = new DataTable();

                MySms.Load(dr);

                MySqlConnection.Close();

                return MySms;
            }
            catch (Exception exmsg)
            {
                throw new Exception("Error Occured While Retrieving Data From DataBase", exmsg);
            }

        }

        public void ExpiredAppointmentNotification()
        {
            SMSView smsview = new SMSView();
            try
            {
                DataTable appointment = new DataTable();
                SMSController sms = new SMSController();
                appointment = sms.appointmentexpired(smsview);

                foreach (DataRow dr in appointment.Rows)
                {
                    SMSView smsview1 = new SMSView();
                    string cname = dr["customer_firstname"].ToString();
                    int appid = Convert.ToInt32(dr["appointment_id"].ToString());
                    string app = dr["appointment_time"].ToString();
                    DateTime adt = Convert.ToDateTime(app);
                    string mobileno = "61" + dr["appointment_mobileno"].ToString();
                    long custid = Convert.ToInt64(dr["appointment_customer_id"].ToString());
                    DateTime syst = DateTime.Now;
                    string apptime = adt.ToString("HH:mm");
                    DateTime timeapp1 = Convert.ToDateTime(app);
                    TimeSpan t1 = new TimeSpan(01, 00, 00);
                    timeapp1 = timeapp1 + t1;
                    string timeapp = timeapp1.ToString("HH:mm");
                    string systemtime = syst.ToString("HH:mm");
                    //string systemtime = "22:19";
                    //DateTime re = Convert.ToDateTime("10:31");
                    //String ret = re.ToString("HH:mm");
                    DataTable dtsmsview = new DataTable();
                    dtsmsview = smscontroller.appointmenTranstexpired(smsview1);
                    if (dtsmsview.Rows.Count == 0)
                    {
                        if (timeapp == systemtime)
                        {
                            string strmsg = "Dear " + cname + ",\r\n  your appointment for " + apptime + " has been Expired, please contact 18xxxxxx to reschedule";
                            smsview.SmsDesc = strmsg;
                            #region Samsung SMS gateway
                            //SMS for Samsung gateway
                            // Set the username of the account holder.
                            Messaging.MessageController.UserAccount.User = "SAMSUNGELECTR213";
                            // Set the password of the account holder.
                            Messaging.MessageController.UserAccount.Password = "y2M28DQD";
                            // Set the first name of the account holder (optional).
                            Messaging.MessageController.UserAccount.ContactFirstName = "David";
                            // Set the last name of the account holder (optional).
                            Messaging.MessageController.UserAccount.ContactLastName = "Smith";
                            // Set the mobile phone number of the account holder (optional).
                            Messaging.MessageController.UserAccount.ContactPhone = "0423612367";
                            // Set the landline phone number of the account holder (optional).
                            Messaging.MessageController.UserAccount.ContactLandLine = "0338901234";
                            // Set the contact email of the account holder (optional).
                            Messaging.MessageController.UserAccount.ContactEmail = "[email protected]";
                            // Set the country of origin of the account holder (optional).
                            Messaging.MessageController.UserAccount.Country = Countries.Australia;
                            bool testOK = false;

                            try
                            {
                                // Test the user account settings.
                                Account testAccount = Messaging.MessageController.UserAccount;
                                testOK = Messaging.MessageController.TestAccount(testAccount);
                            }
                            catch (Exception ex)
                            {
                                // An exception was thrown. Display the details of the exception and return.
                                string message = "There was an error testing the connection details:\n" +
                                ex.Message;
                                // MessageBox.Show(this, message, "Connection Failed", MessageBoxButtons.OK);
                                return;
                            }
                            if (testOK)
                            {
                                // The user account settings were valid. Display a success message
                                // box with the number of credits.
                                int balance = Messaging.MessageController.UserAccount.Balance;
                                string message = string.Format("You have {0} message credits available.",
                                balance);
                                // MessageBox.Show(this, message, "Connection Succeeded", MessageBoxButtons.OK);
                            }
                            else
                            {
                                // The username or password were incorrect. Display a failed message box.
                                //  MessageBox.Show(this, "The username or password you entered were incorrect.",
                                // "Connection Failed", MessageBoxButtons.OK);
                            }

                            Messaging.MessageController.Settings.TimeOut = 5;
                            // Set the batch size (number of messages to be sent at once) to 200.
                            Messaging.MessageController.Settings.BatchSize = 200;
                            Messaging.MessageController.Settings.DeliveryReport = true;
                            SMSMessage smsobj = new SMSMessage(mobileno, strmsg);
                            Messaging.MessageController.AddToQueue(smsobj);
                            Messaging.MessageController.SendMessages();
                            #endregion Samsung SMS gateway

      
                            #region inserting to tbl_sms_tnx
                            smsview.CustId = custid;
                            smsview.SmsDesc = strmsg;
                            smsview.PhoneNo = mobileno;
//.........这里部分代码省略.........

 public DataTable SelectCustIDByUsingQueueno(SMSView smsview)
 {
     SqlConnection MysqlConnection = new SqlConnection(ConnectionString);
     MysqlConnection.Open();
     string sql = "select tcv.visit_customer_id,cd.members_id from tbl_customervisit_tnx tcv, tbl_customer_dtl cd where [email protected] and CONVERT(DATE, tcv.visit_datetime) = CONVERT(DATE, GETDATE()) and tcv.visit_member_id=cd.members_id";
     SqlCommand MyCommand = new SqlCommand(sql, MysqlConnection);
     MyCommand.Parameters.AddWithValue("@Mysms", smsview.MySms);
     SqlDataReader dr = MyCommand.ExecuteReader();
     DataTable dt = new DataTable();
     dt.Load(dr);
     MysqlConnection.Close();
     return dt;
 }

 public DataTable selectDeptId(SMSView smsview)
 {
     SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
     MySqlConnection.Open();
     string sql = "select queue_department_id from tbl_queue_tnx where [email protected]";
     SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
     MySqlCommand.Parameters.AddWithValue("@TnxId", smsview.QueueTransaction);
     SqlDataReader dr = MySqlCommand.ExecuteReader();
     DataTable MyDeptid = new DataTable();
     MyDeptid.Load(dr);
     MySqlConnection.Close();
     return MyDeptid;
 }

        public string InsertMissedQSMS(SMSView smsview)
        {
            SqlConnection MysqlConnection = new SqlConnection(ConnectionString);
            try
            {

                string sql = "if not exists(select * from tbl_sms_tnx where [email protected]_cust_id and [email protected]_phoneno and [email protected] and [email protected]_content and [email protected]_status_flag  )insert into tbl_sms_tnx(sms_visit_tnxid,sms_cust_id, sms_datetime,sms_phoneno,sms_status_flag,sms_content,sms_queueno)values(@sms_visit_tnxid,@sms_cust_id,@sms_datetime,@sms_phoneno,@sms_status_flag,@sms_content,@QueueNo)";
                cmd = new SqlCommand(sql, MysqlConnection);
                cmd.Parameters.AddWithValue("@sms_visit_tnxid", smsview.QueueTransaction);
                cmd.Parameters.AddWithValue("@sms_cust_id", smsview.CustId);
                cmd.Parameters.AddWithValue("@sms_datetime", smsview.SMSDateTime);
                cmd.Parameters.AddWithValue("@sms_phoneno", smsview.PhoneNo);
                cmd.Parameters.AddWithValue("@sms_status_flag", smsview.IncomingsmsFlag);
                cmd.Parameters.AddWithValue("@sms_content", smsview.MySms);
                cmd.Parameters.AddWithValue("@QueueNo", smsview.QueueNo);
                // cmd.Parameters.AddWithValue("@sms_delivery_status", smsview.DeliveryReport);
                // DataTable dt3 = new DataTable();
                MysqlConnection.Open();
                int i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                return Convert.ToString(i);
            }
            catch (Exception)
            {

                throw;
            }
        }

        public DataTable positionretrievebyusingQueueno(SMSView smsview)
        {
            SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
            try
            {
                MySqlConnection.Open();
                // string sql = "select q.queu_visit_tnxid,v.visit_queue_no_show,c.customer_mobile from tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c where v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and q.queue_status_id=1 and q.sms_status_flag='N' and [email protected]";
                // string sql = "select v.visit_queue_no_show from tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c where v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and q.queue_status_id=1 and [email protected]Departmentid";
                string sql = "select tq.queue_department_id from tbl_customervisit_tnx cv,tbl_queue_tnx tq where cv.visit_tnx_id=tq.queu_visit_tnxid and [email protected] and CONVERT(DATE, tq.queue_datetime) = CONVERT(DATE, GETDATE())";
                SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
                MySqlCommand.Parameters.AddWithValue("@MySms", smsview.MySms);
                SqlDataReader dr = MySqlCommand.ExecuteReader();

                DataTable MyQSMSstatus = new DataTable();

                MyQSMSstatus.Load(dr);

                MySqlConnection.Close();

                return MyQSMSstatus;
            }
            catch (Exception ex)
            {
                throw new Exception("Error Occured While Retrieving Data From DataBase", ex);

            }
        }

        public DataTable selectQueuePosition(SMSView smsview)
        {
            SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
            try
            {
                MySqlConnection.Open();
                // string sql = "select DISTINCT q.queu_visit_tnxid,v.visit_queue_no_show,tc.members_mobile from tbl_customer_dtl tc, tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c where v.visit_member_id=tc.members_id and v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and q.queue_status_id=1 and q.sms_status_flag='A'";
                // string sql = "select DISTINCT v.visit_queue_no_show from tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c where v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and q.queue_status_id=1 and sms_status_flag='N'";
                // string sql = "select DISTINCT q.queu_visit_tnxid,v.visit_queue_no_show,tc.members_mobile from tbl_customer_dtl tc, tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c where v.visit_member_id=tc.members_id and v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and q.queue_status_id=1 and q.message_status_flag='A'";
                //string sql = "select q.queu_visit_tnxid,v.visit_queue_no_show,tc.members_mobile from tbl_customer_dtl tc, tbl_customervisit_tnx v,tbl_queue_tnx q,tbl_customerreg_mst c,tbl_appointment_tnx a where v.visit_member_id=tc.members_id and v.visit_tnx_id=q.queu_visit_tnxid and c.customer_id=v.visit_customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and a.appointment_id=v.customer_appointment_id and q.queue_status_id=1 and q.message_status_flag='A' order by v.consulting_status ASC, v.customer_appointment_time ASC";
                string sql = "select c.visit_tnx_id,c.visit_queue_no,c.visit_queue_no_show  from tbl_queue_tnx q,tbl_customervisit_tnx c where q.queu_visit_tnxid = c.visit_tnx_id and CONVERT(DATE, q.queue_datetime) = CONVERT(DATE, GETDATE())and q.queue_status_id = 1 and message_status_flag='A' order by c.consulting_status ASC,c.customer_appointment_time ASC";
                SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
                // MySqlCommand.Parameters.AddWithValue("@Departmentid",smsview.DepartmentID);
                SqlDataReader dr = MySqlCommand.ExecuteReader();

                DataTable MyQSMSstatus = new DataTable();

                MyQSMSstatus.Load(dr);

                MySqlConnection.Close();

                return MyQSMSstatus;
            }
            catch (Exception ex)
            {
                throw new Exception("Error Occured While Retrieving Data From DataBase", ex);

            }
        }

 public DataTable GetAppointmentDetails(SMSView smsview)
 {
     try
     {
         SqlConnection con = new SqlConnection(ConnectionString);
         con.Open();
         //By Ravi- string sql = "select DISTINCT a.appointment_id,a.appointment_customer_id,a.appointment_mobileno,a.appointment_time,c.customer_firstname from tbl_appointment_tnx a,tbl_customerreg_mst c where c.customer_id=a.appointment_customer_id and CONVERT(date,appointment_time)=CONVERT(date,GETDATE())";
         string sql = "select DISTINCT a.appointment_id,a.appointment_customer_id,a.appointment_mobileno,a.appointment_time,c.customer_firstname from tbl_appointment_tnx a,tbl_customerreg_mst c where c.customer_id=a.appointment_customer_id and a.sms_alert='B' and CONVERT(date,appointment_time)=CONVERT(date,GETDATE())";
         SqlCommand cmd = new SqlCommand(sql,con);
         SqlDataReader dr = cmd.ExecuteReader();
         DataTable dtappdetails = new DataTable();
         dtappdetails.Load(dr);
         con.Close();
         return dtappdetails;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         con.Close();
     }
 }

        public void updatesmsalert(SMSView smsview)
        {
            SqlDataAdapter sqlad = new SqlDataAdapter();
            try
            {
                con = new SqlConnection(ConnectionString);
                con.Open();
                string sql = "update tbl_appointment_tnx set [email protected]_alert where [email protected]_id";

                cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@appointment_id", smsview.AppointmentID);
                cmd.Parameters.AddWithValue("@sms_alert", smsview.SMSalert);

                //cmd.Parameters.AddWithValue("@buttoneventflag", rtview.ButtonEventFlag);
                sqlad.InsertCommand = cmd;
                cmd.ExecuteNonQuery();


                //return "0";
            }
            catch
            {
                //return "1";
            }
            finally
            {
                con.Close();
                //sqlrd.Close();
                cmd.Cancel();
            }
        }

        public string InsertAppointmentAlertSMS(SMSView smsview)
        {
            SqlConnection MysqlConnection = new SqlConnection(ConnectionString);
            try
            {

                string sql = "insert into tbl_sms_tnx(sms_visit_tnxid,sms_cust_id,sms_content,sms_datetime,sms_status_flag,sms_phoneno,sms_delivery_status,sms_queueno,sms_centre_id)values(@SmsVisittnxId,@CustId,@SmsDesc,@SMSDateTime,@SMSStatusFlag,@PhoneNo,@DeliveryReport,@QueueNo,@CentreId)";
                cmd = new SqlCommand(sql, MysqlConnection);
                cmd.Parameters.AddWithValue("@SmsVisittnxId", smsview.SmsVisittnxId);
                cmd.Parameters.AddWithValue("@CustId", smsview.CustId);
                cmd.Parameters.AddWithValue("@SmsDesc", smsview.SmsDesc);
                cmd.Parameters.AddWithValue("@SMSDateTime", smsview.SMSDateTime);
                cmd.Parameters.AddWithValue("@SMSStatusFlag", smsview.SMSStatusFlag);
                cmd.Parameters.AddWithValue("@PhoneNo", smsview.PhoneNo);
                cmd.Parameters.AddWithValue("@DeliveryReport", smsview.DeliveryReport);
                cmd.Parameters.AddWithValue("@QueueNo", smsview.QueueNo);
                cmd.Parameters.AddWithValue("@CentreId", smsview.CentreId);
                // DataTable dt3 = new DataTable();
                MysqlConnection.Open();
                int i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                return Convert.ToString(i);
            }
            catch (Exception)
            {

                throw;
            }
        }

        public DataTable updateButtonFlag(SMSView smsview)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            con.Open();
            string sql = "update tbl_queue_tnx set [email protected] where [email protected]";
            SqlCommand cmd = new SqlCommand(sql,con);
            cmd.Parameters.AddWithValue("@Buttonevent",smsview.ButtonEventFlag);
            cmd.Parameters.AddWithValue("@buttonvisittnx",smsview.ButtonVisitTnx);
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable dtt = new DataTable();
            dtt.Load(dr);
            con.Close();
            cmd.Dispose();
            return dtt;

        }

 public DataTable getAlertMessageExistance(SMSView smsview)
 {
     SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
     MySqlConnection.Open();
     string sql = "select * from tbl_sms_tnx where [email protected] and sms_status_flag='A'";
     SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
     MySqlCommand.Parameters.AddWithValue("@QueueTransactionID", smsview.QueueTransaction);
     SqlDataReader dr = MySqlCommand.ExecuteReader();
     DataTable MyCustName = new DataTable();
     MyCustName.Load(dr);
     MySqlConnection.Close();
     return MyCustName;
 }