private void LinkTableSQL(string newTable, ref string tableIncluded, ref string tableSQL)
{
if (TableIncluded(newTable, tableIncluded))
{
return;
}
TableRelation relationUsed = null;
//第一次扫描确定有没有可以直接使用的TableRelation
foreach (TableRelation relation in _TableRelationList)
{
string tableAlias = relation.TableAlias.Trim().ToLower();
if (tableAlias == newTable.Trim().ToLower())
{
if (relationUsed == null)
{
bool canUse = true;
foreach (TableLink link in relation.TableLinkList)
{
if (_DetailedCoreTable.Trim().Length > 0 && link.ParentTable == "**" && _DetailedCoreTable.ToLower().IndexOf(link.ParentTableField.ToLower()) < 0)
{
canUse = false;
break;
}
}
if (canUse)
{
relationUsed = relation;
}
}
//如果所有的ParentTable中不包括**、并且都已经出现，则优先使用这个Relation
bool allParentTableIncluded = true;
foreach (TableLink link in relation.TableLinkList)
{
if (link.ParentTable == "**" || !TableIncluded(link.ParentTable, tableIncluded))
{
allParentTableIncluded = false;
break;
}
}
if (allParentTableIncluded)
{
relationUsed = relation;
break;
}
}
}
//使用第一个可用的TableRelation
if (relationUsed != null)
{
string table = relationUsed.Table.Trim().ToLower();
string tableAlias = relationUsed.TableAlias.Trim().ToLower();
for (int i = 0; i < relationUsed.TableLinkList.Count; i++)
{
string parentTableAlias = relationUsed.TableLinkList[i].ParentTableAlias.Trim().ToLower();
if (!TableIncluded(relationUsed.TableLinkList[i].ParentTableAlias, tableIncluded))
{
LinkTableSQL(parentTableAlias, ref tableIncluded, ref tableSQL);
}
if (i == 0)
{
tableSQL += " LEFT OUTER JOIN " + table + " " + tableAlias + " ";
}
if (i == 0)
{
tableSQL += "ON ";
}
else
{
tableSQL += "AND ";
}
tableSQL += GetPrefixAndField(tableAlias, relationUsed.TableLinkList[i].TableField) + " = " + GetPrefixAndField(parentTableAlias, relationUsed.TableLinkList[i].ParentTableField) + " ";
}
tableIncluded += tableAlias + ",";
return;
}
}
private void InitTableRelation()
{
_TableRelationList = new List <TableRelation>();
TableRelation relation = null;
//tblmaterial
relation = new TableRelation();
relation.Table = "tblmaterial";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("mcode", "**", "**", "itemcode"));
_TableRelationList.Add(relation);
//tblmesentitylist
relation = new TableRelation();
relation.Table = "tblmesentitylist";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("serial", "**", "**", "tblmesentitylist_serial"));
_TableRelationList.Add(relation);
//tbltimedimension
relation = new TableRelation();
relation.Table = "tbltimedimension";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("ddate", "**", "**", "shiftday"));
_TableRelationList.Add(relation);
//tblmo
relation = new TableRelation();
relation.Table = "tblmo";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("mocode", "**", "**", "mocode"));
_TableRelationList.Add(relation);
//tbllot
relation = new TableRelation();
relation.Table = "tbllot";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("lotno", "**", "**", "lotno"));
_TableRelationList.Add(relation);
//tblshift 1
relation = new TableRelation();
relation.Table = "tblshift";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("shiftcode", "**", "**", "shiftcode"));
_TableRelationList.Add(relation);
//tblshift 2
relation = new TableRelation();
relation.Table = "tblshift";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("shiftcode", "tblmesentitylist", "tblmesentitylist", "shiftcode"));
_TableRelationList.Add(relation);
//tbltp 1
relation = new TableRelation();
relation.Table = "tbltp";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("tpcode", "**", "**", "tpcode"));
_TableRelationList.Add(relation);
//tbltp 2
relation = new TableRelation();
relation.Table = "tbltp";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("tpcode", "tblmesentitylist", "tblmesentitylist", "tpcode"));
_TableRelationList.Add(relation);
//tblseg 1
relation = new TableRelation();
relation.Table = "tblseg";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("segcode", "**", "**", "segcode"));
_TableRelationList.Add(relation);
//tblseg 2
relation = new TableRelation();
relation.Table = "tblseg";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("segcode", "tblmesentitylist", "tblmesentitylist", "segcode"));
_TableRelationList.Add(relation);
//tblss 1
relation = new TableRelation();
relation.Table = "tblss";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("sscode", "**", "**", "sscode"));
_TableRelationList.Add(relation);
//tblss 2
relation = new TableRelation();
relation.Table = "tblss";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("sscode", "tblmesentitylist", "tblmesentitylist", "sscode"));
_TableRelationList.Add(relation);
//tblop 1
relation = new TableRelation();
relation.Table = "tblop";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("opcode", "**", "**", "opcode"));
_TableRelationList.Add(relation);
//tblop 2
relation = new TableRelation();
relation.Table = "tblop";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("opcode", "tblmesentitylist", "tblmesentitylist", "opcode"));
_TableRelationList.Add(relation);
//tblres 1
relation = new TableRelation();
relation.Table = "tblres";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("rescode", "**", "**", "rescode"));
_TableRelationList.Add(relation);
//tblres 2
relation = new TableRelation();
relation.Table = "tblres";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("rescode", "tblmesentitylist", "tblmesentitylist", "rescode"));
_TableRelationList.Add(relation);
//tblline2crew 1
relation = new TableRelation();
relation.Table = "tblline2crew";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("shiftdate", "**", "**", "shiftday"));
relation.TableLinkList.Add(new TableLink("sscode", "**", "**", "sscode"));
relation.TableLinkList.Add(new TableLink("shiftcode", "**", "**", "shiftcode"));
_TableRelationList.Add(relation);
//tblline2crew 2
relation = new TableRelation();
relation.Table = "tblline2crew";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("sscode", "tblmesentitylist", "tblmesentitylist", "sscode"));
relation.TableLinkList.Add(new TableLink("shiftcode", "tblmesentitylist", "tblmesentitylist", "shiftcode"));
relation.TableLinkList.Add(new TableLink("shiftdate", "**", "**", "shiftday"));
_TableRelationList.Add(relation);
//tblcrew 1
relation = new TableRelation();
relation.Table = "tblcrew";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("crewcode", "**", "**", "crewcode"));
_TableRelationList.Add(relation);
//tblcrew 2
relation = new TableRelation();
relation.Table = "tblcrew";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("crewcode", "tblline2crew", "tblline2crew", "crewcode"));
_TableRelationList.Add(relation);
//tblfactory 1
relation = new TableRelation();
relation.Table = "tblfactory";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("faccode", "**", "**", "faccode"));
_TableRelationList.Add(relation);
//tblfactory 2
relation = new TableRelation();
relation.Table = "tblfactory";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("faccode", "tblmesentitylist", "tblmesentitylist", "faccode"));
_TableRelationList.Add(relation);
//tblitemclass 1
relation = new TableRelation();
relation.Table = "tblitemclass";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("firstclass", "**", "**", "firstclass"));
_TableRelationList.Add(relation);
//tblitemclass 2
relation = new TableRelation();
relation.Table = "tblitemclass";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("itemgroup", "tblmaterial", "tblmaterial", "mgroup"));
_TableRelationList.Add(relation);
//tblsysparam
relation = new TableRelation();
relation.Table = "tblsysparam";
relation.TableAlias = "tblsysparam_momemo";
relation.TableLinkList.Add(new TableLink("paramcode", "tblmo", "tblmo", "momemo"));
relation.TableLinkList.Add(new TableLink("paramgroupcode", "tblmo", "tblmo", "'MO_PRODUCT_TYPE'"));
_TableRelationList.Add(relation);
//tbluser 2
relation = new TableRelation();
relation.Table = "tbluser";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("usercode", "**", "**", "INSPECTOR"));
_TableRelationList.Add(relation);
//tblvendor 1
relation = new TableRelation();
relation.Table = "tblvendor";
relation.TableAlias = relation.Table;
relation.TableLinkList.Add(new TableLink("vendorcode", "**", "**", "vendorcode"));
_TableRelationList.Add(relation);
}