|
|
Posted on 2008-09-22 20:12 飛雪飄寒 阅读(1189) 评论(4) 编辑 收藏 网摘 所属分类: ASP.NET 、 学习总结
最近,在.net项目中需要实现计算两个日期间的工作日总数,下面简单介绍一下实现过程 !
一、先介绍一下计算逻辑:
1.初始化某一年中的所有周六和周日,比如初始化2008年的所有周六、周日，存入表ConfigDate中；
2.添加周六、周日外的其它节假日，比如国庆节、五一节、元旦等至表ConfigDate中;
3.查询某年某月中的所有节假日，并能对某一节假日进行设置其有效性，比如我某个周六加班，则设置该周六假日无效，实际上该天当计算的时候也是工作日；
4.当输入开始日期和结束日期后，把开始日期至结束日期的每一天与ConfigDate表中所有有效节假日进行对比，如果没有找到则工作日总数加1；
二、计算工作日总数的操作类
  Code
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
}
}
Code
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的结构
Code
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'
五、效果展示
Feedback
楼主的这个思路好像差了点，每数一天都得在ConfigDate里面查。这样你的总时间复杂度就是O（n2）的了。
你何不存几条规则呢，比如:
abstract class Rule
{
public abstract bool Satisfy(DateTime date)
}
class WeekendRule
{
public override bool Satisfy(DateTime date)
{
是周六或周日，返回true，否则返回false
}
}
然后Calendar持有Rule的列表就可以了。
这样时间复杂度低，而且非常灵活
@中华小鹰
谢谢你的建议,你说的没错,我之前的思路和你一样,但在编写代码过程中发现这样计算起来比较麻烦,而用我现在的思路计算起来很简单,思路也非常清晰明了,还有一个更重要的原因是表ConfigDate中存的数据量不会很大,即使时间复杂度高一些,执行起来也并不会慢,并且维护起来很方便.
select * from ConfigDate where state='1'
可以
换成
select * from T_Holiday where state='1' where restDate>='" + BeginDate + "' and restDate<='" + EndDate + "'"
#region 取得工作日数目
public int GetDays(DateTime beginDate, DateTime endDate)
{
System.TimeSpan tsDiffer = endDate.Date - beginDate.Date;
int intDiffer = tsDiffer.Days + 1;
string strSql = "select count(1) from t_holiday where restDate>='" + beginDate + "' and restDate<='" + endDate + "'";
int holidayCount = int.Parse(DbHelperSQL.GetSingle(strSql).ToString());
return intDiffer - holidayCount;
}
#endregion
|