Python database.get_connection函数代码示例

 def post(self):
     try:
         db = get_connection()
         result = self.get_argument("result")
         method = self.get_argument("method", None)
         cart_no = result[2:]
         res = get_model("ecom.cart").search([["number", "=", cart_no]])
         if not res:
             raise Exception("Invalid cart  number")
         cart_id=res[0]
         cart=get_model("ecom.cart").browse(cart_id)
         if method:
             cart.update_paysbuy_method(method)
             db = database.get_connection()
             db.commit()
         if result.startswith("00") and not cart.is_paid:
             set_active_user(1)
             set_active_company(1)
             cart.import_paysbuy_payment()  # Inquiry Doublecheck
             db.commit()
     except Exception as e:
         db = get_connection()
         db.rollback
         import traceback
         audit_log("Failed to get result payment from paysbuy", details=traceback.format_exc())
         traceback.print_exc()

    def post(self):
        try:
            db = get_connection()
            print("########################################")
            print("###########Result Paypal Ipn############")
            print("########################################")

            payment_status = self.get_argument("payment_status", None)
            if payment_status != "Completed":
                raise Exception("Paypal transaction is not completed")
            invoice = self.get_argument("invoice")

            set_active_user(1)
            set_active_company(1)
            res = get_model("ecom.cart").search([["number", "=", invoice]])
            if not res:
                raise Exception("Invalid cart number: %s"%invoice)
            cart_id = res[0]
            cart=get_model("ecom.cart").browse(cart_id)
            website=cart.website_id
            receiver_email = self.get_argument("receiver_email", None)
            if receiver_email != website.paypal_user:
                raise Exception("Wrong paypal receiver email")

            if not website.paypal_user:
                raise Exception("Missing paypal user in cms setting")
            if not website.paypal_password:
                raise Exception("Missing paypal password in cms setting")
            if not website.paypal_signature:
                raise Exception("Missing paypal signature in cms setting")
            if not website.paypal_url:
                raise Exception("Missing paypal URL Server in cms setting")
            params = {}
            for argument in self.request.arguments:
                params[argument] = argument[0].decode('utf-8')
            params['cmd'] = '_notify-validate'
            if website.paypal_url == "test":
                url = "https://www.sandbox.paypal.com/cgi-bin/webscr"
            else:
                url = "https://www.paypal.com/cgi-bin/webscr"
            data = urllib.parse.urlencode(params)
            data = data.encode('utf-8')
            req = urllib.request.Request(url, data)
            response = urllib.request.urlopen(req)
            word = response.read()
            verify = word.decode('utf-8')
            if verify != "VERIFIED":
                raise Exception("Failed to verify payment")
            mc_gross = float(self.get_argument("mc_gross", None))
            if cart.amount_total != mc_gross:
                raise Exception("Amount total doesn't match")
            cart.import_paypal_payment()  # TODO Add Token
            print("Payment Created")
            db.commit()
        except Exception as e:
            db = get_connection()
            db.rollback
            import traceback
            audit_log("Failed to get IPN from paypal", details=traceback.format_exc())
            traceback.print_exc()

 def login(self, context={}):
     set_active_user(None)
     data = context["data"]
     db_name = data.get("db_name")
     if not db_name:
         raise Exception("Missing db name")
     database.set_active_db(db_name)
     login = data["login"]
     password = data["password"]
     user_id = get_model("base.user").check_password(login, password)
     if not user_id:
         audit_log("Invalid login (%s)" % login)
         db = database.get_connection()
         db.commit()
         raise Exception("Invalid login")
     try:
         print("login ok", login)
         set_active_user(1)
         user = get_model("base.user").browse(user_id)
         if user.profile_id.prevent_login or not user.active:
             raise Exception("User not allowed to login")
         t = time.strftime("%Y-%m-%d %H:%M:%S")
         user.write({"lastlog": t})
         profile = user.profile_id
         action = profile.home_action or "account_board"
         token = new_token(db_name, user_id)
         db = database.get_connection()
         res = db.get("SELECT * FROM pg_class WHERE relname='settings'")
         settings = get_model("settings").browse(1)
         version = settings.version
         mod_version = get_module_version()
         if version != mod_version:
             raise Exception("Database version (%s) is different than modules version (%s), please upgrade database before login." % (
                 version, mod_version))
         company_id = user.company_id.id or profile.login_company_id.id
         if not company_id:
             res = get_model("company").search([["parent_id", "=", None]])
             if not res:
                 raise Exception("No company found")
             company_id = res[0]
         comp = get_model("company").browse(company_id)
         return {
             "cookies": {
                 "dbname": database.get_active_db(),
                 "user_id": user_id,
                 "token": token,
                 "user_name": user.name,
                 "package": settings.package,
                 "company_id": company_id,
                 "company_name": comp.name,
             },
             "next": {
                 "type": "url",
                 "url": "/ui#name=%s" % action,
             },
             "login_action": action,
         }
     finally:
         set_active_user(user_id)
         audit_log("Login")

    def check_payment_online(self, ids, context={}):
        obj = self.browse(ids)[0]
        date = time.strftime('%Y%m%d%H%M%S')
        qs = urllib.parse.urlencode([
            ('mid', 1000006741),
            ('terminal', 524114384),
            ('command', 'CRINQ'),
            ('ref_no', obj.number),
            ('ref_date', date),
            ('service_id', 10),
            ('cur_abbr', 'THB'),
            ('amount', obj.amount_total),
        ])

        url = 'https://nsips-test.scb.co.th:443/NSIPSWeb/NsipsMessageAction.do?'
        data = qs.encode('utf-8')
        req = urllib.request.Request(url, data)
        response = urllib.request.urlopen(req)
        ur = response.read()
        te = ur.decode('utf-8')
        p = urllib.parse.parse_qsl(te)
        params = dict(list(map(lambda x: (x[0], x[1]), p)))
        payment_status = params['payment_status'] or ''
        amount = params['amount'] or ''
        trans_no = params['trans_no'] or ''
        if payment_status == "002":
            try:
                db = get_connection()
                vals = {
                    "type": "in",
                    "pay_type": "invoice",
                    "contact_id": obj.contact_id.id,
                    "date": time.strftime("%Y-%m-%d"),
                    "ref": trans_no,
                    "account_id": obj.account_id.id,
                    "currency_id": obj.currency_id.id,
                    "lines": [("create", {
                        "type": "invoice",
                        "invoice_id": obj.id,
                        "account_id": obj.account_id.id,
                        "amount": amount,
                    })]
                }
                pmt_id = get_model("account.payment").create(vals, context={"type": vals["type"]})
                get_model("account.payment").post([pmt_id])
                db.commit()
            except Exception as e:
                db = get_connection()
                db.rollback
                import traceback
                audit_log("Failed to get result payment from scb", details=traceback.format_exc())
                traceback.print_exc()

 def post(self):
     db=get_connection()
     try:
         try:
             print("CHECK protocol XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX")
             print(self.request.protocol)
             print("CHECK protocol XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX")
             fields=["email","password"]
             field_errors={}
             form_vals={}
             for n in fields:
                 v=self.get_argument(n,None)
                 form_vals[n]=v
                 if not v:
                     field_errors[n]=True
             if field_errors:
                 raise Exception("Some required fields are missing")
             user_id=get_model("base.user").check_password(form_vals["email"],form_vals["password"])
             if not user_id:
                 raise Exception("Invalid login")
             set_active_user(user_id)
             dbname=get_active_db()
             token=new_token(dbname,user_id)
             self.set_cookie("user_id",str(user_id))
             self.set_cookie("token",token)
             cart_id=self.get_cookie("cart_id")
             if cart_id:
                 cart_id=int(cart_id)
                 get_model("ecom.cart").set_default_address([cart_id])
             db.commit()
             url=self.get_argument("return_url",None)
             if not url:
                 url="/cms_account"
             self.redirect(url)
         except Exception as e:
             db=get_connection()
             error_message=str(e)
             ctx=self.context
             ctx["form_vals"]=form_vals
             ctx["error_message"]=error_message
             ctx["field_errors"]=field_errors
             content=render("cms_login",ctx)
             ctx["content"]=content
             html=render("cms_layout",ctx)
             self.write(html)
             db.commit()
     except:
         import traceback
         traceback.print_exc()
         db.rollback()

 def post(self):
     db = get_connection()
     try:
         data_json = self.get_argument("json_str",None)
         if not data_json:
             raise Exception("Can't get json_str")
         data = json.loads(data_json)
         cart_id = self.get_cookie("cart_id",None)
         if not cart_id:
             raise Exception("Can't get cart id")
         cart_id = int(cart_id)
         cart = get_model("ecom.cart").browse(cart_id)
         order = 0
         for line in cart.lines:
             for item in data: 
                 if item.get('order') == order:
                     line.write({"ship_method_id": item.get('method_id')})
             order += 1
         cart.calc_shipping()
         amount_ship = "{:0,.2f}".format(float(cart.amount_ship))
         amount_total = "{:0,.2f}".format(float(cart.amount_total))
         total = {
             "amount_ship": amount_ship,
             "amount_total": amount_total,
         }
         data = json.dumps(total)
         self.write(data)
         db.commit()
     except:
         import traceback
         traceback.print_exc()
         db.rollback()

