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 Workflow  workflow = new Workflow();
		private readonly Public _public = new Public();
		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();
        private readonly OrderPublic orderPublic  = new OrderPublic();

        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 module = tool.GetDataTable($@" SELECT * from ""workflow_module"" ; ", pgConn);
					var definition = tool.GetDataTable($@"SELECT * FROM workflow_definition WHERE ""Status"" =1 ; ", pgConn);


					//工单
					var pgOrderSql = order.GetOrderISql(row, orderId, org, user);
                    //工单流程
                    var orderModule = module.AsEnumerable().FirstOrDefault(x => x.Field<string>("Code").ToString() == "OrderHandle");
                    //var orderDefinition = definition.AsEnumerable().FirstOrDefault(x => x.Field<string>("Id").ToString() == orderModule["DefinitionId"].ToString());
					var pgOrderWorkflow = workflow.GetWorkflow(row["FSH_FlowID"].ToString(), "CityHotline");
                    var pgOrderWorkflowSql = workflow.GetWorkflowSql(pgOrderWorkflow, row, orderId, org, user, orderModule);
					//发布
					var publicTable = _public.GetOldPublic(row["FSH_FlowID"].ToString());
                    var publicSql = _public.GetOldPublicSql(publicTable, 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 = pgOrderWorkflowSql.Length > 0 ? pgSql + pgOrderWorkflowSql : pgSql;
					pgSql = publicSql.Length > 0 ? pgSql + publicSql : pgSql;
					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 ExecuteInsertOrderPublic(string stTime, string endTime)
        {
            var know = orderPublic.GetOldNotice(stTime, endTime);
            foreach (DataRow item in know.Rows)
            {
                var insert = orderPublic.GetNoticeSql(item);
                if (!string.IsNullOrEmpty(insert))
                {
                    var pgConn = tool.GetConPgSql();
                    // tool.SaveLog("KnowledgeSQL :" + insert + "  ;", 1);
                    tool.Execute(insert, 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);
        }
    }
}