#!/usr/bin/perl

#    
#    
#      bot1.cfg
# sms      bot1.1.cfg


$botID = 1;


use DBI;

require "/etc/cherry/cherry.cfg";
require "/usr/lib/cherry/docflow/translate";
require "/usr/lib/cherry/docflow/translate2";
require "/etc/cherry/docflow/bot1.cfg";
require "/etc/cherry/docflow/bot1.1.cfg";


($sec_,$min_,$hour_,$mday_,$mon_,$year,$wday_,$yday_,$isdst_) = localtime(time);
if ($hour_>18 || $hour_<9) {
	exit;
}





$dbh = DBI->connect("dbi:$CFG::DB_TYPE:dbname=$CFG::DB_NAME;host=$CFG::DB_HOST",
                    "$CFG::DB_USER",
                    "$CFG::DB_USER_PASSWORD",
                    { RaiseError => 1, AutoCommit => 1 });


#$dbhRC = DBI->connect("dbi:Pg:dbname=rc;host=avatar.t72.ru",
#                    "$CFG::DB_MASTER",
#                    "$CFG::DB_MASTER_PASSWORD",
#                    { RaiseError => 1, AutoCommit => 1 });


$sth=$dbh->prepare(qq{	
	SELECT DISTINCT docflow_docs.id ,
	docflow_types.name 
	FROM docflow_docs 
	LEFT JOIN docflow_bots ON docflow_docs.id=docflow_bots.doc AND docflow_bots.bot=$botID
	JOIN docflow_types ON docflow_docs.type_=docflow_types.id
        JOIN docflow_states_run ON docflow_docs.current_state=docflow_states_run.state
        WHERE docflow_bots.doc IS NULL AND docflow_states_run.name='  '
	AND docflow_types.name='   '
	});

$sth->execute;

while(my($doc,$doctypename)=$sth->fetchrow_array) {

        ($log)=$dbh->selectrow_array(
                qq{SELECT MAX(docflow_logs.id)
                FROM docflow_logs
                WHERE docflow_logs.doc=$doc
                }
        );

	($subj,$comm)=$dbh->selectrow_array(
		qq{SELECT docflow_logs.subject,docflow_logs.comment
		FROM docflow_logs WHERE docflow_logs.doc=$doc AND docflow_logs.id=$log
		}
	);

	
	($phone)=$dbh->selectrow_array(
		qq{SELECT docflow_info_run.value 
		FROM docflow_info_run WHERE doc=$doc AND name=''
		}
	);

	$phone=int($phone);




	#($name,$address) = $dbh->selectrow_array(
	#	qq{	SELECT clients.name as client_name, 
	#		clients.address as client_address 
	#		FROM v_mount JOIN clients ON v_mount.client=clients.id
        #        	WHERE v_mount.phone=$phone limit 1});



	($name,$address) = $dbh->selectrow_array(
		qq{ 	SELECT base_clients.name as client_name,
			CASE WHEN length(telephone_services.address_mount)>0 THEN telephone_services.address_mount ELSE
				base_clients.address_post END as client_address
			FROM base_clients JOIN telephone_services ON
				base_clients.oper_id=telephone_services.oper_id AND
				base_clients.client_id=telephone_services.client_id
			WHERE (telephone_services.date_expire IS NULL OR telephone_services.date_expire>current_date) AND
			telephone_services.user_id='$phone'  limit 1});


	#$name=win2koi($name);
	#$address=win2koi($address);

	$subject="$doc $doctypename:$phone";
	$body="$subj\n$comm\n$name\n$address";


	foreach $to_ (split(" ",$list_)) {
		open (SENDMAIL , "| /usr/sbin/sendmail -t $to_") or die "Can't open pipe!\n";
		print SENDMAIL "From: Billing <$return_to> \n";
		print SENDMAIL "To: $to_\n";
		print SENDMAIL "Subject: $subject\n";
		print SENDMAIL "Content-Type: text/plain;\n";
		print SENDMAIL "\n";
		print SENDMAIL "$body\n\n";
		close (SENDMAIL);
	}


        foreach $to_ (split(" ",$list_sms)) {
                open (SENDMAIL , "| /usr/sbin/sendmail -t $sms_gate") or die "Can't open pipe!\n";
                print SENDMAIL "From: Billing <$return_to> \n";
                print SENDMAIL "To: $sms_gate\n";
                print SENDMAIL "Subject: $to_\n";
                print SENDMAIL "Content-Type: text/plain;\n";
                print SENDMAIL "\n";
                print SENDMAIL trans_lat("$phone: $subj: $address: $name: $comm \n\n");
                close (SENDMAIL);
        }






	$dbh->do("UPDATE docflow_info_run SET value='$name' WHERE name='' AND doc=$doc");
        $dbh->do("UPDATE docflow_info_run SET value='$address' WHERE name=' ' AND doc=$doc");

	$dbh->do("INSERT INTO docflow_bots(doc,bot) VALUES ($doc,$botID) ");


}
