private void CopyValueToLocalizedTable(ConfirmedLocalizedTable confirmedLocalizedTable)
{
List<string> columnsToLocalize = confirmedLocalizedTable.ColumnsToLocalize;
try
{
var localizedColumnPrimaryKeyName = get_LOCALIZEDColumnPrimaryKeyName(confirmedLocalizedTable.TableName);
columnsToLocalize.Add(localizedColumnPrimaryKeyName); //Add coresponding ID Column
//Add data to %_LOCALIZED Table
if (!AddDataTo_LocalizedTable(confirmedLocalizedTable))
{
listViewFailTables.Items.Add(confirmedLocalizedTable.TableName);
return;
}
//}
listViewSuccessTables.Items.Add(confirmedLocalizedTable.TableName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + @" Er 1");
listViewFailTables.Items.Add(confirmedLocalizedTable.TableName);
}
}
private static List<ConfirmedLocalizedTable> GetConfirmedLocalizedTablesList(string pathToXls)
{
DataTable confirmedDataTable = ExcelManipulation.ExcelToDataTable(pathToXls, "Confirmed");
var confirmedLocalizedTablesList = new List<ConfirmedLocalizedTable>();
foreach (DataRow row in confirmedDataTable.Rows)
{
var confirmedLocalizedTable = new ConfirmedLocalizedTable { TableName = row["Table Name"].ToString().Trim() };
var columnsToLocalize = new List<string>();
if (string.Equals(row["Note"].ToString().Trim(), "Localize", StringComparison.CurrentCultureIgnoreCase))
{
for (var i = 1; i <= 8; i++)
{
string columnName = "Column " + i;
if (!string.IsNullOrWhiteSpace(row[columnName].ToString()))
{
columnsToLocalize.Add(row[columnName].ToString().Trim());
}
else
{
break;
}
}
confirmedLocalizedTable.ColumnsToLocalize = columnsToLocalize;
confirmedLocalizedTablesList.Add(confirmedLocalizedTable);
}
}
return confirmedLocalizedTablesList;
}
private List<List<string>> getEN_AUData(ConfirmedLocalizedTable confirmedLocalizedTable)
{
string query = @"SELECT ";
foreach (var item in confirmedLocalizedTable.ColumnsToLocalize)
{
query += string.Format(@"{0}, ", item);
}
var tablePrimaryKey = GetColumnPrimaryKeyName(confirmedLocalizedTable.TableName);
foreach (var item in tablePrimaryKey)
{
query += item + ", ";
}
query = query.Substring(0, query.Length - 2)
+ string.Format(@" FROM {0}", confirmedLocalizedTable.TableName); //Old: {0}_LOCALIZED WHERE
var command = new SqlCommand(query, _connection);
var tableListTable = new DataTable("TableList");
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(tableListTable);
var dataEnEuList = new List<List<string>>();
if (tableListTable.Rows != null && tableListTable.Rows.Count > 0)
{
foreach (DataRow row in tableListTable.Rows)
{
var columnData = new List<string>();
for (int i = 0; i < tableListTable.Columns.Count; i++)
{
columnData.Add(row.ItemArray[i].ToString());
}
dataEnEuList.Add(columnData);
}
}
return dataEnEuList;
}
private void UpdateTranslatedData()
{
var confirmedLocalizedTablesList = GetConfirmedLocalizedTablesList(tbXLSFilePath.Text);
if (_connection == null || _connection.State != ConnectionState.Open) return;
try
{
foreach (var itemTablesList in confirmedLocalizedTablesList)
{
_currentWorkingTable = itemTablesList;
var dataEnEuList = getEN_AUData(itemTablesList);
var tablePrimaryKey = GetColumnPrimaryKeyName(itemTablesList.TableName);
List<string> supportedLanguage = GetSupportedLaguages();
foreach (var itemSupportedLanguage in supportedLanguage)
{
var query = string.Format(@"UPDATE {0}_LOCALIZED SET ", itemTablesList.TableName);
foreach (var itemColumnsToLocalize in itemTablesList.ColumnsToLocalize)
{
//Avoid backspace in Column name
query += string.Format(@"[{0}] = @{1}, ", itemColumnsToLocalize, itemColumnsToLocalize.Replace(' ', '.'));
}
query = query.Substring(0, query.Length - 2) + " WHERE ";
query += string.Format(@"[{0}] = @{1} ", tablePrimaryKey[0], tablePrimaryKey[0].Replace(' ', '.'));
for (int i = 1; i < tablePrimaryKey.Length; i++)
{
query += string.Format(@"AND [{0}] = @{1}", tablePrimaryKey[i], tablePrimaryKey[i].Replace(' ', '.'));
}
query += @" AND (";
int[] ouCultureKeyArray = getOU_CULTURE_KEYByCultureName(itemSupportedLanguage);
foreach (var itemOuCultureKeyArray in ouCultureKeyArray)
{
query += string.Format(" OU_CULTURE_KEY = {0} OR ", itemOuCultureKeyArray);
}
query = query.Substring(0, query.Length - 3) + " )";
foreach (var itemDataEnEuList in dataEnEuList)
{
var cmd = new SqlCommand(query, _connection);
int i = 0;
foreach (var itemColumnsToLocalize in itemTablesList.ColumnsToLocalize)
{
var value = itemDataEnEuList.ElementAt(i++);
if (value.Equals(string.Empty))
{
cmd.Parameters.AddWithValue(itemColumnsToLocalize, DBNull.Value);
}
else
{
if (itemSupportedLanguage.Substring(0, 2).Equals("en", StringComparison.CurrentCultureIgnoreCase))
{
cmd.Parameters.AddWithValue(itemColumnsToLocalize, value);
}
else
{
var translatedText = "";
var charactersCount = value.Length;
var end = 0;
var hasPostionOfEndChange = false;
if (charactersCount > 2000)
{
string translatedPart;
for (int ic = 0; ic < charactersCount / 2000; ic++)
{
int start = 2000 * ic;
if (hasPostionOfEndChange)
{
start = end;
hasPostionOfEndChange = false;
}
end = 2000 * (ic + 1);
const int maxWordLeng = 50;
int changePositionToBackspace = 0;
while (changePositionToBackspace++ < maxWordLeng && end < value.Length && value[end] != ' ')
{
end++;
hasPostionOfEndChange = true;
}
translatedPart =
TranslateText(value.Substring(start, end - start), "en",
itemSupportedLanguage.Substring(0, 2));
if (translatedPart.Equals(string.Empty))
{
SetText(listFail, _currentWorkingTable.TableName);
return;
}
translatedText += translatedPart;
}
translatedPart = TranslateText(value.Substring(end, value.Length - end), "en", itemSupportedLanguage.Substring(0, 2));
if (translatedPart.Equals(string.Empty))
{
SetText(listFail, _currentWorkingTable.TableName);
return;
}
translatedText += translatedPart;
}
else
{
translatedText = TranslateText(value, "en", itemSupportedLanguage.Substring(0, 2));
if (translatedText.Equals(string.Empty))
{
SetText(listFail, _currentWorkingTable.TableName);
return;
}
}
cmd.Parameters.AddWithValue(itemColumnsToLocalize, translatedText);
}
}
}
foreach (string itemTablePrimaryKey in tablePrimaryKey)
{
cmd.Parameters.AddWithValue(itemTablePrimaryKey, itemDataEnEuList.ElementAt(i++));
}
cmd.ExecuteNonQuery();
SetText(lblCurentTb, _currentWorkingTable.TableName);
}
}
}
ConnectionManager.Instance.CloseConnection(ref _connection);
}
catch (Exception)
{
SetText(listFail, _currentWorkingTable.TableName);
}
}
private bool AddDataTo_LocalizedTable(ConfirmedLocalizedTable confirmedLocalizedTable)
{
//Get Data from Text boxes
var newCultureName = NewCultureNameTextBox.Text;
var newOU_CULTURE_KEY = OUCultureKeyTextbox.Text;
var localizedColumnPrimaryKeyName = get_LOCALIZEDColumnPrimaryKeyName(confirmedLocalizedTable.TableName);
if (_connection == null || _connection.State != ConnectionState.Open) return false;
try
{
var columnsToLocalize = confirmedLocalizedTable.ColumnsToLocalize;
var tableName = confirmedLocalizedTable.TableName + "_LOCALIZED";
string query = string.Format("INSERT INTO [{0}] (", tableName);
query = columnsToLocalize.Aggregate(query, (current, coumnToLocalize) => current + string.Format(@"[{0}], ", coumnToLocalize));
query += "[OU_CULTURE_KEY]) SELECT ";
foreach (string column in columnsToLocalize)
{
if (!column.Equals(localizedColumnPrimaryKeyName))
{
query += string.Format("'{0}' + cast([{1}] as nvarchar(max)) as [{1}], ", newCultureName, column);
}
else
{
query += string.Format("[{0}], ", column);
}
}
query += string.Format("{0} as [OU_CULTURE_KEY]", newOU_CULTURE_KEY);
query += string.Format(" FROM {0}", confirmedLocalizedTable.TableName);
var cmd = new SqlCommand(query, _connection);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception)
{
return false;
}
}
