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                     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             sal_Int32 nRealUsedKey = xFormatter->detectNumberFormat(0, rString);
 254             if (nRealUsedKey != nKeyToUse)
 255                 nRealUsedTypeClass = getNumberFormatType(xFormatter, nRealUsedKey) & ~NumberFormat::DEFINED;
 256
 257             // and again a special treatment, this time for percent formats
 258             if ((NumberFormat::NUMBER == nRealUsedTypeClass) && (NumberFormat::PERCENT == nTypeClass))
 259             {   // formatting should be "percent", but the String provides just a simple number -> adjust
 260                 OUString sExpanded(rString);
 261                 static OUString s_sPercentSymbol( "%" );
 262                     // need a method to add a sal_Unicode to a string, 'til then we use a static string
 263                 sExpanded += s_sPercentSymbol;
 264                 fValue = xFormatter->convertStringToNumber(nKeyToUse, sExpanded);
 265             }
 266
 267             switch (nRealUsedTypeClass)
 268             {
 269                 case NumberFormat::DATE:
 270                 case NumberFormat::DATETIME:
 271                 case NumberFormat::TIME:
 272                     DBTypeConversion::setValue(xVariant,rNullDate,fValue,nRealUsedTypeClass);
 273                     //  xVariant->updateDouble(toStandardDbDate(rNullDate, fValue));
 274                     break;
 275                 case NumberFormat::CURRENCY:
 276                 case NumberFormat::NUMBER:
 277                 case NumberFormat::SCIENTIFIC:
 278                 case NumberFormat::FRACTION:
 279                 case NumberFormat::PERCENT:
 280                     xVariant->updateDouble(fValue);
 281                     break;
 282                 default:
 283                     xVariant->updateString(rString);
 284             }
 285         }
 286         catch(const Exception& )
 287         {
 288             xVariant->updateString(rString);
 289         }
 290     }
 291     else
 292     {
 293         switch (nFieldType)
 294         {
 295             case ::com::sun::star::sdbc::DataType::CHAR:
 296             case ::com::sun::star::sdbc::DataType::VARCHAR:
 297             case ::com::sun::star::sdbc::DataType::LONGVARCHAR:
 298                 xVariant->updateString(rString);
 299                 break;
 300             default:
 301                 xVariant->updateNull();
 302         }
 303     }
 304 }
 305
 306
 307 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
 308                                 const Date& rNullDate,
 309                                 const double& rValue,
 310                                 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
 311 {
 312     switch (nKeyType & ~NumberFormat::DEFINED)
 313     {
 314         case NumberFormat::DATE:
 315             xVariant->updateDate(toDate( rValue, rNullDate));
 316             break;
 317         case NumberFormat::DATETIME:
 318             xVariant->updateTimestamp(toDateTime(rValue,rNullDate));
 319             break;
 320         case NumberFormat::TIME:
 321             xVariant->updateTime(toTime(rValue));
 322             break;
 323         default:
 324             {
 325                 double nValue = rValue;
 326 //              Reference<XPropertySet> xProp(xVariant,UNO_QUERY);
 327 //              if (    xProp.is()
 328 //                  &&  xProp->getPropertySetInfo()->hasPropertyByName(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))
 329 //                  && !::comphelper::getBOOL(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))) )
 330 //              {
 331 //                  switch (::comphelper::getINT32(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE))))
 332 //                  {
 333 //                      case DataType::TINYINT:
 334 //                          nValue = static_cast<sal_uInt8>(rValue);
 335 //                          break;
 336 //                      case DataType::SMALLINT:
 337 //                          nValue = static_cast<sal_uInt16>(rValue);
 338 //                          break;
 339 //                      case DataType::INTEGER:
 340 //                          nValue = static_cast<sal_uInt32>(rValue);
 341 //                          break;
 342 //                      case DataType::BIGINT:
 343 //                          nValue = static_cast<sal_uInt64>(rValue);
 344 //                          break;
 345 //                  }
 346 //              }
 347                 xVariant->updateDouble(nValue);
 348             }
 349     }
 350 }
 351
 352
 353 double DBTypeConversion::getValue( const Reference< XColumn >& i_column, const Date& i_relativeToNullDate )
 354 {
 355     try
 356     {
 357         const Reference< XPropertySet > xProp( i_column, UNO_QUERY_THROW );
 358
 359         const sal_Int32 nColumnType = ::comphelper::getINT32( xProp->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex( PROPERTY_ID_TYPE ) ) );
 360         switch ( nColumnType )
 361         {
 362         case DataType::DATE:
 363             return toDouble( i_column->getDate(), i_relativeToNullDate );
 364
 365         case DataType::TIME:
 366             return toDouble( i_column->getTime() );
 367
 368         case DataType::TIMESTAMP:
 369             return toDouble( i_column->getTimestamp(), i_relativeToNullDate );
 370
 371         default:
 372             {
 373                 bool bIsSigned = true;
 374                 OSL_VERIFY( xProp->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex( PROPERTY_ID_ISSIGNED ) ) >>= bIsSigned );
 375                 if ( !bIsSigned )
 376                 {
 377                     switch ( nColumnType)
 378                     {
 379                         case DataType::TINYINT:
 380                             return static_cast<double>(static_cast<sal_uInt8>(i_column->getByte()));
 381                         case DataType::SMALLINT:
 382                             return static_cast<double>(static_cast<sal_uInt16>(i_column->getShort()));
 383                         case DataType::INTEGER:
 384                             return static_cast<double>(static_cast<sal_uInt32>(i_column->getInt()));
 385                         case DataType::BIGINT:
 386                             return static_cast<double>(static_cast<sal_uInt64>(i_column->getLong()));
 387                     }
 388                 }
 389             }
 390             return i_column->getDouble();
 391         }
 392     }
 393     catch( const Exception& )
 394     {
 395         DBG_UNHANDLED_EXCEPTION();
 396         return 0.0;
 397     }
 398 }
 399
 400 OUString DBTypeConversion::getFormattedValue(const Reference< XPropertySet>& _xColumn,
 401                                            const Reference<XNumberFormatter>& _xFormatter,
 402                                            const ::com::sun::star::lang::Locale& _rLocale,
 403                                            const Date& _rNullDate)
 404 {
 405     OSL_ENSURE(_xColumn.is() && _xFormatter.is(), "DBTypeConversion::getFormattedValue: invalid arg !");
 406     if (!_xColumn.is() || !_xFormatter.is())
 407         return OUString();
 408
 409     sal_Int32 nKey(0);
 410     try
 411     {
 412         _xColumn->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FORMATKEY)) >>= nKey;
 413     }
 414     catch (const Exception& )
 415     {
 416         OSL_FAIL("DBTypeConversion::getValue: caught an exception while asking for the format key!");
 417     }
 418
 419     if (!nKey)
 420     {
 421         Reference<XNumberFormats> xFormats( _xFormatter->getNumberFormatsSupplier()->getNumberFormats() );
 422         Reference<XNumberFormatTypes> xTypeList(_xFormatter->getNumberFormatsSupplier()->getNumberFormats(), UNO_QUERY);
 423
 424         nKey = ::dbtools::getDefaultNumberFormat(_xColumn,
 425                                            Reference< XNumberFormatTypes > (xFormats, UNO_QUERY),
 426                                            _rLocale);
 427
 428     }
 429
 430     sal_Int16 nKeyType = getNumberFormatType(_xFormatter, nKey) & ~NumberFormat::DEFINED;
 431
 432     return DBTypeConversion::getFormattedValue(Reference< XColumn > (_xColumn, UNO_QUERY), _xFormatter, _rNullDate, nKey, nKeyType);
 433 }
 434
 435
 436 OUString DBTypeConversion::getFormattedValue(const Reference<XColumn>& xVariant,
 437                                    const Reference<XNumberFormatter>& xFormatter,
 438                                    const Date& rNullDate,
 439                                    sal_Int32 nKey,
 440                                    sal_Int16 nKeyType)
 441 {
 442     OUString aString;
 443     if (xVariant.is())
 444     {
 445         try
 446         {
 447             switch (nKeyType & ~NumberFormat::DEFINED)
 448             {
 449                 case NumberFormat::DATE:
 450                 case NumberFormat::DATETIME:
 451                 {
 452                     // get a value which represents the given date, relative to the given null date
 453                     double fValue = getValue( xVariant, rNullDate );
 454                     if ( !xVariant->wasNull() )
 455                     {
 456                          // get the null date of the formatter
 457                          Date aFormatterNullDate( rNullDate );
 458                          try
 459                          {
 460                              Reference< XNumberFormatsSupplier > xSupplier( xFormatter->getNumberFormatsSupplier(), UNO_SET_THROW );
 461                              Reference< XPropertySet > xFormatterSettings( xSupplier->getNumberFormatSettings(), UNO_SET_THROW );
 462                              OSL_VERIFY( xFormatterSettings->getPropertyValue("NullDate") >>= aFormatterNullDate );
 463                          }
 464                          catch( const Exception& )
 465                          {
 466                             DBG_UNHANDLED_EXCEPTION();
 467                          }
 468                          // get a value which represents the given date, relative to the null date of the formatter
 469                          fValue -= toDays( rNullDate, aFormatterNullDate );
 470                          // format this value
 471                         aString = xFormatter->convertNumberToString( nKey, fValue );
 472                     }
 473                 }
 474                 break;
 475                 case NumberFormat::TIME:
 476                 case NumberFormat::NUMBER:
 477                 case NumberFormat::SCIENTIFIC:
 478                 case NumberFormat::FRACTION:
 479                 case NumberFormat::PERCENT:
 480                 {
 481                     double fValue = xVariant->getDouble();
 482                     if (!xVariant->wasNull())
 483                         aString = xFormatter->convertNumberToString(nKey, fValue);
 484                 }   break;
 485                 case NumberFormat::CURRENCY:
 486                 {
 487                     double fValue = xVariant->getDouble();
 488                     if (!xVariant->wasNull())
 489                         aString = xFormatter->getInputString(nKey, fValue);
 490                 }   break;
 491                 case NumberFormat::TEXT:
 492                     aString = xFormatter->formatString(nKey, xVariant->getString());
 493                     break;
 494                 default:
 495                     aString = xVariant->getString();
 496             }
 497         }
 498         catch(const Exception& )
 499         {
 500             aString = xVariant->getString();
 501         }
 502     }
 503     return aString;
 504 }
 505
 506
 507 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */