from __future__ import unicode_literals
import frappe
from frappe import auth
import datetime
import json, ast
import urllib.request
import requests

current_date = datetime.datetime.today().strftime('%Y-%m-%d')
current_user = frappe.session.user

@frappe.whitelist(allow_guest=True)
def login1(usr, pwd):
    try:
        login_manager = frappe.auth.LoginManager()
        login_manager.authenticate(user=usr, pwd=pwd)
        login_manager.post_login()

    except frappe.exceptions.AuthenticationError:
        frappe.clear_messages()
        frappe.local.response["message"] = {
            "success_key": true,
            "message": "اسم المستخدم او كلمة المرور غير صحيحة !"
        }

        return

    api_generate = generate_keys(frappe.session.user)
    user = frappe.get_doc('User', frappe.session.user)
    allowed_modules = frappe.db.sql(""" select `tabMobile user Modules`.modules as modq 
    from `tabMobile user Modules` join `tabMobile User` on `tabMobile user Modules`.parent = `tabMobile User`.name 
    where `tabMobile User`.user = '{user}' or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile user Modules`.idx """.format(user=usr),as_dict=1)
    allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.modules as modq ,
                                        `tabMobile User Documents`.document_name as docq 
                                        from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                        where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile User Documents`.idx  """.format(user=usr),as_dict=1)
    modules={}
    documents={}
    for module in allowed_modules:
        #modules = {module.modq:"Yes"}
        #modules.update()
        #modules.[module.modq]= 34
        modules.update( {module.modq : "Yes"} )


    for document in allowed_documents:
        documents.update({document.docq:document.modq})


    frappe.response["message"] = {
        "success_key": True,
        "message": "Authentication Success",
        "sid": frappe.session.sid,
        "api_key": user.api_key,
        "api_secret": api_generate,
        "email": user.email,
        "modules": modules,
        "documents": documents,
        "user_type": user.role_profile_name
    }

    return

@frappe.whitelist(allow_guest=True)
def login(usr, pwd, url):

    ## Check for active domain
    link = "https://erpcloud.systems/api/method/ecs_ecs.api.check_domain?url=" + url
    f = requests.get(link)
    y = json.loads(f.text)

    if y['message'] == "Domain Is Inactive":
        frappe.local.response["message"] = {
            "message": "This Domain Is Not Allowed. Please Contact <EMAIL>",
        }
        frappe.throw("This Domain Is Not Allowed. Please Contact <EMAIL>")
        #return "This Domain Is Not Allowed. Please Contact <EMAIL>"


    elif y['message'] == "Domain Is Active":
        try:
            login_manager = frappe.auth.LoginManager()
            login_manager.authenticate(user=usr, pwd=<PASSWORD>)
            login_manager.post_login()

        except frappe.exceptions.AuthenticationError:
            frappe.clear_messages()
            frappe.local.response["message"] = {
                "success_key": true,
                "message": "اسم المستخدم او كلمة المرور غير صحيحة !"
            }

        api_generate = generate_keys(frappe.session.user)
        user = frappe.get_doc('User', frappe.session.user)

        allowed_modules = frappe.db.sql(""" select `tabMobile user Modules`.modules as modq 
        from `tabMobile user Modules` join `tabMobile User` on `tabMobile user Modules`.parent = `tabMobile User`.name 
        where `tabMobile User`.user = '{user}' or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile user Modules`.idx """.format(user=usr),as_dict=1)

        modules = []

        for module in allowed_modules:
            if module.modq == "Selling":
                Selling={}

                Selling["Selling"] = "https://erpcloud.systems/icons/selling.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='selling' order by `tabMobile User Documents`.idx  """.format(user=usr),as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Lead":
                        docs["Lead"] = "https://erpcloud.systems/icons/lead.png"
                    if x.docq == "Opportunity":
                        docs["Opportunity"] = "https://erpcloud.systems/icons/opportunity.png"
                    if x.docq == "Quotation":
                        docs["Quotation"] = "https://erpcloud.systems/icons/quotation.png"
                    if x.docq == "Sales Order":
                        docs["Sales Order"] = "https://erpcloud.systems/icons/sales_order.png"
                    if x.docq == "Customer":
                        docs["Customer"] = "https://erpcloud.systems/icons/customer.png"
                    if x.docq == "Sales Invoice":
                        docs["Sales Invoice"] = "https://erpcloud.systems/icons/sales_invoice.png"
                    if x.docq == "Payment Entry":
                        docs["Payment Entry"] = "https://erpcloud.systems/icons/payment_entry.png"

                Selling["docs"] = (docs)
                modules.append(Selling)

            if module.modq == "Buying":
                Buying = {}
                Buying["Buying"] = "https://erpcloud.systems/icons/buying.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='buying' order by `tabMobile User Documents`.idx  """.format(
                    user=usr), as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Supplier":
                        docs["Supplier"] = "https://erpcloud.systems/icons/supplier.png"
                    if x.docq == "Supplier Quotation":
                        docs["Supplier Quotation"] = "https://erpcloud.systems/icons/supplier_quotation.png"
                    if x.docq == "Purchase Order":
                        docs["Purchase Order"] = "https://erpcloud.systems/icons/purchase_order.png"
                    if x.docq == "Purchase Invoice":
                        docs["Purchase Invoice"] = "https://erpcloud.systems/icons/purchase_invoice.png"

                Buying["docs"] = (docs)
                modules.append(Buying)

            if module.modq == "Stock":
                Stock = {}
                Stock["Stock"] = "https://erpcloud.systems/icons/stock.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='stock' order by `tabMobile User Documents`.idx  """.format(
                    user=usr), as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Item":
                        docs["Item"] = "https://erpcloud.systems/icons/item.png"
                    if x.docq == "Material Request":
                        docs["Material Request"] = "https://erpcloud.systems/icons/material_request.png"
                    if x.docq == "Purchase Order":
                        docs["Purchase Receipt"] = "https://erpcloud.systems/icons/purchase_receipt.png"
                    if x.docq == "Stock Entry":
                        docs["Stock Entry"] = "https://erpcloud.systems/icons/stock_entry.png"
                    if x.docq == "Delivery Note":
                        docs["Delivery Note"] = "https://erpcloud.systems/icons/delivery_note.png"

                Stock["docs"] = (docs)
                modules.append(Stock)

            if module.modq == "HR":
                HR = {}
                HR["HR"] = "https://erpcloud.systems/icons/hr.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='hr' order by `tabMobile User Documents`.idx  """.format(
                    user=usr), as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Employee":
                        docs["Employee"] = "https://erpcloud.systems/icons/hr.png"

                HR["docs"] = (docs)
                modules.append(HR)

        taxes = frappe.db.get_list('Sales Taxes and Charges', filters={'parent': "Default Tax Template", 'parenttype':'Sales Taxes and Charges Template'},
                                        fields=[
                                            'charge_type',
                                            'description',
                                            'account_head',
                                        ])
        company = frappe.db.get_list('Company',
                                      fields=[
                                            'name',
                                            'default_currency',
                                        ])

        frappe.response["message"] = {
            "success_key": True,
            "domain_status": y['message'],
            "message": "Authentication Success",
            "sid": frappe.session.sid,
            "api_key": user.api_key,
            "api_secret": api_generate,
            "email": user.email,
            "modules": modules,
            "user_type": user.role_profile_name,
            "default_tax_template": taxes,
            "company_defaults": company
        }

        return

