connectivity/source/commontools/DateConversion.cxx

   1 /*************************************************************************
   2  *
   3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
   4  *
   5  * Copyright 2008 by Sun Microsystems, Inc.
   6  *
   7  * OpenOffice.org - a multi-platform office productivity suite
   8  *
   9  * $RCSfile: DateConversion.cxx,v $
  10  * $Revision: 1.18 $
  11  *
  12  * This file is part of OpenOffice.org.
  13  *
  14  * OpenOffice.org is free software: you can redistribute it and/or modify
  15  * it under the terms of the GNU Lesser General Public License version 3
  16  * only, as published by the Free Software Foundation.
  17  *
  18  * OpenOffice.org is distributed in the hope that it will be useful,
  19  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  20  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21  * GNU Lesser General Public License version 3 for more details
  22  * (a copy is included in the LICENSE file that accompanied this code).
  23  *
  24  * You should have received a copy of the GNU Lesser General Public License
  25  * version 3 along with OpenOffice.org.  If not, see
  26  * <http://www.openoffice.org/license.html>
  27  * for a copy of the LGPLv3 License.
  28  *
  29  ************************************************************************/
  30
  31 // MARKER(update_precomp.py): autogen include statement, do not remove
  32 #include "precompiled_connectivity.hxx"
  33
  34
  35 #include "connectivity/dbconversion.hxx"
  36 #include <connectivity/dbtools.hxx>
  37 #include <com/sun/star/script/XTypeConverter.hpp>
  38 #include <com/sun/star/sdbc/DataType.hpp>
  39 #include <com/sun/star/util/NumberFormat.hpp>
  40 #include <com/sun/star/util/XNumberFormatTypes.hpp>
  41 #include <com/sun/star/sdb/XColumnUpdate.hpp>
  42 #include <com/sun/star/sdb/XColumn.hpp>
  43 #include <com/sun/star/beans/XPropertySet.hpp>
  44 #include <comphelper/extract.hxx>
  45 #include "TConnection.hxx"
  46 #include "diagnose_ex.h"
  47 #include <comphelper/numbers.hxx>
  48 #include <rtl/ustrbuf.hxx>
  49
  50
  51 using namespace ::connectivity;
  52 using namespace ::comphelper;
  53 using namespace ::com::sun::star::script;
  54 using namespace ::com::sun::star::sdb;
  55 using namespace ::com::sun::star::sdbc;
  56 using namespace ::dbtools;
  57 using namespace ::com::sun::star::lang;
  58 using namespace ::com::sun::star::beans;
  59 using namespace ::com::sun::star::util;
  60 using namespace ::com::sun::star::uno;
  61 using namespace ::com::sun::star::util;
  62 using namespace ::com::sun::star::beans;
  63 // -----------------------------------------------------------------------------
  64 ::rtl::OUString DBTypeConversion::toSQLString(sal_Int32 eType, const Any& _rVal, sal_Bool bQuote,
  65                                               const Reference< XTypeConverter >&  _rxTypeConverter)
  66 {
  67     ::rtl::OUStringBuffer aRet;
  68     if (_rVal.hasValue())
  69     {
  70         try
  71         {
  72             switch (eType)
  73             {
  74                 case DataType::INTEGER:
  75                 case DataType::BIT:
  76                 case DataType::BOOLEAN:
  77                 case DataType::TINYINT:
  78                 case DataType::SMALLINT:
  79                     if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_BOOLEAN)
  80                     {
  81                         if (::cppu::any2bool(_rVal))
  82                             aRet.appendAscii("1");
  83                         else
  84                             aRet.appendAscii("0");
  85                     }
  86                     else
  87                     {
  88                         ::rtl::OUString sTemp;
  89                         _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= sTemp;
  90                         aRet.append(sTemp);
  91                     }
  92                     break;
  93                 case DataType::CHAR:
  94                 case DataType::VARCHAR:
  95                 case DataType::LONGVARCHAR:
  96                     if (bQuote)
  97                         aRet.appendAscii("'");
  98                     {
  99                         ::rtl::OUString aTemp;
 100                         _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= aTemp;
 101                         sal_Int32 nIndex = (sal_Int32)-1;
 102                         const ::rtl::OUString sQuot(RTL_CONSTASCII_USTRINGPARAM("\'"));
 103                         const ::rtl::OUString sQuotToReplace(RTL_CONSTASCII_USTRINGPARAM("\'\'"));
 104                         do
 105                         {
 106                             nIndex += 2;
 107                             nIndex = aTemp.indexOf(sQuot,nIndex);
 108                             if(nIndex != -1)
 109                                 aTemp = aTemp.replaceAt(nIndex,sQuot.getLength(),sQuotToReplace);
 110                         } while (nIndex != -1);
 111
 112                         aRet.append(aTemp);
 113                     }
 114                     if (bQuote)
 115                         aRet.appendAscii("'");
 116                     break;
 117                 case DataType::REAL:
 118                 case DataType::DOUBLE:
 119                 case DataType::DECIMAL:
 120                 case DataType::NUMERIC:
 121                 case DataType::BIGINT:
 122                 default:
 123                     {
 124                         ::rtl::OUString sTemp;
 125                         _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= sTemp;
 126                         aRet.append(sTemp);
 127                     }
 128                     break;
 129                 case DataType::TIMESTAMP:
 130                 {
 131                     DateTime aDateTime;
 132
 133                     // check if this is really a timestamp or only a date
 134                     if ( _rVal >>= aDateTime )
 135                     {
 136                         if (bQuote)
 137                             aRet.appendAscii("{TS '");
 138                         aRet.append(DBTypeConversion::toDateTimeString(aDateTime));
 139                         if (bQuote)
 140                             aRet.appendAscii("'}");
 141                         break;
 142                     }
 143                     break;
 144                 }
 145                 case DataType::DATE:
 146                 {
 147                     Date aDate;
 148                     OSL_VERIFY_RES( _rVal >>= aDate, "DBTypeConversion::toSQLString: _rVal is not date!");
 149                     if (bQuote)
 150                         aRet.appendAscii("{D '");
 151                     aRet.append(DBTypeConversion::toDateString(aDate));
 152                     if (bQuote)
 153                         aRet.appendAscii("'}");
 154                 }       break;
 155                 case DataType::TIME:
 156                 {
 157                     Time aTime;
 158                     OSL_VERIFY_RES( _rVal >>= aTime,"DBTypeConversion::toSQLString: _rVal is not time!");
 159                     if (bQuote)
 160                         aRet.appendAscii("{T '");
 161                     aRet.append(DBTypeConversion::toTimeString(aTime));
 162                     if (bQuote)
 163                         aRet.appendAscii("'}");
 164                 } break;
 165             }
 166         }
 167         catch ( const Exception&  )
 168         {
 169             OSL_ENSURE(0,"TypeConversion Error");
 170         }
 171     }
 172     else
 173         aRet.appendAscii(" NULL ");
 174     return aRet.makeStringAndClear();
 175 }
 176 // -----------------------------------------------------------------------------
 177 Date DBTypeConversion::getNULLDate(const Reference< XNumberFormatsSupplier > &xSupplier)
 178 {
 179     OSL_ENSURE(xSupplier.is(), "getNULLDate : the formatter doesn't implement a supplier !");
 180     if (xSupplier.is())
 181     {
 182         try
 183         {
 184             // get the null date
 185             Date aDate;
 186             xSupplier->getNumberFormatSettings()->getPropertyValue(::rtl::OUString::createFromAscii("NullDate")) >>= aDate;
 187             return aDate;
 188         }
 189         catch ( const Exception&  )
 190         {
 191         }
 192     }
 193
 194     return getStandardDate();
 195 }
 196 // -----------------------------------------------------------------------------
 197 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
 198                                 const Reference<XNumberFormatter>& xFormatter,
 199                                 const Date& rNullDate,
 200                                 const ::rtl::OUString& rString,
 201                                 sal_Int32 nKey,
 202                                 sal_Int16 nFieldType,
 203                                 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
 204 {
 205     double fValue = 0;
 206     if (rString.getLength())
 207     {
 208             // Muss der String formatiert werden?
 209         sal_Int16 nTypeClass = nKeyType & ~NumberFormat::DEFINED;
 210         sal_Bool bTextFormat = nTypeClass == NumberFormat::TEXT;
 211         sal_Int32 nKeyToUse  = bTextFormat ? 0 : nKey;
 212         sal_Int16 nRealUsedTypeClass = nTypeClass;
 213             // bei einem Text-Format muessen wir dem Formatter etwas mehr Freiheiten einraeumen, sonst
 214             // wirft convertStringToNumber eine NotNumericException
 215         try
 216         {
 217             fValue = xFormatter->convertStringToNumber(nKeyToUse, rString);
 218             sal_Int32 nRealUsedKey = xFormatter->detectNumberFormat(0, rString);
 219             if (nRealUsedKey != nKeyToUse)
 220                 nRealUsedTypeClass = getNumberFormatType(xFormatter, nRealUsedKey) & ~NumberFormat::DEFINED;
 221
 222             // und noch eine Sonderbehandlung, diesmal fuer Prozent-Formate
 223             if ((NumberFormat::NUMBER == nRealUsedTypeClass) && (NumberFormat::PERCENT == nTypeClass))
 224             {   // die Formatierung soll eigentlich als Prozent erfolgen, aber der String stellt nur eine
 225                 // einfache Nummer dar -> anpassen
 226                 ::rtl::OUString sExpanded(rString);
 227                 static ::rtl::OUString s_sPercentSymbol = ::rtl::OUString::createFromAscii("%");
 228                     // need a method to add a sal_Unicode to a string, 'til then we use a static string
 229                 sExpanded += s_sPercentSymbol;
 230                 fValue = xFormatter->convertStringToNumber(nKeyToUse, sExpanded);
 231             }
 232
 233             switch (nRealUsedTypeClass)
 234             {
 235                 case NumberFormat::DATE:
 236                 case NumberFormat::DATETIME:
 237                 case NumberFormat::TIME:
 238                     DBTypeConversion::setValue(xVariant,rNullDate,fValue,nRealUsedTypeClass);
 239                     //  xVariant->updateDouble(toStandardDbDate(rNullDate, fValue));
 240                     break;
 241                 case NumberFormat::CURRENCY:
 242                 case NumberFormat::NUMBER:
 243                 case NumberFormat::SCIENTIFIC:
 244                 case NumberFormat::FRACTION:
 245                 case NumberFormat::PERCENT:
 246                     xVariant->updateDouble(fValue);
 247                     break;
 248                 default:
 249                     xVariant->updateString(rString);
 250             }
 251         }
 252         catch(const Exception& )
 253         {
 254             xVariant->updateString(rString);
 255         }
 256     }
 257     else
 258     {
 259         switch (nFieldType)
 260         {
 261             case ::com::sun::star::sdbc::DataType::CHAR:
 262             case ::com::sun::star::sdbc::DataType::VARCHAR:
 263             case ::com::sun::star::sdbc::DataType::LONGVARCHAR:
 264                 xVariant->updateString(rString);
 265                 break;
 266             default:
 267                 xVariant->updateNull();
 268         }
 269     }
 270 }
 271
 272 //------------------------------------------------------------------------------
 273 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
 274                                 const Date& rNullDate,
 275                                 const double& rValue,
 276                                 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
 277 {
 278     switch (nKeyType & ~NumberFormat::DEFINED)
 279     {
 280         case NumberFormat::DATE:
 281             xVariant->updateDate(toDate( rValue, rNullDate));
 282             break;
 283         case NumberFormat::DATETIME:
 284             xVariant->updateTimestamp(toDateTime(rValue,rNullDate));
 285             break;
 286         case NumberFormat::TIME:
 287             xVariant->updateTime(toTime(rValue));
 288             break;
 289         default:
 290             {
 291                 double nValue = rValue;
 292 //                              Reference<XPropertySet> xProp(xVariant,UNO_QUERY);
 293 //                              if (    xProp.is()
 294 //                                      &&      xProp->getPropertySetInfo()->hasPropertyByName(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))
 295 //                                      && !::comphelper::getBOOL(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))) )
 296 //                              {
 297 //                                      switch (::comphelper::getINT32(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE))))
 298 //                                      {
 299 //                                              case DataType::TINYINT:
 300 //                                                      nValue = static_cast<sal_uInt8>(rValue);
 301 //                                                      break;
 302 //                                              case DataType::SMALLINT:
 303 //                                                      nValue = static_cast<sal_uInt16>(rValue);
 304 //                                                      break;
 305 //                                              case DataType::INTEGER:
 306 //                                                      nValue = static_cast<sal_uInt32>(rValue);
 307 //                                                      break;
 308 //                                              case DataType::BIGINT:
 309 //                                                      nValue = static_cast<sal_uInt64>(rValue);
 310 //                                                      break;
 311 //                                      }
 312 //                              }
 313                 xVariant->updateDouble(nValue);
 314             }
 315     }
 316 }
 317
 318 //------------------------------------------------------------------------------
 319 double DBTypeConversion::getValue(const Reference<XColumn>& xVariant,
 320                                   const Date& rNullDate,
 321                                   sal_Int16 nKeyType)
 322 {
 323     try
 324     {
 325         switch (nKeyType & ~NumberFormat::DEFINED)
 326         {
 327             case NumberFormat::DATE:
 328                 return toDouble( xVariant->getDate(), rNullDate);
 329             case NumberFormat::DATETIME:
 330                 return toDouble(xVariant->getTimestamp(),rNullDate);
 331             case NumberFormat::TIME:
 332                 return toDouble(xVariant->getTime());
 333             default:
 334             {
 335                 Reference<XPropertySet> xProp(xVariant,UNO_QUERY);
 336                 if (    xProp.is()
 337                     &&  xProp->getPropertySetInfo()->hasPropertyByName(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))
 338                     && !::comphelper::getBOOL(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))) )
 339                 {
 340                     switch (::comphelper::getINT32(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE))))
 341                     {
 342                         case DataType::TINYINT:
 343                             return static_cast<double>(static_cast<sal_uInt8>(xVariant->getByte()));
 344                         case DataType::SMALLINT:
 345                             return static_cast<double>(static_cast<sal_uInt16>(xVariant->getShort()));
 346                         case DataType::INTEGER:
 347                             return static_cast<double>(static_cast<sal_uInt32>(xVariant->getInt()));
 348                         case DataType::BIGINT:
 349                             return static_cast<double>(static_cast<sal_uInt64>(xVariant->getLong()));
 350                     }
 351                 }
 352
 353                 return xVariant->getDouble();
 354             }
 355         }
 356     }
 357     catch(const Exception& )
 358     {
 359         return 0.0;
 360     }
 361 }
 362 //------------------------------------------------------------------------------
 363 ::rtl::OUString DBTypeConversion::getValue(const Reference< XPropertySet>& _xColumn,
 364                                            const Reference<XNumberFormatter>& _xFormatter,
 365                                            const ::com::sun::star::lang::Locale& _rLocale,
 366                                            const Date& _rNullDate)
 367 {
 368     OSL_ENSURE(_xColumn.is() && _xFormatter.is(), "DBTypeConversion::getValue: invalid arg !");
 369     if (!_xColumn.is() || !_xFormatter.is())
 370         return ::rtl::OUString();
 371
 372     sal_Int32 nKey(0);
 373     try
 374     {
 375         _xColumn->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FORMATKEY)) >>= nKey;
 376     }
 377     catch (const Exception& )
 378     {
 379         OSL_ENSURE(false, "DBTypeConversion::getValue: caught an exception while asking for the format key!");
 380     }
 381
 382     if (!nKey)
 383     {
 384         Reference<XNumberFormats> xFormats( _xFormatter->getNumberFormatsSupplier()->getNumberFormats() );
 385         Reference<XNumberFormatTypes> xTypeList(_xFormatter->getNumberFormatsSupplier()->getNumberFormats(), UNO_QUERY);
 386
 387         nKey = ::dbtools::getDefaultNumberFormat(_xColumn,
 388                                            Reference< XNumberFormatTypes > (xFormats, UNO_QUERY),
 389                                            _rLocale);
 390
 391     }
 392
 393     sal_Int16 nKeyType = getNumberFormatType(_xFormatter, nKey) & ~NumberFormat::DEFINED;
 394
 395     return DBTypeConversion::getValue(Reference< XColumn > (_xColumn, UNO_QUERY), _xFormatter, _rNullDate, nKey, nKeyType);
 396 }
 397
 398 //------------------------------------------------------------------------------
 399 ::rtl::OUString DBTypeConversion::getValue(const Reference<XColumn>& xVariant,
 400                                    const Reference<XNumberFormatter>& xFormatter,
 401                                    const Date& rNullDate,
 402                                    sal_Int32 nKey,
 403                                    sal_Int16 nKeyType)
 404 {
 405     ::rtl::OUString aString;
 406     if (xVariant.is())
 407     {
 408         try
 409         {
 410             switch (nKeyType & ~NumberFormat::DEFINED)
 411             {
 412                 case NumberFormat::DATE:
 413                 case NumberFormat::DATETIME:
 414                 {
 415                     // get a value which represents the given date, relative to the given null date
 416                     double fValue = getValue(xVariant, rNullDate, nKeyType);
 417                     if ( !xVariant->wasNull() )
 418                     {
 419                          // get the null date of the formatter
 420                          Date aFormatterNullDate( rNullDate );
 421                          try
 422                          {
 423                              Reference< XPropertySet > xFormatterSettings;
 424                              Reference< XNumberFormatsSupplier > xSupplier( xFormatter->getNumberFormatsSupplier( ) );
 425                              if ( xSupplier.is() )
 426                                  xFormatterSettings = xSupplier->getNumberFormatSettings();
 427                              if ( xFormatterSettings.is() )
 428                                  xFormatterSettings->getPropertyValue( ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "NullDate" ) ) ) >>= aFormatterNullDate;
 429                          }
 430                          catch( const Exception& )
 431                          {
 432                             OSL_ENSURE( sal_False, "DBTypeConversion::getValue: caught an exception while retrieving the formatter's NullDate!" );
 433                          }
 434                          // get a value which represents the given date, relative to the null date of the formatter
 435                          fValue -= toDays( rNullDate, aFormatterNullDate );
 436                          // format this value
 437                         aString = xFormatter->convertNumberToString( nKey, fValue );
 438                     }
 439                 }
 440                 break;
 441                 case NumberFormat::TIME:
 442                 case NumberFormat::NUMBER:
 443                 case NumberFormat::SCIENTIFIC:
 444                 case NumberFormat::FRACTION:
 445                 case NumberFormat::PERCENT:
 446                 {
 447                     double fValue = xVariant->getDouble();
 448                     if (!xVariant->wasNull())
 449                         aString = xFormatter->convertNumberToString(nKey, fValue);
 450                 }       break;
 451                 case NumberFormat::CURRENCY:
 452                 {
 453                     double fValue = xVariant->getDouble();
 454                     if (!xVariant->wasNull())
 455                         aString = xFormatter->getInputString(nKey, fValue);
 456                 }       break;
 457                 case NumberFormat::TEXT:
 458                     aString = xFormatter->formatString(nKey, xVariant->getString());
 459                     break;
 460                 default:
 461                     aString = xVariant->getString();
 462             }
 463         }
 464         catch(const Exception& )
 465         {
 466             aString = xVariant->getString();
 467         }
 468     }
 469     return aString;
 470 }
 471 //------------------------------------------------------------------