}// for storedprocedure
//internal List<TMRDOCTYPE> ViewMessageTable()
//{
// List<TMRDOCTYPE> lst = new List<TMRDOCTYPE>();
// using (SqlConnection conn = new SqlConnection(strConn))
// {
// using (SqlCommand cmd = new SqlCommand("TMR_VIEW_UNSENTDATA"))
// {
// cmd.CommandType = CommandType.StoredProcedure;
// //"select VENDORID, DOCNUMBR from TMRMESSAGETABLE"
// cmd.Connection = conn;
// conn.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// TMRDOCTYPE info = new TMRDOCTYPE();
// info.id = int.Parse(reader["id"].ToString());
// info.VENDORID = reader["VENDORID"].ToString();
// info.CUSTNMBR = reader["CUSTNMBR"].ToString();
// info.DOCNUMBR = reader["DOCNUMBR"].ToString();
// info.DOCTYPE = int.Parse(reader["DOCTYPE"].ToString());
// info.DUEDATE = DateTime.Parse(reader["DUEDATE"].ToString());
// info.DOCDATE = DateTime.Parse(reader["DOCDATE"].ToString());
// info.VCHRNMBR = reader["VCHRNMBR"].ToString();
// info.DOCAMNT = decimal.Parse(reader["DOCAMNT"].ToString());
// info.SERIES = reader["SERIES"].ToString();
// info.Status = int.Parse(reader["Status"].ToString());
// info.ErrorMsg = reader["ErrorMsg"].ToString();
// info.PhoneNo = reader["PhoneNo"].ToString();
// info.AttemptDate = DateTime.Parse(reader["AttemptDate"].ToString());
// lst.Add(info);
// }
// }
// }
// return lst;
//}
internal List <TMRMESSAGETABLE> ViewTMRTABLEListObjectByindx(TMRMESSAGETABLE ID)
{
List <TMRMESSAGETABLE> list = new List <TMRMESSAGETABLE>();
TMRMESSAGETABLE info = new TMRMESSAGETABLE();
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand("select VENDORID, DOCNUMBR from TMRMESSAGETABLE where VENDORID = @id"))
{
cmd.Parameters.AddWithValue("@id", ID.VENDORID);
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
info = new TMRMESSAGETABLE();
while (reader.Read())
{
info.VENDORID = reader["VENDORID"].ToString();;
info.DOCNUMBR = reader["DOCNUMBR"].ToString();
list.Add(info);
}
}
}
}
return(list);
}//for reference
internal List <TMRMESSAGETABLE> ViewUnsentMessages()
{
List <TMRMESSAGETABLE> lst = new List <TMRMESSAGETABLE>();
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand("TMR_VIEW_UNSENTDATA"))
{
cmd.CommandType = CommandType.StoredProcedure;
//"select VENDORID, DOCNUMBR from TMRMESSAGETABLE"
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TMRMESSAGETABLE info = new TMRMESSAGETABLE();
info.ID = int.Parse(reader["id"].ToString());
info.VENDORID = reader["VENDORID"].ToString();
info.CUSTNMBR = reader["CUSTNMBR"].ToString();
info.DOCNUMBR = reader["DOCNUMBR"].ToString();
info.DOCTYPE = int.Parse(reader["DOCTYPE"].ToString());
info.DUEDATE = DateTime.Parse(reader["DUEDATE"].ToString());
info.DOCDATE = DateTime.Parse(reader["DOCDATE"].ToString());
info.VCHRNMBR = reader["VCHRNMBR"].ToString();
info.DOCAMNT = decimal.Parse(reader["DOCAMNT"].ToString());
info.SERIES = reader["SERIES"].ToString();
info.Status = int.Parse(reader["Status"].ToString());
info.ErrorMsg = reader["ErrorMsg"].ToString();
info.PhoneNo1 = reader["PhoneNo1"].ToString();
info.PhoneNo2 = reader["PhoneNo2"].ToString();
info.PhoneNo3 = reader["PhoneNo3"].ToString();
info.AttemptDate = DateTime.Parse(reader["AttemptDate"].ToString());
lst.Add(info);
}
conn.Close();
}
}
return(lst);
}//for Tmrmessagetable
public bool UpdateTMRMessageTable(TMRMESSAGETABLE info)
{
int st = 0;
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
{
using (SqlCommand cmd = new SqlCommand("TMRUPDATEMESSAGETABLE"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@status", info.Status);
cmd.Parameters.AddWithValue("@errormsg", info.ErrorMsg);
//cmd.Parameters.AddWithValue("@phoneno", info.PhoneNo3);
cmd.Parameters.AddWithValue("@attemptdate", info.AttemptDate);
cmd.Parameters.AddWithValue("@id", info.ID);
st = cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (Exception ex) { }
if (st > 0)
{
return(true);
}
else
{
return(false);
}
}// for storedprocedure
internal List <TMRMESSAGETABLE> ViewFilteredMessages(string status, string series, DateTime startdate, DateTime enddate)
{
List <TMRMESSAGETABLE> lst = new List <TMRMESSAGETABLE>();
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand("ViewFilteredMessages"))
{
cmd.CommandType = CommandType.StoredProcedure;
int st = 0;
if (status == "All")
{
st = 0;
}
else if (status == "Unsent")
{
st = 1;
}
else if (status == "Sent")
{
st = 2;
}
else if (status == "Error")
{
st = 3;
}
cmd.Parameters.AddWithValue("@status", st);
cmd.Parameters.AddWithValue("@series", series);
cmd.Parameters.AddWithValue("@StartDate", startdate);
cmd.Parameters.AddWithValue("@EndDate", enddate);
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TMRMESSAGETABLE info = new TMRMESSAGETABLE();
info.ID = int.Parse(reader["id"].ToString());
info.VENDORID = reader["VENDORID"].ToString();
info.CUSTNMBR = reader["CUSTNMBR"].ToString();
info.DOCNUMBR = reader["DOCNUMBR"].ToString();
info.DOCTYPE = int.Parse(reader["DOCTYPE"].ToString());
info.DUEDATE = DateTime.Parse(reader["DUEDATE"].ToString());
info.DOCDATE = DateTime.Parse(reader["DOCDATE"].ToString());
info.VCHRNMBR = reader["VCHRNMBR"].ToString();
info.DOCAMNT = decimal.Parse(reader["DOCAMNT"].ToString());
info.SERIES = reader["SERIES"].ToString();
info.Status = int.Parse(reader["Status"].ToString());
info.ErrorMsg = reader["ErrorMsg"].ToString();
info.PhoneNo1 = reader["PhoneNo1"].ToString();
info.PhoneNo2 = reader["PhoneNo2"].ToString();
info.PhoneNo3 = reader["PhoneNo3"].ToString();
info.AttemptDate = DateTime.Parse(reader["AttemptDate"].ToString());
lst.Add(info);
}
conn.Close();
}
}
return(lst);
}
public bool InitiateSendingDocument(TMRMESSAGETABLE item)
{
string phoneNumber = "";
string phoneNumber2 = "";
string phoneNumber3 = "";
bool result1 = CheckPhoneValidity(item.PhoneNo1, out phoneNumber);
bool result2 = CheckPhoneValidity(item.PhoneNo2, out phoneNumber2);
bool result3 = CheckPhoneValidity(item.PhoneNo3, out phoneNumber3);
if (result1 == false)
{
lstIncorrectNbrs.Add(item.PhoneNo1);
item.ErrorMsg = "INCORRECTNBR";
item.Status = 4; //ERROR
}
if (result2 == false)
{
lstIncorrectNbrs.Add(item.PhoneNo2);
item.ErrorMsg = "INCORRECTNBR";
item.Status = 4; //ERROR
}
if (result3 == false)
{
lstIncorrectNbrs.Add(item.PhoneNo3);
item.ErrorMsg = "INCORRECTNBR";
item.Status = 4; //ERROR
}
var v = MessageTexts.Where(x => x.SERIES == item.SERIES && x.DOCTYPE == item.DOCTYPE).FirstOrDefault();
string MessageText = v.Message;
MessageText = MessageText.Replace("<DOCNUMBR>", string.Format("{0:F2}", item.DOCNUMBR).Trim());
MessageText = MessageText.Replace("<DOCAMNT>", item.DOCAMNT.ToString("N2"));
MessageText = MessageText.Replace("<DOCDATE>", ((DateTime)item.DOCDATE).ToString("dd-MM-yyyy"));
MessageText = MessageText.Replace("<DUEDATE>", ((DateTime)item.DOCDATE).ToString("dd-MM-yyyy"));
bool done = false, done2 = false, done3 = false;
try
{
if (result1)
{
done = clsSendSMS.SendSMS(phoneNumber, MessageText);
}
if (result2)
{
done2 = clsSendSMS.SendSMS(phoneNumber2, MessageText);
}
if (result3)
{
done3 = clsSendSMS.SendSMS(phoneNumber3, MessageText);
}
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex);
}
if (done == true || done2 == true || done3 == true)
{
item.Status = 2; //SENT
if (done && done2 && done3)
{
item.ErrorMsg = "Sent to all numbers.";
}
else if (!(done && done2 && done3))
{
item.ErrorMsg = "Not sent to all numbers.";
}
}
else if (done != true || done2 != true || done3 != true)
{
item.Status = 1; //SENT
item.ErrorMsg = "Failed to send to all numbers";
}
dAccess.UpdateTMRMessageTable(item);
return(true);
}
