createreport.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"time"

	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/gorilla/handlers"
	"github.com/gorilla/mux"

	_ "github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 2019
	user     = "postgres"
	password = "dbadmin"
	dbname   = "db-dev-01
)

type M map[string]interface{}

func DBconnect() *sql.DB {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)
	db, err := sql.Open("postgres", psqlInfo)

	if err != nil {
		log.Fatal(err)
	}

	return db
}

/*
type NullString struct {
	sql.NullString
}
*/

type NullTime struct {
	sql.NullString
}

/*
func (ns *NullString) MarshalJSON() ([]byte, error) {
	if !ns.Valid {
		return []byte("null"), nil
	}
	return json.Marshal(ns.String)
}
*/

func (nt *NullTime) MarshalJSON() ([]byte, error) {
	if !nt.Valid {
		return []byte("null"), nil
	}

	t, err := time.Parse("3 04 PM", nt.String)

	if err != nil {
		fmt.Println(err)
	}
	return json.Marshal(t)
}

type LogItem struct {
	UserId      string   `form:"id" json:"id"`
	UserName    string   `form:"name" json:"name"`
	UserDate    string   `form:"date" json:"date"`
	CheckinTime NullTime `form:"checkintime" json:"checkintime"`
	//CheckinLocation  NullString `form:"checkinlocation" json:"checkinlocation"`
	CheckoutTime NullTime `form:"checkouttime" json:"checkouttime"`
	//CheckoutLocation NullString `form:"checkoutlocation" json:"checkoutlocation"`
	CheckinDistance  int `form:"checkindistance" json:"checkindistance"`
	CheckoutDistance int `form:"checkoutdistance" json:"checkoutdistance"`
}

func main() {

	headersOk := handlers.AllowedHeaders([]string{"X-Requested-With", "Content-Type"})
	originsOk := handlers.AllowedOrigins([]string{"*"})
	methodsOk := handlers.AllowedMethods([]string{"GET", "HEAD", "POST", "PUT", "OPTIONS"})

	router := mux.NewRouter()
	router.HandleFunc("/report", doCreateReport).Methods("GET")
	http.Handle("/", router)
	fmt.Println("Connected to port 1234...")
	log.Fatal(http.ListenAndServe(":1234", handlers.CORS(originsOk, headersOk, methodsOk)(router)))
}

func doCreateReport(w http.ResponseWriter, r *http.Request) {
	var sqlQuery string
	var arrLogItem []LogItem
	var logItem LogItem

	xlsx := excelize.NewFile()

	sqlQuery = "SELECT oc_log.userid, oc_user.username, oc_log.userdate, oc_log.checkin_time, oc_log.checkin_distance, oc_log.checkout_time, oc_log.checkout_distance FROM oc_log join oc_user on oc_user.userid = oc_log.userid;"
	db := DBconnect()
	defer db.Close()

	rows, err := db.Query(sqlQuery)
	if err != nil {
		log.Print(err)
	}

	for rows.Next() {
		if err := rows.Scan(&logItem.UserId, &logItem.UserName, &logItem.UserDate, &logItem.CheckinTime, &logItem.CheckinDistance, &logItem.CheckoutTime, &logItem.CheckoutDistance); err != nil {
			log.Fatal(err.Error())

		} else {
			arrLogItem = append(arrLogItem, logItem)
		}
	}

	sheet1Name := "Sheet One"
	xlsx.SetSheetName(xlsx.GetSheetName(1), sheet1Name)

	xlsx.SetCellValue(sheet1Name, "A1", "User ID")
	xlsx.SetCellValue(sheet1Name, "B1", "Name")
	xlsx.SetCellValue(sheet1Name, "C1", "Date")
	xlsx.SetCellValue(sheet1Name, "D1", "Checkin hour")
	xlsx.SetCellValue(sheet1Name, "E1", "Checkin distance")
	xlsx.SetCellValue(sheet1Name, "F1", "Checkout hour")
	xlsx.SetCellValue(sheet1Name, "G1", "Checkout distance")


	for idx, elem := range arrLogItem {
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("A%d", idx+2), elem.UserId)
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("B%d", idx+2), elem.UserName)
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("C%d", idx+2), elem.UserDate)
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("D%d", idx+2), elem.CheckinTime)
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("E%d", idx+2), elem.CheckinDistance)
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("F%d", idx+2), elem.CheckoutTime)
		xlsx.SetCellValue(sheet1Name, fmt.Sprintf("G%d", idx+2), elem.CheckoutDistance)
	}

	err = xlsx.SaveAs("./report.xlsx")
	if err != nil {
		fmt.Println(err)
	}
}