问:
Excel VBA: Communicating via named pipe
I am trying to setup communication via a named pipe in VBA unfortunately for some reason it never gets to the line Debug.Print "Connected in the server, nor does the client connect. Seems like a simple scenario but been trying to get this going for hours.

Server

```<code>Public Sub Server()
      Const szPipeName = "\\.\pipe\bigtest"
      Dim hPipe As Long, readVal As Long, readBytes As Long, sendVal As Long, sentBytes As Long
      Dim sa As SECURITY_ATTRIBUTES

      'Create the NULL security token for the pipe
      pSD = GlobalAlloc(GPTR, SECURITY_DESCRIPTOR_MIN_LENGTH)
      res = InitializeSecurityDescriptor(pSD, SECURITY_DESCRIPTOR_REVISION)
      res = SetSecurityDescriptorDacl(pSD, -1, 0, 0)
      sa.nLength = LenB(sa)
      sa.lpSecurityDescriptor = pSD
      sa.bInheritHandle = True

      'Create the Named Pipe
      hPipe = CreateNamedPipe(szPipeName, PIPE_ACCESS_DUPLEX, PIPE_WAIT Or PIPE_TYPE_MESSAGE Or PIPE_READMODE_MESSAGE, 10, 1000, 1000, 10000, sa)

      'Create separate thread as client
      ID = CreateThread(nil, 0, AddressOf ClientThread, nil, 0, nil)

      Debug.Print "Created thread: " & ID
      Debug.Print "Connecting named pipe: " & hPipe

      res = ConnectNamedPipe(hPipe, ByVal 0)
      'XXXXXXXXXXXXXXXXX NEVER GETS HERE XXXXXXXXXXXXXXXXXXx
      Debug.Print "Connected"
      'Read/Write data over the pipe

      res = ReadFile(hPipe, readVal, LenB(readVal), readBytes, ByVal 0)
      Debug.Print "Read file: " & readVal

      'res = WriteFile(hPipe, sendVal , LenB(sendVal ), sendBytes, ByVal 0)
      res = FlushFileBuffers(hPipe)
      res = DisconnectNamedPipe(hPipe)

      'Close the pipe handle
      CloseHandle hPipe
      GlobalFree (pSD)

   End Sub
</code>```

Client

```<code>Public Sub ClientThread()
      Const szPipeName As String = "\\.\pipe\bigtest"
      Dim sentBytes As Long, sendVal As Long, fSuccess As Boolean, readVal As Long, readBytes As Long

      sendVal = 500

      'Give server time to ConnectNamedPipe
      Sleep 2000
      Debug.Print "Connecting to pipe..."
      fSuccess= CallNamedPipe(szPipeName, sendVal, LenB(sendVal), readVal, LenB(readVal), readBytes, 5000)
      'XXXXXXXXXXXXXX NEVER GETS HERE XXXXXXXXXXXXXX
      Debug.Print "Successful: " & fSuccess

      '...
   End Sub
</code>```

答:
(this is my first answer -- time to give back)

Let me show you an example of this working. In the following example, I'll give you the VBA macro module code for a a function Excel to send an message to a server, the server to read the message, the server to compose a response, and excel to receive the response. we'll do this using a message that is a string, but you can take this further and create a protocol you would like to use over this pipe.

Remember that Excel macros are single threaded and event driven -- and so the way it makes sense to use named pipes in excel is as a client -- to in response to an event, send a request to a server and receive a response from that server (much like a web GET or POST sends a request and gets a response then closes the connection)

In this example, we used the method CallNamedPipeA in kernel32
(note that this is written for 64 bit excel. If using 32 bit, you exclude the "PtrSafe") This method connects to a named pipe in Message Mode, sends a message, receives a message, and then closes the connection.

The c# server code is taken largely from a Microsoft example, but we must convert it to handle message mode and had it restart a new thread when each pipe is closed. Thus there are always 4 threads waiting for clients to connect.

Quick note on Strings. Remember Excel uses Unicode. We should use UTF8 or byte arrays over named pipes.

Call the sub testPipe in Excel to see this work.

Excel VBA (client)

```<code>Option Explicit

Declare PtrSafe Function CallNamedPipe Lib "kernel32" Alias _
    "CallNamedPipeA" ( _
    ByVal lpNamedPipeName As String, _
    lpInBuffer As Any, _
    ByVal nInBufferSize As Long, _
    lpOutBuffer As Any, _
    ByVal nOutBufferSize As Long, _
    lpBytesRead As Long, _
    ByVal nTimeOut As Long) As Long

Private Sub testPipe()
    Dim ms As String
    Dim mr As String
    Dim returncode As Long

    ms = "Message from client;Hello World"

    returncode = namedPipeMessageExchange("testpipe", ms, mr)

    If returncode <> 0 Then
        Debug.Print "Sent:     " & ms
        Debug.Print "received: " & mr
    End If

End Sub

Public Function namedPipeMessageExchange(pipe As String, messageToSend As String, messageReceived As String) As Long
    Dim res As Long, myStr As String, i As Long, cbRead As Long, sm As String
    Dim numBytes As Long, bArray() As Byte, temp As String

    Dim b() As Byte
    Dim blen As Long
    b = StrConv(messageToSend, vbFromUnicode)
    blen = UBound(b) - LBound(b) + 1
    If blen = 0 Then b = Array(1)

    numBytes = 1000000
    ReDim bArray(numBytes)  'Build the return buffer

    'Call CallNamedPipe to do the transaction all at once
    res = CallNamedPipe("\\.\pipe\" + pipe, b(0), blen, _
        bArray(0), numBytes, _
        cbRead, 3000)  'Wait up to 3 seconds for a response

    If res > 0 Then
        ReDim Preserve bArray(0 To cbRead - 1)
        messageReceived = StrConv(bArray, vbUnicode)
        'Debug.Print "received: " & messageReceived
    Else
        Debug.Print "Error number " & Err.LastDllError & _
            " attempting to call CallNamedPipe.", vbOKOnly
    End If

    namedPipeMessageExchange = res
End Function
</code>```

c# (server)

```<code>using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Pipes;
using System.Text;
using System.Threading;

public class PipeServer
{
    private static int numThreads = 4;

    public static void Main()
    {
        int i;
        Thread[] servers = new Thread[numThreads];

        Console.WriteLine("\n*** Named pipe server stream with impersonation example ***\n");
        Console.WriteLine("Waiting for client connect...\n");
        for (i = 0; i < numThreads; i++)
        {
            servers[i] = new Thread(ServerThread);
            servers[i].Start();
        }
        Thread.Sleep(250);
        while (i > 0)
        {
            for (int j = 0; j < numThreads; j++)
            {
                if (servers[j] != null)
                {
                    if (servers[j].Join(250))
                    {
                        Console.WriteLine("Server thread[{0}] finished.", servers[j].ManagedThreadId);
                        servers[j] = null;
                        servers[j] = new Thread(ServerThread);
                        servers[j].Start();
                        //i--;    // decrement the thread watch count
                    }
                }
            }
        }
        Console.WriteLine("\nServer threads exhausted, exiting.");
    }

    private static void ServerThread(object data)
    {
        NamedPipeServerStream pipeServer =
            new NamedPipeServerStream("testpipe", PipeDirection.InOut, numThreads, PipeTransmissionMode.Message);

        int threadId = Thread.CurrentThread.ManagedThreadId;

        // Wait for a client to connect
        pipeServer.WaitForConnection();

        Console.WriteLine("Client connected on thread[{0}].", threadId);
        try
        {

            List<byte> intext = new List<byte>();
            do
            {
                byte[] x = new byte[1024*16];
                int read = 0;
                read = pipeServer.Read(x);
                Array.Resize(ref x, read);
                intext.AddRange(x);

            } while (!pipeServer.IsMessageComplete);

            string receivedText = System.Text.Encoding.UTF8.GetString(intext.ToArray());

            string sentText = "I am the server!";
            pipeServer.Write(System.Text.Encoding.UTF8.GetBytes(sentText));

            Console.WriteLine("Received Text: "+receivedText);
            Console.WriteLine("Sent Text:     " + sentText);

        }
        // Catch the IOException that is raised if the pipe is broken
        // or disconnected.
        catch (IOException e)
        {
            Console.WriteLine("ERROR: {0}", e.Message);
        }
        //pipeServer.WaitForPipeDrain();
        pipeServer.Close();
    }
}
</code>```

Title: Excel VBA: Communicating via named pipe

Body:
I am trying to setup communication via a named pipe in VBA unfortunately for some reason it never gets to the line Debug.Print "Connected in the server, nor does the client connect. Seems like a simple scenario but been trying to get this going for hours.

Server

Public Sub Server()
      Const szPipeName = "\\.\pipe\bigtest"
      Dim hPipe As Long, readVal As Long, readBytes As Long, sendVal As Long, sentBytes As Long
      Dim sa As SECURITY_ATTRIBUTES

      'Create the NULL security token for the pipe
      pSD = GlobalAlloc(GPTR, SECURITY_DESCRIPTOR_MIN_LENGTH)
      res = InitializeSecurityDescriptor(pSD, SECURITY_DESCRIPTOR_REVISION)
      res = SetSecurityDescriptorDacl(pSD, -1, 0, 0)
      sa.nLength = LenB(sa)
      sa.lpSecurityDescriptor = pSD
      sa.bInheritHandle = True

      'Create the Named Pipe
      hPipe = CreateNamedPipe(szPipeName, PIPE_ACCESS_DUPLEX, PIPE_WAIT Or PIPE_TYPE_MESSAGE Or PIPE_READMODE_MESSAGE, 10, 1000, 1000, 10000, sa)

      'Create separate thread as client
      ID = CreateThread(nil, 0, AddressOf ClientThread, nil, 0, nil)

      Debug.Print "Created thread: " & ID
      Debug.Print "Connecting named pipe: " & hPipe

      res = ConnectNamedPipe(hPipe, ByVal 0)
      'XXXXXXXXXXXXXXXXX NEVER GETS HERE XXXXXXXXXXXXXXXXXXx
      Debug.Print "Connected"
      'Read/Write data over the pipe

      res = ReadFile(hPipe, readVal, LenB(readVal), readBytes, ByVal 0)
      Debug.Print "Read file: " & readVal

      'res = WriteFile(hPipe, sendVal , LenB(sendVal ), sendBytes, ByVal 0)
      res = FlushFileBuffers(hPipe)
      res = DisconnectNamedPipe(hPipe)

      'Close the pipe handle
      CloseHandle hPipe
      GlobalFree (pSD)

   End Sub

Client

Public Sub ClientThread()
      Const szPipeName As String = "\\.\pipe\bigtest"
      Dim sentBytes As Long, sendVal As Long, fSuccess As Boolean, readVal As Long, readBytes As Long

      sendVal = 500

      'Give server time to ConnectNamedPipe
      Sleep 2000
      Debug.Print "Connecting to pipe..."
      fSuccess= CallNamedPipe(szPipeName, sendVal, LenB(sendVal), readVal, LenB(readVal), readBytes, 5000)
      'XXXXXXXXXXXXXX NEVER GETS HERE XXXXXXXXXXXXXX
      Debug.Print "Successful: " & fSuccess

      '...
   End Sub

