/// <summary>
/// 更新记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int UpdateCompanyRule(CompanyRuleInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("update CompanyRule set CourseNum=" + Model.CourseNum + ",Frequency=" + Model.Frequency + ",StartDate=" + Model.StartDate + ",EndDate=" + Model.EndDate + ",PostId=" + Model.PostId);
return(DbSQLHelper.ExecuteSql(sql.ToString()));
}
public int AddCompanyRule(CompanyRuleInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into CompanyRule (CompanyId,CourseNum,Frequency,StartDate,EndDate,PostId) values(@CompanyId,@CourseNum,@Frequency,@StartDate,@EndDate,@PostId)");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
return(DbSQLHelper.ExecuteSql(sql.ToString(), par));
}
public void PrepareModel(SqlDataReader dr, List <CompanyRuleInfo> CompanyRuleList)
{
while (dr.Read())
{
CompanyRuleInfo Item = new CompanyRuleInfo();
Item.CompanyId = int.Parse(dr["CompanyId"].ToString());
Item.CourseNum = int.Parse(dr["CourseNum"].ToString());
Item.Frequency = int.Parse(dr["Frequency"].ToString());
Item.StartDate = DateTime.Parse(dr["StartDate"].ToString());
Item.EndDate = DateTime.Parse(dr["EndDate"].ToString());
Item.PostId = dr["PostId"].ToString();
Item.CreateDate = DateTime.Parse(dr["CreateDate"].ToString());
CompanyRuleList.Add(Item);
}
}
/// <summary>
/// 取得时间段内通一停课周数
/// </summary>
/// <param name="PostId"></param>
/// <param name="StartDate"></param>
/// <param name="EndDate"></param>
/// <returns></returns>
public static int GetPublicWeekNum(int PostId, DateTime StartDate, DateTime EndDate)
{
int WeekNum = 0;//取整数
int DifferDays = 0;
CompanyRuleInfo RuleModel = new CompanyRuleInfo();
RuleModel.CompanyId = 0;
RuleModel.PostId = PostId.ToString();
List <CompanyRuleInfo> TempList = CompanyRuleList(RuleModel);
if (TempList != null)
{
foreach (CompanyRuleInfo Item in TempList)
{
if (Item.StartDate >= EndDate || Item.EndDate <= StartDate)
{
continue;
}
if (Item.StartDate >= StartDate && Item.EndDate <= EndDate)
{
DifferDays = (Item.EndDate - Item.StartDate).Days;
}
else if (Item.StartDate < StartDate && Item.EndDate > EndDate)
{
DifferDays = (EndDate - StartDate).Days;
}
else
{
if (Item.StartDate >= StartDate && Item.StartDate <= EndDate)
{
DifferDays = (EndDate - Item.StartDate).Days;
}
else if (Item.EndDate >= StartDate && Item.EndDate <= EndDate)
{
DifferDays = (Item.EndDate - StartDate).Days;
}
}
WeekNum += DifferDays / 7;
if (DifferDays % 7 > 0)
{
WeekNum += 1;
}
}
}
return(WeekNum);
}
/// <summary>
/// 该数据访问对象的属性值装载到数据库更新参数数组
/// </summary>
/// <remarks></remarks>
protected IDbDataParameter[] ValueParas(CompanyRuleInfo Model)
{
SqlParameter[] par =
{
new SqlParameter("@CompanyId", SqlDbType.Int),
new SqlParameter("@ChangeNum", SqlDbType.Int),
new SqlParameter("@Frequency", SqlDbType.VarChar, 50),
new SqlParameter("@StartDate", SqlDbType.VarChar, 50),
new SqlParameter("@EndDate", SqlDbType.VarChar, 50),
new SqlParameter("@PostId", SqlDbType.VarChar, 50)
};
par[0].Value = Model.CompanyId;
par[1].Value = Model.CourseNum;
par[2].Value = Model.Frequency;
par[3].Value = Model.StartDate;
par[4].Value = Model.EndDate;
par[5].Value = Model.PostId;
return(par);
}
/// <summary>
/// 查询公司变化记录 带分页
/// </summary>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="RecordSearch"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <CompanyRuleInfo> CompanyRuleList(int currentPage, int pageSize, CompanyRuleInfo RuleSearch, ref int count)
{
List <CompanyRuleInfo> RuleList = new List <CompanyRuleInfo>();
ShopMssqlPagerClass class2 = new ShopMssqlPagerClass();
class2.TableName = ShopMssqlHelper.TablePrefix + "CompanyRule";
class2.Fields = "*";
class2.CurrentPage = currentPage;
class2.PageSize = pageSize;
class2.OrderField = "[Id]";
class2.OrderType = OrderType.Desc;
this.PrepareCondition(class2.MssqlCondition, RuleSearch);
class2.Count = count;
count = class2.Count;
using (SqlDataReader reader = class2.ExecuteReader())
{
this.PrepareModel(reader, RuleList);
}
return(RuleList);
}
public List <CompanyRuleInfo> CompanyRuleList(CompanyRuleInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CompanyRule] ");
List <CompanyRuleInfo> CompanyRuleList = new List <CompanyRuleInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by StartDate");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, CompanyRuleList);
}
}
return(CompanyRuleList);
}
public void PrepareCondition(MssqlCondition mssqlCondition, CompanyRuleInfo RuleSearch)
{
mssqlCondition.Add("[CompanyId]", RuleSearch.CompanyId, ConditionType.Equal);
mssqlCondition.Add("[PostId]", "|" + RuleSearch.PostId + "|", ConditionType.Like);
}
/// <summary>
/// 查询公司规则记录 带分页
/// </summary>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="RecordSearch"></param>
/// <param name="count"></param>
/// <returns></returns>
public static List <CompanyRuleInfo> CompanyRuleList(int currentPage, int pageSize, CompanyRuleInfo RuleSearch, ref int count)
{
return(dal.CompanyRuleList(currentPage, pageSize, RuleSearch, ref count));
}
/// <summary>
/// 查询公司规则记录
/// </summary>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="RecordSearch"></param>
/// <param name="count"></param>
/// <returns></returns>
public static List <CompanyRuleInfo> CompanyRuleList(CompanyRuleInfo RuleSearch)
{
return(dal.CompanyRuleList(RuleSearch));
}
/// <summary>
/// 更新公司规则信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public static int UpdateCompanyRule(CompanyRuleInfo Model)
{
return(dal.UpdateCompanyRule(Model));
}
/// <summary>
/// 添加公司规则信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public static int AddCompanyRule(CompanyRuleInfo Model)
{
return(dal.AddCompanyRule(Model));
}
/// <summary>
/// 取得时间段内的课程数
/// </summary>
/// <param name="CompanyId"></param>
/// <param name="PostId"></param>
/// <param name="StartDate"></param>
/// <param name="EndDate"></param>
/// <returns></returns>
public static int GetCourseNum(int CompanyId, int PostId, DateTime StartDate, DateTime EndDate)
{
int PostWeekNum = 0;
int CourseNum = 0;
PostWeekNum = (EndDate - StartDate).Days / 7;
if ((EndDate - StartDate).Days % 7 > 0)
{
PostWeekNum += 1;
}
CompanyRuleInfo RuleModel = new CompanyRuleInfo();
RuleModel.CompanyId = CompanyId;
RuleModel.PostId = PostId.ToString();
List <CompanyRuleInfo> TempList = CompanyRuleList(RuleModel);
if (TempList != null && TempList.Count > 0)
{
int TotalWeekNum = 0;
foreach (CompanyRuleInfo Item in TempList)
{
if (Item.StartDate >= EndDate || Item.EndDate <= StartDate)
{
continue;
}
int WeekNum = 0, PublicWeekNum = 0;//取整数
int DifferDays = 0;
if (Item.StartDate >= StartDate && Item.EndDate <= EndDate)
{
DifferDays = (Item.EndDate - Item.StartDate).Days;
PublicWeekNum = GetPublicWeekNum(PostId, Item.StartDate, Item.EndDate);
}
else if (Item.StartDate < StartDate && Item.EndDate > EndDate)
{
DifferDays = (EndDate - StartDate).Days;
PublicWeekNum = GetPublicWeekNum(PostId, StartDate, EndDate);
}
else
{
if (Item.StartDate >= StartDate && Item.StartDate <= EndDate)
{
DifferDays = (EndDate - Item.StartDate).Days;
PublicWeekNum = GetPublicWeekNum(PostId, Item.StartDate, EndDate);
}
else if (Item.EndDate >= StartDate && Item.EndDate <= EndDate)
{
DifferDays = (Item.EndDate - StartDate).Days;
PublicWeekNum = GetPublicWeekNum(PostId, StartDate, Item.EndDate);
}
}
WeekNum = DifferDays / 7;
if (DifferDays % 7 > 0)
{
WeekNum += 1;
}
//公共停课周数
//WeekNum = WeekNum + PublicWeekNum;
TotalWeekNum += PublicWeekNum;
CourseNum = CourseNum + (WeekNum * 2 - ((Item.CourseNum * (WeekNum - PublicWeekNum)) / Item.Frequency));
}
TotalWeekNum = GetPublicWeekNum(PostId, StartDate, EndDate) - TotalWeekNum;
if (TotalWeekNum < 0)
{
TotalWeekNum = 0;
}
CourseNum = CourseNum + (TotalWeekNum * 2);
}
else
{
//周数
PostWeekNum = PostWeekNum - GetPublicWeekNum(PostId, StartDate, EndDate);
//默认销售部门两门，其他部门一门
if (!StringHelper.CompareString("4,5,8,64,478,484", PostId.ToString()))
{
CourseNum = PostWeekNum;
}
}
return(PostWeekNum * 2 - CourseNum);
}
