C# DB.ViziAppsExecuteScalar方法代码示例

    protected void ActivateCustomer_Click(object sender, EventArgs e)
    {
        ClearMessages();
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        string customer_id = State["ServerAdminCustomerID"].ToString();
        if (customer_id == "0")
        {
            AdminMessage.Text = "Select a customer and try again.";
            return;
        }

        //check if admin
        string sql = "SELECT status FROM customers WHERE customer_id='" + customer_id + "'";
        DB db = new DB();
        string status = db.ViziAppsExecuteScalar(State, sql);
        if (status == "admin")
        {
            db.CloseViziAppsDatabase(State);
            AdminMessage.Text = "Status of Admin Customer can not be changed.";
        }
        else
        {
            sql = "UPDATE customers SET status='active' WHERE customer_id='" + customer_id + "'";
            db.ViziAppsExecuteNonQuery(State, sql);
            db.CloseViziAppsDatabase(State);
            CustomerStatus.Text = "active";
            AdminMessage.Text = "Customer has been activated.";
        }
    }

    public XmlDocument GetCustomerInfo()
    {
        XmlUtil x_util = new XmlUtil();
        Util util = new Util();
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        XmlNode status = null;
        XmlDocument Response = new XmlDocument();
        XmlNode root = Response.CreateElement("response");
        Response.AppendChild(root);
        try
        {
            DB db = new DB();
            String sql = "SELECT COUNT(*) FROM customers WHERE status!='inactive'";
            String count = db.ViziAppsExecuteScalar(State, sql);
            x_util.CreateNode(Response, root, "customer_count", count);
            db.CloseViziAppsDatabase(State);
            x_util.CreateNode(Response, root, "status", "success");
        }
        catch (System.Exception SE)
        {
            util.LogError(State, SE);

            if (status == null)
            {
                Response = new XmlDocument();
                XmlNode root2 = Response.CreateElement("response");
                Response.AppendChild(root2);
                status = x_util.CreateNode(Response, root2, "status");

            }
            status.InnerText = SE.Message;
            util.LogError(State, SE);
        }
        return Response;
    }

 public string GetApplicationTypeForAdmin(Hashtable State)
 {
     DB db = new DB();
     string sql = "SELECT application_type FROM applications WHERE application_name='" + State["SelectedAdminApp"].ToString() + "' AND customer_id='" + State["ServerAdminCustomerID"].ToString() + "'";
     string application_type = db.ViziAppsExecuteScalar(State, sql);
     db.CloseViziAppsDatabase(State);
     return application_type;
 }

 public string GetDefaultTimeZone(Hashtable State)
 {
     DB db = new DB();
     string sql = "SELECT default_time_zone_delta_hours FROM customers WHERE customer_id='" + State["CustomerID"].ToString() + "'";
     string default_time_zone_delta_hours = db.ViziAppsExecuteScalar(State,sql);
     db.CloseViziAppsDatabase(State);
     State["TimeZoneDeltaHours"] = default_time_zone_delta_hours;
     return default_time_zone_delta_hours;
 }

    //Get the SKU from paid_services table for the AppID.
    public String getAppPaidSKU(Hashtable State)
    {
        DB db = new DB();
            string sql = "SELECT sku FROM paid_services WHERE application_id='" + State["application_id"].ToString() + "' AND status='paid'";
            string sku = db.ViziAppsExecuteScalar(State, sql);
            db.CloseViziAppsDatabase(State);
            State["SelectedAppSKU"] = sku;
            string AppSKU = sku;

            System.Diagnostics.Debug.WriteLine("AppSKU =" + AppSKU);
            return AppSKU;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        Util util = new Util();
        if (util.CheckSessionTimeout(State, Response, "Default.aspx")) return;

           try
        {
            Message.Text = "";
            ToEmail.Text = Request.QueryString.Get("email");
            EmailType.Text = Request.QueryString.Get("type");

            //fill in customers applications
            string sql = "SELECT application_name FROM applications WHERE customer_id='" +  State["CustomerID"].ToString() + "' ORDER BY application_name";
            DB db = new DB();
            DataRow[] rows = db.ViziAppsExecuteSql(State, sql);
            ApplicationList.Items.Clear();
            if (rows != null && rows.Length > 0)
            {
                foreach (DataRow row in rows)
                {
                    ApplicationList.Items.Add(row["application_name"].ToString());
                }
            }
            ApplicationList.Items.Insert(0, "No Application Issue");

            sql = "SELECT email FROM customers WHERE customer_id='" +  State["CustomerID"].ToString() + "'";
            string from = db.ViziAppsExecuteScalar(State, sql);
            if (EmailType.Text == "Customer Email")
            {
                FromEmail.Text =   HttpRuntime.Cache["TechSupportEmail"].ToString();
            }
            else if (from == null)
            {
                FromEmail.Text = "";
            }
            else
            {
                FromEmail.Text = from;
            }
            db.CloseViziAppsDatabase(State);
        }
        catch (Exception ex)
        {
            util.ProcessMainExceptions(State, Response, ex);
        }
    }

    protected void AgreeButton_Click(object sender, EventArgs e)
    {
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        Util util = new Util();
        if (util.CheckSessionTimeout(State, Response, "Default.aspx")) return;

        DB db = new DB();
        string sql = "SELECT agreed_to_eula FROM customers WHERE customer_id = '" +  State["CustomerID"].ToString() + "'";
        string agreed_to_eula = db.ViziAppsExecuteScalar((Hashtable)HttpRuntime.Cache[Session.SessionID], sql);
        if (agreed_to_eula.ToLower() == "false" || agreed_to_eula == "0")
        {
            sql = "UPDATE customers SET agreed_to_eula=true WHERE customer_id = '" +  State["CustomerID"].ToString() + "'";
            db.ViziAppsExecuteNonQuery((Hashtable)HttpRuntime.Cache[Session.SessionID], sql);
            SendEmailToSalesandCustomer(db);
        }
        db.CloseViziAppsDatabase(State);
         State["LoggedinFromEula"] = true;
        Response.Redirect("Default.aspx", false);
    }

    protected void FromAccounts_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
    {
        Util util = new Util();
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        if (util.CheckSessionTimeout(State, Response, "../Default.aspx")) return;

        if (e.Text.IndexOf("->") > 0)
        {
            Applications.Visible = false;
            CopyApplicationButton.Visible = false;
            return;
        }
        Applications.Visible = true;

        DB db = new DB();
        string sql = "SELECT customer_id FROM customers WHERE username='" + e.Text + "'";
        string customer_id = db.ViziAppsExecuteScalar(State, sql);
         State["CopyApplicationFromCustomerID"] = customer_id;

        Init init = new Init();
        init.InitAppsList(State, Applications, customer_id);

        db.CloseViziAppsDatabase(State);
    }

    protected void CreateAccountSubmit_ServerClick(object sender, EventArgs e)
    {
        //check for competitors
        Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
        string address = EmailTextBox.Text.ToLower();
        string bad_domains = Server.MapPath(".") + @"\App_Data\BadDomains.txt";
        string[] lines = File.ReadAllLines(bad_domains);
        foreach(string line in lines)
        {
            if (address.EndsWith(line))
            {
                MessageLabel.Text = "An email has been sent to you to complete your registration. Please follow the directions in the email.";
                return;
            }
        }

        Util util = new Util();
        DB db = new DB();

        Label Error = new Label();
        StringBuilder err = new StringBuilder();
        string username = UsernameBox.Text.Trim().ToLower();
        if (!Check.ValidateUsername(Error, username))
        {
            err.Append(Error.Text.Clone() + "<BR>");
        }
        else
        {
            string query = "SELECT username FROM customers WHERE username='" + username + "'";
            string prev_username = db.ViziAppsExecuteScalar(State,query);
            if (username == prev_username)
            {
               /* query = "SELECT password FROM customers WHERE username='" + username + "'";
                string password = db.ViziAppsExecuteScalar(State, query);
                if(password != PasswordTextBox.Text)*/
                     err.Append("The " + username + " account already exists.<BR>");
            }
            if (address.Length> 0 && address.ToLower() != "[email protected]") //for every email not for testing
            {
                query = "SELECT email FROM customers WHERE email='" + address + "'";
                string email = db.ViziAppsExecuteScalar(State, query);
                if (email == this.EmailTextBox.Text)
                {
                    err.Append("An account already exists with the same email.<BR>");
                }
            }
        }
        if (!Check.ValidatePassword(Error, PasswordTextBox.Text))
        {
            err.Append("Enter Password: " +Error.Text.Clone() + "<BR>");
        }
        if (!Check.ValidateEmail(Error, EmailTextBox.Text))
        {
            err.Append(Error.Text.Clone() + "<BR>");
        }
        if (PasswordTextBox.Text != ConfirmPasswordBox.Text)
        {
            err.Append("The password and confirming password do not match. Try again.<BR>");
        }
        if (!Check.ValidateName(Error,FirstNameTextBox.Text))
        {
            err.Append("Enter First Name: " + Error.Text.Clone() + "<BR>");
        }
        if (!Check.ValidateName(Error, LastNameTextBox.Text))
        {
            err.Append("Enter Last Name: " + Error.Text.Clone() + "<BR>");
        }

        string phone = PhoneTextBox.Text.Trim ();
        if (PhoneTextBox.Text.Length > 0) //optional field
        {
            if (!Check.ValidatePhone(Error, PhoneTextBox.Text))
            {
                err.Append("Enter a valid phone number: " + Error.Text.Clone() + "<BR>");
            }
        }
        if (err.Length > 0)
        {
            MessageLabel.Text = "The following input(s) are required:<BR>" + err.ToString();
            db.CloseViziAppsDatabase(State);
            return;
        }
        try
        {

            string account_type = "type=viziapps;"; //set default for now
            string security_question = "";
            string security_answer = "";

            string customer_id = util.CreateMobiFlexAccount(State, username, PasswordTextBox.Text.Trim(), security_question, security_answer, FirstNameTextBox.Text.Trim(), LastNameTextBox.Text.Trim(),
                    EmailTextBox.Text.ToLower().Trim(), phone, account_type, ReferralSourceList.SelectedValue,AppToBuild.Text, "inactive");

            string email_template_path = Server.MapPath(".") + @"\templates\EmailValidation.txt";
            string url =   HttpRuntime.Cache["PublicViziAppsUrl"].ToString() + "/ValidateEmail.aspx?id=" + customer_id;
            string from =   HttpRuntime.Cache["TechSupportEmail"].ToString();
            string body = File.ReadAllText(email_template_path)
                    .Replace("[NAME]", FirstNameTextBox.Text.Trim())
                    .Replace("[LINK]",url)
                    .Replace("[SUPPORT]",from);

//.........这里部分代码省略.........

 protected void EmailUpgradeNotice_Click(object sender, EventArgs e)
 {
     Hashtable UsersList = (Hashtable)HttpRuntime.Cache["UsersList"];
     DB db = new DB();
     Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
     foreach (string username in UsersList.Keys)
     {
         string To = db.ViziAppsExecuteScalar(State, "SELECT email FROM customers WHERE username='" + username + "'");
         Email email = new Email();
         string From =   HttpRuntime.Cache["TechSupportEmail"].ToString();
         string Body = "The ViziApps Studio will be down in 1 minute for 5 minutes for an upgrade maintenance.\n\nSorry for the inconvenience.\n\n--ViziApps Support";
         string status = email.SendEmail(State, From, To, "", "", "ViziApps Studio Maintenance Notice", Body, "",false);
         if (status.IndexOf("OK") < 0)
         {
             Message.Text = "There was a problem sending the emails: " + status;
             db.CloseViziAppsDatabase(State);
             return;
         }
     }
     db.CloseViziAppsDatabase(State);
     Message.Text = "Maintenance notice has been emailed to " + UsersList.Keys.Count.ToString() + " current users";
 }

 public bool HasAgreedToEula(Hashtable State)
 {
     DB db = new DB();
     string sql = "SELECT agreed_to_eula FROM customers WHERE customer_id = '" + State["CustomerID"].ToString() + "'";
     string agreed_to_eula = db.ViziAppsExecuteScalar(State, sql);
     db.CloseViziAppsDatabase(State);
     return (agreed_to_eula.ToLower() == "false" || agreed_to_eula == "0") ? false : true;
 }

    public bool ActivateCustomerAccount(Hashtable State, string customer_id)
    {
        DB db = new DB();
        string sql = "SELECT COUNT(*) FROM customers WHERE customer_id='" + customer_id + "'";
        string count = db.ViziAppsExecuteScalar(State, sql);
        if (count == "0")
            return false;

        sql = "UPDATE customers SET status='trial' WHERE customer_id='" + customer_id + "'";
        db.ViziAppsExecuteNonQuery(State, sql);

        db.CloseViziAppsDatabase(State);
        return true;
    }

 public bool IsAppSelectedForTesting(Hashtable State)
 {
     DB db = new DB();
     string sql = "SELECT status FROM applications WHERE application_name='" + State["SelectedApp"].ToString() +
                 "' AND customer_id='" + State["CustomerID"].ToString() + "'";
     string status = db.ViziAppsExecuteScalar(State, sql);
     db.CloseViziAppsDatabase(State);
     return (status.Contains("staging")) ? true : false;
 }

//.........这里部分代码省略.........
                if (State["IsProductionAppPaid"] != null && State["IsProductionAppPaid"].ToString() != "true")
                {
                    //if (!util.IsFreeProductionValid(State, application_id))
                    if (State["IsFreeProductionValid"] != null && State["IsFreeProductionValid"].ToString() != "true")
                    {
                        SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: publishing service expired");
                        throw new System.InvalidOperationException("The publishing service for your app has expired.");
                    }
                }

                if (unlimited == null || unlimited != "true")
                {
                    //check username and password
                    // sql = "SELECT * FROM users WHERE username='" + user.ToLower() + "' AND password='" + util.MySqlFilter(password) +
                    //     "' AND application_id='" + application_id + "'";

                    //rows = db.ViziAppsExecuteSql(State, sql);
                    //if (rows.Length == 0)
                    if (State["Password"] == null)
                    {
                        //db.CloseViziAppsDatabase(State);
                        SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: bad credentials");
                        throw new System.InvalidOperationException("Either the username or the password: " + password + " is incorrect.");
                    }

                    //check number of users -- unlimited use never needs a login
                    //bool use_1_user_credential = util.GetUse1UserCredential(State, application_id);
                    //if (use_1_user_credential)
                    if (State["Use1UserCredential"] != null && State["Use1UserCredential"].ToString() == "true")
                    {
                        Hashtable features = util.IsProductionAppPaid(State, application_id);
                        DataRow row = rows[0];
                        sql = "SELECT COUNT(*) FROM users_device_ids WHERE user_id='" + row["user_id"].ToString() + "'";
                        int device_count = Convert.ToInt32(db.ViziAppsExecuteScalar(State, sql));

                        sql = "SELECT COUNT(*) FROM users_device_ids WHERE device_id='" + device_id + "'";
                        string device_exists = db.ViziAppsExecuteScalar(State, sql);

                        if (device_exists == "0")
                        {
                            if (device_count >= (int)features["max_users"])
                            {
                                db.CloseViziAppsDatabase(State);
                                SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: reached limit of users");
                                throw new System.InvalidOperationException("Cannot download app: reached limit of users.");
                            }
                            else
                            {
                                sql = "INSERT INTO users_device_ids SET device_id='" + device_id + "',user_id='" + row["user_id"].ToString() + "'";
                                db.ViziAppsExecuteNonQuery(State, sql);
                            }
                        }
                        //else app is allowed
                    }
                }
            }
            else //staging
            {
                sql = "SELECT * FROM customers WHERE username='" + user.ToLower() + "'";
                rows = db.ViziAppsExecuteSql(State, sql);
                if (rows.Length == 0)
                {
                    db.CloseViziAppsDatabase(State);
                    SaveReport(State, application_id, app_status, customer_id, user_id, device_id, device_model, device_version, viziapps_version, null, null, "app login: user not registered");
                    throw new Exception("The username " + user.ToLower() + " is not registered. Go to www.viziapps.com and create a free account.");
                }

    protected bool CheckAppName(string app)
    {
        try
        {
            ClearMessages();
            if (app.Length == 0)
            {
                Message.Text = "Enter Application Name";
                return false;
            }

            //check for valid name
            if (!Check.ValidateObjectName(Message, app))
            {
                return false;
            }

            //check for previous name
            DB db = new DB();
            Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
            string sql = "SELECT * FROM applications WHERE customer_id='" + State["CustomerID"] + "' AND application_name='" + app + "'";
            string n_matches = db.ViziAppsExecuteScalar(State, sql);
            db.CloseViziAppsDatabase(State);
            if (n_matches != null && n_matches != "0")
            {
                Message.Text = "The app name " + app + " already exists.";
                return false;
            }

            return true;
        }
        catch (Exception ex)
        {
            Util util = new Util();
            Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
            util.LogError(State, ex);
            Message.Text = "Internal Error: " + ex.Message + ": " + ex.StackTrace;
            return false;
        }
    }