Body:
(this is my first answer -- time to give back)

Let me show you an example of this working. In the following example, I'll give you the VBA macro module code for a a function Excel to send an message to a server, the server to read the message, the server to compose a response, and excel to receive the response. we'll do this using a message that is a string, but you can take this further and create a protocol you would like to use over this pipe.

Remember that Excel macros are single threaded and event driven -- and so the way it makes sense to use named pipes in excel is as a client -- to in response to an event, send a request to a server and receive a response from that server (much like a web GET or POST sends a request and gets a response then closes the connection)

In this example, we used the method CallNamedPipeA in kernel32
(note that this is written for 64 bit excel. If using 32 bit, you exclude the "PtrSafe") This method connects to a named pipe in Message Mode, sends a message, receives a message, and then closes the connection.

The c# server code is taken largely from a Microsoft example, but we must convert it to handle message mode and had it restart a new thread when each pipe is closed. Thus there are always 4 threads waiting for clients to connect.

Quick note on Strings. Remember Excel uses Unicode. We should use UTF8 or byte arrays over named pipes.

Call the sub testPipe in Excel to see this work.

Excel VBA (client)

Option Explicit

Declare PtrSafe Function CallNamedPipe Lib "kernel32" Alias _
    "CallNamedPipeA" ( _
    ByVal lpNamedPipeName As String, _
    lpInBuffer As Any, _
    ByVal nInBufferSize As Long, _
    lpOutBuffer As Any, _
    ByVal nOutBufferSize As Long, _
    lpBytesRead As Long, _
    ByVal nTimeOut As Long) As Long

Private Sub testPipe()
    Dim ms As String
    Dim mr As String
    Dim returncode As Long

    ms = "Message from client;Hello World"

    returncode = namedPipeMessageExchange("testpipe", ms, mr)

    If returncode <> 0 Then
        Debug.Print "Sent:     " & ms
        Debug.Print "received: " & mr
    End If

End Sub

Public Function namedPipeMessageExchange(pipe As String, messageToSend As String, messageReceived As String) As Long
    Dim res As Long, myStr As String, i As Long, cbRead As Long, sm As String
    Dim numBytes As Long, bArray() As Byte, temp As String

    Dim b() As Byte
    Dim blen As Long
    b = StrConv(messageToSend, vbFromUnicode)
    blen = UBound(b) - LBound(b) + 1
    If blen = 0 Then b = Array(1)

    numBytes = 1000000
    ReDim bArray(numBytes)  'Build the return buffer

    'Call CallNamedPipe to do the transaction all at once
    res = CallNamedPipe("\\.\pipe\" + pipe, b(0), blen, _
        bArray(0), numBytes, _
        cbRead, 3000)  'Wait up to 3 seconds for a response

    If res > 0 Then
        ReDim Preserve bArray(0 To cbRead - 1)
        messageReceived = StrConv(bArray, vbUnicode)
        'Debug.Print "received: " & messageReceived
    Else
        Debug.Print "Error number " & Err.LastDllError & _
            " attempting to call CallNamedPipe.", vbOKOnly
    End If

    namedPipeMessageExchange = res
End Function

c# (server)

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Pipes;
using System.Text;
using System.Threading;

public class PipeServer
{
    private static int numThreads = 4;

    public static void Main()
    {
        int i;
        Thread[] servers = new Thread[numThreads];

        Console.WriteLine("\n*** Named pipe server stream with impersonation example ***\n");
        Console.WriteLine("Waiting for client connect...\n");
        for (i = 0; i < numThreads; i++)
        {
            servers[i] = new Thread(ServerThread);
            servers[i].Start();
        }
        Thread.Sleep(250);
        while (i > 0)
        {
            for (int j = 0; j < numThreads; j++)
            {
                if (servers[j] != null)
                {
                    if (servers[j].Join(250))
                    {
                        Console.WriteLine("Server thread[{0}] finished.", servers[j].ManagedThreadId);
                        servers[j] = null;
                        servers[j] = new Thread(ServerThread);
                        servers[j].Start();
                        //i--;    // decrement the thread watch count
                    }
                }
            }
        }
        Console.WriteLine("\nServer threads exhausted, exiting.");
    }

    private static void ServerThread(object data)
    {
        NamedPipeServerStream pipeServer =
            new NamedPipeServerStream("testpipe", PipeDirection.InOut, numThreads, PipeTransmissionMode.Message);

        int threadId = Thread.CurrentThread.ManagedThreadId;

        // Wait for a client to connect
        pipeServer.WaitForConnection();

        Console.WriteLine("Client connected on thread[{0}].", threadId);
        try
        {

            List<byte> intext = new List<byte>();
            do
            {
                byte[] x = new byte[1024*16];
                int read = 0;
                read = pipeServer.Read(x);
                Array.Resize(ref x, read);
                intext.AddRange(x);

            } while (!pipeServer.IsMessageComplete);

            string receivedText = System.Text.Encoding.UTF8.GetString(intext.ToArray());

            string sentText = "I am the server!";
            pipeServer.Write(System.Text.Encoding.UTF8.GetBytes(sentText));

            Console.WriteLine("Received Text: "+receivedText);
            Console.WriteLine("Sent Text:     " + sentText);

        }
        // Catch the IOException that is raised if the pipe is broken
        // or disconnected.
        catch (IOException e)
        {
            Console.WriteLine("ERROR: {0}", e.Message);
        }
        //pipeServer.WaitForPipeDrain();
        pipeServer.Close();
    }
}


来源: stackexchange

问:
How to remove RadioButton's padding in Jetpack Compose?
I have a problem with RadioButton component in my Jetpack Compose application. I have some RadioButtons with text and this have a lot of padding by default. Can I remove this padding or to set a custom padding to avoid a lot of space between each?

Currently I have this:

My code is:

```<code>Column {
    MyEnum.values().filter { rb -> rb.visible }.forEach { rb ->
        Row(
            Modifier
                .fillMaxWidth()
                .padding(horizontal = 0.dp, vertical = 0.dp)
                .clickable(
                    interactionSource = interactionSource,
                    indication = null
                ) {
                    TODO()
                },
            verticalAlignment = Alignment.CenterVertically
        ) {
            RadioButton(
                selected = (rb.position == selectedOption),
                onClick = {
                    TODO()
                },
                colors = RadioButtonDefaults.colors(
                    selectedColor = DialogOutlinedTextFocus,
                    unselectedColor = DialogOutlinedTextUnfocus
                )
            )

            Text(
                text = stringResource(id = rb.idText),
                color = Color.Black,
                fontSize = 14.sp,
                modifier = Modifier
                    .padding(horizontal = 3.dp, vertical = 2.dp)
            )
        }
    }
}
</code>```

I tried with contentPadding, but this property does not exist in RadioButton component.

答:
You could specify the Row height in the Row.Modifier
like this:</p>
```<code> Row(
    Modifier
        .fillMaxWidth()
        //HERE YOU GO
        .height(30.dp)
        .padding(horizontal = 0.dp, vertical = 0.dp)

</code>```

ContentLicense: CC BY-SA 4.0

Body:
I have a problem with RadioButton component in my Jetpack Compose application. I have some RadioButtons with text and this have a lot of padding by default. Can I remove this padding or to set a custom padding to avoid a lot of space between each?

Currently I have this:

My code is:

Column {
    MyEnum.values().filter { rb -> rb.visible }.forEach { rb ->
        Row(
            Modifier
                .fillMaxWidth()
                .padding(horizontal = 0.dp, vertical = 0.dp)
                .clickable(
                    interactionSource = interactionSource,
                    indication = null
                ) {
                    TODO()
                },
            verticalAlignment = Alignment.CenterVertically
        ) {
            RadioButton(
                selected = (rb.position == selectedOption),
                onClick = {
                    TODO()
                },
                colors = RadioButtonDefaults.colors(
                    selectedColor = DialogOutlinedTextFocus,
                    unselectedColor = DialogOutlinedTextUnfocus
                )
            )

            Text(
                text = stringResource(id = rb.idText),
                color = Color.Black,
                fontSize = 14.sp,
                modifier = Modifier
                    .padding(horizontal = 3.dp, vertical = 2.dp)
            )
        }
    }
}

I tried with contentPadding, but this property does not exist in RadioButton component.

