using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///商品详细信息操作
/// </summary>
public class ProductClass
{
	public ProductClass()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}
    DataSet ds = new DataSet();
    SqlConnection con = ConSql.ConnectionSql();

    /// <summary>
    /// 查询全部商品信息product
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet ProductAll()
    {
        SqlDataAdapter sdr = new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand();   //创建数据适配集的SelectCommand对象
        sdr.SelectCommand.CommandText = "select * from product order by productId desc";
        sdr.SelectCommand.Connection = con; //直接调用数据库连接对象
        sdr.Fill(ds, "pruductAll");  //填充数据集ds，表名为pruductAll
        return ds;  //返回数据集
    }

    /// <summary>
    /// 按类别查询商品信息
    /// </summary>
    /// <param name="categoryId">类别Id</param>
    /// <returns>DataSet</returns>
    public DataSet ProductByCid(int categoryId)
    {
       
        SqlDataAdapter sdr = new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand();   //创建数据适配集的SelectCommand对象
        sdr.SelectCommand.CommandText = "select * from product where CategoryId=@CategoryId order by productId desc";
        sdr.SelectCommand.Connection = con; //直接调用数据库连接对象
        sdr.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryId;
        sdr.Fill(ds, "pruductByCid");  //填充数据集ds，表名为pruductByCid
        return ds;  //返回数据集
    }

  /// <summary>
  /// 按商品Id查询商品信息
  /// </summary>
  /// <param name="productId">商品ID</param>
    /// <returns>DataSet，表名为pruductByPid</returns>
    public DataSet ProductByProductId(int productId)
    {
        SqlDataAdapter sdr = new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand();   //创建数据适配集的SelectCommand对象
        sdr.SelectCommand.CommandText = "select * from product where productId=@productId";
        sdr.SelectCommand.Connection = con; //直接调用数据库连接对象
        sdr.SelectCommand.Parameters.Add("@productId", SqlDbType.Int).Value = productId;
        sdr.Fill(ds, "pruductByPid");  //填充数据集ds，表名为pruductByPid
        return ds;  //返回数据集
    }

    /// <summary>
    /// 按商品Id查询商品信息包含商家信息
    /// </summary>
    /// <param name="productId">商品ID</param>
    /// <returns></returns>
    public DataSet ProductByProductIdSuppName(int productId)
    {
        SqlDataAdapter sdr = new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand();   //创建数据适配集的SelectCommand对象
        //级联supplier和product表进行查询
        sdr.SelectCommand.CommandText = "select ProductId, ListPrice,UnitCost,Supplier.Name as sname,Product.Name,Descn,[Image],Qty from product inner join supplier on supplier.suppid=product.suppid where productId=@productId";
        sdr.SelectCommand.Connection = con; //直接调用数据库连接对象
        sdr.SelectCommand.Parameters.Add("@productId", SqlDbType.Int).Value = productId;
        sdr.Fill(ds, "pruductByPidSuppName");  //填充数据集ds，表名为pruductByPidSuppName
        return ds;  //返回数据集
    }

