RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.APP/Report/FormICSReports1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ICSSoft.Frame.User.BLL;
using ICSSoft.Base.Language.Tool;
using ICSSoft.Base.UserControl.MessageControl;
using ICSSoft.Frame.Data.BLL;
using System.Data.SqlClient;
using ICSSoft.Base.Config.AppConfig;
using ICSSoft.Frame.Data.Entity;
using ICSSoft.Base.Report.Filter;
using ICSSoft.Base.Config.DBHelper;
using ICSSoft.Base.UserControl.FormControl;
using ICSSoft.Base.ReferForm.AppReferForm;
using ICSSoft.Frame.APP;
using System.Text.RegularExpressions;


namespace ICSSoft.Frame.APP
{
    public partial class FormICSReports1 : DevExpress.XtraEditors.XtraForm
    {
        private DataTable dataSource = null;
        private DataTable dataHeader = null;
        string MOCODE = "";
        #region 构造函数
        public FormICSReports1()
        {
            InitializeComponent();
            this.MaximumSize = new Size(Screen.PrimaryScreen.WorkingArea.Width, Screen.PrimaryScreen.WorkingArea.Height);
            this.WindowState = FormWindowState.Maximized;
        }
        #endregion

        #region 移动窗体
        private const int WM_NCHITTEST = 0x84;
        private const int HTCLIENT = 0x1;
        private const int HTCAPTION = 0x2;
        //首先必须了解Windows的消息传递机制，当有鼠标活动消息时， 
        //系统发送WM_NCHITTEST 消息给窗体作为判断消息发生地的根据。 nchittest
        //假如你点击的是标题栏，窗体收到的消息值就是 HTCAPTION ， 
        //同样地，若接受到的消息是 HTCLIENT，说明用户点击的是客户区，也就是鼠标消息发生在客户区。 

        //重写窗体，使窗体可以不通过自带标题栏实现移动
        protected override void WndProc(ref Message m)
        {


            //当重载窗体的 WndProc 方法时，可以截获 WM_NCHITTEST 消息并改些该消息， 
            //当判断鼠标事件发生在客户区时，改写改消息，发送 HTCAPTION 给窗体， 
            //这样，窗体收到的消息就时 HTCAPTION ，在客户区通过鼠标来拖动窗体就如同通过标题栏来拖动一样。 
            //注意：当你重载 WndProc 并改写鼠标事件后，整个窗体的鼠标事件也就随之改变了。 
            switch (m.Msg)
            {
                case WM_NCHITTEST:
                    base.WndProc(ref m);
                    if ((int)m.Result == HTCLIENT)
                        m.Result = (IntPtr)HTCAPTION;
                    return;
            }
            //拦截双击标题栏、移动窗体的系统消息  
            if (m.Msg != 0xA3)
            {
                base.WndProc(ref m);
            }

        }
        #endregion

        #region SystemOptition

        /// <summary>
        /// 操作权限
        /// </summary>
        /// <returns></returns>
        public DataTable RightOfExute()
        {
            DataTable rData = new DataTable();
            rData.Columns.Add("BtnName");
            rData.Columns.Add("ActionName");
            //查看权限（必须有）
            DataRow seeRow = rData.NewRow();
            seeRow["BtnName"] = "see";
            seeRow["ActionName"] = "查看";
            rData.Rows.Add(seeRow);
            List<Control> ControlList = new List<Control>();
            //ControlList.Add(btnSelect);
            //ControlList.Add(btnCanSelect);
            //ControlList.Add(btnAdd);
            //ControlList.Add(btnEdit);
            //ControlList.Add(btnDelLable);
            ControlList.Add(btnOutPut);
            foreach (Control ctr in ControlList)
            {

                if (ctr.GetType() == typeof(SimpleButton))
                {
                    DataRow dr = rData.NewRow();
                    dr["BtnName"] = ctr.Name;
                    dr["ActionName"] = ctr.Text;
                    rData.Rows.Add(dr);
                }
            }

            rData.AcceptChanges();
            return rData;
        }
        /// <summary>
        /// 数据权限
        /// </summary>
        /// <returns></returns>
        public DataTable RightOfData()
        {
            DataTable rData = new DataTable();
            rData.Columns.Add("BodyName");
            rData.Columns.Add("ControlName");
            rData.Columns.Add("ControlCaption");

            rData.AcceptChanges();
            return rData;
        }
        #endregion

