ตอนนี้ข้อมูลแสดงเกือบครบแล้วครับขาดแค่ lot field เดียวไม่ไม่แสดง โดย query2 จะต้องส่งข้อมูลเข้า query1 และ binddata ออกมาครับ
Code (VB.NET)
Protected Sub PopUpDatabind(ByVal sender As Object, ByVal e As EventArgs)
If GridView1.Rows.Count > 0 Then
Dim row As GridViewRow = CType(CType(sender, LinkButton).Parent.Parent, GridViewRow)
Dim ds As New DataSet
Dim dsPopUp As New DataSet
ds = GetDataIss(txtwkfrom.Text.ToUpper.Trim, txtwkto.Text.ToUpper.Trim, txtItemfrom.Text.ToUpper.Trim, txtItemto.Text.ToUpper.Trim, dpkfrom.Text, dpkto.Text, ddltypedate.SelectedValue, ddlstatus.SelectedValue, "", dpkreqdate.Text)
If GridView1.Rows.Count > 0 Then
pl_wk = ds.Tables(0).Rows.Item(0).Item("pl_wk")
pl_date2 = ds.Tables(0).Rows.Item(0).Item("pl_date2")
If pl_date2 <> "" Then
pl_date2 = CDate(pl_date2).ToString("yyyyMMdd")
End If
pl_type_date = ds.Tables(0).Rows.Item(0).Item("pl_type_date")
If pl_type_date = "Release Date" Then
pl_type_date = "R"
ElseIf pl_type_date = "Plan Date" Then
pl_type_date = "P"
End If
pl_doc_no = ds.Tables(0).Rows.Item(0).Item("pl_doc_no")
pld_material = ds.Tables(0).Rows.Item(0).Item("Pld_material_pop")
GetMFGLot(pld_material, "", "", "")
dsPopUp = GetDataIssPopup(pl_wk, pl_date2, pl_type_date, pl_doc_no)
End If
mp1.Show()
GridView2.DataSource = GetMFGLot(pld_material, "", "", "")
GridView2.DataSource = dsPopUp
GridView2.DataBind()
'mp1.Show()
'GridView2.DataSource = dsPopUp
'GridView2.DataBind()
End If
End Sub
Public Shared Function GetDataIssPopup(ByVal wk As String, _
ByVal datef As String, _
ByVal datetype As String, _
ByVal doc As String) As DataSet
Try
strSql = New StringBuilder
strSql.AppendLine("select (l.user_name || ' ' || l.user_surname) pl_request_by,")
strSql.AppendLine(" to_char(h.pl_request_date,'dd/MM/yyyy') as pl_request_date,")
strSql.AppendLine(" d.pld_material,d.pld_req_qty,d.pld_req_um,")
strSql.AppendLine(" pt.pt_desc1,")
strSql.AppendLine(" ' ' lot ")
strSql.AppendLine("from fpcl_picklist_header h inner join fpcl_picklist_detail d on h.pl_wk=d.pld_wk")
strSql.AppendLine(" and h.pl_date=d.pld_date")
strSql.AppendLine(" and h.pl_doc_no=d.pld_doc_no")
strSql.AppendLine(" inner join nap_user_login l on h.pl_request_by=l.login_id")
strSql.AppendLine(" inner join PT_MSTR@pcttprod pt on upper(pt.pt_part)=d.pld_material")
strSql.AppendLine(" and upper(pt.pt_domain)='2000'")
strSql.AppendLine("where 1=1")
strSql.AppendLine(" and h.pl_wk='" & wk & "'")
strSql.AppendLine(" and to_char(h.pl_date,'yyyyMMdd')='" & datef & "'")
strSql.AppendLine(" and h.pl_type_date='" & datetype & "'")
strSql.AppendLine(" and h.pl_doc_no='" & doc & "'")
strSql.AppendLine("order by d.pld_material")
Dim StrQuery As String = ""
StrQuery = strSql.ToString
Return GetDataOracle(strSql.ToString, "GetDataIss", constrFPCOlacle)
Catch ex As Exception
Throw ex
End Try
End Function
query data2 Code (VB.NET)
Public Shared Function GetMFGLot(ByVal part As String, ByVal floc As String, ByVal tloc As String, ByVal domain As String) As string
Dim ds As New DataSet
Dim Getlot As New DataSet
Try
strSql = New StringBuilder
strSql.AppendLine(" SELECT LD_DET.LD_REF,LD_DET.LD_LOC,LD_DET.LD_LOT,LD_DET.LD_QTY_OH,")
strSql.AppendLine(" (CASE WHEN (to_char(LD_DET.LD_DATE,'yyyyMMdd') >= '20130415') then (CASE WHEN (LD_DET.LD_REF IS NULL OR LD_DET.LD_REF =' ') then LD_DET.LD_LOT else LD_DET.LD_REF end) else LD_DET.LD_LOT end) SERIAL")
strSql.AppendLine(" FROM QAD.LD_DET")
strSql.AppendLine(" WHERE LD_DET.LD_QTY_OH > 0 ")
strSql.AppendLine(" AND UPPER(LD_DET.LD_PART) = '" & part.ToUpper & "'")
'strSql.AppendLine(" AND LD_DET.LD_LOC >= '" & floc & "'")
'strSql.AppendLine(" AND LD_DET.LD_LOC <= '" & tloc & "'")
strSql.AppendLine(" AND LD_DET.LD_LOC >= '21' ")
strSql.AppendLine(" AND LD_DET.LD_LOC <= '28z' ")
strSql.AppendLine(" AND UPPER(LD_DET.Ld_Domain) = '2000'")
strSql.AppendLine(" AND UPPER(LD_DET.LD_STATUS) in ('GOOD')")
strSql.AppendLine(" AND length(LD_DET.LD_LOC) = 8")
strSql.AppendLine(" ORDER BY ")
strSql.AppendLine(" (CASE WHEN (to_char(LD_DET.LD_DATE,'yyyyMMdd') >= '20130415') then (CASE WHEN (LD_DET.LD_REF IS NULL OR LD_DET.LD_REF =' ') then LD_DET.LD_LOT else LD_DET.LD_REF end) else LD_DET.LD_LOT end)")
Dim StrQuery As String = ""
StrQuery = strSql.ToString
Dim lot As String = ""
Dim dstlot As New DataSet
dstlot = (GetDataOracle(strSql.ToString, "GetMFGLot", constrForcastOlacle))
If dstlot.Tables(0).Rows.Count > 0 Then
Dim serial As String = ""
For Each dr As DataRow In dstlot.Tables(0).Rows
serial = ""
If IsDBNull(dr.Item("SERIAL")) = False Then
serial = dr.Item("SERIAL")
End If
If lot = "" Then
lot = serial & " / " & IIf(IsDBNull(dr.Item("LD_LOC")) = False, dr.Item("LD_LOC"), "") & " (" & Format(CDec(IIf(IsDBNull(dr.Item("LD_QTY_OH")) = False, dr.Item("LD_QTY_OH"), 0)), "#,##0.00") & ")"
Else
lot = lot & ", " & serial & " / " & IIf(IsDBNull(dr.Item("LD_LOC")) = False, dr.Item("LD_LOC"), "") & " (" & Format(CDec(IIf(IsDBNull(dr.Item("LD_QTY_OH")) = False, dr.Item("LD_QTY_OH"), 0)), "#,##0.00") & ")"
End If
Next
End If
Return lot
Catch ex As Exception
Throw ex
End Try
End Function
Tag : .NET, Oracle, VS 2008 (.NET 3.x), VS 2010 (.NET 4.x)
Dim varBestway As String = "faceHE"
Dim strSQL = <Suparuck>
--Remark
Select * From Customers Where CustomerName = '<%= varBestway %>'
--Remark
</Suparuck>
Dim strSQLFormated As String = strSQL.Value
DECLARE @budgetYear VARCHAR(max);
SET @budgetYear = (
SELECT TOP (1) BUDGET.year
FROM BDG_TYPE
INNER JOIN BUDGET ON BDG_TYPE.BDGCODE = BUDGET.type
WHERE BUDGET.BudgetOpen = 'O'
);
IF @budgetYear IS NOT NULL
BEGIN
SELECT TblPOStatus.StatusCode,
TblPOStatus.StatusName,
COUNT(MS_PO.PO_NO) AS Items,
LEFT(PO_NO, 2) AS BudgetYear,
SUM(TOTAL_COST) AS Total
FROM MS_PO
INNER JOIN TblPOStatus ON TblPOStatus.StatusCode = MS_PO.STATUS
GROUP BY TblPOStatus.StatusCode,
TblPOStatus.StatusName,
LEFT(PO_NO, 2)
HAVING LEFT(PO_NO, 2) = RIGHT(@budgetYear, 2)
ORDER BY TblPOStatus.StatusCode;
END
--ELSE
--BEGIN
-- SELECT TblPOStatus.StatusCode,
-- TblPOStatus.StatusName,
-- COUNT(MS_PO.PO_NO) AS Items,
-- LEFT(PO_NO, 2) AS BudgetYear,
-- SUM(TOTAL_COST) AS Total
-- FROM MS_PO
-- INNER JOIN TblPOStatus ON TblPOStatus.StatusCode = MS_PO.STATUS
-- GROUP BY TblPOStatus.StatusCode,
-- TblPOStatus.StatusName,
-- LEFT(PO_NO, 2)
-- HAVING LEFT(PO_NO, 2) = RIGHT(@budgetYear, 2)
-- ORDER BY TblPOStatus.StatusCode;
--END
<HttpGet>
<Route("java/myServlet/DashBoard/GetPOStatus_dashBoard")>
Public Function GetPOStatus_dashBoard() As IHttpActionResult
Dim strSQL0 =
<Suparuck>
DECLARE @budgetYear VARCHAR(max);
SET @budgetYear = (
SELECT TOP (1) BUDGET.year
FROM BDG_TYPE
INNER JOIN BUDGET ON BDG_TYPE.BDGCODE = BUDGET.type
WHERE BUDGET.BudgetOpen = 'O'
);
--SELECT @budgetYear;
IF @budgetYear IS NOT NULL
BEGIN
SELECT TblPOStatus.StatusCode,
TblPOStatus.StatusName,
COUNT(MS_PO.PO_NO) AS Items,
SUM(TOTAL_COST) AS Total
FROM MS_PO
INNER JOIN TblPOStatus ON TblPOStatus.StatusCode = MS_PO.STATUS
GROUP BY TblPOStatus.StatusCode,
TblPOStatus.StatusName,
LEFT(PO_NO, 2)
HAVING LEFT(PO_NO, 2) = RIGHT(@budgetYear, 2)
ORDER BY TblPOStatus.StatusCode;
END
--ELSE
--BEGIN
-- SELECT TblPOStatus.StatusCode,
-- TblPOStatus.StatusName,
-- COUNT(MS_PO.PO_NO) AS Items,
-- LEFT(PO_NO, 2) AS BudgetYear,
-- SUM(TOTAL_COST) AS Total
-- FROM MS_PO
-- INNER JOIN TblPOStatus ON TblPOStatus.StatusCode = MS_PO.STATUS
-- GROUP BY TblPOStatus.StatusCode,
-- TblPOStatus.StatusName,
-- LEFT(PO_NO, 2)
-- HAVING LEFT(PO_NO, 2) = RIGHT(@budgetYear, 2)
-- ORDER BY TblPOStatus.StatusCode;
--END
</Suparuck>
Dim dt = WL_Database.DbHelper.Query(strSQL0.Value)
If dt.Rows.Count = 0 Then 'ไม่ได้เปิดงบประมาณ BUDGET.BudgetOpen
'TODO ห่าเหวอะไรก็ว่ากันไป
End If
Return Ok(dt.AsEnumerable().ToList())
End Function
DECLARE @budgetYear VARCHAR(max);
SET @budgetYear = (
SELECT TOP (1) BUDGET.year
FROM BDG_TYPE
INNER JOIN BUDGET ON BDG_TYPE.BDGCODE = BUDGET.type
WHERE BUDGET.BudgetOpen = 'O'
);
IF @budgetYear IS NOT NULL
BEGIN
SELECT TblPOStatus.StatusCode,
TblPOStatus.StatusName,
FORMAT(COUNT(MS_PO.PO_NO), '#,0) AS Items,
FORMAT(SUM(TOTAL_COST), '#,0.00') AS Total
FROM MS_PO
INNER JOIN TblPOStatus ON TblPOStatus.StatusCode = MS_PO.STATUS
GROUP BY TblPOStatus.StatusCode,
TblPOStatus.StatusName,
LEFT(PO_NO, 2)
HAVING LEFT(PO_NO, 2) = RIGHT(@budgetYear, 2)
ORDER BY TblPOStatus.StatusCode;
END
Load balance : Server 04
