数据苦查询数据并将记录条数用excel文档导出——poi
来源:互联网 发布:胸肌 上部 知乎 编辑:程序博客网 时间:2024/05/16 08:03
package com.longrise.LEAP.FISP.BLL.DealData;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.longrise.LEAP.BLL.LEAPBLLBaseService;
import com.longrise.LEAP.Base.Global;
import com.longrise.LEAP.Base.DAL.Transaction;
import com.longrise.LEAP.Base.Objects.EntitySet;
import com.longrise.LEAP.Base.Objects.ResultSet;
import com.longrise.LEAP.FISP.BLL.BaseDataManager.BaseData;
import com.longrise.LEAP.FISP.BLL.BaseDataManager.CalculationInfo;
import com.longrise.LEAP.FISP.BLL.InsManager.InsGroupManager;
import com.longrise.LEAP.FISP.BO.carinsbasecaridnozone;
import com.longrise.LEAP.FISP.BO.carinsbaseinsurancecode;
import com.longrise.LEAP.FISP.BO.carinsbaseinsurancecodesubclass;
import com.longrise.LEAP.FISP.BO.carinscarcollection;
import com.longrise.LEAP.FISP.BO.carinsdatacarinfo;
import com.longrise.LEAP.FISP.BO.carinsdatacarperson;
import com.longrise.LEAP.FISP.BO.carinsdatadetailmodel;
import com.longrise.LEAP.FISP.BO.carinsdatainsurancepolicy;
import com.longrise.LEAP.FISP.BO.carinsdatapolicyinsurancecode;
import com.longrise.LEAP.FISP.BO.carinsmessagesample;
import com.longrise.LEAP.FISP.BO.carinsmessagetask;
import com.longrise.LEAP.FISP.BO.carparameters;
import com.longrise.LEAP.FISP.BO.company;
import com.longrise.LEAP.FISP.BO.selectinscode;
import com.longrise.LEAP.FISP.BO.viewcarinsinsinfo;
import fisp.webservice.BBWebserviceClass;
public class DealData extends LEAPBLLBaseService {
public boolean SaveCarInfo11(carinsdatacarperson person, selectinscode _sc,
carparameters _sp, carinsdatacarinfo carinfo_) {
Transaction trans = null;
try {
trans = Global.getInstance().getDataSource("FISP")
.createTransaction();
if (carinfo_ != null) {
Global.getInstance().getDataSource("FISP").Insert(carinfo_,
trans);
}
if (person != null) {
Global.getInstance().getDataSource("FISP")
.Insert(person, trans);
}
trans.commit();
carinsdatainsurancepolicy _insp = new carinsdatainsurancepolicy();
if (_sc != null) {
_insp.setpolicycarpersonid(person.getid());
_insp.setcarinfoid(carinfo_.getid());
String groupinsid = InsGroupManager.getInsureGroupid(_sp, _sc);
_insp.setinsgrouptype(groupinsid);
_insp.setagentserviceid("");// 代理人
_insp.setamountpaid(BigDecimal.valueOf(_sc.getpayall()));
_insp.setcarimage("");// 復印件
_insp.setcommission(BigDecimal.valueOf(_sc.getdisall()
- _sc.getpayall()));
_insp.setdiscountpremium(BigDecimal.valueOf(_sc.getdisall()));
_insp.setpremium(BigDecimal.valueOf(0));
_insp.setsalemanid("");// 业务员
_insp.setzoneno(BigDecimal.valueOf(0));// 業務員區域
_insp.setorgno("");// 保險人
_insp.setstateid(0);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = df.parse(_sp.getcommercebegin());
Calendar C = Calendar.getInstance();
C.setTime(date);
C.add(C.YEAR, 1);
C.add(C.DATE, -1);
_insp.seteffectivedatebegin(date);// 生效開始時間
_insp.seteffectivedateend(C.getTime());// 保單到期時間
_insp
.setsingleinsuranceseriesno(getDateTimeString("yyyyMMddHHmmssSS"));
Global.getInstance().getDataSource("FISP").Insert(_insp, trans);
for (int i = 0; i < _sc.getinsurcode().length; i++) {
if (_sc.getinsurcode()[i].getpremium() > 0) {
carinsdatapolicyinsurancecode _carcode = new carinsdatapolicyinsurancecode();
if (_sc.getinsurcode()[i].getsuminsured() == null) {
_carcode.setamount(BigDecimal.valueOf(0));
} else {
_carcode.setamount(BigDecimal.valueOf(_sc
.getinsurcode()[i].getsuminsured()));
}
_carcode.setdiscountpremium(BigDecimal.valueOf(_sc
.getinsurcode()[i].getpremium().doubleValue()
* _sc.getinsurcode()[i].getdiscountrate()));
_carcode.setpremium(BigDecimal.valueOf(_sc
.getinsurcode()[i].getpremium().doubleValue()));
_carcode.setremark(_sc.getinsurcode()[i]
.getdiscountrate().toString());
_carcode.setinsurancecodename(_sc.getinsurcode()[i]
.getgeneraname());
_carcode.setinsurancecodeid(_sc.getinsurcode()[i]
.getinsurancecodeid());
_carcode.setsubclassid(_sc.getinsurcode()[i]
.getsubclassid());
_carcode.setinsurancepolicyid(_insp.getid());
Global.getInstance().getDataSource("FISP").Insert(
_carcode, trans);
}
}
}
trans.commit();
return true;
} catch (Exception e) {
Global.getInstance().LogError(this, e);
trans.rollback();
return false;
}
}
public carinscarcollection SaveCarInfo(carinscarcollection carCollection,
selectinscode _sc, carparameters _sp) {
if (carCollection == null)
return null;
try {
if (carCollection.getCarInfo() != null) {
if (carCollection.getCarInfo().getid() != null) {
Global.getInstance().getDataSource("FISP").Update(
carCollection.getCarInfo());
} else {
Global.getInstance().getDataSource("FISP").Insert(
carCollection.getCarInfo());
carCollection.setCarInfo(carCollection.getCarInfo());
}
}
if (carCollection.getCarPerson() != null) {
if (carCollection.getCarPerson().getid() != null) {
Global.getInstance().getDataSource("FISP").Update(
carCollection.getCarPerson());
} else {
Global.getInstance().getDataSource("FISP").Insert(
carCollection.getCarPerson());
carCollection.setCarPerson(carCollection.getCarPerson());
}
}
if (carCollection.getInsPolicy() != null) {
carCollection.getInsPolicy().setpolicycarpersonid(
carCollection.getCarPerson().getid());
carCollection.getInsPolicy().setcarinfoid(
carCollection.getCarInfo().getid());
String groupinsid = InsGroupManager.getInsureGroupid(_sp, _sc);
carCollection.getInsPolicy().setinsgrouptype(groupinsid);
carCollection.getInsPolicy().setamountpaid(
BigDecimal.valueOf(_sc.getpayall()));
carCollection.getInsPolicy().setcommission(
BigDecimal.valueOf(_sc.getdisall() - _sc.getpayall()));
carCollection.getInsPolicy().setdiscountpremium(
BigDecimal.valueOf(_sc.getdisall()));
carCollection.getInsPolicy().setpremium(BigDecimal.valueOf(0));
carCollection.getInsPolicy().setinsuredate(new Date());
// carCollection.getInsPolicy().setagentserviceid("");//代理人
// carCollection.getInsPolicy().setcarimage(carCollection.getInsPolicy().getcarimage());//復印件
// BigDecimal
// l=carCollection.getInsPolicy().getdiscountpremium()/0.8;
// carCollection.getInsPolicy().setpremium(BigDecimal.valueOf(0));
// carCollection.getInsPolicy().setsalemanid("");//业务员
// carCollection.getInsPolicy().setzoneno(BigDecimal.valueOf(0));//業務員區域
// carCollection.getInsPolicy().setorgno("");//保險人
// carCollection.getInsPolicy().setstateid(0);
// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// Date date =
// df.parse(carCollection.getInsPolicy().geteffectivedatebegin());
Calendar C = Calendar.getInstance();
C.setTime(carCollection.getInsPolicy().geteffectivedatebegin());
C.add(C.YEAR, 1);
C.add(C.DATE, -1);
// carCollection.getInsPolicy().seteffectivedatebegin(date);//生效開始時間
carCollection.getInsPolicy().seteffectivedateend(C.getTime());// 保單到期時間
String noice = " ";
if (_sc.getmessagenotic() != null) {
if (_sc.getmessagenotic().length != 0) {
for (int i = 0; i < _sc.getmessagenotic().length; i++) {
noice += _sc.getmessagenotic()[i];
}
}
}
carCollection.getInsPolicy().setremark(noice);
if (carCollection.getInsPolicy().getid() != null) {
Global.getInstance().getDataSource("FISP").Update(
carCollection.getInsPolicy());
} else {
carCollection.getInsPolicy().setsingleinsuranceseriesno(
getDateTimeString("yyyyMMddHHmmssSS"));
Global.getInstance().getDataSource("FISP").Insert(
carCollection.getInsPolicy());
carCollection.setInsPolicy(carCollection.getInsPolicy());
}
}
carinsdatapolicyinsurancecode carcode[] = null;
carinsdatapolicyinsurancecode _carcode = null;
if (carCollection.getInsCode().length != 0) {
// carcode = new
// carinsdatapolicyinsurancecode[carCollection.getInsCode().length];
if (carCollection.getInsPolicy() != null) {
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"delete carinsdatapolicyinsurancecode where insurancepolicyid = '"
+ carCollection.getInsPolicy().getid()
+ "'");
}
// for( int k = 0 ; k < carCollection.getInsCode().length ; k ++
// ){
// if(carCollection.getInsCode()[k]!=null){
// if(carCollection.getInsCode()[k].getid()!=null){
// carCollection.getInsCode()[k].setdiscountpremium(BigDecimal.valueOf(carCollection.getInsCode()[k].getdiscountpremium().doubleValue()));
// carCollection.getInsCode()[k].setpremium(BigDecimal.valueOf(carCollection.getInsCode()[k].getpremium().doubleValue()));
// _carcode=carCollection.getInsCode()[k];
// Global.getInstance().getDataSource("FISP").Insert(_carcode);
// carcode[k]=_carcode;
// }
// }
// }
}
carcode = new carinsdatapolicyinsurancecode[_sc.getinsurcode().length];
for (int i = 0; i < _sc.getinsurcode().length; i++) {
if (!_sc.getinsurcode()[i].getcreator().equals("")) {
_carcode = new carinsdatapolicyinsurancecode();
if (_sc.getinsurcode()[i].getsuminsured() == null) {
_carcode.setamount(BigDecimal.valueOf(0));
} else {
_carcode.setamount(BigDecimal.valueOf(_sc
.getinsurcode()[i].getsuminsured()));
}
_carcode.setpremium(BigDecimal
.valueOf(_sc.getinsurcode()[i].getpremium()));
if (_sc.getinsurcode()[i].getgeneracode().equals("C901")) {
_carcode.setdiscountpremium(BigDecimal.valueOf(_sc
.getconstraintpay()));
} else if (_sc.getinsurcode()[i].getgeneracode().equals(
"C902")) {
_carcode.setdiscountpremium(BigDecimal.valueOf(_sc
.getcartax()));
} else {
_carcode.setdiscountpremium(BigDecimal.valueOf(_sc
.getinsurcode()[i].getpremium().doubleValue()
* _sc.getinsurcode()[i].getdiscountrate()));
}
_carcode.setremark(_sc.getinsurcode()[i].getdiscountrate()
.toString());
_carcode.setinsurancecodename(_sc.getinsurcode()[i]
.getsubclass()[0].getname());
_carcode.setinsurancecodeid(_sc.getinsurcode()[i]
.getsubclass()[0].getinsurancecodeid());//
_carcode
.setsubclassid(_sc.getinsurcode()[i].getsubclass()[0]
.getid());//
_carcode.setinsurancepolicyid(carCollection.getInsPolicy()
.getid());
_carcode.setsubclassinfo(getInsureSingleSubCode(_carcode
.getsubclassid()));
Global.getInstance().getDataSource("FISP").Insert(_carcode);
carcode[i] = _carcode;
}
}
carCollection.setInsCode(carcode);
// }
} catch (Exception ex) {
// Global.getInstance().getLogService().error(ex.getMessage());
// System.out.print(ex.getMessage());
Global.getInstance().LogError(this, ex);
return null;
}
return carCollection;
}
private static String getDateTimeString(String formateStr) {
String timeStr = "";
try {
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("",
java.util.Locale.SIMPLIFIED_CHINESE);
sdf.applyPattern(formateStr);
timeStr = sdf.format(new Date());
// date = sdf.parse(timeStr);
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return timeStr;
}
public static selectinscode getssFromData(String _id) {
selectinscode sc = new selectinscode();
carinsdatainsurancepolicy cp = new carinsdatainsurancepolicy();// cp...seach
try {
EntitySet<carinsdatainsurancepolicy> cpcode = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(
"select * from carinsdatainsurancepolicy where id = '"
+ _id + "'",
carinsdatainsurancepolicy.class);
if (cpcode != null) {
if (cpcode.getSize() > 0) {
cp = cpcode.getResult().get(0);// carinsdatainsurancepolicy
}
}
String[] pic = new String[1];// notice
pic[0] = cp.getremark();
sc.setmessagenotic(pic);
carinsdatapolicyinsurancecode[] _carcode = null; // seach
// sss
EntitySet<carinsdatapolicyinsurancecode> listcode = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsdatapolicyinsurancecode where insurancepolicyid = '"
+ cp.getid() + "'",
carinsdatapolicyinsurancecode.class);
if (listcode.getSize() > 0) {
_carcode = new carinsdatapolicyinsurancecode[listcode.getSize()];
for (int i = 0; i < listcode.getResult().size(); i++) {
_carcode[i] = listcode.getResult().get(i);// carinsdatapolicyinsurancecode
}
}
carinsbaseinsurancecode[] carbase = new carinsbaseinsurancecode[_carcode.length];
carinsbaseinsurancecode[] _arrayinscode = BaseData
.getInstance()
.getInsCode(cp.getzoneno().toString().trim(), cp.getorgno());
Hashtable<String, carinsbaseinsurancecode> _htexist = new Hashtable<String, carinsbaseinsurancecode>();
for (int i = 0; i < _arrayinscode.length; i++) {
if (!_htexist.containsKey(_arrayinscode[i].getid().trim())) {
_htexist.put(_arrayinscode[i].getid().trim(),
_arrayinscode[i]);
}
}
for (int i = 0; i < carbase.length; i++) {
if (_htexist.containsKey(_carcode[i].getinsurancecodeid()
.trim())) {
carbase[i] = _htexist.get(_carcode[i].getinsurancecodeid()
.trim());
carbase[i].setpremium(_carcode[i].getpremium()
.doubleValue());//
carbase[i].setsuminsured(_carcode[i].getamount()
.doubleValue());
carbase[i].setdiscountrate(Double.parseDouble(_carcode[i]
.getremark()));
carbase[i].setdefaultsuminsured(_carcode[i].getamount()
.doubleValue());
// ...
if (carbase[i].getgeneracode().equals("C105")) {
carinsbaseinsurancecodesubclass _ret = getInsureSingleSubCode(_carcode[i]
.getsubclassid());
int tr = _ret.getparametervalue() / 10000
/ _ret.getdisplayindex();
carbase[i].setinputvalue(String.valueOf(tr));
} else {
carbase[i].setinputvalue(_carcode[i].getamount()
.toString());
}
if (carbase[i].getsubclass().length == 1) {
carbase[i].setcreator("1");
} else {
carbase[i].setcreator(_carcode[i].getsubclassid());
}
if (carbase[i].getgeneracode().equals("C902")) {
sc.setcartax(_carcode[i].getdiscountpremium()
.doubleValue());// 车船税
}
if (carbase[i].getgeneracode().equals("C901")) {
sc.setsumconstraintpreiumrate(_carcode[i].getpremium()
.doubleValue());// 交强险
sc.setconstraintpay(_carcode[i].getdiscountpremium()
.doubleValue());// 交强险查看
sc.setdiscountconstraintpreiumrate(_carcode[i]
.getpremium().doubleValue()
* Double.parseDouble(_carcode[i].getremark()));
}
sc.setinsurcode(carbase);
}
}
sc.setImag(cp.getcarimage());// discountcommercepreiumrate
// sumconstraintpreiumrate
sc.setpayall(cp.getamountpaid().doubleValue());// .......
sc.setdisall(cp.getdiscountpremium().doubleValue());
sc.setsumcommercepay(sc.getpayall() - sc.getcartax()
- sc.getconstraintpay());
sc.setdiscountcommercepreiumrate(sc.getdisall()
- sc.getsumconstraintpreiumrate() - sc.getcartax());// ......
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return sc;
}
private static carinsbaseinsurancecode getinsurancecode(
String InsuranceCodeID) {
if (InsuranceCodeID == null)
return null;
carinsbaseinsurancecode _ret = null;
try {
EntitySet<carinsbaseinsurancecode> listset = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(
"select * from carinsbaseinsurancecode where ID='"
+ InsuranceCodeID + "'",
carinsbaseinsurancecode.class);// .Find("");
if (listset != null) {
if (listset.getSize() > 0)
_ret = listset.getResult().get(0);
} else {
return _ret;
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return _ret;
}
private static carinsbaseinsurancecodesubclass getInsureSingleSubCode(
String InsuranceCodeID) {
if (InsuranceCodeID == null)
return null;
carinsbaseinsurancecodesubclass _ret = null;
try {
EntitySet<carinsbaseinsurancecodesubclass> listset = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsbaseinsurancecodesubclass where ID='"
+ InsuranceCodeID + "'",
carinsbaseinsurancecodesubclass.class);// .Find("");
if (listset != null) {
if (listset.getSize() > 0)
_ret = new carinsbaseinsurancecodesubclass();
_ret = listset.getResult().get(0);
} else {
return _ret;
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return _ret;
}
public static carinscarcollection getModiyData(String carid, String persid,
String poliyid, String detailid) {
if (carid == null || persid == null || poliyid == null)
return null;
carinscarcollection collection = new carinscarcollection();
carinsdatacarinfo carobj = null;
carinsdatacarperson perobj = null;
carinsdatainsurancepolicy poliobj = null;
carinsdatadetailmodel detailobj = null;
carinsdatapolicyinsurancecode[] inscodeobj = null;
try {
EntitySet<carinsdatacarinfo> carinfo = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(
"select * from carinsdatacarinfo where ID='"
+ carid + "'", carinsdatacarinfo.class);// .Find("");
EntitySet<carinsdatacarperson> persinfo = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(
"select * from carinsdatacarperson where ID='"
+ persid + "'", carinsdatacarperson.class);
EntitySet<carinsdatainsurancepolicy> poliyidinfo = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsdatainsurancepolicy where ID='"
+ poliyid + "'",
carinsdatainsurancepolicy.class);
EntitySet<carinsdatadetailmodel> detailinfo = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(
"select * from carinsdatadetailmodel where ID='"
+ detailid + "'",
carinsdatadetailmodel.class);
EntitySet<carinsdatapolicyinsurancecode> inscodeinfo = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsdatapolicyinsurancecode where insurancepolicyid='"
+ poliyid + "'",
carinsdatapolicyinsurancecode.class);
if (carinfo != null) {
if (carinfo.getSize() > 0)
carobj = new carinsdatacarinfo();
carobj = carinfo.getResult().get(0);
collection.setCarInfo(carobj);
}
if (persinfo != null) {
if (persinfo.getSize() > 0)
perobj = new carinsdatacarperson();
perobj = persinfo.getResult().get(0);
collection.setCarPerson(perobj);
}
if (poliyidinfo != null) {
if (poliyidinfo.getSize() > 0)
poliobj = new carinsdatainsurancepolicy();
poliobj = poliyidinfo.getResult().get(0);
collection.setInsPolicy(poliobj);
}
if (detailinfo != null) {
if (detailinfo.getSize() > 0)
detailobj = new carinsdatadetailmodel();
detailobj = detailinfo.getResult().get(0);
collection.setDetailmodel(detailobj);
}
if (inscodeinfo.getSize() > 0) {
inscodeobj = new carinsdatapolicyinsurancecode[inscodeinfo
.getSize()];
for (int i = 0; i < inscodeinfo.getResult().size(); i++) {
inscodeobj[i] = inscodeinfo.getResult().get(i);// carinsdatapolicyinsurancecode
inscodeobj[i]
.setsubclassinfo(getInsureSingleSubCode_(inscodeobj[i]
.getsubclassid()));
}
collection.setInsCode(inscodeobj);
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return collection;
}
public static company getcompanydata(String zone, String company) {
if (zone == null || company == null)
return null;
company com = null;
try {
EntitySet<company> cominfo = Global.getInstance().getDataSource(
"FISP").ExecuteSQL(
"select * from company where zoneno='" + zone
+ "' and companyno='" + company + "'",
company.class);// .Find("");
if (cominfo != null) {
if (cominfo.getSize() > 0)
com = new company();
com = cominfo.getResult().get(0);
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return com;
}
public static int updataperson(String t1, String t2, String t3, String t4,
String t5, String id, String temp) {
if (id == null)
return 0;
try {
if (temp.equals("0")) {
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"update carinsdatacarperson set Tname='" + t1
+ "',Taccount='" + t2 + "',Tbank='" + t3
+ "' where id='" + id + "'");// .updata("");
} else if (temp.equals("1")) {
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"update CarInsDataCarPerson set ispostgetreport='" + t5
+ "', Gname='" + t1 + "',Gtel='" + t2
+ "',Gaddress='" + t3 + "',Gpostcode='" + t4
+ "' where id='" + id + "'");// .updata("");
}
return 1;
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return 0;
}
}
private static Hashtable<String, carinsbasecaridnozone> _htexist = new Hashtable<String, carinsbasecaridnozone>();
public static carinsbasecaridnozone getFirstNOfromZoneNo(String city_) {
if (city_ == null)
return null;
carinsbasecaridnozone city = null;
if (_htexist.containsKey(city_)) {
return _htexist.get(city_);
}
try {
EntitySet<carinsbasecaridnozone> cityinfo = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(
"select * from carinsbasecaridnozone where zoneno="
+ city_ + "", carinsbasecaridnozone.class);// .Find("");
if (cityinfo != null) {
if (cityinfo.getSize() > 0)
city = new carinsbasecaridnozone();
city = cityinfo.getResult().get(0);
if (!_htexist.containsKey(city.getzoneno())) {
_htexist.put(city.getzoneno().toString(), city);
}
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return city;
}
public int deletViewedata(String carid, String personid, String poliyid) {
if (carid == null || personid == null || poliyid == null)
return 0;
try {
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"delete from carinsdatacarinfo where id='" + carid + "' ");
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"delete from carinsdatacarperson where id='" + personid
+ "' ");
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"delete from carinsdatainsurancepolicy where id='"
+ poliyid + "' ");
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"delete from carinsdatapolicyinsurancecode where insurancepolicyid='"
+ poliyid + "' ");
return 1;
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return 0;
}
}
public static int paysuccess(String stateid, String id) {
if (id == null)
return 0;
try {
Global.getInstance().getDataSource("FISP").ExecuteNonQuery(
"update carinsdatainsurancepolicy set stateid=" + stateid
+ " where id='" + id + "'");// .updata("");
return 1;
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return 0;
}
}
public static boolean ispaysuccess(String id) {
if (id == null)
return false;
try {
carinsdatainsurancepolicy poliy = null;
EntitySet<carinsdatainsurancepolicy> cityinfo = Global
.getInstance()
.getDataSource("FISP")
.ExecuteSQL(
"select * from carinsdatainsurancepolicy where id='"
+ id + "'", carinsdatainsurancepolicy.class);// .Find("");
if (cityinfo != null) {
if (cityinfo.getSize() > 0) {
poliy = new carinsdatainsurancepolicy();
poliy = cityinfo.getResult().get(0);
if (poliy.getstateid() == 1) {
return true;
}
}
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return false;
}
return false;
}
private static int getCarAge(Date _dtbegin, Date _dtend) {
int _ret = 0;
Calendar a = Calendar.getInstance();
Calendar b = Calendar.getInstance();
a.setTime(_dtbegin);
b.setTime(_dtend);
_ret = (b.get(Calendar.YEAR) - a.get(Calendar.YEAR)) * 12
+ (b.get(Calendar.MONTH) - a.get(Calendar.MONTH))
+ (b.get(Calendar.DATE) >= a.get(Calendar.DATE) ? 0 : -1);
_ret = _ret / 12;
return _ret;
}
// 查看报价
public static int SendMessageByBB(viewcarinsinsinfo _carins, String uerinfo) {
int _ret = 0;
if (_carins == null)
return _ret;
try {
int age = getCarAge(_carins.getcarbuydate(), _carins
.geteffectivedatebegin());
String _sql = "select * From carinsmessagesample where companyNo='"
+ _carins.getorgno().trim() + "' and zoneno='"
+ _carins.getzoneno().toString().trim() + "' and "
+ "(isRenewing = '-1' or isRenewing = '"
+ _carins.getisrenewing() + "') and "
+ "(GroupType = '-1' or GroupType = '"
+ _carins.getinsgrouptype() + "') and "
+ "(CarType ='-1' or CarType = '"
+ _carins.getcarcartypeid() + "') and "
+ "(UseType = '-1' or UseType = '"
+ _carins.getcarusetypeid() + "') and "
+ "(BelongType = '-1' or BelongType = '"
+ _carins.getcarbelongid() + "') and "
+ "(MaxCarAge = '-1' or MaxCarAge > '"
+ String.valueOf(age)
+ "') and (MinCarAge = '-1' or MinCarAge <= '"
+ String.valueOf(age) + "') and "
+ "(MaxTonnage = '-1' or MaxTonnage > '"
+ String.valueOf(_carins.getcartonnage().doubleValue())
+ "') and (MinTonnage = '-1' or MinTonnage <= '"
+ String.valueOf(_carins.getcartonnage().doubleValue())
+ "') and " + "(MaxSeatNum = '-1' or MaxSeatNum > '"
+ String.valueOf(_carins.getcarseating())
+ "') and (MinSeatNum = '-1' or MinSeatNum <= '"
+ String.valueOf(_carins.getcarseating()) + "') ";
EntitySet<carinsmessagesample> _lst = Global.getInstance()
.getDataSource("FISP").ExecuteSQL(_sql,
carinsmessagesample.class);
if (_lst.getSize() == 0)
return _ret;
carinsdatapolicyinsurancecode[] inscodeobj = null;
Hashtable<String, carinsdatapolicyinsurancecode> _htsample = new Hashtable<String, carinsdatapolicyinsurancecode>();
Hashtable<String, String> _htID = new Hashtable<String, String>();
EntitySet<carinsdatapolicyinsurancecode> inscodeinfo = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsdatapolicyinsurancecode where insurancepolicyid='"
+ _carins.getid() + "'",
carinsdatapolicyinsurancecode.class);
if (inscodeinfo.getSize() > 0) {
inscodeobj = new carinsdatapolicyinsurancecode[inscodeinfo
.getSize()];
for (int i = 0; i < inscodeinfo.getResult().size(); i++) {
inscodeobj[i] = inscodeinfo.getResult().get(i);// carinsdatapolicyinsurancecode
}
}
for (int i = 0; i < inscodeobj.length; i++) {
carinsdatapolicyinsurancecode _ic = inscodeobj[i];
_htsample.put(_ic.getinsurancecodeid(), _ic);
}
Hashtable<String, String> _htsampleStr = new Hashtable<String, String>();
Double _dispremium = _carins.getdiscountpremium().doubleValue();
Double _getAll = 0.0;
for (int i = 0; i < _lst.getResult().size(); i++) {
carinsmessagesample _cis = _lst.getResult().get(i);
if (_cis.getremark() != null
&& (!_cis.getremark().trim().equals(""))) {
_htID.put(_cis.getremark(), _cis.getremark());
if (_htsample.containsKey(_cis.getremark())) {
carinsdatapolicyinsurancecode _caripi = (carinsdatapolicyinsurancecode) _htsample
.get(_cis.getremark());
if (_cis.getmaxpremiumsum().doubleValue() > _caripi
.getdiscountpremium().doubleValue()
&& _cis.getminpremiumsum().doubleValue() <= _caripi
.getdiscountpremium().doubleValue()) {
List<String> _lststrtemp = CalculationInfo
.ResolutionStr(_cis.getformula());
if (_lststrtemp != null) {
for (int j = 0; j < _lststrtemp.size(); j++) {
if (_lststrtemp.get(j).equals("X"))
_lststrtemp.set(j, String
.valueOf(_caripi
.getdiscountpremium()
.doubleValue()));
}
}
Double _retdoubletemp = CalculationInfo
.Calculation(_lststrtemp);
String _msgtemp = _cis.getsamplecontent();
String[] _breakmsgtemp = _msgtemp.split("longsms");
String _sendmsgtemp = "";
for (int j = 0; j < _breakmsgtemp.length; j++) {
if (_breakmsgtemp[j].equals("usename"))
_breakmsgtemp[j] = uerinfo.split("//*")[1];// _ui.getname();
if (_breakmsgtemp[j].equals("PremiumSum"))
_breakmsgtemp[j] = String
.valueOf(_caripi
.getdiscountpremium()
.doubleValue());
if (_breakmsgtemp[j].equals("Fees"))
_breakmsgtemp[j] = BigDecimal.valueOf(
_retdoubletemp).divide(
BigDecimal.valueOf(1), 2,
BigDecimal.ROUND_HALF_UP)
.toString();
if (_breakmsgtemp[j].equals("carinfo"))
_breakmsgtemp[j] = _carins
.getcarcarmodeldetailname();
if (_breakmsgtemp[j].equals("carno"))
_breakmsgtemp[j] = _carins.getcarcaridno();
if (!_breakmsgtemp[j].equals("**")) {
_sendmsgtemp += _breakmsgtemp[j];
}
}
_htsampleStr.put(_caripi.getinsurancecodeid(),
_sendmsgtemp);
_dispremium = _dispremium
- _caripi.getdiscountpremium()
.doubleValue();
_getAll += _retdoubletemp;
}
}
}
}
Double _retdouble = _dispremium;
String _msg = "";
for (int i = 0; i < _lst.getResult().size(); i++) {
carinsmessagesample _cis = _lst.getResult().get(i);
;
if (_cis.getremark() == null
|| _cis.getremark().trim().equals("")) {
boolean _allexist = true;
if (!_cis.getdirectedinsurancecodelist().equals("-1")) {
String instempstr = _cis.getdirectedinsurancecodelist();
String[] lstinsstr = instempstr.split(",");
for (int j = 0; j < lstinsstr.length; j++) {
if (!_htsample.containsKey(lstinsstr[j]))
_allexist = false;
}
}
if (_allexist) {
List<String> _lststr = CalculationInfo
.ResolutionStr(_cis.getformula());
if (_lststr != null) {
for (int k = 0; k < _lststr.size(); k++) {
if (_lststr.get(k).equals("X"))
_lststr.set(k, String.valueOf(_dispremium));
}
}
Double _retdoubletemp = CalculationInfo
.Calculation(_lststr);
if (_retdoubletemp < _retdouble) {
_retdouble = _retdoubletemp;
_msg = _cis.getsamplecontent();
}
}
}
}
_getAll += _retdouble;
String[] _breakmsg = _msg.split("longsms");
String _sendmsg = "";
for (int i = 0; i < _breakmsg.length; i++) {
if (_breakmsg[i].equals("usename"))
_breakmsg[i] = uerinfo.split("//*")[1];
if (_breakmsg[i].equals("PremiumSum"))
_breakmsg[i] = String.valueOf(_dispremium);
if (_breakmsg[i].equals("Fees"))
_breakmsg[i] = BigDecimal.valueOf(_retdouble).divide(
BigDecimal.valueOf(1), 2, BigDecimal.ROUND_HALF_UP)
.toString();// String.valueOf(_retdouble);
if (_breakmsg[i].equals("FeesAll"))
_breakmsg[i] = BigDecimal.valueOf(_getAll).divide(
BigDecimal.valueOf(1), 2, BigDecimal.ROUND_HALF_UP)
.toString();// String.valueOf(_getAll);
if (_breakmsg[i].equals("carinfo"))
_breakmsg[i] = _carins.getcarcarmodeldetailname();
if (_breakmsg[i].equals("carno"))
_breakmsg[i] = _carins.getcarcaridno();
if (_htID.containsKey(_breakmsg[i])) {
if (_htsampleStr.containsKey(_breakmsg[i]))
_breakmsg[i] = _htsampleStr.get(_breakmsg[i]);
else
_breakmsg[i] = "";
}
if (!_breakmsg[i].equals("longsms")) {
_sendmsg += _breakmsg[i];// 短信信息
}
}
BBWebserviceClass.senMsg(uerinfo.split("//*")[3], _sendmsg);
carinsmessagetask _cimst = new carinsmessagetask();
_cimst.setmscontent(_sendmsg);
_cimst.setmsmobile(uerinfo.split("//*")[3]);// (_ui.getmobile());
_cimst.setmsobject(uerinfo.split("//*")[0]);// (_ui.getcardno());
_cimst.setmsobjecttype(1);
_cimst.setmsuserid(uerinfo.split("//*")[0]);// (_ui.getcardno());
_cimst.setpaynumber(BigDecimal.valueOf(0));
_cimst.setpayuserid("");// (_ui.getid());
_cimst.setsendflag(0);
// SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
// Date _date = date.parse("2008-01-01");
_cimst.setmssendtime(new Date());
// _cimst =
// (carinsmessagetask)Global.getInstance().getDataProvider().SaveEntity(_cimst);
Global.getInstance().getDataSource("FISP").Insert(_cimst);
if (_cimst.getid() != null)
_ret = 1;
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
}
return _ret;
}
public static void lookprint(String id_) {
try {
carinsdatapolicyinsurancecode[] inscodeobj = null;
EntitySet<carinsdatapolicyinsurancecode> inscodeinfo = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsdatapolicyinsurancecode where insurancepolicyid='"
+ id_ + "'",
carinsdatapolicyinsurancecode.class);
if (inscodeinfo.getSize() > 0) {
inscodeobj = new carinsdatapolicyinsurancecode[inscodeinfo
.getSize()];
for (int i = 0; i < inscodeinfo.getResult().size(); i++) {
inscodeobj[i] = inscodeinfo.getResult().get(i);// carinsdatapolicyinsurancecode
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static carinsdatainsurancepolicy getCarInsInfoFromID(String id) {
carinsdatainsurancepolicy policy = null;
carinsdatapolicyinsurancecode[] _ret = null;
try {
EntitySet<carinsdatainsurancepolicy> listpolicy = Global
.getInstance()
.getDataSource("FISP")
.ExecuteSQL(
"select * from carinsdatainsurancepolicy where ID = '"
+ id + "'", carinsdatainsurancepolicy.class);
if (listpolicy.getSize() > 0) {
policy = new carinsdatainsurancepolicy();
policy = listpolicy.getResult().get(0);
}
EntitySet<carinsdatapolicyinsurancecode> lstcode = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsdatapolicyinsurancecode where insurancepolicyid = '"
+ id + "'",
carinsdatapolicyinsurancecode.class);
if (lstcode.getSize() > 0) {
_ret = new carinsdatapolicyinsurancecode[lstcode.getSize()];
for (int i = 0; i < lstcode.getResult().size(); i++) {
_ret[i] = lstcode.getResult().get(i);
_ret[i].setsubclassinfo(getInsureSingleSubCode_(_ret[i]
.getsubclassid()));
}
policy.setInsurancecode(_ret);
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
}
return policy;
}
private static carinsbaseinsurancecodesubclass getInsureSingleSubCode_(
String InsuranceCodeID) {
if (InsuranceCodeID == null)
return null;
carinsbaseinsurancecodesubclass _ret = null;
try {
EntitySet<carinsbaseinsurancecodesubclass> list = Global
.getInstance().getDataSource("FISP").ExecuteSQL(
"select * from carinsbaseinsurancecodesubclass where ID='"
+ InsuranceCodeID + "'",
carinsbaseinsurancecodesubclass.class);// .Find("");
if (list != null) {
if (list.getSize() > 0)
_ret = list.getResult().get(0);
} else {
return _ret;
}
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
}
return _ret;
}
public ResultSet getcompanydata() {
ResultSet rs=null;
try {
rs= Global.getInstance().getDataSource("FISP").ExecuteSQL("select * from company order by displayindex");
} catch (Exception ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
return null;
}
return rs;
}
// 写文件
private void writeExcel(HSSFWorkbook wb, String fileName)
throws IOException {
FileOutputStream fileOut = new FileOutputStream(fileName);
if (wb != null)
wb.write(fileOut);
}
/**
*
* @param fileName
* @param rs
* @return
*/
public HSSFWorkbook createHSSFWorkbook(String fileName, ResultSet rs) {
try {
fileName="F://OUTDATA.xls";
rs=getcompanydata();
HSSFWorkbook wb = new HSSFWorkbook();
this.createHSSFSheet(wb, rs);
this.writeExcel(wb, fileName);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
*
* @param wb
* @param rs
* @return
*/
private HSSFSheet createHSSFSheet(HSSFWorkbook wb, ResultSet rs) {
if (rs != null && rs.getSize() > 0) {
HSSFSheet sheet = wb.createSheet("保险公司");
// 循环创建数据
String[][] str=rs.getResult();
for (int i = 0; i < str.length; i++) {
HSSFRow row = sheet.createRow(i);
row.setHeight((short)350);//行高
HSSFCell cell = null;
for(int j=0;j<str[0].length;j++){
sheet.setColumnWidth(j,3000);
cell = row.createCell(j);
cell.setCellValue(str[i][j].toString());
}
// HSSFRow row = sheet.createRow(i);
// row.setHeight((short)350);//行高
// HSSFCell cell = null;
//// HSSFCellStyle cellStyle = wb.createCellStyle();//表格样式
//// HSSFFont font = wb.createFont();
//// font.setFontName("Courier New");//字体
//// cellStyle.setFont(font);
//// cell.setCellStyle(cellStyle);
}
System.out.println("完毕了===========================================");
return sheet;
}
return null;
}
/*
public void outputExcel() {
String fileName = "F://Book1.xls";
POIFSFileSystem fs;
try {
// 读取
// company com[] = getcompanydata();
InputStream stream = new FileInputStream(fileName);
// byte[] kk=null;
// new InputStream(new FileInputStream()).read(kk);
fs = new POIFSFileSystem(stream);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
// 第一列
HSSFCell cell = row.getCell(0);
String msg = cell.getStringCellValue();
System.out.println("第一列:" + msg);
//
cell = row.getCell(1);
msg = cell.getStringCellValue();
System.out.println("第二列:" + msg);
HSSFCell cell1 = row.getCell(0);
cell1.setCellValue("目录");
HSSFCell cell2 = row.getCell(1);
cell2.setCellValue("内容");
HSSFCell cell3 = row.getCell(2);
cell3.setCellValue("说明");
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("F://Book2.xls");
wb.write(fileOut);
fileOut.close();
// ===========================================================
// 1.建excel
// 2.建sheet
// 3.建列
// 4.建行
// 5.将excel写到流中
} catch (IOException ex) {
Global.getInstance().getLogService().error(ex.getMessage());
System.out.print(ex.getMessage());
}
}*/
}
- 数据苦查询数据并将记录条数用excel文档导出——poi
- poi将数据库数据导出excel文件
- 使用poi将数据导出到excel
- POI EXCEL 数据导出
- poi导出excel数据
- 将查询的记录导出成excel表(poi)
- ASP将查询数据导出EXCEL
- 使用Apache的POI,将数据导出至EXCEL
- 使用POI将数据导出到Excel文件
- 利用POI将数据导出到EXCEL模板
- 使用poi将jtable数据导出到excel中
- 【copy】利用poi将数据库数据导出excel
- SpringMVC +poi 将web数据导出成Excel
- poi导出excel (大数据)
- erp10--excel数据导出--poi
- poi-Excel导出数据实例
- Java POI Excel 数据导出
- 将数据导出EXCEL
- Java 理论与实践: 正确使用 Volatile 变量
- 自然灾难,食品安全
- 如何使用CodeSmith批量生成代码
- 学习java的30个目标
- 如何使用CodeSmith批量生成代码
- 数据苦查询数据并将记录条数用excel文档导出——poi
- 联想笔记本不能安装操作系统以及如何解决隐藏分区的问题
- ORA-29702
- VS2008建的解决方案如何在VS2005打开?
- 绝对好的C++资料参考网站
- Java栈与堆
- 用Windows还是Linux取决于你最初的选择
- 使Fiddler支持Firefox
- ucLinux下sqlite数据库移植全攻略