1, Review
Purchase and sale contract
1. What is a purchase and sales contract
2. Analyze the relationship of table structure
3. Three paradigms and anti three paradigms of design table
4. Add redundant fields to make the query faster
5. Decentralized calculation principle
Calculation of total amount of purchase and sales contract
6. Implement CRUD operations related to purchase and sales contracts
7. Realize CRUD operation of goods
8. Realize CRUD operation of accessories
2, Fine grained permission control
On the basis of coarse-grained permission control, different controls are added for the data displayed by different users. The essence is data control.
1. Preparation of database tables
public class BaseEntity implements Serializable {
protected String createBy; //Creator's id
protected String createDept;//id of the creator's Department
protected Date createTime;//Creation time
protected String updateBy;//id of the Updater
protected Date updateTime;//Update time
Supplement the methods in Contract Action.insert()
public String insert() throws Exception {
//1. Add fine-grained permission control data
User user = super.getCurUser();
model.setCreateBy(user.getId());//Set the id of the creator
model.setCreateDept(user.getDept().getId());//Set the id of the creator's department
//1. Call the business method to save
contractService.saveOrUpdate(model);
//Skip page
return "alist";
}
Prepare user
OOP employees belong to the shipping department
All AOP employees belong to the shipping department
SOA department managers belong to the shipping department
Grant roles to users
OOP Shipping Specialist
AOP Shipping Specialist
SOA Shipping Manager
Assign modules to roles
Module for configuring the role of [Shipping Specialist]
Shipping Manager
Prepare three records
From the jUserCreate.jsp page, it can be concluded that users mainly determine what data they can see through the level
Modify the Contract Action.list() method
public String list() throws Exception {
String hql = "from Contract where 1=1 ";
//How to determine the user level
User user = super.getCurUser();
int degree = user.getUserinfo().getDegree();
if(degree==4){
//Description is employee
hql+=" and createBy='"+user.getId()+"'";
}else if(degree==3){
//He is the Department Manager and manages the Department
hql+=" and createDept='"+user.getDept().getId()+"'";
}else if(degree==2){
//The description is to manage this department and its subordinate departments?????
}else if(degree==1){
//It means that the vice president?????
}else if(degree==0){
//General manager
}
contractService.findPage(hql, page, Contract.class, null);
//Set the url address of the page
page.setUrl("contractAction_list");
//Push the page object to the top of the stack
super.push(page);
return "list";
}
3, There is a bug when adding goods under the purchase and sales contract
<td>
${o.contractProducts.size() }
/
<c:set var="extNo" value="0"></c:set>
<c:forEach items="${o.contractProducts }" var="cp" >
<c:if test="${cp.extCproducts.size()!=0 }">
<c:set var="extNo" value="${extNo+cp.extCproducts.size() }"></c:set>
</c:if>
</c:forEach>
${extNo }
</td>
What are the problems in loading the number of goods / accessories in the purchase and sales contract?
The problem is that when a purchase and sales contract record is loaded, the goods list under the purchase and sales contract will be loaded through association level data loading, and the attachment list under the goods list will also be loaded, resulting in a slow loading speed.
Solution:
Two redundant fields are added to the purchase and sales contract table. In the future, when adding goods under the purchase and sales contract, the quantity of goods in the purchase and sales contract table will be updated at the same time. When adding accessories, the quantity of accessories in the purchase and sales contract table will be updated.
New problems arise?
Due to the addition of redundant fields, the development and maintenance workload of programmers increases. The purpose is to ensure the consistency of data.
Job: I hope to reform the above problems, use the idea of decentralized calculation, and add two redundant fields in the Contract.c table
[interview:] after the system development is completed, the project has just been launched, and everything is normal. However, with the increase of the amount of data, after the system runs for half a year, the speed of loading data pages in the later stage is very slow, which is unacceptable to users. What good optimization scheme do you have.
5, POI Report
1.POI introduction
Apache specializes in operating excel api
2. Mainstream operation excel api Jxl Poi
1)Jxl can only operate excel 2003
2)Poi can operate the whole office (excel, doc(vb macro), ppt and visio); Including all excel versions
6, Application scenario
It is used to write data into excel, and users can share data. As backup data (excluding large fields), restore data.
1. Import relevant jar packages of POI
commons-codec-1.5.jar is not included
Maven project:
XML unitary setting in Pom.xml file:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.11</version> </dependency>
2. Requirements: 8 steps are required
a) Create a workbook
b) Create a sheet
c) Create a row object row (subscript start value is 0)
d) Create a cell object cell (subscript start value is 0)
e) Set content for cells
f) Set cell style, font and font size
g) Save, close flow object
h) Download
3. Implementation code
Workbook -HSSFWorkbook/XSSFWorkbook/SXSSFWorkbook
//1. Create Workbook //Workbook wb = new HSSFWorkbook();// Only the Excel 2003 version extension xls is supported //Workbook wb = new XSSFWorkbook(); // Support Excel 2007 + version extension xlsx Workbook wb = new SXSSFWorkbook(); //It supports Excel 2007 + version and the extension xlsx does not support template operation. Some objects generated can be transferred from memory to disk. //The default number of transferred objects is 100. If new SXSSFWorkbook(500) represents that the number of objects in memory reaches 500, it will be transferred to disk
public class POITest {
@Test
public void testPoi() throws Exception{
//1. Create Workbook
Workbook wb = new HSSFWorkbook();
//2. Create a worksheet
Sheet sheet = wb.createSheet();
//3. Create row object
Row row = sheet.createRow(4);//Parameter is subscript
//4. Create cells
Cell cell = row.createCell(3);//subscript
//5. Set cell content
cell.setCellValue("Spread Wisdom.Song Jiang");
//6. Set cell style
CellStyle cellStyle = wb.createCellStyle();//Style object created
Font font = wb.createFont();//Create font object
font.setFontHeightInPoints((short)48);//Set font size
font.setFontName("Fangzheng shutI");//Set font name
font.setColor(Font.COLOR_RED);
cellStyle.setFont(font);//Set the style object to use a font
cell.setCellStyle(cellStyle);
//7. Save and close the flow
OutputStream os = new FileOutputStream("D:/abc.xls");
wb.write(os);
//os.write(wb.toString().getBytes());
os.close();
//8. Download
}
}
7, Printing of shipment form
1. Statistics are made according to the ship schedule.
2. Which tables are the data sources?
Through analysis, it comes from the purchase and sales contract table and goods table
3. Think about how to check?
Hql statement
//=======================================Data output================================================= String hql= "from ContractProduct where to_char(contract.shipTime,'yyyy-MM') ='"+inputDate+"'"; List<ContractProduct> list = contractProductService.find(hql, ContractProduct.class, null);//Query the list of goods that meet the specified shipping date
4. Entry of shipment table page
As long as a calendar selection box is provided on this page, you can select the shipping date, and then use poi to print data
OutProductAction.print()
/**
* Print shipping form
* @return
* @throws Exception
*
* printNotTemplate
*/
public String print() throws Exception {
//General variable
int rowNo=0,cellNo=1;
Row nRow =null;
Cell nCell = null;
//1. Create Workbook
//Workbook wb = new HSSFWorkbook();// Only the Excel 2003 version extension xls is supported
//Workbook wb = new XSSFWorkbook(); // Support Excel 2007 + version extension xlsx
Workbook wb = new SXSSFWorkbook(); //It supports Excel 2007 + version and the extension xlsx does not support template operation. Some objects generated can be transferred from memory to disk.
//The default number of transferred objects is 100. If new SXSSFWorkbook(500) represents that the number of objects in memory reaches 500, it will be transferred to disk
//2. Create a worksheet
Sheet sheet = wb.createSheet();
//Setting the column width itself is a bug, and there will be some deviations
sheet.setColumnWidth(cellNo++, 26*256);
sheet.setColumnWidth(cellNo++, 11*256);
sheet.setColumnWidth(cellNo++, 29*256);
sheet.setColumnWidth(cellNo++, 12*256);
sheet.setColumnWidth(cellNo++, 15*256);
sheet.setColumnWidth(cellNo++, 10*256);
sheet.setColumnWidth(cellNo++, 10*256);
sheet.setColumnWidth(cellNo++, 8*256);
cellNo=1;//Reset
//3. Create row object
//=========================================Headline=============================
nRow = sheet.createRow(rowNo++);//Create row object
nRow.setHeightInPoints(36);//Set row height
nCell = nRow.createCell(cellNo);//Create cell object
//merge cell
sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));//Merge cells horizontally
//Set the contents of the cell
nCell.setCellValue(inputDate.replace("-0", "-").replace("-", "year")+"Monthly shipment table");//2015-01 2015-12
//Set cell style
nCell.setCellStyle(this.bigTitle(wb));
//=======================================Subtitle=================================
String titles[] = {"customer","order number","Article number","quantity","factory","Factory delivery date","Shipping date","Trade terms"};
//Create a row object for the subtitle
nRow = sheet.createRow(rowNo++);
nRow.setHeightInPoints(26.25f);//Set row height
//Create a cell object, set the content, and set the style
for(String title :titles){
nCell = nRow.createCell(cellNo++);//Create cell object
nCell.setCellValue(title);//Set content
nCell.setCellStyle(this.title(wb));//Set style
}
//=======================================Data output=================================================
String hql= "from ContractProduct where to_char(contract.shipTime,'yyyy-MM') ='"+inputDate+"'";
List<ContractProduct> list = contractProductService.find(hql, ContractProduct.class, null);//Query the list of goods that meet the specified shipping date
for(int i=1;i<=1000;i++){
for(ContractProduct cp :list){
nRow = sheet.createRow(rowNo++);//Generate data row
nRow.setHeightInPoints(24);//Set row height
cellNo=1;
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getContract().getCustomName());//Customer name
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getContract().getContractNo());//Order No. -- Contract No
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getProductNo()); // Article number
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getCnumber());// quantity
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getFactoryName());//Factory name
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getDeliveryPeriod()));//Delivery date
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getShipTime()));//Shipping date
nCell.setCellStyle(this.text(wb));//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getContract().getTradeTerms());//Trade terms
nCell.setCellStyle(this.text(wb));//Style text
}
}
//======================================Output to client (download)========================================
DownloadUtil downUtil = new DownloadUtil();
ByteArrayOutputStream baos = new ByteArrayOutputStream();//Cache in stream memory
wb.write(baos);//Output the contents of excel table to cache
baos.close();//Refresh cache
HttpServletResponse response = ServletActionContext.getResponse();//Get the response object
downUtil.download(baos, response, "Shipping list.xlsx");//If it is Chinese, garbled code may be generated during downloading. How to solve it?
return NONE;
}
//Style of headline
public CellStyle bigTitle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("Song typeface");
font.setFontHeightInPoints((short)16);
font.setBoldweight(Font.BOLDWEIGHT_BOLD); //Bold font
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER); //Horizontal center
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //Vertical center
return style;
}
//Style of subheading
public CellStyle title(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("Blackbody");
font.setFontHeightInPoints((short)12);
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER); //Horizontal center
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //Vertical center
style.setBorderTop(CellStyle.BORDER_THIN); //Upper thin line
style.setBorderBottom(CellStyle.BORDER_THIN); //Lower thin line
style.setBorderLeft(CellStyle.BORDER_THIN); //Left thin line
style.setBorderRight(CellStyle.BORDER_THIN); //Right thin line
return style;
}
//Text style
public CellStyle text(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("Times New Roman");
font.setFontHeightInPoints((short)10);
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_LEFT); //Horizontal left
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //Vertical center
style.setBorderTop(CellStyle.BORDER_THIN); //Upper thin line
style.setBorderBottom(CellStyle.BORDER_THIN); //Lower thin line
style.setBorderLeft(CellStyle.BORDER_THIN); //Left thin line
style.setBorderRight(CellStyle.BORDER_THIN); //Right thin line
return style;
}
8, Right direction
Template printing
1. Create template file (path of template file: / make / xlprint / toutproduct. XLS)
2. Import (load) the template file to get a workbook
3. Read the worksheet
4 read line
5. Read cells
6. Read cell style
7. Set cell content
8. Other cells can use the read style
tOUTPRODUCT.xls
OutProductAction.print()
//Print using template
public String print() throws Exception {
//General variable
int rowNo=0,cellNo=1;
Row nRow =null;
Cell nCell = null;
//1. Read Workbook
String path = ServletActionContext.getServletContext().getRealPath("/")+"/make/xlsprint/tOUTPRODUCT.xls";
System.out.println(path);
InputStream is = new FileInputStream(path);
Workbook wb = new HSSFWorkbook(is);
//2. Read the worksheet
Sheet sheet = wb.getSheetAt(0);
cellNo=1;//Reset
//3. Create row object
//=========================================Headline=============================
nRow = sheet.getRow(rowNo++);//Read row object
nCell = nRow.getCell(cellNo);
//Set the contents of the cell
nCell.setCellValue(inputDate.replace("-0", "-").replace("-", "year")+"Monthly shipment table");//2015-01 2015-12
//=======================================Subtitle=================================
rowNo++;
//=======================================Data output=================================================
nRow = sheet.getRow(rowNo);//Read the third line
CellStyle customCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle orderNoCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle productNoCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle cNumberCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle factoryCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle deliveryPeriodCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle shipTimeCellStyle = nRow.getCell(cellNo++).getCellStyle();
CellStyle tradeTermsCellStyle = nRow.getCell(cellNo++).getCellStyle();
String hql= "from ContractProduct where to_char(contract.shipTime,'yyyy-MM') ='"+inputDate+"'";
List<ContractProduct> list = contractProductService.find(hql, ContractProduct.class, null);//Query the list of goods that meet the specified shipping date
for(ContractProduct cp :list){
nRow = sheet.createRow(rowNo++);//Generate data row
nRow.setHeightInPoints(24);//Set row height
cellNo=1;
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getContract().getCustomName());//Customer name
nCell.setCellStyle(customCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getContract().getContractNo());//Order No. -- Contract No
nCell.setCellStyle(orderNoCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getProductNo()); // Article number
nCell.setCellStyle(productNoCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getCnumber());// quantity
nCell.setCellStyle(cNumberCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getFactoryName());//Factory name
nCell.setCellStyle(factoryCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getDeliveryPeriod()));//Delivery date
nCell.setCellStyle(deliveryPeriodCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getShipTime()));//Shipping date
nCell.setCellStyle(shipTimeCellStyle);//Style text
nCell = nRow.createCell(cellNo++);//Generate cell object
nCell.setCellValue(cp.getContract().getTradeTerms());//Trade terms
nCell.setCellStyle(tradeTermsCellStyle);//Style text
}
//======================================Output to client (download)========================================
DownloadUtil downUtil = new DownloadUtil();
ByteArrayOutputStream baos = new ByteArrayOutputStream();//Cache in stream memory
wb.write(baos);//Output the contents of excel table to cache
baos.close();//Refresh cache
HttpServletResponse response = ServletActionContext.getResponse();//Get the response object
downUtil.download(baos, response, "Shipping list.xls");//If it is Chinese, garbled code may be generated during downloading. How to solve it?
return NONE;
}
9, Homework
1. Reading process
2. Write later: export declaration module