dateTimePicker日期比较+时间段内查询+员工查询薪资步骤+datagridview

时间段内查询:

button控件

 private void button2_Click(object sender, EventArgs e)
        {
            if ((dTP2.Value.Date-dTP1.Value.Date).TotalDays<0)//后面的日期不能比前面的日期小
            {
                MessageBox.Show("后面的日期不能比前面的日期小,请重选");
                return;
            }
           SqlHelper sqlHelper = new SqlHelper();
           DataTable dt= sqlHelper.selectAttendanceTimeRecord(dTP1.Value.Date, dTP2.Value.Date);
           dGV1.DataSource = dt;
        }

dal->sqlhelper

 public DataTable selectAttendanceTimeRecord(DateTime dateTimePicker1,DateTime dateTimePicker2)
        {
            //下面的State值,可以预定到后面的调用,值是1或0
            string sql = string.Format("SELECT AttendanceRecord.AttendanceId, AttendanceRecord.UserId, demoUsers.TrueName, AttendanceRecord.OnDutyTime, AttendanceRecord.OffDutyTime, AttendanceRecord.OverTimeStart, AttendanceRecord.OverTimeEnd, AttendanceRecord.RecordTime, AttendanceRecord.ComeLate, AttendanceRecord.GoEarly,(CASE AttendanceRecord.ComeLate WHEN 1 THEN '是' ELSE '否' END) AS ComeLateState FROM AttendanceRecord INNER JOIN demoUsers ON AttendanceRecord.UserId = demoUsers.UserId WHERE (AttendanceRecord.RecordTime >= @dateTimePicker1) AND (AttendanceRecord.RecordTime <=@dateTimePicker2)");
            DataConn conn = new DataConn(conStr);
            ArrayList paramlist = new ArrayList();
            paramlist.Add(conn.CreateParameter("@dateTimePicker1",dateTimePicker1 , DbType.Date, 50));
            paramlist.Add(conn.CreateParameter("@dateTimePicker2",dateTimePicker2, DbType.Date, 50));
            IDataParameter[] param = (IDataParameter[])paramlist.ToArray(typeof(IDataParameter));
            return conn.testDataTable(sql, param);
        }

 comboBox控件:

comboBox属性设置 
 1 private void staffSelectPay_Load(object sender, EventArgs e)
 2         {
 3 
 4             DataTable dataYears = new DataTable();
 5             dataYears.Columns.Add("Years", typeof(string));//列字段设置
 6             dataYears.Columns.Add("Value", typeof(string));
 7             dataYears.Rows.Add("2012", "1");//行字段设置
 8             dataYears.Rows.Add("2013", "2");
 9             dataYears.Rows.Add("2014", "3");
10             cbo1.DataSource = dataYears;
11             cbo1.SelectedValue = "2";//默认选择
12             DataTable dataMonths = new DataTable();
13             dataMonths.Columns.Add("Months", typeof(string));//列字段设置
14             dataMonths.Columns.Add("Value", typeof(string));
15             dataMonths.Rows.Add("1月", "01");//行字段设置
16             dataMonths.Rows.Add("2月", "02");
17             dataMonths.Rows.Add("3月", "03");
18             dataMonths.Rows.Add("4月", "04");
19             dataMonths.Rows.Add("5月", "05");
20             dataMonths.Rows.Add("6月", "06");
21             dataMonths.Rows.Add("7月", "07");
22             dataMonths.Rows.Add("8月", "08");
23             dataMonths.Rows.Add("9月", "09");
24             dataMonths.Rows.Add("10月", "10");
25             dataMonths.Rows.Add("11月", "11");
26             dataMonths.Rows.Add("12月", "12");
27             cbo2.DataSource = dataMonths;
28             cbo2.SelectedValue = "01";//默认选择
29         }

 

 

        //根据日期查询自己薪资记录

 1  private void button1_Click(object sender, EventArgs e)
 2         {
 3             //先将年月合并
 4             string dateYearMonth = cbo1.Text.ToString() +"-"+cbo2.SelectedValue.ToString();
 5             SqlHelper sqlHelper = new SqlHelper();
 6             DataTable recordSource = sqlHelper.selectStaffPayRecords(dateYearMonth,gUserInfo.userNP.UserId);
 7             dataGridView1.DataSource = recordSource;
 8             if ((dataGridView1.DataSource as DataTable).Rows.Count == 0)
 9             {
10                 MessageBox.Show("暂无此月份记录");
11                 return;
12             }
13         }

 

员工查询自己的工资记录,有年月 
 1  //员工查询自己的工资记录,有年月
 2         public DataTable selectStaffPayRecords(string dateYearMonth, int UserId)
 3         {
 4             string sql = string.Format("SELECT TOP (200) PayrollRecords.NO, PayrollRecords.UserId, Users.TrueName, PayrollRecords.BasePay, PayrollRecords.Position,PayrollRecords.PaymentDate, PayrollRecords.PayOvertime, PayrollRecords.AmountPayable, PayrollRecords.NetPay, PayrollRecords.Deduct FROM PayrollRecords INNER JOIN Users ON PayrollRecords.UserId = Users.UserId WHERE(CONVERT(varchar(7), PayrollRecords.PaymentDate, 120) = @dateYearMonth)AND(PayrollRecords.UserId = @UserId)");
 5             DataConn conn = new DataConn(conStr);
 6             ArrayList paramlist = new ArrayList();
 7             paramlist.Add(conn.CreateParameter("@dateYearMonth", dateYearMonth, DbType.String, 50));
 8             paramlist.Add(conn.CreateParameter("@UserId",gUserInfo.userNP.UserId, DbType.Int32, 4));
 9             IDataParameter[] param = (IDataParameter[])paramlist.ToArray(typeof(IDataParameter));
10             return conn.testDataTable(sql, param);
11         }

 

 

 

 

 

 

posted @ 2013-04-11 11:06  Jimmy_5  阅读(507)  评论(0)  编辑  收藏  举报