AX 2009/2012 : Deployment of web service on IIS

How to deploy a web service on IIS :

• Open your We Service Application project. Go to Solution Explorer and right click on your Web Service Project. Select “Publish” from the drop down menu.

•  A “Publish Web” window will appear.

Rename the Publish Profile name from “Profile1” to “MyFirstWebServiceProfile”. Select “File System” as Publish Method. And give the target location to publish the Web Service. I am keeping target location under “C:\inetpub\wwwroot\MyFirstPublishedWebService”.

Click on Publish button. The Web Service Application is now published.

•  You will find following files and folder in your published folder.

i)             bin folder – this folder contains the .dll file.

ii)           Web.Config file

iii)          Service1.asmx file

•  Now open IIS (Internet Information Service). Click here to know how to install and enable the IIS 7 in your Windows 7 machine.

Here, you can see your published folder under “Default Web Site”.  We will need to convert it to application.

•   Right click on the published folder and select “Convert to Application”.

 

You can provide different Alias and application pool. The application pool must have a Read Access to the physical path.

Hitting on Ok button will publish your Web Service in IIS.

Note : Select the required application pool rather than selecting "Default application pool" .

• Now you will need to enable the Directory Browsing of our newly created Web Service Application. While selecting “MyFirstPublishedWebService” application double click on “Directory Browsing”.

• Click on enable to enable the Directory Browsing.

Now restart the IIS to take effect the changes.

AX 2009 : ODBC connection in x++

void ABC_ODBCConnection()
{

    LoginProperty                   loginProperty;
    OdbcConnection                  odbcConnection;
    Statement                       statement,updateStmt;
    ResultSet                       resultSet;
    Notes                           sql,values;
    SqlStatementExecutePermission   perm;

    IntegrationParameters   Parameters = IntegrationParameters::find();
    PaymSchedule  PaymSch; //table instance
    ;
   //Create SQL command,
  sql = "USE PwC_Sugar_DataToSugar";
  sql = "INSERT INTO PaymentDetails";
  sql +="\n"+"  (PaymId,SalesId,PaymRefernce,Accepted,AccepetedBy,PaymStatus,DateOfAction,ClearanceDate,ClearanceStatus,ClearanceAmount,Remarks,AXRecId,ProcessedByAX,Entity)";
  sql +="\nVALUES"+values+";";
 //sql +="\nGO";


    //Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setServer(Parameters.SQLServerIPName);
    loginProperty.setDatabase(Parameters.AXToSugarDB);

     try
     {
            //TTSBEGIN;
            odbcConnection = new OdbcConnection(loginProperty);
            if(odbcConnection)
            {              
                    perm = new SqlStatementExecutePermission(sql);
                    perm.assert();
                    statement = odbcConnection.createStatement();
                    statement.executeUpdate(sql);
                    statement.close();

                    update_recordset PaymSch setting
                    CorrectPaymSchd = NoYes::No
                    where PaymSch.CorrectPaymSchd == NoYes::Yes;

                    info("Correction are made successfully.");
               
             }

     }
     Catch(Exception::Error)
      {
         ttsabort;
         error("Error Occured while Payment Schedule Processing");
      }

}

AX 2009 : Read CSV file in x++

static void ReadCSVFile(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogFileName;
    SysOperationProgress    simpleProgress;
    Filename                filename;
    CommaIo                 csvFile;
    container               readCon;
    Container               filterCriteria;
    int                     numLines;
    int                     cnt;
    FileIOPermission        permission;
    TextIO                  textIO;
    Str                     _taskStat;

    SugarRecordId       _sugarRecId; //field variable
    PaymSchedule        _paymentSchedule; // Table instance
    PaymScheduleHistory _paymScheduleHistory; //Table instance
    textBuffer tb = new textBuffer();
    #File
    #avifiles
    ;
    //salesTable = _args.record();

    dialog = new Dialog("Importing Text File");
    dialogFileName = dialog.addField(typeid(Filenameopen), "File Name");
    filterCriteria = ['*.csv'];
    filterCriteria = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();

    if (dialog.run())
    filename = dialogFileName.value();

    if(!filename)
    {
        info("Filename must be filled");
        throw(" ");
    }

    permission = new fileIOpermission(filename,#io_read);
    permission.assert();
    textIO = new TextIO(filename,#io_read);
    if (!textIO)
    {
        throw error("Error reading file");//@ABC4");
    }
    tb.fromFile(filename);//File name  with Path ...
    numLines = tb.numLines();
    csvFile = new CommaIo(filename, 'r');
    csvFile.inFieldDelimiter("  "); // Delimiter...
    simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, 'Importing data'/*@ABC5'*/,1000);

    if (csvFile)//Checking for csv file.
    {
        readCon = csvFile.read();//reading the file.
        ttsbegin;
        for(cnt=1;cnt<=numLines;cnt++)
        {
            readCon     = str2Con(tb.nextToken(true));
            if(cnt>1)
            {
               _sugarRecId = conpeek(readCon,9);
               _paymentSchedule = PwC_PaymSchedule::findBySugarRecId(_sugarRecId);
               _paymScheduleHistory = AUR_PaymScheduleHistory::findBySugarRecId(_sugarRecId);
               if(_paymentSchedule)
               {
                    delete_from _paymentSchedule where _paymentSchedule.SugarRecordId == _sugarRecId;

               }
               if(_paymScheduleHistory)
               {
                    select forupdate _paymScheduleHistory where _paymScheduleHistory.SugarRecordId == _sugarRecId;
                     _paymScheduleHistory.SugarRecordId = _sugarRecId;
                    _paymScheduleHistory.SalesId = conpeek(readCon,1);
                    _paymScheduleHistory.PaymMode = conpeek(readCon,2);
                    _paymScheduleHistory.PaymReference = conpeek(readCon,3);
                    _paymScheduleHistory.InstrumentDate = str2date(conpeek(readCon,4),213);
                    _paymScheduleHistory.InstrumentCurrencyCode = conpeek(readCon,5);
                    _paymScheduleHistory.InstrumentAmount = conpeek(readCon,6);
                    _paymScheduleHistory.BankDetails = conpeek(readCon,7);
                    //_paymScheduleHistory.dataAreaId = conpeek(readCon,8);

                    _paymScheduleHistory.Accepted = conpeek(readCon,10);
                    _paymScheduleHistory.ClearedAmount = conpeek(readCon,11);
                    _paymScheduleHistory.ClearanceStatus = conpeek(readCon,12);
                    _paymScheduleHistory.PaymType = conpeek(readCon,14);
                    _paymScheduleHistory.PaymId = conpeek(readCon,15);
                    _paymScheduleHistory.Remarks = conpeek(readCon,16);
                    _paymScheduleHistory.AUR_Reason = conpeek(readCon,17);

                    _paymScheduleHistory.update();

                    _taskStat = strfmt("History updated");
               }
               else if(_paymentSchedule && !_paymScheduleHistory)
               {
                    //_paymScheduleHistory.SugarRecordId = _sugarRecId;

                    _paymScheduleHistory.SalesId = conpeek(readCon,1);
                    _paymScheduleHistory.PaymMode = conpeek(readCon,2);
                    _paymScheduleHistory.PaymReference = conpeek(readCon,3);
                    _paymScheduleHistory.InstrumentDate = str2date(conpeek(readCon,4),213);
                    _paymScheduleHistory.InstrumentCurrencyCode = conpeek(readCon,5);
                    _paymScheduleHistory.InstrumentAmount = conpeek(readCon,6);
                    _paymScheduleHistory.BankDetails = conpeek(readCon,7);
                    //_paymScheduleHistory.dataAreaId = conpeek(readCon,8);
                    _paymScheduleHistory.SugarRecordId = _sugarRecId;

                    _paymScheduleHistory.Accepted = conpeek(readCon,10);
                    _paymScheduleHistory.ClearedAmount = conpeek(readCon,11);
                    _paymScheduleHistory.ClearanceStatus = conpeek(readCon,12);
                    _paymScheduleHistory.PaymType = conpeek(readCon,14);
                    _paymScheduleHistory.PaymId = conpeek(readCon,15);
                    _paymScheduleHistory.Remarks = conpeek(readCon,16);
                    _paymScheduleHistory.AUR_Reason = conpeek(readCon,17);

                    _paymScheduleHistory.insert();

                    _taskStat = strfmt("History inserted");
               }
               //info(_paymentSchedule.SugarRecordId);
            }
        }
        info(_taskStat);
        ttscommit;

    }

}

AX 2009 : Exporting data in Excel

static void SalesOrderDump(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   SalesLine   _salesLine;
   SalesTable   _salesTable;
   Query                             q;
   QueryBuildDataSource    qbds;
   QueryBuildRange            qbr;
   QueryRun                       qr;
   int                  row = 1;
   FileName                fileName;
   PaymSchedule                     _paymSchedule;
   FileNameFilter      filter = ["Excel file", "*.xlsx"];
   ;
   fileName = winapi::getSaveFileName(infolog.hWnd(), filter , @"c:\...\desktop", "Save as Excel file","xlsx","Sales Order");

   //fileName = "C:\\Test4.xlsx";
   //fileName = @"C:\Users\jagadis\Desktop\tes3.xlsx";
   //fileName = "C:\\Users\\arvind\\Desktop\\FilterSheet_AR.xlsx";

   if(!fileName)
    return;

    //Check whether the document already exists
   if(WinApi::fileExists(fileName))
      throw error("File already exist");
      //WinApi::deleteFile(fileName);

   //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();

   //Open Excel document
   //xlsApplication.visible(true);


   //Create Excel WorkBook and WorkSheet
   xlsWorkBookCollection    = xlsApplication.workbooks();
   xlsWorkBook              = xlsWorkBookCollection.add();
   xlsWorkSheetCollection   = xlsWorkBook.worksheets();
   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);


   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("Sales Order");
   xlsWorkSheet.cells().item(row,2).value("Customer account");
   xlsWorkSheet.cells().item(row,3).value("Invoice account");
   xlsWorkSheet.cells().item(row,4).value("Status");
   xlsWorkSheet.cells().item(row,5).value("Item number");
   xlsWorkSheet.cells().item(row,6).value("Unit price");
   xlsWorkSheet.cells().item(row,7).value("Net Amount");

   row++;

    q                = new Query();
    qbds             = q.addDataSource(tablenum(SalesLine));
    qr                = new QueryRun(q);

   //Fill Excel with CustTable AccountNum and Name fields (only 10 records)
    while(qr.next())
    {
      _salesLine = qr.get(tablenum(SalesLine));
      //if(row == 5)
      //  break;
      xlsWorkSheet.cells().item(row,1).value(_salesLine.SalesId);
      xlsWorkSheet.cells().item(row,2).value(_salesLine.CustAccount);
      xlsWorkSheet.cells().item(row,3).value(_salesLine.invoiceAccount());
      xlsWorkSheet.cells().item(row,4).value(enum2str(_salesLine.SalesStatus));
      xlsWorkSheet.cells().item(row,5).value(_salesLine.ItemId);
      xlsWorkSheet.cells().item(row,6).value(_salesLine.SalesPrice);
      xlsWorkSheet.cells().item(row,7).value(_salesLine.LineAmount);


      row++;


   }


   //Save Excel document
   xlsWorkbook.saveAs(fileName);

   //Open Excel document
   xlsApplication.visible(true);

   info("Success");

   //Close Excel
   //xlsApplication.quit();
   //xlsApplication.finalize();
}