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

                if (line == null)
                {
                    isNew = true;
                    line = new ICSASNIQC();
                    line.ID = AppConfig.GetGuid();
                }
                line.INSPECTOR = ItemLot.INSPECTOR;
                line.PIC = ItemLot.PIC;
                line.LOTNO = ItemLot.LOTNO;
                line.STANDARD = ItemLot.STANDARD;
                line.RESULT = ItemLot.RESULT;
                line.RECEIVEDATE = ItemLot.RECEIVEDATE;
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion
        public static void Save(DataTable retData, string Connecting)
        {
            FramDataContext db = new FramDataContext(Connecting);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (DataRow dr in retData.Rows)
                {
                    
                    string ID = dr["ID"].ToString();
                    string stno = dr["入库单号"].ToString();
                    string dSql = @"SELECT a.VENDORCODE,a.RECTYPE,b.ReceiptNO,b.ReceiptLine,b.ORDERNO,b.ORDERLINE,
	  b.ITEMCODE,b.INVUSER,b.PLANQTY,a.StorageID,d.id as ITEMID,c.id as VENDORID
	  FROM dbo.ICSINVReceipt a LEFT JOIN dbo.ICSINVReceiptDetail b ON a.ID=b.ReceiptID
	  LEFT JOIN ICSShipToStock c ON c.VENDORCODE=a.VENDORCODE
	  LEFT JOIN ICSShipToStock d ON d.ITEMCODE=b.ITEMCODE
	  WHERE b.RECSTATUS='新建' AND b.ReceiptLine NOT IN (SELECT STNO_SEQ FROM ICSASNIQC WHERE STNO='{1}')
		    AND a.ID='{0}' and a.WorkPoint='" + AppConfig.WorkPointCode + "' ORDER BY ReceiptLine  ";
                    dSql = string.Format(dSql, ID,stno);
                    DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, dSql).Tables[0];

                    if (dt.Rows.Count <= 0)
                    {
                        throw new Exception("没有要生成的数据!!");
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        int seq = int.Parse(dt.Rows[i]["ReceiptLine"].ToString());
                        ICSASNIQC header = new ICSASNIQC();
                        string headID = AppConfig.GetGuid();
                        if (dt.Rows[i]["ITEMID"].ToString() != "" && dt.Rows[i]["VENDORID"].ToString() != "")
                        {
                            header.ID = headID;
                            header.IQCNO = dt.Rows[i]["ReceiptNO"].ToString() + "-" + "" + seq + "";
                            header.STNO = dt.Rows[i]["ReceiptNO"].ToString();
                            header.STNO_SEQ = int.Parse(dt.Rows[i]["ReceiptLine"].ToString());
                            header.STATUS = "已检未入库";
                            header.STS = "是";
                            header.INVUSER = dt.Rows[i]["INVUSER"].ToString();
                            header.APPDATE = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                            header.MUSER = AppConfig.UserId;
                            header.MUSERName = AppConfig.UserName;
                            header.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                            header.WorkPoint = AppConfig.WorkPointCode;
                            header.VENDORCODE = dt.Rows[i]["VENDORCODE"].ToString();

                            ICSASNIQCDETAIL deail = new ICSASNIQCDETAIL();
                            deail.ID = AppConfig.GetGuid();
                            deail.IQCID = headID;
                            deail.IQCNO = header.IQCNO;
                            deail.STNO = dt.Rows[i]["ReceiptNO"].ToString();
                            deail.STLINE = int.Parse(dt.Rows[i]["ReceiptLine"].ToString());
                            deail.ITEMCODE = dt.Rows[i]["ITEMCODE"].ToString();
                            deail.ORDERNO = dt.Rows[i]["ORDERNO"].ToString();
                            deail.ORDERLINE = int.Parse(dt.Rows[i]["ORDERLINE"].ToString());
                            deail.STDSTATUS = "已检未入库";
                            deail.RECEIVEQTY = decimal.Parse(dt.Rows[i]["PLANQTY"].ToString());
                            deail.INSType = "全检";
                            deail.NGQTY = 0;
                            deail.CHECKSTATUS = "合格";
                            deail.ATTRIBUTE = "正常";
                            deail.Type = "正常";
                            deail.isSample = "否";
                            deail.concessionStatus = "否";
                            deail.StorageID = dt.Rows[i]["StorageID"].ToString();
                            deail.MUSER = AppConfig.UserId;
                            deail.MUSERNAME = AppConfig.UserName;
                            deail.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                            deail.WORKPOINT = AppConfig.WorkPointCode;
                            db.ICSASNIQCDETAIL.InsertOnSubmit(deail);
                            db.ICSASNIQC.InsertOnSubmit(header);

                            string updateSql = @"UPDATE dbo.ICSINVReceiptDetail SET IQCStatus='合格', QualifyQTY='{0}', RECSTATUS= '已经完成' where ReceiptNO='{1}' and ReceiptLine='{2}' and WorkPoint='" + AppConfig.WorkPointCode + "'";
                            updateSql = string.Format(updateSql,deail.RECEIVEQTY, deail.STNO, deail.STLINE);
                            DBHelper.ExecuteNonQuery(AppConfig.AppConnectString, CommandType.Text, updateSql);

                            string ptsql = "select RECSTATUS from ICSINVReceiptDetail where ReceiptNO='" + deail.STNO + "' AND RECSTATUS!='CLOSE'and WorkPoint='" + AppConfig.WorkPointCode + "'";
                            DataTable table = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, ptsql).Tables[0];
                            if(table.Rows.Count==0){
                                string upptsql = "update  ICSINVReceipt set RECSTATUS='CLOSE' from ICSINVReceipt where ReceiptNO='" + header.STNO + "'  and WorkPoint='" + AppConfig.WorkPointCode + "'";
                                DBHelper.ExecuteNonQuery(AppConfig.AppConnectString, CommandType.Text, updateSql);
                            }
                            
                        }
                        else
                        {
                            header.ID = headID;
                            header.IQCNO = dt.Rows[i]["ReceiptNO"].ToString() + "-" + "" + seq + "";
                            header.STNO = dt.Rows[i]["ReceiptNO"].ToString();
                            header.STNO_SEQ = int.Parse(dt.Rows[i]["ReceiptLine"].ToString());
                            header.STATUS = "新建";
                            header.STS = "否";
                            header.INVUSER = dt.Rows[i]["INVUSER"].ToString();
                            header.APPDATE = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                            header.MUSER = AppConfig.UserId;
                            header.MUSERName = AppConfig.UserName;
                            header.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                            header.WorkPoint = AppConfig.WorkPointCode;
                            header.VENDORCODE = dt.Rows[i]["VENDORCODE"].ToString();

                            ICSASNIQCDETAIL deail = new ICSASNIQCDETAIL();
                            deail.ID = AppConfig.GetGuid();
                            deail.IQCID = headID;
                            deail.IQCNO = header.IQCNO;
                            deail.STNO = dt.Rows[i]["ReceiptNO"].ToString();
                            deail.STLINE = int.Parse(dt.Rows[i]["ReceiptLine"].ToString());
                            deail.ITEMCODE = dt.Rows[i]["ITEMCODE"].ToString();
                            deail.ORDERNO = dt.Rows[i]["ORDERNO"].ToString();
                            deail.ORDERLINE = int.Parse(dt.Rows[i]["ORDERLINE"].ToString());
                            deail.STDSTATUS = "新建";
                            deail.RECEIVEQTY = decimal.Parse(dt.Rows[i]["PLANQTY"].ToString());
                            deail.concessionStatus = "否";
                            deail.StorageID = dt.Rows[i]["StorageID"].ToString();
                            deail.MUSER = AppConfig.UserId;
                            deail.MUSERNAME = AppConfig.UserName;
                            deail.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                            deail.WORKPOINT = AppConfig.WorkPointCode;
                            db.ICSASNIQCDETAIL.InsertOnSubmit(deail);
                            db.ICSASNIQC.InsertOnSubmit(header);
                        }
                    }

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

        #region select
        public static ICSASNIQC select(String guid, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSASNIQC entity = new ICSASNIQC();
            try
            {
                var line = db.ICSASNIQC.SingleOrDefault(a => a.ID == guid && a.WorkPoint == AppConfig.WorkPointCode);
                return (ICSASNIQC)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region query
        public static ICSASNIQC Query(String iqcno, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSASNIQC entity = new ICSASNIQC();
            try
            {
                var line = db.ICSASNIQC.SingleOrDefault(a => a.IQCNO == iqcno && a.WorkPoint == AppConfig.WorkPointCode);
                return (ICSASNIQC)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #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.ICSASNIQC.Where(a => guidList.Contains(a.ID) && a.STATUS == "新建" && a.WorkPoint==AppConfig.WorkPointCode);
                var line = db.ICSASNIQCDETAIL.Where(a => guidList.Contains(a.IQCID) && a.STDSTATUS == "新建" && a.WORKPOINT == AppConfig.WorkPointCode);
                db.ICSASNIQC.DeleteAllOnSubmit(lines);
                db.ICSASNIQCDETAIL.DeleteAllOnSubmit(line);
                db.SubmitChanges();
                db.Transaction.Commit();

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

        #region cancel
        public static void cancel(List<String> guidList,List<string> lineList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                for (int i = 0; i < guidList.Count;i++ )
                {
                    var line = db.ICSASNIQC.SingleOrDefault(a => a.ID == guidList[i] && a.WorkPoint == AppConfig.WorkPointCode);
                    var lines = db.ICSASNIQCDETAIL.SingleOrDefault(a => a.IQCID == guidList[i] && a.WORKPOINT == AppConfig.WorkPointCode);
                    line.STATUS = "新建";
                    lines.STDSTATUS = "新建";

                    var INVReceiptDetail = db.ICSINVReceiptDetail.SingleOrDefault(a => a.ReceiptNO == line.STNO && a.WorkPoint == AppConfig.WorkPointCode && a.ReceiptLine == int.Parse(lineList[i].ToString()));
                    INVReceiptDetail.RECSTATUS = "新建";
                    INVReceiptDetail.IQCStatus = "新建";
                    var ICSINVReceipt = db.ICSINVReceipt.SingleOrDefault(a => a.ReceiptNO == line.STNO && a.WorkPoint == AppConfig.WorkPointCode);
                    ICSINVReceipt.RECSTATUS = "新建";

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

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

        #region OK
        public static void OK(List<String> guidList,List<string> lineList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                for (int i = 0; i < guidList.Count; i++)
                {
                    var line = db.ICSASNIQC.SingleOrDefault(a => a.ID == guidList[i] && a.WorkPoint == AppConfig.WorkPointCode);
                    var lines = db.ICSASNIQCDETAIL.SingleOrDefault(a => a.IQCID == guidList[i] && a.WORKPOINT == AppConfig.WorkPointCode);
                    line.STATUS = "待检";
                    lines.STDSTATUS = "待检";

                    var INVReceiptDetail = db.ICSINVReceiptDetail.SingleOrDefault(a => a.ReceiptNO == line.STNO && a.WorkPoint == AppConfig.WorkPointCode && a.ReceiptLine == int.Parse(lineList[i].ToString()));
                    INVReceiptDetail.RECSTATUS = "待检";
                    INVReceiptDetail.IQCStatus = "待检";
                    db.SubmitChanges();
                    var ptDetail = db.ICSINVReceiptDetail.Where(a => a.ReceiptNO == line.STNO && a.WorkPoint == AppConfig.WorkPointCode && a.RECSTATUS != "待检");
                    if (ptDetail.Count() == 0 || ptDetail == null)
                    {
                        var ICSINVReceipt = db.ICSINVReceipt.SingleOrDefault(a => a.ReceiptNO == line.STNO && a.WorkPoint == AppConfig.WorkPointCode);
                        ICSINVReceipt.RECSTATUS = "待检";
                    }
                    db.SubmitChanges();

                }
                db.Transaction.Commit();

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