API tools faq
paste
Guest User

Untitled

a guest
Feb 28th, 2018
164
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 24.49 KB | None | 0 0
raw download clone embed print report
  1. import csv
  2. import re
  3. import certegy
  4. import pyodbc
  5.  
  6. conn = pyodbc.connect('DRIVER={SQL Server};SERVER=10.1.0.45;DATABASE=q2db_312102;UID=sa;PWD=q2success', True)
  7. db = conn.cursor()
  8.  
  9. addresses = [line for line in csv.reader(open('Customer.txt','r'),'excel')][1:]
  10. comm_users = [line for line in csv.reader(open('Customer_Representative.txt','r'),'excel')][1:]
  11. companies = [line for line in csv.reader(open('Corp_Customer.txt','r'),'excel')][1:]
  12. retail_users = [line for line in csv.reader(open('Indv_Customer.txt','r'),'excel')][1:]
  13. user_login = [line for line in csv.reader(open('INET_Cust_Info.txt','r'),'excel')][1:]
  14. phone_numbers = [line for line in csv.reader(open('Customer_Phone.txt','r'),'excel')][1:]
  15. user_accounts = [line for line in csv.reader(open('Account_Cust.txt','r'),'excel')][1:]
  16. comm_accounts = [line for line in csv.reader(open('Account_CustRep.txt','r'),'excel')][1:]
  17. accounts = [line for line in csv.reader(open('Account.txt','r'),'excel')][1:]
  18. groups = [line for line in csv.reader(open('App_Cust_Info.txt','r'),'excel')][1:]
  19. passwords = [line for line in csv.reader(open('312102passwords.txt','r'))]
  20. account_id_file = [line for line in csv.reader(open('312102accounts.txt','r'),'excel-tab')]
  21. comm_username_map_file = [line for line in csv.reader(open('corp_userids.txt','r'),'excel-tab')]
  22. import_exceptions = [line[0] for line in csv.reader(open('import_exception.txt','r'),'excel')]
  23.  
  24. valid_states = ('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY')
  25.  
  26. acct_map = {
  27. 'CCA':'L',
  28. 'CDA':'T',
  29. 'CLA':'L',
  30. 'DDA':'D',
  31. 'FCA':'D',
  32. 'FSA':'S',
  33. 'GLA':'D',
  34. 'ILA':'L',
  35. 'IRA':'T',
  36. 'MLA':'L',
  37. 'MMA':'D',
  38. 'OEL':'L',
  39. 'PLA':'L',
  40. 'SAV':'S'
  41. }
  42.  
  43. rights_map = {
  44. 5:{
  45. 'True':2,
  46. 'False':0
  47. },
  48. 6:{
  49. 'True':1,
  50. 'False':0
  51. },
  52. 7:{
  53. 'True':4,
  54. 'False':0
  55. },
  56. }
  57.  
  58. phone_map = {
  59. 'Cell':'Mobile',
  60. 'Work':'Business',
  61. 'Home':'Home',
  62. 'Fax':'Fax'
  63. }
  64.  
  65. phoneregex = re.compile('[A-Za-z\-\.\(\)\/\=]')
  66. nameregex = re.compile("[\']")
  67. emailregex = re.compile("^(([A-Za-z0-9]+_+)|([A-Za-z0-9]+\-+)|([A-Za-z0-9]+\.+)|([A-Za-z0-9]+\++))*[A-Za-z0-9]+@((\w+\-+)|(\w+\.))*\w{1,63}\.[a-zA-Z]{2,6}$")
  68.  
  69. header = ['CustomerName', 'CustomerTaxID', 'CustomerIsCompany', 'UserFirstName', 'UserMiddleName', 'UserLastName', 'UserSalutation', 'UserSuffix', 'UserSSN', 'UserLoginName', 'UserPassword', 'VoiceLoginName', 'VoicePassword', 'StreetAddress1', 'StreetAddress2', 'City', 'State', 'PostalCode', 'AddressTypeName', 'HomeCityOrAreaCode', 'HomeLocalNumber', 'HomeExtension', 'WorkCityOrAreaCode', 'WorkLocalNumber', 'WorkExtension', 'CellCityOrAreaCode', 'CellLocalNumber', 'CellExtension', 'FaxCityOrAreaCode', 'FaxLocalNumber', 'FaxExtension', 'EmailAddress', 'TacEmailAddress1', 'TacEmailAddress2', 'TacEmailAddress3', 'TacPhoneNumber1', 'TacPhoneNumber2', 'TacPhoneNumber3', 'ThirdPartySubscriberId', 'UserHasStatementImageAccess', 'UserHasRecipientManagement', 'UserHasAllTemplateAccess', 'UserHasCanManageUserRights', 'UserHasCanAddSubsidiaries', 'SecureMessageSubject', 'SecureMessageBody', 'CustInfo', 'UserInfo', 'A01IntNum', 'A01ExtNum', 'A01HostProdType', 'A01IntCIF', 'A01ExtCIF', 'A01Access', 'A01NickName', 'A02IntNum', 'A02ExtNum', 'A02HostProdType', 'A02IntCIF', 'A02ExtCIF', 'A02Access', 'A02NickName', 'A03IntNum', 'A03ExtNum', 'A03HostProdType', 'A03IntCIF', 'A03ExtCIF', 'A03Access', 'A03NickName', 'A04IntNum', 'A04ExtNum', 'A04HostProdType', 'A04IntCIF', 'A04ExtCIF', 'A04Access', 'A04NickName', 'A05IntNum', 'A05ExtNum', 'A05HostProdType', 'A05IntCIF', 'A05ExtCIF', 'A05Access', 'A05NickName', 'A06IntNum', 'A06ExtNum', 'A06HostProdType', 'A06IntCIF', 'A06ExtCIF', 'A06Access', 'A06NickName', 'A07IntNum', 'A07ExtNum', 'A07HostProdType', 'A07IntCIF', 'A07ExtCIF', 'A07Access', 'A07NickName', 'A08IntNum', 'A08ExtNum', 'A08HostProdType', 'A08IntCIF', 'A08ExtCIF', 'A08Access', 'A08NickName', 'A09IntNum', 'A09ExtNum', 'A09HostProdType', 'A09IntCIF', 'A09ExtCIF', 'A09Access', 'A09NickName', 'A10IntNum', 'A10ExtNum', 'A10HostProdType', 'A10IntCIF', 'A10ExtCIF', 'A10Access', 'A10NickName', 'A11IntNum', 'A11ExtNum', 'A11HostProdType', 'A11IntCIF', 'A11ExtCIF', 'A11Access', 'A11NickName', 'A12IntNum', 'A12ExtNum', 'A12HostProdType', 'A12IntCIF', 'A12ExtCIF', 'A12Access', 'A12NickName', 'A13IntNum', 'A13ExtNum', 'A13HostProdType', 'A13IntCIF', 'A13ExtCIF', 'A13Access', 'A13NickName', 'A14IntNum', 'A14ExtNum', 'A14HostProdType', 'A14IntCIF', 'A14ExtCIF', 'A14Access', 'A14NickName', 'A15IntNum', 'A15ExtNum', 'A15HostProdType', 'A15IntCIF', 'A15ExtCIF', 'A15Access', 'A15NickName', 'A16IntNum', 'A16ExtNum', 'A16HostProdType', 'A16IntCIF', 'A16ExtCIF', 'A16Access', 'A16NickName', 'A17IntNum', 'A17ExtNum', 'A17HostProdType', 'A17IntCIF', 'A17ExtCIF', 'A17Access', 'A17NickName', 'A18IntNum', 'A18ExtNum', 'A18HostProdType', 'A18IntCIF', 'A18ExtCIF', 'A18Access', 'A18NickName', 'A19IntNum', 'A19ExtNum', 'A19HostProdType', 'A19IntCIF', 'A19ExtCIF', 'A19Access', 'A19NickName', 'A20IntNum', 'A20ExtNum', 'A20HostProdType', 'A20IntCIF', 'A20ExtCIF', 'A20Access', 'A20NickName', 'A21IntNum', 'A21ExtNum', 'A21HostProdType', 'A21IntCIF', 'A21ExtCIF', 'A21Access', 'A21NickName', 'A22IntNum', 'A22ExtNum', 'A22HostProdType', 'A22IntCIF', 'A22ExtCIF', 'A22Access', 'A22NickName', 'A23IntNum', 'A23ExtNum', 'A23HostProdType', 'A23IntCIF', 'A23ExtCIF', 'A23Access', 'A23NickName', 'A24IntNum', 'A24ExtNum', 'A24HostProdType', 'A24IntCIF', 'A24ExtCIF', 'A24Access', 'A24NickName', 'A25IntNum', 'A25ExtNum', 'A25HostProdType', 'A25IntCIF', 'A25ExtCIF', 'A25Access', 'A25NickName', 'A26IntNum', 'A26ExtNum', 'A26HostProdType', 'A26IntCIF', 'A26ExtCIF', 'A26Access', 'A26NickName', 'A27IntNum', 'A27ExtNum', 'A27HostProdType', 'A27IntCIF', 'A27ExtCIF', 'A27Access', 'A27NickName', 'A28IntNum', 'A28ExtNum', 'A28HostProdType', 'A28IntCIF', 'A28ExtCIF', 'A28Access', 'A28NickName', 'A29IntNum', 'A29ExtNum', 'A29HostProdType', 'A29IntCIF', 'A29ExtCIF', 'A29Access', 'A29NickName', 'A30IntNum', 'A30ExtNum', 'A30HostProdType', 'A30IntCIF', 'A30ExtCIF', 'A30Access', 'A30NickName', 'A31IntNum', 'A31ExtNum', 'A31HostProdType', 'A31IntCIF', 'A31ExtCIF', 'A31Access', 'A31NickName', 'A32IntNum', 'A32ExtNum', 'A32HostProdType', 'A32IntCIF', 'A32ExtCIF', 'A32Access', 'A32NickName', 'A33IntNum', 'A33ExtNum', 'A33HostProdType', 'A33IntCIF', 'A33ExtCIF', 'A33Access', 'A33NickName', 'A34IntNum', 'A34ExtNum', 'A34HostProdType', 'A34IntCIF', 'A34ExtCIF', 'A34Access', 'A34NickName', 'A35IntNum', 'A35ExtNum', 'A35HostProdType', 'A35IntCIF', 'A35ExtCIF', 'A35Access', 'A35NickName', 'A36IntNum', 'A36ExtNum', 'A36HostProdType', 'A36IntCIF', 'A36ExtCIF', 'A36Access', 'A36NickName', 'A37IntNum', 'A37ExtNum', 'A37HostProdType', 'A37IntCIF', 'A37ExtCIF', 'A37Access', 'A37NickName', 'A38IntNum', 'A38ExtNum', 'A38HostProdType', 'A38IntCIF', 'A38ExtCIF', 'A38Access', 'A38NickName', 'A39IntNum', 'A39ExtNum', 'A39HostProdType', 'A39IntCIF', 'A39ExtCIF', 'A39Access', 'A39NickName', 'A40IntNum', 'A40ExtNum', 'A40HostProdType', 'A40IntCIF', 'A40ExtCIF', 'A40Access', 'A40NickName', 'A41IntNum', 'A41ExtNum', 'A41HostProdType', 'A41IntCIF', 'A41ExtCIF', 'A41Access', 'A41NickName', 'A42IntNum', 'A42ExtNum', 'A42HostProdType', 'A42IntCIF', 'A42ExtCIF', 'A42Access', 'A42NickName', 'A43IntNum', 'A43ExtNum', 'A43HostProdType', 'A43IntCIF', 'A43ExtCIF', 'A43Access', 'A43NickName', 'A44IntNum', 'A44ExtNum', 'A44HostProdType', 'A44IntCIF', 'A44ExtCIF', 'A44Access', 'A44NickName', 'A45IntNum', 'A45ExtNum', 'A45HostProdType', 'A45IntCIF', 'A45ExtCIF', 'A45Access', 'A45NickName', 'A46IntNum', 'A46ExtNum', 'A46HostProdType', 'A46IntCIF', 'A46ExtCIF', 'A46Access', 'A46NickName', 'A47IntNum', 'A47ExtNum', 'A47HostProdType', 'A47IntCIF', 'A47ExtCIF', 'A47Access', 'A47NickName', 'A48IntNum', 'A48ExtNum', 'A48HostProdType', 'A48IntCIF', 'A48ExtCIF', 'A48Access', 'A48NickName', 'A49IntNum', 'A49ExtNum', 'A49HostProdType', 'A49IntCIF', 'A49ExtCIF', 'A49Access', 'A49NickName', 'A50IntNum', 'A50ExtNum', 'A50HostProdType', 'A50IntCIF', 'A50ExtCIF', 'A50Access', 'A50NickName', 'A51IntNum', 'A51ExtNum', 'A51HostProdType', 'A51IntCIF', 'A51ExtCIF', 'A51Access', 'A51NickName', 'A52IntNum', 'A52ExtNum', 'A52HostProdType', 'A52IntCIF', 'A52ExtCIF', 'A52Access', 'A52NickName', 'A53IntNum', 'A53ExtNum', 'A53HostProdType', 'A53IntCIF', 'A53ExtCIF', 'A53Access', 'A53NickName', 'A54IntNum', 'A54ExtNum', 'A54HostProdType', 'A54IntCIF', 'A54ExtCIF', 'A54Access', 'A54NickName', 'A55IntNum', 'A55ExtNum', 'A55HostProdType', 'A55IntCIF', 'A55ExtCIF', 'A55Access', 'A55NickName', 'A56IntNum', 'A56ExtNum', 'A56HostProdType', 'A56IntCIF', 'A56ExtCIF', 'A56Access', 'A56NickName', 'A57IntNum', 'A57ExtNum', 'A57HostProdType', 'A57IntCIF', 'A57ExtCIF', 'A57Access', 'A57NickName', 'A58IntNum', 'A58ExtNum', 'A58HostProdType', 'A58IntCIF', 'A58ExtCIF', 'A58Access', 'A58NickName', 'A59IntNum', 'A59ExtNum', 'A59HostProdType', 'A59IntCIF', 'A59ExtCIF', 'A59Access', 'A59NickName', 'A60IntNum', 'A60ExtNum', 'A60HostProdType', 'A60IntCIF', 'A60ExtCIF', 'A60Access', 'A60NickName', 'A61IntNum', 'A61ExtNum', 'A61HostProdType', 'A61IntCIF', 'A61ExtCIF', 'A61Access', 'A61NickName', 'A62IntNum', 'A62ExtNum', 'A62HostProdType', 'A62IntCIF', 'A62ExtCIF', 'A62Access', 'A62NickName', 'A63IntNum', 'A63ExtNum', 'A63HostProdType', 'A63IntCIF', 'A63ExtCIF', 'A63Access', 'A63NickName', 'A64IntNum', 'A64ExtNum', 'A64HostProdType', 'A64IntCIF', 'A64ExtCIF', 'A64Access', 'A64NickName', 'A65IntNum', 'A65ExtNum', 'A65HostProdType', 'A65IntCIF', 'A65ExtCIF', 'A65Access', 'A65NickName', 'A66IntNum', 'A66ExtNum', 'A66HostProdType', 'A66IntCIF', 'A66ExtCIF', 'A66Access', 'A66NickName', 'A67IntNum', 'A67ExtNum', 'A67HostProdType', 'A67IntCIF', 'A67ExtCIF', 'A67Access', 'A67NickName', 'A68IntNum', 'A68ExtNum', 'A68HostProdType', 'A68IntCIF', 'A68ExtCIF', 'A68Access', 'A68NickName', 'A69IntNum', 'A69ExtNum', 'A69HostProdType', 'A69IntCIF', 'A69ExtCIF', 'A69Access', 'A69NickName', 'A70IntNum', 'A70ExtNum', 'A70HostProdType', 'A70IntCIF', 'A70ExtCIF', 'A70Access', 'A70NickName', 'A71IntNum', 'A71ExtNum', 'A71HostProdType', 'A71IntCIF', 'A71ExtCIF', 'A71Access', 'A71NickName', 'A72IntNum', 'A72ExtNum', 'A72HostProdType', 'A72IntCIF', 'A72ExtCIF', 'A72Access', 'A72NickName', 'A73IntNum', 'A73ExtNum', 'A73HostProdType', 'A73IntCIF', 'A73ExtCIF', 'A73Access', 'A73NickName', 'A74IntNum', 'A74ExtNum', 'A74HostProdType', 'A74IntCIF', 'A74ExtCIF', 'A74Access', 'A74NickName', 'A75IntNum', 'A75ExtNum', 'A75HostProdType', 'A75IntCIF', 'A75ExtCIF', 'A75Access', 'A75NickName', 'A76IntNum', 'A76ExtNum', 'A76HostProdType', 'A76IntCIF', 'A76ExtCIF', 'A76Access', 'A76NickName', 'A77IntNum', 'A77ExtNum', 'A77HostProdType', 'A77IntCIF', 'A77ExtCIF', 'A77Access', 'A77NickName', 'A78IntNum', 'A78ExtNum', 'A78HostProdType', 'A78IntCIF', 'A78ExtCIF', 'A78Access', 'A78NickName', 'A79IntNum', 'A79ExtNum', 'A79HostProdType', 'A79IntCIF', 'A79ExtCIF', 'A79Access', 'A79NickName', 'A80IntNum', 'A80ExtNum', 'A80HostProdType', 'A80IntCIF', 'A80ExtCIF', 'A80Access', 'A80NickName', 'A81IntNum', 'A81ExtNum', 'A81HostProdType', 'A81IntCIF', 'A81ExtCIF', 'A81Access', 'A81NickName', 'A82IntNum', 'A82ExtNum', 'A82HostProdType', 'A82IntCIF', 'A82ExtCIF', 'A82Access', 'A82NickName', 'A83IntNum', 'A83ExtNum', 'A83HostProdType', 'A83IntCIF', 'A83ExtCIF', 'A83Access', 'A83NickName', 'A84IntNum', 'A84ExtNum', 'A84HostProdType', 'A84IntCIF', 'A84ExtCIF', 'A84Access', 'A84NickName', 'A85IntNum', 'A85ExtNum', 'A85HostProdType', 'A85IntCIF', 'A85ExtCIF', 'A85Access', 'A85NickName', 'A86IntNum', 'A86ExtNum', 'A86HostProdType', 'A86IntCIF', 'A86ExtCIF', 'A86Access', 'A86NickName', 'A87IntNum', 'A87ExtNum', 'A87HostProdType', 'A87IntCIF', 'A87ExtCIF', 'A87Access', 'A87NickName', 'A88IntNum', 'A88ExtNum', 'A88HostProdType', 'A88IntCIF', 'A88ExtCIF', 'A88Access', 'A88NickName', 'A89IntNum', 'A89ExtNum', 'A89HostProdType', 'A89IntCIF', 'A89ExtCIF', 'A89Access', 'A89NickName', 'A90IntNum', 'A90ExtNum', 'A90HostProdType', 'A90IntCIF', 'A90ExtCIF', 'A90Access', 'A90NickName', 'A91IntNum', 'A91ExtNum', 'A91HostProdType', 'A91IntCIF', 'A91ExtCIF', 'A91Access', 'A91NickName', 'A92IntNum', 'A92ExtNum', 'A92HostProdType', 'A92IntCIF', 'A92ExtCIF', 'A92Access', 'A92NickName', 'A93IntNum', 'A93ExtNum', 'A93HostProdType', 'A93IntCIF', 'A93ExtCIF', 'A93Access', 'A93NickName', 'A94IntNum', 'A94ExtNum', 'A94HostProdType', 'A94IntCIF', 'A94ExtCIF', 'A94Access', 'A94NickName', 'A95IntNum', 'A95ExtNum', 'A95HostProdType', 'A95IntCIF', 'A95ExtCIF', 'A95Access', 'A95NickName', 'A96IntNum', 'A96ExtNum', 'A96HostProdType', 'A96IntCIF', 'A96ExtCIF', 'A96Access', 'A96NickName', 'A97IntNum', 'A97ExtNum', 'A97HostProdType', 'A97IntCIF', 'A97ExtCIF', 'A97Access', 'A97NickName', 'A98IntNum', 'A98ExtNum', 'A98HostProdType', 'A98IntCIF', 'A98ExtCIF', 'A98Access', 'A98NickName', 'A99IntNum', 'A99ExtNum', 'A99HostProdType', 'A99IntCIF', 'A99ExtCIF', 'A99Access', 'A99NickName', 'A100IntNum', 'A100ExtNum', 'A100HostProdType', 'A100IntCIF', 'A100ExtCIF', 'A100Access', 'A100NickName']
  70. cust_user_dict = {}
  71. account_status_dict = {}
  72. password_dict = {}
  73. comm_username_map = {}
  74. account_ids = {}
  75. retail_list = []
  76. company_list = []
  77. commuser_list = []
  78.  
  79. aba = '111301122'
  80. branch_ids = ('4','14','15','44')
  81.  
  82. for line in comm_username_map_file:
  83. if line[2] = aba:
  84. comm_username_map[line[1].lower()] = line[0]
  85.  
  86. del comm_username_map_file
  87.  
  88. # Get passwords for users
  89. for line in passwords:
  90. password_dict[line[0]] = line[1]
  91.  
  92. del passwords
  93.  
  94. # Create accountnumber/hostaccountid map
  95. for line in account_id_file:
  96. account_ids[line[0]] = line[1]
  97.  
  98. del account_id_file
  99.  
  100. # Using branch ids, pull only customer ids that are relevant to the current conversion
  101. for line in groups:
  102. if line[2] in branch_ids:
  103. cust_user_dict[line[1]] = {}
  104.  
  105. del groups
  106.  
  107. # Create dict of accounts and their status
  108. for line in accounts:
  109. account_status_dict[(line[2],line[1])] = line[5]
  110.  
  111. del accounts
  112.  
  113. # Pull retail customers
  114. for line in retail_users:
  115. if cust_user_dict.has_key(line[0]):
  116. cust_user_dict[line[0]] = {
  117. 'is_company':'False',
  118. 'customer_name':re.sub(nameregex,'',line[2].strip() + ' ' + line[1].strip()),
  119. 'first_name':re.sub(nameregex,'',line[2].strip()),
  120. 'last_name':re.sub(nameregex,'',line[1].strip()),
  121. 'tax_id':'',
  122. 'ssn':line[4].strip(),
  123. 'accounts':[],
  124. 'phone':[],
  125. 'address_type':'Business',
  126. 'group_id':'1'
  127. }
  128.  
  129. del retail_users
  130.  
  131. # Pull companies
  132. for line in companies:
  133. if cust_user_dict.has_key(line[0]):
  134. cust_user_dict[line[0]] = {
  135. 'is_company':'True',
  136. 'customer_name':re.sub(nameregex,'',line[1].strip()),
  137. 'tax_id':line[2].strip(),
  138. 'users':{},
  139. 'accounts':[],
  140. 'phone':[],
  141. 'address_type':'Business',
  142. 'group_id':'8'
  143. }
  144.  
  145. del companies
  146.  
  147. #Pull commercial users
  148. for line in comm_users:
  149. cr_id = line[1].lower()
  150. if comm_username_map.has_key(cr_id):
  151. username = comm_username_map[cr_id]
  152. else:
  153. username = cr_id
  154.  
  155. if re.match(emailregex,line[9].strip()) != None:
  156. email = line[9].strip()
  157. else:
  158. email = ''
  159.  
  160. if cust_user_dict.has_key(line[0]):
  161. try:
  162. password = password_dict[username]
  163. except:
  164. password = 'P@55w0rd!'
  165. cust_user_dict[line[0]]['users'][line[1].strip()] = {
  166. 'username':username,
  167. 'first_name':re.sub(nameregex,'',line[2].strip()),
  168. 'last_name':re.sub(nameregex,'',line[3].strip()),
  169. 'email': email,
  170. 'password':password,
  171. 'accounts':[]
  172. }
  173.  
  174. del comm_users
  175.  
  176. # Get addresses for all customers
  177. for line in addresses:
  178. if cust_user_dict.has_key(line[0]):
  179. cust_user_dict[line[0]]['address_1'] = re.sub(nameregex,'',line[4].strip())
  180. cust_user_dict[line[0]]['address_2'] = re.sub(nameregex,'',line[5].strip())
  181. cust_user_dict[line[0]]['city'] = re.sub(nameregex,'',line[6].strip())
  182. cust_user_dict[line[0]]['CIF'] = line[3][-7:].strip().upper()
  183. if line[7] in valid_states:
  184. cust_user_dict[line[0]]['state'] = line[7].strip()
  185. else:
  186. cust_user_dict[line[0]]['state'] = 'TX'
  187. cust_user_dict[line[0]]['zip'] = re.sub(nameregex,'',line[8].strip()[:5])
  188.  
  189. del addresses
  190.  
  191. # Get login ids for retail customers
  192. for line in user_login:
  193. username = line[2].lower()
  194. if cust_user_dict.has_key(line[1]):
  195. try:
  196. password = password_dict[line[2].lower()]
  197. except:
  198. password = 'P@55w0rd!'
  199. if cust_user_dict[line[1]]['is_company'] == True:
  200. cust_user_dict[line[1]]['users'][line[2].strip()] = {
  201. 'username':re.sub(nameregex,'',username),
  202. 'first_name':' ',
  203. 'last_name':' ',
  204. 'password':password,
  205. 'email': re.sub(nameregex,'',line[17].strip()),
  206. 'accounts':[]
  207. }
  208. else:
  209. cust_user_dict[line[1]]['username'] = re.sub(nameregex,'',line[2].strip())
  210. cust_user_dict[line[1]]['password'] = password
  211. cust_user_dict[line[1]]['email'] = re.sub(nameregex,'',line[17].strip())
  212.  
  213. del user_login
  214.  
  215. # Get phone numbers for all customers
  216. for line in phone_numbers:
  217. if cust_user_dict.has_key(line[1]):
  218. temp = line[0].strip().replace(' ','')
  219. phone_number = re.sub(phoneregex,'',temp)
  220. type = line[2]
  221. if len(phone_number) >= 10:
  222. cust_user_dict[line[1]]['phone'].append([phone_number[:3],phone_number[3:10],phone_map[type]])
  223.  
  224.  
  225.  
  226. # Pull and associate user accounts
  227. for line in user_accounts:
  228. if cust_user_dict.has_key(line[3]):
  229. if account_status_dict[(line[2],line[1])] == 'Open':
  230. if account_ids.has_key(line[2]):
  231. cust_user_dict[line[3]]['accounts'].append([account_ids[line[2]],line[2],acct_map[line[1]],re.sub(nameregex,'',line[10].strip())])
  232. else:
  233. pass
  234. else:
  235. pass
  236.  
  237. del user_accounts
  238.  
  239. # Get commercial user account associations
  240. for line in comm_accounts:
  241. if cust_user_dict.has_key(line[4].strip()):
  242. acct_rights = 0
  243. for i in (5,6,7):
  244. acct_rights = acct_rights + rights_map[i][line[i]]
  245. if account_ids.has_key(line[2]):
  246. cust_user_dict[line[4].strip()]['users'][line[3].strip()]['accounts'].append([account_ids[line[2]],line[2],acct_map[line[1]],str(acct_rights)])
  247.  
  248. query_list = []
  249.  
  250. for key in cust_user_dict:
  251. if cust_user_dict[key]['customer_name'] not in import_exceptions:
  252. cust_user_dict[key]['phone'] = cust_user_dict[key]['phone'][:3]
  253. if cust_user_dict[key]['is_company'] == 'False':
  254. print 'RETAIL USER'
  255. print '--------------------'
  256. print cust_user_dict[key]
  257. customer = certegy.mk_api_customer(cust_user_dict[key], key, cust_user_dict[key]['phone'])
  258. customer_return = db.execute("EXEC dbo.apispAddCustomer @customerData ='" + customer.flatten_self() + "'").fetchall()[0][0]
  259. customer_id = certegy.parse_api_customer_return(customer_return)
  260.  
  261. if customer_id[0] != False:
  262. print 'Customer ID: ' + customer_id
  263. user = certegy.mk_api_ret_user(cust_user_dict[key],customer_id,key)
  264. user_return = db.execute("EXEC dbo.apispAddUser @userData ='" + user.flatten_self() + "'").fetchall()[0][0]
  265. user_id = certegy.parse_api_user_return(user_return)
  266. if user_id[0] != False:
  267. print 'User ID: ' + user_id
  268.  
  269. userlogon = certegy.mk_api_userlogon(cust_user_dict[key], user_id)
  270. userlogon_return = db.execute("EXEC dbo.apispAddUserLogon @userLogonData ='" + userlogon.flatten_self() + "'").fetchall()[0][0]
  271. certegy.parse_api_userlogon_return(userlogon_return)
  272.  
  273. print 'Adding CIF Relationship for Customer/User:'
  274. cif_rel = certegy.mk_api_cif_association(cust_user_dict[key]['CIF'],customer_id,user_id)
  275. print cif_rel.flatten_self()
  276. cust_acct_return = db.execute("EXEC dbo.apispAddAccountAssociation @accountAssociationData ='" + cif_rel.flatten_self() + "'").fetchall()[0][0]
  277. print cust_acct_return
  278. print '------------------------'
  279.  
  280. for record in cust_user_dict[key]['accounts']:
  281. cust_acct = certegy.mk_api_cust_acct(record, customer_id)
  282. cust_acct_return = db.execute("EXEC dbo.apispAddAccountAssociation @accountAssociationData ='" + cust_acct.flatten_self() + "'").fetchall()[0][0]
  283. try:
  284. cust_acct_id = certegy.parse_api_cust_acct_return(cust_acct_return)
  285. except:
  286. cust_acct_id = (False,'')
  287. if cust_acct_id[0] != False:
  288. print 'Customer Account ID: ' + cust_acct_id
  289. user_acct = certegy.mk_api_user_acct(record, user_id, cust_acct_id, '7')
  290. user_acct_return = db.execute("EXEC dbo.apispAddAccountAssociation @accountAssociationData ='" + user_acct.flatten_self() + "'").fetchall()[0][0]
  291. nickname_query = "INSERT INTO q2_UserPropertyData (UISourceID,UserID,HostAccountID,PropertyID,PropertyValue) VALUES ('1','"+user_id+"','"+record[0]+"','2','"+record[3]+"')"
  292. print nickname_query
  293. nickname_return = db.execute(nickname_query)
  294. print '--------------'
  295. print 'Account Nickname Status:'
  296. print nickname_return
  297. certegy.parse_api_user_acct_return(user_acct_return)
  298. else:
  299. print 'FAILED IMPORT (CUSTOMER ACCOUNT):'
  300. print '----------------'
  301. print cust_acct
  302. print 'ERROR RETURN:'
  303. print cust_acct_id[1]
  304.  
  305. else:
  306. print 'FAILED IMPORT (USER):'
  307. print '----------------'
  308. print user
  309. print 'ERROR RETURN:'
  310. print user_id[1]
  311.  
  312. else:
  313. print 'FAILED IMPORT (CUSTOMER):'
  314. print '----------------'
  315. print customer
  316. print 'ERROR RETURN:'
  317. print customer_id[1]
  318.  
  319. else:
  320. customer_accounts = {}
  321. print 'COMMERCIAL USER'
  322. print '--------------------'
  323. print cust_user_dict[key]
  324. customer = certegy.mk_api_customer(cust_user_dict[key], key, cust_user_dict[key]['phone'])
  325. customer_return = db.execute("EXEC dbo.apispAddCustomer @customerData ='" + customer.flatten_self() + "'").fetchall()[0][0]
  326. customer_id = certegy.parse_api_customer_return(customer_return)
  327. if customer_id[0] != False:
  328.  
  329. print 'Customer ID: ' + customer_id
  330. for record in cust_user_dict[key]['accounts']:
  331. cust_acct = certegy.mk_api_cust_acct(record, customer_id)
  332. cust_acct_return = db.execute("EXEC dbo.apispAddAccountAssociation @accountAssociationData ='" + cust_acct.flatten_self() + "'").fetchall()[0][0]
  333. try:
  334. cust_acct_id = certegy.parse_api_cust_acct_return(cust_acct_return)
  335. except:
  336. cust_acct_id = (False,'')
  337. if cust_acct_id[0] != False:
  338. print 'Customer Account ID: ' + cust_acct_id
  339. customer_accounts[record[1]] = cust_acct_id
  340. else:
  341. print 'FAILED IMPORT (CUSTOMER ACCOUNT):'
  342. print '----------------'
  343. print cust_acct_id
  344. print 'ERROR RETURN:'
  345. print cust_acct_id[1]
  346.  
  347. for userid in cust_user_dict[key]['users']:
  348. user = certegy.mk_api_comm_user(cust_user_dict[key],cust_user_dict[key]['users'][userid],customer_id,key)
  349. user_return = db.execute("EXEC dbo.apispAddUser @userData ='" + user.flatten_self() + "'").fetchall()[0][0]
  350. user_id = certegy.parse_api_user_return(user_return)
  351. if user_id[0] != False:
  352. print 'User ID: ' + user_id
  353.  
  354. userlogon = certegy.mk_api_userlogon(cust_user_dict[key]['users'][userid], user_id)
  355. userlogon_return = db.execute("EXEC dbo.apispAddUserLogon @userLogonData ='" + userlogon.flatten_self() + "'").fetchall()[0][0]
  356. certegy.parse_api_userlogon_return(userlogon_return)
  357.  
  358. for record in cust_user_dict[key]['users'][userid]['accounts']:
  359. try:
  360. cust_acct_id = customer_accounts[record[1]]
  361. except:
  362. print 'FAILED IMPORT (CUSTOMER ACCOUNT):'
  363. print '----------------'
  364. print 'NO CORRESPONDING CUSTOMER ACCOUNT RECORD FOR ' + record[1]
  365. cust_acct_id = (False,'')
  366. if cust_acct_id[0] != False:
  367. print 'Customer Account ID: ' + cust_acct_id
  368.  
  369. user_acct = certegy.mk_api_user_acct(record, user_id, cust_acct_id, record[3])
  370. user_acct_return = db.execute("EXEC dbo.apispAddAccountAssociation @accountAssociationData ='" + user_acct.flatten_self() + "'").fetchall()[0][0]
  371. certegy.parse_api_user_acct_return(user_acct_return)
  372. else:
  373. print 'FAILED IMPORT (CUSTOMER ACCOUNT):'
  374. print '----------------'
  375. print cust_acct_id
  376. print 'ERROR RETURN:'
  377. print cust_acct_id[1]
  378.  
  379. else:
  380. print 'FAILED IMPORT (USER):'
  381. print '----------------'
  382. print user
  383. print 'ERROR RETURN:'
  384. print user_id[1]
  385.  
  386. else:
  387. print 'FAILED IMPORT (CUSTOMER):'
  388. print '----------------'
  389. print customer
  390. print 'ERROR RETURN:'
  391. print customer_id[1]
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!