package api import ( "encoding/json" "errors" "fmt" "github.com/360EntSecGroup-Skylar/excelize" "github.com/Anderson-Lu/gofasion/gofasion" "github.com/gin-gonic/gin" "github.com/kptyun/KPTCOMM/pkg/app" "github.com/kptyun/KPTCOMM/pkg/e" "github.com/kptyun/KPTCOMM/pkg/logging" "github.com/kptyun/KPTCOMM/pkg/setting" "github.com/kptyun/KPTCOMM/routers/restful" "github.com/tealeg/xlsx" "github.com/xormplus/xorm" "io/ioutil" "net/http" "os" "path" "strconv" "strings" "time" ) // @Summary 根据SQL执行得到数据集 // @Tags getData // @Accept json // @Produce json // @Param sql query string true "sql" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/getdata [POST] func GetData(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlstr := fsion.Get("sql").ValueStr() offset := fsion.Get("offset").ValueDefaultInt(0) pagecount := fsion.Get("pagecount").ValueDefaultInt(0) returntype := fsion.Get("returntype").ValueDefaultStr("map") queryData, err := getDataBySql(sqlstr, offset, pagecount, returntype, nil) if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } else { appG.Response(http.StatusOK, e.SUCCESS, queryData) } } func TestData(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlstr := fsion.Get("sql").ValueStr() token := "" if tempv, exists := c.Get("jwt_username"); exists { token = tempv.(string) } appG.Response(http.StatusOK, e.SUCCESS, "你录入的名字是："+sqlstr+"；token:"+token) } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags GetDataByName // @Accept json // @Produce json // @Param sqlname body string true "sqlname" // @Param offset body int true "offset" // @Param pagecount body int true "pagecount" // @Param returntype body string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/GetDataByName [POST] func GetDataByName(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlnamestr := fsion.Get("name").ValueStr() offset := fsion.Get("offset").ValueDefaultInt(0) pagecount := fsion.Get("pagecount").ValueDefaultInt(0) returntype := fsion.Get("returntype").ValueDefaultStr("map") sql, p := restful.GetSqlByNameDB(sqlnamestr) fmt.Println("restful.GetSqlByNameDB",sql) if sql != "" { s_params := make([]interface{}, 0) if fsion.HasKey("parammaps") { parammaps := fsion.Get("parammaps") logging.Info("GetDataByName",c.Keys,c.Request.RemoteAddr,sqlnamestr,parammaps.ValueStr()) paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { s_params = append(s_params, tempv.(string)) } else { s_params = append(s_params, "") } } else { s_params = append(s_params, parammaps.Get(strings.Trim(value, " ")).ValueStr()) } } } } else if fsion.HasKey("params") { params := fsion.Get("params").Array() logging.Info("GetDataByName",c.Keys,c.Request.RemoteAddr,sqlnamestr,params) for _, v_params := range params { s_params = append(s_params, v_params.ValueStr()) } } if returntype == "xlsx" { var xlsxfile *xlsx.File xlsxfile, _ = restful.QueryByXlsx(sql, s_params) //c.File("c:/dd.xlsx") c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") c.Header("Content-Disposition", "attachment; filename="+"Workbook.xlsx") c.Header("Content-Transfer-Encoding", "binary") _ = xlsxfile.Write(c.Writer) } else { queryData, err := getDataBySql(sql, offset, pagecount, returntype, s_params) if err != nil { logging.Error("GetDataByName err: ",err) appG.Response(http.StatusOK, e.ERROR, err.Error()) } else { appG.Response(http.StatusOK, e.SUCCESS, queryData) } } } else { appG.Response(http.StatusOK, e.SUCCESS, nil) } } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags GetDataByNameForm // @Accept json // @Produce json // @Param sqlname body json true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Param s formData json true "ss" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/GetDataByNameForm [POST] func GetDataByNameForm(c *gin.Context) { appG := app.Gin{C: c} //sqlnamestr := c.PostForm("measureNumber") //dataByte, _ := ioutil.ReadAll(c.Request.Body) //fsion := gofasion.NewFasion(string(dataByte)) //sqlnamestr := fsion.Get("name").ValueStr() //offset := fsion.Get("offset").ValueInt() //pagecount := fsion.Get("pagecount").ValueInt() //returntype := fsion.Get("returntype").ValueStr() //params := fsion.Get("params").Array() c.Request.ParseForm() params := c.Request.Form offset := 0 pageSize_ := 0 returntype := params.Get("page") if returntype == "" { returntype = "0" } if tempv, exists := c.Get("jwt_username"); exists { params.Set("JwtUserName", tempv.(string)) } else { params.Set("JwtUserName", "") } sqlnamestr := params.Get("name") logging.Info("GetDataByNameForm ",c.Keys,sqlnamestr) s_params := make([]interface{}, 0) if returntype == "1" { pageNum, error := strconv.Atoi(params.Get("pageNum")) if error != nil { appG.ResponseEq(http.StatusOK, e.ERROR, 0, "页码不是整数") } pageSize, error := strconv.Atoi(params.Get("pageSize")) if error != nil { appG.ResponseEq(http.StatusOK, e.ERROR, 0, "每页行数不是整数") } pageSize_ = pageSize offset = pageNum } sql, p := restful.GetSqlByNameDB(sqlnamestr) paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { s_params = append(s_params, params.Get(strings.Trim(value, " "))) } } if sql != "" { if returntype == "xlsx" { var xlsxfile *xlsx.File xlsxfile, _ = restful.QueryByXlsx(sql, s_params) c.Header("Content-Type", "application/octet-stream") c.Header("Content-Disposition", "attachment; filename="+sqlnamestr+".xlsx") c.Header("Content-Transfer-Encoding", "binary") _ = xlsxfile.Write(c.Writer) } else { queryData, err := getDataBySql(sql, offset, pageSize_, returntype, s_params) if err != nil { appG.ResponseEq(http.StatusOK, e.ERROR, 1, err.Error()) } else { appG.ResponseEq(http.StatusOK, e.SUCCESS, 0, queryData) } } } else { appG.ResponseEq(http.StatusOK, e.SUCCESS, 0, "后台没有数据接口") } } // @Summary 递归获取所有 // @Tags menu // @Accept json // @Produce json // @Param Ton query string true "Ton" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Router /authdata/GetRoleMenus [GET] func GetRecuDataByName(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlnamestr := fsion.Get("name").ValueStr() parentidname := fsion.Get("idname").ValueStr() sql, p := restful.GetSqlByNameDB(sqlnamestr) params := make(map[string]interface{}) s_params := make([]interface{}, 0) if fsion.HasKey("params") { for _, v_params := range fsion.Get("params").Array() { s_params = append(s_params, v_params.ValueStr()) } params["params"] = s_params } else { jparams := fsion.Get("parammaps") paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { params[value] = tempv.(string) } else { params[value] = "" } } else { params[value] = jparams.Get(strings.Trim(value, " ")).ValueStr() } } } } queryData, err := GetRecuData_BySql(sql, p, parentidname, params, fsion.HasKey("params")) logging.Info("sqlname parammaps",c.Keys,c.Request.RemoteAddr,sqlnamestr,params) if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } appG.Response(http.StatusOK, e.SUCCESS, queryData) } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags GetDataByNames // @Accept json // @Produce json // @RequestBody sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/GetDataByNames [POST] func GetDataByNames(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlParams := fsion.Array() queryData := make(map[string]interface{}) for _, v := range sqlParams { fsion2 := gofasion.NewFasion(v.Json()) logging.Info("GetDataByNames",c.Keys,fsion2.Get("name").ValueStr(),c.Request.RemoteAddr) sql, p := restful.GetSqlByNameDB(fsion2.Get("name").ValueStr()) if sql == "" { errorm := make(map[string]interface{}) errorm["code"] = e.ERROR errorm["message"] = "sql没找到" queryData[fsion2.Get("name").ValueStr()] = errorm continue } else if sql != "" { offset := fsion2.Get("offset").ValueInt() pagecount := fsion2.Get("pagecount").ValueInt() returntype := fsion2.Get("returntype").ValueStr() s_params := make([]interface{}, 0) if fsion2.HasKey("parammaps") { parammaps := fsion2.Get("parammaps") paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { s_params = append(s_params, tempv.(string)) } else { s_params = append(s_params, "") } } else { s_params = append(s_params, parammaps.Get(strings.Trim(value, " ")).ValueStr()) } } } } else if fsion2.HasKey("params") { params := fsion2.Get("params").Array() for _, v_params := range params { s_params = append(s_params, v_params.ValueStr()) } } queryData1, err := getDataBySql(sql, offset, pagecount, returntype, s_params) if err != nil { errorm := make(map[string]interface{}) errorm["code"] = e.ERROR errorm["message"] = err.Error() queryData[fsion2.Get("name").ValueStr()] = errorm logging.Error("GetDataByNames err: ",err) continue } else { queryData[fsion2.Get("name").ValueStr()] = queryData1 } } } appG.Response(http.StatusOK, e.SUCCESS, queryData) } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags PostDataByNameForm // @Accept json // @Produce json // @Param sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误朱红彬彬"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/PostDataByNameForm [POST] func PostDataByNameForm(c *gin.Context) { appG := app.Gin{C: c} c.Request.ParseForm() params := c.Request.Form sqlnamestr := params.Get("name") logging.Info("PostDataByNameForm ",c.Keys,sqlnamestr) s_params := make([]interface{}, 0) sql, p := restful.GetSqlByNameDB(sqlnamestr) paramslist := strings.Split(p, ",") if len(paramslist) > 0 { for _, value := range paramslist { s_params = append(s_params, params.Get(strings.Trim(value, " "))) } } if sql != "" { queryData, err := execDataBySql(sql, s_params) if err != nil { appG.ResponseEq(http.StatusOK, e.ERROR, 1, err.Error()) } else { appG.ResponseEq(http.StatusOK, e.SUCCESS, 0, queryData) } } else { appG.ResponseEq(http.StatusOK, e.SUCCESS, 0, "后台没有数据接口") } } func GetReportform(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlnamestr := fsion.Get("name").ValueStr() parammaps := fsion.Get("parammaps") logging.Info("GetReportform ",c.Keys,sqlnamestr,parammaps) queryData,_ :=praseReportform(sqlnamestr,parammaps) appG.Response(http.StatusOK, e.SUCCESS, queryData) } func praseReportform(sqlnamestr string,parammaps *gofasion.Fasion)(queryData map[string]interface{},err error){ switch sqlnamestr { case "getpSumNow","getpSumNowWater","getpSumNowElec","getpSumNowDiesel" : queryData,err =getpSumNow(sqlnamestr,parammaps) case "getPastureSumMonth","getPastureSumMonthWater","getPastureSumMonthElec" ,"getPastureSumMonthDiesel": queryData,err =getPastureSumMonth(sqlnamestr,parammaps) case "getPastureSumDay","getPastureSumDayWater","getPastureSumDayElec","getPastureSumDayDiesel" : queryData,err =getPastureSumDay(sqlnamestr,parammaps) case "geteqclassSumMonth","geteqclassSumMonthWater","geteqclassSumMonthElec","geteqclassSumMonthDiesel" : queryData,err =geteqclassSumMonth(sqlnamestr,parammaps) case "geteCowWaterList","geteCowElecList","geteCowDieselList","geteCowMaintainList","geteCowAllList" : queryData,err =geteCow(sqlnamestr,parammaps) case "geteCowWaterMonthList","geteCowElecMonthList","geteCowDieselMonthList","geteCowMaintainMonthList","geteCowAllMonthList" : queryData,err =geteCowMonth(sqlnamestr,parammaps) case "getEqRunSurveyListPasture" : queryData,err =getEqRunSurveyListPasture(sqlnamestr,parammaps) case "getEqRunSurveyListDay" : queryData,err =getEqRunSurveyListDay(sqlnamestr,parammaps) default : queryData,err =nil,nil } return } func geteCowMonth(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("year", " ")).ValueStr() pastureNames:= parammaps.Get(strings.Trim("pastureName", " ")).ValueStr() var pSumNow, pSumLast []map[string]string if sqlnamestr == "geteCowAllMonthList" { sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ = restful.Engine.SQL(sqlNow,receiveTime,receiveTime,receiveTime,receiveTime,receiveTime,receiveTime,pastureNames).QueryString() tempTime,_ := strconv.Atoi(receiveTime) receiveTimetemp :=strconv.Itoa(tempTime-1) pSumLast, _ = restful.Engine.SQL(sqlNow,receiveTimetemp,receiveTimetemp,receiveTimetemp,receiveTimetemp,receiveTimetemp,receiveTimetemp,pastureNames).QueryString() }else { sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ = restful.Engine.SQL(sqlNow,receiveTime,receiveTime,receiveTime,pastureNames).QueryString() tempTime,_ := strconv.Atoi(receiveTime) receiveTimetemp :=strconv.Itoa(tempTime-1) pSumLast, _ = restful.Engine.SQL(sqlNow,receiveTimetemp,receiveTimetemp,receiveTimetemp,pastureNames).QueryString() } //sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) //pSumNow, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTime).QueryString() //tempTime,_ := strconv.Atoi(receiveTime) //receiveTimetemp :=strconv.Itoa(tempTime-1) //pSumLast, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTimetemp).QueryString() chartData :=make(map[string]interface{}) months := []string {} lastYear := []string{} nowYear := []string{} budgetYear := []string{} //lastTotal,nowTotal := 0,0 a,b :=true,true month :="" for i:= 1; i<=12; i++{ if i<10{ month = receiveTime+"-0"+strconv.Itoa(i) }else { month = receiveTime+"-"+strconv.Itoa(i) } months = append(months,month) for _, va := range pSumNow { if va["month"][5:] == month[5:] { nowYear = append(nowYear, va["psum"]) budgetYear = append(budgetYear, va["lsum"]) a = false } } for _, va := range pSumLast { if va["month"][5:] == month[5:] { lastYear = append(lastYear, va["psum"]) b = false } } if a { nowYear = append(nowYear, "0") budgetYear = append(budgetYear, "0") } if b { lastYear = append(lastYear, "0") } a = true b = true } chartData["months"] = months chartData["lastYear"] = lastYear chartData["nowYear"] = nowYear chartData["budgetYear"] = budgetYear queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func getEqRunSurveyListDay(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("praseTime", " ")).ValueStr() pastureNames:= parammaps.Get(strings.Trim("pastureName", " ")).ValueStr() sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTime).QueryString() if pSumNow == nil { return nil,nil } typeName := make([]map[string]interface{},4) ma := make(map[string]interface{}) ma1 := make(map[string]interface{}) ma2 := make(map[string]interface{}) ma3 := make(map[string]interface{}) ma["name"] = "在用" ma["value"] =pSumNow[0]["eqUse"] typeName[0] = ma ma1["name"] = "闲置" ma1["value"] =pSumNow[0]["eqXZ"] typeName[1] = ma1 ma2["name"] = "报废" ma2["value"] =pSumNow[0]["eqBF"] typeName[2] = ma2 ma3["name"] = "封存" ma3["value"] =pSumNow[0]["eqFC"] typeName[3] = ma3 queryData["psm"] = typeName queryData["code"] = 0 queryData["total"] = pSumNow[0]["eqSum"] queryData["message"] = "ok" return queryData,nil } func getEqRunSurveyListPasture(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("praseTime", " ")).ValueStr() pastureNames, _ := restful.Engine.SQL("SELECT `name` FROM pasture").QueryString() sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ := restful.Engine.SQL(sqlNow,receiveTime).QueryString() chartData :=make(map[string]interface{}) pasture := []string {} eqUse := []string{} eqXZ := []string{} eqBF := []string{} eqFC := []string{} //lastTotal,nowTotal := 0,0 a :=true for _, value := range pastureNames { pasture = append(pasture, value["name"]) for _, va := range pSumNow { if va["pastureName"] == value["name"] { eqUset,_ := strconv.Atoi( va["eqUse"]) eqSumt,_ := strconv.Atoi( va["eqSum"]) eqXZt,_ := strconv.Atoi( va["eqXZ"]) eqBFt,_ := strconv.Atoi( va["eqBF"]) eqFCt,_ := strconv.Atoi( va["eqFC"]) eqUsetem := float64(eqUset)/float64(eqSumt)*100 eqXZtem := float64(eqXZt)/float64(eqSumt)*100 eqBFtem := float64(eqBFt)/float64(eqSumt)*100 eqFCtem := float64(eqFCt)/float64(eqSumt)*100 eqUse = append(eqUse, strconv.FormatFloat(eqUsetem,'f',2,64)) //eqSum = append(eqSum, va["eqSum"]) eqXZ = append(eqXZ, strconv.FormatFloat(eqXZtem,'f',2,64)) eqBF = append(eqBF, strconv.FormatFloat(eqBFtem,'f',2,64)) eqFC = append(eqFC, strconv.FormatFloat(eqFCtem,'f',2,64)) a = false } } if a { eqUse = append(eqUse, "0") //eqSum = append(eqSum, "0") eqXZ = append(eqXZ, "0") eqBF = append(eqBF, "0") eqFC = append(eqFC, "0") } a = true } chartData["pastureName"] = pasture chartData["eqUseRate"] = eqUse //chartData["eqSum"] = eqSum chartData["eqXZRate"] = eqXZ chartData["eqBFRate"] = eqBF chartData["eqFCRate"] = eqFC queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func geteqclassSumMonth(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("receiveTime", " ")).ValueStr() pastureNames:= parammaps.Get(strings.Trim("pastureName", " ")).ValueStr() deptName:= parammaps.Get(strings.Trim("deptName", " ")).ValueStr() sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTime,deptName).QueryString() chartData :=make(map[string]interface{}) typeName := []string {} eqCost := []string{} for _, va := range pSumNow { typeName = append(typeName, va["typeName"]) eqCost = append(eqCost, va["psum"]) } chartData["typeName"] = typeName chartData["eqCost"] = eqCost queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func getPastureSumDay(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("receiveTime", " ")).ValueStr() pastureNames:= parammaps.Get(strings.Trim("pastureName", " ")).ValueStr() sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTime).QueryString() tempTime,_ := strconv.Atoi(receiveTime[:4]) receiveTimetemp :=strconv.Itoa(tempTime-1)+receiveTime[4:] pSumLast, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTimetemp).QueryString() chartData :=make(map[string]interface{}) months := []string {} lastYear := []string{} nowYear := []string{} //lastTotal,nowTotal := 0,0 a,b :=true,true month :="" dayCount :=GetMonthCount(receiveTime) for i:= 1; i<=dayCount; i++{ if i<10{ month = receiveTime+"-0"+strconv.Itoa(i) }else { month = receiveTime+"-"+strconv.Itoa(i) } months = append(months,strconv.Itoa(i)) for _, va := range pSumNow { if va["month"][8:] == month[8:] { nowYear = append(nowYear, va["psum"]) a = false } } for _, va := range pSumLast { if va["month"][8:] == month[8:] { lastYear = append(lastYear, va["psum"]) b = false } } if a { nowYear = append(nowYear, "0") } if b { lastYear = append(lastYear, "0") } a = true b = true } chartData["day"] = months chartData["lastYear"] = lastYear chartData["nowYear"] = nowYear queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func GetMonthCount(receiveTime string) (days int) { year,_ := strconv.Atoi(receiveTime[:4]) month,_ := strconv.Atoi(receiveTime[5:]) if month != 2 { if month == 4 || month == 6 || month == 9 || month == 11 { days = 30 } else { days = 31 } } else { if (((year % 4) == 0 && (year % 100) != 0) || (year % 400) == 0) { days = 29 } else { days = 28 } } return } func getPastureSumMonth(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("receiveTime", " ")).ValueStr() pastureNames:= parammaps.Get(strings.Trim("pastureName", " ")).ValueStr() sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTime).QueryString() tempTime,_ := strconv.Atoi(receiveTime) receiveTimetemp :=strconv.Itoa(tempTime-1) pSumLast, _ := restful.Engine.SQL(sqlNow,pastureNames,receiveTimetemp).QueryString() chartData :=make(map[string]interface{}) months := []string {} lastYear := []string{} nowYear := []string{} //lastTotal,nowTotal := 0,0 a,b :=true,true month :="" for i:= 1; i<=12; i++{ if i<10{ month = receiveTime+"-0"+strconv.Itoa(i) }else { month = receiveTime+"-"+strconv.Itoa(i) } months = append(months,month) for _, va := range pSumNow { if va["month"][5:] == month[5:] { nowYear = append(nowYear, va["psum"]) a = false } } for _, va := range pSumLast { if va["month"][5:] == month[5:] { lastYear = append(lastYear, va["psum"]) b = false } } if a { nowYear = append(nowYear, "0") } if b { lastYear = append(lastYear, "0") } a = true b = true } chartData["months"] = months chartData["lastYear"] = lastYear chartData["nowYear"] = nowYear queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func geteCow(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("year", " ")).ValueStr() pastureNames, _ := restful.Engine.SQL("SELECT `name` FROM pasture").QueryString() var pSumNow, pSumLast []map[string]string if sqlnamestr == "geteCowAllList" { sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ = restful.Engine.SQL(sqlNow,receiveTime,receiveTime,receiveTime,receiveTime,receiveTime,receiveTime).QueryString() receiveTimei,_ := strconv.Atoi(receiveTime) receiveTime =strconv.Itoa(receiveTimei-1) pSumLast, _ = restful.Engine.SQL(sqlNow,receiveTime,receiveTime,receiveTime,receiveTime,receiveTime,receiveTime).QueryString() }else { sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ = restful.Engine.SQL(sqlNow,receiveTime,receiveTime,receiveTime).QueryString() receiveTimei,_ := strconv.Atoi(receiveTime) receiveTime =strconv.Itoa(receiveTimei-1) pSumLast, _ = restful.Engine.SQL(sqlNow,receiveTime,receiveTime,receiveTime).QueryString() } chartData :=make(map[string]interface{}) pasture := []string {} lastYear := []string{} nowYear := []string{} budgetYear := []string{} //lastTotal,nowTotal := 0,0 a,b :=true,true for _, value := range pastureNames { pasture = append(pasture, value["name"]) for _, va := range pSumNow { if va["name"] == value["name"] { nowYear = append(nowYear, va["psum"]) budgetYear = append(budgetYear, va["lsum"]) a = false } } for _, va := range pSumLast { if va["name"] == value["name"] { lastYear = append(lastYear, va["psum"]) b = false } } if a { nowYear = append(nowYear, "0") budgetYear = append(budgetYear, "0") } if b { lastYear = append(lastYear, "0") } a = true b = true } chartData["pasture"] = pasture chartData["lastYear"] = lastYear chartData["nowYear"] = nowYear chartData["budgetYear"] = budgetYear queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func getpSumNow(sqlnamestr string,parammaps *gofasion.Fasion)(map[string]interface{},error){ queryData := make(map[string]interface{}) receiveTime :=parammaps.Get(strings.Trim("receiveTime", " ")).ValueStr() pastureNames, _ := restful.Engine.SQL("SELECT `name` FROM pasture").QueryString() sqlNow, _ := restful.GetSqlByNameDB(sqlnamestr) pSumNow, _ := restful.Engine.SQL(sqlNow,receiveTime).QueryString() receiveTimei,_ := strconv.Atoi(receiveTime) receiveTime =strconv.Itoa(receiveTimei-1) pSumLast, _ := restful.Engine.SQL(sqlNow,receiveTime).QueryString() chartData :=make(map[string]interface{}) pasture := []string {} lastYear := []string{} nowYear := []string{} //lastTotal,nowTotal := 0,0 a,b :=true,true for _, value := range pastureNames { pasture = append(pasture, value["name"]) for _, va := range pSumNow { if va["name"] == value["name"] { nowYear = append(nowYear, va["psum"]) a = false } } for _, va := range pSumLast { if va["name"] == value["name"] { lastYear = append(lastYear, va["psum"]) b = false } } if a { nowYear = append(nowYear, "0") } if b { lastYear = append(lastYear, "0") } a = true b = true } chartData["pasture"] = pasture chartData["lastYear"] = lastYear chartData["nowYear"] = nowYear queryData["chart_data"] = chartData queryData["code"] = 0 queryData["message"] = "ok" return queryData,nil } func GetUpkeepPlan(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlnamestr := fsion.Get("name").ValueStr() offset := fsion.Get("offset").ValueDefaultInt(0) pagecount := fsion.Get("pagecount").ValueDefaultInt(0) returntype := fsion.Get("returntype").ValueDefaultStr("map") month := fsion.Get("month").ValueDefaultStr("map") logging.Info("GetUpkeepPlan ",c.Keys,sqlnamestr,month) sql, p := restful.GetSqlByNameDB(sqlnamestr) if sql != "" { s_params := make([]interface{}, 0) if fsion.HasKey("parammaps") { parammaps := fsion.Get("parammaps") paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { s_params = append(s_params, tempv.(string)) } else { s_params = append(s_params, "") } } else { s_params = append(s_params, parammaps.Get(strings.Trim(value, " ")).ValueStr()) } } } } else if fsion.HasKey("params") { params := fsion.Get("params").Array() for _, v_params := range params { s_params = append(s_params, v_params.ValueStr()) } } if returntype == "xlsx" { var xlsxfile *xlsx.File xlsxfile, _ = restful.QueryByXlsx(sql, s_params) //c.File("c:/dd.xlsx") c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") c.Header("Content-Disposition", "attachment; filename="+"Workbook.xlsx") c.Header("Content-Transfer-Encoding", "binary") _ = xlsxfile.Write(c.Writer) } else { queryData, err := getDataBySql(sql, offset, pagecount, returntype, s_params) if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } else { for index, value := range queryData.(map[string]interface{})["list"].([]map[string]interface{}) { if v, ok := value["id"]; ok { //month = "2020-03" seviceplans, _ := restful.Engine.SQL("SELECT * FROM seviceplan where eqId = ? and month=? ", v, month).QueryString() //获取保养计划 if seviceplans != nil { for _, va := range seviceplans { //根据保养计划获取保养单 //day,_ :=strconv.Atoi(va["day"]) bigupkeeps, _ := restful.Engine.SQL("SELECT * FROM bigupkeep where seviceplanId = ?", va["id"]).QueryString() //获取保养计划中的保养单 //buk := make(map[string]map[string]string) for _, bu := range bigupkeeps { //日期为key ，保养单为value 存到map中 jstr := "" if bu["plantime"][8:9] == "0" { jstr = "m" + bu["plantime"][9:] } else { jstr = "m" + bu["plantime"][8:] } if bu["statue"] == "0" && bu["plantime"] < time.Now().Format("2006-01-02") { //如果保养状态是0并且小于当前日期 则状态已延期2 queryData.(map[string]interface{})["list"].([]map[string]interface{})[index][jstr] = va["upkeepName"] + "#" + va["id"] + "#2" + "#" + bu["id"] } else { if bu["statue"] == "2" { queryData.(map[string]interface{})["list"].([]map[string]interface{})[index][jstr] = va["upkeepName"] + "#" + va["id"] + "#1" + "#" + bu["id"] }else { queryData.(map[string]interface{})["list"].([]map[string]interface{})[index][jstr] = va["upkeepName"] + "#" + va["id"] + "#0" + "#" + bu["id"] } } //buk[bu["plantime"]] = bu } //upkeepCycel,_ :=strconv.Atoi(va["upkeepCycel"]) //if upkeepCycel<=0 { // upkeepCycel = 1 //} //for j:=day;j<=31 ;j+= upkeepCycel { // var daytem string // if j<10 { // daytem = "0"+strconv.Itoa(j) // }else { // daytem =strconv.Itoa(j) // } // //date := month + "-" + daytem // ////bigupkeep, _ := restful.Engine.SQL("SELECT * FROM bigupkeep where seviceplanId = ? and plantime=?",va["id"],date).QueryString() // //jstr := "m"+strconv.Itoa(int(j)) // //if bigupkeep != nil { // // if bigupkeep[0]["statue"] == "0" && date 0 && p != "" { for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { s_params = append(s_params, tempv.(string)) } else { s_params = append(s_params, "") } } else { s_params = append(s_params, parammaps.Get(strings.Trim(value, " ")).ValueStr()) } } } } else if fsion.HasKey("params") { params := fsion.Get("params").Array() logging.Info("PostDataByName ",c.Keys,c.Request.RemoteAddr,sqlnamestr,params) for _, v_params := range params { s_params = append(s_params, v_params.ValueStr()) } } queryData, err := execDataBySql(sql, s_params) if err != nil { logging.Error("PostDataByName err: ",err) appG.Response(http.StatusOK, e.ERROR, err.Error()) } else { appG.Response(http.StatusOK, e.SUCCESS, queryData) } } else { appG.Response(http.StatusOK, e.SUCCESS, nil) } } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags ExecDataByConfig // @Accept json // @Produce json // @Param sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/PostDataByName [POST] func ExecDataByConfig(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) tempval_ := make(map[string]interface{}) tempCommon := make(map[string]interface{}) tempval := make([]map[string]interface{}, 0) err := json.Unmarshal(dataByte, &tempval_) logging.Info("ExecDataByConfig ",c.Keys,c.Request.RemoteAddr,tempval_["common"],tempval_["data"]) if err != nil { fmt.Println("error:", err) } else { if v, ok := tempval_["common"].(map[string]interface{}); ok { tempCommon = v } if v, ok := tempval_["data"].([]interface{}); ok { for _, Tvalue := range v { if v1, ok := Tvalue.(map[string]interface{}); ok { tempval = append(tempval, v1) } } } tx := restful.Engine.NewSession() err := tx.Begin() if err != nil { fmt.Println("GetT error:", err) } defer func() { switch { case err != nil: fmt.Println("__error:", err) if tx != nil { tx.Rollback() } default: if tx != nil { err = tx.Commit() } } if tx != nil { tx.Close() } }() if err == nil { if tempv, exists := c.Get("jwt_username"); exists { tempCommon["jwt_username"] = tempv.(string) } for _, paramvalue := range tempval { if _, ok := paramvalue["resultname"]; !ok { paramvalue["resultname"] = paramvalue["name"] } _, err = ExecDataParam(paramvalue, tempCommon, tempval, nil, nil, tx) if err != nil { fmt.Println("ExecDataParam error:", err) break } } if err == nil { if tempCommon["returnmap"] != nil && tempCommon["returnmap"] != "" { appG.Response(http.StatusOK, e.SUCCESS, tempval) } else { appG.Response(http.StatusOK, e.SUCCESS, "成功执行！") } } else { appG.Response(http.StatusOK, e.ERROR, err.Error()) } } } if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } } func interfacetostring(val interface{}) string { switch val.(type) { case string: ss := val.(string) return ss break case int: ss := val.(int) return strconv.Itoa(ss) break case int64: ss := val.(int64) return strconv.FormatInt(ss, 10) break case float64: ss := val.(float64) return strconv.FormatFloat(ss, 'E', -1, 32) break } return "" } func getParamValue(parammaps map[string]interface{}, value string, ParamMap []map[string]interface{}) string { if valuestring, ok := parammaps[strings.Trim(value, " ")].(string); ok { tempstrb := strings.IndexAny(valuestring, "@") tempstre := strings.IndexAny(valuestring, ".") if tempstrb > -1 && tempstre > -1 { for _, ParamMapList := range ParamMap { if v, ok := ParamMapList["resultname"]; ok { if v == valuestring[tempstrb+1:tempstre] { if v1, ok := ParamMapList["resultmaps"]; ok { if newA, ok := v1.([]map[string]interface{}); ok { return interfacetostring(newA[0][valuestring[tempstre+1:]]) } else if newA, ok := v1.(map[string]interface{}); ok { return interfacetostring(newA[valuestring[tempstre+1:]]) } } } } } } else { return valuestring } } else if valuestring, ok := parammaps[strings.Trim(value, " ")].(float64); ok { return strconv.FormatInt(int64(valuestring), 10) } return "" } func getParamValue_(parammaps map[string]interface{}, value string, ParamMap map[string]interface{}) string { if valuestring, ok := parammaps[strings.Trim(value, " ")].(string); ok { tempstrb := strings.IndexAny(valuestring, "@") tempstre := strings.IndexAny(valuestring, ".") if tempstrb > -1 && tempstre > -1 { if v, ok := ParamMap[valuestring[tempstre+1:]]; ok { if s, ok := v.(string); ok { return s } else if s, ok := v.(float64); ok { return strconv.FormatInt(int64(s), 10) } } } } return "" } func ExecDataParam(fasion map[string]interface{}, CommonParamMap map[string]interface{}, ParamMap []map[string]interface{}, ParentParamMap map[string]interface{}, sameParamMap []map[string]interface{}, tx *xorm.Session) ([]map[string]interface{}, error) { sqlname := fasion["name"].(string) sqltype := "" //v 校验/s1 查询/sm 查询/e 执行 if v, ok := fasion["type"]; ok { sqltype = v.(string) } sql := "" p := "" s_params := make([]interface{}, 0) if sqltype != "" { sql, p = restful.GetSqlByName(sqlname, tx) //sqlApi, _ := models.GetApisqlByName(sqlname) if v, ok := fasion["parammaps"].(map[string]interface{}); ok { paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { ss := "" if ss == "" && CommonParamMap != nil { if valuestring, ok := v[strings.Trim(value, " ")].(string); ok { ss = valuestring tempstrb := strings.IndexAny(valuestring, "@") tempstre := strings.IndexAny(valuestring, ".") if (tempstre > tempstrb) && (strings.ToLower(valuestring[tempstrb+1:tempstre]) == "common") { if CommonParamMap[valuestring[tempstre+1:]] != nil { ss = CommonParamMap[valuestring[tempstre+1:]].(string) } } else { ss = "" } } } if ss == "" && ParentParamMap != nil { ss = getParamValue_(v, value, ParentParamMap) } if ss == "" && sameParamMap != nil { ss = getParamValue(v, value, sameParamMap) } if ss == "" && ParamMap != nil { ss = getParamValue(v, value, ParamMap) } s_params = append(s_params, ss) } } } } if sql == "" { sqltype = "" } if sqltype == "s1" { tempValParam, err := restful.QueryByMap(sql, 0, 1, s_params) if v, ok := tempValParam.(map[string]interface{}); ok { fasion["resultmaps"] = v["list"].([]map[string]interface{}) } if err != nil { return nil, err } err = ExitsChildrenParam(fasion, tempValParam, ParamMap, CommonParamMap, tx) if err != nil { return nil, err } return tempValParam.(map[string]interface{})["list"].([]map[string]interface{}), err } else if sqltype == "sm" { tempValParam, err := restful.QueryByMap(sql, 0, 0, s_params) if v, ok := tempValParam.(map[string]interface{}); ok { fasion["resultmaps"] = v["list"].([]map[string]interface{}) } else { fmt.Println("sm", tempValParam) } if err != nil { return nil, err } err = ExitsChildrenParam(fasion, tempValParam, ParamMap, CommonParamMap, tx) if err != nil { return nil, err } return tempValParam.(map[string]interface{})["list"].([]map[string]interface{}), err } else if sqltype == "v" { tempValParam, err := restful.QueryByMap(sql, 0, 1, s_params) if v, ok := tempValParam.(map[string]interface{}); ok { fasion["resultmaps"] = v["list"].([]map[string]interface{}) } else { fmt.Println("v", tempValParam) } if err != nil { return nil, err } if tempValParam.(map[string]interface{})["list"].([]map[string]interface{})[0]["vmsg"] != "" { return nil, errors.New(tempValParam.(map[string]interface{})["list"].([]map[string]interface{})[0]["vmsg"].(string)) } } else if sqltype == "e" { tempVal_arrary := make([]map[string]interface{}, 0) tempVal_map := make(map[string]interface{}) tempValParam, err := restful.ExecQueryT(sql, s_params, tx) if err == nil { fasion["resultmaps"] = tempValParam.(map[string]interface{}) tempVal_map["result"] = tempValParam.(map[string]interface{})["result"] tempVal_map["LastInsertId"] = tempValParam.(map[string]interface{})["LastInsertId"] tempVal_arrary = append(tempVal_arrary, tempVal_map) return tempVal_arrary, err } return nil, err } else { if tempValParam, ok := fasion["resultmaps"].(map[string]interface{}); ok { err := ExitsChildrenParam(fasion, tempValParam, ParamMap, CommonParamMap, tx) if err != nil { return nil, err } return nil, err } } return nil, nil } func ExitsChildrenParam(fasion map[string]interface{}, tempValParam interface{}, ParamMap []map[string]interface{}, commonParam map[string]interface{}, tx *xorm.Session) error { var err error subarray, ok := fasion["children"] if ok { for _, v := range tempValParam.(map[string]interface{})["list"].([]interface{}) { //遍历父集有多少行数据 tempVal_arrary := make([]map[string]interface{}, 0) for _, subarrayvalue := range subarray.([]interface{}) { //遍历所有执行命令 tempVal_ := make(map[string]interface{}) if _, ok := subarrayvalue.(map[string]interface{})["resultname"]; !ok { tempVal_["resultname"] = subarrayvalue.(map[string]interface{})["name"] } else { tempVal_["resultname"] = subarrayvalue.(map[string]interface{})["resultname"] } tempVal_["resultmaps"], err = ExecDataParam(subarrayvalue.(map[string]interface{}), commonParam, ParamMap, v.(map[string]interface{}), tempVal_arrary, tx) //递归调用执行 if err != nil { return err } tempVal_arrary = append(tempVal_arrary, tempVal_) } v.(map[string]interface{})["children"] = tempVal_arrary } } return nil } // @Summary 删除图片 // @Tags RemovePicByName // @Accept json // @Produce json // @Param sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/PostDataByName [POST] func RemovePicByName(c *gin.Context) { logging.Info("RemovePicByName ",c.Keys) appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlnamestr := fsion.Get("name").ValueStr() picname := fsion.Get("picname").ValueStr() sql, _ := restful.GetSqlByNameDB(sqlnamestr) params := fsion.Get("params").Array() s_params := make([]interface{}, 0) for _, v_params := range params { s_params = append(s_params, v_params.ValueStr()) } if sql != "" { queryData, err := execDataBySql(sql, s_params) if picname != "" { // Destination distPath := path.Join(setting.CurrentPath, setting.AppSetting.ImageSavePath, picname) err := os.Remove(distPath) //删除文件test.txt if err != nil { //如果删除失败则输出 file remove Error! fmt.Println("file remove Error!") //输出错误详细信息 fmt.Printf("%s", err) } else { //如果删除成功则输出 file remove OK! fmt.Print("file remove OK!") } distPath = path.Join(setting.CurrentPath, setting.AppSetting.ThumbnailSavePath, picname) err = os.Remove(distPath) //删除文件test.txt if err != nil { //如果删除失败则输出 file remove Error! fmt.Println("file remove Error!") //输出错误详细信息 fmt.Printf("%s", err) } else { //如果删除成功则输出 file remove OK! fmt.Print("file remove OK!") } } if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } else { appG.Response(http.StatusOK, e.SUCCESS, queryData) } } else { appG.Response(http.StatusOK, e.SUCCESS, nil) } } // @Summary 删除文件 // @Tags RemoveFileByName // @Accept json // @Produce json // @Param sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/PostDataByName [POST] func RemoveFileByName(c *gin.Context) { appG := app.Gin{C: c} id := c.Param("id") logging.Info("DownloadFile ",c.Keys,id) eqdoc, err := restful.Engine.SQL("SELECT * FROM eq_doc where id = ? ", id).QueryString() if eqdoc == nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) return } distPath := path.Join(setting.CurrentPath, setting.AppSetting.FileSavePath,eqdoc[0]["docpath"],eqdoc[0]["newdocname"]) err = os.Remove(distPath) //删除文件test.txt if err != nil { //如果删除失败则输出 file remove Error! fmt.Println("file remove Error!") //输出错误详细信息 fmt.Printf("%s", err) appG.Response(http.StatusOK, e.ERROR, err.Error()) return } fmt.Print("file remove OK!") _, err = restful.Engine.SQL("delete FROM eq_doc where id = ? ", id).QueryString() appG.Response(http.StatusOK, e.SUCCESS, "OK") //dataByte, _ := ioutil.ReadAll(c.Request.Body) //fsion := gofasion.NewFasion(string(dataByte)) //sqlnamestr := fsion.Get("name").ValueStr() //filename := fsion.Get("filename").ValueStr() //sql, _ := restful.GetSqlByNameDB(sqlnamestr) //params := fsion.Get("params").Array() //s_params := make([]interface{}, 0) //for _, v_params := range params { // s_params = append(s_params, v_params.ValueStr()) //} //if sql != "" { // queryData, err := execDataBySql(sql, s_params) // // if filename != "" { // // Destination // distPath := path.Join(setting.CurrentPath, setting.AppSetting.FileSavePath, filename) // // err := os.Remove(distPath) //删除文件test.txt // if err != nil { // //如果删除失败则输出 file remove Error! // fmt.Println("file remove Error!") // //输出错误详细信息 // fmt.Printf("%s", err) // } else { // //如果删除成功则输出 file remove OK! // fmt.Print("file remove OK!") // } // } // if err != nil { // appG.Response(http.StatusOK, e.ERROR, err.Error()) // } else { // appG.Response(http.StatusOK, e.SUCCESS, "OK") // } //} else { // appG.Response(http.StatusOK, e.SUCCESS, nil) //} } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags PostDataByName // @Accept json // @Produce json // @Param sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/PostDataByName [POST] func UpdateDataRelation(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) tablename := fsion.Get("name").ValueStr() dataname := fsion.Get("dataname").ValueStr() datavalue := fsion.Get("datavalue").ValueStr() valuename := fsion.Get("valuename").ValueStr() logging.Info("UpdateDataRelation ",c.Keys,c.Request.RemoteAddr,tablename,dataname,datavalue,valuename) values := fsion.Get("values").Array() insertSqlstr := "insert into " + tablename + "( " + dataname + "," + valuename + ") select DISTINCT ?, ? from DUAL " + " where NOT EXISTS (select " + valuename + " from " + tablename + " where " + valuename + " = ? and " + dataname + " = ?)" d_params := make([]interface{}, 4) d_params[0] = datavalue s_params := "" for _, v_values := range values { d_params[1] = v_values.ValueStr() if s_params == "" { s_params = v_values.ValueStr() } else { s_params = s_params + "," + v_values.ValueStr() } d_params[2] = d_params[1] d_params[3] = d_params[0] _, err := execDataBySql(insertSqlstr, d_params) if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) break } } if s_params != "" { deleteSqlstr := "delete from " + tablename + " " + " where " + dataname + " = " + datavalue + " and " + valuename + " not in (" + s_params + ")" queryData, err := execDataBySql(deleteSqlstr, nil) if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } else if err == nil { appG.Response(http.StatusOK, e.SUCCESS, queryData) } } else { deleteSqlstr := "delete from " + tablename + " " + " where " + dataname + " = " + datavalue queryData, err := execDataBySql(deleteSqlstr, nil) if err != nil { appG.Response(http.StatusOK, e.ERROR, err.Error()) } else if err == nil { appG.Response(http.StatusOK, e.SUCCESS, queryData) } } } // @Summary 根据APISQL表中SQL执行得到数据集 // @Tags PostDatasByName // @Accept json // @Produce json // @Param sqlname query string true "sqlname" // @Param offset query int true "offset" // @Param pagecount query int true "pagecount" // @Param returntype query string true "returntype" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/PostDatasByName [POST] func PostDataByNames(c *gin.Context) { appG := app.Gin{C: c} dataByte, _ := ioutil.ReadAll(c.Request.Body) fsion := gofasion.NewFasion(string(dataByte)) sqlParams := fsion.Array() queryData := make(map[string]interface{}) for _, v := range sqlParams { fsion2 := gofasion.NewFasion(v.Json()) logging.Info("PostDataByNames ",c.Keys,fsion2.Get("name").ValueStr()) sql, p := restful.GetSqlByNameDB(fsion2.Get("name").ValueStr()) if sql == "" { errorm := make(map[string]interface{}) errorm["code"] = e.ERROR errorm["message"] = "sql没有找到" errorm["param"] = "" queryData[fsion2.Get("name").ValueStr()] = errorm continue } else if sql != "" { s_params := make([]interface{}, 0) if fsion2.HasKey("parammaps") { parammaps := fsion2.Get("parammaps") paramslist := strings.Split(p, ",") if len(paramslist) > 0 && p != "" { for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { s_params = append(s_params, tempv.(string)) } else { s_params = append(s_params, "") } } else { s_params = append(s_params, parammaps.Get(strings.Trim(value, " ")).ValueStr()) } } } } else if fsion2.HasKey("params") { params := fsion2.Get("params").Array() for _, v_params := range params { s_params = append(s_params, v_params.ValueStr()) } } _, err := execDataBySql(sql, s_params) if err != nil { logging.Error("PostDataByName err: ",err) errorm := make(map[string]interface{}) errorm["code"] = e.ERROR errorm["message"] = err.Error() errorm["param"] = s_params queryData[fsion2.Get("name").ValueStr()] = errorm continue } else { errorm := make(map[string]interface{}) errorm["code"] = e.SUCCESS errorm["message"] = "success" queryData[fsion2.Get("name").ValueStr()] = errorm continue } } else { errorm := make(map[string]interface{}) errorm["code"] = e.ERROR errorm["message"] = "NO SQL" errorm["param"] = "" queryData[fsion2.Get("name").ValueStr()] = errorm } } appG.Response(http.StatusOK, e.SUCCESS, queryData) } func getDataBySql(sqlstr string, offset, pagecount int, returntype string, params []interface{}) (interface{}, error) { if offset > 0 { offset = offset - 1 } //if pagecount == 0 { // pagecount = setting.AppSetting.PageSize //}else if pagecount>setting.AppSetting.PageSize{ // pagecount = setting.AppSetting.PageSize //} if returntype == "list" { queryData, err := restful.QueryByList(sqlstr, offset, pagecount, params) return queryData, err } else if returntype == "listlist" { queryData, err := restful.QueryByListList(sqlstr, offset, pagecount, params) return queryData, err } else { queryData, err := restful.QueryByMap(sqlstr, offset, pagecount, params) return queryData, err } } func GetRecuData_BySql(sql string, Params string, idname string, thisParams map[string]interface{}, isList bool) (interface{}, error) { paramslist := strings.Split(Params, ",") s_params := make([]interface{}, 0) if isList { for _, v_params := range thisParams["params"].([]interface{}) { s_params = append(s_params, v_params) } } else { paramslist := strings.Split(Params, ",") if len(paramslist) > 0 && Params != "" { for _, value := range paramslist { s_params = append(s_params, thisParams[strings.Trim(value, " ")]) } } } queryData, err := restful.QueryByMapMenu(sql, 0, 0, s_params) for _, queryData_ := range queryData { if v, ok := queryData_["haschildren"]; (ok && v.(int64) > 0) || !ok { if isList { //fmt.Println(idname, "-------------- -queryData_[idname]", queryData_) if queryData_[idname] != nil { thisParams["params"].([]interface{})[len(thisParams["params"].([]interface{}))-1] = queryData_[idname].(interface{}) } } else { if queryData_[idname] != nil { thisParams[idname] = queryData_[idname].(interface{}) } if len(paramslist) > 0 && Params != "" { s_params = make([]interface{}, 0) for _, value := range paramslist { s_params = append(s_params, thisParams[strings.Trim(value, " ")]) } } } queryData_["children"], _ = GetRecuData_BySql(sql, Params, idname, thisParams, isList) } else { } } return queryData, err } func execDataBySql(sqlstr string, params []interface{}) (interface{}, error) { queryData, err := restful.ExecQuery(sqlstr, params) return queryData, err } func ExecDataBySql(sqlstr string, params []interface{}) (interface{}, error) { queryData, err := restful.ExecQuery(sqlstr, params) return queryData, err } // @Summary 导入excel文件到数据库，excel第一行作为参数 // @Tags ImportExcel // @Accept json // @Produce json // @Param file query string true "文件名" // @Param name header string true "执行sql的名字" // @Success 200 {string} json "{ "code": 200, "data": {}, "msg": "ok" }" // @Failure 400 {string} json "{"code":400, "data":null,"msg":"请求参数错误"}" // @Failure 404 {string} json "{ "code": 404, "data":null,"msg":"请求参数错误"}" // @Router /data/ImportExcel [POST] func ImportExcel(c *gin.Context) { appG := app.Gin{C: c} inputdata := "" c.Request.ParseForm() //tempv1, _ := c.Get("jwt_username") //fmt.Println("=======================",tempv1) params := c.Request.Form file, _, err := c.Request.FormFile("file") if err != nil { appG.Response(http.StatusOK, e.ERROR, err) return } sqlnamestr := params.Get("name") sheetname := params.Get("sheetname") importParams := params.Get("importParams") logging.Info("ImportExcel ",c.Keys,c.Request.RemoteAddr,sqlnamestr) sql, _ := restful.GetSqlByNameDB(sqlnamestr) paramslist := strings.Split(importParams, ",") paramslist = append(paramslist, "jwt_username") queryData := make([]interface{}, 0) xlsx1, err := excelize.OpenReader(file) if err != nil { fmt.Println(err) return } ok := 0 error := 0 rows, _ := xlsx1.GetRows(sheetname) //fmt.Println("============================",rows) for i, row := range rows { fmt.Print(i, "\t") if i > 0 { if len(paramslist) > 0 { s_params := make([]interface{}, 0) entry := make(map[string]interface{}) outentry := make(map[string]interface{}) inputdata = "" for _, value := range paramslist { if strings.ToLower(strings.Trim(value, " ")) == "jwt_username" { if tempv, exists := c.Get("jwt_username"); exists { s_params = append(s_params, tempv.(string)) } else { s_params = append(s_params, "") } } else { for j, colCell := range rows[0] { if strings.Trim(value, " ") == colCell { for k, colCell1 := range row { if j == k { outentry[colCell] = colCell1 entry[colCell] = colCell1 //inputdata = inputdata + colCell + ":" + colCell1 + ";" } } } } s_params = append(s_params, entry[strings.Trim(value, " ")]) } } outentry["input"] = inputdata // returnmsg, err := execDataBySql(sqlApi.Sqlstr, s_params) returnmsg, err := getDataBySql(sql, 0, 0, "4", s_params) returnmsgmap, _ := returnmsg.(map[string]interface{}) returnmsgmap1, _ := returnmsgmap["list"].([]map[string]interface{}) if err != nil { outentry["error_msg"] = err.Error() error++ queryData = append(queryData, outentry) } else if (len(returnmsgmap1) > 0) && (returnmsgmap1[0]["msg"] == "success") { ok++ } else { outentry["error_msg"] = returnmsgmap1[0]["msg"] error++ queryData = append(queryData, outentry) } } } } data := make(map[string]interface{}) data["result"] = queryData data["success"] = ok data["err_count"] = error appG.Response(http.StatusOK, e.SUCCESS, data) }