Attribute VB_Name = "ModuleMain"
Option Explicit
Option Compare Text

Public wrks As Workspace
' 全局?jǐn)?shù)據(jù)庫名
Public g_db As Database
' access數(shù)據(jù)庫名稱
Public g_dbPath As String
' access數(shù)據(jù)庫路徑
Public g_dbName As String
' 備份路徑
'Public g_backupPath As String
' 店鋪ID
Public g_store As String
'數(shù)據(jù)庫服務(wù)器名
Public g_dbserver As String
' 條形碼長度
Public g_barcode_length As Integer
' 產(chǎn)品編號(hào)起始位置
Public g_barcode_product_start As Integer
' 重量起始位置
Public g_barcode_weight_start As Integer
' 重量基數(shù)(及除數(shù))
Public g_barcode_weight_base As Integer
' 重量小數(shù)位數(shù)，與重量基數(shù)對(duì)應(yīng)
Public g_barcode_weight_scale As String
' 條碼中順序號(hào)(編號(hào))的長度 :條碼中不包含順序號(hào)則其值為零
Public g_barcode_sequenceno_len As Integer
' 硬盤信息字符串
Public g_HddInfo As String
' 試用版本
Public g_TestVersionMsg As String
' 注冊碼
Public g_registerNumber As String

' 當(dāng)前登陸用戶編號(hào)
Public g_userCode As String
' 當(dāng)前登陸用戶名
Public g_userName As String
' 當(dāng)前登陸用戶組
Public g_userGroup As Integer
' 公司名稱
Public g_companyName As String
' 輸出到Excel中的當(dāng)前行(最大行號(hào))
Public g_curMaxOutputRow As String
' 出入庫單輸出到Excel中的總列數(shù)
Public g_billColCount As Long
'  針對(duì)第幾個(gè)客戶版本序號(hào)，從1開始按照自然數(shù)順序遞增。
Public g_CustomerSN As Integer
'   定義輸出到Excel表格中的行高
Public g_rowHeight As Double
'   是否輸出毛重
Public g_outputSkinWeight As Boolean

Sub Main()
    g_outputSkinWeight = False
'  針對(duì)第幾個(gè)客戶版本序號(hào)，從1開始按照自然數(shù)順序遞增。
'   g_CustomerSN為3時(shí)候 條碼編號(hào)為18位 而且條碼編號(hào)不可設(shè)置
    g_CustomerSN = 2
    g_TestVersionMsg = ""
    g_store = "001"
    g_dbName = "hunterPOS.mdb"
    g_dbserver = App.Path       ' "\\hunter"
    g_dbPath = g_dbserver & "\DB-Access\" & g_dbName
'    g_backupPath = "" & g_dbserver & "\backup\" & g_dbName
    createConnection
   
    g_billColCount = 12
    ' 獲取公司名稱
    g_companyName = getCompanyName()
    ' 獲取行高
    g_rowHeight = getRowHeight()
    ' 設(shè)置條碼格式
    setBarcodeFarmat
    '  獲取硬盤信息
    g_HddInfo = getHddInfo()
    ' 生成注冊碼
    g_registerNumber = EncryptString(g_HddInfo)
    form_login.Show
End Sub
' 清零庫存
Sub resetStock()
    Dim strPwd As String
    strPwd = InputBox("請輸入您的帳戶", "提示", "")
    If strPwd = "" Then
        Exit Sub
    End If

    Dim destFilePath As String
    Dim rsPwd As Recordset
    Set rsPwd = g_db.OpenRecordset("select * from hp_sysParas where (((hp_sysParas.paraCode)='resetStockPwd'))")
    If rsPwd.RecordCount = 0 Then
        MsgBox "對(duì)不起，尚未創(chuàng)建帳戶，請與管理員聯(lián)系！", vbCritical, "警告！"
        Exit Sub
    End If
    If rsPwd.RecordCount > 0 And rsPwd.Fields("paraValue") <> strPwd Then
        MsgBox "對(duì)不起，沒有該帳戶！", vbCritical, "警告！"
        Exit Sub
    End If

    Dim strPath, sql As String
    strPath = "C:\Program Files\HP\"
    If Dir(strPath, vbDirectory) = "" Then
      MkDir strPath
    End If
    destFilePath = strPath + "system" + CStr(Format(Now, "YYYYMMDDHHMMSS"))
    If destFilePath <> "" And g_dbPath <> destFilePath Then
        closeConnection
        FileCopy g_dbPath, destFilePath
