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

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

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSInvTransferDetailDal
    {
        #region AddandEdit
        public static void AddandEdit(List<FormICSInvTransferDetailUIModel> ItemLot, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSInvTransferDetailUIModel Item in ItemLot)
                {
                    bool isNew = false;
                    var line = db.ICSInvTransferDetail.SingleOrDefault(a => a.ID == Item.ID);

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSInvTransferDetail();
                        line.ID = AppConfig.GetGuid();
                        line.TransferID = Item.TransferID;
                        line.TransferNO = Item.TransferNO;
                    }

                    var codes = db.ICSInvTransferDetail.Where(a => a.TransferNO == Item.TransferNO&&a.TransferLine == Item.TransferLine && a.ID != line.ID);
                    if (codes.ToList().Count > 0)
                    {
                        throw new Exception("该单据单行号已存在");
                        
                    }
                    
                    line.TransferLine = Item.TransferLine;
                    line.ORDERNO = Item.ORDERNO;
                    line.ORDERLINE = Item.ORDERLINE;
                    line.TransferSTATUS = Item.TransferSTATUS;
                    line.MEMO = Item.MEMO;
                    line.ITEMCODE = Item.ITEMCODE;
                    line.MOCODE = Item.MOCODE;
                    line.PLANQTY = Convert.ToDecimal(Item.PLANQTY);
                    line.ACTQTY = Convert.ToDecimal(Item.ACTQTY);
                    line.CustomerCode = Item.CustomerCode;

                    line.CUSTOMERNAME = Item.CUSTOMERNAME;
                    line.FROMStorageCode = Item.FROMStorageCode;
                    line.FROMStackCode = Item.FROMStackCode;
                    line.TOStorageCode = Item.TOStorageCode;
                    line.TOStackCode = Item.TOStackCode;
                    line.RECEIVE = Item.RECEIVE;
                    line.RECEIVELINE = Item.RECEIVELINE;
                    line.TransferTIME =Convert.ToDateTime(Item.TransferTIME);
                    line.TransferUSER = Item.TransferUSER;

                    line.MUSER = Item.MUSER;
                    line.MUSERName = Item.MUSERName;
                    line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                    line.WorkPoint = AppConfig.WorkPointCode;
                    line.EATTRIBUTE1 = null;

                    if (isNew) db.ICSInvTransferDetail.InsertOnSubmit(line);

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

        #region 通过入库转移单详细id查询
        public static List<FormICSInvTransferDetailUIModel> SearchInfoByID(string guid, string dsconn)
        {
            List<FormICSInvTransferDetailUIModel> InvTransfer = new List<FormICSInvTransferDetailUIModel>();
            string sql = @"select a.ID as ID,
b.ID as TransferID,
b.TransferNO as TransferNO,
a.TransferLine as TransferLine,
a.ORDERNO as ORDERNO,
a.ORDERLINE as ORDERLINE,
b.TransferSTATUS as TransferSTATUS,
a.MEMO as MEMO,
c.INVCODE as ITEMCODE,
d.MOCODE as MOCODE,
a.PLANQTY as PLANQTY,
a.ACTQTY as ACTQTY,
a.CustomerCode as CustomerCode,
a.CUSTOMERNAME as CUSTOMERNAME,
e.StorageCode as FROMStorageCode,
f.StackCode as FROMStackCode,
g.StorageCode as TOStorageCode,
h.StackCode as TOStackCode,
a.RECEIVE as RECEIVE,
a.RECEIVELINE as RECEIVELINE,
a.TransferTIME as TransferTIME,
a.TransferUSER as TransferUSER,
a.MUSERName as MUSERName,
a.MTIME as MTIME

            from dbo.ICSInvTransferDetail a
            left join ICSInvTransfer b on a.TransferID=b.ID 
            left join ICSINVENTORY c on a.ITEMCODE=c.INVCODE
            left join ICSMO d on a.MOCODE=d.MOCODE 
            left join ICSStorage e on a.FROMStorageCode=e.StorageCode
            left join ICSStack f on a.FROMStackCode=f.StackCode
            left join ICSStorage g on a.TOStorageCode=g.StorageCode
            left join ICSStack h on a.TOStackCode=h.StackCode

            where a.ID='{0}'";
            sql = string.Format(sql, guid);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                FormICSInvTransferDetailUIModel returnInfo = new FormICSInvTransferDetailUIModel();
                returnInfo.ID = dr["ID"].ToString();
                returnInfo.invtransfer = new FormICSInvTransferUIModel();
                returnInfo.invtransfer.ID = dr["TransferID"].ToString();
                returnInfo.invtransfer.TransferNO = dr["TransferNO"].ToString();
                returnInfo.invtransfer.TransferSTATUS = dr["TransferSTATUS"].ToString();

                returnInfo.TransferLine =int.Parse(dr["TransferLine"].ToString());
                returnInfo.ORDERNO = dr["ORDERNO"].ToString();
                returnInfo.ORDERLINE = int.Parse(dr["ORDERLINE"].ToString());
                returnInfo.MEMO = dr["MEMO"].ToString();

                returnInfo.inventory = new FormICSINVENTORYUIModel();
                returnInfo.inventory.INVCODE = dr["ITEMCODE"].ToString();

                returnInfo.mo = new FormICSMOUIModel();
                returnInfo.mo.MOCODE = dr["MOCODE"].ToString();

                returnInfo.storage1 = new FormICSStorageUIModel();
                returnInfo.storage1.StorageCode = dr["FROMStorageCode"].ToString();
                returnInfo.storage2 = new FormICSStorageUIModel();
                returnInfo.storage2.StorageCode = dr["TOStorageCode"].ToString();

                returnInfo.stack1 = new FormICSStackUIModel();
                returnInfo.stack1.StackCode = dr["FROMStackCode"].ToString();
                returnInfo.stack2 = new FormICSStackUIModel();
                returnInfo.stack2.StackCode = dr["TOStackCode"].ToString();

                returnInfo.PLANQTY =Convert.ToDecimal(dr["PLANQTY"].ToString());
                returnInfo.ACTQTY = Convert.ToDecimal(dr["ACTQTY"].ToString());

                returnInfo.CustomerCode = dr["CustomerCode"].ToString();
                returnInfo.CUSTOMERNAME = dr["CUSTOMERNAME"].ToString();
                returnInfo.RECEIVE = dr["RECEIVE"].ToString();
                returnInfo.RECEIVELINE = dr["RECEIVELINE"].ToString();
                returnInfo.TransferTIME =Convert.ToDateTime(dr["TransferTIME"].ToString());
                returnInfo.TransferUSER = dr["TransferUSER"].ToString();
           
                returnInfo.MUSERName = dr["MUSERName"].ToString();
                returnInfo.MTIME = System.DateTime.Parse(dr["MTIME"].ToString());

                if (!InvTransfer.Contains(returnInfo))
                {
                    InvTransfer.Add(returnInfo);
                }
            }
            return InvTransfer;

        }
        #endregion

        #region delete
        public static void delete(List<String> guidList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSInvTransferDetail.Where(a => guidList.Contains(a.ID));
                //var line = db.ICSRES.Where(a => codeList.Contains(a.DCTCODE));
                //if (line.Count() != 0)
                //{
                //    throw new Exception("DCT指令在资源维护已经使用,无法删除!");
                //}
                db.ICSInvTransferDetail.DeleteAllOnSubmit(lines);

                db.SubmitChanges();
                db.Transaction.Commit();

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

        #region 查询物料信息
        public static List<FormICSINVENTORYUIModel> SearchItemInfoList(string dsconn)
        {
            try
            {
                List<FormICSINVENTORYUIModel> returntype = new List<FormICSINVENTORYUIModel>();
                string sql = @"select [ID],[INVCODE],[INVNAME],[INVDESC]
                    from [ICSINVENTORY]
                    where 1=1";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSINVENTORYUIModel itemmodel = new FormICSINVENTORYUIModel();
                    itemmodel.ID = dr["ID"].ToString();
                    itemmodel.INVCODE = dr["INVCODE"].ToString();
                    itemmodel.INVNAME = dr["INVNAME"].ToString();
                    itemmodel.INVDESC = dr["INVDESC"].ToString();
                    if (!returntype.Contains(itemmodel))
                        returntype.Add(itemmodel);
                }
                return returntype;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 查询工单信息
        public static List<FormICSMOUIModel> SearchMOInfoList(string dsconn)
        {
            try
            {
                List<FormICSMOUIModel> returntype = new List<FormICSMOUIModel>();
                string sql = @"select [ID],[MOCODE],[MOMEMO]
                    from [ICSMO]
                    where 1=1";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSMOUIModel itemmodel = new FormICSMOUIModel();
                    itemmodel.ID = dr["ID"].ToString();
                    itemmodel.MOCODE = dr["MOCODE"].ToString();
                    itemmodel.MOMEMO = dr["MOMEMO"].ToString();
                    if (!returntype.Contains(itemmodel))
                        returntype.Add(itemmodel);
                }
                return returntype;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


        #region 查询库别信息
        public static List<FormICSStorageUIModel> SearchStorageInfoList(string dsconn)
        {
            try
            {
                List<FormICSStorageUIModel> returntype = new List<FormICSStorageUIModel>();
                string sql = @"select [Serial],[StorageCode],[StorageName]
                    from [ICSStorage]
                    where 1=1";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSStorageUIModel itemmodel = new FormICSStorageUIModel();
                    itemmodel.Serial = dr["Serial"].ToString();
                    itemmodel.StorageCode = dr["StorageCode"].ToString();
                    itemmodel.StorageName = dr["StorageName"].ToString();
                    if (!returntype.Contains(itemmodel))
                        returntype.Add(itemmodel);
                }
                return returntype;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据库别代码查询库别id
        public static DataTable SelectStorageID(string storagecode)
        {
            string sql = @"select Serial
            from dbo.ICSStorage
            where StorageCode='" + storagecode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 查询库位信息
        public static List<FormICSStackUIModel> SearchStackInfoList(string storageid, string dsconn)
        {
            try
            {
                List<FormICSStackUIModel> returntype = new List<FormICSStackUIModel>();
                string sql = @"select [Serial],[StackCode],[StackName]
                    from [ICSStack]
                    where Storage_Serial='" + storageid + "'";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSStackUIModel itemmodel = new FormICSStackUIModel();
                    itemmodel.Serial = dr["Serial"].ToString();
                    itemmodel.StackCode = dr["StackCode"].ToString();
                    itemmodel.StackName = dr["StackName"].ToString();
                    if (!returntype.Contains(itemmodel))
                        returntype.Add(itemmodel);
                }
                return returntype;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}