[#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.ItemName
    260    :                SET            : ItemDetails[##Counter].ItemGrp    : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemGrp
    270    :                SET            : ItemDetails[##Counter].ItemUOM    : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemUOM
    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    : NOT $$IsEmpty:$Symbol:Unit:$SICStockUOM
    490    :                SET VALUE    : Base Units    : $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: Unit            : $SICStockUOM    : $Symbol
 
[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