public List<Message> CheckMessages(string userID)
{
string ID = string.Empty;
string personID = string.Empty;
string pkid = string.Empty;
string location = string.Empty;
List<Message> messages = new List<Message>();
try
{
using (SqlConnection cn = new SqlConnection(ConfigurationValues.GroveHillConnection))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT [ID],[FromID],[FromName],[ToID],[ToName],[Message],[Thread],[Show],[DateCreated]"
+ " FROM [Messages]"
+ " where ((ToID = @ToID) or (FromID = @ToID))"
+ " and show = 'T'"
+ " order by ID";
cm.CommandType = System.Data.CommandType.Text;
cm.Parameters.AddWithValue("@ToID", userID);
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Message message = new Message();
try
{
message.ID = int.Parse(dr["ID"].ToString());
}
catch { }
try
{
message.FromID = dr["FromID"].ToString();
}
catch { }
try
{
message.FromName = dr["FromName"].ToString();
}
catch { }
try
{
message.ToID = dr["ToID"].ToString();
}
catch { }
try
{
message.ToName = dr["ToName"].ToString();
}
catch { }
try
{
message.Note = dr["Message"].ToString();
}
catch { }
messages.Add(message);
}
}
}
return messages;
}
}
catch (Exception er)
{
return messages;
}
}
public List<Message> GetPatientMessages(string uniqueID)
{
string ID = string.Empty;
string personID = string.Empty;
string pkid = string.Empty;
string location = string.Empty;
string userList = string.Empty;
List<Message> messages = new List<Message>();
try
{
using (MySqlConnection cn = new MySqlConnection(ConfigurationValues.GroveHillConnection))
{
cn.Open();
using (MySqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT ID,FromID,FromName,ToID,ToName,Message,Thread,ShowMessage,DateCreated"
+ " FROM Messages"
+ " where ShowMessage = 'T'"
+ " and (fromid = @fromId or toid = @toid)";
cm.CommandType = System.Data.CommandType.Text;
cm.Parameters.AddWithValue("@fromId", uniqueID);
cm.Parameters.AddWithValue("@toid", uniqueID);
using (MySqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Message message = new Message();
try
{
message.ID = int.Parse(dr["ID"].ToString());
}
catch { }
try
{
message.FromID = dr["FromID"].ToString();
}
catch { }
try
{
message.FromName = dr["FromName"].ToString();
}
catch { }
try
{
message.ToID = dr["ToID"].ToString();
}
catch { }
try
{
message.ToName = dr["ToName"].ToString();
}
catch { }
try
{
message.Note = dr["Message"].ToString();
}
catch { }
try
{
message.DateCreated = dr.GetDateTime(8);
}
catch { }
messages.Add(message);
}
}
}
return messages;
}
}
catch (Exception er)
{
return messages;
}
}
public bool AddMessage(Message message)
{
//string fullMessage = "Time :" + message.DateCreated.ToShortDateString() + " " + message.DateCreated.ToShortTimeString()
// + " From: " + message.FromName
// + " To: " + message.ToName
// + " Message: " + message.Note;
string fullMessage = "Time :" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString()
+ " From: " + message.FromName
+ " To: " + message.ToName
+ " Message: " + message.Note;
try
{
using (IDbConnection db = new MySqlConnection(ConfigurationValues.GroveHillConnection))
{
const string query = "insert into Messages("
+ " FromID,FromName,ToID,ToName,Message,Thread,ShowMessage,DateCreated)"
+ " values(@FromID,@FromName,@ToID,@ToName,@Message,@Thread,@ShowMessage,@DateCreated)";
int rowsAffectd = db.Execute(query, new
{
@FromID = message.FromID,
@FromName = message.FromName,
@ToID = message.ToID,
@ToName = message.ToName,
@Message = fullMessage,
@Thread = message.Thread,
@ShowMessage = message.ShowMessage,
@DateCreated = DateTime.Now
});
int r = 5;
return true;
}
}
catch (Exception er)
{
string s1 = er.ToString();
return false;
//Log.LogMessage(er.ToString());
}
}
public List<Message> GetMessagesSentToOffice(string userID)
{
string ID = string.Empty;
string personID = string.Empty;
string pkid = string.Empty;
string location = string.Empty;
string userList = string.Empty;
List<Message> messages = new List<Message>();
if (userID == "0")
{
userList = GetWorkerListIDs();
}
else
{
userList = "('" + userID + "')";
}
try
{
using (SqlConnection cn = new SqlConnection(ConfigurationValues.GroveHillConnection))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT [ID],[FromID],[FromName],[ToID],[ToName],[Message],[Thread],[Show],[DateCreated]"
+ " FROM [Messages]"
+ " where ToID in " + userList
+ " and show = 'T'";
cm.CommandType = System.Data.CommandType.Text;
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Message message = new Message();
try
{
message.ID = int.Parse(dr["ID"].ToString());
}
catch { }
try
{
message.FromID = dr["FromID"].ToString();
}
catch { }
try
{
message.FromName = dr["FromName"].ToString();
}
catch { }
try
{
message.ToID = dr["ToID"].ToString();
}
catch { }
try
{
message.ToName = dr["ToName"].ToString();
}
catch { }
try
{
message.Note = dr["Message"].ToString();
}
catch { }
messages.Add(message);
}
}
}
return messages;
}
}
catch (Exception er)
{
return messages;
}
}