public static string GetUserEmail(string fname)
{
string eml = "";
Linq_1DataContext db = new Linq_1DataContext();
string e = (from a in db.WebAccounts
where a.FName == fname
select a.Email).FirstOrDefault();
if (e != null) { eml = e; }
return eml;
}
public static bool AuthUser(string uname, string pass)
{
bool isAuth = false;
Linq_1DataContext db = new Linq_1DataContext();
int usercount = (from a in db.WebAccounts
where a.UserName == uname && a.Password == pass && a.Active == true
select a).Count();
if (usercount != 0) { isAuth = true; }
return isAuth;
}
private void PopulateUsers()
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.WebAccounts
where a.AccountType != 'A'
orderby a.LastLogin descending
select a).ToList();
GridUsers.DataSource = td;
GridUsers.DataBind();
}
protected void btnBackup_Click(object sender, EventArgs e)
{
Linq_1DataContext db = new Linq_1DataContext();
try
{
db.BackupDb();
lblStatus.Text = "Database backup complete";
}
catch (Exception exx)
{
Shared.WriteError(exx, HttpContext.Current, " Backup Database");
}
}
public static int GetCustomerId(string srch)
{
int cus = 0;
Linq_1DataContext db = new Linq_1DataContext();
string user = (from a in db.Customers
where (a.Company.ToString() == srch || a.Address.ToString() == srch)
select a.id.ToString()).FirstOrDefault();
if (user != null)
{
cus = Convert.ToInt32(user);
}
return cus;
}
protected void Search_Click(object sender, EventArgs e)
{
string srch = txtSearch.Text;
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Trans
where (a.Customer.ToString()).Contains(srch) || (a.Address.ToString()).Contains(srch)
select a).ToList();
if (td != null)
{
GridTickets.DataSource = td;
GridTickets.DataBind();
txtSearch.Text = "";
ColorCode();
}
}
protected void Search_Click(object sender, EventArgs e)
{
string srch = txtSearch.Text;
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Customers
where (a.Company.ToString()).Contains(srch) || (a.Address.ToString()).Contains(srch)
orderby a.Company ascending
select a).ToList().Take(25);
if (td != null)
{
GridCustomers.DataSource = td;
GridCustomers.DataBind();
txtSearch.Text = "";
}
}
protected void btnSaveCustomer_Click(object sender, EventArgs e)
{
changecustomer.Style.Add("display", "none");
btnChangeCustomer.Visible = true;
btnSaveCustomer.Visible = false;
//lblStatus.Text = "Customer changed for ticket " + lblTixketNo.Text;
//Populate address and phone section
if (Request.QueryString["cst"] != null)
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Customers
where a.id == Convert.ToInt32(Request.QueryString["cst"])
select a).FirstOrDefault();
if (td != null)
{
linkCustomer.Text = td.Company;
hideCustomer.Value = td.Company;
hideId.Value = td.id.ToString();
txtContact.Text = td.FName;
txtPhone.Text = td.Phone;
txtServiceAddress.Text = td.Address + " " + td.Address2;
txtCity.Text = td.City;
txtZip.Text = td.Zip.ToString();
}
}
else
{
linkCustomer.Text = ddChangeCustomer.SelectedItem.Text;
hideCustomer.Value = ddChangeCustomer.SelectedItem.Text;
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Customers
where a.Company == hideCustomer.Value
select a).FirstOrDefault();
if (td != null)
{
hideId.Value = td.id.ToString();
txtContact.Text = td.FName;
txtPhone.Text = td.Phone;
txtServiceAddress.Text = td.Address + " " + td.Address2;
txtCity.Text = td.City;
txtZip.Text = td.Zip.ToString();
}
}
}
private void LoadGrid()
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Errors
orderby a.id descending
select a).ToList();
if (td.Count != 0)
{
GridError.DataSource = td;
GridError.DataBind();
}
else
{
GridError.Visible = false;
GridError.Visible = true;
}
}
private void LoadToday(string usr)
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Trans
where a.AssignedTo == usr && a.Status != "Closed"
orderby a.DateAdded ascending
select a).ToList();
if (td.Count != 0)
{
GridToday.DataSource = td;
GridToday.DataBind();
}
else
{
GridToday.Visible = false;
lblToday.Visible = true;
lblToday.Text = "No open service tickets.";
}
}
private void LoadRecent(string usr)
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Trans
where a.AssignedTo == usr
orderby a.LastMod descending
select a).Take(10);
if (td != null)
{
GridRecent.DataSource = td;
GridRecent.DataBind();
}
else
{
lblRecent.Visible = true;
GridRecent.Visible = false;
lblRecent.Text = "No recent service calls.";
}
}
protected void btnReset_Click(object sender, EventArgs e)
{
if (txtNewPass2.Text.Length >= 5)
{
Linq_1DataContext db = new Linq_1DataContext();
var usr = (from a in db.WebAccounts
where a.id == Convert.ToInt32(txtId.Text)
select a).FirstOrDefault();
if (usr != null)
{
usr.Password = txtNewPass2.Text;
db.SubmitChanges();
lblStatus.Text = "User's password has been reset.";
}
}
else
{
lblStatus.Text = "Password must be at least 5 characters long.";
}
}
private void LoadCustomer()
{
string user = (string)Session["username"];
if (Shared.IsAdmin(user))
{
btnDelete.Visible = true;
}
Linq_1DataContext db = new Linq_1DataContext();
int idd = 0;
string cid = "";
if (Request.QueryString["id"] != null)
{
idd = Convert.ToInt32(Request.QueryString["id"]);
var td = (from n in db.Customers
where n.id == idd
select n).FirstOrDefault();
if (td != null)
{
txtId.Text = td.id.ToString();
txtCompany.Text = td.Company;
txtContact.Text = td.FName;
txtAddress.Text = td.Address + " " + td.Address2;
txtCity.Text = td.City;
txtZip.Text = td.Zip.ToString();
txtEmail.Text = td.Email;
txtPhone.Text = td.Phone.ToString();
txtPhone2.Text = td.Phone2.ToString();
linkHistory.NavigateUrl = "~/tickets.aspx?cid=" + td.Company.Replace(' ', '+');
}
}
if (Request.QueryString["cid"] != null)
{
cid = Request.QueryString["cid"].Replace('+',' ');
var td = (from n in db.Customers
where n.Company == cid
select n).FirstOrDefault();
if (td != null)
{
txtId.Text = td.id.ToString();
txtCompany.Text = td.Company;
txtContact.Text = td.FName;
txtAddress.Text = td.Address + " " + td.Address2;
txtCity.Text = td.City;
txtZip.Text = td.Zip.ToString();
txtEmail.Text = td.Email;
txtPhone.Text = td.Phone.ToString();
txtPhone2.Text = td.Phone2.ToString();
}
}
}
protected void btnYes_Click(object sender, EventArgs e)
{
//delete customer and confirm message - if customer has tickets, cannot delete
Linq_1DataContext db = new Linq_1DataContext();
var hastickets = (from b in db.Trans
where b.CstId == Convert.ToInt32(txtId.Text)
select b.id).Count();
if (hastickets == 0)
{
var cst = (from b in db.Customers
where b.id == Convert.ToInt32(txtId.Text)
select b).FirstOrDefault();
if (cst != null)
{
try
{
db.Customers.DeleteOnSubmit(cst);
db.SubmitChanges();
lblStatus.Text = "Customer has been deleted.";
confirm.Visible = false;
txtAddress.Text = txtCity.Text = txtCompany.Text = txtContact.Text = txtEmail.Text = txtId.Text = txtPhone.Text = txtPhone2.Text = txtZip.Text = "";
}
catch (Exception ex)
{
lblStatus.Text = "Unable to delete customer, contact your web master.";
confirm.Visible = false;
}
}
}
else
{
lblStatus.Text = "Customer has service tickets, cannot delete.";
confirm.Visible = false;
}
}
private void LoadTechs()
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.WebAccounts
where a.Active == true && a.AccountType != 'A'
select a.FName).ToList();
ddTech.DataSource = td;
ddTech.DataBind();
}
private void LoadTicketInfo()
{
//customer name will go the the hyperlink and the link will go to the customer page to show customer details
Linq_1DataContext db = new Linq_1DataContext();
var td = (from n in db.Trans
where n.id == cid
select n).FirstOrDefault();
lblTixketNo.Text = "Ticket # " + td.id.ToString();
ddStatus.SelectedValue = td.Status;
ddTech.SelectedValue = td.AssignedTo;
txtScheduled.Text = string.Format("{0:d}", td.DateSch);
txtCost.Text = td.Amount.ToString();
txtCity.Text = td.City;
txtZip.Text = td.Zip.ToString();
txtEquipment.Text = td.Equip;
txtPhone.Text = td.Phone.ToString();
txtService.Text = td.Service;
if (td.Contact != null) { txtContact.Text = td.Contact; }
txtServiceAddress.Text = td.Address + " " + td.Address2;
txtClosed.Text = string.Format("{0:d}", td.DateComplete);
txtOpened.Text = string.Format("{0:d}", td.DateAdded);
hideId.Value = td.CstId.ToString();
hideCustomer.Value = td.Customer;
linkCustomer.Text = td.Customer + " (" + td.CstId.ToString() + ")";
linkCustomer.NavigateUrl = "~/customerdetail.aspx?id=" + hideId.Value;
CstName = td.Customer;
if (ddStatus.SelectedItem.Text == "Closed") { btnClose.Visible = false; }
}
private void LoadTickets(int i)
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Trans
orderby a.LastMod descending
select a).Take(i);
if (td != null)
{
GridTickets.DataSource = td;
GridTickets.DataBind();
ColorCode();
}
}
private void LoadEmployee(int eid)
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from n in db.WebAccounts
where n.id == eid
select n).FirstOrDefault();
if (td != null)
{
txtEmail.Text = td.Email;
txtFirst.Text = td.FName;
txtLast.Text = td.LName;
txtId.Text = td.id.ToString();
ddAccountType.SelectedValue = td.AccountType.ToString();
//txtType.Text = td.AccountType.ToString();
txtUserName.Text = td.UserName;
if (td.Active == true) { chkActive.Checked = true; }
}
}
public static bool IsAdmin(string usr)
{
bool isAdmin = false;
Linq_1DataContext db = new Linq_1DataContext();
int usercount = (from a in db.WebAccounts
where a.UserName == usr && (a.AccountType.ToString() == "A" || a.AccountType.ToString() == "B")
select a).Count();
if (usercount != 0) { isAdmin = true; }
return isAdmin;
}
public static string GetUserFullName(string username)
{
string strFullname = "";
Linq_1DataContext dbc = new Linq_1DataContext();
string nam = (from a in dbc.WebAccounts
where a.UserName == username
select a.FName + " " + a.LName
).First();
if (nam != null) { strFullname = nam; }
return strFullname;
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (btnNew.Visible == false)
{
if (txtNewPass1.Text == "" || txtNewPass2.Text == "")
{
lblStatus.Text = "Please enter a new password for this user";
return;
}
//if (ddAccountType.SelectedItem.Value != "B" || ddAccountType.SelectedItem.Value != "C")
//{
// lblStatus.Text = "Please select a valid account type. Admin account is B and standard is C";
// return;
//}
Linq_1DataContext db = new Linq_1DataContext();
WebAccount t = new WebAccount();
t.AccountType = Convert.ToChar(ddAccountType.SelectedValue);
if (chkActive.Checked) { t.Active = true; }
t.DateOpen = DateTime.Today;
t.Email = txtEmail.Text;
t.FName = txtFirst.Text;
t.LName = txtLast.Text;
t.Password = txtNewPass1.Text;
t.UserName = txtUserName.Text;
db.WebAccounts.InsertOnSubmit(t);
//insert new record
//Show success message + refresh
try
{
db.SubmitChanges();
lblStatus.Text = "New web user Successfully added.";
chkActive.Checked = true;
txtEmail.Text = txtNewPass2.Text = txtNewPass1.Text = txtId.Text = txtFirst.Text = txtLast.Text = txtLast.Text = txtUserName.Text = "";
}
catch (Exception ex)
{
Shared.WriteError(ex, HttpContext.Current, "btnSave_Click");
}
}
else
{
try
{
Linq_1DataContext db = new Linq_1DataContext();
var usr = (from a in db.WebAccounts
where a.id == Convert.ToInt32(txtId.Text)
select a).FirstOrDefault();
if (usr != null)
{
usr.Active = chkActive.Checked;
usr.FName = txtFirst.Text;
usr.LName = txtLast.Text;
usr.Email = txtEmail.Text;
usr.AccountType = Convert.ToChar(ddAccountType.SelectedValue);
usr.UserName = txtUserName.Text;
db.SubmitChanges();
lblStatus.Text = "User's profile updated.";
}
}
catch (Exception ex)
{
Shared.WriteError(ex, HttpContext.Current);
lblStatus.Text = "Error updating user's profile.";
}
}
}
protected void radView_SelectedIndexChanged(object sender, EventArgs e)
{
GridTicks.DataBind();
Linq_1DataContext db = new Linq_1DataContext();
switch (radView.SelectedItem.Text)
{
case "Closed":
var td1 = (from a in db.Trans
where a.Status == "Closed"
orderby a.LastMod descending
select a).Take(100);
if (td1 != null)
{
GridTicks.DataSource = td1;
GridTicks.DataBind();
}
GridTicks.Columns[6].Visible = true;
GridTicks.Columns[7].Visible = true;
GridTicks.Columns[8].Visible = false;
lblsummary.Text = "Showing Last 100";
break;
case "Scheduled":
var td2 = (from a in db.Trans
where a.DateSch != null && a.DateComplete == null
orderby a.DateSch descending
select a).ToList();
if (td2.Count != 0)
{
GridTicks.DataSource = td2;
GridTicks.DataBind();
}
GridTicks.Columns[6].Visible = true;
GridTicks.Columns[7].Visible = false;
GridTicks.Columns[8].Visible = false;
lblsummary.Text = "Total Tickets: " + td2.Count();
break;
case "Warranty":
var td3 = (from a in db.Trans
where a.Warranty == true
orderby a.DateAdded descending
select a).Take(100);
if (td3 != null)
{
GridTicks.DataSource = td3;
GridTicks.DataBind();
}
GridTicks.Columns[6].Visible = true;
GridTicks.Columns[7].Visible = true;
GridTicks.Columns[8].Visible = true;
lblsummary.Text = "Total Tickets: " + td3.Count();
break;
case "Not Scheduled":
var td4 = (from a in db.Trans
where (a.DateSch == null || a.Status == "Scheduled") && a.Status != "Closed"
orderby a.DateAdded ascending
select a).ToList();
if (td4.Count != 0)
{
GridTicks.DataSource = td4;
GridTicks.DataBind();
}
GridTicks.Columns[8].Visible = true;
GridTicks.Columns[6].Visible = false;
GridTicks.Columns[7].Visible = false;
lblsummary.Text = "Total Tickets: " + td4.Count();
break;
case "By Employee":
var td5 = (from a in db.Trans
where a.Status != "Closed"
orderby a.AssignedTo ascending
select a).ToList();
if (td5.Count != 0)
{
GridTicks.DataSource = td5;
GridTicks.DataBind();
}
GridTicks.Columns[6].Visible = true;
GridTicks.Columns[8].Visible = true;
GridTicks.Columns[7].Visible = false;
lblsummary.Text = "Total Tickets: " + td5.Count();
break;
}
if (GridTicks.Columns[8].Visible == true)
{
ColorCode();
}
}
private void LoadNotes()
{
Linq_1DataContext db = new Linq_1DataContext();
var td = (from n in db.TransNotes
where n.Trans == cid
select n).ToList();
GridNotes.DataSource = td;
GridNotes.DataBind();
}
public static void WriteError(Exception ex, HttpContext con, string method = "")
{
Linq_1DataContext db = new Linq_1DataContext();
Error t = new Error();
t.BrowserName = con.Request.UserAgent.ToString();
t.LongDescription = method + " " + ex.Message.ToString();
t.ErrorUser = (int)con.Session["userid"];
t.ErrorDate = DateTime.Today;
try
{
db.Errors.InsertOnSubmit(t);
db.SubmitChanges();
}
catch (Exception e)
{
ErrorOnError(e);
}
}
public static int GetUserId(string uname)
{
int cus = 0;
Linq_1DataContext db = new Linq_1DataContext();
int user = (from a in db.WebAccounts
where a.UserName == uname
select a.id).FirstOrDefault();
if (user != 0)
{
cus = user;
}
return cus;
}
protected void btnSave_Click(object sender, EventArgs e)
{
int iCstId = 0;
if (Request.QueryString["IsNew"] == "true")
{
Linq_1DataContext db = new Linq_1DataContext();
Customer c = new Customer();
c.Company = txtCompany.Text;
c.Address = txtAddress.Text;
c.City = txtCity.Text;
if (txtZip.Text != "") { c.Zip = Convert.ToInt32(txtZip.Text); }
c.FName = txtContact.Text;
c.Phone = txtPhone.Text;
c.Phone2 = txtPhone2.Text;
c.Email = txtEmail.Text;
try
{
db.Customers.InsertOnSubmit(c);
db.SubmitChanges();
iCstId = c.id;
}
catch (Exception et)
{
Shared.WriteError(et, HttpContext.Current, "btnSave_Click");
}
finally
{
string redir = "~/ticketdetail.aspx?IsNew=true&cst=" + iCstId.ToString() ;
//string redir = "~/ticketdetail.aspx?IsNew=true";
Response.Redirect(redir);
}
}
else
{
int cid = Convert.ToInt32(txtId.Text);
Linq_1DataContext db = new Linq_1DataContext();
var t = (from a in db.Customers
where a.id == cid
select a).FirstOrDefault();
if (t != null)
{
t.Company = txtCompany.Text;
t.FName = txtContact.Text;
t.Address = txtAddress.Text;
t.City = txtCity.Text;
if (txtZip.Text != "") { t.Zip = Convert.ToInt32(txtZip.Text); }
t.Email = txtEmail.Text;
t.Phone = txtPhone.Text;
t.Phone2 = txtPhone2.Text;
try
{
db.SubmitChanges();
lblStatus.Text = "Customer " + cid.ToString() + " has been updated.";
}
catch (Exception et)
{
Shared.WriteError(et, HttpContext.Current, "btnSave_Click" );
}
}
}
}
public static void WriteLogin(string user)
{
Linq_1DataContext db = new Linq_1DataContext();
db.WriteLogin(user);
}
public static string GetUserName(int uid)
{
string strFullname = "";
Linq_1DataContext dbc = new Linq_1DataContext();
string nam = (from a in dbc.WebAccounts
where a.id == uid
select a.FName
).First();
if (nam != null) { strFullname = nam; }
return strFullname;
}
private static void ErrorOnError(Exception ex)
{
EmailError(ex);
Linq_1DataContext db = new Linq_1DataContext();
Error t = new Error();
t.LongDescription = ex.ToString();
t.ErrorDate = DateTime.Today;
db.Errors.InsertOnSubmit(t);
db.SubmitChanges();
}
protected void btnSrchCustomer_Click(object sender, EventArgs e)
{
ddChangeCustomer.Visible = true;
string srch = txtChangeCustomer.Text;
Linq_1DataContext db = new Linq_1DataContext();
var td = (from a in db.Customers
where a.Company.Contains(srch)
orderby a.Company ascending
select a).ToList().Take(10);
if (td != null)
{
btnSaveCustomer.Visible = true;
ddChangeCustomer.DataSource = td;
ddChangeCustomer.DataBind();
txtChangeCustomer.Text = "";
}
}
