using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sybase.DataWindow.Web;
using Sybase.DataWindow;
using System.Web;
using System.Web.UI;
using System.Data;
using CoreWebServiceLibrary;
using GcoopServiceCs;

namespace CoreSavingLibrary
{
    public class DwUtil
    {

        public static string replaceOracle2Mysql(String sql)
        {
                sql = sql.Replace("sysdate", "NOW()");
                sql = sql.Replace("SYSDATE", "NOW()");
                sql = sql.Replace("\\", "\\\\");
                sql = sql.Replace("to_date(", "str_to_date(");
                sql = sql.Replace("TO_DATE(", "STR_TO_DATE(");
                sql = sql.Replace("yyyy-mm-dd hh24:mi:ss", "%Y-%m-%d %T");
                sql = sql.Replace("yyyy-MM-dd hh24:mi:ss", "%Y-%m-%d %T");
                sql = sql.Replace("yyyy/mm/dd hh24:mi:ss", "%Y/%m/%d %T");
                sql = sql.Replace("yyyy/MM/dd hh24:mi:ss", "%Y/%m/%d %T");
                sql = sql.Replace("yyyy-mm-dd", "%Y-%m-%d");
                sql = sql.Replace("yyyy-MM-dd", "%Y-%m-%d");
                sql = sql.Replace("yyyy/mm/dd", "%Y/%m/%d");
                sql = sql.Replace("yyyy/MM/dd", "%Y/%m/%d");
                sql = sql.Replace("yyyyMMdd", "%Y%m%d");
                sql = sql.Replace("ddmmyyyy", "%d%m%Y");
                sql = sql.Replace("ddMMyyyy", "%d%m%Y");
            return sql;
        }

        public String GetXmlDataStore(String wsPass, String application, String libralyList, String dwobjectName, params object[] ArgsList)
        {
            DwTrans sqlca = new DwTrans();
            try
            {
                sqlca.Connect();
                DataStore dts = new DataStore(libralyList, dwobjectName);
                dts.SetTransaction(sqlca);
                dts.Retrieve(ArgsList);
                if (dts.RowCount < 1)
                {
                    throw new Exception("ERROR:NOROWXML");
                }
                sqlca.Disconnect();
                return dts.Describe("DataWindow.Data.XML");
            }
            catch (Exception ex)
            {
                try { sqlca.Disconnect(); }
                catch { }
                throw ex;
            }
        }

        //Utility ที่มีการเชื่อมต่อกับ WsCommon
        public static void RetrieveDDDW(WebDataWindowControl dwObj, String columnName, String pbl, params object[] args)
        {
            try
            {
                DataWindowChild dwChild = dwObj.GetChild(columnName);
                try
                {
                    DwTrans sqlca = new DwTrans();
                    sqlca.Connect();
                    try
                    {
                        dwChild.SetTransaction(sqlca);
                        dwChild.Retrieve(args);
                    }
                    catch
                    {
                        sqlca.Disconnect();
                        dwChild.Reset();
                    }
                    sqlca.Disconnect();
                }
                catch { }
            }
            catch
            {
            }
        }

        public static void RetrieveDataWindow(WebDataWindowControl dwObj, String pbl, DwThDate dwThDate, params object[] args)
        {
            try
            {
                DwTrans sqlca = new DwTrans();
                sqlca.Connect();
                try
                {
                    dwObj.SetTransaction(sqlca);
                    dwObj.Retrieve(args);
                }
                catch
                {
                    sqlca.Disconnect();
                    dwObj.Reset();
                }
                sqlca.Disconnect();
            }
            catch
            {
                dwObj.Reset();
            }
            try
            {
                if (dwThDate != null)
                {
                    dwThDate.Eng2ThaiAllRow();
                }
            }
            catch { }
        }

        public static int InsertDataWindow(String wsPass, String xmlData, String pblName, String table, String dwObjectName, String application, int[] rows)
        {
            Security sec = new Security(wsPass);
            DwHandle dwH = new DwHandle(xmlData, pblName, dwObjectName);
            return dwH.InsertData(sec.ConnectionString, table, rows);
        }

