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