public List <EventTable> GetTodayUpdates(string currentlocation)
{
List <EventTable> results = new List <EventTable>();
String query = "select * from [Event],[Location] where (Event.ispresent=1 and Event.LocationID = (select Location.LocationID from [Location] where (Location.LocationName='" + currentlocation + "')))and Location.LocationID=Event.LocationID; ";
using (SqlConnection sql = new SqlConnection())
{
sql.ConnectionString = RTSMSdbConnectionString;
SqlCommand cmd = new SqlCommand(query, sql);
try
{
sql.Open();
}
catch (Exception ex)
{
DateTime tm = DateTime.Now;
results.Add(new EventTable(1, "123", true, false, tm, tm, "Killed !.Quadidabd,Karachi", "3 people killed in Quaidabad.", 67.2162619, 24.8601083));
var connectionFault = new ConnectionFault();
connectionFault.Operation = "UpdateAccount";
connectionFault.Reason =
"Can't connect to the database";
connectionFault.Details = ex.Message;
throw new FaultException <ConnectionFault>(
connectionFault);
}
try
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
DateTime tm = DateTime.Now;
while (reader.Read())
{
// bool status = reader.GetBoolean(8);
//string alert = reader.GetString(8);
results.Add(new EventTable(reader.GetInt32(0), reader.GetString(1), true, false, tm, tm, reader.GetString(4), reader.GetString(5), reader.GetDouble(18), reader.GetDouble(19)));
}
}
else
{
DateTime tm = DateTime.Now;
results.Add(new EventTable(1, " ", true, false, tm, tm, "No Result", "No Result", 0.0, 0.0));
}
}
catch (Exception ex)
{
DateTime tm = DateTime.Now;
results.Add(new EventTable(1, "123", true, false, tm, tm, "Killed !.Quadidabd,Karachi", "3 people killed in Quaidabad.", 67.2162619, 24.8601083));
var dataFault = new DataFault();
dataFault.Operation = "AddOrder";
dataFault.Reason = "ErrError writing to the database";
dataFault.Details = ex.Message;
throw new FaultException <DataFault>(dataFault,
new FaultReason("Error writing to the database"));
}
}
return(results);
}
public bool ValidateUser(User usr)
{
string query;
bool val = false;
query = @"SELECT COUNT(*) FROM [User] AS u where u.UserName = '******' AND u.hashPassword = '******';";
string connectionString = RTSMSdbConnectionString;
using (SqlConnection cnn = new SqlConnection())
{
cnn.ConnectionString = connectionString;
SqlCommand cmd = new SqlCommand(query, cnn);
try
{
cnn.Open();
}
catch (Exception ex)
{
var connectionFault = new ConnectionFault();
connectionFault.Operation = "ListCustomers";
connectionFault.Reason =
"Can't connect to the database";
connectionFault.Details = ex.Message;
throw new FaultException <ConnectionFault>(
connectionFault);
}
try
{
SqlDataReader readerS = cmd.ExecuteReader();
int check = 5;
while (readerS.Read())
{
check = Int32.Parse(readerS[0].ToString());
}
if (check == 0)
{
val = false;
}
else
{
val = true;
}
}
catch (Exception ex)
{
var dataFault = new DataFault();
dataFault.Operation = "AddOrder";
dataFault.Reason = "ErrError writing to the database";
dataFault.Details = ex.Message;
throw new FaultException <DataFault>(dataFault,
new FaultReason("Error writing to the database"));
}
}
return(true);
// return val;
// return connectionString;
}
public bool CreateAccount(string firstname, string lastname, string username, string email, string password, string homelattitude, string homelongitude, string officelattitude, string officelongitude)
{
bool check;
string query;
query = @"insert into [User] values (" + 1 + "," + 1 + "," + 1 + ",'" + firstname + "','" + lastname + "','" + username + "','" + password + "','" + DateTime.Now.ToString() + "')";
string connectionString = RTSMSdbConnectionString;
using (SqlConnection cnn = new SqlConnection())
{
cnn.ConnectionString = connectionString;
SqlCommand cmd = new SqlCommand(query, cnn);
try
{
cnn.Open();
}
catch (Exception ex)
{
var connectionFault = new ConnectionFault();
connectionFault.Operation = "ListCustomers";
connectionFault.Reason =
"Can't connect to the database";
connectionFault.Details = ex.Message;
throw new FaultException <ConnectionFault>(
connectionFault);
}
try
{
int i = cmd.ExecuteNonQuery();
if (i < 0)
{
check = false;
return(false);
}
else
{
check = true;
}
}
catch (Exception ex)
{
check = false;
var dataFault = new DataFault();
dataFault.Operation = "AddOrder";
dataFault.Reason = "ErrError writing to the database";
dataFault.Details = ex.Message;
return(false);
throw new FaultException <DataFault>(dataFault,
new FaultReason("Error writing to the database"));
}
}
return(check);
}
public User GetUserProfile(string username)
{
string query;
User usr = new User();
query = @"select * from [User] AS u where u.UserName='******'";
string connectionString = RTSMSdbConnectionString;
using (SqlConnection cnn = new SqlConnection())
{
cnn.ConnectionString = connectionString;
SqlCommand cmd = new SqlCommand(query, cnn);
try
{
cnn.Open();
}
catch (Exception ex)
{
var connectionFault = new ConnectionFault();
connectionFault.Operation = "ListCustomers";
connectionFault.Reason =
"Can't connect to the database";
connectionFault.Details = ex.Message;
throw new FaultException <ConnectionFault>(
connectionFault);
}
try
{
SqlDataReader readerS = cmd.ExecuteReader();
int check = 5;
while (readerS.Read())
{
usr.HomeLocation = readerS[1].ToString();
usr.OfficeLocation = readerS[2].ToString();
usr.FirstName = readerS[4].ToString();
usr.LastName = readerS[5].ToString();
usr.UserName = readerS[6].ToString();
usr.Password = readerS[7].ToString();
}
}
catch (Exception ex)
{
var dataFault = new DataFault();
dataFault.Operation = "AddOrder";
dataFault.Reason = "ErrError writing to the database";
dataFault.Details = ex.Message;
throw new FaultException <DataFault>(dataFault,
new FaultReason("Error writing to the database"));
}
}
return(usr);
}
public string UpdateAccount(User usr)
{
string query;
query = @"update [User] set [HomeLocationID]=" + usr.HomeLocation + ", [OfficeLocationID]=" + usr.OfficeLocation + ", [FirstName]='" + usr.FirstName + "', [LastName]='" + usr.LastName + "' where [UserName]='" + usr.UserName + "';";
string connectionString = RTSMSdbConnectionString;
using (SqlConnection cnn = new SqlConnection())
{
cnn.ConnectionString = connectionString;
SqlCommand cmd = new SqlCommand(query, cnn);
try
{
cnn.Open();
}
catch (Exception ex)
{
var connectionFault = new ConnectionFault();
connectionFault.Operation = "UpdateAccount";
connectionFault.Reason =
"Can't connect to the database";
connectionFault.Details = ex.Message;
throw new FaultException <ConnectionFault>(
connectionFault);
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
var dataFault = new DataFault();
dataFault.Operation = "AddOrder";
dataFault.Reason = "ErrError writing to the database";
dataFault.Details = ex.Message;
throw new FaultException <DataFault>(dataFault,
new FaultReason("Error writing to the database"));
}
}
return(query);
}
/* SELECT [demoEventID] ,[RSSItemID] ,[CategoryID] ,[Title] ,[demoEvent].[Description],[Link],[RiskLevel],[Status],[Alert],[Message],[pubDate],[heldDate],[Location].LocationName FROM [demoEvent] inner join [Location] on ([demoEvent].LocationID = [Location] .LocationID and [demoEvent].[iscatastrophe] = 1 )
* GO*/
public List <EventTable> GetEvent(Double latt, Double lngt, Decimal radius, string screentype, string clienttype)
{
String query = " SELECT [demoEventID] ,[RSSItemID] ,[CategoryID] ,[Title] ,[demoEvent].[Description],[Link],[RiskLevel],[Status],[Alert],[Message],[pubDate],[heldDate],[Location].LocationName FROM [demoEvent] inner join [Location] on ([demoEvent].LocationID = [Location] .LocationID and [demoEvent].[iscatastrophe] = 1 ) ";
using (SqlConnection sql = new SqlConnection())
{
sql.ConnectionString = RTSMSdbConnectionString;
SqlCommand cmd = new SqlCommand(query, sql);
try
{
sql.Open();
}
catch (Exception ex)
{
DateTime tm = DateTime.Now;
var connectionFault = new ConnectionFault();
connectionFault.Operation = "UpdateAccount";
connectionFault.Reason =
"Can't connect to the database";
connectionFault.Details = ex.Message;
throw new FaultException <ConnectionFault>(
connectionFault);
}
try
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
DateTime tm = DateTime.Now;
while (reader.Read())
{
// bool status = reader.GetBoolean(8);
//string alert = reader.GetString(8);
results.Add(new EventTable(reader.GetInt32(0), reader.GetString(1), true, false, tm.ToString(), tm.ToString(), reader.GetString(4), reader.GetString(5), reader.GetDouble(18), reader.GetDouble(19), "45"));
results.Add(new EventTable("1adcdsf", "http://192.168.1.101/rtsms_pic/logo3.jpg", Double.Parse(article.Longitude.ToString()), Double.Parse(article.Latitude.ToString()), article.Title, "Kill-Firing", screentype, article.Distance.ToString(), "AryNews", dat.ToString(), article.Summary + tmp, "http://www.geo.tv/archive/12334.htm", false, i, dat.ToString()));
}
}
else
{
// DateTime tm = DateTime.Now;
// results.Add(new EventTable("1adcdsf", "http://192.168.1.101/rtsms_pic/logo3.jpg", Double.Parse(article.Longitude.ToString()), Double.Parse(article.Latitude.ToString()), article.Title, "Kill-Firing", screentype, article.Distance.ToString(), "AryNews", dat.ToString(), article.Summary + tmp, "http://www.geo.tv/archive/12334.htm", false, i, dat.ToString()));
}
}
catch (Exception ex)
{
DateTime tm = DateTime.Now;
// results.Add(new EventTable("1adcdsf", "http://192.168.1.101/rtsms_pic/logo3.jpg", Double.Parse(article.Longitude.ToString()), Double.Parse(article.Latitude.ToString()), article.Title, "Kill-Firing", screentype, article.Distance.ToString(), "AryNews", dat.ToString(), article.Summary + tmp, "http://www.geo.tv/archive/12334.htm", false, i, dat.ToString()));
var dataFault = new DataFault();
dataFault.Operation = "AddOrder";
dataFault.Reason = "ErrError writing to the database";
dataFault.Details = ex.Message;
throw new FaultException <DataFault>(dataFault,
new FaultReason("Error writing to the database"));
}
}
return(results);
/*
* //EventTable(string rssitemid,string iconurl,Double longi,Double latti, string locname,string evntname,string distance,string reportedby,string releasedate,string description,string weburl,Boolean isalert,Int32 alertlevel, string helddate)
* List<EventTable> results = new List<EventTable>();
* String query;
* try
* {
* FindNearBy fn = new FindNearBy(latt, lngt);
* List<WikipediaArticle> wiki;
* if (radius > 0)
* {
* wiki = fn.GetNearByLocations(radius);
* }
* else
* {
* wiki = fn.GetNearByLocations(10);
* }
* int i = 0;
*
* Double latt1 = 24.8601083;
* Double lngi1 = 67.2162619;
* Double latt2 = 24.8933790;
* Double lngi2 = 67.0280610;
*
* double e=(3.1415926538*latt1/180);
* double f=(3.1415926538*lngi1/180);
* double g=(3.1415926538*latt2/180);
* double h=(3.1415926538*lngi2/180);
* double P=(Math.Cos(e)*Math.Cos(g)*Math.Cos(f)*Math.Cos(h)+Math.Cos(e)*Math.Sin(f)*Math.Cos(g)*Math.Sin(h)+Math.Sin(e)*Math.Sin(g));
* double j=(Math.Acos(P));
* double k=(6371*j);
*
* String tmp = "sdfjlsjflsjfl sjfl sjf lsjf lsjf lsjflsjf klsjf lsjf lsjf lsjf dsl fjsl fjdsl fjsld fjiwrjovcxlvlvjsl fjls djfsld fjs fljdsfl sj flsj<b>testsfsdf sfjsldf</b> cvlxvljflsjdoie rfdljf sdlf";
*
* foreach (WikipediaArticle article in wiki)
* {
* // DateTime tm;
* // long dat = ((DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000);
* String dat = DateTime.Now.AddSeconds(i++).ToString();
* results.Add(new EventTable("1adcdsf", "http://192.168.1.101/rtsms_pic/logo3.jpg", Double.Parse(article.Longitude.ToString()), Double.Parse(article.Latitude.ToString()), article.Title, "Kill-Firing", screentype, article.Distance.ToString(), "AryNews", dat.ToString(), article.Summary+tmp, "http://www.geo.tv/archive/12334.htm", false, i, dat.ToString()));
*
* }
* }
* catch (Exception ex)
* {
* var dataFault = new DataFault();
* dataFault.Operation = "AddOrder";
* dataFault.Reason = "ErrError writing to the database";
* dataFault.Details = ex.Message;
* throw new FaultException<DataFault>(dataFault,
* new FaultReason("Error writing to the database"));
* }
* return results;
*/
}
