using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataLibrary;
using System.Collections;
using System.IO;

namespace CoreSavingLibrary
{
    public class PageWebSheet : PageWeb
    {
        private bool isConfirmOnNew = false;

        public bool IsConfirmOnNew
        {
            get { return isConfirmOnNew; }
            set { isConfirmOnNew = value; }
        }

        private bool ignoreReadable = false;

        public bool IgnoreReadable
        {
            get { return ignoreReadable; }
            set { ignoreReadable = value; }
        }

        private bool fullScreen = false;

        public bool FullScreen
        {
            get { return fullScreen; }
            set { fullScreen = value; }
        }

        public String WebSheetType
        {
            get { return "Saving.PageWebSheet"; }
        }

        public int ScrollTop { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            ScrollTop = -1;
            isConfirmOnNew = false;
            tDwDates = new List<DwThDate>();
            String SaveWebSheet = WebUtil.JsPostBack(this, "SaveWebSheet");
            String saveJavaScript = SaveWebSheet + @"
            <script>
            function MenubarSave(){
                var isValid;
                try{
                    isValid = Validate();
                }catch(err){
                    alert(err);
                    alert('ไม่พบการ Validate');
                    return;
                }
                if(!isValid) return;
                SaveWebSheet();
            }
            </script>";
            initJavaScript = "";
            Type t = this.GetType();
            String lastFocus = "";
            try
            {
                lastFocus = Request["tempLastFocus"];
            }
            catch { }

            String dsTempElementEnter = "\n<!-- Doys แทรก Script เพื่อ  //-->\n";
            dsTempElementEnter += saveJavaScript;
            dsTempElementEnter += "<input type='text' id='tempElementEnter' name='tempElementEnter' value='fel' style='width:1px;height:1px;border:none;background-color:white;' />\n";
            dsTempElementEnter += "<input type='hidden' id='tempLastFocus' name='tempLastFocus' value='" + lastFocus + "' />\n\n";
            ClientScript.RegisterClientScriptBlock(t, "DsTempElementEnter", dsTempElementEnter);
        }

        protected void Page_LoadComplete(object sender, EventArgs e)
        {
            String s = sender.ToString();
        }

        protected void SetJsOpenIFrame(int width, int height, string w_sheet_name, string queryString)
        {
            String script = "Gcoop.OpenIFrame(" + width + ", " + height + ", \"" + w_sheet_name + "\", \"" + queryString + "\");";
            SetOnLoadedScript(script);
        }
        protected void SetJsOpenIFrameExtend(int width, int height, string w_sheet_name, string queryString)
        {
            String script = "Gcoop.OpenIFrameExtend(" + width + ", " + height + ", \"" + w_sheet_name + "\", \"" + queryString + "\");";
            SetOnLoadedScript(script);
        }
        protected void SetJsOpenDialog(int width, int height, string w_sheet_name, string queryString)
        {
            String script = "Gcoop.OpenDlg(" + width + ", " + height + ", \"" + w_sheet_name + "\", \"" + queryString + "\");";
            SetOnLoadedScript(script);
        }



        public string getXMLStringValue(String xml, String name)
        {
            string value = "";
            name = "<" + name + ">";
            int pos = xml.IndexOf(name);
            if (pos > 0)
            {
                string tmp = xml.Substring(pos + name.Length);
                pos = tmp.IndexOf("</");
                tmp = tmp.Substring(0, pos);
                value = tmp;
            }

            return value;
        }

        public String CURRENT_SAVING_PATH = "";
        public String CURRENT_SAVING_PATH_PBL = "";
        public String process_log;
        public String process_id;
        public String connectionString;
        public String object_name = "";
        public String criteria_xml = "";
        public String criteria_xml_1 = "";
        public String criteria_xml_2 = "";
        public String application = "";
        public DateTime workdate;
        public String coop_id;
        public String coop_control;
        public Sta taMain;
        public Sta taQuery;

        public void startProcessMsgLog(String msg, int numOfAllstep)
        {
            WriteLine("startProcessMsgLog");
            String sql = "update cmprocessing set runtime_message='" + msg + "',entry_date=sysdate,start_process=0,end_process=" + numOfAllstep + " where process_id='" + process_id + "'";
            execAutoCommit(sql);
        }

        public void updateProcessMsgLog(String msg, int step)
        {
            WriteLine("updateProcessMsgLog");
            String sql = "update cmprocessing set runtime_message='" + msg + "',entry_date=sysdate,start_process=" + step + " where process_id='" + process_id + "'";
            execAutoCommit(sql);
        }

