中国工商网.电子商务购物中心系统.EMall

///中国工商网.电子商务购物中心系统.EMall
///演示地址：http://emall.12986.com

///中国工商网 - http://www.12986.com///Copyright (c) 1998-2005
///作者： Dili J.F. Senders

///技术支持：如果任何技术问题，请登录中国工商网技术支持网站咨询：http://cs.12986.com/

using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace EMall
{
    ///
    /// Pub 的摘要说明。
    ///
    public class Pub
    {
        // 数据库公用变量
        public SqlConnection SqlConnection1;
        public SqlCommand SqlCommand1;
        public SqlDataAdapter SqlDataAdapter1;
        public SqlDataAdapter SqlDataAdapter2;
        public SqlDataReader SqlDataReader1;
        public SqlCommandBuilder SqlCommandBuilder1;
        public DataSet DataSet1;
        public DataTable DataTable1;
        public DataRow DataRow1;

        // 非数据库公用变量
        public string LoginName;        // 登录用户
        public string SiteName = System.Configuration.ConfigurationSettings.AppSettings["SiteName"];            // 站点名称
        public string SiteNav;            // 站点导航
        public string sTitle = System.Configuration.ConfigurationSettings.AppSettings["SiteName"];            // 站点标题

        // 页面导航
        public int PageSize = 5;        // 每页显示记录数
        public int RecordCount, PageCount, CurrentPage;

        public Pub()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        // 0.——————————————————通用操作——————————————————————

        public void NavInfo(string sMsg, string sMsgURL, string sMsgTarget)
        {
            sTitle = sTitle + " -- " + sMsg;
            SiteNav = "" + SiteName +"";
        }


        // 1.——————————————————数据库操作——————————————————————

        ///
        /// 连接打开
        ///
        public void ConnOpen()
        {  
            SqlConnection1 = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Conn"]);
            SqlConnection1.Open();
        }

        ///
        /// 连接关闭(之前清除DateSet1中的数据)
        ///
        public void ConnClose()
        {
            if (DataSet1 != null)
            {
                DataSet1.Clear();
            }
            if (SqlConnection1 != null)
            {
                SqlConnection1.Close();
            }
        }

        ///
        /// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
        ///
        /// 打开表Sql语句
        public void Fill(string sSQL)
        {     
            ConnOpen();
            SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
            DataSet1 = new DataSet();
            SqlDataAdapter1.Fill(DataSet1);   
        }

        ///
        /// 引用存储过程构造DataSet
        ///
        /// 要引用的存储过程
        public void FillDataSet(string sp)
        {     
            ConnOpen();
            SqlDataAdapter1 = new SqlDataAdapter(sp, SqlConnection1);
            DataSet1 = new DataSet();
            SqlDataAdapter1.Fill(DataSet1);
        }

        ///
        /// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
        ///
        /// 用于表映谢的源表的名称
        /// 打开表Sql语句
        public void Fill(string TableName,string sql)
        { 
            ConnOpen();
            SqlDataAdapter1 = new SqlDataAdapter(sql, SqlConnection1);
            DataSet1 = new DataSet();
            SqlDataAdapter1.Fill(DataSet1, TableName);
        }

        ///
        /// 给DataSet对象增加映谢表
        ///
        /// SQL语句
        public void FillAdd(string sSQL)
        {
            SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
            SqlDataAdapter1.Fill(DataSet1);
        }

        ///
        /// 给DataSet对象增加映谢表
        ///
        /// SQL语句
        public void FillAdd(string TableName, string sSQL)
        {
            SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
            SqlDataAdapter1.Fill(DataSet1, TableName);
        }

        ///
        /// 获取最大值
        ///
        ///
        ///
        ///
        public string GetMaxId(string fID, string TableName)
        {
            string sKey;
            string sSQL="SELECT TOP 1 " + fID + " FROM " + TableName + " ORDER BY " + fID + " DESC";
            if (GetRowCount(sSQL) == 0)
            {
                sKey="1";
            }
            else
            {
                GetRowRecord(sSQL);
                sKey = (int.Parse(DataRow1[fID].ToString()) + 1).ToString(); // 获得数据库表key值
            }
            return sKey;
        }

        ///
        /// 获得包含在DataSet对象的映谢表集合中的index为0的映谢表
        ///
        /// SQL语句
        public void GetTable(string sSQL)
        {
            Fill(sSQL);
            DataTable1 = DataSet1.Tables[0];
        }

        ///
        /// 获得符合该Sql语句的表记录数
        ///
        /// SQL语句
        /// 返回表记录条数
        public int GetRowCount(string sSQL)
        {
            Fill(sSQL);
            try
            {
                int Count = DataSet1.Tables[0].Rows.Count;
                ConnClose();
                return Count;
            }
            catch
            {
                ConnClose();
                return 0;
            }
        }

        ///
        /// 通过传Sql语句关键key值获得表中一行的数据
        ///
        /// 带关键Key值参数的SQL语句
        public void GetRowRecord(string sSQL)
        {
            Fill(sSQL);
            DataRow1 = DataSet1.Tables[0].Rows[0];
            ConnClose();
        }

        ///
        /// 执行Transact-SQL语句,对数据库记录做插入,修改,删除等操作
        ///
        /// Transact-SQL语句
        public void ExeSql(string sSQL)
        {  
            ConnOpen();
            SqlCommand1 = new SqlCommand(sSQL, SqlConnection1);
            SqlCommand1.ExecuteNonQuery();
            SqlCommand1.Dispose();
        }

        // 2.——————————————————绑定控件——————————————————————
       
        ///
        /// 绑定DataGrid控件并显示数据
        ///
        /// Sql语句
        /// DataGrid控件id值
        public void BindDataGrid(string sSQL, DataGrid DataGrid1)
        {
            Fill(sSQL);
            DataGrid1.DataSource = DataSet1.Tables[0].DefaultView;
            DataGrid1.DataBind();
        }

        ///
        /// 绑定DataList控件并显示数据
        ///
        /// Sql语句
        /// DataList控件ID值
        public void BindDataList(string sSQL, DataList DataList1)
        {
            Fill(sSQL);
            DataList1.DataSource = DataSet1.Tables[0].DefaultView;
            DataList1.DataBind();
        }

        ///
        /// 绑定Repeater控件并显示数
        ///
        /// Sql语句
        /// Repeater控件ID值
        public void BindRepeater(string sSQL,Repeater Repeater1)
        {
            Fill(sSQL);
            Repeater1.DataSource = DataSet1.Tables[0].DefaultView;
            Repeater1.DataBind();
        }

        // 3.——————————————————操作业务——————————————————————

        ///
        /// 返回当前用户的购物车ID
        ///
        /// 返回当前用户的购物车ID
        public String GetShoppingCartID()
        {
            System.Web.HttpContext Context = System.Web.HttpContext.Current;
            // 如果该用户已经通过验证后登录了系统, 那么以该用户的 CustomerId作为购物车ID
            if (Context.User.Identity.Name != "")
            {
                return Context.User.Identity.Name;
            }
            // 否则, 如果浏览器Cookie中已经分配给该用户一个购物车ID
            if (Context.Request.Cookies["ShoppingCartID"] != null)
            {
                return Context.Request.Cookies["ShoppingCartID"].Value;
            }
            else
            {
                // 利用System.Guid Class产生一个随机GUID(全球唯一标识号)
                Guid TempCartId = Guid.NewGuid();
                // 回写到客户端指定的cookie中
                Context.Response.Cookies["ShoppingCartID"].Value = TempCartId.ToString();
                // 同时返回 TempCartId
                return TempCartId.ToString();
            }
        }

        ///
        /// 显示记录列表.最新消息
        ///
        ///
        public SqlDataReader spNewsList()
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spNewsList", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;
            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }

        ///
        /// 用户登录
        ///
        /// 登录时的电子邮件
        /// 相应的密码
        ///
        public String spCustomersLogin(string sEmail, string sPassword)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spCustomersLogin", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@Email", SqlDbType.VarChar, 100);
            SqlParameter Param2 = new SqlParameter("@Password", SqlDbType.VarChar, 50);
            SqlParameter Param3 = new SqlParameter("@ID", SqlDbType.Int, 4);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            SqlCommand1.Parameters.Add(Param3);
            Param1.Value = sEmail;
            Param2.Value = sPassword;
            Param3.Direction = ParameterDirection.Output;

            SqlCommand1.ExecuteNonQuery();
            int ID = (int)(Param3.Value);
            if (ID == 0)
                return null;
            else
                return ID.ToString();
        }

        ///
        /// 更新密码
        ///
        /// 登录时的电子邮件
        /// 相应的密码
        /// 新的密码
        ///
        public String spCustomersUpdatePassword(string sEmail, string sPassword, string sPasswordNew)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spCustomersUpdatePassword", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@Email", SqlDbType.VarChar, 100);
            SqlParameter Param2 = new SqlParameter("@PasswordOld", SqlDbType.VarChar, 50);
            SqlParameter Param3 = new SqlParameter("@PasswordNew", SqlDbType.VarChar, 50);
            SqlParameter Param4 = new SqlParameter("@Result", SqlDbType.VarChar, 100);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            SqlCommand1.Parameters.Add(Param3);
            SqlCommand1.Parameters.Add(Param4);
            Param1.Value = sEmail;
            Param2.Value = sPassword;
            Param3.Value = sPasswordNew;
            Param4.Direction = ParameterDirection.Output;

            SqlCommand1.ExecuteNonQuery();
            return Param4.Value.ToString();
        }

        ///
        /// 获取商品列表
        ///
        /// 查询字段值, 如货号或者品名等/按商品分类值
        /// true/false: 按指定查询字段值/按商品分类
        ///
        public SqlDataReader spProductsSearch(string sStr, bool T)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spProductsSearch", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Para1 = new SqlParameter("@Search", SqlDbType.VarChar, 255);
            SqlParameter Para2 = new SqlParameter("@SearchType", SqlDbType.Bit, 1);
            SqlCommand1.Parameters.Add(Para1);
            SqlCommand1.Parameters.Add(Para2);
            Para1.Value = sStr;
            Para2.Value = T;

            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }
       
        ///
        /// 返回最TOP的几个商品列表
        ///
        /// 限定记录条数
        /// 指定排列字段
        /// 附加有指定商品类别
        /// 返回最TOP的几个商品列表
        public SqlDataReader spProductsTop(string Top, string OrderBy, string CategoryID)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spProductsTop", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
            SqlParameter Para2 = new SqlParameter("@OrderBy", SqlDbType.VarChar, 50);
            SqlParameter Para3 = new SqlParameter("@CategoryID", SqlDbType.VarChar, 25);
            SqlCommand1.Parameters.Add(Para1);
            SqlCommand1.Parameters.Add(Para2);
            SqlCommand1.Parameters.Add(Para3);
            Para1.Value = Top;
            Para2.Value = OrderBy;
            Para3.Value = CategoryID;

            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }

        ///
        /// SqlDataAdapter方式的存储过程.列表.商品.所有.指定OrderBy
        ///
        /// 指定OrderBy
        public void spProductsAllOrderBy(string OrderBy)
        {
            ConnOpen();
            SqlDataAdapter1 = new SqlDataAdapter("spProductsTop", SqlConnection1);
            SqlDataAdapter1.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
            SqlParameter Para2 = new SqlParameter("@OrderBy", SqlDbType.VarChar, 50);
            SqlParameter Para3 = new SqlParameter("@CategoryID", SqlDbType.VarChar, 25);
            SqlDataAdapter1.SelectCommand.Parameters.Add(Para1);
            SqlDataAdapter1.SelectCommand.Parameters.Add(Para2);
            SqlDataAdapter1.SelectCommand.Parameters.Add(Para3);
            Para1.Value = "100 PERCENT";
            Para2.Value = OrderBy;
            Para3.Value = "-100";

            DataSet1 = new DataSet();
            SqlDataAdapter1.Fill(DataSet1);
        }
       
        ///
        /// 返回最TOP的几个链接列表
        ///
        /// 限定记录条数
        /// 指定排列字段
        /// 返回最TOP的几个链接列表
        public SqlDataReader spCustomersTop(string sTop, string sORDERBY)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spCustomersTop", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
            SqlParameter Para2 = new SqlParameter("@ORDERBY", SqlDbType.VarChar, 50);
            SqlCommand1.Parameters.Add(Para1);
            SqlCommand1.Parameters.Add(Para2);
            Para1.Value = sTop;
            Para2.Value = sORDERBY;

            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }       
       
        ///
        /// 获取指定商品ID的明细
        ///
        /// 要查看的商品ID
        /// 返回明细
        public SqlDataReader spProductDetail(int ProductID)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spProductDetail", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Para1 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            SqlCommand1.Parameters.Add(Para1);
            Para1.Value = ProductID;

            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }

        ///
        /// 获取商品列表.按指定字段类型
        ///
        /// 字段类型
        ///
        public SqlDataReader GetProductsOrderBy(string sFieldName)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("GetProductsOrderBy", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@FieldName", SqlDbType.VarChar, 255);
            SqlCommand1.Parameters.Add(Param1);
            Param1.Value = sFieldName;

            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }

        ///
        /// 显示指定购物车ID的记录
        ///
        /// 购物车ID
        ///
        public SqlDataReader spShoppingCartList(string sCartID)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spShoppingCartList", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
            SqlCommand1.Parameters.Add(Param1);
            Param1.Value = sCartID;

            SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }

        ///
        /// 获取指定购物车ID的总金额
        ///
        /// 购物车ID
        /// 总金额
        public decimal spShoppingCartTotal(string sCartID)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spShoppingCartTotal", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
            SqlParameter Param2 = new SqlParameter("@TotalCost", SqlDbType.Money, 8);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            Param1.Value = sCartID;
            Param2.Direction = ParameterDirection.Output;

            SqlCommand1.ExecuteNonQuery();

            if (Param2.Value.ToString() != "")
            {
                return (decimal)Param2.Value;
            }
            else
            {
                return 0;
            }
        }

        ///
        /// 把当前商品加入指定的购物车里
        ///
        /// 购物车ID
        /// 商品ID
        /// 商品数量
        public void spShoppingCartAddItem(string sCartID, int iProductID, int iQuantity)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spShoppingCartAddItem", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            SqlParameter Param2 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
            SqlParameter Param3 = new SqlParameter("@Quantity", SqlDbType.Int, 4);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            SqlCommand1.Parameters.Add(Param3);
            Param1.Value = iProductID;
            Param2.Value = sCartID;
            Param3.Value = iQuantity;

            SqlCommand1.ExecuteNonQuery();
        }

        ///
        /// 购物车.更新数量
        ///
        /// 指定购物车ID
        /// 指定商品ID
        /// 要更新的数量
        public void spShoppingCartUpdateQty(string sCartID, int iProductID, int iQuantity)
        {
            if (iQuantity
        /// 购物车.删除所选项
        ///
        /// 指定购物车ID
        /// 指定商品ID
        public void spShoppingCartRemoveItem(string sCartID, int iProductID)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spShoppingCartRemoveItem", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
            SqlParameter Param2 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            Param1.Value = sCartID;
            Param2.Value = iProductID;

            SqlCommand1.ExecuteNonQuery();
        }

        ///
        /// 将购物车确认为订单
        ///
        /// 指定客户ID
        /// 指定购物车ID
        /// 加入备注说明
        /// 返回所生成的订单ID给客户
        public int spOrdersInsert(int iCustomerID, string sCartID, string sMemo)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spOrdersInsert", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
            SqlParameter Param2 = new SqlParameter("@CustomerID", SqlDbType.Int, 4);
            SqlParameter Param3 = new SqlParameter("@Memo", SqlDbType.VarChar, 2000);
            SqlParameter Param4 = new SqlParameter("@OrderID", SqlDbType.Int, 4);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            SqlCommand1.Parameters.Add(Param3);
            SqlCommand1.Parameters.Add(Param4);
            Param1.Value = sCartID;
            Param2.Value = iCustomerID;
            Param3.Value = sMemo;
            Param4.Direction = ParameterDirection.Output;

            SqlCommand1.ExecuteNonQuery();

            return (int)Param4.Value;
        }

        ///
        /// 作用: 从一个购物车中转到另一个购物车中
        /// 情形: 应用于在登录或者注册过程完成后, 将临时购物车转到该用户的购物车中.
        ///
        /// 原来的购物车ID
        /// 现在的购物车ID
        public void spShoppingCartMigrate(String sOldCartId, String sNewCartId)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("spShoppingCartMigrate", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@OldCartId ", SqlDbType.VarChar, 50);
            SqlParameter Param2 = new SqlParameter("@NewCartId ", SqlDbType.VarChar, 50);
            SqlCommand1.Parameters.Add(Param1);
            SqlCommand1.Parameters.Add(Param2);
            Param1.Value = sOldCartId;
            Param2.Value = sNewCartId;

            SqlCommand1.ExecuteNonQuery();
        }

        ///
        /// 清空购物车
        ///
        ///
        public void EmptyCart(string sCartID)
        {
            ConnOpen();
            SqlCommand1 = new SqlCommand("ShoppingCartEmpty", SqlConnection1);
            SqlCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
            SqlCommand1.Parameters.Add(Param1);
            Param1.Value = sCartID;

            SqlCommand1.ExecuteNonQuery();
        }

        // 4.-----------------------其他实用过程/函数---------------------------
        ///
        /// 检查今天昨天前天及以前的天
        ///
        /// 要检查的表
        /// 返回检查结果
        ///
        public string CheckDate(DateTime dt)
        {
            string sStr;
            DateTime Today;
            Today = DateTime.Today;
            if( dt.Date == Today )
                sStr = "今天";
            else if( dt.AddDays(1).Date == Today )
                sStr = "昨天";
            else if( dt.AddDays(2).Date == Today )
                sStr = "前天";
            else sStr = dt.Year.ToString() + "年" + dt.Month.ToString() + "月" + dt.Day.ToString() + "日";
            sStr += " " + dt.Hour.ToString() + ":" + dt.Minute.ToString();
            return sStr;
        }

        ///
        /// 截取指定长度的子字符串
        ///
        /// 需要截取的字符串
        /// 目标长度
        /// 返回截取后的字符串
        ///
        public string GetSubString(string sStr, int Len)
        {
            if (sStr.Length > Len)
            {
                return sStr.Substring(0, Len) + "...";
            }
            else
            {
                return sStr;
            }
        }
    }
}