Python utils.select函数代码示例

def load_db_config(cfg):
  
  supercats = utils.select('''select distinct supercategory as name from sku 
    where bin is not null and bin != '0' and active=True and supercategory is not null
    order by id''')
  for supercat in supercats:
    cfg['menu']['categories'].append(supercat)
    supercat['subcategories'] = []
    for cat in utils.select('''select distinct category as name from sku where supercategory = %s 
      and bin is not null and bin != '0' and active = True and category is not null order by bin''', 
        args = (supercat['name'])):
      supercat['subcategories'].append(cat)
      cat['items'] = []
      for item in utils.select('''select * from sku where supercategory = %s and category = %s
      and bin is not null and bin != '0' and active=True
      order by bin, name''', args = (supercat['name'], cat['name'])):
        cat['items'].append(item)
        if winecats.match(cat['name']):
          item['name'] = item['bin'] + ' ' + item['name']
          my_logger.info('name: modified ' + item['name'])
          if item['qtprice'] > 0: 
            my_logger.info('qt: added ' + item['name'])
            qtitem = item.copy()
            qtitem['fraction'] = .25
            qtitem['retail_price'] = item['qtprice']
            qtitem['name'] = 'qt: '+item['name']
            cat['items'].append(qtitem)

 def parse_entry(self, entry):
     """Scrape each AotD entry for its URL and title."""
     item = super(ArticleOfTheDay, self).parse_entry(entry)
     summary = lxml.html.fromstring(entry.summary)
     try:
         item['summary'] = select(summary, 'p:first-of-type').text_content()
     except IndexError:
         # The featured article feed is created by
         # Extension:FeaturedFeeds from user-generated descriptions
         # in wikitext. Some descriptions, such as
         # [[Wikipedia:Today's featured article/January 16, 2017]]
         # are not formatted to have <p> tags when we need em.
         item['summary'] = summary.text_content()
         # since we don't have <p> tags for the description, we
         # need to filter out the trailing <div>s
         item['summary'] = item['summary'].split('Recently featured')[0].strip()
     item['summary'] = item['summary'].replace(u'(Full\xa0article...)', '')
     try:
         read_more = select(summary, 'p:first-of-type a:last-of-type')
     except IndexError:
         # The first bolded link is usually a link to the article
         read_more = select(summary, 'b a:first-of-type')
     item['url'] = read_more.get('href')
     item['title'] = read_more.get('title')
     return item

 def parse_entry(self, entry):
     """Scrape each AotD entry for its URL and title."""
     item = super(ArticleOfTheDay, self).parse_entry(entry)
     summary = lxml.html.fromstring(entry.summary)
     item['summary'] = select(summary, 'p:first-of-type').text_content()
     item['summary'] = item['summary'].replace(u'(Full\xa0article...)', '')
     read_more = select(summary, 'p:first-of-type a:last-of-type')
     item['url'] = read_more.get('href')
     item['title'] = read_more.get('title')
     return item

 def run(self, edit):
     preferences=utils.get_preferences()
     if(preferences==None):return
     if(preferences.get("user")==None):preferences["user"]={}
     listKeys=list(preferences["user"].keys())
     print("estas son  : ",listKeys)
     for key in preferences["project"].keys():
         print(preferences["project"][key])
         listKeys+=list(preferences["project"][key].keys())
     utils.select(listKeys)

def weekly_pay(printmode=0, incursor = None):

  if incursor is None:
    incursor = utils.get_cursor()

  for table_name in ('PAY_STUB', 'PAY_STUB_TEMP'):
    utils.execute('''
      create temporary table v_%(table_name)s
      as
      select
      week_of,
      last_name, first_name,
      hours_worked,
      pay_rate,
      fed_withholding + nys_withholding + medicare_tax + social_security_tax as weekly_tax,
      round(weekly_pay -fed_withholding -nys_withholding -medicare_tax -social_security_tax) as net_wage,
      tips,
      total_hourly_pay
      from %(table_name)s
      where yearweek(week_of) = yearweek(now() - interval '1' week)
      order by last_name, first_name''' % locals(),
      incursor=incursor,
      )

    if printmode == 1:
      break

  if printmode == 1:
    return utils.select('''select * from v_PAY_STUB''', incursor=incursor)
  else: 
    return utils.select('''
      select 
        pst.week_of,
        pst.last_name,
        pst.first_name,
        IF(pst.hours_worked = ps.hours_worked or ps.hours_worked is null,
          pst.hours_worked, concat(pst.hours_worked, ' / ', ps.hours_worked)) hours_worked,
        IF(pst.pay_rate = ps.pay_rate or ps.pay_rate is null, 
          pst.pay_rate, concat(pst.pay_rate, ' / ', ps.pay_rate)) pay_rate,
        IF(pst.weekly_tax = ps.weekly_tax or ps.weekly_tax is null, 
          pst.weekly_tax, concat(pst.weekly_tax, ' / ', ps.weekly_tax)) weekly_tax,
        IF(pst.net_wage = ps.net_wage or ps.net_wage is null, 
          pst.net_wage, concat(pst.net_wage, ' / ', ps.net_wage)) net_wage,
        IF(pst.tips = ps.tips or ps.tips is null, 
          pst.tips, concat(pst.tips, ' / ', ps.tips)) tips,
        IF(pst.total_hourly_pay = ps.total_hourly_pay or ps.total_hourly_pay is null, 
          pst.total_hourly_pay, concat(pst.total_hourly_pay, ' / ', ps.total_hourly_pay)) total_hourly_pay
        from   
        v_PAY_STUB_TEMP pst LEFT OUTER JOIN v_PAY_STUB ps on pst.week_of = ps.week_of
        and pst.first_name = ps.first_name and pst.last_name = ps.last_name
        order by last_name, first_name
    ''', 
    incursor = incursor,
    label= False
    )    

def get_stub_data(person_id, week_of, table_name, incursor):  

  print person_id, week_of
  stub_data = utils.select('''
    select 
    last_name as LAST_NAME,
    first_name as FIRST_NAME,
    "000-00-0000" as SOCIAL,
    week_of + interval '1' week as PERIOD_END,
    concat(week_of, ' - ', week_of + interval '6' day) as PERIOD_SPAN,
    fed_withholding as FED,
    social_security_tax as SOC,
    medicare_tax as MED,
    nys_withholding as STATE,
    gross_wages as GROSS,
    gross_wages - fed_withholding - social_security_tax - medicare_tax - nys_withholding as NET,
    round(gross_wages / pay_rate,2) as HOURS,
    pay_rate as RATE
    from {table_name}
    where person_id = {person_id}
    and week_of = "{week_of}"
  '''.format(**locals()), incursor
  )

  stub_ytd_data = utils.select('''
    select 
    sum(fed_withholding) as FEDYTD,
    sum(social_security_tax) as SOCYTD,
    sum(medicare_tax) as MEDYTD,
    sum(nys_withholding) as STATEYTD,
    sum(gross_wages) as GYTD,
    sum(gross_wages - fed_withholding - social_security_tax - medicare_tax - nys_withholding) as NETYTD
    from {table_name}
    where person_id = {person_id}
    and year("{week_of}" + interval '1' week) = year(week_of+interval '1' week) 
    and week_of <= date("{week_of}")
  '''.format(**locals()), incursor
  )

  # make one dictionary of the two result sets
  result = stub_data[0] # start with stub_data
  result.update(stub_ytd_data[0]) #add the YTD stuff to it

  if utils.hostname() == 'salsrv':
    result["BUSS_INFO_LINE"] = "SALUMI dba Ultraviolet Enterprises 5600 Merrick RD, Massapequa, NY 11758 516-620-0057"
  else:  
    result["BUSS_INFO_LINE"] = "PLANCHA dba Infrared Enterprises 931 Franklin AVE, GardenCity, NY 516-246-9459"
  return result

def index(req):

  supercat = req.parsed_uri[7]
  if supercat == 'mkt':
    supercat = 'market'

  resp = ''

  results = utils.select('''
    select * from sku_inv where active = true and bin > 0 and supercategory = %s order by supplier''', args=supercat
  )

  if not results:
    return 'no such category as %s'%supercat

  supplier = ''
  for row in results:
    expand_extra_fields(row)
    next_supplier = row.get('supplier') or '<NONE>'
    if supplier.lower().strip() != next_supplier.lower().strip():
      supplier = next_supplier
      resp += supplier + '\n'

    if (row['estimated_units_remaining'] is not None and 
        isinstance(row.get('par'), (int, long, float)) and 
        row.get('par') > row['estimated_units_remaining']):

      row.setdefault('order_amt', str(int(round(row['par'] - row['estimated_units_remaining']))) + ' ' + row.get('order_unit', 'ct.'))
      resp += "\t{name} - {order_amt} \t(${wholesale_price}, {estimated_units_remaining} on hand, par is {par})\n".format(**row)

  return resp

def wine_insert():  
  winelist = utils.select('''select * from winelist order by id''')

  for item in winelist:
    if item['grapes']:
      item['grapes'] = 'Grapes: %s'%item['grapes']
    dct = {
    'id' : item['id'],
    'name' : item['name'],
    'supercategory' : 'bev',
    'category' : item['category'],
    'subcategory' : item['subcategory'],
    'retail_price' : item['listprice'],
    'qtprice' : item['qtprice'],
    'scalable' : False,
    'tax' : 'standard',
    'wholesale_price' : item['frontprice'],
    'supplier' : item['supplier'],
    'vendor_code' : None,
    'bin' : item['bin'],
    'listorder' : item['listorder'],
    'upc' : None,
    'description' : '\n'.join((i for i in (item['byline'], item['grapes'], item['notes']) if i)),
    'active' : item['active'],
    'units_in_stock' : item['units_in_stock'],
    'inventory_date' : item['inventory_date'],
    'mynotes' : item['mynotes']
    }

    utils.sql_insert('sku', dct)

def index(req):

  weekly = utils.select('''
	select
  first_name,
  last_name,
	round(sum(hours_worked),2) as hours_worked,
  round(sum(hours_worked)*pay_rate - weekly_tax) as weekly_pay
	from hours_worked 
  where yearweek(intime) = yearweek(now() - interval '1' week)
  group by yearweek(intime), last_name, first_name
	order by last_name''',
    incursor=None,
    label=False
  )


  output = '_ '*40 + '\n'*3

  for first_name, last_name, hours_worked, weekly_pay in weekly:
    if last_name in ('Kobrin', 'Kanarova'):
      continue

    output += (
      ' '*4 + first_name.ljust(12) + ' '+last_name.ljust(12) + '$'+str(int(weekly_pay or 0)).ljust(6) +
      str(hours_worked).rjust(30) + ' hrs'+'\n'*2  +
       '_ '*40 + '\n'*3
    )   

  return output

def extractCanvasSettings(d):
    """Split a dict in canvas settings and other items.

    Returns a tuple of two dicts: the first one contains the items
    that are canvas settings, the second one the rest.
    """
    return utils.select(d,pf.refcfg['canvas']),utils.remove(d,pf.refcfg['canvas'])

def update(req, edits, newrows):
  edits = json.loads(edits)
  newrows = json.loads(newrows)
  insert_ids = {}
  cursor = utils.get_cursor()

  for rowid, fields_and_vals in edits.items():
    setlist = ','.join('%s = %s'%(f, sql_representation(v)) for f, v in fields_and_vals.items() if f != 'estimated_units_remaining')
    sql = "update sku set " + setlist + " where id = " + rowid + "\n"
    utils.execute(sql, cursor)
  for rowid, fields_and_vals in newrows.items():
    for bad_field in ('uid', 'undefined', 'estimated_units_remaining', 'boundindex', 'visibleindex', 'uniqueid'):
      if fields_and_vals.has_key(bad_field): fields_and_vals.pop(bad_field)

    fields = fields_and_vals.keys()
    values = fields_and_vals.values()
    field_list = ','.join(fields)
    value_list = ','.join(sql_representation(v) for v in values)
    sql = "insert into sku ("+field_list+") VALUES ("+value_list+")"
    utils.execute(sql, cursor)
    insert_ids[rowid] = utils.select("select LAST_INSERT_ID()", cursor, False)[0][0]

  cursor.close ()

  wineprint.gen_fodt_and_pdf()

  return json.dumps(insert_ids)

