dbsql.java

一个优秀的干洗店管理系统

package dao.common.sql;

public interface DbSql {
	public static final String BETWEEN = "between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd')";
	/**
	 * 查找会员消费记录
	 */
	public static final String VIP_CONSUME_RECORDER = "select * from vip_consume where consume_date "+ BETWEEN + " and vip_id <> 0";
	/**
	 * 当查找时输入了名字，就要加上这一句
	 */
	public static final String WITH_NAME = "and vip_id = (select vip_id from vip where vip_name = ?)";
	/**
	 * 通过交易单号查找交易单信息
	 */
	public static final String GET_ORDERINFO_BY_ORDERID = "select * from oneOrder where order_id = ?";
	/**
	 * 
	 */
	public String GET_VIPCARDVO_BY_ID = "select * from vip_card where vip_id = ?";


	/**
	 * 通过会员ID查找会员信息
	 */
	public static final String GET_VIOINFO_BY_VIPID = "select * from vip where vip_id = ? ";

	/**
	 * 查找会员充值记录
	 */

	public static final String GET_VIPCHARGE_RECORDER = "select * from vip_charge where charge_date "+BETWEEN+" and vip_id <> 0 ";		

	/**
	 * 查找收取衣物汇总
	 */
	public static final String GET_CLOTHESIN_GETHER = "select * from oneOrder where in_date "+BETWEEN;
//			+ "where in_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') ";

	/**
	 * 当选了操作员之后，要加上这一句
	 */
	public static final String WITH_OPERATOR = " and operator_name = ?";

	/**
	 * 当选了交易单号之后，要加上这一句
	 */
	public static final String WITH_ORDERID = " and order_id = ? ";

	/**
	 * 通过会员编号查找会员消费信息
	 */
	public static final String GET_VIPCONSUMEINFO_BY_ORDERID = "select * from vip_consume where order_id = ?  ";

	/**
	 * 通过交易单号查找本交易单的所有衣服信息
	 */
	public static final String GET_ORDERITEMINFO_BY_ORDERID = "select * from order_item where order_id = ? ";

	/**
	 * 通过衣服编号查找衣服类型
	 */
	public static final String GET_CLOTHESTYPE_BY_CLOTHESID = "select * from clothes_type where clothes_id = ?";

	/**
	 * 通过会员编号查找会员折扣
	 */
	public static final String GET_DISCOUNT_BY_VIPID = "select vip_discount from vip_card_level " +
						"where vip_level = (select vip_level from vip_card where vip_id = ? )";
	
