go liteIDE

 go  liteIDE

 

1 COMM FILE

package pricetable

import (
    "fmt"
    "math"
    "os"
    "strings"
)

//func ContainMap(target string, ranges map[string]string) bool {
//    var flag bool = false
//    for k, _ := range ranges {
//        if k == target {
//            flag = true
//        }
//    }
//    return flag
//}

func ContainArray(target string, ranges []string) bool {
    var flag bool = false
    for _, v := range ranges {
        if v == target {
            flag = true
        }
    }
    return flag
}
func FilterString(feild string) string {

    if strings.Index(feild, "CONSULT DUNHAM BUSH") != -1 {
        feild = "-88888"
    }
    if strings.Index(feild, "Contact factory") != -1 {
        feild = "-88888"
    }
    if strings.Index(feild, "contact DBY") != -1 {
        feild = "-88888"
    }

    if strings.Index(feild, "Consult factory") != -1 {
        feild = "-88888"
    }
    if strings.Index(feild, "Consult DBM") != -1 {
        feild = "-33333"
    }
    if strings.ToUpper(feild) == "STD" {
        feild = "-66666"
    }
    if strings.ToUpper(feild) == "N/A" {
        feild = "-77777"
    }

    return feild
}

func Exist(filename string) bool {
    _, err := os.Stat(filename)
    return err == nil || os.IsExist(err)
}

func CheckfileExistAndDelete(path string, filename string) {

    if Exist(path + filename) {

        errs := os.Remove(path + filename)
        if errs == nil {
            fmt.Println("delete old file :" + path + filename + " success !")
        } else {
            fmt.Printf("Error: %s\n", errs)
        }
    } else {
        fmt.Println("file:" + path + filename + " not exist,not need to delete !")
    }
}

func Writertofile(content string, filename string, path string) {

    fil, err := os.OpenFile(path+filename, os.O_RDWR|os.O_APPEND, 0420)
    //fmt.Println(path + filename)
    if err != nil {
        //fmt.Printf("Error: %s\n", err)
        filnew, errnew := os.Create(filename)
        if errnew != nil {
            fmt.Printf("Error: %s\n", errnew)
            filnew.Close()
            return
        } else {
            fmt.Println(path + filename + " Create success !")
            fil = filnew
        }

    }
    defer fil.Close()
    fil.WriteString(content)
}

func CheckErr(err error) {
    if err != nil {
        panic(err)
    }
}

func MyRound(f float64, n int) float64 {
    pow10_n := math.Pow10(n)
    return math.Trunc((f+0.5/pow10_n)*pow10_n) / pow10_n
}

//func strings.TrimLeft(" !!! Achtung !!! ", "! ")
func Substr(str string, start, length int) string {
    rs := []rune(str)
    rl := len(rs)
    end := 0

    if start < 0 {
        start = rl - 1 + start
    }
    end = start + length

    if start > end {
        start, end = end, start
    }

    if start < 0 {
        start = 0
    }
    if start > rl {
        start = rl
    }
    if end < 0 {
        end = 0
    }
    if end > rl {
        end = rl
    }

    return string(rs[start:end])
}

//func main() {
//    writertofile("content string??", "1filenamestring.sql", "/")
//}

2 TABLE

package pricetable

type TableStruct struct {
    TableName   string
    TableColums []string
}

func GetTablea() *TableStruct {
    ts := new(TableStruct)
    ts.TableName = "Tablea"
    ts.TableColums = []string{"acolumA", "acolumB"}
    return ts
}

func GetTableb() *TableStruct {
    ts := new(TableStruct)
    ts.TableName = "Tableb"
    ts.TableColums = []string{"bcolumA", "bcolumB"}
    return ts
}

