/// <summary>
/// 拉姆达表达示
/// </summary>
private void QueryableDemo()
{
using (var db = SugarDao.GetInstance())
{
//查询所有
var student = db.Queryable <Student>().ToList();
var studentDynamic = db.Queryable <Student>().ToDynamic();
var studentJson = db.Queryable <Student>().ToJson();
var ls = db.Queryable <Student>().Select(it => it.name).ToList();
//查询单条
var single = db.Queryable <Student>().Single(c => c.id == 1);
var single222 = db.Queryable <Student>().Single(c => 1 == c.id);
//查询单条根据主键
var singleByPk = db.Queryable <Student>().InSingle(1);
//查询单条没有记录返回空对象
var singleOrDefault = db.Queryable <Student>().SingleOrDefault(c => c.id == 11111111);
//查询单条没有记录返回空对象
var single2 = db.Queryable <Student>().Where(c => c.id == 1).SingleOrDefault();
//查询第一条
var first = db.Queryable <Student>().Where(c => c.id == 1).First();
var first2 = db.Queryable <Student>().Where(c => c.id == 1).FirstOrDefault();
//取11-20条
var page1 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(10).Take(10).ToList();
//取11-20条 等于 Skip(pageIndex-1)*pageSize).Take(pageSize) 等于 between (pageIndex-1)*pageSize and pageIndex*pageSize
var page2 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy(it => it.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(it => it.id).Skip(2).ToList();
//取前2条
var take = db.Queryable <Student>().Where(c => c.id > 10).OrderBy(it => it.id).Take(2).ToList();
//Not like
string conval = "a";
var notLike = db.Queryable <Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
//Like
conval = "三";
var like = db.Queryable <Student>().Where(c => c.name.Contains(conval)).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable <Student>().Where(c => "a" == "a").Where("id>@id", new { id = 1 }).ToList();
var student13 = db.Queryable <Student>().Where(c => "a" == "a").Where("id>100 and id in( select 1)").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable <Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable <Student>().Any(c => c.id == 1);
//获取最大Id
object maxId = db.Queryable <Student>().Max(it => it.id);
int maxId1 = db.Queryable <Student>().Max(it => it.id).ObjToInt(); //拉姆达
int maxId2 = db.Queryable <Student>().Max <int>("id"); //字符串写法
//获取最小
int minId1 = db.Queryable <Student>().Where(c => c.id > 0).Min(it => it.id).ObjToInt(); //拉姆达
int minId2 = db.Queryable <Student>().Where(c => c.id > 0).Min <int>("id"); //字符串写法
//order By
var orderList = db.Queryable <Student>().OrderBy("id desc,name asc").ToList(); //字符串支持多个排序
//可以多个order by表达示
var order2List = db.Queryable <Student>().OrderBy(it => it.name).OrderBy(it => it.id, OrderByType.desc).ToList(); // order by name as ,order by id desc
//In
var intArray = new[] { "5", "2", "3" };
var intList = intArray.ToList();
var listnew = db.Queryable <Student>().Where(it => intArray.Contains(it.name)).ToList();
var list0 = db.Queryable <Student>().In(it => it.id, 1, 2, 3).ToList();
var list1 = db.Queryable <Student>().In(it => it.id, intArray).ToList();
var list2 = db.Queryable <Student>().In("id", intArray).ToList();
var list3 = db.Queryable <Student>().In(it => it.id, intList).ToList();
var list4 = db.Queryable <Student>().In("id", intList).ToList();
var list6 = db.Queryable <Student>().In(intList).ToList();//不设置字段默认主键
//分组查询
var list7 = db.Queryable <Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select("sex,count(*) Count").ToDynamic();
var list8 = db.Queryable <Student>().Where(c => c.id < 20).GroupBy(it => it.sex).GroupBy(it => it.id).Select("id,sex,count(*) Count").ToDynamic();
List <StudentGroup> list9 = db.Queryable <Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select <StudentGroup>("Sex,count(*) Count").ToList();
List <StudentGroup> list10 = db.Queryable <Student>().Where(c => c.id < 20).GroupBy("sex").Select <StudentGroup>("Sex,count(*) Count").ToList();
//SELECT Sex,Count=count(*) FROM Student WHERE 1=1 AND (id < 20) GROUP BY Sex --生成结果
//2表关联查询
var jList = db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id) //默认left join
.Where <School>((s1, s2) => s1.id == 1)
.Select("s1.*,s2.name as schName")
.ToDynamic();
/*等于同于
* SELECT s1.*,s2.name as schName
* FROM [Student] s1
* LEFT JOIN [School] s2 ON s1.sch_id = s2.id
* WHERE s1.id = 1 */
//2表关联查询并分页
var jList2 = db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id) //默认left join
//如果要用inner join这么写
//.JoinTable<School>((s1, s2) => s1.sch_id == s2.id ,JoinType.INNER)
.Where <School>((s1, s2) => s1.id > 1)
.OrderBy(s1 => s1.name)
.Skip(10)
.Take(20)
.Select("s1.*,s2.name as schName")
.ToDynamic();
//3表查询并分页
var jList3 = db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id) // left join School s2 on s1.id=s2.id
.JoinTable <School>((s1, s3) => s1.sch_id == s3.id) // left join School s3 on s1.id=s3.id
.Where <School>((s1, s2) => s1.id > 1) // where s1.id>1
.Where(s1 => s1.id > 0)
.OrderBy <School>((s1, s2) => s1.id) //order by s1.id 多个order可以 .oderBy().orderby 叠加
.Skip(10)
.Take(20)
.Select("s1.*,s2.name as schName,s3.name as schName2")//select目前只支持这种写法
.ToDynamic();
//上面的方式都是与第一张表join，第三张表想与第二张表join写法如下
List <V_Student> jList4 =
db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id) // left join School s2 on s1.id=s2.id
.JoinTable <School, Area>((s1, s2, a1) => a1.id == s2.AreaId) // left join Area a1 on a1.id=s2.AreaId 第三张表与第二张表关联
.JoinTable <Area, School>((s1, a1, s3) => a1.id == s3.AreaId) // left join School s3 on a1.id=s3.AreaId 第四第表第三张表关联
.JoinTable <School>((s1, s4) => s1.sch_id == s4.id) // left join School s2 on s1.id=s4.id
.Select <School, Area, V_Student>((s1, s2, a1) => new V_Student {
id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name
}).ToList();
//等同于
//SELECT id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name
//FROM [Student] s1
//LEFT JOIN School s2 ON ( s1.sch_id = s2.id )
//LEFT JOIN Area a1 ON ( a1.id = s2.AreaId ) //第三张表与第二张表关联
//LEFT JOIN School s3 ON ( a1.id = s3.AreaId ) //第四张表与第三张表关联
//LEFT JOIN School s4 ON ( s1.sch_id = s4.id )
//WHERE 1=1
//Join子查询语句加分页的写法
var childQuery = db.Queryable <Area>().Where("id=@id").Select(it => new { id = it.id, name = it.name }).ToSql(); //创建子查询SQL
string childTableName = SqlSugarTool.PackagingSQL(childQuery.Key); //将SQL语句用()包成表
var queryable = db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id) //LEFT JOIN School s2 ON ( s1.sch_id = s2.id )
.JoinTable(childTableName, "a1", "a1.id=s2.areaid", new { id = 1 }, JoinType.Inner) //INNER JOIN (SELECT * FROM [Area] WHERE 1=1 AND id=@id ) a1 ON a1.id=s2.areaid
.OrderBy(s1 => s1.id);
var list = queryable.Select <School, Area, V_Student>((s1, s2, a1) => new V_Student {
id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name
})
.ToPageList(0, 200);
var count2 = queryable.Count();
//拼接例子
var queryable2 = db.Queryable <Student>().Where(it => true);
if (maxId.ObjToInt() == 1)
{
queryable2.Where(it => it.id == 1);
}
else
{
queryable2.Where(it => it.id == 2);
}
var listJoin = queryable2.ToList();
//queryable和SqlSugarClient解耦
var par = new Queryable <Student>().Where(it => it.id == 1);//声名没有connection对象的Queryable
par.DB = db;
var listPar = par.ToList();
//查看生成的sql和参数
var id = 1;
var sqlAndPars = db.Queryable <Student>().Where(it => it.id == id).OrderBy(it => it.id).ToSql();
//拉姆达支持的函数操作
var par1 = "2015-1-1"; var par2 = " 我 有空格A, ";
var r1 = db.Queryable <Student>().Where(it => it.name == par1.ObjToString()).ToList(); //ObjToString会将null转转成""
var r2 = db.Queryable <InsertTest>().Where(it => it.d1 == par1.ObjToDate()).ToList();
var r3 = db.Queryable <InsertTest>().Where(it => it.id == 1.ObjToInt()).ToList(); //ObjToInt会将null转转成0
var r4 = db.Queryable <InsertTest>().Where(it => it.id == 2.ObjToDecimal()).ToList();
var r5 = db.Queryable <InsertTest>().Where(it => it.id == 3.ObjToMoney()).ToList();
var r6 = db.Queryable <InsertTest>().Where(it => it.v1 == par2.Trim()).ToList();
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 => c.name == par2.ToLower()).ToList();
var convert4 = db.Queryable <Student>().Where(c => c.name == par2.ToUpper()).ToList();
var convert5 = db.Queryable <Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var c1 = db.Queryable <Student>().Where(c => c.name.Contains("a")).ToList();
var c2 = db.Queryable <Student>().Where(c => c.name.StartsWith("a")).ToList();
var c3 = db.Queryable <Student>().Where(c => c.name.EndsWith("a")).ToList();
var c4 = db.Queryable <Student>().Where(c => !string.IsNullOrEmpty(c.name)).ToList();
var c5 = db.Queryable <Student>().Where(c => c.name.Equals("小杰")).ToList();
var c6 = db.Queryable <Student>().Where(c => c.name.Length > 4).ToList();
var time = db.Queryable <InsertTest>().Where(c => c.d1 > DateTime.Now.AddDays(1)).ToList();
var time2 = db.Queryable <InsertTest>().Where(c => c.d1 > DateTime.Now.AddYears(1)).ToList();
var time3 = db.Queryable <InsertTest>().Where(c => c.d1 > DateTime.Now.AddMonths(1)).ToList();
}
}
/// <summary>
/// 获取表结构信息
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public List <PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
{
string sql = SqlSugarTool.GetTtableColumnsInfo(tableName);
return(db.SqlQuery <PubModel.DataTableMap>(sql));
}
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);
}
}
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);
}
/// <summary>
/// 动态获取IDataRecord里面的函数
/// </summary>
/// <param name="generator"></param>
/// <param name="type"></param>
/// <param name="isNullable"></param>
/// <param name="pro"></param>
/// <param name="dbTypeName"></param>
/// <param name="fieldName"></param>
private static void GeneratorCallMethod(ILGenerator generator, Type type, bool isNullable, PropertyInfo pro, string dbTypeName, string fieldName)
{
List <string> guidThrow = new List <string>()
{
"int32", "datetime", "decimal", "double", "byte", "string"
}; //数据库为GUID有错的实体类形
List <string> intThrow = new List <string>()
{
"datetime", "byte"
}; //数据库为int有错的实体类形
List <string> stringThrow = new List <string>()
{
"int32", "datetime", "decimal", "double", "byte", "guid"
}; //数据库为vachar有错的实体类形
List <string> decimalThrow = new List <string>()
{
"datetime", "byte", "guid"
};
List <string> doubleThrow = new List <string>()
{
"datetime", "byte", "guid"
};
List <string> dateThrow = new List <string>()
{
"int32", "decimal", "double", "byte", "guid"
};
List <string> shortThrow = new List <string>()
{
"datetime", "guid"
};
List <string> byteThrow = new List <string>()
{
"datetime", "guid"
};
MethodInfo method = null;
var typeName = SqlSugarTool.ChangeDBTypeToCSharpType(dbTypeName);
var objTypeName = type.Name.ToLower();
var isEnum = type.IsEnum;
if (isEnum)
{
typeName = "ENUMNAME";
}
else if (dbTypeName.Contains("hierarchyid") || typeName == "byte[]" || objTypeName == "object")
{
generator.Emit(OpCodes.Call, getValueMethod);
generator.Emit(OpCodes.Unbox_Any, pro.PropertyType);//找不到类型才执行拆箱（类型转换）
return;
}
if (isNullable)
{
switch (typeName)
{
case "int":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotInt = objTypeName != "int32";
if (isNotInt)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertInt32;
} break;
case "long":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotLong = objTypeName != "int64";
if (isNotLong)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvetInt64;
} break;
case "bool":
if (objTypeName != "bool" && objTypeName != "boolean")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertBoolean;
} break;
case "string":
CheckType(stringThrow, objTypeName, typeName, fieldName);
method = getString; break;
case "dateTime":
CheckType(dateThrow, objTypeName, typeName, fieldName);
if (objTypeName != "datetime")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertDateTime;
} break;
case "decimal":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
var isNotDecimal = objTypeName != "decimal";
if (isNotDecimal)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertDecimal;
} break;
case "double":
CheckType(doubleThrow, objTypeName, typeName, fieldName);
var isNotDouble = objTypeName != "double";
if (isNotDouble)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertDouble;
} break;
case "float":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
if (objTypeName != "float" && objTypeName != "single")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertFloat;
} break;
case "guid":
CheckType(guidThrow, objTypeName, typeName, fieldName);
if (objTypeName != "guid")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertGuid;
} break;
case "byte":
CheckType(byteThrow, objTypeName, typeName, fieldName);
if (objTypeName != "byte")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertByte;
} break;
case "ENUMNAME":
method = getConvertToEnum_Nullable.MakeGenericMethod(type); break;
case "short":
CheckType(shortThrow, objTypeName, typeName, fieldName);
var isNotShort = objTypeName != "int16" && objTypeName != "short";
if (isNotShort)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertInt16;
}
break;
default:
method = getOtherNull.MakeGenericMethod(type); break;
}
generator.Emit(OpCodes.Call, method);
}
else
{
switch (typeName)
{
case "int":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotInt = objTypeName != "int32";
if (isNotInt)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getInt32;
} break;
case "long":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotLong = objTypeName != "int64";
if (isNotLong)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getInt64;
} break;
case "bool":
if (objTypeName != "bool" && objTypeName != "boolean")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getBoolean;
} break;
case "string":
CheckType(stringThrow, objTypeName, typeName, fieldName);
method = getString; break;
case "dateTime":
CheckType(dateThrow, objTypeName, typeName, fieldName);
if (objTypeName != "datetime")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getDateTime;
} break;
case "decimal":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
var isNotDecimal = objTypeName != "decimal";
if (isNotDecimal)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getDecimal;
} break;
case "double":
CheckType(doubleThrow, objTypeName, typeName, fieldName);
var isNotDouble = objTypeName != "double";
if (isNotDouble)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getDouble;
} break;
case "float":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
if (objTypeName != "float" && objTypeName != "single")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getFloat;
} break;
case "guid":
CheckType(guidThrow, objTypeName, typeName, fieldName);
if (objTypeName != "guid")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getGuid;
} break;
case "byte":
CheckType(byteThrow, objTypeName, typeName, fieldName);
if (objTypeName != "byte")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getByte;
} break;
case "ENUMNAME":
method = getValueMethod; break;
case "short":
CheckType(shortThrow, objTypeName, typeName, fieldName);
var isNotShort = objTypeName != "int16" && objTypeName != "short";
if (isNotShort)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getInt16;
}
break;
default:
method = getOther.MakeGenericMethod(type);
break;;
}
generator.Emit(OpCodes.Call, method);
if (method == getValueMethod)
{
generator.Emit(OpCodes.Unbox_Any, pro.PropertyType);//找不到类型才执行拆箱（类型转换）
}
}
}
