Miscellaneous code to tweak IE access rights on random servers

by MikeHogg 23. January 2012 21:33

I set up a framework where developers could write data parsers for a variety of sources that could be scheduled, normalized, logged, and archived for various purposes.  There were hundreds of data sources and jobs so a lot of them had enough similarity that a framework with standard libraries made sense.  A large portion of these were web scrapes.  (I used WatIn for those- a great mocking solution for our purposes).  I would run into problems, because our scheduler would run on a server farm of hundreds, with new machines/instances being created at intervals, and so we basically had to write everything into our jobs.  Here are some of the hacks I put into a Web library to get usual https sites working with the web scrapes, where I had a variety of popup problems (I needed to turn off IE’s built in popup blocker), file download problems (add domain to trusted sites)…  They are all probably obsolete with Server 2012 now, but they might come in handy for the next few years still…

        private static bool IsPopupEnabled(string domain)
        {
            string keylocation = @"Software\Microsoft\Internet Explorer\New Windows\Allow";
            Microsoft.Win32.RegistryKey cu = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parent = cu.OpenSubKey(keylocation);
            if (parent != null) return parent.GetValue(domain) != null;
            else return false;
        }
        private static bool EnablePopup(string domain)
        {
            string keylocation = @"Software\Microsoft\Internet Explorer\New Windows\Allow";
            Microsoft.Win32.RegistryKey cu = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parentkey = cu.CreateSubKey(keylocation);
            parentkey.SetValue(domain, Microsoft.Win32.RegistryValueKind.Binary);
            return IsPopupEnabled(domain);
        }
        private static bool TrustedSiteAddition(string domain)
        {
            const string domainsKeyLocation = @"Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains";
     
            Microsoft.Win32.RegistryKey currentUserKey = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parentKey = currentUserKey.OpenSubKey(domainsKeyLocation, true);
            Microsoft.Win32.RegistryKey key = parentKey.CreateSubKey(domain);
            object objSubDomainValue = key.GetValue("http");
            if (objSubDomainValue == null || Convert.ToInt32(objSubDomainValue) != 0x02)
            {
                key.SetValue("http", 0x02, Microsoft.Win32.RegistryValueKind.DWord);
            }
            objSubDomainValue = key.GetValue("https");
            if (objSubDomainValue == null || Convert.ToInt32(objSubDomainValue) != 0x02)
            {
                key.SetValue("https", 0x02, Microsoft.Win32.RegistryValueKind.DWord);
            }
            return IsTrusted(domain);
        }
        private static bool IsTrusted(string domain)
        {
            const string domainsKeyLocation = @"Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains";
            string keyLocation = string.Format(@"{0}\{1}", domainsKeyLocation, domain);
            Microsoft.Win32.RegistryKey currentUserKey = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey foundRegistryKey = currentUserKey.OpenSubKey(keyLocation, false);
            if (foundRegistryKey != null)
                return true;
            else
                return false;
        }
        private static bool CheckFileDownloadEnabled()
        { 
            string keylocation = @"Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\2";
            Microsoft.Win32.RegistryKey cu = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parent = cu.OpenSubKey(keylocation,true);
            if (parent != null && (int)parent.GetValue(@"2200") != 0x00)
            {
                parent.SetValue(@"2200", 0x00);
                return true;
            }
            else if (parent != null && (int)parent.GetValue(@"2200") == 0x00)
            {
                return true;
            }
            else return false;
        }
        public static bool IEFullyEnabled(string domain)
        {
            if (!IsPopupEnabled(domain) && !EnablePopup(domain)) return false;
            if (!IsTrusted(domain) && !TrustedSiteAddition(domain)) return false;
            if (!CheckFileDownloadEnabled()) return false;
            return true;
        }

Tags:

Automation | C#

Oracle aggregation query

by MikeHogg 20. January 2012 14:56

