Imports Microsoft.VisualBasic

Public Class Audits
  Public sno As Integer
  Public InstanceID As String
  Public ReferenceNo As String
  Public Version As String
  Public FTime As String
  Public StartTime As String
  Public SignTime As String
  Public UserID As String
  Public UserName As String
  Public ApproverID As String
  Public ApproverName As String
  Public Actions As String
  Public Diff As String
  Public vendor As String
  Public dept As String
End Class

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports Newtonsoft.Json
Imports CommonModule
Imports Audits ' define一個Json Array所需的 class

<WebService(Namespace:="http://xxx/webservices")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class qryfinished
  Inherits System.Web.Services.WebService

  <WebMethod()> _
  Public Function ToJSONString(ByVal uid As String) As String
    Dim strSQL As String
    Dim connWorkFlow1 As System.Data.SqlClient.SqlConnection = Nothing
    Dim sqlCommand As System.Data.SqlClient.SqlCommand, sqlCommand1 As System.Data.SqlClient.SqlCommand
    Dim drWorkflow As System.Data.SqlClient.SqlDataReader = Nothing, drWorkflow1 As System.Data.SqlClient.SqlDataReader = Nothing
    Dim cmdWMS As System.Data.SqlClient.SqlCommand
    Dim drWMS As System.Data.SqlClient.SqlDataReader = Nothing
    Dim iCount As Integer = 1
    Dim WF_UserID As String = uid
    Dim starttime As String = "", signtime As String = Now().ToString
    Dim oAudits() As Audits ' define Json Array
    Dim pb_no As String = ""

    ReDim oAudits(0)

    Try

      strSQL = "select distinct SLIP_NO from CELL_TMP_TB where status='C' order by SLIP_NO"

      If connWMS Is Nothing Then
        Connection(False, True, False)
      End If

      If connWorkFlow Is Nothing Then
        Connection(False, False, True)
      End If

      If connWMS.State <> Data.ConnectionState.Open Then
        connWMS.Open()
      End If

      cmdWMS = New System.Data.SqlClient.SqlCommand(strSQL, connWMS)

      drWMS = cmdWMS.ExecuteReader()

      Do While drWMS.Read()
        pb_no = drWMS.GetString(drWMS.GetOrdinal("SLIP_NO"))
        strSQL = "select top 1 * from Audits NOLOCK where ReferenceNo='" + pb_no + "'  order by StartTime"

        If connWorkFlow.State <> Data.ConnectionState.Open Then
          connWorkFlow.Open()
        End If

        sqlCommand = New System.Data.SqlClient.SqlCommand(strSQL, connWorkFlow)

        drWorkflow = sqlCommand.ExecuteReader()
        If drWorkflow.Read() Then
          ReDim Preserve oAudits(iCount - 1)

          starttime = drWorkflow.GetDateTime(drWorkflow.GetOrdinal("StartTime")).ToString()

          oAudits(iCount - 1) = New Audits()
          oAudits(iCount - 1).sno = iCount
          oAudits(iCount - 1).UserID = WF_UserID
          oAudits(iCount - 1).StartTime = starttime
          oAudits(iCount - 1).UserName = drWorkflow.GetString(drWorkflow.GetOrdinal("UserName"))

          GetERP(oAudits(iCount - 1), pb_no)

          strSQL = "select top 1 * from Audits NOLOCK where ReferenceNo='" + pb_no + "' order by SignTime desc"

          connWorkFlow1 = getSqlConnection("cnnWorkFlow")
          sqlCommand1 = New System.Data.SqlClient.SqlCommand(strSQL, connWorkFlow1)
          connWorkFlow1.Open()

          drWorkflow1 = sqlCommand1.ExecuteReader()
          If drWorkflow1.Read() Then
            oAudits(iCount - 1).InstanceID = drWorkflow1.GetString(drWorkflow1.GetOrdinal("InstanceID"))
            oAudits(iCount - 1).ReferenceNo = drWorkflow1.GetString(drWorkflow1.GetOrdinal("ReferenceNo"))
            signtime = drWorkflow1.GetDateTime(drWorkflow1.GetOrdinal("SignTime")).ToString()
            oAudits(iCount - 1).SignTime = signtime
            oAudits(iCount - 1).Actions = drWorkflow1.GetString(drWorkflow1.GetOrdinal("Actions"))
          End If
          drWorkflow1.Close()
          connWorkFlow1.Close()

          oAudits(iCount - 1).Diff = getSignTime(CDate(starttime), CDate(signtime))
        End If
        iCount += 1
        drWorkflow.Close()
      Loop

      drWMS.Close()
    Catch ex As System.Data.Odbc.OdbcException
      My.ErrorHandling.ExceptionHandler(ex, Resources.Settings.smtphost, Resources.Settings.From, Resources.Settings.CopyTo)
    Finally
      If Not drWorkflow Is Nothing Then drWorkflow = Nothing
      If Not drWorkflow1 Is Nothing Then drWorkflow1 = Nothing
      If Not drWMS Is Nothing Then drWMS = Nothing
      If Not connWorkFlow1 Is Nothing Then connWorkFl ow1 = Nothing
    End Try 
    ' 將 Json Array 透過 JavaScriptConvert.SerializeObject 轉換成序列化的String資料以利Internet傳輸
    Return "{""totalCount"":" + CStr(iCount - 1) + ",""data"":" + JavaScriptConvert.SerializeObject(oAudits) + "}"
  End Function

