def append_to_tab_series(self):
from frappe import db
# if the series exists, then forget
# about inserting it
if db.sql("""
Select
name
From
tabSeries
Where
name = "{serie}"
""".format(serie=self.get("serie_splited"))):
return False
db.sql(
"""
Insert Into
tabSeries (name, current)
Values
(%(serie)s, {current_value})
""".format(current_value=self.current_value), {
"serie": self.get("serie_splited"),
})
def execute():
columns = ("production_item", )
for column in columns:
database.sql("""Alter Table
`tabProduction Order`
Add {column} Varchar(40) Not Null
""".format(column=column))
def execute():
columns = ("production_item", )
for column in columns:
database.sql("""Alter Table
`tabProduction Order`
Alter {column} Set Default "N/A"
""".format(column=column))
def execute():
columns = (
"expected_start_date",
"expected_end_date",
"actual_start_date",
"actual_time",
"actual_end_date",
)
for column in columns:
database.sql("""Alter Table
`tabPlanning Mission Template`
Drop Column If Exists {column}
""".format(column=column))
def execute():
core = "DocType"
doctype = "Paperboard Caliper"
if not database.exists(core, doctype):
return False
database.sql("""
Update `tab{0}`
Set caliper_uom = capliper_uom
Where
caliper_uom is null
And capliper_uom is not null
""".format(doctype))
def get_data(filters=None):
conditions = get_conditions(filters)
optional_join = get_optional_join(filters)
return database.sql("""
Select
`tabCustomer`.`name`,
`tabCustomer`.`customer_name`,
`tabCustomer`.`customer_group`,
`tabContact`.`salutation`,
`tabContact`.`first_name`,
`tabContact`.`last_name`,
`tabContact`.`email_id`,
`tabContact`.`contact_role`,
`tabContact`.`phone`,
`tabCustomer`.`mobile_no`
From
`tabContact`
Inner Join
`tabDynamic Link`
On
`tabDynamic Link`.`parent` = `tabContact`.`name`
And `tabDynamic Link`.`parenttype` = "Contact"
And `tabDynamic Link`.`parentfield` = "links"
Inner Join
`tabCustomer`
On
`tabDynamic Link`.`link_doctype` = "Customer"
And `tabDynamic Link`.`link_name` = `tabCustomer`.`name`
Where
{conditions}
""".format(optional_join=optional_join, conditions=conditions),
filters,
as_list=True)
def get_discount_amount(cls, customer, as_of_date=today()):
values = {
"posting_date": as_of_date,
"customer": customer,
}
result = database.sql("""
Select
Sum(child.credit_in_account_currency)
From
`tabJournal Entry` parent
Inner Join
`tabJournal Entry Account` child
On
child.parent = parent.name
And child.parenttype = "Journal Entry"
And child.parentfield = "accounts"
Where
parent.docstatus = 1
And parent.voucher_type = "Write Off Entry"
And parent.posting_date <= %(posting_date)s
And child.party_type = "Customer"
And child.party = %(customer)s
""", values)
return result[0][0] if result else 0.000
def get_billing_percent(self):
filters = {
"item_code": self.item_code,
"project_center": self.name,
}
result = database.sql(
"""
Select
Sum(qty) As total_billed
From
`tabSales Invoice Item`
Where
item_code = %(item_code)s
And project_center = %(project_center)s
And docstatus = 1
""", filters)
production_qty = flt(self.production_qty)
if not production_qty:
return .0
billed_qty = flt(result[0][0]) if result else .0
if billed_qty > production_qty:
return 100.0
return flt(billed_qty) / flt(production_qty) * 100.0
def get_base_files():
user = user = "******"
data = db.sql(
"select file_name, file_url, old_parent from `tabFile` where ({}) and (old_parent = '{base}')"
.format(get_permission_query_conditions_for_file(user), base="Home"),
as_dict=1)
print data
def set_invoice_details(row):
data = db.sql("""
SELECT
si.name as "Invoice", sub.name as "Subscription", sub.next_schedule_date as "Next Date"
FROM
`tabSales Invoice` si left join tabSubscription sub on sub.reference_document = si.name
WHERE
si.docstatus < 2
and si.status not in ('Return', 'Credit Note Issued')
and si.sales_order = %s """,
row["SO Number"],
as_dict=1)
row.update({
"Invoice Nos":
','.join([row["Invoice"] for row in data]),
"Subscription No":
','.join([
row["Subscription"] for row in data if row["Subscription"] != None
]),
"Next Invoice Date":
','.join([
str(row["Next Date"]) for row in data
if row["Subscription"] != None
])
})
def get_latest_sample(self):
last_sample = db.sql(
"select name,date from `tabOutward Sample` \
where docstatus = 1 and product_name = %s and party = %s ORDER BY date DESC",
(self.product_name, self.party))
if last_sample:
self.last_sample = last_sample[0][0]
def getdata(cls, filters):
conditions = cls.getconditions(filters)
data = database.sql("""
Select
`tabSales Invoice`.`customer` As customer,
`tabSales Invoice Item`.`item_code` As item_code,
`tabSales Invoice Item`.`description` As item_description,
Sum(`tabSales Invoice Item`.`stock_qty`) As stock_qty,
`tabSales Invoice Item`.`stock_uom` As stock_uom,
Avg(`tabSales Invoice Item`.`rate`) As avgrate,
StdDev(`tabSales Invoice Item`.`rate`) As stddevrate,
Variance(`tabSales Invoice Item`.`rate`) As varrate,
Sum(`tabSales Invoice Item`.`amount`) As amount
From
`tabItem`
Inner Join
`tabSales Invoice Item`
On
`tabItem`.`name` = `tabSales Invoice Item`.`item_code`
Inner Join
`tabSales Invoice`
On
`tabSales Invoice Item`.`parent` = `tabSales Invoice`.`name`
And `tabSales Invoice Item`.`parenttype` = "Sales Invoice"
And `tabSales Invoice Item`.`parentfield` = "items"
Where
{conditions}
Group By
`tabSales Invoice Item`.`item_code`
""".format(conditions=conditions),
filters,
as_dict=True)
return cls.preprocess(data, filters)
def get_latest_ball_mill(self):
ball_mill = db.sql(
"select name, date from `tabBall Mill Data Sheet` \
where docstatus = 1 and product_name = %s and customer_name = %s ORDER BY date DESC",
(self.product_name, self.party))
if ball_mill:
self.last_purchase_reference = ball_mill[0][0]
def _get_children(parent="Home"):
user = frappe.session.user
if frappe.form_dict.name is not None:
parent = frappe.form_dict.name
sql = "select file_name,file_url,old_parent,is_folder from `tabFile` where ({}) and (old_parent = '{parent}')"\
.format(get_permission_query_conditions_for_file(user), parent=parent)
data = db.sql(sql, as_dict=1)
return data
def get_next_value(self):
"""Will return the next safe value for this serie"""
self.split_serie()
# make some common validations
self.validate_values()
self.validate_due_date()
from frappe import db
db.sql(
"""
Update
tabSeries
Set
current = {current_value}
Where
name = %(serie)s
""".format(current_value=self.current_value), {
"serie": self.get("serie_splited"),
})
frappe.db.set(self, "current_value", self.current_value + 1)
# reload from the database with all the changes
self.reload()
# let's make sure that user has not reached the limit
self.validate_current_values()
# to prevent the system from fetching a
# non-updated value
db.sql("commit")
from frappe.model.naming import make_autoname
# if we don't commit before getting here
# it will fetch the value from another transaction
return make_autoname(self.serie)
def get_master_sample(self):
if hasattr(self, 'master_sample'):
master_sample = db.sql(
"select name from `tabOutward Sample` \
where docstatus = 1 and product_name = %s and party = %s and is_master_sample = 1",
(self.product_name, self.party))
if master_sample:
self.master_sample = master_sample[0][0]
def get_all_series():
return database.sql("""
Select
parent as doctype,
options as serie
From
`tabDocField`
Where
fieldname = "naming_series"
""",
as_dict=True)
def get_current_item_group_code(parent_item_group):
filters = pydict(parent_item_group=parent_item_group)
result = database.sql(
"""
Select
Max(item_group_code)
From
`tabItem Group`
Where
parent_item_group = %(parent_item_group)s
""", filters)
return cint(result[0][0])
def upadte_item_price_daily():
data = db.sql("""
select
item, per_unit_price , buying_price_list
from
`tabBOM`
where
docstatus < 2
and is_default = 1 """,as_dict =1)
for row in data:
upadte_item_price(row.item, row.buying_price_list, row.per_unit_price)
return "Latest price updated in Price List."
def get_data(filters=None):
conditions = get_conditions(filters)
optional_join = get_optional_join(filters)
return database.sql("""
Select
`tabCustomer`.`name`,
`tabCustomer`.`customer_name`,
`tabCustomer`.`customer_group`,
`tabParty Account`.`account`,
`tabAddress`.`address_title`,
`tabAddress`.`address_type`,
`tabAddress`.`address_line1`,
`tabAddress`.`address_line2`,
`tabAddress`.`city`,
`tabAddress`.`county`,
`tabAddress`.`state`,
`tabAddress`.`country`,
`tabAddress`.`pincode`,
`tabAddress`.`email_id`,
`tabAddress`.`phone`,
`tabAddress`.`fax`,
`tabAddress`.`is_primary_address`,
`tabAddress`.`is_shipping_address`
From
`tabAddress`
Inner Join
`tabDynamic Link`
On
`tabDynamic Link`.`parent` = `tabAddress`.`name`
And `tabDynamic Link`.`parenttype` = "Address"
And `tabDynamic Link`.`parentfield` = "links"
Inner Join
`tabCustomer`
On
`tabDynamic Link`.`link_doctype` = "Customer"
And `tabDynamic Link`.`link_name` = `tabCustomer`.`name`
Inner Join
`tabParty Account`
On
`tabParty Account`.parenttype = "Customer"
And `tabParty Account`.parentfield = "accounts"
And `tabParty Account`.parent = `tabCustomer`.`name`
Where
{conditions}
""".format(optional_join=optional_join, conditions=conditions),
filters,
as_list=True)
def get_mode_of_payment_account(self, mode_of_payment):
from frappe import db
result = db.sql("""
Select
default_account
From
`tabMode of Payment Account`
Where
parentfield = "accounts"
And parenttype = "Mode of Payment"
And parent = %s
""", mode_of_payment)
return result[0][0] if result else None
def get_valid_loan_charges():
return db.sql("""
Select
loan_charges.name As name,
'Loan Charges' As doctype
From
`tabLoan Charges` As loan_charges
Inner Join
`tabLoan Charges Type` As loan_charges_type
On
loan_charges.loan_charges_type = loan_charges_type.name
Where
TimestampDiff(Month, loan_charges.modified, Current_Timestamp) > 0
And loan_charges_type.generates_fine > 0
And loan_charges.repayment_date < CURDATE()
And loan_charges.status NOT IN ('Paid', 'Paused', 'Closed')""",
as_dict=True)
def new_item_query1(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
conditions = []
return db.sql("""
select tabItem.name, tabItem.item_customer_code, tabItem.item_group, tabItem.item_other_name,
if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name, if(round(sum(bin.actual_qty),2) > 0,CONCAT_WS(':',w.company,round(sum(bin.actual_qty),2)),0)
from tabItem
LEFT JOIN `tabBin` as bin ON bin.item_code = tabItem.item_code
LEFT JOIN `tabWarehouse` as w ON w.name = bin.warehouse
where
tabItem.docstatus < 2
and tabItem.has_variants=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_customer_code LIKE %(txt)s
or tabItem.item_other_name LIKE %(txt)s)
{fcond} {mcond}
group by
w.company,bin.item_code
order by
if(locate(%(_txt)s, tabItem.name), locate(%(_txt)s, tabItem.name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
sum(bin.actual_qty) desc
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters,
conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%')), {
"today": nowdate(),
"txt": "%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
as_dict=as_dict)
def get_invoice_count(self):
filters = {
"project_center": self.name,
}
result = database.sql(
"""
Select
Count(parent) As invoice_count
From
`tabSales Invoice Item`
Where
project_center = %(project_center)s
And docstatus = 1
Group By
parent
""", filters)
return flt(result[0][0]) if result else .0
def get_delivery_count(self):
filters = {
"project_center": self.name,
}
result = database.sql(
"""
Select
Count(name) As delivery_count
From
`tabDelivery Note`
Where
project_center = %(project_center)s
And docstatus = 1
Group By
parent
""", filters)
return flt(result[0][0]) if result else .0
def get_data(filters):
condition = filters.customer and " and so.customer = '%s' " % filters.customer or ""
data = db.sql("""
SELECT
so.customer as "Customer", so.name as "SO Number", so.contract_start_date as "Start Date", so.contract_end_date as "End Date",
(SELECT
count(*)
FROM
`tabPayment Schedule`
WHERE
parent = so.name
) as "Total Invoices to be made",
(SELECT
count(*)
FROM
`tabSales Invoice`
WHERE
docstatus < 2
and status not in ('Return', 'Credit Note Issued')
and sales_order = so.name
) as "Actual Invoices made"
FROM
`tabSales Order` so
WHERE
so.docstatus < 2
and so.status not in ('Completed', 'Closed')
{condition}
ORDER BY
so.customer """.format(condition=condition),
as_dict=1)
for row in data:
set_invoice_details(row)
row["Pending Invoices to be made"] = int(
row["Total Invoices to be made"]) - int(
row["Actual Invoices made"])
return data
def get_data(filters=None):
from frappe import db
conditions = get_conditions(filters)
return db.sql("""
Select
`tabParty Portfolio`.name As portfolio,
`tabParty Portfolio`.supplier As supplier,
`tabSales Invoice`.name As invoice,
`tabSales Invoice`.customer,
`tabSales Invoice`.posting_date,
`tabSales Invoice`.due_date,
`tabSales Invoice`.base_grand_total,
`tabSales Invoice`.base_grand_total -
(
(
`tabSales Invoice`.outstanding_amount
- `tabSales Invoice`.rounding_adjustment
) * `tabSales Invoice`.conversion_rate
) As received_amount,
`tabSales Invoice`.outstanding_amount
From
`tabCase File`
Inner Join
`tabParty Portfolio`
On
`tabCase File`.parent = `tabParty Portfolio`.name
And `tabCase File`.parenttype = "Party Portfolio"
And `tabCase File`.parentfield = "detail"
Inner Join
`tabSales Invoice`
On
`tabCase File`.invoice = `tabSales Invoice`.name
And `tabCase File`.docstatus = `tabSales Invoice`.docstatus
Where
{conditions}
""".format(conditions=conditions),
filters,
debug=False)
def new_item_query(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
conditions = []
return db.sql("""
select tabItem.name, tabItem.item_customer_code, tabItem.item_group, tabItem.item_other_name,
if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
tabItem.item_group, if(length(tabItem.description) > 40, concat(substr(tabItem.description, 1, 40), "..."), description) as decription
from tabItem
where
tabItem.docstatus < 2
and tabItem.has_variants=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_customer_code LIKE %(txt)s
or tabItem.item_other_name LIKE %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999)
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters,
conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%')), {
"today": nowdate(),
"txt": "%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
as_dict=as_dict)
def get_case_records(case_file):
from frappe import db
return db.sql("""
Select
`tabCase Record`.transaction_date,
`tabCase Record`.activity_type,
`tabCase Record`.activity_option,
`tabCase Record`.notes,
`tabCase Record`.contact_mean,
`tabCase Record`.next_contact_mean,
`tabCase Record`.next_contact_date
From
`tabCase Record`
Where
`tabCase Record`.reference_type = "Case File"
And `tabCase Record`.reference_name = %s
Order By
`tabCase Record`.`creation` Desc
""",
case_file,
as_dict=True)
def get_paid_amount(cls, filters, data):
conditions = cls.get_conditions(filters, ignore_filters=["customer"])
if not conditions:
conditions += "1 = 1"
customer = database.escape(data.customer)
conditions += " And party_type = 'Customer'"
conditions += " And party = '{0}'".format(customer)
result = database.sql("""
Select
Sum(If(payment_type="Receive", paid_amount, 0.000))
- Sum(If(payment_type="Pay", paid_amount, 0.000)) balance
From
`tabPayment Entry`
Where
{conditions}
""".format(conditions=conditions), filters)
return result[0][0] if result else 0.000