def generate_keys(user):
    user_details = frappe.get_doc('User', user)
    api_secret = frappe.generate_hash(length=15)

    if not user_details.api_key:
        api_key = frappe.generate_hash(length=15)
        user_details.api_key = api_key

    user_details.api_secret = api_secret
    user_details.save()
    return api_secret

# ------------------------------------------------------------------------------------
@frappe.whitelist(allow_guest=True)
def search(doctype, search_text='%%'):
    if doctype == "Lead":
        query = frappe.db.get_list('Lead',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'lead_name': ['like', search_text]},
                                               {'company_name': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]}],
                                   fields=["name", "lead_name", "company_name", "territory", "source", "market_segment", "status"]
                                   )
        return query

    if doctype == "Opportunity":
        query = frappe.db.get_list('Opportunity',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'party_name': ['like', search_text]}],
                                   fields=["name", "opportunity_from", "customer_name", "transaction_date", "opportunity_type", "sales_stage", "status"]
                                   )
        return query

    if doctype == "Customer":
        query = frappe.db.get_list('Customer',
                                   filters=[{'disabled': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]}],
                                   fields=['name', 'customer_name', 'customer_group', 'territory', 'mobile_no']
                                   )
        return query

    if doctype == "Quotation":
        query = frappe.db.get_list('Quotation',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'party_name': ['like', search_text]}],
                                   fields=["name", "quotation_to", "customer_name", "transaction_date", "grand_total", "status"]
                                   )
        return query

    if doctype == "Sales Order":
        query = frappe.db.get_list('Sales Order',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'customer': ['like', search_text]}],
                                   fields=["name", "customer_name", "customer_address", "transaction_date", "grand_total", "status"]
                                   )
        return query

    if doctype == "Sales Invoice":
        query = frappe.db.get_list('Sales Invoice',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'customer': ['like', search_text]}],
                                   fields=["name", "customer_name", "customer_address", "posting_date", "grand_total", "status"]
                                   )
        return query

    if doctype == "Payment Entry":
        query = frappe.db.get_list('Payment Entry',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'party_name': ['like', search_text]},
                                               {'mode_of_payment': ['like', search_text]},
                                               {'party': ['like', search_text]}],
                                   fields=["name", "party_name", "payment_type", "mode_of_payment", "posting_date", "paid_amount", "status"]
                                   )
        return query


    if doctype == "Lead Source":
        query = frappe.db.get_list('Lead Source',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'source_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Market Segment":
        query = frappe.db.get_list('Market Segment',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'market_segment': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Industry Type":
        query = frappe.db.get_list('Industry Type',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'industry': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Territory":
        query = frappe.db.get_list('Territory',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'territory_name': ['like', search_text]}],
                                   fields=["name", "parent_territory"]
                                   )
        return query

    if doctype == "Warehouse":
        query = frappe.db.get_list('Warehouse',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'warehouse_name': ['like', search_text]}],
                                   fields=["warehouse_name", "warehouse_type", "parent_warehouse"]
                                   )
        return query

    if doctype == "Country":
        query = frappe.db.get_list('Country',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'country_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Opportunity Type":
        query = frappe.db.get_list('Opportunity Type',
                                   or_filters=[{'name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Customer Group":
        query = frappe.db.get_list('Customer Group',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_group_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Address":
        query = frappe.db.get_list('Address',
                                   filters=[["Dynamic Link", "link_name", "=", "كارفور"]],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'address_title': ['like', search_text]},
                                               {'address_line1': ['like', search_text]},
                                               {'city': ['like', search_text]},
                                               {'phone': ['like', search_text]}],
                                   fields=["address_title", "address_line1", "city", "phone"]
                                   )
        return query

    if doctype == "Contact":
        query = frappe.db.get_list('Contacts',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'email_id': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]},
                                               {'phone': ['like', search_text]},
                                               {'company_name': ['like', search_text]}],
                                   fields=["name", "email_id", "phone", "mobile_no", "company_name"]
                                   )
        return query

    if doctype == "Campaign":
        query = frappe.db.get_list('Campaign',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'campaign_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Currency":
        query = frappe.db.get_list('Currency',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'currency_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Sales Partner":
        query = frappe.db.get_list('Sales Partner',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'partner_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Terms and Conditions":
        query = frappe.db.get_list('Terms and Conditions',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Mode of Payment":
        query = frappe.db.get_list('Mode of Payment',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'mode_of_payment': ['like', search_text]}],
                                   fields=["name", "type"]
                                   )
        return query

    if doctype == "Price List":
        query = frappe.db.get_list('Price List',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'price_list_name': ['like', search_text]}],
                                   fields=["name", "currency"]
                                   )
        return query

    if doctype == "Payment Terms Template":
        query = frappe.db.get_list('Payment Terms Template',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'template_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Cost Center":
        query = frappe.db.get_list('Cost Center',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'cost_center_name': ['like', search_text]}],
                                   fields=["name", "cost_center_name", "parent_cost_center"]
                                   )
        return query

    if doctype == "Project":
        query = frappe.db.get_list('Project',
                                   filters=[{'is_active': ['=', "Yes"]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'project_name': ['like', search_text]}],
                                   fields=["name", "project_name", "status"]
                                   )
        return query

    if doctype == "Account":
        query = frappe.db.get_list('Account',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'account_name': ['like', search_text]},
                                               {'account_number': ['like', search_text]}],
                                   fields=["name", "account_type", "root_type", "account_currency", "parent_account"]
                                   )
        return query

    if doctype == "Item":
        query = frappe.db.get_list('Item',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'item_name': ['like', search_text]},
                                               {'item_code': ['like', search_text]}],
                                   fields=["name", "item_name", "item_group", "stock_uom"]
                                   )
        return query

    if doctype == "Material Request":
        query = frappe.db.get_list('Material Request',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]}],
                                   fields=["name", "material_request_type", "transaction_date", "set_warehouse", "status"]
                                   )
        return query

    if doctype == "Stock Entry":
        query = frappe.db.get_list('Stock Entry',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]}],
                                   fields=["name", "stock_entry_type", "posting_date", "from_warehouse", "to_warehouse", "docstatus"]
                                   )
        return query

    if doctype == "Purchase Receipt":
        query = frappe.db.get_list('Purchase Receipt',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]},
                                               {'supplier': ['like', search_text]},
                                               {'supplier_name': ['like', search_text]}],
                                   fields=["name", "supplier", "posting_date", "set_warehouse", "status"]
                                   )
        return query

    if doctype == "Delivery Note":
        query = frappe.db.get_list('Delivery Note',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]},
                                               {'customer': ['like', search_text]},
                                               {'customer_name': ['like', search_text]}],
                                   fields=["name", "customer", "territory", "posting_date", "set_warehouse", "status"]
                                   )
        return query

    if doctype == "Supplier":
        query = frappe.db.get_list('Supplier',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier_name': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]}],
                                   fields=["name", "supplier_name", "supplier_group", "supplier_type", "country", "mobile_no"]
                                   )
        return query

    if doctype == "Supplier Quotation":
        query = frappe.db.get_list('Supplier Quotation',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier': ['like', search_text]}],
                                   fields=["name", "supplier", "transaction_date", "valid_till", "grand_total", "status"]
                                   )
        return query

    if doctype == "Purchase Order":
        query = frappe.db.get_list('Purchase Order',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier': ['like', search_text]}],
                                   fields=["name", "supplier", "transaction_date", "set_warehouse", "grand_total", "status"]
                                   )
        return query

    if doctype == "Purchase Invoice":
        query = frappe.db.get_list('Purchase Invoice',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier': ['like', search_text]}],
                                   fields=["name", "supplier", "posting_date", "grand_total", "status"]
                                   )
        return query


