Windows LiveWindows Live™
 
 
James's profileJames の BlogPhotosBlogListsMore Tools Help

Blog
    3/19/2008

    extjs: Grid paging & Asp.Net 資料分頁技術

    花了兩天的時間，找了資料分頁的相關技術資料 當DataGrid遇見100萬筆資料
    再將 extjs grid paging 的 sample 好好看了一遍並測試
    分頁展示的部份終於完成了

    在進入程式說明之前，我們先來看一下整體運作的架構
    首先我們讓User去查詢 po_finished.aspx 將已完成簽核的單據給撈出來
    並以分頁的方式來呈現，當然Ajax及分頁的處理都交給 pgfinished.js
    這個以 extjs library所撰寫的 javascript

    image

    由於之前提到我們是以 JsonStore 來擷取後端資料並以Json String的方式傳輸到前端
    所以我們定義了一個 qryfinished2.aspx 來當作 pgfinished.js 跟 qryfinished.asmx 這個 Web Service 中間的橋樑

    資料分頁的部份，除了前端操作介面傳遞 Page Number 與 Page Size 外
    我們在資料庫的部份是以 Store Procedure 來接收前端傳過來的這兩個參數值

    依據這樣的架構，我們就可以很簡單的享有資料分頁的功能

    接下來我們進入程式說明的部份

    qryMasterPage.master

    <%@ 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="300" http-equiv="refresh" />
    <head id="Head1" runat="server">
      <title>採購簽核系統</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,po_no,version,s){
      s=(typeof(s)=="undefined") ? s="":s;
      if (s=="finished")
        window.open("po_read.aspx?unid="+unid+"&isApproval=0&stage="+s+"&po_no="+po_no+"&version="+version+"&uid="+uid,"PO","");
      else
        window.open("po_check.aspx?unid="+unid+"&stage="+s+"&po_no="+po_no+"&version="+version+"&uid="+uid,"PO","");
    }

      </script>

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

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

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

    po_finished.aspx
    因為有多個畫面都共用一個 Master Page，所以將個別使用的部份寫在
    ContentPlaceHolder 裡面

    <%@ Page AutoEventWireup="false" CodeFile="po_finished.aspx.vb" Inherits="po_finished"
      Language="VB" MasterPageFile="~/qryMasterPage.master" %>

    <asp:Content ID="Content1" runat="Server" ContentPlaceHolderID="ContentPlaceHolder1">

      <script type="text/javascript">
      var WF_UserID="<%=WF_UserID %>";
      var poTitle="已完成簽核查詢";
      </script>

      <script src="combos.js" type="text/javascript"></script>

      <link href="combos.css" rel="stylesheet" type="text/css" />

      <script src="pgfinished.js" type="text/javascript"></script> <!-- extjs Library -->

      <div id="pb_master">
        <form id="qryFinished" class="x-form">
          <table align="center" border="0" cellpadding="0" cellspacing="2" width="100%">
            <tr>
              <td>
                採購單號：</td>
              <td>
                <div>
                  <input id="po_no" size="20" type="text" />
                </div>
              </td>
              <td>
                料號：</td>
              <td>
                <div>
                  <input id="part_no" size="20" type="text" />
                </div>
              </td>
              <td>
                供應商：</td>
              <td>
                <div>
                  <input id="vendor" size="20" type="text" />
                </div>
              </td>
            </tr>
            <tr>
              <td>
                部門：
              </td>
              <td>
                <div>
                  <input id="cboDept" size="20" type="text" />
                </div>
              </td>
              <td>
                產品線：
              </td>
              <td>
                <div>
                  <input id="cboBrand" size="20" type="text" />
                </div>
              </td>
              <td>
                PM：</td>
              <td>
                <div>
                  <input id="cboPM" size="20" type="text" />
                </div>
              </td>
            </tr>
            <tr>
              <td>
                採購日期：</td>
              <td>
                <div>
                  <input id="pod1" size="20" type="text" /></div>
              </td>
              <td>
                ~</td>
              <td>
                <div>
                  <input id="pod2" size="20" type="text" />
                </div>
              </td>
              <td>
                &nbsp;
              </td>
              <td>
                &nbsp;
              </td>
            </tr>
            <tr>
              <td>
                URD：</td>
              <td>
                <div>
                  <input id="urd1" size="20" type="text" /></div>
              </td>
              <td>
                ~</td>
              <td>
                <div>
                  <input id="urd2" size="20" type="text" />
                </div>
              </td>
              <td>
                &nbsp;
              </td>
              <td>
                <div id="btnQuery">
                </div>
              </td>
            </tr>
            <tr>
              <td>
                VSD：</td>
              <td>
                <div>
                  <input id="vsd1" size="20" type="text" /></div>
              </td>
              <td>
                ~</td>
              <td>
                <div>
                  <input id="vsd2" size="20" type="text" />
                </div>
              </td>
              <td>
                &nbsp;
              </td>
              <td>
                &nbsp;
              </td>
            </tr>
          </table>
        </form>
      </div>
    </asp:Content>

    pgfinished.js

    /*
    * 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
        var store = new Ext.data.JsonStore({
            url: 'http://xxx/PO/qryfinished2.aspx?uid='+WF_UserID,
            root: 'data',
            totalProperty: 'totalCount',
            id: 'ReferenceNo',
            fields: [
                  'sno','ReferenceNo', 'Version', 'UserName', 'Actions', 'StartTime', 'SignTime', 'Diff'
            ]
        });                                                                        

        // pluggable renders
        function renderReferenceNo(value, p, record){
                return String.format(
                                '<a href="javascript:void(0);" onclick="doSign(\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'finished\');" title="點此開啟採購單">{2}</a>',
                                record.id, WF_UserID, value, record.data['Version']);
        }
        function renderDiff(value, p, record){
                var dd, hh, mm, ss;
                var t;

                t=record.data.SignTime-record.data.StartTime;
                dd = t.getDay();
                hh = t.getHours() - dd * 24
                mm = t.getMinutes() - dd * 24 * 60 - hh * 60
                ss = t - dd * 24 * 60 * 60 - hh * 60 * 60 - mm * 60
                return String.format('{0}天又{1}小時{2}分{3}秒', dd, hh, mm, ss);
        }

        var fs = new Ext.FormPanel({
            id: 'frmFinished',
            collapsible:true,
            frame: true,
            title:'採購簽核-'+poTitle,
            labelAlign: 'right',
            labelWidth: 85,
            width:800,
            waitMsgTarget: true,
            tbar: [''],
            items: [new Ext.form.FieldSet({
                title: '查詢條件',
                collapsible: true,
                animCollapse: true,
                autoHeight: true,
                defaultType: 'textfield',
                contentEl: 'pb_master'
            }),new Ext.grid.GridPanel({
                id: 'grdFinished',
                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: 'UserName', width: 50, 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: 350,
                bbar: new Ext.PagingToolbar({
                    pageSize: 25,
                    store: store,
                    displayInfo: true,
                    displayMsg: '{0} - {1} of {2}',
                    emptyMsg: "查無資料"
                })
            })]
        });

        fs.render('form-ct');

        store.load({params:{start:0, limit:25}});  // 必要參數

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

    });

    在 pgfinished.js 裡面由上到下，我們先定義一個 Ext.data.JsonStore 將 qryfinished2.aspx 中所傳回的 Json String
    存在 store 變數中，供 Grid Panel 使用

    在 Form Panel 上我們定義了一個 Field Set 以及一個 Grid Panel，在 Grid Panel 中我們將 data store 指向我們剛定義的
    store 變數，並在底下加上一個 PageToolBar (bbar 參數)，PageSize設為 25 即一頁顯示25筆 Record

    Define 完 UI 部份，我們將 Form Panel render 在 qryMasterPage.master  中的 <DIV> tag "form-ct"
    即 Form 的 Container，同時開始進行資料的擷取 store.load()

    由於我們要做分頁，所以必須將參數傳遞給 store 中 define 的 url

    qryfinished2.aspx

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

    qryfinished2.vb

    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.Params("uid")
        Dim start As Long = CLng(Request.Params("start")) '必須使用 Request.Params 來接參數
        Dim limit As Integer = CInt(Request.Params("limit")) ' Request.QueryString 接不到
        Dim pg As Integer

        If start = 0 Then
          pg = 1
        Else
          pg = (start / limit) + 1 '由於 extjs 中 start 指的是 開始的列數, 所以要透過計算轉換成頁數 (重要!!)
        End If

        JSONString = myJSONObject.ToJSONString(uid, pg, limit) ' 將 JSONString 傳至前端
      End Sub
    End Class

    接下來我們來看 qryfinished.asmx的程式

    Imports System.Web
    Imports System.Web.Services
    Imports System.Web.Services.Protocols
    Imports Newtonsoft.Json
    Imports CommonModule
    Imports Audits

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

      Dim connERP As System.Data.Odbc.OdbcConnection = Nothing
      Dim connWorkFlow As System.Data.SqlClient.SqlConnection = Nothing
      Dim connWorkFlow1 As System.Data.SqlClient.SqlConnection = Nothing

      <WebMethod()> _
      Public Function ToJSONString(ByVal uid As String, ByVal pg As Long, ByVal limit As Integer) As String
        Dim strSQL As String
        Dim sqlCommand As System.Data.SqlClient.SqlCommand
        Dim drWorkflow As System.Data.SqlClient.SqlDataReader = Nothing
        Dim iCount As Integer = 1, totalcount As Long
        Dim starttime As String = "", signtime As String = Now().ToString
        Dim oAudits() As Audits
        Dim po_no As String, version As String

        ReDim oAudits(0)

        Try

          Init_Connection()

          strSQL = "select count(*) as n from vw_po_finished"  ' 計算總筆數 store 中的 totalProperty 使用

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

          drWorkflow = sqlCommand.ExecuteReader()

          If drWorkflow.Read() Then
            totalcount = CLng(drWorkflow.GetValue(drWorkflow.GetOrdinal("n")))
          End If

          drWorkflow.Close()

          strSQL = "exec sp_po_finished @page=" + CStr(pg) + ",@limit=" + CStr(limit) + ",@userid='" + uid + "'"

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

          drWorkflow = sqlCommand.ExecuteReader()

          Do While drWorkflow.Read() '組織 Json Array
            po_no = drWorkflow.GetString(drWorkflow.GetOrdinal("ReferenceNo"))
            version = drWorkflow.GetString(drWorkflow.GetOrdinal("Version"))

            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 = uid
            oAudits(iCount - 1).StartTime = starttime
            oAudits(iCount - 1).UserName = drWorkflow.GetString(drWorkflow.GetOrdinal("UserName"))
            'oAudits(iCount - 1).InstanceID = drWorkflow.GetString(drWorkflow.GetOrdinal("InstanceID"))
            oAudits(iCount - 1).ReferenceNo = po_no
            oAudits(iCount - 1).Version = version
            signtime = drWorkflow.GetDateTime(drWorkflow.GetOrdinal("SignTime")).ToString()
            oAudits(iCount - 1).SignTime = signtime
            oAudits(iCount - 1).Actions = drWorkflow.GetString(drWorkflow.GetOrdinal("Actions"))

            oAudits(iCount - 1).Diff = getSignTime(CDate(starttime), CDate(signtime))
            iCount += 1

          Loop

          drWorkflow.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

          Close_Connection()
        End Try

        Return "{""totalCount"":" + CStr(totalcount) + ",""data"":" + JavaScriptConvert.SerializeObject(oAudits) + "}" ' 回傳 Json String
      End Function

      Private Sub Init_Connection()
        getConnectionString()

        connWorkFlow = getSqlConnection(connWorkFlowString)
        connWorkFlow.Open()

      End Sub

      Private Sub Close_Connection()
        If connWorkFlow.State = Data.ConnectionState.Open Then connWorkFlow.Close()

        If Not connWorkFlow Is Nothing Then connWorkFlow = Nothing
      End Sub
    End Class

    最後我們再說明一下 SQL Server 2000 中如何透過 T-SQ來達到資料分頁的作法

    CREATE PROCEDURE [dbo].[sp_po_finished]
    @page as int,
    @limit as int,
    @userid as varchar(10)=''
    AS
    begin
    declare @strSQL varchar(5000)

    set @strSQL='SELECT TOP '+convert(varchar(10),@limit)+' * from vw_po_finished '
    set @strSQL=@strSQL+'WHERE ReferenceNo+Version not in (SELECT TOP '+convert(varchar(10),(@page-1)*@limit)+' c.ReferenceNo+c.Version from vw_po_finished c '
    set @strSQL=@strSQL+'ORDER BY c.SignTime desc, c.ReferenceNo, c.Version) '
    set @strSQL=@strSQL+'ORDER BY SignTime desc, ReferenceNo, Version'

    print @strSQL

    exec (@strSQL)

    end
    GO


    說穿了，就是透過兩個 select TOP statement 來完成，舉例說 我們要看 26~50 筆的記錄
    就是 select TOP 25 ... NOT IN (select TOP (2-1)*25 ...)，因為 26~50 筆在第二頁  :)

    完成圖

    image

    程式碼

    po_finished.rar

    2:20:09 PM | Add a comment | Permalink | Blog it | Programming
    1/18/2008

    尾牙賓果券程式

    又是年終尾牙歡樂的時刻
    今年公司福委會決定恢復往年玩賓果的遊戲
    於是找上我寫一個產生賓果券的程式

    用什麼寫呢? 當然是 Excel 囉 直接利用它的 CELL 當作賓果券的格子再適當也不過

    公司這次希望每一個人有六個賓果遊戲券，每一個賓果券有 7 X 7  49 個號碼
    以亂數產生，抽獎球1~88號

    依照此需求我們先在 Excel VBA 中定義所需常數

    Const maxball = 88 ' 最大號碼
    Const matrix = 7     ' 方型矩陣UBound
    Const Nbr = 6         ' 幾個方型矩陣
    Const rs = 3            ' 第一個方形矩陣開始 Cell 的 Row
    Const rc = 2            ' 第一個方形矩陣開始 Cell 的 Column
    Dim bingo(Nbr, matrix, matrix) As Integer ' 存放 BINGO 券號碼的三維陣列

    有此定義後，開始撰寫主程式

      iCount = 2 ' 主頁資料開始列
      Do While Data.Cells(iCount, 1) <> "" ' 如果主頁資料為空白就停止讀取
           sheetcount = sheetcount + 1  ' 為每一個資料列產生新的工作表
          Worksheets("Template").Copy after:=Worksheets(sheetcount)   ' 透過 Template 產生新工作表
          Set NewSheet = Sheets(sheetcount + 1)
          NewSheet.Name = Data.Cells(iCount, 2)
          NewSheet.Visible = True
          NewSheet.Activate
          NewSheet.Cells(2, 2) = "工號：" & Data.Cells(iCount, 1) & " 姓名：" & Data.Cells(iCount, 2)
           Randomize    ' 對亂數產生器做初始化的動作。
          For i = 1 To Nbr
            DoEvents
            For j = 1 To matrix
              DoEvents
              For k = 1 To matrix
                DoEvents
    Continue:
               seed = Int((maxball * Rnd) + 1)    ' 產生 1 到 maxball 之間的亂數值。
                If Not CheckSeed(seed, i) Then  ' 檢查此亂數是否已出現過
                  GoTo Continue
                End If
                bingo(i, j, k) = seed   ' 將亂數值存到陣列中
              Next k
            Next j
          Next i
          For i = 1 To Nbr  ' 全部產生完畢後，將結果輸出
            DoEvents
            For j = 1 To matrix
              DoEvents
              For k = 1 To matrix
                DoEvents
                Select Case i
                  Case 1
                    iRow = rs: iCol = rc
                  Case 2
                    iRow = rs: iCol = rc + matrix + 1
                  Case 3
                    iRow = rs + matrix + 1: iCol = rc
                  Case 4
                    iRow = rs + matrix + 1: iCol = rc + matrix + 1
                  Case 5
                    iRow = rs + 2 * matrix + 2: iCol = rc
                  Case 6
                    iRow = rs + 2 * matrix + 2: iCol = rc + matrix + 1
                End Select
                NewSheet.Cells(iRow + (j - 1), iCol + (k - 1)) = bingo(i, j, k)
              Next k
            Next j
          Next i
          ResetBinGo
          iCount = iCount + 1
      Loop

    引用Function

    Private Sub ResetBinGo()
      Dim i As Integer, j As Integer, k As Integer
      For i = 1 To Nbr
        For j = 1 To matrix
          For k = 1 To matrix
            bingo(i, j, k) = 0
          Next k
        Next j
      Next i
    End Sub

    Private Function CheckSeed(n As Integer, i As Integer) As Boolean
      Dim j As Integer, k As Integer
      CheckSeed = True
      For j = 1 To matrix
        DoEvents
        For k = 1 To matrix
          DoEvents
          If n = bingo(i, j, k) Then
            CheckSeed = False
          End If
        Next k
      Next j
    End Function

    執行時，請記得將VBA安全性調到中度安全性，並且要啟用巨集

    image

    按產生賓果券，開始執行

    新圖片 (9)

    大功告成，不過因為是一個Sheet一個Sheet產生，可能要注意Excel記憶體的問題(還沒正式測啦)

     新圖片 (10)

    1:26:59 PM | Add a comment | Read comments (4) | Permalink | Blog it | Programming
    5/26/2005

    Code Template v1.0.0

    Code Template v1.0.0

     

    Author

      James Jan (jamesjantw@gmail.com)

     

    Description

      Write any text you want to reuse into a template file.

      Using this program you can read template file into the clipboard, and then you can press CTRL+V to paste it into any editor and any position you want.

     

    System Requirement

      Operation Systems: Windows series

      Hardware: Any platform that runs Microsoft Windows

     

    Installation

            1. Unpack the CodeTemplate.rar into your Hard Disk.

            2. Double Click on the setup.exe in your extracting folder to run installation process.

            3. Put your template files into the installation subfolder “Template”.

            4. Run CodeTemplate and enjoy it.

            Click the following URL to get it, check it out!

      http://us.f2.yahoofs.com/bc/429451a6_17f6/bc/VB/Code+Template+v1.0.0.rar?bfp.TlCBMyAzGNqg

    11:08:54 AM | Add a comment | Read comments (1) | Permalink | Blog it | Programming