R/loadSampleData.R

In agpknitweardesign/festotuDbTools: Festotu database tools.

# Order of table loads
#
# 1.- designs
# 2.- products
# 3.- designyarns
# 4.- yarns
# 5.- productYarns
# 6.- photos
# ---------------- enough for playing with etsy apis ----------------------
#
# 7.- materials
# 8.- purchase items
# 9.- supliers
# 10.- purchase orders
# 11.- clients
# 12.- sales
##

#' @import RMariaDB
#' @import XLConnect
#' @import reshape2
#' @import magrittr
#' @import dplyr
#' @export
loadSampleData <- function(testDatabaseFolder = NULL){
  # Default to installed files
  if (!exists("testDatabaseFolder") || is.null(testDatabaseFolder)) testDatabaseFolder <- system.file("testDatabase", package = "festotuDbTools")

  # Create database connection (qualify dbConnect because it also exists in more common DB packages like DBI)
  dbcon <- RMariaDB::dbConnect(
    drv = MariaDB(),
    dbname = "festotu",
    username = "festotu",
    # password = rstudioapi::askForPassword("Please enter the password of user festotu"),
    password = "13pipo",
    host = "localhost",
    port = 3306)

  # Load workbooks
  llb <- loadWorkbook(file.path(testDatabaseFolder, "Llista bufandes.xlsx"))
  inv <- loadWorkbook(file.path(testDatabaseFolder, "Inventori.xlsx"))

  # 1.- designs - from Master List
  masterList <- readWorksheet(llb, "Master list", region = "D4:I50")
  # dbReadTable(dbcon, "designs")
  designs <- data.frame(
    designId = as.numeric(row.names(masterList)),
    name = masterList$Noms,
    category = "Bufanda",
    type = "",
    subtype = "",
    style = "",
    season = "winter",
    creationDate = as.Date("2018-10-01", "%Y-%m-%d"))

  dbWriteTable(dbcon, name = "designs", value = designs, append = TRUE)

  # 2.- products - from 09-10-2018
  bufandes <- readWorksheet(llb, sheet = "09-10 2018", region = "A6:P46")
  # dbReadTable(dbcon, "products")
  products <- with(bufandes,
                   data.frame(
                     productId = Número,
                     name = Nom,
                     description = Nom,
                     # productionDate = as.Date("2018-11-01", "%Y-%m-%d"),
                     productionDate = "2018-11-01",
                     status = Status,
                     retailPrice = Preu.EUR,
                     retailPriceCurrency = "EUR",
                     weight_gr = Pes.en.grams,
                     length_cm = Llarg.en.cm.,
                     width_cm = Ample.en.cm.,
                     length_in = Llarg.en.inch,
                     width_in = Ample.en.inch,
                     isModifiedDesign = NA))

  # lookup designId and align column names
  products <- merge(products, designs[c("designId", "name")])[names(dbReadTable(dbcon, "products"))]

  products[products$name != "Bufanda 10 - Onades creuades",]

  dbWriteTable(dbcon, name = "products", value = products, append = TRUE)


  # 3.- designyarns
  designYarns <-
    with(
      melt(
        merge(designs, masterList[!is.na(masterList$Color.principal),], by.x = "name", by.y = "Noms"),
        id.vars = c("name", "designId"),
        measure.vars = c("Color.principal", "Color.secundari"),
        variable.name = "description",
        value.name = "designValue") %>%
        cbind(designYarnId = row.names(.)),
      data.frame(
        designYarnId = designYarnId,
        description = description,
        designValue = designValue,
        proportion = ifelse(description == "Color.principal", 0.6, 0.4),
        designId = designId
      )
    )

  dbWriteTable(dbcon, name = "designYarns", value = designYarns, append = TRUE)

  # 4.- yarns
  inventori <- readWorksheet(inv, "Llana", region = "A3:J60")
  compraSept18 <- readWorksheet(inv, "Compra 24.09.18", region = "A5:J18")
  # dbReadTable(dbcon, "yarns")

  yarns <-
    rbind(
      with(
        inventori,
        data.frame(
          yarnId = 1:nrow(inventori),
          name = ifelse(Nom. == "??", paste(Material, "-", Color), Nom.),
          material = Material,
          color = Color,
          thickness_Nm = Gruix,
          grossWeight_gr = Pes.brut.en.gr,
          newWeight_gr = Pes.net.en.gr,
          NumberCones = rowSums(cbind(Con.paper..43.gr., Con.plàstic..40.gr.), na.rm = TRUE),
          location = Localització)),
      with(
        compraSept18,
        data.frame(
          yarnId = nrow(inventori) + 1:nrow(compraSept18),
          name = Nom.,
          material = Material,
          color = Color,
          thickness_Nm = Gruix,
          grossWeight_gr = Pes.real.brut.en.gr,
          newWeight_gr = Pes.net.en.gr,
          NumberCones = Num.cons,
          location = ifelse(is.na(Lloc), "unknown", Lloc))
      )
    )

  dbWriteTable(dbcon, name = "yarns", value = yarns, append = TRUE)

  # 5.- productYarns
  productYarns <-
    with(
      melt(bufandes,
           id.vars = c("Número"),
           measure.vars = c("Color.Principal", "Color.Secundari"),
           variable.name = "description",
           value.name = "color") %>% filter(!is.na(color)) %>%
        merge(yarns, by = "color") %>%
        cbind(productYarnId = row.names(.)),
      data.frame(
        productYarnId = productYarnId,
        description = description,
        productId = Número,
        yarnId = yarnId)
    )
  # dbReadTable(dbcon, "productYarns")

  dbWriteTable(dbcon, name = "productYarns", value = productYarns, append = TRUE)


  # 6.- photos
  photos = data.frame(
    photoId = 1:200,
    uri_server = "uri_server",
    uri_nas = "uri_nas",
    uri_mac = "uri_mac",
    uri_cloud = "uri_cloud",
    productId = floor(runif(200, min = 1, max = 41)))

  dbWriteTable(dbcon, name = "photos", value = photos, append = TRUE)

  # 5.-
}