form_stockquery.frm

仓库扫描管理系统

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form form_stockQuery 
   BackColor       =   &H00C0FFFF&
   Caption         =   "库存查询"
   ClientHeight    =   7005
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   9045
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   ScaleHeight     =   7005
   ScaleWidth      =   9045
   StartUpPosition =   3  'Windows Default
   Begin VB.CheckBox chkDisplayAllCols 
      BackColor       =   &H00C0FFFF&
      Caption         =   "显示所有列"
      Height          =   255
      Left            =   6600
      TabIndex        =   6
      Top             =   120
      Value           =   1  'Checked
      Width           =   1215
   End
   Begin VB.CommandButton cmdPrint 
      BackColor       =   &H00FF8080&
      Caption         =   "&P预览"
      Height          =   315
      Left            =   5280
      Picture         =   "form_stockQuery.frx":0000
      TabIndex        =   5
      Top             =   120
      Width           =   975
   End
   Begin VB.CommandButton Command1 
      BackColor       =   &H00FF8080&
      Caption         =   "&X返回"
      Height          =   315
      Left            =   8040
      Picture         =   "form_stockQuery.frx":11DE
      TabIndex        =   4
      Top             =   120
      Width           =   975
   End
   Begin VB.ComboBox cmbField 
      Height          =   315
      ItemData        =   "form_stockQuery.frx":23BC
      Left            =   120
      List            =   "form_stockQuery.frx":23BE
      Style           =   2  'Dropdown List
      TabIndex        =   3
      Top             =   120
      Width           =   1620
   End
   Begin VB.TextBox txtConditon 
      Height          =   315
      Left            =   1800
      TabIndex        =   2
      Text            =   "*"
      Top             =   120
      Width           =   1905
   End
   Begin VB.CommandButton cmdQuery 
      BackColor       =   &H00FF8080&
      Caption         =   "&Q查询"
      Height          =   315
      Left            =   3840
      Picture         =   "form_stockQuery.frx":23C0
      TabIndex        =   1
      Top             =   120
      Width           =   975
   End
   Begin MSFlexGridLib.MSFlexGrid mfgStock 
      Height          =   6255
      Left            =   120
      TabIndex        =   0
      Top             =   600
      Width           =   8865
      _ExtentX        =   15637
      _ExtentY        =   11033
      _Version        =   393216
      AllowUserResizing=   1
   End
End
Attribute VB_Name = "form_stockQuery"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim s, y, i     '定义变量
Dim rsStock As Recordset
Dim sqlMaster As String
Dim sqlOrderBy As String

Private Sub chkDisplayAllCols_Click()
    If chkDisplayAllCols.Value = 1 Then
        setFlexGridColsWidth s, mfgStock
    End If
End Sub

