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