'   刪除出入庫數(shù)據(jù)
On Error GoTo dberr
        createConnection
        wrks.BeginTrans
        sql = " delete from hpos_StockOutBill_Dtl "
        g_db.Execute (sql)
        sql = " delete from hpos_StockOutBill_Master "
        g_db.Execute (sql)
        sql = " delete from hpos_StockIncomeBill_Dtl "
        g_db.Execute (sql)
        sql = " delete from hpos_StockIncomeBill_Master "
        g_db.Execute (sql)
        wrks.CommitTrans
        MsgBox "恭喜您，操作成功!", vbInformation, "提示"
        Exit Sub
dberr:
        wrks.Rollback
        Exit Sub
    Else
        MsgBox "系統(tǒng)錯(cuò)誤，請與管理員聯(lián)系！", vbCritical, "警告"
    End If
End Sub
'打開數(shù)據(jù)庫(創(chuàng)建數(shù)據(jù)庫鏈接)
Public Sub createConnection()
    If g_db Is Nothing Then
        Set wrks = CreateWorkspace("", "admin", "")
        Set g_db = wrks.OpenDatabase(g_dbPath, False, False)
    End If
End Sub
'關(guān)閉數(shù)據(jù)庫鏈接
Public Sub closeConnection()
    If Not (g_db Is Nothing) Then
            g_db.Close
            Set g_db = Nothing
            wrks.Close
            Set wrks = Nothing
     End If
End Sub

Public Function getCompanyName() As String
    Dim rsCompanyName As Recordset
    Set rsCompanyName = g_db.OpenRecordset("SELECT paraValue FROM hp_sysParas WHERE paraCode='companyName'")
    If rsCompanyName.RecordCount <> 1 Then
        getCompanyName = ""
    Else
        getCompanyName = rsCompanyName.Fields("paraValue")
    End If
End Function
Public Function getRowHeight() As Double
    Dim rs As Recordset
    Set rs = g_db.OpenRecordset("SELECT paraValue FROM hp_sysParas WHERE paraCode='rowHeight'")
    If rs.RecordCount <> 1 Then
        getRowHeight = 18.5     ' 默認(rèn)為40行
    Else
        getRowHeight = CDbl(rs.Fields("paraValue"))
    End If
End Function
Public Function getNextPK(tableName As String, pkField As String) As Long
  Dim rst As Recordset
  Set rst = g_db.OpenRecordset("SELECT MAX(CLNG(" & pkField & ")) as pk from " & tableName)
  If IsNull(rst.Fields("pk")) Then
    getNextPK = 1
  Else
      getNextPK = CLng(rst.Fields("pk")) + 1
  End If
End Function
Public Function getNextBillNo(tableName As String, fldName As String) As String
    Dim strDate As String ' YYYYMMDD
    Dim strBillNo As String
    Dim rsBillNo As Recordset
'    strDate = CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date))
    strDate = Format(Date, "YYYYMMDD")
    Set rsBillNo = g_db.OpenRecordset("select MAX(" + fldName + ")  from " + tableName + " where " + fldName + " like '" + strDate + "*'")
    If (rsBillNo.RecordCount = 0 Or IsNull(rsBillNo.Fields(0))) Then
        strBillNo = strDate + "001"
    Else
'        strBillNo = Mid(rsBillNo.Fields(0), 1, 7) + CStr(CInt(Mid(rsBillNo.Fields(0), 8, 4)) + 1)
        Dim nextSn As Integer
        nextSn = CInt(Mid(rsBillNo.Fields(0), 9, 3)) + 1
        strBillNo = strDate + String(3 - Len(CStr(nextSn)), "0") + CStr(nextSn)
    End If
    getNextBillNo = strBillNo
End Function

