FormProductCheck.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;

namespace ProductManager
{
    public partial class FormProductCheck : Form
    {
        private SqlConnection sql;

        public FormProductCheck()
        {
            #region
            InitializeComponent();
            splitContainer.SetStyle();
            dgvProduct.SetStyle();
            clbName.CheckOnClick = true;    // 当选定项时应切换复选框。
            sql = DBConnection.Connection;
            cmbType.SelectedIndex = 0;
            btnRefresh_Click(null, EventArgs.Empty);
            #endregion
        }
        /// <summary>
        /// 以指定商品类型来加载商品名称。
        /// </summary>
        /// <param name="value"></param>
        #region
        public void LoadName(string value)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("select ProductID as ID, ProductName as Name from Product where ProductType=@ProductType", sql);
                cmd.Parameters.Add("@ProductType", SqlDbType.VarChar, 20, "ProductType").Value = value;
                if (sql.State == ConnectionState.Closed)
                    sql.Open();
                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    clbName.ValueMember = "ID";
                    clbName.DataSource = dr.Cast<System.Data.Common.DbDataRecord>().ToList();
                    clbName.DisplayMember = "Name";
                }
            }
            catch
            {
                if (sql.State == ConnectionState.Open)
                    sql.Close();
            }
        }
        #endregion
       
        /// <summary>
        /// 加载商品盘点信息。
        /// </summary>
        #region
        public void LoadData()
        {
            try
            {
                dgvProduct.Rows.Clear();
                SqlCommand cmd = new SqlCommand("Select_TotalInfo", sql);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter spID = cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 10);
                if (cbDate.Checked)
                {
                    cmd.CommandText = "Select_TotalDateInfo";
                    cmd.Parameters.Add("@BeginDate", SqlDbType.SmallDateTime).Value = dtpBegin.Value.Date;
                    cmd.Parameters.Add("@EndDate", SqlDbType.SmallDateTime).Value = dtpEnd.Value.Date;
                }
                if (sql.State == ConnectionState.Closed)
                    sql.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                int type = dr.GetOrdinal("ProductType");
                int unit = dr.GetOrdinal("Unit");
                int number = dr.GetOrdinal("Number");
                int inNumber = dr.GetOrdinal("InNumber");
                int outNumber = dr.GetOrdinal("OutNumber");
                dr.Close();
                foreach (int index in this.clbName.CheckedIndices)
                {
                    clbName.SelectedIndex = index;
                    string value = Convert.ToString(clbName.SelectedValue);
                    spID.Value = value;
                    dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                    if (dr.Read())
                    {
                        string[] values = new string[] { value, dr.GetString(type), dr.GetString(unit), string.Format("{0:#,##0}", dr.GetInt32(number)), string.Format("{0:#,##0}", dr.GetInt32(inNumber)), string.Format("{0:#,##0}", dr.GetInt32(outNumber)) };
                        dgvProduct.Rows.Add(values);
                    }
                    dr.Close();
                }
                cmd.Dispose();
            }
            catch
            {
                return;
            }
            finally
            {
                if (sql.State == ConnectionState.Open)
                    sql.Close();
            }
        }
        #endregion

        #region btnRefresh_Click
        private void btnRefresh_Click(object sender, EventArgs e)
        {
            btnSelectAll_Click(null, EventArgs.Empty);
            this.LoadData();
            btnCancel_Click(null, EventArgs.Empty);
        }
        #endregion

        #region btnQuery_Click
        private void btnQuery_Click(object sender, EventArgs e)
        {
            if (clbName.CheckedIndices.Count < 1)
            {
                MessageBox.Show("请选择商品名！", "系统提示！", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (dtpBegin.Value.Date > dtpEnd.Value.Date)
            {
                MessageBox.Show("起始日期不能超过截至日期", "系统提示！", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                dtpBegin.Value = dtpEnd.Value;
                return;
            }
            this.LoadData();
        }
        #endregion

        #region PrintExcel
        private void btnExcel_Click(object sender, EventArgs e)
        {
            dgvProduct.PrintToExcel("商品盘点信息表");
        }
        #endregion

        #region SetItemCheckState
        private void btnSelectAll_Click(object sender, EventArgs e)
        {
            foreach (int index in Enumerable.Range(0, clbName.Items.Count))
            {
                this.clbName.SetItemCheckState(index, CheckState.Checked);
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            foreach (int index in clbName.CheckedIndices)
            {
                clbName.SetItemCheckState(index, CheckState.Unchecked);
            }
            dtpBegin.ResetText();
            dtpEnd.ResetText();
            cbDate.Checked = false;
        }
        #endregion

        #region
        private void cbDate_CheckedChanged(object sender, EventArgs e)
        {
            dtpEnd.Enabled = dtpBegin.Enabled = cbDate.Checked;
        }

        private void cmbType_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.LoadName(cmbType.Text);
        }

        private void dgvProduct_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1)
            {
                dgvProduct.TopLeftHeaderCell.Value = string.Format("{0:#,##0}", e.RowIndex + 1);
                clbName.SelectedValue = dgvProduct[0, e.RowIndex].Value;
            }
        }
        #endregion
    }
}