Visual FoxPro : การใช้ BindEvent

หลาย ๆ ครั้งเราอยากจะตรวจสอบเหตุการณ์ (event) ที่เกิดขึ้นใน Visual FoxPro 9 แต่บางเหตุการณ์ดันไม่มี method ของเหตุการณ์นั้นมารองรับ เช่น VFP มีแต่ KeyPress Event เป็นเหตุการณ์สำหรับตรวจสอบการกดปุ่มบนคีย์บอรด์อย่างเดียวเลย แต่..ถ้าเราอยากจะตรวจสอบเฉพาะตอนกดปุ่ม (KeyDown) หรือเฉพาะตอนปล่อยปุ่ม (KeyUp) จะทำไงละ แต่...คนสร้าง VFP ก็ช่างคิดเสียนี่กระไร ในเมื่อฉันไม่ได้เตรียม event/method ไว้ให้ แต่ก็ทิ้งคำสั่ง BindEvent เอาไว้ให้แทน 

มาดูตัวอย่างการทำฟอร์มให้สามารถใช้งาน การตรวจสอบการกดปุ่ม (KeyDown) และปล่อยปุ่ม (KeyUp) (ตัวอย่างนี้มาจากคำถามของสมาชิกชาว Fox จะป้องกัน user กดปุ่มค้างไม่ยอมปล่อย ดังนั้นถ้าไปเช็คใน KeyPress Event มันวิ่งตลอด ที่เขียนโปรแกรมทำอะไรไว้วิ่งเป็นพรวน....) ดังนั้น การตรวจสอบ KeyDown  KeyUp จะได้มันใจว่ากดยาว กดแช่ กดสั้น กดไว อย่างไรก็ทำครั้้งเดียวจนกว่าจะกดใหม่

 สำหรับตัวอย่างนี้สามารถใช้เป็นแนวทางในการทำ BindEvent กับเหตุการอื่น ๆ ได้อีกมากมาย นะจ๊ะ...

1. ให้สร้างฟอร์มขึ้นมาใหม่ จากนั้นไปที่เมนู Form -> New Method จะปรากฎหน้าต่าง New Method
        -  ที่ช่อง Name ป้อน KeyDown แล้วคลิกปุ่ม Add
        -  ที่ช่อง Name ป้อน KeyUp แล้วคลิกปุ่ม Add

2. ไปป้อนคำสั่งที่ฟอร์มในเมธอด ดังนี้

    -  เมธอด Init ป้อนคำสั่ง

        * สร้างตัวแปรฟอร์มสำหรับตรวจสอบการกดปุ่มค้าง
    =ADDPROPERTY(ThisForm, "nPreventKeyPress",0)
        * ทำการผูกการกดคีย์(keydown)(เลข 256) กับ object ฟอร์ม(This) เรียกเมธอด KeyDown
    =BINDEVENT(0, 256, This, "KeyDown")
    * ทำการผูกการกดคีย์(keydown)(เลข 257) กับ object ฟอร์ม(This) เรียกเมธอด KeyUp
    =BINDEVENT(0, 257, This, "KeyUp")

ดูความหมายของค่าตัวเลข 256 257 หรืออื่น ๆ ของ Windows Message ที่ส่งมา   https://wiki.winehq.org/List_Of_Windows_Messages

Vฺisual Studio 2022 C# Blazor - 01

// youtube : https://youtu.be/dY_AWdCzsCY?si=eQyqwMcRINbhvUYz
// index.razor

@page "/"
@rendermode InteractiveServer 

<h3>Hello, @userName</h3>
<h3>@GetUserName()</h3>
<p>Sum : @GetSum()</p>
<button @onclick = "ChgName">Change Name</button>

@if (testNum == 1)
{
    <p>Num is 1</p>
} else {
    <p>Num is not 1</p>
}
@foreach(var name in names)
{
    <p>@((MarkupString)name)</p>
}

@code {
    private string userName = "John";
    private int testNum = 10;
    private List<string> names;

    private string GetUserName()
    {
        return userName + "ซำบายดีไหม?";
    }

    private int GetSum()
    {
        return 2 + 2 + 3;
    }

    private void ChgName()
    {
        userName = "เกษม";
    }

    protected override void OnInitialized()
    {
        base.OnInitialized();
        names = new List<string>
        {
            "<b>Value 01<b>",
            "Value 02",
            "Value 03",
            "Value 04",
            "Value 05",
            "<i>Value 06<i>",
            "Value 07",
            "Value 08",
            "Value 09",
            "Value 10"
        };
    }
}

