User Tools

Site Tools


SQL Calendar periods generator

And goment sample.

goment.go
/*
 * Copyright 2021 Oleg Borodin  <borodin@unix7.org>
 */
 
package main
 
import (
    "fmt"
    "time"
    //"encoding/json"
    "regexp"
 
    "github.com/nleeper/goment"
)
 
 
const (
    startYear   int = 2019
    endYear     int = 2025
)
 
type Day struct {
    Id          int         `json:"id"          db:"id"`
    Number      int         `json:"number"      db:"number"`
    Date        string      `json:"date"        db:"date"`
 
    Year        int         `json:"year"        db:"year"`
    Quarter     int         `json:"quarter"     db:"quarter"`
    Month       int         `json:"month"       db:"month"`
 
}
 
const (
    daySchema string = `
        DROP TABLE IF EXISTS day_period;
        CREATE TABLE IF NOT EXISTS day_period (
            id          INT UNIQUE,
            number      INT,
            date        TIMESTAMP,
            year        INT,
            quarter     INT,
            month       INT
        );
    `
)
 
func genDays() {
 
    space := regexp.MustCompile(`\s+`)
    schema := space.ReplaceAllString(daySchema, " ")
    fmt.Println(schema)
 
    for year := startYear; year <= endYear; year++ {
        startDate := fmt.Sprintf("%d-01-01T00:00:00Z", year)
        startTime, _ := time.Parse(time.RFC3339, startDate)
        moment, _ := goment.New(startTime)
 
        var daysOfYear int
        switch moment.IsLeapYear() {
            case true :
                daysOfYear = 366
            default:
                daysOfYear = 365
        }
 
        for day := 1; day <= daysOfYear; day++ {
            theMoment, _ := goment.New(moment)
            theMoment.SetDayOfYear(day)
 
            theDay := Day{
                Id:         year*1000 + day,
                Number:     day,
                Date:       theMoment.Format("YYYY-MM-DD HH:mm:ss"),
                Month:      theMoment.Month(),
                Quarter:    theMoment.Quarter(),
                Year:       year,
            }
            //fmt.Println(theDay)
            //jsonData, _ := json.MarshalIndent(theDay, "", "    ")
            //fmt.Println(string(jsonData))
 
            request := `INSERT INTO day_period(id, number, date, year, quarter, month)
                            VALUES (%d, %d, '%s', %d, %d, %d);`
            //space := regexp.MustCompile(`\s+`)
            request = space.ReplaceAllString(request, " ")
            fmt.Printf(request + "\n",
                                theDay.Id,
                                theDay.Number,
                                theDay.Date,
                                theDay.Year,
                                theDay.Quarter,
                                theDay.Month)
        }
    }
}
 
 
type Week struct {
    Id              int         `json:"id"          db:"id"`
    Lang            string      `json:"lang"        db:"lang"`
    Number          int         `json:"number"      db:"number"`
 
    BeginDate       string      `json:"beginDate"   db:"begin_date"`
    EndDate         string      `json:"endData"     db:"end_date"`
 
    BeginDay  int               `json:"beginDay"    db:"begin_day"`
    EndDay    int               `json:"endDay"      db:"end_day"`
 
    BeginYear       int         `json:"beginYear"   db:"begin_year"`
    EndYear         int         `json:"endYear"     db:"end_year"`
 
    BeginMonth      int         `json:"beginMonth"  db:"begin_month"`
    EndMonth        int         `json:"endMonth"    db:"end_month"`
 
    Form1       string          `json:"form1"       db:"form1"`
}
 
const (
    weekSchema string = `
        DROP TABLE IF EXISTS week_period;
        CREATE TABLE IF NOT EXISTS week_period (
            id          INT UNIQUE,
            lang        TEXT,
            number      INT,
 
            begin_date  TIMESTAMP,
            end_date    TIMESTAMP,
 
            begin_year  INT,
            begin_month INT,
            begin_day   INT,
 
            end_year    INT,
            end_month   INT,
            end_day     INT,
 
            form1       TEXT
        );
    `
)
 
