package thales;

import java.awt.BorderLayout;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JTable;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import javax.swing.table.DefaultTableModel;
import javax.swing.JScrollPane;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.JTextField;
import javax.swing.SwingConstants;

public class game extends JFrame {

	private JPanel contentPane;
	private JTable table;
	private JTextField textField;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					game frame = new game();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public game() {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 414, 286);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);

		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(10, 28, 378, 154);
		contentPane.add(scrollPane);

		table = new JTable();
		scrollPane.setViewportView(table);
		table.setModel(new DefaultTableModel(
			new Object[][] {
				{"0", "0", "0", "0", new Integer(0)},
			},
			new String[] {
				"id", "\u0422\u043E\u0432\u0430\u0440", "\u0426\u0435\u043D\u0430", "\u041E\u0441\u0442\u0430\u0442\u043E\u043A", "\u041E\u043F\u0435\u0440\u0430\u0446\u0438\u044F"
			}
		) {
			Class[] columnTypes = new Class[] {
				Object.class, Object.class, Object.class, Object.class, Integer.class
			};
			public Class getColumnClass(int columnIndex) {
				return columnTypes[columnIndex];
			}
		});
		table.getColumnModel().getColumn(1).setPreferredWidth(268);

		JButton btnNewButton = new JButton("test");
		btnNewButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {
				Connection connect;
				try {
					String driverName="com.mysql.jdbc.Driver";
					Class.forName(driverName);
					String serverName="localhost";
					String mybase="java2";
					String url_="jdbc:mysql://"+serverName+"/"+mybase;
					String username="root";
					String password="";
					connect=DriverManager.getConnection(url_,username,password);

					String query="";
					Statement stmt=connect.createStatement();

					DefaultTableModel model = (DefaultTableModel) table.getModel();

					//выполнение операций
					ArrayList<Integer> list_id2=new ArrayList<Integer>();
					ArrayList<Integer> list_oper=new ArrayList<Integer>();
					ArrayList<Integer> list_price2=new ArrayList<Integer>();

					for (int i = 0; i < model.getRowCount(); i++) {
						int oper=Integer.parseInt(model.getValueAt(i, 4).toString());
						int id2=Integer.parseInt(model.getValueAt(i, 0).toString());
						int price2=Integer.parseInt(model.getValueAt(i, 2).toString());
						if (oper!=0) {
							list_id2.add(id2);
							list_oper.add(oper);
							list_price2.add(price2);
						}
					}

					for (int i = 0; i < list_id2.size(); i++) {
						query="INSERT operations (idgoods,val,sum)"
							+ " values ("+list_id2.get(i)+","+
							list_oper.get(i)+","+
							(-1*list_price2.get(i)*list_oper.get(i))+")";
						stmt=connect.createStatement();
						stmt.executeUpdate(query);
					}


					//замена цен
					query="SELECT * FROM goods";
					stmt=connect.createStatement();
					ResultSet rs=stmt.executeQuery(query);

					ArrayList<Integer> list_id=new ArrayList<Integer>();
					ArrayList<Integer> list_price=new ArrayList<Integer>();

					while (rs.next()) {
						int p=rs.getInt("price");
						double d=Math.random()-0.5;
						p=(int)(p*(1+d));
						if (p<10) p=10;
						list_id.add(rs.getInt("id"));
						list_price.add(p);
					}

					for (int i = 0; i < list_id.size(); i++) {
						query="UPDATE goods SET price="+list_price.get(i)+
						" WHERE id="+list_id.get(i);
						stmt=connect.createStatement();
						stmt.executeUpdate(query);
					}

					//новое состояние
					query="SELECT id,name,price,SUM(ifnull(val,0)) as val FROM goods"
						+ " LEFT JOIN operations ON id=idgoods"
						+ " GROUP BY id,name,price";
					stmt=connect.createStatement();
					rs=stmt.executeQuery(query);

					while (model.getRowCount()>0) {
						  model.removeRow(0);
					}

					while (rs.next()) {
						Object[] rowData=new Object[5];
						rowData[0]=rs.getString("id");
						rowData[1]=rs.getString("name");
						rowData[2]=rs.getString("price");
						rowData[3]=rs.getString("val");
						rowData[4]=0;
						model.addRow(rowData);
					}

					query="SELECT SUM(ifnull(sum,0))+10000 as val FROM operations";
					stmt=connect.createStatement();
					rs=stmt.executeQuery(query);
					if (rs.next()) {
						textField.setText(rs.getString("val"));
					}
					connect.close();
				}
				catch(Exception ex) {}

			}
		});
		btnNewButton.setBounds(10, 207, 152, 30);
		contentPane.add(btnNewButton);

		textField = new JTextField();
		textField.setHorizontalAlignment(SwingConstants.RIGHT);
		textField.setBounds(10, 0, 86, 20);
		contentPane.add(textField);
		textField.setColumns(10);
	}
}