        public void finishProcessMsgLog(String msg, int step)
        {
            WriteLine("finishProcessMsgLog");
            String sql = "update cmprocessing set runtime_message='" + msg + "',entry_date=sysdate,finish_date=sysdate,start_process=" + step + ",runtime_status=1 where process_id='" + process_id + "'";
            execAutoCommit(sql);
            try
            {
                taMain.Commit();
                WriteLine("taMain Commit()");
            }
            catch { }
        }

        public void cancelProcessMsgLog(String msg)
        {
            WriteLine("cancelProcessMsgLog");
            if (msg != null)
            {
                String sql = "update cmprocessing set runtime_message='" + msg + "',entry_date=sysdate,finish_date=sysdate,runtime_status=-9 where process_id='" + process_id + "'";
                execAutoCommit(sql);
            }
            try
            {
                taMain.RollBack();
                WriteLine("taMain rollback()");
            }
            catch { }
        }

        public void execAutoCommit(String sql)
        {
            Sta ta = new Sta(connectionString);
            try
            {
                WriteLine(sql);
                ta.Exe(sql);
            }
            catch (Exception e)
            {
                ta.RollBack();
                e.ToString();
                WriteLine(e.ToString());
            }
            finally
            {
                ta.Close();
            }
        }

        public void WriteLine(String log)
        {
            Console.WriteLine(DateTime.Now.ToString() + ":" + log);
            try
            {
                StreamWriter w = File.AppendText(this.process_log);
                w.WriteLine(log);
                w.Close();
            }
            catch { }
        }

        public void closeProcessing(String msg)
        {

            try
            {
                taMain.Commit();
            }
            catch (Exception e)
            {
                try
                {
                    taMain.RollBack();
                }
                catch (Exception e1)
                {
                    e1.ToString();
                    //WriteLine(e1.ToString());
                }
                e.ToString();
                //WriteLine(e.ToString());
            }
            finally
            {
                taMain.Close();
                taQuery.Close();
                WriteLine((msg != null ? msg : "") + "Close :taMain: Process_ID=" + process_id);
                WriteLine((msg != null ? msg : "") + "Close :taQuery: Process_ID=" + process_id);
            }

            this.WriteLine("=========================CloseProcess:" + msg + "=====================");
        }

        public void getCurrentDir()
        {
            CURRENT_SAVING_PATH = Directory.GetCurrentDirectory().Replace("\\AppProcessing\\bin\\Release", "") + "\\Saving\\";
            CURRENT_SAVING_PATH_PBL = CURRENT_SAVING_PATH + "DataWindow\\" + application + "\\" + "";
        }

        public void buildSQLimport(bool post_flag, bool logssql_flag, bool insert_flag,bool delete_flag, Sdt sdtX, String[] col_pk_nm, String[] col_pk_src, String[] col_pk_rp, String table_schema, String table_name, ref String sql, ref String results, ref String results_sql)
        {
            String sql__ = "SELECT *  FROM INFORMATION_SCHEMA.key_column_usage  WHERE TABLE_SCHEMA ='" + table_schema + "' and TABLE_NAME='" + table_name + "' and lower(constraint_name) in ('primary','lncontmaster_index') ";
            Sdt sdt__ = taQuery.Query(sql__);
            String pk_columns = "";
            while (sdt__.Next())
            {
                pk_columns += sdt__.GetString("column_name") + ",";
            }

            ArrayList columnList = new ArrayList();
            String sql_ = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + table_schema + "' AND TABLE_NAME = '" + table_name + "' order by column_key asc";
            Sdt sdt_ = taQuery.Query(sql_);
            while (sdt_.Next())
            {
                String[] cols = new String[2];
                cols[0] = sdt_.GetString("column_name");
                cols[1] = sdt_.GetString("data_type");
                columnList.Add(cols);
            }

            if (post_flag)
            {
                sql = "";
                while (sdtX.Next())
                {
                    //DELETE SQL
                    if(delete_flag)
                    {

                        String sql_1 = "delete from " + table_name + " ";
                        String sql_2 = " where ";
                        bool found = true; int i = 0;
                        while (found)
                        {
                            String[] cols = new String[2]; cols = (String[])columnList[i];
                            string column_name = cols[0];
                            string date_type = cols[1];
                            bool column_key = pk_columns.IndexOf(column_name) >= 0;
                            string column_value = sdtX.GetString(column_name);
                            string sql_set = column_name + "=";
                            for (int x = 0; x < col_pk_nm.Length; x++)
                            {
                                if (column_name.ToLower() == col_pk_nm[x])
                                {
                                    column_value = column_value.Replace(col_pk_src[x], col_pk_rp[x]);
                                }
                            }
                            //sql_1 += column_name;
                            //sql_2 += "'" + date_type+":" + column_value + "'";

                            if (date_type == "datetime")
                            {
                                if (Sta.IS_MYSQL_MODE)
                                 column_value = "str_to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy HH:mm:ss") + "','%d/%m/%Y %T')";
                                else
                                 column_value = "to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy HH:mm:ss") + "','dd/mm/yyyy hh24:mi:ss')";
                                if (column_value.IndexOf("1370") > 0)
                                    sql_set += "null";
                                else
                                    sql_set += column_value;
                            }
                            else if (date_type == "date")
                            {
                                if (Sta.IS_MYSQL_MODE)
                                 column_value = "str_to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy") + "','%d/%m/%Y')";
                                else
                                 column_value = "to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy") + "','dd/mm/yyyy')";
                                if (column_value.IndexOf("1370") > 0)
                                    sql_set += "null";
                                else
                                    sql_set += column_value;
                            }
                            else if (date_type.IndexOf("char") >= 0)
                            {
                                if (column_value.Length <= 0)
                                    sql_set += "null";
                                else
                                    sql_set += "'" + column_value + "'";
                            }
                            else if (date_type.IndexOf("decimal") >= 0)
                            {
                                if (column_value.Length <= 0)
                                    sql_set += "null";
                                else
                                    sql_set += "'" + column_value + "'";
                            }
                            else
                            {
                                sql_set += "'" + column_value + "'";
                            }

                            i++;
                            found = i < columnList.Count;
                            if (column_key)
                            {
                                sql_2 += sql_set;
                                if (column_key)
                                    sql_2 += " and ";
                            }
                            else
                            {

                               
                            }
                        }
                        sql_2 += "}"; sql_2 = sql_2.Replace("and }", "");
                        sql = sql_1 + sql_2; sql = sql.Replace("} where", " where");