Body:
You could specify the Row height in the Row.Modifier
like this:

 Row(
    Modifier
        .fillMaxWidth()
        //HERE YOU GO
        .height(30.dp)
        .padding(horizontal = 0.dp, vertical = 0.dp)


问:
How to dismiss keyboard on button press in Xamarin Forms
After much searching, I worked out a way to hide the keyboard on a button press in Xamarin Forms for iOS. So it's shared below.

If anyone can improve it, or share a solution for the Android side, that would be great.

答:
You can give a name to the entry fields. For instance Entryfield1 and EntryField2 are the entries that showed the keyboard. Then override the Page Events with OnDisappearing like below:

protected override void OnDisappearing()
{
    Entryfield1.Unfocus();
    EntryField2.Unfocus();
    base.OnDisappearing();
}
```


问:
System too slow after minikube . (4gb ram + core i3 )
I have a core i3 processor 7th generation and 4gb ram in my system. I installed virtual box and then ran .. minikube start on my shell.
As the minikube starts, the system is heavily slowed down. It hangs at the drop of hat. I am learning kubernetes and want to make use of yaml files to deploy and learn which I can't in playground .
And as I delete minikube , system comes back to life.
So, I have two questions. Is the issue with RAM or with core i3 ? The prerequisites for minikube is 2 CPUs . Does that mean minikube alone will have two CPUs for itself and host will not have any?
Whats causing the issue?
Second one, is there any other way i can learn k8 without minikube? playground doesn't provide way for adding yaml file</p>

答:
This is probably not enough memory so the machine starts paging/swapping. Not enough cpu is a problem, too, of course.

If you are using Windows try stopping all programs and docker desktop when using minikube. (minikube uses 2gb ram by default)
Try upgrading to at least 8gb ram when working with virtual maschine. (minikube and docker desktop both create VMs for the linux environment.)
If you are using a linux machine use the docker driver for minikube.


问:
How do you add a backend when uploading a React Native App to the IOS App Store?
I want to know how to add a backend when uploading a React Native App to the IOS App Store?

EXPO has:

```<code>eas build --platform ios
</code>```

However, I believe that this command is run from the React Native Mobile App folder..
This creates a build. However, the backend/server is completely left out which is needed for the app to function. How do I include the backend into the build that is uploaded to Expo. Or otherwise how do I also upload the backend to Expo or the IOS mobile App Platform along with the React Native Front End Build?

My folder structure is as follows for my React Native App:
server (backend)
client (frontend)

答:
You don't. You deploy the backend separately and call it from your app.

If you only need a "local" backend - i.e., no login, no data needs to be available to you as the app developer or users from each other - you can use built-in storage and persistence.

Body:
I want to know how to add a backend when uploading a React Native App to the IOS App Store?

EXPO has:

eas build --platform ios

However, I believe that this command is run from the React Native Mobile App folder..
This creates a build. However, the backend/server is completely left out which is needed for the app to function. How do I include the backend into the build that is uploaded to Expo. Or otherwise how do I also upload the backend to Expo or the IOS mobile App Platform along with the React Native Front End Build?

My folder structure is as follows for my React Native App:
server (backend)
client (frontend)


问:
Get the average difference between two values in time period
What I have:
A single table with ids and prices for date and time.

What I want to do:
To get the average difference of price for every id between the first and 15 minute of every day.
Conditions: I also want to use the last available price in 15 minutes if the 15 is missing. For example if 15 is missing, use the price in 14 minutes.

What I did:
My first step was to get the price after 15 minutes of every day for every id.

My code (I did a lot of tests with it, sorry if it doesn't look good and optimised enough) to get the last price(but not last available) in the 15 minutes interval:

```<code>WITH s as (
  SELECT DATE(date_value) as dt, date_value, stock_id sid, open FROM stocks_1min
)

SELECT sid, dt, date_value, s.open as second_price
FROM s

INNER JOIN (
    SELECT q.stock_id, DATE(q.date_value) as dat
    FROM stocks_1min q
    GROUP BY q.stock_id, dat
    ORDER BY q.stock_id ASC
  ) as end_price
           ON s.sid = end_price.stock_id
    AND s.date_value = (SELECT date_value FROM s WHERE s.sid = end_price.stock_id AND s.dt = end_price.dat LIMIT 1) + interval '15 minutes'

ORDER BY sid, dt;
</code>```

What I didn't do:

get the last AVAILABLE price in that interval.

get the first price in that interval and use it in the same query with the last price in the interval

If I got both prices then I can use AVG and Group By to get the needed value.

Expected result (example):
Table:

```<code>|stock_id|open(price)|date_value      |
|1       |10         |01-12-2020 09:31|
|1       |12         |01-12-2020 09:46|
|1       |14         |02-12-2020 09:31|
|1       |20         |02-12-2020 09:46|
|2       |14         |01-12-2020 09:31|
|2       |16         |01-12-2020 09:46|
|2       |2          |02-12-2020 09:31|
|2       |16         |02-12-2020 09:46|
</code>```

Result:

```<code>|stock_id|average_difference|
| 1      | 4                | ((12-10) + (20-14)) / 2 = 4
| 2      | 8                | ((16-14) + (16-2)) / 2 = 8
</code>```

I have never used "Partition By", but maybe here is the place to start with it?
It's my own project, where I want to test some statistical ideas for the stock market.
Thanks in advance!

You can use this link where it's a sample for testing: http://sqlfiddle.com/#!17/83597/1

答:
If I have understood correctly, you want to:

List the prices of each stock from each day during the first 15 minutes of the day

Identify the first and last (as defined by date_value) of the prices listed in step 1

Calculate the difference between the prices identified in step 2, by stock and day

Calculate the average of the differences calculated in step 3, by stock

If I have interpreted your question correctly, then:

```<code>WITH dat
AS
(
SELECT stock_id
      ,date_value::date AS date_of_date_value
      ,date_value
      ,MIN(date_value) OVER (PARTITION BY stock_id, date_value::date) AS min_date_value
      ,MAX(date_value) OVER (PARTITION BY stock_id, date_value::date) AS max_date_value
      ,"open" AS open_price
FROM stocks_1min
WHERE date_value::time <= '09:45:00.000'
),
dat2
AS
(
SELECT stock_id
      ,date_of_date_value
      ,AVG(CASE WHEN min_date_value = date_value THEN open_price ELSE NULL END) AS open_price_from
      ,AVG(CASE WHEN max_date_value = date_value THEN open_price ELSE NULL END) AS open_price_to
      ,AVG(CASE WHEN max_date_value = date_value THEN open_price ELSE NULL END)
           - AVG(CASE WHEN min_date_value = date_value THEN open_price ELSE NULL END) AS open_price_diff
FROM dat
WHERE date_value = min_date_value OR date_value = max_date_value
GROUP BY stock_id
        ,date_of_date_value
)
SELECT stock_id, AVG(open_price_diff) AS avg_open_price_diff
FROM dat2
GROUP BY stock_id;
</code>```

You may find that you need to wrap the calculation of open_price_diff with ABS, but I have not done that in the example above.

sqlfiddle

Body:
What I have:
A single table with ids and prices for date and time.

What I want to do:
To get the average difference of price for every id between the first and 15 minute of every day.
Conditions: I also want to use the last available price in 15 minutes if the 15 is missing. For example if 15 is missing, use the price in 14 minutes.

What I did:
My first step was to get the price after 15 minutes of every day for every id.

My code (I did a lot of tests with it, sorry if it doesn't look good and optimised enough) to get the last price(but not last available) in the 15 minutes interval:

WITH s as (
  SELECT DATE(date_value) as dt, date_value, stock_id sid, open FROM stocks_1min
)

SELECT sid, dt, date_value, s.open as second_price
FROM s

INNER JOIN (
    SELECT q.stock_id, DATE(q.date_value) as dat
    FROM stocks_1min q
    GROUP BY q.stock_id, dat
    ORDER BY q.stock_id ASC
  ) as end_price
           ON s.sid = end_price.stock_id
    AND s.date_value = (SELECT date_value FROM s WHERE s.sid = end_price.stock_id AND s.dt = end_price.dat LIMIT 1) + interval '15 minutes'

ORDER BY sid, dt;

What I didn't do:

get the last AVAILABLE price in that interval.

get the first price in that interval and use it in the same query with the last price in the interval

If I got both prices then I can use AVG and Group By to get the needed value.

Expected result (example):
Table:

|stock_id|open(price)|date_value      |
|1       |10         |01-12-2020 09:31|
|1       |12         |01-12-2020 09:46|
|1       |14         |02-12-2020 09:31|
|1       |20         |02-12-2020 09:46|
|2       |14         |01-12-2020 09:31|
|2       |16         |01-12-2020 09:46|
|2       |2          |02-12-2020 09:31|
|2       |16         |02-12-2020 09:46|

Result:

|stock_id|average_difference|
| 1      | 4                | ((12-10) + (20-14)) / 2 = 4
| 2      | 8                | ((16-14) + (16-2)) / 2 = 8

I have never used "Partition By", but maybe here is the place to start with it?
It's my own project, where I want to test some statistical ideas for the stock market.
Thanks in advance!

You can use this link where it's a sample for testing: http://sqlfiddle.com/#!17/83597/1

Body:
If I have understood correctly, you want to:

List the prices of each stock from each day during the first 15 minutes of the day

Identify the first and last (as defined by date_value) of the prices listed in step 1

Calculate the difference between the prices identified in step 2, by stock and day

Calculate the average of the differences calculated in step 3, by stock

If I have interpreted your question correctly, then:

WITH dat
AS
(
SELECT stock_id
      ,date_value::date AS date_of_date_value
      ,date_value
      ,MIN(date_value) OVER (PARTITION BY stock_id, date_value::date) AS min_date_value
      ,MAX(date_value) OVER (PARTITION BY stock_id, date_value::date) AS max_date_value
      ,"open" AS open_price
FROM stocks_1min
WHERE date_value::time <= '09:45:00.000'
),
dat2
AS
(
SELECT stock_id
      ,date_of_date_value
      ,AVG(CASE WHEN min_date_value = date_value THEN open_price ELSE NULL END) AS open_price_from
      ,AVG(CASE WHEN max_date_value = date_value THEN open_price ELSE NULL END) AS open_price_to
      ,AVG(CASE WHEN max_date_value = date_value THEN open_price ELSE NULL END)
           - AVG(CASE WHEN min_date_value = date_value THEN open_price ELSE NULL END) AS open_price_diff
FROM dat
WHERE date_value = min_date_value OR date_value = max_date_value
GROUP BY stock_id
        ,date_of_date_value
)
SELECT stock_id, AVG(open_price_diff) AS avg_open_price_diff
FROM dat2
GROUP BY stock_id;

You may find that you need to wrap the calculation of open_price_diff with ABS, but I have not done that in the example above.

sqlfiddle


问:
Java regular expression: escaping multi-lines comment containing the caracter $

    final Pattern PATTERN = Pattern.compile("\"[^\"]*\"");

    @Test
    public void parseCsvTest() {
        StringBuffer result = new StringBuffer();
        Matcher m = null;
        String csv="\"foo$\n" +  "bar\"";
        try {

            m = PATTERN.matcher(csv);
            while (m.find()) {
                m.appendReplacement(result, m.group().replaceAll("\\R+", ""));
            }
            m.appendTail(result);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String escaped_csv = result.toString();
        log.info(escaped_csv);
    }
```

With String csv="\"foo\n" +  "bar\"";
I'm getting the expected result that is: "foobar"

But with String csv="\"foo$\n" +  "bar\""; (notice the $ char after foo), the pattern doesn't identify the group. Note: $ is a char, not the "end of line symbol", despite it can be followed by a "end of line symbol".

Tried with PATTERN = Pattern.compile("\"[^\"]*^$?\""); without success. Will return foo and bar in 2 lines

Any ideas ?

答:
Got it work with: Pattern.compile("\"*[^$]|\"[^\"]*\"");

Results

csv = "\"foo\n" +  "bar\n" +  "doe\"" => foobardoe

csv = "\"foo$\n" +  "bar\n" +  "doe\"" => foo$bardoe

csv = "\"foo$\n" +  "bar$\n" +  "doe\"" => foo$bar$doe

csv = "\"foo$\n" +  "bar$\n" +  "doe$\"" => foo$bar$doe$

Body:
    final Pattern PATTERN = Pattern.compile("\"[^\"]*\"");

    @Test
    public void parseCsvTest() {
        StringBuffer result = new StringBuffer();
        Matcher m = null;
        String csv="\"foo$\n" +  "bar\"";
        try {

            m = PATTERN.matcher(csv);
            while (m.find()) {
                m.appendReplacement(result, m.group().replaceAll("\\R+", ""));
            }
            m.appendTail(result);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String escaped_csv = result.toString();
        log.info(escaped_csv);
    }

With String csv="\"foo\n" +  "bar\"";
I'm getting the expected result that is: "foobar"

But with String csv="\"foo$\n" +  "bar\""; (notice the $ char after foo), the pattern doesn't identify the group. Note: $ is a char, not the "end of line symbol", despite it can be followed by a "end of line symbol".

Tried with PATTERN = Pattern.compile("\"[^\"]*^$?\""); without success. Will return foo and bar in 2 lines

Any ideas ?


问:
phpMyAdmin mysqli::real_connect(): (HY000/2002): Connection refused
I'm tring to set up phpmyadmin on my mac, mysql already running on my mac, but I got mysqli::real_connect(): (HY000/2002): Connection refused error, anyone knows how to fix it, thanks

--------------------------------------------------------

config.inc.php file:

```<code><?php
/*
 * Generated configuration file
 * Generated by: phpMyAdmin 5.0.4 setup script
 * Date: Tue, 20 Oct 2020 11:36:42 +0000
 */

/* Servers configuration */
$i = 0;

/* Server: 127.0.0.1 [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'password';

/* End of servers configuration */

$cfg['blowfish_secret'] = 'C0DH:ogx;5K1W`tteE3T%xL`0TzHYSD~';
$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
?>
</code>```

答:
firstly stop MySQL and in terminal enter:

```<code>cd
cd /var
sudo mkdir mysql
sudo chmod 755 mysql
cd mysql
ln -s /tmp/mysql.sock mysql.sock
</code>```

finally restart MySQL

Body:
I'm tring to set up phpmyadmin on my mac, mysql already running on my mac, but I got mysqli::real_connect(): (HY000/2002): Connection refused error, anyone knows how to fix it, thanks

--------------------------------------------------------

config.inc.php file:

<?php
/*
 * Generated configuration file
 * Generated by: phpMyAdmin 5.0.4 setup script
 * Date: Tue, 20 Oct 2020 11:36:42 +0000
 */

/* Servers configuration */
$i = 0;

/* Server: 127.0.0.1 [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'password';

/* End of servers configuration */

$cfg['blowfish_secret'] = 'C0DH:ogx;5K1W`tteE3T%xL`0TzHYSD~';
$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
?>

Body:
firstly stop MySQL and in terminal enter:

cd
cd /var
sudo mkdir mysql
sudo chmod 755 mysql
cd mysql
ln -s /tmp/mysql.sock mysql.sock

finally restart MySQL


问:
Use numpy to sum indices based on another numpy vector

I am trying to sum specific indices per row in a numpy matrix, based on values in a second numpy vector. For example, in the image, there is the matrix A and the vector of indices inds. Here I want to sum:

```<code>A[0, inds[0]] + A[1, inds[1]] + A[2, inds[2]] + A[3, inds[3]]
</code>```

I am currently using a python for loop, making the code quite slow. Is there a way to do this using vectorisation? Thanks!

答:
Yes, numpy's magic indexing can do this.  Just generate a range for the 1st dimension and use your coords for the second:

```<code>import numpy as np

x1 = np.array( [[1,2,3,4],[5,6,7,8],[9,10,11,12],[13,14,15,16]] )
print(x1[ [0,1,2,3],[2,0,3,1] ].sum())
</code>```

Body:
I am trying to sum specific indices per row in a numpy matrix, based on values in a second numpy vector. For example, in the image, there is the matrix A and the vector of indices inds. Here I want to sum:

A[0, inds[0]] + A[1, inds[1]] + A[2, inds[2]] + A[3, inds[3]]

I am currently using a python for loop, making the code quite slow. Is there a way to do this using vectorisation? Thanks!

Body:
Yes, numpy's magic indexing can do this.  Just generate a range for the 1st dimension and use your coords for the second:

import numpy as np

x1 = np.array( [[1,2,3,4],[5,6,7,8],[9,10,11,12],[13,14,15,16]] )
print(x1[ [0,1,2,3],[2,0,3,1] ].sum())


问:
Establish a P2P Connection in C#
I realize this question is similar to some others, but I figured my situation is sufficiently different to warrant its own question (hopefully).

What I'm planning on is deploying a program on another person's computer which will open a connection to my computer after which, I'm assuming, the computers should be able to communicate with each other. Once the program starts, it should be able to pull the address information and port (if they aren't blocked) to create a connection, right?

What's more is the internet configuration of the dorm I'm living in. Every room is assigned a unique port and a static IP address assigned by a DHCP server. How do I factor this into the design of my program?

I'm thinking that this setup does not require a server as an intermediate access point, as my address details will always remain the same and the host computer can simply connect to my computer without further information. Is this correct?

Finally, I am reading a few pages about creating a connection, but am confused with all the possibilities of TcpConnection, WCF, CORBA, etc. Which one would actually be the simplest one for me to start with assuming I only want to send messages to the other machine?

Update:

The address is static in the sense that it doesn't change. My IP address is of the form 130.83.20.xxx and I can either wait for the DHCP server to assign me this address, or I can manually enter it myself using a static IP configuration.

As for the messages itself, simple text messages will suffice for the start. The ports mentioned before are the switch ports and do not come into play during network programming I believe.

答:
I would go with TcpClient and TcpListener. Check out the example code on MSDN, copy and paste it into two C# console projects and build them.

I would use 127.0.0.1 (localhost) for testing purposes on port 5001 (a commonly used test port).

```<code>TcpListener server = new TcpListener(IPAddress.Parse("127.0.0.1"), 5001);
</code>```

Then you should be able to test a simple client/server that runs on your computer only. Once you get that working, you can take the client to another computer in your dorm and make sure it still works. Once that works, you can go to a local coffee shop and take the client with you. Leave the server running at a known IP address in your dorm. In this case, have the server bind to your actual external IP (not localhost). You can do this by just specifying a port to the TcpListener constructor: `

```<code>TcpListener server = new TcpListener(5001);
</code>```

Once you get all that working by yourself or with a friend, then send it external. Better to get these things working in a demo before sending it to your customer and having him troubleshoot with you. :)

Reasoning behind my answer:

Simple TCP client/server is very straightforward and will allow a simple chat program between two computers. Then you can beef it up to send any data stream you want. You can add code like this to get access to a StreamWriter:

```<code>NetworkStream stream = client.GetStream( );
StreamWriter writer = new StreamWriter(stream);
</code>```

ContentLicense: CC BY-SA 3.0

Title: Establish a P2P Connection in C#

Body:
I would go with TcpClient and TcpListener. Check out the example code on MSDN, copy and paste it into two C# console projects and build them.

I would use 127.0.0.1 (localhost) for testing purposes on port 5001 (a commonly used test port).

TcpListener server = new TcpListener(IPAddress.Parse("127.0.0.1"), 5001);

Then you should be able to test a simple client/server that runs on your computer only. Once you get that working, you can take the client to another computer in your dorm and make sure it still works. Once that works, you can go to a local coffee shop and take the client with you. Leave the server running at a known IP address in your dorm. In this case, have the server bind to your actual external IP (not localhost). You can do this by just specifying a port to the TcpListener constructor: `

TcpListener server = new TcpListener(5001);

Once you get all that working by yourself or with a friend, then send it external. Better to get these things working in a demo before sending it to your customer and having him troubleshoot with you. :)

