-
-
Save maxbad/e2d716b2c552c620e238df529fa3466f to your computer and use it in GitHub Desktop.
makesheet.go
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package main | |
| import "fmt" | |
| import "os" | |
| import "log" | |
| import "bytes" | |
| import "strings" | |
| import "time" | |
| import "compress/gzip" | |
| import "encoding/base64" | |
| import "io/ioutil" | |
| import "gopkg.in/ini.v1" | |
| import "database/sql" | |
| import _ "github.com/go-sql-driver/mysql" | |
| import "github.com/hyperjiang/php" | |
| import "github.com/dimchansky/utfbom" | |
| type _Table_Response struct { | |
| ID int `json:"id"` | |
| CommandID int `json:"command_id"` | |
| DeviceID int `json:"device_id"` | |
| Response string `json:"response"` | |
| IsRead int `json:"is_read"` | |
| DateTime string `json:"datetime"` | |
| } | |
| func main() { | |
| // set working directory | |
| cwd := "/home/gw/protected" | |
| // get arguments | |
| args := os.Args[1:] | |
| if len(args) < 2 { | |
| log.Fatal("arguments are not enough") | |
| } | |
| // set response ID | |
| responseID := args[0] | |
| // set tablename | |
| tableName := args[1] | |
| // read database configuration | |
| cfg, err := ini.Load(cwd + "/config/database.ini") | |
| if err != nil { | |
| log.Fatal(err.Error()) | |
| } | |
| // initalize variables | |
| db_host := cfg.Section("database").Key("db_host").String() | |
| db_name := cfg.Section("database").Key("db_name").String() | |
| db_username := cfg.Section("database").Key("db_username").String() | |
| db_password := cfg.Section("database").Key("db_password").String() | |
| // connect to database | |
| dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s", db_username, db_password, db_host, db_name) | |
| db, err := sql.Open("mysql", dsn) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| defer db.Close() | |
| // get responses | |
| results, err := db.Query("select id, command_id, device_id, response from autoget_responses where id = ?", responseID) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| // fetch all | |
| for results.Next() { | |
| var row _Table_Response | |
| err = results.Scan(&row.ID, &row.CommandID, &row.DeviceID, &row.Response); | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| response := row.Response | |
| data, err := base64.StdEncoding.DecodeString(response) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| // get bytes and remove BOM | |
| rdata, _ := utfbom.Skip(bytes.NewReader(data)) | |
| r, err := gzip.NewReader(rdata) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| s, err := ioutil.ReadAll(r) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| // initialize query | |
| sqlLine := fmt.Sprintf("insert into `%s`(response_id, command_id, device_id, pos_y, pos_x, term, datetime) values ", tableName) | |
| vals := []interface{}{} | |
| // get current datetime | |
| currentTime := time.Now() | |
| datetime := currentTime.Format("2006-01-02 15:04:05") | |
| // split by line and word -> query | |
| delimiters := []string{" ", "\t", "\",\"", "\"", "'", "\r\n", "\n", "(", ")", "\\"} | |
| lines := strings.Split(strings.Replace(string(s), "\r\n", "\n", -1), "\n") | |
| y := 0 | |
| x := 0 | |
| for _, line := range lines { | |
| y++ | |
| line = php.Replace(delimiters, " ", line) | |
| words := strings.Fields(line) | |
| for _, word := range words { | |
| x++ | |
| sqlLine += "(?, ?, ?, ?, ?, ?, ?)," | |
| vals = append(vals, responseID, row.CommandID, row.DeviceID, y, x, word, datetime) | |
| //fmt.Println(x, y, word) | |
| } | |
| x = 0 | |
| } | |
| // trim the last | |
| sqlLine = sqlLine[0:len(sqlLine) - 1] | |
| // prepare the statement | |
| stmt, err := db.Prepare(sqlLine); | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| // format all vals at once | |
| res, err := stmt.Exec(vals...) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| // ... | |
| numInserted, err := res.RowsAffected() | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| fmt.Print(numInserted) | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment