}// 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); }