//表样分配规则insert load
public final class SsSheetAllotDao {
private static final String SQL_INSERT = "INSERT INTO SSSHEETALLOT(SSKeyID,SSSheetId)VALUES(?,?)";private static final String SQL_UPDATE = "UPDATE SSSHEETALLOT SET SSSheetId=? WHERE SSKeyID=?";
private static final String SQL_DELETE = "DELETE FROM SSSHEETALLOT WHERE SSSheetId=?";
public static final String SQL_QUERY = "SELECT a.sskeyid||a.sssheetid AS F_ID,a.sskeyid,a.sssheetid,t.strsscustomertypename,e.strsssheetname FROM SSSHEETALLOT a " +
"left join sscustomertype t on a.sskeyid=t.lngsscustomertypeid " +
"left join sssheet e on a.sssheetid=e.sssheetid";
private static final String SQL_INSERT_F = "insert into ssfileprop (ssfileid,sspropdefineid,strvalue,lngvalueid) values (?,?,?,?)";
private static final String SQL_DELETE_F = "delete from ssfileprop where ssfileid=?";
//数据库引擎
private static DbEngine dbEngine;
static {
dbEngine = DbUtils.getDb();
}
//插入到数据库
/**
*
* @param bean
* @param fileid ssfile.ssfileid
* @param menu 页面的单选按钮 可用表单 分配表单
* @param typesheetid 单位类型的ID和表样类型的ID 组合的字符串
* such as 101:83d1,3851,8352,|104:83d1,3851,|
* @throws Exception
*/
public static void insert(SsSheetAllotBean bean, String fileid, String menu, String typesheetid) throws Exception {
dbEngine.beginTrans();
try {
String sspropdefineid = "";
if (menu == "0" && "0".equals(menu)) {
sspropdefineid = getPropdefineid("可用全部表单");
} else {
String customertypeid = "";
sspropdefineid = getPropdefineid("表单分配准则");
String[] tempid = typesheetid.split("\\|");
for (int k = 0; k < tempid.length; k++) {
String[] typeid = tempid[k].split("\\:");
customertypeid = typeid[0];
String[] sheetid = typeid[1].split(",");
for (int j = 0; j < sheetid.length; j++) {
if (null != sheetid[j] && !"".equals(sheetid[j]) && !"-".equals(sheetid[j])) {
dbEngine.update(SQL_INSERT, customertypeid, sheetid[j]);
}
}
}
dbEngine.update(SQL_INSERT_F, fileid, sspropdefineid, customertypeid, 0);
}
dbEngine.commit();
} catch (Exception e) {
dbEngine.rollback(e);
}
}
//更新数据库
public static void update(SsSheetAllotBean bean, String fileid, String menu, String typesheetid) throws Exception {
dbEngine.beginTrans();
try {
dbEngine.update(SQL_DELETE_F, fileid);
String sspropdefineid = "";
String customertypeid = "";
if (menu == "0" && "0".equals(menu)) {
sspropdefineid = getPropdefineid("可用全部表单");
} else {
sspropdefineid = getPropdefineid("表单分配准则");
String[] tempid = typesheetid.split("\\|");
for (int k = 0; k < tempid.length; k++) {
String[] typeid = tempid[k].split("\\:");
customertypeid = typeid[0];
String[] sheetid = typeid[1].split("\\,");
for (int j = 0; j < sheetid.length; j++) {
if (null != sheetid[j] && !"".equals(sheetid[j]) && !"-".equals(sheetid[j])) {
dbEngine.update(SQL_DELETE, sheetid[j]);
dbEngine.update(SQL_INSERT, customertypeid, sheetid[j]);
}
}
}
dbEngine.update(SQL_INSERT_F, fileid, sspropdefineid, customertypeid, 0);
}
dbEngine.commit();
} catch (Exception e) {
dbEngine.rollback(e);
}
}
//删除
public static void delete(String id) throws Exception {
dbEngine.beginTrans();
try {
dbEngine.update(SQL_DELETE_F, id);
dbEngine.query("select sssheetid from sssheet where ssfileid = ?",
new IResultSetExtractor<String>() {
public String extractData(ResultSet resultSet) throws Exception {
UtilRs urs = new UtilRs(resultSet);
while (urs.next()) {
dbEngine.update(SQL_DELETE, urs.getStringIgnoreNull("sssheetid"));
}
return null;
}
}, id);
dbEngine.commit();
} catch (Exception e) {
dbEngine.rollback(e);
}
}
//加载指定id的bean
public static SsSheetAllotBean load(String sId) throws Exception {
return dbEngine.query(SQL_QUERY + " where e.ssfileid=? ",
new IResultSetExtractor<SsSheetAllotBean>() {
public SsSheetAllotBean extractData(ResultSet resultSet) throws SQLException {
UtilRs urs = new UtilRs(resultSet);
String resutl = "";
String temp;
ArrayList list=new ArrayList();
Iterator it = null;
while (urs.next()) {
resutl = urs.getStringIgnoreNull("sssheetid");
temp = urs.getStringIgnoreNull("sskeyid");
boolean pd=true;
for(int i=0;i<list.size();i++){
if(list.get(i).toString().indexOf(temp+":")==0){
list.set(i,list.get(i).toString()+","+resutl);
pd=false;
break;
}
}
if(pd){
list.add(temp+":"+resutl);
}
}
it = list.iterator();
resutl="";
while(it.hasNext()){
String entry=(String)it.next();
resutl+=entry+"|";
}
SsSheetAllotBean bean = new SsSheetAllotBean();
bean.setTypesheetid(resutl);
return bean;
}
}, sId);
}
//加载所有bean
public static void loadAll(final IEnumWorker<SsSheetAllotBean> worker) throws Exception {
dbEngine.query(SQL_QUERY,
new IResultSetExtractor<SsSheetAllotBean>() {
public SsSheetAllotBean extractData(ResultSet resultSet) throws SQLException {
UtilRs urs = new UtilRs(resultSet);
try {
while (urs.next()) {
SsSheetAllotBean node = new SsSheetAllotBean();
node.readFromRs(urs);
worker.doWork(node);
}
} catch (Exception e) {
throw new SQLException(e);
}
return null;
}
});
}
protected static String getPropdefineid(String code) throws Exception {
return dbEngine.query("select sspropdefineid from sspropdefine where strpropcode = ?",
new IResultSetExtractor<String>() {
public String extractData(ResultSet resultSet) throws SQLException {
UtilRs urs = new UtilRs(resultSet);
if (urs.next()) {
return urs.getStringIgnoreNull("sspropdefineid");
}
return null;
}
}, code);
}
}