public DataTable SetSMSStatusR(SMSView smsview)
{
SMSDAO smsdao = new SMSDAO();
try
{
// Getting Data From Dao
return smsdao.GetDaoSetSMSStatusR(smsview);
}
catch
{
throw;
}
finally
{
smsdao = null;
}
}
// 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 q.queue_status_id=@QueueStatusID and cast(q.queue_datetime as date) = cast(getdate() as date) and c.visit_queue_no=@QueueNo", 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 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 = "******";
// Set the password of the account holder.
Messaging.MessageController.UserAccount.Password = "******";
// 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 = "*****@*****.**";
// 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;
smsview.DeliveryReport = "y";
smsview.SmsDesc = strmsg;
smsview.IncomingsmsFlag = "M";
smsview.SmsVisittnxId = 2;
smsview.SMSDateTime = System.DateTime.Now;
smsview.SMSStatusFlag = "M";
smsview.QueueNo = Convert.ToString("1");
smsview.CentreId = "";
smsview.SMSDateTime = System.DateTime.Now;
string i;
i = smscontroller.getInsertAppointmentAlertSms(smsview);
#endregion into tbl_sms_tnx
#region Update SMS_Alert statsus flag
smsview.AppointmentID = appid;
smsview.SMSalert = 'E';
sms.updatesmsalert(smsview);
#endregion Update SMS_Alert statsus flag
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
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 tcv.visit_queue_no_show=@Mysms 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 queu_visit_tnxid=@TnxId";
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 sms_cust_id=@sms_cust_id and sms_phoneno=@sms_phoneno and sms_queueno=@QueueNo and sms_content=@sms_content and sms_status_flag=@sms_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 queue_department_id=@Departmentid";
// 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 queue_department_id=@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 cv.visit_queue_no_show=@MySms 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 sms_alert=@sms_alert where appointment_id=@appointment_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 button_event_flag=@Buttonevent where queu_visit_tnxid=@buttonvisittnx";
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 sms_visit_tnxid=@QueueTransactionID 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;
}
public DataTable retCustID(SMSView smsview)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
MySqlConnection.Open();
string sql = "select DISTINCT tv.visit_customer_id,td.members_id from tbl_customer_dtl td, tbl_customervisit_tnx tv where cast(tv.visit_datetime as date) = cast(getdate() as date) and tv.visit_member_id=td.members_id and tv.visit_queue_no_show=@QueueNo";
SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@QueueNo", smsview.QueueNo);
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyCustName = new DataTable();
MyCustName.Load(dr);
MySqlConnection.Close();
return MyCustName;
}
public DataTable retrieveNamemobilenoByCustID(SMSView smsview)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
MySqlConnection.Open();
string sql = "select cr.customer_firstname,cr.customer_lastname,cd.members_mobile from tbl_customerreg_mst cr,tbl_customer_dtl cd where cr.customer_id=cd.members_customer_id and cr.customer_id=@CustID and cd.members_id=@members";
SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@CustID", smsview.CustId);
MySqlCommand.Parameters.AddWithValue("@members", smsview.MenberId);
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyCustName = new DataTable();
MyCustName.Load(dr);
MySqlConnection.Close();
return MyCustName;
}
public DataTable serachQueueStatus(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 DISTINCT tq.queue_status_id from tbl_queue_tnx tq, tbl_sms_tnx stx,tbl_customervisit_tnx ctv where stx.sms_content=ctv.visit_queue_no_show COLLATE SQL_Latin1_General_CP1_CI_AI and CONVERT(DATE, tq.queue_datetime) = CONVERT(DATE, GETDATE()) and stx.sms_status_flag='I'", MySqlConnection);
//MySqlCommand.Parameters.AddWithValue("@department_id", queueview.DepartmentID);
MySqlConnection.Open();
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyQSMSstatus = new DataTable();
MyQSMSstatus.Load(dr);
MySqlConnection.Close();
return MyQSMSstatus;
}
catch (Exception exmsg)
{
throw new Exception("Error Occured While Retrieving Data From DataBase", exmsg);
}
}
public DataTable updateincomingsms(SMSView smsview)
{
try
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
SqlCommand MySqlCommand = new SqlCommand("update tbl_incomingsms_mst set status_flag=@IncomingsmsStatus where incomingsms=@MySms and CONVERT(DATE, incoming_sms_datetime) = CONVERT(DATE, GETDATE())", MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@IncomingsmsStatus", smsview.InsmsStatus);
MySqlCommand.Parameters.AddWithValue("@MySms", smsview.MySms);
MySqlConnection.Open();
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyQSMSstatus = new DataTable();
MyQSMSstatus.Load(dr);
MySqlConnection.Close();
return MyQSMSstatus;
}
catch (Exception exmsg)
{
throw new Exception("Error Occured While Retrieving Data From DataBase", exmsg);
}
}
public DataTable GetAppointmentTransactionDetails(SMSView smsview)
{
try
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
string sql = "select * from tbl_customervisit_tnx where visit_customer_id=4350 and CONVERT(date,visit_datetime)=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 DataTable selectQueuePosition123(SMSView smsview)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
try
{
MySqlConnection.Open();
string sql = "select DISTINCT td.department_desc, q.queu_visit_tnxid,v.visit_queue_no_show,tc.members_mobile from tbl_department_mst td, 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.queue_department_id=@Departmentid and td.department_id=@Departmentid";
// 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'";
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)
{
MySqlConnection.Close();
throw new Exception("Error Occured While Retrieving Data From DataBase", ex);
}
finally
{
MySqlConnection.Close();
}
}
// SMS - Reply SMS Total Waiting Queue
#region SMS - Reply SMS Total Waiting Queue
public DataTable GetDaoTotalWaitingQueue(SMSView smsview)
{
try
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
SqlCommand MySqlCommand = new SqlCommand("select q.queue_tnx_id,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 v.visit_customer_id=c.customer_id and cast(q.queue_datetime as date) = cast(getdate() as date) and v.visit_queue_no<@queueno and q.queue_department_id=@departmentid and q.queue_Status_id=1", MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@queueno", smsview.QueueNo);
MySqlCommand.Parameters.AddWithValue("@departmentid", smsview.DepartmentID);
MySqlConnection.Open();
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyQueueNO = new DataTable();
MyQueueNO.Load(dr);
MySqlConnection.Close();
return MyQueueNO;
}
catch (Exception exmsg)
{
throw new Exception("Error Occured While Retrieving Data From DataBase", exmsg);
}
}
public DataTable retrieveNameByCustID(SMSView smsview)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
MySqlConnection.Open();
string sql = "select DISTINCT members_firstname,members_lastname,cd.members_mobile,cd.members_email from tbl_customer_dtl cd,tbl_customervisit_tnx tcv,tbl_customerreg_mst cr where cd.members_id=@memberid and cd.members_id=tcv.visit_member_id and members_customer_id=@CustID and cr.customer_id=tcv.visit_customer_id";
SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@CustID", smsview.CustId);
MySqlCommand.Parameters.AddWithValue("@memberid", smsview.MenberId);
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyCustName = new DataTable();
MyCustName.Load(dr);
MySqlConnection.Close();
return MyCustName;
}
public DataTable GetDaoQueueTokenGenerationSentSMS(SMSView smsview)
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
string sql = "update tbl_queue_tnx set message_status_flag=@smsstatusflag where queu_visit_tnxid=@queuevisittnxid";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@smsstatusflag", smsview.SMSStatusFlag);
cmd.Parameters.AddWithValue("@queuevisittnxid", smsview.QueueTransaction);
SqlDataReader dr = cmd.ExecuteReader();
DataTable MyMissedQueueNO = new DataTable();
MyMissedQueueNO.Load(dr);
con.Close();
return MyMissedQueueNO;
}
public DataTable retrievevisittnxidbyusingrepliedsms(SMSView smsview)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
MySqlConnection.Open();
string sql = "select visit_tnx_id from tbl_customervisit_tnx where visit_queue_no_show=@InSMS and CONVERT(DATE, visit_datetime) = CONVERT(DATE, GETDATE())";
SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@InSMS", smsview.MySms);
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyCustName = new DataTable();
MyCustName.Load(dr);
MySqlConnection.Close();
return MyCustName;
}
// SMS - Missed Queue Sending SMS
#region retrieve status flag
public DataTable RetrieveSMSstatusFlag(SMSView smsview)
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
string sql = "select message_status_flag from tbl_queue_tnx where queu_visit_tnxid=@queuevisittnxid";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@queuevisittnxid", smsview.QueueTransaction);
SqlDataReader dr = cmd.ExecuteReader();
DataTable MyMissedQueueNO = new DataTable();
MyMissedQueueNO.Load(dr);
con.Close();
return MyMissedQueueNO;
}
public DataTable SelectQueueStatus123(SMSView smsview)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
MySqlConnection.Open();
// string sql = "select ctv.visit_queue_no_show from tbl_queue_tnx tq,tbl_customervisit_tnx ctv where tq.queu_visit_tnxid=ctv.visit_tnx_id and queue_department_id=@DeptId and queue_status_id=1 and CONVERT(DATE, queue_datetime) = CONVERT(DATE, GETDATE()) order by ctv.consulting_status ASC, ctv.customer_appointment_time asc";
string sql = "select c.visit_queue_no_show from tbl_queue_tnx q,tbl_customervisit_tnx c where q.queue_department_id = @DeptId and q.queu_visit_tnxid = c.visit_tnx_id and CONVERT(DATE, q.queue_datetime) = CONVERT(DATE, GETDATE())and q.queue_status_id = 1 order by c.consulting_status ASC,c.customer_appointment_time ASC";
SqlCommand MySqlCommand = new SqlCommand(sql, MySqlConnection);
MySqlCommand.Parameters.AddWithValue("@DeptId", smsview.DepartmentID);
SqlDataReader dr = MySqlCommand.ExecuteReader();
DataTable MyDeptid = new DataTable();
MyDeptid.Load(dr);
MySqlConnection.Close();
return MyDeptid;
}
// SMS - Missed Queue Sent SMS
#region SMS - Missed Queue Sent SMS
public DataTable GetDaoAutoQueueSentSMS(SMSView smsview)
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
string sql = "update tbl_queue_tnx set sms_status_flag=@smsstatusflag where queue_tnx_id=@queuetnxid";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@smsstatusflag", smsview.SMSStatusFlag);
cmd.Parameters.AddWithValue("@queuetnxid", smsview.QueueTransaction);
SqlDataReader dr = cmd.ExecuteReader();
DataTable MyAutoSMS = new DataTable();
MyAutoSMS.Load(dr);
con.Close();
return MyAutoSMS;
}
public string InsertReplySMS(SMSView smsview)
{
SqlConnection MysqlConnection = new SqlConnection(ConnectionString);
try
{
MysqlConnection.Open();
string sql = "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.SMSStatusFlag);
cmd.Parameters.AddWithValue("@sms_content", smsview.MySms);
cmd.Parameters.AddWithValue("@QueueNo", smsview.QueueNo);
// cmd.Parameters.AddWithValue("@SmsDeliveryStatus", smsview.DeliveryReport);
cmd.ExecuteNonQuery();
cmd.Connection.Close();
string insert = Convert.ToString(1);
return insert;
}
catch (Exception)
{
throw;
}
}
public void AppRemender()
{
try
{
DataTable Appdetails = new DataTable();
SMSController smscont = new SMSController();
Appdetails = smscont.AppRemender();
DateTime re = Convert.ToDateTime("07:00");//Every Day at 7:00 AUS Time
//DateTime re = Convert.ToDateTime("23:10");
String ret = re.ToString("hh:mm");
DateTime syst = DateTime.Now;
string systime = syst.ToString("hh:mm");
if (ret == systime)
{
foreach (DataRow dr in Appdetails.Rows)
{
SMSView smsview = new SMSView();
int appid = Convert.ToInt32(dr["appointment_id"].ToString());
int custid = Convert.ToInt32(dr["appointment_customer_id"].ToString());
string cname = dr["customer_firstname"].ToString();
string appt = dr["appointment_time"].ToString();
int centreid = Convert.ToInt32(dr["appointment_centre_id"].ToString());
DateTime apt = Convert.ToDateTime(appt);
string cappt = apt.ToString("HH:mm");
string custaptime = apt.ToShortTimeString();
string mobnum = "61"+ dr["customer_mobile"].ToString();
string centrename;
if (centreid==1)
{
centrename = "Sydney";
}
else if (centreid == 58)
{
centrename = "Melbourne central";
}
else
{
centrename = "Highpoint";
}
string strmsg = "Dear " + cname + ",\r\nReminder your appointment with the Samsung Experience Store, " + centrename + " is at " + cappt + " today.\r\nPlease bring a copy of your purchase invoice and back-up your data before your appointment to avoid data loss, Looking forward to seeing you.";
//“Hi Kara, Reminder your appointment with the Samsung Experience Store, Sydney is at 11:15 today. Please bring a copy of your purchase invoice and back-up your data before your appointment to avoid data loss. Look forward to seeing you.”
smsview.SmsDesc = strmsg;
#region Samsung SMS gateway
//SMS for Samsung gateway
// Set the username of the account holder.
Messaging.MessageController.UserAccount.User = "******";
// Set the password of the account holder.
Messaging.MessageController.UserAccount.Password = "******";
// 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 = "*****@*****.**";
// Set the country of origin of the account holder (optional).
Messaging.MessageController.UserAccount.Country = Countries.Australia;
bool testOK = true;
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 = 60;
// Set the batch size (number of messages to be sent at once) to 200.
Messaging.MessageController.Settings.BatchSize = 200;
//string strmsg = "To confirm an appointment with the Samsung Experience Store,\r\nyou will need 4 characters password. The password is " + strrandom + "";
//string strmsg = "Hi " + " " + Cname + ", To finalize your appointment with the Samsung Experience Store at Sydney Central Plaza,\r\nplease enter these 4 characters" + strrandom + "password on the Confirmation screen. Thank you";
//string strmsg = "Hi " + " " + Cname + ", To finalize your appointment with the Samsung Experience Store at Sydney Central Plaza,\r\nplease enter these 4 characters" + strrandom + "password on the Confirmation screen. Thank you";
//string strmsg = "Hi" + " " + Cname + ",Your ticket number is:" + QueueTokenGenerationSMS + " . Thanks";
//"Hi Kara, your ticket number is 040, Approximate waiting time is 00:40 minutes/hours”
Messaging.MessageController.Settings.DeliveryReport = true;
SMSMessage smsobj = new SMSMessage(mobnum, strmsg);
Messaging.MessageController.AddToQueue(smsobj);
Messaging.MessageController.SendMessages();
//end of Samsung SMS
//smsview.SmsUpdatedDateTime = System.DateTime.Now;
//smsview.SmsActive = 'Y';
//smsview.SMSContentTypeId = 1;
//smsview.SmsAlert = 1;
//smsview.SmsUpdatedBy="Admin";
//string i;
//i = smscontroller.getInsertAppointmentAlertSms(smsview);
#endregion Samsung SMS gateway
#region Update SMS_Alert statsus flag
smsview.AppointmentID = appid;
smsview.SMSalert = 'B';
smscont.updatesmsalert(smsview);
#endregion Update SMS_Alert statsus flag
#region inserting to tbl_sms_tnx
smsview.CustId = custid;
smsview.SmsDesc = strmsg;
smsview.PhoneNo = mobnum;
smsview.DeliveryReport = "y";
smsview.SmsDesc = strmsg;
smsview.IncomingsmsFlag = "M";
smsview.SmsVisittnxId = 2;
smsview.SMSDateTime = System.DateTime.Now;
smsview.SMSStatusFlag = "M";
smsview.QueueNo = Convert.ToString("1");
smsview.CentreId = "";
smsview.SMSDateTime = System.DateTime.Now;
string i;
i = smscontroller.getInsertAppointmentAlertSms(smsview);
#endregion into tbl_sms_tnx
}
}
}
catch (Exception ex)
{
throw ex;
}
}
