libname bc 'x:\bc2008';

proc sql;
connect to odbc (dsn=eer_local);
create table bc.records as select * from connection to odbc
(select rgi_purchasedate
      , reg_date
      , reg_tactnumber
      , checknumber
      , issue_brand
      , issue_ean
      , issue_name
      , rgi_serialnumber
      , rgi_flagnodeliver2
      , cns_salutation
      , cns_title
      , cns_name1
      , cns_name2
      , cns_company
      , cns_country
      , cns_zipcode
      , cns_city
      , cns_street
      , cns_streetno
      , cns_email
      , bc_validationStatus
      , bc_campaignStatus as bc_bcStatus
      , bc_documentNumber
      , bc_value
      , acc_manualCompName
      , acc_sheetOwner
      , acc_sheet
      , acc_agency
      , acc_bbn
      , acc_ibn
      , trader_erpnumber
      , trader_name
      , trader_zipcode
      , trader_city
      , trader_country
      , tactertradergroup
      , reg_usage
      , tacterSalesman
      , tacterSalesmanName
      , tacterTradergroup
      , tacterERPNumber
      , tacterCompany
      , tacterZIPCode
      , tacterCity
   from eer_report.record
  where rgi_purchasedate > '2008-06-01' and
        bc_campaignStatus is not null and
        bc_value > 0 and
        man_idorgunitofferor = 26619
);

disconnect from odbc;
quit;

PROC IMPORT OUT=deficient
            DATAFILE= "x:\bc2008\deficient.txt"
            DBMS=TAB REPLACE;
RUN;

proc sql;
     create table bc.records
         as (select * from bc.records
             where reg_tactnumber not in (select reg_tactnumber from deficient)
            );
quit;

data inl;
     infile "x:\bc2008\all_inland.txt" dsd delimiter='09'x firstobs=2;
     informat acninh $40. acnnr $40. zlb $20. anzahl commax10.2 text1 $40. text2 $40. text3 $40. datum ddmmyy10.;
     input acninh acnnr zlb anzahl text1 text2 text3 datum;
     tactnr = scan (text3, 2);
     tactnr = substr (tactnr,1,6) !! '-' !! substr (tactnr,7);
run;

data ausl;
     infile "x:\bc2008\all_ausland.txt" dsd delimiter=';' firstobs=2;
     informat acninh $40. kdname2 $40. kdname1 $40. kdfirma $40. kdstr $40. kdort $40. land_txt $10.
         anzahl commax10.2 text1 $40. text2 $40. text3 $40. datum ddmmyy10. bbn $20. ibn $20.;
     input acninh kdname2 kdname1 kdfirma kdstr kdort land_txt
         anzahl text1 text2 text3 datum bbn ibn ;
     tactnr = scan (text3, 2);
     tactnr = substr (tactnr,1,6) !! '-' !! substr (tactnr,7);
run;

proc sql;
     %macro weg (wen);
      delete from inl where index (lowcase (acninh), "&wen.");
      delete from ausl where index (lowcase (acninh), "&wen.");
     %mend;
     %weg (xxxxxxxxxx);
     select 'inl.?', reg_tactnumber from bc.records where reg_tactnumber in (select tactnr from inl) and bc_bcStatus ^= 3;
     select 'ausl.?', reg_tactnumber from bc.records where reg_tactnumber in (select tactnr from ausl) and bc_bcStatus ^= 3;
     update bc.records set bc_bcStatus = 3 where reg_tactnumber in (select tactnr from inl) and bc_bcStatus ^= 3;
     update bc.records set bc_bcStatus = 3 where reg_tactnumber in (select tactnr from ausl) and bc_bcStatus ^= 3;
quit;

