private static List <string> GetExistingExDatabaseTableNameAndStructure(List <string> exChangeDbTableList, Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto)
{
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
string QueryExchangeTable = string.Format(@" select distinct Left( sysobj .Name , 50 ) AS [ExChangeTableName] from sysObjects as sysobj
inner join dbo.sysColumns as sysColumn on sysColumn.ID = sysobj .ID
where sysobj .name like '{0}%' and sysColumn.Name in ( '{1}', '{2}' ,'{3}' ,'{4}' ,'{5}')",
PLMConstantString.EX_PLM_Import_Prefix,
PLMConstantString.ExchangeRowDataERPFlagColumn,
PLMConstantString.ExchangeRowDataPLMFlagColumn,
PLMConstantString.ExchangeRowDataERPExportDateTimeColumn,
PLMConstantString.ExchangeRowDataPLMImportDateTimeColumn,
PLMConstantString.ExchangeRowDataPLMPrimayKeyColumn
);
CLROutput.Output("QueryExchangeTable=" + QueryExchangeTable);
exChangeDbTableList = DataAcessHelper.GetDataTableQueryResult(conn, QueryExchangeTable).AsEnumerable().Select(o => (o["ExChangeTableName"] as string).Trim().ToLowerInvariant()).ToList();
foreach (string exchangeTable in exChangeDbTableList)
{
List <PdmEntityColumnClrDto> exEntituyColumn = PLMSEntityClrBL.GetExchangeDatabaseTableColumnDto(conn, exchangeTable);
dictExchangeTableColumnDto.Add(exchangeTable, exEntituyColumn);
}
}
return(exChangeDbTableList);
}
public static void GetSpecQcSize(int referenceId, int mainQcTabId)
{
// Initianize context FROM PLM App,
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
//SqlContext.
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
// Get SubitemID
//int baseSizeIndexFromZero = 0;
//int totalSizeNumber = 0;
DataTable returnDataTble = GetOneReferenceSpecQcSelectedSizeTable(referenceId, conn, mainQcTabId);
CLROutput.SendDataTable(returnDataTble);
//CLROutput.OutputDebug("baseSizeIndex=" + baseSizeIndexFromZero);
//CLROutput.OutputDebug("totalSizeNumber=" + totalSizeNumber);
DataAcessHelper.ExecuteReadCommmited(conn);
}
}
internal static string GetLastChangeCheckSum(SqlConnection conn, string tablname)
{
// string querylastScanTimstmap = @" select master.sys.fn_varbintohexstr(cast( MAX(SystemTimeStamp) as varbinary(8)))as varbintohexstr from " + tablname;
string querylastScanTimstmap = @" SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM " + tablname;
// SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM tblsketch WITH (NOLOCK);
try
{
object lastScaltime = DataAcessHelper.RetriveSigleValue(conn, querylastScanTimstmap, null);
if (lastScaltime == null)
{
return(string.Empty);
}
else
{
return(lastScaltime.ToString());
}
}
catch (Exception ex)
{
CLROutput.OutputDebug(tablname + "missing Timstmap" + ex.ToString());
return(string.Empty);
}
}
public static void RestartClr()
{
try
{
// using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
//SqlContext.
conn.Open();
string restcmd = @"EXEC SP_CONFIGURE 'clr enabled' , '0'
RECONFIGURE;
EXEC SP_CONFIGURE 'clr enabled' , '1';
RECONFIGURE;
";
try
{
DataAcessHelper.ExecuteNonQuery(conn, restcmd);
//PLMSCacheSystem.TouchCache();
}
catch { };
}
}
catch { }
//PLMSDWStoredProcedures.GenerateUserDefinTableScript();
}
internal static string GetConnectionInfoWithCode(int?dataSourceFrom)
{
if (dataSourceFrom.HasValue)
{
string dataSourceFronQuery = @"select ConnectionString from PdmDataSource where DataSourceFrom= @DataSourceFrom";
List <SqlParameter> listParamter = new List <SqlParameter> ();
listParamter.Add(new SqlParameter("@DataSourceFrom", dataSourceFrom));
string dataSourValue = null;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
dataSourValue = DataAcessHelper.RetriveSigleValue(conn, dataSourceFronQuery, listParamter) as string;
}
if (string.IsNullOrEmpty(dataSourValue))
{
return(PLMConstantString.PLM_APP_ConnectionString);
}
else
{
return(dataSourValue.ToString());
}
}
else
{
return(PLMConstantString.PLM_APP_ConnectionString);
}
}
private static List <string> GetExternalNappingName(int gridTabId, int currentGridBlockId)
{
List <string> toReturn = new List <string>();
string query = string.Format(@"SELECT distinct dbo.pdmTabGridMetaColumn.GridColumnID,
dbo.pdmTabGridMetaColumn.Visible, dbo.pdmTabGridMetaColumn.ExternalMappingName ,
dbo.pdmTabGridMetaColumn.BlockID AS GridBlockID
FROM dbo.pdmBlockSubItem INNER JOIN
dbo.PdmTabBlock ON dbo.pdmBlockSubItem.BlockID = dbo.PdmTabBlock.BlockID LEFT OUTER JOIN
dbo.pdmTabGridMetaColumn ON dbo.PdmTabBlock.BlockID = dbo.pdmTabGridMetaColumn.BlockID AND
dbo.PdmTabBlock.TabID = dbo.pdmTabGridMetaColumn.TabID
WHERE (dbo.pdmBlockSubItem.ControlType = 6)
AND (dbo.PdmTabBlock.TabID = {0} )
AND (dbo.pdmTabGridMetaColumn.BlockID ={1})
and pdmTabGridMetaColumn.ExternalMappingName <> ''" , gridTabId, currentGridBlockId);
DataTable TabGridColumnResult = new DataTable();
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
TabGridColumnResult = DataAcessHelper.GetDataTableQueryResult(conn, query, null);
}
if (TabGridColumnResult.Rows.Count > 0)
{
toReturn = TabGridColumnResult.AsDataRowEnumerable().Select(o => o["ExternalMappingName"] as string).ToList();
}
return(toReturn);
}
internal static List <LookupItemDto> GetOneSystemDefinTableLookupItems(SqlConnection conn, string queryTAble)
{
List <LookupItemDto> listDto = new List <LookupItemDto> ();
try
{
DataTable datatable = DataAcessHelper.GetDataTableQueryResult(conn, queryTAble);
foreach (DataRow row in datatable.Rows)
{
LookupItemDto itemDto = new LookupItemDto();
// itemDto.Id = (int)row["Id"];
itemDto.Id = row["Id"];
itemDto.Display = row["Display"] as string;
listDto.Add(itemDto);
}
}
catch (Exception ex)
{
}
return(listDto);
}
//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
}
}
// private static void InsertManyToManyRelationshipAndData(Dictionary<string, PdmEntityClrDto> dictAllImportEntity, string childEntityEntityName, string masterEntityTableName, int childEntityId, int masterEntityId, SqlConnection conn)
// {
// string queryRelationEntity = string.Format(@" select EntityID from dbo.pdmentity where ChildEntityID={0} and MasterEntityID={1}", childEntityId, masterEntityId);
// SqlCommand cmdGetRelationEntityId = new SqlCommand(queryRelationEntity, conn);
// object relationEntityId = cmdGetRelationEntityId.ExecuteScalar();
// if (relationEntityId != null)
// {
// string delete1 = @"delete from dbo.pdmEntityMasterChildValue where RelationEntityID = " + relationEntityId;
// string delete2 = string.Format(@" delete from dbo.pdmentity where ChildEntityID={0} and MasterEntityID={1} and IsRelationEntity=1", childEntityId, masterEntityId);
// SqlCommand cmdDeleteRelationEntityId = new SqlCommand(delete1 + System.Environment.NewLine + delete2, conn);
// cmdDeleteRelationEntityId.ExecuteNonQuery();
// }
// string insertNewRaltionEntity = string.Format(@" insert into dbo.pdmentity
// (
// EntityCode,
// IsImport,
// EntityType,
//
// EntityWithFKEntityID,
// MasterEntityColumnID ,
// ChildEntityColumnID,
// IsRelationEntity
// )
// values
// (
// '{0}',
// {1}
// {2},
// {3},
// {4},
// {5}
// ) ", "Import_Relation_" + dictAllImportEntity[masterEntityTableName].EntityCode + "_" + dictAllImportEntity[childEntityEntityName].EntityCode,
// childEntityId,
// masterEntityId
// );
// SqlCommand cmdInsertNewEntity = new SqlCommand();
// CLROutput.Output("insertNewRaltionEntity=" + insertNewRaltionEntity);
// object entityId = InsertNewEntityWithReturnNewIdentityId(conn, insertNewRaltionEntity, cmdInsertNewEntity);
// }
private static void UpdateModifyRecordFromERPToPLM(List <PdmEntityClrDto> plmImportEntityDtoList)
{
foreach (PdmEntityClrDto aPdmEntityClrDto in plmImportEntityDtoList)
{
string exchangeTableName = aPdmEntityClrDto.EntityCode;
DataTable modifyRecordsTable = null;
//Get Ex-change new Row Data
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
string queryERPNewRecord = string.Format(@" select * from {0} where {1}={2} ", aPdmEntityClrDto.EntityCode, PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.Modified);
modifyRecordsTable = DataAcessHelper.GetDataTableQueryResult(conn, queryERPNewRecord);
// CLROutput.SendDataTable(newRecordsTable);
}
using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
{
conn.Open();
foreach (DataRow row in modifyRecordsTable.Rows)
{
int rowId = (int)row[PLMConstantString.ExchangeRowDataPLMPrimayKeyColumn];
SqlCommand cmdDelete = new SqlCommand();
cmdDelete.CommandText = @" delete pdmUserDefineEntityRowValue where RowID=" + rowId;
cmdDelete.Connection = conn;
cmdDelete.ExecuteNonQuery();
InsertRowCellValue(row, conn, aPdmEntityClrDto, rowId);
}
}
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
string updateExchangeStatus = string.Format(@"
update {0}
set {1}={2} ,
{3}=CURRENT_TIMESTAMP
where {4}={5} ",
aPdmEntityClrDto.EntityCode,
PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.NoChange,
PLMConstantString.ExchangeRowDataPLMImportDateTimeColumn,
PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.Modified);
SqlCommand cmdUpdateExTableFromNewToNochnage = new SqlCommand(updateExchangeStatus, conn);
cmdUpdateExTableFromNewToNochnage.ExecuteNonQuery();
}
// Update Ex-change Tabl
}
}
public static void GetRichText(int richTectFileId)
{
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
string query = @"select tblSketch.OriginalImage from tblSketch where SketchID =@richTectFileId";
List <SqlParameter> listPara = new List <SqlParameter>();
listPara.Add(new SqlParameter("@richTectFileId", richTectFileId));
//command.Parameters.Add("@Photo", SqlDbType.Image, photo.Length).Value = photo;
DataTable result = DataAcessHelper.GetDataTableQueryResult(conn, query, listPara);
if (result.Rows.Count > 0)
{
byte[] rowData = result.Rows[0]["OriginalImage"] as byte[];
if (rowData != null)
{
string str = System.Text.Encoding.Default.GetString(rowData);
// CLROutput.OutputDebug(str);
CLROutput.OutputDebug("------------------");
// string str2 = HtmlToPlainText.StripHTML(str);
DataTable datatable = new DataTable();
DataColumn column = new DataColumn("Text");
datatable.Columns.Add(column);
//column.DataType = typeof (String );
// column.MaxLength = 200;
var row = datatable.NewRow();
row["Text"] = str;
datatable.Rows.Add(row);
CLROutput.SendDataTable(datatable);
//LROutput.OutputDebug(str2);
}
}
DataAcessHelper.ExecuteReadCommmited(conn);
}
// GetGridValue(tabId, currentGridBlockId, referenceIds, false);
}
public static void CreateDataBaseTableFromQuery(string query, SqlConnection conn, string datbaseTableName)
{
string sqlCrateTableStament = CreateSQlCreateTableStatmentFromQuery(query, conn, datbaseTableName);
string dropTablecommand = DataAcessHelper.GetSQLDropTableCommand(datbaseTableName);
sqlCrateTableStament = dropTablecommand + System.Environment.NewLine + sqlCrateTableStament;
using (SqlCommand cmd = new SqlCommand(sqlCrateTableStament, conn))
{
cmd.ExecuteNonQuery();
}
}
public static List< PdmEntityMasterChildValueClrDto> GetAllList(SqlConnection conn)
{
List<PdmEntityMasterChildValueClrDto> listDto = new List<PdmEntityMasterChildValueClrDto>();
DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);
foreach (DataRow row in entityDataTable.Rows)
{
listDto.Add(PdmEntityMasterChildValueConverter.ConvertDataRowDto(row));
}
return listDto;
}
private static Dictionary <int, List <LookupItemDto> > GetSysDefineEntityDisplayInfoListNew(List <PdmEntityBlClrDto> sysDefineEntityList, SqlConnection conn)
{
Dictionary <int, List <LookupItemDto> > toReturn = new Dictionary <int, List <LookupItemDto> >();
Dictionary <string, string> dictTableNameAndQuery = new Dictionary <string, string>();
foreach (var aPdmEntityDto in sysDefineEntityList)
{
string systemQueryTable = GetSysDefineQueryIDAndDisplay(aPdmEntityDto);
// CLROutput.OutputDebug("aPdmEntityDto.SysTableName" + aPdmEntityDto.EntityId + "=" + aPdmEntityDto.EntityCode + aPdmEntityDto.SysTableName);
if (!dictTableNameAndQuery.ContainsKey(aPdmEntityDto.SysTableName))
{
dictTableNameAndQuery.Add(aPdmEntityDto.SysTableName, systemQueryTable);
CLROutput.OutputDebug("aPdmEntityDto.SysTableName" + aPdmEntityDto.EntityId + "=" + aPdmEntityDto.EntityCode + aPdmEntityDto.SysTableName + " systemQueryTable=" + systemQueryTable);
}
}
// SqlContext.Pipe.Send("queryRowValue=" + allSystemQueryTable);
DataSet result = DataAcessHelper.GetDataSetQueryResult(conn, dictTableNameAndQuery);
// var dictTableNameAndDto = sysDefineEntityList.ToDictionary(o => o.EntityId , o => o);
foreach (var SysEntityDto in sysDefineEntityList)
{
string sysTableName = SysEntityDto.SysTableName;
foreach (DataTable datatable in result.Tables)
{
// CLROutput.SendDataTable(datatable);
if (sysTableName == datatable.TableName)
{
List <LookupItemDto> listDto = new List <LookupItemDto>();
foreach (DataRow row in datatable.Rows)
{
LookupItemDto itemDto = new LookupItemDto();
itemDto.Id = (int)row["Id"];
itemDto.Display = row["Display"] as string;
listDto.Add(itemDto);
}
toReturn.Add(SysEntityDto.EntityId, listDto);
}
//if(
// DataSetUtilities.SendDataTable(datatable);
}
}
return(toReturn);
}
private static List <PdmTabBlockSubItemExtraInfoClrDto> GetAllList(SqlConnection conn)
{
List <PdmTabBlockSubItemExtraInfoClrDto> listDto = new List <PdmTabBlockSubItemExtraInfoClrDto>();
DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);
foreach (DataRow row in entityDataTable.Rows)
{
listDto.Add(PdmTabBlockSubItemExtraInfoConverter.ConvertDataRowDto(row));
}
return(listDto);
}
public static List <string> GetPrimarykeyList(SqlConnection conn, string databaseTableName)
{
string query = string.Format(@" SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND table_name = '{0}' ", databaseTableName);
// SqlContext.Pipe.Send( query);
var result = DataAcessHelper.GetDataTableQueryResult(conn, query);
//CLROutput.SendDataTable(result);
return(result.AsDataRowEnumerable().Select(row => row["column_name"].ToString()).ToList());
}
public static List <PdmTemplateTabLibReferenceSettingClrDto> GetAllList(SqlConnection conn)
{
List <PdmTemplateTabLibReferenceSettingClrDto> listDto = new List <PdmTemplateTabLibReferenceSettingClrDto>();
DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);
foreach (DataRow row in entityDataTable.Rows)
{
listDto.Add(PdmTemplateTabLibReferenceSettingConverter.ConvertDataRowDto(row));
}
return(listDto);
}
public static List <PdmTabGridMetaColumnClrDto> GetAllList(SqlConnection conn)
{
List <PdmTabGridMetaColumnClrDto> listDto = new List <PdmTabGridMetaColumnClrDto>();
DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);
foreach (DataRow row in entityDataTable.Rows)
{
listDto.Add(PdmTabGridMetaColumnConverter.ConvertDataRowDto(row));
}
return(listDto);
}
private static DataTable GetMutipleBlockGridCellValueTable(int[] referenceIds, int[] blockIds, int[] columnIds)
{
var andblockIdclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(blockIds, GridColumnConstantName.BlockID, false);;
string referenceInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(referenceIds, GridColumnConstantName.ProductReferenceID, true);
// string referenceInClause = string.Empty;
var columnIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(columnIds, GridColumnConstantName.GridColumnID, true);
StringBuilder aQuery = new StringBuilder();
// aQuery.Append( string" SELECT ProductReferenceID,BlockID, GridID, RowID, RowValueGUID ,Sort,");
aQuery.Append(" SELECT " +
GridColumnConstantName.ProductReferenceID + ", " +
GridColumnConstantName.BlockID + ", " +
// GridColumnConstantName.RowID + ", " +
GridColumnConstantName.Sort + ", " +
GridColumnConstantName.GridColumnID + ", " +
GridColumnConstantName.RowValueGUID + ", " +
GridColumnConstantName.ValueText
);
aQuery.Append(" from pdmSearchComplexColumnValue where " + andblockIdclause + referenceInClause + columnIdInclause);
CLROutput.OutputDebug(aQuery.ToString());
string queryCell = aQuery.ToString();
CLROutput.OutputDebug("GetMutipleBlockGridCellValueTable=" + queryCell);
// test 4 Grid for all cell values forthe perforamce
// queryCell = @" SELECT ProductReferenceID, BlockID, RowID, Sort, GridColumnID, ValueText from pdmSearchComplexColumnValue where BlockID in ( 20, 14 ,64,65) ";
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
var dtReferenceGridValue = DataAcessHelper.GetDataTableQueryResult(conn, queryCell);
DataAcessHelper.ExecuteReadCommmited(conn);
return(dtReferenceGridValue);
//CLROutput.SendDataTable(dtReferenceGridValue);
}
}
public static List <PdmEntityColumnClrUserDefineDto> GetErpExchangeDatabaseTableColumnDto(SqlConnection conn, string databaseTableName)
{
string query = string.Format(@"SELECT Columns.COLUMN_NAME as ColumnName,Columns.DATA_TYPE as DataType,
Columns.CHARACTER_MAXIMUM_LENGTH as MaxLength, Columns.DATETIME_PRECISION as Precision, Columns.NUMERIC_SCALE as Scale,
cast( (case when PK.COLUMN_NAME Is not null then 1 else 0 end) as bit) as PrimaryKey
FROM information_schema.columns as Columns
left join
(
SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND table_name = '{0}'
) as PK on Pk.COLUMN_NAME = Columns.COLUMN_NAME
WHERE table_name = '{0}' and
( Columns.DATA_TYPE <> 'timestamp' and Columns.DATA_TYPE <>'image'
and Columns.DATA_TYPE <>'xml'
and Columns.DATA_TYPE <>'varbinary'
)
and Columns.COLUMN_NAME NOT IN ('{1}') ",
databaseTableName,
PLMConstantString.ExchangeRowDataChangeTimeStamp
);
List <PdmEntityColumnClrUserDefineDto> toreturnList = new List <PdmEntityColumnClrUserDefineDto>();
// PLMSDataImport.ExchangeRowDataPLMImportDateTimeColumn
// CLROutput.Output(query);
var result = DataAcessHelper.GetDataTableQueryResult(conn, query);
// CLROutput.SendDataTable(result);
foreach (DataRow arow in result.Rows)
{
PdmEntityColumnClrUserDefineDto fromDataBaseDto = PLMDataRowConverter.ConvertErpExchangeDatabaseTableColumnDataRowToPdmEntityColumnDto(arow);
toreturnList.Add(fromDataBaseDto);
}
var textboxColumn = toreturnList.Where(o => o.UicontrolType == (int)EmControlType.TextBox).FirstOrDefault();
if (textboxColumn != null)
{
textboxColumn.UsedByDropDownList = true;
}
return(toreturnList);
}
private static void TestDatatableScheme(SqlConnection conn)
{
string aQuery = "select TabID,TabName,FolderID from pdmtab ";
DataTable result = DataAcessHelper.GetDataTableQueryResult(conn, aQuery);
Dictionary <string, string> dictDataTableAndDatabaseTableMappingColumn = new Dictionary <string, string>();
dictDataTableAndDatabaseTableMappingColumn.Add("TabID", "TabID");
dictDataTableAndDatabaseTableMappingColumn.Add("TabName", "TabName");
dictDataTableAndDatabaseTableMappingColumn.Add("FolderID", "FolderID");
aQuery = "select * from pdmgrid ";
DataTableUtility.CreateDataBaseTableFromQuery(aQuery, conn, "testpdmgrid");
}
// Key: InternalCode, Value: subitemID
public static Dictionary <string, int> GetDictInteralCodeSubItemId(SqlConnection conn, string blockInterCode)
{
string queryString = @" SELECT pdmBlockSubItem.SubItemID, pdmBlockSubItem.InternalCode
FROM pdmBlockSubItem INNER JOIN
pdmBlock ON pdmBlockSubItem.BlockID = pdmBlock.BlockID
where pdmBlock.InternalCode =@blockInterCode and pdmBlockSubItem.BlockID <3000";
SqlParameter parablockInterCode = new SqlParameter("@blockInterCode", blockInterCode);
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.Parameters.Add(parablockInterCode);
return(DataAcessHelper.GetDataTableQueryResult(cmd).AsDataRowEnumerable()
.ToDictionary(row => row["InternalCode"].ToString(), row => (int)row["SubItemID"]));
}
private static int?GetReferencePomOfUnitMeasure(int referenceId)
{
string query = @"select PomUnitOfMeasure from pdmProduct where ProductReferenceID =@ProductReferenceID";
List <SqlParameter> paraList = new List <SqlParameter> ();
paraList.Add(new SqlParameter("@ProductReferenceID", referenceId));
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
//SqlContext.
conn.Open();
object value = DataAcessHelper.RetriveSigleValue(conn, query, paraList);
return(ControlTypeValueConverter.ConvertValueToInt(value));
}
}
public static List <PdmClrEntitySimpleStructureDto> GetEntityAndColumnStrcutureInfoList(List <int> entityIDs)
{
List <PdmClrEntitySimpleStructureDto> listPdmEntityDto = new List <PdmClrEntitySimpleStructureDto>();
if (entityIDs != null && entityIDs.Count > 0)
{
string entityQuery = "select distinct EntityID ,EntityCode ,EntityType,SysTableName from pdmEntity ";
string entityIdInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
entityQuery = entityQuery + " where " + entityIdInClause;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, entityQuery);
foreach (DataRow row in entityDataTable.Rows)
{
listPdmEntityDto.Add(PLMDataRowConverter.ConvertRowToPdmEntityDto(row));
}
string queryColumn = @" select UserDefineEntityColumnID, EntityId, ColumnName,DataType,UsedByDropDownList,DataRowSort,IsPrimaryKey,IsIdentity,SystemTableColumnName,UicontrolType,Nbdecimal,FkentityId
from PdmUserDefineEntityColumn " + " where " + entityIdInClause;
DataTable columnDataTable = DataAcessHelper.GetDataTableQueryResult(conn, queryColumn);
List <PdmEntityColumnClrUserDefineDto> allColumnDto = new List <PdmEntityColumnClrUserDefineDto>();
foreach (DataRow row in columnDataTable.Rows)
{
allColumnDto.Add(PLMDataRowConverter.ConvertUserDefineEntityColumnDataRowToPdmEntityColumnDto(row));
}
foreach (var entityDto in listPdmEntityDto)
{
entityDto.Columns = allColumnDto.Where(o => o.EntityId == entityDto.EntityId).ToList();
}
}
}
return(listPdmEntityDto);
}
// Big size
private static DataTable GetSizeRotateTableWithSizerunId(int sizerunValueId)
{
var sizeEntity = PdmCacheManager.DictCodeKeyPdmEntityBlCache[EmEntityCode.SizeRunDetail.ToString()];
string connectionInfo = PLMSEntityClrBL.GetConnectionInfoWithCode(sizeEntity.DataSourceFrom);
string querySizeRotate = @" select SizeRunRotateID ,SizeOrder ,SizeName from tblSizeRunRotate where SizeRunId = @sizeRunId order by SizeOrder ";
// need to filter
using (SqlConnection conn = new SqlConnection(connectionInfo))
{
//SqlContext.
conn.Open();
SqlCommand cmdSize = new SqlCommand(querySizeRotate, conn);
SqlParameter paraSizeRunrunIdValueId = new SqlParameter("@sizeRunId", sizerunValueId);
cmdSize.Parameters.Add(paraSizeRunrunIdValueId);
DataTable sizeRotateTable = DataAcessHelper.GetDataTableQueryResult(cmdSize);
return(sizeRotateTable);
}
}
public static void TestExchangeDBConnection()
{
//step1: Get all Ex-change Tables from ex-Chang Dababae
List <string> exChangeDbTableList = null;
Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto = new Dictionary <string, List <PdmEntityColumnClrDto> >();
exChangeDbTableList = GetExistingExDatabaseTableNameAndStructure(exChangeDbTableList, dictExchangeTableColumnDto);
//Step2: insert new Ex-change table
AddNewExchangeTableToPLMSUserDefineEntity(exChangeDbTableList, dictExchangeTableColumnDto);
// step2-2 update strcutre
// Get all Refresh Userdefinetable from PLMS
string queryllEntityImport = @" select EntityID from pdmEntity where IsImport =1 and EntityID > 3000 ";
List <PdmEntityClrDto> plmImportEntityDtoList = null;
using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
{
List <int> enityIds = DataAcessHelper.GetDataTableQueryResult(conn, queryllEntityImport).AsEnumerable().Select(o => (int)o["EntityID"]).ToList();;
plmImportEntityDtoList = PLMSEntityClrBL.GetEntityAndColumnStrcutureInfoList(enityIds, conn);
}
//Step3 Transfer NewRecord FromERP To PLM
TransferNewRecordFromERPToPLM(plmImportEntityDtoList);
////Step4; upadte modfied fileds
UpdateModifyRecordFromERPToPLM(plmImportEntityDtoList);
//ste5: Delete--toDO
//Step 6: update FK relation Ship
UpdateForeignKeyRelationFromERPToPLM(plmImportEntityDtoList);
}
private static Dictionary <int, List <LookupItemDto> > GetEnumEntityDisplayInfoList(List <int> entityIDs, SqlConnection conn)
{
Dictionary <int, List <LookupItemDto> > toReturn = new Dictionary <int, List <LookupItemDto> >();
string enumquery = " select distinct EntityID ,EnumKey ,EnumValue from PdmEntityEnumValue ";
if (entityIDs != null && entityIDs.Count > 0)
{
enumquery = enumquery + " where " + DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
}
// SqlContext.Pipe.Send("enumquery=" + enumquery);
DataTable dataTable = DataAcessHelper.GetDataTableQueryResult(conn, enumquery);
// var result = dataTable.
var subItemQuery = from row in dataTable.AsDataRowEnumerable()
group row by new
{
EntityID = (int)row["EntityID"]
} into grp
select new
{
EntityID = grp.Key.EntityID,
EnumKeyIDAndEnumValueList = grp.Select(r => new LookupItemDto {
Id = (int)r["EnumKey"], Display = r["EnumValue"] as string
}).ToList(),
};
foreach (var o in subItemQuery)
{
toReturn.Add(o.EntityID, o.EnumKeyIDAndEnumValueList);
}
return(toReturn);
}
private static void AddNewExchangeTableToPLMSUserDefineEntity(List <string> exChangeDbTableList, Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto)
{
using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
{
conn.Open();
string queryGetImportFolderId = " SELECT SetupValue FROM pdmsetup WHERE setupCode='ImportEntityFolder'";
SqlCommand cmdGetFodlerId = new SqlCommand(queryGetImportFolderId, conn);
string folderId = cmdGetFodlerId.ExecuteScalar().ToString();
string querExistingImportTableInPLm = @" select EntityCode from pdmentity where IsImport =1 and EntityID > 3000 and EntityCode like 'EX_%' ";
SqlCommand cmdGetExistingTable = new SqlCommand(querExistingImportTableInPLm, conn);
// var existingEntityCode = DataAcessHelper.GetDataTableQueryResult(conn, querExistingImportTableInPLm).AsEnumerable().Select(o => o["EntityCode"]);
List <string> existingEntityList = DataAcessHelper.GetDataTableQueryResult(conn, querExistingImportTableInPLm).AsEnumerable().Select(o => o.Field <string>("EntityCode").Trim().ToLowerInvariant()).ToList();
// existingEntityList.ForEach(o => CLROutput.Output("PLmexistingEntityList=" + o));
//exChangeDbTableList.ForEach(o => CLROutput.Output("exChangeDbTableList=" + o));
var newEntityList = exChangeDbTableList.Except(existingEntityList);
foreach (string newEntity in newEntityList)
{
object newEntityID = InsertNewExportUserDefineEntity(folderId, conn, newEntity);
// need to insert table as well
List <PdmEntityColumnClrDto> columnDtoList = dictExchangeTableColumnDto[newEntity];
foreach (PdmEntityColumnClrDto pdmEntityColumnClrDto in columnDtoList)
{
pdmEntityColumnClrDto.EntityId = (int)newEntityID;
InsertPdmUserDefineColunmDto(pdmEntityColumnClrDto, conn);
}
}
}
}
public static DataTable GetMutipleSysDefineEntityRowValue(int entityID, List <string> systemColumNames)
{
string systemDefineColumnName = string.Empty;
foreach (var columnName in systemColumNames)
{
if (columnName != string.Empty)
{
systemDefineColumnName = systemDefineColumnName + columnName + ",";
}
}
if (systemDefineColumnName != string.Empty)
{
systemDefineColumnName = systemDefineColumnName.Substring(0, systemDefineColumnName.Length - 1);
// var pdmSystemEntity = GetBLEntityStructureById(entityID);
var pdmSystemEntity = PdmCacheManager.DictPdmEntityBlEntity[entityID];
string connectionInfo = GetConnectionInfoWithCode(pdmSystemEntity.DataSourceFrom);
// string idInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(keyValues, pdmSystemEntity.SystemDefinePrimaryKeyColumnName.SystemTableColumnName, false);
string aQuery = "select distinct " + pdmSystemEntity.SystemDefinePrimaryKeyColumnName.SystemTableColumnName + " as Id, " + systemDefineColumnName
+ " from " + pdmSystemEntity.SysTableName; ///+ " where " + idInclause;
// need to filter
using (SqlConnection conn = new SqlConnection(connectionInfo))
{
//SqlContext.
conn.Open();
return(DataAcessHelper.GetDataTableQueryResult(conn, aQuery));
}
}
return(new DataTable());
}
public static Dictionary <int, List <SimpleUserDefineEntityRow> > GetDictEntityUserDefineRows(List <int> entityIDs, List <int> ddlUserDefineEntityColumnIDs)
{
Dictionary <int, List <SimpleUserDefineEntityRow> > toReturn = new Dictionary <int, List <SimpleUserDefineEntityRow> >();
entityIDs = entityIDs.Distinct().ToList();
string queryRowValue = pdmUserDefineMutipleColumnEntityRowValue;
if (entityIDs != null && entityIDs.Count > 0)
{
//AND (EntityID IN (3031, 3095))
queryRowValue = queryRowValue + " where " + DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
}
if (ddlUserDefineEntityColumnIDs != null && ddlUserDefineEntityColumnIDs.Count > 0)
{
//and UserDefineEntityColumnID in ( 442,444)
queryRowValue = queryRowValue + DataAcessHelper.GenerateColumnInClauseWithAndCondition(ddlUserDefineEntityColumnIDs, "UserDefineEntityColumnID", true);
}
DataTable RowValuDatatable;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
RowValuDatatable = DataAcessHelper.GetDataTableQueryResult(conn, queryRowValue);
}
CLROutput.SendDebugDataTable(RowValuDatatable);
// DataSetUtilities.SendDataTable(RowValuDatatable);
// Entity level group
var groupByEntityIdQuery = from row in RowValuDatatable.AsDataRowEnumerable()
group row by new
{
EntityID = (int)row["EntityID"]
} into grp
select new
{
EntityID = grp.Key.EntityID,
RowIDAndValueList = grp.Select(r => new
{
RowID = (int)r["RowID"],
SortOrder = (r["SortOrder"] as int?).HasValue ? (r["SortOrder"] as int?).Value : 0,
UserDefineEntityColumnID = (int)r["UserDefineEntityColumnID"],
ValueText = r["ValueText"] as string
}).ToList(),
};
foreach (var o in groupByEntityIdQuery)
{
int entityId = o.EntityID;
var RowIDAndValueList = o.RowIDAndValueList;
var simpleUserDefineEntityRowList = from row in RowIDAndValueList
group row by new
{
RowID = row.RowID,
SortOrder = row.SortOrder
} into grpRow
select new
{
RowId = grpRow.Key.RowID,
SortOrder = grpRow.Key.SortOrder,
RowCellValueList = grpRow.Select
(
row => new
{
ColumnId = row.UserDefineEntityColumnID,
ValueText = row.ValueText
}
)
};
List <SimpleUserDefineEntityRow> listDto = new List <SimpleUserDefineEntityRow>();
foreach (var queryItem in simpleUserDefineEntityRowList)
{
SimpleUserDefineEntityRow aSimpleUserDefineEntityRow = new SimpleUserDefineEntityRow();
aSimpleUserDefineEntityRow.RowId = queryItem.RowId;
aSimpleUserDefineEntityRow.SortOrder = queryItem.SortOrder;
foreach (var column in queryItem.RowCellValueList)
{
int columnId = column.ColumnId;
//int controlType = dictColumnIdControType[columnId];
//object value= ControlTypeValueConverter.ConvertValueToObject(column.ValueText, controlType);
aSimpleUserDefineEntityRow.Add(columnId, column.ValueText);
}
listDto.Add(aSimpleUserDefineEntityRow);
}
toReturn.Add(o.EntityID, listDto);
}
return(toReturn);
}
//TODO List !!!! CLR link to ERP data source direcly !
public static Dictionary <int, List <LookupItemDto> > GetUserDefineEntityDisplayInfoList(List <PdmEntityBlClrDto> userDefineEntityList, SqlConnection plmConnection)
{
Dictionary <int, List <LookupItemDto> > toReturn = new Dictionary <int, List <LookupItemDto> >();
var entityIDs = userDefineEntityList.Select(o => o.EntityId).Distinct().ToList();
List <int> ddlUserDefineEntityColumnIDs = new List <int>();
foreach (var enityDto in userDefineEntityList)
{
ddlUserDefineEntityColumnIDs.AddRange
(
enityDto.PdmUserDefineEntityColumnList.Where(o => o.UsedByDropDownList.HasValue && o.UsedByDropDownList.Value).Select(o => o.UserDefineEntityColumnId).ToList()
);
}
string queryRowValue = pdmUserDefineMutipleColumnEntityRowValue;
if (entityIDs != null && entityIDs.Count > 0)
{
//AND (EntityID IN (3031, 3095))
queryRowValue = queryRowValue + " where " + DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
}
if (ddlUserDefineEntityColumnIDs != null && ddlUserDefineEntityColumnIDs.Count > 0)
{
//and UserDefineEntityColumnID in ( 442,444)
queryRowValue = queryRowValue + DataAcessHelper.GenerateColumnInClauseWithAndCondition(ddlUserDefineEntityColumnIDs, "UserDefineEntityColumnID", true);
}
// SqlContext.Pipe.Send("queryRowValue=" + queryRowValue);
DataTable RowValuDatatable = DataAcessHelper.GetDataTableQueryResult(plmConnection, queryRowValue);
var subItemQuery = from row in RowValuDatatable.AsDataRowEnumerable()
group row by new
{
EntityID = (int)row["EntityID"]
} into grp
select new
{
EntityID = grp.Key.EntityID,
RowIDAndValueList = grp.Select(r => new
{
RowID = (int)r["RowID"],
ValueText = r["ValueText"] as string
}).ToList(),
};
foreach (var o in subItemQuery)
{
int entityId = o.EntityID;
var RowIDAndValueList = o.RowIDAndValueList;
var lookupItemDtoList = from row in RowIDAndValueList
group row by new
{
RowID = row.RowID
} into grp
select new LookupItemDto()
{
Id = grp.Key.RowID,
Display = grp.Select(r => r.ValueText as string).ToList().Aggregate((current, next) => current + "|" + next)
};
List <LookupItemDto> listDto = new List <LookupItemDto>();
foreach (var itme in lookupItemDtoList)
{
listDto.Add(itme);
}
toReturn.Add(o.EntityID, listDto);
}
return(toReturn);
}