def send_notifs():
    #print("send_notifs",os.getpid(),list(listen_handlers.keys()))
    t=time.strftime("%Y-%m-%d %H:%M:%S")
    db_handlers={}
    for listener_id,h in listen_handlers.items(): # XXX
        db_handlers.setdefault(h.dbname,[]).append(listener_id)
    for dbname,listener_ids in db_handlers.items():
        set_active_db(dbname)
        db=get_connection()
        try:
            ids_sql="("+",".join(str(x) for x in listener_ids)+")"
            res=db.query("SELECT listener_id,name FROM ws_event WHERE listener_id IN "+ids_sql)
            if res:
                handler_events={}
                for r in res:
                    handler_events.setdefault(r.listener_id,[]).append(r.name)
                for listener_id,events in handler_events.items():
                    print("NOTIFY %s %s %s"%(dbname,listener_id,events))
                    handler=listen_handlers[listener_id]
                    handler.write(json.dumps(events))
                    handler.finish()
                    del listen_handlers[listener_id]
                db.execute("DELETE FROM ws_event WHERE listener_id IN "+ids_sql)
            db.execute("UPDATE ws_listener SET last_check_time=%s WHERE id IN "+ids_sql,t) # XXX: do this less often
            db.execute("DELETE FROM ws_listener WHERE last_check_time<TIMESTAMP %s-INTERVAL '10 seconds'",t) # XXX: do this less often, FIXME
            db.commit()
        except:
            print("#########################################################")
            print("ERROR: send_notifs failed")
            db.rollback()
            import traceback
            traceback.print_exc()
    io_loop=tornado.ioloop.IOLoop.instance()
    io_loop.add_timeout(time.time()+POLL_WAIT,send_notifs)

 def get(self):
     db = get_connection()
     try:
         cart_id = int(self.get_argument("cart_id"))
         print("cart_id", cart_id)
         cart = get_model("ecom.cart").browse(cart_id)
         set_active_company(1)
         user_id = get_active_user()
         website = self.context["website"]
         ctx = self.context
         ctx["cart"] = cart
         if not cart.is_paid and website.payment_slip_template_id and (cart.pay_method_id.id == website.bank_method_id.id):
             tmpl_name = website.payment_slip_template_id.name
             url = "/report?type=report_jasper&model=ecom.cart&convert=pdf&ids=[%d]&template=%s" % (
                 cart.id, tmpl_name)
             ctx["payment_slip_report_url"] = url
         content = render("ecom_order_confirmed", ctx)
         ctx["content"] = content
         html = render("cms_layout", ctx)
         self.write(html)
         db.commit()
     except:
         import traceback
         traceback.print_exc()
         db.rollback()