func GetTable_my_dbair2() *TableStruct {
    ts := new(TableStruct)
    ts.TableName = "my_dbair2"
    ts.TableColums = []string{
        "Basic 380-415/3ph/50Hz  (AU )",
        "Basic 460V/3ph/60Hz (Non-UL) (AR )",
        "Drain Pan (Stainless Steel)",
        "Discharge Plenum (Upflow only)",
        "Steam Generating Humidifier",
        "Hot Gas Bypass DX System Only",
        "Liquid Line Solenoid Valve -DX Systems Only (50Hz)",
        "Liquid Line Solenoid Valve -DX Systems Only (60Hz)",
        "Panel Insulation (Insulflex)",
        "Panel Insulation (Double Skin)",
        "SKD (Complete built up unit, piping joints to be brazed by othe",
        "Variable Frequency Drive (VFD) for Evaporator Blower Motor",
        "UnderFloor Water Detector (PinHole Probe)",
        "Smoke Detector",
        "EEV (50Hz/60Hz)",
        "BMS",
        "Low Ambient Kit to 7°C/45F (at design ambient 95F)",
        "Low Ambient Kit to 0°C/32F (at design ambient 95F)",
        "Low Ambient Kit to -10°C/14F (at design ambient 95F)",
        "Low Ambient Kit to 7°C/45F (at design ambient 105F)",
        "Low Ambient Kit to 0°C/32F (at design ambient 105F)",
        "Low Ambient Kit to -10°C/14F (at design ambient 105F)",
        "Coil Fin - Hydrophilic",
        "Coil Fin - Copper",
        "Black Color Epoxy Powder Coating (In lieu of Std Beige Color)  ",
    }

    return ts
}

//====================for ACPSB==================
func Gettable_my_6acpsb_p() *TableStruct {
    ts := new(TableStruct)
    ts.TableName = "my_6ACPSB_P"
    ts.TableColums = []string{
        //"unit_name",
        //"model",
        "basic-R22",
        "basic-R407C",
        "INS0.5",
        "INS1",
        "DWAL",
        "LAC1",
        "SV",
        "HGBP",
        "HWC",
        "DOL1",
        "DOL2",
        "MII",
        "CTLR",
        "ADS-C",
        "ADS-E",
        "HYD-E",
        "HYD-C",
        "SPG",
        "BotR/S",
        "LIGHT",
        "24VAC",
        "SSB",
        "FR",
    }

    return ts
}

 

3 MAIN

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    "pricetable"
    "strconv"
    "strings"
)

const (
    FILEPATH    = "D:/test/pricetable/"
    DB_USER     = "ecatalog"
    DB_PASSWORD = "Zx27yeA"
    DB_NAME     = "spec7"
    DB_HOST     = "spec2.dunham-bush.com"
    DB_PORT     = "5433"
)

func main02() {
    //for acpsb
    //callForACPSB()

    //for accs
    //callForACCS()

    //for wcps
    //callForWCPS()

    //for ACPSE50HZ
    //callForACPSE50HZ()

    // for ACPSE60HZ
    //callForACPSE60HZ()

    // for Acdsr410a
    callForAcdsr410a()

}

//=============================for Acdsr410a=========start==============
func callForAcdsr410a() {
    //Start
    fmt.Println("# Execute Start !")

    //get model

    ts := pricetable.GetTable_my_pmec_acds_r410_prices()

    //check file make sure file is new
    pricetable.CheckfileExistAndDelete(FILEPATH, ts.TableName+".sql")

    //get db connection
    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)

    pricetable.CheckErr(err)
    defer db.Close()

    queryAcdsr410a(ts, db)

    //Print Execute finished info
    fmt.Println("# Execute End !")
}
func queryAcdsr410a(ts *pricetable.TableStruct, db *sql.DB) {

    tbname := ts.TableName
    tbcolums := ts.TableColums

    fmt.Println("# Querying")
    //fmt.Println(len(tablecolums))
    for i, v := range tbcolums {

        //test sentence
        if false {
            fmt.Println(i, v)
        }

        //combin query sql
        qstr := "SELECT  \"model_id\",\"" + v + "\"  FROM my_" + strings.TrimLeft(tbname, "my_")
        fmt.Println(qstr)
        rows, err := db.Query(qstr)
        pricetable.CheckErr(err)

        for rows.Next() {

            var Model_id string
            var val string

            err = rows.Scan(&Model_id, &val)
            pricetable.CheckErr(err)

            if val == "N/A" {
                continue
            }

            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {
                if v == "CE2_21" {
                    vv = val
                } else {
                    //do float round
                    vv = strconv.FormatFloat(pricetable.MyRound(f, 0), 'f', 0, 32)

                }

            } else {
                vv = val
            }

            //select * from price_constants
            //filter fields
            vv = pricetable.FilterString(vv)

            var pixstr string = "insert into " + strings.TrimLeft(tbname, "my_") + "  (\"pid\", \"model_id\",\"price_option\", \"y2013\") VALUES ("
            contents := pixstr + "(select \"max\"(pid)  from " + strings.TrimLeft(tbname, "my_") + ")+1" + " ,'" + strings.Trim(Model_id, " ") + "','" + strings.Trim(v, " ") + "','" + vv + "' );\n"

            // update sql
            //var contents string = "update " + strings.TrimLeft(tbname, "my_") + " set y2013='" + vv + "' where \"model_id\"='" + strings.Trim(Model_id, " ") + "' and  \"price_option\"='" + strings.Trim(v, " ") + "';\n"
            //update pmec_acds_r410_prices set y2013='7501.92' WHERE     "model_id" = '1' and  "price_option" = 'ST1'

            //test print to console
            fmt.Println(contents)

            //writer to file
            pricetable.Writertofile(contents, tbname+".sql", FILEPATH)
        }
    }

}

