Miscellaneous code to tweak IE access rights on random servers

by MikeHogg23. 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 MikeHogg20. 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 QUON 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
                                        ) wqON 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