def init_db():
    db = database.get_connection()
    db.execute("INSERT INTO settings (id) VALUES (1)")
    enc_pass=encrypt_password('1234')
    db.execute("INSERT INTO profile (id,name) VALUES (1,'System Admin')")
    db.execute("INSERT INTO base_user (id,login,password,name,profile_id,active) VALUES (1,'admin',%s,'Admin',1,true)",enc_pass)
    db.execute("INSERT INTO company (id,name) VALUES (1,'Test Company')")

 def get(self):
     raise Exception("Polling is disabled") # XXX
     #print("ListenPoll.get",os.getpid())
     global sending_notifs
     t=time.strftime("%Y-%m-%d %H:%M:%S")
     dbname=get_active_db()
     if not dbname:
         raise Exception("Missing dbname in long poll request")
     db=None
     try:
         db=get_connection()
         user_id=self.get_cookie("user_id",None)
         if user_id:
             user_id=int(user_id)
         res=db.get("INSERT INTO ws_listener (user_id,last_check_time) VALUES (%s,%s) RETURNING id",user_id,t)
         self.listener_id=res.id
         self.dbname=dbname
         listen_handlers[self.listener_id]=self
         if not sending_notifs:
             io_loop=tornado.ioloop.IOLoop.instance()
             io_loop.add_timeout(time.time()+POLL_WAIT,send_notifs) # XXX: should start this directly when process is started?
             sending_notifs=True
         db.commit()
     except:
         print("#########################################################")
         print("ERROR: ListenPoll.get failed")
         if db:
             db.rollback()
         import traceback
         traceback.print_exc()

 def get(self):
     db = get_connection()
     try:
         keyword = self.get_argument("keyword")
         domain = [["or",["name","ilike",keyword],["description","ilike",keyword],["code","ilike",keyword]],["parent_id","=",None],["is_published","=",True]]
         result = get_model("product").search_browse(domain)
         #if not result:
             #raise Exception("Product not found")
         products = []
         for product in result:
             vals = {
                 "name": product.name,
                 "id": product.id,
                 "desc": product.description[:20]+"..." if product.description else ""
             }
             products.append(vals)
         products = sorted(products, key=lambda k: k['name'])
         print(products)
         data = json.dumps(products)
         self.write(data)
         db.commit()
     except:
         import traceback
         traceback.print_exc()
         db.rollback()

 def post(self):
     print("POST ARGUMENT >>>>>>>>>>>>>>>>>>>")
     print(self.request.body)
     f = open("paypal_return", "a")
     s = "################################################################################################################" + \
         "\n"
     s += "Date : " + time.strftime("%Y-%m-%d %H:%M:%S") + "\n"
     s += "Request : " + str(self.request) + "\n"
     if self.request.body:
         s += "Body : " + str(self.request.body) + "\n"
     s += "################################################################################################################" + \
         "\n"
     f.write(s)
     f.close()
     cart_id = int(self.get_argument("cart_id"))
     token = self.get_argument("token", None)
     payer_id = self.get_argument("PayerID", None)
     cart = get_model("ecom.cart").browse(cart_id)
     if not cart.is_paid:
         access.set_active_user(1)
         access.set_active_company(1)
         cart.import_paypal_payment(token, context={})
         db = database.get_connection()
         db.commit()
     self.redirect("/ecom_order_confirmed?cart_id=%s" % cart_id)

    def get(self):
        db = get_connection()
        try:
            ctx = self.context
            brand_id = self.get_argument("brand_id", None)
            if brand_id:
                brand_id = int(brand_id)
            else:
                brand_code = self.get_argument("brand_code", None)
                res = get_model("product.brand").search([["code", "=", brand_code]])
                if not res:
                    raise Exception("Product brand not found: '%s'" % brand_code)
                brand_id = res[0]
            res = get_model("product.brand").browse([brand_id])
            if not res:
                raise Exception("Can't find product brand id: ", brand_id)
            brand = res[0]
            ctx["product_brand"] = brand
            ctx["parent_brand_list"] = list_brand_parent(brand, brand_list=[])
            content = render("ecom_product_brand", ctx)
            ctx["content"] = content
            html = render("cms_layout", ctx)
            self.write(html)
            db.commit()
        except:
            self.redirect("/cms_page_not_found")
            import traceback

            traceback.print_exc()
            db.rollback()

 def get(self):
     db = get_connection()
     if db:
         db.begin()
     ctx = {
         "dbname": get_active_db(),
         "locale": netforce.locale.get_active_locale(),
         "css_file": static.get_css_file(),
         "js_file": static.get_js_file(),
     }
     static.check_ui_params_db()
     html = netforce.template.render("action_local", {"context": ctx})
     self.write(html)
     db = get_connection()
     if db:
         db.commit()  # XXX

 def money_out(self, context={}):
     company_id = get_active_company()
     company_ids = get_model("company").search([["id", "child_of", company_id]])
     db = get_connection()
     res = db.query(
         "SELECT COALESCE(l.due_date,l.move_date) AS due_date,SUM(l.credit-l.debit) as amount FROM account_move_line l JOIN account_account a ON a.id=l.account_id LEFT JOIN account_reconcile r ON r.id=l.reconcile_id WHERE l.move_state='posted' AND a.type='payable' AND l.reconcile_id IS NULL AND a.company_id IN %s GROUP BY COALESCE(l.due_date,l.move_date)",
         tuple(company_ids),
     )
     amounts = {}
     for r in res:
         amounts[r.due_date] = r.amount
     values = []
     d0 = date.today()
     d1 = d0 + timedelta(days=60)
     d = d0
     while d < d1:
         ds = d.strftime("%Y-%m-%d")
         values.append((js_time(d), amounts.get(ds, 0)))
         d += timedelta(days=1)
     data = {"value": [{"key": "Payable", "values": values}]}
     res = db.get(
         "SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='in' AND inv_type='invoice' AND state='draft' AND company_id IN %s",
         tuple(company_ids),
     )
     if res:
         data["draft_count"] = res.count
         data["draft_amount"] = res.amount
     res = db.get(
         "SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='in' AND inv_type='invoice' AND state='waiting_payment' AND due_date<now() AND company_id IN %s",
         tuple(company_ids),
     )
     if res:
         data["overdue_count"] = res.count
         data["overdue_amount"] = res.amount
     return data