Reasoning behind my answer:

Simple TCP client/server is very straightforward and will allow a simple chat program between two computers. Then you can beef it up to send any data stream you want. You can add code like this to get access to a StreamWriter:

NetworkStream stream = client.GetStream( );
StreamWriter writer = new StreamWriter(stream);


问:
Sum of distinct element in a range
Consider a array (0 based index) i have to find the sum of distinct
element of all possible range[i,n] where 0< i < n

Example:

```<code>arr={1,2,1,3}

sum_range[0,3]={1,2,3}=6
sum_range[1,3]={1,2,3}=6
sum_range[2,3]={1,3}=4
sum_range[3,3]={3}=3
</code>```

O(n^2) solution is one possible solution and i have read persistent segment tree can also do this though i can't find good tutorial.

Can it be solved in less than O(N^2) time?

If someone points persistent segment tree please explain or provide some good link?

答:
There are multiple ways to solve this.

sort the array by Arrays.sort();

Using set

```<code>long sumOfDistinct(long arr[], int N)
{
     int size = arr.length;
     HashSet<Long> unique = new HashSet<Long>();
     long sum = 0;
     for(int i=0;i<size;i++){
             if(!unique.contains(arr[i])){
                 sum = sum + arr[i];
                 unique.add(arr[i]);
             }
     }

     return sum;
 }
</code>```

Title: Sum of distinct element in a range

Body:
Consider a array (0 based index) i have to find the sum of distinct
element of all possible range[i,n] where 0< i < n

Example:

arr={1,2,1,3}

sum_range[0,3]={1,2,3}=6
sum_range[1,3]={1,2,3}=6
sum_range[2,3]={1,3}=4
sum_range[3,3]={3}=3

O(n^2) solution is one possible solution and i have read persistent segment tree can also do this though i can't find good tutorial.

Can it be solved in less than O(N^2) time?

If someone points persistent segment tree please explain or provide some good link?

Body:
There are multiple ways to solve this.

sort the array by Arrays.sort();

Using set

long sumOfDistinct(long arr[], int N)
{
     int size = arr.length;
     HashSet<Long> unique = new HashSet<Long>();
     long sum = 0;
     for(int i=0;i<size;i++){
             if(!unique.contains(arr[i])){
                 sum = sum + arr[i];
                 unique.add(arr[i]);
             }
     }

     return sum;
 }


问:
Why My py2app is not working , launch error
I built my app , :

```<code>
import os, shutil
import tkinter as tk
from tkinter import *
#Ca ouvre le fichier et prend la derniere et avant derniere ligne
with open('Secrets.txt', 'r') as f:
    last_line = f.readlines()[-1]
#Ca fait la fenetre
window = tk.Tk()
window.title("Rentre un secret")
window.geometry('1100x60')
#Ton secret
lbl = Label(window, text="Ton secret:")
lbl.grid(column=0, row=0)
txt = Entry(window,width=100)
txt.grid(column=1, row=0)
txt.pack
#ecrire dans le fichier txt ton secret
def enreg():
    "Enregistrer ton secret"
    obfichier = open('Secrets.txt','a')
    if txt.get():
        obfichier.write(txt.get()+"\n")
    obfichier.close()

bou2 = Button(window, text = 'Enregistrer', command = enreg)
bou2.grid(row = 1, column = 0)
#L'autre Secret
log = Label(window, text="L'ancien secret: " + last_line)
log.grid(column=1, row=1)
directorya = txt.get()
</code>```

but when I do
./dist/TonSecret.app/Contents/MacOS/TonSecret
,
it does nothing..
and when I click on the app icon it tells me :
Launch error
Launch error
See the py2app website for debugging launch issues
can u help me

答:
py2app issues aside, this code doesn't do anything because you're never entering the tkinter event loop.  Add this line to the end of your code and your code will do something:

```<code>window.mainloop()
</code>```

If you want help in understanding why what you get when applying py2app doesn't work, you'll need to provide more information about just what you're doing to build your executable.

Body:
I built my app , :

import os, shutil
import tkinter as tk
from tkinter import *
#Ca ouvre le fichier et prend la derniere et avant derniere ligne
with open('Secrets.txt', 'r') as f:
    last_line = f.readlines()[-1]
#Ca fait la fenetre
window = tk.Tk()
window.title("Rentre un secret")
window.geometry('1100x60')
#Ton secret
lbl = Label(window, text="Ton secret:")
lbl.grid(column=0, row=0)
txt = Entry(window,width=100)
txt.grid(column=1, row=0)
txt.pack
#ecrire dans le fichier txt ton secret
def enreg():
    "Enregistrer ton secret"
    obfichier = open('Secrets.txt','a')
    if txt.get():
        obfichier.write(txt.get()+"\n")
    obfichier.close()

bou2 = Button(window, text = 'Enregistrer', command = enreg)
bou2.grid(row = 1, column = 0)
#L'autre Secret
log = Label(window, text="L'ancien secret: " + last_line)
log.grid(column=1, row=1)
directorya = txt.get()

