form_wastebook.frm

仓库扫描管理系统

    excelSheet.Columns(getExcelColId(7) & ":" & getExcelColId(7)).ColumnWidth = 3
    excelSheet.Columns(getExcelColId(2) & ":" & getExcelColId(2)).ColumnWidth = 11
    Dim range As Excel.range
    Set range = excelSheet.range(getExcelCellArea(1, 1) & ":" & getExcelCellArea(getShowDataCols(mfgStock), 1))
    range.MergeCells = True
    range.Value = rptTitle
    range.Font.Bold = True
    range.Font.Name = "宋体"
    range.Font.Size = 14
    range.HorizontalAlignment = xlCenter

    
    excelSheet.Cells(2, 1) = "起始日期："
    excelSheet.Cells(2, 2) = DTP1.Value
    excelSheet.Cells(2, 4) = "截止日期："
    excelSheet.Cells(2, 5) = DTP2.Value
    ' 加打印日期
    excelSheet.Cells(2, 7) = "打印日期："
    excelSheet.Cells(2, 8) = Format(Date, "yyyy-MM-dd")
    excelSheet.Columns(getExcelColId(8) & ":" & getExcelColId(8)).ColumnWidth = 8
    ' 设置某列的格式
    Set range = excelSheet.range(getExcelCellArea(1, dtlRow + 1) & ":" & getExcelCellArea(1, dtlRow + mfgStock.rows - mfgStock.FixedRows))
    range.NumberFormatLocal = "yyyy-MM-dd "
    
    Dim i As Integer
    For i = 4 To 12
        Set range = excelSheet.range(getExcelCellArea(i, dtlRow + 1) & ":" & getExcelCellArea(i, dtlRow + mfgStock.rows - mfgStock.FixedRows))
        range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    Next
    Set range = excelSheet.range(getExcelCellArea(10, dtlRow + 1) & ":" & getExcelCellArea(10, dtlRow + mfgStock.rows - mfgStock.FixedRows))
    range.NumberFormatLocal = "#0_ "
    
    Dim col As Integer
    For col = 1 To getShowDataCols(mfgStock)
'        excelSheet.Columns(getExcelColId(col) & ":" & getExcelColId(col)).EntireColumn.AutoFit
        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.PageSetup.Orientation = xlLandscape
    excelSheet.Cells.PrintPreview
End Sub

Private Sub cmdQuery_Click()
    mfgStock.rows = mfgStock.FixedRows
    Dim sql As String
    Dim fldName As String
    
    If (cmbField.Text = "物料名称") Then
      fldName = "productName"
    End If
    If (cmbField.Text = "物料编号") Then
      fldName = "productCode"
    End If
    If (cmbField.Text = "单据编号") Then
      fldName = "billNo"
    End If
    If (cmbField.Text = "客户编号") Then
      fldName = "orgCode"
    End If
    If (cmbField.Text = "客户全称") Then
      fldName = "fullName"
    End If
    If (cmbField.Text = "工        号") Then
      fldName = "comment"
    End If
    
    Dim startDate As String '开始日期
    Dim endDate As String  ' 起始日期
    If g_userGroup = 1 Then
        startDate = CStr(Date)
        endDate = CStr(Date)
    Else
        startDate = CStr(DTP1.Value)
        endDate = CStr(DTP2.Value)
    End If
    sql = sqlMaster & " and ( " + fldName + " like " + Chr(34) + "*" + txtConditon.Text + "*" + Chr(34) + ")"
    sql = sql + " and (billDate between " + Chr(35) + startDate + " 00:00:00" + Chr(35) + " and " + Chr(35) + endDate + " 23:59:59" + Chr(35) + ") "
    If optInOrOut(1).Value = True Then
        sql = sql + " and tableName='hpos_StockIncomeBill_Dtl' "
    End If
    If optInOrOut(2).Value = True Then
        sql = sql + " and tableName='hpos_StockOutBill_Dtl' "
    End If
    
    sql = sql + sqlOrderBy
'sql = "SELECT V.*, productCode, productName, productSpecs, productModel, productUnit, productStd, qty*V.price AS netWeightAmt,  pieceQty*axesWeight AS axesTtlWeight  FROM V_hpos_StockWasteBook AS V  ORDER BY store, billDate, productCode, tableName,productName "
    Set rsStock = g_db.OpenRecordset(sql)
    If rsStock.RecordCount > 2000 Then
        MsgBox "数据量太大(不能超过2000条)，请缩小时间范围或者精确查询条件！"
        Me.txtConditon.SetFocus
        Exit Sub
    End If
    Dim qty, amount, pieceQty, axesWeight As Double
    With rsStock
        Do While Not .EOF
            ' 系数：入库位1，出库位-1
            Dim k As Integer
