using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class ProductionManagementDAL
    {
        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[ERP订单查询]
        public static ToMessage ErpOrderSearch(string erporderstus, string erpordercode, string partcode, string partname, string partspec, int startNum, string paydatestartdate, string paydateenddate, string creatuser, string createstartdate, string createenddate, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (erporderstus != "" && erporderstus != null)
                {
                    search += "and A.status=@erporderstus ";
                    dynamicParams.Add("@erporderstus", erporderstus);
                }
                if (erpordercode != "" && erpordercode != null)
                {
                    search += "and A.wo like '%'+@erpordercode+'%' ";
                    dynamicParams.Add("@erpordercode", erpordercode);
                }
                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 (paydatestartdate != "" && paydatestartdate != null)
                {
                    search += "and A.paydate between @paydatestartdate and @paydateenddate ";
                    dynamicParams.Add("@paydatestartdate", paydatestartdate);
                    dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
                }
                if (createstartdate != "" && createstartdate != null)
                {
                    search += "and A.createdate between @paydatestartdate and @paydateenddate ";
                    dynamicParams.Add("@createstartdate", createstartdate);
                    dynamicParams.Add("@createenddate", createenddate + " 23:59:59");
                }
                if (creatuser != "" && creatuser != null)
                {
                    search += "and A.createuser like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.status,A.wo,A.materiel_code as partcode,B.partname,B.partspec,A.qty,A.relse_qty,A.wkshp_code,C.org_name as wkshp_name,
                            A.stck_code,D.name as stck_name,A.paydate,A.createuser,A.createdate 
                            from TKimp_Ewo A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TOrganization C on A.wkshp_code=C.org_code
                            left join T_Sec_Stck D on A.stck_code=D.code " + 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[ERP订单下达]
        public static ToMessage MarkSaveErpOrder(string erporderstus, string erpordercode, string partcode, string wkshopcode, string warehousecode, string erpqty, string markqty, string ordernum, string relse_qty, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //获取拆单数量:向下取整
                decimal cdqty = Math.Floor(decimal.Parse(markqty) / decimal.Parse(ordernum));
                //定义累计下单数量
                decimal sumqty = 0;
                //定义最新生成的工单号
                string wo = "";
                //定义工单流水号
                int num = 0;
                //循环下单单数(生成对应几张MES工单)
                for (int i = 1; i <= Convert.ToInt32(ordernum); i++)
                {
                    sumqty += cdqty;
                    //获取最大单据号
                    if (i == 1)  //首单获取工单号
                    {
                        sql = @"select isnull(max(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code))),0)+1 as worknumb from TK_Wrk_Man where m_po=@erpordercode";
                        dynamicParams.Add("@erpordercode", erpordercode);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        num = Convert.ToInt32(data.Rows[0]["WORKNUMB"].ToString());
                        wo = erpordercode + "_" + num;
                    }
                    else
                    {
                        num = num + 1;
                        wo = erpordercode + "_" + num;
                    }
                    if (i == Convert.ToInt32(ordernum))  //最后一单时
                    {
                        sql = @"insert into TK_Wrk_Man(wo_code,status,wkshp_code,plan_qty,stck_code,materiel_code,m_po,lm_user,lm_date) values(@wo_code,@status,@wkshp_code,@plan_qty,@stck_code,@materiel_code,@m_po,@username,@CreateDate)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                wo_code = wo,
                                status = erporderstus,
                                wkshp_code = wkshopcode,
                                plan_qty = cdqty + (decimal.Parse(markqty) - sumqty),  //末单下单数量=切分数量+(下单数量-累计切分下单数量)
                                stck_code = warehousecode,
                                materiel_code = partcode,
                                m_po = erpordercode,
                                username = username,
                                CreateDate = DateTime.Now.ToString()
                            }
                        });
                        sumqty = sumqty + (decimal.Parse(markqty) - sumqty);
                    }
                    else
                    {

                        sql = @"insert into TK_Wrk_Man(wo_code,status,wkshp_code,plan_qty,stck_code,materiel_code,m_po,lm_user,lm_date) values(@wo_code,@status,@wkshp_code,@plan_qty,@stck_code,@materiel_code,@m_po,@username,@CreateDate)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                wo_code = wo,
                                status = erporderstus,
                                wkshp_code = wkshopcode,
                                plan_qty = cdqty,
                                stck_code = warehousecode,
                                materiel_code = partcode,
                                m_po = erpordercode,
                                username = username,
                                CreateDate = DateTime.Now.ToString()
                            }
                        });
                    }
                }
                if (decimal.Parse(erpqty) == decimal.Parse(markqty) + decimal.Parse(relse_qty))   //如果ERP订单=下单数量+已下单数量,则更新ERP订单表状态为CREATED:已创建
                {
                    sql = @"update  TKimp_Ewo set status='CREATED',relse_qty=relse_qty+@sumqty where wo=@wo";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            wo = erpordercode,
                            sumqty = sumqty
                        }
                    });
                }
                else   //更新ERP订单表状态为CREATING:创建中
                {
                    sql = @"update  TKimp_Ewo set status='CREATING',relse_qty=relse_qty+@sumqty where wo=@wo";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            wo = erpordercode,
                            sumqty = sumqty
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "下达MES工单成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "下达MES工单成功失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[ERP订单关闭]
        public static ToMessage ClosedErpOrder(string erpordercode, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select *  from TK_Wrk_Man where m_po=@erpordercode";
                dynamicParams.Add("@erpordercode", erpordercode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前订单有下达的MES工单,不允许关闭,请先删除或关闭相关工单!";
                    mes.data = null;
                }
                else 
                {
                    //关闭订单
                    sql = @"update  TKimp_Ewo set status='CLOSED' where wo=@wo";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            wo = erpordercode
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "订单关闭成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "订单关闭失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[MES工单查询]
        public static ToMessage ErpOrderSearch(string mesorderstus, string mesordercode, string partcode, string partname, string partspec, int startNum, string creatuser, string createdate, 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 (mesordercode != "" && mesordercode != null)
                {
                    search += "and A.wo like '%'+@mesordercode+'%' ";
                    dynamicParams.Add("@mesordercode", mesordercode);
                }
                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 (createdate != "" && createdate != null)
                {
                    search += "and A.paydate=@createdate ";
                    dynamicParams.Add("@createdate", createdate);
                }
                if (creatuser != "" && creatuser != null)
                {
                    search += "and A.createuser like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.status,A.wo,A.materiel_code as partcode,B.partname,B.partspec,A.qty,A.relse_qty,A.wkshp_code,C.org_name as wkshp_name,
                            A.stck_code,D.name as stck_name,A.paydate,A.createuser,A.createdate 
                            from TKimp_Ewo A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TOrganization C on A.wkshp_code=C.org_code
                            left join T_Sec_Stck D on A.stck_code=D.code " + 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
    }
}