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

   1 <!DOCTYPE openRPTDef>
   2 <report>
   3  <title>Sales History</title>
   4  <name>SalesHistory</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>detail</name>
  20   <sql>SELECT cohist_ordernumber AS sonumber,
  21        cohist_invcnumber AS invnumber,
  22        formatDate(cohist_orderdate) AS orddate,
  23        formatDate(cohist_invcdate, 'Return') AS invcdate,
  24        item_number, item_descrip1, item_descrip2,
  25        formatQty(cohist_qtyshipped) AS shipped,
  26        &lt;? if exists("showPrices") ?>
  27        formatPrice(currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate)) AS unitprice,
  28        formatMoney(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) AS f_total,
  29        &lt;? else ?>
  30        '' AS unitprice,
  31        '' AS f_total,
  32        &lt;? endif ?>
  33        round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2) AS total
  34   FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
  35               JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
  36 &lt;? if exists("includeMisc") ?>
  37               LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
  38               LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
  39               LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
  40 &lt;? else ?>
  41               JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
  42               JOIN site() ON (warehous_id=itemsite_warehous_id)
  43               JOIN item ON (item_id=itemsite_item_id)
  44 &lt;? endif ?>
  45 &lt;? if exists("cohead_id") ?>
  46               JOIN cohead ON (cohead_number=cohist_ordernumber)
  47 &lt;? endif ?>
  48 WHERE ( (true)
  49 &lt;? if exists("includeMisc") ?>
  50   AND  (COALESCE(cohist_misc_type, '') &lt;> 'F')
  51   AND  (COALESCE(cohist_misc_type, '') &lt;> 'T')
  52 &lt;? endif ?>
  53 &lt;? if exists("startDate") ?>
  54   AND  (cohist_invcdate >= &lt;? value("startDate") ?>)
  55 &lt;? endif ?>
  56 &lt;? if exists("endDate") ?>
  57   AND  (cohist_invcdate &lt;= &lt;? value("endDate") ?>)
  58 &lt;? endif ?>
  59 &lt;? if exists("shipStartDate") ?>
  60   AND  (cohist_shipdate >= &lt;? value("shipStartDate") ?>)
  61 &lt;? endif ?>
  62 &lt;? if exists("shipEndDate") ?>
  63   AND  (cohist_shipdate &lt;= &lt;? value("shipEndDate") ?>)
  64 &lt;? endif ?>
  65 &lt;? if exists("salesrep_id") ?>
  66   AND  (cohist_salesrep_id=&lt;? value("salesrep_id") ?>)
  67 &lt;? endif ?>
  68 &lt;? if exists("shipto_id") ?>
  69   AND  (cohist_shipto_id=&lt;? value("shipto_id") ?>)
  70 &lt;? endif ?>
  71 &lt;? if exists("billToName") ?>
  72   AND  (UPPER(cohist_billtoname) ~ UPPER(&lt;? value("billToName") ?>))
  73 &lt;? endif ?>
  74 &lt;? if exists("cust_id") ?>
  75   AND  (cohist_cust_id=&lt;? value("cust_id") ?>)
  76 &lt;? endif ?>
  77 &lt;? if exists("custtype_id") ?>
  78   AND  (cust_custtype_id=&lt;? value("custtype_id") ?>)
  79 &lt;? elseif exists("custtype_pattern") ?>
  80   AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
  81                              FROM custtype
  82                              WHERE (custtype_code ~ &lt;? value("custtype_pattern") ?>)))
  83 &lt;? endif ?>
  84 &lt;? if exists("by_custgrp" ?>
  85   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
  86                    FROM custgrpitem))
  87 &lt;? endif ?>
  88 &lt;? if exists("custgrp_id") ?>
  89   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
  90                    FROM custgrpitem
  91                    WHERE (custgrpitem_custgrp_id=&lt;? value("custgrp_id") ?>)))
  92 &lt;? endif ?>
  93 &lt;? if exists("custgrp_pattern") ?>
  94   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
  95                    FROM custgrp, custgrpitem
  96                    WHERE ( (custgrpitem_custgrp_id=custgrp_id)
  97                      AND   (custgrp_name ~ &lt;? value("custgrp_pattern") ?>) )) )
  98 &lt;? endif ?>
  99
 100 &lt;? if exists("item_id") ?>
 101   AND  (itemsite_item_id=&lt;? value("item_id") ?>)
 102 &lt;? endif ?>
 103 &lt;? if exists("prodcat_id") ?>
 104   AND (item_prodcat_id=&lt;? value("prodcat_id") ?>)
 105 &lt;? endif ?>
 106 &lt;? if exists("prodcat_pattern") ?>
 107   AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
 108                            FROM prodcat
 109                            WHERE (prodcat_code ~ &lt;? value("prodcat_pattern") ?>)))
 110 &lt;? endif ?>
 111
 112 &lt;? if exists("warehous_id") ?>
 113   AND  (itemsite_warehous_id=&lt;? value("warehous_id") ?>)
 114 &lt;? endif ?>
 115 &lt;? if exists("shipzone_id") ?>
 116   AND (cohist_shipzone_id=&lt;? value("shipzone_id") ?>)
 117 &lt;? endif ?>
 118 &lt;? if exists("saletype_id") ?>
 119   AND (cohist_saletype_id=&lt;? value("saletype_id") ?>)
 120 &lt;? endif ?>
 121 &lt;? if exists("cohead_id") ?>
 122   AND (cohead_id=&lt;? value("cohead_id") ?>)
 123 &lt;? endif ?>
 124       )
 125 ORDER BY cohist_invcdate, item_number</sql>
 126  </querysource>
 127  <querysource>
 128   <name>head</name>
 129   <sql>SELECT &lt;? if exists("showPrices") ?>
 130            text('Unit Price') AS lbl_unitprice,
 131            text('Total') AS lbl_total
 132        &lt;? else ?>
 133            text('') AS lbl_unitprice,
 134            text('') AS lbl_total
 135        &lt;? endif ?>;</sql>
 136  </querysource>
 137  <rpthead>
 138   <height>233</height>
 139   <label>
 140    <rect>
 141     <x>345</x>
 142     <y>3</y>
 143     <width>400</width>
 144     <height>40</height>
 145    </rect>
 146    <font>
 147     <face>Arial</face>
 148     <size>18</size>
 149     <weight>bold</weight>
 150    </font>
 151    <right/>
 152    <vcenter/>
 153    <string>Sales History</string>
 154   </label>
 155   <field>
 156    <rect>
 157     <x>665</x>
 158     <y>200</y>
 159     <width>80</width>
 160     <height>15</height>
 161    </rect>
 162    <font>
 163     <face>Arial</face>
 164     <size>8</size>
 165     <weight>normal</weight>
 166    </font>
 167    <hcenter/>
 168    <vcenter/>
 169    <data>
 170     <query>head</query>
 171     <column>lbl_total</column>
 172    </data>
 173   </field>
 174   <label>
 175    <rect>
 176     <x>5</x>
 177     <y>200</y>
 178     <width>100</width>
 179     <height>15</height>
 180    </rect>
 181    <font>
 182     <face>Arial</face>
 183     <size>8</size>
 184     <weight>normal</weight>
 185    </font>
 186    <left/>
 187    <vcenter/>
 188    <string>Doc. #</string>
 189   </label>
 190   <label>
 191    <rect>
 192     <x>270</x>
 193     <y>200</y>
 194     <width>80</width>
 195     <height>15</height>
 196    </rect>
 197    <font>
 198     <face>Arial</face>
 199     <size>8</size>
 200     <weight>normal</weight>
 201    </font>
 202    <hcenter/>
 203    <vcenter/>
 204    <string>Ord. Date</string>
 205   </label>
 206   <label>
 207    <rect>
 208     <x>355</x>
 209     <y>215</y>
 210     <width>100</width>
 211     <height>15</height>
 212    </rect>
 213    <font>
 214     <face>Arial</face>
 215     <size>8</size>
 216     <weight>normal</weight>
 217    </font>
 218    <left/>
 219    <vcenter/>
 220    <string>Description</string>
 221   </label>
 222   <label>
 223    <rect>
 224     <x>355</x>
 225     <y>200</y>
 226     <width>100</width>
 227     <height>15</height>
 228    </rect>
 229    <font>
 230     <face>Arial</face>
 231     <size>8</size>
 232     <weight>normal</weight>
 233    </font>
 234    <left/>
 235    <vcenter/>
 236    <string>Item Number</string>
 237   </label>
 238   <label>
 239    <rect>
 240     <x>505</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>Shipped</string>
 253   </label>
 254   <label>
 255    <rect>
 256     <x>110</x>
 257     <y>200</y>
 258     <width>100</width>
 259     <height>15</height>
 260    </rect>
 261    <font>
 262     <face>Arial</face>
 263     <size>8</size>
 264     <weight>normal</weight>
 265    </font>
 266    <left/>
 267    <vcenter/>
 268    <string>Invoice #</string>
 269   </label>
 270   <line>
 271    <xstart>5</xstart>
 272    <ystart>230</ystart>
 273    <xend>745</xend>
 274    <yend>230</yend>
 275    <weight>2</weight>
 276   </line>
 277   <field>
 278    <rect>
 279     <x>585</x>
 280     <y>200</y>
 281     <width>80</width>
 282     <height>15</height>
 283    </rect>
 284    <font>
 285     <face>Arial</face>
 286     <size>8</size>
 287     <weight>normal</weight>
 288    </font>
 289    <hcenter/>
 290    <vcenter/>
 291    <data>
 292     <query>head</query>
 293     <column>lbl_unitprice</column>
 294    </data>
 295   </field>
 296   <label>
 297    <rect>
 298     <x>270</x>
 299     <y>215</y>
 300     <width>80</width>
 301     <height>15</height>
 302    </rect>
 303    <font>
 304     <face>Arial</face>
 305     <size>8</size>
 306     <weight>normal</weight>
 307    </font>
 308    <hcenter/>
 309    <vcenter/>
 310    <string>Invc. Date</string>
 311   </label>
 312   <text>
 313    <rect>
 314     <x>15</x>
 315     <y>48</y>
 316     <width>725</width>
 317     <height>15</height>
 318    </rect>
 319    <bottompad>12</bottompad>
 320    <font>
 321     <face>Arial</face>
 322     <size>10</size>
 323     <weight>bold</weight>
 324    </font>
 325    <left/>
 326    <top/>
 327    <data>
 328     <query>Parameter Query</query>
 329     <column>filter</column>
 330    </data>
 331   </text>
 332  </rpthead>
 333  <pghead>
 334   <firstpage/>
 335   <height>6</height>
 336  </pghead>
 337  <pghead>
 338   <height>36</height>
 339   <label>
 340    <rect>
 341     <x>270</x>
 342     <y>15</y>
 343     <width>80</width>
 344     <height>15</height>
 345    </rect>
 346    <font>
 347     <face>Arial</face>
 348     <size>8</size>
 349     <weight>normal</weight>
 350    </font>
 351    <hcenter/>
 352    <vcenter/>
 353    <string>Invc. Date</string>
 354   </label>
 355   <label>
 356    <rect>
 357     <x>110</x>
 358     <y>0</y>
 359     <width>100</width>
 360     <height>15</height>
 361    </rect>
 362    <font>
 363     <face>Arial</face>
 364     <size>8</size>
 365     <weight>normal</weight>
 366    </font>
 367    <left/>
 368    <vcenter/>
 369    <string>Invoice #</string>
 370   </label>
 371   <label>
 372    <rect>
 373     <x>5</x>
 374     <y>0</y>
 375     <width>100</width>
 376     <height>15</height>
 377    </rect>
 378    <font>
 379     <face>Arial</face>
 380     <size>8</size>
 381     <weight>normal</weight>
 382    </font>
 383    <left/>
 384    <vcenter/>
 385    <string>Doc. #</string>
 386   </label>
 387   <label>
 388    <rect>
 389     <x>505</x>
 390     <y>0</y>
 391     <width>80</width>
 392     <height>15</height>
 393    </rect>
 394    <font>
 395     <face>Arial</face>
 396     <size>8</size>
 397     <weight>normal</weight>
 398    </font>
 399    <hcenter/>
 400    <vcenter/>
 401    <string>Shipped</string>
 402   </label>
 403   <field>
 404    <rect>
 405     <x>665</x>
 406     <y>0</y>
 407     <width>80</width>
 408     <height>15</height>
 409    </rect>
 410    <font>
 411     <face>Arial</face>
 412     <size>8</size>
 413     <weight>normal</weight>
 414    </font>
 415    <hcenter/>
 416    <vcenter/>
 417    <data>
 418     <query>head</query>
 419     <column>lbl_total</column>
 420    </data>
 421   </field>
 422   <label>
 423    <rect>
 424     <x>355</x>
 425     <y>0</y>
 426     <width>100</width>
 427     <height>15</height>
 428    </rect>
 429    <font>
 430     <face>Arial</face>
 431     <size>8</size>
 432     <weight>normal</weight>
 433    </font>
 434    <left/>
 435    <vcenter/>
 436    <string>Item Number</string>
 437   </label>
 438   <line>
 439    <xstart>5</xstart>
 440    <ystart>30</ystart>
 441    <xend>745</xend>
 442    <yend>30</yend>
 443    <weight>2</weight>
 444   </line>
 445   <label>
 446    <rect>
 447     <x>355</x>
 448     <y>15</y>
 449     <width>100</width>
 450     <height>15</height>
 451    </rect>
 452    <font>
 453     <face>Arial</face>
 454     <size>8</size>
 455     <weight>normal</weight>
 456    </font>
 457    <left/>
 458    <vcenter/>
 459    <string>Description</string>
 460   </label>
 461   <field>
 462    <rect>
 463     <x>585</x>
 464     <y>0</y>
 465     <width>80</width>
 466     <height>15</height>
 467    </rect>
 468    <font>
 469     <face>Arial</face>
 470     <size>8</size>
 471     <weight>normal</weight>
 472    </font>
 473    <hcenter/>
 474    <vcenter/>
 475    <data>
 476     <query>head</query>
 477     <column>lbl_unitprice</column>
 478    </data>
 479   </field>
 480   <label>
 481    <rect>
 482     <x>270</x>
 483     <y>0</y>
 484     <width>80</width>
 485     <height>15</height>
 486    </rect>
 487    <font>
 488     <face>Arial</face>
 489     <size>8</size>
 490     <weight>normal</weight>
 491    </font>
 492    <hcenter/>
 493    <vcenter/>
 494    <string>Ord. Date</string>
 495   </label>
 496  </pghead>
 497  <section>
 498   <name>detail</name>
 499   <detail>
 500    <key>
 501     <query>detail</query>
 502    </key>
 503    <height>51</height>
 504    <field>
 505     <rect>
 506      <x>355</x>
 507      <y>15</y>
 508      <width>300</width>
 509      <height>15</height>
 510     </rect>
 511     <font>
 512      <face>Arial</face>
 513      <size>8</size>
 514      <weight>bold</weight>
 515     </font>
 516     <left/>
 517     <vcenter/>
 518     <data>
 519      <query>detail</query>
 520      <column>item_descrip1</column>
 521     </data>
 522    </field>
 523    <field>
 524     <rect>
 525      <x>5</x>
 526      <y>0</y>
 527      <width>100</width>
 528      <height>15</height>
 529     </rect>
 530     <font>
 531      <face>Arial</face>
 532      <size>8</size>
 533      <weight>bold</weight>
 534     </font>
 535     <left/>
 536     <vcenter/>
 537     <data>
 538      <query>detail</query>
 539      <column>sonumber</column>
 540     </data>
 541    </field>
 542    <field>
 543     <rect>
 544      <x>355</x>
 545      <y>30</y>
 546      <width>300</width>
 547      <height>15</height>
 548     </rect>
 549     <font>
 550      <face>Arial</face>
 551      <size>8</size>
 552      <weight>bold</weight>
 553     </font>
 554     <left/>
 555     <vcenter/>
 556     <data>
 557      <query>detail</query>
 558      <column>item_descrip2</column>
 559     </data>
 560    </field>
 561    <field>
 562     <rect>
 563      <x>585</x>
 564      <y>0</y>
 565      <width>80</width>
 566      <height>15</height>
 567     </rect>
 568     <font>
 569      <face>Arial</face>
 570      <size>8</size>
 571      <weight>bold</weight>
 572     </font>
 573     <right/>
 574     <vcenter/>
 575     <data>
 576      <query>detail</query>
 577      <column>unitprice</column>
 578     </data>
 579    </field>
 580    <field>
 581     <rect>
 582      <x>270</x>
 583      <y>0</y>
 584      <width>80</width>
 585      <height>15</height>
 586     </rect>
 587     <font>
 588      <face>Arial</face>
 589      <size>8</size>
 590      <weight>bold</weight>
 591     </font>
 592     <hcenter/>
 593     <vcenter/>
 594     <data>
 595      <query>detail</query>
 596      <column>orddate</column>
 597     </data>
 598    </field>
 599    <field>
 600     <rect>
 601      <x>505</x>
 602      <y>0</y>
 603      <width>80</width>
 604      <height>15</height>
 605     </rect>
 606     <font>
 607      <face>Arial</face>
 608      <size>8</size>
 609      <weight>bold</weight>
 610     </font>
 611     <right/>
 612     <vcenter/>
 613     <data>
 614      <query>detail</query>
 615      <column>shipped</column>
 616     </data>
 617    </field>
 618    <line>
 619     <xstart>5</xstart>
 620     <ystart>45</ystart>
 621     <xend>745</xend>
 622     <yend>45</yend>
 623     <weight>0</weight>
 624    </line>
 625    <field>
 626     <rect>
 627      <x>270</x>
 628      <y>15</y>
 629      <width>80</width>
 630      <height>15</height>
 631     </rect>
 632     <font>
 633      <face>Arial</face>
 634      <size>8</size>
 635      <weight>bold</weight>
 636     </font>
 637     <hcenter/>
 638     <vcenter/>
 639     <data>
 640      <query>detail</query>
 641      <column>invcdate</column>
 642     </data>
 643    </field>
 644    <field>
 645     <rect>
 646      <x>355</x>
 647      <y>0</y>
 648      <width>150</width>
 649      <height>15</height>
 650     </rect>
 651     <font>
 652      <face>Arial</face>
 653      <size>8</size>
 654      <weight>bold</weight>
 655     </font>
 656     <left/>
 657     <vcenter/>
 658     <data>
 659      <query>detail</query>
 660      <column>item_number</column>
 661     </data>
 662    </field>
 663    <field>
 664     <rect>
 665      <x>665</x>
 666      <y>0</y>
 667      <width>80</width>
 668      <height>15</height>
 669     </rect>
 670     <font>
 671      <face>Arial</face>
 672      <size>8</size>
 673      <weight>bold</weight>
 674     </font>
 675     <right/>
 676     <vcenter/>
 677     <data>
 678      <query>detail</query>
 679      <column>f_total</column>
 680     </data>
 681    </field>
 682    <field>
 683     <rect>
 684      <x>110</x>
 685      <y>0</y>
 686      <width>100</width>
 687      <height>15</height>
 688     </rect>
 689     <font>
 690      <face>Arial</face>
 691      <size>8</size>
 692      <weight>bold</weight>
 693     </font>
 694     <left/>
 695     <vcenter/>
 696     <data>
 697      <query>detail</query>
 698      <column>invnumber</column>
 699     </data>
 700    </field>
 701   </detail>
 702  </section>
 703  <section>
 704   <name>total</name>
 705   <detail>
 706    <key>
 707     <query>showPrices</query>
 708    </key>
 709    <height>16</height>
 710    <field>
 711     <rect>
 712      <x>665</x>
 713      <y>0</y>
 714      <width>80</width>
 715      <height>15</height>
 716     </rect>
 717     <font>
 718      <face>Arial</face>
 719      <size>8</size>
 720      <weight>bold</weight>
 721     </font>
 722     <right/>
 723     <vcenter/>
 724     <data>
 725      <query>detail</query>
 726      <column>total</column>
 727     </data>
 728     <format builtin="true">money</format>
 729     <tracktotal/>
 730    </field>
 731    <label>
 732     <rect>
 733      <x>575</x>
 734      <y>0</y>
 735      <width>80</width>
 736      <height>15</height>
 737     </rect>
 738     <font>
 739      <face>Arial</face>
 740      <size>8</size>
 741      <weight>normal</weight>
 742     </font>
 743     <right/>
 744     <vcenter/>
 745     <string>Total Sales:</string>
 746    </label>
 747   </detail>
 748  </section>
 749  <pgfoot>
 750   <height>16</height>
 751   <label>
 752    <rect>
 753     <x>0</x>
 754     <y>0</y>
 755     <width>100</width>
 756     <height>15</height>
 757    </rect>
 758    <font>
 759     <face>Arial</face>
 760     <size>8</size>
 761     <weight>normal</weight>
 762    </font>
 763    <right/>
 764    <vcenter/>
 765    <string>Report Date:</string>
 766   </label>
 767   <field>
 768    <rect>
 769     <x>705</x>
 770     <y>0</y>
 771     <width>40</width>
 772     <height>15</height>
 773    </rect>
 774    <font>
 775     <face>Arial</face>
 776     <size>8</size>
 777     <weight>bold</weight>
 778    </font>
 779    <left/>
 780    <vcenter/>
 781    <data>
 782     <query>Context Query</query>
 783     <column>page_number</column>
 784    </data>
 785   </field>
 786   <label>
 787    <rect>
 788     <x>600</x>
 789     <y>0</y>
 790     <width>100</width>
 791     <height>15</height>
 792    </rect>
 793    <font>
 794     <face>Arial</face>
 795     <size>8</size>
 796     <weight>normal</weight>
 797    </font>
 798    <right/>
 799    <vcenter/>
 800    <string>Page:</string>
 801   </label>
 802   <field>
 803    <rect>
 804     <x>103</x>
 805     <y>0</y>
 806     <width>100</width>
 807     <height>15</height>
 808    </rect>
 809    <font>
 810     <face>Arial</face>
 811     <size>8</size>
 812     <weight>bold</weight>
 813    </font>
 814    <left/>
 815    <vcenter/>
 816    <data>
 817     <query>Context Query</query>
 818     <column>report_date</column>
 819    </data>
 820   </field>
 821  </pgfoot>
 822 </report>