def CreateXls(startTime,endTime,remote=False): returnStr = True now = datetime.now().strftime("%Y-%m-%d %H-%M-%S") fileName = now+".xls" #设置内容标题 # titleList = ['订单号',u'消费时间','消费门店',u"车牌号",u"车主姓名",u"联系电话",u"车型",u"操作人员", # u"消费项目",u"品牌",u"型号",u"花纹",u"数量",u"单价",u"小计",u"工时费", # u"更换里程",u"备注",u"总价"] # tableLen = len(titleList) #获取消费信息 xiaoFei = [] if remote: #获取远程信息 root = 'config.ini' basicMsg = configparser.ConfigParser() basicMsg.read(root) code = basicMsg.get('msg','code') keyWord = "xiaofei {} {} {}".format(code,startTime,endTime) #python3传递的是bytes,所以要编码 try: xiaoFei = SocketServer(keyWord) except: returnStr = False else: xiaoFei = dbhelp.GetXiaoFeiTable(startTime,endTime,Table=False) #填入数据 if xiaoFei: #插入信息 xiaoFei.sort(key=lambda obj:obj[2], reverse=True) orderCheckId = None hebing = list() tempMsg = dict() temp = list() #设置表头 titleList = ["检索ID",'订单号',u'消费时间',"消费门店",u"车牌号",u"车主姓名",u"联系电话",u"车型",u"操作人员", u"消费项目"] tableLen = len(titleList) hearder = ['数量','单价','小计','总价','单位','备注'] for data in xiaoFei: try: attribute = json.loads(data[10]) for k,v in attribute.items(): if k not in hearder: hearder.append(k) except: continue titleList = titleList + hearder allTableLen = len(titleList) wb = xlwt.Workbook() ws = wb.add_sheet('消费列表', cell_overwrite_ok=True) title = set_style('Arial',250,True,True) default = set_style('SimSun',180,True,True,True) top = set_style('Times New Roman',350,True,True) #前两个参数表示需要合并的行范围,后两个参数表示需要合并的列范围 #合并单元格作为大标题,水平居中即可 startTime = MakeTime(startTime)[:10] endTime = MakeTime(endTime)[:10] ws.write_merge(0,0,0,allTableLen-1,'门店系统:{}至{}'.format(startTime,endTime),top) #设置标题 for i in range(allTableLen): #设置单元格宽度 ws.col(i).width = 265*20 #插入标题 ws.write(1, i, titleList[i],title ) #从第二行开始插 row = 2 for data in xiaoFei: if orderCheckId: #如果记录的订单号与当前数据的订单号不同,则进行录入并修改记录订单号 if orderCheckId != data[0]: orderCheckId = data[0] #因为订单号变了所以之前的缓存清空,换成这个订单号的索引 temp = [row] else: #若已经缓存了2个索引则代表此订单号有>2个商品,所以更新第二个索引保留第一个索引 if len(temp) >= 2: temp[1] = row else: temp.append(row) else: #若第一次进来,此时订单号是None,进行录入 orderCheckId = data[0] temp.append(row) #插入信息 for j in range(tableLen): ws.write(row,j,data[j],default) if j == tableLen - 1 : #最后一个的时候遍历填入数据 try: j += 1 attribute = json.loads(data[j]) for k in hearder: ws.write(row,j,attribute.get(k,""),default) j += 1 except: continue row += 1 #如果已经缓存了2个数字,则代表有重复的订单号,所以此时进行记录并合并 if len(temp) >= 2: tempMsg[temp[0]] = MakeTempMsg(data) hebing.append(temp) if len(temp) >= 2: hebing.append(temp) tempMsg[temp[0]] = MakeTempMsg(data) for hb in hebing: ws.write_merge(hb[0],hb[1],0,0,tempMsg[hb[0]].get("checkOrderId"),default) ws.write_merge(hb[0],hb[1],1,1,tempMsg[hb[0]].get("orderNo"),default) ws.write_merge(hb[0],hb[1],2,2,tempMsg[hb[0]].get("createdTime"),default) ws.write_merge(hb[0],hb[1],3,3,tempMsg[hb[0]].get("pcSign"),default) ws.write_merge(hb[0],hb[1],4,4,tempMsg[hb[0]].get("carId"),default) ws.write_merge(hb[0],hb[1],5,5,tempMsg[hb[0]].get("carUser"),default) ws.write_merge(hb[0],hb[1],6,6,tempMsg[hb[0]].get("carPhone"),default) ws.write_merge(hb[0],hb[1],7,7,tempMsg[hb[0]].get("carModel"),default) ws.write_merge(hb[0],hb[1],8,8,tempMsg[hb[0]].get("workerName"),default) if not os.path.exists(savePath): os.mkdir(savePath) wb.save(savePath+fileName) return fileName else: return False
def xiaofeiTableSet(tableView,startTime,endTime,remote=False): #添加表头: model = QtGui.QStandardItemModel() returnStr = True #获取消费信息 if remote: #获取远程信息 try: storeId = GetStoreId() except Exception as e: print (e) print ('traceback.print_exc():{}'.format(traceback.print_exc())) print ('traceback.format_exc():\n{}'.format(traceback.format_exc())) return False keyWord = "xiaofei {} {} {}".format(storeId,startTime,endTime) #python3传递的是bytes,所以要编码 try: xiaoFei = SocketServer(keyWord) except Exception as e: print (e) print ('traceback.print_exc():{}'.format(traceback.print_exc())) print ('traceback.format_exc():\n{}'.format(traceback.format_exc())) return False else: xiaoFei = dbhelp.GetXiaoFeiTable(startTime,endTime) if xiaoFei == 'restart': return xiaoFei if xiaoFei: xiaoFei.sort(key=lambda obj:obj[1], reverse=True) #设置表头 titleList = ["ID",'订单号',u'消费时间',"消费门店",u"车牌号",u"车主姓名",u"联系电话",u"车型",u"操作人员", u"消费项目"] tableLen = len(titleList) hearder = ['数量','单价','小计','总价','单位','备注'] for data in xiaoFei: try: attribute = json.loads(data[9]) for k,v in attribute.items(): if k not in hearder: hearder.append(k) except: continue titleList = titleList + hearder + ['操作'] allTableLen = len(titleList) #设置表格属性: model.setColumnCount(allTableLen) for i in range(allTableLen): model.setHeaderData(i,QtCore.Qt.Horizontal,_fromUtf8(titleList[i])) tableView.setColumnWidth(i,120) tableView.setModel(model) #插入信息 i = 0 orderCheckId = None hebing = list() temp = list() for data in xiaoFei: if orderCheckId: #如果记录的订单号与当前数据的订单号不同,则进行录入并修改记录订单号 if orderCheckId != data[10]: orderCheckId = data[10] #如果已经缓存了2个数字,则代表有重复的订单号,所以此时进行记录并合并 if len(temp) >= 2: hebing.append(temp) #因为订单号变了所以之前的缓存清空,换成这个订单号的索引 temp = [i] else: #若已经记录了2个索引则代表此订单号有>2个商品,所以更新第二个索引保留第一个索引 if len(temp) >= 2: temp[1] = i else: temp.append(i) else: #若第一次进来,此时订单号是None,进行录入 # hebing.append(i) orderCheckId = data[10] temp.append(i) lastj = 0 for j in range(tableLen): if j == 0: model.setItem(i,j,QtGui.QStandardItem(_fromUtf8(str(data[10])))) else: model.setItem(i,j,QtGui.QStandardItem(_fromUtf8(str(data[j-1])))) if j == tableLen - 1 : # model.setItem(i,j,QtGui.QStandardItem(_fromUtf8(str(data[10])))) #最后一个的时候遍历填入数据 try: j += 1 attribute = json.loads(data[9]) for k in hearder: model.setItem(i,j,QtGui.QStandardItem(_fromUtf8(str(attribute.get(k,""))))) j += 1 lastj = j except: continue model.setItem(i,lastj,QtGui.QStandardItem(_fromUtf8("打印单据"))) model.item(i,0).setForeground(QtGui.QBrush(QtGui.QColor(70, 70, 70))) i += 1 if len(temp) >= 2: hebing.append(temp) for hb in hebing: num = hb[1] - hb[0] + 1 tableView.setSpan(hb[0],0,num,1) tableView.setSpan(hb[0],1,num,1) tableView.setSpan(hb[0],2,num,1) tableView.setSpan(hb[0],3,num,1) tableView.setSpan(hb[0],4,num,1) tableView.setSpan(hb[0],5,num,1) tableView.setSpan(hb[0],6,num,1) tableView.setSpan(hb[0],7,num,1) tableView.setSpan(hb[0],allTableLen-1,num,1) else: if returnStr: returnStr = None tableView.setModel(model) return returnStr
def ApiOrder(self,keyWord,getData): try: if self.request.method == 'POST': if keyWord == "add": today = datetime.now() orderNo = self.dbhelp.GetOrderNo(today) getData["orderNo"] = orderNo getData["createdTime"] = today try: # if True: carUser = getData.get("carUser") userId = getData.get("userId") workerId = getData.get("workerId") pcId = getData.get("pcId") carPhone = getData.get("carPhone") carModel = getData.get("carModel") carId = getData.get("carId") pcSign = getData.get("pcSign") workerName = getData.get("workerName") root = 'config.ini' basicMsg = configparser.ConfigParser() basicMsg.read(root) orderCheckId = GetOrderId() saveData = { 'createdTime' : getData.get("createdTime").strftime("%Y-%m-%d %H:%M:%S"), 'userId' : userId, 'pcId' : pcId, 'pcSign' : pcSign, 'carId' : carId, 'workerName' : workerName, 'workerId' : workerId, 'carUser' : carUser, 'carPhone' : carPhone, 'carModel' : carModel, "orderNo":orderNo, "orderCheckId":orderCheckId, 'code' : basicMsg.get("msg","code"), } parameter = getData.get("parameter",[]) if type(parameter) == str: parameter = json.loads(parameter) page = 0 for data in parameter: page += 1 orderId = GetOrderId() temp = { 'project':data.get('project'), 'id' : orderId, 'attribute' : json.dumps(data.get('attribute')) } self.dbhelp.InsertXiaoFei(dict(temp, **saveData)) #回访设置 if data.get("callbackTime"): dbname = "CallBack" key = "{},{},{},{},{},{}".format("callbackTime","phone",'carId',"username",'createdTime','state') value = "\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\'".format(data.get("callbackTime"),carPhone,carId,carUser,today,'0') self.dbhelp.InsertData(dbname,key,value) except: raise ApiException(ErrorCode.ParameterMiss) try: # if True: #打印 p = "defaultPrinter" #打印机名称 html,pageHeight = self.GetPreviewHtml(getData,True) Printer.printing(p, html,pageHeight) except: # raise ApiException(ErrorCode.PrinterError) pass return Set_return_dicts({"orderNo":orderNo}) elif keyWord == 'preview': self.logger.info('============begin-printer==============') html = self.GetPreviewHtml(getData) self.logger.info('============end-printer==============') return Set_return_dicts(html) else: raise ApiException(ErrorCode.ErrorRequest) elif self.request.method == "GET": if not self.storeId: raise ApiException(ErrorCode.PCError) if keyWord == "detail": checkOrderId = getData.get("checkOrderId") if not checkOrderId: raise ApiException(ErrorCode.ParameterMiss) if self.connect: resultDict = SocketServer("orderdetail {} {}".format(self.storeId,checkOrderId)) else: result = self.dbhelp.GetXiaoFeiByKey("orderCheckId",checkOrderId) resultList = list() resultDict = {} if result: createdTime = '' carId = '' carUser = '' carPhone = '' carModel = '' totalPrice = 0 pcId = '' orderNo = '' for data in result: attribute = OrderedDict(json.loads(data[8])) createdTime = data[0] carId = data[2] carUser = data[3] carPhone = data[4] carModel = data[5] price = float(attribute.get("总价",0)) pcId = data[9] orderNo = data[1] if pcId: totalPrice += price attribute['project'] = data[7] attribute['totalPrice'] = price attribute['orderNo'] = orderNo resultList.append(attribute) fp = open("pc.conf",'rb') pcData = fp.readline().decode() pcData = pcData.split(',') fp.close() try: pcSign = pcData[3] except: pcSign = "" resultDict = { "msg" : resultList, "totalPrice" : totalPrice, "createdTime" : createdTime, "carId" : carId, "carUser" : carUser, "carPhone" : carPhone, "carModel" : carModel, "orderNo" : orderNo, "checkOrderId" : checkOrderId, "pcSign" : pcSign, } if resultDict == 'restart': raise ApiException(ErrorCode.ReStartPC) # resultDict = {} return Set_return_dicts(resultDict) else: raise ApiException(ErrorCode.ErrorRequest) except ApiException as e: return Set_return_dicts(forWorker=e.error_result['forWorker'], code=e.error_result['errorCode'], forUser=e.error_result['forUser'])
def ApiUser(self,keyWord,getData): try: if self.request.method == 'POST': if keyWord == "add": try: userName = getData.pop("username") carPhone = getData.pop("carPhone") carModel = getData.pop("carModel") carId = getData.pop("carId") except: raise ApiException(ErrorCode.ParameterMiss) key = "userName,carPhone,carModel,carId,createdTime" value = "'{}','{}','{}','{}','{}'".format(userName,carPhone,carModel,carId,GetToday()) tempUser = self.dbhelp.GetUserByKey("carId",carId) if tempUser: updateData = "userName=\'{}\',carPhone=\'{}\',carModel=\'{}\'".format(userName,carPhone,carModel) search = "carId=\'{}\'".format(carId) try: self.dbhelp.UpdateData("User",updateData,search) except: raise ApiException(ErrorCode.ParameterError) userId = tempUser[0][0] else: try: userId = self.dbhelp.InsertData("User",key,value) except: raise ApiException(ErrorCode.UserMore) return Set_return_dicts({"userId":userId}) else: raise ApiException(ErrorCode.ErrorRequest) elif self.request.method == "GET": # basicMsg = configparser.ConfigParser() # root = 'config.ini' # basicMsg.read(root) # code = basicMsg.get('msg','code') if not self.storeId: raise ApiException(ErrorCode.PCError) if keyWord == "find": key = getData.get("key","") if key not in ["carPhone","carId"]: raise ApiException(ErrorCode.ParameterError) value = getData.get("value","") if self.connect: temp = SocketServer("user {} {} {}".format(self.storeId,key,value)) if not temp: temp = self.GetFind(key,value) else: temp = self.GetFind(key,value) result = [] keyTemp = [] if temp == 'restart': raise ApiException(ErrorCode.ReStartPC) # result = [] else: for data in temp: key = data.get("phone") + data.get("carId") + data.get("carModel") + data.get("userName") if key in keyTemp: pass else: result.append(data) keyTemp.append(key) return Set_return_dicts(result) elif keyWord == 'order': carId = getData.get("carId","") carPhone = getData.get("carPhone","") if not carId: raise ApiException(ErrorCode.ParameterMiss) if self.connect: allOrderMoney = 0 result = SocketServer("userorder {} {} {}".format(self.storeId,carId,carPhone)) if result: orderNumber = len(result) for data in result: allOrderMoney += data.get("totalPrice") else: result,orderNumber,allOrderMoney = self.GetOrder(carId,carPhone) else: result,orderNumber,allOrderMoney = self.GetOrder(carId,carPhone) if result == 'restart': raise ApiException(ErrorCode.ReStartPC) # result = [] else: result.sort(key=lambda obj:obj.get('createdTime'), reverse=True) for data in result: msg = data.get("msg") for msgData in msg: temp = {} attribute = msgData.get("attribute") for k,v in attribute.items(): if v != "" and v != "-": temp[k] = v msgData['attribute'] = temp sendMsg = { 'orderMsg' : result, 'orderNumber' : orderNumber, 'allOrderMoney' : allOrderMoney } return Set_return_dicts(sendMsg) else: raise ApiException(ErrorCode.ErrorRequest) except ApiException as e: return Set_return_dicts(forWorker=e.error_result['forWorker'], code=e.error_result['errorCode'], forUser=e.error_result['forUser'])
def user(self, keyword, get_data): try: if self.request.method == 'POST': if keyword == "add": try: username = get_data.pop("username") phone = get_data.pop("carPhone") car_model = get_data.pop("carModel") car_id = get_data.pop("carId") except Exception as e: print(e) raise ApiException(ErrorCode.ParameterMiss) customer = Customer() customer.username(username) customer.car_model(car_model) customer.phone(phone) customer.car_id(car_id) customer.create_time(time_utils.get_now()) temp_user = customer_handler.get_customer_by_key( "carId", car_id) if temp_user: try: customer_handler.update_customer_by_car_id( customer) except Exception as update_exception: print(update_exception) raise ApiException(ErrorCode.ParameterError) customer_id = temp_user[0][0] else: try: customer_id = customer_handler.add_customer( customer) except Exception as insert_exception: print(insert_exception) raise ApiException(ErrorCode.UserMore) return set_return_dicts({"userId": customer_id}) else: raise ApiException(ErrorCode.ErrorRequest) elif self.request.method == "GET": if not self.storeId: raise ApiException(ErrorCode.PCError) if keyword == "find": key = get_data.get("key", "") if key not in ["carPhone", "carId"]: raise ApiException(ErrorCode.ParameterError) value = get_data.get("value", "") if self.connect: temp = SocketServer("user {} {} {}".format( self.storeId, key, value)) if not temp: temp = self.find_customer(key, value) else: temp = self.find_customer(key, value) result = [] key_temp = [] if temp == 'restart': raise ApiException(ErrorCode.ReStartPC) else: for data in temp: key = data.get("phone") + data.get( "carId") + data.get("carModel") + data.get( "userName") if key in key_temp: pass else: result.append(data) key_temp.append(key) return set_return_dicts(result) elif keyword == 'order': car_id = get_data.get("carId", "") phone = get_data.get("carPhone", "") if not car_id: raise ApiException(ErrorCode.ParameterMiss) if self.connect: all_order_money = 0.0 result = SocketServer("userorder {} {} {}".format( self.storeId, car_id, phone)) if result: order_number = len(result) for data in result: all_order_money += data.get("totalPrice") else: result, order_number, all_order_money = self.get_order( car_id) else: result, order_number, all_order_money = self.get_order( car_id) if result == 'restart': raise ApiException(ErrorCode.ReStartPC) # result = [] else: print('sort') try: result.sort(key=lambda obj: obj.get('createdTime'), reverse=True) except Exception as sortE: print(sortE) try: for data in result: print(data) msg = data.get("msg") for msg_data in msg: temp = {} attribute = msg_data.get("attribute") for k, v in attribute.items(): if v != "" and v != "-": temp[k] = v msg_data['attribute'] = temp except Exception as forException: print(forException) send_msg = { 'orderMsg': result, 'orderNumber': order_number, 'allOrderMoney': all_order_money } print(send_msg) return set_return_dicts(send_msg) else: raise ApiException(ErrorCode.ErrorRequest) except ApiException as e: return set_return_dicts(forWorker=e.error_result['forWorker'], code=e.error_result['errorCode'], forUser=e.error_result['forUser'])
def ApiOrder(self, keyWord, getData): try: if self.request.method == 'POST': if keyWord == "add": today = datetime.now() orderNo = get_sale_order_no(today) getData["orderNo"] = orderNo getData["createdTime"] = today print(keyWord) try: # if True: carUser = getData.get("carUser") userId = getData.get("userId") workerId = getData.get("workerId") pcId = getData.get("pcId") carPhone = getData.get("carPhone") carModel = getData.get("carModel") carId = getData.get("carId") pc_sign = getData.get("pcSign") workerName = getData.get("workerName") root = 'config.ini' basicMsg = configparser.ConfigParser() basicMsg.read(root) orderCheckId = get_order_id() saveData = { 'createdTime': getData.get("createdTime").strftime( "%Y-%m-%d %H:%M:%S"), 'userId': userId, 'pcId': pcId, 'pcSign': pc_sign, 'carId': carId, 'workerName': workerName, 'workerId': workerId, 'carUser': carUser, 'carPhone': carPhone, 'carModel': carModel, "orderNo": orderNo, "orderCheckId": orderCheckId, 'code': basicMsg.get("msg", "code"), } parameter = getData.get("parameter", []) if type(parameter) == str: parameter = json.loads(parameter) page = 0 for data in parameter: page += 1 order_id = get_order_id() services = data.get('project') services = services.split('-') first_service_name = services[0] second_service_name = services[1] first_service_id = service_handler.get_service_id_by_name( first_service_name)[0] second_service_id = service_handler.get_service_id_by_name( second_service_name, first_service_id)[0] attributes = data.get('attribute') print(attributes) try: unit = attributes.get('单位', '') unit_price = float(attributes.pop('单价', '')) number = int(attributes.get('数量', '')) subtotal = float(attributes.get('小计', '')) total = float(attributes.get('总价', '')) note = attributes.get('备注', '') except Exception as attribute_deal_error: print(attribute_deal_error) unit = '' unit_price = 0.0 number = 0 subtotal = 0.0 total = 0.0 note = '' temp = { 'project': data.get('project'), 'id': order_id, 'attribute': json.dumps(data.get('attribute')), 'serviceId': second_service_id, 'unit': unit, 'unit_price': unit_price, 'number': number, 'subtotal': subtotal, 'total': total, 'note': note } sale_id = sale_handler.add_sale_info( dict(temp, **saveData)) service_attrs = service_handler.get_attribute_by_service( second_service_id) print(service_attrs) all_required_attr = attribute_handler.get_all_required_attributes( ) required_attr_list = [] for attr in all_required_attr: required_attr_list.append(attr[1]) for srv_attr in service_attrs: attr_name = srv_attr[1] if attr_name not in required_attr_list: attr_id = attribute_handler.get_attr_by_name( attr_name)[0] sale_item_handler.add_sale_item( order_id, attr_id, attributes.get(attr_name, '')) # 回访设置 if data.get("callbackTime"): dbname = "CallBack" key = "{},{},{},{},{},{}".format( "callbackTime", "phone", 'carId', "username", 'createdTime', 'state') value = "\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\'".format( data.get("callbackTime"), carPhone, carId, carUser, today, '0') self.dbhelp.InsertData(dbname, key, value) customer_handler.add_return_visit_data( data.get("callbackTime"), carPhone, carId, carUser, today) except Exception as add_error: print(add_error) raise ApiException(ErrorCode.ParameterMiss) try: # if True: # 打印 p = "defaultPrinter" # 打印机名称 html, pageHeight = self.preview_html(getData, True) Printer.printing(p, html, pageHeight) except: # raise ApiException(ErrorCode.PrinterError) pass return set_return_dicts({"orderNo": orderNo}) elif keyWord == 'preview': print('preview') html = self.preview_html(getData) print(html) return set_return_dicts(html) else: raise ApiException(ErrorCode.ErrorRequest) elif self.request.method == "GET": if not self.storeId: raise ApiException(ErrorCode.PCError) if keyWord == "detail": checkOrderId = getData.get("checkOrderId") if not checkOrderId: raise ApiException(ErrorCode.ParameterMiss) if self.connect: result_dict = SocketServer("orderdetail {} {}".format( self.storeId, checkOrderId)) else: result = get_sale_info_by_one_key( "orderCheckId", checkOrderId) resultList = list() result_dict = {} if result: createdTime = '' carId = '' carUser = '' carPhone = '' carModel = '' totalPrice = 0 pcId = '' orderNo = '' for data in result: attribute = OrderedDict(json.loads(data[8])) createdTime = data[0] carId = data[2] carUser = data[3] carPhone = data[4] carModel = data[5] price = data[16] pcId = data[9] orderNo = data[1] if pcId: totalPrice += price attribute['project'] = data[7] attribute['totalPrice'] = price attribute['orderNo'] = orderNo resultList.append(attribute) try: pc_sign = config.get_store_name() except: pc_sign = "" result_dict = { "msg": resultList, "totalPrice": totalPrice, "createdTime": createdTime, "carId": carId, "carUser": carUser, "carPhone": carPhone, "carModel": carModel, "orderNo": orderNo, "checkOrderId": checkOrderId, "pcSign": pc_sign, } if result_dict == 'restart': raise ApiException(ErrorCode.ReStartPC) # resultDict = {} return set_return_dicts(result_dict) else: raise ApiException(ErrorCode.ErrorRequest) except ApiException as e: return set_return_dicts(forWorker=e.error_result['forWorker'], code=e.error_result['errorCode'], forUser=e.error_result['forUser'])