report3.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   = "oc-indo-dev"
)

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)
}
*/

type NullDate struct {
	sql.NullString
}

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

	t, err := time.Parse(time.RFC3339, nd.String)

	if err != nil {
		fmt.Println(err)
	}
	return json.Marshal(t.Format("02-01-2006"))
}

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

	//t, err := time.Parse("3 04 PM", nt.String)
	//t, err := time.Parse(time.RFC3339, "0000-01-01T16:29:02+07:00")
	t, err := time.Parse(time.RFC3339, nt.String)

	if err != nil {
		fmt.Println(err)
	}
	return json.Marshal(t.Format("15:04:05"))
}

type LogItem struct {
	UserId      string   `form:"id" json:"id"`
	UserName    string   `form:"name" json:"name"`
	UserDate    NullDate `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  sql.NullInt64 `form:"checkindistance" json:"checkindistance"`
	CheckoutDistance sql.NullInt64 `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

	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)
		}
	}

	xlsx := excelize.NewFile()
	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)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(arrLogItem)
}