How to Use Ajax AsyncFileUpload in Asp.Net

Description:-

In this article will create Ajax AsyncFileUpload Control in Our Webpage to Upload File in Folder and Display after Upload. So we can Upload Image and after Upload we can see image using Ajax AsyncFileUpload Control.

Design your Webpage like below.

<div>
   <asp:ScriptManager ID="ScriptManager1" runat="server">
   </asp:ScriptManager>
   <cc1:AsyncFileUpload OnClientUploadComplete="uploadComplete" runat="server" ID="AsyncFileUpload1" Width="400px" UploaderStyle="Modern" CompleteBackColor="White" UploadingBackColor="#CCFFFF" ThrobberID="imgLoader" OnUploadedComplete="FileUploadComplete" OnClientUploadStarted="uploadStarted" />
   <asp:Image ID="imgLoader" runat="server" ImageUrl="~/images/loader.gif" />
   <br />
   <br />
   <img id="imgDisplay" alt="" src="" style="display: none" />
</div>

Now Create Script for Upload Start and Upload Complete for see Image after Upload.

<script type="text/javascript">
        function uploadStarted() {
            $get("imgDisplay").style.display = "none";
        }
        function uploadComplete(sender, args) {
            var imgDisplay = $get("imgDisplay");
            imgDisplay.src = "images/loader.gif";
            imgDisplay.style.cssText = "";
            var img = new Image();
            img.onload = function () {
                imgDisplay.style.cssText = "height:100px;width:100px";
                imgDisplay.src = img.src;
            };
            img.src = "<%=ResolveUrl(UploadFolderPath) %>" + args.get_fileName(); ;
        }
</script>

Now Generate OnUploadedComplete Property of AsyncFileUpload and Code for Save File in Folder.

protected void FileUploadComplete(object sender, EventArgs e)
{
   string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
   AsyncFileUpload1.SaveAs(Server.MapPath(this.UploadFolderPath) + filename);
}

Now run you Application and Upload File through Ajax AsyncFileUpload Control and after Upload you can see Uploaded image.

How to Check UserDefine or SystemDefine field in table in Ax 2012

Description:-

In this Example we will see how many field are user define and how many fields are system define in table. By achieving this functionality we will check by job in ax 2012.

Let’s Create Table and add some field in table.

Create Table Name it “A_Student”.

• Expand AOT Node.
• Open Data Dictionary Node.
• Select Tables and right Click Select New Table.
• Name it “A_Student”.
• Now open table in Insert Some Data in A_Student table.

Now create job and code for check how many field are user define and how many fields are system define in table.

Create Job from AOT

1. In the AOT, click Job.
2. Right-click the Job node, and then select New Job.

static void SystemClassesFields(Args _args)
{
    SysDictTable dictTable;
    DictField dictField;
    Common common;
    Counter counter;
    ;
    dictTable = new SysDictTable(tableNum(A_PO));
    for (counter=1; counter<=dictTable.fieldCnt(); counter++)
    {
     dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(counter));
     if (dictField.isSystem())
     {
       info(strfmt("System field: %1", dictField.label()));
       info(strfmt("System field: %1", dictField.name()));
     }
     else
       info(strfmt("User field: %1", dictField.label()));
     }
     if(dictField.isSql())
     {
      info(strfmt("sql field: %1", dictField.label()));
    }
}

Now run your job by pressing F5 and their you can see fields Information.

Generate password protected pdf file dynamically in Asp.net

Description:-

Here we will create Password protected Pdf file Dynamically from Gridview Data. PDF File is very useful to read data and we can see clearly in Pdf file. So we can Generate Pdf File Dynamically and set Password as default when we create dynamically in server. Here is the simple code to achieve this functionality in dot net.

Create Table:-

