SYED IRFAN's Blogs on Dynamic AX: May 2016

Tuesday, May 24, 2016

Copy Usergroup Record level security By table

static void AXCopyUsergroupRLSBytable(Args _args)
{
SysRecordLevelSecurity SysRecordLevelSecurity,insertRLS;
dialogfield srcdf,destdf;
dialog dialog;
userGroupid src,dest;
;
dialog = new dialog("copying user group's Record Level security ");
srcdf = dialog.addField(typeid(userGroupid),"Source");
destdf = dialog.addField(typeid(userGroupid),"Destination");

if(dialog.run())
{
src = srcdf.value();
dest = destdf.value();
if(src!="" && dest !="")
{
ttsbegin;
info("Copying RLS to destination user group");
select * from SysRecordLevelSecurity
where SysRecordLevelSecurity.groupId == src
&& SysRecordLevelSecurity.tabId == 40017;//PAYFGOSIItemCodeInfo table id
{

insertRLS.data(SysRecordLevelSecurity);
insertRLS.groupId = dest;

insertRLS.insert();

}
ttscommit;
info("User groups RLS has been copied!");
}
}
else
{
info("Canceled by user");
}

}

Sunday, May 15, 2016

Replace Assigned workflow user In AX 2009 without changing Configuration Id

static void AXReplaceAssignedworkflowuser(Args _args)
{
userid src,dest;
dialogfield srcdf,destdf;
dialog dialog;
WorkflowStepTable WorkflowStepTable;
container con;

;
dialog = new dialog("Replace workflow assigned Approval user");
srcdf = dialog.addField(typeid(userid),"Source");
destdf = dialog.addField(typeid(userid),"Destination");
if(dialog.run())
{
src = srcdf.value();
dest = destdf.value();
if(src!="" && dest !="")
{
ttsbegin;
while select forupdate WorkflowStepTable
{
con = WorkflowStepTable.AssignTo;
if(conpeek(con,4) == src)
{
info(strfmt("Before update userid %1 ",conpeek(con,4)));

con = conpoke(con,4,dest);

WorkflowStepTable.AssignTo = con;
WorkflowStepTable.doUpdate();

info(strfmt("after update userid %1 ",conpeek(con,4)));
}
}
info("Workflow approval user has been replaced !.");
ttscommit;
}
}
else
{
info("Canceled by user");
}

}

Thursday, May 5, 2016

Send mail alert to user group lists in HTML format using AX

