/// <summary>
/// 设置新增和修改的参数数组的值
/// </summary>
/// <param name="parameters">参数数组</param>
/// <param name="model">实体类</param>
/// <returns>返回参数数组</returns>
private static SqlParameter[] SetInsertAndUpdateParametersValue(SqlParameter[] parameters, PersonalNoteModel 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_noteNoCol].Value = model.NoteNo; // 日记编号(年月日时分秒+用户id)
if (!model.NoteDate.HasValue) parameters[m_noteDateCol].Value = System.DBNull.Value; else parameters[m_noteDateCol].Value = model.NoteDate; // 日期
parameters[m_noteContentCol].Value = model.NoteContent; // 日记内容
parameters[m_attachmentCol].Value = model.Attachment; // 附件
parameters[m_canViewUserCol].Value = model.CanViewUser; // 可以查看工作日志的人员（ID，多个）
parameters[m_canViewUserNameCol].Value = model.CanViewUserName; // 可以查看工作日志的人员姓名
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.NotedDate.HasValue) parameters[m_notedDateCol].Value = System.DBNull.Value; else parameters[m_notedDateCol].Value = model.NotedDate; // 点评日期
parameters[m_statusCol].Value = model.Status; // 日志状态（0草稿，1提交,2已点评）
if (!model.Creator.HasValue) parameters[m_creatorCol].Value = System.DBNull.Value; else parameters[m_creatorCol].Value = model.Creator; // 创建人ID(对应员工表ID)
parameters[m_creatorUserNameCol].Value = model.CreatorUserName; // 创建人名称
if (!model.CreateDate.HasValue) parameters[m_createDateCol].Value = System.DBNull.Value; else parameters[m_createDateCol].Value = model.CreateDate; // 创建时间
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_studyContentCol].Value = model.StudyContent; // 今日学习
parameters[m_myCheckContentCol].Value = model.MyCheckContent; // 今日反省
parameters[m_upContentCol].Value = model.UpContent; // 改进办法
if (!model.RenZhenDF.HasValue) parameters[m_renZhenDFCol].Value = System.DBNull.Value; else parameters[m_renZhenDFCol].Value = model.RenZhenDF; // 认真(分)
if (!model.KuaiDF.HasValue) parameters[m_kuaiDFCol].Value = System.DBNull.Value; else parameters[m_kuaiDFCol].Value = model.KuaiDF; // 快(分)
if (!model.ChengNuoDF.HasValue) parameters[m_chengNuoDFCol].Value = System.DBNull.Value; else parameters[m_chengNuoDFCol].Value = model.ChengNuoDF; // 坚守承诺(分)
if (!model.RenWuDF.HasValue) parameters[m_renWuDFCol].Value = System.DBNull.Value; else parameters[m_renWuDFCol].Value = model.RenWuDF; // 保证完成任务(分)
if (!model.LeGuanDF.HasValue) parameters[m_leGuanDFCol].Value = System.DBNull.Value; else parameters[m_leGuanDFCol].Value = model.LeGuanDF; // 乐观(分)
if (!model.ZiXinDF.HasValue) parameters[m_ziXinDFCol].Value = System.DBNull.Value; else parameters[m_ziXinDFCol].Value = model.ZiXinDF; // 自信(分)
if (!model.FenXianDF.HasValue) parameters[m_fenXianDFCol].Value = System.DBNull.Value; else parameters[m_fenXianDFCol].Value = model.FenXianDF; // 爱与奉献(分)
if (!model.JieKouDF.HasValue) parameters[m_jieKouDFCol].Value = System.DBNull.Value; else parameters[m_jieKouDFCol].Value = model.JieKouDF; // 决不找借口(分)
return parameters;
}
/// <summary>
/// 判断今天是否已经写日志
/// </summary>
/// <param name="model">实体类</param>
/// <returns>true:存在;false:不存在</returns>
public static bool ExisitTodayNote(PersonalNoteModel model)
{
string strSql = @"SELECT pn.ID
FROM officedba.PersonalNote pn
WHERE pn.CompanyCD='{0}' AND pn.Creator={1} AND DATEDIFF(dd,pn.NoteDate,'{2}')=0";
return DBHelper.SqlHelper.ExecuteSql(string.Format(strSql, model.CompanyCD, model.Creator, model.NoteDate.Value)).Rows.Count > 0;
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改操作的执行命令</returns>
public static SqlCommand UpdateCommand(PersonalNoteModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_UPDATE;
if (model.Status == "2")
{
comm.CommandText = " UPDATE officedba.PersonalNote SET ManagerNote=@ManagerNote,NotedDate=@NotedDate,Status=@Status " +
" WHERE ID=@ID";
}
// 参数设置
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(PersonalNoteModel model)
{
bool returnValue = false; // 返回值
// SQL语句
string sqlSentence = C_UPDATE;
if (model.Status == "2")
{
sqlSentence = " UPDATE officedba.PersonalNote SET ManagerNote=@ManagerNote,NotedDate=@NotedDate,Status=@Status " +
" WHERE ID=@ID";
}
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
//执行SQL
returnValue = SqlHelper.ExecuteTransSql(sqlSentence, parameters) > 0;
return returnValue;
}
/// <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
, PersonalNoteModel 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 InsertCommand(PersonalNoteModel 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>
/// <param name="list">汇报集合</param>
/// <returns></returns>
public static bool SaveData(PersonalNoteModel model, IList<PersonalNoteReportModel> list)
{
ArrayList sqlList = new ArrayList();
SqlCommand cmd = null;
#region 日志
if (model.ID.HasValue)
{// 编号存在更新
sqlList.Add(PersonalNoteDBHelper.UpdateCommand(model));
}
else
{// 编号不存在保存
cmd = PersonalNoteDBHelper.InsertCommand(model);
sqlList.Add(cmd);
}
#endregion
#region 汇报信息
foreach (PersonalNoteReportModel item in list)
{
if (item.ID.HasValue)
{
sqlList.Add(PersonalNoteReportDBHelper.UpdateCommand(item));
}
else
{
sqlList.Add(PersonalNoteReportDBHelper.InsertCommand(item));
}
}
#endregion
// 保存数据
if (SqlHelper.ExecuteTransWithArrayList(sqlList))
{
if (!model.ID.HasValue)
{
int i = 0;
if (int.TryParse(cmd.Parameters["@IndexID"].Value.ToString(), out i))
{
model.ID = i;
}
}
return true;
}
else
{
return false;
}
}
/// <summary>
/// 判断今天是否已经写日志
/// </summary>
/// <param name="model">实体类</param>
/// <returns>true:存在;false:不存在</returns>
public static bool ExisitTodayNote(PersonalNoteModel model)
{
return PersonalNoteDBHelper.ExisitTodayNote(model);
}
/// <summary>
/// 修改数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(PersonalNoteModel model)
{
return PersonalNoteDBHelper.Update(model);
}
/// <summary>
/// 插入数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>插入数据是否成功:true成功,false不成功</returns>
public static bool Insert(PersonalNoteModel model)
{
ArrayList sqlList = new ArrayList();
SqlCommand cmd = PersonalNoteDBHelper.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;
}
return true;
}
else
{
return false;
}
}
/// <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
, PersonalNoteModel model)
{
return PersonalNoteDBHelper.SelectListData(pageIndex, pageCount, orderBy, ref TotalCount, model);
}