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