c# 划份时间段之二 (按会计月)

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using AmbitsBusiness_CLR;

public partial class WYTableFunction
{
    /// <summary>
    /// 按会计月计算应收日期
    /// CybleBeginDate:周期实际的起始日，由于是按会计月计，所以永远与计算的起始日相同。
    /// CybleEndDate:周期实际的结束日。 
    /// </summary>
    /// <param name="UnitType"></param>
    /// <param name="Cycle"></param>
    /// <param name="sBeginDate"></param>
    /// <param name="sEndDate"></param>
    /// <param name="ExesType"></param>
    /// <param name="ExesMonth"></param>
    /// <param name="AccDate"></param>
    /// <returns></returns>
    [SqlFunction(TableDefinition = @" BeginDate DateTime,EndDate DateTime,GatheringDate DateTime,CybleBeginDate DateTime,CycleEndDate DateTime", FillRowMethodName = "FillRow2")]
    public static IEnumerable CW_F_CalDateTable_Accountant(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 dtCycleEndDate = EndDate;
        DateTime dtCybleBeginDate = dtBegin;//按会计月，则周期的起始日与原起始日相同。

        //Globe.SendString(string.Format("生成周期，从{0:yyyy-MM-dd}至{1:yyyy-MM-dd}", BeginDate, EndDate));

        switch (UnitType)
        {
            #region 
            case 1://年

                while (dtBegin <= EndDate)
                {
                    if (Peroid == enumPeroiod.NotSplit)
                    {
                        dtEnd = EndDate;
                    }
                    else
                        dtEnd = dtBegin.AddYears(Cycle);

                    dtCycleEndDate = dtEnd.AddDays(-1);
                    dtCybleBeginDate = dtEnd.AddYears(-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 2://季
                while (dtBegin <= EndDate)
                {
                    if (Peroid == enumPeroiod.NotSplit)
                    {
                        dtEnd = EndDate;
                    }
                    else
                        dtEnd = dtBegin.AddMonths(Cycle * 3);

                    dtCycleEndDate = dtEnd.AddDays(-1);
                    dtCybleBeginDate = dtEnd.AddMonths(-Cycle*3);

                    if (dtEnd > EndDate)
                        dtEnd = EndDate;
                    else
                        dtEnd = dtEnd.AddDays(-1);
                    //dtEnd = new DateTime(dtEnd.Year, dtEnd.Month, 1).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 3://月
                while (dtBegin <= EndDate)
                {
                    if (Peroid == enumPeroiod.NotSplit)
                    {
                        dtEnd = EndDate;
                    }
                    else
                        dtEnd = dtBegin.AddMonths(Cycle);

                    dtCycleEndDate =dtEnd.AddDays(-1);
                    dtCybleBeginDate = dtEnd.AddMonths(-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 4://周

                while (dtBegin <= EndDate)
                {
                    if (Peroid == enumPeroiod.NotSplit)
                    {
                        dtEnd = EndDate;
                    }
                    else
                        dtEnd = dtBegin.AddDays(Cycle * 7);

                    dtCycleEndDate = dtEnd.AddDays(-1);
                    dtCybleBeginDate = dtEnd.AddDays(-Cycle*7);

                    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 = BeginDate;
                    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;
        //return rowList as IEnumerable;
    }

}

测试代码

select * from CW_F_CalDateTable_Accountant(3,1,'2013-01-15','2014-01-14',3,1,15,2,0)

运行结果

[img]http://dl2.iteye.com/upload/attachment/0089/9671/5bb79dbb-ffc6-350f-969f-148c67d66507.jpg[/img]