Get data from db in ax 2012 X++
class PSA_InvoiceProposalInboundProcess extends RunBaseBatch
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect,cmdUpdate;
System.Data.OleDb.OleDbDataReader reader,readerUpdate;
container resultCon;
str connectStr;
ProjParameters projParameters;
Map dimAttributeMap;
ProjId projID;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public boolean canGoBatch()
{
return true;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
protected boolean canGoBatchJournal()
{
return true;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public void createMilestone(container _c)
{
ALE_STG_Milestone milestone;
ProjLineProperty projLineProperty;
ProjId projIDLoc,psaProjId;
TransDate ProjDateLoc;
DimensionValue WorkerLoc;
Qty HoursLoc;
AmountMST SalesPriceLoc,CostPriceLoc;
str LinePropertyLoc;
CurrencyCode CurrencyCodeLoc;
Description GuidLoc,ContractingUnitLoc,resourcingUnitLoc;
str CompanyIdLoc,msg;
String30 invoiceStatus,psaInvoiceId;
;
GuidLoc = '';
CompanyIdLoc = '';
/*ContractingUnitLoc = '';
resourcingUnitLoc = '';*/
GuidLoc = conpeek(_c, 1);
CompanyIdLoc = conpeek(_c, 2);
invoiceStatus = conpeek(_c, 3);
psaInvoiceId = conpeek(_c, 4);
/*resourcingUnitLoc = conpeek(_c, 11);
ContractingUnitLoc = conpeek(_c, 12);*/
select forupdate milestone where milestone.GUID == GuidLoc;
milestone.BillingStatus = invoiceStatus;
milestone.InvoiceId = psaInvoiceId;
milestone.Status = 2 ;
milestone.update();
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public Str1260 formatString(Str1260 _msg)
{
str replace(str _source, str _what, str _with)
{
int found = 0;
str target = _source;
do
{
found = strscan(target, _what, found, strlen(target));
if (found != 0)
{
target = strdel(target, found, strlen(_what));
target = strins(target, _with, found);
found += strlen(_with);
}
} while (found != 0);
return target;
}
;
_msg = replace(_msg, '\n', '');
_msg = replace(_msg, '\r', '');
_msg = replace(_msg, '\t', '');
_msg = replace(_msg, '\'', '');
return _msg;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public container pack()
{
return conNull();
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public container processMilestone(System.Data.OleDb.OleDbDataReader readerProject)
{
int startLine = infologline(), currentLine, axStatus;
int i;
Name name,psaContractingUnit,psaResourcingUnit;
ProjId projIDLoc,psaProjId;
TransDate psaProjDate;
DimensionValue psaWorker;
Qty psaHours;
AmountMST psaSalesPrice,psaCostPrice;
str psaLineProperty;
String30 psaInvoiceStatus,psaInvoiceId;
Description psaGuid;
str psaCompanyId,msg;
psaGuid = '';
psaCompanyId = '';
psaInvoiceStatus = '';
psaInvoiceId = '';
/*psaResourcingUnit = '';
psaContractingUnit = '';*/
try
{
if (!readerProject.IsDBNull(0))
psaInvoiceStatus = readerProject.GetString(0);
else
psaInvoiceStatus = "";
if (!readerProject.IsDBNull(1))
psaGuid = readerProject.GetString(1);
else
psaGuid = "";
if (!readerProject.IsDBNull(2))
psaCompanyId = readerProject.GetString(2);
else
psaCompanyId = "";
if (!readerProject.IsDBNull(3))
psaInvoiceId = readerProject.GetString(3);
else
psaInvoiceId = "";
ttsbegin;
// Invoice Status
if (readerProject.IsDBNull(0))
{
msg = 'InvoiceStatus cannot be null';
ttsAbort;
axStatus = -1;
return [axStatus, msg];
}
this.createMilestone([psaGuid,psaCompanyId,psaInvoiceStatus,psaInvoiceId]);
axStatus = 2;
msg = strFmt('Milestone successfully ');
ttscommit;
}
catch
{
ttsAbort;
axStatus = -1;
for (currentLine = startLine + 1; currentLine <= infologline(); currentLine++)
{
msg += infolog.text(currentLine);
}
msg = this.formatString(msg);
infolog.clear();
}
return [axStatus, msg];
}
class PSA_InvoiceProposalInboundProcess extends RunBaseBatch
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect,cmdUpdate;
System.Data.OleDb.OleDbDataReader reader,readerUpdate;
container resultCon;
str connectStr;
ProjParameters projParameters;
Map dimAttributeMap;
ProjId projID;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public boolean canGoBatch()
{
return true;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
protected boolean canGoBatchJournal()
{
return true;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public void createMilestone(container _c)
{
ALE_STG_Milestone milestone;
ProjLineProperty projLineProperty;
ProjId projIDLoc,psaProjId;
TransDate ProjDateLoc;
DimensionValue WorkerLoc;
Qty HoursLoc;
AmountMST SalesPriceLoc,CostPriceLoc;
str LinePropertyLoc;
CurrencyCode CurrencyCodeLoc;
Description GuidLoc,ContractingUnitLoc,resourcingUnitLoc;
str CompanyIdLoc,msg;
String30 invoiceStatus,psaInvoiceId;
;
GuidLoc = '';
CompanyIdLoc = '';
/*ContractingUnitLoc = '';
resourcingUnitLoc = '';*/
GuidLoc = conpeek(_c, 1);
CompanyIdLoc = conpeek(_c, 2);
invoiceStatus = conpeek(_c, 3);
psaInvoiceId = conpeek(_c, 4);
/*resourcingUnitLoc = conpeek(_c, 11);
ContractingUnitLoc = conpeek(_c, 12);*/
select forupdate milestone where milestone.GUID == GuidLoc;
milestone.BillingStatus = invoiceStatus;
milestone.InvoiceId = psaInvoiceId;
milestone.Status = 2 ;
milestone.update();
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public Str1260 formatString(Str1260 _msg)
{
str replace(str _source, str _what, str _with)
{
int found = 0;
str target = _source;
do
{
found = strscan(target, _what, found, strlen(target));
if (found != 0)
{
target = strdel(target, found, strlen(_what));
target = strins(target, _with, found);
found += strlen(_with);
}
} while (found != 0);
return target;
}
;
_msg = replace(_msg, '\n', '');
_msg = replace(_msg, '\r', '');
_msg = replace(_msg, '\t', '');
_msg = replace(_msg, '\'', '');
return _msg;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public container pack()
{
return conNull();
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public container processMilestone(System.Data.OleDb.OleDbDataReader readerProject)
{
int startLine = infologline(), currentLine, axStatus;
int i;
Name name,psaContractingUnit,psaResourcingUnit;
ProjId projIDLoc,psaProjId;
TransDate psaProjDate;
DimensionValue psaWorker;
Qty psaHours;
AmountMST psaSalesPrice,psaCostPrice;
str psaLineProperty;
String30 psaInvoiceStatus,psaInvoiceId;
Description psaGuid;
str psaCompanyId,msg;
psaGuid = '';
psaCompanyId = '';
psaInvoiceStatus = '';
psaInvoiceId = '';
/*psaResourcingUnit = '';
psaContractingUnit = '';*/
try
{
if (!readerProject.IsDBNull(0))
psaInvoiceStatus = readerProject.GetString(0);
else
psaInvoiceStatus = "";
if (!readerProject.IsDBNull(1))
psaGuid = readerProject.GetString(1);
else
psaGuid = "";
if (!readerProject.IsDBNull(2))
psaCompanyId = readerProject.GetString(2);
else
psaCompanyId = "";
if (!readerProject.IsDBNull(3))
psaInvoiceId = readerProject.GetString(3);
else
psaInvoiceId = "";
ttsbegin;
// Invoice Status
if (readerProject.IsDBNull(0))
{
msg = 'InvoiceStatus cannot be null';
ttsAbort;
axStatus = -1;
return [axStatus, msg];
}
this.createMilestone([psaGuid,psaCompanyId,psaInvoiceStatus,psaInvoiceId]);
axStatus = 2;
msg = strFmt('Milestone successfully ');
ttscommit;
}
catch
{
ttsAbort;
axStatus = -1;
for (currentLine = startLine + 1; currentLine <= infologline(); currentLine++)
{
msg += infolog.text(currentLine);
}
msg = this.formatString(msg);
infolog.clear();
}
return [axStatus, msg];
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public void run()
{
str datestr;
ALE_STG_HourJournal hourJournalUpdate;
ALE_STG_ExpenseJournal expenseJournal;
ALE_STG_Milestone milestone;
Ale_ProcessingStatus status;
DataAreaId psaCompanyId;
Description pGuid;
string255 err;
projParameters = ProjParameters::find();
connectStr = 'Provider=SQLNCLI11;Integrated Security=SSPI;' +'Persist Security Info=False;Initial Catalog=' +
projParameters.ALE_PSA_StagingDBName + ';Data Source=' + projParameters.ALE_PSA_StagingDBServer + ';'+ 'User id=axadmin;Password=gavs_2012';
try
{
objConn = new System.Data.OleDb.OleDbConnection(connectStr);
objConn.Open();
cmdSelect = objConn.CreateCommand();
cmdSelect.set_CommandText('SELECT HJ.BillingStatus,HJ.GUID,HJ.CompanyId,HJ.PSAInvoiceId '+
'FROM HourJournal AS HJ '+ 'WHERE HJ.STATUS = 2');
reader = cmdSelect.ExecuteReader();
while (reader.Read())
{
psaCompanyId = reader.GetString(2);
if (psaCompanyId == '' || !CompanyInfo::findDataArea(psaCompanyId))
{
resultCon = [-1,"Company code error"];
pGuid = reader.GetString(1);
cmdUpdate = objConn.CreateCommand();
}
else
{
changeCompany(psaCompanyId)
resultCon = this.processHourJournalTable(reader);
pGuid = reader.GetString(1);
cmdUpdate = objConn.CreateCommand();
}
cmdUpdate = objConn.CreateCommand();
cmdUpdate.set_CommandText(strFmt('UPDATE HourJournal SET STATUS = %1,' + ' ERRORMESSAGE = ' + "'%2'" + 'WHERE GUID = ' + "'%3'",
conPeek(resultCon, 1), conPeek(resultCon, 2), pGuid));
readerUpdate = cmdUpdate.ExecuteReader();
if (conPeek(resultCon, 1) == 1)
status = Ale_ProcessingStatus::Processed;
else if (conPeek(resultCon, 1) == -1)
status = Ale_ProcessingStatus::Error;
else
status = Ale_ProcessingStatus::Unprocessed;
err = conPeek(resultCon, 2);
//Update AX staging hourJournal-start
update_recordSet hourJournalUpdate
setting processingstatus = status,ErrorMsg = err
where hourJournalUpdate.GUID == pGuid
&& hourJournalUpdate.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;
//Update AX staging hourJournal-end
}
// update expense journal for Invoice proposal
cmdSelect = objConn.CreateCommand();
cmdSelect.set_CommandText('SELECT EJ.BillingStatus,EJ.GUID,EJ.CompanyId,EJ.PSAInvoiceId '+
'FROM ExpenseJournal AS EJ '+ 'WHERE EJ.STATUS = 2');
reader = cmdSelect.ExecuteReader();
while (reader.Read())
{
psaCompanyId = reader.GetString(2);
if (psaCompanyId == '' || !CompanyInfo::findDataArea(psaCompanyId))
{
resultCon = [-1,"Company code error"];
pGuid = reader.GetString(1);
cmdUpdate = objConn.CreateCommand();
}
else
{
changeCompany(psaCompanyId)
resultCon = this.processExpenseJournalTable(reader);
pGuid = reader.GetString(1);
cmdUpdate = objConn.CreateCommand();
}
cmdUpdate = objConn.CreateCommand();
cmdUpdate.set_CommandText(strFmt('UPDATE ExpenseJournal SET STATUS = %1,' + ' ERRORMESSAGE = ' + "'%2'" + 'WHERE GUID = ' + "'%3'",
conPeek(resultCon, 1), conPeek(resultCon, 2), pGuid));
readerUpdate = cmdUpdate.ExecuteReader();
if (conPeek(resultCon, 1) == 1)
status = Ale_ProcessingStatus::Processed;
else if (conPeek(resultCon, 1) == -1)
status = Ale_ProcessingStatus::Error;
else
status = Ale_ProcessingStatus::Unprocessed;
err = conPeek(resultCon, 2);
//Update AX staging hourJournal-start
update_recordSet expenseJournal
setting processingstatus = status,ErrorMsg = err
where expenseJournal.GUID == pGuid
&& expenseJournal.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;
//Update AX staging hourJournal-end
}
//
// on Account
cmdSelect = objConn.CreateCommand();
cmdSelect.set_CommandText('SELECT MS.BillingStatus,MS.GUID,MS.CompanyId,MS.PSAInvoiceId '+
'FROM Milestone AS MS '+ 'WHERE MS.STATUS = 2');
reader = cmdSelect.ExecuteReader();
while (reader.Read())
{
psaCompanyId = reader.GetString(2);
if (psaCompanyId == '' || !CompanyInfo::findDataArea(psaCompanyId))
{
resultCon = [-1,"Company code error"];
pGuid = reader.GetString(1);
cmdUpdate = objConn.CreateCommand();
}
else
{
changeCompany(psaCompanyId)
resultCon = this.processMilestone(reader);
pGuid = reader.GetString(1);
cmdUpdate = objConn.CreateCommand();
}
cmdUpdate = objConn.CreateCommand();
cmdUpdate.set_CommandText(strFmt('UPDATE Milestone SET STATUS = %1,' + ' ERRORMESSAGE = ' + "'%2'" + 'WHERE GUID = ' + "'%3'",
conPeek(resultCon, 1), conPeek(resultCon, 2), pGuid));
readerUpdate = cmdUpdate.ExecuteReader();
if (conPeek(resultCon, 1) == 1)
status = Ale_ProcessingStatus::Processed;
else if (conPeek(resultCon, 1) == -1)
status = Ale_ProcessingStatus::Error;
else
status = Ale_ProcessingStatus::Unprocessed;
err = conPeek(resultCon, 2);
//Update AX staging hourJournal-start
update_recordSet milestone
setting processingstatus = status,ErrorMsg = err
where milestone.GUID == pGuid
&& milestone.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;
//Update AX staging hourJournal-end
}
}
catch(Exception::Error)
{
error('An Exception has occurred');
}
if(objConn)
{
objConn.Close();
}
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public boolean runsImpersonated()
{
return true;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public boolean unpack(container _packedClass)
{
return true;
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
static ClassDescription description()
{
return " Project invoice proposal inbound process";
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public static void main(Args args)
{
ALE_PSA_InvoiceProposalInboundProcess invoiceProposalInboundProcess = new ALE_PSA_InvoiceProposalInboundProcess();;
if (invoiceProposalInboundProcess.prompt())
{
invoiceProposalInboundProcess.run();
}
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
No comments:
Post a Comment