protected long addLayoutSet(
      long layoutSetId,
      long companyId,
      long groupId,
      long layoutSetPrototypeId,
      UserGroupTemplateInfo userGroupTemplateInfo)
      throws Exception {

    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      con = DataAccess.getConnection();

      StringBundler sb = new StringBundler(5);

      sb.append("insert into LayoutSet (layoutSetId, groupId, ");
      sb.append("companyId, privateLayout, logo, logoId, themeId, ");
      sb.append("colorSchemeId, wapThemeId, wapColorSchemeId, css, ");
      sb.append("pageCount, settings_, layoutSetPrototypeId) values ");
      sb.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

      String sql = sb.toString();

      ps = con.prepareStatement(sql);

      ps.setLong(1, layoutSetId);
      ps.setLong(2, groupId);
      ps.setLong(3, companyId);
      ps.setBoolean(4, userGroupTemplateInfo.isPrivateLayout());
      ps.setShort(5, userGroupTemplateInfo.getLogo());
      ps.setLong(6, userGroupTemplateInfo.getLogoId());
      ps.setString(7, userGroupTemplateInfo.getThemeId());
      ps.setString(8, userGroupTemplateInfo.getColorSchemeId());
      ps.setString(9, userGroupTemplateInfo.getWapThemeId());
      ps.setString(10, userGroupTemplateInfo.getWapColorSchemeId());
      ps.setString(11, userGroupTemplateInfo.getCss());
      ps.setLong(12, userGroupTemplateInfo.getPageCount());
      ps.setString(13, userGroupTemplateInfo.getSettings());
      ps.setLong(14, layoutSetPrototypeId);

      ps.execute();

      return layoutSetId;

    } finally {
      DataAccess.cleanUp(con, ps, rs);
    }
  }
  protected void updateUserGroup() throws Exception {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      con = DataAccess.getConnection();

      StringBundler sb = new StringBundler(17);

      sb.append("select UserGroup.userGroupId as userGroupId, ");
      sb.append("UserGroup.companyId as companyId, UserGroup.name as ");
      sb.append("userGroupName, Group_.groupId as groupId, ");
      sb.append("Group_.creatorUserId as creatorUserId, ");
      sb.append("LayoutSet.layoutSetId as layoutSetId, ");
      sb.append("LayoutSet.privateLayout as privateLayout, ");
      sb.append("LayoutSet.logo as logo, LayoutSet.logoId as logoId, ");
      sb.append("LayoutSet.themeId as themeId, LayoutSet.colorSchemeId ");
      sb.append("as colorSchemeId, LayoutSet.wapThemeId as wapThemeId, ");
      sb.append("LayoutSet.wapColorSchemeId as wapColorSchemeId, ");
      sb.append("LayoutSet.css as css, LayoutSet.pageCount as ");
      sb.append("pageCount, LayoutSet.settings_ as settings, ");
      sb.append("LayoutSet.layoutSetPrototypeId as ");
      sb.append("layoutSetPrototypeId from UserGroup inner join ");
      sb.append("Group_ on Group_.classPK = UserGroup.userGroupId ");
      sb.append("inner join LayoutSet on (LayoutSet.groupId = ");
      sb.append("Group_.groupId) and (LayoutSet.pageCount > 0)");

      String sql = sb.toString();

      ps = con.prepareStatement(sql);

      rs = ps.executeQuery();

      while (rs.next()) {
        long userGroupId = rs.getLong("userGroupId");
        long companyId = rs.getLong("companyId");
        String userGroupName = rs.getString("userGroupName");
        long groupId = rs.getLong("groupId");
        long creatorUserId = rs.getLong("creatorUserId");
        boolean privateLayout = rs.getBoolean("privateLayout");

        long layoutSetPrototypeId = increment();

        addLayoutSetPrototype(layoutSetPrototypeId, companyId, userGroupName);

        long layoutSetPrototypeGroupId =
            addGroup(companyId, creatorUserId, userGroupName, layoutSetPrototypeId);

        addPermissions(companyId, creatorUserId, layoutSetPrototypeId);

        UserGroupTemplateInfo privateUserGroupTemplateInfo = buildUserGroupsTemplatesInfo(rs);

        boolean oldPrivateLayout = privateUserGroupTemplateInfo.isPrivateLayout();

        privateUserGroupTemplateInfo.setPrivateLayout(true);

        addLayoutSet(
            increment(),
            companyId,
            layoutSetPrototypeGroupId,
            layoutSetPrototypeId,
            privateUserGroupTemplateInfo);

        updateLayout(groupId, layoutSetPrototypeGroupId, oldPrivateLayout);

        UserGroupTemplateInfo publicUserGroupTemplateInfo = new UserGroupTemplateInfo();

        publicUserGroupTemplateInfo.setPrivateLayout(false);

        addLayoutSet(
            increment(),
            companyId,
            layoutSetPrototypeGroupId,
            layoutSetPrototypeId,
            publicUserGroupTemplateInfo);

        updateUserGroup(userGroupId, privateLayout, layoutSetPrototypeId);
      }
    } finally {
      DataAccess.cleanUp(con, ps, rs);
    }
  }
  protected UserGroupTemplateInfo buildUserGroupsTemplatesInfo(ResultSet rs) throws Exception {

    UserGroupTemplateInfo userGroupTemplateInfo = new UserGroupTemplateInfo();

    userGroupTemplateInfo.setColorSchemeId(rs.getString("colorSchemeId"));
    userGroupTemplateInfo.setCompanyId(rs.getLong("companyId"));
    userGroupTemplateInfo.setCss(rs.getString("css"));
    userGroupTemplateInfo.setGroupId(rs.getLong("groupId"));
    userGroupTemplateInfo.setLayoutSetId(rs.getLong("layoutSetId"));
    userGroupTemplateInfo.setLayoutSetPrototypeId(rs.getLong("layoutSetPrototypeId"));
    userGroupTemplateInfo.setLogo(rs.getShort("logo"));
    userGroupTemplateInfo.setLogoId(rs.getLong("logoId"));
    userGroupTemplateInfo.setPageCount(rs.getLong("pageCount"));
    userGroupTemplateInfo.setPrivateLayout(rs.getBoolean("privateLayout"));
    userGroupTemplateInfo.setSettings(rs.getString("settings"));
    userGroupTemplateInfo.setThemeId(rs.getString("themeId"));
    userGroupTemplateInfo.setUserGroupId(rs.getLong("userGroupId"));
    userGroupTemplateInfo.setWapColorSchemeId(rs.getString("wapColorSchemeId"));
    userGroupTemplateInfo.setWapThemeId(rs.getString("wapThemeId"));

    return userGroupTemplateInfo;
  }