Пересчеты и бонусы

Материал из BiTel WiKi

(Различия между версиями)
Перейти к: навигация, поиск
(→Функция в библиотеке скриптов 'bonus', производящая перенос перерасчета для конкретного договора)
 
(1 промежуточная версия не показана)
Строка 140: Строка 140:
<source lang="sql">
<source lang="sql">
CREATE FUNCTION `superid`(cid int(10) unsigned) RETURNS int(10) unsigned READS SQL DATA
CREATE FUNCTION `superid`(cid int(10) unsigned) RETURNS int(10) unsigned READS SQL DATA
-
BRGIN
+
BEGIN
  declare r_cid int(10) unsigned;  
  declare r_cid int(10) unsigned;  
  select if(scid>0,scid,id) into r_cid from contract where id=cid;
  select if(scid>0,scid,id) into r_cid from contract where id=cid;
Строка 146: Строка 146:
END
END
</source>
</source>
-
 
==== Глобальный скрипт переноса перерасчета для всех договоров ====
==== Глобальный скрипт переноса перерасчета для всех договоров ====
Строка 294: Строка 293:
</source>
</source>
 +
--[[Участник:Cromeshnic|Cromeshnic]] 07:39, 8 июня 2010 (UTC)

Текущая версия на 07:44, 8 июня 2010

Предыстория вопроса

Содержание

Задача

Необходимо реализовать систему, позволяющую осуществлять перерасчеты клиентам за прошлые месяцы на произвольную сумму без необходимости внесения изменений в тарифный план. Сумма пересчета должна быть отражена в детализации счета клиента. Перерасчет может быть `размазан` на несколько месяцев, если сумма перерасчета больше месячной наработки

Решение

  1. Сумму перерасчета будем задавать в виде расхода определенного типа. Для этого добавляем два новых типа расхода: "Перерасчет" (редактируемый) и "Перенос перерасчета" (нередактируемый)
  2. Заводим в модуле bill новую позицию счета: 
    bill.pos.<n>.title=Перерасчет за предыдущие месяцы
bill.pos.<n>.name=Перерасчет за предыдущие месяцы
bill.pos.<n>.summ=CHARGE($month,<ct1>,<ct2>)

    Где n - номер позиции, ct1, ct2 - наши новые типы расходов

Схема использования

  1. В течение месяца менеджеры вносят расход "Перерасчет" на договора
  2. В следующем месяце после глобального пересчета всех услуг и прямо перед закрытием периода запускается глобальный скрипт (см ниже), выполняющий перенос остатка суммы перерасчета на следующий месяц для договоров, у которых наработка оказалась меньше суммы перерасчета (в т.ч. по всем субдоговорам). Также скрипт помещает все такие договоры в специальную группу, скажем "Перенос перерасчета". Для таких договоров сумма счета окажется нулевой, поэтому нужно обрабатывать их отдельно.
  3. Закрываем период и выставляем счета
  4. Ставим в настройке счета галку "Создавать при сумме счета <=0" и выставляем нулевые счета для группы "Перенос перерасчета"
  5. Убираем галку
  6. Очищаем группу у всех договоров в ней
  7. Самое важное происходит вне биллинга - отрицательная позиция счета должна отразиться в детализации клиента. Как это сделать организационно - вопрос индивидуальный и решается с бухгалтерией. Например, в виде "бонуса".

Функция в библиотеке скриптов 'bonus', производящая перенос перерасчета для конкретного договора 

