Smart Markers and Protecting and unprotecting Excels

Smartmarkers

·        Add header columns  in first row

·        In second row use syntax like &=[ClientMasterDataList].Name , where ClientMasterDataList is the data source and name is the property

·        Rest writing excel code is same as done in acosta project

Sample excel attached which has smart markers as well as protection with password rbmsimulationtoolacosta

Unprotect a workbook

·        Go to review tab à unprotect sheet

·        Enter password

Protect a workbook

·        Approach used à protect all à then unprotect selected area

·        Select all by Ctrl + A à open properties by (Ctrl+1) à Protection tabà click locked (make sure it is clicked and not only selected as selected means partial sheet is getting protected)

·        Selected the columns to be left editable (shift  + right arrow to move across columns) followed by Ctrl + shift + down key to select the entire area

·        Open properties by pressing Ctrl + 1, unselect the locked check box

·        Go to review tab à protect sheet à enter password

review tab 

Important Linq Queries

Update with join

var InputCombipairs = from Input in InputCombinations.ObjectiveSubbannerCombinationList

                        join DBInput in DBInsertInputCombinations

                            on new { SubbannerId= Input.SubbannerId, ObjectiveId= Input.ObjectiveId } equals new { SubbannerId= DBInput.SubBannerId, ObjectiveId= DBInput.ObjectiveId }

                        select new { DBInput, Input };

            foreach (var pair in InputCombipairs)

            {

                pair.Input.ObjectiveName = pair.DBInput.Objective.Name;

                pair.Input.SubbannerName = pair.DBInput.SubBanner.Name;

            }

Sample join and group 

var UserIdsWithPersonalShifts = ActionItems.GroupBy(M=>M.UserId).Select(grp=> new

                    {

                        UserId= grp.Key,

                        MinStartTime= grp.Min(L=>L.CreationDate.Value),

                        MaxEndTime= grp.Max(L=>L.RecurrenceEndDate.Value)

                    }); 

var EligibleUserPCNotiNotNull =

                        from usr in Users.Where(x =>

x.Client.ClientDashboardConfigurations.First().RequireAutomatedPulseNoti.Value &&

                            x.PulseCheckNotiDay != null &&

                            x.PulseCheckNotiHour == 15 &&

                            x.PulseCheckNotiMinutes == 0 &&

                            x.PulseCheckNotiDay == 4 &&

x.DeviceTokenId !=null

                            ) 

join UWPS in UserIdsWithPersonalShifts on usr.Id equals UWPS.UserId

select new

                            {

                                UserId = usr.Id,

                                ClientId = usr.ClientId,

                                Name = string.Format("{0} {1}", usr.FirstName, usr.LastName),

                                DeviceTokenId = usr.DeviceTokenId,

                                LastProvideFBVisitDate = usr.LastProvideFBVisitDate,

                                PCNotiDay = usr.PulseCheckNotiDay,

                                PCNotiHour = usr.PulseCheckNotiHour,

                                PCNotiMinutes = usr.PulseCheckNotiMinutes

                            };

EligibleUserPCNotiNotNull.Dump();

Outer join (example1)

DateTime StartDate=Convert.ToDateTime("07/02/2016 18:30:00");
  var pulseCheckItems = (
                    from u in Users
                    from ai in ActionItems
                        .Where(
                            mapping =>
                                (mapping.DevAreaId.Value==85) && (mapping.UserId == u.Id) &&
                                (mapping.CreationDate.Value < StartDate ||
                                 Math.Abs((mapping.CreationDate.Value - StartDate).Days) < 7) &&
                                ((mapping.DeletionDate == null && mapping.IsDeleted.Value == false) ||
                                 mapping.DeletionDate.Value > StartDate))
                    from groups in PulseChecks
                        .Where(gruppe => gruppe.ActionItemId == ai.Id).DefaultIfEmpty()
                    from groupColor in ColorCodes
                        .Where(gruppeColor => gruppeColor.Id == groups.ColorCodeId).DefaultIfEmpty()
                    where ai.UserId == 404
                    select new
                    {
                        id = (groups != null) ? groups.Id : -1,
                        actionItemId = ai.Id != null ? ai.Id : -1,
                        actionItemTitle = ai.Title != null ? ai.Title : "",
                        colorCodeID = (groups != null && groups.ColorCodeId != null) ? groups.ColorCodeId : -1,
                        color = (groupColor != null) ? groupColor.Color : "",
                        weekStartDate = (groups != null) ? (groups.WeekStartDate) : null
                    });
pulseCheckItems =
                    pulseCheckItems.Where(
                        O => ((O.weekStartDate == null) && O.id == -1) || O.weekStartDate.Value <= StartDate);
pulseCheckItems.Dump();

Outer join (example2)

