EFLite.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.ComponentModel;
using System.Reflection;
using sage.ew.db;

namespace sage.addons.offline.Negocio.Clases
{
    public static class EFLite
    {

        public static void Insert(this Object obj)
        {
            obj.Insert(obj.GetPropertyByName<string>("_DataBase"), obj.GetPropertyByName<string>("_Table"));
        }
        public static void Insert(this Object obj, string _DataBase, string _Table)
        {
            Tuple<string, string> ret = obj.GetNamesValues();
            string lcSql = string.Format("INSERT INTO " + DB.SQLDatabase(_DataBase, _Table) + " ({0})" +" VALUES ({1})",ret.Item1,ret.Item2);
            DB.SQLExec(lcSql);
        }
        public static void InsertOrUpdate(this Object obj)
        {
            string _DataBase = obj.GetPropertyByName<string>("_DataBase");
            string _Table = obj.GetPropertyByName<string>("_Table").ToString();
            string where = obj.GetWhere();
            
            if(obj.Exists(_DataBase, _Table, where))
            {
                if (!string.IsNullOrEmpty(where))
                {
                    obj.Update(_DataBase, _Table, where);
                }
            }
            else
            {
                obj.Insert();
            }
        }
        public static bool Exists(this Object obj, string _DataBase, string _Table, string where)
        {
            DataTable dt = new DataTable();
            string lcSql = "SELECT count(*) as existe " + " FROM " + DB.SQLDatabase(_DataBase, _Table) +
                " WHERE " + where;
            DB.SQLExec(lcSql, ref dt);
            return Convert.ToInt32(dt.Rows[0]["existe"]) > 0;
        }
        public static Int32 NewStrId(this Object obj, string field, Int32 inc = 1, string where = "")
        {
            Int32 ret = inc;
            
            string lcSql = "Select max(CAST(" + field + " AS INT)) as maximo from " +
                DB.SQLDatabase(obj.GetPropertyByName<string>("_DataBase"), obj.GetPropertyByName<string>("_Table"));
            if (!string.IsNullOrEmpty(where))
            {
                lcSql += " WHERE " + where;
            }
             
            DataTable dt = new DataTable();
            DB.SQLExec(lcSql, ref dt);
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["maximo"] == DBNull.Value)
                {
                    ret = inc;
                }
                else
                {
                    ret += Convert.ToInt32(dt.Rows[0]["maximo"]);
                }
            }

