public ExcelDataProvider(String classname, String methodname) {
try {
int dotNum = classname.indexOf(".");
if (dotNum > 0) {
classname = classname.substring(classname.lastIndexOf(".") + 1, classname.length());
}
String path = "data/" + classname + ".xls";
InputStream inputStream = new FileInputStream(path);
book = Workbook.getWorkbook(inputStream);
// 方法名即为sheet名
sheet = book.getSheet(methodname);
// sheet = book.getSheet(0);
rowNum = sheet.getRows();
Cell[] cell = sheet.getRow(0);
columnNum = cell.length;
columnnName = new String[cell.length];
for (int i = 0; i < cell.length; i++) {
columnnName[i] = cell[i].getContents().toString();
}
this.currentRowNo++;
} catch (Exception e) {
e.printStackTrace();
Assert.fail("unable to read Excel data");
}
}
public static List<ConsoleVo> readExcel(String fileName) {
List<ConsoleVo> list = new ArrayList<ConsoleVo>();
try {
Workbook book = Workbook.getWorkbook(new File(fileName));
Sheet[] sheets = book.getSheets();
for (Sheet s : sheets) {
for (int i = 1; i < s.getRows(); i++) {
ConsoleVo m = new ConsoleVo();
Cell[] cells = s.getRow(i);
if (cells != null) {
System.out.println(cells.length);
m.setLoginName(StringUtil.getNotNullValueString(cells[0].getContents()));
m.setUserName(StringUtil.getNotNullValueString(cells[1].getContents()));
m.setDeptId(StringUtil.getNotNullValueString(cells[2].getContents()));
m.setOrders(StringUtil.getNotNullValueString(cells[3].getContents()));
list.add(m);
} else {
System.out.print("excel格式错误导入数据失败！");
return null;
}
}
}
book.close();
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void main(String args[]) {
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
System.out.println("start load file-------------------------");
InputStream is = new FileInputStream("E:/account.xls"); // 创建输入
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0); // 读取第一个sheet
int colNum = rs.getColumns(); // 列数
int rowNum = rs.getRows(); // 行数
// System.out.println(colNum+" "+rowNum);
// 创建account bean
ApplicationContext ctx =
new ClassPathXmlApplicationContext(
new String[] {
"spring/applicationContext-model-account.xml", "spring/applicationContext.xml"
});
AccountService g = (AccountService) ctx.getBean("accountService");
for (int i = 1; i < rowNum; i++) {
String no = rs.getCell(0, i).getContents();
String pass = rs.getCell(1, i).getContents();
String name = rs.getCell(2, i).getContents();
String maj = rs.getCell(3, i).getContents();
String grade = rs.getCell(4, i).getContents();
String cla = rs.getCell(5, i).getContents();
String idn = rs.getCell(6, i).getContents();
String email = rs.getCell(7, i).getContents();
String phone = rs.getCell(8, i).getContents();
String status = rs.getCell(9, i).getContents();
String role = rs.getCell(10, i).getContents();
ModelAccount ma = new ModelAccount();
ma.setAccountNo(no);
ma.setAccountPassword(pass);
ma.setAccountRealName(name);
ma.setAccountMajority(maj);
ma.setAccountGrade(grade);
ma.setAccountClass(cla);
ma.setAccountIdentification(idn);
ma.setAccountEmail(email);
ma.setAccountMobilePhone(phone);
ma.setAccountStatus(0);
ModelRoles ac = new ModelRoles();
ac.setRole_id(role);
ma.setAccountRole(ac);
g.saveOrUpdate(ma);
// ma.setAccount_role(account_role)
// System.out.println(no);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void createExcel(String file) {
String filename = createPath(file);
System.out.println(filename);
/*
try {
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("sv", "SE"));
WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);
WritableSheet s = workbook.createSheet("Sheet1", 0);
//writeDataSheet(s);
workbook.write();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}**/
try {
File newFile = new File(filename);
Writer out =
new BufferedWriter(new OutputStreamWriter(new FileOutputStream(newFile), "UTF8"));
// BufferedWrite output = new BufferedWriter(new OutputStreamReader(new
// FileInputS§tream(filename), "iso-8859-1"));
// FileWriter fw = new FileWriter(newFile.getAbsoluteFile());
// BufferedWriter bw = new BufferedWriter(fw);
// bw.write("Artikel;Antal/st;Pris/st;Total\n");
out.append("Artikel;Antal/st;Pris/st;Total\n");
for (Data d : dataList) {
out.append(d.toString());
out.append("\n");
// bw.write(d.toString());
// bw.write("\n");
}
// bw.close();
out.flush();
out.close();
} catch (UnsupportedEncodingException unsuppEn) {
unsuppEn.printStackTrace();
} catch (IOException ioE) {
ioE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// DESDE AQUI AGREGADO POR GHUCHET
private void completarMovimiento(MMovement move) {
// Preparar el movimiento
move.setDocAction(X_M_Movement.DOCACTION_Prepare);
DocumentEngine.processIt(move, X_M_Movement.DOCACTION_Prepare);
move.save();
commit();
move.load(get_TrxName());
if (move.getDocStatus().equals("IN")) {
ADialog.error(EnvConstants.WINDOW_INFO, new Container(), "NoLines");
} else {
// Aca debo actualizar las cantidades aprobadas
String sql =
"SELECT QtyRequired, XX_ApprovedQty FROM M_MOVEMENTLINE WHERE M_MOVEMENT_ID = "
+ move.getM_Movement_ID();
PreparedStatement ps_1 =
DB.prepareStatement(
sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, null);
ResultSet rs_1 = null;
// System.out.println(sql);
int qty_required = 0;
try {
rs_1 = ps_1.executeQuery();
while (rs_1.next()) {
qty_required = rs_1.getInt("QtyRequired");
rs_1.updateInt("XX_ApprovedQty", qty_required);
rs_1.updateRow();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs_1.close();
ps_1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
move.setXX_Status("PE");
move.setXX_RequestDate(move.getUpdated());
move.save();
// Se envian correos dependiendo del tipo de documento
enviarCorreoATienda(move, Env.getCtx().getContextAsInt("#XX_L_MT_PTRANSFERAPPROVAL_ID"));
}
}
public boolean hasNext() {
if (this.rowNum == 0 || this.currentRowNo >= this.rowNum) {
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
return false;
} else {
// sheet下一行内容为空判定结束
if ((sheet.getRow(currentRowNo))[0].getContents().equals("")) return false;
return true;
}
}
public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) {
String main_tsn = "";
String main_rank = "";
String main_kingdom = "";
row = 0;
try {
statement = new_statement;
func = new AdditionalFunctions(statement);
copy = workbook;
kingdom = arguments[0];
hrank = arguments[1];
lrank = arguments[2];
scientificName = arguments[3];
date_from = arguments[4];
date_to = arguments[5];
user = arguments[6];
String temp = "";
temp =
"SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'";
System.out.println(temp);
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int numberOfRows = 0;
int hrankVal = Integer.parseInt(hrank);
int main_rankVal = 0;
if (result.last()) numberOfRows = result.getRow();
result.first();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
main_tsn = result.getString(1);
main_rank = result.getString(2);
main_rankVal = Integer.parseInt(main_rank);
main_kingdom = result.getString(3);
}
if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func);
FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments);
copy.write();
copy.close();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of constructor
// public method to extract and write the Publication information
public static void WritePublication(String publicationId, int rowNum) {
WritableSheet sheet2 = copy.getSheet(1);
try {
String query =
"SELECT address,author,publicationTitle,chapter,edition,editor,month,day,note,number,publisher,"
+ "series,title,volume,year,isbn,issn,pages FROM Publication WHERE id="
+ publicationId;
System.out.println(query);
ResultSet result_query = statement.executeQuery(query);
ResultSetMetaData metadata_query = result_query.getMetaData();
int numberOfRows = 0;
if (result_query.last()) numberOfRows = result_query.getRow();
result_query.first();
if (numberOfRows > 0 && metadata_query.getColumnCount() > 0) {
String aut_edi = result_query.getString(2);
System.out.println("autor is " + aut_edi);
if (result_query.getString(6).compareTo("") != 0)
aut_edi = aut_edi + " / " + result_query.getString(6) + ",eds.";
Label label = new Label(7, rowNum, aut_edi);
sheet2.addCell(label);
label = new Label(8, rowNum, result_query.getString(13));
sheet2.addCell(label);
String publication_name = result_query.getString(3);
if (result_query.getString(12).compareTo("") != 0)
publication_name = publication_name + ", ser. " + result_query.getString(12);
if (result_query.getString(14).compareTo("0") != 0)
publication_name = publication_name + ", vol. " + result_query.getString(14);
if (result_query.getString(10).compareTo("0") != 0)
publication_name = publication_name + ",no. " + result_query.getString(10);
label = new Label(9, rowNum, publication_name);
sheet2.addCell(label);
label = new Label(12, rowNum, result_query.getString(11));
sheet2.addCell(label);
label = new Label(13, rowNum, result_query.getString(1));
sheet2.addCell(label);
label = new Label(15, rowNum, result_query.getString(9));
sheet2.addCell(label);
label = new Label(16, rowNum, result_query.getString(16));
sheet2.addCell(label);
label = new Label(17, rowNum, result_query.getString(17));
sheet2.addCell(label);
label = new Label(14, rowNum, result_query.getString(18));
sheet2.addCell(label);
String date = result_query.getString(15);
String month = "Jan";
System.out.println("month is " + result_query.getString(7));
if (result_query.getString(7).compareTo("") != 0) month = result_query.getString(7);
month = func.ConvertMonth(month);
date = month + "/" + date;
String day = "01";
if (result_query.getString(8).compareTo("0") != 0) day = result_query.getString(8);
if (day.length() == 1) day = "0" + day;
date = day + "/" + date;
label = new Label(10, rowNum, date);
sheet2.addCell(label);
label = new Label(11, rowNum, date);
sheet2.addCell(label);
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of WritePublication
// public method to find child information
public void FindChildInformation(String tsn, String[] arguments, AdditionalFunctions func) {
WritableSheet sheet2 = copy.getSheet(1);
Label label = null;
String children[] = null;
String temp = "", current_tsn = "", dateCreated = "";
int dateCheck = 1;
temp = "SELECT distinct tsn from Tree where parent_tsn='" + tsn + "'";
try {
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int numberOfRows = 0;
if (result.last()) numberOfRows = result.getRow();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
result.first();
children = new String[numberOfRows];
for (int j = 0; j < numberOfRows; j++) {
children[j] = result.getString(1);
result.next();
}
}
for (int j = 0; j < numberOfRows; j++) {
int current_tsnId = Integer.parseInt(children[j]);
if (current_tsnId >= 999000000) {
temp =
"SELECT distinct Tree.tsn,unit_name1,unit_name2,unit_name3,unit_name4,scientificName,`usage`,"
+ "taxon_author,parent_tsn,rank_name, pages, publicationId, userId, dateCreated, nameType FROM Tree,"
+ " BaseObject, TaxonConcepts, TaxonAuthors,TaxonUnitTypes"
+ " WHERE Tree.tsn=TaxonConcepts.tsn AND Tree.tsn='"
+ current_tsnId
+ "' AND "
+ "BaseObject.id=TaxonConcepts.id AND TaxonAuthors.taxon_author_id=Tree.taxon_author_id AND "
+ "Tree.rank_id=TaxonUnitTypes.rank_id";
System.out.println(temp);
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int RowNumber = 0;
if (result.last()) RowNumber = result.getRow();
if (RowNumber > 0 && metadata.getColumnCount() > 0) {
current_tsn = result.getString(1);
dateCreated = result.getString(14);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date d1 = df.parse(dateCreated);
Date d2 = null;
if (date_from.compareTo("all") != 0) {
d2 = df.parse(date_from);
if (d2.before(d1) || d2.equals(d1)) dateCheck = 1;
else dateCheck = 0;
}
if (date_to.compareTo("today") != 0) {
d2 = df.parse(date_to);
if (d1.before(d2) || d1.equals(d2)) dateCheck = 1;
else dateCheck = 0;
}
if (result.getString(7).compareTo("public") == 0
&& result.getString(15).compareTo("Regular scientific name") == 0
&& result.getString(12).compareTo("") != 0
&& dateCheck == 1) {
String scientificName = result.getString(6);
String taxon_author = result.getString(8);
String publicationId = result.getString(12);
for (int k = 0; k < 4; k++) {
if (result.getString(k + 2).compareTo("null") != 0) {
label = new Label(k, row + 1, result.getString(k + 2));
sheet2.addCell(label);
}
}
label = new Label(4, row + 1, taxon_author);
sheet2.addCell(label);
label = new Label(5, row + 1, result.getString(10));
sheet2.addCell(label);
String parent_tsn = result.getString(9);
String userId = result.getString(13);
WritePublication(publicationId, row + 1);
label = new Label(18, row + 1, func.GetSubmitter(userId));
sheet2.addCell(label);
label = new Label(6, row + 1, func.GetParent(parent_tsn));
sheet2.addCell(label);
row = row + 1;
}
}
}
continue;
}
for (int j = 0; j < numberOfRows; j++) {
if (HasChildren(children[j]) == true) FindChildInformation(children[j], arguments, func);
else continue;
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of FindChildInformation
/** Envia correos al personal de tienda en caso de la aprobación de un traspaso */
private void enviarCorreoATienda(MMovement movimiento, int plantillaDeCorreo) {
MWarehouse almaSalida = new MWarehouse(getCtx(), movimiento.getM_WarehouseFrom_ID(), null);
MWarehouse almaLleada = new MWarehouse(getCtx(), movimiento.getM_WarehouseTo_ID(), null);
X_XX_VMR_Department departamento =
new X_XX_VMR_Department(getCtx(), movimiento.getXX_VMR_Department_ID(), null);
// Mensaje debe indicar departamento, origen, destino y traspaso
String mensaje =
Msg.getMsg(
getCtx(),
"XX_PTransferApproval",
new String[] {
movimiento.getDocumentNo(),
departamento.getValue() + "-" + departamento.getName(),
almaSalida.getValue() + "-" + almaSalida.getName(),
almaLleada.getValue() + "-" + almaLleada.getName(),
movimiento.getXX_StatusName()
});
// Al Gerente de Tienda
// Selecciono el o los gerentes de Tienda
String SQL =
"SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' "
+ " AND C_BPARTNER_ID IN "
+ "("
+ "SELECT C_BPARTNER_ID "
+ "FROM C_BPARTNER WHERE isActive='Y' "
+ "AND C_JOB_ID ="
+ Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_STOREMAN_ID")
+ " "
+ "AND (M_WAREHOUSE_ID = "
+ almaLleada.get_ID()
+ " "
+ "OR M_WAREHOUSE_ID = "
+ almaSalida.get_ID()
+ ") "
+ "AND AD_Client_ID IN (0,"
+ Env.getCtx().getAD_Client_ID()
+ ")"
+ ") "
+ "AND AD_Client_ID IN (0,"
+ Env.getCtx().getAD_Client_ID()
+ ")";
Vector<Integer> storeManagers = new Vector<Integer>();
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
storeManagers.add(rs.getInt("AD_USER_ID"));
}
rs.close();
pstmt.close();
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
}
// Envio correos a los gerentes
Utilities f = null;
for (int i = 0; i < storeManagers.size(); i++) {
f =
new Utilities(
Env.getCtx(),
null,
plantillaDeCorreo,
mensaje,
-1,
Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"),
-1,
storeManagers.get(i),
null);
try {
f.ejecutarMail();
} catch (Exception e) {
e.printStackTrace();
}
f = null;
}
// *********************************
// Selecciono los asesores de almacen
SQL =
"SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' "
+ "AND C_BPARTNER_ID IN "
+ "("
+ "SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE isActive='Y' "
+ "AND C_JOB_ID ="
+ Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_DEPASE_ID")
+ " "
+ "AND (M_WAREHOUSE_ID = "
+ almaLleada.get_ID()
+ " "
+ "OR M_WAREHOUSE_ID = "
+ almaSalida.get_ID()
+ ") "
+ "AND AD_Client_ID IN (0,"
+ Env.getCtx().getAD_Client_ID()
+ ")"
+ ") "
+ "AND AD_Client_ID IN (0,"
+ Env.getCtx().getAD_Client_ID()
+ ")";
Vector<Integer> warehouseAsessors = new Vector<Integer>();
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
warehouseAsessors.add(rs.getInt("AD_USER_ID"));
}
rs.close();
pstmt.close();
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
}
// Envio correos a los asesores
Utilities m = null;
for (int i = 0; i < warehouseAsessors.size(); i++) {
m =
new Utilities(
Env.getCtx(),
null,
plantillaDeCorreo,
mensaje,
-1,
Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"),
-1,
warehouseAsessors.get(i),
null);
try {
m.ejecutarMail();
} catch (Exception e) {
e.printStackTrace();
}
m = null;
}
// *********************************
// Selecciono los Gerentes de Area ADMIN y MERCA, Asesor de Inventario
SQL =
"SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' "
+ "AND C_BPARTNER_ID IN "
+ "("
+ "SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE isActive='Y' "
+ "AND C_JOB_ID IN ("
+ " "
+ Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_ADMINMANAG_ID")
+ ","
+ " "
+ Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_GAMERC_ID")
+ ","
+ " "
+ Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_INVASSES_ID")
+ ") "
+ "AND (M_WAREHOUSE_ID = "
+ almaLleada.get_ID()
+ " "
+ "OR M_WAREHOUSE_ID = "
+ almaSalida.get_ID()
+ ") "
+ "AND AD_Client_ID IN (0,"
+ Env.getCtx().getAD_Client_ID()
+ ")"
+ ") "
+ "AND AD_Client_ID IN (0,"
+ Env.getCtx().getAD_Client_ID()
+ ")";
Vector<Integer> managerArea = new Vector<Integer>();
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
managerArea.add(rs.getInt("AD_USER_ID"));
}
rs.close();
pstmt.close();
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
}
// Envio correos a gerentes de area
Utilities u = null;
for (int i = 0; i < managerArea.size(); i++) {
u =
new Utilities(
Env.getCtx(),
null,
plantillaDeCorreo,
mensaje,
-1,
Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"),
-1,
managerArea.get(i),
null);
try {
u.ejecutarMail();
} catch (Exception e) {
e.printStackTrace();
}
u = null;
}
}