.net实现工作日的计算

最近,在.net项目中需要实现计算两个日期间的工作日总数,下面简单介绍一下实现过程 !

     一、先介绍一下计算逻辑:
     1.初始化某一年中的所有周六和周日,比如初始化2008年的所有周六、周日，存入表ConfigDate中；
     2.添加周六、周日外的其它节假日，比如国庆节、五一节、元旦等至表ConfigDate中;
     3.查询某年某月中的所有节假日，并能对某一节假日进行设置其有效性，比如我某个周六加班，则设置该周六假日无效，实际上该天当计算的时候也是工作日；
     4.当输入开始日期和结束日期后，把开始日期至结束日期的每一天与ConfigDate表中所有有效节假日进行对比，如果没有找到则工作日总数加1；

     二、计算工作日总数的操作类

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace FLX.Portal.Web
{
    /// <summary>    
    /// 计算开始日期到结束日期内的工作日
    /// 2008-09-19
    /// </summary>

    public class CalculateWorkDay
    {

        #region 构造函数
        public CalculateWorkDay()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        #endregion

        #region 计算工作日天数
        public static int CalculateWorkDays(DateTime BeginDate, DateTime EndDate)
        {
            string sql = "select * from ConfigDate where state='1'";
            DataTable dt = new DataTable();//dt为所有有效的休息节假日数据源
            dt = FLX.ORM.BrokerFactory.GetBroker("Yyjcpt").DataAccess.ExecuteDataTable(sql);

            int workdays = 0;//返回值，即EndDate和BeginDate之间的工作日数
            System.TimeSpan tsDiffer = EndDate.Date - BeginDate.Date;//计算EndDate和BeginDate之间相差多少天
            int intDiffer = tsDiffer.Days + 1;//相差天数的int值
            for (int i = 0; i < intDiffer; i++)//从BeginDate开始一天天加，判断临时的日期值是不是节假日，如果不是节假日，则该天为工作日，workdays加1
            {
                DateTime TempDate = BeginDate.Date.AddDays(i);
                if (dt.Rows.Count > 0)
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (TempDate.Date == Convert.ToDateTime(dt.Rows[j]["RestDate"].ToString()).Date)
                        {
                            break;
                        }
                        if (TempDate.Date != Convert.ToDateTime(dt.Rows[j]["RestDate"].ToString()).Date && j == dt.Rows.Count - 1)
                        {
                            workdays++;
                        }
                    }
                }
                else
                {
                    workdays++;
                }
            }
            return workdays;
        }
        #endregion

        #region 调用示例及说明
            //DateTime begindate = Convert.ToDateTime(this.TxtBeginDate.Text.Trim());
            //DateTime enddate = Convert.ToDateTime(this.TxtEndDate.Text.Trim());            
            //int workdays = CalculateWorkDay.CalculateWorkDays(begindate,enddate);
            //this.TxtDays.Text = workdays.ToString();
        #endregion
    }
}

     三、节假日初始化代码

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace FLX.Portal.Web
{
    public partial class InitHoliday : PortalPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                string date = DateTime.Now.Date.Year.ToString();
                this.DDLYear.SelectedValue = date;
            }
        }

        protected void BtnInit_Click(object sender, EventArgs e)
        {
            DateTime begindate = Convert.ToDateTime(this.DDLYear.SelectedValue + "-01-01");
            DateTime enddate = Convert.ToDateTime(this.DDLYear.SelectedValue + "-12-31");
            System.TimeSpan tsDiffer = enddate.Date - begindate.Date;
            int intDiffer = tsDiffer.Days + 1;
            for (int i = 0; i < intDiffer; i++)
            {
                DateTime dtTemp = begindate.Date.AddDays(i);
                if (dtTemp.DayOfWeek == System.DayOfWeek.Sunday)
                {
                    string sqlquery = "select * from ConfigDate where RestDate='" + dtTemp + "'";
                    DataTable dt = new DataTable();
                    dt = FLX.ORM.BrokerFactory.GetBroker("Yyjcpt").DataAccess.ExecuteDataTable(sqlquery);
                    if (dt.Rows.Count == 0)
                    {
                        string sql = "insert into ConfigDate(RestDate,State,Remark) values('" + dtTemp + "','1','星期日')";
                        FLX.ORM.BrokerFactory.GetBroker("Yyjcpt").DataAccess.ExecuteNonQuery(sql);
                    }
                }
                if (dtTemp.DayOfWeek == System.DayOfWeek.Saturday)
                {
                    string sqlquery = "select * from ConfigDate where RestDate='" + dtTemp + "'";
                    DataTable dt = new DataTable();
                    dt = FLX.ORM.BrokerFactory.GetBroker("Yyjcpt").DataAccess.ExecuteDataTable(sqlquery);
                    if (dt.Rows.Count == 0)
                    {
                        string sql = "insert into ConfigDate(RestDate,State,Remark) values('" + dtTemp + "','1','星期六')";
                        FLX.ORM.BrokerFactory.GetBroker("Yyjcpt").DataAccess.ExecuteNonQuery(sql);
                    }
                }
            }
            string strmessage = "<script language=javascript>alert('" + this.DDLYear.SelectedItem.Text + "周末假日初始化成功！')</script>";
            Page.RegisterStartupScript("jump", strmessage);
        }
    }
}

     四、表ConfigDate的结构

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ConfigDate](
    [GUID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ConfigDate_GUID]  DEFAULT (newid()),
    [RestDate] [datetime] NULL,
    [State] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Remark] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_ConfigDate_1] PRIMARY KEY CLUSTERED 