//=============================for Acdsr410a=========END==============

//=============================for ACPSE60HZ=========start==============
func callForACPSE60HZ() {
    //Start
    fmt.Println("# Execute Start !")

    //get model
    ts := pricetable.GetTable_my_6acpse_pg_r410a_60hz_cooling()
    //ts := pricetable.GetTable_my_6acpse_phpg_r410a_heat_pump()

    //check file make sure file is new
    pricetable.CheckfileExistAndDelete(FILEPATH, ts.TableName+".sql")

    //get db connection
    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)

    pricetable.CheckErr(err)
    defer db.Close()

    //460V/3ph/60Hz (non-UL)    -AR
    //230V/3ph/60Hz (non-UL)    -AN
    //208V/3ph/60Hz (non-UL)    -CD
    //380V/3ph/60Hz (non-UL)    -CB

    var voltages = []string{"-AR", "-AN", "-CD", "-CB"}

    for _, v := range voltages {
        queryACPSE60HZ(ts, db, v)
    }

    //Print Execute finished info
    fmt.Println("# Execute End !")
}
func queryACPSE60HZ(ts *pricetable.TableStruct, db *sql.DB, voltage string) {

    tbname := ts.TableName
    tbcolums := ts.TableColums

    fmt.Println("# Querying")
    //fmt.Println(len(tablecolums))
    for i, v := range tbcolums {

        //test sentence
        if false {
            fmt.Println(i, v)
        }

        //combin query sql
        qstr := "SELECT  \"model\",\"" + v + "\"  FROM my_" + strings.TrimLeft(tbname, "my_")
        //fmt.Println(qstr)
        rows, err := db.Query(qstr)
        pricetable.CheckErr(err)

        for rows.Next() {

            var Model string
            var val string

            err = rows.Scan(&Model, &val)
            pricetable.CheckErr(err)

            // if option is endwith in range of voltages and is which
            flag, suf := voltageSuffix(v)
            if flag && suf != voltage {
                continue
            }

            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {

                //do float round
                vv = strconv.FormatFloat(pricetable.MyRound(f, 0), 'f', 0, 32)
            } else {
                vv = val
            }

            //select * from price_constants
            //filter fields
            vv = pricetable.FilterString(vv)

            //("pid", "unit_name", "model", "refrigerant", "price_option", "y2013", "guid")
            var pixstr string = "insert into " + tbname + "  (\"pid\", \"model\", \"price_option\", \"y2013\", \"guid\") VALUES ("

            contents := pixstr + "(select \"max\"(pid)  from " + tbname + ")+1" + " ,\"" + strings.Trim(Model, " ") + "\",\"" + strings.Trim(v, " ") + "\",\"" + vv + "\" , (select \"max\"(guid)  from " + tbname + ")+1 ;\n"

            //test print to console
            //fmt.Println(contents)

            //writer to file
            pricetable.Writertofile(contents, tbname+".sql", FILEPATH)
        }
    }

}

