public Bussiness(string nameTable, SqlConnection connection, Setting setting)
{
Connection = connection;
sTable = nameTable;
Setting = setting;
LstInfoTable = new SqlDatabaseContext(connection).GetInfoTable(nameTable);
NameTable = setting.GetNameTable(nameTable);
Table = new TableObject(sTable, connection);
}
private string GetAll()
{
string col = "";
string r = "";
string where = "";
string join = "";
//columns select
foreach (var item in Table.lstColumns)
{
string s = string.IsNullOrWhiteSpace(col) ? "" : ",";
col += $@"
{s}[{NameTable}].[{item.Name}]";
}
//columns join
foreach (var item in Table.lstFK)
{
var tblJoin = new TableObject(item.NameTableJoin, Connection);
string sTbl = Setting.GetNameTable(tblJoin.Name) + "Join";
foreach (var co in tblJoin.lstColumns)
{
if (co.IsPK)
{
continue;
}
col += $@"
,[{sTbl}].[{co.Name}] as [{co.Name}_{sTbl}]";
}
join += $@"
join [{tblJoin.Name}] as [{sTbl}] on [{NameTable}].[{item.Name}] = [{sTbl}].[{tblJoin.lstColumns.First(q => q.IsPK).Name}]";
}
//where
var cDelete = LstInfoTable.FirstOrDefault(q => q.Type.ToLower().Equals("bit") && q.Name.ToLower().Contains("delete"));
if (cDelete != null)
{
where += $@"
WHERE [{NameTable}].[{cDelete.Name}] <> 1 ";
}
r = $@"
;CREATE PROC {GetName(eMethod.GetAll)[0]}
AS BEGIN
SELECT {col} FROM [{sTable}] as [{NameTable}]
{join}
{where}
END;
";
Map.Add(GetName(eMethod.GetAll)[0], r);
return(r);
}
private string Get_GetAll()
{
string passValue = "";
foreach (var item in LstInfoTable)
{
string checkBool = item.GetTypeCs() == typeof(bool).ToString() ? "== true" : "";
passValue += $@"
obj.{item.Name.Replace(' ', '_')} = item.{item.Name.Replace(' ', '_')} {checkBool} ;";
}
foreach (var itemFK in Table.lstFK)
{
var tblJoin = new TableObject(itemFK.NameTableJoin, Connection);
string sDto = Setting.GetClassDto(tblJoin.Name);
string stableJoin = itemFK.IsPK ? "" : $"_{Setting.GetNameTable(tblJoin.Name)}Join";
string passValueJoin = "";
for (int i = 0; i < tblJoin.lstColumns.Count; i++)
{
var co = tblJoin.lstColumns[i];
string cm = i == tblJoin.lstColumns.Count - 1 ? "" : ",";
string checkBool = co.GetTypeCs() == typeof(bool).ToString() ? "== true" : "";
string checkFK = stableJoin;
string checkNullable = "";
string nameEntty = co.Name.Replace(' ', '_');
if (co.IsPK)
{
checkFK = "";
checkNullable = $"({co.GetTypeCs()})";
nameEntty = itemFK.Name.Replace(' ', '_');
}
passValueJoin += $@"
{co.Name.Replace(' ', '_')} = {checkNullable}item.{nameEntty}{checkFK} {checkBool} {cm}";
}
passValue += $@"
obj.{sDto}Join = new {sDto}()
{'{'}
{passValueJoin}
{'}'};
";
}
return($@"
public List<{cDto}> {GetNameMethod(eMethod.GetAll)}()
{'{'}
var list = new {dbEntity}().{proc.GetName(eMethod.GetAll)[0]}();
List<{cDto}> lst = new List<{cDto}>();
foreach (var item in list)
{'{'}
var obj = new {cDto}();
{passValue}
lst.Add(obj);
{'}'}
return lst;
{'}'}
");
}
// private string GetDeleteBy()
// {
// var lsKey = LstInfoTable.Where(q => q.isKey).ToList();
// if (lsKey.Count < 2) return "";
// var lsName = GetName(eMethod.DeleteBy);
// string r = "";
// for (int i = 0; i < lsKey.Count; i++)
// {
// string len = string.IsNullOrWhiteSpace(lsKey[i].Length) ? "" : $"({lsKey[i].Length})";
// string param = $"@{lsKey[i].Name} {lsKey[i].Type}{len}";
// string rs = $@"
//;CREATE PROC {lsName[i]}
//{param}
//AS BEGIN
// DELETE {Table} WHERE {lsKey[i].Name} = @{lsKey[i].Name}
//END;
//";
// Map.Add(GetName(eMethod.DeleteBy)[0], rs);
// r += rs;
// }
// return r;
// }
private string GetBy()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1)
{
return("");
}
var lsName = GetName(eMethod.GetBy);
string r = "";
for (int i = 0; i < lsKey.Count; i++)
{
string len = string.IsNullOrWhiteSpace(lsKey[i].Length) ? "" : $"({lsKey[i].Length})";
string param = $"@{lsKey[i].Name.Replace(' ', '_')} {lsKey[i].Type}{len}";
string col = "";
string join = "";
string where = $" WHERE [{NameTable}].[{lsKey[i].Name}] = @{lsKey[i].Name.Replace(' ', '_')} ";
//columns select
foreach (var item in Table.lstColumns)
{
if (item.Type.Equals("bit") && item.Name.ToLower().Contains("delete"))
{
where += $" AND [{NameTable}].[{item.Name}] <> 1 ";
}
string s = string.IsNullOrWhiteSpace(col) ? "" : ",";
if (item.Name.Any(c => c == ' '))
{
col += $@"
{s}[{NameTable}].[{item.Name}] as [{item.Name.Replace(' ', '_')}]";
}
else
{
col += $@"
{s}[{NameTable}].[{item.Name}]";
}
}
//column join
foreach (var item in Table.lstFK)
{
var tblJoin = new TableObject(item.NameTableJoin, Connection);
string sTbl = Setting.GetNameTable(tblJoin.Name) + "Join";
foreach (var co in tblJoin.lstColumns)
{
if (co.IsPK)
{
continue;
}
col += $@"
,[{sTbl}].[{co.Name}] as [{co.Name.Replace(' ', '_')}_{sTbl}]";
}
join += $@"
join [{tblJoin.Name}] as [{sTbl}] on [{NameTable}].[{item.Name}] = [{sTbl}].[{tblJoin.lstColumns.First(q => q.IsPK).Name}]";
}
string rs = $@"
;CREATE PROC {lsName[i]}
{param}
AS BEGIN
SELECT
{col}
FROM [{sTable}] as [{NameTable}]
{join}
{where}
END;
";
Map.Add(GetName(eMethod.GetBy)[i], rs);
r += rs;
}
return(r);
}
private string Get_GetBy()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1)
{
return(string.Empty);
}
string result = "";
for (int i = 0; i < lsKey.Count; i++)
{
var itemKey = lsKey[i];
string setValue = "";
foreach (var v in LstInfoTable)
{
string checkBool = v.GetTypeCs() == typeof(bool).ToString() ? "== true" : "";
setValue += $@"
obj.{v.Name.Replace(' ', '_')} = item.{v.Name.Replace(' ', '_')} {checkBool} ;";
}
foreach (var fk in Table.lstFK)
{
var tblJoin = new TableObject(fk.NameTableJoin, Connection);
string sDto = Setting.GetClassDto(tblJoin.Name);
string stableJoin = $"_{Setting.GetNameTable(tblJoin.Name)}Join";
string passValueJoin = "";
for (int j = 0; j < tblJoin.lstColumns.Count; j++)
{
var co = tblJoin.lstColumns[j];
string cm = j == tblJoin.lstColumns.Count - 1 ? "" : ",";
string checkBool = co.GetTypeCs() == typeof(bool).ToString() ? "== true" : "";
string checkFK = stableJoin;
string checkNullable = "";
string nameEntty = co.Name.Replace(' ', '_');
if (co.IsPK)
{
checkFK = "";
checkNullable = $"({co.GetTypeCs()})";
nameEntty = fk.Name.Replace(' ', '_');
}
passValueJoin += $@"
{co.Name.Replace(' ', '_')} = {checkNullable} item.{nameEntty}{checkFK} {checkBool} {cm}";
}
setValue += $@"
obj.{sDto}Join = new {sDto}()
{'{'}
{passValueJoin}
{'}'};
";
}
result += $@"
public {cDto} {GetNameMethod(eMethod.GetBy)}{itemKey.Name}({itemKey.GetTypeCs()} {itemKey.Name})
{'{'}
var list = new {dbEntity}().{proc.GetName(eMethod.GetBy)[i]}({itemKey.Name});
foreach (var item in list)
{'{'}
var obj = new {cDto}();
{setValue}
return obj;
{'}'}
return null;
{'}'}
";
}
return(result);
}
