| public GetConnectionString ( bool includePass ) : string | ||
| includePass | bool | |
| 리턴 | string | |
public MySQLDatabaseHandler(string vHostOrIP, string vDatabaseName, string vUser, string vPassword)
{
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.Database = vDatabaseName;
builder.UserID = vUser;
builder.Password = vPassword;
builder.Server = vHostOrIP;
string conSTring = builder.GetConnectionString(true);
conn = new MySqlConnection(builder.GetConnectionString(true));
}
/// <summary>
/// 链接数据库测试,第一要执行的任务
/// </summary>
/// <param name="config">配置文件</param>
/// <param name="connectionString">链接字符串</param>
/// <returns>是否链接成功</returns>
public bool Connect(ConnectionConfig config, ref string connectionString)
{
MySqlConnectionStringBuilder bui = new MySqlConnectionStringBuilder();
bui.Server = config.Server;
bui.Database = config.Database;
bui.UserID = config.User;
bui.Password = config.Password;
if (config.Port != 3306) bui.Port = config.Port;
connectionString = bui.GetConnectionString(!string.IsNullOrEmpty(config.Password));
using (MySqlConnection con = new MySqlConnection(connectionString))
{
try
{
con.Open();
IsConnect = true;
Config = config;
this._conStr = connectionString;
}
catch
{
IsConnect = false;
return false;
}
}
return true;
}
public MySqlEICData(string server, string dataBase, string userName, string password)
{
MySqlConnectionStringBuilder connStrBuilder = new MySqlConnectionStringBuilder();
connStrBuilder.Server = server;
connStrBuilder.Database = dataBase;
connStrBuilder.UserID = userName;
connStrBuilder.Password = password;
this._dataAdapter = new MySqlEICDataAdapter(connStrBuilder.GetConnectionString(true));
}
/// <summary>
/// Creates a new MySqlConnection object with the exact same ConnectionString value
/// </summary>
/// <returns>A cloned MySqlConnection object</returns>
object ICloneable.Clone()
{
MySqlConnection clone = new MySqlConnection();
string connectionString = settings.GetConnectionString(true);
if (connectionString != null)
{
clone.ConnectionString = connectionString;
}
return(clone);
}
public static String getConnectionStr(String host, String port, String userName, String passWord, String dataBase)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
builder.Server = host;
builder.Port = UInt32.Parse(port);
//builder.Database = dataBaseName;
builder.UserID = userName;
builder.Password = passWord;
builder.Database = dataBase;
return(builder.GetConnectionString(true));
//return String.Format("host={0};database={1};uid={2};pwd={3};charset=UTF-8", host, dataBaseName, userName, passWord);
}
private static IDbConnection IConnectToMySQL()
{
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
sb.Database = pngIni.Ini["Database.Name"];
sb.Password = pngIni.Ini["Database.Password"];
sb.Server = pngIni.Ini["Database.Host"];
sb.UserID = pngIni.Ini["Database.User"];
MySqlConnection conn = new MySqlConnection(sb.GetConnectionString(true));
try {
conn.Open();
} catch (Exception e) {
throw new pnDbException("Failed to connect to MySQL", e);
}
return conn;
}
public static MySqlPool GetPool(MySqlConnectionStringBuilder settings)
{
string text = settings.GetConnectionString(true);
lock (pools.SyncRoot)
{
MySqlPool pool = (pools[text] as MySqlPool);
if (pool == null)
{
pool = new MySqlPool(settings);
pools.Add(text, pool);
}
else
pool.Settings = settings;
return pool;
}
}
public MySqlUserDataSource()
{
m_logger = LogManager.GetLogger("MySqlUserDataSource");
MySqlConnectionStringBuilder bldr = new MySqlConnectionStringBuilder();
bldr.Server = Settings.Store.Host;
int port = Settings.Store.Port;
bldr.Port = Convert.ToUInt32(port);
bldr.UserID = Settings.Store.User;
bldr.Database = Settings.Store.Database;
bldr.Password = Settings.Store.GetEncryptedSetting("Password");
bool useSsl = Settings.Store.UseSsl;
if (useSsl)
bldr.SslMode = MySqlSslMode.Required;
m_conn = new MySqlConnection(bldr.GetConnectionString(true));
if( m_conn != null ) m_conn.Open();
else throw new Exception("Unable to create connection to database.");
}
public static MySqlPool GetPool(MySqlConnectionStringBuilder settings)
{
string text = settings.GetConnectionString(true);
lock (pools.SyncRoot)
{
MySqlPool pool = (pools[text] as MySqlPool);
if (pool == null)
{
pool = new MySqlPool(settings);
pools.Add(text, pool);
}
else
{
pool.Settings = settings;
}
return(pool);
}
}
public static void SetConStr()
{
string strjosn = File.ReadAllText("log\\constr.log");
DBModel db = JsonConvert.DeserializeObject<DBModel>(strjosn);
MySqlConnectionStringBuilder msc = new MySqlConnectionStringBuilder();
msc.CharacterSet = "utf8";
msc.Server = db.Server;
msc.Database = db.DataBase;
msc.UserID = db.User;
msc.Password = db.PassWord;
ConStr = msc.GetConnectionString(!string.IsNullOrEmpty(db.PassWord));
if (string.IsNullOrEmpty(db.PassWord))
{
ConStr = string.Format("Server={0};Database={1}; User={2};", db.Server, db.DataBase, db.User);
}
else
{
ConStr = string.Format("Server={0};Database={1}; User={2};Password={3};", db.Server, db.DataBase, db.User, db.PassWord);
}
}
private void button1_Click(object sender, EventArgs e)
{
MySqlConnectionStringBuilder ConnectionString = new MySqlConnectionStringBuilder(); //Создаем объект строки подклбчения к БД.
//Можем сразу написать строку подключения
//или вбить параметры отдельно. Я вбиваю отдельно
ConnectionString.Server = "127.0.0.1"; //здесь пишем адрес сервера с базой данных.
//Так как наш сервер находтся на этом компьютере
//пишем такой адрес
ConnectionString.Database = "test"; //Имя базы к кторой подключаемся
ConnectionString.UserID = textBox1.Text; //Имя пользователя. Здесь нуно будетм указать свое имя
ConnectionString.Password = textBox2.Text;//Пароль ползователя
Connection = new MySqlConnection(ConnectionString.GetConnectionString(true)); //Создаем объект подключения к БД. В касестве ппраметра
//конструктору передаем строку подключение с методом GetConnectionString(true)
//True означет что мы преедаем строку подключения вместе с паролем
button2.Enabled = true;
button3.Enabled = true;
button4.Enabled = true;
button1.Enabled = false;
}
public override void Configure(Container container)
{
Plugins.Add(new SwaggerFeature());
{
var reqlog = new RequestLogsFeature();
Plugins.Add(reqlog);
}
{
var cfgfile = new IniFile("stksto.ini", null);
var dbhost = cfgfile.ReadValue("db", "host");
var dbport = int.Parse(cfgfile.ReadValue("db", "port"));
var dbuser = cfgfile.ReadValue("db", "user");
var dbpwd = cfgfile.ReadValue("db", "pwd");
var dbname = cfgfile.ReadValue("db", "db");
OrmLiteConfig.DialectProvider = MySqlDialect.Provider;
//先手工写，以后放到配置文件里去
var blder = new MySqlConnectionStringBuilder
{
Server = dbhost,
Port = (uint)dbport,
UserID = dbuser,
Password = dbpwd,
Database = dbname,
OldGuids = true,
CharacterSet = "utf8",
Pooling = true
};
string ConnStr = blder.GetConnectionString(true);
container.Register<IDbConnectionFactory>(c =>
{
return new OrmLiteConnectionFactory(ConnStr, MySqlDialect.Provider);
});
}
HostConfig conf = new HostConfig();
//conf.EnableFeatures = Feature.All.Remove(Feature.Csv | Feature.Soap | Feature.Jsv | Feature.Markdown);
conf.DebugMode = true;
SetConfig(conf);
}
private string BuildConnectionString()
{
uint port = 0;
try
{
port = Convert.ToUInt32(this.portTB.Text);
}
catch (FormatException)
{
MessageBox.Show("Invalid port number.");
return null;
}
MySqlConnectionStringBuilder bldr = new MySqlConnectionStringBuilder();
bldr.Server = this.hostTB.Text.Trim();
bldr.Port = port;
bldr.UserID = this.userTB.Text.Trim();
bldr.Database = this.dbTB.Text.Trim();
bldr.Password = this.passwdTB.Text;
return bldr.GetConnectionString(true);
}
private static string BuildConnectionString()
{
uint port = 0;
try
{
port = Convert.ToUInt32((String)Settings.Store.Port);
}
catch (FormatException e)
{
throw new Exception("Invalid port number.", e);
}
MySqlConnectionStringBuilder bldr = new MySqlConnectionStringBuilder();
bldr.Server = Settings.Store.Host;
bldr.Port = port;
bldr.UserID = Settings.Store.User;
bldr.Database = Settings.Store.Database;
bldr.Password = Settings.Store.GetEncryptedSetting("Password");
//m_logger.DebugFormat("Connecting to {0}:{1} as {2}, database: {3}",
// bldr.Server, bldr.Port, bldr.UserID, bldr.Database);
return bldr.GetConnectionString(true);
}
/// <summary>
/// 判断数据库是否连接成功
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public static bool IsOpen(DBModel db)
{
string str;
//现这个连接字符串测试
MySqlConnectionStringBuilder msc = new MySqlConnectionStringBuilder();
msc.CharacterSet = "utf8";
msc.Server = db.Server;
msc.Database = db.DataBase;
msc.UserID = db.User;
msc.Password = db.PassWord;
str = msc.GetConnectionString(!string.IsNullOrEmpty(db.PassWord));
using (MySqlConnection con = new MySqlConnection(str))
{
try
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
//连接成功
//生成连接字符串
ConStr = str;
//写入到日记中
string dbjson = JsonConvert.SerializeObject(db);
if (!Directory.Exists("log")) Directory.CreateDirectory("log");
File.WriteAllText("log\\constr.log", dbjson, Encoding.UTF8);
return true;
}
return false;
}
catch (Exception)
{
return false;
}
}
}
protected virtual void OnButtonOkClicked(object sender, System.EventArgs e)
{
if (comboboxConnections.Active == -1)
return;
Connection Selected = Connections.Find (m => m.ConnectionName == comboboxConnections.ActiveText);
string connStr, host;
uint port = 3306;
string[] uriSplit = new string[2];
string login = entryUser.Text;
labelLoginInfo.Text = "Соединяемся....";
QSMain.WaitRedraw ();
if (Selected.Type == ConnectionType.MySQL) {
Session.IsSaasConnection = false;
uriSplit = server.Split (new char[] { ':' }, 2, StringSplitOptions.RemoveEmptyEntries);
} else {
try {
ISaaSService svc = Session.GetSaaSService ();
string parameters = String.Format ("login.{0};pass.{1};account.{2};db.{3};",
entryUser.Text, entryPassword.Text, Selected.AccountLogin, Selected.BaseName);
UserAuthorizeResult result = svc.authorizeUser (parameters);
if (!result.Success) {
labelLoginInfo.Text = "Ошибка соединения с сервисом.";
ConnectionError = "Описание: " + result.Description;
logger.Warn (result.Description);
return;
}
uriSplit = result.Server.Split (new char[] { ':' }, 2, StringSplitOptions.RemoveEmptyEntries);
login = result.Login;
Session.IsSaasConnection = true;
Session.SessionId = result.SessionID;
Session.Account = Selected.AccountLogin;
Session.SaasBaseName = BaseName; // Сохраняем имя базы на сервере SAAS
Session.SQLBaseName = BaseName = result.BaseName; //Переписываем на реальное для подключения.
} catch (Exception ex) {
labelLoginInfo.Text = "Ошибка соединения с сервисом.";
ConnectionError = "Описание ошибки: " + ex.Message;
logger.Warn (ex);
return;
}
}
host = uriSplit [0];
if (uriSplit.Length > 1) {
uint.TryParse(uriSplit [1], out port);
}
var conStrBuilder = new MySqlConnectionStringBuilder();
conStrBuilder.Server = host;
conStrBuilder.Port = port;
conStrBuilder.Database = BaseName;
conStrBuilder.UserID = login;
conStrBuilder.Password = entryPassword.Text;
connStr = conStrBuilder.GetConnectionString(true);
QSMain.connectionDB = new MySqlConnection (connStr);
try {
Console.WriteLine ("Connecting to MySQL...");
QSMain.connectionDB.Open();
string sql = "SELECT deactivated FROM users WHERE login = @login";
try {
MySqlCommand cmd = new MySqlCommand (sql, QSMain.connectionDB);
cmd.Parameters.AddWithValue ("@login", entryUser.Text);
using (MySqlDataReader rdr = cmd.ExecuteReader ()) {
if (rdr.Read () && DBWorks.GetBoolean (rdr, "deactivated", false) == true) {
labelLoginInfo.Text = "Пользователь деактивирован.";
ConnectionError = "Пользователь под которым вы пытаетесь войти, деактивирован в настройках базы.";
QSMain.connectionDB.Close ();
return;
}
}
} catch (MySqlException myEx) {
if (myEx.Number == 1054)
logger.Warn (myEx, "Возможно не установлен микро-обновление, пропускаем проверку отключен ли пользователь.");
else
throw myEx;
}
labelLoginInfo.Text = ConnectionError = String.Empty;
String ini = Connections.Find (m => m.ConnectionName == comboboxConnections.ActiveText).IniName;
MachineConfig.ConfigSource.Configs [ini].Set ("UserLogin", entryUser.Text);
MachineConfig.ConfigSource.Configs ["Default"].Set ("ConnectionName", comboboxConnections.ActiveText);
MachineConfig.ConfigSource.Save ();
QSMain.ConnectionString = connStr;
QSMain.User = new UserInfo (entryUser.Text.ToLower ());
this.Respond (ResponseType.Ok);
} catch (MySqlException ex) {
if (ex.Number == 1045 || ex.Number == 0)
labelLoginInfo.Text = "Доступ запрещен.\nПроверьте логин и пароль.";
else if (ex.Number == 1042)
labelLoginInfo.Text = "Не удалось подключиться к серверу БД.";
else
labelLoginInfo.Text = "Ошибка соединения с базой данных.";
ConnectionError = "Строка соединения: " + connStr + "\nИсключение: " + ex.ToString ();
logger.Warn (ex);
QSMain.connectionDB.Close ();
}
}
/// <summary>
/// Creates new PDO DB connection.
/// </summary>
public override PDOConnection OpenConnection(ScriptContext context, string dsn_data, string username, string password, object argdriver_options)
{
////Determine file path
//string filename = dsn_data.Replace('/', Path.DirectorySeparatorChar);
//string filePath = Path.GetFullPath(Path.Combine(context.WorkingDirectory, filename));
var csb = new MySqlConnectionStringBuilder();
SetupConnectionString(csb, dsn_data);
csb.AllowUserVariables = true;
csb.AllowZeroDateTime = true;
if (username != null) csb.UserID = username;
if (password != null) csb.Password = password;
if (argdriver_options is PhpArray)
{
// TODO: process argdriver_options
}
var con = new PDOConnection(csb.GetConnectionString(true), new MySqlConnection(), "PDO mysql connection");
con.Connect();
return con;
}
private void reopenDatabase()
{
MySqlConnectionStringBuilder mycsb = new MySqlConnectionStringBuilder
{
Password = textBox3.Text,
Port = (uint) numericUpDown1.Value,
Server = textBox1.Text,
UserID = textBox2.Text
};
if (_conn != null)
if (_conn.State == ConnectionState.Open)
_conn.Close();
_conn = new MySqlConnection(mycsb.GetConnectionString(true));
_conn.Open();
}
static void push_whq_data(Boolean stage = false, Boolean dev = false, int months = 5, Boolean reset = false, Boolean new_model_only = false)
{
var d = new WHQtoGR.App_Code.whq2grDataContext();
GR gr = null;
ids = new gr_ids(stage);
string whq_system = "";
if (stage)
{
gr = new GR(ConfigurationManager.AppSettings["whq_stage_api_key"], ConfigurationManager.AppSettings["gr_stage_server"], false);
// gr.MeasApi = "http://*****:*****@"SELECT
CAST( DATE_FORMAT(`gma_directorreport`.`directorreport_endDate`, '%Y-%m-01') as DATE) AS `Date`,
`gma_locationtrans`.`location_name` AS `Area`,
`gma_organizationdata`.`organization_countryCode` AS `Country`,
`gma_measurementlbl`.`measurementlbl_name` AS `LMI`,
`gma_strategylbl`.`strategylbl_name` AS `MCC`,
SUM( `gma_directordata`.`directordata_value` ) AS `Value`
FROM
`gma_directordata`
JOIN `gma_directorreport` ON `gma_directordata`.`directorreport_id` = `gma_directorreport`.`directorreport_id`
JOIN `gma_measurement` ON `gma_directordata`.`measurement_id` = `gma_measurement`.`measurement_id`
JOIN `gma_newmeasurementdetails` ON `gma_measurement`.`measurement_details_id` = `gma_newmeasurementdetails`.`measurement_details_id`
JOIN `gma_measurementlbl` ON `gma_measurement`.`measurementlbl_id` = `gma_measurementlbl`.`measurementlbl_id`
JOIN `gma_organizationdata` ON `gma_measurement`.`organization_id` = `gma_organizationdata`.`organization_id`
JOIN `gma_strategydata` ON `gma_newmeasurementdetails`.`strategy_id` = `gma_strategydata`.`strategy_id`
JOIN `gma_strategylbl` ON `gma_strategydata`.`strategylbl_id` = `gma_strategylbl`.`strategylbl_id`
JOIN `gma_locationtrans` ON `gma_organizationdata`.`location_id` = `gma_locationtrans`.`location_id`
WHERE
`gma_organizationdata`.`version_id` = 2
AND `gma_strategydata`.`version_id` = 2
AND `gma_locationtrans`.`language_id` = 1
AND `gma_organizationdata`.`organization_leftIndex` + 1 = `gma_organizationdata`.`organization_rightIndex`
AND MONTH(`gma_directorreport`.`directorreport_endDate`)= MONTH(CURDATE() - INTERVAL " + i + @" MONTH )
AND YEAR(`gma_directorreport`.`directorreport_endDate`)= YEAR(CURDATE() - INTERVAL " + i + @" MONTH )
and Not `gma_locationtrans`.`location_name` = 'NONE' and Not `gma_locationtrans`.`location_name` = 'TEST'
And `gma_directordata`.`directordata_value` >0
GROUP BY
`Date`,
`Area`,
`Country`,
`LMI`,
`MCC`";
var dt = new DataTable();
var da = new MySqlDataAdapter(cmd);
da.Fill(dt);
//get all measurements
// Console.WriteLine(dt.Rows.Count);
var rows = dt.AsEnumerable().Where(row => (d.wierd_countries.Where(c => c.whq_code == row["Country"].ToString() && c.min_code == null).Count() == 0) && ids.m.ContainsKey(row["LMI"].ToString()) && (d.mid_lookups.Where(c => (c.country == row["Country"].ToString()) && c.etl && (c.stage == stage)).Count() == 0 || (row["LMI"].ToString().Contains("actualreportingnodes") || row["LMI"].ToString().Contains("actualreportingnodes")))).ToArray();
//Console.WriteLine("before: " + rows.Where(row => row["Country"].ToString() == "EGY").Count().ToString());
//filter out ETL Active rows and only
//now some tests
int delta = rows.Count() / 10;
int count = 0;
var flat_list = new Dictionary<string, double>();
var flat_list_mt = new Dictionary<string, string>(); //The flatlist contains a name/value list of all measurements (from whq), where the name contains ministry_id : measurement_type_id : dimension. This allows me to lookup a value and see if it has changed.
if (rows.Count() > 0)
{
var everything = gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_win_%25" + m_filter).ToList();
everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_build_%25" + m_filter).ToList());
everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_send_%25" + m_filter).ToList());
everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_movements_%25" + m_filter).ToList());
everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_nbr_%25" + m_filter).ToList());
everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_actual_reporting_nodes" + m_filter).ToList());
everything.AddRange(gr.GetMeasurements("", ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), ((DateTime)rows.First()["Date"]).ToString("yyyy-MM"), "", "", false, "&filters[perm_link]=lmi_local_possible_reporting_nodes" + m_filter).ToList());
foreach (MeasurementType row in everything)
{
foreach (Measurement m in row.measurements)
{
flat_list.Add(m.RelatedEntityId + ":" + row.ID + ":" + m.Dimension, m.Value);
flat_list_mt.Add(m.RelatedEntityId + ":" + row.ID + ":" + m.Dimension, m.ID ) ;
}
}
}
// Console.WriteLine("rows: " + rows.Count().ToString());
// Console.WriteLine("flatlist count: " + flat_list.Count);
int c_changed = 0;
int c_not_changed = 0;
Console.Write("processing data:");
foreach (DataRow row in rows)
{
count++;
if (delta > 0)
{
if (count % delta == 1)
Console.Write("\rprocessing data for " + ((DateTime)row["Date"]).ToString("yyyy-MM") + ": " + ((int)(100 * count / rows.Count())).ToString() + "% complete.");
}
string min_id = (from c in d.mid_lookups where c.country == row["Country"].ToString() && c.stage == stage select c.ministry_id).FirstOrDefault();
if (string.IsNullOrEmpty(min_id))
{
var nms_wierd = (from c in d.wierd_countries where c.whq_code == row["Country"].ToString() select c.min_code);
Entity nms_ministry = null;
if (nms_wierd.Count() > 0 )
{
nms_ministry = gr.GetEntities("ministry", "&filters[owned_by]=all&filters[min_code]=" + nms_wierd.First() + "&fields=min_code", 0, 0, ref totalPage).FirstOrDefault();
}
if (nms_ministry == null)
{
nms_ministry = gr.GetEntities("ministry", "&filters[owned_by]=all&filters[iso_country:relationship][iso3_code]=" + row["Country"].ToString() + "&filters[area:relationship][area_code]=" + ids.areas[row["Area"].ToString()] + "&fields=min_code", 0, 0, ref totalPage).FirstOrDefault();
}
min_id = nms_ministry.ID;
App_Code.mid_lookup insert = new App_Code.mid_lookup();
insert.ministry_id = min_id;
insert.stage = stage;
insert.country = row["Country"].ToString();
d.mid_lookups.InsertOnSubmit(insert);
d.SubmitChanges();
}
Boolean changed=true;
string perm_link = row["LMI"].ToString().ToLower().Replace("fa_", "lmi_local_").Replace("fr_", "lmi_local_").Replace("movements", "lmi_local_movements").Replace("actualreportingnodes", "lmi_local_actual_reporting_nodes").Replace("possiblereportingnodes", "lmi_local_possible_reporting_nodes").Replace("nbr_staff_reporting", "lmi_local_nbr_staff_reporting").Replace("nbr_nonstaff_reporting", "lmi_local_nbr_nonstaff_reporting");
if (perm_link.StartsWith("lmi_local"))
{
MeasurementType this_mt = ids.nms.Where(c => c.PermLink == perm_link).FirstOrDefault();
if (this_mt == null)
{
//get the measurement
// var cache = new WHQtoGR.App_Code.meas_cacheDataContext();
this_mt = gr.GetMeasurements("", "3000-01", "3000-01", "", "", false, "&filters[perm_link]=" + perm_link).FirstOrDefault();
if (this_mt != null)
{
// Console.WriteLine(perm_link);
ids.nms.Add(this_mt);
}
}
if (this_mt != null)
{
string key = min_id + ":" + this_mt.ID + ":" + ids.mcc_code[row["MCC"].ToString()] + "_gma" ;
if (flat_list.ContainsKey(key ))
{
changed = (flat_list[key] != System.Convert.ToDouble(row["Value"]));
flat_list.Remove(key);
flat_list_mt.Remove(key); //the remaining items need to be deleted, as they exist in GR but not in WHQ
}
if (changed)
{
this_mt.addMeasurement(min_id, ((DateTime)row["Date"]).ToString("yyyy-MM"), System.Convert.ToDouble(row["Value"]), ids.mcc_code[row["MCC"].ToString()] + "_gma");
c_changed++;
}
else c_not_changed++;
//Console.WriteLine(row["Country"].ToString() + " " + row["MCC"].ToString().ToLower() + " " + perm_link + " : " + System.Convert.ToDouble(row["Value"]));
}
else
{
Console.WriteLine("could not create measurement_type");
}
}
else
Console.WriteLine("could not find " + perm_link);
}
//Console.WriteLine("changed: " + c_changed);
//Console.WriteLine("not_changed: " + c_not_changed);
//Console.WriteLine("updloading " + ids.nms.Sum(c => c.measurements.Count()) + " into New Msasurement Model");
//TODO: DELETE ALL MEAUREMENTS THAT ARE LEFT IN THE FLATLIST
Console.WriteLine("extra (to be delete): " + flat_list_mt.Count());
foreach(var m in flat_list_mt)
{
gr.DeleteMeasuerment(m.Value);
//need to get measurement_id into flatlist key
}
da.Dispose();
dt.Dispose();
}
//dt.Dump();
}
Console.WriteLine("Finished generating measurmenets");
Console.WriteLine("updloading " + ids.nms.Sum(c => c.measurements.Count()) + " into New Msasurement Model");
var tasks = new List<Task>();
foreach (MeasurementType mt in ids.nms)
{
tasks.Add(System.Threading.Tasks.Task.Factory.StartNew(() =>
{
gr.AddUpdateMeasurement(mt, true, 100);
}));
}
System.Threading.Tasks.Task.WaitAll(tasks.ToArray<Task>());
Console.WriteLine("Finished ");
// Console.ReadKey();
}
private UserEntry GetUserEntry(string user)
{
m_logger.Debug("GetUserEntry");
MySqlConnectionStringBuilder bldr = new MySqlConnectionStringBuilder();
bldr.Server = Settings.Store.Host;
int port = Settings.Store.Port;
bldr.Port = Convert.ToUInt32(port);
bldr.UserID = Settings.Store.User;
bldr.Database = Settings.Store.Database;
bldr.Password = Settings.Store.GetEncryptedSetting("Password");
bool useSsl = Settings.Store.UseSsl;
if (useSsl)
bldr.SslMode = MySqlSslMode.Required;
string tableName = Settings.Store.Table;
try
{
using (MySqlConnection conn = new MySqlConnection(bldr.GetConnectionString(true)))
{
conn.Open();
string query = string.Format("SELECT user, hash_method, password " +
"FROM {0} WHERE user=@user", tableName);
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@user", user);
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
PasswordHashAlgorithm hashAlg;
string uname = rdr[0].ToString();
string hash = rdr[2].ToString();
switch (rdr[1].ToString())
{
case "NONE":
hashAlg = PasswordHashAlgorithm.NONE;
break;
case "MD5":
hashAlg = PasswordHashAlgorithm.MD5;
break;
case "SMD5":
hashAlg = PasswordHashAlgorithm.SMD5;
break;
case "SHA1":
hashAlg = PasswordHashAlgorithm.SHA1;
break;
case "SSHA1":
hashAlg = PasswordHashAlgorithm.SSHA1;
break;
case "SHA256":
hashAlg = PasswordHashAlgorithm.SHA256;
break;
case "SSHA256":
hashAlg = PasswordHashAlgorithm.SSHA256;
break;
case "SHA512":
hashAlg = PasswordHashAlgorithm.SHA512;
break;
case "SSHA512":
hashAlg = PasswordHashAlgorithm.SSHA512;
break;
case "SHA384":
hashAlg = PasswordHashAlgorithm.SHA384;
break;
case "SSHA384":
hashAlg = PasswordHashAlgorithm.SSHA384;
break;
default:
m_logger.ErrorFormat("Unrecognized hash algorithm: {0}", rdr[1].ToString());
return null;
}
rdr.Close();
return new UserEntry(uname, hashAlg, hash);
}
else
{
return null;
}
}
}
catch (MySqlException ex)
{
if (ex.Number == 1042)
m_logger.ErrorFormat("Unable to connect to host: {0}", Settings.Store.Host);
else
m_logger.Error(ex.ToString());
// Return null causes failed auth.
return null;
}
}
public static void ClearPool(MySqlConnectionStringBuilder settings)
{
string text = settings.GetConnectionString(true);
ClearPoolByText(text);
}
public static void RunCreation(CreationScript script, string server, string dbname, string login, string password)
{
if (script == null)
script = Scripts.First ();
string connStr, host;
uint port = 3306;
string[] uriSplit = server.Split (new char[] { ':' }, 2, StringSplitOptions.RemoveEmptyEntries);
host = uriSplit [0];
if (uriSplit.Length > 1) {
uint.TryParse(uriSplit [1], out port);
}
var conStrBuilder = new MySqlConnectionStringBuilder();
conStrBuilder.Server = host;
conStrBuilder.Port = port;
conStrBuilder.UserID = login;
conStrBuilder.Password = password;
connStr = conStrBuilder.GetConnectionString(true);
var connectionDB = new MySqlConnection (connStr);
try {
logger.Info ("Connecting to MySQL...");
connectionDB.Open();
} catch (MySqlException ex) {
logger.Info ("Строка соединения: {0}", connStr);
logger.Error (ex, "Ошибка подключения к серверу.");
if (ex.Number == 1045 || ex.Number == 0)
MessageDialogWorks.RunErrorDialog ("Доступ запрещен.\nПроверьте логин и пароль.");
else if (ex.Number == 1042)
MessageDialogWorks.RunErrorDialog ("Не удалось подключиться к серверу БД.");
else
MessageDialogWorks.RunErrorDialog ("Ошибка соединения с базой данных.");
connectionDB.Close ();
return;
}
logger.Info ("Проверяем существует ли уже база.");
var sql = "SHOW DATABASES;";
var cmd = new MySqlCommand (sql, connectionDB);
bool needDropBase = false;
using (var rdr = cmd.ExecuteReader ())
{
while (rdr.Read ()) {
if (rdr [0].ToString () == dbname) {
if (MessageDialogWorks.RunQuestionDialog ("База с именем `{0}` уже существует на сервере. Удалить существующую базу перед соданием новой?", dbname)) {
needDropBase = true;
break;
} else
return;
}
}
}
logger.Info ("Создаем новую базу.");
progressDlg = new CreatorProgress ();
progressDlg.OperationText = "Получаем скрипт создания базы";
progressDlg.Show ();
string sqlScript;
using(Stream stream = System.Reflection.Assembly.GetEntryAssembly().GetManifestResourceStream(script.Resource))
{
if(stream == null)
throw new InvalidOperationException( String.Format("Ресурс {0} со скриптом не найден.", script.Resource));
StreamReader reader = new StreamReader(stream);
sqlScript = reader.ReadToEnd();
}
int predictedCount = Regex.Matches(sqlScript, ";").Count;
logger.Debug ("Предполагаем наличие {0} команд в скрипте.", predictedCount);
progressDlg.OperationText = String.Format ("Создаем базу <{0}>", dbname);
progressDlg.OperationPartCount = predictedCount + (needDropBase ? 2 : 1);
progressDlg.OperationCurPart = 0;
if(needDropBase)
{
logger.Info ("Удаляем существующую базу {0}.", dbname);
progressDlg.OperationText = String.Format ("Удаляем существующую базу {0}", dbname);
cmd.CommandText = String.Format ("DROP DATABASE `{0}`", dbname);
cmd.ExecuteNonQuery ();
progressDlg.OperationCurPart++;
}
cmd.CommandText = String.Format ("CREATE SCHEMA `{0}` DEFAULT CHARACTER SET utf8 ;", dbname);
cmd.ExecuteNonQuery ();
cmd.CommandText = String.Format ("USE `{0}` ;", dbname);
cmd.ExecuteNonQuery ();
progressDlg.OperationText = String.Format ("Создаем таблицы в <{0}>", dbname);
progressDlg.OperationCurPart++;
var myscript = new MySqlScript(connectionDB, sqlScript);
myscript.StatementExecuted += Myscript_StatementExecuted;;
var commands = myscript.Execute ();
logger.Debug ("Выполнено {0} SQL-команд.", commands);
progressDlg.Destroy ();
progressDlg = null;
MessageDialogWorks.RunInfoDialog ("Создание базы успешно завершено.\nЗайдите в программу под администратором для добавления пользователей.");
}
/// <summary>
/// 获取链接字符串
/// </summary>
/// <returns></returns>
static void CreateConnectionString()
{
//获取配置文件
string configFile = AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\') + "\\App_Data\\mysql.json";
if (!File.Exists(configFile)) throw new MySqlConfigFileException(false);
string configTxt = File.ReadAllText(configFile, Encoding.UTF8);
try
{
ConnectionConfig config = JsonConvert.DeserializeObject<ConnectionConfig>(configTxt);
MySqlConnectionStringBuilder msb = new MySqlConnectionStringBuilder()
{
Server = config.Server,
Database = config.Database,
UserID = config.User,
Password = config.Password,
Port = config.Port,
};
config.Prefix = config.Prefix == null ? "" : config.Prefix.Trim();
string str = msb.GetConnectionString(config.Password != null && config.Password != "");
}
catch
{
throw new MySqlConfigFileException(true);
}
}