        public static int UpdateDataWindow(String wsPass, String xmlData, String pblName, String table, String dwObjectName, String application, int[] rows)
        {
            Security sec = new Security(wsPass);
            DwHandle dwH = new DwHandle(xmlData, pblName, dwObjectName);
            return dwH.UpdateData(sec.ConnectionString, table, rows);
        }

        public static int UpdateDateWindow(IDataStore dwStore, String libraryList, String tableName)
        {
            int[] rows = new int[dwStore.RowCount];
            for (int i = 1; i <= dwStore.RowCount; i++)
            {
                rows[i - 1] = i;
            }
            return DwUtil.UpdateDateWindow(dwStore, libraryList, tableName, rows);
        }

        public static int UpdateDateWindow(IDataStore dwStore, String libraryList, String tableName, int[] rows)
        {
            WebState state = new WebState();
            String xml = dwStore.Describe("DataWindow.Data.XML");
            return UpdateDataWindow(state.SsWsPass, xml, libraryList, tableName, dwStore.DataWindowObject, state.SsApplication, rows);
        }

        public static int InsertDataWindow(WebDataWindowControl dwStore, String libraryList, String tableName)
        {
            int[] rows = new int[dwStore.RowCount];
            for (int i = 1; i <= dwStore.RowCount; i++)
            {
                rows[i - 1] = i;
            }
            return InsertDataWindow(dwStore, libraryList, tableName, rows);
        }

        public static int InsertDataWindow(WebDataWindowControl dwStore, String libraryList, String tableName, int[] rows)
        {
            WcfCalling wcf = new WcfCalling(null);
            WebState state = new WebState();
            String xml = dwStore.Describe("DataWindow.Data.XML");
            HttpServerUtility sv = new Page().Server;
            String libName = sv.MapPath(dwStore.LibraryList);
            String fullPathPbl = libName;
            int ii = DwUtil.InsertDataWindow(state.SsWsPass, xml, fullPathPbl, tableName, dwStore.DataWindowObject, state.SsApplication, rows);
            wcf.Close();
            return ii;
        }

        public static int UpdateDataWindow(WebDataWindowControl dwStore, String libraryList, String tableName)
        {
            int[] rows = new int[dwStore.RowCount];
            for (int i = 1; i <= dwStore.RowCount; i++)
            {
                rows[i - 1] = i;
            }
            return UpdateDataWindow(dwStore, libraryList, tableName, rows);
        }

        public static int UpdateDataWindow(WebDataWindowControl dwStore, String libraryList, String tableName, int[] rows)
        {
            WcfCalling wcf = new WcfCalling(null);
            WebState state = new WebState();
            String xml = dwStore.Describe("DataWindow.Data.XML");
            HttpServerUtility sv = new Page().Server;
            String libName = sv.MapPath(dwStore.LibraryList);
            String fullPathPbl = libName;
            int ii = DwUtil.UpdateDataWindow(state.SsWsPass, xml, fullPathPbl, tableName, dwStore.DataWindowObject, state.SsApplication, rows);
            wcf.Close();
            return ii;
        }

        public static void ImportData(String sqlSyntax, IDataWindowBase dataWindow, DwThDate dwThDate)
        {
            DataTable dt = WebUtil.Query(sqlSyntax);
            ImportData(dt, dataWindow, dwThDate);
        }