but when I do
./dist/TonSecret.app/Contents/MacOS/TonSecret
,
it does nothing..
and when I click on the app icon it tells me :
Launch error
Launch error
See the py2app website for debugging launch issues
can u help me

Body:
py2app issues aside, this code doesn't do anything because you're never entering the tkinter event loop.  Add this line to the end of your code and your code will do something:

window.mainloop()

If you want help in understanding why what you get when applying py2app doesn't work, you'll need to provide more information about just what you're doing to build your executable.


问:
using Jquery to get page content each second
How is the right way to get page content each second?
I have one page which got a music player (using flash player) and other that generates the name of the song(php file). I have set the php file to ECHO only the name, so the only thing is to get that echo and put it at main page where is the player. But it must refresh each second(or 2-3max) because the songs are changing and so are the names.
I hope you can understand my need , ask me for other details if you need.</p>

Body: setInterval(function(){$('#title').load('/songtitle.php'); },2000);


问:
Open ALL External links in Browser (NWjs)
I was trying to make my own standalone copy of Google Chat using NWjs, but I ran into some problems.

So, when you open a link in the NWjs window, it opens in another NWjs window. However, I would like to open it in the default system browser.

I tried using Tampermonkey, which didn't work, since I figured out how to add extensions to NWjs, and making my own extension didn't work either, so I found out about NWjs's inject_js_end and tried to use that.

But, I keep getting Uncaught TypeError: Failed to set the 'innerHTML' property on 'Element': This document requires 'TrustedHTML' assignment. error.

I assumed this was because CSP was blocking my jQuery code, so I modded CSP-Blocker for chrome but it didn't work.

package.json:

```<code>{
    "name": "Chat",
    "main": "index.html",
    "chromium-args": "--enable-logging=stderr --load-extension='./AntiCSP'",
    "inject_js_end": "script.js",
    "window": {
        "title": "Chat",
        "icon": "ico.png",
        "width": 1280,
        "height": 720,
        "resizable": true
    }
}
</code>```

index.html:

```<code><!DOCTYPE html>
<html>
    <head>
        <title>Loading...</title>
    </head>

    <body>
        <script>
window.location.replace("https://chat.google.com")
        </script>
    </body>
</html>
</code>```

script.js

```<code>//jQuery here
console.log("[DEBUG]: Replace links to browser...")

$('a').click(function() {
    nw.Shell.openExternal($(this).prop("href"));
})
</code>```

How can I force all links in the webpage to open in system browser? I am mostly a Python dev and I haven't found any working solutions yet.

EDIT: As Jaredcheeda pointed out, inject_js_end does only run once. Does anyone have other possible solutions I could use? Maybe Tampermonkey...?

EDIT2: Anyone have any ideas? I still don't have any ideas...

EDIT3: I was maybe thinking of writing some kind of jQuery to replace all  tags with

答:
Correct answer: Use the official API

https://developers.google.com/chat/api/guides

Anything other than that will just be very hacky. The inject_js_end only runs once. What happens when two people are IMing and a new message is received containing a link, your function to hijack the click event and open in a default browser would need to be re-ran after every IM. You could try listening to navigation events and preventing them, but anything you do is just gonna be hacky at the end.

Body:
I was trying to make my own standalone copy of Google Chat using NWjs, but I ran into some problems.

So, when you open a link in the NWjs window, it opens in another NWjs window. However, I would like to open it in the default system browser.

I tried using Tampermonkey, which didn't work, since I figured out how to add extensions to NWjs, and making my own extension didn't work either, so I found out about NWjs's inject_js_end and tried to use that.

But, I keep getting Uncaught TypeError: Failed to set the 'innerHTML' property on 'Element': This document requires 'TrustedHTML' assignment. error.

I assumed this was because CSP was blocking my jQuery code, so I modded CSP-Blocker for chrome but it didn't work.

package.json:

{
    "name": "Chat",
    "main": "index.html",
    "chromium-args": "--enable-logging=stderr --load-extension='./AntiCSP'",
    "inject_js_end": "script.js",
    "window": {
        "title": "Chat",
        "icon": "ico.png",
        "width": 1280,
        "height": 720,
        "resizable": true
    }
}

index.html:

<!DOCTYPE html>

        <title>Loading...</title>

script.js

//jQuery here
console.log("[DEBUG]: Replace links to browser...")

$('a').click(function() {
    nw.Shell.openExternal($(this).prop("href"));
})

How can I force all links in the webpage to open in system browser? I am mostly a Python dev and I haven't found any working solutions yet.

EDIT: As Jaredcheeda pointed out, inject_js_end does only run once. Does anyone have other possible solutions I could use? Maybe Tampermonkey...?

EDIT2: Anyone have any ideas? I still don't have any ideas...

EDIT3: I was maybe thinking of writing some kind of jQuery to replace all  tags with


问:
Using the new Application Registration results in an error message on login
I have a legacy B2C application that runs just fine. I decided to upgrade to the new 'experience' since the message said they're discontinuing the old legacy blade as of Nov-21. So I followed the instructions on this page:
AAD B2C specifics
When I start up the application, I get this message:

Switching back to the legacy application results in a working login screen.

答:
To anyone running into a similar problem, I discovered two very interesting reasons for this problem:
In the legacy, you could share applications between your web API and your client. In the new workflow, you need one 'application' for your Web API, and a distinct 'application' for your clients.
This code seemed important to getting the new flow to work:

```<code>                this.PublicClientApplication = PublicClientApplicationBuilder
                    .Create(this.ClientId)
                    .WithIosKeychainSecurityGroup(this.KeychainGroup)
                    .WithRedirectUri(this.RedirectUri)
                    .WithB2CAuthority($"https://{this.AuthorityHostName}/tfp/{this.Tenant}/{this.AuthorityPolicy}")
                    .Build();
</code>```

The 'WithRedirectUri' got me past the original error posted above.

Body:
To anyone running into a similar problem, I discovered two very interesting reasons for this problem:
In the legacy, you could share applications between your web API and your client. In the new workflow, you need one 'application' for your Web API, and a distinct 'application' for your clients.
This code seemed important to getting the new flow to work:

                this.PublicClientApplication = PublicClientApplicationBuilder
                    .Create(this.ClientId)
                    .WithIosKeychainSecurityGroup(this.KeychainGroup)
                    .WithRedirectUri(this.RedirectUri)
                    .WithB2CAuthority($"https://{this.AuthorityHostName}/tfp/{this.Tenant}/{this.AuthorityPolicy}")
                    .Build();

The 'WithRedirectUri' got me past the original error posted above.


问:
Why can't my recyclerview scroll completely
I am trying to get recyclerview to scroll completely. For some reason the recyclerview stops to scroll at a particular spot, as if it as scrolled completedly.

Here's a picture of this:

Please keep in mind this is where the recyclerview scrolls to. And this fragment (screen) only contains the recyclerview.

this is the layout of the fragment containing the recyclerview:

```<code><androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".ui.ways.WaysFragment">

    <androidx.recyclerview.widget.RecyclerView
        android:id="@+id/recyclerview"
        android:layout_width="0dp"
        android:layout_height="0dp"
        app:layoutManager="androidx.recyclerview.widget.LinearLayoutManager"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        tools:listitem="@layout/custom_ways_item" />
</androidx.constraintlayout.widget.ConstraintLayout>
</code>```

This the custom_view_item of the recyclerview:

```<code><androidx.constraintlayout.widget.ConstraintLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    android:layout_height="425dp">

    <androidx.cardview.widget.CardView
        android:id="@+id/cardView"
        android:layout_width="match_parent"
        android:layout_height="370dp"
        android:layout_rowWeight="1"
        android:layout_columnWeight="1"
        android:layout_marginStart="30dp"
        android:layout_marginTop="25dp"
        android:layout_marginEnd="30dp"
        android:foreground="?selectableItemBackground"
        app:cardCornerRadius="5dp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent">

        .....

    </androidx.cardview.widget.CardView>

</androidx.constraintlayout.widget.ConstraintLayout>
</code>```

Thanks for your help in advance.. I'm more than happy to provide any information needed. Thanks again.

EDIT

Here's my activity_main.xml. I'm using navigation component method.

```<code><androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:id="@+id/container"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <fragment
        android:id="@+id/nav_host_fragment_activity_main"
        android:name="androidx.navigation.fragment.NavHostFragment"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        app:defaultNavHost="true"
        app:layout_constraintBottom_toTopOf="@id/nav_view"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toTopOf="@id/container"
        app:navGraph="@navigation/mobile_navigation" />

    <com.google.android.material.bottomnavigation.BottomNavigationView
        android:id="@+id/nav_view"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:background="?android:attr/windowBackground"
        app:labelVisibilityMode="selected"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:menu="@menu/bottom_nav_menu" />

</androidx.constraintlayout.widget.ConstraintLayout>
</code>```

答:
The RecyclerView (fragment content) is drawn behind the BottomNavView; to solve this you need to make the fragment container to match the constraints by setting android:layout_height="0dp"; so that the constraints set can be applied:

```<code> <fragment
    ...
    android:layout_height="0dp"
</code>```

Body:
I am trying to get recyclerview to scroll completely. For some reason the recyclerview stops to scroll at a particular spot, as if it as scrolled completedly.

Here's a picture of this:

Please keep in mind this is where the recyclerview scrolls to. And this fragment (screen) only contains the recyclerview.

this is the layout of the fragment containing the recyclerview:

<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".ui.ways.WaysFragment">

    <androidx.recyclerview.widget.RecyclerView
        android:id="@+id/recyclerview"
        android:layout_width="0dp"
        android:layout_height="0dp"
        app:layoutManager="androidx.recyclerview.widget.LinearLayoutManager"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        tools:listitem="@layout/custom_ways_item" />
</androidx.constraintlayout.widget.ConstraintLayout>

This the custom_view_item of the recyclerview:

<androidx.constraintlayout.widget.ConstraintLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    android:layout_height="425dp">

    <androidx.cardview.widget.CardView
        android:id="@+id/cardView"
        android:layout_width="match_parent"
        android:layout_height="370dp"
        android:layout_rowWeight="1"
        android:layout_columnWeight="1"
        android:layout_marginStart="30dp"
        android:layout_marginTop="25dp"
        android:layout_marginEnd="30dp"
        android:foreground="?selectableItemBackground"
        app:cardCornerRadius="5dp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent">

        .....

    </androidx.cardview.widget.CardView>

</androidx.constraintlayout.widget.ConstraintLayout>

Thanks for your help in advance.. I'm more than happy to provide any information needed. Thanks again.

EDIT

Here's my activity_main.xml. I'm using navigation component method.

<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:id="@+id/container"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <fragment
        android:id="@+id/nav_host_fragment_activity_main"
        android:name="androidx.navigation.fragment.NavHostFragment"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        app:defaultNavHost="true"
        app:layout_constraintBottom_toTopOf="@id/nav_view"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toTopOf="@id/container"
        app:navGraph="@navigation/mobile_navigation" />

    <com.google.android.material.bottomnavigation.BottomNavigationView
        android:id="@+id/nav_view"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:background="?android:attr/windowBackground"
        app:labelVisibilityMode="selected"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:menu="@menu/bottom_nav_menu" />

