public void GetResWordByKey(ResWord word, string to)
{
var dbManager = GetDb();
var sql = new SqlQuery(ResDataTable)
.Select("textvalue", "description", "link")
.Where("fileID", word.ResFile.FileID)
.Where("cultureTitle", "Neutral")
.Where("title", word.Title);
dbManager.ExecuteList(sql).ForEach(r => GetValue(word, to, r));
GetValueByKey(word, to);
sql = new SqlQuery(ResDataTable + " as res1").Select("res1.textvalue").Distinct()
.InnerJoin(ResDataTable + " as res2", Exp.EqColumns("res1.title", "res2.title") & Exp.EqColumns("res1.fileid", "res2.fileid"))
.Where("res1.cultureTitle", to)
.Where("res2.cultureTitle", "Neutral")
.Where("res2.textvalue", word.ValueFrom);
word.Alternative = new List <string>();
dbManager.ExecuteList(sql).ForEach(r => word.Alternative.Add((string)r[0]));
word.Alternative.Remove(word.ValueTo);
sql = new SqlQuery(ResFilesTable)
.Select("resname")
.Where("id", word.ResFile.FileID);
word.ResFile.FileName = dbManager.ExecuteScalar <string>(sql);
}
private static SqlQuery GetQuery(string table, string cultureTitle, ResWord word)
{
return(new SqlQuery(table)
.Select("textvalue")
.Where("fileID", word.ResFile.FileID)
.Where("cultureTitle", cultureTitle)
.Where("title", word.Title));
}
public void AddComment(ResWord word)
{
var dbManager = GetDb();
var sqlUpdate = new SqlUpdate(ResDataTable);
sqlUpdate.Set("description", word.TextComment).Where("title", word.Title).Where("fileID", word.ResFile.FileID).Where("cultureTitle", "Neutral");
dbManager.ExecuteNonQuery(sqlUpdate);
}
public void EditEnglish(ResWord word)
{
var dbManager = GetDb();
var update = new SqlUpdate(ResDataTable);
update.Set("textvalue", word.ValueFrom).Where("fileID", word.ResFile.FileID).Where("title", word.Title).Where("cultureTitle", "Neutral");
dbManager.ExecuteNonQuery(update);
}
private static SqlUpdate Update(string table, string cultureTitle, ResWord word)
{
return(new SqlUpdate(table)
.Set("flag", 2)
.Set("textvalue", word.ValueFrom)
.Where("fileID", word.ResFile.FileID)
.Where("title", word.Title)
.Where("cultureTitle", cultureTitle));
}
private void GetValue(ResWord word, string to, IList <object> r)
{
word.ValueFrom = (string)r[0] ?? "";
word.TextComment = (string)r[1] ?? "";
var langs = (ConfigurationManager.AppSettings["resources.com-lang"] ?? string.Empty).Split(';').ToList();
var dom = langs.Exists(lang => lang == to) ? ".info" : ".com";
word.Link = !string.IsNullOrEmpty((string)r[2]) ? string.Format("http://{0}-translator.teamlab{1}{2}", to, dom, r[2]) : "";
}
/*public List<ResWord> GetListResWords(ResFile resFile, string to, string search)
* {
* var dbManager = GetDb();
* var sql = new SqlQuery(ResDataTable)
* .Select("title", "fileid", "textValue", "description", "flag", "link")
* .InnerJoin(ResFilesTable, Exp.EqColumns(ResFilesTable + ".ID", ResDataTable + ".fileID"))
* .Where("moduleName", resFile.ModuleName)
* .Where("projectName", resFile.ProjectName)
* .Where("cultureTitle", to)
* .Where("flag != 4")
* .Where("resourceType", "text")
* .OrderBy(ResDataTable + ".id", true);
*
* if (!string.IsNullOrEmpty(resFile.FileName))
* sql.Where("resName", resFile.FileName);
*
* if (!string.IsNullOrEmpty(search))
* sql.Where(Exp.Like("textvalue", search));
*
* return dbManager.ExecuteList(sql).ConvertAll(GetWord);
* }
*/
/* public void GetListModules(ResCurrent currentData)
* {
* var dbManager = GetDb();
* var notExist = new SqlQuery(ResDataTable + " rd1")
* .Select("1")
* .Where("rd1.fileid = rd.fileid")
* .Where("rd1.title = concat('del_', rd.title)")
* .Where("rd1.cultureTitle = 'Neutral'");
*
* var exist = new SqlQuery(ResDataTable + " rd2")
* .Select("1")
* .Where("rd2.fileid = rd.fileid")
* .Where("rd2.title = rd.title")
* .Where("rd2.cultureTitle = 'Neutral'");
*
* var sql = new SqlQuery(ResFilesTable + " rf").Select("rf.moduleName",
* string.Format("sum(case rd.cultureTitle when '{0}' then (case rd.flag when 3 then 0 else 1 end) else 0 end)", currentData.Language.Title),
* string.Format("sum(case rd.cultureTitle when '{0}' then (case rd.flag when 3 then 1 else 0 end) else 0 end)", currentData.Language.Title),
* string.Format("sum(case rd.cultureTitle when '{0}' then 1 else 0 end)", "Neutral"))
* .InnerJoin(ResDataTable + " rd", Exp.EqColumns("rd.fileid", "rf.id"))
* .Where("rf.projectName", currentData.Project.Name)
* .Where("rd.resourceType", "text")
* .Where(!Exp.Like("rd.title", @"del\_", SqlLike.StartWith) & Exp.Exists(exist) & !Exp.Exists(notExist))
* .GroupBy("moduleName");
*
*
* dbManager.ExecuteList(sql).ForEach(r =>
* {
* var module = currentData.Project.Modules.Find(mod => mod.Name == r[0].ToString());
* if (module == null) return;
* module.Counts[WordStatusEnum.Translated] = Convert.ToInt32(r[1]);
* module.Counts[WordStatusEnum.Changed] = Convert.ToInt32(r[2]);
* module.Counts[WordStatusEnum.All] = Convert.ToInt32(r[3]);
* module.Counts[WordStatusEnum.Untranslated] = module.Counts[WordStatusEnum.All] - module.Counts[WordStatusEnum.Changed] - module.Counts[WordStatusEnum.Translated];
* });
* }*/
/* public void LockModules(string projectName, string modules)
* {
* var dbManager = GetDb();
* var sqlUpdate = new SqlUpdate(ResFilesTable);
* sqlUpdate.Set("isLock", 1).Where("projectName", projectName).Where(Exp.In("moduleName", modules.Split(',')));
* dbManager.ExecuteNonQuery(sqlUpdate);
* } */
/* public void UnLockModules()
* {
* var dbManager = GetDb();
* var sqlUpdate = new SqlUpdate(ResFilesTable);
* sqlUpdate.Set("isLock", 0);
* dbManager.ExecuteNonQuery(sqlUpdate);
* } */
/* public void AddLink(string resource, string fileName, string page)
* {
* var dbManager = GetDb();
* var query = new SqlQuery(ResDataTable);
* query.Select(ResDataTable + ".id")
* .InnerJoin(ResFilesTable, Exp.EqColumns(ResFilesTable + ".id", ResDataTable + ".fileid"))
* .Where(ResDataTable + ".title", resource).Where(ResFilesTable + ".resName", fileName).Where(ResDataTable + ".cultureTitle", "Neutral");
*
* var key = dbManager.ExecuteScalar<int>(query);
*
* var update = new SqlUpdate(ResDataTable);
* update.Set("link", page).Where("id", key);
* dbManager.ExecuteNonQuery(update);
* } */
/* public void GetResWordByKey(ResWord word, string to)
* {
* var dbManager = GetDb();
* var sql = new SqlQuery(ResDataTable)
* .Select("textvalue", "description", "link")
* .Where("fileID", word.ResFile.FileID)
* .Where("cultureTitle", "Neutral")
* .Where("title", word.Title);
*
* dbManager.ExecuteList(sql).ForEach(r => GetValue(word, to, r));
*
* GetValueByKey(word, to);
*
* sql = new SqlQuery(ResDataTable + " as res1").Select("res1.textvalue").Distinct()
* .InnerJoin(ResDataTable + " as res2", Exp.EqColumns("res1.title", "res2.title") & Exp.EqColumns("res1.fileid", "res2.fileid"))
* .Where("res1.cultureTitle", to)
* .Where("res2.cultureTitle", "Neutral")
* .Where("res2.textvalue", word.ValueFrom);
*
* word.Alternative = new List<string>();
* dbManager.ExecuteList(sql).ForEach(r => word.Alternative.Add((string)r[0]));
* word.Alternative.Remove(word.ValueTo);
*
* sql = new SqlQuery(ResFilesTable)
* .Select("resname")
* .Where("id", word.ResFile.FileID);
*
* word.ResFile.FileName = dbManager.ExecuteScalar<string>(sql);
* } */
public void GetValueByKey(ResWord word, string to)
{
var valueTo = DbContext.ResData
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.CultureTitle == to)
.Where(r => r.Title == word.Title)
.Select(r => r.TextValue)
.FirstOrDefault();
word.ValueTo = valueTo ?? "";
}
public void GetValueByKey(ResWord word, string to)
{
var dbManager = GetDb();
var sql = new SqlQuery(ResDataTable);
sql.Select("textvalue")
.Where("fileID", word.ResFile.FileID)
.Where("cultureTitle", to)
.Where("title", word.Title);
word.ValueTo = dbManager.ExecuteScalar <string>(sql) ?? "";
}
/* public DateTime GetLastUpdate()
* {
* return DbContext.ResFiles.Max(r => r.LastUpdate);
* } */
/* public List<ResCulture> GetListLanguages(int fileId, string title)
* {
* var sql = DbContext.ResCultures
* .Join(DbContext.ResData.DefaultIfEmpty(), r => r.Title, r => r.CultureTitle, (c, d) => new { data = d, culture = c })
* .Where(r => r.data.FileId == fileId)
* .Where(r => r.data.Title == title);
*
* var language = sql.Select(r => GetCultureFromDB(r.culture)).ToList();
*
* language.Remove(language.Find(p => p.Title == "Neutral"));
*
* return language;
* } */
/* public Dictionary<ResCulture, List<string>> GetCulturesWithAuthors()
* {
* return
* DbContext.ResAuthorsLang
* .Join(DbContext.ResCultures, r => r.CultureTitle, r => r.Title, (al, rc) => new { cultures = rc, authorsLang = al })
* .Join(DbContext.Authors, r => r.authorsLang.AuthorLogin, r => r.Login, (a, al) => new { authors = al, a.cultures, a.authorsLang })
* .Where(r => r.authors.IsAdmin == false)
* .ToList()
* .GroupBy(r => new ResCulture { Title = r.cultures.Title, Value = r.cultures.Value }, r => r.authorsLang.AuthorLogin)
* .ToDictionary(r => r.Key, r => r.ToList());
* } */
/* public void AddCulture(string cultureTitle, string name)
* {
* var culture = new ResCultures
* {
* Title = cultureTitle,
* Value = name
* };
*
* DbContext.ResCultures.Add(culture);
* DbContext.SaveChanges();
* } */
public void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin, bool updateIfExist = true)
{
var resData = DbContext.ResData
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.CultureTitle == cultureTitle)
.Where(r => r.Title == word.Title)
.Select(r => r.TextValue)
.FirstOrDefault();
var resReserve = DbContext.ResReserve
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.CultureTitle == cultureTitle)
.Where(r => r.Title == word.Title)
.Select(r => r.TextValue)
.FirstOrDefault();
if (string.IsNullOrEmpty(resData))
{
var newResData = new ResData
{
Title = word.Title,
TextValue = word.ValueFrom,
CultureTitle = cultureTitle,
FileId = word.ResFile.FileID,
ResourceType = resType,
TimeChanges = date,
Flag = 2,
AuthorLogin = authorLogin
};
DbContext.ResData.Add(newResData);
DbContext.SaveChanges();
if (isConsole)
{
var newResReserve = new ResReserve
{
Title = word.Title,
TextValue = word.ValueFrom,
CultureTitle = cultureTitle,
FileId = word.ResFile.FileID
};
DbContext.ResReserve.Add(newResReserve);
DbContext.SaveChanges();
}
}
else
{
if (cultureTitle == "Neutral" && isConsole)
{
updateIfExist =
DbContext.ResData
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.CultureTitle != cultureTitle)
.Where(r => r.Title == word.Title)
.Count() == 0;
}
var isChangeResData = resData != word.ValueFrom;
var isChangeResReserve = resReserve != word.ValueFrom;
if (!updateIfExist)
{
return;
}
if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
{
if (cultureTitle == "Neutral")
{
var forUpdate = DbContext.ResData
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.Title == word.Title)
.ToList();
foreach (var f in forUpdate)
{
f.Flag = 3;
}
DbContext.SaveChanges();
}
var newResData = new ResData
{
Title = word.Title,
TextValue = word.ValueFrom,
CultureTitle = cultureTitle,
FileId = word.ResFile.FileID,
ResourceType = resType,
TimeChanges = date,
Flag = 2,
AuthorLogin = authorLogin
};
DbContext.ResData.Add(newResData);
DbContext.SaveChanges();
if (isConsole)
{
var resReserveForUpdate = DbContext.ResReserve
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.Title == word.Title)
.Where(r => r.CultureTitle == cultureTitle)
.ToList();
foreach (var r in resReserveForUpdate)
{
r.Flag = 2;
r.TextValue = word.ValueFrom;
}
DbContext.SaveChanges();
}
}
else if (isChangeResData)
{
var resReserveForUpdate = DbContext.ResReserve
.Where(r => r.FileId == word.ResFile.FileID)
.Where(r => r.Title == word.Title)
.Where(r => r.CultureTitle == cultureTitle)
.ToList();
foreach (var r in resReserveForUpdate)
{
r.Flag = 2;
r.TextValue = word.ValueFrom;
}
DbContext.SaveChanges();
}
}
}
public IEnumerable <ResWord> GetListResWords(ResCurrent current, string search)
{
IQueryable <TempResData> exist = DbContext.ResData
.Where(r => r.Flag != 4)
.Where(r => r.ResourceType == "text")
.Where(r => r.CultureTitle == "Neutral")
.Join(DbContext.ResData.DefaultIfEmpty(), r => new { r.FileId, r.Title }, r => new { r.FileId, r.Title }, (r1, r2) => new { r1, r2 })
.Join(DbContext.ResFiles, r => r.r1.FileId, f => f.Id, (d, f) => new TempResData {
R1 = d.r1, R2 = d.r2, RF = f
})
.Where(r => r.R2.CultureTitle == current.Language.Title)
.Where(r => !r.R1.Title.StartsWith(@"del\_"))
.Where(r =>
!DbContext.ResData
.Where(d => d.FileId == r.R1.FileId)
.Where(d => d.Title == "del_" + r.R1.Title)
.Where(d => d.CultureTitle == r.R1.CultureTitle)
.Any()
)
.OrderBy(r => r.R1.Id);
if (current.Module != null && !string.IsNullOrEmpty(current.Module.Name))
{
exist = exist.Where(r => r.RF.ModuleName == current.Module.Name);
}
if (current.Project != null && !string.IsNullOrEmpty(current.Project.Name))
{
exist = exist.Where(r => r.RF.ProjectName == current.Project.Name);
}
if (current.Word != null && current.Word.ResFile != null && !string.IsNullOrEmpty(current.Word.ResFile.FileName))
{
exist = exist.Where(r => r.RF.ResName == current.Word.ResFile.FileName);
}
if (!string.IsNullOrEmpty(search))
{
exist = exist.Where(r => r.R1.TextValue == search);
}
return(exist.ToList().Select(r =>
{
var word = new ResWord
{
Title = r.R1.Title,
ResFile = new ResFile
{
FileID = r.R1.FileId
},
ValueFrom = r.R1.TextValue,
TextComment = r.R1.Description,
Flag = r.R1.Flag,
Link = r.R1.Link
};
word.ResFile.FileName = r.RF.ResName;
if (r.R2 != null)
{
word.Status = r.R2.Flag == 3 ? WordStatusEnum.Changed : WordStatusEnum.Translated;
word.ValueTo = r.R2.TextValue;
}
else
{
word.Status = WordStatusEnum.Untranslated;
}
return word;
}).OrderBy(r => r.ValueFrom));
}
public static void Upload(IOptionsMonitor <ILog> option, ResourceData resourceData, string fileName, Stream fileStream, string projectName, string moduleName)
{
var culture = GetCultureFromFileName(fileName);
string jsonString;
using (var reader = new StreamReader(fileStream))
{
jsonString = reader.ReadToEnd();
}
var jsonObj = new Dictionary <string, string>();
if (Path.GetExtension(fileName) == ".xml")
{
var doc = new XmlDocument();
doc.LoadXml(jsonString);
var list = doc.SelectNodes("//resources//string");
if (list != null)
{
try
{
var nodes = list.Cast <XmlNode>().ToList();
jsonObj = nodes.ToDictionary(r => r.Attributes["name"].Value, r => r.InnerText);
}
catch (Exception e)
{
option.CurrentValue.ErrorFormat("parse xml " + fileName, e);
}
}
}
else
{
jsonObj = JsonConvert.DeserializeObject <Dictionary <string, string> >(jsonString);
}
var fileID = resourceData.AddFile(fileName, projectName, moduleName);
const string resourceType = "text";
foreach (var key in jsonObj.Keys)
{
var word = new ResWord
{
Title = key,
ValueFrom = jsonObj[key],
ResFile = new ResFile {
FileID = fileID
}
};
if (culture != "Neutral")
{
var neutralKey = new ResWord
{
Title = key,
ValueFrom = jsonObj[key],
ResFile = new ResFile {
FileID = fileID
}
};
resourceData.GetValueByKey(neutralKey, "Neutral");
if (string.IsNullOrEmpty(neutralKey.ValueTo))
{
continue;
}
}
resourceData.AddResource(culture, resourceType, DateTime.UtcNow, word, true, "Console");
}
}
private static SqlInsert Insert(string table, string cultureTitle, ResWord word)
{
return(new SqlInsert(table, true)
.InColumns("title", "textvalue", "cultureTitle", "fileID")
.Values(word.Title, word.ValueFrom, cultureTitle, word.ResFile.FileID));
}
public void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin, bool updateIfExist = true)
{
var db = GetDb();
var resData = db.ExecuteScalar <string>(GetQuery(ResDataTable, cultureTitle, word));
var resReserve = db.ExecuteScalar <string>(GetQuery(ResReserveTable, cultureTitle, word));
//нет ключа
if (string.IsNullOrEmpty(resData))
{
//добавляем в основную таблицу
db.ExecuteNonQuery(Insert(ResDataTable, cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
//добавляем в резервную таблицу
if (isConsole)
{
db.ExecuteNonQuery(Insert(ResReserveTable, cultureTitle, word));
}
}
else
{
if (cultureTitle == "Neutral" && isConsole)
{
updateIfExist = db.ExecuteScalar <int>(new SqlQuery(ResDataTable)
.SelectCount()
.Where("fileID", word.ResFile.FileID)
.Where(!Exp.Eq("cultureTitle", cultureTitle))
.Where("title", word.Title)) == 0;
}
var isChangeResData = resData != word.ValueFrom;
var isChangeResReserve = resReserve != word.ValueFrom;
if (!updateIfExist)
{
return;
}
//при работе с консолью изменилось по сравнению с res_data и res_reserve, либо при работе с сайтом изменилось по сравнению с res_reserve
if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
{
// изменилась нейтральная культура - выставлен флаг у всех ключей из выбранного файла с выбранным title
if (cultureTitle == "Neutral")
{
var update = new SqlUpdate(ResDataTable)
.Set("flag", 3)
.Where("fileID", word.ResFile.FileID)
.Where("title", word.Title);
db.ExecuteNonQuery(update);
}
// изменилась не нейтральная культура
db.ExecuteNonQuery(Insert(ResDataTable, cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
if (isConsole)
{
db.ExecuteNonQuery(Update(ResReserveTable, cultureTitle, word));
}
}
else if (isChangeResData)
{
db.ExecuteNonQuery(Update(ResReserveTable, cultureTitle, word));
}
}
}