using System;
using System.Collections.Generic;
using System.Text;
using MyCRM.IDAL;
using MyCRM.Models;
using System.Data;
using System.Data.SqlClient;
using MyCRM.DBUtility;

namespace MyCRM.DAL
{
   /// <summary>
   /// 訂單數據層
   /// </summary>
   public  class OrdersService:IOrders
    {
        /// <summary>
        /// 單個訂單總價錢
        /// </summary>
       public decimal Sum(int id)
       {
           decimal prc = 0;
           string sql = "select sum(odd_count*odd_price)as price from orders_line where odd_order_id="+id;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               prc = (decimal)reader["price"];
               reader.Close();
               return prc;
           }
           else
           {
               reader.Close();
               return prc;
           }
       }
       /// <summary>
       /// 構成條件查詢
       /// </summary>
       /// <param name="name"></param>
       /// <param name="time"></param>
       /// <returns></returns>
       public List<Order> GetSelect( string name, string time)
       {
           string sql = "SELECT orders.orders_customer,sum(orders_line.odd_count *orders_line.odd_price) as he FROM orders INNER JOIN orders_line ON orders.orders_id =orders_line.odd_order_id  ";
           string tiaojian = "";
           if (name != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = "where orders.orders_customer='" + name + "' ";
               }
               else
               {
                   tiaojian += "and orders.orders_customer='" + name + "' ";
               }
           }
           if (time != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = " where year(orders.orders_date)='" + time + "' ";
               }
               else
               {
                   tiaojian += " and year(orders.orders_date)='" + time + "'";
               }
           }
           sql = sql + tiaojian + "group by orders.orders_customer";
           List<Order> list = new List<Order>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Order ord = new Order();

                   ord.Price = (decimal)row["he"];
                   ord.Customername = (string)row["orders_customer"];
                   list.Add(ord);
               }
               return list;
           }
       }
       /// <summary>
       /// 根據客戶名稱查詢訂單
       /// </summary>
       /// <param name="name"></param>
       /// <returns></returns>
       public List<Orders> GetOrdersByCustomerName(string name)
       {
           string sql = "select * from orders where orders_customer='"+name+"'";
           List<Orders> list = new List<Orders>();

           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Orders ord = new Orders();
                   ord.Orders_id = (int)row["Orders_id"];
                   ord.Orders_customer = (string)row["Orders_customer"];
                   ord.Orders_date = (DateTime)row["Orders_date"];
                   ord.Orders_status = (string)row["Orders_status"];
                   ord.Orders_addr = (string)row["Orders_addr"];
                   list.Add(ord);
               }

               return list;
           }
       }
       /// <summary>
       /// 根據訂單ID查詢
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public Orders GetOrdersByOrdersId(int id)
       {
           string sql = "select * from orders where orders_id=" + id;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Orders ord = new Orders();
               ord.Orders_id = (int)reader["Orders_id"];
               ord.Orders_customer = (string)reader["Orders_customer"];
               ord.Orders_date = (DateTime)reader["Orders_date"];
               ord.Orders_status = (string)reader["Orders_status"];
               ord.Orders_addr = (string)reader["Orders_addr"];            
               reader.Close();
               return ord;
           }
           else
           {
               reader.Close();
               return null;
           }

       }
       /// <summary>
       /// 根據訂單ID查詢相關信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public List<Orders_line> GetOrdersInfoByOrdersId(int id)
       {
           string sql = "SELECT dbo.product.product_id, dbo.orders_line.odd_count, dbo.orders_line.odd_unit,dbo.orders_line.odd_price FROM dbo.orders_line INNER JOIN dbo.product ON dbo.orders_line.odd_prod_id = dbo.product.product_id WHERE (dbo.orders_line.odd_order_id ="+id+")";
           List<Orders_line> list=new List<Orders_line>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Orders_line ord = new Orders_line();
                   ord.Product_id = GetProductById((int)row["product_id"]);
                   ord.Odd_count = (int)row["odd_count"];
                   ord.Odd_unit=(string)row["odd_unit"];
                   ord.Odd_price=(decimal)row["odd_price"];
                   list.Add(ord);
               }
               return list;
           }
       }
       /// <summary>
       ///  根據時間查詢
       /// </summary>
       /// <param name="time"></param>
       /// <returns></returns>
       public List<Order> GetType(string time)
       {
           string sql = "select service_type,count(*) as num from cst_service ";
           string tiaojian = "";
           if (time != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = " where year(service_create_date)='" + time + "' ";
               }
               else
               {
                   tiaojian += " and year(service_create_date)='" + time + "'";
               }
           }
           sql = sql + tiaojian + "group by service_type";
           List<Order> list = new List<Order>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Order ord = new Order();
                   ord.Type = (string)row["service_type"];
                   ord.Count = (int)row["num"];
                   list.Add(ord);
               }
               return list;
           }
       }
       /// <summary>
       /// 根據等級情況查詢
       /// </summary>
       /// <returns></returns>
       public List<Order> GetOrder()
       {

           string sql = "select customer_level_label,count(*) as num from cst_customer group by customer_level_label";
           List<Order> list = new List<Order>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Order ord = new Order();

                   ord.Level = (string)row["customer_level_label"];
                   ord.Count = (int)row["num"];

                   list.Add(ord);
               }
               return list;
           }
       }
       /// <summary>
       /// 多個條件查詢
       /// </summary>
       /// <param name="name"></param>
       /// <returns></returns>
       public List<Order> GetOrderBylevel(string name)
       {

           string sql = "select "+name+",count(*) as num from cst_customer group by "+name+"";
           List<Order> list = new List<Order>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Order ord = new Order();
                   if (name.Equals("customer_level_label"))
                   {
                       ord.Level = (string)row["" + name];
                   }else{
                        ord.Level = ((int)row[""+name]).ToString();
                   }                 
                   ord.Count = (int)row["num"];  
                   list.Add(ord);
               }
               return list;
           }
       }
       /// <summary>
       /// 查詢用戶訂單情況和總額
       /// </summary>
       /// <returns></returns>
       public List<Order> GetOrders()
       {
           string sql = "SELECT orders.orders_customer,sum(orders_line.odd_count *orders_line.odd_price) as he FROM orders INNER JOIN orders_line ON orders.orders_id =orders_line.odd_order_id group by orders.orders_customer";
           List<Order> list = new List<Order>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Order ord = new Order();
                   
                   ord.Price = (decimal)row["he"];
                   ord.Customername = (string)row["orders_customer"];                 
                   list.Add(ord);
               }
               return list;
           }
       }
       /// <summary>
       /// 根據商品ID查詢
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public Product GetProductById(int id)
       {
           string sql = "select * from product where product_id=" + id;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Product pro = new Product();
               pro.Product_id = (int)reader["product_id"];
               pro.Product_name = (string)reader["product_name"];
               pro.Product_type = (string)reader["product_type"];
               pro.Product_batch = (string)reader["product_batch"];
               pro.Product_unit = (string)reader["product_unit"];
               pro.Product_price = (decimal)reader["Product_price"];
               pro.Product_memo = (string)reader["Product_memo"];
               reader.Close();

               return pro;
           }
           else
           {
               reader.Close();
               return null;
           }
       }
    }
}