def loop(self):
"""Sends camera images in an infinite loop."""
sio = io.BytesIO()
byte_io = io.BytesIO()
testbytes = b''
if args.use_usb:
_, frame = camera.read()
img = Image.fromarray(cv2.cvtColor(frame, cv2.COLOR_BGR2RGB))
img.save(sio, "JPEG")
else:
frame = camera.capture(sio, "jpeg", use_video_port=True)
data = np.fromstring(sio.getvalue(), dtype=np.uint8)
image = cv2.imdecode(data, 1)
# Prepare input blob and perform an inference
blob = cv2.dnn.blobFromImage(image, size=(672, 384), ddepth=cv2.CV_8U)
net.setInput(blob)
out = net.forward()
print('what is the image?')
print(type(image))
# png_buffer = byte_io.getvalue()
# byte_io.close()
# Draw detected faces on the frame
for detection in out.reshape(-1, 7):
confidence = float(detection[2])
xmin = int(detection[3] * image.shape[1])
ymin = int(detection[4] * image.shape[0])
xmax = int(detection[5] * image.shape[1])
ymax = int(detection[6] * image.shape[0])
if confidence > 0.5:
print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
cv2.rectangle(image, (xmin, ymin), (xmax, ymax), color=(0, 255, 0))
#img_crop_pil = Image.fromarray(out)
# img_crop_pil = Image.fromarray(out.astype('uint8'), 'RGB')
# TODO no error but no image
ret, jpeg = cv2.imencode('.jpg', image)
testbytes = jpeg.tobytes()
# byte_io = io.BytesIO(testbytes)
# img_crop_pil.save(byte_io, "JPEG")
try:
# self.write_message(base64.b64encode(sio.getvalue()))
self.write_message(base64.b64encode(testbytes))
except tornado.websocket.WebSocketClosedError:
self.camera_loop.stop()
def get_frame(socketio):
camera = picamera.PiCamera()
camera.start_preview()
camera.resolution(720, 720)
camera.framerate = 40
camera.hflip = True
camera.vflip = True
camera.start_preview()
# camera = cv2.VideoCapture(0)
# camera.set(cv2.CAP_PROP_FPS, 30)
if not camera.isOpened():
raise RuntimeError('Could not start camera.')
sio = io.BytesIO()
while True:
time.sleep(0.1)
_, frame = camera.read()
img = Image.fromarray(cv2.cvtColor(frame, cv2.COLOR_BGR2RGB))
img.save(sio, format="JPEG")
# sio.seek(0)
# encode as jpg and return
# return cv2.imencode('.jpg', img)[1].tobytes()
socketio.emit('videoStart', {
"data": base64.b64encode(sio.getvalue())
})
camera.stop_preview()
def loadImage(self, image):
""""""
image_name = os.path.basename(image)
print(image)
if 1:
imageFile = image
data = open(imageFile, "rb").read()
# convert to a data stream
stream = cStringIO.BytesIO(data)
img = wx.ImageFromStream(stream)
#pp(dir(im))
W = img.GetWidth()
H = img.GetHeight()
print('wxPython:', img.GetSize(), W, H)
if 1:
stream.seek(0)
cv = cv2.imdecode(
np.asarray(bytearray(stream.read()), dtype=np.uint8), 0)
o_h, o_w = cv.shape
print('cv2:', o_h, o_w, self.dh, self.dw)
# convert to a bitmap
#img = wx.Image(image, wx.BITMAP_TYPE_JPEG)
# scale the image, preserving the aspect ratio
W = img.GetWidth()
H = img.GetHeight()
print(W, H, img.GetSize())
if 0:
if W > H:
NewW = self.photoMaxSize
NewH = self.photoMaxSize * H / W
else:
NewH = self.photoMaxSize
NewW = self.photoMaxSize * W / H
img = img.Scale(NewW, NewH)
if o_h > o_w and 1:
img_centre = wx.Point(img.GetWidth() / 2, img.GetHeight() / 2)
img = img.Rotate(math.pi / 2, img_centre)
if o_h > self.dh:
NewH = self.dh
NewW = o_w * self.dh / o_h
print('new:', NewH, NewW)
img = img.Scale(NewW, NewH)
else:
NewH = self.dh
NewW = o_w * self.dh / o_h
print('new:', NewH, NewW)
img = img.Scale(NewW, NewH)
self.imageCtrl.SetBitmap(wx.BitmapFromImage(img))
self.imageLabel.SetLabel(image_name)
self.NewH = NewH
self.NewW = NewW
self.Refresh()
pub.sendMessage("resize", msg="")
def __init__(self, parent, id):
# create the panel
wx.Panel.__init__(self, parent, id)
try:
# pick a .jpg file you have in the working folder
imageFile = 'test.JPG'
data = open(imageFile, "rb").read()
# convert to a data stream
stream = cStringIO.BytesIO(data)
# convert to a bitmap
#bmp = wx.BitmapFromImage( wx.ImageFromStream( stream ))
# show the bitmap, (5, 5) are upper left corner coordinates
#wx.StaticBitmap(self, -1, bmp, (5, 5))
# alternate (simpler) way to load and display a jpg image from a file
# actually you can load .jpg .png .bmp or .gif files
jpg1 = wx.Image(imageFile, wx.BITMAP_TYPE_ANY).ConvertToBitmap()
# bitmap upper left corner is in the position tuple (x, y) = (5, 5)
#wx.StaticBitmap(self, -1, jpg1, (10 + jpg1.GetWidth(), 5), (jpg1.GetWidth(), jpg1.GetHeight()))
wx.StaticBitmap(self, -1, jpg1, (5, 5))
except IOError:
print("Image file %s not found" % imageFile)
raise SystemExit
def unzip(contents):
mod_logging.debug('Unzipping %s bytes' % len(contents))
zip_file = mod_zipfile.ZipFile(mod_cstringio.BytesIO(contents))
zip_info_list = zip_file.infolist()
zip_info = zip_info_list[0]
result = zip_file.open(zip_info).read()
mod_logging.debug('Unzipped')
return result
def loop(self):
"""Sends camera images in an infinite loop."""
sio = io.BytesIO()
camera.capture(sio, "jpeg", use_video_port=True)
try:
self.write_message(base64.b64encode(sio.getvalue()))
except tornado.websocket.WebSocketClosedError:
self.camera_loop.stop()
def _getServiceInfo(self, services):
'''
Gets service info through the RPC interface of the service.
This method blocks until the info is retrieved or socket timeout is reached (0.5 seconds).
:param service: the name of the service
:type service: str
:param uri: the uri of the service
:type uri: str
'''
for (service, uri) in services.items():
with self._lock:
if service in self.__cached_services:
if time.time() - self.__cached_services[service][2] < self.MAX_PING_SEC:
return
if uri is not None:
dest_addr = dest_port = None
try:
dest_addr, dest_port = rospy.parse_rosrpc_uri(uri)
except:
continue
# raise ROSServiceException("service [%s] has an invalid RPC URI [%s]"%(service, uri))
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
# connect to service and probe it to get the headers
s.settimeout(0.5)
s.connect((dest_addr, dest_port))
header = {'probe': '1', 'md5sum': '*',
'callerid': self.ros_node_name, 'service': service}
roslib.network.write_ros_handshake_header(s, header)
buf = io.StringIO() if sys.version_info < (3, 0) else io.BytesIO()
stype = roslib.network.read_ros_handshake_header(s, buf, 2048)
with self._lock:
self.__new_master_state.getService(service).type = stype['type']
self.__cached_services[service] = (uri, stype['type'], time.time())
except socket.error:
with self._lock:
try:
del self.__cached_services[service]
except:
pass
# raise ROSServiceIOException("Unable to communicate with service [%s], address [%s]"%(service, uri))
except:
with self._lock:
self._limited_log(service, "can't get service type: %s" % traceback.format_exc(), level=rospy.DEBUG)
with self._lock:
try:
del self.__cached_services[service]
except:
pass
pass
finally:
if s is not None:
s.close()
def rotateImg(self, img, degree):
self.angle += degree
ROI = self.rotate(img, self.angle)
o_h, o_w = ROI.shape
#print('ROI:',o_h, o_w )
retval, buffer = cv2.imencode('.jpg', ROI)
img2 = wx.Image(cStringIO.BytesIO(buffer.tobytes()))
return img2
def cameraLoop(self):
"""Sends camera images in an infinite loop."""
#sio = io.BytesIO()
if v.processing == False:
is_success, im_buf_arr = cv2.imencode(".jpg", v.image)
sio = io.BytesIO(im_buf_arr)
try:
self.write_message(base64.b64encode(sio.getvalue()))
except tornado.websocket.WebSocketClosedError:
self.camera_loop.stop()
v.imageProcessing()
def capture_image(self):
if self.initialized:
if ON_RASPI:
sio = io.BytesIO()
self.camera.capture(sio, "jpeg", use_video_port=True)
buffer = sio.getvalue()
else:
_, frame = self.camera.read()
_, buffer = cv2.imencode('.jpg', frame)
return buffer
else:
raise RuntimeError('Camera manager is not initialized!')
def resizeRotated(self):
stream.seek(0)
cv = cv2.imdecode(np.asarray(bytearray(stream.read()), dtype=np.uint8),
0)
o_h, o_w = cv.shape
#print('cv2:',o_h, o_w )
if 1:
x, y, w, h = 0, 0, 500, 500
ROI = cv[y:y + h, x:x + w]
o_h, o_w = ROI.shape
#print('ROI:',o_h, o_w )
retval, buffer = cv2.imencode('.jpg', ROI)
img2 = wx.Image(cStringIO.BytesIO(buffer.tobytes()))
def loadImage(self, imgpath):
data = open(opj(imgpath), "rb").read()
stream = cStringIO.BytesIO(data)
img = wx.Image(stream)
if 1: #scale
W = img.GetWidth()
H = img.GetHeight()
if 1:
self.cvimg = cv2.imdecode(np.frombuffer(data, np.uint8), 0)
o_h, o_w = self.cvimg.shape
self.W, self.H = img.GetSize()
return img
def loop(self):
"""Sends camera images in an infinite loop."""
lock.acquire()
try:
if args.detection == 'Yes':
if frame_jpeg is not None:
self.write_message(base64.b64encode(frame_jpeg))
else:
sio = io.BytesIO()
camera.capture(sio, "jpeg", use_video_port=True)
self.write_message(base64.b64encode(sio.getvalue()))
except tornado.websocket.WebSocketClosedError:
self.camera_loop.stop()
lock.release()
def loop(self):
"""Sends camera images in an infinite loop."""
sio = io.BytesIO()
if args.use_usb:
_, frame = camera.read()
img = Image.fromarray(cv2.cvtColor(frame, cv2.COLOR_BGR2RGB))
img.save(sio, "JPEG")
else:
camera.capture(sio, "jpeg", use_video_port=True)
try:
self.write_message(base64.b64encode(sio.getvalue()))
except tornado.websocket.WebSocketClosedError:
self.camera_loop.stop()
def handle_webcam_request():
# NOTE Module 'cv2' has no 'imencode' member -- pylint(no-member)
# pylint: disable=no-member
if camera is not None:
if ON_RASPI:
sio = io.BytesIO()
camera.capture(sio, "jpeg", use_video_port=True)
buffer = sio.getvalue()
else:
_, frame = camera.read()
_, buffer = cv2.imencode('.jpg', frame)
b64 = base64.b64encode(buffer)
print('webcam buffer in bytes:', len(b64))
emit('webcam-response', b64)
def frames():
with picamera.PiCamera() as camera:
# let camera warm up
time.sleep(2)
stream = io.BytesIO()
for foo in camera.capture_continuous(
stream,
'jpeg',
use_video_port=True):
# return current frame
stream.seek(0)
yield stream.read()
# reset stream for next frame
stream.seek(0)
stream.truncate()
def regenerateHumanReadable(iris, outfile):
"""
Generate human readable, tabular version of the IRIS database.
:param iris: Query response object containing result string returned from IRIS query.
:type iris: requests.Response
:param outfile: Output text file name
:type outfile: str
"""
print("Generating human readable version...")
from seismic.inventory.pdconvert import inventory2Dataframe
import pandas as pd
from obspy import read_inventory
if sys.version_info[0] < 3:
import cStringIO as sio # pylint: disable=import-error
else:
import io as sio
print(" Ingesting query response into obspy...")
# This code removes all instances of the substrings matching KNOWN_ILLEGAL_ELEMENTS_PATTERNS from iris.text,
# then encodes it as UTF-8.
matcher = re.compile("|".join(KNOWN_ILLEGAL_ELEMENTS_PATTERNS))
iris_str = matcher.sub("", iris.text).encode('utf-8')
obspy_input = sio.BytesIO(iris_str)
try:
station_inv = read_inventory(obspy_input)
except:
DUMPFILE = 'fdsn_stn_inv_dump.xml'
print(
"FAILED ingesting server response into obspy, dumping server response string to "
+ DUMPFILE)
with open(DUMPFILE, 'w') as f:
f.write(iris_str.decode('utf-8'))
raise
print(" Converting to dataframe...")
inv_df = inventory2Dataframe(station_inv)
with pd.option_context("display.max_rows", None, "display.max_columns",
None, "display.width", 1000):
print(" Converting to tabular text file " + outfile)
inv_str = str(inv_df)
with open(outfile, "w") as f:
f.write(inv_str)
def _LoadDicts():
# Load the dictionary from a .zip file if that is where we live.
if is_zip:
import cStringIO as io
loader = win32com.__loader__
arc_path = loader.archive
dicts_path = os.path.join(win32com.__gen_path__, "dicts.dat")
if dicts_path.startswith(arc_path):
dicts_path = dicts_path[len(arc_path) + 1:]
else:
# Hm. See below.
return
try:
data = loader.get_data(dicts_path)
except AttributeError:
# The __loader__ has no get_data method. See below.
return
except IOError:
# Our gencache is in a .zip file (and almost certainly readonly)
# but no dicts file. That actually needn't be fatal for a frozen
# application. Assuming they call "EnsureModule" with the same
# typelib IDs they have been frozen with, that EnsureModule will
# correctly re-build the dicts on the fly. However, objects that
# rely on the gencache but have not done an EnsureModule will
# fail (but their apps are likely to fail running from source
# with a clean gencache anyway, as then they would be getting
# Dynamic objects until the cache is built - so the best answer
# for these apps is to call EnsureModule, rather than freezing
# the dict)
return
f = io.BytesIO(data)
else:
# NOTE: IOError on file open must be caught by caller.
f = open(os.path.join(win32com.__gen_path__, "dicts.dat"), "rb")
try:
p = pickle.Unpickler(f)
version = p.load()
global clsidToTypelib
clsidToTypelib = p.load()
versionRedirectMap.clear()
finally:
f.close()
def loop(self):
"""Sends camera images in an infinite loop."""
sio = io.BytesIO()
_, frame = camera.read()
if frame is None:
return
width, height, _ = frame.shape
frame = cv2.cvtColor(frame, cv2.COLOR_BGR2RGB)
_, draw_frame = yolo.detect(frame)
draw_frame = cv2.resize(draw_frame, (height, width))
img = Image.fromarray(draw_frame)
img.save(sio, "JPEG")
try:
self.write_message(base64.b64encode(sio.getvalue()))
except tornado.websocket.WebSocketClosedError:
self.camera_loop.stop()
def serialize_query_result_to_xlsx(query_result):
s = cStringIO.BytesIO()
query_data = json_loads(query_result.data)
book = xlsxwriter.Workbook(s, {'constant_memory': True})
sheet = book.add_worksheet("result")
column_names = []
for (c, col) in enumerate(query_data['columns']):
sheet.write(0, c, col['name'])
column_names.append(col['name'])
for (r, row) in enumerate(query_data['rows']):
for (c, name) in enumerate(column_names):
v = row.get(name)
if isinstance(v, list) or isinstance(v, dict):
v = str(v).encode('utf-8')
sheet.write(r + 1, c, v)
book.close()
return s.getvalue()
def test_private_actions(self):
"""
Test all the stuff that requires a login.
"""
# Get an editable document
obj_id = self.get_editable_document(self.version)
obj = self.private_client.documents.get(obj_id)
# Make sure `data` attribute will only accept a dictionary.
obj.data = dict(foo='bar')
self.assertRaises(TypeError, obj.set_data, "string")
self.assertRaises(TypeError, obj.set_data, 666)
self.assertRaises(TypeError, obj.set_data, obj)
# Test whether we can put random noise to all the editable fields.
title = get_random_string()
source = get_random_string()
description = get_random_string()
data = {get_random_string(): get_random_string()}
if obj.resources.related_article == 'http://documents.latimes.com':
related_article = 'http://documentcloud.org'
else:
related_article = 'http://documents.latimes.com'
if obj.resources.published_url == 'http://documents.latimes.com':
published_url = 'http://documentcloud.org'
else:
published_url = 'http://documents.latimes.com'
obj.title = title
obj.source = source
obj.description = description
obj.data = data
obj.resources.related_article = related_article
obj.resources.published_url = published_url
# Save the changes up to DocumentCloud
obj.put()
# Pull the object again and verify the changes stuck
obj = self.private_client.documents.get(obj_id)
self.assertEqual(obj.title, title)
self.assertEqual(obj.source, source)
self.assertEqual(obj.description, description)
self.assertEqual(obj.data, data)
self.assertEqual(obj.resources.related_article, related_article)
self.assertEqual(obj.resources.published_url, published_url)
# Test reserved namespaces to make sure they're protected
black_list = [
'person',
'organization',
'place',
'term',
'email',
'phone',
'city',
'state',
'country',
'title',
'description',
'source',
'account',
'group',
'project',
'projectid',
'document',
'access',
'filter',
]
for key in black_list:
self.assertRaises(ValueError, setattr, obj, "data", {key: 'foo'})
obj.data = dict(boom='bap')
# Test to make sure non-strings can't get into the data dictionary
with self.assertRaises(TypeError):
obj.data = dict(a=1)
with self.assertRaises(TypeError):
obj.data = {1: 'a'}
obj.data = dict(boom='bap')
with self.assertRaises(TypeError):
obj.data[1] = 2
# Resources
self.assertEqual(obj.published_url, obj.resources.published_url)
self.assertEqual(obj.related_article, obj.resources.related_article)
# And their shortcuts
obj.published_url = 'http://latimes.com'
obj.related_article = 'http://palewi.re'
self.assertEqual(obj.published_url, obj.resources.published_url)
self.assertEqual(obj.related_article, obj.resources.related_article)
# Test whether the save method properly aliases `put`.
title = get_random_string()
obj.title = title
obj.save()
obj = self.private_client.documents.get(obj_id)
self.assertEqual(obj.title, title)
# Test whether you can save an attribute with some weird encoding
before_title = copy(obj.title)
before_description = copy(obj.description)
obj.title = random.choice(list(PANGRAMS.keys()))
obj.description = random.choice(list(PANGRAMS.keys()))
obj.put()
obj.title = before_title
obj.description = before_description
obj.put()
# Upload
title = get_random_string()
obj = self.private_client.documents.upload(
os.path.join(os.path.dirname(__file__), "test.pdf"),
title,
description='Blah blah',
related_article='http://www.latimes.com',
data=dict(like_this='like+that', boom='bap'),
)
self.assertTrue(isinstance(obj, Document))
self.assertEqual(obj.title, title)
self.assertEqual(obj.description, 'Blah blah')
self.assertEqual(obj.related_article, 'http://www.latimes.com')
self.assertEqual(obj.data, {
u'like_this': u'like+that',
u'boom': u'bap'
})
# Delete
obj.delete()
self.assertRaises(DoesNotExistError, self.private_client.documents.get,
obj.id)
# Test upload with bad keyword
title = '001 - Test upload (%s)' % get_random_string()
self.assertRaises(
ValueError,
self.private_client.documents.upload,
os.path.join(os.path.dirname(__file__), "test.pdf"),
title,
description='Blah blah',
related_article='http://www.latimes.com',
# Access is an reserved keyword so this should fail
data=dict(access='this', boom='bap'),
)
# Upload with a file object, not a path
title = get_random_string()
obj = self.private_client.documents.upload(
open(os.path.join(os.path.dirname(__file__), "test.pdf"), "rb"),
title,
)
self.assertTrue(isinstance(obj, Document))
self.assertEqual(obj.title, title)
obj.delete()
# Ensure that documents with non-english characters can be uploaded
pdf = os.path.join(os.path.dirname(__file__), "español.pdf")
obj = self.private_client.documents.upload(open(pdf, 'rb'))
self.assertTrue(isinstance(obj, Document))
obj.delete()
# Test virtual file upload and delete
path = os.path.join(os.path.dirname(__file__), "español.pdf")
real_file = open(path, 'rb')
if six.PY3:
virtual_file = io.BytesIO(real_file.read())
else:
virtual_file = io.StringIO(real_file.read())
obj = self.private_client.documents.upload(virtual_file,
title='Espanola!')
self.assertTrue(isinstance(obj, Document))
obj.delete()
# Test secure upload
title = get_random_string()
obj = self.private_client.documents.upload(
os.path.join(os.path.dirname(__file__), "test.pdf"),
title,
secure=True,
)
self.assertTrue(isinstance(obj, Document))
obj.delete()
# Upload everything in this directory.
obj_list = self.private_client.documents.upload_directory(
'./',
source='Los Angeles Times',
published_url='http://www.latimes.com',
)
self.assertEqual(len(obj_list), 2)
self.assertTrue(isinstance(obj_list[0], Document))
self.assertEqual(obj_list[0].source, 'Los Angeles Times')
self.assertEqual(obj_list[0].published_url, 'http://www.latimes.com')
[i.delete() for i in obj_list]
# Test URL upload
url = 'http://ord.legistar.com/Chicago/attachments/e3a0cbcb-044d-4ec3-9848-23c5692b1943.pdf'
obj = self.private_client.documents.upload(url)
obj.delete()
def BytesIO(*args, **kwargs):
if PYTHON_3: return cStringIO.BytesIO(*args, **kwargs)
else: return cStringIO.StringIO(*args, **kwargs)
def convertToCSV(zipFileName,outputFileName):
if os.path.exists(outputFileName):
return
output = open(outputFileName,'w')
if not zipfile.is_zipfile(zipFileName):
print("not zip file")
return
print(zipFileName)
frameIndex = 0
frameNumber = -1
with zipfile.ZipFile(zipFileName, 'r') as post:
for info in post.infolist():
if sys.version_info.major ==2:
buf = ""
f = cStringIO.StringIO(post.read(info.filename))
else:
buf = b""
f = cStringIO.BytesIO(post.read(info.filename))
for line in f:
buf += line
fn = info.filename.split("_")[2]
fn = int(fn.replace(".pos",""))
step = 1
if frameNumber != -1:
while fn < frameNumber:
fn += 30
step = fn - frameNumber
fn = info.filename.split("_")[2]
fn = int(fn.replace(".pos",""))
frameNumber = fn
dataCount = int(len(buf)/308)
# print "DataCount:"+str(dataCount)
frameIndex += step
for j in range(0,dataCount):
output.write(str(frameIndex))
output.write(",")
index = 0 + j*308
val = struct.unpack("Q",buf[index:index+8])
# print "ID:"+str(val[0])
output.write(str(val[0]))
output.write(",")
for i in range(1,26):
index = j*308+ 8+(i-1)*(12)
xVal=buf[index:index+4]
xVal = struct.unpack("f",xVal)
index = index+4
yVal=buf[index:index+4]
yVal = struct.unpack("f",yVal)
index = index+4
zVal=buf[index:index+4]
zVal = struct.unpack("f",zVal)
output.write(str(xVal[0]))
output.write(",")
output.write(str(yVal[0]))
output.write(",")
output.write(str(zVal[0]))
output.write(",")
output.write("\n")
output.close()
def export_xls(request):
response = HttpResponse(content_type='application/vnd.ms-excel') # 指定返回为excel文件
response['Content-Disposition'] = 'attachment;filename=export_list.xls' # 指定返回文件名
wb = xlwt.Workbook(encoding='utf-8') # 设定编码类型为utf8
hosttype_id1 = 1 # workstation
hosttype_id2 = 2 #laptop
hosttype_id3 = 3 #desktop
hosttype_id4 = 4 #server
hosttype_id5 = 5 #switch
host_status1 = "in use" # was using by users
host_status2 = "inventory" # in space , no user
host_status3 = "P-Dispose" # apply for disposal,waiting for approval
host_status4 = "disposed" # disposed , but it is till in use
if request.method=='GET':
id=request.GET.get('id')
if id == '1':
sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s \
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
and (host_status = '%s' or host_status='%s' )order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status1, host_status2)
sheet = wb.add_sheet(u'01 Deployed') # excel里添加类别
if id == '2':
sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s \
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' ) \
and (host_status = '%s' or host_status='%s' )order by h.id, p.host_name """ % (hosttype_id4, host_status1, host_status2)
sheet = wb.add_sheet(u'02 Servers') # excel里添加类别
if id == '3':
IBM_ASS='%CNSHZ1BPC%'
sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s \
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' ) \
and host_name like '%s' order by h.id, p.host_name """ % (hosttype_id1,IBM_ASS)
sheet = wb.add_sheet(u'03 IBM Assets') # excel里添加类别
if id == '4':
sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s \
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
and (host_status = '%s' )order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status3)
sheet = wb.add_sheet(u'04 Pending Disposal') # excel里添加类别
if id == '5':
age_year=4 # 使用期超过四年的电脑
public_user='******' # public 用户名，在导出清单时要排队公用电脑
sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s \
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
and (host_status = '%s' ) and u.user_name<>'%s' and TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE())>=%s order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status1,public_user,age_year)
sheet = wb.add_sheet(u'05 Refresh List') # excel里添加类别
else:
print('error for export excel')
#conn.cursor().execute(sql) # 执行sql语句
#conn.cursor().fetchall() # 获取查询结果
data_list = exc_sql(sql)
#sheet = wb.add_sheet(u'清单') # excel里添加类别
style_heading = xlwt.easyxf("""
font:
name Calibri,
colour_index white,
bold on,
height 0xA0;
align:
wrap off,
vert center,
horiz center;
pattern:
pattern solid,
fore-colour 0x19;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
"""
)
style_body = xlwt.easyxf("""
font:
name Calibri,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
"""
)
style_num_col = xlwt.easyxf("""
font:
name Arial,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
"""
)
style_green = xlwt.easyxf(" pattern: pattern solid,fore-colour 0x11;")
style_red = xlwt.easyxf(" pattern: pattern solid,fore-colour 0x0A;")
fmts = [
'M/D/YY',
'D-MMM-YY',
'D-MMM',
'MMM-YY',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:ss',
'M/D/YY h:mm',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
]
style_body.num_format_str = fmts[0]
sheet.write(0, 0, 'Host Name', style_heading)
sheet.write(0, 1, 'Service Tag', style_heading)
sheet.write(0, 2, 'Type', style_heading)
sheet.write(0, 3, 'Model', style_heading)
sheet.write(0, 4, 'Receive Date', style_heading)
sheet.write(0, 5, 'Today', style_heading)
sheet.write(0, 6, 'Age(Yr)', style_heading)
sheet.write(0, 7, 'Age(M)', style_heading)
sheet.write(0, 8, 'Users', style_heading)
sheet.write(0, 9, 'BL', style_heading)
sheet.write(0, 10, 'Studio(if Applicable)', style_heading)
sheet.write(0, 11, 'Location', style_heading)
sheet.write(0, 12, 'Status', style_heading)
sheet.write(0, 13, 'FA Code', style_heading)
sheet.write(0, 14, 'Remark', style_heading)
row = 1
for list in data_list:
sheet.write(row, 0, list[0], style_body)
sheet.write(row, 1, list[1], style_body)
sheet.write(row, 2, list[2], style_body)
sheet.write(row, 3, list[3], style_body)
sheet.write(row, 4, list[4], style_body)
sheet.write(row, 5, list[5], style_body)
sheet.write(row, 6, list[6], style_num_col )
sheet.write(row, 7, list[7], style_num_col)
sheet.write(row, 8, list[8], style_body)
sheet.write(row, 9, list[9], style_body)
sheet.write(row, 10, list[10], style_body)
sheet.write(row, 11, list[11], style_body)
sheet.write(row, 12, list[12], style_body)
sheet.write(row, 13, list[13], style_num_col)
sheet.write(row, 14, list[14], style_body)
row = row + 1
#output = StringIO.StringIO()
output = stringIOModule.BytesIO()
wb.save(output)
output.seek(0)
response.write(output.getvalue())
return response
def parse_configuration_file(configuration_file):
if configuration_file.endswith('.zip'):
glob.Debug.debug(
'[DEBUG] Zip package detected as config, will try and extract it temporarily...'
)
if not configuration_file.startswith(
'http') and not configuration_file.startswith('file://'):
configuration_file = 'file://' + configuration_file
glob.Debug.debug(
'[DEBUG] Opening and parsing configuration file in zip "' +
configuration_file + '"...')
try:
decided_config = False
# Read zip-file
zip = None
if is_python_3:
remotezip = urlopen(configuration_file)
remotezip_read = remotezip.read()
zipinmemory = io.BytesIO(remotezip_read)
zip = zipfile.ZipFile(zipinmemory, 'r')
else:
remotezip = urlopen(configuration_file)
remotezip_read = remotezip.read()
zipinmemory = io.StringIO(remotezip_read)
zip = zipfile.ZipFile(zipinmemory, 'r')
# Create tmp directory with these files
glob.Debug.debug(
'[DEBUG] Creating temporary folder for zip-contents: "' +
runtime_tmp_id + '"')
extra_functions.FileHelper.create_directory(runtime_tmp_id)
for fn in zip.namelist():
binary_contents = zip.read(fn)
extra_functions.FileHelper.write_bytes_to_file(
runtime_tmp_id + '/' + fn, binary_contents)
if fn.endswith('platform-route.txt'):
configuration_file = get_configuration_in_route(
extra_functions.FileHelper.get_file_contents(
runtime_tmp_id + '/' + fn))
decided_config = True
elif not decided_config and fn.replace(
'.txt', '.zip') in configuration_file:
configuration_file = runtime_tmp_id + '/' + fn
return parse_configuration_file(configuration_file)
except HTTPError:
print(
extra_functions.ColorOutput.get_colored(
'ERROR Configuration file couldn\'t load...'))
exit_notifier()
glob.Debug.debug('[DEBUG] Checking if configuration file "' +
configuration_file + '" exists...')
if extra_functions.FileHelper.does_file_exist(configuration_file):
glob.Debug.debug('[DEBUG] Opening and parsing configuration file "' +
configuration_file + '"...')
contents = extra_functions.FileHelper.get_file_contents(
configuration_file)
parsed_configuration = parse_configuration_contents(contents)
if len(parsed_configuration.commands) == 0:
print(
extra_functions.ColorOutput.get_colored(
'ERROR Configuration file don\'t have any commands to run...'
))
exit_notifier()
return parsed_configuration
else:
print(
extra_functions.ColorOutput.get_colored(
'ERROR: The specified file don\'t exist or couldn\'t be opened'
))
exit_notifier()
def export2xls(request):
response = HttpResponse(
content_type='application/vnd.ms-excel') # 指定返回为excel文件
#response['Content-Disposition'] = 'attachment;filename=export_list.xls' # 指定返回文件名
wb = xlwt.Workbook(encoding='utf-8') # 设定编码类型为utf8
hosttype_id1 = 1 # workstation
hosttype_id2 = 2 #laptop
hosttype_id3 = 3 #desktop
hosttype_id4 = 4 #server
hosttype_id5 = 5 #switch
host_status1 = "in use" # was using by users
host_status2 = "inventory" # in space , no user
host_status3 = "P-Dispose" # apply for disposal,waiting for approval
host_status4 = "disposed" # disposed , but it is till in use
IBM_ASS_PC = '%BPC%'
IBM_ASS_LT = '%BLT%'
if request.method == 'GET':
id = request.GET.get('id')
#site_id=request.GET.get('site')
site_id = request.session.get('current_site')
list_version = request.GET.get('ver')
sql_plus = ''
if list_version == 'plus':
sql_plus = """ ,u.user_name, u.email_address, p.seat_no, p.mac """
sql_base = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) ,
TIMESTAMPDIFF(MONTH, p.receive_date,CURDATE())%12 ,u.full_name ,b.bl_name, d.dept_name, s.sitename as location, p.host_status, p.asset_code, p.remark """ + sql_plus + \
""" from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s ,deviceman_bl_list as b
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and b.id=d.bl_list_id """
# 01 Deployed （工作站、笔记本、台式机清单）
if id == '1':
response[
'Content-Disposition'] = 'attachment;filename=01 Stationery.xls' # 指定返回文件名
sql = 'SELECT s.name,t.name,s.stock_num,s.alert_num FROM deviceman.stationery_stationery as s, deviceman.stationery_stat_type as t where s.stat_type_id=t.id'
sheet = wb.add_sheet(u'01 Stationery') # excel里添加类别
#02 Servers （服务器清单）
if id == '2':
response[
'Content-Disposition'] = 'attachment;filename=02 Application.xls' # 指定返回文件名
sql = "SELECT u.full_name,d.dept_name, st.name,sl.order_num, m.date FROM deviceman.stationery_order_record_master as m, deviceman.deviceman_user_list as u, deviceman.deviceman_dept_list as d,deviceman.stationery_order_record_slave as sl, deviceman.stationery_stationery as st where m.user_list_id=u.id and u.dept_list_id=d.id and sl.order_record_master_id=m.id and sl.stationery_id=st.id and m.order_status='Completed'"
sheet = wb.add_sheet(u'02 application') # excel里添加类别
#03 IBM Assets （IBM资产清单）
if id == '3':
response[
'Content-Disposition'] = 'attachment;filename=03 Alert List.xls' # 指定返回文件名
sql = 'SELECT s.name,t.name,s.stock_num,s.alert_num FROM deviceman.stationery_stationery as s, deviceman.stationery_stat_type as t where s.stat_type_id=t.id and s.stock_num < s.alert_num'
sheet = wb.add_sheet(u'03 Alert List') # excel里添加类别
#04 Pending Disposal （待报废资产清单）
if id == '4':
response[
'Content-Disposition'] = 'attachment;filename=04 Pending Disposal.xls' # 指定返回文件名
# sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
# TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , b.bl_name,d.dept_name, s.sitename as location,p.host_status, \
# p.asset_code,p.remark \
# from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s ,deviceman_bl_list as b\
# where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and b.id=d.bl_list_id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
# and (host_status = '%s' ) and p.site_id='%s' order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status3,site_id)
#
sql = sql_base + """ and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' )
and (host_status = '%s' ) and p.site_id='%s' order by h.id, p.host_name """ % (
hosttype_id1, hosttype_id2, hosttype_id3, host_status3,
site_id)
sheet = wb.add_sheet(u'04 Pending Disposal') # excel里添加类别
#05 Refresh list （超过四年且有用户使用的电脑）
if id == '5':
response[
'Content-Disposition'] = 'attachment;filename=05 Refresh list.xls' # 指定返回文件名
age_year = 4 # 使用期超过四年的电脑
public_user = '******' # public 用户名，在导出清单时要排队公用电脑
# sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
# TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , b.bl_name,d.dept_name, s.sitename as location,p.host_status, \
# p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d,deviceman_bl_list as b, deviceman_hosttype as h, deviceman_site as s \
# where p.user_list_id=u.id and u.dept_list_id=d.id and d.bl_list_id=b.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
# and (host_status = '%s' ) and u.user_name<>'%s' and TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE())>=%s and p.site_id='%s' order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status1,public_user,age_year,site_id)
#
sql = sql_base + """and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' )
and (host_status = '%s' ) and u.user_name<>'%s' and TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE())>=%s and p.site_id='%s' order by h.id, p.host_name """ % (
hosttype_id1, hosttype_id2, hosttype_id3, host_status1,
public_user, age_year, site_id)
#
sheet = wb.add_sheet(u'05 Refresh List') # excel里添加类别
# 05 Refresh list （超过四年且有用户使用的电脑）
if id == '6':
response[
'Content-Disposition'] = 'attachment;filename=06 Changsha PC list.xls' # 指定返回文件名
#age_year = 4 # 使用期超过四年的电脑
public_user = '******' # public 用户名，在导出清单时要排队公用电脑
# sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
# TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , b.bl_name, d.dept_name, s.sitename as location,p.host_status, \
# p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d,deviceman_bl_list as b, deviceman_hosttype as h, deviceman_site as s \
# where p.user_list_id=u.id and u.dept_list_id=d.id and d.bl_list_id=b.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
# and (host_status = '%s' ) and u.user_name<>'%s' and u.remark like '%s'and p.site_id='%s' order by h.id, p.host_name """ % (
# hosttype_id1, hosttype_id2, hosttype_id3, host_status1, public_user,'%Changsha%',site_id)
#
sql = sql_base + """and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' )
and (host_status = '%s' ) and u.user_name<>'%s' and u.remark like '%s'and p.site_id='%s' order by h.id, p.host_name """ % (
hosttype_id1, hosttype_id2, hosttype_id3, host_status1,
public_user, '%Changsha%', site_id)
sheet = wb.add_sheet(u'06 Changsha PC List') # excel里添加类别
else:
print('error for export excel')
#conn.cursor().execute(sql) # 执行sql语句
#conn.cursor().fetchall() # 获取查询结果
data_list = exc_sql(sql)
print(data_list)
#sheet = wb.add_sheet(u'清单') # excel里添加类别
style_heading = xlwt.easyxf("""
font:
name Calibri,
colour_index white,
bold on,
height 0xA0;
align:
wrap off,
vert center,
horiz center;
pattern:
pattern solid,
fore-colour 0x19;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
style_body = xlwt.easyxf("""
font:
name Calibri,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
style_num_col = xlwt.easyxf("""
font:
name Arial,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
style_green = xlwt.easyxf(" pattern: pattern solid,fore-colour 0x11;")
style_red = xlwt.easyxf(" pattern: pattern solid,fore-colour 0x0A;")
fmts = [
'M/D/YY',
'D-MMM-YY',
'D-MMM',
'MMM-YY',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:ss',
'M/D/YY h:mm',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
]
style_body.num_format_str = fmts[0]
if id == '1':
sheet.write(0, 0, 'Stationery Name', style_heading)
sheet.write(0, 1, 'Type Name', style_heading)
sheet.write(0, 2, 'Stock Num', style_heading)
sheet.write(0, 3, 'Alert Num', style_heading)
row = 1
for list in data_list:
sheet.write(row, 0, list[0], style_body)
sheet.write(row, 1, list[1], style_body)
sheet.write(row, 2, list[2], style_num_col)
sheet.write(row, 3, list[3], style_num_col)
row = row + 1
if id == '2':
sheet.write(0, 0, 'Full Name', style_heading)
sheet.write(0, 1, 'Deparment', style_heading)
sheet.write(0, 2, 'Statioinery', style_heading)
sheet.write(0, 3, 'Num', style_heading)
sheet.write(0, 4, 'Date', style_heading)
row = 1
for list in data_list:
sheet.write(row, 0, list[0], style_body)
sheet.write(row, 1, list[1], style_body)
sheet.write(row, 2, list[2], style_body)
sheet.write(row, 3, list[3], style_num_col)
sheet.write(row, 4, list[4], style_body)
row = row + 1
if id == '3':
sheet.write(0, 0, 'Stationery Name', style_heading)
sheet.write(0, 1, 'Type Name', style_heading)
sheet.write(0, 2, 'Stock Num', style_heading)
sheet.write(0, 3, 'Alert Num', style_heading)
row = 1
for list in data_list:
sheet.write(row, 0, list[0], style_body)
sheet.write(row, 1, list[1], style_body)
sheet.write(row, 2, list[2], style_num_col)
sheet.write(row, 3, list[3], style_num_col)
row = row + 1
#output = StringIO.StringIO()
output = stringIOModule.BytesIO()
wb.save(output)
output.seek(0)
response.write(output.getvalue())
return response
def export_excel(request):
response = HttpResponse(
content_type='application/vnd.ms-excel') # 指定返回为excel文件
# response['Content-Disposition'] = 'attachment;filename=export_list.xls' # 指定返回文件名
wb = xlwt.Workbook(encoding='utf-8') # 设定编码类型为utf8
hosttype_id1 = 1 # workstation
hosttype_id2 = 2 # laptop
hosttype_id3 = 3 # desktop
hosttype_id4 = 4 # server
hosttype_id5 = 5 # switch
# host_status1 = "in use" # was using by users
# host_status2 = "inventory" # in space , no user
# host_status3 = "P-Dispose" # apply for disposal,waiting for approval
# host_status4 = "disposed" # disposed , but it is till in use
IBM_ASS_PC = '%BPC%'
IBM_ASS_LT = '%BLT%'
# sql_base= """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) ,
# TIMESTAMPDIFF(MONTH, p.receive_date,CURDATE())%12 ,u.full_name ,b.bl_name, d.dept_name, s.sitename as location, p.host_status, p.asset_code, p.remark
# from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s ,deviceman_bl_list as b
# where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and b.id=d.bl_list_id """
#
if request.method == 'GET':
hosttype_name = request.GET.get('hosttype')
hosttype_ids = hosttype.objects.filter(name=hosttype_name).values('id')
hosttype_id = hosttype_ids[0].get('id')
host_status = request.GET.get('host_status')
site_id = request.GET.get('site_id')
#site_id = request.session.get('current_site')
# list_version = request.GET.get('ver')
# sql_plus = ''
# if list_version == 'plus':
# sql_plus = """ ,u.user_name, u.email_address, p.seat_no, p.mac """
sql_base = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) ,
TIMESTAMPDIFF(MONTH, p.receive_date,CURDATE())%12 ,u.full_name ,b.bl_name, d.dept_name, s.sitename as location, p.host_status, p.asset_code, p.remark """ \
""" from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s ,deviceman_bl_list as b
where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and b.id=d.bl_list_id """
# 01 Deployed （工作站、笔记本、台式机清单）
if site_id == '0':
site_sql = ''
else:
site_sql = 'and p.site_id=' + str(site_id)
response[
'Content-Disposition'] = 'attachment;filename=Asset_list.xls' # 指定返回文件名
# sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
# TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name ,b.bl_name, d.dept_name, s.sitename as location,p.host_status, p.asset_code,p.remark \
# from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s ,deviceman_bl_list as b\
# where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and b.id=d.bl_list_id \
# and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
# and (host_status = '%s' or host_status='%s' ) and p.host_name not like '%s' and p.site_id='%s' order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status1, host_status2,IBM_ASS,site_id )
sql = sql_base + """ and ( hosttype_id ='%s' )
and (host_status = '%s' ) %s order by h.id, p.host_name """ % (
hosttype_id, host_status, site_sql)
sheet = wb.add_sheet(u'asset list') # excel里添加类别
# conn.cursor().execute(sql) # 执行sql语句
# conn.cursor().fetchall() # 获取查询结果
data_list = exc_sql(sql)
print(data_list)
# sheet = wb.add_sheet(u'清单') # excel里添加类别
style_heading = xlwt.easyxf("""
font:
name Calibri,
colour_index white,
bold on,
height 0xA0;
align:
wrap off,
vert center,
horiz center;
pattern:
pattern solid,
fore-colour 0x19;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
style_body = xlwt.easyxf("""
font:
name Calibri,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
style_num_col = xlwt.easyxf("""
font:
name Arial,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
style_green = xlwt.easyxf(" pattern: pattern solid,fore-colour 0x11;")
style_red = xlwt.easyxf(" pattern: pattern solid,fore-colour 0x0A;")
fmts = [
'M/D/YY',
'D-MMM-YY',
'D-MMM',
'MMM-YY',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:ss',
'M/D/YY h:mm',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
]
style_body.num_format_str = fmts[0]
sheet.write(0, 0, 'Host Name', style_heading)
sheet.write(0, 1, 'Service Tag', style_heading)
sheet.write(0, 2, 'Type', style_heading)
sheet.write(0, 3, 'Model', style_heading)
sheet.write(0, 4, 'Receive Date', style_heading)
sheet.write(0, 5, 'Today', style_heading)
sheet.write(0, 6, 'Age(Yr)', style_heading)
sheet.write(0, 7, 'Age(M)', style_heading)
sheet.write(0, 8, 'Users', style_heading)
sheet.write(0, 9, 'BL', style_heading)
sheet.write(0, 10, 'Studio(Department)', style_heading)
sheet.write(0, 11, 'Location', style_heading)
sheet.write(0, 12, 'Status', style_heading)
sheet.write(0, 13, 'FA Code', style_heading)
sheet.write(0, 14, 'Remark', style_heading)
# if list_version == 'plus':
# sheet.write(0, 15, 'AD Login', style_heading)
# sheet.write(0, 16, 'Email Addr', style_heading)
# sheet.write(0, 17, 'Seat No', style_heading)
# sheet.write(0, 18, 'MAC Addr', style_heading)
row = 1
for list in data_list:
sheet.write(row, 0, list[0], style_body)
sheet.write(row, 1, list[1], style_body)
sheet.write(row, 2, list[2], style_body)
sheet.write(row, 3, list[3], style_body)
sheet.write(row, 4, list[4], style_body)
sheet.write(row, 5, list[5], style_body)
sheet.write(row, 6, list[6], style_num_col)
sheet.write(row, 7, list[7], style_num_col)
sheet.write(row, 8, list[8], style_body)
sheet.write(row, 9, list[9], style_body)
sheet.write(row, 10, list[10], style_body)
sheet.write(row, 11, list[11], style_body)
sheet.write(row, 12, list[12], style_body)
sheet.write(row, 13, list[13], style_num_col)
sheet.write(row, 14, list[14], style_body)
# if list_version == 'plus':
# sheet.write(row, 15, list[15], style_body)
# sheet.write(row, 16, list[16], style_body)
# sheet.write(row, 17, list[17], style_num_col)
# sheet.write(row, 18, list[18], style_body)
row = row + 1
# output = StringIO.StringIO()
output = stringIOModule.BytesIO()
wb.save(output)
output.seek(0)
response.write(output.getvalue())
return response
def detectSSDThread(camera,lock):
global frame_jpeg
import csv
csvfile = csv.reader(open("labels.txt"))
classNames = dict(csvfile)
MODEL_NAME = 'ssd_mobilenet_v1_0.75_depth_300x300_coco14_sync_2018_07_03'
PATH_TO_CKPT = os.getcwd() + '/' + MODEL_NAME +'/frozen_inference_graph.pb'
if not os.path.exists(PATH_TO_CKPT):
downloadDNN(MODEL_NAME)
detection_graph = tf.Graph()
with detection_graph.as_default():
od_graph_def = tf.GraphDef()
with tf.gfile.GFile(PATH_TO_CKPT, 'rb') as fid:
serialized_graph = fid.read()
od_graph_def.ParseFromString(serialized_graph)
tf.import_graph_def(od_graph_def, name='')
font_path = "/usr/share/fonts/truetype/"
ttf = ImageFont.truetype (font_path+'freefont/FreeMono.ttf', 10)
with detection_graph.as_default():
with tf.Session(graph=detection_graph) as sess:
#PREPROCESSING
image_placeholder = tf.placeholder(tf.string)
decode_jpeg = tf.image.decode_jpeg(image_placeholder)
resize_image = tf.image.resize_images(decode_jpeg, [300,300])
expand_image = tf.expand_dims(resize_image, axis=0)
#POSTPROCESSING
image_to_jpeg_placeholder = tf.placeholder(dtype=tf.uint8, shape=[None, None, 3])
jpeg_image = tf.image.encode_jpeg(image_to_jpeg_placeholder)
while True:
sio = io.BytesIO()
lock.acquire()
camera.capture(sio, "jpeg", use_video_port=True)
lock.release()
(frame, image_np_expanded) = sess.run([decode_jpeg, expand_image],feed_dict={image_placeholder: sio.getvalue()})
image_tensor = detection_graph.get_tensor_by_name('image_tensor:0')
boxes = detection_graph.get_tensor_by_name('detection_boxes:0')
scores = detection_graph.get_tensor_by_name('detection_scores:0')
classes = detection_graph.get_tensor_by_name('detection_classes:0')
num_detections = detection_graph.get_tensor_by_name('num_detections:0')
start = time.time()
(boxes, scores, classes, num_detections) = sess.run(
[boxes, scores, classes, num_detections],
feed_dict={image_tensor: image_np_expanded})
print("DNN Time:",time.time() - start)
h, w, _ = frame.shape
frame_pil = Image.fromarray(frame)
draw = ImageDraw.Draw(frame_pil)
for n in range(len(scores[0])):
if scores[0][n] > 0.50:
ymin = int(boxes[0][n][0] * h)
xmin = int(boxes[0][n][1] * w)
ymax = int(boxes[0][n][2] * h)
xmax = int(boxes[0][n][3] * w)
draw.rectangle([xmin, ymin, xmax, ymax], outline = "red")
if str(int(classes[0][n])) in classNames.keys():
label = classNames[str(int(classes[0][n]))]+ ": " + str( scores[0][n])
draw.text((xmin,ymin), label, font=ttf, fill=(255,255,255,128))
print(label) # print class and confidence
frame_np = np.asarray(frame_pil)
lock.acquire()
frame_jpeg = sess.run(jpeg_image, feed_dict={image_to_jpeg_placeholder:frame_np})
lock.release()
time.sleep(0)
