CNC_WOOD
Karolis Šarskus
2022-06-28
SETUP
library(DBI)
library(RODBC)
library(dbplyr)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:dbplyr':
##
## ident, sql## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(purrr)
library(DescTools)
con <- dbConnect(odbc::odbc(), "CNC_WOOD")
db <- odbcConnect("CNC_WOOD")
homagTbl <- sqlFetch(db, "dbo_HOMAG_FProd")
getBrand <- function(filePath) {
mp4Location <- str_locate(pattern = "mp4", filePath)
subpath <- substr(filePath, mp4Location[2] + 4, str_length(filePath))
splitted <- strsplit(subpath, "\\\\")
# unlist(
# strsplit(
# substr(
# filePath,
# str_locate(
# pattern = "mp4",
# filePath)[2] + 4,
# str_length(filePath)),
# "\\\\"))[1]
brandNameInPath <- splitted
#
#
# result <- switch(
# brandNameInPath,
# "dior_parfums" = "DIOR PARFUMS",
# "louis_vuitton" = "LOUIS VUITTON",
# "th" = "TOMMY HILFIGER",
# "Unknown"
# )
}2022 March
# Getting March month's data
March_2022 <- homagTbl %>%
filter(Date >= '2022-03-01', Date <= '2022-03-31')
# To retrieve specific column data from March month
homagTbl %>%
filter(Date >= '2022-03-01', Date <= '2022-03-31') %>%
select(Type)## Type
## 1 START
## 2 END
## 3 START
## 4 END
## 5 START
## 6 END
## 7 START
## 8 END
## 9 START
## 10 END
## 11 START
## 12 END
## 13 START
## 14 END
## 15 START
## 16 END
## 17 START
## 18 END
## 19 START
## 20 END
## 21 START
## 22 END
## 23 START
## 24 END
## 25 START
## 26 END
## 27 START
## 28 END
## 29 START
## 30 END
## 31 START
## 32 END
## 33 START
## 34 ABORT
## 35 START
## 36 END
## 37 START
## 38 END
## 39 START
## 40 END
## 41 START
## 42 END
## 43 START
## 44 ABORT
## 45 START
## 46 ABORT
## 47 START
## 48 ABORT
## 49 START
## 50 ABORT
## 51 START
## 52 ABORT
## 53 START
## 54 END
## 55 START
## 56 END
## 57 START
## 58 END
## 59 START
## 60 END
## 61 START
## 62 END
## 63 START
## 64 END
## 65 START
## 66 END
## 67 START
## 68 END
## 69 START
## 70 END
## 71 START
## 72 END
## 73 START
## 74 END
## 75 START
## 76 ABORT
## 77 START
## 78 END
## 79 START
## 80 END
## 81 START
## 82 END
## 83 START
## 84 END
## 85 START
## 86 END
## 87 START
## 88 END
## 89 START
## 90 END
## 91 START
## 92 END
## 93 START
## 94 ABORT
## 95 START
## 96 END
## 97 START
## 98 END
## 99 START
## 100 END
## 101 START
## 102 END
## 103 START
## 104 END
## 105 START
## 106 END
## 107 START
## 108 END
## 109 START
## 110 END
## 111 START
## 112 END
## 113 START
## 114 END
## 115 START
## 116 END
## 117 START
## 118 END
## 119 START
## 120 END
## 121 START
## 122 END
## 123 START
## 124 END
## 125 START
## 126 END
## 127 START
## 128 END
## 129 START
## 130 END
## 131 START
## 132 END
## 133 START
## 134 END
## 135 START
## 136 END
## 137 START
## 138 END
## 139 START
## 140 ABORT
## 141 START
## 142 END
## 143 START
## 144 END
## 145 START
## 146 END
## 147 START
## 148 END
## 149 START
## 150 END
## 151 START
## 152 END
## 153 START
## 154 END
## 155 START
## 156 END
## 157 START
## 158 END
## 159 START
## 160 END
## 161 START
## 162 END
## 163 START
## 164 END
## 165 START
## 166 START
## 167 END
## 168 END
## 169 START
## 170 END
## 171 START
## 172 END
## 173 START
## 174 END
## 175 START
## 176 ABORT
## 177 START
## 178 END
## 179 START
## 180 END
## 181 START
## 182 END
## 183 START
## 184 END
## 185 START
## 186 END
## 187 START
## 188 END
## 189 START
## 190 END
## 191 START
## 192 END
## 193 START
## 194 ABORT
## 195 START
## 196 END
## 197 START
## 198 END
## 199 START
## 200 END
## 201 START
## 202 END
## 203 START
## 204 END
## 205 START
## 206 END
## 207 START
## 208 END
## 209 START
## 210 ABORT
## 211 START
## 212 END
## 213 START
## 214 END
## 215 START
## 216 END
## 217 START
## 218 END
## 219 START
## 220 END
## 221 START
## 222 END
## 223 START
## 224 END
## 225 START
## 226 END
## 227 START
## 228 END
## 229 START
## 230 END
## 231 START
## 232 END
## 233 START
## 234 END
## 235 START
## 236 END
## 237 START
## 238 END
## 239 START
## 240 END
## 241 START
## 242 END
## 243 START
## 244 END
## 245 START
## 246 END
## 247 START
## 248 END
## 249 START
## 250 END
## 251 START
## 252 END
## 253 START
## 254 END
## 255 START
## 256 END
## 257 START
## 258 END
## 259 START
## 260 END
## 261 START
## 262 END
## 263 START
## 264 END
## 265 START
## 266 END
## 267 START
## 268 END
## 269 START
## 270 END
## 271 START
## 272 END
## 273 START
## 274 END
## 275 START
## 276 END
## 277 START
## 278 END
## 279 START
## 280 END
## 281 START
## 282 END
## 283 START
## 284 END
## 285 START
## 286 ABORT
## 287 START
## 288 END
## 289 START
## 290 END
## 291 START
## 292 END
## 293 START
## 294 END
## 295 START
## 296 END
## 297 START
## 298 END
## 299 START
## 300 END
## 301 START
## 302 END
## 303 START
## 304 END
## 305 START
## 306 END
## 307 START
## 308 END
## 309 START
## 310 END
## 311 START
## 312 END
## 313 START
## 314 END
## 315 START
## 316 END
## 317 START
## 318 END
## 319 START
## 320 END
## 321 START
## 322 END
## 323 START
## 324 END
## 325 START
## 326 END
## 327 START
## 328 END
## 329 START
## 330 END
## 331 START
## 332 END
## 333 START
## 334 END
## 335 START
## 336 END
## 337 START
## 338 END
## 339 START
## 340 END
## 341 START
## 342 END
## 343 START
## 344 END
## 345 START
## 346 END
## 347 START
## 348 END
## 349 START
## 350 END
## 351 START
## 352 END
## 353 START
## 354 END
## 355 START
## 356 END
## 357 START
## 358 END
## 359 START
## 360 END
## 361 START
## 362 END
## 363 START
## 364 END
## 365 START
## 366 END
## 367 START
## 368 END
## 369 START
## 370 END
## 371 START
## 372 END
## 373 START
## 374 END
## 375 START
## 376 END
## 377 START
## 378 END
## 379 START
## 380 END
## 381 START
## 382 END
## 383 START
## 384 END
## 385 START
## 386 END
## 387 START
## 388 END
## 389 START
## 390 END
## 391 START
## 392 END
## 393 START
## 394 END
## 395 START
## 396 END
## 397 START
## 398 END
## 399 START
## 400 END
## 401 START
## 402 END
## 403 START
## 404 END
## 405 START
## 406 END
## 407 START
## 408 END
## 409 START
## 410 END
## 411 START
## 412 END
## 413 START
## 414 END
## 415 START
## 416 END
## 417 START
## 418 END
## 419 START
## 420 END
## 421 START
## 422 END
## 423 START
## 424 END
## 425 START
## 426 END
## 427 START
## 428 END
## 429 START
## 430 END
## 431 START
## 432 END
## 433 START
## 434 END
## 435 START
## 436 END
## 437 START
## 438 END
## 439 START
## 440 END
## 441 START
## 442 END
## 443 START
## 444 END
## 445 START
## 446 END
## 447 START
## 448 END
## 449 START
## 450 END
## 451 START
## 452 END
## 453 START
## 454 ABORT
## 455 START
## 456 ABORT
## 457 START
## 458 END
## 459 START
## 460 END
## 461 START
## 462 END
## 463 START
## 464 END
## 465 START
## 466 END
## 467 START
## 468 END
## 469 START
## 470 END
## 471 START
## 472 END
## 473 START
## 474 END
## 475 START
## 476 END
## 477 START
## 478 END
## 479 START
## 480 END
## 481 START
## 482 END
## 483 START
## 484 END
## 485 START
## 486 END
## 487 START
## 488 END
## 489 START
## 490 END
## 491 START
## 492 END
## 493 START
## 494 END
## 495 START
## 496 END
## 497 START
## 498 END
## 499 START
## 500 END
## 501 START
## 502 END
## 503 START
## 504 END
## 505 START
## 506 END
## 507 START
## 508 ABORT
## 509 START
## 510 END
## 511 START
## 512 END
## 513 START
## 514 ABORT
## 515 START
## 516 ABORT
## 517 START
## 518 END
## 519 START
## 520 END
## 521 START
## 522 ABORT
## 523 START
## 524 END
## 525 START
## 526 END
## 527 START
## 528 END
## 529 START
## 530 END
## 531 START
## 532 END
## 533 START
## 534 END
## 535 START
## 536 END
## 537 START
## 538 END
## 539 START
## 540 END
## 541 START
## 542 END
## 543 START
## 544 END
## 545 START
## 546 END
## 547 START
## 548 END
## 549 START
## 550 END
## 551 START
## 552 END
## 553 START
## 554 END
## 555 START
## 556 END
## 557 START
## 558 END
## 559 START
## 560 END
## 561 START
## 562 END
## 563 START
## 564 END
## 565 START
## 566 END
## 567 START
## 568 END
## 569 START
## 570 END
## 571 START
## 572 END
## 573 START
## 574 END
## 575 START
## 576 END
## 577 START
## 578 END# Odd week morning - Nikita shift -> 1
# Even week morning - Sigitas shift -> 2
# Morning
MStartTime <- hms::as_hms("06:00:00")
MEndTime <- hms::as_hms("14:30:00")
#Evening
EStartTime <- hms::as_hms("14:30:00")
EEndTime <- hms::as_hms("23:10:00")
# Getting Week
March_2022 <- March_2022 %>%
mutate(Week = as.integer(strftime(Date, format = "%V")))
# Getting Shift by week and working hours
March_2022 <- March_2022 %>%
mutate(Shift = if_else(
IsOdd(Week),
if_else(between(hms::as_hms(Time), MStartTime, MEndTime), 1, 2),
if_else(between(hms::as_hms(Time), MStartTime, MEndTime), 2, 1)
))## Warning: between() called on numeric vector with S3 class
## between() called on numeric vector with S3 class# Getting Brand
March_2022 <- March_2022 %>%
rowwise() %>%
mutate(Brand = switch(
unlist(map(strsplit(Name, split = "\\\\"), 7)),
"dior_parfums" = "DIOR PARFUMS",
"louis_vuitton" = "LOUIS VUITTON",
"th" = "TOMMY HILFIGER",
"Unknown"))
# Getting ProductId
March_2022 <- March_2022 %>%
rowwise() %>%
mutate(ProductId = switch(
unlist(map(strsplit(Name, split = "\\\\"), 8)),
"_th50" = unlist(map(strsplit(Name, split = "\\\\"), 9)), # Use regex to fix all _th** with this line
unlist(map(strsplit(Name, split = "\\\\"), 8))
))
# Getting PartNumber
March_2022 <- March_2022 %>%
mutate(ProgramName = strsplit(Name, split = "\\\\")[[1]][[length(strsplit(Name, split = "\\\\")[[1]])]])
# Arrange by MachineID, DateTime and Type, make it tibble (for lead and lag functions)
March_2022 <- tibble(arrange(March_2022, MachineID, DateTime, Type))
# Get "START" type observations
Start_March_2022 <- March_2022 %>%
filter(Type == "START")
# Get "END" type observations
End_March_2022 <- March_2022 %>%
filter(Type != "START") %>%
select(Type, DateTime)
# Check whether "START" type observations count is equal to half of total observations count
count(Start_March_2022) == count(End_March_2022)## n
## [1,] TRUE# Add end time or abort time for each observation.
Start_March_2022$StopTime = End_March_2022$DateTime
Start_March_2022$StopType = End_March_2022$Type
# Base table example
# ProductId PartNumber Brand StartTime EndTime AbortTime IdleTime MchTime MfcTime Shift Machine
# 1.1 Table example
# Shift Machine MachinedPartsTableId
# 1 10 Id1
# 2 10 Id2
# 1 11 Id3
# 2 11 Id4
# So after that create separate related Tables, which will have data, that I will need
# I need my workbook....
# So what do I need to get?
# Performance calculations --> Priority
# Performance difference between shifts --> 1.
# 1.1. Get total machined parts for each shift
# 1.2. Top5, Min5, Average time for machined parts for each shift --> Another table
# 1.3. Machined brands divided by percentage
# 1.4. Total Idle time for each shift
# Performance difference between selected productId's mfc time --> 2.
# Performance difference between machines --> 3.
# Performance difference between selected time period --> 4.
# Total performance growth --> 5.
# Tools for pricing department --> For presentation
# Product Id Total mfc time (idle time + working hours)
# Potential price for electricity
# Potential product Id mfc time in % day shift / % night shift --> for final price
# Product Id cnc wood mfc auto total price ???
# Machine depreciation
# Tools for planning department --> For presentation
# Autodesk process analysis
# Average approximate time to finish
# Potential mfc start / finish date for product IdR Markdown
This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.
When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:
summary(cars)## speed dist
## Min. : 4.0 Min. : 2.00
## 1st Qu.:12.0 1st Qu.: 26.00
## Median :15.0 Median : 36.00
## Mean :15.4 Mean : 42.98
## 3rd Qu.:19.0 3rd Qu.: 56.00
## Max. :25.0 Max. :120.00Including Plots
You can also embed plots, for example:
Note that the echo = FALSE parameter was added to the
code chunk to prevent printing of the R code that generated the
plot.