OpenConcerto

Dépôt officiel du code source de l'ERP OpenConcerto
sonarqube

svn://code.openconcerto.org/openconcerto

Rev

Rev 140 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
140 ilm 1
package org.openconcerto.modules.customerrelationship.lead.importer;
2
 
3
import java.io.BufferedReader;
4
import java.io.File;
5
import java.io.FileInputStream;
6
import java.io.IOException;
7
import java.io.InputStreamReader;
8
import java.nio.charset.Charset;
9
import java.util.ArrayList;
10
import java.util.HashMap;
11
import java.util.List;
12
import java.util.Map;
13
import java.util.Set;
14
 
15
import org.openconcerto.erp.importer.DataImporter;
16
import org.openconcerto.openoffice.ContentType;
17
import org.openconcerto.openoffice.ODPackage;
18
import org.openconcerto.openoffice.spreadsheet.Cell;
19
import org.openconcerto.openoffice.spreadsheet.Sheet;
20
import org.openconcerto.openoffice.spreadsheet.SpreadSheet;
21
import org.openconcerto.sql.model.DBRoot;
181 ilm 22
import org.openconcerto.sql.model.SQLRow;
23
import org.openconcerto.sql.model.SQLRowAccessor;
24
import org.openconcerto.sql.model.SQLRowListRSH;
140 ilm 25
import org.openconcerto.sql.model.SQLRowValues;
26
import org.openconcerto.sql.model.SQLRowValuesCluster;
181 ilm 27
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
140 ilm 28
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
181 ilm 29
import org.openconcerto.sql.model.SQLSelect;
30
import org.openconcerto.sql.model.SQLTable;
140 ilm 31
import org.openconcerto.utils.cc.IdentityHashSet;
32
import org.openconcerto.utils.text.CSVReader;
33
import org.openconcerto.utils.text.CharsetHelper;
34
 
