public IHttpActionResult RefreshTokenById([FromBody] object data)
{
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
string token = headers.GetValues(Models.User.COL_TOKEN).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList assignments = new ArrayList();
ArrayList conditions = new ArrayList();
string statement;
assignments.Add(Models.User.COL_TOKEN + "=" + QueryGenerator.QuoteString(token));
conditions.Add(Models.User.COL_ID + "=" + id);
statement = QueryGenerator.GenerateSqlUpdate(Models.User.TABLE, assignments, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult CreateUser([FromBody] object data)
{
try
{
var headers = Request.Headers;
string userName = headers.GetValues(Models.User.COL_USERNAME).First();
string email = headers.GetValues(Models.User.COL_EMAIL).First();
string password = headers.GetValues(Models.User.COL_PASSWORD).First();
string phone = headers.GetValues(Models.User.COL_PHONE).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList values = new ArrayList();
string statement = string.Empty;
values.Add(QueryGenerator.QuoteString(userName));
values.Add(QueryGenerator.QuoteString(email));
values.Add(QueryGenerator.QuoteString(password));
values.Add(phone);
values.Add("0"); // Login
values.Add("NULL"); // LastLogin
values.Add("1"); // StatusID
values.Add("0"); // Deleted
statement = QueryGenerator.GenerateSqlInsert(values, Models.User.TABLE);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch (Exception e)
{
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.BadRequest, false));
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, true));
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult CreateLocation([FromBody] object data)
{
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
string lat = headers.GetValues(Location.COL_LAT).First();
string lng = headers.GetValues(Location.COL_LNG).First();
string time = headers.GetValues(Location.COL_ALERTTIME).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList values = new ArrayList();
ArrayList assignments = new ArrayList();
string statement = string.Empty;
values.Add(id);
values.Add(lat);
values.Add(lng);
values.Add(QueryGenerator.QuoteString(time));
statement = QueryGenerator.GenerateSqlInsert(values, Location.TABLE);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch
{
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.BadRequest, false));
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, true));
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult GetLocationById([FromBody] object data)
{
Location location = new Location();
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
ArrayList orders = new ArrayList();
string statement = string.Empty;
colums.Add(Location.COL_ID);
colums.Add(Location.COL_USERID);
colums.Add(Location.COL_LAT);
colums.Add(Location.COL_LNG);
colums.Add(Location.COL_ALERTTIME);
conditions.Add(Location.COL_USERID + "=" + id);
orders.Add(Location.COL_ALERTTIME);
statement = QueryGenerator.GenerateSqlSelect(colums,
Location.TABLE,
conditions,
orders,
QueryGenerator.KW_DSC,
1);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
int i = 0;
location.ID = dr.GetInt32(i);
i++;
location.UserID = dr.GetInt32(i);
i++;
location.Lat = dr.GetDouble(i);
i++;
location.Lng = dr.GetDouble(i);
i++;
location.AlertTime = dr.GetDateTime(i);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
if (location.ID == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No location is found.", "")));
}
return(Ok(new { location }));
}
public IHttpActionResult RemoveLinkedUser([FromBody] object data)
{
try
{
var headers = Request.Headers;
string idMe = headers.GetValues(Models.User.COL_ID).First();
string idTarget = headers.GetValues(LinkedUser.PARAM_TARGET).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// conditions 1
ArrayList conditions1 = new ArrayList();
string c1;
ArrayList columnsC1 = new ArrayList();
ArrayList conditionsC1 = new ArrayList();
columnsC1.Add(LinkedUser.COL_ID);
conditionsC1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
c1 = QueryGenerator.GenerateSqlSelect(columnsC1, LinkedUser.TABLE, conditionsC1
, null, QueryGenerator.KW_ASC, 1);
c1 = QueryGenerator.KW_EXISTS + QueryGenerator.SPACE + QueryGenerator.ParenthesisString(c1);
conditions1.Add(c1);
// conditions 2
ArrayList conditions2 = new ArrayList();
string c2;
ArrayList columnsC2 = new ArrayList();
ArrayList conditionsC2 = new ArrayList();
columnsC2.Add(LinkedUser.COL_ID);
conditionsC2.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
c2 = QueryGenerator.GenerateSqlSelect(columnsC2, LinkedUser.TABLE, conditionsC2
, null, QueryGenerator.KW_ASC, 1);
c2 = QueryGenerator.KW_EXISTS + QueryGenerator.SPACE + QueryGenerator.ParenthesisString(c2);
conditions2.Add(c2);
// statement 1
ArrayList assignS1 = new ArrayList();
ArrayList conditionS1 = new ArrayList();
string s1;
assignS1.Add(LinkedUser.COL_ADDED1 + "=0");
assignS1.Add(LinkedUser.COL_DELETED + "=1");
conditionS1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
s1 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS1, conditionS1);
// statement 2
ArrayList assignS2 = new ArrayList();
ArrayList conditionS2 = new ArrayList();
string s2;
assignS2.Add(LinkedUser.COL_ADDED2 + "=0");
assignS2.Add(LinkedUser.COL_DELETED + "=1");
conditionS2.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
s2 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS2, conditionS2);
// statement 3
string s3 = QueryGenerator.KW_DONOTHING;
string statement;
statement = QueryGenerator.GenerateSqlIfElseIfElse(conditions1, conditions2, s1, s2, s3);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch
{
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.BadRequest, false));
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, true));
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult SetLinkedUserMute([FromBody] object data)
{
try
{
var headers = Request.Headers;
string idMe = headers.GetValues(Models.User.COL_ID).First();
string idTarget = headers.GetValues(LinkedUser.PARAM_TARGET).First();
string mute = headers.GetValues(LinkedUser.PARAM_MUTE).First();
if (mute.ToLower() == "true")
{
mute = "1";
}
else if (mute.ToLower() == "false")
{
mute = "0";
}
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// condition 1
string c1;
ArrayList columnsC1 = new ArrayList();
ArrayList conditionsC1 = new ArrayList();
columnsC1.Add(LinkedUser.COL_USERID1);
conditionsC1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
conditionsC1.Add(QueryGenerator.KW_OR);
conditionsC1.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
c1 = QueryGenerator.GenerateSqlSelect(columnsC1, LinkedUser.TABLE, conditionsC1
, null, QueryGenerator.KW_ASC, 1);
c1 = QueryGenerator.ParenthesisString(c1) + "=" + idMe;
// conditions
ArrayList conditions = new ArrayList();
conditions.Add(c1);
// statement 1
ArrayList assignS1 = new ArrayList();
ArrayList conditionS1 = new ArrayList();
string s1;
assignS1.Add(LinkedUser.COL_MUTE1 + "=" + mute);
conditionS1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
s1 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS1, conditionS1);
// statement 2
ArrayList assignS2 = new ArrayList();
ArrayList conditionS2 = new ArrayList();
string s2;
assignS2.Add(LinkedUser.COL_MUTE2 + "=" + mute);
conditionS2.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
s2 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS2, conditionS2);
string statement;
statement = QueryGenerator.GenerateSqlIfElse(conditions, s1, s2);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult GetLinkedUsersById([FromBody] object data)
{
ArrayList linkedUsers = new ArrayList();
LinkedUser lu;
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// query linked user list
ArrayList columsS1 = new ArrayList();
ArrayList conditionsS1 = new ArrayList();
string statement1 = string.Empty;
columsS1.Add(LinkedUser.COL_ID);
columsS1.Add(LinkedUser.COL_USERID1);
columsS1.Add(LinkedUser.COL_USERID2);
columsS1.Add(LinkedUser.COL_ALERT1);
columsS1.Add(LinkedUser.COL_ALERT2);
columsS1.Add(LinkedUser.COL_MUTE1);
columsS1.Add(LinkedUser.COL_MUTE2);
columsS1.Add(LinkedUser.COL_DELETED);
columsS1.Add(LinkedUser.COL_ADDED1);
columsS1.Add(LinkedUser.COL_ADDED2);
conditionsS1.Add(LinkedUser.COL_USERID1 + "=" + id);
conditionsS1.Add(QueryGenerator.KW_OR);
conditionsS1.Add(LinkedUser.COL_USERID2 + "=" + id);
statement1 = QueryGenerator.GenerateSqlSelect(columsS1, LinkedUser.TABLE, conditionsS1);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement1;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
lu = new LinkedUser();
int i = 0;
lu.ID = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
if (lu.UserIDMe == int.Parse(id))
{
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
}
else
{
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
}
linkedUsers.Add(lu);
}
dr.Close();
}
linkedUsers.Sort();
// query linked user name and status
ArrayList columsS2 = new ArrayList();
ArrayList conditionsS2 = new ArrayList();
ArrayList ordersS2 = new ArrayList();
string statement2 = string.Empty;
columsS2.Add(Models.User.COL_ID);
columsS2.Add(Models.User.COL_DELETED);
columsS2.Add(Models.User.COL_USERNAME);
columsS2.Add(Models.User.COL_STATUS);
for (int i = 0; i < linkedUsers.Count; ++i)
{
if (i > 0)
{
conditionsS2.Add(QueryGenerator.KW_OR);
}
conditionsS2.Add(Models.User.COL_ID + "=" + ((LinkedUser)linkedUsers[i]).UserIDTarget);
}
ordersS2.Add(Models.User.COL_ID);
statement2 = QueryGenerator.GenerateSqlSelect(columsS2, Models.User.TABLE, conditionsS2, ordersS2, QueryGenerator.KW_ASC);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement2;
using (SqlDataReader dr = cmd.ExecuteReader())
{
int i = 0;
if (linkedUsers.Count > 0)
{
while (dr.Read())
{
if (((LinkedUser)linkedUsers[i]).UserIDTarget == dr.GetInt32(0) &&
!dr.GetBoolean(1))
{
((LinkedUser)linkedUsers[i]).NameTarget = dr.GetString(2);
((LinkedUser)linkedUsers[i]).StatusTarget = dr.GetInt32(3);
}
else
{
((LinkedUser)linkedUsers[i]).NameTarget = string.Empty;
((LinkedUser)linkedUsers[i]).StatusTarget = 5;
}
i++;
}
}
dr.Close();
}
for (int i = linkedUsers.Count - 1; i >= 0; --i)
{
if (((LinkedUser)linkedUsers[i]).NameTarget.Length == 0)
{
linkedUsers.RemoveAt(i);
}
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
return(Ok(new { linkedUsers }));
}
public IHttpActionResult Alert([FromBody] object data)
{
LinkedUser lu;
ArrayList linkedUsers = new ArrayList();
ArrayList emailsToAlert = new ArrayList();
string userName = string.Empty;
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
string lat = headers.GetValues(Location.COL_LAT).First();
string lng = headers.GetValues(Location.COL_LNG).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// Set flag for user table ---------------------
ArrayList assignmentsS1 = new ArrayList();
ArrayList conditionsS1 = new ArrayList();
string statement1;
assignmentsS1.Add(Models.User.COL_STATUS + "=6");
conditionsS1.Add(Models.User.COL_ID + "=" + id);
statement1 = QueryGenerator.GenerateSqlUpdate(Models.User.TABLE, assignmentsS1, conditionsS1);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement1.ToString();
cmd.ExecuteNonQuery();
// Set location for location table -------------
ArrayList valuesS2 = new ArrayList();
ArrayList assignmentsS2 = new ArrayList();
string statement2 = string.Empty;
valuesS2.Add(id);
valuesS2.Add(lat);
valuesS2.Add(lng);
valuesS2.Add(QueryGenerator.QuoteString(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")));
statement2 = QueryGenerator.GenerateSqlInsert(valuesS2, Location.TABLE);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement2;
cmd.ExecuteNonQuery();
// Get linked users ----------------------------
ArrayList columsS3 = new ArrayList();
ArrayList conditionsS3 = new ArrayList();
string statement3 = string.Empty;
columsS3.Add(LinkedUser.COL_ID);
columsS3.Add(LinkedUser.COL_USERID1);
columsS3.Add(LinkedUser.COL_USERID2);
columsS3.Add(LinkedUser.COL_ALERT1);
columsS3.Add(LinkedUser.COL_ALERT2);
columsS3.Add(LinkedUser.COL_MUTE1);
columsS3.Add(LinkedUser.COL_MUTE2);
columsS3.Add(LinkedUser.COL_DELETED);
columsS3.Add(LinkedUser.COL_ADDED1);
columsS3.Add(LinkedUser.COL_ADDED2);
conditionsS3.Add(LinkedUser.COL_USERID1 + "=" + id);
conditionsS3.Add(QueryGenerator.KW_OR);
conditionsS3.Add(LinkedUser.COL_USERID2 + "=" + id);
statement3 = QueryGenerator.GenerateSqlSelect(columsS3, LinkedUser.TABLE, conditionsS3);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement3;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
lu = new LinkedUser();
int i = 0;
lu.ID = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
if (lu.UserIDMe == int.Parse(id))
{
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
}
else
{
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
}
linkedUsers.Add(lu);
}
dr.Close();
}
linkedUsers.Sort();
// Filter linked users -------------------------
for (int i = linkedUsers.Count - 1; i >= 0; --i)
{
LinkedUser linkedUser = (LinkedUser)linkedUsers[i];
if (!(linkedUser.AddedMe && linkedUser.AddedTarget) ||
!linkedUser.AlertMe ||
linkedUser.MuteTarget ||
linkedUser.Deleted)
{
linkedUsers.RemoveAt(i);
}
}
// Get linked user emails ----------------------
ArrayList columsS4 = new ArrayList();
ArrayList conditionsS4 = new ArrayList();
string statement4 = string.Empty;
columsS4.Add(Models.User.COL_DELETED);
columsS4.Add(Models.User.COL_EMAIL);
for (int i = 0; i < linkedUsers.Count; ++i)
{
if (i > 0)
{
conditionsS4.Add(QueryGenerator.KW_OR);
}
conditionsS4.Add(Models.User.COL_ID + "=" + ((LinkedUser)linkedUsers[i]).UserIDTarget);
}
statement4 = QueryGenerator.GenerateSqlSelect(columsS4, Models.User.TABLE, conditionsS4);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement4;
// Set linked user emails ----------------------
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (!dr.GetBoolean(0))
{
if (dr.GetValue(1) != DBNull.Value)
{
emailsToAlert.Add(dr.GetString(1));
}
}
}
dr.Close();
}
// Get this user's name ------------------------
ArrayList columsS5 = new ArrayList();
ArrayList conditionsS5 = new ArrayList();
string statement5 = string.Empty;
columsS5.Add(Models.User.COL_USERNAME);
conditionsS5.Add(Models.User.COL_ID + "=" + id);
statement5 = QueryGenerator.GenerateSqlSelect(columsS5, Models.User.TABLE, conditionsS5);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement5;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
userName = dr.GetString(0);
}
dr.Close();
}
}
con.Close();
}
// Send notification
foreach (string email in emailsToAlert)
{
string message = "[" + userName + "] Help!";
SendNotification(message, email);
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
if (emailsToAlert.Count == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No linked user is found.", "")));
}
//return ResponseMessage(JsonContent.ReturnMessage("Linked users are alerted.", ""));
return(Ok(emailsToAlert));
}
public IHttpActionResult GetUserById([FromBody] object data)
{
User user = new User();
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
string statement = string.Empty;
colums.Add(Models.User.COL_ID);
colums.Add(Models.User.COL_USERNAME);
colums.Add(Models.User.COL_EMAIL);
colums.Add(Models.User.COL_PHONE);
colums.Add(Models.User.COL_LOGIN);
colums.Add(Models.User.COL_LASTLOGIN);
colums.Add(Models.User.COL_STATUS);
conditions.Add(Models.User.COL_ID + "=" + id);
conditions.Add(QueryGenerator.KW_AND);
conditions.Add(Models.User.COL_DELETED + "=0");
statement = QueryGenerator.GenerateSqlSelect(colums, Models.User.TABLE, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
user.ID = dr.GetInt32(0);
user.UserName = dr.GetString(1);
user.Email = dr.GetString(2);
user.Phone = dr.GetInt64(3);
user.Login = dr.GetBoolean(4);
if (dr.GetValue(5) != DBNull.Value)
{
user.LastLogin = dr.GetDateTime(5);
}
user.Status = dr.GetInt32(6);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
if (user.ID == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No user is found.", "")));
}
return(Ok(new { user }));
}
public IHttpActionResult LoginByEmail([FromBody] object data)
{
User user = new User();
try
{
var headers = Request.Headers;
string[] authToken = headers.GetValues("Authorization").First().Split(' ');
string decodedToken = Encoding.UTF8.GetString(Convert.FromBase64String(authToken[1]));
string email = decodedToken.Substring(0, decodedToken.IndexOf(":"));
string password = decodedToken.Substring(decodedToken.IndexOf(":") + 1);
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList assignments = new ArrayList();
ArrayList conditions = new ArrayList();
string statement;
assignments.Add(Models.User.COL_LASTLOGIN
+ "=" + QueryGenerator.QuoteString(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")));
assignments.Add(Models.User.COL_LOGIN + "=1");
conditions.Add(Models.User.COL_EMAIL + "=" + QueryGenerator.QuoteString(email));
conditions.Add(QueryGenerator.KW_AND);
conditions.Add(Models.User.COL_PASSWORD + "=" + QueryGenerator.QuoteString(password));
conditions.Add(QueryGenerator.KW_AND);
conditions.Add(Models.User.COL_DELETED + "=0");
statement = QueryGenerator.GenerateSqlUpdate(Models.User.TABLE, assignments, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
ArrayList columsS2 = new ArrayList();
ArrayList conditionsS2 = new ArrayList();
string statement2 = string.Empty;
columsS2.Add(Models.User.COL_ID);
columsS2.Add(Models.User.COL_USERNAME);
columsS2.Add(Models.User.COL_EMAIL);
columsS2.Add(Models.User.COL_PHONE);
columsS2.Add(Models.User.COL_LOGIN);
columsS2.Add(Models.User.COL_LASTLOGIN);
columsS2.Add(Models.User.COL_STATUS);
conditionsS2.Add(Models.User.COL_EMAIL + "=" + QueryGenerator.QuoteString(email));
conditionsS2.Add(QueryGenerator.KW_AND);
conditionsS2.Add(Models.User.COL_PASSWORD + "=" + QueryGenerator.QuoteString(password));
conditionsS2.Add(QueryGenerator.KW_AND);
conditionsS2.Add(Models.User.COL_DELETED + "=0");
statement2 = QueryGenerator.GenerateSqlSelect(columsS2, Models.User.TABLE, conditionsS2);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement2;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
user.ID = dr.GetInt32(0);
user.UserName = dr.GetString(1);
user.Email = dr.GetString(2);
user.Phone = dr.GetInt64(3);
user.Login = dr.GetBoolean(4);
if (dr.GetValue(5) != DBNull.Value)
{
user.LastLogin = dr.GetDateTime(5);
}
user.Status = dr.GetInt32(6);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
if (user.ID == 0)
{
new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
HttpContext.Current.User = new GenericPrincipal(new ApiIdentity(user), new string[] { });
return(Ok(new { user }));
}
public IHttpActionResult GetEmergencyBldgAll()
{
ArrayList buildings = new ArrayList();
EmergencyBldg bldg;
try
{
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
string statement = string.Empty;
colums.Add(EmergencyBldg.COL_ID);
colums.Add(EmergencyBldg.COL_CATEGORY);
colums.Add(EmergencyBldg.COL_BLDGID);
colums.Add(EmergencyBldg.COL_BLDGNAME);
colums.Add(EmergencyBldg.COL_STRNUM);
colums.Add(EmergencyBldg.COL_STRNAME);
colums.Add(EmergencyBldg.COL_MAPREF);
colums.Add(EmergencyBldg.COL_LAT);
colums.Add(EmergencyBldg.COL_LNG);
colums.Add(EmergencyBldg.COL_PHONE);
colums.Add(EmergencyBldg.COL_LOCNAME);
statement = QueryGenerator.GenerateSqlSelect(colums,
EmergencyBldg.TABLE, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
bldg = new EmergencyBldg();
int i = 0;
bldg.ID = dr.GetInt32(i);
i++;
bldg.Category = dr.GetInt32(i);
i++;
bldg.BldgID = dr.GetInt32(i);
i++;
bldg.BldgName = dr.GetString(i);
i++;
bldg.StrNum = dr.GetInt32(i);
i++;
bldg.StrName = dr.GetString(i);
i++;
bldg.MapRef = dr.GetInt32(i);
i++;
bldg.Lat = dr.GetDouble(i);
i++;
bldg.Lng = dr.GetDouble(i);
i++;
if (dr.GetValue(i) != DBNull.Value)
{
bldg.Phone = dr.GetInt64(i);
}
i++;
if (dr.GetValue(i) != DBNull.Value)
{
bldg.LocName = dr.GetString(i);
}
buildings.Add(bldg);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
if (buildings.Count == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No building is found.", "")));
}
return(Ok(new { buildings }));
}
public override void OnActionExecuting(System.Web.Http.Controllers.HttpActionContext actionContext)
{
if (actionContext.Request.Headers.Authorization == null)
{
actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
}
else
{
string authToken = actionContext.Request.Headers.Authorization.Parameter;
string decodedToken = Encoding.UTF8.GetString(Convert.FromBase64String(authToken));
string email = decodedToken.Substring(0, decodedToken.IndexOf(":"));
string password = decodedToken.Substring(decodedToken.IndexOf(":") + 1);
User user = new User();
try
{
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
string statement = string.Empty;
colums.Add(User.COL_EMAIL);
colums.Add(User.COL_PASSWORD);
colums.Add(User.COL_DELETED);
colums.Add(User.COL_LOGIN);
conditions.Add(User.COL_EMAIL + " = " + QueryGenerator.QuoteString(email));
statement = QueryGenerator.GenerateSqlSelect(colums, User.TABLE, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
user.Email = dr.GetString(0);
user.Password = dr.GetString(1);
user.Deleted = dr.GetBoolean(2);
user.Login = dr.GetBoolean(3);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.InternalServerError);
}
if (email.Equals(user.Email) &&
password.Equals(user.Password) &&
!user.Deleted &&
user.Login)
{
HttpContext.Current.User = new GenericPrincipal(new ApiIdentity(user), new string[] { });
base.OnActionExecuting(actionContext);
}
else
{
actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
}
}
}