func voltageSuffix(vv string) (bool, string) {

    var flag bool = false
    var suf string = ""

    var voltages = []string{"-AR", "-AN", "-CD", "-CB"}
    for _, v := range voltages {
        if strings.HasSuffix(vv, v) {
            flag = true
            suf = v
            break
        }
    }
    return flag, suf
}

//=============================for ACPSE60HZ=========start==============

//=============================for ACPSE50HZ=========start==============
func callForACPSE50HZ() {
    //Start
    fmt.Println("# Execute Start !")

    //get model

    ts := pricetable.Gettable_my_acpse_pg_r410a_50hz_cooling()
    //ts := pricetable.Gettable_my_acpse_phpg_r410a_50hz_heat_pump()

    //check file make sure file is new
    pricetable.CheckfileExistAndDelete(FILEPATH, ts.TableName+".sql")

    //get db connection
    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)

    pricetable.CheckErr(err)
    defer db.Close()

    queryACPSE50HZ(ts, db)

    //Print Execute finished info
    fmt.Println("# Execute End !")
}
func queryACPSE50HZ(ts *pricetable.TableStruct, db *sql.DB) {

    tbname := ts.TableName
    tbcolums := ts.TableColums

    fmt.Println("# Querying")
    //fmt.Println(len(tablecolums))
    for i, v := range tbcolums {

        //test sentence
        if false {
            fmt.Println(i, v)
        }

        //combin query sql
        qstr := "SELECT  \"model\",\"" + v + "\"  FROM my_" + strings.TrimLeft(tbname, "my_")
        fmt.Println(qstr)
        rows, err := db.Query(qstr)
        pricetable.CheckErr(err)

        for rows.Next() {

            var Model string
            var val string

            err = rows.Scan(&Model, &val)
            pricetable.CheckErr(err)

            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {

                //do float round
                vv = strconv.FormatFloat(pricetable.MyRound(f, 0), 'f', 0, 32)
            } else {
                vv = val
            }

            //select * from price_constants
            //filter fields
            vv = pricetable.FilterString(vv)

            //("pid", "unit_name", "model", "refrigerant", "price_option", "y2013", "guid")
            var pixstr string = "insert into " + tbname + "  (\"pid\", \"model\", \"price_option\", \"y2013\", \"guid\") VALUES ("

            contents := pixstr + "(select \"max\"(pid)  from " + tbname + ")+1" + " ,\"" + strings.Trim(Model, " ") + "\",\"" + strings.Trim(v, " ") + "\",\"" + vv + "\" , (select \"max\"(guid)  from " + tbname + ")+1 ;\n"

            //test print to console
            //fmt.Println(contents)

            //writer to file
            pricetable.Writertofile(contents, tbname+".sql", FILEPATH)
        }
    }

}

//=============================for ACPSE50HZ=========END==============

//=============================for WCPS=========start==============
func callForWCPS() {
    //Start
    fmt.Println("# Execute Start !")

    //get model

    //ts := pricetable.Gettable_my_6wcps_ch()
    //ts := pricetable.Gettable_my_6wcpsb()
    //ts := pricetable.Gettable_my_wcps_ch()
    //ts := pricetable.Gettable_my_wcpsb()
    ts := pricetable.Gettable_my_wcpsb_fb()

    //check file make sure file is new
    pricetable.CheckfileExistAndDelete(FILEPATH, ts.TableName+".sql")

    //get db connection
    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)

    pricetable.CheckErr(err)
    defer db.Close()

    // divide condition run different times
    var refrigerant407 string = "R407C"
    var refrigerant22 string = "R22"

    if isContainsREF(ts, refrigerant407) == true && isContainsREF(ts, refrigerant22) == true {
        queryExeWCPS(ts, "R-407C", db)
        queryExeWCPS(ts, "R-22", db)
        fmt.Println(" has  both ")
    } else if isContainsREF(ts, refrigerant22) == true {
        queryExeWCPS(ts, "R-22", db)
        fmt.Println(" has  R-22 ")
    } else if isContainsREF(ts, refrigerant407) == true {
        queryExeWCPS(ts, "R-407C", db)
        fmt.Println(" has  R-407C ")
    } else {
        queryExeWCPS(ts, "R-407C", db)
        fmt.Println(" has  no ")
    }

    //Print Execute finished info
    fmt.Println("# Execute End !")
}

