cucu_YOS/src/custom/restcontroller/webmanage/sale/order/OrderImport.java

package restcontroller.webmanage.sale.order;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import beans.datacontrllog.DataContrlLog;
import beans.itemprice.ItemPrice;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING_CLEAN;
import common.data.ExcelFactory;
import common.data.Row;
import common.data.Rows;
import common.data.SQLFactory;
import restcontroller.R;
import restcontroller.system.attachment.Attachment;
import utility.tools.Math;

import static beans.order.Order.getOrderRows;

public class OrderImport extends Controller {

	public OrderImport(JSONObject arg0) throws YosException {
		super(arg0);
		// TODO Auto-generated constructor stub
	}

	/**
	 * 下载订单导入单模板
	 *
	 * @return
	 */
	@API(title = "下载订单导入单模板", apiversion = R.ID20230227194703.v1.class)
	public String downloadOrderExcel() throws YosException {
		ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForOrder");

		XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
		XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

		CellStyle style = xssfFWorkbook.createCellStyle();
		// 设置为文本格式，防止身份证号变成科学计数法
		DataFormat format = xssfFWorkbook.createDataFormat();
		style.setDataFormat(format.getFormat("@"));
		// 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
		sheet.setDefaultColumnStyle(0, style);
		ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
		XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
		XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
		XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
		XSSFCellStyle titleCellStyle4 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
		ExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4,
				xssfFWorkbook);// 写入标题