        public static void ImportData(DataTable dataTable, IDataWindowBase dataWindow, DwThDate dwThDate)
        {
            if (dataTable == null) return;
            dataWindow.Reset();
            for (int r = 0; r < dataTable.Rows.Count; r++)
            {
                dataWindow.InsertRow(0);
                for (int c = 0; c < dataWindow.ColumnCount; c++)
                {
                    try
                    {
                        String columnName = dataWindow.Describe("#" + (c + 1) + ".Name");
                        String cType = dataWindow.Describe(columnName + ".ColType").ToLower(); //dataTable.Columns[c].DataType.Name.ToLower();

                        if (cType.IndexOf("(") > 0)
                        {
                            cType = cType.Substring(0, cType.IndexOf("("));
                        }

                        if (cType == "datetime" || cType == "date")
                        {
                            if (dataTable.TableName == "DwDescribe")
                            {
                                String get = dataTable.Rows[r][columnName].ToString();
                                DateTime gdt = Convert.ToDateTime(get);   //by kowit
                                get = gdt.ToString("yyyy-MM-dd HH:mm:ss"); // by kowit
                                DateTime dt = DateTime.ParseExact(get, "yyyy-MM-dd HH:mm:ss", WebUtil.EN);
                                dataWindow.SetItemDateTime(r + 1, columnName, dt);
                            }
                            else
                            {
                                dataWindow.SetItemDateTime(r + 1, columnName, Convert.ToDateTime(dataTable.Rows[r][columnName]));
                            }
                        }
                        else if (cType == "int" || cType == "int16" || cType == "int32" || cType == "int64" || cType == "short" || cType == "dec" || cType == "decimal" || cType == "long" || cType == "float" || cType == "double" || cType == "number")
                        {
                            decimal decnum = Convert.ToDecimal(dataTable.Rows[r][columnName]);
                            dataWindow.SetItemDecimal(r + 1, columnName, decnum);
                        }
                        else
                        {
                            dataWindow.SetItemString(r + 1, columnName, Convert.ToString(dataTable.Rows[r][columnName]));
                        }
                    }
                    catch { }
                }
            }
            try
            {
                if (dwThDate != null)
                {
                    dwThDate.Eng2ThaiAllRow();
                }
                else
                {
                    dataWindow.ResetUpdateStatus();
                }
            }
            catch { }
        }

        public static void ImportData(String xmlString, IDataWindowBase dataWindow, DwThDate dwThDate, FileSaveAsType dataImportType)
        {
            if (dataImportType != FileSaveAsType.Xml)
                return;
            if (string.IsNullOrEmpty(xmlString))
            {
                dataWindow.Reset();
                return;
            }
            if (!WebUtil.IsXML(xmlString))
            {
                throw new Exception("ข้อมูลรูปแบบ XML ไม่ถูกต้อง");
            }
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();

            int dsIndex = -1;
            int dsMaxCol = -1;

            System.IO.StringReader sReader = new System.IO.StringReader(xmlString);
            ds.ReadXml(sReader);

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                int col = ds.Tables[i].Columns.Count;
                if (col > dsMaxCol)
                {
                    dsIndex = i;
                    dsMaxCol = ds.Tables[i].Columns.Count;
                }
            }

            dt = ds.Tables[dsIndex];
            dt.TableName = "DwDescribe";
            ImportData(dt, dataWindow, dwThDate);
        }

        public static void DeleteLastRow(IDataStore dwObj)
        {
            if (dwObj.RowCount > 1)
            {
                dwObj.DeleteRow(dwObj.RowCount);
            }
        }

        public static String GetString(IDataStore dw, int row, String column)
        {
            try
            {
                return dw.GetItemString(row, column).Trim();
            }
            catch
            {
                return null;
            }
        }

        public static int GetInt(IDataStore dw, int row, String column)
        {
            try
            {
                return Convert.ToInt32(dw.GetItemDecimal(row, column));
            }
            catch
            {
                return 0;
            }
        }

        public static Decimal GetDec(IDataStore dw, int row, String column)
        {
            try
            {
                return dw.GetItemDecimal(row, column);
            }
            catch
            {
                return 0;
            }
        }

        public static DateTime GetDateTime(IDataStore dw, int row, String column)
        {
            try
            {
                return dw.GetItemDateTime(row, column);
            }
            catch
            {
                return new DateTime(1370, 1, 1);
            }
        }

        public static String GetString(IDataWindowBase dw, int row, String column, String ifExceptionValue)
        {
            try
            {
                return dw.GetItemString(row, column).Trim();
            }
            catch
            {
                return ifExceptionValue;
            }
        }

        public static int GetInt(IDataStore dw, int row, String column, int ifExceptionValue)
        {
            try
            {
                return Convert.ToInt32(dw.GetItemDecimal(row, column));
            }
            catch
            {
                return ifExceptionValue;
            }
        }

        public static Decimal GetDec(IDataStore dw, int row, String column, decimal ifExceptionValue)
        {
            try
            {
                return dw.GetItemDecimal(row, column);
            }
            catch
            {
                return ifExceptionValue;
            }
        }
    }
}