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