public async Task <ApiResult <List <SysNoticeChi> > > GetNoticeList(string admin_guid)
{
var res = new ApiResult <List <SysNoticeChi> >();
try
{
res.data = await Db.Queryable <SysNotice, SysNoticeChi>((a, b) => new object[] {
JoinType.Inner, a.id == b.notice_id
}).
Where((a, b) => b.admin_guid == admin_guid && SqlFunc.Between(DateTime.Now, a.begin_time, a.end_time)).Select((a, b) => new SysNoticeChi
{
title = a.title,
id = b.id,
read_status = b.read_status
}).ToListAsync();
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
Logger.Default.ProcessError((int)ApiEnum.Error, ex.Message);
}
return(res);
}
/// <summary>
/// 获取项目动态 提交情况
/// </summary>
/// <param name="projectId"></param>
/// <returns></returns>
public Dictionary <string, object> getTaskRecordByProjectId(QueryTaskRecordRequest request)
{
var dateQuery = Db.Queryable <TaskRecord, Task, Project>((tr, t, p) => new object[]
{
JoinType.Left, tr.TaskId == t.Id, JoinType.Left, t.ProjectId == p.Id
}).Where((tr, t, p) => t.ProjectId == request.projectOrTaskId && tr.Status == 1);
if (!string.IsNullOrEmpty(request.startTime) && !string.IsNullOrEmpty(request.endTime))
{
dateQuery = dateQuery.Where((tr, t, p) =>
SqlFunc.Between(tr.CreateTime, request.startTime, request.endTime));
}
var dateList = dateQuery.GroupBy((tr, t, p) => SqlFunc.DateValue(tr.CreateTime, DateType.Year) + "-" +
SqlFunc.DateValue(tr.CreateTime, DateType.Month) + "-" +
SqlFunc.DateValue(tr.CreateTime, DateType.Day))
.OrderBy((tr, t, p) => tr.CreateTime)
.Select((tr, t, p) => tr.CreateTime).ToList();
var dict = new Dictionary <string, object>();
foreach (var date in dateList)
{
var taskRecordList = Db.Queryable <TaskRecord>().Where(u => SqlFunc.DateIsSame(date, u.CreateTime))
.ToList();
dict.Add(date.ToString("yyyy-MM-dd"), taskRecordList);
}
return(dict);
}
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>().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>().Sum(it => it.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();
}
/// <summary>
/// 获取已回复消息 查询已回复消息 需要判断是否已回复过消息
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PageResponse <string> getHaveRelied(QueryMessageRequest request)
{
var query = Db.Queryable <Comment, Users, Users>((c, u1, u2) => new object[]
{
JoinType.Left, c.SubmitterId == u1.userId, JoinType.Left, c.TargetId == u2.userId
}).Where((c, u1, u2) => c.Content.Contains(request.key) && c.Type == request.type && c.TargetId == user.UserId &&
c.SubmitterId == user.UserId);
if (!string.IsNullOrEmpty(request.startTime) && !string.IsNullOrEmpty(request.endTime))
{
query = query.Where((c, u1, u2) => SqlFunc.Between(c.CreateTime, request.startTime, request.endTime));
}
var total = 0;
var json = query.Select((c, u1, u2) => new
{
id = c.Id, submitterName = u1.userName, content = c.Content, type = c.Type, createTime = c.CreateTime,
attachment = c.Attachment, targetName = u2.userName, docId = c.DocId, haveRead = c.HaveRead
}).ToPageList(request.page, request.limit, ref total).ToJson();
var pageResponse = new PageResponse <string> {
Total = total, Result = json
};
return(pageResponse);
}
public void Refresh(DateTime begin, DateTime end)
{
GetBandChartValues.Clear();
ProcessControlChartValues.Clear();
PostTraceChartValues.Clear();
PostJGPChartValues.Clear();
PostIFactoryChartValues.Clear();
//查询数据
var list = new DbContext().Read(db => db.InterfaceTimeDb
.GetList(p => SqlFunc.Between(p.CreateTime, begin, end)));
//var db = new DbContext().InterfaceTimeDb;
//var list = db.GetList(p => SqlFunc.Between(p.CreateTime, begin, end));
foreach (var one in list)
{
switch (one.InterfaceType)
{
case "GetBand":
GetBandChartValues.Add(new MeasureModel
{
DateTime = one.CreateTime,
Value = one.RequestTime
});
break;
case "ProcessControl":
ProcessControlChartValues.Add(new MeasureModel
{
DateTime = one.CreateTime,
Value = one.RequestTime
});
break;
case "PostTrace":
PostTraceChartValues.Add(new MeasureModel
{
DateTime = one.CreateTime,
Value = one.RequestTime
});
break;
case "PostJGP":
PostJGPChartValues.Add(new MeasureModel
{
DateTime = one.CreateTime,
Value = one.RequestTime
});
break;
case "PostIFactory":
PostIFactoryChartValues.Add(new MeasureModel
{
DateTime = one.CreateTime,
Value = one.RequestTime
});
break;
}
}
SetAxisLimits(begin, end);
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").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 getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).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 count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
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);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
}
/// <summary>
/// 获得历史数据
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="vin"></param>
/// <returns></returns>
public async static Task <DataTable> GetHistoryResult(DateTime startTime, DateTime endTime, string vin)
{
try
{
return(await _sqlSugar.Queryable <T_Result>().Where(t => SqlFunc.Between(t.testtime, startTime, endTime))
.WhereIF(!string.IsNullOrEmpty(vin), t => t.vin == vin)
.Select(t => new
{
序号 = t.id,
VIN码 = t.vin,
MTOC码 = t.mtoc,
驱动文件 = t.flashBin,
写入文件 = t.writeBin,
标定文件 = t.calBin,
软件版本 = t.softwareversion,
刷写时间 = t.testtime,
刷写状态 = SqlFunc.IIF(t.teststate == 2, "成功", "失败"),
是否打印 = SqlFunc.IIF(t.isprint == 1, "是", "否"),
追溯码 = t.tracyCode,
刷写端口 = t.num,
}).ToDataTableAsync());
}
catch (Exception ex)
{
string timestr = "yyyy-MM-dd HH:mm:ss";
Log.Error($"查询数据失败！\r\n {startTime.ToString(timestr)},{endTime.ToString(timestr)},{vin}", ex);
}
return(new DataTable());
}
/// <summary>
/// 获取我已审批
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PageResponse <string> getHaveApprovedList(QueryPendingRequest request)
{
var query = Db.Queryable <TaskRecord, ApproveRecord>((tr, ar) => new object[]
{
JoinType.Left, tr.Id == ar.DocId
}).Where((tr, ar) => tr.Desc.Contains(request.key) && ar.ApproverId == user.UserId && !ar.Result.Equals("已提交"));
if (!string.IsNullOrEmpty(request.startTime) && !string.IsNullOrEmpty(request.endTime))
{
query = query.Where((tr, ar) => SqlFunc.Between(tr.CreateTime, request.startTime, request.endTime));
}
var total = 0;
var json = query.Select((tr, ar) => new
{
id = tr.Id, submitterName = tr.SubmitterName, desc = tr.Desc, status = tr.Status, createTime = tr.CreateTime,
attachment = tr.Attachment, percent = tr.Percent, taskId = tr.TaskId
}).ToPageList(request.page, request.limit, ref total).ToJson();
// 此处返回json的原因是 目前审批类别只有任务完成情况 以后可能会新增审批类别 所以统一转成json返回
var pageResponse = new PageResponse <string> {
Total = total, Result = json
};
return(pageResponse);
}
public List <DepositItem> GetDepositItems(int did, string payMode, string beginDate, string endDate, string sno)
{
var list = db.Queryable <fee_depositdetail>()
.WhereIF(!string.IsNullOrEmpty(beginDate), it => SqlFunc.Between(it.addtime, beginDate, endDate))
.WhereIF(!string.IsNullOrEmpty(sno), it => it.paysn == sno)
.WhereIF(!string.IsNullOrEmpty(payMode), it => it.paytype == payMode)
.Where(x => x.did == did)
.Select(f => new DepositItem
{
branchCode = "",
branchName = "",
payAmout = (f.price * 100).ToString(),
hisOrdNum = f.detailid.ToString(),
agtOrdNum = f.paysn,
payMode = f.paytype.ToString(),
payStatus = "1",
balance = ""
}).ToList();
foreach (var item in list)
{
item.hisOrdNum = EString.ZeroFill(item.hisOrdNum);
item.receiptNum = EString.ZeroFill(item.hisOrdNum);
item.payMode = CodeConvertUtils.GetDepositType(item.payMode);
}
return(list);
}
/// <summary>
/// 检测该宿舍本月是否已登记
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool IsRecord(int id)
{
var i = Db.Queryable <T_Used>().
Where(u => u.Used_model_state && id == u.Used_room_id && 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).First();
return(i > 0);
}
/// <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());
}
/// <summary>
/// 查询今日待办事项，销售统计
/// </summary>
/// <returns></returns>
public Task <ApiResult <BackLogReport> > GetBackLogReport()
{
var res = new ApiResult <BackLogReport>()
{
statusCode = (int)ApiEnum.Error
};
try
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
string startWeek = Utils.GetMondayDate().ToShortDateString();
string endWeek = Utils.GetSundayDate().AddDays(1).ToShortDateString();
//昨天销售额
var yesterDayTime = Convert.ToDateTime(DateTime.Now.ToShortDateString() + " 00:00:00");
var yesterDay = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, yesterDayTime)).Sum(m => m.RealMoney);
//上周销售额
var yesterWeekTimeStart = Utils.GetMondayDate().AddDays(-7).ToShortDateString();
var yesterWeekTimeEnd = Utils.GetMondayDate().AddDays(-1).ToShortDateString();
var yesterWeek = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.Between(m.AddDate, yesterWeekTimeStart, yesterWeekTimeEnd)).Sum(m => m.RealMoney);
//上个月销售额
var yesterMonthTime = DateTime.Now.AddMonths(-1);
var yesterMonth = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, yesterMonthTime, DateType.Month)).Sum(m => m.RealMoney);
var model = new BackLogReport
{
//今日返货数量
ReturnCount = ErpReturnOrderDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime)),
//今日退货数量
BackCount = ErpBackGoodsDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime)),
//库存报警
StockPoliceCount = ErpGoodsSkuDb.Count(m => m.StockSum < 10),
//今日加入会员
JoinUserCount = ErpShopUserDb.Count(m => SqlFunc.DateIsSame(m.RegDate, dayTime)),
//今日销售金额
DaySaleMoney = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, dayTime)).Sum(m => m.RealMoney),
//本周销售金额
WeekSaleMoney = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.Between(m.AddDate, Utils.GetMondayDate(), Utils.GetMondayDate().AddDays(7))).Sum(m => m.RealMoney),
//本月销售金额
MonthSaleMoney = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, dayTime, DateType.Month)).Sum(m => m.RealMoney)
};
//日同比 同比增长率=（本年的指标值-去年同期的值）÷去年同期的值*100%
model.DayOnYear = yesterDay == 0?0:Convert.ToDouble((model.DaySaleMoney - yesterDay) / yesterDay * 100);
//周同比
model.WeekOnYear = yesterWeek == 0 ? 0 : Convert.ToDouble((model.WeekSaleMoney - yesterWeek) / yesterWeek * 100);
//月同比
model.MonthOnYear = yesterMonth == 0 ? 0 : Convert.ToDouble((model.MonthSaleMoney - yesterMonth) / yesterMonth * 100);
res.data = model;
res.statusCode = (int)ApiEnum.Status;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(Task.Run(() => res));
}
private List <BaseIList> SeachInput()
{
UserProc.CheckTime = "";
var exp = Expressionable.Create <BaseIList>().And(it => it.leibie == "染料入库");
if (checkMohu.Checked)
{
if (chkRLMC.chkSel)
{
exp.And(b => b.item0.Contains(chkRLMC.cobodgv.Text));
}
if (chkDJ.chkSel)
{
exp.And(b => b.item1.Contains(chkDJ.txt.Text));
}
if (chkGHS.chkSel)
{
exp.And(b => b.item2.Contains(chkGHS.txt.Text));
}
}
else
{
if (chkRLMC.chkSel)
{
exp.And(b => b.item0 == (chkRLMC.cobodgv.Text));
}
if (chkDJ.chkSel)
{
exp.And(b => b.item1 == (chkDJ.txt.Text));
}
if (chkGHS.chkSel)
{
exp.And(b => b.item2 == (chkGHS.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(it => it.SN).ToList();
return(getAll1);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <Page <ShopActivityDto> > > GetPagesAsync(PageParm parm)
{
var res = new ApiResult <Page <ShopActivityDto> >();
try
{
var nowTime = DateTime.Now;
var query = Db.Queryable <ErpShopActivity>()
.Where(m => !m.IsDel)
.WhereIF(parm.types == 1, m => SqlFunc.Between(nowTime, m.BeginDate, m.EndDate))
.Where(m => m.ShopGuid == parm.guid || m.ShopGuid == "all")
.OrderBy(m => m.AddDate, OrderByType.Desc).Select(m => new ShopActivityDto()
{
Guid = m.Guid,
Types = m.Types,
TypeName = SqlFunc.ToString(m.Types),
MethodName = SqlFunc.ToString(m.Method),
CountNum = m.CountNum,
BeginDate = m.BeginDate,
Enable = m.Enable,
EndDate = m.EndDate
}).ToPageAsync(parm.page, parm.limit);
if (query.Result.TotalItems != 0)
{
foreach (var item in query.Result.Items)
{
item.Status = DateTime.Now > item.EndDate ? "已完成" : "进行中";
switch (item.Types)
{
case 0: item.TypeName = "全部加盟商"; break;
case 1: item.TypeName = "商铺"; break;
case 2: item.TypeName = "品牌"; break;
}
item.MethodName = item.MethodName == "1" ? "打折" : "满减";
}
}
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));
}
private void Between()
{
Expression <Func <Student, bool> > exp = it => SqlFunc.Between(it.Name, 1, 2);
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " ([Name] BETWEEN @MethodConst0 AND @MethodConst1) ", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", 1), new SugarParameter("@MethodConst1", 2),
}, "Between error");
}
/// <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());
}
/// <summary>
/// 查询营业额
/// </summary>
/// <returns></returns>
public Task <ApiResult <DayTurnover> > GetTurnover(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <DayTurnover>();
try
{
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(1);
DateTime dayTime = Convert.ToDateTime(now.AddDays(1).ToShortDateString() + " 00:00:00");
//订单数
var orderSum = Db.Queryable <ErpSaleOrder>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Count();
//订单金额
var orderMoney = Db.Queryable <ErpSaleOrder>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Sum(m => m.RealMoney);
//查询退单金额
var backMoney = Db.Queryable <ErpBackGoods>()
.Where(m => m.Status == 1)
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Sum(m => m.BackMoney);
res.data = new DayTurnover()
{
OrderSum = orderSum,
Money = orderMoney - backMoney
};
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
private static void Demo3(SqlSugarClient db)
{
db.CodeFirst.InitTables <operateinfo>();
db.Deleteable <operateinfo>().ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-1-1")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-1-2")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-3-1")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-3-2")
}).ExecuteCommand();
db.Insertable(new operateinfo()
{
id = 1,
operate_type = 1,
operate_time = Convert.ToDateTime("2021-4-2")
}).ExecuteCommand();
var queryableLeft = db.Reportable(ReportableDateType.MonthsInLast1years).ToQueryable <DateTime>();
var queryableRight = db.Queryable <operateinfo>();
var list = db.Queryable(queryableLeft, queryableRight, JoinType.Left,
(x1, x2) => x2.operate_time.ToString("yyyy-MM") == x1.ColumnName.ToString("yyyy-MM"))
.GroupBy((x1, x2) => x1.ColumnName)
.Where(x1 => SqlFunc.Between(x1.ColumnName, "2021-01-01", DateTime.Now))
.Select((x1, x2) => new
{
count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.id > 0, 1, 0)),
date = x1.ColumnName.ToString("yyyy-MM")
}).ToList();
}
public override async Task <ApiResult <Page <CmsBalance> > > GetPagesAsync(PageParm parm, bool Async = true)
{
var res = new ApiResult <Page <CmsBalance> >();
var start_date = parm.start_date.Value.ToString("yyyyMM");
var end_date = parm.end_date.Value.ToString("yyyyMM");
var query = Db.Queryable <CmsBalance>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), a => a.agent_admin_guid == parm.guid && a.statu > 0)
.Where(t => SqlFunc.Between(t.BizDt, start_date, end_date))
.OrderBy(a => a.BizDt, OrderByType.Desc);
res.data = await query.ToPageAsync(parm.page, parm.limit);
return(res);
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().ToList();
var getTop2 = db.Queryable <Student>().Take(2).ToList();//TOP2
var getLike = db.Queryable <Student>().Where(it => it.Name.Contains("a")).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 == 2).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>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var asCount = db.Queryable <object>().AS("student").Count();
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 unionAll = db.UnionAll <Student>(db.Queryable <Student>(), db.Queryable <Student>());
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
}
/// <summary>
/// 获取我的任务列表
/// </summary>
/// <returns></returns>
public PageResponse <string> getTaskList(QueryProjectOrTaskRequest request)
{
var total = 0;
var query = Db.Queryable <Task, Project>((t, p) => new object[]
{
JoinType.Left, t.ProjectId == p.Id
}).Where((t, p) => t.TaskName.Contains(request.key));
if (SqlFunc.HasValue(request.startTime) && SqlFunc.HasValue(request.endTime))
{
query = query.Where((t, p) => SqlFunc.Between(t.CreateTime, request.startTime, request.endTime));
}
if (SqlFunc.HasValue(request.type))
{
query = query.Where((t, p) => t.Status == int.Parse(request.type));
}
// 如果是管理员 才查看所有任务 否则查询自己负责或者自己提交的
var roleId = Db.Queryable <UserRole>().Where(u => u.UserId == user.UserId).Select(u => u.RoleId).First();
if (roleId != 1)
{
query = query.Where((t, p) => t.ChargeUserId == user.UserId || t.SubmitterId == user.UserId || p.ChargeUserId == user.UserId);
}
var result = query.Select((t, p) => new
{
id = t.Id, projectId = t.ProjectId, taskName = t.TaskName, weight = t.Weight, progress = t.Progress,
status = t.Status, priority = t.Priority, startTime = t.StartTime, endTime = t.EndTime,
submitterName = t.SubmitterName, projectName = p.Name, chargeUserName = t.ChargeUserName
});
var json = result.ToPageList(request.page, request.limit, ref total).ToJson();
var pageResponse = new PageResponse <string> {
Total = total, Result = json
};
return(pageResponse);
}
/// <summary>
/// 获取我已提交
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PageResponse <string> getMySubmitList(QueryPendingRequest request)
{
var query = SimpleDb.AsQueryable().Where(u => u.SubmitterId == user.UserId && u.Desc.Contains(request.key));
if (!string.IsNullOrEmpty(request.startTime) && !string.IsNullOrEmpty(request.endTime))
{
query = query.Where(u => SqlFunc.Between(u.CreateTime, request.startTime, request.endTime));
}
var total = 0;
var json = query.Select(u => new
{
id = u.Id, submitterName = u.SubmitterName, desc = u.Desc, status = u.Status, createTime = u.CreateTime,
attachment = u.Attachment, percent = u.Percent, taskId = u.TaskId
}).ToPageList(request.page, request.limit, ref total).ToJson();
// 此处返回json的原因是 目前审批类别只有任务完成情况 以后可能会新增审批类别 所以统一转成json返回
var pageResponse = new PageResponse <string> {
Total = total, Result = json
};
return(pageResponse);
}
/// <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)
.OrderByIF(parm.orderType == 2, (t1, t2) => t1.Stock, 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.Stock,
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) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
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) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 2)
{
//本月
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(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) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
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) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
/// <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());
}
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.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(),
BackCount = SqlFunc.Subqueryable <ErpBackGoods>().Where(g => g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Count(),
BackMoney = SqlFunc.Subqueryable <ErpBackGoods>().Where(g => g.ShopGuid == m.Guid && SqlFunc.Between(g.AddDate, beginTime, endTime)).Sum(g => g.BackMoney),
})
.OrderBy(m => m.Money, OrderByType.Desc)
.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 async Task <MessageModel <List <AllData> > > Get(string openId = "123456", DateTime?date = null)
{
List <AllData> allDatas = new List <AllData>();
if (!string.IsNullOrEmpty(openId))
{
User user = (await _userServices.Query(q => q.OpenId == openId)).FirstOrDefault();
if (user != null)
{
List <Info> infos = (await _infoServices.Query(q => q.OpenId == openId)).ToList();
foreach (Info info in infos)
{
List <InfoDetail> infoDetails = new List <InfoDetail>();
DateTime?lastMonth = null;
DateTime?nextMonth = null;
if (date != null)
{
lastMonth = new DateTime(date.Value.Year, date.Value.Month, 1).AddMonths(-1);
nextMonth = lastMonth.Value.AddMonths(3).AddDays(-1);
}
if (lastMonth != null && nextMonth != null)
{
infoDetails = (await _infoDetailServices.Query(q =>
q.InfoId == info.InfoId &&
SqlFunc.Between(q.Date.Value, lastMonth.Value, nextMonth.Value)
)).ToList();
}
else
{
infoDetails = (await _infoDetailServices.Query(q =>
q.InfoId == info.InfoId
)).ToList();
}
allDatas.Add(new AllData()
{
user = user,
info = info,
infoDetail = infoDetails
});;
}
}
else
{
allDatas = await UserInitialAsync(openId);
}
}
return(new MessageModel <List <AllData> >()
{
msg = "获取成功",
success = allDatas.Count > 0,
response = allDatas
});
}
public async Task <ApiResult <string> > GetUnreadQuantity(string admin_guid)
{
var res = new ApiResult <string>()
{
statusCode = (int)ApiEnum.Status, data = "0"
};
try
{
var count = await Db.Queryable <SysNotice, SysNoticeChi>((a, b) => new object[] {
JoinType.Inner, a.id == b.notice_id
}).
Where((a, b) => b.admin_guid == admin_guid && b.read_status == false && SqlFunc.Between(DateTime.Now, a.begin_time, a.end_time)).CountAsync();
res.data = count.ToString();
}
catch (Exception ex)
{
res.statusCode = (int)ApiEnum.Error;
Logger.Default.ProcessError((int)ApiEnum.Error, ex.Message);
}
return(res);
}
/// <summary>
/// 获取项目列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PageResponse <List <Project> > findProjects(QueryProjectOrTaskRequest request)
{
var total = 0;
var sugarQueryableList = SimpleDb.AsQueryable().Where(u =>
u.Name.Contains(request.key) && SqlFunc.Between(u.CreateTime, request.startTime, request.endTime));
if (!string.IsNullOrEmpty(request.type))
{
sugarQueryableList = sugarQueryableList.Where(u => u.Type == request.type);
}
// 如果是管理员 才查看所有项目 否则查询自己负责或者自己提交的项目
var roleId = Db.Queryable <UserRole>().Where(u => u.UserId == user.UserId).Select(u => u.RoleId).First();
if (roleId != 1)
{
sugarQueryableList = sugarQueryableList.Where(u => u.ChargeUserId == user.UserId || u.SubmitterId == user.UserId);
}
var list = sugarQueryableList.OrderBy(u => request.sortColumn,
request.sortType == "asc" ? OrderByType.Asc : OrderByType.Desc)
.ToPageList(request.page, request.limit, ref total);
var pageResponse = new PageResponse <List <Project> > {
Total = total, Result = list
};
return(pageResponse);
}
/// <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 (chkRHLB.chkSel)
{
exp.And((b) => b.item1.Contains(chkRHLB.cobodgv.Text));
}
if (chkGYS.chkSel)
{
exp.And((b) => b.item2.Contains(chkGYS.txt.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.pishu.Contains(chkZL.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 (chkRHLB.chkSel)
{
exp.And((b) => b.item1 == (chkRHLB.cobodgv.Text));
}
if (chkGYS.chkSel)
{
exp.And((b) => b.item2 == (chkGYS.txt.Text));
}
if (chkZL.chkSel)
{
exp.And((b) => b.pishu == (chkZL.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);
}
private List <BaseIList> schData()
{
UserProc.CheckTime = "";
var exp = Expressionable.Create <BaseIList>().And((b) => b.leibie == "计划表");
if (checkMohu.Checked)
{
if (chkDanHao.chkSel)
{
exp.And((b) => b.dingdanhao.Contains(chkDanHao.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 (chkZPS.chkSel)
{
exp.And((b) => b.pishu.Contains(chkZPS.txt.Text));
}
if (chkZZ.chkSel)
{
exp.And((b) => b.seming.Contains(chkZZ.cobodgv.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.sehao.Contains(chkPS.cobodgv.Text));
}
if (chkTxt1.chkSel)
{
exp.And((b) => b.item0.Contains(chkTxt1.txt.Text));
}
if (chkTxt2.chkSel)
{
exp.And((b) => b.zongliang.Contains(chkTxt2.txt.Text));
}
}
else
{
if (chkDanHao.chkSel)
{
exp.And((b) => b.dingdanhao == (chkDanHao.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 (chkZPS.chkSel)
{
exp.And((b) => b.pishu == (chkZPS.txt.Text));
}
if (chkZZ.chkSel)
{
exp.And((b) => b.seming == (chkZZ.cobodgv.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.sehao == (chkPS.cobodgv.Text));
}
if (chkTxt1.chkSel)
{
exp.And((b) => b.item0 == (chkTxt1.txt.Text));
}
if (chkTxt2.chkSel)
{
exp.And((b) => b.zongliang == (chkTxt2.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.riqi, OrderByType.Desc)
.ToList();
return(getAll1);
}
public void RecordDataMigrateJob(PerformContext context)
{
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob Running ...");
// 从SourceDB查询出符合条件的FinaSettlementlist
var finaSettlement10s = SourceDB.Queryable <Models.SourceDB.FinaSettlement>().Where(it => SqlFunc.Between(it.CreateDate, DateTime.Now.AddHours(-1), DateTime.Now)).ToList();
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob 查询SourceDB.AreaList 个数为" + finaSettlement10s.Count.ToString());
if (finaSettlement10s.Count == 0)
{
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} 更新个数为" + finaSettlement10s.Count.ToString() + ",停止更新！");
}
else
{
var progressBar = context.WriteProgressBar();
var insertConnt = 0;
foreach (Models.SourceDB.FinaSettlement item in finaSettlement10s.WithProgress(progressBar))
{
Models.TargetDB.FinaSettlement targetItem = new Models.TargetDB.FinaSettlement();
targetItem.ID = item.ID;
targetItem.SerialNumber = item.SerialNumber;
targetItem.ClerkID = item.ClerkID;
targetItem.ClerkNum = item.ClerkNum;
targetItem.Date = item.CreateDate;
targetItem.Company = item.Company;
targetItem.Reckoner = item.Reckoner;
targetItem.Money = item.Money;
targetItem.ClearingForm = item.ClearingForm;
targetItem.OpeningBank = item.OpeningBank;
targetItem.Account = item.Account;
targetItem.PrintCount = item.PrintCount;
targetItem.PaymentWay = item.PaymentWay;
targetItem.Verifier = item.Verifier;
targetItem.Status = item.Status;
targetItem.Creater = item.Creater;
targetItem.CreateDate = item.CreateDate;
targetItem.UpdateDate = item.UpdateDate;
targetItem.Remark = item.Remark;
targetItem.FundContent = "0.0";
// 为什么一个一个插入都不行？
int insert = TargetDB.Insertable(targetItem).ExecuteCommand();
insertConnt += insert;
}
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob 插入TargetDB.AreaList 个数为" + insertConnt);
}
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} TrySqlSugarJob Running Over ...");
}