SQL Server : CTEs (Common Table Expressions)

Common Table Expressions หรือ CTEs เริ่มถูกนำมาใช้ใน SQL Server 2005 เป็นครั้งแรก โดยที่ CTE นั้นเป็นผลลัพท์ที่เป็นตารางชั่วคราว ซึ่งสามารถนำไปอ้างหรือใช้งานภายใต้คำสั่งคิวรี่ SELECT INSERT UPDATE หรือ DELETE ใน SQL Server 2008 ได้เพิ่มความสามารถให้ใช้ CTE ในคำสั่ง MERGE ได้ การใช้งาน CTE เริ่มด้วยการใช้ WITH

WITH  ชื่อตาราง_CTE
    AS
    (
          คำสั่ง Query ผลลัพธ์ของคิวรีที่ได้จะไปเก็บใน ชื่อตาราง_CTE
     )
คำสั่ง Query ที่เรียกใช้งานตาราง CTE

กรณีที่สร้างหลาย CTE  ก็ให้เพิ่มโดยใส่ , (คอมม่า) แล้วก็ชื่อตารางตามตัวอย่าง

WITH  ชื่อตาราง_CTE_1
    AS
    (
          คำสั่ง Query ผลลัพธ์ของคิวรีที่ได้จะไปเก็บใน ชื่อตาราง_CTE_1
     ) ,
ชื่อตาราง_CTE_2
    AS
    (
          คำสั่ง Query ผลลัพธ์ของคิวรีที่ได้จะไปเก็บใน ชื่อตาราง_CTE_2
         และสามารถเรียกใช้ตาราง CTE_1 ได้ด้วย
     )
คำสั่ง Query ที่เรียกใช้งานตาราง CTE

รูปแบบคำสั่งแค่นี้ก็เราก็สามารถแปรกระบวนท่าในการเขียนจัดการคำสั่งคิวรีได้มากมาย

ตัวอย่าง

WITH CTE AS (
            SELECT dd.Driver_no, COUNT(dd.Do_Date) Total_day
                FROM (SELECT DISTINCT Driver_no, Do_Date
                                     FROM DoDriver WHERE MYear = '2022' AND MPeriod = '12') dd
                GROUP BY dd.Driver_no
        )
 SELECT d.*, c.Total_Day FROM CTE c
       LEFT JOIN (
                SELECT d.Driver_no, d.Driver_name, SUM(d.Qty_do) Tot_do
                    ,SUM(d.Z_Q_tot) Total_QTY_G
                    ,SUM(d.Z_T_tot + d.Z_Trip_E1 ) as Total_WAGE
                    ,COUNT(d.Do_no) Total_trip, SUM(d.Z_T_tot) Total_T_tot
                    ,SUM(d.Z_Trip_E1) AS Total_E1, SUM(d.Z_Trip_E2) AS Total_E2
                    ,SUM(d.Z_Trip_E3) AS Total_E3, SUM(d.Z_Trip_E4) AS Total_E4
                    , SUM(d.Z_Trip_E5) AS Total_E5
                FROM DoDriver d WHERE d.MYear = '2022' AND d.MPeriod = '12'
                GROUP BY d.Driver_no, d.Driver_name
                ) AS d
         ON c.Driver_no = d.Driver_no

ตัวอย่าง 

WITH CTE AS (
         SELECT  RowOrder, MYear, MPeriod, Driver_no, Do_date, Do_Time
                      , ROW_NUMBER() OVER(PARTITION BY Driver_no, Do_Date
                               ORDER BY Driver_no, Do_Date, Do_time) AS Trip_of_D
                      , ROW_NUMBER() OVER(PARTITION BY Driver_no
                               ORDER BY Driver_no,Do_Date, Do_time) AS Trip_of_M
              FROM DoDriver
              WHERE MYear = '2022' AND MPeriod = '20212'
                    AND Driver_no = '0001' )
SELECT c.*,c1.Trip_sum_M FROM CTE c
     LEFT JOIN (SELECT Driver_no, COUNT(*) AS Trip_sum_M
                                    FROM CTE
                                    GROUP BY Driver_no) c1
     ON c.Driver_no = c1.Driver_no

จริง ๆ มัน เหมือน ๆ กับทำ subquery แต่มันจะเอามายำ ๆ ๆ ๆ ยำไปยำมาก่อน แล้วสุดท้ายเอาไปใช้งาน ได้ผลตามต้องการ ดูง่ายไล่สะดวก