@frappe.whitelist(allow_guest=True)
def party_search(doctype=0, search_text=0, party=0):
    if party == 0:
        return "Check Data"
    else:
        if doctype == "Address":
            addresses = frappe.db.get_list('Dynamic Link', filters={'link_name': party}, fields=['parent'])
            result = []
            for d in addresses:
                query = frappe.db.sql(""" select name as name ,
                                                address_title as address_title,
                                                 address_line1 as address_line1,
                                                 city as city,
                                                 phone as phone
                                         from tabAddress where name = '{filtered}'
                                         and address_title like '{search_text}'
                                         """.format(filtered=d.parent,search_text=search_text), as_dict=1)
                for x in query:
                    data = {
                        'name': x.name,
                        'address_title': x.address_title,
                        'address_line1': x.address_line1,
                        'city': x.city,
                        'phone': x.phone
                    }
                    result.append(data)

            if result:
                return result
            else:
                return "no result"

        if doctype == "Contact":
            contacts = frappe.db.get_list('Dynamic Link', filters={'link_name': party}, fields=['parent'])
            result = []
            for d in contacts:
                query = frappe.db.sql(""" select name as name ,
                                                email_id as email_id,
                                                 mobile_no as mobile_no,
                                                 phone as phone,
                                                 company_name as company_name
                                         from tabContact where name = '{filtered}'
                                         and name like '{search_text}'
                                         """.format(filtered=d.parent,search_text=search_text), as_dict=1)
                for x in query:
                    data = {
                        'name': x.name,
                        'email_id': x.email_id,
                        'mobile_no': x.mobile_no,
                        'company_name': x.company_name,
                        'phone': x.phone
                    }
                    result.append(data)

            if result:
                return result
            else:
                return "no result"

