protected void GetAlerts(string date)
{
try
{
string unitID = Request.QueryString["unitID"].ToString();
string _strSQL = "SELECT alertType,alertMessage,alertTime FROM tblAlert WHERE comID = " +
Session["trkCompany"].ToString() + " AND unitID = " + unitID +
" and alertTime::date = '" + date + "' ORDER BY alertTime DESC;";
ExecuteSQL _executeSQL = new ExecuteSQL();
DataSet _ds = _executeSQL.getDataSet(_strSQL);
Session["_ds"] = _ds;
_grdAlert.DataSource = _ds;
_grdAlert.DataBind();
if (_grdAlert.Rows.Count < 1)
{
DateTime dateTime = new DateTime();
dateTime = DateTime.Parse(date);
_lblMessage.Text = "No alerts occured for this unit on " +
dateTime.ToString(dateFormat) + ".";
_grdAlert.Visible = false;
}
else
{
_lblMessage.Text = "";
_grdAlert.Visible = true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}
public void btnStatus_Click(object sender, EventArgs e)
{
status_div.Visible = true;
Timer.Enabled = false;
try
{
AlertData alert = getLastAlert();
string deviceID = Session["deviceID"].ToString();
string strSQL = "SELECT lat,long,recTimeRevised AS recDateTime,velocity,deviceID,unitName FROM unitRecords WHERE recTimeRevised = '" +
alert.AlertTime + "' AND deviceID = " + deviceID;
ExecuteSQL exec = new ExecuteSQL();
DataSet ds = new DataSet();
ds = exec.getDataSet(strSQL);
unitInfo.Text = "Type: " + alert.AlertType + "<br/>";
unitInfo.Text += "Message: " + alert.AlertMessage + "<br/><br/>";
unitInfo.Text += MapDataCommon.GetCoreDataSetMarkerText(ds, 0);
}
catch (Exception ex)
{
Console.WriteLine("btnStatus_Click(): " + ex.Message);
}
}
private bool isCorrect(string answer)
{
try
{
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT securityAnswer FROM tblUser WHERE" +
" uID = " + Session["userID"].ToString());
if (_ds.Tables[0].Rows.Count > 0)
{
if (answer == _ds.Tables[0].Rows[0]["securityAnswer"].ToString())
{
return true;
}
}
}
catch (Exception ex)
{
Console.WriteLine("PasswordRetrive::isCorrect(): " + ex.Message.ToString());
}
return false;
}
private void loadUnit()
{
if (Request.QueryString["unitID"] != null)
{
try
{
string strSQL = "SELECT unitName,deviceID FROM tblUnits WHERE unitID = '" + Request.QueryString["unitID"] + "' AND comID = " + Session["trkCompany"].ToString() + ";";
ExecuteSQL exec = new ExecuteSQL();
DataSet ds = new DataSet();
ds = exec.getDataSet(strSQL);
if (ds.Tables[0].Rows.Count > 0)
{
Session["deviceID"] = ds.Tables[0].Rows[0]["deviceID"].ToString();
unitName.Text = "Selected Unit: " + ds.Tables[0].Rows[0]["unitName"].ToString();
}
}
catch (Exception ex)
{
throw new Exception("loadUnit(): " + ex.Message.ToString());
Console.WriteLine("loadUnit(): " + ex.Message.ToString());
}
}
}
private string getUnitName()
{
try
{
string strSQL = "SELECT unitName FROM tblUnits WHERE deviceID = " + Session["deviceID"] + " AND comID = " + Session["trkCompany"].ToString() + ";";
ExecuteSQL exec = new ExecuteSQL();
DataSet ds = new DataSet();
ds = exec.getDataSet(strSQL);
if (ds.Tables[0].Rows.Count > 0)
{
return ds.Tables[0].Rows[0]["unitName"].ToString();
}
}
catch (Exception ex)
{
throw new Exception("getUnitName(): " + ex.Message.ToString());
Console.WriteLine("getUnitName(): " + ex.Message.ToString());
}
return "";
}
private int createUserGroup(string name)
{
UserGroup userGroup = new UserGroup();
ProcessUserGroup pGroup = new ProcessUserGroup(AlarmasABC.BLL.InvokeOperations.operations.INSERT);
try
{
userGroup.GroupName = name;
userGroup.ComID = comID;
pGroup.UserGroup = userGroup;
pGroup.invoke();
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT groupID FROM tblGroup WHERE comID = "
+ comID + " AND groupName = '" + name + "';");
int groupID = int.Parse(_ds.Tables[0].Rows[0][0].ToString());
if (groupID < 1)
{
return -1;
}
return groupID;
}
catch (Exception ex)
{
Console.WriteLine("CreateNewAccount::createUserGroup():" + ex.Message.ToString());
}
finally
{
userGroup = null;
pGroup = null;
}
return -1;
}
private bool isDemoUser()
{
string _userID = Session["uID"].ToString();
string _comID = Session["trkCompany"].ToString();
string _strSQL = "SELECT login FROM tblUser WHERE uID = " + _userID + " AND comID = " + _comID;
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet(_strSQL);
if (_ds.Tables[0].Rows.Count > 0)
{
Console.WriteLine(_ds.Tables[0].Rows[0]["login"]);
if (_ds.Tables[0].Rows[0]["login"].ToString() == "demo")
{
return true;
}
}
return false;
}
private bool SpeedRule()
{
try
{
DataSet _ds = new DataSet();
string _strSQL = "select RulesValue from tblrules r inner join tblunitwiserules ur on r.RulesID=ur.RulesID where UnitID=" + this.UnitID.ToString() + " and comid=" + this.ComID.ToString() + "; ";
_strSQL += "select top 1 velocity from tblGprs where deviceID=(select deviceID from tblunits where unitid=" + this.UnitID.ToString() + " and comid=" + this.ComID.ToString() + ") order by rectime desc ";
ExecuteSQL _execute = new ExecuteSQL();
_ds = _execute.getDataSet(_strSQL);
if (_ds != null)
{
if (_ds.Tables[0].Rows.Count > 0)
{
if (int.Parse(_ds.Tables[0].Rows[0]["RulesValue"].ToString()) < int.Parse(_ds.Tables[1].Rows[0]["velocity"].ToString()))
{
return true;
}
}
}
}
catch (Exception ex)
{
throw new Exception("GetColor::SpeedRule::" + ex.Message);
}
return false;
}
private void LoadUnits(string _comID)
{
string _strSQL = "SELECT unitID AS id,unitName,t.typeName," +
" CAST((CASE u.isActive WHEN '1' THEN '1' WHEN '0' THEN '0' ELSE '0' END) AS BOOLEAN) AS status" +
" FROM tblUnits u INNER JOIN tblUnitType t ON t.typeID = u.typeID" +
" WHERE coalesce(u.isDelete,'0') != '1' AND u.comID = " + _comID + ";";
ExecuteSQL _exc = new ExecuteSQL();
_grdUnit.DataSource = _exc.getDataSet(_strSQL);
//_grdUnit.DataBind();
}
private void loadSecurityScheme()
{
try
{
DataSet _ds = new DataSet();
int _comID = int.Parse(Session["trkCompany"].ToString());
ProcessScheme.fillDropDownItems(_ddlSecurityScheme,_comID);
string _strSQL = "SELECT id FROM tblSecurityScheme WHERE comID = "
+ _comID + " AND defaultScheme = '1';";
ExecuteSQL _sql = new ExecuteSQL();
_ds = _sql.getDataSet(_strSQL);
if (_ds.Tables[0].Rows.Count > 0)
_ddlSecurityScheme.SelectedValue = _ds.Tables[0].Rows[0]["ID"].ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
private bool userIsValid()
{
try
{
string strSQL = "SELECT * FROM tblUser WHERE login = '******' AND email = '" + txtEmail.Text.Trim() + "';";
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet(strSQL);
if (_ds.Tables[0].Rows.Count > 0)
{
Session["userID"] = _ds.Tables[0].Rows[0]["uID"].ToString();
Session["userName"] = _ds.Tables[0].Rows[0]["login"].ToString();
Session["securityQID"] = _ds.Tables[0].Rows[0]["securityQuestion"].ToString();
Session["email"] = _ds.Tables[0].Rows[0]["email"].ToString();
}
else
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = "The supplied user name and email does not exist in our database.";
return false;
}
}
catch (Exception ex)
{
Console.WriteLine("PasswordRetrive::userIsValid(): " + ex.Message.ToString());
}
finally
{
}
return true;
}
private void SendEmail(string email, string department, string description)
{
string subject = "Support request from " + Session["UserName"].ToString() +
" (" + department + ")";
try
{
// Send contact email....
DataSet ds = new DataSet();
string strSQL = "SELECT email,adminEmail FROM tblContact WHERE department = '" +
department + "';";
ExecuteSQL exec = new ExecuteSQL();
ds = exec.getDataSet(strSQL);
if (ds.Tables[0].Rows.Count < 1)
{
throw new Exception("Couldn't get any data from query!");
}
if (ds.Tables[0].Rows[0]["email"].ToString().Length > 0)
{
Mailer.SendMailMessage(email, ds.Tables[0].Rows[0]["email"].ToString(),
ds.Tables[0].Rows[0]["adminEmail"].ToString(), "",
subject, description);
}
Mailer.SendMailMessage(email, ds.Tables[0].Rows[0]["adminEmail"].ToString(), "", "",
subject, description);
}
catch (Exception ex)
{
Console.WriteLine("SendMail(): " + ex.Message.ToString());
}
}
private int createUnitGroup(string name)
{
AlarmasABC.Core.Admin.UnitType unitType = new AlarmasABC.Core.Admin.UnitType();
ProcessUnitTypeNotQueries insert = new ProcessUnitTypeNotQueries(AlarmasABC.BLL.InvokeOperations.operations.INSERT);
try
{
unitType.TypeName = name;
unitType.ComID = comID;
insert.UnitType = unitType;
insert.invoke();
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT typeID FROM tblUnitType WHERE comID = "
+ comID + " AND typeName = '" + name + "';");
int typeID = int.Parse(_ds.Tables[0].Rows[0][0].ToString());
if (typeID < 1)
{
return -1;
}
return typeID;
}
catch (Exception ex)
{
throw new Exception(" CreateNewAccount::createUnitGroup(): " + ex.Message.ToString());
}
finally
{
unitType = null;
}
}
private bool userExists(string name, string email)
{
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT * FROM tblUser WHERE login = '******' AND" +
" email = '" + email + "';");
if (_ds.Tables[0].Rows.Count > 0)
{
return true;
}
return false;
}
private bool unitExists(string deviceID)
{
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT * FROM tblUnits WHERE deviceID = " + deviceID);
if (_ds.Tables[0].Rows.Count > 0)
{
return true;
}
return false;
}
private int getDefaultSecurityScheme()
{
ExecuteSQL exec = new ExecuteSQL();
DataSet _ds = new DataSet();
_ds = exec.getDataSet("SELECT id FROM tblSecurityScheme WHERE comID = " + comID +
" AND defaultScheme = '1'");
return int.Parse(_ds.Tables[0].Rows[0][0].ToString());
}
private void LoadSchemes()
{
try
{
string _comID = Session["trkCompany"].ToString();
string _strSQL = "SELECT * FROM tblSecurityScheme WHERE comID = " + _comID + "; ";
_strSQL += " SELECT * FROM tblSecurityScheme WHERE comID = " + _comID + " AND defaultScheme = 1 ;";
_strSQL += " SELECT s.ID,f.formName,s.schemeName,p.fullaccess,p.delete,p.view,p.insert,p.edit";
_strSQL += " FROM tblSchemePermission p INNER JOIN tblSecurityScheme s ON p.schemeID = s.ID";
_strSQL += " INNER JOIN tblForms f ON f.ID = p.formID ORDER BY s.ID;";
ExecuteSQL _ex = new ExecuteSQL();
DataSet _ds = new DataSet();
_ds = _ex.getDataSet(_strSQL);
_grdScheme.DataSource = _ds;
_grdScheme.DataBind();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void sendMessage()
{
DataSet _ds = new DataSet();
try
{
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT password FROM tblUser WHERE uID = " + Session["userID"].ToString());
if (_ds.Tables[0].Rows.Count > 0)
{
string password = EncDec.GetDecryptedText(_ds.Tables[0].Rows[0]["password"].ToString());
string subject = "Password Recovery";
string body = "This message was sent to you because you";
body += " reqested to recover a lost password. \r\n\r\n";
body += "Login Name: " + Session["userName"].ToString() + " \r\n";
body += "Password: "******"\r\n\r\n";
SendEmail(Session["email"].ToString(), subject, body);
lblMessage.Text = "Your password has been sent to your email address.";
lblMessage.ForeColor = System.Drawing.Color.Green;
txtAnswer.Text = "";
Session["userID"] = null;
Session["userName"] = null;
Session["securityQID"] = null;
Session["email"] = null;
}
else
{
lblMessage.Text = "There was an error trying to recover your password.";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}
catch (Exception ex)
{
Console.WriteLine("PasswordRetrive::sendMessage(): " + ex.Message.ToString());
}
finally
{
_ds = null;
}
}
private void LoadUser(string _comID)
{
string _strSQL = "SELECT uID AS id,login,userName,groupName,email," +
" CAST((CASE u.isActive WHEN '1' THEN '1' WHEN '0' THEN '0' ELSE '0' END) AS BOOLEAN) AS status" +
" FROM tblUser u INNER JOIN tblGroup gr ON u.groupID=gr.groupID" +
" WHERE coalesce(u.isDelete,'0') != '1' AND u.comID = " + _comID + ";";
ExecuteSQL _exc = new ExecuteSQL();
_grdUser.DataSource = _exc.getDataSet(_strSQL);
//_grdUser.DataBind();
}
private void loadSecurityQuestion()
{
try
{
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet("SELECT question FROM tblSecurityQuestion WHERE" +
" id = " + Session["securityQID"].ToString());
if (_ds.Tables[0].Rows.Count > 0)
{
lblQuestion.Text = _ds.Tables[0].Rows[0]["question"].ToString();
}
}
catch (Exception ex)
{
Console.WriteLine("PasswordRetrive::loadSecurityQuestion(): " + ex.Message.ToString());
}
}
private void LoadAlerts(string unitID)
{
try
{
string _strSQL = "SELECT alertType,alertMessage,alertTime FROM tblAlert WHERE comID=" +
Session["trkCompany"].ToString() + " AND unitID = " + unitID +
" ORDER BY alertTime DESC;";
ExecuteSQL _executeSQL = new ExecuteSQL();
DataSet _ds = _executeSQL.getDataSet(_strSQL);
Session["_ds"] = _ds;
_grdAlert.DataSource = _ds;
_grdAlert.DataBind();
}
catch (Exception ex)
{
}
}
public AlertData getLastAlert()
{
string strSQL = "SELECT * FROM tblAlert WHERE unitID = " +
"(SELECT DISTINCT unitID FROM tblUnits WHERE deviceID = " + Session["deviceID"].ToString() +
" AND comID = " + Session["trkCompany"].ToString() + ") ORDER BY id DESC LIMIT 1;";
ExecuteSQL exec = new ExecuteSQL();
DataSet ds = new DataSet();
AlertData alert = new AlertData();
try
{
ds = exec.getDataSet(strSQL);
if (ds.Tables[0].Rows.Count > 0)
{
alert.AlertTime = ds.Tables[0].Rows[0]["alertTime"].ToString();
alert.AlertMessage = ds.Tables[0].Rows[0]["alertMessage"].ToString();
alert.AlertType = ds.Tables[0].Rows[0]["alertType"].ToString();
}
}
catch (Exception ex)
{
throw new Exception ("getLastAlert(): " + ex.Message + "\n" + strSQL);
}
return alert;
}
private string getTreeColor(int unitID)
{
int comID = int.Parse(Session["trkCompany"].ToString());
string strSQL;
DataSet ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
strSQL = "select * from tblAlert where unitID = " + unitID.ToString() + " and comID = "
+ comID.ToString() + " and alertTime > 'now'::timestamp" +
" - interval '5 minutes' order by alertTime limit 1";
try
{
ds = exec.getDataSet(strSQL);
}
catch (Exception ex)
{
Console.WriteLine("getTreeColor(): " + ex.Message.ToString());
}
if (ds.Tables[0].Rows.Count < 1)
{
strSQL = "select deviceID from tblUnits where unitID = " + unitID.ToString() +
" and comID = " + comID.ToString();
ds = exec.getDataSet(strSQL);
int deviceID ;
if (ds.Tables[0].Rows.Count > 0) {
deviceID = int.Parse(ds.Tables[0].Rows[0]["deviceID"].ToString());
} else {
return "Green";
}
DataSet ds2 = new DataSet();
strSQL = "select * from tblGPRS where deviceID = " + deviceID +
" and recTimeRevised > 'now'::timestamp - interval '12 hours' limit 1";
ds2 = exec.getDataSet(strSQL);
if(ds2.Tables[0].Rows.Count < 1) {
strSQL = "select * from tblGPRS where deviceID = " + deviceID + " limit 5";
ds2 = exec.getDataSet(strSQL);
if (ds2.Tables[0].Rows.Count < 1) {
return "Black";
} else {
return "Gray";
}
}
return "Green";
}
else
{
string alertType = ds.Tables[0].Rows[0]["alertType"].ToString();
if (alertType == "Event")
{
return "Yellow";
}
else
{
return "Red";
}
}
}
private bool checkPassword()
{
string strSQL = "SELECT password FROM tblUser WHERE uID = " + Session["uID"].ToString();
ExecuteSQL exec = new ExecuteSQL();
DataSet ds = new DataSet();
ds = exec.getDataSet(strSQL);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["password"].ToString() == EncDec.GetEncryptedText(txtPassword.Text))
{
return true;
}
}
return false;
}
private void setUserRights()
{
string _userID = Session["uID"].ToString();
string _comID = Session["trkCompany"].ToString();
bool _hasPermission = false;
Telerik.Web.UI.RadMenuItem _menu = new RadMenuItem();
if (isDemoUser())
{
// Remote all from admin
foreach (RadMenuItem item in menuAdministration.Items)
{
item.Visible = false;
}
// Remove all from fleet
foreach (RadMenuItem item in menuFleet.Items)
{
item.Visible = false;
}
}
else
{
string _strSQL = "SELECT id,moduleName FROM tblModule";
DataSet _ds = new DataSet();
ExecuteSQL exec = new ExecuteSQL();
_ds = exec.getDataSet(_strSQL);
for (int i = 0; i < _ds.Tables[0].Rows.Count; i++)
{
_strSQL = "SELECT * FROM tblForms WHERE id IN";
_strSQL += " (SELECT formID FROM tblSchemePermission WHERE schemeID = ";
_strSQL += " (SELECT MAX(schemeID) FROM tblUserWiseScheme WHERE userID = ";
_strSQL += _userID + " AND comID = " + _comID + ") AND view = '1') AND moduleID = ";
_strSQL += _ds.Tables[0].Rows[i]["id"].ToString() + ";";
DataSet _dsForms = exec.getDataSet(_strSQL);
if (_dsForms.Tables[0].Rows.Count > 0)
{
if (_ds.Tables[0].Rows[i]["moduleName"].ToString() == "Administration")
{
_menu = menuAdministration;
}
else if (_ds.Tables[0].Rows[i]["moduleName"].ToString() == "Fleet Management")
{
_menu = menuFleet;
}
else
{
continue;
}
foreach (RadMenuItem item in _menu.Items)
{
_hasPermission = false;
for (int j = 0; j < _dsForms.Tables[0].Rows.Count; j++)
{
string _formName = _dsForms.Tables[0].Rows[j]["formName"].ToString();
if (_formName == item.Text)
{
_hasPermission = true;
break;
}
}
if (!_hasPermission && item.Value == "Admin") {
item.Visible = false;
}
}
}
else
{
if (_ds.Tables[0].Rows[i]["moduleName"].ToString() == "Administration")
{
menuAdministration.Visible = false;
}
else if (_ds.Tables[0].Rows[i]["moduleName"].ToString() == "Fleet Management")
{
menuFleet.Items.Clear();
}
}
}
}
}
private void ShowRulesDetails()
{
try
{
int comID, unitID;
string strSQL;
ExecuteSQL exec = new ExecuteSQL();
comID = int.Parse(Session["trkCompany"].ToString());
unitID = int.Parse(Request.QueryString["unitID"].ToString());
strSQL = "select * from tblAlertRules where comID = " + comID + " and unitID = " + unitID;
DataSet ds = new DataSet();
ds = exec.getDataSet(strSQL);
if (ds.Tables[0].Rows.Count > 0)
{
_txtNotificationEmails.Text = ds.Tables[0].Rows[0]["email"].ToString();
_txtPhoneNumber.Text = ds.Tables[0].Rows[0]["phoneNumber"].ToString();;
bool isActive = bool.Parse(ds.Tables[0].Rows[0]["isActive"].ToString());
if (isActive)
{
_chkOn.Checked = true;
_chkOff.Checked = false;
}
else
{
_chkOff.Checked = true;
_chkOn.Checked = false;
}
bool isSMS = false;
string tmp = ds.Tables[0].Rows[0]["isSMS"].ToString();
if (tmp.Length > 0)
{
isSMS = bool.Parse(tmp);
}
if (isSMS)
{
_ckOnSMS.Checked = true;
_chkOffSMS.Checked = false;
}
else
{
_ckOnSMS.Checked = false;
_chkOffSMS.Checked = true;
}
_btnOk.Text = "Update";
}
else
{
_btnOk.Text = "Save";
}
}
catch (Exception ex)
{
}
}