public static void GetSpecSize(int referenceId)
{
// 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 = GetSpecSizeRunTable(referenceId, conn, out baseSizeIndexFromZero, out totalSizeNumber);
CLROutput.SendDataTable(returnDataTble);
CLROutput.OutputDebug("baseSizeIndex=" + baseSizeIndexFromZero);
CLROutput.OutputDebug("totalSizeNumber=" + totalSizeNumber);
DataAcessHelper.ExecuteReadCommmited(conn);
}
}
private static void ProcessUserDefineEntityColumn(PdmGridClrDto aPdmGridEntity, DataTable toReturnDcuTable, List <SimpleUserDefineEntityRow> listRow, int masterColumnId)
{
Dictionary <int, SimpleUserDefineEntityRow> dictRow = listRow.ToDictionary(o => o.RowId, o => o);
foreach (DataRow dataRow in toReturnDcuTable.Rows)
{
string valueId = dataRow[masterColumnId.ToString()] as string;
CLROutput.OutputDebug("masterColumnId value" + valueId);
if (!string.IsNullOrEmpty(valueId))
{
// int.Parse (
int intValueId = int.Parse(valueId);
if (dictRow.ContainsKey(intValueId))
{
var dtRow = dictRow[intValueId];
foreach (var dependentEntityColumn in aPdmGridEntity.MasterEntityDepdentColumn[masterColumnId])
{
int userDefineColumnId = int.Parse(dependentEntityColumn.InternalCode);
// CLROutput.Output("userDefineColumnId" + userDefineColumnId);
object value = ControlTypeValueConverter.ConvertValueToObject(dtRow[userDefineColumnId], dependentEntityColumn.ColumnTypeId);
if (value != null)
{
dataRow[dependentEntityColumn.GridColumnId.ToString()] = value;
}
}
}
}
}
}
//dataBaseTableName must exsits first
public static void BulkCopyDatatableToDatabaseTable(SqlConnection connection, DataTable sourceDataTable, string destinationDataBaseTableName, Dictionary <string, string> dictDataTableAndDatabaseTableMappingColumn)
{
try
{
SqlBulkCopy bulkCopy =
new SqlBulkCopy
(
connection,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
);
if (dictDataTableAndDatabaseTableMappingColumn != null && dictDataTableAndDatabaseTableMappingColumn.Count > 0)
{
foreach (var pair in dictDataTableAndDatabaseTableMappingColumn)
{
bulkCopy.ColumnMappings.Add(pair.Key, pair.Value);
}
}
// bulkCopy.BatchSize = 50;
bulkCopy.DestinationTableName = destinationDataBaseTableName;
bulkCopy.WriteToServer(sourceDataTable);
}
catch (Exception ex)
{
CLROutput.OutputDebug("connection" + connection.Database);
CLROutput.OutputDebug("Exception" + ex.ToString());
}
}
private static void ProcessSingleTab(SqlInt32 aTabID, SqlConnection conn, string refids)
{
string tabQuery = @"select top 1 RootlevelselectSQLscript from PLM_Dw_tabgridscript where tabID=" + aTabID + " and gridid is null";
SqlCommand cmdQueryTabNameAndSelectRootlevel = new SqlCommand(tabQuery, conn);
string sQLTabLevelSelect = cmdQueryTabNameAndSelectRootlevel.ExecuteScalar() as string;
CLROutput.OutputDebug("tabQuery=" + tabQuery, conn);
CLROutput.OutputDebug("sQLTabLevelSelect=" + sQLTabLevelSelect, conn);
string tabTablename = PLMSDWStoredProcedures.TabTableName[aTabID.Value];
if (tabTablename != string.Empty)
{
string deleteOLDValue = string.Format(" delete {0} WHERE TabID={1} and ProductReferenceID in ({2}) ", tabTablename, aTabID, refids);
SqlCommand deletcmd = new SqlCommand(deleteOLDValue, conn);
CLROutput.OutputDebug("before deleteOLDValue=" + deleteOLDValue, conn);
try
{
deletcmd.ExecuteNonQuery();
CLROutput.OutputDebug("after delete ok deleteOLDValue=" + deleteOLDValue, conn);
}
catch (Exception ex)
{
CLROutput.OutputDebug("delete failed deleteOLDValue=" + deleteOLDValue + ex.ToString(), conn);
}
string addReplaceAndRef = string.Format(" where ProductReferenceID in ({0}) and ", refids);
string inserIntoSelect = " insert into " + tabTablename + " " + sQLTabLevelSelect.Replace("WHERE", addReplaceAndRef);;
CLROutput.OutputDebug("before inserIntoSelect=" + inserIntoSelect, conn);
try
{
SqlCommand insertcmd = new SqlCommand(inserIntoSelect, conn);
insertcmd.ExecuteNonQuery();
CLROutput.OutputDebug("after insert ok inserIntoSelect=" + inserIntoSelect, conn);
}
catch (Exception ex)
{
CLROutput.OutputDebug("exception insertcmd failed=" + inserIntoSelect + ex.ToString(), 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);
}
}
//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 ScanSessionTimer_Elapsed(object sender, ElapsedEventArgs e)
{
CLROutput.OutputDebug("ScanSessionTimer_Elapsed??");
foreach (var sysDefineEntity in SystemDefineEntityList)
{
UpdateSystemDefineCache(sysDefineEntity);
}
UpdateUserDefineCache();
}
public static void SetupAllLookItemCache()
{
var allCacheEntity = PdmCacheManager.DictPdmEntityBlEntity;
List <int> entityIDs = allCacheEntity.Keys.ToList();
Dictionary <int, List <LookupItemDto> > dictEntityLookupItemDto = GetDictEntityLookItemDtoFromDataBase(entityIDs);
AddEntityToSystemCache(dictEntityLookupItemDto);
CLROutput.OutputDebug("done: add cache with last scan timestamp");
}
public static string GenerateSQlCreateStatmentFromSchmeTable(string tableName, DataTable schema, int[] primaryKeys)
{
string sql = "CREATE TABLE [" + tableName + "] (\n";
// columns
foreach (DataRow column in schema.Rows)
{
bool?isHidden = column["IsHidden"] as bool?;
if (!(isHidden.HasValue && isHidden.Value))
{
sql += "\t[" + column["ColumnName"].ToString() + "] " + SQLGetType(column);
bool?isAllowDBNull = column["AllowDBNull"] as bool?;
if (!(isAllowDBNull.HasValue && isAllowDBNull.Value))
{
sql += " NOT NULL";
}
sql += ",\n";
}
}
sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// primary keys
string pk = ", CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";
bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
if (hasKeys)
{
// user defined keys
foreach (int key in primaryKeys)
{
pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
}
}
else
{
// check schema for keys
string keys = string.Join(", ", GetPrimaryKeys(schema));
pk += keys;
hasKeys = keys.Length > 0;
}
pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";
if (hasKeys)
{
sql += pk;
}
sql += ")";
CLROutput.OutputDebug("SQl" + sql);
return(sql);
}
private static void UpdateSystemDefineCache(PdmEntityBlClrDto entity)
{
int entityId = entity.EntityId;
CLROutput.OutputDebug("UpdateSystemDefineCache start... ConnectInfo:" + entity.ConnectInfo);
// CLROutput.OutputDebug("UpdateSystemDefineCache start...");
if (!string.IsNullOrEmpty(entity.ConnectInfo))
{
//try
//{
using (SqlConnection conn = new SqlConnection(entity.ConnectInfo))
{
conn.Open();
string lastChange = PdmCacheEntityLookupItem.GetLastChangeCheckSum(conn, entity.SysTableName);
// CLROutput.InsertException(conn, "entity.LastScanCheckSum: " + entity.LastScanCheckSum + "lastChange :" + lastChange);
CLROutput.OutputDebug("entity.LastScanCheckSum: " + entity.LastScanCheckSum + "lastChange :" + lastChange);
if (lastChange != entity.LastScanCheckSum)
{
if (DictEntityLookupCache.ContainsKey(entityId))
{
CLROutput.OutputDebug(" lastChange sysdefine before DictEntityLookupCache " + "__" + entity.EntityCode + "__" + entity.LastScanCheckSum, conn);
List <LookupItemDto> newLookupitem = PdmCacheEntityLookupItem.GetOneSystemDefinTableLookupItems(conn, PLMSEntityClrBL.GetSysDefineQueryIDAndDisplay(entity));
Dictionary <object, string> result = PdmCacheEntityLookupItem.ConvertLookItemToDictionaryItem(newLookupitem);
DictEntityLookupCache.Remove(entityId);
DictEntityLookupCache.Add(entityId, result);
entity.LastScanCheckSum = lastChange;
// CLROutput.InsertException(conn, " lastChange sysdefine after" + "__" + entity.EntityCode + "__" + entity.LastScanCheckSum);
}
}
}
//}
//catch (Exception ex)
//{
// //using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
// //{
// // conn.Open();
// // CLROutput.InsertException(conn, " exception " + entity.EntityId + "__" + entity.EntityCode + ex.ToString());
// //}
//}
}
}
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);
}
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 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);
}
}
internal static List <GridColumnClrUserDefineDto> GetTabGridSelectColumnAndAliasDtoFromCache(int tabId, PdmBlockClrDto dmBlockClrDto)
{
string key = tabId.ToString() + "_" + dmBlockClrDto.BlockId.ToString();
if (DictMainTabBlokGridColumnCache.ContainsKey(key))
{
CLROutput.OutputDebug("string key = " + key);
return(DictMainTabBlokGridColumnCache[key]);
}
else
{
List <GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto);
DictMainTabBlokGridColumnCache.Add(key, printColumnListDto);
return(printColumnListDto);
}
}
private static void SetupEntityScanTimer()
{
CLROutput.OutputDebug("tiemrestart");
Timer SessionTimer = new Timer();
SessionTimer.Enabled = true;
SessionTimer.AutoReset = true;
SessionTimer.Elapsed += new ElapsedEventHandler(ScanSessionTimer_Elapsed);
// open datasource, edist save,, then open teckpakc print need one 1 minus
// cause bug 1.5 ???
SessionTimer.Interval = 1 * 20 * 1000;
// SessionTimer.Interval = 5 * 1000;
SessionTimer.Start();
}
internal static List <GridColumnClrUserDefineDto> GetTabGridSelectColumnAndAliasDtoFromCache(int tabId, PdmBlockClrDto dmBlockClrDto, bool IsGetAliasname)
{
string key = tabId.ToString() + "_" + dmBlockClrDto.BlockId.ToString();
if (IsGetAliasname)
{
if (DictMainTabBlokGridColumnCache.ContainsKey(key))
{
CLROutput.OutputDebug("string key = " + key);
return(DictMainTabBlokGridColumnCache[key]);
}
else
{
List <GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto, true);
DictMainTabBlokGridColumnCache.Add(key, printColumnListDto);
return(printColumnListDto);
}
}
else // it is External mapping column
{
if (DictMainTabBlokGridColumnExternaMappingCache.ContainsKey(key))
{
// CLROutput.OutputDebug("string key = " + key);
return(DictMainTabBlokGridColumnExternaMappingCache[key]);
}
else
{
List <GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto, false);
DictMainTabBlokGridColumnExternaMappingCache.Add(key, printColumnListDto);
return(printColumnListDto);
}
}
// List<GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto, IsGetAliasname);
// DictMainTabBlokGridColumnCache.Add(key, printColumnListDto);
// return printColumnListDto;
}
// [Microsoft.SqlServer.Server.SqlProcedure]
public static void TestCLR()
{
// PLMSReferenceValueRetrieveBL.GetMutipleBlockGridCellValueTable(new[] { 9625 }, new[] { 5807 }, new[] { 4407,
//4408,
//4410,
//4411,
//4412,
//4413,
//4414,
//4415,
//4416});
var result =
PLMSReferenceValueRetrieveBL.RetrieveBlocksReferencesGridColumnsRowList(new[] { 9625 }, new[] { 5807 }, new[] { 4407,
4408,
4410,
4411,
4412,
4413,
4414,
4415,
4416 });
//CLROutput.Output(result.Count.ToString () );
foreach (var pair in result)
{
CLROutput.OutputDebug(pair.Key.ToString());
var dictValue1 = pair.Value;
foreach (var pair2 in dictValue1)
{
CLROutput.OutputDebug(pair2.Key.ToString());
var dictvalue3 = pair2.Value;
foreach (var row in dictvalue3)
{
// row.RowId
//CLROutput.Output(pair3.Key.ToString());
//var dictvalue4 = pair3.Value;
//foreach (var pair4 in dictvalue4)
//{
// CLROutput.Output(pair4.Key.ToString());
// var dictvalue5 = pair4.Value;
//}
}
}
}
// CLROutput.SendDataTable(gridcolumnResultDataTable);
//using (SqlConnection conn = new SqlConnection("Data Source=srv-spider;Initial Catalog=PLMS_Devlp;User ID=sa;Password=visualdev"))
//{
// //SqlContext.
// conn.Open();
// // TestDatatableScheme(conn);
// // DataTableUtility.BulkCopyDatatableToDatabaseTable(conn, result, "FromDataTale", dictDataTableAndDatabaseTableMappingColumn);
// TestExcelExport(conn);
//}
//PLMSDWStoredProcedures.GenerateUserDefinTableScript();
}
} // key1: blockId, key2:referecne, value
public static Dictionary <int, Dictionary <int, object> > RetrieveReferenceSimpleDcutValue(IEnumerable <int> referenceIds, List <PdmBlockSubItemClrDto> subItemList)
{
// subitemIds = subitemIds.Distinct().ToArray ();
if (referenceIds.Count() == 0 || subItemList.Count == 0)
{
return(new Dictionary <int, Dictionary <int, object> >());
}
Dictionary <int, int> dictSuibtemIdControType = new Dictionary <int, int>();
foreach (var blockSubitem in subItemList)
{
dictSuibtemIdControType.Add(blockSubitem.SubItemId, blockSubitem.ControlType);
}
Dictionary <int, Dictionary <int, object> > toReturn = new Dictionary <int, Dictionary <int, object> >();
if (referenceIds == null || referenceIds.Count() == 0)
{
return(toReturn);
}
var subitemIds = subItemList.Select(o => o.SubItemId).ToList();
var referenceInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(referenceIds, GridColumnConstantName.ProductReferenceID, false);
var subitemIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(subitemIds, GridColumnConstantName.SubItemID, true);
StringBuilder aQuery = new StringBuilder();
// aQuery.Append( string" SELECT ProductReferenceID,BlockID, GridID, RowID, RowValueGUID ,Sort,");
aQuery.Append(" SELECT " +
GridColumnConstantName.ProductReferenceID + ", " +
GridColumnConstantName.SubItemID + ", " +
GridColumnConstantName.ValueText
);
string queryString = aQuery.ToString() + " from pdmSearchSimpleDcuValue where " + referenceInClause + subitemIdInclause;
CLROutput.OutputDebug("from pdmSearchSimpleDcuValu" + queryString);
DataTable dtSimpleValue;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
dtSimpleValue = DataAcessHelper.GetDataTableQueryResult(conn, queryString);
DataAcessHelper.ExecuteReadCommmited(conn);
}
var subItemQuery = from row in dtSimpleValue.AsDataRowEnumerable()
group row by new
{
ProductReferenceID = (int)row[GridColumnConstantName.ProductReferenceID]
} into grp
orderby grp.Key.ProductReferenceID
select new
{
Key = grp.Key.ProductReferenceID,
SubitemIDAndValueList = grp.Select(r => new { id = (int)r[GridColumnConstantName.SubItemID], value = r[GridColumnConstantName.ValueText] }),
};
foreach (var o in subItemQuery)
{
Dictionary <int, object> dictSubitemValue = new Dictionary <int, object>();
foreach (var subitem in o.SubitemIDAndValueList)
{
var value = ControlTypeValueConverter.ConvertValueToObject(subitem.value, dictSuibtemIdControType[subitem.id]);
dictSubitemValue.Add(subitem.id, value);
}
var notInDict = subitemIds.Except(dictSubitemValue.Keys);
foreach (var subitId in notInDict)
{
dictSubitemValue.Add(subitId, null);
}
toReturn.Add(o.Key, dictSubitemValue);
}
return(toReturn);
} // key1: blockId, key2:referecne, value
public static DataTable RetrieveDataTableReferenceSimpleDcutValue(IEnumerable <string> referenceIds, List <PdmBlockSubItemClrDto> subItemList, bool isShowLookUpItem = true)
{
Dictionary <int, int> dictSuibtemIdControType = new Dictionary <int, int>();
foreach (var blockSubitem in subItemList)
{
dictSuibtemIdControType.Add(blockSubitem.SubItemId, blockSubitem.ControlType);
}
Dictionary <int, Dictionary <int, object> > toReturn = new Dictionary <int, Dictionary <int, object> >();
//if (referenceIds == null || referenceIds.Count() == 0)
// return simpleDcuTable;
var subitemIds = subItemList.Select(o => o.SubItemId).ToList();
if (subitemIds.Count == 0)
{
return(new DataTable());
}
string referenceInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(referenceIds, GridColumnConstantName.ProductReferenceID, true);
if (referenceIds.Count() == 1 && referenceIds.ElementAt(0) == "-2")
{
referenceInClause = string.Empty;
}
string subitemIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(subitemIds, GridColumnConstantName.SubItemID, false);
StringBuilder aQuery = new StringBuilder();
// aQuery.Append( string" SELECT ProductReferenceID,BlockID, GridID, RowID, RowValueGUID ,Sort,");
aQuery.Append(" SELECT " +
GridColumnConstantName.ProductReferenceID + ", " +
GridColumnConstantName.SubItemID + ", " +
GridColumnConstantName.ValueText
);
string queryString = aQuery.ToString() + " from pdmSearchSimpleDcuValue where " + subitemIdInclause + referenceInClause;
DataTable dtSimpleValue;
var allSubitemeEntityIds = subItemList.Where(o => o.EntityId.HasValue).Select(o => o.EntityId.Value).ToList();
Dictionary <int, Dictionary <object, string> > dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds);
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
dtSimpleValue = DataAcessHelper.GetDataTableQueryResult(conn, queryString);
// dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds, conn);
DataAcessHelper.ExecuteReadCommmited(conn);
}
var subItemQuery = from row in dtSimpleValue.AsDataRowEnumerable()
group row by new
{
ProductReferenceID = (int)row[GridColumnConstantName.ProductReferenceID]
} into grp
orderby grp.Key.ProductReferenceID
select new
{
Key = grp.Key.ProductReferenceID,
SubitemIDAndValueList = grp.Select(r => new { id = (int)r[GridColumnConstantName.SubItemID], value = r[GridColumnConstantName.ValueText] }),
};
DataTable roReturnDcuTable = CreateDataTableStrcutureFromBlockSubitem(subItemList);
foreach (var o in subItemQuery)
{
//Dictionary<int, object> dictSubitemValue = new Dictionary<int, object>();
DataRow dataRow = roReturnDcuTable.NewRow();
dataRow[GridColumnConstantName.ProductReferenceID] = o.Key;
foreach (var subitem in o.SubitemIDAndValueList)
{
object value = ControlTypeValueConverter.ConvertValueToObject(subitem.value, dictSuibtemIdControType[subitem.id]);
if (value == null)
{
dataRow[subitem.id.ToString()] = DBNull.Value;
}
else
{
// CLROutput.Output("convervalue=" + value);
dataRow[subitem.id.ToString()] = value;
// CLROutput.Output("dataRow[subitem.id.ToString()]" + dataRow[subitem.id.ToString()]);
}
}
roReturnDcuTable.Rows.Add(dataRow);
}
if (isShowLookUpItem)
{
List <PdmBlockSubItemClrDto> dDLSubitemDtoList = subItemList.Where(o => o.ControlType == (int)EmControlType.DDL && o.EntityId.HasValue).ToList();
foreach (var dictDDLSubitemDto in dDLSubitemDtoList)
{
int entityId = dictDDLSubitemDto.EntityId.Value;
string stringSubItemId = dictDDLSubitemDto.SubItemId.ToString();
if (dictKeyEntityDictKeyIdDisplayString.ContainsKey(entityId))
{
Dictionary <object, string> lookUpItem = dictKeyEntityDictKeyIdDisplayString[entityId];
foreach (DataRow row in roReturnDcuTable.Rows)
{
int?lookupid = ControlTypeValueConverter.ConvertValueToInt(row[stringSubItemId]);
if (lookupid.HasValue)
{
int idvalue = lookupid.Value;
if (lookUpItem.ContainsKey(idvalue))
{
row[stringSubItemId] = lookUpItem[idvalue];;
CLROutput.OutputDebug("entityID_idvalue+dislayvalue=" + entityId.ToString() + "_" + idvalue.ToString() + "_" + "_" + lookUpItem[idvalue]);
}
}
}
}
}
}
return(roReturnDcuTable);
} // key1: blockId, key2:referecne, value
/// <summary>
/// Main method for exporting to Excel.
/// </summary>
/// <param name="data"></param>
public void Export(DataSet data)
{
string outputFile;
outputFile = mFilePath + defaultFilename + ".xls";
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
CLROutput.OutputDebug("outputFile=" + outputFile);
using (XmlWriter xw = XmlWriter.Create(outputFile, settings))
{
//Required namespaces used for SpreadsheetML standard.
xw.WriteStartDocument();
xw.WriteProcessingInstruction("mso-application", "Excel.Sheet");
xw.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:Spreadsheet");
xw.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
xw.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
xw.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:Spreadsheet");
xw.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");
WriteHeaderInfo(xw);
//Iterate the tables in the dataset.
//Each table will become a tab or sheet in the workbook.
foreach (DataTable dt in data.Tables)
{
//Default the tab name to either the table name
//or pull tab name from first column of each resultset.
string sheetName;
if (dt.Rows.Count > 0)
{
sheetName = dt.Rows[0][sheetNameColumnOrdinal].ToString();
}
else
{
sheetName = dt.TableName;
}
//Start of a tab
xw.WriteStartElement("Worksheet");
xw.WriteAttributeString("ss", "Name", null, sheetName);
xw.WriteStartElement("Table");
xw.WriteAttributeString("ss", "DefaultColumnWidth", null, "100");
//Write out header data
xw.WriteStartElement("Row");
//Format column headings
foreach (DataColumn dc in dt.Columns)
{
if (dc.Ordinal != sheetNameColumnOrdinal)
{
xw.WriteStartElement("Cell");
xw.WriteAttributeString("ss", "StyleID", null, "Header");
xw.WriteStartElement("Data");
xw.WriteAttributeString("ss", "Type", null, "String");
xw.WriteString(dc.ColumnName);
xw.WriteEndElement(); //End Data
xw.WriteEndElement(); //End Cell
}
}
xw.WriteEndElement(); //End Row
//Write out row data
foreach (DataRow dr in dt.Rows)
{
xw.WriteStartElement("Row");
foreach (DataColumn dc in dt.Columns)
{
if (dc.Ordinal != sheetNameColumnOrdinal)
{
string dataType;
string style;
string output;
//Set appropriate styling of each cell based on datatype
//This depends on how sql server ends up reporting the datatype.
switch (dc.DataType.ToString())
{
case "System.DateTime":
dataType = "DateTime";
style = "Date";
try
{
output = DateTime.Parse(dr[dc].ToString()).ToString("yyyy-MM-dd");
}
catch (FormatException fe) //date is null or empty in dataset
{
output = "";
}
break;
case "System.Decimal":
case "System.Double":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
dataType = "Number";
style = "Data";
output = dr[dc].ToString().Trim();
break;
default:
dataType = "String";
style = "Data";
output = dr[dc].ToString().Trim();
break;
}
//if no data then write empty cell node
xw.WriteStartElement("Cell");
xw.WriteStartAttribute("StyleID", "");
xw.WriteString(style);
xw.WriteEndAttribute(); //End Style Attribute
if (output != "")
{
xw.WriteStartElement("Data");
xw.WriteAttributeString("ss", "Type", null, dataType);
xw.WriteString(output);
xw.WriteEndElement(); //End Data
}
xw.WriteEndElement(); //End Cell
}
}
xw.WriteEndElement(); //End Row
}
xw.Flush();
xw.WriteEndElement(); //End Table
xw.WriteEndElement(); //End Worksheet
}
xw.WriteEndElement(); //End Workbook
xw.Flush();
}
}
// [Microsoft.SqlServer.Server.SqlProcedure]
public static void DoSynchronizeReferenceBlockToTabGridDWTo(string refids, string blockids)
{
if (string.IsNullOrEmpty(refids) || string.IsNullOrEmpty(blockids))
{
return;
}
#region ------------ do Tab sych
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
//InsertException(conn, "refids=" + refids + " blockids=" + blockids);
CLROutput.OutputDebug("refids=" + refids + " blockids=" + blockids, conn);
string processTab = string.Format(@"
SELECT DISTINCT pdmTabBlock.TabID
FROM pdmTabBlock INNER JOIN
pdmBlock on pdmTabBlock.BlockID = pdmBlock.BlockID
inner join
pdmDWRequireTabAndGrid ON pdmDWRequireTabAndGrid.TabID = pdmTabBlock.TabID INNER JOIN
pdmBlockSubItem ON pdmTabBlock.BlockID = pdmBlockSubItem.BlockID
WHERE (pdmBlockSubItem.GridID IS NULL) and (pdmTabBlock.BlockID IN ({0}))", blockids);
CLROutput.OutputDebug("processTabBlock=" + processTab, conn);
SqlCommand cmd = new SqlCommand(processTab, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
System.Data.DataTable resultTabel = new DataTable();
adapter.Fill(resultTabel);
foreach (DataRow aRow in resultTabel.Rows)
{
//begin root leve
SqlInt32 aTabID = Converter.ToDDLSqlInt32(aRow["TabID"]);
ProcessSingleTab(aTabID, conn, refids);
}
}
#endregion
#region ----------- do Grid Transfer
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
// string processGrid = string.Format(@"
// SELECT DISTINCT dbo.pdmBlockSubItem.BlockID, dbo.pdmBlockSubItem.GridID, dbo.pdmTabField.TabID, dbo.pdmTab.ProductCopyTabRootTabID
// FROM dbo.pdmBlockSubItem INNER JOIN
// dbo.pdmItem ON dbo.pdmBlockSubItem.BlockID = dbo.pdmItem.BlockID INNER JOIN
// dbo.pdmTabField ON dbo.pdmItem.FieldID = dbo.pdmTabField.FieldID INNER JOIN
// dbo.pdmTab ON dbo.pdmTabField.TabID = dbo.pdmTab.TabID
// INNER JOIN pdmDWRequireTabAndGrid on pdmDWRequireTabAndGrid.GridID = dbo.pdmBlockSubItem.GridID and pdmDWRequireTabAndGrid.TabID=dbo.pdmTab.TabID
// WHERE (dbo.pdmBlockSubItem.GridID IS NOT NULL) and dbo.pdmTab.ProductCopyTabRootTabID is null
// AND ( dbo.pdmBlockSubItem.BlockID IN ({0}))", blockids);
string processGrid = string.Format(@"
SELECT DISTINCT pdmBlockSubItem.BlockID, pdmBlockSubItem.GridID, PdmTabBlock.TabID
FROM pdmDWRequireTabAndGrid INNER JOIN
pdmBlockSubItem ON pdmDWRequireTabAndGrid.GridID = pdmBlockSubItem.GridID INNER JOIN
PdmTabBlock ON pdmBlockSubItem.BlockID = PdmTabBlock.BlockID AND pdmDWRequireTabAndGrid.TabID = PdmTabBlock.TabID
WHERE (dbo.pdmBlockSubItem.GridID IS NOT NULL) AND ( dbo.pdmBlockSubItem.BlockID IN ({0}))", blockids);
SqlCommand cmd = new SqlCommand(processGrid, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
System.Data.DataTable resultTabel = new DataTable();
adapter.Fill(resultTabel);
foreach (DataRow aRow in resultTabel.Rows)
{
//dbo.pdmTab.TabID, dbo.pdmTab.ProductCopyTabRootTabID, dbo.pdmTab.ProductReferenceID
SqlInt32 aGridID = Converter.ToDDLSqlInt32(aRow["GridID"]);
SqlInt32 aTabID = Converter.ToDDLSqlInt32(aRow["TabID"]);
SqlInt32 aBlockID = Converter.ToDDLSqlInt32(aRow["BlockID"]);
string gridTablename = string.Empty;
if (PLMSDWStoredProcedures.GridTableName.ContainsKey(aGridID.Value))
{
gridTablename = PLMSDWStoredProcedures.GridTableName[aGridID.Value];
CLROutput.OutputDebug("GridTableName=" + gridTablename, conn);
}
if (gridTablename != string.Empty)
{
//TabID,ProductReferenceID, GridID
string deleteOLDValue = string.Format(" delete {0} WHERE TabID={1} and ProductReferenceID in ({2}) and GridID={3} and BlockID = {4} ", gridTablename, aTabID, refids, aGridID, aBlockID);
try
{
SqlCommand deletcmd = new SqlCommand(deleteOLDValue, conn);
deletcmd.ExecuteNonQuery();
}
catch
{
CLROutput.OutputDebug("deleteGridFieldOLDValue=" + deleteOLDValue, conn);
}
string replaceWHERE = string.Format(" where TabID={0} and ProductReferenceID in ({1}) and BlockID = {2} and ", aTabID, refids, aBlockID);
string insertIntoGridTable = string.Empty;
string getRootGridSelectQuery = @"select top 1 RootlevelselectSQLscript from PLM_Dw_tabgridscript where gridid=" + aGridID + " and tabid is null";
SqlCommand cmdRootGrid = new SqlCommand(getRootGridSelectQuery, conn);
string rootLevelquery = cmdRootGrid.ExecuteScalar() as string;
//WHERE Gridid=7 group by TabID ,ProductReferenceID ,BlockID,GridID,RowID,RowValueGUID,Sort
insertIntoGridTable = " insert into " + gridTablename + " " + rootLevelquery.Replace("WHERE", replaceWHERE);
try
{
// SqlContext.Pipe.Send("exception insertcmd=" + insertIntoGridTable);
SqlCommand insertcmd = new SqlCommand(insertIntoGridTable, conn);
insertcmd.ExecuteNonQuery();
CLROutput.OutputDebug("insertIntoGridTable=" + insertIntoGridTable, conn);
}
catch (Exception ex)
{
// SqlContext.Pipe.Send("exception insertcmd=" + insertIntoGridTable);
CLROutput.OutputDebug("exception insertcmd failed=" + insertIntoGridTable + ex.ToString(), conn);
}
}
}
}
#endregion
}
public static void DoSynchronizeUserDefineTableToDW(int entityID)
{
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
string tabQuery = @"select RootlevelselectSQLscript from " + PLMConstantString.PLM_DW_TabGridScripContainerTable + " where EntityID=" + entityID + " and IsPassValidation =1";
SqlCommand cmdQueryTabNameAndSelectRootlevel = new SqlCommand(tabQuery, conn);
string sQLTabLevelSelect = cmdQueryTabNameAndSelectRootlevel.ExecuteScalar() as string;
string tabTablename = PLMSDWStoredProcedures.EntityTableName[entityID];
if (tabTablename != string.Empty)
{
string deleteOLDValue = string.Format(" delete {0} ", tabTablename);
SqlCommand deletcmd = new SqlCommand(deleteOLDValue, conn);
CLROutput.OutputDebug("before delete Entity OLDValue=" + deleteOLDValue, conn);
try
{
deletcmd.ExecuteNonQuery();
CLROutput.OutputDebug("after delete ok deleteOLDValue=" + deleteOLDValue, conn);
}
catch (Exception ex)
{
CLROutput.OutputDebug("delete failed deleteOLDValue=" + deleteOLDValue + ex.ToString(), conn);
}
string inserIntoSelect = " insert into " + tabTablename + " " + sQLTabLevelSelect;
CLROutput.OutputDebug("before inserIntoSelect=" + inserIntoSelect, conn);
try
{
SqlCommand insertcmd = new SqlCommand(inserIntoSelect, conn);
insertcmd.ExecuteNonQuery();
CLROutput.OutputDebug("after insert ok inserIntoSelect=" + inserIntoSelect, conn);
}
catch (Exception ex)
{
CLROutput.OutputDebug("exception insertcmd failed=" + inserIntoSelect + ex.ToString(), conn);
}
}
}
}
public static void GetGradingSizeValue(int tabId, int currentGridBlockId, int referenceId)
{
//if (string.IsNullOrEmpty(referenceId))
// return;
PdmBlockClrDto dmBlockClrDto = PdmCacheManager.DictBlockCache[currentGridBlockId];
if (dmBlockClrDto.BlockPdmGridDto == null)
{
return;
}
bool IsGetAliasname = true;
DataTable gridcolumnResultDataTable = PLMSGetGridValueHeler.LoadVariousGridColumnValue(tabId, currentGridBlockId, referenceId.ToString(), true, dmBlockClrDto, false, true, IsGetAliasname);
int baseSizeIndexFromZero = 0;
int totalSizeNumber = 0;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
//SqlContext.
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
PLMSpecGradingHelper.GetSpecSizeRunTable(referenceId, conn, out baseSizeIndexFromZero, out totalSizeNumber);
CLROutput.OutputDebug("baseSizeIndexFromZero" + baseSizeIndexFromZero.ToString() + "totalSizeNumber" + totalSizeNumber.ToString());
DataAcessHelper.ExecuteReadCommmited(conn);
}
PdmGridClrDto aPdmGridClrDto = dmBlockClrDto.BlockPdmGridDto;
int baseSizeColumnId = aPdmGridClrDto[GridRegister.GridSpecGrading.GradingBaseSize].GridColumnId;
List <DataColumn> gradingSizeNameList = new List <DataColumn>();
DataColumn baseSizeColumn = null;
foreach (DataColumn column in gridcolumnResultDataTable.Columns)
{
if (column.ColumnName.StartsWith(GridRegister.GridSpecGrading.GradingSize))
{
gradingSizeNameList.Add(column);
}
if (column.ColumnName.EndsWith("_" + baseSizeColumnId))
{
baseSizeColumn = column;
}
}
//GridRegister.GridSpecGrading.
foreach (DataRow dataRow in gridcolumnResultDataTable.Rows)
{
List <double> gradingValue = new List <double>();
List <String> columnName = new List <string>();
double basesize = ControlTypeValueConverter.ConvertValueToDoubleWithDefautZero(dataRow[baseSizeColumn.ColumnName]);
for (int i = 1; i <= totalSizeNumber; i++)
{
foreach (DataColumn sizecolumn in gradingSizeNameList)
{
string baseSizeColumnName = GridRegister.GridSpecGrading.GradingSize + i.ToString() + "_";
if (sizecolumn.ColumnName.StartsWith(baseSizeColumnName))
{
double value = ControlTypeValueConverter.ConvertValueToDoubleWithDefautZero(dataRow[sizecolumn.ColumnName]);
gradingValue.Add(value);
columnName.Add(sizecolumn.ColumnName);
}
}
}
List <double> newValueList = PLMSpecGradingHelper.CaculateSizeValueWithGradingValue(gradingValue, basesize, baseSizeIndexFromZero);
for (int i = 0; i < totalSizeNumber; i++)
{
string columnNmae = columnName[i];
string value = newValueList[i].ToString();
dataRow[columnNmae] = value;
}
}
List <DataColumn> needConvertColumn = new List <DataColumn>();
foreach (DataColumn column in gridcolumnResultDataTable.Columns)
{
int baseSizeId = aPdmGridClrDto[GridRegister.GridSpecGrading.GradingBaseSize].GridColumnId;
if (column.ColumnName.EndsWith("_" + baseSizeColumnId))
{
needConvertColumn.Add(column);
}
int tolColumnId = aPdmGridClrDto[GridRegister.GridSpecGrading.Tolerance].GridColumnId;
if (column.ColumnName.EndsWith("_" + tolColumnId))
{
needConvertColumn.Add(column);
}
for (int i = 1; i <= 20; i++)
{
int sizeColumnIdId = aPdmGridClrDto[GridRegister.GridSpecGrading.GradingSize + i.ToString()].GridColumnId;
if (column.ColumnName.EndsWith("_" + sizeColumnIdId))
{
needConvertColumn.Add(column);
}
}
}
// need to get the bock size and unit of mesaure;
//DefaultPOMUnitOfMeasure 1:INch, 2: Cm
int?pomUnitOfmeasure = GetReferencePomOfUnitMeasure(referenceId);
if (pomUnitOfmeasure.HasValue && pomUnitOfmeasure.Value == 1)
{
ConvertCMDatatableToInch(gridcolumnResultDataTable, needConvertColumn);
}
else // NO VALUE, NEED TO CHECK DEFAULT VALUE
{
if (PLMConstantString.DictPdmSetup["DefaultPOMUnitOfMeasure"].SetupValue == "1")
{
ConvertCMDatatableToInch(gridcolumnResultDataTable, needConvertColumn);
}
}
CLROutput.SendDataTable(gridcolumnResultDataTable);
}
internal static void SetDepdentMasterEntityColumn(PdmGridClrDto aPdmGridEntity, DataTable toReturnDcuTable)
{
//var dictSysDefineMasterEntityColumnIdEntityIds = aPdmGridEntity.MasterEntityColumn.Where(col => col.EntityId.HasValue && col.EntityId.Value < 3000).ToDictionary(col => col.GridColumnId, col => col.EntityId.Value);
Dictionary <int, int> dictUserDefineMasterEntityColumnIdEntityIds = new Dictionary <int, int>();
foreach (var column in aPdmGridEntity.MasterEntityColumn)
{
if (column.EntityId.HasValue)
{
int entityId = column.EntityId.Value;
int masterColumnId = column.GridColumnId;
PdmEntityBlClrDto aPdmEntityBlClrDto = PdmCacheManager.DictPdmEntityBlEntity[entityId];
if (aPdmEntityBlClrDto.EntityType == (int)EmEntityType.SystemDefineTable)
{
// int entityId = columnIdEntity.Value;
List <string> columnNames = new List <string>();
if (aPdmGridEntity.MasterEntityDepdentColumn.ContainsKey(masterColumnId))
{
columnNames = aPdmGridEntity.MasterEntityDepdentColumn[masterColumnId].Where(o => !string.IsNullOrEmpty(o.InternalCode)).Select(o => o.InternalCode).ToList();
DataTable aDtResult = PLMSEntityClrBL.GetMutipleSysDefineEntityRowValue(entityId, columnNames);
ProcessSystemDefineEntityColumn(aPdmGridEntity, toReturnDcuTable, aDtResult, masterColumnId);
}
}
else if (aPdmEntityBlClrDto.EntityType == (int)EmEntityType.UserDefineTable)
{
dictUserDefineMasterEntityColumnIdEntityIds.Add(masterColumnId, entityId);
}
}
}
List <int> userDefineColumnIds = new List <int>();
foreach (var columnIdEntity in dictUserDefineMasterEntityColumnIdEntityIds)
{
int masterColumnId = columnIdEntity.Key;
int entityId = columnIdEntity.Value;
var pdmUserDefineEntity = PdmCacheManager.DictPdmEntityBlEntity[entityId];
if (aPdmGridEntity.MasterEntityDepdentColumn.ContainsKey(masterColumnId))
{
// if (aDtcolumn.UserDefineEntityColumnId.ToString() == dependentEntityColumn.InternalCode)
var columnids = aPdmGridEntity.MasterEntityDepdentColumn[masterColumnId]
.Where(o => !string.IsNullOrEmpty(o.InternalCode))
.Select(o => int.Parse(o.InternalCode.Trim()));
userDefineColumnIds.AddRange(columnids);
}
}
if (userDefineColumnIds.Count > 0)
{
// Key: EntityId, vlaue
Dictionary <int, List <SimpleUserDefineEntityRow> > result = PLMSEntityClrBL.GetDictEntityUserDefineRows(dictUserDefineMasterEntityColumnIdEntityIds.Values.ToList(), userDefineColumnIds);
foreach (var columnIdEntity in dictUserDefineMasterEntityColumnIdEntityIds)
{
int masterColumnId = columnIdEntity.Key;
int entityId = columnIdEntity.Value;
if (result.ContainsKey(entityId))
{
List <SimpleUserDefineEntityRow> listRow = result[entityId];
CLROutput.OutputDebug("masterColumnId =" + masterColumnId);
ProcessUserDefineEntityColumn(aPdmGridEntity, toReturnDcuTable, listRow, masterColumnId);
}
}
}
}
static PLMConstantString()
{
// Initianize context FROM PLM App
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
;
//PLMAPPDBConnection
string aqueryDwConn = " SELECT SetupValue FROM pdmsetup WHERE setupCode='PLMDWDBConnection'";
System.Data.DataTable resultTabelPLMDWDB = DataAcessHelper.GetDataTableQueryResult(conn, aqueryDwConn);
if (resultTabelPLMDWDB.Rows.Count > 0)
{
PLM_DW_ConnectionString = resultTabelPLMDWDB.Rows[0]["SetupValue"].ToString();
}
CLROutput.OutputDebug("PLM_DW_ConnectionString=" + PLM_DW_ConnectionString);
//PLMAPPDBConnection
string aqueryAppConn = " SELECT SetupValue FROM pdmsetup WHERE setupCode='PLMAPPDBConnection'";
System.Data.DataTable resultTabelPLMAPPDB = DataAcessHelper.GetDataTableQueryResult(conn, aqueryAppConn);
if (resultTabelPLMAPPDB.Rows.Count > 0)
{
PLM_APP_ConnectionString = resultTabelPLMAPPDB.Rows[0]["SetupValue"].ToString();
}
CLROutput.OutputDebug("PLM_APP_ConnectionString=" + PLM_APP_ConnectionString);
//Swap_DBConnectin string
var regex = new Regex("Catalog=" + conn.Database, RegexOptions.IgnoreCase);
PLM_DW_SchemeTemDBMSConnectionString = regex.Replace(PLM_APP_ConnectionString, "Catalog=" + PLM_DW_SchemeTemDBMS);
// PLM_DW_SwapDBFilePathConn
string PLM_DW_SwapDBFilePathConn = " SELECT SetupValue FROM pdmsetup WHERE setupCode='PLMDWSwapDBFilePath'";
System.Data.DataTable resultTabelPLMDWSwapDBFilePath = DataAcessHelper.GetDataTableQueryResult(conn, PLM_DW_SwapDBFilePathConn);
if (resultTabelPLMDWSwapDBFilePath.Rows.Count > 0)
{
PLM_DW_SwapDBFilePath = resultTabelPLMDWSwapDBFilePath.Rows[0]["SetupValue"].ToString();
}
CLROutput.OutputDebug("PLM_DW_SwapDBFilePath=" + PLM_DW_SwapDBFilePath);
////Ex-chagne-dB
string exChangeDBConn = " SELECT SetupValue FROM pdmsetup WHERE setupCode='PLMExChangeDatabaseConnection'";
System.Data.DataTable resultTabelexChangeDBConn = DataAcessHelper.GetDataTableQueryResult(conn, exChangeDBConn);
if (resultTabelexChangeDBConn.Rows.Count > 0)
{
PLM_ExChangeDatabase_ConnectionString = resultTabelexChangeDBConn.Rows[0]["SetupValue"].ToString();
}
CLROutput.OutputDebug("PLM_ExChangeDatabase_ConnectionString=" + PLM_ExChangeDatabase_ConnectionString);
//
DictPdmSetup = PdmSetupDal.GetAllList(conn).Where(o => !string.IsNullOrEmpty(o.SetupCode)).ToDictionary(o => o.SetupCode, o => o);
}
}
private static Dictionary <int, List <LookupItemDto> > LoadSysDefineLookupItemFromDabaBase(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 = PLMSEntityClrBL.GetSysDefineQueryIDAndDisplay(aPdmEntityDto);
// CLROutput.OutputDebug("aPdmEntityDto.SysTableName systemQueryTable" + aPdmEntityDto.EntityId + "=" + systemQueryTable);
if (string.IsNullOrEmpty(systemQueryTable))
{
continue;
}
// CLROutput.OutputDebug("aPdmEntityDto.SysTableName" + aPdmEntityDto.EntityId + "=" + aPdmEntityDto.EntityCode + aPdmEntityDto.SysTableName);
if (!String.IsNullOrEmpty(aPdmEntityDto.SysTableName))
{
if (!dictTableNameAndQuery.ContainsKey(aPdmEntityDto.SysTableName))
{
dictTableNameAndQuery.Add(aPdmEntityDto.SysTableName, systemQueryTable);
}
}
}
foreach (string tablname in dictTableNameAndQuery.Keys)
{
string queryTAble = dictTableNameAndQuery[tablname];
List <LookupItemDto> listDto = GetOneSystemDefinTableLookupItems(conn, queryTAble);
foreach (PdmEntityBlClrDto SysEntityDto in sysDefineEntityList)
{
string sysTableName = SysEntityDto.SysTableName;
// CLROutput.SendDataTable(datatable);
if (sysTableName == tablname)
{
toReturn.Add(SysEntityDto.EntityId, listDto);
// CLROutput.OutputDebug("before EntityCode " + SysEntityDto.EntityCode + " tableName="+ sysTableName + "=" + " lastScaltime=" );
//Important
//Important
string lastScaltime = GetLastChangeCheckSum(conn, tablname);
//F**k conn.ConnectionString will retur no password !!!!
// SysEntityDto.ConnectInfo = conn.ConnectionString;
CLROutput.OutputDebug("SysEntityDto.ConnectInfo = conn.ConnectionString" + SysEntityDto.ConnectInfo);
SysEntityDto.LastScanCheckSum = lastScaltime;
CLROutput.OutputDebug(sysTableName + " lastScaltime :" + lastScaltime);
}
}
}
return(toReturn);
}