void Main()
{
var UserSpecificDevAreas = UserDevAreaMappings.Where(L => (L.UserId == 426) && (L.DevArea.IsDeleted == false)).
                    Select(K => new {DevAreaId= K.DevAreaId,Date= (DateTime?)K.DateAdded.Value}).ToList();
UserSpecificDevAreas.Dump();


var ClientDevAreas = ClientDevAreaMappings.Where(L => (L.ClientId == 47) && (L.DevArea.IsDeleted == false)).Select(O => new
                {
                    DevAreaId = O.DevAreaId,
                    DevAreaName = O.DevArea.Name,
                    IsSelected = false,
                    IconPath = "DevAreas-images" + "/" + O.DevArea.IconPath,
                    GroupId = O.DevArea.GroupId,
                    GroupName = O.DevArea.Group.Name,
                    GroupIconPath = "DevAreaGroup-images" + "/" + O.DevArea.Group.IconPath,
                    DateAdded= Convert.ToDateTime(null)
                }).ToList();
ClientDevAreas.Dump();

var ClientNUserDAMerged= (from CDA in ClientDevAreas                        
                    from USDA in UserSpecificDevAreas
                        .Where(USDA => USDA.DevAreaId == CDA.DevAreaId).DefaultIfEmpty()
select new
                    {
                         DevAreaId = CDA.DevAreaId,
                    DevAreaName = CDA.DevAreaName,
                    IsSelected = USDA!=null? true : false,
                    IconPath = CDA.IconPath,
                    GroupId = CDA.GroupId??-1,
                    GroupName = CDA.GroupName??"Others",
                    GroupIconPath = CDA.GroupIconPath,
                    DateAdded = USDA != null ? USDA.Date : null,
USDA
                    }).ToList();
ClientNUserDAMerged.Dump();



var GroupedDevAreas = ClientNUserDAMerged.GroupBy(L => new { L.GroupId, L.GroupName, L.GroupIconPath }).Select(M => new GroupedDevArea()
                {
                    GroupId = M.Key.GroupId,
                    GroupName = M.Key.GroupName,
                    IconPath = M.Key.GroupIconPath,
                    DevAreaList = M.Select(K => new DevAreaModel()
                    {
                        DevAreaId = K.DevAreaId,
                        DevAreaName = K.DevAreaName,
                        IsSelected = K.IsSelected,
                        IconPath = K.IconPath
                    }).ToList()
                });
}

  public class DevAreaModel
    {
        public long DevAreaId { get; set; }
        public string DevAreaName { get; set; }
        public bool IsSelected { get; set; }
        public string IconPath { get; set; }
        public DateTime? DateAdded { set; get; }

    }
    public class GroupedDevArea
    {
        public int? GroupId { get; set; }
        public string GroupName { get; set; }
        public string IconPath { get; set; }

        public List<DevAreaModel> DevAreaList { set; get; }

    }

// Define other methods and classes here





Enumerating AD users via LINQ utility

var domainName = Util.ReadLine("Domain Name");
var groupName = Util.ReadLine("Group Name");

using (var context = new PrincipalContext(ContextType.Domain, domainName))
{
using (var group = GroupPrincipal.FindByIdentity(context, groupName))
{
if (group == null)
{
"Group does not exist".Dump();
}
else
{

var users = group.GetMembers(true);
foreach (UserPrincipal user in users)
{
user.Dump();
}
}
}
}
-------------------------------
utility can be found at link



custom distinct by in linq

public static IEnumerable<TSource> DistinctBy<TSource, TKey>

   (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)

        {

            HashSet<TKey> seenKeys = new HashSet<TKey>();

            foreach (TSource element in source)

            {

                if (seenKeys.Add(keySelector(element)))

                {

                    yield return element;

                }

            }

        }

nodeJS launch.json for debugging node js projects

1) Open cmd  at web api rootà run Tsc –w

2) now using debug options start debugging, updated launch.json and tsconfig.json are attached

It enables an auto watch.

launch.json

settings.json

tsconfig.json

snapshot

Build process

1) npm run build

2) copy dist folder

3) run gulp and copy the web build inside public folder of server

4) run powershell on server, then run à node server.js

basic steps git repository + node app

Git ignore details

https://elanderson.net/2016/09/add-git-ignore-to-existing-visual-studio-project/

Clone the repo from git

C:\Users\Pahuja Saurabh\saurabh\projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API>git clone http://appstfs.sample.com/tfs/AppsSAMPLE/_git/DriverBasedModel

Navigate to folder where packages.json is present

C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API>cd C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API

Installing all the node packages as defined in package.json

C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API>npm install

starting node server

C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API>npm start

eg of installing a module (here passport)

C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API>npm install passport –save

checking if node has started:

C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedModel\DriverBasedModel\DriverBasedModel.API>node

> 2+2

4

>  

Sample chkin procedure

Move to the project root location where .git is placed

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ cd "C:\Users\Pahuja Saurabh\saurabh\sample projects\SonyPictures\DriverBasedMode

l"

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git status

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git branch

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git stash –u

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git stash pop

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git stash –u

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git checkout -b sql

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git branch

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git stash pop

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git status

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git add .

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git status

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git commit -m "sql interaction"