public String ProcessContractBonus(java.sql.Connection con, int cid, int uid, int CHARGE_TYPE, int CHARGE_CORR_TYPE, long gr){
 
	bu =  new BalanceUtils(con);
	cm = new ContractManager(con);
	c = cm.getContractByID(cid);
 
	//Пропускаем зависимые субдоговора
	if(c.isDependSub()){
		return("Пропускаем зависимый субдоговор "+c.getTitle()+" ("+cid+")");
	}
 
	out="id="+cid+"\ttitle="+c.getTitle()+"\t\t";
 
	//берем текущий месяц и год
	Calendar calendarLastMonth = new GregorianCalendar();
	calendarLastMonth.add(Calendar.DAY_OF_YEAR, -calendarLastMonth.get(Calendar.DAY_OF_MONTH)); //устанавливаем calendarLastMonth в последнее число предыдущего месяца
	calendarLastMonth.setTimeInMillis(Math.round(Math.floor(calendarLastMonth.getTimeInMillis()/86400000.0)*86400000)); //Отрезаем минуты и часы
	if(c.getDateTo()!=null){
		if(!c.getDateTo().after(calendarLastMonth.getTime())){
			return("Пропускаем закрытый договор "+c.getTitle()+" ("+cid+")");
		}
	}
 
	//берем сумму бонуса за предыдущий месяц минус наработку по договору за предыдущий месяц
	float sum = 0.00;
	float acc = 0.00;
 
	//очищаем предыдущие переносы бонуса с прошлого месяца на текущий для этого договора и его зависимых субдоговоров
	ps = con.prepareStatement("delete from contract_charge where ((month(dt)=? and year(dt)=? and summa>0) or (month(dt)=? and year(dt)=? and summa<0)) and pt=? and (cid in (select group_concat(id) from contract where scid=? and sub_mode=0) or cid=?);");
	ps.setInt(1, calendarLastMonth.get(Calendar.MONTH)+1);
	ps.setInt(2, calendarLastMonth.get(Calendar.YEAR));
	ps.setInt(3, (new GregorianCalendar()).get(Calendar.MONTH)+1);
	ps.setInt(4, (new GregorianCalendar()).get(Calendar.YEAR));
	ps.setInt(5, CHARGE_CORR_TYPE);
	ps.setInt(6, cid);
	ps.setInt(7, cid);
	rs = ps.executeUpdate();
 
	//Общий бонус за прошлый месяц
	ps = con.prepareStatement("select sum(ch.summa) from contract_charge ch join contract c on ch.cid=c.id where month(ch.dt)=? and year(ch.dt)=? and (ch.cid=? or (c.scid=? and c.sub_mode=0)) and (ch.pt=? or ch.pt=?)");
	ps.setInt(1, calendarLastMonth.get(Calendar.MONTH)+1);
	ps.setInt(2, calendarLastMonth.get(Calendar.YEAR));
	ps.setInt(3, cid);
	ps.setInt(4, cid);
	ps.setInt(5, CHARGE_TYPE);
	ps.setInt(6, CHARGE_CORR_TYPE);
	rs = ps.executeQuery();
	if(rs.next()) { sum = rs.getFloat(1); }
	ps.close();
	out+="бонус="+sum+"\t";
 
	//наработка
	ps = con.prepareStatement("select cb.summa3 from contract_balance cb where cb.mm=? and cb.yy=? and cb.cid=?");
	ps.setInt(1, calendarLastMonth.get(Calendar.MONTH)+1);
	ps.setInt(2, calendarLastMonth.get(Calendar.YEAR));
	ps.setInt(3, cid);
	rs = ps.executeQuery();
	if(rs.next()) { acc = rs.getFloat(1); }
	ps.close();
	out+="наработка="+acc+"\t";
 
	//Если сумма пересчета не меньше наработки, то счет будет нулевой - нужно выставить группу gr
	if(-sum-acc>=0){
		ps = con.prepareStatement("update contract set gr=gr|pow(2,?) where id=?");
		ps.setLong(1,gr);
		ps.setInt(2,cid);
		ps.executeUpdate();
	}else{
		ps = con.prepareStatement("update contract set gr=gr&~pow(2,?) where id=?");
		ps.setLong(1,gr);
		ps.setInt(2,cid);
		ps.executeUpdate();
	}
 
	//если сумма больше наработки по договору,
	if(-sum-acc>0){
		out+="переносим="+(-sum-acc);
		chargeManager = new ChargeManager(con);
		//...то добавляем положительный расход суммой разности в прошлом месяце
		charge = new Charge();
		charge.setChargeDate(calendarLastMonth.getTime());
		charge.setChargeTypeID(CHARGE_CORR_TYPE);
		charge.setComment("переносим остаток бонуса на следующий месяц");
		charge.setContractID(cid);
		charge.setID(-1);
		charge.setSumma(new BigDecimal(-sum-acc));
		charge.setUserID(0);
		chargeManager.updateCharge(charge);
		EventProcessor.getProcessor().addEvent(new ChargeEvent(charge));
		//Обновляем баланс в предыдущем месяце
		bu.updateBalance(calendarLastMonth.getTime(), cid);
 
		//...и такой же отрицательный - в будущем
		charge = new Charge();
		charge.setChargeDate((new GregorianCalendar()).getTime());
		charge.setChargeTypeID(CHARGE_CORR_TYPE);
		charge.setComment("Остаток бонуса с предыдущего месяца");
		charge.setContractID(cid);
		charge.setID(-1);
		charge.setSumma(new BigDecimal(sum+acc));
		charge.setUserID(0);
		chargeManager.updateCharge(charge);
		EventProcessor.getProcessor().addEvent(new ChargeEvent(charge));
		//Обновляем баланс в текущем месяце
		bu.updateBalance(new Date(), cid);
 
	}
	return out;
}

