23/8/23

[chriltola]

for table event first i want to check isActive of it and then get event_id and branch.

on 'SELECT amount FROM tblevent WHERE branch = @branchValue' i want to check with the event_id because if we check with branch, it may get wrong event. pls make change and provide the full code

[chriltola]

Certainly! Here's the modified script based on your requirements:

DECLARE @event_id INT;
DECLARE @branchValue VARCHAR(500);
DECLARE @branchPrefix VARCHAR(3);
DECLARE @eventAmount FLOAT; -- Adjust data type if necessary
-- ... (declare other variables)

-- Select active events
DECLARE cur CURSOR FOR
SELECT event_id, branch FROM tblevent WHERE isActive = 1;

OPEN cur;
FETCH NEXT FROM cur INTO @event_id, @branchValue;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

    -- Get event amount based on event_id
    SELECT @eventAmount = amount FROM tblevent WHERE event_id = @event_id;

    -- Query tbltrxn based on branch and event amount
    INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
    SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
    FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix
    AND AMT = @eventAmount;

    FETCH NEXT FROM cur INTO @event_id, @branchValue;
END;

CLOSE cur;
DEALLOCATE cur;

This script fetches active events from tblevent, gets the event amount based on the event_id, and then queries tbltrxn based on branches and the corresponding event amount, inserting the matching records into the temp table.

Please test this script thoroughly in a controlled environment before applying it to your production database.

[chriltola]

when input value insteat of @branchPrefix as '011' as below

SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, TX_Currency, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIFBRID, AMT, PCT_ID, PCT, PPT, PCTTYPE FROM tbltrxn WHERE SUBSTRING(TXNUM, 1, 3) = '011'

it work and why?

[chriltola]

DECLARE @event_id INT; DECLARE @branchValue VARCHAR(500); DECLARE @branchPrefix VARCHAR(3); DECLARE @eventAmount VARCHAR(MAX); -- Adjusted data type -- ... (declare other variables)

-- Select active events DECLARE cur CURSOR FOR SELECT event_id, branch FROM tblevent WHERE isActive = 1;

OPEN cur; FETCH NEXT FROM cur INTO @event_id, @branchValue;

WHILE @@FETCH_STATUS = 0 BEGIN SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

-- Get event amount based on event_id
SELECT @eventAmount = amount FROM tblevent WHERE event_id = @event_id;

-- Query tbltrxn based on branch and event amount
INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
FROM tbltrxn
WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix
AND AMT = @eventAmount;

FETCH NEXT FROM cur INTO @event_id, @branchValue;

END;

CLOSE cur; DEALLOCATE cur;

[chriltola]

Thank you for clarifying. If the query is running without errors but no rows are affected, it indicates that there might be a mismatch between the values you're trying to match in the WHERE clause. Let's troubleshoot step by step:

1. Check Data Types: Make sure that the data types of TXNUM and AMT columns match in both tables (tbltrxn and tblevent).

2. Trim Leading/Trailing Spaces: It's possible that the values in the TXNUM column have leading or trailing spaces. Try using the TRIM function to remove any spaces:

WHERE TRIM(SUBSTRING(TXNUM, 1, 3)) = @branchPrefix

3. Debugging: To understand the issue better, try printing out the values you're working with. Add PRINT statements to see what values are being used in your script. For example:

PRINT 'Branch Prefix: ' + @branchPrefix;
PRINT 'Event Amount: ' + @eventAmount;

4. Verify Data: Double-check that there are records in the tbltrxn table that match both the branch prefix and the event amount conditions.

5. Test with Specific Values: To narrow down the issue, try using specific values for @branchPrefix and @eventAmount that you know should match records in the tbltrxn table.

6. Debugging Techniques: You can also use tools like SQL Server Profiler or Extended Events to trace the queries being executed and check for any unexpected behavior.

If you've covered all these points and the issue still persists, it might be beneficial to have someone with SQL expertise directly review your database setup and data to identify the root cause.

[chriltola]

the result (0 rows affected) Branch Prefix: 010 Event Amount: 1000.00