func queryExeWCPS(ts *pricetable.TableStruct, refrigerant string, db *sql.DB) {

    tbname := ts.TableName
    tbcolums := ts.TableColums

    fmt.Println("# Querying")
    //fmt.Println(len(tablecolums))
    for i, v := range tbcolums {

        //test sentence
        if false {
            fmt.Println(i, v)
        }

        //combin query sql
        qstr := "SELECT \"unit_name\",\"model\",\"" + v + "\", \"basic-R22\",\"basic-R407C\"     FROM my_" + strings.TrimLeft(tbname, "my_")
        //fmt.Println(qstr)
        rows, err := db.Query(qstr)
        pricetable.CheckErr(err)

        for rows.Next() {

            var UNIT string
            var Model string
            var val string
            var basic_R22 string
            var basic_R407C string

            err = rows.Scan(&UNIT, &Model, &val, &basic_R22, &basic_R407C)
            pricetable.CheckErr(err)

            if (refrigerant == "R-22") && ((basic_R22 == "N/A") || (strings.Index(v, "R407C") != -1)) {
                continue
            }

            if (refrigerant == "R-407C") && ((basic_R407C == "N/A") || (strings.Index(v, "R22") != -1)) {
                continue
            }

            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {

                //do float round
                vv = strconv.FormatFloat(pricetable.MyRound(f, 0), 'f', 0, 32)
            } else {
                vv = val
            }

            //select * from price_constants
            //filter fields
            vv = pricetable.FilterString(vv)

            //("pid", "unit_name", "model", "refrigerant", "price_option", "y2013", "guid")
            var pixstr string = "insert into " + tbname + "  (\"pid\", \"unit_name\", \"model\", \"refrigerant\", \"price_option\", \"y2013\", \"guid\") VALUES ("

            contents := pixstr + "(select \"max\"(pid)  from " + tbname + ")+1" + " ,\"" + strings.Trim(UNIT, " ") + "\",\"" + strings.Trim(Model, " ") + "\",\"" + refrigerant + "\",\"" + strings.Trim(v, " ") + "\",\"" + vv + "\" , (select \"max\"(guid)  from " + tbname + ")+1 ;\n"

            //test print to console
            //fmt.Println(contents)

            //writer to file
            pricetable.Writertofile(contents, tbname+".sql", FILEPATH)
        }
    }

}

//=============================for WCPS=========start==============

//=============================for ACCS=========start==============
func findNotNA(listvalue []string) int {

    var index int
    for k, n := range listvalue {
        if n != "N/A" {
            index = k
            break
        }
    }
    return index
}
func callForACCS() {
    //Start
    fmt.Println("# ACCS Execute Start !")

    //get model

    //ts := pricetable.Gettable_my_dbair2()

    //ts := pricetable.Gettable_my_6accs_hp_r22() //--
    //ts := pricetable.Gettable_my_6accs_hp_r407c()
    //ts := pricetable.Gettable_my_6accs_r22()
    //ts := pricetable.Gettable_my_6accs_r407c()
    //ts := pricetable.Gettable_my_accs_hp_r22() //----
    ts := pricetable.Gettable_my_accs_hp_r407c() //----
    //ts := pricetable.Gettable_my_accs_r407c()
    //ts := pricetable.Gettable_my_accs_r22()

    //check file make sure file is new
    var accsfilepath = "D:/test/pricetable/"

    pricetable.CheckfileExistAndDelete(accsfilepath, ts.TableName+".sql")

    //get db connection
    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)

    pricetable.CheckErr(err)
    defer db.Close()

    // divide condition run different times

    var refrigerant string
    //my_accs_r407c
    //my_accs_r22

    if strings.Index(ts.TableName, "r22") != -1 {
        refrigerant = "R-22"
        fmt.Println(" has  R-22 ")
    } else if strings.Index(ts.TableName, "r407c") != -1 {
        refrigerant = "R-407C"
        fmt.Println(" has  R-407C ")
    }
    queryExeACCS(ts, refrigerant, db)

    //Print Execute finished info
    fmt.Println("# ACCS Execute End !")
}