Pahuja Saurabh@IKA-PAHUJSAU1 MINGW64 ~/saurabh/sample projects/SonyPictures/DriverBasedModel (sql)$ git push origin sql

Sql is the branch name

Creating a branch and merging back

• take latest pull from master:  git pull
• create new branch : git checkout –b {branchname}      {-b is creating the branch, if already created –b is not needed}
• do your work in this branch, commit in this branch itself
• on merging back:
• git checkout master
• git pull
• git checkput {branchname}
• git rebase master   à if conflicts are there, they would be shown. resolve/merge the conflicts
• once resolved, git add .
• git rebase –continue
• git push origin {branchname}
• now create a pull request from master to merge {branchname} into it

merging a branch to master

• git checkout codeAlt
• git status
• git add .
• git commit -m "sampleMsg"
• git checkout master
• git pull
• git checkout codeAlt
• git rebase master
• git push origin codeAlt
• git push -f origin codeAlt   à force push, use only if above line gives you error

deleting the branch

• git checkout master
• git pull
• git branch
• git branch -D codeAlt

Resolving multiple promises , closure and hosting

promise

then((pool: any) => {

         Promise.all([pool.request()

        .query(queryStr),

        pool.request()

        .query(queryStr),

        pool.request()

        .query(queryStr)] )

        .then((result: any) => {

           

       })

Closure

function like (){

var like=0;

return function(){

    like++;

    console.log(like);

}

}

undefined

var myLike = new like()

hosting

add ();

function add(){console.log("adkka")}

[5:12]

add ();

var add = function (){console.log("adkka")}

Running large sql server file via command prompt + sql server bulk copy

Sql via command line

C:\Users\Pahuja Saurabh\Desktop\harinder>sqlcmd -S PAHUJSAU1\SQLEXPRESS -i R

eferencesTables17.sql

PS C:\Users\Pahuja Saurabh\DbScriptFolder> sqlcmd -S 981.143.99.95 -U peritias_demouser -P 'pwd' -i DataScript13Jan18.sql -o

C:\Temp\SqlQueryOutput.txt

sqlcmd -S localhost\sqlsrv2012 -d AdventureWorks2012 -i C:\DataFiles\EmployeeQuery.sql -o C:\DataFiles\Employees.txt

To specify a username, add the -U switch followed by the username. To include a password, add the -P switch followed by the password. If you enter the username option without the password option, you’ll be prompted for a password.

https://www.red-gate.com/simple-talk/sql/sql-tools/sql-server-sqlcmd-basics/

SQL server bulk copy sample

private static void CreateTableInDataBaseOfProductDataUploaded(BOMDataContext context, DataTable productDataTable, string productDataTempTableNameInSql)

        {

            using (var con = new SqlConnection(context.Connection.ConnectionString))

            {

                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(context.Connection.ConnectionString,

                    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepIdentity))

                {

                    sqlBulkCopy.DestinationTableName = "ProductDataTableTemplate";

                    productDataTable.AcceptChanges();

                    foreach (var columnsMapping in AppConstant.ProductBoMDataTableColumnMapping)

                    {

                        sqlBulkCopy.ColumnMappings.Add(columnsMapping.Value, columnsMapping.Value);

                    }

                    sqlBulkCopy.ColumnMappings.Add(

                        AppConstant.Constant.ROWNUMBERCOLUMNINPRODUCTDATATABLE,

                        AppConstant.Constant.ROWNUMBERCOLUMNINPRODUCTDATATABLE);

                    sqlBulkCopy.ColumnMappings.Add(

                        AppConstant.Constant.INSTANCEIDCOLUMNINPRODUCTDATATEMPTABLE,

                        AppConstant.Constant.INSTANCEIDCOLUMNINPRODUCTDATATEMPTABLE);

                    con.Open();

                    sqlBulkCopy.WriteToServer(productDataTable);

                    con.Close();

                }

            }

        }

public static Dictionary<string, string> ProductBoMDataTableColumnMapping

            = new Dictionary<string, string>() {

                { "Column1", "SubAssemblyId"},

                { "Column2", "ComponentId"},

                { "Column3", "KeyStepId"},

                { "Column4", "SubAssembly" },

                { "Column5", "Component" },

                { "Column6", "KeyStep" },

                { "Column7", "Quantities" },

                { "Column8", "SupplierMaterialUnit" },

                { "Column9", "SupplierLaborUnit" },

                { "Column10", "SupplierSetupUnit" },

                { "Column11", "SupplierDirectTotal" },

                { "Column12", "SupplierOHBurdenUnit" },

                { "Column13", "LowestCurrentQuote" },

                { "Column14", "AnalysisMaterialUnit" },

                { "Column15", "AnalysisLaborUnit" },

                { "Column16", "AnalysisSetupUnit" },

                { "Column17", "AnalysisDirectTotal" },

                { "Column18", "AnalysisOHBurdenUnit" },

                { "Column19", "ShouldCostOfBoM" }

            };