|
|
EXCEL3.SQL=SELECT DISTINCT p1.puid, p3.pcreation_date, CASE WHEN p4.pitem_revision_id = 'A' AND p7.pname IN ('ML8_SXFB', 'ML8_JGFB') THEN p4.puid ELSE p4.puid END AS puid FROM PITEM p1 INNER JOIN PWORKSPACEOBJECT p2 ON p1.puid = p2.puid AND p2.pobject_type = 'ML8_Part' AND ( SELECT count(puid) FROM PPSOCCURRENCE WHERE rchild_itemu = p2.puid ) = 0 INNER JOIN PPOM_APPLICATION_OBJECT p3 ON p1.puid = p3.puid AND p3.pcreation_date < ( SELECT CONVERT( varchar(70), dateadd(MONTH, -2, CAST(GETDATE() AS datetime)), 21 )) INNER JOIN PITEMREVISION p4 ON p1.puid = p4.ritems_tagu AND p4.pitem_revision_id =( SELECT max(pitem_revision_id) AS item_revision_id FROM PITEMREVISION WHERE ritems_tagu = p1.puid GROUP BY ritems_tagu ) INNER JOIN PML8_PART p5 ON p1.puid = p5.puid AND p5.pml8_Status IN (?, ?, ?) INNER JOIN PRELEASE_STATUS_LIST p6 ON p4.puid = p6.puid INNER JOIN PRELEASESTATUS p7 ON p6.pvalu_0 = p7.puid
|
|
|
#EXCEL4.SQL=SELECT DISTINCT ritems_tagu FROM ( SELECT T7.pname, isnull(T9.pml8_Status, '') pml8_Status, T1.ritems_tagu, T4.puid, CASE WHEN T7.pname IN('ML8_Replaced', 'ML8_Freeze') AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others')THEN 'ok' WHEN (pml8_Status = ? OR T7.pname = 'ML8_Replaced') AND T8.pobject_type = 'ML8_Part' THEN 'ok' ELSE 'no' END OTHERS FROM AAA T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.ritems_tagu INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid INNER JOIN PRELEASE_STATUS_LIST T6 ON T5.puid = T6.puid INNER JOIN PRELEASESTATUS T7 ON T6.pvalu_0 = T7.puid INNER JOIN PWORKSPACEOBJECT T8 ON T5.ritems_tagu = T8.puid AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others', 'ML8_Part') LEFT JOIN PML8_PART T9 ON T5.ritems_tagu = T9.puid) TTT1 WHERE OTHERS = 'ok' AND ritems_tagu NOT IN( SELECT DISTINCT ritems_tagu FROM ( SELECT T7.pname, isnull(T9.pml8_Status, '') pml8_Status, T1.ritems_tagu, T4.puid, CASE WHEN T7.pname IN('ML8_Replaced', 'ML8_Freeze') AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others')THEN 'ok' WHEN (pml8_Status = ? OR T7.pname = 'ML8_Replaced') AND T8.pobject_type = 'ML8_Part' THEN 'ok' ELSE 'no' END OTHERS FROM AAA T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.ritems_tagu INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid INNER JOIN PRELEASE_STATUS_LIST T6 ON T5.puid = T6.puid INNER JOIN PRELEASESTATUS T7 ON T6.pvalu_0 = T7.puid INNER JOIN PWORKSPACEOBJECT T8 ON T5.ritems_tagu = T8.puid AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others', 'ML8_Part') LEFT JOIN PML8_PART T9 ON T5.ritems_tagu = T9.puid) sss WHERE OTHERS = 'no') GROUP BY ritems_tagu
|
|
|
EXCEL4.SQL=SELECT DISTINCT ritems_tagu FROM ( SELECT T7.pname, isnull(T9.pml8_Status, '') pml8_Status, T1.ritems_tagu, T4.puid, CASE WHEN T7.pname IN('ML8_Replaced', 'ML8_Freeze') AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others')THEN 'ok' WHEN (pml8_Status = ? OR pml8_Status = ? OR T7.pname = 'ML8_Replaced') AND T8.pobject_type = 'ML8_Part' THEN 'ok' ELSE 'no' END OTHERS FROM AAA T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.ritems_tagu INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid INNER JOIN PRELEASE_STATUS_LIST T6 ON T5.puid = T6.puid INNER JOIN PRELEASESTATUS T7 ON T6.pvalu_0 = T7.puid INNER JOIN PWORKSPACEOBJECT T8 ON T5.ritems_tagu = T8.puid AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others', 'ML8_Part') LEFT JOIN PML8_PART T9 ON T5.ritems_tagu = T9.puid) TTT1 WHERE OTHERS = 'ok' AND ritems_tagu NOT IN( SELECT DISTINCT ritems_tagu FROM ( SELECT T7.pname, isnull(T9.pml8_Status, '') pml8_Status, T1.ritems_tagu, T4.puid, CASE WHEN T7.pname IN('ML8_Replaced', 'ML8_Freeze') AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others')THEN 'ok' WHEN (pml8_Status = ? OR pml8_Status = ? OR T7.pname = 'ML8_Replaced') AND T8.pobject_type = 'ML8_Part' THEN 'ok' ELSE 'no' END OTHERS FROM AAA T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.ritems_tagu INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid INNER JOIN PRELEASE_STATUS_LIST T6 ON T5.puid = T6.puid INNER JOIN PRELEASESTATUS T7 ON T6.pvalu_0 = T7.puid INNER JOIN PWORKSPACEOBJECT T8 ON T5.ritems_tagu = T8.puid AND T8.pobject_type IN('ML8_Refrigerator', 'ML8_Washing', 'ML8_Cold', 'ML8_Others', 'ML8_Part') LEFT JOIN PML8_PART T9 ON T5.ritems_tagu = T9.puid) sss WHERE OTHERS = 'no') GROUP BY ritems_tagu
|
|
|
#EXCEL5.SQL=WITH TEMP AS( SELECT T5.ritems_tagu AS parent_uid, T5.puid AS rev_uid FROM ( SELECT * FROM PITEM WHERE puid = ? ) T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.puid INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid UNION ALL SELECT T5.ritems_tagu AS parent_uid, T5.puid AS rev_uid FROM TEMP A INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = A.parent_uid INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid) SELECT DISTINCT * FROM TEMP
|
|
|
EXCEL5.SQL= WITH TEMP AS(SELECT T5.ritems_tagu AS parent_uid, T5.puid AS rev_uid FROM ( SELECT * FROM PITEM WHERE puid = ?) T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.puid INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid and (not exists(select * from PRELEASE_STATUS_LIST where puid =T5.puid) or exists (select * from PRELEASE_STATUS_LIST where puid=T5.puid and pvalu_0 in(select puid from PRELEASESTATUS where pname not like 'ML8_Replaced' )))UNION ALL SELECT T5.ritems_tagu AS parent_uid, T5.puid AS rev_uid FROM TEMP A INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = A.parent_uid INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid and (not exists(select * from PRELEASE_STATUS_LIST where puid =T5.puid) or exists (select * from PRELEASE_STATUS_LIST where puid=T5.puid and pvalu_0 in(select puid from PRELEASESTATUS where pname not like 'ML8_Replaced' )))) SELECT DISTINCT * FROM TEMP
|
|
|
EXCEL6.SQL=WITH TEMP AS( SELECT T5.ritems_tagu AS parent_uid, T5.puid AS rev_uid FROM ( SELECT * FROM PITEM WHERE puid = ? ) T1 INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = T1.puid INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid INNER JOIN PRELEASE_STATUS_LIST T6 ON T4.puid = T6.puid INNER JOIN PRELEASESTATUS T7 ON T6.pvalu_0 = T7.puid AND T7.pname not like 'ML8_Replaced' UNION ALL SELECT T5.ritems_tagu AS parent_uid, T5.puid AS rev_uid FROM TEMP A INNER JOIN PPSOCCURRENCE T2 ON T2.rchild_itemu = A.parent_uid INNER JOIN POM_BACKPOINTER T3 ON T3.from_uid = T2.puid AND T3.to_class = '934' INNER JOIN PSTRUCTURE_REVISIONS T4 ON T4.pvalu_0 = T3.to_uid INNER JOIN PITEMREVISION T5 ON T4.puid = T5.puid INNER JOIN PRELEASE_STATUS_LIST T6 ON T4.puid = T6.puid INNER JOIN PRELEASESTATUS T7 ON T6.pvalu_0 = T7.puid AND T7.pname not like 'ML8_Replaced') SELECT DISTINCT * FROM TEMP
|
|
|
#<23><>ͼ<EFBFBD><CDBC><EFBFBD><EFBFBD>ԭ<EFBFBD><D4AD><EFBFBD><EFBFBD><EFBFBD>ɿ<EFBFBD><C9BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD><D3A6><EFBFBD><EFBFBD><EFBFBD>ĸ߶<C4B8>
|
|
|
#<23><>ͼ<EFBFBD><CDBC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>x/y (HEIGHT=WIDTH*y/x)
|
|
|
IMAGE.WIDTH=1
|
|
|
IMAGE.HEIGHT=1 |