func queryExeACCS(ts *pricetable.TableStruct, refrigerant string, db *sql.DB) {

    tbname := ts.TableName
    tbcolums := ts.TableColums

    fmt.Println("# Querying")
    //fmt.Println(len(tablecolums))
    for i, v := range tbcolums {

        //test sentence
        if false {
            fmt.Println(i, v)
        }

        //combin query sql
        qstr := "SELECT \"unit_name\",\"model\",\"" + v + "\", \"" + ts.TableColums[0] + "\",\"" + ts.TableColums[1] + "\",\"" + ts.TableColums[2] + "\",\"" + ts.TableColums[3] + "\",\"" + ts.TableColums[4] + "\"  FROM my_" + strings.TrimLeft(tbname, "my_")
        //qstr := "SELECT \"" + v + "\", * FROM my_" + strings.TrimLeft(tbname, "my_")

        //fmt.Println(qstr)
        rows, err := db.Query(qstr)
        pricetable.CheckErr(err)

        for rows.Next() {

            var val string
            var UNIT string
            var Model string

            var basicA string
            var basicB string
            var basicC string
            var basicD string
            var basicE string

            err = rows.Scan(&UNIT, &Model, &val, &basicA, &basicB, &basicC, &basicD, &basicE)
            pricetable.CheckErr(err)

            var listvalue = []string{
                basicA,
                basicB,
                basicC,
                basicD,
                basicE}

            if !strings.HasPrefix(ts.TableColums[findNotNA(listvalue)], strings.Trim(UNIT, " ")) || val == "N/A" {
                //fmt.Println(UNIT, Model, listvalue, val, " = ", v, " : ", ts.TableColums[findNotNA(listvalue)])
                continue
            }

            //fmt.Println(UNIT, Model, listvalue, val, " = ", v, " : ", ts.TableColums[findNotNA(listvalue)])
            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {

                //do float round
                vv = strconv.FormatFloat(pricetable.MyRound(f, 0), 'f', 0, 32)
            } else {
                vv = val
            }

            //select * from price_constants
            //filter fields
            vv = pricetable.FilterString(vv)

            //("pid", "unit_name", "model", "refrigerant", "price_option", "y2013", "guid")
            var pixstr string = "insert into " + tbname + "  (\"pid\", \"unit_name\", \"model\", \"refrigerant\", \"price_option\", \"y2013\", \"guid\") VALUES ("

            contents := pixstr + "(select \"max\"(pid)  from " + tbname + ")+1" + " ,\"" + strings.Trim(UNIT, " ") + "\",\"" + strings.Trim(Model, " ") + "\",\"" + refrigerant + "\",\"" + strings.Trim(v, " ") + "\",\"" + vv + "\" , (select \"max\"(guid)  from " + tbname + ")+1 ;\n"

            //test print to console
            //fmt.Println(contents)

            //writer to file
            pricetable.Writertofile(contents, tbname+".sql", FILEPATH)
        }
    }

}

//=============================for ACCS=========end================

//=============================for ACPSB=========start=============
func isContainsREF(ts *pricetable.TableStruct, refrigerant string) bool {

    var flag bool = false
    for _, v := range ts.TableColums {
        //fmt.Println(v, refrigerant)
        if strings.Index(strings.ToUpper(v), refrigerant) != -1 {
            flag = true
            break
        }
    }
    return flag
}

