using NORM.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text; 

namespace DevelopAssistant.Service
{
    public class DataObjectToSQL : SnippetBase
    {
        public static string ToInsertSQL(string TableName, object DataSource, string dateFormat, DataBaseServer DatabaseServer, System.Windows.Forms.ProgressBar ProgressBar = null)
        {
            StringPlus sp = new StringPlus();

            List<Column> columns = new List<Column>();
            using (var db = Utility.GetAdohelper(DatabaseServer))
            {
                DataTable dt = db.GetTableObject(TableName);
                foreach (DataRow dr in dt.Rows)
                {
                    Column col = new Column();
                    col.Name = dr["ColumnName"] + "";
                    col.DataType = dr["TypeName"] + "";
                    col.IsNull = dr["CisNull"] + "";

                    if (col.IsNull.Contains("pk"))
                    {
                        col.Pk_Identify = true;
                        col.IsNull = col.IsNull.Replace("pk,", "");
                    }

                    if (col.IsNull.Contains("identity"))
                    {
                        col.Auto_Identify = true;
                        col.IsNull = col.IsNull.Replace("identity,", "");
                    }

                    col.IsNull = col.IsNull.Replace(",", "");

                    columns.Add(col);

                }
            }

            if (DataSource == null)
                throw new Exception("数据源对象为空");

            bool Auto_Identify = false;

            if (columns.FindAll((Column c) =>
            {
                if (c.Auto_Identify)
                    return true;
                else return false;
            }).Count > 0)
            {
                //有自增列
                Auto_Identify = true;
            }

            var providerName = (DatabaseServer.ProviderName + "").ToLower();
            if (Auto_Identify && providerName == "system.data.sql")
            {
                if (AppSettings.EditorSettings.KeywordsCase)
                {
                    sp.Append("SET IDENTITY_INSERT " + getObject(TableName, DatabaseServer.ProviderName) + " ON");
                }
                else
                {
                    sp.Append("set identity_insert " + getObject(TableName, DatabaseServer.ProviderName) + " on");
                }
                sp.Append(Environment.NewLine);
            }

            DataTable dst = null;

            if (DataSource.GetType().Equals(typeof(DataTable)))
            {
                dst = DataSource as DataTable;
            }
            else if (DataSource.GetType().Equals(typeof(DataView)))
            {
                dst = ((DataView)DataSource).Table;
            }

            int indexRow = 0;
            int totalRows = dst.Rows.Count;

            foreach (DataRow dr in dst.Rows)
            {
                if (AppSettings.EditorSettings.KeywordsCase)
                {
                    sp.Append("INSERT INTO");
                }
                else
                {
                    sp.Append("insert into");
                }
                sp.Append(" " + getObject(TableName, DatabaseServer.ProviderName) + "");

                int num = 0;
                StringPlus fsp = new StringPlus();
                StringPlus vsp = new StringPlus();

                foreach (DataColumn dc in dst.Columns)
                {
                    if (num > 0)
                    {
                        fsp.Append(",");
                        vsp.Append(",");
                    }

                    fsp.Append(getObject(dc.ColumnName, DatabaseServer.ProviderName));
                    vsp.Append(getObjectValue(dr[dc.ColumnName], dateFormat, DatabaseServer.ProviderName));

                    num++;
                }

                //sp.Append(Environment.NewLine);
                sp.Append("( " + fsp.Value + " )");
                //sp.Append(Environment.NewLine);
                sp.Append(" ");
                if (AppSettings.EditorSettings.KeywordsCase)
                {
                    sp.Append("VALUES");
                }
                else
                {
                    sp.Append("values");
                }
                //sp.Append(Environment.NewLine);
                sp.Append("( " + vsp.Value + " )");
                sp.Append(Environment.NewLine);

                if (!Auto_Identify || !(providerName == "system.data.sql"))
                {
                    if (AppSettings.EditorSettings.KeywordsCase)
                    {
                        sp.Append("GO");
                    }
                    else
                    {
                        sp.Append("go");
                    }
                    sp.Append(Environment.NewLine);
                }                

                indexRow = indexRow + 1;

                if (ProgressBar != null)
                {
                    ProgressBar.Invoke(new System.Windows.Forms.MethodInvoker(() => { ProgressBar.Value = (int)((indexRow * 1.0d / totalRows) * 100); }));
                    System.Windows.Forms.Application.DoEvents();
                }

            }

            if (Auto_Identify && providerName == "system.data.sql")
            {
                if (AppSettings.EditorSettings.KeywordsCase)
                {
                    sp.Append("SET IDENTITY_INSERT dbo." + getObject(TableName, DatabaseServer.ProviderName) + " OFF");
                }
                else
                {
                    sp.Append("set identity_insert dbo." + getObject(TableName, DatabaseServer.ProviderName) + " off");
                }
                sp.Append(Environment.NewLine);
            }

            return sp.Value;
        }

        public static string ToTableDataEmptySQL(string TableName, DataTable DataSoruce, DataBaseServer DatabaseServer)
        {
            StringPlus sql = new StringPlus();

            if (AppSettings.EditorSettings.KeywordsCase)
            {
                sql.Append("SELECT");
            }
            else
            {
                sql.Append("select");
            }
            sql.Append(Environment.NewLine);
            switch (DatabaseServer.ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":
                    if (AppSettings.EditorSettings.KeywordsCase)
                    {
                        sql.Append(" TOP 0 ");
                    }
                    else
                    {
                        sql.Append(" top 0 ");
                    }
                    break;
                case "System.Data.Sqlite":
                    break;
                case "System.Data.PostgreSql":
                    break;
                default :
                    break;
            }

            int colIndex = 0;
            foreach (DataColumn dc in DataSoruce.Columns)
            {
                if (colIndex > 0)
                    sql.Append(",");

                sql.Append(" "+getObject(dc.ColumnName,DatabaseServer.ProviderName)+" ");
                colIndex++;
            }

            sql.Append(Environment.NewLine);
            if (AppSettings.EditorSettings.KeywordsCase)
            {
                sql.Append("FROM ");
            }
            else
            {
                sql.Append("from ");
            }
            sql.Append(getObject(TableName, DatabaseServer.ProviderName) + "");

            if (AppSettings.EditorSettings.KeywordsCase)
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":
                        break;
                    case "System.Data.Sqlite":
                        sql.Append(" LIMIT 0;");
                        break;
                    case "System.Data.MySql":
                    case "System.Data.PostgreSql":
                        sql.Append(" LIMIT 0;");
                        break;
                    default:
                        break;
                }
            }
            else
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":
                        break;
                    case "System.Data.Sqlite":
                        sql.Append(" limit 0;");
                        break;
                    case "System.Data.MySql":
                    case "System.Data.PostgreSql":
                        sql.Append(" limit 0;");
                        break;
                    default:
                        break;
                }
            }

            return sql.Value;
        }
    }
}