IPOS友好终端组批量修改

 由于公司需要，需要变更IPOS系统的友好终端组和IPOS友好终端组的调货方式 

由于此操作非常麻烦 ，工作非常繁琐，特写此模板，批量更新，一键完成

excel 表格式 

共2个表格

目前是每家店铺有2个友好终端组

1、所有终端   权限 为  横调

2、所属友好终端组 权限为 库存查询和横调

上代码

首先把三个表清空

ipos_yhzdz   友好终端组的主表

ipos_yhzdzmx   友好终端组的明细表

ipos_dpyhzd   各店铺友好终端组明细数据，每家店2行数据

我这里ID为9的是所有终端，这个不用改，所以不删除

'设置以下全局变量 
Dim cn As Object
Dim rs As Object

 Sub 数据库连接mysql()
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.RecordSet")
    cn = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=IP地址;Port=3306;Database=数据库名称;User=root; Password=数据库密码;Option=3;"
      cn.Open
 End Sub

Sub 删除原有()
Call 数据库连接mysql
sql = "delete from ipos_yhzdz where id <> 9 "
sql2 = "delete from ipos_yhzdzmx where pid <> 9 "
sql3 = "delete from ipos_dpyhzd"
cn.Execute (sql)
cn.Execute (sql2)
cn.Execute (sql3)
cn.Close

End Sub

接下来执行修改

上代码

Sub 查询区域内所有店铺()
Application.ScreenUpdating = False   '关闭屏幕刷新
Sheets("1").Select     '选择表格1进行工作 
Call 删除原有   
Call 数据库连接mysql
x = 17    '此项自定义，看你的ipos_yhzdz表里面ID到多少，往下一位就可以了，避免重复，主表单据号
For y = 1 To Sheets("sheet2").Cells(2, 256).End(1).Column    '外层循环控制列
d_name = ""    '名称初始化

  Sheets("1").Cells.Clear   '先清空工作表1的数据
    For i = 2 To 20    '这里看你的每个大区有多少区域 包含在里面，内层循环控制行
    If Sheets("sheet2").Cells(i, y) = "" Then    '判断是否有区域名称
    Else
    d_name = d_name & "," & Sheets("sheet2").Cells(i, y)    '备注名称
    
       sql = "select id from com_base_kehu where qy_id in (select id from com_base_quyu where qymc='" & Sheets("sheet2").Cells(i, y) & "') and ty=0"   '查询区域名称对应的商店ID
      If rs.State = 1 Then rs.Close
      rs.Open sql, cn, 1, 1
              If Range("a1") = "" Then
                Sheets("1").Range("A" & [A65536].End(xlUp).Row).CopyFromRecordset rs
              Else
                Sheets("1").Range("A" & [A65536].End(xlUp).Row + 1).CopyFromRecordset rs
              End If   '工作表1列出查询到的ID
        End If
    Next i
time1 = (Now - 70 * 365 - 19) * 86400 - 8 * 3600    '时间戳
sql1 = "INSERT INTO ipos_yhzdz (Id, pid, tn_id, org_id, yhzddm, yhzdmc, bz, row_no, alterdate, lastchanged, yhzd_kzz) VALUES (" & x & ", NULL, '0', '1', 'zdz0000" & x & "', '" & d_name & "', '" & d_name & "', NULL, '" & time1 & "', '" & Now & "', '0')"  
cn.Execute (sql1)   '插入主表单据
    For t = 1 To Sheets("1").[A65536].End(xlUp).Row    '此处开始更新明细表和店铺明细终端组
    sql3 = "INSERT INTO ipos_yhzdzmx (pid, tn_id, zd_id, bz, row_no, alterdate, lastchanged, jtzd, zdjs, zdtj) VALUES ( '" & x & "', '0', '" & Sheets("1").Cells(t, 1) & "', NULL, NULL, '" & time1 & "', '" & Now & "', '1', NULL, NULL)"  
    sql4 = "INSERT INTO ipos_dpyhzd(pid,zdz_id,hd,kc,xs,hdkz,hdthkz,kqyh, yhzd_kzz,ck_cus,mod_cus,ck_vip,mod_vip,ck_vcard,ck_czk)VALUES('" & Sheets("1").Cells(t, 1) & "','" & x & "','1','1','0','0','0','0','0','0','0','0','0','0','0')"   '更新所属区域的查询
    sql5 = "INSERT INTO ipos_dpyhzd(pid,zdz_id,hd,kc,xs,hdkz,hdthkz,kqyh, yhzd_kzz,ck_cus,mod_cus,ck_vip,mod_vip,ck_vcard,ck_czk)VALUES('" & Sheets("1").Cells(t, 1) & "','9','1','0','0','0','0','0','0','0','0','0','0','0','0')"    '更新所有终端的横调
    cn.Execute (sql3)
    cn.Execute (sql4)
    cn.Execute (sql5)
 Next t
x = x + 1    '主表单据号+1
Next y
cn.Close
Sheets("sheet2").Select
Application.ScreenUpdating = True
MsgBox "添加完成！"
End Sub