//当前类已经继承了 DbContext增、删、查、改的方法
//这里面写的代码不会给覆盖,如果要重新生成请删除 RPT_Group_SettingManager.cs
#region 教学方法
/// <summary>
/// 如果DbContext中的增删查改方法满足不了你，你可以看下具体用法
/// </summary>
public void Study()
{
/*********查询*********/
var data1 = RPT_Group_SettingDb.GetById(1); //根据ID查询
var data2 = RPT_Group_SettingDb.GetList(); //查询所有
var data3 = RPT_Group_SettingDb.GetList(it => 1 == 1); //根据条件查询
//var data4 = RPT_Group_SettingDb.GetSingle(it => 1 == 1);//根据条件查询一条,如果超过一条会报错
var p = new PageModel()
{
PageIndex = 1, PageSize = 2
}; // 分页查询
var data5 = RPT_Group_SettingDb.GetPageList(it => 1 == 1, p);
Console.Write(p.PageCount); //返回总数
var data6 = RPT_Group_SettingDb.GetPageList(it => 1 == 1, p, it => SqlFunc.GetRandom(), OrderByType.Asc); // 分页查询加排序
Console.Write(p.PageCount); //返回总数
List <IConditionalModel> conModels = new List <IConditionalModel>(); //组装条件查询作为条件实现 分页查询加排序
conModels.Add(new ConditionalModel()
{
FieldName = typeof(RPT_Group_Setting).GetProperties()[0].Name, ConditionalType = ConditionalType.Equal, FieldValue = "1"
}); //id=1
var data7 = RPT_Group_SettingDb.GetPageList(conModels, p, it => SqlFunc.GetRandom(), OrderByType.Asc);
RPT_Group_SettingDb.AsQueryable().Where(x => 1 == 1).ToList();//支持了转换成queryable,我们可以用queryable实现复杂功能
//我要用事务
var result = Db.Ado.UseTran(() =>
{
//写事务代码
});
if (result.IsSuccess)
{
//事务成功
}
//多表查询地址 http://www.codeisbug.com/Doc/8/1124
/*********插入*********/
var insertData = new RPT_Group_Setting()
{
}; //测试参数
var insertArray = new RPT_Group_Setting[] { insertData };
RPT_Group_SettingDb.Insert(insertData); //插入
RPT_Group_SettingDb.InsertRange(insertArray); //批量插入
var id = RPT_Group_SettingDb.InsertReturnIdentity(insertData); //插入返回自增列
RPT_Group_SettingDb.AsInsertable(insertData).ExecuteCommand(); //我们可以转成 Insertable实现复杂插入
/*********更新*********/
var updateData = new RPT_Group_Setting()
{
}; //测试参数
var updateArray = new RPT_Group_Setting[] { updateData }; //测试参数
RPT_Group_SettingDb.Update(updateData); //根据实体更新
RPT_Group_SettingDb.UpdateRange(updateArray); //批量更新
//RPT_Group_SettingDb.Update(it => new RPT_Group_Setting() { Name = "a", CreateTime = DateTime.Now }, it => it.id==1);// 只更新Name列和CreateTime列，其它列不更新，条件id=1
RPT_Group_SettingDb.AsUpdateable(updateData).ExecuteCommand();
/*********删除*********/
var deldata = new RPT_Group_Setting()
{
}; //测试参数
RPT_Group_SettingDb.Delete(deldata); //根据实体删除
RPT_Group_SettingDb.DeleteById(1); //根据主键删除
RPT_Group_SettingDb.DeleteById(new int[] { 1, 2 }); //根据主键数组删除
RPT_Group_SettingDb.Delete(it => 1 == 2); //根据条件删除
RPT_Group_SettingDb.AsDeleteable().Where(it => 1 == 2).ExecuteCommand(); //转成Deleteable实现复杂的操作
}
/// <summary>
/// 获取单条数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public sys_role GetRoleById(string id)
{
return(Sqldb.Queryable <sys_role>().Where(s => s.id == SqlFunc.ToInt64(id)).First());
}
public static void Easy()
{
var db = GetInstance();
var getAll = db.Queryable <Student>().ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
// var getTodayList = db.Queryable<Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
}
/// <summary>
/// 查询库存剩余数量和销售数量
/// 可根据店铺查询，日期，品牌
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <StockSaleNum> > > GetStockNumByShopAsync(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <Page <StockSaleNum> >();
try
{
var query = Db.Queryable <ErpShopSku, ErpGoodsSku>((t1, t2) => new object[] { JoinType.Left, t1.SkuGuid == t2.Guid })
.Where((t1, t2) => t1.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.brand), (t1, t2) => t2.BrankGuid == searchParm.brand)
.OrderByIF(parm.orderType == 1, (t1, t2) => t1.Sale, OrderByType.Desc)
.Select((t1, t2) => new StockSaleNum()
{
Guid = t2.Guid,
Code = t2.Code,
Brand = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.BrankGuid).Select(g => g.Name),
Style = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.StyleGuid).Select(g => g.Name),
Stock = t1.Sale,
returnSum = SqlFunc.Subqueryable <ErpReturnGoods>().Where(g => g.GoodsGuid == t1.SkuGuid && g.ShopGuid == parm.guid).Sum(g => g.ReturnCount)
}).ToPage(parm.page, parm.limit);
//根据日期查询
var guidList = query.Items.Select(m => m.Guid).ToList();
if (parm.types == 0)
{
//所有
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid);
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts);
}
}
if (parm.types == 1)
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
//本日
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.DateIsSame(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), dayTime));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts);
}
}
if (parm.types == 2)
{
//本月
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(1).AddDays(-1);
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), d1, d2));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts);
}
}
if (parm.types == 3)
{
//自定义时间
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts);
}
}
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
///tab1
///tab1
///设置搜索数据
private List <CP_TABLE> schData()
{
UserProc.CheckTime = "";
var exp = Expressionable.Create <CP_TABLE>().And(it => it.status == "2");
if (checkMohu.Checked)
{
if (chkDanHao.chkSel)
{
exp.And((b) => b.ganghao.Contains(chkDanHao.txt.Text));
}
if (chkKehu.chkSel)
{
exp.And((b) => b.kehu.Contains(chkKehu.cobodgv.Text));
}
if (chkPinmin.chkSel)
{
exp.And((b) => b.pinming.Contains(chkPinmin.cobodgv.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.pishu.Contains(chkPS.txt.Text));
}
if (chkCW.chkSel)
{
exp.And((b) => b.cangwei.Contains(chkCW.txt.Text));
}
if (chkRKDH.chkSel)
{
exp.And((b) => b.rukudanhao.Contains(chkRKDH.txt.Text));
}
if (chkDDH.chkSel)
{
exp.And((b) => b.dingdanhao.Contains(chkDDH.txt.Text));
}
if (chkYWY.chkSel)
{
exp.And((b) => b.yewuyuan.Contains(chkYWY.cobodgv.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.zhongliang.Contains(chkZL.txt.Text));
}
if (chkJCY.chkSel)
{
exp.And((b) => b.jincangyuan.Contains(chkJCY.cobodgv.Text));
}
}
else
{
if (chkDanHao.chkSel)
{
exp.And((b) => b.ganghao == (chkDanHao.txt.Text));
}
if (chkKehu.chkSel)
{
exp.And((b) => b.kehu == (chkKehu.cobodgv.Text));
}
if (chkPinmin.chkSel)
{
exp.And((b) => b.pinming == (chkPinmin.cobodgv.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.pishu == (chkPS.txt.Text));
}
if (chkCW.chkSel)
{
exp.And((b) => b.cangwei == (chkCW.txt.Text));
}
if (chkRKDH.chkSel)
{
exp.And((b) => b.rukudanhao == (chkRKDH.txt.Text));
}
if (chkDDH.chkSel)
{
exp.And((b) => b.dingdanhao == (chkDDH.txt.Text));
}
if (chkYWY.chkSel)
{
exp.And((b) => b.yewuyuan == (chkYWY.cobodgv.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.zhongliang == (chkZL.txt.Text));
}
if (chkJCY.chkSel)
{
exp.And((b) => b.jincangyuan == (chkJCY.cobodgv.Text));
}
}
DateTime dateTime;
string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (this.chkDtime1.chkFsel)
{
dateTime = chkDtime1.dtimeF.Value;
str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (this.chkDtime1.chkTsel)
{
dateTime = chkDtime1.dtimeT.Value;
str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
exp.And((b) => SqlFunc.Between(b.riqi, str1, str2));
UserProc.CheckTime = str1 + " - " + str2;
}
var getAll1 = db.Queryable <CP_TABLE>()
.Where(exp.ToExpression())
.OrderBy((b) => b.riqi, OrderByType.Desc)
.ToList();
return(getAll1);
}
/// <summary>
/// table1
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
///
private List <BaseIList> schData()
{
UserProc.CheckTime = "";
var exp = Expressionable.Create <BaseIList>().And(it => it.leibie == benleibie);
if (checkMohu.Checked)
{
if (chkGH.chkSel)
{
exp.And((b) => b.item0.Contains(chkGH.txt.Text));
}
if (chkKehu.chkSel)
{
exp.And((b) => b.kehu.Contains(chkKehu.cobodgv.Text));
}
if (chkPinmin.chkSel)
{
exp.And((b) => b.pingmin.Contains(chkPinmin.cobodgv.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.zongliang.Contains(chkZL.txt.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.pishu.Contains(chkPS.txt.Text));
}
if (chkSH.chkSel)
{
exp.And((b) => b.sehao.Contains(chkSH.cobodgv.Text));
}
if (chkSM.chkSel)
{
exp.And((b) => b.seming.Contains(chkSM.cobodgv.Text));
}
if (chkDDH.chkSel)
{
exp.And((b) => b.dingdanhao.Contains(chkDDH.txt.Text));
}
if (chkMF.chkSel)
{
exp.And((b) => b.menfu.Contains(chkMF.txt.Text));
}
if (chkKZ.chkSel)
{
exp.And((b) => b.kezong.Contains(chkKZ.txt.Text));
}
}
else
{
if (chkGH.chkSel)
{
exp.And((b) => b.item0 == (chkGH.txt.Text));
}
if (chkKehu.chkSel)
{
exp.And((b) => b.kehu == (chkKehu.cobodgv.Text));
}
if (chkPinmin.chkSel)
{
exp.And((b) => b.pingmin == (chkPinmin.cobodgv.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.zongliang == (chkZL.txt.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.pishu == (chkPS.txt.Text));
}
if (chkSH.chkSel)
{
exp.And((b) => b.sehao == (chkSH.cobodgv.Text));
}
if (chkSM.chkSel)
{
exp.And((b) => b.seming == (chkSM.cobodgv.Text));
}
if (chkDDH.chkSel)
{
exp.And((b) => b.dingdanhao == (chkDDH.txt.Text));
}
if (chkMF.chkSel)
{
exp.And((b) => b.menfu == (chkMF.txt.Text));
}
if (chkKZ.chkSel)
{
exp.And((b) => b.kezong == (chkKZ.txt.Text));
}
}
DateTime dateTime;
string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (this.chkDtime1.chkFsel)
{
dateTime = chkDtime1.dtimeF.Value;
str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (this.chkDtime1.chkTsel)
{
dateTime = chkDtime1.dtimeT.Value;
str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
exp.And((b) => SqlFunc.Between(b.riqi, str1, str2));
UserProc.CheckTime = str1 + " - " + str2;
}
var getAll1 = db.Queryable <BaseIList>()
.Where(exp.ToExpression())
.OrderBy((b) => b.SN, OrderByType.Desc)
.ToList();
return(getAll1);
}
public JsonResult LoadData(string account, string sn, int?countryId, int?status, GridPager pager)
{
List <MerchantProfile> profileList;
List <MerchantProfileAccountViewModel> merchantPos;
string countryName;
if (string.IsNullOrEmpty(sn))
{
if (!countryId.HasValue)
{
return(Json(new List <MerchantProfile>().ToGridJson(pager)));
}
profileList = new MerchantProfileBLL().GetMerchantKYCProfilePageListL1(account, countryId.Value, status, ref pager);
if (profileList == null || profileList.Count <= 0)
{
return(Json(new List <MerchantProfile>().ToGridJson(pager)));
}
var merchantIds = profileList.Select(t => t.MerchantId).ToArray();
countryName = FoundationDB.CountryDb.GetById(countryId)?.Name;
merchantPos = FiiiPayDB.DB.Queryable <MerchantAccounts, POSs>((u, v) => new object[] { JoinType.Left, u.POSId == v.Id })
.Where((u, v) => SqlFunc.ContainsArray <Guid>(merchantIds, u.Id))
.Select((u, v) => new MerchantProfileAccountViewModel {
MerchantId = u.Id, Cellphone = u.Cellphone, MerchantName = u.MerchantName, SN = v.Sn, Username = u.Username
}).ToList();
}
else
{
var pos = FiiiPayDB.POSDb.GetSingle(t => t.Sn == sn);
if (pos == null)
{
return(Json(new List <MerchantProfile>().ToGridJson(pager)));
}
var merchantAccount = FiiiPayDB.MerchantAccountDb.GetSingle(t => t.POSId == pos.Id);
if (merchantAccount == null)
{
return(Json(new List <MerchantProfile>().ToGridJson(pager)));
}
var profile = new MerchantProfileBLL().GetMerchantProfile(merchantAccount.Id);
if (profile == null)
{
return(Json(new List <MerchantProfile>().ToGridJson(pager)));
}
profileList = new List <MerchantProfile> {
profile
};
profileList = profileList.Where(t =>
(account == null ? true : t.Cellphone.Contains(account)) &&
(countryId.HasValue ? t.Country == countryId.Value : true) &&
(status.HasValue ? (byte)t.L1VerifyStatus == status.Value : true)
).ToList();
countryName = FoundationDB.CountryDb.GetById(merchantAccount.CountryId)?.Name;
merchantPos = new List <MerchantProfileAccountViewModel>
{
new MerchantProfileAccountViewModel
{
MerchantId = merchantAccount.Id,
MerchantName = merchantAccount.MerchantName,
Cellphone = merchantAccount.Cellphone,
Username = merchantAccount.Username,
SN = pos.Sn
}
};
}
var data = profileList.ToGridJson(pager, r =>
new
{
id = r.MerchantId,
cell = new
{
MerchantId = r.MerchantId,
Username = merchantPos.Where(t => t.MerchantId == r.MerchantId).Select(t => t.Username).FirstOrDefault(),
Cellphone = r.Cellphone,
SN = merchantPos.Where(t => t.MerchantId == r.MerchantId).Select(t => t.SN).FirstOrDefault(),
MerchantName = merchantPos.Where(t => t.MerchantId == r.MerchantId).Select(t => t.MerchantName).FirstOrDefault(),
FirstName = r.FirstName,
LastName = r.LastName,
CountryName = countryName,
L1VerifyStatus = r.L1VerifyStatus.ToString(),
L1SubmissionDate = r.L1SubmissionDate.HasValue ? r.L1SubmissionDate.Value.ToLocalTime().ToString("yyyy-MM-dd HH:mm") : "",
Remark = r.L1Remark,
Options = (int)r.L1VerifyStatus
}
});
return(Json(data));
}
/// <summary>
/// 前台右侧内容
/// </summary>
/// <returns></returns>
public async Task <ApiResult <PageRightDto> > GetRgithData()
{
var res = new ApiResult <PageRightDto>()
{
statusCode = (int)ApiEnum.Error
};
try
{
var m = new PageRightDto()
{
};
//问题总数
m.QuestionCount = await Db.Queryable <Bbs_Questions>().CountAsync();
//会员总数
m.UserCount = await Db.Queryable <Member>().CountAsync();
//常用标签
m.TagList = await Db.Queryable <Bbs_Tags>()
.Where(s => !s.IsDel)
.Select(s => new TagsCount()
{
TagName = s.TagName,
EnTagName = s.EnTagName,
TagCount = SqlFunc.Subqueryable <Bbs_Questions>().Where(g => g.Tags.Contains(s.TagName)).Count()
}).OrderBy(g => g.TagCount, OrderByType.Desc)
.Take(8).ToListAsync();
//热门话题
m.RedQuestionList = await Db.Queryable <Bbs_Questions, Member>((q, u) => new JoinQueryInfos(
JoinType.Inner, q.UserGuid == u.Guid
)).Where((q, u) => q.IsRed)
.OrderBy((q, u) => q.AnswerSum, OrderByType.Desc)
.Select((q, u) => new Bbs_Questions()
{
Guid = q.Guid,
EnTitle = q.EnTitle,
HeadPic = u.HeadPic,
Title = q.Title
})
.Take(6).ToListAsync();
//推荐专家
m.ExpertList = await Db.Queryable <Member>()
.Where(u => !u.IsDel && u.Grade == "1fc19833-539a-40cf-a000-1f9037b8dae4")
.Select(u => new MemberQuestion()
{
Guid = u.Guid,
NickName = u.NickName,
HeadPic = u.HeadPic,
AnswerCount = SqlFunc.Subqueryable <Bbs_Answer>().Where(g => g.UserGuid == u.Guid).Count(),
AdoptCount = SqlFunc.Subqueryable <Bbs_Answer>().Where(g => g.UserGuid == u.Guid && g.IsAdopt).Count()
})
.OrderBy(g => g.AnswerCount, OrderByType.Desc)
.Take(6).ToListAsync();
res.data = m;
res.statusCode = (int)ApiEnum.Status;
}
catch (Exception ex)
{
res.message = ex.Message;
}
return(res);
}
public static List <UserInComesHis> GetInComesHisBySome(string useraddress, DateTime?begin, DateTime?end, int pagesize, int pageindex, ref int totalCount)
{
if (begin == null)
{
begin = DateTime.MinValue;
}
if (end == null)
{
end = DateTime.MaxValue;
}
totalCount = 0;
return(SugarBase.DBAutoClose.Queryable <UserInComesHis>().Where(x => (x.UserAddress == useraddress || string.IsNullOrWhiteSpace(useraddress)) && SqlFunc.Between(x.SetTime, begin, end)).OrderBy(x => x.SetTime, OrderByType.Desc).ToPageList(pageindex, pagesize, ref totalCount));
}
public static decimal GetInComesSum(string useraddress, DateTime?begin, DateTime?end)
{
if (begin == null)
{
begin = DateTime.MinValue;
}
if (end == null)
{
end = DateTime.MaxValue;
}
return(SugarBase.DBAutoClose.Queryable <UserInComesHis>().Where(x => (x.UserAddress == useraddress || string.IsNullOrWhiteSpace(useraddress)) && SqlFunc.Between(x.SetTime, begin, end)).Sum(x => x.GetCoins));
}
private static void Subquery()
{
Console.WriteLine("");
Console.WriteLine("#### Subquery Start ####");
var db = GetInstance();
var list = db.Queryable <Order>().Take(10).Select(it => new
{
customName = SqlFunc.Subqueryable <Custom>().Where("it.CustomId=id").Select(s => s.Name),
customName2 = SqlFunc.Subqueryable <Custom>().Where("it.CustomId = id").Where(s => true).Select(s => s.Name)
}).ToList();
var list1 = db.Queryable <Order>().Select(it => new
{
id = SqlFunc.Subqueryable <Custom>().Where(s => s.Id == 1).Sum(s => s.Id) * 1
}).ToList();
var list2 = db.Queryable <Order>().Where(it =>
SqlFunc.Subqueryable <OrderItem>()
.LeftJoin <OrderItem>((i, z) => i.ItemId == z.ItemId)
.InnerJoin <OrderItem>((i, z, y) => i.ItemId == z.ItemId)
.InnerJoin <OrderItem>((i, z, y, h) => i.ItemId == z.ItemId)
.InnerJoin <OrderItem>((i, z, y, h, n) => i.ItemId == z.ItemId)
.Where((i, z) => i.ItemId == z.ItemId)
.Any()
).ToList();
var list3 = db.Queryable <Order>().Select(it => new
{
customName = SqlFunc.Subqueryable <Custom>().Where(s => s.Id == it.CustomId).GroupBy(s => s.Name).Having(s => SqlFunc.AggregateCount(s.Id) > 0).Select(s => s.Name)
}).ToList();
var exp = Expressionable.Create <Custom>().And(s => s.Id == 1).ToExpression();
var list4 = db.Queryable <Order>().Select(it => new
{
customName = SqlFunc.Subqueryable <Custom>().Where(exp).Where(exp).GroupBy(s => s.Name).Having(s => SqlFunc.AggregateCount(s.Id) > 0).Select(s => s.Name)
}).ToList();
var list5 = db.Queryable <Order>().Where(it =>
SqlFunc.Subqueryable <OrderItem>()
.LeftJoin <OrderItem>((i, y) => i.ItemId == y.ItemId)
.InnerJoin <OrderItem>((i, z) => i.ItemId == z.ItemId)
.Where(i => i.ItemId == 1)
.Any()
).ToList();
Console.WriteLine("#### Subquery End ####");
}
private static void EasyExamples()
{
Console.WriteLine("");
Console.WriteLine("#### Examples Start ####");
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Order>().ToList();
var getTake = db.Queryable <Order>().Take(2).ToList();
var getSkip = db.Queryable <Order>().Skip(2).ToList();
var getOrderBy = db.Queryable <Order>().OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy2 = db.Queryable <Order>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy3 = db.Queryable <Order>().OrderBy(it => new { it.Name, it.Id }).ToList();
var getRandom = db.Queryable <Order>().OrderBy(it => SqlFunc.GetRandom()).First();
var getByPrimaryKey = db.Queryable <Order>().InSingle(2);
var getSingleOrDefault = db.Queryable <Order>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Order>().First();
var getByWhere = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Order>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Order>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
// var getByFuns2 = db.Queryable<Order>().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList();
var getDicionary = db.Queryable <Order>().ToDictionary(it => it.Id, it => it.Name);
var getDicionaryList = db.Queryable <Order>().ToDictionaryList();
var getTest = db.Queryable <Order>().Where(it => string.IsNullOrWhiteSpace(it.Name)).ToList();
// var test01 = db.Queryable<Order>().PartitionBy(it => it.Id).ToList();
var q1 = db.Queryable <Order>().Take(1);
var q2 = db.Queryable <Order>().Take(2);
//var test02 = db.Union(q1, q2).ToList();
var test03 = db.Queryable <Order>().Take(1).ToList();
var dp = DateTime.Now;
var test05 = db.Queryable <Order>().Where(it => it.CreateTime.Month == dp.Month).ToList();
var test06 = db.Queryable <Order>()
.ToPivotTable(it => it.Id, it => it.Name, it => it.Sum(x => x.Price));
var test07 = db.Queryable <Order>()
.ToPivotList(it => it.Id, it => it.Name, it => it.Sum(x => x.Price));
var test08 = db.Queryable <Order>()
.ToPivotJson(it => it.Id, it => it.Name, it => it.Sum(x => x.Price));
//var test09 = db.Queryable<Order>().PartitionBy(it=>it.Id).ToPageListAsync(1,2,0);
//test09.Wait();
int c = 0;
var test10 = db.Queryable <Order>().OrderBy(it => it.Id).ToPageList(3, 5, ref c);
var test11 = db.Queryable <Order>().GroupBy(it => new { it.CreateTime.Year }).Select(it => it.CreateTime.Year).ToList();
// var test12 = db.Queryable<Order>().GroupBy(it => it.CreateTime.Date ).Select(it => it.CreateTime.Date).ToList();
//var test13 = db.Queryable<Order>().GroupBy(it => new { it.CreateTime.Date ,it.CreateTime.Year,it.CreateTime.Minute })
// .Select(it => new { it.CreateTime.Date, it.CreateTime.Year, it.CreateTime.Minute }).ToList();
//var test14 = db.Queryable<Order>()
// .GroupBy(it => it.CreateTime.Year )
// .GroupBy(it => it.CreateTime.Second)
// .GroupBy(it => it.CreateTime.Date)
// .Select(it => new {
// it.CreateTime.Year,
// it.CreateTime.Second,
// it.CreateTime.Date
// }).ToList();
var test15 = db.Queryable <Order, Order>((o, i) => new JoinQueryInfos(
JoinType.Left, o.Name == SqlFunc.ToString(SqlFunc.MergeString(",", i.Name, ","))
))
.Select <ViewOrder>().ToList();
// var test16 = db.Queryable<Order>().Select(it => SqlFunc.SqlServer_DateDiff("day", DateTime.Now.AddDays(-1), DateTime.Now)).ToList();
//var test17 =
// db.Queryable<Order>()
// .Select<Order>()
// .MergeTable()
// .Select(it => new ViewOrder()
// {
// Name = SqlFunc.Subqueryable<Order>().Select(s => s.Name)
// }).ToList(); ;
//var test18 = db.UnionAll(
// db.Queryable<Order>() ,
// db.Queryable<Order>()
// )
// .Select(it=>new ViewOrder(){
// Name=SqlFunc.Subqueryable<Order>().Select(s=>s.Name)
// }).ToList();
var test19 = db.Queryable <Order>().Select <ViewOrder>().ToList();
var test20 = db.Queryable <Order>().LeftJoin <Custom>((o, cs) => o.Id == cs.Id)
.ToDictionary(it => it.Id, it => it.Name);
//var test21 = db.Queryable<Order>().Where(it=>it.Id.ToString()==1.ToString()).Select(it => it.CreateTime.ToString("24")).First();
Console.WriteLine("#### Examples End ####");
}
/// <summary>
/// table1
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
///
private List <BaseIList> schData()
{
UserProc.CheckTime = "";
var exp = Expressionable.Create <BaseIList>().And(it => it.leibie == "染料入库");
if (checkMohu.Checked)
{
if (chkRKDH.chkSel)
{
exp.And((b) => b.dingdanhao.Contains(chkRKDH.txt.Text));
}
if (chkSL.chkSel)
{
exp.And((b) => b.seming.Contains(chkSL.txt.Text));
}
if (chkDJ.chkSel)
{
exp.And((b) => b.item0.Contains(chkDJ.txt.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.pishu.Contains(chkZL.txt.Text));
}
if (chkRHLB.chkSel)
{
exp.And((b) => b.item1.Contains(chkRHLB.cobodgv.Text));
}
if (chkGYS.chkSel)
{
exp.And((b) => b.item2.Contains(chkGYS.txt.Text));
}
if (chkRHMC.chkSel)
{
exp.And((b) => b.item3.Contains(chkRHMC.cobodgv.Text));
}
}
else
{
if (chkRKDH.chkSel)
{
exp.And((b) => b.dingdanhao == (chkRKDH.txt.Text));
}
if (chkSL.chkSel)
{
exp.And((b) => b.seming == (chkSL.txt.Text));
}
if (chkDJ.chkSel)
{
exp.And((b) => b.item0 == (chkDJ.txt.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.pishu == (chkZL.txt.Text));
}
if (chkRHLB.chkSel)
{
exp.And((b) => b.item1 == (chkRHLB.cobodgv.Text));
}
if (chkGYS.chkSel)
{
exp.And((b) => b.item2 == (chkGYS.txt.Text));
}
if (chkRHMC.chkSel)
{
exp.And((b) => b.item3 == (chkRHMC.cobodgv.Text));
}
}
DateTime dateTime;
string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (this.chkDtime1.chkFsel)
{
dateTime = chkDtime1.dtimeF.Value;
str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (this.chkDtime1.chkTsel)
{
dateTime = chkDtime1.dtimeT.Value;
str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
exp.And((b) => SqlFunc.Between(b.riqi, str1, str2));
UserProc.CheckTime = str1 + " - " + str2;
}
var getAll1 = db.Queryable <BaseIList>()
.Where(exp.ToExpression())
.OrderBy((b) => b.riqi, OrderByType.Desc)
.ToList();
return(getAll1);
}
/// <summary>
/// 返回本月未登记的宿舍数量
/// </summary>
/// <param name="dorm_id">园区ID：默认全部</param>
/// <returns></returns>
public short CountNoRecord(int dorm_id = 0)
{
var rooms = Db.Queryable <T_Used>().
Where(u => u.Used_model_state && SqlFunc.Between(SqlFunc.Substring(u.Used_post_date, 0, 7),
SqlFunc.Substring(DateTime.Now, 0, 7), SqlFunc.Substring(DateTime.Now, 0, 7))).Select(u => u.Used_room_id).ToList(); // 已登记的宿舍ID
var sql = Db.Queryable <T_Room>().Where(r => r.Room_model_state && r.Number > 0 && r.Room_is_active && !rooms.Contains(r.Room_id));
if (dorm_id != 0)
{
sql = sql.Where(r => r.Room_id == dorm_id);
}
return((short)sql.OrderBy(r => r.Room_vid).Count());
}
public void AddAccountInfoHistory(string name, string bankOfDeposit, string account)
{
name = string.IsNullOrWhiteSpace(name) ? string.Empty : name;
bankOfDeposit = string.IsNullOrWhiteSpace(bankOfDeposit) ? string.Empty : bankOfDeposit;
account = string.IsNullOrWhiteSpace(account) ? string.Empty : account;
if (string.IsNullOrWhiteSpace(name) || string.IsNullOrWhiteSpace(bankOfDeposit) || string.IsNullOrWhiteSpace(account))
{
return;
}
var query = BusDb.Queryable <DH_AccountInfoHistory>()
.Where(m => SqlFunc.Equals(m.Name, name) && SqlFunc.Equals(m.BankOfDeposit, bankOfDeposit) && SqlFunc.Equals(m.Account, account)).ToList();
if (query.Count > 0)
{
return;
}
DH_AccountInfoHistory info = new DH_AccountInfoHistory()
{
Id = Guid.NewGuid(),
Name = name,
BankOfDeposit = bankOfDeposit,
Account = account
};
BusDb.Insertable(info).ExecuteCommand();
}
/// <summary>
/// 加盟商库存盘点报表
/// </summary>
/// <param name="parm"></param>
/// <param name="searchParm"></param>
/// <returns></returns>
public Task <ApiResult <Page <ShopInventory> > > GetShopInventoryAsync(PageParm parm, SearchSaleOrderGoods searchParm)
{
var res = new ApiResult <Page <ShopInventory> >();
try
{
//默认只查询当月的营业额
DateTime now = DateTime.Now;
DateTime beginTime = new DateTime(now.Year, now.Month, 1), endTime = beginTime.AddMonths(1);
if (!string.IsNullOrEmpty(parm.time))
{
var timeRes = Utils.SplitString(parm.time, '-');
beginTime = Convert.ToDateTime(timeRes[0].Trim());
endTime = Convert.ToDateTime(timeRes[1].Trim());
}
if (string.IsNullOrEmpty(parm.field) || string.IsNullOrEmpty(parm.order))
{
parm.field = "stock";
parm.order = "desc";
}
var query = Db.Queryable <ErpShopSku, ErpGoodsSku, ErpShops>((t1, t2, t3) => new object[] { JoinType.Left, t1.SkuGuid == t2.Guid, JoinType.Left, t1.ShopGuid == t3.Guid })
.WhereIF(!string.IsNullOrEmpty(parm.time), (t1, t2, t3) => SqlFunc.Between(t1.AddDate, beginTime, endTime))
.WhereIF(!string.IsNullOrEmpty(searchParm.shopGuid), (t1, t2, t3) => t1.ShopGuid == searchParm.shopGuid)
.WhereIF(!string.IsNullOrEmpty(searchParm.brank), (t1, t2, t3) => t2.BrankGuid == searchParm.brank)
.WhereIF(!string.IsNullOrEmpty(searchParm.year), (t1, t2, t3) => t2.YearGuid == searchParm.year)
.WhereIF(!string.IsNullOrEmpty(searchParm.size), (t1, t2, t3) => t2.SizeGuid == searchParm.size)
.WhereIF(!string.IsNullOrEmpty(searchParm.style), (t1, t2, t3) => t2.StyleGuid == searchParm.style)
.WhereIF(!string.IsNullOrEmpty(searchParm.season), (t1, t2, t3) => t2.SeasonGuid == searchParm.season)
.WhereIF(!string.IsNullOrEmpty(parm.key), (t1, t2, t3) => t2.Code.Contains(parm.key))
.OrderByIF(parm.orderType == 1, (t1, t2, t3) => t1.Sale, OrderByType.Desc)
.OrderByIF(parm.orderType == 2, (t1, t2, t3) => t1.Stock, OrderByType.Desc)
.Select((t1, t2, t3) => new ShopInventory()
{
Code = t2.Code,
ShopName = t3.ShopName,
//CollectSum= SqlFunc.Subqueryable<ErpInOutLog>().Where(g => g.Types == 2).Sum(g => g.GoodsSum),
Brand = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.BrankGuid).Select(g => g.Name),
Style = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.StyleGuid).Select(g => g.Name),
SeasonName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.SeasonGuid).Select(g => g.Name),
Size = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.SizeGuid).Select(g => g.Name),
Stock = t1.Stock,
Sale = t1.Sale,
ReturnSum = SqlFunc.Subqueryable <ErpReturnGoods>().Where(g => g.GoodsGuid == t1.SkuGuid && g.ShopGuid == parm.guid && g.Status == 1).Sum(g => g.ReturnCount)
})
.OrderBy(parm.field + " " + parm.order)
.ToPage(parm.page, parm.limit);
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
public List <DH_AccountInfoHistory> SearchAccountInfo(string key)
{
List <DH_AccountInfoHistory> result = new List <DH_AccountInfoHistory>();
var query = BusDb.Queryable <DH_AccountInfoHistory>()
.WhereIF(!string.IsNullOrEmpty(key), m => SqlFunc.Contains(m.Name, key) || SqlFunc.Contains(m.BankOfDeposit, key) || SqlFunc.Contains(m.Account, key));
if (string.IsNullOrEmpty(key))
{
result = query.Take(10).ToList();
}
else
{
result = query.ToList();
}
return(result);
}
private List <T_PFmain> schData()
{
var exp = Expressionable.Create <T_PFmain>().And(it => it.leibie == XYD);
List <T_PFmain> getAll = null;
if (schMohu.Checked)
{
if (schKH.chkSel)
{
exp.And(it => it.kehu.Contains(schKH.cobodgv.Text));
}
if (schSZ.chkSel)
{
exp.And(it => it.shazhong.Contains(schSZ.cobodgv.Text));
}
if (schSH.chkSel)
{
exp.And(it => it.sehao.Contains(schSH.cobodgv.Text));
}
if (schYS.chkSel)
{
exp.And(it => it.yanse.Contains(schYS.cobodgv.Text));
}
if (schLD.chkSel)
{
exp.And(it => it.danhao.Contains(schLD.txt.Text));
}
if (schDY.chkSel)
{
exp.And(it => it.dayang.Contains(schDY.txt.Text));
}
if (schDDH.chkSel)
{
exp.And(it => it.dingdan.Contains(schDDH.txt.Text));
}
}
else
{
if (schKH.chkSel)
{
exp.And(it => it.kehu == (schKH.cobodgv.Text));
}
if (schSZ.chkSel)
{
exp.And(it => it.shazhong == (schSZ.cobodgv.Text));
}
if (schSH.chkSel)
{
exp.And(it => it.sehao == (schSH.cobodgv.Text));
}
if (schYS.chkSel)
{
exp.And(it => it.yanse == (schYS.cobodgv.Text));
}
if (schLD.chkSel)
{
exp.And(it => it.danhao == (schLD.txt.Text));
}
if (schDY.chkSel)
{
exp.And(it => it.dayang == (schDY.txt.Text));
}
if (schDDH.chkSel)
{
exp.And(it => it.dingdan == (schDDH.txt.Text));
}
}
DateTime dateTime;
string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (this.schRQsave.chkFsel)
{
dateTime = schRQsave.dtimeF.Value;
str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (this.schRQsave.chkTsel)
{
dateTime = schRQsave.dtimeT.Value;
str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
exp.And(it => SqlFunc.Between(it.riqiSave, str1, str2));
}
getAll = db.Queryable <T_PFmain>().Where(exp.ToExpression()).OrderBy(it => it.SN, OrderByType.Desc).ToList();
return(getAll);
}
public List <OA_Client> SearchClientInfo(string nameOrCode)
{
List <OA_Client> result = new List <OA_Client>();
var query = BusDb.Queryable <OA_Client>()
.WhereIF(!string.IsNullOrEmpty(nameOrCode), m => SqlFunc.Contains(m.ClientCode, nameOrCode) || SqlFunc.Contains(m.ClientName, nameOrCode));
if (string.IsNullOrEmpty(nameOrCode))
{
result = query.Take(10).ToList();
}
else
{
result = query.ToList();
}
return(result);
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <Page <ErpShopUser> > > GetPagesAsync(PageParm parm)
{
var res = new ApiResult <Page <ErpShopUser> >();
try
{
if (string.IsNullOrEmpty(parm.field) || string.IsNullOrEmpty(parm.order))
{
parm.field = "points";
parm.order = "desc";
}
var dt = new DateTime().Date;
var query = Db.Queryable <ErpShopUser>()
.WhereIF(parm.guid != "all", m => m.ShopGuid == parm.guid)
.WhereIF(parm.types == 1, m => m.RegDate.Year == SqlFunc.ToInt32(dt.Year) && m.RegDate.Month == SqlFunc.ToInt32(dt.Month) && m.RegDate.Day == SqlFunc.ToInt32(dt.Day))
.WhereIF(!string.IsNullOrEmpty(parm.key),
m => m.Mobile == parm.key ||
m.UserName == parm.key)
.OrderBy(parm.field + " " + parm.order)
.ToPageAsync(parm.page, parm.limit);
res.success = true;
res.message = "获取成功！";
res.data = await query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(await Task.Run(() => res));
}
public static void Init()
{
var db = NewUnitTest.Db;
db.CodeFirst.InitTables <Export>();
db.CodeFirst.InitTables <LoadCon>();
db.CodeFirst.InitTables <ExToCon>();
var LclId = "FCL";
var conno = "conno";
var withSameCarriCarNo = db.Queryable <Export>()
.Where(export => SqlFunc.IsNull(export.IeId, "E") == "E")
.Where(export => SqlFunc.Subqueryable <LoadCon>().Where(loadconn =>
SqlFunc.Subqueryable <ExToCon>().Where(extocon =>
extocon.ExId == export.Id && extocon.LcId == loadconn.Id).Any())
.WhereIF(LclId == "FCL",
loadconn => SqlFunc.IsNull(loadconn.ConNo, "") == conno)
.WhereIF(LclId != "FCL",
loadconn => SqlFunc.IsNull(loadconn.ConNo, "") == conno && export.LclId == "FCL")
.Any())
.ToList();
}
public CommonRtnEntity GetInfoByLessThanInviteCountAndUpdateDoing(string clientName, string projectName, int inviteCount, string status = "0")
{
lock (GetInfoByLessThanInviteCountAndUpdateDoingLockStr)
{
IMobileInfoServices advertisementServices = new MobileInfoServices();
List <MobileInfo> list = new List <MobileInfo>();
list = advertisementServices.Query(d => d.ProjectName == projectName && d.ClientName == clientName && d.Status == status && !SqlFunc.IsNullOrEmpty(d.InviteCode)).Take(5).ToList();
list = list.Where(x => x.InviteCount < inviteCount).ToList();
MobileInfo info = null;
if (list != null && list.Count > 0)
{
info = list[(new Random()).Next(0, list.Count - 1)];
info.Status = "占用";
if (info.CreateTime < DateTime.Now.AddYears(-5))
{
info.CreateTime = DateTime.Now;
}
info.UpdateTime = DateTime.Now;
advertisementServices.Update(info);
}
string errorMsg = "暂未获取到！";
CommonRtnEntity rtnInfo = new CommonRtnEntity()
{
Success = info != null,
Data = info,
Message = info != null ? "获取成功！" : errorMsg
};
return(rtnInfo);
}
}
public static void Funs()
{
var db = GetInstance();
var t1 = db.Queryable <Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList();
var t2 = db.Queryable <Student>().Where(it => SqlFunc.IsNull(it.Name, "nullvalue") == "nullvalue").ToList();
//SELECT [Id],[SchoolId],[Name],[CreateTime] FROM [Student] WHERE ((LOWER([Name])) = (LOWER(@MethodConst0)) )
/***More Functions***/
//SqlFunc.IsNullOrEmpty(object thisValue)
//SqlFunc.ToLower(object thisValue)
//SqlFunc.string ToUpper(object thisValue)
//SqlFunc.string Trim(object thisValue)
//SqlFunc.bool Contains(string thisValue, string parameterValue)
//SqlFunc.ContainsArray(object[] thisValue, string parameterValue)
//SqlFunc.StartsWith(object thisValue, string parameterValue)
//SqlFunc.EndsWith(object thisValue, string parameterValue)
//SqlFunc.Equals(object thisValue, object parameterValue)
//SqlFunc.DateIsSame(DateTime date1, DateTime date2)
//SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType)
//SqlFunc.DateAdd(DateTime date, int addValue, DateType millisecond)
//SqlFunc.DateAdd(DateTime date, int addValue)
//SqlFunc.DateValue(DateTime date, DateType dataType)
//SqlFunc.Between(object value, object start, object end)
//SqlFunc.ToInt32(object value)
//SqlFunc.ToInt64(object value)
//SqlFunc.ToDate(object value)
//SqlFunc.ToString(object value)
//SqlFunc.ToDecimal(object value)
//SqlFunc.ToGuid(object value)
//SqlFunc.ToDouble(object value)
//SqlFunc.ToBool(object value)
//SqlFunc.Substring(object value, int index, int length)
//SqlFunc.Replace(object value, string oldChar, string newChar)
//SqlFunc.Length(object value) { throw new NotImplementedException(); }
//SqlFunc.AggregateSum(object thisValue)
//SqlFunc.AggregateAvg<TResult>(TResult thisValue)
//SqlFunc.AggregateMin(object thisValue)
//SqlFunc.AggregateMax(object thisValue)
//SqlFunc.AggregateCount(object thisValue)
}
public void Q2()
{
using (var db = GetInstance())
{
//db.Database.IsEnableLogEvent = true;
db.Ado.LogEventStarting = (sql, pars) =>
{
Console.WriteLine(sql + " " + pars);
};
#region dr ot entity
db.IgnoreColumns.Add("TestId", "Student");
var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Name = it.Name, Student = it
}).ToList();
var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList();
var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList();
var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList();
var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Student = it, Name = it.Name
}).ToList();
#endregion
#region sql and parameters validate
var t1 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Inner, st.Id == sc.Id
}).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql();
base.Check("SELECT AVG(`st`.`ID`) AS `avgId` FROM `STudent` st Inner JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` ) GROUP BY `st`.`ID` HAVING (AVG(`st`.`ID`) = @Const0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Const0", 1)
}
,
t1.Key, t1.Value, " select t1 Error");
var t2 = db.Queryable <School, School>((st, st2) => new object[] {
JoinType.Left, st.Id == st2.Id
})
.Where(st => st.Id > 0)
.Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql();
base.Check("SELECT `st`.`Id` AS `stid` , `st2`.`Id` AS `scId` , `st`.`Id` AS `School.Id` , `st`.`Name` AS `School.Name` FROM `School` st Left JOIN `School` st2 ON ( `st`.`Id` = `st2`.`Id` ) WHERE ( `st`.`Id` > @Id0 ) "
, new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t2.Key, t2.Value, "select t2 Error");
var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToSql();
base.Check("SELECT `st`.`ID` AS `Id` FROM `STudent` st Left JOIN `School` sc ON ( `st`.`SchoolId` = `sc`.`Id` ) Left JOIN `School` sc2 ON ( `sc2`.`Id` = `sc`.`Id` ) WHERE ( `st`.`ID` > @Id0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t3.Key, t3.Value, "select t3 Error");
#endregion
}
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
}
private async Task <TWMOtherWhSendMainQueryModel> GetMainData(int iMainId)
{
try
{
var mainModel = await _db.Instance.Queryable <TWMOtherWhSendMainDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>(
(t, t0, t1, t2) => new object[]
{
JoinType.Left, t.OperatorId == t0.ID,
JoinType.Left, t.ReceiptId == t1.ID,
JoinType.Left, t.AuditId == t2.ID
})
.Select((t, t0, t1, t2) => new TWMOtherWhSendMainQueryModel
{
ID = t.ID,
WhSendType = t.WhSendType,
WhSendDate = t.WhSendDate,
WhSendOrder = t.WhSendOrder,
AuditStatus = t.AuditStatus,
OperatorId = t.OperatorId,
OperatorName = t0.AccountName,
ReceiptId = t.ReceiptId,
ReceiptName = t1.AccountName,
AuditId = t.AuditId,
AuditName = t2.AccountName,
AuditTime = t.AuditTime,
DeleteFlag = t.DeleteFlag,
Number = t.Number,
Amount = t.Amount
})
.Where(t => t.ID == iMainId)
.FirstAsync();
var detailModels = await _db.Instance.Queryable <TWMOtherWhSendDetailDbModel, TBMMaterialFileDbModel, TBMDictionaryTypeDbModel,
TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel>(
(t, t0, t1, t2, t3, t4) => new object[]
{
JoinType.Left, t.MaterialId == t0.ID,
JoinType.Left, t0.MaterialTypeId == t1.ID,
JoinType.Left, t0.ColorId == t2.ID,
JoinType.Left, t0.BaseUnitId == t3.ID,
JoinType.Left, t0.WarehouseUnitId == t4.ID
})
.Select((t, t0, t1, t2, t3, t4) => new TWMOtherWhSendDetailQueryModel
{
ID = t.ID,
MainId = t.MainId,
MaterialId = t.MaterialId,
MaterialName = t0.MaterialName,
MaterialCode = t0.MaterialCode,
WarehouseId = t.WarehouseId,
BatchNumber = t.BatchNumber,
ActualNumber = t.ActualNumber,
UnitPrice = t.UnitPrice,
Amount = t.Amount,
MaterialTypeId = t0.MaterialTypeId,
MaterialTypeName = t1.TypeName,
ColorId = t0.ColorId,
ColorName = t2.DicValue,
BaseUnitId = t0.BaseUnitId,
BaseUnitName = t3.DicValue,
WarehouseUnitId = t0.WarehouseUnitId,
WarehouseUnitName = SqlFunc.IsNullOrEmpty(t4.ID) ? t3.DicValue : t4.DicValue,
WarehouseRate = t0.WarehouseRate,
Spec = t0.Spec,
ValidityPeriod = t.ValidityPeriod,
Remark = t.Remark
})
.Where(t => t.MainId == iMainId)
.ToListAsync();
mainModel.ChildList = detailModels;
return(mainModel);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 根据权限获取操作按钮
/// </summary>
/// <param name="menuId"></param>
/// <returns></returns>
public List <sys_operate> GetOperateByRole(string menuId)
{
var list = new List <sys_operate>();
if (UserCookie.IsSuper)
{
list =
Sqldb.Queryable <sys_operate, sys_menu_ref_operate>((f, m) => f.id == m.operate_id && m.menu_id == SqlFunc.ToInt64(menuId))
.Select((f, m) => f)
.ToList();
}
else
{
list =
Sqldb.Queryable <sys_operate, sys_role_authorize>((f, r) => f.id == r.menu_id)
.Where((f, r) => r.role_id == UserCookie.SysRoleId && r.menu_pid == SqlFunc.ToInt64(menuId))
.Select((f, m) => f)
.ToList();
}
return(list);
}
/// <summary>
/// 查询营业额
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <ShopTurnover> > > GetShopTurnover(PageParm parm)
{
var res = new ApiResult <Page <ShopTurnover> >();
try
{
//默认只查询当月的营业额
DateTime now = DateTime.Now;
DateTime beginTime = new DateTime(now.Year, now.Month, 1), endTime = beginTime.AddMonths(1);
if (!string.IsNullOrEmpty(parm.time))
{
var timeRes = Utils.SplitString(parm.time, '-');
beginTime = Convert.ToDateTime(timeRes[0].Trim());
endTime = Convert.ToDateTime(timeRes[1].Trim());
}
if (string.IsNullOrEmpty(parm.field) || string.IsNullOrEmpty(parm.order))
{
parm.field = "Money";
parm.order = "desc";
}
var query = Db.Queryable <ErpShops>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.Guid == parm.guid)
.Select(m => new ShopTurnover()
{
ShopName = m.ShopName,
Principal = m.AdminName,
Mobile = m.Mobile,
OrderCount = SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(),
Money = SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Sum(g => g.RealMoney),
ReturnCount = SqlFunc.Subqueryable <ErpReturnOrder>().Where(g => g.Status == 1 && g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(),
BackCount = SqlFunc.Subqueryable <ErpBackGoods>().Where(g => g.Status == 1 && g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(),
BackMoney = SqlFunc.Subqueryable <ErpBackGoods>().Where(g => g.Status == 1 && g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Sum(g => g.BackMoney),
})
.OrderBy(parm.field + " " + parm.order)
.ToPage(parm.page, parm.limit);
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
/*
** Find an SqlFunc structure with the given name. Or create a new
** one if an existing one cannot be found. Return a pointer to the
** structure.
*/
static SqlFunc findSqlFunc( SqliteDb pDb, string zName )
{
SqlFunc p, pNew;
int i;
pNew = new SqlFunc();//(SqlFunc)Tcl_Alloc( sizeof(*pNew) + strlen30(zName) + 1 );
//pNew.zName = (char*)&pNew[1];
//for(i=0; zName[i]; i++){ pNew.zName[i] = tolower(zName[i]); }
//pNew.zName[i] = 0;
pNew.zName = zName.ToLower();
for ( p = pDb.pFunc ; p != null ; p = p.pNext )
{
if ( p.zName == pNew.zName )
{
//Tcl_Free((char*)pNew);
return p;
}
}
pNew.interp = pDb.interp;
pNew.pScript = null;
pNew.pNext = pDb.pFunc;
pDb.pFunc = pNew;
return pNew;
}
/// <summary>
/// 按宿舍楼查找当月未登记宿舍并返回Json
/// </summary>
/// <param name="id">宿舍楼ID</param>
/// <returns></returns>
public string GetJsonAllNoRecordByBuilding(int id)
{
/// 先获取本月已登记的宿舍ID
var rooms = Db.Queryable <T_Used>().
Where(u => u.Used_model_state && SqlFunc.Between(SqlFunc.Substring(u.Used_post_date, 0, 7),
SqlFunc.Substring(DateTime.Now, 0, 7), SqlFunc.Substring(DateTime.Now, 0, 7))).Select(u => u.Used_room_id).ToList();
return(Db.Queryable <T_Room>().Where((r) => r.Room_model_state && r.Number > 0 && r.Room_building_id == id && r.Room_is_active == true && !rooms.Contains(r.Room_id)).OrderBy(r => r.Room_vid).
ToJson());
}