func callForACPSB() {
    //Start
    fmt.Println("# Execute Start !")

    //get model

    //ts := pricetable.Gettable_my_dbair2()

    //ts := pricetable.Gettable_my_6acpsb_p()
    //ts := pricetable.Gettable_my_6acpsb_p_hp()
    //ts := pricetable.Gettable_my_acpsb_p()
    //ts := pricetable.Gettable_my_acpsb_p_hp()
    ts := pricetable.Gettable_my_acps_c()

    //check file make sure file is new
    pricetable.CheckfileExistAndDelete(FILEPATH, ts.TableName+".sql")

    //get db connection
    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)

    pricetable.CheckErr(err)
    defer db.Close()

    // divide condition run different times
    var refrigerant407 string = "R407C"
    var refrigerant22 string = "R22"

    if isContainsREF(ts, refrigerant407) == true && isContainsREF(ts, refrigerant22) == true {
        queryExeACPSB(ts, "R-407C", db)
        queryExeACPSB(ts, "R-22", db)
        fmt.Println(" has  both ")
    } else if isContainsREF(ts, refrigerant22) == true {
        queryExeACPSB(ts, "R-22", db)
        fmt.Println(" has  R-22 ")
    } else if isContainsREF(ts, refrigerant407) == true {
        queryExeACPSB(ts, "R-407C", db)
        fmt.Println(" has  R-407C ")
    } else {
        queryExeACPSB(ts, "R-407C", db)
        fmt.Println(" has  no ")
    }
    // ACCS TYPE NEED MANUAL PROCESS

    //ACCS 共4回Evaporator Unit Price
    //Horizontal type    Vertical Type    Vertical type     Horizontal type
    //HEB -D     EB -D    VEB -D    VEB -D-FB

    //Print Execute finished info
    fmt.Println("# Execute End !")
}

func queryExeACPSB(ts *pricetable.TableStruct, refrigerant string, db *sql.DB) {

    tbname := ts.TableName
    tbcolums := ts.TableColums

    fmt.Println("# Querying")
    //fmt.Println(len(tablecolums))
    for i, v := range tbcolums {

        //test sentence
        if false {
            fmt.Println(i, v)
        }

        //combin query sql
        qstr := "SELECT \"unit_name\",\"model\",\"" + v + "\", \"basic-R22\",\"basic-R407C\"     FROM my_" + strings.TrimLeft(tbname, "my_")
        //fmt.Println(qstr)
        rows, err := db.Query(qstr)
        pricetable.CheckErr(err)

        for rows.Next() {

            var UNIT string
            var Model string
            var val string
            var basic_R22 string
            var basic_R407C string

            err = rows.Scan(&UNIT, &Model, &val, &basic_R22, &basic_R407C)
            pricetable.CheckErr(err)

            if (refrigerant == "R-22") && ((basic_R22 == "N/A") || (strings.Index(v, "R407C") != -1)) {
                continue
            }

            if (refrigerant == "R-407C") && ((basic_R407C == "N/A") || (strings.Index(v, "R22") != -1)) {
                continue
            }

            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {

                //do float round
                vv = strconv.FormatFloat(pricetable.MyRound(f, 0), 'f', 0, 32)
            } else {
                vv = val
            }

            //select * from price_constants
            //filter fields
            vv = pricetable.FilterString(vv)

            //("pid", "unit_name", "model", "refrigerant", "price_option", "y2013", "guid")
            var pixstr string = "insert into " + tbname + "  (\"pid\", \"unit_name\", \"model\", \"refrigerant\", \"price_option\", \"y2013\", \"guid\") VALUES ("

            contents := pixstr + "(select \"max\"(pid)  from " + tbname + ")+1" + " ,\"" + UNIT + "\",\"" + strings.Trim(Model, " ") + "\",\"" + refrigerant + "\",\"" + v + "\",\"" + vv + "\" , (select \"max\"(guid)  from " + tbname + ")+1 ;\n"

            //test print to console
            //fmt.Println(contents)

            //writer to file
            pricetable.Writertofile(contents, tbname+".sql", FILEPATH)
        }
    }

}

//=============================for ACPSB=========end================

 

单独执行代码 go database executive

 

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    //"time"
    "math"
    "os"
    "strconv"
)

const (
    DB_USER     = "ecatalog"
    DB_PASSWORD = "Zx27yeA"
    DB_NAME     = "spec7"
    DB_HOST     = "spec2.dunham-bush.com"
    DB_PORT     = "5433"
)

//D:\test\postgresql
const path = "D:/test/postgresql/output.txt"

