using Dapper;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Tools;

namespace VueWebCoreApi.DLL.DAL
{
    public class SopSearchDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt

        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组


        #region[MES工单查询]
        public static ToMessage MesOrderSearch(string mesorderstus, string wkshopcode, string mesordercode, string sourceorder, string saleordercode, string ordertype, string partcode, string partname, string partspec, int startNum, string creatuser, string datatype, string paydatestartdate, string paydateenddate, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (mesorderstus != "" && mesorderstus != null)
                {
                    search += "and A.status=@mesorderstus ";
                    dynamicParams.Add("@mesorderstus", mesorderstus);
                }
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (mesordercode != "" && mesordercode != null)
                {
                    search += "and A.wo_code like '%'+@mesordercode+'%' ";
                    dynamicParams.Add("@mesordercode", mesordercode);
                }
                if (sourceorder != "" && sourceorder != null)
                {
                    search += "and A.m_po like '%'+@sourceorder+'%' ";
                    dynamicParams.Add("@sourceorder", sourceorder);
                }
                if (saleordercode != "" && saleordercode != null)
                {
                    search += "and W.saleOrderCode like '%'+@saleordercode+'%' ";
                    dynamicParams.Add("@saleordercode", saleordercode);
                }
                if (ordertype != "" && ordertype != null)
                {
                    search += "and A.wotype like '%'+@ordertype+'%' ";
                    dynamicParams.Add("@ordertype", ordertype);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.materiel_code like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and B.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and B.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (creatuser != "" && creatuser != null)
                {
                    search += "and U.username like '%'+@creatuser+'%' ";
                    dynamicParams.Add("@creatuser", creatuser);
                }
                if (paydatestartdate != "" && paydatestartdate != null)
                {
                    switch (datatype)
                    {
                        case "PS":
                            search += "and A.plan_startdate between @paydatestartdate and @paydateenddate ";
                            dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
                            dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
                            break;
                        case "PE":
                            search += "and A.plan_enddate between @paydatestartdate and @paydateenddate ";
                            dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
                            dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
                            break;
                        case "ED":
                            search += "and A.saleOrderDeliveryDate between @paydatestartdate and @paydateenddate ";
                            dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
                            dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
                            break;
                        case "CT":
                            search += "and A.lm_date between @paydatestartdate and @paydateenddate ";
                            dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
                            dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
                            break;
                        default:
                            break;
                    }
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.route_code,B.default_route,R.name as route_name,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,C.islastreport,
                            A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date,A.data_sources,A.isstep,A.clerkuser,
                            B.priuserdefnvc1,B.priuserdefnvc2,B.priuserdefnvc3,B.priuserdefnvc4,B.priuserdefnvc5,B.priuserdefnvc6,A.printcount
                            from TK_Wrk_Man A
                            left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TOrganization C on A.wkshp_code=C.torg_code
                            left join TSecStck D on A.stck_code=D.code 
                            left join TUser U on A.lm_user=U.usercode 
                            left join TOrganization L on  C.parent_id=L.id
                            left join TFlw_Rout R on A.route_code=R.code
                            where A.is_delete<>'1' and A.status<>'CLOSED' and isstep='Y' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[MES工单编辑获取工序数据]
        public static ToMessage UpdateMesOrderStepSearch(string sourceid, string sourcewo, string wocode, string data_sources)
        {
            string sql = ""; decimal canupdate_qty = 0;
            var dynamicParams = new DynamicParameters();
            Dictionary<object, object> dir = new Dictionary<object, object>();
            try
            {
                if (data_sources == "ERP")  //数据来源ERP
                {
                    //查询订单任务总数
                    sql = @"select qty,relse_qty  from TKimp_Ewo where id=@sourceid and wo=@sourcewo";
                    dynamicParams.Add("@sourceid", sourceid);
                    dynamicParams.Add("@sourcewo", sourcewo);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    //查询当前工单可修改数量=订单总数-已下达工单总数
                    sql = @"select isnull(sum(plan_qty),0) as plan_qty   from TK_Wrk_Man 
                            where sourceid=@sourceid and m_po=@sourcewo and wo_code<>@wocode";
                    dynamicParams.Add("@sourceid", sourceid);
                    dynamicParams.Add("@sourcewo", sourcewo);
                    dynamicParams.Add("@wocode", wocode);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    //当前工单可修改数量=订单数量-非当前工单总下达工单数量
                    canupdate_qty = decimal.Parse(data0.Rows[0]["qty"].ToString()) - decimal.Parse(data.Rows[0]["plan_qty"].ToString());
                }
                if (data_sources == "MES")  //数据来源MES
                {
                    if (sourceid == "" || sourceid == null) //无源单
                    {
                        //查询当前工单可修改数量=工单总数
                        sql = @"select plan_qty   from TK_Wrk_Man where wo_code=@wo_code";
                        dynamicParams.Add("@wo_code", wocode);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        //当前工单可修改数量=工单数量
                        canupdate_qty = decimal.Parse(data.Rows[0]["plan_qty"].ToString());
                    }
                    else //有源单(报废补单)
                    {
                        //不控制 标识为-1
                        canupdate_qty = -1;
                    }
                }
                //获取工序信息
                sql = @"select S.wo_code,S.seq,S.step_code,T.stepname,S.stepprice,S.ratio,(isnull(S.good_qty,0)+isnull(S.ng_qty,0)+isnull(S.laborbad_qty,0)+isnull(S.materielbad_qty,0)) as produceq_qty,
                        S.good_qty,S.ng_qty,S.laborbad_qty,S.materielbad_qty,S.plan_qty,(isnull(S.plan_quantity,0)-(isnull(S.good_qty,0)+isnull(S.ng_qty,0)+isnull(S.laborbad_qty,0)+isnull(S.materielbad_qty,0))) as delive_qty,S.isbott,S.isend 
                        from TK_Wrk_Step S
                        left join TStep  T on S.step_code=T.stepcode
                        where S.wo_code=@wocode order by S.seq ";
                dynamicParams.Add("@wocode", wocode);
                var data1 = DapperHelper.selectdata(sql, dynamicParams);
                dir.Add("canupdate_qty", canupdate_qty);
                dir.Add("stepdata", data1);
                mes.code = "200";
                mes.count = data1.Rows.Count;
                mes.message = "查询成功";
                mes.data = dir;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[MES工单查看工艺SOP]
        public static ToMessage MesOrderProcessSopSearch(string materielcode, string routecode, string stepcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //查询系统流程配置
                strProcName = @"select json from TSystemProcConfig";
                DataTable dt0_ = DBHelper.GetTable(strProcName);
                dynamic dynObj = JObject.Parse(dt0_.Rows[0]["json"].ToString()); 
                bool route = dynObj.route;
                if (route) //工艺路线版
                {
                    //获取SOP文件信息
                    sql = @"select filename,filepath,version from TProcessSop
                        where  materielcode=@materielcode and routecode=@routecode and stepcode=@stepcode
                        order by version";
                    dynamicParams.Add("@materielcode", materielcode);
                    dynamicParams.Add("@routecode", routecode);
                    dynamicParams.Add("@stepcode", stepcode);
                }
                else
                {
                    //获取SOP文件信息
                    sql = @"select filename,filepath,version from TProcessSop
                        where  materielcode=@materielcode and stepcode=@stepcode
                        order by version";
                    dynamicParams.Add("@materielcode", materielcode);
                    dynamicParams.Add("@stepcode", stepcode);
                }

                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.count = data.Rows.Count;
                mes.data = data;
                mes.message = "查询成功!";
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}