End Class

Partial Class qryfinished2
    Inherits System.Web.UI.Page
  Public JSONString As String

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myJSONObject As New qryfinished ' 呼叫 Web Service
    Dim uid As String = Request.QueryString("uid")

    JSONString = myJSONObject.ToJSONString(uid)
  End Sub
End Class

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="qryfinished2.aspx.vb" Inherits="qryfinished2" %>
<%=JSONString%>

/*
* Ext JS Library 2.0
* Copyright(c) 2006-2007, Ext JS, LLC.
* licensing@extjs.com
*
* http://extjs.com/license
*/

Ext.onReady(function(){

    Ext.QuickTips.init();

    // turn on validation errors beside the field globally
    Ext.form.Field.prototype.msgTarget = 'side';

    // create the Data Store
    // 透過qryfinished2.aspx去讀取qryfinished.asmx這個Web Service抓出已簽核完成的資料
    // 作為 Grid 的資料來源
    var store = new Ext.data.JsonStore({
        url: 'http://xxx/DateCode/qryfinished2.aspx?uid='+WF_UserID,
        root: 'data',
        id: 'InstanceID',
        fields: [
              'sno','ReferenceNo', 'UserName', 'Actions', 'StartTime', 'SignTime', 'Diff', 'dept', 'vendor'
        ]
    });

    // 呼叫 define 在 HTML 中的 Javascript 讓第一個欄位可以點擊後開啟資料頁
    function renderReferenceNo(value, p, record){
            return String.format(
                            '<a href="javascript:void(0);" onclick="doSign(\'{0}\',\'{1}\',\'{2}\',\'worklist\');" title="點此開啟入庫單">{2}</a>',
                            record.id, WF_UserID, value);
    }

    // define 一個 FormPanel 上面擺 Grid
    var fs = new Ext.FormPanel({
        collapsible:true,
        frame: true,
        title:'Date Code 逾期簽核-'+dcTitle,
        labelAlign: 'right',
        labelWidth: 85,
        width:800,
        waitMsgTarget: true,
        tbar: [''],
        // 顯示在FormPanel上的元件
        items: [new Ext.form.FieldSet({
            title: '查詢條件',
            collapsible: true,
            animCollapse: true,
            autoHeight: true,
            defaultType: 'textfield'/*,
            contentEl: 'pb_master'*/
        }),new Ext.grid.GridPanel({
            store: store,
            frame: true,
            //autoHeight: true,
            autoScroll: true,
            loadMask: true,
            sm: new Ext.grid.RowSelectionModel({singleSelect:true}),
            stripeRows: true,
            columns: [
                { header: '', dataIndex: 'sno', width: 30, align: 'right' },
                { id: 'ReferenceNo', header: '入庫單號', dataIndex: 'ReferenceNo', width: 80, renderer: renderReferenceNo, align: 'center', sortable: true },
                { header: '部門', dataIndex: 'dept', width: 50, align: 'center', sortable: true },               
                { header: '申請者', dataIndex: 'UserName', width: 50, align: 'center', sortable: true },
                { header: '廠商', dataIndex: 'vendor', width: 80, align: 'center', sortable: true },               
                { header: '簽核結果', dataIndex: 'Actions', width: 70, align: 'center', sortable: true },
                { header: '申請時間', dataIndex: 'StartTime', width: 150, align: 'center', sortable: true },
                { header: '完成時間', dataIndex: 'SignTime', width: 150, align: 'center', sortable: true    },
                { header: '簽核歷時', dataIndex: 'Diff', width: 150, sortable: true }
            ],
            width: 788,
            height: 400
        })]
    });

    fs.render('form-ct');  // 將 FormPanel render 在 "form-ct" 這個 div tag 中

    store.load();  // 讀取資料

    fs.getTopToolbar().add('->', {
            text: '',
            tooltip: '重新整理',
            iconCls: 'breload',
            handler: function(){       
                    location.reload();
            }
    });
});

<%@ Page Language="VB" MasterPageFile="~/qryMasterPage.master" AutoEventWireup="false" CodeFile="dc_finished.aspx.vb" Inherits="dc_finished" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
  <script type="text/javascript">
  var WF_UserID="<%=WF_UserID %>";
  var dcTitle="已完成簽核查詢";
  </script>
  <script src="pgfinished.js" type="text/javascript"></script>
