stockmanagement.cs

本论文叙述了联机考试系统的现状以及C#语言的概况。重点介绍了联机考试系统的实现过程：包括系统分析、 系统调查、 数据流程分析、功能设计、 数据库设计、 系统物理配置方案、 系统实现、 系统测试和调试。

using System;
using System.Data;
using System.Data.OleDb;

namespace SupermarketProject
{
	/// <summary>
	/// Summary description for StockManagement.
	/// </summary>
	public struct strPrdCat
	{
		public string CatCode;
		public string CatTitle;
		public string CatDesc;
		public string FlvlCatCode;
		public decimal Rate;
	};
	public class StockManagement
	{
		public StockManagement()
		{
			//
			// TODO: Add constructor logic here
			//
		}
		public DataTable FetchProdDetails(string[] searchValue)
		{
			try
			{
				string str="";
						
				if(searchValue[0] != "") //ProdName is given
				{
					str="select * from Products where ProductName like '"+searchValue[0]+ "'";
					if(searchValue[1] != "") // catcode is also given
					{
						str += " and CatCode = '"+searchValue[1]+"'";
						if(searchValue[2] != "") //prodid is also given
						{
							str += " and ProductId = '"+searchValue[2]+"'";
						}
					}
				}
				else if(searchValue[0] == "") //if prodname is not given then check for the other two fields
				{
					if(searchValue[1] != "") //if catcode is given
					{
						str="select * from Products where CatCode = '"+searchValue[1]+ "'";
						if(searchValue[2] != "") //if prodid is also given
							str += " and ProductId = '"+searchValue[2]+"'";
					}
					else
					{
						if(searchValue[2] != "")//if only prodid is given 
							str = " Select * from Products where ProductId = '"+searchValue[2]+"'";
						else
							str = " Select * from Products";
					}						
				}
				Console.WriteLine(str);
	
				OleDbDataAdapter adapter=new OleDbDataAdapter(str,DataConnection.oleconn);

				DataSet dset=new DataSet();
				adapter.Fill (dset);
				
				return dset.Tables[0]; 
			}
			catch(Exception e)
			{
				Console.WriteLine ("由于出错，不能搜索！"+e.Message.ToString ());	
				return new DataTable(); 
			}
		}
		
		public void AddProduct(DataRow drPrd)
		{
			Product prd=new Product();
			prd.AddProduct (drPrd);
		}
		public void ModifyProduct(DataRow drPrd)
		{
			Product prd = new Product();
			prd.ModifyProduct(drPrd);
		}
		public void AddProdCat(strPrdCat stPrdCat)
		{

			string str="Insert into ProductCategories values('"+
				stPrdCat.CatCode+"','"+stPrdCat.CatTitle+"','"+stPrdCat.CatDesc+"','"+stPrdCat.FlvlCatCode+"',"+stPrdCat.Rate+");";

			Console.WriteLine(str);
			
			DataConnection.commnd.CommandText = str;
			DataConnection.commnd.ExecuteNonQuery ();
			Console.WriteLine("added");
			
		}
		public void ModifyProdCat(strPrdCat stPrdCat)
		{

			string str="Update ProductCategories set CatTitle='"+stPrdCat.CatTitle+"', CatDesc='"+stPrdCat.CatDesc+"',FlvlCatCode='"+stPrdCat.FlvlCatCode+"', Rate="+stPrdCat.Rate+" where CatCode='"+stPrdCat.CatCode+"';";

			Console.WriteLine(str);
			
			DataConnection.commnd.CommandText=str;
			DataConnection.commnd.ExecuteNonQuery ();
			Console.WriteLine("updated");
			

		}
	public void AddStock(DataRow drStk)
		{
			
			string str="Insert into Stock (StockId, Productid,Qty,StkDate,PurPrice) values ("+drStk["StockId"]+",'"+drStk["ProductId"]+"',"+drStk["Qty"]+",'"+drStk["StkDate"]+"',"+drStk["PurPrice"]+");";
			Console.WriteLine(str);
			DataConnection.commnd.CommandText=str;
			DataConnection.commnd.ExecuteNonQuery ();
			
			//update qoh in products
			DataConnection.load();
			str = "Update Products set QOH = QOH+"+drStk["Qty"]+" where ProductId='"+drStk["ProductId"]+"'";
			Console.WriteLine(str);
			DataConnection.commnd.CommandText=str;
			DataConnection.commnd.ExecuteNonQuery ();
			
		}

		public void UpdateStock(DataRow drStk)
		{
			string str="Update Stock set PurPrice="+
				drStk["PurPrice"]+", StkDate='"+drStk["StkDate"]+"',Qty="+drStk["Qty"]+" where StockId="+drStk["StockId"];
			Console.WriteLine(str);
			
			DataConnection.commnd.CommandText=str;
			DataConnection.commnd.ExecuteNonQuery ();
			Console.WriteLine("updated");
						
		}
	
		// Accepting values from the form and processing, return value is Datatable type
		public DataTable FetchStockDetails(string[] searchValue)
		{
			
			string str="";
	
			//Product Name is not null
			if(searchValue[0] !="") 
			{
				str="select StockId as [Stock ID],p.ProductName as [Product Name],Qty as [Stock Quantity],StkDate as [Stock Date],PurPrice as [Purchase Price] from Stock s,products p where s.productid = p.productid and p.productname like '"+searchValue[0]+ "'";
				
				// Category Title and Stock Date is not null
				if(searchValue[1] != "") 
				{
					str += "and p.catcode = (select catcode from productcategories where cattitle = '"+searchValue[1]+"')";
				}
				if (searchValue[2] !="")
				{
					str += "and stkDate = #"+searchValue[2]+"#";
				}
				// If Category title is null and product name is not null
			}
				// If product name is null and category title and date is not null
			else
			{
				if(searchValue[1] != "")
				{
					str="select StockId as [Stock ID],p.ProductName as [Product Name],Qty as [Stock Quantity] ,StkDate as [Stock Date],PurPrice as [Purchase Price] from Stock s,products p where s.productid = p.productid and p.catcode = (select catcode from productcategories where cattitle = '"+searchValue[1]+"')";
				
					if (searchValue[2]!="")
				
						str += " and stkDate = #"+searchValue[2]+"#";
				
				}
					//if product name and category title is null only the date is not null
				else
				{
					str="select StockId as [Stock ID],p.ProductName as [Product Name],Qty as [Stock Quantity],StkDate as [Stock Date],PurPrice as [Purchase Price] from Stock s,products p where s.productid = p.productid";

					if (searchValue[2]!="")
					str+=" and stkDate = #"+searchValue[2]+"#";
				}
			}
			
			Console.WriteLine(str);
				//processing 
				OleDbDataAdapter adapter=new OleDbDataAdapter(str,DataConnection.oleconn);
				DataSet dset = new DataSet();
				
				// Filling the Adapter with values
				adapter.Fill(dset);

			//	Console.WriteLine(dset.Tables[0].Rows.Count.ToString());
				// Returning the Data Table
				return dset.Tables[0]; 
			}
		}
	
}