เอาไว้เป็นไอเดีย การทดลองทำจะทำให้เข้าใจมากยิ่งขึ้น

SQL Server : OUTER APPLY and CROSS APPLY

หลายคนคงเคยใช้คำสั่ง JOIN เพื่อทำการเชื่อมโยงตารางตั้งแต่ 2 ตารางขึ้นไป แล้วให้ได้ผลลัพธ์ที่ต้องการ แต่มีคำสั่ง APPLY ที่ทำงานได้ลักษณะเดียวกันกับ JOIN แต่มีความคล่องตัวและยืดหยุ่นกว่า ซึ่งจริง ๆ แล้วคำสั่ง APPLY เกิดขึ้นมานานมากตั้งแต่ SQL Server 2005 การทำงานจะเหมือนการใช้งานคำสั่ง JOIN แต่จะแตกต่างกันตรงที่ตารางด้านขวาของ APPLY สามารถประมวลผลภายใต้เงื่อน where ไขของตารางด้านซ้ายได้ หรือสามารถเขียนเป็น table-value function แล้วส่งค่ากลับมาเชื่อมโยงกับตารางฝั่งซ้ายมือก็ได้ เป็นต้น

การใช้ APPLY หลัก ๆ จะใช้อยู่ 2 แบบคือ OUTER APPLY กับ CROSS APPLY โดยที่

    OUTER APPLY เปรียบได้กับการใช้ LEFT JOIN

    CROSS APPLY เปรียบได้กับการใช้ INNER JOIN

ดังนั้นการนำไปใช้งาน สามารถเลือกใช้ได้ตามความเหมาะสม

หลายท่านคงมีคำถามในใจ แล้วเราจะเอาไปประยุกต์ใช้ในงานใดได้บ้าง 

ดังนั้น : ขอยกตัวอย่าง สัก 2 - 3 ตัวอย่าง เพื่อให้พอเห็นภาพ เพื่อท่าน ๆ จะได้นำไปประยุกต์ใช้ได้ตามต้องการ

 

 

ตัวอย่าง การใช้คำสั่ง JOIN เปรียบเทียบกับการใช้คำสั่ง APPLY

SELECT p.[prod_no], p.[prod_name], t1.[in_min], t1.[in_max], t2.[in_last]
  FROM [Products] p
OUTER APPLY (
SELECT MIN(t.[qty]) in_min, MAX(t.[qty]) in_max
FROM [Transactions] t
WHERE t.type = 'IN' AND t.[prod_no] = p.[prod_no] GROUP BY t.[prod_no] 
) t1
OUTER APPLY  (
SELECT TOP 1 t.[qty] in_last
FROM [Transactions] t
WHERE t.type = 'IN' AND t.[prod_no] = p.[prod_no] ORDER BY t.[tran_id] DESC
) t2

SQL SERVER : UPDATE JOIN

มีอยู่วันหนึ่งไปเจอคำถาม ซึ่งไม่เกี่ยวกับผู้เขียน แต่อดที่จะตอบไม่ได้

"ผมจะใช้คำสั่ง SQL เพื่อ Update Table A โดยเอายอดรวมจาก Select sum ของ Table B ไปใส่ใน Table A ที่มี Key ตรงกันกับ Table B ได้อย่างไรครับ"

คำถามแบบนี้ชอบที่ยื่นมือเข้าไปตอบ ถ้าพูดภาษาชาวบ้านเขาเรียกว่า เสือก 555

การกระทำแบบนี้ สามารถใช้คำสั่ง SQL ได้หลายตัว เช่น UPDATE Join หรือ MERGE ก็ได้
แต่.... คำถาม ต้องการแค่ปรับปรุงยอดรวมเฉย ๆ ก็เลยใช้ UPDATE ร่วมกับ JOIN แทนการใช้คำสั่ง MERGE แต่คำสั่ง MERGER จริง ๆ แล้วทำอะไรได้มากกว่า มันสามารถ INSERT, UPDATE, DELETE จากแหล่งข้อมูลอื่นมาใส่ตารางเป้าหมายได้ในคำสั่งเดียวเลย อ้าว... นอกเรื่องอีกแล้ว 

จากที่เดาข้อคำถาม เข้าใจว่าคงเป็นการปรับปรุงยอดบางอย่าง เช่น ยอดสินค้าคงเหลือ ยอดเงินคงเหลือ หรือยอดลูกหนี้คงค้าง  เป็นต้น

เพื่อให้เห็นภาพ จะขอยกตัวอย่างการปรับปรุงยอดสต๊อกสินค้า จากรายการรับ/จ่ายที่เกิดขึ้น

