/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.m16e.mpbiz;
import com.m16e.mpbiz.common.MpBizAppConfig;
import com.m16e.mpbiz.common.bizclasses.MpBizException;
import com.m16e.mpbiz.common.MpBizUser;
import com.m16e.mpbiz.docs.MpBizDoc;
import com.m16e.mpbiz.docs.MpDocException;
import com.m16e.mpbiz.docs.MpDocFactory;
import com.m16e.mpbiz.docs.manipulators.ProdDocLinData;
import com.m16e.mpbiz.prods.MpBizProd;
import com.m16e.mpbiz.tables.DbDoc;
import com.m16e.mpbiz.tables.DbDocLin;
import com.m16e.mpbiz.tables.DbProd;
import com.m16e.mpbiz.tables.DbProdArm;
import com.m16e.mpbiz.tables.DbProdInvent;
import com.m16e.mpbiz.tables.DbProdLot;
import com.m16e.mpbiz.tables.DbProdLotInvent;
import com.m16e.tools.Barcode;
import com.m16e.tools.KnownBarcodes;
import com.m16e.tools.MpAppContext;
import com.m16e.tools.MpDay;
import com.m16e.tools.MpTimestamp;
import com.m16e.tools.MpToolsException;
import com.m16e.tools.Tools;
import com.m16e.tools.db.Database;
import com.m16e.tools.db.DatabaseException;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import org.apache.log4j.Logger;
/**
*
* @author carlos
*/
///////////////////////////////////////////////////////////////
public class MpBizInventoryClassic
{
static Logger logger = Logger.getLogger( MpBizInventoryClassic.class );
private static int consoleColumns = 40;
Integer docId = null;
int lastDocLinId = 0;
boolean printLabelsByDefault = false;
int warehouseId = 0;
///////////////////////////////////////////////////////////////
public MpBizInventoryClassic() {}
public MpBizInventoryClassic( int warehouseId )
{
this.warehouseId = warehouseId;
}
///////////////////////////////////////////////////////////////
private void listLots( int warehouseId, int prodId )
throws SQLException, DatabaseException
{
String query =
"select " +
DbProdLot.FN_SUPPLIER_LOT_ID + ", " +
DbProdLotInvent.T_NAME + "." + DbProdLotInvent.FN_QUANT + ", " +
DbProdLotInvent.FN_QT1 +
" from " +
DbProdLotInvent.T_NAME + " join " +
DbProdLot.T_NAME + " using ( " +
DbProdLot.FN_PROD_ID + ", " + DbProdLot.FN_PROD_LOT_ID + " )" +
" where " +
DbProdLotInvent.FN_ARM_ID + " = ? and " +
DbProdLotInvent.FN_PROD_ID + " = ? " +
" order by " +
DbProdLot.FN_SUPPLIER_LOT_ID;
print( "Lote\t\tQuant.\tCont." );
PreparedStatement ps =
MpAppContext.getInstance().getDatabase().getPreparedStatement( query );
ps.setInt( 1, warehouseId );
ps.setInt( 2, prodId );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
String supLotId = rs.getString( 1 );
System.out.print( supLotId );
int len = supLotId.length();
if( len < 8 )
System.out.print( "\t\t" );
else
System.out.print( "\t" );
System.out.print( String.format( "%4.0f", rs.getDouble( 2 ) ) + "\t" );
System.out.println( String.format( "%4.0f", rs.getDouble( 3 ) ) );
}
}
///////////////////////////////////////////////////////////////
private void removeProdLot(
PreparedStatement psProdInvent, BufferedReader br,
int warehouseId, int prodId )
throws IOException, SQLException, DatabaseException
{
System.out.print( "Lote a remover: " );
String supplierLotId = br.readLine();
print( "Confirma remover lote [" + supplierLotId + "] (s/N)?" );
String s = br.readLine();
if( s.length() == 0 || s.toLowerCase().startsWith( "s" ) )
{
DbProdLot prodLot =
MpBizProd.getProdLot( prodId, supplierLotId );
if( prodLot == null )
{
print( "lote desconhecido: " + supplierLotId + "!" );
}
else
{
DbProdLotInvent plotInvt = new DbProdLotInvent();
plotInvt.setArmId( warehouseId );
plotInvt.setProdId( prodId );
plotInvt.setProdLotId( prodLot.getProdLotId() );
if( !plotInvt.getById() )
print( "lote desconhecido: " + plotInvt + "!" );
else
plotInvt.delete();
}
}
}
///////////////////////////////////////////////////////////////
private void countProdLot(
PreparedStatement psProdInvent, BufferedReader br,
int warehouseId, MpBizProd bizProd )
throws IOException, SQLException, DatabaseException, MpToolsException, MpBizException
{
Integer prodId = bizProd.getProd().getProdId();
DbProdLotInvent prodLotInvent = new DbProdLotInvent();
System.out.print( "Lote: " );
String supplierLotId = br.readLine();
prodLotInvent.setArmId( warehouseId );
prodLotInvent.setProdId( prodId );
MpDay day = null;
DbProdLot prodLot =
MpBizProd.getProdLot( prodId, supplierLotId );
if( prodLot != null )
{
prodLotInvent.setProdLotId( prodLot.getProdLotId() );
if( prodLotInvent.getById() )
{
day = new MpDay( prodLotInvent.getValidUntill() );
}
}
System.out.print( "Val.(" );
if( day != null )
System.out.print( day.getDateYYYYMMDD() );
System.out.print( "): " );
String sValidUntill = br.readLine();
if( sValidUntill.length() == 0 )
{
sValidUntill = day.getDateYYYYMMDD();
}
MpDay validUntill = null;
for( ; validUntill == null; )
{
try
{
validUntill = new MpDay( sValidUntill );
}
catch( ParseException ex )
{
print( "Data INVALIDA!" );
print( "O formato da data deve ser: AAAA-MM-DD (Ex. 2008-01-31)" );
System.out.print( "Val.: " );
sValidUntill = br.readLine();
if( sValidUntill.length() == 0 )
return;
}
}
prodLotInvent.setArmId( warehouseId );
prodLotInvent.setProdId( prodId );
if( prodLot != null )
prodLotInvent.setProdLotId( prodLot.getProdLotId() );
Double qt1 = null;
if( prodLotInvent.getById() )
{
qt1 = prodLotInvent.getQt1();
System.out.print( "Quant. (" + qt1 + "): " );
}
else
{
System.out.print( "Quant. (0): " );
}
prodLotInvent.setValidUntill( validUntill.getSqlDate() );
String s = br.readLine();
prodLotInvent.setQt1( new Double( s ) );
if( qt1 == null )
{
print( "Lote nao existe\nPretende cria-lo (s/N)?" );
s = br.readLine();
if( !s.toLowerCase().startsWith( "s" ) )
{
return;
}
}
Database database = null;
try
{
database = MpAppContext.getInstance().getDatabase();
database.beginTransaction();
if( qt1 == null )
{
if( prodLot == null )
{
prodLot = new DbProdLot();
prodLot.setProdId( prodLotInvent.getProdId() );
prodLot.setProdLotId( prodLotInvent.getProdLotId() );
prodLot.setValidUntill( prodLotInvent.getValidUntill() );
prodLot = MpBizProd.insertProdLot( prodLot );
prodLotInvent.setProdLotId( prodLot.getProdLotId() );
String prefix =
MpBizAppConfig.getInstance().getBarcodeGeneratedPrefix();
final KnownBarcodes[] barcodes =
MpBizAppConfig.getInstance().getBarcodeTypeOrder();
String barcode =
Barcode.validateBarcode(
barcodes[0], prodLotInvent.getProdId().toString(), prefix, null );
bizProd.setUseBarcode( barcode );
bizProd.update();
bizProd.refreshBarcodes();
}
prodLotInvent.insert();
database.commitTransaction();
System.out.println( "Lote criado!" );
}
else
{
prodLotInvent.update(
DbProdLotInvent.VALID_UNTILL, DbProdLotInvent.QT1 );
}
double total = 0;
for(
DbProdLotInvent pl :
DbProdLotInvent.getList(
DbProdLotInvent.FN_ARM_ID + " = ? and " +
DbProdLotInvent.FN_PROD_ID + " = ?",
DbProdLotInvent.FN_PROD_LOT_ID,
warehouseId, prodId ) )
{
total += pl.getQt1();
}
psProdInvent.setDouble( 1, total );
psProdInvent.setInt( 2, prodId );
psProdInvent.setInt( 3, warehouseId );
psProdInvent.executeUpdate();
print( "\n>>> Actualizada existencia!\n\n" );
}
catch( Exception e )
{
logger.error( e.getMessage(), e );
}
finally
{
if( database != null && database.inTransaction() )
database.rollbackTransaction();
}
print( "Imprimir etiqueta(s)? (s/N)?" );
s = br.readLine();
if( s.toLowerCase().startsWith( "s" ) )
{
bizProd.printLabel( prodLotInvent.getProdLotId(), prodLotInvent.getQuant().intValue() );
}
}
///////////////////////////////////////////////////////////////
private void countProd(
PreparedStatement psProdInvent, BufferedReader br,
int warehouseId, String prodCode )
throws SQLException, MpBizException, DatabaseException, IOException, MpToolsException
{
MpBizProd bizProd = MpBizProd.getByBarcode( prodCode );
if( bizProd == null )
bizProd = MpBizProd.getByAlfaCode( prodCode );
if( bizProd == null )
{
Integer i = null;
try
{
i = new Integer( prodCode );
bizProd = MpBizProd.getByProdId( i );
}
catch( NumberFormatException e ) {}
}
if( bizProd == null )
{ // 0123456789 123456789
print( "produto desconhecido!" );
return;
}
print( "Nome: " + bizProd.getProd().getName() );
int prodId = bizProd.getProd().getProdId();
DbProdInvent prodInvent = new DbProdInvent();
DbProdLotInvent prodLotInvent = new DbProdLotInvent();
prodInvent.setArmId( warehouseId );
prodInvent.setProdId( prodId );
if( !prodInvent.getById() )
{
System.out.println(
"Produto ("+ prodInvent.getProdId() +
") inexistente na lista de inventario!" );
System.out.print( "Pretende cria-lo (s/N)?" );
String s = br.readLine();
if( !s.toLowerCase().startsWith( "s" ) )
{
return;
}
DbProdArm prodArm = new DbProdArm();
prodArm.setProdId( prodId );
prodArm.setArmId( warehouseId );
prodArm.persist();
DbProd prod = bizProd.getProd();
prodInvent.setName( prod.getName() );
prodInvent.setUltPCusto( prod.getUltPCusto() );
prodInvent.setPvenda( bizProd.getSellPrice() );
prodInvent.setTaxaId( prod.getTaxaId() );
prodInvent.setTipoProdId( prod.getTipoProdId() );
prodInvent.setUniMovId( prod.getUniMovId() );
prodInvent.setDataUltPCusto( prod.getDataUltPCusto() );
prodInvent.setFamiliaId( prod.getFamiliaId() );
prodInvent.setSubFamiliaId( prod.getSubFamiliaId() );
prodInvent.setMarcaId( prod.getMarcaId() );
prodInvent.setModeloId( prod.getModeloId() );
prodInvent.setUnitsPerBox( prod.getUnitsPerBox() );
Database database = MpAppContext.getInstance().getDatabase();
CallableStatement cs =
database.getCallableStatement( "call mpbiz_get_preferred_prod_supplier( ? )" );
cs.setInt( 1, prodId );
ResultSet rs = cs.executeQuery();
if( rs.next() )
prodInvent.setProdSupplierId( rs.getInt( 1 ) );
rs.close();
cs.close();
prodInvent.insert();
}
print( prodInvent.getName() );
if( bizProd.hasLotsControl() )
{ // 0123456789 123456789
print( "(controle de lotes)\n" );
listLots( warehouseId, prodInvent.getProdId() );
print( "Exist.: " + prodInvent.getQuant() + "\tCont.:" + prodInvent.getQt1() );
for( ; ; )
{
print( "--\nContar / Listar / Remover (C/l/r)" );
String s = br.readLine();
if( s.length() == 0 || s.toLowerCase().startsWith( "c" ) )
{
break;
}
if( s.toLowerCase().startsWith( "l" ) )
{
listLots( warehouseId, prodInvent.getProdId() );
}
else if( s.toLowerCase().startsWith( "r" ) )
{
removeProdLot( psProdInvent, br, warehouseId, bizProd.getProd().getProdId() );
}
}
countProdLot( psProdInvent, br, warehouseId, bizProd );
}
else
{
print( "Exist.: " + prodInvent.getQuant() );
System.out.print( "Quant. (" + prodInvent.getQt1() + "): " );
String s = br.readLine();
if( s.length() > 0 )
{
Double qt = Double.parseDouble( s );
psProdInvent.setDouble( 1, qt );
psProdInvent.setInt( 2, prodInvent.getProdId() );
psProdInvent.setInt( 3, warehouseId );
psProdInvent.executeUpdate();
print( "\n>>> Actualizada existencia!\n\n" );
print( "Imprimir etiqueta(s)? (s/N)?" );
s = br.readLine();
if( s.toLowerCase().startsWith( "s" ) )
{
bizProd.printLabel( qt.intValue() );
}
}
}
}
///////////////////////////////////////////////////////////////
/**
*
* @param warehouseId
* @throws java.sql.SQLException
* @throws IOException
* @throws DatabaseException
*/
public void countProds()
throws SQLException, IOException, DatabaseException
{
String updProd =
"update " + DbProdInvent.T_NAME +
" set " + DbProdInvent.FN_QT1 + " = ?" +
" where " +
DbProdInvent.FN_PROD_ID + " = ? and " +
DbProdInvent.FN_ARM_ID + " = ?";
Database database = MpAppContext.getInstance().getDatabase();
PreparedStatement ps = database.getPreparedStatement( updProd );
DbProdInvent prodInvent = new DbProdInvent();
InputStreamReader isr = new InputStreamReader( System.in );
BufferedReader br = new BufferedReader( isr );
if( !login( br ) )
return;
System.out.println( "\f" );
for( ; ; )
{
try
{
System.out.print( "\nCodigo: " );
String s = br.readLine();
if( s.equalsIgnoreCase( "x" ) )
{
System.out.println( "\nFim!" );
break;
}
if( s.length() > 0 )
{
countProd( ps, br, warehouseId, s );
}
}
catch( Exception e )
{
logger.error( e.getMessage(), e );
return;
}
}
}
///////////////////////////////////////////////////////////////
static private void print( String text )
{
for( String line : Tools.splitLine( text, consoleColumns) )
{
System.out.println( line );
}
}
///////////////////////////////////////////////////////////////
public static int getConsoleColumns() { return consoleColumns; }
public static void setConsoleColumns( int aConsoleColumns ) { consoleColumns = aConsoleColumns; }
public boolean isPrintLabelsByDefault() { return printLabelsByDefault; }
public void setPrintLabelsByDefault( boolean printLabelsByDefault ) { this.printLabelsByDefault = printLabelsByDefault; }
///////////////////////////////////////////////////////////////
private boolean login( BufferedReader br )
throws IOException, SQLException, DatabaseException
{
boolean logged = false;
print( "Utilizador: " );
String user = br.readLine();
if( user.length() > 0 )
{
print( "Password: " );
String password = br.readLine();
MpBizUser mbu = MpBizUser.getInstance();
logged = mbu.login( user, password );
}
return logged;
}
///////////////////////////////////////////////////////////////
private void initDoc()
throws SQLException, DatabaseException, MpDocException, MpToolsException, MpBizException
{
DbDoc doc = new DbDoc();
doc.setTipoEntId( MpDocFactory.TENT_INTERNAL );
doc.setTipoDocId( MpDocFactory.TDOC_ACERTO_INVENTARIO );
MpDay day = new MpDay();
doc.setDataDoc( day.getSqlDate() );
doc.setEntId( MpDocFactory.INTERNAL_ENT );
docId = new Integer(
MpDocFactory.getInstance().getLastDocNumber(
day,
MpDocFactory.TENT_INTERNAL, MpDocFactory.TDOC_ACERTO_INVENTARIO ) + 1 );
int i = 0;
boolean done = false;
while( i++ < MpBizDoc.MAX_TRIES )
{
try
{
doc.insert();
done = true;
}
catch( SQLException e )
{
logger.error( e.getMessage(), e );
doc.setDocId( ++docId );
}
if( done )
break;
}
if( !done )
throw new MpDocException( "Erro a criar documento: ", docId );
}
///////////////////////////////////////////////////////////////
private void addProdToDoc( ProdDocLinData dld )
throws SQLException, DatabaseException, MpDocException, MpToolsException, MpBizException
{
MpBizProd bizProd = dld.getBizProd();
if( docId == null )
initDoc();
DbDocLin dl = new DbDocLin();
dl.setTipoEntId( MpDocFactory.TENT_INTERNAL );
dl.setTipoDocId( MpDocFactory.TDOC_ACERTO_INVENTARIO );
dl.setDocId( docId );
dl.setDocLinId( ++lastDocLinId );
DbProd prod = bizProd.getProd();
dl.setItemId( prod.getProdId() );
dl.setDescricao( prod.getName() );
dl.setQuant( dld.getQt() );
dl.setBarcode( bizProd.getUseBarcode() );
DbProdLot prodLot = dld.getProdLot();
if( prodLot != null )
{
dl.setProdLotId( prodLot.getProdId() );
dl.setSupplierLotId( prodLot.getSupplierLotId() );
dl.setTs1( new MpTimestamp( prodLot.getValidUntill() ).getSqlTimestamp() );
}
dl.insert();
}
///////////////////////////////////////////////////////////////
static public void main( String[] args )
{
try
{
// countProds();
}
catch( Exception e )
{
logger.error( e.getMessage(), e );
}
System.exit( 0 );
}
}
|