MES_Client/TGMES/Core.StlMes.Client.Pms/order/FrmImportOrder.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using com.hnshituo.pur.vo;
using Core.Mes.Client.Comm.Control;
using Core.Mes.Client.Comm.Tool;
using Infragistics.Win;
using Infragistics.Win.UltraWinGrid;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Pur.Entity;
using Pur.Entity.configureEntity;
using Pur.Entity.pur_orderEntiy;
using Pur.Entity.require_planEntiy;
using Pur.Entity.pur_planEntity;
using Pur.pur_plan;

namespace Pur.order
{
    public partial class FrmImportOrder : FrmPmsBase
    {
        public FrmImportOrder()
        {
            InitializeComponent();
        }
       
        HSSFWorkbook hssfworkbook;    //Office 2003
        XSSFWorkbook xssfworkbook;     //Office 2007 2010
        ISheet sheet = null;
        private void FrmImportOrder_Load(object sender, EventArgs e)
        {
            getInportData();
            ultraTabControl1.SelectedTab = ultraTabControl1.Tabs[0];
            //ultraGrid1.DisplayLayout.Bands[0].Override.AllowUpdate = DefaultableBoolean.False;
            //ultraGrid2.DisplayLayout.Bands[0].Override.AllowUpdate = DefaultableBoolean.False;
            GridHelper.SetExcludeColumnsActive(ultraGrid2.DisplayLayout.Bands[0],new string[]{"CHK"});
            ultraGrid3.DisplayLayout.Bands[0].Override.AllowUpdate = DefaultableBoolean.False;
            ultraGrid4.DisplayLayout.Bands[0].Override.AllowUpdate = DefaultableBoolean.False;
            ultraGrid5.DisplayLayout.Bands[0].Override.AllowUpdate = DefaultableBoolean.False;

        }
        private void FrmImportOrder_Shown(object sender, EventArgs e)
        {
            this.ToolBarItemEnable(this, "doImport", true);
            this.ToolBarItemEnable(this, "doSure", true);
            this.ToolBarItemEnable(this, "doClear", true);
            this.ToolBarItemEnable(this, "doDelete", false);
        }
        //查询导入的记录
        private void getInportData()
        {
            try
            {
                this.Cursor = Cursors.WaitCursor;
                dataSet1.Clear();
                dataSet2.Clear();
                dataSet3.Clear();
                OrderM Odm = new OrderM();
                Odm.Validflag = "1";
                Odm.Remark3 = "1";//查询旧合同
                Odm.ValidDataPurviewIds = this.ValidDataPurviewIds;
                Odm.BpmUserId = UserInfo.GetUserID();
                DataTable dt = this.execute<DataTable>("com.hnshituo.pur.order.service.OrderMService", "find_OrderM", new object[] { Odm, 0, 0 });
                GridHelper.CopyDataToDatatable(dt, dataTable1, true);
                GridHelper.RefreshAndAutoSize(ultraGrid2);
            }
            catch (Exception ex)
            {
                MessageUtil.ShowTips("查询导入历史数据数据失败：" + ex);
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
        /// <summary>
        /// 使用系统软件打开模板
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string path = "";
            try
            {
                path = System.Windows.Forms.Application.StartupPath + "\\OrderTemplate\\OrderTemplate.xls";
                System.Diagnostics.Process.Start(path);
            }
            catch (Exception ex)
            {
                MessageUtil.ShowWarning("没有可用的模板！");
            }
        }
        #region toolbar工具栏
        public override void ToolBar_Click(object sender, string ToolbarKey)// toolbar菜单
        {
            switch (ToolbarKey)
            {
                case "doImport":    // ButtonTool
                    ImportExcel();
                    break;

                case "doSure":    // ButtonTool
                    ConfirmData();
                    break;
                case "doDelete":    // ButtonTool
                    doDelete();
                    break;
                case "doClose":    // ButtonTool
                    this.Close();
                    break;
                case "doClear":    // ButtonTool
                    doClear();
                    break;
            }

        }
        //清空
        private void doClear()
        {
            if (MessageUtil.ShowYesNoAndQuestion("确定清空？") != DialogResult.Yes)
            { return; }
            for (int i = this.ultraGrid1.Rows.Count-1; i >=0 ; i--)
            {
                this.ultraGrid1.Rows[i].Delete();
            }
        }
        //删除
        private void doDelete()
        {
            ultraGrid2.UpdateData();
            ArrayList list = new ArrayList();
            foreach (UltraGridRow row in ultraGrid2.Rows)
            {
                if (Convert.ToBoolean(row.GetCellValue("CHK")) == true)
                {
                    OrderM inp = new OrderM();
                    String orderId = row.GetCellValue("OrderId").ToString().Trim();
                    if (String.IsNullOrEmpty(orderId))
                    {
                        MessageUtil.ShowTips("未获取到合同号");
                        return;
                    }
                    inp.OrderId = orderId;
                    inp.DeleteName = UserInfo.GetUserName();
                    inp.DeleteUserid = UserInfo.GetUserID();
                    inp.DeleteTime = DateTime.Now;
                    list.Add(inp);
                }
            }
            if (list.Count <= 0)
            {
                MessageUtil.ShowTips("请选择需撤销导入的合同");
            }
            if (MessageUtil.ShowYesNoAndQuestion("确认撤销勾选的" + list.Count + "条数据？") != DialogResult.Yes) return;
            CoreResult rt = this.execute<CoreResult>("com.hnshituo.pur.order.service.OrderMService", "doDeleteOldOrder", new object[] { list });
            if (rt.Resultcode == 0)
            {
                MessageUtil.ShowTips("操作成功");
                ultraGrid1.Refresh();
                getInportData();
            }
            else
            {
                MessageUtil.ShowTips("操作失败:" + rt.Resultmsg);
            }
        }

        private void ImportExcel()
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "所有文件|*.xls;*.xlsx|xls文件(*.xls)|*.xls|xlsx文件(*.xlsx)|*.xlsx";
            ofd.Multiselect = false;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                this.Cursor = Cursors.WaitCursor;
                string filePath = ofd.FileName;
                InitializeWorkbook(filePath);
                //ConvertToDataTable();
                Thread t = new Thread(new ThreadStart(ConvertToDataTable));
                t.Start();
            }
        }
        /// <summary>
        /// 确定提交
        /// </summary>
        private void ConfirmData()
        {
            try
            {
                string model = "";
                this.Cursor = Cursors.WaitCursor;
                List<OrderC> listc = new List<OrderC>();
                ultraGrid1.UpdateData();

                foreach (UltraGridRow row in ultraGrid1.Rows)
                {
                    if (row.Cells["选择"].Value.ToString().ToUpper() == "TRUE")
                    {
                        string orderNo = row.Cells[1].Value.ToString().Trim();
                        string orderType = row.Cells[2].Value.ToString().Trim();
                        string suppCode = row.Cells[3].Value.ToString().Trim();
                        string orderLineSqe = row.Cells[4].Value.ToString().Trim();
                        string itemCode = row.Cells[5].Value.ToString().Trim();
                        string qty = row.Cells[6].Value.ToString().Trim();
                        string qtyInStock = row.Cells[7].Value.ToString().Trim();
                        string itemUomId = row.Cells[8].Value.ToString().Trim();
                        string priceWithoutTax = row.Cells[9].Value.ToString().Trim();
                        String receiveType = row.Cells[10].Value.ToString().Trim();
                        string BuyerName = row.Cells[11].Value.ToString().Trim();
                        string createName = row.Cells[12].Value.ToString().Trim();
                        string createTime = row.Cells[13].Value.ToString().Trim();
                        string modelId = row.Cells[14].Value.ToString().Trim();
                        string ordertitleId = row.Cells[15].Value.ToString().Trim();//合同抬头编号
                        if (String.IsNullOrEmpty(orderNo))
                        {
                            MessageUtil.ShowWarning("合同号不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(orderType))
                        {
                            MessageUtil.ShowWarning("合同类型不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(suppCode))
                        {
                            MessageUtil.ShowWarning("供应商编号不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(orderLineSqe))
                        {
                            MessageUtil.ShowWarning("合同行号不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(itemCode))
                        {
                            MessageUtil.ShowWarning("物料编号不能为空！");
                            return;
                        }
                        //if (String.IsNullOrEmpty(itemName))
                        //{
                        //    MessageUtil.ShowWarning("物料名称不能为空！");
                        //    return;
                        //}
                        if (String.IsNullOrEmpty(qty))
                        {
                            MessageUtil.ShowWarning("采购总量不能为空！");
                            return;
                        }
                        if (!StringUtil.IsNumber(qtyInStock))
                        {
                            MessageUtil.ShowWarning("采购入库量不是数字！");
                            return;
                        }
                        if (String.IsNullOrEmpty(itemUomId))
                        {
                            MessageUtil.ShowWarning("计量单位（采购单位）不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(priceWithoutTax))
                        {
                            MessageUtil.ShowWarning("含税单价不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(BuyerName))
                        {
                            MessageUtil.ShowWarning("业务员不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(createName))
                        {
                            MessageUtil.ShowWarning("创建人能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(createTime))
                        {
                            MessageUtil.ShowWarning("创建时间不能为空！");
                            return;
                        }
                        if (String.IsNullOrEmpty(receiveType))
                        {
                            MessageUtil.ShowWarning("接收类型不能为空！");
                            return;
                        }               
                        OrderC orderC = new OrderC();
                        orderC.PackageType = orderType;//合同类别
                        orderC.OrderId = orderNo;
                        orderC.SuppCode = suppCode;
                        orderC.OrderLineSqe = orderLineSqe;
                        orderC.ItemCode = itemCode;
                        orderC.Qty = Convert.ToDouble(qty);
                        orderC.ReceivedQty = Convert.ToDouble(qtyInStock);
                        orderC.ItemUomId = itemUomId;
                        orderC.PriceWithoutTax = Convert.ToDouble(priceWithoutTax);
                        orderC.CreateTime = Convert.ToDateTime(createTime);
                        orderC.CreateUserid = createName;
                        orderC.CreateName = createName;
                        orderC.BuyerName = BuyerName;
                        orderC.ReceiveType = receiveType;
                        orderC.ModelId = modelId;
                        orderC.OrdertitleId = ordertitleId;
                        listc.Add(orderC);
                    }
                }
                if (listc.Count <= 0)
                {
                    MessageUtil.ShowWarning("请勾选您要确认导入的合同数据！");
                    return;
                }
         
                if (MessageUtil.ShowYesNoAndQuestion("确认导入勾选的" + listc.Count + "条数据？") != DialogResult.Yes) return;
                CoreResult rt = this.execute<CoreResult>("com.hnshituo.pur.order.service.OrderCService", "insertOldOrderNew", new object[] { listc });
                if (rt.Resultcode == 0)
                {
                    MessageUtil.ShowTips("操作成功");
                    ultraGrid1.Refresh();
                    getInportData();
                    for (int i = this.ultraGrid1.Rows.Count - 1; i >= 0; i--)
                    {
                        if (ultraGrid1.Rows[i].Cells["选择"].Value.ToString().ToUpper() == "TRUE")
                        {
                            this.ultraGrid1.Rows[i].Delete();
                        }
                        
                    }
                }
                else
                {
                    MessageUtil.ShowTips("操作失败:" + rt.Resultmsg);
                }
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
        #endregion
     
        /// 获取查询条件
        /// </summary>
        /// <returns></returns>
        private PurTaskMEntity getEnSelContion()
        {
            PurTaskMEntity entity = new PurTaskMEntity();

            entity.ValidDataPurviewIds = this.ValidDataPurviewIds;
            //判断当前拥有此页面功能的角色是否启用数据权限，//0未启用
            string QstrDataFlag = this.execute<string>("com.hnshituo.pur.utils.service.UtilsService", "getRoleDataFlag", new object[] { UserInfo.GetUserID(), "Pur.pur_plan.frmTaskMana_MC" });

            entity.Validflag = "1";
            return entity;
        }
        /// <summary>
        /// 查询任务主表
        /// </summary>
        public void GetTaskM(PurTaskMEntity M_PurTaskM)
        {
            dataTable2.Rows.Clear();
            dataTable3.Rows.Clear();
            dataTable4.Rows.Clear();
            DataTable dt = this.execute<DataTable>("com.hnshituo.pur.purplan.service.TaskMService", "getTaskM", new object[] { M_PurTaskM });
            //按创建时间降序
            if (dt.Rows.Count > 0)
            {
                dt.DefaultView.Sort = "CREATETIME DESC";
                dt = dt.DefaultView.ToTable();
            }
            GridHelper.CopyDataToDatatable(ref dt, ref dataTable1, true);//绑定物料分类表
            ultraGrid1.DisplayLayout.Bands[0].PerformAutoResizeColumns(true, PerformAutoSizeType.AllRowsInBand);
        }
        /// <summary>
        /// 打开选择的Excel
        /// </summary>
        /// <param name="path"></param>
        private void InitializeWorkbook(string path)
        {
            try
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                    sheet = hssfworkbook.GetSheetAt(0);
                }
            }
            catch
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    xssfworkbook = new XSSFWorkbook(file);
                    sheet = xssfworkbook.GetSheetAt(0);
                }
            }
        }
        /// <summary>
        /// 将Excel内容转化为DataTable
        /// </summary>
        private void ConvertToDataTable()
        {
            //ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow iRow = sheet.GetRow(0);
            short count = iRow.LastCellNum;
            if (count < 15)
            {
                MessageUtil.ShowWarning("导入的Excel列数与模板列数不符！");
                return;
            }
            DataTable dt = new DataTable();
            for (int j = 0; j < 15 + 1; j++)
            {
                if (j == 0)  //加一列选择框
                {
                    dt.Columns.Add("选择");
                }
                else
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
            }
            dt.Columns[0].DefaultValue = "False";
            dt.Columns[0].DataType = typeof(Boolean);
            while (rows.MoveNext())
            {
                IRow row = null;
                try
                {
                    row = (HSSFRow)rows.Current;
                }
                catch
                {
                    row = (XSSFRow)rows.Current;
                }
                DataRow dr = dt.NewRow();
                int k = 0;
                for (int i = 1; i < row.LastCellNum + 1; i++)
                {
                    ICell cell = row.GetCell(i - 1);

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                        if (!String.IsNullOrEmpty(cell.ToString()))
                        {
                            k = k + 1;
                        }
                    }
                }
                if (k > 0)
                {
                    dt.Rows.Add(dr);
                }
            }
            SetGrid(dt);

        }

        delegate void SetGridCallBack(DataTable dt);

        private void SetGrid(DataTable dt)
        {
            if (ultraGrid1.InvokeRequired)
            {
                SetGridCallBack sg = new SetGridCallBack(SetGrid);
                this.Invoke(sg, new object[] { dt });
            }
            else
            {
                ultraGrid1.DataSource = dt;
                ultraGrid1.DisplayLayout.Bands[0].Columns["选择"].Header.CheckBoxVisibility = Infragistics.Win.UltraWinGrid.HeaderCheckBoxVisibility.WhenUsingCheckEditor;
                ultraGrid1.DisplayLayout.Bands[0].Columns[1].Width = 135;
                GridHelper.RefreshAndAutoSize(ultraGrid1);
                this.Cursor = Cursors.Default;
            }

        }

        private void ultraTabControl1_SelectedTabChanged(object sender, Infragistics.Win.UltraWinTabControl.SelectedTabChangedEventArgs e)
        {
            if (ultraTabControl1.SelectedTab == ultraTabControl1.Tabs[0])
            {
                this.ToolBarItemEnable(this, "doImport", true);
                this.ToolBarItemEnable(this, "doSure", true);
                this.ToolBarItemEnable(this, "doDelete", false);
                this.ToolBarItemEnable(this, "doClear", true);
            }
            else
            {
                this.ToolBarItemEnable(this, "doImport", false);
                this.ToolBarItemEnable(this, "doSure", false);
                this.ToolBarItemEnable(this, "doDelete", true);
                this.ToolBarItemEnable(this, "doClear", false);
            }
        }

        private void ultraGrid2_AfterRowActivate(object sender, EventArgs e)
        {
            dataTable2.Clear();
            dataTable3.Clear();
            dataTable4.Clear();
            UltraGridRow uge = ultraGrid2.ActiveRow;
            if (uge == null)
            { return; }
            String orderId = uge.GetCellValue("OrderId").ToString().Trim();
            String purLineId = uge.GetCellValue("remark3").ToString().Trim();
            if (!String.IsNullOrEmpty(orderId))
            {
                OrderC odC = new OrderC();
                odC.OrderId = orderId;
                odC.Validflag = "1";
                DataTable dt = this.execute<DataTable>("com.hnshituo.pur.order.service.OrderCService", "doQueryOrderC", new object[] { odC, 0, 0 });
                GridHelper.CopyDataToDatatable(dt, dataTable2, true);
                GridHelper.RefreshAndAutoSize(ultraGrid3);
            }
            if (!String.IsNullOrEmpty(purLineId))
            {
                EntityPurPlanD PurPlanDEntity = new EntityPurPlanD();
                PurPlanDEntity.MrId = purLineId;
                PurPlanDEntity.Validflag = "1";
                DataTable dt1 = this.execute<DataTable>("com.hnshituo.pur.purplan.service.PurPlanDService", "getPlanD", new object[] { PurPlanDEntity });
                GridHelper.CopyDataToDatatable(dt1, dataTable4, true);
                GridHelper.RefreshAndAutoSize(ultraGrid4);
            }
        }
    }
}