using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class RP_TimesheetImportClassDaywise extends RunBase
{
public static void main(Args _args)
{
System.IO.Stream stream;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
//TSTIMESHEETTABLE timesheetTable;
//TSTIMESHEETLINE timesheetLine;
//TSTIMESHEETLINEWEEK timesheetLineWeek;
//*****************Code added on 12/07/2023 ************************//
TSTIMESHEETTABLE timesheetTable, timesheetTableLoc;
TSTIMESHEETLINE timesheetLine, timesheetLineLoc, timesheetLineLocal;
TSTIMESHEETLINEWEEK timesheetLineWeek, timesheetLineWeekLoc, timesheetLineWeekLocal;
int identifierValue, counterIdentifierValue;
real expLines;
RecId trvExpTableRecId, trvExpLineRecId, trvExpLineLocRecId, r1, r2;
//*****************Code added on 12/07/2023 ************************//
ResourceView resourceView;
ProjPeriodId periodId;
TransDate startDate, endDate;
str timesheetNumber, resourceId, resourceLegalEntity,projectLegalEntity, projectId, categoryId, approvalStatus;
str day,hrs;
str startDateStr, endDateStr;
Dialog dialog = new Dialog('Import the data from Excel');
dlgUploadGroup = dialog.addGroup('@SYS54759');
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted('.xlsx');
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
for (i = 2; i<= rowCount; i++)
{
try
{
//*****************Code added on 12/07/2023 ************************//
identifierValue = str2Int(any2Str(range.get_Item(i, 1).value));
//*****************Code added on 12/07/2023 ************************//
timesheetNumber = any2Str(range.get_Item(i, 2).value);
resourceId = any2Str(range.get_Item(i, 3).value);
resourceLegalEntity = any2Str(range.get_Item(i, 4).value);
projectLegalEntity = any2Str(range.get_Item(i, 5).value);
projectId = any2Str(range.get_Item(i, 6).value);
categoryId = any2Str(range.get_Item(i, 7).value);
periodId = any2Str(range.get_Item(i, 8).value);
startDateStr = range.get_Item(i,9).value;
endDateStr = range.get_Item(i,10).value;
startDate = str2Date(startDateStr, 123);
endDate = str2Date(endDateStr, 123);
day = any2str(range.get_Item(i, 11).value);
hrs = any2str(range.get_Item(i, 12).value);
approvalStatus = any2Str(range.get_Item(i, 13).value);
//*****************Code added on 12/07/2023 ************************//
if(counterIdentifierValue != identifierValue)
{
ttsbegin;
//*****************Code added on 12/07/2023 ************************//
timesheetTable.clear();
timesheetLine.clear();
timesheetLineWeek.clear();
//*****************Code added on 12/07/2023 ************************//
expLines = 1.0;
//*****************Code added on 12/07/2023 ************************//
select RecId from resourceView
where resourceView.ResourceId == resourceId;
if (approvalStatus == /Approved/) timesheetTable.ApprovalStatus = TSAppStatus::Approved;
else if(approvalStatus == /Draft/) timesheetTable.ApprovalStatus = TSAppStatus::Create;
else if(approvalStatus == /In Review/) timesheetTable.ApprovalStatus = TSAppStatus::Pending;
timesheetTable.TimesheetNbr = timesheetNumber;
timesheetTable.Resource = resourceView.RecId;
timesheetTable.ProjPeriodId = periodId;
timesheetTable.PeriodFrom = startDate;
timesheetTable.PeriodTo = endDate;
info(strFmt(/Period ID : %1/,timesheetTable.ProjPeriodId));
Info(strFmt(/Start Date: %1, End Date : %2/,timesheetTable.PeriodFrom, timesheetTable.PeriodTo));
timesheetTable.insert();
//*****************Code added on 12/07/2023 ************************//
counterIdentifierValue = identifierValue;
trvExpTableRecId = timesheetTable.RecId;
ttscommit;
//*****************Code added on 12/07/2023 ************************//
Info(/Inserted successfully to timesheettable/);
//*****************Code added on 13/07/2023 ************************//
if(trvExpTableRecId)
//*****************Code added on 13/07/2023 ************************//
{
//timesheetLine.TimesheetNbr = timesheetNumber;
//*****************Code added on 13/07/2023 ************************//
timesheetLine.TimesheetNbr =timesheetTable.TimesheetNbr;
//*****************Code added on 13/07/2023 ************************//
timesheetLine.ProjId = projectId;
timesheetLine.ProjectDataAreaId = projectLegalEntity;
timesheetLine.CategoryId = categoryId;
timesheetLine.Resource = resourceView.RecId;
//*****************Code added on 12/07/2023 ************************//
timesheetLine.LineNum = expLines;
//*****************Code added on 12/07/2023 ************************//
if (approvalStatus == /Approved/) timesheetLine.ApprovalStatus = TSAppStatus::Approved;
else if (approvalStatus == /Draft/) timesheetLine.ApprovalStatus = TSAppStatus::Create;
else if (approvalStatus == /In Review/) timesheetLine.ApprovalStatus = TSAppStatus::Pending;
timesheetLine.insert();
Info(/Inserted successfully to timesheetLine/);
//*****************Code added on 13/07/2023 ************************//
trvExpLineRecId = timesheetLine.RecId;
if(trvExpLineRecId)
{
//*****************Code added on 13/07/2023 ************************//
select RecId from timesheetLine where timesheetLine.TimesheetNbr == timesheetNumber;
//timesheetLineWeek.TimesheetNbr = timesheetNumber;
//*****************Code added on 13/07/2023 ************************//
timesheetLineWeek.TimesheetNbr =timesheetTable.TimesheetNbr;
//*****************Code added on 13/07/2023 ************************//
timesheetLineWeek.DayFrom = startDate;
timesheetLineWeek.DayTo = endDate;
//timesheetLineWeek.Hours[1] = any2Real(day1);
//timesheetLineWeek.Hours[2] = any2Real(day2);
//timesheetLineWeek.Hours[3] = any2Real(day3);
//timesheetLineWeek.Hours[4] = any2Real(day4);
//timesheetLineWeek.Hours[5] = any2Real(day5);
//timesheetLineWeek.Hours[6] = any2Real(day6);
//timesheetLineWeek.Hours[7] = any2Real(day7);
timesheetLineWeek.Hours[any2Real(day)] = any2Real(hrs);
timesheetLineWeek.TSTimesheetLine = timesheetLine.RecId;
timesheetLineWeek.insert();
Info(/Inserted successfully to timesheetLineWeek/);
}
}
}
//*****************Code added on 12/07/2023 ************************//
else
{
//timesheetLine.clear();
//*************************************************************
ttsbegin;
//while select forupdate firstonly ProjId from timesheetLineLocal where timesheetLineLocal.TimesheetNbr == timesheetNumber
//while select forupdate firstonly timesheetLineLocal join timesheetLineWeekLocal where timesheetLineWeekLocal.TimesheetNbr == timesheetNumber
// && timesheetLineLocal.TimesheetNbr == timesheetNumber
//while select forupdate timesheetLineWeekLocal join timesheetLineLocal where timesheetLineWeekLocal.TSTimesheetLine == timesheetLineLocal.RecId && timesheetLineLocal.TimesheetNbr == timesheetNumber
select timesheetLineLocal where timesheetLineLocal.TimesheetNbr == timesheetNumber && timesheetLineLocal.ProjId == projectId;
select forupdate timesheetLineWeekLocal where timesheetLineWeekLocal.TSTimesheetLine == timesheetLineLocal.RecId;
if(timesheetLineLocal.RecId)
{
//r1 = timesheetLineLocal.RecId;
//r2 = timesheetLineWeekLocal.RecId;
timesheetLineWeekLocal.Hours[any2Real(day)] = any2Real(hrs);
//timesheetLineWeekLocal.TSTimesheetLine = timesheetLineLocal.RecId;
timesheetLineWeekLocal.update();
//delete_from timesheetLineLocal where timesheetLineLocal.RecId == r1;
//delete_from timesheetLineWeekLocal where timesheetLineWeekLocal.RecId == r2;
// break;
}
else
{
//**************************************************************
timesheetLineLoc.clear();
timesheetLineWeekLoc.clear();
expLines++;
//*****************Code added on 12/07/2023 ************************//
timesheetTableLoc= TSTimesheetTable::findRecId(trvExpTableRecId);
timesheetLineLoc.TimesheetNbr = timesheetTableLoc.TimesheetNbr;
select RecId from resourceView
where resourceView.ResourceId == resourceId;
//*****************Code added on 12/07/2023 ************************//
// timesheetLineLoc.TimesheetNbr = timesheetNumber;
timesheetLineLoc.ProjId = projectId;
timesheetLineLoc.ProjectDataAreaId = projectLegalEntity;
timesheetLineLoc.CategoryId = categoryId;
timesheetLineLoc.Resource = resourceView.RecId;
timesheetLineLoc.LineNum = expLines;
if (approvalStatus == /Approved/) timesheetLineLoc.ApprovalStatus = TSAppStatus::Approved;
else if (approvalStatus == /Draft/) timesheetLineLoc.ApprovalStatus = TSAppStatus::Create;
else if (approvalStatus == /In Review/) timesheetLineLoc.ApprovalStatus = TSAppStatus::Pending;
timesheetLineLoc.insert();
Info(/Inserted successfully to timesheetLineLoc/);
//*****************Code added on 13/07/2023 ************************//
trvExpLineLocRecId = timesheetLineLoc.RecId;
if(trvExpLineLocRecId)
{
//*****************Code added on 13/07/2023 ************************//
//select RecId from timesheetLineLoc where timesheetLineLoc.TimesheetNbr == timesheetNumber;
//timesheetLineWeekLoc.TimesheetNbr = timesheetNumber;
// //****************************************************************
timesheetLineWeekLoc.TimesheetNbr = timesheetTable.TimesheetNbr;
////**********************************************************
timesheetLineWeekLoc.DayFrom = startDate;
timesheetLineWeekLoc.DayTo = endDate;
timesheetLineWeekLoc.Hours[any2Real(day)] = any2Real(hrs);
//timesheetLineWeekLoc.Hours[1] = any2Real(day1);
//timesheetLineWeekLoc.Hours[2] = any2Real(day2);
//timesheetLineWeekLoc.Hours[3] = any2Real(day3);
//timesheetLineWeekLoc.Hours[4] = any2Real(day4);
//timesheetLineWeekLoc.Hours[5] = any2Real(day5);
//timesheetLineWeekLoc.Hours[6] = any2Real(day6);
//timesheetLineWeekLoc.Hours[7] = any2Real(day7);
timesheetLineWeekLoc.TSTimesheetLine = timesheetLineLoc.RecId;
timesheetLineWeekLoc.insert();
Info(/Inserted successfully to timesheetLineWeekLoc/);
}
}
ttscommit;
}
}
//*****************Code added on 12/07/2023 ************************//
catch(Exception::Error)
{
info(strFmt(/The line no. %1 has been skipped due to an error/, i));
continue;
}
}
}
}
}
}
}