private static int?GetReferencePomOfUnitMeasure(int referenceId)
{
string query = @"select PomUnitOfMeasure from pdmProduct where ProductReferenceID =@ProductReferenceID";
List <SqlParameter> paraList = new List <SqlParameter> ();
paraList.Add(new SqlParameter("@ProductReferenceID", referenceId));
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
//SqlContext.
conn.Open();
object value = DataAcessHelper.RetriveSigleValue(conn, query, paraList);
return(ControlTypeValueConverter.ConvertValueToInt(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
//blockPdmGridDto, gridcolumnResultDataTable
public static void SetupBodypartName(PdmGridClrDto aPdmGridClrDto, DataTable gridcolumnResultDataTable)
{
if (string.IsNullOrEmpty(aPdmGridClrDto.InternalCode))
{
return;
}
// get all colunName
if (!(
aPdmGridClrDto.InternalCode == GridRegister.GridSpecGrading.SpecGradingGrid ||
aPdmGridClrDto.InternalCode == GridRegister.GridSpecFit.SpecFitGrid ||
aPdmGridClrDto.InternalCode == GridRegister.GridSpecQC.SpecQCGrid
))
{
return;
}
string PomSketchIDColumnName = "PomSketchID";
gridcolumnResultDataTable.Columns.Add(PomSketchIDColumnName);
DataColumn bodypartIdColumn = null;
DataColumn bodypartCodeColumn = null;
DataColumn bodypartNameColumn = null;
DataColumn bodypartDescColumn = null;
DataColumn bodypartHowToColumn = null;
foreach (DataColumn column in gridcolumnResultDataTable.Columns)
{
int idColumnId = aPdmGridClrDto[GridRegister.GridSpecFit.BodyPartDetailIDWDimDetailID].GridColumnId;
if (column.ColumnName.EndsWith("_" + idColumnId))
{
bodypartIdColumn = column;
}
int codeColumnId = aPdmGridClrDto[GridRegister.GridSpecFit.BodyPartCustomerCode].GridColumnId;
if (column.ColumnName.EndsWith("_" + codeColumnId))
{
bodypartCodeColumn = column;
}
int nameColumnId = aPdmGridClrDto[GridRegister.GridSpecFit.BodyPartName].GridColumnId;
if (column.ColumnName.EndsWith("_" + nameColumnId))
{
bodypartNameColumn = column;
}
int descColumnId = aPdmGridClrDto[GridRegister.GridSpecFit.BodyPartDesc].GridColumnId;
if (column.ColumnName.EndsWith("_" + descColumnId))
{
bodypartDescColumn = column;
}
int howtoColumnId = aPdmGridClrDto[GridRegister.GridSpecFit.HowToMeasure].GridColumnId;
if (column.ColumnName.EndsWith("_" + howtoColumnId))
{
bodypartHowToColumn = column;
}
}
// gridcolumnResultDataTable.AsDataRowEnumerable ().Select (o=>o
Dictionary <DataRow, int> dictRowAndBodypartIdForColumnResultDataTable = new Dictionary <DataRow, int>();
List <int> bodypartIds = new List <int> ();
foreach (DataRow aRow in gridcolumnResultDataTable.Rows)
{
int?bodyparid = ControlTypeValueConverter.ConvertValueToInt(aRow[bodypartIdColumn]);
if (bodyparid.HasValue)
{
bodypartIds.Add(bodyparid.Value);
dictRowAndBodypartIdForColumnResultDataTable.Add(aRow, bodyparid.Value);
}
}
if (bodypartIds.Count > 0)
{
string query = @" select BodyPartID , Code , BodyPartName ,Description ,MeasureInstruction,SketchID from pdmV2kBodyPart ";
string inclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(bodypartIds.Distinct(), "BodyPartID", false);
query = query + " where " + inclause;
DataTable bodypartDataTable = null;
using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
{
conn.Open();
bodypartDataTable = DataAcessHelper.GetDataTableQueryResult(conn, query);
}
Dictionary <int, DataRow> dictbodyRowAndId = new Dictionary <int, DataRow>();
foreach (DataRow aDataRow in bodypartDataTable.Rows)
{
dictbodyRowAndId.Add((int)aDataRow["BodyPartID"], aDataRow);
}
// EnablePomCodeSynchonizeWithProduct
//EnablePomNameSynchonizeWithProduct
//EnablePomDescriptionSynchonizeWithProduct
//EnablePomMeasureInstrcutionSynchonizeWithProduct
bool isEnableCode = PLMConstantString.DictPdmSetup["EnablePomCodeSynchonizeWithProduct"].SetupValue.ToLower() == "true";
bool isEnableName = PLMConstantString.DictPdmSetup["EnablePomNameSynchonizeWithProduct"].SetupValue.ToLower() == "true";
bool isEnableDesc = PLMConstantString.DictPdmSetup["EnablePomDescriptionSynchonizeWithProduct"].SetupValue.ToLower() == "true";
bool isEnableMeasure = PLMConstantString.DictPdmSetup["EnablePomMeasureInstrcutionSynchonizeWithProduct"].SetupValue.ToLower() == "true";
foreach (DataRow gridRow in dictRowAndBodypartIdForColumnResultDataTable.Keys)
{
int bodyId = dictRowAndBodypartIdForColumnResultDataTable[gridRow];
if (dictbodyRowAndId.ContainsKey(bodyId))
{
DataRow bodyparRow = dictbodyRowAndId[bodyId];
if (isEnableCode)
{
gridRow[bodypartCodeColumn] = bodyparRow["Code"] as string;
}
if (isEnableName)
{
gridRow[bodypartNameColumn] = bodyparRow["BodyPartName"] as string;
}
if (isEnableDesc)
{
gridRow[bodypartDescColumn] = bodyparRow["Description"] as string;
}
if (isEnableMeasure)
{
gridRow[bodypartHowToColumn] = bodyparRow["MeasureInstruction"] as string;
}
gridRow[PomSketchIDColumnName] = bodyparRow["SketchID"] as int?;
}
}
}
}
// will return base size
//GetSpecQcSizeRunTable
public static DataTable GetSpecQcSelectedSizeForAllCopyTabsTable(int mainReferenceId, SqlConnection conn, int mainQcTabId)
{
Dictionary <string, int> dictSubitemInterlCodeAndId = SysDefinBlockHelper.GetDictInteralCodeSubItemId(conn, BlockRegister.SpecQCBlock.SpecQCBlockName);
int sizeRunSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecQCBlock.SpecFitSizeRun];
// int selecdSizeRunSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecQCBlock.SpecSelectedSize];
// int SpecFitBaseSizeSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecQCBlock.SpecFitBaseSize];
// Get MainTab SizeRunId
string searchSubitemValue = @" select ValueText from PdmSearchSimpleDcuValue where
( SubItemID =@sizeRunSubitemId and ProductReferenceID = @ProductReferenceID ) ";
SqlCommand cmd = new SqlCommand(searchSubitemValue, conn);
SqlParameter pararselecdSizeRunSubitemId = new SqlParameter("@sizeRunSubitemId", sizeRunSubitemId);
cmd.Parameters.Add(pararselecdSizeRunSubitemId);
SqlParameter paraMainReferenceId = new SqlParameter("@ProductReferenceID", mainReferenceId);
cmd.Parameters.Add(paraMainReferenceId);
DataTable mainTabSizeRun = DataAcessHelper.GetDataTableQueryResult(cmd);
if (mainTabSizeRun.Rows.Count == 0)
{
return(new DataTable());
}
int?sizeRunId = ControlTypeValueConverter.ConvertValueToInt(mainTabSizeRun.Rows[0]["ValueText"]);
if (!sizeRunId.HasValue)
{
return(new DataTable());
}
// Get Copy Tab
string queryCopyTab = @"SELECT CopyTabID, MainTabID, CopyTabReferenceID, Name, Description, Sort, MainReferenceID
FROM pdmCopyTabReference
WHERE (MainReferenceID = @MainReferenceID) AND (MainTabID =@MainTabID)";
List <SqlParameter> parameters = new List <SqlParameter> ();
parameters.Add(new SqlParameter("@MainReferenceID", mainReferenceId));
parameters.Add(new SqlParameter("@MainTabID", mainQcTabId));
DataTable copyTabTable = DataAcessHelper.GetDataTableQueryResult(conn, queryCopyTab, parameters);
List <int> allReferenceIds = new List <int>();
allReferenceIds.Add(mainReferenceId);
// refere copy tab mapping
Dictionary <int, string> dictReferenceTabName = new Dictionary <int, string>();
var pdmTabClrDto = PdmCacheManager.DictTabCache[mainQcTabId];
dictReferenceTabName.Add(mainReferenceId, pdmTabClrDto.TabName);
// foreach ( DataRow row in
foreach (DataRow row in copyTabTable.Rows)
{
int copyTabRefId = (int)row["CopyTabReferenceID"];
allReferenceIds.Add(copyTabRefId);
dictReferenceTabName.Add(copyTabRefId, row["Name"].ToString());
}
// Get QC seelct Size
string productInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(allReferenceIds, "ProductReferenceID", true);
string querySelectSize = @" select ProductReferenceID,SizeRunRotateID from PdmProductQcSize where TabID = " + mainQcTabId + productInclause;
DataTable selectSizeDataTable = DataAcessHelper.GetDataTableQueryResult(conn, querySelectSize);
// CLROutput.SendDataTable(selectSizeDataTable);
Dictionary <int, List <int> > dictRefereneSelectSize = selectSizeDataTable.AsDataRowEnumerable().GroupBy(o => (int)o["ProductReferenceID"]).ToDictionary(g => g.Key, g => g.Select(r => (int)r["SizeRunRotateID"]).ToList());
// get Size run table
// DataTable sizeRotateTable = GetSizeRotateTableWithSizerunId(conn, sizeRunId.Value );
DataTable sizeRotateTable = GetSpecGradingBlockSelectSizeRotateTable(mainReferenceId, conn);
// Create Return table
DataTable returnDataTble = new DataTable();
DataColumn tabNamecolumn = new DataColumn("TabName", typeof(string));
returnDataTble.Columns.Add(tabNamecolumn);
for (int i = 1; i <= 20; i++)
{
DataColumn column = new DataColumn("Size" + i.ToString(), typeof(string));
returnDataTble.Columns.Add(column);
}
foreach (int refId in dictReferenceTabName.Keys)
{
DataRow aRow = returnDataTble.NewRow();
aRow["TabName"] = dictReferenceTabName[refId];
if (dictRefereneSelectSize.ContainsKey(refId))
{
List <int> selectedSizeRotatedIds = dictRefereneSelectSize[refId];
int rowCount = 1;
foreach (DataRow row in sizeRotateTable.Rows)
{
int rizeRotateId = (int)row["SizeRunRotateID"];
if (selectedSizeRotatedIds.Contains(rizeRotateId))
{
aRow["Size" + rowCount.ToString()] = row["SizeName"];
}
rowCount++;
}
}
returnDataTble.Rows.Add(aRow);
}
return(returnDataTble);
}
