public static DataTable GetSpecGradingBlockSelectSizeRotateTable(int referenceId, SqlConnection conn) { Dictionary <string, int> dictSubitemInterlCodeAndId = SysDefinBlockHelper.GetDictInteralCodeSubItemId(conn, BlockRegister.SpecGradingBlock.SpecGradingBlockName); int sizeRunSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecGradingBlock.SpecFitSizeRun]; int selecdSizeRunSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecGradingBlock.SpecSelectedSize]; int SpecFitBaseSizeSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecGradingBlock.SpecFitBaseSize]; DataTable returnDataTble = GetSelectSizeDataTable(referenceId, conn, sizeRunSubitemId, selecdSizeRunSubitemId, SpecFitBaseSizeSubitemId); return(returnDataTble); }
// 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); }
public static DataTable GetSpecSizeRunTable(int referenceId, SqlConnection conn, out int baseSizeIndexFromZero, out int totalSizeNumber) { Dictionary <string, int> dictSubitemInterlCodeAndId = SysDefinBlockHelper.GetDictInteralCodeSubItemId(conn, BlockRegister.SpecGradingBlock.SpecGradingBlockName); int sizeRunSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecGradingBlock.SpecFitSizeRun]; int selecdSizeRunSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecGradingBlock.SpecSelectedSize]; int SpecFitBaseSizeSubitemId = dictSubitemInterlCodeAndId[BlockRegister.SpecGradingBlock.SpecFitBaseSize]; // Get ValueText string SubitemInClause = @" select SubItemID ,ValueText from PdmSearchSimpleDcuValue where (SubItemID = @sizeRunSubitemId or SubItemID=@selecdSizeRunSubitemId or SubItemID=@SpecFitBaseSizeSubitemId ) and ProductReferenceID =@ProductReferenceID "; SqlParameter paraSizeRunSubitemId = new SqlParameter("@sizeRunSubitemId", sizeRunSubitemId); SqlParameter pararselecdSizeRunSubitemId = new SqlParameter("@selecdSizeRunSubitemId", selecdSizeRunSubitemId); SqlParameter pararSpecFitBaseSizeSubitemId = new SqlParameter("@SpecFitBaseSizeSubitemId", SpecFitBaseSizeSubitemId); SqlParameter pararProductReferenceId = new SqlParameter("@ProductReferenceID", referenceId); SqlCommand cmd = new SqlCommand(SubitemInClause, conn); cmd.Parameters.Add(paraSizeRunSubitemId); cmd.Parameters.Add(pararselecdSizeRunSubitemId); cmd.Parameters.Add(pararSpecFitBaseSizeSubitemId); cmd.Parameters.Add(pararProductReferenceId); Dictionary <int, string> dictSubitemIdandValueText = DataAcessHelper.GetDataTableQueryResult(cmd).AsDataRowEnumerable().ToDictionary(row => (int)row["SubItemID"], row => row["ValueText"] as string); baseSizeIndexFromZero = 0; totalSizeNumber = 0; if (dictSubitemIdandValueText.Count == 0) { // need to get all size return(new DataTable()); } string baseSizeId = string.Empty; if (dictSubitemIdandValueText.ContainsKey(SpecFitBaseSizeSubitemId)) { baseSizeId = dictSubitemIdandValueText[SpecFitBaseSizeSubitemId]; } DataTable returnDataTble = new DataTable(); for (int i = 1; i <= 20; i++) { DataColumn column = new DataColumn("Size" + i.ToString(), typeof(string)); returnDataTble.Columns.Add(column); } DataRow aRow = returnDataTble.NewRow(); returnDataTble.Rows.Add(aRow); // Get Size run if (dictSubitemIdandValueText.Count > 0) { if (dictSubitemIdandValueText.ContainsKey(sizeRunSubitemId)) { string sizeRunId = dictSubitemIdandValueText[sizeRunSubitemId]; if (!string.IsNullOrEmpty(sizeRunId)) { int sizerunValueId = int.Parse(sizeRunId); DataTable sizeRotateTable = GetSizeRotateTableWithSizerunId(sizerunValueId); string selectsizeRunId = dictSubitemIdandValueText[selecdSizeRunSubitemId]; if (!string.IsNullOrEmpty(selectsizeRunId) && selectsizeRunId.Length > 0) { ProcessSelectSized(ref baseSizeIndexFromZero, ref totalSizeNumber, baseSizeId, aRow, sizeRotateTable, ref selectsizeRunId); } else // NO seelcted Size , it show all size run tble { int rowCount = 1; foreach (DataRow row in sizeRotateTable.Rows) { aRow["Size" + rowCount.ToString()] = row["SizeName"]; int rizeRotateId = (int)row["SizeRunRotateID"]; if (rizeRotateId.ToString() == baseSizeId) { baseSizeIndexFromZero = rowCount - 1; } rowCount++; } totalSizeNumber = sizeRotateTable.Rows.Count; } } } } return(returnDataTble); }
public static DataTable GetOneReferenceSpecQcSelectedSizeTable(int referenceId, SqlConnection conn, int mainQcTabId) { Dictionary <string, int> dictSubitemInterlCodeAndId = SysDefinBlockHelper.GetDictInteralCodeSubItemId(conn, BlockRegister.SpecQCBlock.SpecQCBlockName); // Get QC seelct Size // string productInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(allReferenceIds, "ProductReferenceID", true); string querySelectSize = @" select ProductReferenceID,SizeRunRotateID from PdmProductQcSize where TabID = " + mainQcTabId + " and ProductReferenceID= " + referenceId; 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()); DataTable selectedSizeRotateTable = GetSpecQCBlockSelectSizeRotateTable(referenceId, conn); // CLROutput.SendDataTable(selectedSizeRotateTable); // Create Return table DataTable returnDataTble = new DataTable(); DataColumn tabNamecolumn = new DataColumn("ProductReferenceID", typeof(int)); returnDataTble.Columns.Add(tabNamecolumn); for (int i = 1; i <= 20; i++) { DataColumn column = new DataColumn("Size" + i.ToString(), typeof(string)); returnDataTble.Columns.Add(column); } if (dictRefereneSelectSize.ContainsKey(referenceId)) { DataRow aRow = returnDataTble.NewRow(); aRow["ProductReferenceID"] = referenceId; List <int> selectedSizeRotatedIds = dictRefereneSelectSize[referenceId]; int rowCount = 1; foreach (DataRow row in selectedSizeRotateTable.Rows) { int rizeRotateId = (int)row["SizeRunRotateID"]; if (selectedSizeRotatedIds.Contains(rizeRotateId)) { aRow["Size" + rowCount.ToString()] = row["SizeName"]; } rowCount++; } returnDataTble.Rows.Add(aRow); } return(returnDataTble); }