1055 lines
26 KiB
Go
1055 lines
26 KiB
Go
package ews
|
|
|
|
import (
|
|
"APIServer/cnf"
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"time"
|
|
|
|
_ "github.com/go-sql-driver/mysql"
|
|
)
|
|
|
|
type EWS struct{}
|
|
|
|
type UutInfo struct {
|
|
USN string
|
|
MAC string
|
|
IPAddr string
|
|
Relay string
|
|
Item string
|
|
Status string
|
|
Message string
|
|
PartNO string
|
|
Model string
|
|
MfgSKU string
|
|
MfgMO string
|
|
MfgLine string
|
|
MfgStage string
|
|
FirstAck string
|
|
LastAck string
|
|
LastChg string
|
|
}
|
|
|
|
type ReportInfo struct {
|
|
USN string
|
|
PartNO string
|
|
MfgSKU string
|
|
MfgMO string
|
|
Line string
|
|
Location string
|
|
IPAddr string
|
|
Relay string
|
|
Item string
|
|
Status string
|
|
Message string
|
|
FirstAck string
|
|
LastAck string
|
|
LastChg string
|
|
DiffMins string
|
|
}
|
|
|
|
func (e *EWS) RecordIssue(cfg cnf.Cfg, logger *log.Logger, host string, addr string, uri string, params map[string]string, ui *UutInfo, dbo *sql.DB) map[string]string {
|
|
rst := map[string]string{"RESULT": ""}
|
|
firstAck := ui.FirstAck
|
|
if firstAck == "" {
|
|
firstAck = time.Now().Format("2006-01-02 15:04:05")
|
|
}
|
|
|
|
line := ""
|
|
col := 0
|
|
row := 0
|
|
num := 0
|
|
locTime := ""
|
|
loc := dbo.QueryRow(fmt.Sprintf(`SELECT line,col,row,num,DATE_FORMAT(update_time,'%%Y-%%m-%%d %%H:%%i:%%s') FROM locinfo WHERE mac='%s';`, params["MAC"]))
|
|
err := loc.Scan(&line, &col, &row, &num, &locTime)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
}
|
|
if locTime == "" {
|
|
locTime = "0000-00-00 00:00:00"
|
|
}
|
|
|
|
stmt, err := dbo.Prepare(
|
|
fmt.Sprintf(`INSERT INTO %s
|
|
(usn,mac,ipaddr,relay,partno,sku,mo,line,stage,item,status,message,first_ack,last_change,locline,loccol,locrow,locnum,loctime)
|
|
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);`,
|
|
cfg.MySQL.IssuesTable),
|
|
)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
defer stmt.Close()
|
|
|
|
_, err = stmt.Exec(
|
|
params["USN"],
|
|
params["MAC"],
|
|
addr,
|
|
host,
|
|
params["PARTNO"],
|
|
params["SKU"],
|
|
params["MO"],
|
|
params["LINE"],
|
|
params["STAGE"],
|
|
params["ITEM"],
|
|
params["STATUS"],
|
|
params["MESSAGE"],
|
|
firstAck,
|
|
time.Now().Format("2006-01-02 15:04:05"),
|
|
line,
|
|
col,
|
|
row,
|
|
num,
|
|
locTime,
|
|
)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
rst["RESULT"] = "OK"
|
|
return rst
|
|
}
|
|
|
|
func (e *EWS) SetUutInfo(cfg cnf.Cfg, logger *log.Logger, host string, addr string, uri string, params map[string]string) map[string]string {
|
|
rst := map[string]string{"RESULT": ""}
|
|
if params["USN"] == "" {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, "Missing Parameter USN.")
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = "Missing Parameter USN."
|
|
return rst
|
|
}
|
|
|
|
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
|
|
cfg.MySQL.User,
|
|
cfg.MySQL.Password,
|
|
cfg.MySQL.Server,
|
|
cfg.MySQL.Port,
|
|
cfg.MySQL.Database,
|
|
)
|
|
dbo, err := sql.Open("mysql", dsn)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
defer dbo.Close()
|
|
err = dbo.Ping()
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
rec := dbo.QueryRow(
|
|
fmt.Sprintf(`SELECT usn,mac,ipaddr,relay,item,status,message,partno,mo,sku,line,stage,first_ack,last_ack,last_change
|
|
FROM uutinfo
|
|
WHERE usn='%s';`, params["USN"]))
|
|
|
|
ui := new(UutInfo)
|
|
err = rec.Scan(
|
|
&ui.USN,
|
|
&ui.MAC,
|
|
&ui.IPAddr,
|
|
&ui.Relay,
|
|
&ui.Item,
|
|
&ui.Status,
|
|
&ui.Message,
|
|
&ui.PartNO,
|
|
&ui.MfgMO,
|
|
&ui.MfgSKU,
|
|
&ui.MfgLine,
|
|
&ui.MfgStage,
|
|
&ui.FirstAck,
|
|
&ui.LastAck,
|
|
&ui.LastChg,
|
|
)
|
|
|
|
//如果在uutinfo表中未查找到此USN的记录, 则新增一条此USN的记录
|
|
if err == sql.ErrNoRows {
|
|
dmlInsert := fmt.Sprintf(`INSERT INTO uutinfo
|
|
(usn,mac,ipaddr,relay,item,status,message,partno,sku,mo,line,stage,first_ack,last_ack,last_change)
|
|
VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,%s,%s);`,
|
|
params["USN"],
|
|
params["MAC"],
|
|
addr,
|
|
host,
|
|
params["ITEM"],
|
|
params["STATUS"],
|
|
params["MESSAGE"],
|
|
params["PARTNO"],
|
|
params["SKU"],
|
|
params["MO"],
|
|
params["LINE"],
|
|
params["STAGE"],
|
|
"NOW()",
|
|
"NOW()",
|
|
"NOW()",
|
|
)
|
|
|
|
_, err = dbo.Exec(dmlInsert)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
if params["STATUS"] == "NG" {
|
|
rst = e.RecordIssue(cfg, logger, host, addr, uri, params, ui, dbo)
|
|
if rst["RESULT"] != "OK" {
|
|
return rst
|
|
}
|
|
}
|
|
rst["RESULT"] = "OK"
|
|
rst["ErrMsg"] = "Inserted 1 new record."
|
|
return rst
|
|
}
|
|
|
|
//如果测试项不为空(第一个心跳包), 当部分客户端参数为空, 则保留uutinfo表中已存在记录的值
|
|
mac := params["MAC"]
|
|
pn := params["PARTNO"]
|
|
sku := params["SKU"]
|
|
mo := params["MO"]
|
|
line := params["LINE"]
|
|
stage := params["STAGE"]
|
|
if mac == "" {
|
|
mac = ui.MAC
|
|
}
|
|
if pn == "" {
|
|
pn = ui.PartNO
|
|
}
|
|
if sku == "" {
|
|
sku = ui.MfgSKU
|
|
}
|
|
if mo == "" {
|
|
mo = ui.MfgMO
|
|
}
|
|
if line == "" {
|
|
line = ui.MfgLine
|
|
}
|
|
if stage == "" {
|
|
stage = ui.MfgStage
|
|
}
|
|
|
|
//如果此USN的记录存在于uutinfo表中, 且此USN的Relay/MAC/Item/Status/Message已变化, 则:
|
|
//1. 备份此USN对应记录行到uutinfobkup表
|
|
//2. 删除uutinfo表中对应USN的记录行
|
|
//3. 新增此USN的最新记录到uutinfo表
|
|
isExist := false
|
|
chgFlag := false
|
|
|
|
_, isExist = params["MAC"]
|
|
if isExist && params["MAC"] != ui.MAC {
|
|
chgFlag = true
|
|
}
|
|
|
|
_, isExist = params["ITEM"]
|
|
if isExist && params["ITEM"] != ui.Item {
|
|
chgFlag = true
|
|
}
|
|
|
|
_, isExist = params["STATUS"]
|
|
if isExist && params["STATUS"] != ui.Status {
|
|
chgFlag = true
|
|
}
|
|
|
|
_, isExist = params["MESSAGE"]
|
|
if isExist && params["MESSAGE"] != ui.Message {
|
|
chgFlag = true
|
|
}
|
|
|
|
if host != ui.Relay {
|
|
chgFlag = true
|
|
}
|
|
|
|
if chgFlag {
|
|
tx, err := dbo.Begin()
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
stmt, err := tx.Prepare("INSERT INTO uutinfobkup (usn,mac,ipaddr,relay,item,status,message,partno,sku,mo,line,stage,first_ack,last_ack,last_change) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);")
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
defer stmt.Close()
|
|
_, err = stmt.Exec(
|
|
ui.USN,
|
|
ui.MAC,
|
|
ui.IPAddr,
|
|
ui.Relay,
|
|
ui.Item,
|
|
ui.Status,
|
|
ui.Message,
|
|
ui.PartNO,
|
|
ui.MfgSKU,
|
|
ui.MfgMO,
|
|
ui.MfgLine,
|
|
ui.MfgStage,
|
|
ui.FirstAck,
|
|
ui.LastAck,
|
|
ui.LastChg,
|
|
)
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
stmt, err = tx.Prepare("DELETE FROM uutinfo WHERE usn=?;")
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
defer stmt.Close()
|
|
_, err = stmt.Exec(params["USN"])
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
stmt, err = tx.Prepare("INSERT INTO uutinfo (usn,mac,ipaddr,relay,item,status,message,partno,sku,mo,line,stage,first_ack,last_ack,last_change) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW());")
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
_, err = stmt.Exec(
|
|
params["USN"],
|
|
mac,
|
|
addr,
|
|
host,
|
|
params["ITEM"],
|
|
params["STATUS"],
|
|
params["MESSAGE"],
|
|
pn,
|
|
sku,
|
|
mo,
|
|
line,
|
|
stage,
|
|
ui.FirstAck,
|
|
)
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
tx.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
if params["STATUS"] == "NG" {
|
|
rst = e.RecordIssue(cfg, logger, host, addr, uri, params, ui, dbo)
|
|
if rst["RESULT"] != "OK" {
|
|
return rst
|
|
}
|
|
}
|
|
|
|
rst["RESULT"] = "OK"
|
|
rst["ErrMsg"] = "Updated and backed up 1 record."
|
|
return rst
|
|
}
|
|
|
|
//如果此USN的记录存在于uutinfo表中, 且Relay/MAC/Item/Status/Message未变化, 则更新uutinfo表中此USN对应的last_ack栏位为当前时间
|
|
//dmlUpdate := fmt.Sprintf("UPDATE uutinfo SET ipaddr='%s', relay='%s', line='%s', stage='%s', last_ack=NOW() WHERE usn='%s';",
|
|
// addr, host, params["LINE"], params["STAGE"], params["USN"])
|
|
//2024-01-26: 使用事务模式先删除再插入此USN的记录, 避免直接使用UPDATE导致自增键seqid不更新
|
|
|
|
txu, err := dbo.Begin()
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
stmt, err := txu.Prepare("DELETE FROM uutinfo WHERE usn=?;")
|
|
if err != nil {
|
|
txu.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
defer stmt.Close()
|
|
_, err = stmt.Exec(ui.USN)
|
|
if err != nil {
|
|
txu.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
stmt, err = txu.Prepare("INSERT INTO uutinfo (usn,mac,ipaddr,relay,item,status,message,partno,sku,mo,line,stage,first_ack,last_ack,last_change) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?);")
|
|
if err != nil {
|
|
txu.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
_, err = stmt.Exec(
|
|
ui.USN,
|
|
mac,
|
|
addr,
|
|
host,
|
|
ui.Item,
|
|
ui.Status,
|
|
ui.Message,
|
|
pn,
|
|
sku,
|
|
mo,
|
|
line,
|
|
stage,
|
|
ui.FirstAck,
|
|
ui.LastChg,
|
|
)
|
|
if err != nil {
|
|
txu.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
err = txu.Commit()
|
|
if err != nil {
|
|
txu.Rollback()
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = err.Error()
|
|
return rst
|
|
}
|
|
|
|
rst["RESULT"] = "OK"
|
|
rst["ErrMsg"] = "Updated 1 record."
|
|
return rst
|
|
}
|
|
|
|
func (e *EWS) GetUutInfo(cfg cnf.Cfg, logger *log.Logger, host string, addr string, uri string, params map[string]string) map[string]string {
|
|
rst := map[string]string{"RESULT": ""}
|
|
if params["KEY"] == "" || params["VALUE"] == "" {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, "Invalid Parameters !")
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", "Invalid Parameters !")
|
|
return rst
|
|
}
|
|
|
|
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
|
|
cfg.MySQL.User,
|
|
cfg.MySQL.Password,
|
|
cfg.MySQL.Server,
|
|
cfg.MySQL.Port,
|
|
cfg.MySQL.Database,
|
|
)
|
|
dbo, err := sql.Open("mysql", dsn)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer dbo.Close()
|
|
err = dbo.Ping()
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
|
|
ul := new(UutInfo)
|
|
model := ""
|
|
partNO := ""
|
|
mfgMO := ""
|
|
mfgSKU := ""
|
|
fstAck := ""
|
|
lstAck := ""
|
|
|
|
//Get Last Record
|
|
last := ""
|
|
curr, err := dbo.Query(
|
|
fmt.Sprintf(`SELECT
|
|
t1.usn,
|
|
t1.mac,
|
|
t1.ipaddr,
|
|
t1.relay,
|
|
t1.item,
|
|
t1.status,
|
|
t1.message,
|
|
t1.partno,
|
|
IFNULL(t2.model, '') AS model,
|
|
t1.sku,
|
|
t1.mo,
|
|
t1.line,
|
|
t1.stage,
|
|
t1.first_ack,
|
|
t1.last_ack,
|
|
t1.last_change
|
|
FROM uutinfo t1 LEFT JOIN modelfamily t2 ON t1.partno=t2.modelfamily
|
|
WHERE t1.%s='%s'
|
|
ORDER BY t1.usn ASC, t1.seqid ASC;`, params["KEY"], params["VALUE"]))
|
|
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer curr.Close()
|
|
|
|
for curr.Next() {
|
|
err := curr.Scan(
|
|
&ul.USN,
|
|
&ul.MAC,
|
|
&ul.IPAddr,
|
|
&ul.Relay,
|
|
&ul.Item,
|
|
&ul.Status,
|
|
&ul.Message,
|
|
&ul.PartNO,
|
|
&ul.Model,
|
|
&ul.MfgSKU,
|
|
&ul.MfgMO,
|
|
&ul.MfgLine,
|
|
&ul.MfgStage,
|
|
&ul.FirstAck,
|
|
&ul.LastAck,
|
|
&ul.LastChg,
|
|
)
|
|
if err != nil {
|
|
break
|
|
}
|
|
|
|
if ul.Status == "NG" {
|
|
last = last + fmt.Sprintf(`<tr>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
</tr>%s`, ul.USN, ul.MfgSKU, ul.MfgMO, ul.MfgLine, ul.MfgStage, ul.MAC, ul.IPAddr, ul.Relay, ul.Item, ul.Status, ul.Message, ul.LastChg, "\r\n")
|
|
} else {
|
|
last = last + fmt.Sprintf(`<tr>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
<td style="color: blue;">%s</td>
|
|
</tr>%s`, ul.USN, ul.MfgSKU, ul.MfgMO, ul.MfgLine, ul.MfgStage, ul.MAC, ul.IPAddr, ul.Relay, ul.Item, ul.Status, ul.Message, ul.LastChg, "\r\n")
|
|
}
|
|
|
|
if ul.Model != "" {
|
|
model = ul.Model
|
|
}
|
|
if ul.PartNO != "" {
|
|
partNO = ul.PartNO
|
|
}
|
|
if ul.MfgSKU != "" {
|
|
mfgSKU = ul.MfgSKU
|
|
}
|
|
if ul.MfgMO != "" {
|
|
mfgMO = ul.MfgMO
|
|
}
|
|
if ul.FirstAck != "" {
|
|
fstAck = ul.FirstAck
|
|
}
|
|
if ul.LastAck != "" {
|
|
lstAck = ul.LastAck
|
|
}
|
|
}
|
|
|
|
//Get History Records
|
|
uh := new(UutInfo)
|
|
bkup := ""
|
|
prev, err := dbo.Query(
|
|
fmt.Sprintf(`SELECT
|
|
t1.usn,
|
|
t1.mac,
|
|
t1.ipaddr,
|
|
t1.relay,
|
|
t1.item,
|
|
t1.status,
|
|
t1.message,
|
|
t1.partno,
|
|
IFNULL(t2.model, '') AS model,
|
|
t1.sku,
|
|
t1.mo,
|
|
t1.line,
|
|
t1.stage,
|
|
t1.first_ack,
|
|
t1.last_ack,
|
|
t1.last_change
|
|
FROM uutinfobkup t1 LEFT JOIN modelfamily t2 ON t1.partno=t2.modelfamily
|
|
WHERE t1.%s='%s'
|
|
ORDER BY t1.usn ASC, t1.seqid ASC;`, params["KEY"], params["VALUE"]))
|
|
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer prev.Close()
|
|
|
|
for prev.Next() {
|
|
err := prev.Scan(
|
|
&uh.USN,
|
|
&uh.MAC,
|
|
&uh.IPAddr,
|
|
&uh.Relay,
|
|
&uh.Item,
|
|
&uh.Status,
|
|
&uh.Message,
|
|
&uh.PartNO,
|
|
&uh.Model,
|
|
&uh.MfgSKU,
|
|
&uh.MfgMO,
|
|
&uh.MfgLine,
|
|
&uh.MfgStage,
|
|
&uh.FirstAck,
|
|
&uh.LastAck,
|
|
&uh.LastChg,
|
|
)
|
|
if err != nil {
|
|
break
|
|
}
|
|
if uh.Status == "NG" {
|
|
bkup = bkup + fmt.Sprintf(`<tr>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
<td style="color: red; font-weight: bold;">%s</td>
|
|
</tr>%s`,
|
|
uh.USN, uh.MfgSKU, uh.MfgMO, uh.MfgLine, uh.MfgStage, uh.MAC, uh.IPAddr, uh.Relay, uh.Item, uh.Status, uh.Message, uh.LastChg, "\r\n")
|
|
} else {
|
|
bkup = bkup + fmt.Sprintf(`<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>%s`,
|
|
uh.USN, uh.MfgSKU, uh.MfgMO, uh.MfgLine, uh.MfgStage, uh.MAC, uh.IPAddr, uh.Relay, uh.Item, uh.Status, uh.Message, uh.LastChg, "\r\n")
|
|
|
|
}
|
|
}
|
|
|
|
//Get Location Information
|
|
loc, locTime := "", ""
|
|
var locStmp int64 = 0
|
|
if ul.MAC != "" {
|
|
qry := dbo.QueryRow(fmt.Sprintf(`SELECT CONCAT_WS(' ', line, CONCAT_WS('-',col,row,num)) AS loc, update_time, update_stmp FROM locinfo WHERE mac='%s' ORDER BY seqid DESC;`, ul.MAC))
|
|
qry.Scan(&loc, &locTime, &locStmp)
|
|
} else {
|
|
qry := dbo.QueryRow(fmt.Sprintf(`SELECT CONCAT_WS(' ', line, CONCAT_WS('-',col,row,num)) AS loc, update_time, update_stmp FROM locinfo WHERE mac='%s' ORDER BY seqid DESC;`, uh.MAC))
|
|
qry.Scan(&loc, &locTime, &locStmp)
|
|
}
|
|
|
|
if loc == "" {
|
|
loc = `<span style="color: red;">N/A</span>`
|
|
} else {
|
|
loc = fmt.Sprintf(`<span>%s</span>`, loc)
|
|
}
|
|
|
|
if time.Now().Unix()-locStmp >= 3600 {
|
|
locTime = fmt.Sprintf(`<span style="color: red;">%s</span>`, locTime)
|
|
} else {
|
|
locTime = fmt.Sprintf(`<span>%s</span>`, locTime)
|
|
}
|
|
|
|
//Get Misc. Info
|
|
if model == "" {
|
|
model = uh.Model
|
|
}
|
|
if partNO == "" {
|
|
partNO = uh.PartNO
|
|
}
|
|
if mfgSKU == "" {
|
|
mfgSKU = uh.MfgSKU
|
|
}
|
|
if mfgMO == "" {
|
|
mfgMO = uh.MfgMO
|
|
}
|
|
if fstAck == "" {
|
|
fstAck = uh.FirstAck
|
|
}
|
|
if lstAck == "" {
|
|
lstAck = uh.LastAck
|
|
}
|
|
|
|
misc := ""
|
|
misc = fmt.Sprintf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
|
|
model, partNO, mfgSKU, mfgMO, fstAck, lstAck)
|
|
|
|
rst["RESULT"] = "OK"
|
|
rst["ErrMsg"] = e.TemplateUutInfo(loc, locTime, misc, last, bkup)
|
|
|
|
return rst
|
|
}
|
|
|
|
func (e *EWS) GetIssueReport(cfg cnf.Cfg, logger *log.Logger, host string, addr string, uri string, params map[string]string) map[string]string {
|
|
rst := map[string]string{"RESULT": ""}
|
|
|
|
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
|
|
cfg.MySQL.User,
|
|
cfg.MySQL.Password,
|
|
cfg.MySQL.Server,
|
|
cfg.MySQL.Port,
|
|
cfg.MySQL.Database,
|
|
)
|
|
dbo, err := sql.Open("mysql", dsn)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer dbo.Close()
|
|
err = dbo.Ping()
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
|
|
ri := new(ReportInfo)
|
|
defect := ""
|
|
rows, err := dbo.Query(fmt.Sprintf(
|
|
`SELECT usn,partno,sku,mo,line,location,ipaddr,relay,item,status,message,last_change,last_ack,diffmins
|
|
FROM %s;`, cfg.MySQL.IssuesView))
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer rows.Close()
|
|
|
|
num := 1
|
|
for rows.Next() {
|
|
err := rows.Scan(
|
|
&ri.USN,
|
|
&ri.PartNO,
|
|
&ri.MfgSKU,
|
|
&ri.MfgMO,
|
|
&ri.Line,
|
|
&ri.Location,
|
|
&ri.IPAddr,
|
|
&ri.Relay,
|
|
&ri.Item,
|
|
&ri.Status,
|
|
&ri.Message,
|
|
&ri.LastChg,
|
|
&ri.LastAck,
|
|
&ri.DiffMins,
|
|
)
|
|
if err != nil {
|
|
break
|
|
}
|
|
temp := fmt.Sprintf(`<tr><td>%d</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>%s`,
|
|
num, ri.USN, ri.PartNO, ri.MfgSKU, ri.MfgMO, ri.Line, ri.Location, ri.IPAddr, ri.Relay, ri.Item, ri.Status, ri.Message, ri.LastChg, ri.LastAck, ri.DiffMins, "\r\n")
|
|
defect = defect + temp
|
|
num += 1
|
|
}
|
|
|
|
rst["RESULT"] = "OK"
|
|
rst["ErrMsg"] = e.TemplateReportInfo(defect)
|
|
return rst
|
|
}
|
|
|
|
func (e *EWS) GetOfflineReport(cfg cnf.Cfg, logger *log.Logger, host string, addr string, uri string, params map[string]string) map[string]string {
|
|
rst := map[string]string{"RESULT": ""}
|
|
|
|
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
|
|
cfg.MySQL.User,
|
|
cfg.MySQL.Password,
|
|
cfg.MySQL.Server,
|
|
cfg.MySQL.Port,
|
|
cfg.MySQL.Database,
|
|
)
|
|
dbo, err := sql.Open("mysql", dsn)
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer dbo.Close()
|
|
err = dbo.Ping()
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
|
|
ri := new(ReportInfo)
|
|
offline := ""
|
|
rows, err := dbo.Query(fmt.Sprintf(
|
|
`SELECT usn,partno,sku,mo,line,location,ipaddr,relay,item,status,message,last_change,last_ack,diffmins
|
|
FROM %s;`, cfg.MySQL.OfflineView))
|
|
if err != nil {
|
|
logger.Printf("[ERR] %s; %s; %#v; %s\r\n", addr, uri, params, err.Error())
|
|
rst["RESULT"] = "NG"
|
|
rst["ErrMsg"] = e.SimpleMsgHTML("red", err.Error())
|
|
return rst
|
|
}
|
|
defer rows.Close()
|
|
|
|
num := 1
|
|
for rows.Next() {
|
|
err := rows.Scan(
|
|
&ri.USN,
|
|
&ri.PartNO,
|
|
&ri.MfgSKU,
|
|
&ri.MfgMO,
|
|
&ri.Line,
|
|
&ri.Location,
|
|
&ri.IPAddr,
|
|
&ri.Relay,
|
|
&ri.Item,
|
|
&ri.Status,
|
|
&ri.Message,
|
|
&ri.LastChg,
|
|
&ri.LastAck,
|
|
&ri.DiffMins,
|
|
)
|
|
if err != nil {
|
|
break
|
|
}
|
|
temp := fmt.Sprintf(`<tr><td>%d</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>%s`,
|
|
num, ri.USN, ri.PartNO, ri.MfgSKU, ri.MfgMO, ri.Line, ri.Location, ri.IPAddr, ri.Relay, ri.Item, ri.Status, ri.Message, ri.LastChg, ri.LastAck, ri.DiffMins, "\r\n")
|
|
offline = offline + temp
|
|
num += 1
|
|
}
|
|
|
|
rst["RESULT"] = "OK"
|
|
rst["ErrMsg"] = e.TemplateReportInfo(offline)
|
|
return rst
|
|
}
|
|
|
|
func (e *EWS) SimpleMsgHTML(clr, msg string) string {
|
|
now := time.Now().Format("2006-01-02 15:04:05")
|
|
resp := fmt.Sprintf(`<!DOCTYPE html>
|
|
<html>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
|
|
<head>
|
|
<style>pre {word-wrap: break-word;}</style>
|
|
<style>body {font-family: Consolas; font-size: 14px;}</style>
|
|
</head>
|
|
<body>
|
|
<br/><span style="color: %s;">[%s] %s</span><br/>
|
|
</body>
|
|
</html>`, clr, now, msg)
|
|
return resp
|
|
}
|
|
|
|
func (e *EWS) TemplateUutInfo(loc, locTime, misc, last, bkup string) string {
|
|
now := time.Now().Format("2006-01-02 15:04:05")
|
|
|
|
resp := fmt.Sprintf(`<!DOCTYPE html>
|
|
<html>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
|
|
<title>Test Records</title>
|
|
<style type="text/css">
|
|
table {
|
|
border: 1px solid rgb(81, 130, 187);
|
|
border-collapse: separate;
|
|
border-spacing: 1px;
|
|
position: static;
|
|
}
|
|
th {
|
|
background-color: rgb(81, 130, 187);
|
|
color: #fff;
|
|
border: 1px solid rgb(81, 130, 187);
|
|
padding: 5px 10px;
|
|
font-size: 12px;
|
|
font-family: Verdana;
|
|
font-weight: bold;
|
|
}
|
|
tr {
|
|
border: 1px solid rgb(81, 130, 187);
|
|
}
|
|
td {
|
|
color: #000;
|
|
padding: 5px 10px;
|
|
font-size: 12px;
|
|
font-family: Consolas;
|
|
}
|
|
span {
|
|
font-size: 14px;
|
|
font-family: Consolas;
|
|
}
|
|
</style>
|
|
<body>
|
|
<span>Response Time: %s</span><br/><br/>
|
|
<span style="font-weight: bold; font-size: 16px;">Location Info</span>
|
|
<table>
|
|
<tr>
|
|
<th>Location</th>
|
|
<th>Location Time</th>
|
|
</tr>
|
|
<tr>
|
|
<td>%s</td>
|
|
<td>%s</td>
|
|
</tr>
|
|
</table>
|
|
|
|
<br/>
|
|
<span style="font-weight: bold; font-size: 16px;">Basic Info</span>
|
|
<br/>
|
|
<table>
|
|
<tr>
|
|
<th>Model</th>
|
|
<th>Project Code</th>
|
|
<th>SKU</th>
|
|
<th>MO</th>
|
|
<th>First Ack</th>
|
|
<th>Last Ack</th>
|
|
</tr>
|
|
%s
|
|
</table>
|
|
|
|
<br/>
|
|
<span style="font-weight: bold; font-size: 16px;">Test Records</span>
|
|
<br/>
|
|
<table>
|
|
<tr>
|
|
<th>USN</th>
|
|
<th>SKU</th>
|
|
<th>MO</th>
|
|
<th>Line</th>
|
|
<th>Stage</th>
|
|
<th>MAC</th>
|
|
<th>IP Address</th>
|
|
<th>Server</th>
|
|
<th>Item</th>
|
|
<th>Status</th>
|
|
<th>Message</th>
|
|
<th>Last Change</th>
|
|
</tr>
|
|
%s
|
|
%s
|
|
</table>
|
|
</body>
|
|
</html>`, now, loc, locTime, misc, last, bkup)
|
|
return resp
|
|
}
|
|
|
|
func (e *EWS) TemplateReportInfo(src string) string {
|
|
now := time.Now().Format("2006-01-02 15:04:05")
|
|
if src == "" {
|
|
src = fmt.Sprintf(`<span style="color: red;">[%s] NO Records !</span><br/>`, now)
|
|
}
|
|
resp := fmt.Sprintf(`<!DOCTYPE html>
|
|
<html>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
|
|
<title>Test Records</title>
|
|
<style type="text/css">
|
|
table {
|
|
border: 1px solid rgb(81, 130, 187);
|
|
border-collapse: separate;
|
|
border-spacing: 1px;
|
|
position: static;
|
|
}
|
|
th {
|
|
background-color: rgb(81, 130, 187);
|
|
color: #fff;
|
|
border: 1px solid rgb(81, 130, 187);
|
|
padding: 5px 10px;
|
|
font-size: 12px;
|
|
font-family: Verdana;
|
|
font-weight: bold;
|
|
}
|
|
tr {
|
|
border: 1px solid rgb(81, 130, 187);
|
|
}
|
|
td {
|
|
color: #000;
|
|
padding: 5px 10px;
|
|
font-size: 12px;
|
|
font-family: Consolas;
|
|
}
|
|
span {
|
|
font-size: 14px;
|
|
font-family: Consolas;
|
|
}
|
|
</style>
|
|
<body>
|
|
<span>Response Time: %s</span><br/>
|
|
<table>
|
|
<tr>
|
|
<th>NO.</th>
|
|
<th>USN</th>
|
|
<th>Project Code</th>
|
|
<th>SKU</th>
|
|
<th>MO</th>
|
|
<th>Line</th>
|
|
<th>Location</th>
|
|
<th>IP Address</th>
|
|
<th>Server</th>
|
|
<th>Item</th>
|
|
<th>Status</th>
|
|
<th>Message</th>
|
|
<th>Last Change</th>
|
|
<th>Last Ack</th>
|
|
<th>Differ Mins</th>
|
|
</tr>
|
|
%s
|
|
</table>
|
|
</body>
|
|
</html>`, now, src)
|
|
return resp
|
|
}
|