using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace SK.MemberDAL
{
	/// <summary>
	/// Product 的摘要说明。
	/// </summary>
	public class Product
	{
		public Product()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public DataSet List()
		{
			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select a.*,b.ProductTypeName from Product a LEFT JOIN ProductType b ON a.ProductTypeID=b.ProductTypeID");
		}
		public DataSet List_AllowMonthly()
		{
			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select a.*,b.ProductTypeName from Product a LEFT JOIN ProductType b ON a.ProductTypeID=b.ProductTypeID where a.AllowMonthly=1");
		}
		/*public void Update(object ProductID,object ProductName,object MonthlyPayment,object ProductNO)
		{
			SqlParameter[] oParms = new SqlParameter[4];
			oParms[0] = new SqlParameter("@ProductID",ProductID);
			oParms[1] = new SqlParameter("@ProductName",ProductName);
			oParms[2] = new SqlParameter("@MonthlyPayment",MonthlyPayment);
			oParms[3] = new SqlParameter("@ProductNO",ProductNO);

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Product set ProductName=@ProductName,MonthlyPayment=@MonthlyPayment,ProductNO=@ProductNO where ProductID=@ProductID",oParms);
		}*/
		public void Add(object ProductName,object ProductTypeID,object HomePage,object ProductNO,object AllowMonthly,object MonthlyPayment)
		{
			SqlParameter[] oParms = new SqlParameter[]{
														  new SqlParameter("@ProductName",ProductName),
														  new SqlParameter("@ProductTypeID",ProductTypeID),
														  new SqlParameter("@HomePage",HomePage),
														  new SqlParameter("@ProductNO",ProductNO),
														  new SqlParameter("@AllowMonthly",AllowMonthly),
														  new SqlParameter("@MonthlyPayment",MonthlyPayment)};

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"insert into Product(ProductName,ProductTypeID,HomePage,ProductNO,AllowMonthly,MonthlyPayment) values(@ProductName,@ProductTypeID,@HomePage,@ProductNO,@AllowMonthly,@MonthlyPayment)",oParms);
		}
		public void Update(object ProductID,object ProductName,object ProductTypeID,object HomePage,object ProductNO,object AllowMonthly,object MonthlyPayment)
		{
			SqlParameter[] oParms = new SqlParameter[]{
														  new SqlParameter("@ProductID",ProductID),
														  new SqlParameter("@ProductName",ProductName),
														  new SqlParameter("@ProductTypeID",ProductTypeID),
														  new SqlParameter("@HomePage",HomePage),
														  new SqlParameter("@ProductNO",ProductNO),
														  new SqlParameter("@AllowMonthly",AllowMonthly),
														  new SqlParameter("@MonthlyPayment",MonthlyPayment)};

			SqlHelper.ExecuteNonQuery(Data.ConnString,CommandType.Text,"update Product set ProductName=@ProductName,ProductTypeID=@ProductTypeID,HomePage=@HomePage,ProductNO=@ProductNO,AllowMonthly=@AllowMonthly,MonthlyPayment=@MonthlyPayment where ProductID=@ProductID",oParms);
		}
		public object ProductID(object ProductNO)
		{
			SqlParameter[] oParms = new SqlParameter[1];
			oParms[0] = new SqlParameter("@ProductNO",ProductNO);

			using(DataSet ds = SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select * from Product where ProductNO=@ProductNO",oParms))
			{
				if(ds.Tables[0].Rows.Count>0)
					return ds.Tables[0].Rows[0]["ProductID"];
				else
					return null;
			}
		}
		public DataRow Info(object ProductID)
		{
			SqlParameter[] oParms = new SqlParameter[1];
			oParms[0] = new SqlParameter("@ProductID",ProductID);

			return SqlHelper.ExecuteDataset(Data.ConnString,CommandType.Text,"select * from Product where ProductID=@ProductID",oParms).Tables[0].Rows[0];
		}
		public bool ExistsProductNO(object ProductID,object ProductNO)
		{
			SqlParameter[] oParms = new SqlParameter[2];
			oParms[0] = new SqlParameter("@ProductID",ProductID);
			oParms[1] = new SqlParameter("@ProductNO",ProductNO);

			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString,CommandType.Text,"select count(*) from Product where ProductNO=@ProductNO and ProductID!=@ProductID",oParms))>0;
		}
		public bool ExistsProductNO(object ProductNO)
		{
			SqlParameter[] oParms = new SqlParameter[1];
			oParms[0] = new SqlParameter("@ProductNO",ProductNO);

			return Convert.ToInt32(SqlHelper.ExecuteScalar(Data.ConnString,CommandType.Text,"select count(*) from Product where ProductNO=@ProductNO",oParms))>0;
		}
		public void Delete(object ProductID)
		{
			string spName;
			SqlParameter[] oParms;
			spName = "Product_Delete";
			oParms = SqlHelperParameterCache.GetSpParameterSet(Data.ConnString, spName);
			oParms[0].Value=ProductID;
			
			SqlHelper.ExecuteNonQuery(Data.ConnString, spName, oParms);
		}
	}
}
