using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;
using System.Data;

namespace demo
{
    public enum EM_Read_State
    {
        成功,
        失败,
        省内,
        省外
    }
    class ClassFirstCount
    {
        public static string path = Application.StartupPath + "\\db\\读卡数量记录.db";

        public static string tableName = "firstInfo";

        public static string strConn = "data source=" + path;

        public static bool CreateDB()
        {
            if (File.Exists(path))
            {
                return true;
            }
            bool ret = false;
            SQLiteConnection cn = null;
            try
            {
                cn = new SQLiteConnection("data source=" + path);
                cn.Open();
                ret = true;
            }
            catch (System.Exception ex)
            {
            }
            finally
            {
                cn.Close();
            }
            return ret;
        }

        public static bool CreateTable()
        {
            bool ret = false;
            SQLiteConnection cn = new SQLiteConnection("data source=" + path);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    cn.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = cn;
                    string sql = "CREATE TABLE IF NOT EXISTS " + tableName +
                        "(sfz_id varchar(18),sfz_name varchar(20),post_id varchar(13),write_time INTEGER,read_state varchar(8))";

                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    ret = true;
                }
                catch (System.Exception ex)
                {

                }
                finally
                {
                    cn.Close();
                }
            }
            return ret;
        }

        public static bool IsExitInfo(string sfz_id)
        {//
            bool ret = false;

            using (SQLiteConnection conn = new SQLiteConnection(strConn))
            {
                string sql = string.Format("select * from {0} where sfz_id = '{1}'", tableName, sfz_id);
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        int i = cmd.ExecuteNonQuery();
                        if (i > 0)
                        {
                            ret = true;
                        }
                    }
                    catch
                    {
                    }
                    finally
                    {
                        try
                        {
                            cmd.Dispose();
                            conn.Close();
                        }
                        catch
                        {
                            ret = false;
                        }
                    }
                }
            }
            return ret;
        }
        public static object obj = new object();
        public static bool InsertInfo(string sfz_id, string sfz_name, string post_id, EM_Read_State state)
        {
            lock (obj)
            {
                bool ret = false;
                if (sfz_id != null)
                {
                    if (IsExitInfo(sfz_id))
                    {
                        return true;
                    }
                }
               
                SQLiteConnection conn = new SQLiteConnection(strConn);
                SQLiteCommand cmd = null;
                try
                {
                    conn.Open();
                    string sql = string.Format
                        ("insert into {0}(sfz_id, sfz_name, post_id, write_time, read_state)values('{1}','{2}','{3}',{4},'{5}')",
                        tableName, sfz_id, sfz_name, post_id, Convert.ToInt32(DateTime.Now.ToString("yyyyMMdd")), state.ToString());
                    cmd = new SQLiteCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i == 1)
                    {
                        ret = true;
                    }

                }
                catch (Exception ex)
                {
                    string str = ex.ToString();
                }
                finally
                {
                    try
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                    catch
                    {
                        ret = false;
                    }
                }
                return ret;
            }
        }

        public static bool DeleteBefore30DaysData()
        {
            bool flag = false;
            SQLiteConnection conn = null;
            SQLiteCommand cmd = null;
            DateTime nowTime = DateTime.Now;
            FileInfo fi = new FileInfo(path);
            DateTime createTime = fi.CreationTime;
            TimeSpan ts = nowTime - createTime;
            double time = ts.TotalDays;
            if (time <= 30)
            {
                return true;
            }
            string sql = string.Format("delete from {0} where write_time < {1}",
                tableName, Convert.ToInt32(nowTime.ToString("yyyyMMdd"))-30);
            try
            {
                conn = new SQLiteConnection(strConn);
                conn.Open();
                cmd = new SQLiteCommand(sql, conn);
                cmd.ExecuteNonQuery();
                flag = true;
            }
            catch (Exception ex)
            {
                string str = ex.ToString();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return flag;
        }

        public static bool GetRowsFromTimeByState(int day, EM_Read_State state, ref int count)
        {
            lock (obj)
            {
                bool ret = false;

                SQLiteConnection conn = new SQLiteConnection(strConn);
                SQLiteCommand cmd = null;
                try
                {
                    conn.Open();
                    string sql = string.Format("select * from {0} where read_state = '{1}' and write_time = {2}", tableName, state, day);
                    cmd = new SQLiteCommand(sql, conn);
                    DataTable dt = new DataTable();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(dt);
                    count = dt.Rows.Count;
                    ret = true;                    
                }
                catch (Exception ex)
                {
                    string str = ex.ToString();
                }
                finally
                {
                    try
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                    catch
                    {
                        ret = false;
                    }
                }
                return ret;
            }
        }

        public static bool GetRowsFromTimeByState(int startDay, int endDay, EM_Read_State state, ref int count)
        {
            lock (obj)
            {
                bool ret = false;

                SQLiteConnection conn = new SQLiteConnection(strConn);
                SQLiteCommand cmd = null;
                try
                {
                    conn.Open();
                    string sql = string.Format("select * from {0} where read_state = '{1}' and write_time >= {2} and write_time <={3}", tableName, state, startDay, endDay);
                    cmd = new SQLiteCommand(sql, conn);
                    DataTable dt = new DataTable();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(dt);
                    count = dt.Rows.Count;                    
                    ret = true;
                }
                catch (Exception ex)
                {
                    string str = ex.ToString();
                }
                finally
                {
                    try
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                    catch
                    {
                        ret = false;
                    }
                }
                return ret;
            }
        }
    }
}