Education

1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23library(tidyverse)
24exam_dat <- 
25  bind_rows(trt_fit1[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 1"), 
26            trt_fit2[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 2"))
27  
28
29ggplot(data = exam_dat, aes(x = x, y = y, colour = fit)) +
30  geom_line() +
31  labs(x = "Examination", y = "s(Examination)") + 
32  theme_bw() +
33  theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank())
34

1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23library(tidyverse)
24exam_dat <- 
25  bind_rows(trt_fit1[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 1"), 
26            trt_fit2[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 2"))
27  
28
29ggplot(data = exam_dat, aes(x = x, y = y, colour = fit)) +
30  geom_line() +
31  labs(x = "Examination", y = "s(Examination)") + 
32  theme_bw() +
33  theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank())
34gridExtra::grid.arrange(
35  trt_fit1[["plots"]][[2]]$ggObj, 
36  trt_fit2[["plots"]][[2]]$ggObj, 
37  nrow = 1)
38

1mydf <- data.frame(
2  SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of  listening radio', NA, NA, NA ),
3  Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest',  NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4  AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5  OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6  ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7  ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8  Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),  
9  stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16     xlim=c(-0.8,6.2), ylim=c(0,42),
17     xlab='', ylab='', yaxt='n', xaxt='n',
18     bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42), 
50     xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90')   ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70

1mydf <- data.frame(
2  SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of  listening radio', NA, NA, NA ),
3  Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest',  NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4  AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5  OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6  ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7  ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8  Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),  
9  stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16     xlim=c(-0.8,6.2), ylim=c(0,42),
17     xlab='', ylab='', yaxt='n', xaxt='n',
18     bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42), 
50     xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90')   ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
82

1mydf <- data.frame(
2  SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of  listening radio', NA, NA, NA ),
3  Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest',  NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4  AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5  OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6  ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7  ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8  Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),  
9  stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16     xlim=c(-0.8,6.2), ylim=c(0,42),
17     xlab='', ylab='', yaxt='n', xaxt='n',
18     bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42), 
50     xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90')   ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
82## plot
83png('temp.png', width=18, height=12, units='in', res=400)
84
85op <- par(mar=c(5, 18.5, 4, 15)+.1)
86
87plot(mydf$OddsRatio, rowseq, type='n', xlim=rg, axes=FALSE, xlab='', ylab='')
88## content
89abline(h=rowseq[rowseq %% 2 == 0], lwd=20, col='grey95', xpd=TRUE)  ## grey shades
90points(mydf$OddsRatio, rowseq, pch=15)
91segments(1, 0, 1, max(rowseq)*1.025, lty=3)
92segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
93## margin 1
94axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
95mtext('Adjusted Odds Ratio (95% CI)', 1, line=2.5, at=1.2, cex=cexh1, font=2)
96## margin 2
97mtext("Factors", 2, 17.5, at=max(rowseq)*1.03, las=2, adj=0, font=2, cex=cexh1)
98mtext(t2h, 2, 17.5, at=rowseq, las=2, adj=0, font=2, cex=cexh2)
99mtext(t2, 2, 17, at=rowseq, las=2, adj=0, cex=cext)
100## margin 4
101mtext("Adjusted Odds Ratio (95% CI)", 4, 7, at=max(rowseq)*1.03, las=2, adj=1,
102      font=2, cex=cexh1)
103mtext(t4or, 4, 7, at=rowseq, las=2, adj=1, cex=cext)
104mtext("P-value", 4, 12, at=max(rowseq)*1.03, las=2, adj=1, font=2, cex=cexh1)
105mtext(t4p, 4, 12, at=rowseq, las=2, adj=1, cex=cext)
106
107par(op)
108
109dev.off()
110

1mydf <- data.frame(
2  SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of  listening radio', NA, NA, NA ),
3  Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest',  NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4  AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5  OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6  ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7  ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8  Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),  
9  stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16     xlim=c(-0.8,6.2), ylim=c(0,42),
17     xlab='', ylab='', yaxt='n', xaxt='n',
18     bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42), 
50     xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90')   ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
82## plot
83png('temp.png', width=18, height=12, units='in', res=400)
84
85op <- par(mar=c(5, 18.5, 4, 15)+.1)
86
87plot(mydf$OddsRatio, rowseq, type='n', xlim=rg, axes=FALSE, xlab='', ylab='')
88## content
89abline(h=rowseq[rowseq %% 2 == 0], lwd=20, col='grey95', xpd=TRUE)  ## grey shades
90points(mydf$OddsRatio, rowseq, pch=15)
91segments(1, 0, 1, max(rowseq)*1.025, lty=3)
92segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
93## margin 1
94axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
95mtext('Adjusted Odds Ratio (95% CI)', 1, line=2.5, at=1.2, cex=cexh1, font=2)
96## margin 2
97mtext("Factors", 2, 17.5, at=max(rowseq)*1.03, las=2, adj=0, font=2, cex=cexh1)
98mtext(t2h, 2, 17.5, at=rowseq, las=2, adj=0, font=2, cex=cexh2)
99mtext(t2, 2, 17, at=rowseq, las=2, adj=0, cex=cext)
100## margin 4
101mtext("Adjusted Odds Ratio (95% CI)", 4, 7, at=max(rowseq)*1.03, las=2, adj=1,
102      font=2, cex=cexh1)
103mtext(t4or, 4, 7, at=rowseq, las=2, adj=1, cex=cext)
104mtext("P-value", 4, 12, at=max(rowseq)*1.03, las=2, adj=1, font=2, cex=cexh1)
105mtext(t4p, 4, 12, at=rowseq, las=2, adj=1, cex=cext)
106
107par(op)
108
109dev.off()
110mydf <- structure(list(SubgroupH = c("Age", NA, NA, NA, NA, "Marital or Union Status", 
111NA, NA, NA, "Place of Residence", NA, NA, "Education", NA, NA, 
112NA, NA, "Occupation", NA, NA, NA, NA, "Wealth", NA, NA, NA, NA, 
113NA, "Reading newspaper or magazine", NA, NA, NA, "Frequency of watching television", 
114NA, NA, NA, "Frequency of  listening radio", NA, NA, NA), Subgroup = c(NA, 
115"15-19", "20-29", "30-39", "40-49", NA, "Currently Married or Union", 
116"Never Married or Union", "Formally Married or Union", NA, "Rural", 
117"Urban", NA, "Higher", "Secondary", "Primary", "No eduction", 
118NA, "Not working", "Professional/technical/managerial/services", 
119"Agriculture", "Skilled/unskilled manual", NA, "Poorest", "Poorer", 
120"Middle", "Richer", "Richest", NA, "Not at all", "Less than once a week", 
121"At least once a week", NA, "Not at all", "Less than once a week", 
122"At least once a week", NA, "Not at all", "Less than once a week", 
123"At least once a week"), AdjustedOR = c(NA, "1", "2.76 (2.49-3.07)", 
124"3.68 (3.26-4.15)", "4.61 (4.06-5.23)", NA, "1", "1.03 (0.94-1.13)", 
125"1.26 (1.04-1.54)", NA, "1", "1.12 (1.04-1.21)", NA, "1", "1.42 (1.30-1.56)", 
126"2.09 (1.85-2.36)", "2.20 (1.93-2.49)", NA, "1", "1.39 (1.25-1.54)", 
127"1.37 (1.24-1.51)", "1.55 (1.39-1.72)", NA, "1", "0.91 (0.84-0.99)", 
128"0.77 (0.71-0.84)", "0.72 (0.65-0.79)", "0.61 (0.54-0.69)", NA, 
129"1", "1 (0.91-1.10)", "0.92 (0.85-0.998)", NA, "1", "1.19 (1.07-1.31)", 
130"1.29 (1.19-1.40)", NA, "1", "1.19 (1.09-1.30)", "1.13 (1.05-1.21)"
131), OddsRatio = c(NA, 1, 2.76, 3.68, 4.61, NA, 1, 1.03, 1.26, 
132NA, 1, 1.12, NA, 1, 1.42, 2.09, 2.2, NA, 1, 1.39, 1.37, 1.55, 
133NA, 1, 0.91, 0.77, 0.78, 0.61, NA, 1, 1, 0.92, NA, 1, 1.19, 1.29, 
134NA, 1, 1.19, 1.13), ORLower = c(NA, NA, 2.49, 3.26, 4.06, NA, 
135NA, 0.94, 1.04, NA, NA, 1.04, NA, NA, 1.3, 1.85, 1.93, NA, NA, 
1361.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 
1370.85, NA, NA, 1.07, 1.19, NA, NA, 1.09, 1.05), ORUpper = c(NA, 
138NA, 3.07, 4.15, 5.23, NA, NA, 1.13, 1.54, NA, NA, 1.21, NA, NA, 
1391.56, 2.36, 2.49, NA, NA, 1.54, 1.51, 1.72, NA, NA, 0.99, 0.84, 
1400.79, 0.69, NA, NA, 1.1, 0.998, NA, NA, 1.31, 1.4, NA, NA, 1.3, 
1411.21), Pvalue = c(NA, NA, "< 0.001", "< 0.001", "< 0.001", NA, 
142NA, "0.518", "0.021", NA, NA, "0.003", NA, NA, "< 0.001", "< 0.001", 
143"< 0.001", NA, NA, "< 0.001", "< 0.001", "< 0.001", NA, NA, "0.029", 
144"< 0.001", "< 0.001", "< 0.001", NA, NA, "0.993", "0.045", NA, 
145NA, "< 0.001", "< 0.001", NA, NA, "< 0.001", "0.002")), class = "data.frame", row.names = c(NA, 
146-40L))
147

1SELECT 
2    R_ID,
3    R_From,
4    R_To,
5    SUM(UR_TotalTime) AS UR_TotalTime,
6    R_Reported,
7    U_ID,
8    U_Lastname,
9    U_Firstname,
10    C_ID,
11    C_Lastname,
12    C_Firstname,
13    R_Breaks,
14    MAX(CR_BID) AS CR_BID,
15    R_Type,
16    R_Distance,
17    R_AdditionalDistance,
18    R_Activities,
19    R_Description,
20    R_Signature,
21    CT_SigReq,
22    MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24    reports
25        LEFT JOIN
26    userreports ON R_ID = UR_RID
27        LEFT JOIN
28    users ON R_UID = U_ID
29        LEFT JOIN
30    customers ON R_CID = C_ID
31        LEFT JOIN
32    customerterms ON CT_CID = R_CID
33        LEFT JOIN
34    integration ON R_UID = I_UID
35        LEFT JOIN
36    customerreports ON R_ID = CR_RID
37WHERE
38    (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39        OR R_CID = 0)
40        AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41        OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42        OR (R_From <= '2021-02-01 00.00.00'
43        AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports`  (
47  `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48  `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49  `R_UID` int(6) NOT NULL,
50  `R_CID` int(10) NOT NULL,
51  `R_From` datetime(0) NOT NULL,
52  `R_To` datetime(0) NOT NULL,
53  `R_Traveltime` int(11) NOT NULL,
54  `R_Breaks` int(11) NOT NULL DEFAULT 0,
55  `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56  `R_Distance` int(11) NOT NULL DEFAULT 0,
57  `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58  `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59  `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60  `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61  `R_SignatureDate` datetime(0) DEFAULT NULL,
62  `R_Reported` datetime(0) NOT NULL,
63  `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64  `R_EditedBy` int(11) DEFAULT NULL,
65  `R_EditedDateTime` datetime(0) DEFAULT NULL,
66  PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports`  (
69  `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70  `UR_RID` int(100) NOT NULL,
71  `UR_UID` int(6) NOT NULL,
72  `UR_Date` date NOT NULL,
73  `UR_From` time(0) NOT NULL,
74  `UR_To` time(0) NOT NULL,
75  `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76  `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77  `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78  `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79  `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80  `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81  `UR_Distance` decimal(20, 2) DEFAULT NULL,
82  `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83  `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84  PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports`  (
87  `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88  `CR_RID` int(100) NOT NULL,
89  `CR_CID` int(6) NOT NULL,
90  `CR_Date` date NOT NULL,
91  `CR_From` time(0) NOT NULL,
92  `CR_To` time(0) NOT NULL,
93  `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94  `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95  `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96  `CR_Salary` decimal(20, 2) DEFAULT NULL,
97  `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98  `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99  `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100  `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101  `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102  `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103  `CR_Distance` decimal(20, 2) DEFAULT NULL,
104  `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105  `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106  `CR_BID` int(11) NOT NULL DEFAULT 0,
107  PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users`  (
110  `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111  `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112  `U_PWInitial` tinyint(1) NOT NULL,
113  `U_FailedAttempts` int(1) NOT NULL,
114  `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115  `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116  `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117  `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118  `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119  `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120  `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121  `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122  `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123  `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124  `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125  `U_Birthdate` date NOT NULL,
126  `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127  `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128  `U_Severelydisabled` tinyint(1) NOT NULL,
129  `U_Severelydisabledspecify` int(3) NOT NULL,
130  `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131  `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132  `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133  `U_CLID` tinyint(1) NOT NULL,
134  `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135  `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136  `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137  `U_INID` int(11) DEFAULT NULL,
138  `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139  `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140  `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141  `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142  `U_Entry` date NOT NULL,
143  `U_TEntry` date NOT NULL,
144  `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145  `U_Hourscarryover` decimal(20, 2) NOT NULL,
146  `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147  `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148  `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149  `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150  `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151  `U_DeleteDate` date DEFAULT NULL,
152  PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers`  (
155  `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156  `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157  `C_Active` tinyint(1) NOT NULL,
158  `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159  `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160  `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161  `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162  `C_Birthdate` date NOT NULL,
163  `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164  `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165  `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166  `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167  `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168  `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169  `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170  `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171  `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172  `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173  `C_INID` int(11) DEFAULT NULL,
174  `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175  `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176  `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177  `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178  `C_ContactChoice` int(1) DEFAULT 0,
179  `C_DeleteDate` date DEFAULT NULL,
180  `C_DeactivationDate` date DEFAULT NULL,
181  `C_CreationDate` date DEFAULT NULL,
182  `C_DeceasedDate` date DEFAULT NULL,
183  PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration`  (
186  `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187  `I_UID` int(11) NOT NULL,
188  `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189  `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190  `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191  PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms`  (
194  `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195  `CT_CID` int(10) NOT NULL,
196  `CT_From` date NOT NULL,
197  `CT_To` date NOT NULL,
198  `CT_Hourly` decimal(20, 2) NOT NULL,
199  `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200  `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201  `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202  `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203  `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204  `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205  `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206  `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207  `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208  `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209  `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210  `CT_SigReq` int(1) NOT NULL,
211  `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212  `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213  `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214  `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215  `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216  `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217  `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218  PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id  | select_type  | table            | partitions  | type    | possible_keys        | key      | key_len  | ref                      | rows  | filtered  | Extra                                               |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1   | SIMPLE       | reports          | NULL        | ALL     | PRIMARY,R_From,R_To  | NULL     | NULL     | NULL                     | 22249 | 29.76     | Using where; Using temporary; Using filesort        |
224| 1   | SIMPLE       | userreports      | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 21359 | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
225| 1   | SIMPLE       | users            | NULL        | eq_ref  | PRIMARY              | PRIMARY  | 4        | dbs671769.reports.R_UID  | 1     | 100.00    | NULL                                                |
226| 1   | SIMPLE       | customers        | NULL        | eq_ref  | PRIMARY              | PRIMARY  | 4        | dbs671769.reports.R_CID  | 1     | 100.00    | NULL                                                |
227| 1   | SIMPLE       | customerterms    | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 1429  | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
228| 1   | SIMPLE       | integration      | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 1134  | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
229| 1   | SIMPLE       | customerreports  | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 9078  | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231ALTER TABLE `userreports`
232    ADD INDEX `FK_UR_RID` (`UR_RID`);
233
234ALTER TABLE `customerterms`
235    ADD INDEX `FK_CT_CID` (`CT_CID`);
236
237ALTER TABLE `integration`
238    ADD INDEX `FK_I_UID` (`I_UID`);
239
240ALTER TABLE `customerreports`
241    ADD INDEX `FK_CR_RID` (`CR_RID`);
242

1SELECT 
2    R_ID,
3    R_From,
4    R_To,
5    SUM(UR_TotalTime) AS UR_TotalTime,
6    R_Reported,
7    U_ID,
8    U_Lastname,
9    U_Firstname,
10    C_ID,
11    C_Lastname,
12    C_Firstname,
13    R_Breaks,
14    MAX(CR_BID) AS CR_BID,
15    R_Type,
16    R_Distance,
17    R_AdditionalDistance,
18    R_Activities,
19    R_Description,
20    R_Signature,
21    CT_SigReq,
22    MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24    reports
25        LEFT JOIN
26    userreports ON R_ID = UR_RID
27        LEFT JOIN
28    users ON R_UID = U_ID
29        LEFT JOIN
30    customers ON R_CID = C_ID
31        LEFT JOIN
32    customerterms ON CT_CID = R_CID
33        LEFT JOIN
34    integration ON R_UID = I_UID
35        LEFT JOIN
36    customerreports ON R_ID = CR_RID
37WHERE
38    (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39        OR R_CID = 0)
40        AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41        OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42        OR (R_From <= '2021-02-01 00.00.00'
43        AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports`  (
47  `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48  `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49  `R_UID` int(6) NOT NULL,
50  `R_CID` int(10) NOT NULL,
51  `R_From` datetime(0) NOT NULL,
52  `R_To` datetime(0) NOT NULL,
53  `R_Traveltime` int(11) NOT NULL,
54  `R_Breaks` int(11) NOT NULL DEFAULT 0,
55  `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56  `R_Distance` int(11) NOT NULL DEFAULT 0,
57  `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58  `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59  `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60  `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61  `R_SignatureDate` datetime(0) DEFAULT NULL,
62  `R_Reported` datetime(0) NOT NULL,
63  `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64  `R_EditedBy` int(11) DEFAULT NULL,
65  `R_EditedDateTime` datetime(0) DEFAULT NULL,
66  PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports`  (
69  `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70  `UR_RID` int(100) NOT NULL,
71  `UR_UID` int(6) NOT NULL,
72  `UR_Date` date NOT NULL,
73  `UR_From` time(0) NOT NULL,
74  `UR_To` time(0) NOT NULL,
75  `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76  `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77  `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78  `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79  `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80  `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81  `UR_Distance` decimal(20, 2) DEFAULT NULL,
82  `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83  `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84  PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports`  (
87  `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88  `CR_RID` int(100) NOT NULL,
89  `CR_CID` int(6) NOT NULL,
90  `CR_Date` date NOT NULL,
91  `CR_From` time(0) NOT NULL,
92  `CR_To` time(0) NOT NULL,
93  `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94  `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95  `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96  `CR_Salary` decimal(20, 2) DEFAULT NULL,
97  `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98  `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99  `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100  `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101  `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102  `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103  `CR_Distance` decimal(20, 2) DEFAULT NULL,
104  `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105  `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106  `CR_BID` int(11) NOT NULL DEFAULT 0,
107  PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users`  (
110  `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111  `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112  `U_PWInitial` tinyint(1) NOT NULL,
113  `U_FailedAttempts` int(1) NOT NULL,
114  `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115  `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116  `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117  `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118  `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119  `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120  `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121  `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122  `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123  `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124  `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125  `U_Birthdate` date NOT NULL,
126  `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127  `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128  `U_Severelydisabled` tinyint(1) NOT NULL,
129  `U_Severelydisabledspecify` int(3) NOT NULL,
130  `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131  `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132  `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133  `U_CLID` tinyint(1) NOT NULL,
134  `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135  `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136  `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137  `U_INID` int(11) DEFAULT NULL,
138  `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139  `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140  `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141  `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142  `U_Entry` date NOT NULL,
143  `U_TEntry` date NOT NULL,
144  `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145  `U_Hourscarryover` decimal(20, 2) NOT NULL,
146  `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147  `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148  `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149  `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150  `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151  `U_DeleteDate` date DEFAULT NULL,
152  PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers`  (
155  `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156  `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157  `C_Active` tinyint(1) NOT NULL,
158  `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159  `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160  `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161  `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162  `C_Birthdate` date NOT NULL,
163  `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164  `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165  `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166  `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167  `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168  `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169  `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170  `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171  `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172  `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173  `C_INID` int(11) DEFAULT NULL,
174  `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175  `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176  `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177  `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178  `C_ContactChoice` int(1) DEFAULT 0,
179  `C_DeleteDate` date DEFAULT NULL,
180  `C_DeactivationDate` date DEFAULT NULL,
181  `C_CreationDate` date DEFAULT NULL,
182  `C_DeceasedDate` date DEFAULT NULL,
183  PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration`  (
186  `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187  `I_UID` int(11) NOT NULL,
188  `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189  `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190  `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191  PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms`  (
194  `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195  `CT_CID` int(10) NOT NULL,
196  `CT_From` date NOT NULL,
197  `CT_To` date NOT NULL,
198  `CT_Hourly` decimal(20, 2) NOT NULL,
199  `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200  `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201  `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202  `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203  `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204  `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205  `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206  `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207  `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208  `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209  `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210  `CT_SigReq` int(1) NOT NULL,
211  `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212  `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213  `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214  `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215  `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216  `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217  `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218  PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id  | select_type  | table            | partitions  | type    | possible_keys        | key      | key_len  | ref                      | rows  | filtered  | Extra                                               |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1   | SIMPLE       | reports          | NULL        | ALL     | PRIMARY,R_From,R_To  | NULL     | NULL     | NULL                     | 22249 | 29.76     | Using where; Using temporary; Using filesort        |
224| 1   | SIMPLE       | userreports      | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 21359 | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
225| 1   | SIMPLE       | users            | NULL        | eq_ref  | PRIMARY              | PRIMARY  | 4        | dbs671769.reports.R_UID  | 1     | 100.00    | NULL                                                |
226| 1   | SIMPLE       | customers        | NULL        | eq_ref  | PRIMARY              | PRIMARY  | 4        | dbs671769.reports.R_CID  | 1     | 100.00    | NULL                                                |
227| 1   | SIMPLE       | customerterms    | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 1429  | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
228| 1   | SIMPLE       | integration      | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 1134  | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
229| 1   | SIMPLE       | customerreports  | NULL        | ALL     | NULL                 | NULL     | NULL     | NULL                     | 9078  | 100.00    | Using where; Using join buffer (Block Nested Loop)  |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231ALTER TABLE `userreports`
232    ADD INDEX `FK_UR_RID` (`UR_RID`);
233
234ALTER TABLE `customerterms`
235    ADD INDEX `FK_CT_CID` (`CT_CID`);
236
237ALTER TABLE `integration`
238    ADD INDEX `FK_I_UID` (`I_UID`);
239
240ALTER TABLE `customerreports`
241    ADD INDEX `FK_CR_RID` (`CR_RID`);
242-- this just retrieves some basic stats about size of each table used in your query
243SELECT TABLE_NAME, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
244FROM information_schema.TABLES
245WHERE TABLE_SCHEMA = 'dbs671769'
246AND TABLE_NAME IN('customerreports', 'customers', 'customerterms', 'integration', 'reports', 'userreports', 'users');
247

1var curated_database = {};
2
3var database = {
4    0: [{name: 'Micheal'}, 
5        {age: 45},
6        {education: 'BA'},  
7        {income: 245000},  
8        {occupation: 'director'}],
9    1: [{name: 'John'}, 
10        {age: 23},
11        {education: 'BA'},  
12        {income: 60000},  
13        {occupation: 'manager'}],
14    2: [{name: 'Judith'}, 
15        {age: 45},
16        {education: 'PhD'},  
17        {income: 140000},  
18        {occupation: 'professor'}],
19    3: [{name: 'Gill'}, 
20        {age: 28},
21        {education: 'MS'},  
22        {income: 98000},  
23        {occupation: 'scientist'}],
24    4: [{name: 'Dave'}, 
25        {age: 17},
26        {education: 'HS'},  
27        {income: 30000},  
28        {occupation: 'retail associate'}]
29};
30
31curated_database = { 
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39    desired_contents = [0,3,4]
40    for(contents in desired_contents){
41    console.log(database[data][desired_contents[contents]]);
42    }
43    var k = database[data][0];
44    if (!currated_database[k.key]) {
45        currated_database[k.key] = [];
46    }
47        currated_database[k.key].push(k.val);
48}
49var curated_database = {};
50
51var database = {
52    0: [{name: 'Micheal'},
53        {age: 45},
54        {education: 'BA'},
55        {income: 245000},
56        {occupation: 'director'}],
57    1: [{name: 'John'},
58        {age: 23},
59        {education: 'BA'},
60        {income: 60000},
61        {occupation: 'manager'}],
62    2: [{name: 'Judith'},
63        {age: 45},
64        {education: 'PhD'},
65        {income: 140000},
66        {occupation: 'professor'}],
67    3: [{name: 'Gill'},
68        {age: 28},
69        {education: 'MS'},
70        {income: 98000},
71        {occupation: 'scientist'}],
72    4: [{name: 'Dave'},
73        {age: 17},
74        {education: 'HS'},
75        {income: 30000},
76        {occupation: 'retail associate'}]
77};
78
79
80
81var indexToExtract = new Set([0,3,4]);
82
83    var wantedLst = Object.values(database)
84    .map(lst => lst.filter((ob, idx) => indexToExtract.has(idx)))
85    .map((lst, idx) => curated_database[idx] = lst.map(ob => Object.values(ob)[0]))
86    
87    console.log(curated_database);

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low"    "medium" "high"  
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low"    "medium" "high"  
32# 
33# $class
34# [1] "factor"
35

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low"    "medium" "high"  
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low"    "medium" "high"  
32# 
33# $class
34# [1] "factor"
35attributes(factor(1:3, labels=c("low", "medium", "high")))
36# $levels
37# [1] "low"    "medium" "high"  
38# 
39# $class
40# [1] "factor"
41

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low"    "medium" "high"  
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low"    "medium" "high"  
32# 
33# $class
34# [1] "factor"
35attributes(factor(1:3, labels=c("low", "medium", "high")))
36# $levels
37# [1] "low"    "medium" "high"  
38# 
39# $class
40# [1] "factor"
41stopifnot(all.equal(unclass(f), 
42                    unclass(factor(1:3, labels=c("low", "medium", "high")))))
43

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low"    "medium" "high"  
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low"    "medium" "high"  
32# 
33# $class
34# [1] "factor"
35attributes(factor(1:3, labels=c("low", "medium", "high")))
36# $levels
37# [1] "low"    "medium" "high"  
38# 
39# $class
40# [1] "factor"
41stopifnot(all.equal(unclass(f), 
42                    unclass(factor(1:3, labels=c("low", "medium", "high")))))
43f <- 3:1
44levels(f) <- c("low", "medium", "high")
45class(f) <- 'factor'
46f
47# [1] high   medium low   
48# Levels: low medium high
49

1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high")  ## mark
14class(f) <- "factor"
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low"    "medium" "high"  
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low"    "medium" "high"  
32# 
33# $class
34# [1] "factor"
35attributes(factor(1:3, labels=c("low", "medium", "high")))
36# $levels
37# [1] "low"    "medium" "high"  
38# 
39# $class
40# [1] "factor"
41stopifnot(all.equal(unclass(f), 
42                    unclass(factor(1:3, labels=c("low", "medium", "high")))))
43f <- 3:1
44levels(f) <- c("low", "medium", "high")
45class(f) <- 'factor'
46f
47# [1] high   medium low   
48# Levels: low medium high
49g <- 2:4
50levels(g) <- c("low", "medium", "high")
51class(g) <- 'factor'
52g
53# Error in as.character.factor(x) : malformed factor
54
55h <- c(1, 3, 4)
56levels(h) <- c("low", "medium", "high")
57class(h) <- 'factor'
58# Error in class(h) <- "factor" : 
59#   adding class "factor" to an invalid object
60

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '<div>Hello world!</div>';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    <RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    />
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() => {
27      setIsToastVisible((v) => !v);
28    }, 30);
29    return () => {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    <ScrollView contentContainerStyle={styles.container}>
35      <WebDisplay html={html} />
36      {isToastVisible && <Text>This is a toast!</Text>}
37    </ScrollView>
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46const WebDisplay = React.memo(function WebDisplay({html}) {
47  const {width: contentWidth} = useWindowDimensions();
48  const tagsStyles = {
49    a: {
50      textDecorationLine: 'none',
51    },
52  };
53  return (
54    <RenderHtml
55      contentWidth={contentWidth}
56      source={{html}}
57      tagsStyles={tagsStyles}
58    />
59  );
60});
61

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '<div>Hello world!</div>';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    <RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    />
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() => {
27      setIsToastVisible((v) => !v);
28    }, 30);
29    return () => {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    <ScrollView contentContainerStyle={styles.container}>
35      <WebDisplay html={html} />
36      {isToastVisible && <Text>This is a toast!</Text>}
37    </ScrollView>
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46const WebDisplay = React.memo(function WebDisplay({html}) {
47  const {width: contentWidth} = useWindowDimensions();
48  const tagsStyles = {
49    a: {
50      textDecorationLine: 'none',
51    },
52  };
53  return (
54    <RenderHtml
55      contentWidth={contentWidth}
56      source={{html}}
57      tagsStyles={tagsStyles}
58    />
59  );
60});
61const tagsStyles = {
62  a: {
63    textDecorationLine: 'none',
64  },
65};
66
67function WebDisplay({html}) {
68  const {width: contentWidth} = useWindowDimensions();
69  return (
70    <RenderHtml
71      contentWidth={contentWidth}
72      source={{html}}
73      tagsStyles={tagsStyles}
74    />
75  );
76};
77

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '<div>Hello world!</div>';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    <RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    />
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() => {
27      setIsToastVisible((v) => !v);
28    }, 30);
29    return () => {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    <ScrollView contentContainerStyle={styles.container}>
35      <WebDisplay html={html} />
36      {isToastVisible && <Text>This is a toast!</Text>}
37    </ScrollView>
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46const WebDisplay = React.memo(function WebDisplay({html}) {
47  const {width: contentWidth} = useWindowDimensions();
48  const tagsStyles = {
49    a: {
50      textDecorationLine: 'none',
51    },
52  };
53  return (
54    <RenderHtml
55      contentWidth={contentWidth}
56      source={{html}}
57      tagsStyles={tagsStyles}
58    />
59  );
60});
61const tagsStyles = {
62  a: {
63    textDecorationLine: 'none',
64  },
65};
66
67function WebDisplay({html}) {
68  const {width: contentWidth} = useWindowDimensions();
69  return (
70    <RenderHtml
71      contentWidth={contentWidth}
72      source={{html}}
73      tagsStyles={tagsStyles}
74    />
75  );
76};
77function WebDisplay({html, anchorColor}) {
78  const {width: contentWidth} = useWindowDimensions();
79  const tagsStyles = React.useMemo(
80    () => ({
81      a: {
82        color: anchorColor,
83        textDecorationLine: 'none',
84      },
85    }),
86    [anchorColor],
87  );
88  return (
89    <RenderHtml
90      contentWidth={contentWidth}
91      source={{html}}
92      tagsStyles={tagsStyles}
93    />
94  );
95}
96

1Alzheimer’s Disease Assessment Scale-Cognitive  = ADAS_CogT0
2Rey-Osterrieth Complex Figure Test immediate recall = ROCF_IT0
3Copy of Rey-Osterrieth Complex Figure test   = ROCF_CT0
4Rey-Osterrieth Complex Figure Test delayed recall   = ROCf_RT0
5Phonemic verbal fluency = PVF_T0
6Semantic verbal fluency = SVF_T0
7library(tidyverse)
8library(readxl)
9library(rms)
10library(lme4)
11library(flextable)
12library(gtsummary)
13library(data.table)
14library(plotrix)
15library(purrr)
16
17out <db %>% 
18  mutate(GROUP = fct_recode(GROUP, CONTROL = "CONTROLLO")) %>%
19  to_long(keys  = c("tests0", "tests7"),
20          values =c("score0", "score7"),
21          grep("T0$", names(.), value = TRUE), 
22          grep("T7$", names(.), value = TRUE)) %>%
23  as_tibble %>%
24  mutate(Gender = factor(Gender, levels = c(1, 0), labels = c("M", "F")),
25         across(c(tests0, tests7), factor))
26  
27out1 <- out %>%   
28  group_by(tests0, GROUP) %>%
29  filter(GROUP == 'TRAINING') %>% 
30  summarise(mean0 = mean(score0, na.rm = T),
31            stderr0 = std.error(score0, na.rm = T), 
32            mean7 = mean(score7, na.rm = T), 
33            stederr7 = std.error(score7, na.rm = T),
34            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
35            p.value = t.test(score0, score7, paired = T)$p.value  
36  ) %>% 
37  ungroup()
38
39out2 <-  out %>% 
40  group_by(tests0, GROUP) %>% 
41  filter(GROUP == 'CONTROL') %>% 
42  summarise(mean0 = mean(score0, na.rm = T),
43            stderr0 = std.error(score0, na.rm = T), 
44            mean7 = mean(score7, na.rm = T), 
45            stederr7 = std.error(score7, na.rm = T),
46            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
47            p.value = t.test(score0, score7, paired = T)$p.value  
48  ) %>% 
49  ungroup()
50  
51out3 <- out1 %>%
52  bind_cols(out2) %>% 
53  select(., -'tests0...9') %>%
54  flextable()
55TRAINING and CONTROL groups 
56
57WITHIN AND BETWEEN GROUPS
58dput(head(db, 100))
59structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
6013, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 
6129, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 
6245, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 
6361, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 
6477, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 
6593, 94, 95, 96, 97, 98, 99, 100), GROUP = c("TRAINING", "TRAINING", 
66"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
67"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
68"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
69"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
70"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
71"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
72"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
73"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", 
74"TRAINING", "TRAINING", "TRAINING", "TRAINING", "TRAINING", "CONTROLLO", 
75"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
76"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
77"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
78"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
79"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
80"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
81"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
82"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO", 
83"CONTROLLO", "CONTROLLO", "CONTROLLO", "CONTROLLO"), Gender = c(1, 
840, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0, 1, 1, 
850, 0, 0, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 0, 1, 1, 1, 
860, 0, 1, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 
871, 1, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, 
880, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 0), Age = c(74, 76, 
8981, 74, 69, 72, 75, 83, 78, 72, 82, 68, 72, 72, 73, 80, 69, 72, 
9070, 80, 75, 80, 78, 74, 82, 74, 80, 82, 78, 81, 66, 71, 70, 79, 
9178, 73, 72, 77, 77, 71, 83, 74, 70, 71, 77, 69, 67, 64, 79, 71, 
9277, 77, 73, 67, 68, 79, 81, 67, 84, 75, 80, 73, 68, 74, 77, 79, 
9379, 72, 73, 78, 76, 78, 77, 74, 78, 77, 77, 82, 77, 70, 77, 81, 
9479, 75, 74, 78, 69, 77, 73, 77, 70, 79, 70, 72, 77, 72, 71, 71, 
9573, 81), Education = c(18, 4, 8, 5, 8, 11, 5, 5, 4, 8, 8, 12, 
965, 18, 13, 5, 13, 13, 5, 5, 13, 5, 3, 8, 17, 5, 8, 5, 5, 8, 17, 
978, 18, 18, 13, 13, 13, 13, 15, 17, 8, 5, 5, 13, 8, 5, 11, 13, 
988, 8, 8, 5, 13, 8, 5, 17, 8, 12, 13, 5, 8, 8, 8, 5, 3, 8, 18, 
995, 8, 13, 8, 5, 17, 8, 5, 17, 5, 8, 11, 8, 8, 5, 12, 3, 8, 8, 
1008, 13, 5, 5, 8, 8, 13, 5, 5, 8, 13, 5, 8, 12), ADAS_CogT0 = c(14.66, 
10115.33, 17.33, 19, 7.66, 12.6, 18.67, 14.99, 17.99, 17.33, 13.66, 
10216.99, 10.66, 9.66, 14.99, 15.66, 13.33, 4.33, 14.33, 15.99, 
10316.33, 10.66, 14.66, 10.66, 19.33, 17.66, 15.99, 20.66, 20.6, 
10417, 10.33, 6.33, 6.66, 19.99, 13.33, 24.33, 12.33, 10.33, 12.33, 
1059.66, 10.99, 13.99, 23, 6.32, 11.32, 13.99, 14.66, 8.99, 14.33, 
1069.99, 7.33, 15.66, 14, 7.99, 23.32, 14.66, 9.99, 5.66, 6.99, 
10711.66, 10.33, 6.99, 19.32, NA, 10, 17.66, 13.66, 10.32, NA, NA, 
1088.66, 9, 6.99, 14.99, 9.66, 13.66, 15.32, 12, 14, 13.66, 11.99, 
10915.66, 16, 15, 16.99, 20, 11, 7.99, 8.33, 8.32, 14.99, 18.66, 
11010.33, 11.99, 9.32, 17, 14.33, 14.66, 16.6, 9.99), ROCF_CT0 = c(32, 
11113.5, 34.7, 26.1, 35.4, 24.6, 28.9, 34.1, 12.1, 28.1, 22.7, 31.6, 
11230.4, 34.4, 37.2, 38.1, 18.6, 26.2, 34.8, 37.1, 26.2, 35.1, 37.1, 
11336, 11.2, 17.4, 32.7, 28.4, 21.1, 26.7, 31.9, 33.4, 27.4, 32.7, 
11433.9, 37.2, 28.2, 34.2, 29.7, 30, 16.2, 37.4, 34.8, 36.6, 33, 
11530.8, 34.6, 35.1, 29.7, 36.8, 28, 34.4, 33.7, 28.9, 31.8, 31.7, 
11610.2, 27.1, 36.8, 22.4, 35.7, 28, 30.9, 35.4, 26.9, 29.7, 26.7, 
11728.8, 32, 37.9, 27, 18.6, 33, 38, 25.1, 31, 27.1, 26.7, 37, 30.4, 
11837, 30.1, 36.9, 24.4, 34, 6.7, 20.4, 30.2, 31.1, 33.4, 17.4, 
11923.7, 30.6, 28.3, 19.4, 33.4, 31.6, 39.8, 27, 33.9), ROCF_IT0 = c(3.7, 
1207.6, 14.9, 13.6, 23.4, 4, 8.1, 23.9, 10.1, 7, 10, 9.2, 15.6, 
12110.8, 6.6, 13.6, 4, 15.3, 7.4, 0, 8.6, 22.9, 11.6, 10.1, 6.8, 
12212.1, 12.9, 0, 10.6, 5.9, 14.8, 10.9, 10.6, 10.6, 7.5, 7.3, 4.1, 
1233.1, 7.6, 13.4, 10.9, 19, 3.4, 11.7, 14.6, 9.4, 16.7, 15.7, 14.5, 
12412.5, 15.1, 22.6, 9.1, 15.5, 11, 6.1, 11.5, 5.6, 17.2, 18.1, 
12523.5, 15.6, 7.7, 13.5, 14.5, 11.9, 10.3, 11.5, 12.6, 19, 11.9, 
12615.4, 17.7, 8.4, 13.4, 5.2, 11.1, 16, 10.9, 8, 22.6, 13.6, 12.2, 
1276.5, 13.4, 11.5, 8, 18.1, 10, 11, 5.9, 14.9, 12, 11.4, 17, 5.9, 
12815, 10.9, 8.9, 15.7), ROCF_RT0 = c(3.9, 7.8, 16.3, 13.4, 24.1, 
1294.7, 7.8, 20.9, 9.8, 5.1, 9.8, 8.2, 16.3, 11.3, 5.9, 14.8, 4.7, 
13014.4, 6.7, 0, 4.9, 23.3, 14.8, 11.7, 6.4, 10.3, 14.3, 0, 10.8, 
1316.3, 15.4, 10.1, 5.3, 12, 6.9, 5.4, 4.4, 4.4, 7, 10.9, 11.9, 
13217.3, 3.7, 12.2, 6.7, 14.7, 16.2, 7.8, 10.8, 10.2, 16.7, 22.8, 
1339.1, 15.7, 10.7, 4, 8.8, 6.8, 14.6, 14.5, 24.8, 13.7, 0, 10.3, 
13417.3, 11.3, 8, 13.2, 15.2, 18.4, 4.3, 12.8, 18.9, 4.3, 11.8, 
1354.9, 11.8, 13.3, 10.3, 7.1, 23.7, 15.8, 16.4, 6.8, 20.3, 11.3, 
1367.6, 19.9, 10.8, 12.5, 5.6, 11.3, 11.7, 13.5, 12.3, 5.1, 14.2, 
1378.7, 12.3, 16.4), PVF_T0 = c(41.3, 35.4, 16.7, 28.4, 46.9, 33.6, 
13839.4, 27.4, 17.4, 37.9, 31.7, 37.6, 33.4, 21.5, 49.5, 40.4, 10.6, 
13940.5, 30.6, 28.4, 29.5, 22.4, 45.4, 29.7, 17.3, 39.4, 31.7, 24.4, 
14038.4, 22.7, 6.8, 19.9, 56.5, 35.3, 43.5, 32.5, 28.5, 38.5, 22.3, 
14132.3, 30.7, 23.4, 18.6, 46.6, 36.7, 35.6, 38.6, 16.9, 38.7, 37.6, 
14239.7, 34.4, 27.5, 16.2, 32.6, 48.3, 26.7, 49.9, 40.5, 34.4, 37.7, 
14339.7, 23.2, 19.4, 21.4, 33.7, 11.3, 41.6, 23.7, 31.5, 28.7, 34.4, 
14433.3, 27.7, 23.4, 32.3, 49.4, 35.7, 26.7, 31.9, 37.7, 35.4, 20.5, 
14528.4, 14.7, 30.7, 35.9, 16.5, 27.4, 49.4, 37.9, 27.7, 41.6, 31.6, 
14655.4, 42.9, 21.6, 25.6, 26.7, 43.5), SVF_T0 = c(40, 37, 30, 28, 
14749, 27, 34, 35, 29, 33, 20, 18, 36, 47, 43, 39, 36, 38, 48, 31, 
14825, 34, 31, 33, 24, 30, 45, 20, 33, 34, 29, 42, 40, 37, 37, 25, 
14928, 42, 19, 32, 37, 35, 20, 43, 33, 40, 40, 42, 27, 35, 29, 39, 
15031, 48, 41, 27, 32, 45, 39, 40, 36, 38, 27, 32, 47, 36, 24, 40, 
15138, 29, 43, 47, 38, 33, 44, 39, 46, 36, 32, 38, 38, 35, 26, 43, 
15229, 24, 33, 30, 33, 57, 40, 37, 44, 34, 46, 41, 33, 35, 29, 37
153), ADAS_CogT7 = c(16, 9.32, 21.33, 17, 8.32, 11, 14.99, 10.99, 
15417, 18.33, 13.32, 14.34, 8.99, 7, 11.99, 15.33, 6.99, 5.33, 12.32, 
15513, 21.32, 7.99, 13.33, 11.99, 17.32, 16.32, 16.33, 14.66, 18.99, 
15617.33, 7.99, 9.33, 10.99, NA, 12.99, 16.33, 21.66, 9, 9.34, 8.66, 
1578.33, 13.66, 15.66, 6.66, 10.99, 13.33, 13.33, 7.99, 11.99, 11.32, 
1587.33, 9.66, 6.99, NA, 15.99, 15.66, 14.66, 6.32, 7, 11, 14, 10.33, 
15924.66, NA, 14.99, NA, 15.99, 9.32, NA, NA, 9.99, 9.33, 7.66, 
16017.33, 10.32, 16, 17, 12.99, 15, 14.33, 10, 14.99, 19, 13.99, 
16119.33, NA, 10, 6.99, 11.66, 6.66, 14.33, 16, 8.66, 10, NA, 20, 
16214.99, 19.66, 26.66, 8.99), ROCF_CT7 = c(33, 23.9, 28.7, 39.1, 
16336.4, 29.2, 11.4, 38.1, 18.1, 28.4, 22.2, 18.6, 37, 35, 33.2, 
16423.6, 21.1, 30.2, 28.8, 37.1, 32.2, 33.1, 26.1, 38, 23.6, 21.4, 
16537.3, 36.4, 15.1, 35.7, 29.9, 31.4, 32.4, NA, 37.9, 28.2, 37.2, 
16631.9, 36.7, 24.5, 19.2, 23.4, 32.8, 36.2, 31.7, 24.3, 31.6, 33.1, 
16719.2, 30.4, 32.7, 24.4, 36.2, NA, 28.1, 31.7, 19.7, 31.6, 28.3, 
16827.4, 37.7, 35, 33.4, 34.4, 32.1, NA, 33.7, 31.8, NA, 34.9, 15, 
16920.1, 31, 35, 37.1, 29, 32.6, 31.7, 28.4, 34.4, 35.7, 35.1, 35.9, 
17027.4, 34, NA, 17.4, 28.9, 35.4, 33.4, 30.4, 14.7, 33.2, 26.8, 
171NA, 20, 33.6, 26.8, 36, 34.9), ROCF_IT7 = c(1.7, 12.1, 5.9, 12.1, 
17224.4, 9.8, 7.6, 24.4, 9.6, 7, 11.5, 9.7, 17.1, 14.2, 4.6, 12.1, 
1734.5, 18.8, 8.4, 14.6, 6.1, 17.1, 15.1, 13.1, 11.8, 12.1, 15.9, 
1747.6, 11.1, 5.9, 23.8, 12.4, 11.1, NA, 11, 7.8, 5.6, 6.5, 10.1, 
1759.9, 14.4, 17, 6.4, 27.3, 16, 14.4, 19.7, 16.2, 8.5, 8, 20.5, 
17615.1, 15.6, NA, 9, 8.6, 8.5, 13.2, 17.7, 14.1, 11, 9.1, 7, 16, 
17720.4, NA, 12.3, 16, NA, 15, 8.4, 17.9, 19.7, 11.9, 18.6, 4.7, 
17812.1, 17.5, 9, 5.5, 17.4, 12.1, 9.7, 9.5, 13.4, NA, 7.5, 14.5, 
17911, 19.5, 4.9, 11.9, 26, 12, NA, 3.9, 5.5, 6.4, 4.9, 16.2), ROCF_RT7 = c(2.4, 
1809.3, 6.3, 12.4, 22.6, 8.9, 7.8, 21.9, 9.8, 5.1, 10.8, 4.2, 12.3, 
18118.9, 2.9, 13.8, 1.2, 19.9, 4.2, 9.8, 5.9, 21.3, 12.3, 10.2, 
18212.1, 7.8, 6.3, 7.8, 9.8, 6.3, 16.9, 11.6, 11.3, NA, 8.9, 16.9, 
1833.9, 6.4, 13, 9.9, 14.9, 18.4, 3.7, 15.4, 8.8, 14.2, 14.7, 14.3, 
1848.8, 5.1, 21.8, 14.3, 11.4, NA, 8.7, 9.5, 8.8, 14.2, 17, 13.8, 
18512.8, 9.7, 5.1, 15.3, 22.8, NA, 6.4, 12.7, NA, 20.9, 7.8, 11.8, 
18622.4, 4.3, 17.8, 4.9, 13.8, 16.4, 7.4, 5.6, 24.8, 11.8, 9.4, 
18711.8, 20.3, NA, 7.1, 13.9, 12.3, 23.5, 2.6, 14.8, 15.2, 11.7, 
188NA, 4.3, 6.7, 5.2, 7.3, 18.4), PVF_T7 = c(38.3, 49.4, 16.7, 31.4, 
18960.9, 29.5, 48.4, 28.4, 17.4, 32.9, 40.7, 42.6, 42.4, 32.3, 38.5, 
19040.4, 20.4, 40.5, 42.6, 28.4, 27.5, 24.4, 40.4, 28.7, 5.3, 38.4, 
19146.7, 26.4, 37.4, 23.7, 9.8, 34.9, 55.5, NA, 38.5, 38.5, 25.5, 
19243.5, 36.3, 30.3, 32.7, 15.4, 21.6, 40.5, 38.7, 48.6, 47.6, 26.9, 
19335.7, 31.9, 47.7, 35.4, 29.5, NA, 33.6, 48.3, 27.7, 55.6, 39.5, 
19430.4, 36.7, 38.7, 23.9, 26.4, 28.4, NA, 18.3, 47.6, NA, 35.5, 
19529.7, 39.4, 44.3, 26.7, 29.4, 31.3, 44.4, 30.7, 11.5, 40.9, 37.7, 
19640.4, 14.5, 21.4, 14.7, NA, 29.9, 20.5, 34.4, 55.4, 32.9, 20.7, 
19727.6, 31.6, NA, 7.7, 25.6, 22.6, 22.7, 34.5), SVF_T7 = c(26, 
19848, 30, 29, 43, 25, 42, 36, 31, 27, 22, 21, 40, 46, 32, 29, 32, 
19938, 46, 31, 20, 36, 35, 32, 12, 28, 47, 20, 33, 30, 22, 44, 35, 
200NA, 36, 21, 24, 45, 25, 25, 30, 27, 34, 50, 31, 42, 34, 40, 25, 
20131, 41, 37, 38, NA, 38, 19, 29, 46, 40, 38, 36, 34, 31, 40, 39, 
202NA, 19, 40, NA, 29, 44, 43, 43, 31, 45, 28, 35, 31, 33, 36, 28, 
20335, 34, 34, 29, NA, 26, 30, 34, 57, 40, 36, 41, 36, NA, 22, 31, 
20429, 26, 31)), row.names = c(NA, -100L), class = c("tbl_df", "tbl", 
205"data.frame"))
206> 
207library(palmerpenguins)
208library(tidyverse)
209library(flextable)
210by <- c("species", "island")
211
212tabl <- palmerpenguins::penguins %>% 
213  group_by(across(all_of(by))) %>% 
214  summarise(
215    across(ends_with("mm"),
216           list(
217             mean = ~ mean(.x, na.rm = TRUE),
218             median = ~ median(.x, na.rm = TRUE),
219             sd = ~ sd(.x, na.rm = TRUE)
220           )
221           ),
222    .groups = "drop"
223  )
224
225tab_header <- tibble( key = colnames(tabl)) %>% 
226  separate(col = key, into = c("part", "measure", "unit", "stat"), 
227           fill = "right", remove = FALSE) %>% 
228  select(-unit) %>% 
229  mutate(
230    measure = if_else(is.na(measure), part, measure),
231    stat = if_else(is.na(stat), measure, stat)
232  )
233tab_header
234
235flextable(tabl) %>% 
236  flextable::set_header_df(tab_header, key = "key") %>% 
237  merge_h(part = "header") %>% 
238  merge_at(part = "header", i = 1:3, j = 1) %>%
239  merge_at(part = "header", i = 1:3, j = 2) %>%
240  theme_box() %>%
241  add_header_row(values = c("", "bada", "boum"), colwidths = c(1, 5, 5)) %>%
242  add_header_lines(c("another way to add a row of text")) %>% 
243  align(part = "header", i = seq_len(nrow_part(., part = "header")-1), align = "center")
244
245
246

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = "df4_full"
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm => Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country => Grouping(),
18             :country_year => Grouping(),
19             :id => Grouping(),
20             :sex => DummyCoding(base="Female"),
21             :education => DummyCoding(base="Tertiary"))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education => unique(df1))
25effects(design, m1)
26MethodError: no method matching effects!(::Dict{Symbol, DataFrame}, ::LinearMixedModel{Float64})
27    Closest candidates are:
28      effects!(::DataFrame, ::StatsBase.RegressionModel; eff_col, err_col, typical) at /Users/jmoawad/.julia/packages/Effects/B3CEy/src/regressionmodel.jl:40
29    
30    Stacktrace:
31     [1] top-level scope
32       @ In[28]:1
33     [2] eval
34       @ ./boot.jl:360 [inlined]
35     [3] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
36       @ Base ./loading.jl:1116
37filter!(!ismissing, df1.education)
38

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = "df4_full"
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm => Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country => Grouping(),
18             :country_year => Grouping(),
19             :id => Grouping(),
20             :sex => DummyCoding(base="Female"),
21             :education => DummyCoding(base="Tertiary"))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education => unique(df1))
25effects(design, m1)
26MethodError: no method matching effects!(::Dict{Symbol, DataFrame}, ::LinearMixedModel{Float64})
27    Closest candidates are:
28      effects!(::DataFrame, ::StatsBase.RegressionModel; eff_col, err_col, typical) at /Users/jmoawad/.julia/packages/Effects/B3CEy/src/regressionmodel.jl:40
29    
30    Stacktrace:
31     [1] top-level scope
32       @ In[28]:1
33     [2] eval
34       @ ./boot.jl:360 [inlined]
35     [3] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
36       @ Base ./loading.jl:1116
37filter!(!ismissing, df1.education)
38design = Dict(:education => ["Tertiary","Below upper-secondary"],:unemploy => [5,10,15,20,25])
39

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = "df4_full"
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm => Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country => Grouping(),
18             :country_year => Grouping(),
19             :id => Grouping(),
20             :sex => DummyCoding(base="Female"),
21             :education => DummyCoding(base="Tertiary"))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education => unique(df1))
25effects(design, m1)
26MethodError: no method matching effects!(::Dict{Symbol, DataFrame}, ::LinearMixedModel{Float64})
27    Closest candidates are:
28      effects!(::DataFrame, ::StatsBase.RegressionModel; eff_col, err_col, typical) at /Users/jmoawad/.julia/packages/Effects/B3CEy/src/regressionmodel.jl:40
29    
30    Stacktrace:
31     [1] top-level scope
32       @ In[28]:1
33     [2] eval
34       @ ./boot.jl:360 [inlined]
35     [3] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
36       @ Base ./loading.jl:1116
37filter!(!ismissing, df1.education)
38design = Dict(:education => ["Tertiary","Below upper-secondary"],:unemploy => [5,10,15,20,25])
39effects(design, m1)
40