package jp.agentec.abook.abv.bl.data.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jp.agentec.abook.abv.bl.acms.type.ContentSortingType;
import jp.agentec.abook.abv.bl.acms.type.DownloadStatusType;
import jp.agentec.abook.abv.bl.common.db.Cursor;
import jp.agentec.abook.abv.bl.common.db.SQLiteDatabase;
import jp.agentec.abook.abv.bl.common.log.Logger;
import jp.agentec.abook.abv.bl.data.SortDirection;
import jp.agentec.abook.abv.bl.dto.ContentDto;
import jp.agentec.abook.abv.bl.dto.MydataDto;
import jp.agentec.adf.util.DateTimeFormat;
import jp.agentec.adf.util.DateTimeUtil;
import jp.agentec.adf.util.StringUtil;


public class ContentDao extends AbstractDao {
	private static final String TAG = "ContentDao";

	/*package*/ ContentDao() {
	}

	/**
	 * カーソルから値を取りだしDTOを生成して返す。
	 *
	 * 注：使用しないカラムは事前にスキップすることでgetColumnIndexとその後の判定を避けられるが、実験したところあまり性能向上が見られないので
	 * 利便性とパフォーマンスを考慮すると、下記のロジックが妥当。
	 *
	 * @param cursor
	 * @return
	 */
	@Override
	public ContentDto convert(Cursor cursor) {
		ContentDto dto = new ContentDto();

		int column = cursor.getColumnIndex("content_id");
		if (column != -1) {
			dto.contentId = cursor.getLong(column);
		}
		column = cursor.getColumnIndex("content_name");
		if (column != -1) {
			dto.contentName = cursor.getString(column);
		}
		column = cursor.getColumnIndex("content_name_kana");
		if (column != -1) {
			dto.contentNameKana = cursor.getString(column);
		}
		column = cursor.getColumnIndex("content_detail");
		if (column != -1) {
			dto.contentDetail = cursor.getString(column);
		}
		column = cursor.getColumnIndex("orientation");
		if (column != -1) {
			dto.orientation = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("all_page_num");
		if (column != -1) {
			dto.allPageNum = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("detail_page_num");
		if (column != -1) {
			dto.detailPageNum = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("updated_flg");
		if (column != -1) {
			dto.updatedFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("downloaded_flg");
		if (column != -1) {
			dto.downloadedFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("downloading_flg");
		if (column != -1) {
			dto.downloadingFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("meta_version");
		if (column != -1) {
			dto.metaVersion = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("resource_version");
		if (column != -1) {
			dto.resourceVersion = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("delivery_start_date");
		if (column != -1) {
			dto.deliveryStartDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		column = cursor.getColumnIndex("delivery_end_date");
		if (column != -1) {
			dto.deliveryEndDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		column = cursor.getColumnIndex("favorite_flg");
		if (column != -1) {
			dto.favoriteFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("content_protected_flg");
		if (column != -1) {
			dto.contentProtectedFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("thumbnail_normal_path");
		if (column != -1) {
			dto.thumbnailNormalPath = cursor.getString(column);
		}
		column = cursor.getColumnIndex("thumbnail_big_path");
		if (column != -1) {
			dto.thumbnailBigPath = cursor.getString(column);
		}
		column = cursor.getColumnIndex("page_path");
		if (column != -1) {
			dto.pagePath = cursor.getString(column);
		}
		column = cursor.getColumnIndex("resource_path");
		if (column != -1) {
			dto.resourcePath = cursor.getString(column);
		}
		column = cursor.getColumnIndex("content_groups");
		if (column != -1) {
			dto.contentGroups = cursor.getString(column);
		}
		column = cursor.getColumnIndex("new_flg");
		if (column != -1) {
			dto.newFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("contract_content_id");
		if (column != -1) {
			dto.contractContentId = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("reading_date");
		if (column != -1) {
			dto.readingDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		column = cursor.getColumnIndex("last_viewed_page");
		if (column != -1) {
			dto.lastViewedPage = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("content_size");
		if (column != -1) {
			dto.contentSize = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("reading_count");
		if (column != -1) {
			dto.readingCount = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("content_type");
		if (column != -1) {
			dto.contentType = cursor.getString(column);
		}
		column = cursor.getColumnIndex("last_delivery_date");
		if (column != -1) {
			dto.lastDeliveryDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		column = cursor.getColumnIndex("content_alert_level");
		if (column != -1) {
			dto.alertMessageLevel= cursor.getInt(column);
		}
		column = cursor.getColumnIndex("content_alert_message");
		if (column != -1) {
			dto.alertMessage = cursor.getString(column);
		}
		column = cursor.getColumnIndex("disable_auto_dl");
		if (column != -1) {
			dto.disableAutoDl = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("product_id");
		if (column != -1) {
			dto.productId = cursor.getString(column);
		}
		column = cursor.getColumnIndex("price");
		if (column != -1) {
			dto.price = cursor.getString(column);
		}
		column = cursor.getColumnIndex("purchase_state");
		if (column != -1) {
			dto.purchaseState = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("purchase_token");
		if (column != -1) {
			dto.purchaseToken = cursor.getString(column);
		}
		column = cursor.getColumnIndex("delivery_type");
		if (column != -1) {
			dto.deliveryType = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("disable_swipe");
		if (column != -1) {
			dto.disableSwipe = toBool(cursor.getInt(column));
		}
		
		column = cursor.getColumnIndex("download_start_date");
		if (column != -1) {
			dto.downloadStartDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		column = cursor.getColumnIndex("download_progress");
		if (column != -1) {
			dto.downloadProgress = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("downloaded_bytes");
		if (column != -1) {
			dto.downloadedBytes = cursor.getLong(column);
		}
		column = cursor.getColumnIndex("download_end_date");
		if (column != -1) {
			dto.downloadEndDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		column = cursor.getColumnIndex("log_sended_flg");
		if (column != -1) {
			dto.logSendedFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("status");
		if (column != -1) {
			dto.status = cursor.getString(column);
		}

		column = cursor.getColumnIndex("unauthorized_content_flg");
		if (column != -1) {
			dto.isUnAuthorizedContent = toBool(cursor.getInt(column));
		}

		// ABookCheck用
		column = cursor.getColumnIndex("category_id");
		if (column != -1) {
			dto.categoryId = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("category_name");
		if (column != -1) {
			dto.categoryNames = cursor.getString(column);
		}

		column = cursor.getColumnIndex("common_content_flg");
		if (column != -1) {
			dto.commonContentFlg = toBool(cursor.getInt(column));
		}

		return dto;
	}

	protected MydataDto convertToMyData(Cursor cursor) {
		MydataDto dto = new MydataDto();
		int colnum = cursor.getColumnIndex("content_id");
		if (colnum != -1) {
			dto.contentId = cursor.getLong(colnum);
		}
		colnum = cursor.getColumnIndex("content_name");
		if (colnum != -1) {
			dto.contentName = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("content");
		if (colnum != -1) {
			dto.content = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("insert_date");
		if (colnum != -1) {
			dto.insertDate = DateTimeUtil.toDate(cursor.getString(colnum), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		colnum = cursor.getColumnIndex("page_num");
		if (colnum != -1) {
			dto.pageNum = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("item");
		if (colnum != -1) {
			dto.item = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("favorite_flg");
		if (colnum != -1) {
			dto.favoriteFlg = toBool(cursor.getInt(colnum));
		}
		colnum = cursor.getColumnIndex("memo_id");
		if (colnum != -1) {
			dto.memoId = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("reading_count");
		if (colnum != -1) {
			dto.readingCount = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("reading_date");
		if (colnum != -1) {
			dto.readingDate = DateTimeUtil.toDate(cursor.getString(colnum), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		return dto;
	}

	public List<ContentDto> getAllContents() {
		return rawQueryGetDtoList("select * from t_content order by delivery_start_date DESC", null, ContentDto.class); // detailも入れているがよいか
	}
	
	//全社グループのコンテンツを取得しない。
	public List<ContentDto> notGetAllContent() {
		return rawQueryGetDtoList("select content_id from t_content where content_id not in (select content_id from r_content_group where group_relation_id =(select group_relation_id From m_group where group_level = 0)) order by delivery_start_date DESC", null, ContentDto.class);
	}

	public boolean updateUnAuthorizedFlg(long contentId, boolean unAuthorized) {
		long count = update("update t_content set unauthorized_content_flg = ? where content_id= ?", new Object[]{unAuthorized, contentId});
		return (count > 0);
	}

	public ContentDto getContent(long contentId) {
		return rawQueryGetDto("select * from t_content where content_id=?", new String[]{"" + contentId}, ContentDto.class);
	}

	public boolean isExist(long contentId) {
		return getContent(contentId) != null;
	}

	public void insertContent(ContentDto dto) {
		insert("insert into t_content (content_id, content_name, content_name_kana, content_detail, all_page_num, orientation, updated_flg, downloaded_flg, downloading_flg, meta_version, resource_version, delivery_start_date, delivery_end_date, favorite_flg, content_protected_flg, thumbnail_normal_path, thumbnail_big_path, page_path, resource_path, content_groups, new_flg, contract_content_id, reading_date, last_viewed_page, content_size, reading_count, content_type, last_delivery_date, content_alert_level, content_alert_message, disable_auto_dl, product_id, delivery_type, disable_swipe, unauthorized_content_flg, common_content_flg) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", dto.getInsertValues());
	}

	public boolean updateContent(ContentDto dto, boolean initialize) {
		String[] keyValues = new String[]{""+ dto.contentId};

		if (initialize) {
			delete("r_content_category", "content_id=?", keyValues);
			delete("r_content_group", "content_id=?", keyValues);
			delete("t_content_tag", "content_id=?", keyValues);
		}

		long count = update("update t_content set content_name=?, content_name_kana=?, content_detail=?, all_page_num=?, orientation=?, updated_flg=?, downloaded_flg=?, downloading_flg=?, meta_version=?, resource_version=?, delivery_start_date=?, delivery_end_date=?, favorite_flg=?, content_protected_flg=?, thumbnail_normal_path=?, thumbnail_big_path=?, page_path=?, resource_path=?, content_groups=?, new_flg=?, contract_content_id=?,reading_date=?, last_viewed_page=?, content_size=?, reading_count=?, content_type=?, last_delivery_date=?, content_alert_level=?, content_alert_message=?, disable_auto_dl=?, product_id=?, delivery_type=?, disable_swipe=?, download_start_date=?, status=?, common_content_flg=? where content_id=?", dto.getUpdateValues());

		return count > 0;
	}


	public boolean updateContent(long contentId, int detailPageNumber, String pagePath) {
		//	newアイコンを表示しないように

		long count = update("update t_content set detail_page_num=?, page_path=?, new_flg=0 where content_id=?", new Object[]{detailPageNumber, pagePath, contentId});

		return count > 0;
	}

	public boolean updateContentNewFlg(long contentId, boolean newFlg) {
		long count = update("update t_content set new_flg=0 where content_id=?", new Object[]{contentId});
		return (count > 0);
	}

	public boolean updateContent(long contentId, DownloadStatusType status) {
        long count;
        switch (status) {
            case Canceled:
            case Failed:
//			count = update("update t_content set downloading_flg=0, update_date=CURRENT_TIMESTAMP where content_id=?", new Object[]{contentId});//update_dateがない
                count = update("update t_content set downloading_flg=0, download_progress=null, downloaded_bytes=null where content_id=?", new Object[]{contentId});
                break;
            case Succeeded:
                count = update("update t_content set downloaded_flg=1, downloading_flg=0 where content_id=?", new Object[]{contentId});
                break;
            case Waiting:
            case Downloading:
            case Paused:
            case AutoPaused:
            case Initializing:
            case None:
            default:
                count = update("update t_content set downloading_flg=1 where content_id=?", new Object[]{contentId});
                break;
        }

        if (count > 0) {
            count = update("update t_content_download_queue set status=? where content_id=?", new Object[]{status.type(), contentId});
        }

        return count > 0;
    }

	public boolean updateDownload(ContentDto dto) {
		long count = update("update t_content set downloading_flg=?, downloaded_flg=?, updated_flg=?, download_progress=?, download_start_date=?, download_end_date=?, downloaded_bytes=?, status=? where content_id=?",
				new Object[]{dto.downloadingFlg, dto.downloadedFlg, dto.updatedFlg, dto.downloadProgress, dto.downloadStartDate, dto.downloadEndDate, dto.downloadedBytes, dto.status, dto.contentId});
		return (count > 0);
	}

	public boolean updateLogSendFlg(ContentDto dto) {
		long count = update("update t_content set log_sended_flg=? where content_id=?", new Object[]{dto.logSendedFlg, dto.contentId});
		return (count > 0);
	}


	public boolean updateContentFavoriteFlg(long contentId, boolean favoriteFlg) {
		long count = update("update t_content set favorite_flg=? where content_id=?", new Object[]{favoriteFlg, contentId});
		return count > 0;
	}
	
	public boolean updateContentReadingDate(Date readingDate, long contenId) {
		long count = update("update t_content set reading_date=?, reading_count = (reading_count+1) where content_id=?", new Object[]{readingDate, contenId});
		return count > 0;
	}

	public boolean updateLastViewedPage(long contentId, int pageNumber) {
		long count = update("update t_content set last_viewed_page=? where content_id=?", new Object[]{pageNumber, contentId});
		return count > 0;
	}

	public boolean updateCommonContentFlg(long contentId, int commonContentFlg) {
		long count = update("update t_content set common_content_flg=? where content_id=?", new Object[]{commonContentFlg, contentId});
		return count > 0;
	}

	public boolean updateForAbkImport(long contentId, String resourcePath) {
		long count = update("update t_content set downloaded_flg=1, resource_path=? where content_id=?", new Object[]{resourcePath, contentId});
		return count > 0;
	}

	public boolean updateContentPrice(long contentId, String price) {
		long count = update("update t_content set price=? where content_id=?", new Object[]{price, contentId});
		return count > 0;
	}

	public boolean updatePurchaseState(long contentId, int purchaseState, String purchaseToken) {
		long count = update("update t_content set purchase_state=?, purchase_token=? where content_id=?", new Object[]{purchaseState, purchaseToken, contentId});
		return count > 0;
	}

	public boolean updatePurchaseStateBySku(String sku, int purchaseState, String purchaseToken) {
		long count = update("update t_content set purchase_state=?, purchase_token=? where product_id=?", new Object[]{purchaseState, purchaseToken, sku});
		return count > 0;
	}

	public void deleteContent(Long contentId, boolean physicalDelete) {
		String[] keyValues = new String[]{"" + contentId};

		try {
			beginTransaction();

			delete("t_content_download_queue", "content_id=?", keyValues);
			delete("t_content_resource", "content_id=?", keyValues);
			delete("t_content_bookmark", "content_id=?", keyValues);
			delete("t_content_memo", "content_id=?", keyValues);
			delete("t_content_marking", "content_id=?", keyValues);
			delete("t_content_tag", "content_id=?", keyValues);
			delete("t_content_page", "content_id=?", keyValues);
			delete("t_content_old_pdf", "content_id=?", keyValues);


			if (physicalDelete) {
				delete("r_content_category", "content_id=?", keyValues);
				delete("r_content_group", "content_id=?", keyValues);
				delete("r_operation_content", "content_id=?", keyValues);
				delete("t_content", "content_id=?", keyValues);
			} else {
				// 削除したコンテンツは自動ダウンロード禁止フラグをtrueにする
				String sql = "update t_content set updated_flg=0, downloaded_flg=0, downloading_flg=0, new_flg=0, reading_count=0, reading_date=null, disable_auto_dl=1," +
						" download_progress=null, download_start_date=null, download_end_date=null, downloaded_bytes=null, log_sended_flg=null where content_id=?";
				update(sql, keyValues);
			}

			commit();
            Logger.i(TAG, "[deleteContent]:contentId=" + contentId);
		} catch (Exception e) {
			rollback();
			Logger.e("deleteContent failed.", e);
			throw new RuntimeException(e);
		}
	}

	public void deleteContentLog(Long contentId) {
		String[] keyValues = new String[]{"" + contentId};
		try {
			beginTransaction();
			delete("l_content_page_reading_log", "content_id=?", keyValues);
			delete("l_content_object_log", "content_id=?", keyValues);
			delete("l_content_reading_log", "content_id=?", keyValues);
			commit();
		} catch (Exception e) {
			rollback();
			Logger.e("deleteContentLong failed.", e);
			throw new RuntimeException(e);
		}
	}

	public boolean getFavorite(long contentId) {
		int count = rawQueryGetInt("select COUNT(content_id) AS content_count from t_content where content_id=? AND favorite_flg=1", new String[]{""+ contentId});
		return count > 0;
	}

	public boolean exists(long contentId) {
		return rawQueryGetInt("select count(content_id) from t_content where content_id=?", new String[]{"" + contentId}) > 0;
	}

	public List<ContentDto> getDownloadedContents() {
		return rawQueryGetDtoList("select * from t_content where downloaded_flg=1", null, ContentDto.class);
	}

	public List<MydataDto> getAllMyDataList(ContentSortingType contentSortingType, SortDirection sortDirection, String[] contentTypes) {
		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT * ");
		sql.append("      , '' AS content ");
		sql.append("      , 'readingdate' item ");
		sql.append(" FROM t_content tc ");

		sql.append("WHERE reading_date is not null ");

		// コンテンツタイプ
		if (contentTypes != null && contentTypes.length > 0) {
			sql.append("    AND tc.content_type IN (");
			for (int i = 0; i < contentTypes.length; i++) {
				sql.append("\"");
				sql.append(contentTypes[i]);
				sql.append("\"");
				if (i != contentTypes.length - 1) {
					sql.append(",");
				}
			}
			// 下位互換のためPDF、空文字も入れておく
			sql.append(",\"PDF\",'') ");
		}

		if (contentSortingType != null) {
			switch (contentSortingType) {
			case ContentName:
				sql.append("  ORDER BY tc.content_name ");
				break;
			case ContentNameKana:
				sql.append("  ORDER BY tc.content_name_kana ");
				break;
			case DeliveryStartDate:
				sql.append("  ORDER BY tc.delivery_start_date ");
				break;
			case DownloadDate:
				sql.append("  ORDER BY tc.download_end_date ");
				break;
			case ReadingCount:
				sql.append("  ORDER BY tc.reading_count ");
				break;
			case ReadingDate:
				sql.append("  ORDER BY tc.reading_date ");
				break;
			case ContentSize:
				sql.append("  ORDER BY tc.content_size ");
				break;
			case ContentId:
				sql.append("  ORDER BY tc.contract_content_id ");
				break;
			default:
				sql.append("  ORDER BY tc.delivery_start_date ");
				sortDirection = SortDirection.DESC;
				break;
			}
		} else {
			sql.append("  ORDER BY tc.delivery_start_date ");
			sortDirection = SortDirection.DESC;
		}

		sql.append(StringUtil.Space);

		if (sortDirection != null) {
			sql.append(sortDirection.direction());
		} else {
			sql.append(SortDirection.DESC.direction());
		}

		sql.append(StringUtil.Space);

		Logger.v(TAG, "sql=%s", sql);

		return rawQueryGetMyDataDtoList(sql.toString(), null);

	}

	public List<MydataDto> getMyDataList(long contentId) {
		return rawQueryGetMyDataDtoList("select content_id, content_name, thumbnail_normal_path AS content, reading_date as insert_date, contract_content_id as page_num, reading_count, reading_date, 'readingdate' item from t_content tc where tc.reading_date is not null and tc.content_id = ? order by tc.content_name, tc.reading_date", new String[] { "" + contentId });
	}

	private List<MydataDto> rawQueryGetMyDataDtoList(String sql, String[] bindArgs) {
		SQLiteDatabase db = dbConn.getDatabase();
		Cursor cursor = null;
		try {
			List<MydataDto> list = new ArrayList<MydataDto>();
			if (Logger.isVerboseEnabled()) {
				Logger.v(TAG, "%s [%s]", sql, join(bindArgs));
			}
			cursor = db.rawQuery(sql, bindArgs);
			while (cursor.moveToNext()) {
				list.add(convertToMyData(cursor));
			}
			return list;
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}

	public List<MydataDto> getReadingContent() {
		List<MydataDto> list = rawQueryGetMyDataDtoList("select content_id, content_name, '' content, reading_date as insert_date, last_viewed_page as page_num, 'readingdate' item from t_content tc where tc.reading_date is not null order by tc.reading_date desc", null);
		return list;
	}

	public List<ContentDto> getUnfinishedDownloadAll() {
		String sql = String.format(" select * from t_content where downloaded_flg=0 and (status is null or status IN ('%s', '%s', '%s', '%s', '%s')) "
				, DownloadStatusType.Waiting.type(), DownloadStatusType.Downloading.type()
				, DownloadStatusType.Paused.type(), DownloadStatusType.Initializing.type(), DownloadStatusType.Failed.type());
		return rawQueryGetDtoList(sql, null, ContentDto.class);
	}
	
	public List<ContentDto> getDownloadWaiting(int maxCount) {
		String sql = String.format(" select * from t_content where downloaded_flg=0 and status IN ('%s', '%s') limit ? "
				, DownloadStatusType.Waiting.type(), DownloadStatusType.Downloading.type());
		return rawQueryGetDtoList(sql, new String[]{"" + maxCount}, ContentDto.class);
	}

	public List<ContentDto> getSendLog() {
		return rawQueryGetDtoList("select * from t_content where download_end_date IS NOT NULL AND (log_sended_flg = 0 OR log_sended_flg IS NULL)", null, ContentDto.class);
	}

	public List<ContentDto> getUpdateOrNotDownloadContent() {
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		sql.append("    * ");
		sql.append("from ");
		sql.append("    t_content ");
		sql.append("where ");
		sql.append("    ( ");
		sql.append("        updated_flg = 1 ");
		sql.append("    or  downloaded_flg = 0 ");
		sql.append("    ) ");

		return rawQueryGetDtoList(sql.toString(), null, ContentDto.class);
	}

	public List<ContentDto> getOperationRelatedContent(Long operationId) {

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("      mc.category_id, ");
		sql.append("      mc.category_name, ");
		sql.append("      tc.content_id, ");
		sql.append("      tc.contract_content_id, ");
		sql.append("      tc.content_name, ");
		sql.append("      tc.thumbnail_normal_path, ");
		sql.append("      tc.updated_flg, ");
		sql.append("      tc.downloaded_flg, ");
		sql.append("      tc.downloading_flg, ");
		sql.append("      tc.new_flg, ");
		sql.append("      tc.status, ");
		sql.append("      tc.last_delivery_date ");
		sql.append("   FROM t_content AS tc ");
		sql.append("   INNER JOIN r_operation_content AS rpc ");
		sql.append("    ON tc.content_id = rpc.content_id ");
		sql.append("   INNER JOIN r_content_category AS rcc ");
		sql.append("     ON rpc.content_id = rcc.content_id ");
		sql.append("   INNER JOIN m_category AS mc ");
		sql.append("     ON rcc.category_relation_id = mc.category_relation_id ");
		sql.append("   WHERE rpc.operation_id = ? ");
		sql.append("    AND rpc.operation_content_flg = 0");
		sql.append("   ORDER BY mc.category_id, tc.content_id DESC ");

		return rawQueryGetDtoList(sql.toString(), new String[]{"" + operationId}, ContentDto.class);
	}

	public boolean isOperationRelatedContent(long contentId, long operationId) {
		return rawQueryGetInt("select count(tc.content_id) from t_content as tc inner join r_operation_content as rpc on tc.content_id = rpc.content_id where rpc.operation_id = ? and rpc.operation_content_flg = 0 and tc.content_id = ?", new String[]{"" + operationId, "" + contentId}) > 0;
	}

	public List<ContentDto> getCommonContent() {

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("      mc.category_id, ");
        sql.append("      mc.category_name, ");
        sql.append("      tc.content_id, ");
        sql.append("      tc.contract_content_id, ");
        sql.append("      tc.content_name, ");
        sql.append("      tc.thumbnail_normal_path, ");
        sql.append("      tc.updated_flg, ");
        sql.append("      tc.downloaded_flg, ");
        sql.append("      tc.downloading_flg, ");
        sql.append("      tc.new_flg, ");
        sql.append("      tc.status, ");
        sql.append("      tc.last_delivery_date ");
        sql.append("   FROM t_content AS tc ");
        sql.append("   INNER JOIN r_content_category AS rcc ");
        sql.append("     ON tc.content_id = rcc.content_id ");
        sql.append("   INNER JOIN m_category AS mc ");
        sql.append("     ON rcc.category_relation_id = mc.category_relation_id ");
        sql.append("    WHERE tc.common_content_flg = 1");
        sql.append("   ORDER BY mc.category_name, mc.category_id, tc.content_id DESC ");

		return rawQueryGetDtoList(sql.toString(), null, ContentDto.class);
    }

	public boolean isCommonContent(long contentId) {
		return rawQueryGetInt("select count(content_id) from t_content where content_id=? and common_content_flg = 1", new String[]{"" + contentId}) > 0;
	}


}