</asp:Content>

<%@ Master CodeFile="qryMasterPage.master.vb" Inherits="qryMasterPage" Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<!--<meta content="30" http-equiv="refresh" />-->
<head id="Head1" runat="server">
  <title>Date Code逾期簽核系統</title>
  <link href="js/ext-2.0/resources/css/ext-all.css" rel="stylesheet" type="text/css" />
  <link href="js/ext-2.0/resources/css/xtheme-gray.css" rel="stylesheet" type="text/css" />
  <script src="js/ext-2.0/adapter/ext/ext-base.js" type="text/javascript"></script>

  <script src="js/ext-2.0/ext-all.js" type="text/javascript"></script>

  <link href="menus.css" rel="stylesheet" type="text/css" />
  <link href="forms.css" rel="stylesheet" type="text/css" />
  <!-- Common Styles for the examples -->
  <link href="js/ext-2.0/examples/examples.css" rel="stylesheet" type="text/css" />
  <style type="text/css">
    html, body {
        font:normal 12px verdana;
        margin:0;
        padding:0;
        border:0 none;
        overflow:hidden;
        height:100%;
    }
</style>

  <script type="text/javascript">
function doSign(unid,uid,pb_no,s){
  s=(typeof(s)=="undefined") ? s="":s;
  if (s=="finished")
    window.open("dc_read.aspx?unid="+unid+"&stage="+s+"&pb_no="+pb_no+"&uid="+uid,"DC","");
  else
    window.open("dc_check.aspx?unid="+unid+"&stage="+s+"&pb_no="+pb_no+"&uid="+uid,"DC","");
}

  </script>

</head>
<body class="x-aero">

  <script src="js/ext-2.0/examples/examples.js" type="text/javascript"></script>

  <br />
  <form id="form1" class="x-form">
    <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
    </asp:ContentPlaceHolder>
  </form>
  <table align="center" border="0" cellpadding="0" cellspacing="0">
    <tr>
      <td>
        <div id="form-ct">
          <div id="pb_master">
          </div>
        </div>
      </td>
    </tr>
  </table> 
</body>
</html>

 <script type="text/javascript">
  var oauto=new AjaxService();
  oauto.callback=autoCompleteResponse;  

  
  function autoCompleteField(cust_code) {
   event.cancelBubble=true;
    var key=window.event.keyCode;
   switch (key){
     case 9,13,27: // Tab, Enter, Esc 鍵
      return;
   }
  
   var querystring="cust_code="+encodeURI(cust_code);
   var url = "/Authority/autoComplete.jsp?"+querystring;
   oauto.loadXMLDoc(url);
   return false;
  }
   
  function autoCompleteResponse() {
   // only if req shows "loaded"
   if (oauto.xhttp.readyState == 4) {
    // only if "OK"
    if (oauto.xhttp.status == 200) {            
     $('autocomplete').innerHTML = oauto.xhttp.responseText;
     $('autocomplete').style.display="block";
     $('autocomplete').style.width=$('cust_code').offsetWidth;
     var point = fGetXY($('cust_code'));
     $('autocomplete').style.top=point.y+$('cust_code').offsetHeight+15;
     $('autocomplete').style.left=point.x+10;
    } else {
     alert(errormsg + oauto.xhttp.statusText);
    }
   }
  }    

  function Point(iX, iY){
   this.x = iX;
   this.y = iY;
  }  
  
  function fGetXY(aTag){
   var oTmp = aTag;
   var pt = new Point(0,0);
   do {
    pt.x += oTmp.offsetLeft;
    pt.y += oTmp.offsetTop;
    oTmp = oTmp.offsetParent;
   } while(oTmp.tagName!="BODY");
   return pt;
  }  
  
  function ChangeSelect(){
   event.cancelBubble=true;
    var key=window.event.keyCode;
   switch (key){
     case 9,13,27: // Tab, Enter, Esc 鍵
      $('autocomplete').style.display="none";
   }
  }
  
  function setValue(v){
    $('cust_code').value=v;
   $('autocomplete').style.display="none";
   query(1,v);
  }
 </script>

<input autocomplete="off" type="text" name="cust_code" id="cust_code" size="20" onkeyup="if (frmQuery.chkAutoComplete.checked) autoCompleteField(this.value);" onkeydown="return ChangeSelect();" /> <input type="checkbox" value="1" name="chkAutoComplete" checked="checked" />啟用自動完成

String cust_code=new String(request.getParameter("cust_code").getBytes("ISO-8859-1"),"UTF8");

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

while (rs.next()){
  out.print("<a href='#' onclick='setValue(\""+rs.getString("occ01").trim()+"\")' style='cursor:default'>"+rs.getString("occ01").trim()+" ("+rs.getString("occ02").trim()+")</a><br />");
}