/// <summary>
/// Gets multuple images with bad canvas.
/// </summary>
public static List<DigitalContentRow> GetMultipleImagesWithBadCanvas()
{
List<DigitalContentRow> rows = new List<DigitalContentRow>();
using (SqlConnection conn = s_db.OpenConnection())
{
s_db.ExecuteReader(
conn,
@"
CREATE TABLE #MaxSize(
Url VARCHAR(200),
OriginalId VARCHAR(100),
[Size] TINYINT)
INSERT INTO #MaxSize
SELECT
Url,
OriginalId,
CASE
WHEN RealWidthPercent > RealHeightPercent THEN RealWidthPercent
ELSE RealHeightPercent
END
FROM [Canvas]
WHERE RunCode = '2010-05-18'
SELECT
Url,
OriginalId
FROM #MaxSize
WHERE [Size] <= 50
ORDER BY [Size]
DROP TABLE #MaxSize
",
delegate(IDataRecord reader)
{
DigitalContentRow row = new DigitalContentRow(reader);
rows.Add(row);
});
}
return rows;
}
/// <summary>
/// Gets standard images.
/// </summary>
public static Dictionary<int, List<DigitalContentRow>> GetStandardImages(IEnumerable<int> skuIds)
{
Dictionary<int, List<DigitalContentRow>> map = new Dictionary<int, List<DigitalContentRow>>();
using (SqlConnection conn = s_db.OpenConnection())
{
s_db.ExecuteNonQuery(
conn,
@"
CREATE TABLE #Sku (
SkuId INT PRIMARY KEY)
");
s_db.ExecuteBulkCopy(
conn,
"#Sku",
s_db.CreateBulkTable(new HashSet<int>(skuIds), "SkuId"));
s_db.ExecuteReader(
conn,
@"
SELECT
DCL.sku_id AS SkuId,
DC.content_guid AS ContentUid,
DC.original_id AS OriginalId
FROM tpd_digital_content DC WITH(NOLOCK)
INNER JOIN tpd_digital_content_link DCL WITH(NOLOCK)
ON DCL.content_guid = DC.content_guid
INNER JOIN #Sku S WITH(NOLOCK)
ON S.SkuId = DCL.sku_id
WHERE
media_type_id = 1
ORDER BY DCL.sku_id
",
delegate(IDataRecord reader)
{
int skuId = (int)reader["SkuId"];
if (!map.ContainsKey(skuId))
map[skuId] = new List<DigitalContentRow>();
DigitalContentRow row = new DigitalContentRow(reader);
map[skuId].Add(row);
});
s_db.ExecuteNonQuery(
conn,
@"
DROP TABLE #Sku
");
}
return map;
}
/// <summary>
/// Gets all multiple images by category.
/// </summary>
public static List<DigitalContentRow> GetAllMultipleImages(string categoryCode)
{
List<DigitalContentRow> rows = new List<DigitalContentRow>();
using (SqlConnection conn = s_db.OpenConnection())
{
s_db.ExecuteReader(
conn,
@"
CREATE TABLE #Temp (
ContentUid UNIQUEIDENTIFIER,
ItemUid UNIQUEIDENTIFIER,
FileUid UNIQUEIDENTIFIER,
MetaValueId INT)
INSERT INTO #Temp
SELECT DISTINCT TOP 500
TDC.content_guid,
CAST(LEFT(TDC.original_id, 36) AS UNIQUEIDENTIFIER),
CAST(SUBSTRING(TDC.original_id, 38, 36) AS UNIQUEIDENTIFIER),
TDCML.meta_value_id
FROM [tpd_digital_content] TDC WITH(NOLOCK)
INNER JOIN [tpd_digital_content_link] TDCL WITH(NOLOCK)
ON TDCL.content_guid = TDC.content_guid
INNER JOIN [tpd_digital_content_meta_link] TDCML WITH(NOLOCK)
ON TDCML.content_guid = TDC.content_guid
INNER JOIN [tpd_sku] TS WITH(NOLOCK)
ON TS.sku_id = TDCL.sku_id
WHERE
TDC.media_type_id = 15
AND TDCML.meta_value_id IN (2683, 2689) -- 200 x 150 or 400 x 300
AND TDCL.priority BETWEEN 500000 AND 509999
AND TS.category_code = @categoryCode
SELECT DISTINCT
ItemUid,
FileUid,
(
SELECT TOP 1 ContentUid
FROM #Temp A
WHERE
A.ItemUid = T.ItemUid
AND A.FileUid = T.FileUid
ORDER BY MetaValueId DESC
) AS ContentUid
FROM #Temp T
ORDER BY ItemUid
DROP TABLE #Temp
",
delegate(IDataRecord reader)
{
DigitalContentRow row = new DigitalContentRow(reader);
rows.Add(row);
},
new SqlParameter("@categoryCode", categoryCode));
}
return rows;
}
/// <summary>
/// Gets multiple images.
/// </summary>
public static Dictionary<int, List<DigitalContentRow>> GetMultipleImages(IEnumerable<int> skuIds)
{
Dictionary<int, List<DigitalContentRow>> map = new Dictionary<int, List<DigitalContentRow>>();
using (SqlConnection conn = s_db.OpenConnection())
{
s_db.ExecuteNonQuery(
conn,
@"
CREATE TABLE #Sku (
SkuId INT PRIMARY KEY)
");
s_db.ExecuteBulkCopy(
conn,
"#Sku",
s_db.CreateBulkTable(new HashSet<int>(skuIds), "SkuId"));
s_db.ExecuteReader(
conn,
@"
SELECT
DCL.sku_id AS SkuId,
DC.content_guid AS ContentUid,
DC.original_id AS OriginalId
FROM tpd_digital_content DC WITH(NOLOCK)
-- Resolution: 200 x 150
INNER JOIN tpd_digital_content_meta_link DCML_Resolution WITH(NOLOCK)
ON DCML_Resolution.content_guid = DC.content_guid
AND DCML_Resolution.meta_value_id = 2683
-- Image Weight for ordering
INNER JOIN tpd_digital_content_meta_link DCML_Weight WITH(NOLOCK)
ON DCML_Weight.content_guid = DC.content_guid
INNER JOIN tpd_digital_content_meta_value_voc DCMVV_Weight WITH(NOLOCK)
ON DCMVV_Weight.meta_value_id = DCML_Weight.meta_value_id
INNER JOIN tpd_digital_content_meta_value DCMV_Weight WITH(NOLOCK)
ON DCMV_Weight.meta_value_id = DCML_Weight.meta_value_id
AND DCMV_Weight.meta_attribute_id = 7
INNER JOIN tpd_digital_content_link DCL WITH(NOLOCK)
ON DCL.content_guid = DC.content_guid
INNER JOIN #Sku S WITH(NOLOCK)
ON S.SkuId = DCL.sku_id
WHERE
media_type_id = 15
ORDER BY DCL.sku_id, DCMVV_Weight.meta_value_name
",
delegate(IDataRecord reader)
{
int skuId = (int)reader["SkuId"];
if (!map.ContainsKey(skuId))
map[skuId] = new List<DigitalContentRow>();
DigitalContentRow row = new DigitalContentRow(reader);
map[skuId].Add(row);
});
s_db.ExecuteNonQuery(
conn,
@"
DROP TABLE #Sku
");
}
return map;
}
