package com.mjm.solarInsertDB;

import com.csvreader.CsvWriter;

import javax.baja.naming.BOrd;
import javax.baja.nre.annotations.NiagaraAction;
import javax.baja.nre.annotations.NiagaraProperty;
import javax.baja.nre.annotations.NiagaraSlots;
import javax.baja.nre.annotations.NiagaraType;
import javax.baja.sys.*;
import javax.baja.util.BNotification;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by user on 2017/4/7.
 */
@NiagaraType
@NiagaraSlots(
        properties =
                {
                        @NiagaraProperty(name = "DatabaseInformation", type = "BDatabaseInformation", defaultValue = "new BDatabaseInformation()", flags = Flags.SUMMARY),
                        @NiagaraProperty(name = "disable", type = "boolean", defaultValue = "true", flags = Flags.SUMMARY),
                        @NiagaraProperty(name = "exception", type = "boolean", defaultValue = "false", flags = Flags.SUMMARY),
                        @NiagaraProperty(name = "working", type = "boolean", defaultValue = "false", flags = Flags.SUMMARY),
                        @NiagaraProperty(name = "showMessage", type = "boolean", defaultValue = "true", flags = Flags.SUMMARY),
                        @NiagaraProperty(name = "path", type = "String", defaultValue = "", flags = Flags.SUMMARY),
                        @NiagaraProperty(name = "fileName", type = "String", defaultValue = "", flags = Flags.SUMMARY)
                },
        actions =
                {
                        @NiagaraAction(name = "update"),
                        @NiagaraAction(name = "setField", parameterType = "BInteger", defaultValue = "BInteger.make(1)")
                }
)


public class BUpdateData extends BComponent {
/*+ ------------ BEGIN BAJA AUTO GENERATED CODE ------------ +*/
/*@ $com.mjm.solarInsertDB.BUpdateData(2177564192)1.0$ @*/
/* Generated Mon Mar 10 19:48:33 CST 2025 by Slot-o-Matic (c) Tridium, Inc. 2012 */

////////////////////////////////////////////////////////////////
// Property "DatabaseInformation"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code DatabaseInformation} property.
   * @see #getDatabaseInformation
   * @see #setDatabaseInformation
   */
  public static final Property DatabaseInformation = newProperty(Flags.SUMMARY, new BDatabaseInformation(), null);
  
  /**
   * Get the {@code DatabaseInformation} property.
   * @see #DatabaseInformation
   */
  public BDatabaseInformation getDatabaseInformation() { return (BDatabaseInformation)get(DatabaseInformation); }
  
  /**
   * Set the {@code DatabaseInformation} property.
   * @see #DatabaseInformation
   */
  public void setDatabaseInformation(BDatabaseInformation v) { set(DatabaseInformation, v, null); }

////////////////////////////////////////////////////////////////
// Property "disable"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code disable} property.
   * @see #getDisable
   * @see #setDisable
   */
  public static final Property disable = newProperty(Flags.SUMMARY, true, null);
  
  /**
   * Get the {@code disable} property.
   * @see #disable
   */
  public boolean getDisable() { return getBoolean(disable); }
  
  /**
   * Set the {@code disable} property.
   * @see #disable
   */
  public void setDisable(boolean v) { setBoolean(disable, v, null); }

////////////////////////////////////////////////////////////////
// Property "exception"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code exception} property.
   * @see #getException
   * @see #setException
   */
  public static final Property exception = newProperty(Flags.SUMMARY, false, null);
  
  /**
   * Get the {@code exception} property.
   * @see #exception
   */
  public boolean getException() { return getBoolean(exception); }
  
  /**
   * Set the {@code exception} property.
   * @see #exception
   */
  public void setException(boolean v) { setBoolean(exception, v, null); }

////////////////////////////////////////////////////////////////
// Property "working"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code working} property.
   * @see #getWorking
   * @see #setWorking
   */
  public static final Property working = newProperty(Flags.SUMMARY, false, null);
  
  /**
   * Get the {@code working} property.
   * @see #working
   */
  public boolean getWorking() { return getBoolean(working); }
  
  /**
   * Set the {@code working} property.
   * @see #working
   */
  public void setWorking(boolean v) { setBoolean(working, v, null); }

////////////////////////////////////////////////////////////////
// Property "showMessage"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code showMessage} property.
   * @see #getShowMessage
   * @see #setShowMessage
   */
  public static final Property showMessage = newProperty(Flags.SUMMARY, true, null);
  
  /**
   * Get the {@code showMessage} property.
   * @see #showMessage
   */
  public boolean getShowMessage() { return getBoolean(showMessage); }
  
  /**
   * Set the {@code showMessage} property.
   * @see #showMessage
   */
  public void setShowMessage(boolean v) { setBoolean(showMessage, v, null); }

////////////////////////////////////////////////////////////////
// Property "path"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code path} property.
   * @see #getPath
   * @see #setPath
   */
  public static final Property path = newProperty(Flags.SUMMARY, "", null);
  