Пользовательская функция mysql superid: 

CREATE FUNCTION `superid`(cid int(10) UNSIGNED) RETURNS int(10) UNSIGNED READS SQL DATA
BEGIN
 declare r_cid int(10) UNSIGNED; 
 SELECT IF(scid>0,scid,id) INTO r_cid FROM contract WHERE id=cid;
 RETURN r_cid; 
END

Глобальный скрипт переноса перерасчета для всех договоров 

import bitel.billing.server.util.*;
import bitel.billing.server.contract.bean.*;
import java.math.*;
import bitel.billing.server.util.*;
import bitel.billing.server.script.bean.event.*;
 
public void main( setup, con, conSlave )
{
        email = "programmer@someprovider.ru";
	CHARGE_TYPE = 12;		//тип расхода для начисления бонуса
	CHARGE_CORR_TYPE = 13;	//тип расхода для переноса бонуса
	long SPEC_GROUP = 55; //Группа, в которую включаются все договора с пересчетом за последний месяц, не меньшим чем наработка
	includeBGBS( "bgbs://ru.bitel.bgbilling.kernel.script.common.bean.ScriptLibrary/bonus" );
 
	//Убираем у всех группу SPEC_GROUP
	ps = con.prepareStatement("update contract set gr=gr&~pow(2,?)");
	ps.setLong(1,SPEC_GROUP);
	ps.executeUpdate();
 
	ps = con.prepareStatement("select superid(cid) ccid from contract_charge where year(dt)=year(now()-interval 1 month) and month(dt)=month(now()-interval 1 month) and (pt=? or pt=?) group by ccid;");
	ps.setInt(1, CHARGE_TYPE);
	ps.setInt(2, CHARGE_CORR_TYPE);
	rs = ps.executeQuery();
	report="";
	while(rs.next()){
		cid = rs.getInt(1);
		m=ProcessContractBonus(con, cid,	0, CHARGE_TYPE, CHARGE_CORR_TYPE, SPEC_GROUP);
		print(m);
		report+=m+"\n";
	}
	try{
		MailMsg msg = new MailMsg(setup);
		msg.sendMessage(email, "Перенос бонусов для всех договоров", report);
	}catch(e){
		print("cannot send email");
	}
}

Отчет по пересчетам (csv)

Отчет не универсальный, необходимо указать в sql-запросе нужные mid модуля bill и типы платежей

.java-файл

import java.sql.*;
import java.util.*;
import java.util.regex.*;
import java.sql.*;
import bitel.billing.server.util.*;
import bitel.billing.server.contract.bean.*;
import bitel.billing.common.*;
 
