form_wastebooksum.frm

仓库扫描管理系统

    excelSheet.Columns(getExcelColId(5) & ":" & getExcelColId(5)).ColumnWidth = 10
    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 = 10
    ' 设置某列的格式
    Set range = excelSheet.range(getExcelCellArea(5, dtlRow + 1) & ":" & getExcelCellArea(5, dtlRow + mfgStock.rows - mfgStock.FixedRows))
    range.NumberFormatLocal = "#0_ "
    
    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(8, dtlRow + 1) & ":" & getExcelCellArea(8, 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 = g_barcode_weight_scale + "_ "
    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.Cells.PrintPreview
End Sub
Private Function getFieldNameByText(listText As String) As String
    Dim fldName As String
    If (listText = "物料名称") Then
      fldName = "productName"
    End If
    If (listText = "物料编号") Then
      fldName = "productCode"
    End If
    If (listText = "单据编号") Then
      fldName = "billNo"
    End If
    If (listText = "客户编号") Then
      fldName = "orgCode"
    End If
    If (listText = "客户全称") Then
      fldName = "fullName"
    End If
    If (listText = "工      号") Then
      fldName = "comment"
    End If
    getFieldNameByText = fldName
End Function
Private Sub cmdQuery_Click()
    mfgStock.rows = mfgStock.FixedRows
    Dim sql As String
    Dim fldName As String
    Dim fldValue As String
'   按供应商查询的话，只用汇总入库单数据
'    If cmbField.Text = "供应商编号" Or cmbField.Text = "供应商全称" Then
'        optInOrOut(1).Value = True
'    End If
'   按客户查询的话，只用汇总出库单数据
    sql = sqlMaster
    If ((cmbField.Text = "客户编号" Or cmbField.Text = "客户全称") And Trim(txtConditon.Text) <> "") _
            Or ((cmbFieldEx.Text = "客户编号" Or cmbFieldEx.Text = "客户全称") And Trim(txtConditonEx.Text) <> "") Then
        optInOrOut(2).Value = True
    End If
    fldName = getFieldNameByText(Me.cmbField.Text)
    sql = sql & " and ( " + fldName + " like " + Chr(34) + "*" + txtConditon.Text + "*" + Chr(34) + ")"

    If (cmbFieldEx.Text <> "供应商全称" And cmbFieldEx.Text <> "供应商编号") Then
        fldName = getFieldNameByText(Me.cmbFieldEx.Text)
        sql = sql & " and ( " + fldName + " like " + Chr(34) + "*" + txtConditonEx.Text + "*" + Chr(34) + ")"
    End If
    
    fldName = ""
'   按照供应商查询：汇总 入库单＝供应商；出库单的条码编号以供应商条码前缀开头
    If (cmbFieldEx.Text = "供应商全称") Then
        fldName = "fullName"
    End If
    If (cmbFieldEx.Text = "供应商编号") Then
        fldName = "orgCode"
    End If
    Dim rsSupplier As Recordset
    Dim barcodePrefix, sqlSupplier As String
    
    If (fldName = "fullName" Or fldName = "orgCode") And Trim(txtConditonEx.Text) <> "" And Trim(txtConditonEx.Text) <> "*" Then
        sqlSupplier = "select shortenedform from hpos_organization where orgType=1 and " + fldName + " like " + Chr(34) + "*" + Me.txtConditonEx.Text + "*" + Chr(34)
        Set rsSupplier = g_db.OpenRecordset(sqlSupplier)
        If rsSupplier.RecordCount > 0 Then
            barcodePrefix = Trim(rsSupplier.Fields("shortenedform"))
        Else
            barcodePrefix = "hunterpeng"
        End If
        rsSupplier.Close
'   按照供应商查询：汇总 入库单＝供应商；出库单的条码编号以供应商条码前缀开头
        sql = sql + " and ((tableName='hpos_StockIncomeBill_Dtl' and " + fldName + " like " + Chr(34) + "*" + _
            txtConditonEx.Text + "*" + Chr(34) + " )  or (tableName='hpos_StockOutBill_Dtl' and barcode like " + _
            Chr(34) + barcodePrefix + "*" + Chr(34) + "  ) )"
    End If
    
    sql = sql + " and (billDate between " + Chr(35) + CStr(DTP1.Value) + " 00:00:00" + Chr(35) + " and " + Chr(35) + CStr(DTP2.Value) + " 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 * from (SELECT billDate,orgId,productId,qty as inqty,0.0 as outqty FROM V_hpos_StockWasteBook WHERE tableName='hpos_StockIncomeBill_Dtl' UNION ALL SELECT billDate,orgId,productId,0.0 as inqty,qty as outqty FROM V_hpos_StockWasteBook WHERE tableName='hpos_StockOutBill_Dtl') as tmp "
    Set rsStock = g_db.OpenRecordset(sql)
    Dim qty, amount, pieceQty, axesWeight As Double
    With rsStock
        Do While Not .EOF
            ' 系数：出库位-1
            Dim k As Integer
'            If optInOrOut(2).Value = False 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) = ""
'            End If
            If Not IsNull(.Fields("productCode")) Then
              mfgStock.TextMatrix(mfgStock.row, 2) = .Fields("productCode")
            End If
            If Not IsNull(.Fields("productName")) Then
              mfgStock.TextMatrix(mfgStock.row, 3) = .Fields("productName")
            End If
            If Not IsNull(.Fields("productModel")) Then
              mfgStock.TextMatrix(mfgStock.row, 4) = .Fields("productModel")
            End If
            If Not IsNull(.Fields("productSpecs")) Then
              mfgStock.TextMatrix(mfgStock.row, 4) = mfgStock.TextMatrix(mfgStock.row, 4) + " || " + .Fields("productSpecs")
            End If
            If Not IsNull(.Fields("productStd")) Then
              mfgStock.TextMatrix(mfgStock.row, 5) = .Fields("productStd")
            End If
            If Not IsNull(.Fields("pQty")) Then
              mfgStock.TextMatrix(mfgStock.row, 6) = Format(.Fields("pQty"), "#0")
            End If
            If Not IsNull(.Fields("netWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 7) = Format(.Fields("netWeight") * k, g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("netWeightAmt")) Then
              mfgStock.TextMatrix(mfgStock.row, 8) = Format(.Fields("netWeightAmt") * k, g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("outnetWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 9) = Format(.Fields("outnetWeight") * k, g_barcode_weight_scale)
            End If
            If Not IsNull(.Fields("netWeight")) And Not IsNull(.Fields("outnetWeight")) Then
              mfgStock.TextMatrix(mfgStock.row, 10) = Format(.Fields("netWeight") * k - .Fields("outnetWeight") * k, g_barcode_weight_scale)
            End If
          .MoveNext
        Loop
    End With

End Sub

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

Private Sub Form_Load()
    chkDisplayAllCols.Visible = False
    Frame1.Visible = False
    Dim sql As String
    'DTP1.Format = dtpCustom
    'DTP1.CustomFormat = "yyyy-MM-dd HH:mm:ss"  '"yyyy年MM月dd日 HH时mm分ss秒"
    'DTP1.Value = Now
     DTP2.Value = CStr(Date)
    Me.Left = (Screen.Width - Me.Width) / 2
    Me.Top = (Screen.Height - Me.Height) / 2
    cmbField.AddItem "物料编号"
    cmbField.AddItem "物料名称"
    cmbField.AddItem "单据编号"
    cmbField.AddItem "工      号"
    
    cmbFieldEx.AddItem "客户编号"
    cmbFieldEx.AddItem "客户全称"
    cmbFieldEx.AddItem "供应商编号"
    cmbFieldEx.AddItem "供应商全称"
    
    cmbField.Text = "物料编号"
    cmbFieldEx.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  productCode, productName, productSpecs, productModel,productStd,SUM(qty*pieceQty) as netWeight,SUM(qty*pieceQty*V.price) AS netWeightAmt,SUM(pieceQty-outpieceQty) as pQty,SUM(axesWeight-outaxesWeight) AS axesTtlWeight,SUM(outqty*outpieceQty) as outnetWeight FROM V_hpos_StockWasteBook AS V WHERE 1=1 "
    sqlOrderBy = " group by productCode, productName, productSpecs, productModel,productStd "
    mfgStock.rows = 2: mfgStock.cols = 15     '定义mfgStock表的总行数、总列数
    mfgStock.FixedRows = 1: mfgStock.FixedCols = 1     '定义mfgStock表的固定行数、固定列数
    mfgStock.rows = mfgStock.FixedRows
    s = Array("500", "0", "800", "2000", "1500", "1200", "600", "1200", "0", "1200", "1200", "0", "0", "0", "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