</androidx.constraintlayout.widget.ConstraintLayout>

Body:
The RecyclerView (fragment content) is drawn behind the BottomNavView; to solve this you need to make the fragment container to match the constraints by setting android:layout_height="0dp"; so that the constraints set can be applied:

 <fragment
    ...
    android:layout_height="0dp"


问:
ImageCol in DataGridView doesn't resize my Bitmap image from URL
I have a function which I found on here it works for me  but it doesn't shrink the png image to 10,10 size as it suppose to do and after it populate my cell it is actually size as 250px like from web (png image from web)

My Function:

```<code>    Public Shared Function GetImage(Url As String) As Image
    Try
        If (Not ImageList.ContainsKey(Url.Trim)) Then
            Dim req As WebRequest = WebRequest.Create(Url)
            Dim response As WebResponse = req.GetResponse()
            Dim stream As Stream = response.GetResponseStream()
            Dim img As Image = Image.FromStream(stream)
            stream.Close()
            ImageList.Add(Url.Trim, img)
            '
        End If

        Return ImageList(Url.Trim)

    Catch ex As Exception
        Debug.Print(ex.Message)
        Return New Bitmap(10, 10)
    End Try
End Function
</code>```

DatagridView code:

```<code>   Dim _inj_ThumbLink As String = jsonResult(0)._thumb
   Dim _inj_Thumb As Image = GetImage(_inj_ThumbLink)
   Form1.dgvMain.Rows(3).Cells(1).Value = _inj_Thumb
</code>```

picture 1
picture 2

答:
Following Jimi's suggestion in a comment

DataGridViewImageColumn.ImageLayout

I used

```<code>column.ImageLayout = DataGridViewImageCellLayout.Zoom
</code>```

Body:
I have a function which I found on here it works for me  but it doesn't shrink the png image to 10,10 size as it suppose to do and after it populate my cell it is actually size as 250px like from web (png image from web)

My Function:

    Public Shared Function GetImage(Url As String) As Image
    Try
        If (Not ImageList.ContainsKey(Url.Trim)) Then
            Dim req As WebRequest = WebRequest.Create(Url)
            Dim response As WebResponse = req.GetResponse()
            Dim stream As Stream = response.GetResponseStream()
            Dim img As Image = Image.FromStream(stream)
            stream.Close()
            ImageList.Add(Url.Trim, img)
            '
        End If

        Return ImageList(Url.Trim)

    Catch ex As Exception
        Debug.Print(ex.Message)
        Return New Bitmap(10, 10)
    End Try
End Function

DatagridView code:

   Dim _inj_ThumbLink As String = jsonResult(0)._thumb
   Dim _inj_Thumb As Image = GetImage(_inj_ThumbLink)
   Form1.dgvMain.Rows(3).Cells(1).Value = _inj_Thumb

picture 1
picture 2

Body:
Following Jimi's suggestion in a comment

DataGridViewImageColumn.ImageLayout

I used

column.ImageLayout = DataGridViewImageCellLayout.Zoom


问:
Hebrew send to google spreadsheet from PowerShell Hebrew correctly
Please help.
In console look hebrew correct but in spreadsheet is not correctenter image description here

```<code>    $import = New-Object System.Collections.ArrayList($null)
$import.Add( @("route_id"))
$import.Add( @( 'אבא אבן 1 הרצליה' ))  | Out-NULL

try {

   $Response = Set-GSheetData -accessToken $accessToken -rangeA1 "A1:X$($import.Count)" -sheetName "23-07-21" -spreadSheetID $SpreadsheetID -values $import -Verbose
   $Response | ConvertTo-Json

} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
    }
</code>```

答:
It's bug in the psmodule. Author must specify encoding UTF8.
https://github.com/umn-microsoft-automation/UMN-Google/pull/50</p>

Body:
Please help.
In console look hebrew correct but in spreadsheet is not correctenter image description here

    $import = New-Object System.Collections.ArrayList($null)
$import.Add( @("route_id"))
$import.Add( @( 'אבא אבן 1 הרצליה' ))  | Out-NULL

try {

   $Response = Set-GSheetData -accessToken $accessToken -rangeA1 "A1:X$($import.Count)" -sheetName "23-07-21" -spreadSheetID $SpreadsheetID -values $import -Verbose
   $Response | ConvertTo-Json

} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
    }


问:
Is there a way to open multiple tabs simultaneously on Playwright or Puppeteer to complete the same tasks?
I just started coding, and I was wondering if there was a way to open multiple tabs concurrently with one another. Currently, my code goes something like this:

```<code>const puppeteer = require("puppeteer");

const rand_url = "https://www.google.com";

async function initBrowser() {
  const browser = await puppeteer.launch({ headless: false });
  const page = await browser.newPage();
  await page.goto(rand_url);
  await page.setViewport({
    width: 1200,
    height: 800,
  });

  return page;
}

async function login(page) {
  await page.goto("https://www.google.com");
  await page.waitFor(100);
  await page.type("input[id ='user_login'", "xxx");
  await page.waitFor(100);
  await page.type("input[id ='user_password'", "xxx");
}
</code>```

this is not my exact code, replaced with different aliases, but you get the idea. I was wondering if there was anyone out there that knows the code that allows this same exact browser to be opened on multiple instances, replacing the respective login info only. Of course, it would be great to prevent my IP from getting banned too, so if there was a way to apply proxies to each respective "browser"/ instance, that would be perfect.

Lastly, I would like to know whether or not playwright or puppeteer is superior in the way they can handle these multiple instances. I don't even know if this is a possibility, but please enlighten me. I want to learn more.

答:
You can use multiple browser window as different login/cookies.

For simplicity, you can use the puppeteer-cluster module by Thomas Dondorf.

This module can make your puppeteer launched and queued one by one so that you can use this to automating your login, and even save login cookies for the next launches.

Feel free to go to the Github: https://github.com/thomasdondorf/puppeteer-cluster

```<code>const { Cluster } = require('puppeteer-cluster')

(async () => {
  const cluster = await Cluster.launch({
    concurrency: Cluster.CONCURRENCY_CONTEXT,
    maxConcurrency: 2, // <= this is the number of
                       // parallel task running simultaneously
  })                   // You can change to the number of CPU
  const cpuNumber = require('os').cpus().length // for example

  await cluster.task(async ({ page, data: [username, password] }) => {
    await page.goto('https://www.example.com')
    await page.waitForTimeout(100)
    await page.type('input[id ="user_login"', username)
    await page.waitForTimeout(100)
    await page.type('input[id ="user_password"', password)
    const screen = await page.screenshot()
    // Store screenshot, Save Cookies, do something else
  });

     cluster.queue(['myFirstUsername', 'PassW0Rd1'])
     cluster.queue(['anotherUsername', 'Secr3tAgent!'])
  // cluster.queue([username, password])
  // username and password array passed into cluster task function
  // many more pages/account

  await cluster.idle()
  await cluster.close()
})()
</code>```

For Playwright, sadly still unsupported by the module above,
you can use browser pool (cluster) module to automating the Playwright launcher.

And for proxy usage, I recommend Puppeteer library as the legendary one.

Don't forget to choose my answer as the right one, if this helps you.

Body:
I just started coding, and I was wondering if there was a way to open multiple tabs concurrently with one another. Currently, my code goes something like this:

const puppeteer = require("puppeteer");

const rand_url = "https://www.google.com";

async function initBrowser() {
  const browser = await puppeteer.launch({ headless: false });
  const page = await browser.newPage();
  await page.goto(rand_url);
  await page.setViewport({
    width: 1200,
    height: 800,
  });

  return page;
}

async function login(page) {
  await page.goto("https://www.google.com");
  await page.waitFor(100);
  await page.type("input[id ='user_login'", "xxx");
  await page.waitFor(100);
  await page.type("input[id ='user_password'", "xxx");
}

this is not my exact code, replaced with different aliases, but you get the idea. I was wondering if there was anyone out there that knows the code that allows this same exact browser to be opened on multiple instances, replacing the respective login info only. Of course, it would be great to prevent my IP from getting banned too, so if there was a way to apply proxies to each respective "browser"/ instance, that would be perfect.

Lastly, I would like to know whether or not playwright or puppeteer is superior in the way they can handle these multiple instances. I don't even know if this is a possibility, but please enlighten me. I want to learn more.

Body:
You can use multiple browser window as different login/cookies.

For simplicity, you can use the puppeteer-cluster module by Thomas Dondorf.

This module can make your puppeteer launched and queued one by one so that you can use this to automating your login, and even save login cookies for the next launches.

Feel free to go to the Github: https://github.com/thomasdondorf/puppeteer-cluster

const { Cluster } = require('puppeteer-cluster')

(async () => {
  const cluster = await Cluster.launch({
    concurrency: Cluster.CONCURRENCY_CONTEXT,
    maxConcurrency: 2, // <= this is the number of
                       // parallel task running simultaneously
  })                   // You can change to the number of CPU
  const cpuNumber = require('os').cpus().length // for example

  await cluster.task(async ({ page, data: [username, password] }) => {
    await page.goto('https://www.example.com')
    await page.waitForTimeout(100)
    await page.type('input[id ="user_login"', username)
    await page.waitForTimeout(100)
    await page.type('input[id ="user_password"', password)
    const screen = await page.screenshot()
    // Store screenshot, Save Cookies, do something else
  });

     cluster.queue(['myFirstUsername', 'PassW0Rd1'])
     cluster.queue(['anotherUsername', 'Secr3tAgent!'])
  // cluster.queue([username, password])
  // username and password array passed into cluster task function
  // many more pages/account

  await cluster.idle()
  await cluster.close()
})()

For Playwright, sadly still unsupported by the module above,
you can use browser pool (cluster) module to automating the Playwright launcher.

And for proxy usage, I recommend Puppeteer library as the legendary one.

Don't forget to choose my answer as the right one, if this helps you.


问:
Snowflake Connectors Require Region in Account Name (Locator)
In the past week I've run across two tools (Hevo and Confluent) that require their cluster to be in the same region as the Snowflake account. This is understandable - since both are services they own the pipes and they want to manage their architecture. Unfortunately both parse the Snowflake account locator to determine the region. I'm using an account created under our Snowflake organization, which has an account name, but not the full account locator.

I consider this a tool as opposed to a Snowflake issue - they should do validation using Current_Region, but it's been a bit of a challenge to get the message across (particularly to Hevo). Is there some trick that will let me turn the account name into a full account locator so that I can solve this on the Snowflake side?

答:
Assuming you are an organization:
https://docs.snowflake.com/en/sql-reference/sql/show-organization-accounts.html

You can also find SELECT CURRENT_REGION() and use that to construct your URL.


问:
How to get data point label using click in plotly
I would like to store the data point label whenever I click it in my plotly plot. The label is the name of the data  point that appears in the first column of my data frame.

For example if I hover over a data point it will show me the x and y information as well as the data point name:
x: TRUE
y: 27
Name: cheeseburger

What I want is to store the label of that data point, 'cheeseburger' as a variable to use later.

