library(readxl)
rm(list=ls())
library(readr)
library(DBI)
library(odbc)
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
#library(pracma)
library(RODBC)
library(readr)
library(tidyr)
library(xml2)
library(dplyr)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats 1.0.1 ✔ purrr 1.1.0
## ✔ ggplot2 4.0.0 ✔ stringr 1.5.2
## ✔ lubridate 1.9.4 ✔ tibble 3.3.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(keyring)
library(sendmailR)
library(xtable)
library(blastula)
library(gt)
## Warning: package 'gt' was built under R version 4.5.2
##
## Attaching package: 'gt'
##
## The following object is masked from 'package:blastula':
##
## md
library(glue)
#con <- dbConnect(odbc(), Driver = "SQL Server", Server = "Betahsml25\\betahsm",
# Database = "ENGDB", Trusted_Connection = "True")
#con_local <- dbConnect(odbc(), Driver = "SQL Server", Server = "MFRYE-LT",
# Database = "HSMQA", Trusted_Connection = "True")
#locl <- odbcDriverConnect("Driver={SQL Server};Server=MFRYE-LT;Database=HSMQA;Trusted_Connection=Yes")
library(RODBC)
con <- odbcConnect("onesource",uid="apollo_ro",pwd="apollo_ro")
# R
con_local <- dbConnect(odbc(), Driver = "SQL Server", Server = "NHAKE-DT",
Database = "HSMQA", Trusted_Connection = "True")
library(readxl)
#ageing_report <- read_excel("W:/Quality Assurance/Hot Mill/Quality Reports/Quality Report 2-5.xlsx",
# sheet = "Apex_Coil_Aging_Report")
ageing_report <- read_excel("C:/Users/rbathla/OneDrive - NLMK USA/DAILY_HFI/apex_coil_aging_report.xlsx")
ageing_report <- ageing_report[ageing_report$`Inventory Org`=='NLMK INDIANA INVENTORY',]
ageing_report <- ageing_report[ageing_report$Customer != 'NLMK INDIANA LLC STOCK',]
ageing_report <- ageing_report[,-which(names(ageing_report) %in% c("Hardness","Hardness Range","Inventory Org","Coated Flag","Sched Seq","Sched #","Item #","Slab Id","Order Type","Schedule Date", "Op Code", "Hold Status","Request Date" ))]
sorted_df <- arrange(ageing_report, desc(`Days Elps`))
sorted_df$`Last Scan Date` <- as.Date(sorted_df$`Last Scan Date`, origin = "1899-12-30")
#sorted_df$`Request Date` <- as.Date(sorted_df$`Request Date`)
#sorted_df$`Schedule Date` <- as.Date(sorted_df$`Schedule Date`)
sorted_df$`Promise Date` <- as.Date(sorted_df$`Promise Date`)
por_coils_all <- dbGetQuery(con_local,"select * from quality.dbo.por_coils where customer <> 'NLMK INDIANA LLC STOCK' ")
por_coils <- dbGetQuery(con_local,"select [Coil ID],[HSM Date] as PROD_DATE, status,HOLD_CODES,[HFI Flag]
,isnull([HFI Disposition Type],'') + isnull(case when [HFI Flag]='Y' and [HFI Disposition Type] is null then 'QA_NON_DISPO' end, '') as DISPO_TYPE
from quality.dbo.por_coils where customer <> 'NLMK INDIANA LLC STOCK' ")
df <- merge(sorted_df,por_coils,all.x=T)
new_order <- c("Coil ID" , "Customer" , "Isr" , "Grade" , "Coil Size" ,
"Finish" ,"PROD_DATE" , "Promise Date" , "Ord Gauge" ,
"Ord Width" , "Ord Grade" , "Work Order" , "Heat Number" ,
"Carrier Type" , "Last Scan Location" ,"Last Scan Date" , "Last Scan By" ,
"HOLD_CODES" ,"HFI Flag" , "DISPO_TYPE"
, "Previous Step" , "First Step" , "Second Step","status" ,"Current Step" , "Days Elps" )
df <- df[, new_order]
df$PROD_DATE <- as.Date(df$PROD_DATE)
df <- arrange(df, desc(`Days Elps`))
df_osp <- filter(df, grepl("^osp", `Current Step`, ignore.case = TRUE)|grepl("^porrcv", `Current Step`, ignore.case = TRUE))
df_osp <- df_osp[df_osp$`Days Elps` >= 15, ]
df_ih <- filter(df, !grepl("^osp", `Current Step`, ignore.case = TRUE) & !grepl("^porrcv", `Current Step`, ignore.case = TRUE) )
df_ih <- df_ih[df_ih$`Days Elps` >= 8, ]
library(openxlsx)
## Warning: package 'openxlsx' was built under R version 4.5.2
wb <- createWorkbook()
addWorksheet(wb, "Ageing_COILS")
writeDataTable(wb, "Ageing_COILS", x = df_ih, startRow = 1, startCol = 1,
withFilter = TRUE, tableStyle = "TableStyleLight9")
start_row_df2 <- 1 + nrow(df_ih) + 1 + 2
writeDataTable(wb, "Ageing_COILS", x = df_osp, startRow = start_row_df2, startCol = 1,
withFilter = TRUE, tableStyle = "TableStyleLight9")
# IMPORTANT: no setColWidths() here
p_dir <- "C:/Users/rbathla/OneDrive - NLMK USA/DAILY_HFI/Slow_Moving_Coils"
p1 <- file.path(p_dir, paste0("Slow_Moving_Coils_", as.character(Sys.Date()), ".xlsx"))
p2 <- file.path(p_dir, "Slow_Moving_Coils.xlsx")
saveWorkbook(wb, p1, overwrite = TRUE) # 1st save
saveWorkbook(wb, p2, overwrite = TRUE) # 2nd save
library(openxlsx)
# Create a new workbook
wb <- createWorkbook()
# Add a sheet
addWorksheet(wb, "Ageing_COILS")
# Write first dataframe starting at cell A1
# Write as an Excel Table
writeDataTable(
wb,
sheet = "Ageing_COILS",
x = df_ih, startRow = 1, startCol = 1,
withFilter = TRUE, # adds autofilter to the header
tableStyle = "TableStyleLight9" # optional styling
)
# Leave 2 blank rows, then place the second table
n_rows_df1 <- nrow(df_ih) + 1 # +1 for header
gap <- 2
start_row_df2 <- 1 + n_rows_df1 + gap
# Write as an Excel Table
writeDataTable(
wb,
sheet = "Ageing_COILS",
x = df_osp, startRow = start_row_df2, startCol = 1,
withFilter = TRUE, # adds autofilter to the header
tableStyle = "TableStyleLight9" # optional styling
)
setColWidths(wb, "Ageing_COILS",cols = 1:ncol(df_osp), widths = "auto")
# Write second dataframe starting at cell A6 (below df1)
#writeData(wb, sheet = "OSP_COILS", x = df_osp, as_table = TRUE)
# Save the workbook datedversion
saveWorkbook(wb, paste0("C:/Users/rbathla/OneDrive - NLMK USA/DAILY_HFI/Slow_Moving_Coils/Slow_Moving_Coils_",paste0(today()),".xlsx"), overwrite = TRUE)
# Save the workbook generic version
saveWorkbook(wb,"C:/Users/rbathla/OneDrive - NLMK USA/DAILY_HFI/Slow_Moving_Coils/Slow_Moving_Coils.xlsx", overwrite = TRUE)
cat("Excel file created: Slow_Moving_Coils.xlsx\n")
sql <- c(' SELECT case when HOLD_CODES is null then \'--?--\' else HOLD_CODES end as HOLD_CODES
, count(*) as NUM_COILS
FROM QUALITY.dbo.v_POR_COILS
where [HFI Flag] =\'Y\'
and [HFI Disposition Type] is null
group by HOLD_CODES
order by count(*) desc;
')
pivot_non_dispo <- dbGetQuery(con_local,sql)
receipient_list = c("rbathla@us.nlmk.com")#,"nhake@us.nlmk.com")
tbl_html <- pivot_non_dispo |>
gt() |>
tab_options(
table.border.top.style = "solid",
table.border.top.width = gt::px(1),
table.border.top.color = "#ccc",
table.border.bottom.style= "solid",
table.border.bottom.width= gt::px(1),
table.border.bottom.color= "#ccc"
) |>
# Borders around header cells
tab_style(
style = cell_borders(
sides = c("left", "right", "top", "bottom"),
color = "#ccc",
weight = gt::px(1)
),
locations = cells_column_labels(everything())
) |>
# Borders around body cells
tab_style(
style = cell_borders(
sides = c("left", "right", "top", "bottom"),
color = "#ccc",
weight = gt::px(1)
),
locations = cells_body()
) |>
as_raw_html() # returns a character string with inline styles
email <- compose_email(
body = blocks(
md(sprintf("**Total slow moving Coils : %d" , NROW(df_ih$`Coil ID`) + NROW(df_ih$`Coil ID`)))
)
)
# 2. Attach a file (ensure the file exists in your working directory)
file_path <- "Slow_Moving_Coils.xlsx" # Change to your file path
if (!file.exists(file_path)) {
stop(paste("Attachment not found:", file_path))
}
email <- email %>%
add_attachment(file = file_path)
if(NROW(df_ih)>0){
smtp_send(
email,
from = "nlmkpor@gmail.net",
to = receipient_list,
subject = sprintf("Slow moving Coils Summary: " ),
credentials = creds_key("gmail")
)
}