OA系统

开发平台：
ASP/ASPX

load.aspx.cs：源码内容
							﻿using System;
using System.Text;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class web_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        int id = 1;
        ArrayList num1 = new ArrayList();
        ArrayList num2 = new ArrayList();
        ArrayList status = new ArrayList();
        decimal T1 = 113000;
        decimal T2 = 130000;
        decimal T3 = 150000;
        decimal T4 = 173000;
        SqlConnection conn = dbConnection.getConnection();
        SqlCommand sqlcomm1 = new SqlCommand("select distinct data from dbo.OA_KAOQIN where data between '" + TextBox1.Text + "'and'" + TextBox2.Text+"'", conn);
        conn.Open();
        SqlDataReader comm1 = sqlcomm1.ExecuteReader();
        while (comm1.Read())
        {
            num1.Add(Convert.ToString(comm1["data"]));
        }
        comm1.Close();
        SqlCommand sqlcomm6 = new SqlCommand("select distinct  card_id from dbo.OA_KAOQIN", conn);
        SqlDataReader comm6 = sqlcomm6.ExecuteReader();
        while (comm6.Read())
        {
            num2.Add(comm6["card_id"]);
        }
        comm6.Close();
     //  SqlCommand sqlcomm8 = new SqlCommand("select status from dbo.OA_KAOQIN where status=0", conn);
     //   SqlDataReader comm8 = sqlcomm8.ExecuteReader();
     //   while (comm8.Read())
      //  {
    //        status.Add(comm8["status"].ToString());
    //    }
    //    comm8.Close();
        SqlCommand cmd2 = new SqlCommand("select max(id) from dbo.OA_KAOQIN_jl ", conn);
        // id =Convert.ToInt32(cmd2.ExecuteScalar())+1;
        if (!cmd2.ExecuteScalar().ToString().Equals("")) { id = Convert.ToInt32(cmd2.ExecuteScalar()) + 1; }
          foreach (string i in num1)
            {
                foreach (string j in num2)
                {
                    string time1 = null;
                    string time2 = null;
                    string time3 = null;
                    string time4 = null;
                    SqlCommand sqlcomm2 = new SqlCommand("select min(card_time) as card_time from dbo.OA_KAOQIN where data ='" + i + "'and card_id = " + j + " and card_time <" + T1 , conn);
                    SqlDataReader comm2 = sqlcomm2.ExecuteReader();
                    while (comm2.Read())
                    {
                        time1 = Convert.ToString(comm2["card_time"]);
                    }
                    comm2.Close();
                    SqlCommand sqlcomm3 = new SqlCommand("select min(card_time) as card_time from dbo.OA_KAOQIN where data = '" + i + "'and card_id = " + j + "and card_time >" + T1 + "and card_time<" + T2 , conn);
                    SqlDataReader comm3 = sqlcomm3.ExecuteReader();
                    while (comm3.Read())
                    {
                        time2 = Convert.ToString(comm3["card_time"]);
                    }
                    comm3.Close();
                    SqlCommand sqlcomm4 = new SqlCommand("select max(card_time) as card_time from dbo.OA_KAOQIN where data = '" + i + "'and card_id = " + j + "and card_time >" + T1 + "and card_time <" + T3 , conn);
                    SqlDataReader comm4 = sqlcomm4.ExecuteReader();
                    while (comm4.Read())
                    {
                        time3 = Convert.ToString(comm4["card_time"]);
                    }
                    comm4.Close();
                    SqlCommand sqlcomm5 = new SqlCommand("select max(card_time) as card_time from dbo.OA_KAOQIN where data = '" + i + "'and card_id = " + j + "and card_time > " + T3 + "and card_time < " + T4  , conn);
                    SqlDataReader comm5 = sqlcomm5.ExecuteReader();
                    while (comm5.Read())
                    {
                        time4 = Convert.ToString(comm5["card_time"]);
                    }
                    comm5.Close();
                    SqlCommand sqlcomm7 = new SqlCommand("select max(card_time) as card_time from dbo.OA_KAOQIN where data = '" + i + "'and card_id = " + j + "and card_time > " + T4, conn);
                    SqlDataReader comm7 = sqlcomm7.ExecuteReader();
                    while (comm7.Read())
                    {
                        time4 = Convert.ToString(comm7["card_time"]);
                    }
                    comm7.Close();
                    if (time1.Length == 5)
                    {
                        time1 = "0".ToString()+ time1;
                    }
                    if (time2.Length == 5)
                    {
                        time2 = "0" + time2;
                    }
                    if(time3.Length==5)
                    {
                        time3 = "0" + time3;
                    }
                    if (time4.Length == 5)
                    {
                        time4 = "0" + time4;
                    }
                    try
                    {
                        SqlCommand cmd = new SqlCommand("insert into OA_KAOQIN_JL(id ,data , card_id,zao, wan, wuxia,wushang )values('" + id + "','" + i.Substring(0,10) + "','" + j + "','" + time1 + "','" + time4 + "','" + time2 + "','" + time3 + "')", conn);
                        cmd.ExecuteNonQuery();
                        Response.Write("<script>更新成功!</script>");
                        Response.Write("更新成功!");
                    }
                    catch (Exception e1)
                    {
                        Response.Write(e1.Message);
                        Response.Write("<script>更新失败!</script>");
                        Response.Write("更新失败!");
                    }
                    id++;
                }
            } conn.Close();
        
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = dbConnection.getConnection();
        con.Open();
        ArrayList id_record = new ArrayList();//记录待更新的值
        ArrayList card_time = new ArrayList();//待更新的值的打卡时间
        ArrayList zao1 = new ArrayList();//待更新值打卡时间的早班值
        ArrayList wan1 = new ArrayList();//待更新值打卡时间的晚班值
        ArrayList wushang1 = new ArrayList();
        ArrayList wuxia1 = new ArrayList();
        ArrayList id1=new ArrayList ();
        ArrayList id2 = new ArrayList();
        SqlCommand cmd = new SqlCommand("select zao from oa_kaoqin_gd ", con);
        string zao = cmd.ExecuteScalar().ToString();
        cmd = new SqlCommand("select wan from oa_kaoqin_gd ", con);
        string wan = cmd.ExecuteScalar().ToString();
        cmd = new SqlCommand("select wushang from oa_kaoqin_gd ", con);
        string wushang = cmd.ExecuteScalar().ToString();
        cmd = new SqlCommand("select wuxia from oa_kaoqin_gd ", con);
        string wuxia = cmd.ExecuteScalar().ToString();
        cmd = new SqlCommand("select id from oa_kaoqin where status=0",con);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            id_record.Add(dr["id"].ToString());
          
        }
        dr.Close();
        for (int i = 0; i < id_record.Count; i++)
        {
            cmd = new SqlCommand("select card_time from oa_kaoqin where id=" + id_record[i], con);
            SqlDataReader dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                card_time.Add(dr1["card_time"]);
            }
            dr1.Close();
        }
        //记录早上打卡的
        for (int j2 = 0; j2 < card_time.Count; j2++)
        {
            cmd = new SqlCommand("select id from oa_kaoqin where card_time<"+zao+"and id="+id_record[j2],con);
            SqlDataReader dr2 = cmd.ExecuteReader();
           while (dr2.Read())
            {
                id1.Add(dr2["id"].ToString());
            }
            
            dr2.Close();
        }
        //记录午下
        for (int j3 = 0; j3 < card_time.Count; j3++)
        {
            cmd = new SqlCommand("select id from oa_kaoqin where card_time>" 
                + wuxia+"and card_time<"+wushang+"and id="+id_record[j3], con);
            SqlDataReader dr3 = cmd.ExecuteReader();
            while (dr3.Read())
            {               
              id1.Add(dr3["id"].ToString());
            }
            dr3.Close();
        }
        //记录中午上班的
        for (int j4 = 0; j4 < card_time.Count; j4++)
        {
            cmd = new SqlCommand("select id from oa_kaoqin where card_time>" +
                wuxia+"and card_time<"+wushang+"and id="+id_record[j4], con);
            SqlDataReader dr4 = cmd.ExecuteReader();
            while (dr4.Read())
            {
                id1.Add(dr4["id"].ToString());
            }
            dr4.Close();
        }
        //记录下午下班的
        for (int j5 = 0; j5 < card_time.Count; j5++)
        {
            cmd = new SqlCommand("select id from oa_kaoqin where card_time>" + wan, con);
            SqlDataReader dr5 = cmd.ExecuteReader();
            while (dr5.Read())
            {
                id1.Add(dr5["id"].ToString());
            }
            dr5.Close();
        }
        //没迟到的
        for (int k = 0; k < id1.Count; k++)
        {
            cmd = new SqlCommand("update oa_kaoqin set status=1 where id=" + id1[k], con);
            cmd.ExecuteNonQuery();
        }
        //异常的
        cmd = new SqlCommand("select id from oa_kaoqin where status=0",con);
        SqlDataReader dr6 = cmd.ExecuteReader();
        while (dr6.Read()) 
        {
            id2.Add(dr6["id"].ToString());
        }
        dr6.Close();
        for (int k1 = 0; k1< id2.Count; k1++)
        {
            cmd = new SqlCommand("update oa_kaoqin set status=2 where id=" + id2[k1], con);
            cmd.ExecuteNonQuery();
        }
    }
    protected void TextBox1_TextChanged(object sender, EventArgs e)
    {
    }
    protected void TextBox2_TextChanged(object sender, EventArgs e)
    {
    }
}

						