[#Menu : Gateway of Tally] Add : Key Item : After : @@LocDisplay: Import Stock Items : I : Execute : SMPL Import Config [Function: SMPL Import Stock Items] Variable : Row : Number : 1 Variable : Temp Var : String Variable : Last Status : String Variable : Counter : Number : 1 Variable : Item Columns List Variable : Item Details, Item Import Status 00 : QUERY BOX : "Import Stock Items" : Yes : No 10 : IF : $$LastResult 20 : IF : ##SMPLSICSource = "Excel" 30 : OPEN FILE: @@SMPLTotFilePath : Excel : READ 40 : ELSE : 50 : OPEN FILE: @@SMPLTotFilePath : Text : READ 60 : ENDIF 70 : SET : ItemColumns.ItemName : @@SMPLItemNameColIdx 80 : SET : ItemColumns.ItemGrp : @@SMPLItemGrpColIdx 90 : SET : ItemColumns.ItemUOM : @@SMPLItemUOMColIdx 100 : IF : ##SMPLSICSource = "Excel" 110 : SET : Row : if ##SMPLSICIncHeader then 2 else 1 ;; Reading from Excel File and updating to List 120 : WHILE : NOT $$IsEmpty$$FileReadCell:##Row:##ItemColumns.ItemName) 130 : List Add Ex : Item Details 140 : SET : ItemDetails[$$LoopIndex].ItemName : $$FileReadCell:##Row:##ItemColumns.ItemName 150 : SET : ItemDetails[$$LoopIndex].ItemGrp : $$FileReadCell:##Row:##ItemColumns.ItemGrp 160 : SET : ItemDetails[$$LoopIndex].ItemUOM : $$FileReadCell:##Row:##ItemColumns.ItemUOM 170 : INCREMENT : Row 180 : END WHILE 190 : ELSE: ;; Reading from Text File and updating to List 200 : SET : Counter : 1 210 : WHILE : NOT $$FileIsEOF 220 : SET : Temp Var : $$FileRead 230 : IF : NOT $$IsEmpty:##TempVar AND (NOT ##SMPLSICIncHeader OR (##SMPLSICIncHeader AND $$LoopIndex > 1)) 240 : LIST ADD EX : Item Details 250 : SET : ItemDetails[##Counter].ItemName : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemNa me 260 : SET : ItemDetails[##Counter].ItemGrp : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemGr p 270 : SET : ItemDetails[##Counter].ItemUOM : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemUO M 280 : INCREMENT : Counter 290 : ENDIF 300 : END WHILE 310 : ENDIF 320 : CLOSE FILE 330 : IF : ##SMPLSICOpenLogFile 340 : OPEN FILE : @@SMPLErrorFilePath : Text : WRITE : ASCII 350 : TRUNCATE FILE 360 : ENDIF 370 : SET : Counter : 1 ;; Creating Stock Items from List of Compound Variable 380 : WALK COLLECTION : SMPL Imp StockItem Summ 390 : SET : Last Status : "" 391 : IF : $$IsRemoteCompany 392 : CALL : SMPL Fetch Objects 393 : ENDIF 400 : IF : $$IsEmpty:$Name:StockItem:$SICStockItem 410 : New Object : Stock Item 420 : SET VALUE : Name : $SICStockItem 430 : IF : NOT $$IsEmpty:$Name:StockGroup:$SICStockGroup 440 : SET VALUE : Parent : $SICStockGroup 450 : ELSE: 460 : SET : LastStatus: "Group " + $SICStockGroup + " does not exist" 470 : ENDIF 480 : IF : $$IsEmpty:$AgentName:Company:$SICStockUOM 490 : SET VALUE : AgentName : $SICStockUOM 500 : ELSE: 510 : SET : LastStatus : "Unit " + $SICStockUOM + " does not exist" 520 : ENDIF 530 : IF : $$IsEmpty:##LastStatus 540 : SAVE TARGET 550 : SET : Last Status : "Imported Successfully" 560 : ENDIF 570 : ENDIF 575 : IF : $$IsEmpty:##LastStatus 576 : SET : Last Status : "Item already exists" 577 : ENDIF ;; Writing Import Status to the LOG File if LOG File is to be displayed at the end 580 : IF : ##SMPLSICOpenLogFile 590 : WRITE FILE LINE : $SICStockItem + ##SMPLSICTextSep + ##LastStatus 600 : ENDIF ;; Updating List of Compound Variables is Status is to be displayed in a Report 610 : IF : ##SMPLSICDisplayReport 620 : LIST ADD EX : ItemImportStatus 630 : SET : ItemImportStatus[##Counter].ItemName : $SICStockItem 640 : SET : ItemImportStatus[##Counter].Status : ##LastStatus 650 : INCREMENT : Counter 660 : ENDIF 670 : END WALK ;; 680 : CLOSE TARGET FILE ;; Display Error Report from the List of Compound Variables 'ItemImportStatus' 690 : IF : ##SMPLSICDisplayReport 700 : DISPLAY : SMPL SIC Error Report 710 : ENDIF ;; Open the LOG File 720 : IF : ##SMPLSICOpenLogFile 730 : EXEC COMMAND : @@SMPLErrorFilePath 740 : ENDIF 750 : ENDIF [Collection: SMPL Imp StockItem] ;; Collection using List of Compound Variable Data Source : Variable : Item Details [Collection: SMPL Imp StockItem Summ] Source Collection : SMPLImpStockItem By : SICStockItem : $ItemName By : SICStockGroup : $ItemGrp By : SICStockUOM : $ItemUOM Filter : SMPL NonEmpty Item Client Only : Yes [System: Formula] SMPL NonEmpty Item : NOT $$IsEmpty:$SICStockItem SMPL Folder Path : if $$SMPLEndsWithSlash:##SMPLSICDirPath then ##SMPLSICDirPath else ##SMPLSICDirPath + "" SMPL File Name : ##SMPLSICFilePath SMPL File Ext : if $$SMPLSymbolExistsinFileName:##SMPLSICFileExt then ##SMPLSICFileExt else "." + ##SMPLSICFileExt SMPL TotFilePath : @@SMPLFolderPath + @@SMPLFileName + @@SMPLFileExt SMPL ItemNameColIdx : if $$Number:##SMPLSICItemName = 0 then $$FileGetColumnIdx:##SMPLSICItemName else ##SMPLSICItemName SMPL ItemGrpColIdx : if $$Number:##SMPLSICItemGroup = 0 then $$FileGetColumnIdx:##SMPLSICItemGroup else ##SMPLSICItemGroup SMPL ItemUOMColIdx : if $$Number:##SMPLSICItemUOM = 0 then $$FileGetColumnIdx:##SMPLSICItemUOM else ##SMPLSICItemUOM SMPL Error File Path: @@SMPLFolderPath + @@SMPLFileName + ".LOG" ;; Functions [Function: SMPL Fetch Objects] Fetch Object: Stock Item : $SICStockItem : $Name Fetch Object: Stock Group : $SICStockGroup : $Name Fetch Object: Company : $SICStockUOM : $AgentName [Function: SMPL SIC Extract Det] Parameter : pValue : String Parameter : pIndex : Number Variable : Counter : Number : 1 00 : FOR TOKEN : TokenVar : ##pValue : ##SMPLSICTextSep 10 : IF : ##Counter = ##pIndex 20 : RETURN : ##TokenVar 30 : ENDIF 40 : INCREMENT : Counter 50 : END FOR ;; Compound Variable Declaration [Variable: Item Columns] Variable : Item Name, Item Grp, Item UOM : Number [Variable: Item Details] Variable : Item Name, Item Grp, Item UOM : String [Variable: Item Import Status] Variable : Item Name, Status : String ;; Import Configuration [Report: SMPL Import Config] ;; Report Definition invoking Import of Stock Items Form : SMPL Import Config Title : $$LocaleString:"Import Configuration" [Form: SMPL Import Config] Parts : SMPL Import Config Driver Info, SMPL Import Config Column Info, SMPL Import Config Other Info Full Width : No Space Right : 2 Space Left : 2 No confirm : Yes Option : Small Size Form Background : @@SV_CMPCONFIG On: Form Accept : Yes : Form Accept On: Form Accept : Yes : Call : SMPL Import Stock Items [Part: SMPL Import Config Driver Info] Lines : Form SubTitle, SMPL SIC Source, SMPL SIC DirInfo, SMPL SIC FileInfo, SMPL SIC FileExtension, SMPL SIC SheetInfo Local: Field: Form SubTitle : Info : $$LocaleString:"File Information" Local: Line : Form SubTitle : Space Top : 1 Local: Field: Name Field : Width : 25% Page [Line: SMPL SIC Source] Fields : Medium Prompt, Name Field Local: Field: Medium Prompt : Set As : "Import Source :" Local: Field: Name Field : Modifies : SMPL SIC Source : Yes Local: Field: Name Field : Variable : SMPL SIC Source Local: Field: Name Field : Table : SMPL SIC Source Local: Field: Name Field : Show Table: Always [Table: SMPL SIC Source] List Name : "Text", "Excel" Title : "Source" [Line: SMPL SIC DirInfo] Fields : Medium Prompt, Name Field Local: Field: Medium Prompt : Set As : "Location of File:" Local: Field: Name Field : Modifies : SMPL SIC DirPath : Yes Local: Field: Name Field : Variable : SMPL SIC DirPath Local: Field: Name Field : Max : 120 [Line: SMPL SIC FileInfo] Use : SMPL SIC DirInfo Local: Field: Medium Prompt : Set As : "File Name:" Local: Field: Medium Prompt : Indent : 1 Local: Field: Name Field : Modifies : SMPL SIC FilePath : Yes Local: Field: Name Field : Variable : SMPL SIC FilePath Local: Field: Name Field : Control : SMPL NoExt: $$SMPLSymbolExistsinFileName:$$Value [Line: SMPL SIC FileExtension] Use : SMPL SIC DirInfo Local: Field: Medium Prompt : Set As : "File Extension:" Local: Field: Medium Prompt : Indent : 1 Local: Field: Name Field : Modifies : SMPL SIC FileExt : Yes Local: Field: Name Field : Variable : SMPL SIC FileExt Local: Field: Name Field : Set As : if (##SMPLSICSource = "Excel" AND NOT ##SMPLSICFileExt CONTAINS "xls") then "xls" else if (##SMPLSICSource = "Text" AND ##SMPLSICFileExt CONTAINS "xls") then "txt" else ##SMPLSICFileExt Local: Field: Name Field : Set Always: Yes Local: Field: Name Field : Case : Normal [Line: SMPL SIC SheetInfo] Use : SMPL SIC DirInfo Local: Field: Default : Inactive : NOT ##SMPLSICSource = "Excel" Local: Field: Medium Prompt : Set As : "Sheet Name (Excel):" Local: Field: Name Field : Set As : if $$IsEmpty:$$Value then "Stock Items" else ##SMPLSICTableName Local: Field: Name Field : Modifies : SMPL SIC TableName : Yes Local: Field: Name Field : Variable : SMPL SIC TableName [Part: SMPL Import Config Column Info] Lines : Form SubTitle, SMPL SIC Inc Header, SMPL SIC Item Name, SMPL SIC Item Group, SMPL SIC Item UOM, SMPL SIC Text Sep Local: Field: Form SubTitle : Info : $$LocaleString:"Column for the following (A, B, C or 1, 2, 3)" Local: Line : Form SubTitle : Space Top : 1 Local: Field: Name Field : Set Always: Yes [Line: SMPL SIC Inc Header] Use : SMPL SIC DirInfo Local: Field: Medium Prompt : Set As : "Includes Header Info :" Local: Field: Name Field : Modifies : SMPL SIC IncHeader : Yes Local: Field: Name Field : Variable : SMPL SIC IncHeader Local: Field: Name Field : Width : @@LogicalWidth Local: Field: Name Field : Table : YesNoTable [Line: SMPL SIC Item Name] Use : SMPL SIC DirInfo Local: Field: Medium Prompt : Set As : "Stock Item Name :" Local: Field: Name Field : Modifies : SMPL SIC ItemName : Yes Local: Field: Name Field : Variable : SMPL SIC ItemName Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" AND $$Number:##SMPLSICItemName = 0 then "1" else ##SMPLSICItemName Option : SMPL SIC Item Name Max [!Line: SMPL SIC Item Name Max] Local: Field: Name Field : Maximum : 1 Local: Field: Name Field : Width : 2 [Line: SMPL SIC Item Group] Use : SMPL SIC Item Name Local: Field: Medium Prompt : Set As : "Under Group :" Local: Field: Name Field : Modifies : SMPL SIC ItemGroup : Yes Local: Field: Name Field : Variable : SMPL SIC ItemGroup Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" AND $$Number:##SMPLSICItemGroup = 0 then "2" else ##SMPLSICItemGroup Local: Field: Name Field : Control : Duplicate Entry : $$Value = ##SMPLSICItemName Option : SMPL SIC Item Name Max [Line: SMPL SIC Item UOM] Use : SMPL SIC Item Name Local: Field: Medium Prompt : Set As : "Units of Measure :" Local: Field: Name Field : Modifies : SMPL SIC ItemUOM : Yes Local: Field: Name Field : Variable : SMPL SIC ItemUOM Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" AND $$Number:##SMPLSICItemUOM = 0 then "3" else ##SMPLSICItemUOM Local: Field: Name Field : Control : Duplicate Entry : $$Value = ##SMPLSICItemName OR $$Value = ##SMPLSICItemGroup Option : SMPL SIC Item Name Max [Line: SMPL SIC Text Sep] Use : SMPL SIC Item Name Local: Field: Default : Inactive : NOT ##SMPLSICSource = "Text" Local: Field: Medium Prompt : Set As : "Text Separator Character :" Local: Field: Name Field : Modifies : SMPL SIC TextSep : Yes Local: Field: Name Field : Variable : SMPL SIC TextSep Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" then ##SMPLSICTextSep else "" [Part: SMPL Import Config Other Info] Lines : Form SubTitle, SMPL SIC Display Report, SMPL SIC Open Log File Local: Field: Form SubTitle : Info : $$LocaleString:"Other Information" Local: Line : Form SubTitle : Space Top : 1 [Line: SMPL SIC Display Report] Use : SMPL SIC Dir Info Local: Field: Medium Prompt : Set As : "Display Error Report, if any :" Local: Field: Name Field : Modifies : SMPL SIC DisplayReport : Yes Local: Field: Name Field : Variable : SMPL SIC DisplayReport Local: Field: Name Field : Table : YesNoTable Local: Field: Name Field : Width : @@LogicalWidth [Line: SMPL SIC Open Log File] Use : SMPL SIC Display Report Local: Field: Medium Prompt : Set As : "Open Log File Error, if any :" Local: Field: Name Field : Modifies : SMPL SIC Open Log File : Yes Local: Field: Name Field : Variable : SMPL SIC Open Log File Local: Field: Name Field : Table : YesNoTable Local: Field: Name Field : Width : @@LogicalWidth ;; Function Definition [Function: SMPL SymbolExistsinFileName] Parameter : pValue : String Parameter : pSymbol : String : "." Variable : TempVar : String 00 : FOR RANGE : IteratorVar : Number : ($$StringLength:##pValue) : 0 : 1 10 : SET : TempVar : $$StringPart:##pValue:##IteratorVar:1 20 : IF : $$ExactMatch:##TempVar:##pSymbol 30 : RETURN : True 40 : ENDIF 50 : END FOR [Function: SMPL EndsWithSlash] Parameter : pValue : String Variable : TempNo : Number Variable : TempVar : String 00 : SET : TempNo : $$StringLength:##pValue - 1 10 : SET : TempVar : $$StringPart:##pValue:##TempNo:1 20 : IF : ##TempVar = "" 30 : RETURN : True 40 : ELSE : 50 : RETURN : False 60 : ENDIF ;; Variable and Formulae [System: Formula] SMPL NoExt : "Please do not enter \n File Extension" [System: Variable] SMPL SICSource : "Excel" SMPL SICDirPath : "C:\Tally.ERP9\FileIOData" SMPL SICFilePath : "ListofStockItems" SMPL SICFileExt : ".xls" SMPL SICTableName : "Stock Items" SMPL SICIncHeader : Yes SMPL SICItemName : "A" SMPL SICItemGroup : "B" SMPL SICItemUOM : "C" SMPL SICTextSep : "," SMPL SICDisplayReport : Yes SMPL SICOpen Log File : No [Variable: SMPL SIC Source] Type : String Persistent : Yes [Variable: SMPL SIC DirPath] Type : String Persistent : Yes [Variable: SMPL SIC FilePath] Type : String Persistent : Yes [Variable: SMPL SIC FileExt] Type : String Persistent : Yes [Variable: SMPL SIC TableName] Type : String Persistent : Yes [Variable: SMPL SIC IncHeader] Type : String Persistent : Yes [Variable: SMPL SIC ItemName] Type : String Persistent : Yes [Variable: SMPL SIC ItemGroup] Type : String Persistent : Yes [Variable: SMPL SIC ItemUOM] Type : String Persistent : Yes [Variable: SMPL SIC TextSep] Type : String Persistent : Yes [Variable: SMPL SIC DisplayReport] Type : Logical Persistent : Yes [Variable: SMPL SIC OpenLogFile] Type : Logical Persistent : Yes ;; Import Error Report [Report: SMPL SIC Error Report] Form : SMPL SIC Error Report Title : "Import Status" [Form: SMPL SIC Error Report] Parts : Form Subtitle, SMPL SIC Error Report Local : Field : Form SubTitle : Info : "Status of Stock Item import" [Part: SMPL SIC Error Report] Lines : SMPL SIC Error Title, SMPL SIC Error Details Repeat : SMPL SIC Error Details : SMPL SIC LOGVarColl Summ Scroll : Vertical Common Border : Yes [Line: SMPL SIC Error Title] Use : SMPL SIC Error Details Local: Field: Default : Style : Normal Bold Local: Field: Default : Align : Centre Local: Field: SMPL SIC Error Item : Set As : "Particulars" Local: Field: SMPL SIC Error Status : Set As : "Status" Border : Thin Top Bottom [Line: SMPL SIC Error Details] Fields : SMPL SIC Error Item Right Fields: SMPL SIC Error Status Option : SMPL SIC Success : $SICStatus CONTAINS "Imported Successfully" OR $SICStatus CONTAINS "Item already exists" [!Line: SMPL SIC Success] Local : Field : Default : Style : Normal Italic [Field: SMPL SIC Error Item] Use : Name Field Set As : $SICStockItem FullWidth : Yes [Field: SMPL SIC Error Status] Use : Name Field Set As : $SICStatus Border : Thin Left Width : 50% Page [Collection: SMPL SIC LOGVarColl] Data Source : Variable : Item Import Status [Collection: SMPL SIC LOGVarColl Summ] Source Collection : SMPL SIC LOGVarColl By : SICStockItem : $ItemName By : SICStatus : $Status Keep Source : (). Filter : SMPL NonEmpty Item Client Only : Yes ;; End-of-File
