Excel upload

 

static void excelUpload(Args _args)

{

    //dialog

    dialog d = new dialog("Excel upload Tool");

    dialogfield dExcelFilepath,dTablename,dFieldName1,dFieldName2,dFieldName3,dFieldName4,dFieldName5,dExcelStart,dExcelEnd;

    dialogfield type1,type2,type3,type4,type5;

    DialogGroup fGroup1,fGroup2,fGroup3,fGroup4,fGroup5;

    //general variables

    filepath excelFilepath;

    tablename tableName;

    FieldName fieldName1,fieldName2,fieldName3,fieldName4,fieldName5;

    str 25   fieldtype1,fieldtype2,fieldtype3,fieldtype4,fieldtype5;

    str 100    NexcelField1,NexcelField2,NexcelField3,NexcelField4,NexcelField5;

    int     excelStart,excelEnd;

    boolean ok = true;

    #AviFiles

    SysOperationProgress    progress = new SysOperationProgress();

    //dictTable variables

    dicttable dictTable;

    dictField  dictField1,dictField2,dictField3,dictField4,dictField5;

    common common;

    //excel variables

    SysExcelApplication ldA;

    SysExcelWorkbooks   ldWbs;

    SysExcelWorkbook    ldWb;

    SysExcelWorksheets  ldWss;

    SysExcelWorksheet   ldWs;

    SysExcelCells       ldCs;

    SysExcelCell        ldCA;

    SysExcelRange       ldR;

    int                 j;

    container value( str _Value,types _type,fieldName _fieldName = '')

    {

        container conLocal [1];

        dictEnum    dictEnum;

        int         i;

        ;

        switch (_type)

        {

            case types::Integer :

            conlocal[1] = [str2int(_value)];

            break;

            case types::Real :

            conlocal[1] = [str2num(_value)];

            break;

            case types::Date :

            conlocal[1] = [str2date(_value,1)];

            break;

            case types::Int64:

            conlocal[1] = [str2int64(_value)];

            break;

            case types::UtcDateTime:

            conlocal[1] = [str2datetime(_value,1)];

            break;

            case types::Enum:

            {

                if(_fieldName != '')

                {

                    dictEnum = new DictEnum(EnumName2Id(_fieldName));

                    for(i=0;i<= dictEnum.values();i++)

                    {

                        if(dictEnum.index2Label(i) == _value)

                        {

                            conlocal[1] = [dictEnum.name2Value(_value)];

                            Break;

                        }

                    }

                }

            }

            break;

            default:

            conlocal[1] = [strfmt(_value)];

            break;

        }

        return conlocal[1];

    }

    boolean validate()

    {

        Dictionary            dictionary = new Dictionary();

        tableName         tableNameLocal;

        ;

        if(!excelFilepath)

        ok = ok && checkFailed("Please give the Excel path to import data");

        if(!tableName && ok)

        ok = ok && checkFailed("Table Name is mandatory");

        if(tablename && ok)

        {

            tableNameLocal = Dictionary.tableName(tableName2Id(tableName));

            if(!tableNameLocal)

            ok = ok && checkFailed(strfmt("Table %1 does not exist",tablename));

        }

        if(!fieldName1 && ok)

        {

            ok = ok && checkfailed("Primary Key is mandatory Proceed");

        }

        if((!FieldName2 && !fieldname3 && !FieldName4 && !Fieldname5) && ok)

        {

            ok = ok && checkFailed("Atleast one field is mandatory Proceed");

        }

        if(ok && !ExcelStart || !Excelend)

        {

            ok = ok && checkFailed("Excel start and end rows are mandatory to proceed");

        }

        if(ok && (ExcelStart > Excelend))

        {

            ok = ok && checkfailed("Excel Start row number cannot be less than Excelend row number");

        }

        if((!FieldName2 || !fieldname3 || !FieldName4 || !Fieldname5) && ok )

        {

            ok = box::yesNo("All fields are not given, you still want to proceed ?",dialogbutton::Yes,"Check validation") == dialogButton::Yes;

        }

        return ok;

    }

    ;

    dExcelFilepath = d.addField(extendedtypestr(FilePath),"Excel file path","Please select the excel");

    dTableName     = d.addField(extendedTypeStr(tablename),"Table name","Please enter tablename");

    fgroup1 = d.addGroup("Primary Key");

    dFieldName1    = d.addField(extendedtypestr(FieldName),"Primary Key","Please enter Field-1");

    type1 = d.addField(extendedtypestr(fieldType),"Primary Key","Select the field type");

    fgroup2 = d.addGroup("Field list 1");

    dFieldName2    = d.addField(extendedtypestr(FieldName),"Field-1","Please enter Field-2");

    type2 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    fgroup3 = d.addGroup("Field list 2");

    dFieldName3    = d.addField(extendedtypestr(FieldName),"Field-2","Please enter Field-3");

    type3 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    fgroup4 = d.addGroup("Field list 3");

    dFieldName4    = d.addField(extendedtypestr(FieldName),"Field-3","Please enter Field-4");

    type4 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    fgroup5 = d.addGroup("Field list 4");

    dFieldName5    = d.addField(extendedtypestr(FieldName),"Field-4","Please enter Field-5");

    type5 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    dExcelStart    = d.addField(extendedtypestr(integer),"Excel Start","Enter excel Starting row");

    dExcelEnd    = d.addField(extendedtypestr(integer),"Excel End","Enter excel ending row");

    if(d.run()== noyes::Yes)

    {

        //reading dialog

        excelFilepath = dExcelFilepath.value();

        tablename = dtablename.value();

        dictTable = new dicttable(tableName2Id(tableName));

        if(!dictTable)

           throw error(strfmt("Table %1 is not found ",tableName));

        FieldName1 = dFieldName1.value();

        if(FieldName1)

            dictField1 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName1));

        if(FieldName1 && !dictField1)

            throw error(strfmt("Field %1 Not found in table %2",FieldName1,tableid2name(dictTable.id())));

        FieldName2 = dFieldName2.value();

        if(FieldName2)

            dictField2 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName2));

        if(FieldName2 &&!dictField2)

            throw error(strfmt("Field %1 Not found in table %2",FieldName2,tableid2name(dictTable.id())));

        FieldName3 = dFieldName3.value();

        if(FieldName3)

            dictField3 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName3));

        if(FieldName3 && !dictField3)

            throw error(strfmt("Field %1 Not found in table %2",FieldName3,tableid2name(dictTable.id())));

        FieldName4 = dFieldName4.value();

        if(FieldName4)

            dictField4 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName4));

        if(FieldName4 && !dictField4)

            throw error(strfmt("Field %1 Not found in table %2",FieldName4,tableid2name(dictTable.id())));

        FieldName5 = dFieldName5.value();

        if(FieldName5)

            dictField5 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName5));

        if(FieldName5 && !dictField5)

            throw error(strfmt("Field %1 Not found in table %2",FieldName5,tableid2name(dictTable.id())));

        ExcelStart = dExcelStart.value();

        ExcelEnd =    dExcelEnd.value();

        if(validate())

        {

            if(winapi::pathExists(excelFilepath))//&& winapi::fileExists(excelFilepath +'test2.Xlsx'))

            {

                // reading excel

                ldA = SysExcelApplication::construct();

                ldA.visible(false);

                ldWbs = ldA.workbooks();

                ldWbs.open(excelFilepath+'\\'+'excel.Xlsx',0,true);

                ldWb = ldWbs.item(1);

                ldWss = ldWb.worksheets();

                ldws  = ldwss.itemFromName('Sheet1');

                ldCs = ldWs.cells();

                ldR = ldCs.range('A1');

                for (j=ExcelStart;j<=ExcelEnd;j++)

                {

                    progress.setCaption("Data update is in progress...");

                    progress.setAnimation(#AviUpdate);

                    progress.setTotal(ExcelEnd);

                    progress.setText(strfmt("Record %1 is been updated", j-1));

                    progress.setCount(j-1, 1);

                    ldCA         = ldCs.item(j,1);

                    NexcelField1 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,2);

                    NexcelField2 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,3);

                    NexcelField3 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,4);

                    NexcelField4 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,5);

                    NexcelField5 =  ldCA.value().bStr();

                    common = dictTable.makeRecord();

                    ttsbegin;

                    select forupdate common where common.(fieldname2id(dicttable.id(),fieldname1)) == conpeek(value(NexcelField1,type1.value(),fieldname1),1);//str2int64(NexcelField1);//;

                    if(Common)

                    {

                        if(NexcelField2 && fieldName2)

                        {

                                common.(fieldname2id(dicttable.id(),fieldname2)) = conpeek(value(NexcelField2,type2.value(),fieldname2),1);

                         }

                        if(NexcelField3 && fieldName3)

                        {

                                common.(fieldname2id(dicttable.id(),fieldname3)) = conpeek(value(NexcelField3,type3.value(),fieldname3),1);

                        }

                        if(NexcelField4 && fieldName4)

                        {

                            common.(fieldname2id(dicttable.id(),fieldname4)) = conpeek(value(NexcelField4,type4.value(),fieldname4),1);

                        }

                        if(NexcelField5 && fieldName5)

                        {

                            common.(fieldname2id(dicttable.id(),fieldname5)) = conpeek(value(NexcelField5,type5.value(),fieldname5),1);

                        }

                        common.update();

                    }

                    ttscommit;

                    common = null;

                }

                ldWbs.close();

                info(strFmt("Task has completed,Succcessfully updated %1 records",j));

            }

            else

            {

             throw error(strfmt("Please check excel path - %1",excelFilepath));

            }

        }

    }

    else

        info("Task has cancelled");

}

 Query to get list of all table level indexes ....

