using System;
using System.Data.SqlClient;
using System.Data;
namespace AssetManagement
{
	/// <summary>
	/// asset 的摘要說明。
	/// </summary>
	public class asset:Connection
	{
		private SqlConnection connConnection;
		public SqlConnection getConnection
		{
			get
			{
				return new SqlConnection(this.getConnectionString);
			}
			set
			{
			}
		}
		public asset()
		{
			//
			// TODO: 在此處添加構造函數邏輯
			//
		}
		
		public void Open()
		{
			connConnection = this.getConnection;
			connConnection.Open();
		}
		public int ExecuteSQL(string strCommandString)
		{
			SqlCommand cmdCommand = new SqlCommand(strCommandString,connConnection);
			int nAffected = cmdCommand.ExecuteNonQuery();
			return nAffected;
		}
		public SqlDataReader ExecuteReader(string str)
		{
			SqlCommand cmdCommand = new SqlCommand(str,connConnection);
			SqlDataReader dr = cmdCommand.ExecuteReader();
			return dr;
		}
		public void Close()
		{
			connConnection.Close();
		}
		public class assetDetails
		{
			public string assetNo;
			public string assetCategoryNo;
			public string employeeNo;
			public string statusNo;
			public string assetDescription;
			public int serialNo;
			public string dateAcquired;
			public float purchasePrice;
			public float currentValue;
			public string dateSold;
		    public string nextMaintenanceDate;

		}
		/*@assetNo varchar(10),
		@assetCategoryNo varchar (10),
		@employeeNo varchar(10),
		@assetDescription varchar (20),
		@serialNo int,
		@dateAcquired varchar (20),
		@purchasePrice float(8)*/
		public bool addAssetNo1(string assetNo,string assetCategoryNo,string employeeNo)
		{
			SqlCommand command = new SqlCommand("addAssetNo",this.connConnection);
			command.CommandType = CommandType.StoredProcedure;
			SqlParameter AssetNo = new SqlParameter("@assetNo",SqlDbType.VarChar,10);
			AssetNo.Value = assetNo;
			command.Parameters.Add(AssetNo);
			SqlParameter AssetCategoryNo = new SqlParameter("@assetCategory",SqlDbType.VarChar,10);
			AssetCategoryNo.Value= assetCategoryNo;
			command.Parameters.Add(AssetCategoryNo);
			SqlParameter EmployeeNo = new SqlParameter("@employeeNo",SqlDbType.VarChar,10);
			EmployeeNo.Value = employeeNo;
			command.Parameters.Add(EmployeeNo);
			try
			{
				command.ExecuteNonQuery();
				return true;
			}
			catch
			{
				return false;
			}
		}
	public bool addAsset(string assetNo,string assetCategoryNo,string employeeNo,string assetDescription,int serialNo,string dateAcquired,float purchasePrice)
		{
		   SqlCommand command = new SqlCommand("addAsset",connConnection);
		command.CommandType = CommandType.StoredProcedure;
		SqlParameter AssetNo = new SqlParameter("@assetNo",SqlDbType.VarChar,10);
		AssetNo.Value = assetNo;
		command.Parameters.Add(AssetNo);
		SqlParameter AssetCategoryNo = new SqlParameter("@assetCategory",SqlDbType.VarChar,10);
		AssetCategoryNo.Value= assetCategoryNo;
		command.Parameters.Add(AssetCategoryNo);
		SqlParameter EmployeeNo = new SqlParameter("@employeeNo",SqlDbType.VarChar,10);
		EmployeeNo.Value = employeeNo;
		command.Parameters.Add(EmployeeNo);
		SqlParameter AssetDescription = new SqlParameter("@assetDescription",SqlDbType.VarChar,20);
		AssetDescription.Value = assetDescription;
		command.Parameters.Add(AssetDescription);
		try
		{
			SqlParameter SerialNo = new SqlParameter("@serialNo",SqlDbType.Int);
			SerialNo.Value = serialNo;
			command.Parameters.Add(SerialNo);
		}
		catch
		{
			return false;
		}
		SqlParameter DateAcquired = new SqlParameter("@dateAcquired",SqlDbType.VarChar,20);
		DateAcquired.Value = dateAcquired;
		command.Parameters.Add(DateAcquired);
		try
		{
			SqlParameter PurchasePrice = new SqlParameter("@purchasePrice",SqlDbType.Float,8);
			PurchasePrice.Value = purchasePrice;
			command.Parameters.Add(PurchasePrice);
		}
		catch
		{
			return false;
		}
		try
		{
			command.ExecuteNonQuery();
			return true;
		}
		catch
		{
			return false;
		}

		}
		public bool modifyAsset(string assetNo,string assetCategoryNo,string employeeNo,string statusNo,string assetDescription,
			int serialNo,string dateAcquired,float purchasePrice,string dateSold,string nextMaintenanceDate,string sql)
		{
			SqlConnection conn1 = new SqlConnection(sql);
			conn1.Open();
			SqlCommand cmd = new SqlCommand("modifyAsset",conn1);
			cmd.CommandType =  CommandType.StoredProcedure;

			SqlParameter AssetNo = new SqlParameter("@assetNo",SqlDbType.VarChar,10);
			AssetNo.Value = assetNo;
			cmd.Parameters.Add(AssetNo);

			SqlParameter AssetCategoryNo = new SqlParameter("@assetCategoryNo",SqlDbType.VarChar,10);
			AssetCategoryNo.Value = AssetCategoryNo;
			cmd.Parameters.Add(AssetCategoryNo);

			SqlParameter EmployeeNo = new SqlParameter("@employeeNo",SqlDbType.VarChar,10);
			EmployeeNo.Value = employeeNo;
			cmd.Parameters.Add(EmployeeNo);

			SqlParameter StatusNo = new SqlParameter("@statusNo",SqlDbType.VarChar,10);
			StatusNo.Value = statusNo;
			cmd.Parameters.Add(StatusNo);

			SqlParameter AssetDescription = new SqlParameter("@assetDescription",SqlDbType.VarChar,20);
			AssetDescription.Value = assetDescription;
			cmd.Parameters.Add(AssetDescription);

			SqlParameter SerialNo = new SqlParameter("@serialNo",SqlDbType.Int);
			SerialNo.Value = serialNo;
			cmd.Parameters.Add(SerialNo);

			
			SqlParameter DateAcquired = new SqlParameter("@dateAcquired",SqlDbType.VarChar,20);
			DateAcquired.Value = dateAcquired;
			cmd.Parameters.Add(DateAcquired);
		  
			SqlParameter PurchasePrice = new SqlParameter("@purchasePrice",SqlDbType.Float,8);
			PurchasePrice.Value = purchasePrice;
			cmd.Parameters.Add(PurchasePrice);

			SqlParameter DateSold = new SqlParameter("@dateSold",SqlDbType.VarChar,20);
			DateSold.Value = dateSold;
			cmd.Parameters.Add(DateSold);

			SqlParameter NextMaintenanceDate = new SqlParameter("@nextMaintenance",SqlDbType.VarChar,20);
			NextMaintenanceDate.Value = nextMaintenanceDate;
			cmd.Parameters.Add(NextMaintenanceDate);

			
			try
			{
				cmd.ExecuteNonQuery();
				return true;
			}
			catch
			{
				return false;
			}
			
		}
	}
}