protected void Page_Load(object sender, EventArgs e)
    {
        this.Title = Resources.Common.MyIssues;
        GridView[] gvs = new GridView[] { gvMy, gvGroup };
        db = new dbDataContext();
        userName = Utils.UserName();
        userIsRegistered = Users.Exists(db, userName);
        user me = Users.Get(db, userName);
        lblMyGroup.Text = me.sub_unit1.unit.unit_name + " - " + me.sub_unit1.sub_unit_name;
        txtSubmitter.Text = me.id.ToString();
        txtGroup.Text = me.sub_unit.ToString();

        urgency.Add(1, "transparent");
        urgency.Add(2, "#ffe800;color:#666666;");
        urgency.Add(3, "#ff7700");
        urgency.Add(4, "#ff2f00");

        myTickets = Tickets.MyTickets(db, me.id);
        groupTickets = Tickets.MyGroupsTickets(db, me);

        if (!IsPostBack)
        {
            System.Drawing.Color alt_color = System.Drawing.ColorTranslator.FromHtml(Themes.Current(db).alt_rows);
            foreach (GridView gv in gvs)
            {
                gv.HeaderStyle.BackColor = alt_color;
                gv.AlternatingRowStyle.BackColor = alt_color;
            }
            gvMy.DataSource = myTickets;
            gvMy.DataBind();
            gvGroup.DataSource = groupTickets;
            gvGroup.DataBind();
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        this.Title = Resources.Common.NewTicket;
        db = new dbDataContext();
        userName = Utils.UserName();

        ddlUnit.Focus();
        try{ accessLevel = Utils.AccessLevel().security_level1.id; }
        catch { }
        if (!IsPostBack)
        {
            foreach (priority p in Dbi.Priorities.List(db, 10)) ddlPriority.Items.Add(new ListItem(p.priority_name, p.id.ToString()));
            var units = Groups.List(db, accessLevel);
            if (units.Count() < 1)
            {
                // user has no privelege to post new tickets
                lblReport.report(false, GetLocalResourceObject("DontHaveAccess").ToString() + "<br /><br /><span class='smaller'>" +
                    GetLocalResourceObject("ContactAdmin").ToString() + "</span>", null);
                pnlInput.Style.Add(HtmlTextWriterStyle.Display, "none");
                pnlError.Visible = true;
            }
            else 
            {
                foreach (unit u in units.OrderBy(p => p.unit_name))
                    ddlUnit.Items.Add(new ListItem(u.unit_name, u.id.ToString()));
                Utils.PopulateSubUnits(db, ddlUnit, ddlSubUnit, accessLevel);
            }
        }
    }

        /// <summary>
        /// This function get the users
        /// </summary>
        /// <param name="userTypeId"></param>
        /// <returns></returns>
        public IEnumerable<User> GetUsers(int userTypeId)
        {
            var context = new dbDataContext();
            var users = context.tbl_Users.Where(t => !t.Deleted);

            //filter by type if passed
            if (userTypeId > 0) users = users.Where(t => t.UserType == userTypeId);

            return users.Select(user => new User
               {
                   UserId = user.UserId,
                   Forename = user.Forename,
                   Surname = user.Surname,
                   JobTitle = user.JobTitle,
                   Email = user.Email,
                   Password = user.Password,
                   Telephone = user.Telephone,
                   CreatedDate = user.CreatedDate,
                   Fax = user.Fax,
                   Mobile = user.Mobile,
                   Deleted = user.Deleted,
                   CalendarColor = user.CalendarColor,
                   FullName = user.Forename + " " + user.Surname,
                   UserType = GetUserType(user.UserType),
                   IsActive = user.IsActive
               });
        }

        /// <summary>
        /// This function returns the application statuses 
        /// </summary>
        /// <param name="filters"></param> 
        /// <returns></returns>
        public IEnumerable<ApplicationStatus> GetApplicationStatuses(ApplicationStatusFilter filters)
        {
            var context = new dbDataContext();

            //get default statuses
            var applicationStatuses = filters.JobId > 0 ? context.tbl_ApplicationStatus.Where(t => (t.IsDefault || t.JobId == filters.JobId))
                : context.tbl_ApplicationStatus.Where(t => t.IsDefault);

            // return application statuses
            var appStatuses = applicationStatuses.Where(t => !t.Deleted).OrderBy(t => t.ViewOrder).Select(appStatus => new ApplicationStatus
            {
                ApplicationStatusId = appStatus.ApplicationStatusId,
                StatusName = appStatus.StatusName,
                IsDefault = appStatus.IsDefault,
                EmailTemplateId = appStatus.EmailTemplateId,
                InterviewType = appStatus.InterviewType,
                JobId = appStatus.JobId,
                ApplicationCount = (filters.NeedAppCount && filters.JobId > 0) ?
                (context.tbl_JobApplications.Count(t => t.JobId == filters.JobId && t.ApplicationStatusId == appStatus.ApplicationStatusId)) : 0
            }).ToList();


            // add view all if job id is passed
            if (filters.JobId > 0)
                appStatuses.Insert(0, new ApplicationStatus { ApplicationStatusId = 0, StatusName = "View All", ApplicationCount = (context.tbl_JobApplications.Count(t => t.JobId == filters.JobId)), IsDefault = true });
            return appStatuses;
        }

 public static void Add(dbDataContext db, string domain)
 {
     allowed_email_domain aed = new allowed_email_domain();
     aed.domain = domain;
     db.allowed_email_domains.InsertOnSubmit(aed);
     db.SubmitChanges();
 }

    public static string Import(Stream xmlFile)
    {
        string output = string.Empty;
        try
        {
            TextReader rdr = new StreamReader(xmlFile);
            XElement x = XElement.Load(rdr);
            var faqs = from p in x.Descendants("faq") select p;

            foreach (XElement xe in faqs)
            {
                dbDataContext db = new dbDataContext();
                faq f = new faq();
                f.title = xe.FirstAttribute.Value;
                f.body = xe.Value;
                db.faqs.InsertOnSubmit(f);
                try
                {
                    db.SubmitChanges(); //inefficient to submit each time, *but* this will tell which faqs got inserted and which didn't
                    output += "<div class='success'>-" + xe.FirstAttribute.Value + " " + Resources.Common.Updated + "</div>";
                }
                catch// (Exception ex)
                {
                    output += "<div class='error'>" + Resources.Common.Error + " " + xe.FirstAttribute.Value + "</div>";
                }
            }
        }
        catch (Exception ex)
        {
            output = "<div class='error'>" + Resources.Common.Error + ": <div class='sub_error'>" + ex.Message + "</div></div>";
        }
        return output;
    }

        public String[] ComputeGuildsItemLevel(String dbAddress, String realmName)
        {
            GuildDictionary dataFromBase = new GuildDictionary();
            List<String> list = new List<String>();

            dbDataContext cont = new dbDataContext(dbAddress);

            var data = cont.CharInfos.Where(x => x.realm == realmName).ToList();
            foreach (CharInfo ci in data)
            {
                dataFromBase.Add(ci.realm, ci.guild, ci.name, (int)ci.itemLevel, ci.role);
            }

            int result = 0;
            foreach(var guild in dataFromBase.Get())
            {
                RaidCounter rc = new RaidCounter();

                foreach(var character in guild.Value)
                {
                    result += character.itemLevel;
                    rc.AddCharacterRole(character.role);
                    if (rc.isEnough())
                        break;
                }

                result /= rc.Count();

                list.Add(guild.Key + " - " + result.ToString());
            }

            return list.ToArray();
        }

        /// <summary>
        /// Get client user from the id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ClientUser GetClientUser(int id)
        {
            var context = new dbDataContext();
            // Set up the query to retrieve client user
            var clientUser = context.tbl_ClientUsers.FirstOrDefault(t => t.ClientUserId == id);
            if (clientUser != null)
            {
                var cUser = new ClientUser
                {
                    ClientId = clientUser.ClientId,
                    ClientUserId = clientUser.ClientUserId,
                    Email = clientUser.Email,
                    Forename = clientUser.Forename,
                    Surname = clientUser.Surname,
                    Password = clientUser.Password,
                    JobTitle = clientUser.JobTitle,
                    Mobile = clientUser.Mobile,
                    Telephone = clientUser.Telephone,
                    IosApp = clientUser.IosApp,
                    IsActive = clientUser.IsActive,
                    CreatedDate = clientUser.CreatedDate,
                    FooterTemplate = GetFooterTemplate(id)
                };

                var docs = Documents.GetDocumentByDocType(new[] { 9 }, clientUser.ClientUserId).ToList();
                if (docs.Count > 0)
                {
                    cUser.ProfileImage = docs[0];
                }
                return cUser;
            }
            return null;
        }

        public void CheckTimeZone()
        {
            db = new dbDataContext();
            try
            {
                var timezone = (from t in db.GetTable<timezones_t>()
                    where t.timezone_start <= DateTime.Now.TimeOfDay
                    where t.timezone_end > DateTime.Now.TimeOfDay
                    select t.timezone_name).SingleOrDefault();
                MessageBox.Show(timezone);
            }
            catch (Exception)
            {
                var timezone = (from t in db.GetTable<timezones_t>()
                                where t.timezone_start <= DateTime.Now.TimeOfDay
                                where t.timezone_end < DateTime.Now.TimeOfDay
                                select t.timezone_name).Single();
                MessageBox.Show(timezone);
            }
            //bool n = DateTime.Parse("1:00:00") < DateTime.Parse("7:00:00");
            //bool n2 = DateTime.Parse("1:00:00") < DateTime.Parse("12:00:00");

            //bool n = DateTime.Parse("1:00:00") < DateTime.Parse("12:00:00");
            //bool n2 = DateTime.Parse("1:00:00") < DateTime.Parse("19:00:00");

            //bool n = DateTime.Parse("1:00:00") < DateTime.Parse("19:00:00");
            //bool n2 = DateTime.Parse("1:00:00") < DateTime.Parse("7:00:00");
            //MessageBox.Show(n + " " + n2);
        }

 public static void Add(dbDataContext db, string unitName)
 {
     unit newUnit = new unit();
     newUnit.unit_name = unitName;
     db.units.InsertOnSubmit(newUnit);
     db.SubmitChanges();
 }

    /// <summary>
    /// This function adds the widgets for a user
    /// </summary>
    /// <param name="widgets"></param>
    /// <returns></returns>
    public bool SetUserWidgets(List<DashboardWidget> widgets)
    {
        if (widgets != null && widgets.Count > 0)
        {
            var context = new dbDataContext();
            var userId = widgets[0].UserId;

            //delete all the widgets for the user
            var foundWidgets = context.tbl_UserWidgets.Where(t => t.UserId == userId).Select(t => t).ToList();
            context.tbl_UserWidgets.DeleteAllOnSubmit(foundWidgets);
            context.SubmitChanges();

            //add widgets
            foreach (var wi in widgets)
            {
                if (string.IsNullOrEmpty(wi.WidgetName))
                {
                    continue;
                }

                var widget = new tbl_UserWidget
                {
                    Ordering = wi.Ordering,
                    UserId = wi.UserId,
                    WidgetName = wi.WidgetName,
                    WidgetTitle = wi.WidgetTitle,
                    Width = wi.Width
                };
                context.tbl_UserWidgets.InsertOnSubmit(widget);
            }
            context.SubmitChanges();
        }
        return true;
    }

 public static void Edit(dbDataContext db, int id, string q, string a)
 {
     faq f = Get(db, id);
     f.title = q;
     f.body = a;
     db.SubmitChanges();
 }

 /// <summary>
 /// Thsis function returns the list of all the templates
 /// </summary>
 /// <param name="filters"></param>
 /// <returns></returns>
 public List<Template> GetTemplates(TemplateFilter filters)
 {
     var context = new dbDataContext();
     var templateIds = string.IsNullOrEmpty(filters.TemplateIds) ? new List<string>() : filters.TemplateIds.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
     var templates =
         (from t in context.tbl_Templates
          where (t.Deleted == filters.Deleted && (filters.ClientId == 0 || t.ClientId == filters.ClientId) &&
                (string.IsNullOrEmpty(filters.TemplateIds) || templateIds.Contains(t.TemplateId.ToString()))
                 &&
                  (string.IsNullOrEmpty(filters.Title) || t.Title.ToLower().Equals(filters.Title.ToLower())))
          select new Template
                              {
                                  TemplateId = t.TemplateId,
                                  Title = t.Title,
                                  ApplicationStatusId = t.ApplicationStatusId,
                                  Deleted = t.Deleted,
                                  Published = t.Published,
                                  ClientId = t.ClientId,
                                  Subject = t.Subject,
                                  TemplateBody = t.Template,
                                  CreatedDate = t.CreatedDate,
                                  TemplateTypeId = t.TemplateTypeId,
                                  ApplicationStatus = new ApplicationStatuses().GetApplicationStatusName(t.ApplicationStatusId)
                              });
     return templates.ToList();
 }

        /// <summary>
        /// This function add/edit application status
        /// </summary>
        /// <param name="appStatusDetails"></param>
        /// <returns></returns>
        public int AddUpdateApplicationStatus(ApplicationStatus appStatusDetails)
        {
            var context = new dbDataContext();
            var appStatus = context.tbl_ApplicationStatus.FirstOrDefault(t => t.ApplicationStatusId == appStatusDetails.ApplicationStatusId) ??
                            new tbl_ApplicationStatus();
            appStatus.StatusName = appStatusDetails.StatusName;
            appStatus.IsDefault = appStatusDetails.IsDefault;
            appStatus.EmailTemplateId = appStatusDetails.EmailTemplateId;
            appStatus.InterviewType = appStatusDetails.InterviewType;
            appStatus.JobId = appStatusDetails.JobId;

            //   try
            //  {
            // Add/Update application status
            if (appStatus.ApplicationStatusId <= 0)
            {
                appStatus.ViewOrder = GetNextRecordId();
                context.tbl_ApplicationStatus.InsertOnSubmit(appStatus);
            }
            context.SubmitChanges();

            //change orders  
            ChangeOrder("");
            return appStatus.ApplicationStatusId;
            // }
            // catch (Exception)
            // {
            return -1;
            // }
        }

 /// <summary>
 /// This function validates the user
 /// </summary>
 /// <param name="username"></param>
 /// <param name="password"></param>
 /// <returns></returns>
 public User ValidateUser(string username, string password)
 {
     var context = new dbDataContext();
     // Set up the query to retrieve user
     var user = context.tbl_Users.FirstOrDefault(t => String.Compare(t.Email, username, StringComparison.OrdinalIgnoreCase) == 0 &&
         String.Compare(t.Password, password, StringComparison.OrdinalIgnoreCase) == 0 && !t.Deleted && t.IsActive);
     if (user != null)
     {
         var cUser = new User
         {
             UserId = user.UserId,
             Forename = user.Forename,
             Surname = user.Surname,
             JobTitle = user.JobTitle,
             Email = user.Email,
             Password = user.Password,
             Telephone = user.Telephone,
             CreatedDate = user.CreatedDate,
             Fax = user.Fax,
             Mobile = user.Mobile,
             FullName = user.Forename + " " + user.Surname,
             Deleted = user.Deleted,
             IsActive = user.IsActive,
             CalendarColor = user.CalendarColor,
             UserType = GetUserType(user.UserType)
         };
         var docs = Documents.GetDocumentByDocType(new[] { 10 }, cUser.UserId).ToList();
         if (docs.Count > 0)
         {
             cUser.ProfileImage = docs[0];
         }
         return cUser;
     }
     return null;
 }

        /// <summary>
        /// Validate client user
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public ClientUser ValidateClientUser(string username, string password)
        {
            var context = new dbDataContext();
            // Set up the query to retrieve client user
            var clientUser = context.tbl_ClientUsers.FirstOrDefault(t => t.IsActive && String.Compare(t.Email, username, StringComparison.OrdinalIgnoreCase) == 0 && String.Compare(t.Password, password, StringComparison.OrdinalIgnoreCase) == 0);
            if (clientUser != null)
            {
                var cUser = new ClientUser
                {
                    ClientId = clientUser.ClientId,
                    ClientUserId = clientUser.ClientUserId,
                    Email = clientUser.Email,
                    Forename = clientUser.Forename,
                    Surname = clientUser.Surname,
                    Password = clientUser.Password,
                    Telephone = clientUser.Telephone,
                    JobTitle = clientUser.JobTitle,
                    Mobile = clientUser.Mobile,
                    IosApp = clientUser.IosApp,
                    Published = clientUser.IsActive,
                    CreatedDate = clientUser.CreatedDate
                };

                var docs = Documents.GetDocumentByDocType(new[] { 9 }, clientUser.ClientUserId).ToList();
                if (docs.Count > 0)
                {
                    cUser.ProfileImage = docs[0];
                }
                return cUser;
            }
            return null;
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     fileUrl = Server.MapPath(".") + "\\patch_Data\\update.sql";
     bool done = bool.Parse(Setup.Patch.Get("db_updated"));
     btnRunSQL.Enabled = !done;
     lblDBCreation.Visible = done;
     lnkNext.Visible = done;
     if (!done)
     {
         string templateUrl = Server.MapPath(".") + "\\patch_Data\\update_template.sql";
         StreamReader sr = new StreamReader(templateUrl);
         StreamWriter sw = new StreamWriter(fileUrl, false);
         try
         {
             dbDataContext db = new dbDataContext();
             int first_group = db.sub_units.First().id;
             while (!sr.EndOfStream)
                 sw.WriteLine(sr.ReadLine().Replace("_REPLACE_", first_group.ToString()));
         }
         catch (Exception ex)
         {
             lblOutput.Text = "An error occured: " + ex.ToString();
             lblOutput.CssClass = "error";
         }
         finally
         {
             sr.Close(); sr.Dispose();
             sw.Close(); sw.Dispose();
         }
     }
 }

        /// <summary>
        /// Save documents
        /// </summary>
        /// <param name="documentList"></param>
        /// <param name="saveToDb"></param>
        /// <returns></returns>
        public bool SaveDocuments(IEnumerable<Document> documentList, bool saveToDb = true)
        {
            var context = new dbDataContext();
            foreach (var doc in documentList)
            {
                var docId = 0;
                var docTypeIds = context.tbl_DocumentTypes.Where(t => t.DeleteExisting).Select(t => t.DocTypeValue).ToList();
                if (!string.IsNullOrEmpty(doc.UploadUrl))
                {
                    // Download and save document
                    docId = DownloadAndSaveDocument(doc, docTypeIds.Contains(doc.DocumentTypeValue), saveToDb);
                }
                else if (!string.IsNullOrEmpty(doc.Base64String))
                {
                    // Create the file using base 64 string
                    docId = CreateFileFromBase64String(doc, docTypeIds.Contains(doc.DocumentTypeValue), saveToDb);
                }
                else if (doc.PostedFile != null)
                {
                    // Create the file using file uploader
                    docId = CreateFileFromFileUploader(doc, docTypeIds.Contains(doc.DocumentTypeValue), saveToDb);
                }

                // Save Entry into the 
                if (doc.DocumentTypeValue == 4 && docId > 0)
                {
                    AddCvDatabaseIndexRecord(docId);
                } 
            }
            return true;
        }

        /// <summary>
        /// This function returns the killer questions with answers for a given job id
        /// </summary>
        /// <param name="jobId"></param>
        /// <returns></returns>
        public IEnumerable<KillerQuestion> GetKillerQuestionsForJob(int jobId)
        {
            var context = new dbDataContext();
            var questions = (from t in context.tbl_KillerQuestions
                             where (t.JobId == jobId && !t.Deleted)
                             select new KillerQuestion
                                 {
                                     Question = t.Question,
                                     KillerQuestionId = t.QuestionId,
                                     JobId = t.JobId,
                                     Published = t.Published,
                                     SingleChoice = t.SingleChoice,
                                     ExcludeFromScoring = t.ExcludeFromScoring
                                 }).ToList();

            foreach (var ques in questions)
            {
                ques.Answers = (from k in context.tbl_KillerQuestionAnswers
                                where k.QuestionId == ques.KillerQuestionId
                                select new KillerQuestionAnswer
                                {
                                    AnswerId = k.AnswerId,
                                    Answer = k.Answer,
                                    KillerQuestionId = k.QuestionId,
                                    Score = k.Score
                                }).ToList();
            }
            return questions;
        }

        /// <summary>
        /// This function returns the killer question for a given id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public KillerQuestion GetKillerQuestion(int id)
        {
            var context = new dbDataContext();
            var ques = (from t in context.tbl_KillerQuestions
                        join c in context.tbl_KillerQuestionAnswers on t.QuestionId equals c.QuestionId into j1
                        from j2 in j1.DefaultIfEmpty()
                        where t.QuestionId == id
                        group j2 by new { t }
                            into grouped
                            select new KillerQuestion
                            {
                                Question = grouped.Key.t.Question,
                                KillerQuestionId = grouped.Key.t.QuestionId,
                                JobId = grouped.Key.t.JobId,
                                Published = grouped.Key.t.Published,
                                SingleChoice = grouped.Key.t.SingleChoice,
                                ExcludeFromScoring = grouped.Key.t.ExcludeFromScoring,

                            }).FirstOrDefault();


            if (ques != null)
                ques.Answers = (from k in context.tbl_KillerQuestionAnswers
                                where k.QuestionId == ques.KillerQuestionId
                                select new KillerQuestionAnswer
                                {
                                    AnswerId = k.AnswerId,
                                    Answer = k.Answer,
                                    KillerQuestionId = k.QuestionId,
                                    Score = k.Score
                                }).ToList();
            return ques;
        }