techsuppgetinfo/DCIManager6/dci6-support/platform_info_fromDB.go

package main

import (
	"fmt"
	"log"
	"strconv"
	"strings"
)

func getDciLocationDB(ispDbName string, runningSql runningSqlServer) ([]dciLocation, []error) {
	dbName := "dci_1"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	query := "SELECT id, status, COALESCE(status_info, 'null') as status_info, name, ssh_address, ssh_port, ssh_user, COALESCE(setup_info, 'null') as setup_info, COALESCE(params_dhcp, 'null') as params_dhcp, COALESCE(params_redis, 'null') as params_redis, COALESCE(params_nginx, 'null') as params_nginx, COALESCE(params_netflow, 'null') as params_netflow, COALESCE(settings, 'null') as settings, CASE WHEN is_main THEN 1 ELSE 0 END as is_main, COALESCE(proxy_params, '') as proxy_params, COALESCE(userspace, 0) as userspace, COALESCE(patch, 'null') as patch FROM dci_location;"

	var errs []error
	var dl []dciLocation

	if runningSql.Type == "mysql" {
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.dci_location : %s", dbName, err.Error())
			errs = append(errs, err)
			return dl, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var dciLoc dciLocation
			err = rows.Scan(&dciLoc.Id, &dciLoc.Status, &dciLoc.StatusInfo, &dciLoc.Name, &dciLoc.SshAddress, &dciLoc.SshPort, &dciLoc.SshUser, &dciLoc.SetupInfo, &dciLoc.ParamsDhcp, &dciLoc.ParamsRedis, &dciLoc.ParamsNginx, &dciLoc.ParamsNetflow, &dciLoc.Settings, &dciLoc.IsMain, &dciLoc.ProxyParams, &dciLoc.Userspace, &dciLoc.Patch)
			if err != nil {
				err = fmt.Errorf("Ошибка сканирования результатов запроса в структуру dciLocation : %s", err.Error())
				errs = append(errs, err)
				continue
			}
			dl = append(dl, dciLoc)
		}
	} else if runningSql.Type == "pgsql" {
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)
		// dbName dci_1

		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.dci_location : %s", dbName, err.Error())
			errs = append(errs, err)
			return dl, errs
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var dciLoc dciLocation
			err = rows.Scan(&dciLoc.Id, &dciLoc.Status, &dciLoc.StatusInfo, &dciLoc.Name, &dciLoc.SshAddress, &dciLoc.SshPort, &dciLoc.SshUser, &dciLoc.SetupInfo, &dciLoc.ParamsDhcp, &dciLoc.ParamsRedis, &dciLoc.ParamsNginx, &dciLoc.ParamsNetflow, &dciLoc.Settings, &dciLoc.IsMain, &dciLoc.ProxyParams, &dciLoc.Userspace, &dciLoc.Patch)
			if err != nil {
				err = fmt.Errorf("Ошибка сканирования результатов запроса в структуру dciLocation : %s", err.Error())
				errs = append(errs, err)
				continue
			}
			dl = append(dl, dciLoc)
		}
	}
	return dl, errs
}

func getDciLocationComposeDB(ispDbName string, runningSql runningSqlServer) ([]dciLocationDockerCompose, []error) {
	dbName := "dci_1"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	var errs []error
	var dlc []dciLocationDockerCompose
	query := "SELECT id,name,docker_compose FROM dci_location;"

	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.dci_location : %s", dbName, err.Error())
			errs = append(errs, err)
			return dlc, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var dciLocCompose dciLocationDockerCompose
			err = rows.Scan(&dciLocCompose.Id, &dciLocCompose.Name, &dciLocCompose.DockerCompose)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру dciLocationDockerCompose : %s", err.Error())
			}
			dlc = append(dlc, dciLocCompose)
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.dci_location : %s", dbName, err.Error())
			errs = append(errs, err)
			return dlc, errs
		}
		defer rows.Close()
		defer pool.Close()
		for rows.Next() {
			var dciLocCompose dciLocationDockerCompose
			err = rows.Scan(&dciLocCompose.Id, &dciLocCompose.Name, &dciLocCompose.DockerCompose)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру dciLocationDockerCompose : %s", err.Error())
			}
			dlc = append(dlc, dciLocCompose)
		}
	}
	return dlc, errs
}

