Moka_CShare_FactoryTool/FactoryTool_CShare/Database/SQLiteHelper.cs

using SXLibrary;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace MOKA_Factory_Tools
{
    class SQLiteHelper
    {
        /// <summary>
        /// 新建数据库文件
        /// </summary>
        /// <param name="dbPath">数据库文件路径及名称</param>
        /// <returns>新建成功，返回true，否则返回false</returns>
        static public Boolean NewDbFile(string dbPath)
        {
            try
            {
                Log.WriteInfoLog(dbPath);
                SQLiteConnection.CreateFile(dbPath);
                return true;
            }
            catch (Exception ex)
            {
                Log.WriteInfoLog(ex.Message);
                throw new Exception("Create DB" + dbPath + "fail：" + ex.Message);
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbPath">指定数据库文件</param>
        /// <param name="tableName">表名称</param>
        static public void NewTable(string dbPath, string tableName,string ColumnMessage)
        {

            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "CREATE TABLE " + tableName + ColumnMessage;
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        /// <summary>
        /// mid表插入一行
        /// </summary>
        /// <param name="dbPath"></param>
        /// <param name="param"></param>
        static public void AddOneLine(string dbPath,object[] param)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("insert into mid(bid,number,pid,ctype,version,host,purl,psize,pmd5,status) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", param[0], param[1], param[2], param[3], param[4], param[5], param[6], param[7], param[8], param[9]);
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        /// <summary>
        /// roku表插入一行
        /// </summary>
        /// <param name="dbPath"></param>
        /// <param name="param"></param>
        static public void AddRokuOneLine(string dbPath, object[] param)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("insert into rokuCustomer(ordernum,data) values('{0}','{1}')", param[0], param[1]);
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        /// <summary>
        /// whitebalance表插入一行
        /// </summary>
        /// <param name="dbPath"></param>
        /// <param name="param"></param>
        static public void AddwbOneLine(string dbPath, object[] param)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("insert into whitebalance(ordernum,hdmirgain,hdmiggain,hdmibgain,nrgain,nggain,nbgain,lrgain,lggain,lbgain) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", param[0], param[1], param[2], param[3], param[4], param[5], param[6], param[7], param[8], param[9]);
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        /// <summary>
        /// dsn表插入一行
        /// </summary>
        /// <param name="dbPath"></param>
        /// <param name="param"></param>
        static public void AdddsnOneLine(string dbPath, object[] param)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("insert into dsn(ordernum,dsn) values('{0}','{1}')", param[0], param[1]);
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        static public void UpdateTime(string dbPath, string bid)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("update mid set finish_date=datetime(CURRENT_TIMESTAMP,'localtime') where bid='{0}'",bid);
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        static public void UpdateStatus(string dbPath, string str)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("update mid set status='{0}'", str);
                cmd.ExecuteNonQuery();
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
        }

        /// <summary>
        /// 使用事务批量插入sn，key行
        /// </summary>
        /// <param name="dbPath"></param>
        /// <param name="keys"></param>
        /// <returns></returns>
        static public bool InsertKeys(string dbPath, List<object[]> keys)
        {
            DbTransaction trans = null;
            try
            {
                SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
                if (sqliteConn.State != System.Data.ConnectionState.Open)
                {
                    sqliteConn.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = sqliteConn;
                    trans = sqliteConn.BeginTransaction();
                    foreach (var key in keys)
                    {
                        cmd.CommandText = string.Format("insert into keys(sn,keys) values('{0}','{1}')", key[0], key[1]);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                sqliteConn.Close();
                sqliteConn.Dispose();
                return true;
            }
            catch(Exception ex)
            {
                if (trans != null)
                    trans.Rollback();
                Log.WriteErrorLog("\r\nFail to transfer key to DB:\r\n" + ex.Message);
                return false;
            }            
        }

        /// <summary>
        /// 插入一条sn/key数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="sn"></param>
        /// <param name="keydata"></param>
        /// <returns></returns>
        static public bool Insertonekey(SQLiteConnection sqliteConn,string sn,string keydata)
        {
            DbTransaction trans = null;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                trans = sqliteConn.BeginTransaction();
                cmd.CommandText = string.Format("insert into keys(sn,keys,copy_date) values('{0}','{1}','{2}')", sn, keydata, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.ExecuteNonQuery();
                trans.Commit();
                return true;
            }
            catch (Exception ex)
            {
                if (trans != null)
                    trans.Rollback();
                Log.WriteErrorLog("\r\nFail to transfer key to DB:\r\n" + ex.Message);
                return false;
            }
        }

        static public bool CheckDownloadStatus(string dbPath,string bid)
        {
            SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
            if (sqliteConn.State != System.Data.ConnectionState.Open)
            {              
                sqliteConn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("select * from mid where bid='{0}'and status='1'", bid);
                using (SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if(dr.Read())
                    {
                        return true;
                    }
                }
            }
            sqliteConn.Close();
            sqliteConn.Dispose();
            return false;
        }

        static public bool WholeCheckDownloadStatus(SQLiteConnection sqliteConn, string bid)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("select * from mid where bid='{0}'and status='1'", bid);
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                dr.Close();
                return true;
            }
            dr.Close();
            return false;
        }

        /// <summary>
        /// 获取DB文件内的key数据（json格式）
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="SN"></param>
        /// <param name="copydone"></param>
        /// <returns></returns>
        static public string Getkeys(SQLiteConnection sqliteConn, string SN,out bool copydone)
        {
            copydone = false;
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("select * from keys where sn='{0}'", SN);
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                NameValueCollection user = dr.GetValues();
                string values = user.Get("keys");
                if (user.Get("copy_date").Length>0)
                    copydone = true;
                dr.Close();
                return values;
            }
            dr.Close();
            Log.WriteErrorLog("\r\nFail to Get keys to DB:\r\n" + cmd.CommandText);
            return "";
        }

        /// <summary>
        /// 获取DB文件内的roku抄写数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="order"></param>
        /// <param name="rokuCustomer"></param>
        /// <returns></returns>
        static public bool GetrokuCustomer(SQLiteConnection sqliteConn, string order, out RokuCustomer rokuCustomer)
        {
            rokuCustomer = null;
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("select * from rokuCustomer where ordernum='{0}'", order);
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                NameValueCollection user = dr.GetValues();
                rokuCustomer = V2Method.GetRokuInfo(user.Get("data"));
                dr.Close();
                return true;
            }
            dr.Close();
            return false;
        }

        /// <summary>
        /// 获取DB文件内的dsn数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="order"></param>
        /// <param name="rokuCustomer"></param>
        /// <returns></returns>
        static public bool Getdsn(SQLiteConnection sqliteConn, string order, out string dsn)
        {
            dsn = null;
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("select * from dsn where ordernum='{0}'", order);
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {

                NameValueCollection user = dr.GetValues();
                dsn = user.Get("dsn");
                dr.Close();
                return true;
            }
            dr.Close();
            return false;
        }

        /// <summary>
        /// 获取DB文件内的白平衡数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="order"></param>
        /// <param name="whiteBalanceInfo"></param>
        /// <returns></returns>
        static public bool Getwhitebalance(SQLiteConnection sqliteConn, string order, out WhiteBalanceInfo whiteBalanceInfo)
        {
            whiteBalanceInfo = null;
            try
            {               
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("select * from whitebalance where ordernum='{0}'", order);
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    whiteBalanceInfo = new WhiteBalanceInfo()
                    {
                        ordernum = user.Get("ordernum"),
                        hdmirgain = user.Get("hdmirgain"),
                        hdmiggain = user.Get("hdmiggain"),
                        hdmibgain = user.Get("hdmibgain"),
                        nrgain = user.Get("nrgain"),
                        nggain = user.Get("nggain"),
                        nbgain = user.Get("nbgain"),
                        lrgain = user.Get("lrgain"),
                        lggain = user.Get("lggain"),
                        lbgain = user.Get("lbgain")
                    };
                    dr.Close();
                    return true;
                }
                dr.Close();
                return false;
            }
            catch(Exception ex)
            {
                Log.WriteErrorLog(ex.Message);
                return false;
            }
        }

        static public bool GetDBMidInfo(SQLiteConnection sqliteConn, string bid,out MidAddress MidAddress1)
        {
            MidAddress1 = new MidAddress();
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("select * from mid where bid='{0}'", bid);
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                NameValueCollection user = dr.GetValues();
                MidAddress1.order = user.Get("bid");
                MidAddress1.pid= user.Get("pid");
                MidAddress1.des = user.Get("des");
                MidAddress1.number = user.Get("number");
                MidAddress1.ctype = user.Get("ctype");
                MidAddress1.purl = user.Get("purl");
                MidAddress1.psize = user.Get("psize");
                MidAddress1.pmd5 = user.Get("pmd5");
                MidAddress1.version = user.Get("version");
                MidAddress1.host = user.Get("host");
                dr.Close();
                return true;
            }
            dr.Close();
            return false;
        }

        static public bool UpdateCopyStatus(SQLiteConnection sqliteConn,string SN,out string copydate)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = "select datetime(CURRENT_TIMESTAMP,'localtime')";
            SQLiteDataReader dr = cmd.ExecuteReader();
            copydate = "";
            if (dr.Read())
            {
                NameValueCollection user = dr.GetValues();
                copydate = user.Get("datetime(CURRENT_TIMESTAMP,'localtime')");
            }
            dr.Close();
            cmd.CommandText = string.Format("update keys set copy_date='"+ copydate+ "'where sn='{0}'", SN);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0 && copydate.Length > 0)
                return true;
            else
                return false;
        }

        static public bool UpdateReportStatus(SQLiteConnection sqliteConn, string SN)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("update keys set report_date=datetime(CURRENT_TIMESTAMP,'localtime') where sn='{0}'", SN);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                return true;
            else
                return false;
        }

        static public bool UpdateReportListStatus(SQLiteConnection sqliteConn, List<string> SNs)
        {
            DbTransaction trans = null;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                trans = sqliteConn.BeginTransaction();
                foreach (var SN in SNs)
                {
                    cmd.CommandText = string.Format("update keys set report_date=datetime(CURRENT_TIMESTAMP,'localtime') where sn='{0}'", SN);
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                cmd.Dispose();
                return true;
            }
            catch(Exception ex)
            {
                return false;
            }
        }

        static public bool InsertDelayCopyReport(SQLiteConnection sqliteConn, object[] param)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("insert into CopyDelayReport(bid,url,content) values('{0}','{1}','{2}')", param[0], param[1], param[2]);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                return true;
            else
                return false;
        }

        static public bool DeleteDelayCopyReport(SQLiteConnection sqliteConn, string ID)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("Delete from CopyDelayReport where ID='{0}'",ID);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                return true;
            else
                return false;
        }

        static public bool IsErrorReportExist(SQLiteConnection sqliteConn, string url, string content)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("select count from ErrorReport where url='{0}' and content='{1}'", url, content);
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    string values = user.Get("count");
                    int count = Convert.ToInt32(values);
                    dr.Close();

                    if (count > 0)
                        return true;
                    cmd.Dispose();
                    return false;
                }
                dr.Close();
                cmd.Dispose();
                return false;
            }
            catch (Exception ex)
            {
                Log.WriteErrorLog("\r\nGet Report Count Error:" + ex.Message);
                return false;
            }
        }

        static public bool InsertDelayErrorReport(SQLiteConnection sqliteConn, object[] param)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            if (param[0].ToString().EndsWith("smes/RecordKey"))
            {
                if (IsErrorReportExist(sqliteConn, param[0].ToString(), param[1].ToString()))
                {
                    // 记录存在,退出;
                    return true;
                }
            }
            cmd.CommandText = string.Format("insert into ErrorReport(url,content) values('{0}','{1}')", param[0], param[1]);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                return true;
            else
                return false;
        }
        public static bool CheckProductionNum(SQLiteConnection sqliteConn, string order)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("select * from ProductionCount where bid='{0}'", order);
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                dr.Close();
                return true;
            }
            dr.Close();
            cmd.CommandText = string.Format("insert into ProductionCount(bid,count) values('{0}','{1}')", order, "0");
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                return true;
            else
                return false;
        }

        public static bool UpdateProductionNum(SQLiteConnection sqliteConn, string order)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("select count from ProductionCount where bid='{0}'", order);
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    string values = user.Get("count");
                    string count = (Convert.ToInt32(values) + 1).ToString();
                    dr.Close();
                    cmd.CommandText = string.Format("update ProductionCount set count ='{0}' where bid='{1}'", count, order);
                    int result = cmd.ExecuteNonQuery();
                    
                    if (result > 0)
                        return true;
                    else
                        Log.WriteErrorLog("\r\nFail to update ProductionCount from db:\r\n" + cmd.CommandText);
                    cmd.Dispose();
                    return false;
                }
                dr.Close();
                Log.WriteErrorLog("\r\nFail to get ProductionCount from db:\r\n" + cmd.CommandText);
                cmd.Dispose();
                return false;
            }
            catch(Exception ex)
            {
                Log.WriteErrorLog("\r\nFail to UpdateProductionNum:" + ex.Message);
                return false;
            }            
        }

        /// <summary>
        /// 获取本地DB对应订单已经抄写的数量（包含重复抄写）
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static string GetProductionNum(SQLiteConnection sqliteConn, string order)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = string.Format("select count from ProductionCount where bid='{0}'", order);
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    string values = user.Get("count");
                    dr.Close();
                    cmd.Dispose();
                    return values;
                }
                dr.Close();
                cmd.Dispose();
                return "";
            }
            catch (Exception ex)
            {
                Log.WriteErrorLog("\r\nFail to GetProductionNum:" + ex.Message);
                return "";
            }
        }

        /// <summary>
        /// 删除30天之前已经上报的数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <returns></returns>
        public static bool DeleteOldData(SQLiteConnection sqliteConn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "Delete from ErrorReport where report_date is not null and date('now', '-30 day') >= date(report_date)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Delete from CopyDelayReport where report_date is not null and date('now', '-30 day') >= date(report_date)";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                return true;
            }
            catch(Exception ex)
            {
                GC.Collect();
                Log.WriteErrorLog("\r\nFail to DeleteOldData:" + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 获取本地DB记录的未上报异常数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="url"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public static bool GetErrorReportData(SQLiteConnection sqliteConn,out string url,out string content,out string id)
        {
            url = "";
            content = "";
            id = "";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select url,content,ID from ErrorReport where report_date is null LIMIT 1";
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    url = user.Get("url");
                    content = user.Get("content");
                    id = user.Get("ID");
                    dr.Close();                    
                    return true;
                }
                dr.Close();
                return false;
            }
            catch(Exception ex)
            {
                GC.Collect();
                Log.WriteErrorLog("\r\nFail to GetErrorReportData:" + ex.Message);
                return false;
            }            
        }

        /// <summary>
        /// 获取本地DB记录的未上报烧录数据
        /// </summary>
        /// <param name="sqliteConn"></param>
        /// <param name="url"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public static bool GetDelayReportData(SQLiteConnection sqliteConn, out string url, out string content,out string id)
        {
            url = "";
            content = "";
            id = "";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select url,content,ID from CopyDelayReport where report_date is null LIMIT 1";
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    url = user.Get("url");
                    content = user.Get("content");
                    id = user.Get("ID");
                    dr.Close();
                    return true;
                }
                dr.Close();
                return false;
            }
            catch (Exception ex)
            {
                GC.Collect();
                Log.WriteErrorLog("\r\nFail to GetDelayReportData:" + ex.Message);
                return false;
            }
        }

        public static bool GetEppormDataList(SQLiteConnection sqliteConn, out List<PostDateClass> postList,out List<string> SNs)
        {
            postList = new List<PostDateClass>();
            try
            {                
                SQLiteCommand cmd = new SQLiteCommand();
                SNs = new List<string>();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select sn from keys where report_date is null LIMIT 200";
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    string sn = user.Get("sn");
                    SNs.Add(sn);
                    postList.Add(new PostDateClass("sn", sn));
                    postList.Add(new PostDateClass("date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));                                   
                }
                dr.Close();
                return true;
            }
            catch (Exception ex)
            {
                GC.Collect();
                SNs = null;
                Log.WriteErrorLog("\r\nFail to GetEppormData:" + ex.Message);
                return false;
            }
        }

        public static bool GetEppormDataListV2(SQLiteConnection sqliteConn, out List<string> listSN, out List<string> listCopydates)
        {
            try
            {
                listSN = new List<string>();
                listCopydates = new List<string>();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select sn,copydate from keys where report_date is null LIMIT 200";
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    listSN.Add(user.Get("sn"));
                    listCopydates.Add(user.Get("copydate").ToString());
                }
                dr.Close();
                return true;
            }
            catch (Exception ex)
            {
                GC.Collect();
                listSN = null;
                listCopydates = null;
                Log.WriteErrorLog("\r\nFail to GetEppormData:" + ex.Message);
                return false;
            }
        }

        public static bool GetEppormDataCount(SQLiteConnection sqliteConn, out string count)
        {
            count = "";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select count(*) from keys";
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    count = user.Get("count(*)");
                    dr.Close();
                    return true;
                }
                dr.Close();
                return false;
            }
            catch (Exception ex)
            {
                GC.Collect();
                Log.WriteErrorLog("\r\nFail to GetEppormDataCount:" + ex.Message);
                return false;
            }

        }

        public static bool GetEppormUploadedDataCount(SQLiteConnection sqliteConn, out string count)
        {
            count = "";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select count(*) from keys where report_date is not null";
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    count = user.Get("count(*)");
                    dr.Close();
                    return true;
                }
                dr.Close();
                return false;
            }
            catch (Exception ex)
            {
                GC.Collect();
                Log.WriteErrorLog("\r\nFail to GetEppormDataCount:" + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 更新本地DB记录的未上报数据
        /// </summary>
        public static bool UpdateReportData(SQLiteConnection sqliteConn,string table,string column,string data)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConn;
            cmd.CommandText = string.Format("update {0} set report_date = datetime(CURRENT_TIMESTAMP,'localtime') where {1}= '{2}'", table, column, data);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                return true;
            else
                return false;
        }

        public static bool AddReportOnlineData(SQLiteConnection connection, string url, string content)
        {
            if (connection == null)
                return false;

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = connection;
            cmd.CommandText = string.Format("insert into ReportData(url,content) values('{0}','{1}')", url, content);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();

            return result > 0 ? true : false;
        }

        public static bool GetReportData(SQLiteConnection sqliteConn, out string id, out string url, out string content)
        {
            url = "";
            content = "";
            id = "";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = sqliteConn;
                cmd.CommandText = "select id, url, content from ReportData Order by random() limit 1";
                SQLiteDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    NameValueCollection user = dr.GetValues();
                    url = user.Get("url");
                    content = user.Get("content");
                    id = user.Get("id");
                    dr.Close();
                    return true;
                }
                dr.Close();
                return false;
            }
            catch (Exception ex)
            {
                GC.Collect();
                Log.WriteErrorLog("\r\nFail to GetReportData:" + ex.Message);
                return false;
            }
        }
    }
}