            return ret;
        }
        public static Int32 NewId(this Object obj, string field, Int32 inc=1, string where="")
        {
            Int32 ret = inc;

            string lcSql = "Select max(" + field + ") as maximo from " +
                DB.SQLDatabase(obj.GetPropertyByName<string>("_DataBase"), obj.GetPropertyByName<string>("_Table"));
            if (!string.IsNullOrEmpty(where))
            {
                lcSql += " WHERE " + where;
            }

            DataTable dt = new DataTable();
            DB.SQLExec(lcSql, ref dt);
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["maximo"] == DBNull.Value)
                {
                    ret = inc;
                }
                else
                {
                    ret += Convert.ToInt32(dt.Rows[0]["maximo"]);
                }
            }

            return ret;
        }
        public static bool IsNullOrEmpty(this Object obj)
        {
            bool ret = false;
            string myType = obj.GetType().Name.ToLower();
            switch (myType)
            {
                case "string":
                    {
                        ret = obj == null || ((string)obj) == "";
                    }
                    break;
                case "datetime":
                    {
                        ret = obj == null || ((DateTime)obj) == DateTime.MinValue;
                    }
                    break;
                default:
                    {
                        ret = obj == null;
                    }
                    break;
            }
            return ret;
        }

        public static void Delete(this Object obj)
        {
            DataTable dt = new DataTable();
            string[] keys = obj.GetPropertyByName<string[]>("_keys");
            StringBuilder sb = new StringBuilder();
            List<string> _flds = (List<string>)obj.GetType().GetField("_flds").GetValue(obj);

            foreach (string key in keys)
            {
                PropertyInfo prop = obj.GetPropertyInfoByName(key);
                object valor = prop.GetValue(obj, null);
                if (_flds.Contains(prop.Name))
                {
                    sb.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " and ");
                }
            }
            sb.Remove(sb.Length - 4, 4);
            obj.Delete(obj.GetPropertyByName<string>("_DataBase"), obj.GetPropertyByName<string>("_Table"), sb.ToString());
        }
        public static void Delete(this Object obj, string _DataBase, string _Table, string where)
        {
            string lcSql = "DELETE FROM " + DB.SQLDatabase(_DataBase, _Table);
            
            if (!string.IsNullOrEmpty(where))
            {
                lcSql += " WHERE " + where;
                DB.SQLExec(lcSql);
            }
        }
        public static void Delete(this Object obj, string where)
        {
            obj.Delete(obj.GetPropertyByName<string>("_DataBase"), obj.GetPropertyByName<string>("_Table"), where);
        }
        public static void Update(this Object obj, string _DataBase, string _Table, string where)
        {
            string lcSql = " UPDATE " + DB.SQLDatabase(_DataBase, _Table) +
                                 " SET " + "%%CAMPOSVALORES%%" +
                                    " WHERE " + where;

            StringBuilder sbCamposValores = new StringBuilder();
            Type myType = obj.GetType();
            IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties().Where(r => r.Name.Substring(0, 1) != "_"));
            List<string> _flds = (List<string>)obj.GetType().GetField("_flds").GetValue(obj);

            foreach (PropertyInfo prop in props)
            {
                AttributeCollection attributes = TypeDescriptor.GetProperties(obj)[prop.Name].Attributes;
                //DefaultValueAttribute myAttribute = (DefaultValueAttribute)attributes[typeof(DefaultValueAttribute)];
                object valor = prop.GetValue(obj, null);

                //if (myAttribute != null && System.Collections.Comparer.DefaultInvariant.Compare(myAttribute.Value,valor)!=0)
                if (_flds.Contains(prop.Name))
                {
                    sbCamposValores.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " , ");
                }
                /*else
                {
                    switch (prop.PropertyType.Name)
                    {
                        case "DateTime":
                            {
                                DateTime dateTime = new DateTime(1, 1, 1, 0, 0, 0);
                                if (prop.PropertyType.Name == "DateTime" && dateTime.CompareTo(Convert.ToDateTime(valor)) != 0)
                                {
                                    sbCamposValores.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " , ");
                                }
                            }
                            break;                        
                    }
                }*/
            }
            sbCamposValores.Remove(sbCamposValores.Length - 2, 2);
            lcSql = lcSql.Replace("%%CAMPOSVALORES%%", sbCamposValores.ToString());
                      
            DB.SQLExec(lcSql);
            _flds.Clear();
        }
        public static void Update(this Object obj)
        {
            string _DataBase = obj.GetPropertyByName<string>("_DataBase");
            string _Table = obj.GetPropertyByName<string>("_Table").ToString();
            string lcSql = " UPDATE " + DB.SQLDatabase(_DataBase, _Table) +
                                 " SET " + "%%CAMPOSVALORES%%" +
                                    " WHERE %%WHERE%%";

            StringBuilder sbKeys = new StringBuilder();
            string[] keys = GetPropertyByName<string[]>(obj, "_keys");
            if (keys.Length == 0)
                return;
            List<string> _flds = (List<string>)obj.GetType().GetField("_flds").GetValue(obj);
                //GetPropertyByName<List<string>>(obj, "_flds");

            // Localizamos el registro por los valores de las _keys
            StringBuilder sb = new StringBuilder();
            foreach (string key in keys)
            {
                PropertyInfo prop = obj.GetPropertyInfoByName(key);
                object valor = prop.GetValue(obj, null);
                //if (!valor.IsNullOrEmpty())
                if(_flds.Contains(prop.Name))
                {
                    sb.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " and ");
                }
            }
            sb.Remove(sb.Length - 4, 4);
            obj.Update(_DataBase, _Table, sb.ToString());
        }
        public static void Load(this Object obj, DataRow row)
        {
            foreach (DataColumn column in row.Table.Columns)
            {
                Type _type = Type.GetType(obj.GetType().Namespace + "." + obj.GetType().Name);
                PropertyInfo _propertyInfo = _type.GetProperty(column.ColumnName.ToUpper());

                if (_propertyInfo != null && row[column.ColumnName] != DBNull.Value)
                {
                    object propertyValue = System.Convert.ChangeType(row[column.ColumnName], _propertyInfo.PropertyType);
                    _propertyInfo.SetValue(obj, propertyValue, null);
                }
            }
        }

        public static bool Find(this Object obj, string where = "")
        {
            DataTable dt = new DataTable();
            string lcSql = " SELECT Count(*) as num FROM " + DB.SQLDatabase(obj.GetPropertyByName<string>("_DataBase"), obj.GetPropertyByName<string>("_Table")) + " WHERE ";
            if (string.IsNullOrEmpty(where))
            {
                string[] keys = obj.GetPropertyByName<string[]>("_keys");
                StringBuilder sb = new StringBuilder();
                foreach (string key in keys)
                {
                    PropertyInfo prop = obj.GetPropertyInfoByName(key);
                    object valor = prop.GetValue(obj, null);
                    if (!valor.IsNullOrEmpty())
                    {
                        sb.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " and ");
                    }
                }
                sb.Remove(sb.Length - 4, 4);
                lcSql += sb.ToString();
            }
            else
            {
                lcSql += where;
            }
            DB.SQLExec(lcSql, ref dt);

            return dt.Rows.Count > 0 && Convert.ToInt32(dt.Rows[0]["num"]) > 0;
        }
        public static DataTable GetDataTable(this Object obj, string[] columns = null, string where = "", string joins="", string orderby = "")
        {
            string _DataBase = obj.GetPropertyByName<string>("_DataBase");
            string _Table = obj.GetPropertyByName<string>("_Table").ToString();
            DataTable ret = new DataTable();
            StringBuilder sb = new StringBuilder();
            if (columns == null)
            {
                Type myType = obj.GetType();
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties().Where(r => r.Name.Substring(0, 1) != "_"));

                foreach (PropertyInfo prop in props)
                {
                    sb.Append(prop.Name + ", ");
                }
            }
            else
            {
                foreach (string column in columns)
                {
                    sb.Append(column + ", ");
                }
            }
            sb.Remove(sb.Length - 2, 2);

            string lcSql = "SELECT " + sb.ToString() + " FROM " + DB.SQLDatabase(_DataBase, _Table) + " A ";
            if (!string.IsNullOrEmpty(joins))
            {
                lcSql += joins;
            }
            if (!string.IsNullOrEmpty(where))
            {
                lcSql += " where " + where;
            }
            if (!string.IsNullOrEmpty(orderby))
            {
                lcSql += " ORDER BY " + orderby;
            }
            DB.SQLExec(lcSql, ref ret);
            return ret;
        }
        public static BindingList<T> ToBindingList<T>(this Object obj, string[] columns = null, string where = "", string join = "")
        {
            BindingList<T> ret = new BindingList<T>();
            DataTable dt = obj.GetDataTable(columns, where, join );
            List<T> s = dt.ToLista<T>();
            return new BindingList<T>(s);
        }
        public static List<T> ToLista<T>(this Object obj, string[] columns = null, string where = "", string join = "")
        {
            BindingList<T> ret = new BindingList<T>();
            DataTable dt = obj.GetDataTable(columns, where, join);

            return dt.ToLista<T>();
        }
        public static List<T> ToLista<T>(this DataTable dt)
        {
            List<T> data = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                T item = GetItem<T>(row);
                data.Add(item);
            }
            return data;
        }
        public static T GetItem<T>(DataRow dr)
        {
            Type temp = typeof(T);
            T obj = Activator.CreateInstance<T>();

            foreach (DataColumn column in dr.Table.Columns)
            {
                foreach (PropertyInfo pro in temp.GetProperties())
                {
                    if (pro.Name == column.ColumnName)
                    {
                        pro.SetValue(obj, (dr[column.ColumnName] == DBNull.Value?null: dr[column.ColumnName]), null);
                        break;
                    }
                    else
                        continue;
                }
            }
            return obj;
        }
        private static string FormatValue(string propertyType, object propValue)
        {
            string ret = "";
            propertyType = propertyType.Replace("system.", "");
            if (propertyType == "int" ||
                    propertyType == "int32" ||
                    propertyType == "decimal" ||
                    propertyType == "numeric")
            {
                ret = propValue.ToString().Replace(",", ".");
            }
            else
                if (propertyType == "string" ||
                     propertyType == "datetime" ||
                     propertyType == "char" ||
                     propertyType == "smalldatetime" ||
                     propertyType == "text" ||
                     propertyType == "ntext")
            {
                ret = DB.SQLString(propValue);
            }
            else
                if (propertyType == "bool" ||
                    propertyType == "boolean" ||
                    propertyType == "bit")
            {
                ret = Convert.ToBoolean(propValue) ? "1" : "0";
            }
            return ret;
        }
        public static T GetPropertyByName<T>(this object input, string name)
        {
            return (T)input
                .GetType()
                .GetProperty(name)?
                .GetValue(input, null);
        }

        public static PropertyInfo GetPropertyInfoByName(this object input, string name)
        {
            PropertyInfo info;
            
            IList<PropertyInfo> props = new List<PropertyInfo>(input.GetType().GetProperties());

            info = (PropertyInfo)props.Single(x => x.Name == name.Trim());

            return info;
        }
        public static string GetWhere(this Object obj)
        {
            string[] keys = GetPropertyByName<string[]>(obj, "_keys");
            if (keys.Length == 0)
                return "";

            // Localizamos el registro por los valores de las _keys
            StringBuilder sb = new StringBuilder();
            foreach (string key in keys)
            {
                PropertyInfo prop = obj.GetPropertyInfoByName(key);
                object valor = prop.GetValue(obj, null);
                if (!valor.IsNullOrEmpty())
                {
                    sb.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " and ");
                }
            }
            sb.Remove(sb.Length - 4, 4);
            return sb.ToString();
        }
        public static string GetWhereExt(this Object obj)
        {
            //Tuple<string, string> ret = obj.GetNamesValues();

            //StringBuilder sb = new StringBuilder();
            //string[] names = ret.Item1.Split(',');
            //string[] values = ret.Item2.Split(',');
            //for (int i=0; i<names.Length; i++)
            //{
            //    string name = names[i].Trim();
            //    string valor = values[i].Trim();
            //    PropertyInfo prop = obj.GetPropertyInfoByName(name);
            //    sb.Append(name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " and ");
            //}
            //sb.Remove(sb.Length - 4, 4);
            //return sb.ToString();
            return "";
        }
        public static Tuple<string, string> GetNamesValues(this Object obj)
        {
            StringBuilder sbNames = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            Type myType = obj.GetType();
            IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties().Where(r => r.Name.Substring(0, 1) != "_"));
            List<string> _flds = (List<string>)obj.GetType().GetField("_flds").GetValue(obj);

            foreach (PropertyInfo prop in props)
            {
                object valor = prop.GetValue(obj, null);
                //AttributeCollection attributes = TypeDescriptor.GetProperties(obj)[prop.Name].Attributes;
                //DefaultValueAttribute myAttribute = (DefaultValueAttribute)attributes[typeof(DefaultValueAttribute)];

                //if (myAttribute != null && System.Collections.Comparer.DefaultInvariant.Compare(myAttribute.Value, valor) != 0)
                if (_flds.Contains(prop.Name))
                {
                    sbNames.Append(prop.Name + ", ");
                    sbValues.Append(FormatValue(prop.PropertyType.ToString().ToLower(), valor) + ", ");
                    //sbCamposValores.Append(prop.Name + " = " + FormatValue(prop.PropertyType.ToString().ToLower(), valor) + " , ");
                }
                else
                {
                    switch (prop.PropertyType.Name)
                    {
                        case "DateTime":
                            {
                                DateTime dateTime = new DateTime(1, 1, 1, 0, 0, 0);
                                if (prop.PropertyType.Name == "DateTime" && dateTime.CompareTo(Convert.ToDateTime(valor)) != 0)
                                {
                                    sbNames.Append(prop.Name + ", ");
                                    sbValues.Append(FormatValue(prop.PropertyType.ToString().ToLower(), valor) + ", ");
                                }
                            }
                            break;
                    }
                }
            }

            sbNames.Remove(sbNames.Length - 2, 2);   // quitar las últimas comas
            sbValues.Remove(sbValues.Length - 2, 2);

        //    _flds.Clear();
            return new Tuple<string,string>(sbNames.ToString(), sbValues.ToString());            
        }
        
        public static string InnerJoin(this Object obj, string asobj, string rel1, Object obj2, string asobj2, string rel2)
        {
            // Pendiente de desarrollar
            string lcSql = ""; 
            

            return lcSql;
        }
        public static T GetFieldValue<T>(this Object obj, string fieldName, string where)
        {
            T ret = default(T);

            DataTable dt = obj.GetDataTable(new string[] { fieldName }, where);

            if (dt != null && dt.Rows.Count == 1)
            {
                ret = (T)Convert.ChangeType(dt.Rows[0][fieldName], typeof(T) );
            }
            return ret;
        }
        public static void AddExt<T>(this List<T> lista, T dato)
        {
            if (!lista.Contains(dato))
            {
                lista.Add(dato);
            }
        }
    }
}