                        if (logssql_flag) results += "\r\n         SQL=" + sql + "  \r\n \r\n";
                        results_sql += sql + ";\n";
                    }

                    //INSERT SQL
                    if (insert_flag)
                    {
                        String sql_1 = "insert into " + table_name + " (";
                        String sql_2 = ")values(";
                        bool found = true; int i = 0;
                        while (found)
                        {
                            String[] cols = new String[2]; cols = (String[])columnList[i];
                            string column_name = cols[0];
                            string date_type = cols[1];
                            bool column_key = pk_columns.IndexOf(column_name) >= 0;
                            string column_value = sdtX.GetString(column_name);
                            for (int x = 0; x < col_pk_nm.Length;x++ )
                            {
                                if (column_name.ToLower() == col_pk_nm[x])
                                {
                                    column_value = column_value.Replace(col_pk_src[x], col_pk_rp[x]);
                                }
                            }
                            sql_1 += column_name;
                            //sql_2 += "'" + date_type+":" + column_value + "'";

                            if (date_type == "datetime")
                            {
                                if(Sta.IS_MYSQL_MODE)
                                 column_value = "str_to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy HH:mm:ss") + "','%d/%m/%Y %T')";
                                else
                                    column_value = "to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy HH:mm:ss") + "','dd/mm/yyyy hh24:mi:ss')";
                                if (column_value.IndexOf("1370") > 0)
                                    sql_2 += "null";
                                else
                                    sql_2 += column_value;
                            }
                            else if (date_type == "date")
                            {
                                if (Sta.IS_MYSQL_MODE)
                                 column_value = "str_to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy") + "','%d/%m/%Y')";
                                if (column_value.IndexOf("1370") > 0)
                                    sql_2 += "null";
                                else
                                    sql_2 += column_value;
                            }
                            else if (date_type.IndexOf("char") >= 0)
                            {
                                if (column_value.Length <= 0)
                                    sql_2 += "null";
                                else
                                    sql_2 += "'" + column_value + "'";
                            }
                            else if (date_type.IndexOf("decimal") >= 0)
                            {
                                if (column_value.Length <= 0)
                                    sql_2 += "null";
                                else
                                    sql_2 += "'" + column_value + "'";
                            }
                            else
                            {
                                sql_2 += "'" + column_value + "'";
                            }
                            i++;
                            found = i < columnList.Count;
                            if (found)
                            {
                                sql_1 += ",";
                                sql_2 += ",";
                            }
                        }
                        sql_2 += ")";
                        sql = sql_1 + sql_2;
                    }
                    else
                    {

                        String sql_1 = "update " + table_name + " set ";
                        String sql_2 = " where ";
                        bool found = true; int i = 0;
                        while (found)
                        {
                            String[] cols = new String[2]; cols = (String[])columnList[i];
                            string column_name = cols[0];
                            string date_type = cols[1];
                            bool column_key = pk_columns.IndexOf(column_name) >= 0;
                            string column_value = sdtX.GetString(column_name);
                            string sql_set = column_name + "=";
                            for (int x = 0; x < col_pk_nm.Length; x++)
                            {
                                if (column_name.ToLower() == col_pk_nm[x])
                                {
                                    column_value = column_value.Replace(col_pk_src[x], col_pk_rp[x]);
                                }
                            }
                            //sql_1 += column_name;
                            //sql_2 += "'" + date_type+":" + column_value + "'";

                            if (date_type == "datetime")
                            {
                                if (Sta.IS_MYSQL_MODE)
                                 column_value = "str_to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy HH:mm:ss") + "','%d/%m/%Y %T')";
                                else
                                 column_value = "to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy HH:mm:ss") + "','dd/mm/yyyy hh24:mi:ss')";
                                if (column_value.IndexOf("1370") > 0)
                                    sql_set += "null";
                                else
                                    sql_set += column_value;
                            }
                            else if (date_type == "date")
                            {
                                if (Sta.IS_MYSQL_MODE)
                                 column_value = "str_to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy") + "','%d/%m/%Y')";
                                else
                                 column_value = "to_date('" + sdtX.GetDate(column_name).ToString("dd/MM/yyyy") + "','dd/mm/yyyy')";
                                if (column_value.IndexOf("1370") > 0)
                                    sql_set += "null";
                                else
                                    sql_set += column_value;
                            }
                            else if (date_type.IndexOf("char") >= 0)
                            {
                                if (column_value.Length <= 0)
                                    sql_set += "null";
                                else
                                    sql_set += "'" + column_value + "'";
                            }
                            else if (date_type.IndexOf("decimal") >= 0)
                            {
                                if (column_value.Length <= 0)
                                    sql_set += "null";
                                else
                                    sql_set += "'" + column_value + "'";
                            }
                            else
                            {
                                sql_set += "'" + column_value + "'";
                            }

                            i++;
                            found = i < columnList.Count;
                            if (column_key)
                            {
                                sql_2 += sql_set;
                                if (column_key)
                                    sql_2 += " and ";
                            }
                            else
                            {

                                sql_1 += sql_set;
                                if (column_key == false)
                                    sql_1 += " , ";
                            }
                        }
                        sql_1 += "}"; sql_1 = sql_1.Replace(", }", "");
                        sql_2 += "}"; sql_2 = sql_2.Replace("and }", "");
                        sql = sql_1 + sql_2; sql = sql.Replace("} where", " where");
                    }
                    if (logssql_flag) results += "\r\n         SQL=" + sql + "  \r\n \r\n";
                    results_sql += sql + ";\n";
                }
            }

        }

        public void logResultTextFile(String logsFilePath, ref String results, ref String results_sql, bool clear_flag, bool post_flag, bool create_flag)
        {
            if (create_flag)
            {
                CreateTextFile(logsFilePath, ref results, true);
                if (post_flag) CreateTextFile(logsFilePath + ".sql", ref results_sql, true);
            }
            else
            {
                AppendTextFile(logsFilePath, ref results, true);
                if (post_flag) AppendTextFile(logsFilePath + ".sql", ref results_sql, true);
            }
        }

        public void CreateTextFile(String logsFilePath,ref String results,bool clear_flag)
        {
            File.WriteAllText(logsFilePath, results);
            if (clear_flag) results = "";
        }

        public void AppendTextFile(String logsFilePath, ref String results, bool clear_flag)
        {
            File.AppendAllText(logsFilePath, results);
            if (clear_flag) results = "";
        }

        public void initProcessing(String outputProcess, String object_name, String criteria_xml, String criteria_xml_1, String criteria_xml_2)
        {

            this.criteria_xml = criteria_xml;
            this.criteria_xml_1 = criteria_xml_1;
            this.criteria_xml_2 = criteria_xml_2;
            this.object_name = object_name;
            this.process_id = outputProcess.Substring(outputProcess.IndexOf("?p=") + 3, outputProcess.IndexOf("&d=") - outputProcess.IndexOf("?p=") - 3);
            this.initProcessing();
        }

        public void initProcessing()
        {
            WriteLine(process_id + " " + connectionString);
            taMain = new Sta(connectionString);
            taQuery = new Sta(connectionString);
            try
            {
                taMain.Transection();
            }
            catch (Exception e)
            {
                e.ToString();
                WriteLine(e.ToString());
            }
            finally
            {
            }
        }

    }
}