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.TestDataDetail { public class TestDataDetail { //原始版本 public List QueryData2(string lot, string wo, string bt, string et, string workshop, string pallet_nbr, string sale_order,string containerno ,string check_nbr) { using (var context = new mesModel()) { string strwhere = ""; if (!string.IsNullOrEmpty(bt.ToString())) { strwhere += "and iv.wks_visit_date >= '" + Convert.ToString(bt) + "'"; strwhere += "and iv.wks_visit_date <= '" + Convert.ToString(et) + "'"; } strwhere += String.IsNullOrEmpty(workshop) ? "" : " and wm.area_code LIKE '" + Convert.ToString(workshop) + "%'"; strwhere += String.IsNullOrEmpty(wo) ? "" : " AND wm.workorder = '" + Convert.ToString(wo) + "'"; strwhere += String.IsNullOrEmpty(lot) ? "" : " AND iv.serial_nbr = '" + Convert.ToString(lot) + "'"; strwhere += String.IsNullOrEmpty(pallet_nbr) ? "" : " AND bz.pallet_nbr = '" + Convert.ToString(pallet_nbr) + "'"; //// strwhere += String.IsNullOrEmpty(sale_order) ? "" : " AND wm.sale_order = '" + Convert.ToString(sale_order) + "'"; strwhere += String.IsNullOrEmpty(containerno) ? "" : " and pg.containerno = '" + Convert.ToString(containerno) + "'"; strwhere += String.IsNullOrEmpty(check_nbr) ? "" : " and BZ.check_nbr = '" + Convert.ToString(check_nbr) + "'"; string sql = @"select a.[product_code]/*组件类型*/ ,a.power_grade/*功率档位*/ ,convert(varchar(100),a.[wks_visit_date],120) as wks_visit_date/*测试时间*/ ,a.[wks_id]/*机台号*/ ,a.containerno/*柜号*/ ,a.pallet_nbr/*托盘号*/ ,a.[serial_nbr] /*组件序列号*/ ,a.pmax ,a.[isc] ,a.voc ,a.[ipm] ,a.vpm ,a.[ff] ,a.current_grade/*电流档位*/ ,a.[workorder] as workorder/*工单号*/ ,a.cell_uop/*电池片功率*/ ,a.cell_eff/*电池片效率*/ --,a.[ipm] ,a.[rs] ,a.[rsh] ,a.[eff] ,a.[env_temp] ,a.[surf_temp] ,a.[temp] ,a.[ivfile_path] ,a.[cell_part_nbr]/*电池料号*/ ,a.[cell_lot_nbr]/*电池批号*/ ,a.[cell_supplier_code]/*电池供应商*/ ,a.[glass_part_nbr]/*玻璃料号*/ ,a.[glass_supplier_code]/*玻璃供应商*/ ,a.[glass_lot_nbr]/*电池批号*/ ,a.[eva_part_nbr]/*EVA料号*/ ,a.[eva_supplier_code]/*EVA供应商*/ ,a.[eva_lot_nbr]/*EVA批号*/ ,a.[bks_part_nbr]/*背板料号*/ ,a.[bks_supplier_code]/*背板供应商*/ ,a.[bks_lot_nbr]/*背板批号*/ ,a.[frame_part_nbr]/*型材料号*/ ,a.[frame_supplier_code]/*型材供应商*/ ,a.[frame_lot_nbr]/*型材批号*/ ,a.[jbox_part_nbr]/*接线盒料号*/ ,a.[jbox_supplier_code]/*接线盒供应商*/ ,a.[jbox_lot_nbr]/*接线盒批号*/ ,a.[huiliu_part_nbr] ,a.[huiliu_supplier_code] ,a.[huiliu_lot_nbr] ,a.[hulian_part_nbr] ,a.[hulian_supplier_code] ,a.[hulian_lot_nbr] ,a.el_grade ,a.DPGL ,a.cell_qty ,a.EDGL ,cast(convert(DECIMAL(18,2),a.pmax/a.EDGL*100) as varchar)+'%' as CTM from (select ab.[product_code]/*组件类型*/ ,sm.power_grade/*功率档位*/ ,convert(varchar(100),iv.[wks_visit_date],120) as wks_visit_date/*测试时间*/ ,iv.[wks_id]/*机台号*/ ,pg.containerno/*柜号*/ ,BZ.pallet_nbr/*托盘号*/ ,iv.[serial_nbr] /*组件序列号*/ ,iv.[voc] ,iv.[isc] ,iv.[vpm] ,iv.[ipm] ,iv.[pmax] ,iv.[ff] ,sm.current_grade/*电流档位*/ ,ab.[workorder] as workorder/*工单号*/ ,cmc.cell_uop/*电池片功率*/ ,cmc.cell_eff/*电池片效率*/ --,iv.[ipm] ,iv.[rs] ,iv.[rsh] ,iv.[eff] ,iv.[env_temp] ,iv.[surf_temp] ,iv.[temp] ,iv.[ivfile_path] ,ab.[cell_part_nbr]/*电池料号*/ ,ab.[cell_lot_nbr]/*电池批号*/ ,ab.[cell_supplier_code]/*电池供应商*/ ,ab.[glass_part_nbr]/*玻璃料号*/ ,ab.[glass_supplier_code]/*玻璃供应商*/ ,ab.[glass_lot_nbr]/*电池批号*/ ,ab.[eva_part_nbr]/*EVA料号*/ ,ab.[eva_supplier_code]/*EVA供应商*/ ,ab.[eva_lot_nbr]/*EVA批号*/ ,ab.[bks_part_nbr]/*背板料号*/ ,ab.[bks_supplier_code]/*背板供应商*/ ,ab.[bks_lot_nbr]/*背板批号*/ ,ab.[frame_part_nbr]/*型材料号*/ ,ab.[frame_supplier_code]/*型材供应商*/ ,ab.[frame_lot_nbr]/*型材批号*/ ,ab.[jbox_part_nbr]/*接线盒料号*/ ,ab.[jbox_supplier_code]/*接线盒供应商*/ ,ab.[jbox_lot_nbr]/*接线盒批号*/ ,ab.[huiliu_part_nbr] ,ab.[huiliu_supplier_code] ,ab.[huiliu_lot_nbr] ,ab.[hulian_part_nbr] ,ab.[hulian_supplier_code] ,ab.[hulian_lot_nbr] ,sm.el_grade ,SUBSTRING(part.descriptions,dbo.fn_find('_',part.descriptions,5)+1, case when dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5) >0 then dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5)-2 else 0 end) as DPGL ,wm.cell_qty ,CONVERT(decimal(10,2),SUBSTRING(part.descriptions,dbo.fn_find('_',part.descriptions,5)+1, case when dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5) >0 then dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5)-2 else 0 end))*CONVERT(int,wm.cell_qty) as EDGL from mes_level2_iface.dbo.iv iv inner join [mes_main].[dbo].[assembly_status] sm on iv.serial_nbr = sm.serial_nbr inner join [mes_main].[dbo].[wo_mfg] wm on sm.workorder = wm.workorder left join [mes_main].[dbo].[assembly_basis] ab on ab.serial_nbr=sm.serial_nbr left join [mes_main].[dbo].[config_mat_cell]cmc on ab.cell_part_nbr=cmc.[part_nbr] left join [mes_main].[dbo].[pack_pallets] BZ on BZ.pallet_nbr = SM.pallet_nbr left join [wh].[dbo].[tcontainerpallet] PG ON PG.palletno = bz.pallet_nbr left join mes_level4_iface.dbo.master_part part on ab.cell_part_nbr = part.bom_part_nbr where 1=1 " + strwhere + @" )a order by right(a.pallet_nbr,2)"; var res = context.DynamicListFromSql(sql, null); return res.ToList(); } } //山东客户版本 public List QueryData(string lot, string wo, string bt, string et, string workshop, string pallet_nbr, string sale_order, string containerno, string check_nbr) { using (var context = new mesModel()) { string strwhere = ""; if (!string.IsNullOrEmpty(bt.ToString())) { strwhere += "and iv.wks_visit_date >= '" + Convert.ToString(bt) + "'"; strwhere += "and iv.wks_visit_date <= '" + Convert.ToString(et) + "'"; } strwhere += String.IsNullOrEmpty(workshop) ? "" : " and wm.area_code LIKE '" + Convert.ToString(workshop) + "%'"; strwhere += String.IsNullOrEmpty(wo) ? "" : " AND wm.workorder = '" + Convert.ToString(wo) + "'"; strwhere += String.IsNullOrEmpty(lot) ? "" : " AND ast.serial_nbr in (" + Convert.ToString(lot) + ")"; strwhere += String.IsNullOrEmpty(pallet_nbr) ? "" : " AND ppk.pallet_nbr = '" + Convert.ToString(pallet_nbr) + "'"; //// strwhere += String.IsNullOrEmpty(sale_order) ? "" : " AND wm.sale_order = '" + Convert.ToString(sale_order) + "'"; strwhere += String.IsNullOrEmpty(containerno) ? "" : " and pg.containerno = '" + Convert.ToString(containerno) + "'"; strwhere += String.IsNullOrEmpty(check_nbr) ? "" : " and ppk.check_nbr = '" + Convert.ToString(check_nbr) + "'"; string sql = @"SELECT wh.containerno as container_nbr/*柜号*/ ,ast.[pallet_nbr]/*托盘编码*/ ,ast.[pack_seq]/*包装顺序*/ ,ast.[workorder]/*工单号*/ ,wm.sale_order ,ast.[serial_nbr] /*组件序列号*/ --,CONVERT(varchar(100), ppk.[pack_date], 120) as pack_date/*封箱时间*/ ,CONVERT(varchar(100),iv.wks_visit_date, 120) as wks_visit_date /*测试时间*/ ,iv.wks_id/*测试机台*/ ,ast.[power_grade]/*功率档位*/ ,ast.current_grade/*电流档位*/ --,ast.[product_code]/*装配件号*/ ,ast.[el_grade]/*EL等级*/ ,ast.exterior_grade/*外观等级*/ ,ast.[final_grade]/*最终等级*/ --,cpp.[descriptions]/*功率组*/ --,dst.[descriptions] shift_type/*班次*/ , iv.[pmax] pmax ,cast(substring(cast(iv.[ff]*100.00 as varchar(100)),1,charindex('.',iv.[ff]*100.00)+2) as float) as ff ,iv.[voc] voc ,iv.[isc] isc ,iv.[vpm] vpm ,iv.[ipm] ipm ,iv.[rs] rs ,iv.[rsh] rsh ,iv.[eff] eff --,cast(iv.[eff] as numeric(18,2)) eff ,iv.[env_temp] env_temp --,cast(iv.[surf_temp] as numeric(18,2)) surf_temp --,cast(iv.[temp] as numeric(18,2)) temp --,cast(iv.[ivfile_path] as numeric(18,2)) ivfile_path ,iv.[surf_temp] tmod ,ms.[descriptions] as cell_supplier/*电池片供应商*/ --,ppk.[check_nbr] ,case when cmc.attr_code = 'cell_uop' then cmc.attr_value end cell_uop/*单片功率*/ ,case when color.attr_code = 'cell_color' then color.attr_value end cell_color/*电池颜色*/ ,case when grade.attr_code = 'cell_grade' then grade.attr_value end cell_grade/*电池片等级*/ ,case when type.attr_code = 'cell_type' then type.attr_value end cell_type/*电池片类型*/ ,case when eff.attr_code = 'cell_eff' then eff.attr_value end cell_eff/*电池片效率档*/ --,cp.[cell_qty] --,ppk.container_nbr as batch ,dps.[pallet_status_desc]/*托盘状态*/ ,wm.cell_qty ,CONVERT(numeric(18,2),wm.cell_qty)* cast( case when right(cmc.attr_value,1)='W' THEN left(cmc.attr_value,LEN(cmc.attr_value)-1) else cmc.attr_value end as float) EDGL ,isnull(cast(cast(iv.[pmax]/NULLIF(cast(CONVERT(numeric(18,2),wm.cell_qty)* cast( case when right(cmc.attr_value,1)='W' THEN left(cmc.attr_value,LEN(cmc.attr_value)-1) else cmc.attr_value end as float) as FLOAT),0)*100 as numeric(18,2)) as varchar(50)),0)+'%' fff /*投入产出比*/ ,case when cmc.attr_code = 'crys_type' then cmc.attr_value end crys_type/*电池片类型*/ ,case when cmc.attr_code = 'cell_size' then cmc.attr_value end cell_size/*电池片效率档*/ --,CONVERT(int,wm.cell_qty) ,a.[wks_id] hj_wks_id FROM[mes_main].[dbo].[assembly_status] ast left join[mes_level2_iface].[dbo].[iv] iv on ast.[serial_nbr]=iv.[serial_nbr] left join[mes_main].[dbo].[pack_pallets] ppk on ast.[pallet_nbr]=ppk.[pallet_nbr] left join[mes_main].[dbo].[df_pallet_status] dps on ppk.[pallet_status]=dps.[pallet_status] left join[mes_main].[dbo].[df_shift_type] dst on ppk.[shift_type]=dst.[shift_type] left join[mes_main].[dbo].[config_power_group] cpp on ppk.[power_grade_group_id]=cpp.[power_grade_group_id] LEFT join mes_main.dbo.trace_componnet_lot ab on ab.serial_nbr = ast.serial_nbr and ab.part_type = 'CELL' --left join[mes_main].[dbo].[assembly_basis] -- ab --on ast.[serial_nbr]=ab.[serial_nbr] left join[mes_main].[dbo].[config_mat_attr] cmc on ab.part_nbr=cmc.[part_nbr] and cmc.attr_code = 'cell_uop' left join[mes_main].[dbo].[config_mat_attr] color on ab.part_nbr=color.[part_nbr] and color.attr_code = 'cell_color' left join[mes_main].[dbo].[config_mat_attr] grade on ab.part_nbr=grade.[part_nbr] and grade.attr_code = 'cell_grade' left join[mes_main].[dbo].[config_mat_attr] type on ab.part_nbr=type.[part_nbr] and type.attr_code = 'cell_type' left join[mes_main].[dbo].[config_mat_attr] eff on ab.part_nbr=eff.[part_nbr] and eff.attr_code = 'cell_eff' left join[mes_level4_iface].[dbo].[master_supplier] ms on ab.supplier_code=ms.[supplier_code] left join[mes_main].[dbo].[config_products] cp on ppk.[product_code]=cp.[product_code] left join[mes_main].[dbo].[wo_mfg] wm on ast.[workorder]=wm.[workorder] left join [wh].[dbo].[tcontainerpallet] PG ON PG.palletno = ppk.pallet_nbr --left join[mes_main].[dbo].[config_mat_eva] --cmeva --on ab.[eva_part_nbr]=cmeva.[part_nbr] --left join[mes_main].[dbo].[config_mat_bks] --cmbks --on ab.[cell_part_nbr]=cmbks.[part_nbr] --left join[mes_main].[dbo].[config_mat_glass] --cmglass --on ab.glass_part_nbr=cmglass.part_nbr --left join[mes_main].[dbo].[config_mat_frame] --cmframe --on ab.frame_part_nbr=cmframe.part_nbr --left join[mes_main].[dbo].[config_mat_jbox] --cmjbox --on ab.jbox_part_nbr=cmjbox.part_nbr left join wh.dbo.tcontainerpallet wh on ast.pallet_nbr =wh.palletno left join [mes_main].[dbo].[trace_workstation_visit] a on ast.[serial_nbr]=a.[serial_nbr] and a.wks_id like '%HJJ%' where 1=1 and iv.[pmax] is not null " + strwhere; var res = context.DynamicListFromSql(sql, null); return res.ToList(); } } //九江正泰衰减数据 public List QueryDataFake(string lot, string wo, string bt, string et, string workshop, string pallet_nbr, string sale_order, string containerno, string check_nbr) { using (var context = new mesModel()) { string strwhere = ""; if (!string.IsNullOrEmpty(bt.ToString())) { strwhere += "and iv.wks_visit_date >= '" + Convert.ToString(bt) + "'"; strwhere += "and iv.wks_visit_date <= '" + Convert.ToString(et) + "'"; } strwhere += String.IsNullOrEmpty(workshop) ? "" : " and wm.area_code LIKE '" + Convert.ToString(workshop) + "%'"; strwhere += String.IsNullOrEmpty(wo) ? "" : " AND wm.workorder = '" + Convert.ToString(wo) + "'"; strwhere += String.IsNullOrEmpty(lot) ? "" : " AND iv.serial_nbr = '" + Convert.ToString(lot) + "'"; strwhere += String.IsNullOrEmpty(pallet_nbr) ? "" : " AND bz.pallet_nbr = '" + Convert.ToString(pallet_nbr) + "'"; //// strwhere += String.IsNullOrEmpty(sale_order) ? "" : " AND wm.sale_order = '" + Convert.ToString(sale_order) + "'"; strwhere += String.IsNullOrEmpty(containerno) ? "" : " and pg.containerno = '" + Convert.ToString(containerno) + "'"; strwhere += String.IsNullOrEmpty(check_nbr) ? "" : " and BZ.check_nbr = '" + Convert.ToString(check_nbr) + "'"; string sql = @"select a.[product_code]/*组件类型*/ ,a.power_grade/*功率档位*/ ,convert(varchar(100),a.[wks_visit_date],120) as wks_visit_date/*测试时间*/ ,a.[wks_id]/*机台号*/ ,a.containerno/*柜号*/ ,a.pallet_nbr/*托盘号*/ ,a.[serial_nbr] /*组件序列号*/ ,case when a.[pmax] >= 405 and a.pmax < 410 then ROUND(a.pmax,2,1) when a.pmax >= 410 and a.pmax < 415 then ROUND(a.pmax*0.9879,2,1) when a.pmax >= 415 then ROUND(a.pmax*0.98,2,1) end pmax ,round(a.[isc],2,1) isc ,case when a.[pmax] >= 405 and a.pmax < 410 then ROUND(a.voc,2,1) when a.pmax >= 410 and a.pmax < 415 then ROUND(a.voc*0.9879,2,1) when a.pmax >= 415 then ROUND(a.voc*0.98,2,1) end voc ,round(a.[ipm],2,1) ipm ,case when a.[pmax] >= 405 and a.pmax < 410 then ROUND(a.vpm,2,1) when a.pmax >= 410 and a.pmax < 415 then ROUND(a.vpm*0.9879,2,1) when a.pmax >= 415 then ROUND(a.vpm*0.98,2,1) end vpm ,a.[ff] ,a.current_grade/*电流档位*/ ,a.[workorder] as workorder/*工单号*/ ,a.cell_uop/*电池片功率*/ ,a.cell_eff/*电池片效率*/ --,a.[ipm] ,a.[rs] ,a.[rsh] ,a.[eff] ,a.[env_temp] ,a.[surf_temp] ,a.[temp] ,a.[ivfile_path] ,a.[cell_part_nbr]/*电池料号*/ ,a.[cell_lot_nbr]/*电池批号*/ ,a.[cell_supplier_code]/*电池供应商*/ ,a.[glass_part_nbr]/*玻璃料号*/ ,a.[glass_supplier_code]/*玻璃供应商*/ ,a.[glass_lot_nbr]/*电池批号*/ ,a.[eva_part_nbr]/*EVA料号*/ ,a.[eva_supplier_code]/*EVA供应商*/ ,a.[eva_lot_nbr]/*EVA批号*/ ,a.[bks_part_nbr]/*背板料号*/ ,a.[bks_supplier_code]/*背板供应商*/ ,a.[bks_lot_nbr]/*背板批号*/ ,a.[frame_part_nbr]/*型材料号*/ ,a.[frame_supplier_code]/*型材供应商*/ ,a.[frame_lot_nbr]/*型材批号*/ ,a.[jbox_part_nbr]/*接线盒料号*/ ,a.[jbox_supplier_code]/*接线盒供应商*/ ,a.[jbox_lot_nbr]/*接线盒批号*/ ,a.[huiliu_part_nbr] ,a.[huiliu_supplier_code] ,a.[huiliu_lot_nbr] ,a.[hulian_part_nbr] ,a.[hulian_supplier_code] ,a.[hulian_lot_nbr] ,a.el_grade ,a.DPGL ,a.cell_qty ,a.EDGL ,cast(convert(DECIMAL(18,2),a.pmax/a.EDGL*100) as varchar)+'%' as CTM from (select ab.[product_code]/*组件类型*/ ,sm.power_grade/*功率档位*/ ,convert(varchar(100),iv.[wks_visit_date],120) as wks_visit_date/*测试时间*/ ,iv.[wks_id]/*机台号*/ ,pg.containerno/*柜号*/ ,BZ.pallet_nbr/*托盘号*/ ,iv.[serial_nbr] /*组件序列号*/ ,iv.[voc] ,iv.[isc] ,iv.[vpm] ,iv.[ipm] ,iv.[pmax] ,iv.[ff] ,sm.current_grade/*电流档位*/ ,ab.[workorder] as workorder/*工单号*/ ,cmc.cell_uop/*电池片功率*/ ,cmc.cell_eff/*电池片效率*/ --,iv.[ipm] ,iv.[rs] ,iv.[rsh] ,iv.[eff] ,iv.[env_temp] ,iv.[surf_temp] ,iv.[temp] ,iv.[ivfile_path] ,ab.[cell_part_nbr]/*电池料号*/ ,ab.[cell_lot_nbr]/*电池批号*/ ,ab.[cell_supplier_code]/*电池供应商*/ ,ab.[glass_part_nbr]/*玻璃料号*/ ,ab.[glass_supplier_code]/*玻璃供应商*/ ,ab.[glass_lot_nbr]/*电池批号*/ ,ab.[eva_part_nbr]/*EVA料号*/ ,ab.[eva_supplier_code]/*EVA供应商*/ ,ab.[eva_lot_nbr]/*EVA批号*/ ,ab.[bks_part_nbr]/*背板料号*/ ,ab.[bks_supplier_code]/*背板供应商*/ ,ab.[bks_lot_nbr]/*背板批号*/ ,ab.[frame_part_nbr]/*型材料号*/ ,ab.[frame_supplier_code]/*型材供应商*/ ,ab.[frame_lot_nbr]/*型材批号*/ ,ab.[jbox_part_nbr]/*接线盒料号*/ ,ab.[jbox_supplier_code]/*接线盒供应商*/ ,ab.[jbox_lot_nbr]/*接线盒批号*/ ,ab.[huiliu_part_nbr] ,ab.[huiliu_supplier_code] ,ab.[huiliu_lot_nbr] ,ab.[hulian_part_nbr] ,ab.[hulian_supplier_code] ,ab.[hulian_lot_nbr] ,sm.el_grade ,SUBSTRING(part.descriptions,dbo.fn_find('_',part.descriptions,5)+1, case when dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5) >0 then dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5)-2 else 0 end) as DPGL ,wm.cell_qty ,CONVERT(decimal(10,2),SUBSTRING(part.descriptions,dbo.fn_find('_',part.descriptions,5)+1, case when dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5) >0 then dbo.fn_find('_',part.descriptions,6)-dbo.fn_find('_',part.descriptions,5)-2 else 0 end))*CONVERT(int,wm.cell_qty) as EDGL from mes_level2_iface.dbo.iv iv inner join [mes_main].[dbo].[assembly_status] sm on iv.serial_nbr = sm.serial_nbr inner join [mes_main].[dbo].[wo_mfg] wm on sm.workorder = wm.workorder left join [mes_main].[dbo].[assembly_basis] ab on ab.serial_nbr=sm.serial_nbr left join [mes_main].[dbo].[config_mat_cell]cmc on ab.cell_part_nbr=cmc.[part_nbr] left join [mes_main].[dbo].[pack_pallets] BZ on BZ.pallet_nbr = SM.pallet_nbr left join [wh].[dbo].[tcontainerpallet] PG ON PG.palletno = bz.pallet_nbr left join mes_level4_iface.dbo.master_part part on ab.cell_part_nbr = part.bom_part_nbr where 1=1 " + strwhere + @" )a order by right(a.pallet_nbr,2)"; var res = context.DynamicListFromSql(sql, null); return res.ToList(); } } } }