/// <summary>
/// 拷贝的构造函数。
/// </summary>
/// <param name="joinItem">JoinItem对象</param>
public JoinItem(JoinItem joinItem)
{
if (joinItem == null) return;
this.ForeignTableName = joinItem.ForeignTableName;
this.JoinFilter = joinItem.JoinFilter;
this.JoinType = joinItem.JoinType;
}
/// <summary>
/// Creates a relationship between two schemas.
/// </summary>
/// <typeparam name="Joined"></typeparam>
/// <param name="join">The type of relationship. May it be INNER, OUTER, LEFT or RIGHT.</param>
/// <param name="schema">The schema to be related with.</param>
/// <param name="columnOn">The columns will be used for relating the two schemas provided.</param>
public void Relate <Joined>(Join join, Schema <Joined> schema, params Relation[] columnOn) where Joined : Entity, new()
{
var onString = new List <string>();
if (columnOn == null)
{
return;
}
foreach (var column in columnOn)
{
if (column == null)
{
continue;
}
onString.Add($"{column.Column1.Get} {Conditions<Joined>.GetCondition(column.Condition ?? Is.EqualTo)} {column.Column2.Get}");
}
var joinItem = new JoinItem()
{
Join = join,
TableName = schema.TableName,
OnString = string.Join(", ", onString),
EntityType = typeof(Joined)
};
this.Join.Add(joinItem);
this.Select.Joined.Add(joinItem);
}
/// <summary>
/// Join list of password-protected document of known format
/// </summary>
/// <param name="fileOne">first source file</param>
/// <param name="fileTwo">second source file</param>
public static void JoinDocumentsOfKnownFormat(string fileOne, string fileTwo)
{
//ExStart:JoinDocumentsOfKnownFormat
string sourceFile1 = CommonUtilities.fileOne + fileOne;
string sourceFile2 = CommonUtilities.fileTwo + fileTwo;
// Preparing.
string password = "******";
Stream openFile1 = new FileStream(sourceFile1, FileMode.Open);
Stream openFile2 = new FileStream(sourceFile2, FileMode.Open);
List <JoinItem> documentStreams = new List <JoinItem>();
JoinItem item1 = new JoinItem(openFile1, FileFormat.Docx, password);
documentStreams.Add(item1);
JoinItem item2 = new JoinItem(openFile2, FileFormat.Docx, password);
documentStreams.Add(item2);
// Main method.
DocumentResult result = new DocumentHandler().Join(documentStreams);
Stream documentStream = result.Stream;
var fileStream = File.Create(CommonUtilities.outputPath + "OutPut." + FileFormat.Docx);
documentStream.CopyTo(fileStream);
documentStream.Close();
//ExEnd:JoinDocumentsOfKnownFormat
}
//group by, join sample
//This is a sample
public List <DataExportVM> DataExport2(int userid)
{
var currentYear = DateTime.Now.Year;
var currentWeek = DateTime.Now.GetIso8601WeekOfYear();
var currentMonth = DateTime.Now.Month;
var currentQuarter = DateTime.Now.GetQuarter();
var model = (from l in _dbContext.Levels
join u in _dbContext.Users on l.ID equals u.LevelID
join item in _dbContext.KPILevels on l.ID equals item.LevelID
join d in _dbContext.Datas on item.KPILevelCode equals d.KPILevelCode into JoinItem
from joi in JoinItem.DefaultIfEmpty()
join k in _dbContext.KPIs on item.KPIID equals k.ID
where u.ID == userid && item.Checked == true
group new { u, l, item, joi, k } by new
{
u.Username,
Area = l.Name,
KPIName = k.Name,
item.KPILevelCode,
item.Checked,
item.WeeklyChecked,
item.MonthlyChecked,
item.QuarterlyChecked,
item.YearlyChecked,
} into g
select new
{
g.Key.Username,
Area = g.Key.Area,
KPIName = g.Key.KPIName,
g.Key.KPILevelCode,
g.Key.Checked,
g.Key.WeeklyChecked,
g.Key.MonthlyChecked,
g.Key.QuarterlyChecked,
g.Key.YearlyChecked,
PeriodValueW = g.Select(x => x.joi.Week).Max(),
PeriodValueM = g.Select(x => x.joi.Month).Max(),
PeriodValueQ = g.Select(x => x.joi.Quarter).Max(),
PeriodValueY = g.Select(x => x.joi.Year).Max(),
}).AsEnumerable()
.Select(x => new DataExportVM
{
Value = "0",
Year = currentYear,
KPILevelCode = x.KPILevelCode,
KPIName = x.KPIName,
Area = x.Area,
Remark = string.Empty,
PeriodValueW = x.PeriodValueW,
PeriodValueM = x.PeriodValueM,
PeriodValueQ = x.PeriodValueQ,
PeriodValueY = x.PeriodValueY,
});
return(model.ToList());
}
//****************************************
public IEnumerable <T> Enumerate()
{
JoinItem <T>?MyItem = Root;
do
{
yield return(MyItem.Item);
} while ((MyItem = MyItem.Next) != null);
}
internal void AddJoinFilter(JoinItem join, SQLParamCreater creater)
{
var filter = Filter(join, creater);
if (filter != null)
{
join.ON(filter);
}
}
/// <summary>
/// 添加到连接子句
/// </summary>
private void AppendJoin(string joinType, string sql)
{
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
sql = Helper.ResolveSql(sql, _dialect);
var item = new JoinItem(joinType, sql, raw: true);
AddItem(item);
}
public List <FromItem> ParseTableSources(TableSourcesContext node)
{
List <FromItem> fromItems = new List <FromItem>();
TableSourceContext[] tables = node.tableSource();
foreach (TableSourceContext table in tables)
{
if (table is TableSourceBaseContext tb)
{
TableSourceItemContext tsi = tb.tableSourceItem();
FromItem fromItem = new FromItem();
if (tsi is SubqueryTableItemContext subquery)
{
fromItem.SubSelectStatement = this.ParseSelectStatement(subquery.selectStatement());
UidContext uid = subquery.uid();
if (uid != null)
{
fromItem.Alias = new TokenInfo(uid)
{
Type = TokenType.Alias
};
}
}
else
{
TableName tableName = this.ParseTableName(tsi);
fromItem.TableName = tableName;
JoinPartContext[] joins = tb.joinPart();
if (joins != null && joins.Length > 0)
{
foreach (JoinPartContext join in joins)
{
JoinItem joinItem = this.ParseJoin(join);
fromItem.JoinItems.Add(joinItem);
}
}
}
fromItems.Add(fromItem);
}
}
return(fromItems);
}
public static void Run()
{
var configuration = new Configuration(Common.MyAppSid, Common.MyAppKey);
var apiInstance = new DocumentApi(configuration);
try
{
var item1 = new JoinItem
{
FileInfo = new FileInfo
{
FilePath = "WordProcessing/sample-10-pages.docx"
},
Pages = new List <int?> {
3, 6, 8
}
};
var item2 = new JoinItem
{
FileInfo = new FileInfo
{
FilePath = "WordProcessing/four-pages.docx"
},
StartPageNumber = 1,
EndPageNumber = 4,
RangeMode = JoinItem.RangeModeEnum.OddPages
};
var options = new JoinOptions
{
JoinItems = new List <JoinItem> {
item1, item2
},
OutputPath = "Output/joined-pages.docx"
};
var request = new JoinRequest(options);
var response = apiInstance.Join(request);
Console.WriteLine("Output file path: " + response.Path);
}
catch (Exception e)
{
Console.WriteLine("Exception while calling api: " + e.Message);
}
}
public void Test_1()
{
var item = new JoinItem("join", "b");
item.On("a.A", "b.B", Operator.Equal);
//复制一份
var copy = item.Clone();
Assert.Equal("join b On a.A=b.B", item.ToSql());
Assert.Equal("join b On a.A=b.B", copy.ToSql());
//修改副本
copy.On("a.C", "b.D", Operator.Equal);
Assert.Equal("join b On a.A=b.B", item.ToSql());
Assert.Equal("join b On a.A=b.B And a.C=b.D", copy.ToSql());
}
/// <summary>
/// get join condition
/// </summary>
/// <param name="sourceQuery">source query</param>
/// <param name="joinItem">join item</param>
/// <returns></returns>
string GetJoinCondition(IQuery sourceQuery, JoinItem joinItem, string sourceObjShortName, string targetObjShortName)
{
if (joinItem.JoinType == JoinType.CrossJoin)
{
return(string.Empty);
}
var joinFields = joinItem?.JoinFields.Where(r => !r.Key.IsNullOrEmpty() && !r.Value.IsNullOrEmpty());
var sourceEntityType = sourceQuery.EntityType;
var targetEntityType = joinItem.JoinQuery.EntityType;
bool useValueAsSource = false;
if (joinFields.IsNullOrEmpty())
{
joinFields = EntityManager.GetRelationFields(sourceEntityType, targetEntityType);
}
if (joinFields.IsNullOrEmpty())
{
useValueAsSource = true;
joinFields = EntityManager.GetRelationFields(targetEntityType, sourceEntityType);
}
if (joinFields.IsNullOrEmpty())
{
return(string.Empty);
}
List <string> joinList = new List <string>();
foreach (var joinField in joinFields)
{
if (joinField.Key.IsNullOrEmpty() || joinField.Value.IsNullOrEmpty())
{
continue;
}
var sourceField = DataManager.GetField(ServerType.MySQL, sourceEntityType, joinField.Key);
var targetField = DataManager.GetField(ServerType.MySQL, targetEntityType, joinField.Value);
joinList.Add(string.Format(" {0}.`{1}`{2}{3}.`{4}`",
sourceObjShortName,
useValueAsSource ? targetField.FieldName : sourceField.FieldName,
GetJoinOperator(joinItem.Operator),
targetObjShortName,
useValueAsSource ? sourceField.FieldName : targetField.FieldName
));
}
return(joinList.IsNullOrEmpty() ? string.Empty : " ON" + string.Join(" AND", joinList));
}
public JoinItem ParseJoin(JoinPartContext node)
{
JoinItem joinItem = new JoinItem();
if (node.children.Count > 0 && node.children[0] is TerminalNodeImpl terminalNode)
{
int type = terminalNode.Symbol.Type;
switch (type)
{
case MySqlParser.LEFT:
joinItem.Type = JoinType.LEFT;
break;
case MySqlParser.RIGHT:
joinItem.Type = JoinType.RIGHT;
break;
case MySqlParser.FULL:
joinItem.Type = JoinType.FULL;
break;
case MySqlParser.CROSS:
joinItem.Type = JoinType.CROSS;
break;
}
}
if (node is InnerJoinContext innerJoin)
{
joinItem.TableName = this.ParseTableName(innerJoin.tableSourceItem());
joinItem.Condition = this.ParseCondition(innerJoin.expression());
}
else if (node is NaturalJoinContext naturalJoin)
{
joinItem.TableName = this.ParseTableName(naturalJoin.tableSourceItem());
}
else if (node is OuterJoinContext outerJoin)
{
joinItem.TableName = this.ParseTableName(outerJoin.tableSourceItem());
joinItem.Condition = this.ParseCondition(outerJoin.expression());
}
return(joinItem);
}
public static void Run()
{
var configuration = new Configuration(Common.MyAppSid, Common.MyAppKey);
var apiInstance = new DocumentApi(configuration);
try
{
var item1 = new JoinItem
{
FileInfo = new FileInfo
{
FilePath = "WordProcessing/four-pages.docx"
}
};
var item2 = new JoinItem
{
FileInfo = new FileInfo
{
FilePath = "WordProcessing/one-page.docx"
}
};
var options = new JoinOptions
{
JoinItems = new List <JoinItem> {
item1, item2
},
OutputPath = "Output/joined.docx"
};
var request = new JoinRequest(options);
var response = apiInstance.Join(request);
Console.WriteLine("Output file path: " + response.Path);
}
catch (Exception e)
{
Console.WriteLine("Exception while calling api: " + e.Message);
}
}
//****************************************
public JoinRoot(T item)
{
Root = new JoinItem <T>(item);
}
private void FillSelectScript(string name, int level, SQLScriptCollection collection, WhereItem where = null, Stack <JoinItem <TableEntity> > stack = null)
{
if (collection == null)
{
throw new ArgumentNullException("collection");
}
var script = collection.NewSQLScript(level == 0 ? 0.ToString() : string.Format("{0}-{1}", name.Trim(), level), SQLTYPE.SELECT);
var alias = collection.NewTableAlias();
var from = new FromItem <TableEntity>(collection.accessor, Entity, alias);
var select = new SelectItem(collection.accessor, from);
script.AddItems(select, from);
//Build Jions
JoinItem <TableEntity>[] join_items = null;
if (stack != null)
{
if (HasForeigns)
{
join_items = new JoinItem <TableEntity> [stack.Count];
stack.CopyTo(join_items, 0);
}
TableItem table1 = from;
JoinItem <TableEntity> table2 = null;
while (stack.Count > 0)
{
table2 = stack.Pop().Clone() as JoinItem <TableEntity>;
table2.ON(table1, table2.Keys.ToArray());
table2.Entity.AddJoinFilter(table2, collection.SQLParamCreater);
script.AddItems(table2);
table1 = table2;
}
}
//Build Where
if (level == 0)
{
var where1 = new WhereItem(collection.accessor, from);
Columns.Where(c => c.IsPK).ToList().ForEach(col =>
{
var param = collection.NewParameter(col.Value);
where1.And(where1.ColumnEquals(col.Name, param));
});
where = where == null ? where1 : where + where1;
}
if (where != null)
{
script.AddItem(where.Clone() as WhereItem);
}
var where0 = new WhereItem(collection.accessor, from);
Entity.AddWhereFilter(where0, collection.SQLParamCreater);
script.AddItem(where0);
foreach (var foreign in this.Foreigns)
{
var nstack = join_items != null ? new Stack <JoinItem <TableEntity> >(join_items) : new Stack <JoinItem <TableEntity> >();
nstack.Push(new JoinItem <TableEntity>(collection.accessor, Entity, alias, foreign.Keys.Select(key => key.ToKeyValuePair()).ToArray()));
foreign.EntityInst.Schema.FillSelectScript(foreign.Name, level + 1, collection, where.Clone() as WhereItem, nstack);
}
}
/// <summary>
/// 添加连接项
/// </summary>
private void AddItem(JoinItem item)
{
item.SetDependency(_helper);
_params.Add(item);
}
public override StatementScriptBuilder Build(Statement statement, bool appendSeparator = true)
{
base.Build(statement, appendSeparator);
if (statement is SelectStatement select)
{
this.BuildSelectStatement(select, appendSeparator);
}
else if (statement is UnionStatement union)
{
this.AppendLine(this.GetUnionTypeName(union.Type));
this.Build(union.SelectStatement);
}
else if (statement is InsertStatement insert)
{
this.AppendLine($"INSERT INTO {insert.TableName}");
if (insert.Columns.Count > 0)
{
this.AppendLine($"({ string.Join(",", insert.Columns.Select(item => item.ToString()))})");
}
if (insert.SelectStatements != null && insert.SelectStatements.Count > 0)
{
this.AppendChildStatements(insert.SelectStatements, true);
}
else
{
this.AppendLine($"VALUES({string.Join(",", insert.Values.Select(item => item))});");
}
}
else if (statement is UpdateStatement update)
{
this.AppendLine($"UPDATE");
List <TokenInfo> tableNames = new List <TokenInfo>();
if (update.FromItems != null)
{
int i = 0;
foreach (FromItem fromItem in update.FromItems)
{
if (i == 0 && fromItem.TableName != null)
{
tableNames.Add(fromItem.TableName);
}
List <JoinItem> usedJoinItems = new List <JoinItem>();
foreach (NameValueItem nameValue in update.SetItems)
{
string[] ids = nameValue.Value.Symbol.Split('.').Select(item => item.Trim()).ToArray();
string tableName = ids[0];
JoinItem joinItem = fromItem.JoinItems.FirstOrDefault(item => item.TableName.Name?.Symbol?.ToUpper().Trim('"') == tableName.ToUpper() ||
item.TableName.Alias?.Symbol?.ToUpper()?.Trim('"') == tableName.ToUpper());
if (joinItem != null)
{
usedJoinItems.Add(joinItem);
string condition = joinItem.Condition == null ? "" : $" WHERE {joinItem.Condition}";
nameValue.Value = new TokenInfo($"(SELECT {nameValue.Name} FROM {joinItem.TableName}{condition})");
}
}
var otherJoinItems = fromItem.JoinItems.Where(item => !usedJoinItems.Contains(item));
if (otherJoinItems != null && otherJoinItems.Count() > 0)
{
foreach (var otherJoinItem in otherJoinItems)
{
//TODO
}
}
i++;
}
}
if (tableNames.Count == 0 && update.TableNames.Count > 0)
{
tableNames.AddRange(update.TableNames);
}
this.Append($" {string.Join(",", tableNames)}", false);
this.AppendLine("SET");
this.AppendLine(string.Join("," + Environment.NewLine + indent, update.SetItems.Select(item => $"{item.Name}={item.Value}")));
if (update.Condition != null && update.Condition.Symbol != null)
{
this.AppendLine($"WHERE {update.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeleteStatement delete)
{
this.AppendLine($"DELETE {delete.TableName}");
if (delete.Condition != null)
{
this.AppendLine($"WHERE {delete.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeclareStatement declare)
{
if (declare.Type == DeclareType.Variable)
{
string defaultValue = (declare.DefaultValue == null ? "" : $" :={declare.DefaultValue}");
this.AppendLine($"DECLARE {declare.Name} {declare.DataType}{defaultValue};");
}
else if (declare.Type == DeclareType.Table)
{
}
}
else if (statement is IfStatement @if)
{
foreach (IfStatementItem item in @if.Items)
{
if (item.Type == IfStatementType.IF || item.Type == IfStatementType.ELSEIF)
{
this.AppendLine($"{item.Type} {item.Condition} THEN");
}
else
{
this.AppendLine($"{item.Type}");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(item.Statements, true);
this.AppendLine("END;");
}
this.AppendLine("END IF;");
}
else if (statement is CaseStatement @case)
{
this.AppendLine($"CASE {@case.VariableName}");
foreach (IfStatementItem item in @case.Items)
{
if (item.Type != IfStatementType.ELSE)
{
this.AppendLine($"WHEN {item.Condition} THEN");
}
else
{
this.AppendLine("ELSE");
}
this.AppendLine("BEGIN");
this.AppendChildStatements(item.Statements, true);
this.AppendLine("END;");
}
this.AppendLine("END CASE;");
}
else if (statement is SetStatement set)
{
if (set.Key != null && set.Value != null)
{
this.AppendLine($"{set.Key } := {set.Value };");
}
}
else if (statement is LoopStatement loop)
{
if (loop.Type == LoopType.LOOP)
{
this.AppendLine("LOOP");
}
else
{
this.AppendLine($"{loop.Type.ToString()} {loop.Condition} LOOP");
}
this.AppendChildStatements(loop.Statements, true);
this.AppendLine("END LOOP;");
}
else if (statement is LoopExitStatement loopExit)
{
this.AppendLine($"EXIT WHEN {loopExit.Condition};");
}
else if (statement is WhileStatement @while)
{
LoopStatement loopStatement = @while as LoopStatement;
this.AppendLine($"WHILE {@while.Condition} LOOP");
this.AppendChildStatements(@while.Statements, true);
this.AppendLine("END LOOP;");
}
else if (statement is ReturnStatement @return)
{
this.AppendLine($"RETURN {@return.Value};");
}
else if (statement is PrintStatement print)
{
this.AppendLine($"DBMS_OUTPUT.PUT_LINE({print.Content.Symbol?.ToString()?.Replace("+", "||")});");
}
else if (statement is CallStatement execute)
{
this.AppendLine($"{execute.Name}({string.Join(",", execute.Arguments.Select(item => item.Symbol?.Split('=')?.LastOrDefault()))});");
}
else if (statement is TransactionStatement transaction)
{
TransactionCommandType commandType = transaction.CommandType;
switch (commandType)
{
case TransactionCommandType.COMMIT:
this.AppendLine("COMMIT;");
break;
case TransactionCommandType.ROLLBACK:
this.AppendLine("ROLLBACK;");
break;
}
}
else if (statement is LeaveStatement leave)
{
this.AppendLine("RETURN;");
}
else if (statement is TryCatchStatement tryCatch)
{
this.AppendLine("EXCEPTION");
this.AppendLine("BEGIN");
this.AppendChildStatements(tryCatch.CatchStatements, true);
this.AppendLine("END;");
this.AppendChildStatements(tryCatch.TryStatements, true);
}
else if (statement is ExceptionStatement exception)
{
this.AppendLine("EXCEPTION");
foreach (ExceptionItem exceptionItem in exception.Items)
{
this.AppendLine($"WHEN {exceptionItem.Name} THEN");
this.AppendLine("BEGIN");
this.AppendChildStatements(exceptionItem.Statements, true);
this.AppendLine("END;");
}
}
else if (statement is DeclareCursorStatement declareCursor)
{
this.AppendLine($"DECLARE CURSOR {declareCursor.CursorName} IS");
this.Build(declareCursor.SelectStatement);
}
else if (statement is OpenCursorStatement openCursor)
{
this.AppendLine($"OPEN {openCursor.CursorName};");
}
else if (statement is FetchCursorStatement fetchCursor)
{
if (fetchCursor.Variables.Count > 0)
{
this.AppendLine($"FETCH {fetchCursor.CursorName} INTO {string.Join(",", fetchCursor.Variables)};");
}
}
else if (statement is CloseCursorStatement closeCursor)
{
this.AppendLine($"CLOSE {closeCursor.CursorName};");
}
else if (statement is TruncateStatement truncate)
{
this.AppendLine($"TRUNCATE TABLE {truncate.TableName};");
}
return(this);
}
//****************************************
public JoinItem(T item)
{
Item = item;
Next = null;
}
public JoinItem(T item, JoinItem <T> next)
{
Item = item;
Next = next;
}