func genWeeks() {
    space := regexp.MustCompile(`\s+`)
    schema := space.ReplaceAllString(weekSchema, " ")
    fmt.Println(schema)
 
    //return
    for year := startYear; year <= endYear; year++ {
 
        startDate := fmt.Sprintf("%d-01-01T00:00:00Z", year)
        startTime, _ := time.Parse(time.RFC3339, startDate)
        moment, _ := goment.New(startTime)
        moment.StartOf("year")
 
        numberOfWeeks := moment.WeeksInYear()
 
        for week := 1; week <= numberOfWeeks + 1; week++ {
            theMoment, _ := goment.New(moment)
            theMoment.SetWeek(week)
            //fmt.Println(theMoment.ToString())
 
            beginMoment, _ := goment.New(theMoment)
            endMoment, _ := goment.New(theMoment)
 
            beginMoment.StartOf("week")
            endMoment.EndOf("week")
 
            week := Week {
                Id:             year*1000 + week,
 
                BeginDate:      beginMoment.Format("YYYY-MM-DD HH:mm:ss"),
                EndDate:        endMoment.Format("YYYY-MM-DD HH:mm:ss"),
                Number:         week,
                Lang:           "ru",
 
                BeginDay:       beginMoment.DayOfYear(),
                EndDay:         endMoment.DayOfYear(),
 
                BeginYear:      beginMoment.Year(),
                EndYear:        endMoment.Year(),
 
                BeginMonth:     beginMoment.Month(),
                EndMonth:       endMoment.Month(),
 
                Form1:          beginMoment.Format("DD MMMM") + "-" + endMoment.Format("DD MMMM"),
            }
            //fmt.Println(week)
            //jsonData, _ := json.MarshalIndent(week, "", "    ")
            //fmt.Println(string(jsonData))
 
            request := `INSERT INTO week_period(
                            id, lang, number, begin_date, end_date,
                                begin_year, begin_month, begin_day,
                                end_year, end_month, end_day,
                                form1)
                        VALUES (%d, '%s', %d, '%s', '%s',
                                        %d, %d, %d,
                                        %d, %d, %d,
                                        '%s');`
            space := regexp.MustCompile(`\s+`)
            request = space.ReplaceAllString(request, " ")
            fmt.Printf(request + "\n",
                            week.Id,
                            week.Lang,
                            week.Number,
                            week.BeginDate,
                            week.EndDate,
                            week.BeginYear,
                            week.BeginMonth,
                            week.BeginDay,
                            week.EndYear,
                            week.EndMonth,
                            week.EndDay,
                            week.Form1)
        }
    }
}
 
type Month struct {
    Id          int         `json:"id"          db:"id"`
    Number      int         `json:"number"      db:"number"`
    Year        int         `json:"year"        db:"year"`
 
    BeginDate   string      `json:"beginDate"   db:"begin_date"`
    EndDate     string      `json:"endDate"     db:"end_date"`
 
    BeginWeek   int         `json:"startWeek"   db:"start_week"`
    EndWeek     int         `json:"endWeek"     db:"end_week"`
 
    BeginDay    int         `json:"beginDay"    db:"begin_day"`
    EndDay      int         `json:"endDaya"     db:"end_day"`
 
    BeginYear   int         `json:"beginYear"   db:"begin_year"`
    EndYear     int         `json:"endYear"     db:"end_year"`
 
    BeginMonth  int         `json:"beginMonth"  db:"begin_month"`
    EndMonth    int         `json:"endMonth"    db:"end_month"`
 
    Form1       string      `json:"form1"       db:"form1"`
}
 
const (
    monthSchema string = `
        DROP TABLE IF EXISTS month_period;
        CREATE TABLE IF NOT EXISTS month_period (
 
            id          INT UNIQUE,
            number      INT,
            year        INT,
 
            begin_date  TIMESTAMP,
            end_date    TIMESTAMP,
 
            begin_year  INT,
            begin_month INT,
            begin_week  INT,
            begin_day   INT,
 
            end_year    INT,
            end_month   INT,
            end_week    INT,
            end_day     INT,
 
            form1       TEXT
        );`
)
 
 
func genMonths() {
 
    space := regexp.MustCompile(`\s+`)
    schema := space.ReplaceAllString(monthSchema, " ")
    fmt.Println(schema)
 
    //return
 
    for year := startYear; year <= endYear; year++ {
 
        startDate := fmt.Sprintf("%d-01-01T00:00:00Z", year)
        startTime, _ := time.Parse(time.RFC3339, startDate)
        moment, _ := goment.New(startTime)
        moment.StartOf("year")
 
        numberOfMonths := 12
 
        for month := 1; month <= numberOfMonths; month++ {
            theMoment, _ := goment.New(moment)
            theMoment.SetMonth(month)
            //fmt.Println(theMoment.ToString())
 
            beginMoment, _ := goment.New(theMoment)
            endMoment, _ := goment.New(theMoment)
 
            beginMoment.StartOf("month")
            endMoment.EndOf("month")
 
            month := Month {
                Id:             year*1000 + month,
                Number:         month,
 
                Year:           year,
 
                BeginDate:      beginMoment.Format("YYYY-M-D HH:mm:ss"),
                EndDate:        endMoment.Format("YYYY-M-D HH:mm:ss"),
 
                BeginDay:       beginMoment.DayOfYear(),
                EndDay:         endMoment.DayOfYear(),
 
                BeginYear:      beginMoment.Year(),
                EndYear:        endMoment.Year(),
 
                BeginMonth:     beginMoment.Month(),
                EndMonth:       endMoment.Month(),
 
                Form1:          beginMoment.Format("MMMM") + "-" + endMoment.Format("MMMM YYYY"),
 
            }
            //fmt.Println(month)
            //jsonData, _ := json.MarshalIndent(month, "", "    ")
            //fmt.Println(string(jsonData))
            request := `INSERT INTO month_period(
                            id, number, year, begin_date, end_date,
                            begin_year, begin_month, begin_week, begin_day,
                            end_year, end_month, end_week, end_day,
                            form1)
                        VALUES (%d, %d, %d, '%s', '%s',
                                    %d, %d, %d, %d,
                                    %d, %d, %d, %d,
                                '%s');`
            space := regexp.MustCompile(`\s+`)
            request = space.ReplaceAllString(request, " ")
            fmt.Printf(request + "\n",
                        month.Id,
                        month.Number,
                        month.Year,
                        month.BeginDate,
                        month.EndDate,
 
                        month.BeginYear,
                        month.BeginMonth,
                        month.BeginWeek,
                        month.BeginDay,
 
                        month.EndYear,
                        month.EndMonth,
                        month.EndWeek,
                        month.EndDay,
                        month.Form1)
        }
    }
}
 
