OTS/OTSIncAMeasureApp/0-OTSModel/Measure/DBDataTransition/GenInfoDB.cs

using OTSModelSharp.DTLBase;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OTSModelSharp
{
    public enum SYSTEMTIME
    {
        wYear = 0,
        wMonth = 1,
        wDayOfWeek = 2,
        wDay = 3,
        wHour = 4,
        wMinute = 5,
        wSecond = 6,
        wMilliseconds = 7
    };
    public  class CGenInfoDB : CSQLiteDB
    {

        String m_sFileVersion;
        const string g_sTableItemNameCreateTime = "CreateTime";
        const String g_sTableItemNameTimeStart="TimeStart";
        const String g_sTableItemNameTimeEnd="TimeEnd";
        const String g_sTableItemNameResultStatus="ResultStatus";

        const String g_sTableItemNameFileVersion="FileVersion";
        const string g_sDBFileVersion = "2.0";
        public CGenInfoDB(IDBStoreBase store, CSQLiteTable _table) : base(store, _table)
        {

        }

        public new bool  Init( bool a_clean=false)
	{
		base.Init();

            if (!InsertTimeStampRow(g_sTableItemNameCreateTime,""))
            {
            }
            if (!InsertStringRow(g_sTableItemNameFileVersion, g_sDBFileVersion, ""))
            {

               
            }
            if (!InsertTimeStampRow(g_sTableItemNameTimeStart, ""))
            {
            }
            if (!InsertTimeStampRow(g_sTableItemNameTimeEnd, ""))
            {
            }
            string resultSta="0";//in default ,initialize it to 0,it will be updated in the running process.
            if (!InsertStringRow(g_sTableItemNameResultStatus, resultSta,""))
            {
              
               
            }
            m_sFileVersion = g_sDBFileVersion;

            return true;

	}
      public  String GetFileVersion()
        {
            //System.Data.DataTable query;
            m_sFileVersion.Trim();
            if (m_sFileVersion=="")
            {
                // read in file version if it is empty
                var datastorePtr = GetDatastore();
               

                var tableInfoPtr = GetTableInfo();
               

                var sItemName = tableInfoPtr.GetColumnName((int)CGenInfoTable.ColumnID.ITEM - (int)CGenInfoTable.ColumnID.MIN);
                String sSQLCommand = string.Format("SELECT * FROM \'{0}\' WHERE {1} = \'{2}\';",
                    (String)tableInfoPtr.GetTableName(),
                    sItemName,
                    (String)g_sTableItemNameFileVersion);

               

               var query = datastorePtr.QueryByCommand(sSQLCommand);

              return  query.GetColValue(0);
            }

            return m_sFileVersion;
        }

       public String GetTableItemNameTimeStart()
        {
            return g_sTableItemNameTimeStart;
        }
       public String GetTableItemNameTimeEnd()
        {
            return g_sTableItemNameTimeEnd;
        }
        public String GetTableItemNameResultStatus()
        {
            return g_sTableItemNameResultStatus;
        }
        public bool InsertRow(string a_sItemName, string a_sItemContent, string a_sItemComment /*= nullptr*/)
        {
            var datastorePtr = GetDatastore();
            var t = GetTableInfo();
            String sComment = "NULL";
           

            String sFormat = t.GetInsertCommandFormatString(true);

            String sSQLCommand = 
            string.Format(sFormat,
                a_sItemName,
                a_sItemContent,
                sComment);


            datastorePtr.RunCommand(sSQLCommand);
            return true;

        }

        public bool InsertStringRow(string a_sItemName, string a_sItemContent, string a_sItemComment /*= nullptr*/)
        {
           

            if (!InsertRow(a_sItemName, a_sItemContent, a_sItemComment))
            {

                return false;
            }

            return true;
        }

        public bool InsertIntegerRow(string a_sItemName, int a_nItemContent, string a_sItemComment /*= nullptr*/)
        {
            String sContent = string.Format("{0:G}", a_nItemContent);


            return InsertRow(a_sItemName, sContent, a_sItemComment);
        }

        public bool InsertDoubleRow(string a_sItemName, double a_dItemContent, string a_sItemComment /*= nullptr*/)
        {
            String sContent =  string.Format("{0:G}", a_dItemContent);

            return InsertRow(a_sItemName, sContent, a_sItemComment);
        }

        public bool InsertTimeStampRow(string a_sItemName, string a_sItemComment /*= nullptr*/)
        {
            //DateTime m_time = DateTime.Now;
            //GetLocalTime(m_time);

            string sDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            //string.Format( "{0:G}", m_time);
           
            return InsertRow(a_sItemName, sDateTime, a_sItemComment);

        }

        public bool UpdateRow(string a_sItemName, string a_sItemContent, string a_sItemComment /*= nullptr*/)
        {

         
            var tableInfo = GetTableInfo();
            var store = GetDatastore();
                    

            List<int> vColIndexes = new List<int>();
            var nItemIndex = (int)(CGenInfoTable.ColumnID.ITEM - (int)CGenInfoTable.ColumnID.MIN);

            vColIndexes.Add((int)CGenInfoTable.ColumnID.CONTENT - (int)CGenInfoTable.ColumnID.MIN);
            vColIndexes.Add((int)CGenInfoTable.ColumnID.COMMENT - (int)CGenInfoTable.ColumnID.MIN);

            String sFormat = tableInfo.GetUpdateCommandFormatString(vColIndexes, nItemIndex);
            String sSQLCommand = string.Format(sFormat,
                a_sItemContent,
                a_sItemComment,
                a_sItemName);


            return store.RunCommand(sSQLCommand);

        }

        public bool UpdateStringRow(string a_sItemName, string a_sItemContent, string a_sItemComment /*= nullptr*/)
        {
            String sContent = "";
            string.Format("\"{0:G}\"", a_sItemContent);

            if (!UpdateRow(a_sItemName, sContent, a_sItemComment))
            {

                return false;
            }

            return true;
        }

        public bool UpdateIntegerRow(string a_sItemName, int a_nItemContent, string a_sItemComment /*= nullptr*/)
        {
            String sContent = "";
            string.Format("{0:G}", a_nItemContent);

            return UpdateRow(a_sItemName, sContent, a_sItemComment);
        }

        public bool UpdateDoubleRow(string a_sItemName, double a_dItemContent, string a_sItemComment /*= nullptr*/)
        {
            String sContent = "";
            string.Format("{0:G}", a_dItemContent);

            return UpdateRow(a_sItemName, sContent, a_sItemComment);
        }

        public bool UpdateTimeStampRow(string a_sItemName, string a_sItemComment /*= nullptr*/)
        {
            DateTime m_time = DateTime.Now;

            string sDateTime = m_time.ToString("yyyy-MM-dd HH:mm:ss");

            return UpdateRow(a_sItemName, sDateTime, a_sItemComment);
        }

        public bool DeleteRow(string a_sItemName)
        {
            System.Data.DataTable query;
            var datastorePtr = GetDatastore();

            var tableInfoPtr = GetTableInfo();
            var sNameColumnName = tableInfoPtr.GetColumnName((int)CGenInfoTable.ColumnID.ITEM - (int)CGenInfoTable.ColumnID.MIN);
            String sSQLCommand = "";
            string.Format("DELETE FROM \'{0}\' WHERE {1} = \'{2}\'", (string)tableInfoPtr.GetTableName(), sNameColumnName, a_sItemName);

            //var helper = new SQLiteHelper(datastorePtr);

            return datastorePtr.RunCommand(sSQLCommand);

        }

        public bool GetStringValue(string a_sItemName,ref String a_sString)
        {
            //System.Data.DataTable query;
            var datastorePtr = GetDatastore();

            var tableInfoPtr = GetTableInfo();

            var sNameColumnName = tableInfoPtr.GetColumnName((int)CGenInfoTable.ColumnID.ITEM - (int)CGenInfoTable.ColumnID.MIN);
            String sSQLCommand = "";
            sSQLCommand=string.Format("SELECT * FROM \'{0}\' WHERE {1} = \'{2}\'",
                (string)tableInfoPtr.GetTableName(),
                sNameColumnName,
                a_sItemName);


            //var helper = new SQLiteHelper(datastorePtr);

            var q = datastorePtr.QueryByCommand(sSQLCommand);

            a_sString = q.GetColValue((int)CGenInfoTable.ColumnID.CONTENT - (int)CGenInfoTable.ColumnID.MIN);

            return true;
        }



        public bool GetIntValue(string a_sItemName, int a_nValue)
        {
            System.Data.DataTable query;
            var datastorePtr = GetDatastore();

            var tableInfoPtr = GetTableInfo();

            var sNameColumnName = tableInfoPtr.GetColumnName((int)CGenInfoTable.ColumnID.ITEM - (int)CGenInfoTable.ColumnID.MIN);
            String sSQLCommand = "";
            string.Format("SELECT * FROM \'{0}\' WHERE {1}= \'{2}\'",
                (string)tableInfoPtr.GetTableName(),
                sNameColumnName,
                a_sItemName);


            //var helper = new SQLiteHelper(datastorePtr);

            var q = datastorePtr.QueryByCommand(sSQLCommand);

            int nCol = (int)CGenInfoTable.ColumnID.CONTENT - (int)CGenInfoTable.ColumnID.MIN;
            a_nValue = q.GetColIntValue(nCol, 0);

            return true;
        }
    }
}