问:
PhP regex for a string, what would be the best way to do it?
I have an array with rule field that has a string like this:

```<code>FREQ=MONTHLY;BYDAY=3FR
FREQ=MONTHLY;BYDAY=3SA
FREQ=WEEKLY;UNTIL=20170728T080000Z;BYDAY=MO,TU,WE,TH,FR
FREQ=MONTHLY;UNTIL=20170527T100000Z;BYDAY=4SA
FREQ=WEEKLY;BYDAY=SA
FREQ=WEEKLY;INTERVAL=2;BYDAY=TH
FREQ=WEEKLY;BYDAY=TH
FREQ=WEEKLY;UNTIL=20170610T085959Z;BYDAY=SA
FREQ=MONTHLY;BYDAY=2TH
</code>```

Each line is a different array, I am giving a few clues to get an idea of what I need.

What I need is to write a regex that would take off all unnecessary values.

So, I don't need FREQ= ; BYDAY= etc. I basically need the values after = but each one I want to store in a different variable.

Taking third one as an example it would be:

```<code>$frequency = WEEKLY
$until = 20170728T080000Z
$day = MO, TU, WE, TH, FR
</code>```

It doesn't have to be necessarily one regex, there can be one regex for each value. So I have one for FREQ:

```<code>preg_match("/[^FREQ=][A-Z]+/", $input_line, $output_array);
</code>```

But I can't do it for the rest unfortunately, how can I solve this?

答:
Notice: For this problem, I recommend the generell approach @revo posted, it's concise and safe and easy on the eyes -- but keep in mind, that regular expressions come with a performance penalty compared to fixed string functions, so if you can use strpos/substr/explode/..., try to use them, don't 'knee-jerk' to a preg_-based solution.

Since the seperators are fixed and don't seem to occur in the values your are interested in, and you furthermore rely on knowledge of the keys (FREQ:, etc) you don't need regular-expressions (as much as  I like to use them anywhere I can, and you can use them here); why not simply explode and split in this case?

```<code>$lines = explode("\n", $text);
foreach($lines as $line) {
    $parts = explode(';', $line);
    $frequency = $until = $day = $interval = null;
    foreach($parts as $part) {
        list($key, $value) = explode('=', $part);
        switch($key) {
             case 'FREQ':
                   $frequency = $value;
                   break;
             case 'INTERVAL':
                   $interval = $value;
                   break;
              // and so on
         }
    }
    doSomethingWithTheValues();
}
</code>```

This may be more readable and efficient if your use-case is as simple as stated.

Body:
I have an array with rule field that has a string like this:

FREQ=MONTHLY;BYDAY=3FR
FREQ=MONTHLY;BYDAY=3SA
FREQ=WEEKLY;UNTIL=20170728T080000Z;BYDAY=MO,TU,WE,TH,FR
FREQ=MONTHLY;UNTIL=20170527T100000Z;BYDAY=4SA
FREQ=WEEKLY;BYDAY=SA
FREQ=WEEKLY;INTERVAL=2;BYDAY=TH
FREQ=WEEKLY;BYDAY=TH
FREQ=WEEKLY;UNTIL=20170610T085959Z;BYDAY=SA
FREQ=MONTHLY;BYDAY=2TH

Each line is a different array, I am giving a few clues to get an idea of what I need.

What I need is to write a regex that would take off all unnecessary values.

So, I don't need FREQ= ; BYDAY= etc. I basically need the values after = but each one I want to store in a different variable.

Taking third one as an example it would be:

$frequency = WEEKLY
$until = 20170728T080000Z
$day = MO, TU, WE, TH, FR

It doesn't have to be necessarily one regex, there can be one regex for each value. So I have one for FREQ:

preg_match("/[^FREQ=][A-Z]+/", $input_line, $output_array);

But I can't do it for the rest unfortunately, how can I solve this?

Body:
Notice: For this problem, I recommend the generell approach @revo posted, it's concise and safe and easy on the eyes -- but keep in mind, that regular expressions come with a performance penalty compared to fixed string functions, so if you can use strpos/substr/explode/..., try to use them, don't 'knee-jerk' to a preg_-based solution.

Since the seperators are fixed and don't seem to occur in the values your are interested in, and you furthermore rely on knowledge of the keys (FREQ:, etc) you don't need regular-expressions (as much as  I like to use them anywhere I can, and you can use them here); why not simply explode and split in this case?

$lines = explode("\n", $text);
foreach($lines as $line) {
    $parts = explode(';', $line);
    $frequency = $until = $day = $interval = null;
    foreach($parts as $part) {
        list($key, $value) = explode('=', $part);
        switch($key) {
             case 'FREQ':
                   $frequency = $value;
                   break;
             case 'INTERVAL':
                   $interval = $value;
                   break;
              // and so on
         }
    }
    doSomethingWithTheValues();
}

This may be more readable and efficient if your use-case is as simple as stated.


来源: stackexchange

问:
Unsupported type for timedelta
I'm trying to increase dates in the pandas dataframe by values contained in the other column of the same dataframe like this

```<code>loans['est_close_date'] = loans['dealdate'] + loans['tenor_weeks'].apply(lambda x: dt.timedelta(weeks =x))
</code>```

but I keep getting error as:

"unsupported type for timedelta weeks component: numpy.int64" error.

On the other hand, construction like this

```<code>loans['est_close_date'] = loans['dealdate'] + loans['tenor_weeks'].apply(lambda x: dt.timedelta(weeks =1)*x)
</code>```

works just fine. I can't understand what's wrong with the first approach.
There is no missing values in tenor_weeks column.

Thank you in advance!

ContentLicense: CC BY-SA 3.0

Title: Unsupported type for timedelta

Body:
I'm trying to increase dates in the pandas dataframe by values contained in the other column of the same dataframe like this

loans['est_close_date'] = loans['dealdate'] + loans['tenor_weeks'].apply(lambda x: dt.timedelta(weeks =x))

but I keep getting error as:

"unsupported type for timedelta weeks component: numpy.int64" error.

On the other hand, construction like this

loans['est_close_date'] = loans['dealdate'] + loans['tenor_weeks'].apply(lambda x: dt.timedelta(weeks =1)*x)

works just fine. I can't understand what's wrong with the first approach.
There is no missing values in tenor_weeks column.

Thank you in advance!

Body:
For me you solutions work perfectly, maybe necessary upgrade pandas/python.

I add pure pandas solution with to_timedelta:

rng = pd.date_range('2017-04-03', periods=10)
loans = pd.DataFrame({'dealdate': rng, 'tenor_weeks': range(1,11)})
print (loans)
    dealdate  tenor_weeks
0 2017-04-03            1
1 2017-04-04            2
2 2017-04-05            3
3 2017-04-06            4
4 2017-04-07            5
5 2017-04-08            6
6 2017-04-09            7
7 2017-04-10            8
8 2017-04-11            9
9 2017-04-12           10

loans['est_close_date'] = loans['dealdate'] + loans['tenor_weeks'].apply(lambda x: dt.timedelta(weeks =x))
loans['est_close_date1'] = loans['dealdate'] + loans['tenor_weeks'].apply(lambda x: dt.timedelta(weeks =1)*x)

loans['est_close_date2'] = loans['dealdate'] + pd.to_timedelta(loans['tenor_weeks'],unit='w')
print (loans)
    dealdate  tenor_weeks est_close_date est_close_date1 est_close_date2
0 2017-04-03            1     2017-04-10      2017-04-10      2017-04-10
1 2017-04-04            2     2017-04-18      2017-04-18      2017-04-18
2 2017-04-05            3     2017-04-26      2017-04-26      2017-04-26
3 2017-04-06            4     2017-05-04      2017-05-04      2017-05-04
4 2017-04-07            5     2017-05-12      2017-05-12      2017-05-12
5 2017-04-08            6     2017-05-20      2017-05-20      2017-05-20
6 2017-04-09            7     2017-05-28      2017-05-28      2017-05-28
7 2017-04-10            8     2017-06-05      2017-06-05      2017-06-05
8 2017-04-11            9     2017-06-13      2017-06-13      2017-06-13
9 2017-04-12           10     2017-06-21      2017-06-21      2017-06-21


问:
Efficient data/string reading and copy from file (CSV) c
I have read other posts regarding copying data from files. Let me show you why my case is different.
With C I must read 43 Million input lines from a csv file. The entries have no errors and are in this form:

```<code>int, int , int , variable length string with only alphanumerics and spaces , int \n
</code>```

Thing is I'm copying all data to memory in array and lists to do some very very simple averages on it and then outputting all processed data to a file, nothing fancy.
There are three main areas where I need help:

Regarding the string, (my BIGGEST problem is here) it is first read from the file, then is copied to an array and then passed to another functions which will copy it into dynamic memory only if a condition is met. Ex:

```<code>fileAnalizer(){
  while ( ! EOF ){
    char * s = function_to_read_string_from_file();
    data_to_array(s);
  }
  ....
  ....
  processData(arrays);
  dataToFiles(arrays);

}

void data_to_structures(char * s){
  if ( condition is met)
    char * aux = malloc((strlen(s)+1 )* sizeof(char));
    strcpy(aux,s);
  ....
  ...
}
</code>```

As you can see the string is traveled through 3 times. I'm needing a way to do this process more efficiently, traveling the string fewer times. I have tried reading char by char and counting the string length but the entire process becomes slower.

Efficient reading of input: would you recommend first copying all data into a buffer? If so: what type of buffer, and in many blocks or just one?
This is my current reading program:

```<code>void
csvReader(FILE* f){
    T_structCDT c;
    c.string = malloc(MAX_STRING_LENGHT*sizeof(char));
    while (fscanf(f,"%d,%d,%d,%[^,],%d\n",&c.a, &c.b, &c.vivienda, c.c, &c.d)==5 ){
        data_to_structures(c);
    }
}
</code>```

I then have nearly 500 hundred csv lines of processed data to dump in to other files. How would you recommend the dumping? Line by line or again sending the data to a buffer and then doing the dump? My code now looks something like this.

```<code>void dataToFiles(arrayOfStructs immenseAr1, arrayOfStructs immenseAr2){
  for (iteration over immenseAr1) {
      fprintf(f1, "%d,%d,%s\n", immenseAr1[i].a, immenseAr1[i].b, inmenseAr1[i].string);
  }
  for (iteration over immenseAr2) {
      fprintf(f2, "%d,%d,%s\n", inmenseAr2[i].a, inmenseAr2[i].b, inmenseAr2[i].string);
  }
}
</code>```

I must read all data before dumping it. Would you recommend a different method other than storing all data into memory and then analyzing it and then dumping all the analyzed data?
With the 20 Million lines the program is currently taking more than 40 seconds.
I really need to lower that time.

答:
Choose the right tool

With so much data (you speak of 43 Million input lines from a csv file), the harddisk I/O will be the bottleneck, and since you are dealing with flat text files, each time you need to do a different calculation (if you change your mind and want to do slightly different very very simple averages on it and then outputting all processed data to a file), you will need to go through all this process each time.

A better strategy would be to use a database management system, that is the proper tool for storing and processing large amounts of data, and will give you the flexibility of making whatever processing you need, with indexed data, efficient handling of memory and caching, etc., with simple SQL commands.

If you do not want to set-up a SQL server (such as MySQL or PostgreSQL), you can use a database management system that does not require a server, such as SQLite: http://www.sqlite.org/ which you can, in addition, drive from the command-line with the sqlite3 shell program, or from a C program if you wish (SQLite is actually a C library), or by using with a GUI interface, such as http://sqlitestudio.pl/

SQLite will allow you to create your Database, create your Table, import CSV files into it, do your calculations, and dump the results in a variety of formats,...

Walk-through example with SQLite

Here is an example to get you started, illustrating use of the sqlite3 shell program and C code.

Let us say you have your data in data1.csv, in the fomat you described, containing:

```<code>1,2,3,variable length string with only alphanumerics and spaces,5
11,22,33,other variable length string with only alphanumerics and spaces,55
111,222,333,yet another variable length string with only alphanumerics and spaces,555
</code>```

and in data2.csv containing:

```<code>2,3,4,from second batch variable length string with only alphanumerics and spaces,6
12,23,34,from second batch other variable length string with only alphanumerics and spaces,56
112,223,334,from second batch yet another variable length string with only alphanumerics and spaces,556
</code>```

You create the database with the sqlite3 command-line utility, create the table with the proper format, import the CSV files, and issue SQL commands like this:

```<code>$ sqlite3 bigdatabase.sqlite3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> create table alldata(col1 int, col2 int, col3 int, col4 varchar(255), col5 int);
sqlite> .mode csv
sqlite> .import data1.csv alldata
sqlite> .import data2.csv alldata
sqlite> select * from alldata;
1,2,3,"variable length string with only alphanumerics and spaces",5
11,22,33,"other variable length string with only alphanumerics and spaces",55
111,222,333,"yet another variable length string with only alphanumerics and spaces",555
2,3,4,"from second batch variable length string with only alphanumerics and spaces",6
12,23,34,"from second batch other variable length string with only alphanumerics and spaces",56
112,223,334,"from second batch yet another variable length string with only alphanumerics and spaces",556
sqlite> select avg(col2) from alldata;
82.5
sqlite>
</code>```

(press Ctrl-D to exit the SQLite shell)

