RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSMbarcodeLOTDAL.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 ICSMbarcodeLOTDAL
    {
    //    public static string EATTRIBUTE3RMC = "人民厂条码";
    //    public static string EATTRIBUTE3LX = "良信条码";
    //    public static string EATTRIBUTE3INV = "产品条码";
        #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;
                    if (info.TYPE == "I02")
                    {
                        line.TYPE = "半成品";
                    }
                    else
                    {
                        line.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 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;

                    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.rdrecords = db.ICSMO.SingleOrDefault(a => a.MOCODE == CODE && a.MOSEQ== RowNo && a.WorkPoint == AppConfig.WorkPointCode);
                if (info.rdrecords != null)
                    info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.rdrecords.ITEMCODE && 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.rdrecords.MOCODE + info.rdrecords.MOSEQ) && 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.MOCODE,
	                                a.MOSEQ,
	                                a.MOPLANQTY,
	                                a.ITEMCODE,
	                                b.INVNAME,
                                    b.INVCLASS,
	                                b.INVSTD
                                FROM
	                                ICSMO a
                                LEFT JOIN ICSINVENTORY b ON a.ITEMCODE=b.INVCODE
                                WHERE
                                a.MOCODE='{0}'
                                AND a.MOSEQ='{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.MOCODE,
	                        b.MOSEQ,
	                        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,
                            d.INVUOM
                        FROM
	                        ICSITEMLot a
	                      LEFT JOIN ICSMO b ON a.TransNO=b.MOCODE AND a.TransLine=b.MOSEQ 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.ITEMCODE=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
    }
}