| import webnotes |
| |
| from webnotes.model.doc import Document |
| from webnotes.utils import load_json, cint, cstr |
| from webnotes import msgprint, errprint |
| |
| def make_address(): |
| from webnotes.modules.module_manager import reload_doc |
| reload_doc('tools','doctype','address') |
| |
| from webnotes.model.db_schema import updatedb |
| updatedb('Address') |
| |
| def make_address_from_customer(): |
| for c in webnotes.conn.sql("select * from tabCustomer", as_dict=1): |
| d = Document('Address') |
| d.address_line1 = c['address_line1'] |
| d.address_line2 = c['address_line2'] |
| d.city = c['city'] |
| d.country = c['country'] |
| d.pincode = c['pincode'] |
| d.state = c['state'] |
| d.fax = c['fax_1'] |
| d.email_id = c['email_id'] |
| d.phone = c['phone_1'] |
| d.customer = c['name'] |
| d.customer_name = c['customer_name'] |
| d.is_primary_address = 1 |
| d.address_type = 'Office' |
| try: |
| d.save(1) |
| except NameError, e: |
| pass |
| |
| def make_address_from_supplier(): |
| for c in webnotes.conn.sql("select * from tabSupplier", as_dict=1): |
| d = Document('Address') |
| d.address_line1 = c['address_line1'] |
| d.address_line2 = c['address_line2'] |
| d.city = c['city'] |
| d.country = c['country'] |
| d.pincode = c['pincode'] |
| d.state = c['state'] |
| d.supplier = c['name'] |
| d.supplier_name = c['supplier_name'] |
| d.is_primary_address = 1 |
| d.address_type = 'Office' |
| try: |
| d.save(1) |
| except NameError, e: |
| pass |
| |
| def make_contact_from_contacttab(): |
| webnotes.conn.sql(""" |
| update ignore tabContact set |
| is_primary_contact = if(is_primary_contact='Yes',1,0) |
| """) |
| |
| webnotes.conn.sql(""" |
| update ignore tabContact t1, tabCustomer t2 set |
| t1.name = concat(ifnull(t1.contact_name,t1.name), '-', ifnull(t1.customer_name, t2.name)) |
| where ifnull(t1.is_customer,0)=1 |
| and t1.customer = t2.name |
| """) |
| |
| webnotes.conn.sql(""" |
| update ignore tabContact t1, tabSupplier t2 set |
| t1.name = concat(ifnull(t1.contact_name,t1.name), '-', ifnull(t1.supplier_name, t2.name)) |
| where ifnull(t1.is_supplier,0)=1 |
| and t1.supplier = t2.name |
| """) |
| |
| webnotes.conn.sql(""" |
| update ignore tabContact set |
| name = concat(ifnull(contact_name,name), '-', sales_partner) |
| where ifnull(is_sales_partner,0)=1 |
| """) |
| |
| webnotes.conn.commit() |
| try: |
| webnotes.conn.sql("""alter table tabContact change contact_no phone varchar(180)""") |
| webnotes.conn.sql("""alter table tabContact change is_primary_contact is_primary_contact int(1)""") |
| except: |
| pass |
| webnotes.conn.begin() |
| |
| def delete_unwanted_fields(): |
| delete_fields = [ |
| ('Contact', 'is_sales_partner'), ('Contact', 'sales_partner_address'), ('Contact', 'partner_type'), ('Contact', 'disable_login'), ('Contact', 'contact_address'), ('Contact', 'fax'), ('Contact', 'company_name'), ('Contact', 'contact_no'), ('Contact', 'customer_group'), ('Contact', 'has_login'), ('Contact', 'Create Login'), ('Contact', 'contact_name'), ('Contact', 'company_address'), ('Contact', 'customer_address'), ('Contact', 'supplier_address'), ('Contact', 'supplier_type'), ('Contact', 'is_customer'), ('Contact', 'is_supplier'), ('Contact', 'employee_id'), ('Contact', 'is_employee'), |
| ('Customer', 'region'), ('Customer', 'pincode'), ('Customer', 'city'), ('Customer', 'country'), ('Customer', 'state'), ('Customer', 'address'), ('Customer', 'telephone'), ('Customer', 'address_line2'), ('Customer', 'address_line1'), ('Customer', 'last_sales_order'), ('Customer', 'Shipping HTML'), ('Customer', 'phone_1'), ('Customer', 'Territory Help'), ('Customer', 'CG Help'), ('Customer', 'fax_1'), ('Customer', 'email_id'), |
| ('Customer Issue', 'email_id'), ('Customer Issue', 'contact_no'), |
| ('Delivery Note', 'customer_mobile_no'), ('Delivery Note', 'Send SMS'), ('Delivery Note', 'Get Other Charges'), ('Delivery Note', 'message'), ('Delivery Note', 'shipping_address'), ('Delivery Note', 'ship_to'), ('Delivery Note', 'ship_det_no'), ('Delivery Note', 'contact_no'), ('Delivery Note', 'Customer Details'), ('Delivery Note', 'email_id'), ('Delivery Note', 'delivery_address'), ('Delivery Note', 'Contact Help'), ('Delivery Note', 'Territory Help'), |
| ('Enquiry', 'address'), ('Enquiry', 'Send Email'), ('Enquiry', 'enquiry_attachment_detail'), ('Enquiry', 'contact_date_ref'), ('Enquiry', 'Update Follow up'), ('Enquiry', 'email_id1'), ('Enquiry', 'cc_to'), ('Enquiry', 'subject'), ('Enquiry', 'message'), ('Enquiry', 'Attachment Html'), ('Enquiry', 'Create New File'), ('Enquiry', 'contact_no'), ('Enquiry', 'email_id'), ('Enquiry', 'project'), ('Enquiry', 'update_follow_up'), ('Enquiry', 'Contact Help'), |
| ('Installation Note', 'address'), |
| ('Lead', 'message'), ('Lead', 'Send Email'), ('Lead', 'address'), ('Lead', 'subject'), ('Lead', 'contact_no'), ('Lead', 'TerritoryHelp'), |
| ('Maintenance Schedule', 'address'), |
| ('Maintenance Visit', 'address'), |
| ('Purchase Order', 'Contact Help'), ('Purchase Order', 'supplier_qtn'), ('Purchase Order', 'contact_no'), ('Purchase Order', 'email'), |
| ('Purchase Receipt', 'Contact Help'), |
| ('Quotation', 'email_id'), ('Quotation', 'contact_no'), ('Quotation', 'Update Follow up'), ('Quotation', 'contact_date_ref'), ('Quotation', 'Territory Help'), ('Quotation', 'Contact Help'), |
| ('Receivable Voucher', 'Territory Help'), |
| ('Sales Order', 'contact_no'), ('Sales Order', 'email_id'), ('Sales Order', 'Contact Help'), ('Sales Order', 'file_list'), ('Sales Order', 'ship_det_no'), ('Sales Order', 'mobile_no'), ('Sales Order', 'Territory Help'), ('Sales Order', 'ship_to'), ('Sales Order', 'Customer Details'), |
| ('Sales Partner', 'area_code'), ('Sales Partner', 'telephone'), ('Sales Partner', 'email'), ('Sales Partner', 'address'), ('Sales Partner', 'TerritoryHelp'), ('Sales Partner', 'pincode'), ('Sales Partner', 'country'), ('Sales Partner', 'city'), ('Sales Partner', 'address_line2'), ('Sales Partner', 'address_line1'), ('Sales Partner', 'mobile'), ('Sales Partner', 'state'), |
| ('Serial No', 'supplier_address'), |
| ('Supplier', 'city'), ('Supplier', 'country'), ('Supplier', 'state'), ('Supplier', 'address_line1'), ('Supplier', 'last_purchase_order'), ('Supplier', 'address'), ('Supplier', 'address_line2'), ('Supplier', 'pincode'), ('Supplier rating', 'address'), ('Supplier rating', 'select'), ('Supplier rating', 'supplier')] |
| for d in delete_fields: |
| webnotes.conn.sql("delete from tabDocField where parent=%s and if(ifnull(fieldname,'')='',ifnull(label,''),fieldname)=%s", (d[0], d[1])) |
| |
| #def gen_txt_files(): |
| # from webnotes.modules.export_module import export_to_files |
| # for dt in ['Contact','Customer','Customer Issue','Delivery Note','Enquiry','Installation Note','Lead','Maintenance Schedule','Maintenance Visit','Purchase Order','Purchase Receipt','Quotation','Receivable Voucher','Sales Order','Sales Partner','Serial No','Supplier']: |
| # export_to_files(record_list=[['DocType',dt]]) |
| |
| def reload_doc_files(): |
| from webnotes.modules.module_manager import reload_doc |
| reload_doc('tools', 'doctype', 'contact') |
| reload_doc('crm', 'doctype', 'customer') |
| reload_doc('maintenance', 'doctype', 'customer_issue') |
| reload_doc('material_management', 'doctype', 'delivery_note') |
| reload_doc('crm', 'doctype', 'enquiry') |
| reload_doc('crm', 'doctype', 'installation_note') |
| reload_doc('crm', 'doctype', 'lead') |
| reload_doc('maintenance', 'doctype', 'maintenance_schedule') |
| reload_doc('maintenance', 'doctype', 'maintenance_visit') |
| reload_doc('srm', 'doctype', 'purchase_order') |
| reload_doc('material_management', 'doctype', 'purchase_receipt') |
| reload_doc('crm', 'doctype', 'quotation') |
| reload_doc('accounts', 'doctype', 'receivable_voucher') |
| reload_doc('accounts', 'doctype', 'payable_voucher') |
| reload_doc('crm', 'doctype', 'sales_order') |
| reload_doc('setup', 'doctype', 'sales_partner') |
| reload_doc('material_management', 'doctype', 'serial_no') |
| reload_doc('srm', 'doctype', 'supplier') |
| |
| def reload_mapper_files(): |
| from webnotes.modules.module_manager import reload_doc |
| reload_doc('Mapper', 'DocType Mapper', 'Customer Issue-Maintenance Visit') |
| reload_doc('Mapper', 'DocType Mapper', 'Delivery Note-Installation Note') |
| reload_doc('Mapper', 'DocType Mapper', 'Delivery Note-Receivable Voucher') |
| reload_doc('Mapper', 'DocType Mapper', 'Enquiry-Quotation') |
| reload_doc('Mapper', 'DocType Mapper', 'Lead-Customer') |
| reload_doc('Mapper', 'DocType Mapper', 'Lead-Enquiry') |
| reload_doc('Mapper', 'DocType Mapper', 'Purchase Order-Payable Voucher') |
| reload_doc('Mapper', 'DocType Mapper', 'Purchase Order-Purchase Receipt') |
| reload_doc('Mapper', 'DocType Mapper', 'Purchase Receipt-Payable Voucher') |
| reload_doc('Mapper', 'DocType Mapper', 'Quotation-Sales Order') |
| reload_doc('Mapper', 'DocType Mapper', 'Receivable Voucher-Delivery Note') |
| reload_doc('Mapper', 'DocType Mapper', 'Sales Order-Delivery Note') |
| reload_doc('Mapper', 'DocType Mapper', 'Sales Order-Maintenance Schedule') |
| reload_doc('Mapper', 'DocType Mapper', 'Sales Order-Maintenance Visit') |
| reload_doc('Mapper', 'DocType Mapper', 'Sales Order-Receivable Voucher') |
| |
| def delete_unwanted_mapper_fields(): |
| delete_fields = [ |
| ('Customer Issue-Maintenance Visit', 'customer_address', 'address'), |
| ('Delivery Note-Installation Note', 'customer_address', 'address'), |
| ('Enquiry-Quotation', 'contact_no', 'contact_no'), ('Enquiry-Quotation', 'subject', 'enq_det'), ('Enquiry-Quotation', 'customer_name', 'customer_name'), ('Enquiry-Quotation', 'customer_name', 'customer_name'), ('Enquiry-Quotation', 'address', 'customer_address'), ('Enquiry-Quotation', 'email_id', 'email_id'), |
| ('Quotation-Sales Order', 'contact_no', 'contact_no'), ('Quotation-Sales Order', 'email_id', 'email_id'), ('Quotation-Sales Order', 'customer_mobile_no', 'customer_mobile_no'), |
| ('Sales Order-Delivery Note', 'customer_address', 'delivery_address'), ('Sales Order-Delivery Note', 'customer_address', 'customer_address'), ('Sales Order-Delivery Note', 'contact_no', 'contact_no'), ('Sales Order-Delivery Note', 'email_id', 'email_id'), ('Sales Order-Delivery Note', 'ship_det_no', 'ship_det_no'), ('Sales Order-Delivery Note', 'ship_to', 'ship_to'), ('Sales Order-Delivery Note', 'shipping_address', 'shipping_address'), ('Sales Order-Delivery Note', 'customer_mobile_no', 'customer_mobile_no'), |
| ('Sales Order-Maintenance Schedule', 'customer_address', 'address'), |
| ('Sales Order-Maintenance Visit', 'customer_address', 'address'), |
| ('Sales Order-Receivable Voucher', 'contact_no', 'contact_no')] |
| |
| for rec in delete_fields: |
| webnotes.conn.sql("delete from `tabField Mapper Detail` where parent=%s and from_field=%s and to_field=%s",(rec[0], rec[1], rec[2])) |
| |
| def sync_docfield_properties(): |
| update_fields = [ |
| ('Contact', 'customer', 'Customer', 0L, None, 0L, None), ('Contact', 'supplier', 'Supplier', 0L, None, None, None), ('Contact', 'is_primary_contact', None, 0L, None, None, None), ('Contact', 'email_id', None, 0L, 1L, None, None), ('Contact', 'department', 'Suggest', 0L, None, None, None), ('Contact', 'designation', 'Suggest', 0L, None, None, None), |
| ('Customer Issue', 'customer', 'Customer', 0L, 1L, 1L, None), ('Customer Issue', 'customer_address', 'Address', 0L, None, 1L, None), ('Customer Issue', 'contact_person', 'Contact', 0L, None, 1L, None), ('Customer Issue', 'customer_name', None, 1L, None, None, None), ('Customer Issue', 'company', 'Company', 0L, 1L, 1L, None), ('Customer Issue', 'fiscal_year', 'link:Fiscal Year', 0L, 1L, 1L, None), |
| ('Delivery Note', 'customer_address', 'Address', 0L, None, 1L, None), ('Delivery Note', 'contact_person', 'Contact', 0L, None, 1L, None), ('Delivery Note', 'customer_name', None, 1L, None, None, None), ('Delivery Note', 'status', '\nDraft\nSubmitted\nCancelled', 1L, 1L, 1L, None), ('Delivery Note', 'territory', 'Territory', 0L, 1L, 1L, 0L), ('Delivery Note', 'customer_group', 'Customer Group', 0L, None, 1L, None), ('Delivery Note', 'transporter_name', None, 0L, 0L, 1L, None), ('Delivery Note', 'lr_no', None, 0L, 0L, 1L, None), ('Delivery Note', 'lr_date', None, 0L, None, 1L, None), ('Delivery Note', 'currency', 'link:Currency', 0L, 1L, 1L, None), ('Delivery Note', 'letter_head', 'link:Letter Head', 0L, None, 1L, None), |
| ('Enquiry', 'contact_person', 'Contact', 0L, None, 1L, None), ('Enquiry', 'customer_name', None, 1L, None, 0L, None), ('Enquiry', 'lead', 'Lead', 0L, None, 1L, 0L), ('Enquiry', 'enquiry_type', '\nSales\nMaintenance', 0L, 1L, None, None), ('Enquiry', 'territory', 'Territory', 0L, 1L, 1L, None), ('Enquiry', 'customer_group', 'Customer Group', 0L, 0L, 1L, 0L), ('Enquiry', 'contact_by', 'Profile', 0L, None, None, None), |
| ('Installation Note', 'contact_person', 'Contact', 0L, None, 1L, None), ('Installation Note', 'customer_name', None, 1L, 0L, None, None), ('Installation Note', 'territory', 'Territory', 0L, 1L, 1L, None), ('Installation Note', 'status', 'Draft\nSubmitted\nCancelled', 1L, 1L, 1L, None), |
| ('Lead', 'city', None, 0L, 1L, 1L, None), ('Lead', 'country', 'link:Country', 0L, 1L, 1L, None), ('Lead', 'state', 'Suggest', 0L, None, 1L, None), ('Lead', 'company', 'Company', 0L, 1L, None, None), ('Lead', 'contact_by', 'Profile', 0L, 0L, 0L, 0L), |
| ('Maintenance Schedule', 'customer', 'Customer', 0L, 1L, 1L, None), ('Maintenance Schedule', 'contact_person', 'Contact', 0L, None, 1L, None), ('Maintenance Schedule', 'status', '\nDraft\nSubmitted\nCancelled', 1L, 1L, None, None), ('Maintenance Schedule', 'territory', 'Territory', 0L, 1L, None, None), |
| ('Maintenance Visit', 'customer', 'Customer', 0L, 1L, 1L, None), ('Maintenance Visit', 'contact_person', 'Contact', 0L, None, 1L, None), ('Maintenance Visit', 'customer_name', None, 1L, None, None, None), ('Maintenance Visit', 'company', 'link:Company', 0L, 1L, 1L, None), ('Maintenance Visit', 'fiscal_year', 'link:Fiscal Year', 0L, 1L, 1L, None), ('Maintenance Visit', 'status', '\nDraft\nCancelled\nSubmitted', 1L, 1L, None, None), ('Maintenance Visit', 'territory', 'Territory', 0L, None, 1L, None), |
| ('Purchase Order', 'supplier_address', 'Address', 0L, None, 1L, None), ('Purchase Order', 'contact_person', 'Contact', 0L, None, 1L, None), ('Purchase Order', 'supplier_name', None, 1L, None, None, None), ('Purchase Order', 'status', '\nDraft\nSubmitted\nStopped\nCancelled', 1L, 1L, 1L, None), ('Purchase Order', 'indent_no', 'Indent', 0L, None, 1L, 0L), ('Purchase Order', 'is_subcontracted', '\nYes\nNo', 0L, None, 1L, None), ('Purchase Order', 'currency', 'link:Currency', 0L, 1L, 1L, None), ('Purchase Order', 'net_total', None, 1L, 0L, 1L, None), |
| ('Purchase Receipt', 'supplier_address', 'Address', 0L, None, 1L, None), ('Purchase Receipt', 'contact_person', 'Contact', 0L, None, 1L, None), ('Purchase Receipt', 'supplier_name', None, 1L, None, None, None), ('Purchase Receipt', 'status', '\nDraft\nSubmitted\nCancelled', 1L, 1L, 1L, None), ('Purchase Receipt', 'currency', 'link:Currency', 0L, 1L, 1L, None), |
| ('Quotation', 'customer', 'Customer', 0L, None, 1L, 0L), ('Quotation', 'customer_address', 'Address', 0L, None, 1L, 0L), ('Quotation', 'contact_person', 'Contact', 0L, 0L, 1L, 0L), ('Quotation', 'customer_name', None, 1L, None, None, None), ('Quotation', 'lead', 'Lead', 0L, None, 1L, 0L), ('Quotation', 'lead_name', None, 1L, None, None, None), ('Quotation', 'order_type', '\nSales\nMaintenance', 0L, 1L, 0L, None), ('Quotation', 'status', '\nDraft\nSubmitted\nOrder Confirmed\nOrder Lost\nCancelled', 1L, 1L, 1L, None), ('Quotation', 'territory', 'Territory', 0L, 1L, 1L, 0L), ('Quotation', 'currency', 'link:Currency', 0L, 1L, 1L, None), ('Quotation', 'letter_head', 'link:Letter Head', 0L, None, 1L, None), ('Quotation', 'order_lost_reason', None, 1L, None, 1L, None), ('Quotation', 'contact_by', 'Profile', 0L, None, 1L, None), ('Quotation', 'contact_date', None, 0L, None, 1L, None), ('Quotation', 'to_discuss', None, 0L, None, 1L, None), |
| ('Receivable Voucher', 'debit_to', 'Account', 0L, 1L, 1L, None), ('Receivable Voucher', 'customer_address', 'Address', 0L, None, 1L, None), ('Receivable Voucher', 'territory', 'Territory', 0L, 1L, 1L, None), ('Receivable Voucher', 'paid_amount', None, 0L, None, 1L, None), ('Receivable Voucher', 'company', 'Company', 0L, 1L, 1L, None), ('Receivable Voucher', 'fiscal_year', 'link:Fiscal Year', 0L, 1L, 1L, None), ('Receivable Voucher', 'outstanding_amount', None, 1L, None, 1L, None), |
| ('Payable Voucher', 'supplier_address', 'Address', 0L, None, 1L, None), ('Payable Voucher', 'contact_display', None, 1L, None, None, None), ('Payable Voucher', 'contact_mobile', None, 1L, None, None, None), ('Payable Voucher', 'contact_email', None, 1L, None, 1L, None), ('Payable Voucher', 'currency', 'link:Currency', 0L, 1L, 1L, None), ('Payable Voucher', 'conversion_rate', None, 0L, 1L, 1L, None), ('Payable Voucher', 'company', 'Company', 0L, 1L, 1L, None), ('Payable Voucher', 'fiscal_year', 'link:Fiscal Year', 0L, 1L, 1L, None), |
| ('Sales Order', 'customer_address', 'Address', 0L, None, 1L, 0L), ('Sales Order', 'contact_person', 'Contact', 0L, None, 1L, None), ('Sales Order', 'customer_name', None, 1L, None, None, None), ('Sales Order', 'status', '\nDraft\nSubmitted\nStopped\nCancelled', 1L, 1L, 1L, None), ('Sales Order', 'quotation_date', None, 1L, 0L, 1L, 1L), ('Sales Order', 'currency', 'link:Currency', 0L, 1L, 1L, None), ('Sales Order', 'letter_head', 'link:Letter Head', 0L, None, 1L, None), |
| ('Sales Partner', 'territory', 'Territory', 0L, 1L, None, None), |
| ('Supplier', 'company', 'Company', 0L, 1L, None, None)] |
| |
| for rec in update_fields: |
| webnotes.conn.sql("UPDATE `tabDocField` SET options=%s, permlevel=%s, reqd=%s, print_hide=%s, hidden=%s where parent=%s and fieldname=%s",(rec[2], rec[3], rec[4], rec[5], rec[6], rec[0], rec[1])) |
| |
| def run_patch(): |
| make_address() |
| make_address_from_customer() |
| make_address_from_supplier() |
| make_contact_from_contacttab() |
| delete_unwanted_fields() |
| reload_doc_files() |
| reload_mapper_files() |
| delete_unwanted_mapper_fields() |
| sync_docfield_properties() |
| |
| #Old Customer Data Sync Patch for "Quotation, SO, PO, RV, PV, DN, PR, Installation Note, Maintenance Schedule, Customer Issue, Maintenance Visit" |
| #-------------------------------------------------------------- |
| |
| def run_old_data_sync_patch(): |
| sync_quotation_customer_data() |
| sync_sales_order_customer_data() |
| sync_purchase_order_supplier_data() |
| sync_receivable_voucher_customer_data() |
| sync_payable_voucher_supplier_data() |
| sync_delivery_note_customer_data() |
| sync_purchase_receipt_supplier_data() |
| sync_installation_note_customer_data() |
| sync_maintenance_schedule_customer_data() |
| sync_customer_issue_customer_data() |
| sync_maintenance_visit_customer_data() |
| sync_lead_phone() |
| |
| #Quotation |
| def sync_quotation_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT tq.name as id,tq.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM tabQuotation tq, tabAddress ta |
| WHERE tq.customer = ta.customer |
| AND tq.quotation_to = 'Customer' |
| AND tq.docstatus !=2 |
| ORDER BY tq.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE tabQuotation SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT tq.name as id,tq.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM tabQuotation tq, tabContact tc |
| WHERE tq.customer = tc.customer |
| AND tq.quotation_to = 'Customer' |
| AND tq.docstatus !=2 |
| ORDER BY tq.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE tabQuotation SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| |
| #Sales Order |
| def sync_sales_order_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabSales Order` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Phone: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabSales Order` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabSales Order` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabSales Order` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Purchase Order |
| def sync_purchase_order_supplier_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.supplier, |
| ta.name as supplier_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabPurchase Order` t, tabAddress ta |
| WHERE t.supplier = ta.supplier |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabPurchase Order` SET |
| supplier_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['supplier_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.supplier, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabPurchase Order` t, tabContact tc |
| WHERE t.supplier = tc.supplier |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabPurchase Order` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Sales Invoice |
| def sync_receivable_voucher_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabReceivable Voucher` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabReceivable Voucher` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabReceivable Voucher` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabReceivable Voucher` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Purchase Invoice |
| def sync_payable_voucher_supplier_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.supplier, |
| ta.name as supplier_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabPayable Voucher` t, tabAddress ta |
| WHERE t.supplier = ta.supplier |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabPayable Voucher` SET |
| supplier_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['supplier_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.supplier, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabPayable Voucher` t, tabContact tc |
| WHERE t.supplier = tc.supplier |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabPayable Voucher` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Delivery Note |
| def sync_delivery_note_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabDelivery Note` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabDelivery Note` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabDelivery Note` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabDelivery Note` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Purchase Receipt |
| def sync_purchase_receipt_supplier_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.supplier, |
| ta.name as supplier_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabPurchase Receipt` t, tabAddress ta |
| WHERE t.supplier = ta.supplier |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabPurchase Receipt` SET |
| supplier_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['supplier_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.supplier, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabPurchase Receipt` t, tabContact tc |
| WHERE t.supplier = tc.supplier |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabPurchase Receipt` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Installation Note |
| def sync_installation_note_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabInstallation Note` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabInstallation Note` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabInstallation Note` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabInstallation Note` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Maintenance Schedule |
| def sync_maintenance_schedule_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabMaintenance Schedule` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabMaintenance Schedule` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabMaintenance Schedule` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabMaintenance Schedule` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Customer Issue |
| def sync_customer_issue_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabCustomer Issue` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabCustomer Issue` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabCustomer Issue` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabCustomer Issue` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #Maintenance Visit |
| def sync_maintenance_visit_customer_data(): |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| ta.name as customer_address, ta.address_line1, ta.address_line2, ta.city, ta.country, ta.pincode, ta.state, ta.phone |
| FROM `tabMaintenance Visit` t, tabAddress ta |
| WHERE t.customer = ta.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| address_display = cstr((rec['address_line1'] and rec['address_line1'] or '')) + cstr((rec['address_line2'] and '\n' + rec['address_line2'] or '')) + cstr((rec['city'] and '\n'+rec['city'] or '')) + cstr((rec['pincode'] and ', ' + rec['pincode'] or '')) + cstr((rec['state'] and '\n'+rec['state']+', ' or '')) + cstr((rec['country'] and rec['country'] or '')) + '\n' + cstr((rec['phone'] and 'Tel: '+rec['phone'] or '')) |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabMaintenance Visit` SET |
| customer_address = %s, |
| address_display = %s |
| WHERE name = %s |
| """,(rec['customer_address'],address_display,rec['id'])) |
| |
| data_rec = webnotes.conn.sql(""" |
| SELECT t.name as id,t.customer, |
| tc.name as contact_person, tc.first_name, tc.last_name, tc.email_id, tc.phone as contact_phone, tc.mobile_no, tc.department, tc.designation |
| FROM `tabMaintenance Visit` t, tabContact tc |
| WHERE t.customer = tc.customer |
| AND t.docstatus !=2 |
| ORDER BY t.name |
| """, as_dict=1) |
| |
| for rec in data_rec: |
| contact_display = (rec['first_name'] and rec['first_name'] or '') + (rec['last_name'] and ' ' + rec['last_name'] or '') |
| |
| webnotes.conn.sql(""" |
| UPDATE `tabMaintenance Visit` SET |
| contact_person = %s, |
| contact_mobile = %s, |
| contact_email = %s, |
| contact_display = %s |
| WHERE name = %s |
| """,(rec['contact_person'],rec['mobile_no'],rec['email_id'],contact_display,rec['id'])) |
| |
| #lead phone data sync |
| def sync_lead_phone(): |
| webnotes.conn.sql(""" |
| update ignore tabLead set |
| phone = contact_no |
| where contact_no is not null |
| """) |