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);
}
}
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 SplitStringToMutipleColumn([SqlFacet(MaxSize = -1)] string referenceIds, int numberOfColumn)
{
DataTable datatable = new DataTable();
for (int i = 1; i <= numberOfColumn; i++)
{
datatable.Columns.Add("C" + i.ToString());
}
string [] referenceidList = referenceIds.Split(new char[] { ',' });
DataRow row = datatable.NewRow();
int columncount = 0;
for (int i = 0; i < referenceidList.Length; i++)
{
int remain = i % numberOfColumn;
if (remain == 0)
{
row = datatable.NewRow();
datatable.Rows.Add(row);
columncount = 0;
row[columncount] = referenceidList[i];
}
else
{
columncount++;
row[columncount] = referenceidList[i];
}
}
CLROutput.SendDataTable(datatable);
}
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);
}
}
}
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);
}
}
private static object InsertNewExportUserDefineEntity(string folderId, SqlConnection conn, string newEntityCode)
{
string sqlInsertPdmEntity = string.Format(@"
Insert into dbo.PdmEntity
(
EntityCode,
FolderID,
IsImport
) " +
@" values
(
'{0}',{1},{2}
)
", newEntityCode, folderId, 1);
using (SqlCommand insertNewentity = new SqlCommand(sqlInsertPdmEntity, conn))
{
CLROutput.Output("sqlInsertPdmEntity" + sqlInsertPdmEntity);
insertNewentity.ExecuteNonQuery();
}
string newEntityIdQuery = @"select EntityID from pdmEntity where entitycode='" + newEntityCode + "' and EntityID > 3000 ";
using (SqlCommand cmdGetNewEntityId = new SqlCommand(newEntityIdQuery, conn))
{
return(cmdGetNewEntityId.ExecuteScalar());;
}
}
//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);
}
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 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 GetTabValue(int tabId, string referenceIds, bool isShowLookupitem, bool IsSubItemFullPathName = true)
{
if (string.IsNullOrEmpty(referenceIds))
{
return;
}
System.Data.DataTable tabFieldResultDataTable = GetTabDataTable(tabId, referenceIds, isShowLookupitem, IsSubItemFullPathName);
CLROutput.SendDataTable(tabFieldResultDataTable);
}
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);
}
}
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)
{
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);
}
}
public static void GetMergeBlockPrintGrid(int tabId, int gridBlockId1, string block1RwValueFilter, int gridBlockId2, string block2RwValueFilter, string referenceIds)
{
if (gridBlockId2 == -1) // it is one block
{
DataTable gridBlock1 = PLMSGetGridValueHeler.GetOneGridBlockSelectRows(tabId, gridBlockId1, block1RwValueFilter, referenceIds, true, false);
CLROutput.SendDataTable(gridBlock1);
}
else // it is merger gridblcok
{
DataTable gridTable1 = PLMSGetGridValueHeler.GetOneGridBlockSelectRows(tabId, gridBlockId1, block1RwValueFilter, referenceIds, true, false);
DataTable gridTable2 = PLMSGetGridValueHeler.GetOneGridBlockSelectRows(tabId, gridBlockId2, block2RwValueFilter, referenceIds, true, false);;
if (gridTable2.Columns.Count == gridTable1.Columns.Count)
{
for (int i = 0; i < gridTable2.Columns.Count; i++)
{
gridTable2.Columns[i].ColumnName = gridTable1.Columns[i].ColumnName;
}
gridTable1.Merge(gridTable2);
}
// int count =1;
//foreach (DataRow row in gridTable1.Rows)
//{
// if (row.Table.Columns.Contains("Sort"))
// {
// row["Sort"] = count;
// count++;
// }
//}
CLROutput.SendDataTable(gridTable1);
}
}
private static object InsertNewEntityWithReturnNewIdentityId(SqlConnection conn, string sqlInsertpdmUserDefineEntityRow, SqlCommand cmdInsertNewentity)
{
cmdInsertNewentity.Connection = conn;
cmdInsertNewentity.CommandText = sqlInsertpdmUserDefineEntityRow;
CLROutput.Output("sqlInsertpdmUserDefineEntityRow" + sqlInsertpdmUserDefineEntityRow);
cmdInsertNewentity.ExecuteNonQuery();
cmdInsertNewentity.CommandText = "SELECT SCOPE_IDENTITY()";
object rowID = cmdInsertNewentity.ExecuteScalar();
CLROutput.Output("SELECT SCOPE_IDENTITY( ) RowId=" + rowID);
return(rowID);
}
private static void InsertPdmUserDefineColunmDto(PdmEntityColumnClrDto pdmEntityColumnClrDto, SqlConnection conn)
{
int bitPrimaryKey = 0;
if (pdmEntityColumnClrDto.IsPrimaryKey.HasValue && pdmEntityColumnClrDto.IsPrimaryKey.Value)
{
bitPrimaryKey = 1;
}
int NBDecimal = 0;
if (pdmEntityColumnClrDto.Nbdecimal.HasValue)
{
NBDecimal = pdmEntityColumnClrDto.Nbdecimal.Value;
}
string sqlInsertPdmEntityColumn = string.Format(@"
Insert into dbo.pdmUserDefineEntityColumn
(
EntityID,
ColumnName,
IsPrimaryKey,
UIControlType,
NBDecimal
) " +
@" values
(
{0},'{1}',{2},{3},{4}
)
", pdmEntityColumnClrDto.EntityId, pdmEntityColumnClrDto.ColumnName, bitPrimaryKey, pdmEntityColumnClrDto.UicontrolType, NBDecimal);
using (SqlCommand insertNewentity = new SqlCommand(sqlInsertPdmEntityColumn, conn))
{
CLROutput.Output("sqlInsertPdmEntity" + sqlInsertPdmEntityColumn);
insertNewentity.ExecuteNonQuery();
}
}
public static void GetGridExternalColumnValue(int gridTabId, int currentGridBlockId, string referenceIds)
{
if (string.IsNullOrEmpty(referenceIds))
{
return;
}
PdmBlockClrDto dmBlockClrDto = PdmCacheManager.DictBlockCache[currentGridBlockId];
if (dmBlockClrDto.BlockPdmGridDto == null)
{
return;
}
bool IsGetAliasname = false;
// Get Grading Size will call PrepareGetGridDataTable and set
DataTable gridcolumnResultDataTable = PLMSGetGridValueHeler.LoadVariousGridColumnValue(gridTabId, currentGridBlockId, referenceIds, false, dmBlockClrDto, false, false, IsGetAliasname);
CLROutput.SendDataTable(gridcolumnResultDataTable);
}
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;
}
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);
}
} // 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);
}
}
}
}