var tablecolums []string = []string{
    "Basic 380-415/3ph/50Hz  (AU )",
    "Basic 460V/3ph/60Hz (Non-UL) (AR )",
    "Drain Pan (Stainless Steel)",
    "Discharge Plenum (Upflow only)",
    "Steam Generating Humidifier",
    "Hot Gas Bypass DX System Only",
    "Liquid Line Solenoid Valve -DX Systems Only (50Hz)",
    "Liquid Line Solenoid Valve -DX Systems Only (60Hz)",
    "Panel Insulation (Insulflex)",
    "Panel Insulation (Double Skin)",
    "SKD (Complete built up unit, piping joints to be brazed by othe",
    "Variable Frequency Drive (VFD) for Evaporator Blower Motor",
    "UnderFloor Water Detector (PinHole Probe)",
    "Smoke Detector",
    "EEV (50Hz/60Hz)",
    "BMS",
    "Low Ambient Kit to 7°C/45F (at design ambient 95F)",
    "Low Ambient Kit to 0°C/32F (at design ambient 95F)",
    "Low Ambient Kit to -10°C/14F (at design ambient 95F)",
    "Low Ambient Kit to 7°C/45F (at design ambient 105F)",
    "Low Ambient Kit to 0°C/32F (at design ambient 105F)",
    "Low Ambient Kit to -10°C/14F (at design ambient 105F)",
    "Coil Fin - Hydrophilic",
    "Coil Fin - Copper",
    "Black Color Epoxy Powder Coating (In lieu of Std Beige Color)  ",
}

func main() {
    queryExe()
}
func queryExe() {

    dbinfo := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    db, err := sql.Open("postgres", dbinfo)
    //expected := `dbname=database host=hostname.remote password=top\ secret port=1234 user=username`

    checkErr(err)
    defer db.Close()

    fmt.Println("# Querying")

    fmt.Println(len(tablecolums))
    for i, v := range tablecolums {
        if false {
            fmt.Println(i, v)
        }

        qstr := "SELECT \"UNIT\",\"Model\",\"" + v + "\"      FROM my_dbair2"
        //fmt.Println(qstr)

        //rows, err := db.Query("SELECT \"UNIT\",\"Model\" FROM my_dbair2")
        rows, err := db.Query(qstr)
        checkErr(err)

        for rows.Next() {

            var UNIT string
            var Model string
            var val string
            err = rows.Scan(&UNIT, &Model, &val)
            checkErr(err)

            var vv string

            f, err := strconv.ParseFloat(val, 2)

            if err == nil {
                vv = strconv.FormatFloat(myRound(f, 0), 'f', 0, 32)
                //fmt.Println(vv)
            } else {
                vv = val
                //fmt.Println(vv)
            }
            //fmt.Println(vv)

            //("pid", "unit_name", "model", "refrigerant", "price_option", "y2013", "guid")
            //fmt.Printf("%3v | %8v |%8v |\n", UNIT, Model, vv)
            var pixstr string = "insert into ppu_air_cooled_50_options_prices  (\"pid\", \"unit_name\", \"model\", \"refrigerant\", \"price_option\", \"y2013\", \"guid\") VALUES ("

            var refrigerant407 string = "R-407C"
            //var refrigerant22 string = "R-22"
            var refrigerant string

            refrigerant = refrigerant407

            contents := pixstr + "(select \"max\"(pid)  from ppu_air_cooled_50_options_prices)+1" + " ,\"" + UNIT + "\",\"" + Model + "\",\"" + refrigerant + "\",\"" + vv + "\" , (select \"max\"(guid)  from ppu_air_cooled_50_options_prices)+1 ;\n"
            fmt.Println(contents)
            writertofile(contents)

        }
    }

}
func writertofile(content string) {

    fil, err := os.OpenFile(path, os.O_RDWR|os.O_APPEND, 0420)
    if err != nil {
        fmt.Printf("Error: %s\n", err)
        return
    }
    defer fil.Close()
    fil.WriteString(content)
}
func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

func myRound(f float64, n int) float64 {
    pow10_n := math.Pow10(n)
    return math.Trunc((f+0.5/pow10_n)*pow10_n) / pow10_n
}

 

posted on 2016-03-01 15:59  rojas  阅读(218)  评论(0编辑  收藏  举报