constoriginTableName='Attendance table';constoriginViewName='Default view';constoriginNameColumnName='Name';constoriginDepartmentColumnName='Department';constoriginDateColumnName='Date';constoriginTimeColumnName='Attendance time';consttargetTableName='Statistics attendance';consttargetNameColumnName='Name';consttargetDepartmentColumnName='Department';consttargetDateColumnName='Date';consttargetStartTimeColumnName='Clock-In';consttargetEndTimeColumnName='Clock-Out';consttargetTable=base.getTableByName(targetTableName);consttable=base.getTableByName(originTableName);constview=base.getViewByName(table,originViewName);constrows=base.getRows(table,view);// sort the rows in the table according to the date column;rows.sort((row1,row2)=>{if(row1[originDateColumnName]<row2[originDateColumnName]){return-1;}elseif(row1[originDateColumnName]>row2[originDateColumnName]){return1;}else{return0;}});/* group all rows via date and save them to groupedRows, the format of the object is {'2020-09-01': [row, ...], '2020-09-02': [row, ...]}*/constgroupedRows={};rows.forEach((row)=>{constdate=row[originDateColumnName];if(!groupedRows[date]){groupedRows[date]=[];}groupedRows[date].push(row);});constdateKeys=Object.keys(groupedRows);// traverse all the groups in groupedRowsdateKeys.forEach((dateKey)=>{// get all attendance data of all members on the current dateconstdateRows=groupedRows[dateKey];conststaffDateStatItem={};// traverse these rows of data and group by the name of the employee, get the clock-in and clock-out time of each employee that day, and save it to staffDateStatItem// the format is { a1: {Name: 'a1', Date: '2020-09-01', Clock-In: '08:00', Clock-Out: '18:00'},... }dateRows.forEach((row)=>{constname=row[originNameColumnName];if(!staffDateStatItem[name]){// Generate a new row based on the original row data, and add Clock-In and Clock-Out columns in the newly generated rowstaffDateStatItem[name]={[targetNameColumnName]:name,[targetDateColumnName]:row[originDateColumnName],[targetDepartmentColumnName]:row[originDepartmentColumnName],[targetEndTimeColumnName]:row[originTimeColumnName],[targetStartTimeColumnName]:row[originTimeColumnName]};}else{// when the column name of the row is repeated, compare the time, choose the largest one as the Clock-Out time, and the smallest one as the Clock-In timeconsttime=row[originTimeColumnName];conststaffItem=staffDateStatItem[name];if(compareTime(staffItem[targetStartTimeColumnName],time)){staffItem[targetStartTimeColumnName]=time;}elseif(compareTime(time,staffItem[targetEndTimeColumnName])){staffItem[targetEndTimeColumnName]=time;}}});// write the attendance data of all employees on the current date into the tableObject.keys(staffDateStatItem).forEach((name)=>{base.addRow(targetTable,staffDateStatItem[name]);});});// compare the size of two string format timefunctioncompareTime(time1,time2){constt1=time1.split(':');constt2=time2.split(':');if(parseInt(t1[0])>parseInt(t2[0])){returntrue;}elseif(parseInt(t1[0])<parseInt(t2[0])){returnfalse;}elseif(parseInt(t1[0])==parseInt(t2[0])){if(parseInt(t1[1])>parseInt(t2[1])){returntrue;}else{returnfalse;}}}