        #region 退出
        private void btnClose_Click(object sender, EventArgs e)
        {
            AppConfig.CloseFormShow(this.Text);
            this.Close();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            AppConfig.CloseFormShow(this.Text);
            this.Close();
        }
        #endregion

        #region 分页
        private void rptPage_PageIndexChanged(object Sender, EventArgs e)
        {

            DataTable data = AppConfig.GetPageData(dataSource, rptPage.PageIndex, rptPage.PageSize).Copy();
            //DataTable data = AppConfig.GetPageDataByDb(tempTableName, "pagerowindex", rptPage.PageSize, rptPage.PageIndex, dataSource.Rows.Count);
            grdDetail.DataSource = data;
        }
        #endregion

        #region 列表
        private void grvDetail_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator && e.RowHandle >= 0)
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
        }
        #endregion

        #region 导出
        private void btnOutput_Click(object sender, EventArgs e)
        {
            FormOutExcel foe = new FormOutExcel(this.Tag.ToString(), grdDetail);
            foe.ShowDialog();
        }
        #endregion

        private void txtMOCODE_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode != Keys.Enter)
            {
                return;
            }
            txtMOCODE.Enabled = false;
            MOCODE = txtMOCODE.Text = txtMOCODE.Text.Trim().ToUpper();
            if (MOCODE == "")
            {
                return;
            }
            try
            {
                if (!Regex.IsMatch(MOCODE, "^[0-9A-Z_]+$"))
                {
                    throw new Exception("输入错误");
                }
                string sql = @"SELECT MOCODE,MOSEQ FROM dbo.ICSMO WITH(NOLOCK) WHERE WorkPoint='{0}' AND MOCODE='{1}' ORDER BY MOSEQ";
                sql = string.Format(sql, AppConfig.WorkPointCode, MOCODE);
                DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    throw new Exception("工单不存在");
                }
                cboMOSEQ.Items.Clear();
                cboMOSEQ.SelectedIndex = -1;
                foreach (DataRow dr in dt.Rows)
                {
                    cboMOSEQ.Items.Add(dr["MOSEQ"]);
                }
                cboMOSEQ.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                ICSBaseSimpleCode.AppshowMessageBox(ex.Message);
            }
            finally
            {
                txtMOCODE.Enabled = true;
            }
        }

        private void cboMOSEQ_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (MOCODE == "" || cboMOSEQ.SelectedIndex == -1)
            {
                return;
            }
            //string sqlx = @"
            //SELECT 
            //	A.MOCODE,
            //	A.MOSEQ,
            //	A.ITEMCODE,
            //	A.MOPLANQTY,
            //	A.MOPLANSTARTDATE,
            //	A.MOPLANENDDATE,
            //	B.INVNAME AS ITEMDESC,
            //	B.INVSTD,
            //	B.INVShelfLife AS LIFT,
            //	B.INVGROUP AS LVL,
            //	B.INVTYPE,
            //	B.INVMODELGROUP AS cITEMCODE,
            //	B.INVMODELCODE
            //FROM dbo.ICSMO A
            //LEFT JOIN dbo.ICSINVENTORY B ON B.INVCODE=A.ITEMCODE AND B.WorkPoint=A.WorkPoint
            //WHERE A.WorkPoint='{0}' AND A.MOCODE='{1}' AND A.MOSEQ='{2}'
            //
            //SELECT
            //	A.TransNO AS 工单号,
            //	A.TransLine AS 工单行,
            //	A.LOTNO 跟踪单号,
            //	ISNULL(A.EATTRIBUTE2,A.LOTQTY+CONVERT(DECIMAL,ISNULL(A.EATTRIBUTE6,0))) 父数量,
            //    D.LOTQTY 作业数量,
            //	A.LOTQTY 现数量,
            //	A.TYPE 来源,
            //	B.OPSEQ 工序次序,
            //	B.OPCODE 工序代码,
            //	B.OPAttr AS 工序属性,
            //	B.CtrlType 控制方式,
            //	CONVERT(BIT,CASE WHEN C.ID IS NULL THEN 0 ELSE 1 END) AS 派工,
            //	CONVERT(BIT,CASE WHEN D.ID IS NULL THEN 0 ELSE 1 END) AS 报工,
            //	CASE WHEN D.ACTIONRESULT='COLLECT_BEGIN' THEN '开工' WHEN D.ACTIONRESULT='COLLECT_END' THEN '完工' ELSE '' END  AS 工序状态,
            //	D.GOODQTY 工序报工数量,
            //	D.BeginTime 工序开工,
            //	D.EndTime 工序完工,
            //	chk.Status 检验状态,
            //	--chk.Enable,
            //	chk.OKQuantity 检验OK,
            //	chk.NGQuantity 检验NG,
            //	chk.BeginDateTime 检验开始,
            //	chk.EndDateTime 检验结束,
            //	ncr.NCRNo NCR单号,
            //	ncr.Status NCR状态,
            //	CONVERT(BIT,ISNULL(ncr.IsInput,0)) 结算
            //FROM dbo.ICSITEMLot A WITH(NOLOCK)
            //LEFT JOIN dbo.ICSITEMROUTE2OPLot B WITH(NOLOCK) ON B.LotNo=A.LotNO
            //LEFT JOIN dbo.ICSMO2User C WITH(NOLOCK) ON C.LOTNO=B.LotNo AND C.OPCODE=B.OPCODE
            //LEFT JOIN dbo.ICSLOTONWIP D WITH(NOLOCK) ON D.LOTNO=C.LOTNO AND D.OPCODE=C.OPCODE
            //LEFT JOIN dbo.ICSLOTONWIPCheck chk WITH(NOLOCK) ON chk.FORTRANID=D.ID AND chk.Enable=1
            //LEFT JOIN dbo.ICSLOTONWIPCheckNCR ncr WITH(NOLOCK) ON ncr.CheckID=chk.ID
            //WHERE  A.WorkPoint='{0}' AND A.TransNO='{1}' AND A.TransLine='{2}'
            //ORDER BY a.TransLine,A.LotNO,B.OPSEQ
            //";
            DevExpress.Utils.WaitDialogForm _wait = new DevExpress.Utils.WaitDialogForm("正在查找...请稍等...");
            try
            {
                string sql = sqlx;
                _wait.Show();
                sql = string.Format(sql, AppConfig.WorkPointCode, MOCODE, cboMOSEQ.SelectedItem.ToString());
                DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
                dataHeader = ds.Tables[0];
                DataRow drHeadr = dataHeader.Rows[0];

                txtITEMCODE.Text = drHeadr["ITEMCODE"].ToString();
                txtITEMDESC.Text = drHeadr["ITEMDESC"].ToString();
                txtCITEMCODE.Text = drHeadr["cITEMCODE"].ToString();
                txtMOQTY.Text = drHeadr["MOPLANQTY"].ToString();
                txtINVSTD.Text = drHeadr["INVSTD"].ToString();
                txtLIFT.Text = drHeadr["LIFT"].ToString();
                txtLVL.Text = drHeadr["LVL"].ToString();
                txtINVTYPE.Text = drHeadr["INVTYPE"].ToString();
                txtJYOK.Text = Convert.ToDecimal(drHeadr["完工JYOK"]).ToString("f0");
                txtSJNG.Text = Convert.ToDecimal(drHeadr["SJNG"]).ToString();

                dataSource = ds.Tables[1];
                grdDetail.DataSource = dataSource;
                grvDetail.BestFitColumns();
                rptPage.PageSize = 200;
                rptPage.PageIndex = 1;
                rptPage.ReLoad();
            }
            catch (Exception ex)
            {
                ICSBaseSimpleCode.AppshowMessageBox(ex.Message);
            }
            finally
            {
                _wait.Close();
            }
        }
        string sqlx = @"

