RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/CheckDAL.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;
using System.Data.SqlClient;

namespace ICSSoft.Frame.Data.DAL
{
    public class CheckDAL
    {
        public static DataTable PoArriveCheckData(string LotNO, string dsconn)
        {
            string sql = @"SELECT A.LotNO, B.INVCODE, B.INVNAME, B.INVSTD, A.LOTQTY, A.VenderLotNO, A.EATTRIBUTE3 AS iNum, B.INVUOM, A.EATTRIBUTE2, ISNULL(A.EATTRIBUTE5, '') EATTRIBUTE5
FROM ICSITEMLot A INNER JOIN ICSINVENTORY B ON A.ItemCode = B.INVCODE WHERE 1=1 AND A.LotNO = '{0}' AND TYPE = '到货' ";
            sql = string.Format(sql, LotNO);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            return dt;
        }

        public static bool UpdateCheck(SqlConnection sqlConnection, SqlTransaction trans, string LotNO, string iStatus, string User, string dsconn)
        {
            string sql = "";
            string ConnectString = dsconn;
            bool isSuccess = false;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlConnection;
            cmd.Transaction = trans;
            try
            {
                sql = @"UPDATE ICSITEMLot SET EATTRIBUTE2 = '{1}', VENDORITEMCODE = '{2}', VENDORCODE = '{3}', EATTRIBUTE5 = '已检' WHERE LotNO = '{0}' ";
                sql = string.Format(sql, LotNO, iStatus, User, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return isSuccess;
        }

        public static DataTable IQCCheckData(string LotNO, string dsconn)
        {
            string sql = @"SELECT TOP 1 A.LotNO, D.INVCODE, D.INVNAME, D.INVSTD, B.OPCODE, A.VenderLotNO, D.INVUOM, B.LOTQTY,
B.UserCodeBegin, B.EQPCODE, ISNULL(C.Result, '') Result, E.EATTRIBUTE1 FROM ICSITEMLot A
INNER JOIN ICSLOTONWIP B ON A.LotNO = B.LOTNO LEFT JOIN ICSLOTONWIPCheck C ON B.ID = C.ONWIPID
JOIN ICSINVENTORY D ON A.ItemCode = D.INVCODE LEFT JOIN ICSMO2User E ON B.LotNO = E.LOTNO AND B.OPCODE = E.OPCODE
WHERE 1=1 AND A.LotNO = '{0}' AND A.TYPE = '工单' ORDER BY B.MTIME DESC ";
            sql = string.Format(sql, LotNO);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            return dt;
        }

        public static bool UpdateIQCCheck(SqlConnection sqlConnection, SqlTransaction trans, string LotNO, string iStatus, string User, string UserName, string WorkPoint, string dsconn)
        {
            string sql = "";
            string ConnectString = dsconn;
            bool isSuccess = false;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlConnection;
            cmd.Transaction = trans;
            try
            {
                sql = @"
INSERT INTO ICSLOTONWIPCheck
(ID, ONWIPID, Result, [USER], TIME, MUSER, MUSERName, MTIME, WorkPoint)
SELECT NEWID(), A.ID, '{1}', '{2}', GETDATE(), '{2}', '{3}', GETDATE(), '{4}' FROM ICSLOTONWIP A
LEFT JOIN ICSLOTONWIPCheck B ON A.ID = B.ONWIPID WHERE 1=1 AND LOTNO = '{0}' AND B.ID IS NULL ";
                sql = string.Format(sql, LotNO, iStatus, User, UserName, WorkPoint);
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return isSuccess;
        }

        public static DataTable isFinished(string LotNO, string dsconn)
        {
            string sql = @"SELECT TOP 1 CollectStatus FROM ICSLOTSIMULATION WHERE 1=1 AND LOTNO = '{0}' ORDER BY MTIME DESC ";
            sql = string.Format(sql, LotNO);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            return dt;
        }


        public static DataTable QueryAllCheck100(string ITEMCODE, string OPCODE, string WORKPOINT, string dsconn)
        {
            string sql = @"
SELECT * FROM (
	SELECT * FROM (
		SELECT TOP 50 CAST( 0 AS BIT ) AS isSelect,CASE WHEN mo2u.EATTRIBUTE1='0' THEN '工序检验' ELSE '委外检验' END AS CheckMode,
		a.ITEMCODE,a.LOTNO,a.OPCODE,ncr.IsInput,ncr.Status AS ncrStatus,a.LOTQTY AS Quantity,a.EQPCODE,chk.Enable,
		chk.ID,ncr.MTIME ncrTIME,ncr.MUSERName AS ncrUSER,ncr.Memo as ncrMemo,ncr.NCRNo,chk.Status AS chkResult,det.SetValue,det.UNIT,'' TransNO,det.CKGROUPMETH AS chkMemo,chk.MTIME chkTIME,users.username chkUSER
		FROM ICSLOTONWIP a
		INNER JOIN dbo.ICSMO2User mo2u ON mo2u.LOTNO=A.LOTNO AND mo2u.OPCODE=A.OPCODE
		INNER JOIN ICSLOTONWIPCheck chk ON  a.ID=chk.FORTRANID AND a.WorkPoint=chk.WorkPoint --AND chk.Enable='1'
		INNER JOIN ICSLOTONWIPCheckDetail det ON det.FORTRANID=chk.ID AND det.Type='不良'
        left join sys_user users  on users.usercode=chk.eattribute1 and users.workpointcode=a.WorkPoint
		LEFT JOIN ICSLOTONWIPCheckNCR ncr ON  det.ID=ncr.CheckID AND det.WorkPoint=ncr.WorkPoint
		WHERE  a.ITEMCODE='{0}'  AND a.OPCODE='{1}' AND chk.Status='不良' AND a.WorkPoint='{2}'
		ORDER BY chk.MTIME DESC
	) T1
	UNION ALL
	SELECT * FROM (
		SELECT TOP 50 CAST( 0 AS BIT ) AS isSelect,e.CheckMode,
		a.ITEMCODE,a.LOTNO,a.OPCODE,ncr.IsInput,ncr.Status AS ncrStatus,a.LOTQTY AS Quantity,a.EQPCODE,chk.Enable,
		chk.ID,ncr.MTIME ncrTIME,ncr.MUSERName AS ncrUSER,ncr.Memo as ncrMemo,ncr.NCRNo,chk.Status AS chkResult,det.SetValue,det.UNIT,e.TransNO,det.CKGROUPMETH AS chkMemo,chk.MTIME chkTIME,users.username chkUSER
		FROM ICSLOTONWIP a
		INNER JOIN ICSLOTONWIPDetail Wipdet ON Wipdet.LOTNO=a.LOTNO AND Wipdet.OPCode=a.OPCODE AND a.WorkPoint=Wipdet.WorkPoint
		INNER JOIN ICSLOTONWIPInspection e ON Wipdet.ID=e.DetailID AND Wipdet.WorkPoint=e.WorkPoint
		INNER JOIN ICSLOTONWIPCheck chk ON  e.ID=chk.FORTRANID AND e.WorkPoint=chk.WorkPoint --AND chk.Enable='1'
        left join sys_user users  on users.usercode=chk.eattribute1  and users.workpointcode=a.WorkPoint
		INNER JOIN ICSLOTONWIPCheckDetail det ON det.FORTRANID=chk.ID AND det.Type='不良'
		LEFT JOIN ICSLOTONWIPCheckNCR ncr ON  det.ID=ncr.CheckID AND det.WorkPoint=ncr.WorkPoint
		WHERE  a.ITEMCODE='{0}'  AND a.OPCODE='{1}' AND chk.Status='不良' AND a.WorkPoint='{2}'
		ORDER BY chk.MTIME DESC
	) T2
) T ORDER BY T.chkTIME ASC
";
            sql = string.Format(sql, ITEMCODE, OPCODE, WORKPOINT);
            return DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
        }
    }
}