func getDciTaskMonthErrorsDB(ispDbName string, runningSql runningSqlServer) ([]taskManagerTask, []error) {
	var tm []taskManagerTask
	var errs []error
	dbName := "auth"
	if ispDbName == "isp" {
		dbName = ispDbName
	}

	switch runningSql.Type {
	case "mysql":
		query := "SELECT id,name,registration_time,request_info,output,status FROM taskmgr_task WHERE status != 'complete' AND registration_time >= NOW() - INTERVAL 1 MONTH;"
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.taskmgr_task : %s", dbName, err.Error())
			errs = append(errs, err)
			return tm, errs
		}
		defer rows.Close()
		defer db.Close()
		for rows.Next() {
			var taskManagerFailed taskManagerTask
			err = rows.Scan(&taskManagerFailed.Id, &taskManagerFailed.Name, &taskManagerFailed.RegistrationTime, &taskManagerFailed.RequestInfo, &taskManagerFailed.Output, &taskManagerFailed.Status)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру taskManagerFailed : %s", err.Error())
			}
			tm = append(tm, taskManagerFailed)
		}
	case "pgsql":
		query := "SELECT id,name,registration_time,request_info,output,status FROM taskmgr_task WHERE status != 'complete' AND registration_time >= NOW() - INTERVAL '1 MONTH';"
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.taskmgr_task : %s", dbName, err.Error())
			errs = append(errs, err)
			return tm, errs
		}
		defer rows.Close()
		defer pool.Close()
		for rows.Next() {

			var taskManagerFailed taskManagerTask
			err = rows.Scan(&taskManagerFailed.Id, &taskManagerFailed.Name, &taskManagerFailed.RegistrationTime, &taskManagerFailed.RequestInfo, &taskManagerFailed.Output, &taskManagerFailed.Status)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру taskManagerFailed : %s", err.Error())
			}
			log.Println(taskManagerFailed.Id)
			tm = append(tm, taskManagerFailed)
		}
	}

	return tm, errs
}

func getDciLocationHWCountDB(ispDbName string, runningSql runningSqlServer, platformStruct outputPlatformStruct) ([]hwByLocation, []error) {
	var hw []hwByLocation
	var errs []error
	dbName := "dci_1"
	if ispDbName == "isp" {
		dbName = ispDbName
	}

	// dbName dci_1
	for _, loc := range platformStruct.DciLocations {
		locId := strconv.Itoa(loc.Id)
		query := "select (SELECT COUNT(id) FROM dci_server WHERE location = " + locId + ") AS server, (SELECT COUNT(id) FROM dci_switch WHERE location = " + locId + ") AS switch, (SELECT count(id) FROM dci_pdu WHERE location = " + locId + ") AS pdu, (SELECT COUNT(id) FROM dci_ups WHERE location = " + locId + ") AS ups;"
		switch runningSql.Type {
		case "mysql":
			rows, db, err := mysqlQuery(query, dbName, runningSql)

			if err != nil {
				err = fmt.Errorf("Ошибка выполнения запроса к таблицам с оборудованиям : %s", err.Error())
				errs = append(errs, err)
				return hw, errs
			}
			defer db.Close()
			defer rows.Close()
			for rows.Next() {
				var hwByLocation hwByLocation
				err = rows.Scan(&hwByLocation.Server, &hwByLocation.Switch, &hwByLocation.Pdu, &hwByLocation.Ups)
				if err != nil {
					err = fmt.Errorf("Ошибка записи в структуру hwByLocation: %s", err.Error())
				}
				hwByLocation.Location = loc.Id
				hw = append(hw, hwByLocation)
			}
		case "pgsql":
			rows, pool, err := pgsqlQuery(query, dbName, runningSql)
			if err != nil {
				err = fmt.Errorf("Ошибка выполнения запроса к таблицам с оборудованием : %s", err.Error())
				errs = append(errs, err)
				return hw, errs
			}
			defer pool.Close()
			defer rows.Close()
			for rows.Next() {
				var hwByLocation hwByLocation
				err = rows.Scan(&hwByLocation.Server, &hwByLocation.Switch, &hwByLocation.Pdu, &hwByLocation.Ups)
				if err != nil {
					err = fmt.Errorf("Ошибка записи в структуру hwByLocation: %s", err.Error())
				}
				hwByLocation.Location = loc.Id
				hw = append(hw, hwByLocation)
			}
		}
	}
	return hw, errs
}

