SHARE
TWEET

Untitled

a guest Apr 25th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. OnExit, CleanUp ; Runs the specified subroutine when the script closes
  2. #SingleInstance Force
  3. setbatchlines, -1
  4.  
  5. SetWorkingDir %A_ScriptDir%
  6. #include %A_ScriptDir%\Includes\XL.ahk
  7. #include %A_ScriptDir%\Includes\pArr.ahk
  8.  
  9. ; ==================================================
  10. ; GLOBAL VARIABLES
  11. ; ==================================================
  12.  
  13. GLOBAL      cWB                 ;ActiveX Control for the HTML GUI
  14. global      pWB
  15.  
  16. xl := XL_Check("Change-2019_25_03.xlsx - Excel")
  17. If(!isobject(xl))
  18.     xl := Load(A_ScriptDir "\Change-2019_25_03.xlsx")
  19.  
  20. ; Excel is invisible by default. Once you have tested the script this line can be removed.
  21. ;Clients_List := "ALL||SFR|SFR+BENELUX|SFR+SFF+BENELUX|SFR+SFF|SFR+SFF+MA|SFR+SFF+SIT|SFR+MA|MA+SIT|MA|SIT|BENELUX" ; Will fill the DropDownList with clients IDs
  22.  
  23. Sheet_Name := "Change" ; Sheet name, may change in the future, depends on the extracted Excel file
  24. App_Name := "CMC Toolbox"
  25. Headers_Offset := 10
  26.  
  27.  
  28. ;Get the length of headers to loop through - im assuming headers start at A1 - and are always in Row 1
  29. HeaderColumns := xl.sheets(Sheet_Name).range("A1").end(xlright)["column"]
  30. Rows := xl.sheets(Sheet_Name).range("A1").end(xldown)["Row"]
  31. LastCol := ConverttoLetter(HeaderColumns)
  32.  
  33. ;Function to remove all duplicates from a range in Excel
  34. XL_RemoveDuplicates(xl,Sheet_Name,"A1:" LastCol Rows,1)
  35.  
  36. ; ==================================================
  37. ; GUI
  38. ; ==================================================
  39. Gui, Main:New
  40. Gui, Main:+OwnDialogs
  41. Gui, Main:Margin,5,5
  42. Gui, Main:Add, ActiveX, vcWB w1340 h325 -Caption, Shell.Explorer
  43.  
  44. ;Display HTML Page and Attach Event Listeners
  45. cWB.silent := 1
  46. fileread,html, % A_ScriptDir "\HTML\Base.html"
  47. Display(cWB,HTML)
  48.  
  49. BuildGui(xl,Sheet_name)
  50.  
  51. Gui,Main:Show,Autosize
  52. cWB.document.parentWindow.ahk_Hoverin := Func("hoverTitlein")
  53. cWB.document.parentWindow.ahk_Hoverout := Func("hoverTitleout")
  54. cWB.document.parentWindow.ahk_changeNumClick := Func("ChangeNumClick")
  55. cWB.document.parentWindow.ahk_submitDateFilter := Func("DateFilter")
  56. cWB.document.parentWindow.ahk_submitOutlook := Func("ItemstoOutlook")
  57. pWB.Navigate("http://servicecenter.scania.com")
  58. return
  59.  
  60. ; ==================================================
  61. ; LABELS
  62. ; ==================================================
  63.  
  64. Load(path) {
  65.     ;xl := ComObjCreate("Excel.Application")
  66.     ;xl.Visible := true
  67.    
  68.     Run, % path
  69.     winwait,Change-2019_25_03.xlsx - Excel
  70.     xl := XL_Check("Change-2019_25_03.xlsx - Excel")
  71.    
  72.     ;MyWorkbook := xl.Workbooks.Open(path) ; Open the file in Excel
  73.     Return % xl
  74. }
  75.  
  76.  
  77. ; ==================================================
  78. ; HOTKEYS
  79. ; ==================================================
  80. ESC::
  81. Cleanup:
  82. GuiEscape:
  83. MainGuiClose:
  84. try{
  85.     pWB.quit
  86.     pWB:=
  87. }
  88.     ExitApp
  89. return
  90.  
  91. ; ==================================================
  92. ; FUNCTIONS
  93. ; ==================================================
  94.  
  95. ItemsToOutlook()
  96. {
  97.     TableRows := cWB.document.querySelectorAll("#dataTable1x tbody tr")
  98.     Loop % TableRows.Length
  99.     {
  100.         RowChecked := TableRows[A_Index-1].getElementsByClassName("include")[0].checked
  101.         IF(RowChecked = -1)
  102.         {
  103.             Row := TableRows[A_Index-1]
  104.             Subject := Row.getElementsByClassName("title")[0].value
  105.             Clients := Row.getElementsByClassName("clients")[0] .value     
  106.             ChangeNum := Row.getElementsByClassName("changenum")[0] .value 
  107.             changetype := Row.getElementsByClassName("changetype")[0].value    
  108.             mainaltered := Row.getElementsByClassName("mainaltered")[0] .value 
  109.             start := Row.getElementsByClassName("plannedstart")[0].value   
  110.             RegExMatch(Start, "(\d+)/(\d+)/(\d+)", part)
  111.             cStart := part3 "/" part2 "/" part1
  112.             body := "Clients: " clients "`nChange Number: " changenum "`nChange Type: " changetype "`n Main Altered CI: " mainaltered
  113.             createAppointment(Subject,Body,cStart,cstart)
  114.         }
  115.     }
  116. }
  117.  
  118. createAppointment(pSubject,pBody,pStart,pEnd){
  119.     olMailItem := 1 ; 1 for Calendar Item 0 for Mail item
  120.     Outlook := comobjactive("outlook.application").CreateItem(olMailItem)
  121.     if !IsObject(OutLook)
  122.         OutLook := ComObjCreate("outlook.application").CreateItem(olMailItem)
  123.    
  124.     OutLook.subject := pSubject
  125.     Outlook.body := pBody
  126.     Outlook.Start := pStart " 12:00:00 PM"
  127.     Outlook.AllDayEvent := 1
  128.     Outlook.save
  129. }
  130.  
  131. BuildGui(xl,Sheet_Name) {
  132.     Static Classlist = {0:"title",1:"changenum",2:"changetype",3:"plannedstart",4:"plannedend",5:"mainaltered"}
  133.     Not_Wanted_Columns := "C,E,H,I,J,K,M,N,O,P,Q"
  134.     fileread,RawRow, % A_ScriptDir "\HTML\RawRow.txt"
  135.     ;Get Total # of rows again, after we remove duplicate rows
  136.     HeaderColumns := xl.sheets(Sheet_Name).range("A1").end(xlright)["column"]
  137.     LastCol := ConverttoLetter(HeaderColumns)
  138.     Rows := xl.sheets(Sheet_Name).range("A1").end(xldown)["Row"]
  139.     Loop % Rows
  140.     {
  141.         Table := cwb.document.getElementById("dataTable1x").getElementsByTagName("tbody")[0]
  142.  
  143.         RowIdx := A_Index
  144.         If(A_Index = 1)
  145.             continue ;Skip Header Row in Excel - we hardcoded it in the html
  146.  
  147.         tableRow := cwb.Document.createElement("tr")
  148.         tableRow.innerhtml := RawRow
  149.         table.appendChild(tableRow)
  150.            
  151.         CurrentRow := XL_RangeToObj(xl,Sheet_Name,"A" A_Index ":" LastCol A_Index,"Value")
  152.         i=0
  153.         for Column, Value in CurrentRow
  154.         {
  155.             list := Not_Wanted_Columns ;list of unwanted columns
  156.             If Column in %list%
  157.                 continue
  158.             Cell := Table.querySelector("tr:nth-child(" RowIdx-1 ")").getElementsByClassName(ClassList[i])[0]
  159.             Cell.value := Value[RowIdx]
  160.             if(i=1){
  161.                 str := "ahk_changeNumClick(this," RowIdx-1 ")"
  162.                 Cell.setAttribute("onClick",str)
  163.             }
  164.             i++
  165.         }
  166.     }  
  167. }
  168.  
  169. DateFilter() {
  170.     Start := cWB.document.getElementById("sDate").value
  171.     End := cWB.document.getElementById("eDate").value
  172.     RegExMatch(Start, "(\d+)-(\d+)-(\d+)", part)
  173.     cStart := part1  part3  part2 0000000
  174.     RegExMatch(End, "(\d+)-(\d+)-(\d+)", part) 
  175.     cEnd := part1  part3  part2 0000000
  176.    
  177.    
  178.     TableRows := cWB.document.querySelectorAll("#dataTable1x tbody tr")
  179.     Loop % TableRows.Length
  180.     {
  181.         DateStart := TableRows[A_Index-1].getElementsByClassName("plannedstart")[0].value
  182.         RegExMatch(DateStart, "(\d+)/(\d+)/(\d+)", part)   
  183.         dStart := part1  part3  part2 0000000
  184.        
  185.         If((dStart >= cStart) AND (dStart <= cEnd)) OR ((cStart="0000000") AND (cEnd="0000000"))
  186.             TableRows[A_Index-1].style.display := "table-row"
  187.         else
  188.             TableRows[A_Index-1].style.display := "none"
  189.     }
  190.    
  191. }
  192.  
  193. hoverTitlein(t){
  194.     tooltip % t.innerText
  195. }
  196. hoverTitleout(t){
  197.     tooltip,
  198. }
  199.  
  200. ChangeNumClick(t,idk)
  201. {
  202.     Row := cWB.document.getElementById("dataTable1x").querySelector("tbody tr:nth-of-type(" idk ")")
  203.     change_num := Row.getElementsByClassName("changenum")[0].value
  204.     Change_URL := "http://servicecenter.vw.vwg/jctsc/smp/index.do?ctx=docEngine&file=cm3r&query=header%2Cnumber%3D%22" Change_Num "%22&action=&title=Change%20" Change_Num
  205.     msgbox % change_url "`n Now Browsing to GOOGLE! PLEASE!"
  206.     pWB.Navigate("www.google.com")
  207.     return
  208. }
  209.  
  210. Display(WB,html_str) {
  211.     Count:=0
  212.     while % FileExist(f:=A_Temp "\" A_TickCount A_NowUTC "-tmp" Count ".DELETEME.html")
  213.         Count+=1
  214.     FileAppend,%html_str%,%f%
  215.     WB.Navigate("file://" . f)
  216. }
RAW Paste Data
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
 
Top