| | |
|---|
| | | var orderBy = $"{query.sortName ?? "DepotCode"} {query.sortOrder ?? "ASC"}"; |
|---|
| | | var totalCount = 0; |
|---|
| | | |
|---|
| | | // 使用更清晰的查询语法 |
|---|
| | | // 修正后的查询语法 - 注意LeftJoin的参数 |
|---|
| | | var queryable = Db.Queryable<MesDepots>() |
|---|
| | | .LeftJoin<SysOrganization>((a, Org) => a.FSubsidiary == Org.Fid) |
|---|
| | | .LeftJoin<MesStaff>((a, c) => a.CreateBy == c.Id.ToString()) |
|---|
| | | .LeftJoin<MesCustomer>((a, d) => d.Id.ToString() == a.ClientId.ToString()) |
|---|
| | | .LeftJoin<MesSupplier>((a, f) => f.Id.ToString() == a.SuppLierId.ToString()) |
|---|
| | | .LeftJoin<SysDepartment>((a, g) => g.Id.ToString() == a.department.ToString()) |
|---|
| | | .LeftJoin<MesItems>((a, h) => h.DepotCode.ToString() == a.DepotId.ToString()) |
|---|
| | | .LeftJoin<MesStaff>((a, Org, MesStaff) => a.CreateBy == MesStaff.Id.ToString()) // 修正这一行 |
|---|
| | | .LeftJoin<MesCustomer>((a, Org, MesStaff, d) => d.Id.ToString() == a.ClientId.ToString()) // 修正参数 |
|---|
| | | .LeftJoin<MesSupplier>((a, Org, MesStaff, d, f) => f.Id.ToString() == a.SuppLierId.ToString()) // 修正参数 |
|---|
| | | .LeftJoin<SysDepartment>((a, Org, MesStaff, d, f, g) => g.Id.ToString() == a.department.ToString()) // 修正参数 |
|---|
| | | .LeftJoin<MesItems>((a, Org, MesStaff, d, f, g, h) => h.DepotCode.ToString() == a.DepotId.ToString()) // 修正参数 |
|---|
| | | .Where(whereClause) |
|---|
| | | .GroupBy(a => new { |
|---|
| | | a.Guid, |
|---|
| | |
|---|
| | | var sortName = string.IsNullOrEmpty(model.sortName) ? "a.PAGE_GROUP" : model.sortName; |
|---|
| | | var keyWhere = model.keyWhere; |
|---|
| | | string keyType = model.keyType; |
|---|
| | | string keyWord = model.keyWord; |
|---|
| | | var sbSql = new StringBuilder(); |
|---|
| | | sbSql.Append(" SELECT * FROM "); |
|---|
| | | sbSql.Append(" (SELECT N'(' +[Org].[FNumber] + N')' +[Org].[NAME] AS [FSubsidiary2] ,"); |
|---|
| | |
|---|
| | | sbSql.Append(" [f].[supp_name] AS [SuppLierId] , [g].[departmentname] AS [department2] ,"); |
|---|
| | | sbSql.Append(" ( CASE WHEN ( [a].[is_ng] = N'A' ) THEN N'正常' ELSE N'禁用' END ) AS [IsNg] "); |
|---|
| | | sbSql.Append(", [a].*,ROW_NUMBER() OVER(ORDER BY org.FNumber asc ,a.depot_code asc) AS RowIndex "); |
|---|
| | | if (string.IsNullOrEmpty(keyWord)) |
|---|
| | | { |
|---|
| | | sbSql.Append(",'' as kcQty"); |
|---|
| | | } |
|---|
| | | else |
|---|
| | | { |
|---|
| | | sbSql.Append(", isnull((select top 1 库存对比 from [dbo].[即时库存差异表] where 物料ID=" + keyWord + " and 仓库ID=[a].[depot_id]),'('+a.depot_name+')0/0') AS [kcQty] "); |
|---|
| | | } |
|---|
| | | if (string.IsNullOrEmpty(keyType)) |
|---|
| | | { |
|---|
| | | sbSql.Append(",cast(0 as bit) as chkInt"); |
|---|
| | | } |
|---|
| | | else |
|---|
| | | sbSql.Append(",cast( (select count(1) from SYS_USER_BIND b where b.userGuid='" + keyType + "' and b.aboutGuid=a.guid) as bit) as chkInt "); |
|---|
| | | sbSql.Append(",cast( (select count(1) from SYS_USER_BIND b where b.userGuid='" + keyType + "' and b.aboutGuid=a.guid and b.fType='仓库') as bit) as chkInt "); |
|---|
| | | sbSql.Append(" FROM [MES_DEPOTS] [a] Left JOIN [SYS_ORGANIZATION] [Org] ON ( [a].[FSubsidiary] = [Org].[FID] )"); |
|---|
| | | sbSql.Append(" Left JOIN [MES_STAFF] [c] ON ( [a].[create_by] = CAST([c].[id] AS NVARCHAR(50))) "); |
|---|
| | | sbSql.Append(" Left JOIN [MES_CUSTOMER] [d] ON (CAST([d].[id] AS NVARCHAR(50)) = CAST([a].[Client_Id] AS NVARCHAR(50))) "); |
|---|
