form_outadd-bak.frm

仓库扫描管理系统

        End If
        If IsNumeric(mf1.TextMatrix(mf1.row, 6)) And IsNumeric(mf1.TextMatrix(mf1.row, 7)) Then
            mf1.TextMatrix(mf1.row, 8) = Val(mf1.TextMatrix(mf1.row, 6)) * Val(mf1.TextMatrix(mf1.row, 7))
            mf1.TextMatrix(mf1.row, 8) = Format(mf1.TextMatrix(mf1.row, 8), g_barcode_weight_scale)
        End If
        fillTotalDataFromDtlData
    End If
    '  轴重和件数变化时候更新总轴重
    If mf1.col = 9 Or mf1.col = 10 Then
        If text1.Text <> "" And Not IsNumeric(text1.Text) Then
            MsgBox "件数/轴重必须为数值！", vbCritical, "输入错误"
            text1.SetFocus
            Exit Sub
        End If
        If IsNumeric(mf1.TextMatrix(mf1.row, 9)) And IsNumeric(mf1.TextMatrix(mf1.row, 10)) Then
            mf1.TextMatrix(mf1.row, 11) = Val(mf1.TextMatrix(mf1.row, 9)) * Val(mf1.TextMatrix(mf1.row, 10)) + Val(mf1.TextMatrix(mf1.row, 6))
            mf1.TextMatrix(mf1.row, 11) = Format(mf1.TextMatrix(mf1.row, 11), g_barcode_weight_scale)
        End If
        fillTotalDataFromDtlData
    End If
End Sub
Private Sub Comdj_Click()
    isAdd = True
    '设置控件有效或无效
'    takeunitName.Enabled = True: handler.Enabled = True
'    billNo.Enabled = True
'    text1.Enabled = True: mf1.Enabled = True: Combc.Enabled = True: mf1.Enabled = True: Comqx.Enabled = True: Comdj.Enabled = False
    enableControls (True)
    '清空数据
    txttakeunit.Text = ""
    txtBillId.Text = ""
    takeunitName.Text = ""
    handler.Text = g_userName
    billDate.Text = CStr(Now)
    text1.Text = ""
    clearData mf1
    clearData msfgTtl
    takeunitName.SetFocus
    billNo.Text = getNextBillNo("hpos_StockOutBill_Master", "billNo")
End Sub
Private Sub Combc_Click()
     Set rs1 = g_db.OpenRecordset("hpos_StockOutBill_Dtl", dbOpenTable)
     Set rsMaster = g_db.OpenRecordset("hpos_StockOutBill_Master", dbOpenTable)
     Dim hasDtl As Boolean
     Dim billId As String
     hasDtl = False
    billId = CStr(getNextPK("hpos_StockOutBill_Master", "billId")) 'billNo.Text
    txtBillId.Text = billId
     Dim rsTmp As Recordset