public void fillReport( con, filter, bitel.billing.server.reports.BGCSVReport.ReportResult result, pageSize, pageIndex )
{
 
        query = "select "+
                        "c.id, "+
                        "c.title, "+
                        "c.comment, "+
                        "(select sum(summa) from contract_charge join contract cc on cid=cc.id where pt=13 and summa<0 and month(dt)=month(?) and year(dt)=year(?) and (cid=c.id or (cc.scid=c.id and cc.sub_mode=0))) prev, "+
                        "(select sum(summa) from contract_charge join contract cc on cid=cc.id where pt=12 and month(dt)=month(?) and year(dt)=year(?) and (cid=c.id or (cc.scid=c.id and cc.sub_mode=0))) curr, "+
                        "(select sum(summa) from contract_charge join contract cc on cid=cc.id where pt=13 and summa>0 and month(dt)=month(?) and year(dt)=year(?) and (cid=c.id or (cc.scid=c.id and cc.sub_mode=0))) next, "+
                        "(select summa3 from contract_balance cb where cb.yy=year(?) and cb.mm=month(?) and cid=c.id ) engine, "+
                        "(select sum(summ) from bill_data_6 where cid=c.id and yy=year(?) and mm=month(?)-1) bills "+
                "from "+
                        "contract c "+
                "where "+
                        "c.scid<=0 "+
                        "or c.sub_mode=1 "+
                "having (prev!=0 or curr!=0 or next!=0);";
 
        ps = con.prepareStatement( query );
 
        java.sql.Date month = TimeUtils.convertCalendarToSqlDate(filter.getCalendarParam( "month" ));
 
        ps.setDate(1, month);
        ps.setDate(2, month);
        ps.setDate(3, month);
        ps.setDate(4, month);
        ps.setDate(5, month);
        ps.setDate(6, month);
        ps.setDate(7, month);
        ps.setDate(8, month);
        ps.setDate(9, month);
        ps.setDate(10, month);
 
        rs = ps.executeQuery();
 
        data = new ArrayList(rs.getFetchSize());
        while(rs.next()){
                map = new HashMap();
                float expected_bill_summ = (rs.getFloat(4)==null? 0 : rs.getFloat(4)) + (rs.getFloat(5)==null? 0 : rs.getFloat(5)) + (rs.getFloat(6)==null? 0 : rs.getFloat(6)) + (rs.getFloat(7)==null? 0 : rs.getFloat(7));
                float expected_next = (rs.getFloat(4)==null? 0 : rs.getFloat(4)) + (rs.getFloat(5)==null? 0 : rs.getFloat(5)) + (rs.getFloat(7)==null? 0 : rs.getFloat(7));
                String expected_bill_summ_s;
                if(expected_bill_summ>0){
                        expected_bill_summ_s=new java.text.DecimalFormat("0.##").format((double)(expected_bill_summ)).toString();
                }else{
                        expected_bill_summ_s="0";
                }
 
                String expected_next_s;
                if(expected_next<0){
                        expected_next_s=new java.text.DecimalFormat("0.##").format((double)(-expected_next)).toString();
                }else{
                        expected_next_s=null;
                }
                map.put("cid",rs.getString(1));
                map.put("title",rs.getString(2));
                map.put("comment",rs.getString(3));
                map.put("prev",rs.getString(4));
                map.put("curr",rs.getString(5));
                map.put("next",rs.getString(6));
                map.put("engine",rs.getString(7));
                map.put("bills",rs.getString(8));
//              map.put("expected_bill_summ",rs.getFloat(4)+rs.getFloat(5)+rs.getFloat(6)+rs.getFloat(7));
                map.put("expected_bill_summ", expected_bill_summ_s);
                map.put("expected_next", expected_next_s);
                data.add(map);
        }
 
        result.setData( data );
}

.rep.xml-файл

<?xml version="1.0" encoding="UTF-8"?>
<report title="Пересчеты за месяц" type="java">
 
        <month name="month" title="Месяц"/>
 
        <fields>
                <item id="cid" title="cid"/>
                <item id="title" title="Договор"/>
                <item id="comment" title="Комментарий"/>
                <item id="prev" title="Перенос с предыдущего месяца"/>
                <item id="curr" title="Пересчеты за месяц"/>
                <item id="next" title="Факт. перенос на следующий месяц"/>
                <item id="expected_next" title="Предполагаемый перенос на следующий месяц"/>
                <item id="engine" title="Наработка"/>
                <item id="expected_bill_summ" title="Ожидаемая сумма счетов"/>
                <item id="bills" title="Фактическая сумма счетов"/>
        </fields>
</report>

--Cromeshnic 07:39, 8 июня 2010 (UTC)

Источник — «http://wiki.bitel.ru/index.php/%D0%9F%D0%B5%D1%80%D0%B5%D1%81%D1%87%D0%B5%D1%82%D1%8B_%D0%B8_%D0%B1%D0%BE%D0%BD%D1%83%D1%81%D1%8B»
Личные инструменты