Ejemplo n.º 1
0
func MSSQL_to_PG(conf *SABModules.Config_STR, pg_minsert int) int {

	var (
		ckl             int
		ckl_servers     int
		num_servers     int
		ckl_que         int
		num_que         int
		queryx          string
		pg_Query_Create = []string{`
							CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
								(server character varying(255), uid bytea, idparent bytea, name character varying(255), nametr character varying(255),
									primary key (uid));
						`, `
							CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
								(server character varying(255), uid bytea, idparent bytea, name character varying(255), nametr character varying(255), idorg bytea,
									primary key (uid));
						`, `
							CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
								(server character varying(255), uid bytea, idparent bytea,
									nfr character varying(255), nfir character varying(5), nlr character varying(255), nmr character varying(255), nmir character varying(5),
									nft character varying(255), nfit character varying(5), nlt character varying(255), nmt character varying(255), nmit character varying(5),
									tab character varying(255), pos character varying(255), idorg bytea, contract integer NOT NULL,
									primary key (uid));
						`}

		pg_Query_Create_Status = string(`
							CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
								(server character varying(255), status character varying(255),
									primary key (server));
						`)

		return_result = int(0)
	)

	log.Printf("MS SQL export to PG...")

	num_servers = len(conf.MSSQL_DSN)
	num_que = len(SABDefine.MSSQL_QUE)

	dbpg, err := sql.Open("postgres", conf.PG_DSN)

	if err != nil {
		log.Printf("PG::Open() error: %v\n", err)
		return 10
	}
	defer dbpg.Close()

	queryx = strings.Replace(pg_Query_Create_Status, "XYZWorkTableZYX", SABDefine.PG_Table_MSSQL_Status, -1)
	//	log.Printf("%s\n", queryx)
	_, err = dbpg.Query(queryx)
	if err != nil {
		log.Printf("%s\n", queryx)
		log.Printf("PG::Query() Create table error: %v\n", err)
		return 11
	}

	for ckl_servers = 0; ckl_servers < num_servers; ckl_servers++ {

		db, err := sql.Open("mssql", conf.MSSQL_DSN[ckl_servers][0])
		if err != nil {
			log.Printf("MS SQL::Open() error: %v\n", err)
			continue
		} else {
			defer db.Close()

			for ckl_que = 0; ckl_que < num_que; ckl_que++ {

				log.Printf("\t\tServer %2d of %2d / Pass %2d of %2d / Server name: %s\n", ckl_servers+1, num_servers, ckl_que+1, num_que, conf.MSSQL_DSN[ckl_servers][1])

				queryx = strings.Replace(pg_Query_Create[ckl_que], "XYZWorkTableZYX", SABDefine.PG_Table_MSSQL[ckl_que], -1)
				_, err = dbpg.Query(queryx)
				if err != nil {
					log.Printf("%s\n", queryx)
					log.Printf("PG::Query() Create table error: %v\n", err)
					return 12
				}

				rows, err := db.Query(SABDefine.MSSQL_QUE[ckl_que])
				if err != nil {
					log.Printf("MS SQL::Query() error: %v\n", err)
					break
				} else {

					timenow := time.Now().Format("2006.01.02 15:04:05")

					queryx = fmt.Sprintf("INSERT INTO %s (server, status) select '%s', '%s' where not exists (select server from %s where server='%s'); update %s set status='%s' where server='%s'; ", SABDefine.PG_Table_MSSQL_Status, conf.MSSQL_DSN[ckl_servers][1], timenow, SABDefine.PG_Table_MSSQL_Status, conf.MSSQL_DSN[ckl_servers][1], SABDefine.PG_Table_MSSQL_Status, timenow, conf.MSSQL_DSN[ckl_servers][1])
					//					log.Printf("%s\n", queryx)
					_, err = dbpg.Query(queryx)
					if err != nil {
						log.Printf("%s\n", queryx)
						log.Printf("PG::Query() Create table error: %v\n", err)
						return 13
					}

					_, err = dbpg.Query(fmt.Sprintf("delete from %s where server='%s';", SABDefine.PG_Table_MSSQL[ckl_que], conf.MSSQL_DSN[ckl_servers][1]))
					if err != nil {
						log.Printf("PG::Query() Clean table error: %v\n", err)
						return 14
					}

				}

				ckl = 0

				for rows.Next() {

					if ckl < 1 {
						queryx = ""
					}

					return_result = 94

					switch ckl_que + 1 {
					case 1:
						var (
							xid       []byte
							xname     string
							xidparent []byte
							xnameq    string
						)
						rows.Scan(&xid, &xname, &xidparent)
						xname = SABModules.TextMutation(xname)
						xnameq = SABModules.TransMutation(xname, conf)
						xnametr := unidecode.Unidecode(xnameq)
						queryx = fmt.Sprintf("%sINSERT INTO %s (server, uid, idparent, name, nametr) select '%s', '%v', '%v','%s','%s' where not exists (select uid from %s where uid='%v'); ", queryx, SABDefine.PG_Table_MSSQL[ckl_que], conf.MSSQL_DSN[ckl_servers][1], xid, xidparent, xname, xnametr, SABDefine.PG_Table_MSSQL[ckl_que], xid)
					case 2:
						var (
							xid       []byte
							xidorg    []byte
							xidparent []byte
							xname     string
							xnameq    string
						)
						rows.Scan(&xid, &xidorg, &xidparent, &xname)
						xname = SABModules.TextMutation(xname)
						xnameq = SABModules.TransMutation(xname, conf)
						xnametr := unidecode.Unidecode(xnameq)
						if fmt.Sprintf("%v", xidparent) == "[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0]" {
							queryx = fmt.Sprintf("%sINSERT INTO %s (server, uid, idparent, name, nametr, idorg) VALUES ('%s', '%v', '%v','%s','%s','%v'); ", queryx, SABDefine.PG_Table_MSSQL[ckl_que], conf.MSSQL_DSN[ckl_servers][1], xid, xidorg, xname, xnametr, xidorg)
						} else {
							queryx = fmt.Sprintf("%sINSERT INTO %s (server, uid, idparent, name, nametr, idorg) VALUES ('%s', '%v', '%v','%s','%s','%v'); ", queryx, SABDefine.PG_Table_MSSQL[ckl_que], conf.MSSQL_DSN[ckl_servers][1], xid, xidparent, xname, xnametr, xidorg)
						}
					case 3:
						var (
							xid       []byte
							xname     string
							xfio      []string
							xfiotr    []string
							xtab      string
							xidorg    []byte
							xidparent []byte
							xpos      string
							xcontract int
						)
						rows.Scan(&xid, &xname, &xtab, &xidorg, &xidparent, &xpos, &xcontract)
						xfio = SABModules.PeopleMutation(xname, "RUS")
						xfiotr = SABModules.PeopleMutation(unidecode.Unidecode(xname), "LAT")
						xtab = SABModules.TextMutation(xtab)
						xpos = SABModules.PosMutation(xpos, conf)
						if len(xfio) < 3 {
							queryx = fmt.Sprintf("%sINSERT INTO %s (server, uid, idparent, nlr, nfr, nfir, nmr, nmir, nlt, nft, nfit, nmt, nmit, tab, pos, idorg, contract) VALUES  ('%s', '%v', '%v','%s','%s','%s','','','%s','%s','%s','','','%s','%s','%v',%d); ", queryx, SABDefine.PG_Table_MSSQL[ckl_que], conf.MSSQL_DSN[ckl_servers][1], xid, xidparent, xfio[0], xfio[1], xfio[1][:2], xfiotr[0], xfiotr[1], xfiotr[1][:1], xtab, xpos, xidorg, xcontract)
						} else {
							queryx = fmt.Sprintf("%sINSERT INTO %s (server, uid, idparent, nlr, nfr, nfir, nmr, nmir, nlt, nft, nfit, nmt, nmit, tab, pos, idorg, contract) VALUES  ('%s', '%v', '%v','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%v',%d); ", queryx, SABDefine.PG_Table_MSSQL[ckl_que], conf.MSSQL_DSN[ckl_servers][1], xid, xidparent, xfio[0], xfio[1], xfio[1][:2], xfio[2], xfio[2][:2], xfiotr[0], xfiotr[1], xfiotr[1][:1], xfiotr[2], xfiotr[2][:1], xtab, xpos, xidorg, xcontract)
						}
					default:
						break
					}

					if ckl >= pg_minsert-1 {
						//						log.Printf("%s\n\n", queryx)
						_, err = dbpg.Query(queryx)
						if err != nil {
							log.Printf("%s\n", queryx)
							log.Printf("PG::Query() Insert into table error: %v\n", err)
						}
						queryx = ""
						ckl = 0
					} else {
						ckl++
					}
				}

				//				log.Printf("%s\n\n", queryx)
				_, err = dbpg.Query(queryx)
				if err != nil {
					log.Printf("%s\n", queryx)
					log.Printf("PG::Query() Insert into table error: %v\n", err)
				}
			}
		}
	}

	log.Printf("\tComplete")

	return return_result

}
func Oracle_to_PG(conf *SABModules.Config_STR, pg_minsert int) int {

	var (
		ckl_servers int
		num_servers int

		ckl = int(0)

		insert_exec = int(0)

		row_comment string
		row_tm      string
		row_number  string
		row_fname   string
		queryx      string

		pg_Query_Create = string(`
						CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
							(
							server character varying(255),
							uid bytea, phone character varying(255),
							comment character varying(255),
							tm character varying(5), visible character varying(5), type integer, fname character varying(255));
						`)

		pg_Query_Create_Status = string(`
							CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
								(server character varying(255), status character varying(255),
									primary key (server));
						`)

		return_result = int(0)
	)

	log.Printf("Oracle export to PG...")

	num_servers = len(conf.Oracle_SRV)

	db, err := sql.Open("postgres", conf.PG_DSN)

	if err != nil {
		log.Printf("PG::Open() error: %v\n", err)
		return 10
	}

	defer db.Close()

	queryx = strings.Replace(pg_Query_Create_Status, "XYZWorkTableZYX", SABDefine.PG_Table_Oracle_Status, -1)

	_, err = db.Query(queryx)
	if err != nil {
		log.Printf("PG::Query() Create table error: %v\n", err)
		return 11
	}

	queryx = strings.Replace(pg_Query_Create, "XYZWorkTableZYX", SABDefine.PG_Table_Oracle, -1)

	_, err = db.Query(queryx)
	if err != nil {
		log.Printf("PG::Query() Create table error: %v\n", err)
		return 12
	}

	for ckl_servers = 0; ckl_servers < num_servers; ckl_servers++ {

		log.Printf("\t\tServer %2d of %2d / Pass  1 of  1 / Server name: %s\n", ckl_servers+1, num_servers, conf.Oracle_SRV[ckl_servers][3])

		cx, err := oracle.NewConnection(conf.Oracle_SRV[ckl_servers][0], conf.Oracle_SRV[ckl_servers][1], conf.Oracle_SRV[ckl_servers][2], false)

		if err != nil {
			log.Printf("Oracle::Connection() error: %v\n", err)
			continue
		}

		defer cx.Close()
		cu := cx.NewCursor()
		defer cu.Close()

		err = cu.Execute(SABDefine.Oracle_QUE, nil, nil)

		if err != nil {
			log.Printf("Oracle::Execute() error: %v\n", err)
			continue
		}

		rows, err := cu.FetchMany(pg_minsert)

		queryx = fmt.Sprintf("delete from %s where server='%s';", SABDefine.PG_Table_Oracle, conf.Oracle_SRV[ckl_servers][3])
		//		log.Printf("%s\n", queryx)
		_, err = db.Query(queryx)
		if err != nil {
			log.Printf("PG::Query() Clean table error: %v\n", err)
			return 13
		}

		timenow := time.Now().Format("2006.01.02 15:04:05")

		queryx = fmt.Sprintf("INSERT INTO %s (server, status) select '%s', '%s' where not exists (select server from %s where server='%s'); update %s set status='%s' where server='%s'; ", SABDefine.PG_Table_Oracle_Status, conf.Oracle_SRV[ckl_servers][3], timenow, SABDefine.PG_Table_Oracle_Status, conf.Oracle_SRV[ckl_servers][3], SABDefine.PG_Table_Oracle_Status, timenow, conf.Oracle_SRV[ckl_servers][3])
		//		log.Printf("%s\n", queryx)
		_, err = db.Query(queryx)
		if err != nil {
			log.Printf("%s\n", queryx)
			log.Printf("PG::Query() Create table error: %v\n", err)
			return 14
		}

		ckl = 0

		for err == nil && len(rows) > 0 {
			for _, row := range rows {

				if ckl < 1 {
					queryx = ""
				}

				if fmt.Sprintf("%s", row[4]) != "%!s(<nil>)" {
					row_comment = fmt.Sprintf("%s", row[4])
				} else {
					row_comment = ""
				}

				if fmt.Sprintf("%s", row[5]) != "%!s(<nil>)" {
					row_tm = fmt.Sprintf("%s", row[5])
				} else {
					row_tm = "X"
				}

				if fmt.Sprintf("%s", row[8]) != "%!s(<nil>)" {
					row_fname = SABModules.TextMutation(fmt.Sprintf("%s", row[8]))
				} else {
					row_fname = ""
				}

				if fmt.Sprintf("%s", row[1]) != "%!s(<nil>)" && fmt.Sprintf("%s", row[2]) != "%!s(<nil>)" {
					return_result = 94
					switch fmt.Sprintf("%d", row[7]) {
					case "1":
						row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
						row_number = strings.Replace(fmt.Sprintf("+7%s%s", row[1], row[2]), " ", "", -1)
						row_number = SABModules.PhoneMutation(row_number)
					case "2":
						row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
						row_number = strings.Replace(fmt.Sprintf("8%s%s", row[1], row[2]), " ", "", -1)
						row_number = SABModules.PhoneMutation(row_number)
					case "3":
						if fmt.Sprintf("%s", row[3]) != "%!s(<nil>)" {
							row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
							row[1] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[1]))
							row[2] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[2]))
							row[3] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[3]))
							row_number = strings.Replace(fmt.Sprintf("8(%s)%sдоб.%s", row[1], row[2], row[3]), " ", "", -1)
							row_number = strings.Replace(row_number, "доб.", " доб.", -1)
						} else {
							row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
							row[1] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[1]))
							row[2] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[2]))
							row_number = strings.Replace(fmt.Sprintf("8(%s)%s", row[1], row[2]), " ", "", -1)
						}
					default:
						insert_exec = 1
					}
				} else {
					insert_exec = 1
				}
				if insert_exec == 0 {
					queryx = fmt.Sprintf("%sinsert into %s (server, uid, phone, comment, tm, visible, type, fname) select '%s','%v','%s','%s','%s','%s','%d','%s' where not exists (select uid from %s where uid='%v' and phone='%s'); ", queryx, SABDefine.PG_Table_Oracle, conf.Oracle_SRV[ckl_servers][3], row[0], row_number, row_comment, row_tm, row[6], row[7], row_fname, SABDefine.PG_Table_Oracle, row[0], row_number)
					//					fmt.Printf("%s\n", queryx)
					if ckl >= pg_minsert-1 {
						//						log.Printf("%s\n\n", queryx)
						_, err = db.Query(queryx)
						if err != nil {
							log.Printf("%s\n", queryx)
							log.Printf("PG::Query() insert error: %v /// %s\n", err, queryx)
						}
						queryx = ""
						ckl = 0
					} else {
						ckl++
					}
				}
				insert_exec = 0
			}
			rows, err = cu.FetchMany(pg_minsert)
		}
		//		log.Printf("%s\n\n", queryx)
		_, err = db.Query(queryx)
		if err != nil {
			log.Printf("%s\n", queryx)
			log.Printf("PG::Query() Insert into table error: %v\n", err)
		}
	}

	log.Printf("\tComplete")

	return return_result

}
Ejemplo n.º 3
0
func LDAP_to_PG(conf *SABModules.Config_STR, pg_minsert int) int {

	var (
		ckl_servers int
		num_servers int

		fName []string
		fCN   []string
		fOUa  []string
		fOU   string
		fMail string

		pfName string
		pfCN   string
		pfMail string

		ckl   = int(0)
		state = int(0)

		queryx string

		pg_Domino_Create = string(`
						CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
							(server character varying(255),
							namerus character varying(255), trnamerus character varying(255), namelat character varying(255),
							ou character varying(255), mail character varying(255),
								primary key (mail));
						`)

		pg_Query_Create_Status = string(`
							CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
								(server character varying(255), status character varying(255),
									primary key (server));
						`)

		return_result = int(0)
	)

	log.Printf("LDAP Export to PG...")

	num_servers = len(conf.LDAP_URL)

	db, err := sql.Open("postgres", conf.PG_DSN)
	if err != nil {
		log.Printf("PG::Open() error: %v\n", err)
		return 10
	}

	defer db.Close()

	queryx = strings.Replace(pg_Domino_Create, "XYZWorkTableZYX", SABDefine.PG_Table_Domino, -1)
	_, err = db.Query(queryx)
	if err != nil {
		log.Printf("PG::Query() Create table error: %v\n", err)
		return 11
	}

	queryx = strings.Replace(pg_Query_Create_Status, "XYZWorkTableZYX", SABDefine.PG_Table_Domino_Status, -1)
	_, err = db.Query(queryx)
	if err != nil {
		log.Printf("%s\n", queryx)
		log.Printf("PG::Query() Create table error: %v\n", err)
		return 12
	}

	for ckl_servers = 0; ckl_servers < num_servers; ckl_servers++ {

		log.Printf("\t\tServer %2d of %2d / Pass  1 of  1 / Server name: %s\n", ckl_servers+1, num_servers, conf.LDAP_URL[ckl_servers][0])

		l, err := ldap.Dial("tcp", conf.LDAP_URL[ckl_servers][0])
		if err != nil {
			log.Printf("LDAP::Initialize() error: %v\n", err)
			continue
		}

		defer l.Close()
		//		l.Debug = true

		err = l.Bind(conf.LDAP_URL[ckl_servers][1], conf.LDAP_URL[ckl_servers][2])
		if err != nil {
			log.Printf("LDAP::Bind() error: %v\n", err)
			continue
		}

		search := ldap.NewSearchRequest(conf.LDAP_URL[ckl_servers][3], ldap.ScopeWholeSubtree, ldap.NeverDerefAliases, 0, 0, false, conf.LDAP_URL[ckl_servers][4], SABDefine.LDAP_attr, nil)

		sr, err := l.Search(search)
		if err != nil {
			log.Printf("LDAP::Search() error: %v\n", err)
			continue
		}

		log.Printf("\t\t\t%s // %d\n", search.Filter, len(sr.Entries))

		if len(sr.Entries) > 10 {
			timenow := time.Now().Format("2006.01.02 15:04:05")

			queryx = fmt.Sprintf("INSERT INTO %s (server, status) select '%s', '%s' where not exists (select server from %s where server='%s'); update %s set status='%s' where server='%s'; ", SABDefine.PG_Table_Domino_Status, conf.LDAP_URL[ckl_servers][0], timenow, SABDefine.PG_Table_Domino_Status, conf.LDAP_URL[ckl_servers][0], SABDefine.PG_Table_Domino_Status, timenow, conf.LDAP_URL[ckl_servers][0])
			//			log.Printf("%s\n", queryx)
			_, err = db.Query(queryx)
			if err != nil {
				log.Printf("%s\n", queryx)
				log.Printf("PG::Query() Create table error: %v\n", err)
				return 14
			}

			_, err = db.Query(fmt.Sprintf("delete from %s where server='%s';", SABDefine.PG_Table_Domino, conf.LDAP_URL[ckl_servers][0]))
			if err != nil {
				log.Printf("PG::Query() Clean table error: %v\n", err)
				return 14
			}

		}

		ckl = 0

		for _, entry := range sr.Entries {

			if ckl < 1 {
				queryx = ""
			}

			for _, attr := range entry.Attributes {
				if attr.Name == "altfullname" {
					x := strings.Join(attr.Values, ",")
					fOUa = strings.Split(x, ",")
					fName = strings.Split(fOUa[0], "=")
				}
				if attr.Name == "cn" {
					x := strings.Join(attr.Values, ",")
					fCN = strings.Split(x, ",")
				}
				if attr.Name == "mail" {
					fMail = strings.Join(attr.Values, ",")
				}
			}

			if len(fName) > 0 && len(fCN) > 0 {

				return_result = 94

				if ckl > 0 && state == 1 {
					queryx = fmt.Sprintf("%s , ", queryx)
				}
				fOU = ""
				for ckl1 := int(len(fOUa) - 2); ckl1 > 0; ckl1-- {
					fOU = fmt.Sprintf("%s/%s", fOU, fOUa[ckl1])
				}
				fOU = strings.Trim(strings.Trim(strings.Replace(strings.Replace(fOU, "OU=", "", -1), "O=", "", -1), "/"), " ")
				if fOU == "" {
					fOU = conf.ROOT_OU
				}
				pfName = SABModules.TextMutation(strings.Replace(fName[int(len(fName)-1)], "'", "", -1))
				pfCN = SABModules.TextMutation(strings.Replace(fCN[0], "'", "", -1))
				pfMail = strings.Replace(strings.ToLower(fMail), " ", "", -1)
				queryx = fmt.Sprintf("%sINSERT INTO %s (server, namerus, trnamerus,  namelat, ou, mail) select '%s','%s','%s','%s','%s','%s' where not exists (select mail from %s where mail='%s'); ", queryx, SABDefine.PG_Table_Domino, conf.LDAP_URL[ckl_servers][0], pfName, unidecode.Unidecode(pfName), pfCN, fOU, pfMail, SABDefine.PG_Table_Domino, pfMail)
				//				log.Printf("%s", queryx)
				//				state = 1
			}

			if ckl >= pg_minsert-1 {
				//				log.Printf("%s\n\n", queryx)
				_, err = db.Query(queryx)
				if err != nil {
					log.Printf("%s\n", queryx)
					log.Printf("PG::Query() insert error: %v /// %s\n", err, queryx)
				}
				queryx = ""
				ckl = 0
				//				state=0
			} else {
				ckl++
			}
		}
		//		log.Printf("%s\n\n", queryx)
		_, err = db.Query(queryx)
		if err != nil {
			log.Printf("%s\n", queryx)
			log.Printf("PG::Query() Insert into table error: %v\n", err)
		}

	}

	log.Printf("\tComplete")

	return return_result

}