A Helpful Database Library

using System;
using System.Collections.Generic;
using System.Text;
using LDC_10;
using Settings = LDC_10.Properties.Settings;

namespace LDC_10
{
    internal static class SQL
    {
        internal static string BuildHeaderSQL(ref HdrData _Header)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString()+ "Header";
            _StrSQL = @"INSERT INTO " + tblName + 
                      @"(RecorderID, " +
                      @" GroupID, " +
                      @" Name, " +
                      @" Address)" +
                      @" VALUES" +
                      @" ('" + _Header.RecID + 
                      @"','" + _Header.GrpID + 
                      @"','" + _Header.Name + 
                      @"','" + _Header.Address + "')";
            return _StrSQL;
        }

        internal static string SelectHeaderID(ref HdrData _Header)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Header";
            _StrSQL = @"SELECT RecID FROM " + tblName + " " +
                      @"WHERE RecorderID='" + _Header.RecID + "' " +
                      @"AND GroupID='" + _Header.GrpID + "' " +
                      @"AND Name='" + _Header.Name + "' " +
                      @"AND Address='" + _Header.Address + "'";
            return _StrSQL;
        }

        internal static string CreateHdrTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Header";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                          @" (RecID AUTOINCREMENT " +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY, " +
                          @" RecorderID VARCHAR WITH COMPRESSION, " +
                          @" GroupID VARCHAR WITH COMPRESSION, " +
                          @" Name VARCHAR WITH COMPRESSION, " +
                          @" Address VARCHAR WITH COMPRESSION)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*         SQL          */
                          @" (RecID int IDENTITY " +
                          @" CONSTRAINT PK_" + tblName + "  PRIMARY KEY, " +
                          @" RecorderID varchar, " +
                          @" GroupID varchar, " +
                          @" Name varchar, " +
                          @" Address varchar)";
            }
            return _StrSQL;
        }

        internal static string BuildDataSetSQL(ref DataSet _DataSet, ref HdrData _Header)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "DataSet";
            if (!_Header.Set)
            {
                _StrSQL = @"INSERT INTO " + tblName +
                          @"(RecID," +
                          @" [Start Time]," +
                          @" [Stop Time]," +
                          @" [Total Intervals]," +
                          @" [StrA Meter Readings]," +
                          @" [StpA Meter Reading]," +
                          @" [Meter Multiplier]," +
                          @" [Pulse Multiplier]," +
                          @" Unit," +
                          @" [Requested Output]," +
                          @" [DST Change]," +
                          @" [PT Ratio]," +
                          @" [WYE/Delta]," +
                          @" [File Type])" +
                          @" VALUES " +
                          @"(" + _DataSet.RecID +
                          @",'" + _DataSet.StTime +
                          @"','" + _DataSet.SpTime +
                          @"','" + _DataSet.TotIntv +
                          @"','" + _DataSet.SMReading +
                          @"','" + _DataSet.EMReading +
                          @"','" + _DataSet.MMulti +
                          @"','" + _DataSet.PMulti +
                          @"','" + _DataSet.Unit +
                          @"','" + _DataSet.ReqOut +
                          @"','" + _DataSet.DST +
                          @"','" + _DataSet.PTRatio +
                          @"','" + _DataSet.WYE +
                          @"','" + _DataSet.FType + "')";
            }
            else
            {
                _StrSQL = @"UPDATE " + tblName +
                          @" SET" +
                          @" [StrB Meter Readings]='" + _DataSet.SMReading +
                          @"', [StpB Meter Reading]='" + _DataSet.EMReading +
                          @"' WHERE RecID=" + _DataSet.RecID +
                          @" AND DSID=" + _DataSet.DSID + "";

            }
            return _StrSQL;
        }

        internal static string SelectDSID(ref DataSet _DataSet)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "DataSet";
            _StrSQL = @"SELECT DSID FROM " + tblName + " " +
                      @"WHERE RecID=" + _DataSet.RecID + " " +
                      @"AND [Start Time]='" + _DataSet.StTime + "' " +
                      @"AND [Stop Time]='" + _DataSet.SpTime + "' " +
                      @"AND [Total Intervals]='" + _DataSet.TotIntv + "'";
            return _StrSQL;
        }

        internal static string CreateDSTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "DataSet";
            string tblName2 = DateTime.Now.Year.ToString() + "Header";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                          @" (DSID AUTOINCREMENT " +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY, " +
                          @" RecID INTEGER NOT NULL " +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (RecID), " +
                          @" [Start Time] VARCHAR WITH COMPRESSION, " +
                          @" [Stop Time] VARCHAR WITH COMPRESSION, " +
                          @" [Total Intervals] VARCHAR WITH COMPRESSION, " +
                          @" [StrA Meter Readings] VARCHAR WITH COMPRESSION, " +
                          @" [StpA Meter Reading] VARCHAR WITH COMPRESSION, " +
                          @" [StrB Meter Readings] VARCHAR WITH COMPRESSION, " +
                          @" [StpB Meter Reading] VARCHAR WITH COMPRESSION, " +
                          @" [Meter Multiplier] VARCHAR WITH COMPRESSION, " +
                          @" [Pulse Multiplier] VARCHAR WITH COMPRESSION, " +
                          @" Unit VARCHAR WITH COMPRESSION, " +
                          @" [Requested Output] VARCHAR WITH COMPRESSION, " +
                          @" [DST Change] VARCHAR WITH COMPRESSION, " +
                          @" [PT Ratio] VARCHAR WITH COMPRESSION, " +
                          @" [WYE/Delta] VARCHAR WITH COMPRESSION, " +
                          @" [File Type] VARCHAR WITH COMPRESSION)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName + /*         SQL          */
                          @"(DSID int IDENTITY " +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY, " +
                          @" RecID int NOT NULL " +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (RecID), " + 
                          @" [Start Time] varchar, " + 
                          @" [Stop Time] varchar, " + 
                          @" [Total Intervals] varchar, " + 
                          @" [StrA Meter Readings] varchar, " + 
                          @" [StpA Meter Reading] varchar, " +
                          @" [StrB Meter Readings] varchar, " +
                          @" [StpB Meter Reading] varchar, " + 
                          @" [Meter Multiplier] varchar, " + 
                          @" [Pulse Multiplier] varchar, " + 
                          @" Unit varchar, " + 
                          @" [Requested Output] varchar, " + 
                          @" [DST Change] varchar, " +
                          @" [PT Ratio] varchar, " +
                          @" [WYE/Delta] varchar, " + 
                          @" [File Type] varchar)";
            }
            return _StrSQL;
        }

        internal static string BuildTimeSQL(ref RecTime _Time)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Time";
            _StrSQL = @"INSERT INTO " + tblName +
                      @"(RecID," +
                      @" DSID," + 
                      @" [Month]," + 
                      @" [Day]," + 
                      @" [Year]," + 
                      @" [Hour]," + 
                      @" [Minute])" + 
                      @" VALUES " +
                      @"(" + _Time.RecID + 
                      @"," + _Time.DataSetID + 
                      @"," + _Time.Month + 
                      @"," + _Time.Day + 
                      @"," + _Time.Year + 
                      @"," + _Time.Hour + 
                      @"," + _Time.Minute + ")";
            return _StrSQL;
        }

        internal static string SelectTimeID(ref RecTime _Time)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Time";
            _StrSQL = @"SELECT TimeID FROM " + tblName + " " +
                      @"WHERE RecID=" +  _Time.RecID + " " +
                      @"AND DSID=" +  _Time.DataSetID + " " +
                      @"AND [Month]=" +  _Time.Month + " " +
                      @"AND [Day]=" +  _Time.Day + " " +
                      @"AND [Year]=" +  _Time.Year +" " +
                      @"AND [Hour]=" +  _Time.Hour +" " +
                      @"AND [Minute]=" +  _Time.Minute +"";
            return _StrSQL;
        }

        internal static string CreateTmTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Time";
            string tblName2 = DateTime.Now.Year.ToString() + "DataSet";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                      @"(TimeID AUTOINCREMENT " +
                      @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                      @" RecID INTEGER NOT NULL, " +
                      @" DSID INTEGER NOT NULL " +
                      @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                      @" REFERENCES " + tblName2 + " (DSID)," + 
                      @" [Month] INTEGER," +
                      @" [Day] INTEGER," + 
                      @" [Year] INTEGER," +
                      @" [Hour] INTEGER," + 
                      @" [Minute] INTEGER)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName + /*         SQL          */
                          @"(TimeID int IDENTITY " +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID int NOT NULL, " +
                          @" DSID int NOT NULL " + 
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (DSID)," +
                          @" [Month] int, " + 
                          @" [Day] int, " + 
                          @" [Year] int, " +
                          @" [Hour] int, " + 
                          @" [Minute] int)";
            }
            return _StrSQL;
        }

        internal static string BuildUnitDataSQL(bool _Set, ref UnitData _UnitData, ref RecTime _Rtm)
        {
            string tblName;
            string _StrSQL = null;
            tblName = DateTime.Now.Year.ToString() + "Units";
            if (!_Set)
            {
                _StrSQL = @"INSERT INTO " + tblName +
                          @"(RecID," +
                          @" DSID," + 
                          @" TimeID," + 
                          @" [Unit A]," + 
                          @" [Unit B]," + 
                          @" [Unit C]," + 
                          @" [Unit D])" +
                          @" VALUES " +
                          @"(" + _UnitData.RecID + 
                          @"," + _UnitData.DataSetID + 
                          @"," + _UnitData.TimeID + 
                          @",'" + _UnitData.U_A + 
                          @"','" + _UnitData.U_B + 
                          @"','" + _UnitData.U_C + 
                          @"','" + _UnitData.U_D + "')";
            }
            else if (_Set)
            {
                _StrSQL = @"UPDATE " + tblName +
                          @" SET " +
                          @" [Unit E]='" + _UnitData.U_A + "', " +
                          @" [Unit F]='" + _UnitData.U_B + "', " +
                          @" [Unit G]='" + _UnitData.U_C + "', " +
                          @" [Unit H]='" + _UnitData.U_D + "'"   +
                          @" WHERE TimeID=" + _UnitData.TimeID + "";
            }
            return _StrSQL;
        }

        internal static string CreateUnTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Units";
            string tblName2 = DateTime.Now.Year.ToString() + "Time";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                          @"(UnitID AUTOINCREMENT " +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID INTEGER NOT NULL, " +
                          @" DSID INTEGER NOT NULL, " +
                          @" TimeID INTEGER NOT NULL " +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (TimeID)," + 
                          @" [Unit A] VARCHAR WITH COMPRESSION, " + 
                          @" [Unit B] VARCHAR WITH COMPRESSION, " + 
                          @" [Unit C] VARCHAR WITH COMPRESSION, " +
                          @" [Unit D] VARCHAR WITH COMPRESSION, " +
                          @" [Unit E] VARCHAR WITH COMPRESSION, " +
                          @" [Unit F] VARCHAR WITH COMPRESSION, " + 
                          @" [Unit G] VARCHAR WITH COMPRESSION, " +
                          @" [Unit H] VARCHAR WITH COMPRESSION)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName + /*         SQL          */
                          @"(UnitID int IDENTITY" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID int NOT NULL, " +
                          @" DSID int NOT NULL, " + 
                          @" TimeID int NOT NULL " + 
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (TimeID)," +
                          @" [Unit A] varchar, " + 
                          @" [Unit B] varchar, " + 
                          @" [Unit C] varchar, " + 
                          @" [Unit D] varchar, " +
                          @" [Unit E] varchar, " + 
                          @" [Unit F] varchar, " +
                          @" [Unit G] varchar, " + 
                          @" [Unit H] varchar)";
            }
            return _StrSQL;
        }

        internal static string SelectUnitID(ref UnitData _UnitData)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Units";
            _StrSQL = @"SELECT UnitID FROM " + tblName + " " +
                      @"WHERE RecID=" + _UnitData.RecID + " " +
                      @"AND DSID=" + _UnitData.DataSetID + " " +
                      @"AND TimeID=" +  _UnitData.TimeID + "";
            return _StrSQL;
        }

        internal static string BuildDataRowSQL(bool _Set, ref UnitData _UnitData, ref RecTime _Rtm)
        {
            string tblName;
            string _StrSQL = null;
            tblName = DateTime.Now.Year.ToString() + "Readings";
            if (!_Set)
            {
                if (_UnitData.M_C != 0)
                {
                    _UnitData.M_C = _UnitData.M_C * -1;
                }
                _StrSQL = @"INSERT INTO " + tblName +
                          @"(RecID," +
                          @" DSID," + 
                          @" TimeID," + 
                          @" UnitID," + 
                          @" [Meter A]," + 
                          @" [Meter B]," + 
                          @" [Meter C]," + 
                          @" [Meter D])" +
                          @" VALUES " +
                          @"(" + _UnitData.RecID + 
                          @"," + _UnitData.DataSetID + 
                          @"," + _UnitData.TimeID + 
                          @"," + _UnitData.UnitID + 
                          @"," + _UnitData.M_A + 
                          @"," + _UnitData.M_B + 
                          @"," + _UnitData.M_C + 
                          @"," + _UnitData.M_D + ")";
            }
            else if (_Set)
            {
                _StrSQL = @"UPDATE " + tblName +
                          @" SET " +
                          @" [Meter E]=" + _UnitData.M_A + "," + 
                          @" [Meter F]=" + _UnitData.M_B + "," +
                          @" [Meter G]=" + _UnitData.M_C + "," +
                          @" [Meter H]=" + _UnitData.M_D + 
                          @" WHERE UnitID=" + _UnitData.UnitID + "";
            }
            return _StrSQL;
        }

        internal static string CreateRwTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "Readings";
            string tblName2 = DateTime.Now.Year.ToString() + "Units";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                          @"(MeterID AUTOINCREMENT" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID INTEGER NOT NULL, " +
                          @" DSID INTEGER NOT NULL," +
                          @" TimeID INTEGER NOT NULL," +
                          @" UnitID  INTEGER NOT NULL" +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (UnitID), " +
                          @" [Meter A] INTEGER, " + 
                          @" [Meter B] INTEGER, " + 
                          @" [Meter C] INTEGER, " + 
                          @" [Meter D] INTEGER, " +
                          @" [Meter E] INTEGER, " + 
                          @" [Meter F] INTEGER, " +
                          @" [Meter G] INTEGER, " +
                          @" [Meter H] INTEGER)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName + /*         SQL          */
                          @"(MeterID int IDENTITY" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID int NOT NULL, " +
                          @" DSID int NOT NULL," +
                          @" TimeID int NOT NULL," +
                          @" UnitID int NOT NULL " +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (UnitID), " +
                          @" [Meter A] int, " +
                          @" [Meter B] int, " +
                          @" [Meter C] int, " +
                          @" [Meter D] int, " +
                          @" [Meter E] int, " +
                          @" [Meter F] int, " +
                          @" [Meter G] int, " +
                          @" [Meter H] int)";
            }
            return _StrSQL;
        }

        internal static string CreateHourlyTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "HourlyData";            
            string tblName2 = DateTime.Now.Year.ToString() + "Header";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                          @"(HourID AUTOINCREMENT" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID INTEGER NOT NULL " +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName + 
                          @" REFERENCES " + tblName2 + " (RecID), " +
                          @" DSID INTEGER NOT NULL," +
                          @" [Month] INTEGER," +
                          @" [Day] INTEGER," + 
                          @" [Year] INTEGER," +
                          @" [Hour] INTEGER," + 
                          @" KW INTEGER," +
                          @" KVAR INTEGER," +
                          @" CAP INTEGER)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           SQL            */
                          @"(HourID int IDENTITY" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" RecID int NOT NULL " +
                          @" CONSTRAINT FK_" + tblName2 + "_" + tblName +
                          @" REFERENCES " + tblName2 + " (RecID), " +
                          @" DSID int NOT NULL," +
                          @" [Month] Iint," +
                          @" [Day] int," +
                          @" [Year] int," +
                          @" [Hour] int," +
                          @" KW int," +
                          @" KVAR int," +
                          @" CAP int)";
            }
            return _StrSQL;
        }

        internal static string BuildHourlySQL(ref HourlyData _HrData)
        {
            string tblName;
            string _StrSQL;
            tblName = DateTime.Now.Year.ToString() + "HourlyData";
            _StrSQL = @"INSERT INTO " + tblName +
                      @" (RecId," +
                      @" DSID," +
                      @" [Month]," +
                      @" [Day]," +
                      @" [Year]," +
                      @" [Hour]," +
                      @" KW," +
                      @" KVAR," +
                      @" CAP)" +
                      @" VALUES " +
                      @"(" + _HrData.RecID +
                      @", " + _HrData.DataSetID +
                      @", " + _HrData.Month +
                      @", " + _HrData.Day +
                      @", " + _HrData.Year +
                      @", " + _HrData.Hour +
                      @", " + _HrData.RA_Tot +
                      @", " + _HrData.RB_Tot +
                      @", " + _HrData.RC_Tot + ")";
            return _StrSQL;
        }

        internal static string CreateLogTable(string _Provider)
        {
            string tblName;
            string _StrSQL;
            tblName = "LDC10_LogData";
            if (_Provider == "Microsoft.Jet.OLEDB.4.0")
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           ACCESS            */
                          @"(LogID AUTOINCREMENT" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" [LDC-10 Messages] VARCHAR WITH COMPRESSION," +
                          @" [Processed Files] VARCHAR WITH COMPRESSION, " +
                          @" [Time] TIMESTAMP)";
            }
            else
            {
                _StrSQL = @"CREATE TABLE " + tblName +/*           SQL            */
                          @"(LogID int IDENTITY" +
                          @" CONSTRAINT PK_" + tblName + " PRIMARY KEY," +
                          @" [LDC-10 Messages] varchar," +
                          @" [Processed Files] varchar," +
                          @" [Time] TIMESTAMP)";
            }
            return _StrSQL;
        }

        internal static string BuildLogSQL(string _Message, string _FileName)
        {
            string tblName;
            string _StrSQL;
            tblName = "LDC10_LogData";
            if (_FileName == null || _FileName == "")
            {
                _FileName = "----";
            }
            _StrSQL = @"INSERT INTO " + tblName +
                      @"([LDC-10 Messages]," +
                      @" [Processed Files]," +
                      @" [Time])" +
                      @" VALUES " +
                      @"('" + _Message +
                      @"','" + _FileName + 
                      @"','" + DateTime.Now + "')";
            return _StrSQL;
        }

        internal static string SelectFile(string _FileName)
        {
            string _StrSQL;
            _StrSQL = @"SELECT * " +
                      @"FROM LDC10_LogData " +
                      @"WHERE [Processed Files] ='" + _FileName + "'";
            return _StrSQL;
        }
    }
}