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