	/**
	 * 查找索赔衣物信息
	 */
	public static final String GET_REFUNDMENT_INFO = "select * from refundment where refund_date " +BETWEEN;
//			"where refund_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd')";
	/**
	 * 查找要重洗的衣物的信息
	 */
	public static final String GET_REWASHED_INFO = "select * from rewash where require_rewash_date " +BETWEEN;
//						"where require_rewash_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') ";
	/**
	 * 通过交易单号查找会员编号
	 */
	public static final String GET_VIPID_BY_ORDERID = "select vip_id from vip_consume where order_id = ? ";
	/**
	 * 查找过期未取走的衣物信息
	 */
	public static final String GET_OUTOFDATE_ORDERID = "select order_id from oneOrder where take_or_not = '否' and out_date_prodicted " + BETWEEN;
	/**
	 * 获取衣服编号数组成
	 */
	public static final String GET_CLOTHESIDS = "select clothes_id from order_item where order_id in (select order_id from oneOrder where in_date "+BETWEEN+") group by clothes_id";
	//select clothes_id from order_item group by clothes_id
	//select clothes_id from order_item where order_id in (select order_id from oneOrder where in_date < to_date('2009-03-28','yyyy-mm-dd' )) group by clothes_id
	/**
	 * 通过衣服编号获取某类衣物数量统计
	 */
	public static final String GET_CLOTHESINCOUNT_BY_CLOTHESID = "select sum(clothes_quantity) ,clothes_id from order_item group by clothes_id having clothes_id = ?";
	/**
	 * 通过衣服编号获取该类衣服的总收入
	 */
	public static final String GET_INCOMECOUNT_BY_CLOTHESID = "select sum(order_item_value),clothes_id from order_item group by clothes_id having clothes_id = ?";
	/**
	 * 查找会员充值收入总额
	 */
	public static final String GET_VIPCHARGEINCONE = "select sum(charge_amount) from vip_charge where charge_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd')";
	/**
	 * 查找未支付金额总额
	 */
//	public static final String GET_UNPAYCOUNT = "select sum(needed_amount - received_amount - reduced_amount) from vip_consume where consume_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd')";
	/*public static final String GET_UNPAYCOUNT = "select sum(received_amount) from vip_consume " +
			"where consume_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') " +
			"and order_id = (select order_id from oneOrder where paid_or_not = '否') ";*/
	public static final String GET_UNPAYCOUNT = "select sum(order_value) from (select order_value from oneOrder where paid_or_not = '否' and in_date " + BETWEEN+")";
	/**
	 * 查找会员消费总额
	 */
	/*public static final String GET_VIPCONSUME_AMOUNT = "select sum(received_amount) from vip_consume " +
			"where consume_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') " +
			"and order_id = (select order_id from oneOrder where paid_or_not = '是' and vip_id <> 0) ";*/
	public static final String GET_VIPCONSUME_AMOUNT ="select sum(order_value) from (select order_value from oneOrder where paid_or_not = '是' and vip_id <> 0 and in_date " + BETWEEN+")";
	/**
	 * 普通用户消费金额
	 */
	/*public static final String GET_NONVIPCONSUME_AMOUNT = "select sum(received_amount) from vip_consume " +
	"where consume_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') " +
	"and order_id = (select order_id from oneOrder where paid_or_not = '是' and vip_id = 0) ";*/
	public static final String GET_NONVIPCONSUME_AMOUNT ="select sum(order_value) from (select order_value from oneOrder where paid_or_not = '是' and vip_id = 0 and in_date " + BETWEEN+")";
	/**
	 * 赔偿衣物金额
	 */
	public static final String GET_REFUNDMENT_AMOUNT = "select sum(refund_amount) from refundment where refund_date " + BETWEEN;
	
	public static final String GET_ORDERID_COMMON = "select order_id from oneOrder ";
	/**
	 * 查找未支付定单的交易单号
	 */
	public static final String GET_UNPAYIDS = GET_ORDERID_COMMON+"where paid_or_not = '否' and  in_date " + BETWEEN ;
	/**
	 * 查找会员消费记录的会员编号
	 */
	public static final String GET_VIPCONSUMEIDS = GET_ORDERID_COMMON + "where vip_id <> 0 and paid_or_not = '是' and in_date " + BETWEEN;
	/**
	 * 普通用户消费的交易单号
	 */
	public static final String GET_NUNVIPCONSUMEORDERIDS = GET_ORDERID_COMMON + "where vip_id = 0 and in_date " + BETWEEN;
	

	/**
	 * 按日统计收入
	 */
//	public static final String COUNT_BY_DAY = "select to_char(consume_date,'yyyy-mm-dd') ,sum(received_amount) from (select consume_date , received_amount from vip_consume where consume_date "+BETWEEN+") group by to_char(consume_date,'yyyy-mm-dd')";
	public static final String COUNT_BY_DAY = "select to_char(consume_date,'yyyy-mm-dd') ,sum(received_amount) from (select consume_date , received_amount from vip_consume where consume_date "+ BETWEEN+") group by to_char(consume_date,'yyyy-mm-dd')";
	/**
	 * 按月统计收入
	 */
	 public static final String COUNT_BY_MONTH = "select to_char(consume_date,'yyyy-mm') ,sum(received_amount) from (select consume_date , received_amount from vip_consume where consume_date "+ BETWEEN+") group by to_char(consume_date,'yyyy-mm')";

	 /**
	  * 按年统计收入
	  */
	 public static final String COUNT_BY_YEAR = "select to_char(consume_date,'yyyy') ,sum(received_amount) from (select consume_date , received_amount from vip_consume where consume_date "+ BETWEEN+") group by to_char(consume_date,'yyyy')";
	 /**
	  * 按服务类型统计收入
	  */
	 public static final String COUNT_BY_TYPE = "select service_type,sum(order_item_value) from " +
	 		"(select a.clothes_id,order_item_value,service_type from order_item a,clothes_type b " +
	 		"where a.clothes_id = b.clothes_id and a.order_id in (select order_id from oneOrder where in_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd')))" +
	 		"group by service_type ";
}