//
// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//

//  .Net StockTrader Sample WCF Application for Benchmarking, Performance Analysis and Design Considerations for Service-Oriented Applications


//===============================================================================================
// Customer is part of the SQLServer DAL for StockTrader.  This is called from the
// BSL to execute commands against the database.  It is constructed to use one SqlConnection per
// instance.  Hence, BSLs that use this DAL should always be instanced properly.
// The DAL will work with both ADO.NET and System.Transactions or ServiceComponents/Enterprise
// Services attributed transactions [autocomplete]. When using ADO.NET transactions,
// The BSL will control the transaction boundaries with calls to dal.BeginTransaction(); 
// dal.CommitTransaction(); dal.RollbackTransaction().
//===============================================================================================
//======================================================================================================
// Code originally contributed by Microsoft Corporation.
// This contribution to the Stonehenge project is limited strictly 
// to the source code that is submitted in this submission.  
// Any technology, including underlying platform technology, 
// that is referenced or required by the submitted source code 
// is not a part of the contribution.  
// For example and not by way of limitation, 
// any systems/Windows libraries (WPF, WCF, ASP.NET etc.) 
// required to run the submitted source code is not a part of the contribution
//======================================================================================================


using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using Trade.IDAL;
using Trade.Utility;
using Trade.ConfigServiceDataContract;
using Trade.BusinessServiceDataContract;


namespace Trade.DALMySQL
{
    public class Customer : ICustomer
    {
        public Customer()
        {
        }

        //Constructor for internal DAL-DAL calls to use an existing DB connection.
        public Customer(MySqlConnection conn, MySqlTransaction trans)
        {
            _internalConnection = conn;
            _internalADOTransaction = trans;
        }

        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        //Used only when doing ADO.NET transactions.
        //This will be completely ignored when null, and not attached to a cmd object
        //In MySQLHelper unless it has been initialized explicitly in the BSL with a
        //dal.BeginADOTransaction().  See app config setting in web.config and 
        //Trade.BusinessServiceHost.exe.config "Use System.Transactions Globally" which determines
        //whether user wants to run with ADO transactions or System.Transactions.  The DAL itself
        //is built to be completely agnostic and will work with either.
        public void BeginADOTransaction()
        {
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }

        //Used only when doing ADO.NET transactions.
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }

        //Used only when doing ADO.NET transactions.
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }

        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }

        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != ConnectionState.Closed)
                _internalConnection.Close();
        }

        //This would be the more efficient way to get holding data with quote price part of the model class/mapping, but do not use to maintain compatibility with Trade 6.1.  While
        //this query is easy with ADO.NET (or JDBC); it would be very problematic with EJB Entity Beans to do efficiently.  
        //private const string SQL_SELECT_HOLDINGS = "SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID, " +
        //                                           "QUOTE.PRICE FROM ACCOUNT INNER JOIN HOLDING ON ACCOUNT.ACCOUNTID = HOLDING.ACCOUNT_ACCOUNTID INNER JOIN " +
        //                                           "QUOTE ON HOLDING.QUOTE_SYMBOL = QUOTE.SYMBOL WHERE (ACCOUNT.PROFILE_USERID = @UserId) ORDER BY HOLDING.HOLDINGID DESC";

        private const string SQL_SELECT_HOLDINGS = @"SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID 
                                                    from holding WHERE HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM ACCOUNT WHERE PROFILE_USERID = ?UserId) ORDER BY HOLDING.HOLDINGID DESC";
        private const string SQL_SELECT_HOLDING_LOCK = @"SELECT HOLDING.HOLDINGID, HOLDING.ACCOUNT_ACCOUNTID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE,
                                                       HOLDING.QUOTE_SYMBOL FROM HOLDING  INNER JOIN ORDERS ON HOLDING.HOLDINGID = ORDERS.HOLDING_HOLDINGID
                                                       WHERE (ORDERS.ORDERID = ?OrderId)";
        private const string SQL_SELECT_HOLDING_NOLOCK = "SELECT HOLDING.ACCOUNT_ACCOUNTID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL FROM HOLDING WHERE HOLDING.HOLDINGID=?holdingId AND HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM ACCOUNT WHERE PROFILE_USERID = ?UserId)";
        private const string SQL_SELECT_GET_CUSTOMER_BYUSERID = "SELECT account.ACCOUNTID, account.PROFILE_USERID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM account WHERE account.PROFILE_USERID = ?UserId";
        private const string SQL_SELECT_CUSTOMERPROFILE_BYUSERID = "SELECT accountprofile.USERID, accountprofile.PASSWORD, accountprofile.FULLNAME, accountprofile.ADDRESS, accountprofile.EMAIL, accountprofile.CREDITCARD FROM accountprofile WHERE accountprofile.USERID = ?UserId";
        private const string SQL_SELECT_UPDATE_CUSTOMER_LOGIN = "UPDATE account  SET LOGINCOUNT = (LOGINCOUNT + 1), LASTLOGIN = CURRENT_TIMESTAMP where PROFILE_USERID= ?UserId; SELECT account.ACCOUNTID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM account  WHERE account.PROFILE_USERID = ?UserId";
        private const string SQL_UPDATE_LOGOUT = "UPDATE account  SET LOGOUTCOUNT = (LOGOUTCOUNT + 1) where PROFILE_USERID= ?UserId";
        private const string SQL_UPDATE_ACCOUNTPROFILE = "UPDATE accountprofile  SET ADDRESS=?Address, PASSWORD=?Password, EMAIL=?Email, CREDITCARD = ?CreditCard, FULLNAME=?FullName WHERE USERID= ?UserId";
        private const string SQL_SELECT_CLOSED_ORDERS = "SELECT ORDERID, ORDERTYPE, ORDERSTATUS, COMPLETIONDATE, OPENDATE, QUANTITY, PRICE, ORDERFEE, QUOTE_SYMBOL FROM orders WHERE ACCOUNT_ACCOUNTID = (select accountid from account where profile_userid =?UserId) AND ORDERSTATUS = 'closed'";
        private const string SQL_UPDATE_CLOSED_ORDERS = "UPDATE orders SET ORDERSTATUS = 'completed' where ORDERSTATUS = 'closed' AND ACCOUNT_ACCOUNTID = (select accountid from account where profile_userid =?UserId)";
        private const string SQL_SELECT_ORDERS_BY_ID = " o.ORDERID, o.ORDERTYPE, o.ORDERSTATUS, o.OPENDATE, o.COMPLETIONDATE, o.QUANTITY, o.PRICE, o.ORDERFEE, o.QUOTE_SYMBOL from orders o where o.account_accountid = (select a.accountid from account a where a.profile_userid = ?UserId) ORDER BY o.ORDERID DESC";
        private const string SQL_INSERT_ACCOUNTPROFILE = "INSERT INTO accountprofile VALUES (?Address, ?Password, ?UserId, ?Email, ?CreditCard, ?FullName)";
        private const string SQL_INSERT_ACCOUNT = "INSERT INTO account (CREATIONDATE, OPENBALANCE, LOGOUTCOUNT, BALANCE, LASTLOGIN, LOGINCOUNT, PROFILE_USERID) VALUES (NOW(), ?OpenBalance, ?LogoutCount, ?Balance, ?LastLogin, ?LoginCount, ?UserId); SELECT LAST_INSERT_ID()";
        private const string SQL_DEBIT_ACCOUNT = "UPDATE ACCOUNT  SET BALANCE=(BALANCE-?Debit) WHERE ACCOUNTID=?AccountId";

        //Parameters
        private const string PARM_USERID = "?UserId";
        private const string PARM_HOLDINGID = "?holdingId";
        private const string PARM_ORDERID = "?OrderId";
        private const string PARM_ACCOUNTID = "?accountId";
        private const string PARM_PASSWORD = "?Password";
        private const string PARM_FULLNAME = "?FullName";
        private const string PARM_ADDRESS = "?Address";
        private const string PARM_EMAIL = "?Email";
        private const string PARM_CREDITCARD = "?CreditCard";
        private const string PARM_OPENBALANCE = "?OpenBalance";
        private const string PARM_LOGOUTCOUNT = "?LogoutCount";
        private const string PARM_BALANCE = "?Balance";
        private const string PARM_LASTLOGIN = "?LastLogin";
        private const string PARM_LOGINCOUNT = "?LoginCount";
        private const string PARM_TOPORDERS = "?TopOrders";
        private const string PARM_DEBIT = "?Debit";


        public List<OrderDataModel> getOrders(string userID, bool top, int maxTop, int maxDefault)
        {

            //Here you can configure between two settings: top default to display
            //is MAX_DISPLAY_ORDERS; the upper limit is MAX_DISPLAY_TOP_ORDERS.
            //Set these in Web.Config/Trade.BusinessServiceHost.exe.config; and those will be the toggle 
            //choices for the user in the Account.aspx page.
            try
            {
                string commandText;
                if (top)
                {
                    commandText = "Select " + SQL_SELECT_ORDERS_BY_ID + " LIMIT 0," + maxTop.ToString();
                }
                else
                {
                    commandText = "Select " + SQL_SELECT_ORDERS_BY_ID + " LIMIT 0," + maxDefault.ToString();
                }
                MySqlParameter accountidparm = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                accountidparm.Value = userID;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, commandText, accountidparm);
                List<OrderDataModel> orders = new List<OrderDataModel>();
                while (rdr.Read())
                {
                    int orderid = rdr.GetInt32(0);
                    DateTime openDate = (DateTime)rdr.GetDateTime(3);
                    Object completionDate = null;
                    //can be null
                    try
                    {
                        if (!Convert.IsDBNull(rdr.GetDateTime(4)))
                            completionDate = rdr.GetDateTime(4);
                        else
                            completionDate = DateTime.MinValue;
                    }
                    catch (Exception e)
                    {
                        string message = e.Message;
                        completionDate = DateTime.MinValue;
                    }
                    OrderDataModel order = new OrderDataModel(orderid, rdr.GetString(1), rdr.GetString(2), openDate, (DateTime)completionDate, rdr.GetDouble(5), rdr.GetDecimal(6), rdr.GetDecimal(7), rdr.GetString(8));
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }
        }

        public AccountDataModel login(string userid, string password)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                parm1.Value = userid;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CUSTOMERPROFILE_BYUSERID, parm1);
                if (rdr.Read())
                {
                    string userPassword = rdr.GetString(1);
                    rdr.Close();
                    if (userPassword.Equals(password))
                    {
                        MySqlParameter profileparm1 = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                        profileparm1.Value = userid;
                        rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_UPDATE_CUSTOMER_LOGIN, profileparm1);
                        rdr.Read();
                        AccountDataModel customer = new AccountDataModel(rdr.GetInt32(0), userid, rdr.GetDateTime(1), rdr.GetDecimal(2), rdr.GetInt32(3), rdr.GetDecimal(4), rdr.GetDateTime(5), rdr.GetInt32(6) + 1);
                        rdr.Close();
                        return customer;
                    }
                    rdr.Close();
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        public AccountProfileDataModel getAccountProfileData(string userid)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                parm1.Value = userid;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CUSTOMERPROFILE_BYUSERID, parm1);
                if (rdr.Read())
                {
                    AccountProfileDataModel customerprofile = new AccountProfileDataModel(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
                    rdr.Close();
                    return customerprofile;
                }
                rdr.Close();
                return null;
            }
            catch
            {
                throw;
            }
        }

        public void logOutUser(string userID)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                parm1.Value = userID;
                MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_LOGOUT, parm1);
                return;
            }
            catch
            {
                throw;
            }
        }

        public AccountDataModel getCustomerByUserID(string userID)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                parm1.Value = userID;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_GET_CUSTOMER_BYUSERID, parm1);
                if (rdr.Read())
                {
                    AccountDataModel customer = new AccountDataModel(rdr.GetInt32(0), rdr.GetString(1), rdr.GetDateTime(2), rdr.GetDecimal(3), rdr.GetInt32(4), rdr.GetDecimal(5), rdr.GetDateTime(6), rdr.GetInt32(7));
                    rdr.Close();
                    return customer;
                }
                rdr.Close();
                return null;
            }
            catch
            {
                throw;
            }
        }

        public List<OrderDataModel> getClosedOrders(string userId)
        {
            try
            {
                MySqlParameter useridparm = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                useridparm.Value = userId;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CLOSED_ORDERS, useridparm);
                List<OrderDataModel> closedorders = new List<OrderDataModel>();
                DateTime completionDate = DateTime.MinValue;
                while (rdr.Read())
                {
                    int orderid = rdr.GetInt32(0);
                    DateTime openDate = (DateTime)rdr.GetDateTime(4);
                    try
                    {
                        completionDate = (DateTime)rdr.GetDateTime(3);
                    }
                    catch (Exception e) { if (e.Message.Equals("Data is Null. This method or property cannot be called on Null values.")) completionDate = DateTime.MinValue; }
                    OrderDataModel order = new OrderDataModel(orderid, rdr.GetString(1), rdr.GetString(2), openDate, completionDate, rdr.GetDouble(5), rdr.GetDecimal(6), rdr.GetDecimal(7), rdr.GetString(8));
                    order.orderStatus = StockTraderUtility.ORDER_STATUS_COMPLETED;
                    closedorders.Add(order);
                }
                if (rdr.HasRows)
                {
                    rdr.Close();
                    useridparm = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                    useridparm.Value = userId;
                    MySQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_CLOSED_ORDERS, useridparm);
                }
                else
                    rdr.Close();
                return closedorders;
            }
            catch
            {
                throw;
            }
        }

        public List<HoldingDataModel> getHoldings(string userID)
        {
            try
            {
                MySqlParameter useridparm = new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20);
                useridparm.Value = userID;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDINGS, useridparm);
                List<HoldingDataModel> holdings = new List<HoldingDataModel>();
                while (rdr.Read())
                {
                    HoldingDataModel holding = new HoldingDataModel(rdr.GetInt32(0), rdr.GetDouble(1), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4), rdr.GetInt32(5));
                    holdings.Add(holding);
                }
                rdr.Close();
                return holdings;
            }
            catch
            {
                throw;
            }
        }

        public HoldingDataModel getHoldingForUpdate(int orderID)
        {
            try
            {
                MySqlParameter orderIDparm = new MySqlParameter(PARM_ORDERID, MySqlDbType.Int32);
                orderIDparm.Value = orderID;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING_LOCK, orderIDparm);
                while (rdr.Read())
                {
                    HoldingDataModel holding = new HoldingDataModel(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetDouble(2), rdr.GetDecimal(3), rdr.GetDateTime(4), rdr.GetString(5));
                    rdr.Close();
                    return holding;
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        public HoldingDataModel getHolding(string userid, int holdingid)
        {
            try
            {
                MySqlParameter[] holdingidparms = new MySqlParameter[]{new MySqlParameter(PARM_HOLDINGID, MySqlDbType.Int32), 
                                                new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20)};
                holdingidparms[0].Value = holdingid;
                holdingidparms[1].Value = userid;
                MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING_NOLOCK, holdingidparms);
                while (rdr.Read())
                {
                    HoldingDataModel holding = new HoldingDataModel(holdingid, rdr.GetInt32(0), rdr.GetDouble(1), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4));
                    rdr.Close();
                    return holding;
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        public void updateAccountBalance(int accountID, decimal total)
        {
            try
            {
                // Get the parameters from the cache
                MySqlParameter[] accountParms = GetUpdateAccountBalanceParameters();
                accountParms[0].Value = total;
                accountParms[1].Value = accountID;
                MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DEBIT_ACCOUNT, accountParms);
            }
            catch
            {
                throw;
            }
            return;
        }

        public void insertAccount(AccountDataModel customer)
        {
            // Get the parameters from the cache
            MySqlParameter[] AccountParms = GetCreateAccountParameters();
            try
            {
                AccountParms[0].Value = customer.openBalance;
                AccountParms[1].Value = customer.logoutCount;
                AccountParms[2].Value = customer.balance;
                AccountParms[3].Value = customer.lastLogin;
                AccountParms[4].Value = customer.loginCount;
                AccountParms[5].Value = customer.profileID;
                customer.accountID = Convert.ToInt32(MySQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INSERT_ACCOUNT, AccountParms));
                return;
            }
            catch
            {
                throw;
            }
        }

        public void insertAccountProfile(AccountProfileDataModel customerprofile)
        {
            // Get the paramters from the cache
            MySqlParameter[] ProfileParms = GetCreateAccountProfileParameters();
            try
            {
                ProfileParms[0].Value = customerprofile.address;
                ProfileParms[1].Value = customerprofile.password;
                ProfileParms[2].Value = customerprofile.userID;
                ProfileParms[3].Value = customerprofile.email;
                ProfileParms[4].Value = customerprofile.creditCard;
                ProfileParms[5].Value = customerprofile.fullName;
                 MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INSERT_ACCOUNTPROFILE, ProfileParms);
            }
            catch
            {
                throw;
            }
        }

        public AccountProfileDataModel update(AccountProfileDataModel customerprofile)
        {
            try
            {
                // Get the paramters from the cache
                MySqlParameter[] ProfileParms = GetUpdateAccountProfileParameters();
                ProfileParms[0].Value = customerprofile.address;
                ProfileParms[1].Value = customerprofile.password;
                ProfileParms[2].Value = customerprofile.email;
                ProfileParms[3].Value = customerprofile.creditCard;
                ProfileParms[4].Value = customerprofile.fullName;
                ProfileParms[5].Value = customerprofile.userID;
                 MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_ACCOUNTPROFILE, ProfileParms);
                return customerprofile;
            }
            catch
            {
                throw;
            }
        }

        public String getProfileIdFromStsIdentifier(string uniqueIdentifier, string issuingSts)
        {
            return null;
        }

        private static MySqlParameter[] GetUpdateAccountBalanceParameters()
        {
            // Get the paramters from the cache
            MySqlParameter[] parms = MySQLHelper.GetCacheParameters(SQL_DEBIT_ACCOUNT);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {new MySqlParameter(PARM_DEBIT, MySqlDbType.Decimal, 14),
                                            new MySqlParameter(PARM_ACCOUNTID, MySqlDbType.Int32)};
                // Add the parameters to the cached
                MySQLHelper.CacheParameters(SQL_DEBIT_ACCOUNT, parms);
            }
            return parms;
        }

        private static MySqlParameter[] GetCreateAccountProfileParameters()
        {
            // Get the parameters from the cache
            MySqlParameter[] parms = MySQLHelper.GetCacheParameters(SQL_INSERT_ACCOUNTPROFILE);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {
        			  new MySqlParameter(PARM_ADDRESS, MySqlDbType.VarChar, StockTraderUtility.ADDRESS_MAX_LENGTH),
		              new MySqlParameter(PARM_PASSWORD, MySqlDbType.VarChar, StockTraderUtility.PASSWORD_MAX_LENGTH),
                      new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, StockTraderUtility.USERID_MAX_LENGTH),
                      new MySqlParameter(PARM_EMAIL, MySqlDbType.VarChar, StockTraderUtility.EMAIL_MAX_LENGTH),
                      new MySqlParameter(PARM_CREDITCARD, MySqlDbType.VarChar, StockTraderUtility.CREDITCARD_MAX_LENGTH),
                      new MySqlParameter(PARM_FULLNAME, MySqlDbType.VarChar, StockTraderUtility.FULLNAME_MAX_LENGTH)};

                // Add the parametes to the cached
                MySQLHelper.CacheParameters(SQL_INSERT_ACCOUNTPROFILE, parms);
            }
            return parms;
        }

        private static MySqlParameter[] GetUpdateAccountProfileParameters()
        {
            // Get the parameters from the cache
            MySqlParameter[] parms = MySQLHelper.GetCacheParameters(SQL_UPDATE_ACCOUNTPROFILE);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {
            		  new MySqlParameter(PARM_ADDRESS, MySqlDbType.VarChar, StockTraderUtility.ADDRESS_MAX_LENGTH),
					  new MySqlParameter(PARM_PASSWORD, MySqlDbType.VarChar, StockTraderUtility.PASSWORD_MAX_LENGTH),
                      new MySqlParameter(PARM_EMAIL, MySqlDbType.VarChar, StockTraderUtility.EMAIL_MAX_LENGTH),
                      new MySqlParameter(PARM_CREDITCARD, MySqlDbType.VarChar, StockTraderUtility.CREDITCARD_MAX_LENGTH),
                      new MySqlParameter(PARM_FULLNAME, MySqlDbType.VarChar, StockTraderUtility.FULLNAME_MAX_LENGTH),
                      new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, StockTraderUtility.USERID_MAX_LENGTH)};

                // Add the parametes to the cached
                MySQLHelper.CacheParameters(SQL_UPDATE_ACCOUNTPROFILE, parms);
            }
            return parms;
        }

        private static MySqlParameter[] GetCreateAccountParameters()
        {
            // Get the parameters from the cache
            MySqlParameter[] parms = MySQLHelper.GetCacheParameters(SQL_INSERT_ACCOUNT);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {
					  new MySqlParameter(PARM_OPENBALANCE, MySqlDbType.Decimal),
                      new MySqlParameter(PARM_LOGOUTCOUNT, MySqlDbType.Int32),
                      new MySqlParameter(PARM_BALANCE, MySqlDbType.Decimal),
                      new MySqlParameter(PARM_LASTLOGIN, MySqlDbType.DateTime),
                      new MySqlParameter(PARM_LOGINCOUNT, MySqlDbType.Int32),
                      new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, StockTraderUtility.USERID_MAX_LENGTH)};

                // Add the parameters to the cached
                MySQLHelper.CacheParameters(SQL_INSERT_ACCOUNT, parms);
            }
            return parms;
        }

    }
}