Attribute VB_Name = "DBupdate"

'********************************************************
'   文字列からカンマ(,)を除く
'********************************************************
Function CommaChk(ByVal WS As String) As String
    
    Dim P As Variant
    P = InStr(1, WS, ",")
    Do While P > 0
        Mid(WS, P, 1) = "."
        P = InStr(1, WS, ",")
    Loop
    
    P = InStr(1, WS, "，")
    Do While P > 0
        Mid(WS, P, 1) = "．"
        P = InStr(1, WS, "，")
    Loop
    CommaChk = WS

End Function

'********************************************************
'   分類マスタ更新
'********************************************************
Sub UpdateBunMas(ByVal FldrNo As Variant)
    Dim lstrPath As String                  ' DBへのパス
    Dim lobjRS As New ADODB.Recordset       ' レコードセットオブジェクト
    Dim lstrSQL As String
    Dim str As String
    Dim 端末ID, 分類コード As Variant
    Dim WSQL As String
    Dim cFso As FileSystemObject
    Dim FLDRname, DatName, CSVname As String
    Dim Wstr As String
    Dim MasName As String
    Dim P, WS As Variant
    
    MasName = "BUNMAS"
    
    FLDRname = DTPath & Zero_fix(4, FldrNo) & "\rcv"
    DatName = FLDRname & "\" & MasName & ".dat"
    CSVname = FLDRname & "\" & MasName & ".csv"
    
    ' ADOのオブジェクト変数を宣言する
    Dim cnn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    
    ' 接続を確立する
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBname & ";"
             
    端末ID = Zero_fix(4, FldrNo)
    ' テーブル名を指定してレコードセットを作成する
    WSQL = "SELECT * From " & MasName & " where ([端末ID]='" & 端末ID & "')"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic

    Open DatName For Input As #1

    P = 0
    Do While Not EOF(1)
        Line Input #1, str
        P = P + 1
        分類コード = Mid(str, 2, 4)
        
'        Forms!MainMenu!LStatus.Text = "分類:" & P & ":" & 分類コード
        Debug.Print "分類:" & P & ":" & 分類コード
        
        If (Not rec.EOF) Then rec.MoveFirst
        
        rec.Find "([分類コード]='" & 分類コード & "')"
        
        If (rec.EOF) Then
            rec.AddNew
        End If
            rec!端末ID = Zero_fix(4, FldrNo)
            rec!有効区分 = Mid(str, 1, 1)
            rec!分類コード = Mid(str, 2, 4)
            
            rec!分類漢字名 = CommaChk(Trim(Mid(str, 6, 20)))
            
            rec!消費税区分 = Mid(str, 26, 1)
            rec!消費税率 = Mid(str, 27, 3)
            
            rec!分類カナ名 = CommaChk(Trim(Mid(str, 30, 10)))
            
            rec!filler = Mid(str, 40, 3)
        rec.Update
        
    Loop
    Close (1)
    
    rec.Close
    
    
'*****************************************************************
'   CSVファイルの作成
'*****************************************************************
    Set cFso = New FileSystemObject
    
    If cFso.FileExists(CSVname) Then
        Call cFso.DeleteFile(CSVname)
    End If
    
    Open CSVname For Output As #1
    WSQL = "select * from " & MasName & " where [端末ID]='" & 端末ID & "' order by 分類コード;"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic
    Do Until rec.EOF
        Wstr = Trim(rec!分類コード) & "," & _
               Trim(rec!分類漢字名) & "," & _
               rec!消費税区分 & "," & _
               rec!消費税率 & "," & _
               Trim(rec!分類カナ名) & "," & _
               rec!有効区分 & "," & rec!filler
        Print #1, Wstr
        
        rec.MoveNext
    Loop
    
    rec.Close
    Close (1)

    
    ' 切断
    cnn.Close

End Sub

