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); } }