for the branch it have more value on it. why when i print it display only one. in tblevent branch have value 010,011. note: branch value can be more as 010,011,012,020,......

[chriltola]

Thank you for providing more details. It seems like the issue might be related to the way you're iterating through the branch values. The loop you've shared earlier only fetches a single branch value and uses it to filter data from tbltrxn. In order to process multiple branch values, you need to iterate through each branch value and perform the required actions.

Here's how you can modify the code to process all the branch values and insert matching rows from tbltrxn:

DECLARE @event_id INT;
DECLARE @branchValue VARCHAR(500);
DECLARE @eventAmount VARCHAR(MAX); -- Adjusted data type
-- ... (declare other variables)

-- Select active events
DECLARE cur CURSOR FOR
SELECT event_id, branch FROM tblevent WHERE isActive = 1;

OPEN cur;
FETCH NEXT FROM cur INTO @event_id, @branchValue;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

    -- Get event amount based on event_id
    SELECT @eventAmount = amount FROM tblevent WHERE event_id = @event_id;

    -- Query tbltrxn based on branch and event amount
    INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
    SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
    FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix
    AND AMT = @eventAmount;

    FETCH NEXT FROM cur INTO @event_id, @branchValue;
END;

CLOSE cur;
DEALLOCATE cur;

This loop will iterate through all the active events in tblevent, retrieve the corresponding branch and event amount, and then insert matching rows from tbltrxn into the temp table. It will perform this process for each active event.

Please ensure that you are calling this code correctly within your SQL Server environment, as this script assumes you are executing it within SQL Server Management Studio or a similar tool.

[chriltola]

I apologize for the confusion. If the loop is not iterating correctly for each branch, let's revise the code to ensure the loop works as expected:

DECLARE @event_id INT;
DECLARE @branchValue VARCHAR(500);
DECLARE @eventAmount VARCHAR(MAX); -- Adjusted data type
-- ... (declare other variables)

-- Create a temporary table to store active events
CREATE TABLE #ActiveEvents (
    event_id INT,
    branch VARCHAR(500)
);

-- Insert active events into the temporary table
INSERT INTO #ActiveEvents (event_id, branch)
SELECT event_id, branch FROM tblevent WHERE isActive = 1;

-- Loop through active events and process branches
DECLARE cur CURSOR FOR
SELECT event_id, branch FROM #ActiveEvents;

OPEN cur;
FETCH NEXT FROM cur INTO @event_id, @branchValue;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

    -- Get event amount based on event_id
    SELECT @eventAmount = amount FROM tblevent WHERE event_id = @event_id;

    -- Query tbltrxn based on branch and event amount
    INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
    SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
    FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix
    AND AMT = @eventAmount;

    FETCH NEXT FROM cur INTO @event_id, @branchValue;
END;

CLOSE cur;
DEALLOCATE cur;

-- Clean up temporary table
DROP TABLE #ActiveEvents;

In this version, I've added a temporary table #ActiveEvents to store the active events. This should ensure that the loop iterates over each active event and processes the corresponding branches correctly. Once the loop is completed, the temporary table is dropped to clean up.

Please try running this version of the code and let me know if it works as intended.

[chriltola]

-- Get all active event IDs DECLARE @activeEventIDs VARCHAR(MAX); SELECT @activeEventIDs = COALESCE(@activeEventIDs + ',', '') + CAST(event_id AS VARCHAR) FROM tblevent WHERE isActive = 1;

-- Query to insert data into temp and delete from tbltrxn INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIFBRID, AMT, PCT_ID, PCT, PPT, PCTTYPE) SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIFBRID, AMT, PCT_ID, PCT, PPT, PCTTYPE FROM tbltrxn WHERE SUBSTRING(TXNUM, 1, 3) IN (SELECT branch FROM tblevent WHERE event_id IN (@activeEventIDs)) AND AMT IN (SELECT amount FROM tblevent WHERE event_id IN (@activeEventIDs));

