EC-CUBE 2系、3系で MySQL を使って送料設定できる便利ツール

送料設定用 SQL文をはき出すマクロを組みました。EC-CUBE 2系、3系、両方に対応させています。応急措置ですが、何かの役に立つかもしれませんので、こちらで公開しておきます。

1 ネットショップの送料更新が大変
2 送料更新用 SQL文作成ツール
3 EC-CUBE 送料設定ツール実行にあたっての注意事項

ネットショップの送料更新が大変

ヤマト運輸さんが宅急便の送料を 2017年10月から値上げするということで、送料の更新作業に追われているネットショップさんも多いのではないでしょうか？
送料の設定って結構手間がかかる作業で、EC-CUBE も例外ではありません。

料金表を読み込んで一発で変換するツールを作れば良いのでしょうが、そんな時間もないので応急処置で送料を更新するSQL文をはき出すマクロを組みましたので公開しておきます。改造すれば、送料を新規に設定する場合でも使えると思います。

送料更新用 SQL文作成ツール

色々手動ですが、次の手順で使います。EC-CUBE の管理画面から都道府県ごとに送料を入力するよりは、各段に作業が早いしミスも減らせます。

Excel で送料の表を作る

新規に Excelブックを開き、sheet1 に次のような送料の表を作ります。

サイズ名	北海道	北東北	南東北	関東	信越	北陸	中部	関西	中国	四国	九州	沖縄	配送業者ID
60	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	1
80	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	2
100	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	3
クール便60	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	4
ネコポス	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	xx	5

Excel の Visual Basic Editor を開く

Excel の開発タブから Visual Basic Editor を起動させ、標準モジュールを追加して、以下のコードをコピペします。Sub Main の１行目の Const Syubetu に、EC-CUBE 2系なら「2kei Update」3系なら「3kei Update」の文字列をセットします。機会があれば、Insert も作る予定なので。。。

Sub Main()
    Const FileName = "\delive-fee.txt"
    Const Syubetu = "2kei Update"

    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim fee(47) As Currency
    Dim s As String
    Dim feeId As Integer
    Dim row As Integer
    Dim deliveID As Integer
    
    ' ファイル指定
    Set ts = fso.CreateTextFile( _
        FileName:=ThisWorkbook.Path &amp; FileName, _
        Overwrite:=True)

    '種別
    Select Case Syubetu
        Case "2kei Update"
            For row = 2 To ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).row
                Call SubProcedure(row, fee(), deliveID)
                
                For feeId = 1 To 47
                    s = "UPDATE `dtb_delivfee` SET `fee` = " &amp; fee(feeId) &amp; " WHERE `deliv_id` = " &amp; deliveID &amp; " and `fee_id` =" &amp; feeId &amp; ";"
                    ts.WriteLine s
                Next
            Next
        Case "3kei Update"
            For row = 2 To ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).row
                Call SubProcedure(row, fee(), deliveID)
                
                For feeId = 1 To 47
                    s = "UPDATE `dtb_delivery_fee` SET `fee` = " &amp; fee(feeId) &amp; " WHERE `delivery_id` = " &amp; deliveID &amp; " and `pref` =" &amp; feeId &amp; ";"
                    ts.WriteLine s
                Next
            Next
    End Select
    
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    
    MsgBox "終了しました"

End Sub