data bbn_ibn_issue bc.records;
     set bc.records;
     if cns_country ^= 'DE' and
        not (length(compress(acc_agency)) = 8 and acc_sheet ^= '') and
        (acc_bbn = '' or acc_ibn = '' or verify (acc_bbn, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ')) then
        output bbn_ibn_issue;
     else
        output bc.records;
run;
proc export DATA=bbn_ibn_issue
            OUTFILE="x:\bc2008\bbn_ibn_issue.txt"
            DBMS=TAB REPLACE;
run;

data zlb_issue bc.records;
     set bc.records;
     if cns_country = 'DE' and bc_bcstatus = 2 and
        (length(compress(acc_agency)) ^= 8 or verify (acc_agency, '0123456789 ')) then
        output zlb_issue;
     else
        output bc.records;
run;
proc export DATA=zlb_issue
            OUTFILE="x:\bc2008\zlb_issue.txt"
            DBMS=TAB REPLACE;
run;

data _null_;
     set bc.records (where=(bc_bcStatus = 2)) end=fertig;
     if fertig then put 'Anzahl Status=2: ' _n_;
run;

proc sort data=bc.records;
     by reg_date;
run;

data _null_;
     set bc.records (
        where = (
           bc_bcStatus = 2 and
           ( cns_country='DE' or (length(compress(acc_agency)) = 8 and verify (acc_agency, '0123456789 ') = 0 and acc_sheet ^= '') )
                )         );

     text1 = 'Klima!';
     text2 = scan(issue_name,1)!!' (SNO '!!compress(rgi_serialnumber)!!')';
     text3 = 'bc '!!compress (reg_tactnumber, '-');
     if _n_ >= 1401 then do;
        file "x:\bc2008\bccsv-1401.txt" dsd delimiter='09'x;
        if _n_ = 1401 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 1201 then do;
        file "x:\bc2008\bccsv-1201.txt" dsd delimiter='09'x;
        if _n_ = 1201 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 1001 then do;
        file "x:\bc2008\bccsv-1001.txt" dsd delimiter='09'x;
        if _n_ = 1001 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 801 then do;
        file "x:\bc2008\bccsv-801.txt" dsd delimiter='09'x;
        if _n_ = 801 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 601 then do;
        file "x:\bc2008\bccsv-601.txt" dsd delimiter='09'x;
        if _n_ = 601 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 401 then do;
        file "x:\bc2008\bccsv-401.txt" dsd delimiter='09'x;
        if _n_ = 401 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 201 then do;
        file "x:\bc2008\bccsv-201.txt" dsd delimiter='09'x;
        if _n_ = 201 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     else if _n_ >= 1 then do;
        file "x:\bc2008\bccsv-001.txt" dsd delimiter='09'x;
        if _n_ = 1 then put 'acninh' '09'x 'acnnr' '09'x 'zlb' '09'x 'anzahl' '09'x 'text1' '09'x 'text2' '09'x 'text3' '09'x 'datum';
     end;
     datum = today();
     put acc_sheetowner acc_sheet acc_agency bc_value : commax12.2 text1 text2 text3 datum : ddmmyy10.;

     file "x:\bc2008\bccsv-update.txt" lrecl=400;
     length u $ 400;

run;

data _null_;
     set bc.records (
        where = (
           bc_bcStatus = 2 and
           cns_country^='DE' and
           not (length(compress(acc_agency)) = 8 and verify (acc_agency, '0123456789 ') = 0 and acc_sheet ^= '')
                )         );
     text1 = 'Klima!';
     text2 = scan(issue_name,1)!!' (SNO '!!compress(rgi_serialnumber)!!')';
     text3 = 'bc '!!compress (reg_tactnumber, '-');
     if _n_ >= 201 then do;
        file "x:\bc2008\bccsv-aus-201.txt" dsd delimiter=';';
        if _n_ = 201 then put 'acninh' ';' 'kdname2' ';' 'kdname1' ';' 'kdfirma' ';' 'kdstr' ';' 'kdort' ';' 'land_txt' ';' 'anzahl' ';' 'text1' ';' 'text2' ';' 'text3' ';' 'datum' ';' 'bbn' ';' 'ibn' ';' ;
     end;
     else if _n_ >= 1 then do;
        file "x:\bc2008\bccsv-aus-001.txt" dsd delimiter=';';
        if _n_ = 1 then put 'acninh' ';' 'kdname2' ';' 'kdname1' ';' 'kdfirma' ';' 'kdstr' ';' 'kdort' ';' 'land_txt' ';' 'anzahl' ';' 'text1' ';' 'text2' ';' 'text3' ';' 'datum' ';' 'bbn' ';' 'ibn' ';' ;
     end;
     datum = today();

     length land_txt $ 20;
     if cns_country = "AT" then land_txt = "ÖSTERREICH";
     if cns_country = "CH" then land_txt = "SCHWEIZ";
     if cns_country = "NL" then land_txt = "NIEDERLANDE";
     if cns_country = "CZ" then land_txt = "TSCHECHISCHE REPUBLIK";
     if cns_country = "BE" then land_txt = "BELGIEN";

     if land_txt = '' then return; /* put / 'Unbekanntes Land: ' cns_country=; ... z.B. LV */

     length name $ 40;
     if cns_company = '' then
     name = trim(cns_name2) !! ', ' !! trim(cns_name1);
     else
     name = cns_company;

     put name cns_name2 cns_name1 cns_company cns_street cns_city land_txt bc_value : commax12.2 text1 text2 text3 datum : ddmmyy10. acc_bbn acc_ibn;

run;

proc export DATA=bc.records
            OUTFILE="x:\bc2008\records.txt"
            DBMS=TAB REPLACE;
run;

proc sql;
     create table report as (
     select cns_country
          , intnx ('week', reg_date, 0) as week
          , reg_date
          , reg_usage
          , issue_name
          , tacterSalesman
          , tacterSalesmanName
          , tacterTradergroup
          , tacterERPNumber
          , tacterCompany
          , tacterZIPCode
          , tacterCity
          , rgi_serialnumber
          , bc_value
          , bc_bcstatus
       from bc.records );
quit;
data reportv / view = reportv;
     set report;
     where bc_bcstatus in (2,3);
run;
proc export DATA=report
            OUTFILE="x:\bc2008\report.txt"
            DBMS=TAB REPLACE;
run;
proc summary data=report nway;
     where bc_bcstatus in (2,3);
     class week bc_value;
     output out=report_sum1 (drop=_type_ rename=(_freq_=anzahl));
run;
proc export DATA=report_sum1
            OUTFILE="x:\bc2008\report_value.txt"
            DBMS=TAB REPLACE;
run;
proc summary data=report nway;
     where bc_bcstatus in (2,3);
     class issue_name;
     output out=report_sum2 (drop=_type_ rename=(_freq_=anzahl));
run;
proc export DATA=report_sum2
            OUTFILE="x:\bc2008\report_issue.txt"
            DBMS=TAB REPLACE;
run;
proc summary data=report nway;
     class bc_bcstatus;
     output out=report_sum3 (drop=_type_ rename=(_freq_=anzahl));
run;
proc export DATA=report_sum3
            OUTFILE="x:\bc2008\report_status.txt"
            DBMS=TAB REPLACE;
run;