@frappe.whitelist(allow_guest=True)
def submit(doctype, name):
    doc = frappe.get_doc(doctype, name)
    doc.save()
    doc.submit()
    if doc.docstatus == 1:
        message = frappe.response["message"] = {
            "success_key": True,
            "message": "تم تسجيل المعاملة بنجاح!",
            "name": doc.name
        }
        return message
    else:
        return "حدث خطأ ولم نتمكن من اضافة المعاملة . برجاء المحاولة مرة اخري!"

from __future__ import unicode_literals
import frappe
from frappe import auth
import datetime
import json, ast
import urllib.request
import requests

current_date = datetime.datetime.today().strftime('%Y-%m-%d')
current_user = frappe.session.user

@frappe.whitelist(allow_guest=True)
def login1(usr, pwd):
    try:
        login_manager = frappe.auth.LoginManager()
        login_manager.authenticate(user=usr, pwd=pwd)
        login_manager.post_login()

    except frappe.exceptions.AuthenticationError:
        frappe.clear_messages()
        frappe.local.response["message"] = {
            "success_key": true,
            "message": "اسم المستخدم او كلمة المرور غير صحيحة !"
        }

        return

    api_generate = generate_keys(frappe.session.user)
    user = frappe.get_doc('User', frappe.session.user)
    allowed_modules = frappe.db.sql(""" select `tabMobile user Modules`.modules as modq 
    from `tabMobile user Modules` join `tabMobile User` on `tabMobile user Modules`.parent = `tabMobile User`.name 
    where `tabMobile User`.user = '{user}' or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile user Modules`.idx """.format(user=usr),as_dict=1)
    allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.modules as modq ,
                                        `tabMobile User Documents`.document_name as docq 
                                        from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                        where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile User Documents`.idx  """.format(user=usr),as_dict=1)
    modules={}
    documents={}
    for module in allowed_modules:
        #modules = {module.modq:"Yes"}
        #modules.update()
        #modules.[module.modq]= 34
        modules.update( {module.modq : "Yes"} )


    for document in allowed_documents:
        documents.update({document.docq:document.modq})


    frappe.response["message"] = {
        "success_key": True,
        "message": "Authentication Success",
        "sid": frappe.session.sid,
        "api_key": user.api_key,
        "api_secret": api_generate,
        "email": user.email,
        "modules": modules,
        "documents": documents,
        "user_type": user.role_profile_name
    }

    return

@frappe.whitelist(allow_guest=True)
def login(usr, pwd, url):

    ## Check for active domain
    link = "https://erpcloud.systems/api/method/ecs_ecs.api.check_domain?url=" + url
    f = requests.get(link)
    y = json.loads(f.text)

    if y['message'] == "Domain Is Inactive":
        frappe.local.response["message"] = {
            "message": "This Domain Is Not Allowed. Please Contact <EMAIL>",
        }
        frappe.throw("This Domain Is Not Allowed. Please Contact <EMAIL>")
        #return "This Domain Is Not Allowed. Please Contact <EMAIL>"


    elif y['message'] == "Domain Is Active":
        try:
            login_manager = frappe.auth.LoginManager()
            login_manager.authenticate(user=usr, pwd=<PASSWORD>)
            login_manager.post_login()

        except frappe.exceptions.AuthenticationError:
            frappe.clear_messages()
            frappe.local.response["message"] = {
                "success_key": true,
                "message": "اسم المستخدم او كلمة المرور غير صحيحة !"
            }

        api_generate = generate_keys(frappe.session.user)
        user = frappe.get_doc('User', frappe.session.user)

        allowed_modules = frappe.db.sql(""" select `tabMobile user Modules`.modules as modq 
        from `tabMobile user Modules` join `tabMobile User` on `tabMobile user Modules`.parent = `tabMobile User`.name 
        where `tabMobile User`.user = '{user}' or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile user Modules`.idx """.format(user=usr),as_dict=1)

        modules = []

        for module in allowed_modules:
            if module.modq == "Selling":
                Selling={}

                Selling["Selling"] = "https://erpcloud.systems/icons/selling.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='selling' order by `tabMobile User Documents`.idx  """.format(user=usr),as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Lead":
                        docs["Lead"] = "https://erpcloud.systems/icons/lead.png"
                    if x.docq == "Opportunity":
                        docs["Opportunity"] = "https://erpcloud.systems/icons/opportunity.png"
                    if x.docq == "Quotation":
                        docs["Quotation"] = "https://erpcloud.systems/icons/quotation.png"
                    if x.docq == "Sales Order":
                        docs["Sales Order"] = "https://erpcloud.systems/icons/sales_order.png"
                    if x.docq == "Customer":
                        docs["Customer"] = "https://erpcloud.systems/icons/customer.png"
                    if x.docq == "Sales Invoice":
                        docs["Sales Invoice"] = "https://erpcloud.systems/icons/sales_invoice.png"
                    if x.docq == "Payment Entry":
                        docs["Payment Entry"] = "https://erpcloud.systems/icons/payment_entry.png"

                Selling["docs"] = (docs)
                modules.append(Selling)

            if module.modq == "Buying":
                Buying = {}
                Buying["Buying"] = "https://erpcloud.systems/icons/buying.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='buying' order by `tabMobile User Documents`.idx  """.format(
                    user=usr), as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Supplier":
                        docs["Supplier"] = "https://erpcloud.systems/icons/supplier.png"
                    if x.docq == "Supplier Quotation":
                        docs["Supplier Quotation"] = "https://erpcloud.systems/icons/supplier_quotation.png"
                    if x.docq == "Purchase Order":
                        docs["Purchase Order"] = "https://erpcloud.systems/icons/purchase_order.png"
                    if x.docq == "Purchase Invoice":
                        docs["Purchase Invoice"] = "https://erpcloud.systems/icons/purchase_invoice.png"

                Buying["docs"] = (docs)
                modules.append(Buying)

            if module.modq == "Stock":
                Stock = {}
                Stock["Stock"] = "https://erpcloud.systems/icons/stock.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='stock' order by `tabMobile User Documents`.idx  """.format(
                    user=usr), as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Item":
                        docs["Item"] = "https://erpcloud.systems/icons/item.png"
                    if x.docq == "Material Request":
                        docs["Material Request"] = "https://erpcloud.systems/icons/material_request.png"
                    if x.docq == "Purchase Order":
                        docs["Purchase Receipt"] = "https://erpcloud.systems/icons/purchase_receipt.png"
                    if x.docq == "Stock Entry":
                        docs["Stock Entry"] = "https://erpcloud.systems/icons/stock_entry.png"
                    if x.docq == "Delivery Note":
                        docs["Delivery Note"] = "https://erpcloud.systems/icons/delivery_note.png"

                Stock["docs"] = (docs)
                modules.append(Stock)

            if module.modq == "HR":
                HR = {}
                HR["HR"] = "https://erpcloud.systems/icons/hr.png"
                allowed_documents = frappe.db.sql(""" select `tabMobile User Documents`.document_name as docq 
                                            from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name 
                                            where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='hr' order by `tabMobile User Documents`.idx  """.format(
                    user=usr), as_dict=1)
                docs = {}
                for x in allowed_documents:
                    if x.docq == "Employee":
                        docs["Employee"] = "https://erpcloud.systems/icons/hr.png"

                HR["docs"] = (docs)
                modules.append(HR)

        taxes = frappe.db.get_list('Sales Taxes and Charges', filters={'parent': "Default Tax Template", 'parenttype':'Sales Taxes and Charges Template'},
                                        fields=[
                                            'charge_type',
                                            'description',
                                            'account_head',
                                        ])
        company = frappe.db.get_list('Company',
                                      fields=[
                                            'name',
                                            'default_currency',
                                        ])

        frappe.response["message"] = {
            "success_key": True,
            "domain_status": y['message'],
            "message": "Authentication Success",
            "sid": frappe.session.sid,
            "api_key": user.api_key,
            "api_secret": api_generate,
            "email": user.email,
            "modules": modules,
            "user_type": user.role_profile_name,
            "default_tax_template": taxes,
            "company_defaults": company
        }

        return

