private static void ReadFlags(ExcelTable flagsTable, StringBuilder sb)
{
foreach (var row in flagsTable.Rows)
{
var code = CastTo(row[0], 0);
var typeCode = CastStrTo(row[1], 255);
var shortName = CastTo(row[2], "UNSP");
var color = CastTo(row[3], "#FFFFFF");
var reportMarker = shortName;// CastStrTo("UNSP");
const int system = 1;
var typeName = CastStrTo(row[4], 255);
var description = CastStrTo(row[5], 1024);
if (code < 0)
{
Console.WriteLine("[Warn ] flag is ignored, which code is '" + code + "', typecode is '" + typeCode + "'!");
continue;
}
sb.AppendFormat(@"SET @FlagDataId = 0;
");
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM Flag WHERE TypeCode={0} AND Code={1};
", typeCode, code);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" SELECT @FlagDataId=FlagID FROM Flag WHERE TypeCode={0} AND Code={1};
", typeCode, code);
sb.AppendFormat(@" UPDATE Flag SET shortName={2},color={3},reportMarker={4},system={5},lastModified={6} WHERE TypeCode={0} AND Code={1};
", typeCode, code, shortName, color, reportMarker, system, CurrentDateTime);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" SELECT @FlagDataId=Max(FlagID)+1 FROM Flag;
");
sb.AppendFormat(@" INSERT INTO Flag (FlagID,TypeCode,Code,shortName,color,reportMarker,system,lastModified) VALUES (@FlagDataId, {0},{1},{2},{3},{4},{5},{6});
", typeCode, code, shortName, color, reportMarker, system, CurrentDateTime);
sb.AppendFormat(@"END
");
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM FlagLoc WHERE FlagId=@FlagDataId AND LanguageID={0};
", LanuguageId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE FlagLoc SET TypeName={1},Description={2} WHERE FlagId=@FlagDataId AND LanguageID={0};
", LanuguageId, typeName, description);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO FlagLoc (FlagId,LanguageID,TypeName,Description) VALUES (@FlagDataId, {0},{1},{2});
", LanuguageId, typeName, description);
sb.AppendFormat(@"END
");
}
}
private static void ReadUnitsGroup(ExcelTable unitsGroupTable, StringBuilder sb)
{
foreach (var row in unitsGroupTable.Rows)
{
var unitGroupId = CastStrTo(row[1], 50);
var baseUnitId = CastStrTo(row[2], 50);
var dimLength = CastTo(row[3], 0);
var dimMass = CastTo(row[4], 0);
var dimTime = CastTo(row[5], 0);
var dimCurrent = CastTo(row[6], 0);
var dimTemperature = CastTo(row[7], 0);
var dimSubstance = CastTo(row[8], 0);
var dimIntensity = CastTo(row[9], 0);
var system = CastTo(row[10], 1);
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM UnitGroup WHERE UnitGroupID={0};
", unitGroupId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE UnitGroup SET BaseUnitId={1},DimLength={2},dimMass={3},dimTime={4},dimCurrent={5},DimTemperature={6},dimSubstance={7},dimIntensity={8},system={9},lastModified={10} WHERE UnitGroupID={0};
", unitGroupId, baseUnitId, dimLength, dimMass, dimTime, dimCurrent, dimTemperature, dimSubstance, dimIntensity, system, CurrentDateTime);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO UnitGroup (UnitGroupID,BaseUnitId,DimLength,dimMass,dimTime,dimCurrent,DimTemperature,dimSubstance,dimIntensity,system,lastModified) VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10});
", unitGroupId, baseUnitId, dimLength, dimMass, dimTime, dimCurrent, dimTemperature, dimSubstance, dimIntensity, system, CurrentDateTime);
sb.AppendFormat(@"END
");
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM UnitGroupLoc WHERE UnitGroupID={0} AND LanguageID={1}
", unitGroupId, LanuguageId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE UnitGroupLoc SET Name={2} WHERE UnitGroupID={0} AND LanguageID={1};
", unitGroupId, LanuguageId, unitGroupId);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO UnitGroupLoc (UnitGroupID,LanguageID,Name) VALUES ({0},{1},{2});
", unitGroupId, LanuguageId, unitGroupId);
sb.AppendFormat(@"END
");
}
}
private static void ReadUnits(ExcelTable unitsTable, StringBuilder sb)
{
foreach (var row in unitsTable.Rows)
{
var unitId = CastStrTo(row[1], 50);
var unitGroupId = CastStrTo(row[2], 50);
var baseMultiplier = CastTo(row[3], 1.0);
var baseOffset = CastTo(row[4], 0.0);
var system = CastTo(row[5], 1);
var @base = CastStrTo(row[7], 20);
var comment = CastStrTo(row[8], 50);
var symbol = @base.Length > 2 ? @base : unitId;
var name = comment.Length > 2 ? comment : symbol;
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM Unit WHERE unitId={0};
", unitId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE Unit SET unitGroupId={1},baseMultiplier={2},baseOffset={3},system={4},lastModified={5} WHERE unitId={0};
", unitId, unitGroupId, baseMultiplier, baseOffset, system, CurrentDateTime);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO Unit (unitId,unitGroupId,baseMultiplier,baseOffset,system,lastModified) VALUES ({0},{1},{2},{3},{4},{5});
", unitId, unitGroupId, baseMultiplier, baseOffset, system, CurrentDateTime);
sb.AppendFormat(@"END
");
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM UnitLoc WHERE UnitID={0} AND LanguageID={1}
", unitId, LanuguageId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE UnitLoc SET Symbol={2},SingularName={3},PluralName={4} WHERE UnitID={0} AND LanguageID={1};
", unitId, LanuguageId, symbol, name, name);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO UnitLoc (UnitId,LanguageID,Symbol,SingularName,PluralName) VALUES ({0},{1},{2},{3},{4});
", unitId, LanuguageId, symbol, name, name);
sb.AppendFormat(@"END
");
}
}
private static void ReadParameters(ExcelTable paramTable, StringBuilder sb)
{
var idListDict = new Dictionary <string, string>();
foreach (var row in paramTable.Rows)
{
var parameterId = row[0];
var usgsId = row[4];
if (idListDict.ContainsKey(parameterId))
{
idListDict[parameterId] = idListDict[parameterId] + "," + usgsId;
}
else
{
idListDict.Add(parameterId, usgsId);
}
}
var didList = new List <string>();
foreach (var row in paramTable.Rows)
{
var parameterId = CastStrTo(row[0], 50);
if (didList.Contains(parameterId))
{
//We choose the first parameterId if there are duplication in Excel Sheet
continue;
}
var displayId = CastStrTo(row[1], 256);
var defaultUnitId = CastStrTo(row[2], 50);
var unitGroupId = CastStrTo(row[3], 50);
const int defaultInterpolationTypeId = 7;
var usgsIds = idListDict.ContainsKey(row[0]) ? idListDict[row[0]] : row[0];
var name = CastStrTo(usgsIds + " - " + row[9], 256);
const int system = 1;
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM Parameter WHERE ParameterId={0};
", parameterId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE Parameter SET DisplayId={1},UnitGroupId={2},DefaultUnitId={3},DefaultInterpolationTypeId={4},System={5},lastModified={6} WHERE ParameterId={0};
", parameterId, displayId, unitGroupId, defaultUnitId, defaultInterpolationTypeId, system, CurrentDateTime);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO Parameter (ParameterId,DisplayId,UnitGroupId,DefaultUnitId,DefaultInterpolationTypeId,System,lastModified) VALUES ({0},{1},{2},{3},{4},{5},{6});
", parameterId, displayId, unitGroupId, defaultUnitId, defaultInterpolationTypeId, system, CurrentDateTime);
sb.AppendFormat(@"END
");
sb.AppendFormat(@"SELECT @CNT = COUNT(*) FROM ParameterLoc WHERE ParameterId={0} AND LanguageID={1}
", parameterId, LanuguageId);
sb.AppendFormat(@"IF @CNT > 0 BEGIN
");
sb.AppendFormat(@" UPDATE ParameterLoc SET Name={2} WHERE ParameterId={0} AND LanguageID={1};
", parameterId, LanuguageId, name);
sb.AppendFormat(@"END
ELSE BEGIN
");
sb.AppendFormat(@" INSERT INTO ParameterLoc (ParameterId,LanguageID,Name) VALUES ({0},{1},{2});
", parameterId, LanuguageId, name);
sb.AppendFormat(@"END
");
didList.Add(parameterId);
}
}