package com.smartone.bmequip;
import com.entree.utility.DB;
import com.entree.utility.common;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Date;
import java.util.Iterator;
import java.util.Properties;
import javax.activation.DataSource;
import javax.mail.BodyPart;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.naming.NamingException;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.text.SimpleDateFormat;
import java.util.*;
public class bm_overdue_alert_rpt {
private static String logFilename = "";
private static String logDirectory = "";
static public void sendEmail(common ocm, String to, String cc, String body) throws Exception {
try{
if (to.length()>2) {
if (ocm.Application("SmtpServer").length() > 0 && to.length() > 0) {
String[] para = new String[1];
para[0] = body;
int x= ocm.sendMessage("bmOverdueCaseRpt", ocm.Application("SystemEmailAddress"), to, cc, "", para, null, "HTML");
}
}
} catch(Exception e) {
if (e.getMessage()!=null) {
String errMsg = new String (e.getMessage());
System.out.println("bm_overdue_alert_rpt-sendEmail: Exception " + errMsg);
} else
System.out.println("bm_overdue_alert_rpt-sendEmail: Exception ");
}
}
private void genReport() throws NamingException,
ClassNotFoundException,
SQLException, Exception {
String[] email = null, requesterstaffid = null, description=null,
transferdi=null, com_hold_amt=null, rmk=null, nature=null,
status=null, refno=null, reason=null, cus_borrower=null, exp_end=null;
String str="", sql="", accessory="", device="", sim="", long_description="", hld_cal_num="", itemno="";
String cc="";
int cyc=0, len=0, cust=0, nocust=0, sendflag=0;;
common ocm = new common("", "");
DB db = new DB(ocm.Application("driverStandalone"),
ocm.Application("connectionStandalone"), "true");
// int cntConnie=0;
try {
ArrayList email_array = new ArrayList();
ArrayList staffid_array = new ArrayList();
/*
str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),5,'A')=TRUNC(d.ACT_END) "+//CONNIE
" and b.TID=c.TID and c.TDID=d.TDID ";
sql = "select count(*) as cnt from HR_STAFF a, BM_LOAN_HEADER b, BM_LOAN_DTL c, BM_LOAN_ITEM d where b.REQUESTERSTAFFID=a.STAFF_ID and "+str;
db.Execute(sql);
while (db.next()) {
cntConnie = Integer.parseInt(db.rs("cnt"));
}
email = new String[cntConnie];
requesterstaffid = new String[cntConnie];
*/
// str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),4,'A')=TRUNC(b.EXP_END) "+//CONNIE
str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),5,'A')=TRUNC(d.ACT_END) "+//CONNIE
" and b.TID=c.TID and c.TDID=d.TDID ";
sql = "select distinct a.EMAIL, b.REQUESTERSTAFFID from HR_STAFF a, BM_LOAN_HEADER b, BM_LOAN_DTL c, BM_LOAN_ITEM d where b.REQUESTERSTAFFID=a.STAFF_ID and "+str;
db.Execute(sql);
while (db.next()) {
email_array.add(db.rs("EMAIL").trim());
staffid_array.add(db.rs("REQUESTERSTAFFID").trim());
/* email[len] = db.rs("EMAIL").trim();
requesterstaffid[len] = db.rs("REQUESTERSTAFFID").trim(); */
++len;
}
// for (int i=0; i<len; i++) {
for (int i=0; i<email_array.size(); i++) {
///
// print header
///
StringBuffer table = new StringBuffer("<html><head><style><!-- .hdr { text-align: left; background: #ffffff; font-size: 10pt; font-weight: bold;color: #000000; margin: 100px 20px 20px 20px;} .dtl { text-align: left; background: #ffffff; font-size: 10pt; color: #000000; margin: 100px 20px 20px 20px;} .title { text-align: left; background: #ffffff; font-size: 10pt; font-weight: bold; color: #000000; margin: 100px 20px 20px 20px;}--></style></head><body >");
// StringBuffer table = new StringBuffer("<TABLE border=/"0/" bgcolor='#CCCCCC' width=/"100%/" cellpadding=/"1/" cellspacing=/"1/"><FONT FACE=/"Arial/" SIZE=10pt>");
table = table.append("<TABLE border=1 bgcolor='#cccccc' width=100% cellpadding=0 cellspacing=0 vspace=1 hspace=1><FONT FACE=/"Arial/" SIZE=10pt>");
table = table.append("<TR class=hdr><TD colspan=2>For Trading Stock</TD></TR>");
table = table.append("<TR class=dtl><TD colspan=2>1) Please return stock, submit signed SA or Loss Compensation Form (with compensation amount) to Salse Admin on or before Approval Return Date. <BR>");
table = table.append("If you are unable to do so, please submit an extension request via On Loan System before Approval Return Date. <BR>");
table = table.append("Otherwise, commission will be held in the coming month.<BR><BR>");
table = table.append("2) Once commission is held , it will be released only if you have returned the stock, submitted signed Sa or Loss Compensation Form (with compensation <BR>");
table = table.append("amount) to Sales Admin </TD></TR><BR>");
table = table.append("<TR class=hdr><TD colspan=2>For Customer Trial Stock</TD></TR>");
table = table.append("<TR class=dtl><TD colspan=/"2/">1) Please return stock, submit Loss Compensation Form (with compensation amount) to Sales Admin on or before Approval Return Date. If you are unable to do <BR>");
table = table.append("so, please submit an extension request via On Loan System before Approval Return Date. $300 Commission will be held per order(per reference no.) <BR>");//connie
table = table.append("in the coming month.</TD></TR>");
table = table.append("<TR CLASS=title><TD colspan=2> </TD></TR>");
ArrayList unique_nature_array = new ArrayList();
///
// By Nature
///
str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),5,'A')=TRUNC(b.ACT_END) "+// connie
" and a.TID=e.TID and e.TDID=b.TDID ";
sql = "select distinct c.DESCRIPTION as DESCRIPTION "+ // connie
"from BM_LOAN_HEADER a, BM_LOAN_ITEM b, BM_CODE c, FES_PRODUCT_CODE d, BM_LOAN_DTL e where a.NATURE=c.CD and c.CAT='NATURE' and "+ // CONNIE
"e.ETYPE=d.WAREHOUSE AND e.PCODE=d.PRODUCT and '"+
staffid_array.get(i)+"'=a.REQUESTERSTAFFID and "+str+" ORDER BY c.DESCRIPTION ";
db.Execute(sql);// connie
while (db.next()) {
unique_nature_array.add(db.rs("DESCRIPTION").trim());
}
for (int z=0; z<unique_nature_array.size(); z++) {
ArrayList refno_array = new ArrayList();
ArrayList reason_array = new ArrayList();
ArrayList cus_borrower_array = new ArrayList();
ArrayList exp_end_array = new ArrayList();
ArrayList com_hold_amt_array = new ArrayList();
ArrayList rmk_array = new ArrayList();
ArrayList status_array = new ArrayList();
ArrayList description_array = new ArrayList();
ArrayList itemno_array = new ArrayList();
ArrayList longdescription_array = new ArrayList();
ArrayList hld_cal_num_array = new ArrayList();
ArrayList nature_array = new ArrayList();
///
// Get All Details
///
// str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),4,'A')=TRUNC(a.EXP_END) "+// connie
str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),5,'A')=TRUNC(b.ACT_END) "+// connie
" and a.TID=e.TID and e.TDID=b.TDID ";
// sql = "select a.REFNO, a.REASON, a.CUS_BORROWER, a.EXP_END, a.NATURE, "+// connie
sql = "select a.REFNO, a.REASON, a.CUS_BORROWER, to_char(b.ACT_END,'dd-Mon-yyyy') as ACT_END, a.NATURE, "+// connie
"b.ITEMNO, b.COM_HOLD_AMT, b.RMK,nvl( ( SELECT DESCRIPTION FROM BM_CODE f where f.cd=B.prd_status AND f.cat = 'ITEM_CON' ) ,'') as STATUS, c.DESCRIPTION, d.LONG_DESCRIPTION, "+
"(hr_HOLIDAY_CAL_NUM(TRUNC(b.TRANSFERDt), TRUNC(SYSDATE),'A')) as HLD_CAL_NUM "+ // connie
"from BM_LOAN_HEADER a, BM_LOAN_ITEM b, BM_CODE c, FES_PRODUCT_CODE d, BM_LOAN_DTL e where a.NATURE=c.CD and c.CAT='NATURE' and "+ // CONNIE
"e.ETYPE=d.WAREHOUSE AND e.PCODE=d.PRODUCT and '"+
// requesterstaffid[i]+"'=a.REQUESTERSTAFFID and "+str+" ORDER BY c.DESCRIPTION ";
staffid_array.get(i)+"'=a.REQUESTERSTAFFID and "+str+" and c.DESCRIPTION = '"+unique_nature_array.get(z)+"' ORDER BY c.DESCRIPTION ";
db.Execute(sql);// connie
while (db.next()) {
refno_array.add(db.rs("REFNO").trim());
reason_array.add(db.rs("REASON").trim());
cus_borrower_array.add(db.rs("CUS_BORROWER").trim());
exp_end_array.add(db.rs("ACT_END").trim());
com_hold_amt_array.add(db.rs("COM_HOLD_AMT").trim());
rmk_array.add(db.rs("RMK").trim());
status_array.add(db.rs("STATUS").trim());
description_array.add(db.rs("DESCRIPTION").trim());
itemno_array.add(db.rs("ITEMNO").trim());
longdescription_array.add(db.rs("LONG_DESCRIPTION").trim());
hld_cal_num_array.add(db.rs("HLD_CAL_NUM").trim());
nature_array.add(db.rs("NATURE").trim());
if (cyc==0) {
if ("V".equals(nature_array.get(0))) {
// cc="salesadmin@smartone-vodafone.com";
// cc="andy_law@smartone-vodafone.com"; //CONNIE
cc="connie_ho@smartone-vodafone.com"; //CONNIE
}
}
/*
refno[cyc] = db.rs("REFNO").trim();
reason[cyc] = db.rs("REASON").trim();
cus_borrower[cyc] = db.rs("CUS_BORROWER").trim();
// exp_end[cyc] = db.rs("EXP_END").trim(); // CONNIE
exp_end[cyc] = db.rs("ACT_END").trim(); // CONNIE
com_hold_amt[cyc] = db.rs("COM_HOLD_AMT").trim();
rmk[cyc] = db.rs("RMK").trim();
status[cyc] = db.rs("STATUS").trim();
description[cyc] = db.rs("DESCRIPTION").trim();
itemno = db.rs("ITEMNO").trim();
long_description = db.rs("LONG_DESCRIPTION").trim();
hld_cal_num = db.rs("HLD_CAL_NUM").trim();
nature[cyc] = db.rs("NATURE").trim();
System.out.println("tes4 1loop"); // test
if (cyc==0) {
if ("V".equals(nature[0])) {
System.out.println("testy"); // test
// cc="salesadmin@smartone-vodafone.com";
// cc="andy_law@smartone-vodafone.com"; //CONNIE
cc="connie_ho@smartone-vodafone.com"; //CONNIE
}
}*/ // connei
++cyc;
} // while detail
// Get Count
// str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),4,'A')=TRUNC(b.EXP_END) "+//CONNIE
str = " HR_HOLIDAY_CAL_DATE(TRUNC(SYSDATE),5,'A')=TRUNC(d.ACT_END) "+//CONNIE
" and b.TID=a.TID and a.TDID=d.TDID ";
String desc = (String) unique_nature_array.get(z);
if ("CUSTOMER TRIAL".equals(desc.toUpperCase()) && cust==0) {
sql = "select COUNT(*) as ACCESSORY from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c , BM_LOAN_ITEM d where a.ETYPE = 'AA' and '"+ // connie
staffid_array.get(i)+"'=b.REQUESTERSTAFFID and "+str+
" and c.CD=b.NATURE and c.CAT='NATURE' and c.DESCRIPTION='"+desc+"' "; // CONNIE
// " and '"+nature[j]+"'=b.NATURE and c.CAT='Nature' and c.DESCRIPTION='"+description[j]+"' "; // CONNIE
db.Execute(sql);
if (db.next())
accessory = db.rs("ACCESSORY").trim();
sql = "select COUNT(*) as DEVICE from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AH' and '"+ // connie
staffid_array.get(i)+"'=b.REQUESTERSTAFFID and "+str+
" and c.CD=b.NATURE and c.CAT='NATURE' and c.DESCRIPTION='"+desc+"' ";
db.Execute(sql);
if (db.next())
device = db.rs("DEVICE").trim();
sql = "select COUNT(*) as SIM from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AP' and '"+ // connie
staffid_array.get(i)+"'=b.REQUESTERSTAFFID and "+str+
" and c.CD=b.NATURE and c.CAT='NATURE' and c.DESCRIPTION='"+desc+"' ";
db.Execute(sql);
if (db.next())
sim = db.rs("SIM").trim();
table = table.append("<TR CLASS=title colspan=2><TH>Total no. of Customer Trial Stock to be overdue:</TH></TR>");
table = table.append("<TR><TD class=hdr>Accessory:</TD><TD class=dtl>"+accessory+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Device:</TD><TD class=dtl>"+device+"</TD></TR>");
table = table.append("<TR><TD class=hdr>SIM:</TD><TD class=dtl>"+sim+"</TD></TR>");
table = table.append("<TR><TD class=dtl colspan=2><a href=/"http://in02/ieformdev/login.html/">Link</a></TD></TR>");
table = table.append("<TR><TD class=dtl> </TD></TR>");
cust=1;
} else if (!"CUSTOMER TRIAL".equals(desc.toUpperCase()) && nocust==0) {
/* sql = "select SUM(a.QTY) as ACCESSORY from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AA' and '"+ // connie*/
sql = "select COUNT(*) as ACCESSORY from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AA' and '"+ // connie
staffid_array.get(i)+"'=b.REQUESTERSTAFFID and "+str+
" and c.CD=b.NATURE and c.CAT='NATURE' and c.DESCRIPTION='"+desc+"' ";
db.Execute(sql);
if (db.next())
accessory = db.rs("ACCESSORY").trim();
/* sql = "select SUM(a.QTY) as ACCESSORY from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AH' and '"+ // connie*/
sql = "select COUNT(*) as DEVICE from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AH' and '"+ // connie
staffid_array.get(i)+"'=b.REQUESTERSTAFFID and "+str+
" and c.CD=b.NATURE and c.CAT='NATURE' and c.DESCRIPTION='"+desc+"' ";
db.Execute(sql);
if (db.next())
device = db.rs("DEVICE").trim();
/* sql = "select SUM(a.QTY) as ACCESSORY from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AP' and '"+ // connie*/
sql = "select COUNT(*) as SIM from BM_LOAN_DTL a, BM_LOAN_HEADER b, BM_CODE c, BM_LOAN_ITEM d where a.ETYPE = 'AP' and '"+ // connie
staffid_array.get(i)+"'=b.REQUESTERSTAFFID and "+str+
" and c.CD=b.NATURE and c.CAT='NATURE' and c.DESCRIPTION='"+desc+"' ";
db.Execute(sql);
if (db.next())
sim = db.rs("SIM").trim();
table = table.append("<TR CLASS=title colspan=2><TH>Total no. of Trading Stock to be overdue:</TH></TR>");
table = table.append("<TR><TD class=hdr>Accessory:</TD><TD class=dtl>"+accessory+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Device:</TD><TD class=dtl>"+device+"</TD></TR>");
table = table.append("<TR><TD class=hdr>SIM:</TD><TD class=dtl>"+sim+"</TD></TR>");
table = table.append("<TR><TD colspan=2><a href=/"http://in02/ieformdev/login.html/">Link</a></TD></TR>");
table = table.append("<TR><TD colspan=2> </TD></TR>");
nocust=1;
}
for (int j=0; j<refno_array.size(); j++) {
table = table.append("<TR><TD class=hdr>Reference No.</TD><TD class=dtl>"+refno_array.get(j)+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Loan Nature</TD><TD class=dtl>"+description_array.get(j)+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Product Description</TD><TD class=dtl>"+("".equals(longdescription_array.get(j))?" ":longdescription_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>IMEI/SIM/Serial No.</TD><TD class=dtl>"+("".equals(itemno_array.get(j))?" ":itemno_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Commission On Hold Amount</TD><TD class=dtl>"+("".equals(com_hold_amt_array.get(j))?" ":com_hold_amt_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>On Loan Reason</TD><TD class=dtl>"+("".equals(reason_array.get(j))?" ":reason_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Company Name</TD><TD class=dtl>"+("".equals(cus_borrower_array.get(j))?" ":cus_borrower_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Remarks</TD><TD class=dtl>"+("".equals(rmk_array.get(j))?" ":rmk_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Approval Return Date</TD><TD class=dtl>"+("".equals(exp_end_array.get(j))?" ":exp_end_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>Latest Status</TD><TD class=dtl>"+("".equals(status_array.get(j))?" ":status_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD class=hdr>No. of working days on hand</TD><TD class=dtl>"+("".equals(hld_cal_num_array.get(j))?" ":hld_cal_num_array.get(j))+"</TD></TR>");
table = table.append("<TR><TD colspan=2> </TD></TR>");
sendflag=1;
}
} // for distinct nature
if (sendflag==1) {
table = table.append("</FONT></TABLE></body></html>");
// sendEmail(ocm, email[i], cc, table.toString());
sendEmail(ocm, (String) email_array.get(i), cc, table.toString());
}
sendflag=0;
long_description="";
hld_cal_num="";
itemno="";
cyc=0;
}// for distinct staff
} catch (Exception e) {
System.out.println("genReport: Exception " + e.getMessage());
System.exit(-1);
} finally {
db.close();
}
}
public static void main(String[] args) throws NamingException,
ClassNotFoundException,
SQLException, Exception {
bm_overdue_alert_rpt oar = new bm_overdue_alert_rpt();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
Date dt = new Date();
try {
// logFilename = args[1] + "bpload.log." + df.format(dt);
logFilename = args[1] + logDirectory + args[2] +".log";
}
catch (Exception e) {
e.printStackTrace();
}
oar.genReport();
}
private static void writeLog(String content) {
try {
SimpleDateFormat df =
new SimpleDateFormat("dd/MM/yyyy HH:mm:ss:SSS");
Date dts = new Date();
BufferedWriter out =
new BufferedWriter(new FileWriter(logFilename, true));
out.write(df.format(dts) + " " + content);
out.newLine();
out.close();
}
catch (IOException e) {
System.err.println("Unable to write to file");
System.exit(-1);
}
}
}
481
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