ให้สร้างตารางเพื่อทดสอบ ดังนี้

สร้างตาราง Products

CREATE TABLE [Products] (
    [prod_no] [nchar](10) NOT NULL,
    [prod_name] [nvarchar](100),
    [onhand] [numeric](10, 0),
    PRIMARY KEY ([prod_no]))

สร้างตาราง Transactions สำหรับเก็บรายการ รับ/จ่าย สินค้า

CREATE TABLE [Transactions] (
    [tran_id] [int] IDENTITY(1,1) NOT NULL,
    [prod_no] [nchar](10),
    [type] [nchar](3),
    [qty] [numeric](10, 0),
    PRIMARY KEY ([tran_id])) 

ทำการเพิ่มข้อมูลตัวอย่างเข้าสู่ตาราง

INSERT INTO [Products] VALUES ('001','วิทยุทรานซิสเตอร์ ธานินทร์',0),
('002','ถ่านไฟฉาย ตรากบ',0),
('003','ยาแก้ปวด ทันใจ',0),
('004','บะหมี่กึ่งสำเร็จรูป มาม่า',0),
('005','นมข้นหวาน ตราหมี',0) ;

INSERT INTO [Transactions] VALUES('001','IN',20),
('003','IN',10),
('002','IN',100),
('005','IN',50),
('002','OUT',10),
('001','OUT',1),
('001','OUT',2),
('003','IN',15),
('003','OUT',10),
('001','OUT',2) ;

หลังจากทำการเพิ่มข้อมูลเรียบร้อยแล้ว ลองทำการคิวรีข้อมูลออกมาแสดงจะได้ ดังนี้

SELECT * FROM [Products]
SELECT * FROM [Transactions]

ให้ผู้อ่านลองดูข้อมูลในตาราง Transactions จะเห็นว่าข้อมูลจะมีคอลัมน์ type ซึ่งเก็บสถานะการรับ (in) และจ่าย (out) หากเราจะหาผลรวมที่เป็นยอดคงเหลือโดยใช้คำสั่ง SUM(qty) ก็จะมีปัญหาเนื่องจากยอดในคอลัมน์ qty มีค่าเป็นบวกหมด ดังนั้นเราต้องทำการแปลงเป็นค่า บวก หรือ ลบ โดยถ้า type มีค่าเป็น IN ให้ยอด qty มีค่าเป็นบวก แต่ถ้า type มีค่าเป็น OUT ก็ให้ยอด qty มีค่าเป็นลบ เพื่อจะได้หายอดคงเหลือของการรับ/จ่ายได้ สามารถเขียนคำสั่งได้ ดังนี้

SELECT * FROM [Transactions] ORDER BY [prod_no], [type]

SELECT [prod_no], SUM(CASE WHEN [type]='IN' THEN [qty] ELSE [qty] * -1 END) AS [onhand]  FROM [Transactions] GROUP BY [prod_no]

จากคำสั่ง SUM(CASE WHEN [type]='IN' THEN [qty] ELSE [qty] * -1 END) ข้างต้นที่เราจะได้ยอดคงเหลือมาแสดงเรียบร้อย

ขั้นต่อมาคือนำไปปรับปรุงยอดในคอลัมน์ onhand ของตาราง Products ซึ่งการทำเราจะใช้คำสั่ง UPDATE ร่วมกับ JOIN เพื่อปรับปรุงยอด ดังนี้

--ปรับปรุงข้อมูลตาราง Products ในคอลัมน์ onhand จากยอด SUM ของตาราง Transactions 
UPDATE P SET P.[onhand] = T.[onhand]
    FROM [Products] P
    INNER JOIN (SELECT [prod_no], SUM(CASE WHEN [type]='IN' THEN [qty]  ELSE [qty] * -1  END) AS [onhand] 
    FROM [Transactions] GROUP BY [prod_no]) T 
    ON P.[prod_no] = T.[prod_no]

--แสดงยอดที่ปรับปรุงแล้วของตาราง Products
SELECT * FROM [Products]

จะเห็นว่าการใช้ UPDATE JOIN ช่วยได้มากเลย คำสั่ง SQL เชื่อมโยงรหัส (prod_no) ที่ตรงกันแล้วทำกันปรับปรุงยอดให้ทำให้เรามันใจว่าอย่างไรเสียข้อมูลไม่มีวันผิดพลาด

สวัสดี ผลสลากกินแบ่ง งวด 1 พย. 65 รางวัลที่ 1 ..... 913106