'     Set rsTmp = g_db.OpenRecordset("select fullName,orgId,orgCode,shortenedform from hpos_organization where orgType=0 and orgId=" + txttakeunit.Text + " and ((fullName  = " + "'" + takeunitName.Text + "" + "'" + ")or (shortenedform = " + "'" + takeunitName.Text + "" + "'" + ")) order by fullName")
'    If rsTmp.EOF Then
'       MsgBox "请选择正确的客户名称!", vbCritical, "数据无效"
'       takeunitName.SetFocus
'       takeunitName.SelStart = 0
'       takeunitName.SelLength = Len(takeunitName.Text)
'       Exit Sub
'    End If
    Set rsTmp = g_db.OpenRecordset("select fullName,orgId,orgCode,shortenedform from hpos_organization " _
        & "where orgType=0 and ((fullName  = " + "'" + takeunitName.Text + "" + "'" + ")) order by fullName")
    If rsTmp.EOF Then
       MsgBox "无此客户，请按【PageDown】键选择!", vbCritical, "数据无效"
        txttakeunit.Text = ""
       takeunitName.Text = ""
       takeunitName.SetFocus
       takeunitName.SelStart = 0
       takeunitName.SelLength = Len(takeunitName.Text)
       Exit Sub
    Else
        txttakeunit.Text = rsTmp.Fields("orgId")
    End If
    
    If billNo.Text = "" Then
       MsgBox "单据编号不能为空!", vbCritical, "数据无效"
       billNo.SetFocus
       Exit Sub
    End If
    
    'Dim rsTmp As Recordset
    Set rsTmp = g_db.OpenRecordset("select * from hpos_StockOutBill_Master where billType=" + CStr(m_billType) + " and billNo='" & billNo.Text & "'")
    If rsTmp.RecordCount > 0 Then
       MsgBox "单据编号已经存在，请输入其它!", vbCritical, "数据无效"
       billNo.SetFocus
       Exit Sub
    End If
    rsTmp.Close
    
     For i = mf1.FixedRows To mf1.Rows - mf1.FixedRows
      If mf1.TextMatrix(i, 13) <> "" And mf1.TextMatrix(i, 6) <> "" Then
       hasDtl = True
       rs1.AddNew
    
       If billNo.Text <> "" Then rs1.Fields("billId") = billId
       rs1.Fields("dtlId") = billId & i
       ' 产品ID
       If mf1.TextMatrix(i, 13) <> "" Then rs1.Fields("productId") = mf1.TextMatrix(i, 13)
       ' 条形码
       If mf1.TextMatrix(i, 1) <> "" Then rs1.Fields("barcode") = mf1.TextMatrix(i, 1)
       If mf1.TextMatrix(i, 6) <> "" Then rs1.Fields("qty") = mf1.TextMatrix(i, 6)
       If mf1.TextMatrix(i, 7) <> "" Then rs1.Fields("price") = mf1.TextMatrix(i, 7)
       If mf1.TextMatrix(i, 9) <> "" Then rs1.Fields("axesWeight") = mf1.TextMatrix(i, 9)
       If mf1.TextMatrix(i, 10) <> "" Then rs1.Fields("pieceQty") = mf1.TextMatrix(i, 10)
       If mf1.TextMatrix(i, 12) <> "" Then rs1.Fields("comment") = mf1.TextMatrix(i, 12)
       rs1.Update        '更新表
      End If
     Next i
     If Not hasDtl Then
        MsgBox "没有数据可保存，请输入明细!", vbCritical, "警告"
        Exit Sub
     End If
     If hasDtl Then
        rsMaster.AddNew
    '   店铺(仓库)标识
        rsMaster.Fields("store") = g_store
        rsMaster.Fields("billType") = m_billType
    '   单据ID--主关键字
        If billNo.Text <> "" Then rsMaster.Fields("billId") = billId
        If takeunitName.Text <> "" Then rsMaster.Fields("takeunit") = txttakeunit.Text
        If handler.Text <> "" Then rsMaster.Fields("handler") = handler.Text
        If billDate.Text <> "" Then rsMaster.Fields("billDate") = CDate(billDate.Text) ' Now 'billDate.Text
        If billNo.Text <> "" Then rsMaster.Fields("billNo") = billNo.Text
        rsMaster.Update
    End If
    rsMaster.Close
    rs1.Close
'    MsgBox "保存成功!", vbInformation, "提示"
    isAdd = False
    ' 预览数据
    If MsgBox("保存成功，需要打印预览吗？", vbYesNo + vbQuestion + vbDefaultButton1, "提示") = vbYes Then
        previewData
    End If
     '清空数据
     clearData mf1
     clearData msfgTtl
     takeunitName.Text = "": handler.Text = ""
     billNo.Text = ""
     text1.Visible = False: gridCustomer.Visible = False     '设置控件不可见
     enableControls (False)
'     mf1.Enabled = False: Combc.Enabled = False: mf1.Enabled = False: Comdj.Enabled = True: Comqx.Enabled = False
End Sub
Private Sub Comqx_Click()      '取消操作
    takeunitName.Text = "": handler.Text = ""
    clearData mf1
    enableControls (False)