type Quarter struct {
    Id          int         `json:"id"          db:"id"`
    Lang        string      `json:"lang"        db:"lang"`
 
    BeginDate   string      `json:"beginDate"   db:"begin_date"`
    EndDate     string      `json:"endDate"     db:"end_date"`
 
    Number      int         `json:"number"      db:"number"`
    Year        int         `json:"year"        db:"year"`
 
    BeginWeek   int         `json:"beginWeek"   db:"begin_week"`
    EndWeek     int         `json:"endWeek"     db:"end_week"`
 
    BeginDay    int         `json:"beginDay"    db:"begin_day"`
    EndDay      int         `json:"endDay"      db:"end_day"`
 
    Form1       string      `json:"form1"   db:"form1"`
}
 
const (
    quarterSchema string = `
        DROP TABLE IF EXISTS quarter_period;
        CREATE TABLE IF NOT EXISTS quarter_period (
            id          INT UNIQUE,
            lang        TEXT,
 
            begin_date  TIMESTAMP,
            end_date    TIMESTAMP,
 
            number      INT,
            year        INT,
 
            begin_week  INT,
            end_week    INT,
 
            begin_day   INT,
            end_day     INT,
 
            form1       TEXT
        );`
)
 
 
func genQuarters() {
 
    space := regexp.MustCompile(`\s+`)
    schema := space.ReplaceAllString(quarterSchema, " ")
    fmt.Println(schema)
 
 
    for year := startYear; year <= endYear; year++ {
 
        startDate := fmt.Sprintf("%d-01-01T00:00:00Z", year)
        startTime, _ := time.Parse(time.RFC3339, startDate)
        moment, _ := goment.New(startTime)
        moment.StartOf("year")
 
        numberOfQuarters := 4
 
        for quarter := 1; quarter <= numberOfQuarters; quarter++ {
            theMoment, _ := goment.New(moment)
            theMoment.SetMonth((quarter - 1) * 3 + 1)
            //fmt.Println(theMoment.ToString())
 
            beginMoment, _ := goment.New(theMoment)
            endMoment, _ := goment.New(theMoment)
 
            //beginMoment.SetLocale("en")
            //endMoment.SetLocale("en")
 
            beginMoment.StartOf("quarter")
            endMoment.EndOf("quarter")
 
            quarter := Quarter{
                Id:         year*1000 + quarter,
                Lang:       "ru",
 
                BeginDate:  beginMoment.Format("YYYY-M-D HH:mm:ss"),
                EndDate:    endMoment.Format("YYYY-M-D HH:mm:ss"),
 
                Number:     quarter,
                Year:       year,
 
                BeginWeek:  beginMoment.Week(),
                EndWeek:    endMoment.Week(),
 
                BeginDay:   beginMoment.DayOfYear(),
                EndDay:     endMoment.DayOfYear(),
 
                Form1:      beginMoment.Format("MMMM") + "-" + endMoment.Format("MMMM YYYY"),
            }
            //fmt.Println(quarter)
            //jsonData, _ := json.MarshalIndent(quarter, "", "    ")
            //fmt.Println(string(jsonData))
 
            request := `INSERT INTO quarter_period(
                            id, lang, begin_date, end_date,
                            number, year,
                            begin_week, end_week,
                            begin_day, end_day,
                            form1)
                        VALUES (%d, '%s', '%s', '%s',
                                    %d, %d,
                                    %d, %d,
                                    %d, %d,
                                '%s');`
            space := regexp.MustCompile(`\s+`)
            request = space.ReplaceAllString(request, " ")
            fmt.Printf(request + "\n",
                        quarter.Id,
                        quarter.Lang,
                        quarter.BeginDate,
                        quarter.EndDate,
 
                        quarter.Number,
                        quarter.Year,
 
                        quarter.BeginWeek,
                        quarter.EndWeek,
 
                        quarter.BeginDay,
                        quarter.EndDay,
                        quarter.Form1,
            )
        }
    }
}
 
 
func main() {
    genDays()
    genWeeks()
    genMonths()
    genQuarters()
}