--基础信息
SELECT 
	A.MOCODE,
	A.MOSEQ,
	A.ITEMCODE,
	A.MOPLANQTY,
	A.MOPLANSTARTDATE,
	A.MOPLANENDDATE,
	B.INVNAME AS ITEMDESC,
	B.INVSTD,
	B.INVShelfLife AS LIFT,
	B.INVGROUP AS LVL,
	B.INVTYPE,
	B.INVMODELGROUP AS cITEMCODE,
	B.INVMODELCODE,
	LOT.LOTNO,
	LOT.EATTRIBUTE2,
	LOT.LOTQTY,
	LOT.EATTRIBUTE6,
	LOT.TYPE,
	L2.OPSEQ AS MAXOPSEQ,
	L1.OPSEQ,
	L1.OPCODE,
	L1.OPAttr,
	L1.CtrlType
	INTO #T1
FROM dbo.ICSMO A WITH(NOLOCK)
LEFT JOIN dbo.ICSINVENTORY B WITH(NOLOCK) ON B.INVCODE=A.ITEMCODE AND B.WorkPoint=A.WorkPoint
LEFT JOIN dbo.ICSITEMLot LOT WITH(NOLOCK) ON LOT.TransNO=A.MOCODE AND LOT.TransLine=A.MOSEQ AND LOT.WorkPoint=A.WorkPoint
LEFT JOIN dbo.ICSITEMROUTE2OPLot L1 WITH(NOLOCK) ON L1.LotNo=LOT.LotNO
LEFT JOIN dbo.ICSITEMROUTE2OPLot L2 WITH(NOLOCK) ON L2.LotNo=LOT.LotNO AND L2.OPSEQ=(SELECT MAX(OPSEQ) FROM ICSITEMROUTE2OPLot WHERE LotNo=LOT.LotNO )
WHERE A.WorkPoint='{0}' AND A.MOCODE='{1}' AND A.MOSEQ='{2}'