void sendmailtousers()
{
SysMailer mail ;
SysEmailParameters parameters ;
userGroupList userGroupListTO,userGroupListBCC;
BatchJob batchJob;
SysCompanyUserInfo sysCompanyUserInfo;

projtable projtable;

InteropPermission permission = new InteropPermission(InteropKind::ClrInterop);
emplid emplid ;
date scheduledate,dueenddate;
str header,mainbody,footer;
PAYFPrintPayrollBankFile PAYFPrintPayrollBankFile;
Counter linenum;

System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.SmtpClient smtpClient;
System.Net.Mail.MailAddress mailAddressFrom;
System.Net.Mail.MailAddress mailAddressTo;

str strBody;
str strSMTPServer;
str strFileName,email;
FileIOPermission perm;

projid projid;

str sender,mailAddressTos;

void htmlbody()
{
date subdate;
str comp,event;
;
header = "" ;
mainbody = "";
footer = "";

comp = "Company AND SON'S LTD.";
event = "PAYROLL ALERTS";

header = strfmt("PAYROLL REPORTS "+
"<TABLE cellSpacing=0 cellPadding=1 width='100%' border=3>"+
"<tr bgColor=gray> "+
"<td align=center>الرقم التسلسلي</td>"+//sn
"<td align=center>الرقم العام</td>"+//emplid
"<td align=center>‏‏الاسم</td>"+//name
"<td align=center>الجنسية</td>"+//Nationality
"<td align=center>العنوان</td>"+ //title
"<td align=center>المشروع</td>"+//project
"<td align=center>المنطقة</td>"+//region
"<td align=center>الشهر</td>"+//Month
"<td align=center>‏‏الحالة</td>"+//status
"</tr>");

while select PAYFPrintPayrollBankFile order by PAYFPrintPayrollBankFile.Dimension[2] ,PAYFPrintPayrollBankFile.EmplId where
PAYFPrintPayrollBankFile.TransferStatus == BankFileTransferStaus::NotTransfer
// && PAYFPrintPayrollBankFile.PAYFPeriodNumber == "PERD000005035"
{
linenum++;

if(linenum != 1 && projid != PAYFPrintPayrollBankFile.Dimension[2])
{
mainbody += strfmt("<tr bgColor=gray> <td height=10 colspan=9> "+" " +"</td></tr>" );
}

mainbody += strfmt("<TR ALIGN='CENTER'>"+
"<TD>" + num2str(linenum,3,0,0,0) + "</TD>"+
"<TD>" + PAYFPrintPayrollBankFile.EmplId + "</TD>"+
"<TD>" + PAYFPrintPayrollBankFile.name()+"</TD>"+
"<TD>" + PAYFPrintPayrollBankFile.addressCountryRegionNationality()+ "</TD>"+
"<TD>" + PAYFPrintPayrollBankFile.titleDescdription()+"</TD>"+

"<TD>" + PAYFPrintPayrollBankFile.DescDim2()+ "</TD>"+
"<TD>" + PAYFPrintPayrollBankFile.DescDim3()+"</TD>"+
"<TD>" + date2str(PAYFCalculationPeriods::find(PAYFPrintPayrollBankFile.PAYFPeriodNumber,false).PAYFPeriodStartDate,123,0,4,2,4,4) +"</TD>"+
"<TD>" + enum2str(PAYFPrintPayrollBankFile.TransferStatus) +"</TD>"+
"</TR>" );

projid = PAYFPrintPayrollBankFile.Dimension[2];
}

footer = strfmt( "</TABLE>_______________________________________________________________________ "+
"Alert details"+
""+
"<table border= 5 cellSpacing=1 cellPadding=3>"+
"<tbody>"+
"<tr> <td>Date:</td> <td>"+date2str(today(),123,2,4,3,4,4)+"</td></tr>"+
"<tr> <td>Company:</td><td> "+comp+" </td></tr>"+
"<tr> <td>Event:</td><td>"+event+"</td></tr>"+
"</tbody> </table>") ;
}

void sendmail(str to)
{
mailMessage = new System.Net.Mail.MailMessage("ax.alertsender@alfahhad.com.sa",to);
mailmessage.set_Subject(strfmt("PAYROLL BANKFILE NOT TRANSFER ALERTS"));
strBody =header+mainbody+footer;
mailmessage.set_IsBodyHtml(true);
mailmessage.set_Body(strBody);
smtpClient.Send(mailmessage);
}

;
permission.assert();

parameters = SysEmailParameters::find();
mailAddressFrom = new System.Net.Mail.MailAddress("ax.alertsender@alfahhad.com.sa","@FAH878");
strSMTPServer = parameters.SMTPRelayServerName;
sender = "ax.alertsender@alfahhad.com.sa";
smtpClient = new System.Net.Mail.SmtpClient(strSMTPServer);

select * from BatchJob where BatchJob.Caption == "Bankfilealerts";

htmlbody();

/* Send Emails to user*/

if(linenum != 0)
{
while
select userGroupListTO
where userGroupListTO.GroupId == "Bnkfilalrt"
{
Email = "";
Email =SysUserInfo::find(userGroupListTO.UserId).Email;
sendmail(Email);
}
}
}

Download .xpo file

https://drive.google.com/file/d/0ByF76Avzw0r1TnlJUW9ZWm5WYkk/view?usp=sharing

Export to excel sheet in AX

void buttonclicked()
{
Args args;
ReportRun reportRun;

Container con;

;
args = new Args();

con = conins(con,1,ProjIdFilter.text());
con = conins(con,2,PeriodNumber.valueStr());

args.parmObject(NEW containerClass(con));
new MenuFunction(identifierstr(TimeSheetAnalysisExcel), MenuItemType::Action).run(args);

}

download:
https://drive.google.com/file/d/0ByF76Avzw0r1cW0wNGZKUlVDdFE/view?usp=sharing

Import from Excel sheet to AX

public void insertEmplIqamaIdentificationTable()
{
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=0;
#Excel
SysOperationProgress progress;
#AVIFiles

HRMEmplIdentification HRMEmplIdentification,findIdentification,checkrecordexitsornot,checkpassportdetailsexitsornot;
HRMIdentification IqamaIdentification,passIdentification;
PAYFDateHijri ValidUnTillIqamaDateHijri,ActualIqamaDateHijri,ValidUnTillPassDateHijri,ActualPassDateHijri;
HRMIdentificationTypeId IdentificationTypeId;
Emplid emplid;
TransDate ValidUntiliqamaDate,ValidUntilpassDate;
PAYFEmplIdentificationGroup IdentificationGroup;
PAYFEmplIdentificationId IdentificationId;
Projid projid;
Empltable Empltable;

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();

case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);

