Sample ASP Page :
<%@ Language=VBScript %>
<% Response.ContentType = "application/vnd.ms-excel"
dim iVendor,iFactory,iInspectionEvent,iSeason,iYear
dim iLevel1,iLevel2,iLevel3,iLevel4,iLevel5,iLevel6
dim levels,ilevelNo
iLevel1 = Request("Level1")
iLevel2 = Request("Level2")
iLevel3 = Request("Level3")
iLevel4 = Request("Level4")
iLevel5 = Request("Level5")
iLevel6 = Request("Level6")
ifacPass = Request("facPass")
ivenPass = Request("venPass")
ilevelNo = Request("levelNo")
iVendor = Request("vendor")
iFactory = Request("factory")
'iProcessGroup = Request("processGroup")
iInspectionEvent = Request("inspectionEvent")
iSeason = Request("season")
iYear = Request("year")
'iStyleStatus = Request("styleStatus")
'Response.Write(iVendor)
dim queryText
dim queryText1
dim queryText2
dim queryText3
dim queryText4
Dim rstParam1
Dim rstParam2
Dim rstParam4
Dim rstParam5
Dim rstParam6
Dim rstParam7
Dim rstParam8
queryText= "SELECT COUNT(PO_ID) AS NoOfStylePO,PO_ID ,StyleID,Sty_Code,Sty_Name,CASE WHEN PO='Default'THEN'-' else PO END as PO,SUM(DH_Iteration) AS NoOfIterations,SUM(InspectionQty) AS InspectionQty "
queryText1=",SUM(TotalDefects) AS TotalDefects, SUM(DHU) AS DHU, SUM(Critical) AS Critical, SUM(Major) AS Major, "
queryText1=queryText1 & "SUM(Minor) AS Minor, SUM(StylePOqty) AS StylePOqty FROM (SELECT C.DH_ID, C.StyleID, C.PO_ID, C.EventID, "
queryText1=queryText1 & "C.InspectionQty, C.TotalDefects, C.DHU, C.Critical,C.Major, C.Minor, C.ProcessGroupID, C.PO, C.DH_Iteration,C.FactoryID, C.VendorID, "
queryText1=queryText1 & "C.Year, C.SeasonID, C.CWB_ID, dbo.SS_Style.Sty_Code, dbo.SS_Style.Sty_Name, SS_Mtr_Events.Eve_EventName, "
queryText1=queryText1 & "dbo.SS_Mtr_ProcessGroup.PG_ProcessGroupName, dbo.SS_Mtr_BusinessPartner.BP_Name,dbo.SS_Mtr_Factory.Fac_Factory_Name, "
queryText1=queryText1 & "dbo.SS_Mtr_Season.SM_SeasonName, dbo.SS_Mtr_ColorWiseBreakup.CWB_Color, SS_Mtr_Events.Eve_EventType AS EventType, "
queryText1=queryText1 & "dbo.SS_Style.Sty_Status,C.StylePOqty FROM dbo.SS_Mtr_Factory RIGHT OUTER JOIN "
queryText1=queryText1 & "(SELECT dbo.SS_Tra_DefectHeader.DH_Iteration,dbo.SS_Tra_DefectHeader.DH_ID, dbo.SS_Tra_DefectHeader.DH_StyleID AS StyleID,dbo.SS_Tra_DefectHeader.DH_PoID AS PO_ID, "
queryText1=queryText1 & "dbo.SS_Tra_DefectHeader.DH_EventID AS EventID, dbo.SS_Tra_DefectHeader.DH_InspectionAmount AS InspectionQty, "
queryText1=queryText1 & "dbo.SS_Tra_DefectHeader.DH_TotalDefects AS TotalDefects,dbo.SS_Tra_DefectHeader.DH_DHU AS DHU, A.Critical, A.Major, A.Minor, "
queryText1=queryText1 & "SS_Mtr_Events_1.Eve_ProcessId AS ProcessGroupID, SS_PO_Style.Sty_PO_PONum AS PO,SS_PO_Style.Sty_PO_Factory_Id AS FactoryID, "
queryText1=queryText1 & "SS_PO_Style.Sty_PO_Vendor_Id AS VendorID, SS_PO_Style.Sty_PO_Season_Year AS Year,SS_PO_Style.Sty_PO_Season_Id AS SeasonID, "
queryText1=queryText1 & "dbo.SS_Tra_DefectHeader.DH_CwbID AS CWB_ID, SS_PO_Style.Sty_PO_TotQty AS StylePOqty FROM dbo.SS_Tra_DefectHeader LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_PO_Style AS SS_PO_Style ON dbo.SS_Tra_DefectHeader.DH_PoID = SS_PO_Style.Sty_PO_Id LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_Events AS SS_Mtr_Events_1 ON dbo.SS_Tra_DefectHeader.DH_EventID = SS_Mtr_Events_1.Eve_EventID LEFT OUTER JOIN "
queryText1=queryText1 & "(SELECT DD_ID, SUM(DD_Critical) AS Critical, SUM(DD_Major) AS Major, SUM(DD_Minor) AS Minor "
queryText1=queryText1 & "FROM dbo.SS_Tra_DefectDetails GROUP BY DD_ID) AS A ON dbo.SS_Tra_DefectHeader.DH_ID = A.DD_ID) AS C "
queryText1=queryText1 & "LEFT OUTER JOIN dbo.SS_Mtr_ColorWiseBreakup ON C.CWB_ID = dbo.SS_Mtr_ColorWiseBreakup.CWB_Id LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_Season ON C.SeasonID = dbo.SS_Mtr_Season.SM_SeasonID ON dbo.SS_Mtr_Factory.Fac_Id = C.FactoryID LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_BusinessPartner ON C.VendorID = dbo.SS_Mtr_BusinessPartner.BP_Id LEFT OUTER JOIN dbo.SS_Style ON C.StyleID = dbo.SS_Style.Sty_Id LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_ProcessGroup ON C.ProcessGroupID = dbo.SS_Mtr_ProcessGroup.PG_ProcessGroupID LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_Events AS SS_Mtr_Events ON C.EventID = SS_Mtr_Events.Eve_EventID "
queryText1=queryText1 & "WHERE(SS_Mtr_Events.Eve_EventType = 'Inspection') and SS_Style.sty_status='1') AS DHU GROUP BY StyleID,PO_ID,Sty_Code,Sty_Name,PO "
dim flag
flag=0
if iVendor<>"NULL" then
queryText = queryText&",VendorID, BP_Name "
queryText2=queryText1&" ,VendorID,BP_Name "
queryText4=" HAVING (VendorID IN ("&iVendor&")) "
queryText3=queryText&queryText2&queryText4
flag=1
end if
if iFactory<>"NULL" then
queryText = queryText&",FactoryID, Fac_Factory_Name "
queryText2= queryText2&",FactoryID,Fac_Factory_Name "
if flag=1 then
queryText4=queryText4&" AND ((FactoryID IN ("&iFactory&")or FactoryID is null)) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (FactoryID IN ("&iFactory&")or FactoryID is null) "
queryText3=queryText&queryText2&queryText4
end if
flag=1
end if
if iInspectionEvent<>"NULL" then
queryText =queryText&",EventID, Eve_EventName "
queryText2= queryText2&",EventID,Eve_EventName "
if flag=1 then
queryText4=queryText4&" AND (EventID IN ("&iInspectionEvent&")) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (EventID IN ("&iInspectionEvent&")) "
queryText3=queryText&queryText2&queryText4
end if
flag=1
end if
if iSeason<>"NULL" then
queryText =queryText&",SeasonID, SM_SeasonName "
queryText2= queryText2&",SeasonID,SM_SeasonName "
if flag=1 then
queryText4=queryText4&" AND (SeasonID IN ("&iSeason&")) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (SeasonID IN ("&iSeason&")) "
queryText3=queryText&queryText2&queryText4
end if
flag=1
end if
if iYear<>"NULL" then
queryText =queryText&",Year "
queryText2= queryText2&",Year "
if flag=1 then
queryText4=queryText4&" AND (Year IN ("&iYear&")) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (Year IN ("&iYear&")) "
queryText3=queryText&queryText2&queryText4
end if
end if
if flag=0 then
queryText3=queryText&queryText1
end if
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Dim getLevelNames
Dim levQry
levQry = "select HL_Level,HL_LableName from dbo.SS_Mtr_HierarchyLabel"
Dim getVendorNames
Dim venQry
venQry = "select BP_Id,BP_Name from dbo.SS_Mtr_BusinessPartner where BP_Id IN ("&iVendor&")"
Dim getFactoryNames
facQry ="select Fac_Id,Fac_Factory_Name from dbo.SS_Mtr_Factory where Fac_Id IN ("&iFactory&")"
Set cnnSimple = Server.CreateObject("ADODB.Connection")
' DSNLess
cnnSimple.Open "Provider=SQLOLEDB;Data Source=blrsrvdev010;" _
& "Initial Catalog=SimSource;User Id=sa;Password=simbus;" _
& "Connect Timeout=15;"
Set rstSimple = CreateObject("ADODB.Recordset")
Set getLevelNames= CreateObject("ADODB.Recordset")
Set getVendorNames= CreateObject("ADODB.Recordset")
Set getFactoryNames= CreateObject("ADODB.Recordset")
rstSimple.open queryText3,cnnSimple,3,1
getLevelNames.open levQry,cnnSimple,3,1
getVendorNames.open venQry,cnnSimple,3,1
getFactoryNames.open facQry,cnnSimple,3,1
'Set rstSimple = cnnSimple.Execute(queryText3)
'Response.Write(getVendorNames.recordcount)
'Response.Write(getVendorNames.Fields.Item(1))
if iLevel1<>"NULL" then
Set rstParam1 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel1)
end if
if iLevel2<>"NULL" then
Set rstParam2 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel2)
end if
if iLevel3<>"NULL" then
Set rstParam3 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel3)
end if
if iLevel4<>"NULL" then
Set rstParam4 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel4)
end if
if iLevel5<>"NULL" then
Set rstParam5 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel5)
end if
if iLevel6<>"NULL" then
Set rstParam6 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel6)
end if
if iSeason<>"NULL" then
Set rstParam7 = cnnSimple.Execute("select SM_SeasonName from SS_Mtr_Season where SM_SeasonID="&iSeason)
end if
if iInspectionEvent<>"NULL" then
Set rstParam8 = cnnSimple.Execute("select Eve_EventName from SS_Mtr_Events where Eve_EventID="&iInspectionEvent)
end if
%>
<p align="center" style="font-size:21px" ><B><U>DHU Detailed Report</U><B></p>
<B><U>Search Criteria</U><B><BR>
<%
'response.write(queryText3)
if iLevel1<>"NULL" then
iLevel1=rstParam1(0)
else
iLevel1="ALL"
end if
if iLevel2<>"NULL" then
iLevel2=rstParam2(0)
else
iLevel2="ALL"
end if
if iLevel3<>"NULL" then
iLevel3=rstParam3(0)
else
iLevel3="ALL"
end if
if iLevel4<>"NULL" then
iLevel4=rstParam4(0)
else
iLevel4="ALL"
end if
if iLevel5<>"NULL" then
iLevel5=rstParam5(0)
else
iLevel5="ALL"
end if
if iLevel6<>"NULL" then
iLevel6=rstParam6(0)
else
iLevel6="ALL"
end if
dim seas
seas = iSeason
if seas<>"NULL" then
seas=rstParam7(0)
else
seas = "ALL"
end if
dim yr
yr = iYear
if yr<>"NULL" then
yr=iYear
else
yr="ALL"
end if
dim inspEvt
inspEvt=iInspectionEvent
if inspEvt<>"NULL" then
inspEvt=rstParam8(0)
else
inspEvt="ALL"
end if
dim ven
ven=ivenPass
if ven<>"NULL" then
if getVendorNames.recordcount=1 then
ven = getVendorNames.Fields.Item(1)
else
ven=" Multiple"
end if
else
ven="ALL"
end if
dim fac
fac=ifacPass
if fac<>"NULL" then
if getFactoryNames.recordcount=1 then
fac = getFactoryNames.Fields.Item(1)
else
fac=" Multiple"
end if
else
fac="ALL"
end if
%>
<table width="22%" border="0">
<tr>
<% Dim j
For j=1 to ilevelNo %>
<td bgcolor="silver"><B><% Response.Write(getLevelNames.Fields.Item(1)) %></B></td>
<% getLevelNames.MoveNext %>
<% Next%>
</tr>
<tr>
<%
Dim i
Dim myLevel
For i=1 to ilevelNo
myLevel = i
Select Case myLevel %>
<%Case 1%>
<td><%=iLevel1%></td>
<%Case 2%>
<td><%=iLevel2%></td>
<%Case 3%>
<td><%=iLevel3%></td>
<%Case 4%>
<td><%=iLevel4%></td>
<%Case 5%>
<td><%=iLevel5%></td>
<%Case 6%>
<td><%=iLevel6%></td>
<% End Select %>
<% Next %>
</tr>
<tr></tr>
<tr>
<td bgcolor="silver"><B>Season</B></td>
<td>: <%Response.Write(seas)%></td>
<td bgcolor="silver"><B>Year</B></td>
<td>: <%Response.Write(yr)%></td>
</tr>
<tr>
<td bgcolor="silver"><B>Vendor</B></td>
<td>: <%Response.Write(ven)%></td>
<td bgcolor="silver"><B>Factory</B></td>
<td>: <%Response.Write(fac)%></td>
</tr>
<tr>
<td bgcolor="silver"><B>Event</B></td>
<td>: <%Response.Write(inspEvt)%></td>
</tr>
</table><BR>
<% if rstSimple.recordcount >0 then %>
<table border="1">
<tr>
<td align="center" bgcolor="silver" width="9%"><b>Style<b></td>
<td align="center" bgcolor="silver" width="9%"><b>PO<b></td>
<%if iSeason<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Season<b></td>
<%end if%>
<%if iYear<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Year<b></td>
<%end if%>
<%if iVendor<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Vendor<b></td>
<%end if%>
<%if iFactory<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Factory<b></td>
<%end if%>
<td align="center" bgcolor="silver" width="9%"><b>Style/PO Qty<b></td>
<td align="center" bgcolor="silver" width="9%"><b>No.Of Inspections<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Inpsection Qty<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Critical Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Major Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Minor Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Total Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>DHU(%)<b></td>
</tr>
<%
dim totaldefects
dim inspctnQty
dim DHU
dim rdDHU
Do While Not rstSimple.EOF
totaldefects = rstSimple.Fields("TotalDefects").Value
inspctnQty = rstSimple.Fields("InspectionQty").Value
DHU = (CDbl(totaldefects)*100)/CDbl(inspctnQty)
rdDHU = Round(DHU, 2)
'Response.Write(rdDHU)
%>
<tr>
<td><%= rstSimple.Fields("Sty_Code").Value %></td>
<td><%= rstSimple.Fields("PO").Value %></td>
<%if iSeason<>"NULL" then%>
<td><%= rstSimple.Fields("SM_SeasonName").Value %></td>
<%end if%>
<%if iYear<>"NULL" then%>
<td><%= rstSimple.Fields("Year").Value %></td>
<%end if%>
<%if iVendor<>"NULL" then%>
<td><%= rstSimple.Fields("BP_Name").Value %></td>
<%end if%>
<%if iFactory<>"NULL" then%>
<td><%= rstSimple.Fields("Fac_Factory_Name").Value %></td>
<%end if%>
<td><%= rstSimple.Fields("StylePOqty").Value %></td>
<td><%= rstSimple.Fields("NoOfIterations").Value %></td>
<td><%= inspctnQty %></td>
<td><%= rstSimple.Fields("Critical").Value %></td>
<td><%= rstSimple.Fields("Major").Value %></td>
<td><%= rstSimple.Fields("Minor").Value %></td>
<td><%= totaldefects %></td>
<td><%=rdDHU %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
rstSimple.Close
Set rstSimple = Nothing
cnnSimple.Close
Set cnnSimple = Nothing
%>
<% Else
Response.write("No Record found")
end if
%>
To call from JavaScript :
Write the following code under any event in JavaScript . The URL contains the parameter to be passed to ASP page.
window.open("http://blrsrvdev010/Report/dhuSummary.asp?vendor="+vendors+"&levelNo="+levels1.length+"&venPass="+venPass+"&facPass="+facPass+"&factory="+factories+"&inspectionEvent="+Events+"&season="+season+"&year="+year+"&Level1="+lev1+"&Level2="+lev2+"&Level3="+lev3+"&Level4="+lev4+"&Level5="+lev5+"&Level6="+lev6, '_self','width=200,height=100');
-- <%@ Language=VBScript %>
<% Response.ContentType = "application/vnd.ms-excel"
dim iVendor,iFactory,iInspectionEvent,iSeason,iYear
dim iLevel1,iLevel2,iLevel3,iLevel4,iLevel5,iLevel6
dim levels,ilevelNo
iLevel1 = Request("Level1")
iLevel2 = Request("Level2")
iLevel3 = Request("Level3")
iLevel4 = Request("Level4")
iLevel5 = Request("Level5")
iLevel6 = Request("Level6")
ifacPass = Request("facPass")
ivenPass = Request("venPass")
ilevelNo = Request("levelNo")
iVendor = Request("vendor")
iFactory = Request("factory")
'iProcessGroup = Request("processGroup")
iInspectionEvent = Request("inspectionEvent")
iSeason = Request("season")
iYear = Request("year")
'iStyleStatus = Request("styleStatus")
'Response.Write(iVendor)
dim queryText
dim queryText1
dim queryText2
dim queryText3
dim queryText4
Dim rstParam1
Dim rstParam2
Dim rstParam4
Dim rstParam5
Dim rstParam6
Dim rstParam7
Dim rstParam8
queryText= "SELECT COUNT(PO_ID) AS NoOfStylePO,PO_ID ,StyleID,Sty_Code,Sty_Name,CASE WHEN PO='Default'THEN'-' else PO END as PO,SUM(DH_Iteration) AS NoOfIterations,SUM(InspectionQty) AS InspectionQty "
queryText1=",SUM(TotalDefects) AS TotalDefects, SUM(DHU) AS DHU, SUM(Critical) AS Critical, SUM(Major) AS Major, "
queryText1=queryText1 & "SUM(Minor) AS Minor, SUM(StylePOqty) AS StylePOqty FROM (SELECT C.DH_ID, C.StyleID, C.PO_ID, C.EventID, "
queryText1=queryText1 & "C.InspectionQty, C.TotalDefects, C.DHU, C.Critical,C.Major, C.Minor, C.ProcessGroupID, C.PO, C.DH_Iteration,C.FactoryID, C.VendorID, "
queryText1=queryText1 & "C.Year, C.SeasonID, C.CWB_ID, dbo.SS_Style.Sty_Code, dbo.SS_Style.Sty_Name, SS_Mtr_Events.Eve_EventName, "
queryText1=queryText1 & "dbo.SS_Mtr_ProcessGroup.PG_ProcessGroupName, dbo.SS_Mtr_BusinessPartner.BP_Name,dbo.SS_Mtr_Factory.Fac_Factory_Name, "
queryText1=queryText1 & "dbo.SS_Mtr_Season.SM_SeasonName, dbo.SS_Mtr_ColorWiseBreakup.CWB_Color, SS_Mtr_Events.Eve_EventType AS EventType, "
queryText1=queryText1 & "dbo.SS_Style.Sty_Status,C.StylePOqty FROM dbo.SS_Mtr_Factory RIGHT OUTER JOIN "
queryText1=queryText1 & "(SELECT dbo.SS_Tra_DefectHeader.DH_Iteration,dbo.SS_Tra_DefectHeader.DH_ID, dbo.SS_Tra_DefectHeader.DH_StyleID AS StyleID,dbo.SS_Tra_DefectHeader.DH_PoID AS PO_ID, "
queryText1=queryText1 & "dbo.SS_Tra_DefectHeader.DH_EventID AS EventID, dbo.SS_Tra_DefectHeader.DH_InspectionAmount AS InspectionQty, "
queryText1=queryText1 & "dbo.SS_Tra_DefectHeader.DH_TotalDefects AS TotalDefects,dbo.SS_Tra_DefectHeader.DH_DHU AS DHU, A.Critical, A.Major, A.Minor, "
queryText1=queryText1 & "SS_Mtr_Events_1.Eve_ProcessId AS ProcessGroupID, SS_PO_Style.Sty_PO_PONum AS PO,SS_PO_Style.Sty_PO_Factory_Id AS FactoryID, "
queryText1=queryText1 & "SS_PO_Style.Sty_PO_Vendor_Id AS VendorID, SS_PO_Style.Sty_PO_Season_Year AS Year,SS_PO_Style.Sty_PO_Season_Id AS SeasonID, "
queryText1=queryText1 & "dbo.SS_Tra_DefectHeader.DH_CwbID AS CWB_ID, SS_PO_Style.Sty_PO_TotQty AS StylePOqty FROM dbo.SS_Tra_DefectHeader LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_PO_Style AS SS_PO_Style ON dbo.SS_Tra_DefectHeader.DH_PoID = SS_PO_Style.Sty_PO_Id LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_Events AS SS_Mtr_Events_1 ON dbo.SS_Tra_DefectHeader.DH_EventID = SS_Mtr_Events_1.Eve_EventID LEFT OUTER JOIN "
queryText1=queryText1 & "(SELECT DD_ID, SUM(DD_Critical) AS Critical, SUM(DD_Major) AS Major, SUM(DD_Minor) AS Minor "
queryText1=queryText1 & "FROM dbo.SS_Tra_DefectDetails GROUP BY DD_ID) AS A ON dbo.SS_Tra_DefectHeader.DH_ID = A.DD_ID) AS C "
queryText1=queryText1 & "LEFT OUTER JOIN dbo.SS_Mtr_ColorWiseBreakup ON C.CWB_ID = dbo.SS_Mtr_ColorWiseBreakup.CWB_Id LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_Season ON C.SeasonID = dbo.SS_Mtr_Season.SM_SeasonID ON dbo.SS_Mtr_Factory.Fac_Id = C.FactoryID LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_BusinessPartner ON C.VendorID = dbo.SS_Mtr_BusinessPartner.BP_Id LEFT OUTER JOIN dbo.SS_Style ON C.StyleID = dbo.SS_Style.Sty_Id LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_ProcessGroup ON C.ProcessGroupID = dbo.SS_Mtr_ProcessGroup.PG_ProcessGroupID LEFT OUTER JOIN "
queryText1=queryText1 & "dbo.SS_Mtr_Events AS SS_Mtr_Events ON C.EventID = SS_Mtr_Events.Eve_EventID "
queryText1=queryText1 & "WHERE(SS_Mtr_Events.Eve_EventType = 'Inspection') and SS_Style.sty_status='1') AS DHU GROUP BY StyleID,PO_ID,Sty_Code,Sty_Name,PO "
dim flag
flag=0
if iVendor<>"NULL" then
queryText = queryText&",VendorID, BP_Name "
queryText2=queryText1&" ,VendorID,BP_Name "
queryText4=" HAVING (VendorID IN ("&iVendor&")) "
queryText3=queryText&queryText2&queryText4
flag=1
end if
if iFactory<>"NULL" then
queryText = queryText&",FactoryID, Fac_Factory_Name "
queryText2= queryText2&",FactoryID,Fac_Factory_Name "
if flag=1 then
queryText4=queryText4&" AND ((FactoryID IN ("&iFactory&")or FactoryID is null)) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (FactoryID IN ("&iFactory&")or FactoryID is null) "
queryText3=queryText&queryText2&queryText4
end if
flag=1
end if
if iInspectionEvent<>"NULL" then
queryText =queryText&",EventID, Eve_EventName "
queryText2= queryText2&",EventID,Eve_EventName "
if flag=1 then
queryText4=queryText4&" AND (EventID IN ("&iInspectionEvent&")) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (EventID IN ("&iInspectionEvent&")) "
queryText3=queryText&queryText2&queryText4
end if
flag=1
end if
if iSeason<>"NULL" then
queryText =queryText&",SeasonID, SM_SeasonName "
queryText2= queryText2&",SeasonID,SM_SeasonName "
if flag=1 then
queryText4=queryText4&" AND (SeasonID IN ("&iSeason&")) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (SeasonID IN ("&iSeason&")) "
queryText3=queryText&queryText2&queryText4
end if
flag=1
end if
if iYear<>"NULL" then
queryText =queryText&",Year "
queryText2= queryText2&",Year "
if flag=1 then
queryText4=queryText4&" AND (Year IN ("&iYear&")) "
queryText3=queryText&queryText2&queryText4
else
queryText4=" HAVING (Year IN ("&iYear&")) "
queryText3=queryText&queryText2&queryText4
end if
end if
if flag=0 then
queryText3=queryText&queryText1
end if
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Dim getLevelNames
Dim levQry
levQry = "select HL_Level,HL_LableName from dbo.SS_Mtr_HierarchyLabel"
Dim getVendorNames
Dim venQry
venQry = "select BP_Id,BP_Name from dbo.SS_Mtr_BusinessPartner where BP_Id IN ("&iVendor&")"
Dim getFactoryNames
facQry ="select Fac_Id,Fac_Factory_Name from dbo.SS_Mtr_Factory where Fac_Id IN ("&iFactory&")"
Set cnnSimple = Server.CreateObject("ADODB.Connection")
' DSNLess
cnnSimple.Open "Provider=SQLOLEDB;Data Source=blrsrvdev010;" _
& "Initial Catalog=SimSource;User Id=sa;Password=simbus;" _
& "Connect Timeout=15;"
Set rstSimple = CreateObject("ADODB.Recordset")
Set getLevelNames= CreateObject("ADODB.Recordset")
Set getVendorNames= CreateObject("ADODB.Recordset")
Set getFactoryNames= CreateObject("ADODB.Recordset")
rstSimple.open queryText3,cnnSimple,3,1
getLevelNames.open levQry,cnnSimple,3,1
getVendorNames.open venQry,cnnSimple,3,1
getFactoryNames.open facQry,cnnSimple,3,1
'Set rstSimple = cnnSimple.Execute(queryText3)
'Response.Write(getVendorNames.recordcount)
'Response.Write(getVendorNames.Fields.Item(1))
if iLevel1<>"NULL" then
Set rstParam1 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel1)
end if
if iLevel2<>"NULL" then
Set rstParam2 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel2)
end if
if iLevel3<>"NULL" then
Set rstParam3 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel3)
end if
if iLevel4<>"NULL" then
Set rstParam4 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel4)
end if
if iLevel5<>"NULL" then
Set rstParam5 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel5)
end if
if iLevel6<>"NULL" then
Set rstParam6 = cnnSimple.Execute("select HE_Value from SS_Mtr_Hierarchy_Entity where HE_Id="&iLevel6)
end if
if iSeason<>"NULL" then
Set rstParam7 = cnnSimple.Execute("select SM_SeasonName from SS_Mtr_Season where SM_SeasonID="&iSeason)
end if
if iInspectionEvent<>"NULL" then
Set rstParam8 = cnnSimple.Execute("select Eve_EventName from SS_Mtr_Events where Eve_EventID="&iInspectionEvent)
end if
%>
<p align="center" style="font-size:21px" ><B><U>DHU Detailed Report</U><B></p>
<B><U>Search Criteria</U><B><BR>
<%
'response.write(queryText3)
if iLevel1<>"NULL" then
iLevel1=rstParam1(0)
else
iLevel1="ALL"
end if
if iLevel2<>"NULL" then
iLevel2=rstParam2(0)
else
iLevel2="ALL"
end if
if iLevel3<>"NULL" then
iLevel3=rstParam3(0)
else
iLevel3="ALL"
end if
if iLevel4<>"NULL" then
iLevel4=rstParam4(0)
else
iLevel4="ALL"
end if
if iLevel5<>"NULL" then
iLevel5=rstParam5(0)
else
iLevel5="ALL"
end if
if iLevel6<>"NULL" then
iLevel6=rstParam6(0)
else
iLevel6="ALL"
end if
dim seas
seas = iSeason
if seas<>"NULL" then
seas=rstParam7(0)
else
seas = "ALL"
end if
dim yr
yr = iYear
if yr<>"NULL" then
yr=iYear
else
yr="ALL"
end if
dim inspEvt
inspEvt=iInspectionEvent
if inspEvt<>"NULL" then
inspEvt=rstParam8(0)
else
inspEvt="ALL"
end if
dim ven
ven=ivenPass
if ven<>"NULL" then
if getVendorNames.recordcount=1 then
ven = getVendorNames.Fields.Item(1)
else
ven=" Multiple"
end if
else
ven="ALL"
end if
dim fac
fac=ifacPass
if fac<>"NULL" then
if getFactoryNames.recordcount=1 then
fac = getFactoryNames.Fields.Item(1)
else
fac=" Multiple"
end if
else
fac="ALL"
end if
%>
<table width="22%" border="0">
<tr>
<% Dim j
For j=1 to ilevelNo %>
<td bgcolor="silver"><B><% Response.Write(getLevelNames.Fields.Item(1)) %></B></td>
<% getLevelNames.MoveNext %>
<% Next%>
</tr>
<tr>
<%
Dim i
Dim myLevel
For i=1 to ilevelNo
myLevel = i
Select Case myLevel %>
<%Case 1%>
<td><%=iLevel1%></td>
<%Case 2%>
<td><%=iLevel2%></td>
<%Case 3%>
<td><%=iLevel3%></td>
<%Case 4%>
<td><%=iLevel4%></td>
<%Case 5%>
<td><%=iLevel5%></td>
<%Case 6%>
<td><%=iLevel6%></td>
<% End Select %>
<% Next %>
</tr>
<tr></tr>
<tr>
<td bgcolor="silver"><B>Season</B></td>
<td>: <%Response.Write(seas)%></td>
<td bgcolor="silver"><B>Year</B></td>
<td>: <%Response.Write(yr)%></td>
</tr>
<tr>
<td bgcolor="silver"><B>Vendor</B></td>
<td>: <%Response.Write(ven)%></td>
<td bgcolor="silver"><B>Factory</B></td>
<td>: <%Response.Write(fac)%></td>
</tr>
<tr>
<td bgcolor="silver"><B>Event</B></td>
<td>: <%Response.Write(inspEvt)%></td>
</tr>
</table><BR>
<% if rstSimple.recordcount >0 then %>
<table border="1">
<tr>
<td align="center" bgcolor="silver" width="9%"><b>Style<b></td>
<td align="center" bgcolor="silver" width="9%"><b>PO<b></td>
<%if iSeason<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Season<b></td>
<%end if%>
<%if iYear<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Year<b></td>
<%end if%>
<%if iVendor<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Vendor<b></td>
<%end if%>
<%if iFactory<>"NULL" then%>
<td align="center" bgcolor="silver" width="9%"><b>Factory<b></td>
<%end if%>
<td align="center" bgcolor="silver" width="9%"><b>Style/PO Qty<b></td>
<td align="center" bgcolor="silver" width="9%"><b>No.Of Inspections<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Inpsection Qty<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Critical Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Major Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Minor Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>Total Defects<b></td>
<td align="center" bgcolor="silver" width="9%"><b>DHU(%)<b></td>
</tr>
<%
dim totaldefects
dim inspctnQty
dim DHU
dim rdDHU
Do While Not rstSimple.EOF
totaldefects = rstSimple.Fields("TotalDefects").Value
inspctnQty = rstSimple.Fields("InspectionQty").Value
DHU = (CDbl(totaldefects)*100)/CDbl(inspctnQty)
rdDHU = Round(DHU, 2)
'Response.Write(rdDHU)
%>
<tr>
<td><%= rstSimple.Fields("Sty_Code").Value %></td>
<td><%= rstSimple.Fields("PO").Value %></td>
<%if iSeason<>"NULL" then%>
<td><%= rstSimple.Fields("SM_SeasonName").Value %></td>
<%end if%>
<%if iYear<>"NULL" then%>
<td><%= rstSimple.Fields("Year").Value %></td>
<%end if%>
<%if iVendor<>"NULL" then%>
<td><%= rstSimple.Fields("BP_Name").Value %></td>
<%end if%>
<%if iFactory<>"NULL" then%>
<td><%= rstSimple.Fields("Fac_Factory_Name").Value %></td>
<%end if%>
<td><%= rstSimple.Fields("StylePOqty").Value %></td>
<td><%= rstSimple.Fields("NoOfIterations").Value %></td>
<td><%= inspctnQty %></td>
<td><%= rstSimple.Fields("Critical").Value %></td>
<td><%= rstSimple.Fields("Major").Value %></td>
<td><%= rstSimple.Fields("Minor").Value %></td>
<td><%= totaldefects %></td>
<td><%=rdDHU %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
rstSimple.Close
Set rstSimple = Nothing
cnnSimple.Close
Set cnnSimple = Nothing
%>
<% Else
Response.write("No Record found")
end if
%>
To call from JavaScript :
Write the following code under any event in JavaScript . The URL contains the parameter to be passed to ASP page.
window.open("http://blrsrvdev010/Report/dhuSummary.asp?vendor="+vendors+"&levelNo="+levels1.length+"&venPass="+venPass+"&facPass="+facPass+"&factory="+factories+"&inspectionEvent="+Events+"&season="+season+"&year="+year+"&Level1="+lev1+"&Level2="+lev2+"&Level3="+lev3+"&Level4="+lev4+"&Level5="+lev5+"&Level6="+lev6, '_self','width=200,height=100');
Thanks and regards,
Vivek TiwariMobile : 8095924581
No comments:
Post a Comment