问:
Custom function to create an index of results
I’m trying to create a function which creates an index (starting at 100) and then adjust this index according to the results of investments. So, in a nutshell, if the first investment gives an profit of 5%, then the index will stand 105, if the second result is -7%, then the index stands at 97.65. In this question when I use the word "index", I'm not referring to the index function of the zoo package.

Besides creating this index, my goal is also to create an function which can be applied to various subsets of my complete data set (i.e. with the use of sapply and it's friends).

Here’s the function which I have so far (data at end of this question):

```<code>CalculateIndex <- function(x){
    totalAccount <- accountValueStart
    if(x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)){
        indexedValues <- 100 + ( 100 *((((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100))
        # Update the accountvalue
        totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    else{ # the value is not the first
        indexedValues <- c(indexedValues,
                indexedValues[-1] + (indexedValues[-1] *(((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100)
                )
            # Update the accountvalue
            totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    return(indexedValues)
}
</code>```

In words the function does (read: is intended to do) the following:
If the value is the first, use 100 as an starting point for the index. If the value is not the first, use the previous calculated index value as the starting point for calculating the new index value. Besides this, the function also takes the weight of the individual result (compared with the totalAccount value) into account.

The problem:
Using this CalculateIndex function on the theData data frame gives the following incorrect output:

```<code>> CalculateIndex(theData)
 [1]  99.97901  99.94180  99.65632 101.88689 100.89309  98.92878 102.02911 100.49159  98.52955 102.02243  98.43655 100.76502  99.34869 100.76401 101.18014  99.75136  97.90130
[18] 100.39935  99.81311 101.34961
Warning message:
In if (x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)) { :
  the condition has length > 1 and only the first element will be used
</code>```

Edit:
Wow, I already got an vote down, though I thought my question was already too long. Sorry, I thought/think the problem lay inside my loop, so I didn't want to bore you with the details, which I thought would only give less answers. Sorry, misjudgement on my part.

The problem is, with the above output from CalculateIndex, that the results are wildly different from Excel. Even though this could be resulting from rounding errors (as Joris mentions below), I doubt it. In comparison with the Excel results, the R results differ quite some:

```<code>R output    Excel calculate values
99,9790085700   99,97900857
99,9418035700   99,92081189
99,6563228600   99,57713687
101,8868850000  101,4639947
100,8930864300  102,3570786
98,9287771400   101,2858564
102,0291071400  103,3149664
100,4915864300  103,806556
98,5295542900   102,3361186
102,0224285700  104,3585552
98,4365550000   102,795089
100,7650171400  103,5601228
99,3486857100   102,9087897
100,7640057100  103,6728077
101,1801400000  104,8529634
99,7513600000   104,6043164
97,9013000000   102,5055298
100,3993485700  102,9048999
99,8131085700   102,7179995
101,3496071400  104,0676555
</code>```

I think it would be fair to say that the difference in output isn't the result of R versus Excel problems, but more an error in my function. So, let's focus on the function.

The manual calculation of the function
The function uses different variables:

Size.Units.; this is the number of units which are bought at the EntryPrice.

EntryPrice: the price at which the stocks are bought,

TradeResult.Percent.: the percentage gain or loss resulting from the investment,

TradeResult.Currency.: the currency value ($) of the gain or loss resulting from the investment,

These variables are used in the following section of the function:

```<code>100 + ( 100 *((((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100))
</code>```

and

```<code>indexedValues[-1] + (indexedValues[-1] *(((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100)
</code>```

Both of the formula's are essentially the same, with the difference that the the first starts at 100, and the second uses the previous value to calculate the new indexed value.

The formula can be broken down in different steps:

First, x$Size.Units. * x$EntryPrice determines the total position that was taken, in the sense that buying 100 shares at an price of 48.98 gives an position of $4898.

The resulting total position is then divided by the total account size (i.e. totalAccount). This is needed to correct the impact of one position relative to the complete portfolio. For example, if our 100 shares bought at 48.98 drop 10 percent, the calculated index (i.e. the CalculateIndex function) doesn't have to drop 10%, because off course not all the money in totalAccount is invested in one stock. So, by dividing the total position by the totalAccount we get an ratio which tells us how much money is invested. For example, the position with the size of 4898 dollar (on a total account of 14000) results in a total account loss of 3.49% if the stock drops 10%. (i.e. 4898 / 14000 = 0.349857. 0.349857 * 10% = 3.49857%)

This ratio (of invested amount versus total amount) is then in the formula multiplied with x$TradeResult.Percent., so to get the percentage impact on the total account (see calculation example in the previous paragraph).

As an last step, the percentage loss on the total account is applied to the index value (which starts at 100). In this case, the first investment in 100 shares bought at 48.89 dollar let's the index drop from it starting point at 100 to 99.97901, reflecting the losing trade's impact on the total account.

End of Edit

Stripping the function clean and then adding a part of the formula at a time, so to uncover the error, I came to the following step where the error seems to reside:

```<code>CalculateIndex <- function(x){
    totalAccount <- accountValueStart
    if(x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)){
        indexedValues <- totalAccount
        # Update the accountvalue
        totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    else{ # the value is not the first
        indexedValues <- c(indexedValues, totalAccount)
            # Update the accountvalue
            totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    return(indexedValues)
}
> CalculateIndex(theData)
[1] 14000
Warning message:
In if (x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)) { :
  the condition has length > 1 and only the first element will be used
</code>```

So, it seems that if I just use the totalAccount variable, the function doesn’t get updated correctly. This seems to suggest there is some error with the basics of the if else statement, because it only outputs the first value.

If I remove the else statement from the function, I do get values for each of the rows in theData. However, these are then wrongly calculated. So, it seems to me that there is some error in how this function updates the totalAccount variable. I don’t see where I made an error, so any suggestion would be highly appreciated. What am I doing wrong?

The Data

Here’s what my data looks like:

```<code>> theData
   Size.Units. EntryPrice TradeResult.Percent. TradeResult.Currency.
1          100      48.98                -0.06                    -3
11         100      32.59                -0.25                    -8
12         100      32.51                -1.48                   -48
2          100      49.01                 5.39                   264
13         100      32.99                 3.79                   125
14         100      34.24                -4.38                  -150
3          100      51.65                 5.50                   284
4          100      48.81                 1.41                    69
15         100      35.74                -5.76                  -206
5          100      49.50                 5.72                   283
6          100      46.67                -4.69                  -219
16         100      33.68                 3.18                   107
7          100      44.48                -2.05                   -91
17         100      32.61                 3.28                   107
8          100      45.39                 3.64                   165
9          100      47.04                -0.74                   -35
10         100      47.39                -6.20                  -294
18         100      33.68                 1.66                    56
19         100      33.12                -0.79                   -26
20         100      32.86                 5.75                   189

theData <- structure(list(X = c(1L, 11L, 12L, 2L, 13L, 14L, 3L, 4L, 15L,
    5L, 6L, 16L, 7L, 17L, 8L, 9L, 10L, 18L, 19L, 20L), Size.Units. = c(100L,
    100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L,
    100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L), EntryPrice = c(48.98,
    32.59, 32.51, 49.01, 32.99, 34.24, 51.65, 48.81, 35.74, 49.5,
    46.67, 33.68, 44.48, 32.61, 45.39, 47.04, 47.39, 33.68, 33.12,
    32.86), TradeResult.Percent. = c(-0.06, -0.25, -1.48, 5.39, 3.79,
    -4.38, 5.5, 1.41, -5.76, 5.72, -4.69, 3.18, -2.05, 3.28, 3.64,
    -0.74, -6.2, 1.66, -0.79, 5.75), TradeResult.Currency. = c(-3L,
    -8L, -48L, 264L, 125L, -150L, 284L, 69L, -206L, 283L, -219L,
    107L, -91L, 107L, 165L, -35L, -294L, 56L, -26L, 189L)), .Names = c("X",
    "Size.Units.", "EntryPrice", "TradeResult.Percent.", "TradeResult.Currency."
    ), class = "data.frame", row.names = c(NA, -20L))

# Set the account start @ 14000
> accountValueStart <- 14000
</code>```

答:
Your code looks very strange, and it seems you have a lot of misconceptions about R that come from another programming language. Gavin and Gillespie pointed out already why you get the warniong. Let me add some tips for far more optimal coding:

[-1] does NOT mean: drop the last one. It means "keep everything but the first value", which also explains why you get erroneous results.

calculate common things in the beginning, to unclutter your code.

head(x$TradeResult.Currency., n = 1) is the same as x$TradeResult.Currency.[1].

Keep an eye on your vectors. Most of the mistakes in your code come from forgetting you're working with vectors.

If you need a value to be the first in a vector, put that OUTSIDE of any loop you'd use, never add an if-clause in the function.

predefine your vectors/matrices as much as possible, that goes a lot faster and gives less memory headaches when working with big data.

vectorization, vectorization, vectorization. Did I mention vectorization?

Learn the use of debug(), debugonce() and browser() to check what your function is doing. Many of your problems could have been solved by checking the objects when manipulated within the function.

This said and taken into account, your function becomes :

```<code>CalculateIndex <- function(x,accountValueStart){
  # predifine your vector
  indexedValues <- vector("numeric",nrow(x))
  # get your totalAccount calculated FAST. This is a VECTOR!!!
  totalAccount <- cumsum(c(accountValueStart,x$TradeResult.Currency.))
  #adjust length:
  totalAccount <- totalAccount[-(nrow(x)+1)]

  # only once this calculation. This is a VECTOR!!!!
  totRatio <- 1+(((x$Size.Units. * x$EntryPrice)/totalAccount) *
                 x$TradeResult.Percent.)/100

  # and now the calculations
  indexedValues[1] <- 100 * totRatio[1]
  for(i in 2:nrow(x)){
      indexedValues[i] <- indexedValues[i-1]*totRatio[i]
  }
  return(indexedValues)
}
</code>```

and returns

```<code>> CalculateIndex(theData,14000)
[1]  99.97901  99.92081  99.57714 101.46399 102.35708 101.28586 103.31497
 103.80656 102.33612 104.35856 102.79509 103.56012
[13] 102.90879 103.67281 104.85296 104.60432 102.50553 102.90490 102.71800
 104.06766
</code>```

So now you do:

```<code> invisible(replicate(10,print("I will never forget about vectorization any more!")))
</code>```

Body:
I’m trying to create a function which creates an index (starting at 100) and then adjust this index according to the results of investments. So, in a nutshell, if the first investment gives an profit of 5%, then the index will stand 105, if the second result is -7%, then the index stands at 97.65. In this question when I use the word "index", I'm not referring to the index function of the zoo package.

Besides creating this index, my goal is also to create an function which can be applied to various subsets of my complete data set (i.e. with the use of sapply and it's friends).

Here’s the function which I have so far (data at end of this question):

CalculateIndex <- function(x){
    totalAccount <- accountValueStart
    if(x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)){
        indexedValues <- 100 + ( 100 *((((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100))
        # Update the accountvalue
        totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    else{ # the value is not the first
        indexedValues <- c(indexedValues,
                indexedValues[-1] + (indexedValues[-1] *(((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100)
                )
            # Update the accountvalue
            totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    return(indexedValues)
}

In words the function does (read: is intended to do) the following:
If the value is the first, use 100 as an starting point for the index. If the value is not the first, use the previous calculated index value as the starting point for calculating the new index value. Besides this, the function also takes the weight of the individual result (compared with the totalAccount value) into account.

The problem:
Using this CalculateIndex function on the theData data frame gives the following incorrect output:

> CalculateIndex(theData)
 [1]  99.97901  99.94180  99.65632 101.88689 100.89309  98.92878 102.02911 100.49159  98.52955 102.02243  98.43655 100.76502  99.34869 100.76401 101.18014  99.75136  97.90130
[18] 100.39935  99.81311 101.34961
Warning message:
In if (x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)) { :
  the condition has length > 1 and only the first element will be used

Edit:
Wow, I already got an vote down, though I thought my question was already too long. Sorry, I thought/think the problem lay inside my loop, so I didn't want to bore you with the details, which I thought would only give less answers. Sorry, misjudgement on my part.

The problem is, with the above output from CalculateIndex, that the results are wildly different from Excel. Even though this could be resulting from rounding errors (as Joris mentions below), I doubt it. In comparison with the Excel results, the R results differ quite some:

R output    Excel calculate values
99,9790085700   99,97900857
99,9418035700   99,92081189
99,6563228600   99,57713687
101,8868850000  101,4639947
100,8930864300  102,3570786
98,9287771400   101,2858564
102,0291071400  103,3149664
100,4915864300  103,806556
98,5295542900   102,3361186
102,0224285700  104,3585552
98,4365550000   102,795089
100,7650171400  103,5601228
99,3486857100   102,9087897
100,7640057100  103,6728077
101,1801400000  104,8529634
99,7513600000   104,6043164
97,9013000000   102,5055298
100,3993485700  102,9048999
99,8131085700   102,7179995
101,3496071400  104,0676555

I think it would be fair to say that the difference in output isn't the result of R versus Excel problems, but more an error in my function. So, let's focus on the function.

The manual calculation of the function
The function uses different variables:

Size.Units.; this is the number of units which are bought at the EntryPrice.

EntryPrice: the price at which the stocks are bought,

TradeResult.Percent.: the percentage gain or loss resulting from the investment,

TradeResult.Currency.: the currency value ($) of the gain or loss resulting from the investment,

These variables are used in the following section of the function:

100 + ( 100 *((((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100))

and

indexedValues[-1] + (indexedValues[-1] *(((x$Size.Units. * x$EntryPrice) / totalAccount) * x$TradeResult.Percent.) / 100)

Both of the formula's are essentially the same, with the difference that the the first starts at 100, and the second uses the previous value to calculate the new indexed value.

The formula can be broken down in different steps:

First, x$Size.Units. * x$EntryPrice determines the total position that was taken, in the sense that buying 100 shares at an price of 48.98 gives an position of $4898.

The resulting total position is then divided by the total account size (i.e. totalAccount). This is needed to correct the impact of one position relative to the complete portfolio. For example, if our 100 shares bought at 48.98 drop 10 percent, the calculated index (i.e. the CalculateIndex function) doesn't have to drop 10%, because off course not all the money in totalAccount is invested in one stock. So, by dividing the total position by the totalAccount we get an ratio which tells us how much money is invested. For example, the position with the size of 4898 dollar (on a total account of 14000) results in a total account loss of 3.49% if the stock drops 10%. (i.e. 4898 / 14000 = 0.349857. 0.349857 * 10% = 3.49857%)

This ratio (of invested amount versus total amount) is then in the formula multiplied with x$TradeResult.Percent., so to get the percentage impact on the total account (see calculation example in the previous paragraph).

As an last step, the percentage loss on the total account is applied to the index value (which starts at 100). In this case, the first investment in 100 shares bought at 48.89 dollar let's the index drop from it starting point at 100 to 99.97901, reflecting the losing trade's impact on the total account.

End of Edit

Stripping the function clean and then adding a part of the formula at a time, so to uncover the error, I came to the following step where the error seems to reside:

CalculateIndex <- function(x){
    totalAccount <- accountValueStart
    if(x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)){
        indexedValues <- totalAccount
        # Update the accountvalue
        totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    else{ # the value is not the first
        indexedValues <- c(indexedValues, totalAccount)
            # Update the accountvalue
            totalAccount <- totalAccount + x$TradeResult.Currency.
    }
    return(indexedValues)
}
> CalculateIndex(theData)
[1] 14000
Warning message:
In if (x$TradeResult.Currency == head(x$TradeResult.Currency., n = 1)) { :
  the condition has length > 1 and only the first element will be used

So, it seems that if I just use the totalAccount variable, the function doesn’t get updated correctly. This seems to suggest there is some error with the basics of the if else statement, because it only outputs the first value.

If I remove the else statement from the function, I do get values for each of the rows in theData. However, these are then wrongly calculated. So, it seems to me that there is some error in how this function updates the totalAccount variable. I don’t see where I made an error, so any suggestion would be highly appreciated. What am I doing wrong?

The Data

Here’s what my data looks like:

> theData
   Size.Units. EntryPrice TradeResult.Percent. TradeResult.Currency.
1          100      48.98                -0.06                    -3
11         100      32.59                -0.25                    -8
12         100      32.51                -1.48                   -48
2          100      49.01                 5.39                   264
13         100      32.99                 3.79                   125
14         100      34.24                -4.38                  -150
3          100      51.65                 5.50                   284
4          100      48.81                 1.41                    69
15         100      35.74                -5.76                  -206
5          100      49.50                 5.72                   283
6          100      46.67                -4.69                  -219
16         100      33.68                 3.18                   107
7          100      44.48                -2.05                   -91
17         100      32.61                 3.28                   107
8          100      45.39                 3.64                   165
9          100      47.04                -0.74                   -35
10         100      47.39                -6.20                  -294
18         100      33.68                 1.66                    56
19         100      33.12                -0.79                   -26
20         100      32.86                 5.75                   189

theData <- structure(list(X = c(1L, 11L, 12L, 2L, 13L, 14L, 3L, 4L, 15L,
    5L, 6L, 16L, 7L, 17L, 8L, 9L, 10L, 18L, 19L, 20L), Size.Units. = c(100L,
    100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L,
    100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L), EntryPrice = c(48.98,
    32.59, 32.51, 49.01, 32.99, 34.24, 51.65, 48.81, 35.74, 49.5,
    46.67, 33.68, 44.48, 32.61, 45.39, 47.04, 47.39, 33.68, 33.12,
    32.86), TradeResult.Percent. = c(-0.06, -0.25, -1.48, 5.39, 3.79,
    -4.38, 5.5, 1.41, -5.76, 5.72, -4.69, 3.18, -2.05, 3.28, 3.64,
    -0.74, -6.2, 1.66, -0.79, 5.75), TradeResult.Currency. = c(-3L,
    -8L, -48L, 264L, 125L, -150L, 284L, 69L, -206L, 283L, -219L,
    107L, -91L, 107L, 165L, -35L, -294L, 56L, -26L, 189L)), .Names = c("X",
    "Size.Units.", "EntryPrice", "TradeResult.Percent.", "TradeResult.Currency."
    ), class = "data.frame", row.names = c(NA, -20L))

# Set the account start @ 14000
> accountValueStart <- 14000

Body:
Your code looks very strange, and it seems you have a lot of misconceptions about R that come from another programming language. Gavin and Gillespie pointed out already why you get the warniong. Let me add some tips for far more optimal coding:

[-1] does NOT mean: drop the last one. It means "keep everything but the first value", which also explains why you get erroneous results.

calculate common things in the beginning, to unclutter your code.

head(x$TradeResult.Currency., n = 1) is the same as x$TradeResult.Currency.[1].

Keep an eye on your vectors. Most of the mistakes in your code come from forgetting you're working with vectors.

If you need a value to be the first in a vector, put that OUTSIDE of any loop you'd use, never add an if-clause in the function.

predefine your vectors/matrices as much as possible, that goes a lot faster and gives less memory headaches when working with big data.

vectorization, vectorization, vectorization. Did I mention vectorization?

Learn the use of debug(), debugonce() and browser() to check what your function is doing. Many of your problems could have been solved by checking the objects when manipulated within the function.

This said and taken into account, your function becomes :

CalculateIndex <- function(x,accountValueStart){
  # predifine your vector
  indexedValues <- vector("numeric",nrow(x))
  # get your totalAccount calculated FAST. This is a VECTOR!!!
  totalAccount <- cumsum(c(accountValueStart,x$TradeResult.Currency.))
  #adjust length:
  totalAccount <- totalAccount[-(nrow(x)+1)]

  # only once this calculation. This is a VECTOR!!!!
  totRatio <- 1+(((x$Size.Units. * x$EntryPrice)/totalAccount) *
                 x$TradeResult.Percent.)/100

  # and now the calculations
  indexedValues[1] <- 100 * totRatio[1]
  for(i in 2:nrow(x)){
      indexedValues[i] <- indexedValues[i-1]*totRatio[i]
  }
  return(indexedValues)
}

and returns

> CalculateIndex(theData,14000)
[1]  99.97901  99.92081  99.57714 101.46399 102.35708 101.28586 103.31497
 103.80656 102.33612 104.35856 102.79509 103.56012
[13] 102.90879 103.67281 104.85296 104.60432 102.50553 102.90490 102.71800
 104.06766

So now you do:

 invisible(replicate(10,print("I will never forget about vectorization any more!")))


来源: stackexchange

问:
sql stored procedure conditional where clause returning syntax error
I have the following stored procedure which takes 2 parameters. The @Class parameter can have any one of 11 values. Depending on the value the where clause looks at a different column.

The procedure compiles ok and if I copy the print and paste it into a new query window it runs fine, but if I execute the procedure I am getting an Incorrect syntax error message near 'Bristol' but cannot figure out what I need to do to fix despite looking on here and on other web pages. Any help would be much appreciated

```<code>ALTER PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)

AS
BEGIN
DECLARE @Where nvarchar(1000)
DECLARE @sSql nvarchar(MAX)
DECLARE @Order nvarchar(1000)

SET @sSql = '
SELECT EM.EmployeeNumber
, EM.EmployeeSurname
, EM.EmployeeInitials
, D.Depot
, EDL.Class1
, EDL.Class2
, EDL.Class3
, EDL.Class4
, EDL.Class5
, EDL.Class6
, EDL.Class7
, EDL.Class8
, EDL.Class9
, CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
, EDL.Tanks
, EDL.Package
FROM   EmployeeMaster EM
LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode
INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode
LEFT OUTER JOIN Depot D ON EM.Depot = D.DescCode'

SET @Where = '
WHERE (D.Depot = ''' + @Depot + ''' OR ''' + @Depot + ''' IS NULL)
AND EM.EmployeeLeft = ''N''
AND PF.FrequencyDesc = ''Weekly'''

SET @Order = '
    ORDER BY D.DepotDepotDescription
    , EDL.ExpiryDate'

IF @Class = '1'
SET @Where = @Where + ' AND EDL.Class1 = ''Y'''
IF @Class = '2'
SET @Where = @Where + ' AND EDL.Class2 = ''Y'''
IF @Class = '3'
SET @Where = @Where + ' AND EDL.Class3 = ''Y'''
IF @Class = '4'
SET @Where = @Where + ' AND EDL.Class4 = ''Y'''
IF @Class = '5'
SET @Where = @Where + ' AND EDL.Class5 = ''Y'''
IF @Class = '6'
SET @Where = @Where + ' AND EDL.Class6 = ''Y'''
IF @Class = '7'
SET @Where = @Where + ' AND EDL.Class7 = ''Y'''
IF @Class = '8'
SET @Where = @Where + ' AND EDL.Class8 = ''Y'''
IF @Class = '9'
SET @Where = @Where + ' AND EDL.Class9 = ''Y'''
IF @Class = 'T'
SET @Where = @Where + ' AND EDL.Tanks = ''Y'''
IF @Class = 'P'
SET @Where = @Where + ' AND EDL.Package = ''Y'''

SET @sSql = @sSql + @Where + @Order
PRINT @sSql

EXEC sp_executesql @sSQL, @Depot, @Class
END
</code>```

The print statement produces the following query:

```<code>SELECT EM.EmployeeNumber
 , EM.EmployeeSurname
 , EM.EmployeeInitials
 , D.Depot
 , EDL.Class1
 , EDL.Class2
 , EDL.Class3
 , EDL.Class4
 , EDL.Class5
 , EDL.Class6
 , EDL.Class7
 , EDL.Class8
 , EDL.Class9
 , CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
 , EDL.Tanks
 , EDL.Package
 FROM   EmployeeMaster EM
 LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode
 INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode
 LEFT OUTER JOIN Depot D ON EM.DepotDepotDescription = D.DescCode
 WHERE (D.DepotDepotDescription = 'Bristol' OR 'Bristol' IS NULL)
 AND PF.FrequencyDesc = 'Weekly'
 AND EDL.Class3 = 'Y'
    ORDER BY D.Depot
    , EDL.ExpiryDate
</code>```

答:
Since you're embedding the parameter values into the query variable, you don't need to add the params to the sp_executesql call, so this should work:

```<code>EXEC sp_executesql @sSQL
</code>```

Instead of:

```<code>EXEC sp_executesql @sSQL, @Depot, @Class
</code>```

Here's a full working example with DDL statements - swap out the last line with the original version, and you'll get the syntax error.

```<code>CREATE TABLE EmployeeMaster (EmployeeNumber INT, EmployeeSurname VARCHAR(25), EmployeeInitials VARCHAR(10), FrequencyDesc VARCHAR(25), EmpCode VARCHAR(25), Depot VARCHAR(25), EmployeeLeft VARCHAR(1))
CREATE TABLE PayrollFrequency (DescCode VARCHAR(25), FrequencyDesc VARCHAR(25))
CREATE TABLE EmployeeDrivingLicence (EmpCode VARCHAR(25), Class1 VARCHAR(1), Package VARCHAR(1), Tanks VARCHAR(1))
ALTER TABLE EmployeeDrivingLicence ADD ExpiryDate DATETIME
CREATE TABLE Depot (Depot VARCHAR(25), DescCode VARCHAR(25), DepotDepotDescription VARCHAR(25))

CREATE PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)

AS
BEGIN

...

EXEC sp_executesql @sSQL
END
GO

EXEC [TTR_HazDriver] 'test', 'P'
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I have the following stored procedure which takes 2 parameters. The @Class parameter can have any one of 11 values. Depending on the value the where clause looks at a different column.

The procedure compiles ok and if I copy the print and paste it into a new query window it runs fine, but if I execute the procedure I am getting an Incorrect syntax error message near 'Bristol' but cannot figure out what I need to do to fix despite looking on here and on other web pages. Any help would be much appreciated

ALTER PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)

AS
BEGIN
DECLARE @Where nvarchar(1000)
DECLARE @sSql nvarchar(MAX)
DECLARE @Order nvarchar(1000)

SET @sSql = '
SELECT EM.EmployeeNumber
, EM.EmployeeSurname
, EM.EmployeeInitials
, D.Depot
, EDL.Class1
, EDL.Class2
, EDL.Class3
, EDL.Class4
, EDL.Class5
, EDL.Class6
, EDL.Class7
, EDL.Class8
, EDL.Class9
, CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
, EDL.Tanks
, EDL.Package
FROM   EmployeeMaster EM
LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode
INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode
LEFT OUTER JOIN Depot D ON EM.Depot = D.DescCode'

SET @Where = '
WHERE (D.Depot = ''' + @Depot + ''' OR ''' + @Depot + ''' IS NULL)
AND EM.EmployeeLeft = ''N''
AND PF.FrequencyDesc = ''Weekly'''

SET @Order = '
    ORDER BY D.DepotDepotDescription
    , EDL.ExpiryDate'

IF @Class = '1'
SET @Where = @Where + ' AND EDL.Class1 = ''Y'''
IF @Class = '2'
SET @Where = @Where + ' AND EDL.Class2 = ''Y'''
IF @Class = '3'
SET @Where = @Where + ' AND EDL.Class3 = ''Y'''
IF @Class = '4'
SET @Where = @Where + ' AND EDL.Class4 = ''Y'''
IF @Class = '5'
SET @Where = @Where + ' AND EDL.Class5 = ''Y'''
IF @Class = '6'
SET @Where = @Where + ' AND EDL.Class6 = ''Y'''
IF @Class = '7'
SET @Where = @Where + ' AND EDL.Class7 = ''Y'''
IF @Class = '8'
SET @Where = @Where + ' AND EDL.Class8 = ''Y'''
IF @Class = '9'
SET @Where = @Where + ' AND EDL.Class9 = ''Y'''
IF @Class = 'T'
SET @Where = @Where + ' AND EDL.Tanks = ''Y'''
IF @Class = 'P'
SET @Where = @Where + ' AND EDL.Package = ''Y'''

SET @sSql = @sSql + @Where + @Order
PRINT @sSql

EXEC sp_executesql @sSQL, @Depot, @Class
END

The print statement produces the following query:

SELECT EM.EmployeeNumber
 , EM.EmployeeSurname
 , EM.EmployeeInitials
 , D.Depot
 , EDL.Class1
 , EDL.Class2
 , EDL.Class3
 , EDL.Class4
 , EDL.Class5
 , EDL.Class6
 , EDL.Class7
 , EDL.Class8
 , EDL.Class9
 , CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
 , EDL.Tanks
 , EDL.Package
 FROM   EmployeeMaster EM
 LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode
 INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode
 LEFT OUTER JOIN Depot D ON EM.DepotDepotDescription = D.DescCode
 WHERE (D.DepotDepotDescription = 'Bristol' OR 'Bristol' IS NULL)
 AND PF.FrequencyDesc = 'Weekly'
 AND EDL.Class3 = 'Y'
    ORDER BY D.Depot
    , EDL.ExpiryDate

Body:
Since you're embedding the parameter values into the query variable, you don't need to add the params to the sp_executesql call, so this should work:

EXEC sp_executesql @sSQL

Instead of:

EXEC sp_executesql @sSQL, @Depot, @Class

Here's a full working example with DDL statements - swap out the last line with the original version, and you'll get the syntax error.

CREATE TABLE EmployeeMaster (EmployeeNumber INT, EmployeeSurname VARCHAR(25), EmployeeInitials VARCHAR(10), FrequencyDesc VARCHAR(25), EmpCode VARCHAR(25), Depot VARCHAR(25), EmployeeLeft VARCHAR(1))
CREATE TABLE PayrollFrequency (DescCode VARCHAR(25), FrequencyDesc VARCHAR(25))
CREATE TABLE EmployeeDrivingLicence (EmpCode VARCHAR(25), Class1 VARCHAR(1), Package VARCHAR(1), Tanks VARCHAR(1))
ALTER TABLE EmployeeDrivingLicence ADD ExpiryDate DATETIME
CREATE TABLE Depot (Depot VARCHAR(25), DescCode VARCHAR(25), DepotDepotDescription VARCHAR(25))

CREATE PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)

AS
BEGIN

...

EXEC sp_executesql @sSQL
END
GO

EXEC [TTR_HazDriver] 'test', 'P'


问:
Create and update same or different entity record in single ExecuteTransactionRequest or ExecuteMultipleRequest
I want to Create and Update OR Update and Delete records on single or multiple entities in single transaction so that if any co-related transaction gets failed other should be rolled back.

I have tried to search over the net but didn't have luck on this. Is there any possible way we can achieve this?

答:
I wrote the below code to test if a Create and Update is possible in a transaction. It appears that the answer is, "No".

It fails because the UpdateRequest doesn't have access to the Id of the Account that got created earlier in the transaction. Update and Delete seems like it should be possible.

On the Create and Update scenario you're dealing with, would it be possible to apply the logic of the Update before creating the record?

```<code>var transReq = new ExecuteTransactionRequest()
{
    // Create an empty organization request collection.
    Requests = new OrganizationRequestCollection(),
    ReturnResponses = true
};

var account = new Account()
{
    Name = "Acme, Inc."
};

var createReq = new CreateRequest
{
    Target = account
};

transReq.Requests.Add(createReq);

account.NumberOfEmployees = 100;

var updateReq = new UpdateRequest
{
    Target = account
};

transReq.Requests.Add(updateReq);

var response = (ExecuteTransactionResponse)svc.Execute(transReq);
</code>```

Please check this article:
https://msdn.microsoft.com/en-us/library/mt634414.aspx

Body:
I wrote the below code to test if a Create and Update is possible in a transaction. It appears that the answer is, "No".

It fails because the UpdateRequest doesn't have access to the Id of the Account that got created earlier in the transaction. Update and Delete seems like it should be possible.

On the Create and Update scenario you're dealing with, would it be possible to apply the logic of the Update before creating the record?

var transReq = new ExecuteTransactionRequest()
{
    // Create an empty organization request collection.
    Requests = new OrganizationRequestCollection(),
    ReturnResponses = true
};

var account = new Account()
{
    Name = "Acme, Inc."
};

var createReq = new CreateRequest
{
    Target = account
};

transReq.Requests.Add(createReq);

account.NumberOfEmployees = 100;

var updateReq = new UpdateRequest
{
    Target = account
};

transReq.Requests.Add(updateReq);

var response = (ExecuteTransactionResponse)svc.Execute(transReq);

Please check this article:
https://msdn.microsoft.com/en-us/library/mt634414.aspx


问:
How way i can do Two array use in a Loop
I have two array and i want to use it in a Loop How way i can do that

Controller Codes are:

```<code>public function showJobCategoryContent($id)
{
    $jobsInfoById = DB::table('jobs')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    $jobsInfoById = $jobsInfoById->map(function ($job) {
        return [
            'created_at' => Carbon::parse($job->created_at)->addDays(30)->toDateTimeString()
        ];
    });

    $dates = [];
    $dates[] = $jobsInfoById->pluck('created_at');
    $jobsInfosById = [];
    $jobsInfosById[] = DB::table('jobs')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    return view('front.category-content.job-category-content',[
        'jobsInfosById'=> $jobsInfosById,
        'dates'=>$dates
    ]);
}
</code>```

How can I use this two array in a loop

My view page:

```<code>@php
    $arrayData = array_merge($jobsInfosById, $dates);
@endphp

@foreach($arrayData as $jobInfoById)
    <li>
        <h4>{{$jobInfoById->company_name}}</h4>
        <h5 class="deadline">Deadline:<b>{{ date('d F, Y', strtotime($jobInfoById->date)) }}</b></h5>
    </li>
@endforeach
</code>```

It shows an error Undefined property:

Illuminate\Pagination\LengthAwarePaginator::$company_name

{
$arrayData Value As Follows:

Array
(
    [0] => Illuminate\Pagination\LengthAwarePaginator Object
        (
            [total:protected] => 3
            [lastPage:protected] => 1
            [items:protected] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 3

```<code>                                [company_name] => Khulan Computer house

                                [published] => 1
                                [created_at] => 2017-10-22 15:25:54
                                [updated_at] => 2017-10-24 11:00:52
                            )

                        [1] => stdClass Object
                            (
                                [id] => 4

                                [company_name] => Khulan Computer house

                                [published] => 1
                                [created_at] => 2017-10-22 15:46:44
                                [updated_at] => 2017-10-24 11:00:41
                            )

                        [2] => stdClass Object
                            (
                                [id] => 11

                                [company_name] => Computer Lab

                                [published] => 1
                                [created_at] => 2017-10-24 08:01:00
                                [updated_at] => 2017-10-24 10:53:57
                            )

                    )

            )

        [perPage:protected] => 3
        [currentPage:protected] => 1
        [path:protected] => http://localhost/jobs-zone/public/category-content/job-category-content/4
        [query:protected] => Array
            (
            )

        [fragment:protected] =>
        [pageName:protected] => page
    )

[1] => Illuminate\Support\Collection Object
    (
        [items:protected] => Array
            (
                [0] => 2017-11-21 15:25:54
                [1] => 2017-11-21 15:46:44
                [2] => 2017-11-23 08:01:00
            )

    )
</code>```

)[![enter image description here][1]][1]

[1]:

答:
you are creating a array and atributing on element 0 of the array and you do 2 queries. So my idea:</p>

```<code>public function showJobCategoryContent($id)
{
    $dates = DB::table('jobs')->select('created_at')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    $jobsInfosById = DB::table('jobs')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    return view('front.category-content.job-category-content',[
        'jobsInfosById'=> $jobsInfosById,
        'dates' => $dates
    ]);
}
</code>```

Body:
I have two array and i want to use it in a Loop How way i can do that

Controller Codes are:

public function showJobCategoryContent($id)
{
    $jobsInfoById = DB::table('jobs')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    $jobsInfoById = $jobsInfoById->map(function ($job) {
        return [
            'created_at' => Carbon::parse($job->created_at)->addDays(30)->toDateTimeString()
        ];
    });

    $dates = [];
    $dates[] = $jobsInfoById->pluck('created_at');
    $jobsInfosById = [];
    $jobsInfosById[] = DB::table('jobs')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    return view('front.category-content.job-category-content',[
        'jobsInfosById'=> $jobsInfosById,
        'dates'=>$dates
    ]);
}

How can I use this two array in a loop

My view page:

@php
    $arrayData = array_merge($jobsInfosById, $dates);
@endphp

@foreach($arrayData as $jobInfoById)

{{$jobInfoById->company_name}}

Deadline:{{ date('d F, Y', strtotime($jobInfoById->date)) }}

@endforeach

It shows an error Undefined property:

Illuminate\Pagination\LengthAwarePaginator::$company_name

{
$arrayData Value As Follows:

Array
(
    [0] => Illuminate\Pagination\LengthAwarePaginator Object
        (
            [total:protected] => 3
            [lastPage:protected] => 1
            [items:protected] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 3

                                [company_name] => Khulan Computer house

                                [published] => 1
                                [created_at] => 2017-10-22 15:25:54
                                [updated_at] => 2017-10-24 11:00:52
                            )

                        [1] => stdClass Object
                            (
                                [id] => 4

                                [company_name] => Khulan Computer house

                                [published] => 1
                                [created_at] => 2017-10-22 15:46:44
                                [updated_at] => 2017-10-24 11:00:41
                            )

                        [2] => stdClass Object
                            (
                                [id] => 11

                                [company_name] => Computer Lab

                                [published] => 1
                                [created_at] => 2017-10-24 08:01:00
                                [updated_at] => 2017-10-24 10:53:57
                            )

                    )

            )

        [perPage:protected] => 3
        [currentPage:protected] => 1
        [path:protected] => http://localhost/jobs-zone/public/category-content/job-category-content/4
        [query:protected] => Array
            (
            )

        [fragment:protected] =>
        [pageName:protected] => page
    )

[1] => Illuminate\Support\Collection Object
    (
        [items:protected] => Array
            (
                [0] => 2017-11-21 15:25:54
                [1] => 2017-11-21 15:46:44
                [2] => 2017-11-23 08:01:00
            )

    )

)[![enter image description here][1]][1]

[1]:

Body:
you are creating a array and atributing on element 0 of the array and you do 2 queries. So my idea:

public function showJobCategoryContent($id)
{
    $dates = DB::table('jobs')->select('created_at')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    $jobsInfosById = DB::table('jobs')->where('category_id', '=', $id)->where('published', '=', 1)->paginate(3);

    return view('front.category-content.job-category-content',[
        'jobsInfosById'=> $jobsInfosById,
        'dates' => $dates
    ]);
}


问:
selectOneMenu inside panelGrid -> row height too big
I have a ui-composition containing a form containing a panelGrid. The panelGrid has 7 rows with 4 columns each. The last row contains in the second column a selectOneMenu.

It was requested to adjust the row height to 22px. Every element in the cells is 21px or less. Firebug shows me, that the selectOneMenu is 21px high als well. But somehow the row will be 25px high. The only css change I found out, that will change this, is setting the height of the inner label of the selectOneMenu from 15px to 12px. But this is no solution, since every input has the inner height of 15px.

```<code><p:row>
<p:column styleClass="columnLabel">
<p:outputLabel value="#{msgs.inv_sum_assignment}" />
</p:column>
<p:column>
<p:selectOneMenu styleClass="assign" disabled="#{summaryBean.noInvoiceSelected}" value="#{summaryBean.assignedUser}" converter="vertreterConverter" immediate="true">
<f:selectItem itemValue="#{null}" />
<f:selectItems value="#{summaryBean.possibleAssignments}" var="vertreter" itemLabel="#{vertreter.lastname}, #{vertreter.firstname}" itemValue="#{vetreter}" />
<p:ajax event="change" />
</p:selectOneMenu>
</p:column>
...
</code>```

screenshot of the cell with the selectOneMenu

Tr style

selectOneMenu Layout

Tr layout

Can you help me, getting the row height to 22px or is that not possible?

I'm using:
primefaces version 6.1
com.sun.faces jsf version 2.2.8

答:
```<code><p:panelGrid columnClasses="rowHeight">
</code>```

In your CSS at the end:

```<code>.rowHeight {
    height: 22px;
    max-height: 22px;
}
</code>```

To force it:

```<code>.rowHeight {
    height: 22px !important;
    max-height: 22px !important;
}
</code>```

If this still doesn't work, you need to go to every element in the panelGrid and remove margin and padding.

If this still doesn't work, it means you customized one of your elements in the panelGrid with a specific height and will need to find witch one ;)

Once it works you can try to remove the max-height and !important. Then you will find the culprit

Body:
I have a ui-composition containing a form containing a panelGrid. The panelGrid has 7 rows with 4 columns each. The last row contains in the second column a selectOneMenu.

It was requested to adjust the row height to 22px. Every element in the cells is 21px or less. Firebug shows me, that the selectOneMenu is 21px high als well. But somehow the row will be 25px high. The only css change I found out, that will change this, is setting the height of the inner label of the selectOneMenu from 15px to 12px. But this is no solution, since every input has the inner height of 15px.

<f:selectItem itemValue="#{null}" />
<f:selectItems value="#{summaryBean.possibleAssignments}" var="vertreter" itemLabel="#{vertreter.lastname}, #{vertreter.firstname}" itemValue="#{vetreter}" />

...

screenshot of the cell with the selectOneMenu

Tr style

selectOneMenu Layout

Tr layout

Can you help me, getting the row height to 22px or is that not possible?

I'm using:
primefaces version 6.1
com.sun.faces jsf version 2.2.8

Body:
<p:panelGrid columnClasses="rowHeight">

In your CSS at the end:

.rowHeight {
    height: 22px;
    max-height: 22px;
}

To force it:

.rowHeight {
    height: 22px !important;
    max-height: 22px !important;
}

If this still doesn't work, you need to go to every element in the panelGrid and remove margin and padding.

If this still doesn't work, it means you customized one of your elements in the panelGrid with a specific height and will need to find witch one ;)

Once it works you can try to remove the max-height and !important. Then you will find the culprit


问:
Find out how many digits a number as in Maple
I'm trying to implement a basic procedure to generate an RSA key. The procedure accepts a range of numbers a and b. It has to check that the intervall between a and b is "five digits".

So I came up with a solution:

```<code>with (numtheory);
gen_rsa := proc(a, b)
  local p, q, len_p, len_q, larger;
  # the two prime-numbers
  p:=safeprime(round(RandomTools[Generate](integer(range=a .. b))-1/2));
  q:=safeprime(round(RandomTools[Generate](integer(rande=a .. b))-1/2));
  if( evalb(log10(p) > log10(q)+5 )
  [...]
</code>```

Thing is: Maple seems to understand p and q as variables of the type function. I want to use the log10 to find out how many digits the prime-number has in order to calculate a safe RSA key. So evalb fails, because it cannot determine the two logarithms??

答:
You shouldn't load the package outside the proc definition -- it's not good practice.

You don't need a call to evalb, when using if...then, as it automatically does that.

You could either use is instead, or evalf both quantities so that the inequality can be tested.

For example,

```<code>gen_rsa := proc(a, b)
local p, q, len_p, len_q, larger;
uses numtheory, RandomTools;
   randomize();
   # the two prime-numbers
   p:=safeprime(round(Generate(integer(range=a .. b))-1/2));
   q:=safeprime(round(Generate(integer(range=a .. b))-1/2));
   if is(log10(p) > log10(q)+5) then
      hi;
   else
      bye;
   end if;
end proc:
</code>```

or you could replace that is call by applying evalf to both sides of the < inequality conditional. (The is command can actually utilize evalf internally, possibly via shake, to figure it out.)

What you mean by the "interval" between p and q being "5 digits" is not clear. If you mean that one must have five more decimal digits that the other then you might want to round or trunc those log10 calls separately. It's hard to say, as the wording is fuzzy.

ps. I also corrected the misspelling "rande" for "range", and removed the inappropriate open-parenthesis right after the if. And the randomize call will make the RandomTools command produce different answers after each restart or in each fresh session.

ContentLicense: CC BY-SA 2.5

Body:
I'm trying to implement a basic procedure to generate an RSA key. The procedure accepts a range of numbers a and b. It has to check that the intervall between a and b is "five digits".

So I came up with a solution:

with (numtheory);
gen_rsa := proc(a, b)
  local p, q, len_p, len_q, larger;
  # the two prime-numbers
  p:=safeprime(round(RandomTools[Generate](integer(range=a .. b))-1/2));
  q:=safeprime(round(RandomTools[Generate](integer(rande=a .. b))-1/2));
  if( evalb(log10(p) > log10(q)+5 )
  [...]

Thing is: Maple seems to understand p and q as variables of the type function. I want to use the log10 to find out how many digits the prime-number has in order to calculate a safe RSA key. So evalb fails, because it cannot determine the two logarithms??

Body:
You shouldn't load the package outside the proc definition -- it's not good practice.

You don't need a call to evalb, when using if...then, as it automatically does that.

You could either use is instead, or evalf both quantities so that the inequality can be tested.

For example,

gen_rsa := proc(a, b)
local p, q, len_p, len_q, larger;
uses numtheory, RandomTools;
   randomize();
   # the two prime-numbers
   p:=safeprime(round(Generate(integer(range=a .. b))-1/2));
   q:=safeprime(round(Generate(integer(range=a .. b))-1/2));
   if is(log10(p) > log10(q)+5) then
      hi;
   else
      bye;
   end if;
end proc:

or you could replace that is call by applying evalf to both sides of the < inequality conditional. (The is command can actually utilize evalf internally, possibly via shake, to figure it out.)

What you mean by the "interval" between p and q being "5 digits" is not clear. If you mean that one must have five more decimal digits that the other then you might want to round or trunc those log10 calls separately. It's hard to say, as the wording is fuzzy.

ps. I also corrected the misspelling "rande" for "range", and removed the inappropriate open-parenthesis right after the if. And the randomize call will make the RandomTools command produce different answers after each restart or in each fresh session.


问:
Error 404 on logfile endpoint when the spring boot application is deployed on Linux
I have a simple spring boot application written for a REST API. I am trying to use logback for logs into a file. It works ok when the application is deployed within eclipse on my windows machine and I can see all my logs on /logfile endpoint.

when I deploy the same application on a Linux box , the logfile is created and updated as expected however when I try to access the logfile over the browser,I get a 404 on the same endpoint(/logfile).

Any help will be much appreciated

My logback configuration is as below

logback-spring.xml

```<code>    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <include resource="org/springframework/boot/logging/logback/base.xml"/>
    <logger name="org.springframework.web" level="DEBUG" additivity="false">
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="FILE"/>
    </logger>
    <logger name="api.controllers" level="DEBUG" additivity="false">
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="FILE"/>
    </logger>
 </configuration>
</code>```

My application.properties file looks like

```<code>    server.contextPath=/api
#Configure Access Logs
server.tomcat.basedir=log
server.tomcat.accesslog.enabled=true
server.tomcat.accesslog.pattern=%t %a "%r" %s (%D ms)

#configure other log
logging.file=log/vixenapi.log
logging.level.org.springframework.web=DEBUG
logging.level.org.springframework.boot.autoconfigure.logging=DEBUG
logging.level.org.hibernate=ERROR
#-------------------------------------

spring.jackson.serialization-inclusion=non_empty

#By Default include all properties. If a view property hasn't been set then it will be shown
spring.jackson.mapper.DEFAULT_VIEW_INCLUSION=true
</code>```

The Log file records the exceptions as

```<code> : Before request [uri=/com.causeway.vixen.api/logfile;client=172.16.4.56]
2017-11-16 18:29:13.662 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : DispatcherServlet with name 'dispatcherServlet' processing GET request for [/com.causeway.vixen.api/logfile]
2017-11-16 18:29:13.662 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : Last-Modified value for [/com.causeway.vixen.api/logfile] is: -1
2017-11-16 18:29:13.663  WARN 1552 --- [http-nio-8080-exec-10] o.s.w.s.r.ResourceHttpRequestHandler     : Locations list is empty. No resources will be served unless a custom ResourceResolver is configured as an alternative to PathResourceResolver.
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : Null ModelAndView returned to DispatcherServlet with name 'dispatcherServlet': assuming HandlerAdapter completed request handling
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : Successfully completed request
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.w.f.CommonsRequestLoggingFilter      : After request [uri=/com.causeway.vixen.api/logfile;client=172.16.4.56]
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : DispatcherServlet with name 'dispatcherServlet' processing GET request for [/com.causeway.vixen.api/error]
2017-11-16 18:29:13.664 DEBUG 1552 --- [http-nio-8080-exec-10] s.w.s.m.m.a.RequestMappingHandlerMapping : Looking up handler method for path /error
</code>```

Here is my minimal example
https://github.com/spring-projects/spring-boot/files/1482233/test_api.zip

答:
There's a trailing space in the value of the logging.file property in both your minimal example and the configuration in your question. The value in the question is "log/vixenapi.log " and it should be "log/vixenapi.log".

You can confirm the problem by enabling debug logging for org.springframework.boot.actuate.endpoint.mvc. LogFileMvcEndpoint will log a message when the log file doesn't exist that includes the path that was checked:

```<code>2017-11-17 14:40:24.801 DEBUG 30140 --- [nio-8080-exec-1] o.s.b.a.endpoint.mvc.LogFileMvcEndpoint  : Log file 'file [/Users/awilkinson/Downloads/test_api/log/test.log ]' does not exist
</code>```

Note the trailing space at the end of the path.

Body:
I have a simple spring boot application written for a REST API. I am trying to use logback for logs into a file. It works ok when the application is deployed within eclipse on my windows machine and I can see all my logs on /logfile endpoint.

when I deploy the same application on a Linux box , the logfile is created and updated as expected however when I try to access the logfile over the browser,I get a 404 on the same endpoint(/logfile).

Any help will be much appreciated

My logback configuration is as below

logback-spring.xml

    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <include resource="org/springframework/boot/logging/logback/base.xml"/>
    <logger name="org.springframework.web" level="DEBUG" additivity="false">
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="FILE"/>
    </logger>
    <logger name="api.controllers" level="DEBUG" additivity="false">
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="FILE"/>
    </logger>
 </configuration>

My application.properties file looks like

    server.contextPath=/api
#Configure Access Logs
server.tomcat.basedir=log
server.tomcat.accesslog.enabled=true
server.tomcat.accesslog.pattern=%t %a "%r" %s (%D ms)

#configure other log
logging.file=log/vixenapi.log
logging.level.org.springframework.web=DEBUG
logging.level.org.springframework.boot.autoconfigure.logging=DEBUG
logging.level.org.hibernate=ERROR
#-------------------------------------

spring.jackson.serialization-inclusion=non_empty

#By Default include all properties. If a view property hasn't been set then it will be shown
spring.jackson.mapper.DEFAULT_VIEW_INCLUSION=true

The Log file records the exceptions as

 : Before request [uri=/com.causeway.vixen.api/logfile;client=172.16.4.56]
2017-11-16 18:29:13.662 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : DispatcherServlet with name 'dispatcherServlet' processing GET request for [/com.causeway.vixen.api/logfile]
2017-11-16 18:29:13.662 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : Last-Modified value for [/com.causeway.vixen.api/logfile] is: -1
2017-11-16 18:29:13.663  WARN 1552 --- [http-nio-8080-exec-10] o.s.w.s.r.ResourceHttpRequestHandler     : Locations list is empty. No resources will be served unless a custom ResourceResolver is configured as an alternative to PathResourceResolver.
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : Null ModelAndView returned to DispatcherServlet with name 'dispatcherServlet': assuming HandlerAdapter completed request handling
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : Successfully completed request
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.w.f.CommonsRequestLoggingFilter      : After request [uri=/com.causeway.vixen.api/logfile;client=172.16.4.56]
2017-11-16 18:29:13.663 DEBUG 1552 --- [http-nio-8080-exec-10] o.s.web.servlet.DispatcherServlet        : DispatcherServlet with name 'dispatcherServlet' processing GET request for [/com.causeway.vixen.api/error]
2017-11-16 18:29:13.664 DEBUG 1552 --- [http-nio-8080-exec-10] s.w.s.m.m.a.RequestMappingHandlerMapping : Looking up handler method for path /error

Here is my minimal example
https://github.com/spring-projects/spring-boot/files/1482233/test_api.zip

Body:
There's a trailing space in the value of the logging.file property in both your minimal example and the configuration in your question. The value in the question is "log/vixenapi.log " and it should be "log/vixenapi.log".

You can confirm the problem by enabling debug logging for org.springframework.boot.actuate.endpoint.mvc. LogFileMvcEndpoint will log a message when the log file doesn't exist that includes the path that was checked:

2017-11-17 14:40:24.801 DEBUG 30140 --- [nio-8080-exec-1] o.s.b.a.endpoint.mvc.LogFileMvcEndpoint  : Log file 'file [/Users/awilkinson/Downloads/test_api/log/test.log ]' does not exist

Note the trailing space at the end of the path.


问:
Ansible mysqldb add drop database line
Is it possible to add the add-drop database statement in the dump generated by mysql_db ?

My module works fine and the dump is well generated, but i want to add the "-B --add-drop-database" line (like in the mysqldump command)
to get at the beginning of the dump:

```<code>DROP DATABASE IF EXISTS `my_db`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `my_db` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `my_db`;
</code>```

答:
You can try to create custom my.cnf with

```<code>[mysqldump]
add-drop-database
</code>```

And pass my.cnf path as config_file parameter to mysql_db module.

Title: Ansible mysqldb add drop database line

Body:
Is it possible to add the add-drop database statement in the dump generated by mysql_db ?

My module works fine and the dump is well generated, but i want to add the "-B --add-drop-database" line (like in the mysqldump command)
to get at the beginning of the dump:

DROP DATABASE IF EXISTS `my_db`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `my_db` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `my_db`;

Body:
You can try to create custom my.cnf with

[mysqldump]
add-drop-database

And pass my.cnf path as config_file parameter to mysql_db module.


问:
PyQt5 render with QWebEngineView
I want render the webpage to an image offscreen, however when I use following code, the output image is blank, I can't solve the problem</p>

```<code>class PageShotter(QWidget):
    def __init__(self,url,parent=None):
        QWidget.__init__(self,parent)
        self.url = url
    def shot(self):
        self.webview = QtWebEngineWidgets.QWebEngineView(self)
        self.webview.load(QtCore.QUrl(self.url))
        webpage = self.webview.page()
        self.webview.setVisible(True)
        self.webview.loadFinished.connect(self.save)
    def save(self,finished):
        if finished:
            size = self.webview.contentsRect()
            print(u"width：%d，hight：%d" % (size.width(), size.height()))
            img = QtGui.QImage(size.width(), size.height(), QtGui.QImage.Format_ARGB32)
            painter = QtGui.QPainter(img)
            self.webview.render(painter,QtCore.QPoint())
            painter.end()
            filename = 'page.png'
            if img.save(filename):
                filepath = os.path.join(os.path.dirname(__file__), filename)
                print(u"success：%s" % filepath)
            else:
                print(u"fail")
        else:
            print("Error")

if __name__ == '__main__':
    import sys
    app = QApplication(sys.argv)
    shotter = PageShotter(web_site.base_url)
    shotter.shot()
    app.exec()
</code>```

答:
You must enable the ScreenCaptureEnabled attribute, for this you must first enable the PluginsEnabled attribute. In addition, the content is rendered after using show, but as you do not want to show the widget, we enable the WA_DontShowOnScreen attribute.

```<code>import os
from PyQt5 import QtCore, QtGui, QtWidgets, QtWebEngineWidgets

class PageShotter(QtWebEngineWidgets.QWebEngineView):
    def __init__(self, url, parent=None):
        super(PageShotter, self).__init__(parent)
        self.loadFinished.connect(self.save)
        self.url = url
        self.setAttribute(QtCore.Qt.WA_DontShowOnScreen, True)
        self.setAttribute(QtCore.Qt.WA_DeleteOnClose, True)
        self.show()
        settings = QtWebEngineWidgets.QWebEngineSettings.globalSettings()
        for attr in (QtWebEngineWidgets.QWebEngineSettings.PluginsEnabled,
                     QtWebEngineWidgets.QWebEngineSettings.ScreenCaptureEnabled,):
            settings.setAttribute(attr, True)

    def shot(self):
        if self.size().isNull():
            self.resize(640, 480)
        self.load(QtCore.QUrl.fromLocalFile(self.url))

    @QtCore.pyqtSlot(bool)
    def save(self, finished):
        if finished:
            size = self.contentsRect()
            print(u"width：%d，hight：%d" % (size.width(), size.height()))
            img = QtGui.QImage(size.width(), size.height(), QtGui.QImage.Format_ARGB32)
            painter = QtGui.QPainter(img)
            self.render(painter)
            painter.end()
            filename = 'page.png'
            if img.save(filename):
                filepath = os.path.join(os.path.dirname(__file__), filename)
                print(u"success：%s" % filepath)
            else:
                print(u"fail")
        else:
            print("Error")
        self.close()

if __name__ == '__main__':
    import sys

    app = QtWidgets.QApplication(sys.argv)
    shotter = PageShotter(r'C:\Output.html')
    shotter.shot()
    app.exec()
</code>```

Title: PyQt5 render with QWebEngineView

Body:
I want render the webpage to an image offscreen, however when I use following code, the output image is blank, I can't solve the problem

class PageShotter(QWidget):
    def __init__(self,url,parent=None):
        QWidget.__init__(self,parent)
        self.url = url
    def shot(self):
        self.webview = QtWebEngineWidgets.QWebEngineView(self)
        self.webview.load(QtCore.QUrl(self.url))
        webpage = self.webview.page()
        self.webview.setVisible(True)
        self.webview.loadFinished.connect(self.save)
    def save(self,finished):
        if finished:
            size = self.webview.contentsRect()
            print(u"width：%d，hight：%d" % (size.width(), size.height()))
            img = QtGui.QImage(size.width(), size.height(), QtGui.QImage.Format_ARGB32)
            painter = QtGui.QPainter(img)
            self.webview.render(painter,QtCore.QPoint())
            painter.end()
            filename = 'page.png'
            if img.save(filename):
                filepath = os.path.join(os.path.dirname(__file__), filename)
                print(u"success：%s" % filepath)
            else:
                print(u"fail")
        else:
            print("Error")

if __name__ == '__main__':
    import sys
    app = QApplication(sys.argv)
    shotter = PageShotter(web_site.base_url)
    shotter.shot()
    app.exec()

Body:
You must enable the ScreenCaptureEnabled attribute, for this you must first enable the PluginsEnabled attribute. In addition, the content is rendered after using show, but as you do not want to show the widget, we enable the WA_DontShowOnScreen attribute.

import os
from PyQt5 import QtCore, QtGui, QtWidgets, QtWebEngineWidgets

class PageShotter(QtWebEngineWidgets.QWebEngineView):
    def __init__(self, url, parent=None):
        super(PageShotter, self).__init__(parent)
        self.loadFinished.connect(self.save)
        self.url = url
        self.setAttribute(QtCore.Qt.WA_DontShowOnScreen, True)
        self.setAttribute(QtCore.Qt.WA_DeleteOnClose, True)
        self.show()
        settings = QtWebEngineWidgets.QWebEngineSettings.globalSettings()
        for attr in (QtWebEngineWidgets.QWebEngineSettings.PluginsEnabled,
                     QtWebEngineWidgets.QWebEngineSettings.ScreenCaptureEnabled,):
            settings.setAttribute(attr, True)

    def shot(self):
        if self.size().isNull():
            self.resize(640, 480)
        self.load(QtCore.QUrl.fromLocalFile(self.url))

    @QtCore.pyqtSlot(bool)
    def save(self, finished):
        if finished:
            size = self.contentsRect()
            print(u"width：%d，hight：%d" % (size.width(), size.height()))
            img = QtGui.QImage(size.width(), size.height(), QtGui.QImage.Format_ARGB32)
            painter = QtGui.QPainter(img)
            self.render(painter)
            painter.end()
            filename = 'page.png'
            if img.save(filename):
                filepath = os.path.join(os.path.dirname(__file__), filename)
                print(u"success：%s" % filepath)
            else:
                print(u"fail")
        else:
            print("Error")
        self.close()

if __name__ == '__main__':
    import sys

    app = QtWidgets.QApplication(sys.argv)
    shotter = PageShotter(r'C:\Output.html')
    shotter.shot()
    app.exec()

ContentLicense: CC BY-SA 4.0


问:
Syntax error in ODBC for a correct query
I have to insert the contents of a file into a database (it's a requirement to do it using odbc and not using a COPY instruction)

It was all working well until I tried to extract the part where I send the query to a new function. The weird part is I'm getting a syntax error while the queries run without any problem if I execute them by hand in psql.

The new function is this:

```<code>int insertRowWithData(const char *purchase_id, const char *username, const char *isbn, const char *date, char *query, SQLHSTMT *stmt) {
    SQLRETURN ret;

    SQLCHAR diag[DIAG_BUFFER_SIZE]; /** To store the error code */
    SQLRETURN diag_ret; /** To store the return code of the diagnostic function, just in case it misbehaves */
    SQLCHAR diag_text[DIAG_BUFFER_SIZE]; /** To store the text explanation of the error */

    /* INSERT INTO purchase VALUES ('purchase_id', (SELECT customer.customer_id from customer where customer.username='username'), 'isbn', 'date'); */
    sprintf(query, "insert into purchase VALUES ('%s', (SELECT customer.customer_id from customer where customer.username='%s'), '%s', '%s');", purchase_id, username, isbn, date);
    if (DEBUG) {
        printf(ANSI_COLOR_YELLOW "[DEBUG]" ANSI_COLOR_RESET " The query to be sent is: %s\n", query);
    }

    ret = SQLExecDirect(*stmt, (SQLCHAR*) query, sizeof(query));
    if (DEBUG && !SQL_SUCCEEDED(ret)) {
        diag_ret = SQLGetDiagRec(SQL_HANDLE_STMT, *stmt, 1, diag, NULL, diag_text, DIAG_BUFFER_SIZE, NULL);
        printf(ANSI_COLOR_RED "[ERROR]" ANSI_COLOR_RESET " %s (error code: %s)\n", diag_text, diag);
    }
    if (SQL_SUCCEEDED(ret)) {
        printf(ANSI_COLOR_GREEN "[SUCCESS]" ANSI_COLOR_RESET " Row with purchase_id=%s, username=%s, isbn=%s and date=%s was successfully inserted in the purchase table\n", purchase_id, username, isbn, date);
        return EXIT_SUCCESS;
    } else {
        printf(ANSI_COLOR_RED "[ERROR]" ANSI_COLOR_RESET " Row with purchase_id=%s, username=%s, isbn=%s and date=%s could not be inserted in the purchase table\n", purchase_id, username, isbn, date);
        return EXIT_FAILURE;
    }
}
</code>```

And the output I'm getting when running the program is this:

```<code>[DEBUG] The query to be sent is: insert into purchase VALUES ('421', (SELECT customer.customer_id from customer where customer.username='765150'), '8441436169', '2014-02-09');
[ERROR] ERROR: syntax error at or near "i";
Error while executing the query (error code: 42601)
[ERROR] Row with purchase_id=421, username=765150, isbn=8441436169 and date=2014-02-09 could not be inserted in the purchase table
[DEBUG] The query to be sent is: insert into purchase VALUES ('422', (SELECT customer.customer_id from customer where customer.username='1000591'), '9061123097', '2015-09-05');
[ERROR] ERROR: syntax error at or near "i";
Error while executing the query (error code: 42601)
[ERROR] Row with purchase_id=422, username=1000591, isbn=9061123097 and date=2015-09-05 could not be inserted in the purchase table
</code>```

The SQLHSTMT passed to the function is allocated in its caller function. The queries printed in the output work perfectly fine.

What's my mistake?

答:
The problem  is that you use sizeof() to determine the length of the query string, but the variable query is declared as char *, so this will give you 4 or 8, depending on the architecture.

Use strlen() instead.

Body:
I have to insert the contents of a file into a database (it's a requirement to do it using odbc and not using a COPY instruction)

It was all working well until I tried to extract the part where I send the query to a new function. The weird part is I'm getting a syntax error while the queries run without any problem if I execute them by hand in psql.

The new function is this:

int insertRowWithData(const char *purchase_id, const char *username, const char *isbn, const char *date, char *query, SQLHSTMT *stmt) {
    SQLRETURN ret;

    SQLCHAR diag[DIAG_BUFFER_SIZE]; /** To store the error code */
    SQLRETURN diag_ret; /** To store the return code of the diagnostic function, just in case it misbehaves */
    SQLCHAR diag_text[DIAG_BUFFER_SIZE]; /** To store the text explanation of the error */

    /* INSERT INTO purchase VALUES ('purchase_id', (SELECT customer.customer_id from customer where customer.username='username'), 'isbn', 'date'); */
    sprintf(query, "insert into purchase VALUES ('%s', (SELECT customer.customer_id from customer where customer.username='%s'), '%s', '%s');", purchase_id, username, isbn, date);
    if (DEBUG) {
        printf(ANSI_COLOR_YELLOW "[DEBUG]" ANSI_COLOR_RESET " The query to be sent is: %s\n", query);
    }

    ret = SQLExecDirect(*stmt, (SQLCHAR*) query, sizeof(query));
    if (DEBUG && !SQL_SUCCEEDED(ret)) {
        diag_ret = SQLGetDiagRec(SQL_HANDLE_STMT, *stmt, 1, diag, NULL, diag_text, DIAG_BUFFER_SIZE, NULL);
        printf(ANSI_COLOR_RED "[ERROR]" ANSI_COLOR_RESET " %s (error code: %s)\n", diag_text, diag);
    }
    if (SQL_SUCCEEDED(ret)) {
        printf(ANSI_COLOR_GREEN "[SUCCESS]" ANSI_COLOR_RESET " Row with purchase_id=%s, username=%s, isbn=%s and date=%s was successfully inserted in the purchase table\n", purchase_id, username, isbn, date);
        return EXIT_SUCCESS;
    } else {
        printf(ANSI_COLOR_RED "[ERROR]" ANSI_COLOR_RESET " Row with purchase_id=%s, username=%s, isbn=%s and date=%s could not be inserted in the purchase table\n", purchase_id, username, isbn, date);
        return EXIT_FAILURE;
    }
}

And the output I'm getting when running the program is this:

[DEBUG] The query to be sent is: insert into purchase VALUES ('421', (SELECT customer.customer_id from customer where customer.username='765150'), '8441436169', '2014-02-09');
[ERROR] ERROR: syntax error at or near "i";
Error while executing the query (error code: 42601)
[ERROR] Row with purchase_id=421, username=765150, isbn=8441436169 and date=2014-02-09 could not be inserted in the purchase table
[DEBUG] The query to be sent is: insert into purchase VALUES ('422', (SELECT customer.customer_id from customer where customer.username='1000591'), '9061123097', '2015-09-05');
[ERROR] ERROR: syntax error at or near "i";
Error while executing the query (error code: 42601)
[ERROR] Row with purchase_id=422, username=1000591, isbn=9061123097 and date=2015-09-05 could not be inserted in the purchase table

The SQLHSTMT passed to the function is allocated in its caller function. The queries printed in the output work perfectly fine.

What's my mistake?


问:
Custom date format in elastic-search
I am trying to format a date field in elastic-search using the joda-time library formats. I want to achieve the format "Mon Sep 18 17:12:37 IST 2017".First I used the following code:

```<code>"alarm_timestamp": {
    "type": "date",
    "format": "EEE MMM d H:m:s zzz YYYY"
}
</code>```

It did not work. Later, I read the question asked here. So, I changed the code:

```<code>"alarm_timestamp": {
    "type": "date",
    "format": "E MMM d H:m:s z Y"
}
</code>```

In both cases, mapping was successfully created. But, It gave error when I tried to index data.

The error it gives is:

```<code>{
    "error": {
        "root_cause": [
            {
              "type": "mapper_parsing_exception",
              "reason": "failed to parse [alarm_timestamp]"
            }
        ],
        "type": "mapper_parsing_exception",
        "reason": "failed to parse [alarm_timestamp]",
        "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Invalid format: \"Mon Sep 18 17:12:37 IST 2017\" is malformed at \"IST 2017\""
        }
    },
    "status": 400
}
</code>```

Any suggestion(s)?

答:
From the Elasticsearch documentation for format:

Completely customizable date formats are supported. The syntax for
  these is explained in the Joda docs.

The Joda docs says about z:

Zone names: Time zone names ('z') cannot be parsed.

The problem is that these abbreviations like IST for timezones can be ambiguous.

The Wikipedia Time Zone page says the following:

Abbreviations

  Time zones
  are often represented by alphabetic abbreviations such as "EST",
  "WST", and "CST", but these are not part of the international time and
  date standard ISO 8601 and their use as sole designator for a time
  zone is discouraged. Such designations can be ambiguous; for example,
  "ECT" could be interpreted as "Eastern Caribbean Time" (UTC−4h),
  "Ecuador Time" (UTC−5h), or "European Central Time" (UTC+1h).

So you will see that some abbreviations will work and some will not. It is encouraged not to use these abbreviations. Instead use one of the following:

E MMM d H:m:s Z Y and index as Mon Sep 18 17:12:37 +02:00 2017

E MMM d H:m:s ZZZ Y and index as Mon Sep 18 17:12:37 Europe/Istanbul 2017

Here is a list of Available Time Zones for Joda.

Title: Custom date format in elastic-search

Body:
I am trying to format a date field in elastic-search using the joda-time library formats. I want to achieve the format "Mon Sep 18 17:12:37 IST 2017".First I used the following code:

"alarm_timestamp": {
    "type": "date",
    "format": "EEE MMM d H:m:s zzz YYYY"
}

It did not work. Later, I read the question asked here. So, I changed the code:

"alarm_timestamp": {
    "type": "date",
    "format": "E MMM d H:m:s z Y"
}

In both cases, mapping was successfully created. But, It gave error when I tried to index data.

The error it gives is:

{
    "error": {
        "root_cause": [
            {
              "type": "mapper_parsing_exception",
              "reason": "failed to parse [alarm_timestamp]"
            }
        ],
        "type": "mapper_parsing_exception",
        "reason": "failed to parse [alarm_timestamp]",
        "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Invalid format: \"Mon Sep 18 17:12:37 IST 2017\" is malformed at \"IST 2017\""
        }
    },
    "status": 400
}

Any suggestion(s)?


问:
Service Principal : Set-AzureRmKeyVaultAccessPolicy : Insufficient privileges to complete the operation
Post updated. Issue has been solved. The scripts below will create a resource group, create a service principal, deploy a key vault, configure permissions and write a secret to the vault. Hopes this help! :)

Problem:
I am logged into PowerShell as a Service Principal that has Owner permissions on a resource group.
I get permission errors when i try to create a vault, set permission on the vault and when i try to write secrets.

Solution:
Step 1: Create resource group and Service Principal. You must be logged in as an administrator to execute this script

```<code>Clear-Host
Import-Module Azure
Import-Module AzureRM.Resources

Add-AzureRmAccount
Get-AzureRmSubscription
Set-AzureRmContext -SubscriptionId <Your subscription id goes here>

$ServicePrincipalDisplayName = "myServicePrincipalName"
$CertificateName = "CN=SomeCertName"

$cert = New-SelfSignedCertificate -CertStoreLocation "cert:\CurrentUser\My" -Subject $CertificateName -KeySpec KeyExchange
$keyValue = [Convert]::ToBase64String($cert.GetRawCertData())

$ResouceGroupName = "myRessourceGroup"
$location = "North Central US"

# Create the resource group
New-AzureRmResourceGroup -Name $ResouceGroupName -Location $location

$ResouceGroupNameScope = (Get-AzureRmResourceGroup -Name $ResouceGroupName -ErrorAction Stop).ResourceId

# Create the Service Principal that logs in with a certificate
New-AzureRMADServicePrincipal -DisplayName $ServicePrincipalDisplayName -CertValue $keyValue -EndDate $cert.NotAfter -StartDate $cert.NotBefore

$myServicePrincipal = Get-AzureRmADServicePrincipal -SearchString $ServicePrincipalDisplayName
Write-Host "myServicePrincipal.ApplicationId " $myServicePrincipal.ApplicationId -ForegroundColor Green
Write-Host "myServicePrincipal.DisplayName " $myServicePrincipal.DisplayName

# Sleep here for a few seconds to allow the service principal application to become active (should only take a couple of seconds normally)
Write-Host "Waiting 10 seconds"
Start-Sleep -s 10

Write-Host "Make the Service Principal owner of the resource group " $ResouceGroupName

$NewRole = $null
$Retries = 0
 While ($NewRole -eq $null -and $Retries -le 6)
 {
    New-AzureRMRoleAssignment -RoleDefinitionName Owner -ServicePrincipalName $myServicePrincipal.ApplicationId  -Scope $ResouceGroupNameScope -ErrorAction SilentlyContinue
    $NewRole = Get-AzureRMRoleAssignment -ServicePrincipalName $myServicePrincipal.ApplicationId
    Write-Host "NewRole.DisplayName " $NewRole.DisplayName
    Write-Host "NewRole.Scope: " $NewRole.Scope
    $Retries++

    Start-Sleep -s 10
 }

Write-Host "Service principal created" -ForegroundColor Green
</code>```

Step 2 : ARM deployment of the vault. Create a filenamed keyvault2.parameters.json Update the id's to reflect your installation (5479eaf6-31a3-4be3-9fb6-c2cdadc31735 is the service principal used by azure web apps when accessing the vault.)

```<code>{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
  "parameters": {
    "vaultName": {
      "value": "valueFromParameterFile"
    },
    "vaultlocation": {
      "value": "valueFromParameterFile"
    },
    "skumode": {
      "value": "Standard"
    },
    "accessPolicyList": {
      "value": [
        {
          "objectId": "The object ID for your AAD user goes here so that you can read secrets etc",
          "tenantId": "Your Tenant Id goes here",
          "permissions": {
            "keys": [
              "Get",
              "List"
            ],
            "secrets": [
              "Get",
              "List"
            ],
            "certificates": [
              "Get",
              "List"
            ]
          }
        },
        {
          "objectId": "The object ID for the service principal goes here Get-AzureRmADServicePrincipal -ServicePrincipalName <Service Principal Application ID>",
          "tenantId": "Your Tenant Id goes here",
          "permissions": {
            "keys": [
              "Get",
              "List",
              "Update",
              "Create",
              "Import",
              "Delete",
              "Recover",
              "Backup",
              "Restore"
            ],
            "secrets": [
              "Get",
              "List",
              "Set",
              "Delete",
              "Recover",
              "Backup",
              "Restore"
            ],
            "certificates": [
              "Get",
              "List",
              "Update",
              "Create",
              "Import",
              "Delete",
              "ManageContacts",
              "ManageIssuers",
              "GetIssuers",
              "ListIssuers",
              "SetIssuers",
              "DeleteIssuers"
            ]
          },
          "applicationId": null
        },
        {
        "objectId": "5479eaf6-31a3-4be3-9fb6-c2cdadc31735",
        "tenantId": "Your Tenant Id goes here",
        "permissions": {
            "keys": [],
            "secrets": [
                "Get"
            ],
            "certificates": []
        },
        "applicationId": null
    }
      ]
    },
    "tenant": {
      "value": "Your Tenant Id goes here"
    },
    "isenabledForDeployment": {
      "value": true
    },
    "isenabledForTemplateDeployment": {
      "value": false
    },
    "isenabledForDiskEncryption": {
      "value": false
    }
  }
}
</code>```

Step 3 : ARM deployment of the vault. Create a filenamed keyvault2.template.json

```<code>{
    "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
  "parameters": {
    "vaultName": {
      "type": "string"
    },
    "vaultlocation": {
      "type": "string"
    },
    "skumode": {
      "type": "string",
      "defaultValue": "Standard",
      "allowedValues": [
        "Standard",
        "standard",
        "Premium",
        "premium"
      ],
      "metadata": {
        "description": "SKU for the vault"
      }
    },
    "accessPolicyList": {
      "type": "array",
      "defaultValue": [],
      "metadata": {
        "description": "The access policies defined for this vault."
      }
    },
    "tenant": {
      "type": "string"
    },
    "isenabledForDeployment": {
      "type": "bool"
    },
    "isenabledForTemplateDeployment": {
      "type": "bool"
    },
    "isenabledForDiskEncryption": {
      "type": "bool"
    }
  },
    "resources": [
      {
        "apiVersion": "2015-06-01",
        "name": "[parameters('vaultName')]",
        "location": "[parameters('vaultlocation')]",
        "type": "Microsoft.KeyVault/vaults",
        "properties": {
          "enabledForDeployment": "[parameters('isenabledForDeployment')]",
          "enabledForTemplateDeployment": "[parameters('isenabledForTemplateDeployment')]",
          "enabledForDiskEncryption": "[parameters('isenabledForDiskEncryption')]",
          "accessPolicies": "[parameters('accessPolicyList')]",
          "tenantId": "[parameters('tenant')]",
          "sku": {
            "name": "[parameters('skumode')]",
            "family": "A"
          }
        }
      }
    ]
}
</code>```

Step 4 : Deploy vault. Start a new powershell window and execute this script. Update 3 x id's

```<code>Clear-Host

Import-Module Azure
Import-Module AzureRM.Resources

$ServicePrincipalApplicationId = "xxx"
$TenantId = "yyy"
$SubscriptionId = "zzz"
$CertificateName = "CN=SomeCertName"
$ResouceGroupName = "myRessourceGroup"
$location = "North Central US"
$VaultName = "MyVault" + (Get-Random -minimum 10000000 -maximum 1000000000)
$MySecret = ConvertTo-SecureString -String "MyValue" -AsPlainText -Force

$Cert = Get-ChildItem cert:\CurrentUser\My\ | Where-Object {$_.Subject -match $CertificateName }
Write-Host "cert.Thumbprint " $cert.Thumbprint
Write-Host "cert.Subject " $cert.Subject

Add-AzureRmAccount -ServicePrincipal -CertificateThumbprint $cert.Thumbprint -ApplicationId $ServicePrincipalApplicationId -TenantId $TenantId
Get-AzureRmSubscription
Set-AzureRmContext -SubscriptionId $SubscriptionId

Write-Host ""
Write-Host "Creating vault" -ForegroundColor Yellow

New-AzureRmResourceGroupDeployment -ResourceGroupName $ResouceGroupName -vaultName $vaultName -vaultlocation $location -isenabledForDeployment $true -TemplateFile ".\keyvault2.template.json"  -TemplateParameterFile ".\keyvault2.parameters.json"

Write-Host ""
Write-Host "Key Vault " $vaultName " deployed" -ForegroundColor green

Write-Host "Wait 5 seconds"
Start-Sleep -Seconds 5

Write-Host "Write Secret" -ForegroundColor Yellow
Set-AzureKeyVaultSecret -VaultName $VaultName -Name "MyKey" -SecretValue $MySecret

Write-Host "Wait 10 seconds"
Start-Sleep -Seconds 10

Write-Host "Read secret"
Get-AzureKeyVaultSecret -VaultName $VaultName -Name "MyKey"
</code>```

答:
Set-AzureRmKeyVaultAccessPolicy -VaultName $name -ObjectId $oId -PermissionsToSecrets get

returns error

Set-AzureRmKeyVaultAccessPolicy : Insufficient privileges to complete the operation.

Solution is to add additional parameter -BypassObjectIdValidation

Set-AzureRmKeyVaultAccessPolicy -BypassObjectIdValidation -VaultName $name -ObjectId $oId -PermissionsToSecrets get

Solution looks like a hack, but it works for me. After this, object with $oId have got access to keyVault. (For checks access polices use Get-AzureRmKeyVault -VaultName $vaultName)

Body:
Post updated. Issue has been solved. The scripts below will create a resource group, create a service principal, deploy a key vault, configure permissions and write a secret to the vault. Hopes this help! :)

Problem:
I am logged into PowerShell as a Service Principal that has Owner permissions on a resource group.
I get permission errors when i try to create a vault, set permission on the vault and when i try to write secrets.

Solution:
Step 1: Create resource group and Service Principal. You must be logged in as an administrator to execute this script

Clear-Host
Import-Module Azure
Import-Module AzureRM.Resources

Add-AzureRmAccount
Get-AzureRmSubscription
Set-AzureRmContext -SubscriptionId <Your subscription id goes here>

$ServicePrincipalDisplayName = "myServicePrincipalName"
$CertificateName = "CN=SomeCertName"

$cert = New-SelfSignedCertificate -CertStoreLocation "cert:\CurrentUser\My" -Subject $CertificateName -KeySpec KeyExchange
$keyValue = [Convert]::ToBase64String($cert.GetRawCertData())

$ResouceGroupName = "myRessourceGroup"
$location = "North Central US"

# Create the resource group
New-AzureRmResourceGroup -Name $ResouceGroupName -Location $location

$ResouceGroupNameScope = (Get-AzureRmResourceGroup -Name $ResouceGroupName -ErrorAction Stop).ResourceId

# Create the Service Principal that logs in with a certificate
New-AzureRMADServicePrincipal -DisplayName $ServicePrincipalDisplayName -CertValue $keyValue -EndDate $cert.NotAfter -StartDate $cert.NotBefore

$myServicePrincipal = Get-AzureRmADServicePrincipal -SearchString $ServicePrincipalDisplayName
Write-Host "myServicePrincipal.ApplicationId " $myServicePrincipal.ApplicationId -ForegroundColor Green
Write-Host "myServicePrincipal.DisplayName " $myServicePrincipal.DisplayName

# Sleep here for a few seconds to allow the service principal application to become active (should only take a couple of seconds normally)
Write-Host "Waiting 10 seconds"
Start-Sleep -s 10

Write-Host "Make the Service Principal owner of the resource group " $ResouceGroupName

$NewRole = $null
$Retries = 0
 While ($NewRole -eq $null -and $Retries -le 6)
 {
    New-AzureRMRoleAssignment -RoleDefinitionName Owner -ServicePrincipalName $myServicePrincipal.ApplicationId  -Scope $ResouceGroupNameScope -ErrorAction SilentlyContinue
    $NewRole = Get-AzureRMRoleAssignment -ServicePrincipalName $myServicePrincipal.ApplicationId
    Write-Host "NewRole.DisplayName " $NewRole.DisplayName
    Write-Host "NewRole.Scope: " $NewRole.Scope
    $Retries++

    Start-Sleep -s 10
 }

Write-Host "Service principal created" -ForegroundColor Green

Step 2 : ARM deployment of the vault. Create a filenamed keyvault2.parameters.json Update the id's to reflect your installation (5479eaf6-31a3-4be3-9fb6-c2cdadc31735 is the service principal used by azure web apps when accessing the vault.)

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
  "parameters": {
    "vaultName": {
      "value": "valueFromParameterFile"
    },
    "vaultlocation": {
      "value": "valueFromParameterFile"
    },
    "skumode": {
      "value": "Standard"
    },
    "accessPolicyList": {
      "value": [
        {
          "objectId": "The object ID for your AAD user goes here so that you can read secrets etc",
          "tenantId": "Your Tenant Id goes here",
          "permissions": {
            "keys": [
              "Get",
              "List"
            ],
            "secrets": [
              "Get",
              "List"
            ],
            "certificates": [
              "Get",
              "List"
            ]
          }
        },
        {
          "objectId": "The object ID for the service principal goes here Get-AzureRmADServicePrincipal -ServicePrincipalName <Service Principal Application ID>",
          "tenantId": "Your Tenant Id goes here",
          "permissions": {
            "keys": [
              "Get",
              "List",
              "Update",
              "Create",
              "Import",
              "Delete",
              "Recover",
              "Backup",
              "Restore"
            ],
            "secrets": [
              "Get",
              "List",
              "Set",
              "Delete",
              "Recover",
              "Backup",
              "Restore"
            ],
            "certificates": [
              "Get",
              "List",
              "Update",
              "Create",
              "Import",
              "Delete",
              "ManageContacts",
              "ManageIssuers",
              "GetIssuers",
              "ListIssuers",
              "SetIssuers",
              "DeleteIssuers"
            ]
          },
          "applicationId": null
        },
        {
        "objectId": "5479eaf6-31a3-4be3-9fb6-c2cdadc31735",
        "tenantId": "Your Tenant Id goes here",
        "permissions": {
            "keys": [],
            "secrets": [
                "Get"
            ],
            "certificates": []
        },
        "applicationId": null
    }
      ]
    },
    "tenant": {
      "value": "Your Tenant Id goes here"
    },
    "isenabledForDeployment": {
      "value": true
    },
    "isenabledForTemplateDeployment": {
      "value": false
    },
    "isenabledForDiskEncryption": {
      "value": false
    }
  }
}

Step 3 : ARM deployment of the vault. Create a filenamed keyvault2.template.json

{
    "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
  "parameters": {
    "vaultName": {
      "type": "string"
    },
    "vaultlocation": {
      "type": "string"
    },
    "skumode": {
      "type": "string",
      "defaultValue": "Standard",
      "allowedValues": [
        "Standard",
        "standard",
        "Premium",
        "premium"
      ],
      "metadata": {
        "description": "SKU for the vault"
      }
    },
    "accessPolicyList": {
      "type": "array",
      "defaultValue": [],
      "metadata": {
        "description": "The access policies defined for this vault."
      }
    },
    "tenant": {
      "type": "string"
    },
    "isenabledForDeployment": {
      "type": "bool"
    },
    "isenabledForTemplateDeployment": {
      "type": "bool"
    },
    "isenabledForDiskEncryption": {
      "type": "bool"
    }
  },
    "resources": [
      {
        "apiVersion": "2015-06-01",
        "name": "[parameters('vaultName')]",
        "location": "[parameters('vaultlocation')]",
        "type": "Microsoft.KeyVault/vaults",
        "properties": {
          "enabledForDeployment": "[parameters('isenabledForDeployment')]",
          "enabledForTemplateDeployment": "[parameters('isenabledForTemplateDeployment')]",
          "enabledForDiskEncryption": "[parameters('isenabledForDiskEncryption')]",
          "accessPolicies": "[parameters('accessPolicyList')]",
          "tenantId": "[parameters('tenant')]",
          "sku": {
            "name": "[parameters('skumode')]",
            "family": "A"
          }
        }
      }
    ]
}

Step 4 : Deploy vault. Start a new powershell window and execute this script. Update 3 x id's

Clear-Host

Import-Module Azure
Import-Module AzureRM.Resources

$ServicePrincipalApplicationId = "xxx"
$TenantId = "yyy"
$SubscriptionId = "zzz"
$CertificateName = "CN=SomeCertName"
$ResouceGroupName = "myRessourceGroup"
$location = "North Central US"
$VaultName = "MyVault" + (Get-Random -minimum 10000000 -maximum 1000000000)
$MySecret = ConvertTo-SecureString -String "MyValue" -AsPlainText -Force

$Cert = Get-ChildItem cert:\CurrentUser\My\ | Where-Object {$_.Subject -match $CertificateName }
Write-Host "cert.Thumbprint " $cert.Thumbprint
Write-Host "cert.Subject " $cert.Subject

Add-AzureRmAccount -ServicePrincipal -CertificateThumbprint $cert.Thumbprint -ApplicationId $ServicePrincipalApplicationId -TenantId $TenantId
Get-AzureRmSubscription
Set-AzureRmContext -SubscriptionId $SubscriptionId

Write-Host ""
Write-Host "Creating vault" -ForegroundColor Yellow

New-AzureRmResourceGroupDeployment -ResourceGroupName $ResouceGroupName -vaultName $vaultName -vaultlocation $location -isenabledForDeployment $true -TemplateFile ".\keyvault2.template.json"  -TemplateParameterFile ".\keyvault2.parameters.json"

Write-Host ""
Write-Host "Key Vault " $vaultName " deployed" -ForegroundColor green

Write-Host "Wait 5 seconds"
Start-Sleep -Seconds 5

Write-Host "Write Secret" -ForegroundColor Yellow
Set-AzureKeyVaultSecret -VaultName $VaultName -Name "MyKey" -SecretValue $MySecret

Write-Host "Wait 10 seconds"
Start-Sleep -Seconds 10

Write-Host "Read secret"
Get-AzureKeyVaultSecret -VaultName $VaultName -Name "MyKey"


问:
Chrome settings page opens as default in selenium test
I am a beginner in test automation, Trying to learn selenium.

I did some automation with selenium Chrome Webdriver in C#

i am facing a problem, on execution test, settings page is opening as default in chrome browser, and URL is opening in the 2nd tab.

Only required URL should open in chrome.......

i am using selenium chrome driver in c# on windows 10.

can anyone help? how to prevent this problem?

Thanks.

答:
Can you try this?</p>

```<code>                ChromeOptions options = new ChromeOptions();
                options.AddArguments("--disable-infobars");
                options.AddArguments("--start-maximized");
                options.AddUserProfilePreference("credentials_enable_service", false);
                driver= new ChromeDriver("path to chrome driver"), options, TimeSpan.FromMinutes(3));
                driver.Navigate().GoToUrl("http://www.google.com");
</code>```

Body:
Can you try this?

                ChromeOptions options = new ChromeOptions();
                options.AddArguments("--disable-infobars");
                options.AddArguments("--start-maximized");
                options.AddUserProfilePreference("credentials_enable_service", false);
                driver= new ChromeDriver("path to chrome driver"), options, TimeSpan.FromMinutes(3));
                driver.Navigate().GoToUrl("http://www.google.com");


问:
python moving multiple files from one folder to the other based on text characters in file name
I'm quite new to Python.  I've been exploring the shutil module and can move things in general.  My question revolves around:

Imagine a scenario in which you have hundreds of files in an export folder.  While all the files are distinct, 13 of each are for a specific vendor.  I would like to create a script that goes through the export folder, evaluates each file name, grabs all the Apple files and puts them in the Apple Folder, Intel files and puts them in the Intel Folder, etc.  Any wisdom would be greatly appreciated.

I was trying to have wildcards in the shutil copy, but did not have any luck.

Thanks,

JT

答:
Minor edit on Padaic's answer:</p>

```<code>import glob
import shutil

vendors = ("*Apple*.*", "*Intel*.*")
paths = (".\\Apple\\", ".\\Intel\\")
for idx in range(len(vendors)):
    for matches in glob.glob(vendors[idx]):
        shutil.move(matches, paths[idx])
</code>```

Body:
Minor edit on Padaic's answer:

import glob
import shutil

vendors = ("*Apple*.*", "*Intel*.*")
paths = (".\\Apple\\", ".\\Intel\\")
for idx in range(len(vendors)):
    for matches in glob.glob(vendors[idx]):
        shutil.move(matches, paths[idx])