แล้วนี้ code ทั้งหมดครับ เป็น asp.net ครับ
ขอความช่วยเหลือด้วยนะครับ
Code (VB.NET)
Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports System.Data.SqlClient
Imports CrystalDecisions.Shared
Imports System.Data.OleDb
Partial Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim dtAdapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim strConnString As String
Dim strSQL As New StringBuilder
strConnString = "Server=IT-PC\SQL2005;UID=sa;PASSWORD=taweesak;database=TESTB1;Max Pool Size=400;Connect Timeout=600;"
strSQL.Append("SELECT '1-2'as 'Level',T1.docnum,T1.U_JobNo,T1.U_ProcessDate as 'Level1_ProcessDate',T1.DueDate as 'Level1_DueDate', T0.itemname as 'Level1_ProductName',")
strSQL.Append("T1.ItemCode as 'Level1_ProductCode',T1.[PlannedQty]as 'Level1_PlanQty',")
strSQL.Append("T18.[OnHand] as 'Level1_Onhand', T18.[IsCommited]as 'Level1_Commited', T18.[OnOrder]as 'Level1_Ordered', T18.[Counted] as 'Level1_Available',")
strSQL.Append("T2.ItemCode as 'Level2_Code',T2.[BaseQty] as 'Level2_BaseQty', T2.[PlannedQty] as 'Level2_PlanQty',")
strSQL.Append("T19.[OnHand] as 'Level2_Onhand', T19.[IsCommited]as 'Level2_Commited', T19.[OnOrder]as 'Level2_Ordered', T19.[Counted] as 'Level2_Available',")
strSQL.Append("'0' as 'Level3_Code','0' as 'Level3_BaseQty','0' as 'Level3_PlanQty',")
strSQL.Append("'0' as 'Level3_Onhand', '0' as 'Level3_Commited', '0' as 'Level3_Ordered', '0' as 'Level3_Available',")
strSQL.Append("'0' as 'Level4_Code','0' as 'Level4_BaseQty','0' as 'Level4_PlanQty',")
strSQL.Append("'0' as 'Level4_Onhand', '0'as 'Level4_Commited', '0'as 'Level4_Ordered', '0' as 'Level4_Available'")
strSQL.Append(" FROM OWOR T1 INNER JOIN WOR1 T2 ON T1.DocEntry = T2.DocEntry ")
strSQL.Append(" INNER join OITM T0 ON T0.ItemCode = T1.ItemCode and T0.[TreeType] = 'P'") '--Level 1
strSQL.Append(" INNER JOIN OITM T13 On T13.ItemCode= T2.ItemCode and T13.[TreeType] = 'P'") '--Level 2
strSQL.Append(" INNER JOIN OITW T18 ON T1.itemcode = T18.itemcode and T18.[WhsCode] = 'W02'") '--FG-Factory--Level 1
strSQL.Append(" INNER JOIN OITW T19 ON T2.itemcode = T19.itemcode and T19.[WhsCode] = 'W02'") '--FG-Factory--Level 2
strSQL.Append("where T1.U_jobno = '" & Me.DDL1.Text & "'")
strSQL.Append(" Union ")
strSQL.Append(" SELECT '3'as 'Level',T1.docnum,T1.U_JobNo,T1.U_ProcessDate as 'Level1_ProcessDate',T1.DueDate as 'Level1_DueDate', T0.itemname as 'Level1_ProductName',")
strSQL.Append("T1.ItemCode as 'Level1_ProductCode',T1.[PlannedQty]as 'Level1_PlanQty',")
strSQL.Append("T18.[OnHand] as 'Level1_Onhand', T18.[IsCommited]as 'Level1_Commited', T18.[OnOrder]as 'Level1_Ordered', T18.[Counted] as 'Level1_Available',")
strSQL.Append("T2.ItemCode as 'Level2_Code',T2.[BaseQty] as 'Level2_BaseQty', T2.[PlannedQty] as 'Level2_PlanQty',")
strSQL.Append("T19.[OnHand] as 'Level2_Onhand', T19.[IsCommited]as 'Level2_Commited', T19.[OnOrder]as 'Level2_Ordered', T19.[Counted] as 'Level2_Available',")
strSQL.Append("T4.Code as 'Level3_Code',T4.[Quantity] as 'Level3_BaseQty',T4.[Quantity]*T1.[PlannedQty] as 'Level3_PlanQty',")
strSQL.Append("T20.[OnHand] as 'Level3_Onhand', T20.[IsCommited]as 'Level3_Commited', T20.[OnOrder]as 'Level3_Ordered', T20.[Counted] as 'Level3_Available',")
strSQL.Append("'0' as 'Level4_Code','0' as 'Level4_BaseQty','0' as 'Level4_PlanQty',")
strSQL.Append("'0' as 'Level4_Onhand', '0'as 'Level4_Commited', '0'as 'Level4_Ordered', '0' as 'Level4_Available'")
strSQL.Append("FROM OWOR T1 INNER JOIN WOR1 T2 ON T1.DocEntry = T2.DocEntry ")
strSQL.Append(" INNER join OITM T0 ON T0.ItemCode = T1.ItemCode and T0.[TreeType] = 'P'") '--Level 1
strSQL.Append(" INNER JOIN OITM T13 On T13.ItemCode= T2.ItemCode and T13.[TreeType] = 'P'") '--Level 2
strSQL.Append(" INNER JOIN OITW T18 ON T1.itemcode = T18.itemcode and T18.[WhsCode] = 'W02'") '--FG-Factory--Level 1
strSQL.Append(" INNER JOIN OITW T19 ON T2.itemcode = T19.itemcode and T19.[WhsCode] = 'W02'") '--FG-Factory--Level 2
strSQL.Append(" INNER JOIN OITT T3 On T3.Code = T2.ItemCode") ' --Level 2
strSQL.Append(" INNER JOIN ITT1 T4 On T3.Code = T4.Father") ' --Level 3
strSQL.Append(" INNER JOIN OITM T14 On T14.ItemCode= T4.Code and T14.[TreeType] = 'P'") ' --Level 3
strSQL.Append(" INNER JOIN OITW T20 ON T4.code = T20.itemcode and T20.[WhsCode] = 'W01'") ' --RM-Factory--Level 3
strSQL.Append("where T1.U_jobno = '" & Me.DDL1.Text & "'")
strSQL.Append(" Union ")
strSQL.Append(" SELECT '4'as 'Level',T1.docnum,T1.U_JobNo,T1.U_ProcessDate as 'Level1_ProcessDate',T1.DueDate as 'Level1_DueDate', T0.itemname as 'Level1_ProductName',")
strSQL.Append("T1.ItemCode as 'Level1_ProductCode',T1.[PlannedQty]as 'Level1_PlanQty',")
strSQL.Append("T18.[OnHand] as 'Level1_Onhand', T18.[IsCommited]as 'Level1_Commited', T18.[OnOrder]as 'Level1_Ordered', T18.[Counted] as 'Level1_Available',")
strSQL.Append("T2.ItemCode as 'Level2_Code',T2.[BaseQty] as 'Level2_BaseQty', T2.[PlannedQty] as 'Level2_PlanQty',")
strSQL.Append("T19.[OnHand] as 'Level2_Onhand', T19.[IsCommited]as 'Level2_Commited', T19.[OnOrder]as 'Level2_Ordered', T19.[Counted] as 'Level2_Available',")
strSQL.Append("T4.Code as 'Level3_Code',T4.[Quantity] as 'Level3_BaseQty',T4.[Quantity]*T1.[PlannedQty] as 'Level3_PlanQty',")
strSQL.Append("T20.[OnHand] as 'Level3_Onhand', T20.[IsCommited]as 'Level3_Commited', T20.[OnOrder]as 'Level3_Ordered', T20.[Counted] as 'Level3_Available',")
strSQL.Append("T16.Code as 'Level4_Code',T16.[Quantity] as 'Level4_BaseQty',T16.[Quantity]*T1.[PlannedQty] as 'Level4_PlanQty',")
strSQL.Append("T21.[OnHand] as 'Level4_Onhand', T21.[IsCommited]as 'Level4_Commited', T21.[OnOrder]as 'Level4_Ordered', T21.[Counted] as 'Level4_Available'")
strSQL.Append("FROM OWOR T1 INNER JOIN WOR1 T2 ON T1.DocEntry = T2.DocEntry ")
strSQL.Append(" INNER join OITM T0 ON T0.ItemCode = T1.ItemCode and T0.[TreeType] = 'P'") '--Level 1
strSQL.Append(" INNER JOIN OITM T13 On T13.ItemCode= T2.ItemCode and T13.[TreeType] = 'P'") '--Level 2
strSQL.Append(" INNER JOIN OITW T18 ON T1.itemcode = T18.itemcode and T18.[WhsCode] = 'W02'") ' --FG-Factory--Level 1
strSQL.Append(" INNER JOIN OITW T19 ON T2.itemcode = T19.itemcode and T19.[WhsCode] = 'W02'") ' --FG-Factory--Level 2
'--Level 3
strSQL.Append(" INNER JOIN OITT T3 On T3.Code = T2.ItemCode") ' --Level 2
strSQL.Append(" INNER JOIN ITT1 T4 On T3.Code = T4.Father") ' --Level 3
strSQL.Append(" INNER JOIN OITM T14 On T14.ItemCode= T4.Code and T14.[TreeType] = 'P'") ' --Level 3
strSQL.Append(" INNER JOIN OITW T20 ON T4.code = T20.itemcode and T20.[WhsCode] = 'W01'") ' --RM-Factory--Level 3
'--Level 4
strSQL.Append(" INNER JOIN OITT T15 On T15.Code = T4.Code") ' --Level 4
strSQL.Append(" INNER JOIN ITT1 T16 On T15.Code = T16.Father") ' --Level 5
strSQL.Append(" INNER JOIN OITM T17 On T17.ItemCode= T16.Code and T17.[TreeType] = 'P'") ' --Level 5
strSQL.Append(" INNER JOIN OITW T21 ON T16.code = T21.itemcode and T21.[WhsCode] = 'W01'") '--RM-Factory--Level 4
strSQL.Append("where T1.U_jobno = '" & Me.DDL1.Text & "'")
objConn.ConnectionString = strConnString
With objCmd
.Connection = objConn
.CommandText = strSQL.ToString()
.CommandType = CommandType.Text
End With
dtAdapter.SelectCommand = objCmd
dtAdapter.Fill(ds, "myDataReport")
dt = ds.Tables(0)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Dim rpt As New ReportDocument()
rpt.Load(Server.MapPath("CrystalReport1.rpt"))
rpt.SetDataSource(dt)
Me.CrystalReportViewer1.ReportSource = rpt
'Dim TL As TableLogOnInfo =
End Sub
End Class
Tag : ASP, Ms SQL Server 2005, Crystal Report, Web (ASP.NET), VS 2008 (.NET 3.x)