def generate_keys(user):
    user_details = frappe.get_doc('User', user)
    api_secret = frappe.generate_hash(length=15)

    if not user_details.api_key:
        api_key = frappe.generate_hash(length=15)
        user_details.api_key = api_key

    user_details.api_secret = api_secret
    user_details.save()
    return api_secret

# ------------------------------------------------------------------------------------
@frappe.whitelist(allow_guest=True)
def search(doctype, search_text='%%'):
    if doctype == "Lead":
        query = frappe.db.get_list('Lead',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'lead_name': ['like', search_text]},
                                               {'company_name': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]}],
                                   fields=["name", "lead_name", "company_name", "territory", "source", "market_segment", "status"]
                                   )
        return query

    if doctype == "Opportunity":
        query = frappe.db.get_list('Opportunity',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'party_name': ['like', search_text]}],
                                   fields=["name", "opportunity_from", "customer_name", "transaction_date", "opportunity_type", "sales_stage", "status"]
                                   )
        return query

    if doctype == "Customer":
        query = frappe.db.get_list('Customer',
                                   filters=[{'disabled': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]}],
                                   fields=['name', 'customer_name', 'customer_group', 'territory', 'mobile_no']
                                   )
        return query

    if doctype == "Quotation":
        query = frappe.db.get_list('Quotation',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'party_name': ['like', search_text]}],
                                   fields=["name", "quotation_to", "customer_name", "transaction_date", "grand_total", "status"]
                                   )
        return query

    if doctype == "Sales Order":
        query = frappe.db.get_list('Sales Order',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'customer': ['like', search_text]}],
                                   fields=["name", "customer_name", "customer_address", "transaction_date", "grand_total", "status"]
                                   )
        return query

    if doctype == "Sales Invoice":
        query = frappe.db.get_list('Sales Invoice',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_name': ['like', search_text]},
                                               {'customer': ['like', search_text]}],
                                   fields=["name", "customer_name", "customer_address", "posting_date", "grand_total", "status"]
                                   )
        return query

    if doctype == "Payment Entry":
        query = frappe.db.get_list('Payment Entry',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'party_name': ['like', search_text]},
                                               {'mode_of_payment': ['like', search_text]},
                                               {'party': ['like', search_text]}],
                                   fields=["name", "party_name", "payment_type", "mode_of_payment", "posting_date", "paid_amount", "status"]
                                   )
        return query


    if doctype == "Lead Source":
        query = frappe.db.get_list('Lead Source',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'source_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Market Segment":
        query = frappe.db.get_list('Market Segment',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'market_segment': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Industry Type":
        query = frappe.db.get_list('Industry Type',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'industry': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Territory":
        query = frappe.db.get_list('Territory',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'territory_name': ['like', search_text]}],
                                   fields=["name", "parent_territory"]
                                   )
        return query

    if doctype == "Warehouse":
        query = frappe.db.get_list('Warehouse',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'warehouse_name': ['like', search_text]}],
                                   fields=["warehouse_name", "warehouse_type", "parent_warehouse"]
                                   )
        return query

    if doctype == "Country":
        query = frappe.db.get_list('Country',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'country_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Opportunity Type":
        query = frappe.db.get_list('Opportunity Type',
                                   or_filters=[{'name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Customer Group":
        query = frappe.db.get_list('Customer Group',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'customer_group_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Address":
        query = frappe.db.get_list('Address',
                                   filters=[["Dynamic Link", "link_name", "=", "كارفور"]],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'address_title': ['like', search_text]},
                                               {'address_line1': ['like', search_text]},
                                               {'city': ['like', search_text]},
                                               {'phone': ['like', search_text]}],
                                   fields=["address_title", "address_line1", "city", "phone"]
                                   )
        return query

    if doctype == "Contact":
        query = frappe.db.get_list('Contacts',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'email_id': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]},
                                               {'phone': ['like', search_text]},
                                               {'company_name': ['like', search_text]}],
                                   fields=["name", "email_id", "phone", "mobile_no", "company_name"]
                                   )
        return query

    if doctype == "Campaign":
        query = frappe.db.get_list('Campaign',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'campaign_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Currency":
        query = frappe.db.get_list('Currency',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'currency_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Sales Partner":
        query = frappe.db.get_list('Sales Partner',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'partner_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Terms and Conditions":
        query = frappe.db.get_list('Terms and Conditions',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Mode of Payment":
        query = frappe.db.get_list('Mode of Payment',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'mode_of_payment': ['like', search_text]}],
                                   fields=["name", "type"]
                                   )
        return query

    if doctype == "Price List":
        query = frappe.db.get_list('Price List',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'price_list_name': ['like', search_text]}],
                                   fields=["name", "currency"]
                                   )
        return query

    if doctype == "Payment Terms Template":
        query = frappe.db.get_list('Payment Terms Template',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'template_name': ['like', search_text]}],
                                   fields=["name"]
                                   )
        return query

    if doctype == "Cost Center":
        query = frappe.db.get_list('Cost Center',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'cost_center_name': ['like', search_text]}],
                                   fields=["name", "cost_center_name", "parent_cost_center"]
                                   )
        return query

    if doctype == "Project":
        query = frappe.db.get_list('Project',
                                   filters=[{'is_active': ['=', "Yes"]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'project_name': ['like', search_text]}],
                                   fields=["name", "project_name", "status"]
                                   )
        return query

    if doctype == "Account":
        query = frappe.db.get_list('Account',
                                   filters=[{'is_group': ['=', 0]}],
                                   or_filters=[{'name': ['like', search_text]},
                                               {'account_name': ['like', search_text]},
                                               {'account_number': ['like', search_text]}],
                                   fields=["name", "account_type", "root_type", "account_currency", "parent_account"]
                                   )
        return query

    if doctype == "Item":
        query = frappe.db.get_list('Item',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'item_name': ['like', search_text]},
                                               {'item_code': ['like', search_text]}],
                                   fields=["name", "item_name", "item_group", "stock_uom"]
                                   )
        return query

    if doctype == "Material Request":
        query = frappe.db.get_list('Material Request',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]}],
                                   fields=["name", "material_request_type", "transaction_date", "set_warehouse", "status"]
                                   )
        return query

    if doctype == "Stock Entry":
        query = frappe.db.get_list('Stock Entry',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]}],
                                   fields=["name", "stock_entry_type", "posting_date", "from_warehouse", "to_warehouse", "docstatus"]
                                   )
        return query

    if doctype == "Purchase Receipt":
        query = frappe.db.get_list('Purchase Receipt',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]},
                                               {'supplier': ['like', search_text]},
                                               {'supplier_name': ['like', search_text]}],
                                   fields=["name", "supplier", "posting_date", "set_warehouse", "status"]
                                   )
        return query

    if doctype == "Delivery Note":
        query = frappe.db.get_list('Delivery Note',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'title': ['like', search_text]},
                                               {'customer': ['like', search_text]},
                                               {'customer_name': ['like', search_text]}],
                                   fields=["name", "customer", "territory", "posting_date", "set_warehouse", "status"]
                                   )
        return query

    if doctype == "Supplier":
        query = frappe.db.get_list('Supplier',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier_name': ['like', search_text]},
                                               {'mobile_no': ['like', search_text]}],
                                   fields=["name", "supplier_name", "supplier_group", "supplier_type", "country", "mobile_no"]
                                   )
        return query

    if doctype == "Supplier Quotation":
        query = frappe.db.get_list('Supplier Quotation',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier': ['like', search_text]}],
                                   fields=["name", "supplier", "transaction_date", "valid_till", "grand_total", "status"]
                                   )
        return query

    if doctype == "Purchase Order":
        query = frappe.db.get_list('Purchase Order',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier': ['like', search_text]}],
                                   fields=["name", "supplier", "transaction_date", "set_warehouse", "grand_total", "status"]
                                   )
        return query

    if doctype == "Purchase Invoice":
        query = frappe.db.get_list('Purchase Invoice',
                                   or_filters=[{'name': ['like', search_text]},
                                               {'supplier': ['like', search_text]}],
                                   fields=["name", "supplier", "posting_date", "grand_total", "status"]
                                   )
        return query