select i.[name] as index_name,

    substring(column_names, 1, len(column_names)-1) as [columns],

    case when i.[type] = 1 then 'Clustered index'

        when i.[type] = 2 then 'Nonclustered unique index'

        when i.[type] = 3 then 'XML index'

        when i.[type] = 4 then 'Spatial index'

        when i.[type] = 5 then 'Clustered columnstore index'

        when i.[type] = 6 then 'Nonclustered columnstore index'

        when i.[type] = 7 then 'Nonclustered hash index'

        end as index_type,

    case when i.is_unique = 1 then 'Unique'

        else 'Not unique' end as [unique],

    schema_name(t.schema_id) + '.' + t.[name] as table_view, 

    case when t.[type] = 'U' then 'Table'

        when t.[type] = 'V' then 'View'

        end as [object_type]

from sys.objects t

    inner join sys.indexes i

        on t.object_id = i.object_id

    cross apply (select col.[name] + ', '

                    from sys.index_columns ic

                        inner join sys.columns col

                            on ic.object_id = col.object_id

                            and ic.column_id = col.column_id

                    where ic.object_id = t.object_id

                        and ic.index_id = i.index_id

                            order by key_ordinal

                            for xml path ('') ) D (column_names)

where t.is_ms_shipped <> 1

and index_id > 0

order by i.[name]

A simple job to get the user related roles information

 static void HSIN_UserAndRoles(Args _args)

{

    SecurityUserRole         securityUserRole;

    SecurityRole             securityRole;

    HSecurityRoleforUser  securityRoleforUserTmp; // My own table to store the info

    boolean                  Created;

    delete_from securityRoleforUserTmp;

    while select securityRole

        join securityUserRole

            where securityUserRole.SecurityRole == securityRole.RecId &&

                  securityUserRole.User like '*'

    {

        securityRoleforUserTmp.clear();

        securityRoleforUserTmp.User     = securityUserRole.User +" "+"("+UserInfoHelp::userName(securityUserRole.User)+")";

        securityRoleforUserTmp.Role     = securityRole.AotName;

        securityRoleforUserTmp.RoleName = securityRole.Name;

        securityRoleforUserTmp.insert();

        Created = true;

    }

    if(Created)

    {

        Info(' User roles info generated  sucessfully');

    }

}