I have tried using plotly_click and accessing the event_data but it returns x and y values and all I want is the data point name. This is my code:

```<code>
    library(shiny)
    library(plotly)

ui <- navbarPage(fluid = TRUE, id = "navbarID",
  theme = shinytheme("superhero"),
  tabsetPanel(type = "tabs",
  tabPanel("File Upload",

  # Sidebar layout with input and output definitions ----
  sidebarLayout(

    # Sidebar panel for inputs ----
    sidebarPanel(

      # Input: Select a file ----
      fileInput("file1", "Choose CSV File",
                multiple = FALSE,
                accept = c("text/csv",
                         "text/comma-separated-values,text/plain",
                         ".csv")),

      # Horizontal line ----
      tags$hr(),

      # Input: Checkbox if file has header ----
      checkboxInput("header", "Header", TRUE),

      # Input: Select separator ----
      radioButtons("sep", "Separator",
                   choices = c(Comma = ",",
                               Semicolon = ";",
                               Tab = "\t"),
                   selected = ","),

      # Input: Select quotes ----
      radioButtons("quote", "Quote",
                   choices = c(None = "",
                               "Double Quote" = '"',
                               "Single Quote" = "'"),
                   selected = '"'),

      # Horizontal line ----
      tags$hr(),

      # Input: Select number of rows to display ----
      radioButtons("disp", "Display",
                   choices = c(Head = "head",
                               All = "all"),
                   selected = "head")

    ),

     mainPanel(

      # Output: Data file ----
      tableOutput("contents")

    ),

  )
  ),
 tabPanel("firstbox",
 uiOutput("box"),

),
    tabPanel("facet_plots",
    mainPanel(plotlyOutput("ind_plot"))
    )    # Main panel for displaying outputs ----
))

# server ----
# Define server logic to plot various variables against
server <- function(input, output, session) {

  my_data <- reactive({

  inFile <- input$file1
    req(inFile)

    # when reading semicolon separated files,
    # having a comma separator causes `read.csv` to error
    tryCatch(
      {
        df_x <<- read.csv(inFile$datapath,
                 header = input$header,
                 sep = input$sep,
                 quote = input$quote)

      },
      error = function(e) {
        # return a safeError if a parsing error occurs
        stop(safeError(e))
      }
    )

    if(input$disp == "head") {
      return(head(df_x))
    }
    else {
      return(df_x)
    }

    })

  #server logic for file upload tab
   output$contents <- renderTable({
     my_data()

  })

  #server logic for boxplot tab
  output$box <- renderUI({
   tabPanel("first_box",
  sidebarPanel(
    selectInput("variable", "Name:", unique(qc$Action)),
    sliderInput("quantile", "Quantile Range:",
      min = 75, max = 95, value = c(85), step = 5),
    br(),
    br(),

mainPanel(
    h2("title", align = "center"),
plotlyOutput("plot", height = '1000px', width = "100%")
           )

)

    })

  observeEvent(input$file1, {
    req(df_x)
    source("db_prep.R")

  fn <- reactive(get(paste0("s_", input$quantile)))
  output$plot <-  renderPlotly(fn()(input$variable))
})                        #    ^^^ note the reactive value goes fn()(var)

  s_75 <- function(var) box_75(var)
  s_80 <- function(var) box_80(var)
  s_85 <- function(var) box_85(var)
  s_90 <- function(var) box_90(var)
  s_95 <- function(var) box_95(var)
  m_p <-  function(var) com_g(var)

 #Below is that part I am running into trouble

  #create reactive for subset plot on second tab
    observeEvent(input$variable, {
    s <- reactive({
    event_data("plotly_click",  source = 'sub_plot')
    })

  observeEvent(s(), {
    updateTabsetPanel(session, inputId = "navbarID", selected = "facet_plots")
  })

    output$ind_plot <- renderPlotly({
    req(s())

    m_p(s()) # <-- problem here. I need to feed data point label as a string into this function to transform and render new sub plot.
  })

  })

</code>```

答:
Updated based on OP's comment that the label for each point is placed using geom_text(label()) and converted to plotly object

```<code>library(shiny)
library(plotly)
library(data.table)

data = data.table(x=1:10, y=sample(1:100, 10))
food_labels = sample(c("cheeseburger", "hamburger", "salad", "fries"), 10, replace=T)

get_plotly <- function() {
  ggplotly(ggplot(data,aes(x,y)) + geom_point() + geom_text(label=food_labels))
}

ui <- fluidPage(
  plotlyOutput("plot"),
  verbatimTextOutput(outputId = "clicked_point")
)

server <- function(input, output, session) {

  plt <- reactive(get_plotly())

  output$plot <- renderPlotly(plt())

  s <- reactive(event_data("plotly_click"))

  label_name <- eventReactive(s(), {
    data.table(
      x = plt()$x$data[[2]]$x,
      y = plt()$x$data[[2]]$y,
      text = plt()$x$data[[2]]$text
      )[x==s()$x & y==s()$y, text]
  })

  output$clicked_point = renderPrint(label_name())
}

shinyApp(ui, server)
</code>```

Body:
I would like to store the data point label whenever I click it in my plotly plot. The label is the name of the data  point that appears in the first column of my data frame.

For example if I hover over a data point it will show me the x and y information as well as the data point name:
x: TRUE
y: 27
Name: cheeseburger

What I want is to store the label of that data point, 'cheeseburger' as a variable to use later.

I have tried using plotly_click and accessing the event_data but it returns x and y values and all I want is the data point name. This is my code:

    library(shiny)
    library(plotly)

ui <- navbarPage(fluid = TRUE, id = "navbarID",
  theme = shinytheme("superhero"),
  tabsetPanel(type = "tabs",
  tabPanel("File Upload",

  # Sidebar layout with input and output definitions ----
  sidebarLayout(

    # Sidebar panel for inputs ----
    sidebarPanel(

      # Input: Select a file ----
      fileInput("file1", "Choose CSV File",
                multiple = FALSE,
                accept = c("text/csv",
                         "text/comma-separated-values,text/plain",
                         ".csv")),

      # Horizontal line ----
      tags$hr(),

      # Input: Checkbox if file has header ----
      checkboxInput("header", "Header", TRUE),

      # Input: Select separator ----
      radioButtons("sep", "Separator",
                   choices = c(Comma = ",",
                               Semicolon = ";",
                               Tab = "\t"),
                   selected = ","),

      # Input: Select quotes ----
      radioButtons("quote", "Quote",
                   choices = c(None = "",
                               "Double Quote" = '"',
                               "Single Quote" = "'"),
                   selected = '"'),

      # Horizontal line ----
      tags$hr(),

      # Input: Select number of rows to display ----
      radioButtons("disp", "Display",
                   choices = c(Head = "head",
                               All = "all"),
                   selected = "head")

    ),

     mainPanel(

      # Output: Data file ----
      tableOutput("contents")

    ),

  )
  ),
 tabPanel("firstbox",
 uiOutput("box"),

),
    tabPanel("facet_plots",
    mainPanel(plotlyOutput("ind_plot"))
    )    # Main panel for displaying outputs ----
))

