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); }