using System.Data;
using System.Dynamic;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using SqlSugar;
using DbType = System.Data.DbType;

namespace MES.Service.service.Warehouse;

public class OrganizeService : RepositoryNoEntity
{
    public List<Organize> GetOrganizes()
    {
        return Db.Queryable<Organize>()
            .GroupBy(s => new
            {
                s.Fname, s.Fnumber
            }).Select<Organize>(s => new Organize
            {
                Fname = s.Fname,
                Fnumber = s.Fnumber,
                Id = SqlFunc.AggregateMax(s.Id)
            }).ToList();
    }

    public List<MesRfPrnbarcode> GetBarcodeQcok(WarehouseQuery query)
    {
        var printCommandResult = Db.Queryable<MesBarcodeType>()
            .Where(t => t.TypeCode == 108)
            .First();
        if (printCommandResult == null) throw new Exception("模版ID为空，请设置打印格式！");

        var cPrintCommand = printCommandResult.Mbtype;
        var cBid = printCommandResult.Id;

        var cMesItems = Db.Queryable<MesItems>()
            .Where(s =>
                s.ItemNo == query.ItemNo && s.Factory == "1000" &&
                s.Company == "1000")
            .First();
        if (cMesItems == null) throw new Exception("物料信息不存在该物料编码，请维护！");

        if (string.IsNullOrEmpty(query.ItemNo)) throw new Exception("请录入物料编码");

        if (string.IsNullOrEmpty(query.ItemModel))
            throw new Exception("请输入完物料编码后，点击确认生成规格！");

        if (string.IsNullOrEmpty(query.OrgName)) throw new Exception("请录入组织");

        if (string.IsNullOrEmpty(query.OrgOwner)) throw new Exception("请录入货主");

        if (string.IsNullOrEmpty(query.ItemNum)) throw new Exception("请录入物料数量");

        if (string.IsNullOrEmpty(query.Printnumn))
            throw new Exception("请录入条码数量");

        var cId =
            Db.Ado.GetInt(
                "SELECT F_GETSEQNEXTVALUE('MES_RF_PRNBARCODE') FROM DUAL");

        string itemBarcode = null;
        try
        {
            Db.Ado.BeginTran();
            //删除10天前的资料，防止表过大
            Db.Deleteable<MesRfPrnbarcode>()
                .Where(it => it.CreateDate < DateTime.Now.AddDays(-10))
                .ExecuteCommand();

            for (var i = 1; i <= int.Parse(query.Printnumn); i++)
            {
                var cIndate = DateTime.Now.ToString("yyyyMMdd");

                string serial = null;
                string poBarcodeComand = null;
                string cBarcode2 = null;
                if (Db.Ado.GetInt(
                        "SELECT GETSYSLOOKUPVALUE('1000', '1000', 'ItemBarcodeType') FROM DUAL") ==
                    0)
                {
                    var barcodeResult = SpGetBarcode2("1000", "1000", cBid,
                        query.ItemNo + cIndate, query.ItemNo + cIndate, 0);
                    serial = barcodeResult.Value;
                    poBarcodeComand = barcodeResult.Msg;
                    if (!string.IsNullOrEmpty(poBarcodeComand)) return null;

                    if (string.IsNullOrEmpty(serial))
                        cBarcode2 = "9";
                    else
                        cBarcode2 = "9" + serial[1..];
                    itemBarcode = query.ItemNo + cIndate + cBarcode2;
                }
                else
                {
                    itemBarcode = Db.Queryable<MesInvItemBarcodes>()
                        .Where(s =>
                            s.LotNo == cIndate && s.ItemNo == query.ItemNo &&
                            s.TrLotno == cIndate &&
                            s.Mblnr == null && s.Memo == "初始库存" &&
                            s.Factory == "1000" && s.Company == "1000")
                        .Select(s => s.ItemBarcode)
                        .First();
                    if (string.IsNullOrEmpty(itemBarcode))
                    {
                        var barcodeResult = SpGetBarcode2("1000", "1000", cBid,
                            query.ItemNo + cIndate, query.ItemNo + cIndate, 0);
                        serial = barcodeResult.Value;
                        poBarcodeComand = barcodeResult.Msg;
                        if (!string.IsNullOrEmpty(poBarcodeComand)) return null;

                        cBarcode2 = "9" + serial.Substring(1);
                        itemBarcode = query.ItemNo + cIndate +
                                      cBarcode2;
                    }
                }

                var poBarcode = itemBarcode;

                // Check if barcode exists
                var cCount = Db.Queryable<MesInvItemBarcodes>()
                    .Where(s =>
                        s.ItemBarcode == itemBarcode && s.Factory == "1000" &&
                        s.Company == "1000")
                    .Count();
                if (cCount == 0)
                    // Insert new barcode
                    Db.Insertable(new MesInvItemBarcodes
                    {
                        ItemBarcode = poBarcode,
                        ItemNo = query.ItemNo,
                        Quantity = int.Parse(query.ItemNum),
                        CreateBy = query.userName,
                        CreateDate = DateTime.Now,
                        LastupdateBy = query.userName,
                        LastupdateDate = DateTime.Now,
                        ItemSname = cMesItems.ItemName,
                        Factory = "1000",
                        Company = "1000",
                        LotDate = DateTime.Now.ToString("yyyy-MM-dd"),
                        Memo = "初始库存",
                        OrgName = query.OrgName,
                        OrgOwner = query.OrgOwner
                    }).ExecuteCommand();

                // Insert record into MES_RF_PRNBARCODE
                Db.Insertable(new MesRfPrnbarcode
                {
                    Id = cId,
                    Forder = "1",
                    Fno = "1",
                    CreateDate = DateTime.Now,
                    Strp1 = poBarcode,
                    Strp3 = query.ItemNo,
                    Strp4 = query.ItemName ?? " ",
                    Strp5 = query.ItemNum + " " + cMesItems.ItemUnit,
                    Strp6 = DateTime.ParseExact(cIndate, "yyyyMMdd", null)
                        .ToString("yyyy-MM-dd"),
                    Strp7 = query.ItemModel ?? " ",
                    Strp8 = poBarcode,
                    Strp9 = query.ItemModel ?? " ",
                    Strp10 = poBarcode,
                    Strp11 = poBarcode,
                    Strp12 = query.ItemName ?? " ",
                    Strp15 = query.OrgName ?? " ",
                    Strp16 = query.OrgOwner ?? " "
                }).ExecuteCommand();
            }

            Db.Ado.CommitTran();
        }
        catch (Exception ex)
        {
            Db.Ado.RollbackTran();
            throw new Exception("报错" + ex.Message);
        }

        return Db.Queryable<MesRfPrnbarcode>()
            .Where(s => s.Id == cId).ToList();
    }

