import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.authentication.encoding.Md5PasswordEncoder;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.clt.sub.dao.ICustomerDao;
import com.clt.sub.model.TCity;
import com.clt.sub.model.TCustomer;
import com.clt.sub.model.TDriverCost;
import com.clt.sub.model.TArkilometer;
import com.clt.sub.model.TSubCarStyle;
import com.clt.sub.model.TSubsuppliers;
import com.clt.sub.service.IArkilometerService;
import com.clt.sub.service.ICityService;
import com.clt.sub.service.ICustomerSerivce;
import com.clt.sub.service.IDriverCostService;
import com.clt.sub.service.IProductService;
import com.clt.sub.service.ISubCarStyleService;
import com.clt.sub.service.ISubsuppliersService;
import com.clt.sub.service.imp.ProductServiceImp;
import com.clt.sub.service.IDriverCostService;
import com.clt.systemmanger.dao.IRoleDao;
import com.clt.systemmanger.dao.IStoresDao;
import com.clt.systemmanger.dao.IUserRoleDao;
import com.clt.systemmanger.model.TRole;
import com.clt.systemmanger.model.TStores;
import com.clt.systemmanger.model.TUser;
import com.clt.systemmanger.model.TUserRole;
import com.clt.systemmanger.service.IStoresService;
import com.clt.systemmanger.service.IUserService;
import com.clt.util.NumberUtil;
import com.clt.util.SystemConstants;
import com.clt.util.ValidUtils;
import com.mangofactory.swagger.annotations.ApiIgnore;
import com.sun.tools.xjc.reader.xmlschema.bindinfo.BIConversion.User;
import com.wordnik.swagger.annotations.Api;
@Api( value = "TKiloPriceAction-api" , description = "导入" , position = 5 )
@Controller
@RequestMapping( "/TKiloPriceAction" )
public class TKiloPriceAction{
@Autowired
ISubsuppliersService subService;
@Autowired
IUserService userService;
@Autowired
IArkilometerService arkilometerService;
@Autowired
IDriverCostService driverCostService;
@Autowired
ICustomerSerivce customerService;
@Autowired
ISubCarStyleService subCarStyleService;
@Autowired
IStoresDao iStoresDao;
@Autowired
IStoresService iStoresService;
@Autowired
IRoleDao roleDao;
@Autowired
IUserRoleDao userRoleDao;
@Autowired
ICustomerDao customerDao;
@Autowired
ICityService cityService;
private static final int EXCEL_ORDER_MAXCOUNT = 200;// 限制订单条数为200
private static final Integer Okilometers = null;
/**
* 进入导入
* @param request
* @return
*/
@RequestMapping( "/importTKiloPrice" )
@ApiIgnore
public String importOrder( HttpServletRequest request )
{
return "sub/orderInfo/importTKiloPrice";
}
/**
* 导入订单数据处理
* @param orderFile
* @param request
* @param response
* @return
*/
@RequestMapping( value = "/importTKiloPriceData" , method = RequestMethod.POST )
@ApiIgnore
public String importProductData( @RequestParam MultipartFile orderFile ,
HttpServletRequest request , HttpServletResponse response ) throws Exception
{
TUser user = ( TUser ) request.getSession().getAttribute( "user" );
int archid = user.getiArchive();
TSubsuppliers sub = subService.get( archid );// 分供方
if (null == sub || null == sub.getVcSubno())
{
request.setAttribute( "msg" , "分供方才可以导入订单" );
return "sub/orderInfo/importTKiloPriceRes";
}
String message = "";
int successCount = 0;
Map<String, String> errMap = new HashMap<String, String>();
// 获取上传文件输入流
try
{
InputStream instream = orderFile.getInputStream();
// 获取原始文件名
String fileName = orderFile.getOriginalFilename();
if ( !fileName.endsWith( ".xlsx" ) && !fileName.endsWith( ".xls" ))
{
message = "文件格式错误，请上传excle文件";
request.setAttribute( "msg" , message );
return "sub/orderInfo/importTKiloPriceRes";
}
// 判断是否是excel2007格式
boolean isE2007 = false;
if ( fileName.endsWith( ".xlsx" ) )
{
isE2007 = true;
}
// 根据不同版本获取Excel对象
Workbook wb = null;
if ( isE2007 )
{
wb = new XSSFWorkbook( instream );
}
else
{
// 将上传文件输入流直接转化为Excel对象
wb = new HSSFWorkbook( instream );
}
// 获取第一张sheet
Sheet sheet = wb.getSheetAt( 0 );
int rowNum = sheet.getLastRowNum();
if (rowNum < 1)
{
message = "文件中无数据，请检查后上传";
request.setAttribute( "msg" , message );
return "sub/orderInfo/importTKiloPriceRes";
}
if ( rowNum+1 > EXCEL_ORDER_MAXCOUNT)
{
message = "数据太多，请小于" + EXCEL_ORDER_MAXCOUNT + "条";
request.setAttribute( "msg" , message );
return "sub/orderInfo/importTKiloPriceRes";
}
Row row = null;
for ( int i = 1 ; i < rowNum + 1 ; i++ )
{
String no = "第" + i + "行";;
try
{
row = sheet.getRow( i );
int lastRowNum = row.getLastCellNum();
if (lastRowNum != 11)
{
// errMap.put( "第" + (i+1) + "行", "数据有错误，请检查数据" );
continue;
}
if ( null != row && row.getLastCellNum() > 0 )
{
no = getCellFormatValue( row.getCell( 0 ) );
String dtStart = getCellFormatValue( row.getCell( 1 ) ); // 发运日
String dtEnd = getCellFormatValue( row.getCell( 2 ) ); // 抵达日
String styleName=getCellFormatValue( row.getCell( 3 ) );//商品车名
String vcStartCity = getCellFormatValue( row.getCell( 4 ) ); // 起运城市
String vcEndCity = getCellFormatValue( row.getCell( 5 ) ); // 目的城市
String kiolmeter = getCellFormatValue( row.getCell( 6 ) ); // 公里
String nCost = getCellFormatValue( row.getCell( 7 ) ); // 单价
String custName = getCellFormatValue( row.getCell( 8 ) ); // 客户名
String nType=getCellFormatValue(row.getCell(9));//计价方式：1:单公里;2:单台
String nEnable=getCellFormatValue(row.getCell(10));//是否有效 （0.有效 1.无效 默认0）
if (ValidUtils.isEmptyStr(custName))
{
errMap.put( no, "客户名不能为空！" );
continue;
}
if (ValidUtils.isEmptyStr(vcStartCity))
{
errMap.put( no, "起运城市不能为空！" );
continue;
}
if (ValidUtils.isEmptyStr(vcEndCity))
{
errMap.put( no, "目的城市不能为空！" );
continue;
}
// 数据校验
if ( null == styleName || styleName.length() < 1 )
{
errMap.put( no, "车型名称为空" );
continue;
}
TDriverCost tdc = new TDriverCost();
//时间
SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
Date arriveTime = null;//时间
Date shipTime = null;
if ( null != dtEnd && dtEnd.length() > 0 )
{
arriveTime = sdf.parse( dtEnd.trim() );
tdc.setDtEnd( arriveTime );// 抵达日期
}
if ( null != dtStart && dtStart.length() > 0 )
{
shipTime = sdf.parse( dtStart.trim() );
tdc.setDtStart( shipTime ); // 发运日期
}
if (shipTime.after( arriveTime ))
{
errMap.put( no, "发运日期晚于抵达日期" );
continue;
}
TSubCarStyle subCarStyle = creatCarStyle( styleName , sub );// 分供方车型
tdc.setISubCarid(subCarStyle.getId());//车型id
tdc.setVcCarName( subCarStyle.getVcCarStyle());//车名
TCustomer tCustomer = createCust( custName,sub);// 客户
tdc.setICustomerid(tCustomer.getId());//客户id
tdc.setVcCustomer(tCustomer.getVcCustomerName());//客户名
tdc.setVcSubno(sub.getVcSubno());//所属分供方编号
tdc.setDtAdd(new Date());
tdc.setVcStartCity(vcStartCity);//起点
tdc.setVcEndCity(vcEndCity);//终点
int startCityId = cityService.getIdByName( vcStartCity );//通过城市名 获取它对应的id
int endCityId = cityService.getIdByName( vcEndCity );
tdc.setIStartId( startCityId);
tdc.setIEndId( endCityId);
tdc.setnCost( NumberUtil.parseFloat(nCost));//单价
tdc.setVcUpdateUser( user.getVcAccount());
tdc.setNEnable(tdc.getNEnable());//是否有效
tdc.setnType(Integer.parseInt( nType ));//支付类型
driverCostService.save(tdc);
TArkilometer tak=new TArkilometer();
if ( null != dtEnd && dtEnd.length() > 0 )
{
arriveTime = sdf.parse( dtEnd.trim() );
tak.setDtEnd( arriveTime );// 抵达日期
}
if ( null != dtStart && dtStart.length() > 0 )
{
shipTime = sdf.parse( dtStart.trim() );
tak.setDtStart( shipTime ); // 发运日期
}
if (shipTime.after( arriveTime ))
{
errMap.put( no, "发运日期晚于抵达日期" );
continue;
}
tak.setVcStartCity(vcStartCity);//起点
tak.setVcEndCity(vcEndCity);//终点
tak.setIStartId( startCityId);//起点城市ID
tak.setIEndId( endCityId);//终点城市id
tak.setNKilometer(NumberUtil.parseFloat(kiolmeter));//公里数
tak.setVcCustomer(tCustomer.getVcCustomerName());//客户
tak.setiCustomerId(tCustomer.getId());//客户id
tak.setVcSubno(sub.getVcSubno());//所属供应商编号
tak.setNEnable(tak.getNEnable());//是否有效
tak.setVcUpdateUser( user.getVcAccount());
arkilometerService.saveOrUpdate( tak );
successCount++ ;
}
}
catch(ParseException e)
{
errMap.put( no , "日期格式转换异常");
}
catch ( Exception e )
{
errMap.put( no , "系统处理异常");
}
}
}
catch ( Exception e )
{
e.printStackTrace();
message = "系统错误";
}
message = "成功导入：<span style='color:red; font-size:16px; font-weight: bold;'> " + successCount + " </span>条数据";
request.setAttribute( "msg" , message );
request.setAttribute( "res" , errMap );
return "sub/orderInfo/importTKiloPriceRes";
}
/**
* @Description:创建车型
* @param styleName
*/
private TSubCarStyle creatCarStyle( String styleName , TSubsuppliers sub )
{
TSubCarStyle subCarStyle = null;
List< TSubCarStyle > substylist = subCarStyleService.findByProperties(
new String[] { "vcSubno" , "vcCarStyle" , "NEnable" } , new Object[] {
sub.getVcSubno() , styleName , SystemConstants.SYS_ENABLE } );
if ( CollectionUtils.isNotEmpty( substylist ) )
{
subCarStyle = substylist.get( 0 );
}
else
{
// 增加新的车型
subCarStyle = new TSubCarStyle();
subCarStyle.setNEnable( SystemConstants.SYS_ENABLE );
subCarStyle.setVcCarStyle( styleName );
subCarStyle.setVcSubno( sub.getVcSubno() );
subCarStyleService.save( subCarStyle );
}
return subCarStyle;
}
/**
* @Description:创建客户
* @param custName
*/
private TCustomer createCust(String custName,TSubsuppliers sub)
{
TCustomer tCustomer = null;
List< TCustomer > custList = customerDao.findByPropertys( new String[] {
"vcCustomerName", "vcSubno" , "NEnable" } , new Object[] { custName ,
sub.getVcSubno(),
SystemConstants.SYS_ENABLE } );
if ( CollectionUtils.isEmpty( custList ) )
{
// 新增客户信息
tCustomer = new TCustomer();
tCustomer.setNEnable( SystemConstants.SYS_ENABLE );//当前状态
tCustomer.setDtAdd( new Date()); //操作时间
tCustomer.setVcSubno( sub.getVcSubno());//所属供应商编号
tCustomer.setVcCustomerName( custName ); //客户全称
tCustomer.setVcShortName(custName);//客户简称
tCustomer.setVcCustomerNo(tCustomer.getVcCustomerNo());//客户编号
customerService.save( tCustomer );
}
else
{
tCustomer = custList.get( 0 );
}
return tCustomer;
}
/**
* 根据Cell类型设置数据
* @param cell
* @return
*/
public static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case Cell.CELL_TYPE_NUMERIC:
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
//方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//方法2：这样子的data格式是不带带时分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
DecimalFormat df = new DecimalFormat("0");
cellvalue = df.format(cell.getNumericCellValue());
}
break;
 null