'            If .Fields("tableName") = "hpos_StockIncomeBill_Dtl" Then
'                k = 1
'            Else
'                k = -1
'            End If
            k = 1
            mfgStock.rows = mfgStock.rows + 1
            mfgStock.row = mfgStock.rows - mfgStock.FixedRows
            mfgStock.TextMatrix(mfgStock.row, 0) = CStr(mfgStock.row)
            If Not IsNull(.Fields("billDate")) Then
              mfgStock.TextMatrix(mfgStock.row, 1) = .Fields("billDate")
            End If
            If Not IsNull(.Fields("billNo")) Then
              mfgStock.TextMatrix(mfgStock.row, 2) = .Fields("billNo")
            End If
            If Not IsNull(.Fields("productCode")) Then
              mfgStock.TextMatrix(mfgStock.row, 3) = .Fields("productCode")
            End If
            If Not IsNull(.Fields("productName")) Then
              mfgStock.TextMatrix(mfgStock.row, 4) = .Fields("productName")
            End If
            If Not IsNull(.Fields("productModel")) Then
              mfgStock.TextMatrix(mfgStock.row, 5) = .Fields("productModel")
            End If
            If Not IsNull(.Fields("productSpecs")) Then
              mfgStock.TextMatrix(mfgStock.row, 5) = mfgStock.TextMatrix(mfgStock.row, 5) + " || " + .Fields("productSpecs")
            End If
            If Not IsNull(.Fields("productStd")) Then
              mfgStock.TextMatrix(mfgStock.row, 6) = .Fields("productStd")
            End If
            If Not IsNull(.Fields("productUnit")) Then
              mfgStock.TextMatrix(mfgStock.row, 7) = .Fields("productUnit")
            End If
            If Not IsNull(.Fields("netWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 8) = Format(.Fields("netWeight") * k, g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("netWeightAmt")) Then
              mfgStock.TextMatrix(mfgStock.row, 9) = Format(.Fields("netWeightAmt") * k, g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("axesTtlWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 10) = Format(.Fields("axesTtlWeight") * k, g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("ttlPQty")) Then
              mfgStock.TextMatrix(mfgStock.row, 11) = Format(Val(.Fields("ttlPQty")), "#0")
            End If
            If Not IsNull(.Fields("tableName")) Then
                If Trim(.Fields("tableName")) = "hpos_StockIncomeBill_Dtl" Then
                    mfgStock.TextMatrix(mfgStock.row, 12) = "入库"
                End If
                If Trim(.Fields("tableName")) = "hpos_StockOutBill_Dtl" Then
                    mfgStock.TextMatrix(mfgStock.row, 12) = "出库"
                End If
            End If
'            If Not IsNull(.Fields("billNo")) Then
'              mfgStock.TextMatrix(mfgStock.row, 12) = .Fields("billNo")
'            End If
            If Not IsNull(.Fields("fullName")) Then
              mfgStock.TextMatrix(mfgStock.row, 13) = .Fields("fullName")
            End If
            If Not IsNull(.Fields("dtlId")) Then
              mfgStock.TextMatrix(mfgStock.row, 14) = .Fields("dtlId")
            End If
          .MoveNext
        Loop
    End With

End Sub

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

Private Sub Form_Load()
    Dim sql As String
    'DTP1.Format = dtpCustom
    'DTP1.CustomFormat = "yyyy-MM-dd HH:mm:ss"  '"yyyy年MM月dd日 HH时mm分ss秒"
    'DTP1.Value = Now
    DTP1.Value = CStr(DateAdd("d", -2, Date))
     DTP2.Value = CStr(Date)
    Me.Left = (Screen.Width - Me.Width) / 2
    Me.Top = (Screen.Height - Me.Height) / 2
    cmbField.AddItem "物料编号", 0
    cmbField.AddItem "物料名称", 1
    cmbField.AddItem "单据编号", 2
    cmbField.AddItem "客户编号", 3
    cmbField.AddItem "客户全称", 4
    cmbField.AddItem "工        号", 5
    cmbField.Text = "物料编号"
'    If g_userGroup = 1 Then
'        DTP1.Value = CStr(Date)
'        DTP1.Visible = False
'        DTP2.Visible = False
'        Label10.Visible = False
'        Label5.Visible = False
'    End If

    sqlMaster = " SELECT orgCode, fullName, shortenedform, productCode, productName, productSpecs, productModel, productUnit, productStd, tableName, billId, orgId, store, billDate, billNo, handler, billType, dtlId, barcode, productId,qty*pieceQty+outqty*outpieceQty as netWeight,qty*pieceQty*price+outqty*outpieceQty*outprice AS netWeightAmt,pieceQty+outpieceQty as ttlPQty,  axesWeight+outaxesWeight AS axesTtlWeight " & _
        " FROM V_hpos_StockWasteBook  WHERE 1=1 "
    sqlOrderBy = " ORDER BY store, billDate, productCode, tableName,productName "
    
    mfgStock.rows = 2: mfgStock.cols = 15     '定义mfgStock表的总行数、总列数
    mfgStock.FixedRows = 1: mfgStock.FixedCols = 1     '定义mfgStock表的固定行数、固定列数
    mfgStock.rows = mfgStock.FixedRows
    s = Array("500", "1600", "1200", "900", "1200", "1300", "900", "450", "750", "0", "750", "750", "500", "1200", "0")
    y = Array("序号", "业务日期", "单据编号", "物料编号", "物料名称", "型号||规格", "标准", "单位", "总净重", "金额", "总皮重", "件/箱", "出/入", "供应商/客户", "dtlId")
    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 optInOrOut_Click(Index As Integer)
    Dim i As Integer
    optInOrOut(Index).Value = True
    For i = 0 To optInOrOut.Count - 1
        If i <> Index Then
            optInOrOut(i).Value = False
        End If
    Next
    cmdQuery_Click
End Sub

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