private static bool UpdateFromV094ToV95(Configuration oldVersion)
{
if (string.Compare(oldVersion.Value.ToString().Trim(), "0.9.4") == 0)
{
try
{
DbHelper.AddColumn("Tasks", "IsActive", "Bit");
DbHelper.AddColumn("Tasks", "IconId", "Integer");
DbHelper.ExecuteNonQuery("Update Tasks Set IsActive = 1, IconId = " + IconsManager.DefaultTaskIconId);
Application.DoEvents();
DbHelper.ExecuteNonQuery(
"UPDATE Tasks INNER JOIN DefaultTasks ON Tasks.DefaultTaskId = DefaultTasks.Id SET Tasks.IsActive = DefaultTasks.IsActive, Tasks.IconId = DefaultTasks.Icon, Tasks.Description = DefaultTasks.Description");
Application.DoEvents();
DbHelper.DeleteTable("DefaultTasks");
Application.DoEvents();
DbHelper.DeleteConstraint("Tasks", "IsDefaultTask");
DbHelper.DeleteConstraint("Tasks", "DefaultTaskId");
DbHelper.DeleteColumn("Tasks", "IsDefaultTask");
DbHelper.DeleteColumn("Tasks", "DefaultTaskId");
Application.DoEvents();
DbHelper.ExecuteNonQuery("Delete from Tasks where Description = 'Idle'");
Application.DoEvents();
ConfigurationHelper.SaveConfiguration(new Configuration(ConfigurationKey.DataBaseVersion, "0.9.5"));
return(true);
}
catch (OleDbException ex)
{
Logger.WriteException(ex);
return(false);
}
}
return(false);
}
/// <summary>
/// POI 数据填充区号
/// </summary>
public void WritePoiDirect()
{
var tables = GetTables("poi");
var dbHelper = new DbHelper(this.con);
var connection = new NpgsqlConnection(con);
connection.Open();
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
foreach (KeyValuePair <string, ProviceCity> keyValuePair in dictionary)
{
var provice = keyValuePair.Value.Provice;
var cityName = keyValuePair.Value.City;
var table = keyValuePair.Value.CityPinyin + "_poi";
if (!tables.Contains(table))
{
continue;
}
dbHelper.AddColumn("districtname", table);
Console.WriteLine(cityName);
var cityList = CityConfig.GetInstance().getCityByName(cityName);
if (cityList.Count != 1)
{
Console.WriteLine(string.Format("{0}重复", cityName));
continue;
}
var code = cityList[0].baiduCode;
if (cityName == "长沙")
{
code = "158";
}
var districts = new Dictionary <string, string>(); // code name
command.CommandText = string.Format(
"SELECT area_code,area_name,gid FROM CITY WHERE parent_code = '{0}'",
code);
var read = command.ExecuteReader();
while (read.Read())
{
districts[read.GetInt32(2).ToString()] = read.GetString(1);
}
read.Close();
var sql = @" UPDATE {0} set districtName = '{1}' WHERE GID IN (
SELECT m.gid FROM {0} as m , city as c
where st_intersects(st_setsrid(m.geom,4326),st_setsrid(c.geom,4326)) and c.gid = {2}
) ";
districts.Keys.ToList().ForEach(
m =>
{
command.CommandText = (string.Format(sql, table, districts[m], m));
command.ExecuteNonQuery();
});
}
}
private static bool UpdateFromV099ToV0910(Configuration oldVersion)
{
if (string.Compare(oldVersion.Value.ToString().Trim(), "0.9.9") == 0)
{
try
{
DbHelper.AddColumn("Tasks", "Estimation", "Integer");
ConfigurationHelper.SaveConfiguration(new Configuration(ConfigurationKey.DataBaseVersion, "0.9.10"));
return(true);
}
catch (OleDbException ex)
{
Logger.WriteException(ex);
return(false);
}
}
return(false);
}
private static bool UpdateFromV_1_0_1ToV_1_0_2(Configuration oldVersion)
{
if (string.Compare(oldVersion.Value.ToString().Trim(), "1.0.1") == 0)
{
try
{
DbHelper.AddColumn("ApplicationsLog", "Caption", "VarChar(255)");
ConfigurationHelper.SaveConfiguration(new Configuration(ConfigurationKey.DataBaseVersion, "1.0.2"));
return(true);
}
catch (OleDbException ex)
{
Logger.WriteException(ex);
return(false);
}
}
return(false);
}
private static bool UpdateFromV091ToV092(Configuration oldVersion)
{
if (string.Compare(oldVersion.Value.ToString().Trim(), "0.9.1") == 0)
{
try
{
DbHelper.CreateTable("Addins");
DbHelper.AddColumn("Addins", "Path", "VarChar(255)");
ConfigurationHelper.SaveConfiguration(new Configuration(ConfigurationKey.DataBaseVersion, "0.9.2"));
return(true);
}
catch (OleDbException ex)
{
Logger.WriteException(ex);
return(false);
}
}
return(false);
}
private static bool UpdateFromV_0_9_10_ToV_1_0_0(Configuration oldVersion)
{
if (string.Compare(oldVersion.Value.ToString().Trim(), "0.9.10") == 0)
{
try
{
DbHelper.AddColumn("Tasks", "Hidden", "Bit");
DbHelper.AddColumn("Tasks", "Priority", "Integer");
DbHelper.AddColumn("Tasks", "Notes", "VarChar(255)");
ConfigurationHelper.SaveConfiguration(new Configuration(ConfigurationKey.DataBaseVersion, "1.0.0"));
return(true);
}
catch (OleDbException ex)
{
Logger.WriteException(ex);
return(false);
}
}
return(false);
}
private static bool UpdateFromV092ToV093(Configuration oldVersion)
{
if (string.Compare(oldVersion.Value.ToString().Trim(), "0.9.2") == 0)
{
try
{
DbHelper.AddColumn("DefaultTasks", "IsActive", "Bit");
DbHelper.AddColumn("DefaultTasks", "Icon", "Integer");
DbHelper.AddColumn("DefaultTasks", "Hidden", "Bit");
string updateQuery = "Update DefaultTasks Set IsActive = ?, Icon = ?, Hidden = ? Where Id = ?";
string[] parameterNames = new string[] { "IsActive", "Icon", "Hidden", "Id" };
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { false, 0, false, 1 });
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { false, 2, false, 2 });
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { false, 3, false, 3 });
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { true, 4, false, 4 });
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { true, 5, false, 5 });
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { false, 6, false, 6 });
DbHelper.ExecuteNonQuery(updateQuery, parameterNames,
new object[] { true, 7, false, 7 });
ConfigurationHelper.SaveConfiguration(new Configuration(ConfigurationKey.DataBaseVersion, "0.9.3"));
return(true);
}
catch (OleDbException ex)
{
Logger.WriteException(ex);
return(false);
}
}
return(false);
}
/// <summary>
/// 路口/兴趣点 增加拼音和拼音首字母
/// </summary>
public void ExcuteRoadCrossAndPoi()
{
var tables = GetTables("poi");
tables.AddRange(this.GetTables("roadcross"));
var dbHelper = new DbHelper(this.con);
var connection = new NpgsqlConnection(con);
connection.Open();
var command = connection.CreateCommand();
var readcon = new NpgsqlConnection(con);
readcon.Open();
var readcommand = readcon.CreateCommand();
readcommand.CommandType = CommandType.Text;
foreach (var table in tables)
{
dbHelper.AddColumn("quanpin", table);
dbHelper.AddColumn("szm", table);
Console.WriteLine(table);
command.CommandType = CommandType.Text;
command.CommandTimeout = 1000 * 60;
command.CommandText = string.Format("SELECT COUNT(1) FROM {0} where quanpin is null", table);
int rowCount = int.Parse(command.ExecuteScalar().ToString());
if (rowCount != 0)
{
var max = rowCount / 2000 + 1;
Console.WriteLine(max);
for (int i = 0; i < max; i++)
{
Console.WriteLine(i);
readcommand.CommandText = string.Format("SELECT gid,name from {0} where quanpin is null limit 2000", table);
var reader = readcommand.ExecuteReader();
while (reader.Read())
{
var gid = reader.GetInt32(0);
var name = reader.GetString(1);
command.CommandText = string.Format(
"UPDATE {0} SET quanpin='{1}',szm='{2}' where gid = {3}",
table,
Hz2Py.GetPinyin(name).Replace('\'', ' '),
Hz2Py.GetFirstPinyin(name).Replace('\'', ' '),
gid);
command.ExecuteNonQuery();
}
reader.Close();
}
//readcommand.Dispose();
}
// readcon.Close();
//readcon.Dispose();
// command.Dispose();
// connection.Close();
//connection.Dispose();
}
}