SQL Server : Dynamic PIVOT in SQL Server

PIVOT ใน SQL Server คือเครื่องมือที่ช่วยสร้างตารางสรุปในรูปแบบการเปลี่ยนการแสดงผลจากแนวตั้ง (Row) มาเป็นแนวนอน (Column) ซึ่งช่วยให้การดูข้อมูลสะดวกมากยิ่งขึ้น แต่เนื่องจาก การทำ PIVOT ใน SQL Server จำเป็นต้องทราบข้อมูลที่จะนำมาใช้เป็นชื่อคอลัมน์ในแนวนอนที่แน่นอน ซึ่งเหมาะกับการทำสรุป ยอดขายประจำเดือน ซึ่งมีแค่ เดือน 1 ถึงเดือน 12 หรือการสรุปตามวัน ซึ่งมี 1 - 31 ไม่เกินนี้ แต่ถ้าหากข้อมูลเรามีการเพิ่มลดอย่างต่อเนื่อง (แต่ต้องไม่มากจนเกินไป) จำเป็นต้องใช้วิธีการเขียนคำสั่งเพื่อทำให้ PIVOT สามารถแสดงผลเป็นแบบ Dynamic ได้ตามต้องการ 

สำหรับตัวอย่างที่จะนำเสนอเป็นการสรุปยอด ตาม Category ของสินค้า ซึ่งจะแยกเป็นยอดขายแต่ละวัน โดยใช้ตาราง Category เป็นตารางที่จะนำมาเป็นตัวที่ทำ PIVOT ในส่วนของคอลัมน์ ซึ่งจะผันแปรไปตามข้อมูลที่มีอยู่ในตาราง Category ตาราง Product เก็บรายการสินค้า และ ตาราง Inv_dtl เก็บข้อมูลการขายสินค้า ตาราง inv_hd เก็บวันที่ขาย รายละเอียด ดังภาพ

ตัวอย่างตารางและข้อมูล

CREATE TABLE [category](
    [cat_id] [nchar](10) NULL,
    [cat_name] [nvarchar](50) NULL
)

CREATE TABLE [inv_dtl](
    [inv_no] [nchar](10) NULL,
    [product_id] [nchar](10) NULL,
    [qty] [money] NULL,
    [price] [money] NULL,
    [amount] [money] NULL
)

CREATE TABLE [inv_hd](
    [inv_no] [nchar](10) NULL,
    [inv_date] [date] NULL
)

CREATE TABLE [product](
    [product_id] [int] NULL,
    [product_name] [varchar](50) NOT NULL,
    [cat_id] [varchar](25) NULL
)
GO

INSERT [category] ([cat_id], [cat_name]) VALUES (N'C01', N'Beverage')
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C02', N'Beauty')
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C03', N'Health')
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C04', N'Food')

INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65001', N'101', 5.0000, 200.0000, 100.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65002', N'105', 1.0000, 80.0000, 80.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65002', N'104       ', 2.0000, 70.0000, 140.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65003', N'102', 3.0000, 50.0000, 150.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65003', N'104', 2.0000, 70.0000, 140.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65004', N'102', 2.0000, 80.0000, 160.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65005', N'101', 2.0000, 200.0000, 400.0000)

INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65001', CAST(N'2022-05-22' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65002', CAST(N'2022-05-22' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65003', CAST(N'2022-05-23' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65004', CAST(N'2022-05-24' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65005', CAST(N'2022-05-24' AS Date))

INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (1, 101, N'Soda', N'C01')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (2, 102, N'Water', N'C01')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (3, 103, N'Pork', N'C04')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (4, 104, N'Vegetable', N'C04')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (5, 105, N'Fruit', N'C04')
 

จากตารางข้างต้นถ้าเราจะทำรายงานสรุป ยอดขายตาม Category โดยแยกเป็นแต่ละวัน สามารถทำได้โดยใช้คำสั่ง

SELECT h.inv_date,c.cat_id,SUM(i.amount) as Total FROM inv_dtl i
    LEFT JOIN inv_hd h ON i.inv_no = h.inv_no
    LEFT JOIN product p ON i.product_id = p.product_id
    LEFT JOIN category c ON p.cat_id = c.cat_id
GROUP BY c.cat_id,h.inv_date
ORDER BY h.inv_date,c.cat_id

จะได้ผลลัพธ์ดังภาพ

จะเห็นว่าข้อมูลข้างต้นจะเป็นข้อมูลสรุปยอดขายตาม Category โดยแยกสรุปเป็นวัน แต่เป็นรายการในลักษณะแนวตั้ง การดูรายงานจะทำให้เข้าใจได้ยาก หากต้องการนำเสนอในรูปแบบสรุป จำเป็นต้องอาศัยการใช้งานคำสั่ง PIVOT เพื่อกลับตารางจากแนวตั้งเป็นแนวนอน

แต่สิ่งที่เป็นโจทย์ในบทความนี้คอ ต้องนำรายการของ Category ทั้งหมด มาแสดงเป็นหัวคอลัมน์ ดังนั้นต้องนำข้อมูลในตาราง Category ทั้งหมดมาแปลงก่อนนำไปใช้ในคำสั่ง PIVOT 

ซึ่งลักษณะนี้จะเป็นรูปแบบ Dynamic ที่ข้อมูลไม่ได้คงที่อาจมีการเพิ่มหรือลดลงได้ ดังนั้นจะต้องทำการ เตรียมข้อมูลสำหรับ PIVOT โดยให้ข้อมูลในตารางที่เป็น cat_id ทุกรายการ เอามาทำให้ให้อยู่ในรูปแบบดังนี้

C01,C02,C03,C04

สามารถทำได้โดยการเขียนคำสั่งดังนี้

DECLARE @colList varchar(MAX)
SELECT @colList = COALESCE(@colList + ',','') + RTRIM(cat_id)
FROM category 

SELECT @colList

จะได้ผลลัพธ์ดังภาพ

จากนั้นนำผลลัพธ์ที่เก็บในตัวแปร @colList ไปใช้ในคำสั่ง PIVOT โดยเขียนคำสั่งในรูปของตัวแปร และเรียกประมวลผลผ่านคำสั่ง EXEC ดังนี้

DECLARE @colList varchar(MAX)
SELECT @colList = COALESCE(@colList + ',','') + RTRIM(cat_id) FROM category
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery = 'SELECT * FROM   
                 (
                    SELECT h.inv_date,c.cat_id,SUM(i.amount) as Total FROM inv_dtl i
                        LEFT JOIN inv_hd h ON i.inv_no = h.inv_no
                        LEFT JOIN product p ON i.product_id = p.product_id
                        LEFT JOIN category c ON p.cat_id = c.cat_id
                    GROUP BY c.cat_id,h.inv_date
                 ) t
                PIVOT(
                    SUM(total)
                    FOR cat_id IN ('+ @colList +')
                ) AS pivot_table;';

EXEC(@SQLQuery)

จะได้ผลลัพธ์ตามภาพ

หากไม่ต้องการ คอลัมน์ เป็นรหัส C01 C02 C03 C04 สามารถใช้ชื่อ cat_name มาแทนได้ แต่ต้องระวังเรื่องของความยาว โดยที่ชื่อนั้นไม่ควรยาวมากจนเกินไป และควรใส่ [ ] คลุมระหว่างชื่อด้วยเพื่อป้องกันเรื่องของการเว้นช่องว่างระหว่างคำ

DECLARE @colList varchar(MAX)
SELECT @colList = COALESCE(@colList + ',','') + '[' + RTRIM(cat_name)+ ']' FROM category
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery = 'SELECT * FROM   
                 (
                    SELECT h.inv_date,c.cat_name,SUM(i.amount) as Total FROM inv_dtl i
                        LEFT JOIN inv_hd h ON i.inv_no = h.inv_no
                        LEFT JOIN product p ON i.product_id = p.product_id
                        LEFT JOIN category c ON p.cat_id = c.cat_id
                    GROUP BY c.cat_name,h.inv_date
                 ) t
                PIVOT(
                    SUM(total)
                    FOR cat_name IN ('+ @colList +')
                ) AS pivot_table;';

EXEC(@SQLQuery)

จะได้ผลลัพธ์ ดังภาพ

ขอให้สนุกกับการกลับตาราง

สวัสดี ร่ำรวย ดูหนังกลางแปลง ปิ้งปลาหมึก อร่อย

PostgreSQL : กำหนดให้ postgresql เก็บ log คำสั่ง SQL

การกำหนดให้ PostgreSQL เก็บ log คำสั่ง SQL

1. เข้าไปที่แก้ไขไฟล์ postgresql.conf กรณีใช้ postgresql 14 ถ้ารุ่นอื่นก็เปลี่ยนตัวเลขรุ่นตามต้องการ โดยป้อนคำสั่งผ่าน Terminal

         sudo vim /etc/postgresql/14/main/postgresql.conf

        จากนั้น ไปหาและแก้ไขค่าตามนี้          

      

        กดปุ่ม i เพื่อเข้าโหมด insert ของ โปรแกรม vim แล้วแก้ไข

         log_destination = 'csvlog'
         logging_collector = on
         log_directory = 'pg_log'
         log_filename = 'test_postgresql-%Y-%m-%d_%H%M%S.log'
         log_statement = 'all'

         เมื่อแก้ไขเสร็จให้กดปุ่ม Esc เพื่อออกจากโหมดแก้ไข
         กด    :w       บันทึก
          กด    :q        ออก

2. ทำการ รีสตาร์ทเซอร์วิสของ postgresql ใหม่ ด้วยคำสั่ง

         sudo systemctl restart postgresql

จากนั้นไป เมื่อมีการใช้คำสั่ง SQL จัดการกับฐานข้อมูลก็จะถูกเก็บ log ไว้ที่          

        /var/lib/postgresql/14/main/   ภายใต้โฟลเดอร์ pg_log

เรียกดูไฟล์

        sudo ls -l /var/lib/postgresql/14/main/pg_log/

เมื่อพบไฟล์เป้าหมาแล้ว ก็เอาออกมาไว้ที่อื่น เช่น โฟลเดอร์ Desktop

        sudo cat /var/lib/postgresql/14/main/pg_log/ชื่อไฟล์log.csv > Desktop/ชื่อไฟล์log.csv

เป็นอันเรียบร้อย  

PostgreSQL : กำหนด Configuration สำหรับฐานข้อมูล PostgreSQL บน Ubuntu 20.04 ให้สามารถเข้าถึงผ่าน ip address จากภายนอก

การกำหนด Configuration สำหรับฐานข้อมูล PostgreSQL บน Ubuntu 20.04  ให้สามารถเข้าถึงผ่าน ip address จากภายนอก
สำหรับเครื่องที่ใช้ เป็นระบบปฏิบัติการ Windows 10 ดังนั้นเราจำเป็นต้องติดตั้ง Hyper-V ซึ่งเป็น Virtualization ของ Windows เอง ดั้งนั้นของที่มีก็ใช้แบบไม่ต้องไปหาอื่นไกล ส่วนการติดตั้ง Hyper-V และติดตั้ง Ubuntu 20.04 ไม่ขอกล่าวถึง

 

 

 

 

 

 

 

 

 

เปิด Terminal แล้วเรียกใช้คำสั่ง ifconfig ดู ip address แล้วจดไว้เพื่อจะ Connect เข้าถึงข้อมูล

เมื่อติดตั้ง Ubuntu ใน hyper-v เสร็จก็ไปทำการติดตั้ง PostgreSQL ต่อเลยโดยเข้าไปดูตาม Link ด้านล่างได้เลย 555

https://www.cherryservers.com/blog/how-to-install-and-setup-postgresql-server-on-ubuntu-20-04

 

ทำการเปิด firewall ให้กับ port ที่ต้องการ เช่น 5432 โดยพิมพ์คำสั่ง ดังนี้

sudo ufw allow from any to any port 5432 proto tcp 

* สั้ง List รายการเพื่อตรวจสอบว่า port เปิดหรือไม่

sudo lsof -i -P -n | grep LISTEN

ติดตั้งเสร็จ ให้เข้าไปแก้ไข config เพิ่มเติ่มจากเว็บก่อนหน้าที่ให้กำหนดไว้ โดยพิมพ์คำสั่งที่ Terminal

sudo vim /etc/postgresql/14/main/postgresql.conf

แล้วแก้ ตรงส่วนของ listen_addresses = 'local' ให้เป็น

listen_addresses = '*'

จากนั้นไปแก้ไฟล์ pg_hba.conf

sudo vim /etc/postgresql/14/main/pg_hba.conf

แล้วเพิ่มสิทธิ์เข้าไปตามนี้ หรือจะกำหนดเฉพาะไอพีก็ได้  แต่สำหรับตัวอย่างข้างล่างคือเปิดหมด

host     all    all     0.0.0.0/0           scram-sha-256
host     all    all     ::1/128              trust
host     all    all     127.0.0.1/32     trust

ทำเสร็จก็ทำการ restart service ด้วยคำสั่ง

systemctl restart postgresql

เสร็จสำหรับ Ubuntu และ PostgreSQL

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

จากนั้นไปที่เครื่อง Client ที่เราต้องการติดต่อที่ Windows 10

ไปทำการดาวน์โหลด ODBC มาลงที่เครื่อง ที่เว็บ

https://www.postgresql.org/ftp/odbc/versions/msi/

ให้เลือกตัวใหม่ ๆ แล้วเลือก 32 bit หรือ 64 bit ขึ้นอยู่กับโปรแกรมที่เราจะติดต่อว่าเป็น 32 bit หรือ 64 bit ก็ลงตามนั้น

ส่วนการติดต่อก็ผ่าน connection string ประมาณนี้

Driver={PostgreSQL Unicode};Server=172.18.117.166;Port=5432;Database=ชื่อDatabase;Uid=postgres;Pwd=รหัสผ่าน;

ตัวอย่างโปรแกรมที่ใช้เขียนเพื่อติดต่อฐานข้อมูล

cPostgreSQL = [Driver={PostgreSQL Unicode};Server=172.18.117.166;Port=5432;Database=test_erp;Uid=postgres;Pwd=12345555;]
nHandle = SQLSTRINGCONNECT(cPostgreSQL)
IF nHandle > 0
    TEXT TO cSQL NOSHOW TEXTMERGE
        SELECT * FROM clients
    ENDTEXT
    nSuccess = SQLEXEC(nHandle,cSQL)
    IF nSuccess > 0
        ** นำข้อมูลมาแสดงข้อมูล
    ENDIF
    =SQLDISCONNECT(nHandle)
ENDIF

macOS : macOS Monterey การติดตั้ง Home-brew และติดตั้ง apache php

macOS Monterey เป็นรุ่นที่ตัด php ออกจากสารบบ ถ้าจะใข้ต้องลงเอง 555

เริ่มกันเลย ก่อนอื่นให้เปิด Terminal ขึ้นมาก่อน แล้วป้อนคำสั่ง

1. ติดตั้ง Xcode command line tools

        xcode-select --install

2. ติดตั้ง Apache (ล้างของเก่าแล้วลงใหม่)

       sudo apachectl stop

        sudo launchctl unload -w /System/Library/LaunchDaemons/org.apache.httpd.plist 2>/dev/null

        brew install httpd

    จากนั้นก็ทำการ stop แล้ว start service ของ php

        brew services stop httpd

        

        brew services start httpd

    สำหรับ document root ในการนำไฟล์ html ไปใส่ จะเก็บที่ /usr/local/var/www     

3. ติดตั้ง Visual Studio Code กัน

        brew install --cask visual-studio-code

        แต่ถ้าเคยติดตั้ง VS Code ไปแล้วก็ให้หาดูว่ามี โฟลดเดอร์ /opt/homebrew/bin อยู่ไหม 

        ถ้าไม่มีให้สร้างก่อน ด้วยคำสั่ง mkdir  แล้วเรียกคำสั่ง ตามด้านล่าง

        sudo ln -s /Applications/Visual\ Studio\ Code.app/Contents/Resources/app/bin/code /opt/homebrew/bin/code

4. ติดตั้ง php 7.4   รุ่นอื่นรอให้เขาทำออกมาก่อน แล้วก็เปลี่ยนตัวเลขรุ่นด้านหลังคำสั่งเอาเองนะ

        brew install shivammathur/php/php@7.4

        

        กำหนด path ให้กับ php ด้วยคำสั่ง

        brew link --overwrite --force php@7.4  

        การ start / stop / restart  php จะสั่งอะไรก็เลือกเอาได้ ตามต้องการ 

        แต่ยังไงสุดท้ายก็อย่าลืม start ละ

        stop

        brew services stop shivammathur/php/php@7.4

        start

        brew services start shivammathur/php/php@7.4

         restart

         brew services restart shivammathur/php/php@7.4

        หลังจาก start php ได้แล้ว ลองทดสอบดู ถาม version ของ php กัน

        php -v

        ลองเรียกใช้งาน phpinfo()

        php -r 'phpinfo();'

  5. แก้ไขไฟล์ httpd.conf    

      ให้เข้าไปแก้ที่โฟลดเดอร์   /usr/local/etc/httpd/   (สำหรับ cpu intel นะ)

      ถ้า cpu M1 ก็ไปอีกที่ ... ลืม ไว้หาก่อน

    

        sudo code httpd.conf

        แล้วไปเพิ่ม 

    

        LoadModule php7_module    /usr/local/opt/php@7.4/lib/httpd/modules/libphp7.so

        

        แก้ตรง dir_module ตามนี้

        <IfModule dir_module>

            DirectoryIndex index.php index.html

        </IfModule>

        <FilesMatch \.php$>

            SetHandler application/x-httpd-php

        </FilesMatch>