public void importHourJournal(ProjJournalId _journal )
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
ProjJournalTable projJournalTable;
ProjJournalTrans projJourTrans;
ProjTransDate transDate;
ProjId projId;
HcmPersonnelNumberId personnelNumber;
CategoryId categoryId;
TransTxt description;
Qty hours;
ProjLinePropertyId linePropertyId;
ProjJournalTransData journalTransData;
ProjTable projTable;
ProjInvoiceTable projInvoiceTable;
NumberSeq numberSeq;
ProjQtyEmpl qty;
JournalNumOfLines numOfLines;
Dialog dialog;
DialogField dialogFilename;
int row;
File
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
dialog = new Dialog("Excel Upoad");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
//dialog.filenameLookupFilter(["@SYS28576",XLSX,"@SYS28576",XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Hour journal lines upload");
dialogFilename.value(filename);
if(dialog.run())
{
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
do
{
row++;
transDate = any2date(cells.item(row, 1).value().date()); //toString());
projId = cells.item(row, 2).value().bStr();
description = cells.item(row, 5).value().bStr();
categoryId = cells.item(row, 3).value().bStr();
personnelNumber = cells.item(row, 4).value().bStr();
hours = cells.item(row, 6).value().double();
linePropertyId = cells.item(row, 7).value().bstr();
ttsBegin;
projJourTrans.clear();
projJournalTable = ProjJournalTable::find(_journalId);
projJourTrans.JournalId = projJournalTable.JournalId;
projTable = ProjTable::find(projId);
projInvoiceTable = projTable.projInvoice();
projJourTrans.TransDate = transDate;
projJourTrans.ProjTransDate = transDate;
projJourTrans.ProjId = projTable.ProjId;
projJourTrans.Qty = hours;
projJourTrans.CategoryId = categoryId;
projJourTrans.LinePropertyId = linePropertyId;
projJourTrans.Worker = HcmWorker::findByPersonnelNumber(personnelNumber).RecId;
projJourTrans.Txt = description;
if(projInvoiceTable.CurrencyId)
projJourTrans.CurrencyId = projInvoiceTable.CurrencyId;
else
projJourTrans.CurrencyId = CompanyInfo::standardCurrency();
projJourTrans.DefaultDimension = projTable.DefaultDimension;
projJourTrans.TaxGroupId = ProjParameters::taxGroupInvoice(projTable.ProjId);
if(projJourTrans.Worker)
{
projJourTrans.setHourPrices();
projJourTrans.setPeriodDate();
}
numberSeq = NumberSeq::newGetVoucherFromId(projJournalTable.VoucherNumberSequenceTable, false);
projJourTrans.Voucher = numberSeq.voucher();
projJourTrans.insert();
ttsCommit;
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
}
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
ProjJournalTable projJournalTable;
ProjJournalTrans projJourTrans;
ProjTransDate transDate;
ProjId projId;
HcmPersonnelNumberId personnelNumber;
CategoryId categoryId;
TransTxt description;
Qty hours;
ProjLinePropertyId linePropertyId;
ProjJournalTransData journalTransData;
ProjTable projTable;
ProjInvoiceTable projInvoiceTable;
NumberSeq numberSeq;
ProjQtyEmpl qty;
JournalNumOfLines numOfLines;
Dialog dialog;
DialogField dialogFilename;
int row;
File
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
dialog = new Dialog("Excel Upoad");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
//dialog.filenameLookupFilter(["@SYS28576",XLSX,"@SYS28576",XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Hour journal lines upload");
dialogFilename.value(filename);
if(dialog.run())
{
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
do
{
row++;
transDate = any2date(cells.item(row, 1).value().date()); //toString());
projId = cells.item(row, 2).value().bStr();
description = cells.item(row, 5).value().bStr();
categoryId = cells.item(row, 3).value().bStr();
personnelNumber = cells.item(row, 4).value().bStr();
hours = cells.item(row, 6).value().double();
linePropertyId = cells.item(row, 7).value().bstr();
ttsBegin;
projJourTrans.clear();
projJournalTable = ProjJournalTable::find(_journalId);
projJourTrans.JournalId = projJournalTable.JournalId;
projTable = ProjTable::find(projId);
projInvoiceTable = projTable.projInvoice();
projJourTrans.TransDate = transDate;
projJourTrans.ProjTransDate = transDate;
projJourTrans.ProjId = projTable.ProjId;
projJourTrans.Qty = hours;
projJourTrans.CategoryId = categoryId;
projJourTrans.LinePropertyId = linePropertyId;
projJourTrans.Worker = HcmWorker::findByPersonnelNumber(personnelNumber).RecId;
projJourTrans.Txt = description;
if(projInvoiceTable.CurrencyId)
projJourTrans.CurrencyId = projInvoiceTable.CurrencyId;
else
projJourTrans.CurrencyId = CompanyInfo::standardCurrency();
projJourTrans.DefaultDimension = projTable.DefaultDimension;
projJourTrans.TaxGroupId = ProjParameters::taxGroupInvoice(projTable.ProjId);
if(projJourTrans.Worker)
{
projJourTrans.setHourPrices();
projJourTrans.setPeriodDate();
}
numberSeq = NumberSeq::newGetVoucherFromId(projJournalTable.VoucherNumberSequenceTable, false);
projJourTrans.Voucher = numberSeq.voucher();
projJourTrans.insert();
ttsCommit;
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
}
No comments:
Post a Comment