'料金表１行分の処理
Sub SubProcedure(row As Integer, fee() As Currency, deliveID)
    Dim Area(12) As Currency
    Dim col As Integer  '料金表の地域の列
    Dim feeId As Integer
    Dim areaId As Integer
    
    '地域の列のスタート位置
    col = 2     'B列
    
    '地域別サイズ別送料をセット
    For areaId = 1 To 12
        Area(areaId) = ThisWorkbook.Worksheets(1).Cells(row, col).Value
        col = col + 1
    Next areaId
    
    '配送業者ID
    deliveID = ThisWorkbook.Worksheets(1).Cells(row, col).Value
       
    '北海道
    fee(1) = Area(1)

    '北東北
    fee(2) = Area(2)
    fee(3) = Area(2)
    fee(5) = Area(2)    '秋田県
    
    '南東北
    fee(4) = Area(3)    '宮城県
    fee(6) = Area(3)
    fee(7) = Area(3)
    
    '関東
    For feeId = 8 To 14
        fee(feeId) = Area(4)
    Next
    fee(19) = Area(4)   '山梨県
    
    '信越
    fee(15) = Area(5)
    fee(20) = Area(5)
    
    '北陸
    For feeId = 16 To 18
        fee(feeId) = Area(6)
    Next
    
    '中部
    For feeId = 21 To 24
        fee(feeId) = Area(7)
    Next
    
    '関西
    For feeId = 25 To 30
        fee(feeId) = Area(8)
    Next
    
    '中国
    For feeId = 31 To 35
        fee(feeId) = Area(9)
    Next
    
    '四国
    For feeId = 36 To 39
        fee(feeId) = Area(10)
    Next
    
    '九州
    For feeId = 40 To 46
        fee(feeId) = Area(11)
    Next

    '沖縄
    fee(47) = Area(12)

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

Sub Main()

Const FileName = "\delive-fee.txt"

Const Syubetu = "2kei Update"

Dim fso As New FileSystemObject

Dim ts As TextStream

Dim fee(47) As Currency

Dim s As String

Dim feeId As Integer

Dim row As Integer

Dim deliveID As Integer

' ファイル指定

Set ts = fso.CreateTextFile( _

FileName:=ThisWorkbook.Path & FileName, _

Overwrite:=True)

'種別

Select Case Syubetu

Case "2kei Update"

For row = 2 To ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).row

Call SubProcedure(row, fee(), deliveID)

For feeId = 1 To 47

s = "UPDATE `dtb_delivfee` SET `fee` = " & fee(feeId) & " WHERE `deliv_id` = " & deliveID & " and `fee_id` =" & feeId & ";"

ts.WriteLine s

Case "3kei Update"

For row = 2 To ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).row

Call SubProcedure(row, fee(), deliveID)

For feeId = 1 To 47

s = "UPDATE `dtb_delivery_fee` SET `fee` = " & fee(feeId) & " WHERE `delivery_id` = " & deliveID & " and `pref` =" & feeId & ";"

ts.WriteLine s

End Select

ts.Close

Set ts = Nothing

Set fso = Nothing

MsgBox "終了しました"

End Sub

'料金表１行分の処理

Sub SubProcedure(row As Integer, fee() As Currency, deliveID)

Dim Area(12) As Currency

Dim col As Integer '料金表の地域の列

Dim feeId As Integer

Dim areaId As Integer

'地域の列のスタート位置

col = 2 'B列

'地域別サイズ別送料をセット

For areaId = 1 To 12

Area(areaId) = ThisWorkbook.Worksheets(1).Cells(row, col).Value

col = col + 1

Next areaId

'配送業者ID

deliveID = ThisWorkbook.Worksheets(1).Cells(row, col).Value

'北海道

fee(1) = Area(1)

'北東北

fee(2) = Area(2)

fee(3) = Area(2)

fee(5) = Area(2) '秋田県

'南東北

fee(4) = Area(3) '宮城県

fee(6) = Area(3)

fee(7) = Area(3)

'関東

For feeId = 8 To 14

fee(feeId) = Area(4)

fee(19) = Area(4) '山梨県

'信越

fee(15) = Area(5)

fee(20) = Area(5)

'北陸

For feeId = 16 To 18

fee(feeId) = Area(6)

'中部

For feeId = 21 To 24

fee(feeId) = Area(7)

'関西

For feeId = 25 To 30

fee(feeId) = Area(8)

'中国

For feeId = 31 To 35

fee(feeId) = Area(9)

'四国

For feeId = 36 To 39

fee(feeId) = Area(10)

'九州

For feeId = 40 To 46

fee(feeId) = Area(11)

'沖縄

fee(47) = Area(12)

End Sub