    private dynamic SpGetBarcode2(string p0, string p1, decimal cBid,
        string cItemNo, string cIndate, int p5)
    {
        try
        {
            // 定义输出参数
            var outputResult = new SugarParameter("PO_VALUE", null,
                DbType.Int32, ParameterDirection.Output,
                4000);

            var outputMessage = new SugarParameter("PO_MSG", null,
                DbType.String,
                ParameterDirection.Output, 4000);

            // 定义输入参数
            var parameters = new List<SugarParameter>
            {
                new("PI_FACTORY", p0,
                    DbType.String, ParameterDirection.Input),
                new("PI_COMPANY", p1, DbType.String,
                    ParameterDirection.Input),
                new("P_BARCODE_TYPE_ID", cBid, DbType.String,
                    ParameterDirection.Input),
                new("P_TASK_NO", cItemNo, DbType.String,
                    ParameterDirection.Input),
                new("P_TASK_ITEM", cIndate, DbType.String,
                    ParameterDirection.Input),
                new("P_IF_COMMIT", p5, DbType.String,
                    ParameterDirection.Input),
                outputResult,
                outputMessage
            };

            // 使用 SqlSugar 执行存储过程
            Db.Ado.ExecuteCommand(
                "BEGIN SP_GET_BARCODE2(:PI_FACTORY, :PI_COMPANY, :P_BARCODE_TYPE_ID, :P_TASK_NO, :P_TASK_ITEM, :P_IF_COMMIT, :PO_VALUE, :PO_MSG); END;",
                parameters.ToArray());

            // 获取输出参数的值
            var resultValue = outputResult.Value?.ToString();
            var messageValue = outputMessage.Value?.ToString();

            dynamic m = new ExpandoObject();
            m.Value = resultValue;
            m.Msg = messageValue;

            return m;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
}