  /**
   * Get the {@code path} property.
   * @see #path
   */
  public String getPath() { return getString(path); }
  
  /**
   * Set the {@code path} property.
   * @see #path
   */
  public void setPath(String v) { setString(path, v, null); }

////////////////////////////////////////////////////////////////
// Property "fileName"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code fileName} property.
   * @see #getFileName
   * @see #setFileName
   */
  public static final Property fileName = newProperty(Flags.SUMMARY, "", null);
  
  /**
   * Get the {@code fileName} property.
   * @see #fileName
   */
  public String getFileName() { return getString(fileName); }
  
  /**
   * Set the {@code fileName} property.
   * @see #fileName
   */
  public void setFileName(String v) { setString(fileName, v, null); }

////////////////////////////////////////////////////////////////
// Action "update"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code update} action.
   * @see #update()
   */
  public static final Action update = newAction(0, null);
  
  /**
   * Invoke the {@code update} action.
   * @see #update
   */
  public void update() { invoke(update, null, null); }

////////////////////////////////////////////////////////////////
// Action "setField"
////////////////////////////////////////////////////////////////
  
  /**
   * Slot for the {@code setField} action.
   * @see #setField(BInteger parameter)
   */
  public static final Action setField = newAction(0, BInteger.make(1), null);
  
  /**
   * Invoke the {@code setField} action.
   * @see #setField
   */
  public void setField(BInteger parameter) { invoke(setField, parameter, null); }

////////////////////////////////////////////////////////////////
// Type
////////////////////////////////////////////////////////////////
  
  @Override
  public Type getType() { return TYPE; }
  public static final Type TYPE = Sys.loadType(BUpdateData.class);

/*+ ------------ END BAJA AUTO GENERATED CODE -------------- +*/



    BDatabaseInformation databaseInformation = null;

    public void started() {
        databaseInformation = (BDatabaseInformation) this.getDatabaseInformation().asComponent();
        BOrd absolute = BOrd.make("station:|slot:/Services/PlatformServices/SystemService/hostId");
        String hostID = absolute.get().toString();
        System.out.println(this.getType().getTypeName() + " --onStarted-- " + hostID);
    }


    public void changed(Property property, Context context) {
        if (this.isRunning()) {
            if (property == databaseInformation) {
                System.out.println("in update  data");
                databaseInformation = (BDatabaseInformation) this.databaseInformation.asComponent();
            }
        }
    }

    public void doUpdate() {

        System.out.println("0310 doUpdate: ");
        if (1 == 1) { //if (checkLicense.getLegal())
            List<String> fieldName = new ArrayList<String>();
            List<String> fieldValue = new ArrayList<String>();
            List<String> conditionName = new ArrayList<String>();
            List<String> conditionValue = new ArrayList<String>();

            if (this.getDisable() || this.getException()) {
                if (this.getShowMessage()) {
                    System.out.println("disable or exception");
                }
            } else {
                try {
                    this.setWorking(true);
                    Property property[] = this.getDynamicPropertiesArray();

                    for (int i = 0; i < property.length; i++) {
                        if (property[i].getName().contains("Field")) {
//                            BFieldUpdate field = (BFieldUpdate) this.get(property[i]).asComponent();
//                            fieldName.add(field.getFieldName());
//                            fieldValue.add(field.getFieldValue());
//                            conditionName.add(field.getConditionName());
//                            conditionValue.add(field.getConditionValue());

                            BField field = (BField) this.get(property[i]).asComponent();
                            fieldName.add(field.getFieldName());
                            fieldValue.add(field.getFieldValue());
                        }
                    }

                    if (fieldName.size() == fieldValue.size() && conditionName.size() == conditionValue.size()) {
                        if (databaseInformation.getInsertToDatabase()) {
//                            String updateCommand = makeUpdateCommand(fieldName, fieldValue,conditionName,conditionValue);
                            String updateCommand = makeUpdateCommand2(fieldName, fieldValue);
                            // 建立连接
                            ConnectInformation connectInformation = new ConnectInformation();
                            Connection connection = connectInformation.getSQLServerConnection(databaseInformation.getDbIp(), databaseInformation.getDbName(),
                                    databaseInformation.getDbUser(), databaseInformation.getDbPassword(), databaseInformation.getDbTableName());
                            System.out.println("------connection info: " + connection);
                            // 创建Statement
                            Statement statement = connection.createStatement();
                            System.out.println("------updateCommand info: " + updateCommand);
                            // 执行查询
                            statement.execute(updateCommand);
                            statement.close();
                            connection.close();
                        }
                        createCSV(fieldName, fieldValue);
                    }
                } catch (Exception e) {
                    System.out.println(e);
                    this.setException(true);
                    e.printStackTrace();
                } finally {
                    this.setWorking(false);
                }
            }
        } else {
            System.out.println("no license!");
            BNotification note = new BNotification();
            note.add("title", BString.make("No License"));
            note.add("message", BString.make("Please contact FIC"));
            note.raise(true);
        }
    }


