def search(request): ids=[] item4=[] res2=[] search=request.params['search'] words=search.split() s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: ItemStrings=["ItemName like '%"+x+"%' or ItemDescription like '%"+x+"%'" for x in words] sql="SELECT idCatalog FROM Catalog WHERE " + " or ".join(ItemStrings) cursor.execute(sql) res=cursor.fetchall() for a in res: ids.append(a['idCatalog']) for b in ids: sql1="SELECT c.`idCatalog`, i.`ItemTypeName`, c.`ItemName`, ca.`CategoryName`, im.`URL` FROM `ItemType` as i, `Catalog` as c, `Category` as ca, `Images` as im WHERE ca.`idCategory`=c.`idCategory` AND i.`idItemType`=c.`idItemType` AND c.`idCatalog`=im.`idCatalog` AND c.`idCatalog`='%s'" %(b) cursor.execute(sql1) res1=cursor.fetchall() res2.extend(res1) for c in res2: item1={'idcat':c['idCatalog'],'itemtypename':c['ItemTypeName'],'url':c['URL'] , 'itemname':c['ItemName'], 'catname':c['CategoryName']} item4.append(item1) connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/itemfetch.jinja2',{'item':item4},request=request)
def submitaddquote(request): idven=request.params['idven'] idcat2=request.params['id'] quotationex=request.params['quotationex'] quotationin=request.params['quotationin'] taxes=request.params['tax'] shippingcharge=request.params['shipping'] totalcost=request.params['cost'] delivery=request.params['delivery'] payment=request.params['payment'] comments=request.params['comments'] warranty=request.params['warranty'] exciseduty=request.params['exciseduty'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `VendorPieceQuotation` (`idVendor`,`idCatalog`,`Quotation(Exc Taxes)`,`Excise Duty`,`Taxes`,`Quotation(Inc Taxes)`,`Shipping Charges`,`Total Cost Price`,`Warranty`,`Delivery Time`,`PaymentTerms`,`Comments`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" cursor.execute(sql, (idven,idcat2,quotationex,exciseduty,taxes,quotationin,shippingcharge,totalcost,warranty,delivery,payment,comments)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('Quotation is added for an Item!') return render_to_response('templates/addquote.jinja2',{},request=request)
def editdetails(request): details=request.params['det'] details1 = ast.literal_eval(details) s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql1="SELECT * from `ItemType`" cursor.execute(sql1) result1=cursor.fetchall() typename1=[] for b in result1: typename={'id':b['idItemType'], 'name':b['ItemTypeName']} typename1.append(typename) sql2="SELECT * from `Category`" cursor.execute(sql2) res2=cursor.fetchall() catname1=[] for c in res2: catname={'id':c['idCategory'], 'name':c['CategoryName']} catname1.append(catname) connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/edititem.jinja2',{'details1':details1,'typename':typename1,'catname':catname1},request=request)
def quotation(request): s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT * FROM `Vendor`" cursor.execute(sql) result=cursor.fetchall() ven1=[] for r in result: ven={'id':r['idVendor'], 'name':r['Name']} ven1.append(ven) sql1="SELECT * FROM `Catalog`" cursor.execute(sql1) result1=cursor.fetchall() name1=[] for s in result1: name={'id':s['idCatalog'], 'name':s['ItemName']} name1.append(name) connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/vendorpiece.jinja2',{'ven1':ven1,'name1':name1},request=request)
def itemfetch(request): userid=request.params['id'] s = conn.connect() connection=s["connection"] typename=[] catname=[] try: with connection.cursor() as cursor: sql="SELECT c.`idCatalog`, i.`ItemTypeName`, c.`ItemDescription`, c.`ItemName`, ca.`CategoryName`, im.`URL` FROM `ItemType` as i, `Catalog` as c, `Category` as ca, `Images` as im WHERE ca.`idCategory`=c.`idCategory` AND i.`idItemType`=c.`idItemType` AND c.`idCatalog`=im.`idCatalog`" cursor.execute(sql) result=cursor.fetchall() item=[] for a in result: item1={'itemtypename':a['ItemTypeName'],'idcat':a['idCatalog'] ,'url':a['URL'], 'description':a['ItemDescription'], 'itemname':a['ItemName'], 'catname':a['CategoryName']} item.append(item1) sql1="SELECT * from `ItemType`" cursor.execute(sql1) result1=cursor.fetchall() for b in result1: typename.append(b['ItemTypeName']) typename1=[str(i) for i in typename] sql2="SELECT * from `Category`" cursor.execute(sql2) res2=cursor.fetchall() for c in res2: catname.append(c['CategoryName']) catname1=[str(i) for i in catname] connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/itemfetch.jinja2',{'userid':userid,'item':item,'typename':typename1,'catname':catname1},request=request)
def login(request,is_loggedin): message='' if is_loggedin: return HTTPFound(location="/submitlogin") if request.method=="POST": name=request.params['uname'] password=request.params['password'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT * FROM `User`" cursor.execute(sql) result=cursor.fetchall() for res in result: user=res['UserName'] pwd=res['Password'] if name==user and password==pwd: headers = remember(request, res['idUser']) return HTTPFound(location='/submitlogin',headers=headers) else: message="wrong creds" except Exception as e: message="something went wrong" print (e) connection.close() came_from = request.params.get('came_from', request.url) return dict(name='Login',message=message,came_from=came_from)
def catalog(request): s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT * FROM `Category`" cursor.execute(sql) result=cursor.fetchall() cat1=[] for r in result: cat={'id':r['idCategory'], 'name':r['CategoryName']} cat1.append(cat) sql1="SELECT * FROM `ItemType`" cursor.execute(sql1) result1=cursor.fetchall() item1=[] for s in result1: item={'id':s['idItemType'], 'name':s['ItemTypeName']} item1.append(item) connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/catalog.jinja2',{'cat1':cat1,'item1':item1},request=request)
def submiteditquote(request): idvenpiece=request.params['id'] quotationex=request.params['quotationex'] quotationin=request.params['quotationin'] taxes=request.params['tax'] shippingcharge=request.params['shipping'] totalcost=request.params['cost'] delivery=request.params['delivery'] payment=request.params['payment'] comments=request.params['comments'] warranty=request.params['warranty'] exciseduty=request.params['exciseduty'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "UPDATE `VendorPieceQuotation` SET `Quotation(Exc Taxes)`=%s, `Excise Duty`=%s,`Taxes`=%s,`Quotation(Inc Taxes)`=%s,`Shipping Charges`=%s,`Total Cost Price`=%s,`Warranty`=%s,`Delivery Time`=%s,`PaymentTerms`=%s,`Comments`=%s where `idVendorPieceQuotation`=%s" cursor.execute(sql,(quotationex,exciseduty,taxes,quotationin,shippingcharge,totalcost,warranty,delivery,payment,comments,idvenpiece)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('Quotations for an Item are edited and saved successfully!') return render_to_response('templates/editvendor.jinja2',{},request=request)
def subviewproject(request): p_name=request.params['name'] projectname1=[] projectname=[] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT id, apartment_name, style_name, client_name from presentation_project where presentation_name=%s" cursor.execute(sql,(p_name)) res=cursor.fetchall() p_id=res[0]['id'] a_name=res[0]['apartment_name'] s_name=res[0]['style_name'] c_name=res[0]['client_name'] sql1="SELECT presentation_name from presentation_project" cursor.execute(sql1) result1=cursor.fetchall() for b in result1: projectname.append(b['presentation_name']) projectname1=[str(i) for i in projectname] projectname1=[x for x in projectname1 if x!='None'] connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/viewproject.jinja2',{'p_id':p_id,'p_name':p_name,'a_name':a_name,'s_name':s_name,'c_name':c_name,'name':projectname1},request=request)
def addproject(request): pname = request.params['p_name'] style = request.params['s_name'] apartment = request.params['a_name'] client = request.params['c_name'] s_id_list = list() s = conn.connect() connection = s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO presentation_project(presentation_name,apartment_name,style_name,client_name) values(%s,%s,%s,%s)" cursor.execute(sql, (pname, apartment, style, client)) sql1 = "SELECT LAST_INSERT_ID()" cursor.execute(sql1) pro_id = cursor.fetchall() project_id = int(pro_id[0]['LAST_INSERT_ID()']) query_1 = '''insert into Presentation (pr_id) values (%s)''' cursor.execute(query_1, (project_id)) connection.commit() except NameError: print('An exception flew by!') finally: # conn.close_connection() connection.close() return project_id
def download(request): listname=request.params['listname'] newList=[] images=[] listid=[] ids=[] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql6="SELECT idList from List where ListName=%s" cursor.execute(sql6,(listname)) res=cursor.fetchall() listid=res[0]['idList'] sql1="SELECT idCatalog from ListItems where idList='%s'" %(listid) cursor.execute(sql1) res1=cursor.fetchall() for a in res1: ids1=a['idCatalog'] ids.append(ids1) for c in ids: sql="SELECT URL from Images where idCatalog='%s'" %(c) cursor.execute(sql) res=cursor.fetchall() for b in res: images1=[b['URL']] images.extend(images1) for i in images: newList.append(i.split("/")[-1]) r = glob.glob(dst_dir1) for i in r: os.remove(i) for d in newList: for file in glob.iglob(os.path.join(src_dir,d)): shutil.copy(file,dst_dir) connection.commit() fp = tempfile.NamedTemporaryFile('w+b', dir=src_dir, delete=True) compression=zipfile.ZIP_DEFLATED zf = zipfile.ZipFile(fp, mode='w') for folder,subfolder,files in os.walk(dst_dir): for file in files: zf.write(os.path.join(folder, file),file,compress_type=compression) zf.close() fp.seek(0) response = request.response response.content_type = 'application/zip' response.app_iter = FileIter(fp) except Exception as e: print(e) finally: connection.close() return response
def local_connector(): resp = dict(status=True, con='') s = conn.connect() if not s["status"]: print("conn fail") resp["status"] = False return resp else: connection = s["connection"] resp['con'] = connection return resp
def subviewlist(request): listname=request.params['listname'] userid=request.params['id'] ids=[] item=[] res3=[] images=[] filename=[] newList=[] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT idList from List where ListName=%s" cursor.execute(sql,(listname)) res=cursor.fetchall() listid=res[0]['idList'] sql3="SELECT ListName FROM List where idUser='******'" %(userid) cursor.execute(sql3) result=cursor.fetchall() namelist=[] for d in result: namelist1=d['ListName'] namelist.append(namelist1) namelist2=[str(i) for i in namelist] sql1="SELECT idCatalog from ListItems where idList='%s'" %(listid) cursor.execute(sql1) res1=cursor.fetchall() for a in res1: ids1=a['idCatalog'] ids.append(ids1) for b in ids: sql2="SELECT c.`idCatalog`, i.`ItemTypeName`, c.`ItemName`, ca.`CategoryName`, im.`URL` FROM `ItemType` as i, `Catalog` as c, `Category` as ca, `Images` as im WHERE ca.`idCategory`=c.`idCategory` AND i.`idItemType`=c.`idItemType` AND c.`idCatalog`=im.`idCatalog` AND c.`idCatalog`='%s'" %(b) cursor.execute(sql2) res2=cursor.fetchall() res3.extend(res2) for c in res3: item1={'idcat':c['idCatalog'],'itemtypename':c['ItemTypeName'],'url':c['URL'] , 'itemname':c['ItemName'], 'catname':c['CategoryName']} item.append(item1) images1=[c['URL']] images.extend(images1) connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/viewlist.jinja2',{'listname':listname,'item':item,'namelist':namelist2,'userid':userid},request=request)
def getprice_quote(request): pr_id = request.params['pr_id'] cat_name = [] sum_val = 0 cost = [] sum_value = 0 total_sum = {} s = conn.connect() connection = s["connection"] try: with connection.cursor() as cursor: sql = "select c.idCatalog, c.ItemName, ca.CategoryName, coalesce(v.`Quotation(Exc Taxes)`,0) as `Quotation(Exc Taxes)` from Presentation p inner join slide_elements s on p.s_id=s.s_id and s.flag=0 inner join Catalog c on c.idCatalog=s.e_id inner join Category ca on ca.idCategory=c.idCategory left join VendorPieceQuotation v on c.idCatalog=v.idCatalog where p.pr_id={pr_id};".format( pr_id=int(pr_id)) cursor.execute(sql) res = cursor.fetchall() if res: for i in res: sum_val += int(i["Quotation(Exc Taxes)"]) sql1 = "select CategoryName from Category" cursor.execute(sql1) res1 = cursor.fetchall() for i in res1: cat_name.append(i['CategoryName']) for j in cat_name: sql1 = "select ca.CategoryName,sum(v.`Quotation(Exc Taxes)`) as `Quotation(Exc Taxes)` from Presentation p inner join slide_elements s on p.s_id=s.s_id and s.flag=0 inner join Catalog c on c.idCatalog=s.e_id inner join Category ca on ca.idCategory=c.idCategory left join VendorPieceQuotation v on c.idCatalog=v.idCatalog where p.pr_id='%s' and ca.CategoryName='%s'" % ( pr_id, j) cursor.execute(sql1) res2 = cursor.fetchall() if res2: for r in res2: if (r['Quotation(Exc Taxes)'] != None): sum_value = int(r['Quotation(Exc Taxes)']) total_sum = (dict(cat_name=j, cost=sum_value)) cost.append(total_sum.copy()) else: sum_value = 0 total_cost = dict(price=res, sum_val=sum_val) connection.commit() except Exception as e: print(e) finally: connection.close() return dict(cat_cost=cost, total_cost=total_cost)
def itemdetails(request): idcat=request.params['id'] userid=request.params['userid'] listname=[] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: details=[] sql="SELECT * FROM `VendorPieceQuotation` where `idCatalog`='%s'" %(idcat) cursor.execute(sql) result=cursor.fetchall() detail=[] for a in result: sql3="SELECT Name FROM Vendor where idVendor='%s'" %(a['idVendor']) cursor.execute(sql3) result3=cursor.fetchall() venname=result3[0]['Name'] detail1={'venname':venname,'idvenpiece':a['idVendorPieceQuotation'],'costexc':a['Quotation(Exc taxes)'],'excise':a['Excise Duty'],'tax':a['Taxes'],'costinc':a['Quotation(Inc taxes)'],'shipping':a['Shipping Charges'],'totalcost':a['Total Cost Price'],'warranty':a['Warranty'],'delivery':a['Delivery Time'],'payment':a['PaymentTerms'],'comments':a['Comments']} detail.append(detail1) sql1="SELECT `ItemDescription`,`ItemName`,`Width(inch)`,`Depth(inch)`,`Height(inch)`,`Remarks` FROM `Catalog` where `idCatalog`='%s'" %(idcat) cursor.execute(sql1) result1=cursor.fetchall() sql2="SELECT `URL` FROM `Images` where `idCatalog`='%s'" %(idcat) cursor.execute(sql2) result2=cursor.fetchall() sql4="SELECT ca.`CategoryName`, i.`ItemTypeName` FROM `Category` as ca, `ItemType` as i, `Catalog` as c where ca.`idCategory`=c.`IdCategory` and i.`idItemtype`=c.`idItemType` and `idCatalog`='%s'" %(idcat) cursor.execute(sql4) result4=cursor.fetchall() sql5="SELECT idList,ListName FROM List where idUser='******' and Flag=0" %(userid) cursor.execute(sql5) result5=cursor.fetchall() for b in result5: listname1={'listid':b['idList'],'listname':b['ListName']} listname.append(listname1) details={'categoryname':result4[0]['CategoryName'],'itemtypename':result4[0]['ItemTypeName'],'idcat':idcat,'itemdesc':result1[0]['ItemDescription'],'itemname':result1[0]['ItemName'],'width':result1[0]['Width(inch)'],'depth':result1[0]['Depth(inch)'],'height':result1[0]['Height(inch)'],'remarks':result1[0]['Remarks'],'url':result2[0]['URL']} connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/itemdetailsnew.jinja2',{'userid':userid,'details':details,'detail':detail,'listname':listname},request=request)
def itemadded(request): listid=request.params['idlist'] catalogid=request.params['id'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="INSERT INTO `ListItems` (`idList`,`idCatalog`) VALUES (%s,%s)" cursor.execute(sql, (listid,catalogid)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('Item added to List!') return render_to_response('templates/submitlist.jinja2',{},request=request)
def delete_element(request): resp_dict = dict(status=True) id = request.params['id'] print(id) s = conn.connect() connection = s["connection"] try: with connection.cursor() as cursor: sql = "UPDATE slide_elements SET flag=1 where id=%d" % (int(id)) cursor.execute(sql) connection.commit() except Exception as e: print(e) resp_dict['status'] = False finally: connection.close() return resp_dict
def vendorsubmit(request): name=request.params['name'] address=request.params['address'] contactno=request.params['contactno'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `Vendor` (`Name`, `Address`,`Contact_No`) VALUES (%s, %s, %s)" cursor.execute(sql, (name,address,contactno)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('New Vendor is added!') return render_to_response('templates/vendorform.jinja2',{},request=request)
def submititem(request): typename=request.params['typename'] typedesc=request.params['typedesc'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `ItemType` (`ItemTypeName`,`ItemTypeDescription`) VALUES (%s,%s)" cursor.execute(sql, (typename,typedesc)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('Item Type is added!') return render_to_response('templates/itemtype.jinja2',{},request=request)
def submitmat(request): name=request.params['name'] quantity=request.params['quantity'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `Material` (`Name`,`Quantity`) VALUES (%s)" cursor.execute(sql, (name,quantity)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('New Material is added!') return render_to_response('templates/material.jinja2',{},request=request)
def submitcat(request): catname=request.params['name'] desc=request.params['description'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `Category` (`CategoryName`,`CategoryDescription`) VALUES (%s,%s)" cursor.execute(sql, (catname,desc)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('New Category is added!') return render_to_response('templates/category.jinja2',{},request=request)
def submitaddlist(request): listname=request.params['name'] listdesc=request.params['description'] userid=request.params['id'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `List` (`idUser`,`ListName`,`ListDescription`) VALUES (%s,%s,%s)" cursor.execute(sql,(userid,listname,listdesc)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('New List is added!') return render_to_response('templates/addlist.jinja2',{'id':userid},request=request)
def viewpresentation(request): projectname=[] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT id,presentation_name from presentation_project" cursor.execute(sql) result1=cursor.fetchall() for b in result1: projectname.append(b['presentation_name']) projectname1=[str(i) for i in projectname] projectname1=[x for x in projectname1 if x!='None'] connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/viewproject.jinja2',{'name':projectname1},request=request)
def editslide(): slide_id = request.params['idslide'] id_cat = request.params['idcat'] pos_x = request.params['pos_y'] pos_y = request.params['pos_x'] obj_len = request.params['height'] obj_wid = request.params['width'] s = conn.connect() connection = s["connection"] try: with connection.cursor() as cursor: sql = "UPDATE slide_elements SET position_x=%d, position_y=%d, object_length=%d, object_breadth=%d where s_id=%d" cursor.execute(sql, (int(pos_x), int(pos_y), int(obj_len), int(obj_wid), int(slide_id))) connection.commit() except Exception as e: print(e) finally: connection.close()
def subclient(request): pr_id = request.params['prid'] p_name = request.params['p_name'] a_name = request.params['a_name'] s_name = request.params['s_name'] c_name = request.params['c_name'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="UPDATE presentation_project set presentation_name=%s, style_name=%s, apartment_name=%s, client_name=%s where id=%s" cursor.execute(sql,(p_name,s_name,a_name,c_name,pr_id)) connection.commit() except Exception as e: print(e) finally: connection.close() from pyramid.httpexceptions import HTTPFound url=request.application_url+'/presentation/'+str(pr_id) return Response(status_int=302, location=url)
def submitfab(request): length=request.params['len'] fabtype=request.params['type'] cost=request.params['cost'] code=request.params['code'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO `Fabric` (`Length(metre)`,`Type`,`Cost/metre`,`Fabric Code`) VALUES (%s,%s,%s,%s)" cursor.execute(sql, (length,fabtype,cost,code)) connection.commit() except Exception as e: print(e) finally: connection.close() request.session.pop_flash() request.flash_message.add('New Fabric is added!') return render_to_response('templates/fabric.jinja2',{},request=request)
def editclient(request): pr_id=request.params['prid'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT apartment_name, presentation_name, style_name, client_name from presentation_project where id='%s'" %(pr_id) cursor.execute(sql) res=cursor.fetchall(); print(res) a_name=res[0]['apartment_name'] p_name=res[0]['presentation_name'] s_name=res[0]['style_name'] c_name=res[0]['client_name'] connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/editclient.jinja2',{'pr_id':pr_id, 'p_name':p_name, 'a_name':a_name, 's_name':s_name, 'c_name':c_name}, request=request)
def viewlist(request): userid=request.params['id'] s = conn.connect() connection=s["connection"] try: with connection.cursor() as cursor: sql="SELECT ListName FROM List where idUser='******'" %(userid) cursor.execute(sql) result=cursor.fetchall() namelist=[] for a in result: namelist1=a['ListName'] namelist.append(namelist1) namelist2=[str(i) for i in namelist] connection.commit() except Exception as e: print(e) finally: connection.close() return render_to_response('templates/viewlist.jinja2',{'namelist':namelist2,'userid':userid},request=request)
def addslide(request): resp_dict = dict(status=True) id_p = request.params['id'] id_slide = [] s = conn.connect() connection = s["connection"] try: with connection.cursor() as cursor: sql = "INSERT INTO Presentation (pr_id) values(%s)" cursor.execute(sql, (int(id_p))) sql1 = "SELECT LAST_INSERT_ID()" cursor.execute(sql1) idslide = cursor.fetchall() id_slide = idslide[0]['LAST_INSERT_ID()'] resp_dict['slide_id'] = id_slide connection.commit() except Exception as e: print(e) resp_dict['status'] = False finally: connection.close() return resp_dict
def slide(request): pid=request.matchdict['p_id'] sid=request.matchdict['s_id'] s = conn.connect() connection=s["connection"] resp_text=list() resp_url=list() slides=list() resp = dict(status=True,data=dict(text=resp_text,url=resp_url,slides=slides)) try: with connection.cursor() as cursor: if int(sid): sql2="select p.s_id,p.pr_id,a.id,b.URL,a.temp_url,a.position_x,a.position_y,a.object_length,a.object_breadth,a.e_desc,a.e_id from Presentation p left join slide_elements a on p.s_id=a.s_id and p.flag=0 and a.flag=0 left join Images b on a.e_id=b.idCatalog where a.s_id={s_id} and p.pr_id={p_id} order by p.s_id;".format(s_id=int(sid),p_id=int(pid)) else: sql2="select p.s_id,p.pr_id,a.id,b.URL,a.temp_url,a.position_x,a.position_y,a.object_length,a.object_breadth,a.e_desc,a.e_id from Presentation p left join slide_elements a on p.s_id=a.s_id and p.flag=0 and a.flag=0 left join Images b on a.e_id=b.idCatalog where p.pr_id={p_id} order by p.s_id;".format(p_id=int(pid)) cursor.execute(sql2) res=cursor.fetchall() p_s_id=0 for i in res: c_s_id=int(i["s_id"]) if i["id"]: if (i["URL"] or i["temp_url"]): resp_url.append(i) else: resp_text.append(i) if c_s_id!=p_s_id: s=c_s_id slides.append(s) p_s_id=c_s_id except Exception as e: resp["status"]=False print(e) finally: connection.close() return resp