実行すると生成される SQL文

マクロを実行すると、Excel ブックの保存先に delive-fee.txt が作成されます。Main Sub の Const でファイル名と保管場所は適宜変更してください。

ちなみに、EC-CUBE 2系と EC-CUBE 3系の送料テーブルの構造は以下のとおり違いがありますので、書き出される Update 文も、Const Syubetu が、EC-CUBE 2系か 3系で分けています。

例えば、2系は生成されるのは、EC-CUBE の dtb_delivefee テーブル用 MySQL の UPDATE 文になります。47都道府県別の送料を Excel の表から読み込み各行に埋め込んでいます。deliv_id と fee_id（都道府県の pref_idに相当）が Key になります。

	テーブル名	カラム名	キー
EC-CUBE 2.13系	dtb_delivfee	deliv_id 配送業者 ID fee_id 配送料 ID fee 配送料 pref 都道府県ID	deliv_id と fee_id の複合主キー fee_id は pref と同じになっている
EC-CUBE 3系	dtb_delivery_fee	fee_id 配送料ID delivery_id 配送業者 ID pref 都道府県ID fee 配送料	fee_id が主キー delivery_id と pref はインデックス

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =1;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =2;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =3;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =4;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =5;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =6;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =7;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =8;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =9;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =10;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =11;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =12;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =13;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =14;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =15;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =16;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =17;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =18;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =19;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =20;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =21;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =22;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =23;
UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =24;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =25;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =26;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =27;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =28;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =29;
UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =30;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =31;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =32;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =33;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =34;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =35;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =36;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =37;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =38;
UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =39;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =40;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =41;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =42;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =43;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =44;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =45;
UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =46;
UPDATE `dtb_delivfee` SET `fee` = 1447 WHERE `deliv_id` = 1 and `fee_id` =47;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =1;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =2;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =3;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =4;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =5;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =6;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =7;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =8;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =9;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =10;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =11;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =12;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =13;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =14;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =15;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =16;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =17;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =18;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =19;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =20;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =21;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =22;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =23;

UPDATE `dtb_delivfee` SET `fee` = 907 WHERE `deliv_id` = 1 and `fee_id` =24;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =25;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =26;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =27;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =28;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =29;

UPDATE `dtb_delivfee` SET `fee` = 1015 WHERE `deliv_id` = 1 and `fee_id` =30;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =31;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =32;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =33;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =34;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =35;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =36;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =37;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =38;

UPDATE `dtb_delivfee` SET `fee` = 1123 WHERE `deliv_id` = 1 and `fee_id` =39;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =40;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =41;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =42;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =43;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =44;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =45;

UPDATE `dtb_delivfee` SET `fee` = 1339 WHERE `deliv_id` = 1 and `fee_id` =46;

UPDATE `dtb_delivfee` SET `fee` = 1447 WHERE `deliv_id` = 1 and `fee_id` =47;

phpMyAdmin を起動させて、SQL文を実行する

phpMyAdmin を起動させて、生成された SQL を実行させます。正しく送料がセットされたかどうか、EC-CUBE の管理画面で確認してください。

簡単ですね　(＾◇＾)

EC-CUBE 送料設定ツール実行にあたっての注意事項

お約束ですが、このツールの実行は自己責任でお願いします。万一、不具合が発生しても責任は負いかねますのでご了承ください。実行にあたっての注意点は以下のとおりです。

必ず、データベースのバックアップをとってから実行させてください
Excel VBA を実行させる前に、必ず Excel ブックを保存しましょう
phpMyAdmin や Excelマクロに詳しくない方は使わない方が無難です

このツールを使うのが大変という方、お仕事しますのでご連絡ください。
もちろん、マクロ制作も承ります (^^)/

IT駒ヶ根お問い合わせフォームはこちら

Nahoko

長野県駒ヶ根市在住。ネットショップ構築とネットショップ運営サポートをしています。このサイトでは、ユーザーさん向けに役立つIT情報や、技術情報のメモを公開しています。詳しいプロフィール