35
public class LeadImporter {
36
 
37
    abstract class CSVImporter<T extends LineCSV> {
38
 
39
        public Map<Object, T> importFrom(File csvFile) throws IOException {
40
 
41
            Map<Object, T> map = new HashMap<Object, T>();
42
 
43
            Charset cs = CharsetHelper.guessEncoding(csvFile, 4096, Charset.forName("Cp1252"));
44
 
45
            BufferedReader r = new BufferedReader(new InputStreamReader(new FileInputStream(csvFile), cs));
46
            String l = r.readLine();
47
            if (l == null) {
48
                r.close();
49
                return null;
50
            }
51
            char separator = ',';
52
            int cCount = 0;
53
            int scCount = 0;
54
            for (int i = 0; i < l.length(); i++) {
55
                char c = l.charAt(i);
56
                if (c == ',') {
57
                    cCount++;
58
                } else if (c == ';') {
59
                    scCount++;
60
                }
61
            }
62
            r.close();
63
            if (scCount > cCount) {
64
                separator = ';';
65
            }
66
 
67
            final CSVReader csvReader = new CSVReader(new InputStreamReader(new FileInputStream(csvFile), cs), separator);
68
            final List<String[]> lines = csvReader.readAll();
69
            final int rowCount = lines.size();
70
            int start = 0;
71
 
72
            for (int i = start; i < rowCount; i++) {
73
                final String[] values = lines.get(i);
74
                final T convertedLine = convert(values);
75
                map.put(convertedLine.getId(), convertedLine);
76
            }
77
            csvReader.close();
78
            return map;
79
        }
80
 
81
        public abstract T convert(String[] line);
82
    }
83
 
181 ilm 84
    public Map<Object, LeadCSV> exportLeads(SQLTable tableLead, File dir2save, File sheetFile) throws Exception {
85
        List<String[]> adresse = new ArrayList<String[]>();
86
        List<String[]> leadList = new ArrayList<String[]>();
87
 
88
        Map<Object, LeadCSV> leadMap = new HashMap<Object, LeadCSV>();
89
 
90
        SQLSelect sel = new SQLSelect();
91
        sel.addSelectStar(tableLead);
92
        List<SQLRow> leads = SQLRowListRSH.execute(sel);
93
        int i = 1;
94
        for (SQLRow lead : leads) {
95
 
96
            int idAdr = adresse.size();
97
            AdresseCSV adr = createAdresse(i, lead.getForeign("ID_ADRESSE"));
98
            adresse.add(adr.toCSVLine());
99
            LeadCSV leadCsv = createLeadFromRow(i, lead, Integer.valueOf(adr.getId().toString()));
100
            leadList.add(leadCsv.toCSVLine());
101
            leadMap.put(leadCsv.getId(), leadCsv);
102
            i++;
103
        }
104
 
105
        DataImporter importer = new DataImporter(tableLead);
106
        final File csvFile = new File(dir2save, "Lead.csv");
107
        csvFile.createNewFile();
108
        importer.exportModelToCSV(csvFile, leadList);
109
        DataImporter importerAdr = new DataImporter(tableLead.getForeignTable("ID_ADRESSE"));
110
        final File csvFile2 = new File(dir2save, "Address.csv");
111
        csvFile2.createNewFile();
112
        importerAdr.exportModelToCSV(csvFile2, adresse);
113
 
114
        return leadMap;
115
    }
116
 
117
    public AdresseCSV createAdresse(int i, SQLRow rowAdr) {
118
 
119
        String street = rowAdr.getString("RUE");
120
        final String ville = rowAdr.getString("VILLE");
121
        final String cp = rowAdr.getString("CODE_POSTAL");
122
 
123
        AdresseCSV adrLine = new AdresseCSV(i, street, ville, cp);
124
 
125
        return adrLine;
126
    }
127
 
128
    public LeadCSV createLeadFromRow(int i, SQLRowAccessor row, int idAdr) {
129
 
130
        LeadCSV leadLine = new LeadCSV(i, row.getString("COMPANY"), "");
131
 
132
        leadLine.setIdAdr(idAdr);
133
 
134
        leadLine.setPhone(row.getString("PHONE"));
135
        leadLine.setMail(row.getString("EMAIL"));
136
        leadLine.setCell(row.getString("MOBILE"));
137
        leadLine.setFax(row.getString("FAX"));
138
        leadLine.setContact(row.getString("NAME"));
139
        leadLine.setLocalisation(row.getString("LOCALISATION"));
140
        leadLine.setSecteur(row.getString("INDUSTRY"));
141
        leadLine.setEffectif(String.valueOf(row.getInt("EMPLOYEES")));
142
        leadLine.setOrigine(row.getString("SOURCE"));
143
 
144
        leadLine.setSiret(row.getString("SIRET"));
145
        leadLine.setApe(row.getString("APE"));
146
 
147
        leadLine.setNom(row.getString("NAME"));
148
        leadLine.setPrenom(row.getString("FIRSTNAME"));
149
        leadLine.setDesc(row.getString("INFORMATION"));
150
        // rowVals.put("REVENUE", (getBudget().trim().length()==0?0:Integer);
151
        leadLine.setDispo(row.getString("DISPO"));
152
        leadLine.setTypeT(row.getString("INDUSTRY"));
153
        leadLine.setStatut(row.getString("STATUS"));
154
        leadLine.setInfos(row.getString("INFOS"));
155
 
156
        return leadLine;
157
 
158
    }
159
 
140 ilm 160
    public LeadCSV createLead(int i, Sheet sheet, int idAdr, int id) {
161
        final Cell<SpreadSheet> cell0 = sheet.getImmutableCellAt(0, i);
162
        final String societeName = cell0.getValue().toString().trim();
163
        final Cell<SpreadSheet> cell1 = sheet.getImmutableCellAt(1, i);
164
        final String loc = cell1.getValue().toString().trim();
165
 
166
        LeadCSV leadLine = new LeadCSV(id, societeName, loc);
167
        leadLine.setIdAdr(idAdr);
168
 
169
        final Cell<SpreadSheet> cell2 = sheet.getImmutableCellAt(2, i);
170
        final String siret = cell2.getValue().toString().trim();
171
        leadLine.setSiret(siret);
172
 
173
        final Cell<SpreadSheet> cell3 = sheet.getImmutableCellAt(3, i);
174
        final String ape = cell3.getValue().toString().trim();
175
        leadLine.setApe(ape);
176
 
177
        final Cell<SpreadSheet> cell4 = sheet.getImmutableCellAt(4, i);
178
        final String tel = cell4.getValue().toString().trim();
179
        leadLine.setPhone(tel);
180
 
181
        final Cell<SpreadSheet> cell5 = sheet.getImmutableCellAt(5, i);
182
        final String fax = cell5.getValue().toString().trim();
183
        leadLine.setFax(fax);
184
 
185
        final Cell<SpreadSheet> cell10 = sheet.getImmutableCellAt(10, i);
186
        final String secteur = cell10.getValue().toString().trim();
187
        leadLine.setSecteur(secteur);
188
 
189
        final Cell<SpreadSheet> cell11 = sheet.getImmutableCellAt(11, i);
190
        final String effectif = cell11.getValue().toString().trim();
191
        leadLine.setEffectif(effectif);
192
 
193
        final Cell<SpreadSheet> cell12 = sheet.getImmutableCellAt(12, i);
194
        final String origine = cell12.getValue().toString().trim();
195
        leadLine.setOrigine(origine);
196
 
197
        // final Cell<SpreadSheet> cell13 = sheet.getImmutableCellAt(13, i);
198
        // final String com = cell13.getValue().toString().trim();
199
        // leadLine.setCom(com);
200
        //
201
        final Cell<SpreadSheet> cell14 = sheet.getImmutableCellAt(14, i);
202
        final String statut = cell14.getValue().toString().trim();
203
        leadLine.setStatut(statut);
204
 
205
        final Cell<SpreadSheet> cell15 = sheet.getImmutableCellAt(15, i);
206
        final String prenom = cell15.getValue().toString().trim();
207
        leadLine.setPrenom(prenom);
208
 
209
        final Cell<SpreadSheet> cell16 = sheet.getImmutableCellAt(16, i);
210
        final String nom = cell16.getValue().toString().trim();
211
        leadLine.setNom(nom);
212
 
213
        final Cell<SpreadSheet> cell17 = sheet.getImmutableCellAt(17, i);
214
        final String civi = cell17.getValue().toString().trim();
215
        leadLine.setCivilite(civi);
216
 
217
        final Cell<SpreadSheet> cell18 = sheet.getImmutableCellAt(18, i);
218
        final String mail = cell18.getValue().toString().trim();
219
        leadLine.setMail(mail);
220
 
221
        final Cell<SpreadSheet> cell19 = sheet.getImmutableCellAt(19, i);
222
        final String cell = cell19.getValue().toString().trim();
223
        leadLine.setCell(cell);
224
 
225
        final Cell<SpreadSheet> cell20 = sheet.getImmutableCellAt(20, i);
226
        final String infos = cell20.getValue().toString().trim();
227
        leadLine.setInfos(infos);
228
 
229
        final Cell<SpreadSheet> cell21 = sheet.getImmutableCellAt(21, i);
230
        final String desc = cell21.getValue().toString().trim();
231
        leadLine.setDesc(desc);
232
 
233
        final Cell<SpreadSheet> cell22 = sheet.getImmutableCellAt(22, i);
234
        final String conv = cell22.getValue().toString().trim();
235
        leadLine.setConverti(conv);
236
 
237
        final Cell<SpreadSheet> cell23 = sheet.getImmutableCellAt(23, i);
238
        final String budget = cell23.getValue().toString().trim();
239
        leadLine.setBudget(budget);
240
 
241
        final Cell<SpreadSheet> cell24 = sheet.getImmutableCellAt(24, i);
242
        final String com = cell24.getValue().toString().trim();
243
        leadLine.setCom(com);
244
 
245
        final Cell<SpreadSheet> cell25 = sheet.getImmutableCellAt(25, i);
246
        final String dispo = cell25.getValue().toString().trim();
247
        leadLine.setDispo(dispo);
248
 
249
        final Cell<SpreadSheet> cell26 = sheet.getImmutableCellAt(26, i);
250
        final String cat = cell26.getValue().toString().trim();
251
        leadLine.setCategorie(cat);
252
 
253
        final Cell<SpreadSheet> cell27 = sheet.getImmutableCellAt(27, i);
254
        final String type = cell27.getValue().toString().trim();
255
        leadLine.setTypeT(type);
256
 
257
        return leadLine;
258
    }
259
 
260
    public AdresseCSV createAdresse(int i, Sheet sheet, int id) {
261
        final Cell<SpreadSheet> cell6 = sheet.getImmutableCellAt(6, i);
262
        String street = cell6.getValue().toString().trim();
263
 
264
        final Cell<SpreadSheet> cell7 = sheet.getImmutableCellAt(7, i);
265
        final String street2 = cell7.getValue().toString().trim();
266
        if (street2 != null && street2.trim().length() > 0) {
267
            street += "\n" + street2;
268
        }
269
 
270
        final Cell<SpreadSheet> cell8 = sheet.getImmutableCellAt(8, i);
271
        final String ville = cell8.getValue().toString().trim();
272
        final Cell<SpreadSheet> cell9 = sheet.getImmutableCellAt(9, i);
273
        final String cp = cell9.getValue().toString().trim();
274
 
275
        AdresseCSV adrLine = new AdresseCSV(id, street, ville, cp);
276
 
277
        return adrLine;
278
    }
279
 
280
    public Map<Object, LeadCSV> exportLead(DBRoot root, File dir2save, File sheetFile) throws Exception {
281
 
282
        List<String[]> adresse = new ArrayList<String[]>();
283
        List<String[]> leadList = new ArrayList<String[]>();
284
 
285
        Map<Object, LeadCSV> leadMap = new HashMap<Object, LeadCSV>();
286
 
287
        final ODPackage pkg = new ODPackage(sheetFile);
288
        if (pkg.getContentType().getType() != ContentType.SPREADSHEET)
289
            throw new IOException("Pas un tableur");
290
        final SpreadSheet calc = pkg.getSpreadSheet();
291
        final Sheet commandesSheet = calc.getSheet(0);
292
        int sheetCount = commandesSheet.getRowCount();
293
        for (int s = 1; s < sheetCount; s++) {
294
 
295
            final Cell<SpreadSheet> cell0 = commandesSheet.getImmutableCellAt(0, s);
296
            final String societeName = cell0.getValue().toString().trim();
297
            final Cell<SpreadSheet> cell16 = commandesSheet.getImmutableCellAt(16, s);
298
            final String name = cell16.getValue().toString().trim();
299
            if (societeName.trim().length() == 0 && name.trim().length() == 0) {
300
                break;
301
            }
302
            // Adresse principale
303
            int idAdr = adresse.size();
304
            AdresseCSV adr = createAdresse(s, commandesSheet, idAdr);
305
            adresse.add(adr.toCSVLine());
306
            LeadCSV lead = createLead(s, commandesSheet, idAdr, leadList.size());
307
            leadList.add(lead.toCSVLine());
308
            leadMap.put(lead.getId(), lead);
309
        }
310
 
311
        DataImporter importer = new DataImporter(root.getTable("LEAD"));
312
        final File csvFile = new File(dir2save, "Lead.csv");
313
        csvFile.createNewFile();
314
        importer.exportModelToCSV(csvFile, leadList);
315
        DataImporter importerAdr = new DataImporter(root.getTable("ADRESSE"));
316
        final File csvFile2 = new File(dir2save, "Address.csv");
317
        csvFile2.createNewFile();
318
        importerAdr.exportModelToCSV(csvFile2, adresse);
319
 
320
        return leadMap;
321
    }
322
 
323
    public void importFromFile(File csvFileDir, DBRoot root) throws Exception {
324
 
325
        SQLRowValues rowValsLead = new SQLRowValues(root.getTable("LEAD"));
326
        rowValsLead.put("COMPANY", null);
327
        rowValsLead.put("APE", null);
328
        rowValsLead.putRowValues("ID_ADRESSE").putNulls("VILLE", "RUE");
329
        SQLRowValuesListFetcher fecther = SQLRowValuesListFetcher.create(rowValsLead);
330
        List<SQLRowValues> existingLeads = fecther.fetch();
331
        List<String> existingHashLead = new ArrayList<String>(existingLeads.size());
332
 
333
        for (SQLRowValues sqlRowValues : existingLeads) {
334
            existingHashLead.add(sqlRowValues.getString("APE") + "----" + sqlRowValues.getString("COMPANY") + "----" + sqlRowValues.getForeign("ID_ADRESSE").getString("RUE") + "----"
335
                    + sqlRowValues.getForeign("ID_ADRESSE").getString("VILLE"));
336
        }
337
 
338
        // Adresse
339
        CSVImporter<AdresseCSV> adr = new CSVImporter<AdresseCSV>() {
340
            @Override
341
            public AdresseCSV convert(String[] line) {
342
                return AdresseCSV.fromCSVLine(line);
343
            }
344
        };
345
        final Map<Object, AdresseCSV> mapAdr = adr.importFrom(new File(csvFileDir, "Address.csv"));
346
 
347
        Map<Object, SQLRowValues> mapAdrRows = new HashMap<Object, SQLRowValues>();
348
        for (Object o : mapAdr.keySet()) {
349
            AdresseCSV c = mapAdr.get(o);
350
            mapAdrRows.put(c.getId(), c.toSQLRowValues(root));
351
        }
352
 
353
        // Client
354
        CSVImporter<LeadCSV> client = new CSVImporter<LeadCSV>() {
355
            @Override
356
            public LeadCSV convert(String[] line) {
357
                return LeadCSV.fromCSVLine(line);
358
            }
359
        };
360
 
361
        Map<Object, LeadCSV> mapClient = client.importFrom(new File(csvFileDir, "Lead.csv"));
362
 
363
        Map<Object, SQLRowValues> mapCliRows = new HashMap<Object, SQLRowValues>();
364
        for (Object o : mapClient.keySet()) {
365
            LeadCSV c = mapClient.get(o);
366
 
367
            String hashLead = c.getApe() + "----" + c.getName() + "----" + mapAdr.get(c.getIdAdr()).getStreet() + "----" + mapAdr.get(c.getIdAdr()).getCity();
368
            if (!existingHashLead.contains(hashLead)) {
369
                mapCliRows.put(c.getId(), c.toSQLRowValues(root, mapAdrRows));
370
            } else {
371
                System.err.println("Prospect déjà existant " + c.getName() + " --> Non importé");
372
            }
373
        }
374
 
375
        final Set<SQLRowValuesCluster> graphs = new IdentityHashSet<SQLRowValuesCluster>();
376
        for (Object c : mapCliRows.keySet()) {
377
            graphs.add(mapCliRows.get(c).getGraph());
378
        }
379
 
380
        for (final SQLRowValuesCluster graph : graphs) {
381
            graph.store(StoreMode.COMMIT);
382
        }
383
    }
384
 
385
}