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.
471 lines
21 KiB
471 lines
21 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.TestDataDetail
|
|
{
|
|
public class TestDataDetail
|
|
{
|
|
//原始版本
|
|
public List<dynamic> 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<dynamic> 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<dynamic> 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();
}
}
}
}
|