'***************************************************************
'   店舗マスタ更新
'***************************************************************
Sub UpdateTenMas(ByVal FldrNo As Variant)
    Dim lstrPath As String                  ' DBへのパス
    Dim lobjRS As New ADODB.Recordset       ' レコードセットオブジェクト
    Dim lstrSQL As String
    Dim str As String
    Dim 端末ID, 店舗コード As Variant
    Dim WSQL As String
    Dim cFso As FileSystemObject
    Dim FLDRname, DatName, CSVname As String
    Dim Wstr As String
    Dim MasName As String
    Dim P As Variant
    
    MasName = "TENMAS"
    
    FLDRname = DTPath & Zero_fix(4, FldrNo) & "\rcv"
    DatName = FLDRname & "\" & MasName & ".dat"
    CSVname = FLDRname & "\" & MasName & ".csv"
    
    ' ADOのオブジェクト変数を宣言する
    Dim cnn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    
    ' 接続を確立する
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBname & ";"
             
    端末ID = Zero_fix(4, FldrNo)
    ' テーブル名を指定してレコードセットを作成する
    WSQL = "SELECT * From " & MasName & " where ([端末ID]='" & 端末ID & "')"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic

    Open DatName For Input As #1

    P = 0
    Do While Not EOF(1)
        Line Input #1, str
        P = P + 1
        
        店舗コード = Mid(str, 2, 3)
        
        Debug.Print "店舗:" & P & ":" & 店舗コード
        
        
        If (Not rec.EOF) Then rec.MoveFirst
        
        rec.Find "([店舗コード]='" & 店舗コード & "')"
        
        If (rec.EOF) Then rec.AddNew
            rec!端末ID = Zero_fix(4, FldrNo)
            rec!有効区分 = Mid(str, 1, 1)
            rec!店舗コード = Mid(str, 2, 3)
            rec!店舗名漢字 = CommaChk(Trim(Mid(str, 5, 15)))
            rec!filler = Mid(str, 40, 14)
        rec.Update
        
    Loop
    Close (1)
    
    rec.Close
    
    
'*****************************************************************
'   CSVファイルの作成
'*****************************************************************

    Set cFso = New FileSystemObject
    
    If cFso.FileExists(CSVname) Then
        Call cFso.DeleteFile(CSVname)
    End If
    
    Open CSVname For Output As #1
    WSQL = "select * from " & MasName & " where [端末ID]='" & 端末ID & "' order by 店舗コード;"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic
    Do Until rec.EOF
        Wstr = Trim(rec!店舗コード) & "," & Trim(rec!店舗名漢字) & "," & rec!有効区分
        Print #1, Wstr
        
        rec.MoveNext
    Loop
    
    rec.Close
    Close (1)

    
    ' 切断
    cnn.Close

End Sub

'***************************************************************
'   仕入先マスタ更新
'***************************************************************
Sub UpdateSirMas(ByVal FldrNo As Variant)
    Dim lstrPath As String                  ' DBへのパス
    Dim lobjRS As New ADODB.Recordset       ' レコードセットオブジェクト
    Dim lstrSQL As String
    Dim str As String
    Dim 端末ID, 仕入先コード As Variant
    Dim WSQL As String
    Dim cFso As FileSystemObject
    Dim FLDRname, DatName, CSVname As String
    Dim Wstr As String
    Dim MasName As String
    Dim P As Variant
    
    MasName = "SIRMAS"
    
    FLDRname = DTPath & Zero_fix(4, FldrNo) & "\rcv"
    DatName = FLDRname & "\" & MasName & ".dat"
    CSVname = FLDRname & "\" & MasName & ".csv"
    
    ' ADOのオブジェクト変数を宣言する
    Dim cnn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    
    ' 接続を確立する
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBname & ";"
             
    端末ID = Zero_fix(4, FldrNo)
    ' テーブル名を指定してレコードセットを作成する
    WSQL = "SELECT * From " & MasName & " where ([端末ID]='" & 端末ID & "')"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic

    Open DatName For Input As #1

    P = 0
    Do While Not EOF(1)
        Line Input #1, str
        P = P + 1
        
        仕入先コード = Mid(str, 2, 6)
        
        Debug.Print "仕入先:" & P & ":" & 仕入先コード
        
        If (Not rec.EOF) Then rec.MoveFirst
        
        rec.Find "([仕入先コード]='" & 仕入先コード & "')"
        
        If (rec.EOF) Then rec.AddNew
            rec!端末ID = Zero_fix(4, FldrNo)
            rec!有効区分 = Mid(str, 1, 1)
            rec!仕入先コード = Mid(str, 2, 6)
            rec!仕入先漢字名 = CommaChk(Trim(Mid(str, 8, 20)))
            rec!業者区分 = Mid(str, 28, 1)
            rec!消費税区分 = Mid(str, 29, 1)
            rec!端数処理 = Mid(str, 30, 1)
            rec!丸め単位 = Mid(str, 31, 1)
            rec!仕入先カナ名 = CommaChk(Trim(Mid(str, 32, 10)))
        rec.Update
        
    Loop
    Close (1)
    
    rec.Close
    