---送检巡检
SELECT
	A.MOCODE,
	A.MOSEQ,
	A.LOTNO,
	A.OPCODE,
	A.OPSEQ,
	SUM(CASE WHEN ncr.Status<>'让步接受' THEN chk.OKQuantity ELSE chk.OKQuantity+chk.NGQuantity END) 送验OK,
	SUM(CASE WHEN ncr.Status<>'让步接受' THEN 0 ELSE chk.NGQuantity END) 送验NG,
	AA.OPSEQ MAXOPSEQ
	INTO #T2
FROM dbo.ICSLOTONWIP A
LEFT JOIN dbo.ICSLOTONWIP AA ON AA.LOTNO=A.LOTNO AND AA.OPSEQ=(SELECT MAX(OPSEQ) FROM dbo.ICSLOTONWIP WHERE LOTNO=A.LOTNO)
LEFT JOIN dbo.ICSLOTONWIPDetail B WITH(NOLOCK) ON B.LOTNO=A.LOTNO AND B.OPCODE=A.OPCODE AND A.WorkPoint=B.WorkPoint
LEFT JOIN dbo.ICSLOTONWIPInspection C WITH(NOLOCK) ON C.DetailID=B.ID AND C.IsCalc=1
LEFT JOIN dbo.ICSLOTONWIPCheck chk WITH(NOLOCK) ON chk.FORTRANID=C.ID AND chk.Enable=1
LEFT JOIN dbo.ICSLOTONWIPCheckNCR ncr WITH(NOLOCK) ON ncr.CheckID=chk.ID
WHERE A.WorkPoint='{0}' AND A.MOCODE='{1}' AND A.MOSEQ='{2}'
GROUP BY 
	A.MOCODE,
	A.MOSEQ,
	A.LOTNO,
	AA.OPSEQ,
	A.OPCODE,
	A.OPSEQ

--完工/委外检验
SELECT
	A.LOTNO,
	ISNULL(A.EATTRIBUTE2,A.LOTQTY+CONVERT(DECIMAL,ISNULL(A.EATTRIBUTE6,0))) 父数量,
	A.LOTQTY 现数量,
    D.LOTQTY 作业数量,
	A.TYPE 来源,
	A.OPSEQ,
	A.OPCODE 工序代码,
	A.OPAttr AS 工序属性,
	A.CtrlType 控制方式,
	CASE WHEN C.EATTRIBUTE1=1 THEN '委外' WHEN C.EATTRIBUTE1=0 THEN '自制' ELSE '' END 实际派工,
	CONVERT(BIT,CASE WHEN C.ID IS NULL THEN 0 ELSE 1 END) AS 派工,
	CONVERT(BIT,CASE WHEN D.ID IS NULL THEN 0 ELSE 1 END) AS 报工,
	CASE WHEN D.ACTIONRESULT='COLLECT_BEGIN' THEN '开工' WHEN D.ACTIONRESULT='COLLECT_END' THEN '完工' ELSE '' END  AS 工序状态,
	D.GOODQTY 工序报工数量,
	D.BeginTime 工序开工,
	D.EndTime 工序完工,
	chk.Status 检验状态,
	--chk.Enable,
	chk.OKQuantity 检验OK,
	chk.NGQuantity 检验NG,
	chk.BeginDateTime 检验开始,
	chk.EndDateTime 检验结束,
	ncr.NCRNo NCR单号,
	ncr.Status NCR状态,
	CONVERT(BIT,ISNULL(ncr.IsInput,0)) 结算
	INTO #T3
