/**
* Copyright (c) 2003-2004 System Integrator Corporation.
* All Rights Reserved.
*/
package jp.co.sint.servlet.mallmgr;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import jp.co.sint.basic.SILogin;
import jp.co.sint.beans.mallmgr.UISaleList;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIFlagConf;//7.2.0 ST0542 追加
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SICSVWrite;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加
import org.apache.log4j.Category;
/**
* @version $Id: SIRegSaleSrv,v 1.0 2003/12/15 Exp $
* @author yamauchi
*
Description:
* History
* Author Date Reason
* ============ ========== ===========================
* yamauchi 2003/12/15 Original
*/
public class SIRegSaleSrv extends SIServlet{
//ログ用のインスタンスの生成
private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
/**
* doUpdate
* HTTP リクエストの処理
* @param request リクエスト
* @param response
* @return なし
* @throws ServletException
* @throws IOException
*/
public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
SILogin manLogin=SIHTMLUtil.getLogin(request);
if (!manLogin.isLogin()){
forwardKey(request,response,"webshop.jsp.manager.login");
return;
}
HttpSession session=request.getSession(true);//セッションの取得
SIDatabaseConnection databaseConnection=new SIDatabaseConnection();//DBへのコネクションの作成
SIURLParameter urlParam = new SIURLParameter(request);//7.1.1 ST0236 追加
try {
String actionName=this.getActionName(urlParam);//画面からのアクション //7.1.2 ST0236 修正
String editMode=this.getEditMode(urlParam);//DBへの編集モード //7.1.2 ST0236 修正
SILogin lLogin=SIHTMLUtil.getLogin(request);
if (SIUtil.isNull(actionName)){
forwardKey(request,response,"webshop.jsp.manager.sale.list");
}else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)){//CSVファイルへの出力
UISaleList saleList=new UISaleList();
saleList=(UISaleList)session.getAttribute(SIConfig.SISESSION_MAN_SALE_LIST_NAME);
if (saleList==null) {
saleList=new UISaleList(request,urlParam);//7.1.2 ST0236 修正
//7.2.0 ST0542 追加 ここから
saleList.validate(request);
try{
saleList.getGroupByShop(databaseConnection.getConnection(),lLogin);
saleList.getGroupByCmdty(databaseConnection.getConnection(),lLogin);
}catch(Exception ex){}
//7.2.0 ST0542 追加 ここまで
}
this.produceCSVFile(lLogin,response,databaseConnection.getConnection(),saleList,getParameter(urlParam,"csvCodeTxt"));//7.1.2 ST0236 修正
if (!response.isCommitted()){
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("database.query.notexist","CSVデータ"));
forwardKey(request,response,"webshop.jsp.manager.sale.list");
}
}else if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){//一覧と検索などの画面のレコードの表示
UISaleList saleList=new UISaleList();
//データの取得
saleList=new UISaleList(request,urlParam);//7.1.2 ST0236 修正
//データのチェック
saleList.validate(request);
try{
int callFlg=1;
saleList.getGroupByShop(databaseConnection.getConnection(),lLogin);
saleList.getGroupByCmdty(databaseConnection.getConnection(),lLogin);
session.setAttribute(SIConfig.SISESSION_MAN_SALE_LIST_NAME,saleList);
forwardKey(request,response,"webshop.jsp.manager.sale.list");
}catch(Exception ex){}
}
}catch (SQLException e){
e.printStackTrace();
throw new ServletException();
}catch (NamingException e){
e.printStackTrace();
throw new ServletException();
}finally{
databaseConnection.close();
}
}
/**
* produceCSVFile
* CSVファイルを作成します。
* @param response クライアントまでのresponse
* @param lConnection DBへのコネクション
* @param lListCond CSVファイルに出力するデータセット
* @return なし
* @throws なし
*/
private void produceCSVFile(SILogin lLogin,HttpServletResponse response,Connection lConnection,UISaleList lsaleList,String lCsvCode){
//CSVファイル名の作成
String lCsvFileName=SICSVConf.getCsvFileName(SICSVConf.SICSV_ORDER_INX);
SICSVWrite lCsv=new SICSVWrite(lCsvFileName);
//SQL文の作成
StringBuffer lSqlBuf=new StringBuffer();
String lGroup=" ";
String lOrder="";
//ショップ別
if (Integer.parseInt(lCsvCode)==SICSVConf.SICSV_SALE_SHOP_M_INX || Integer.parseInt(lCsvCode)==SICSVConf.SICSV_SALE_SHOP_S_INX){
//7.2.0 ST0542 追加・修正 ここから
lSqlBuf.append("SELECT bb.SHOPCODE,bb.SHOPNAME "); //ショップコード、ショップ名
lSqlBuf.append(" ,SUM(bb.INTAXTOTAL) AS INTAXTOTAL,SUM(bb.OUTTAXTOTAL) AS OUTTAXTOTAL "); //売上金額（税込、税抜）
lSqlBuf.append(" ,COUNT(DISTINCT(aa.ORDERCODE)) AS ORDERTOTAL "); //受注件数
lSqlBuf.append(" ,TRUNC(SUM(bb.OUTTAXTOTAL) / COUNT(DISTINCT(aa.ORDERCODE))) AS CUSTUNITPRICE "); //単価
lSqlBuf.append(" ,SUM(dd.TOTALOFDELIVERYFEE) AS TOTALOFDELIVERYFEE "); //送料
lSqlBuf.append(" ,SUM(bb.WRAPPINGPRICEINCTAXTOTAL) AS WRAPPINGPRICEINCTAX "); //ラッピング金額
//受注データのとき
if (lsaleList.getDispFlgRdo().equals(SIFlagConf.SIFLAG_SALES_FLG_NAME[0][1])){
lSqlBuf.append(",SUM(aa.SUMBYPOINT) AS SUMBYPOINTTOTAL "); //ポイント使用額
}
lSqlBuf.append("FROM ");
lSqlBuf.append("ORDERSUMVW aa ");
lSqlBuf.append("INNER JOIN ( ");
//受注番号,ショップコードで集計した受注配送先TBL
lSqlBuf.append(" SELECT bbb.ORDERCODE,aaa.SHOPCODE,");
lSqlBuf.append(" SUM(CASE WHEN bbb.DELIVERYTAXFLG=1 THEN TRUNC(bbb.DELIVERYFEE * (1.0 + (bbb.TAXRATE / 100.0))) ELSE bbb.DELIVERYFEE END ) AS TOTALOFDELIVERYFEE ");
lSqlBuf.append(" FROM ORDERDELIVERYVW").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" bbb");
lSqlBuf.append(" INNER JOIN ORDERDETAILVW").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" aaa").append(" ON bbb.ORDERCODE = aaa.ORDERCODE AND bbb.DELIVERYCODE = aaa.DELIVERYCODE AND aaa.DETAILCODE = '1' ");
//出荷データのとき検索条件をセット
if(!lsaleList.getDispFlgRdo().equals(SIFlagConf.SIFLAG_SALES_FLG_NAME[0][1])){
lSqlBuf.append(" "+lsaleList.getShopCSVConditionSQL(lConnection));
}
lSqlBuf.append("GROUP BY bbb.ORDERCODE,aaa.SHOPCODE ");
lSqlBuf.append(")").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" dd").append(" ON").append(" aa.ORDERCODE = dd.ORDERCODE ");
lSqlBuf.append("INNER JOIN (");
//受注番号,ショップコードで集計した受注明細TBL
lSqlBuf.append(" SELECT aaa.ORDERCODE,aaa.SHOPCODE,aaa.SHOPNAME ");
lSqlBuf.append(" ,SUM(aaa.AMOUNT * aaa.PRICEINCTAX) AS INTAXTOTAL ");
lSqlBuf.append(" ,SUM(CASE WHEN aaa.TAXFLG = '2' THEN aaa.AMOUNT * CEIL(aaa.PRICE / (1.0 + aaa.TAXRATE / 100.0)) ELSE aaa.AMOUNT * aaa.PRICE END ) AS OUTTAXTOTAL ");
lSqlBuf.append(" ,SUM(aaa.AMOUNT * aaa.WRAPPINGPRICEINCTAX) AS WRAPPINGPRICEINCTAXTOTAL ");
lSqlBuf.append(" FROM ORDERDETAILVW").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" aaa");
lSqlBuf.append(" INNER JOIN ORDERDELIVERYVW ").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" bbb").append(" ON aaa.ORDERCODE = bbb.ORDERCODE AND aaa.DELIVERYCODE = bbb.DELIVERYCODE ");
//出荷データのとき検索条件をセット
if(!lsaleList.getDispFlgRdo().equals(SIFlagConf.SIFLAG_SALES_FLG_NAME[0][1])){
lSqlBuf.append(" "+lsaleList.getShopCSVConditionSQL(lConnection));
}
lSqlBuf.append(" GROUP BY aaa.ORDERCODE,aaa.SHOPCODE,aaa.SHOPNAME ");
lSqlBuf.append(")").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ").append(" ON dd.ORDERCODE = bb.ORDERCODE AND dd.SHOPCODE = bb.SHOPCODE ");
lSqlBuf.append(" WHERE aa.STATUS=").append(SIDBUtil.SQL2Str(SIConfig.SIORDER_STATUS_FLG_ORDER," ")); //受注状態
lSqlBuf.append(" AND bb.ShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
//受注データのとき検索条件をセット
if(lsaleList.getDispFlgRdo().equals(SIFlagConf.SIFLAG_SALES_FLG_NAME[0][1])){
lSqlBuf.append(" "+lsaleList.getShopCSVConditionSQL(lConnection));
}
lSqlBuf.append("GROUP BY bb.SHOPCODE,bb.SHOPNAME ");
lSqlBuf.append(" ORDER BY ShopCode ASC ");
if (lsaleList.getDispFlgRdo().equals(SIFlagConf.SIFLAG_SALES_FLG_NAME[0][1])){
//受注データで集計のとき
lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_SALE_SHOP_S_INX));//タイトルの設定
lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_SALE_SHOP_S_INX));//ファイル名の設定
if (SIDBMultiConf.SIDB_CURRENT_INX !=SIDBMultiConf.SIDB_POSTGRESQL_INX){
lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_SALE_SHOP_S_INX));
}
}else{
//出荷データで集計のとき
lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_SALE_SHOP_M_INX));//タイトルの設定
lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_SALE_SHOP_M_INX));//ファイル名の設定
if (SIDBMultiConf.SIDB_CURRENT_INX !=SIDBMultiConf.SIDB_POSTGRESQL_INX){
lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_SALE_SHOP_M_INX));
}
}
//7.2.0 ST0542 追加・修正 ここまで
//商品別
}else if (Integer.parseInt(lCsvCode)==SICSVConf.SICSV_SALE_CMDTY_INX){
lSqlBuf.append("SELECT bb.ShopCode,bb.ShopName,bb.CmdtyCode,bb.CmdtyName,SUM(bb.amount) AS Amount");
//7.2.0 ST0259 追加 ここから
//規格商品
lSqlBuf.append(",bb.StndrdCode1,bb.ElementCode1,ff1.ElementName AS ElementName1");
lSqlBuf.append(",bb.StndrdCode2,bb.ElementCode2,ff2.ElementName AS ElementName2");
//7.2.0 ST0259 追加 ここまで
//7.2.0 ST0542 追加・修正 ここから
lSqlBuf.append(",SUM(bb.Amount*bb.PriceIncTax) AS InTaxTotal "); //合計金額（税込）
lSqlBuf.append(",SUM(CASE WHEN bb.TaxFlg ='2' THEN bb.Amount * CEIL(bb.Price/(1.0+bb.TaxRate/100.0)) ELSE bb.amount * bb.Price END) AS OutTaxTotal ");//合計金額（税抜）
lSqlBuf.append(",COUNT(DISTINCT(aa.OrderCode)) AS OrderTotal "); //受注件数
lSqlBuf.append(",TRUNC((SUM(CASE WHEN bb.TaxFlg ='2' THEN bb.Amount * CEIL(bb.Price/(1.0+bb.TaxRate/100.0)) ELSE bb.amount * bb.Price END))/COUNT(DISTINCT(aa.OrderCode))) AS CustUnitPrice ");//客単価
lSqlBuf.append(",SUM(bb.Amount*bb.WrappingPriceIncTax) AS WrappingPriceTotal "); //ラッピング金額
lSqlBuf.append(" FROM OrderSumVW").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" aa");
lSqlBuf.append(" INNER JOIN OrderDetailVW").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" bb").append(" ON bb.OrderCode = aa.OrderCode");
lSqlBuf.append(" INNER JOIN CmdtymTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" cc").append(" ON cc.CmdtyCode=bb.CmdtyCode AND cc.ShopCode=bb.ShopCode");
//7.2.0 ST1030 追加 ここから
lSqlBuf.append(" INNER JOIN ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" dd").append(" ON dd.OrderCode=aa.OrderCode AND bb.DeliveryCode=dd.DeliveryCode");
//lSqlBuf.append(" INNER JOIN OrderDeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" dd").append(" ON dd.OrderCode=aa.OrderCode AND bb.DeliveryCode=dd.DeliveryCode");
//7.2.0 ST1030 追加 ここまで
//7.2.0 ST0259 追加 ここから
lSqlBuf.append(" LEFT OUTER JOIN StndrdNamemTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" ee1").append(" ON ee1.ShopCode=bb.ShopCode AND ee1.StndrdCode=bb.StndrdCode1 ");//規格名称Ｍ
lSqlBuf.append(" LEFT OUTER JOIN StndrdNamemTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" ee2").append(" ON ee2.ShopCode=bb.ShopCode AND ee2.StndrdCode=bb.StndrdCode2 ");
lSqlBuf.append(" LEFT OUTER JOIN StndrdContentmTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" ff1").append(" ON ff1.ShopCode=bb.ShopCode AND ff1.StndrdCode=bb.StndrdCode1 AND ff1.ElementCode=bb.ElementCode1 ");//規格内容Ｍ
lSqlBuf.append(" LEFT OUTER JOIN StndrdContentmTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" ff2").append(" ON ff2.ShopCode=bb.ShopCode AND ff2.StndrdCode=bb.StndrdCode2 AND ff2.ElementCode=bb.ElementCode2 ");
//7.2.0 ST0259 追加 ここまで
lSqlBuf.append(" WHERE aa.status=").append(SIDBUtil.SQL2Str(SIConfig.SIORDER_STATUS_FLG_ORDER," ")); //受注状態 //7.2.0 ST0259 修正
lSqlBuf.append(" AND bb.ShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
lSqlBuf.append(lsaleList.getConditionSQL());
//合計
lSqlBuf.append("GROUP BY bb.ShopCode,bb.ShopName,bb.CmdtyCode,bb.CmdtyName,bb.StndrdCode1,bb.ElementCode1,bb.StndrdCode2,bb.ElementCode2,ee2.StndrdName,ee1.StndrdName,ff1.ElementName,ff2.ElementName ");
//ソート
lSqlBuf.append("ORDER BY bb.ShopCode ASC,bb.CmdtyCode ASC ");
//7.2.0 ST0542 追加・修正 ここまで
lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_SALE_CMDTY_INX));//タイトルの設定
lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_SALE_CMDTY_INX));//ファイル名の設定
if (SIDBMultiConf.SIDB_CURRENT_INX !=SIDBMultiConf.SIDB_POSTGRESQL_INX){
lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_SALE_CMDTY_INX));
}
}
log.debug("produceCSVFile:lSqlBuf="+lSqlBuf.toString());
//SQL文の設定
lCsv.setSqlStatement(lSqlBuf.toString());
//CSVファイルの出力
lCsv.execute(lConnection,response);
}
}