def get_sales_data(date_end, dict_db):
sql = """
declare @dateend date = '{d1}'
declare @datestart date = dateadd("m", -11, @dateend)
set @datestart = dateadd("d", -datepart("d", @datestart) + 1, @datestart)
select
glb.period,
-sum(glb.amount) as Sales
from Playground.[myop\jason.walker].gl_balance glb
inner join Playground.[myop\jason.walker].gl_account_reporting glr
on glb.company = glr.company
and glb.gl_account = glr.gl_account
where
glr.company in ('HT', 'MYOP')
and glr.level_1 = 'Sales'
and glb.period between @datestart and @dateend
group by
glb.period
""".format(d1=date_end)
sales = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return sales
def retrieve_level_2(date_start, date_ytd_start, dict_db):
sql = """
declare @RptPeriodCur date = '{d1}'
declare @YTDStart date = '{d2}'
select
gr.level_2,
sum(case when gb.period = @RptPeriodCur then gb.amount else 0 end) as Amount_Mth,
sum(case when gb.period between @YTDStart and @RptPeriodCur then gb.amount else 0 end) as Amount_YTD
from Playground.[myop\jason.walker].gl_balance gb
inner join Playground.[myop\jason.walker].gl_account_reporting gr
on gb.gl_account = gr.gl_account
and gb.company = gr.company
where
gb.period between @YTDStart and @RptPeriodCur
and gr.level_2 is not null
group by
gr.level_2
""".format(d1=date_start, d2=date_ytd_start)
gl_data = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return gl_data
def retrieve1(dict_db):
sql = "select * from [myop\jason.walker].office_products_prod_class"
result = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return result
def retrieve_bs_data(dict_db):
sql = """
select
glr.level_1,
glr.level_2,
glb.period,
sum(glb.amount) as amount
from Playground.[myop\jason.walker].gl_balance_bs glb
inner join Playground.[myop\jason.walker].gl_account_reporting glr
on glb.company = glr.company
and glb.gl_account = glr.gl_account
where
glr.financial_statement = 'BS'
and glr.level_1 is not null
and glr.level_2 is not null
and glb.company in ('HT', 'MYOP')
group by
glr.level_1,
glr.level_2,
glb.period
union all
select
'Net Income',
'Net Income',
glb.period,
sum(glb.amount) as amount
from Playground.[myop\jason.walker].gl_balance_bs glb
where
glb.gl_account = '99999'
and glb.company in ('HT', 'MYOP')
group by
glb.period
union all
select
'Net Income YTD',
'Net Income YTD',
glb.period,
sum(glb.amount) as amount
from Playground.[myop\jason.walker].gl_balance_bs glb
where
glb.gl_account = '99999Z'
and glb.company in ('HT', 'MYOP')
group by
glb.period
"""
gl_data = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return gl_data
def get_rebate_data(date_end, dict_db):
sql = """
declare @dateend date = '{d1}'
declare @datestart date = dateadd("m", -11, @dateend)
set @datestart = dateadd("d", -datepart("d", @datestart) + 1, @datestart)
select
glb.period,
case when (glb.company = 'HT' and glb.gl_account = '45650') or (glb.company = 'MYOP' and glb.gl_account = '52000') then 'Pricing'
when glb.company = 'MYOP' and glb.gl_account = '52100' then 'Wholesalers'
when (glb.company = 'HT' and glb.gl_account = '45660') or (glb.company = 'MYOP' and glb.gl_account = '52200') then 'Manufacturing'
when (glb.company = 'HT' and glb.gl_account in ('45640', '45911')) or (glb.company = 'MYOP' and glb.gl_account = '52300') then 'Direct Buy Savings'
else 'Unknown'
end as Rebate_Category,
-sum(glb.amount) as Rebates
from Playground.[myop\jason.walker].gl_balance glb
inner join Playground.[myop\jason.walker].gl_account_reporting glr
on glb.company = glr.company
and glb.gl_account = glr.gl_account
where
glr.company in ('HT', 'MYOP')
and glr.level_1 = 'Adjustment to Margin'
and glr.level_2 = 'Rebates'
and glb.period between @datestart and @dateend
group by
glb.period,
case when (glb.company = 'HT' and glb.gl_account = '45650') or (glb.company = 'MYOP' and glb.gl_account = '52000') then 'Pricing'
when glb.company = 'MYOP' and glb.gl_account = '52100' then 'Wholesalers'
when (glb.company = 'HT' and glb.gl_account = '45660') or (glb.company = 'MYOP' and glb.gl_account = '52200') then 'Manufacturing'
when (glb.company = 'HT' and glb.gl_account in ('45640', '45911')) or (glb.company = 'MYOP' and glb.gl_account = '52300') then 'Direct Buy Savings'
else 'Unknown'
end
""".format(d1=date_end)
rebates = gl_data = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return rebates
def update1(date_end, dict_db):
#Build GL Reporting Table
sql = """
USE [Playground]
DECLARE @RC int
EXECUTE @RC = [myop\jason.walker].[proc_gl_account_reporting]
"""
sql_update.update1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
#Populate GL Balances
sql = """
USE [Playground]
DECLARE @RC int
DECLARE @dateend date
EXECUTE @RC = [myop\jason.walker].[proc_gl_balance]
'{d1}'
""".format(d1=date_end)
sql_update.update1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
#Validate Net Income
sql = """
declare @dateend date = '{d1}'
declare @datestart date = dateadd("d",-day(@dateend)+1,@dateend)
select
q1.company,
sum(q1.amount)
from (
select
glb.company,
sum(glb.amount) as amount --positive
from Playground.[myop\jason.walker].gl_balance glb
inner join Playground.[myop\jason.walker].gl_account_reporting glr
on glb.gl_account = glr.gl_account
and glb.company = glr.company
where
glb.period = @datestart
and glr.financial_statement = 'P&L'
and glr.level_1 is not null
and glr.level_2 is not null
group by
glb.company
union all
select
'HT' as company,
-sum(gl.Amount) --negative
from TNDCSQL03.NAVRep.dbo.[Hi Touch$G_L Entry] gl with(nolock)
where
gl.[G_L Account No_] between '30000' and '69999'
and gl.[Posting Date] between @datestart and @dateend
union all
select
'MYOP' as company,
-sum(gl.Amount) --negative
from TNDCSQL03.NAVRep.dbo.[MYOP$G_L Entry] gl with(nolock)
where
gl.[G_L Account No_] between '40000' and '79999'
and gl.[Posting Date] between @datestart and @dateend
union all
select
'RAC' as company,
-sum(gl.Amount) --negative
from TNDCSQL03.NAVRep.dbo.[Rentacrate$G_L Entry] gl with(nolock)
where
gl.[G_L Account No_] between '40000' and '79999'
and gl.[Posting Date] between @datestart and @dateend
) q1
group by
q1.company
""".format(d1=date_end)
net_income_recon = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return net_income_recon
def update2(date_end, dict_db):
#Populate GL Balances
sql = """
USE [Playground]
DECLARE @RC int
DECLARE @dateend date
EXECUTE @RC = [myop\jason.walker].[proc_gl_balance_bs]
'{d1}'
""".format(d1=date_end)
sql_update.update1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
#Validate Balance Sheet
sql = """
declare @dateend date = '{d1}'
select
q1.company,
sum(q1.amount) as Amount
from (
select
'GLBalance' as source,
glb.company,
glb.gl_account,
-sum(glb.amount) as amount --negative
from Playground.[myop\jason.walker].gl_balance_bs glb
inner join Playground.[myop\jason.walker].gl_account_reporting glr
on glb.gl_account = glr.gl_account
and glb.company = glr.company
where
glb.period = @dateend
and glr.financial_statement = 'BS'
and glr.level_1 is not null
and glr.level_2 is not null
group by
glb.company,
glb.gl_account
union all
select
'GLEntry' as source,
'HT' as company,
gl.[G_L Account No_],
sum(gl.Amount) --positive
from TNDCSQL03.NAVRep.dbo.[Hi Touch$G_L Entry] gl with(nolock)
where
gl.[G_L Account No_] between '10000' and '29999'
and gl.[Posting Date] <= @dateend
group by
gl.[G_L Account No_]
union all
select
'GLEntry' as source,
'MYOP' as company,
gl.[G_L Account No_],
sum(gl.Amount) --positive
from TNDCSQL03.NAVRep.dbo.[MYOP$G_L Entry] gl with(nolock)
where
gl.[G_L Account No_] between '10000' and '39999'
and gl.[Posting Date] <= @dateend
group by
gl.[G_L Account No_]
union all
select
'GLEntry' as source,
'RAC' as company,
gl.[G_L Account No_] collate Latin1_General_100_CS_AS,
sum(gl.Amount) --positive
from TNDCSQL03.NAVRep.dbo.[Rentacrate$G_L Entry] gl with(nolock)
where
gl.[G_L Account No_] between '10000' and '39999'
and gl.[Posting Date] <= @dateend
group by
gl.[G_L Account No_]
) q1
group by
q1.company
""".format(d1=date_end)
balance_sheet_recon = sql_retrieve.getdata1(a_driver=dict_db.get('driver'),
a_server=dict_db.get('server_2'),
a_db=dict_db.get('db_playground'),
a_sql=sql)
return balance_sheet_recon
