最近做项目中，发现这两个方法非常不错，记下来。以备后用
测试
运行结果
[img]http://dl2.iteye.com/upload/attachment/0089/9667/3c2745ed-302b-3b3e-bef1-0a6103148fe5.jpg[/img]
/// <summary>
/// //按自然月计算应收日期
/// </summary>
public partial class WYTableFunction
{
public enum enumPeroiod
{
NotSplit=0,//不分隔，起始与截止整个是一周期。
First=1,//只取第一期
AllPeroid=2//取所有周期。
}
//如果有应收日，那就以合同的应收日为准，否刚就为应收日期就为自然月最后一天
/// <summary>
/// 计算应收日期及所属期
/// CybleBeginDate:周期实际的起始日
/// CybleEndDate:周期实际的结束日。
/// </summary>
/// <param name="UnitType">周期单位</param>
/// <param name="Cycle">周期</param>
/// <param name="sBeginDate">起始日期</param>
/// <param name="sEndDate">截止日期</param>
/// <param name="ExesType">应收类别,1代表提前,2代表本期,3代表延后</param>
/// <param name="ExesMonth">指定月份,0代表本期</param>
/// <param name="AccDate">指定应收天数,0代表每期最后一天</param>
/// <returns></returns>
[SqlFunction(TableDefinition = @" BeginDate DateTime,EndDate DateTime,GatheringDate DateTime,CybleBeginDate DateTime,CycleEndDate DateTime", FillRowMethodName = "FillRow2")]
public static IEnumerable CW_F_CalDateTable_CLR(int UnitType, int Cycle, string sBeginDate,
string sEndDate, int ExesType, int ExesMonth, int AccDate, int iEnumPeroiod,bool ReturnDataRow)
{
enumPeroiod Peroid = (enumPeroiod)iEnumPeroiod;
DateTime BeginDate = Convert.ToDateTime(sBeginDate);
DateTime EndDate = Convert.ToDateTime(sEndDate);
DateTime AccountDate = DateTime.MinValue;
List<DateTime[]> rowList = new List<DateTime[]>();
DateTime dtBegin = BeginDate, dtEnd = DateTime.MinValue;
DateTime dtCybleBeginDate = new DateTime(dtBegin.Year, dtBegin.Month, 1), dtCycleEndDate = EndDate;
//Globe.SendString(string.Format("生成周期，从{0}至{1}",BeginDate,EndDate));
switch (UnitType)
{
#region 年交
case 1://年
while (dtBegin <= EndDate)
{
if (Peroid == enumPeroiod.NotSplit)
{
dtEnd = EndDate;
}
else
{
/*2011-6-14,首期，是算到当前期间的最后一天，之后才是加一个周期*/
if (dtEnd == DateTime.MinValue)
dtEnd = new DateTime(dtBegin.Year, 12, 31).AddYears(Cycle - 1);//按自然月，则首期,加上周期减1（有可能是每2年或每3年）
else
dtEnd = new DateTime(dtEnd.Year, 12, 31).AddYears(Cycle);
}
dtCycleEndDate = dtEnd;
dtCybleBeginDate = dtEnd.AddDays(1).AddYears(-Cycle);//例：2011-12-31，变2012-1-1，再减1年（设Cycle为1）
if (dtEnd > EndDate)
dtEnd = EndDate;
AccountDate = CalAccountDate(dtBegin, dtEnd, ExesType, ExesMonth, AccDate);
rowList.Add(new DateTime[] { dtBegin, dtEnd, AccountDate,dtCybleBeginDate, dtCycleEndDate });
if (Peroid!=enumPeroiod.AllPeroid) break;
dtBegin = dtEnd.AddDays(1);
}
break;
#endregion
#region 季交
case 2://季
while (dtBegin <= EndDate)
{
if (Peroid == enumPeroiod.NotSplit)
{
dtEnd = EndDate;
}
else
{
if (dtEnd == DateTime.MinValue)
dtEnd = new DateTime(dtBegin.Year, dtBegin.Month, 1).AddMonths((Cycle - 1) * 3);
else
dtEnd = new DateTime(dtEnd.Year, dtEnd.Month, 1).AddMonths(Cycle * 3);
switch (dtEnd.Month)
{
case 1:
case 2:
case 3:
dtEnd = new DateTime(dtEnd.Year, 3, 31);
break;
case 4:
case 5:
case 6:
dtEnd = new DateTime(dtEnd.Year, 6, 30);
break;
case 7:
case 8:
case 9:
dtEnd = new DateTime(dtEnd.Year, 9, 30);
break;
case 10:
case 11:
case 12:
dtEnd = new DateTime(dtEnd.Year, 12, 31);
break;
}
}
dtCycleEndDate = dtEnd;
dtCybleBeginDate = dtEnd.AddDays(1).AddMonths(-(Cycle * 3));//倒退到起始日。例：结束日期是2011-3-31，则先变为2011-4-1,再减一季（3个月），则变为2011-1-1
if (dtEnd > EndDate)
dtEnd = EndDate;
AccountDate = CalAccountDate(dtBegin, dtEnd, ExesType, ExesMonth, AccDate);
rowList.Add(new DateTime[] { dtBegin, dtEnd, AccountDate, dtCybleBeginDate, dtCycleEndDate });
if (Peroid != enumPeroiod.AllPeroid) break;
dtBegin = dtEnd.AddDays(1);
}
break;
#endregion
#region 月交
case 3://月
while (dtBegin <= EndDate)
{
if (Peroid == enumPeroiod.NotSplit)
{
dtEnd = EndDate;
}
else
{
if (dtEnd == DateTime.MinValue)
dtEnd = new DateTime(dtBegin.Year, dtBegin.Month, 1).AddMonths(Cycle).AddDays(-1);//本
else
dtEnd = new DateTime(dtEnd.Year, dtEnd.Month, 1).AddMonths(Cycle + 1).AddDays(-1);//注意：这个dtEnd，每次都是上一周期所在月的最后一天，所以取当月，加上周期Cycle加1，得到后月（设Cycle=1)第1天，再减1天。
}
dtCycleEndDate = dtEnd;
dtCybleBeginDate = new DateTime(dtCycleEndDate.Year, dtCycleEndDate.Month, 1);
if (dtEnd > EndDate)
dtEnd = EndDate;
//Globe.SendString(string.Format("计算自然月时：起始{0:yyyy-MM-dd},结束{1:yyyy-MM-dd},周期：{2}", dtBegin,dtEnd,Cycle));
AccountDate = CalAccountDate(dtBegin, dtEnd, ExesType, ExesMonth, AccDate);
//Globe.SendString(string.Format("计算应收日：ExesType={0},ExesMonth={1},AccDate={2},AccountDate={3:yyyy-MM-dd}", ExesType, ExesMonth, AccDate, AccountDate));
rowList.Add(new DateTime[] { dtBegin, dtEnd, AccountDate, dtCybleBeginDate, dtCycleEndDate });
if (Peroid != enumPeroiod.AllPeroid) break;
dtBegin = dtEnd.AddDays(1);
}
break;
#endregion
#region 周交
case 4://周
while (dtBegin <= EndDate)
{
/*2011-6-15，这里有些问题没有按星期来计周。以后再改,ljh*/
if (Peroid == enumPeroiod.NotSplit)
{
dtEnd = EndDate;
}
else
{
dtEnd = dtBegin.AddDays(Cycle * 7);
}
dtCycleEndDate = dtEnd.AddDays(-1);
dtCybleBeginDate = dtCycleEndDate.AddDays(-7 * Cycle);
if (dtEnd > EndDate)
dtEnd = EndDate;
else
dtEnd = dtEnd.AddDays(-1);
AccountDate = CalAccountDate(dtBegin, dtEnd, ExesType, ExesMonth, AccDate);
rowList.Add(new DateTime[] { dtBegin, dtEnd, AccountDate, dtCybleBeginDate, dtCycleEndDate });
if (Peroid != enumPeroiod.AllPeroid) break;
dtBegin = dtEnd.AddDays(1);
}
break;
#endregion
#region 日交
case 5://日
while (dtBegin <= EndDate)
{
dtEnd = dtBegin.AddDays(Cycle);
if (dtEnd > EndDate)
dtEnd = EndDate;
else
dtEnd = dtEnd.AddDays(-1);
dtCycleEndDate = dtBegin;
dtCybleBeginDate = dtBegin;
AccountDate = CalAccountDate(dtBegin, dtEnd, ExesType, ExesMonth, AccDate);
rowList.Add(new DateTime[] { dtBegin, dtEnd, AccountDate, dtCybleBeginDate, dtCycleEndDate });
if (Peroid != enumPeroiod.AllPeroid) break;
dtBegin = dtEnd.AddDays(1);
}
break;
#endregion
#region 其他
case 6:
{
dtEnd = EndDate;
dtCycleEndDate = dtEnd;
dtCybleBeginDate = dtBegin;
AccountDate = CalAccountDate(dtBegin, dtEnd, ExesType, ExesMonth, AccDate);
rowList.Add(new DateTime[] { BeginDate, EndDate, AccountDate, dtCybleBeginDate, dtCycleEndDate });
if (Peroid != enumPeroiod.AllPeroid) break;
}
break;
#endregion
}
DataTable dt=new DataTable();
dt.Columns.Add("BeginDate",typeof(DateTime));
dt.Columns.Add("EndDate",typeof(DateTime));
dt.Columns.Add("GatheringDate",typeof(DateTime));
dt.Columns.Add("CycleBeginDate", typeof(DateTime));
dt.Columns.Add("CycleEndDate", typeof(DateTime));
for (int i = 0; i < rowList.Count; i++)
{
DataRow row = dt.NewRow();
row["BeginDate"] = rowList[i][0];
row["EndDate"] = rowList[i][1];
row["GatheringDate"] = rowList[i][2];
row["CycleBeginDate"] = rowList[i][3];
row["CycleEndDate"] = rowList[i][4];
dt.Rows.Add(row);
}
if (ReturnDataRow==true)
return dt.Rows as IEnumerable;
else
return rowList as IEnumerable;
//返回一个string 数组，这个数组符合IEnumerable接口，当然你也可以返回hashtable等类型。
//return (IEnumerable)dt.Rows.GetEnumerator();
}
private static DateTime CalAccountDate(DateTime dtBegin,DateTime dtEnd, int DelayType,int DelayMonth, int DelayDay)
{
DateTime dtAccount = dtEnd;
//Globe.SendString(string.Format("\r\n************{0},{1},{2}", DelayType, DelayMonth, DelayDay));
switch (DelayType)
{
case 1://提前提前N个月
dtAccount = new DateTime(dtBegin.Year, dtBegin.Month, 1);
dtAccount = dtAccount.AddMonths(-DelayMonth);
break;
case 3://延后
dtAccount = new DateTime(dtEnd.Year, dtEnd.Month, 1);
dtAccount = dtAccount.AddMonths(DelayMonth);
break;
case 4://指定月份
if (DelayMonth<1 || DelayMonth>12)
dtAccount = new DateTime(dtEnd.Year, dtEnd.Month, 1);
else
dtAccount = new DateTime(dtEnd.Year, DelayMonth, 1);
break;
case 2://本期
default:
dtAccount = new DateTime(dtEnd.Year, dtEnd.Month, 1);
break;
}
DateTime dtMonthLast=new DateTime(dtAccount.Year, dtAccount.Month, 1).AddMonths(1).AddDays(-1);
int MonthDays = dtMonthLast.Day;//应收所在月份的天数。
if (DelayDay <= 0 || DelayDay > MonthDays)
dtAccount = dtMonthLast;
else
dtAccount = new DateTime(dtAccount.Year, dtAccount.Month, DelayDay);
return dtAccount;
}
}测试
select * from CW_F_CalDateTable_CLR(3,1,'2013-01-15','2014-01-14',3,1,15,2,0)运行结果
[img]http://dl2.iteye.com/upload/attachment/0089/9667/3c2745ed-302b-3b3e-bef1-0a6103148fe5.jpg[/img]
2370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