def print_one_week_stubs(week_of):

  for table_name in ('PAY_STUB', 'WEEKLY_PAY_STUB'):
    stub_keys = utils.select('''select person_id from {table_name} where week_of = {week_of}'''.format(**locals()), label=False) 

    for person_id in stub_keys:
      print_stubs(person_id, week_of, table_name)

def populate_pay_stub():

  results = utils.select('''
  select
  DATE(intime) - interval (DAYOFWEEK(intime) -1) DAY as week_of,
  employee_tax_info.person_id,
  last_name, first_name,
  sum(hours_worked) as hours_worked,
  pay_rate, 
  allowances,
  nominal_scale,
  round(sum(hours_worked)*pay_rate) as weekly_pay,
  round(sum(hours_worked)*pay_rate*nominal_scale) as gross_wages,
  married,
  sum(tip_pay) tips,
  round(sum(hours_worked)*pay_rate - weekly_tax) + sum(tip_pay) as total_weekly,
  sum(tip_pay) / sum(hours_worked) + pay_rate as total_hourly_pay
  from hours_worked JOIN employee_tax_info ON hours_worked.person_id = employee_tax_info.person_id
  where yearweek(intime) = yearweek(now() - interval '1' week)
  and intime != 0
  group by employee_tax_info.person_id
  ''',
  incursor = None,
  label = True
  )

  for row in results:
    add_witholding_fields(employee_tax_info = row)
    columns = ','.join(row.keys())
    values = ','.join(map(str,row.values()))
    utils.execute('''INSERT into pay_stub (%s) VALUES (%s)'''%(columns, values))

def nightly_sales_by_server(label=False, lag_days=1):

  tax_rate = texttab.TAXRATE

  return utils.select('''
    select sales.*, rbs.cc1, rbs.cc2, rbs.cash1, rbs.cash2, rbs.id as receipts_id
    from
    (
    SELECT 
      concat(p.last_name, ', ', p.first_name) server,
      p.id as person_id,
      p.ccid,
      sum(oi.price) sales, 
      sum(ti.price) taxable_sales,
      sum(oi.price) + COALESCE(round(sum(ti.price) * %(tax_rate)s, 2),0) receipts,
      count(distinct og.id) tabs_closed,
      convert(date(now() - INTERVAL '%(lag_days)s' DAY), CHAR(10)) as dat
    FROM (order_item oi left outer join taxable_item ti on ti.id = oi.id), order_group og, person p 
    WHERE oi.order_group_id = og.id 
    AND oi.is_cancelled = False
    AND oi.is_comped = False
    AND og.closedby = p.id 
    AND date(og.updated - interval '6' HOUR) = date(now() - INTERVAL '%(lag_days)s' DAY)
    GROUP BY p.id) sales
    left outer join
    (select *
    from receipts_by_server
    where dat = date(now() - INTERVAL '%(lag_days)s' DAY)
    ) rbs on sales.person_id = rbs.person_id ;''' % locals(),
    incursor=None,
    label=label
  )

def print_this_week_stubs():

  for table_name in ('PAY_STUB', 'WEEKLY_PAY_STUB'):
    stub_keys = utils.select('''
      select person_id, week_of from {table_name} where yearweek(week_of) = yearweek(now() - interval '1' week)'''.
      format(**locals()), label=False) 

    for person_id, week_of in stub_keys:
      print_stubs(person_id, week_of, table_name)