protected void Page_Load(object sender, EventArgs e) { //ToJoinSqlInVal var par = new string[] { "a", "c", "3" }; var ids = par.ToJoinSqlInVal();//将数组转成 'a','c','3' 有防SQL注入处理 //ToSqlValue try { var par2 = "a'"; var newpar2 = par2.ToSqlValue();//对字符串防注入处理 } catch (Exception ex) { Response.Write(ex.Message); } //SqlLikeWordEncode 处理LIKE特殊字符 var likestr = SqlSugarTool.SqlLikeWordEncode("[%%%"); //GetParameterArray 获取页面参数所有参数的键和值 var pars = SqlSugarTool.GetParameterArray(); //将匿名对象转成SqlParameter var par3 = SqlSugarTool.GetParameters(new { id = 1 }); //用于生成多语言的视图 //LanguageHelper.UpdateView() }
/// <summary> /// 获取答过本套习题人员列表 /// </summary> /// <param name="vguid">习题主Vguid</param> /// <returns></returns> public JsonResultModel <U_MarkingPerson> GetAnsweredPersonList(string vguid, string isMarking, GridParams para) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { JsonResultModel <U_MarkingPerson> jsonResult = new JsonResultModel <U_MarkingPerson>(); // List<U_MarkingPerson> markingPersonList = new List<U_MarkingPerson>(); Guid exerciseVguid = Guid.Parse("00000000-0000-0000-0000-000000000000"); if (!string.IsNullOrEmpty(vguid)) { exerciseVguid = Guid.Parse(vguid); } var pars = SqlSugarTool.GetParameters(new { ExercisesVguid = exerciseVguid, Start = (para.pagenum - 1) * para.pagesize + 1, End = para.pagenum * para.pagesize, isMark = string.IsNullOrEmpty(isMarking) ? "1,2" : isMarking, Count = 0, }); //将匿名对象转成SqlParameter _dbMsSql.IsClearParameters = false; //禁止清除参数 pars[4].Direction = ParameterDirection.Output; //将Count设为 output string sql = string.Format(@"EXEC usp_ExercisesUserDetailInformation @ExercisesVguid,@Start,@End,@isMark,@count output"); var list = _dbMsSql.SqlQuery <U_MarkingPerson>(sql, pars); _dbMsSql.IsClearParameters = true; //启动请动清除参数 var outPutValue = pars[4].Value.ObjToInt(); //获取output @Count的值 jsonResult.Rows = list; jsonResult.TotalRows = outPutValue; //markingPersonList = _dbMsSql.GetList<U_MarkingPerson>(sql, new { ExercisesVguid = exerciseVguid }); //return markingPersonList; return(jsonResult); } }
/// <summary> /// 条件筛选 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="queryable">查询对象</param> /// <param name="whereString">Where后面的Sql条件语句 (例如: id=@id )</param> /// <param name="whereObj"> 匿名参数 (例如:new{id=1,name="张三"})</param> /// <returns>Queryable</returns> public static Queryable <T> Where <T>(this Queryable <T> queryable, string whereString, object whereObj = null) { var type = queryable.Type; var whereStr = string.Format(" AND {0} ", whereString); queryable.WhereValue.Add(whereStr); if (whereObj != null) { queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj)); } return(queryable); }
public List <SchemeDiscountDTO> GetSchemeDiscountList(SchemeDiscountSearchDTO req) { using (var db = new SqlSugarClient(Connection)) { var paras = SqlSugarTool.GetParameters(new { RestaurantId = req.RestaurantId, MarketId = req.MarketId, OrderId = req.OrderId == 0?33:req.OrderId }); string sql = $@" SELECT D.*, ISNULL(R.Id, 0) AS RestaurantId, ISNULL(M.Id, 0) AS MarketId, ISNULL(A.Id, 0) AS AreaId, ISNULL(A.Name, '') AS AreaName FROM dbo.R_Discount D LEFT JOIN dbo.R_Restaurant R ON R.Id = D.R_Restaurant_Id LEFT JOIN dbo.R_Market M ON M.Id = D.R_Area_Id LEFT JOIN dbo.R_Area A ON A.Id = D.R_Area_Id left join dbo.R_Order OD on R.Id=OD.R_Restaurant_Id WHERE (GETDATE() BETWEEN D.StartDate AND D.EndDate) AND D.IsEnable = 1 AND D.R_Restaurant_Id = @RestaurantId and D.IsDelete=0 and OD.Id=@OrderId and (OD.R_Market_Id=D.R_Market_Id OR 2=0)"; //AND (D.R_Market_Id = @MarketId OR D.R_Market_Id = 0) var list = db.SqlQuery <SchemeDiscountDTO>(sql, paras); if (list != null && list.Any()) { var discountIds = list.Select(x => x.Id).ToArray().Join(","); var detailParas = SqlSugarTool.GetParameters(new { DiscountIds = discountIds }); string detailSql = "SELECT DC.*, D.Id AS SchemeId, D.[Name] AS SchemeName, " + "D.R_Area_Id AS AreaId, D.R_Market_Id AS MarketId, " + "isnull(C.Id,0) AS CategoryId, isnull(C.Name,'全部') AS CategoryName " + "FROM dbo.R_DiscountCategory DC " + "left JOIN dbo.R_Discount D ON D.Id = DC.R_Discount_Id " + "left JOIN dbo.R_Category C ON C.Id = DC.R_Category_Id " + "WHERE DC.R_Discount_Id IN ({0})"; detailSql = string.Format(detailSql, discountIds); var detailList = db.SqlQuery <SchemeDiscountDetailDTO>(detailSql); foreach (var item in list) { item.DetailList = detailList.Where(x => x.SchemeId == item.Id).ToList(); } } return(list); } }
/// <summary> /// 基于原生Sql的查询 /// </summary> private void SqlQuery() { using (var db = SugarDao.GetInstance()) { //转成list var list1 = db.SqlQuery <StudentEntity>("select * from Student"); //转成list带参 var list2 = db.SqlQuery <StudentEntity>("select * from Student where id=@id", new { id = 1 }); //转成dynamic dynamic list3 = db.SqlQueryDynamic("select * from student"); //转成json var list4 = db.SqlQueryJson("select * from student"); //返回int var list5 = db.SqlQuery <int>("select top 1 id from Student").SingleOrDefault(); //反回键值 var list6 = db.SqlQuery <KeyValuePair <string, string> >("select id,name from Student") .ToDictionary(it => it.Key, it => it.Value); //反回List<string[]> var list7 = db.SqlQuery <string[]>("select top 1 id,name from Student").SingleOrDefault(); //存储过程 var spResult = db.SqlQuery <School>("exec sp_school @p1,@p2", new { p1 = 1, p2 = 2 }); //存储过程加Output var pars = SqlSugarTool.GetParameters(new { p1 = 1, p2 = 0 }); //将匿名对象转成SqlParameter db.IsClearParameters = false; //禁止清除参数 pars[1].Direction = ParameterDirection.Output; //将p2设为 output var spResult2 = db.SqlQuery <School>("exec sp_school @p1,@p2 output", pars); db.IsClearParameters = true; //启用清除参数 var outPutValue = pars[1].Value; //获取output @p2的值 //存储过程优化操作 var pars2 = SqlSugarTool.GetParameters(new { p1 = 1, p2 = 0 }); //将匿名对象转成SqlParameter db.CommandType = CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 var spResult3 = db.SqlQuery <School>("sp_school", pars2); db.CommandType = CommandType.Text; //还原回默认 //获取第一行第一列的值 var v1 = db.GetString("select '张三' as name"); var v2 = db.GetInt("select 1 as name"); var v3 = db.GetDouble("select 1 as name"); var v4 = db.GetDecimal("select 1 as name"); //.... } }
static void Main(string[] args) { SqlParameter[] sp = new SqlParameter[] { new SqlParameter("name", 1) }; var ss = SqlSugarTool.GetParameters(sp); CodeTimer.InitializeByConcurrent(); CodeTimerResult ret = CodeTimer.Execute("sugar", 1, new Action(() => { using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer1"].ConnectionString)) { //var list = db.GetDataTable("select * from AccountCode where AccountType=1", new { }); //var list = db.GetDataTable("select * from AccountCode where AccountType=@AccountType", new { AccountType = 1 }); var list = db.Queryable <AccountCodeModel1>("AccountCode").Where <AccountCodeModel1>(c => c.AccountType == 1).ToList(); int rowCount = list.Count; } })); //Console.ReadKey(); }
/// <summary> /// 联表查询根据字符串 /// </summary> /// <typeparam name="T">第一个表的对象</typeparam> /// <param name="queryable">查询对象</param> /// <param name="tableName">表名(可是以表或也可以是SQL语句加括号)</param> /// <param name="shortName">表名简写</param> /// <param name="onWhere">on后面的条件</param> /// <param name="whereObj">匿名参数(例如:new{id=1,name="张三"})</param> /// <param name="type">Join的类型</param> /// <returns>Queryable</returns> public static Queryable <T> JoinTable <T>(this Queryable <T> queryable, string tableName, string shortName, string onWhere, object whereObj, JoinType type = JoinType.LEFT) { queryable.WhereIndex = queryable.WhereIndex + 100; ; var joinType = type.ToString(); var joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ", /*0*/ joinType, /*1*/ tableName, /*2*/ shortName, /*3*/ onWhere ); queryable.JoinTableValue.Add(joinStr); if (whereObj != null) { queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj)); } return(queryable); }
/// <summary> /// 基于原生Sql的查询 /// </summary> private void SqlQuery() { using (var db = SugarDao.GetInstance()) { //转成list List <Student> list1 = db.SqlQuery <Student>("select * from Student"); //转成list带参 List <Student> list2 = db.SqlQuery <Student>("select * from Student where id=@id", new { id = 1 }); //转成dynamic dynamic list3 = db.SqlQueryDynamic("select * from student"); //转成json string list4 = db.SqlQueryJson("select * from student"); //返回int var list5 = db.SqlQuery <int>("select top 1 id from Student").SingleOrDefault(); //反回键值 Dictionary <string, string> list6 = db.SqlQuery <KeyValuePair <string, string> >("select id,name from Student").ToDictionary(it => it.Key, it => it.Value); //反回List<string[]> var list7 = db.SqlQuery <string[]>("select top 1 id,name from Student").SingleOrDefault(); //存储过程 var spResult = db.SqlQuery <School>("exec sp_school @p1,@p2", new { p1 = 1, p2 = 2 }); //存储过程加Output var pars = SqlSugarTool.GetParameters(new { p1 = 1, p2 = 0 }); //将匿名对象转成SqlParameter db.IsClearParameters = false; //禁止清除参数 pars[1].Direction = ParameterDirection.Output; //将p2设为 output var spResult2 = db.SqlQuery <School>("exec sp_school @p1,@p2 output", pars); db.IsClearParameters = true; //启动请动清除参数 var outPutValue = pars[1].Value; //获取output @p2的值 //获取第一行第一列的值 string v1 = db.GetString("select '张三' as name"); int v2 = db.GetInt("select 1 as name"); double v3 = db.GetDouble("select 1 as name"); decimal v4 = db.GetDecimal("select 1 as name"); //多个结果集 var ds = db.GetDataSetAll("select 1 as id;select 2 as id"); } }
public void Init() { Console.WriteLine("启动PubMethod.Init"); using (var db = SugarDao.GetInstance()) { //ToJoinSqlInVal var par = new string[] { "a", "c", "3" }; var ids = par.ToJoinSqlInVal();//将数组转成 'a','c','3' 有防SQL注入处理 //ToSqlValue try { var par2 = "a'"; var newpar2 = par2.ToSqlValue();//对字符串防注入处理 } catch (Exception ex) { Console.WriteLine(ex.Message); } //SqlLikeWordEncode 处理LIKE特殊字符 var likestr = SqlSugarTool.SqlLikeWordEncode("[%%%"); //GetParameterArray 获取页面参数所有参数的键和值 var pars = SqlSugarTool.GetParameterArray(); //将匿名对象转成SqlParameter var par3 = SqlSugarTool.GetParameters(new { id = 1 }); //用于生成多语言的视图 //LanguageHelper.UpdateView() } }
/// <summary> /// 分页查询人员列表信息 /// </summary> /// <param name="searchParam"></param> /// <param name="para"></param> /// <returns></returns> public JsonResultModel <v_Business_PersonnelDepartment_Information> GetUserPageList(Business_PersonDepartmrnt_Search searchParam, GridParams para) { using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance()) { var jsonResult = new JsonResultModel <v_Business_PersonnelDepartment_Information>(); var currentUserInfo = CurrentUser.GetCurrentUser(); string ownfleet = string.Empty; //是系统管理员 if (currentUserInfo.LoginName.ToLower() == "sysadmin") { ownfleet = searchParam.OwnedFleet != Guid.Empty ? searchParam.OwnedFleet.ToString() : dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault().ToString(); } else //非系统管理员 { //查出当前登录人的部门[dbo].[Sys_Role_Module] Guid dep = Guid.Parse(currentUserInfo.Department); ownfleet = dep.ToString(); var listDep = dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 if (searchParam.OwnedFleet != Guid.Empty) { ownfleet = !listDep.Contains(searchParam.OwnedFleet) ? dep.ToString() : searchParam.OwnedFleet.ToString(); } } var labelStr = string.Empty; if (!string.IsNullOrEmpty(searchParam.LabelName)) { var labelArr = searchParam.LabelName.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < labelArr.Length; i++) { labelArr[i] = "'" + labelArr[i].Trim() + "'"; } labelStr = string.Join(",", labelArr); } string sql = "exec usp_Business_PersonnelDepartment_Information @UserName,@JobNumber,@OwnedFleet,@ServiceNumber,@IDNumber,@Status,@Phone,@Start,@End,@Count output,@LabelName"; var pars = SqlSugarTool.GetParameters(new { UserName = searchParam.name ?? "", JobNumber = searchParam.JobNumber ?? "", OwnedFleet = ownfleet, ServiceNumber = searchParam.ServiceNumber ?? "", IDNumber = searchParam.IDNumber ?? "", Status = searchParam.TranslationApprovalStatus ?? "", Phone = searchParam.PhoneNumber ?? "", Start = (para.pagenum - 1) * para.pagesize + 1, End = para.pagenum * para.pagesize, Count = 0, LabelName = labelStr }); //将匿名对象转成SqlParameter dbMsSql.IsClearParameters = false; //禁止清除参数 pars[9].Direction = ParameterDirection.Output; //将Count设为 output var query = dbMsSql.SqlQuery <v_Business_PersonnelDepartment_Information>(sql, pars); dbMsSql.IsClearParameters = true; //启动请动清除参数 var outPutValue = pars[9].Value.ObjToInt(); //获取output @Count的值 jsonResult.TotalRows = outPutValue; jsonResult.Rows = query; //存入操作日志表 string logData = JsonHelper.ModelToJson(jsonResult); _ll.SaveLog(3, 5, CurrentUser.GetCurrentUser().LoginName, searchParam.name + searchParam.JobNumber + searchParam.ServiceNumber + searchParam.UserID, logData); return(jsonResult); } }
private MemberInfoDTO ApplyChangeMemberToDb(int memberId, string pwd, string userCode, decimal amount, string remark, bool isGroup, SqlSugarClient db, int restaurantId) { SqlSugarClient localDb = null; if (isGroup)//是否集团库连接 { localDb = new SqlSugarClient(Connection); } string xtdmSql = "SELECT xtdmbz00 FROM xtdm WHERE xtdmlx00='YKBZ' AND xtdmdm00='YKBZ' AND xtdmbzs0='Y'"; List <string> list = null; //始终查本地库的会员卡配置的系统代码标识 if (isGroup) { list = localDb.SqlQuery <string>(xtdmSql); } else { list = db.SqlQuery <string>(xtdmSql); } if (list == null || list.Count == 0) { if (isGroup && localDb != null) { localDb.Dispose(); } throw new Exception("会员卡消费挂账关联的系统代码配置不正确,请联系管理员!"); } SqlParameter[] paras; var member = VerifyMemberInfo(memberId, amount, pwd, db); Guid guid = Guid.NewGuid(); paras = SqlSugarTool.GetParameters(new { Zs = member.Id, //客人历史序号Id KH = member.Id, //客人历史序号Id Lx = "B", dd = restaurantId.ToString(), bz = remark, //备注 je = amount, //金额 cz = userCode, //操作员代码 fs = "01", // GPID = list[0], //协议单位代码(lxdmdm00) GUID = guid }); db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 db.ExecuteCommand("p_zw_gbxf", paras); db.CommandType = System.Data.CommandType.Text; //还原回默认 if (isGroup) { string krlsSql = string.Format("SELECT krlsxh00 FROM krls WHERE krlsGUID = '{0}'", member.MemberGUID); var memberList = localDb.SqlQuery <int>(krlsSql);//查本地库的会员 if (memberList == null || memberList.Count == 0) { string insertSql = string.Format( "INSERT krls(krlszwxm, krlszt00, krlsvpkh, krlsxb00, krlsGPID, krlsGUID, krlsdh00, krlszjhm) " + "VALUES('{0}', 'Y', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}') ", member.MemberName, member.MemberCardNo, member.MemberGender, member.MemberGPID, member.MemberGUID, member.MemberPhoneNo, member.MemberIdentityNo); localDb.CommandType = System.Data.CommandType.Text; localDb.ExecuteCommand(insertSql, paras); memberList = localDb.SqlQuery <int>(krlsSql);//查本地库的会员 } paras = SqlSugarTool.GetParameters(new { Zs = memberList[0], //本地库客人历史序号Id KH = memberList[0], //本地库客人历史序号Id Lx = "B", dd = restaurantId.ToString(), bz = remark, //备注 je = amount, //金额 cz = userCode, //操作员代码 fs = "01", // GPID = list[0], //协议单位代码(lxdmdm00) GUID = guid }); localDb.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 localDb.ExecuteCommand("p_zw_gbxf", paras); localDb.CommandType = System.Data.CommandType.Text; //还原回默认 localDb.Dispose(); //销毁本地连接 } return(member); }
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); } }