Ejemplo n.º 1
0
        private void textBox1_TextChanged(object sender, EventArgs ea)
        {
            if (txtBox_name.Text.EndsWith("*"))
            {
                mark++;
                string name = txtBox_name.Text.Split('*')[0];

                //通过学员姓名查询学员信息
                MySqlConnection con = DBConnector.getcon();
                con.Open();
                string          sql    = "select * from student where stuname = '" + name + "'";
                MySqlCommand    comm   = new MySqlCommand(sql, con);
                MySqlDataReader reader = comm.ExecuteReader();
                student         stu    = new student();
                try
                {
                    if (!reader.HasRows)
                    {
                        MessageBox.Show("无此学员信息!");
                        return;
                    }
                    while (reader.Read())
                    {
                        stu.id                 = reader.GetInt32(0);
                        stu.stuname            = reader.GetString(1);
                        stu.englishname        = reader.GetString(2);
                        stu.classtime          = reader.GetInt32(3);
                        stu.signtime           = reader.GetInt32(4);
                        stu.classcode          = reader.GetString(5);
                        stu.singleclasstime    = reader.GetInt32(6);
                        stu.remainderclasstime = reader.GetInt32(7);
                    }
                }
                catch
                {
                    MessageBox.Show("查询学员信息失败!");
                }
                reader.Close();

                //判断此学员是今天第几次打卡
                bool            isSign  = false;
                string          sql2    = "select * from signlist where stuid = " + stu.id + " and signtime >= '" + DateTime.Now.ToString().Split(' ')[0] + "'";
                MySqlCommand    comm2   = new MySqlCommand(sql2, con);
                MySqlDataReader reader2 = comm2.ExecuteReader();
                try
                {
                    //今天没有当前学员的打卡记录
                    if (!reader2.HasRows)
                    {
                        stu.signtime++;
                        isSign = false;
                    }
                    else
                    {
                        isSign = true;
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show("查询当前学员打卡记录失败!---" + e.Message);
                }
                finally
                {
                    reader2.Close();
                }

                //保存此学员打卡信息

                if (!isSign)
                {
                    stu.remainderclasstime = stu.remainderclasstime - stu.singleclasstime;
                }
                string       sql1  = "update student set signtime = " + stu.signtime + ",remainderclasstime = " + stu.remainderclasstime + " where id = '" + stu.id + "'";
                MySqlCommand comm1 = new MySqlCommand(sql1, con);
                try
                {
                    if (comm1.ExecuteNonQuery() > 0)
                    {
                        //
                    }
                    else
                    {
                        MessageBox.Show("保存学员打卡信息失败!");
                    }
                }
                catch
                {
                    MessageBox.Show("保存学员打卡信息失败!");
                }

                string       now   = DateTime.Now.ToString();
                string       sql3  = "insert into signlist(signtime,stuid) values('" + now + "'," + stu.id + ")";
                MySqlCommand comm3 = new MySqlCommand(sql3, con);
                try
                {
                    if (comm3.ExecuteNonQuery() > 0)
                    {
                        //
                    }
                    else
                    {
                        MessageBox.Show("保存打卡信息失败!");
                    }
                }
                catch
                {
                    MessageBox.Show("保存打卡信息失败!");
                }
                finally {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                //显示打卡信息
                signList.BeginUpdate();
                ListViewItem lvi1 = new ListViewItem();
                lvi1.Text = mark.ToString();
                lvi1.SubItems.Add(stu.classcode);
                lvi1.SubItems.Add(stu.stuname);
                lvi1.SubItems.Add(stu.englishname);
                lvi1.SubItems.Add(DateTime.Now.ToString().Split(' ')[1]);
                lvi1.SubItems.Add(stu.signtime.ToString());
                lvi1.SubItems.Add(stu.remainderclasstime.ToString());
                lvi1.SubItems.Add(stu.classtime.ToString());
                signList.Items.Add(lvi1);
                signList.EndUpdate();
                txtBox_name.Text = "";
            }
        }
Ejemplo n.º 2
0
        private void btn_get_Click_1(object sender, EventArgs ea)
        {
            string beginDate = datetime_begin.Text;
            string endDate   = datetime_end.Text;
            string classcode = txtbox_classcode.Text;
            string stuname   = txtbox_stuname.Text;

            if (beginDate.Equals("") && endDate.Equals("") && classcode.Equals("") && stuname.Equals(""))
            {
                MessageBox.Show("请输入查询条件!");
                return;
            }
            else
            {
                listview_record.Items.Clear();
                listview_record.Refresh();

                MySqlConnection con = DBConnector.getcon();
                con.Open();
                //首先查询出符合条件的学员
                string sql1 = "select * from student where 1=1";
                if (!stuname.Equals(""))
                {
                    sql1 += " and stuname = '" + stuname + "'";
                }
                if (!classcode.Equals(""))
                {
                    sql1 += " and classcode = '" + classcode + "'";
                }
                MySqlCommand    comm1       = new MySqlCommand(sql1, con);
                MySqlDataReader reader1     = comm1.ExecuteReader();
                List <student>  studentList = new List <student>();
                try
                {
                    if (reader1.HasRows)
                    {
                        while (reader1.Read())
                        {
                            student stu = new student();
                            stu.id                 = reader1.GetInt32(0);
                            stu.stuname            = reader1.GetString(1);
                            stu.englishname        = reader1.GetString(2);
                            stu.classtime          = reader1.GetInt32(3);
                            stu.signtime           = reader1.GetInt32(4);
                            stu.classcode          = reader1.GetString(5);
                            stu.singleclasstime    = reader1.GetInt32(6);
                            stu.remainderclasstime = reader1.GetInt32(7);
                            studentList.Add(stu);
                        }
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show("查询学员信息失败:" + e.Message);
                }
                finally
                {
                    reader1.Close();
                }
                //单独列出符合条件的学员ID
                List <int> stuidList = new List <int>();
                string     idstring  = "";
                foreach (student stu in studentList)
                {
                    stuidList.Add(stu.id);
                    idstring += "'" + stu.id.ToString() + "',";
                }
                if (!idstring.Equals(""))
                {
                    idstring = idstring.Substring(0, idstring.Length - 1);
                }

                //防止起截止时间为空
                if (beginDate.Equals(""))
                {
                    beginDate = "1949-01-01";
                }
                if (endDate.Equals(""))
                {
                    endDate = "9999-12-31";
                }

                string          sql2     = "select * from signlist where signtime >= '" + beginDate + "' and signtime <= '" + endDate + "'";
                MySqlCommand    comm2    = new MySqlCommand(sql2, con);
                MySqlDataReader reader2  = comm2.ExecuteReader();
                List <signlist> Signlist = new List <signlist>();
                while (reader2.Read())
                {
                    signlist list = new signlist();
                    list.id       = reader2.GetInt32(0);
                    list.signtime = reader2.GetString(1);
                    list.stuid    = reader2.GetInt32(2);
                    Signlist.Add(list);
                }
                reader2.Close();
                List <record> records = new List <record>();
                //MySqlDataReader reader3;
                foreach (signlist sign in Signlist)
                {
                    record record = new record();
                    int    stuid  = sign.stuid;
                    foreach (student stu in studentList)
                    {
                        if (stu.id == stuid)
                        {
                            record.stuname            = stu.stuname;
                            record.englishname        = stu.englishname;
                            record.classcode          = stu.classcode;
                            record.signdate           = sign.signtime;
                            record.signtime           = stu.signtime;
                            record.classtime          = stu.classtime;
                            record.remainderclasstime = stu.remainderclasstime;
                            records.Add(record);
                        }
                    }
                }

                int mark = 0;
                listview_record.BeginUpdate();
                foreach (record record in records)
                {
                    mark++;
                    ListViewItem lvi1 = new ListViewItem();
                    lvi1.Text = mark.ToString();
                    lvi1.SubItems.Add(record.classcode);
                    lvi1.SubItems.Add(record.stuname);
                    lvi1.SubItems.Add(record.englishname);
                    lvi1.SubItems.Add(record.signdate);
                    lvi1.SubItems.Add(record.signtime.ToString());
                    lvi1.SubItems.Add(record.classtime.ToString());
                    lvi1.SubItems.Add(record.remainderclasstime.ToString());
                    listview_record.Items.Add(lvi1);
                }
                listview_record.EndUpdate();
            }
        }