FROM #T1 A
LEFT JOIN dbo.ICSMO2User C WITH(NOLOCK) ON C.LOTNO=A.LotNo AND C.OPCODE=A.OPCODE
LEFT JOIN dbo.ICSLOTONWIP D WITH(NOLOCK) ON D.LOTNO=C.LOTNO AND D.OPCODE=C.OPCODE
LEFT JOIN dbo.ICSLOTONWIPCheck chk WITH(NOLOCK) ON chk.FORTRANID=D.ID AND chk.Enable=1
LEFT JOIN dbo.ICSLOTONWIPCheckNCR ncr WITH(NOLOCK) ON ncr.CheckID=chk.ID
ORDER BY A.MOCODE,A.LotNO,A.OPSEQ

--工单合计
SELECT
	A.MOCODE,
	A.MOSEQ,
	A.ITEMCODE,
	A.MOPLANQTY,
	A.MOPLANSTARTDATE,
	A.MOPLANENDDATE,
	A.ITEMDESC,
	A.INVSTD,
	A.LIFT,
	A.LVL,
	A.INVTYPE,
	A.cITEMCODE,
	A.INVMODELCODE,
	--SUM(CASE WHEN B.OPSEQ=A.MAXOPSEQ THEN B.送验OK ELSE 0 END) 完工SJOK,
	--SUM(CASE WHEN B.OPSEQ=A.MAXOPSEQ THEN B.送验NG ELSE 0 END) 完工SJNG,
	SUM(CASE WHEN B.OPSEQ=B.MAXOPSEQ THEN B.送验NG ELSE 0 END) SJNG,
	SUM(CASE WHEN B.OPSEQ=B.MAXOPSEQ THEN B.送验OK ELSE 0 END) SJOK,
	SUM(CASE WHEN C.OPSEQ=A.MAXOPSEQ THEN (CASE WHEN C.NCR状态<>'让步接受' THEN ISNULL(C.检验OK,0) ELSE C.检验OK+C.检验NG END) ELSE 0 END) 完工JYOK,
	SUM(CASE WHEN C.OPSEQ=A.MAXOPSEQ THEN (CASE WHEN C.NCR状态<>'让步接受' THEN ISNULL(C.检验NG,0) ELSE 0 END) ELSE 0 END) 完工JYNG
FROM #T1 A
LEFT JOIN #T2 B ON B.LOTNO=A.LOTNO AND B.OPSEQ=A.OPSEQ
LEFT JOIN #T3 C ON C.LOTNO=A.LOTNO AND C.OPSEQ=A.OPSEQ
GROUP BY
	A.MOCODE,
	A.MOSEQ,
	A.ITEMCODE,
	A.MOPLANQTY,
	A.MOPLANSTARTDATE,
	A.MOPLANENDDATE,
	A.ITEMDESC,
	A.INVSTD,
	A.LIFT,
	A.LVL,
	A.INVTYPE,
	A.cITEMCODE,
	A.INVMODELCODE
--送检,完工检合并
SELECT
		A.LOTNO 跟踪单,
		A.OPSEQ 工序次序,
		A.OPCODE 工序代码,
		A.送验OK,
		A.送验NG,
		B.父数量,
		B.现数量,
		B.作业数量,
		B.来源,
		B.工序属性,
		B.控制方式,
		B.实际派工,
		B.派工,
		B.报工,
		B.工序状态,
		B.工序报工数量,
		B.工序开工,
		B.工序完工,
		B.检验状态,
		B.检验OK,
		B.检验NG,
		B.检验开始,
		B.检验结束,
		B.NCR单号,
		B.NCR状态,
		B.结算
FROM #T2 A
LEFT JOIN #T3 B ON B.LOTNO=A.LOTNO AND B.OPSEQ=A.OPSEQ
";


    }
}