/// <summary>
/// 保存一个月的节假日设定
/// 添加人：周 鹏
/// 添加时间：2014-03-07
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="list">节假日集</param>
/// <param name="sTime">设定月份</param>
/// <returns></returns>
public bool SaveHostDate(List <ComHolidaysEntity> list, DateTime sTime)
{
var transaction = new TransactionLoader().BeginTransaction("SaveHolidays");
try
{
var startTime = Convert.ToDateTime(sTime.ToString("yyyy-MM") + "-01"); //开始时间
var endTime = startTime.AddMonths(1).AddDays(-1); //截止时间
var sbSql = new StringBuilder("");
sbSql.AppendFormat("UPDATE ComHolidays SET RowStatus=0 WHERE HostDate BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"));
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
foreach (var comHolidaysEntity in list)
{
Add(comHolidaysEntity, transaction);
}
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message, ex);
}
transaction.Commit();
return(true);
}
/// <summary>
/// 删除消息
/// 添加人：周 鹏
/// 添加时间：2014-12-23
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="list">待删除的消息集</param>
/// <param name="htype">操作方式</param>
/// <returns></returns>
public bool DeleteWorkList(List <string> list, string htype)
{
if (list.Any())
{
var transaction = new TransactionLoader().BeginTransaction("DeleteWorkList");
try
{
var sbSql = new StringBuilder();
if (list.Count >= 5)
{
foreach (var id in list)
{
sbSql.AppendFormat(@"UPDATE [CrmMessageWork] SET RowStatus={0} WHERE [Id] ={1};", htype.Equals("renew") ? 1 : 0, id);
}
}
else
{
sbSql.AppendFormat(@"UPDATE [CrmMessageWork] SET RowStatus={0} WHERE [Id] IN ('{1}');", htype.Equals("renew") ? 1 : 0, string.Join("','", list.ToArray()));
}
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
return(false);
}
/// <summary>
/// 更新用户菜单
/// 添加人：周 鹏
/// 添加时间：2014-01-03
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="userid">用户编号</param>
/// <param name="usermenus">用户菜单(以,分隔，例如 1,2,3)</param>
/// <returns></returns>
public bool SaveUserMenu(string userid, string usermenus)
{
var transaction = new TransactionLoader().BeginTransaction("SaveUserMenu");
try
{
var sbSql = new StringBuilder();
sbSql.AppendFormat(@"DELETE FROM [CrmUserMenu] WHERE UserId='{0}';", userid);
if (!(string.IsNullOrEmpty(usermenus) || usermenus.Trim(',').Length.Equals(0)))
{
sbSql.AppendFormat(
@"INSERT INTO [CrmUserMenu]([UserId],[MenuId],[RowStatus])
SELECT {0} AS UserId,Id AS MenuId,1 AS RowStatus
FROM ComMenu with(nolock)
WHERE Id IN ({1})",
userid, usermenus);
}
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
/// <summary>
/// 新增或更新用户
/// 添加人：周 鹏
/// 添加时间：2014-01-03
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="user">用户实体</param>
/// <param name="userroles">用户角色(以,分隔，例如 1,2,3)</param>
/// <param name="usermenus">用户菜单(以,分隔，例如 1,2,3)</param>
/// <param name="userId">用户id</param>
/// <returns></returns>
public bool SaveUser(CrmUserEntity user, string userroles, string usermenus, out string userId)
{
userId = "";
var transaction = new TransactionLoader().BeginTransaction("SaveUser");
try
{
var userModel = Get(user.Id);
if (!string.IsNullOrEmpty(userModel.Id))
{
Update(user, transaction);
}
else
{
Add(user, transaction);
}
userId = user.Id;
var sbSql = new StringBuilder();
sbSql.AppendFormat(@"DELETE FROM [CrmUserRole] WHERE [UserId]='{0}';", user.Id);
if (!(string.IsNullOrEmpty(userroles) || userroles.Trim(',').Length.Equals(0)))
{
sbSql.AppendFormat(
@"INSERT INTO [CrmUserRole](UserId,RoleId,RowStatus)
SELECT {0} AS UserId,Id,1 AS RowStatus FROM CrmRole with(nolock)
WHERE Id IN ({1});",
user.Id, userroles);
}
sbSql.AppendFormat(@"DELETE FROM [CrmUserMenu] WHERE UserId='{0}';", user.Id);
if (!(string.IsNullOrEmpty(usermenus) || usermenus.Trim(',').Length.Equals(0)))
{
sbSql.AppendFormat(
@"INSERT INTO [CrmUserMenu]([UserId],[MenuId],[RowStatus])
SELECT {0} AS UserId,Id AS MenuId,1 AS RowStatus
FROM ComMenu with(nolock)
WHERE Id IN ({1})",
user.Id, usermenus);
}
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
public static void Run(Options options)
{
if (!File.Exists(options.PathToCsv))
{
System.Console.WriteLine($"File at {options.PathToCsv} does not exist.");
Environment.Exit(1);
}
ConsoleKey choice;
var transactions = TransactionLoader.LoadTransactions(options.PathToCsv);
PrintTitle();
do
{
PrintMenu();
choice = GetInput();
RunBusinessLogic(choice, transactions);
}while (choice != ConsoleKey.D0);
}
/// <summary>
/// 更新已读和未读状态
/// </summary>
/// <param name="ContetId"></param>
/// <returns></returns>
public bool UpdateDocState(string ContetId, string Pid)
{
var transaction = new TransactionLoader().BeginTransaction("UpdateDocState");
try
{
var sbSql = new StringBuilder("");
sbSql.AppendFormat("UPDATE dbo.DocNotfPerson SET IsJS = 1 WHERE ContentId = '{0}' AND Pid = '{1}';", ContetId, Pid);
sbSql.AppendFormat("UPDATE dbo.DocumentNotification SET JieShouCount = JieShouCount + 1 WHERE Id = '{0}';", ContetId);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
transaction.Commit();
return(true);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
/// <summary>
/// 保存附件
/// </summary>
/// <param name="resourceId">外键编号</param>
/// <param name="attachments">附件数据集，以逗号分隔</param>
/// <returns></returns>
public bool SaveAttachment(string resourceId, string attachments)
{
var transaction = new TransactionLoader().BeginTransaction("SaveAttachment");
try
{
//保存附件
var sbSql = new StringBuilder();
sbSql.AppendFormat("DELETE ComAttachment WHERE ResourceId='{0}'", resourceId);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
if (!(string.IsNullOrEmpty(attachments) || attachments.Split(',').Length.Equals(0)))
{
var attachDal = new ComAttachmentDal();
var splitAttach = attachments.Split('|');
foreach (var attach in splitAttach)
{
var attachInfo = attach.Split(',');
var receiveEntity = new ComAttachmentEntity
{
Id = Guid.NewGuid().ToString(),
ResourceId = resourceId,
FileName = attachInfo[2],
FileReName = attachInfo[3],
FileAddress = attachInfo[1],
RowStatus = 1,
CreateOn = DateTime.Now
};
attachDal.Add(receiveEntity, transaction);
}
}
}
catch (Exception ex)
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
/// <summary>
/// 保存用户与角色对应
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="list">对应关系数据集</param>
/// <param name="roleId">角色编号</param>
/// <returns></returns>
public bool SaveUserRole(List <CrmUserRoleEntity> list, string roleId)
{
var transaction = new TransactionLoader().BeginTransaction("SaveUserRole");
try
{
var sbSql = new StringBuilder();
sbSql.AppendFormat(@"DELETE FROM CrmUserRole WHERE RoleId='{0}'", roleId);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
foreach (var entity in list)
{
Add(entity);
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
/// <summary>
/// 事务插入接收人
/// 该事务后期增加插入公告表的内容
/// add by lpl
/// 2018-12-24
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public bool SaveDocNotPerson(List <DocNotfPersonEntity> list)
{
var transaction = new TransactionLoader().BeginTransaction("SaveDocNotPerson");
try
{
var sbSql = new StringBuilder();
foreach (DocNotfPersonEntity person in list)
{
sbSql.AppendFormat(@"INSERT INTO dbo.DocNotfPerson(Name,Pid,FilePath,IsJS,RowStatus,CreatorId,CreateBy,CreateOn,UpdateId,UpdateBy,UpdateOn,ContentId,Title,Category,Publisher,ReleaseTime)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", person.Name, person.Pid, person.FilePath, person.IsJS, person.RowStatus, person.CreatorId, person.CreateBy, person.CreateOn, person.UpdateId, person.UpdateBy, person.UpdateOn, person.ContentId, person.Title, person.Category, person.Publisher, person.ReleaseTime);
}
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
transaction.Commit();
return(true);
}
public void LoadCsvWithTooFewColumnsTest()
{
Action loadBadCsv = () => TransactionLoader.LoadTransactions(CsvWithTooFewColumns);
loadBadCsv.Should().Throw <BadDataException>();
}
public void LoadValidCsvTest()
{
var transactions = TransactionLoader.LoadTransactions(KnownGoodCsv);
transactions.Should().HaveCount(12);
}
/// <summary>
/// 保存公告通知
/// 添加人：周 鹏
/// 添加时间：2015-01-28
/// </summary>
/// <hisotry>
/// 修改描述：时间+作者+描述
/// </hisotry>
/// <param name="entity">通知实体</param>
/// <returns></returns>
public bool SaveNotice(ComNoticeEntity entity)
{
var transaction = new TransactionLoader().BeginTransaction("SaveNotice");
try
{
var noticeId = "";
if (!string.IsNullOrEmpty(entity.Id))
{
noticeId = entity.Id;
Update(entity, transaction);
}
else
{
noticeId = Guid.NewGuid().ToString();
entity.Id = noticeId;
Add(entity, transaction);
}
//保存接收对象
var sbSql = new StringBuilder();
sbSql.AppendFormat("DELETE ComNoticeReceive WHERE NoticeId='{0}'", noticeId);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
if (!(string.IsNullOrEmpty(entity.Actioners) || entity.Actioners.Split(',').Length.Equals(0)))
{
var actionerNames = entity.ActionerNames;
var receiveDal = new ComNoticeReceiveDal();
var splitUser = entity.Actioners.Split(',');
var splitUserName = actionerNames.Split(',');
int i = 0;
foreach (var user in splitUser)
{
var receiveEntity = new ComNoticeReceiveEntity
{
Id = Guid.NewGuid().ToString(),
NoticeId = noticeId,
UserId = user,
UserNameq = "",
RowStatus = 1,
CreateOn = DateTime.Now
};
receiveDal.Add(receiveEntity, transaction);
//var regex = new Regex(@"<[^>]+>|</[^>]+>"); //去掉尖括号里面的数据得到手机号码
//var phoneNum = regex.Replace(splitUserName[i], string.Empty);
//i++;
////发短信
//if (string.IsNullOrEmpty(phoneNum) || phoneNum.Equals(" ")) continue;
//var noteEntity = new ComNoteEntity()
// {
// Id = Guid.NewGuid().ToString(),
// ResourcesId = "",
// ReceivePhone = phoneNum,
// MistakeInfo = "公告通知：" + entity.Title + "，请查阅！",
// ReceiveTime = DateTime.Now,
// Status = 0,
// RowStatus = 1
// };
//new ComNoteDal().Add(noteEntity);
}
}
//保存附件
sbSql = new StringBuilder();
sbSql.AppendFormat("DELETE ComNoticeAttach WHERE ResourceId='{0}'", noticeId);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sbSql.ToString());
if (!(string.IsNullOrEmpty(entity.Attachment) || entity.Attachment.Split(',').Length.Equals(0)))
{
var attachDal = new ComNoticeAttachDal();
var splitAttach = entity.Attachment.Split('|');
foreach (var attach in splitAttach)
{
var attachInfo = attach.Split(',');
var receiveEntity = new ComNoticeAttachEntity
{
Id = Guid.NewGuid().ToString(),
ResourceId = noticeId,
FileName = attachInfo[2],
FileReName = attachInfo[3],
FileAddress = attachInfo[1],
RowStatus = 1,
CreateOn = DateTime.Now
};
attachDal.Add(receiveEntity, transaction);
}
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
transaction.Commit();
return(true);
}
