git.roojs.org Git - xtuple/blob - foundation-database/public/tables/report/BriefSalesHistory.xml

   1 <!DOCTYPE openRPTDef>
   2 <report>
   3  <title>Brief Sales History</title>
   4  <name>BriefSalesHistory</name>
   5  <description></description>
   6  <grid>
   7   <snap/>
   8   <show/>
   9   <x>0.05</x>
  10   <y>0.05</y>
  11  </grid>
  12  <size>Letter</size>
  13  <portrait/>
  14  <topmargin>50</topmargin>
  15  <bottommargin>50</bottommargin>
  16  <rightmargin>50</rightmargin>
  17  <leftmargin>50</leftmargin>
  18  <querysource>
  19   <name>head</name>
  20   <sql>SELECT &lt;? if exists("startDate") ?>
  21          formatDate(date(&lt;? value("startDate") ?>))
  22        &lt;? else ?>
  23          text('Earliest')
  24        &lt;? endif ?> AS startDate,
  25        &lt;? if exists("endDate") ?>
  26          formatDate(date(&lt;? value("endDate") ?>))
  27        &lt;? else ?>
  28          text('Latest')
  29        &lt;? endif ?> AS endDate,
  30        &lt;? if exists("showPrices") ?>
  31            text('Unit Price') AS lbl_unitprice,
  32            text('Total') AS lbl_total,
  33        &lt;? else ?>
  34            text('') AS lbl_unitprice,
  35            text('') AS lbl_total,
  36        &lt;? endif ?>
  37        &lt;? if exists("warehous_id") ?>
  38          (SELECT warehous_code FROM whsinfo WHERE warehous_id=&lt;? value("warehous_id") ?>)
  39        &lt;? else ?>
  40          text('All Sites')
  41        &lt;? endif ?>
  42        AS warehouse,
  43        &lt;? if exists("cust_id") ?>
  44          (SELECT cust_name FROM custinfo WHERE cust_id=&lt;? value("cust_id") ?>)
  45        &lt;? else ?>
  46          text('All Customers')
  47        &lt;? endif ?>
  48          AS cust_name,
  49        &lt;? if exists("custtype_id") ?>
  50          (SELECT custtype_code FROM custtype WHERE custtype_id=&lt;? value("custtype_id") ?>)
  51        &lt;? else ?>
  52          text('All Customer Types')
  53        &lt;? endif ?>
  54          AS custtype_code,
  55        &lt;? if exists("custtype_pattern") ?>
  56            text(&lt;? value("custtype_pattern") ?>)
  57        &lt;? else ?>
  58            text('Not Applicable')
  59        &lt;? endif ?>
  60          AS custtype_pattern,
  61        &lt;? if exists("custgrp_id") ?>
  62          (SELECT custgrp_name FROM custgrp WHERE custgrp_id=&lt;? value("custgrp_id") ?>)
  63        &lt;? else ?>
  64          text('All Customer Groups')
  65        &lt;? endif ?>
  66          AS custgrp_name,
  67        &lt;? if exists("custgrp_pattern") ?>
  68            text(&lt;? value("custgrp_pattern") ?>)
  69        &lt;? else ?>
  70            text('Not Applicable')
  71        &lt;? endif ?>
  72          AS custgrp_pattern,
  73       &lt;? if exists("prodcat_id") ?>
  74          (SELECT prodcat_code FROM prodcat WHERE prodcat_id=&lt;? value("prodcat_id") ?>)
  75        &lt;? else ?>
  76          text('All Product Categories')
  77        &lt;? endif ?>
  78          AS prodcat_code,
  79        &lt;? if exists("prodcat_pattern") ?>
  80            text(&lt;? value("prodcat_pattern") ?>)
  81        &lt;? else ?>
  82            text('Not Applicable')
  83        &lt;? endif ?>
  84          AS prodcat_pattern,
  85        &lt;? if exists("item_id") ?>
  86          (SELECT item_number FROM item WHERE item_id=&lt;? value("item_id") ?>)
  87        &lt;? else ?>
  88          text('All Item Numbers')
  89        &lt;? endif ?>
  90          AS item_number,
  91        &lt;? if exists("cohead_id") ?>
  92          (SELECT cohead_number FROM cohead WHERE cohead_id=&lt;? value("cohead_id") ?>)
  93        &lt;? else ?>
  94          text('All Orders')
  95        &lt;? endif ?>
  96          AS docnumber; </sql>
  97  </querysource>
  98  <querysource>
  99   <name>detail</name>
 100   <sql>SELECT cohist_ordernumber AS sonumber,
 101        cohist_invcnumber AS invnumber,
 102        formatDate(cohist_orderdate) AS orddate,
 103        formatDate(cohist_invcdate, 'Return') AS shipdate,
 104        SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) as extended,
 105        formatMoney(SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2))) as f_total,
 106        SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) as total
 107 FROM saleshistory
 108   LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
 109 WHERE ((cohist_invcdate BETWEEN &lt;? value("startDate") ?> AND  &lt;? value("endDate") ?>)
 110 &lt;? if exists("cust_id") ?>
 111    AND (cohist_cust_id=&lt;? value("cust_id") ?>)
 112 &lt;? endif ?>
 113 &lt;? if exists("shipto_id") ?>
 114    AND (cohist_shipto_id=&lt;? value("shipto_id") ?>)
 115 &lt;? endif ?>
 116 &lt;? if exists("custtype_id") ?>
 117    AND (cust_custtype_id=&lt;? value("custtype_id") ?>)
 118 &lt;? endif ?>
 119 &lt;? if exists("custtype_pattern") ?>
 120    AND (custtype_code ~ &lt;? value("custtype_pattern") ?>)
 121 &lt;? endif ?>
 122 &lt;? if exists("custgrp_id") ?>
 123   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 124                    FROM custgrpitem
 125                    WHERE (custgrpitem_custgrp_id=&lt;? value("custgrp_id") ?>)))
 126 &lt;? endif ?>
 127 &lt;? if exists("custgrp_pattern") ?>
 128   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 129                    FROM custgrp, custgrpitem
 130                    WHERE ( (custgrpitem_custgrp_id=custgrp_id)
 131                      AND   (custgrp_name ~ &lt;? value("custgrp_pattern") ?>) )) )
 132 &lt;? endif ?>
 133 &lt;? if exists("warehous_id") ?>
 134    AND (itemsite_warehous_id=&lt;? value("warehous_id") ?>)
 135 &lt;? endif ?>
 136 &lt;? if exists("item_id") ?>
 137   AND  (itemsite_item_id=&lt;? value("item_id") ?>)
 138 &lt;? endif ?>
 139 &lt;? if exists("prodcat_id") ?>
 140    AND (item_prodcat_id=&lt;? value("prodcat_id") ?>)
 141 &lt;? endif ?>
 142 &lt;? if exists("prodcat_pattern") ?>
 143    AND (item_prodcat_id IN (SELECT prodcat_id
 144                             FROM prodcat
 145                             WHERE (prodcat_code ~ &lt;? value("prodcat_pattern") ?>)))
 146 &lt;? endif ?>
 147 &lt;? if exists("salesrep_id") ?>
 148    AND (cohist_salesrep_id=&lt;? value("salesrep_id") ?>)
 149 &lt;? endif ?>
 150       )
 151 GROUP BY cohead_id, cust_id,cust_number,cust_custtype_id,cohist_cust_id,custtype_code,
 152          cust_name,cohist_ordernumber, cohist_ponumber,
 153          cohist_invcnumber,cohist_orderdate, cohist_invcdate
 154 ORDER BY cohist_invcdate, cohist_orderdate;</sql>
 155  </querysource>
 156  <rpthead>
 157   <height>221</height>
 158   <label>
 159    <rect>
 160     <x>5</x>
 161     <y>200</y>
 162     <width>100</width>
 163     <height>15</height>
 164    </rect>
 165    <font>
 166     <face>Arial</face>
 167     <size>8</size>
 168     <weight>normal</weight>
 169    </font>
 170    <left/>
 171    <vcenter/>
 172    <string>Doc. #</string>
 173   </label>
 174   <label>
 175    <rect>
 176     <x>355</x>
 177     <y>200</y>
 178     <width>80</width>
 179     <height>15</height>
 180    </rect>
 181    <font>
 182     <face>Arial</face>
 183     <size>8</size>
 184     <weight>normal</weight>
 185    </font>
 186    <hcenter/>
 187    <vcenter/>
 188    <string>Ship Date</string>
 189   </label>
 190   <label>
 191    <rect>
 192     <x>110</x>
 193     <y>200</y>
 194     <width>100</width>
 195     <height>15</height>
 196    </rect>
 197    <font>
 198     <face>Arial</face>
 199     <size>8</size>
 200     <weight>normal</weight>
 201    </font>
 202    <left/>
 203    <vcenter/>
 204    <string>Invoice #</string>
 205   </label>
 206   <label>
 207    <rect>
 208     <x>345</x>
 209     <y>5</y>
 210     <width>400</width>
 211     <height>40</height>
 212    </rect>
 213    <font>
 214     <face>Arial</face>
 215     <size>18</size>
 216     <weight>bold</weight>
 217    </font>
 218    <right/>
 219    <vcenter/>
 220    <string>Brief Sales History</string>
 221   </label>
 222   <label>
 223    <rect>
 224     <x>665</x>
 225     <y>200</y>
 226     <width>80</width>
 227     <height>15</height>
 228    </rect>
 229    <font>
 230     <face>Arial</face>
 231     <size>8</size>
 232     <weight>normal</weight>
 233    </font>
 234    <hcenter/>
 235    <vcenter/>
 236    <string>Total</string>
 237   </label>
 238   <label>
 239    <rect>
 240     <x>270</x>
 241     <y>200</y>
 242     <width>80</width>
 243     <height>15</height>
 244    </rect>
 245    <font>
 246     <face>Arial</face>
 247     <size>8</size>
 248     <weight>normal</weight>
 249    </font>
 250    <hcenter/>
 251    <vcenter/>
 252    <string>Ord. Date</string>
 253   </label>
 254   <line>
 255    <xstart>0</xstart>
 256    <ystart>218</ystart>
 257    <xend>745</xend>
 258    <yend>218</yend>
 259    <weight>2</weight>
 260   </line>
 261   <text>
 262    <rect>
 263     <x>10</x>
 264     <y>38</y>
 265     <width>725</width>
 266     <height>15</height>
 267    </rect>
 268    <bottompad>12</bottompad>
 269    <font>
 270     <face>Arial</face>
 271     <size>10</size>
 272     <weight>bold</weight>
 273    </font>
 274    <left/>
 275    <top/>
 276    <data>
 277     <query>Parameter Query</query>
 278     <column>filter</column>
 279    </data>
 280   </text>
 281  </rpthead>
 282  <pghead>
 283   <firstpage/>
 284   <height>6</height>
 285  </pghead>
 286  <pghead>
 287   <height>21</height>
 288   <label>
 289    <rect>
 290     <x>665</x>
 291     <y>0</y>
 292     <width>80</width>
 293     <height>15</height>
 294    </rect>
 295    <font>
 296     <face>Arial</face>
 297     <size>8</size>
 298     <weight>normal</weight>
 299    </font>
 300    <hcenter/>
 301    <vcenter/>
 302    <string>Total</string>
 303   </label>
 304   <label>
 305    <rect>
 306     <x>270</x>
 307     <y>0</y>
 308     <width>80</width>
 309     <height>15</height>
 310    </rect>
 311    <font>
 312     <face>Arial</face>
 313     <size>8</size>
 314     <weight>normal</weight>
 315    </font>
 316    <hcenter/>
 317    <vcenter/>
 318    <string>Ord. Date</string>
 319   </label>
 320   <label>
 321    <rect>
 322     <x>110</x>
 323     <y>0</y>
 324     <width>100</width>
 325     <height>15</height>
 326    </rect>
 327    <font>
 328     <face>Arial</face>
 329     <size>8</size>
 330     <weight>normal</weight>
 331    </font>
 332    <left/>
 333    <vcenter/>
 334    <string>Invoice #</string>
 335   </label>
 336   <line>
 337    <xstart>5</xstart>
 338    <ystart>15</ystart>
 339    <xend>745</xend>
 340    <yend>15</yend>
 341    <weight>2</weight>
 342   </line>
 343   <label>
 344    <rect>
 345     <x>355</x>
 346     <y>0</y>
 347     <width>80</width>
 348     <height>15</height>
 349    </rect>
 350    <font>
 351     <face>Arial</face>
 352     <size>8</size>
 353     <weight>normal</weight>
 354    </font>
 355    <hcenter/>
 356    <vcenter/>
 357    <string>Ship Date</string>
 358   </label>
 359   <label>
 360    <rect>
 361     <x>5</x>
 362     <y>0</y>
 363     <width>100</width>
 364     <height>15</height>
 365    </rect>
 366    <font>
 367     <face>Arial</face>
 368     <size>8</size>
 369     <weight>normal</weight>
 370    </font>
 371    <left/>
 372    <vcenter/>
 373    <string>Doc. #</string>
 374   </label>
 375  </pghead>
 376  <section>
 377   <name>detail</name>
 378   <detail>
 379    <key>
 380     <query>detail</query>
 381    </key>
 382    <height>16</height>
 383    <field>
 384     <rect>
 385      <x>5</x>
 386      <y>0</y>
 387      <width>100</width>
 388      <height>15</height>
 389     </rect>
 390     <font>
 391      <face>Arial</face>
 392      <size>8</size>
 393      <weight>bold</weight>
 394     </font>
 395     <left/>
 396     <vcenter/>
 397     <data>
 398      <query>detail</query>
 399      <column>sonumber</column>
 400     </data>
 401    </field>
 402    <field>
 403     <rect>
 404      <x>110</x>
 405      <y>0</y>
 406      <width>100</width>
 407      <height>15</height>
 408     </rect>
 409     <font>
 410      <face>Arial</face>
 411      <size>8</size>
 412      <weight>bold</weight>
 413     </font>
 414     <left/>
 415     <vcenter/>
 416     <data>
 417      <query>detail</query>
 418      <column>invnumber</column>
 419     </data>
 420    </field>
 421    <field>
 422     <rect>
 423      <x>665</x>
 424      <y>0</y>
 425      <width>80</width>
 426      <height>15</height>
 427     </rect>
 428     <font>
 429      <face>Arial</face>
 430      <size>8</size>
 431      <weight>bold</weight>
 432     </font>
 433     <right/>
 434     <vcenter/>
 435     <data>
 436      <query>detail</query>
 437      <column>f_total</column>
 438     </data>
 439    </field>
 440    <field>
 441     <rect>
 442      <x>355</x>
 443      <y>0</y>
 444      <width>80</width>
 445      <height>15</height>
 446     </rect>
 447     <font>
 448      <face>Arial</face>
 449      <size>8</size>
 450      <weight>bold</weight>
 451     </font>
 452     <hcenter/>
 453     <vcenter/>
 454     <data>
 455      <query>detail</query>
 456      <column>shipdate</column>
 457     </data>
 458    </field>
 459    <field>
 460     <rect>
 461      <x>270</x>
 462      <y>0</y>
 463      <width>80</width>
 464      <height>15</height>
 465     </rect>
 466     <font>
 467      <face>Arial</face>
 468      <size>8</size>
 469      <weight>bold</weight>
 470     </font>
 471     <hcenter/>
 472     <vcenter/>
 473     <data>
 474      <query>detail</query>
 475      <column>orddate</column>
 476     </data>
 477    </field>
 478   </detail>
 479  </section>
 480  <pgfoot>
 481   <height>16</height>
 482   <label>
 483    <rect>
 484     <x>0</x>
 485     <y>0</y>
 486     <width>100</width>
 487     <height>15</height>
 488    </rect>
 489    <font>
 490     <face>Arial</face>
 491     <size>8</size>
 492     <weight>normal</weight>
 493    </font>
 494    <right/>
 495    <vcenter/>
 496    <string>Report Date:</string>
 497   </label>
 498   <field>
 499    <rect>
 500     <x>705</x>
 501     <y>0</y>
 502     <width>40</width>
 503     <height>15</height>
 504    </rect>
 505    <font>
 506     <face>Arial</face>
 507     <size>8</size>
 508     <weight>bold</weight>
 509    </font>
 510    <left/>
 511    <vcenter/>
 512    <data>
 513     <query>Context Query</query>
 514     <column>page_number</column>
 515    </data>
 516   </field>
 517   <field>
 518    <rect>
 519     <x>103</x>
 520     <y>0</y>
 521     <width>100</width>
 522     <height>15</height>
 523    </rect>
 524    <font>
 525     <face>Arial</face>
 526     <size>8</size>
 527     <weight>bold</weight>
 528    </font>
 529    <left/>
 530    <vcenter/>
 531    <data>
 532     <query>Context Query</query>
 533     <column>report_date</column>
 534    </data>
 535   </field>
 536   <label>
 537    <rect>
 538     <x>600</x>
 539     <y>0</y>
 540     <width>100</width>
 541     <height>15</height>
 542    </rect>
 543    <font>
 544     <face>Arial</face>
 545     <size>8</size>
 546     <weight>normal</weight>
 547    </font>
 548    <right/>
 549    <vcenter/>
 550    <string>Page:</string>
 551   </label>
 552  </pgfoot>
 553  <rptfoot>
 554   <height>21</height>
 555   <label>
 556    <rect>
 557     <x>575</x>
 558     <y>0</y>
 559     <width>80</width>
 560     <height>15</height>
 561    </rect>
 562    <font>
 563     <face>Arial</face>
 564     <size>8</size>
 565     <weight>normal</weight>
 566    </font>
 567    <right/>
 568    <vcenter/>
 569    <string>Total:</string>
 570   </label>
 571   <field>
 572    <rect>
 573     <x>665</x>
 574     <y>0</y>
 575     <width>80</width>
 576     <height>15</height>
 577    </rect>
 578    <font>
 579     <face>Arial</face>
 580     <size>8</size>
 581     <weight>bold</weight>
 582    </font>
 583    <right/>
 584    <vcenter/>
 585    <data>
 586     <query>detail</query>
 587     <column>total</column>
 588    </data>
 589    <format builtin="true">money</format>
 590    <tracktotal/>
 591   </field>
 592  </rptfoot>
 593 </report>