select 
m.MasterID _partId,
m.PartNumber _partNumber,
'' _Dept1,
'' _Dept2,
'' _Process,
l.FormComponentID _controlId,
d.PageNum _pageNum,
p.DeptID _DeptID,
p.ProcessID _ProcessID,
p.LayoutID _LayoutID,
l.FormComponentType _typeIdOld,
l.FormComponentType _typeId,
'' _fieldName,
isnull(lt.ValueMemo, d.ValueString) resultValue,
(select top 1 lower(fpath) from #fmap fm join DETAIL dd on fm.fname = dd.ValueString where dd.MasterID = m.MasterID group by fpath order by count(*) desc) pathHint
from "MASTER" m
join MASTPROC mp on m.MasterID = mp.MasterID
join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
join LAYOUT l on p.LayoutID = l.LayoutID
join DETAIL d on m.MasterID = d.MasterID and p.DeptID = d.DeptID and p.ProcessID = d.ProcessID and l.FormComponentID = d.FormComponentID
left join LONGTEXT lt on d.IsLong = 1 and (case when isnumeric(d.ValueString) = 1 and d.IsLong = 1 then cast(d.ValueString as int) else 0 end) = lt.LongID
-- left join FIELDS f on p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID
where not exists (select 1 from FIELDS f where p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID)
--and m.MasterID = 876
order by m.MasterID

select * from filemapping fm
join DETAIL dd on fm.fname = dd.ValueString
where dd.MasterID = 876

select top 1 fpath from filemapping fm
join DETAIL dd on fm.fname = dd.ValueString
where dd.MasterID = 876
group by fpath
order by count(*) desc


select * from DEPT d
join PROCESS p on d.DeptID = p.DeptID
where d.DeptID = 6 and p.ProcessID = 4

if ('im001' = 'IM001')begin
print 1
end else begin
print 0
end


--901----------
select 
m.MasterID _partId,
m.PartNumber _partNumber,
'' _Dept1,
'' _Dept2,
'' _Process,
l.FormComponentID _controlId,
od.PageNum _pageNum,
p.DeptID _DeptID,
p.ProcessID _ProcessID,
p.LayoutID _LayoutID,
l.FormComponentType _typeIdOld,
l.FormComponentType _typeId,
f.FieldName _fieldName,
od.FileName resultValue
from "MASTER" m
join MASTPROC mp on m.MasterID = mp.MasterID
join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
join LAYOUT l on p.LayoutID = l.LayoutID
join FIELDS f on p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID and f.SourceID = 901
left join ObjectsDetail od on m.PartNumber = od.PartNumber and f.DeptID = od.DeptID and f.ProcessID = od.ProcessID and f.FormComponentID = od.FormComponentID
--1----------
select 
m.MasterID _partId,
m.PartNumber _partNumber,
'' _Dept1,
'' _Dept2,
'' _Process,
l.FormComponentID _controlId,
-1 _pageNum,
p.DeptID _DeptID,
p.ProcessID _ProcessID,
p.LayoutID _LayoutID,
l.FormComponentType _typeIdOld,
l.FormComponentType _typeId,
f.FieldName _fieldName,
'' resultValue
from "MASTER" m
join MASTPROC mp on m.MasterID = mp.MasterID
join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
join LAYOUT l on p.LayoutID = l.LayoutID
join FIELDS f on p.DeptID = f.DeptID and p.ProcessID = f.ProcessID and l.FormComponentID = f.FormComponentID and f.SourceID = 1
where m.PartNumber = 'C145'

select * from "MASTER" where PartNumber = 'C145'
select * from DetailPart where PartNumber = 'C145'

select * from Detail_Quality where PartNumber = 'CO5525-002'

/*
select 
'' Dept1,
'' Dept2,
'' Process,
p.DeptID,
p.ProcessID,
p.LayoutID,
case when 
		(select count(*) from LAYOUT l11 where ld.LayoutID = l11.LayoutID) = 1 
		and exists (select 1 from LAYOUT l12 where ld.LayoutID = l12.LayoutID and l12.FormComponentType in (2, 11))
	then 'y' 
	else 'n' end isMedia,
(select max(l21.FormComponentTop + l21.FormComponentHeight) from LAYOUT l21 where ld.LayoutID = l21.LayoutID) height
from PROCESS p
join LAYDESC ld on p.LayoutID = ld.LayoutID


select 
'' Dept1,
'' Dept2,
'' Process,
l.FormComponentID controlId,
p.DeptID,
p.ProcessID,
p.LayoutID,
l.FormComponentType typeIdOld,
l.FormComponentType typeId,
l.FormComponentLeft "left",
l.FormComponentTop "top",
l.FormComponentWidth "width",
l.FormComponentHeight "height",
l.FormComponentTitle "title", 
l.Description "descriptionAltTitle",
'' defaultValues,
(l.FormComponentID + 1) * 10 zIndex
from PROCESS p
join LAYDESC ld on p.LayoutID = ld.LayoutID
join LAYOUT l on ld.LayoutID = l.LayoutID
order by 4, 5, 6, 7

where l.FormComponentType = 9
*/
/*
select count(*) from LAYOUT l1 where 3 = l1.LayoutID

select * from LAYOUT
where LayoutID in (2, 49)

select FormComponentType, count(*)
from LAYOUT
group by FormComponentType
order by 1

select PageNum, count(*)
from LAYOUT
group by PageNum

select * from LAYOUT
where PageNum = 23
*/
-----------------------------------------------------------------------------------------

/*
select 
m.MasterID partId,
case when m.IsReleased = 1 then 'n' else 'y' end preProduction,
m.PartNumber number,
m.MasterName name,
m.CustomerID clientId,
dp.Weight weight,
dp.Alloy alloyTemper,
'untracked' status,
m.ThumbnailImage imageThumb,
(
	select top 1 isnull(lt.ValueMemo, d.ValueString)
	from MASTPROC mp 
	join PROCESS p on mp.DeptID = p.DeptID and mp.ProcessID = p.ProcessID
	join DEPT de on p.DeptID = de.DeptID
	join LAYOUT l on p.LayoutID = l.LayoutID
	join DETAIL d on m.MasterID = d.MasterID and p.DeptID = d.DeptID and p.ProcessID = d.ProcessID and l.FormComponentID = d.FormComponentID
	left join LONGTEXT lt on d.IsLong = 1 and (case when isnumeric(d.ValueString) = 1 and d.IsLong = 1 then cast(d.ValueString as int) else 0 end) = lt.LongID
	where mp.MasterID = m.MasterID 
	and l.FormComponentType = 2
	order by de.RecordOrder, p.RecordOrder
) imageMain,
(select top 1 lower(fpath) from #fmap fm join DETAIL dd on fm.fname = dd.ValueString where dd.MasterID = m.MasterID group by fpath order by count(*) desc) pathHint

from "MASTER" m
left join DetailPart dp on m.PartNumber = dp.PartNumber


select 
MasterID partId, 
DeptID, 
ProcessID,
'' Dept1,
'' Dept2,
'' Process
from MASTPROC
*/

/*
select * 
from MASTDEPT md
left join MASTPROC mp on md.MasterID = mp.MasterID and md.DeptID = mp.DeptID
where mp.MasterId is null

select * 
from MASTDEPT md
right join MASTPROC mp on md.MasterID = mp.MasterID and md.DeptID = mp.DeptID
where md.MasterId is null
*/

/*
SELECT     PartNumber, 
Weight,
Alloy
FROM         DetailPart
*/

/*
select IsSample, count(*)
from "MASTER"
group by IsSample

select IsReleased, count(*)
from "MASTER"
group by IsReleased
*/