using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;

namespace IndicadoresCartera {
    public class SPQuery {
        public ClientContext clientContext;
        public Web site;
        public ListItemCreationInformation itemCreateInfo;
        static SPQuery queryService = new SPQuery();
        public SPQuery() {
            string url = ConfigurationManager.AppSettings["url"];
            string user = ConfigurationManager.AppSettings["user"];
            string password = ConfigurationManager.AppSettings["password"];

            SecureString securePassword = new SecureString();
            foreach(char c in password.ToCharArray()) securePassword.AppendChar(c);

            clientContext = new ClientContext(url);
            clientContext.Credentials = new SharePointOnlineCredentials(user, securePassword);
            site = clientContext.Web;

            itemCreateInfo = new ListItemCreationInformation();
        }

        public ListItemCollection queryList(string list, string queryString)
        {

        List queriedList = clientContext.Web.Lists.GetByTitle(list);
        CamlQuery query = new CamlQuery();
        query.ViewXml = queryString;

        ListItemCollection items = queriedList.GetItems(query);
        clientContext.Load(items);
        clientContext.ExecuteQuery();
        return items;
    }

        public bool createItem(string List, ListItem newListItem)
    {
        Clientes inCartera = new Clientes();
        List targetList = site.Lists.GetByTitle(List);

        newListItem.Update();
        clientContext.Load(newListItem);

        try {
            clientContext.ExecuteQuery();
            return true;

        }
        catch (Exception e) {
            Console.WriteLine("error creando item: " + e.Message);
            return false;
        }
    }

        public void getClientes()
    {

        List clientesGet = clientContext.Web.Lists.GetByTitle("Clientes");
        CamlQuery query = new CamlQuery();
        query.ViewXml = "<View><ViewFields><FieldRef Name='ID' /><FieldRef Name='Title' /><FieldRef Name='FechaSolicitud' /><FieldRef Name='FechaAsignacionCodigo' /></ViewFields></View>";
        ;
        ListItemCollection ToDoClientes = clientesGet.GetItems(query);
        clientContext.Load(ToDoClientes);
        clientContext.ExecuteQuery();

        List indicadorCarteraGet = clientContext.Web.Lists.GetByTitle("IndicadorCartera");
        CamlQuery query2 = new CamlQuery();
        query2.ViewXml = "<View><ViewFields><FieldRef Name='ID' /><FieldRef Name='Title' /></ViewFields></View>";
        ListItemCollection ToDoCartera = indicadorCarteraGet.GetItems(query);
        clientContext.Load(ToDoCartera);
        clientContext.ExecuteQuery();

        foreach(var cl in ToDoClientes.ToList())
        {

            if (ToDoCartera.ToList().Count != 0) {
                foreach(var car in ToDoCartera.ToList())
                {
                    var cl2 = Convert.ToString(cl["ID"]);
                    var car2 = Convert.ToString(car["Title"]);
                    if (cl2 != car2) {
                        if (cl["Title"] != null && cl["FechaAsignacionCodigo"] != null && cl["FechaSolicitud"] != null) {
                            List targetList = queryService.site.Lists.GetByTitle("IndicadorCartera");
                            ListItem inCartera = targetList.AddItem(queryService.itemCreateInfo);
                            Console.WriteLine("Creando indicadores: ID: {0} Codigo: {1} FechaSolicitud: {2} Fecha: {3}", cl.Id, cl["Title"], cl["FechaSolicitud"], cl["FechaAsignacionCodigo"]);
                            inCartera["Title"] = cl.Id;
                            inCartera["Codigo"] = cl["Title"];
                            inCartera["FechaSolicitud"] = cl["FechaSolicitud"];
                            inCartera["FechaAsignacionCodigo"] = cl["FechaAsignacionCodigo"];

                            queryService.createItem("IndicadorCartera", inCartera);
                        }
                    }
                }
            }
            else {
                if (cl["Title"] != null && cl["FechaAsignacionCodigo"] != null && cl["FechaSolicitud"] != null) {
                    List targetList = queryService.site.Lists.GetByTitle("IndicadorCartera");
                    ListItem inCartera = targetList.AddItem(queryService.itemCreateInfo);
                    Console.WriteLine("Creando indicadores: ID: {0} Codigo: {1} FechaSolicitud: {2} Fecha: {3}", cl.Id, cl["Title"], cl["FechaSolicitud"], cl["FechaAsignacionCodigo"]);
                    inCartera["Title"] = cl.Id;
                    inCartera["Codigo"] = cl["Title"];
                    inCartera["FechaSolicitud"] = cl["FechaSolicitud"];
                    inCartera["FechaAsignacionCodigo"] = cl["FechaAsignacionCodigo"];

                    queryService.createItem("IndicadorCartera", inCartera);
                }
            }
        }

    }
}