func getDciPluginsDB(ispDbName string, runningSql runningSqlServer) ([]installedPlugin, []error) {
	var ip []installedPlugin
	var errs []error
	dbName := "auth"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	query := "SELECT COALESCE(name, 'null'), COALESCE(status, 'null'), COALESCE(version, 'null'), COALESCE(current_version, 'null') from ps_plugin;"
	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.ps_plugin : %s", dbName, err.Error())
			errs = append(errs, err)
			return ip, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var instPlug installedPlugin
			err = rows.Scan(&instPlug.Name, &instPlug.Status, &instPlug.Version, &instPlug.CurrentVersion)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру installedPlugin : %s", err.Error())
				errs = append(errs, err)
			}
			ip = append(ip, instPlug)
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.ps_plugin : %s", dbName, err.Error())
			errs = append(errs, err)
			return ip, errs
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var instPlug installedPlugin
			err = rows.Scan(&instPlug.Name, &instPlug.Status, &instPlug.Version, &instPlug.CurrentVersion)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру installedPlugin : %s", err.Error())
				errs = append(errs, err)
			}
			ip = append(ip, instPlug)
		}
	}
	return ip, errs
}

func getDciUsersCountDB(ispDbName string, runningSql runningSqlServer) (users, error) {
	var u users
	var err error
	dbName := "auth"
	if ispDbName == "isp" {
		dbName = ispDbName
	}

	query := "SELECT COUNT(id) FROM auth_user;"
	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.auth_user : %s", dbName, err.Error())
			return u, err
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			err = rows.Scan(&u.Count)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру users")
				if rows != nil {
					rows.Close()
				}
				return u, err
			}
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к таблице %s.auth_user : %s", dbName, err.Error())
			return u, err
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			err = rows.Scan(&u.Count)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру users")
				if rows != nil {
					rows.Close()
				}
				return u, err
			}
		}
	}
	return u, err
}

func getDciLdapDB(ispDbName string, runningSql runningSqlServer) ([]ldap, []error) {
	var ldaps []ldap
	var errs []error
	dbName := "auth"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	query := "SELECT id,name,last_sync FROM auth_ldap;"
	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка при выполнении запроса к %s.auth_ldap : %s", dbName, err.Error())
			errs = append(errs, err)
			return ldaps, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var ldap ldap
			err = rows.Scan(&ldap.Id, &ldap.Name, &ldap.LastSync)
			if err != nil {
				err = fmt.Errorf("Ошибка при записи структуры ldap : %s", err.Error())
				errs = append(errs, err)
			}
			ldaps = append(ldaps, ldap)
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка при выполнении запроса к %s.auth_ldap : %s", dbName, err.Error())
			errs = append(errs, err)
			return ldaps, errs
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var ldap ldap
			err = rows.Scan(&ldap.Id, &ldap.Name, &ldap.LastSync)
			if err != nil {
				err = fmt.Errorf("Ошибка при записи структуры ldap : %s", err.Error())
				errs = append(errs, err)
			}
			ldaps = append(ldaps, ldap)
		}
	}
	return ldaps, errs
}

func getDciRepoDB(ispDbName string, runningSql runningSqlServer) ([]repo, []error) {
	var repos []repo
	var errs []error
	query := "SELECT id,name,url FROM dci_repo"
	dbName := "dci_1"
	if ispDbName == "isp" {
		dbName = ispDbName
	}

	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к %s.dci_repo : %s", dbName, err.Error())
			errs = append(errs, err)
			return repos, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var repo repo
			err = rows.Scan(&repo.Id, &repo.Name, &repo.Url)
			if err != nil {
				err = fmt.Errorf("Ошибка запписи в структуру repo : %s", err.Error())
				errs = append(errs, err)
			}
			repos = append(repos, repo)
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка выполнения запроса к %s.dci_repo : %s", dbName, err.Error())
			errs = append(errs, err)
			return repos, errs
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var repo repo
			err = rows.Scan(&repo.Id, &repo.Name, &repo.Url)
			if err != nil {
				err = fmt.Errorf("Ошибка запписи в структуру repo : %s", err.Error())
				errs = append(errs, err)
			}
			repos = append(repos, repo)
		}
	}
	return repos, errs
}

