query.cs

这是一个考勤系统大家多多指教

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace kaoqin.qy
{
    public partial class query : Form
    {
        public query()
        {
            InitializeComponent();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.comboBox1.SelectedIndex == 0)
            {
                this.groupBox2.Enabled = true;
                this.groupBox3.Enabled = false;
            }else
            {
                this.groupBox2.Enabled = false;
                this.groupBox3.Enabled = true;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            queryData();
        }
        
        private void queryData()
        {
            if (this.comboBox1.SelectedIndex == -1 || this.comboBox1.SelectedIndex == 0)
            {
                queryByFirst();
            }
            else
            {
                queryBySecond();
            }
        }

        private void queryByFirst()
        {
            string sql = "select * from vwQueryPrint";
            bool where = false;
          
            if (this.txtCd.Text.Trim() != "")
            {
                string temp = " where cidao";
                string sign = ">";
                if (this.cbCd.SelectedIndex == 1)
                {
                    sign = "<";
                }
                if (this.cbCd.SelectedIndex == 2)
                {
                    sign = "=";
                }
                sql += temp + sign + "'" + this.txtCd.Text.Trim() + "' ";
                where = true;
            }
           
            if (this.txtZt.Text.Trim() != "")
            {   
                string sign = ">";
                
                if (this.cbCd.SelectedIndex == 1)
                {
                    sign = "<";
                }else if (this.cbCd.SelectedIndex == 2)
                {
                    sign = "=";
                }
               
                if (where)
                {
                    string temp = " and zaotui";
                    sql += temp + sign + "'" + this.txtZt.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where zaotui ";
                    sql += temp + sign + "'" + this.txtZt.Text.Trim() + "' ";
                    where = true;
                }

            }

          
            if (this.txtKk.Text.Trim() != "")
            {
                string sign = ">";

                if (this.cbKk.SelectedIndex == 1)
                {
                    sign = "<";
                }
                else if (this.cbKk.SelectedIndex == 2)
                {
                    sign = "=";
                }

                if (where)
                {
                    string temp = " and kuangke ";
                    sql += temp + sign + "'" + this.txtKk.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where kuangke  ";
                    sql += temp + sign + "'" + this.txtKk.Text.Trim() + "' ";
                    where = true;
                }

            }


            if (this.txtQj.Text.Trim() != "")
            {
                string sign = ">";

                if (this.cbQj.SelectedIndex == 1)
                {
                    sign = "<";
                }
                else if (this.cbQj.SelectedIndex == 2)
                {
                    sign = "=";
                }

                if (where)
                {
                    string temp = " and qingjia ";
                    sql += temp + sign + "'" + this.txtQj.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where qingjia  ";
                    sql += temp + sign + "'" + this.txtQj.Text.Trim() + "' ";
                    where = true;
                }
            }

            if (this.txtXh.Text.Trim() != "")
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and xuehao ";
                    sql += temp + sign + "'" + this.txtXh.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where xuehao  ";
                    sql += temp + sign + "'" + this.txtXh.Text.Trim() + "' ";
                    where = true;
                }
            }

            if (this.txtXm.Text.Trim() != "")
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and studentName ";
                    sql += temp + sign + "'" + this.txtXm.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where studentName  ";
                    sql += temp + sign + "'" + this.txtXm.Text.Trim() + "' ";
                    where = true;
                }
            }

            if (this.cbBj.SelectedIndex != -1)
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and className ";
                    sql += temp + sign + "'" + this.cbBj.SelectedItem.ToString() + "' ";
                }
                else
                {
                    string temp = " where className  ";
                    sql += temp + sign + "'" + this.cbBj.SelectedItem.ToString() + "' ";
                    where = true;
                }
            }

            if (this.cbXb.SelectedIndex != -1)
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and departName ";
                    sql += temp + sign + "'" + this.cbXb.SelectedItem.ToString() + "' ";
                }
                else
                {
                    string temp = " where departName  ";
                    sql += temp + sign + "'" + this.cbXb.SelectedItem.ToString() + "' ";
                    where = true;
                }
            }
            DataTable dt = DB.getTable(sql);
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("对不起，没找到您要的记录！");
            }
            this.dataGrid1.DataSource = dt;
        }

        private void queryBySecond()
        {
            string sql = "select distinct * from vwQueryAll";
            bool where = false;
            string date = this.dateTimePicker1.Value.ToShortDateString();

            if (this.cbKqtime.SelectedIndex == 0)
            {
                sql+= " where kaoqinTime < '"+date+"' ";
                where = true;
            }
            else if (this.cbKqtime.SelectedIndex == 1)
            {
                sql += " where kaoqinTime > '" + Convert.ToDateTime(date).AddDays(1).ToString() + "' ";
                where = true;
            }
            else if (this.cbKqtime.SelectedIndex == 2)
            {
                sql += " where datename(year,kaoqinTime) = '" + Convert.ToDateTime(date).Year.ToString() + 
                    "' and datename(month,kaoqinTime) = '" + Convert.ToDateTime(date).Month.ToString() + 
                    "'and datename(day,kaoqinTime) = '" + Convert.ToDateTime(date).Day.ToString() + "' ";
                where = true;
            }


            if (this.txtXh2.Text.Trim() != "")
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and xuehao ";
                    sql += temp + sign + "'" + this.txtXh2.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where xuehao  ";
                    sql += temp + sign + "'" + this.txtXh2.Text.Trim() + "' ";
                    where = true;
                }
            }

            if (this.txtXm2.Text.Trim() != "")
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and studentName ";
                    sql += temp + sign + "'" + this.txtXm2.Text.Trim() + "' ";
                }
                else
                {
                    string temp = " where studentName  ";
                    sql += temp + sign + "'" + this.txtXm2.Text.Trim() + "' ";
                    where = true;
                }
            }

            if (this.cbBj2.SelectedIndex !=-1)
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and className ";
                    sql += temp + sign + "'" + this.cbBj2.SelectedItem.ToString() + "' ";
                }
                else
                {
                    string temp = " where studentName  ";
                    sql += temp + sign + "'" + this.txtXm2.Text.Trim() + "' ";
                    where = true;
                }
            }


            if (this.cbXb2.SelectedIndex != -1)
            {
                string sign = "=";
                if (where)
                {
                    string temp = " and departName ";
                    sql += temp + sign + "'" + this.cbXb2.SelectedItem.ToString() + "' ";
                }
                else
                {
                    string temp = " where departName  ";
                    sql += temp + sign + "'" + this.cbXb2.SelectedItem.ToString() + "' ";
                    where = true;
                }
            }
            DataTable dt = DB.getTable(sql);
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("对不起，没找到您要的记录！");
            }
            this.dataGrid1.DataSource = dt;
            this.txtXm2.Text = sql;
        }

        private void BindData()
        {

            string sql = "select * from depart";
            DataTable dt = DB.getTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                this.cbXb.Items.Add(dt.Rows[i]["departName"].ToString().Trim());
                this.cbXb2.Items.Add(dt.Rows[i]["departName"].ToString().Trim());

            }
            dt.Clear();
            sql = "select * from class";
            dt = DB.getTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                this.cbBj.Items.Add(dt.Rows[i]["className"].ToString().Trim());
                this.cbBj2.Items.Add(dt.Rows[i]["className"].ToString().Trim());
            }
        }

        private void query_Load(object sender, EventArgs e)
        {
            BindData();
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
           
        }

        private void cbBj2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}