110,502
社区成员
发帖
与我相关
我的任务
分享
private void kaoqin_Load(object sender, EventArgs e)
{
comboBox1.Items.Add("早上");
comboBox1.Items.Add("中午");
comboBox1.Items.Add("下午");
comboBox1.Items.Add("晚上");
comboBox1.Items.Add("半夜");
}
private DateTime time3;
private void button1_Click(object sender, EventArgs e)
{
try
{
#region 根据条件查询
string timehour1 = "";
string timehour2 = "";
string timehour3 = "";
string txt = comboBox1.SelectedItem.ToString();
if (txt == "早上")
{
timehour1 = "06:30:00";
timehour2 = "12:00:00";
timehour3 = "08:30:00";
}
else if (txt == "中午")
{
timehour1 = "13:30:00";
timehour2 = "15:59:59";
timehour3 = "13:30:00";
}
else if (txt == "下午")
{
timehour1 = "16:00:00";
timehour2 = "20:00:00";
timehour3 = "18:00:00";
}
else if (txt == "晚上")
{
timehour1 = "20:00:00";
timehour2 = "23:59:59";
timehour3 = "21:00:00";
}
else if (txt == "半夜")
{
timehour1 = "00:00:00";
timehour2 = "05:59:59";
}
string timedate1 = dateTimePicker1.Value.ToString("yyyy-MM-dd ");
string time1 = timedate1 + timehour1;
string time2 = timedate1 + timehour2;
time3 = Convert.ToDateTime(timedate1 + timehour3);
string sql = "select id,name, userID, min(time) from test where time > '" + time1 + "' and time < '" + time2 + "' group by userID";
DbHelper db = new DbHelper();
dataGridView1.DataSource = db.GetDataSet(sql).Tables[0];
#endregion
#region 得到迟到早退列
int row = dataGridView1.Rows.Count;//得到总行数
for (int i = 0; i < row; i++)//循环表
{
DateTime dateTime = Convert.ToDateTime(dataGridView1.Rows[i].Cells["mintime"].Value);//取得mintime时间
if (txt == "早上" )
{
if((dateTime - time3).TotalMinutes < 0)
{
dataGridView1.Rows[i].Cells["beLate"].Value = "";
}
else
{
dataGridView1.Rows[i].Cells["beLate"].Value = (dateTime - time3).TotalMinutes.ToString("f0");
}
}
else if (txt == "中午")
{
if((dateTime - time3).TotalMinutes >0&& (dateTime - time3).TotalMinutes < 5)
{
dataGridView1.Rows[i].Cells["beLate"].Value = "";
}
else
{
dataGridView1.Rows[i].Cells["beLate"].Value = (dateTime - time3).TotalMinutes.ToString("f0");
}
}
else if (txt == "下午 ")
{
if ((dateTime - time3).TotalMinutes < 0)
{
dataGridView1.Rows[i].Cells["beLate"].Value = (System.Math.Abs((dateTime - time3).TotalMinutes)).ToString();
}
else if((dateTime - time3).TotalMinutes>65)
{
dataGridView1.Rows[i].Cells["beLate"].Value = (dateTime - time3).TotalMinutes.ToString("f0");
}
}
else if (txt == "晚上")
{
if((dateTime - time3).TotalMinutes >0&& (dateTime - time3).TotalMinutes < 60)
{
dataGridView1.Rows[i].Cells["jiaban"].Value = "120";
}
else if((dateTime - time3).TotalMinutes > 60)
{
dataGridView1.Rows[i].Cells["jiaban"].Value = "180";
}
}
else if (txt == "半夜")
{
dataGridView1.Rows[i].Cells["jiaban"].Value = "180";
}
}
#endregion
dataGridView1.AllowUserToAddRows = false;
}
catch
{
MessageBox.Show("时间错误");
}
}
private void buttonExport_Click(object sender, EventArgs e)
{
}
#region
public DataSet GetDataSet(string sql)
{
using (conn = new MySqlConnection(connString))
{
conn.Open();
}
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
#endregion
string sql = "select id,name, userID, min(time) from test where time > '" + time1 + "' and time < '" + time2 + "' group by userID";
DbHelper db = new DbHelper();
dataGridView1.DataSource = db.GetDataSet(sql).Tables[0];
如果不是你写的代码,看看DbHelper 类中应该有Executexxx之类的方法供调用,看懂什么用法你的需求就解决了。