Above, we have created a bigdatabase.sqlite3 file containing the created database handled by SQLite, a table alldata, we imported the CSV data into it, displayed the data contained in it (don't do that on 43 Million lines), and (calculated and) displayed the average of the integers contained in the column we named col2, which happens to be the second column.

You can use the created SQLite database with C and the SQLite library, to achieve the same.

Create a sqlite-average.c file (adapted from the example found in SQLite's Quickstart Page) like this:

```<code>#include <stdio.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for(i=0; i<argc; i++){
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main(void) {
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;

    rc = sqlite3_open("bigdatabase.sqlite3", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    rc = sqlite3_exec(db, "select avg(col2) from alldata;", callback, 0, &zErrMsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    sqlite3_close(db);

    return 0;
}
</code>```

compile it, linking to the installed SQLite library, with gcc you do like this:

```<code>$ gcc -Wall sqlite-average.c -lsqlite3
</code>```

run the compiled executable:

```<code>$ ./a.out
avg(col2) = 82.5

$
</code>```

You will likely want to create indexes for columns where you look for data, for instance for columns 2 and 5 in this table, so that information is fetched quicker there:

```<code>sqlite> create index alldata_idx ON alldata(col2,col5);
</code>```

Decide, if applicable, what column will contain the primary key for the table, etc.

For more information, check:

http://www.sqlite.org/quickstart.html

http://www.sqlite.org/cli.html

http://www.sqlite.org/docs.html

http://www.sqlite.org/c3ref/intro.html

Body:
I have read other posts regarding copying data from files. Let me show you why my case is different.
With C I must read 43 Million input lines from a csv file. The entries have no errors and are in this form:

int, int , int , variable length string with only alphanumerics and spaces , int \n

Thing is I'm copying all data to memory in array and lists to do some very very simple averages on it and then outputting all processed data to a file, nothing fancy.
There are three main areas where I need help:

Regarding the string, (my BIGGEST problem is here) it is first read from the file, then is copied to an array and then passed to another functions which will copy it into dynamic memory only if a condition is met. Ex:

fileAnalizer(){
  while ( ! EOF ){
    char * s = function_to_read_string_from_file();
    data_to_array(s);
  }
  ....
  ....
  processData(arrays);
  dataToFiles(arrays);

}

void data_to_structures(char * s){
  if ( condition is met)
    char * aux = malloc((strlen(s)+1 )* sizeof(char));
    strcpy(aux,s);
  ....
  ...
}

As you can see the string is traveled through 3 times. I'm needing a way to do this process more efficiently, traveling the string fewer times. I have tried reading char by char and counting the string length but the entire process becomes slower.

Efficient reading of input: would you recommend first copying all data into a buffer? If so: what type of buffer, and in many blocks or just one?
This is my current reading program:

void
csvReader(FILE* f){
    T_structCDT c;
    c.string = malloc(MAX_STRING_LENGHT*sizeof(char));
    while (fscanf(f,"%d,%d,%d,%[^,],%d\n",&c.a, &c.b, &c.vivienda, c.c, &c.d)==5 ){
        data_to_structures(c);
    }
}

I then have nearly 500 hundred csv lines of processed data to dump in to other files. How would you recommend the dumping? Line by line or again sending the data to a buffer and then doing the dump? My code now looks something like this.

void dataToFiles(arrayOfStructs immenseAr1, arrayOfStructs immenseAr2){
  for (iteration over immenseAr1) {
      fprintf(f1, "%d,%d,%s\n", immenseAr1[i].a, immenseAr1[i].b, inmenseAr1[i].string);
  }
  for (iteration over immenseAr2) {
      fprintf(f2, "%d,%d,%s\n", inmenseAr2[i].a, inmenseAr2[i].b, inmenseAr2[i].string);
  }
}

I must read all data before dumping it. Would you recommend a different method other than storing all data into memory and then analyzing it and then dumping all the analyzed data?
With the 20 Million lines the program is currently taking more than 40 seconds.
I really need to lower that time.

Body:
Choose the right tool

With so much data (you speak of 43 Million input lines from a csv file), the harddisk I/O will be the bottleneck, and since you are dealing with flat text files, each time you need to do a different calculation (if you change your mind and want to do slightly different very very simple averages on it and then outputting all processed data to a file), you will need to go through all this process each time.

A better strategy would be to use a database management system, that is the proper tool for storing and processing large amounts of data, and will give you the flexibility of making whatever processing you need, with indexed data, efficient handling of memory and caching, etc., with simple SQL commands.

If you do not want to set-up a SQL server (such as MySQL or PostgreSQL), you can use a database management system that does not require a server, such as SQLite: http://www.sqlite.org/ which you can, in addition, drive from the command-line with the sqlite3 shell program, or from a C program if you wish (SQLite is actually a C library), or by using with a GUI interface, such as http://sqlitestudio.pl/

SQLite will allow you to create your Database, create your Table, import CSV files into it, do your calculations, and dump the results in a variety of formats,...

Walk-through example with SQLite

Here is an example to get you started, illustrating use of the sqlite3 shell program and C code.

Let us say you have your data in data1.csv, in the fomat you described, containing:

1,2,3,variable length string with only alphanumerics and spaces,5
11,22,33,other variable length string with only alphanumerics and spaces,55
111,222,333,yet another variable length string with only alphanumerics and spaces,555

and in data2.csv containing:

2,3,4,from second batch variable length string with only alphanumerics and spaces,6
12,23,34,from second batch other variable length string with only alphanumerics and spaces,56
112,223,334,from second batch yet another variable length string with only alphanumerics and spaces,556

You create the database with the sqlite3 command-line utility, create the table with the proper format, import the CSV files, and issue SQL commands like this:

$ sqlite3 bigdatabase.sqlite3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> create table alldata(col1 int, col2 int, col3 int, col4 varchar(255), col5 int);
sqlite> .mode csv
sqlite> .import data1.csv alldata
sqlite> .import data2.csv alldata
sqlite> select * from alldata;
1,2,3,"variable length string with only alphanumerics and spaces",5
11,22,33,"other variable length string with only alphanumerics and spaces",55
111,222,333,"yet another variable length string with only alphanumerics and spaces",555
2,3,4,"from second batch variable length string with only alphanumerics and spaces",6
12,23,34,"from second batch other variable length string with only alphanumerics and spaces",56
112,223,334,"from second batch yet another variable length string with only alphanumerics and spaces",556
sqlite> select avg(col2) from alldata;
82.5
sqlite>

(press Ctrl-D to exit the SQLite shell)

Above, we have created a bigdatabase.sqlite3 file containing the created database handled by SQLite, a table alldata, we imported the CSV data into it, displayed the data contained in it (don't do that on 43 Million lines), and (calculated and) displayed the average of the integers contained in the column we named col2, which happens to be the second column.

You can use the created SQLite database with C and the SQLite library, to achieve the same.

Create a sqlite-average.c file (adapted from the example found in SQLite's Quickstart Page) like this:

#include <stdio.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for(i=0; i<argc; i++){
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main(void) {
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;

    rc = sqlite3_open("bigdatabase.sqlite3", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    rc = sqlite3_exec(db, "select avg(col2) from alldata;", callback, 0, &zErrMsg);
    if (rc!=SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    sqlite3_close(db);

    return 0;
}

compile it, linking to the installed SQLite library, with gcc you do like this:

$ gcc -Wall sqlite-average.c -lsqlite3

run the compiled executable:

$ ./a.out
avg(col2) = 82.5

$

You will likely want to create indexes for columns where you look for data, for instance for columns 2 and 5 in this table, so that information is fetched quicker there:

sqlite> create index alldata_idx ON alldata(col2,col5);

Decide, if applicable, what column will contain the primary key for the table, etc.

For more information, check:

http://www.sqlite.org/quickstart.html

http://www.sqlite.org/cli.html

http://www.sqlite.org/docs.html

http://www.sqlite.org/c3ref/intro.html


问:
fread. fseek. ftell. " warning: value computed is not used "
how to instead of

```<code>char text[10000];
</code>```

do

```<code>char text[fp_len];
</code>```

i also get this warning:

warning: value computed is not used

```<code>FILE *fp;
fp = fopen(path, "r");
int fp_len;
fseek(fp, 0, SEEK_END)+1;
fp_len = ftell(fp);
fseek(fp, 0, SEEK_SET);
printf("%d---", fp_len);

char text[10000];
fread(text, fp_len, 1, fp);

printf("%s", text);
</code>```

should i also add:

```<code>text[]= '\0'
</code>```

to the bottom. ( above printf )

答:
as noted in comments fseek(fp, 0, SEEK_END)+1 is just adding the return code of fseek to 1 (why would you do that?), and the result (useless) is dropped.

Now, you could create a variable length array (perfectly legal in C) using char text[fp_len];, that would work, but generally auto variable space is not designed to be big, so you can have "stack overflow" if the file is too big: in that case char *text = malloc(fp_len) is preferred.

If you want to null-terminate it (which is useful only if you want to printf the contents of the file, and if the file contains text), don't forget to add 1 to your buffer so you can legally do text[fp_len] = '\0';

Aside: fseek(fp, 0, SEEK_SET); could be just rewind(fp);

Body:
how to instead of

char text[10000];

do

char text[fp_len];

i also get this warning:

warning: value computed is not used

FILE *fp;
fp = fopen(path, "r");
int fp_len;
fseek(fp, 0, SEEK_END)+1;
fp_len = ftell(fp);
fseek(fp, 0, SEEK_SET);
printf("%d---", fp_len);

char text[10000];
fread(text, fp_len, 1, fp);

printf("%s", text);

should i also add:

text[]= '\0'

to the bottom. ( above printf )


问:
Reinstalling NuGet packages with NuGet installed as VS Extension
I would like to be able to install all of the NuGet packages in the packages.config, as per The NuGet docs. NuGet is installed as a VS Extension, and I can't seem to find nuget.exe. Is it possible to run:

nuget i packages.config -o Packages

Without maintaining a seperate copy of nuget.exe on a per project basis?

答:
A much easier option that you can keep enabled in Visual Studio during development to ease off your NuGet package installation related pain.

Keep both below mentioned options under NuGet Package Manager > General in checked state -

Allow NuGet to download missing packages

Automatically check for missing packages during build in Visual Studio

Have a look at the screenshot below:

ContentLicense: CC BY-SA 4.0


问:
In VScode editor shows wrong Intellisense for Javascript
I use Javascript but Intellisense gave me a lot of Typescript interface options.
How can I stop Intellisense for Typescript interface when I'm working in Javascript?

For reference see this snapshot.

答:
Those examples are standard DOM objects. To disable these, create a jsconfig.json at the root of you workspace that only include the standard es6 type definitions:

```<code>{
    "compilerOptions": {
        "lib": ["es6"]
    }
}
</code>```

However, there also was a bug in VSCode <=1.13 that caused interface suggestions to show in js files: https://github.com/Microsoft/TypeScript/issues/15750

This should be fixed in VSCode 1.14+. Please let us know if you still see this issue: https://github.com/Microsoft/vscode/issues/new

Body:
Those examples are standard DOM objects. To disable these, create a jsconfig.json at the root of you workspace that only include the standard es6 type definitions:

{
    "compilerOptions": {
        "lib": ["es6"]
    }
}

However, there also was a bug in VSCode <=1.13 that caused interface suggestions to show in js files: https://github.com/Microsoft/TypeScript/issues/15750

This should be fixed in VSCode 1.14+. Please let us know if you still see this issue: https://github.com/Microsoft/vscode/issues/new


问:
Case Statement Not Working - VBA
In my project I have a list of clients that have unique commission calculations (ex. Client_1 has a 0.1% commission rate, whereas Client_2 has a 0.25% commission rate).

In my VBA code I made special cases, that vary by the client_id. The problem I'm getting with this code is that even though I put in a special ID, it jumps over the case (ex. if klienta_nr = 100, it skips over the Case klienta_nr = 100 and returns the Case Else). How can I fix this issue?

```<code>Private Sub CommandButton1_Click()

Dim klienta_nr As Long
Dim ISIN As String
Dim Cena As Double
Dim Skaits As Double
Dim Komisija As Double
Dim vk As String

Set kSheet = ThisWorkbook.Sheets("komisijas")

klienta_nr = Range("B2").Value
ISIN = Range("E2").Value
Cena = Range("H2").Value
Skaits = Range("I2").Value
vk = Range("B2").Value

Select Case klienta_nr

Case klienta_nr = 100

            If klienta_nr = 100 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
                Komisija = (Cena * Skaits) * 0.01
                Range("K2").Value = Komisija
                End If
            If klienta_nr = 100 And Komisija <= 30 Then
                Range("K2").Value = 30
                End If

            'Case where klient is special, but ISIN doesn't apply
            If klienta_nr = 100 And (Left(ISIN, 2) <> "DE" Or Left(ISIN, 2) <> "FR" Or Left(ISIN, 2) <> "NL" Or Left(ISIN, 2) <> "IT" Or Left(ISIN, 2) <> "IE") Then
                Komisija = (Cena * Skaits) * 0.003
                If Komisija >= 40 Then
                    Range("K2").Value = 40
                    End If
            End If

 Case klienta_nr = 105

            If klienta_nr = 105 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
                Komisija = (Cena * Skaits) * 0.01
                Range("K2").Value = Komisija
                End If
            'Set 30 EUR Min
            If klienta_nr = 105 And Komisija <= 30 Then
                Range("K2").Value = 30
                End If

    'End If

 Case klienta_nr = 110

            If klienta_nr = 110 And (Left(ISIN, 2) = "NO" Or Left(ISIN, 2) = "SE" Or Left(ISIN, 2) = "DK" Or Left(ISIN, 2) = "FI" Or Left(ISIN, 2) = "IS" Or Left(ISIN, 2) = "LT" Or Left(ISIN, 2) = "EE" Or Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE" Or Left(ISIN, 2) = "AT" Or Left(ISIN, 2) = "BE" Or Left(ISIN, 2) = "ES" Or Left(ISIN, 2) = "PT") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 110 And (Left(ISIN, 2) = "US") Then
                Komisija = (Cena * Skaits) * 0.002
                End If

            If klienta_nr = 110 And (Left(ISIN, 2) = "UK") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 110 And (Left(ISIN, 2) = "CH") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If
            'Set 20 [valūte] MIN
            If klienta_nr = 110 And Komisija <= 20 Then
                Range("K2").Value = 20
                End If

Case klienta_nr = 115

            If klienta_nr = 115 And (Left(ISIN, 2) = "NO" Or Left(ISIN, 2) = "SE" Or Left(ISIN, 2) = "DK" Or Left(ISIN, 2) = "FI" Or Left(ISIN, 2) = "IS" Or Left(ISIN, 2) = "LT" Or Left(ISIN, 2) = "EE" Or Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE" Or Left(ISIN, 2) = "AT" Or Left(ISIN, 2) = "BE" Or Left(ISIN, 2) = "ES" Or Left(ISIN, 2) = "PT") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 115 And (Left(ISIN, 2) = "US") Then
                Komisija = (Cena * Skaits) * 0.002
                End If

            If klienta_nr = 115 And (Left(ISIN, 2) = "UK") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 115 And (Left(ISIN, 2) = "CH") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If
            'Set 20 [valūte] MIN
            If klienta_nr = 115 And Komisija <= 20 Then
                Range("K2").Value = 20
                End If

 Case klienta_nr = 120

            If klienta_nr = 120 And (Left(ISIN, 2) = "US") Then
                Komisija = (Cena * Skaits) * 0.0027
                Range("K2").Value = Komisija
                End If
            'Set 40 USD MIN
            If klienta_nr = 120 And Komisija <= 40 Then
                Range("K2").Value = 40
                End If

'Non-special klient cases
Case Else
    If Not Application.Match(klienta_nr, kSheet.Range("A2:A100")) Then

         If Right(vk, 1) = 1 Or Right(vk, 1) = 8 Then
            Komisija = (Cena * Skaits) * 0.003
            Range("K2").Value = Komisija
            End If

        If Right(vk, 1) = 7 Then
            Komisija = (Cena * Skaits) * 0.01
            Range("K2").Value = Komisija
            End If
        'Komisija MAX is 40, so anything >=40 equals 40
        If Komisija >= 40 Then
            Range("K2").Value = 40
            End If
    End If
   End Select
  End Sub
</code>```

答:
Select Case only needs criteria in

```<code>Select Case klienta_nr
</code>```

And not in the Case part as below:

Change

```<code>Case klienta_nr = 105
</code>```

to

```<code>Case 105
</code>```

Title: Case Statement Not Working - VBA

Body:
In my project I have a list of clients that have unique commission calculations (ex. Client_1 has a 0.1% commission rate, whereas Client_2 has a 0.25% commission rate).

In my VBA code I made special cases, that vary by the client_id. The problem I'm getting with this code is that even though I put in a special ID, it jumps over the case (ex. if klienta_nr = 100, it skips over the Case klienta_nr = 100 and returns the Case Else). How can I fix this issue?

Private Sub CommandButton1_Click()

Dim klienta_nr As Long
Dim ISIN As String
Dim Cena As Double
Dim Skaits As Double
Dim Komisija As Double
Dim vk As String

Set kSheet = ThisWorkbook.Sheets("komisijas")

klienta_nr = Range("B2").Value
ISIN = Range("E2").Value
Cena = Range("H2").Value
Skaits = Range("I2").Value
vk = Range("B2").Value

Select Case klienta_nr

Case klienta_nr = 100

            If klienta_nr = 100 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
                Komisija = (Cena * Skaits) * 0.01
                Range("K2").Value = Komisija
                End If
            If klienta_nr = 100 And Komisija <= 30 Then
                Range("K2").Value = 30
                End If

            'Case where klient is special, but ISIN doesn't apply
            If klienta_nr = 100 And (Left(ISIN, 2) <> "DE" Or Left(ISIN, 2) <> "FR" Or Left(ISIN, 2) <> "NL" Or Left(ISIN, 2) <> "IT" Or Left(ISIN, 2) <> "IE") Then
                Komisija = (Cena * Skaits) * 0.003
                If Komisija >= 40 Then
                    Range("K2").Value = 40
                    End If
            End If

 Case klienta_nr = 105

            If klienta_nr = 105 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
                Komisija = (Cena * Skaits) * 0.01
                Range("K2").Value = Komisija
                End If
            'Set 30 EUR Min
            If klienta_nr = 105 And Komisija <= 30 Then
                Range("K2").Value = 30
                End If

    'End If

 Case klienta_nr = 110

            If klienta_nr = 110 And (Left(ISIN, 2) = "NO" Or Left(ISIN, 2) = "SE" Or Left(ISIN, 2) = "DK" Or Left(ISIN, 2) = "FI" Or Left(ISIN, 2) = "IS" Or Left(ISIN, 2) = "LT" Or Left(ISIN, 2) = "EE" Or Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE" Or Left(ISIN, 2) = "AT" Or Left(ISIN, 2) = "BE" Or Left(ISIN, 2) = "ES" Or Left(ISIN, 2) = "PT") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 110 And (Left(ISIN, 2) = "US") Then
                Komisija = (Cena * Skaits) * 0.002
                End If

            If klienta_nr = 110 And (Left(ISIN, 2) = "UK") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 110 And (Left(ISIN, 2) = "CH") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If
            'Set 20 [valūte] MIN
            If klienta_nr = 110 And Komisija <= 20 Then
                Range("K2").Value = 20
                End If

Case klienta_nr = 115

            If klienta_nr = 115 And (Left(ISIN, 2) = "NO" Or Left(ISIN, 2) = "SE" Or Left(ISIN, 2) = "DK" Or Left(ISIN, 2) = "FI" Or Left(ISIN, 2) = "IS" Or Left(ISIN, 2) = "LT" Or Left(ISIN, 2) = "EE" Or Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE" Or Left(ISIN, 2) = "AT" Or Left(ISIN, 2) = "BE" Or Left(ISIN, 2) = "ES" Or Left(ISIN, 2) = "PT") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 115 And (Left(ISIN, 2) = "US") Then
                Komisija = (Cena * Skaits) * 0.002
                End If

            If klienta_nr = 115 And (Left(ISIN, 2) = "UK") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If

            If klienta_nr = 115 And (Left(ISIN, 2) = "CH") Then
                Komisija = (Cena * Skaits) * 0.002
                Range("K2").Value = Komisija
                End If
            'Set 20 [valūte] MIN
            If klienta_nr = 115 And Komisija <= 20 Then
                Range("K2").Value = 20
                End If

 Case klienta_nr = 120

            If klienta_nr = 120 And (Left(ISIN, 2) = "US") Then
                Komisija = (Cena * Skaits) * 0.0027
                Range("K2").Value = Komisija
                End If
            'Set 40 USD MIN
            If klienta_nr = 120 And Komisija <= 40 Then
                Range("K2").Value = 40
                End If

'Non-special klient cases
Case Else
    If Not Application.Match(klienta_nr, kSheet.Range("A2:A100")) Then

         If Right(vk, 1) = 1 Or Right(vk, 1) = 8 Then
            Komisija = (Cena * Skaits) * 0.003
            Range("K2").Value = Komisija
            End If

        If Right(vk, 1) = 7 Then
            Komisija = (Cena * Skaits) * 0.01
            Range("K2").Value = Komisija
            End If
        'Komisija MAX is 40, so anything >=40 equals 40
        If Komisija >= 40 Then
            Range("K2").Value = 40
            End If
    End If
   End Select
  End Sub

Body:
Select Case only needs criteria in

Select Case klienta_nr

And not in the Case part as below:

Change

Case klienta_nr = 105

to

Case 105


问:
BackgroundWorker and instance variables
One thing that's always confused me is how a BackgroundWorker seems to have thread-safe access to the instance variables of the surrounding class.

Given a basic class:

```<code>public class BackgroundProcessor
{
    public List<int> Items { get; private set; }

    public BackgroundProcessor(IEnumerable<int> items)
    {
        Items = new List<int>(items);
    }

    public void DoWork()
    {
        BackgroundWorker worker = new BackgroundWorker();
        worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
        worker.DoWork += new DoWorkEventHandler(worker_DoWork);
        worker.RunWorkerAsync();
    }

    void worker_DoWork(object sender, DoWorkEventArgs e)
    {
        var processor = new ProcessingClass();

        processor.Process(this.Items); //Accessing the instance variable
    }

    void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        //Stuff goes here
    }
}
</code>```

Am I erroneous in my assumption the the call to processor.Process(this.Points); is a thread-safe call? How don't I get a cross-thread access violation?

I'm sure it's obvious, but it always has confused me.

答:
The background worker is a simple way of starting off a process on a different thread, it does not take care of thread safety for you.

If you are simply reading variables that have been changed by the background worker, this is fine.

ContentLicense: CC BY-SA 2.5

Title: BackgroundWorker and instance variables

Body:
One thing that's always confused me is how a BackgroundWorker seems to have thread-safe access to the instance variables of the surrounding class.

Given a basic class:

public class BackgroundProcessor
{
    public List<int> Items { get; private set; }

    public BackgroundProcessor(IEnumerable<int> items)
    {
        Items = new List<int>(items);
    }

    public void DoWork()
    {
        BackgroundWorker worker = new BackgroundWorker();
        worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
        worker.DoWork += new DoWorkEventHandler(worker_DoWork);
        worker.RunWorkerAsync();
    }

    void worker_DoWork(object sender, DoWorkEventArgs e)
    {
        var processor = new ProcessingClass();

        processor.Process(this.Items); //Accessing the instance variable
    }

    void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        //Stuff goes here
    }
}

Am I erroneous in my assumption the the call to processor.Process(this.Points); is a thread-safe call? How don't I get a cross-thread access violation?

I'm sure it's obvious, but it always has confused me.


问:
MySql get one record from two identical tables with a check
I want to get same records from two identical tables but with a check.

I have following two tables that are identical :

table_1

```<code>id  | enc   | first_name | last_name | address | city
1   | 1001  | John       | Doe       | abc     |
</code>```

table_2

```<code>id  | enc   | first_name | last_name | address | city
1   | 1001  |            | Doe       |         | xyz
</code>```

I want to get record from table_2 but since first_name and address are empty it should the from record table_1
But again city is empty in table_1 so it would get the record from table_2

I've tried the union like this :

```<code>(SELECT * from `table_1` where `id` = `1)
UNION
(SELECT * from `table_2` where `id` = `1)
</code>```

But its providing both the records from the table. I want only one record using both the table like this :

```<code>id  | enc   | first_name | last_name | address | city
1   | 1001  | John       | Doe       | abc     | xyz
</code>```

答:
```<code>SELECT table_2.id,table_2.enc,table_1.first_name,table_2.last_name,table_1.address,table_2.city
FROM table_2,table_1
WHERE table_2.id=table_1.id
AND table_2.id=1;
</code>```

Body:
I want to get same records from two identical tables but with a check.

I have following two tables that are identical :

table_1

id  | enc   | first_name | last_name | address | city
1   | 1001  | John       | Doe       | abc     |

table_2

id  | enc   | first_name | last_name | address | city
1   | 1001  |            | Doe       |         | xyz

I want to get record from table_2 but since first_name and address are empty it should the from record table_1
But again city is empty in table_1 so it would get the record from table_2

I've tried the union like this :

(SELECT * from `table_1` where `id` = `1)
UNION
(SELECT * from `table_2` where `id` = `1)

But its providing both the records from the table. I want only one record using both the table like this :

id  | enc   | first_name | last_name | address | city
1   | 1001  | John       | Doe       | abc     | xyz


问:
Run code after ValueEventLister has retrieved the firebase database
Im using firebase authUI in order to login/register users in my app, the thing is that I want to know when the user registers for the 1st time and when he is a returning user who just logs in. I thought that it would be a good idea to "see" in my DB if the current UID exists, if it is the user exists and if it doesnt exists its a new user.

Below is the code which works fine but the the ValueEventLister "call" is asynchronous and when it checks for the user UID it already exists because it was just written in DB but in reality its a new user. How should I modify my code in order to work as expected?

```<code>    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        super.onActivityResult(requestCode, resultCode, data);

        if (requestCode == RC_SIGN_IN) {

            final FirebaseUser signedUser = mAuth.getCurrentUser();

            ValueEventListener postListener = new ValueEventListener() {
                @Override
                public void onDataChange(DataSnapshot dataSnapshot) {
                    Log.d(TAG, "onDataChange: " + dataSnapshot.child(getString(R.string.users)).child(signedUser.getUid()).exists());
                }

                @Override
                public void onCancelled(DatabaseError databaseError) {
                    // Getting Post failed, log a message
                    Log.w(TAG, "loadPost:onCancelled", databaseError.toException());
                }
            };

            mDatabase.addListenerForSingleValueEvent(postListener);

//            uid = setNullToDefaultValue(signedUser.getUid());
//            displayName = setNullToDefaultValue(signedUser.getDisplayName());
//            email = setNullToDefaultValue(signedUser.getEmail());
//            photoUrl = setNullToDefaultValue(signedUser.getPhotoUrl().toString());
//            provider = setNullToDefaultValue(signedUser.getProviderId());
//            phoneNumber = setNullToDefaultValue(signedUser.getPhoneNumber());
//
//            Log.d(TAG, "onActivityResult: User Signed In!");
//            User mUser = new User(uid, displayName, email, photoUrl, provider, phoneNumber);
//            Map<String, Object> userMap = mUser.toMap();
//            mDatabase.child(getString(R.string.users)).child(mAuth.getCurrentUser().getUid()).updateChildren(userMap);
        }
    }
</code>```

I commented to code in order to see if the "check" works and it does.

答:
The FirebaseAuth class has separate methods for creating an account and for signing into an account. You can just add an onCompleteListener to the method to execute your code whenever the respective method is called. For example, for authentication via email, you can use the following :-

```<code> mAuth.createUserWithEmailAndPassword(email,password).addOnCompleteListener(new OnCompleteListener<AuthResult>() {
        @Override
        public void onComplete(@NonNull Task<AuthResult> task) {

        }
    });
</code>```

and

```<code>mAuth.signInWithEmailAndPassword(email,password).addOnCompleteListener(new OnCompleteListener<AuthResult>() {
       @Override
       public void onComplete(@NonNull Task<AuthResult> task) {

       }
   });
</code>```

Besides these, if your user stays signed in between app sessions, then you can simply use auth state listeners.

Body:
Im using firebase authUI in order to login/register users in my app, the thing is that I want to know when the user registers for the 1st time and when he is a returning user who just logs in. I thought that it would be a good idea to "see" in my DB if the current UID exists, if it is the user exists and if it doesnt exists its a new user.

Below is the code which works fine but the the ValueEventLister "call" is asynchronous and when it checks for the user UID it already exists because it was just written in DB but in reality its a new user. How should I modify my code in order to work as expected?

    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        super.onActivityResult(requestCode, resultCode, data);

        if (requestCode == RC_SIGN_IN) {

            final FirebaseUser signedUser = mAuth.getCurrentUser();

            ValueEventListener postListener = new ValueEventListener() {
                @Override
                public void onDataChange(DataSnapshot dataSnapshot) {
                    Log.d(TAG, "onDataChange: " + dataSnapshot.child(getString(R.string.users)).child(signedUser.getUid()).exists());
                }

                @Override
                public void onCancelled(DatabaseError databaseError) {
                    // Getting Post failed, log a message
                    Log.w(TAG, "loadPost:onCancelled", databaseError.toException());
                }
            };

            mDatabase.addListenerForSingleValueEvent(postListener);

//            uid = setNullToDefaultValue(signedUser.getUid());
//            displayName = setNullToDefaultValue(signedUser.getDisplayName());
//            email = setNullToDefaultValue(signedUser.getEmail());
//            photoUrl = setNullToDefaultValue(signedUser.getPhotoUrl().toString());
//            provider = setNullToDefaultValue(signedUser.getProviderId());
//            phoneNumber = setNullToDefaultValue(signedUser.getPhoneNumber());
//
//            Log.d(TAG, "onActivityResult: User Signed In!");
//            User mUser = new User(uid, displayName, email, photoUrl, provider, phoneNumber);
//            Map<String, Object> userMap = mUser.toMap();
//            mDatabase.child(getString(R.string.users)).child(mAuth.getCurrentUser().getUid()).updateChildren(userMap);
        }
    }

I commented to code in order to see if the "check" works and it does.

Body:
The FirebaseAuth class has separate methods for creating an account and for signing into an account. You can just add an onCompleteListener to the method to execute your code whenever the respective method is called. For example, for authentication via email, you can use the following :-

 mAuth.createUserWithEmailAndPassword(email,password).addOnCompleteListener(new OnCompleteListener<AuthResult>() {
        @Override
        public void onComplete(@NonNull Task<AuthResult> task) {

        }
    });

and

mAuth.signInWithEmailAndPassword(email,password).addOnCompleteListener(new OnCompleteListener<AuthResult>() {
       @Override
       public void onComplete(@NonNull Task<AuthResult> task) {

       }
   });

Besides these, if your user stays signed in between app sessions, then you can simply use auth state listeners.


答:
You need to use the Pattern

```<code>;?[A-Z]+=
</code>```

together with preg_split();

```<code>preg_split('/;?[A-Z]+=/', $str);
</code>```

Explanation

```<code>;       match Semikolon
?       no or one of the last Character
[A-Z]+  match one or more uppercase Letters
=       match one =
</code>```

If you want to have each Line into a seperate Array, you should do it this Way:

```<code> # split each Line into an Array-Element
 $lines = preg_split('/[\n\r]+/', $str);

 # initiate Array for Results
 $results = array();

 # start Looping trough Lines
 foreach($lines as $line){

  # split each Line by the Regex mentioned above and
  # put the resulting Array into the Results-Array
  $results[] = preg_split('/;?[A-Z]+=/', $line);

 }
</code>```

Title: PhP regex for a string, what would be the best way to do it?

Body:
You need to use the Pattern

;?[A-Z]+=

together with preg_split();

preg_split('/;?[A-Z]+=/', $str);

Explanation

;       match Semikolon
?       no or one of the last Character
[A-Z]+  match one or more uppercase Letters
=       match one =

If you want to have each Line into a seperate Array, you should do it this Way:

 # split each Line into an Array-Element
 $lines = preg_split('/[\n\r]+/', $str);

 # initiate Array for Results
 $results = array();

 # start Looping trough Lines
 foreach($lines as $line){

  # split each Line by the Regex mentioned above and
  # put the resulting Array into the Results-Array
  $results[] = preg_split('/;?[A-Z]+=/', $line);

 }


问:
How can i pass other data in other function in my controller to view using CI
how can i pass a value from my query to the same view in CI. I am having a situation of like this:

```<code>public function index() {
  $myinfo = $this->Myprofile_model->mydata();
  $data['myinfo'] = $myinfo
  $this->load->view('header');
  $this->load->view('myprofile',$data);
  $this->load->view('footer');
}

public function getResults($id) {
 $fetchdata = $this->Myprofile_model->fetchresult($id);
 $data['userselect'] = $fetchdata;
 $this->load->view('myprofile',$data);
}
</code>```

i already load my wholepage in index. and now i want to make another function to my button which is the getResults($id) function in my controller, and i want to display my another data from database to the same page. When i do the top code, my view is duplicating, because i loaded the $this->load->view('myprofile',$data); twice.

答:
1: Create helper function and use that function on view page.

```<code>   OR
</code>```

If you want records for single id then merge both function in one.
If there is many ids and you want to fetch records on click for each id then you can use ajax.

```<code>$( document ).ready(function() {
        $('.class_name').click(function(){
            // place your code here to fetch records.
            // on success you can display the result on same page
        })
    });
</code>```

Body:
how can i pass a value from my query to the same view in CI. I am having a situation of like this:

public function index() {
  $myinfo = $this->Myprofile_model->mydata();
  $data['myinfo'] = $myinfo
  $this->load->view('header');
  $this->load->view('myprofile',$data);
  $this->load->view('footer');
}

public function getResults($id) {
 $fetchdata = $this->Myprofile_model->fetchresult($id);
 $data['userselect'] = $fetchdata;
 $this->load->view('myprofile',$data);
}

i already load my wholepage in index. and now i want to make another function to my button which is the getResults($id) function in my controller, and i want to display my another data from database to the same page. When i do the top code, my view is duplicating, because i loaded the $this->load->view('myprofile',$data); twice.

Body:
1: Create helper function and use that function on view page.

   OR

If you want records for single id then merge both function in one.
If there is many ids and you want to fetch records on click for each id then you can use ajax.

$( document ).ready(function() {
        $('.class_name').click(function(){
            // place your code here to fetch records.
            // on success you can display the result on same page
        })
    });


问:
small problem in shifting range please help me out
I have small concept problem.

i am working on graph.

consider i hav a point 32 between range 28 and 35

and i have to bring all the points between the range 28 and 35 within range 1 and 2.

how to calculate it?

actually i ll have the point 32.
and i have to shift it between 1 and 2...

please help me out.

In other words,

if 32 is between 28 and 35
    what is 32 in range 1 and 2

答:
I think it is: 1 + [(number - 28) / (35 - 28)], for example for 32 is (1 + 4/7) = 1.57...

and in general if you want move it within [a,b]:

```<code>a + (b-a) * [(number - 28) / (35 - 28)]
</code>```

Body:
I think it is: 1 + [(number - 28) / (35 - 28)], for example for 32 is (1 + 4/7) = 1.57...

and in general if you want move it within [a,b]:

a + (b-a) * [(number - 28) / (35 - 28)]


问:
How to swap value in SQL Server?
I want to swap the value between the row but I don't have an idea how to make it as I create a script to do it but the answer still not correct. I get the data raw in from the pdf then convert it into excel 'xls'.

Please see the sample below.

```<code>DECLARE @MyTable TABLE(Id INT, RoomNo INT, Class VARCHAR(10), Price VARCHAR(255))

INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 1,    21,     'A',        '43,028,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 2,    NULL,   NULL,       '43,896,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 3,    20,     'A',        '42,681,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 4,    NULL,   NULL,       '43,549,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 5,    19,     'A',        '42,334,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 6,    NULL,   NULL,       '43,202,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 7,    18,     'A',        '41,987,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 8,    NULL,   NULL,       '42,855,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 9,    17,     'A',        '41,467,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 10,   NULL,   NULL,       '42,334,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 11,   16,     'A',        '41,120,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 12,   NULL,   NULL,       '41,987,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 13,   NULL,   'A',        '39,211,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 14,   9,      NULL,       '40,079,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 15,   NULL,   NULL,       '38,691,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 16,   6,      NULL,       '39,385,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 17,   NULL,   'A',        '44,756,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 18,   5,      NULL,       '45,591,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 19,   21,     'B',        '26,598,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 20,   20,     'B',        '26,393,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 21,   NULL,   NULL,       '26,905,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 22,   19,     'B',        '26,189,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 23,   NULL,   NULL,       '26,700,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 24,   18,     'B',        '25,984,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 25,   NULL,   NULL,       '26,496,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 26,   17,     'B',        '25,677,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 27,   NULL,   'B',        '24,041,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 28,   6,      NULL,       '24,552,000'
</code>```

what i tried

```<code>SELECT
        *,
        ISNULL(RoomNo, (SELECT TOP 1 RoomNo FROM @MyTable WHERE Id < MyTable.Id AND RoomNo IS NOT NULL ORDER BY ID DESC)),
        ISNULL(Class, (SELECT TOP 1 Class FROM @MyTable WHERE Id < MyTable.Id AND Class IS NOT NULL ORDER BY ID DESC)),
        Price
    FROM @MyTable as MyTable
</code>```

Result is wrong:

```<code>Id  RoomNo  Class   Price       NewRoomNo   NewClass    Price
1   21      A       43,028,000  21          A           43,028,000
2   NULL    NULL    43,896,000  21          A           43,896,000
3   20      A       42,681,000  20          A           42,681,000
4   NULL    NULL    43,549,000  20          A           43,549,000
5   19      A       42,334,000  19          A           42,334,000
6   NULL    NULL    43,202,000  19          A           43,202,000
7   18      A       41,987,000  18          A           41,987,000
8   NULL    NULL    42,855,000  18          A           42,855,000
9   17      A       41,467,000  17          A           41,467,000
10  NULL    NULL    42,334,000  17          A           42,334,000
11  16      A       41,120,000  16          A           41,120,000
12  NULL    NULL    41,987,000  16          A           41,987,000
13  NULL    A       39,211,000  16          A           39,211,000
14  9       NULL    40,079,000  9           A           40,079,000
15  NULL    NULL    38,691,000  9           A           38,691,000
16  6       NULL    39,385,000  6           A           39,385,000
17  NULL    A       44,756,000  6           A           44,756,000
18  5       NULL    45,591,000  5           A           45,591,000
19  21      B       26,598,000  21          B           26,598,000
20  20      B       26,393,000  20          B           26,393,000
21  NULL    NULL    26,905,000  20          B           26,905,000
22  19      B       26,189,000  19          B           26,189,000
23  NULL    NULL    26,700,000  19          B           26,700,000
24  18      B       25,984,000  18          B           25,984,000
25  NULL    NULL    26,496,000  18          B           26,496,000
26  17      B       25,677,000  17          B           25,677,000
27  NULL    B       24,041,000  17          B           24,041,000
28  6       NULL    24,552,000  6           B           24,552,000
</code>```

I want to achieve:

```<code>Id  RoomNo  Class   Price       NewRoomNo   NewClass    Price
1   21      A       43,028,000  NULL        NULL        43,028,000
2   NULL    NULL    43,896,000  21          A           43,896,000
3   20      A       42,681,000  NULL        NULL        42,681,000
4   NULL    NULL    43,549,000  20          A           43,549,000
5   19      A       42,334,000  NULL        NULL        42,334,000
6   NULL    NULL    43,202,000  19          A           43,202,000
7   18      A       41,987,000  NULL        NULL        41,987,000
8   NULL    NULL    42,855,000  18          A           42,855,000
9   17      A       41,467,000  NULL        NULL        41,467,000
10  NULL    NULL    42,334,000  17          A           42,334,000
11  16      A       41,120,000  NULL        NULL        41,120,000
12  NULL    NULL    41,987,000  16          A           41,987,000
13  NULL    A       39,211,000  NULL        NULL        39,211,000
14  9       NULL    40,079,000  9           A           40,079,000
15  NULL    NULL    38,691,000  NULL        NULL        38,691,000
16  6       NULL    39,385,000  6           A           39,385,000
17  NULL    A       44,756,000  NULL        NULL        44,756,000
18  5       NULL    45,591,000  5           A           45,591,000
19  21      B       26,598,000  21          B           26,598,000
20  20      B       26,393,000  NULL        NULL        26,393,000
21  NULL    NULL    26,905,000  20          B           26,905,000
22  19      B       26,189,000  NULL        NULL        26,189,000
23  NULL    NULL    26,700,000  19          B           26,700,000
24  18      B       25,984,000  NULL        NULL        25,984,000
25  NULL    NULL    26,496,000  18          B           26,496,000
26  17      B       25,677,000  17          B           25,677,000
27  NULL    B       24,041,000  NULL        NULL        24,041,000
28  6       NULL    24,552,000  6           B           24,552,000
</code>```

答:
Using LEAD and LAG , I came up into this.

```<code>            ;WITH    CTE
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(( LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(Class, 1) OVER ( ORDER BY id ) IS NULL
                                                ), NULL, IIF(LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(Class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(RoomNo,
                                                                                1) OVER ( ORDER BY id ), NULL)) NewRoomNo ,
                                            IIF(( LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(Class, 1) OVER ( ORDER BY id ) IS NULL
                                                ), NULL, IIF(LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(Class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(Class,
                                                                                1) OVER ( ORDER BY id ), NULL)) NewClass ,
                                            price NewPrice
                                   FROM     @MyTable
                                 ),
                            CTE2
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND Class IS NULL
                                            AND LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, RoomNo, NewRoomNo) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND Class IS NULL
                                            AND LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(class,
                                                                                1) OVER ( ORDER BY id ), NewClass) NewClass ,
                                            NewPrice
                                   FROM     cte
                                 ),
                            CTE3
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(( RoomNo IS NOT NULL
                                                  AND Class IS NULL
                                                  AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL
                                                ), RoomNo, NewRoom) NewRoom ,
                                            IIF(( RoomNo IS NOT NULL
                                                  AND Class IS NULL
                                                  AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL
                                                ), LEAD(class, 1) OVER ( ORDER BY id ), NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE2
                                 ),
                            CTE4
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NULL, RoomNo, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NULL, Class, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE3
                                 ),
                            CTE5
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL, RoomNo, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL, Class, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE4
                                 ),
                            CTE6
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(roomNo IS NULL
                                            AND class IS NOT NULL
                                            AND LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, NULL, newRoom) newRoom ,
                                            IIF(roomNo IS NULL
                                            AND class IS NOT NULL
                                            AND LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, NULL, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE5
                                 ),
                            CTE7
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS  NULL, NULL, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS  NULL, NULL, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE6
                                 )
                        SELECT  id ,
                                roomNo ,
                                Class ,
                                price ,
                                IIF(LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                AND LAG(Class, 1) OVER ( ORDER BY id ) IS NULL
                                AND roomno IS NOT NULL
                                AND class IS NOT NULL, roomno, NewRoom) NewRoom ,
                                IIF(LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                AND LAG(Class, 1) OVER ( ORDER BY id ) IS NULL
                                AND roomno IS NOT NULL
                                AND class IS NOT NULL, Class, NewClass) NewClass ,
                                NewPrice price
                        FROM    CTE7
</code>```

RESULT:

```<code>                id          roomNo      Class      price           NewRoom     NewClass   price
                ----------- ----------- ---------- --------------- ----------- ---------- ------------------
                1           21          A          43,028,000      NULL        NULL       43,028,000
                2           NULL        NULL       43,896,000      21          A          43,896,000
                3           20          A          42,681,000      NULL        NULL       42,681,000
                4           NULL        NULL       43,549,000      20          A          43,549,000
                5           19          A          42,334,000      NULL        NULL       42,334,000
                6           NULL        NULL       43,202,000      19          A          43,202,000
                7           18          A          41,987,000      NULL        NULL       41,987,000
                8           NULL        NULL       42,855,000      18          A          42,855,000
                9           17          A          41,467,000      NULL        NULL       41,467,000
                10          NULL        NULL       42,334,000      17          A          42,334,000
                11          16          A          41,120,000      NULL        NULL       41,120,000
                12          NULL        NULL       41,987,000      16          A          41,987,000
                13          NULL        A          39,211,000      NULL        NULL       39,211,000
                14          9           NULL       40,079,000      9           A          40,079,000
                15          NULL        NULL       38,691,000      NULL        NULL       38,691,000
                16          6           NULL       39,385,000      6           A          39,385,000
                17          NULL        A          44,756,000      NULL        NULL       44,756,000
                18          5           NULL       45,591,000      5           A          45,591,000
                19          21          B          26,598,000      21          B          26,598,000
                20          20          B          26,393,000      NULL        NULL       26,393,000
                21          NULL        NULL       26,905,000      20          B          26,905,000
                22          19          B          26,189,000      NULL        NULL       26,189,000
                23          NULL        NULL       26,700,000      19          B          26,700,000
                24          18          B          25,984,000      NULL        NULL       25,984,000
                25          NULL        NULL       26,496,000      18          B          26,496,000
                26          17          B          25,677,000      17          B          25,677,000
                27          NULL        B          24,041,000      NULL        NULL       24,041,000
                28          6           NULL       24,552,000      6           B          24,552,000

                (28 row(s) affected)
</code>```

Title: How to swap value in SQL Server?

Body:
I want to swap the value between the row but I don't have an idea how to make it as I create a script to do it but the answer still not correct. I get the data raw in from the pdf then convert it into excel 'xls'.

Please see the sample below.

DECLARE @MyTable TABLE(Id INT, RoomNo INT, Class VARCHAR(10), Price VARCHAR(255))

INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 1,    21,     'A',        '43,028,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 2,    NULL,   NULL,       '43,896,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 3,    20,     'A',        '42,681,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 4,    NULL,   NULL,       '43,549,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 5,    19,     'A',        '42,334,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 6,    NULL,   NULL,       '43,202,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 7,    18,     'A',        '41,987,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 8,    NULL,   NULL,       '42,855,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 9,    17,     'A',        '41,467,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 10,   NULL,   NULL,       '42,334,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 11,   16,     'A',        '41,120,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 12,   NULL,   NULL,       '41,987,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 13,   NULL,   'A',        '39,211,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 14,   9,      NULL,       '40,079,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 15,   NULL,   NULL,       '38,691,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 16,   6,      NULL,       '39,385,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 17,   NULL,   'A',        '44,756,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 18,   5,      NULL,       '45,591,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 19,   21,     'B',        '26,598,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 20,   20,     'B',        '26,393,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 21,   NULL,   NULL,       '26,905,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 22,   19,     'B',        '26,189,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 23,   NULL,   NULL,       '26,700,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 24,   18,     'B',        '25,984,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 25,   NULL,   NULL,       '26,496,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 26,   17,     'B',        '25,677,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 27,   NULL,   'B',        '24,041,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 28,   6,      NULL,       '24,552,000'

what i tried

SELECT
        *,
        ISNULL(RoomNo, (SELECT TOP 1 RoomNo FROM @MyTable WHERE Id < MyTable.Id AND RoomNo IS NOT NULL ORDER BY ID DESC)),
        ISNULL(Class, (SELECT TOP 1 Class FROM @MyTable WHERE Id < MyTable.Id AND Class IS NOT NULL ORDER BY ID DESC)),
        Price
    FROM @MyTable as MyTable

Result is wrong:

Id  RoomNo  Class   Price       NewRoomNo   NewClass    Price
1   21      A       43,028,000  21          A           43,028,000
2   NULL    NULL    43,896,000  21          A           43,896,000
3   20      A       42,681,000  20          A           42,681,000
4   NULL    NULL    43,549,000  20          A           43,549,000
5   19      A       42,334,000  19          A           42,334,000
6   NULL    NULL    43,202,000  19          A           43,202,000
7   18      A       41,987,000  18          A           41,987,000
8   NULL    NULL    42,855,000  18          A           42,855,000
9   17      A       41,467,000  17          A           41,467,000
10  NULL    NULL    42,334,000  17          A           42,334,000
11  16      A       41,120,000  16          A           41,120,000
12  NULL    NULL    41,987,000  16          A           41,987,000
13  NULL    A       39,211,000  16          A           39,211,000
14  9       NULL    40,079,000  9           A           40,079,000
15  NULL    NULL    38,691,000  9           A           38,691,000
16  6       NULL    39,385,000  6           A           39,385,000
17  NULL    A       44,756,000  6           A           44,756,000
18  5       NULL    45,591,000  5           A           45,591,000
19  21      B       26,598,000  21          B           26,598,000
20  20      B       26,393,000  20          B           26,393,000
21  NULL    NULL    26,905,000  20          B           26,905,000
22  19      B       26,189,000  19          B           26,189,000
23  NULL    NULL    26,700,000  19          B           26,700,000
24  18      B       25,984,000  18          B           25,984,000
25  NULL    NULL    26,496,000  18          B           26,496,000
26  17      B       25,677,000  17          B           25,677,000
27  NULL    B       24,041,000  17          B           24,041,000
28  6       NULL    24,552,000  6           B           24,552,000

I want to achieve:

Id  RoomNo  Class   Price       NewRoomNo   NewClass    Price
1   21      A       43,028,000  NULL        NULL        43,028,000
2   NULL    NULL    43,896,000  21          A           43,896,000
3   20      A       42,681,000  NULL        NULL        42,681,000
4   NULL    NULL    43,549,000  20          A           43,549,000
5   19      A       42,334,000  NULL        NULL        42,334,000
6   NULL    NULL    43,202,000  19          A           43,202,000
7   18      A       41,987,000  NULL        NULL        41,987,000
8   NULL    NULL    42,855,000  18          A           42,855,000
9   17      A       41,467,000  NULL        NULL        41,467,000
10  NULL    NULL    42,334,000  17          A           42,334,000
11  16      A       41,120,000  NULL        NULL        41,120,000
12  NULL    NULL    41,987,000  16          A           41,987,000
13  NULL    A       39,211,000  NULL        NULL        39,211,000
14  9       NULL    40,079,000  9           A           40,079,000
15  NULL    NULL    38,691,000  NULL        NULL        38,691,000
16  6       NULL    39,385,000  6           A           39,385,000
17  NULL    A       44,756,000  NULL        NULL        44,756,000
18  5       NULL    45,591,000  5           A           45,591,000
19  21      B       26,598,000  21          B           26,598,000
20  20      B       26,393,000  NULL        NULL        26,393,000
21  NULL    NULL    26,905,000  20          B           26,905,000
22  19      B       26,189,000  NULL        NULL        26,189,000
23  NULL    NULL    26,700,000  19          B           26,700,000
24  18      B       25,984,000  NULL        NULL        25,984,000
25  NULL    NULL    26,496,000  18          B           26,496,000
26  17      B       25,677,000  17          B           25,677,000
27  NULL    B       24,041,000  NULL        NULL        24,041,000
28  6       NULL    24,552,000  6           B           24,552,000

Body:
Using LEAD and LAG , I came up into this.

            ;WITH    CTE
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(( LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(Class, 1) OVER ( ORDER BY id ) IS NULL
                                                ), NULL, IIF(LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(Class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(RoomNo,
                                                                                1) OVER ( ORDER BY id ), NULL)) NewRoomNo ,
                                            IIF(( LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(Class, 1) OVER ( ORDER BY id ) IS NULL
                                                ), NULL, IIF(LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(Class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(Class,
                                                                                1) OVER ( ORDER BY id ), NULL)) NewClass ,
                                            price NewPrice
                                   FROM     @MyTable
                                 ),
                            CTE2
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND Class IS NULL
                                            AND LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, RoomNo, NewRoomNo) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND Class IS NULL
                                            AND LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(class,
                                                                                1) OVER ( ORDER BY id ), NewClass) NewClass ,
                                            NewPrice
                                   FROM     cte
                                 ),
                            CTE3
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(( RoomNo IS NOT NULL
                                                  AND Class IS NULL
                                                  AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL
                                                ), RoomNo, NewRoom) NewRoom ,
                                            IIF(( RoomNo IS NOT NULL
                                                  AND Class IS NULL
                                                  AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL
                                                ), LEAD(class, 1) OVER ( ORDER BY id ), NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE2
                                 ),
                            CTE4
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NULL, RoomNo, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NULL, Class, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE3
                                 ),
                            CTE5
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL, RoomNo, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL, Class, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE4
                                 ),
                            CTE6
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(roomNo IS NULL
                                            AND class IS NOT NULL
                                            AND LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, NULL, newRoom) newRoom ,
                                            IIF(roomNo IS NULL
                                            AND class IS NOT NULL
                                            AND LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, NULL, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE5
                                 ),
                            CTE7
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS  NULL, NULL, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS  NULL, NULL, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE6
                                 )
                        SELECT  id ,
                                roomNo ,
                                Class ,
                                price ,
                                IIF(LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                AND LAG(Class, 1) OVER ( ORDER BY id ) IS NULL
                                AND roomno IS NOT NULL
                                AND class IS NOT NULL, roomno, NewRoom) NewRoom ,
                                IIF(LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                AND LAG(Class, 1) OVER ( ORDER BY id ) IS NULL
                                AND roomno IS NOT NULL
                                AND class IS NOT NULL, Class, NewClass) NewClass ,
                                NewPrice price
                        FROM    CTE7

RESULT:

                id          roomNo      Class      price           NewRoom     NewClass   price
                ----------- ----------- ---------- --------------- ----------- ---------- ------------------
                1           21          A          43,028,000      NULL        NULL       43,028,000
                2           NULL        NULL       43,896,000      21          A          43,896,000
                3           20          A          42,681,000      NULL        NULL       42,681,000
                4           NULL        NULL       43,549,000      20          A          43,549,000
                5           19          A          42,334,000      NULL        NULL       42,334,000
                6           NULL        NULL       43,202,000      19          A          43,202,000
                7           18          A          41,987,000      NULL        NULL       41,987,000
                8           NULL        NULL       42,855,000      18          A          42,855,000
                9           17          A          41,467,000      NULL        NULL       41,467,000
                10          NULL        NULL       42,334,000      17          A          42,334,000
                11          16          A          41,120,000      NULL        NULL       41,120,000
                12          NULL        NULL       41,987,000      16          A          41,987,000
                13          NULL        A          39,211,000      NULL        NULL       39,211,000
                14          9           NULL       40,079,000      9           A          40,079,000
                15          NULL        NULL       38,691,000      NULL        NULL       38,691,000
                16          6           NULL       39,385,000      6           A          39,385,000
                17          NULL        A          44,756,000      NULL        NULL       44,756,000
                18          5           NULL       45,591,000      5           A          45,591,000
                19          21          B          26,598,000      21          B          26,598,000
                20          20          B          26,393,000      NULL        NULL       26,393,000
                21          NULL        NULL       26,905,000      20          B          26,905,000
                22          19          B          26,189,000      NULL        NULL       26,189,000
                23          NULL        NULL       26,700,000      19          B          26,700,000
                24          18          B          25,984,000      NULL        NULL       25,984,000
                25          NULL        NULL       26,496,000      18          B          26,496,000
                26          17          B          25,677,000      17          B          25,677,000
                27          NULL        B          24,041,000      NULL        NULL       24,041,000
                28          6           NULL       24,552,000      6           B          24,552,000

                (28 row(s) affected)


问:
git server side 'update' hook in bitbucket
I'm looking for way to create a git server side push hook (will be used to enforce referencing JIRA ticket number in each commit message).

According to pro git - git hooks, the git hook to use on the server side is 'update', which exists on .git/hooks dir.
Since I'm using BitBucket for git hosting, I need to somehow send this file to the .git/hooks dir in the site.

Using BitBucket services will not be suitable, since they are 'post-receive' hooks, so there's no point in using existing or writing my own broker, therefore this question doesn't give me a proper solution.

答:
Unfortunately, Bitbucket Server doesn't provide an API or UI to install an update hook. It only supports pre-receive and post-receive hooks on that level. However, since Bitbucket Server uses a generic git at the backend, it is indeed possible to install any of the standard git hooks.

This approach requires filesystem access with admin/root rights to the host where Bitbucket Server is installed.

The hooks are located in shared/config/git/template/hooks and are automatically copied inside per-repository hooks directory when you create a new repository. To install a custom hook for an existing repository, do that in shared/data/repositories/<repo_number>/hooks.

I have created a ready to use solution for validating the commit log against a configurable JIRA host using a configurable username and password. The solution verifies the following:

Conformance to Linux kernel style log formatting (50/72 rule)

Presence of a JIRA issue key in the summary

JIRA issue assignee's e-mail matching the committer's e-mail

JIRA issue status being 'In Progress'

You can download the files here:
https://github.com/AlexanderAmelkin/bitbucket-git-hooks-for-jira

For portability, the main validation script is written in Javascript for node.js that is available both for Windows and Linux.