'*****************************************************************
'   CSVファイルの作成
'*****************************************************************

    Set cFso = New FileSystemObject
    
    If cFso.FileExists(CSVname) Then
        Call cFso.DeleteFile(CSVname)
    End If
    
    Open CSVname For Output As #1
    WSQL = "select * from " & MasName & " where [端末ID]='" & 端末ID & "' order by 仕入先コード;"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic
    Do Until rec.EOF
        Wstr = rec!仕入先コード & "," & _
               Trim(rec!仕入先漢字名) & "," & _
               rec!業者区分 & "," & _
               rec!消費税区分 & "," & _
               rec!端数処理 & "," & _
               rec!丸め単位 & "," & _
               Trim(rec!仕入先カナ名) & "," & _
               rec!有効区分 & "," & _
               rec!有効区分
        Print #1, Wstr
        
        rec.MoveNext
    Loop
    
    rec.Close
    Close (1)

    
    ' 切断
    cnn.Close

End Sub

'***************************************************************
'   得意先マスタ更新
'***************************************************************
Sub UpdateTokMas(ByVal FldrNo As Variant)

    Dim lstrPath As String                  ' DBへのパス
    Dim lobjRS As New ADODB.Recordset       ' レコードセットオブジェクト
    Dim lstrSQL As String
    Dim str As String
    Dim 端末ID, 有効区分, 得意先コード As Variant
    Dim WSQL As String
    Dim cFso As FileSystemObject
    Dim FLDRname, DatName, CSVname As String
    Dim Wstr As String
    Dim MasName As String
    Dim P As Variant
    
    MasName = "TOKMAS"
    
    FLDRname = DTPath & Zero_fix(4, FldrNo) & "\rcv"
    DatName = FLDRname & "\" & MasName & ".dat"
    CSVname = FLDRname & "\" & MasName & ".csv"
    
    ' ADOのオブジェクト変数を宣言する
    Dim cnn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    
    ' 接続を確立する
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBname & ";"
             
    端末ID = Zero_fix(4, FldrNo)
    ' テーブル名を指定してレコードセットを作成する
    WSQL = "SELECT * From " & MasName & " where ([端末ID]='" & 端末ID & "')"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic

    Open DatName For Input As #1

    P = 0
    Do While Not EOF(1)
        Line Input #1, str
        P = P + 1
        得意先コード = Mid(str, 2, 6)
        
        Debug.Print "得意先:" & P & ":" & 得意先コード
        
        If (Not rec.EOF) Then rec.MoveFirst
        
        rec.Find "([得意先コード]='" & 得意先コード & "')"
        
        If (rec.EOF) Then rec.AddNew
            rec!端末ID = Zero_fix(4, FldrNo)
            rec!有効区分 = Mid(str, 1, 1)
            rec!得意先コード = Mid(str, 2, 6)
            rec!得意先漢字名 = CommaChk(Trim(Mid(str, 8, 20)))
            rec!業者区分 = Mid(str, 28, 1)
            rec!消費税区分 = Mid(str, 29, 1)
            rec!端数処理 = Mid(str, 30, 1)
            rec!丸め単位 = Mid(str, 31, 1)
            rec!得意先カナ名 = CommaChk(Trim(Mid(str, 32, 10)))
            rec!filler = Mid(str, 42, 1)
        rec.Update
        
    Loop
    Close (1)
    
    rec.Close
    
    
