public List <Cls_SMS> GetSmsNotSent()
{
List <Cls_SMS> SMSs = new List <Cls_SMS>();
string sql = @"SELECT s.ID, s.JobID,j.JobDate,s.PatientID, s.MobileNumber, s.TxtBody, s.TryCount FROM Tbl_Jobs as j INNER JOIN Tbl_SentSMS as s ON j.ID = s.JobID WHERE s.IsSent = 0 and s.TryCount < 3 ORDER BY j.CategoryID, j.JobDate;";
using (SqliteCommand command = new SqliteCommand(sql, m_dbConnection))
{
SqliteDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Cls_SMS sms = new Cls_SMS();
sms.ID = dr.GetInt32(0);
sms.JobID = dr.GetInt32(1);
//sms.JobDate = dr.GetDateTime(2);
sms.PatientID = dr.GetInt32(3);
sms.MobileNumber = dr.GetString(4);
sms.TxtBody = dr.GetString(5);
sms.TryCount = dr.GetInt16(6);
SMSs.Add(sms);
}
command.Dispose();
}
return(SMSs);
}
public void SendGroupNotificationForOneDayAppointmentsToContacts(List <cls_Contact> contacts, int JobID, DateTime date, string TxtBody)
{
DateTime d = new DateTime(date.Year, date.Month, date.Day);
//string TxtBodyTemplate = dal.GetSMSTextBodyTemplateByJobID(JobID);
if (TxtBody != null)
{
foreach (cls_Contact contact in contacts)
{
string bodyStr = TxtBody;// string.Format(TxtBodyTemplate, d.ToLongDateString());
Cls_SMS sms = new Cls_SMS();
sms.JobID = JobID;
sms.PatientID = int.Parse(contact.PatientID);
sms.MobileNumber = contact.Mobile;
sms.TxtBody = bodyStr;
sms.TryCount = 0;
sms.IsSent = false;
sms.ErrorTxt = "";
if (!dal.isSentSMSToMobile(sms.MobileNumber, sms.JobID))
{
dal.InsertSmsInfoToSentSMSTable(sms);
}
}
}
}
public void SendAnSMSToAllContacts(int jobID, string StrSmsBody)
{
int counter = 0;
foreach (cls_Contact contact in contacts)
{
try
{
if (!dal.isSentSMSToMobile(contact.Mobile, jobID))
{
Cls_SMS sms = new Cls_SMS();
sms.JobID = jobID;
sms.PatientID = int.Parse(contact.PatientID);
sms.MobileNumber = contact.Mobile;
sms.TxtBody = StrSmsBody;
sms.TryCount = 0;
sms.IsSent = false;
sms.ErrorTxt = "";
if (!dal.isSentSMSToMobile(sms.MobileNumber, sms.JobID))
{
dal.InsertSmsInfoToSentSMSTable(sms);
}
}
++counter;
TxtSmsCounter.Invoke(new Action(() => TxtSmsCounter.Text = counter.ToString()));
}
catch (Exception ex)
{
//MessageBox.Show("اتصال با مودم با مشکل مواجه شده است.");
logger.ErrorLog(ex.Message);
//throw (ex);
}
}
}
public void SendVisitConfirmationSmsToContact(cls_Appointment a, int JobID)
{
if (!dal.isSentSMSToMobile(a.contact.Mobile, JobID))
{
try
{
if (a.StartDateTime > DateTime.Now)
{
string TxtBodyTemplate = dal.GetSMSTextBodyTemplateByJobID(JobID);
if (TxtBodyTemplate != null)
{
string bodyStr = string.Format(TxtBodyTemplate, a.contact.FullName, a.StartDateTime.ToLongDateString(), a.StartDateTime.ToShortTimeString());
Cls_SMS sms = new Cls_SMS();
sms.JobID = JobID;
sms.PatientID = int.Parse(a.contact.PatientID);
sms.MobileNumber = a.contact.Mobile;
sms.TxtBody = bodyStr;
sms.TryCount = 0;
sms.IsSent = false;
sms.ErrorTxt = "";
dal.InsertSmsInfoToSentSMSTable(sms);
if (listBox1 != null)
{
listBox1.Invoke(new Action(() => listBox1.Items.Add(a.Date + " --- " + a.contact.FullName + " --- " + a.contact.Mobile + " --- " + a.contact.PatientID)));
}
}
}
}
catch (Exception ex)
{
logger.ErrorLog(ex.Message);
}
}
}
public bool SetErrorSentSMS(Cls_SMS sms)
{
try
{
string sql = "Update Tbl_SentSMS set trycount = " + sms.TryCount + ", ErrorTxt = '" + sms.ErrorTxt + "' where ID = " + sms.ID;
using (SqliteCommand command = new SqliteCommand(sql, m_dbConnection))
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool SetSuccessSentSMS(Cls_SMS sms)
{
try
{
string sql = "Update Tbl_SentSMS set IsSent = 1 where ID = " + sms.ID;
using (SqliteCommand command = new SqliteCommand(sql, m_dbConnection))
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool InsertSmsInfoToSentSMSTable(Cls_SMS sms)
{
try
{
string sql = "INSERT INTO[Tbl_SentSMS] ([JobID],[PatientID], [MobileNumber], [TxtBody], [TryCount], [IsSent], [ErrorTxt]) VALUES (" + sms.JobID + ", " + sms.PatientID + ", '" + sms.MobileNumber + "', '" + sms.TxtBody + "', 0, 0, '');";
using (SqliteCommand command = new SqliteCommand(sql, m_dbConnection))
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
