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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSSoft.Frame.Data.Entity;
using System.Data;
using ICSSoft.Base.Config.DBHelper;
using ICSSoft.Base.Config.AppConfig;
namespace ICSSoft.Frame.Data.DAL
{
    public class ICSINVReceiptDAL
    {
        public static List<FormICSStorageUIModel> SearchStorageInfoList(string dsconn)
        {
            try
            {
                List<FormICSStorageUIModel> returns = new List<FormICSStorageUIModel>();
                string sql = @"select [Serial],[StorageCode],[StorageName]
                    from [ICSStorage]";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSStorageUIModel smodel = new FormICSStorageUIModel();
                    smodel.Serial = dr["Serial"].ToString();
                    smodel.StorageCode = dr["StorageCode"].ToString();
                    smodel.StorageName = dr["StorageName"].ToString();

                    if (!returns.Contains(smodel))
                        returns.Add(smodel);
                }
                return returns;

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

        public static List<FormICSVendorUIModel> SearchVendorInfoList(string dsconn)
        {
            try
            {
                List<FormICSVendorUIModel> returnv = new List<FormICSVendorUIModel>();
                string sql = @"select [ID],[VendorCode],[VendorName]
                    from [ICSVendor]";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSVendorUIModel vmodel = new FormICSVendorUIModel();
                    vmodel.ID = dr["ID"].ToString();
                    vmodel.VendorCode = dr["VendorCode"].ToString();
                    vmodel.VendorName = dr["VendorName"].ToString();

                    if (!returnv.Contains(vmodel))
                        returnv.Add(vmodel);
                }
                return returnv;

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

        #region 新增和修改

        public static void Add(List<FormICSINVReceiptUIModel> INVInfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSINVReceiptUIModel info in INVInfoList)
                {
                    bool isNew = false;
                    var line = db.ICSINVReceipt.SingleOrDefault(a =>a.ID==info.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSINVReceipt();
                        line.ID = AppConfig.GetGuid();
                        line.ReceiptNO = info.ReceiptNO;
                        line.RECSTATUS = "新建";
                        line.ISALLINSTORAGE = "N";
                       
                    }
                    line.RECTYPE = info.RECTYPE;
                    

                    line.StorageID = info.sto.Serial;

                    if (info.ven != null)
                        line.VENDORCODE = info.ven.ID;
                    else
                        line.VENDORCODE = null;

                    line.MEMO = info.MEMO;
                    line.CreateTIME = info.CreateTIME;
                    line.CreateUSER = info.CreateUSER;

                    line.MUSER = AppConfig.UserId;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = DateTime.Now;
                    line.WorkPoint = AppConfig.WorkPointCode;

                    if (isNew)
                    {
                        db.ICSINVReceipt.InsertOnSubmit(line);
                    }
                    db.SubmitChanges();
                }
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion
        #region 入库单代码是否存在
        public static bool IsIncluding(string noCODE,string workpoint, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSINVReceipt.SingleOrDefault(a => a.ReceiptNO==noCODE&&a.WorkPoint==workpoint);
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion
        #region 通过入库代码查询
        public static List<FormICSINVReceiptUIModel> SearchInfoByID(string Rguid, string dsconn)
        {
            List<FormICSINVReceiptUIModel> INVR = new List<FormICSINVReceiptUIModel>();
            string sql = @"select a.[ReceiptNO] as ReceiptNO,a.[RECTYPE] as RECTYPE,a.[RECSTATUS] as RECSTATUS,
a.[StorageID] as StorageID,b.[StorageCode] as StorageCode,b.[StorageName] as StorageName,
a.[VENDORCODE] as VENDORID,c.[VendorCode] as VendorCode,c.[VendorName] as VendorName,
a.[MEMO] as MEMO,a.[CreateTIME] as CreateTIME,a.[CreateUSER] as CreateUSER,a.[ISALLINSTORAGE] as ISALLINSTORAGE
            from ICSINVReceipt as a
            left join ICSStorage b on a.StorageID=b.Serial
            left join ICSVendor c on a.VENDORCODE=c.ID
            where a.ID='{0}'";
            sql = string.Format(sql, Rguid);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                FormICSINVReceiptUIModel returnInfo = new FormICSINVReceiptUIModel();

                returnInfo.ReceiptNO = dr["ReceiptNO"].ToString();
                returnInfo.RECTYPE = dr["RECTYPE"].ToString();
                returnInfo.RECSTATUS = dr["RECSTATUS"].ToString();

                returnInfo.sto = new FormICSStorageUIModel();
                returnInfo.sto.Serial = dr["StorageID"].ToString();
                returnInfo.sto.StorageCode = dr["StorageCode"].ToString();
                returnInfo.sto.StorageName = dr["StorageName"].ToString();

                returnInfo.ven = new FormICSVendorUIModel();
                returnInfo.ven.ID = dr["VENDORID"].ToString();
                returnInfo.ven.VendorCode = dr["VendorCode"].ToString();
                returnInfo.ven.VendorName = dr["VendorName"].ToString();

                returnInfo.MEMO = dr["MEMO"].ToString();
                returnInfo.CreateTIME = Convert.ToDateTime(dr["CreateTIME"].ToString());
                returnInfo.CreateUSER = dr["CreateUSER"].ToString();
                returnInfo.ISALLINSTORAGE = dr["ISALLINSTORAGE"].ToString();

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

        }
        #endregion
        #region 删除
        public static void deleteInfo(List<string> RIDList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSINVReceipt.Where(a => RIDList.Contains(a.ID));
                db.ICSINVReceipt.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.Transaction.Commit();         
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        public static bool IsIncludingDetail(List<string> RIDList, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

                bool flag = false;
                var lines = db.ICSINVReceiptDetail.Where(a => RIDList.Contains(a.ReceiptID));
                if (lines.Count() != 0)
                    flag = true;

                return flag;

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

        }

        #endregion


        #region 子件资料信息
        public static DataTable GetDetail(string MoSql)
        {
            try
            {
                string sql = @"SELECT
	                                ReceiptNO,
	                                ReceiptLine,
	                                MOCODE,
	                                MOSEQ,
	                                PLANQTY,
	                                MEMO,
	                                ITEMCODE,
	                                b.INVNAME,
	                                b.INVSTD,
	                                b.INVUOM
                                FROM
	                                ICSINVReceiptDetail a
                                LEFT JOIN ICSINVENTORY b ON a.ITEMCODE=b.INVCODE AND a.WorkPoint=b.WorkPoint where ReceiptNO IN (SELECT ReceiptNO FROM(" + MoSql + ")a) AND  a.WorkPoint ='" + AppConfig.WorkPointCode + "'";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}