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;
}
}
}