frmstockdate.cs

制造业ERP系统搜集

																																																			  new System.Data.Common.DataColumnMapping("当日库存", "当日库存"),
																																																			  new System.Data.Common.DataColumnMapping("最新进价", "最新进价")})});
			// 
			// sqlSelectCommand1
			// 
			this.sqlSelectCommand1.CommandText = "SELECT 物料编号, 品名, 单位, 仓库, 上日库存, 当日购入, 购入累计, 当日出库, 出库累计, 当日库存, 最新进价 FROM 物料库存";
			this.sqlSelectCommand1.Connection = this.sqlConnection1;
			// 
			// sqlConnection1
			// 
			this.sqlConnection1.ConnectionString = "workstation id=WINDOWSXP;packet size=4096;user id=sa;data source=\".\";persist secu" +
				"rity info=False;initial catalog=jxcbook";
			// 
			// txtDisplayPageNo
			// 
			this.txtDisplayPageNo.BackColor = System.Drawing.SystemColors.ActiveBorder;
			this.txtDisplayPageNo.Location = new System.Drawing.Point(152, 104);
			this.txtDisplayPageNo.Name = "txtDisplayPageNo";
			this.txtDisplayPageNo.Size = new System.Drawing.Size(112, 16);
			this.txtDisplayPageNo.TabIndex = 15;
			// 
			// FrmStockDate
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(680, 414);
			this.Controls.Add(this.dataGrid1);
			this.Controls.Add(this.splitter1);
			this.Controls.Add(this.panel1);
			this.Name = "FrmStockDate";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "物料库存查询(即时)";
			this.Load += new System.EventHandler(this.FrmStockDate_Load);
			this.panel1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>

		private void FrmStockDate_Load(object sender, System.EventArgs e)
		{//显示所有记录

			//Open Connection. 
			SqlConnection conn = new SqlConnection("workstation id=localhost;Integrated Security=SSPI;database=jxcbook");

			//Set the DataAdapter's query.
			da = new SqlDataAdapter("select * from 物料库存", conn);
			ds = new DataSet();

			//Fill the DataSet.
			da.Fill(ds, "物料库存");

			//Set the source table.
			dtSource = ds.Tables["物料库存"];
		}

		private void button1_Click(object sender, System.EventArgs e)
		{//查询记录
			try
			{
				this.ds.Clear();							
				string StrSQL=" FROM 物料库存 WHERE ";
				if (comboBox1.Text.Trim() != "")
					StrSQL+="仓库 like '"+comboBox1.Text.Trim()+ "%' and ";
				if (textBox1.Text.Trim() != "")
					StrSQL+="物料编号 like '"+textBox1.Text.Trim()+ "%' and ";

				if(StrSQL !="")
					StrSQL=StrSQL.Substring(0,StrSQL.Length-5);
				this.sqlDataAdapter1.SelectCommand.CommandText="Select * "+StrSQL;
				this.sqlDataAdapter1.SelectCommand.Connection=this.sqlConnection1;
				this.sqlConnection1.Open();
				this.sqlDataAdapter1.SelectCommand.ExecuteNonQuery();
				this.sqlDataAdapter1.Fill(this.ds);
				this.dataGrid1.DataSource=this.dtSource;
				System.Data.SqlClient.SqlCommand MyCommandAmt=new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) "+StrSQL,this.sqlConnection1);
				this.label3.Text="一共查询到："+MyCommandAmt.ExecuteScalar().ToString()+"条记录符合要求";			
				System.Data.SqlClient.SqlCommand MyCommandSum=new System.Data.SqlClient.SqlCommand("SELECT SUM(上日库存) "+StrSQL,this.sqlConnection1);
				this.label4.Text="上日库存总计为："+MyCommandSum.ExecuteScalar().ToString();
				System.Data.SqlClient.SqlCommand MyCommandAvg=new System.Data.SqlClient.SqlCommand("SELECT SUM(当日库存) "+StrSQL,this.sqlConnection1);
				this.label4.Text+="    ,      当日库存总计为："+MyCommandAvg.ExecuteScalar().ToString();
				this.sqlConnection1.Close();

				// Set the start and max records. 
				pageSize = Convert.ToInt32(txtPageSize.Text);
				maxRec = dtSource.Rows.Count;
				PageCount = maxRec / pageSize;

				//Adjust the page number if the last page contains a partial page.
				if ((maxRec % pageSize) > 0) 
				{
					PageCount += 1;
				}

				// Initial seeings
				currentPage = 1;
				recNo = 0;

				// Display the content of the current page.
				LoadPage();
			}
			catch(Exception Err)
			{
				MessageBox.Show("查询数据集记录操作失败："+Err.Message,"信息提示",
					MessageBoxButtons.OK,MessageBoxIcon.Information);
				if(this.sqlConnection1.State==ConnectionState.Open)
				{
					this.sqlConnection1.Close();
				}
			}				
		}

		private void FrmStockDate_Closed(object sender, System.EventArgs e)
		{//关闭程序
			if(this.sqlConnection1.State==ConnectionState.Open)
			{
				this.sqlConnection1.Close();
			}			
		}

		private void LoadPage() 
		{
			int i;
			int startRec;
			int endRec;
			DataTable dtTemp;

			//Clone the source table to create a temporary table.
			dtTemp = dtSource.Clone();

			if (currentPage == PageCount) 
			{
				endRec = maxRec;
			}
			else 
			{
				endRec = pageSize * currentPage;
			}
			startRec = recNo;

			//Copy rows from the source table to fill the temporary table.
			for (i = startRec; i < endRec; i++) 
			{
				if(i<=0)
				{
					MessageBox.Show("没有查询到任何数据！","提示信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
					break;
				}
				else
				{
					dtTemp.ImportRow(dtSource.Rows[i]);
					recNo += 1;
				}
			}
			dataGrid1.DataSource = dtTemp;
			DisplayPageInfo();
		}

		private void DisplayPageInfo() 
		{
			txtDisplayPageNo.Text = "总页数： " + currentPage.ToString() + "/ " + PageCount.ToString();
		}

		private bool CheckFillButton() 
		{
			// Check if the user clicks the "Fill Grid" button.
			if (pageSize == 0) 
			{
				MessageBox.Show("Set the Page Size, and then click the Fill Grid button!");
				return false;
			}
			else 
			{
				return true;
			}
		}

		private void btnFillGrid_Click(object sender, System.EventArgs e)
		{
			// Set the start and max records. 
			pageSize = Convert.ToInt32(txtPageSize.Text);
			maxRec = dtSource.Rows.Count;
			PageCount = maxRec / pageSize;

			//Adjust the page number if the last page contains a partial page.
			if ((maxRec % pageSize) > 0) 
			{
				PageCount += 1;
			}

			// Initial seeings
			currentPage = 1;
			recNo = 0;

			// Display the content of the current page.
			LoadPage();
			this.button1.Enabled=true;
			this.btnFirstPage.Enabled=true;
			this.btnLastPage.Enabled=true;
			this.btnPreviousPage.Enabled=true;
			this.btnNextPage.Enabled=true;
			this.comboBox1.Enabled=true;
			this.textBox1.Enabled=true;
			this.label1.Enabled=true;
			this.label2.Enabled=true;
			this.label3.Enabled=true;
			this.label4.Enabled=true;
			this.btnFillGrid.Enabled=false;
			this.btnFillGrid.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(192)), ((System.Byte)(1)));
		}

		private void btnFirstPage_Click(object sender, System.EventArgs e)
		{
			if (CheckFillButton() == false) 
			{
				return;
			}

			//Check if you are already at the first page.
			if (currentPage == 1) 
			{
				MessageBox.Show("已经是首页了!");
				return;
			}

			currentPage = 1;
			recNo = 0;
			LoadPage();
		}

		private void btnNextPage_Click(object sender, System.EventArgs e)
		{
			//If the user did not click the "Fill Grid" button, then return.
			if (CheckFillButton() == false) 
			{ 
				return;
			}

			//Check if the user clicks the "Fill Grid" button.
			if (pageSize == 0) 
			{
				MessageBox.Show("Set the Page Size, and then click the Fill Grid button!");
				return;
			}

			currentPage += 1;
			if (currentPage > PageCount) 
			{
				currentPage = PageCount;
				//Check if you are already at the last page.
				if (recNo == maxRec) 
				{
					MessageBox.Show("已经是最后一页了!");
					return;
				}
			}
			LoadPage();
		}

		private void btnPreviousPage_Click(object sender, System.EventArgs e)
		{
			if (CheckFillButton() == false) 
			{
				return;
			}

			if (currentPage == PageCount) 
			{
				recNo = pageSize * (currentPage - 2);
			}

			currentPage -= 1;
			//Check if you are already at the first page.
			if (currentPage < 1) 
			{
				MessageBox.Show("已经到头了!");
				currentPage = 1;
				return;
			}
			else 
			{
				recNo = pageSize * (currentPage - 1);
			}
			LoadPage();
		}

		private void btnLastPage_Click(object sender, System.EventArgs e)
		{
			if (CheckFillButton() == false) 
			{
				return;
			}

			//Check if you are already at the last page.
			if (recNo == maxRec) 
			{
				MessageBox.Show("已经到底了!");
				return;
			}
			currentPage = PageCount;
			recNo = pageSize * (currentPage - 1);
			LoadPage();
		}

	}
}