旧版报表、仓库
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 

84 lines
3.2 KiB

using System.Collections.Generic;
using System.Data.Entity.Validation;
using System.Linq;
using System.Web;
using Warehouse.Models.mesModel;
using Z.EntityFramework.Plus;
using System;
using System.Data;
namespace Warehouse.DAL.WOprocess
{
public class WOprocess
{
public List<dynamic> QueryInfo(string wo)
{
using (var context = new mesModel())
{
string sql = @"select FA.descriptions site,FB.going_nbr module,FA.workshop areas,FA.workorder wo from
(
SELECT
e.descriptions as workshop,
e.area_code,
b.process_code ,
d.descriptions,
f.workorder
FROM (select distinct serial_nbr,wks_id,shift_type,max(wks_visit_date)as wks_visit_date
from mes_main.dbo.trace_workstation_visit
group by serial_nbr,wks_id,shift_type) a
-- left join [mes_main].[dbo].[df_shift_type] time
-- on a.shift_type=time.shift_type
LEFT join mes_main.dbo.config_workstation b
on a.wks_id = b.wks_id
LEFT JOIN mes_main.dbo.assembly_status c
on a.serial_nbr =c.serial_nbr
LEFT JOIN mes_main.dbo.df_processes d
on b.process_code = d.process_code
left join mes_main.dbo.df_areas e
on b.area_code= e.area_code
LEFT JOIN mes_main.DBO.wo_mfg f
ON f.workorder = c.workorder
where 1=1 and f.workorder = '" + Convert.ToString(wo) + @"'
group by b.process_code,d.descriptions,e.descriptions,e.area_code,f.workorder
--order by RIGHT(b.process_code,2)
)FA
LEFT JOIN
(
select COUNT(zzsl.serial_nbr) as going_nbr,zzsl.ZZZD,zzsl.area_code from
(
select a.serial_nbr,MAX(a.wks_visit_date) as wks_visit_date,max(g.process_code) AS ZZZD,b.area_code
from (select serial_nbr,MAX(wks_visit_date) as wks_visit_date
from mes_main.dbo.trace_workstation_visit
group by serial_nbr) a
left join mes_main.dbo.trace_workstation_visit h
on a.serial_nbr = h.serial_nbr and a.wks_visit_date = h.wks_visit_date
--left join [mes_main].[dbo].[df_shift_type] time
-- on a.shift_type=time.shift_type
left join mes_main.dbo.assembly_status e
on a.serial_nbr = e.serial_nbr
left join mes_main.dbo.config_products c
on e.product_code = c.product_code
left join mes_main.dbo.config_workstation b
on h.wks_id = b.wks_id
left join mes_main.dbo.config_process d
on c.process_group_id = d.process_group_id
and e.process_code = d.process_code
left join mes_main.dbo.config_process g
on c.process_group_id = g.process_group_id
and g.process_idx-1 = d.process_idx
LEFT JOIN mes_main.DBO.wo_mfg f
ON f.workorder = e.workorder
where 1=1 and f.workorder = '" + Convert.ToString(wo) + @"'
group by a.serial_nbr,b.area_code
)zzsl
group by zzsl.ZZZD,zzsl.area_code
)FB
ON FA.process_code = FB.ZZZD and FA.area_code = FB.area_code
where FB.going_nbr is not null
order by RIGHT(FA.process_code,2)";
var res = context.DynamicListFromSql(sql, null);
return res.ToList();
}
}
}
}