'*****************************************************************
'   CSVファイルの作成
'*****************************************************************

    Set cFso = New FileSystemObject
    
    If cFso.FileExists(CSVname) Then
        Call cFso.DeleteFile(CSVname)
    End If
    
    Open CSVname For Output As #1
    WSQL = "select * from " & MasName & " where [端末ID]='" & 端末ID & "' order by 得意先コード;"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic
    Do Until rec.EOF
        Wstr = rec!得意先コード & "," & _
               Trim(rec!得意先漢字名) & "," & _
               rec!業者区分 & "," & _
               rec!消費税区分 & "," & _
               rec!端数処理 & "," & _
               rec!丸め単位 & "," & _
               Trim(rec!得意先カナ名) & "," & _
               rec!有効区分
        Print #1, Wstr
        
        rec.MoveNext
    Loop
    
    rec.Close
    Close (1)

    
    ' 切断
    cnn.Close

End Sub


'***************************************************************
'   商品マスタ更新
'***************************************************************
Sub UpdateShoMas(ByVal FldrNo As Variant)

    Dim lstrPath As String                  ' DBへのパス
    Dim lobjRS As New ADODB.Recordset       ' レコードセットオブジェクト
    Dim lstrSQL As String
    Dim str As String
    Dim 端末ID, 商品コード As Variant
    Dim WSQL As String
    Dim cFso As FileSystemObject
    Dim FLDRname, DatName, CSVname As String
    Dim Wstr As String
    Dim MasName As String
    Dim P, WS As Variant
    
    MasName = "SHOMAS"
    
    FLDRname = DTPath & Zero_fix(4, FldrNo) & "\rcv"
    DatName = FLDRname & "\" & MasName & ".dat"
    CSVname = FLDRname & "\" & MasName & ".csv"
    
    ' ADOのオブジェクト変数を宣言する
    Dim cnn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    
    ' 接続を確立する
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBname & ";"
             
    端末ID = Zero_fix(4, FldrNo)
    ' テーブル名を指定してレコードセットを作成する
    WSQL = "SELECT * From " & MasName & " where ([端末ID]='" & 端末ID & "')"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic

    Open DatName For Input As #1

    P = 0
    Do While Not EOF(1)
        Line Input #1, str
        P = P + 1
        
        商品コード = Mid(str, 2, 13)
        
        Debug.Print "商品:" & P & ":" & 商品コード
        
        If (Not rec.EOF) Then rec.MoveFirst
        
        rec.Find "([商品コード]='" & 商品コード & "')"
        
        If (rec.EOF) Then rec.AddNew
            rec!端末ID = Zero_fix(4, FldrNo)
            rec!有効区分 = Mid(str, 1, 1)
            rec!商品コード = Mid(str, 2, 13)
            'rec!自社コード = Mid(str, 15, 13)
            rec!自社コード = Mid(str, 15, 12)
            rec!商品漢字名 = CommaChk(Trim(Mid(str, 28, 20)))
            rec!商品カナ名 = CommaChk(Trim(Mid(str, 48, 14)))
            rec!分類コード = Mid(str, 62, 4)
            rec!発注単位 = Mid(str, 66, 3)
            rec!仕入先コード = Mid(str, 69, 6)
            rec!入力区分 = Mid(str, 75, 1)
            rec!原価 = Mid(str, 76, 9)
            rec!売価 = Mid(str, 85, 7)
            rec!端数処理 = Mid(str, 92, 1)
            rec!丸め単位 = Mid(str, 93, 1)
            
            rec!税区分1 = Mid(str, 94, 1)
            rec!適用開始日1 = Mid(str, 95, 8)
            rec!税率1 = Mid(str, 103, 3)
            
            rec!税区分2 = Mid(str, 106, 1)
            rec!適用開始日2 = Mid(str, 107, 8)
            rec!税率2 = Mid(str, 115, 3)
            
            rec!特売開始日1 = Mid(str, 118, 8)
            rec!特売終了日1 = Mid(str, 126, 8)
            rec!特売原価1 = Mid(str, 134, 9)
            rec!特売売価1 = Mid(str, 143, 7)
            
            rec!特売開始日2 = Mid(str, 150, 8)
            rec!特売終了日2 = Mid(str, 158, 8)
            
            rec!特売開始日3 = Mid(str, 166, 8)
            rec!特売終了日3 = Mid(str, 174, 8)
            
        rec.Update
        
        DoEvents
    Loop
    Close (1)
    
    rec.Close
    
    
