Hi all,
I need to create a new journal with lines as for prodid 174-10 will be a journal and for prodid174-20 will be a journal…
Like that for other production also.
Thanks in advance.
Hi all,
I need to create a new journal with lines as for prodid 174-10 will be a journal and for prodid174-20 will be a journal…
Like that for other production also.
Thanks in advance.
What issue do you have? Please explain.
static void sri(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
System.DateTime ShlefDate;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
int row;
int lines;
str jourID;
//Table Declarations Starts
ProdJournalRoute PJR;
ProdJournalTable PJTab, table;
//Table Declartions Ends
;
dialog = new Dialog("Upload excel file");
dialogFilename = dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Excel upload");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
info(filename);
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try{
workbooks.open(filename);
}
catch(Exception::Error){
throw error("Cannot open the file. Check if file exists in location ");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
ttsbegin;
// create a journal header
//journalType = Routecard
PJTab.JournalType =ProdJournalType::RouteCard ;
//get name ID for header
PJTab.ProdId=cells.item(2,1).value().bStr();
//PJTab.JournalNameId = ProdJournalName::standardJournalName(ProdJournalType::RouteCard,PJTab.ProdId);
//PJTab.JournalNameId = ProdParametersDim::findDefault().RouteJournalNameId;
PJTab.Description =ProdJournalName::find(PJTab.JournalNameId).Description;
PJTab.insert();
do{
ttsbegin;
row++;
PJR.JournalId = PJTab.JournalId;
PJR.OprId = cells.item(row,2).value().bStr();
PJR.TransDate=cells.item(row,3).value().date();
PJR.OprFinished=cells.item(row,4).value().boolean();
PJR.QtyError = cells.item(row,5).value().double();
PJR.QtyGood = cells.item(row,6).value().double();
PJR.EmplId= cells.item(row,7).value().bStr();
//Initialize lineNum to '0' else inventJournalTrans.insert() method wouldn't create a new lineNum for each insertion
PJR.LineNum=0;
//parameter for insert method
PJR.insert();
//update type variable to next row in file
type= cells.item(row+1,1).value().variantType();
ttscommit;
}
while(type != COMVariantType::VT_EMPTY);
select count(recid) from PJR where PJR.JournalId == PJTab.JournalId;
lines = PJR.RecId;
select forupdate table where table.JournalId == PJTab.JournalId;
table.NumOfLines=lines;
table.update();
application.quit();
ttscommit;
}
I tried this coding, but the result came is like
but i want the prd id 174 as a new journal with 2 lines as oprnum 10 and 20 and a new journals for other prodid’s…
i want this 2 prod id’s to be in a new journal and the same prodid with oprnum 20 to be in a new journal…
like that prodid’s 172,156…
I need to create a new journals.
When you are inserting the data (example for select line), check if there is already a journal with that production order and opr num. If there is a line, don’t create a new journal and use the existing journal. If there is no line, then create a new journal.
The other approach would be using a staging table , insert everything from the excel into that table and try applying the sorting/grouping on the staging table before actually creating the production route journal.
When you are inserting the data (example for select line), check if there is already a journal with that production order and opr num. If there is a line, don’t create a new journal and use the existing journal. If there is no line, then create a new journal.
can you give me an example for this, kranthi?
Before doing the insert do a select on ProdJournalRoute and to see if the record exists,
Example :
ProdJournalRoute prodJournalRouteFind;
select firstonly JournalId from prodJournalRouteFind
where prodJournalRouteFind.ProdId == prodId // prodId from excel
&& prodJournalRouteFind.OprNum = oprNum; //Opr num from excel
if (!prodJournalRouteFind.JournalId )
{
// create a new journal
}
else
{
// use the existing journal
}
prodId and oprid from excel, how to get this in our code…
how to get column length from excel using code?
You are already inserting them in your code.
select firstonly JournalId from prodJournalRouteFind
where prodJournalRouteFind.ProdId ==prodId // prodId from excel
&& prodJournalRouteFind.OprNum == oprNum; //Opr num from excel
if (!prodJournalRouteFind.JournalId )
{
row++;
PJTab.JournalType =ProdJournalType::RouteCard ;
PJTab.ProdId=cells.item(row,1).value().bStr();
PJTab.JournalNameId = ProdJournalName::standardJournalName(ProdJournalType::RouteCard,PJTab.ProdId);
PJTab.Description =ProdJournalName::find(PJTab.JournalNameId).Description;
PJTab.insert();
do{
ttsbegin;
row++;
PJR.JournalId = PJTab.JournalId;
PJR.OprNum = cells.item(row,2).value().double();
PJR.TransDate=cells.item(row,3).value().date();
PJR.OprFinished=cells.item(row,4).value().boolean();
PJR.QtyError = cells.item(row,6).value().double();
PJR.QtyGood = cells.item(row,5).value().double();
PJR.EmplId= cells.item(row,7).value().bStr();
//Initialize lineNum to ‘0’ else inventJournalTrans.insert() method wouldn’t create a new lineNum for each insertion
PJR.LineNum=0;
//parameter for insert method
PJR.insert();
//update type variable to next row in file
type= cells.item(row+1,1).value().variantType();
ttscommit;
}
while(type != COMVariantType::VT_EMPTY);
}
else
{
warning(strfmt("Prodid already exists %1 : ",PJR));
}
I got a error has prodid not declared…
This is what i mean Prod Id from excel. You need to use that value in the select statement.
No, Kranthi.
I dont need to check tat journal and prodid was already exist…
i just need to create a new jorunal for prodid’s…
PRD_00000174 lines as oprnum 10,20
PRD_00000172 lines as oprnum 20
Like this new journals need to be created from excel sheets…
Store ur ProdId in one variable…Then check it with the upcoming values…
For example…
If(prev == curr)
{
skip
create records only for lines…Bcz header already exists.
}
else
{
insert
Create records for both header and lines…
}
or
As kranthi said u can check with the table also…
This will work if the excel has the data sorted by ProdId.
static void import_routecard_journal(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelRange range;
COMVariantType type;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
int row;
int lines;
str jid;
boolean flag;
//Table declaration
ProdJournalRoute PJR, PJRC, prodJournalRouteFind;
ProdJournalTable PJTab, table;
//Field declaration
ProdJournalIdRoute Journalid;
JournalTransDate transdate;
OprNum OprNum;
ProdId ProdId,prevprodid, nextProdId;
ProdReportedGood qtygood;
ProdReportedError qtyerror;
EmplId EmplId;
ProdFinishedOpr OprFinished;
;
dialog = new Dialog("Upload excel file");
dialogFilename = dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Excel upload");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
info(filename);
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try{
workbooks.open(filename);
}
catch(Exception::Error){
throw error("Cannot open the file. Check if file exists in location ");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
ttsbegin;
do
{
row++;
ProdId= cells.item(row,1).value().bStr();
prevprodid=cells.item(row-1,1).value().bStr();
nextProdId = cells.item(row+1,1).value().bStr();
OprNum = cells.item(row,2).value().double();
TransDate=cells.item(row,3).value().date();
OprFinished=cells.item(row,4).value().boolean();
QtyGood = cells.item(row,5).value().double();
QtyError = cells.item(row,6).value().double();
EmplId= cells.item(row,7).value().bStr();
/*select firstonly recid from prodJournalRouteFind
where prodJournalRouteFind.ProdId == prodId // prodId from excel
&& prodJournalRouteFind.OprNum == oprNum; //Opr num from excel
if (!prodJournalRouteFind.JournalId )
{*/
if(prevprodid!=prodid && row >= 2)
{
//create a new header
PJTab.JournalType =ProdJournalType::RouteCard;
PJTab.JournalNameId = ProdJournalName::standardJournalName(ProdJournalType::RouteCard,PJTab.ProdId);
PJTab.Description =ProdJournalName::find(PJTab.JournalNameId).Description;
PJTab.ProdId=prodid;
pjtab.initValue();
PJTab.JournalId="";
PJTab.insert();
flag = True;
//info(strfmt("Creating new header JID:%1, PRD:%2",PJTab.JournalId, PJTab.ProdId));
jid=PJTab.JournalId;
//PJTab.JournalId="";
}
if((prevprodid==prodid && prevprodid!= "" && row >2)||(row==2)||(flag))
{
//populate line and insert
pjr.ProdId = prodid;
pjr.JournalId = jid;
pjr.OprNum = oprNum;
pjr.TransDate = TransDate;
pjr.QtyGood = QtyGood;
pjr.QtyError = QtyError;
pjr.OprFinished = OprFinished;
pjr.insert();
flag = False;
info(strfmt("jourID->PRD->Line : %1 -> %2 -> %3",pjr.JournalId, pjr.ProdId, pjr.LineNum));
pjr.LineNum = 0;
if(pjr.ProdId != nextProdId)
{
select count(recid) from pjrc where pjrc.JournalId == pjr.JournalId && pjrc.ProdId == pjr.ProdId;
lines = pjrc.RecId;
info(strfmt("PRD: %1. lines: %2",pjr.ProdId,lines));
select forupdate PJTab where Pjtab.JournalId == pjr.JournalId && pjtab.ProdId == pjr.ProdId;
pjtab.NumOfLines = lines;
pjtab.update();
}
}
/* else
{
warning("Journal id already exists");
}
*/
type = cells.item(row+1,1).value().variantType();
}
while(type != COMVariantType::VT_EMPTY);
ttscommit;
}
i tried like this, whether this is correct?