statform.cs

超市管理系统的完整版文档

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using SupDataBase;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace WindowsApplication1
{
	/// <summary>
	/// StatForm 的摘要说明。
	/// </summary>
	public class StatForm : System.Windows.Forms.Form
	{
		private CrystalDecisions.Windows.Forms.CrystalReportViewer crystalReportViewer1;
		private System.Windows.Forms.Splitter splitter1;
		private System.Windows.Forms.DataGrid m_GoodsData;
		private System.Windows.Forms.Panel panel1;
		private System.Windows.Forms.ComboBox comboBox1;
		private System.Windows.Forms.Button button1;
		private System.Data.DataView dataView1;
		int month;
		int currentRow;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;

		public StatForm()
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();
			month = 0;
			currentRow = -1;

			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗体设计器生成的代码
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.crystalReportViewer1 = new CrystalDecisions.Windows.Forms.CrystalReportViewer();
			this.splitter1 = new System.Windows.Forms.Splitter();
			this.m_GoodsData = new System.Windows.Forms.DataGrid();
			this.panel1 = new System.Windows.Forms.Panel();
			this.button1 = new System.Windows.Forms.Button();
			this.comboBox1 = new System.Windows.Forms.ComboBox();
			this.dataView1 = new System.Data.DataView();
			((System.ComponentModel.ISupportInitialize)(this.m_GoodsData)).BeginInit();
			this.panel1.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.dataView1)).BeginInit();
			this.SuspendLayout();
			// 
			// crystalReportViewer1
			// 
			this.crystalReportViewer1.ActiveViewIndex = -1;
			this.crystalReportViewer1.DisplayBackgroundEdge = false;
			this.crystalReportViewer1.DisplayGroupTree = false;
			this.crystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill;
			this.crystalReportViewer1.Location = new System.Drawing.Point(0, 155);
			this.crystalReportViewer1.Name = "crystalReportViewer1";
			this.crystalReportViewer1.ReportSource = null;
			this.crystalReportViewer1.ShowCloseButton = false;
			this.crystalReportViewer1.ShowGotoPageButton = false;
			this.crystalReportViewer1.ShowGroupTreeButton = false;
			this.crystalReportViewer1.ShowPageNavigateButtons = false;
			this.crystalReportViewer1.ShowTextSearchButton = false;
			this.crystalReportViewer1.Size = new System.Drawing.Size(608, 451);
			this.crystalReportViewer1.TabIndex = 0;
			this.crystalReportViewer1.Load += new System.EventHandler(this.crystalReportViewer1_Load);
			// 
			// splitter1
			// 
			this.splitter1.Dock = System.Windows.Forms.DockStyle.Top;
			this.splitter1.Location = new System.Drawing.Point(0, 152);
			this.splitter1.Name = "splitter1";
			this.splitter1.Size = new System.Drawing.Size(608, 3);
			this.splitter1.TabIndex = 1;
			this.splitter1.TabStop = false;
			// 
			// m_GoodsData
			// 
			this.m_GoodsData.AllowSorting = false;
			this.m_GoodsData.CaptionText = "商品统计信息";
			this.m_GoodsData.CaptionVisible = false;
			this.m_GoodsData.DataMember = "";
			this.m_GoodsData.Dock = System.Windows.Forms.DockStyle.Top;
			this.m_GoodsData.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.m_GoodsData.Location = new System.Drawing.Point(0, 32);
			this.m_GoodsData.Name = "m_GoodsData";
			this.m_GoodsData.Size = new System.Drawing.Size(608, 120);
			this.m_GoodsData.TabIndex = 2;
			this.m_GoodsData.Click += new System.EventHandler(this.m_GoodsData_Click);
			// 
			// panel1
			// 
			this.panel1.Controls.Add(this.button1);
			this.panel1.Controls.Add(this.comboBox1);
			this.panel1.Dock = System.Windows.Forms.DockStyle.Top;
			this.panel1.Location = new System.Drawing.Point(0, 0);
			this.panel1.Name = "panel1";
			this.panel1.Size = new System.Drawing.Size(608, 32);
			this.panel1.TabIndex = 3;
			// 
			// button1
			// 
			this.button1.Location = new System.Drawing.Point(144, 5);
			this.button1.Name = "button1";
			this.button1.TabIndex = 1;
			this.button1.Text = "刷新";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// comboBox1
			// 
			this.comboBox1.Location = new System.Drawing.Point(8, 6);
			this.comboBox1.Name = "comboBox1";
			this.comboBox1.Size = new System.Drawing.Size(121, 20);
			this.comboBox1.TabIndex = 0;
			this.comboBox1.Text = "comboBox1";
			this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.comboBox1_SelectedIndexChanged);
			// 
			// StatForm
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.AutoScroll = true;
			this.ClientSize = new System.Drawing.Size(608, 606);
			this.Controls.Add(this.crystalReportViewer1);
			this.Controls.Add(this.splitter1);
			this.Controls.Add(this.m_GoodsData);
			this.Controls.Add(this.panel1);
			this.Name = "StatForm";
			this.Text = "StatForm";
			this.Load += new System.EventHandler(this.StatForm_Load);
			((System.ComponentModel.ISupportInitialize)(this.m_GoodsData)).EndInit();
			this.panel1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataView1)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		private void crystalReportViewer1_Load(object sender, System.EventArgs e)
		{
			int currentMonth = System.DateTime.Now.Month;
			comboBox1.Items.Add("全年统计");
			for(int i = 1; i<= currentMonth; i++)
			{
				comboBox1.Items.Add(i + "月份统计");
			}
			comboBox1.SelectedIndex = 0;
			LoadSaleReport(0);

		}

		private void StatForm_Load(object sender, System.EventArgs e)
		{
			SetDataGrid();
		}

		private void m_GoodsData_Click(object sender, System.EventArgs e)
		{
			Console.WriteLine(m_GoodsData.CurrentRowIndex);
			if(currentRow == m_GoodsData.CurrentRowIndex)
			{
				return;
			}
			currentRow = m_GoodsData.CurrentRowIndex;
			string id = dataView1.Table.Rows[currentRow]["商品编号"].ToString();
			int goodsID;
			if(id.Equals("汇总"))
			{
				LoadSaleReport(month);
			}else{
				goodsID = System.Convert.ToInt32(id);
				LoadGoodsReport(goodsID, month);
			}
		}

		/// <summary>
		/// 加载销售统计图表，参数为月份，如为0表示全年
		/// </summary>
		private void LoadSaleReport(int month)
		{
			ReportDocument saleDoc = new ReportDocument();
			saleDoc.Load(GetPath() + "\\SaleReport.rpt");
			ParameterValues paraValues = new ParameterValues();
			ParameterRangeValue rangeValue = new ParameterRangeValue();
			ParameterDiscreteValue discreteValue = new ParameterDiscreteValue();
			int currentYear = DateTime.Now.Year;
			int currentDate = DateTime.Now.Day;

			if(month == 0)
			{
				rangeValue.StartValue = new DateTime(currentYear, 1, 1);
				rangeValue.EndValue = new DateTime(currentYear, 12, 31, 23, 59, 59);
				paraValues.Add(rangeValue);
				saleDoc.DataDefinition.ParameterFields["Period"].ApplyCurrentValues(paraValues);
				paraValues.Clear();
				discreteValue.Value = true;
				paraValues.Add(discreteValue);
				saleDoc.DataDefinition.ParameterFields["MonthOrWeek"].ApplyCurrentValues(paraValues);
			}else{
				int date = DaysOfMonth(currentYear, month);
				rangeValue.StartValue = new DateTime(currentYear, month, 1);
				rangeValue.EndValue = new DateTime(currentYear, month, date, 23, 59, 59);
				paraValues.Add(rangeValue);
				saleDoc.DataDefinition.ParameterFields["Period"].ApplyCurrentValues(paraValues);
				paraValues.Clear();
				discreteValue.Value = false;
				paraValues.Add(discreteValue);
				saleDoc.DataDefinition.ParameterFields["MonthOrWeek"].ApplyCurrentValues(paraValues);
			}
			
			crystalReportViewer1.ReportSource = saleDoc;		
		}

		private void button1_Click(object sender, System.EventArgs e)
		{
			month = comboBox1.SelectedIndex;
			SetDataGrid();
			LoadSaleReport(month);
		}

		/// <summary>
		/// 加载商品统计表；goodsID为商品标号；month为月份，如为0表示全年
		/// </summary>
		private void LoadGoodsReport(int goodsID, int month)
		{
			ReportDocument goodsDoc = new ReportDocument();
			goodsDoc.Load(GetPath() + "\\GoodsReport.rpt");
			ParameterValues  paraValues = new ParameterValues();
			ParameterRangeValue rangeValue = new ParameterRangeValue();
			ParameterDiscreteValue discreteValue = new ParameterDiscreteValue();

			int currentYear = DateTime.Now.Year;
			int currentMonth = DateTime.Now.Month;
			int currentDate = DateTime.Now.Day;
			if(month != 0)
			{
				currentMonth = month;
			}

			if(month == 0)
			{
				rangeValue.StartValue = new DateTime(currentYear, 1, 1);
				rangeValue.EndValue = new DateTime(currentYear, 12, 31, 23, 59, 59);
				paraValues.Add(rangeValue);
				goodsDoc.DataDefinition.ParameterFields["Period"].ApplyCurrentValues(paraValues);
				paraValues.Clear();
				discreteValue.Value = true;
				paraValues.Add(discreteValue);
				goodsDoc.DataDefinition.ParameterFields["MonthOrWeek"].ApplyCurrentValues(paraValues);
				discreteValue.Value = goodsID;
				paraValues.Clear();
				paraValues.Add(discreteValue);
				goodsDoc.DataDefinition.ParameterFields["ID"].ApplyCurrentValues(paraValues);
			}
			else
			{
				int date = DaysOfMonth(currentYear, currentMonth);
				rangeValue.StartValue = new DateTime(currentYear, currentMonth, 1);
				rangeValue.EndValue = new DateTime(currentYear, currentMonth, date, 23, 59, 59);
				paraValues.Add(rangeValue);
				goodsDoc.DataDefinition.ParameterFields["Period"].ApplyCurrentValues(paraValues);
				paraValues.Clear();
				discreteValue.Value = false;
				paraValues.Add(discreteValue);
				goodsDoc.DataDefinition.ParameterFields["MonthOrWeek"].ApplyCurrentValues(paraValues);
				discreteValue.Value = goodsID;
				paraValues.Clear();
				paraValues.Add(discreteValue);
				goodsDoc.DataDefinition.ParameterFields["ID"].ApplyCurrentValues(paraValues);

			}

			crystalReportViewer1.ReportSource = goodsDoc;
		}

		private void comboBox1_SelectedIndexChanged(object sender, System.EventArgs e)
		{
//			month = comboBox1.SelectedIndex;
		}

		/// <summary>
		/// 获得一个月的天数
		/// </summary>
		private int DaysOfMonth(int year, int month)
		{
			int days = 28;
			switch(month)
			{
				case 1:case 3:case 5:case 7:case 8:case 10:case 12:
					days = 31;
					break;
				case 4:case 6:case 9:case 11:
					days = 30;
					break;
				case 2:
					if(year % 4 == 0 && year % 100 != 0)
					{
						days = 29;
					}
					else
					{
						days = 28;
					}
					break;
			}
			return days;
		}

		private void SetDataGrid()
		{
			string begin, end;
			int currentYear = System.DateTime.Now.Year;
			if(month == 0)
			{
				begin = currentYear + "-1-1";
				end = currentYear + "-12-31";
			}
			else
			{
				begin = currentYear + "-" + month + "-1";
				end = currentYear + "-" + month + "-" + DaysOfMonth(currentYear, month);
			}
			string query = "SELECT GoodsID, GoodsName, SUM(PNum) AS Expr1, SUM(PSum) AS Expr2, SUM(SNum) AS Expr3, SUM(SSum) AS Expr4, SUM(Profit) AS Expr5 , AVG(StockNumber)"
				+ "FROM dbo.GoodsS "
				+ "WHERE (PSDate BETWEEN '" + begin + "' AND '" + end + "') "
				+ "GROUP BY GoodsID, GoodsName";

			System.Data.DataTable datatable = new System.Data.DataTable();
			System.Data.DataRow datarow;

			datatable.Columns.Add(new System.Data.DataColumn("商品编号"));
			datatable.Columns.Add(new System.Data.DataColumn("商品名称"));
			datatable.Columns.Add(new System.Data.DataColumn("进货量"));
			datatable.Columns.Add(new System.Data.DataColumn("进货额"));
			datatable.Columns.Add(new System.Data.DataColumn("销售量"));
			datatable.Columns.Add(new System.Data.DataColumn("销售额"));
			datatable.Columns.Add(new System.Data.DataColumn("利润"));
			datatable.Columns.Add(new System.Data.DataColumn("库存"));

			SqlConnection conn = DBUtil.GetConnection();
			conn.Open();
			SqlCommand cmd = new SqlCommand(query, conn);
			SqlDataReader reader = cmd.ExecuteReader();
			//			cmd.CommandType = CommandType.StoredProcedure;
			//			cmd.Parameters.Add()
			int    pNum = 0;
			Double pSum = 0.0;
			int    sNum = 0;
			Double sSum = 0.0;
			Double profit = 0.0;
			int    stock = 0;

			while(reader.Read())
			{
				datarow = datatable.NewRow();
				datarow[0] = reader[0];
				datarow[1] = reader[1];
				datarow[2] = reader[2];
				datarow[3] = System.Convert.ToDouble(reader[3]).ToString("f2");
				datarow[4] = System.Convert.ToDouble(reader[4]).ToString("f0");;
				datarow[5] = System.Convert.ToDouble(reader[5]).ToString("f2");
				datarow[6] = System.Convert.ToDouble(reader[6]).ToString("f2");;
				datarow[7] = reader[7];
				pNum += System.Convert.ToInt32(reader[2]);
				pSum += System.Convert.ToDouble(reader[3]);
				sNum += System.Convert.ToInt32(reader[4]);
				sSum += System.Convert.ToDouble(reader[5]);
				profit += System.Convert.ToDouble(reader[6]);
				stock += System.Convert.ToInt32(reader[7]);
				datatable.Rows.Add(datarow);
			}
			datarow = datatable.NewRow();
			datarow[0] = "汇总";
			datarow[1] = "";
			datarow[2] = pNum;
			datarow[3] = pSum.ToString("f2");
			datarow[4] = sNum;
			datarow[5] = sSum.ToString("f2");
			datarow[6] = profit.ToString("f2");
			datarow[7] = stock;
			datatable.Rows.Add(datarow);
			dataView1 = new DataView(datatable);
			m_GoodsData.DataSource = dataView1;
			conn.Close();
		}


		/// <summary>
		/// 获得程序路径
		/// </summary>
		public string GetPath()
		{
			string path = System.IO.Directory.GetCurrentDirectory();
			path = path.Substring(0, path.LastIndexOf(@"\"));
			path = path.Substring(0, path.LastIndexOf(@"\"));
			return path;
		}

		



	}
}