Skip to content

Instantly share code, notes, and snippets.

@alien3d

alien3d/Windows1.xaml.cs

Created May 11, 2017 08:44
Show Gist options
  • Save alien3d/d52aa447cacf6bf4ad43289d495e8047 to your computer and use it in GitHub Desktop.
Save alien3d/d52aa447cacf6bf4ad43289d495e8047 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Windows1.xaml.cs
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Windows;
using Microsoft.Translator.Samples;
using System.Net;
using System.IO;
namespace GenerateDefaultAndListClass
{
/// <summary>
/// Interaction logic for Window1.xaml
/// </summary>
public partial class Window1 : Window
{
MySqlConnection connection;
MySqlTransaction transaction;
public string headerValue { get; set; }
// Before running the application, input the secret key for your subscription to Translator Text Translation API.
private const string TEXT_TRANSLATION_API_SUBSCRIPTION_KEY = "da key";
// Object to get an authentication token
private AzureAuthToken tokenProvider;
// Cache language friendly names
private string[] friendlyName = { " " };
// Cache list of languages for speech synthesis
private List<string> speakLanguages;
// Dictionary to map language code from friendly name
private Dictionary<string, string> languageCodesAndTitles = new Dictionary<string, string>();
public Window1()
{
InitializeComponent();
var connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
try
{
connection = new MySqlConnection(connectionString);
connection.Open();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
tokenProvider = new AzureAuthToken(TEXT_TRANSLATION_API_SUBSCRIPTION_KEY);
/**
string clientID = "SangLinTackle";
string clientSecret = "OuUiF9kcINsOx5EOMdYnVnqgQJtXTYQKVnE48euhvYg=";
string strTranslatorAccessURI =
"https://datamarket.accesscontrol.windows.net/v2/OAuth2-13";
string strRequestDetails =
string.Format("grant_type=client_credentials&client_id={0}&client_secret={1} &scope=http://api.microsofttranslator.com", HttpUtility.UrlEncode(clientID),
HttpUtility.UrlEncode(clientSecret));
System.Net.WebRequest webRequest = System.Net.WebRequest.Create(strTranslatorAccessURI);
webRequest.ContentType = "application/x-www-form-urlencoded";
webRequest.Method = "POST";
byte[] bytes = System.Text.Encoding.ASCII.GetBytes(strRequestDetails);
webRequest.ContentLength = bytes.Length;
using (System.IO.Stream outputStream = webRequest.GetRequestStream())
{
outputStream.Write(bytes, 0, bytes.Length);
}
System.Net.WebResponse webResponse = webRequest.GetResponse();
System.Runtime.Serialization.Json.DataContractJsonSerializer serializer = new
System.Runtime.Serialization.Json.DataContractJsonSerializer(typeof(AdmAccessToken));
AdmAccessToken token =
(AdmAccessToken)serializer.ReadObject(webResponse.GetResponseStream());
headerValue = "Bearer " + token.access_token;
**/
comboBox1.Items.Add(new ComboboxItem() { Text = "Please Choose", Value = "" });
comboBox1.Items.Add(new ComboboxItem() { Text = "Access", Value = 1 });
comboBox1.Items.Add(new ComboboxItem() { Text = "Menu", Value = 2 });
comboBox1.Items.Add(new ComboboxItem() { Text = "Table", Value = 3 });
comboBox1.Items.Add(new ComboboxItem() { Text = "Label", Value = 4 });
comboBox1.Items.Add(new ComboboxItem() { Text = "Column", Value = 2 });
comboBox1.Text = "Please select";
}
private void button_Click(object sender, RoutedEventArgs e)
{
var w = new Window();
w.Show();
Close();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
switch (comboBox1.Text)
{
case "Access":
BatchApplicationAccess();
BatchModuleAccess();
BatchFolderAccess();
BatchLeafRoleAccess();
BatchLeafAccess();
BatchUserAccess();
break;
case "Menu":
BatchApplicationTranslate();
BatchModuleTranslate();
BatchFolderTranslate();
BatchLeafTranslate();
break;
case "Table":
BatchTableTranslate();
break;
case "Label":
BatchLabelTranslate();
break;
case "Column":
GenerateTableColumnNameLabel();
break;
}
MessageBox.Show("complete");
}
private void BatchApplicationAccess()
{
// forget limitation of c# . need to parse to array code again tomorow
// truncate table access
string sqlTruncate = @"TRUNCATE application_access";
var command = new MySqlCommand(sqlTruncate, connection, transaction);
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
command.Dispose();
// loop role and bind to data contract
string sqlRole = @"
SELECT roleId,
isAdmin
FROM role
WHERE isActive = 1 ";
var commandRole = new MySqlCommand(sqlRole, connection, transaction);
List<role> roles = new List<role>();
try
{
var data = commandRole.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
var r = new role();
r.roleId = Convert.ToInt32(data["roleId"]);
r.isAdmin = Convert.ToInt32(data["isAdmin"]);
roles.Add(r);
}
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandRole.Dispose();
}
// loop application and bind to data contract
string sqlApplication = @"SELECT applicationId FROM application WHERE isActive = 1";
var commandApplication = new MySqlCommand(sqlApplication, connection, transaction);
List<application> applications = new List<application>();
try
{
var dataApplication = commandApplication.ExecuteReader();
if (dataApplication.HasRows)
{
while (dataApplication.Read())
{
var application = new application();
application.applicationId = Convert.ToInt32(dataApplication["applicationId"]);
applications.Add(application);
}
}
dataApplication.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandApplication.Dispose();
}
// modify for speed .. eerr array in array .. next time .
foreach (dynamic role in roles)
{
foreach (dynamic application in applications)
{
const string sqlInsertAccess = @"
INSERT INTO `application_access` (
`roleId`, `applicationId`, `applicationAccessValue`,
`executeBy`, `executeTime`
) VALUES (
@roleId, @applicationId, @applicationAccessValue,
@executeBy, @executeTime
)";
var commandInsert = new MySqlCommand(sqlInsertAccess, connection, transaction);
commandInsert.Parameters.Add("@roleId", MySqlDbType.Int32).Value = role.roleId;
commandInsert.Parameters.Add("@applicationId", MySqlDbType.Int32).Value = application.applicationId;
int applicationAccessValue = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@applicationAccessValue", MySqlDbType.Int32).Value = applicationAccessValue;
commandInsert.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandInsert.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandInsert.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandInsert.Dispose();
}
}
}
}
private void BatchModuleAccess()
{
// truncate table access
string sqlTruncate = @"TRUNCATE module_access";
var command = new MySqlCommand(sqlTruncate, connection, transaction);
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
string sqlRole = @"
SELECT roleId,
isAdmin
FROM role
WHERE isActive = 1 ";
var commandRole = new MySqlCommand(sqlRole, connection, transaction);
List<role> roles = new List<role>();
try
{
var data = commandRole.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
var r = new role();
r.roleId = Convert.ToInt32(data["roleId"]);
r.isAdmin = Convert.ToInt32(data["isAdmin"]);
roles.Add(r);
}
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandRole.Dispose();
}
// tukar lagi lol.
string sqlModule = @"SELECT moduleId FROM module WHERE isActive = 1";
var commandModule = new MySqlCommand(sqlModule, connection, transaction);
List<module> modules = new List<module>();
try
{
var dataModule = commandModule.ExecuteReader();
if (dataModule.HasRows)
{
while (dataModule.Read())
{
var module = new module();
module.moduleId = Convert.ToInt32(dataModule["moduleId"]);
modules.Add(module);
}
}
dataModule.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandModule.Dispose();
}
// modify for speed .. eerr array in array .. next time .
foreach (dynamic role in roles)
{
foreach (dynamic module in modules)
{
const string sqlInsertAccess = @"
INSERT INTO `module_access` (
`roleId`, `moduleId`, `moduleAccessValue`,
`executeBy`, `executeTime`
) VALUES (
@roleId, @moduleId, @moduleAccessValue,
@executeBy, @executeTime
)";
var commandInsert = new MySqlCommand(sqlInsertAccess, connection, transaction);
commandInsert.Parameters.Add("@roleId", MySqlDbType.Int32).Value = role.roleId;
commandInsert.Parameters.Add("@moduleId", MySqlDbType.Int32).Value = module.moduleId;
int moduleAccessValue = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@moduleAccessValue", MySqlDbType.Int32).Value = moduleAccessValue;
commandInsert.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandInsert.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
int i = commandInsert.ExecuteNonQuery();
if (i == 1)
{
textBox1.AppendText("success insert module" + Environment.NewLine);
}
else
{
textBox1.AppendText("nothing updated module" + Environment.NewLine);
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandInsert.Dispose();
}
}
}
}
private void BatchFolderAccess()
{
// truncate table access
string sqlTruncate = @"TRUNCATE folder_access";
var command = new MySqlCommand(sqlTruncate, connection, transaction);
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
string sqlRole = @"
SELECT roleId,
isAdmin
FROM role
WHERE isActive = 1 ";
var commandRole = new MySqlCommand(sqlRole, connection, transaction);
List<role> roles = new List<role>();
try
{
var data = commandRole.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
var r = new role();
r.roleId = Convert.ToInt32(data["roleId"]);
r.isAdmin = Convert.ToInt32(data["isAdmin"]);
roles.Add(r);
}
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandRole.Dispose();
}
// tukar lagi lol.
string sqlFolder = @"SELECT * FROM folder WHERE isActive = 1";
var commandFolder = new MySqlCommand(sqlFolder, connection, transaction);
List<folder> folders = new List<folder>();
try
{
var dataFolder = commandFolder.ExecuteReader();
if (dataFolder.HasRows)
{
while (dataFolder.Read())
{
var folder = new folder();
folder.folderId = Convert.ToInt32(dataFolder["folderId"]);
folders.Add(folder);
}
}
dataFolder.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandFolder.Dispose();
}
// modify for speed .. eerr array in array .. next time .
foreach (dynamic role in roles)
{
foreach (dynamic folder in folders)
{
const string sqlInsertAccess = @"
INSERT INTO `folder_access` (
`roleId`, `folderId`, `folderAccessValue`,
`executeBy`, `executeTime`
) VALUES (
@roleId, @folderId, @folderAccessValue,
@executeBy, @executeTime
)";
var commandInsert = new MySqlCommand(sqlInsertAccess, connection, transaction);
commandInsert.Parameters.Add("@roleId", MySqlDbType.Int32).Value = role.roleId;
commandInsert.Parameters.Add("@folderId", MySqlDbType.Int32).Value = folder.folderId;
int folderAccessValue = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@folderAccessValue", MySqlDbType.Int32).Value = folderAccessValue;
commandInsert.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandInsert.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandInsert.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandInsert.Dispose();
}
}
}
}
private void BatchLeafAccess()
{
// truncate table access
string sqlTruncate = @"TRUNCATE leaf_access";
var command = new MySqlCommand(sqlTruncate, connection, transaction);
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
string sqlUser = @"
SELECT userId,
isAdmin,
user.isActive
FROM user
JOIN role
USING (roleId)
WHERE user.isActive = 1 ";
var commandUser = new MySqlCommand(sqlUser, connection, transaction);
List<user> users = new List<user>();
try
{
var data = commandUser.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
var r = new user();
r.userId = Convert.ToInt32(data["userId"]);
r.isAdmin = Convert.ToInt32(data["isAdmin"]);
users.Add(r);
}
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandUser.Dispose();
}
// tukar lagi lol.
string sqlLeaf = @"SELECT * FROM leaf WHERE isActive = 1";
var commandLeaf = new MySqlCommand(sqlLeaf, connection, transaction);
List<leaf> leafs = new List<leaf>();
try
{
var dataLeaf = commandLeaf.ExecuteReader();
if (dataLeaf.HasRows)
{
while (dataLeaf.Read())
{
var leaf = new leaf();
leaf.leafId = Convert.ToInt32(dataLeaf["leafId"]);
leafs.Add(leaf);
}
}
dataLeaf.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandLeaf.Dispose();
}
// modify for speed .. eerr array in array .. next time .
foreach (dynamic user in users)
{
foreach (dynamic leaf in leafs)
{
const string sqlInsertAccess = @"
INSERT INTO `fish`.`leaf_access` (
`userId`, `leafAccessDraftValue`, `leafAccessCreateValue`,
`leafAccessReadValue`, `leafAccessUpdateValue`, `leafAccessDeleteValue`,
`leafAccessReviewValue`, `leafAccessApprovedValue`, `leafAccessPostValue`,
`leafAccessPrintValue`, `executeBy`, `executeTime`,
`leafId`
) VALUES (
@userId, @leafAccessDraftValue, @leafAccessCreateValue,
@leafAccessReadValue, @leafAccessUpdateValue, @leafAccessDeleteValue,
@leafAccessReviewValue, @leafAccessApprovedValue, @leafAccessPostValue,
@leafAccessPrintValue, @executeBy, @executeTime,
@leafId
);";
var commandInsert = new MySqlCommand(sqlInsertAccess, connection, transaction);
commandInsert.Parameters.Add("@userId", MySqlDbType.Int32).Value = user.userId;
commandInsert.Parameters.Add("@leafId", MySqlDbType.Int32).Value = leaf.leafId;
commandInsert.Parameters.Add("@leafAccessDraftValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessCreateValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessReadValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessUpdateValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessDeleteValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessReviewValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessApprovedValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessPostValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafAccessPrintValue", MySqlDbType.Int32).Value = (user.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandInsert.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandInsert.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandInsert.Dispose();
}
}
}
}
private void BatchLeafRoleAccess()
{
// truncate table access
string sqlTruncate = @"TRUNCATE leaf_role_access";
var command = new MySqlCommand(sqlTruncate, connection, transaction);
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
string sqlRole = @"
SELECT roleId,
isAdmin
FROM role
WHERE isActive = 1 ";
var commandRole = new MySqlCommand(sqlRole, connection, transaction);
List<role> roles = new List<role>();
try
{
var data = commandRole.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
var r = new role();
r.roleId = Convert.ToInt32(data["roleId"]);
r.isAdmin = Convert.ToInt32(data["isAdmin"]);
roles.Add(r);
}
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandRole.Dispose();
}
// tukar lagi lol.
string sqlLeaf = @"SELECT * FROM leaf WHERE isActive = 1";
var commandLeaf = new MySqlCommand(sqlLeaf, connection, transaction);
List<leaf> leafs = new List<leaf>();
try
{
var dataLeaf = commandLeaf.ExecuteReader();
if (dataLeaf.HasRows)
{
while (dataLeaf.Read())
{
var leaf = new leaf();
leaf.leafId = Convert.ToInt32(dataLeaf["leafId"]);
leafs.Add(leaf);
}
}
dataLeaf.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandLeaf.Dispose();
}
// modify for speed .. eerr array in array .. next time .
foreach (dynamic role in roles)
{
foreach (dynamic leaf in leafs)
{
const string sqlInsertAccess = @"
INSERT INTO `fish`.`leaf_role_access` (
`roleId`, `leafRoleAccessDraftValue`, `leafRoleAccessCreateValue`,
`leafRoleAccessReadValue`, `leafRoleAccessUpdateValue`, `leafRoleAccessDeleteValue`,
`leafRoleAccessReviewValue`, `leafRoleAccessApprovedValue`, `leafRoleAccessPostValue`,
`leafRoleAccessPrintValue`, `executeBy`, `executeTime`,
`leafId`
) VALUES (
@roleId, @leafRoleAccessDraftValue, @leafRoleAccessCreateValue,
@leafRoleAccessReadValue, @leafRoleAccessUpdateValue, @leafRoleAccessDeleteValue,
@leafRoleAccessReviewValue, @leafRoleAccessApprovedValue, @leafRoleAccessPostValue,
@leafRoleAccessPrintValue, @executeBy, @executeTime,
@leafId
);";
var commandInsert = new MySqlCommand(sqlInsertAccess, connection, transaction);
commandInsert.Parameters.Add("@roleId", MySqlDbType.Int32).Value = role.roleId;
commandInsert.Parameters.Add("@leafId", MySqlDbType.Int32).Value = leaf.leafId;
commandInsert.Parameters.Add("@leafRoleAccessDraftValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessCreateValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessReadValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessUpdateValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessDeleteValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessReviewValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessApprovedValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessPostValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@leafRoleAccessPrintValue", MySqlDbType.Int32).Value = (role.isAdmin == 1) ? 1 : 0;
commandInsert.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandInsert.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandInsert.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandInsert.Dispose();
}
}
}
}
private void BatchUserAccess()
{
// this query update for business partner
string SqlModuleBusinessPartnerAccess = @"
UPDATE module_access
SET moduleAccessValue = 1
WHERE roleId = 4
AND moduleId IN (
SELECT moduleId
FROM module
WHERE applicationId = 12
);";
var commandModuleBusinessPartnerAccess = new MySqlCommand(SqlModuleBusinessPartnerAccess, connection);
try
{
commandModuleBusinessPartnerAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandModuleBusinessPartnerAccess.Dispose();
}
string SqlFolderBusinessPartnerAccess = @"
UPDATE folder_access
SET folderAccessValue = 1
WHERE roleId = 4
AND folderId IN (
SELECT folderId
FROM folder
WHERE applicationId = 12
);";
var commandFolderBusinessPartnerAccess = new MySqlCommand(SqlFolderBusinessPartnerAccess, connection);
try
{
commandFolderBusinessPartnerAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandFolderBusinessPartnerAccess.Dispose();
}
//---------------------------------------------------------done
// this query update for candidate
string SqlModuleCandidateAccess = @"
UPDATE module_access
SET moduleAccessValue = 1
WHERE roleId = 2
AND moduleId IN (
SELECT moduleId
FROM module
WHERE applicationId = 11
);";
var commandModuleCandidateAccess = new MySqlCommand(SqlModuleCandidateAccess, connection);
try
{
commandModuleCandidateAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandModuleCandidateAccess.Dispose();
}
string SqlFolderCandidateAccess = @"
UPDATE folder_access
SET folderAccessValue = 1
WHERE roleId = 2
AND folderId IN (
SELECT folderId
FROM folder
WHERE applicationId = 11
);";
var commandFolderCandidateAccess = new MySqlCommand(SqlFolderCandidateAccess, connection);
try
{
commandFolderCandidateAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandFolderCandidateAccess.Dispose();
}
//---------------------------------------------------done
// this query update for normal customer access
string SqlModuleCustomerAccess = @"
UPDATE module_access
SET moduleAccessValue = 1
WHERE roleId = 3
AND moduleId IN (
SELECT moduleId
FROM module
WHERE applicationId = 13
);";
var commandModuleCustomerAccess = new MySqlCommand(SqlModuleCustomerAccess, connection);
try
{
commandModuleCustomerAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandModuleCustomerAccess.Dispose();
}
string SqlFolderCustomerAccess = @"
UPDATE folder_access
SET folderAccessValue = 1
WHERE roleId = 3
AND folderId IN (
SELECT folderId
FROM folder
WHERE applicationId = 13
);";
var commandFolderCustomerAccess = new MySqlCommand(SqlFolderCustomerAccess, connection);
try
{
commandFolderCustomerAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandFolderCustomerAccess.Dispose();
}
//---------------------------------------------------------------
// this query update administrator
string SqlModuleAdministratorAccess = @"
UPDATE module_access
SET moduleAccessValue = 1
WHERE roleId = 1 ";
var commandModuleAdministratorAccess = new MySqlCommand(SqlModuleAdministratorAccess, connection);
try
{
commandModuleAdministratorAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandModuleAdministratorAccess.Dispose();
}
string SqlFolderAdministratorAccess = @"
UPDATE folder_access
SET folderAccessValue = 1
WHERE roleId = 1;";
var commandFolderAdministratorAccess = new MySqlCommand(SqlFolderAdministratorAccess, connection);
try
{
commandFolderAdministratorAccess.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandFolderAdministratorAccess.Dispose();
}
}
private void BatchApplicationTranslate()
{
//MySqlTransaction transaction = connection.BeginTransaction();
int existedRecord = 0;
string sqlApplication = @"
SELECT applicationId,
applicationEnglish
FROM application
WHERE isActive = 1";
var commandApplication = new MySqlCommand(sqlApplication, connection, transaction);
List<application> applications = new List<application>();
try
{
var dataApplication = commandApplication.ExecuteReader();
if (dataApplication.HasRows)
{
while (dataApplication.Read())
{
var application = new application();
application.applicationId = Convert.ToInt32(dataApplication["applicationId"]);
application.applicationEnglish = dataApplication["applicationEnglish"].ToString();
applications.Add(application);
}
}
dataApplication.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandApplication.Dispose();
}
string sqlLanguage = @"
SELECT languageId,
languageCode
FROM language
WHERE isImportant = 1
AND isActive = 1 ";
var commandLanguage = new MySqlCommand(sqlLanguage, connection, transaction);
List<language> languages = new List<language>();
try
{
var data = commandLanguage.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
language language = new language();
language.languageId = Convert.ToInt32(data["languageId"]);
language.languageCode = data["languageCode"].ToString();
languages.Add(language);
}
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
foreach (dynamic application in applications)
{
foreach (dynamic language in languages)
{
string sqlCheckRecord = @"
SELECT *
FROM application_translate
WHERE languageId = @languageId
AND applicationId = @applicationId";
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandCheckRecord.Parameters.Add("@applicationId", MySqlDbType.Int32).Value = application.applicationId;
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
if (existedRecord == 0)
{
int languageId = Convert.ToInt32(language.languageId);
/**
string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" +
HttpUtility.UrlEncode(application.applicationEnglish) + "&from=en&to=" + language.languageCode;
System.Net.WebRequest translationWebRequest = System.Net.WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", headerValue);
System.Net.WebResponse response = null;
response = translationWebRequest.GetResponse();
System.IO.Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
System.IO.StreamReader translatedStream = new System.IO.StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string applicationTranslateNative = xTranslation.InnerText;
**/
//new
//*****BEGIN CODE TO MAKE THE CALL TO THE TRANSLATOR SERVICE TO PERFORM A TRANSLATION FROM THE USER TEXT ENTERED INCLUDES A CALL TO A SPEECH METHOD*****
string uri = string.Format("http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" + HttpUtility.UrlEncode(application.applicationEnglish) + "&from=en&to={0}", language.languageCode);
WebRequest translationWebRequest = WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", tokenProvider.GetAccessToken()); //header value is the "Bearer plus the token from ADM
WebResponse response = null;
response = translationWebRequest.GetResponse();
Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
StreamReader translatedStream = new StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string applicationTranslateNative = xTranslation.InnerText;
// new
// insert record into translate table
string sqlTranslateText = @"
INSERT INTO application_translate (
`applicationId`, `languageId`, `applicationTranslateNative`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES(
@applicationId, @languageId, @applicationTranslateNative,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTranslateText = new MySqlCommand(sqlTranslateText, connection, transaction);
commandTranslateText.Parameters.Add("@applicationId", MySqlDbType.Int32).Value = application.applicationId;
commandTranslateText.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandTranslateText.Parameters.Add("@applicationTranslateNative", MySqlDbType.String).Value = applicationTranslateNative;
commandTranslateText.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTranslateText.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTranslateText.ExecuteNonQuery();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
}
private void BatchModuleTranslate()
{
//MySqlTransaction transaction = connection.BeginTransaction();
int existedRecord = 0;
string sqlModule = @"
SELECT moduleId,
moduleEnglish
FROM module
WHERE isActive = 1
AND moduleId NOT IN (
SELECT DISTINCT moduleId
FROM module_translate
)";
var commandModule = new MySqlCommand(sqlModule, connection, transaction);
List<module> modules = new List<module>();
try
{
var dataModule = commandModule.ExecuteReader();
if (dataModule.HasRows)
{
while (dataModule.Read())
{
var module = new module();
module.moduleId = Convert.ToInt32(dataModule["moduleId"]);
module.moduleEnglish = dataModule["moduleEnglish"].ToString();
modules.Add(module);
}
}
dataModule.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandModule.Dispose();
}
string sqlLanguage = @"
SELECT languageId,
languageCode
FROM language
WHERE isImportant = 1
AND isActive = 1 ";
var commandLanguage = new MySqlCommand(sqlLanguage, connection, transaction);
List<language> languages = new List<language>();
try
{
var data = commandLanguage.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
language language = new language();
language.languageId = Convert.ToInt32(data["languageId"]);
language.languageCode = data["languageCode"].ToString();
languages.Add(language);
}
data.Close();
}
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
foreach (dynamic module in modules)
{
foreach (dynamic language in languages)
{
string sqlCheckRecord = @"
SELECT *
FROM module_translate
WHERE languageId = @languageId
AND moduleId = @moduleId";
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandCheckRecord.Parameters.Add("@moduleId", MySqlDbType.Int32).Value = module.moduleId;
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
// check if exist the record.
// insert if non
if (existedRecord == 0)
{
int languageId = Convert.ToInt32(language.languageId);
/**
string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" +
HttpUtility.UrlEncode(module.moduleEnglish) + "&from=en&to=" + language.languageCode;
System.Net.WebRequest translationWebRequest = System.Net.WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", headerValue);
System.Net.WebResponse response = null;
response = translationWebRequest.GetResponse();
System.IO.Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
System.IO.StreamReader translatedStream = new System.IO.StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string moduleTranslateNative = xTranslation.InnerText;
**/
string uri = string.Format("http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" + HttpUtility.UrlEncode(module.moduleEnglish) + "&from=en&to={0}", language.languageCode);
WebRequest translationWebRequest = WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", tokenProvider.GetAccessToken()); //header value is the "Bearer plus the token from ADM
WebResponse response = null;
response = translationWebRequest.GetResponse();
Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
StreamReader translatedStream = new StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string moduleTranslateNative = xTranslation.InnerText;
// insert record into translate table
string sqlTranslateText = @"
INSERT INTO module_translate (
`moduleId`, `languageId`, `moduleTranslateNative`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES(
@moduleId, @languageId, @moduleTranslateNative,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTranslateText = new MySqlCommand(sqlTranslateText, connection, transaction);
commandTranslateText.Parameters.Add("@moduleId", MySqlDbType.Int32).Value = module.moduleId;
commandTranslateText.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandTranslateText.Parameters.Add("@moduleTranslateNative", MySqlDbType.String).Value = moduleTranslateNative;
commandTranslateText.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTranslateText.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTranslateText.ExecuteNonQuery();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
}
private void BatchFolderTranslate()
{
//MySqlTransaction transaction = connection.BeginTransaction();
int existedRecord = 0;
string sqlFolder = @"SELECT folderId,folderEnglish FROM folder WHERE isActive = 1 AND folderId not in (select distinct folderId from folder_translate)";
var commandFolder = new MySqlCommand(sqlFolder, connection, transaction);
List<folder> folders = new List<folder>();
try
{
var dataFolder = commandFolder.ExecuteReader();
if (dataFolder.HasRows)
{
while (dataFolder.Read())
{
var folder = new folder();
folder.folderId = Convert.ToInt32(dataFolder["folderId"]);
folder.folderEnglish = dataFolder["folderEnglish"].ToString();
folders.Add(folder);
}
}
dataFolder.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandFolder.Dispose();
}
string sqlLanguage = @"
SELECT languageId,
languageCode
FROM language
WHERE isImportant = 1
AND isActive = 1 ";
var commandLanguage = new MySqlCommand(sqlLanguage, connection, transaction);
List<language> languages = new List<language>();
try
{
var data = commandLanguage.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
language language = new language();
language.languageId = Convert.ToInt32(data["languageId"]);
language.languageCode = data["languageCode"].ToString();
languages.Add(language);
}
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
foreach (dynamic folder in folders)
{
foreach (dynamic language in languages)
{
string sqlCheckRecord = @"
SELECT *
FROM folder_translate
WHERE languageId = @languageId
AND folderId = @folderId";
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandCheckRecord.Parameters.Add("@folderId", MySqlDbType.Int32).Value = folder.folderId;
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
if (existedRecord == 0)
{
// MessageBox.Show("ada lagi belum");
}
else
{
MessageBox.Show("Semua dah");
}
if (existedRecord == 0)
{
int languageId = Convert.ToInt32(language.languageId);
/**
string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" +
HttpUtility.UrlEncode(folder.folderEnglish) + "&from=en&to=" + language.languageCode;
System.Net.WebRequest translationWebRequest = System.Net.WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", headerValue);
System.Net.WebResponse response = null;
response = translationWebRequest.GetResponse();
System.IO.Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
System.IO.StreamReader translatedStream = new System.IO.StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string folderTranslateNative = xTranslation.InnerText;
**/
string uri = string.Format("http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" + HttpUtility.UrlEncode(folder.folderEnglish) + "&from=en&to={0}", language.languageCode);
WebRequest translationWebRequest = WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", tokenProvider.GetAccessToken()); //header value is the "Bearer plus the token from ADM
WebResponse response = null;
response = translationWebRequest.GetResponse();
Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
StreamReader translatedStream = new StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string folderTranslateNative = xTranslation.InnerText;
// insert record into translate table
string sqlTranslateText = @"
INSERT INTO folder_translate (
`folderId`, `languageId`, `folderTranslateNative`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES(
@folderId, @languageId, @folderTranslateNative,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTranslateText = new MySqlCommand(sqlTranslateText, connection, transaction);
commandTranslateText.Parameters.Add("@folderId", MySqlDbType.Int32).Value = folder.folderId;
commandTranslateText.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandTranslateText.Parameters.Add("@folderTranslateNative", MySqlDbType.String).Value = folderTranslateNative;
commandTranslateText.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTranslateText.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTranslateText.ExecuteNonQuery();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
}
private void BatchLeafTranslate()
{
//MySqlTransaction transaction = connection.BeginTransaction();
int existedRecord = 0;
string sqlLeaf = @"SELECT leafId,leafEnglish FROM leaf WHERE isActive = 1 AND leafId not in (select distinct leafId from leaf_translate) ";
var commandLeaf = new MySqlCommand(sqlLeaf, connection, transaction);
List<leaf> leafs = new List<leaf>();
try
{
var dataLeaf = commandLeaf.ExecuteReader();
if (dataLeaf.HasRows)
{
while (dataLeaf.Read())
{
var leaf = new leaf();
leaf.leafId = Convert.ToInt32(dataLeaf["leafId"]);
leaf.leafEnglish = dataLeaf["leafEnglish"].ToString();
leafs.Add(leaf);
}
}
dataLeaf.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
commandLeaf.Dispose();
}
string sqlLanguage = @"
SELECT languageId,
languageCode
FROM language
WHERE isImportant = 1
AND isActive = 1 ";
var commandLanguage = new MySqlCommand(sqlLanguage, connection, transaction);
List<language> languages = new List<language>();
try
{
var data = commandLanguage.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
language language = new language();
language.languageId = Convert.ToInt32(data["languageId"]);
language.languageCode = data["languageCode"].ToString();
languages.Add(language);
}
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
foreach (dynamic leaf in leafs)
{
foreach (dynamic language in languages)
{
string sqlCheckRecord = @"
SELECT *
FROM leaf_translate
WHERE languageId = @languageId
AND leafId = @leafId";
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandCheckRecord.Parameters.Add("@leafId", MySqlDbType.Int32).Value = leaf.leafId;
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
if (existedRecord == 0)
{
int languageId = Convert.ToInt32(language.languageId);
/**
string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" +
HttpUtility.UrlEncode(leaf.leafEnglish) + "&from=en&to=" + language.languageCode;
System.Net.WebRequest translationWebRequest = System.Net.WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", headerValue);
System.Net.WebResponse response = null;
response = translationWebRequest.GetResponse();
System.IO.Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
System.IO.StreamReader translatedStream = new System.IO.StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string leafTranslateNative = xTranslation.InnerText;
**/
string uri = string.Format("http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" + HttpUtility.UrlEncode(leaf.leafEnglish) + "&from=en&to={0}", language.languageCode);
WebRequest translationWebRequest = WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", tokenProvider.GetAccessToken()); //header value is the "Bearer plus the token from ADM
WebResponse response = null;
response = translationWebRequest.GetResponse();
Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
StreamReader translatedStream = new StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string leafTranslateNative = xTranslation.InnerText;
// insert record into translate table
string sqlTranslateText = @"
INSERT INTO leaf_translate (
`leafId`, `languageId`, `leafTranslateNative`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES(
@leafId, @languageId, @leafTranslateNative,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTranslateText = new MySqlCommand(sqlTranslateText, connection, transaction);
commandTranslateText.Parameters.Add("@leafId", MySqlDbType.Int32).Value = leaf.leafId;
commandTranslateText.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandTranslateText.Parameters.Add("@leafTranslateNative", MySqlDbType.String).Value = leafTranslateNative;
commandTranslateText.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTranslateText.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTranslateText.ExecuteNonQuery();
}
catch (MySqlException ex)
{
textBox1.AppendText("hui error apa ni " + ex.Message + "Source : " + ex.Source + ex.ToString());
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
}
private void BatchLabelTranslate()
{
int existedRecord = 0;
// distinct table name
string sqlLanguage = @"
SELECT languageId,
languageCode
FROM language
WHERE isImportant = 1
AND isActive = 1
AND languageId = 21";
var commandLanguage = new MySqlCommand(sqlLanguage, connection, transaction);
List<language> languages = new List<language>();
try
{
var data = commandLanguage.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
language language = new language();
language.languageId = Convert.ToInt32(data["languageId"]);
language.languageCode = data["languageCode"].ToString();
languages.Add(language);
}
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
// loop all label
List<label> labels = new List<label>();
string sqlTableTranslate = @"SELECT labelId,labelCode,labelEnglish FROM label where labelId not in ( select distinct labelId from label_translate)";
var commandTableTranslate = new MySqlCommand(sqlTableTranslate, connection, transaction);
try
{
var data = commandTableTranslate.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
label l = new label();
l.labelId = Convert.ToInt32(data["labelId"]);
l.labelCode = data["labelCode"].ToString();
l.labelEnglish = data["labelEnglish"].ToString();
labels.Add(l);
}
}
else
{
textBox1.AppendText("no record loh");
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
// start translate detail table
foreach (dynamic label in labels)
{
foreach (dynamic language in languages)
{
string sqlCheckRecord = @"
SELECT *
FROM label_translate
WHERE languageId = @languageId
AND labelId = @labelId";
textBox1.AppendText("language id" + language.languageId + " label id " + label.labelId + Environment.NewLine);
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandCheckRecord.Parameters.Add("@labelId", MySqlDbType.Int32).Value = label.labelId;
textBox1.AppendText(sqlCheckRecord + Environment.NewLine);
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
textBox1.AppendText("ada tak record" + existedRecord + Environment.NewLine);
if (existedRecord == 0)
{
int languageId = Convert.ToInt32(language.languageId);
/**
string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" +
HttpUtility.UrlEncode(label.labelEnglish) + "&from=en&to=" + language.languageCode;
textBox1.AppendText(" url translate" + uri + Environment.NewLine);
System.Net.WebRequest translationWebRequest = System.Net.WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", headerValue);
System.Net.WebResponse response = null;
response = translationWebRequest.GetResponse();
System.IO.Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
System.IO.StreamReader translatedStream = new System.IO.StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string labelTranslateNative = xTranslation.InnerText;
**/
string labelTranslateNative = string.Empty;
if (languageId != 21)
{
string uri = string.Format("http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" + HttpUtility.UrlEncode(label.labelEnglish) + "&from=en&to={0}", language.languageCode);
WebRequest translationWebRequest = WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", tokenProvider.GetAccessToken()); //header value is the "Bearer plus the token from ADM
WebResponse response = null;
response = translationWebRequest.GetResponse();
Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
StreamReader translatedStream = new StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
labelTranslateNative = xTranslation.InnerText;
}
else
{
labelTranslateNative = label.labelEnglish;
}
// insert record into translate table
string sqlTranslateText = @"
INSERT INTO label_translate (
`labelId`, `languageId`, `labelTranslateNative`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES(
@labelId, @languageId, @labelTranslateNative,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTranslateText = new MySqlCommand(sqlTranslateText, connection, transaction);
commandTranslateText.Parameters.Add("@labelId", MySqlDbType.Int32).Value = label.labelId;
commandTranslateText.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandTranslateText.Parameters.Add("@labelTranslateNative", MySqlDbType.String).Value = labelTranslateNative;
commandTranslateText.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTranslateText.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTranslateText.ExecuteNonQuery();
}
catch (MySqlException ex)
{
textBox1.AppendText("hui error apa ni " + ex.Message + "Source : " + ex.Source + ex.ToString());
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
}
private void GenerateTableColumnNameLabel()
{
int existedRecord = 0;
string targetDatabase = "fish";
List<tableMappingColumn> tableMappingColumns = new List<tableMappingColumn>();
string sql = @"
SELECT table_name,
column_name,
ordinal_position
FROM information_schema.columns
WHERE table_schema = '" + targetDatabase + "'";
try
{
var command = new MySqlCommand(sql.ToString(), connection);
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var column = new tableMappingColumn();
column.tableMappingName = reader["table_name"].ToString();
column.tableMappingColumnName = reader["column_name"].ToString();
column.ordinalPosition = Convert.ToInt32(reader["ordinal_position"]);
tableMappingColumns.Add(column);
}
reader.Close();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
// insert record to tablemapping
foreach (dynamic x in tableMappingColumns)
{
string sqlCheckRecord = @"
SELECT tableMappingName,
tableMappingColumnName
FROM table_mapping
WHERE tableMappingName = @tableMappingName
AND tableMappingColumnName = @tableMappingColumnName";
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@tableMappingName", MySqlDbType.VarChar).Value = x.tableMappingName;
commandCheckRecord.Parameters.Add("@tableMappingColumnName", MySqlDbType.VarChar).Value = x.tableMappingColumnName;
textBox1.AppendText("Name" + x.tableMappingName + Environment.NewLine);
textBox1.AppendText("Column" + x.tableMappingColumnName + Environment.NewLine);
textBox1.AppendText(sqlCheckRecord + Environment.NewLine);
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
else
{
existedRecord = 0;
}
data.Close();
}
catch (MySqlException ex)
{
//transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
if (existedRecord == 0)
{
string SqlInsert = @"
INSERT INTO `fish`.`table_mapping` (
`tableMappingName`, `tableMappingColumnName`, `tableMappingEnglish`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES (
@tableMappingName, @tableMappingColumnName, @tableMappingEnglish,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTableMapping = new MySqlCommand(SqlInsert, connection, transaction);
commandTableMapping.Parameters.Add("@tableMappingName", MySqlDbType.String).Value = x.tableMappingName;
commandTableMapping.Parameters.Add("@tableMappingColumnName", MySqlDbType.String).Value = x.tableMappingColumnName;
commandTableMapping.Parameters.Add("@tableMappingEnglish", MySqlDbType.String).Value = UppercaseFirst(ToLowercaseNamingConvention(x.tableMappingColumnName, x.tableMappingName, x.ordinalPosition));
commandTableMapping.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTableMapping.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTableMapping.ExecuteNonQuery();
}
catch (MySqlException ex)
{
textBox1.AppendText("hui error apa ni " + ex.Message + "Source : " + ex.Source + ex.ToString());
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
private void BatchTableTranslate()
{
int existedRecord = 0;
// distinct table name
string sqlLanguage = @"
SELECT languageId,
languageCode
FROM language
WHERE isImportant = 1
AND isActive = 1 ";
var commandLanguage = new MySqlCommand(sqlLanguage, connection, transaction);
List<language> languages = new List<language>();
try
{
var data = commandLanguage.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
language language = new language();
language.languageId = Convert.ToInt32(data["languageId"]);
language.languageCode = data["languageCode"].ToString();
languages.Add(language);
}
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
// loop all table name
List<tableMapping> tableMappings = new List<tableMapping>();
string sqlTableTranslate = @"SELECT tableMappingId,tableMappingName,tableMappingColumnName,tableMappingEnglish FROM table_mapping where tableMappingId not in ( SELECT distinct tableMappingId FROM table_mapping_translate ) ";
var commandTableTranslate = new MySqlCommand(sqlTableTranslate, connection, transaction);
try
{
var data = commandTableTranslate.ExecuteReader();
if (data.HasRows)
{
while (data.Read())
{
tableMapping tb = new tableMapping();
tb.tableMappingId = Convert.ToInt32(data["tableMappingId"]);
tb.tableMappingName = data["tableMappingName"].ToString();
tb.tableMappingColumnName = data["tableMappingColumnName"].ToString();
tb.tableMappingEnglish = data["tableMappingEnglish"].ToString();
tableMappings.Add(tb);
}
}
else
{
textBox1.AppendText("no record loh");
}
data.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
// start translate detail table
foreach (dynamic tableMapping in tableMappings)
{
foreach (dynamic language in languages)
{
string sqlCheckRecord = @"
SELECT *
FROM table_mapping_translate
WHERE languageId = @languageId
AND tableMappingId = @tableMappingId";
var commandCheckRecord = new MySqlCommand(sqlCheckRecord, connection, transaction);
commandCheckRecord.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandCheckRecord.Parameters.Add("@tableMappingId", MySqlDbType.Int32).Value = tableMapping.tableMappingId;
textBox1.AppendText(sqlCheckRecord + Environment.NewLine);
try
{
var data = commandCheckRecord.ExecuteReader();
if (data.HasRows)
{
existedRecord = 1;
}
else
{
existedRecord = 0;
}
data.Close();
}
catch (MySqlException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
commandCheckRecord.Dispose();
}
if (existedRecord == 0)
{
int languageId = Convert.ToInt32(language.languageId);
/**
string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" +
HttpUtility.UrlEncode(tableMapping.tableMappingEnglish) + "&from=en&to=" + language.languageCode;
textBox1.AppendText(uri + Environment.NewLine);
// MessageBox.Show(uri);
System.Net.WebRequest translationWebRequest = System.Net.WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", headerValue);
System.Net.WebResponse response = null;
response = translationWebRequest.GetResponse();
System.IO.Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
System.IO.StreamReader translatedStream = new System.IO.StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string tableMappingTranslateNative = xTranslation.InnerText;
**/
string uri = string.Format("http://api.microsofttranslator.com/v2/Http.svc/Translate?text=" + HttpUtility.UrlEncode(tableMapping.tableMappingEnglish) + "&from=en&to={0}", language.languageCode);
WebRequest translationWebRequest = WebRequest.Create(uri);
translationWebRequest.Headers.Add("Authorization", tokenProvider.GetAccessToken()); //header value is the "Bearer plus the token from ADM
WebResponse response = null;
response = translationWebRequest.GetResponse();
Stream stream = response.GetResponseStream();
Encoding encode = Encoding.GetEncoding("utf-8");
StreamReader translatedStream = new StreamReader(stream, encode);
System.Xml.XmlDocument xTranslation = new System.Xml.XmlDocument();
xTranslation.LoadXml(translatedStream.ReadToEnd());
string tableMappingTranslateNative = xTranslation.InnerText;
// insert record into translate table
string sqlTranslateText = @"
INSERT INTO table_mapping_translate (
`tableMappingId`, `languageId`, `tableMappingTranslateNative`,
`isDefault`, `isNew`, `isDraft`,
`isUpdate`, `isDelete`, `isActive`,
`isApproved`, `isReview`, `isPost`,
`executeBy`, `executeTime`
) VALUES(
@tableMappingId, @languageId, @tableMappingTranslateNative,
0, 1, 0,
0, 0, 1,
0, 0, 0,
@executeBy, @executeTime
);";
var commandTranslateText = new MySqlCommand(sqlTranslateText, connection, transaction);
commandTranslateText.Parameters.Add("@tableMappingId", MySqlDbType.Int32).Value = tableMapping.tableMappingId;
commandTranslateText.Parameters.Add("@languageId", MySqlDbType.Int32).Value = language.languageId;
commandTranslateText.Parameters.Add("@tableMappingTranslateNative", MySqlDbType.String).Value = tableMappingTranslateNative;
commandTranslateText.Parameters.Add("@executeBy", MySqlDbType.VarChar).Value = "hafizan";
commandTranslateText.Parameters.Add("@executeTime", MySqlDbType.DateTime).Value = DateTime.Now;
try
{
commandTranslateText.ExecuteNonQuery();
}
catch (MySqlException ex)
{
textBox1.AppendText("hui error apa ni " + ex.Message + "Source : " + ex.Source + ex.ToString());
transaction.Rollback();
MessageBox.Show(ex.Message);
}
}
}
}
}
public string UppercaseFirst(string s)
{
// Check for empty string.
if (string.IsNullOrEmpty(s))
{
return string.Empty;
}
// Return char and concat substring.
return char.ToUpper(s[0]) + s.Substring(1);
}
/// <summary>
///
/// </summary>
/// <param name="s">Column Name</param>
/// <param name="t">Table Name</param>
/// <param name="od">Ordinal Position</param>
/// <returns></returns>
public string ToLowercaseNamingConvention(string s, string t, int od)
{
t = t.ToLower();
if (t.IndexOf("_") > 0)
{
string[] splitTableName = t.Split('_');
for (int i = 0; i < splitTableName.Length; i++) // Loop with for.
{
if (i > 0)
{
splitTableName[i] = UppercaseFirst(splitTableName[i]);
}
}
t = string.Join("", splitTableName);
}
if (od == 1)
{
s = s.Replace("Id", "");
}
else {
if (s.IndexOf(t) != -1)
{
s = s.Replace(t, "");
}
if (s.IndexOf("Id") != -1)
{
s = s.Replace("Id", "");
}
}
if (s.IndexOf("is") != -1)
{
if (s.Substring(0, 2) == "is")
{
s = s.Replace("is", "");
}
}
var r = new Regex(@"
(?<=[A-Z])(?=[A-Z][a-z]) |
(?<=[^A-Z])(?=[A-Z]) |
(?<=[A-Za-z])(?=[^A-Za-z])", RegexOptions.IgnorePatternWhitespace);
return r.Replace(s, " ");
}
public List<string> checkString(string str)
{
List<string> x = new List<string>();
for (int i = 0; i < str.Length; i++)
{
if (char.IsUpper(str[i]))
{
x.Add(str[i].ToString());
}
}
return x;
}
}
public class AdmAccessToken
{
public string access_token { get; set; }
public string token_type { get; set; }
public string expires_in { get; set; }
public string scope { get; set; }
}
/// <summary>
/// Role Data Contract
/// </summary>
public class role
{
public int roleId { get; set; }
public int isAdmin { get; set; }
}
public class application
{
public int applicationId { get; set; }
public string applicationEnglish { get; set; }
}
public class module
{
public int moduleId { get; set; }
public string moduleEnglish { get; set; }
}
public class folder
{
public int folderId { get; set; }
public string folderEnglish { get; set; }
}
public class leaf
{
public int leafId { get; set; }
public string leafEnglish { get; set; }
}
public class user
{
public int userId { get; set; }
public int isAdmin { get; set; }
}
public class language
{
public int languageId { get; set; }
public string languageCode { get; set; }
}
public class label
{
public int labelId { get; set; }
public string labelCode { get; set; }
public string labelEnglish { get; set; }
}
public class tableMappingColumn
{
public int ordinalPosition { get; set; }
public string tableMappingName { get; set; }
public string tableMappingColumnName { get; set; }
}
public class tableMapping
{
public int tableMappingId { get; set; }
public string tableMappingName { get; set; }
public string tableMappingColumnName { get; set; }
public string tableMappingEnglish { get; set; }
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment