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
}
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
}