-
Notifications
You must be signed in to change notification settings - Fork 0
/
main.go
229 lines (189 loc) · 5.43 KB
/
main.go
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
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
package main
import (
"encoding/json"
"fmt"
"io/ioutil"
"log"
"os"
"strings"
"github.com/gosimple/slug"
"github.com/xuri/excelize/v2"
)
type Parser struct {
FileName string
SheetName string
}
type University struct {
Code string `json:"code"`
Name string `json:"name"`
City string `json:"city"`
Type string `json:"type"`
}
type Faculty struct {
Code string `json:"code"`
UniversityCode string `json:"universityCode"`
Name string `json:"name"`
}
type Department struct {
UniversityCode string `json:"universityCode"`
FacultyCode string `json:"facultyCode"`
Name string `json:"name"`
}
func NewYokUniversityParser(fileName, sheetName string) *Parser {
return &Parser{
FileName: fileName,
SheetName: sheetName,
}
}
func (p *Parser) parse() error {
f, err := excelize.OpenFile(p.FileName)
if err != nil {
return fmt.Errorf("failed to open file: %w", err)
}
rows, err := f.GetRows(p.SheetName)
if err != nil {
return fmt.Errorf("failed to get rows: %w", err)
}
universitiesMap, facultyMap, departmentArr := p.processRows(rows)
if err := writeJSON("university.json", universitiesMap); err != nil {
return err
}
if err := writeJSON("faculties.json", facultyMap); err != nil {
return err
}
if err := writeJSON("departments.json", departmentArr); err != nil {
return err
}
if err := writeUniversitySql("universities.sql", universitiesMap); err != nil {
return err
}
if err := writeFacultySql("faculties.sql", facultyMap); err != nil {
return err
}
if err := writeDepartmentSql("departments.sql", departmentArr); err != nil {
return err
}
return nil
}
func (p *Parser) processRows(rows [][]string) (map[string]University, map[string]Faculty, []Department) {
universitiesMap := make(map[string]University)
facultyMap := make(map[string]Faculty)
var departmentArr []Department
for i, row := range rows {
if i == 0 {
continue
}
universityName := row[0]
facultyName := row[1]
departmentName := row[2]
universityType := row[4]
universityCity := row[5]
universityCode := slug.Make(universityName)
facultyCode := slug.Make(facultyName)
// Populate universities map
if _, exists := universitiesMap[universityCode]; !exists {
universitiesMap[universityCode] = University{
Code: universityCode,
Name: universityName,
Type: universityType,
City: universityCity,
}
}
// Populate faculty map
_, ok := facultyMap[facultyCode+"-"+universityCode]
if !ok {
facultyMap[facultyCode+"-"+universityCode] = Faculty{
Code: facultyCode + "-" + universityCode,
Name: facultyName,
UniversityCode: universityCode,
}
}
// Populate department array
departmentArr = append(departmentArr, Department{
UniversityCode: universityCode,
FacultyCode: facultyCode + "-" + universityCode,
Name: departmentName,
})
}
return universitiesMap, facultyMap, departmentArr
}
func writeJSON(fileName string, v interface{}) error {
data, err := json.MarshalIndent(v, "", " ")
if err != nil {
return fmt.Errorf("failed to marshal data: %w", err)
}
if err := os.WriteFile(fileName, data, 0644); err != nil {
return fmt.Errorf("failed to write file %s: %w", fileName, err)
}
return nil
}
func writeUniversitySql(fileName string, universities map[string]University) error {
sqlHead := `
CREATE TABLE universities(
id SERIAL,
code TEXT,
name TEXT,
city TEXT,
type TEXT,
UNIQUE(code)
);
INSERT INTO universities (code, name, city, type) VALUES`
var values []string
for _, u := range universities {
values = append(values, fmt.Sprintf("('%s', '%s', '%s', '%s')", u.Code, u.Name, u.City, u.Type))
}
sql := sqlHead + "\n" + strings.Join(values, ",\n") + ";\n"
if err := ioutil.WriteFile(fileName, []byte(sql), 0644); err != nil {
return fmt.Errorf("failed to write file %s: %w", fileName, err)
}
return nil
}
func writeFacultySql(fileName string, faculties map[string]Faculty) error {
sqlHead := `
CREATE TABLE faculties (
id SERIAL,
code TEXT,
name TEXT,
university_code TEXT,
UNIQUE(code,university_code),
FOREIGN KEY (university_code) REFERENCES universities(code)
);
INSERT INTO faculties (code, name, university_code) VALUES`
var values []string
for _, f := range faculties {
values = append(values, fmt.Sprintf("('%s', '%s', '%s')", f.Code, f.Name, f.UniversityCode))
}
sql := sqlHead + "\n" + strings.Join(values, ",\n") + ";\n"
if err := ioutil.WriteFile(fileName, []byte(sql), 0644); err != nil {
return fmt.Errorf("failed to write file %s: %w", fileName, err)
}
return nil
}
func writeDepartmentSql(fileName string, departments []Department) error {
sqlHead := `
CREATE TABLE departments (
id SERIAL,
name TEXT,
university_code TEXT,
faculty_code TEXT,
FOREIGN KEY (university_code) REFERENCES universities(code),
FOREIGN KEY (faculty_code) REFERENCES faculties(code)
);
INSERT INTO departments ( university_code, faculty_code, name) VALUES`
var values []string
for _, d := range departments {
values = append(values, fmt.Sprintf("('%s', '%s', '%s')", d.UniversityCode, d.FacultyCode, d.Name))
}
sql := sqlHead + "\n" + strings.Join(values, ",\n") + ";\n"
if err := ioutil.WriteFile(fileName, []byte(sql), 0644); err != nil {
return fmt.Errorf("failed to write file %s: %w", fileName, err)
}
return nil
}
func main() {
parser := NewYokUniversityParser("universiteler.xlsx", "Sheet1")
if err := parser.parse(); err != nil {
log.Fatalln(err)
}
return
}