统计出库数，sql查询数据，sqldf做透视表，循坏处理数据

#设置工作路径 D:\资料\天富盈\天富盈\erp\erp录入
setwd("D:\\资料\\程序文档")

#设置文件名
path="C:\\Users\\Jarvis\\Desktop\\12月平台品类数据.xlsx"

sql_yuju<-"SELECT * FROM edb_order
            WHERE SUBSTRING(`订货日期`,1,10) >= '2019-12-01'
            AND SUBSTRING(`订货日期`,1,10) <= '2019-12-31'"

#导入包
library(openxlsx)
library(dplyr)
library(sqldf) 
#library(RMySQL)

#链接数据库
con<-RMySQL::dbConnect(RMySQL::MySQL(),host='127.0.0.1',port=3306,dbname="team fortune",user="root",password="123456")
#查看数据库里所有的表
dbListTables(con)
#设置编码  GBK
dbSendQuery(con,'SET NAMES GBK') 
#查询数据
E<-dbGetQuery(con,sql_yuju)

str(E)
#E店宝数据
#E<-read.csv('12月.csv',header=TRUE,sep=',',colClasses="character")

#店铺名称对照表连入D:\资料\天富盈\天富盈
duizhao<-read.xlsx('D:\\资料\\天富盈\\天富盈\\对照表.xlsx',1)

E<-left_join(E,duizhao,by="产品名称")
names(E)
unique(E$店铺名称)

#按店铺筛选
TM_E<-E[which(E$店铺名称=="【天】chocky旗舰店"),]
TB_E<-E[which(E$店铺名称=="【淘】泰好吃食趣"),]
JD_POP_E<-E[which(E$店铺名称=="【京】比斯奇果屋巧客旗舰店"),]
JD_ZHUAN_E<-E[which(E$店铺名称=="【京】天富盈食品专营店"),]
PDD_E<-E[which(E$店铺名称=="【拼】比斯奇果屋巧客食品旗舰店"),]
WPH_E<-E[which(E$店铺名称=="【唯】唯品会"),]
YZ_E<-E[which(E$店铺名称=="【赞】泰国比斯奇果屋巧客"),]
XHY_E<-E[which(E$店铺名称=="【黑】小黑鱼APP"),]
XCX_E<-E[which(E$店铺名称=="小程序"),]
HSQ_E<-E[which(E$店铺名称=="好食期"),]
shopname<-c(substitute(TM_E),substitute(TB_E),
            substitute(JD_POP_E),substitute(JD_ZHUAN_E),
            substitute(PDD_E),substitute(WPH_E),
            substitute(YZ_E),substitute(XHY_E),
            substitute(XCX_E),substitute(HSQ_E))

#销售量汇总
##分类汇总
toushi<-function(tablename){
  #做数据透视表，构建sql语句
  sql<-paste('select "产品名称",sum(订货数量),sum("实付金额"),"修改后名称" from ',tablename,' group by "产品名称"',sep=" ")
  shops<-sqldf(sql)
  names(shops)<-c("产品名称","订货数量","实付金额","修改后名称")
  return(shops)
}

##数据处理   将分类名中有X的乘入  +号分开
clean<-function(tablname){
  #提取修改名后有X的名称
  doubles<-grep(pattern = "X",x = tablname$修改后名称,value = F)
  #提取乘以倍数
  LastBate<-as.integer(substr(tablname[doubles,]$修改后名称,nchar(tablname[doubles,]$修改后名称),nchar(shop[doubles,]$修改后名称)))
  #将订货数量乘以倍数，去掉修改名称后面的乘项
  tablname[doubles,]$订货数量 <- tablname[doubles,]$订货数量 *  LastBate 
  tablname[doubles,]$修改后名称<-substr(tablname[doubles,]$修改后名称,1,nchar(tablname[doubles,]$修改后名称)-2)
  
  #提取修改名中有 + 的名称
  adds<-grep(pattern = "\\+",x = tablname$修改后名称,value = F)
  if (length(adds)!=0) {
    #将有+的数据剔除，复制到新表
    tichu<-tablname[adds,]
    tablname<-tablname[-adds,]
    
    #将修改名中有 + 的名称用+切割为list
    xx<-strsplit(tichu$修改后名称,split = "\\+")
    
    #将有+的数据循环写回原来的表
    for (j in c(1:length(adds))) {
      print("开始处理这个表")
      pro<-cbind(tichu$产品名称[j],tichu$订货数量[j],tichu$实付金额[j]/length(xx[[j]]),xx[[j]])
      #print(pro)
      colnames(pro)<-colnames(tablname)
      tablname<-rbind(tablname,pro)
    }
    
  }else {
    print("这个表无需处理")
  } 
  return(tablname)
}

#循环查询 
mydata<-list() 
for (i in shopname) {
  shop<-toushi(i)
  shop<-clean(shop)
  #再进行一次透视处理
  sql<-paste('select "修改后名称",sum(订货数量),sum("实付金额") from ',substitute(shop),' group by "修改后名称"',sep=" ")
  shops<-sqldf(sql)
  names(shops)<-c("产品名称","订货数量","销售额")
  shops<-arrange(shops, desc(shops$订货数量))
  mydata[[i]]<-shops
}

# 总体数据
shuju<-matrix(NA,0,3)
colnames(shuju)<-names(mydata[[1]])
shuju<-as.data.frame(shuju)
for (j in mydata){
  shuju<-rbind(shuju,j)
}
shuju<-sqldf('select "产品名称",sum(订货数量) "订货数量",sum("销售额") "销售额" from shuju group by "产品名称" order by 订货数量 DESC')

#写出list
names(mydata)<-c("天猫", "淘宝","京东旗舰店" ,"京东专营店","拼多多",
                 "唯品会" ,"有赞" ,"小黑鱼","小程序","好食期")
shuju<-list(shuju)
#写出 
write.xlsx(c("总体"=shuju,mydata), path)