CREATE TABLE [dbo].[Employee](

      [Number] [int] IDENTITY(1,1) NOT NULL,

      [Name] [varchar](50) NOT NULL,

      [Gender] [varchar](50) NOT NULL,

      [Email] [varchar](50) NOT NULL,

      [MobileNumber] [bigint] NOT NULL,

      [Bdate] [date] NULL,

 CONSTRAINT [PK_Employeee] PRIMARY KEY CLUSTERED

(

      [Number] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

Default.aspx:-

<div>
   <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal" Height="123px" Width="594px">
      <Columns>
         <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="80px">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle Width="80px" VerticalAlign="Middle"></ItemStyle>
         </asp:BoundField>
         <asp:BoundField DataField="Gender" HeaderText="Gender" ItemStyle-Width="80px">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle Width="80px" ></ItemStyle>
         </asp:BoundField>
         <asp:BoundField DataField="Email" HeaderText="Email" ItemStyle-Width="80px">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle Width="80px"></ItemStyle>
         </asp:BoundField>
         <asp:BoundField DataField="MobileNumber" HeaderText="MobileNumber" ItemStyle-Width="60px">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle Width="60px"></ItemStyle>
         </asp:BoundField>
         <asp:BoundField DataField="BDate" HeaderText="BDate" ItemStyle-Width="100px">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle Width="120px"></ItemStyle>
         </asp:BoundField>
      </Columns>
      <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
      <HeaderStyle BackColor="#333333" ForeColor="White" Font-Bold="True"></HeaderStyle>
      <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
      <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
      <SortedAscendingCellStyle BackColor="#F7F7F7" />
      <SortedAscendingHeaderStyle BackColor="#4B4B4B" />
      <SortedDescendingCellStyle BackColor="#E5E5E5" />
      <SortedDescendingHeaderStyle BackColor="#242121" />
   </asp:GridView>
   <br />
   <asp:Button ID="btnExport" runat="server" Text="Export To PDF" OnClick="ExportToPDF" />
</div>

Default.aspx.cs:-

Now Go to Code behind and Create Method for Bind Gridview when page load first time in browser. And Call in Page_Load () method.

private void BindGrid()
{
    string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("select * FROM Employee"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

Page_load () method when page call first time in browser or else it is not Postback.

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    this.BindGrid();
  }
}

Now generate ExporttoPdf button click event and code for Generate Password Protected Pdf file.

protected void ExportToPDF(object sender, EventArgs e)
{
  using (StringWriter sw = new StringWriter())
  {
    using (HtmlTextWriter hw = new HtmlTextWriter(sw))
    {
      //To Export all pages
      GridView1.AllowPaging = false;
      this.BindGrid();

      GridView1.RenderControl(hw);
      StringReader sr = new StringReader(sw.ToString());
      Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
      HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
      using (MemoryStream memoryStream = new MemoryStream())
      {
        PdfWriter.GetInstance(pdfDoc, memoryStream);
        pdfDoc.Open();
        htmlparser.Parse(sr);
        pdfDoc.Close();
        byte[] bytes = memoryStream.ToArray();
        memoryStream.Close();
        using (MemoryStream input = new MemoryStream(bytes))
        {
          using (MemoryStream output = new MemoryStream())
          {
            string password = "Umesh";
            PdfReader reader = new PdfReader(input);
            PdfEncryptor.Encrypt(reader, output, true, password, password, PdfWriter.ALLOW_SCREENREADERS);
            bytes = output.ToArray();
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-disposition", "attachment;filename=" + "Export_" + DateTime.Now.ToShortDateString() + "_" + DateTime.Now.ToString("T") + ".pdf");
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.BinaryWrite(bytes);
            Response.End();
          }
        }
      }
    }
  }
}

Here I have Set Password “Umesh” and Name it When Pdf file Export like "Export_Todate.Pdf". When you Run and Export file from Gridview you can see in your Downloaded file.

Now Click on Export to Pdf button and Download Pdf File and Open Pdf file.

Now Enter Password and see Data from that Pdf File.

How to import BOM (Bills of materials) through code

What Is BOM ?

You can create different versions of an existing bill of materials (BOM). You can then attach the BOM to one or more items.  

Create a BOM

1. Click Inventory management > Common > Bills of materials.
   
2. Press CTRL+N to create a new BOM.
   
3. Enter information to identify the BOM.
   
4. Click Lines to open the BOM line form.
   
5. Select the item number, and enter the quantity to create additional BOM lines for any component items that make up the BOM.
   
6. Close the form.
   
7. Click Approve to open the Approve bill of materials form.
   
8. Select an employee name, and then click OK. 

Create a BOM version

1. To create another version of the new BOM, press CTRL+N in the Versions grid to create a new line.
   
2. Select the relevant item number, and enter dates in the From date and To date fields to control the validity period of the BOM version. Use the From qty. field to set a minimum item quantity for using the BOM version.
   
3. Click Approve to approve the BOM version.
   
4. Select the Active check box to make the BOM version active.

 

Here I have create code for import BOM through code. for import if you have single BOMTable line, Single BOM version line, and Multiple BOM lines then you can import it or else through code changes you can also change import code how to import and what format you need to import BOM.

 

Import Code:

 

    #AviFiles
    #File
    COM                                 excelCOM;
    SysOperationProgress                simpleProgress;
    int                                 lastRow;
    FileIOPermission                    permission;
    SysOperationProgress                progress = new SysOperationProgress();
    SysExcelApplication                 application;
    SysExcelWorkbooks                   workbooks;
    SysExcelWorkbook                    workbook;
    SysExcelWorksheets                  worksheets;
    SysExcelWorksheet                   worksheet;
    SysExcelWorkSheet                   workSheetInventTableModule;
    SysExcelCells                       cellsInventTableModule;
    SysExcelCells                       cells;
    COMVariant                          file;
    COMVariantType                      type,type1,type2;
    COMVariantType                      typeModule;
    int                                 row,countinsert,row1;
    FileName                            fileName;
    Container                           filterCriteria;
    FileIoPermission                    perm;
    dialogField                         dialogFilename;
    dialog                              dialog;
    NoYes                               NoYes;
    boolean                             VefiryFlag,ImportData;
    str 60                              Vefiry;
    int                                 ii;

    dev_ApplBomTemplate                 Objdev_ApplBomTemplate;
    BOM                                 ObjBOM,ObjBOM1,ObjBOM2;
    BOMTable                            ObjBOMTable,ObjBOMTable1;
    InventDim                           ObjInventDim;
    BOMVersion                          ObjBOMVersion,ObjBOMVersion1;
    InventSite                          ObjInventSite;
    dev_ImportedCV                      Objdev_ImportedCV;
    BOMType                             BOMType;

    Query                               query;
    QueryRun                            queryRun;
    QueryBuildDataSource                qbds;
    QueryBuildRange                     qbr,qbr1,qbr2,qbr3,qbr4,qbr5,qbr6;

    str                                 A,B,C,D,E,G,H,I,J,K,L,P,R,S,T,U,V,W,X,AA,AB,AC,AD,AE;
    real                                F,Q,O,Y,Z;
    date                                M,N;
    boolean                             FlagBOMTable,FlagBOMVersion,FlagBOM1Line,FlagBOMOtherLine;
    str WorkerId;
    super();

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    file    = new COMVariant();

    //Dialog Start
    dialog = new dialog();
    dialog.caption("select a file for BOM import");
    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"File Name");
    filterCriteria = ['xlsx','*.xlsx','xls','*.xls'];
    filterCriteria = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();
    if(dialog.closedOk())
    {
        filename = dialogFileName.value();
    }
    //Dialog end

    //Permission start
    permission = new fileIOpermission(filename,#io_read);
    permission.assert();
    //Permission end
    //Workbook open start
    try
    {
        workbooks.open(fileName);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    //Workbook open end

    //worksheet start
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    if (worksheet)
    {
        cells = worksheet.cells();
    }
    else
    {
        throw warning('File cannot be opened.');
    }
    //worksheet end

    //Select last row of excel sheet start
    excelCOM = cells.comObject();
    excelCOM = excelCOM.SpecialCells(11);
    lastRow = excelCOM.Row();
    //Select last row of excel sheet end

    simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, 'Reading data from ExcelSheet...', lastRow);
    VefiryFlag = true;
    ImportData = true;
    //main try catch start
    ttsBegin;
    try
    {
        //Varify Excel sheet columns Start
        if(VefiryFlag)
        {
            row++;
            //ttsbegin;
            //Progessbar for verifying start
            simpleProgress.incCount();
            simpleprogress.setText("Varifying Excel sheet");
            simpleprogress.update(true);
            //Progessbar for verifying end

            //fatching fields start
            if (row == 1)
            {
                Vefiry = any2str(cells.item(row, ii+1).value().bStr());
                while select * from Objdev_ApplBomTemplate
                    order by Objdev_ApplBomTemplate.SerialNo asc
                {
                    if(Objdev_ApplBomTemplate.Name == Vefiry)
                    {
                        ii++;
                        Vefiry = any2str(cells.item(row, ii+1).value().bStr());
                    }
                    else
                    {
                        warning(strFmt("Excel Not Match from %1th row",ii));
                        VefiryFlag = false;
                        application.quit();
                        break;
                    }
                }
            }
            //fatching fields end
            //ttscommit;
        }
        //Varify Excel sheet columns end

        //Importing Item Start
        if(ImportData == true && VefiryFlag == true)
        {
            row = 1;
            //ttsbegin;
            //do while start
            do
            {
                row++;

                //Clear objects start
                A='';B='';C='';D='';E='';G='';H='';I='';J='';K='';L='';P='';R='';S='';T='';U='';V='';W='';X='';AA='';AB='';AC='';AD='';AE='';
                F=0;Q=0;O=0;Y=0;Z=0;
                M=datenull(); N=datenull();
                Objdev_ApplBomTemplate.clear();
                ObjBOM.clear();
                ObjBOMTable.clear();
                ObjInventDim.clear();
                ObjBOMVersion.clear();
                ObjInventSite.clear();
                //qbds.clearRanges();
                //Clear objects end

                //progressbar for importing Item start
                simpleProgress.incCount();
                simpleprogress.setText(strfmt("Generated BOM - %1 remaining %2", row-1,lastRow-row));
                simpleprogress.update(true);
                //progressbar for importing Item end

                //importing Item by rows start
                if (row > 1)
                {
                    //Fatching data from Excel start
                    A = cells.item(row, 1).value().bStr();//Sr No.
                    B = any2str(cells.item(row, 2).value().bStr());//BOM Identification
                    C = any2str(cells.item(row, 3).value().bStr());//BOM Name
                    D = any2str(cells.item(row, 4).value().bStr());//Site
                    E = any2str(cells.item(row, 5).value().bStr());//Item Group
                    F = cells.item(row, 6).value().double();//Approved by
                    G = any2str(cells.item(row, 7).value().bStr());//Approved (Yes/No)
                    H = any2str(cells.item(row, 8).value().bStr());//BOM Item Number
                    AE = any2str(cells.item(row, 9).value().bStr());//BOM Item Configuration
                    I = any2str(cells.item(row, 10).value().bStr());//BOM Item Size
                    J = any2str(cells.item(row, 11).value().bStr());//BOM Item Color
                    K = any2str(cells.item(row, 12).value().bStr());//BOM Item Style
                    L = any2str(cells.item(row, 13).value().bStr());//BOM Item Site
                    M = cells.item(row, 14).value().date();//From Date
                    N = cells.item(row, 15).value().date();//To Date
                    O = cells.item(row, 16).value().double();//From Qty.
                    P = any2str(cells.item(row, 17).value().bStr());//Active (Yes/No)
                    Q = cells.item(row, 18).value().double();//Approved by
                    R = any2str(cells.item(row, 19).value().bStr());//Approved (Yes/No)
                    S = any2str(cells.item(row, 20).value().bStr());//Item Number
                    T = any2str(cells.item(row, 21).value().bStr());//Configuration
                    U = any2str(cells.item(row, 22).value().bStr());//Size
                    V = any2str(cells.item(row, 23).value().bStr());//Color
                    W = any2str(cells.item(row, 24).value().bStr());//Style
                    X = any2str(cells.item(row, 25).value().bStr());//Warehouse
                    Y = cells.item(row, 26).value().double();//Per Series
                    Z = cells.item(row, 27).value().double();//Quantity
                    AA = any2str(cells.item(row, 28).value().bStr());//Unit
                    AB = any2str(cells.item(row, 29).value().bStr());//Line Type (Item / Pegged supply)
                    AC = any2str(cells.item(row, 30).value().bStr());//Sub-BOM formula
                    AD = any2str(cells.item(row, 31).value().bStr());//Configuration Group
                    //Fatching data from Excel end

                    //Item Import start
                    try
                    {
                        //BOM Table insert start
                        //Code for import start
                        ObjBOMTable.clear();
                        ObjBOMTable.initValue();
                        //Bom identification start
                        if(B!=''){ObjBOMTable.BOMId = B;}
                        //Bom identification end

                        //BOM name start
                        if(C!=''){ObjBOMTable.Name = C;}
                        //BOM name end

                        //BOM Site start
                        if(D!='')
                        {
                            if(InventSite::find(D).SiteId == D){ObjBOMTable.SiteId = InventSite::find(D).SiteId;}
                        }
                        //BOM Site end

                        //BOM Item group start
                        if(E!='')
                        {
                            if(InventItemGroup::find(E).ItemGroupId == E){ObjBOMTable.ItemGroupId = InventItemGroup::find(E).ItemGroupId;}
                        }
                        //BOM Item group end

                        //BOM Approver start
                        if(F!=0)
                        {
                            if(HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(F)),6, "0")).PersonnelNumber)
                            {
                                ObjBOMTable.Approver = HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(F)),6, "0")).RecId;
                                ObjBOMTable.Approved = NoYes::Yes;
                            }
                            else{ObjBOMTable.Approved = NoYes::No;}
                        }
                        else{ObjBOMTable.Approved = NoYes::No;}
                        //BOM Approver end

                        if(ObjBOMTable.validateWrite())
                        {
                            ObjBOMTable.insert();
                        }
                        else
                        {
                            FlagBOMTable = true;
                            //info(strFmt(" from bom table %1",FlagBOMTable));
                        }
                        //BOM Table insert end

                        //BOM version insert start
                        ObjBOMVersion.clear();
                        ObjBOMVersion.initFromBOMTable(ObjBOMTable);
                        ObjBOMVersion.BOMId = ObjBOMTable.BOMId;
                        ObjBOMVersion.initValue();
                        //BOM Item number start
                        if(H!='')
                        {
                            if(InventTable::find(H).ItemId)
                            {
                                ObjBOMVersion.ItemId = H;
                            }
                        }

                        //BOM Item number end

                        //BOM from date start
                        if(M!=systemDateGet())
                        {ObjBOMVersion.FromDate = M;}
                        //BOM from date end

                        //BOM Todate start
                        if(N!=systemDateGet())
                        {ObjBOMVersion.ToDate = N;}
                        //BOM Todate end

                        //BOM From Qty start
                        if(O!=0)
                        {
                            if(O != 1){ObjBOMVersion.FromQty = O;}
                        }
                        //BOM From Qty end

                        //BOM version Configuration,Site,style,colour,size start
                        query =  new Query();
                        qbds = query.addDataSource( tableNum(InventDim));

                        qbr = qbds.addRange(fieldNum(InventDim,InventSiteId));
                        if(L != ''){qbr.value(L);}else{qbr.value(sysquery::valueEmptyString());}

                        qbr1 = qbds.addRange(fieldNum(InventDim,configId));
                        if(AE!=''){qbr1.value(AE);}else{qbr1.value(sysquery::valueEmptyString());}

                        qbr2 = qbds.addRange(fieldNum(InventDim,InventColorId));
                        if(J != ''){qbr2.value(J);}else{qbr2.value(sysquery::valueEmptyString());}

                        qbr3 = qbds.addRange(fieldNum(InventDim,InventLocationId));
                        qbr3.value(sysquery::valueEmptyString());

                        qbr4 = qbds.addRange(fieldNum(InventDim,inventSerialId));
                        qbr4.value(sysquery::valueEmptyString());

                        qbr5 = qbds.addRange(fieldNum(InventDim,InventSizeId));
                        if(I!=''){qbr5.value(queryValue(strFmt("%1",I)));}else{qbr5.value(sysquery::valueEmptyString());}

                        qbr6 = qbds.addRange(fieldNum(InventDim,InventStyleId));
                        if(K!=''){qbr6.value(K);}else{qbr6.value(sysquery::valueEmptyString());}

                        queryRun = new QueryRun(query);
                        while(queryRun.next())
                        {
                            //info(strFmt("main line - %1",query.dataSourceNo(1).toString()));
                            ObjInventDim = queryRun.get(tableNum(inventDim));
                            if(ObjInventDim.inventDimId)
                            {
                                ObjBOMVersion.InventDimId = ObjInventDim.inventDimId;
                            }
                        }
                        qbds.clearRanges();
                        //BOM version Configuration,Site,style,colour,size end

                        //BOM version approved start
                        if(Q!=0)
                        {
                            if(ObjBOMTable.Approved == true)
                            {
                                if(HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(Q)),6, "0")).PersonnelNumber)
                                {
                                    ObjBOMVersion.Approver = HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(Q)),6, "0")).RecId;
                                    ObjBOMVersion.Approved =  true;
                                    //BOM version active start
                                    if(P!='')
                                    {ObjBOMVersion.Active = str2enum(NoYes,P);}
                                    //BOM version active end

                                    //if(ObjBOMVersion.Approver != 0 && ObjBOMVersion.Approved == true)
                                    //{
                                        //update_recordSet ObjBOMVersion1
                                        //setting Approver = ObjBOMVersion.Approver,
                                                //Approved = ObjBOMVersion.Approved,
                                                //Active = ObjBOMVersion.Active
                                        //where ObjBOMVersion1.BOMId == ObjBOMVersion.BOMId;
                                    //}
                                }
                                else{ObjBOMVersion.Approved =  false;}
                            }
                        }
                        else{ObjBOMVersion.Approved =  false;}
                        //BOM version approved end

                        if(ObjBOMVersion.validateWrite())
                        {
                            ObjBOMVersion.insert();
                        }
                        else
                        {
                            FlagBOMVersion = true;
                            //info(strFmt(" from bom version %1",FlagBOMVersion));
                        }
                        //BOM version insert end

                        //BOM insert start
                        ObjBOM.clear();
                        ObjBOM.initValue();
                        ObjBOM.BOMId = ObjBOMTable.BOMId;
                        //BOM item start
                        if(S!='')
                        {
                            if(InventTable::find(S).ItemId)
                            {
                                ObjBOM.ItemId = InventTable::find(S).ItemId;
                            }
                        }
                        //BOM item end

                        //BOM per series start
                        if(Y !=0)
                        {
                            if(Y!=1)
                            {
                                ObjBOM.BOMQtySerie = Y;
                            }
                        }
                        //BOM per series end

                        //BOM Quamtity start
                        if(Z != 0)
                        {
                            ObjBOM.BOMQty = Z;
                        }
                        //BOM Quamtity end

                        //BOM Unit start
                        if(AA !='')
                        {
                            if(UnitOfMeasure::findBySymbol(AA).Symbol)
                            {
                                ObjBOM.UnitId = AA;
                            }
                        }
                        //BOM Unit end

                        //BOM Type start
                        if(AB!='')
                        {
                            if(AB == enum2str(BOMType::Item) || AB == enum2str(BOMType::PeggedSupply))
                            {
                                ObjBOM.BOMType = str2enum(BOMType,AB);
                            }
                        }
                        //BOM Type end

                        //BOM Sub-Formula start
                        if(AC!='')
                        {
                            if(ObjBOMTable.BOMId != AC)
                            {
                                ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
                            }
                            else
                            {
                                ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
                            }
                        }
                        //BOM Sub-Formula end

                        //BOM ConfigGroupId start
                        if(AD != '')
                        {
                            if(ConfigGroup::find(AD).ConfigGroupId)
                            {
                                ObjBOM.ConfigGroupId = AD;
                            }
                        }
                        //BOM ConfigGroupId end

                        //BOM Configuration,style,colour,size,warehouse start
                        query =  new Query();
                        qbds = query.addDataSource( tableNum(InventDim));

                        qbr = qbds.addRange(fieldNum(InventDim,InventSiteId));
                        if(D!=''){qbr.value(D);}else{qbr.value(sysquery::valueEmptyString());}

                        qbr1 = qbds.addRange(fieldNum(InventDim,configId));
                        if(T!=''){qbr1.value(T);}else{qbr1.value(sysquery::valueEmptyString());}

                        qbr2 = qbds.addRange(fieldNum(InventDim,InventColorId));
                        if(V != ''){qbr2.value(V);}else{qbr2.value(sysquery::valueEmptyString());}

                        qbr3 = qbds.addRange(fieldNum(InventDim,InventLocationId));
                        if(X!=''){qbr3.value(X);}else{qbr3.value(sysquery::valueEmptyString());}

                        qbr4 = qbds.addRange(fieldNum(InventDim,inventSerialId));
                        qbr4.value(sysquery::valueEmptyString());

                        qbr5 = qbds.addRange(fieldNum(InventDim,InventSizeId));
                        if(U!=''){qbr5.value(queryValue(strFmt("%1",U)));}else{qbr5.value(sysquery::valueEmptyString());}

                        qbr6 = qbds.addRange(fieldNum(InventDim,InventStyleId));
                        if(W!=''){qbr6.value(W);}else{qbr6.value(sysquery::valueEmptyString());}

                        queryRun = new QueryRun(query);
                        while(queryRun.next())
                        {
                            //info(strFmt("Main sublie - %1",query.dataSourceNo(1).toString()));
                            ObjInventDim = queryRun.get(tableNum(inventDim));
                            if(ObjInventDim.inventDimId)
                            {
                                ObjBOM.InventDimId = ObjInventDim.inventDimId;
                            }
                        }
                        qbds.clearRanges();
                        //BOM Configuration,style,colour,size,warehouse end

                        if(ObjBOM.validateWrite())
                        {
                            ObjBOM.insert();
                        }
                        else
                        {
                            FlagBOM1Line = true;
                            //info(strFmt(" from bom 1 line %1",FlagBOM1Line));
                        }
                        //BOM insert end

                        //BOM Line Do While start
                        //S='';T='';U ='';V='';W ='';X='';Y=0;Z=0;AA='';AB='';AC='';AD='';
                        row1 = row;
                        do
                        {
                            S='';T='';U ='';V='';W ='';X='';Y=0;Z=0;AA='';AB='';AC='';AD='';
                            row1++;

                            //Getting eub lines start
                            S = any2str(cells.item(row1, 20).value().bStr());//Item Number
                            T = any2str(cells.item(row1, 21).value().bStr());//Configuration
                            U = any2str(cells.item(row1, 22).value().bStr());//Size
                            V = any2str(cells.item(row1, 23).value().bStr());//Color
                            W = any2str(cells.item(row1, 24).value().bStr());//Style
                            X = any2str(cells.item(row1, 25).value().bStr());//Warehouse
                            Y = cells.item(row1, 26).value().double();//Per Series
                            Z = cells.item(row1, 27).value().double();//Quantity
                            AA = any2str(cells.item(row1, 28).value().bStr());//Unit
                            AB = any2str(cells.item(row1, 29).value().bStr());//Line Type (Item / Pegged supply)
                            AC = any2str(cells.item(row1, 30).value().bStr());//Sub-BOM formula
                            AD = any2str(cells.item(row1, 31).value().bStr());//Configuration Group
                            //Getting eub lines end

                            //importing Item by rows start
                            if (row1 > row)
                            {
                                //BOM Sub line insert start
                                //BOM insert start
                                ObjBOM.clear();
                                ObjBOM.initValue();
                                ObjBOM.BOMId = ObjBOMTable.BOMId;
                                //BOM item start
                                if(S!='')
                                {
                                    if(InventTable::find(S).ItemId)
                                    {
                                        ObjBOM.ItemId = InventTable::find(S).ItemId;
                                    }
                                }
                                //BOM item end

                                //BOM per series start
                                if(Y !=0)
                                {
                                    if(Y!=1)
                                    {
                                        ObjBOM.BOMQtySerie = Y;
                                    }
                                }
                                //BOM per series end

                                //BOM Quamtity start
                                if(Z != 0)
                                {
                                    ObjBOM.BOMQty = Z;
                                }
                                //BOM Quamtity end

                                //BOM Unit start
                                if(AA !='')
                                {
                                    if(UnitOfMeasure::findBySymbol(AA).Symbol)
                                    {
                                        ObjBOM.UnitId = AA;
                                    }
                                }
                                //BOM Unit end

                                //BOM Type start
                                if(AB!='')
                                {
                                    if(AB == enum2str(BOMType::Item) || AB == enum2str(BOMType::PeggedSupply))
                                    {
                                        ObjBOM.BOMType = str2enum(BOMType,AB);
                                    }
                                }
                                //BOM Type end

                                //BOM Sub-Formula start
                                if(AC!='')
                                {
                                    if(ObjBOMTable.BOMId != AC)
                                    {
                                        ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
                                    }
                                    else
                                    {
                                        ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
                                    }
                                }
                                //BOM Sub-Formula end

                                //BOM ConfigGroupId start
                                if(AD != '')
                                {
                                    if(ConfigGroup::find(AD).ConfigGroupId)
                                    {
                                        ObjBOM.ConfigGroupId = AD;
                                    }
                                }
                                //BOM ConfigGroupId end

                                //BOM Configuration,style,colour,size,warehouse start
                                query =  new Query();
                                qbds = query.addDataSource( tableNum(InventDim));

                                qbr = qbds.addRange(fieldNum(InventDim,InventSiteId));
                                if(D!=''){qbr.value(D);}else{qbr.value(sysquery::valueEmptyString());}

                                qbr1 = qbds.addRange(fieldNum(InventDim,configId));
                                if(T!=''){qbr1.value(T);}else{qbr1.value(sysquery::valueEmptyString());}

                                qbr2 = qbds.addRange(fieldNum(InventDim,InventColorId));
                                if(V!=''){qbr2.value(V);}else{qbr2.value(sysquery::valueEmptyString());}

                                qbr3 = qbds.addRange(fieldNum(InventDim,InventLocationId));
                                if(X!=''){qbr3.value(X);}else{qbr3.value(sysquery::valueEmptyString());}

                                qbr4 = qbds.addRange(fieldNum(InventDim,inventSerialId));
                                qbr4.value(sysquery::valueEmptyString());

                                qbr5 = qbds.addRange(fieldNum(InventDim,InventSizeId));
                                if(U!=''){qbr5.value(U);}else{qbr5.value(sysquery::valueEmptyString());}

                                qbr6 = qbds.addRange(fieldNum(InventDim,InventStyleId));
                                if(W!=''){qbr6.value(W);}else{qbr6.value(sysquery::valueEmptyString());}

                                queryRun = new QueryRun(query);
                                while(queryRun.next())
                                {
                                    //info(strFmt("sublie - %1",query.dataSourceNo(1).toString()));
                                    ObjInventDim = queryRun.get(tableNum(inventDim));
                                    if(ObjInventDim.inventDimId)
                                    {
                                        ObjBOM.InventDimId = ObjInventDim.inventDimId;
                                    }
                                }
                                //BOM Configuration,style,colour,size,warehouse end

                                if(ObjBOM.validateWrite())
                                {
                                    ObjBOM.insert();
                                }
                                else
                                {
                                    FlagBOMOtherLine = true;
                                    //info(strFmt(" from bom Other line %1",FlagBOMOtherLine));
                                }
                                //BOM insert end
                                //BOM sub line insert end
                            }

                            type1 = cells.item(row1+1, 2).value().variantType();
                            type2 = cells.item(row1+1, 20).value().variantType();
                            //importing Item by rows end
                        }while (type1 == COMVariantType::VT_EMPTY && type2 != COMVariantType::VT_EMPTY);
                        //BOM Line Do While end
                        row = row1;
                        //Code for import end

                    }
                    catch(Exception::Error)
                    {
                        CodeAccessPermission::revertAssert();
                        application.quit();
                        ttsabort;
                        return;
                    }
                    //Item Import End

                    if(FlagBOMTable == true || FlagBOMVersion == true || FlagBOM1Line == true || FlagBOMOtherLine == true)
                    {
                        setPrefix("BOM not imported successfully.");
                        info(strFmt("%1",ObjBOMTable.BOMId));

                        delete_from ObjBOMVersion1 where ObjBOMVersion1.BOMId == ObjBOMVersion.BOMId;
                        //while select * from ObjBOM1 where ObjBOM1.BOMId == ObjBOM.BOMId
                        //{
                            //delete_from ObjBOM2 where ObjBOM2.BOMId == ObjBOM1.BOMId;
                        //}
                        delete_from ObjBOMTable1 where ObjBOMTable1.BOMId == ObjBOMTable.BOMId;

                        FlagBOMTable = false;
                        FlagBOMVersion = false;
                        FlagBOM1Line = false;
                        FlagBOMOtherLine = false;
                    }
                    else
                    {
                        //dev_ImportedCV Insert Start
                        Objdev_ImportedCV.AccountNum = ObjBOMTable.BOMId;
                        Objdev_ImportedCV.Name = ObjBOMTable.Name;
                        Objdev_ImportedCV.Type = "BOM";
                        Objdev_ImportedCV.insert();
                        //dev_ImportedCV Insert End

                        countinsert++;
                        setPrefix("BOM imported successfully.");
                        info(strFmt("%1",ObjBOMTable.BOMId));
                    }
                }
                type = cells.item(row+1, 1).value().variantType();
                //importing Item by rows end
               }while (type != COMVariantType::VT_EMPTY);
            //do while end
            //ttscommit;
        }
        //Importing Item End
    }
    catch(Exception::Error)
    {
        CodeAccessPermission::revertAssert();
        application.quit();
        ttsabort;
    }
    ttsCommit;
    info(strFmt("Total BOM %1 Inserted Successfully",countinsert));
    //main try catch end