//TODO list--------
public static void GenerateUserDefinTableScript()
{
List <int> userDefineEntityIds = null;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
int userDefineEntityType = (int)EmEntityType.UserDefineTable;
string qeuryUserDefineEntity = @" SELECT EntityID FROM pdmentity WHERE EntityType = " + userDefineEntityType;
DataTable resultTabel = DataAcessHelper.GetDataTableQueryResult(conn, qeuryUserDefineEntity);
userDefineEntityIds = resultTabel.GetDistinctOneColumnValueIds("EntityID").ToList();
}
// List<PdmEntityClrUserDefineDto> listPdmEntityClrDto = null;
List <PdmClrEntitySimpleStructureDto> listPdmEntityClrDto = PLMSEntityClrBL.GetEntityAndColumnStrcutureInfoList(userDefineEntityIds);
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
string aEntiValueScript = string.Empty;
foreach (PdmClrEntitySimpleStructureDto aEntityDTO in listPdmEntityClrDto)
{
// int entityId = aEntityDTO.EntityId;
// string entityCode = ;
aEntityDTO.EntityCode = DataTableUtility.FilterSQLDBInvalidChar(aEntityDTO.EntityCode);
GenetateEntityDTOQuery(aEntityDTO, conn);
string deleteOldEntityTable = @"delete [pdmDWTabGridScriptSetting] where EntityID=" + aEntityDTO.EntityId;
// string sqlSelect = aEntityDTO.SQLSelect;
string IntoUseDefineTablName = " insert into " + aEntityDTO.DWUserDefineTableName + aEntityDTO.SQLSelect;
// save to dbms
string deleteAndInsert = deleteOldEntityTable + System.Environment.NewLine + @" insert into " + PLMConstantString.PLM_DW_TabGridScripContainerTable + " ( EntityID,EntityName,InserIntoSQLScript,DWTableName,RootLevelSelectSQLScript) values (@EntityID,@EntityName,@queryResult,@DWTableName,@SQLRootLevelSelect)";
SqlCommand cmd = new SqlCommand(deleteAndInsert, conn);
cmd.Parameters.Add(new SqlParameter("@EntityID", aEntityDTO.EntityId));
cmd.Parameters.Add(new SqlParameter("@EntityName", DataTableUtility.FilterSQLDBInvalidChar(aEntityDTO.EntityCode)));
cmd.Parameters.Add(new SqlParameter("@queryResult", IntoUseDefineTablName));
cmd.Parameters.Add(new SqlParameter("@DWTableName", aEntityDTO.DWUserDefineTableName));
cmd.Parameters.Add(new SqlParameter("@SQLRootLevelSelect", aEntityDTO.SQLSelect));
CLROutput.OutputDebug("aEntityDTO.entityCode" + aEntityDTO.EntityCode);
CLROutput.OutputDebug("aEntityDTO.SQLSelect" + aEntityDTO.SQLSelect);
cmd.ExecuteNonQuery();
} // end of for eahc entity
}
}
internal static List <BlockSubitemClrUserDefineDto> RetriveBlockSubitemFullPathNameAndControlType(SqlConnection conn, string qeuryTabBlockSubitem)
{
List <BlockSubitemClrUserDefineDto> aDictSubitemID_Name = new List <BlockSubitemClrUserDefineDto>();
SqlCommand cmd = new SqlCommand(qeuryTabBlockSubitem, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
System.Data.DataTable resultTabel = new DataTable();
adapter.Fill(resultTabel);
foreach (DataRow aRow in resultTabel.Rows)
{
int subItemID = (int)aRow["SubItemID"];
string aStringSubItemId = subItemID.ToString();
string subItemName = DataTableUtility.FilterSQLDBInvalidChar(aRow["SubItemName"].ToString()) + "_" + aStringSubItemId;
//dbo.pdmBlockSubItem.EntityID dbo.pdmEntity.EntityCode, dbo.pdmEntity.SysTableName
int?entityID = aRow["EntityID"] as int?;
if (entityID.HasValue)
{
string entityCode = DataTableUtility.FilterSQLDBInvalidChar(aRow["EntityCode"].ToString());
string systemTable = aRow["SysTableName"].ToString();
PdmEntityBlClrDto aEntityBlClrDto = PdmCacheManager.DictPdmEntityBlEntity[entityID.Value];
if (aEntityBlClrDto.EntityType == (int)EmEntityType.UserDefineTable)
{
subItemName += "_FK_" + PLMConstantString.PLM_DW_UserDefineTablePrefix + entityCode + "_" + entityID;
}
else
{
subItemName += "_FK_" + systemTable;
}
}
// need to
int controlType = int.Parse(aRow["ControlType"].ToString());
BlockSubitemClrUserDefineDto aDto = new BlockSubitemClrUserDefineDto();
aDto.SubItemID = subItemID;
aDto.SubItemName = subItemName;
aDto.EntityID = entityID;
aDto.ControlType = controlType;
//!!!!!!!!!!!!!!!!!!!!!!!
aDictSubitemID_Name.Add(aDto);
}
return(aDictSubitemID_Name);
}
public static List <GridColumnClrUserDefineDto> GetGridColumnIDAndName(int gridID)
{
List <GridColumnClrUserDefineDto> aGridColumns = new List <GridColumnClrUserDefineDto>();
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
// call udf function RetriveProductTabCopyRootTabID
string qeuryGridColumn = string.Format(@"SELECT PdmGridMetaColumn.GridColumnID, PdmGridMetaColumn.ColumnName, PdmGridMetaColumn.ColumnTypeID , pdmEntity.EntityCode, pdmEntity.EntityID, pdmEntity.SysTableName
FROM PdmGridMetaColumn LEFT OUTER JOIN pdmEntity ON PdmGridMetaColumn.EntityID = pdmEntity.EntityID WHERE GridID={0} ", gridID);
SqlCommand cmd = new SqlCommand(qeuryGridColumn, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
System.Data.DataTable resultTabel = new DataTable();
adapter.Fill(resultTabel);
foreach (DataRow aRow in resultTabel.Rows)
{
int aColumnID = (int)aRow["GridColumnID"];
string colName = DataTableUtility.FilterSQLDBInvalidChar(aRow["ColumnName"].ToString() + "_" + aColumnID);
int? entityID = aRow["EntityID"] as int?;
string entityCode = DataTableUtility.FilterSQLDBInvalidChar(aRow["EntityCode"].ToString());
string systemTable = aRow["SysTableName"].ToString();
if (entityID.HasValue)
{
PdmEntityBlClrDto aEntityBlClrDto = PdmCacheManager.DictPdmEntityBlEntity[entityID.Value];
if (aEntityBlClrDto.EntityType == (int)EmEntityType.UserDefineTable)
{
colName += "_FK_" + PLMConstantString.PLM_DW_UserDefineTablePrefix + entityCode + "_" + entityID;
}
else
{
colName += "_FK_" + systemTable;
}
}
int controlType = int.Parse(aRow["ColumnTypeID"].ToString());
GridColumnClrUserDefineDto aDto = new GridColumnClrUserDefineDto();
aDto.GridColumnID = aColumnID;
aDto.ColumnName = colName;
aDto.EntityID = entityID;
aDto.ColumnTypeId = controlType;
aGridColumns.Add(aDto);
}
}
return(aGridColumns);
}
internal static string GetDWMainTabTableName(int rootabID)
{
string aRootTabName = string.Empty;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
string qeuryTabBlockSubitem = " SELECT TabName FROM pdmtab WHERE tabid = " + rootabID;
SqlCommand cmd = new SqlCommand(qeuryTabBlockSubitem, conn);
aRootTabName = cmd.ExecuteScalar().ToString();
}
// remove Space, ( )
aRootTabName = DataTableUtility.FilterSQLDBInvalidChar(aRootTabName);
return(string.Format("{0}" + aRootTabName + "_" + rootabID + "", PLMConstantString.PLM_DW_TabPreifxTableName));
}
internal static string GetDWGridIntoTalbeName(int gridID)
{
string aRootGridName = string.Empty;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
string qeuryTabBlockSubitem = " SELECT GridName FROM pdmGrid WHERE GridID = " + gridID;
SqlCommand cmd = new SqlCommand(qeuryTabBlockSubitem, conn);
aRootGridName = cmd.ExecuteScalar().ToString();
}
// remove Space, ( )
aRootGridName = DataTableUtility.FilterSQLDBInvalidChar(aRootGridName); //.Replace(' ', '_').Replace('(', '_').Replace(')', '_').Replace('-', '_');
return(string.Format(" {0}" + aRootGridName + "_" + gridID + " ", PLMConstantString.PLM_DW_GridPreifxTableName));
// return IntoDWTableName;
}
//TODO
public static void GenetateEntityDTOQuery(PdmClrEntitySimpleStructureDto aEntityDTO, SqlConnection conn)
{
List <PdmEntityColumnClrUserDefineDto> listColumnDto = aEntityDTO.Columns;
if (listColumnDto.Count == 0)
{
aEntityDTO.SQLSelect = " invalid column (column no exist !)";
return;
}
StringBuilder aUserDefineTableQuery = new StringBuilder();
aUserDefineTableQuery.Append(" SELECT RowID as ValueID , SortOrder ,");
string userDefineColumnIdInClause = string.Empty;
//WHERE UserDefineEntityColumnID in (4583,4584)
foreach (var columnClrDto in listColumnDto)
{
// columnClrDto.ColumnName = DataTableUtility.FilterSQLDBInvalidChar(columnClrDto.ColumnName + "_" + columnClrDto.UserDefineEntityColumnID);
columnClrDto.ColumnName = DataTableUtility.FilterSQLDBInvalidChar(columnClrDto.ColumnName);
string aUserDefineEntityColumnID = columnClrDto.UserDefineEntityColumnID.ToString();
userDefineColumnIdInClause += aUserDefineEntityColumnID + ",";
string aCaseQuery = string.Empty;
if (columnClrDto.UicontrolType == (int)EmControlType.DDL || columnClrDto.UicontrolType == (int)EmControlType.Image)
{
aCaseQuery = string.Format(" {0} (dbo.Concatenate ( case when UserDefineEntityColumnID={1} then ValueText end )) as '{2}' ,", PLMConstantString.ConvertValueTextToInt, aUserDefineEntityColumnID, columnClrDto.ColumnName);
}
else if (columnClrDto.UicontrolType == (int)EmControlType.Date)
{
aCaseQuery = string.Format(" {0} (dbo.Concatenate ( case when UserDefineEntityColumnID={1} then ValueText end )) as '{2}' ,", PLMConstantString.ConvertValueTextToDateTime, aUserDefineEntityColumnID, columnClrDto.ColumnName);
}
else if (
columnClrDto.UicontrolType == (int)EmControlType.Numeric
)
{
aCaseQuery = string.Format(" {0} ( dbo.Concatenate ( case when UserDefineEntityColumnID={1} then ValueText end )) as '{2}' ,", PLMConstantString.ConvertValueTextToDecimal, aUserDefineEntityColumnID, columnClrDto.ColumnName);
}
else
{
aCaseQuery = string.Format(" dbo.Concatenate ( case when UserDefineEntityColumnID={0} then ValueText end ) as '{1}' ,", aUserDefineEntityColumnID, columnClrDto.ColumnName);
}
aUserDefineTableQuery.Append(aCaseQuery);
}
aUserDefineTableQuery.Remove(aUserDefineTableQuery.Length - 1, 1);
if (userDefineColumnIdInClause != string.Empty)
{
userDefineColumnIdInClause = userDefineColumnIdInClause.Substring(0, userDefineColumnIdInClause.Length - 1);
userDefineColumnIdInClause = " WHERE UserDefineEntityColumnID in ( " + userDefineColumnIdInClause + " ) " + " and EntityID=" + aEntityDTO.EntityId;
aUserDefineTableQuery.Append(@" FROM " + PLMSEntityClrBL.PdmUserDefineEntityRowCellValueView + userDefineColumnIdInClause + " group by RowID, SortOrder ");
aEntityDTO.SQLSelect = aUserDefineTableQuery.ToString();
}
else
{
aEntityDTO.SQLSelect = "invalid column ";
}
// return aEntityDTO;
}
