using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SysCore;
using SysCore.Control;

public class CartDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Cart m)
    {
        string sql = @" declare @cart_id int;
                        select @cart_id=isnull(max(cart_id),0) from Sale_Cart where uid=@uid and i_id=@i_id
                          and isnull(free1,'')=isnull(@free1,'')
                          and isnull(free1,'')=isnull(@free2,'')
                        if @cart_id = 0 
                        begin
                            insert Sale_Cart(uid,i_id,free1,free2,quantity,num,tax_unit_price)
                            select @uid,@i_id,@free1,@free2,@quantity,@num,@tax_unit_price;
                        end
                        else
                        begin
                            update Sale_Cart set quantity=isnull(quantity,0)+@quantity,
                            tax_unit_price=@tax_unit_price where cart_id=@cart_id;
                        end";
        SqlParameter[] par = {
            new SqlParameter("@uid",XTools.IntToDBNull(m.uid)),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">cart_id</param>
    /// <returns>Cart</returns>
    public Cart Get(int cart_id)
    {
        Cart m = new Cart();
        string sql = @"select *
                        from Sale_Cart 
                        where cart_id=@cart_id";
        SqlParameter[] par = {
                new SqlParameter("@cart_id",cart_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.cart_id = Convert.ToInt32(r["cart_id"]);
                m.uid = Convert.ToInt32(r["uid"]);
                m.i_id = Convert.ToInt32(r["i_id"]);
                m.free1 = r["free1"].ToString();
                m.free2 = r["free2"].ToString();
                m.convert_rate = XTools.DBNullToDecimal(r["convert_rate"]);
                m.quantity = XTools.DBNullToDecimal(r["quantity"]);
                m.num = XTools.DBNullToDecimal(r["num"]);
                m.tax_unit_price = XTools.DBNullToDecimal(r["tax_unit_price"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Cart m)
    {
        string sql = @"update Sale_Cart set 
                        uid=@uid,
                        i_id=@i_id,
                        free1=@free1,
                        free2=@free2,
                        quantity=@quantity,
                        num=@num,
                        convert_rate=@convert_rate,
                        tax_unit_price=@tax_unit_price
                       where cart_id=@cart_id";
        SqlParameter[] par = {
            new SqlParameter("@cart_id",XTools.IntToDBNull(m.cart_id)),
            new SqlParameter("@uid",XTools.IntToDBNull(m.uid)),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到列表
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetCartListByUser(int uid)
    {
        string sql = @"select * from Sale_Cart where uid=@uid ";
        SqlParameter[] par = {
            new SqlParameter("@uid",XTools.IntToDBNull(uid))
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select ROW_NUMBER() OVER(@order_field) AS row_num,@sql_select_field 
                from(
                    select *
                    from Sale_Cart
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .Replace("@sql_select_field", sql_select_field);

        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = "select count(1) from Sale_Cart where 1=1 ";
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }
    /// <summary>
    /// 得到某用户购物车总数量
    /// </summary>
    /// <param name="用户id">uid</param>
    /// <returns>Cart</returns>
    public decimal GetCartNum(int uid)
    {
        string sql = @"select isnull(sum(quantity),0)
                        from Sale_Cart 
                        where uid=@uid";
        SqlParameter[] par = {
                new SqlParameter("@uid",uid)
            };
        return Convert.ToDecimal(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 删除购物车
    /// </summary>
    /// <param name="用户id">uid</param>
    public void Delete(int cart_id)
    {
        string sql = @"delete Sale_Cart 
                        where cart_id=@cart_id";
        SqlParameter[] par = {
                new SqlParameter("@cart_id",cart_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 清空某用户的购物车
    /// </summary>
    /// <param name="用户id">uid</param>
    public void DeleteCartByUser(int uid)
    {
        string sql = @"delete Sale_Cart 
                        where uid=@uid";
        SqlParameter[] par = {
                new SqlParameter("@uid",uid)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
}