func getDciOsTemplatesDB(ispDbName string, runningSql runningSqlServer, platformStruct outputPlatformStruct) ([]osTemplate, []error) {
	var ispRepSlice []string
	for _, r := range platformStruct.Repos {
		if strings.Contains(r.Url, "download.ispsystem.com") {
			ispRepSlice = append(ispRepSlice, strconv.Itoa(r.Id))
		}
	}
	ispRep := strings.Join(ispRepSlice, ",")
	var osTemplates []osTemplate
	var errs []error
	query := "SELECT id,name,filename,macro,metainfo,directory_name,repository,size from dci_os_template where repository not in (" + ispRep + ");"
	dbName := "dci_1"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка при выполнении запроса к %s.dci_os_template : %s", dbName, err.Error())
			errs = append(errs, err)
			return osTemplates, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var ostempl osTemplate
			err = rows.Scan(&ostempl.Id, &ostempl.Name, &ostempl.Filename, &ostempl.Macro, &ostempl.Metainfo, &ostempl.DirectoryName, &ostempl.Repository, &ostempl.Size)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру osTemplate : %s", err.Error())
				errs = append(errs, err)
			}
			osTemplates = append(osTemplates, ostempl)
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка при выполнении запроса к %s.dci_os_template : %s", dbName, err.Error())
			errs = append(errs, err)
			return osTemplates, errs
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var ostempl osTemplate
			err = rows.Scan(&ostempl.Id, &ostempl.Name, &ostempl.Filename, &ostempl.Macro, &ostempl.Metainfo, &ostempl.DirectoryName, &ostempl.Repository, &ostempl.Size)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру osTemplate : %s", err.Error())
				errs = append(errs, err)
			}
			osTemplates = append(osTemplates, ostempl)
		}
	}

	return osTemplates, errs
}

func getDciRealIpDB(ispDbName string, runningSql runningSqlServer) (realIP, error) {
	dbName := "dci_1"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	var ri realIP
	var err error
	query := "SELECT name, value FROM isp_settings WHERE name = 'trusted_servers';"

	switch runningSql.Type {
	case "mysql":
		// TODO Тут важно проверить, что это хранится в таблице isp_settings в базе dci_1, а не auth Эта таблица есть в обеих базах и там разные настройки
		rows, db, err := mysqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка запроса к %s.isp_settings : %s", dbName, err.Error())
			return ri, err
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var ri realIP
			err = rows.Scan(&ri.Name, &ri.Value)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру realIP : %s", err.Error())
			}
		}

	case "pgsql":
		// TODO Тут важно проверить, что это хранится в таблице isp_settings в базе dci_1, а не auth Эта таблица есть в обеих базах и там разные настройки
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка запроса к %s.isp_settings : %s", dbName, err.Error())
			return ri, err
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var ri realIP
			err = rows.Scan(&ri.Name, &ri.Value)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру realIP : %s", err.Error())
			}
		}
	}

	return ri, err
}

func getDciBackupTask(ispDbName string, runningSql runningSqlServer) ([]backupTask, []error) {
	var bts []backupTask
	var errs []error
	dbName := "auth"
	if ispDbName == "isp" {
		dbName = ispDbName
	}
	query := "SELECT id,enabled,cron_expression,limit_count,limit_size_mib FROM backup_task;"

	switch runningSql.Type {
	case "mysql":
		rows, db, err := mysqlQuery(query, dbName, runningSql)
		if err != nil {
			err = fmt.Errorf("Ошибка запроса к %s.backup_task : %s", dbName, err)
			errs = append(errs, err)
			return bts, errs
		}
		defer db.Close()
		defer rows.Close()
		for rows.Next() {
			var bt backupTask
			err = rows.Scan(&bt.Id, &bt.Enabled, &bt.CronExpression, &bt.LimitCount, &bt.LimitSizeMib)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру backupTask : %s", err.Error())
			}
			bts = append(bts, bt)
		}
	case "pgsql":
		rows, pool, err := pgsqlQuery(query, dbName, runningSql)

		if err != nil {
			err = fmt.Errorf("Ошибка запроса к %s.backup_task : %s", dbName, err)
			errs = append(errs, err)
			return bts, errs
		}
		defer pool.Close()
		defer rows.Close()
		for rows.Next() {
			var bt backupTask
			err = rows.Scan(&bt.Id, &bt.Enabled, &bt.CronExpression, &bt.LimitCount, &bt.LimitSizeMib)
			if err != nil {
				err = fmt.Errorf("Ошибка записи в структуру backupTask : %s", err.Error())
			}
			bts = append(bts, bt)
		}
	}
	return bts, errs
}