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
internal static void SetupDataTableDDLLookupValue(List <PdmGridMetaColumnClrDto> columnList, DataTable roReturnDcuTable)
{
var allSubitemeEntityIds = columnList.Where(o => o.EntityId.HasValue).Select(o => o.EntityId.Value).ToList();
Dictionary <int, Dictionary <object, string> > dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds);
var dDLColumnDtoList = columnList.Where(o => o.ColumnTypeId == (int)EmControlType.DDL).ToList();
foreach (var aDdlColumnDto in dDLColumnDtoList)
{
int columnId = aDdlColumnDto.GridColumnId;
if (aDdlColumnDto.ColumnTypeId == (int)EmControlType.DDL && aDdlColumnDto.EntityId.HasValue)
{
int entityId = aDdlColumnDto.EntityId.Value;
if (dictKeyEntityDictKeyIdDisplayString.ContainsKey(entityId))
{
Dictionary <object, string> lookUpItem = dictKeyEntityDictKeyIdDisplayString[entityId];
foreach (DataRow row in roReturnDcuTable.Rows)
{
string stringColumnId = columnId.ToString();
// CLROutput.Output("stringColumnId=" + stringColumnId);
SqlInt32 lookupid = Converter.ToDDLSqlInt32(row[stringColumnId]);
if (!lookupid.IsNull)
{
int idvalue = lookupid.Value;
if (lookUpItem.ContainsKey(idvalue))
{
row[stringColumnId] = lookUpItem[idvalue];;
}
}
}
}
}
}
}
