using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;

namespace IDM.Data
{
    public class TOrderDetailXLS
    {
        public string goodsno;
        public string goodsname;
        public int goodslvl;
        public int goodsnum;

        public TOrderDetailXLS(string gno, string gname, int glvl, int gnum)
        {
            goodsno = gno;
            goodsname = gname;
            goodslvl = glvl;
            goodsnum = gnum;
        }
    }

    public class TOrderXLS
    {
        public string customer = String.Empty;
        public string ikeano;
        public DateTime ddate = DateTime.Today;
        public List<TOrderDetailXLS> details;

        public TOrderXLS(string ikno, string cust, DateTime date)
        {
            ikeano = ikno;
            customer = cust;
            ddate = date;
            details = new List<TOrderDetailXLS>();
        }

        public static TOrderXLS GetOrderFromExcelFile(string fullFileName)
        {
            string connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", fullFileName);
            OleDbConnection conn = new OleDbConnection(connString);

            string cmdString =
@"SELECT 發(fā)貨時(shí)間 AS ddate, 商店 AS customer, [IKEA NO#] AS ikeano, 貨號(hào) AS goodsno, 
      貨名 AS goodsname, 數(shù)量 AS ordernum, 發(fā)貨等級(jí) AS goodslvl
FROM [Sheet1$]";
            OleDbCommand cmd = new OleDbCommand(cmdString, conn);

            TOrderXLS order = null;
            try
            {
                conn.Open();
                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    if (! reader.IsDBNull(0))
                    {
                        DateTime ddate = Convert.ToDateTime(reader[0].ToString());
                        string customer = reader[1].ToString();
                        string ikeano = reader[2].ToString().ToUpper();
                        string goodsno = reader[3].ToString();
                        string goodsname = reader[4].ToString();
                        int goodsnum = Convert.ToInt32(reader[5].ToString());
                        int goodslvl = Convert.ToInt32(reader[6].ToString());
                        if (order == null)
                        {
                            order = new TOrderXLS(ikeano, customer, ddate);
                        }
                        order.details.Add(new TOrderDetailXLS(goodsno, goodsname, goodslvl, goodsnum));
                    }
                }
            }
            catch
            {
                order = null;

            }
            finally
            {
                conn.Close();
            }

            return order;
        }
    }
}