besingamk

user.go

Aug 1st, 2021 (edited)
1,153
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package user
  2.  
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "log"
  7.     "math"
  8.  
  9.     _ "github.com/go-sql-driver/mysql"
  10. )
  11.  
  12. // Database instance
  13. var db *sql.DB
  14.  
  15. // Database settings
  16. const (
  17.     host     = "localhost"
  18.     port     = 3306 // Default port
  19.     user     = "root"
  20.     password = ""
  21.     dbname   = "netflip_db"
  22. )
  23.  
  24. // User struct
  25. type User struct {
  26.     ID                    int    `json:"id"`
  27.     Name                  string `json:"name"`
  28.     Email                 string `json:"salary"`
  29.     PhoneNumber           string `json:"phone_number"`
  30.     SubscriptionName      string `json:"subscription_name"`
  31.     UserCreatedAt         string `json:"user_created_at"`
  32.     SubscriptionCreatedAt string `json:"subscription_created_at"`
  33.     SubscriptionEndedAt   string `json:"subscription_ended_at"`
  34. }
  35.  
  36. // Users struct
  37. type Users struct {
  38.     Users []User `json:"users"`
  39. }
  40.  
  41. // Connect function
  42. func Connect() error {
  43.     var err error
  44.     // Use DSN string to open
  45.     db, err = sql.Open("mysql", fmt.Sprintf("%s:%s@/%s", user, password, dbname))
  46.     if err != nil {
  47.         return err
  48.     }
  49.     if err = db.Ping(); err != nil {
  50.         return err
  51.     }
  52.     return nil
  53. }
  54.  
  55. func GetUsersSubscriptionPaginationMeta() float64 {
  56.     var count float64
  57.     // Connect with database
  58.     if err := Connect(); err != nil {
  59.         log.Fatal(err)
  60.     }
  61.  
  62.     row := db.QueryRow(`
  63.         SELECT
  64.             count(*)
  65.         FROM users u
  66.         JOIN subscriptions s on u.id = s.user_id
  67.         JOIN plans p on s.plan_id = p.id
  68.     `)
  69.  
  70.     err := row.Scan(&count)
  71.     defer db.Close()
  72.  
  73.     if err != nil {
  74.         log.Fatal(err)
  75.     }
  76.  
  77.     fmt.Print(count)
  78.     return count
  79. }
  80.  
  81. func GetUsersSubscriptionPaginated(page_no int) (Users, error) {
  82.  
  83.     // page_no := 0
  84.     // no_of_records_per_page := 10
  85.     // offset := (page_no -1) * no_of_records_per_page
  86.     var no_of_records_per_page, offset int = 10, 0
  87.     offset = (page_no - 1) * no_of_records_per_page
  88.  
  89.     totalUserCount := GetUsersSubscriptionPaginationMeta()
  90.  
  91.     totalPages := int(math.Ceil(totalUserCount / float64(no_of_records_per_page)))
  92.  
  93.     // Connect with database
  94.     if err := Connect(); err != nil {
  95.         log.Fatal(err)
  96.     }
  97.  
  98.     result := Users{}
  99.  
  100.     // Get Users list from database
  101.  
  102.     rows, err := db.Query(`
  103.     SELECT
  104.         u.id,
  105.         u.name,
  106.         u.email,
  107.         u.mobile as "phone_number",
  108.         u.created_at as "user_created_at",
  109.         p.name as "subscription_name",
  110.         s.created_at as "subscription_created_at",
  111.         s.ended_at as "subscription_ended_at"
  112.     FROM users u
  113.     JOIN subscriptions s on u.id = s.user_id
  114.     JOIN plans p on s.plan_id = p.id
  115.     LIMIT ?, 10
  116.     `, offset)
  117.  
  118.     if err != nil {
  119.         // return c.Status(500).SendString(err.Error())
  120.         return result, err
  121.     }
  122.     defer rows.Close()
  123.  
  124.     for rows.Next() {
  125.         user := User{}
  126.         if err := rows.Scan(
  127.             &user.ID,
  128.             &user.Name,
  129.             &user.Email,
  130.             &user.PhoneNumber,
  131.             &user.SubscriptionName,
  132.             &user.UserCreatedAt,
  133.             &user.SubscriptionCreatedAt,
  134.             &user.SubscriptionEndedAt,
  135.         ); err != nil {
  136.             return result, err // Exit if we get an error
  137.         }
  138.  
  139.         // Append User to Users
  140.         result.Users = append(result.Users, user)
  141.     }
  142.  
  143.     fmt.Println(totalPages)
  144.  
  145.     return result, err
  146. }
  147.  
RAW Paste Data