示例#1
0
        static void Main(string[] args)
        {
            /*
             * 1. 更變訂單狀態 私廚確認_開放客戶評價 -> 客戶確認_完成評價
             * 2. 變更 [私廚] 的評級 : 由 [訂單] 平均算出
             * 3. 依[訂單][數量] * [v評價VM][價格] 加入至私廚的 [會員][點數]
             */

            Dbclass db = new Dbclass(args[0]);

            var fOID_list = new List <int>();
            var fPID_list = new List <int>();
            var fCID_list = new List <int>();

            var sql_order = @"
               SELECT o.fOID [fOID], o.fPID [fPID], c.fCID [fCID]
                FROM
                    (SELECT fOID , fPID 
                    FROM [t訂單] o
                    WHERE o.f狀態=2 AND 
                        DATEADD(day,5,
	                        (SELECT TOP 1 value FROM STRING_SPLIT(o.f預定日期,' '))
                        ) < GETDATE()
                    ) AS o
                JOIN [t販售項目] p ON o.fPID=p.fPID
                JOIN [t私廚] c ON p.fCID=c.fCID
            ";

            db.SQLReader(sql_order, new SqlParameter[] { }, (reader) =>
            {
                while (reader.Read())
                {
                    fOID_list.Add((int)reader["fOID"]);
                    fPID_list.Add((int)reader["fPID"]);
                    fCID_list.Add((int)reader["fCID"]);
                }
            });

            for (int i = 0; i < fOID_list.Count(); i++)
            {
                // 1. 更變訂單狀態 私廚確認_開放客戶評價 -> 客戶確認_完成評價
                string sql_up_order = @"
                UPDATE o
                SET o.f評價日期 = @f評價日期,
	                o.f狀態=3,
	                o.f評級=5,
	                o.f評價內容=N'5天未評價,系統自動評價'
                FROM [t訂單] o
                WHERE o.fOID=@fOID
                ";

                db.SQLExecute(sql_up_order, new SqlParameter[] {
                    new SqlParameter("@fOID", fOID_list[i]),
                    new SqlParameter("@f評價日期", DateTime.Now.ToString("g"))
                });

                // 2. 變更 [私廚] 的評級 : 由 [訂單] 平均算出
                string sql_up_avg = @"
                UPDATE c
                SET c.f私廚評級 = chef_Avg.平均評級,
	                c.f私廚評級筆數 = chef_Avg.總筆數
                FROM 
	                (SELECT	
		                CAST(ROUND(AVG(CAST(o.f評級 AS decimal)),0) AS int) [平均評級], 
		                COUNT(*) [總筆數], c.fCID [fCID] 
	                FROM [t私廚] c 
	                JOIN [t販售項目] p ON c.fCID = p.fCID 
	                JOIN [t訂單] o ON o.fPID = p.fPID 
	                WHERE c.fCID = @fCID  and o.f狀態 = 3 
	                GROUP BY c.fCID) AS chef_Avg
                JOIN [t私廚] c ON chef_Avg.fCID=c.fCID
                WHERE c.fCID = @fCID
                ";

                db.SQLExecute(sql_up_avg, new SqlParameter[] { new SqlParameter("@fCID", fCID_list[i]) });


                // 3. 依[訂單][數量] * [v評價VM][價格] 加入至私廚的 [會員][點數]
                string sql_up_point = @"
                UPDATE u
                SET u.f點數= u.f點數 - CAST(ROUND(0.9*o.f數量*p.f價格, 0) AS int)
                FROM
	                (SELECT *
	                FROM [t訂單] o
	                WHERE o.fPID=@fPID
		                ) AS o
                JOIN [t販售項目] p ON o.fPID=p.fPID
                JOIN [t私廚] c ON p.fCID=c.fCID
                JOIN [t會員] u ON c.fUID=u.fUID
                ";

                db.SQLExecute(sql_up_point, new SqlParameter[] { new SqlParameter("@fPID", fPID_list[i]) });
            }
        }