/// <summary>
/// 查询全部商品信息包含商家信息
/// </summary>
/// <returns></returns>
    public DataSet ProductAllSuppName()
    {
        SqlDataAdapter sdr = new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand();   //创建数据适配集的SelectCommand对象
        //级联supplier和product表进行查询
        sdr.SelectCommand.CommandText = "select ProductId, ListPrice,UnitCost,Supplier.Name as sname,Product.Name,Descn,[Image],Qty from product inner join supplier on supplier.suppid=product.suppid ";
        sdr.SelectCommand.Connection = con; //直接调用数据库连接对象

        sdr.Fill(ds, "pruductAllSuppName");  //填充数据集ds，表名为pruductAllSuppName
        return ds;  //返回数据集
    }



    /// <summary>
    /// 修改商品信息
    /// </summary>
    /// <param name="pst">实体类</param>
    /// <returns></returns>
    public string ProductUpdate(ProductST pst)
    {
        string messgae = "";
        SqlCommand cmd = new SqlCommand();                           //创建SqlCommand对象
        cmd.Connection = con;
        cmd.CommandText = "update product set categoryid=@categoryid, ListPrice=@ListPrice,SuppId=@SuppId,[Name]=@pName,Descn=@Descn,[Image]=@img,Qty=@Qty where productId=@productId";
        cmd.Parameters.Add("@categoryId", SqlDbType.Int).Value = pst.CategoryId;
        cmd.Parameters.Add("@ListPrice", SqlDbType.Decimal).Value = pst.ListPrice;       
        cmd.Parameters.Add("@SuppId", SqlDbType.Int).Value = pst.Suppid;
        cmd.Parameters.Add("@pName", SqlDbType.VarChar).Value = pst.Pname;
        cmd.Parameters.Add("@Descn", SqlDbType.VarChar).Value = pst .Descn;
        cmd.Parameters.Add("@img", SqlDbType.VarChar).Value = pst.Image;
        cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = pst .Qty;
        cmd.Parameters.Add("@productId", SqlDbType.Int).Value = pst.ProductId;
        try
        {
            con.Open();            //打开数据库
            if (cmd.ExecuteNonQuery() > 0)              //执行SQL语句
            {
                messgae = "商品修改成功！";
            }
            else
            {
                messgae = "商品修改失败！";
            }
        }
        catch(Exception ex)
        {
            messgae = "数据库打开出错！";
        }
        finally
        {
            con.Close();      //关闭数据库           
        }
        return messgae;
    }

    /// <summary>
    ///添加商品详细信息
    /// </summary>
    /// <param name="categoryId"></param>
    /// <param name="listPrice"></param>
    /// <param name="unitCost"></param>
    /// <param name="suppId"></param>
    /// <param name="pname"></param>
    /// <param name="desc"></param>
    /// <param name="img"></param>
    /// <param name="qty"></param>
    /// <returns></returns>
    public string ProductAdd(int categoryId,decimal listPrice,int suppId,string pname,string desc,string img,int qty )
    {
        string messgae = "";
        SqlCommand cmd = new SqlCommand();                           //创建SqlCommand对象
        cmd.Connection = con;
        cmd.CommandText = "insert into product(CategoryId,ListPrice,SuppId,[Name],Descn,[Image],Qty) values(@CategoryId,@ListPrice,@SuppId,@pName,@Descn,@img,@Qty)";
        cmd.Parameters.Add("@categoryId", SqlDbType.Int).Value = categoryId;
        cmd.Parameters.Add("@ListPrice", SqlDbType.Decimal).Value = listPrice;       
        cmd.Parameters.Add("@SuppId", SqlDbType.Int).Value = suppId;
        cmd.Parameters.Add("@pName", SqlDbType.VarChar).Value = pname ;
        cmd.Parameters.Add("@Descn", SqlDbType.VarChar).Value = desc;
        cmd.Parameters.Add("@img", SqlDbType.VarChar).Value = img;
        cmd.Parameters.Add("@Qty", SqlDbType.Int).Value =qty ;
        try
        {
            con.Open();            //打开数据库
            if (cmd.ExecuteNonQuery() > 0)              //执行SQL语句
            {
                messgae = "商品添加成功！";
            }
            else
            {
                messgae = "商品添加失败！";
            }
        }
        catch
        {
            messgae = "数据库打开出错！";
        }
        finally
        {
            con.Close();      //关闭数据库           
        }
        return messgae;
    }

    public string ProductAddST(ProductST pst)
    {
        string messgae = "";
        SqlCommand cmd = new SqlCommand();                           //创建SqlCommand对象
        cmd.Connection = con;
        cmd.CommandText = "insert into product(CategoryId,ListPrice,SuppId,[Name],Descn,[Image],Qty) values(@CategoryId,@ListPrice,@SuppId,@pName,@Descn,@img,@Qty)";
        cmd.Parameters.Add("@categoryId", SqlDbType.Int).Value = pst.CategoryId;
        cmd.Parameters.Add("@ListPrice", SqlDbType.Decimal).Value = pst.ListPrice;
        cmd.Parameters.Add("@SuppId", SqlDbType.Int).Value = pst.Suppid;
        cmd.Parameters.Add("@pName", SqlDbType.VarChar).Value = pst.Pname;
        cmd.Parameters.Add("@Descn", SqlDbType.VarChar).Value = pst.Descn;
        cmd.Parameters.Add("@img", SqlDbType.VarChar).Value = pst.Image;
        cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = pst.Qty;
        try
        {
            con.Open();            //打开数据库
            if (cmd.ExecuteNonQuery() > 0)              //执行SQL语句
            {
                messgae = "商品添加成功！";
            }
            else
            {
                messgae = "商品添加失败！";
            }
        }
        catch
        {
            messgae = "数据库打开出错！";
        }
        finally
        {
            con.Close();      //关闭数据库           
        }
        return messgae;
    }

/// <summary>
/// 按商品id删除商品
/// </summary>
/// <param name="pst"></param>
/// <returns></returns>
    public string ProductDeleteST(ProductST pst)
    {
        string messgae = "";
        SqlCommand cmd = new SqlCommand();                           //创建SqlCommand对象
        cmd.Connection = con;
        cmd.CommandText = "delete product where productid=@productid";
        cmd.Parameters.Add("@productid", SqlDbType.Int).Value = pst.ProductId;
        try
        {
            con.Open();            //打开数据库
            if (cmd.ExecuteNonQuery() > 0)              //执行SQL语句
            {
                messgae = "商品删除成功！";
            }
            else
            {
                messgae = "商品删除失败！";
            }
        }
        catch
        {
            messgae = "数据库打开出错！";
        }
        finally
        {
            con.Close();      //关闭数据库           
        }
        return messgae;
    }
//购买后的商品数量修改
    public string ProductQty(int productId,int buyNum)
    {
        string messgae = "";
        SqlCommand cmd = new SqlCommand();                           //创建SqlCommand对象
        cmd.Connection = con;
        cmd.CommandText = "update product set qty=qty-@qty where productid=@productid";
        cmd.Parameters.Add("@productid", SqlDbType.Int).Value =productId ;
        cmd.Parameters.Add("@qty", SqlDbType.Int).Value = buyNum ;
        try
        {
            con.Open();            //打开数据库
            if (cmd.ExecuteNonQuery() > 0)              //执行SQL语句
            {
                messgae = "商品修改成功！";
            }
            else
            {
                messgae = "商品修改失败！";
            }
        }
        catch
        {
            messgae = "数据库打开出错！";
        }
        finally
        {
            con.Close();      //关闭数据库           
        }
        return messgae;
    }

    /// <summary>
    /// 模糊查找商品信息
    /// </summary>
    /// <param name="productId">商品名</param>
    /// <returns></returns>
    public DataSet ProductByProductName(string pname)
    {
        SqlDataAdapter sdr = new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand();   //创建数据适配集的SelectCommand对象
        sdr.SelectCommand.CommandText = "select * from product where name like '%"+pname +"%'";
        sdr.SelectCommand.Connection = con; //直接调用数据库连接对象
        sdr.Fill(ds, "pruductByPname");  //填充数据集ds，表名为pruductByPname
        return ds;  //返回数据集
    }



}
