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