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