salesadminmanagement.cs

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

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

namespace SupermarketProject
{
	/// <summary>
	/// Summary description for DiscountMgmt.
	/// </summary>
	
	

	public class SalesAdminManagement
	{
		string str="", logFileName,folder;
        public static string logFileDirectory;

		public SalesAdminManagement()
		{
			
		}
		public DataTable FetchReturnDetails(string[] searchValue)
		{
			try
			{

				DateTime today = DateTime.Today;

				if(searchValue[0] != "") //billid is given
				{
					str="select billno,billdate,totalbillamt,usrid from Bills where billno = "+searchValue[0];
				}

				else if(searchValue[1] != "") // billdate is given
				{
					str="select billno,billdate,totalbillamt,usrid from Bills where billdate=#" +searchValue[1]+"#";

				}
				else
				{
					str="select billno,billdate,totalbillamt,usrid from Bills" ;
					//if date criteria is selected as today
					if(searchValue[2].Equals("Today")) 
						str +=" where billdate = #"+DateTime.Today.ToShortDateString()+"#";

					//if date criteria is selected as this week
					if(searchValue[2].Equals("This Week")) 
					{
						//determine starting date of this week
						DateTime currentdt = DateTime.Today.Date;
						int weekday=-1;
						switch (currentdt.DayOfWeek)
						{
							case DayOfWeek.Saturday: weekday=6; break;
							case DayOfWeek.Friday: weekday=5;break;
							case DayOfWeek.Thursday: weekday=4;break;
							case DayOfWeek.Wednesday: weekday=3;break;
							case DayOfWeek.Tuesday: weekday=2;break;
							case DayOfWeek.Monday: weekday=1;break;
							case DayOfWeek.Sunday: weekday=0;break;
						}
						DateTime weekstartdt = currentdt.AddDays(-weekday);
						str += " where billdate between #"+DateTime.Today.ToShortDateString()+"# and #"+weekstartdt.ToShortDateString()+"#";
						Console.WriteLine (str);
					}
					//if date criteria is selected as this month
					if(searchValue[2].Equals("This Month"))
						str += " where month(billdate)="+ today.Month;
					//if date criteria is selected as this year
					if(searchValue[2].Equals("This Year"))
						str += " where Year(billdate)="+ today.Year;
				}
				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("Error! Aborting application"+e.Message.ToString ());
				
				return new DataTable();
			}
		}
		
	
		
	
		// Accepting values from the form and processing, return value is Datatable type
		public DataTable FetchBillsDetails(string[] searchValue)
		{

			string str="";
	
			//Bill Number is not null
			if(searchValue[0] !="") 
			{
				System.Convert.ToInt32(searchValue[0]);
				str="select BillNo as [Bill Number],BillDate as [Bill Date], UsrId as [User Name], TotalBillAmt as [Total Bill Amount],PaymentMode as [Payment Mode] from Bills         where BillNo = "+searchValue[0]+"";
				
				// Bill Number and User Name is not null
				if(searchValue[1] != "") 
				{
					str += " and UsrId = '"+searchValue[1]+"'";
				}
				if(searchValue[2] != "") 
				{
					str += " and BillDate = #"+searchValue[2]+"#";
				}
					if(searchValue[3] !="")
					{
						str += " and PaymentMode = '"+searchValue[3]+"'";
					}
				}
					
			else if(searchValue[1] !="")
			{
				str = "Select BillNo as [Bill Number],BillDate as [Bill Date], UsrId as [User Name], TotalBillAmt as [Total Bill Amount],PaymentMode as [Payment Mode] from Bills         where UsrId = '"+searchValue[1]+"'"; 
				
				if(searchValue[2] !="")
				{
					str += " and BillDate = #"+searchValue[2]+"#";
				}
						
				if(searchValue[3] !="")
				{
					str += " and PaymentMode = '"+searchValue[3]+"'";
				}
			}
			else 
			{
				str = "Select BillNo as [Bill Number],BillDate as [Bill Date], UsrId as [User Name], TotalBillAmt as [Total Bill Amount],PaymentMode as [Payment Mode] from Bills"; 
				if(searchValue[3] != "")
				{
					str += " where PaymentMode = '"+searchValue[3]+"'";
				}
			}
		
			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]; 
		}