One of the processes I set up required a merging of a few different data sources.  Besides our own data store of user input data, we also had our own data store of operational account data, and went to another system to get account billing data, and yet another system for customer contract data.  This query was an aggregation of several queries that, all together, aggregated the exact data we needed for our data warehouse import.  Some of the neat features of this query are the company name partition with replace to account for manual data entry discrepencies…

  cursor c_contracts(p_start_date date, p_end_date date, p_snapshot_date date) is
  Select seq_some.nextval, c.propid, to_char(p_start_date,'YYYYMM'), 
         PRODUCT_NAME ,
         ACCOUNTS_SOLD ,
          case when bui_account.bdm_id is not null then trim(csh_account.bdm)
               when bui_contract.bdm_id is not null then trim(csh_contract.bdm)
               else 'Unknown' end BDM, 
          trim(c.account_bdm) account_bdm,
          trim(c.contract_bdm) contract_bdm,
          busunit_id, 
          c.COMPANY_NAME, 
          c.maxid  COMPANY_ID,
          TRUNC(CONTRACT_TERM_MONTHS,2),
          c.COUNTER_SIGN_DATE,
          TRUNC(TOTAL_CREDIT_COST,2),
          CUSTOMERS_SOLD,
          c.EDC_ACCTNO, 
          c.EDC_NAME,
          c.EST_POWER_END_DATE,
          c.EST_POWER_FLOW_DATE,
          ISO,
          MW_SOLD,
          TRUNC(c.SALES_AGENT_FEE,2),
          c.SALESFEE_CONAME,
          STATE,
          STATUS, 
          TRUNC(ORIGINATION_GROSSMARGIN,2),
          TRUNC(ORIGINATION_NETMARGIN,2),
          TRUNC(ORIGINATION_VOLUME,2),
          c.ZONE,
          c.PRICE_DATE,
          substr(1000000000 + nvl(trim(regexp_replace(regexp_replace(c.duns_no,'-|/|[a-z|A-Z]|\.|[[:space:]]',''),'([[:digit:]]{9})[[:digit:]]*','\1')),'999999999'),2,9) DUNS_NO,
          c.SIC_CODE,
          c.CCM_ID, 
          somebillingsystem.num_quotes,   
          ar.AR_Bal_31_60,
          ar.AR_Bal_61_90,
          ar.AR_Bal_91Plus, 
          ar.bad_debt_balance,
          TRUNC(FM_NET_MARGIN,2) FLOW_Margin,
          TRUNC(FM_VOLUME,2)  FLOW_Volume,
          s_billing.bill_type,                    ----------  BILL TYPES AND OPTIONS
          s_billing.billing_option,
          trim(Green_Volume) ,
          trim(REC_GREEN_VOLUME),
          FM_Sales_Agent_Fee, 
          c.leg_month,
          c.leg_year,
          c.annual_mwh,
          contract_type, 
          NON_STD_TERMS_FLG,
          TELESALES_FLG,
          somebillingsystem.broker2,
          somebillingsystem.ou_type_cd,
          somebillingsystem.x_sub_type,
          ls.Billing_Weight,
          somebillingsystem.early_renewal,
          c.servzip,
          c.acquisition_type,
          1, p_snapshot_date 
          , c.OFFER_TYPE
        from (  select e.*, max(e.cust_id) over (partition by replace(replace(e.company_name,','),'.')) maxid
                       , NULL OFFER_TYPE
                  from some_UNI_CONTRACT_SOME_EXTRACT e 
                 union 
                select null customers_sold, null accounts_sold, 
                       r.MASTERCONTRACT_OUR_NUM propid, r.CONTRACT_STATUS status,
                       r.PRODUCTNAME product_name, r.BDM account_bdm, 'RESI' company_name, 
                       r.BDM contract_bdm, to_char(r.COUNTER_SIGN_DATE,'dd-MON-yy') COUNTER_SIGN_DATE, 
                       '1' cust_id, to_char(r.EST_POWER_FLOW_DATE,'dd-MON-yy') EST_POWER_FLOW_DATE, to_char(r.EST_POWER_END_DATE,'dd-MON-yy') EST_POWER_END_DATE, 
                       r.LEAD_SEGMENT, null mw_Sold, null product_group, r.BUSUNIT_NAME region,
                       null sales_agent_fee, null salesfee_coname, null sic_code, r.STATE, r.ZONE, null LEG_MONTH, null LEG_YEAR, null fm_net_margin, 
                       null fm_volume, null fm_sales_agent_fee, r.TOTAL_MARGIN origination_netmargin, r.TOTAL_VOLUME origination_volume, 
                       null origination_grossmargin, r.TOTAL_CREDITCOST total_credit_cost, null duns_no, null busunit_id, r.ISO, 
                       null price_date, null ccm_id, null rec_green_volume, null green_volume, r.UDC_NAME edc_name, r.contract_term contract_term_months, 
                       r.EDC_ACCTNO, r.ZIP servzip, r.ANNUAL_MWH, null contract_type, null non_std_terms_flg, null telesales_flg, null acquisition_type, '1'
                       , r.OFFER_TYPE
                from (select distinct MASTERCONTRACT_OUR_NUM, CONTRACT_STATUS, PRODUCTNAME, BDM,  
                            COUNTER_SIGN_DATE, EST_POWER_FLOW_DATE, EST_POWER_END_DATE, LEAD_SEGMENT, 
                            BUSUNIT_NAME, STATE, ZONE, 
                            sum(TOTAL_MARGIN) over (partition by mastercontract_our_num) TOTAL_MARGIN, 
                            sum(TOTAL_VOLUME) over (partition by mastercontract_our_num) TOTAL_VOLUME, 
                            sum(TOTAL_CREDITCOST) over (partition by mastercontract_our_num) TOTAL_CREDITCOST, 
                            ISO, UDC_NAME, contract_term, 
                            EDC_ACCTNO, ZIP, ANNUAL_MWH 
                            , OFFER_TYPE
                       from cer_res.residential) r
             )c
        
        -------------- somebillingsystem 
        
        left join ( 
                   select ag.agree_num, (select brk.name from s_org_ext@sblreadonly brk where brk.row_id = ag.x_broker_2_account_id) broker2, 
                         (select count(q.row_id) from s_doc_quote@sblreadonly q where q.quote_num = qu.quote_num) num_quotes,
                         round(months_between(sysdate,co.cust_since_dt),2) cust_accumulated_term, co.x_sub_type, co.ou_type_cd,
                         case when exists (select ai.row_id from somebillingsystem.s_agree_item@sblreadonly ai 
                                             join somebillingsystem.s_org_ext@sblreadonly sa on ai.x_sa_ou_id = sa.row_id
                                            where ag.row_id = ai.doc_agree_id 
                                              and ai.itm_stat_cd = 'Pending' and sa.cust_stat_cd = 'Enrolled'
                                              and ag.stat_cd = 'Pending' and ag.x_offer_type = 'Renewal' ) then 1 else 0 end Early_Renewal 
                    from S_DOC_AGREE@sblreadonly AG 
                    JOIN S_DOC_QUOTE@sblreadonly QU  ON AG.QUOTE_ID = QU.ROW_ID(+)
                    JOIN S_ORG_EXT@sblreadonly CO ON AG.TARGET_OU_ID = CO.ROW_ID               
        )somebillingsystem on c.propid = somebillingsystem.agree_num 
        
        -------------   BILL OPTIONS
        
        left join ( select ag.agree_num, 
                           min(bp.x_group_bill)  billing_option, -- Multi over Single
                           min(bp.bill_pref_cd) bill_type -- SOME (Charges) over UDC (Rate) 
                      from s_doc_agree@sblreadonly ag 
                      join s_agree_item@sblreadonly ai on ag.row_id = ai.doc_agree_id
                      join s_org_ext@sblreadonly sa on ai.x_sa_ou_id = sa.row_id
                      join s_party_rel@sblreadonly pr on sa.row_id = pr.rel_party_id 
                                                     and pr.rel_type_cd = 'Bill Group' 
                                                     and pr.x_primary_bill_group = 'Y'
                      join s_org_ext@sblreadonly bg on pr.party_id = bg.row_id 
                      join S_INV_PROF@sblreadonly bp on bg.row_id = bp.accnt_id 
                     group by ag.agree_num
           ) s_billing on s_billing.agree_num = c.propid
        
           
        ---------    AR_BALANCE  
        left join (select contract_id, /*ar.edc_acctno, */sum(ar.ar_bal_current) ar_bal_current, sum(ar.ar_bal_1_30) ar_bal_1_30 , 
                          sum(ar.ar_bal_31_60) ar_bal_31_60, sum(ar.ar_bal_61_90) ar_bal_61_90, 
                          sum(ar.ar_bal_91plus) ar_bal_91plus, ar.snapshot_date, round(sum(bad.balance),2) bad_debt_balance
                     from (select x.contract_id, x.edc_acctno, sum(b.ar_bal_current) ar_bal_current, sum(b.ar_bal_1_30) ar_bal_1_30 , 
                                  sum(b.ar_bal_31_60) ar_bal_31_60, sum(b.ar_bal_61_90) ar_bal_61_90, 
                                  sum(b.ar_bal_91plus) ar_bal_91plus, b.snapshot_date
                             from some_uni_cust_arb b
                             join some_uni_edc_x_propid x on b.edc_acctno = x.edc_acctno and x.snapshot_date = b.snapshot_date
                            where b.snapshot_date = (select max(snapshot_date) from some_uni_cust_arb where snapshot_date <= p_end_date)
                              --and x.contractid = '1-220606257'
                            group by x.contract_id, x.edc_acctno, b.snapshot_date
                           ) ar
                      -- BAD DEBT LOGIC : ave of EDCs, summed up at contract level, so three EDCs in a contract have 24 month avgs of 12, 1250, 650 bad debt show at contract 1912
                      join (select b.edc_acctno, avg(b.ar_bal_91plus) balance
                              from (select a.edc_acctno,trunc(a.snapshot_date,'MM'),  max(a.snapshot_date) maxdate 
                                      from some_uni_cust_arb a group by a.edc_acctno, trunc(snapshot_date, 'MM')
                                   ) maxdate
                              join some_uni_cust_arb b on maxdate.edc_acctno = b.edc_acctno and maxdate.maxdate = b.snapshot_date 
                              where b.snapshot_date between add_months(p_start_date,-23) and p_end_date 
                              group by b.edc_acctno  
                           ) bad on ar.edc_acctno = bad.edc_acctno
                      group by ar.contract_id, ar.snapshot_date
        ) ar on ar.contract_id = c.propid        
  
        ---------    WORK QUEUES AND BILLG MODES 
        left join ( 
                     select contractid,
                            -- for breakout analysis- remove grouping if you run this query alone
                            /*min(billgroupoption) over(partition by contractid) billgroupoption, -- M trumps S 
                            max(bgbmf) over(partition by contractid) bgbmf, -- in case of multiple just get the highest
                            max(ssomef) over(partition by contractid) ssomef, 
                            q.uidwqitem, q.openedtime ,q.wqtypecode, q.wqqueuecode, q.weight 
                            ,sum(nvl(weight,0)) over(partition by contractid) sumweightpercontract*/  
                            decode(min(billgroupoption),'M',max(bgbmf), max(ssomef)) + sum(weight) Billing_Weight
                      from (
                             select con.contractid,
                                    h.billgroupoption,  
                                    bg.billingmodeflag bgbmf, 
                                    sa.billingmodeflag ssomef,   
                                    --wq.uidwqitem, wq.openedtime ,wq.wqtypecode, wq.wqqueuecode, 
                                    wq.weight                             
                              from (select contractid, uidaccount 
                                      from (select c.contractid, ci.uidaccount, max(c.contractid) over (partition by ci.uidaccount) maxid
                                              from (select uidaccount, uidcontract from CUSTOMERSYS.lscmcontractitem@ls_link) ci 
                                              join (select contractid, starttime, stoptime, uidcontract, executetime 
                                                      from CUSTOMERSYS.lscmcontract@ls_link) c on ci.uidcontract = c.uidcontract and c.executetime is not null
                                             where c.starttime < p_end_date and c.stoptime > add_months(p_start_date,-2)
                                            ) where contractid = maxid 
                                     ) con   
                              
                              -- SUBSELECT to pick one(can't tell which is correct) when LS has bad dupes
                              left join (select uidaccount, accountid, billingmodeflag   
                                       from (select a.uidaccount, a.accountid, a.udcacctid, a.billingmodeflag,
                                                    a.lstime, max(a.lstime) over (partition by a.udcacctid) maxlstime
                                               from CUSTOMERSYS.ACCOUNT@ls_link a
                                              where a.accounttypecode = 'SERVICE_ACCOUNT' ) 
                                       where maxlstime = lstime
                                     ) sa on sa.uidaccount = con.uidaccount      
                
                             LEFT JOIN (select lci.accountid, lci.wqtypecode, lci.wqqueuecode, q.weight--, lci.uidwqitem, lci.openedtime
                                         from CUSTOMERSYS.LSWQCLOSEDITEM@ls_link  lci                                                       
                                         join some_work_queue q on lci.WQTYPECODE = q.type and lci.WQQUEUECODE = q.code 
                                        where CLOSEDTIME Between add_months(p_start_date,-2) and p_end_date     
                                        ) wq  ON wq.accountid = sa.accountid           
                                                                                      
                              -- SUBSELECT to pick one(can't tell which is correct) when LS has bad overlap
                             left JOIN (select uidaccountfrom, uidaccountto   
                                      from (select l.uidaccountfrom, l.uidaccountto, 
                                                   max(l.uidaccountfrom) over (partition by l.uidaccountto) maxbgacctid
                                             from CUSTOMERSYS.LSACCOUNTREL@ls_link l
                                            where l.starttime < p_end_date and l.stoptime > add_months(p_start_date,-2))
                                      where uidaccountfrom = maxbgacctid
                                    )x ON sa.UIDACCOUNT = x.UIDACCOUNTTO                                                 
                             left join CUSTOMERSYS.ACCOUNT@ls_link bg on bg.UIDACCOUNT = x.UIDACCOUNTFROM 
                                                                and bg.accounttypecode = 'BILLING_GROUP'  
                             left JOIN (select billgroupoption, uidaccount
                                      from (select ah.billgroupoption, ah.uidaccount, 
                                                   ah.starttime, min(ah.starttime) over (partition by ah.uidaccount) maxstart
                                              from CUSTOMERSYS.ACCOUNTHISTORY@ls_link ah  )
                                     where starttime = maxstart
                                   )h ON bg.UIDACCOUNT = h.UIDACCOUNT  
                                   
                            ) q group by contractid  
                 
         ) ls on ls.contractid = c.propid 
                   
        
        -----------   BDM JOIN     
        left join main_rpt.SOME_sales_hierarchy_new csh_account on UPPER(c.account_bdm) = UPPER(csh_account.bdm_crm)
        left join some_uni_bdm bui_account on csh_account.employee_id = bui_account.bdm_id and bui_account.month = to_char(p_start_date,'YYYYMM')
        left join main_rpt.SOME_sales_hierarchy_new csh_contract on UPPER(c.contract_bdm) = UPPER(csh_contract.bdm_crm)
        left join some_uni_bdm bui_contract on csh_contract.employee_id = bui_contract.bdm_id and bui_contract.month = to_char(p_start_date,'YYYYMM')
        
        where trim(c.propid) is not null
        and c.est_power_end_date >= p_start_date 
        and c.counter_sign_date  <= p_end_date;   
the subselects for AR balances, and the Bad Debt aggregations over different partitions… the Decode for deciding which weights to use in calculations… and picking distinct values from an external system that had inherent 1-3% data anomalies.

Tags:

Oracle

About Mike Hogg

Mike Hogg is a c# developer in Brooklyn.

More Here

Favorite Books

This book had the most influence on my coding style. It drastically changed the way I write code and turned me on to test driven development even if I don't always use it. It made me write clearer, functional-style code using more principles such as DRY, encapsulation, single responsibility, and more. amazon.com

This book opened my eyes to a methodical and systematic approach to upgrading legacy codebases step by step. Incrementally transforming code blocks into testable code before making improvements. amazon.com

More Here