RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/FormICSBatchsQueryDAL.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 FormICSBatchsQueryDAL
    {
        #region 新增和修改
        public static void AddBatch(List<ICSInvBatch> batchInfo, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSInvBatch Batch in batchInfo)
                {
                    bool isNew = false;
                    var line = db.ICSInvBatch.SingleOrDefault(a => a.ID == Batch.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSInvBatch();
                        line.ID = AppConfig.GetGuid();
                    }
                    
                    line.MOCode = Batch.MOCode;
                    line.MORow = Batch.MORow;
                    line.InvCode = Batch.InvCode;
                    line.SSCODE = Batch.SSCODE;
                    line.Batch = Batch.Batch;
                    line.EATTRIBUTE1 = Batch.EATTRIBUTE1;
                    line.MUSER = AppConfig.UserCode;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                    line.WorkPoint = AppConfig.WorkPointCode;

                    db.ICSInvBatch.InsertOnSubmit(line);
                    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();
            #region
            try
            {
                FormICSRdrecord2LOTUIModel info = new FormICSRdrecord2LOTUIModel();
                info.rdrecord = db.ICSINVReceiptDetail.SingleOrDefault(a => a.ReceiptNO == CODE && a.ReceiptLine == Convert.ToInt32(RowNo) && a.WorkPoint == AppConfig.WorkPointCode);
                if(info.rdrecord!=null)
                    info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.rdrecord.ITEMCODE && a.WorkPoint == AppConfig.WorkPointCode);
                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.rdrecord.MOCODE + info.rdrecord.MOSEQ) && a.WorkPoint == AppConfig.WorkPointCode);
                if (infoMaxs.Count() == 0)
                    info.MaxNo = 0;
                else
                {
                    string lotno = infoMaxs.OrderByDescending(a => a.LotNO).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
        }

        public static FormICSRdrecord2LOTUIModelX Search_Info(string CODE, string RowNo, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            #region
            try
            {
                string Row_NO = RowNo.ToString();
                FormICSRdrecord2LOTUIModelX info = new FormICSRdrecord2LOTUIModelX();
                info.icsmo = db.ICSMO.SingleOrDefault(a => a.MOCODE == CODE && a.MOSEQ == Row_NO && a.WorkPoint == AppConfig.WorkPointCode);
                if (info.icsmo != null)
                    info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.icsmo.ITEMCODE && a.WorkPoint == AppConfig.WorkPointCode);
                info.icsitems = db.ICSITEMLot.Where(a => a.ItemCode == info.icsmo.ITEMCODE && a.WorkPoint == AppConfig.WorkPointCode && a.TransNO == info.icsmo.MOCODE && a.TransLine == info.icsmo.MOSEQ).FirstOrDefault();
                var infos = db.ICSITEMLot.Where(a => a.ItemCode == info.icsmo.ITEMCODE && a.WorkPoint == AppConfig.WorkPointCode);
                info.SumQty = Convert.ToDecimal(info.icsmo.MOPLANQTY);
                //info.SumNo = infos.Count();
                try
                {
                    string sql = @"SELECT count(B.ItemCode) as num, b.ItemCode, B.LOTQTY FROM ICSMO M
	                    LEFT JOIN ICSITEMLot B ON M.ITEMCODE = B.ItemCode AND M.WorkPoint=B.WorkPoint AND M.MOCODE = B.TransNO AND CONVERT(int, M.MOSEQ) = B.TransLine
                        LEFT JOIN ICSWareHouseLotInfo C ON B.LOTNO=C.LotNO AND B.WorkPoint=C.WorkPoint
                        WHERE M.MOCODE = '{0}'
                        AND M.MOSEQ = '{1}'
                        AND M.WorkPoint = '{2}' AND B.ItemCode = '{3}'
                        group by b.ItemCode, B.LOTQTY
                        ";
                    sql = string.Format(sql, CODE, RowNo, AppConfig.WorkPointCode, info.icsmo.ITEMCODE);
                    DataTable ds = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                    if (ds.Rows.Count == 0 || ds == null)
                    {
                        info.SumNo = 0;
                    }
                    else
                    {
                        info.SumNo = 0;
                        for (int i = 0; i < ds.Rows.Count; i++)
                        {
                            info.SumNo += Int32.Parse(ds.Rows[i]["num"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                var infoMaxs = db.ICSITEMLot.Where(a => a.TransNO == info.icsmo.MOCODE && a.TransLine == info.icsmo.MOSEQ && a.WorkPoint == AppConfig.WorkPointCode);
                if (infoMaxs.Count() == 0)
                    info.MaxNo = 0;
                else
                {
                    string lotno = infoMaxs.OrderByDescending(a => a.LotNO).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
        }
        #endregion

        #region 通过主表行和子表
       
        public static DataSet SearchData(string CODE, string RowNo, string dsconn)
        {
            try
            {
                string sql = @"SELECT
	                                A.MOCODE,
	                                A.MOSEQ,
                                    A.MOPLANLINE,
	                                '' as RECSTATUS,
	                                '' as TYPE,
	                                A.ITEMCODE,
	                                A.MOPLANQTY,
	                                C.INVNAME,
	                                C.INVSTD
                                FROM ICSMO A
                                LEFT JOIN ICSINVENTORY C ON A.ITEMCODE = C.INVCODE
                                WHERE
                                A.MOCODE='{0}'
                                AND A.MOSEQ='{1}'	
                                AND A.WorkPoint = '{2}' 
                                ";
//                sql += @"SELECT 
//	                        CASE WHEN C.LOTNO IS NULL THEN 'Y' ELSE '' END AS isSelect,
//	                        B.ID,
//	                        M.MOCODE,
//	                        M.MOSEQ,
//	                        B.TransNO,
//	                        B.TransLine,
//	                        B.LOTNO,
//	                        CAST(CASE WHEN C.LotNO IS NOT NULL THEN '1' ELSE '0' END AS BIT) AS isInput,
//	                        B.LOTQTY,
//	                        B.VenderLotNO,
//	                        B.PRODUCTDATE,
//	                        M.MUSERName,
//							A.INVUOM
//                        FROM ICSMO M
//						LEFT JOIN ICSINVENTORY A ON M.ITEMCODE = A.INVCODE
//	                    LEFT JOIN ICSITEMLot B ON M.ITEMCODE = B.ItemCode AND M.WorkPoint=B.WorkPoint AND M.MOCODE = B.TransNO AND CONVERT(int, M.MOSEQ) = B.TransLine
//                        LEFT JOIN ICSWareHouseLotInfo C ON B.LOTNO=C.LotNO AND B.WorkPoint=C.WorkPoint
//                        WHERE M.MOCODE = '{0}'
//                        AND M.MOSEQ = '{1}'
//                        AND M.WorkPoint = '{2}'
//                        ORDER BY  M.MOCODE
//                        ";
                sql = string.Format(sql, CODE, RowNo, AppConfig.WorkPointCode);
                DataSet ds = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

         
    }
}