case (COMVariantType::VT_EMPTY):
return "";

default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}

;
Progress = new SysOperationProgress();
Progress.setCaption("Uploading Employee Details");
Progress.setAnimation(#AviStopWatch);

dialog = new Dialog("Excel Upload Details");
dialogFilename = dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Excel Upload Employee Details");
dialogFilename.value(filename);
if(!dialog.run())
return;
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();

do
{
try
{
ttsbegin;
row++;
IqamaIdentification = COMVariant2Str(cells.item(row+1, 9).value());
ValidUnTillIqamaDateHijri = COMVariant2Str(cells.item(row+1, 2).value());

ValidUntiliqamaDate = global::toGregorianDateNew(ValidUnTillIqamaDateHijri);
ActualIqamaDateHijri = CalendarDateConvert::construct().toHijriDateOld(ValidUntiliqamaDate);

ValidUnTillPassDateHijri = COMVariant2Str(cells.item(row+1, 3).value());

ValidUntilpassDate = global::toGregorianDateNew(ValidUnTillPassDateHijri);

ActualPassDateHijri = ValidUnTillPassDateHijri;
passIdentification = COMVariant2Str(cells.item(row+1, 4).value());

if(IqamaIdentification)
{
Empltable = empltable::findEmplIqamaNo(IqamaIdentification,true);

if(Empltable.RecId != 0)
{
checkrecordexitsornot = HRMEmplIdentification::findEmplIdentBytypeId(Empltable.EmplId,"Iqama",true);

if(checkrecordexitsornot.RecId == 0 || checkrecordexitsornot.ValidUntilDateHijri != ActualIqamaDateHijri)
{
HRMEmplIdentification.Identification = IqamaIdentification;
HRMEmplIdentification.ValidUntilDate = ValidUntiliqamaDate;
HRMEmplIdentification.ValidUntilDateHijri = ActualIqamaDateHijri;

HRMEmplIdentification.EmplId =Empltable.EmplId;
HRMEmplIdentification.ProjId =EMpltable.Dimension[2];
HRMEmplIdentification.IdentificationTypeId = "Iqama";

HRMEmplIdentification.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
HRMEmplIdentification.IdentificationId =Empltable.EmplId;
HRMEmplIdentification.insert();

}

//check passport details exits or not in HRMemplIdentification table
checkpassportdetailsexitsornot = HRMEmplIdentification::findEmplIdentBytypeId(Empltable.EmplId,"Passport",true);
if(checkpassportdetailsexitsornot.RecId != 0 && checkpassportdetailsexitsornot.ValidUntilDateHijri == ActualPassDateHijri
&& checkpassportdetailsexitsornot.Identification == passIdentification )
{
checkpassportdetailsexitsornot.Identification = passIdentification;
checkpassportdetailsexitsornot.ValidUntilDate = ValidUntilpassDate;
checkpassportdetailsexitsornot.ValidUntilDateHijri = ActualPassDateHijri;

checkpassportdetailsexitsornot.EmplId =Empltable.EmplId;
checkpassportdetailsexitsornot.ProjId =EMpltable.Dimension[2];
checkpassportdetailsexitsornot.IdentificationTypeId = "Passport";

checkpassportdetailsexitsornot.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
checkpassportdetailsexitsornot.IdentificationId =Empltable.EmplId;
checkpassportdetailsexitsornot.doUpdate();
}
else
{
checkpassportdetailsexitsornot.Identification = passIdentification;
checkpassportdetailsexitsornot.ValidUntilDate = ValidUntilpassDate;
checkpassportdetailsexitsornot.ValidUntilDateHijri = ActualPassDateHijri;

checkpassportdetailsexitsornot.EmplId =Empltable.EmplId;
checkpassportdetailsexitsornot.ProjId =EMpltable.Dimension[2];
checkpassportdetailsexitsornot.IdentificationTypeId = "Passport";

checkpassportdetailsexitsornot.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
checkpassportdetailsexitsornot.IdentificationId =Empltable.EmplId;
checkpassportdetailsexitsornot.doinsert();
}
}
else
{
Error(strfmt("Upload Failed of iqama No %1 ,because employee details does not exits in employee master table.",IqamaIdentification));
}
}
type = cells.item(row+2, 1).value().variantType();
progress.setText(strfmt("Uploading Employee New Iqama / passport Details %1",row+1));
ttscommit;
}

catch
{
Error(strfmt("Upload Failed in row %1 iqama No %2", row+1,IqamaIdentification));
}

}
while (type != COMVariantType::VT_EMPTY );
info(strfmt("Employee New Iqama Details uploaded successfully"));
progress.kill();
application.quit();

}

