source: ccsd/private/modules/ccs_rurallink_billing.py

Last change on this file was 1708, checked in by ckb6, 3 years ago

Private changes for new charged flag
  • Property svn:keywords set to Id
File size: 10.9 KB
Line 
1# Copyright (C) 2006  Rural Link Ltd.
2#
3# This file is an extension to crcnetd - CRCnet Configuration System Daemon
4#
5# RuralLink Billing Module - Provides logic for manipulating customer accounts
6#                            according to the Rural Link business rules.
7#
8# Author:       Matt Brown <matt@crc.net.nz>
9#               Ivan Meredith <ivan@ivan.net.nz>
10# Version:      $Id$
11#
12# No usage or redistribution rights are granted for this file unless
13# otherwise confirmed in writing by the copyright holder.
14from crcnetd._utils.ccsd_common import *
15from crcnetd._utils.ccsd_config import config_getboolean, config_get
16from crcnetd._utils.ccsd_log import *
17from crcnetd._utils.ccsd_events import *
18from crcnetd._utils.ccsd_session import getSession, getSessionE
19from datetime import datetime, timedelta
20from crcnetd._utils.ccsd_server import exportViaXMLRPC
21
22from crcnetd.modules.ccs_billing import billing_email
23from Cheetah.Template import Template
24
25ccs_mod_type = CCSD_SERVER
26
27#####################################################################
28# Scheduled event handling
29#####################################################################
30@catchEvent("billingRegularUpdate")
31def checkUsage(*args, **kwargs):
32    """Check customer data usage. Perform cap actions and warnings as
33       necessary"""
34    session = getSession(ADMIN_SESSION_ID)
35
36    #Do warnings first to ensure that a warning email in generated
37    sql = "SELECT cs.*, c.* FROM broadband_account cs, customers c WHERE " \
38            "cs.login_id=c.login_id AND cs.used_mb > cs.cap_at_mb*0.8 " \
39            "AND cs.warning_sent=false AND cs.charged=true"
40    res = session.query(sql, ())
41    for state in res:
42        doCapWarn(state)
43       
44    #Do any cap ations required
45    sql = "SELECT cs.*, c.* FROM broadband_account cs, v_customers c WHERE " \
46            "cs.login_id=c.login_id AND cs.used_mb > cs.cap_at_mb"
47    res = session.query(sql, ())
48    for state in res:
49        doCapAction(state)
50       
51
52
53@catchEvent("billingDaily")
54def rurallinkBillingDaily(*args, **kwargs):
55    """Peforms plan rollover for any daily plans"""
56    doRollover("daily")
57
58@catchEvent("billingWeekly")
59def rurallinkBillingWeekly(*args, **kwargs):
60    """Peforms plan rollover for any weekly plans"""
61    doRollover("weekly")
62
63@catchEvent("billingMonthly")
64def rurallinkBillingMonthly(*args, **kwargs):
65    """Peforms plan rollover for any monthly plans"""
66    doRollover("monthly")
67
68@catchEvent("billingMonthEnd")
69def ruralllinkBillingMonthEnd(*args, **kwargs):
70    """Generate billing records for all plans and users at the end of month"""
71    doBilling()
72
73#####################################################################
74# Rural Link billing logic implementation
75#####################################################################
76@exportViaXMLRPC(SESSION_RW, AUTH_ADMINISTRATOR)
77def processBilling(auth, as_at=None):
78    doBilling(as_at)
79    return True
80
81def doBilling(as_at=None):
82    """Called on the monthly event to charge the customer for their plan
83   
84    The monthly event is called on the last day of the month, so we add one
85    to the current date to report the month that we're billing the customer
86    for.
87    """
88    session = getSession(ADMIN_SESSION_ID)
89    print "DOING BILLING"
90    # Generate a billing record for each plan that is active in the system
91    sql = "SELECT * FROM rollover"
92    res = session.query(sql, ())
93
94    n = timedelta(1)
95    # Select all unbilled records up till NOW or the time specified,
96    # date the invoice one day earlier
97    if as_at is None:
98        datestr = datetime.strftime(datetime.now() + n, "%b %Y")
99        db_time = datetime.strftime(datetime.now(), "%Y-%m-%d %H:%M:%S")
100    else:
101        datestr = datetime.fromtimestamp(as_at).strftime("%b %Y")
102        db_time = datetime.fromtimestamp(as_at).strftime("%Y-%m-%d %H:%M:%S")
103
104   
105    for ro in res:
106        if ro['charged'] == 'f':
107            continue
108
109        sql = "INSERT INTO billing_record (customer_id, record_date, " \
110            "description, amount, quantity, record_type) VALUES ((select customer_id from customers where login_id=%s), %s, " \
111            "%s, %s, 1, 'Plan');"
112        desc = "%s: %s" % (datestr, ro["description"])
113        try:
114            session.execute(sql, (ro["login_id"], db_time, desc, ro["price"]))
115        except:
116            log_error("Failed to generate billing record for contact #%s" % \
117                    ro["login_id"], sys.exc_info())
118
119def doRollover(type):
120    """Handles resetting customer caps when their usage rolls over"""
121    session = getSession(ADMIN_SESSION_ID)
122   
123    sql = "SELECT * FROM rollover WHERE cap_period=%s"
124    res = session.query(sql, (type))
125    for ro in res:
126        try:           
127            # Update the broadband_account table to reset the cap
128            sql = "UPDATE broadband_account SET plan_start=CURRENT_TIMESTAMP, "\
129                "cap_at_mb=%s, used_mb=0, radius_plan=%s WHERE login_id=%s"
130            session.execute(sql, (ro["included_mb"], ro["radius_plan"], \
131                    ro["login_id"]))
132        except:
133            log_error("Failed to reset cap and state for contact #%s" % \
134                    ro["login_id"], sys.exc_info())
135
136    #Reset the warning sent flags
137    try:           
138        sql = "UPDATE broadband_account SET warning_sent=false where exists " \
139            "(select billing_plan.plan_id, billing_plan.cap_period from " \
140            "billing_plan where broadband_account.plan_id = " \
141            "billing_plan.plan_id AND billing_plan.cap_period=%s)"
142        session.execute(sql, (type))
143    except:
144        log_error("Failed to reset warning_sent field")
145   
146def doCapWarn(customer):
147    session = getSession(ADMIN_SESSION_ID)
148
149    sql = "SELECT cap_action, cap_param, cap_price, description FROM " \
150            "billing_plan WHERE plan_id=%s"
151    res = session.query(sql, (customer["plan_id"]))
152   
153    sql = "UPDATE broadband_account SET warning_sent='t' WHERE " \
154        "login_id=%s"
155    session.execute(sql,(customer["login_id"]))   
156
157    subject = "Data Cap Notice: 80% used" 
158    if res[0]["cap_action"] == "throttle":
159        sendCapEmail(customer, subject, "throttle-warn")
160    elif res[0]["cap_action"] == "purchase":
161        customer["purchase_mb"] = res[0]["cap_param"]
162        customer["purchase_price"] = "$%.2f" % (res[0]["cap_price"]*1.125)
163        sendCapEmail(customer, subject, "purchase-warn")
164    else:
165        log_error("Unknown cap action '%s' for contact '%s'" % \
166                (res[0]["cap_action"], customer['username']))
167
168def doCapAction(customer):
169    """Called when a customer has exceeded their cap.
170
171    Rate limits or purchases more data for the customer, based on their chosen
172    plan.
173    """
174    session = getSession(ADMIN_SESSION_ID)
175
176    sql = "SELECT cap_action, cap_param, cap_price, description FROM " \
177            "billing_plan WHERE plan_id=%s"
178    res = session.query(sql, (customer["plan_id"]))
179   
180    used_mb = int(customer["used_mb"])
181    subject = "Data Cap Notice: %.1fGB used." % (used_mb/1024.0) 
182   
183    if res[0]["cap_action"] == "throttle":
184        # Customer has chosen to be throttled
185        current_plan =  customer["radius_plan"]
186        if current_plan == res[0]["cap_param"]:
187            # Already throttled
188            return
189        else:
190            # Throttle and notify
191            sql = "UPDATE broadband_account SET radius_plan=%s WHERE " \
192                    "login_id=%s"
193            session.execute(sql,(res[0]["cap_param"], customer["login_id"]))
194            log_info("Moved '%s' to plan %s (throttled)" % \
195                    (customer["username"], res[0]["description"]))
196            # Tell the customer
197            sendCapEmail(customer, subject, "throttle")
198    elif res[0]["cap_action"] == "purchase":
199        # Customer has chosen to purchase more data
200        cap_at_mb = customer["cap_at_mb"]
201        used_mb = customer["used_mb"]
202       
203        # If the customer is leeching at full rate, it's possible that we may
204        # have to purchase multiple extra blocks of usage to get their cap
205        # back above their current data usage.
206        extra_mb = 0
207        extra_price = 0
208        while cap_at_mb < used_mb:
209            try:
210                sql = "UPDATE broadband_account SET cap_at_mb=cap_at_mb+%s " \
211                        "WHERE login_id=%s"
212                session.execute(sql,(res[0]["cap_param"],
213                    customer["login_id"]))
214               
215                sql = "INSERT INTO billing_record (customer_id, record_date, " \
216                        "description, amount, quantity,record_type) VALUES (%%s, " \
217                        "CURRENT_TIMESTAMP, '%sMB data pack', %%s, 1, 'Data')" % \
218                        (res[0]["cap_param"])
219                session.execute(sql, (customer["customer_id"], 
220                    res[0]["cap_price"]))
221                cap_at_mb += res[0]["cap_param"]
222                log_info("Purchased %sMB block for '%s'. New cap: %sMB." % \
223                        (res[0]["cap_param"], customer["username"], cap_at_mb))
224                extra_mb += res[0]["cap_param"]
225                extra_price += res[0]["cap_price"]
226            except:
227                log_error("Failed to purchase new block for '%s'!" % \
228                        customer["username"], sys.exc_info())
229                break
230        # Tell the customer
231        customer["purchased_mb"] = extra_mb
232        customer["purchased_price"] = "$%.2f" % (extra_price*1.125)
233        sendCapEmail(customer, subject, "purchase")
234    else:
235        log_error("Unknown cap action '%s' for contact '%s'" % \
236                (res[0]["cap_action"], customer['username']))
237
238def sendCapEmail(customer, subject, action):
239    """Sends the user an email regarding their cap
240
241    The second parameter is used to choose a template which decides the actual
242    message that the user will receive.
243    Valid values are typically the same as the cap actions.
244    Eg (throttle, purchase), but can be any value that will find a valid file in
245    the billing template directory.
246    """
247    from crcnetd.modules.ccs_billing import billing_template_dir
248
249    # Can't send email if no template available
250    if billing_template_dir is None:
251        log_error("Failed to load cap email template; No template specified")
252        return False
253    template = "%s/cap-%s-email.tmpl" % (billing_template_dir, action)
254    try:
255        email_template = Template(file=template)
256    except:
257        log_error("Failed to load cap email template: %s" % template,
258                sys.exc_info())
259        return ""
260
261    # Setup data for the body of the email
262    info = {}
263    info["customer"] = customer
264    email_template._searchList = [info]
265    email_template._CHEETAH__searchList += email_template._searchList
266
267    # Send the email
268    rv = billing_email(customer["email"], subject, 
269            str(email_template).strip(), [])
270    if rv:
271        log_info("Emailed Cap Notice to '%s'" % customer["email"])
272        return True
273    else:
274        log_error("Failed to email Cap Notice to '%s'" % customer["email"])
275        return False
Note: See TracBrowser for help on using the repository browser.