protected void Page_Load(object sender, EventArgs e)
{
string connStr = "server=.;uid=sa;password=sasa;database=nfd";
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
db.BeginTran();
db.Sqlable.IsNoLock = true;
try
{
var sql = db.Sqlable.MappingTable<CutBill, CutBillShipment>("t1.c_id=t2.c_id").SelectToSql("t1.*");
var dt = db.GetDataTable(sql);
var id = db.Insert(new test() { name = "哈哈" + DateTime.Now });
var del = db.Delete<test>(21);
var update = db.Update<test>(new { name = "5555" + DateTime.Now }, new { id=1 });
}
catch (Exception)
{
db.RollbackTran();
}
}
;
//var xx = SqlTool.CreateMappingTable(20);
Console.Read();
}
/// <summary>
/// 创建队列排班
/// </summary>
/// <param name="modalityId">检查类型Id</param>
/// <param name="db">dbClient</param>
/// <param name="listMainInfo">排班日期主表列表</param>
private void CreateQueueArrange(SqlSugarClient db, string hospitalId, List <ModalityArrangeDate> listModalityDate, List <string> remainModalityList)
{
YLog.LogInfo($"开始生成队列排班............");
try
{
if (listModalityDate.Count > 0)
{
listModalityDate.ForEach(item =>
{
//获取该检查类型下的所有队列
var queueList = db.Queryable <t_mt_devicegroup>().Where(n => n.IsDelete == "0")
.Where(n => n.State == 1).Where(n => n.ClinicID == item.ModalityId)
.ToList().Select(n => new
{
QueueId = n.ID,
QueueName = n.GroupName
}).ToList();
item.ArrangeDateList.ForEach(ele =>
{
var listDetailInfo = new List <t_mt_queuearrangedetail>();
var listRelationInfo = new List <t_mt_queuerearrangerelation>();
#region "detail表"
for (var i = 0; i < 7; i++)
{
for (var j = 1; j <= 4; j++)
{
var detailInfo = new t_mt_queuearrangedetail();
detailInfo.ID = CommonHandleMethod.GetID();
detailInfo.QueueArrangeMainID = ele.ID;
detailInfo.QueueArrangeDate = ele.QueueArrangeStartDate.ToDateTime().AddDays(i).ToDate1();
detailInfo.QueueArrangeWeekDay = CommonHandleMethod.CaculateWeekDay(ele.QueueArrangeStartDate.ToDateTime().AddDays(i));
detailInfo.QueueArrangePeriod = j;
detailInfo.CreateDT = DateTime.Now.ToDate4();
detailInfo.IsDelete = 0;
listDetailInfo.Add(detailInfo);
#region "relation表"
queueList.ForEach(element =>
{
var relationInfo = new t_mt_queuerearrangerelation();
relationInfo.ID = CommonHandleMethod.GetID();
relationInfo.QueueArrangeDetailID = detailInfo.ID;
relationInfo.QueueID = element.QueueId;
relationInfo.QueueName = element.QueueName;
relationInfo.State = 0;
relationInfo.CreateDT = DateTime.Now.ToDate4();
relationInfo.IsDelete = 0;
listRelationInfo.Add(relationInfo);
});
#endregion
}
}
#endregion
db.Insertable <t_mt_queuearrangemain>(ele).ExecuteCommand();
db.Insertable <t_mt_queuearrangedetail>(listDetailInfo).ExecuteCommand();
db.Insertable <t_mt_queuerearrangerelation>(listRelationInfo).ExecuteCommand();
});
});
}
//存在未曾添加过队列排班的检查类型
if (remainModalityList.Any())
{
var listMainInfo = new List <t_mt_queuearrangemain>();
var listDetailInfo = new List <t_mt_queuearrangedetail>();
var listRelationInfo = new List <t_mt_queuerearrangerelation>();
Dictionary <string, int> modalityCountMap = new Dictionary <string, int>();
int count = 1;
remainModalityList.ForEach(item =>
{
if (modalityCountMap.TryGetValue(item, out count))
{
modalityCountMap[item] = count++;
}
else
{
modalityCountMap.Add(item, 1);
count = 1;
}
YLog.LogInfo($"新增检查类型:{CommonHandleMethod.GetModalityNameByGUID(item)}的队列排班............");
var mainInfo = new t_mt_queuearrangemain();
var firstDay = CommonHandleMethod.GetWeekFirstDayMon(DateTime.Now);
var lastDay = CommonHandleMethod.GetWeekLastDaySun(DateTime.Now);
//获取该检查类型下的所有队列
var queueList = db.Queryable <t_mt_devicegroup>().Where(n => n.IsDelete == "0").Where(n => n.State == 1).Where(n => n.ClinicID == item).ToList().Select(n => new
{
QueueId = n.ID,
QueueName = n.GroupName
}).ToList();
mainInfo.ID = CommonHandleMethod.GetID();
mainInfo.HospitalID = hospitalId;
mainInfo.ModalityID = item;
mainInfo.Modality = CommonHandleMethod.GetModalityNameByGUID(item);
mainInfo.IsDelete = 0;
mainInfo.CreateDT = DateTime.Now.ToDate4();
mainInfo.QueueArrangeStartDate = firstDay.ToDate1();
mainInfo.QueueArrangeEndDate = lastDay.ToDate1();
mainInfo.SequenceNumber = count;
YLog.LogInfo($"当前生成序号:{count}");
for (var i = 0; i < 7; i++)
{
for (var j = 1; j <= 4; j++)
{
var detailInfo = new t_mt_queuearrangedetail();
detailInfo.ID = CommonHandleMethod.GetID();
detailInfo.QueueArrangeMainID = mainInfo.ID;
detailInfo.QueueArrangeDate = firstDay.AddDays(i).ToDate1();
detailInfo.QueueArrangeWeekDay = CommonHandleMethod.CaculateWeekDay(firstDay.AddDays(i));
detailInfo.QueueArrangePeriod = j;
detailInfo.CreateDT = DateTime.Now.ToDate4();
detailInfo.IsDelete = 0;
listDetailInfo.Add(detailInfo);
#region "relation表"
queueList.ForEach(ele =>
{
var relationInfo = new t_mt_queuerearrangerelation();
relationInfo.ID = CommonHandleMethod.GetID();
relationInfo.QueueArrangeDetailID = detailInfo.ID;
relationInfo.QueueID = ele.QueueId;
relationInfo.QueueName = ele.QueueName;
relationInfo.State = 0;
relationInfo.CreateDT = DateTime.Now.ToDate4();
relationInfo.IsDelete = 0;
listRelationInfo.Add(relationInfo);
});
#endregion
}
}
listMainInfo.Add(mainInfo);
});
db.Insertable <t_mt_queuearrangemain>(listMainInfo).ExecuteCommand();
db.Insertable <t_mt_queuearrangedetail>(listDetailInfo).ExecuteCommand();
db.Insertable <t_mt_queuerearrangerelation>(listRelationInfo).ExecuteCommand();
}
}
catch (Exception ex)
{
db.RollbackTran();
throw (ex);
}
}
protected void Page_Load(object sender, EventArgs e)
{
//连接字符串
string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest";
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var stud = new Student() { id = 1 };
//查询单条
var single = db.Queryable<Student>().Single(c => c.id==stud.id);
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery<Student>("select * from Student");
//获取id
var id = db.SqlQuery<int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List<School> sList = new List<School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update<School>(new { name = "蓝翔2" }, it => it.id == id);
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete<School>(10);//注意主键必需为实体类的第一个属性
db.Delete<School>(it => it.id > 100);
db.Delete<School>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList<Student>(sql);
db.GetSingle<Student>(sql + " where id=1");
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
public async Task <RouteData> StockInReport(long stockInId, [FromBody] OutsideStockInReportDto result)
{
try
{
_logger.LogInformation($"[入库任务状态变化通知]收到通知,StockInId={stockInId},data={JsonConvert.SerializeObject(result)}");
result.StockInId = stockInId;
Wms_stockin stockIn = await _client.Queryable <Wms_stockin>()
.FirstAsync(x => x.StockInId == result.StockInId);
if (stockIn == null)
{
_logger.LogError($"[入库任务状态变化通知]E2013-没有找到入库单,StockInId={stockInId}");
return(YL.Core.Dto.RouteData.From(PubMessages.E2013_STOCKIN_NOTFOUND));
}
if (stockIn.StockInStatus == StockInStatus.task_finish.ToInt32())
{
_logger.LogError($"[入库任务状态变化通知]E2014-入库单状态已标记为完成,本次操作中断,StockInId={stockInId}, StockInNo={stockIn.StockInNo}");
//return YL.Core.Dto.RouteData.From(PubMessages.E2014_STOCKIN_ALLOW_FINISHED);
return(new RouteData());
}
Wms_mestask mesTask = await _client.Queryable <Wms_mestask>()
.FirstAsync(x => x.MesTaskId == stockIn.MesTaskId);
if (mesTask == null)
{
_logger.LogError($"[入库任务状态变化通知]E3000-没有找到相应的Mes任务,StockInId={stockInId}, StockInNo={stockIn.StockInNo}, MesTaskId={stockIn.MesTaskId}");
return(YL.Core.Dto.RouteData.From(PubMessages.E3000_MES_STOCKINTASK_NOTFOUND));
}
Wms_stockindetail[] details = _client.Queryable <Wms_stockindetail>()
.Where(x => x.StockInId == result.StockInId).ToArray();
_client.BeginTran();
foreach (OutsideStockInReportDetail detail in result.Details)
{
Wms_stockindetail localDetail = details.FirstOrDefault(
x => x.UniqueIndex == detail.UniqueIndex);
if (localDetail == null)
{
_client.RollbackTran();
_logger.LogError($"[入库任务状态变化通知]E2015-没有找到相应的物料,StockInId={stockInId}, StockInNo={stockIn.StockInNo} ,UniqueIndex ={ detail.UniqueIndex}");
return(YL.Core.Dto.RouteData.From(PubMessages.E2015_STOCKIN_HASNOT_MATERIAL, $"MaterialId={detail.MaterialId}"));
}
localDetail.PlanInQty = detail.PlanInQty;
localDetail.ActInQty = detail.ActInQty;
localDetail.Status = detail.Status.ToInt32();
localDetail.ModifiedBy = PubConst.InterfaceUserId;
localDetail.ModifiedUser = detail.ModifiedBy;
localDetail.ModifiedDate = Convert.ToDateTime(detail.ModifiedDate);
localDetail.Remark = detail.Remark;
localDetail.ErrorId = detail.ErrorId;
localDetail.ErrorInfo = detail.ErrorInfo;
}
if (_client.Updateable(details).ExecuteCommand() == 0)
{
_client.RollbackTran();
_logger.LogError($"[入库任务状态变化通知]E0002-任务明细更新失败,StockInId={stockInId}, StockInNo={stockIn.StockInNo}");
return(YL.Core.Dto.RouteData.From(PubMessages.E0002_UPDATE_COUNT_FAIL));
}
stockIn.StockInStatus = result.StockInStatus.ToInt32();
stockIn.ModifiedBy = PubConst.InterfaceUserId;
stockIn.ModifiedUser = PubConst.InterfaceUserName;
if (_client.Updateable(stockIn).ExecuteCommand() == 0)
{
_client.RollbackTran();
_logger.LogError($"[入库任务状态变化通知]E0002-任务更新失败,StockInId={stockInId}, StockInNo={stockIn.StockInNo}");
return(YL.Core.Dto.RouteData.From(PubMessages.E0002_UPDATE_COUNT_FAIL));
}
var anyWorking = await _client.Queryable <Wms_stockin>()
.AnyAsync(x => x.MesTaskId == stockIn.MesTaskId &&
x.StockInStatus != (int)StockInStatus.task_finish &&
x.StockInStatus != (int)StockInStatus.task_canceled);
if (!anyWorking)
{
_logger.LogInformation($"[入库任务状态变化通知]所有相关任务已完成,尝试通知MES MesTaskId={stockIn.MesTaskId}");
await _client.NofityStockIn(mesTask);
}
else
{
_logger.LogInformation($"[入库任务状态变化通知]尚有未完成任务,等待其余任务完成通知 MesTaskId={stockIn.MesTaskId}");
}
_logger.LogInformation($"[入库任务状态变化通知]通知处理正常完成,StockInId={stockInId}");
_client.CommitTran();
return(new RouteData());
}
catch (Exception ex)
{
_client.RollbackTran();
_logger.LogError($"[入库任务状态变化通知]E-1-发生异常,处理结束 ex={ex.ToString()}");
return(new RouteData()
{
Code = -1, Message = ex.Message
});
}
}
/// <summary>
/// 手动关注企业号
/// </summary>
/// <param name="vguid"></param>
/// <returns></returns>
public bool UserFocusWeChat(string vguid, out string outString)
{
bool result = false;
outString = string.Empty;
using (SqlSugarClient _dbSql = SugarDao.SugarDao_LandaVSql.GetInstance())
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
Guid Vguid = Guid.Parse(vguid);
Business_Personnel_Information userInfo = dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == Vguid).SingleOrDefault();
try
{
AllEmployee landaUser = _dbSql.Queryable <AllEmployee>().Where(i => i.IDCard == userInfo.IDNumber).FirstOrDefault();
if (landaUser != null)
{
dbMsSql.BeginTran();
string weChatJson = JsonHelper.ModelToJson(userInfo);
//1为未关注的人
if (userInfo.ApprovalStatus == 1)
{
//获取accessToken
string accessToken = Common.WeChatPush.WeChatTools.GetAccessoken();
//用户关注微信企业号
string focusResult = Common.WeChatPush.WeChatTools.GetAuthSucee(accessToken, userInfo.UserID);
U_FocusResult resultMsg = new U_FocusResult();
resultMsg = JsonHelper.JsonToModel <U_FocusResult>(focusResult);
if (resultMsg.errcode == 0)
{
dbMsSql.Update <Business_Personnel_Information>(new { ApprovalStatus = 2 }, i => i.Vguid == Vguid); //更新审批状态
result = true; //关注成功
}
else
{
result = false;
//model.respnseInfo = resultMsg.errmsg;
LogHelper.WriteLog("人员" + userInfo.UserID + "手动关注失败：错误码为-" + resultMsg.errcode + ",错误消息为_" + resultMsg.errmsg);
}
//存入操作日志表
_ll.SaveLog(9, 5, CurrentUser.GetCurrentUser().LoginName, userInfo.Name, weChatJson);
}
else if (userInfo.ApprovalStatus == 4)
{
//获取accessToken
string accessToken = Common.WeChatPush.WeChatTools.GetAccessoken();
//用户关注微信企业号
string focusResult = Common.WeChatPush.WeChatTools.EnableWeChatData(accessToken, userInfo.UserID);
U_FocusResult resultMsg = new U_FocusResult();
resultMsg = JsonHelper.JsonToModel <U_FocusResult>(focusResult);
if (resultMsg.errcode == 0)
{
dbMsSql.Update <Business_Personnel_Information>(new { ApprovalStatus = 2 }, i => i.Vguid == Vguid); //更新审批状态
result = true; //关注成功
}
else
{
result = false;
outString = "人员" + userInfo.UserID + "手动关注失败：错误码为-" + resultMsg.errcode + ",错误消息为_" + resultMsg.errmsg;
LogHelper.WriteLog(outString);
}
//存入操作日志表
_ll.SaveLog(9, 5, CurrentUser.GetCurrentUser().LoginName, userInfo.Name, weChatJson);
}
else
{
result = true;
}
dbMsSql.CommitTran();
}
else
{
outString = "人事库中不存在该用户，不能关注！";
}
}
catch (Exception ex)
{
dbMsSql.RollbackTran();
outString = "人员" + userInfo.UserID + "手动关注异常";
LogHelper.WriteLog("人员" + userInfo.UserID + "手动关注异常：" + ex.Message + "/n" + ex.ToString() + "/n" + ex.StackTrace);
}
}
}
return(result);
}
public void RollbackTran()
{
_db.RollbackTran();
}
public bool RefundDepositHandler(RefundDepositDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
db.BeginTran();
try
{
string msg = null;
bool result = false;
var depositRecord = _payRep.GetModelByPayId(req.OrigianlDepositId);
if (depositRecord == null || depositRecord.Id <= 0)
{
msg = "无法找到此定金支付记录，重新确认！";
}
else if (depositRecord.CyddJzType != CyddJzType.定金)
{
msg = "非定金支付记录不能操作退回，重新确认！";
}
else if (depositRecord.CyddJzStatus != CyddJzStatus.已付)
{
msg = "当前定金记录已操作过转结或退款操作，请重新确认！";
}
else if (depositRecord.PayAmount <= 0)
{
msg = "未发现可退定金金额！";
}
else
{
OrderPayHistoryDTO refundObj = new OrderPayHistoryDTO();
var dateItem = _extendItemRepository.GetModelList(req.CompanyId, 10003).FirstOrDefault();
if (dateItem != null)
{
var billDateNow = Convert.ToDateTime(dateItem.ItemValue);
if (billDateNow > DateTime.Today)
{
msg = "账务日期不能超过当前日期！";
}
else
{
R_OrderPayRecord model = new R_OrderPayRecord();
model.CreateDate = DateTime.Now;
model.CreateUser = req.CurrentUserId;
model.R_Order_Id = depositRecord.R_Order_Id;
model.CyddJzStatus = CyddJzStatus.已退;
model.CyddJzType = CyddJzType.定金;
model.CyddPayType = depositRecord.CyddPayType;
model.PayAmount = -depositRecord.PayAmount;
model.R_Market_Id = req.CurrentMarketId;
model.BillDate = billDateNow;
model.Remark = "退回定金";
model.PId = req.OrigianlDepositId;
model.R_Restaurant_Id = req.RestaurantId;
var orderRecord = new R_OrderRecord()
{
CreateUser = req.CurrentUserId,
CreateDate = DateTime.Now,
CyddCzjlStatus = CyddStatus.结账,
CyddCzjlUserType = CyddCzjlUserType.员工,
R_Order_Id = depositRecord.R_Order_Id,
Remark = "退定金操作，原定金支付记录Id：" + depositRecord.Id
+ "，退回金额（" + depositRecord.PayAmount.ToString("f2") + "）",
};
db.Insert(orderRecord);//记录退定金操作
//更新原定金记录状态为已结
db.Update <R_OrderPayRecord>(new { CyddJzStatus = CyddJzStatus.已结 }, x => x.Id == depositRecord.Id);
result = db.Insert <R_OrderPayRecord>(model) == null ? false : true;
db.CommitTran();
}
}
else
{
msg = "餐饮账务日期尚未初始化，请联系管理员！";
}
}
if (msg != null)
{
throw new Exception(msg);
}
return(result);
}
catch (Exception e)
{
db.RollbackTran();
throw e;
}
}
}
public bool SeparateTableHandle(SeparateTableSubmitDTO handleDto)
{
using (var db = new SqlSugarClient(Connection))
{
bool res = false;
try
{
db.BeginTran();
var originalOrderTable = db.Queryable <R_OrderTable>()
.Where(p => p.Id == handleDto.OrderTableId)
.FirstOrDefault();
var originalOrder = db.Queryable <R_Order>()
.Where(x => x.Id == originalOrderTable.R_Order_Id)
.FirstOrDefault();
var newTable = db.Queryable <R_Table>()
.FirstOrDefault(p => p.Id == handleDto.NewTableId);
var oldTable = db.Queryable <R_Table>()
.FirstOrDefault(p => p.Id == handleDto.OldTableId);
if (newTable.CythStatus == CythStatus.在用)
{
throw new Exception(string.Format(
"所选台号（{0}）已被使用，请重新选择！", newTable.Name));
}
R_Order order = new R_Order
{
CyddStatus = CyddStatus.用餐中,
OrderNo = DateTime.Now.ToString("yyyyMMddHHmmssfff"),
CreateDate = DateTime.Now,
CreateUser = handleDto.CreateUser,
CyddOrderSource = originalOrder.CyddOrderSource,
ContactPerson = originalOrder.ContactPerson,
ContactTel = originalOrder.ContactTel,
R_Market_Id = originalOrder.R_Market_Id,
TableNum = 1,
PersonNum = 1,
R_Restaurant_Id = newTable.R_Restaurant_Id,
Remark = string.Format("从原订单({0})中拆台生成的新订单", originalOrder.OrderNo)
};
var newOrderId = db.Insert(order);
R_OrderTable orderTable = new R_OrderTable();
orderTable.CreateDate = DateTime.Now;
orderTable.IsOpen = true;
orderTable.PersonNum = order.PersonNum;
orderTable.R_Order_Id = newOrderId.ObjToInt();
orderTable.R_Table_Id = handleDto.NewTableId;
var newOrderTableId = db.Insert(orderTable);
res = db.Update <R_Table>(new
{
CythStatus = CythStatus.在用
}, p => p.Id == handleDto.NewTableId);
var oldDetailList = db.Queryable <R_OrderDetail>()
.Where(x => x.R_OrderTable_Id == handleDto.OrderTableId)
.ToList();
foreach (var item in handleDto.SelectedList)
{
var detail = oldDetailList.Where(x => x.Id == item.Id).FirstOrDefault();
if (detail != null)
{
//判断拆台勾选菜品项数量是否全部转至其它台
if (detail.Num >= item.Num)
{
db.Delete <R_OrderDetail>(x => x.Id == detail.Id);
}
else
{
db.Update <R_OrderDetail>(new { Num = (detail.Num - item.Num) }, x => x.Id == detail.Id);//更新明细菜品数量
}
R_OrderDetail newDetail = detail;
newDetail.Id = 0;
newDetail.Num = item.Num;
newDetail.R_OrderTable_Id = newOrderTableId.ObjToInt();
db.Insert <R_OrderDetail>(newDetail);
}
}
//插入两条操作订单记录
R_OrderRecord entity = new R_OrderRecord();
entity.CreateDate = DateTime.Now;
entity.CreateUser = handleDto.CreateUser;
entity.CyddCzjlStatus = CyddStatus.拆台;
entity.CyddCzjlUserType = CyddCzjlUserType.员工;
entity.R_OrderTable_Id = handleDto.OrderTableId;
entity.R_Order_Id = originalOrder.Id;
entity.Remark = string.Format(
"拆台操作 - 把订单({0})的台号[{1}]勾选的菜品信息拆台到新订单({2})下的台号[{3}]中",
originalOrder.OrderNo, oldTable.Name, order.OrderNo, newTable.Name);
db.Insert <R_OrderRecord>(entity);
entity = new R_OrderRecord
{
CreateDate = DateTime.Now,
CreateUser = handleDto.CreateUser,
CyddCzjlStatus = CyddStatus.拆台,
CyddCzjlUserType = CyddCzjlUserType.员工,
R_OrderTable_Id = newOrderTableId.ObjToInt(),
R_Order_Id = newOrderId.ObjToInt(),
Remark = string.Format(
"拆台操作 - 把订单({0})的台号[{1}]勾选的菜品信息拆台到新订单({2})下的台号[{3}]中",
originalOrder.OrderNo, oldTable.Name, order.OrderNo, newTable.Name)
};
db.Insert <R_OrderRecord>(entity);
db.CommitTran();
res = true;
}
catch (Exception)
{
res = false;
db.RollbackTran();
throw;
}
return(res);
}
}
/// <summary>
/// 加台操作
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public List <int> AddTableHandle(AddTableSubmitDTO req)
{
List <int> res = new List <int>();
using (var db = new SqlSugarClient(Connection))
{
try
{
db.BeginTran();
List <R_OrderTable> inserOTList = new List <R_OrderTable>();
var dateItem = _extendItemRepository.GetModelList(req.CompanyId, 10003).FirstOrDefault();
if (dateItem == null)
{
throw new Exception("餐饮账务日期尚未初始化，请联系管理员");
}
if (db.Queryable <R_OrderTable>().Any(p => (p.IsOpen == true && p.IsCheckOut == false) &&
req.NewTableIds.Contains(p.R_Table_Id)))
{
throw new Exception("选择的台号已经被别的订单使用，请重新选择");
}
var orderModel = db.Queryable <R_Order>().JoinTable <R_OrderTable>((s1, s2) =>
s1.Id == s2.R_Order_Id).Where <R_OrderTable>((s1, s2) => s2.Id == req.OrderTableId)
.Select <R_Order>("s1.*").First();
var tables = db.Queryable <R_Table>().Where(p => req.NewTableIds.Contains(p.Id))
.Select(p => p.Name).ToList();
db.Update <R_Table>(new { CythStatus = CythStatus.在用 }, p => req.NewTableIds.Contains(p.Id));
foreach (var item in req.NewTableIds)
{
inserOTList.Add(new R_OrderTable
{
R_Order_Id = orderModel.Id,
R_Table_Id = item,
CreateDate = DateTime.Now,
PersonNum = 0,
IsCheckOut = false,
IsOpen = true,
IsLock = false,
BillDate = Convert.ToDateTime(dateItem.ItemValue),
R_Market_Id = req.CurrentMarketId
});
}
var orderTableIds = db.InsertRange <R_OrderTable>(inserOTList).ToList();
var orderTables = db.Queryable <R_OrderTable>()
.Where(p => p.R_Order_Id == orderModel.Id).ToList();
int personNumAvg = orderTables.Count() > 1 ?
orderModel.PersonNum / orderTables.Count() : orderModel.PersonNum; //台号人均
int personNumRemainder = orderTables.Count() > 1 ?
orderModel.PersonNum % orderTables.Count() : 0; //台号余人
int eachRemainder = 0;
if (orderTables != null && orderTables.Count() > 0)
{
foreach (var item in orderTableIds)
{
res.Add(Convert.ToInt32(item));
}
foreach (var orderTable in orderTables)
{
eachRemainder++;
orderTable.PersonNum = personNumAvg + (personNumRemainder - eachRemainder >= 0 ? 1 : 0);
}
db.UpdateRange <R_OrderTable>(orderTables);//更新订单台号人数
}
R_OrderRecord record = new R_OrderRecord
{
CreateDate = DateTime.Now,
R_Order_Id = orderModel.Id,
CreateUser = req.CreateUser,
CyddCzjlStatus = CyddStatus.开台,
CyddCzjlUserType = CyddCzjlUserType.员工,
Remark = string.Format(
"开台操作-订单（{0}）开台（{1}）", orderModel.OrderNo, tables.Join(",")),
R_OrderTable_Id = orderTableIds.Count == 1 ? Convert.ToInt32(orderTableIds[0]) : 0
};
db.Insert <R_OrderRecord>(record);
db.CommitTran();
}
catch (Exception ex)
{
db.RollbackTran();
throw ex;
}
}
return(res);
}
/// <summary>
/// 开台 拼台操作处理
/// </summary>
/// <param name="req"></param>
/// <param name="tableIds"></param>
/// <param name="msg"></param>
/// <param name="reuse">false 开台，true 拼台</param>
/// <returns></returns>
public OpenTableCreateResultDTO OpenTableHandle(
ReserveCreateDTO req, List <int> tableIds, out string msg, bool reuse = false)
{
//取餐饮账务日期 TypeId=10003
var dateItem = _extendItemRepository.GetModelList(req.CompanyId, 10003).FirstOrDefault();
using (var db = new SqlSugarClient(Connection))
{
if (dateItem == null)
{
throw new Exception("餐饮账务日期尚未初始化，请联系管理员");
}
DateTime accDate = DateTime.Today;
if (!DateTime.TryParse(dateItem.ItemValue, out accDate))
{
throw new Exception("餐饮账务日期设置错误，请联系管理员");
}
string messge = string.Empty;
OpenTableCreateResultDTO res = new OpenTableCreateResultDTO();
string ids = string.Join(",", tableIds);
try
{
db.BeginTran();
var isCanOpen = db.Queryable <R_Table>()
.Any(p => p.CythStatus != CythStatus.空置 && tableIds.Contains(p.Id));
if ((!isCanOpen && !reuse) || reuse)
{
int insertId = req.Id;
R_Order model = Mapper.Map <ReserveCreateDTO, R_Order>(req);
if (req.Id <= 0)
{
model.OpenDate = model.CreateDate;
var insert = db.Insert <R_Order>(model); //订单主表
insertId = Convert.ToInt32(insert);
}
else
{
if (req.Id > 0 &&
req.CyddStatus == CyddStatus.预定 &&
req.ReserveDate.HasValue &&
req.ReserveDate.Value < DateTime.Now.AddHours(-1))//判断是否超过时间，保留一小时
{
throw new Exception("此预订订单已过期，当前时间已超过预订时间("
+ req.ReserveDate.Value.ToString("yyyy-MM-dd HH:mm") + ")！");
}
if (req.Id > 0 && req.CyddStatus == CyddStatus.预定 &&
req.ReserveDate.HasValue &&
req.ReserveDate.Value > DateTime.Now.AddHours(8))//判断是否已到预订时间
{
throw new Exception("此预订订单未到预订开台时间(" +
req.ReserveDate.Value.ToString("yyyy-MM-dd HH:mm") +
")，但可在此预订时间提前8小时开台！");
}
db.Update <R_Order>(new
{
CyddStatus = CyddStatus.开台,
OpenDate = DateTime.Now
}, x => x.Id == req.Id);
}
if (!reuse)//开台时更新台状态
{
db.Update <R_Table>(new
{
CythStatus = (int)CythStatus.在用,
}, p => tableIds.Contains(p.Id));
}
res.OrderId = insertId;
int personNumAvg = tableIds.Count() > 1 ?
req.PersonNum / tableIds.Count() : req.PersonNum; //台号人均
int personNumRemainder = tableIds.Count() > 1 ?
req.PersonNum % tableIds.Count() : 0; //台号余人
int eachRemainder = 0;
if (tableIds != null && tableIds.Count > 0)
{
res.OrderTableIds = new List <int>();
int orderTableId = 0;
if (req.Id <= 0)
{
List <R_OrderTable> otList = new List <R_OrderTable>();
foreach (var item in tableIds)
{
eachRemainder++;
R_OrderTable obj = new R_OrderTable();
obj.R_Order_Id = insertId;
obj.R_Table_Id = item;
obj.CreateDate = DateTime.Now;
obj.IsOpen = true; //开台标识
obj.PersonNum = personNumAvg + (personNumRemainder - eachRemainder >= 0 ? 1 : 0);
obj.R_Market_Id = req.CurrentMarketId;
obj.BillDate = accDate;
otList.Add(obj);
}
db.InsertRange(otList);
}
else
{
db.Update <R_OrderTable>(new { IsOpen = true, R_Market_Id = req.CurrentMarketId, BillDate = accDate }
, x => x.R_Order_Id == req.Id);
}
res.OrderTableIds = db.Queryable <R_OrderTable>()
.Where(x => x.R_Order_Id == insertId).Select(x => x.Id).ToList();
var tableNames = db.Queryable <R_Table>()
.Where(x => tableIds.Contains(x.Id))
.Select(x => x.Name).ToList();
res.TablesName = tableNames;
R_OrderRecord record = new R_OrderRecord
{
CreateDate = DateTime.Now,
R_Order_Id = insertId,
CreateUser = req.CreateUser,
CyddCzjlStatus = CyddStatus.开台,
CyddCzjlUserType = req.UserType,
Remark = string.Format(
"开台操作-订单（{0}）开台（{1}）",
model.OrderNo, tableNames.Join(",")),
R_OrderTable_Id = res.OrderTableIds.Count == 1 ? orderTableId : 0 //订单操作纪录
};
db.Insert <R_OrderRecord>(record);
}
else
{
res = null;
msg = "开台请选择餐台！";
//db.RollbackTran();
//return res;
}
}
else
{
res = null;
messge = "所选台号已被占用，请重新选择";
}
db.CommitTran();
}
catch (Exception e)
{
res = null;
messge = e.Message;
db.RollbackTran();
}
msg = messge;
return(res);
}
}
/// <summary>
/// 换桌操作处理
/// </summary>
/// <param name="handleDto"></param>
/// <returns></returns>
public bool ChangeTableHandle(ChangeTableSubmitDTO handleDto)
{
using (var db = new SqlSugarClient(Connection))
{
bool res = true;
try
{
var userInfo = OperatorProvider.Provider.GetCurrent();
db.BeginTran();
var orderTable = db.Queryable <R_OrderTable>()
.Where(p => p.Id == handleDto.OrderTableId)
.FirstOrDefault();
var newTable = db.Queryable <R_Table>()
.FirstOrDefault(p => p.Id == handleDto.NewTableId);
if (newTable.CythStatus == CythStatus.在用)
{
throw new Exception(string.Format(
"所选台号（{0}）已被使用，请重新选择！", newTable.Name));
}
//var orderTableList = OrderRep.GetOrderTableListBy(handleDto.OldTableId, SearchTypeBy.台号Id);
var orderTableList = db.Queryable <R_OrderTable>()
.Where(x => x.R_Table_Id == handleDto.OldTableId &&
!x.IsCheckOut && x.IsOpen)
.ToList();
db.Update <R_OrderTable>(new
{
R_Table_Id = handleDto.NewTableId
}, p => p.Id == handleDto.OrderTableId);
if (orderTableList.Count == 1)
{
db.Update <R_Table>(new
{
CythStatus = CythStatus.清理
}, p => p.Id == handleDto.OldTableId);
}
db.Update <R_Table>(new
{
CythStatus = CythStatus.在用
}, p => p.Id == handleDto.NewTableId);
var order = db.Queryable <R_Order>()
.Where(x => x.Id == orderTable.R_Order_Id)
.FirstOrDefault();
var market = db.Queryable <R_Market>().FirstOrDefault(p => p.Id == order.R_Market_Id);
var resturant = db.Queryable <R_Restaurant>().FirstOrDefault(p => p.Id == order.R_Restaurant_Id);
var oldTableName = db.Queryable <R_Table>()
.Where(x => x.Id == handleDto.OldTableId)
.Select(x => x.Name)
.FirstOrDefault();
R_OrderRecord record = new R_OrderRecord
{
CreateDate = DateTime.Now,
R_Order_Id = orderTable.R_Order_Id,
R_OrderTable_Id = handleDto.OrderTableId,
CyddCzjlStatus = CyddStatus.换桌,
Remark = string.Format("订单（{0}）换桌从台[{1}]换至:[{2}]",
order.OrderNo, oldTableName, newTable.Name),
CyddCzjlUserType = CyddCzjlUserType.员工,
CreateUser = handleDto.CreateUser
};
db.Insert <R_OrderRecord>(record);
var areaIds = newTable.R_Area_Id;
var prints = db.Queryable <Printer>()
.JoinTable <R_WeixinPrint>((s1, s2) => s1.Id == s2.Print_Id)
.JoinTable <R_WeixinPrint, R_WeixinPrintArea>((s1, s2, s3) => s2.Id == s3.R_WeixinPrint_Id)
.Where <R_WeixinPrint, R_WeixinPrintArea>((s1, s2, s3) => s1.IsDelete == false &&
s2.PrintType == PrintType.换台区域出单 && areaIds == s3.R_Area_Id).Select("s1.*").ToList();
var printerList = prints.Distinct().ToList();
if (printerList.Any())
{
string cpdyThGuid = string.Empty; //出品打印按桌号出生成标识
cpdyThGuid = orderTable.Id + DateTime.Now.ToString("yyyyMMddHHmmssfff");
List <Cpdy> cydyInsert = new List <Cpdy>();
foreach (var print in printerList)
{
cydyInsert.Add(new Cpdy
{
cymxxh00 = orderTable.Id,
cyzdxh00 = orderTable.R_Order_Id,
cymxdm00 = market.Name,
cymxmc00 = string.Format("订单号:({0}) 台号:({1}) 转至 台号:({2})", order.OrderNo, oldTableName, newTable.Name),
cymxdw00 = userInfo.UserName,
cymxsl00 = string.Empty,
cymxdybz = false,
cymxyj00 = print.IpAddress,
cymxclbz = "0",
cymxczrq = DateTime.Now,
cymxzdbz = "2",
//cymxyq00 = detail.CyddMxName,
//cymxzf00 = detail.CyddMxName,
//cymxpc00 = detail.CyddMxName,
cymxczy0 = userInfo.UserName,
cymxfwq0 = print.PcName,
cymxczdm = userInfo.UserCode,
cymxje00 = "",
cymxth00 = oldTableName,
cymxrs00 = order.PersonNum.ToString(),
cymxct00 = resturant.Name,
cymxzdid = cpdyThGuid,
cymxbt00 = "换台单",
cymxzwrq = DateTime.Now.ToString("yyyy-MM-dd"),
cpdysdxh = cpdyThGuid,
cymxdk00 = print.Name,
cymxgdbz = false,
cpdyfsl0 = market.Name
});
}
db.InsertRange <Cpdy>(cydyInsert);
}
db.CommitTran();
}
catch (Exception e)
{
res = false;
db.RollbackTran();
throw e;
}
return(res);
}
}
public bool JoinTableHandle(JoinTableSubmitDTO handleDto)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
var fromOrderModel = db.Queryable <R_Order>()
.Where(x => x.Id == handleDto.FromTableObj.OrderId)
.FirstOrDefault();
var toOrderModel = db.Queryable <R_Order>()
.Where(x => x.Id == handleDto.ToTableObj.OrderId)
.FirstOrDefault();
var fromTableModel = db.Queryable <R_Table>()
.FirstOrDefault(p => p.Id == handleDto.FromTableObj.TableId);
var toTableModel = db.Queryable <R_Table>()
.FirstOrDefault(p => p.Id == handleDto.ToTableObj.TableId);
if (fromOrderModel.CyddStatus != CyddStatus.点餐 && fromOrderModel.CyddStatus != CyddStatus.开台)
{
throw new Exception("当前订单状态异常,请退出后重新操作");
}
if (toOrderModel.CyddStatus != CyddStatus.开台 && toOrderModel.CyddStatus != CyddStatus.点餐)
{
throw new Exception("并台的订单状态异常,请退出后重新操作");
}
if (fromTableModel.CythStatus != CythStatus.在用)
{
throw new Exception("当前台号状态异常,请退出后重新操作");
}
if (toTableModel.CythStatus != CythStatus.在用)
{
throw new Exception("并台的台号状态异常,请退出后重新操作");
}
var originalOrderList = db.Queryable <R_OrderTable>()
.Where(x => x.R_Order_Id == handleDto.FromTableObj.OrderId)
.ToList();
var originalTableList = db.Queryable <R_OrderTable>()
.Where(x =>
x.R_Table_Id == handleDto.FromTableObj.TableId &&
!x.IsCheckOut && x.IsOpen)
.ToList();
db.BeginTran();
//此订单只有一张台时，更改原订单状态
if (originalOrderList.Count == 1)
{
db.Update <R_Order>(new { CyddStatus = CyddStatus.取消 }, x => x.Id == handleDto.FromTableObj.OrderId);
}
db.Delete <R_OrderTable>(x => x.Id == handleDto.FromTableObj.OrderTableId);//删除原订单台号记录
if (originalTableList.Count == 1)
{
db.Update <R_Table>(new
{
CythStatus = CythStatus.空置
}, x => x.Id == handleDto.FromTableObj.TableId);
}
//更新订单明细关联订单台号信息
db.Update <R_OrderDetail>(
new
{
R_OrderTable_Id = handleDto.ToTableObj.OrderTableId
},
x => x.R_OrderTable_Id == handleDto.FromTableObj.OrderTableId);
//插入两条操作订单记录
R_OrderRecord entity = new R_OrderRecord();
entity.CreateDate = DateTime.Now;
entity.CreateUser = handleDto.UserId;
entity.CyddCzjlStatus = CyddStatus.并台;
entity.CyddCzjlUserType = CyddCzjlUserType.员工;
entity.R_OrderTable_Id = handleDto.FromTableObj.OrderTableId;
entity.R_Order_Id = handleDto.FromTableObj.OrderId;
entity.Remark = string.Format(
"并台操作 - 把订单({0})的台号[{1}]的菜品信息,并台到订单({2})下的台号[{3}]中",
fromOrderModel.OrderNo, fromTableModel.Name,
toOrderModel.OrderNo, toTableModel.Name);
db.Insert <R_OrderRecord>(entity);
entity = new R_OrderRecord
{
CreateDate = DateTime.Now,
CreateUser = handleDto.UserId,
CyddCzjlStatus = CyddStatus.并台,
CyddCzjlUserType = CyddCzjlUserType.员工,
R_OrderTable_Id = handleDto.ToTableObj.OrderTableId,
R_Order_Id = handleDto.ToTableObj.OrderId,
Remark = string.Format(
"并台操作 - 收到订单({0})的台号[{1}]的菜品信息,并台到订单({2})下的台号[{3}]中",
fromOrderModel.OrderNo, fromTableModel.Name,
toOrderModel.OrderNo, toTableModel.Name)
};
db.Insert <R_OrderRecord>(entity);
//OrderPayRecord
db.Update <R_OrderPayRecord>(new { R_Order_Id = handleDto.ToTableObj.OrderId }, x => x.R_Order_Id == handleDto.FromTableObj.OrderId && x.CyddJzStatus == CyddJzStatus.已付 && x.PId == 0);
db.CommitTran();
}
catch (Exception e)
{
result = false;
db.RollbackTran();
throw e;
}
return(result);
}
}
/// <summary>
/// 保存推送信息（主信息，详细信息）
/// </summary>
/// <param name="weChatMain"></param>
/// <param name="weChatDetailList"></param>
/// <returns></returns>
public bool SavePushMsg(Business_WeChatPush_Information weChatMain, List <Business_WeChatPushDetail_Information> weChatDetailList, bool isEdit)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
_dbMsSql.BeginTran();
if (isEdit) //编辑
{
var model = new
{
Title = weChatMain.Title,
RevenueType = weChatMain.RevenueType,
CountersignType = weChatMain.CountersignType,
Status = weChatMain.Status,
Timed = weChatMain.Timed,
TimedSendTime = weChatMain.TimedSendTime,
Important = weChatMain.Important,
Message = weChatMain.Message,
History = weChatMain.History,
PeriodOfValidity = weChatMain.PeriodOfValidity,
ChangeDate = weChatMain.ChangeDate,
ChangeUser = weChatMain.ChangeUser,
CoverImg = weChatMain.CoverImg,
CoverDescption = weChatMain.CoverDescption,
ExercisesVGUID = weChatMain.ExercisesVGUID, //推送习题Vguid
KnowledgeVGUID = weChatMain.KnowledgeVGUID, //推送知识库Vguid
RedpacketMoney = weChatMain.RedpacketMoney,
RedpacketMoneyFrom = weChatMain.RedpacketMoneyFrom,
RedpacketMoneyTo = weChatMain.RedpacketMoneyTo
};
if (model.PeriodOfValidity == null) //说明是永久有效，则加入知识库
{
//先查询改知识是否存在
bool isExist = _dbMsSql.Queryable <Business_KnowledgeBase_Information>().Any(i => i.Title == model.Title);
if (!isExist)
{
//不存在则插入
var knowledge = new Business_KnowledgeBase_Information()
{
Title = model.Title,
Content = weChatMain.MessageType == 4 ? model.Message + " " + "4" : model.Message,
Type = "1",
Status = "1",
Remark = "",
CreatedDate = DateTime.Now,
CreatedUser = CurrentUser.GetCurrentUser().LoginName,
Vguid = Guid.NewGuid()
};
_dbMsSql.Insert(knowledge, false);
}
else
{
//存在则更新
_dbMsSql.Update <Business_KnowledgeBase_Information>(new
{
//Title = model.Title,
Content = model.Message,
ChangeUser = CurrentUser.GetCurrentUser().LoginName,
ChangeDate = DateTime.Now,
}, i => i.Title == model.Title);
}
}
result = _dbMsSql.Update <Business_WeChatPush_Information>(model, i => i.VGUID == weChatMain.VGUID);
//存入操作日志表
string logData = JsonHelper.ModelToJson <Business_WeChatPush_Information>(weChatMain);
_ll.SaveLog(4, 14, Common.CurrentUser.GetCurrentUser().LoginName, weChatMain.Title, logData);
}
else //新增
{
if (weChatMain.PeriodOfValidity == null)
{
var knowledge = new Business_KnowledgeBase_Information()
{
Title = weChatMain.Title,
Content = weChatMain.Message,
Type = "1",
Status = "1",
Remark = "",
CreatedDate = weChatMain.CreatedDate,
CreatedUser = weChatMain.CreatedUser,
Vguid = Guid.NewGuid()
};
_dbMsSql.Insert(knowledge, false);
}
result = _dbMsSql.Insert(weChatMain, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(weChatMain);
_ll.SaveLog(1, 13, weChatMain.CreatedUser, weChatMain.Title, logData);
}
_dbMsSql.Delete <Business_WeChatPushDetail_Information>(i => i.Business_WeChatPushVguid == weChatMain.VGUID);
//存入操作日志表
string log = JsonHelper.ModelToJson(weChatDetailList);
_ll.SaveLog(1, 15, weChatMain.CreatedUser, weChatMain.Title, log);
_dbMsSql.SqlBulkCopy(weChatDetailList);
_dbMsSql.CommitTran();
}
catch (Exception ex)
{
_dbMsSql.RollbackTran();
LogHelper.WriteLog(ex.Message + "/n" + ex.ToString() + "/n" + ex.StackTrace);
}
return(result);
}
}
//public OutsideStockInResult Warehousing([FromBody]OutsideStockInDto data)
public string Warehousing(string WarehousingId, string WarehousingType, string WarehousingTime, string ProductionPlanId, string BatchPlanId, string WorkAreaName, string SuppliesKinds, string SuppliesInfoList)
{
OutsideStockInDto data = null;
string resultStr = null;
try
{
data = OutsideStockInDto.Create(
Guard.GuardEmpty(() => WarehousingId),
Guard.GuardEmpty(() => WarehousingType),
Guard.GuardEmpty(() => WarehousingTime),
Guard.GuardEmpty(() => ProductionPlanId),
Guard.GuardEmpty(() => BatchPlanId),
Guard.GuardEmpty(() => WorkAreaName),
Guard.GuardInteger(() => SuppliesKinds),
Guard.GuardEmpty(() => SuppliesInfoList)
);
_sqlClient.BeginTran();
string jsonSuppliesInfoStr = data.SuppliesInfoList;
OutsideWarehousingMaterialDto[] suppliesInfos = JsonConvert.DeserializeObject <OutsideWarehousingMaterialDto[]>(jsonSuppliesInfoStr);
Wms_mestask mesTask = new Wms_mestask()
{
MesTaskId = PubId.SnowflakeId,
MesTaskType = MESTaskTypes.StockIn,
WarehousingId = data.WarehousingId, //入库单编号
WarehousingType = data.WarehousingType, //入库类型
WarehousingTime = Convert.ToDateTime(data.WarehousingTime), //入库时间
ProductionPlanId = data.ProductionPlanId, //生产令号
BatchPlanId = data.BatchPlanId, //批次号
WorkAreaName = data.WorkAreaName, //作业区
SuppliesKinds = data.SuppliesKinds, //物料种类
SuppliesInfoJson = "", // jsonSuppliesInfoStr, //物料信息
WorkStatus = MESTaskWorkStatus.WaitPlan, //等待计划
NotifyStatus = MESTaskNotifyStatus.Requested, //已接收
CreateDate = DateTime.Now
};
_mastaskServices.Insert(mesTask);
RouteData routeData = CreateWMSStockin(mesTask, suppliesInfos);
if (!routeData.IsSccuess)
{
_sqlClient.RollbackTran();
OutsideStockInResult result = new OutsideStockInResult()
{
ErrorId = routeData.CodeString,
ErrorInfo = routeData.Message,
Success = false,
WarehousingId = data.WarehousingId,
WarehousingTime = data.WarehousingTime
};
resultStr = JsonConvert.SerializeObject(result);
return(resultStr);
}
else
{
_sqlClient.CommitTran();
OutsideStockInResult result = new OutsideStockInResult()
{
Success = true,
ErrorId = string.Empty,
ErrorInfo = string.Empty,
WarehousingId = data.WarehousingId,
WarehousingTime = data.WarehousingTime
};
resultStr = JsonConvert.SerializeObject(result);
return(resultStr);
}
}
catch (Exception ex)
{
_sqlClient.RollbackTran();
OutsideStockInResult result = new OutsideStockInResult()
{
Success = false,
ErrorId = "-1",
ErrorInfo = ex.ToString()
// WarehousingId = Warehousingid,
// WarehousingTime = Warehousingtime
};
resultStr = JsonConvert.SerializeObject(result);
return(resultStr);
}
finally {
LogRequest("Warehousing", data, resultStr);
}
}
/// <summary>
/// 添加 队列排班detail表、relation表
/// </summary>
/// <param name="hospitalId">医院组织机构代码</param>
/// <param name="modalityId">检查类型Id</param>
/// <param name="db">数据库连接对象</param>
/// <param name="info">队列排班主表</param>
private void AddDateFormat(string hospitalId, string modalityId, SqlSugarClient db, t_mt_queuearrangemain info = null)
{
var mainInfo = new t_mt_queuearrangemain();
var listDetailInfo = new List <t_mt_queuearrangedetail>();
var listRelationInfo = new List <t_mt_queuerearrangerelation>();
try
{
var firstDay = CommonHandleMethod.GetWeekFirstDayMon(DateTime.Now);
var lastDay = CommonHandleMethod.GetWeekLastDaySun(DateTime.Now);
//获取该检查类型下的所有队列
var queueList = db.Queryable <t_mt_devicegroup>().Where(n => n.IsDelete == "0").Where(n => n.State == 1).Where(n => n.ClinicID == modalityId).ToList().Select(n => new
{
QueueId = n.ID,
QueueName = n.GroupName
}).ToList();
#region "main表"
if (info == null)
{
mainInfo.ID = CommonHandleMethod.GetID();
mainInfo.HospitalID = hospitalId;
mainInfo.ModalityID = modalityId;
mainInfo.Modality = CommonHandleMethod.GetModalityNameByGUID(modalityId);
mainInfo.IsDelete = 0;
mainInfo.CreateDT = DateTime.Now.ToDate4();
mainInfo.QueueArrangeStartDate = firstDay.ToDate1();
mainInfo.QueueArrangeEndDate = lastDay.ToDate1();
mainInfo.SequenceNumber = 1;
}
else
{
mainInfo = info;
firstDay = mainInfo.QueueArrangeStartDate.ToDateTime();
}
#endregion
#region "detail表"
for (var i = 0; i < 7; i++)
{
for (var j = 1; j <= 4; j++)
{
var detailInfo = new t_mt_queuearrangedetail();
detailInfo.ID = CommonHandleMethod.GetID();
detailInfo.QueueArrangeMainID = mainInfo.ID;
detailInfo.QueueArrangeDate = firstDay.AddDays(i).ToDate1();
detailInfo.QueueArrangeWeekDay = CommonHandleMethod.CaculateWeekDay(firstDay.AddDays(i));
detailInfo.QueueArrangePeriod = j;
detailInfo.CreateDT = DateTime.Now.ToDate4();
detailInfo.IsDelete = 0;
listDetailInfo.Add(detailInfo);
#region "relation表"
queueList.ForEach(item =>
{
var relationInfo = new t_mt_queuerearrangerelation();
relationInfo.ID = CommonHandleMethod.GetID();
relationInfo.QueueArrangeDetailID = detailInfo.ID;
relationInfo.QueueID = item.QueueId;
relationInfo.QueueName = item.QueueName;
relationInfo.State = 0;
relationInfo.CreateDT = DateTime.Now.ToDate4();
relationInfo.IsDelete = 0;
listRelationInfo.Add(relationInfo);
});
#endregion
}
}
#endregion
//开始事务
db.BeginTran();
db.Insertable <t_mt_queuearrangemain>(mainInfo).ExecuteCommand();
db.Insertable <t_mt_queuearrangedetail>(listDetailInfo).ExecuteCommand();
db.Insertable <t_mt_queuerearrangerelation>(listRelationInfo).ExecuteCommand();
//提交事务
db.CommitTran();
}
catch (Exception ex)
{
db.RollbackTran();
throw (ex);
}
}
/// <summary>
/// 辙台操作
/// </summary>
/// <param name="orderTableId"></param>
/// <returns></returns>
public bool CancelOrderTable(CancelOrderTableSubmitDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool res = false;
try
{
db.BeginTran();
var detailList = db.Queryable <R_OrderDetail>()
.Where(p => p.R_OrderTable_Id == req.OrderTableId && p.CyddMxStatus != CyddMxStatus.保存);
var orderTableModel = db.Queryable <R_OrderTable>()
.Where(p => p.Id == req.OrderTableId).First();
var orderModel = db.Queryable <R_Order>()
.Where(p => p.Id == orderTableModel.R_Order_Id).First();
if (detailList.Any())
{
throw new Exception("订单台号下已有下单菜品,不能执行撤台操作");
}
else
{
List <R_OrderRecord> records = new List <R_OrderRecord>();
var otherOrderTables = db.Queryable <R_OrderTable>()
.Where(p => p.R_Table_Id == orderTableModel.R_Table_Id &&
p.IsCheckOut == false && p.IsOpen == true && p.Id != orderTableModel.Id); //该台号下未结账的记录,用于判断是否设该台号为清理状态
var orderTables = db.Queryable <R_OrderTable>()
.Where(p => p.R_Order_Id == orderModel.Id && p.Id != orderTableModel.Id); //该订单下其它订单台号纪录
db.Update <R_OrderTable>(new { IsCheckOut = true, PersonNum = 0, IsOpen = false }, p => p.Id == orderTableModel.Id);
if (!otherOrderTables.Any())
{
db.Update <R_Table>(new { CythStatus = CythStatus.清理 }, p => p.Id == orderTableModel.R_Table_Id);
}
if (!orderTables.Any())
{
orderModel.CyddStatus = CyddStatus.取消;
}
else
{
orderModel.PersonNum = orderModel.PersonNum - orderTableModel.PersonNum;
var lastNoCheckOut = orderTables.Any(p => p.IsCheckOut == false);
var AllNoCheckOutCount = orderTables.Where(p => p.IsCheckOut == false).Count();
if (!lastNoCheckOut)
{
orderModel.CyddStatus = CyddStatus.结账;
}
}
records.Add(new R_OrderRecord
{
CreateUser = req.CreateUser,
CreateDate = DateTime.Now,
CyddCzjlStatus = CyddStatus.取消,
CyddCzjlUserType = CyddCzjlUserType.员工,
R_OrderTable_Id = orderTableModel.Id,
R_Order_Id = orderModel.Id,
Remark = string.Format("台号已撤消")
});
db.Update <R_Order>(orderModel);
db.InsertRange <R_OrderRecord>(records);
db.CommitTran();
res = true;
}
}
catch (Exception ex)
{
db.RollbackTran();
throw ex;
}
return(res);
}
}
/// <summary>
/// 回滚
/// </summary>
public void RollbackTran()
{
sqlSugarDB.RollbackTran();
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
string aaa = null;
var xx = db.Queryable <School>().Where(it => true).ToList();
var xx2 = db.Queryable <Student>().Where(it => !it.isOk).ToList();
var sl2 = db.Sqlable().Form <Student>("s").SelectToList <Student>("id");
var sl = db.Sqlable().Form <Student>("s").SelectToList <Student>("*");
db.Queryable <Student>().In("id", "1", "2", "3").ToList();
db.Queryable <Student>().In("id", new string[] { "1", "2", "3" }).ToList();
db.Queryable <Student>().In("id", new List <string> {
"1", "2", "3"
}).ToList();
var array = new string[] { "1", "2", "3" };
db.Queryable <Student>().Where(it => array.Contains(it.name));
db.Delete <Student, int>(1, 2);
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable <Student>().ToList();
var stud = new Student()
{
id = db.GetInt("select top 1 id from Student")
};
//查询单条
var single = db.Queryable <Student>().Single(c => c.id == stud.id);
//取10-20条
var page1 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable <Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable <Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable <Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable <Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable <Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable <Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable <Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable <Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable <Student>().Any(c => c.id == 1);
int maxId = db.Queryable <Student>().Max <Student, int>("id");
int minId = db.Queryable <Student>().Where(c => c.id > 0).Min <Student, int>("id");
//---------Sqlable,创建多表查询---------//
//多表查询
List <School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList <School>("st.*", new { id = 1 });
//多表分页
List <School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList <School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery <Student>("select * from Student");
//获取id
var id = db.SqlQuery <int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List <School> sList = new List <School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update <School>(new { name = "蓝翔2" }, it => it.id == id);
db.Update <School, int>(new { name = "蓝翔2" }, 1, 3, 12);
db.Update <School, string>(new { name = "蓝翔2" }, new string[] { "1", "2" });
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update(new School {
id = id, name = "蓝翔2"
});
db.Update <School>(new School {
id = id, name = "蓝翔2"
}, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete <School, int>(10);//注意主键必需为实体类的第一个属性
db.Delete <School>(it => it.id > 100);
db.Delete <School, string>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList <Student>(sql);
db.GetSingle <Student>(sql + " where id=" + stud.id);
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
/// <summary>
/// 预定保存
/// </summary>
/// <param name="req"></param>
/// <param name="tableIds"></param>
/// <param name="msg"></param>
/// <returns></returns>
public ReserveCreateDTO SaveReserveOrderHandle(ReserveCreateDTO req, List <int> tableIds, out string msg)
{
using (var db = new SqlSugarClient(Connection))
{
string messge = string.Empty;
ReserveCreateDTO res = new ReserveCreateDTO();
if (!req.ReserveDate.HasValue || (req.ReserveDate.HasValue && req.ReserveDate.Value < DateTime.Now))
{
msg = "所选预订日期时间无效，请重新选择";
res = null;
return(res);
}
string date = req.ReserveDate.Value.ToString("yyyyMMdd");
string ids = string.Join(",", tableIds);
try
{
db.BeginTran();
if (req.Id > 0)
{
var ordTabList = db.Queryable <R_OrderTable>().Where(x => x.R_Order_Id == req.Id).ToList();
if (ordTabList != null && ordTabList.Count > 0)
{
var ordTabIds = ordTabList.Select(x => x.Id).ToList();
db.Delete <R_OrderDetail>(x => ordTabIds.Contains(x.R_OrderTable_Id));//删除旧预订菜品明细
}
db.Delete <R_OrderTable>(ordTabList); //删除预订的旧订单台号记录
}
var data = db.Sqlable().From <R_Order>("s1")
.Join <R_OrderTable>("s2", "s1.Id", "s2.R_Order_Id", JoinType.Inner);
data = data.Where("s1.CyddStatus=" + (int)CyddStatus.预定);
data = data.Where("s1.R_Market_Id=" + req.R_Market_Id);
data = data.Where("CONVERT(varchar(20), s1.ReserveDate, 112)=" + date);
data = data.Where("s2.R_Table_Id in (" + ids + ")");
var list = data.SelectToList <R_Order>("s1.Id");
//var sql = data.Sql;
if (list == null || list.Count == 0)
{
string recordRemark = "";
int orderId = req.Id;
R_Order model = Mapper.Map <ReserveCreateDTO, R_Order>(req);
if (req.Id > 0)
{
db.Update <R_Order>(
new
{
ContactPerson = req.ContactPerson,
ContactTel = req.ContactTel,
CyddOrderSource = req.CyddOrderSource,
PersonNum = req.PersonNum,
ReserveDate = req.ReserveDate,
R_Market_Id = req.R_Market_Id,
R_Restaurant_Id = req.R_Restaurant_Id,
Remark = req.Remark,
TableNum = req.TableNum,
CustomerId = req.CustomerId,
BillingUser = req.BillingUser,
OrderType = req.OrderType,
BillDepartMent = req.BillDepartMent
}, x => x.Id == req.Id); //更新预订单信息
recordRemark = string.Format("修改预订 - 预订订单号({0})更新预订信息", req.OrderNo);
}
else
{
var insert = db.Insert <R_Order>(model); //订单主表
orderId = Convert.ToInt32(insert); //订单操作纪录
recordRemark = string.Format("新加预订 - 新增预订信息订单号({0})", model.OrderNo);
if (req.DepositAmount > 0)
{
db.Insert(new R_OrderPayRecord()
{
BillDate = req.AccountingDate,
CreateDate = DateTime.Now,
CreateUser = req.CreateUser,
CyddJzStatus = CyddJzStatus.已付,
CyddJzType = CyddJzType.定金,
CyddPayType = (int)CyddPayType.现金,
PayAmount = req.DepositAmount,
R_Order_Id = orderId,
R_Market_Id = req.CurrentMarketId, //当前用户登录的分市Id
R_OrderMainPay_Id = 0, //预订定金不生成当次主结Id
Remark = "预订定金",
R_Restaurant_Id = req.CurrentRestaurantId
});
}
}
string tableNames = string.Empty;
int personNumAvg = tableIds.Count() > 1 ?
req.PersonNum / tableIds.Count() : req.PersonNum; //台号人均
int personNumRemainder = tableIds.Count() > 1 ?
req.PersonNum % tableIds.Count() : 0; //台号余人
int eachRemainder = 0;
if (tableIds != null && tableIds.Count > 0)
{
List <R_OrderTable> otList = new List <R_OrderTable>();
foreach (var tId in tableIds)
{
eachRemainder++;
R_OrderTable otModel = new R_OrderTable();
otModel.R_Order_Id = orderId;
otModel.R_Table_Id = tId;
otModel.CreateDate = DateTime.Now;
otModel.PersonNum = personNumAvg + (personNumRemainder - eachRemainder >= 0 ? 1 : 0);
otModel.BillDate = req.AccountingDate;
otModel.R_Market_Id = req.CurrentMarketId;
otModel.R_OrderMainPay_Id = 0;
otList.Add(otModel);
}
db.InsertRange <R_OrderTable>(otList);//订单台号
req.OrderTableIds = db.Queryable <R_OrderTable>()
.Where(x => x.R_Order_Id == orderId).Select(x => x.Id).ToList();
tableNames = db.Queryable <R_Table>()
.Where(x => tableIds.Contains(x.Id)).Select(x => x.Name).ToList().Join(",");
}
db.Insert <R_OrderRecord>(new R_OrderRecord()
{
CreateDate = DateTime.Now,
R_Order_Id = orderId,
CreateUser = req.CreateUser,
CyddCzjlStatus = CyddStatus.预定,
CyddCzjlUserType = CyddCzjlUserType.员工,
Remark = recordRemark + (tableNames.IsEmpty() ? "" : string.Format("；预订台：（{0}）", tableNames)),
});
res = req;
res.Id = orderId;
db.CommitTran();
}
else
{
res = null;
messge = "订单台号在预定日期已被占用，请重新选择";
db.RollbackTran();
}
}
catch (Exception e)
{
res = null;
messge = e.Message;
db.RollbackTran();
}
msg = messge;
return(res);
}
}
/// <summary>
/// 回滚
/// </summary>
public void RollbackTran()
{
SqlSugarDb.RollbackTran();
}
public bool CancelOrderHandle(CancelOrderOperateDTO operateDTO)
{
//取餐饮账务日期 TypeId=10003
var dateItem = _extendItemRepository.GetModelList(operateDTO.CompanyId, 10003).FirstOrDefault();
using (var db = new SqlSugarClient(Connection))
{
bool result = false;
try
{
db.BeginTran();
var orderModel = db.Queryable <R_Order>().Where(x => x.Id == operateDTO.OrderId).FirstOrDefault();
if (orderModel == null || orderModel.Id <= 0)
{
throw new Exception("此订单不存在！");
}
if (orderModel.CyddStatus != CyddStatus.预定 && orderModel.CyddStatus != CyddStatus.开台)
{
throw new Exception(string.Format("不能取消{0}状态订单！", Enum.GetName(typeof(CyddStatus), orderModel.CyddStatus)));
}
var orderTableLsit = db.Queryable <R_OrderTable>()
.Where(x => x.R_Order_Id == operateDTO.OrderId).ToList();//取当前订单关联的订单台列表
List <int> ordTabIds = new List <int>();
List <int> tabIds = new List <int>();
if (orderTableLsit != null && orderTableLsit.Any())
{
ordTabIds = orderTableLsit.Select(x => x.Id).ToList(); // 取关联的订单台Id
tabIds = orderTableLsit.Select(x => x.R_Table_Id).ToList(); //取关联的台Id
}
//查当前关联台号下是否同时存在的其它订单（已开台未结账的）
var otherOrderTableList = db.Queryable <R_OrderTable>()
.Where(x => tabIds.Contains(x.R_Table_Id) &&
x.R_Order_Id != operateDTO.OrderId &&
!x.IsCheckOut && x.IsOpen).ToList();
var orderDetailList = db.Queryable <R_OrderDetail>()
.Where(x => ordTabIds.Contains(x.R_OrderTable_Id) && x.CyddMxStatus != CyddMxStatus.保存).ToList();
if (orderDetailList != null && orderDetailList.Any())
{
throw new Exception("不能取消已点餐订单！");
}
if (orderModel.CyddStatus == CyddStatus.开台)//开台状态下的订单才更新餐台状态，预订订单则跳过
{
foreach (var id in tabIds)
{
// 判断当前餐台是否当前同时存在多个订单，若包含则跳过
if (otherOrderTableList.Where(x => x.R_Table_Id == id).Any())
{
continue;
}
db.Update <R_Table>(new { CythStatus = CythStatus.清理 }, x => x.Id == id);
}
}
var payList = db.Queryable <R_OrderPayRecord>()
.Where(x => x.R_Order_Id == operateDTO.OrderId && x.CyddJzType == CyddJzType.定金).ToList();
if (payList != null && payList.Count > 0)
{
List <R_OrderPayRecord> newPayList = new List <R_OrderPayRecord>();
foreach (var item in payList)
{
var model = Mapper.Map <R_OrderPayRecord, R_OrderPayRecord>(item);
model.Id = 0;
model.PayAmount = -model.PayAmount;
model.CreateDate = DateTime.Now;
model.CreateUser = operateDTO.OperateUserId;
model.BillDate = dateItem.ItemValue.ToDateOrNull();
newPayList.Add(model);
}
db.InsertRange(newPayList);
}
result = db.Update <R_Order>(new { CyddStatus = CyddStatus.取消 }, x => x.Id == operateDTO.OrderId);
//更新订单台号 状态关闭
result = db.Update <R_OrderTable>(new { IsOpen = false }, x => x.R_Order_Id == operateDTO.OrderId);
R_OrderRecord entity = new R_OrderRecord();
entity.CreateDate = DateTime.Now;
entity.CreateUser = operateDTO.OperateUserId;
entity.CyddCzjlStatus = CyddStatus.取消;
entity.CyddCzjlUserType = CyddCzjlUserType.员工;
entity.R_OrderTable_Id = 0;
entity.R_Order_Id = operateDTO.OrderId;
entity.Remark = string.Format("取消订单操作 - 订单({0})取消", orderModel.OrderNo);
db.Insert <R_OrderRecord>(entity);
db.CommitTran();
result = true;
}
catch (Exception ex)
{
result = false;
db.RollbackTran();
throw new Exception(ex.Message);
}
return(result);
}
}
public void RollbackTran()
{
DB.RollbackTran();
}
/// <summary>
/// 获取用户答案
/// </summary>
/// <param name="mainExerciseAnswerModel"></param>
/// <param name="detailAnswerModel"></param>
/// <returns></returns>
public bool SaveUserAnswer(Business_ExercisesAnswer_Information mainExerciseAnswerModel, Business_ExercisesAnswerDetail_Information detailAnswerModel)
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
bool result = false;
bool mainEdit = false; //习题人员与答案主表中是否存在
bool detailEdit = false; //习题人员与答案附表中是否存在
try
{
dbMsSql.BeginTran();
mainEdit = dbMsSql.Queryable <Business_ExercisesAnswer_Information>().Any(i => i.BusinessPersonnelVguid == mainExerciseAnswerModel.BusinessPersonnelVguid && i.BusinessExercisesVguid == mainExerciseAnswerModel.BusinessExercisesVguid);
if (!mainEdit) //新增
{
mainExerciseAnswerModel.Vguid = Guid.NewGuid();
mainExerciseAnswerModel.CreatedDate = DateTime.Now;
mainExerciseAnswerModel.ChangeDate = DateTime.Now;
result = dbMsSql.Insert(mainExerciseAnswerModel, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(mainExerciseAnswerModel);
_ll.SaveLog(1, 34, mainExerciseAnswerModel.BusinessPersonnelVguid.ToString(), "用户" + mainExerciseAnswerModel.BusinessPersonnelVguid + " " + mainExerciseAnswerModel.BusinessExercisesVguid + "套题的答题情况", logData);
}
else
{
mainExerciseAnswerModel = dbMsSql.Queryable <Business_ExercisesAnswer_Information>().Where(i => i.BusinessPersonnelVguid == mainExerciseAnswerModel.BusinessPersonnelVguid && i.BusinessExercisesVguid == mainExerciseAnswerModel.BusinessExercisesVguid).SingleOrDefault();
result = dbMsSql.Update <Business_ExercisesAnswer_Information>(new { ChangeDate = DateTime.Now }, i => i.BusinessPersonnelVguid == mainExerciseAnswerModel.BusinessPersonnelVguid && i.BusinessExercisesVguid == mainExerciseAnswerModel.BusinessExercisesVguid);
//存入操作日志表
string logData = JsonHelper.ModelToJson(mainExerciseAnswerModel);
_ll.SaveLog(4, 34, mainExerciseAnswerModel.BusinessPersonnelVguid.ToString(), "用户" + mainExerciseAnswerModel.BusinessPersonnelVguid + " " + mainExerciseAnswerModel.BusinessExercisesVguid + "套题的答题情况", logData);
}
detailEdit = dbMsSql.Queryable <Business_ExercisesAnswerDetail_Information>().Any(i => i.BusinessAnswerExercisesVguid == mainExerciseAnswerModel.Vguid && i.BusinessExercisesDetailVguid == detailAnswerModel.BusinessExercisesDetailVguid);
if (result) //主表插入成功
{
Business_ExercisesAnswerDetail_Information newDeatailAnswerModel = new Business_ExercisesAnswerDetail_Information();
if (detailEdit) //编辑
{
newDeatailAnswerModel = dbMsSql.Queryable <Business_ExercisesAnswerDetail_Information>().Where(i => i.BusinessAnswerExercisesVguid == mainExerciseAnswerModel.Vguid && i.BusinessExercisesDetailVguid == detailAnswerModel.BusinessExercisesDetailVguid).SingleOrDefault();
var model = new
{
Answer = detailAnswerModel.Answer,
Score = detailAnswerModel.Score,
ChangeDate = DateTime.Now
};
result = dbMsSql.Update <Business_ExercisesAnswerDetail_Information>(model, i => i.Vguid == newDeatailAnswerModel.Vguid);
//存入操作日志表
string logData = JsonHelper.ModelToJson(detailAnswerModel);
_ll.SaveLog(4, 34, "", detailAnswerModel.BusinessExercisesDetailVguid + "道题的答题情况", logData);
}
else //新增
{
detailAnswerModel.BusinessAnswerExercisesVguid = mainExerciseAnswerModel.Vguid;
//关联”习题人员与答案信息“主表
detailAnswerModel.Vguid = Guid.NewGuid();
detailAnswerModel.CreatedDate = DateTime.Now;
detailAnswerModel.ChangeDate = DateTime.Now;
result = dbMsSql.Insert(detailAnswerModel, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(detailAnswerModel);
_ll.SaveLog(1, 34, "", detailAnswerModel.BusinessExercisesDetailVguid + "道题的答题情况", logData);
}
}
dbMsSql.CommitTran();
}
catch (Exception ex)
{
LogHelper.WriteLog(ex.ToString());
dbMsSql.RollbackTran();
}
return(result);
}
}
public CheckOutResultDTO ReverseOrder(ReverseOrderDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
CheckOutResultDTO res = null;
try
{
var dateItem = _extendItemRepository.GetModelList(req.CompanyId, 10003).FirstOrDefault();
if (dateItem == null)
{
throw new Exception("餐饮账务日期尚未初始化，请联系管理员");
}
DateTime accDate = DateTime.Today;
if (!DateTime.TryParse(dateItem.ItemValue, out accDate))
{
throw new Exception("餐饮账务日期设置错误，请联系管理员");
}
var tables = db.Queryable <R_Table>().JoinTable <R_OrderTable>
((s1, s2) => s1.Id == s2.R_Table_Id).Where <R_OrderTable>((s1, s2) =>
s2.R_OrderMainPay_Id == req.MainPayId).Select("s1.*").ToList();
var tableIds = tables.Select(p => p.Id).ToArray();
var tableOrderIngs = tables.Where(p => p.CythStatus == CythStatus.在用).ToList();
if (tableOrderIngs.Any())
{
var tableNames = string.Join(",", tableOrderIngs.Select(p => p.Name));
throw new Exception(string.Format("({0}) 正在开台状态,请结账后再执行该账单的反结操作", tableNames));
}
var reverTableNames = string.Join(",", tables.Select(p => p.Name));
var mainPayModel = db.Queryable <R_OrderMainPay>().First(p => p.Id == req.MainPayId);
if (mainPayModel.BillDate.Date != accDate.Date)
{
throw new Exception("该账单账务日期和系统当前账务日期不一致,不允许反结");
}
var orderModel = db.Queryable <R_Order>().First(p => p.Id == mainPayModel.R_Order_Id);
var orderTables = db.Queryable <R_OrderTable>()
.Where(p => p.R_OrderMainPay_Id == mainPayModel.Id).ToList();
var orderTableIds = orderTables.Select(p => p.Id).ToArray();
var orderDetails = db.Queryable <R_OrderDetail>()
.Where(p => orderTableIds.Contains(p.R_OrderTable_Id)).ToList();
var orderPayRecords = db.Queryable <R_OrderPayRecord>()
.Where(p => p.R_OrderMainPay_Id == mainPayModel.Id).ToList();
if (orderPayRecords.Any(p => p.CyddJzStatus == CyddJzStatus.已结 && p.CyddJzType != CyddJzType.定金))
{
throw new Exception(string.Format("该主结单已经做过反结操作，不能重复反结"));
}
db.BeginTran();
//db.Delete<R_OrderPayRecord>(p => p.R_OrderMainPay_Id == mainPayModel.Id);
//db.Delete<R_OrderMainPay>(p => p.Id == mainPayModel.Id);
//db.Update<R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已付 }, p => p.R_Order_Id == orderModel.Id);
//db.Delete<R_OrderPayRecord>(p=>p.R_Order_Id == orderModel.Id && p.PayAmount<=0 && p.CyddJzType==CyddJzType.定金);
var depositReverAll = db.Queryable <R_OrderPayRecord>().Where(p => p.R_Order_Id == orderModel.Id && p.R_OrderMainPay_Id == 0 &&
p.CyddJzType == CyddJzType.定金).ToList();
var depositRePids = depositReverAll.Where(p => p.CyddJzStatus == CyddJzStatus.已退).Select(p => p.PId).ToList();
var depositReverIds = depositReverAll.Where(p => !depositRePids.Contains(p.Id) && p.CyddJzStatus == CyddJzStatus.已结)
.Select(p => p.Id).ToList();
db.Update <R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已付 }, p => depositReverIds.Contains(p.Id));
db.Update <R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已结 }, p => p.R_OrderMainPay_Id == mainPayModel.Id);
db.Delete <R_OrderPayRecord>(p => p.R_Order_Id == orderModel.Id && p.CyddJzType == CyddJzType.定金 &&
p.CyddJzStatus == CyddJzStatus.已结 && p.R_OrderMainPay_Id > 0);
db.Update <R_OrderDetail>(new { PayableTotalPrice = 0, DiscountRate = 1 }, p => orderTableIds.Contains(p.R_OrderTable_Id));
db.Update <R_OrderTable>(new { IsCheckOut = 0, IsOpen = 1, R_OrderMainPay_Id = 0 }, p => orderTableIds.Contains(p.Id));
db.Update <R_Order>(new { RealAmount = 0, CyddStatus = (int)CyddStatus.反结, OriginalAmount = 0, ConAmount = 0, DiscountRate = 0, DiscountAmount = 0, GiveAmount = 0, ClearAmount = 0 }, p => p.Id == orderModel.Id);
db.Update <R_Table>(new { CythStatus = (int)CythStatus.在用 }, p => tableIds.Contains(p.Id));
db.Insert <R_OrderRecord>(new R_OrderRecord()
{
CreateDate = DateTime.Now,
CreateUser = req.UserId,
CyddCzjlStatus = CyddStatus.反结,
CyddCzjlUserType = CyddCzjlUserType.员工,
Remark = string.Format("执行了反结操作,台号:({0})", reverTableNames),
R_OrderTable_Id = 0,
R_Order_Id = orderModel.Id
});
List <R_OrderPayRecord> reverseRecords = new List <R_OrderPayRecord>();
#region 反写应收账
foreach (var item in orderPayRecords)
{
if (item.CyddPayType == (int)CyddPayType.挂账 || item.CyddPayType == (int)CyddPayType.转客房 ||
item.CyddPayType == (int)CyddPayType.会员卡)
{
if (item.CyddPayType == (int)CyddPayType.会员卡)
{
if (EnabelGroupFlag)
{
try
{
var dto = AutoMapper.Mapper.Map <OrderPayRecordDTO>(item);
dto.Remark = string.Format("{0} {1}", "反结会员卡", item.Remark);
List <OrderPayRecordDTO> dtoList = new List <OrderPayRecordDTO>
{
dto
};
SaveMemberConsumeInfo(dtoList, req.UserCode, false, orderModel.R_Restaurant_Id);
//ApplyChangeMemberToDb(item.SourceId, item.SourceName, req.UserCode,
// -item.PayAmount, string.Format("{0} {1}", "反结会员卡", item.Remark), false, new SqlSugarClient(ConnentionGroup));
}
catch (Exception ex)
{
throw new Exception("反结集团库记录会员卡消费信息操作失败：" + ex.Message);
}
}
else
{
try
{
ApplyChangeMemberToDb(item.SourceId, item.SourceName, req.UserCode,
-item.PayAmount, string.Format("{0} {1}", "反结会员卡", item.Remark), false, db, orderModel.R_Restaurant_Id);
}
catch (Exception ex)
{
throw new Exception("反结本地库记录会员卡消费信息操作失败：" + ex.Message);
}
}
//if (!EnabelGroupFlag)
//{
// //若已启用集团会员库，里面则不再执行，本地库会员消费记录已在插入集团库时一并插入到本地库
//}
}
else if (item.CyddPayType == (int)CyddPayType.挂账)
{
var verifyInfo = new VerifySourceInfoDTO();
verifyInfo.SourceId = item.SourceId;
verifyInfo.SourceName = item.SourceName;
verifyInfo.RestaruantId = orderModel.R_Restaurant_Id;
verifyInfo.PayMethod = (int)CyddPayType.挂账;
verifyInfo.OperateValue = item.PayAmount;
string remark = string.Format("反结挂账客户【{0}】- 代码：({1})", item.SourceName, item.SourceId);
List <string> resultList = SearchVerifyOutsideInfo(verifyInfo, db);
try
{
var paras = SqlSugarTool.GetParameters(new
{
xh = orderModel.Id, //餐饮单序号
dh = orderModel.R_Restaurant_Id + "." + orderModel.Id, //餐厅代码+'.'+餐饮单单号
lx = resultList[0].Trim(), //协议单位代码(lxdmdm00)
je = -item.PayAmount, //金额
cz = req.UserCode, //操作员代码
ctmc = orderModel.R_Restaurant_Id, //餐厅名称
fsmc = "", //分市名称
th = orderModel.Id,
rs = orderModel.PersonNum,
bz = remark,
mz = "",
atr = 0
});
db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数
db.ExecuteCommand("p_po_toys_newCY", paras);
db.CommandType = System.Data.CommandType.Text; //还原回默认
}
catch (Exception ex)
{
throw new Exception("反结挂账操作失败：" + ex.Message);
}
}
else if (item.CyddPayType == (int)CyddPayType.转客房)
{
#region 转客房处理
var verifyInfo = new VerifySourceInfoDTO();
verifyInfo.SourceId = item.SourceId;
verifyInfo.SourceName = item.SourceName;
verifyInfo.RestaruantId = orderModel.R_Restaurant_Id;
verifyInfo.PayMethod = (int)CyddPayType.转客房;
verifyInfo.OperateValue = item.PayAmount;
List <string> resultList = SearchVerifyOutsideInfo(verifyInfo, db);
try
{
var paras = SqlSugarTool.GetParameters(new
{
zh00 = Convert.ToInt32(resultList[1]), //客人账号(krzlzh00)
zwdm = resultList[0], //账项代码
hsje = -item.PayAmount, //金额
ckhm = item.SourceName, //房号(krzlfh00)
czdm = req.UserCode, //操作员代码
xfje = 1,
bz00 = "反结餐厅转客房",
bc00 = "",
});
db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数
db.ExecuteCommand("p_zw_addx", paras);
db.CommandType = System.Data.CommandType.Text; //还原回默认
}
catch (Exception ex)
{
throw new Exception("反结转客房操作失败：" + ex.Message);
}
#endregion
}
}
if (item.PayAmount != 0 && (item.CyddJzStatus != CyddJzStatus.已退 || item.CyddJzStatus != CyddJzStatus.已结) && item.CyddJzType != CyddJzType.定金)
{
item.PayAmount = -item.PayAmount;
item.CreateDate = DateTime.Now;
item.BillDate = accDate;
item.R_Market_Id = req.CurrentMarketId;
item.CreateUser = req.UserId;
item.CyddJzStatus = CyddJzStatus.已结;
if (item.CyddJzType == CyddJzType.找零)
{
item.Remark = string.Format("反结找零纪录 {0}", item.SourceName);
}
else
{
item.Remark = string.Format("反结付款纪录 {0}", item.SourceName);
}
reverseRecords.Add(item);
}
//if (item.CyddJzType==CyddJzType.转结 && item.CyddJzStatus==CyddJzStatus.已结)
//{
// reverseRecords.Add(new R_OrderPayRecord()
// {
// PayAmount = -item.PayAmount,
// CreateDate = DateTime.Now,
// BillDate = accDate,
// R_Market_Id = req.CurrentMarketId,
// CreateUser = req.UserId,
// CyddJzStatus = CyddJzStatus.已付,
// CyddJzType = CyddJzType.定金,
// CyddPayType=item.CyddPayType,
// SourceId=0,
// R_OrderMainPay_Id= mainPayModel.Id,
// Remark = string.Format("反结重置定金纪录")
// });
//}
}
db.InsertRange <R_OrderPayRecord>(reverseRecords);
#endregion
db.CommitTran();
res = new CheckOutResultDTO()
{
OrderId = orderModel.Id,
OrderMainPayId = mainPayModel.Id,
OrderTables = orderTableIds.ToList(),
ReverTableNames = reverTableNames
};
}
catch (Exception ex)
{
db.RollbackTran();
throw ex;
}
return(res);
}
}
/// <summary>
/// 保存习题信息(主信息、详细信息)
/// </summary>
/// <param name="exerciseMainModel"></param>
/// <param name="isEdit"></param>
/// <returns></returns>
public bool SaveExerciseMain(Business_Exercises_Infomation exerciseMainModel, bool isEdit, List <Business_ExercisesDetail_Infomation> exerciseDetailList)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
_dbMsSql.BeginTran();
if (isEdit)
{
//定义匿名类型model
var model = new
{
ExercisesName = exerciseMainModel.ExercisesName,
Description = exerciseMainModel.Description,
Remarks = exerciseMainModel.Remarks,
EffectiveDate = exerciseMainModel.EffectiveDate,
Status = exerciseMainModel.Status,
ChangeUser = exerciseMainModel.ChangeUser,
ChangeDate = exerciseMainModel.ChangeDate
};
result = _dbMsSql.Update <Business_Exercises_Infomation>(model, i => i.Vguid == exerciseMainModel.Vguid);
//存入操作日志表
string logData = JsonHelper.ModelToJson(exerciseMainModel);
_ll.SaveLog(4, 9, CurrentUser.GetCurrentUser().LoginName, exerciseMainModel.ExercisesName + " " + "主信息", logData);
}
else
{
result = _dbMsSql.Insert(exerciseMainModel, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(exerciseMainModel);
_ll.SaveLog(1, 8, CurrentUser.GetCurrentUser().LoginName, exerciseMainModel.ExercisesName + " " + "主信息", logData);
}
_dbMsSql.Delete <Business_ExercisesDetail_Infomation>(i => i.ExercisesInformationVguid == exerciseMainModel.Vguid);
var listExerciseLibrary = new List <Business_ExerciseLibrary_Information>();
foreach (var item in exerciseDetailList)
{
_dbMsSql.Delete <Business_ExerciseLibrary_Information>(c => c.BusinessExercisesVguid == item.ExercisesInformationVguid);
if (item.IsEntry == "1")
{
bool isExist = _dbMsSql.Queryable <Business_ExerciseLibrary_Information>().Any(c => c.ExerciseType == item.ExerciseType && c.ExerciseName == item.ExerciseName);
if (!isExist)
{
//将习题保存到习题库中
var model = new Business_ExerciseLibrary_Information()
{
ExerciseName = item.ExerciseName,
ExerciseType = item.ExerciseType,
Option = item.ExerciseOption,
Answer = item.Answer,
Score = item.Score,
BusinessExercisesVguid = item.ExercisesInformationVguid,
Status = 1,
InputType = 3,
TopicType = 0,
CreatedUser = item.CreatedUser,
CreatedDate = item.CreatedDate,
Vguid = Guid.NewGuid()
};
listExerciseLibrary.Add(model);
}
}
_dbMsSql.Insert(item, false);
//存入操作日志表
string logData = JsonHelper.ModelToJson(item);
_ll.SaveLog(4, 10, CurrentUser.GetCurrentUser().LoginName, exerciseMainModel.ExercisesName + " " + "习题详细信息", logData);
}
_dbMsSql.InsertRange(listExerciseLibrary);
_dbMsSql.CommitTran();
}
catch (Exception ex)
{
_dbMsSql.RollbackTran();
Common.LogHelper.LogHelper.WriteLog(ex.Message + "/n" + ex + "/n" + ex.StackTrace);
}
return(result);
}
}
/// <summary>
/// 生成队列排班
/// </summary>
public void GenerateQueueArrange(SqlSugarClient db, string hospitalId)
{
try
{
var validDTO = GetSystemConfig(db, hospitalId);
//当前该医院已有的排班日期段
var rsMain = db.Queryable <t_mt_queuearrangemain>().Where(n => n.IsDelete == 0)
.Where(n => n.HospitalID == hospitalId).ToList();
//获取当前医院所有检查类型
var rsModality = db.Queryable <t_mt_clinic>().Where(n => n.IsDelete == 0)
.Where(n => n.HospitalID == hospitalId).ToList();
//已经参与排班的检查类型
var rsModalityMain = rsMain.Select(n => n.ModalityID).ToList();
//当前存在未生成队列排班的检查类型
var rsRemain = rsModality.Select(n => n.ID).ToList().Except(rsModalityMain).ToList();
var modalityDateList = new List <ModalityArrangeDate>();
//检查类型-队列排班日期列表
rsModality.ForEach(item =>
{
var modalityDate = new ModalityArrangeDate();
modalityDate.ModalityId = item.ID;
modalityDate.Modality = CommonHandleMethod.GetModalityNameByGUID(item.ID);
rsMain.ForEach(ele =>
{
if (item.ID == ele.ModalityID)
{
modalityDate.ArrangeDateList.Add(ele);
}
});
if (modalityDate.ArrangeDateList.Count > 0)
{
modalityDateList.Add(modalityDate);
}
});
YLog.LogInfo($"获取检查类型/队列排班日期列表............");
//清洗后的数据列表
var tempList = ClearData(db, modalityDateList);
YLog.LogInfo($"数据清洗完成............");
var listModalityArrangeDate = new List <ModalityArrangeDate>();
//检查类型-队列排班日期列表
tempList.ForEach(item =>
{
var modalityArrangeDate = new ModalityArrangeDate();
modalityArrangeDate.ModalityId = item.ModalityId;
modalityArrangeDate.Modality = item.Modality;
if (item.ArrangeDateList.Count >= validDTO.WeekNums)
{
YLog.LogInfo($"当前检查类型:{item.Modality}的队列排班已满足号源生成周数............");
return;
}
var listArrangeMain = new List <t_mt_queuearrangemain>();
//生成其余几周的队列排班
for (var i = 0; i < (validDTO.WeekNums - item.ArrangeDateList.Count); i++)
{
var info = new t_mt_queuearrangemain();
info.ID = CommonHandleMethod.GetID();
info.IsDelete = 0;
info.CreateDT = DateTime.Now.ToDate4();
info.HospitalID = hospitalId;
info.Modality = CommonHandleMethod.GetModalityNameByGUID(item.ModalityId);
info.ModalityID = item.ModalityId;
//获取当前日期列表的 最大序号
var maxSequenceNo = item.ArrangeDateList.Max(n => n.SequenceNumber);
var maxSequenceInfo = item.ArrangeDateList.Where(n => n.SequenceNumber == maxSequenceNo).FirstOrDefault();
info.SequenceNumber = maxSequenceNo + (i + 1);
info.QueueArrangeStartDate = CommonHandleMethod.GetWeekFirstDayMon(maxSequenceInfo.QueueArrangeEndDate.ToDateTime().AddDays(7 * i + 1)).ToDate1();
info.QueueArrangeEndDate = CommonHandleMethod.GetWeekLastDaySun(maxSequenceInfo.QueueArrangeEndDate.ToDateTime().AddDays(7 * i + 1)).ToDate1();
listArrangeMain.Add(info);
}
modalityArrangeDate.ArrangeDateList = listArrangeMain;
listModalityArrangeDate.Add(modalityArrangeDate);
});
//添加队列排班主业务
CreateQueueArrange(db, hospitalId, listModalityArrangeDate, rsRemain);
//提交事务
db.CommitTran();
YLog.LogInfo($"队列排班生成完成............");
}
catch (Exception ex)
{
db.RollbackTran();
YLog.LogSystemError(ex.Message);
}
}
private static void DistributedTransactionExample()
{
Console.WriteLine("");
Console.WriteLine("#### Distributed TransactionExample Start ####");
SqlSugarClient db = new SqlSugarClient(new List <ConnectionConfig>()
{
new ConnectionConfig()
{
ConfigId = "1", DbType = DbType.Sqlite, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
},
new ConnectionConfig()
{
ConfigId = "2", DbType = DbType.Sqlite, ConnectionString = Config.ConnectionString2, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
}
});
//use db1
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));//
db.Insertable(new Order()
{
Name = "order1", CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count());
//use db2
db.ChangeDatabase("2");
db.DbMaintenance.CreateDatabase();//Create Database2
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));
db.Insertable(new Order()
{
Name = "order1", CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count());
// Example 1
Console.WriteLine("Example 1");
try
{
db.BeginTran();
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception();
db.CommitTran();
}
catch
{
db.RollbackTran();
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use db1
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
// Example 2
Console.WriteLine("Example 2");
var result = db.UseTran(() =>
{
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
if (result.IsSuccess == false)
{
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use db1
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
// Example 3
Console.WriteLine("Example 3");
var result2 = db.UseTranAsync(() =>
{
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
result2.Wait();
if (result.IsSuccess == false)
{
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use sqlserver
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use mysql
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
Console.WriteLine("#### Distributed TransactionExample End ####");
}
public bool DetailCreate(PackageCreateDTO cytc, List <PackageDetailCreateDTO> list)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
db.BeginTran();
db.Update <R_Package>(new { Price = cytc.Price, CostPrice = cytc.CostPrice }, p => p.Id == cytc.Id);
if (list != null && list.Any())
{
db.Update <R_PackageDetail>(new
{
IsDelete = true
}, p => p.R_Package_Id == cytc.Id);
foreach (var item in list)
{
if (item.Id > 0)
{
db.Update <R_PackageDetail>(new R_PackageDetail
{
Id = item.Id,
R_Package_Id = cytc.Id,
R_ProjectDetail_Id = item.R_ProjectDetail_Id,
Num = item.Num,
IsDelete = false
});
}
else
{
R_PackageDetail model = new R_PackageDetail()
{
R_Package_Id = cytc.Id,
R_ProjectDetail_Id = item.R_ProjectDetail_Id,
IsDelete = false,
Num = item.Num
};
db.Insert <R_PackageDetail>(model);
}
}
db.Update <R_Package>(new
{
CostPrice = cytc.CostPrice,
Price = cytc.Price
}, p => p.Id == cytc.Id);
}
db.CommitTran();
}
catch (Exception)
{
result = false;
db.RollbackTran();
throw;
}
return(result);
}
}