Convert Hijri To Gregorian in ax 2009

date toGregorianDateNew(PAYFDateHijri _hijriDate)
{
date ret;
str retStr;
HijriToGregorian.HijriToGregorian convert;
CLRObject clr; // Define a CLRObject object.
InteropKind interopKind;
;
clr = new HijriToGregorian.HijriToGregorian();
convert = clr;

retStr = convert.HijriToGreg(_hijriDate);

ret = str2date(retstr,321);
return ret;
}

example :

toGregorianDateNew("1460/05/13");

Download:

https://drive.google.com/file/d/0ByF76Avzw0r1eFVSUEpiRk9Kdms/view?usp=sharing

Check user group history In AX

static void checkUserGroup(Args _args)
{
UserGroupList UserGroupList;
UserInfo UserInfo;
;

while select UserGroupList
where UserGroupList.groupId == "UsergroupId"
{
info(strfmt('| Users - %1 | modifiedBy - %2 | modify datetime - %3 |', UserGroupList.userId,UserGroupList.createdBy,UserGroupList.createdDateTime));
}

}

Copy User group Record level security to another user group In AX

static void AXCopyUsergroupRLS(Args _args)
{
SysRecordLevelSecurity SysRecordLevelSecurity,insertRLS;
dialogfield srcdf,destdf;
dialog dialog;
userGroupid src,dest;
;
dialog = new dialog("copying user group's Record Level security ");
srcdf = dialog.addField(typeid(userGroupid),"Source");
destdf = dialog.addField(typeid(userGroupid),"Destination");

if(dialog.run())
{
src = srcdf.value();
dest = destdf.value();
if(src!="" && dest !="")
{
ttsbegin;
info("Copying RLS to destination user group");
while select * from SysRecordLevelSecurity
where SysRecordLevelSecurity.groupId == src
{

insertRLS.data(SysRecordLevelSecurity);
insertRLS.groupId = dest;
insertRLS.insert();

}
ttscommit;
info("User groups RLS has been copied!");
}
}
else
{
info("Canceled by user");
}

}

Copy one user permissions group to another user in AX

static void AXcopyUserGroup(Args _args)
{
usergrouplist usergrouplist,ins;
userid src,dest;
dialogfield srcdf,destdf;
dialog dialog;
;
dialog = new dialog("copying user's user groups");
srcdf = dialog.addField(typeid(userid),"Source");
destdf = dialog.addField(typeid(userid),"Destination");

if(dialog.run())
{
src = srcdf.value();
dest = destdf.value();
if(src!="" && dest !="")
{
ttsbegin;
while select * from usergrouplist
where usergrouplist.userId == dest
{
info(strfmt("Before update: '%1'-'%2'",usergrouplist.userid,usergrouplist.groupId));
}

info("Deleting user groups from destination user");
delete_from usergrouplist
where usergrouplist.userId == dest;

info("Copying user groups to destination user");
while select * from usergrouplist
where usergrouplist.userId == src
{
info(strfmt("source user: '%1'-'%2'",usergrouplist.userid,usergrouplist.groupId));
ins.clear();
ins.data(usergrouplist);
ins.UserId = dest;
ins.insert();
}
ttscommit;
info("User groups has been copied!");
}
}
else
{
info("Canceled by user");
}

}

Copy one UserGroup Users list to another user group in AX

static void AXcopyUserGroupUserlist(Args _args)
{
usergrouplist usergrouplist,ins,exitslist;
userGroupid src,dest;
dialogfield srcdf,destdf;
dialog dialog;
;
dialog = new dialog("copying user group's user lists ");
srcdf = dialog.addField(typeid(userGroupid),"Source");
destdf = dialog.addField(typeid(userGroupid),"Destination");

if(dialog.run())
{
src = srcdf.value();
dest = destdf.value();
if(src!="" && dest !="")
{
ttsbegin;

info("Copying user groups USER ID to destination user GROUP ");
while select * from usergrouplist
where usergrouplist.groupId == src
{
select exitslist where exitslist.UserId == usergrouplist.userId
&& exitslist.groupId == dest;

if(exitslist.RecId == 0)
{
ins.clear();
ins.userId = usergrouplist.userId;
ins.groupId = dest;
ins.insert();
}

}
ttscommit;
info("User groups has been copied!");
}
}
else
{
info("Canceled by user");
}

}

SYED IRFAN's Blogs on Dynamic AX