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

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSMaterialPickDAL
    {
        public static void CheckInfo(FramDataContext db,ICSLOTSIMULATION simulation)
        {
            try
            {
                var user = db.Sys_User.SingleOrDefault(a=>a.UserCode==simulation.MUSER);
                if (user == null)
                    throw new Exception("用户不存在！");

                var mouser = db.ICSMO2User.SingleOrDefault(a=>a.LOTNO==simulation.LOTNO && a.OPCODE==simulation.OPCODE);
                if (mouser == null)
                    throw new Exception("批次工序派工信息不存在！");

                var mo = db.ICSMO.SingleOrDefault(a => a.MOCODE==mouser.MOCODE && a.MOSEQ==mouser.MOSEQ.ToString());
                if (mo == null)
                    throw new Exception("工单信息不存在！");

                var moroute = db.ICSMO2ROUTE.SingleOrDefault(a => a.MOID == mo.ID);
                if (moroute == null)
                    throw new Exception("工单未关联途程！");

                var itemop = db.ICSITEMROUTE2OP.SingleOrDefault(a => a.ITEMCODE==mo.ITEMCODE && a.ROUTECODE==moroute.ROUTECODE && a.OPCODE==mouser.OPCODE);
                if (itemop == null)
                    throw new Exception("产品途程工序不存在！");

                var sim = db.ICSLOTSIMULATION.SingleOrDefault(a => a.LOTNO == simulation.LOTNO);
                var itemops = db.ICSITEMROUTE2OP.Where(a => a.ITEMCODE == itemop.ITEMCODE && a.ROUTECODE == itemop.ROUTECODE && a.OPSEQ < itemop.OPSEQ);
                if (sim == null && itemops != null && itemops.Count() > 0)
                    throw new Exception("请从第一道工序开始报工！");

                //判断
                if (sim != null)
                {
                    //如果是完工判断是否是当前工序、设备
                    if (sim.CollectStatus == "COLLECT_BEGIN")
                    {
                        if(sim.OPCODE != simulation.OPCODE)
                            throw new Exception(string.Format("应采集工序：{0}！", sim.OPCODE));
                        if (sim.EQPCODE != simulation.EQPCODE)
                            throw new Exception(string.Format("应采集设备：{0}！", sim.EQPCODE));
                    }
                    //开始下道工序时判断下一道工序是否正确
                    else if (sim.CollectStatus == "COLLECT_END")
                    {
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void SaveStart(FramDataContext db, ICSLOTSIMULATION simulation)
        {
            try
            {
                var user = db.Sys_User.SingleOrDefault(a => a.UserCode == simulation.MUSER);
                var mouser = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == simulation.LOTNO && a.OPCODE == simulation.OPCODE);
                var lot = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == simulation.LOTNO);

                var simulationNew = db.ICSLOTSIMULATION.SingleOrDefault(a => a.LOTNO == simulation.LOTNO);
                #region simulation
                //ICSLOTSIMULATION simulationNew = new ICSLOTSIMULATION();
                if (simulationNew == null)
                {
                    simulationNew = new ICSLOTSIMULATION();
                    simulationNew.ID = AppConfig.GetGuid();
                    simulationNew.LOTNO = simulation.LOTNO;
                    simulationNew.MOCODE = mouser.MOCODE;
                    //simulationNew.LOTSEQ = 0;

                    simulationNew.LOTQTY = Convert.ToInt32(lot.LOTQTY);
                    simulationNew.GOODQTY = simulationNew.LOTQTY;
                    simulationNew.NGQTY = 0;
                    simulationNew.LOTStatus = "新增";

                    simulationNew.MODELCODE = "";
                    simulationNew.ITEMCODE = lot.ItemCode;
                    simulationNew.FROMROUTE = "";
                    simulationNew.FROMOP = "";

                    simulationNew.ROUTECODE = mouser.RouteCode;
                    simulationNew.RESCODE = "";
                    simulationNew.CHECKLOTNO = "";
                    simulationNew.CARTONCODE = "";
                    simulationNew.PALLETCODE = "";
                    simulationNew.PRODUCTSTATUS = "GOOD";

                    simulationNew.LACTION = "GOOD";
                    simulationNew.ACTIONLIST = "GOOD";
                    simulationNew.NGTIMES = 0;
                    simulationNew.ISCOM = "0";

                    simulationNew.ISHOLD = 0;
                    simulationNew.SHELFNO = "";
                    simulationNew.MOSEQ = mouser.MOSEQ;
                    simulationNew.CollectStatus = "COLLECT_BEGIN";
                    simulationNew.LOTSEQ = 1;
                    simulationNew.BeginTime = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                }
                else
                {
                    if (simulationNew.OPCODE == simulation.OPCODE)
                    {
                        simulationNew.CollectStatus = "COLLECT_END";
                    }
                    else
                    {
                        simulationNew.CollectStatus = "COLLECT_BEGIN";
                        simulationNew.LOTSEQ = simulation.LOTSEQ + 1;
                    }
                }
                simulationNew.OPCODE = simulation.OPCODE;
                simulationNew.EQPCODE = simulation.EQPCODE;

                simulationNew.EndTime = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                simulationNew.MUSER = AppConfig.UserCode;
                simulationNew.MUSERName = AppConfig.UserName;
                simulationNew.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                simulationNew.WorkPoint = AppConfig.WorkPointCode;
                #endregion

                ICSLOTONWIP onwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == simulation.LOTNO && a.LOTSEQ == simulation.LOTSEQ);
                #region onwip
                ICSLOTONWIP onwipNew = new ICSLOTONWIP();
                onwipNew.ID = AppConfig.GetGuid();

                onwipNew.LOTNO = onwip.LOTNO;
                onwipNew.MOCODE = onwip.MOCODE;
                onwipNew.LOTSEQ = onwip.LOTSEQ;
                onwipNew.MODELCODE = onwip.MODELCODE;
                onwipNew.ITEMCODE = onwip.ITEMCODE;
                onwipNew.ROUTECODE = onwip.ROUTECODE;
                onwipNew.OPCODE = onwip.OPCODE;
                onwipNew.SEGCODE = onwip.SEGCODE;
                onwipNew.SSCODE = onwip.SSCODE;
                onwipNew.RESCODE = onwip.RESCODE;
                onwipNew.EQPCODE = simulationNew.EQPCODE;
                onwipNew.SHIFTTYPECODE = onwip.SHIFTTYPECODE;
                onwipNew.ACTION = onwip.ACTION;
                onwipNew.ACTIONRESULT = onwip.ACTIONRESULT;
                onwipNew.NGTIMES = onwip.NGTIMES;
                onwipNew.SHELFNO = onwip.SHELFNO;
                onwipNew.MOSEQ = onwip.MOSEQ;
                onwipNew.Processed = onwip.Processed;
                onwipNew.LOTQTY = onwip.LOTQTY;
                onwipNew.GOODQTY = onwip.GOODQTY;
                onwipNew.NGQTY = onwip.NGQTY;

               // onwipNew.SEQ = simulationNew.SEQ;
                onwipNew.BeginTime = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                onwipNew.MUSER = AppConfig.UserCode;
                onwipNew.MUSERName = AppConfig.UserName;
                onwipNew.MTIME = DateTime.Now;
                onwipNew.WorkPoint = AppConfig.WorkPointCode;
                onwipNew.UserCodeBegin = simulation.MUSER;
                #endregion

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

        #region 根据工单查询子件机汇总信息
        public static DataSet select(String MOCode, String TransNO, String Appconstr)
        {
            try
            {
                string sql = @" SELECT
	                                a.VouchCode as MOCODE ,
	                                a.VouchRow as MOSEQ,
	                                a.InvCode as ITEMCODE,
	                                a.VoucherNO as MOVER,
	                                b.INVNAME,
	                                b.INVSTD AS INVTYPE,
	                                a.SubInvCode AS MOBITEMCODE,
	                                d.INVNAME AS CINVNAME,
	                                d.INVSTD AS CINVTYPE,
	                                d.INVUOM,
	                                CAST(ISNULL(a.Quantity, 0) AS DECIMAL(18,2)) AS QTY,
--CAST(ISNULL(f.HasQuantity, 0) AS DECIMAL(18,2)) AS QTYS, D.EATTRIBUTE1
CAST(ISNULL(A.HasQuantity, 0) AS DECIMAL(18,2)) AS QTYS, D.EATTRIBUTE1
	                                --ISNULL(f.HasQuantity, 0) AS QTYS
                                FROM
                                ICSMaterialPick a
                                LEFT JOIN ICSINVENTORY b ON a.InvCode = b.INVCODE
                                LEFT JOIN ICSINVENTORY d ON a.SubInvCode = d.INVCODE
	                            LEFT JOIN ICSMOPickLog f ON a.VouchCode=f.MOCODE AND a.VouchRow=f.MOSEQ 
                                WHERE   a.VouchCode='{0}' AND
	                                a.WorkPoint = '{1}' AND ISNULL(b.INVCLASS, '') <> 'I09'
                                ORDER BY  a.VouchRow,a.SubInvCode
                                ";

                sql += @"
                       SELECT c.MOCODE ,
	                        c.SubInvCode as MOBITEMCODE,
	                        c.CINVNAME,                  
	                        c.CINVTYPE,
	                        c.INVUOM,
	                        CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS MOBITEMQTY
	                       -- ISNULL(f.HasQuantity, 0) AS MOBITEMQTY
                        FROM
                        (
	                         SELECT
		                        a.VouchCode as MOCODE,
		                        a.VouchRow as MOSEQ,
		                        a.SubInvCode,
		                        b.INVNAME AS CINVNAME,
		                        b.INVSTD AS CINVTYPE,
		                        b.INVUOM,
		                        b.INVMACHINETYPE,
		                        SUM(a.Quantity) AS MOBITEMQTY
		                        FROM ICSMaterialPick a
	                        LEFT JOIN ICSINVENTORY b ON a.SubInvCode = b.INVCODE AND a.WorkPoint=b.WorkPoint
	                         WHERE  a.VouchCode='{0}' AND
		                        a.WorkPoint = '{1}' --AND ISNULL(b.INVCLASS, '') <> 'I09'
	                        GROUP BY  a.VouchCode,a.SubInvCode,a.VouchRow, b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE
                        )c
                        
                          LEFT JOIN ICSMOPickLog f ON c.MOCode=f.MOCODE AND c.MOSEQ=f.MOSEQ 
                        ORDER BY c.SubInvCode
                        ";
                sql = string.Format(sql, MOCode, AppConfig.WorkPointCode, TransNO);
                DataSet ds = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 根据工单查询拣料表信息
        public static DataSet GetGauge(String MOCode, String Appconstr)
        {
            try
            {
                string sql = @" SELECT c.VouchCode as MOCODE,
	                                c.SubInvCode as MOBITEMCODE,
	                                c.INVNAME as CINVNAME,
	                                c.INVSTD as CINVTYPE,
	                                c.INVUOM,
	                                CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS MOBITEMQTY,

 CAST( ISNULL(e.QTYTotal, 0) AS DECIMAL(18,2)) AS QTYTotal,
 CAST( ISNULL(f.QTYXBC, 0) AS DECIMAL(18,2)) AS QTYXBC,
	                                CASE WHEN ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) < 0 THEN '0' ELSE CAST(ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) AS DECIMAL(18,2) ) END AS QTYNeed,
	                                d.WHCode,
	                                d.BinCode,
	                                d.LotNO,

 g.VenderLotNO AS cTVBatch,

                                 CAST( ISNULL(d.LotQty, 0) AS DECIMAL(18,2)) QTY,

	                                d.MTIME
                                FROM
                                (
	                                SELECT
		                                a.VouchCode , 
		                                a.SubInvCode,
		                                b.INVNAME,
		                                b.INVSTD,
		                                b.INVUOM,
		                                b.INVMACHINETYPE,
		                                SUM(a.Quantity) AS MOBITEMQTY
		                                FROM ICSMaterialPick a
	                                LEFT JOIN ICSINVENTORY b ON a.SubInvCode = b.INVCODE AND a.WorkPoint=b.WorkPoint
									WHERE  a.VouchCode='{0}' AND
										a.WorkPoint = '{1}' --AND ISNULL(b.INVCLASS, '') <> 'I09'
									GROUP BY  a.MOCODE,a.VouchCode,a.VouchRow,a.SubInvCode,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE
                                )c
                                LEFT JOIN ICSWareHouseLotInfo d ON c.SubInvCode=d.INVCode AND d.WorkPoint='{1}' AND d.LotQty>0
                               
LEFT JOIN ICSITEMLot g ON g.LotNO=d.LotNO 

LEFT JOIN (SELECT INVCode,SUM(LotQty) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode) e ON c.SubInvCode=e.INVCode
                                LEFT JOIN (SELECT INVCode,WHCode,SUM(LotQty) AS QTYXBC FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WHCode) f ON c.SubInvCode=f.INVCode AND c.INVMACHINETYPE=f.WHCode
                                ORDER BY c.SubInvCode,d.MTIME,d.LotNO
                        ";
                sql = string.Format(sql, MOCode, AppConfig.WorkPointCode);
                DataSet ds = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion
        
    }
}