当前文件： AutoDutySystem/App_Code/DAL/DutyDao.cs[5K，2009-6-12 11:33:11]，打开代码结构图

		            
  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Web;
  5using System.Web.Security;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Web.UI.WebControls.WebParts;
  9using System.Web.UI.HtmlControls;
 10//5_1_a_s_p_x.c_o_m
 11
 12/**//// <summary>
 13/// DutyDao 的摘要说明
 14/// </summary>
 15public class DutyDao
 16...{
 17    public DutyDao()
 18    ...{
 19        //
 20        // TODO: 在此处添加构造函数逻辑
 21        //
 22    }
 23
 24
 25
 26    public static DataSet dsDuty(int year,int begin,int end)
 27    ...{
 28        string sql = "select StaffName,count(a.id) as sum from dutystaff a,dutydetail b where a.id = b.staffid and datepart(yyyy,dutydate)=" + year + " and dutymonth between " + begin + " and " + end + " group by a.id,a.staffname";
 29        return Sqlcommon.dsBySql(sql);
 30    }
 31
 32    public static DataSet dsDuty(int year)
 33    ...{
 34        string sql = "select b.id ,a.staffname,b.dutydate,b.dutymonth,b.dutydayofweek as week,(case dutytype when 0 then '白班' when 1 then '夜班' end) as dutytype from dutystaff a,dutydetail b where a.id = b.staffid and datepart(yyyy,dutydate)="+year+"";
 35        return Sqlcommon.dsBySql(sql);
 36    }
 37
 38    public static DataSet dsDuty(DateTime date)
 39    ...{
 40        string sql = "select b.id ,a.staffname,b.dutydate,b.dutymonth,b.dutydayofweek as week,(case dutytype when 0 then '白班' when 1 then '夜班' end) as dutytype from dutystaff a,dutydetail b where a.id = b.staffid and dutydate='"+date+"'";
 41        return Sqlcommon.dsBySql(sql);
 42    }
 43
 44
 45    /**//// <summary>
 46    /// 排班的逻辑是这样的：按年份自动排班，值班的员工自动轮流值班，周六，周日排两班
 47    /// 
 48    /// 
 49    /// 
 50    /// 
 51    /// </summary>
 52    /// <param name="year"></param>
 53    public static void inertDuty(int year)
 54    ...{
 55
 56        if (!UtilCommon.isValiddDataSet(StaffDao.dsStaff()))
 57        ...{
 58            return;
 59        }
 60        DataTable dt = StaffDao.dsStaff().Tables[0];
 61        DateTime dtBegin = DateTime.Parse(year + "-1-1");//一年的开始时间
 62        DateTime dtEnd = DateTime.Parse(year + "-12-31");//一年的结束时间
 63        int i, j;
 64        i = j = 0;
 65        string sql = string.Empty;
 66
 67        //插入之前先删除值班信息，以免重复插入
 68        sql = "delete from dutydetail where datepart(yyyy,dutydate)="+year+"";
 69        Sqlcommon.executeNonQuery(sql);
 70
 71        //排班开始，循环一年的时间
 72        while (dtBegin <= dtEnd)
 73        ...{
 74            //周六排两班
 75            if (dtBegin.DayOfWeek.ToString().Equals("Saturday"))
 76            ...{
 77
 78                if (i >= dt.Rows.Count)
 79                ...{
 80                    j = 0;
 81                }
 82                sql = "insert into dutydetail(staffid,dutydate,dutymonth,dutydayofweek,dutytype) values(" + UtilCommon.stringToInt(dt.Rows[j++][0].ToString()) + ",'" + dtBegin + "'," + dtBegin.Month + ",'" + dtBegin.DayOfWeek.ToString() + "',0)";
 83                Sqlcommon.executeNonQuery(sql);
 84
 85                i = j;
 86                if (i >= dt.Rows.Count)
 87                ...{
 88                    j = 0;
 89                }
 90                sql = "insert into dutydetail(staffid,dutydate,dutymonth,dutydayofweek,dutytype) values(" + UtilCommon.stringToInt(dt.Rows[j++][0].ToString()) + ",'" + dtBegin + "'," + dtBegin.Month + ",'" + dtBegin.DayOfWeek.ToString() + "',1)";
 91                Sqlcommon.executeNonQuery(sql);
 92                
 93                i = j;
 94
 95            }
 96               
 97            else if (dtBegin.DayOfWeek.ToString().Equals("Sunday"))//周日排两班
 98            ...{
 99
100                if (i >= dt.Rows.Count)
101                ...{
102                    j = 0;
103                }
104                sql = "insert into dutydetail(staffid,dutydate,dutymonth,dutydayofweek,dutytype) values(" + UtilCommon.stringToInt(dt.Rows[j++][0].ToString()) + ",'" + dtBegin + "'," + dtBegin.Month + ",'" + dtBegin.DayOfWeek.ToString() + "',0)";
105                Sqlcommon.executeNonQuery(sql);
106                i = j;
107                if (i >= dt.Rows.Count)
108                ...{
109                    j = 0;
110                }
111                sql = "insert into dutydetail(staffid,dutydate,dutymonth,dutydayofweek,dutytype) values(" + UtilCommon.stringToInt(dt.Rows[j++][0].ToString()) + ",'" + dtBegin + "'," + dtBegin.Month + ",'" + dtBegin.DayOfWeek.ToString() + "',1)";
112                Sqlcommon.executeNonQuery(sql);
113                
114                i = j;
115            }
116            else
117            ...{
118                //其余排一班
119                if (i >= dt.Rows.Count)
120                ...{
121                    j = 0;
122                }
123
124                sql = "insert into dutydetail(staffid,dutydate,dutymonth,dutydayofweek,dutytype) values(" + UtilCommon.stringToInt(dt.Rows[j++][0].ToString()) + ",'" + dtBegin + "'," + dtBegin.Month + ",'" + dtBegin.DayOfWeek.ToString() + "',1)";
125                Sqlcommon.executeNonQuery(sql);
126                i = j;
127
128            }
129
130            //递增日期
131            dtBegin = dtBegin.AddDays(1);
132        }
133
134
135    }
136   
137}
138