'    takeunitName.Enabled = False: handler.Enabled = False
'    gridCustomer.Visible = False: text1.Enabled = False: mf1.Enabled = False: Combc.Enabled = False: mf1.Enabled = False:
'    Comqx.Enabled = False: Comdj.Enabled = True: Comdj.SetFocus
End Sub
Private Sub Comend_Click()
  frm_main.Enabled = True
  Unload Me
End Sub

Private Sub text1_Validate(Cancel As Boolean)
  Dim sql, pCode As String
  Dim qty As Double
  If Len(Trim(text1.Text)) = g_barcode_length And mf1.col = 1 Then
        Dim StrMsg As String
        StrMsg = fillDataFromBarcode()
        If StrMsg = "" Then
'        mf1.row = mf1.row - 1
            mf1.col = 7
        Else
            MsgBox StrMsg, vbCritical, "警告"
'            mf1.Row =mf1.Row
'            text1.SetFocus
            Cancel = True
        End If
        Exit Sub
     
     
'     Call fillDataFromBarcode
'     mf1.row = mf1.row - 1
'     Exit Sub
  ElseIf mf1.col = 1 And Trim(text1.Text) <> "" Then
    MsgBox "条形码长度必须为" & CStr(g_barcode_length) & "位", vbCritical, "警告"
    Cancel = True
    Exit Sub
  End If
'  If mf1.col = 6 And Trim(text1.Text) <> "" And mf1.TextMatrix(mf1.row, 13) <> "" Then
'    pCode = Mid(mf1.TextMatrix(mf1.row, 1), g_barcode_product_start, g_barcode_weight_start - g_barcode_product_start)
'    qty = CDbl(mf1.TextMatrix(mf1.row, 6))
'    If qty > getStockQty(pCode) Then
'      MsgBox "出库数量(" + CStr(qty) + ")不能大于库存数量(" + CStr(getStockQty(pCode)) + ")！", vbCritical, "警告"
''      mf1.col = 6
'      Exit Sub
'    End If
'  End If
  
End Sub
Private Function getStockQty(pCode As String) As Double
    Dim rs As Recordset
    Set rs = g_db.OpenRecordset("SELECT netWeight,netWeightAmt,pQty,axesTtlWeight FROM V_currentStock WHERE productCode='" + pCode + "' ")
    If rs.BOF Then
        getStockQty = 0
        Exit Function
    Else
        getStockQty = rs.Fields("netWeight")
    End If
End Function

' 校验某列的数据输入是否有效;diffRow:0－表示当前行，－1表示上一行，1表示下一行。
Private Function checkData(col As Integer, colName As String, diffRow As Integer) As Boolean
  If mf1.row > mf1.FixedRows - 1 Then
    If Not Trim(mf1.TextMatrix(mf1.row, 1)) = "" And (mf1.col = col Or mf1.col = 1) And Not IsNumeric(Mid(Trim(mf1.TextMatrix(mf1.row, 1)), g_barcode_weight_start, g_barcode_length - g_barcode_weight_start)) Then
      MsgBox colName + "必须为数值！", vbCritical, "输入错误"
      If mf1.row > 1 Then
        mf1.row = mf1.row + diffRow
        text1.Visible = True
        text1.SetFocus
        text1.SelStart = 0
        text1.SelLength = Len(text1.Text)
        Exit Function
      End If
      checkData = False
    Else
      checkData = True
    End If
  End If
End Function

Private Sub clearData(msfg As MSFlexGrid)
    For r = msfg.FixedRows To msfg.Rows - msfg.FixedRows
        For c = msfg.FixedCols To msfg.cols - msfg.FixedCols
            msfg.TextMatrix(r, c) = ""
        Next
    Next