@frappe.whitelist(allow_guest=True)
def party_search(doctype=0, search_text=0, party=0):
    if party == 0:
        return "Check Data"
    else:
        if doctype == "Address":
            addresses = frappe.db.get_list('Dynamic Link', filters={'link_name': party}, fields=['parent'])
            result = []
            for d in addresses:
                query = frappe.db.sql(""" select name as name ,
                                                address_title as address_title,
                                                 address_line1 as address_line1,
                                                 city as city,
                                                 phone as phone
                                         from tabAddress where name = '{filtered}'
                                         and address_title like '{search_text}'
                                         """.format(filtered=d.parent,search_text=search_text), as_dict=1)
                for x in query:
                    data = {
                        'name': x.name,
                        'address_title': x.address_title,
                        'address_line1': x.address_line1,
                        'city': x.city,
                        'phone': x.phone
                    }
                    result.append(data)

            if result:
                return result
            else:
                return "no result"

        if doctype == "Contact":
            contacts = frappe.db.get_list('Dynamic Link', filters={'link_name': party}, fields=['parent'])
            result = []
            for d in contacts:
                query = frappe.db.sql(""" select name as name ,
                                                email_id as email_id,
                                                 mobile_no as mobile_no,
                                                 phone as phone,
                                                 company_name as company_name
                                         from tabContact where name = '{filtered}'
                                         and name like '{search_text}'
                                         """.format(filtered=d.parent,search_text=search_text), as_dict=1)
                for x in query:
                    data = {
                        'name': x.name,
                        'email_id': x.email_id,
                        'mobile_no': x.mobile_no,
                        'company_name': x.company_name,
                        'phone': x.phone
                    }
                    result.append(data)

            if result:
                return result
            else:
                return "no result"

