public static IEnumerable <Design_ArtTaskList> PageList(int submitterId, int page, int pageSize, out long records, int display)
{
var where = new Where <Design_ArtTaskList>();
where.And(o => o.Display != 4);
if (submitterId > 0)
{
where.And(o => o.SubmitterId == submitterId);
}
if (display > 0)
{
if (display == 3)
{
where.And(o => o.Display == display || o.Display == 5);
}
else
{
where.And(o => o.Display == display);
}
}
var p = Db.Context.From <Design_ArtTaskList>()
.Where(where);
records = p.Count();
return(p.Page(pageSize, page).OrderByDescending(o => o.SubmittedDate).ToList());
}
/// <summary>
/// 分页
/// </summary>
/// <param name="title"></param>
/// <param name="classId"></param>
/// <param name="prono"></param>
/// <param name="onlypar"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="records"></param>
/// <returns></returns>
public static List <Product_Info> PageList(string title, string classParPath, string spu, string tag, int isshow, int page, int pageSize, out long records)
{
var where = new Where <Product_Info>();
if (!string.IsNullOrEmpty(title))
{
where.And(Product_Info._.Title.Like(title));
}
if (!string.IsNullOrEmpty(classParPath) && classParPath != "0")
{
where.And(Product_Info._.ClassParPath.BeginWith(classParPath));
}
if (!string.IsNullOrEmpty(spu))
{
where.And(Product_Info._.Spu.BeginWith(spu));
}
if (isshow >= 0)
{
where.And(Product_Info._.IsShow == (isshow == 1 ? true : false));
}
if (!string.IsNullOrWhiteSpace(tag))
{
List <int> pids = Db.Context.FromSql($"select DISTINCT ProId from Product_Image where Tag like '%{tag.Trim()}%'").ToList <int>();
where.And(o => o.Id.In(pids));
}
var p = Db.Context.From <Product_Info>()
.Select <Mng_User>((a, b) => new { a.Id, a.Title, a.Color, a.Spu, a.FirstPic, a.Moq, a.QuantityUnit, a.AdminId, a.IsShow, a.CreateDate, a.Url, a.Stock, b.RealName, a.SupplierProductCode })
.LeftJoin <Mng_User>((a, b) => a.AdminId == b.Id)
.Where(where);
records = p.Count();
return(p.Page(pageSize, page).OrderByDescending(o => o.Id).ToList());
}
public ActionResult Index(int?pageIndex, int?pageSize, Dictionary entity)
{
try
{
DictionaryRepository ml = new DictionaryRepository();
var where = new Where <Dictionary>();
where.And(b => b.IsDeleted == false);
if (!string.IsNullOrEmpty(entity.Name))
{
where.And(b => b.Name.Like(entity.Name));
}
PagedList <Dictionary> page = ml.Search().Where(where).GetPagedList(pageIndex ?? PageIndex, pageSize ?? PageSize, Order, By);
if (Request.IsAjaxRequest())
{
return(PartialView("_Index", page));
}
return(View(page));
}
catch (Exception ex)
{
return(Content(ContentIcon.Error + "|" + ErrorWirter(RouteData, ex.Message)));
}
}
/// <summary>
/// 以当日查询班前讲话
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public DataTable GetToday(PageSearchRequest psr, string keyValue)
{
var KTime = DateTime.Parse(keyValue + " 00:00:00");
var JTime = DateTime.Parse(keyValue + " 23:59:59");
var where = new Where <TbSafeDatumManage>();
where.And(d => d.StartTime >= KTime && d.StartTime <= JTime);
//if (!string.IsNullOrWhiteSpace(psr.ProjectId))
//{
// where.And(d => d.ProjectId == psr.ProjectId);
//}
if (!string.IsNullOrWhiteSpace(psr.ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == psr.ProcessFactoryCode);
}
var ret = Db.Context.From <TbSafeDatumManage>()
.Select(
TbSafeDatumManage._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName.As("InsertUserName"))
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbUser>().Select(p => p.UserName)
.Where(TbUser._.UserCode == TbSafeDatumManage._.SpeechUser), "SpeechUserName")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).ToDataTable();
if (ret.Rows.Count > 0)
{
var data = ret.Rows[0]["SpeechContent"].ToString().Replace("<", "<").Replace(">", ">");
ret.Rows[0]["SpeechContent"] = data;
}
return(ret);
}
/// <summary>
/// 加工订单弹窗
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PageModel GetJGDDGridJson(WorkOrderRequest request)
{
var where = new Where <TbWorkOrder>();
where.And(d => d.ProcessingState == "Processing");
if (!string.IsNullOrEmpty(request.ProjectId))
{
where.And(d => d.ProjectId == request.ProjectId);
}
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == request.ProcessFactoryCode);
}
if (!string.IsNullOrWhiteSpace(request.keyword))
{
where.And <TbCompany>((a, c) => a.TypeName.Like(request.keyword));
}
try
{
var data = Db.Context.From <TbWorkOrder>()
.Select(
TbWorkOrder._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName"))
.LeftJoin <TbCompany>((a, b) => a.ProcessFactoryCode == b.CompanyCode)
.AddSelect(Db.Context.From <TbCompany>().Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbWorkOrder._.SiteCode), "SiteName")
.Where(where)
.ToPageList(request.rows, request.page);
return(data);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取数据。
/// </summary>
public BaseResult GetUser(TestTableParam param)
{
var where = new Where <TestTable>();
#region 模糊搜索条件
if (!string.IsNullOrWhiteSpace(param.SearchName))
{
where.And(d => d.Name.Like(param.SearchName));
}
if (!string.IsNullOrWhiteSpace(param.SearchIDNumber))
{
where.And(d => d.IDNumber.Like(param.SearchIDNumber));
}
if (!string.IsNullOrWhiteSpace(param.SearchMobilePhone))
{
where.And(d => d.MobilePhone.Like(param.SearchMobilePhone));
}
#endregion
var fs = DB.MySql.From <TestTable>()
.Where(where)
//.OrderByDescending(TableMysql._.CreateTime, TableMysql._.Id)
.OrderByDescending(d => new { d.CreateTime, d.Id });
#region 是否分页
var dateCount = 0;
if (param._PageIndex != null && param._PageSize != null)
{
//取总数，以计算共多少页。自行考虑将总数缓存。
dateCount = fs.Count();//.SetCacheTimeOut(10)
fs.Page(param._PageSize.Value, param._PageIndex.Value);
}
#endregion
var list = fs.ToList();
return(new BaseResult(true, list, "", dateCount));
}
/// <summary>
/// 获取数据。
/// </summary>
public BaseResult GetUser(TestTableParam param)
{
var where = new Where<TestTable>();
#region 模糊搜索条件
if (!string.IsNullOrWhiteSpace(param.SearchName))
{
where.And(d => d.Name.Like(param.SearchName));
}
if (!string.IsNullOrWhiteSpace(param.SearchIDNumber))
{
where.And(d => d.IDNumber.Like(param.SearchIDNumber));
}
if (!string.IsNullOrWhiteSpace(param.SearchMobilePhone))
{
where.And(d => d.MobilePhone.Like(param.SearchMobilePhone));
}
#endregion
var fs = DB.Context.From<TestTable>()
.Where(where)
.OrderByDescending(d => d.CreateTime);
#region 是否分页
var dateCount = 0;
if (param.pageIndex != null && param.pageSize != null)
{
//取总数，以计算共多少页。自行考虑将总数缓存。
dateCount = fs.Count();//.SetCacheTimeOut(10)
fs.Page(param.pageSize.Value, param.pageIndex.Value);
}
#endregion
var list = fs.ToList();
return new BaseResult(true, list, "", dateCount);
}
/// <summary>
/// 获取数据列表(设备信息)
/// </summary>
public PageModel GetEquipmentRegisterList(MachineCostRequest request)
{
#region 模糊搜索条件
var where = new Where <TbEquipmentRegister>();
if (!string.IsNullOrWhiteSpace(request.keyword))
{
where.And(d => d.EquimentName.Like(request.CheckCode));
}
where.And(d => d.TypeCode == "09");
#endregion
try
{
var data = Db.ContextGJ.From <TbEquipmentRegister>()
.Select(
TbEquipmentRegister._.EquimentCode
, TbEquipmentRegister._.EquimentName
, TbEquipmentRegister._.ManageLevel
, TbEquipmentRegister._.EquipmentCategory
, TbEquipmentRegister._.IsTzEquiment
, TbEquipmentRegister._.MeasurementUnit
, EquipmentType._.Type_Name)
.LeftJoin <EquipmentType>((a, c) => a.TypeCode == c.TypeCode)
.Where(where)
.OrderByDescending(p => p.id)
.ToPageList(request.rows, request.page);
return(data);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取接单,加工完成
/// </summary>
/// <param name="request"></param>
/// <returns>已接单,加工完成</returns>
private Tuple <List <TbWorkOrder>, List <TbWorkOrderDetail> > GetOverReceiveData(HomeRequest request)
{
var where = new Where <TbWorkOrder>();
var whereOver = new Where <TbWorkOrderDetail>();
if (!string.IsNullOrEmpty(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
//组织机构
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _WorkOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
if (SiteList.Count > 0)
{
where.And(p => p.SiteCode.In(SiteList));
}
}
where.And(new WhereClip("YEAR(TbWorkOrder.InsertTime)=" + request.DayMonth.Year));
where.And(p => p.ProcessingState != "ConfirmWork");
//已接单
var orderList = Db.Context.From <TbWorkOrder>()
.Select(TbWorkOrder._.WeightTotal, TbWorkOrder._.OrderCode, TbCompany._.ParentCompanyCode, TbWorkOrder._.DistributionTime, TbWorkOrder._.InsertTime)
.LeftJoin <TbCompany>((a, c) => a.SiteCode == c.CompanyCode)
.Where(where).ToList();
var receiveData = orderList.ToList();
//加工完成
var oderCodeList = orderList.Select(p => p.OrderCode).ToList();
whereOver.And(p => p.OrderCode.In(oderCodeList));
whereOver.And(new WhereClip("YEAR(FinishTime)=" + request.DayMonth.Year));
var overData = Db.Context.From <TbWorkOrderDetail>().Select(TbWorkOrderDetail._.WeightSmallPlan, TbWorkOrderDetail._.FinishTime, TbWorkOrderDetail._.OrderCode).Where(whereOver).ToList();
return(new Tuple <List <TbWorkOrder>, List <TbWorkOrderDetail> >(receiveData, overData));
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(PageSearchRequest request, string EarlyWarningCode)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbFormEarlyWarningNode>();
if (!string.IsNullOrWhiteSpace(EarlyWarningCode))
{
where.And(p => p.EarlyWarningCode == EarlyWarningCode);
}
if (!string.IsNullOrWhiteSpace(OperatorProvider.Provider.CurrentUser.ProjectId))
{
where.And(d => d.ProjectId == OperatorProvider.Provider.CurrentUser.ProjectId);
}
#endregion
try
{
var ret = Db.Context.From <TbFormEarlyWarningNode>()
.Select(
TbFormEarlyWarningNode._.All
, TbSysMenu._.MenuName)
.LeftJoin <TbSysMenu>((a, c) => a.MenuCode == c.MenuCode)
.Where(where).OrderBy(d => d.ID).ToPageList(request.rows, request.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
public void CommonUse()
{
// some times the select can be static in son place (the service of a DAO)
Select s = new Select("f.Name, f.Description, b.Descriptoin").From("Foo f join f.Bar b");
// Create a new where by checking some contition
Where where = new Where();
// check something and set a condition of where clause
where.And("f.Name like :pName");
// check something else and set another condition of where clause
where.And("b.Asociated > :pAso");
// Inject the where to the select
s.From().SetWhere(where);
// Create a basic order by
OrderBy order = new OrderBy().Add("b.Asociated", true);
// Check some condition and add an order
order.Add("f.Name");
// Inject the OrderBy to the select
s.From().SetOrderBy(order);
s.From().OrderBy("b.Other");
// And: The winner is....
string expected = "select f.Name, f.Description, b.Descriptoin from Foo f join f.Bar b where ((f.Name like :pName) and (b.Asociated > :pAso)) order by b.Asociated desc, f.Name, b.Other";
Assert.AreEqual(expected, s.Clause);
}
public static IEnumerable <Finance_Statistics> PageList(int page, int pageSize, out long records, string file, string key, DateTime?startdate, DateTime?enddate
, string departmentName)
{
var where = new Where <Finance_Statistics>();
where.And(o => o.Display != 2);
if (!string.IsNullOrEmpty(file) && !string.IsNullOrEmpty(key))
{
where.And(string.Format(" {0} like '%{1}%' ", file, key));
}
if (!string.IsNullOrEmpty(departmentName) && departmentName != "0")
{
where.And(string.Format(" DepartmentName like '%{0}%' ", departmentName));
}
if (startdate != null)
{
where.And(o => o.SaleDate >= startdate);
}
if (enddate != null)
{
where.And(o => o.SaleDate <= enddate);
}
var p = Db.Context.From <Finance_Statistics>()
.Where(where);
records = p.Count();
return(p.Page(pageSize, page).OrderByDescending(o => o.Id).ToList());
}
/// <summary>
/// 获取数据。
/// </summary>
public BaseResult GetUser(TStudentParam param)
{
var where = new Where <TStudent>();
#region 模糊搜索条件
if (!string.IsNullOrWhiteSpace(param.SearchName))
{
where.And(d => d.Name.Like(param.SearchName));
}
if (!string.IsNullOrWhiteSpace(param.SearchIDNumber))
{
where.And(d => d.IDNumber.Like(param.SearchIDNumber));
}
if (!string.IsNullOrWhiteSpace(param.SearchMobilePhone))
{
where.And(d => d.MobilePhone.Like(param.SearchMobilePhone));
}
#endregion
var fs = Db.Context.From <TStudent>()
.Where(where)
.OrderByDescending(d => new { d.CreateTime, d.Id });
#region 是否分页
var dateCount = 0;
if (param._PageIndex != null && param._PageSize != null)
{
//取总数，以计算共多少页。
dateCount = fs.Count();
fs.Page(param._PageSize.Value, param._PageIndex.Value);
}
#endregion
var list = fs.ToList();
return(new BaseResult(true, list, "", dateCount));
}
/// <summary>
/// 获取数据。此数据会持续增长，所以不建议一次性缓存。建议单个Model实体缓存。
/// </summary>
public BaseResult GetUser(TestTableParam param)
{
var where = new Where <TestTable>();
#region 模糊搜索条件
if (!string.IsNullOrWhiteSpace(param.SearchName))
{
where.And(d => d.Name.Like(param.SearchName));
}
if (!string.IsNullOrWhiteSpace(param.SearchIDNumber))
{
where.And(d => d.IDNumber.Like(param.SearchIDNumber));
}
if (!string.IsNullOrWhiteSpace(param.SearchMobilePhone))
{
where.And(d => d.MobilePhone.Like(param.SearchMobilePhone));
}
#endregion
#region 是否分页
var dateCount = 0;
if (param._PageIndex != null && param._PageSize != null)
{
//取总数，以计算共多少页。自行考虑将总数缓存。
dateCount = TestTableRepository.Count(where);//.SetCacheTimeOut(10)
}
#endregion
var list = TestTableRepository.Query(where, d => d.CreateTime, "desc", null, param._PageSize, param._PageIndex);
return(new BaseResult(true, list, "", dateCount));
}
/// <summary>
/// 获取进程列表
/// </summary>
/// <param name="year"></param>
/// <param name="fromMonth"></param>
/// <param name="toMonth"></param>
/// <param name="userId"></param>
/// <returns></returns>
public static List <Kpi_Process> GetList(int year, int fromMonth, int toMonth, int userId = 0)
{
var where = new Where <Kpi_Process>();
where.And(o => o.Year == year);
if (userId > 0)
{
where.And(o => o.UserId == userId);
}
if (fromMonth > 0 && fromMonth <= 12 && toMonth > 0 && toMonth <= 12 && toMonth == fromMonth)
{
where.And(o => o.Year == year && o.Month == fromMonth);
}
else if (toMonth > 0 && toMonth <= 12 && toMonth > fromMonth)
{
where.And(o => o.Year == year && o.Month >= fromMonth && o.Month <= toMonth);
}
else
{
where.And(o => o.Year == year && o.Month >= fromMonth);
}
var p = Db.Context.From <Kpi_Process>()
.Select(o => new { o.Id, o.UserId, o.Score, o.Year, o.Month, o.IsApprove, o.IsCreated, o.SelfScore })
.Where(where);
return(p.OrderBy(o => o.Id).ToList());
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public DataTable GetExportList(MonthCostHeSuanRequest request)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbMonthCostHeSuan>();
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
if (!string.IsNullOrWhiteSpace(request.HeSuanCode))
{
where.And(p => p.HeSuanCode == request.HeSuanCode);
}
#endregion
try
{
var ret = Db.Context.From <TbMonthCostHeSuan>()
.Select(
TbMonthCostHeSuan._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName)
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).OrderByDescending(d => d.ID).ToDataTable();
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 历史接单量统计
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public List <MoreReportModel> GetOrderMore(HomeRequest request)
{
var where = new Where <TbWorkOrder>();
//组织机构
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _WorkOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
if (SiteList.Count > 0)
{
where.And(p => p.SiteCode.In(SiteList));
}
}
if (!string.IsNullOrEmpty(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
where.And(new WhereClip("YEAR(DistributionTime)=" + request.DayMonth.Year));
var group = new GroupByClip("month(DistributionTime)");
var select = new Field("convert(varchar,month(DistributionTime))+'月' name,SUM(WeightTotal) as y");
var orderList = Db.Context.From <TbWorkOrder>().Select(select)
.Where(where)
.GroupBy(group).ToList <MoreReportModel>();
CreatLast(orderList);
return(orderList);
}
/// <summary>
/// 获取数据列表(明细数据)
/// </summary>
public PageModel GetDataItemListForPage(CarInfoRequest request)
{
var where = new Where <TbCarInfoDetail>();
if (!string.IsNullOrWhiteSpace(request.keyword))
{
where.And(p => p.UserCode.Like(request.keyword) || p.UserName.Like(request.keyword));
}
if (!string.IsNullOrWhiteSpace(request.CarCode))
{
where.And(p => p.CarCode == request.CarCode);
}
try
{
var data = Db.Context.From <TbCarInfoDetail>()
.Select(
TbCarInfoDetail._.UserCode.As("CarUserCode"),
TbCarInfoDetail._.UserName.As("CarUserName"),
TbCarInfoDetail._.Tel)
.Where(where)
.OrderByDescending(p => p.ID)
.ToPageList(request.rows, request.page);
return(data);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 查询本年本月所有信息
/// </summary>
/// <returns></returns>
public DataTable GetAllDayDatum1(string ProcessFactoryCode)
{
DateTime now = DateTime.Now;
//本月第一天
DateTime FirstDay = new DateTime(now.Year, now.Month, 1);
//本月最后一天
DateTime LastDay = Convert.ToDateTime(FirstDay.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd") + " 23:59:59");
var where = new Where <TbSafeDatumManage>();
where.And(d => d.StartTime >= FirstDay && d.StartTime <= LastDay);
if (!string.IsNullOrWhiteSpace(ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == ProcessFactoryCode);
}
var ret = Db.Context.From <TbSafeDatumManage>().Select(
TbSafeDatumManage._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName.As("InsertUserName"))
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbUser>().Select(p => p.UserName)
.Where(TbUser._.UserCode == TbSafeDatumManage._.SpeechUser), "SpeechUserName")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode).Where(where).ToDataTable();
if (ret.Rows.Count > 0)
{
for (var i = 0; i < ret.Rows.Count; i++)
{
var data = ret.Rows[i]["SpeechContent"].ToString().Replace("<", "<").Replace(">", ">");
ret.Rows[i]["SpeechContent"] = data;
}
}
return(ret);
}
/// <summary>
/// 获取数据。此数据会持续增长，所以不建议一次性缓存。建议单个Model实体缓存。
/// </summary>
public BaseResult GetUser(TestTableParam param)
{
var where = new Where<TestTable>();
#region 模糊搜索条件
if (!string.IsNullOrWhiteSpace(param.SearchName))
{
where.And(d => d.Name.Like(param.SearchName));
}
if (!string.IsNullOrWhiteSpace(param.SearchIDNumber))
{
where.And(d => d.IDNumber.Like(param.SearchIDNumber));
}
if (!string.IsNullOrWhiteSpace(param.SearchMobilePhone))
{
where.And(d => d.MobilePhone.Like(param.SearchMobilePhone));
}
#endregion
#region 是否分页
var dateCount = 0;
if (param._PageIndex != null && param._PageSize != null)
{
//取总数，以计算共多少页。自行考虑将总数缓存。
dateCount = TestTableRepository.Count(where);//.SetCacheTimeOut(10)
}
#endregion
var list = TestTableRepository.Query(where, d => d.CreateTime, "desc", null, param._PageSize, param._PageIndex);
return new BaseResult(true, list, "", dateCount);
}
///// <summary>
///// 获取数据列表(分页)
///// </summary>
//public List<TbRole> GetDataListForPage(TbRoleRequset param, string keyword)
//{
// //组装查询语句
// #region 模糊搜索条件
// var where = new Where<TbRole>();
// if (!string.IsNullOrEmpty(keyword))
// {
// where.And(d => d.RoleCode.Like(keyword));
// where.Or(d => d.RoleName.Like(keyword));
// }
// #endregion
// var orderBy = OrderByOperater.ASC;
// if (param.sord.Equals("desc"))
// orderBy = OrderByOperater.DESC;
// try
// {
// //取总数，以计算共多少页。
// var dateCount = Repository<TbRole>.Count(where);
// var orderByClip = new OrderByClip(new Field(param.sidx), orderBy);//排序字段
// var list = Repository<TbRole>.Query(where, orderByClip, param.sord, param.rows, param.page).ToList();
// param.records = dateCount;
// return list;
// }
// catch (Exception)
// {
// throw;
// }
//}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(TbRoleRequset request)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbRole>();
if (!string.IsNullOrWhiteSpace(request.RoleName))
{
where.And(p => p.RoleName.Contains(request.RoleName));
}
if (!string.IsNullOrWhiteSpace(request.DepartmentId))
{
where.And(p => p.DepartmentId == request.DepartmentId);
}
#endregion
try
{
var ret = Db.Context.From <TbRole>()
.Select(TbRole._.All).Where(where).OrderByDescending(d => d.RoleId).ToPageList(request.rows, request.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取分页列表数据(订单)
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PageModel GetDataListForPage(CostReportRequest request)
{
#region 模糊搜索条件
var where = new Where <TbWorkOrder>();
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
where.And(p => p.SiteCode.In(SiteList));
}
if (!string.IsNullOrEmpty(request.ProjectId))
{
where.And(p => p.ProjectId == request.ProjectId);
}
#endregion
try
{
var data = Db.Context.From <TbWorkOrder>()
.Select(
TbWorkOrder._.OrderCode
, TbWorkOrder._.SiteCode
, TbWorkOrder._.TypeCode
, TbWorkOrder._.TypeName
, TbCompany._.CompanyFullName)
.LeftJoin <TbCompany>((a, c) => a.SiteCode == c.CompanyCode)
.Where(where)
.OrderByDescending(p => p.ID)
.ToPageList(request.rows, request.page);
return(data);
}
catch (Exception)
{
throw;
}
}
public static IEnumerable <Finance_Statistics> GetList(string file, string key, DateTime?startdate, DateTime?enddate, string departmentName)
{
var where = new Where <Finance_Statistics>();
where.And(o => o.Display != 2);
if (!string.IsNullOrEmpty(file) && !string.IsNullOrEmpty(key))
{
where.And(string.Format(" {0} like '%{1}%' ", file, key));
}
if (!string.IsNullOrEmpty(departmentName) && departmentName != "0")
{
where.And(string.Format(" DepartmentName like '%{0}%' ", departmentName));
}
if (startdate != null)
{
where.And(o => o.SaleDate >= startdate);
}
if (enddate != null)
{
where.And(o => o.SaleDate <= enddate);
}
var p = Db.Context.From <Finance_Statistics>()
.Where(where);
return(p.ToList());
}
public DataTable GetDeliveryEarlyWarningList(int EWStart = 0, string UserCode = "", string ProjectId = "")
{
#region 模糊搜索条件
var where = new Where <TbFormEarlyWarningNodeInfo>();
where.And(d => d.ProjectId == ProjectId);
where.And(d => d.EWUserCode == UserCode);
where.And(d => d.EWStart == EWStart);
#endregion
try
{
var data = Db.Context.From <TbFormEarlyWarningNodeInfo>()
.Select(
TbFormEarlyWarningNodeInfo._.All
, TbUser._.UserName
, TbCompany._.CompanyFullName.As("SiteName")
, TbSysMenu._.MenuName)
.AddSelect(Db.Context.From <TbCompany>()
.Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbFormEarlyWarningNodeInfo._.WorkArea), "WorkAreaName")
.LeftJoin <TbUser>((a, c) => a.EWUserCode == c.UserCode)
.LeftJoin <TbCompany>((a, c) => a.CompanyCode == c.CompanyCode)
.LeftJoin <TbSysMenu>((a, c) => a.MenuCode == c.MenuCode)
.Where(where)
.OrderByDescending(p => p.ID).ToDataTable();
return(data);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public DataTable GetExportList(SafeCheckRequest request)
{
#region 模糊搜索条件
var where = new Where <TbSafeCheck>();
if (!string.IsNullOrWhiteSpace(request.CheckType))
{
where.And(d => d.CheckType == request.CheckType);
}
if (!string.IsNullOrWhiteSpace(request.SafeCheckCode))
{
where.And(d => d.SafeCheckCode.Like(request.SafeCheckCode));
}
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == request.ProcessFactoryCode);
}
if (request.CheckTimeS.HasValue)
{
where.And(d => d.CheckTime >= request.CheckTimeS);
}
if (request.CheckTimeE.HasValue)
{
where.And(d => d.CheckTime <= request.CheckTimeE);
}
#endregion
try
{
var data = Db.Context.From <TbSafeCheck>()
.Select(
TbSafeCheck._.ID
, TbSafeCheck._.Examinestatus
, TbSafeCheck._.SafeCheckCode
, TbSafeCheck._.PartInUsers
, TbSafeCheck._.CheckTime
, TbSafeCheck._.Remark
, TbSafeCheck._.InsertUserCode
, TbSafeCheck._.InsertTime
, TbUser._.UserName
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbSysDictionaryData._.DictionaryText.As("CheckTypeName"))
.AddSelect(Db.Context.From <TbUser>()
.Select(p => p.UserName)
.Where(TbUser._.UserCode == TbSafeCheck._.CheckUser), "CheckUserName")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.LeftJoin <TbSysDictionaryData>((a, c) => a.CheckType == c.DictionaryCode)
.Where(where)
.OrderByDescending(p => p.ID).ToDataTable();
return(data);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(StockTakingRequest request)
{
#region 模糊搜索条件
var where = new Where <TbStockTaking>();
if (request.TakDayS.HasValue)
{
where.And(p => p.TakDay >= request.TakDayS);
}
if (request.TakDayE.HasValue)
{
where.And(p => p.TakDay <= request.TakDayE);
}
if (!string.IsNullOrWhiteSpace(request.WarehouseType))
{
where.And(p => p.WarehouseType == request.WarehouseType);
}
if (!string.IsNullOrWhiteSpace(request.TakNum))
{
where.And(p => p.TakNum.Like(request.TakNum));
}
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
#endregion
try
{
var data = Db.Context.From <TbStockTaking>()
.Select(
TbStockTaking._.ID
, TbStockTaking._.TakNum
, TbStockTaking._.TakDay
, TbStockTaking._.TotalInventory
, TbStockTaking._.TotalTak
, TbStockTaking._.TotalEarnOrLos
, TbStockTaking._.EarnOrLos
, TbStockTaking._.Remarks
, TbStockTaking._.InsertUserCode
, TbStockTaking._.InsertTime
, TbUser._.UserName
, TbCompany._.CompanyFullName.As("FactoryName")
, TbSysDictionaryData._.DictionaryText.As("WarehouseTypeName"))
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.LeftJoin <TbSysDictionaryData>((a, c) => a.WarehouseType == c.DictionaryCode)
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.Where(where)
.OrderByDescending(p => p.ID)
.ToPageList(request.rows, request.page);
return(data);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取所有人员
/// </summary>
/// <param name="onlyInjob"></param>
/// <returns></returns>
public static List <Mng_User> GetAllList(bool onlyInjob, int departId = 0)
{
var where = new Where <Mng_User>();
where.And(o => o.InJob == onlyInjob);
if (departId > 0)
{
where.And(o => o.DepartId == departId);
}
return(Db.Context.From <Mng_User>().Where(where).OrderBy(o => o.Id).ToList());
}
/// <summary>
/// 创建人：李沈杰
/// 描 述：获取项目当天节能量
/// </summary>
/// <returns></returns>
public DataTable GetVW_TodayEmissionreduction(DateTime starttm, DateTime endtm)
{
var data = DB_Conn.Context.From <VW_TodayEmissionreduction>();
Where <VW_TodayEmissionreduction> where = new Where <VW_TodayEmissionreduction>();
where.And(t => t.TimeStamp >= starttm);
where.And(t => t.TimeStamp <= endtm);
data = data.Where(where).OrderBy(VW_TodayEmissionreduction._.TimeStamp.Asc);
DataTable tb = data.ToDataTable();
return(tb);
}
/// <summary>
/// 获取左边导航菜单列表数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <TbSysMenu> GetNavMenuList()
{
#region 模糊搜索条件
var where = new Where <TbSysMenu>();
where.And(d => d.IsShow == "0");
where.And(d => d.MenuType == "PC");
#endregion
return(Repository <TbSysMenu> .Query(where, d => d.Sort, "asc").ToList());
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="tag"></param>
/// <param name="thisId"></param>
/// <returns></returns>
public static bool IsExist(string tag, int thisId)
{
var where = new Where <Product_ImageTag>();
if (thisId > 0)
{
where.And(o => o.Id != thisId);
}
where.And(o => o.Tag == tag);
int count = Db.Context.From <Product_ImageTag>().Where(where).Count();
return(count > 0);
}
/// <summary>
/// 创建人：李沈杰
/// 描 述：(获取每年所有月的计量数据)
/// </summary>
/// <returns></returns>
public DataTable Get_tb_MonthIndex(DateTime starttm, DateTime endtm)
{
var data = DB_Conn.Context.From <tb_MonthIndex>();
Where <tb_MonthIndex> where = new Where <tb_MonthIndex>();
where.And(t => t.TimeStamp >= starttm);
where.And(t => t.TimeStamp <= endtm);
data = data.Where(where).OrderBy(tb_MonthIndex._.TimeStamp.Asc);
DataTable tb = data.ToDataTable();
return(tb);
}
/// <summary>
/// 获取流程列表
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public ActionResult GetList(string key)
{
var where = new Where <TbFlowDefine>();
where.And(d => d.FormCode == key);
where.And(d => d.FlowType == "New");
if (!string.IsNullOrWhiteSpace(OperatorProvider.Provider.CurrentUser.ProjectId))
{
where.And(d => d.ProjectId == OperatorProvider.Provider.CurrentUser.ProjectId);
}
var list = FlowDefineLogic.Query(where, new OrderByClip(new Field("FlowName")), "asc");
return(Content(list.ToJson()));
}
/// <summary>
/// 获取数据。
/// </summary>
public BaseResult GetUser(TestTableParam param)
{
#region 测试子查询修改
//var model = new TestTable
//{
// IDNumber = "XXXXXXXXXX"
//};
//var count2 = DB.MySql.Update<TestTable>(model, TestTable._.Id.SubQueryIn(
// DB.MySql.From<TestTable>().Select(d => d.Id).Where(d => d.IDNumber == "777")
//));
////以上同Sql语句：
////update TestTable set IDNumber='XXXXXXX' where Id in
//// (SELECT Id from TestTable where IDNumber='777')
#endregion
#region 测试批量Save
//var listModel = new List<TestTable>();
//var model1 = new TestTable()
//{
// Id = Guid.NewGuid(),
// IDNumber = "0000",
// CreateTime = DateTime.Now,
// MobilePhone = "000",
// Name = "00000"
//};
//var model2 = new TestTable()
//{
// Id = Guid.Parse("fdc87fad-0e80-49b2-aab0-c52d1fcd1297"),
// IDNumber = "000",
// CreateTime = DateTime.Now,
// MobilePhone = "000",
// Name = "00000"
//};
//var model3 = new TestTable()
//{
// Id = Guid.Parse("68805e30-5bc4-43ae-8ad7-8464be215e69")
//};
//model1.Attach(EntityState.Added);
//model2.Attach(EntityState.Modified);
//model3.Attach(EntityState.Deleted);
//listModel.Add(model1);
//listModel.Add(model2);
//listModel.Add(model3);
//var count = DB.MySql.Save<TestTable>(listModel);
#endregion
var where = new Where<TableMysql>();
#region 模糊搜索条件
if (!string.IsNullOrWhiteSpace(param.SearchName))
{
where.And(d => d.Name.Like(param.SearchName));
}
if (!string.IsNullOrWhiteSpace(param.SearchIDNumber))
{
where.And(d => d.IDNumber.Like(param.SearchIDNumber));
}
if (!string.IsNullOrWhiteSpace(param.SearchMobilePhone))
{
where.And(d => d.MobilePhone.Like(param.SearchMobilePhone));
}
#endregion
#region test
var tempFs = DB.MySql.From<TableMysql>()
.Select(TableMysql._.Id)
.Select(TableMysql._.Name)
.Select(d=>d.MobilePhone)
.Where(d=>d.Id == Guid.NewGuid())
.Where(d=>d.Name != "zhouhao")
.OrderByDescending(d => d.CreateTime)
.OrderBy(d => d.Id)
.GroupBy(d => new { d.Id, d.Name })
.GroupBy(TableMysql._.Id, TableMysql._.Test1)
.ToList();
var tempFs2 = DB.MySql.From<TableMysql>()
.Select(TableMysql._.Id)
.Select(TableMysql._.Test1)
.Select(d => d.Test2)
.Where(d => d.Test1 == false)
.Where(d => d.Test2 != 333)
.OrderByDescending(d => d.Name)
.OrderBy(d => d.Id)
.ToList();
#endregion
var fs = DB.MySql.From<TableMysql>()
.Where<TableMysql>((a,b)=>a.Id == Guid.NewGuid() && b.IDNumber == "11")
//.OrderByDescending(TableMysql._.CreateTime, TableMysql._.Id)
//.OrderByDescending(d => new { d.CreateTime, d.Id });
.OrderByDescending(d => d.CreateTime)
.OrderBy(d => d.Id);
#region 是否分页
var dateCount = 0;
if (param._PageIndex != null && param._PageSize != null)
{
//取总数，以计算共多少页。自行考虑将总数缓存。
dateCount = fs.Count();//.SetCacheTimeOut(10)
fs.Page(param._PageSize.Value, param._PageIndex.Value);
}
#endregion
var list = fs.ToList();
return new BaseResult(true, list, "", dateCount);
}