' 字符串加密
Public Function EncryptString(Text As String) As String
    Dim i, k As Integer
    Dim intTmp As Long
    Dim strTmp As String
    intTmp = 0
    strTmp = ""
    For i = 1 To Len(Text)
    ' ascii碼加1的字母
        k = Asc(Mid(Text, Len(Text) - i + 1, 1)) + 1
        ' 數(shù)字或者字符的ascii碼值
        If (k >= 48 And k <= 57) Or (k >= 65 And k <= 90) Or (k >= 97 And k <= 122) Then
            strTmp = strTmp & Chr(k)
        Else
            strTmp = strTmp & "P"
        End If
    Next i
    EncryptString = strTmp
'    For i = 1 To Len(Text)
'        intTmp = intTmp + Asc(Mid(Text, i, 1))
'    Next i
'    EncryptString = CStr(intTmp * 123)
End Function

' 字符串倒置重排
Public Function UpendString(Text As String) As String
    Dim i As Integer
    Dim strTmp As String
    strTmp = ""
    For i = 1 To Len(Text)
        strTmp = strTmp & Mid(Text, Len(Text) - i + 1, 1)
    Next i
    UpendString = strTmp
End Function
'  返回true 表示注冊通過，false 表示未注冊通過
Public Function validRegister() As Boolean
    ' 校驗(yàn)是否注冊  每個(gè)表超過10條記錄便提示輸入注冊碼
    Dim rsIncomeBill As Recordset
    Dim rsOutBill As Recordset
    Dim rsOrganize As Recordset
    Dim rsProducts As Recordset
    Dim rsRegistNo As Recordset
    Set rsIncomeBill = g_db.OpenRecordset("SELECT COUNT(*) FROM hpos_StockIncomeBill_Master")
    Set rsOutBill = g_db.OpenRecordset("SELECT COUNT(*) FROM hpos_StockOutBill_Master")
    Set rsOrganize = g_db.OpenRecordset("SELECT COUNT(*) FROM hpos_organization")
    Set rsProducts = g_db.OpenRecordset("SELECT COUNT(*) FROM hpos_products")
    Set rsRegistNo = g_db.OpenRecordset("SELECT COUNT(*) FROM hp_sysParas WHERE paraCode='registerNumber' and paraValue='" & g_registerNumber & "'")
    Dim iCount As Integer
    iCount = 30
    If rsRegistNo.Fields(0) = 0 Then
        g_TestVersionMsg = "--試用版"
    End If
    If rsRegistNo.Fields(0) = 0 And (rsIncomeBill.Fields(0) > iCount Or rsOutBill.Fields(0) > iCount Or rsOrganize.Fields(0) > iCount Or rsProducts.Fields(0) > iCount) Then
        validRegister = False
        Exit Function
    End If
    validRegister = True
End Function

' 設(shè)置條碼格式
Public Sub setBarcodeFarmat()
    Dim intBarcodeFormat As Integer
    Dim rsBCFormat As Recordset
    Set rsBCFormat = g_db.OpenRecordset("SELECT paraValue FROM hp_sysParas WHERE paraCode='barcodeFormat'")
    If rsBCFormat.RecordCount <> 1 Then
        MsgBox "設(shè)置條形碼格式出錯(cuò)，請與管理員聯(lián)系！", vbInformation, "提示"
        End
    Else
        intBarcodeFormat = CInt(rsBCFormat.Fields("paraValue"))
    End If
    g_barcode_length = 13
    g_barcode_sequenceno_len = 0
    If g_CustomerSN = 3 Then
        g_barcode_length = 18
        g_barcode_sequenceno_len = 5
    End If
    ' 格式一：條碼格式1+重量/100重量取條形碼的第8位到第12位除以100
    If intBarcodeFormat = 1 Then
        g_barcode_product_start = 3
        g_barcode_weight_start = 8
        g_barcode_weight_base = 100
    End If
    ' 格式二：條碼格式1+重量/1000重量取條形碼的第8位到第12位除以1000
    If intBarcodeFormat = 2 Then
        g_barcode_product_start = 3
        g_barcode_weight_start = 8
        g_barcode_weight_base = 1000
    End If
    ' 格式三：條碼格式2+重量/100重量取條形碼的第7位到第12位除以100
    If intBarcodeFormat = 3 Then
        g_barcode_product_start = 3
        g_barcode_weight_start = 7
        g_barcode_weight_base = 100
    End If