@frappe.whitelist(allow_guest=True)
def submit(doctype, name):
    doc = frappe.get_doc(doctype, name)
    doc.save()
    doc.submit()
    if doc.docstatus == 1:
        message = frappe.response["message"] = {
            "success_key": True,
            "message": "تم تسجيل المعاملة بنجاح!",
            "name": doc.name
        }
        return message
    else:
        return "حدث خطأ ولم نتمكن من اضافة المعاملة . برجاء المحاولة مرة اخري!"

select `tabMobile user Modules`.modules as modq      from `tabMobile user Modules` join `tabMobile User` on `tabMobile user Modules`.parent = `tabMobile User`.name      where `tabMobile User`.user = '{user}' or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile user Modules`.idx select `tabMobile User Documents`.modules as modq ,                                         `tabMobile User Documents`.document_name as docq                                          from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name                                          where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile User Documents`.idx #modules = {module.modq:"Yes"} #modules.update() #modules.[module.modq]= 34 ## Check for active domain #return "This Domain Is Not Allowed. Please Contact <EMAIL>" select `tabMobile user Modules`.modules as modq          from `tabMobile user Modules` join `tabMobile User` on `tabMobile user Modules`.parent = `tabMobile User`.name          where `tabMobile User`.user = '{user}' or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' order by `tabMobile user Modules`.idx select `tabMobile User Documents`.document_name as docq                                              from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name                                              where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='selling' order by `tabMobile User Documents`.idx select `tabMobile User Documents`.document_name as docq                                              from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name                                              where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='buying' order by `tabMobile User Documents`.idx select `tabMobile User Documents`.document_name as docq                                              from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name                                              where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='stock' order by `tabMobile User Documents`.idx select `tabMobile User Documents`.document_name as docq                                              from `tabMobile User Documents` join `tabMobile User` on `tabMobile User Documents`.parent = `tabMobile User`.name                                              where `tabMobile User`.user = '{user}'  or `tabMobile User`.username = '{user}' or `tabMobile User`.mobile_no = '{user}' and `tabMobile User Documents`.modules ='hr' order by `tabMobile User Documents`.idx # ------------------------------------------------------------------------------------ select name as name ,                                                 address_title as address_title,                                                  address_line1 as address_line1,                                                  city as city,                                                  phone as phone                                          from tabAddress where name = '{filtered}'                                          and address_title like '{search_text}' select name as name ,                                                 email_id as email_id,                                                  mobile_no as mobile_no,                                                  phone as phone,                                                  company_name as company_name                                          from tabContact where name = '{filtered}'                                          and name like '{search_text}'