End Sub
' 计算累计总净重和轴重
Private Sub fillTotalDataFromDtlData()
    clearData msfgTtl
    For r = mf1.FixedRows To mf1.Rows - mf1.FixedRows
        '  只对存在的物料进行总净重、轴重等的累加
        If mf1.TextMatrix(r, 13) <> "" Then
            For i = msfgTtl.FixedRows To msfgTtl.Rows - msfgTtl.FixedRows
                If msfgTtl.TextMatrix(i, 10) = mf1.TextMatrix(r, 13) Then
                '对于存在的物料(productId相等)总净重、轴重等累加
                    msfgTtl.TextMatrix(i, 6) = Format(Val(msfgTtl.TextMatrix(i, 6)) + Val(mf1.TextMatrix(r, 6)), g_barcode_weight_scale) '总净重
                    msfgTtl.TextMatrix(i, 7) = Format(Val(msfgTtl.TextMatrix(i, 7)) + Val(mf1.TextMatrix(r, 8)), g_barcode_weight_scale) '金额
                    msfgTtl.TextMatrix(i, 8) = Format(Val(msfgTtl.TextMatrix(i, 8)) + Val(mf1.TextMatrix(r, 10)), "#0")  '件数
                    msfgTtl.TextMatrix(i, 9) = Format(Val(msfgTtl.TextMatrix(i, 9)) + Val(mf1.TextMatrix(r, 11)), g_barcode_weight_scale)  '轴重
                    Exit For
                Else '对于没有的物料新增一行并填充数据
                    If msfgTtl.TextMatrix(i, 10) = "" Then
'                        msfgTtl.Rows = msfgTtl.Rows + 1
                        msfgTtl.TextMatrix(i, 1) = Mid(mf1.TextMatrix(r, 1), g_barcode_product_start, g_barcode_weight_start - g_barcode_product_start)
                        For col = 2 To 6
                            msfgTtl.TextMatrix(i, col) = mf1.TextMatrix(r, col)
                        Next
                        msfgTtl.TextMatrix(i, 6) = Format(mf1.TextMatrix(r, 6), g_barcode_weight_scale) '总净重
                        msfgTtl.TextMatrix(i, 7) = Format(mf1.TextMatrix(r, 8), g_barcode_weight_scale) '金额
                        msfgTtl.TextMatrix(i, 8) = Format(mf1.TextMatrix(r, 10), "#0") '件数
                        msfgTtl.TextMatrix(i, 9) = Format(mf1.TextMatrix(r, 11), g_barcode_weight_scale) '轴重
                        msfgTtl.TextMatrix(i, 10) = mf1.TextMatrix(r, 13)  ' productId
                        Exit For
                    End If
                End If
            Next
        End If
    Next
End Sub

' 激活或者去活相关控件
Private Sub enableControls(flag As Boolean)
    takeunitName.Enabled = flag
    handler.Enabled = flag
    billNo.Enabled = flag
' 网格
    text1.Enabled = flag
    mf1.Enabled = flag
    msfgTtl.Enabled = flag
' 按钮
    Combc.Enabled = flag
    Comqx.Enabled = flag
    cmdDeleteLine.Enabled = flag
    Comdj.Enabled = Not flag
End Sub

Private Sub previewData()
    Dim excelSheet, sheet As New Excel.Worksheet
