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