1.同步ERP存货档案、仓库信息 2.存货分类(查询、新增、修改、删除) 3.存货档案(查询、新增、修改)

yl

2023-08-31 be2a307d9cfaacdbeb169a3ad61637e23a96bebe

 VueWebCoreApi/DLL/DAL/ProductModelDAL.cs

@@ -1,4 +1,5 @@
using Dapper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
@@ -33,6 +34,7 @@
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
                LogHelper.OperateLog("999", "查询", "计量单位组查询数据为：" + JsonConvert.SerializeObject(data), "PC");
            }
            catch (Exception e)
            {
@@ -82,6 +84,7 @@
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
                LogHelper.OperateLog("999", "查询", "查询了计量单位", "PC");
            }
            catch (Exception e)
            {
@@ -230,7 +233,8 @@
                {
                    if (tunittype == "S") //单计量
                    {
                        sql = @"select *  from TUnit where isGroup='0' and name=@name";
                        sql = @"select *  from TUnit where isGroup='0' and code<>@code and name=@name";
                        dynamicParams.Add("@code", listjson[0].unitcode);
                        dynamicParams.Add("@name", listjson[0].unitname);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        if (data.Rows.Count > 0)
@@ -262,7 +266,8 @@
                    }
                    if (tunittype == "M")//多计量
                    {
                        sql = @"select *  from TUnitGroup where name=@name";
                        sql = @"select *  from TUnitGroup where code<>@code and name=@name";
                        dynamicParams.Add("@code", listjson[0].unitcode);
                        dynamicParams.Add("@name", listjson[0].unitname);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        if (data.Rows.Count > 0)
@@ -315,7 +320,7 @@
                                    disabled = listjson[0].children[i].disabled,
                                    idunitgroup = listjson[0].children[i].idunitgroup,
                                    isGroup = listjson[0].children[i].isGroup,
                                    data_sources = data_sources,
                                    data_sources = listjson[0].children[i].data_sources,
                                    lm_user = us.usercode,
                                    lm_date = DateTime.Now.ToString()
                                }
@@ -364,7 +369,7 @@
                if (tunittype == "S") //单计量
                {
                    sql = @"select *  from TMateriel_Info where isSingleUnit='1' and idunit=@idunit";
                    dynamicParams.Add("@idunit", tunitgroupcode);
                    dynamicParams.Add("@idunit", tunitcode);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
@@ -386,7 +391,7 @@
                        }
                    });
                }
                if (tunittype == "M" && (tunitgroupcode == "" || tunitgroupcode == null)) //多计量,且多计量组编码为空
                if (tunittype == "M" && (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null)) //多计量,且多计量组编码不为空,明细编码为空
                {
                    sql = @"select *   from TUnit T
                            inner join TMateriel_Info M on T.idunitgroup=M.idunitgroup and T.code=M.idunit
@@ -413,18 +418,18 @@
                        }
                    });
                    //删除多计量单位组下所有计量单位
                    sql = @"delete TUnit where isSingleUnit='0' and isGroup='1' where idunitgroup=@idunitgroup and data_sources=@data_sources";
                    sql = @"delete TUnit where isSingleUnit='0' and isGroup='1' and idunitgroup=@idunitgroup and data_sources=@data_sources";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            idunitgroup= tunitgroupcode,
                            idunitgroup = tunitgroupcode,
                            data_sources = data_sources
                        }
                    });
                }
                if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null)) //多计量,且多计量组编码不为空
                if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null) && (tunitcode != "" || tunitcode != null)) //多计量,且多计量组编码不为空,明细编码不为空
                {
                    sql = @"select *   from TUnit T
                            inner join TMateriel_Info M on T.idunitgroup=M.idunitgroup and T.code=M.idunit
@@ -441,7 +446,7 @@
                        return mes;
                    }
                    //删除多计量单位组下指定计量单位
                    sql = @"delete TUnit where isSingleUnit='0' and isGroup='1' where idunitgroup=@idunitgroup and code=@code and data_sources=@data_sources";
                    sql = @"delete TUnit where isSingleUnit='0' and isGroup='1' and idunitgroup=@idunitgroup and code=@code and data_sources=@data_sources";
                    list.Add(new
                    {
                        str = sql,
@@ -481,5 +486,487 @@
            return mes;
        }
        #endregion


        #region[存货分类信息]
        public static ToMessage TMaterielClassTree()
        {
            string sql = "";
            try
            {
                //获取存货分类信息
                sql = @"select code,name,idparent,data_sources from TMateriel_Class ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
                LogHelper.OperateLog("999", "查询", "存货分类查询数据为：" + JsonConvert.SerializeObject(data), "PC");
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[存货分类新增/编辑提交]
        public static ToMessage TMaterielClassTreeAddUpdate(string data_sources, string inventoryclasscode, string inventoryclassname, string parentcode, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (parentcode != "" || parentcode != null) //存货分类上级编码为空
                {
                    sql = @"select *  from TMateriel_Info where idinventoryclass=@parentcode";
                    dynamicParams.Add("@parentcode", parentcode);
                    var data0 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data0.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货分类上级不能是已有存货的分类，请重新输入！";
                        mes.data = null;
                        return mes;
                    }
                }
                if (OperType == "Add")
                {
                    //判断存货分类编码是否重复
                    sql = @"select *  from TMateriel_Class where code=@inventoryclasscode";
                    dynamicParams.Add("@inventoryclasscode", inventoryclasscode);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货分类编码已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //判断存货分类名称是否重复
                    sql = @"select *  from TMateriel_Class where name=@inventoryclassname";
                    dynamicParams.Add("@inventoryclassname", inventoryclassname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货分类名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //写入存货分类
                    sql = @"insert into TMateriel_Class(code,name,idparent,lm_user,lm_date,data_sources) 
                            values(@code,@name,@idparent,@lm_user,@lm_date,@data_sources)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = inventoryclasscode,
                            name = inventoryclassname,
                            idparent = parentcode,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货分类", us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (OperType == "Update")
                {
                    //判断存货分类名称是否重复
                    sql = @"select *  from TMateriel_Class where  code<>@inventoryclasscode and name=@inventoryclassname";
                    dynamicParams.Add("@inventoryclasscode", inventoryclasscode);
                    dynamicParams.Add("@inventoryclassname", inventoryclassname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货分类名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //更新存货分类
                    sql = @"update TMateriel_Class set name=@name,idparent=@idparent,lm_user=@lm_user,lm_date=@lm_date where code=@code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = inventoryclasscode,
                            name = inventoryclassname,
                            idparent = parentcode,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货分类", us.usertype);
                        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[存货分类删除]
        public static ToMessage TMaterielClassTreeDelete(string inventoryclasscode, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                string[] classcode = Array.ConvertAll<string, string>(inventoryclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                //判断存货分类是否被存货信息引用
                sql = @"select *  from TMateriel_Info where idinventoryclass in @inventoryclasscode";
                dynamicParams.Add("@inventoryclasscode", classcode);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前存货分类或下级被引用不允许删除！";
                    mes.data = null;
                    return mes;
                }
                //更新存货分类
                sql = @"delete TMateriel_Class  where code in @code";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        code = classcode
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货分类", us.usertype);
                    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[存货信息列表]
        public static ToMessage TMaterielData(string inventoryclasscode, string partcode, string partname, string partspec, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "", sql = "";
            IEnumerable<object> data;
            try
            {
                if (inventoryclasscode != "" && inventoryclasscode != null)
                {
                    string[] classcode = Array.ConvertAll<string, string>(inventoryclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                    search += "and M.idinventoryclass in @classcode";
                    dynamicParams.Add("@classcode", classcode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                sql = @"select M.partcode,M.partname,M.partspec,M.idunit as idunitcode,T.name as idunitname,M.idunitgroup as idunitgroupcode,G.name as idunitgroupname,
                            M.isSingleUnit,M.idSubUnitByReport as idSubUnitByReportcode,R.name as idSubUnitByReportname,M.idUnitByStock as idUnitByStockcode,K.name as idUnitByStockname,
                            M.idUnitByPurchase as idUnitByPurchasecode,P.name as idUnitByPurchasename,M.idUnitBySale as idUnitBySalecode,S.name as idUnitBySalename,
                            M.idunitbymanufacture as idunitbymanufacturecode,F.name as idunitbymanufacturename,M.idinventoryclass as idinventoryclasscode,C.name as idinventoryclassname,
                            M.isPurchase,M.isSale,M.isMadeSelf,M.isMaterial,M.isMadeRequest,M.idwarehouse as idwarehousecode,H.name as idwarehousename,M.lowQuantity,M.topQuantity,M.safeQuantity,
                            M.status,M.lm_user,M.lm_date,M.data_sources
                            from TMateriel_Info M
                            left join TUnit T on M.idunit=T.code
                            left join TUnit R on M.idSubUnitByReport=R.code
                            left join TUnit K on M.idUnitByStock=K.code
                            left join TUnit P on M.idUnitByPurchase=P.code
                            left join TUnit S on M.idUnitBySale=S.code
                            left join TUnit F on M.idunitbymanufacture=F.code
                            left join TMateriel_Class C on M.idinventoryclass=C.code
                            left join TUnitGroup G on M.idunitgroup=G.code
                            left join TWareHouse H on M.idwarehouse=H.code
                            where 1=1 " + search;
                // --------------查询指定数据--------------
                var total = 0; //总条数
                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[存货信息新增/编辑保存]
        public static ToMessage TMaterielAddUpdate(string data_sources, string partcode, string partname, string partspec, string inventoryclasscode, string unittypcode, string unitcode, string unitsubcode, string idSubUnitByReport, string idUnitByStock, string idUnitByPurchase, string idUnitBySale, string idunitbymanufacture, string isPurchase, string isSale, string isMadeSelf, string isMaterial, string isMadeRequest, string idwarehouse, string status, string OperType, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    //判断存货档案编码是否重复
                    sql = @"select *  from TMateriel_Info where partcode=@partcode";
                    dynamicParams.Add("@partcode", partcode);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    if (data.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货档案编码已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //判断存货档案名称是否重复
                    sql = @"select *  from TMateriel_Info where partname=@partname";
                    dynamicParams.Add("@partname", partname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货档案名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //判断存货档案规格是否重复
                    sql = @"select *  from TMateriel_Info where partspec=@partspec";
                    dynamicParams.Add("@partspec", partspec);
                    var data2 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data2.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货档案规格已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //写入存货分类
                    sql = @"insert into TMateriel_Info(partcode,partname,partspec,idunit,idunitgroup,isSingleUnit,idSubUnitByReport,idUnitByStock,idUnitByPurchase,idUnitBySale,idunitbymanufacture,
                            idinventoryclass,isPurchase,isSale,isMadeSelf,isMaterial,isMadeRequest,idwarehouse,status,lm_user,lm_date,data_sources) 
                            values(@partcode,@partname,@partspec,@idunit,@idunitgroup,@isSingleUnit,@idSubUnitByReport,@idUnitByStock,@idUnitByPurchase,@idUnitBySale,@idunitbymanufacture,
                            @idinventoryclass,@isPurchase,@isSale,@isMadeSelf,@isMaterial,@isMadeRequest,@idwarehouse,@status,@lm_user,@lm_date,@data_sources)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            partcode=partcode,
                            partname=partname,
                            partspec=partspec,
                            idunit=unittypcode=="1"?unitcode:unitsubcode,
                            idunitgroup = unittypcode == "0"?"1":"0",
                            isSingleUnit= unittypcode,
                            idSubUnitByReport=idSubUnitByReport,
                            idUnitByStock= idUnitByStock,
                            idUnitByPurchase= idUnitByPurchase,
                            idUnitBySale= idUnitBySale,
                            idunitbymanufacture= idunitbymanufacture,
                            idinventoryclass= inventoryclasscode,
                            isPurchase= isPurchase,
                            isSale= isSale,
                            isMadeSelf= isMadeSelf,
                            isMaterial= isMaterial,
                            isMadeRequest= isMadeRequest,
                            idwarehouse= idwarehouse,
                            status= status,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString(),
                            data_sources = data_sources
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货档案", us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (OperType == "Update")
                {
                    //判断存货档案名称是否重复
                    sql = @"select *  from TMateriel_Info where  partcode<>@partcode and partname=@partname";
                    dynamicParams.Add("@partcode", partcode);
                    dynamicParams.Add("@partname", partname);
                    var data1 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data1.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货档案名称已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //判断存货档案名称是否重复
                    sql = @"select *  from TMateriel_Info where  partcode<>@partcode and partspec=@partspec";
                    dynamicParams.Add("@partcode", partcode);
                    dynamicParams.Add("@partspec", partspec);
                    var data2 = DapperHelper.selectdata(sql, dynamicParams);
                    if (data2.Rows.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "存货档案规格型号已存在,请重新输入!";
                        mes.data = null;
                        return mes;
                    }
                    //更新存货分类
                    sql = @"update TMateriel_Class set partname=@partname,partspec=@partspec,idunit=@idunit,idunitgroup=@idunitgroup,isSingleUnit=@isSingleUnit,idSubUnitByReport=@idSubUnitByReport,idUnitByStock=@idUnitByStock,
                                   idUnitByPurchase=@idUnitByPurchase,idUnitBySale=@idUnitBySale,idunitbymanufacture=@idunitbymanufacture,idinventoryclass=@idinventoryclass,isPurchase=@isPurchase,isSale=@isSale,isMadeSelf=@isMadeSelf,
                                   isMaterial=@isMaterial,isMadeRequest=@isMadeRequest,idwarehouse=@idwarehouse,status=@status,lm_user=@lm_user,lm_date=@lm_date where partcode=@partcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            partcode = partcode,
                            partname = partname,
                            partspec = partspec,
                            idunit = unittypcode == "1" ? unitcode : unitsubcode,
                            idunitgroup = unittypcode == "0" ? "1" : "0",
                            isSingleUnit = unittypcode,
                            idSubUnitByReport = idSubUnitByReport,
                            idUnitByStock = idUnitByStock,
                            idUnitByPurchase = idUnitByPurchase,
                            idUnitBySale = idUnitBySale,
                            idunitbymanufacture = idunitbymanufacture,
                            idinventoryclass = inventoryclasscode,
                            isPurchase = isPurchase,
                            isSale = isSale,
                            isMadeSelf = isMadeSelf,
                            isMaterial = isMaterial,
                            isMadeRequest = isMadeRequest,
                            idwarehouse = idwarehouse,
                            status = status,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货档案", us.usertype);
                        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
    }
}