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 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(); } } } }