1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
| public class OFB
{
public OFB()
{
}
public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("org.gjt.mm.mysql.Driver");
local = DriverManager.getConnection("jdbc:odbc:OFB", "gebruikersnaam", "wachtwoord");
remote = DriverManager.getConnection("jdbc:mysql://localhost", "root", "wachtwoord");
Statement statement = local.createStatement();
Statement statement1 = remote.createStatement();
System.out.println("[Categories]");
System.out.print("Cleaning up remote categories... ");
statement1.executeUpdate("delete from category");
statement1.executeUpdate("alter table category AUTO_INCREMENT=1");
System.out.println("done");
System.out.print("Import main categories... ");
ResultSet resultset = statement.executeQuery("SELECT * FROM tblObjectGroep where obgType=0");
int i = 0;
PreparedStatement preparedstatement = remote.prepareStatement("INSERT INTO category(pid,OFB,name,visible,volgnr) values(?,?,?,?,?)");
while(resultset.next())
{
preparedstatement.setNull(1, 4);
preparedstatement.setInt(2, resultset.getInt("obgId"));
preparedstatement.setString(3, resultset.getString("obgNaam"));
preparedstatement.setInt(4, 1);
preparedstatement.setInt(5, resultset.getInt("obgVolgNr"));
preparedstatement.executeUpdate();
i++;
}
System.out.println("done (" + i + ")");
System.out.print("Import 1st level subcategories... ");
resultset = statement.executeQuery("SELECT * FROM [tbl artikel subgroep]");
i = 0;
do
{
if(!resultset.next())
break;
String s = resultset.getString("artsgOmsch");
if(s != null && s.length() != 0)
{
ResultSet resultset1 = statement1.executeQuery("select id from category where OFB=" + resultset.getInt("artsgObgId"));
if(!resultset1.next())
{
resultset1.close();
} else
{
preparedstatement.setInt(1, resultset1.getInt(1));
preparedstatement.setInt(2, resultset.getInt("artsgId"));
preparedstatement.setString(3, s);
preparedstatement.setInt(4, resultset.getInt("artSgInWeb"));
preparedstatement.setInt(5, 0);
preparedstatement.executeUpdate();
resultset1.close();
i++;
}
}
} while(true);
System.out.println("done (" + i + ")");
System.out.print("Import 2nd level subcategories... ");
resultset = statement.executeQuery("SELECT * FROM tblArtikelSsg");
i = 0;
do
{
if(!resultset.next())
break;
String s1 = resultset.getString("ssgNaam");
if(s1 != null && s1.length() != 0)
{
ResultSet resultset2 = statement1.executeQuery("select id from category where OFB=" + resultset.getInt("ssgArtsgId") + " and pid<=10");
if(!resultset2.next())
{
resultset2.close();
} else
{
preparedstatement.setInt(1, resultset2.getInt(1));
preparedstatement.setInt(2, resultset.getInt("ssgId"));
preparedstatement.setString(3, s1);
preparedstatement.setInt(4, 1);
preparedstatement.setInt(5, resultset.getInt("ssgVolgNr"));
preparedstatement.executeUpdate();
resultset2.close();
i++;
}
}
} while(true);
System.out.println("done (" + i + ")");
System.out.println("\n[Products]");
System.out.print("Cleaning up remote products... ");
statement1.executeUpdate("delete from import");
statement1.executeUpdate("alter table import AUTO_INCREMENT=1");
statement1.executeUpdate("delete from product");
statement1.executeUpdate("alter table product AUTO_INCREMENT=1");
System.out.println("done");
System.out.print("Import products... ");
preparedstatement = remote.prepareStatement("INSERT INTO import values(?,?,?,?,?,?,?,?,?,?,?,?)");
resultset = statement.executeQuery("SELECT * FROM [tbl artikel]");
i = 0;
do
{
if(!resultset.next())
break;
String s2 = resultset.getString("artOmsComm");
if(s2 != null && s2.length() != 0)
{
preparedstatement.setInt(1, resultset.getInt("artId"));
preparedstatement.setString(2, s2);
preparedstatement.setString(3, resultset.getString("artOms"));
preparedstatement.setString(4, resultset.getString("artNr"));
preparedstatement.setString(5, resultset.getString("artOle"));
preparedstatement.setInt(6, resultset.getInt("artArtsgId"));
preparedstatement.setFloat(7, resultset.getFloat("artVerkpPrijsInc"));
preparedstatement.setInt(8, resultset.getInt("artArtsId"));
preparedstatement.setInt(9, resultset.getInt("artInWEB"));
preparedstatement.setInt(10, 0);
preparedstatement.setFloat(11, resultset.getFloat("artOrdVerzKosten"));
preparedstatement.setInt(12, resultset.getInt("artSsgId"));
preparedstatement.executeUpdate();
i++;
}
} while(true);
System.out.println("done (" + i + ")");
System.out.print(" > Combining imported products... ");
statement1.executeUpdate("INSERT INTO product(OFB,cid,artid,name,price,description,img,vis) SELECT i.id, c.id as cid, i.artnum, i.name, i.prijs_ex, i.description, i.photo, i.prijslijst FROM import i, category c WHERE(i.subcatid is null OR i.subcatid <= 0) AND(c.pid >= 1 AND c.pid <= 10) AND c.OFB = i.catid AND i.artsid = 1");
statement1.executeUpdate("INSERT INTO product(OFB,cid,artid,name,price,description,img,vis) SELECT i.id, c.id as cid, i.artnum, i.name, i.prijs_ex, i.description, i.photo, i.prijslijst FROM import i, category c WHERE i.subcatid>0 AND c.pid>10 AND c.OFB=i.subcatid AND i.artsid=1");
System.out.println("done");
System.out.println("\n[Configurations]");
System.out.print("Cleaning up remote configurations... ");
statement1.executeUpdate("delete from importcnf");
statement1.executeUpdate("alter table importcnf AUTO_INCREMENT=1");
statement1.executeUpdate("delete from configuration");
statement1.executeUpdate("alter table configuration AUTO_INCREMENT=1");
System.out.println("done");
System.out.print("Import configurations... ");
preparedstatement = remote.prepareStatement("INSERT INTO importcnf values(?,?,?,?,?,?,?,?,?)");
resultset = statement.executeQuery("SELECT * FROM [tbl configuratie]");
for(i = 0; resultset.next(); i++)
{
String s3 = resultset.getString("cnfOms");
preparedstatement.setInt(1, resultset.getInt("cnfId"));
preparedstatement.setString(2, s3);
preparedstatement.setString(3, resultset.getString("cnfOmsComm"));
preparedstatement.setString(4, resultset.getString("cnfKopTekst"));
preparedstatement.setString(5, resultset.getString("cnfFile"));
preparedstatement.setInt(6, resultset.getInt("cnfObgId"));
preparedstatement.setInt(7, resultset.getInt("cnfOfgId"));
preparedstatement.setInt(8, resultset.getInt("cnfMargeWens"));
preparedstatement.setInt(9, resultset.getInt("cnfMargeOpt"));
preparedstatement.executeUpdate();
}
System.out.println("done (" + i + ")");
System.out.print(" > Combining imported configurations... ");
statement1.executeUpdate("INSERT INTO configuration(OFB, cid, name, oms, details,img) SELECT i.id, c.id, i.oms, i.omscomm, i.koptekst,i.foto FROM importcnf i, category c WHERE c.pid IS NULL AND \tc.OFB=i.groep");
System.out.println("done");
System.out.print("Cleaning up remote config articles... ");
statement1.executeUpdate("delete from importcnfart");
statement1.executeUpdate("alter table importcnfart AUTO_INCREMENT=1");
statement1.executeUpdate("delete from conf_part");
statement1.executeUpdate("alter table conf_part AUTO_INCREMENT=1");
System.out.println("done");
System.out.print("Import config articles... ");
preparedstatement = remote.prepareStatement("INSERT INTO importcnfart values(?,?,?,?,?,?,?,?)");
resultset = statement.executeQuery("SELECT * FROM [tbl configuratie artikelen]");
for(i = 0; resultset.next(); i++)
{
preparedstatement.setInt(1, resultset.getInt("caCnfId"));
preparedstatement.setInt(2, resultset.getInt("caArtId"));
preparedstatement.setString(3, resultset.getString("caType"));
preparedstatement.setInt(4, resultset.getInt("caVolgnr"));
preparedstatement.setInt(5, resultset.getInt("caArtsgId"));
preparedstatement.setInt(6, resultset.getInt("caArtIdIpv"));
preparedstatement.setInt(7, resultset.getInt("caAantal"));
preparedstatement.setString(8, resultset.getString("caOmsExtra"));
preparedstatement.executeUpdate();
}
System.out.println("done (" + i + ")");
System.out.print(" > Combining imported config articles...");
statement1.executeUpdate("INSERT INTO conf_part(cid, pid, type, num,name,price,vis,volgnr,cat) SELECT c.id, p.id, i.type, i.aantal, p.name, p.price, p.vis,i.volgnr,i.catnaam FROM configuration c, importcnfart i, product p WHERE c.OFB=i.cnfid AND p.OFB=i.artid AND i.type<>'M'");
statement1.executeUpdate("INSERT INTO conf_part(cid, pid, type, rep, num,name,price,vis,volgnr,cat) SELECT c.id, p1.id, i.type, p2.id, i.aantal, p1.name, p1.price, p1.vis,i.volgnr,i.catnaam FROM configuration c, importcnfart i, product p1, product p2 WHERE c.OFB=i.cnfid AND p1.OFB=i.artid AND p2.OFB=i.ipv AND i.type='M'");
System.out.println("done\n\n");
}
catch(Exception exception)
{
System.out.println(exception);
exception.printStackTrace();
}
finally
{
try
{
local.close();
remote.close();
}
catch(SQLException sqlexception)
{
sqlexception.printStackTrace();
}
}
}
private static Connection local;
private static Connection remote;
} |
/u/63681/soia.png?f=community)
:strip_icc():strip_exif()/u/10526/avatar.jpg?f=community){kind=avatar}
/u/56000/neuken.png?f=community)
en dat geeft dus problemen
:strip_icc():strip_exif()/u/6697/gerco-icon.jpg?f=community){kind=icon}
:strip_exif()/u/13818/episodebutler_60x60.gif?f=community)
/u/26735/crop5b6e03049ac12.png?f=community)
