def _test(i):
self.assertEquals(
i + 1,
webnotes.conn.sql(
"""select count(*) from `tabSales Invoice`
where recurring_id=%s and docstatus=1""", base_si.doc.recurring_id)[0][0])
next_date = add_months(base_si.doc.posting_date, no_of_months)
manage_recurring_invoices(next_date=next_date, commit=False)
recurred_invoices = webnotes.conn.sql(
"""select name from `tabSales Invoice`
where recurring_id=%s and docstatus=1 order by name desc""",
base_si.doc.recurring_id)
self.assertEquals(i + 2, len(recurred_invoices))
new_si = webnotes.bean("Sales Invoice", recurred_invoices[0][0])
for fieldname in [
"convert_into_recurring_invoice", "recurring_type",
"repeat_on_day_of_month", "notification_email_address"
]:
self.assertEquals(base_si.doc.fields.get(fieldname),
new_si.doc.fields.get(fieldname))
self.assertEquals(new_si.doc.posting_date, unicode(next_date))
self.assertEquals(
new_si.doc.invoice_period_from_date,
unicode(
add_months(base_si.doc.invoice_period_from_date,
no_of_months)))
if first_and_last_day:
self.assertEquals(
new_si.doc.invoice_period_to_date,
unicode(
get_last_day(
add_months(base_si.doc.invoice_period_to_date,
no_of_months))))
else:
self.assertEquals(
new_si.doc.invoice_period_to_date,
unicode(
add_months(base_si.doc.invoice_period_to_date,
no_of_months)))
self.assertEquals(
getdate(new_si.doc.posting_date).day,
base_si.doc.repeat_on_day_of_month)
return new_si
def get_half_yearly_trend_details(self):
first_half_start = self.year_start_date
first_half_end = add_days(add_months(first_half_start,6),-1)
second_half_start = add_days(first_half_end,1)
second_half_end = add_days(add_months(second_half_start,6),-1)
col_names = ['First Half (Qty)', 'First Half (Amt)', 'Second Half (Qty)', 'Second Half (Amt)']
query_val = 'SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+first_half_start+'" AND "'+first_half_end+'" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+first_half_start+'" AND "'+first_half_end+'" THEN t2.amount ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+second_half_start+'" AND "'+second_half_end+'" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+second_half_start+'" AND "'+second_half_end+'" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def get_quarterly_trend_details(self):
first_qsd, second_qsd, third_qsd, fourth_qsd = self.year_start_date, add_months(self.year_start_date,3), add_months(self.year_start_date,6), add_months(self.year_start_date,9)
first_qed, second_qed, third_qed, fourth_qed = add_days(add_months(first_qsd,3),-1), add_days(add_months(second_qsd,3),-1), add_days(add_months(third_qsd,3),-1), add_days(add_months(fourth_qsd,3),-1)
col_names = ['Q1 (Qty)','Q1 (Amt)','Q2 (Qty)','Q2 (Amt)','Q3 (Qty)','Q3 (Amt)','Q4 (Qty)','Q4 (Amt)']
query_val = ''
bet_dates = [[first_qsd,first_qed],[second_qsd,second_qed],[third_qsd,third_qed],[fourth_qsd,fourth_qed]]
for d in bet_dates:
query_val += 'SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+d[0]+'" AND "'+d[1]+'" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.'+self.trans_date+' BETWEEN "'+d[0]+'" AND "'+d[1]+'" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def _test(i):
self.assertEquals(
i + 1,
webnotes.conn.sql(
"""select count(*) from `tabSales Invoice`
where recurring_id=%s and docstatus=1""",
base_si.doc.recurring_id,
)[0][0],
)
next_date = add_months(base_si.doc.posting_date, no_of_months)
manage_recurring_invoices(next_date=next_date, commit=False)
recurred_invoices = webnotes.conn.sql(
"""select name from `tabSales Invoice`
where recurring_id=%s and docstatus=1 order by name desc""",
base_si.doc.recurring_id,
)
self.assertEquals(i + 2, len(recurred_invoices))
new_si = webnotes.bean("Sales Invoice", recurred_invoices[0][0])
for fieldname in [
"convert_into_recurring_invoice",
"recurring_type",
"repeat_on_day_of_month",
"notification_email_address",
]:
self.assertEquals(base_si.doc.fields.get(fieldname), new_si.doc.fields.get(fieldname))
self.assertEquals(new_si.doc.posting_date, unicode(next_date))
self.assertEquals(
new_si.doc.invoice_period_from_date,
unicode(add_months(base_si.doc.invoice_period_from_date, no_of_months)),
)
if first_and_last_day:
self.assertEquals(
new_si.doc.invoice_period_to_date,
unicode(get_last_day(add_months(base_si.doc.invoice_period_to_date, no_of_months))),
)
else:
self.assertEquals(
new_si.doc.invoice_period_to_date,
unicode(add_months(base_si.doc.invoice_period_to_date, no_of_months)),
)
return new_si
def get_half_yearly_trend_details(self):
first_half_start = self.year_start_date
first_half_end = add_days(add_months(first_half_start, 6), -1)
second_half_start = add_days(first_half_end, 1)
second_half_end = add_days(add_months(second_half_start, 6), -1)
col_names = [
'First Half (Qty)', 'First Half (Amt)', 'Second Half (Qty)',
'Second Half (Amt)'
]
query_val = 'SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + first_half_start + '" AND "' + first_half_end + '" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + first_half_start + '" AND "' + first_half_end + '" THEN t2.amount ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + second_half_start + '" AND "' + second_half_end + '" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + second_half_start + '" AND "' + second_half_end + '" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def get_allocated_budget(distribution_id, posting_date, fiscal_year,
yearly_budget):
if distribution_id:
distribution = {}
for d in webnotes.conn.sql(
"""select bdd.month, bdd.percentage_allocation
from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd
where bdd.parent=bd.name and bd.fiscal_year=%s""",
fiscal_year,
as_dict=1):
distribution.setdefault(d.month, d.percentage_allocation)
dt = webnotes.conn.get_value("Fiscal Year", fiscal_year, "year_start_date")
budget_percentage = 0.0
while (dt <= getdate(posting_date)):
if distribution_id:
budget_percentage += distribution.get(
getdate(dt).strftime("%B"), 0)
else:
budget_percentage += 100.0 / 12
dt = add_months(dt, 1)
return yearly_budget * budget_percentage / 100
def get_loan_details(self):
end= self.doc.doi_closing
list1 = []
#list1.append(self.doc.doi_start)
#webnotes.errprint(start)
#webnotes.errprint(number )
#webnotes.errprint((cstr(self.doc.doi_start) + datetime.timedelta(12*365/12)).isoformat())
#self.doc.doi_closing=(self.doc.doi_start + datetime.timedelta(12*365/12)).isformat()
#webnotes.errprint(self.doc.doi_closing)
#j=0
j=self.doc.number_of_installments
dt=self.doc.doi_start
for j in range(0,j):
date=add_months(getdate(dt),1)
#ebnotes.errprint(["j",date])
#ebnotes.errprint(["hii",end])
if date<=getdate(end):
list1.append(date)
dt=date
#webnotes.errprint(date)
#ebnotes.errprint(list1)
self.doclist=self.doc.clear_table(self.doclist,'installment')
for i in list1:
#ebnotes.errprint("in for loop")
#self.doclist=self.doc.clear_table(self.doclist,'installment')
ch = addchild(self.doc, 'installment',
'Loan Installment Details', self.doclist)
ch.date_of_installment = i
ch.amount_to_be_paid =self.doc.amount_per_month
ch.status='Unpaid'
def send_notification(new_rv):
"""Notify concerned persons about recurring invoice generation"""
subject = "Invoice : " + new_rv.doc.name
com = new_rv.doc.company # webnotes.conn.get_value('Control Panel', '', 'letter_head')
hd = '''<div><h2>%s</h2></div>
<div><h3>Invoice: %s</h3></div>
<table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "50%%"><b>Customer</b><br>%s<br>%s</td>
<td width = "50%%">Invoice Date : %s<br>Invoice Period : %s to %s <br>Due Date : %s</td>
</tr>
</table>
''' % (com, new_rv.doc.name, new_rv.doc.customer, new_rv.doc.address_display, getdate(new_rv.doc.posting_date).strftime("%d-%m-%Y"), \
getdate(add_days(add_months(new_rv.doc.posting_date, -1), 1)).strftime("%d-%m-%Y"), getdate(new_rv.doc.posting_date).strftime("%d-%m-%Y"),\
getdate(new_rv.doc.due_date).strftime("%d-%m-%Y"))
tbl = '''<table border="1px solid #CCC" width="100%%" cellpadding="0px" cellspacing="0px">
<tr>
<td width = "15%%" bgcolor="#CCC" align="left"><b>Item</b></td>
<td width = "40%%" bgcolor="#CCC" align="left"><b>Description</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Qty</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Rate</b></td>
<td width = "15%%" bgcolor="#CCC" align="center"><b>Amount</b></td>
</tr>
'''
for d in getlist(new_rv.doclist, 'entries'):
tbl += '<tr><td>' + d.item_code +'</td><td>' + d.description+'</td><td>' + cstr(d.qty) +'</td><td>' + cstr(d.basic_rate) +'</td><td>' + cstr(d.amount) +'</td></tr>'
tbl += '</table>'
totals =''' <table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "50%%"></td>
<td width = "50%%">
<table width = "100%%">
<tr>
<td width = "50%%">Net Total: </td><td>%s </td>
</tr><tr>
<td width = "50%%">Total Tax: </td><td>%s </td>
</tr><tr>
<td width = "50%%">Grand Total: </td><td>%s</td>
</tr><tr>
<td width = "50%%">In Words: </td><td>%s</td>
</tr>
</table>
</td>
</tr>
<tr><td>Terms and Conditions:</td></tr>
<tr><td>%s</td></tr>
</table>
''' % (new_rv.doc.net_total, new_rv.doc.other_charges_total,new_rv.doc.grand_total, new_rv.doc.in_words,new_rv.doc.terms)
msg = hd + tbl + totals
from webnotes.utils.email_lib import sendmail
sendmail(recipients = new_rv.doc.notification_email_address.split(", "), \
sender=new_rv.doc.owner, subject=subject, parts=[['text/plain', msg]])
def get_quarterly_trend_details(self):
first_qsd, second_qsd, third_qsd, fourth_qsd = self.year_start_date, add_months(
self.year_start_date,
3), add_months(self.year_start_date,
6), add_months(self.year_start_date, 9)
first_qed, second_qed, third_qed, fourth_qed = add_days(
add_months(first_qsd, 3),
-1), add_days(add_months(second_qsd, 3), -1), add_days(
add_months(third_qsd, 3),
-1), add_days(add_months(fourth_qsd, 3), -1)
col_names = [
'Q1 (Qty)', 'Q1 (Amt)', 'Q2 (Qty)', 'Q2 (Amt)', 'Q3 (Qty)',
'Q3 (Amt)', 'Q4 (Qty)', 'Q4 (Amt)'
]
query_val = ''
bet_dates = [[first_qsd, first_qed], [second_qsd, second_qed],
[third_qsd, third_qed], [fourth_qsd, fourth_qed]]
for d in bet_dates:
query_val += 'SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + d[
0] + '" AND "' + d[
1] + '" THEN t2.qty ELSE NULL END), SUM(CASE WHEN t1.' + self.trans_date + ' BETWEEN "' + d[
0] + '" AND "' + d[
1] + '" THEN t2.amount ELSE NULL END),'
return col_names, query_val
def get_deduction_details(self,args):
#webnotes.errprint(args)
arg = json.loads(args)
#webnotes.errprint(arg.get('maxdate'))
end1=add_months(getdate(arg.get('maxdate')),1)
#webnotes.errprint(end1)
#if request=='1':
#webnotes.errprint("in if ")
ch = addchild(self.doc, 'installment',
'Loan Installment Details', self.doclist)
ch.date_of_installment = end1
ch.amount_to_be_paid =self.doc.amount_per_month
ch.status='Unpaid'
def get_allocated_budget(distribution_id, posting_date, fiscal_year, yearly_budget):
if distribution_id:
distribution = {}
for d in webnotes.conn.sql("""select bdd.month, bdd.percentage_allocation
from `tabBudget Distribution Detail` bdd, `tabBudget Distribution` bd
where bdd.parent=bd.name and bd.fiscal_year=%s""", fiscal_year, as_dict=1):
distribution.setdefault(d.month, d.percentage_allocation)
dt = webnotes.conn.get_value("Fiscal Year", fiscal_year, "year_start_date")
budget_percentage = 0.0
while(dt <= getdate(posting_date)):
if distribution_id:
budget_percentage += distribution.get(getdate(dt).strftime("%B"), 0)
else:
budget_percentage += 100.0/12
dt = add_months(dt, 1)
return yearly_budget * budget_percentage / 100
def make_serial_no(self,serial_no):
sr = webnotes.new_bean("Serial No")
sr.doc.serial_no = serial_no
sr.doc.item_code = self.doc.item
sr.make_controller().via_stock_ledger = True
sr.insert()
sr.doc.warehouse = 'Finished Goods - P'
sr.doc.status = "Available"
sr.doc.purchase_document_type = 'Packing Items'
sr.doc.purchase_document_no = self.doc.name
sr.doc.purchase_date = self.doc.creation
sr.save()
qr="select warranty_period from tabItem where name='"+self.doc.item+"'"
res=webnotes.conn.sql(qr)
if res:
exdt=add_months(cstr(nowdate()),cint(res[0][0]))
qr1="update `tabSerial No` set warranty_expiry_date='"+cstr(exdt)+"' where name='"+sr.doc.name+"'"
webnotes.conn.sql(qr1)
webnotes.msgprint(_("Serial No created") + ": " + sr.doc.name)
return sr.doc.name
def make_serial_no(self, serial_no):
sr = webnotes.new_bean("Serial No")
sr.doc.serial_no = serial_no
sr.doc.item_code = self.doc.item
sr.make_controller().via_stock_ledger = True
sr.insert()
sr.doc.warehouse = 'Finished Goods - P'
sr.doc.status = "Available"
sr.doc.purchase_document_type = 'Packing Items'
sr.doc.purchase_document_no = self.doc.name
sr.doc.purchase_date = self.doc.creation
sr.save()
qr = "select warranty_period from tabItem where name='" + self.doc.item + "'"
res = webnotes.conn.sql(qr)
if res:
exdt = add_months(cstr(nowdate()), cint(res[0][0]))
qr1 = "update `tabSerial No` set warranty_expiry_date='" + cstr(
exdt) + "' where name='" + sr.doc.name + "'"
webnotes.conn.sql(qr1)
webnotes.msgprint(_("Serial No created") + ": " + sr.doc.name)
return sr.doc.name
def get_loan_details(self):
end= self.doc.doi_closing
list1 = []
list2= []
j=self.doc.number_of_installments
dt=self.doc.doi_start
#if (self.doc.request_hr_not_to_deduct!=1) or (self.doc.request_hr_not_to_deduct==1 and self.doc.request_status=='Rejected'):
# webnotes.errprint("first")
for j in range(0,j):
date=add_months(getdate(dt),1)
if date<=getdate(end):
list1.append(date)
dt=date
self.doclist=self.doc.clear_table(self.doclist,'installment')
for i in list1:
ch = addchild(self.doc, 'installment',
'Loan Installment Details', self.doclist)
ch.date_of_installment = i
ch.amount_to_be_paid =self.doc.amount_per_month
ch.status='Unpaid'
def get_events(start, end, user=None, for_reminder=False):
if not user:
user = webnotes.session.user
roles = webnotes.get_roles(user)
events = webnotes.conn.sql("""select name, subject, description,
starts_on, ends_on, owner, all_day, event_type, repeat_this_event, repeat_on,
monday, tuesday, wednesday, thursday, friday, saturday, sunday, patient, service
from tabEvent where ((
(date(starts_on) between date('%(start)s') and date('%(end)s'))
or (date(ends_on) between date('%(start)s') and date('%(end)s'))
or (date(starts_on) <= date('%(start)s') and date(ends_on) >= date('%(end)s'))
) or (
date(starts_on) <= date('%(start)s') and ifnull(repeat_this_event,0)=1 and
ifnull(repeat_till, "3000-01-01") > date('%(start)s')
))
%(reminder_condition)s
and (event_type='Public' or owner='%(user)s'
or exists(select * from `tabEvent User` where
`tabEvent User`.parent=tabEvent.name and person='%(user)s')
or exists(select * from `tabEvent Role` where
`tabEvent Role`.parent=tabEvent.name
and `tabEvent Role`.role in ('%(roles)s')))
order by starts_on""" % {
"start":
start,
"end":
end,
"reminder_condition":
"and ifnull(send_reminder,0)=1" if for_reminder else "",
"user":
user,
"roles":
"', '".join(roles)
},
as_dict=1)
# process recurring events
start = start.split(" ")[0]
end = end.split(" ")[0]
add_events = []
remove_events = []
def add_event(e, date):
new_event = e.copy()
new_event.starts_on = date + " " + e.starts_on.split(" ")[1]
if e.ends_on:
new_event.ends_on = date + " " + e.ends_on.split(" ")[1]
add_events.append(new_event)
for e in events:
if e.repeat_this_event:
event_start, time_str = e.starts_on.split(" ")
if e.repeat_on == "Every Year":
start_year = cint(start.split("-")[0])
end_year = cint(end.split("-")[0])
event_start = "-".join(event_start.split("-")[1:])
# repeat for all years in period
for year in range(start_year, end_year + 1):
date = str(year) + "-" + event_start
if date >= start and date <= end:
add_event(e, date)
remove_events.append(e)
if e.repeat_on == "Every Month":
date = start.split("-")[0] + "-" + start.split(
"-")[1] + "-" + event_start.split("-")[2]
# last day of month issue, start from prev month!
try:
getdate(date)
except ValueError:
date = date.split("-")
date = date[0] + "-" + str(cint(date[1]) -
1) + "-" + date[2]
start_from = date
for i in xrange(int(date_diff(end, start) / 30) + 3):
if date >= start and date <= end and date >= event_start:
add_event(e, date)
date = add_months(start_from, i + 1)
remove_events.append(e)
if e.repeat_on == "Every Week":
weekday = getdate(event_start).weekday()
# monday is 0
start_weekday = getdate(start).weekday()
# start from nearest weeday after last monday
date = add_days(start, weekday - start_weekday)
for cnt in xrange(int(date_diff(end, start) / 7) + 3):
if date >= start and date <= end and date >= event_start:
add_event(e, date)
date = add_days(date, 7)
remove_events.append(e)
if e.repeat_on == "Every Day":
for cnt in xrange(date_diff(end, start) + 1):
date = add_days(start, cnt)
if date >= event_start and date <= end \
and e[weekdays[getdate(date).weekday()]]:
add_event(e, date)
remove_events.append(e)
for e in remove_events:
events.remove(e)
events = events + add_events
for e in events:
# remove weekday properties (to reduce message size)
for w in weekdays:
del e[w]
return events
def get_message(number,message,_type='POST'):
if (cint(len(message[1:-1]))<=0 or cint(len(number[1:-1]))<=0):
invObj={}
invObj['status']='401'
invObj['error']='Incomplete data to send message, Please provide messages and mobile number'
return invObj
from webnotes.model.doc import Document
from webnotes.utils import nowdate,flt,cstr,add_months
m=message[1:-1].split(',')
#return m[2]
bsrn=m[2]
csrn=m[3]
vm=m[4]
un=m[5]
va=m[6]
a="select name from `tabCustomer Details` where name='"+m[0]+"-"+number[1:-1]+"'"
b=webnotes.conn.sql(a)
itm="select item_code from `tabSerial No` where name='"+cstr(m[2])+"'"
itemm=webnotes.conn.sql(itm)
#webnotes.errprint(itemm[0][0])
#webnotes.errprint(itemm)
qrs="select warranty_period from tabItem where name='"+cstr(itemm[0][0])+"'"
#webnotes.errprint("item----------------")
#webnotes.errprint(itemm[0][0])
ress=webnotes.conn.sql(qrs)
#webnotes.errprint("warrenty-----------")
#webnotes.errprint(ress[0][0])
if b:
l = Document('Customer Details',b[0][0])
l.customer_name=m[0]
l.customer_email=m[1]
l.phone_number=number[1:-1]
l.save()
webnotes.conn.commit()
m = Document('Customer Data')
m.serial_no=bsrn
m.warranty_start_on=nowdate()
m.vehical_make=vm
m.vehical_age=un
m.unit_no=va
m.item=itemm[0][0]
m.capacitor_serial_no=csrn
m.warranty_end_on=add_months(cstr(nowdate()),cint(ress[0][0]))
m.parent=l.name
m.parenttype='Customer Details'
m.parentfield='customer_data'
#webnotes.errprint(m.item)
#webnotes.errprint(add_months(cstr(nowdate()),cint(ress[0][0])))
m.save(new=1)
webnotes.conn.commit()
else:
l = Document('Customer Details')
l.customer_name=m[0]
l.customer_email=m[1]
l.phone_number=number[1:-1]
l.save(new=1)
webnotes.conn.commit()
m = Document('Customer Data')
m.serial_no=bsrn
m.warranty_start_on=nowdate()
m.warranty_end_on=add_months(cstr(nowdate()),cint(ress[0][0]))
m.item=itemm[0][0]
m.vehical_make=vm
m.vehical_age=un
m.unit_no=va
m.capacitor_serial_no=csrn
m.parent=l.name
m.parenttype='Customer Details'
m.parentfield='customer_data'
m.save(new=1)
webnotes.conn.commit()
import requests
from webnotes.model.code import get_obj
msg="Dear Customer, welcome to PowerCap You have purchased the battery with serail no. '"+bsrn+"' today, your warrenty will expire on 29/09/2015, \n Regards,\n PowerCap"
#msg1=msg.split(",")
#return msg
#number='9890865260'
number1=number[1:-1]
webnotes.errprint(number1)
nos=[]
nos.append(number1)
webnotes.errprint(nos)
get_obj('SMS Control', 'SMS Control').send_sms(nos, cstr(msg))
login =[]
invObj={}
invObj['status']='200'
invObj['message']='Updated the record'
return invObj
def create_in(data,_type='POST'):
from webnotes.model.doc import Document
from webnotes.utils import nowdate,add_months
data1=json.loads(data)
auth_key=data1['token']
invoice_type=data1['invoice_type']
customer=data1['customer']
dattime=data1['datetime']
item_details=data1['item_details']
login =[]
loginObj = {}
amt=0
qt=0
srl=''
cc=''
zz=''
#return data
if len(auth_key[1:-1]) <=0 or len(dattime[1:-1]) <=0 or len(customer[1:-1])<=0 :
loginObj['status']='401'
loginObj['error']='Incomplete data to generate Sales Invoice , Please provide token no , Datetime,customer and serial no'
return loginObj
for ff in item_details:
a=ff['barcode']
c="select item_code,warehouse from `tabSerial No` where name='"+a[:-1]+"'"
cc=webnotes.conn.sql(c)
#webnotes.errprint(c)
#webnotes.errprint(cc)
amt=cint(amt)+cint(ff['rate'])
qt+=1
srl+=a[:-2].replace(',','\n')
#return cc
if cc :
qr="select name from `tabauth keys` where auth_key='"+auth_key+"'"
#return qr
res=webnotes.conn.sql(qr)
if res :
rr="select region from `tabFranchise` where contact_email='"+res[0][0]+"'"
r1=webnotes.conn.sql(rr)
if r1[0][0]==cc[0][1] :
#webnotes.errprint("same")
pass
else:
#webnotes.errprint("different warehouse")
pass
zz+="'"+a[:-1].replace(',','')+"',"
else:
if invoice_type=='CUSTOMER':
l = Document('Customer Details',customer)
l.customer_name=customer
l.save()
webnotes.conn.commit()
for ll in item_details:
m = Document('Customer Data')
m.serial_no=a[:-1]
m.parent=l.name
m.parenttype='Customer Details'
m.parentfield='customer_data'
m.save(new=1)
webnotes.conn.commit()
qrt="select name from `tabauth keys` where auth_key='"+auth_key+"'"
res=webnotes.conn.sql(qrt)
net=0
if res:
a="select name from tabCustomer where name='"+customer+"'"
b=webnotes.conn.sql(a)
if b:
a=''
else:
d = Document('Customer')
d.customer_name=customer
d.name=customer
d.save(new=1)
webnotes.conn.commit()
qr="select name from `tabauth keys` where auth_key='"+auth_key+"'"
#webnotes.errprint(qr)
res=webnotes.conn.sql(qr)
rgn=''
if res :
rr="select region from `tabFranchise` where contact_email='"+res[0][0]+"'"
#webnotes.errprint(qr)
r1=webnotes.conn.sql(rr,as_list=1)
#webnotes.errprint(r1[0][0])
d = Document('Sales Invoice')
d.customer=customer
d.customer_name=customer
d.posting_date=dattime[1:-1]
d.due_date=dattime[1:-1]
d.remarks='Invalid QR code'
d.selling_price_list='Standard Selling'
d.currency='INR'
if r1:
d.territory=r1[0][0]
d.region=r1[0][0]
d.net_total_export=0
d.grand_total_export=0
d.rounded_total_export=0
d.plc_conversion_rate=1
from webnotes.utils import nowdate
from accounts.utils import get_fiscal_year
today = nowdate()
d.fiscal_year=get_fiscal_year(today)[0]
d.debit_to=customer[1:-1]+" - P"
d.is_pos=1
d.cash_bank_account='Cash - P'
d.docstatus=1
d.save(new=1)
webnotes.conn.commit()
e=Document('Sales Invoice Item')
e.description="Invalid QR code"
e.qty='1'
e.stock_uom='Nos'
e.serial_no_=a[:-1]
e.parent=d.name
e.parenttype='Sales Invoice'
e.parentfield='entries'
e.save(new=1)
webnotes.conn.commit()
key={}
key['invoice_id']=d.name
login.append(key)
loginObj['status']='200'
loginObj['invoice']=login
return loginObj
#loginObj['status']='400'
#loginObj['error']='invalid serial no (QR Code) please contact administrator'
#return loginObj
z=zz[:-1]
u="select distinct item_code from `tabSerial No` where name in ("+z+")"
s=webnotes.conn.sql(u,as_list=1)
qty_dict={}
rate_dict={}
srl_dict={}
for key in s:
qty_dict.setdefault(key[0],0)
rate_dict.setdefault(key[0],0)
srl_dict.setdefault(key[0],'')
for sss in item_details:
a=sss['barcode']
c="select item_code from `tabSerial No` where name='"+a[:-1]+"'"
cc=webnotes.conn.sql(c)
d=cc and cc[0][0] or ''
qty_dict[d]=cint(qty_dict[d])+1
rate_dict[d]=cint(rate_dict[d])+cint(sss['rate'])
srl_dict[d]=srl_dict[d]+a[:-1]+"\n"
if invoice_type=='CUSTOMER':
#a="select name from `tabCustomer Details`where name like '"+customer+"-%'"
#b=webnotes.conn.sql(a)
#webnotes.errprint(a)
#if b:
#a=''
#else:
#webnotes.errprint("else "+a)
l = Document('Customer Details',customer)
l.customer_name=customer
l.save()
webnotes.conn.commit()
for ll in item_details:
m = Document('Customer Data')
#.serial_no=ll['barcode']
#webnotes.errprint(a[:-1])
m.serial_no=a[:-1]
#m.start_date=nowdate()
m.parent=l.name
m.parenttype='Customer Details'
m.parentfield='customer_data'
m.save(new=1)
#webnotes.errprint("before g1")
g1="update `tabSerial No` set warehouse='',delivery_date=CURDATE(),status='Delivered',customer='"+customer+"' where name='"+cstr(ll['barcode']).replace(',','')+"'"
webnotes.conn.sql(g1)
webnotes.conn.commit()
else:
for gg in item_details:
g=gg['barcode']
#webnotes.errprint("in else gg")
#webnotes.errprint(g[:-2])
g1="update `tabSerial No` set warehouse='',delivery_date=CURDATE(),status='Delivered',customer='"+customer+"' where name='"+g[:-1]+"'"
webnotes.conn.sql(g1)
webnotes.conn.commit()
for hh in item_details:
h=hh['barcode']
g=h[:-2]
i="select item_code from `tabSerial No` where name='"+g+"'"
ii=webnotes.conn.sql(i)
qrt="select name from `tabauth keys` where auth_key='"+auth_key+"'"
res=webnotes.conn.sql(qrt)
net=0
if res:
a="select name from tabCustomer where name='"+customer+"'"
#webnotes.errprint(a)
b=webnotes.conn.sql(a)
#webnotes.errprint(b)
if b:
a=''
else:
#webnotes.errprint("else")
d = Document('Customer')
d.customer_name=customer
d.name=customer
d.save(new=1)
webnotes.conn.commit()
d = Document('Sales Invoice')
d.customer=customer
d.customer_name=customer
d.posting_date=dattime[1:-1]
d.due_date=dattime[1:-1]
d.selling_price_list='Standard Selling'
d.currency='INR'
d.territory=r1[0][0]
d.net_total_export=0
d.grand_total_export=0
d.rounded_total_export=0
d.plc_conversion_rate=1
d.region=r1[0][0]
from webnotes.utils import nowdate
from accounts.utils import get_fiscal_year
today = nowdate()
d.fiscal_year=get_fiscal_year(today)[0]
d.debit_to=customer[1:-1]+" - P"
d.is_pos=1
d.cash_bank_account='Cash - P'
d.save(new=1)
webnotes.conn.commit()
for x in qty_dict.keys():
e=Document('Sales Invoice Item')
e.item_code=x
e.item_name=x
e.description=x
e.qty=qty_dict[x]
e.stock_uom='Nos'
e.ref_rate=cint(rate_dict[x])/cint(qty_dict[x])
e.export_rate=cint(rate_dict[x])/cint(qty_dict[x])
e.export_amount=cint(rate_dict[x])
e.income_account='Sales - P'
e.cost_center='Main - P'
e.serial_no_=srl_dict[x]
e.parent=d.name
e.parenttype='Sales Invoice'
e.parentfield='entries'
e.save(new=1)
net+=cint(rate_dict[x])
qrs="select warranty_period from tabItem where name='"+x+"'"
#webnotes.errprint(qrs)
ress=webnotes.conn.sql(qrs)
#webnotes.errprint(ress[0][0])
if ress:
exdt=add_months(cstr(nowdate()),cint(ress[0][0]))
else:
exdt='null'
#webnotes.errprint(cstr(srl_dict[x]))
#webnotes.errprint(g)
g2="update `tabSerial No` set warehouse='', warranty_expiry_date='"+cstr(exdt)+"',delivery_date=CURDATE(),status='Delivered',customer='"+customer+"' where name='"+cstr(h[:-1])+"'"
#webnotes.errprint(g2)
#webnotes.errprint("in x in qty dict")
webnotes.conn.sql(g2)
webnotes.conn.commit()
d = Document('Sales Invoice',d.name)
d.net_total_export=net
d.net_total=net
d.grand_total_export=net
d.rounded_total_export=net
d.outstanding_amount=net
d.docstatus=1
d.save()
webnotes.conn.commit()
key={}
key['invoice_id']=d.name
login.append(key)
loginObj['status']='200'
loginObj['invoice']=login
return loginObj
else:
loginObj['status']='401'
loginObj['error']='invalid token please contact administrator'
return loginObj
def get_events(start, end, user=None, for_reminder=False):
if not user:
user = webnotes.session.user
roles = webnotes.get_roles(user)
events = webnotes.conn.sql("""select name, subject, description,
starts_on, ends_on, owner, all_day, event_type, repeat_this_event, repeat_on,
monday, tuesday, wednesday, thursday, friday, saturday, sunday
from tabEvent where ((
(date(starts_on) between date('%(start)s') and date('%(end)s'))
or (date(ends_on) between date('%(start)s') and date('%(end)s'))
or (date(starts_on) <= date('%(start)s') and date(ends_on) >= date('%(end)s'))
) or (
date(starts_on) <= date('%(start)s') and ifnull(repeat_this_event,0)=1 and
ifnull(repeat_till, "3000-01-01") > date('%(start)s')
))
%(reminder_condition)s
and (event_type='Public' or owner='%(user)s'
or exists(select * from `tabEvent User` where
`tabEvent User`.parent=tabEvent.name and person='%(user)s')
or exists(select * from `tabEvent Role` where
`tabEvent Role`.parent=tabEvent.name
and `tabEvent Role`.role in ('%(roles)s')))
order by starts_on""" % {
"start": start,
"end": end,
"reminder_condition": "and ifnull(send_reminder,0)=1" if for_reminder else "",
"user": user,
"roles": "', '".join(roles)
}, as_dict=1)
# process recurring events
start = start.split(" ")[0]
end = end.split(" ")[0]
add_events = []
remove_events = []
def add_event(e, date):
new_event = e.copy()
new_event.starts_on = date + " " + e.starts_on.split(" ")[1]
if e.ends_on:
new_event.ends_on = date + " " + e.ends_on.split(" ")[1]
add_events.append(new_event)
for e in events:
if e.repeat_this_event:
event_start, time_str = e.starts_on.split(" ")
if e.repeat_on=="Every Year":
start_year = cint(start.split("-")[0])
end_year = cint(end.split("-")[0])
event_start = "-".join(event_start.split("-")[1:])
# repeat for all years in period
for year in range(start_year, end_year+1):
date = str(year) + "-" + event_start
if date >= start and date <= end:
add_event(e, date)
remove_events.append(e)
if e.repeat_on=="Every Month":
date = start.split("-")[0] + "-" + start.split("-")[1] + "-" + event_start.split("-")[2]
# last day of month issue, start from prev month!
try:
getdate(date)
except ValueError:
date = date.split("-")
date = date[0] + "-" + str(cint(date[1]) - 1) + "-" + date[2]
start_from = date
for i in xrange(int(date_diff(end, start) / 30) + 3):
if date >= start and date <= end and date >= event_start:
add_event(e, date)
date = add_months(start_from, i+1)
remove_events.append(e)
if e.repeat_on=="Every Week":
weekday = getdate(event_start).weekday()
# monday is 0
start_weekday = getdate(start).weekday()
# start from nearest weeday after last monday
date = add_days(start, weekday - start_weekday)
for cnt in xrange(int(date_diff(end, start) / 7) + 3):
if date >= start and date <= end and date >= event_start:
add_event(e, date)
date = add_days(date, 7)
remove_events.append(e)
if e.repeat_on=="Every Day":
for cnt in xrange(date_diff(end, start) + 1):
date = add_days(start, cnt)
if date >= event_start and date <= end \
and e[weekdays[getdate(date).weekday()]]:
add_event(e, date)
remove_events.append(e)
for e in remove_events:
events.remove(e)
events = events + add_events
for e in events:
# remove weekday properties (to reduce message size)
for w in weekdays:
del e[w]
return events