    public void doSetField(BInteger interger) {

        int number = interger.getInt();
        Property property[] = this.getDynamicPropertiesArray();
        for (int i = 0; i < property.length; i++) {
            if (property[i].getName().contains("Field")) {
                this.remove(property[i].getName());
            }
        }
        for (int i = 0; i < number; i++) {
            this.add("Field" + (i + 1), new BField());
        }
    }

    private void createCSV(List<String> fieldName, List<String> fieldValue) {

        SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();
        String strDate = sdFormat.format(date);
        String outputFile = "../../shared/" + this.getPath() + strDate + this.getFileName();

        // before we open the file check to see if it already exists
        boolean alreadyExists = new File(outputFile).exists();
        try {
            // use FileWriter constructor that specifies open for appending
            if (!alreadyExists) {
                deleteFile();
                if (!this.getPath().equals("")) {
                    File file = new File(outputFile);
                    file.getParentFile().mkdir();
                    try {
                        file.createNewFile();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

            CsvWriter csvOutput = new CsvWriter(new FileWriter(outputFile, true), ',');
            // if the file didn't already exist then we need to write out the header line
            if (!alreadyExists) {
                for (int i = 0; i < fieldName.size(); i++) {
                    csvOutput.write(fieldName.get(i));
                }
                csvOutput.endRecord();
            }
            // else assume that the file already has the correct header line
            // write out a few records

            for (int i = 0; i < fieldValue.size(); i++) {
                csvOutput.write(fieldValue.get(i));
            }
            csvOutput.endRecord();
            csvOutput.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void deleteFile() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DAY_OF_MONTH, -90);//90天前
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String before90 = format.format(cal.getTime()) + " 00:00:00";
        System.out.println("delete file before " + before90);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));

        try {
            Date sDate = sdf.parse(before90);
            long beforeTimestamp = sDate.getTime() / 1000;
//            System.out.println(beforeTimestamp);
            String fullpath = "../../shared/" + this.getPath();
            File a = new File(fullpath);
            if (a.isDirectory()) {
                String[] filenames = a.list();
                for (int i = 0; i < filenames.length; i++) {
                    File tempFile = new File(fullpath + "\\" + filenames[i]);
                    if (tempFile.isDirectory()) {
                        System.out.println("目錄:" + filenames[i]);
                    } else {
//                        System.out.println("檔案:" + filenames[i]);
                        String dateName = filenames[i].split(" ")[0] + " 00:00:00";
                        sDate = sdf.parse(dateName);
                        long fileTimestamp = sDate.getTime() / 1000;
                        if (fileTimestamp < beforeTimestamp) {
                            System.out.println("delete file " + fullpath + filenames[i]);
                            File deleteFile = new File(fullpath + filenames[i]);
                            deleteFile.delete();
                        }
                    }
                }
            } else {
                System.out.println("[" + a + "]不是目錄");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @param fieldName 要修改的属性
     * @param fieldValue 修改后值
     * @return
     */
    private String makeUpdateCommand2(List<String> fieldName, List<String> fieldValue){
        String command = "update " + databaseInformation.getDbName() + ".dbo." + databaseInformation.getDbTableName() + " set ";
        for (int i = 0; i < fieldName.size(); i++) {
            command = command + fieldName.get(i) +"='"+fieldValue.get(i) +"',";
        }
        command = command.substring(0, command.length() -1) + " ;";
        System.out.println("update command: " + command);
        return command;
    }

    /**
     *
     * @param fieldName 要修改的属性
     * @param fieldValue 修改后值
     * @param conditionName wehere属性
     * @param conditionValue wehere值
     * @return
     */
    private String makeUpdateCommand(List<String> fieldName, List<String> fieldValue , List<String> conditionName ,List<String> conditionValue){
        String command = "update " + databaseInformation.getDbName() + ".dbo." + databaseInformation.getDbTableName() + " set ";
        for (int i = 0; i < fieldName.size(); i++) {
            command = command + fieldName.get(i) +"='"+fieldValue.get(i) +"',";
        }
        command = command.substring(0, command.length() - 1)+" where ";
        for (int i = 0; i < conditionName.size(); i++) {
            command = command + conditionName.get(i) +"='"+conditionValue.get(i)+"' and ";
        }
        command = command.substring(0, command.length() - 4) + " ;";
        System.out.println("update command: " + command);
        return command;
    }

    public void setDatabaseInformation() {
        databaseInformation = (BDatabaseInformation) this.getDatabaseInformation().asComponent();
    }
}
