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);
}
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);
}
}
} // 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
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);
}