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)