package com.dut.logic.logic;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.dut.dao.BatchDBConnectionUtil;
import com.dut.logic.db.Hagakicd;
import com.dut.logic.db.Syouhin;
import com.dut.logic.db.Toukyucd;
import com.dut.logic.util.StringUtil;
import com.dut.struts.form.SyouhinInputActionForm;
import com.dut.struts.form.SyouhinUpdateActionForm;

public class SyouhinLogic {
	static int first = 0;

	static int last = 10;

	static int maxrow = 0;

	static int jiannum;

	static String sql;

	static String[] yeshu;

	static String[] dengji;

	static public ArrayList radioname(int i) {
		ArrayList Al = new ArrayList();
		BatchDBConnectionUtil BDB1 = new BatchDBConnectionUtil();
		BatchDBConnectionUtil BDB2 = new BatchDBConnectionUtil();
		BatchDBConnectionUtil BDB3 = new BatchDBConnectionUtil();
		StringUtil SU = new StringUtil();
		int a=0;
		int b=0;
		String sql1="select count(*) from Hagakicd";
		String sql2="select count(*) from Toukyucd";
		ResultSet rs1 = BDB2.executeQuery(sql1);
		ResultSet rs2 = BDB3.executeQuery(sql2);
		try {
			while(rs1.next()){
				a=Integer.parseInt(rs1.getString(1));
				break;
			}
			while(rs2.next()){
				b=Integer.parseInt(rs2.getString(1));
				break;
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		if (i == 1) {
			String sql = "select HAGAKI_MEI,HAGAKI_ID from Hagakicd ORDER BY HAGAKI_ID ASC";
			yeshu = new String[a];
			ResultSet rs = BDB1.executeQuery(sql);
			try {
				a=0;
				while (rs.next()) {
					String H_mei = SU.trans_date(rs.getString("HAGAKI_MEI"));
					yeshu[a]=H_mei;
					int H_id = rs.getInt("HAGAKI_ID");
					Hagakicd Ha = new Hagakicd();
					Ha.setHagaki_mei(H_mei);
					Ha.setHagaki_id(H_id);
					Al.add(Ha);
					a++;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			BDB1.close();
			return Al;
		}

		if (i == 2) {
			dengji = new String[b];
			String sql = "select toukyu_id,toukyu_mei from Toukyucd ORDER BY toukyu_id ASC";
			ResultSet rs = BDB1.executeQuery(sql);
			try {
				b=0;
				while (rs.next()) {
					String T_mei = SU.trans_date(rs.getString("toukyu_mei"));
					dengji[b]=T_mei;
					int T_id = rs.getInt("toukyu_id");
					Toukyucd tou = new Toukyucd();
					tou.setToukyu_id(T_id);
					tou.setToukyu_mei(T_mei);
					Al.add(tou);
					b++;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			BDB1.close();
		}
		return Al;
	}

	static public ArrayList SelectDatabase(SyouhinInputActionForm form) {

		ArrayList Al = new ArrayList();
		StringUtil SU = new StringUtil();
		BatchDBConnectionUtil BD = new BatchDBConnectionUtil();
		String s_year = SU.trans_date(form.getS_year());
		String yeshu = SU.trans(form.getYeshu());
		int nen = Integer.parseInt(s_year);
		int hagaki_id = Integer.parseInt(yeshu);
		String sqlpage;
		sql = "select * from SYOUHIN where nen=" + nen + " and hagaki_id="
				+ hagaki_id;
		String row = "select count(*) from SYOUHIN where nen=" + nen
				+ " and hagaki_id=" + hagaki_id;
		System.out.println(sql);

		if (form.getDengji() != null && !form.getDengji().trim().equals("")) {
			int toukyu_id = Integer.parseInt(SU.trans(form.getDengji()));
			String sqllast = " and toukyu_id= " + toukyu_id;
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_sp() != null && !form.getS_sp().trim().equals("")) {
			int syouhin_id = Integer.parseInt(SU.trans(form.getS_sp()));
			String sqllast = " and syouhin_id= " + syouhin_id;
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_spname() != null && !form.getS_spname().trim().equals("")) {
			String sqllast = " and syouhin_mei= '"
					+ SU.trans(form.getS_spname()) + "'";
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_spnamel() != null
				&& !form.getS_spnamel().trim().equals("")) {
			String sqllast = " and syouhin_ryaku= '"
					+ SU.trans(form.getS_spnamel()) + "'";
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_fl() != null && !form.getS_fl().trim().equals("")) {
			String sqllast = " and bunrui= '" + SU.trans(form.getS_fl()) + "'";
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_qyyz() != null && !form.getS_qyyz().trim().equals("")) {
			int gyousya_id = Integer.parseInt(SU.trans(form.getS_qyyz()));
			String sqllast = " and gyousya_id= " + gyousya_id;
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_qyfh() != null && !form.getS_qyfh().trim().equals("")) {
			String sqllast = " and keiyaku_cd= '" + SU.trans(form.getS_qyfh())
					+ "'";
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_ddbs() != null && !form.getS_ddbs().trim().equals("")) {
			int tyoutatusu = Integer.parseInt(SU.trans(form.getS_ddbs()));
			String sqllast = " and tyoutatusu= " + tyoutatusu;
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getDuix() != null && !form.getDuix().trim().equals("")) {
			int jitaku = Integer.parseInt(SU.trans(form.getDuix()));
			String sqllast = " and jitaku= " + jitaku;
			sql = sql + sqllast;
			row = row + sqllast;
		}
		if (form.getS_by() != null && !form.getS_by().trim().equals("")) {
			int nissu = Integer.parseInt(SU.trans(form.getS_by()));
			String sqllast = " and nissu= " + nissu;
			sql = sql + sqllast;
			row = row + sqllast;
		}
		sqlpage = sql + " limit " + first + "," + last;
		ResultSet rs = BD.executeQuery(row);
		try {
			while (rs.next()) {
				maxrow = Integer.parseInt(rs.getString(1));
				break;
			}
			// rs1.last();
			// maxrow = rs1.getRow();
			if (maxrow >= last) {
				jiannum = 10;
			} else {
				jiannum = maxrow;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Al = SyouhinLogic.fanye(sql, first, last, maxrow, jiannum, 3);
		BD.close();

		return Al;
	}

	static public String getnum(int i) {
		if (i == 1) {
			String firstnum = String.valueOf(first + 1);
			return firstnum;
		} else if (i == 2) {
			String lastnum = String.valueOf(last);
			return lastnum;
		} else if (i == 3) {
			String maxrownum = String.valueOf(maxrow);
			return maxrownum;
		} else if (i == 4) {
			return sql;
		} else if (i == 5) {
			String Jiannum = String.valueOf(jiannum);
			return Jiannum;
		} else {
			return null;
		}
	}

	static public ArrayList fanye(String sql, int firstcs, int lastcs,
			int maxrowcs, int jiannumcs, int i) {
		ArrayList Al = new ArrayList();
		StringUtil SU = new StringUtil();
		BatchDBConnectionUtil BD = new BatchDBConnectionUtil();
		String sqlpage;
		if (i == 1) {
			if ((firstcs - 1) <= 0) {
				first = 0;
				last = 10;
				jiannum = jiannumcs;
			} else {
				first = firstcs - 11;
				last = lastcs - 10;
				if (first == 0 && last == 10) {
					jiannum = 10;
				} else {
					jiannum = jiannumcs - 10;
				}
			}
		}
		if (i == 2) {
			if (lastcs >= maxrowcs) {
				jiannum = maxrowcs;
				first = firstcs - 1;
			} else {
				first = firstcs + 9;
				last = lastcs + 10;
				if (last >= maxrowcs) {
					jiannum = maxrowcs;
				} else {
					jiannum = jiannumcs + 10;
				}
			}
		}
		if (i == 3) {
			first = firstcs;
			last = lastcs;
			jiannum = jiannumcs;
			maxrow = maxrowcs;
		}
		sqlpage = sql + " limit " + first + "," + last;
		System.out.println(sqlpage);
		ResultSet rs = BD.executeQuery(sqlpage);
		try {
			while (rs.next()) {
				Syouhin syh = new Syouhin();
				int ye = Integer.parseInt(rs.getString("hagaki_id"));
				int deng = Integer.parseInt(rs.getString("toukyu_id"));
				int duixiang = Integer.parseInt(rs.getString("jitaku"));
				syh.setHagaki_idstring(yeshu[ye-1]);
				syh.setToukyu_idstring(dengji[deng-1]);
				if (duixiang == 0) {
					syh.setJitakustring("?配送");
				} else if (duixiang == 1) {
					syh.setJitakustring("自局窓口");
				}
				if (rs.getString("hassousu") != null
						&& !rs.getString("hassousu").equals("")) {
					syh.setHassousu(Integer.parseInt(rs.getString("hassousu")));
				} else {
					syh.setHassousu(0);
				}
				if (rs.getString("hikikaesu") != null
						&& !rs.getString("hikikaesu").equals("")) {
					syh.setHikikaesu(Integer
							.parseInt(rs.getString("hikikaesu")));
				} else {
					syh.setHikikaesu(0);
				}
				if (rs.getString("nissu") != null
						&& !rs.getString("nissu").equals("")) {
					syh.setNissu(Integer.parseInt(rs.getString("nissu")));
				} else {
					syh.setNissu(0);
				}
				if (rs.getString("bunrui") != null
						&& !rs.getString("bunrui").equals("")) {
					syh.setBunrui(SU.trans_date(rs.getString("bunrui")));
				} else {
					syh.setBunrui("");
				}
				syh.setNen(Integer.parseInt(rs.getString("nen")));
				syh.setHagaki_id(ye);
				syh.setToukyu_id(deng);
				syh.setJitaku(duixiang);
				syh.setGyousya_id(Integer.parseInt(rs.getString("gyousya_id")));
				syh.setKeiyaku_cd(rs.getString("keiyaku_cd"));
				syh.setSyouhin_id(Integer.parseInt(rs.getString("syouhin_id")));
				syh.setSyouhin_mei(SU.trans_date(rs.getString("syouhin_mei")));
				syh.setSyouhin_ryaku(SU.trans_date(rs.getString("syouhin_ryaku")));
				syh.setTyoutatusu(Integer.parseInt(rs.getString("tyoutatusu")));
				syh.setInsert_date(rs.getDate("insert_date"));
				syh.setUpdate_date(rs.getDate("update_date"));
				Al.add(syh);
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BD.close();
		return Al;
	}

	static public Object ZhuanHuan(SyouhinUpdateActionForm form, int i) {
		Syouhin syh = new Syouhin();
		StringUtil SU = new StringUtil();
		if (i == 1) {
			syh.setNen(Integer.parseInt(SU.trans(form.getNen())));
			syh.setHagaki_id(Integer.parseInt(SU.trans(form.getHagaki_id())));
			syh.setHagaki_idstring(SU.trans(form.getHagaki_idstring()));
			syh.setToukyu_id(Integer.parseInt(SU.trans(form.getToukyu_id())));
			syh.setToukyu_idstring(SU.trans(form.getToukyu_idstring()));
			syh.setJitaku(Integer.parseInt(SU.trans(form.getJitaku())));
			syh.setJitakustring(SU.trans(form.getJitakustring()));
			syh.setBunrui(SU.trans(form.getBunrui()));
			syh.setGyousya_id(Integer.parseInt(SU.trans(form.getGyousya_id())));
			syh.setHassousu(Integer.parseInt(SU.trans(form.getHassousu())));
			syh.setHikikaesu(Integer.parseInt(SU.trans(form.getHikikaesu())));
			syh.setKeiyaku_cd(SU.trans(form.getKeiyaku_cd()));
			syh.setNissu(Integer.parseInt(SU.trans(form.getNissu())));
			syh.setSyouhin_id(Integer.parseInt(SU.trans(form.getSyouhin_id())));
			syh.setSyouhin_mei(SU.trans(form.getSyouhin_mei()));
			syh.setSyouhin_ryaku(SU.trans(form.getSyouhin_ryaku()));
			syh.setTyoutatusu(Integer.parseInt(SU.trans(form.getTyoutatusu())));
			syh.setInsert_date(InputCheckUtil.Turn_Date(form.getInsert_date()));
			syh.setUpdate_date(InputCheckUtil.Turn_Date(form.getUpdate_date()));
		}
		if (i == 2) {
			int duixiang = Integer.parseInt(SU.trans(form.getJitaku()));
			syh.setSyouhin_mei(SU.trans(form.getSyouhin_mei()));
			syh.setSyouhin_ryaku(SU.trans(form.getSyouhin_ryaku()));
			syh.setJitaku(duixiang);
			syh.setNissu(Integer.parseInt(SU.trans(form.getNissu())));
			syh.setBunrui(SU.trans(form.getBunrui()));
			syh.setGyousya_id(Integer.parseInt(SU.trans(form.getGyousya_id())));
			syh.setKeiyaku_cd(SU.trans(form.getKeiyaku_cd()));
			syh.setTyoutatusu(Integer.parseInt(SU.trans(form.getTyoutatusu())));
			if (duixiang == 0) {
				syh.setJitakustring("配送??? ");
			} else if (duixiang == 1) {
				syh.setJitakustring("自局窓口???ǒ???");
			}
		}
		return syh;
	}

	static public void Delete(Syouhin sys) {
		BatchDBConnectionUtil BD = new BatchDBConnectionUtil();
		int nen = sys.getNen();
		int hagaki_id = sys.getHagaki_id();
		int toukyu_id = sys.getToukyu_id();
		int syouhin_id = sys.getSyouhin_id();
		String sql = "delete from syouhin where nen=" + nen + " and hagaki_id="
				+ hagaki_id + " and toukyu_id=" + toukyu_id
				+ " and syouhin_id=" + syouhin_id;
		BD.executeUpdate(sql);
		BD.close();
	}

	static public void Update(Syouhin sys1, Syouhin sys2) {
		BatchDBConnectionUtil BD = new BatchDBConnectionUtil();
		StringUtil SU = new StringUtil();
		SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
		String nowdate = date.format(new java.util.Date());
		int nen = sys1.getNen();
		int hagaki_id = sys1.getHagaki_id();
		int toukyu_id = sys1.getToukyu_id();
		int syouhin_id = sys1.getSyouhin_id();
		String syouhin_mei = sys2.getSyouhin_mei();
		String syouhin_ryaku = sys2.getSyouhin_ryaku();
		int jitaku = sys2.getJitaku();
		int nissu = sys2.getNissu();
		String bunrui = sys2.getBunrui();
		int gyousya_id = sys2.getGyousya_id();
		String keiyaku_cd = sys2.getKeiyaku_cd();
		int tyoutatusu = sys2.getTyoutatusu();
		String sql = "update syouhin set syouhin_mei='" + syouhin_mei
				+ "',syouhin_ryaku='" + syouhin_ryaku + "',jitaku=" + jitaku
				+ ",nissu=" + nissu + ",bunrui='" + bunrui + "',gyousya_id="
				+ gyousya_id + ",keiyaku_cd='" + keiyaku_cd + "',tyoutatusu="
				+ tyoutatusu + ",update_date='" + nowdate + "'" + " where nen="
				+ nen + " and hagaki_id=" + hagaki_id + " and toukyu_id="
				+ toukyu_id + " and syouhin_id=" + syouhin_id;
		BD.executeUpdate(sql);
		BD.close();
	}

}