using Dapper;
using Siger.Middlelayer.Dapper.Data;
using System;
using System.Collections.Generic;

namespace Siger.Middlelayer.Dapper.FlowMetek
{
    public class ErpRepository : RepositoryBase
    {
        public ErpRepository() : base()
        {
        }
        public IEnumerable<MetekErpWoType> GetMetekErpWoType(string createDate)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $"SELECT TOP 1000 RTRIM(MQ001) as Code,RTRIM(MQ002) as Name ,RTRIM(CREATE_DATE) CreateDate,RTRIM(MODI_DATE) as ModifyDate FROM CMSMQ";
                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE RTRIM(CREATE_DATE)>='{createDate}'";
                }
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";

                return conn.Query<MetekErpWoType>(sql);
            }
        }

        /// <summary>
        /// 同步订单数据 创建
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MetekErpWo> GetMetekErpWoInfo(string createDate, string modifyDate,bool modify=false)
        {
            using (var conn = GetDbConnection())
            {
                if (modify)
                {
                    var sql = $"SELECT TOP 1500 RTRIM(TA001) AS WoType,RTRIM(TA002) as Wo,RTRIM(TA015) as ExpectQty,RTRIM(TA006) as ProductCode,RTRIM(TA034) as ProductName,RTRIM(TA035) as Spc,RTRIM(TA007) AS Unit, RTRIM(TA009) as ExpectDate,CREATOR as userid,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate FROM MOCTA ";
                    var condtion = string.Empty;
                    if (!string.IsNullOrEmpty(modifyDate))
                    {
                        sql += $" WHERE MODI_DATE > CREATE_DATE and RTRIM(MODI_DATE)>='{modifyDate}'";
                    }
                    sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                    return conn.Query<MetekErpWo>(sql);
                }
                else
                {
                    var sql = $"SELECT TOP 1500 RTRIM(TA001) AS WoType,RTRIM(TA002) as Wo,RTRIM(TA015) as ExpectQty,RTRIM(TA006) as ProductCode,RTRIM(TA034) as ProductName,RTRIM(TA035) as Spc,RTRIM(TA007) AS Unit, RTRIM(TA009) as ExpectDate,CREATOR as userid,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate FROM MOCTA ";
                    var condtion = string.Empty;
                    if (!string.IsNullOrEmpty(createDate))
                    {
                        sql += $" WHERE RTRIM(CREATE_DATE)>='{createDate}'";
                    }
                    sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                    return conn.Query<MetekErpWo>(sql);
                }
            }
        }
        /// <summary>
        ///  同步订单数据SN 创建
        /// </summary>
        /// <param name="createDate"></param>
        /// <param name="modifyDate"></param>
        /// <returns></returns>
        public IEnumerable<MetekErpWoSn> GetMetekErpWoSns(string createDate, string modifyDate, bool modify = false)
        {
            using (var con = GetDbConnection())
            {
                if (modify)
                {
                    var sql = " SELECT TOP 1500 RTRIM(MC001) as WoType, RTRIM(MC002) as Wo,RTRIM(MC004) AS Sn,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate FROM PSMMC";
                    if (!string.IsNullOrEmpty(modifyDate))
                    {
                        sql += $" WHERE WHERE MODI_DATE > CREATE_DATE and RTRIM(MODI_DATE)>='{modifyDate}'";
                    }
                    sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                    return con.Query<MetekErpWoSn>(sql);
                }
                else
                {
                    var sql = " SELECT TOP 1500 RTRIM(MC001) as WoType, RTRIM(MC002) as Wo,RTRIM(MC004) AS Sn,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate FROM PSMMC";
                    if (!string.IsNullOrEmpty(createDate))
                    {
                        sql += $" WHERE RTRIM(CREATE_DATE)>='{createDate}'";
                    }
                    sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                    return con.Query<MetekErpWoSn>(sql);
                }
            }
        }

        /// <summary>
        /// 物料类别
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MetekErpMaterialType> GetMetekMaterialType(string createDate, string modifyDate)
        {
            using (var conm = GetDbConnection())
            {
                var sql = $"SELECT TOP 1000 MA001 parentid ,MA002 tcode,MA003 name,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate  FROM INVMA ";
                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE CREATE_DATE>='{createDate}'";
                }
               
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                return conm.Query<MetekErpMaterialType>(sql);
            }
        }

        /// <summary>
        /// 物料信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MeteErpMaterial> GetMeteMaterial(string createDate, string modifyDate)
        {
            using (var col = GetDbConnection())
            {
                var sql = $"SELECT TOP 1000 MB002 name,MB001 pn,MB003 spec ,MB005 typeid ,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate  from INVMB ";
                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE CREATE_DATE>='{createDate}'";
                }
               
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                return col.Query<MeteErpMaterial>(sql);
            }
        }

        /// <summary>
        /// 部门信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MeteErpSection> GetMetekErpSection(string createDate, string modifyDate)
        {
            using (var cou = GetDbConnection())
            {
                var sql = $"SELECT  TOP 1000 ME001 role_id ,ME002 title ,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate  FROM CMSME ";
                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE CREATE_DATE>='{createDate}'";
                }
            
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                return cou.Query<MeteErpSection>(sql);
            }
        }

        /// <summary>
        /// 岗位信息
        /// </summary>
        /// 
        /// <returns></returns>
        public IEnumerable<MeteErpUseGroup> GetMetekErpUseGroup(string createDate, string modifyDate)
        {
            using (var cop = GetDbConnection())
            {
                var sql = $"SELECT TOP 1000 MJ001 role_id,MJ002 title ,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate  FROM CMSMJ ";
                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE CREATE_DATE>='{createDate}'";
                }
             
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                return cop.Query<MeteErpUseGroup>(sql);
            }
        }

        /// <summary>
        /// 人员信息
        /// </summary>
        public IEnumerable<MeteErpUser> GetMeteUser(string createDate, string modifyDate)
        {
            using (var cok = GetDbConnection())
            {
                var sql = $"select TOP 1000 MV002 name,MV001 work_code,MV004 sectionid,MV016 mobile ,MV007 sex,RTRIM(CREATE_DATE) as CreateDate,RTRIM(MODI_DATE) as ModifyDate  from CMSMV ";

                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE CREATE_DATE>='{createDate}'";
                }
               
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                return cok.Query<MeteErpUser>(sql);
            }
        }

        public IEnumerable<MetekErpBatch> GetBatchrRecord(string createDate, string modifyDate)
        {
            using (var cok = GetDbConnection())
            {
                var sql = $"SELECT TOP 1000 TE011 as WoType,TE012 as Wo,TE004 as Pn,TE005 AS Qty,TE010 as Batch,TE017 as Pname,TE018 as Type,CREATE_DATE as CreateDate,MODI_DATE as ModifyDate FROM MOCTE";
                var condtion = string.Empty;
                if (!string.IsNullOrEmpty(createDate))
                {
                    sql += $" WHERE CREATE_DATE>='{createDate}'";
                }
              
                sql += " ORDER BY CREATE_DATE ,MODI_DATE ASC;";
                return cok.Query<MetekErpBatch>(sql);
            }
        }

        public override void Dispose()
        {
            throw new NotImplementedException();
        }

    }
}