Private Sub cmdPrint_Click()
    Dim excelSheet As New Excel.Worksheet
    If mfgStock.rows = mfgStock.FixedRows Then
        MsgBox "没有数据可打印，请先查询！", vbInformation, "提示"
        Exit Sub
    End If
    Dim dtlRow As Integer
    dtlRow = 3
    Set excelSheet = flexgridToExcel(mfgStock, dtlRow)
    Dim range As Excel.range
    Set range = excelSheet.range(getExcelCellArea(1, 1) & ":" & getExcelCellArea(getShowDataCols(mfgStock), 1))
    range.MergeCells = True
    range.Value = "当   前   库   存"
    range.Font.Bold = True
    range.Font.Name = "宋体"
    range.Font.Size = 14
    range.HorizontalAlignment = xlCenter
    
    ' 加打印日期
    excelSheet.Cells(2, 6) = "打印日期："
    excelSheet.Cells(2, 7) = Format(Date, "yyyy-MM-dd")
    excelSheet.Columns(getExcelColId(7) & ":" & getExcelColId(7)).ColumnWidth = 10
    
    ' 格式化某列的数据
    Set range = excelSheet.range(getExcelCellArea(6, dtlRow + 1) & ":" & getExcelCellArea(6, dtlRow + mfgStock.rows - mfgStock.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    Set range = excelSheet.range(getExcelCellArea(7, dtlRow + 1) & ":" & getExcelCellArea(7, dtlRow + mfgStock.rows - mfgStock.FixedRows))
    range.NumberFormatLocal = "0_ "
    Set range = excelSheet.range(getExcelCellArea(8, dtlRow + 1) & ":" & getExcelCellArea(8, dtlRow + mfgStock.rows - mfgStock.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    
    Dim col As Integer
    For col = 1 To getShowDataCols(mfgStock)
        excelSheet.Columns(getExcelColId(col) & ":" & getExcelColId(col)).ShrinkToFit = False
    Next col
    Set range = excelSheet.range(getExcelCellArea(1, 1), getExcelCellArea(mfgStock.cols, mfgStock.rows + dtlRow))
    autoFitSize range
    excelSheet.Cells.PrintPreview
End Sub

Private Sub cmdQuery_Click()
    mfgStock.rows = mfgStock.FixedRows
    Dim fldName As String
    If (cmbField.Text = "物料名称") Then
      fldName = "productName"
    End If
    If (cmbField.Text = "物料编号") Then
      fldName = "productCode"
    End If
    Dim sql As String
    sql = sqlMaster & " and ( " + fldName + " like " + Chr(34) + "*" + txtConditon.Text + "*" + Chr(34) + ")"
    sql = sql + sqlOrderBy
    Set rsStock = g_db.OpenRecordset(sql)
    Dim qty, amount, pieceQty, axesWeight As Double
    With rsStock
        Do While Not .EOF
            mfgStock.rows = mfgStock.rows + 1
            mfgStock.row = mfgStock.rows - mfgStock.FixedRows
            mfgStock.TextMatrix(mfgStock.row, 0) = CStr(mfgStock.row)
            If Not IsNull(.Fields("productCode")) Then
              mfgStock.TextMatrix(mfgStock.row, 1) = .Fields("productCode")
            End If
            If Not IsNull(.Fields("productName")) Then
              mfgStock.TextMatrix(mfgStock.row, 2) = .Fields("productName")
            End If
            If Not IsNull(.Fields("productModel")) Then
              mfgStock.TextMatrix(mfgStock.row, 3) = .Fields("productModel")
            End If
            If Not IsNull(.Fields("productSpecs")) Then
              mfgStock.TextMatrix(mfgStock.row, 3) = mfgStock.TextMatrix(mfgStock.row, 3) + " || " + .Fields("productSpecs")
            End If
            If Not IsNull(.Fields("productStd")) Then
              mfgStock.TextMatrix(mfgStock.row, 4) = .Fields("productStd")
            End If
            If Not IsNull(.Fields("productUnit")) Then
              mfgStock.TextMatrix(mfgStock.row, 5) = .Fields("productUnit")
            End If
            If Not IsNull(.Fields("netWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 6) = Format(.Fields("netWeight"), g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("netWeightAmt")) Then
              mfgStock.TextMatrix(mfgStock.row, 7) = Format(.Fields("netWeightAmt"), g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("pQty")) Then
              mfgStock.TextMatrix(mfgStock.row, 8) = .Fields("pQty")
            End If
            If Not IsNull(.Fields("axesTtlWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 9) = Format(Val(.Fields("axesTtlWeight")) + Val(.Fields("netWeight")), g_barcode_weight_scale)
            End If
          .MoveNext
        Loop
    End With

End Sub

Private Sub Command1_Click()
  frm_main.Enabled = True
  Unload Me
End Sub

Private Sub Form_Load()
    Dim sql As String
    Me.Left = (Screen.Width - Me.Width) / 2
    Me.Top = (Screen.Height - Me.Height) / 2
    cmbField.AddItem "物料名称", 0
    cmbField.AddItem "物料编号", 1
    cmbField.Text = "物料编号"
    sqlMaster = "SELECT * FROM V_currentStock WHERE 1=1 "
    sqlOrderBy = " ORDER BY productCode, productName "
    mfgStock.rows = 2: mfgStock.cols = 10     '定义mfgStock表的总行数、总列数
    mfgStock.FixedRows = 1: mfgStock.FixedCols = 1     '定义mfgStock表的固定行数、固定列数
    mfgStock.rows = mfgStock.FixedRows
    s = Array("500", "1300", "1200", "1300", "900", "450", "800", "0", "900", "700")
    y = Array("序号", "物料编号", "物料名称", "型号||规格", " 标 准", "单位", "总净重", "金额", "件/箱", "总毛重")
    setFlexGridColsWidth s, mfgStock
    setFlexGridHead y, mfgStock
    '定义mfgStock表的列序号
    For i = mfgStock.FixedRows To mfgStock.rows - mfgStock.FixedRows
        mfgStock.TextMatrix(i, 0) = i
    Next i
    '  查询
    cmdQuery_Click
End Sub

Private Sub Form_Unload(Cancel As Integer)
    frm_main.Enabled = True
End Sub

Private Sub mfgStock_DblClick()
    If mfgStock.ColSel <> mfgStock.FixedCols - 1 Then
        mfgStock.ColWidth(mfgStock.ColSel) = 0
        chkDisplayAllCols.Value = 0
    End If
End Sub

Private Sub txtConditon_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then     '按回车键
        cmdQuery_Click
    End If
End Sub