	// Displaying the Details of a Bill 
	public DataTable FetchBillDetails(int billno)
		{

			string str="";
			
			str="SELECT p.productname as [Product Name],quantity as [Quantity],saleprice as [Sales Price], StkDate as [Stock Date] from BillDetails bd, Products p, Stock s where bd.stockid = s.stockid and s.productid = p.productid and bd.billno = "+billno+"";
				
			
			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]; 
		}
		public string ReturnProducts(int billNo,string [] prodId)
		{
			try
			{
				/////////////////////////////			
				//logic to create the logfile 
				/////////////////////////////
				///
				bool toWriteHeading;
				DateTime dt = DateTime.Today;
				string currentDate = dt.ToString("dd/MM/yyyy") ;
				Settings settings = new Settings();
				folder = settings.mainDirectory+@"\"+currentDate.Substring(6,4);
				folder = folder +@"\"+currentDate.Substring(3,2);
				// Determine whether the directory for logfile exists.
				if (Directory.Exists(folder)) 
				{
					Console.WriteLine("该文件夹已存在。");
				}

				// Try to create the directory.
				DirectoryInfo dInfo =  Directory.CreateDirectory(folder);
			
				logFileName = folder+@"\"+currentDate.Substring(0,2)+".txt";
			
				// Determine whether the logfile already exists.
			
				if (File.Exists(logFileName)) 
					toWriteHeading =false;
				else
					toWriteHeading =true;
			
				//create the text file in append mode
				StreamWriter logFile = new StreamWriter (logFileName,true);
            
				//create heading row if doesnt already exist
				if(toWriteHeading)
				{
					logFile.WriteLine ("收银员标识\t\t帐单编号\t帐单日期\t\t\t库存标识\t\t数量\t单价");       
					logFile.WriteLine("");
					logFile.WriteLine("");
				}

				//for each product in the list of products
		
				OleDbDataReader reader;		
				decimal returnedprice = 0;
			
				for(int ctr = 0; ctr <= prodId.GetUpperBound(0);ctr++)
				{
				//retrieve the saleprice of that product
					str= "Select saleprice from billdetails where billno="+billNo+ " and stockid in (select stockid from stock where productid='"+prodId[ctr]+"')";
				
					DataConnection.commnd.CommandText= str;
					reader = DataConnection.commnd.ExecuteReader ();
					if (reader.HasRows)
					{
						reader.Read();
						returnedprice = returnedprice +  reader.GetDecimal (0);
					}
					reader.Close();	
					////////////
					//update qoh in product
					///////////
					int qty = 0;
					str="select b.quantity from Bills a, billdetails b where a.billno ="+billNo +" and b.billno="+billNo;
	
					
					DataConnection.commnd.CommandText = str;
					reader = DataConnection.commnd.ExecuteReader ();
					
					while (reader.Read())
					{
						qty= reader.GetInt32(0); 
				
					}

					reader.Close();
					str="Update Products set QOH=QOH+"+qty+" where productid='"+ prodId[ctr]+"'";

					DataConnection.commnd.CommandText= str;
					DataConnection.commnd.ExecuteNonQuery();
			
					//get the data from the table to write to the text file
					str="select a.usrid,a.billno,a.billdate,b.stockid,b.quantity,b.saleprice from Bills a, billdetails b where a.billno ="+billNo +" and b.billno="+billNo +" and b.stockid in (select stockid from stock where productid='"+prodId[ctr]+"')";
	
					
					DataConnection.commnd.CommandText =str;
					reader = DataConnection.commnd.ExecuteReader ();
					
					while (reader.Read())
					{
						logFile.Write(reader.GetString(0)+"\t\t"); //usr id
						logFile.Write(reader.GetInt32(1)+"\t"); //billno
						logFile.Write(reader.GetDateTime(2).ToShortDateString() +"\t\t"); //billdate
						logFile.Write(reader.GetInt32(3)+"\t\t"); //stock id
						logFile.Write(reader.GetInt32(4)+"\t"); //quantity
						logFile.Write(reader.GetDecimal(5)); //saleprice
						logFile.WriteLine("");
					}
				
			        reader.Close();
				
					/////////////
					//delete the returned item from BillDetails
					///////////
					///
					
					str="delete from billdetails where billno="+billNo+" and stockid in (select stockid from stock where productid='"+ prodId[ctr]+"')";
					DataConnection.commnd.CommandText= str;
					DataConnection.commnd.ExecuteNonQuery();
					
					
				}//end of the for loop
				////////////////
				///update the total bill amt in Bills
				/////////////////
				str="Update bills set totalbillamt = totalbillamt - " + returnedprice +                " where billno = "+billNo;
				DataConnection.commnd.CommandText= str;
				DataConnection.commnd.ExecuteNonQuery();
				logFile.Close();
				return (logFileName);
			}
			catch(Exception e)
			{
				Console.WriteLine("由于出错，不能搜索！"+e.Message.ToString ());
			
				return null;
	
			}
		}//end of method
	}
}