		Rows aa = uploadExcelToObs(excelFactory);
		String url = "";
		if (!aa.isEmpty()) {
			url = aa.get(0).getString("url");
		}
		return getSucReturnObject().setData(url).toString();

	}

	/**
	 * 下载订单导入单模板
	 *
	 * @return
	 */
	@API(title = "下载订单导入单模板", apiversion = R.ID20230308155703.v1.class)
	public String downloadOrderExcelSpecial() throws YosException {
		ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForOrder");

		XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
		XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

		CellStyle style = xssfFWorkbook.createCellStyle();
		// 设置为文本格式，防止身份证号变成科学计数法
		DataFormat format = xssfFWorkbook.createDataFormat();
		style.setDataFormat(format.getFormat("@"));
		// 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
		sheet.setDefaultColumnStyle(0, style);
		ExportExcelSpecial.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
		XSSFCellStyle titleCellStyle1 = ExportExcelSpecial.createTitleCellStyle1(xssfFWorkbook);
		XSSFCellStyle titleCellStyle2 = ExportExcelSpecial.createTitleCellStyle2(xssfFWorkbook);
		XSSFCellStyle titleCellStyle3 = ExportExcelSpecial.createBodyCellStyle(xssfFWorkbook);
		XSSFCellStyle titleCellStyle4 = ExportExcelSpecial.createTitleCellStyle3(xssfFWorkbook);
		ExportExcelSpecial.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4,
				xssfFWorkbook);// 写入标题

		Rows aa = uploadExcelToObs(excelFactory);
		String url = "";
		if (!aa.isEmpty()) {
			url = aa.get(0).getString("url");
		}
		return getSucReturnObject().setData(url).toString();

	}

	/**
	 * 导入订单
	 *
	 * @return
	 */
	@API(title = "导入订单明细", apiversion = R.ID20230227194803.v1.class)
	@CACHEING_CLEAN(apiClass = { Order.class, OrderItems.class, restcontroller.sale.order.Order.class })
	public String uploadOrderMxByExcel() throws YosException {

		long sa_orderid = content.getLongValue("sa_orderid");
		long sa_projectid = 0;
		Rows orderRows = getOrderRows(this, sa_orderid);
		Rows sa_orderitemsrows = dbConnect
				.runSqlQuery("select itemid from sa_orderitems where sa_orderid=" + sa_orderid);
		ArrayList<Long> itemids = sa_orderitemsrows.toArrayList("itemid", new ArrayList<>());
		if (orderRows.isEmpty()) {
			return getErrReturnObject().setErrMsg("订单不存在，无法导入明细").toString();
		} else {
			if (!orderRows.get(0).getString("status").equals("新建")) {
				return getErrReturnObject().setErrMsg("非新建状态下的订单无法导入明细").toString();
			}
		}
		long sa_contractid = orderRows.get(0).getLong("sa_contractid");
		sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
		String type = orderRows.get(0).getString("type");

		// select t1.userid,t1.siteid,min(t2.discountrate) discountrate from sys_hr t1
		// inner join sa_salearea_hr t2 on t1.hrid=t2.hrid and t1.siteid=t2.siteid group
		// by t1.userid,t1.siteid

		JSONArray jsonArray = new JSONArray();
		jsonArray.add(content.getLong("attachmentid"));

		content.put("ownertable", "sa_order");
		content.put("ownerid", sa_orderid);
		content.put("usetype", "default");
		content.put("attachmentids", jsonArray);
		Attachment attachment = new Attachment(content);
		attachment.createFileLink();

		ExcelFactory e;
		try {

			// 华为云
			e = getPostExcelFactory(content.getLong("attachmentid"));
			// 本地
			// e = getPostExcelFactory();

			// Rows keyRow = dbConnect.runSqlQuery("select fagentnum,faddress from tagents
			// where 1=2");
			// Rows row1 = e.getSheetRows(1, keyRow, 1);
			ArrayList<String> keys = new ArrayList<>();
			ArrayList<String> sqllist = new ArrayList<>();
			keys.add("itemno");
			keys.add("qty");

			Rows rows = e.getSheetRows(0, keys, 3);
			int a = 1;
			int i = 0;
			// String msg = "";
			boolean iserr = false;
			Rows rowserr = new Rows();
			Rows rowssuc = new Rows();
			for (Row row : rows) {

				if (StringUtils.isEmpty(row.getString("itemno")) || StringUtils.isEmpty(row.getString("qty"))) {
					iserr = true;
					row.put("msg", "错误信息:商品编号，数量不能为空");
					rowserr.add(row);
					// rows.remove(row);
					// msg = "手机号，省市县及来源不能为空";
				} else {
					if (sa_projectid > 0) {
						Rows itemsRows = dbConnect.runSqlQuery(
								"select t1.itemid from sa_project_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.deleted=0 and t1.sa_projectid='"
										+ sa_projectid + "' and t2.itemno='" + row.getString("itemno")
										+ "' and t1.siteid='" + siteid + "'");
						if (!itemsRows.isEmpty()) {
							row.put("itemid", itemsRows.get(0).getString("itemid"));
							rowssuc.add(row);
						} else {
							iserr = true;
							row.put("msg", "错误信息:该报价单所属项目中不存在商品编号为" + row.getString("itemno") + "的商品");
							rowserr.add(row);
						}

					} else {
						Rows itemsRows = dbConnect.runSqlQuery("select itemid from plm_item where itemno='"
								+ row.getString("itemno").trim() + "' and siteid='" + siteid + "'");
						if (!itemsRows.isEmpty()) {
							row.put("itemid", itemsRows.get(0).getString("itemid"));
							rowssuc.add(row);
						} else {
							iserr = true;
							row.put("msg", "错误信息:不存在商品编号为" + row.getString("itemno").trim() + "的商品");
							rowserr.add(row);
						}
					}

				}
			}
			long[] sa_orderitemsid = createTableID("sa_orderitems", rowssuc.size());
			if (!rowssuc.isEmpty()) {
				SQLFactory sqlFactoryupload = null;
				for (Row row : rowssuc) {
					BigDecimal qty = BigDecimal.valueOf(row.getDouble("qty"));
					if (!itemids.isEmpty()) {
						if (itemids.contains(row.getLong("itemid"))) {
							sqlFactoryupload = new SQLFactory(this, "订单商品明细_更新");
							Rows sa_orderitemsids = dbConnect
									.runSqlQuery("select sa_orderitemsid from sa_orderitems where sa_orderid="
											+ sa_orderid + " and itemid=" + row.getLong("itemid"));
							sqlFactoryupload.addParameter("sa_orderitemsid",
									sa_orderitemsids.get(0).getLong("sa_orderitemsid"));
						} else {
							sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
							sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
						}
					} else {
						sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
						sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
					}
					sqlFactoryupload.addParameter("siteid", siteid);
					sqlFactoryupload.addParameter("userid", userid);
					sqlFactoryupload.addParameter("username", username);
					sqlFactoryupload.addParameter("sa_orderid", sa_orderid);
					sqlFactoryupload.addParameter("rowno", getRowNo(sa_orderid));
					sqlFactoryupload.addParameter("itemid", row.getLong("itemid"));
					sqlFactoryupload.addParameter("remarks", "");
					// 商品信息
					Row itemRow = getItemRow(row.getLong("itemid"));
					BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");
					if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
						conversionrate = BigDecimal.valueOf(1);
					}
					sqlFactoryupload.addParameter("itemno", itemRow.getString("itemno"));
					sqlFactoryupload.addParameter("itemname", itemRow.getString("itemname"));
					sqlFactoryupload.addParameter("model", itemRow.getString("model"));
					sqlFactoryupload.addParameter("unit", itemRow.getString("unit"));
					sqlFactoryupload.addParameter("auxunit", itemRow.getString("auxunit"));
					sqlFactoryupload.addParameter("batchcontrol", itemRow.getLong("batchcontrol"));
					sqlFactoryupload.addParameter("delivery", itemRow.getLong("delivery"));
					sqlFactoryupload.addParameter("needdate", "null");
					sqlFactoryupload.addParameter("deliverydate", "null");
					sqlFactoryupload.addParameter("conversionrate", conversionrate);
					sqlFactoryupload.addParameter("stockid", 0);
					sqlFactoryupload.addParameter("position", 0);
					sqlFactoryupload.addParameter("batchno", "");
					sqlFactoryupload.addParameter("stockno", "");

					// 订购数量
					sqlFactoryupload.addParameter("qty", qty);
					// 辅助单位数量
					sqlFactoryupload.addParameter("auxqty", qty.divide(conversionrate));
					BigDecimal price;
					// 价格
					ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
					// 判断单价是否高于系统的单价
					// 价格
					BigDecimal defaultprice;
					switch (type) {
					case "项目订单":
						defaultprice = itemPrice.getContractprice(sa_contractid);
						break;
					case "工具订单":
						defaultprice = itemPrice.getMarketprice();
						break;
					default:
						defaultprice = itemPrice.getContractprice();
					}

					// 单价,折后价（元）,取合同价
					sqlFactoryupload.addParameter("defaultprice", defaultprice);
					// 金额,折后金额（元）
					sqlFactoryupload.addParameter("defaultamount", defaultprice.multiply(qty));
					// 牌价、市场价（元）,标准订单牌价取商品价格，项目订单取合同里的牌价
					sqlFactoryupload.addParameter("marketprice", itemPrice.getMarketprice());
					sqlFactoryupload.addParameter("price", defaultprice);
					// 折前金额（元）
					sqlFactoryupload.addParameter("amount", defaultprice.multiply(qty));
					sqllist.add(sqlFactoryupload.getSQL());
					i++;
				}
			}
			if (sqllist != null && !sqllist.isEmpty()) {
				sqllist.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "导入", "订单明细导入成功").getSQL());
				dbConnect.runSqlUpdate(sqllist);
				// 重新排序
				updateRowNo(sa_orderid);
			}
			if (iserr) {
				ExcelFactory excelFactory = new ExcelFactory("sa_ordererr");
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("itemno", "商品编号");
				map.put("qty", "数量");
				map.put("msg", "错误信息");
				ArrayList<String> colNameList = new ArrayList<String>();
				HashMap<String, Class> keytypemap = new HashMap<String, Class>();
				colNameList.add("itemno");
				colNameList.add("qty");
				colNameList.add("msg");
				keytypemap.put("itemno", String.class);
				keytypemap.put("qty", String.class);
				keytypemap.put("msg", String.class);
				rowserr.setFieldList(colNameList);
				rowserr.setFieldTypeMap(keytypemap);
				addSheet(excelFactory, "Sheet1", rowserr, map);

				Rows aa = uploadExcelToObs(excelFactory);
				String url = "";
				if (!aa.isEmpty()) {
					url = aa.get(0).getString("url");
				}
				return getSucReturnObject().setData(url).toString();
			}
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			// dbConnect.runSqlUpdate("delete from sa_order where sa_orderid=" +
			// sa_orderid);
			e1.printStackTrace();
			return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
		}
		return getSucReturnObject().toString();
	}

	/**
	 * 导入订单
	 *
	 * @return
	 */
	@API(title = "导入订单明细", apiversion = R.ID20230308155803.v1.class)
	@CACHEING_CLEAN(apiClass = { Order.class, OrderItems.class, restcontroller.sale.order.Order.class })
	public String uploadOrderMxByExcelSpecial() throws YosException {

		long sa_orderid = content.getLongValue("sa_orderid");
		long sa_projectid = 0;
		Rows orderRows = getOrderRows(this, sa_orderid);
		Rows sa_orderitemsrows = dbConnect
				.runSqlQuery("select itemid from sa_orderitems where sa_orderid=" + sa_orderid);
		ArrayList<Long> itemids = sa_orderitemsrows.toArrayList("itemid", new ArrayList<>());
		if (orderRows.isEmpty()) {
			return getErrReturnObject().setErrMsg("订单不存在，无法导入明细").toString();
		} else {
			if (!orderRows.get(0).getString("status").equals("新建")) {
				return getErrReturnObject().setErrMsg("非新建状态下的订单无法导入明细").toString();
			}
		}
		long sa_contractid = orderRows.get(0).getLong("sa_contractid");
		sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
		String type = orderRows.get(0).getString("type");

		// select t1.userid,t1.siteid,min(t2.discountrate) discountrate from sys_hr t1
		// inner join sa_salearea_hr t2 on t1.hrid=t2.hrid and t1.siteid=t2.siteid group
		// by t1.userid,t1.siteid

		JSONArray jsonArray = new JSONArray();
		jsonArray.add(content.getLong("attachmentid"));

		content.put("ownertable", "sa_order");
		content.put("ownerid", sa_orderid);
		content.put("usetype", "default");
		content.put("attachmentids", jsonArray);
		Attachment attachment = new Attachment(content);
		attachment.createFileLink();

		ExcelFactory e;
		try {

			// 华为云
			e = getPostExcelFactory(content.getLong("attachmentid"));
			// 本地
			//e = getPostExcelFactory();

			// Rows keyRow = dbConnect.runSqlQuery("select fagentnum,faddress from tagents
			// where 1=2");
			// Rows row1 = e.getSheetRows(1, keyRow, 1);
			ArrayList<String> keys = new ArrayList<>();
			ArrayList<String> sqllist = new ArrayList<>();
			keys.add("itemno");
			keys.add("qty");
			keys.add("defaultprice");
			
			Rows rows = e.getSheetRows(0, keys, 3);
			int a = 1;
			int i = 0;
			// String msg = "";
			boolean iserr = false;
			Rows rowserr = new Rows();
			Rows rowssuc = new Rows();
			for (Row row : rows) {

				if (StringUtils.isEmpty(row.getString("itemno")) || StringUtils.isEmpty(row.getString("qty")) || StringUtils.isEmpty(row.getString("defaultprice"))) {
					iserr = true;
					row.put("msg", "错误信息:商品编号，数量，折前价不能为空");
					rowserr.add(row);
					// rows.remove(row);
					// msg = "手机号，省市县及来源不能为空";
				} else {
					if (sa_projectid > 0) {
						Rows itemsRows = dbConnect.runSqlQuery(
								"select t1.itemid from sa_project_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.deleted=0 and t1.sa_projectid='"
										+ sa_projectid + "' and t2.itemno='" + row.getString("itemno")
										+ "' and t1.siteid='" + siteid + "'");
						if (!itemsRows.isEmpty()) {
							row.put("itemid", itemsRows.get(0).getString("itemid"));
							rowssuc.add(row);
						} else {
							iserr = true;
							row.put("msg", "错误信息:该报价单所属项目中不存在商品编号为" + row.getString("itemno") + "的商品");
							rowserr.add(row);
						}

					} else {
						Rows itemsRows = dbConnect.runSqlQuery("select itemid from plm_item where itemno='"
								+ row.getString("itemno").trim() + "' and siteid='" + siteid + "'");
						if (!itemsRows.isEmpty()) {
							row.put("itemid", itemsRows.get(0).getString("itemid"));
							rowssuc.add(row);
						} else {
							iserr = true;
							row.put("msg", "错误信息:不存在商品编号为" + row.getString("itemno").trim() + "的商品");
							rowserr.add(row);
						}
					}

				}
			}
			long[] sa_orderitemsid = createTableID("sa_orderitems", rowssuc.size());
			if (!rowssuc.isEmpty()) {
				SQLFactory sqlFactoryupload = null;
				for (Row row : rowssuc) {
					BigDecimal qty = BigDecimal.valueOf(row.getDouble("qty"));
					BigDecimal defaultprice = BigDecimal.valueOf(row.getDouble("defaultprice"));
					if (!itemids.isEmpty()) {
						if (itemids.contains(row.getLong("itemid"))) {
							sqlFactoryupload = new SQLFactory(this, "订单商品明细_更新");
							Rows sa_orderitemsids = dbConnect
									.runSqlQuery("select sa_orderitemsid from sa_orderitems where sa_orderid="
											+ sa_orderid + " and itemid=" + row.getLong("itemid"));
							sqlFactoryupload.addParameter("sa_orderitemsid",
									sa_orderitemsids.get(0).getLong("sa_orderitemsid"));
						} else {
							sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
							sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
						}
					} else {
						sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
						sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
					}
					sqlFactoryupload.addParameter("siteid", siteid);
					sqlFactoryupload.addParameter("userid", userid);
					sqlFactoryupload.addParameter("username", username);
					sqlFactoryupload.addParameter("sa_orderid", sa_orderid);
					sqlFactoryupload.addParameter("rowno", getRowNo(sa_orderid));
					sqlFactoryupload.addParameter("itemid", row.getLong("itemid"));
					sqlFactoryupload.addParameter("remarks", "");
					// 商品信息
					Row itemRow = getItemRow(row.getLong("itemid"));
					BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");
					if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
						conversionrate = BigDecimal.valueOf(1);
					}
					sqlFactoryupload.addParameter("itemno", itemRow.getString("itemno"));
					sqlFactoryupload.addParameter("itemname", itemRow.getString("itemname"));
					sqlFactoryupload.addParameter("model", itemRow.getString("model"));
					sqlFactoryupload.addParameter("unit", itemRow.getString("unit"));
					sqlFactoryupload.addParameter("auxunit", itemRow.getString("auxunit"));
					sqlFactoryupload.addParameter("batchcontrol", itemRow.getLong("batchcontrol"));
					sqlFactoryupload.addParameter("delivery", itemRow.getLong("delivery"));
					sqlFactoryupload.addParameter("needdate", "null");
					sqlFactoryupload.addParameter("deliverydate", "null");
					sqlFactoryupload.addParameter("conversionrate", conversionrate);
					sqlFactoryupload.addParameter("stockid", 0);
					sqlFactoryupload.addParameter("position", 0);
					sqlFactoryupload.addParameter("batchno", "");
					sqlFactoryupload.addParameter("stockno", "");

					// 订购数量
					sqlFactoryupload.addParameter("qty", qty);
					// 辅助单位数量
					sqlFactoryupload.addParameter("auxqty", qty.divide(conversionrate));
					BigDecimal price;
					// 价格
					ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
					// 判断单价是否高于系统的单价
					// 价格
//					switch (type) {
//					case "项目订单":
//						defaultprice = itemPrice.getContractprice(sa_contractid);
//						break;
//					case "工具订单":
//						defaultprice = itemPrice.getMarketprice();
//						break;
//					default:
//						defaultprice = itemPrice.getContractprice();
//					}

					// 单价,折后价（元）,取合同价
					sqlFactoryupload.addParameter("defaultprice", defaultprice);
					// 金额,折后金额（元）
					sqlFactoryupload.addParameter("defaultamount", defaultprice.multiply(qty));
					// 牌价、市场价（元）,标准订单牌价取商品价格，项目订单取合同里的牌价
					sqlFactoryupload.addParameter("marketprice", itemPrice.getMarketprice());
					sqlFactoryupload.addParameter("price", defaultprice);
					// 折前金额（元）
					sqlFactoryupload.addParameter("amount", defaultprice.multiply(qty));
					sqllist.add(sqlFactoryupload.getSQL());
					i++;
				}
			}
			if (sqllist != null && !sqllist.isEmpty()) {
				sqllist.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "导入", "订单明细导入成功").getSQL());
				dbConnect.runSqlUpdate(sqllist);
				// 重新排序
				updateRowNo(sa_orderid);
			}
			if (iserr) {
				ExcelFactory excelFactory = new ExcelFactory("sa_ordererr");
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("itemno", "商品编号");
				map.put("qty", "数量");
				map.put("msg", "错误信息");
				ArrayList<String> colNameList = new ArrayList<String>();
				HashMap<String, Class> keytypemap = new HashMap<String, Class>();
				colNameList.add("itemno");
				colNameList.add("qty");
				colNameList.add("msg");
				keytypemap.put("itemno", String.class);
				keytypemap.put("qty", String.class);
				keytypemap.put("msg", String.class);
				rowserr.setFieldList(colNameList);
				rowserr.setFieldTypeMap(keytypemap);
				addSheet(excelFactory, "Sheet1", rowserr, map);

				Rows aa = uploadExcelToObs(excelFactory);
				String url = "";
				if (!aa.isEmpty()) {
					url = aa.get(0).getString("url");
				}
				return getSucReturnObject().setData(url).toString();
			}
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			// dbConnect.runSqlUpdate("delete from sa_order where sa_orderid=" +
			// sa_orderid);
			e1.printStackTrace();
			return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
		}
		return getSucReturnObject().toString();
	}

	public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows,
			HashMap<String, String> titlemap) {
		ArrayList<String> keylist = datarows.getFieldList();
		XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
		XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

		XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
		XSSFFont font = xssfFWorkbook.createFont();

		font.setColor((short) 0xa);
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		xssfCellStyle1.setFont(font);

		ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
		XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
		XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
		XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
		ExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

		for (int n = 0; n < datarows.size(); n++) {
			Row row = datarows.get(n);
			XSSFRow datarow = sheet.createRow(n + 3);
			for (int i1 = 0; i1 < keylist.size(); i1++) {
				Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
				if (fieldclazztype == Integer.class) {
					datarow.createCell(i1).setCellValue(row.getInteger((String) keylist.get(i1)).intValue());
				} else if (fieldclazztype == Long.class) {
					datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1)));
				} else if (fieldclazztype == Float.class) {
					datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1)));
				} else if (fieldclazztype == Double.class) {
					datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1)));
				} else {
					datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1)));
				}
				if (i1 == 2) {
					datarow.getCell(i1).setCellStyle(xssfCellStyle1);
				}
			}
		}
		return sheet;
	}

	public XSSFSheet addSheetSpecial(ExcelFactory excelFactory, String sheetname, Rows datarows,
			HashMap<String, String> titlemap) {
		ArrayList<String> keylist = datarows.getFieldList();
		XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
		XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

		XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
		XSSFFont font = xssfFWorkbook.createFont();

		font.setColor((short) 0xa);
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		xssfCellStyle1.setFont(font);

		ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
		XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
		XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
		XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
		ExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

		for (int n = 0; n < datarows.size(); n++) {
			Row row = datarows.get(n);
			XSSFRow datarow = sheet.createRow(n + 3);
			for (int i1 = 0; i1 < keylist.size(); i1++) {
				Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
				if (fieldclazztype == Integer.class) {
					datarow.createCell(i1).setCellValue(row.getInteger((String) keylist.get(i1)).intValue());
				} else if (fieldclazztype == Long.class) {
					datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1)));
				} else if (fieldclazztype == Float.class) {
					datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1)));
				} else if (fieldclazztype == Double.class) {
					datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1)));
				} else {
					datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1)));
				}
				if (i1 == 3) {
					datarow.getCell(i1).setCellStyle(xssfCellStyle1);
				}
			}
		}
		return sheet;
	}

	/**
	 * 获取当前订单的最大行号
	 *
	 * @param sa_orderid
	 * @return
	 * @throws YosException
	 */
	public Long getRowNo(Long sa_orderid) throws YosException {
		Rows rows = dbConnect.runSqlQuery("SELECT COUNT(0) count from sa_orderitems WHERE sa_orderid = " + sa_orderid
				+ " AND siteid = '" + siteid + "'");
		return rows.get(0).getLong("count") + 1;
	}

	/**
	 * 重新对商品行排序
	 *
	 * @param sa_orderid
	 * @throws YosException
	 */
	public void updateRowNo(Long sa_orderid) throws YosException {
		String sql = "SELECT sa_orderitemsid from sa_orderitems WHERE sa_orderid= " + sa_orderid + "  and siteid = '"
				+ siteid + "' ORDER  BY createdate ";
		ArrayList<Long> sa_orderitemsids = dbConnect.runSqlQuery(sql).toArrayList("sa_orderitemsid", new ArrayList<>());
		if (sa_orderitemsids.size() > 0) {
			int rowno = 1;
			StringBuffer where = new StringBuffer("");
			for (Long id : sa_orderitemsids) {
				where.append(" WHEN " + id + " THEN " + rowno);
				rowno++;
			}

			SQLFactory sqlFactory = new SQLFactory(this, "更新行号");
			sqlFactory.addParameter("siteid", siteid);
			sqlFactory.addParameter_SQL("where", where);
			sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids);
			sql = sqlFactory.getSQL(false);
			dbConnect.runSqlUpdate(sql);
		}

	}

	/**
	 * 查询商品信息
	 *
	 * @param itemid
	 * @return
	 * @throws YosException
	 */
	public Row getItemRow(Long itemid) throws YosException {
		SQLFactory sqlFactory = new SQLFactory(this, "查询商品信息");
		sqlFactory.addParameter("siteid", siteid);
		sqlFactory.addParameter("itemid", itemid);
		Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false));
		return rows.isNotEmpty() ? rows.get(0) : new Row();

	}

}