(
    [GUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1表示有效的休息日期，2表示无效的休息日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ConfigDate', @level2type=N'COLUMN', @level2name=N'State'

创建节假日表:

USE [XHManage]
GO

/****** Object:  Table [dbo].[Holiday]    Script Date: 02/20/2014 17:51:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Holiday](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [BeginDate] [datetime] NOT NULL,
 [EndDate] [datetime] NOT NULL,
 [AddUser] [nvarchar](50) NOT NULL,
 [AddTime] [datetime] NOT NULL,
 [Exchange] [bit] NOT NULL,
 CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Holiday] ADD  CONSTRAINT [DF_Holiday_AddTime]  DEFAULT (getdate()) FOR [AddTime]
GO

ALTER TABLE [dbo].[Holiday] ADD  CONSTRAINT [DF_Holiday_Exchange]  DEFAULT ((0)) FOR [Exchange]
GO

 输入数据(2014年国家节假日设置数据):

INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('元旦','2014-1-1','2014-1-1','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('春节','2014-1-31','2014-2-6','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('清明节','2014-4-5','2014-4-7','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('劳动节','2014-5-1','2014-5-3','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('端午节','2014-6-2','2014-6-2','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('中秋节','2014-9-8','2014-9-8','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('国庆节','2014-10-1','2014-10-7','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('春节调休','2014-1-26','2014-1-26','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('春节调休','2014-2-8','2014-2-8','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('劳动节调休','2014-5-4','2014-5-4','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('国庆节调休','2014-9-28','2014-9-28','admin','2014-2-20',1)
INSERT INTO [XHManage].[dbo].[Holiday]([Name] ,[BeginDate] ,[EndDate] ,[AddUser] ,[AddTime] ,[Exchange])VALUES('国庆节调休','2014-10-11','2014-10-11','admin','2014-2-20',1)

 

 

 

go
create function [dbo].[WorkDay]
(
@beginday   datetime,
@endday   datetime
)
returns int
 AS
begin
  --set   datefirst   1
  declare  @caldays   int   
  declare  @id   int
  select   @caldays=0
  
  while  DATEDIFF(d, @beginday,@endday)>=0
      begin
              
          if  datepart(dw,@beginday)>1 and datepart(dw,@beginday)<7
     begin
   SELECT @id=count(*) from Holiday 
   where @beginday between begindate and DATEADD(s,-1,DATEADD(day,1,enddate)) and [Exchange]=0
   if(@id=0)   
    select   @caldays=@caldays+1
     end
     else
     begin
     SELECT @id=count(*) from Holiday 
     where @beginday between begindate and DATEADD(s,-1,DATEADD(day,1,enddate)) and [Exchange]=1
   if(@id>0)
    select   @caldays=@caldays+1
     end
     select   @beginday=dateadd(day,1,@beginday)
      end   
return   @caldays
end

测试:

go
select dbo.[WorkDay]('2014-10-1','2014-10-31')as '工作日'
select dbo.[WorkDay]('2014-9-1','2014-9-30')as '工作日'
select dbo.[WorkDay]('2014-9-30','2014-9-30')as '工作日'
select dbo.[WorkDay]('2014-6-1','2014-6-30')as '工作日'