-- Delete data from tbltrxn after insertion into temp DELETE FROM tbltrxn WHERE SUBSTRING(TXNUM, 1, 3) IN (SELECT branch FROM tblevent WHERE event_id IN (@activeEventIDs)) AND AMT IN (SELECT amount FROM tblevent WHERE event_id IN (@activeEventIDs));

[chriltola]

To create an automatic script that runs every 1 minute in PHP, you can use a combination of PHP and a system tool like cron jobs (on Unix-like systems) or Task Scheduler (on Windows). Here's how you can set it up:

Using Cron Jobs (Unix-like systems):

1. Open your terminal.

2. Type crontab -e to open the cron table for editing.

3. Add a new line to run your PHP script every minute. For example:

   * * * * * /usr/bin/php /path/to/your/script.php

   This runs the script located at /path/to/your/script.php using the PHP interpreter every minute.

4. Save the file and exit the editor.

Using Task Scheduler (Windows):

1. Open the Start menu and search for "Task Scheduler."

2. Click on "Create Basic Task" or "Create Task," depending on your version of Windows.

3. Follow the wizard to set up the task:

   • Choose a name and description for the task.
   • Select "Daily" trigger.
   • Choose "Repeat task every" and set it to 1 minute.
   • Select "Start a program" action.
   • Browse and select the PHP executable (usually php.exe) in the "Program/script" field.
   • In the "Add arguments (optional)" field, enter the path to your PHP script, e.g., C:\path\to\your\script.php.

4. Complete the wizard and save the task.

Keep in mind that running a script every minute can potentially put a strain on your system's resources, especially if the script performs resource-intensive tasks. Make sure your script is efficient and lightweight. Additionally, consider implementing mechanisms to prevent the script from overlapping if it takes longer than 1 minute to execute.

Remember to replace /path/to/your/script.php or C:\path\to\your\script.php with the actual path to your PHP script.

[chriltola]

<?php $logFilePath = 'output.log';

// Capture the current standard output $originalStdout = fopen('php://stdout', 'w');

// Open the log file for writing $logFile = fopen($logFilePath, 'a');

if ($logFile === false) { die("Error opening log file."); }

// Redirect standard output to the log file stream_filter_append($logFile, 'php://output', 'w');

// Your echo statements echo "This is a log message.\n"; echo "Another log message.\n";

// Restore original standard output fclose($logFile); fclose($originalStdout); ?>

[chriltola]

<?php $logFilePath = 'output.log';

// Capture the current standard output $originalStdout = fopen('php://stdout', 'w');

// Open the log file for writing $logFile = fopen($logFilePath, 'a');

if ($logFile === false) { die("Error opening log file."); }

// Redirect standard output to the log file stream_filter_append($logFile, 'php://output', 'w');

// Your echo statements echo "This is a log message.\n"; echo "Another log message.\n";

// Restore original standard output fclose($logFile); fclose($originalStdout); ?>

[chriltola]

$logFilePath = 'outpu.log';

// Capture the current standard output $originalStdout = fopen('php://stdout', 'w');

// Open the log file for writing $logFile = fopen($logFilePath, 'a');

if ($logFile === false) { die("Error opening log file."); }

// Redirect standard output to the log file stream_filter_append($logFile, 'php://output', 'w');

// Your echo statements echo "This is a log message.\n"; echo "Another log message.\n";

// Restore original standard output fclose($logFile); fclose($originalStdout); ?>

[chriltola]

Warning: stream_filter_append() expects parameter 3 to be integer, string given in C:\AppServ\www\project point\testing.php on line 15 This is a log message. Another log message.

[chriltola]

$logFilePath = 'output.log';

// Start output buffering ob_start();

// Your echo statements echo "This is a log message.\n"; echo "Another log message.\n";

// Get the buffered output $output = ob_get_clean();

// Append the output to the log file file_put_contents($logFilePath, $output, FILE_APPEND);

// Display a message if needed echo "Output logged to $logFilePath"; ?>

[chriltola]

If your PHP script produces different results when run through a scheduled task compared to running it manually, there might be a few reasons for this behavior. Here are some common factors to consider:

1. Working Directory: Scheduled tasks might run from a different working directory than when you run the script manually. Make sure your script's relative paths and file references are correctly defined to work regardless of the working directory.

2. Environment Variables: Scripts can rely on environment variables that are set when you run them manually, but these might not be available or set differently when run as a scheduled task. Ensure that any required environment variables are properly configured for the scheduled task.

3. User Permissions: The user account running the scheduled task might have different permissions compared to your user account. This could affect file access, database connections, and more. Ensure the scheduled task user has the necessary permissions.

4. Execution Time Limit: Some environments, including scheduled tasks, might have execution time limits that could impact the script's execution. Check if the script's execution time is being restricted by the task scheduler.

5. Resource Availability: Scheduled tasks might have different resource availability compared to running manually. This could impact memory usage, CPU resources, and other factors that affect script execution.

6. Output and Error Handling: Make sure your script handles errors and outputs correctly. When run through a scheduled task, errors might not be displayed on the screen, so ensure your script is logging errors or sending output to a log file.

7. Dependencies: If your script relies on external files or services, ensure they are accessible when the script runs as a scheduled task.

8. Concurrency and Timing: If the script interacts with resources like databases or files, ensure there are no conflicts or race conditions when multiple instances of the script run simultaneously due to scheduling.

To troubleshoot the issue, you might consider adding detailed logging and error handling throughout your script. This will help you identify where the differences in behavior occur and provide insight into the underlying problem. Additionally, testing the script in an environment that more closely mimics the conditions of the scheduled task can help you identify and address any discrepancies.

[chriltola]

this is the php code that i want to check into sql script

// Query enabled events from tblevent table $sql = "SELECT * FROM tblevent WHERE isActive = 1"; $query = sqlsrv_query($conn, $sql);

if ($query === false) { die(print_r(sqlsrv_errors(), true)); }

// Loop through enabled events and perform actions while ($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) { $branchValues = explode(',', $row['branch']);

// Loop through each branch value and create and execute queries
foreach ($branchValues as $branchValue) {
    $branchPrefix = substr($branchValue, 0, 3);
    $trxQuery = "SELECT * FROM tbltrxn WHERE SUBSTRING(TXNUM,1, 3) = '$branchPrefix'";

    echo $trxQuery;
    $trxResult = sqlsrv_query($conn, $trxQuery);

    if ($trxResult === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    // Perform actions based on the query result for each branch value
    while ($trxRow = sqlsrv_fetch_array($trxResult, SQLSRV_FETCH_ASSOC)) {
        // Perform desired actions for each matching row
        // For example, insert data into the temp table as per your previous requirements
        $TXDATE = $trxRow['TXDATE'];
        $BUSDATE = $trxRow['BUSDATE'];
        $DORC = $trxRow['DORC'];
        $TXNUM = $trxRow['TXNUM'];
        $TLTXCD = $trxRow['TLTXCD'];
        $ACCTNO = $trxRow['ACCTNO'];
        $CUSTID = $trxRow['CUSTID'];
        $CCYCD = $trxRow['CCYCD'];
        $ShortCD = $trxRow['ShortCD'];
        $AMT = $trxRow['AMT'];
        $ACC_BRID = $trxRow['ACC_BRID'];
        $ACTYPE = $trxRow['ACTYPE'];
        $TXDESC = $trxRow['TXDESC'];
        $WSNAME = $trxRow['WSNAME'];
        $CIF_BRID = $trxRow['CIF_BRID'];
        $AMT_ = $trxRow['AMT_'];
        $PCT_ID = $trxRow['PCT_ID'];
        $PCT = $trxRow['PCT'];
        $PPT = $trxRow['PPT'];
        $PCTTYPE = $trxRow['PCTTYPE'];

        $insertQuery = "INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE) VALUES ('$TXDATE','$BUSDATE','$DORC','$TXNUM','$TLTXCD','$ACCTNO','$CUSTID','$CCYCD','$ShortCD','$AMT','$ACC_BRID','$ACTYPE','$TXDESC','$WSNAME','$CIF_BRID','$AMT_','$PCT_ID','$PCT','$PPT','$PCTTYPE')";

        $insertResult = sqlsrv_query($conn, $insertQuery, $insertParams);

        echo $insertQuery;

        if ($insertResult === false) {
            die(print_r(sqlsrv_errors(), true));
        }
    }

    // Delete data from tbltrxn after insertion into temp
    $deleteQuery = "DELETE FROM tbltrxn WHERE SUBSTRING(TXNUM,1, 3) = '$branchPrefix'";

    echo $deleteQuery;

    $deleteResult = sqlsrv_query($conn, $deleteQuery);

    if ($deleteResult === false) {
        die(print_r(sqlsrv_errors(), true));
    }
}

}

