SQL Calendar periods generator
And goment sample.
- goment.go
/* * Author, Copyright: Oleg Borodin <onborodin@gmail.com> */ 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() }