library(readr)
#library(data.table)
library(Cairo)
library(ggplot2)
library(stringi)
library(stringr)
library(datetime)
library(dplyr)
library(ggthemes)
library(RMySQL)

plotfunction<- function(dfData,independentVariableName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionVector仪器序列号<- function(ik,independentVariableList,dfData,independentVariableName,dependentVariableNames,titleNames) {
  dfData<-dfData[(dfData$仪器序列号 %in% c(independentVariableList[independentVariableList[,2]==ik,][,1])),]
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionVector批次名称<- function(ik,independentVariableList,dfData,independentVariableName,dependentVariableNames,titleNames) {
  dfData<-dfData[(dfData$批次名称 %in% c(independentVariableList[independentVariableList[,2]==ik,][,1])),]
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionVector批次名称Log<- function(ik,independentVariableList,dfData,independentVariableName,dependentVariableNames,titleNames) {
  dfData<-dfData[(dfData$批次名称 %in% c(independentVariableList[independentVariableList[,2]==ik,][,1])),]
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionVector项目名称<- function(ik,independentVariableList,dfData,independentVariableName,dependentVariableNames,titleNames) {
  dfData<-dfData[(dfData$项目名称 %in% c(independentVariableList[independentVariableList[,2]==ik,][,1])),]
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionVector项目名称Log<- function(ik,independentVariableList,dfData,independentVariableName,dependentVariableNames,titleNames) {
  dfData<-dfData[(dfData$项目名称 %in% c(independentVariableList[independentVariableList[,2]==ik,][,1])),]
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionLog<- function(dfData,independentVariableName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionVector仪器序列号Log<- function(ik,independentVariableList,dfData,independentVariableName,dependentVariableNames,titleNames) {
  dfData<-dfData[(dfData$仪器序列号 %in% c(independentVariableList[independentVariableList[,2]==ik,][,1])),]
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = dfData00[,2], y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

panelPlotByDayAndLocation<- function(dfData,choosenList,independentVariableName,panelName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,panelName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,panelName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  dfData00<-dfData00[dfData00$省编号 %in% choosenList,]
  dfData00$省编号<-factor(dfData00$省编号,levels = choosenList)
  
  plot1<-ggplot(dfData00, aes(x = testDay, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    facet_wrap( ~ 省编号,ncol=2) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      panel.border = element_rect(
        color = "black",   # 边框颜色
        size = 1.,        # 边框粗细
        linetype = "solid", # 线型（solid/dashed/dotted）
        fill = NA          # 填充色（NA为透明）
      ),
      legend.position = "right")
  plot1
}

panelPlotByMonthAndLocation<- function(dfData,choosenList,independentVariableName,panelName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,panelName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,panelName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  dfData00<-dfData00[dfData00$省编号 %in% choosenList,]
  dfData00$省编号<-factor(dfData00$省编号,levels = choosenList)
  
  plot1<-ggplot(dfData00, aes(x = testMonth, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    facet_wrap( ~ 省编号,ncol=2) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      panel.border = element_rect(
        color = "black",   # 边框颜色
        size = 1.,        # 边框粗细
        linetype = "solid", # 线型（solid/dashed/dotted）
        fill = NA          # 填充色（NA为透明）
      ),
      legend.position = "right")
  plot1
}

panelPlotByDayAnd细胞<- function(dfData,choosenList,independentVariableName,panelName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,panelName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,panelName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  dfData00<-dfData00[dfData00$小细胞 %in% choosenList,]
  dfData00$小细胞<-factor(dfData00$小细胞,levels = choosenList)
  
  plot1<-ggplot(dfData00, aes(x = testDay, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    facet_wrap( ~ 小细胞,ncol=2) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      panel.border = element_rect(
        color = "black",   # 边框颜色
        size = 1.,        # 边框粗细
        linetype = "solid", # 线型（solid/dashed/dotted）
        fill = NA          # 填充色（NA为透明）
      ),
      legend.position = "right")
  plot1
}

panelPlotByMonthAnd细胞<- function(dfData,choosenList,independentVariableName,panelName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,panelName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,panelName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  dfData00<-dfData00[dfData00$小细胞 %in% choosenList,]
  dfData00$小细胞<-factor(dfData00$小细胞,levels = choosenList)
  
  plot1<-ggplot(dfData00, aes(x = testMonth, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    facet_wrap( ~ 小细胞,ncol=2) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      panel.border = element_rect(
        color = "black",   # 边框颜色
        size = 1.,        # 边框粗细
        linetype = "solid", # 线型（solid/dashed/dotted）
        fill = NA          # 填充色（NA为透明）
      ),
      legend.position = "right")
  plot1
}

pointPlotfunction批次<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(批次名称)))+
     # geom_line(aes(testDay,浓度1移动均值),color="blue")+
     # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
     # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction批次Log<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(批次名称)))+
     # geom_line(aes(testDay,浓度1移动均值),color="blue")+
     # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
     # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction仪器<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(仪器序列号)))+
    # geom_line(aes(testDay,浓度1移动均值),color="blue")+
    # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
    # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction仪器Log<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(仪器序列号)))+
    # geom_line(aes(testDay,浓度1移动均值),color="blue")+
    # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
    # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction样本<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(样本类型)))+
    # geom_line(aes(testDay,浓度1移动均值),color="blue")+
    # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
    # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction样本Log<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(样本类型)))+
    # geom_line(aes(testDay,浓度1移动均值),color="blue")+
    # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
    # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction项目<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(项目名称)))+
    # geom_line(aes(testDay,浓度1移动均值),color="blue")+
    # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
    # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

pointPlotfunction项目Log<- function(dfData,independentVariableName,dependentVariableName,titleNames) {
  
  plot2 <- ggplot(df20Ploted)+geom_point(aes(dfData[,independentVariableName], dfData[,dependentVariableName],color=as.character(项目名称)))+
    # geom_line(aes(testDay,浓度1移动均值),color="blue")+
    # geom_line(aes(testDay,浓度1累计均值),color="green",linewidth=1.)+
    # geom_line(aes(testDay,浓度1允许波动范围),color="red",linewidth=1.)+
    #  xlim(xmin, xmax) + ylim(ymin, ymax)+theme(legend.position ="right")+
    labs(
      title = titleNames[1],      # 主标题
      x = titleNames[2],        # X轴标签
      y = titleNames[3],              # Y轴标签
      color = titleNames[4]                  # 图例标题
    )+
    # scale_x_date(
    #   date_labels = "%Y-%m-%d",  # 格式符组合
    #   date_breaks = "1 month"         # 标签间隔（如 "2 weeks"）
    # )+
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 10,face = "bold"),
      axis.title.y = element_text(size = 10,face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90),
      legend.position = "right")
  
  plot2
  
}

plotfunctionByDay<- function(dfData,independentVariableName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = testDay, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionBy区域<- function(dfData,independentVariableName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = 省编号, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionBy仪器<- function(dfData,independentVariableName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = 仪器序列号, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

plotfunctionBy仪器Log<- function(dfData,independentVariableName,dependentVariableNames,titleNames) {
  lth1<-length(dependentVariableNames)
  dfData00<-data.frame()
  for(i in 1:lth1){
    dfData0<-cbind(dependentVariableNames[i],dfData[,c(independentVariableName,dependentVariableNames[i])])
    colnames(dfData0)<-c("Group",c(independentVariableName,"Count"))
    dfData00<-rbind(dfData00,dfData0)
  }
  
  plot1<-ggplot(dfData00, aes(x = 仪器序列号, y = Count, fill = Group)) +
    geom_col(
      position = position_dodge(width = 0.4),  # 控制条间距
      width = 0.7  # 条宽度
    ) +
    geom_line(
      aes(group = Group, color = Group),
      position = position_dodge(width = 0.4),
      size = 1,
      linetype="dashed",
      show.legend=FALSE,
      alpha = 1.0
    ) +  
    scale_color_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    geom_text(
      aes(label = round(Count,2)),         # 标签内容
      position = position_dodge(width = 0.4),  # 与柱子位置一致
      vjust = -0.4,               # 垂直位置（负值向上）
      color = "black",            # 标签颜色
      size = 1.2                    # 字体大小
    ) +
    labs(
      title = titleNames[1],
      x = titleNames[2],
      y = titleNames[3],
      fill = titleNames[4]
    ) +
    scale_fill_manual(values = c("red", "blue","green","yellow")) +  # 自定义颜色
    scale_y_log10()+
    theme_minimal() + theme(
      plot.title = element_text(size = 14, face = "bold", hjust = 0.5),
      axis.title.x = element_text(size = 12, face = "bold"),
      axis.title.y = element_text(size = 12, face = "bold"),
      axis.text.x = element_text(size = 8, face = "bold",angle=90) ,
      legend.position = "right")
  
  plot1
  #return (list(plotdfDataNumber,b23,b33,b43,b53))
  
}

running_avg <- function(x, window = 3) {
  sapply(seq_along(x), function(i) {
    start <- max(1, i - window + 1)
    mean(x[start:i],na.rm=TRUE)
  })
}

lumping_avg<- function(x,window = 100) {
  sapply(seq_along(x), function(i) {
    start <- max(1, i - window + 1)
    mean(x[start:i],na.rm=TRUE)
  })
}

lumping_sd<- function(x,window = 100) {
  sapply(seq_along(x), function(i) {
    start <- max(1, i - window + 1)
    sd(x[start:i],na.rm=TRUE)
  })
}

rep6<-rep(1,6)
rep50<-rep(1,50)
rep0<-rep6
for(i in 1:1000) rep0<-c(rep0,rep6+i)
rep00<-rep50
for(i in 1:100) rep00<-c(rep00,rep50+i)

if(FALSE){
  csv_files <- list.files(pattern = "\\UTF.csv$")
  
  df00<-data.frame()
  
  for(i in 1:length(csv_files)) {
    #guess_encoding("historyRecord20.csv") # [[1]]$encoding 
    #df2 <- fread("historyRecord20250604175205UTF.csv",encoding = "UTF-8",fill = TRUE)
    # df22 <- read.csv(csv_files[i],encoding = "UTF-8",fill = TRUE)
    # colnames(df22)
    # summary(df22)
    df2 <- read.csv(csv_files[i],encoding = "UTF-8",fill = TRUE)
     df2$批次名称<-as.character(df2$批次名称)
    df2<-df2[!is.na(df2$批次名称),]
    
    df2$是否阳性<-df2$结论
    df2$是否阳性[df2$是否阳性=="阳性"]<-1.0
    df2$是否阳性[df2$是否阳性=="阴性"]<-0.0
    df2$是否阳性[df2$是否阳性=="无效"]<-0.5
    df2$是否阳性<-as.numeric(df2$是否阳性)
    df2$是否阳性[is.na(df2$是否阳性)]<-0.5
    
    df2$结论[df2$结论=="阳性"]<-1.0
    df2$结论[df2$结论=="阴性"]<-0.0
    df2$结论<-as.numeric(df2$结论)
    
    df2$是否有效<-1
    df2$是否有效[df2$是否阳性==0.5]<-0
    
    df00<-rbind(df00,df2)
  }
  write_excel_csv(df00,file="history06092025.csv")
  
  df2<-df00
  
} 
if(FALSE){
series<-read.csv("荧光层析设备1.csv",encoding = "UTF-8",fill = TRUE)
series1<-read.csv("FIC-Q100N.csv",encoding = "UTF-8",fill = TRUE)
series0<-series$仪器序列号[series$仪器序列号 %in% 仪器号List$仪器序列号]
series00<-series$仪器编号[series$仪器编号 %in% series1$仪器编号]
colnames(series[,1:13])
colnames(series1[,3:11])
仪器编号和序列号<-merge(series[,1:13],series1[,3:11],by="仪器编号",all=FALSE)
#仪器编号和序列号all<-merge(series[,1:13],series1[,3:11],by="仪器编号",all=TRUE)
colnames(仪器编号和序列号)
仪器编号和序列号$SIM卡号<- gsub("\t", " ", 仪器编号和序列号$SIM卡号)
仪器编号和序列号$返利表序号<- gsub("\n", "_", 仪器编号和序列号$返利表序号)
#仪器编号和序列号all$SIM卡号<- gsub("\t", " ", 仪器编号和序列号all$SIM卡号)
#仪器编号和序列号all$返利表序号<- gsub("\n", "_", 仪器编号和序列号all$返利表序号)
#仪器编号和序列号[仪器编号和序列号$仪器序列号=="781be43bb68f05bb",]

cname0<-c("仪器编号"  ,       "仪器序列号"  ,    
 "SIM卡号"  ,        "发货时间"   ,      "仪器类型"   ,     
 "总测试量"   ,      "最后一次开机时间" ,"时间差"   ,       
"开机地点"  ,       "网络类型" ,        "ip地址"    ,      
 "用户"  ,           "用户CRM"   ,       "返利表序号"  ,    
 "申请日期"  ,       "区域"     ,        "客户编码"  ,      
 "代理商名称" ,      "用户名称"   ,      "规格" ,           
 "状态")
#write_excel_csv(仪器编号和序列号all[,cname0],file="仪器编号和序列号all.csv")
write_excel_csv(仪器编号和序列号[,cname0],file="仪器编号和序列号.csv")
}
if(FALSE){
  series<-read.csv("荧光层析设备1.csv",encoding = "UTF-8",fill = TRUE)
  series1<-read.csv("FIC-Q100N20250710.csv",encoding = "UTF-8",fill = TRUE)
  series0<-series$仪器序列号[series$仪器序列号 %in% 仪器号List$仪器序列号]
  series00<-series$仪器编号[series$仪器编号 %in% series1$仪器编号]
  colnames(series)[1:13]
  colnames(series1)[3:11]
  仪器编号和序列号0<-merge(series[,1:13],series1[,3:11],by="仪器编号",all=FALSE)
  仪器编号和序列号1<-merge(series[,1:13],series1[,3:11],by="仪器编号",all=TRUE)
  仪器编号和序列号2<-merge(series[,1:13],series1[,3:11],by="仪器编号",all.y=TRUE,all.x=FALSE)
  unique编号1<-unique(仪器编号和序列号1$仪器编号)
  unique序列号1<-unique(仪器编号和序列号1$仪器序列号)
  unique编号2<-unique(仪器编号和序列号2$仪器编号)
  unique序列号2<-unique(仪器编号和序列号2$仪器序列号)
  #仪器编号和序列号all<-merge(series[,1:13],series1[,3:11],by="仪器编号",all=TRUE)
  colnames(仪器编号和序列号1)
  仪器编号和序列号0$SIM卡号<- gsub("\t", " ", 仪器编号和序列号0$SIM卡号)
  仪器编号和序列号0$返利表序号<- gsub("\n", "_", 仪器编号和序列号0$返利表序号)
  仪器编号和序列号1$SIM卡号<- gsub("\t", " ", 仪器编号和序列号1$SIM卡号)
  仪器编号和序列号1$返利表序号<- gsub("\n", "_", 仪器编号和序列号1$返利表序号)
  #仪器编号和序列号all$SIM卡号<- gsub("\t", " ", 仪器编号和序列号all$SIM卡号)
  #仪器编号和序列号all$返利表序号<- gsub("\n", "_", 仪器编号和序列号all$返利表序号)
  #仪器编号和序列号[仪器编号和序列号$仪器序列号=="781be43bb68f05bb",]
  仪器编号和序列号1e<-仪器编号和序列号1[complete.cases(仪器编号和序列号1$区域),]
  
    cname0<-c("仪器编号"  ,       "仪器序列号"  ,    
            "SIM卡号"  ,        "发货时间"   ,      "仪器类型"   ,     
            "总测试量"   ,      "最后一次开机时间" ,"时间差"   ,       
            "开机地点"  ,       "网络类型" ,        "ip地址"    ,      
            "用户"  ,           "用户CRM"   ,       "返利表序号"  ,    
            "申请日期"  ,       "区域"     ,        "客户编码"  ,      
            "代理商名称" ,      "用户名称"   ,      "规格" ,           
            "状态")
  #write_excel_csv(仪器编号和序列号all[,cname0],file="仪器编号和序列号all.csv")
  write_excel_csv(仪器编号和序列号1[,cname0],file="仪器编号和序列号1.csv")

  mydb = dbConnect(MySQL(), user='root', password='My9521$$', dbname='和迈', host='192.168.11.223')
  dbWriteTable(mydb, name='仪器编号和序列号1', value=仪器编号和序列号1)
  dbDisconnect(mydb)
  
  write_excel_csv(仪器编号和序列号1e[,cname0],file="仪器编号和序列号1e.csv")
  
  mydb = dbConnect(MySQL(), user='root', password='My9521$$', dbname='和迈', host='192.168.11.223')
  dbWriteTable(mydb, name='仪器编号和序列号1e', value=仪器编号和序列号1e)
  dbDisconnect(mydb)
  
  Numberof区域<-length(unique(仪器编号和序列号1e$区域))
  listOfUnique细胞<-""
  for(j in 1:Numberof区域) listOfUnique细胞<-paste(listOfUnique细胞,unique(仪器编号和序列号1e$区域)[j],sep="___")
  细胞<-data.frame(cbind(1:Numberof区域,unique(df2andInf$区域)))
  colnames(细胞)<-c("ID","细胞")
  
  write_excel_csv(细胞,file="细胞.csv")
  
  细胞1<-细胞[str_length(细胞$细胞)<=3,]
  
  similiar<-str_like(细胞$细胞,paste(paste("%",细胞1$细胞[3],sep=""),"%",sep=""), ignore_case = TRUE)
  细胞$细胞[30]
  细胞$细胞[43]
  细胞$细胞[30]<-细胞$细胞[43]
  similiar<-str_like(细胞$细胞,paste(paste("%",细胞1$细胞[4],sep=""),"%",sep=""), ignore_case = TRUE)
  细胞$细胞[9]
  细胞$细胞[45]
  细胞$细胞[45]<-细胞$细胞[9]
  similiar<-str_like(细胞$细胞,paste(paste("%",细胞1$细胞[5],sep=""),"%",sep=""), ignore_case = TRUE)
  细胞$细胞[17]
  细胞$细胞[46]
  细胞$细胞[46]<-细胞$细胞[17]
  similiar<-str_like(细胞$细胞,paste(paste("%",细胞1$细胞[6],sep=""),"%",sep=""), ignore_case = TRUE)
  细胞$细胞[11]
  细胞$细胞[47]
  细胞$细胞[47]<-细胞$细胞[11]
  similiar<-str_like(细胞$细胞,paste(paste("%",细胞1$细胞[7],sep=""),"%",sep=""), ignore_case = TRUE)
  细胞$细胞[14]
  细胞$细胞[54]
  细胞$细胞[54]<-细胞$细胞[14]
  similiar<-str_like(细胞$细胞,paste(paste("%",细胞1$细胞[8],sep=""),"%",sep=""), ignore_case = TRUE)
  细胞$细胞[63]
  细胞$细胞[34]
  细胞$细胞[63]<-细胞$细胞[34]
  
  unique(细胞$细胞)
  仪器编号和序列号1m<-仪器编号和序列号1e
  
  仪器编号和序列号1m$区域[ str_like(仪器编号和序列号1m$区域,paste(paste("%",细胞1$细胞[3],sep=""),"%",sep=""), ignore_case = TRUE)]<-细胞$细胞[43]
  仪器编号和序列号1m$区域[ str_like(仪器编号和序列号1m$区域,paste(paste("%",细胞1$细胞[4],sep=""),"%",sep=""), ignore_case = TRUE)]<-细胞$细胞[9]
  仪器编号和序列号1m$区域[ str_like(仪器编号和序列号1m$区域,paste(paste("%",细胞1$细胞[5],sep=""),"%",sep=""), ignore_case = TRUE)]<-细胞$细胞[17]
  仪器编号和序列号1m$区域[ str_like(仪器编号和序列号1m$区域,paste(paste("%",细胞1$细胞[6],sep=""),"%",sep=""), ignore_case = TRUE)]<-细胞$细胞[11]
  仪器编号和序列号1m$区域[ str_like(仪器编号和序列号1m$区域,paste(paste("%",细胞1$细胞[7],sep=""),"%",sep=""), ignore_case = TRUE)]<-细胞$细胞[14]
  仪器编号和序列号1m$区域[ str_like(仪器编号和序列号1m$区域,paste(paste("%",细胞1$细胞[8],sep=""),"%",sep=""), ignore_case = TRUE)]<-细胞$细胞[34]

  仪器编号和序列号$小细胞[str_like(仪器编号和序列号$小细胞,paste(paste("%","陈凯 华中",sep=""),"%",sep=""), ignore_case = TRUE)]<-"华中 陈凯"
  
  unique(仪器编号和序列号$小细胞)
  
  write_excel_csv(仪器编号和序列号1m[,cname0],file="仪器编号和序列号1m.csv")
  
  mydb = dbConnect(MySQL(), user='root', password='My9521$$', dbname='和迈', host='192.168.11.223')
  dbWriteTable(mydb, name='仪器编号和序列号1m', value=仪器编号和序列号[,2:22])
  dbDisconnect(mydb)

  Numberof区域<-length(unique(仪器编号和序列号1m$小细胞))
  listOfUnique细胞<-""
  for(j in 1:Numberof区域) listOfUnique细胞<-paste(listOfUnique细胞,unique(仪器编号和序列号1m$小细胞)[j],sep="___")
  细胞m<-data.frame(cbind(1:Numberof区域,unique(df2andInf$小细胞)))
  colnames(细胞m)<-c("ID","细胞")
  
  write_excel_csv(细胞m,file="细胞m.csv")
}

mydb = dbConnect(MySQL(), user='root', password='My9521$$', dbname='和迈', host='192.168.11.223')
dbListTables(mydb)
#dbListFields(mydb, '测试数据')

#仪器编号和序列号all<-read.csv("仪器编号和序列号all.csv",encoding = "UTF-8",fill = TRUE)

#仪器编号和序列号<-read.csv("仪器编号和序列号.csv",encoding = "UTF-8",fill = TRUE)
rs = dbSendQuery(mydb, "select * from  和迈.仪器编号和序列号1m ")
仪器编号和序列号 = fetch(rs, n=-1)
colnames(仪器编号和序列号)
#仪器编号和序列号<-distinct(仪器编号和序列号[,2:22])
#仪器编号和序列号<-read.csv("仪器编号和序列号.csv",encoding = "UTF-8",fill = TRUE)

rs = dbSendQuery(mydb, "select * from  和迈.和迈测试数据 ")
df2 = fetch(rs, n=-1)
dbDisconnect(mydb)

#df2 = fetch(rs, n=-1)
#df2 <- read.csv("history06092025.csv",encoding = "UTF-8",fill = TRUE)
colnames(df2)
cname00<-c( "序号" ,        "项目号"   ,    "批次号" ,      "样品编号" ,  
            "项目名称" ,    "批次名称" ,    "测试时间" ,    "浓度1"  ,     
             "结论"  ,       "C值"   ,       "T值"  ,        "浓度2" ,      
            "浓度3" ,       "结论2"  ,      "结论3"  ,      "样本类型"  ,  
            "省市编号"  ,   "仪器序列号" ,  "T2值"  ,       "T3值"  ,      
             "仪器备注名称", "仪器投放区域", "详细地址")
df2<-distinct(df2[,cname00])
df2$批次名称<-as.character(df2$批次名称)
df2<-df2[!is.na(df2$批次名称),]

df2andInf<-merge(df2,仪器编号和序列号[,c("仪器编号","仪器序列号","发货时间" ,"申请日期" ,       
                                 "小细胞"   ,          "客户编码"   ,      "代理商名称" ,     
                                  "用户名称" ,        "规格"   ,          "状态")],by="仪器序列号",all.x=TRUE,all.y=FALSE)
df2andInf<-distinct(df2andInf)

#df2andInf0<-df2andInf[str_c(df2andInf$仪器编号, df2andInf$仪器序列号)!=NA,]


df2andInf$是否阳性<-df2andInf$结论
df2andInf$是否阳性[df2andInf$是否阳性=="阳性"]<-1.0
df2andInf$是否阳性[df2andInf$是否阳性=="阴性"]<-0.0
df2andInf$是否阳性[df2andInf$是否阳性=="无效"]<-0.5
df2andInf$是否阳性<-as.numeric(df2andInf$是否阳性)
df2andInf$是否阳性[is.na(df2andInf$是否阳性)]<-0.5

df2andInf$结论[df2andInf$结论=="阳性"]<-1.0
df2andInf$结论[df2andInf$结论=="阴性"]<-0.0
df2andInf$结论<-as.numeric(df2andInf$结论)

df2andInf$是否有效<-1
df2andInf$是否有效[df2andInf$是否阳性==0.5]<-0
df2andInf$省编号<-(df2andInf$省市编号 %/% 10000)*10000
df2andInf$省名<-str_sub(df2andInf$详细地址,1,3)
df2andInf$省名[str_length(df2andInf$省名)<3]<-NA

Numberof项目号<-length(unique(df2andInf$项目号))
Numberof批次号<-length(unique(df2andInf$批次号))
Numberof样品编号<-length(unique(df2andInf$样品编号))
Numberof项目名称<-length(unique(df2andInf$项目名称))
Numberof批次名称<-length(unique(df2andInf$批次名称))
Numberof省编号<-length(unique(df2andInf$省编号))
Numberof省名<-length(unique(df2andInf$省名))
Numberof省市编号<-length(unique(df2andInf$省市编号))
Numberof仪器序列号<-length(unique(df2andInf$仪器序列号))
Numberof详细地址<-length(unique(df2andInf$详细地址))
Numberof小细胞<-length(unique(df2andInf$小细胞))
Numberof仪器备注名称<-length(unique(df2andInf$仪器备注名称))
Numberof仪器投放区域<-length(unique(df2andInf$仪器投放区域))
Numberof样本类型<-length(unique(df2andInf$样本类型))



省编号and省名<-cbind(df2andInf$省编号,df2andInf$省名)
省编号and省名<-省编号and省名[!duplicated(省编号and省名),]

summaryTable<-stri_join("Numberof测试: ",as.character(nrow(df2andInf)),
                        "\nNumberof项目名称: ", as.character(Numberof项目名称),
                        "\nNumberof批次号:  ",as.character(Numberof批次号), "  \n",
                        "Numberof样品编号：  ",as.character(Numberof样品编号),"  \n",
                        "Numberof批次名称:   ",as.character(Numberof批次名称),"  \n",
                        "Numberof省编号:   ",as.character(Numberof省编号),"  \n",
                        "Numberof省市编号:   ",as.character(Numberof省市编号),"  \n",
                        "Numberof仪器序列号:   ",as.character(Numberof仪器序列号),"  \n",
                        "Numberof详细地址:   ",as.character(Numberof详细地址),"  \n",
                        "Numberof小细胞:   ",as.character(Numberof小细胞),"  \n",
                        "Numberof样本类型:   ",as.character(Numberof样本类型),"  \n",sep="")
  小细胞<-unique(df2andInf$小细胞)
  Numberof小细胞<-length(小细胞)
  细胞<-data.frame(cbind(1:Numberof小细胞,小细胞))
  colnames(细胞)<-c("ID","细胞")
  细胞0<-细胞[!(str_like(细胞$细胞,paste(paste("%","内部",sep=""),"%",sep=""), ignore_case = TRUE) |
              str_like(细胞$细胞,paste(paste("%","客服",sep=""),"%",sep=""), ignore_case = TRUE) |
              str_like(细胞$细胞,paste(paste("%","医学",sep=""),"%",sep=""), ignore_case = TRUE)  ),]
  细胞0<-细胞0[!is.na(细胞0$细胞),]
  细胞0$ID<-1:nrow(细胞0)
  write_excel_csv(细胞0,file="细胞0.csv")

summary(df2andInf$结论)
summary(df2andInf$是否阳性)
summary(df2andInf$是否有效)

#df2andInf$仪器序列号_批次名称<-str_c(df2andInf$仪器序列号,'_',df2andInf$批次名称)


colnames(df2andInf)
#colnames(df00)
#df2andInf00<-df2andInf0
summary(df2andInf$仪器序列号)
summary(df2andInf$批次名称)


df20<-df2andInf #[,c(1:11,16,17,20)]
df20$省编号<-str_c("省编号: ",df20$省编号)
df20<-df20[complete.cases(df20$测试时间), ]
df20<-df20[str_length(df20$测试时间)>10, ]

colnames(df20)

df20$浓度1<-as.numeric(df20$浓度1)
df20$C值<-as.numeric(df20$C值)
df20$T值<-as.numeric(df20$T值)
df20$ToverC值<-df20$T值/df20$C值
df20$ToverC值[df20$是否有效==0]<-NA

summary(as.numeric(df20$结论))
summary(df20$浓度1)
summary(df20$ToverC值)

summary(df20$testTimeFromeBegining)
colnames(df20)


df20$testMonth<-str_sub(df20$测试时间,1,7)

#df20$testMonthinNumber<-as.numeric(str_c(str_sub(df20$测试时间,1,4),str_sub(df20$测试时间,6,7)))

df20$testDay<-as.Date(str_sub(df20$测试时间,1,10))
df20<-df20[df20$testDay<="2025-06-05", ]
#df20<-df20[df20$testDay<=Sys.Date(), ]

df20<-df20[complete.cases(df20$testDay), ]

summary(df20$testDay)

df20$testTime<-as.datetime(str_sub(df20$测试时间,1,19),format='%Y-%m-%d %H:%M:%S')

duration<-(max(df20$testTime,na.rm = TRUE)-min(df20$testTime,na.rm = TRUE))/(60.*60.*24.)

df20$testTimeFromeToday<-(max(df20$testTime,na.rm = TRUE)-df20$testTime)/(60.*60.*24.)+1.
#max0<-max(df20$testTime,na.rm=TRUE)
#min0<-min(df20$testTime,na.rm=TRUE)

#unique(dfBy样本$样本类型)
#max0-min0

#max(df20$testDay,na.rm=TRUE)
#min(df20$testDay,na.rm=TRUE)

#clean_df <- df20[complete.cases(df20[,c("小细胞")]), ]
#summary(clean_df)


summary(df20$testTimeFromeToday)
dayMax<-max(df20$testTimeFromeToday,na.rm = TRUE)


df2000<-df20
df20<-df20[df20$testTimeFromeToday<=730,]
#df20<-df20[complete.cases(df20$测试时间), ]
lastDay<-max(df20$testDay,na.rm = TRUE)
df20dayLast21<-df20[lastDay-df20$testDay<21,]


dfByDay <- df2000 %>%  group_by(testMonth,testDay) %>%  summarise(
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  小细胞数=length(unique(小细胞)),
  市数=length(unique(省市编号)),
  省数=length(unique(省编号))
)
dfByDay$阳性率<-dfByDay$阳性数/dfByDay$有效数
dfByDay$有效率<-dfByDay$有效数/dfByDay$测试数
dfByDay$阳性数<-as.integer(dfByDay$阳性数)
dfByDay$测试数<-as.integer(dfByDay$测试数)
dfByDay$有效数<-as.integer(dfByDay$有效数)
dfByDay$省数<-as.integer(dfByDay$省数)
dfByDay <- dfByDay[order(dfByDay$testDay),]
#dfByDay<-dfByDay[lastDay-dfByDay$testDay<=21,]

write_excel_csv(dfByDay,file="按日统计0.csv")

dfByMonth <- df2000 %>%  group_by(testMonth) %>%  summarise(
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  小细胞数=length(unique(小细胞)),
  市数=length(unique(省市编号)),
  省数=length(unique(省编号))
)
dfByMonth$阳性率<-dfByMonth$阳性数/dfByMonth$有效数
dfByMonth$有效率<-dfByMonth$有效数/dfByMonth$测试数
dfByMonth$阳性数<-as.integer(dfByMonth$阳性数)
dfByMonth$测试数<-as.integer(dfByMonth$测试数)
dfByMonth$有效数<-as.integer(dfByMonth$有效数)
dfByMonth$省数<-as.integer(dfByMonth$省数)
dfByMonth <- dfByMonth[order(dfByMonth$testMonth),]
dfByMonth <- dfByMonth[nrow(dfByMonth):1,]

write_excel_csv(dfByMonth,file="按月统计0.csv")

dfBy项目 <- df2000 %>%  group_by(项目名称) %>%  summarise(
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  样本数=length(unique(样本类型)),
  省数=length(unique(省编号)),
  市数=length(unique(省市编号)),
  小细胞数=length(unique(小细胞)),
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy项目$阳性率<-dfBy项目$阳性数/dfBy项目$有效数
dfBy项目$有效率<-dfBy项目$有效数/dfBy项目$测试数
dfBy项目$阳性数<-as.integer(dfBy项目$阳性数)
dfBy项目$测试数<-as.integer(dfBy项目$测试数)
dfBy项目$有效数<-as.integer(dfBy项目$有效数)
dfBy项目 <- dfBy项目[order(-dfBy项目$测试数),]
write_excel_csv(dfBy项目,file="按项目统计0.csv")

dfBy样本 <- df2000 %>%  group_by(样本类型) %>%  summarise(
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  项目数=length(unique(项目名称)),
  省数=length(unique(省编号)),
  市数=length(unique(省市编号)),
  小细胞数=length(unique(小细胞)),
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy样本$阳性率<-dfBy样本$阳性数/dfBy样本$有效数
dfBy样本$有效率<-dfBy样本$有效数/dfBy样本$测试数
dfBy样本$阳性数<-as.integer(dfBy样本$阳性数)
dfBy样本$测试数<-as.integer(dfBy样本$测试数)
dfBy样本$有效数<-as.integer(dfBy样本$有效数)
dfBy样本 <- dfBy样本[order(-dfBy样本$测试数),]
write_excel_csv(dfBy样本,file="按样本统计0.csv")

dfBy细胞 <- df2000 %>%  group_by(小细胞) %>%  summarise(
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  市数=length(unique(省市编号)),
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy细胞$阳性率<-dfBy细胞$阳性数/dfBy细胞$有效数
dfBy细胞$有效率<-dfBy细胞$有效数/dfBy细胞$测试数
dfBy细胞$阳性数<-as.integer(dfBy细胞$阳性数)
dfBy细胞$测试数<-as.integer(dfBy细胞$测试数)
dfBy细胞$有效数<-as.integer(dfBy细胞$有效数)
dfBy细胞 <- dfBy细胞[order(-dfBy细胞$测试数),]
write_excel_csv(dfBy细胞,file="按细胞统计0.csv")

dfBy区域 <- df2000 %>%  group_by(省编号) %>%  summarise(
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  市数=length(unique(省市编号)),
  小细胞数=length(unique(小细胞)),
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy区域$阳性率<-dfBy区域$阳性数/dfBy区域$有效数
dfBy区域$有效率<-dfBy区域$有效数/dfBy区域$测试数
dfBy区域$阳性数<-as.integer(dfBy区域$阳性数)
dfBy区域$测试数<-as.integer(dfBy区域$测试数)
dfBy区域$有效数<-as.integer(dfBy区域$有效数)
dfBy区域 <- dfBy区域[order(-dfBy区域$测试数),]
write_excel_csv(dfBy区域,file="按区域统计0.csv")

dfBy批次 <- df2000 %>%  group_by(批次名称) %>%  summarise(
  仪器数=length(unique(仪器序列号)),
  测试数 = n(),
  省数=length(unique(省编号)),
  市数=length(unique(省市编号)),
  小细胞数=length(unique(小细胞)),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  最早测试=min(testDay,na.rm = TRUE),
  最近测试=max(testDay,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy批次$阳性率<-dfBy批次$阳性数/dfBy批次$有效数
dfBy批次$有效率<-dfBy批次$有效数/dfBy批次$测试数
dfBy批次$阳性数<-as.integer(dfBy批次$阳性数)
dfBy批次$测试数<-as.integer(dfBy批次$测试数)
dfBy批次$有效数<-as.integer(dfBy批次$有效数)
dfBy批次$省数<-as.integer(dfBy批次$省数)
dfBy批次 <- dfBy批次[order(-dfBy批次$测试数),]
write_excel_csv(dfBy批次,file="按批次统计0.csv")

df20001<-cbind(df2000,IDIndex=1:nrow(df2000))
dfBy仪器 <- df20001 %>%  group_by(仪器序列号) %>%  summarise(
  批次数=length(unique(批次名称)),
  测试数 = n(),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  earliestIndex=min(IDIndex),
  medianIndex=median(IDIndex),
  latestIndex=max(IDIndex),
  最早测试=min(testDay,na.rm = TRUE),
  最近测试=max(testDay,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy仪器$阳性率<-dfBy仪器$阳性数/dfBy仪器$有效数
dfBy仪器$有效率<-dfBy仪器$有效数/dfBy仪器$测试数
dfBy仪器$阳性数<-as.integer(dfBy仪器$阳性数)
dfBy仪器$测试数<-as.integer(dfBy仪器$测试数)
dfBy仪器$有效数<-as.integer(dfBy仪器$有效数)

dfBy仪器$最早测试地点<-df2000$详细地址[dfBy仪器$earliestIndex]
dfBy仪器$中期测试地点<-df2000$详细地址[dfBy仪器$medianIndex]
dfBy仪器$最后测试地点<-df2000$详细地址[dfBy仪器$latestIndex]
dfBy仪器 <- dfBy仪器[order(-dfBy仪器$测试数),]
write_excel_csv(dfBy仪器,file="按仪器统计0.csv")

cname0<-c( "仪器序列号"  ,"仪器编号"  ,"最早测试地点","中期测试地点", "最后测试地点",      
           "开机地点"  ,  "批次数"  ,   "测试数"  ,   "样本数"  ,   "项目数" ,   
          "阳性数",     "有效数"  ,   "最早测试" ,  "最近测试" ,  "测试开始" , 
          "测试截止",   "阳性率" ,    "有效率",  
          "SIM卡号"  ,        "发货时间"   ,      "仪器类型"   ,     
          "总测试量"   ,      "最后一次开机时间" ,"时间差"   ,       
                "网络类型" ,        "ip地址"    ,      
          "用户"  ,           "用户CRM"   ,       "返利表序号"  ,    
          "申请日期"  ,       "小细胞"     ,        "客户编码"  ,      
          "代理商名称" ,      "用户名称"   ,      "规格" ,           
          "状态")
colnames(dfBy仪器)
dfBy仪器andInf<-merge(dfBy仪器,仪器编号和序列号,by="仪器序列号",all.x=TRUE,all.y=FALSE)
colnames(dfBy仪器andInf)
dfBy仪器andInf <- dfBy仪器andInf[order(-dfBy仪器andInf$测试截止),][,cname0]
#dfBy仪器andInf0<-distinct(dfBy仪器andInf)
write_excel_csv(dfBy仪器andInf,file="仪器统计andInf.csv")
dfBy仪器andInfall<-merge(dfBy仪器,仪器编号和序列号,by="仪器序列号",all.x=TRUE,all.y=TRUE)
dfBy仪器andInfall <- dfBy仪器andInfall[order(-dfBy仪器andInfall$测试截止),][,cname0]
#dfBy仪器andInfall0<-distinct(dfBy仪器andInfall)
write_excel_csv(dfBy仪器andInfall,file="仪器统计andInfall.csv")
colnames(仪器编号和序列号)
notFound<-仪器编号和序列号[!(仪器编号和序列号$仪器序列号 %in% dfBy仪器$仪器序列号),]

colnames(notFound)

cname1<-c( "仪器序列号"  ,"仪器编号"  ,     
           "开机地点"  ,  
           "SIM卡号"  ,        "发货时间"   ,      "仪器类型"   ,     
           "总测试量"   ,      "最后一次开机时间" ,"时间差"   ,       
           "网络类型" ,        "ip地址"    ,      
           "用户"  ,           "用户CRM"   ,       "返利表序号"  ,    
           "申请日期"  ,       "小细胞"     ,        "客户编码"  ,      
           "代理商名称" ,      "用户名称"   ,      "规格" ,           
           "状态")
#notFound[,cname1]
#notFound0<-distinct(notFound)
write_excel_csv(notFound[,cname1],file="仪器统计Notfound.csv")

if(FALSE) {
  CRMNotFound <- read.csv("CRMNotFound.csv",encoding = "UTF-8",fill = TRUE)
  NoTestSince05012025 <- read.csv("NoTestSince05012025.csv",encoding = "UTF-8",fill = TRUE)
  colnames(CRMNotFound)[c(3,4,5)]<-c("销售申请时间NoCRM","销售申请区域NoCRM","销售申请时代理商名称NoCRM")
  colnames(NoTestSince05012025) [9]<-"备注NoTest"
  
  feedbackData<-merge(CRMNotFound,NoTestSince05012025,by="仪器编号",all.x=TRUE,all.y=TRUE)
  colnames(feedbackData)
  colnames(feedbackData)[10]<-"用户onCRM"
  
  write_excel_csv(feedbackData,file="feedbackData07042025.csv")
  仪器编号和序列号feedbackData<-merge(仪器编号和序列号,feedbackData,by="仪器编号",all.x=TRUE,all.y=TRUE)
  colnames(仪器编号和序列号feedbackData)

dfBy仪器feedbackDataandInf<-merge(dfBy仪器,仪器编号和序列号feedbackData,by="仪器序列号",all.x=TRUE,all.y=FALSE)
colnames(dfBy仪器feedbackDataandInf)
cname0<-c( "仪器序列号"  ,"仪器编号"  ,"最早测试地点","中期测试地点", "最后测试地点",      
           "开机地点"  ,  "批次数"  ,   "测试数"  ,   "样本数"  ,   "项目数" ,   
           "阳性数",     "有效数"  ,   "最早测试" ,  "最近测试" ,  "测试开始" , 
           "测试截止",   "阳性率" ,    "有效率",  
           "SIM卡号"  ,        "发货时间"   ,      "仪器类型"   ,     
           "总测试量"   ,      "最后一次开机时间" ,"时间差"   ,       
           "网络类型" ,        "ip地址"    ,      
           "用户"  ,           "用户CRM"   ,       "返利表序号"  ,    
           "申请日期"  ,       "小细胞"     ,        "客户编码"  ,      
           "代理商名称" ,      "用户名称"   ,      "规格" ,           
           "状态","销售申请时间" ,             "销售申请时间NoCRM"   ,     
           "销售申请区域NoCRM"   ,      "销售申请时代理商名称NoCRM",
           "是否装机" ,                 "未装机原因"  ,             
           "备注"   ,                   "最后测试时间"  ,           
           "用户onCRM"     ,            "销售申请日期"   ,          
           "销售申请区域"   ,           "销售申请时代理商名称"  ,   
           "销售申请时用户名称"    ,    "无测试原因"  ,             
           "备注NoTest"   )
dfBy仪器feedbackDataandInf <- dfBy仪器feedbackDataandInf[order(-dfBy仪器feedbackDataandInf$测试截止),][,cname0]
write_excel_csv(dfBy仪器feedbackDataandInf,file="仪器统计feedbackandInf.csv")
dfBy仪器feedbackDataandInfall<-merge(dfBy仪器,仪器编号和序列号feedbackData,by="仪器序列号",all.x=TRUE,all.y=TRUE)
dfBy仪器feedbackDataandInfall <- dfBy仪器feedbackDataandInfall[order(-dfBy仪器feedbackDataandInfall$测试截止),][,cname0]
write_excel_csv(dfBy仪器feedbackDataandInfall,file="仪器统计feedbackandInfall.csv")
colnames(仪器编号和序列号feedbackData)
notFoundfeedBack<-仪器编号和序列号feedbackData[!(仪器编号和序列号feedbackData$仪器序列号 %in% dfBy仪器$仪器序列号),]

cname1<-c( "仪器序列号"  ,"仪器编号"  ,     
           "开机地点"  ,  
           "SIM卡号"  ,        "发货时间"   ,      "仪器类型"   ,     
           "总测试量"   ,      "最后一次开机时间" ,"时间差"   ,       
           "网络类型" ,        "ip地址"    ,      
           "用户"  ,           "用户CRM"   ,       "返利表序号"  ,    
           "申请日期"  ,       "小细胞"     ,        "客户编码"  ,      
           "代理商名称" ,      "用户名称"   ,      "规格" ,           
           "状态","销售申请时间" ,             "销售申请时间NoCRM"   ,     
           "销售申请区域NoCRM"   ,      "销售申请时代理商名称NoCRM",
           "是否装机" ,                 "未装机原因"  ,             
           "备注"   ,                   "最后测试时间"  ,           
           "用户onCRM"     ,            "销售申请日期"   ,          
           "销售申请区域"   ,           "销售申请时代理商名称"  ,   
           "销售申请时用户名称"    ,    "无测试原因"  ,             
           "备注NoTest" )

colnames(notFoundfeedBack)

write_excel_csv(notFoundfeedBack[,cname1],file="仪器统计NotfoundfeedBack.csv")
}

dfByDayAndLocation <- df2000 %>%  group_by(testDay,testMonth,省编号) %>%  summarise(
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  小细胞数=length(unique(小细胞)),
  市数=length(unique(省市编号)),
  省数=length(unique(省编号))
)
dfByDayAndLocation$阳性率<-dfByDayAndLocation$阳性数/dfByDayAndLocation$有效数
dfByDayAndLocation$有效率<-dfByDayAndLocation$有效数/dfByDayAndLocation$测试数
dfByDayAndLocation$阳性数<-as.integer(dfByDayAndLocation$阳性数)
dfByDayAndLocation$测试数<-as.integer(dfByDayAndLocation$测试数)
dfByDayAndLocation$有效数<-as.integer(dfByDayAndLocation$有效数)
dfByDayAndLocation <- dfByDayAndLocation[order(dfByDayAndLocation$testDay),]

dfByMonthAndLocation <- df20 %>%  group_by(testMonth,省编号) %>%  summarise(
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称)),
  小细胞数=length(unique(小细胞)),
  市数=length(unique(省市编号))
)
dfByMonthAndLocation$阳性率<-dfByMonthAndLocation$阳性数/dfByMonthAndLocation$有效数
dfByMonthAndLocation$有效率<-dfByMonthAndLocation$有效数/dfByMonthAndLocation$测试数
dfByMonthAndLocation$阳性数<-as.integer(dfByMonthAndLocation$阳性数)
dfByMonthAndLocation$测试数<-as.integer(dfByMonthAndLocation$测试数)
dfByMonthAndLocation$有效数<-as.integer(dfByMonthAndLocation$有效数)
dfByMonthAndLocation <- dfByMonthAndLocation[order(dfByMonthAndLocation$testMonth),]

dfByDayAnd细胞 <- df2000 %>%  group_by(testDay,testMonth,小细胞) %>%  summarise(
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称))
)
dfByDayAnd细胞$阳性率<-dfByDayAnd细胞$阳性数/dfByDayAnd细胞$有效数
dfByDayAnd细胞$有效率<-dfByDayAnd细胞$有效数/dfByDayAnd细胞$测试数
dfByDayAnd细胞$阳性数<-as.integer(dfByDayAnd细胞$阳性数)
dfByDayAnd细胞$测试数<-as.integer(dfByDayAnd细胞$测试数)
dfByDayAnd细胞$有效数<-as.integer(dfByDayAnd细胞$有效数)
dfByDayAnd细胞 <- dfByDayAnd细胞[order(dfByDayAnd细胞$testDay),]

dfByMonthAnd细胞 <- df20 %>%  group_by(testMonth,小细胞) %>%  summarise(
  测试数 = n(),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  仪器数=length(unique(仪器序列号)),
  批次数=length(unique(批次名称))
)
dfByMonthAnd细胞$阳性率<-dfByMonthAnd细胞$阳性数/dfByMonthAnd细胞$有效数
dfByMonthAnd细胞$有效率<-dfByMonthAnd细胞$有效数/dfByMonthAnd细胞$测试数
dfByMonthAnd细胞$阳性数<-as.integer(dfByMonthAnd细胞$阳性数)
dfByMonthAnd细胞$测试数<-as.integer(dfByMonthAnd细胞$测试数)
dfByMonthAnd细胞$有效数<-as.integer(dfByMonthAnd细胞$有效数)
dfByMonthAnd细胞 <- dfByMonthAnd细胞[order(dfByMonthAnd细胞$testMonth),]

cairo_pdf(paste("和迈dataAnalytics07142025n",".pdf",sep=""), width = 8, height = 6,family = "SimHei" )

plotSummaryTable<-ggplot() +geom_text(aes(x = 100, y = 40,
                                          label = summaryTable),
                                      stat = "unique",
                                      fontface = "bold",
                                      color = "black",            # 标签颜色
                                      size = 5.0  )+                 # 字体大小
  xlim(0,200)+ylim(0,100)+
  theme_minimal() + theme(axis.text = element_blank(),
                          axis.ticks = element_blank(),
                          axis.title = element_blank())

plotSummaryTable

dfByDay1<-dfByDay[lastDay-dfByDay$testDay<42,]
plotfunction(dfData=dfByDay1,independentVariableName="testDay",
                  dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
plotfunction(dfData=dfByDay1,independentVariableName="testDay",
                  dependentVariableNames=c("有效率","阳性率"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
plotfunction(dfData=dfByDay1,independentVariableName="testDay",
                  dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
plotfunction(dfData=dfByDay1,independentVariableName="testDay",
             dependentVariableNames=c("样本数","市数","省数","小细胞数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))

dfByDayAndLocation1<-dfByDayAndLocation[lastDay-dfByDayAndLocation$testDay<42,]

panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[1:6],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[7:12],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[13:18],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[c(19:23,25)],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[26:31],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))

panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[1:6],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("有效率","阳性率"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[1:6],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAndLocation(dfData=dfByDayAndLocation1,choosenList=dfBy区域$省编号[1:6],independentVariableName="testDay",panelName="省编号",
                          dependentVariableNames=c("样本数","市数","省数","小细胞数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))

dfByDayAnd细胞1<-dfByDayAnd细胞[lastDay-dfByDayAnd细胞$testDay<42,]

panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[1:6],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[7:12],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[13:18],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[19:24],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[25:30],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[31:36],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[37:42],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=dfBy细胞$小细胞[43:nrow(细胞0)],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))

panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[1:6],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("有效率","阳性率"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[1:6],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))
panelPlotByDayAnd细胞(dfData=dfByDayAnd细胞1,choosenList=细胞0$细胞[1:6],independentVariableName="testDay",panelName="小细胞",
                    dependentVariableNames=c("样本数"),titleNames=c("最后六周按日测试统计","测试时间","统计数值",""))

dfByMonth1<-dfByMonth[1:30,]

plotfunction(dfData=dfByMonth1,independentVariableName="testMonth",
                    dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月测试统计","测试时间","统计数值",""))
plotfunction(dfData=dfByMonth1,independentVariableName="testMonth",
                    dependentVariableNames=c("有效率","阳性率"),titleNames=c("按月测试统计","测试时间","统计数值",""))
plotfunction(dfData=dfByMonth1,independentVariableName="testMonth",
                    dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("按月测试统计","测试时间","统计数值",""))
plotfunction(dfData=dfByMonth1,independentVariableName="testMonth",
                    dependentVariableNames=c("样本数","市数","省数","小细胞数"),titleNames=c("按月测试统计","测试时间","统计数值",""))

panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[1:6],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))
panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[7:12],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))
panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[13:18],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))
panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[c(19:23,25)],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))
panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[26:31],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))

panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[1:6],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("有效率","阳性率"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))
panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[1:6],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))
panelPlotByMonthAndLocation(dfData=dfByMonthAndLocation,choosenList=dfBy区域$省编号[1:6],independentVariableName="testMonth",panelName="省编号",
                            dependentVariableNames=c("样本数","市数","小细胞数"),titleNames=c("按月分省测试统计","测试时间","统计数值",""))

panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[1:6],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[7:12],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[13:18],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[19:24],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[25:30],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[31:36],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[37:42],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[43:nrow(细胞0)],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))

panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[1:6],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("有效率","阳性率"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[1:6],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))
panelPlotByMonthAnd细胞(dfData=dfByMonthAnd细胞,choosenList=细胞0$细胞[1:6],independentVariableName="testMonth",panelName="小细胞",
                      dependentVariableNames=c("样本数"),titleNames=c("按月分细胞测试统计","测试时间","统计数值",""))

plotfunction(dfData=dfBy区域,independentVariableName="省编号",
                 dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按省测试统计","省编号","统计数值",""))
plotfunction(dfData=dfBy区域,independentVariableName="省编号",
                 dependentVariableNames=c("有效率","阳性率"),titleNames=c("按省测试统计","省编号","统计数值",""))
plotfunction(dfData=dfBy区域,independentVariableName="省编号",
                 dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("按省测试统计","省编号","统计数值",""))
plotfunction(dfData=dfBy区域,independentVariableName="省编号",
                 dependentVariableNames=c("样本数","市数","小细胞数"),titleNames=c("按省测试统计","省编号","统计数值",""))
plotfunctionLog(dfData=dfBy区域,independentVariableName="省编号",
                 dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按省测试统计","省编号","多少天之前",""))

lenhalf<-length(dfBy细胞$小细胞)/2
dfBy细胞1<-dfBy细胞[dfBy细胞$小细胞 %in% dfBy细胞$小细胞[1:lenhalf],]
dfBy细胞2<-dfBy细胞[dfBy细胞$小细胞 %in% dfBy细胞$小细胞[(1+lenhalf):length(dfBy细胞$小细胞)],]
plotfunction(dfData=dfBy细胞1,independentVariableName="小细胞",
             dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞2,independentVariableName="小细胞",
             dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞1,independentVariableName="小细胞",
             dependentVariableNames=c("有效率","阳性率"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞2,independentVariableName="小细胞",
             dependentVariableNames=c("有效率","阳性率"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞1,independentVariableName="小细胞",
             dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞2,independentVariableName="小细胞",
             dependentVariableNames=c("项目数","仪器数","批次数"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞1,independentVariableName="小细胞",
             dependentVariableNames=c("样本数","市数"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunction(dfData=dfBy细胞2,independentVariableName="小细胞",
             dependentVariableNames=c("样本数","市数"),titleNames=c("按细胞测试统计","细胞","统计数值",""))
plotfunctionLog(dfData=dfBy细胞1,independentVariableName="小细胞",
                dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按细胞测试统计","细胞","多少天之前",""))
plotfunctionLog(dfData=dfBy细胞2,independentVariableName="小细胞",
                dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按细胞测试统计","细胞","多少天之前",""))

仪器号List0<-cbind(dfBy仪器,GroupID=rep00[1:length(dfBy仪器$仪器序列号)])
仪器号List<-仪器号List0[,c(1,ncol(仪器号List0))]
dfBy仪器$仪器序列号<-factor(dfBy仪器$仪器序列号,levels = 仪器号List[,1])

  plotfunctionVector仪器序列号(ik=1,independentVariableList=仪器号List,dfData=dfBy仪器,independentVariableName="仪器序列号",
                   dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按仪器测试统计","仪器序列号","统计数值",""))
  plotfunctionVector仪器序列号(ik=1,independentVariableList=仪器号List,dfData=dfBy仪器,independentVariableName="仪器序列号",
                   dependentVariableNames=c("项目数","样本数","批次数"),titleNames=c("按仪器测试统计","仪器序列号","统计数值",""))
  plotfunctionVector仪器序列号(ik=1,independentVariableList=仪器号List,dfData=dfBy仪器,independentVariableName="仪器序列号",
                   dependentVariableNames=c("有效率","阳性率"),titleNames=c("按仪器测试统计","仪器序列号","统计数值",""))
  plotfunctionVector仪器序列号Log(ik=1,independentVariableList=仪器号List,dfData=dfBy仪器,independentVariableName="仪器序列号",
                      dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按仪器测试统计","仪器序列号","多少天之前",""))


 if(FALSE) {
dfBy仪器00 <- df2000 %>%  group_by(仪器序列号,省市编号,详细地址,仪器备注名称,仪器投放区域) %>%  summarise(
  批次数=length(unique(批次名称)),
  测试数 = n(),
  样本数=length(unique(样本类型)),
  项目数=length(unique(项目名称)),
  阳性数=sum(结论,na.rm = TRUE),
  有效数=sum(是否有效,na.rm = TRUE),
  earliestIndex=min(IDIndex),
  medianIndex=median(IDIndex),
  latestIndex=max(IDIndex),
  最早测试=min(testDay,na.rm = TRUE),
  最近测试=max(testDay,na.rm = TRUE),
  测试开始 = round(max(testTimeFromeToday,na.rm = TRUE),2),
  测试截止 = round(min(testTimeFromeToday,na.rm = TRUE),2)
)
dfBy仪器00$阳性率<-dfBy仪器00$阳性数/dfBy仪器00$有效数
dfBy仪器00$有效率<-dfBy仪器00$有效数/dfBy仪器00$测试数
dfBy仪器00$阳性数<-as.integer(dfBy仪器00$阳性数)
dfBy仪器00$测试数<-as.integer(dfBy仪器00$测试数)
dfBy仪器00$有效数<-as.integer(dfBy仪器00$有效数)



dfBy仪器1<-merge(dfBy仪器00[,1:5],dfBy仪器,by="仪器序列号",all=FALSE)
write_excel_csv(dfBy仪器1,file="仪器统计1.csv")
#仪器号MissedFromTwoYearsAgo<-仪器号[!(仪器号 %in% 仪器号00)]
 }
  
#样本号List<-unique(dfBy样本$样本类型)
plotfunction(dfData=dfBy样本,independentVariableName="样本类型",
             dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按样本类型测试统计","样本类型","统计数值",""))
plotfunction(dfData=dfBy样本,independentVariableName="样本类型",
             dependentVariableNames=c("项目数","批次数","省数","小细胞数"),titleNames=c("按样本类型测试统计","样本类型","统计数值",""))
plotfunction(dfData=dfBy样本,independentVariableName="样本类型",
             dependentVariableNames=c("有效率","阳性率"),titleNames=c("按样本类型测试统计","样本类型","统计数值",""))
plotfunctionLog(dfData=dfBy样本,independentVariableName="样本类型",
                dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按样本类型测试统计","样本类型","多少天之前",""))


批次号List0<-cbind(dfBy批次,GroupID=rep00[1:length(dfBy批次$批次名称)])
批次号List<-批次号List0[,c(1,ncol(批次号List0))]
dfBy批次$批次名称<-factor(dfBy批次$批次名称,levels = 批次号List[,1])

plotfunctionVector批次名称(ik=1,independentVariableList=批次号List,dfData=dfBy批次,independentVariableName="批次名称",
                        dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按批次测试统计","批次名称","统计数值",""))
plotfunctionVector批次名称(ik=1,independentVariableList=批次号List,dfData=dfBy批次,independentVariableName="批次名称",
             dependentVariableNames=c("项目数","样本数","省数","小细胞数"),titleNames=c("按试剂批次测试统计","批次名称","统计数值",""))
plotfunctionVector批次名称(ik=1,independentVariableList=批次号List,dfData=dfBy批次,independentVariableName="批次名称",
             dependentVariableNames=c("有效率","阳性率"),titleNames=c("按试剂批次测试统计","批次名称","统计数值",""))
plotfunctionVector批次名称Log(ik=1,independentVariableList=批次号List,dfData=dfBy批次,independentVariableName="批次名称",
                dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按试剂批次测试统计","批次名称","多少天之前",""))

项目号List0<-cbind(dfBy项目,GroupID=rep00[1:length(dfBy项目$项目名称)])
项目号List<-项目号List0[,c(1,ncol(项目号List0))]
dfBy项目$项目名称<-factor(dfBy项目$项目名称,levels = 项目号List[,1])

plotfunctionVector项目名称(ik=1,independentVariableList=项目号List,dfData=dfBy项目,independentVariableName="项目名称",
                       dependentVariableNames=c("测试数","有效数","阳性数"),titleNames=c("按项目测试统计","项目名称","统计数值",""))
plotfunctionVector项目名称(ik=1,independentVariableList=项目号List,dfData=dfBy项目,independentVariableName="项目名称",
                       dependentVariableNames=c("批次数","样本数","省数","小细胞数"),titleNames=c("按试剂项目测试统计","项目名称","统计数值",""))
plotfunctionVector项目名称(ik=1,independentVariableList=项目号List,dfData=dfBy项目,independentVariableName="项目名称",
                       dependentVariableNames=c("有效率","阳性率"),titleNames=c("按试剂项目测试统计","项目名称","统计数值",""))
plotfunctionVector项目名称Log(ik=1,independentVariableList=项目号List,dfData=dfBy项目,independentVariableName="项目名称",
                          dependentVariableNames=c("测试开始","测试截止"),titleNames=c("按试剂项目测试统计","项目名称","多少天之前",""))


dev.off()

df20$浓度1<-as.numeric(df20$浓度1)
df20$C值<-as.numeric(df20$C值)
df20$T值<-as.numeric(df20$T值)
df20$ToverC值<-df20$T值/df20$C值
df20$ToverC值[df20$是否有效==0]<-NA


summary(as.numeric(df20$结论))
summary(df20$浓度1)
summary(df20$ToverC值)

summary(df20$testTimeFromeToday)
colnames(df20)

#df201<-df20

# for(i in 1:5){
  
  cairo_pdf(paste("和迈dataAnalyticsForRandD","_a07142025m.pdf",sep=""), width = 8, height = 6,family = "SimHei" )
  
  plotSummaryTable
  
#  df20<-df201[(df201$项目名称 %in% dfBy项目top4[i]),]
  # plot项目名称Title<-ggplot() +geom_text(aes(x = 100, y = 60,
  #                                        label = paste("项目名称： ",dfBy项目名称top5[i],sep="")),
  #                                    stat = "unique",
  #                                    fontface = "bold",
  #                                    color = "black",            # 标签颜色
  #                                    size = 8.0  )+                 # 字体大小
  #   xlim(0,200)+ylim(0,100)+
  #   theme_minimal() + theme(axis.text = element_blank(),
  #                           axis.ticks = element_blank(),
  #                           axis.title = element_blank())
  # 
  # plot项目名称Title
  
  df20 <- df20[order(-df20$testTimeFromeToday),]

df20 <- df20 %>%  group_by(批次名称) %>%  transform(
  浓度1移动均值 = running_avg(浓度1,20),
  C值移动均值 = running_avg(C值,20),
  T值移动均值 = running_avg(T值,20),
  ToverC值移动均值 = running_avg(ToverC值,20),
  结论移动均值 = running_avg(结论,20)
)

# df20$浓度1移动均值<-running_avg(df20$浓度1, 20)
# df20$C值移动均值<-running_avg(df20$C值, 20)
# df20$T值移动均值<-running_avg(df20$T值, 20)
# df20$ToverC值移动均值<-running_avg(df20$ToverC值, 20)
# df20$结论移动均值<-running_avg(df20$结论, 20)

df20$浓度1累计均值<-lumping_avg(df20$浓度1)
df20$C值累计均值<-lumping_avg(df20$C值)
df20$T值累计均值<-lumping_avg(df20$T值)
df20$ToverC值累计均值<-lumping_avg(df20$ToverC值)
df20$结论累计均值<-lumping_avg(df20$结论)

# df20$浓度1累计标差<-lumping_sd(df20$浓度1)
# df20$C值累计标差<-lumping_sd(df20$C值)
# df20$T值累计标差<-lumping_sd(df20$T值)
# df20$ToverC值累计标差<-lumping_sd(df20$ToverC值)
# df20$结论累计标差<-lumping_sd(df20$结论移动均值)

df20$浓度1累计标差<-lumping_sd(df20$浓度1移动均值)
df20$C值累计标差<-lumping_sd(df20$C值移动均值)
df20$T值累计标差<-lumping_sd(df20$T值移动均值)
df20$ToverC值累计标差<-lumping_sd(df20$ToverC值移动均值)
df20$结论累计标差<-lumping_sd(df20$结论移动均值)

df20$浓度1允许波动范围<-df20$浓度1累计均值+2*df20$浓度1累计标差
df20$C值允许波动范围<-df20$C值累计均值+2*df20$C值累计标差
df20$T值允许波动范围<-df20$T值累计均值+2*df20$T值累计标差
df20$ToverC值允许波动范围<-df20$ToverC值累计均值+2*df20$ToverC值累计标差
df20$结论允许波动范围<-df20$结论累计均值+2*df20$结论累计标差

summary(df20$浓度1移动均值)
summary(df20$running_avg)
summary(df20$浓度1累计均值)
summary(df20$浓度1累计标差)
summary(df20$结论累计均值)
df200<-df20
df20<-df200[df200$项目名称 %in% 项目号List[1,1], ]
#df20Ploted<-df20[df20$批次名称 %in% 批次号List$批次名称[批次号List[,2]==1],]
df20Ploted<-df20[df20$批次名称 %in% 批次号List$批次名称[1:5],]

pointPlotfunction批次(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="浓度1",titleNames=c("浓度","测试日期","浓度1",""))
pointPlotfunction批次Log(dfData=df20Ploted,independentVariableName="testDay",
                       dependentVariableName="C值",titleNames=c("C值","测试日期","C值",""))
pointPlotfunction批次Log(dfData=df20Ploted,independentVariableName="testDay",
                       dependentVariableName="T值",titleNames=c("T值","测试日期","T值",""))
pointPlotfunction批次(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="ToverC值",titleNames=c("T/C值","测试日期","T/C值",""))
pointPlotfunction批次(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="结论移动均值",titleNames=c("结论/阳性率","测试日期","结论/阳性率",""))

df20Ploted<-df20[df20$仪器序列号 %in% 仪器号List$仪器序列号[1:5],]

pointPlotfunction仪器(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="浓度1",titleNames=c("浓度","测试日期","浓度1",""))
pointPlotfunction仪器Log(dfData=df20Ploted,independentVariableName="testDay",
                       dependentVariableName="C值",titleNames=c("C值","测试日期","C值",""))
pointPlotfunction仪器Log(dfData=df20Ploted,independentVariableName="testDay",
                       dependentVariableName="T值",titleNames=c("T值","测试日期","T值",""))
pointPlotfunction仪器(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="ToverC值",titleNames=c("T/C值","测试日期","T/C值",""))
pointPlotfunction仪器(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="结论移动均值",titleNames=c("结论/阳性率","测试日期","结论/阳性率",""))


df20Ploted<-df20[df20$样本类型 %in% unique(df20$样本类型)[1:2],]

pointPlotfunction样本(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="浓度1",titleNames=c("浓度","测试日期","浓度1",""))
pointPlotfunction样本Log(dfData=df20Ploted,independentVariableName="testDay",
                       dependentVariableName="C值",titleNames=c("C值","测试日期","C值",""))
pointPlotfunction样本Log(dfData=df20Ploted,independentVariableName="testDay",
                       dependentVariableName="T值",titleNames=c("T值","测试日期","T值",""))
pointPlotfunction样本(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="ToverC值",titleNames=c("T/C值","测试日期","T/C值",""))
pointPlotfunction样本(dfData=df20Ploted,independentVariableName="testDay",
                    dependentVariableName="结论移动均值",titleNames=c("结论/阳性率","测试日期","结论/阳性率",""))


dev.off()