and the result of it is query isActive event and then check check condition as code provide and after done go delete fields. on $branchValues = explode(',', $row['branch']); we use it because column branch has multiple value such as 010,011,030. pls note on it and make sql script by code provide

[chriltola]

Sure, I've translated the PHP code you provided into SQL script that performs the same actions. Here's the equivalent SQL code:

DECLARE @activeEvents TABLE (
    event_id INT,
    branch VARCHAR(100)
);

-- Get active events and their branches
INSERT INTO @activeEvents (event_id, branch)
SELECT event_id, branch
FROM tblevent
WHERE isActive = 1;

-- Loop through active events and perform actions
DECLARE @event_id INT;
DECLARE @branch VARCHAR(100);

DECLARE event_cursor CURSOR FOR
SELECT event_id, branch
FROM @activeEvents;

OPEN event_cursor;
FETCH NEXT FROM event_cursor INTO @event_id, @branch;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Insert data into temp based on branch and event_id
    INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
    SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
    FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branch
    AND AMT = (SELECT CAST(amount AS FLOAT) FROM tblevent WHERE event_id = @event_id);

    -- Delete data from tbltrxn after insertion into temp
    DELETE FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branch
    AND AMT = (SELECT CAST(amount AS FLOAT) FROM tblevent WHERE event_id = @event_id);

    FETCH NEXT FROM event_cursor INTO @event_id, @branch;
END;

CLOSE event_cursor;
DEALLOCATE event_cursor;

This SQL script mimics the PHP code's behavior of querying active events, inserting data into the temp table based on branch and event conditions, and then deleting the corresponding records from the tbltrxn table. Note that this script uses a cursor to loop through each active event and perform the required actions. Cursors can be resource-intensive, so use them judiciously and consider the performance implications in a production environment.

[chriltola]

make below sentence professional On 17/8/23, Campu ITD has completely disable NLA (Network Level Authentication) on 39 servers and one server cannot disable. However, Campu ITD has escalated and open case with Microsoft for checking on this issue on 18/8/23.

[chriltola]

On August 17, 2023, the Campus IT Department (Campu ITD) successfully implemented the disablement of Network Level Authentication (NLA) across 39 servers, with the exception of one server that encountered difficulties in the disabling process. To address this matter, Campu ITD promptly escalated the situation and initiated a case with Microsoft for thorough investigation and resolution, commencing on August 18, 2023.

[chriltola]

• On 13/7/23, PBB E-security has returned the pentest result after Campu ITD request pentest since 6/7/23. Campu ITD has fixed the issue of pentest successfully on 25/8/23. Otherwise, To continue other pentest, Campu ITD has emailed to PBB ITD Mr. Chee Jin on 15/7/23 to request assistance in raising a fresh eJS. From that day campu ITD do not get any update about eJs event if we mailed to follow up many time.

[chriltola]

On July 13, 2023, PBB E-security provided the results of the pentest in response to Campu ITD's pentest request made since July 6, 2023. Subsequently, Campu ITD effectively addressed the identified pentest issues and completed the remediation process on August 25, 2023.

In order to proceed with additional pentesting activities, Campu ITD initiated communication via email with Mr. Chee Jin of PBB ITD on July 15, 2023, seeking assistance in generating a new eJS. However, despite several follow-up emails, Campu ITD has yet to receive any updates regarding the eJS request since that time.