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

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

import jp.agentec.abook.abv.bl.acms.type.OperationSortingType;
import jp.agentec.abook.abv.bl.common.Constant;
import jp.agentec.abook.abv.bl.common.db.Cursor;
import jp.agentec.abook.abv.bl.common.log.Logger;
import jp.agentec.abook.abv.bl.dto.OperationDto;
import jp.agentec.adf.util.DateTimeFormat;
import jp.agentec.adf.util.DateTimeUtil;
import jp.agentec.adf.util.StringUtil;

/**
 * Created by leej on 2018/08/17.
 */

public class OperationDao extends AbstractDao {

    private static final String TAG = "OperationDao";

    @Override
    protected OperationDto convert(Cursor cursor) {
        OperationDto dto = new OperationDto();

        int column = cursor.getColumnIndex("operation_id");
        if (column != -1) {
            dto.operationId = cursor.getLong(column);
        }
        column = cursor.getColumnIndex("operation_name");
        if (column != -1) {
            dto.operationName = cursor.getString(column);
        }

        column = cursor.getColumnIndex("operation_descriptions");
        if (column != -1) {
            dto.operationDescriptions = cursor.getString(column);
        }
        column = cursor.getColumnIndex("operation_start_date");
        if (column != -1) {
            dto.operationStartDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMdd_hyphen);
        }
        column = cursor.getColumnIndex("operation_end_date");
        if (column != -1) {
            dto.operationEndDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMdd_hyphen);
        }
        column = cursor.getColumnIndex("operation_type");
        if (column != -1) {
            dto.operationType = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("last_edit_date");
        if (column != -1) {
            dto.lastEditDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
        }
        column = cursor.getColumnIndex("edit_lock_flg");
        if (column != -1) {
            dto.editLockFlg = toBool(cursor.getInt(column));
        }
        column = cursor.getColumnIndex("need_sync_flg");
        if (column != -1) {
            dto.needSyncFlg = toBool(cursor.getInt(column));
        }
        column = cursor.getColumnIndex("content_id");
        if (column != -1) {
            dto.contentId = cursor.getLong(column) == 0 ? null : cursor.getLong(column);
        }

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

        column = cursor.getColumnIndex("report_cycle");
        if (column != -1) {
            dto.reportCycle = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("enable_report_update");
        if (column != -1) {
            dto.enableReportUpdate = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("report_period");
        if (column != -1) {
            dto.reportPeriod = cursor.getString(column);
        }

        column = cursor.getColumnIndex("report_count");
        if (column != -1) {
            dto.reportCount = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("enable_report_history");
        if (column != -1) {
            dto.enableReportHistory = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("enable_report_edit");
        if (column != -1) {
            dto.enableReportEdit = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("report_type");
        if (column != -1) {
            dto.reportType = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("enable_add_report");
        if (column != -1) {
            dto.enableAddReport = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("quick_report");
        if (column != -1) {
            dto.quickReport = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("status_not_started_count");
        if (column != -1) {
            dto.statusNotStartedCount = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("status_working_count");
        if (column != -1) {
            dto.statusWorkingCount = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("status_completed_count");
        if (column != -1) {
            dto.statusCompletedCount = cursor.getInt(column);
        }

        column = cursor.getColumnIndex("operation_last_sync_date");
        if (column != -1) {
            dto.operationLastSyncDate = DateTimeUtil.toDate(cursor.getString(column), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
        }

        return dto;
    }

    public List<OperationDto> getAllOperation() {
        return rawQueryGetDtoList("select tp.*, rpc.content_id from t_operation AS tp left outer join r_operation_content AS rpc on tp.operation_id = rpc.operation_id", null, OperationDto.class);
    }

    public List<OperationDto> getLatestOperations() {
        return rawQueryGetDtoList("select * from t_operation where operation_start_date <= ? order by operation_start_date DESC " , new String[]{DateTimeUtil.toStringInTimeZone(DateTimeUtil.getCurrentDate(), DateTimeFormat.yyyyMMddHHmmss_hyphen, "GMT")}, OperationDto.class);
    }

    public OperationDto getOperation(long operationId) {
        StringBuffer sql = new StringBuffer();
        String[] args = new String[] { "" + operationId };
        sql.append("    SELECT * ");
        sql.append("    FROM t_operation ");
        sql.append("    WHERE operation_id = ?");
        return rawQueryGetDto(sql.toString(), args, OperationDto.class);
    }

    public OperationDto getOperationJoinContent(long operationId) {
        StringBuffer sql = new StringBuffer();
        String[] args = new String[] { "" + operationId };
        sql.append("    SELECT * ");
        sql.append("    FROM t_operation AS tp ");
        sql.append("    LEFT OUTER JOIN r_operation_content AS rpc ");
        sql.append("    ON tp.operation_id = rpc.operation_id ");
        sql.append("    AND rpc.operation_content_flg = 1 ");
        sql.append("    WHERE tp.operation_id = ?");
        return rawQueryGetDto(sql.toString(), args, OperationDto.class);
    }

    public OperationDto getOperationForContentId(long contentId) {
        StringBuffer sql = new StringBuffer();
        String[] args = new String[] { "" + contentId };
        sql.append("    SELECT * ");
        sql.append("    FROM t_operation AS tp ");
        sql.append("    LEFT OUTER JOIN r_operation_content AS rpc ");
        sql.append("    ON tp.operation_id = rpc.operation_id ");
        sql.append("    AND rpc.operation_content_flg = 1 ");
        sql.append("    WHERE tp.operation_id = ?");
        return rawQueryGetDto(sql.toString(), args, OperationDto.class);
    }

    public boolean updateNeedSyncFlg(long operationId, boolean needSyncFlg) {
        OperationDto dto = getOperationJoinContent(operationId);
        if (dto != null) {
            dto.needSyncFlg = needSyncFlg;
            return update(dto);
        }
        return false;
    }

    public void insert(OperationDto dto) {
        insert("insert into t_operation "
                        + "(operation_id, "
                        + "operation_name, "
                        + "operation_descriptions, "
                        + "operation_start_date, "
                        + "operation_end_date, "
                        + "operation_type, "
                        + "report_type, "
                        + "last_edit_date, "
                        + "content_creating_flg, "
                        + "edit_lock_flg, "
                        + "need_sync_flg, "
                        + "report_cycle, "
                        + "enable_report_update, "
                        + "enable_report_history, "
                        + "enable_report_edit,"
                        + "enable_add_report,"
                        + "status_not_started_count, "
                        + "status_working_count, "
                        + "status_completed_count, "
                        + "quick_report, "
                        + "operation_last_sync_date) "
                        + "values "
                        + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                dto.getInsertValues());
    }

    public boolean update(OperationDto dto) {
        long count = update("update t_operation "
                        + "set "
                        + "operation_name=?, "
                        + "operation_descriptions=?, "
                        + "operation_start_date=?, "
                        + "operation_end_date=?, "
                        + "operation_type=?, "
                        + "report_type=?, "
                        + "last_edit_date=?, "
                        + "content_creating_flg=?, "
                        + "edit_lock_flg=?, "
                        + "need_sync_flg=?, "
                        + "report_cycle=?, "
                        + "enable_report_update=?, "
                        + "enable_report_history=?, "
                        + "enable_report_edit=?, "
                        + "enable_add_report=?, "
                        + "status_not_started_count=?, "
                        + "status_working_count=?, "
                        + "status_completed_count=?, "
                        + "quick_report=?, "
                        + "operation_last_sync_date=? "
                        + "where operation_id=?",
                dto.getUpdateValues());
        return count > 0;
    }

    public void delete(OperationDto dto) {
        delete("r_task_worker_group", "operation_id=?", dto.getKeyValues());
        delete("r_operation_content", "operation_id=?", dto.getKeyValues());
        delete("t_operation", "operation_id=?", dto.getKeyValues());
    }

    public void deleteAll() {
        delete("t_push_message", null, null);
        delete("r_operation_content", null, null);
        delete("r_task_worker_group", null, null);
        delete("r_operation_group_master_relation", null, null);
        delete("t_operation", null, null);
        delete("t_task", null, null);
        delete("t_task_report", null, null);
        delete("t_task_report_send", null, null);
        delete("t_task_report_items", null, null);
        delete("m_worker_group", null, null);
    }

    /**
     * 作業一覧に表示する情報を取得（フィルターと検索キーワードで絞込）
     * @param searchOperationName ホーム画面入力した検索キーワード
     * @param searchStartDateStr 開始期間
     * @param searchEndDateStr 終了期間
     * @param reportTypeStr　点検 or 報告
     * @return 該当する作業一覧
     */
    public List<OperationDto> getOperations(String searchOperationName, String searchStartDateStr, String searchEndDateStr, String reportTypeStr) {
        String sql = generateGetOperationQuery(searchOperationName, searchStartDateStr, searchEndDateStr, reportTypeStr);
        return rawQueryGetDtoList(sql, null, OperationDto.class);
    }

    /**
     * SQL文を作成
     * @param searchOperationName ホーム画面入力した検索キーワード
     * @param searchStartDateStr 開始期間
     * @param searchEndDateStr 終了期間
     * @param reportTypeStr　点検 or 報告
     * @return SQL文
     */
    private String generateGetOperationQuery(String searchOperationName, String searchStartDateStr, String searchEndDateStr, String reportTypeStr) {
        String curDate = DateTimeUtil.toStringInTimeZone(new Date(), DateTimeFormat.yyyyMMddHHmmss_hyphen, "UTC");

        StringBuffer sql = new StringBuffer();

        sql.append("    SELECT top.operation_id, ");
        sql.append("        top.operation_name, ");
        sql.append("        top.operation_type, ");
        sql.append("        top.operation_descriptions, ");
        sql.append("        top.operation_start_date, ");
        sql.append("        top.operation_end_date, ");
        sql.append("        top.last_edit_date, ");
        sql.append("        top.edit_lock_flg, ");
        sql.append("        top.need_sync_flg, ");
        sql.append("        top.content_creating_flg, ");
        sql.append("        top.report_type, ");
        sql.append("        top.report_cycle, ");
        sql.append("        top.enable_report_update, ");
        sql.append("        top.enable_report_edit, ");
        sql.append("        top.enable_add_report, ");
        sql.append("        top.status_not_started_count, ");
        sql.append("        top.status_working_count, ");
        sql.append("        top.status_completed_count, ");
        sql.append("        top.operation_last_sync_date, ");
        sql.append("        CASE ");
        sql.append("            WHEN report_type = 1 THEN ( ");
        sql.append("                SELECT strftime('%Y/%m/%d %H:%M', datetime(ttr.report_start_date, 'localtime')) || ' ~ ' || strftime('%Y/%m/%d %H:%M', datetime(ttr.report_end_date, 'localtime')) ");
        sql.append("                FROM t_task tt ");
        sql.append("                    INNER JOIN t_task_report ttr ");
        sql.append("                    ON tt.task_key = ttr.task_key ");
        sql.append("                    AND tt.del_flg = 0 ");
        //sql.append("                    AND datetime(ttr.report_start_date) <= datetime('" + curDate + "') ");
        sql.append("                    AND datetime(ttr.report_end_date) >= datetime('" + curDate + "') ");
        sql.append("                WHERE tt.operation_id = top.operation_id ");
        sql.append("                ORDER BY ttr.report_start_date ASC LIMIT 1 ) ");
        sql.append("            ELSE '' ");
        sql.append("        END AS report_period, ");
        sql.append("        CASE ");
        sql.append("            WHEN report_type = 1 THEN ( ");
        sql.append("                SELECT count(*) ");
        sql.append("                FROM t_task_report ");
        sql.append("                WHERE task_key = (SELECT task_key FROM t_task WHERE operation_id = top.operation_id LIMIT 1) ) ");
        sql.append("            ELSE 0 ");
        sql.append("        END AS report_count, ");
        sql.append("        rpc.content_id ");
        sql.append("        FROM t_operation AS top ");
        sql.append("        LEFT OUTER JOIN r_operation_content AS rpc ");
        sql.append("        ON top.operation_id = rpc.operation_id ");
        sql.append("        AND rpc.operation_content_flg = 1 ");
        sql.append("        WHERE top.operation_id IS NOT NULL ");
        if (!StringUtil.isNullOrEmpty(searchOperationName)) {
            sql.append("    AND top.operation_name GLOB " + "'*" + searchOperationName + "*'");
        }

        if (!StringUtil.isNullOrEmpty(searchStartDateStr)) {
            Date startDate = DateTimeUtil.toDate(searchStartDateStr, "UTC", DateTimeFormat.yyyyMMdd_hyphen);
            sql.append("    AND top.operation_end_date >= '" + DateTimeUtil.toString(startDate, DateTimeFormat.yyyyMMdd_hyphen) + "'");
        }

        if (!StringUtil.isNullOrEmpty(searchEndDateStr)) {
            Date endDate = DateTimeUtil.toDate(searchEndDateStr, "UTC", DateTimeFormat.yyyyMMdd_hyphen);
            sql.append("    AND top.operation_start_date <= '" + DateTimeUtil.toString(endDate, DateTimeFormat.yyyyMMdd_hyphen) + "'");
        }

        if (reportTypeStr != null) {
            sql.append("    AND top.report_type in ("+ reportTypeStr +")");
        }

        sql.append("        ORDER BY top.operation_name ASC");

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

        return sql.toString();
    }

    public boolean updateContentCreatingFlg(long operationId, boolean contentCreatingFlg) {
        OperationDto dto = getOperation(operationId);
        if (dto != null) {
            dto.contentCreatingFlg = contentCreatingFlg;
            return update(dto);
        }
        return false;
    }

    /**
     * 報告タイプの作業取得
     * @param reportType 報告タイプ
     * @return 作業リスト
     */
    public List<OperationDto> getOperationByReportType(Integer reportType) {
        StringBuffer sql = new StringBuffer();
        sql.append("    SELECT * ");
        sql.append("    FROM t_operation ");
        sql.append("    WHERE report_type = ?");
        return rawQueryGetDtoList(sql.toString(), new String[] { "" + reportType }, OperationDto.class);
    }

    /**
     * ユーザ情報でグループが変更した時のみ使用
     * 報告・回答タイプを検索して、同期フラグをtrueに設定
     */
    public void updateNeedSyncForReportReplyType() {
        List<OperationDto> operationDtoList = getOperationByReportType(Constant.ReportType.ReportReply);
        for (OperationDto operationDto : operationDtoList) {
            updateNeedSyncFlg(operationDto.operationId, true);
        }
    }

    // quick_report フィールドを追加

    /**
     * 作業グループに紐づく同期可能な作業リストが存在するかチェック
     * @param operationGroupMasterId
     * @return
     */
    public boolean hasNeedSyncOperationByGroupMasterId(Integer operationGroupMasterId) {
        List<OperationDto> operationDtoList = getNeedSyncOperationByGroupMasterId(operationGroupMasterId);
        return operationDtoList != null && operationDtoList.size() > 0;
    }

    /**
     * 作業グループに紐づく同期可能な作業リストを取得
     * @param operationGroupMasterId
     * @return
     */
    public List<OperationDto> getNeedSyncOperationByGroupMasterId(Integer operationGroupMasterId) {
        StringBuffer sql = new StringBuffer();
        sql.append("    SELECT * ");
        sql.append("    FROM t_operation AS top ");
        sql.append("    INNER JOIN r_operation_content AS rop ");
        sql.append("        ON top.operation_id = rop.operation_id ");
        sql.append("        AND rop.operation_content_flg = 1 ");
        sql.append("    INNER JOIN r_operation_group_master_relation AS rogm ");
        sql.append("        ON top.operation_id = rogm.operation_id ");
        sql.append("    WHERE top.need_sync_flg = 1");
        sql.append("    AND rogm.operation_group_master_id = ?");
        return rawQueryGetDtoList(sql.toString(), new String[] { "" + operationGroupMasterId }, OperationDto.class);
    }

    /**
     * カテゴリの作業一覧検索用
     * @param operationGroupMasterId カテゴリID
     * @param operationSortingType ソートタイプ
     * @return カテゴリに紐づく作業リスト
     */
    public List<OperationDto> getOperationsByGroupMasterId(Integer operationGroupMasterId, String reportTypeStr, OperationSortingType operationSortingType) {
        String sql = generateGetOperationQuery(null, null, null, operationGroupMasterId, reportTypeStr, operationSortingType);
        return rawQueryGetDtoList(sql, null, OperationDto.class);
    }

    /**
     * 引数の検索条件で作業情報リストを
     * 取得用のsql文作成
     * @param searchOperationName ホーム画面入力した検索キーワード
     * @param searchStartDateStr 開始期間
     * @param searchEndDateStr 終了期間
     * @param operationGroupMasterId 作業ID
     * @param reportTypeStr　点検 or 報告
     * @param operationSortingType ソート方向
     * @return SQL文
     */
    private String generateGetOperationQuery(String searchOperationName,
                                             String searchStartDateStr,
                                             String searchEndDateStr,
                                             Integer operationGroupMasterId,
                                             String reportTypeStr,
                                             OperationSortingType operationSortingType) {
        String curDate = DateTimeUtil.toStringInTimeZone(new Date(), DateTimeFormat.yyyyMMddHHmmss_hyphen, "UTC");

        StringBuffer sql = new StringBuffer();

        sql.append("    SELECT top.operation_id, ");
        sql.append("        top.operation_name, ");
        sql.append("        top.operation_type, ");
        sql.append("        top.operation_descriptions, ");
        sql.append("        top.operation_start_date, ");
        sql.append("        top.operation_end_date, ");
        sql.append("        top.last_edit_date, ");
        sql.append("        top.edit_lock_flg, ");
        sql.append("        top.need_sync_flg, ");
        sql.append("        top.content_creating_flg, ");
        sql.append("        top.report_type, ");
        sql.append("        top.report_cycle, ");
        sql.append("        top.enable_report_update, ");
        sql.append("        top.enable_report_edit, ");
        sql.append("        top.enable_add_report, ");
        sql.append("        top.status_not_started_count, ");
        sql.append("        top.status_working_count, ");
        sql.append("        top.status_completed_count, ");
        sql.append("        top.operation_last_sync_date, ");
        sql.append("        CASE ");
        sql.append("            WHEN report_type = 1 THEN ( ");
        sql.append("                SELECT strftime('%Y/%m/%d %H:%M', datetime(ttr.report_start_date, 'localtime')) || ' ~ ' || strftime('%Y/%m/%d %H:%M', datetime(ttr.report_end_date, 'localtime')) ");
        sql.append("                FROM t_task tt ");
        sql.append("                    INNER JOIN t_task_report ttr ");
        sql.append("                    ON tt.task_key = ttr.task_key ");
        sql.append("                    AND tt.del_flg = 0 ");
        //sql.append("                    AND datetime(ttr.report_start_date) <= datetime('" + curDate + "') ");
        sql.append("                    AND datetime(ttr.report_end_date) >= datetime('" + curDate + "') ");
        sql.append("                WHERE tt.operation_id = top.operation_id ");
        sql.append("                ORDER BY ttr.report_start_date ASC LIMIT 1 ) ");
        sql.append("            ELSE '' ");
        sql.append("        END AS report_period, ");
        sql.append("        CASE ");
        sql.append("            WHEN report_type = 1 THEN ( ");
        sql.append("                SELECT count(*) ");
        sql.append("                FROM t_task_report ");
        sql.append("                WHERE task_key = (SELECT task_key FROM t_task WHERE operation_id = top.operation_id LIMIT 1) ) ");
        sql.append("            ELSE 0 ");
        sql.append("        END AS report_count, ");
        sql.append("        rpc.content_id ");
        sql.append("        FROM t_operation AS top ");
        sql.append("        LEFT OUTER JOIN r_operation_content AS rpc ");
        sql.append("        ON top.operation_id = rpc.operation_id ");
        sql.append("        AND rpc.operation_content_flg = 1 ");

        // 作業種別IDに紐づく作業のみ検索するため、INNER JOINする
        if (operationGroupMasterId != null) {
            sql.append("        INNER JOIN r_operation_group_master_relation AS rogmo ");
            sql.append("        ON rogmo.operation_id = top.operation_id ");
            sql.append("        AND rogmo.operation_group_master_id =" + operationGroupMasterId);
        }

        sql.append("        WHERE top.operation_id IS NOT NULL ");
        if (!StringUtil.isNullOrEmpty(searchOperationName)) {
            sql.append("    AND top.operation_name GLOB " + "'*" + searchOperationName + "*'");
        }

        if (!StringUtil.isNullOrEmpty(searchStartDateStr)) {
            Date startDate = DateTimeUtil.toDate(searchStartDateStr, "UTC", DateTimeFormat.yyyyMMdd_hyphen);
            sql.append("    AND top.operation_end_date >= '" + DateTimeUtil.toString(startDate, DateTimeFormat.yyyyMMdd_hyphen) + "'");
        }

        if (!StringUtil.isNullOrEmpty(searchEndDateStr)) {
            Date endDate = DateTimeUtil.toDate(searchEndDateStr, "UTC", DateTimeFormat.yyyyMMdd_hyphen);
            sql.append("    AND top.operation_start_date <= '" + DateTimeUtil.toString(endDate, DateTimeFormat.yyyyMMdd_hyphen) + "'");
        }
        if (reportTypeStr != null) {
            sql.append("    AND top.report_type in (" + reportTypeStr + ")");
        }

        sql.append("        ORDER BY top.operation_name ASC");
        //並び替え情報がないので以下はコメントアウト（次回開発のため残す）
//        if (operationSortingType != null) {
//            switch (operationSortingType) {
//                case OperationName:
//                    sql.append("  ORDER BY top.operation_name ASC ");
//                    break;
//                case OperationStartDateASC:
//                    sql.append("  ORDER BY top.operation_start_date ASC ");
//                    break;
//                case OperationStartDateDESC:
//                    sql.append("  ORDER BY top.operation_start_date DESC ");
//                    break;
//                case OperationType:
//                    sql.append("  ORDER BY top.report_type ASC ");
//                    break;
//                case ReadingDate:
//                    sql.append("  ORDER BY top.operation_open_date DESC ");
//                    break;
//                default:
//                    sql.append("  ORDER BY top.operation_name ASC ");
//                    break;
//            }
//        } else {
//            sql.append("  ORDER BY top.operation_name ASC ");
//        }
//        // 必ずORDER BYが存在する 2次ソート
//        sql.append(", top.operation_id DESC");

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

        return sql.toString();
    }

    private String incrementCount(long operationId, String column) {
        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE t_operation ");
        sql.append("  SET ").append(column).append(" = (").append(column).append(" + 1)");
        sql.append("  WHERE operation_id = ").append(operationId);

        return sql.toString();
    }

    private String decrementCount(long operationId, String column) {
        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE t_operation ");
        sql.append("  SET ").append(column).append(" = (").append(column).append(" - 1)");
        sql.append("  WHERE operation_id = ").append(operationId);
        sql.append("  AND ").append(column).append(" > 0");

        return sql.toString();
    }

    public void countUpCompleted(long operationId) {
        beginTransaction();
        try {
            execSql(decrementCount(operationId, "status_not_started_count"));
            execSql(incrementCount(operationId, "status_completed_count"));
            commit();
        } catch (Throwable e) {
            rollback();
            Logger.e(TAG, e);
        }
    }

    public void countUpWorking(long operationId) {
        beginTransaction();
        try {
            execSql(decrementCount(operationId, "status_not_started_count"));
            execSql(incrementCount(operationId, "status_working_count"));
            commit();
        } catch (Throwable e) {
            rollback();
            Logger.e(TAG, e);
        }
    }

    public void coutUpCompletedFromWorking(long operationId) {
        beginTransaction();
        try {
            execSql(decrementCount(operationId, "status_working_count"));
            execSql(incrementCount(operationId, "status_completed_count"));
            commit();
        } catch (Throwable e) {
            rollback();
            Logger.e(TAG, e);
        }
    }

    public List<OperationDto> getNeedSyncAllOperation() {
        return rawQueryGetDtoList(
                "select tp.*, rpc.content_id from t_operation AS tp "
                        + "left outer join r_operation_content AS rpc on tp.operation_id = rpc.operation_id "
                        + "and rpc.operation_content_flg = 1 "
                        + "where tp.need_sync_flg = 1",
                null,
                OperationDto.class);
    }
}