# server ----
# Define server logic to plot various variables against
server <- function(input, output, session) {

  my_data <- reactive({

  inFile <- input$file1
    req(inFile)

    # when reading semicolon separated files,
    # having a comma separator causes `read.csv` to error
    tryCatch(
      {
        df_x <<- read.csv(inFile$datapath,
                 header = input$header,
                 sep = input$sep,
                 quote = input$quote)

      },
      error = function(e) {
        # return a safeError if a parsing error occurs
        stop(safeError(e))
      }
    )

    if(input$disp == "head") {
      return(head(df_x))
    }
    else {
      return(df_x)
    }

    })

  #server logic for file upload tab
   output$contents <- renderTable({
     my_data()

  })

  #server logic for boxplot tab
  output$box <- renderUI({
   tabPanel("first_box",
  sidebarPanel(
    selectInput("variable", "Name:", unique(qc$Action)),
    sliderInput("quantile", "Quantile Range:",
      min = 75, max = 95, value = c(85), step = 5),
    br(),
    br(),

mainPanel(
    h2("title", align = "center"),
plotlyOutput("plot", height = '1000px', width = "100%")
           )

)

    })

  observeEvent(input$file1, {
    req(df_x)
    source("db_prep.R")

  fn <- reactive(get(paste0("s_", input$quantile)))
  output$plot <-  renderPlotly(fn()(input$variable))
})                        #    ^^^ note the reactive value goes fn()(var)

  s_75 <- function(var) box_75(var)
  s_80 <- function(var) box_80(var)
  s_85 <- function(var) box_85(var)
  s_90 <- function(var) box_90(var)
  s_95 <- function(var) box_95(var)
  m_p <-  function(var) com_g(var)

 #Below is that part I am running into trouble

  #create reactive for subset plot on second tab
    observeEvent(input$variable, {
    s <- reactive({
    event_data("plotly_click",  source = 'sub_plot')
    })

  observeEvent(s(), {
    updateTabsetPanel(session, inputId = "navbarID", selected = "facet_plots")
  })

    output$ind_plot <- renderPlotly({
    req(s())

    m_p(s()) # <-- problem here. I need to feed data point label as a string into this function to transform and render new sub plot.
  })

  })

Body:
Updated based on OP's comment that the label for each point is placed using geom_text(label()) and converted to plotly object

library(shiny)
library(plotly)
library(data.table)

data = data.table(x=1:10, y=sample(1:100, 10))
food_labels = sample(c("cheeseburger", "hamburger", "salad", "fries"), 10, replace=T)

get_plotly <- function() {
  ggplotly(ggplot(data,aes(x,y)) + geom_point() + geom_text(label=food_labels))
}

ui <- fluidPage(
  plotlyOutput("plot"),
  verbatimTextOutput(outputId = "clicked_point")
)

server <- function(input, output, session) {

  plt <- reactive(get_plotly())

  output$plot <- renderPlotly(plt())

  s <- reactive(event_data("plotly_click"))

  label_name <- eventReactive(s(), {
    data.table(
      x = plt()$x$data[[2]]$x,
      y = plt()$x$data[[2]]$y,
      text = plt()$x$data[[2]]$text
      )[x==s()$x & y==s()$y, text]
  })

  output$clicked_point = renderPrint(label_name())
}

shinyApp(ui, server)


问:
Parsing XML from an HTTPS URL using NSXMLParser?
I'm trying to parse XML directly from an HTTPS URL, as follows:

```<code>NSString *const URL = @"https://some/HTTPS/url";
NSURL* url = [NSURL URLWithString:URL];
NSXMLParser* parser = [[NSXMLParser alloc] initWithContentsOfURL:url];
[parser setDelegate:self];
[parser parse];
</code>```

I have the following delegate method for the parser:

```<code>- (void) parser:(NSXMLParser *)parser
didStartElement:(NSString *)elementName
namespaceURI:(NSString *)namespaceURI
qualifiedName:(NSString *)qualifiedName
attributes:(NSDictionary *)attributeDict
{
    NSLog(@"Started parsing %@", elementName);
}
</code>```

This seems to work fine for HTTP URL's, but shows no result for an HTTPS URL.

How could I fix this?

答: I'm not sure if you're doing iPhone programming or not, but for the record, in the NSXMLParser Class Reference (in the iPhone 3.0 beta 2 SDK), initWithContentsOfURL:(NSURL *)url does NOT appear to be deprecated.

OwnerDisplayName: Debajit

ContentLicense: CC BY-SA 2.5

Body:
I'm trying to parse XML directly from an HTTPS URL, as follows:

NSString *const URL = @"https://some/HTTPS/url";
NSURL* url = [NSURL URLWithString:URL];
NSXMLParser* parser = [[NSXMLParser alloc] initWithContentsOfURL:url];
[parser setDelegate:self];
[parser parse];

I have the following delegate method for the parser:

- (void) parser:(NSXMLParser *)parser
didStartElement:(NSString *)elementName
namespaceURI:(NSString *)namespaceURI
qualifiedName:(NSString *)qualifiedName
attributes:(NSDictionary *)attributeDict
{
    NSLog(@"Started parsing %@", elementName);
}

This seems to work fine for HTTP URL's, but shows no result for an HTTPS URL.

How could I fix this?

OwnerDisplayName: sandover


问:
Saving credentials for reuse by powershell and error ConvertTo-SecureString : Key not valid for use in specified state
I was doing something like described in this post to save credentials in a secured file so our automated process can use that to run remote PS scripts via Invoke-command:
http://blogs.technet.com/b/robcost/archive/2008/05/01/powershell-tip-storing-and-using-password-credentials.aspx

This works great when I run this under my account - password is read from encrypted file, passed to Invoke-command and everything is fine.

Today, when my script was ready for its prime time, I tried to run it under windows account that will be used by automated process and got this error below while my script was trying to read secured password from a file:

```<code>ConvertTo-SecureString : Key not valid for use in specified state.
At \\remoted\script.ps1:210 char:87
+ $password = get-content $PathToFolderWithCredentials\pass.txt | convertto-sec
urestring <<<<
    + CategoryInfo          : InvalidArgument: (:) [ConvertTo-SecureString], C
   ryptographicException
    + FullyQualifiedErrorId : ImportSecureString_InvalidArgument_Cryptographic
   Error,Microsoft.PowerShell.Commands.ConvertToSecureStringCommand
</code>```

Asked my workmate to run under his account and he got the same error.

This is the code I am using to save credentials:

```<code>$PathToFolderWithCredentials = "\\path\removed"

write-host "Enter login as domain\login:"
read-host | out-file $PathToFolderWithCredentials\login.txt

write-host "Enter password:"
read-host -assecurestring | convertfrom-securestring | out-file $PathToFolderWithCredentials\pass.txt

write-host "*** Credentials have been saved to $pathtofolder ***"
</code>```

This is the code in the script to run by automated process to read them to use in Invoke-command:

```<code>$login= get-content $PathToFolderWithCredentials\login.txt
$password = get-content $PathToFolderWithCredentials\pass.txt | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $login,$password
</code>```

Error happens on line $password = get-content $PathToFolderWithCredentials\pass.txt | convertto-securestring

Any ideas?

答:
ConvertFrom-SecureString takes a Key ( and SecureKey) parameter. You can specify the key to save the encrypted standard string and then use the key again in ConvertTo-SecureString to get back the secure string, irrespective of the user account.

http://technet.microsoft.com/en-us/library/dd315356.aspx

In a project, I have implemented asymmetric encryption, whereby people encrypt the password using the public key and the automation process has the private key to decrypt passwords: Handling passwords in production config for automated deployment

Body:
I was doing something like described in this post to save credentials in a secured file so our automated process can use that to run remote PS scripts via Invoke-command:
http://blogs.technet.com/b/robcost/archive/2008/05/01/powershell-tip-storing-and-using-password-credentials.aspx

This works great when I run this under my account - password is read from encrypted file, passed to Invoke-command and everything is fine.

Today, when my script was ready for its prime time, I tried to run it under windows account that will be used by automated process and got this error below while my script was trying to read secured password from a file:

ConvertTo-SecureString : Key not valid for use in specified state.
At \\remoted\script.ps1:210 char:87
+ $password = get-content $PathToFolderWithCredentials\pass.txt | convertto-sec
urestring <<<<
    + CategoryInfo          : InvalidArgument: (:) [ConvertTo-SecureString], C
   ryptographicException
    + FullyQualifiedErrorId : ImportSecureString_InvalidArgument_Cryptographic
   Error,Microsoft.PowerShell.Commands.ConvertToSecureStringCommand

Asked my workmate to run under his account and he got the same error.

This is the code I am using to save credentials:

$PathToFolderWithCredentials = "\\path\removed"

write-host "Enter login as domain\login:"
read-host | out-file $PathToFolderWithCredentials\login.txt

write-host "Enter password:"
read-host -assecurestring | convertfrom-securestring | out-file $PathToFolderWithCredentials\pass.txt

write-host "*** Credentials have been saved to $pathtofolder ***"

This is the code in the script to run by automated process to read them to use in Invoke-command:

$login= get-content $PathToFolderWithCredentials\login.txt
$password = get-content $PathToFolderWithCredentials\pass.txt | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $login,$password

Error happens on line $password = get-content $PathToFolderWithCredentials\pass.txt | convertto-securestring

Any ideas?


问:
JSONb update multiply fields with one query
I need to update data in two elements of JSON array. The example below is simplified, usually it has more elements wit other keys

```<code>[{"key": "startDate", "value": "2022-02-05T23:00:00Z"}, {"key": "endDate", "value": "2022-02-05T23:00:00Z"}]
</code>```

My goal is to change 'value' in startDate and endDate

My query is

```<code>UPDATE my_table ti
SET fields = jsonb_set(ti.fields, path, temp, false)
FROM my_table ti1,
     LATERAL (
         SELECT ARRAY [(ordinal - 1)::text, 'value']                                               AS path,
                to_jsonb(((field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields #> '{}') WITH ORDINALITY arr(field, ordinal)
         WHERE field ->> 'key' = 'startDate'
           AND field ->> 'value' IS NOT NULL
         ) field
WHERE ti.id = ti1.id;
</code>```

And the same query I do for endDate, just replacing WHERE condition.
So, I got two queries, but I want to replace it with one

I tried to rewrite WHERE condition to  WHERE field ->> 'key' in ('startDate', 'endDate), but it didn't work and as a result I got only one (the first) key value updated

```<code>[{"key": "startDate", "value": "2022-02-06"}, {"key": "endDate", "value": "2022-02-05T23:00:00Z"}]
</code>```

How can I update two values in one query?

答:
Solution 1

You need to call the jsonb_set function twice if you want to make two updates in the same jsonb data :

```<code>UPDATE my_table ti
   SET fields = jsonb_set(jsonb_set(ti1.fields, e.path, e.temp, false), s.path, s.temp, false)
  FROM my_table ti1
 CROSS JOIN LATERAL
     ( SELECT ARRAY [(arr.ordinal - 1)::text, 'value'] AS path
            , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' = 'startDate'
          AND arr.field ->> 'value' IS NOT NULL
     ) AS s
 CROSS JOIN LATERAL
     ( SELECT ARRAY [(arr.ordinal - 1)::text, 'value'] AS path
            , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' = 'endDate'
          AND arr.field ->> 'value' IS NOT NULL
     ) AS e
 WHERE ti.id = ti1.id
</code>```

Solution 2

You can create an aggregate function jsonb_set_agg based on the jsonb_set function :

```<code>CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, b boolean)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$ SELECT jsonb_set(COALESCE(x, y), p, z, b) $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg (jsonb, text[], jsonb, boolean)
( SFUNC = jsonb_set
, STYPE = jsonb
) ;
</code>```

Then you can iterate on the new aggregate function jsonb_set_agg :

```<code>UPDATE my_table AS ti
   SET fields = r.res
  FROM my_table AS ti1
 CROSS JOIN LATERAL
     ( SELECT jsonb_set_agg( ti1.fields
                           , ARRAY [(arr.ordinal - 1)::text, 'value']
                           , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date)
                           , false
                           ) AS res
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' IN ('startDate', 'endDate')
          AND arr.field ->> 'value' IS NOT NULL
     ) AS r
 WHERE ti.id = ti1.id
</code>```

Body:
I need to update data in two elements of JSON array. The example below is simplified, usually it has more elements wit other keys

[{"key": "startDate", "value": "2022-02-05T23:00:00Z"}, {"key": "endDate", "value": "2022-02-05T23:00:00Z"}]

My goal is to change 'value' in startDate and endDate

My query is

UPDATE my_table ti
SET fields = jsonb_set(ti.fields, path, temp, false)
FROM my_table ti1,
     LATERAL (
         SELECT ARRAY [(ordinal - 1)::text, 'value']                                               AS path,
                to_jsonb(((field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields #> '{}') WITH ORDINALITY arr(field, ordinal)
         WHERE field ->> 'key' = 'startDate'
           AND field ->> 'value' IS NOT NULL
         ) field
WHERE ti.id = ti1.id;

And the same query I do for endDate, just replacing WHERE condition.
So, I got two queries, but I want to replace it with one

I tried to rewrite WHERE condition to  WHERE field ->> 'key' in ('startDate', 'endDate), but it didn't work and as a result I got only one (the first) key value updated

[{"key": "startDate", "value": "2022-02-06"}, {"key": "endDate", "value": "2022-02-05T23:00:00Z"}]

How can I update two values in one query?

Body:
Solution 1

You need to call the jsonb_set function twice if you want to make two updates in the same jsonb data :

UPDATE my_table ti
   SET fields = jsonb_set(jsonb_set(ti1.fields, e.path, e.temp, false), s.path, s.temp, false)
  FROM my_table ti1
 CROSS JOIN LATERAL
     ( SELECT ARRAY [(arr.ordinal - 1)::text, 'value'] AS path
            , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' = 'startDate'
          AND arr.field ->> 'value' IS NOT NULL
     ) AS s
 CROSS JOIN LATERAL
     ( SELECT ARRAY [(arr.ordinal - 1)::text, 'value'] AS path
            , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' = 'endDate'
          AND arr.field ->> 'value' IS NOT NULL
     ) AS e
 WHERE ti.id = ti1.id

Solution 2

You can create an aggregate function jsonb_set_agg based on the jsonb_set function :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, b boolean)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$ SELECT jsonb_set(COALESCE(x, y), p, z, b) $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg (jsonb, text[], jsonb, boolean)
( SFUNC = jsonb_set
, STYPE = jsonb
) ;

Then you can iterate on the new aggregate function jsonb_set_agg :

UPDATE my_table AS ti
   SET fields = r.res
  FROM my_table AS ti1
 CROSS JOIN LATERAL
     ( SELECT jsonb_set_agg( ti1.fields
                           , ARRAY [(arr.ordinal - 1)::text, 'value']
                           , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date)
                           , false
                           ) AS res
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' IN ('startDate', 'endDate')
          AND arr.field ->> 'value' IS NOT NULL
     ) AS r
 WHERE ti.id = ti1.id