'    If Me.SSTab1.Tab = 0 Then
'        Me.SSTab1.Tab = 1
'    End If
'    If mf1.Rows = mf1.FixedRows Then
'        MsgBox "没有数据可打印，请先查询！", vbInformation, "提示"
'        Exit Sub
'    End If
    Dim dtlRow As Integer
    dtlRow = 3
    Set sheet = createExcel()
    Set excelSheet = multiGridDataToExcel(mf1, dtlRow, sheet)
    Set sheet = Nothing
    ' 设置第一列的宽度
    excelSheet.Columns(getExcelColId(1) & ":" & getExcelColId(1)).ColumnWidth = 13
    excelSheet.Columns(getExcelColId(3) & ":" & getExcelColId(3)).ColumnWidth = 15
    Dim range As Excel.range
    Set range = excelSheet.range(getExcelCellArea(1, 1) & ":" & getExcelCellArea(getShowDataCols(mf1), 1))
    range.MergeCells = True
    range.Value = "出    库    单"
    range.Font.Bold = True
    range.Font.Size = 14
    range.HorizontalAlignment = xlCenter
    
    excelSheet.Cells(2, 1) = "客户名称："
    excelSheet.Cells(2, 2) = Me.takeunitName.Text
    excelSheet.Cells(2, 4) = "单据编号："
    excelSheet.Cells(2, 5) = Me.billNo
    ' 加打印日期
    excelSheet.Cells(2, 7) = "出库日期："
    excelSheet.Cells(2, 8) = Format(Me.billDate.Text, "yyyy-MM-dd")
    excelSheet.Columns(getExcelColId(8) & ":" & getExcelColId(8)).ColumnWidth = 10
    ' 设置某列的格式
    Set range = excelSheet.range(getExcelCellArea(6, dtlRow + mf1.FixedRows) & ":" & getExcelCellArea(6, dtlRow + getValidRows(mf1) - mf1.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    Set range = excelSheet.range(getExcelCellArea(7, dtlRow + mf1.FixedRows) & ":" & getExcelCellArea(7, dtlRow + getValidRows(mf1) - mf1.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    Set range = excelSheet.range(getExcelCellArea(8, dtlRow + mf1.FixedRows) & ":" & getExcelCellArea(8, dtlRow + getValidRows(mf1) - mf1.FixedRows))
    range.NumberFormatLocal = "0_ "
    Set range = excelSheet.range(getExcelCellArea(9, dtlRow + mf1.FixedRows) & ":" & getExcelCellArea(9, dtlRow + getValidRows(mf1) - mf1.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    Set range = excelSheet.range(getExcelCellArea(1, dtlRow + mf1.FixedRows) & ":" & getExcelCellArea(1, dtlRow + getValidRows(mf1) - mf1.FixedRows))
    range.NumberFormatLocal = "000000"
    Dim col As Integer
    For col = 1 To getShowDataCols(mf1)
'        ExcelSheet.Columns(getExcelColId(col) & ":" & getExcelColId(col)).EntireColumn.AutoFit
        excelSheet.Columns(getExcelColId(col) & ":" & getExcelColId(col)).ShrinkToFit = False
    Next col
    ' 汇总数据起始行
    dtlRow = getValidRows(mf1) + dtlRow + 2
    Set range = excelSheet.range(getExcelCellArea(1, dtlRow - 1) & ":" & getExcelCellArea(getShowDataCols(msfgTtl), dtlRow - 1))
    range.MergeCells = True
    range.Value = "累计总净重、皮重"
    range.Font.Bold = True
    range.Font.Size = 14
    range.HorizontalAlignment = xlCenter

    Dim eSheet As New Excel.Worksheet
    Set eSheet = excelSheet
    Set eSheet = multiGridDataToExcel(Me.msfgTtl, dtlRow, eSheet)
    Set excelSheet = Nothing
    ' 设置某列的格式
    Set range = eSheet.range(getExcelCellArea(6, dtlRow + msfgTtl.FixedRows) & ":" & getExcelCellArea(6, dtlRow + getValidRows(msfgTtl) - msfgTtl.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    Set range = eSheet.range(getExcelCellArea(7, dtlRow + msfgTtl.FixedRows) & ":" & getExcelCellArea(7, dtlRow + getValidRows(msfgTtl) - msfgTtl.FixedRows))
    range.NumberFormatLocal = "0_ "
    Set range = eSheet.range(getExcelCellArea(8, dtlRow + msfgTtl.FixedRows) & ":" & getExcelCellArea(8, dtlRow + getValidRows(msfgTtl) - msfgTtl.FixedRows))
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    
    eSheet.Cells(getValidRows(msfgTtl) + dtlRow, 1) = "发货员："
    eSheet.Cells(getValidRows(msfgTtl) + dtlRow, 2) = Me.handler.Text
    eSheet.Cells(getValidRows(msfgTtl) + dtlRow, 7) = "打印日期："
    eSheet.Cells(getValidRows(msfgTtl) + dtlRow, 8) = Format(Date, "yyyy-MM-dd")
    Set range = eSheet.range(getExcelCellArea(1, 1), getExcelCellArea(mf1.cols, getValidRows(msfgTtl) + dtlRow))
    autoFitSize range
    eSheet.PageSetup.Orientation = xlLandscape
    eSheet.Cells.PrintPreview
End Sub