0001: /*
0002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
0003: C/Oña, 107 1º2 28050 Madrid (Spain)
0004:
0005: Redistribution and use in source and binary forms, with or without
0006: modification, are permitted provided that the following conditions
0007: are met:
0008:
0009: 1. Redistributions of source code must retain the above copyright
0010: notice, this list of conditions and the following disclaimer.
0011:
0012: 2. The end-user documentation included with the redistribution,
0013: if any, must include the following acknowledgment:
0014: "This product includes software parts from hipergate
0015: (http://www.hipergate.org/)."
0016: Alternately, this acknowledgment may appear in the software itself,
0017: if and wherever such third-party acknowledgments normally appear.
0018:
0019: 3. The name hipergate must not be used to endorse or promote products
0020: derived from this software without prior written permission.
0021: Products derived from this software may not be called hipergate,
0022: nor may hipergate appear in their name, without prior written
0023: permission.
0024:
0025: This library is distributed in the hope that it will be useful,
0026: but WITHOUT ANY WARRANTY; without even the implied warranty of
0027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
0028:
0029: You should have received a copy of hipergate License with this code;
0030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
0031: */
0032:
0033: package com.knowgate.crm;
0034:
0035: import java.sql.CallableStatement;
0036: import java.sql.SQLException;
0037: import java.sql.PreparedStatement;
0038: import java.sql.ResultSet;
0039: import java.sql.ResultSetMetaData;
0040:
0041: import com.knowgate.debug.DebugFile;
0042: import com.knowgate.jdc.JDCConnection;
0043: import com.knowgate.dataobjs.DB;
0044: import com.knowgate.dataobjs.DBBind;
0045: import com.knowgate.dataobjs.DBPersist;
0046: import com.knowgate.misc.Gadgets;
0047:
0048: /**
0049: * <p>List Member</p>
0050: * <p>List Members are kept at database differently depending on the type of list
0051: * they belong to.<br><br>
0052: * For Members of Dynamic Lists (k_lists.tp_list=2) no member record is explicitly
0053: * stored at database, but members are dynamically extracted from k_member_address
0054: * view each time that the DistributionList is used.<br><br>
0055: * For Members of Static Lists (k_lists.tp_list=1) a record is stored at
0056: * k_x_list_members table. Members of Static Lists can be Companies from k_companies
0057: * table of Contacts from k_contacts table. If Member is a Company then tp_member is
0058: * set to 91 and gu_company is set to Company GUID. If Member is a Contact then
0059: * tp_member is set to 90 and gu_contact is set to Contact GUID.<br><br>
0060: * For Members of Direct and Black Lists (k_lists.tp_list=3 and k_lists.tp_list=4)
0061: * tp_member is set to 95. One record is stored at k_x_list_members table and
0062: * another record is stored at k_list_members. There is one unique record per
0063: * Member at k_list_members and one record per member and list at k_x_list_members.<br><br>
0064: * <b>List Member Storage Summary<b><br>
0065: * <table cellpadding="4" border="1">
0066: * <tr><td>List Type</td><td>k_lists.tp_member</td><td>k_x_list_member.gu_contact</td><td>k_x_list_member.gu_company</td><td>k_list_member.gu_member</td></tr>
0067: * <tr><td>DYNAMIC</td><td align="middle">90 or 91</td><td align="middle">NONE</td><td align="middle">NONE</td><td align="middle">NONE</td></tr>
0068: * <tr><td>STATIC</td><td align="middle">90 or 91</td><td>References k_contacts if tp_member=90</td><td align="middle">References k_companies if tp_member=91</td><td align="middle">NONE</td></tr>
0069: * <tr><td>DIRECT</td><td align="middle">95</td><td>References k_list_members</td><td align="middle">NULL</td><td align="middle">Member GUID</td></tr>
0070: * </table>
0071: * @author Sergio Montoro Ten
0072: * @version 2.0
0073: */
0074:
0075: public class ListMember {
0076:
0077: private DBPersist oMember;
0078:
0079: // ----------------------------------------------------------
0080:
0081: private short getListType(JDCConnection oConn, String sListGUID)
0082: throws SQLException {
0083:
0084: PreparedStatement oList;
0085: ResultSet oRSet;
0086: short iTpList;
0087:
0088: if (DebugFile.trace) {
0089: DebugFile
0090: .writeln("Begin ListMember.getListType([Connection], "
0091: + (null != sListGUID ? sListGUID : "null")
0092: + ")");
0093: DebugFile.incIdent();
0094: }
0095:
0096: if (null == sListGUID)
0097: iTpList = 0;
0098:
0099: else {
0100:
0101: if (!oMember.getItemMap().containsKey(DB.tp_list)) {
0102:
0103: if (DebugFile.trace)
0104: DebugFile
0105: .writeln("Connection.prepareStatement(SELECT "
0106: + DB.tp_list
0107: + " FROM "
0108: + DB.k_lists
0109: + " WHERE "
0110: + DB.gu_list
0111: + "='"
0112: + (null != sListGUID ? sListGUID
0113: : "null") + "'");
0114:
0115: oList = oConn.prepareStatement("SELECT " + DB.tp_list
0116: + " FROM " + DB.k_lists + " WHERE "
0117: + DB.gu_list + "=?",
0118: ResultSet.TYPE_FORWARD_ONLY,
0119: ResultSet.CONCUR_READ_ONLY);
0120: oList.setString(1, sListGUID);
0121:
0122: try {
0123: oList.setQueryTimeout(20);
0124: } catch (SQLException sqle) { /* ignore */
0125: }
0126:
0127: if (DebugFile.trace)
0128: DebugFile
0129: .writeln("PreparedStatement.executeQuery()");
0130:
0131: oRSet = oList.executeQuery();
0132:
0133: if (oRSet.next()) {
0134: iTpList = oRSet.getShort(1);
0135: oMember.put(DB.tp_list, iTpList);
0136: } else
0137: iTpList = 0;
0138:
0139: oRSet.close();
0140: oList.close();
0141:
0142: } else
0143:
0144: iTpList = oMember.getShort(DB.tp_list);
0145: } // fi (sListGUID)
0146:
0147: if (DebugFile.trace) {
0148: DebugFile.decIdent();
0149: DebugFile.writeln("End ListMember.getListType() : "
0150: + String.valueOf(iTpList));
0151: }
0152:
0153: return iTpList;
0154: } // getListType
0155:
0156: // ----------------------------------------------------------
0157:
0158: public ListMember() {
0159: oMember = new DBPersist(DB.k_list_members, "ListMember");
0160: }
0161:
0162: /**
0163: * Create ListMember and load its fields from database.
0164: * ListMember with given GUID or e-amil is seeked at k_x_list_members table.
0165: * @param oConn Database Connection
0166: * @param sMemberId One of: GUID for Contact, GUID for Company or Member e-mail.
0167: * @param sListGUID DistributionList GUID
0168: * @throws SQLException
0169: */
0170: public ListMember(JDCConnection oConn, String sMemberId,
0171: String sListGUID) throws SQLException {
0172:
0173: PreparedStatement oStmt;
0174: ResultSet oRSet;
0175: ResultSetMetaData oMDat;
0176: Object oFld;
0177: int iCols;
0178:
0179: oMember = new DBPersist(DB.k_list_members, "ListMember");
0180:
0181: if (DebugFile.trace)
0182: DebugFile
0183: .writeln("Connection.prepareStatement(SELECT * FROM "
0184: + DB.k_x_list_members
0185: + " WHERE "
0186: + DB.gu_list
0187: + "='"
0188: + sListGUID
0189: + "' AND ("
0190: + DB.gu_contact
0191: + "='"
0192: + sMemberId
0193: + "' OR "
0194: + DB.gu_company
0195: + "='"
0196: + sMemberId
0197: + "' OR "
0198: + DB.tx_email
0199: + "='"
0200: + sMemberId + "'))");
0201:
0202: oStmt = oConn
0203: .prepareStatement("SELECT * FROM "
0204: + DB.k_x_list_members + " WHERE " + DB.gu_list
0205: + "=? AND (" + DB.gu_contact + "=? OR "
0206: + DB.gu_company + "=? OR " + DB.tx_email
0207: + "=?)", ResultSet.TYPE_FORWARD_ONLY,
0208: ResultSet.CONCUR_READ_ONLY);
0209:
0210: try {
0211: oStmt.setQueryTimeout(20);
0212: } catch (SQLException sqle) { /* ignore */
0213: }
0214:
0215: oStmt.setString(1, sListGUID);
0216: oStmt.setString(2, sMemberId);
0217: oStmt.setString(3, sMemberId);
0218: oStmt.setString(4, sMemberId);
0219:
0220: if (DebugFile.trace)
0221: DebugFile.writeln("PreparedStatement.executeQuery()");
0222:
0223: oRSet = oStmt.executeQuery();
0224:
0225: oMDat = oRSet.getMetaData();
0226: iCols = oMDat.getColumnCount();
0227:
0228: if (oRSet.next()) {
0229: for (int c = 1; c <= iCols; c++) {
0230: if (!oMDat.getColumnName(c).equalsIgnoreCase(
0231: DB.dt_created)) {
0232: oFld = oRSet.getObject(c);
0233: if (!oRSet.wasNull())
0234: oMember.put(oMDat.getColumnName(c)
0235: .toLowerCase(), oFld);
0236: } // fi (getColumnName(c)!=DB.dt_created)
0237: } // next
0238: } // fi (oRSet)
0239: else {
0240: if (DebugFile.trace)
0241: DebugFile.writeln("member " + sMemberId
0242: + " not found at list " + sListGUID);
0243: }
0244:
0245: oRSet.close();
0246: oStmt.close();
0247: }
0248:
0249: // ----------------------------------------------------------
0250:
0251: /**
0252: * Internal DBPersist object reference for k_list_members register.
0253: */
0254: public DBPersist member() {
0255: return oMember;
0256: }
0257:
0258: // ----------------------------------------------------------
0259:
0260: public java.util.Date getDate(String sKey) {
0261: return oMember.getDate(sKey);
0262: }
0263:
0264: // ----------------------------------------------------------
0265:
0266: public String getDateFormated(String sKey, String sFormat) {
0267: return oMember.getDateFormated(sKey, sFormat);
0268: }
0269:
0270: // ----------------------------------------------------------
0271:
0272: public short getShort(String sKey)
0273: throws java.lang.NullPointerException {
0274: return oMember.getShort(sKey);
0275: }
0276:
0277: // ----------------------------------------------------------
0278:
0279: public String getString(String sKey)
0280: throws java.lang.NullPointerException {
0281: return oMember.getString(sKey);
0282: }
0283:
0284: // ----------------------------------------------------------
0285:
0286: public String getStringNull(String sKey, String sDefault) {
0287: return oMember.getStringNull(sKey, sDefault);
0288: }
0289:
0290: // ----------------------------------------------------------
0291:
0292: public boolean isNull(String sColumnName) {
0293: return oMember.isNull(sColumnName);
0294: }
0295:
0296: // ----------------------------------------------------------
0297:
0298: public void put(String sKey, String sVal) {
0299: oMember.put(sKey, sVal);
0300: }
0301:
0302: // ----------------------------------------------------------
0303:
0304: public void put(String sKey, short iVal) {
0305: oMember.put(sKey, iVal);
0306: }
0307:
0308: // ----------------------------------------------------------
0309:
0310: public void put(String sKey, java.util.Date dtVal) {
0311: oMember.put(sKey, dtVal);
0312: }
0313:
0314: // ----------------------------------------------------------
0315:
0316: public void put(String sKey, Object oObj) {
0317: oMember.put(sKey, oObj);
0318: }
0319:
0320: // ----------------------------------------------------------
0321:
0322: public void replace(String sKey, Object oObj) {
0323: oMember.replace(sKey, oObj);
0324: }
0325:
0326: // ----------------------------------------------------------
0327:
0328: public void replace(String sKey, short iVal) {
0329: oMember.replace(sKey, iVal);
0330: }
0331:
0332: // ----------------------------------------------------------
0333:
0334: public void remove(String sKey) {
0335: oMember.remove(sKey);
0336: }
0337:
0338: // ----------------------------------------------------------
0339:
0340: /**
0341: * <p>Get whether or not a List Member is Blocked</p>
0342: * Blocked Members are those present on the associated black list.<br>
0343: * Companies and Contacts are considered to be in the black list if its
0344: * gu_company or gu_contact is into black list register at k_x_list_members<br>
0345: * Direct List Members are considered to be in the black list if its
0346: * tx_email is into black list register at k_x_list_members<br>
0347: * This way, by searching Companies and Contacts by GUID and Direct Members by
0348: * e-mail, a Contact or Company may have several e-mail addresses all of them
0349: * blocked by a single black list entry.<br>
0350: * This method calls stored procedures: k_sp_company_blocked, k_sp_contact_blocked and k_sp_email_blocked
0351: * @param oConn Database Connection
0352: * @return <b>true</b> if member is blocked (present at associated black list for this list) <b>false</b> otherwise.
0353: * @throws SQLException
0354: */
0355: public boolean isBlocked(JDCConnection oConn) throws SQLException {
0356: boolean bBlocked;
0357: CallableStatement oCall;
0358: PreparedStatement oStmt;
0359: ResultSet oRSet;
0360: String sList;
0361: String sProc;
0362: String sParm;
0363:
0364: if (DebugFile.trace) {
0365: DebugFile
0366: .writeln("Begin ListMember.isBlocked([Connection])");
0367: DebugFile.incIdent();
0368: }
0369:
0370: sList = oMember.getString(DB.gu_list);
0371:
0372: switch (oMember.getShort(DB.tp_member)) {
0373: case Company.ClassId:
0374: sProc = "k_sp_company_blocked";
0375: sParm = oMember.getString(DB.gu_company);
0376: if (DebugFile.trace)
0377: DebugFile.writeln("gu_company=" + sParm);
0378: break;
0379: case Contact.ClassId:
0380: sProc = "k_sp_contact_blocked";
0381: sParm = oMember.getString(DB.gu_contact);
0382: if (DebugFile.trace)
0383: DebugFile.writeln("gu_contact=" + sParm);
0384: break;
0385: default:
0386: sProc = "k_sp_email_blocked";
0387: sParm = oMember.getString(DB.tx_email);
0388: if (DebugFile.trace)
0389: DebugFile.writeln("tx_email=" + sParm);
0390: }
0391:
0392: switch (oConn.getDataBaseProduct()) {
0393:
0394: case JDCConnection.DBMS_POSTGRESQL:
0395: if (DebugFile.trace)
0396: DebugFile.writeln("Connection.prepareStatement(SELECT "
0397: + sProc + "(?,?))");
0398:
0399: oStmt = oConn.prepareStatement("SELECT " + sProc + "(?,?)",
0400: ResultSet.TYPE_FORWARD_ONLY,
0401: ResultSet.CONCUR_READ_ONLY);
0402: oStmt.setString(1, sList);
0403: oStmt.setString(2, sParm);
0404: oRSet = oStmt.executeQuery();
0405: oRSet.next();
0406: bBlocked = (oRSet.getShort(1) != (short) 0);
0407: oRSet.close();
0408: oStmt.close();
0409: break;
0410:
0411: case JDCConnection.DBMS_ORACLE:
0412: if (DebugFile.trace)
0413: DebugFile.writeln("Connection.prepareCall({ call "
0414: + sProc + "(?,?,?)})");
0415:
0416: oCall = oConn.prepareCall("{ call " + sProc + "(?,?,?)}");
0417: oCall.setString(1, sList);
0418: oCall.setString(2, sParm);
0419: oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
0420: oCall.execute();
0421: bBlocked = (oCall.getBigDecimal(3).intValue() != 0);
0422: oCall.close();
0423: break;
0424:
0425: default:
0426: if (DebugFile.trace)
0427: DebugFile.writeln("Connection.prepareCall({ call "
0428: + sProc + "(?,?,?)})");
0429:
0430: oCall = oConn.prepareCall("{ call " + sProc + "(?,?,?)}");
0431: oCall.setString(1, sList);
0432: oCall.setString(2, sParm);
0433: oCall.registerOutParameter(3, java.sql.Types.SMALLINT);
0434: oCall.execute();
0435: bBlocked = (oCall.getShort(3) != (short) 0);
0436: oCall.close();
0437: }
0438:
0439: if (DebugFile.trace) {
0440: DebugFile.decIdent();
0441: DebugFile.writeln("End ListMember.isBlocked() : "
0442: + String.valueOf(bBlocked));
0443: }
0444:
0445: return bBlocked;
0446: }
0447:
0448: // ----------------------------------------------------------
0449:
0450: /**
0451: * <p>Remove Member from a DistributionList.</p>
0452: * Member is also removed from associated black list.
0453: * @param oConn Database Connection
0454: * @param sListId GUID of DistributionList from witch Member is to be removed.
0455: * @throws SQLException
0456: */
0457: public boolean delete(JDCConnection oConn, String sListId)
0458: throws SQLException {
0459: PreparedStatement oDlte;
0460: PreparedStatement oStmt;
0461: ResultSet oRSet;
0462: boolean bRetVal;
0463: String sBlackList;
0464:
0465: if (DebugFile.trace) {
0466: DebugFile.writeln("Begin ListMember.delete([Connection], "
0467: + sListId + ")");
0468: DebugFile.incIdent();
0469: }
0470:
0471: // Find associated Black List
0472:
0473: if (DebugFile.trace)
0474: DebugFile
0475: .writeln("Connection.prepareStatement(SELECT "
0476: + DB.gu_list
0477: + " FROM "
0478: + DB.k_lists
0479: + " WHERE "
0480: + DB.gu_query
0481: + "='"
0482: + sListId
0483: + "' AND "
0484: + DB.tp_list
0485: + "="
0486: + String
0487: .valueOf(DistributionList.TYPE_BLACK)
0488: + ")");
0489:
0490: oStmt = oConn
0491: .prepareStatement("SELECT " + DB.gu_list + " FROM "
0492: + DB.k_lists + " WHERE " + DB.gu_query
0493: + "=? AND " + DB.tp_list + "="
0494: + String.valueOf(DistributionList.TYPE_BLACK),
0495: ResultSet.TYPE_FORWARD_ONLY,
0496: ResultSet.CONCUR_READ_ONLY);
0497: oStmt.setString(1, sListId);
0498: oRSet = oStmt.executeQuery();
0499: if (oRSet.next())
0500: sBlackList = oRSet.getString(1);
0501: else
0502: sBlackList = null;
0503: oRSet.close();
0504: oStmt.close();
0505:
0506: // Delete Member from Black List
0507:
0508: if (null != sBlackList) {
0509: if (DebugFile.trace)
0510: DebugFile
0511: .writeln("Connection.prepareStatement(DELETE FROM "
0512: + DB.k_x_list_members
0513: + " WHERE "
0514: + DB.gu_list
0515: + "='"
0516: + sBlackList
0517: + "' AND ("
0518: + DB.gu_contact
0519: + "='"
0520: + oMember.getStringNull(DB.gu_member,
0521: "null")
0522: + "' OR "
0523: + DB.gu_company
0524: + "='"
0525: + oMember.getStringNull(DB.gu_member,
0526: "null")
0527: + "' OR "
0528: + DB.tx_email
0529: + "='"
0530: + oMember.getStringNull(DB.tx_email,
0531: "null") + "'))");
0532:
0533: oDlte = oConn.prepareStatement("DELETE FROM "
0534: + DB.k_x_list_members + " WHERE " + DB.gu_list
0535: + "=? AND (" + DB.gu_contact + "=? OR "
0536: + DB.gu_company + "=? OR " + DB.tx_email + "=?)");
0537:
0538: oDlte.setString(1, sBlackList);
0539: oDlte.setString(2, oMember.getString(DB.gu_member));
0540: oDlte.setString(3, oMember.getString(DB.gu_member));
0541: oDlte.setString(4, oMember.getString(DB.tx_email));
0542:
0543: if (DebugFile.trace)
0544: DebugFile.writeln("PreparedStatement.executeUpdate()");
0545:
0546: bRetVal = oDlte.executeUpdate() > 0;
0547: oDlte.close();
0548: }
0549:
0550: // Delete Member from List
0551:
0552: if (DebugFile.trace)
0553: DebugFile
0554: .writeln("Connection.prepareStatement(DELETE FROM "
0555: + DB.k_x_list_members
0556: + " WHERE "
0557: + DB.gu_list
0558: + "='"
0559: + sListId
0560: + "' AND ("
0561: + DB.gu_contact
0562: + "='"
0563: + oMember.getStringNull(DB.gu_member,
0564: "null")
0565: + "' OR "
0566: + DB.gu_company
0567: + "='"
0568: + oMember.getStringNull(DB.gu_member,
0569: "null")
0570: + "' OR "
0571: + DB.tx_email
0572: + "='"
0573: + oMember
0574: .getStringNull(DB.tx_email, "null")
0575: + "'))");
0576:
0577: oDlte = oConn.prepareStatement("DELETE FROM "
0578: + DB.k_x_list_members + " WHERE " + DB.gu_list
0579: + "=? AND (" + DB.gu_contact + "=? OR " + DB.gu_company
0580: + "=? OR " + DB.tx_email + "=?)");
0581:
0582: oDlte.setString(1, sListId);
0583: oDlte.setString(2, oMember.getString(DB.gu_member));
0584: oDlte.setString(3, oMember.getString(DB.gu_member));
0585: oDlte.setString(4, oMember.getString(DB.tx_email));
0586:
0587: if (DebugFile.trace)
0588: DebugFile.writeln("PreparedStatement.executeUpdate()");
0589:
0590: bRetVal = oDlte.executeUpdate() > 0;
0591: oDlte.close();
0592:
0593: int iTpList = getListType(oConn, sListId);
0594:
0595: if (DistributionList.TYPE_DIRECT == iTpList
0596: || DistributionList.TYPE_BLACK == iTpList)
0597: bRetVal = oMember.delete(oConn);
0598:
0599: if (DebugFile.trace) {
0600: DebugFile.decIdent();
0601: DebugFile.writeln("End ListMember.delete() : "
0602: + String.valueOf(bRetVal));
0603: }
0604:
0605: return bRetVal;
0606: } // delete
0607:
0608: // ----------------------------------------------------------
0609:
0610: /**
0611: * Remove Member from all DistributionList.
0612: * @param oConn Database Connection
0613: * @throws SQLException
0614: */
0615: public boolean delete(JDCConnection oConn) throws SQLException {
0616: PreparedStatement oDlte;
0617: boolean bRetVal;
0618:
0619: if (DebugFile.trace) {
0620: DebugFile.writeln("Begin ListMember.delete([Connection])");
0621: DebugFile.incIdent();
0622: }
0623:
0624: oDlte = oConn.prepareStatement("DELETE FROM "
0625: + DB.k_x_list_members + " WHERE " + DB.gu_contact
0626: + "=? OR " + DB.gu_company + "=?");
0627: oDlte.setString(1, oMember.getString(DB.gu_member));
0628: oDlte.setString(2, oMember.getString(DB.gu_member));
0629: oDlte.execute();
0630: oDlte.close();
0631:
0632: bRetVal = oMember.delete(oConn);
0633:
0634: if (DebugFile.trace) {
0635: DebugFile.decIdent();
0636: DebugFile.writeln("End ListMember.delete() : "
0637: + String.valueOf(bRetVal));
0638: }
0639:
0640: return bRetVal;
0641: } // delete
0642:
0643: // ----------------------------------------------------------
0644:
0645: /**
0646: * <p>Store Member at a DistributionList</p>
0647: * <p>Automatically generates gu_member GUID for Direct List Members and dt_modified DATE if not explicitly set.</p>
0648: * @param oConn Database Connection
0649: * @param sListGUID GUID of Distribution List
0650: * @throws ClassCastException If sListId type is DYNAMIC.
0651: * @throws NoSuchFieldException If List is Static and gu_member field is not set
0652: * @throws SQLException
0653: */
0654:
0655: public boolean store(JDCConnection oConn, String sListGUID)
0656: throws ClassCastException, NoSuchFieldException,
0657: SQLException {
0658: boolean bRetVal;
0659: String sSQL;
0660: java.sql.Timestamp dtNow = new java.sql.Timestamp(DBBind
0661: .getTime());
0662: PreparedStatement oStmt;
0663: int iAffected;
0664:
0665: if (DebugFile.trace) {
0666: DebugFile.writeln("Begin ListMember.store([Connection])");
0667: DebugFile.incIdent();
0668: }
0669:
0670: int iTpList = getListType(oConn, sListGUID);
0671:
0672: if (iTpList == DistributionList.TYPE_DYNAMIC)
0673: throw new ClassCastException(
0674: "Dynamic Distribution Lists cannot have additional Members directly added");
0675:
0676: if (!oMember.getItemMap().containsKey(DB.gu_member)) {
0677: if (iTpList == DistributionList.TYPE_STATIC)
0678: throw new NoSuchFieldException(
0679: "gu_member field must be set before storing a Member from a Static Distribution List");
0680:
0681: oMember.put(DB.gu_member, Gadgets.generateUUID());
0682: }
0683:
0684: // Forzar la fecha de modificación del registro
0685: oMember.replace(DB.dt_modified, dtNow);
0686:
0687: if (DistributionList.TYPE_DIRECT == iTpList
0688: || DistributionList.TYPE_BLACK == iTpList)
0689: bRetVal = oMember.store(oConn);
0690:
0691: if (DebugFile.trace) {
0692: String sActive;
0693: if (oMember.getItemMap().containsKey(DB.bo_active))
0694: sActive = String
0695: .valueOf(oMember.getShort(DB.bo_active));
0696: else
0697: sActive = "null";
0698:
0699: sSQL = "UPDATE " + DB.k_x_list_members + " SET "
0700: + DB.tx_email + "='"
0701: + oMember.getStringNull(DB.tx_email, "null") + "',"
0702: + DB.tx_name + "=?," + DB.tx_surname + "=?,"
0703: + DB.tx_salutation + "=?," + DB.bo_active + "="
0704: + sActive + "," + DB.id_format + "='"
0705: + oMember.getStringNull(DB.id_format, "TXT") + "',"
0706: + DB.dt_modified + "="
0707: + DBBind.escape(new java.util.Date(), "ts")
0708: + " WHERE " + DB.gu_list + "='" + sListGUID
0709: + "' AND (" + DB.gu_contact + "='"
0710: + oMember.getStringNull(DB.gu_member, "null")
0711: + "' OR " + DB.gu_company + "=? OR " + DB.tx_email
0712: + "='" + oMember.getStringNull(DB.tx_email, "null")
0713: + "')";
0714:
0715: DebugFile.writeln("Connection.prepareStatement(" + sSQL
0716: + ")");
0717: }
0718:
0719: sSQL = "UPDATE " + DB.k_x_list_members + " SET " + DB.tx_email
0720: + "=?," + DB.tx_name + "=?," + DB.tx_surname + "=?,"
0721: + DB.tx_salutation + "=?," + DB.bo_active + "=?,"
0722: + DB.id_format + "=?," + DB.dt_modified + "="
0723: + DBBind.escape(new java.util.Date(), "ts") + " WHERE "
0724: + DB.gu_list + "=? AND (" + DB.gu_contact + "=? OR "
0725: + DB.gu_company + "=? OR " + DB.tx_email + "=?)";
0726:
0727: oStmt = oConn.prepareStatement(sSQL);
0728:
0729: oStmt.setString(1, oMember.getString(DB.tx_email));
0730: oStmt.setString(2, oMember.getStringNull(DB.tx_name, null));
0731: oStmt.setString(3, oMember.getStringNull(DB.tx_surname, null));
0732: oStmt.setString(4, oMember
0733: .getStringNull(DB.tx_salutation, null));
0734:
0735: if (oMember.getItemMap().containsKey(DB.bo_active))
0736: oStmt.setShort(5, oMember.getShort(DB.bo_active));
0737: else
0738: oStmt.setShort(5, (short) 1);
0739:
0740: oStmt.setString(6, oMember.getStringNull(DB.id_format, "TXT"));
0741:
0742: oStmt.setString(7, sListGUID);
0743:
0744: if (oMember.getItemMap().containsKey(DB.tp_member)) {
0745:
0746: if (DebugFile.trace)
0747: DebugFile
0748: .writeln("tp_member="
0749: + String.valueOf(oMember
0750: .getShort(DB.tp_member)));
0751:
0752: if (oMember.getShort(DB.tp_member) == Company.ClassId) {
0753:
0754: if (DebugFile.trace)
0755: DebugFile.writeln("gu_contact="
0756: + oMember.getStringNull(DB.gu_member,
0757: "null") + " , gu_company=null");
0758:
0759: oStmt.setString(8, oMember.getString(DB.gu_member));
0760: oStmt.setString(9, null);
0761: } else {
0762: if (DebugFile.trace)
0763: DebugFile.writeln("gu_contact=null, gu_company="
0764: + oMember.getStringNull(DB.gu_member,
0765: "null"));
0766:
0767: oStmt.setString(8, null);
0768: oStmt.setString(9, oMember.getString(DB.gu_member));
0769: }
0770: } else {
0771: if (DebugFile.trace)
0772: DebugFile.writeln("tp_member not set");
0773:
0774: if (DebugFile.trace)
0775: DebugFile.writeln("gu_contact=null, gu_company="
0776: + oMember.getStringNull(DB.gu_member, "null"));
0777:
0778: oStmt.setString(8, null);
0779: oStmt.setString(9, oMember.getString(DB.gu_member));
0780: }
0781:
0782: oStmt.setString(10, oMember.getString(DB.tx_email));
0783:
0784: if (DebugFile.trace)
0785: DebugFile.writeln("PreparedStatement.executeUpdate()");
0786:
0787: iAffected = oStmt.executeUpdate();
0788:
0789: if (DebugFile.trace)
0790: DebugFile.writeln("affected rows = "
0791: + String.valueOf(iAffected));
0792:
0793: oStmt.close();
0794:
0795: if (0 == iAffected) {
0796:
0797: if (DebugFile.trace) {
0798: sSQL = "INSERT INTO " + DB.k_x_list_members + " ("
0799: + DB.gu_list + "," + DB.tx_email + ","
0800: + DB.tx_name + "," + DB.tx_surname + ","
0801: + DB.tx_salutation + "," + DB.bo_active + ","
0802: + DB.tp_member + "," + DB.gu_company + ","
0803: + DB.gu_contact + "," + DB.id_format
0804: + ") VALUES ('" + sListGUID + "','"
0805: + oMember.getStringNull(DB.tx_email, "null")
0806: + "',?,?,?,?,?,?,'"
0807: + oMember.getStringNull(DB.gu_member, "null")
0808: + "','"
0809: + oMember.getStringNull(DB.id_format, "TXT")
0810: + "')";
0811:
0812: DebugFile.writeln("Connection.prepareStatement(" + sSQL
0813: + ")");
0814: }
0815:
0816: sSQL = "INSERT INTO " + DB.k_x_list_members + " ("
0817: + DB.gu_list + "," + DB.tx_email + "," + DB.tx_name
0818: + "," + DB.tx_surname + "," + DB.tx_salutation
0819: + "," + DB.bo_active + "," + DB.tp_member + ","
0820: + DB.gu_company + "," + DB.gu_contact + ","
0821: + DB.id_format + ") VALUES (?,?,?,?,?,?,?,?,?,?)";
0822:
0823: oStmt = oConn.prepareStatement(sSQL);
0824: oStmt.setString(1, sListGUID);
0825: oStmt.setString(2, oMember.getString(DB.tx_email));
0826: oStmt.setString(3, oMember.getStringNull(DB.tx_name, null));
0827: oStmt.setString(4, oMember.getStringNull(DB.tx_surname,
0828: null));
0829: oStmt.setString(5, oMember.getStringNull(DB.tx_salutation,
0830: null));
0831:
0832: if (oMember.getItemMap().containsKey(DB.bo_active))
0833: oStmt.setShort(6, oMember.getShort(DB.bo_active));
0834: else
0835: oStmt.setShort(6, (short) 1);
0836:
0837: if (oMember.getItemMap().containsKey(DB.tp_member)) {
0838: if (DebugFile.trace)
0839: DebugFile.writeln("member type is "
0840: + String.valueOf(oMember
0841: .getShort(DB.tp_member)));
0842:
0843: oStmt.setShort(7, oMember.getShort(DB.tp_member));
0844:
0845: if (oMember.getShort(DB.tp_member) == Company.ClassId) {
0846: oStmt.setString(8, oMember.getString(DB.gu_member));
0847: oStmt.setString(9, null);
0848: } else {
0849: oStmt.setString(8, null);
0850: oStmt.setString(9, oMember.getString(DB.gu_member));
0851: }
0852: } else {
0853:
0854: if (new Contact(getString(DB.gu_member)).exists(oConn)) {
0855: if (DebugFile.trace)
0856: DebugFile
0857: .writeln("member type automatically set to "
0858: + String
0859: .valueOf(Contact.ClassId));
0860:
0861: oStmt.setShort(7, Contact.ClassId);
0862: oStmt.setString(8, null);
0863: oStmt.setString(9, oMember.getString(DB.gu_member));
0864: } else if (new Company(getString(DB.gu_member))
0865: .exists(oConn)) {
0866: if (DebugFile.trace)
0867: DebugFile
0868: .writeln("member type automatically set to "
0869: + String
0870: .valueOf(Company.ClassId));
0871:
0872: oStmt.setShort(7, Company.ClassId);
0873: oStmt.setString(8, oMember.getString(DB.gu_member));
0874: oStmt.setString(9, null);
0875: } else {
0876: if (DebugFile.trace)
0877: DebugFile
0878: .writeln("member type automatically set to "
0879: + String
0880: .valueOf(ListMember.ClassId));
0881:
0882: oStmt.setShort(7, ListMember.ClassId);
0883: oStmt.setString(8, null);
0884: oStmt.setString(9, oMember.getString(DB.gu_member));
0885: }
0886: }
0887:
0888: oStmt.setString(10, oMember.getStringNull(DB.id_format,
0889: "TXT"));
0890:
0891: if (DebugFile.trace)
0892: DebugFile.writeln("PreparedStatement.execute()");
0893:
0894: bRetVal = oStmt.execute();
0895:
0896: oStmt.close();
0897: } else
0898: bRetVal = true;
0899:
0900: if (DebugFile.trace) {
0901: DebugFile.decIdent();
0902: DebugFile.writeln("End ListMember.store() : "
0903: + String.valueOf(bRetVal));
0904: }
0905:
0906: return bRetVal;
0907: } // store()
0908:
0909: /**
0910: * <p>Block Member</p>
0911: * <p>Add member to Black List associated to Base List.</p>
0912: * @param oConn Database Connection
0913: * @param sListGUID Base List GUID
0914: * @throws SQLException
0915: * @throws NoSuchFieldException
0916: */
0917:
0918: public void block(JDCConnection oConn, String sListGUID)
0919: throws SQLException, NoSuchFieldException {
0920:
0921: if (DebugFile.trace) {
0922: DebugFile.writeln("Begin ListMember.block([Connection], "
0923: + sListGUID + ")");
0924: DebugFile.incIdent();
0925: }
0926:
0927: DistributionList oList = new DistributionList(oConn, sListGUID);
0928:
0929: String sBlack = oList.blackList(oConn);
0930:
0931: if (null == sBlack) {
0932: sBlack = Gadgets.generateUUID();
0933:
0934: if (DebugFile.trace)
0935: DebugFile
0936: .writeln("Connection.prepareStatement(INSERT INTO "
0937: + DB.k_lists
0938: + "("
0939: + DB.gu_list
0940: + ","
0941: + DB.gu_workarea
0942: + ","
0943: + DB.tp_list
0944: + ","
0945: + DB.gu_query
0946: + ","
0947: + DB.de_list
0948: + ") VALUES('"
0949: + sBlack
0950: + "','"
0951: + getStringNull(DB.gu_workarea, "null")
0952: + "',4,'"
0953: + getStringNull(DB.gu_list, "null")
0954: + "','"
0955: + getStringNull(DB.de_list, "null")
0956: + "'))");
0957:
0958: PreparedStatement oStmt = oConn
0959: .prepareStatement("INSERT INTO " + DB.k_lists + "("
0960: + DB.gu_list + "," + DB.gu_workarea + ","
0961: + DB.tp_list + "," + DB.gu_query + ","
0962: + DB.de_list + ") VALUES(?,?,?,?,?)");
0963: oStmt.setString(1, sBlack);
0964: oStmt.setString(2, oList.getString(DB.gu_workarea));
0965: oStmt.setShort(3, DistributionList.TYPE_BLACK);
0966: oStmt.setString(4, sListGUID);
0967: oStmt.setString(5, oList.getString(DB.de_list));
0968: oStmt.executeUpdate();
0969: oStmt.close();
0970: }
0971:
0972: store(oConn, sBlack);
0973:
0974: if (DebugFile.trace) {
0975: DebugFile.decIdent();
0976: DebugFile.writeln("End ListMember.block()");
0977: }
0978: }
0979:
0980: /**
0981: * <p>Unblock Member</p>
0982: * <p>Remove member from Black List associated to Base List.</p>
0983: * @param oConn Database Connection
0984: * @param sListGUID Base List GUID
0985: * @throws SQLException
0986: */
0987:
0988: public void unblock(JDCConnection oConn, String sListGUID)
0989: throws SQLException {
0990:
0991: if (DebugFile.trace) {
0992: DebugFile.writeln("Begin ListMember.unblock([Connection], "
0993: + sListGUID + ")");
0994: DebugFile.incIdent();
0995: }
0996:
0997: DistributionList oList = new DistributionList(oConn, sListGUID);
0998:
0999: String sBlack = oList.blackList(oConn);
1000:
1001: if (null != sBlack) {
1002: if (DebugFile.trace)
1003: DebugFile
1004: .writeln("Connection.prepareStatement(DELETE FROM "
1005: + DB.k_x_list_members
1006: + " WHERE "
1007: + DB.gu_list
1008: + "='"
1009: + sBlack
1010: + "' AND "
1011: + DB.tx_email
1012: + "='"
1013: + getStringNull(DB.tx_email, "null")
1014: + "')");
1015:
1016: PreparedStatement oDlte = oConn
1017: .prepareStatement("DELETE FROM "
1018: + DB.k_x_list_members + " WHERE "
1019: + DB.gu_list + "=? AND " + DB.tx_email
1020: + "=?");
1021: oDlte.setString(1, sBlack);
1022: oDlte.setString(2, getString(DB.tx_email));
1023: oDlte.executeUpdate();
1024: oDlte.close();
1025: }
1026:
1027: if (DebugFile.trace) {
1028: DebugFile.decIdent();
1029: DebugFile.writeln("End ListMember.unblock()");
1030: }
1031: } // unblock
1032:
1033: // **********************************************************
1034: // Constantes Publicas
1035:
1036: public static final short ClassId = 95;
1037:
1038: }
|