connectivity/source/commontools/DateConversion.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include <connectivity/dbconversion.hxx>
  21 #include <connectivity/dbtools.hxx>
  22 #include <com/sun/star/script/XTypeConverter.hpp>
  23 #include <com/sun/star/sdbc/DataType.hpp>
  24 #include <com/sun/star/util/NumberFormat.hpp>
  25 #include <com/sun/star/util/XNumberFormatTypes.hpp>
  26 #include <com/sun/star/sdb/XColumnUpdate.hpp>
  27 #include <com/sun/star/sdb/XColumn.hpp>
  28 #include <com/sun/star/beans/XPropertySet.hpp>
  29 #include <comphelper/extract.hxx>
  30 #include "TConnection.hxx"
  31 #include "diagnose_ex.h"
  32 #include <comphelper/numbers.hxx>
  33 #include <rtl/ustrbuf.hxx>
  34 #include <tools/diagnose_ex.h>
  35
  36
  37 using namespace ::connectivity;
  38 using namespace ::comphelper;
  39 using namespace ::com::sun::star::script;
  40 using namespace ::com::sun::star::sdb;
  41 using namespace ::com::sun::star::sdbc;
  42 using namespace ::dbtools;
  43 using namespace ::com::sun::star::lang;
  44 using namespace ::com::sun::star::beans;
  45 using namespace ::com::sun::star::util;
  46 using namespace ::com::sun::star::uno;
  47
  48 OUString DBTypeConversion::toSQLString(sal_Int32 eType, const Any& _rVal, bool bQuote,
  49                                               const Reference< XTypeConverter >&  _rxTypeConverter)
  50 {
  51     OUStringBuffer aRet;
  52     if (_rVal.hasValue())
  53     {
  54         try
  55         {
  56             switch (eType)
  57             {
  58                 case DataType::INTEGER:
  59                 case DataType::BIT:
  60                 case DataType::BOOLEAN:
  61                 case DataType::TINYINT:
  62                 case DataType::SMALLINT:
  63                     if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_BOOLEAN)
  64                     {
  65                         if (::cppu::any2bool(_rVal))
  66                             aRet.appendAscii("1");
  67                         else
  68                             aRet.appendAscii("0");
  69                     }
  70                     else
  71                     {
  72                         OUString sTemp;
  73                         _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= sTemp;
  74                         aRet.append(sTemp);
  75                     }
  76                     break;
  77                 case DataType::CHAR:
  78                 case DataType::VARCHAR:
  79                 case DataType::LONGVARCHAR:
  80                     if (bQuote)
  81                         aRet.appendAscii("'");
  82                     {
  83                         OUString aTemp;
  84                         _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= aTemp;
  85                         sal_Int32 nIndex = (sal_Int32)-1;
  86                         const OUString sQuot("\'");
  87                         const OUString sQuotToReplace("\'\'");
  88                         do
  89                         {
  90                             nIndex += 2;
  91                             nIndex = aTemp.indexOf(sQuot,nIndex);
  92                             if(nIndex != -1)
  93                                 aTemp = aTemp.replaceAt(nIndex,sQuot.getLength(),sQuotToReplace);
  94                         } while (nIndex != -1);
  95
  96                         aRet.append(aTemp);
  97                     }
  98                     if (bQuote)
  99                         aRet.appendAscii("'");
 100                     break;
 101                 case DataType::REAL:
 102                 case DataType::DOUBLE:
 103                 case DataType::DECIMAL:
 104                 case DataType::NUMERIC:
 105                 case DataType::BIGINT:
 106                 default:
 107                     {
 108                         OUString sTemp;
 109                         _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= sTemp;
 110                         aRet.append(sTemp);
 111                     }
 112                     break;
 113                 case DataType::TIMESTAMP:
 114                 {
 115                     DateTime aDateTime;
 116                     bool bOk = false;
 117                     if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_DOUBLE)
 118                     {
 119                         double nValue = 0.0;
 120                        _rVal >>= nValue;
 121                        aDateTime = DBTypeConversion::toDateTime(nValue);
 122                        bOk = true;
 123                     }
 124                     else if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_STRING)
 125                     {
 126                         OUString sValue;
 127                        _rVal >>= sValue;
 128                        aDateTime = DBTypeConversion::toDateTime(sValue);
 129                        bOk = true;
 130                     }
 131                     else
 132                         bOk = _rVal >>= aDateTime;
 133
 134                     OSL_VERIFY_RES( bOk, "DBTypeConversion::toSQLString: _rVal is not datetime!");
 135                     // check if this is really a timestamp or only a date
 136                     if ( bOk )
 137                     {
 138                         if (bQuote)
 139                             aRet.appendAscii("{ts '");
 140                         aRet.append(DBTypeConversion::toDateTimeString(aDateTime));
 141                         if (bQuote)
 142                             aRet.appendAscii("'}");
 143                         break;
 144                     }
 145                     break;
 146                 }
 147                 case DataType::DATE:
 148                 {
 149                     Date aDate;
 150                     bool bOk = false;
 151                     if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_DOUBLE)
 152                     {
 153                         double nValue = 0.0;
 154                        _rVal >>= nValue;
 155                        aDate = DBTypeConversion::toDate(nValue);
 156                        bOk = true;
 157                     }
 158                     else if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_STRING)
 159                     {
 160                         OUString sValue;
 161                        _rVal >>= sValue;
 162                        aDate = DBTypeConversion::toDate(sValue);
 163                        bOk = true;
 164                     }
 165                     else
 166                         bOk = _rVal >>= aDate;
 167                     OSL_VERIFY_RES( bOk, "DBTypeConversion::toSQLString: _rVal is not date!");
 168                     if (bQuote)
 169                         aRet.appendAscii("{d '");
 170                     aRet.append(DBTypeConversion::toDateString(aDate));
 171                     if (bQuote)
 172                         aRet.appendAscii("'}");
 173                 }   break;
 174                 case DataType::TIME:
 175                 {
 176                     css::util::Time aTime;
 177                     bool bOk = false;
 178                     if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_DOUBLE)
 179                     {
 180                         double nValue = 0.0;
 181                        _rVal >>= nValue;
 182                        aTime = DBTypeConversion::toTime(nValue);
 183                        bOk = true;
 184                     }
 185                     else if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_STRING)
 186                     {
 187                         OUString sValue;
 188                        _rVal >>= sValue;
 189                        aTime = DBTypeConversion::toTime(sValue);
 190                        bOk = true;
 191                     }
 192                     else
 193                         bOk = _rVal >>= aTime;
 194                     OSL_VERIFY_RES( bOk,"DBTypeConversion::toSQLString: _rVal is not time!");
 195                     if (bQuote)
 196                         aRet.appendAscii("{t '");
 197                     aRet.append(DBTypeConversion::toTimeString(aTime));
 198                     if (bQuote)
 199                         aRet.appendAscii("'}");
 200                 } break;
 201             }
 202         }
 203         catch ( const Exception&  )
 204         {
 205             OSL_FAIL("TypeConversion Error");
 206         }
 207     }
 208     else
 209         aRet.appendAscii(" NULL ");
 210     return aRet.makeStringAndClear();
 211 }
 212
 213 Date DBTypeConversion::getNULLDate(const Reference< XNumberFormatsSupplier > &xSupplier)
 214 {
 215     OSL_ENSURE(xSupplier.is(), "getNULLDate : the formatter doesn't implement a supplier !");
 216     if (xSupplier.is())
 217     {
 218         try
 219         {
 220             // get the null date
 221             Date aDate;
 222             xSupplier->getNumberFormatSettings()->getPropertyValue("NullDate") >>= aDate;
 223             return aDate;
 224         }
 225         catch ( const Exception&  )
 226         {
 227         }
 228     }
 229
 230     return getStandardDate();
 231 }
 232
 233 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
 234                                 const Reference<XNumberFormatter>& xFormatter,
 235                                 const Date& rNullDate,
 236                                 const OUString& rString,
 237                                 sal_Int32 nKey,
 238                                 sal_Int16 nFieldType,
 239                                 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
 240 {
 241     if (!rString.isEmpty())
 242     {
 243         // Does the String need to be formatted?
 244         sal_Int16 nTypeClass = nKeyType & ~NumberFormat::DEFINED;
 245         bool bTextFormat = nTypeClass == NumberFormat::TEXT;
 246         sal_Int32 nKeyToUse  = bTextFormat ? 0 : nKey;
 247         sal_Int16 nRealUsedTypeClass = nTypeClass;
 248         // for a Text-Format the formatter needs some more freedom, otherwise
 249         // convertStringToNumber will throw a NotNumericException
 250         try
 251         {
 252             double fValue = xFormatter->convertStringToNumber(nKeyToUse, rString);
 253             Reference< XNumberFormats > xFormats(xFormatter->getNumberFormatsSupplier()->getNumberFormats());
 254             Reference< XNumberFormatTypes > xFormatTypes(xFormats, UNO_QUERY);
 255             sal_Int32 nStandardKey(0);
 256             if(xFormatTypes.is())
 257             {
 258                 const Reference< XPropertySet > xFormatProps(xFormats->getByKey(nKeyToUse));
 259                 if (xFormatProps.is())
 260                 {
 261                     css::lang::Locale loc;
 262                     if (xFormatProps->getPropertyValue("Locale") >>= loc)
 263                         nStandardKey = xFormatTypes->getStandardIndex(loc);
 264                     else
 265                     {
 266                         assert(false);
 267                     }
 268                 }
 269                 else
 270                 {
 271                     SAL_WARN("connectivity.commontools", "no format by key " << nKeyToUse);
 272                 }
 273             }
 274             else
 275             {
 276                 assert(false);
 277             }
 278             // Why use nStandardKey rather than nKeyToUse here? I'm not sure, but "it was always like that".
 279             // Previously had hardcoded 0 instead of nStandardKey, which led to problems with dates
 280             // because of differences M/D/Y vs D/M/Y. This at least fixes those problems, but possibly
 281             // nKeyToUse is an even better choice than nStandardKey.
 282             // OTOH, using nKeyToUse nullifies the special treatment for percent formats,
 283             //       leading to "5" (in a percent format) to be understood as "500%" instead of "5%".
 284             sal_Int32 nRealUsedKey = xFormatter->detectNumberFormat(nStandardKey, rString);
 285             if (nRealUsedKey != nKeyToUse)
 286                 nRealUsedTypeClass = getNumberFormatType(xFormatter, nRealUsedKey) & ~NumberFormat::DEFINED;
 287
 288             // and again a special treatment, this time for percent formats
 289             if ((NumberFormat::NUMBER == nRealUsedTypeClass) && (NumberFormat::PERCENT == nTypeClass))
 290             {   // formatting should be "percent", but the String provides just a simple number -> adjust
 291                 OUString sExpanded = rString + "%";
 292                 fValue = xFormatter->convertStringToNumber(nKeyToUse, sExpanded);
 293             }
 294
 295             switch (nRealUsedTypeClass)
 296             {
 297                 case NumberFormat::DATE:
 298                 case NumberFormat::DATETIME:
 299                 case NumberFormat::TIME:
 300                     DBTypeConversion::setValue(xVariant,rNullDate,fValue,nRealUsedTypeClass);
 301                     //  xVariant->updateDouble(toStandardDbDate(rNullDate, fValue));
 302                     break;
 303                 case NumberFormat::CURRENCY:
 304                 case NumberFormat::NUMBER:
 305                 case NumberFormat::SCIENTIFIC:
 306                 case NumberFormat::FRACTION:
 307                 case NumberFormat::PERCENT:
 308                     xVariant->updateDouble(fValue);
 309                     break;
 310                 default:
 311                     xVariant->updateString(rString);
 312             }
 313         }
 314         catch(const Exception& )
 315         {
 316             xVariant->updateString(rString);
 317         }
 318     }
 319     else
 320     {
 321         switch (nFieldType)
 322         {
 323             case ::com::sun::star::sdbc::DataType::CHAR:
 324             case ::com::sun::star::sdbc::DataType::VARCHAR:
 325             case ::com::sun::star::sdbc::DataType::LONGVARCHAR:
 326                 xVariant->updateString(rString);
 327                 break;
 328             default:
 329                 xVariant->updateNull();
 330         }
 331     }
 332 }
 333
 334
 335 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
 336                                 const Date& rNullDate,
 337                                 const double& rValue,
 338                                 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
 339 {
 340     switch (nKeyType & ~NumberFormat::DEFINED)
 341     {
 342         case NumberFormat::DATE:
 343             xVariant->updateDate(toDate( rValue, rNullDate));
 344             break;
 345         case NumberFormat::DATETIME:
 346             xVariant->updateTimestamp(toDateTime(rValue,rNullDate));
 347             break;
 348         case NumberFormat::TIME:
 349             xVariant->updateTime(toTime(rValue));
 350             break;
 351         default:
 352             {
 353                 double nValue = rValue;
 354 //              Reference<XPropertySet> xProp(xVariant,UNO_QUERY);
 355 //              if (    xProp.is()
 356 //                  &&  xProp->getPropertySetInfo()->hasPropertyByName(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))
 357 //                  && !::comphelper::getBOOL(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))) )
 358 //              {
 359 //                  switch (::comphelper::getINT32(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE))))
 360 //                  {
 361 //                      case DataType::TINYINT:
 362 //                          nValue = static_cast<sal_uInt8>(rValue);
 363 //                          break;
 364 //                      case DataType::SMALLINT:
 365 //                          nValue = static_cast<sal_uInt16>(rValue);
 366 //                          break;
 367 //                      case DataType::INTEGER:
 368 //                          nValue = static_cast<sal_uInt32>(rValue);
 369 //                          break;
 370 //                      case DataType::BIGINT:
 371 //                          nValue = static_cast<sal_uInt64>(rValue);
 372 //                          break;
 373 //                  }
 374 //              }
 375                 xVariant->updateDouble(nValue);
 376             }
 377     }
 378 }
 379
 380
 381 double DBTypeConversion::getValue( const Reference< XColumn >& i_column, const Date& i_relativeToNullDate )
 382 {
 383     try
 384     {
 385         const Reference< XPropertySet > xProp( i_column, UNO_QUERY_THROW );
 386
 387         const sal_Int32 nColumnType = ::comphelper::getINT32( xProp->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex( PROPERTY_ID_TYPE ) ) );
 388         switch ( nColumnType )
 389         {
 390         case DataType::DATE:
 391             return toDouble( i_column->getDate(), i_relativeToNullDate );
 392
 393         case DataType::TIME:
 394             return toDouble( i_column->getTime() );
 395
 396         case DataType::TIMESTAMP:
 397             return toDouble( i_column->getTimestamp(), i_relativeToNullDate );
 398
 399         default:
 400             {
 401                 bool bIsSigned = true;
 402                 OSL_VERIFY( xProp->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex( PROPERTY_ID_ISSIGNED ) ) >>= bIsSigned );
 403                 if ( !bIsSigned )
 404                 {
 405                     switch ( nColumnType)
 406                     {
 407                         case DataType::TINYINT:
 408                             return static_cast<double>(static_cast<sal_uInt8>(i_column->getByte()));
 409                         case DataType::SMALLINT:
 410                             return static_cast<double>(static_cast<sal_uInt16>(i_column->getShort()));
 411                         case DataType::INTEGER:
 412                             return static_cast<double>(static_cast<sal_uInt32>(i_column->getInt()));
 413                         case DataType::BIGINT:
 414                             return static_cast<double>(static_cast<sal_uInt64>(i_column->getLong()));
 415                     }
 416                 }
 417             }
 418             return i_column->getDouble();
 419         }
 420     }
 421     catch( const Exception& )
 422     {
 423         DBG_UNHANDLED_EXCEPTION();
 424         return 0.0;
 425     }
 426 }
 427
 428 OUString DBTypeConversion::getFormattedValue(const Reference< XPropertySet>& _xColumn,
 429                                            const Reference<XNumberFormatter>& _xFormatter,
 430                                            const ::com::sun::star::lang::Locale& _rLocale,
 431                                            const Date& _rNullDate)
 432 {
 433     OSL_ENSURE(_xColumn.is() && _xFormatter.is(), "DBTypeConversion::getFormattedValue: invalid arg !");
 434     if (!_xColumn.is() || !_xFormatter.is())
 435         return OUString();
 436
 437     sal_Int32 nKey(0);
 438     try
 439     {
 440         _xColumn->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FORMATKEY)) >>= nKey;
 441     }
 442     catch (const Exception& )
 443     {
 444         OSL_FAIL("DBTypeConversion::getValue: caught an exception while asking for the format key!");
 445     }
 446
 447     if (!nKey)
 448     {
 449         Reference<XNumberFormats> xFormats( _xFormatter->getNumberFormatsSupplier()->getNumberFormats() );
 450         Reference<XNumberFormatTypes> xTypeList(_xFormatter->getNumberFormatsSupplier()->getNumberFormats(), UNO_QUERY);
 451
 452         nKey = ::dbtools::getDefaultNumberFormat(_xColumn,
 453                                            Reference< XNumberFormatTypes > (xFormats, UNO_QUERY),
 454                                            _rLocale);
 455
 456     }
 457
 458     sal_Int16 nKeyType = getNumberFormatType(_xFormatter, nKey) & ~NumberFormat::DEFINED;
 459
 460     return DBTypeConversion::getFormattedValue(Reference< XColumn > (_xColumn, UNO_QUERY), _xFormatter, _rNullDate, nKey, nKeyType);
 461 }
 462
 463
 464 OUString DBTypeConversion::getFormattedValue(const Reference<XColumn>& xVariant,
 465                                    const Reference<XNumberFormatter>& xFormatter,
 466                                    const Date& rNullDate,
 467                                    sal_Int32 nKey,
 468                                    sal_Int16 nKeyType)
 469 {
 470     OUString aString;
 471     if (xVariant.is())
 472     {
 473         try
 474         {
 475             switch (nKeyType & ~NumberFormat::DEFINED)
 476             {
 477                 case NumberFormat::DATE:
 478                 case NumberFormat::DATETIME:
 479                 {
 480                     // get a value which represents the given date, relative to the given null date
 481                     double fValue = getValue( xVariant, rNullDate );
 482                     if ( !xVariant->wasNull() )
 483                     {
 484                          // get the null date of the formatter
 485                          Date aFormatterNullDate( rNullDate );
 486                          try
 487                          {
 488                              Reference< XNumberFormatsSupplier > xSupplier( xFormatter->getNumberFormatsSupplier(), UNO_SET_THROW );
 489                              Reference< XPropertySet > xFormatterSettings( xSupplier->getNumberFormatSettings(), UNO_SET_THROW );
 490                              OSL_VERIFY( xFormatterSettings->getPropertyValue("NullDate") >>= aFormatterNullDate );
 491                          }
 492                          catch( const Exception& )
 493                          {
 494                             DBG_UNHANDLED_EXCEPTION();
 495                          }
 496                          // get a value which represents the given date, relative to the null date of the formatter
 497                          fValue -= toDays( rNullDate, aFormatterNullDate );
 498                          // format this value
 499                         aString = xFormatter->convertNumberToString( nKey, fValue );
 500                     }
 501                 }
 502                 break;
 503                 case NumberFormat::TIME:
 504                 case NumberFormat::NUMBER:
 505                 case NumberFormat::SCIENTIFIC:
 506                 case NumberFormat::FRACTION:
 507                 case NumberFormat::PERCENT:
 508                 {
 509                     double fValue = xVariant->getDouble();
 510                     if (!xVariant->wasNull())
 511                         aString = xFormatter->convertNumberToString(nKey, fValue);
 512                 }   break;
 513                 case NumberFormat::CURRENCY:
 514                 {
 515                     double fValue = xVariant->getDouble();
 516                     if (!xVariant->wasNull())
 517                         aString = xFormatter->getInputString(nKey, fValue);
 518                 }   break;
 519                 case NumberFormat::TEXT:
 520                     aString = xFormatter->formatString(nKey, xVariant->getString());
 521                     break;
 522                 default:
 523                     aString = xVariant->getString();
 524             }
 525         }
 526         catch(const Exception& )
 527         {
 528             aString = xVariant->getString();
 529         }
 530     }
 531     return aString;
 532 }
 533
 534
 535 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */