def insertMappingList(dbcon, MapType, Value, RelValue):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
sql = (
"INSERT INTO [dbo].[MAPAAA]([GUID],[MAPAAA001],[MAPAAA002],[MAPAAA003],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + GUID + "',N'" + PublicFun.SQLFilter(MapType) + "',N'" +
PublicFun.SQLFilter(Value) + "',N'" + PublicFun.SQLFilter(RelValue) +
"','System','" + PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") +
"','','')")
dbcon.Execute(sql)
return GUID
def addQueue(SystemName, QueueType, Path, Files, Param):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
jsonData = str(Param)
D_INSERTTIME = PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")
dbcon = getQueueDBConnect()
sql = (
"INSERT INTO [dbo].[JobQueue]([GUID],[SystemName],[QueueType],[Path],[Files],[Param],[D_INSERTTIME])"
+ "VALUES('" + GUID + "',N'" + PublicFun.SQLFilter(SystemName) +
"',N'" + PublicFun.SQLFilter(QueueType) + "',N'" +
PublicFun.SQLFilter(Path) + "',N'" + PublicFun.SQLFilter(str(Files)) +
"',N'" + PublicFun.SQLFilter(str(jsonData)) + "','" +
str(D_INSERTTIME) + "')")
dbcon.Execute(sql)
dbcon.close()
def findCompany(dbcon, CompanysName):
import Public.PublicFun as PublicFun
sql = ("SELECT GUID FROM Companys WHERE Companys003=N'" +
PublicFun.SQLFilter(CompanysName) + "'")
CompanysGUID = dbcon.GetDataTable(sql)
if CompanysGUID is not None and len(CompanysGUID) > 0:
return CompanysGUID[0].GUID
return ""
def findOption(dbcon, OptionName):
import Public.PublicFun as PublicFun
sql = ("SELECT GUID FROM OPTAAA WHERE OPTAAA001=N'" +
PublicFun.SQLFilter(OptionName) + "'")
OptionGUID = dbcon.GetDataTable(sql)
if OptionGUID is not None and len(OptionGUID) > 0:
return OptionGUID[0].GUID
return ""
def CheckMappingList(dbcon, MapType, Value):
import Public.PublicFun as PublicFun
if (MapType == "CompanyName"):
sql = ("SELECT TOP 1 MAPAAA003 FROM MAPAAA WHERE MAPAAA001='" +
MapType + "' AND MAPAAA002=N'" + PublicFun.SQLFilter(Value) +
"'")
else:
sql = ("SELECT TOP 1 MAPAAA003 FROM MAPAAA WHERE MAPAAA001='" +
MapType + "' AND MAPAAA002=N'" + Value + "'")
return dbcon.GetDataTable(sql)
def writeDBMsg(JobName, Param, msg, dbcon=None):
import Public.PublicFun as PublicFun
if dbcon is None:
dbcon = SQLConnect.DBConnect(publicSetting=True)
dbcon.ConnectDB()
writeDBMsg(JobName, Param, msg, dbcon)
dbcon.close()
else:
try:
sql = (
"INSERT INTO [dbo].[LogMsg]([GUID],[JOB],[Param],[Message],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + PublicFun.createID() + "',N'" +
PublicFun.SQLFilter(JobName) + "',N'" +
PublicFun.SQLFilter(str(Param)) + "',N'" +
PublicFun.SQLFilter(str(msg)) + "',N'" +
PublicFun.SQLFilter(JobName) + "','" +
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") + "','','')")
dbcon.Execute(sql)
writeMsg(str(msg))
except Exception as ex:
writeMsg("寫入資料庫失敗:" + ex)
def UpdateData(dbcon,DataObject):
import Public.PublicFun as PublicFun
sql=DataObject.QueryStr
WhereClause=" WHERE 1=1 "
for key in DataObject.KeyFields:
WhereClause=WhereClause+" AND "+key+ "='"+DataObject.getData(key)+"'"
excuteSQL=sql+WhereClause
FindRow = dbcon.GetDataTable(excuteSQL)
excuteSQL=""
NowTime=PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")
if FindRow is not None and len(FindRow)>0:
FindRow=FindRow[0]
DataObject.D_MODIFYTIME=NowTime
if DataObject.CheckTimeStamp:
DBTime=FindRow.D_MODIFYTIME
if DBTime is None or DBTime =="":
DBTime=FindRow.D_INSERTTIME
if DBTime >DataObject.TimeStamp:
raise Exception("The data has been updated by others!")
excuteSQL="UPDATE "+DataObject.TableName+" SET "
strField=""
for Field in DataObject.Fields:
strField=strField+Field+"=N'"+PublicFun.SQLFilter(DataObject.getData(Field))+"',"
excuteSQL = excuteSQL+strField.rstrip(',') + WhereClause
else:
DataObject.D_INSERTTIME=NowTime
excuteSQL="INSERT INTO "+DataObject.TableName
strField=""
strValue=""
for Field in DataObject.Fields:
strField=strField+Field+","
strValue=strValue+"N'"+PublicFun.SQLFilter(DataObject.getData(Field))+"',"
excuteSQL=excuteSQL+"("+strField.rstrip(',')+")VALUES("+strValue.rstrip(',')+")"
dbcon.Execute(excuteSQL)
return True
def writeDBMsg(msg):
import Public.PublicFun as PublicFun
import Public.LogHandler as LogHandler
dbcon = getQueueDBConnect()
sql = ("INSERT INTO [dbo].[LogMsg]([GUID],[Message],[D_INSERTTIME])" +
"VALUES(?,?,?)")
try:
dbcon.Execute(sql, (PublicFun.createID(), PublicFun.SQLFilter(msg),
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
except:
msg = "writeDBMsg失敗：" + msg
LogHandler.writeMsg(msg)
dbcon.close()
def insertOption(dbcon, OptionName, RelGUID=None):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
sql = (
"INSERT INTO [dbo].[OPTAAA]([GUID],[OPTAAA001],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + GUID + "',N'" + PublicFun.SQLFilter(OptionName) +
"','System','" + PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") +
"','','')")
dbcon.Execute(sql)
if RelGUID is not None:
sql = (
"INSERT INTO [dbo].[OPTAAB]([GUID],[OPTAAB001],[OPTAAB002],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + PublicFun.createID() + "','" + RelGUID + "','" +
GUID + "','System','" +
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") + "','','')")
dbcon.Execute(sql)
return GUID
text_re = re.compile('Text=')
queryString = PhoneNumberurl.split("&")
for param in queryString:
if len(text_re.findall(param)) > 0:
return param[5:]
if __name__ == '__main__':
jsondata = ""
try:
JobID = sys.argv[1]
data = sys.argv[2]
jsondata = PublicFun.StringToJson(data)
jobdate = str(jsondata["date"])
jobName = PublicFun.SQLFilter(str(jsondata["jobName"]))
jobURL = str(jsondata["jobURL"])
jobAREA = str(jsondata["jobAREA"])
companyInfo = jsondata["companyInfo"]
try:
companyName = PublicFun.SQLFilter(str(companyInfo["companyName"]))
except Exception as ex:
companyInfo = PublicFun.StringToJson(companyInfo, Default=False)
companyName = PublicFun.SQLFilter(str(companyInfo["companyName"]))
ScanDate = str(jsondata["ScanDate"])
DBConnect = SQLConnect.DBConnect(publicSetting=True)
DBConnect.ConnectDB()
CompanyGUID = BIASDataIO.CheckCompanyMappingList(
DBConnect, companyName)