/// <summary>
/// 列表界面查询方法
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageCount">每页记录数</param>
/// <param name="orderBy">排序方法</param>
/// <param name="TotalCount">总记录数</param>
/// <param name="model">实体类</param>
/// <returns></returns>
public static DataTable SelectListData(int pageIndex, int pageCount, string orderBy, ref int TotalCount
, PersonalDateArrangeModel model)
{
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder(C_SELECT_ALL);
sql.Append(" WHERE 1=1 ");
comm.CommandText = sql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, orderBy, ref TotalCount));
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改操作的执行命令</returns>
public static SqlCommand UpdateCommand(PersonalDateArrangeModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_UPDATE;
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
comm.Parameters.AddRange(parameters);
return(comm);
}
/// <summary>
/// 修改数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(PersonalDateArrangeModel model)
{
ArrayList sqlList = new ArrayList();
sqlList.Add(PersonalDateArrangeDBHelper.UpdateCommand(model));
sqlList.Add(PersonalDateArrangeDBHelper.InsertMessage(model));
if (SqlHelper.ExecuteTransWithArrayList(sqlList))
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(PersonalDateArrangeModel model)
{
bool returnValue = false; // 返回值
// SQL语句
StringBuilder sqlSentence = new StringBuilder(C_UPDATE);
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
//执行SQL
returnValue = SqlHelper.ExecuteTransSql(sqlSentence.ToString(), parameters) > 0;
return(returnValue);
}
/// <summary>
/// 插入操作的执行命令
/// </summary>
/// <param name="model">实体类</param>
/// <returns>插入操作的执行命令</returns>
public static SqlCommand InsertCommand(PersonalDateArrangeModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_INSERT + " SET @IndexID = @@IDENTITY ";
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
comm.Parameters.AddRange(parameters);
SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int);
IndexID.Direction = ParameterDirection.Output;
comm.Parameters.Add(IndexID);
return(comm);
}
/// <summary>
/// 更新点评信息
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool UpdateManager(PersonalDateArrangeModel model)
{
bool returnValue = false; // 返回值
// SQL语句
StringBuilder sqlSentence = new StringBuilder(@"UPDATE officedba.PersonalDateArrange
SET ManagerNote = @ManagerNote,
ManagerDate = @ManagerDate,
[Status] = @Status
WHERE ID = @ID");
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
//执行SQL
returnValue = SqlHelper.ExecuteTransSql(sqlSentence.ToString(), parameters) > 0;
return(returnValue);
}
/// <summary>
/// 添加短信命令
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static SqlCommand InsertMessage(PersonalDateArrangeModel model)
{
SqlCommand cmd = new SqlCommand();
StringBuilder strSql = new StringBuilder();
strSql.Append("DELETE FROM officedba.NoticeHistory WHERE CompanyCD=@CompanyCD AND SourceID=@ID AND SourceFlag =@SourceFlag ");
if (model.IsMobileNotice == "1")
{// 添加短信
strSql.Append("insert into officedba.NoticeHistory(");
strSql.Append(" CompanyCD,SourceFlag,SourceID,PlanNoticeDate ) ");
strSql.Append(" values(@CompanyCD, @SourceFlag, @ID, @PlanNoticeDate ) ");
}
cmd.CommandText = strSql.ToString();
cmd.Parameters.Add(new SqlParameter("@CompanyCD", model.CompanyCD));
cmd.Parameters.Add(new SqlParameter("@SourceFlag", 1));
cmd.Parameters.Add(new SqlParameter("@ID", model.ID.Value));
DateTime dt = DateTime.Now;
if (model.IsMobileNotice == "1")
{// 计算发送时间
try
{
double hour = Convert.ToDouble(model.StartTime.Substring(0, 2));
double minite = Convert.ToDouble(model.StartTime.Substring(2, 2));
DateTime datetime = (DateTime)model.StartDate;
datetime = datetime.AddHours(hour);
datetime = datetime.AddMinutes(minite);
double aheadtime = Convert.ToDouble(model.AheadTimes) * -1;
datetime = datetime.AddHours(aheadtime);
dt = datetime;
}
catch
{
}
}
cmd.Parameters.Add(new SqlParameter("@PlanNoticeDate", dt));
return(cmd);
}
/// <summary>
/// 插入数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>插入数据是否成功:true成功,false不成功</returns>
public static bool Insert(PersonalDateArrangeModel model)
{
ArrayList sqlList = new ArrayList();
SqlCommand cmd = PersonalDateArrangeDBHelper.InsertCommand(model);
sqlList.Add(cmd);
if (SqlHelper.ExecuteTransWithArrayList(sqlList))
{
int i = 0;
if (int.TryParse(cmd.Parameters["@IndexID"].Value.ToString(), out i))
{
model.ID = i;
}
sqlList.Clear();
sqlList.Add(PersonalDateArrangeDBHelper.InsertMessage(model));
SqlHelper.ExecuteTransWithArrayList(sqlList);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 设置新增和修改的参数数组的值
/// </summary>
/// <param name="parameters">参数数组</param>
/// <param name="model">实体类</param>
/// <returns>返回参数数组</returns>
private static SqlParameter[] SetInsertAndUpdateParametersValue(SqlParameter[] parameters, PersonalDateArrangeModel model)
{
if (!model.ID.HasValue)
{
parameters[m_iDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_iDCol].Value = model.ID; // 自动生成
}
parameters[m_companyCDCol].Value = model.CompanyCD; // 企业代码
parameters[m_arrangeTItleCol].Value = model.ArrangeTItle; // 安排主题
parameters[m_criticalCol].Value = model.Critical; // 紧急程度(1宽松,2一般,3较急,4紧急,5特急)
parameters[m_arrangPersonCol].Value = model.ArrangPerson; // 保留
parameters[m_contentCol].Value = model.Content; // 日程内容
if (!model.StartDate.HasValue)
{
parameters[m_startDateCol].Value = System.DBNull.Value;
}
else
{
parameters[m_startDateCol].Value = model.StartDate; // 日程日期
}
parameters[m_startTimeCol].Value = model.StartTime; // 开始时间（时分）
parameters[m_endTimeCol].Value = model.EndTime; // 结束时间（时分）
if (!model.Creator.HasValue)
{
parameters[m_creatorCol].Value = System.DBNull.Value;
}
else
{
parameters[m_creatorCol].Value = model.Creator; // 日程安排人ID(对应员工表ID)
}
parameters[m_isPublicCol].Value = model.IsPublic; // 保密度（0不公开，1公开）
if (!model.CreateDate.HasValue)
{
parameters[m_createDateCol].Value = System.DBNull.Value;
}
else
{
parameters[m_createDateCol].Value = model.CreateDate; // 创建时间
}
parameters[m_isMobileNoticeCol].Value = model.IsMobileNotice; // 是否手机短信提醒（0否，1是）
if (!model.AheadTimes.HasValue)
{
parameters[m_aheadTimesCol].Value = System.DBNull.Value;
}
else
{
parameters[m_aheadTimesCol].Value = model.AheadTimes; // 提前时间（小时）
}
if (!model.ModifiedDate.HasValue)
{
parameters[m_modifiedDateCol].Value = System.DBNull.Value;
}
else
{
parameters[m_modifiedDateCol].Value = model.ModifiedDate; // 最后更新日期
}
parameters[m_modifiedUserIDCol].Value = model.ModifiedUserID; // 最后更新用户ID（对应操作用户表中的UserID）
parameters[m_importantCol].Value = model.Important; // 重要程度(1不重要,2普通,3重要,4关键)
if (!model.EndDate.HasValue)
{
parameters[m_endDateCol].Value = System.DBNull.Value;
}
else
{
parameters[m_endDateCol].Value = model.EndDate; // 结束日期
}
if (!model.ToManagerID.HasValue)
{
parameters[m_toManagerIDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_toManagerIDCol].Value = model.ToManagerID; // 点评人ID
}
parameters[m_managerNoteCol].Value = model.ManagerNote; // 点评内容
if (!model.ManagerDate.HasValue)
{
parameters[m_managerDateCol].Value = System.DBNull.Value;
}
else
{
parameters[m_managerDateCol].Value = model.ManagerDate; // 点评日期
}
parameters[m_canViewUserCol].Value = model.CanViewUser; // 可查看人员ID
parameters[m_canViewUserNameCol].Value = model.CanViewUserName; // 可查看人员姓名
parameters[m_statusCol].Value = model.Status; // 日程状态（0草稿，1提交,2已点评）
return(parameters);
}
/// <summary>
/// 更新点评信息
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool UpdateManager(PersonalDateArrangeModel model)
{
return(PersonalDateArrangeDBHelper.UpdateManager(model));
}
/// <summary>
/// 列表界面查询方法
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageCount">每页记录数</param>
/// <param name="orderBy">排序方法</param>
/// <param name="TotalCount">总记录数</param>
/// <param name="model">实体类</param>
/// <returns></returns>
public static DataTable SelectListData(int pageIndex, int pageCount, string orderBy, ref int TotalCount
, PersonalDateArrangeModel model)
{
return(PersonalDateArrangeDBHelper.SelectListData(pageIndex, pageCount, orderBy, ref TotalCount, model));
}
