Welcome, Guest. Please Login or Register.
eccoMAGIC Forums
07/21/24 at 20:31:12
Home Help Search Login Register


Pages: 1
Send Topic Print
  Rating
Rate:
Rating:
Rating: 0.00
Votes: 0

5
4
3
2
1












5
4
3
2
1


1


2


3


4


5


6


7


8


9


10

Job Cost Data Extraction (Popularity: 2945 )
albertschepers
Contributing  Member
***


I Love Ecco!

Posts: 11
Gender: male
Show the link to this post Job Cost Data Extraction
12/21/07 at 17:03:45
 
Not complete as yet but it does work well.  The intent is to be able to import the ecco data into a database for invoiceing and payroll.  I do not think that the formating (indents of the conditionals and loops) prints properly but...
 
 
In the luacmd.lua file there are several of these scripts, one for each "name", one for each employee file as there is no way f passing the folder name to teh lua script.  The path that I set is to a common folder on the server "p:\common".  Those are the only two changes that are made for each function.  The admin staff opens each emplyee's file and extracts data weekly so that it can be consolidated into a data base where it will be processed for invoicing.
 
 
I will post the default emplyee file wth instructions on how it is used along with the pop up list info just in case anyone is interested.
 
 
I hope that those who read this find it of iterest.
 
 
Albert
 
 
-- LUA script to extract data from ecco formated specifically for job costing
--  
-- version 1.0
-- December 8, 2007
-- Albert Schepers
--
-- Change the "Name" to suit the employee for whom data is being extracted
-- set "Path" to the directory in which the data is to be stored  
--
function JobCostName()                              -- function name note the () at the end of the name
name="Name"                                    -- employee name
folder="Path"                                    -- set the directory into which the data file will be placed
file=folder..name..".txt"                              -- create a txt file name from the folder and emplyee name
io.output(file,"w+")                                    -- open an empty file for output if file exists it will be emptied, this is done early to ensure an empty file should the extraction fail
NumberItems=get_select_items()                        -- get the item ids of all selected items
MaxItems=table.maxn(NumberItems)                        -- determine the total number of selected items
if get_folder_value("Appointments",NumberItems[1])=="" then
  msgbox("Invalid Selection\nNot a TLI (no Appointment date)","error 1")
else -- line 8
  if MaxItems > 0 then                              -- A checks to see if there are one or more items
    h1="Date"                                    -- all the hn variables are the header row for teh output data
    h2=",".."Start Time"
    h3=",".."End Time"
    h4=",".."Employee Name"
    h5=",".."Project Number"
    h6=",".."Work Description"
    h7=",".."Regular Hours"
    h8=",".."Overtime 1"
    h9=",".."Overtime 2"
    h10=",".."Mileage"
    h11=",".."Expense Amount"
    h12=",".."Expense Type"
    h13=",".."Equipment Time"
    h14=",".."Equipment Description"
    h15=",".."Consumable Quantity"
    h16=",".."Consumable Description"
    h17=",".."Reimbursable Amount"
    h18=",".."Reimbursible Description"
    header=h1..h2..h3..h4..h5..h6..h7..h8..h9..h10..h11..h12..h13..h14..h15..h16..h1
7..h18.."\n"
    io.write(header)                                    -- store the header row in the open data file
    ctr1=1                                          -- set the counter to 1 for the while loop
    while ctr1 <= MaxItems do
     datetime=get_folder_value("Appointments",NumberItems[ctr1])
     if datetime~="" then                              -- check to see if the date has no value and if true set variables for following lines
       date=tonumber(string.sub(datetime,1,8))
       starttime=tonumber(string.sub(datetime,9,12))
       stoptime=tonumber(string.sub(datetime,14,17))
       project=get_folder_value("Project",NumberItems[ctr1])
     else -- line 35                                    -- if it has no value then set the start and stop times to null
       stoptime=""
       starttime=""
     end -- line 35
     text=get_item_text(NumberItems[ctr1])
     if string.sub(text,1,1)=="#" or string.sub(text,1,1)=="$" then            -- get rid of the front end of the text if it starts with #
       if string.sub(text,1,3)=="#m " or string.sub(text,1,3)=="#M " then
         text=text.." Mileage"                              -- add "Mileage" to the string if it represents mileage (#m)
       end  -- line 46
       text=string.match(text,"%s%a\.*")                        -- regex expression that replaces the text with the end of the string
     end  -- line 45
     ptr1=ctr1                                    -- set pointer to current item
     j=ctr1
     while j<MaxItems do                              -- do loop to concatenate text based on the next item text not starting with a #
       ctr1=j
       nexttext=get_item_text(NumberItems[j+1])
       nextdate=get_folder_value("Appointments",NumberItems[j+1])
       first = string.sub(nexttext,1,1)
       if first =="#"  or first =="$" then
         j=MaxItems
       else
         if nextdate=="" then
           text=text.." "..nexttext
         else -- line 58
           j=MaxItems                                    -- exit the loop pas the next item is a TLI, the Appoinments folder has value
         end -- line 58
       end -- line 57
       j=j+1                                          -- increment the cocatenation loop (j)
     end -- line 51
     f1='"'..date..'"'
     f2=',"'..starttime..'"'
     f3=',"'..stoptime..'"'
     f4=',"'..name..'"'
     f5=',"'..project..'"'
     f6=',"'..text..'"'
     f7=','..get_folder_value("Reg",NumberItems[ptr1])
     f8=','..get_folder_value("OT1",NumberItems[ptr1])
     f9=','..get_folder_value("OT2",NumberItems[ptr1])
     f10=','..get_folder_value("Mileage",NumberItems[ptr1])
     f11=','..get_folder_value("Expense Amount",NumberItems[ptr1])
     f12=',"'..get_folder_value("Expense Type",NumberItems[ptr1])..'"'
     f13=',"'..get_folder_value("Equipment Time",NumberItems[ptr1])..'"'
     f14=',"'..get_folder_value("Equipment Description",NumberItems[ptr1])..'"'
     f15=','..get_folder_value("Consumable",NumberItems[ptr1])
     f16=',"'..get_folder_value("Consumable Description",NumberItems[ptr1])..'"'
     f17=','..get_folder_value("Reimbursable",NumberItems[ptr1])
     f18=',"'..get_folder_value("Reimbursable Description",NumberItems[ptr1])..'"'
     data=f1..f2..f3..f4..f5..f6..f7..f8..f9..f10..f11..f12..f13..f14..f15..f16..f17.
.f18.."\n"
     io.write(data)
     ctr1=ctr1+1                                          -- increment the counter
    end -- line 33                                          -- end of the while loop
    io.close()
  else -- line 11
    msgbox("No items selected","error 2")
  end -- line 11
end -- line 8
msgbox("Click on enter to finish","Last Message")
end -- line 1                                          function end
 
Back to top
 
 

Albert Schepers
Email WWW   IP Logged
Pages: 1
Send Topic Print