Пример продажи OEM ключей с помощью скрипта

CREATE TABLE oem_key (
    id          int(11) AUTO_INCREMENT NOT NULL,
    lid         int(150) NOT NULL,
    text        varchar(250) NOT NULL,
    cid         int(11) NOT NULL DEFAULT '-1',
    date        datetime NULL,
    PRIMARY KEY (id)
    )
 
CREATE TABLE oem_key_list (
    id          int(11) AUTO_INCREMENT NOT NULL,
    title       varchar(150) NOT NULL,
    amount      decimal(10,2) NOT NULL ,
    PRIMARY KEY (id)
    )

import bitel.billing.server.contract.bean.*;
import bitel.billing.server.util.*;
import java.sql.*;
import java.util.*;
import bitel.billing.server.contract.bean.*;
import bitel.billing.server.script.bean.event.AdditionalActionEvent;
import bitel.billing.server.script.bean.event.GetAdditionalWebActionListEvent;
import bitel.billing.server.script.bean.event.GetAdditionalActionListEvent;
import java.math.BigDecimal;
 
if(event instanceof GetAdditionalWebActionListEvent || event instanceof GetAdditionalActionListEvent)
{
  Statement st = con.createStatement();
  ResultSet rs = st.executeQuery("select id,title FROM oem_key_list ");
  while( rs.next() )
  {
    event.addAction( rs.getInt( 1 ), rs.getString( 2 ) );
  }
  rs.close();
  st.close();
}
else if (event instanceof AdditionalActionEvent)
{
  chargeTypeId = 11; //ид типа расхода!!!
  lid =  event.getActionId();
  cid = event.getContractID();
  String query = "SELECT t1.*, t2.amount, t2.title FROM oem_key AS t1 JOIN oem_key_list AS t2 ON t1.lid = t2.id "
                 + " WHERE t1.cid=? and t1.lid=? LIMIT 0,1";
  PreparedStatement ps = con.prepareStatement( query );
  ps.setInt( 1, -1 );
  ps.setInt( 2, lid );
 
  ResultSet rs = ps.executeQuery();
  if ( rs.next() )
  {
    BigDecimal amount = rs.getBigDecimal( "t2.amount" );
    BigDecimal summa = rs.getBigDecimal( "t2.amount" );
    String listTitle = rs.getString( "t2.title" );
    String keyText = rs.getString( "t1.text" );
    int kid = rs.getInt( "t1.id" );
    // проверить есть ли средства на балансе
    BalanceUtils bu = new BalanceUtils( con );
    BigDecimal balance = bu.getBalance( new Date(), cid );
    if ( balance.compareTo( amount ) < 0 )
    {
      event.addReport("На балансе не хватает средств");
      return;
    }
    String comment = listTitle + " [" + keyText + "] ";
    // списать
    ChargeManager cm = new ChargeManager( con );
    Date date = new Date();
    Charge charge = new Charge();
    charge.setID( -1 );
    charge.setChargeTypeID( chargeTypeId );
    charge.setContractID( cid );
    charge.setChargeDate( date );
    charge.setSumma( summa );
    charge.setComment( comment );
    cm.updateCharge( "new",charge );
    bu.updateBalance( date, cid );
    rs.close();
    ps.close();
 
    // пометить ключ
    query = "UPDATE oem_key SET cid =?, date=?  WHERE id =?";
    ps = con.prepareStatement( query );
    ps.setInt( 1, cid );
    ps.setTimestamp( 2, TimeUtils.convertDateToTimestamp( date ) );
    ps.setInt( 3, kid );
    ps.executeUpdate();
    //формируем отчет на страницу		   
    String report = "С вашего баланса списана сумма "+ amount + " руб.\nВаш ключ: "+ keyText;
    event.addReport( report );
    //отчет на почту
    query = "SELECT count(*)  FROM oem_key where cid = -1 and lid="+lid;
    rs = ps.executeQuery(query);
    count = -1;
    if(rs.next())
    {
      count = rs.getInt(1);
    }			
    ContractManager cb =new ContractManager(con);
    String contractTitle = cb.getContractByID( cid ).getTitle();		
 
    StringBuilder sb = new StringBuilder();
    sb.append( "продан ключ id =>" );
    sb.append( kid );
    sb.append( " [" );
    sb.append( keyText );
    sb.append( "]\n" );
    sb.append( "Дата продажи " );
    sb.append( date );
    sb.append( "\n" );
    sb.append( "ID договора: " );
    sb.append( cid );
    sb.append( "\n" );
    sb.append( "Договор: " );
    sb.append( contractTitle );
    sb.append( "\n" );
    sb.append( "Для списка " );
    sb.append( listTitle );
    sb.append( " свободных ключей осталось " );
    sb.append( count );
 
    (new MailMsg( setup )).sendMessageEx( "report@operator.ru", "Продажа OEM ключа", sb.toString(), "text/" + contentType );
  }
  else
  {
    event.addReport("Нет свободных ключей");
  }
}
return this;

--оперции за вчера
SELECT t2.title AS list, t1.text AS oem_key, t3.title AS contract_title,t1.date 
FROM oem_key AS t1 LEFT JOIN oem_key_list AS t2 ON t1.lid=t2.id
LEFT JOIN contract AS t3 ON t1.cid=t3.id
WHERE DATE(date)=CURDATE()-1 AND cid != -1 ORDER BY list
 
--кол-во и сумма по спискам за вчера
SELECT t2.title AS list, count(*) AS count, count(*)*t2.amount AS summa  
FROM oem_key AS t1 LEFT JOIN oem_key_list AS t2 ON t1.lid=t2.id
WHERE DATE(date)=CURDATE()-1 GROUP BY list
 
-- остаток свободных ключей на данный момент
SELECT t2.title AS list, count(*) AS free_key 
FROM oem_key AS t1 LEFT JOIN oem_key_list AS t2 ON t1.lid=t2.id
WHERE cid = -1 GROUP BY lid
 
//запрос ключей договора
SELECT t2.title AS list, t1.text AS oem_key, t3.title AS contract_title,t1.date 
FROM oem_key AS t1 LEFT JOIN oem_key_list AS t2 ON t1.lid=t2.id
LEFT JOIN contract AS t3 ON t1.cid=t3.id
WHERE t3.title=''
 
--оперции за прошлый месяц 
SELECT t2.title AS list, t1.text AS oem_key, t3.title AS contract_title,t1.date 
FROM oem_key AS t1 LEFT JOIN oem_key_list AS t2 ON t1.lid=t2.id
LEFT JOIN contract AS t3 ON t1.cid=t3.id
WHERE MONTH(date)=MONTH(CURDATE())-1 AND YEAR(date)=YEAR(CURDATE()) AND cid != -1 ORDER BY list
GO
--кол-во и сумма по спискам за прошлый месяц
SELECT t2.title AS list, count(*) AS count, count(*)*t2.amount AS summa  
FROM oem_key AS t1 LEFT JOIN oem_key_list AS t2 ON t1.lid=t2.id
WHERE MONTH(date)=MONTH(CURDATE())-1 AND YEAR(date)=YEAR(CURDATE()) GROUP BY list