public int UpdateSMSQueue(SMSQueue obj)
{
int i = 0;
try
{
using (MySqlConnection con = new MySqlConnection(ConnectionString.con_string))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "Update sms_sms_queue SET is_sent='Y' where id=@id";
cmd.Connection = con;
con.Open();
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
i = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(i);
}
public int UpdateSMSQueue(SMSQueue obj)
{
int i = 0;
try
{
using (MySqlConnection con = new MySqlConnection(ConnectionString.con_string))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "Update sms_sms_queue SET is_sent='Y', updated_date_time=@updated_date_time, sender_com_port=@sender_com_port, sender_cell_no=@sender_cell_no, sms_length=@sms_length where id=@id";
cmd.Connection = con;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
cmd.Parameters.Add("@updated_date_time", MySqlDbType.DateTime).Value = obj.updated_date_time;
cmd.Parameters.Add("@sender_com_port", MySqlDbType.VarChar).Value = obj.sender_com_port;
cmd.Parameters.Add("@sender_cell_no", MySqlDbType.VarChar).Value = obj.sender_cell_no;
cmd.Parameters.Add("@sms_length", MySqlDbType.Int32).Value = obj.sms_length;
con.Open();
i = Convert.ToInt32(cmd.ExecuteNonQuery());
con.Close();
}
}
}
catch (MySqlException ex)
{
throw ex;
}
return(i);
}
public List <SMSQueue> GetSMSQueueOnline(MySqlConnection con)
{
List <SMSQueue> lst = new List <SMSQueue>();
try
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "Select * from sms_sms_queue where is_sent='N'";
MySqlDataReader reader = cmd.ExecuteReader();
SMSQueue obj;
while (reader.Read())
{
obj = new SMSQueue()
{
id = Convert.ToInt32(reader["id"]),
receiver_id = Convert.ToInt32(reader["receiver_id"]),
receiver_name = Convert.ToString(reader["receiver_name"]),
receiver_cell_no = Convert.ToString(reader["receiver_cell_no"]),
receiver_type_id = Convert.ToInt32(reader["receiver_type_id"]),
sms_message = Convert.ToString(reader["sms_message"]),
sms_type = Convert.ToString(reader["sms_type"]),
sms_length = Convert.ToInt32(reader["sms_length"]),
sms_type_id = Convert.ToInt32(reader["sms_type_id"]),
created_by = Convert.ToString(reader["created_by"]),
emp_id = Convert.ToInt32(reader["emp_id"]),
sort_order = Convert.ToInt32(reader["sort_order"]),
date_time = Convert.ToDateTime(reader["date_time"]),
class_id = 0,
section_id = 0,
is_sent = Convert.ToString(reader["is_sent"]),
is_periority = Convert.ToString(reader["is_periority"]),
sender_cell_no = Convert.ToString(reader["sender_cell_no"]),
sender_com_port = Convert.ToString(reader["sender_com_port"]),
institute_id = Convert.ToInt32(reader["institute_id"]),
institute_name = Convert.ToString(reader["institute_name"]),
institute_cell = Convert.ToString(reader["institute_cell"]),
created_date_time = Convert.ToDateTime(reader["created_date_time"]),
downloaded_date_time = Convert.ToDateTime(reader["downloaded_date_time"]),
updated_date_time = Convert.ToDateTime(reader["updated_date_time"]),
isEncoded = Convert.ToInt32(reader["isEncoded"]),
};
lst.Add(obj);
}
reader.Close();
};
}
catch (Exception ex)
{
throw ex;
}
return(lst);
}
async void SendMessage(SMSQueue obj, Modem comm, BackgroundWorker worker, DoWorkEventArgs e)
{
try
{
await SendMessageAsync(obj, comm, worker, e);
}
catch (Exception ex)
{
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " IsFree=" + comm.IsFree + " Exception";
AddLog(EventLevel.Error.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
}
public List <SMSQueue> GetSMSQueue()
{
List <SMSQueue> lst = new List <SMSQueue>();
try
{
using (MySqlConnection con = new MySqlConnection(ConnectionString.con_string))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "Select * from sms_sms_queue where is_sent='N'";
con.Open();
MySqlDataReader reader = cmd.ExecuteReader();
SMSQueue obj;
while (reader.Read())
{
obj = new SMSQueue()
{
id = Convert.ToInt32(reader["id"]),
receiver_id = Convert.ToInt32(reader["receiver_id"]),
receiver_name = Convert.ToString(reader["receiver_name"]),
receiver_cell_no = Convert.ToString(reader["receiver_cell_no"]),
receiver_type_id = Convert.ToInt32(reader["receiver_type_id"]),
sms_message = Convert.ToString(reader["sms_message"]),
sms_type = Convert.ToString(reader["sms_type"]),
sms_type_id = Convert.ToInt32(reader["sms_type_id"]),
created_by = Convert.ToString(reader["created_by"]),
emp_id = Convert.ToInt32(reader["emp_id"]),
sort_order = Convert.ToInt32(reader["sort_order"]),
date_time = Convert.ToDateTime(reader["date_time"]),
class_id = 0,
section_id = 0,
};
lst.Add(obj);
}
};
};
}
catch (Exception ex)
{
throw ex;
}
return(lst);
}
public int insertIntoQueue(SMSQueue obj)
{
int i = 0;
try
{
using (MySqlConnection con = new MySqlConnection(ConnectionString.con_string))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "INSERT INTO sms_sms_queue(receiver_id,receiver_type_id, receiver_cell_no, receiver_name, sms_message, sms_type, sms_type_id, sort_order, created_by, date_time, emp_id) Values(@receiver_id, @receiver_type_id, @receiver_cell_no, @receiver_name, @sms_message, @sms_type, @sms_type_id, @sort_order, @created_by, @date_time, @emp_id)";
cmd.Connection = con;
cmd.Parameters.Add("@receiver_id", MySqlDbType.Int32).Value = obj.receiver_id;
cmd.Parameters.Add("@receiver_type_id", MySqlDbType.Int32).Value = obj.receiver_type_id;
cmd.Parameters.Add("@receiver_cell_no", MySqlDbType.VarChar).Value = obj.receiver_cell_no;
cmd.Parameters.Add("@receiver_name", MySqlDbType.VarChar).Value = obj.receiver_name;
cmd.Parameters.Add("@sms_message", MySqlDbType.VarChar).Value = obj.sms_message;
cmd.Parameters.Add("@sms_type", MySqlDbType.VarChar).Value = obj.sms_type;
cmd.Parameters.Add("@sms_type_id", MySqlDbType.Int32).Value = obj.sms_type_id;
cmd.Parameters.Add("@sort_order", MySqlDbType.Int32).Value = obj.sort_order;
cmd.Parameters.Add("@created_by", MySqlDbType.VarChar).Value = obj.created_by;
cmd.Parameters.Add("@date_time", MySqlDbType.DateTime).Value = obj.date_time;
cmd.Parameters.Add("@emp_id", MySqlDbType.Int32).Value = obj.emp_id;
i = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
con.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(i);
}
public int UpdateOnlineSMSQueueAndLocalSMSQueueSynchronization(SMSQueue obj, MySqlConnection conLocal, MySqlConnection conOnline)
{
int i = 0;
try
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "Update sms_sms_queue SET is_sent='Y', updated_date_time=@updated_date_time, downloaded_date_time=@downloaded_date_time where id=@id";
cmd.Connection = conOnline;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
cmd.Parameters.Add("@updated_date_time", MySqlDbType.DateTime).Value = obj.updated_date_time;
cmd.Parameters.Add("@downloaded_date_time", MySqlDbType.DateTime).Value = obj.downloaded_date_time;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
i = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
if (i > 0)
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "Update sms_sms_queue SET isSynchronized=1 where id=@id";
cmd.Connection = conLocal;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
i = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
}
}
catch (MySqlException ex)
{
throw ex;
}
return(i);
}
private void Window_KeyDown(object sender, KeyEventArgs e)
{
Debug.WriteLine(e.Key.ToString());
string input = e.Key.ToString();
if (e.Key == Key.Enter)
{
if (!string.IsNullOrEmpty(textInput) && !string.IsNullOrWhiteSpace(textInput))
{
try
{
rfid_assignment rfidObj = rfidDAL.GetIDFromRfidCArdNo(textInput);
if (rfidObj != null)
{
if (rfidObj.is_std == "Y")
{
student = studentDAL.getStudentInfo(rfidObj.card_holder_id);
}
else
{
//employee
}
if (student != null && student.std_name != null)
{
StudentAttendance sa = new StudentAttendance();
sa.std_id = student.id;
sa.std_name = student.std_name;
sa.class_id = student.class_id;
sa.section_id = student.section_id;
sa.date_time = DateTime.Now;
sa.created_by = "AttendanceAdmin";
sa.roll_no = student.roll_no;
sa.attendence_date = DateTime.Now;
sa.attendence = 'P';
if (studentDAL.insertStudentAttendance(sa) > 0)
{
SMSQueue queue = new SMSQueue()
{
receiver_id = Convert.ToInt32(student.id),
receiver_name = student.std_name,
receiver_cell_no = student.cell_no,
receiver_type_id = 1,
sms_message = (btnInOut.Content.ToString() == "IN")?("Respected Parents," + Environment.NewLine + "AoA," + Environment.NewLine + student.std_name + " has entered in school at " + DateTime.Now.ToString("hh:mm tt") + Environment.NewLine + "On " + DateTime.Now.ToString("dd-MMM-yy") + "." + Environment.NewLine + "Admin " + institue.institute_name + "." + Environment.NewLine + institue.institute_phone + Environment.NewLine + institue.institute_cell): ("Respected Parents," + Environment.NewLine + "AoA," + Environment.NewLine + student.std_name + " has exited from school at " + DateTime.Now.ToString("hh:mm") + Environment.NewLine + "On " + DateTime.Now.ToString("dd-MMM-yy") + "." + Environment.NewLine + "Admin " + institue.institute_name + "." + Environment.NewLine + institue.institute_phone + Environment.NewLine + institue.institute_cell),
sms_type = "RFID Attendance SMS",
sms_type_id = 9,
created_by = "admin",
date_time = DateTime.Now,
emp_id = 0,
sort_order = 1
};
if (miscDAL.insertIntoQueue(queue) > 0)
{
count++;
student.count = count;
StudentSP.DataContext = student;
studentProfileSP.DataContext = student;
listview1.Items.Insert(0, student);
msgGRID.Visibility = Visibility.Collapsed;
}
else
{
Debug.WriteLine("Attendence queue Not Inserted");
msgTB.Text = "Attendance queue Not Inserted";
msgGRID.Visibility = Visibility.Visible;
}
}
else
{
Debug.WriteLine("Attendance Not Inserted");
msgTB.Text = "Attendance Not Inserted";
msgGRID.Visibility = Visibility.Visible;
}
}
}
else
{
Debug.WriteLine("Attendance Not Inserted");
msgTB.Text = "Incorrect Login";
msgGRID.Visibility = Visibility.Visible;
}
textInput = "";
}
catch (Exception ex)
{
Debug.WriteLine(ex);
msgGRID.Visibility = Visibility.Visible;
msgTB.Text = ex.ToString();
}
}
}
else
{
if (input.Contains("Number"))
{
input = input.Substring(6);
textInput = textInput + input;
}
if (input.Contains("D"))
{
input = input.Substring(1);
textInput = textInput + input;
}
if (input.Equals(189))
{
input = "-";
textInput = textInput + input;
}
}
}
public bool sendMsg(SMSQueue obj, BackgroundWorker worker, DoWorkEventArgs e)
{
try
{
bool isSend = false;
i = 0;
#region PDU Creation
SmsSubmitPdu[] pdu;
try
{
if (m_IsEncoded)
{
pdu = CreateConcatTextMessage(obj.sms_message, true, Convert.ToString("+92" + obj.receiver_cell_no));
}
else
{
pdu = CreateConcatTextMessage(obj.sms_message, false, Convert.ToString("+92" + obj.receiver_cell_no));
}
}
catch (Exception ex)
{
return(false);
}
#endregion
#region Message Sedning
if (comm.IsConnected() && comm.IsOpen())
{
comm.SendMessages(pdu);
isSend = true;
obj.sms_status = "Sent";
}
else
{
Thread.Sleep(500);
openPort();
isSend = false;
obj.sms_status = "Not Sent";
Thread.Sleep(1000);
return(false);
}
#endregion
#region Insert History And Update queue
sh = new SMSHistory();
sh.sender_id = obj.id.ToString();
sh.sender_name = obj.receiver_name;
sh.class_id = obj.class_id.ToString();
sh.class_name = obj.class_name;
sh.section_id = obj.section_id.ToString();
sh.section_name = obj.section_name;
sh.cell = obj.receiver_cell_no;
sh.msg = obj.sms_message;
sh.sms_type = obj.sms_type;
sh.created_by = obj.created_by;
sh.date_time = DateTime.Now;
if (miscDAL.InsertSMSHistory(sh) > 0)
{
if (miscDAL.UpdateSMSQueue(obj) > 0)
{
}
else
{
//MessageBox.Show("Not updated sms queue");
}
}
else
{
//MessageBox.Show("Sms History not inserted");
}
return(isSend);
#endregion
}
catch (Exception ex)
{
return(false);
}
}
private void sendMsg(SMSQueue obj, Modem comm, BackgroundWorker worker, DoWorkEventArgs e)
{
try
{
obj.sms_message = ValidateMessage(obj.sms_message);
bool isSend = false;
comm.IsFree = false;
UpdateModemStatus(comm);
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " IsFree=" + comm.IsFree + " Start Sending Message";
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
i = 0;
SmsSubmitPdu[] pdu;
if (m_IsEncoded)
{
pdu = CreateConcatTextMessage(obj.sms_message, true, Convert.ToString("+92" + obj.receiver_cell_no));
}
else
{
pdu = CreateConcatTextMessage(obj.sms_message, false, Convert.ToString("+92" + obj.receiver_cell_no));
}
for (int j = 0; j < pdu.Length; j++)
{
try
{
if (comm.GsmCommMain.IsConnected() && comm.GsmCommMain.IsOpen() && Modems.Count > 0)
{
comm.GsmCommMain.SendMessage(pdu[j], true);
//comm.GsmCommMain.EnablePermanentSmsBatchMode();
Thread.Sleep(1000);
isSend = true;
m_TotalSmsSent++;
if (j + 1 == pdu.Length)
{
if (comm.TotalSmsSent == 0)
{
comm.StartTime = DateTime.Now;
}
comm.IsFree = true;
comm.TotalSmsSent = comm.TotalSmsSent + pdu.Length;
UpdateModemStatus(comm);
isWholeSent = true;
obj.sms_status = "Sent";
obj.updated_date_time = DateTime.Now;
obj.sender_com_port = comm.GsmCommMain.PortName;
obj.sms_length = pdu.Length;
//obj.sender_cell_no = comm.GsmCommMain.GetSmscAddress().Address;
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " IsFree=True, Receiver=" + obj.receiver_cell_no + " Message:" + obj.sms_message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
}
else
{
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " IsConnected=" + comm.GsmCommMain.IsConnected() + " IsOpen=" + comm.GsmCommMain.IsOpen();
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
//j--;
isSend = false;
comm.IsFree = false;
UpdateModemStatus(comm);
obj.sms_status = "Not Sent";
Thread.Sleep(1000);
if (!comm.GsmCommMain.IsOpen())
{
OpenPort(comm.GsmCommMain.PortName);
}
//j--;
}
}
catch (Exception ex)
{
if (ex.Message.Contains("Message service error 500 occurred."))
{
try
{
Thread.Sleep(1000);
comm.GsmCommMain.Close();
Modems.Remove(Modems.Where(x => x.GsmCommMain.PortName == comm.GsmCommMain.PortName).First());
message = comm.GsmCommMain.PortName + " Removed";
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
catch (Exception exx)
{
message = comm.GsmCommMain.PortName + " Removed Exception: " + exx.Message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
finally
{
CloseWindow();
}
Thread.Sleep(1000);
}
//Access is denied.
else if (ex.Message.Contains("Access is denied."))
{
}
else if (ex.Message.Contains("Unexpected response received from phone"))
{
//Unexpected response received from phone: warid
comm.IsFree = true;
if (comm.TotalSmsSent == 0)
{
comm.StartTime = DateTime.Now;
}
comm.TotalSmsSent = comm.TotalSmsSent + pdu.Length;
UpdateModemStatus(comm);
isSend = true;
obj.sms_status = "Sent";
obj.updated_date_time = DateTime.Now;
obj.sender_com_port = comm.GsmCommMain.PortName;
obj.sms_length = pdu.Length;
//obj.sender_cell_no = comm.GsmCommMain.GetSmscAddress().Address;
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " IsFree=True, Receiver=" + obj.receiver_cell_no + " Message:" + obj.sms_message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
else if (ex.Message.Contains("No data received from phone after waiting for"))
{
try
{
Thread.Sleep(1000);
comm.GsmCommMain.Close();
Modems.Remove(Modems.Where(x => x.GsmCommMain.PortName == comm.GsmCommMain.PortName).First());
message = comm.GsmCommMain.PortName + " Removed";
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
catch (Exception exx)
{
message = comm.GsmCommMain.PortName + " Removed Exception: " + exx.Message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
finally
{
CloseWindow();
}
Thread.Sleep(1000);
}
else if (ex.Message.Contains("The character"))
{
//The character '' at position 10 does not exist in the GSM 7-bit default alphabet.
try
{
Thread.Sleep(1000);
comm.IsFree = true;
UpdateModemStatus(comm);
message = comm.GsmCommMain.PortName + " Invalid Character: " + ex.Message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
catch (Exception exm)
{
}
finally
{
}
}
else
{
comm.IsFree = false;
UpdateModemStatus(comm);
isSend = false;
obj.sms_status = "Not Sent";
}
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " Exception:" + ex.Message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
}
// saved to sms history table whether sent or not
//change for queue
if (isSend)
{
sh = new SMSHistory();
sh.sender_id = obj.id.ToString();
sh.sender_name = obj.receiver_name;
sh.class_id = obj.class_id.ToString();
sh.class_name = obj.class_name;
sh.section_id = obj.section_id.ToString();
sh.section_name = obj.section_name;
sh.cell = obj.receiver_cell_no;
sh.msg = obj.sms_message;
sh.sms_type = obj.sms_type;
sh.created_by = obj.created_by;
sh.date_time = DateTime.Now;
if (miscDAL.InsertSMSHistory(sh) > 0)
{
if (miscDAL.UpdateSMSQueue(obj) > 0)
{
}
else
{
MessageBox.Show("Not updated sms queue");
}
}
else
{
MessageBox.Show("Sms History not inserted");
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("The character"))
{
//The character '' at position 10 does not exist in the GSM 7-bit default alphabet.
try
{
Thread.Sleep(1000);
comm.IsFree = true;
UpdateModemStatus(comm);
message = comm.GsmCommMain.PortName + " Invalid Character: " + ex.Message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
catch (Exception exm)
{
}
finally
{
}
}
else
{
message = comm.GsmCommMain.PortName + "-" + comm.TotalSmsSent + " Exception:" + ex.Message;
AddLog(EventLevel.Warning.ToString(), DateTime.Now, EventSource.SendMessage.ToString(), message);
}
}
}
private Task SendMessageAsync(SMSQueue obj, Modem comm, BackgroundWorker worker, DoWorkEventArgs e)
{
return(Task.Run(() => sendMsg(obj, comm, worker, e)));
}
//Online Synchronizer
public int SynchronizeSMSQueue(MySqlConnection conLocal, MySqlConnection conOnline)
{
List <SMSQueue> lst = new List <SMSQueue>();
int result = 0;
try
{
try
{
//Get all unsynchorinized item from local db
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conLocal;
cmd.CommandText = "Select * from sms_sms_queue where is_sent='Y' && isSynchronized=0";
MySqlDataReader reader = cmd.ExecuteReader();
SMSQueue obj;
while (reader.Read())
{
obj = new SMSQueue()
{
id = Convert.ToInt32(reader["id"]),
updated_date_time = HelperClass.ConvertFromDBVal <DateTime>(reader["updated_date_time"]),
downloaded_date_time = HelperClass.ConvertFromDBVal <DateTime>(reader["downloaded_date_time"]),
};
lst.Add(obj);
}
reader.Close();
};
}
catch (Exception ex)
{
}
//UpdateOnlineSMSQueueAndLocalSMSQueueSynchronization
try
{
foreach (var obj in lst)
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "Update sms_sms_queue SET is_sent='Y', updated_date_time=@updated_date_time, sender_com_port=@sender_com_port, sender_cell_no=@sender_cell_no, sms_length=@sms_length, downloaded_date_time=@downloaded_date_time, isSynchronized=@isSynchronized where id=@id";
cmd.Connection = conOnline;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
cmd.Parameters.Add("@updated_date_time", MySqlDbType.DateTime).Value = obj.updated_date_time;
cmd.Parameters.Add("@downloaded_date_time", MySqlDbType.DateTime).Value = obj.downloaded_date_time;
cmd.Parameters.Add("@sender_com_port", MySqlDbType.VarChar).Value = obj.sender_com_port;
cmd.Parameters.Add("@sender_cell_no", MySqlDbType.VarChar).Value = obj.sender_cell_no;
cmd.Parameters.Add("@sms_length", MySqlDbType.Int32).Value = obj.sms_length;
cmd.Parameters.Add("@isSynchronized", MySqlDbType.Int32).Value = 1;
result = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
if (result > 0)
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "Update sms_sms_queue SET isSynchronized=1 where id=@id";
cmd.Connection = conLocal;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
result = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
}
}
}
catch (Exception ex)
{
}
// get all items from online where is_Sent=N
try
{
lst = new List <SMSQueue>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conOnline;
cmd.CommandText = "Select * from sms_sms_queue where is_sent='N'";
MySqlDataReader reader = cmd.ExecuteReader();
SMSQueue obj;
while (reader.Read())
{
obj = new SMSQueue()
{
id = Convert.ToInt32(reader["id"]),
receiver_id = Convert.ToInt32(reader["receiver_id"]),
receiver_name = Convert.ToString(reader["receiver_name"]),
receiver_cell_no = Convert.ToString(reader["receiver_cell_no"]),
receiver_type_id = Convert.ToInt32(reader["receiver_type_id"]),
sms_message = Convert.ToString(reader["sms_message"]),
sms_type = Convert.ToString(reader["sms_type"]),
sms_length = Convert.ToInt32(reader["sms_length"]),
sms_type_id = Convert.ToInt32(reader["sms_type_id"]),
created_by = Convert.ToString(reader["created_by"]),
emp_id = Convert.ToInt32(reader["emp_id"]),
sort_order = Convert.ToInt32(reader["sort_order"]),
date_time = HelperClass.ConvertFromDBVal <DateTime>(reader["date_time"]),
class_id = 0,
section_id = 0,
is_sent = Convert.ToString(reader["is_sent"]),
is_periority = Convert.ToString(reader["is_periority"]),
sender_cell_no = Convert.ToString(reader["sender_cell_no"]),
sender_com_port = Convert.ToString(reader["sender_com_port"]),
institute_id = Convert.ToInt32(reader["institute_id"]),
institute_name = Convert.ToString(reader["institute_name"]),
institute_cell = Convert.ToString(reader["institute_cell"]),
created_date_time = HelperClass.ConvertFromDBVal <DateTime>(reader["created_date_time"]),
downloaded_date_time = HelperClass.ConvertFromDBVal <DateTime>(reader["downloaded_date_time"]),
updated_date_time = HelperClass.ConvertFromDBVal <DateTime>(reader["updated_date_time"]),
isEncoded = Convert.ToInt32(reader["isEncoded"]),
isSynchronized = Convert.ToInt32(reader["isSynchronized"]),
};
lst.Add(obj);
}
reader.Close();
};
}
catch (Exception ex)
{
}
// insert in local database
foreach (var obj in lst)
{
try
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = "INSERT INTO sms_sms_queue(id, receiver_id,receiver_type_id, receiver_cell_no, receiver_name, sms_message, sms_type, sms_type_id, sms_length, sort_order, created_by, date_time, emp_id, is_sent, is_periority, sender_cell_no, sender_com_port, institute_id, institute_name, institute_cell, created_date_time, downloaded_date_time) " +
"Values(@id, @receiver_id, @receiver_type_id, @receiver_cell_no, @receiver_name, @sms_message, @sms_type, @sms_type_id, @sms_length, @sort_order, @created_by, @date_time, @emp_id, @is_sent, @is_periority, @sender_cell_no, @sender_com_port, @institute_id, @institute_name, @institute_cell, @created_date_time, @downloaded_date_time)";
cmd.Connection = conLocal;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = obj.id;
cmd.Parameters.Add("@receiver_id", MySqlDbType.Int32).Value = obj.receiver_id;
cmd.Parameters.Add("@receiver_type_id", MySqlDbType.Int32).Value = obj.receiver_type_id;
cmd.Parameters.Add("@receiver_cell_no", MySqlDbType.VarChar).Value = obj.receiver_cell_no;
cmd.Parameters.Add("@receiver_name", MySqlDbType.VarChar).Value = obj.receiver_name;
cmd.Parameters.Add("@sms_message", MySqlDbType.VarChar).Value = obj.sms_message;
cmd.Parameters.Add("@sms_type", MySqlDbType.VarChar).Value = obj.sms_type;
cmd.Parameters.Add("@sms_type_id", MySqlDbType.Int32).Value = obj.sms_type_id;
cmd.Parameters.Add("@sms_length", MySqlDbType.Int32).Value = obj.sms_length;
cmd.Parameters.Add("@sort_order", MySqlDbType.Int32).Value = obj.sort_order;
cmd.Parameters.Add("@created_by", MySqlDbType.VarChar).Value = obj.created_by;
cmd.Parameters.Add("@date_time", MySqlDbType.DateTime).Value = obj.date_time;
cmd.Parameters.Add("@emp_id", MySqlDbType.Int32).Value = obj.emp_id;
cmd.Parameters.Add("@is_sent", MySqlDbType.VarChar).Value = obj.is_sent;
cmd.Parameters.Add("@is_periority", MySqlDbType.VarChar).Value = obj.is_periority;
cmd.Parameters.Add("@sender_cell_no", MySqlDbType.VarChar).Value = obj.sender_cell_no;
cmd.Parameters.Add("@sender_com_port", MySqlDbType.VarChar).Value = obj.sender_com_port;
cmd.Parameters.Add("@institute_id", MySqlDbType.Int32).Value = obj.institute_id;
cmd.Parameters.Add("@institute_name", MySqlDbType.VarChar).Value = obj.institute_name;
cmd.Parameters.Add("@institute_cell", MySqlDbType.VarChar).Value = obj.institute_cell;
cmd.Parameters.Add("@created_date_time", MySqlDbType.DateTime).Value = obj.created_date_time;
cmd.Parameters.Add("@downloaded_date_time", MySqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@isSynchronized", MySqlDbType.Int32).Value = obj.isSynchronized;
cmd.Parameters.Add("@isEncoded", MySqlDbType.Int32).Value = obj.isEncoded;
result = Convert.ToInt32(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
}
}
catch (MySqlException ex)
{
//To avoid duplicataion
//it can be happended that item may be already exists in local db but still not sent
}
}
}
catch (MySqlException ex)
{
throw ex;
}
return(result);
}
