특정의류의 누적 대여자 정보

10001if commented 6 years ago

세가지 의류의 누적 대여자 정보를 요청드립니다. 원하는 정보는 다음과 같습니다.

no.	이름	대여일	대여목적	주소	대여메세지(없는 경우에는 공란)	합격여부(없는 경우에는 공란)
1	홍길동	2015-11-02	입사면접	서울시 광진구 화양동 48-3	감사합니다.	합격

aanoaa commented 6 years ago

SELECT
      o.id          AS order_id
    , u.name        AS user_name
    , o.rental_date AS order_rental_date
    , o.purpose     AS order_purpose
    , o.purpose2    AS order_purpose2
    , ui.address1   AS user_address1
    , ui.address2   AS user_address2
    , ui.address3   AS user_address3
    , ui.address4   AS user_address4
    , o.message     AS order_message
    , o.pass        AS pass

FROM `order`             AS o
LEFT JOIN `order_detail` AS od ON o.id       = od.order_id
LEFT JOIN `user`         AS u  ON u.id       = o.user_id
LEFT JOIN `user_info`    AS ui ON ui.user_id = u.id

WHERE (
    od.clothes_code = '0J0PK'
    AND o.parent_id IS NULL       -- 부모 주문서
    AND o.rental_date IS NOT NULL -- 실질 대여
)
ORDER BY
    order_rental_date DESC

aanoaa commented 6 years ago

use utf8;
use strict;
use warnings;
use Getopt::Long;
use Pod::Usage;

use Text::CSV;
use OpenCloset::Schema;

binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';

my $conf = {
    database => {
        dsn  => "...",
        user => '...,
        pass => '...,
        opts => {
            quote_char        => q{`},
            mysql_enable_utf8 => 1,
            on_connect_do     => 'SET NAMES utf8',
            RaiseError        => 1,
            AutoCommit        => 1,
        },
    }
};

my %options;
GetOptions( \%options, '--help' );
run( \%options, @ARGV );

sub run {
    my ( $opts, @args ) = @_;
    pod2usage(0) if $opts->{help};

    my $db     = $conf->{database};
    my $schema = OpenCloset::Schema->connect(
        {
            dsn      => $db->{dsn},
            user     => $db->{user},
            password => $db->{pass},
            %{ $db->{opts} },
        }
    );

    my $arrayref = $schema->storage->dbh_do(
        sub {
            my ( $storage, $dbh, @args ) = @_;
            $dbh->selectall_arrayref(
                <<SQL
SELECT
      u.name        AS user_name
    , o.rental_date AS order_rental_date
    , o.purpose     AS order_purpose
    , ui.address1   AS user_address1
    , ui.address2   AS user_address2
    , ui.address3   AS user_address3
    , ui.address4   AS user_address4
    , o.message     AS order_message
    , o.pass        AS pass

FROM `order`             AS o
LEFT JOIN `order_detail` AS od ON o.id       = od.order_id
LEFT JOIN `user`         AS u  ON u.id       = o.user_id
LEFT JOIN `user_info`    AS ui ON ui.user_id = u.id

WHERE (
    od.clothes_code = '0J1H6'
    AND o.parent_id IS NULL       -- 부모 주문서
    AND o.rental_date IS NOT NULL -- 실질 대여
)
ORDER BY
    order_rental_date DESC
SQL

# 0J0PK
# 0J0RR
# 0J1H6
            );
        }
    );

    my $csv = Text::CSV->new;
    print
        "이름,대여일,대여목적,주소,대여메세지,합격여부\n";
    for my $row (@$arrayref) {
        $csv->combine(
            $row->[0], $row->[1], $row->[2], $row->[4] . $row->[6],
            $row->[7], $row->[8] ? '합격' : '',
        );
        print $csv->string(), "\n";
    }
}

$ perl a.pl > J1H6.csv

aanoaa commented 6 years ago

이메일로 전달해드렸습니다.

10001if commented 6 years ago

확인 하였습니다. 감사합니다. ^^

opencloset / OpenCloset-Share-Web

특정의류의 누적 대여자 정보 #168