order.cs

基于Asp.net、MS sql sever 2000、C# 网上书店系统源码

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DataAccessLayer
{
	/// <summary>
	/// Order description。
	/// </summary>
	public class Order:DbBase.Base,IEnumerator
	{
		private ArrayList allBooks = new ArrayList();
		private int userId;
		//private byte status;
		private DateTime date = System.DateTime.Now.Date;
		private int position=-1;

		public Order()
		{
			
		}


		#region Properties of order class

		object IEnumerator.Current
		{
			get
			{
				return allBooks[position];
			}
		}


		/// <summary>
		/// set detail of order form
		/// </summary>
		public int Count
		{
			get
			{
				strSQL = "Select Count(*) From OrderDetails Where OrderId=" + this.ID; 
				try
				{
					return ExecuteSql4Value(strSQL);
				}
				catch
				{
					return -1;
				}				
			}
		}

		/// <summary>
		/// sum
		/// </summary>
		public double Total
		{
			get
			{
				strSQL = "Select Sum(A.Price * A.Discount * C.Quantity) as Total From Book A,Orders B ,OrderDetails C Where A.id = C.BookId and B.id = C.OrderId And C.OrderId=" + this.ID; 
				try
				{
					return (double)ExecuteSql4ValueEx(strSQL);
				}
				catch
				{
					return 0.0;
				}				
			}
		}


		/// <summary>
		/// User ID
		/// </summary>
		public int UserId
		{
			get
			{
				return userId;			
			}
			set
			{
				userId = value;
			}
		}
		#endregion


		#region Functions of order class

		/// <summary>
		/// return details of this order
		/// </summary>
		/// <returns></returns>
		public ICollection GetItems()
		{
			return allBooks;
		}

		

		public OrderDetails this[int index]
		{
			get
			{
				return (OrderDetails)allBooks[index];
			}
		}


		public void Clear()
		{
			allBooks.Clear();
		}


		public void Add(OrderDetails value)
		{
			allBooks.Add(value);
		}


		bool IEnumerator.MoveNext()
		{
			position++;
			if(position>=allBooks.Count)
			{
				return false;
			}
			else
			{
				return true;
			}
		}


		void IEnumerator.Reset()
		{
			position = -1;
		}


		public void RemoveAt(int index)
		{
			allBooks.RemoveAt(index);
		}


		public void Remove(int itemId)
		{
			foreach(OrderDetails item in allBooks)
			{
				if(itemId == item.BookId)
				{
					allBooks.Remove(item);
					return;
				}
			}
		}


		public int Have(int userId)
		{
			try
			{
				string [] arrSQL = new String[allBooks.Count];
				strSQL = "Insert into Orders(UserId) values(" + userId.ToString() + ")";
				ExecuteSql(strSQL);
				strSQL = "Select Max(Id) From Orders";
				int orderId = ExecuteSql4Value(strSQL);
			
				System.Text.StringBuilder sb = new System.Text.StringBuilder();
				DataAccessLayer.OrderDetails item = new DataAccessLayer.OrderDetails();
				for(int i=0;i<allBooks.Count;i++)
				{
					item = (DataAccessLayer.OrderDetails)allBooks[i];
					sb.Append("Insert into OrderDetails(OrderId,BookId,Quantity) values(");
					sb.Append(orderId.ToString());
					sb.Append(",");
					sb.Append(item.BookId.ToString());
					sb.Append(",");
					sb.Append(item.Quantity.ToString());
					sb.Append(")");
					arrSQL[i] = sb.ToString();
					sb.Remove(0,sb.Length);
				}

				ExecuteSqls(arrSQL);
				return orderId;
			}
			catch
			{
				throw new Exception("Order books FAILED!");
			}			
		}


		public static bool Deal(int orderId)
		{
			strSQL = "Update OrdersV Set Status =1 Where Id=" + orderId.ToString();
			try
			{
				ExecuteSql4Ds(strSQL);
				strSQL = "Update Book set Sales=Sales+1 Where id in(SELECT b.BookId FROM Orders a INNER JOIN OrderDetails b ON a.Id = b.OrderId AND a.Id = " + orderId.ToString() + ")";
				ExecuteSql4Ds(strSQL);
				return true;
			}
			catch
			{
				throw new Exception("处理清单失败！");
			}
		}


		public static DataSet GetOrder(int orderId)
		{
			strSQL = "SELECT UserId, OrderDate,CASE WHEN Status = '1' THEN '处理完毕' ELSE '没有处理' END AS Status FROM Orders Where Id=" + orderId.ToString();
			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("得到清单失败!");
			}
		}

		public static DataSet GetOrders_Search()
		{
			
		strSQL = "SELECT Orders.Id AS orderid,* FROM UserInfo INNER JOIN"

     
			+" Orders ON UserInfo.Id = Orders.UserId";


			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("更新失败!");
			}
		}

		public static DataSet GetOrders()
		{
			
		/*	strSQL="SELECT UserInfo.Name as name, UserInfo.Mail as mail, UserInfo.Address as address, UserInfo.Phone,"

				+"Orders.Id AS orderid, Orders.OrderDate as OrderDate, Orders.Bookid, Book.Name as bookname,"

				+"Book.Price as price"
				
				+" FROM Orders INNER JOIN"

				+" Book ON Orders.Bookid =Book.Id INNER JOIN"

				+" UserInfo ON Orders.UserId = UserInfo.Id";

				//+" WHERE Orders.Id = '"+orderId+"'";*/

			
			strSQL = "SELECT Orders.Id AS orderid,* FROM UserInfo INNER JOIN"

     
			+" Orders ON UserInfo.Id = Orders.UserId";


			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("更新失败!");
			}
		}

		/// <summary>
		/// get details orders
		/// 
		/// SELECT UserInfo.Name, UserInfo.Mail, UserInfo.Address, UserInfo.Phone, 
		//Orders.Id AS orderid, Orders.OrderDate, Orders.Bookid, Book.Name, 
		//Book.Price
		//FROM Orders INNER JOIN
		//Book ON Orders.Bookid = Book.Id INNER JOIN
		//UserInfo ON Orders.UserId = UserInfo.Id
		/// </summary>
		/// <returns></returns>
		public static DataSet GetOrders_details(string orderId)
		{
			
			/*strSQL = "SELECT UserInfo.Id, UserInfo.Name, UserInfo.Mail, UserInfo.Address, UserInfo.Phone," 
      
				+"Orders.Id AS orderid, Orders.OrderDate"
								
				+" FROM UserInfo INNER JOIN"

     
				+" Orders ON Orders.userid = UserInfo.Id"


				+" WHERE Orders.Id = '"+orderId+"'";*/

			/*strSQL="SELECT UserInfo.Name, UserInfo.Mail, UserInfo.Address, UserInfo.Phone,"

				+"Orders.Id AS orderid, Orders.OrderDate, Orders.Bookid, Book.Name,"

				+"Book.Price"
				
				+" FROM Orders INNER JOIN"

				+" Book ON Orders.Bookid =Book.Id INNER JOIN"

				+" UserInfo ON Orders.UserId = UserInfo.Id"

				+" WHERE Orders.Id = '"+orderId+"'";*/
			strSQL="SELECT * FROM OrderDetails INNER JOIN"

				+" Book ON OrderDetails.Bookid =Book.Id"

				+" INNER JOIN  UserInfo ON OrderDetails.Id = UserInfo.Id"

				+" WHERE OrderDetails.Id = '"+orderId+"'";

			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("更新失败!");
			}
		}


		public static DataSet GetOrders(int userId)
		{
			strSQL = "SELECT Id,UserId, OrderDate,CASE WHEN Status = '1' THEN '处理完毕' ELSE '没有处理' END AS Status FROM Orders Where UserId=" + userId.ToString();
			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("更新失败!");
			}
		}


		public static DataSet GetDetails(int orderId)
		{
			strSQL = "Select * from OrderDetails Where orderId=" + orderId.ToString();
			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("更新失败!");
			}
		}

		#endregion
	}
}