RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSInvTransferDal.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 ICSInvTransferDal
    {
        #region AddandEdit
        public static void AddandEdit(List<FormICSInvTransferUIModel> ItemLot, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSInvTransferUIModel Item in ItemLot)
                {
                    bool isNew = false;
                    var line = db.ICSInvTransfer.SingleOrDefault(a => a.ID == Item.ID);

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSInvTransfer();
                        line.ID = AppConfig.GetGuid();
                    }

                    var codes = db.ICSInvTransfer.Where(a => a.TransferNO == Item.TransferNO && a.ID != line.ID);
                    if (codes.ToList().Count > 0)
                    {
                        throw new Exception("转移单号已存在");
                    }
                    line.TransferNO = Item.TransferNO;
                    line.FRMStorageID = Item.FRMStorageID;
                    line.TOStorageID = Item.TOStorageID;
                    line.FRMStorageCode = Item.FRMStorageCode;
                    line.TOStorageCode = Item.TOStorageCode;
                    line.TransferSTATUS = Item.TransferSTATUS;
                    line.RECTYPE = Item.RECTYPE;
                    line.TIMETYPE = Item.TIMETYPE;
                    line.MEMO = Item.MEMO;
                    line.CreateTIME = Convert.ToDateTime(Item.CreateTIME);
                    line.CreateUSER = Item.CreateUSER;
                    line.EffectedTIME = Convert.ToDateTime(Item.EffectedTIME);
                    line.EffectedUSER = Item.EffectedUSER;
                    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.ICSInvTransfer.InsertOnSubmit(line);

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

        #region 通过入库转移单id查询
        public static List<FormICSInvTransferUIModel> SearchInfoByID(string guid, string dsconn)
        {
            List<FormICSInvTransferUIModel> InvTransfer = new List<FormICSInvTransferUIModel>();
            string sql = @"select a.ID as ID,a.TransferNO as TransferNO,b.Serial as FRMStorageID,c.Serial as TOStorageID,b.StorageCode as FRMStorageCode,
 c.StorageCode as TOStorageCode,a.TransferSTATUS as TransferSTATUS,a.RECTYPE as RECTYPE,a.TIMETYPE as TIMETYPE,a.MEMO as MEMO,a.CreateTIME as CreateTIME,a.CreateUSER as CreateUSER,
a.EffectedTIME as EffectedTIME,a.EffectedUSER as EffectedUSER,a.MUSERName as MUSERName,a.MTIME as MTIME
            from dbo.ICSInvTransfer a
            left join ICSStorage b on a.FRMStorageID=b.Serial 
            left join ICSStorage c on a.TOStorageID=c.Serial
            
            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)
            {
                FormICSInvTransferUIModel returnInfo = new FormICSInvTransferUIModel();
                returnInfo.ID = dr["ID"].ToString();
                returnInfo.TransferNO = dr["TransferNO"].ToString();
                returnInfo.storage1 = new FormICSStorageUIModel();
                returnInfo.storage1.Serial = dr["FRMStorageID"].ToString();
                returnInfo.storage1.StorageCode = dr["FRMStorageCode"].ToString();
                returnInfo.storage2 = new FormICSStorageUIModel();
                returnInfo.storage2.Serial = dr["TOStorageID"].ToString();
                returnInfo.storage2.StorageCode = dr["TOStorageCode"].ToString();
                
                returnInfo.RECTYPE = dr["RECTYPE"].ToString();

                returnInfo.TransferSTATUS = dr["TransferSTATUS"].ToString();
                returnInfo.TIMETYPE = dr["TIMETYPE"].ToString();
                returnInfo.MEMO = dr["MEMO"].ToString();
                returnInfo.CreateTIME = dr["CreateTIME"].ToString();
                returnInfo.CreateUSER = dr["CreateUSER"].ToString();
                returnInfo.EffectedTIME = dr["EffectedTIME"].ToString();
                returnInfo.EffectedUSER = dr["EffectedUSER"].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,List<string> noList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSInvTransfer.Where(a => guidList.Contains(a.ID));
                var line = db.ICSInvTransferDetail.Where(a => noList.Contains(a.TransferNO));
                if (line.Count() != 0)
                {
                    throw new Exception("该转移单已经生成转移单详细表,无法删除!");
                }
                db.ICSInvTransfer.DeleteAllOnSubmit(lines);

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

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

        #region 根据转移源库别代码查找转移源库别代码id
        public static DataTable SelectFRMStorageId(string str, string wp)
        {
            string sql = @"select Serial
            from dbo.ICSStorage
            where StorageCode='" + str + "' and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据转移目标库别代码查找转移目标库别代码id
        public static DataTable TOStorageId(string str, string wp)
        {
            string sql = @"select Serial
            from dbo.ICSStorage
            where StorageCode='" + str + "'and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

    }
}