'*****************************************************************
'   CSVファイルの作成
'*****************************************************************

    Set cFso = New FileSystemObject
    
    If cFso.FileExists(CSVname) Then
        Call cFso.DeleteFile(CSVname)
    End If
    
    Open CSVname For Output As #1
    WSQL = "select * from " & MasName & " where [端末ID]='" & 端末ID & "' order by 商品コード;"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic
    Do Until rec.EOF
        Wstr = Trim(rec!商品コード) & "," & _
               Trim(rec!自社コード) & "," & _
               Trim(rec!商品漢字名) & "," & _
               Trim(rec!商品カナ名) & "," & _
               rec!分類コード & "," & _
               rec!発注単位 & "," & _
               rec!仕入先コード & "," & _
               rec!入力区分 & "," & _
               rec!原価 & "," & _
               rec!売価 & "," & _
               rec!端数処理 & "," & _
               rec!丸め単位 & "," & _
               rec!税区分1 & "," & rec!適用開始日1 & "," & rec!税率1 & "," & _
               rec!税区分2 & "," & rec!適用開始日2 & "," & rec!税率2 & "," & _
               rec!特売開始日1 & "," & rec!特売終了日1 & "," & rec!特売原価1 & "," & rec!特売売価1 & "," & _
               rec!特売開始日2 & "," & rec!特売終了日2 & "," & rec!特売開始日3 & "," & rec!特売終了日3 & "," & _
               rec!有効区分
        Print #1, Wstr
        
        rec.MoveNext
    Loop
    
    rec.Close
    Close (1)

    
    ' 切断
    cnn.Close

End Sub


'***************************************************************
'   自社コードマスタ更新
'***************************************************************
Sub UpdateJisya(ByVal FldrNo As Variant)

    Dim lstrPath As String                  ' DBへのパス
    Dim lobjRS As New ADODB.Recordset       ' レコードセットオブジェクト
    Dim lstrSQL As String
    Dim str As String
    Dim 端末ID, 自社コード As Variant
    Dim WSQL As String
    Dim cFso As FileSystemObject
    Dim FLDRname, DatName, CSVname As String
    Dim Wstr As String
    Dim MasName As String
    
    MasName = "JISYA"
    
    FLDRname = DTPath & Zero_fix(4, FldrNo) & "\rcv"
    DatName = FLDRname & "\" & MasName & ".dat"
    CSVname = FLDRname & "\" & MasName & ".csv"
    
    ' ADOのオブジェクト変数を宣言する
    Dim cnn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    
    ' 接続を確立する
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBname & ";"
             
    端末ID = Zero_fix(4, FldrNo)
    ' テーブル名を指定してレコードセットを作成する
    WSQL = "SELECT * From " & MasName & " where ([端末ID]='" & 端末ID & "')"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic

    Open DatName For Input As #1

    P = 0
    Do While Not EOF(1)
        Line Input #1, str
        P = P + 1
        自社コード = Mid(str, 2, 13)
        Debug.Print "自社:" & P & ":" & 自社コード
        If (Not rec.EOF) Then rec.MoveFirst
        
        rec.Find "([自社コード]='" & 自社コード & "')"
        
        If (rec.EOF) Then rec.AddNew
            rec!端末ID = Zero_fix(4, FldrNo)
            rec!有効区分 = Mid(str, 1, 1)
            'rec!自社コード = Trim(Mid(str, 2, 13))
            rec!自社コード = Trim(Mid(str, 2, 12))
            rec!商品コード = Trim(Mid(str, 15, 13))
            
        rec.Update
        
    Loop
    Close (1)
    
    rec.Close
    
    
'*****************************************************************
'   CSVファイルの作成
'*****************************************************************

    Set cFso = New FileSystemObject
    
    If cFso.FileExists(CSVname) Then
        Call cFso.DeleteFile(CSVname)
    End If
    
    Open CSVname For Output As #1
    WSQL = "select * from " & MasName & " where [端末ID]='" & 端末ID & "' order by 自社コード;"
    rec.Open WSQL, cnn, adOpenKeyset, adLockOptimistic
    Do Until rec.EOF
        Wstr = Trim(rec!自社コード) & "," & _
               Trim(rec!商品コード) & "," & _
               rec!有効区分
        Print #1, Wstr
        
        rec.MoveNext
    Loop
    
    rec.Close
    Close (1)

    
    ' 切断
    cnn.Close

End Sub