RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/FormICSRewarehouseDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSSoft.Frame.Data.Entity;
using ICSSoft.Base.Config.DBHelper;
using System.Data;
using System.Data.SqlClient;
using ICSSoft.Base.Config.AppConfig;

namespace ICSSoft.Frame.Data.DAL
{
    public class FormICSRewarehouseDAL
    {

        #region 新增和修改
        public static void Add(List<ICSITEMLot> InfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSITEMLot info in InfoList)
                {
                    var inv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ItemCode && a.WorkPoint == AppConfig.WorkPointCode);
                    if (inv == null)
                        throw new Exception("物料不存在！");

                    var lines = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode);
                    if (lines != null)
                        throw new Exception("条码已存在！");

                    bool isNew = false;
                    var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == info.ID && a.WorkPoint == AppConfig.WorkPointCode);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSITEMLot();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.LotNO = info.LotNO;
                    line.ItemCode = info.ItemCode;
                    line.TransNO = info.TransNO;
                    line.TransLine = info.TransLine;
                    line.VENDORITEMCODE = info.VENDORITEMCODE;
                    line.VENDORCODE = info.VENDORCODE;
                    line.VenderLotNO = info.VenderLotNO;
                    line.PRODUCTDATE = info.PRODUCTDATE;
                    line.LOTQTY = info.LOTQTY;
                    line.EATTRIBUTE3 = info.LOTQTY;
                    line.EATTRIBUTE4 = 0;
                    line.ACTIVE = info.ACTIVE;
                    line.Exdate = info.Exdate;
                    line.TYPE = "退料";

                    line.EATTRIBUTE3 = info.EATTRIBUTE3;

                    line.MUSER = AppConfig.UserCode;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); 
                    line.WorkPoint = AppConfig.WorkPointCode;
                    if (isNew)
                    {
                        db.ICSITEMLot.InsertOnSubmit(line);
                    }

                    db.SubmitChanges();

                }

                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 成品条码新增和修改
        public static void AddInv(List<ICSITEMLot> InfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSITEMLot info in InfoList)
                {
                    var inv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ItemCode && a.WorkPoint == AppConfig.WorkPointCode);
                    if (inv == null)
                        throw new Exception("物料不存在！");

                    var lines = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode);
                    if (lines != null)
                        throw new Exception("条码已存在！");

                    bool isNew = false;
                    var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == info.ID && a.WorkPoint == AppConfig.WorkPointCode);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSITEMLot();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.LotNO = info.LotNO;
                    line.ItemCode = info.ItemCode;
                    line.TransNO = info.TransNO;
                    line.TransLine = info.TransLine;
                    line.VENDORITEMCODE = info.VENDORITEMCODE;
                    line.VENDORCODE = info.VENDORCODE;
                    line.VenderLotNO = info.VenderLotNO;
                    line.PRODUCTDATE = info.PRODUCTDATE;
                    line.LOTQTY = info.LOTQTY;
                    line.EATTRIBUTE3 = info.LOTQTY;
                    line.EATTRIBUTE4 = 0;
                    line.ACTIVE = info.ACTIVE;
                    line.Exdate = info.Exdate;
                    line.TYPE = info.TYPE;

                    line.MUSER = AppConfig.UserCode;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                    line.WorkPoint = AppConfig.WorkPointCode;

                    if (isNew)
                    {
                        db.ICSITEMLot.InsertOnSubmit(line);
                    }

                    db.SubmitChanges();

                }

                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 更新打印信息
        public static void updatePrint(List<ICSITEMLot> InfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSITEMLot info in InfoList)
                {
                    var line = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode);
                    if (line == null)
                    {
                        continue;
                    }
                    if (line.PrintTimes == null)
                    {
                        line.PrintTimes = 1;
                    }
                    else
                    {
                        line.PrintTimes++;
                    }
                      
                    line.lastPrintUSERID = info.lastPrintUSERID;
                    line.lastPrintTime = info.lastPrintTime;
                    line.EATTRIBUTE3 = info.EATTRIBUTE3;
                    db.SubmitChanges();

                }

                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 通过代码、行号查询
        public static FormICSRdrecord2LOTUIModel SearchInfo(string CODE,string RowNo, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            try
            {
                FormICSRdrecord2LOTUIModel info = new FormICSRdrecord2LOTUIModel();

                info.reware = db.ICSMaterialPick.SingleOrDefault(a => a.VouchCode == CODE && a.VouchRow == RowNo && a.WorkPoint == AppConfig.WorkPointCode);
                if (info.reware != null)
                    info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.reware.SubInvCode && a.WorkPoint == AppConfig.WorkPointCode);

                if (info.inventory == null)
                    throw new Exception("物料不存在！");

                var infos = db.ICSITEMLot.Where(a => a.TransNO == CODE && a.TransLine == RowNo&& a.WorkPoint == AppConfig.WorkPointCode);
                info.SumQty = Convert.ToDecimal(infos.AsEnumerable().Sum(a => a.LOTQTY));
                info.SumNo = infos.Count();

                var infoMaxs = db.ICSITEMLot.Where(a => a.LotNO.Contains(info.reware.VouchCode + info.reware.VouchRow) && a.WorkPoint == AppConfig.WorkPointCode);
                if (infoMaxs.Count() == 0)
                    info.MaxNo = 0;
                else
                {
                    string lotno = infoMaxs.OrderByDescending(a => a.LotNO.Substring(a.LotNO.Length - 5)).FirstOrDefault().LotNO;
                    info.MaxNo =Convert.ToInt32(lotno.Substring(lotno.Length - 5));
                }

                if (infos != null && infos.Count() > 0)
                {
                    var VenderLotNO = infos.Where(a => a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.VenderLotNO).Distinct();
                    if (VenderLotNO != null && VenderLotNO.Count()>0)
                    {
                        info.VenderLotNO = VenderLotNO.ToList()[0];
                        return info;
                    }
                }
                var infoTodays = db.ICSITEMLot.Where(a => a.WorkPoint == AppConfig.WorkPointCode && a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.TransNO);
                info.TodayMOCount = infoTodays.Distinct().Count();

                return info;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 通过物料查询
        public static FormICSRdrecord2LOTUIModel SearchInfoInv(string CODE, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            try
            {
                FormICSRdrecord2LOTUIModel info = new FormICSRdrecord2LOTUIModel();

                info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == CODE && a.WorkPoint == AppConfig.WorkPointCode);

                if (info.inventory == null)
                    throw new Exception("物料不存在！");

                var infos = db.ICSITEMLot.Where(a => a.TransNO == CODE && a.WorkPoint == AppConfig.WorkPointCode);
                string time = AppConfig.GetSeverDateTime("yyyy-MM-dd").ToString("yyyyMMdd");
                time = "RP" + time.Substring(2);
                var infoMaxs = db.ICSITEMLot.Where(a => a.LotNO.Contains(time) && a.WorkPoint == AppConfig.WorkPointCode);
                if (infoMaxs.Count() == 0)
                    info.MaxNo = 0;
                else
                {
                    string lotno = infoMaxs.OrderByDescending(a => a.LotNO.Substring(a.LotNO.Length - 4)).FirstOrDefault().LotNO;
                    info.MaxNo = Convert.ToInt32(lotno.Substring(lotno.Length - 4));
                }

                if (infos != null && infos.Count() > 0)
                {
                    var VenderLotNO = infos.Where(a => a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.VenderLotNO).Distinct();
                    if (VenderLotNO != null && VenderLotNO.Count() > 0)
                    {
                        info.VenderLotNO = VenderLotNO.ToList()[0];
                        return info;
                    }
                }
                var infoTodays = db.ICSITEMLot.Where(a => a.WorkPoint == AppConfig.WorkPointCode && a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.TransNO);
                info.TodayMOCount = infoTodays.Distinct().Count();

                return info;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 通过主表行和子表
        public static DataSet SearchData(string CODE, string  RowNo, string dsconn)
        {
            try
            {
                string sql = @"SELECT
	                                a.VouchCode,
	                                a.VouchRow,
	                                a.Quantity,
	                                a.SubInvCode,
	                                b.INVNAME,
	                                b.INVSTD,a.VoucherNO
                                FROM
	                                ICSMaterialPick a
                                LEFT JOIN ICSINVENTORY b ON a.SubInvCode=b.INVCODE
                                WHERE
                                a.VouchCode='{0}'
                                AND a.VouchRow='{1}'	
                                AND a.WorkPoint = '{2}' 
                                AND b.WorkPoint = '{2}'
                                ";
                sql += @"SELECT 
	                        CASE WHEN ISNULL(a.PrintTimes,0) <=0 THEN 'Y' ELSE '' END AS isSelect,
	                        a.ID,
	                        b.VouchCode,
	                        b.VouchRow,
	                        a.TransNO,
	                        a.TransLine,
	                        a.LOTNO,
	                        CAST(CASE WHEN c.LotNO IS NOT NULL THEN '1' ELSE '0' END AS BIT) AS isInput,
	                        a.LOTQTY,
                            a.PrintTimes,
	                        --a.VenderLotNO,
	                        a.PRODUCTDATE,
	                        a.MUSERName,


--20190924新增ZM
--b.SupplyQuantity,
--B.Quantity,
--a.EATTRIBUTE3, 
b.VoucherNO as VoucherNO,
--Convert(decimal(18,4),ISNULL(b.SupplyQuantity/b.Quantity,0)) as EATTRIBUTE3,
--直接从ICSITEMLot表中获取辅助计量单位
Convert(decimal(18,4),ISNULL(a.EATTRIBUTE3,0)) as EATTRIBUTE3,

                            d.INVUOM
                        FROM
	                        ICSITEMLot a
	                      LEFT JOIN ICSMaterialPick b ON a.TransNO=b.VouchCode AND a.TransLine=b.VouchRow AND a.WorkPoint=b.WorkPoint
                        LEFT JOIN ICSWareHouseLotInfo c ON a.LOTNO=c.LotNO AND a.WorkPoint=c.WorkPoint
                         LEFT JOIN ICSINVENTORY d ON b.SubInvCode=d.INVCODE
                        WHERE
	                        a.TransNO = '{0}'
                        AND a.TransLine = '{1}'
                        AND a.WorkPoint = '{2}'
                        AND ISNULL(a.EATTRIBUTE2, '') <> 'FP'
                        ORDER BY
	                        a.LOTNO
                        ";


                sql = string.Format(sql,CODE,RowNo,AppConfig.WorkPointCode);
                DataSet ds = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
                    
        }
        #endregion
          
        #region 删除
        public static void deleteInfo(List<string> codeList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSITEMLot.Where(a => codeList.Contains(a.ID));
                db.ICSITEMLot.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion
    }
}