static void TimesheetJob(Args _args)
{
TSTimesheetTable tsTimesheetTable;
TSTimesheetLine newTimesheetLine, existsTimesheetLine;
ProjPeriodLine projPeriodLine;
HcmWorker worker;
ProjPeriodTimesheetWeek projPeriodTimesheetWeek, periodWeek;
TSTimesheetLineWeek lineWeek, existsLineWeek;
DialogButton dlgButton;
Dialog dialog;
DialogField dfFileName;
//To filter the files while selecting
container conFilter;
FileIoPermission perm;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Filename fileName;
int row;
HcmPersonnelNumberId personnelnumber;
ProjId projId;
smmActivityNumber activityNumber;
TransDate periodFrom, periodTo;
Hours hourMon, hourTue, hourWed, hourThr, hourFri, hourSat, hourSun;
TSWeeklyHours weeklyHours;
INT i;
#File
#define.FileMode('R')
//flag = 0;
startLengthyOperation();
conFilter = ["@PM527" ,"@PM528"];
dialog = new Dialog("Auto creation of timesheet");
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(conFilter);
// dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
dialog.filenameLookupTitle("@PM529");
dialog.caption("@PM530");
if (dialog.run())
{
fileName = dfFileName.value();
perm = new FileIOPermission(fileName, #FileMode);
perm.assert();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("@SYS19358");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
try
{
do
{
row++;
tsTimesheetTable.clear();
newTimesheetLine.clear();
existsTimesheetLine.clear();
worker.clear();
projPeriodLine.clear();
projPeriodTimesheetWeek.clear();
lineWeek.clear();
personnelnumber = cells.item(row, 1).value().bStr();
periodFrom = cells.item(row, 2).value().date();
periodTo = cells.item(row,3).value().date();
projId = cells.item(row,4).value().bStr();
activitynumber = cells.item(row,5).value().bStr();
hourMon = cells.item(row,6).value().double();
hourTue = cells.item(row,7).value().double();
hourWed = cells.item(row,8).value().double();
hourThr = cells.item(row,9).value().double();
hourFri = cells.item(row, 10).value().double();
hourSat = cells.item(row, 11).value().double();
hourSun = cells.item(row, 12).value().double();
if(personnelnumber != "Employee Id")
{
info(strFmt("personnelnumber -%1",personnelnumber));
worker = HcmWorker::findByPersonnelNumber(personnelnumber);
if (!worker.RecId)
{
throw error("@SYS304951");
}
projPeriodLine = TSTimesheetTable::getValidWorkerPeriod(worker.RecId, periodFrom);
// Check if the user has not exceeded the timesheet limit
if (TSTimesheetTable::checkMaxTimesheets(projPeriodLine.PeriodFrom, worker.RecId, true))
{
tsTimesheetTable.initValue();
tsTimesheetTable.Worker = worker.RecId;
if(projPeriodLine != null && projPeriodLine.RecId != 0)
{
tsTimesheetTable.ProjPeriodId = projPeriodLine.PeriodId;
tsTimesheetTable.PeriodFrom = projPeriodLine.PeriodFrom;
tsTimesheetTable.PeriodTo = projPeriodLine.PeriodTo;
projPeriodTimesheetWeek = ProjPeriodTimesheetWeek::findFromPeriod(projPeriodLine.PeriodId, periodFrom);
if(projPeriodTimesheetWeek.RecId == 0)
{
warning("@SYS338882");
}
else
{
tsTimesheetTable.ProjPeriodTimesheetWeek = projPeriodTimesheetWeek.RecId;
}
}
select forUpdate existsTimesheetLine
order by RecId desc
where existsTimesheetLine.Worker == worker.RecId
&& existsTimesheetLine.ProjPeriodTimesheetWeek == projPeriodTimesheetWeek.RecId;
// && existsTimesheetLine.ProjId == projId;
if(!existsTimesheetLine.RecId)// && flag == 0)
{
tsTimesheetTable.insert();
info(strFmt("created timesheet -%1",tsTimesheetTable.RecId));
// flag = 1;
if (ProjParameters::find().TimesheetAuditTrail)
{
TSTimesheetTableLog::createTableLog(tsTimesheetTable, '', TsTimesheetChangeType::Create);
}
}
else
{
tsTimesheetTable = TSTimesheetTable::find(existsTimesheetLine.TimesheetNbr);
info(strFmt("exist timesheet -%1",tsTimesheetTable.RecId));
}
if(tsTimesheetTable.RecId)
{
existsTimesheetLine.clear();
select forUpdate existsTimesheetLine
where existsTimesheetLine.TimesheetNbr == tsTimesheetTable.TimesheetNbr
&& existsTimesheetLine.Worker == worker.RecId
&& existsTimesheetLine.ProjId == projId
&& existsTimesheetLine.ActivityNumber == activityNumber
&& existsTimesheetLine.ProjPeriodTimesheetWeek == projPeriodTimesheetWeek.RecId;
newTimesheetLine.clear();
newTimesheetLine.initFromTSTimesheetTable(tsTimesheetTable);
newTimesheetLine.initFromWorkerTable(worker);
newTimesheetLine.initFromProjTable(ProjTable::find(projId));
newTimesheetLine.initValue();
newTimesheetLine.TimesheetNbr = tsTimesheetTable.TimesheetNbr;
newTimesheetLine.ApprovalStatus = TSAppStatus::Create;
newTimesheetLine.Worker = tsTimesheetTable.Worker;
newTimesheetLine.ProjId = projId;
newTimesheetLine.ActivityNumber = activityNumber;
newTimesheetLine.ProjPeriodTimesheetWeek = tsTimesheetTable.ProjPeriodTimesheetWeek;
info(strFmt("timesheet no -%1, worker -%2, proj - %3, activity numver - %4, peoj week -%5 --- %6", tsTimesheetTable.TimesheetNbr, tsTimesheetTable.Worker, projId,activityNumber, tsTimesheetTable.ProjPeriodTimesheetWeek, existsTimesheetLine.RecId));
// if(newTimesheetLine.validateWrite() && newTimesheetLine.ProjPeriodTimesheetWeek != 0)
{
if(!existsTimesheetLine.RecId)
newTimesheetLine.insert();
else
newTimesheetLine = existsTimesheetLine ;
for(i = 1; i <= 7; i++)
{
switch(i)
{
case 1:
weeklyHours[1] = hourMon;
break;
case 2:
weeklyHours[2] = hourTue;
break;
case 3:
weeklyHours[3] = hourWed;
break;
case 4:
weeklyHours[4] = hourThr;
break;
case 5:
weeklyHours[5] = hourFri;
break;
case 6:
weeklyHours[6] = hourSat;
break;
case 7:
weeklyHours[7] = hourSun;
break;
}
}
ttsbegin;
lineWeek.clear();
lineWeek.selectForUpdate(true);
lineWeek.TimesheetNbr = newTimesheetLine.TimesheetNbr;
lineWeek.LineNum = newTimesheetLine.LineNum;
lineWeek.tsTimesheetLine = newTimesheetLine.RecId;
lineWeek.Hours = weeklyHours;
while select PeriodFrom, PeriodId, PeriodTo from periodWeek
where periodWeek.RecId == newTimesheetLine.ProjPeriodTimesheetWeek
{
if (!TSTimesheetLineWeek::existByTimesheetLineRecId(newTimesheetLine.RecId))
{
lineWeek.initValue();
lineWeek.ProjPeriodId = periodWeek.PeriodId;
lineWeek.DayFrom = periodWeek.PeriodFrom;
lineWeek.DayTo = periodWeek.PeriodTo;
lineWeek.insert();
}
}
ttscommit;
info(strFmt("%1 Timesheet is created", tsTimesheetTable.TimesheetNbr));
}
}
}
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
workbooks.close();
application.quit();
}
catch (Exception::Error)
{
ttsabort;
}
}
}
