using Abp;
using DataTransmission.Joint;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataTransmission
{
    public class Execute
    {
        private readonly CommonTool tool = new CommonTool();
        private readonly Order order = new Order();
        private readonly Visit visit = new Visit();
        private readonly Delay delay = new Delay();
        private readonly Knowledge knowledge = new Knowledge();
        private readonly Organize organize = new Organize();
        private readonly User user = new User();
        private readonly Notice notice = new Notice();

        public void ExecuteSql(DataTable orderTable)
        {
            try
            {
                DataTable org = GetPgOrganize();
                DataTable user = GetPgUser();
                foreach (DataRow row in orderTable.Rows)
                {
                    var pgConn = tool.GetConPgSql();
                    var pgOrder = tool.GetDataTable($@" SELECT * from ""order"" where ""No"" ='{row["FSH_Code"]}'; ", pgConn);
                    if (pgOrder != null && pgOrder.Rows.Count > 0)
                    {
                        continue;
                    }
                    var orderId = SequentialGuidGenerator.Instance.Create().ToString("D");
                    var VisitId = SequentialGuidGenerator.Instance.Create().ToString("D");

                    //工单
                    var pgOrderSql = order.GetOrderISql(row, orderId, org, user);
                    //回访
                    var appraiseTable = visit.GetVisit(row["FSH_FlowID"].ToString());
                    var visitSql = visit.GetVisitSql(appraiseTable, row, VisitId, orderId, org, user);
                    //延期
                    var delayTable = delay.GetDelay(row["FSH_FlowID"].ToString());
                    var delaySql = delay.GetDelaySql(delayTable, row, orderId, org, user);
                    var pgSql = pgOrderSql;
                    pgSql = visitSql.Length > 0 ? pgSql + visitSql : pgSql;
                    pgSql = delaySql.Length > 0 ? pgSql + delaySql : pgOrderSql;
                    tool.SaveLog("SQL :" + pgSql + "  ;", 1);
                    tool.Execute(pgSql, pgConn);
                }
            }
            catch (Exception e)
            {
                tool.SaveLog(e.ToString(), 1);
                throw;
            }
        }

        public void ExecuteTypeSql()
        {
            var pgConn = tool.GetConPgSql();
            var pgSql = knowledge.GetKnowledgeTypeSql();
            //tool.SaveLog("KnowledgeTypeSQL :" + pgSql + "  ;", 1);
            tool.Execute(pgSql, pgConn);
        }

        public void ExecuteOrganizeSql()
        {
            var pgConn = tool.GetConPgSql();
            var pgSql = organize.GetOrganize();
            tool.SaveLog("OrganizeSQL :" + pgSql + "  ;", 1);
            //tool.Execute(pgSql, pgConn);
        }

        public void ExecuteUserSql()
        {
            var pgConn = tool.GetConPgSql();
            var pgSql = user.GetUserSql();
            tool.SaveLog("UserSQL :" + pgSql + "  ;", 1);
            //tool.Execute(pgSql, pgConn);
        }

        public void ExecuteStaffSql()
        {
            var pgConn = tool.GetConPgSql();
            var pgSql = user.GetStaffNoSql();
            tool.SaveLog("StaffSQL :" + pgSql + "  ;", 1);
            //tool.Execute(pgSql, pgConn);
        }

        /// <summary>
        /// 消息通知
        /// </summary>
        /// <param name="stTime"></param>
        /// <param name="endTime"></param>
        public void ExecuteInsertNotice(string stTime, string endTime)
        {
            var know = notice.GetOldNotice(stTime, endTime);
            foreach (DataRow item in know.Rows)
            {
                var insert = notice.GetNoticeSql(item);
                if (!string.IsNullOrEmpty(insert))
                {
                    var pgConn = tool.GetConPgSql();
                   // tool.SaveLog("KnowledgeSQL :" + insert + "  ;", 1);
                    tool.Execute(insert, pgConn);
                }
            }
        }

        public void ExecuteInsertKnowledge(string stTime, string endTime)
        {
            var know = knowledge.GetKnowledge(stTime, endTime);
            foreach (DataRow item in know.Rows)
            {
                var insert = knowledge.GetKnowledgeSql(item);
                if (!string.IsNullOrEmpty(insert))
                {
                    var pgConn = tool.GetConPgSql();
                    //tool.SaveLog("KnowledgeSQL :" + insert + "  ;", 1);
                    tool.Execute(insert, pgConn);
                }
            }
        }

        public DataTable GetPgOrganize()
        {
            var conn = tool.GetConPgSql();
            var sql = $@"  SELECT * FROM  system_organize  ";
            return tool.GetDataTable(sql, conn);
        }

        public DataTable GetPgUser()
        {
            var conn = tool.GetConPgSql();
            var sql = $@"  SELECT * FROM  ""user""   ";
            return tool.GetDataTable(sql, conn);
        }
    }
}