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);
}
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);
}
}
public static void GetRichText(int richTectFileId)
{
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
DataAcessHelper.ExecuteReadUnCommmited(conn);
string query = @"select tblSketch.OriginalImage from tblSketch where SketchID =@richTectFileId";
List <SqlParameter> listPara = new List <SqlParameter>();
listPara.Add(new SqlParameter("@richTectFileId", richTectFileId));
//command.Parameters.Add("@Photo", SqlDbType.Image, photo.Length).Value = photo;
DataTable result = DataAcessHelper.GetDataTableQueryResult(conn, query, listPara);
if (result.Rows.Count > 0)
{
byte[] rowData = result.Rows[0]["OriginalImage"] as byte[];
if (rowData != null)
{
string str = System.Text.Encoding.Default.GetString(rowData);
// CLROutput.OutputDebug(str);
CLROutput.OutputDebug("------------------");
// string str2 = HtmlToPlainText.StripHTML(str);
DataTable datatable = new DataTable();
DataColumn column = new DataColumn("Text");
datatable.Columns.Add(column);
//column.DataType = typeof (String );
// column.MaxLength = 200;
var row = datatable.NewRow();
row["Text"] = str;
datatable.Rows.Add(row);
CLROutput.SendDataTable(datatable);
//LROutput.OutputDebug(str2);
}
}
DataAcessHelper.ExecuteReadCommmited(conn);
}
// GetGridValue(tabId, currentGridBlockId, referenceIds, false);
}
public static void 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);
}
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);
}
}
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);
}
private static void UpdateForeignKeyRelationFromERPToPLM(List <PdmEntityClrDto> plmImportEntityDtoList)
{
List <string> exchangeTableNameList = plmImportEntityDtoList.Select(o => o.EntityCode).ToList();
Dictionary <string, PdmEntityClrDto> dictAllImportEntity = plmImportEntityDtoList.ToDictionary(o => o.EntityCode.ToLowerInvariant(), o => o);
string queryForeignTable = @" SELECT distinct OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
PT.NAME FIELD_NAME,
OBJECT_NAME(REFERENCED_OBJECT_ID) REFTABLE_NAME,
FT.NAME REFFIELD_NAME
FROM SYS.FOREIGN_KEY_COLUMNS FKC
JOIN SYS.COLUMNS PT
ON FKC.PARENT_OBJECT_ID = PT.OBJECT_ID
AND FKC.PARENT_COLUMN_ID = PT.COLUMN_ID
JOIN SYS.COLUMNS FT
ON FKC.REFERENCED_OBJECT_ID = FT.OBJECT_ID
AND FKC.REFERENCED_COLUMN_ID = FT.COLUMN_ID ";
// WHERE (OBJECT_NAME(FKC.parent_object_id))";
string tableInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(exchangeTableNameList, "OBJECT_NAME(FKC.parent_object_id)", false);
queryForeignTable = queryForeignTable + " WHERE " + tableInClause;
DataTable foreignKeyTable = null;
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
foreignKeyTable = DataAcessHelper.GetDataTableQueryResult(conn, queryForeignTable);
CLROutput.SendDataTable(foreignKeyTable);
}
foreach (DataRow fkDataRow in foreignKeyTable.Rows)
{
string childEntityEntityName = (fkDataRow["TABLE_NAME"] as string).Trim().ToLowerInvariant();
string childFkColumnName = (fkDataRow["FIELD_NAME"] as string).Trim().ToLowerInvariant();
string masterEntityTableName = (fkDataRow["REFTABLE_NAME"] as string).Trim().ToLowerInvariant();;
string masterKeyName = (fkDataRow["REFFIELD_NAME"] as string).Trim().ToLowerInvariant();
// clear old relationship
int childEntityId = dictAllImportEntity[childEntityEntityName].EntityId;
int masterEntityId = dictAllImportEntity[masterEntityTableName].EntityId;
// clear old relationship
using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
{
conn.Open();
PdmEntityClrDto EntityWithFKEntityDto = dictAllImportEntity[childEntityEntityName];
// need to updat FK control Type
foreach (var column in EntityWithFKEntityDto.Columns)
{
if (column.ColumnName.ToLowerInvariant() == childFkColumnName.ToLowerInvariant())
{
string updatepdmUserDefineEntityColumn = string.Format(@"update pdmUserDefineEntityColumn
set UIControlType={0},
FKEntityID ={1}
where UserDefineEntityColumnID ={2}", (int)EmControlType.DDL, masterEntityId, column.UserDefineEntityColumnID);
SqlCommand cmdupdate = new SqlCommand(updatepdmUserDefineEntityColumn, conn);
CLROutput.Output("updatepdmUserDefineEntityColumn=" + updatepdmUserDefineEntityColumn);
cmdupdate.ExecuteNonQuery();
}
}
InsertEntityWithFKRelaship(EntityWithFKEntityDto, childFkColumnName, masterEntityTableName, conn);
}
}
}
public static void GetTabAndGridExternalColumnValue(int gridTabId, int currentGridBlockId, string referenceIds)
{
if (string.IsNullOrEmpty(referenceIds))
{
return;
}
PdmBlockClrDto dmBlockClrDto = PdmCacheManager.DictBlockCache[currentGridBlockId];
if (dmBlockClrDto.BlockPdmGridDto == null)
{
return;
}
bool IsGetAliasname = false;
DataTable tabFieldResultDataTable = GetTabDataTable(gridTabId, referenceIds, false, false);
// Get Grading Size will call PrepareGetGridDataTable and set
DataTable gridcolumnResultDataTable = PLMSGetGridValueHeler.LoadVariousGridColumnValue(gridTabId, currentGridBlockId, referenceIds, false, dmBlockClrDto, false, false, IsGetAliasname);
List <string> gridExternalMappingNameList = GetExternalNappingName(gridTabId, currentGridBlockId);
string firstReferencecolumn = gridcolumnResultDataTable.Columns[0].ColumnName;
gridExternalMappingNameList.Add(firstReferencecolumn);
List <string> allCoumns = new List <string>();
foreach (DataColumn column in gridcolumnResultDataTable.Columns)
{
allCoumns.Add(column.ColumnName);
}
var needTOremovecolumn = allCoumns.Except(gridExternalMappingNameList);
foreach (string needToRemove in needTOremovecolumn)
{
gridcolumnResultDataTable.Columns.Remove(needToRemove);
}
DataTable mergeTable = null;
if (gridcolumnResultDataTable.Rows.Count > 0)
{
mergeTable = DataTableUtility.Join(tabFieldResultDataTable, gridcolumnResultDataTable, tabFieldResultDataTable.Columns[0], gridcolumnResultDataTable.Columns[0]);
}
else
{
//mergeTable
foreach (DataColumn gridColumn in gridcolumnResultDataTable.Columns)
{
if (!tabFieldResultDataTable.Columns.Contains(gridColumn.ColumnName))
{
tabFieldResultDataTable.Columns.Add(gridColumn.ColumnName);
}
}
mergeTable = tabFieldResultDataTable;
}
CLROutput.SendDataTable(mergeTable);
}
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);
}
