LAB Documentation

00001 /****************************************************************************
00002 ** ui.h extension file, included from the uic-generated form implementation.
00003 **
00004 ** If you wish to add, delete or rename functions or slots use
00005 ** Qt Designer which will update this file, preserving your code. Create an
00006 ** init() function in place of a constructor, and a destroy() function in
00007 ** place of a destructor.
00008 *****************************************************************************/
00009 
00010 
00011 void QlabCashDailyReport::init()
00012 {
00013         baseTable = "Ticket_receipts" ;
00014         origTable = "Ticket_receipts" ;
00015         ticks = 0 ;
00016         inchange = FALSE ;
00017         filterParts["reportType"] = tr( "Synthesis" ) ;
00018         filterParts["filterBox"] = tr( "(None)" ) ;
00019         filterParts["filterEdit"] = "" ;
00020         filterParts["groupByBox"] = tr( "(None)" ) ;
00021 }
00022 
00023 void QlabCashDailyReport::setDate( const QDate & dt )
00024 {
00025         dateSelector->setDate( dt ) ;
00026 }
00027 
00028 
00029 
00030 void QlabCashDailyReport::buildSynthesis()
00031 {
00032         QSqlQuery q ;
00033         txt = "" ;
00034         reportTitle = tr( "Cashbox Status for" ) ;
00035         txt.append( "<table width=\"100%\" border=\"1\">" ) ;
00036         // This holds statistical info first, then we add other info
00037         addSection( tr( "Synthesis" ), 3 ) ;
00038         // Now insert Values for section
00039         addSyntheticLine( tr( "Registered Total" ), "line_type = 'Total'", FALSE ) ;
00040         manageCash() ;
00041         addSyntheticLine( tr( "Cheque" ), "line_type = 'Cheque'", FALSE ) ;
00042         addSyntheticLine( tr( "Credit Card" ), "line_type = 'CreditCard'", FALSE ) ;
00043         addSyntheticLine( tr( "Moneo" ), "line_type = 'Moneo'", FALSE ) ;
00044         addSyntheticLine( tr( "Delayed" ), "line_type = 'Delayed'", FALSE ) ;
00045         addSyntheticLine( tr( "Lottery Refund" ), "LEFT( serial, 2 ) = 'rj'", TRUE ) ;
00046         addCancelledTickets() ;
00047         
00048         addSection( tr( "Statistics" ), 3 ) ;
00049         categoryList() ;
00050         txt.append( "</table>" ) ;
00051         
00052 }
00053 
00054 
00055 void QlabCashDailyReport::acceptDate()
00056 {
00057         QDate dt = dateSelector->date() ;
00058         QSqlQuery q( QString( "SELECT * FROM %1 WHERE received >= '%2' AND received < '%3' ;" )
00059                                  .arg( origTable )
00060                                  .arg( dt.toString( "yyyyMMdd000000" ) )
00061                                  .arg( ( dt.addDays( 1 ) ).toString( "yyyyMMdd000000" ) ) ) ;
00062 
00063         if ( q.size() < 1 ) {
00064                 baseTable = "Ticket_history" ;
00065         } else {
00066                 baseTable = "Ticket_receipts" ;
00067         }
00068         if ( ticks != 0 ) {
00069                 delete ticks ;
00070                 ticks = 0 ;
00071         }
00072         ticks = new QSqlCursor( baseTable, TRUE ) ;
00073         if ( !inchange ) {
00074                 inchange = TRUE ;
00075                 cancelled.clear() ;
00076                 resultTable->clear() ;
00077                 ticks->setFilter( QString( "received >= '%1' AND received < '%2' AND line_type='TicketCancel'")
00078                                                   .arg( dt.toString( "yyyyMMdd000000" ) )
00079                                                   .arg( ( dt.addDays( 1 ) ).toString( "yyyyMMdd000000" ) ) ) ;
00080                 ticks->select() ;
00081                 finalFilter = QString( "received >= '%1' AND received < '%2'" )
00082                                                   .arg( dt.toString( "yyyyMMdd000000" ) )
00083                                                   .arg( ( dt.addDays( 1 ) ).toString( "yyyyMMdd000000" ) ) ;
00084                 if ( filterParts["filterBox"] != tr( "(None)" ) ) {
00085                         // We should add filter here
00086                 }
00087                 if ( filterParts["groupByBox"] != tr( "(None)" ) ) {
00088                         // we should add a groupBy clause here...
00089                 }
00090                 while ( ticks->next() ) {
00091                         finalFilter.append( QString( " AND ticket_num != '%1'" ).arg( ticks->value( "ticket_num" ).toString() ) ) ;
00092                         cancelled[ticks->value( "recid" ).toInt()] = ticks->value( "ticket_num" ).toString() ;
00093                 }
00094                 
00095                 ticks->setFilter( finalFilter ) ;
00096                 ticks->select() ;
00097                 txt = "" ;
00098                 if ( filterParts["reportType"]  == tr( "Synthesis" ) ) {
00099                         buildSynthesis() ;
00100                 } else if ( filterParts["reportType"]  == tr( "By Products" ) ) {
00101                         // Product synthesis
00102                         buildByProducts() ;
00103                 } else if ( filterParts["reportType"]  == tr( "By Cashier" ) ) {
00104                         // Cashier Synthesis
00105                 } else if ( filterParts["reportType"]  == tr( "By Group" ) ) {
00106                         // Cashbox Group  synthesis
00107                 }
00108                 resultTable->setText( txt ) ;
00109                 inchange = FALSE ;
00110         }
00111 }
00112 
00113 
00114 
00115 
00116 void QlabCashDailyReport::addSyntheticLine( const QString & title, const QString & flt, bool negate )
00117 {
00118         QSqlQuery q ;
00119         QString result = "" ;
00120                 
00121         if ( negate ) {
00122                 result.append( QString( "<td width=\"70%\"><font color=\"#ff0000\"><b>%1</b></font></td>" ).arg( title ) ) ;
00123         } else {
00124                 result.append( QString( "<td width=\"70%\">%1</td>" ).arg( title ) ) ;
00125         }
00126         q.exec( QString( "SELECT SUM( total ) FROM %1 WHERE %2 AND %3 ;" )
00127                         .arg( baseTable ).arg( finalFilter ).arg( flt ) ) ;
00128         q.next() ;
00129         double val = q.value( 0 ).toDouble() ;
00130         if ( negate ) {
00131                 val = -val ;
00132                 result.append( QString( "<td width=\"15%\" align=\"right\"><font color=\"#ff0000\"><b>%1</b></font></td>" )
00133                                                 .arg( QString( "" ).number( val, 'f', 2 ) ) ) ;
00134         } else {
00135                 result.append( QString( "<td width=\"15%\" align=\"right\">%1</td>" )
00136                                                 .arg( QString( "" ).number( val, 'f', 2 ) ) ) ; 
00137         }
00138         q.exec( QString( "SELECT count( * ) FROM %1 WHERE %2 AND %3 ;" )
00139                         .arg( baseTable).arg( finalFilter ).arg( flt ) ) ;
00140         q.next() ;
00141         result.append( QString( "<td width=\"15%\" align=\"right\">%1</td>" )
00142                                                 .arg( q.value( 0 ).toString() ) ) ;     
00143         result.prepend( "<tr>" ) ;
00144         result.append( "</tr>" ) ;
00145         txt.append( result ) ;
00146 }
00147 
00148 
00149 void QlabCashDailyReport::addSection( const QString & title, int cols )
00150 {
00151         txt.append( QString( "<tr><td colspan=\"%1\" bgcolor=\"#ffffd5\"><font color=\"#ff0000\" size=\"+1\"><b>%2</b></font>" )
00152                                 .arg( cols ).arg( title ) ) ;
00153         txt.append( "</td></tr>" ) ;
00154 }
00155 
00156 
00157 void QlabCashDailyReport::categoryList()
00158 {
00159         QSqlCursor * vat = new QSqlCursor( "System_taxrates", TRUE ) ;
00160         QSqlCursor * prods = new QSqlCursor( "Inv_products", TRUE ) ;
00161         QSqlCursor * parts = new QSqlCursor( "Inv_parts", TRUE ) ;
00162         QMap<QString, double> catmap ;
00163         QMap<QString, int> catcount ;
00164         QMap<int, double> vatval ;
00165         QMap<int, QString> vattext ;
00166         QMap<int, double> vatcount ;
00167         
00168         vatval[0] = 0.0 ;
00169         vattext[0] = tr( "Unknown to check" ) ;
00170         vatcount[0] = 0.0 ;
00171         vat->setFilter( "" ) ;
00172         vat->select() ;
00173         while ( vat->next() ) {
00174                 vatval[vat->value( "recid" ).toInt()] = vat->value( "tax_rate" ).toDouble() ;
00175                 vattext[vat->value( "recid" ).toInt()] = vat->value( "tax_name" ).toString() ;
00176                 vatcount[vat->value( "recid" ).toInt()] = 0.0 ;
00177         }
00178         catmap[tr( "Unknown" )] = 0.0 ;
00179         catcount[tr( "Unknown" )] = 0 ;
00180         ticks->setFilter( QString( "%1 AND %2" ).arg( finalFilter ).arg( "line_type = 'Line'" ) ) ;
00181         ticks->select() ;
00182         while ( ticks->next() ) {
00183                 QString serial = ticks->value( "serial" ).toString() ;
00184                 if ( !serial.isEmpty() && serial != "0" && serial.left( 2 ) != "rj" && ticks->value( "prodid" ).toInt() != 0 ) {
00185                         parts->setFilter( QString( "serial='%1'" ).arg( serial ) ) ;
00186                         parts->select() ;
00187                         parts->next() ;
00188                         prods->setFilter( QString( "recid='%1'" ).arg( parts->value( "prodid" ).toString() ) ) ;
00189                         prods->select() ;
00190                         if ( prods->next() ) {
00191                                 QString pcat = catAncestor( prods->value( "main_category" ).toInt() ) ;
00192                                 if ( catmap.find( pcat ) != catmap.end() ) {
00193                                         catmap[pcat] = catmap[pcat] + ticks->value( "total" ).toDouble() ;
00194                                         catcount[pcat] = catcount[pcat] + ( parts->value( "qty_unit" ).toInt() * ticks->value( "qty" ).toInt() ) ;
00195                                 } else {
00196                                         catmap[pcat] = ticks->value( "total" ).toDouble() ;
00197                                         catcount[pcat] = parts->value( "qty_unit" ).toInt() * ticks->value( "qty" ).toInt()  ;
00198                                 }
00199                                 vatcount[parts->value( "Taxid" ).toInt()] = vatcount[parts->value( "Taxid" ).toInt()] + ticks->value( "total" ).toDouble() ;
00200                         } else {
00201                                 catmap[tr( "Unknown" )] = catmap[tr( "Unknown" )] + ticks->value( "total" ).toDouble() ;
00202                                 catcount[tr( "Unknown" )] = catcount[tr( "Unknown" )] + ticks->value( "qty" ).toInt()  ;
00203                                 vatcount[0] = vatcount[0] + ticks->value( "total" ).toDouble() ;
00204                         }
00205                 } else if ( (ticks->value( "serial" ).toString()).left( 2 ) != "rj" ) {
00206                         catmap[tr( "Unknown" )] = catmap[tr( "Unknown" )] + ticks->value( "total" ).toDouble() ;
00207                         catcount[tr( "Unknown" )] = catcount[tr( "Unknown" )] + ticks->value( "qty" ).toInt()  ;
00208                         vatcount[0] = vatcount[0] + ticks->value( "total" ).toDouble() ;
00209                 }
00210         }
00211         for ( QMap<QString, double>::Iterator it = catmap.begin(); it != catmap.end(); ++it ) {
00212                 txt.append( QString( "<tr><td>%1</td><td align =\"right\">%2</td><td align=\"right\">%3</td></tr>" )
00213                                         .arg( it.key() )
00214                                         .arg( QString("").number( it.data(), 'f', 2 ) )
00215                                         .arg( QString("%1").arg( catcount[it.key()] ) ) ) ;
00216         }
00217         
00218         addSection( tr( "Vat Values" ), 3 ) ;
00219         for ( QMap<int, double>::Iterator it = vatval.begin(); it != vatval.end(); ++it ) {
00220                 txt.append( QString( "<tr><td>%1</td><td align=\"right\">%2</td><td align=\"right\">%3</td></tr>" )
00221                                         .arg( vattext[it.key()] )
00222                                         .arg( "&nbsp;" )
00223                                         .arg( QString( "" ).number( vatcount[it.key()] * ( vatval[it.key()] / 100 ), 
00224                                                                                                                                                 'f', 2 ) ) ) ;
00225         }
00226 }
00227 
00228 
00229 QString QlabCashDailyReport::catAncestor( int cat )
00230 {
00231         QSqlCursor * cats = new QSqlCursor( "System_categories", TRUE ) ;
00232         QString cn = "" ;
00233         cats->setFilter( QString( "recid='%1'" ).arg( cat ) ) ;
00234         cats->select() ;
00235         if ( cats->next() ) {
00236                 cn = cats->value( "cat_name" ).toString() ;
00237                 while ( cats->value( "cat_parent" ).toInt() != 0 ) {
00238                         cats->setFilter( QString( "recid='%1'" ).arg( cats->value( "cat_parent" ).toInt() ) ) ;
00239                         cats->select() ;
00240                         cats->next() ;
00241                         cn = cats->value( "cat_name" ).toString() ;
00242                 }
00243         } else {
00244                 cn = tr( "Unknown" ) ;
00245         }
00246         return cn ;
00247 }
00248 
00249 
00250 
00251 void QlabCashDailyReport::manageCash()
00252 {
00253         QSqlQuery q ;
00254         
00255         addSyntheticLine( tr( "Total Cash in" ), "line_type = 'Cashin'", FALSE  ) ;
00256         addSyntheticLine( tr( "Cash Refund" ), "line_type = 'Refund'", TRUE  ) ;
00257         q.exec( QString( "SELECT SUM( total ) FROM %1 WHERE %2 AND %3 ;" )
00258                         .arg( baseTable ).arg( finalFilter ).arg( "line_type='Cashin'" ) ) ;
00259         q.next() ;
00260         double cashin = q.value( 0 ).toDouble() ;
00261         q.exec( QString( "SELECT count( * ) FROM %1 WHERE %2 AND %3 ;" )
00262                         .arg( baseTable ).arg( finalFilter ).arg( "line_type='Cashin'" ) ) ;
00263         q.next() ;
00264         double refund = q.value( 0 ).toDouble() ;
00265         txt.append( QString( "<tr><td>%1</td><td align=\"right\">%2</td><td>&nbsp;</td></tr>" )
00266                                 .arg( tr( "Net Cash in Box" ) )
00267                                 .arg( QString( "" ).number( cashin - refund , 'f', 2 ) ) ) ;
00268 }
00269 
00270 
00271 void QlabCashDailyReport::printMe()
00272 {
00273         QPrinter * printer = new QPrinter( QPrinter::ScreenResolution ) ;
00274         printer->setFullPage( TRUE ) ;
00275         if ( printer->setup( this ) ) {
00276                 QPainter p ;
00277                 if ( !p.begin( printer ) ) {
00278                         return ;
00279                 }
00280                 QPaintDeviceMetrics metrics( p.device() ) ;
00281                 int dpiy = metrics.logicalDpiY() ;
00282                 margin = (int)( ( 1 / 2.54 ) * dpiy ) ; // 1 cm margins
00283                 QRect body( margin, margin * 2, 
00284                                         metrics.width() - 2 * margin, 
00285                                         metrics.height() - 4 * margin ) ;
00286                 QSimpleRichText richText( resultTable->text(), 
00287                                                                   resultTable->font(), 
00288                                                                   0, 0, 0, body.height() ) ;
00289                 richText.setWidth( &p, body.width() ) ;
00290                 QRect view( body ) ;
00291                 
00292                 page = 1;
00293                 do {
00294                         // Draw Page
00295                         richText.draw( &p, body.left(), body.top(), view, colorGroup() ) ;
00296                         printheader( p, view ) ;
00297                         printfooter( p, view ) ;
00298                         printer->newPage() ;
00299                         page++ ;
00300                         if ( view.top() >= richText.height() )
00301                                 break ;
00302                 } while (TRUE) ;
00303                 
00304         }
00305 }
00306 
00307 
00308 void QlabCashDailyReport::addCancelledTickets()
00309 {
00310         txt.append( "<tr>" ) ;
00311         txt.append( QString( "<td width=\"70%\"><font color=\"#0000ff\">%1</font></td>" ).arg( tr( "Cancelled tickets" ) ) ) ;
00312         txt.append( QString( "<td width=\"15%\" align=\"right\">%1</td>" )
00313                                                 .arg( QString( "" ).number( cancelled.size(), 'f', 0 ) ) ) ;    
00314         txt.append( "<td width=\"15%\" align=\"right\">&nbsp;</td>" ) ;
00315         txt.append( "</tr>" ) ;
00316 }
00317 
00318 
00319 void QlabCashDailyReport::changeFilterString( const QString & flt )
00320 {
00321         QString want = QObject::sender()->name() ;
00322         
00323         filterParts[want] = flt ;
00324 }
00325 
00326 
00327 void QlabCashDailyReport::synthesisByProduct()
00328 {
00329 
00330 }
00331 
00332 
00333 void QlabCashDailyReport::printheader( QPainter & p, QRect & view )
00334 {
00335         QString header = "Le Maryland" ;
00336         QString rhead = QString( "%1: %2" ) 
00337                                         .arg( reportTitle )
00338                                         .arg( dateSelector->date().toString( "dddd dd MMMM yyyy" ) ) ;
00339         view.moveBy( 0, view.height() ) ;
00340         p.translate( 0 , - view.height() ) ;
00341         p.drawText( view.left() , 
00342                                 view.top() - p.fontMetrics().ascent() - 5, 
00343                                 header ) ;
00344         p.drawText( view.right() - p.fontMetrics().width( rhead ) ,
00345                                 view.top() - p.fontMetrics().ascent() - 5, 
00346                                 rhead ) ;
00347 }
00348 
00349 
00350 void QlabCashDailyReport::printfooter( QPainter & p, QRect & view )
00351 {
00352         view.moveBy( 0, view.height() ) ;
00353         p.translate( 0 , - view.height() ) ;
00354         p.drawText( view.left(),
00355                                 view.bottom() + margin - p.fontMetrics().ascent() - 5,
00356                                 QString( tr( "Printed on: " ) + 
00357                                                  QDateTime::currentDateTime().toString( "dddd dd MMMM yyyy - hh:mm:ss" ) ) ) ;
00358         
00359         p.drawText( view.right() - p.fontMetrics().width( QString( "Page: " ) + QString::number( page ) ),
00360                                 view.bottom() + margin - p.fontMetrics().ascent() - 5,
00361                                 QString( "Page: " ) + QString::number( page ) ) ;
00362 
00363 }
00364 
00365 
00366 void QlabCashDailyReport::buildByProducts()
00367 {
00368         qDebug ( "Building By Product report" ) ;
00369         QSqlSelectCursor q;
00370         txt = "" ;
00371         QString flt = finalFilter + " AND ( line_type='Line' OR line_type='LineCancel' )" ;
00372         reportTitle = tr( "Report By Products" ) ;
00373         txt.append( "<table width=\"100%\" border=\"1\">" ) ;
00374         
00375         q.exec( QString( "SELECT t1.* , t2.main_cat, t2.qty_unit, t3.cat_name "
00376                         "FROM %1 AS t1, Inv_parts AS t2, System_categories AS t3 "
00377                         "WHERE t1.serial = t2.serial AND t3.recid = t2.main_cat AND t1.serial > '0' "
00378                         "AND %2 "
00379                         "ORDER BY cat_name, prodid ASC" ).arg( baseTable ).arg( flt ) ) ;
00380         QString cat_name = "" ;
00381         QString prod_name = "" ;
00382         double unit_price = 0.0 ;
00383         double total = 0.0 ;
00384         int qty = 0 ;
00385         while ( q.next() ) {
00386                 if ( q.value( "prodname" ) != prod_name ) {
00387                         // New product. Write previous and reset...
00388                         if ( prod_name != "" ) {
00389                                 txt.append( QString( "<tr><td width=\"80%\">%1</td>" ).arg( prod_name ) ) ;
00390                                 txt.append( QString( "<td width=\"10%\" align=\"right\">%1</td>" ).arg( qty ) ) ;
00391                                 txt.append( QString( "<td width=\"10%\" align=\"right\">%1</td>" )
00392                                                         .arg( QString( "" ).number( unit_price, 'f', 2 ) ) ) ;
00393                                 txt.append( QString( "<td width=\"10%\" align=\"right\">%1</td></tr>\n" )
00394                                                         .arg( QString( "" ).number( total, 'f', 2 ) ) ) ;
00395                         }
00396                         prod_name = q.value( "prodname" ).toString() ;
00397                         unit_price = q.value( "retail" ).toDouble() ;
00398                         qty = q.value( "qty" ).toInt() * q.value( "qty_unit" ).toInt() ;
00399                         total = q.value( "total" ).toDouble() ;
00400                 } else {
00401                         qty += ( q.value( "qty" ).toInt() * q.value( "qty_unit" ).toInt() ) ; 
00402                         total += q.value( "total" ).toDouble() ;
00403                 }
00404                 if ( q.value( "cat_name" ).toString() != cat_name ) {
00405                         // This is a new Category. Tell System
00406                         cat_name = q.value( "cat_name" ).toString() ;
00407                         addSection( cat_name, 4 ) ;
00408                 }
00409         }       
00410         txt.append( QString( "<tr><td width=\"70%\">%1</td>" ).arg( prod_name ) ) ;
00411         txt.append( QString( "<td width=\"10%\" align=\"right\">%1</td>" ).arg( qty ) ) ;
00412         txt.append( QString( "<td width=\"10%\" align=\"right\">%1</td>" )
00413                                 .arg( QString( "" ).number( unit_price, 'f', 2 ) ) ) ;
00414         txt.append( QString( "<td width=\"10%\" align=\"right\">%1</td></tr>\n" )
00415                                 .arg( QString( "" ).number( total, 'f', 2 ) ) ) ;
00416         txt.append( "</table>" ) ;
00417 }
qlabcashdailyreport.ui.h
