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 CoreFS.CA06;
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;

namespace Pur.require_plan
{
    public partial class FrmPurOilImportUse : FrmPmsBase
    {
        public String strTips = "0";
        string[] str ={"0","0","0","0","0","0","0","0","0","0","0","0","0"};
        string[] str1 = { "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0" };
        public FrmPurOilImportUse()
        {
            InitializeComponent();
            this.IsLoadUserView = true;
        }
        public FrmPurOilImportUse(OpeBase OB)
        {
            InitializeComponent();
            this.ob = OB;
        }
        HSSFWorkbook hssfworkbook;    //Office 2003
        XSSFWorkbook xssfworkbook;     //Office 2007 2010
        ISheet sheet = null;
        private void FrmPurOilImportUse_Load(object sender, EventArgs e)
        {
            //绑定年份数据
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("YEAR", Type.GetType("System.Int32"));
            for (int i = DateTime.Now.AddYears(-3).Year; i <= DateTime.Now.AddYears(3).Year; i++)
                dt1.Rows.Add(new object[] { i });
            Qyear.DataSource = dt1;
            Qyear.ValueMember = "YEAR";
            Qyear.DisplayMember = "YEAR";

            Qyear.Value = DateTime.Now.Year.ToString();

            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"});
            ultraGrid2.DisplayLayout.Bands[0].Override.AllowUpdate = DefaultableBoolean.False;

            ck_ctime.Checked = true;
            txtCreateTimeStart.Value = DateTime.Now.AddMonths(-1);
            txtCreateTimeEnd.Value = DateTime.Now.AddDays(2);
            int mouth=DateTime.Now.Month;
            if (mouth == 1)
            {
                ck_yu.Checked = true;
                ck_1m.Checked = true;
            }
            else if (mouth == 2)
            {
                ck_2m.Checked = true;
            }
            else if (mouth == 3)
            {
                ck_3m.Checked = true;
            }
            else if (mouth == 4)
            {
                ck_4m.Checked = true;
            }
            else if (mouth == 5)
            {
                ck_5m.Checked = true;
            }
            else if (mouth == 6)
            {
                ck_6m.Checked = true;
            }
            else if (mouth == 7)
            {
                ck_7m.Checked = true;
            }
            else if (mouth == 8)
            {
                ck_8m.Checked = true;
            }
            else if (mouth == 9)
            {
                ck_9m.Checked = true;
            }
            else if (mouth == 10)
            {
                ck_10m.Checked = true;
            }
            else if (mouth == 11)
            {
                ck_10m.Checked = true;
            }
            else if (mouth == 12)
            {
                ck_12m.Checked = true;
            }
        }
        private void FrmImportOrder_Shown(object sender, EventArgs e)
        {
            this.ultraToolbarsManager1.Toolbars[0].Tools["doImport"].InstanceProps.Visible = DefaultableBoolean.True;
            this.ultraToolbarsManager1.Toolbars[0].Tools["doSure"].InstanceProps.Visible = DefaultableBoolean.True;
            this.ultraToolbarsManager1.Toolbars[0].Tools["doExcel"].InstanceProps.Visible = DefaultableBoolean.True;
            this.ultraToolbarsManager1.Toolbars[0].Tools["doClear"].InstanceProps.Visible = DefaultableBoolean.True;
            this.ultraToolbarsManager1.Toolbars[0].Tools["doQuery"].InstanceProps.Visible = DefaultableBoolean.False;
        }
        //查询导入的记录
        private void getInportData()
        {
            try
            {
                if (ultraTabControl1.SelectedTab == ultraTabControl1.Tabs[1])
                {
                    this.Cursor = Cursors.WaitCursor;
                    import.Clear();
                    OilImport ipt = new OilImport();
                    ipt.UserName = txt_useName.Text.Trim();
                    ipt.CardDId = txt_card_change_d.Text.Trim();
                    if (ck_ctime.Checked)
                    {
                        if (!String.IsNullOrEmpty(txtCreateTimeStart.Text))
                        {
                            ipt.CreateTime = Convert.ToDateTime(txtCreateTimeStart.Value);
                        }
                        if (!String.IsNullOrEmpty(txtCreateTimeEnd.Text))
                        {
                            ipt.DeleteTime = Convert.ToDateTime(txtCreateTimeEnd.Value);
                        }
                    }
                    DataTable dt = this.execute<DataTable>("com.hnshituo.pur.requireplan.service.OilImportService", "find_Import", new object[] { ipt });
                    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工具栏
        private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
        {
            switch (e.Tool.Key)
            {
                case "doImport":    // ButtonTool
                    ImportExcel();
                    break;
                case "doQuery":    // ButtonTool
                    getInportData();
                    break;
                case "doSure":    // ButtonTool
                    ConfirmData();
                    break;
                case "doClose":    // ButtonTool
                    this.Close();
                    break;
                case "doExcel":    // ButtonTool
                    doExcel();
                    break;
                case "doClear":    // ButtonTool
                    doClear();
                    break;


            }
            
        }

        private void doExcel()
        {
            try
            {
                this.Cursor = Cursors.Default;
                if (ultraTabControl1.SelectedTab == ultraTabControl1.Tabs[0])
                {
                    if (ultraGrid1.Rows.Count <= 0)
                    {
                        MessageUtil.ShowTips("无可导出的待导入交易明细！");
                        return;
                    }
                    ArrayList alUltraGrid = new ArrayList();
                    alUltraGrid.Add(ultraGrid1);
                    ArrayList alSheeft = new ArrayList();
                    alSheeft.Add("待导入交易明细");
                    GridHelper.ulGridToExcel(alUltraGrid, alSheeft, "待导入交易明细");
                }
                else
                {
                    if (ultraGrid2.Rows.Count <= 0)
                    {
                        MessageUtil.ShowTips("无可导出导入记录！");
                        return;
                    }
                    ArrayList alUltraGrid = new ArrayList();
                    alUltraGrid.Add(ultraGrid2);
                    ArrayList alSheeft = new ArrayList();
                    alSheeft.Add("导入记录");
                    GridHelper.ulGridToExcel(alUltraGrid, alSheeft, "导入记录");
                }
            }
            catch (Exception e)
            {
                MessageUtil.ShowTips("导出失败" + e.Message);
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
        //清空
        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 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
            {
                this.Cursor = Cursors.WaitCursor;
                List<OilImport> listc = new List<OilImport>();
                ultraGrid1.UpdateData();
                if (Qyear.SelectedIndex == -1)
                {
                    MessageUtil.ShowTips("请选择导入的年份");
                    Qyear.Focus();
                    return;
                }
                String strYear = Qyear.Value.ToString().Trim();
                foreach (UltraGridRow row in ultraGrid1.Rows)
                {
                    if (row.Cells["选择"].Value.ToString().ToUpper() == "TRUE")
                    {
                        string cardDId = row.Cells[1].Value.ToString().Trim();
                        string remain = row.Cells[2].Value.ToString().Trim();
                        string use1 = row.Cells[3].Value.ToString().Trim();
                        string use2 = row.Cells[4].Value.ToString().Trim();
                        string use3 = row.Cells[5].Value.ToString().Trim();
                        string use4 = row.Cells[6].Value.ToString().Trim();
                        string use5 = row.Cells[7].Value.ToString().Trim();
                        string use6 = row.Cells[8].Value.ToString().Trim();
                        string use7 = row.Cells[9].Value.ToString().Trim();
                        String use8 = row.Cells[10].Value.ToString().Trim();
                        string use9 = row.Cells[11].Value.ToString().Trim();
                        string use10 = row.Cells[12].Value.ToString().Trim();
                        string use11 = row.Cells[13].Value.ToString().Trim();
                        string use12 = row.Cells[14].Value.ToString().Trim();
                        if (String.IsNullOrEmpty(cardDId))
                        {
                            MessageUtil.ShowWarning("副卡卡号不能为空！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(remain))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "年初卡余额输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use1))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "去年12月输入数字格式不正确！");
                            return;
                        }
                        if (!IsNum(use2))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "1月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use3))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "2月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use4))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "3月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use5))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "4月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use6))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "5月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use7))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "6月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use8))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "7月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use9))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "8月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use10))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "9月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        if (!IsNum(use11))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "10月输入数字格式不正确！");
                            return;
                        }
                        if (!IsNum(use12))
                        {
                            MessageUtil.ShowWarning("卡:" + cardDId + "11月输入数字格式不正确！");
                            row.Activate();
                            return;
                        }
                        OilImport ipt = new OilImport();
                        ipt.CardDId = cardDId;
                        if (!String.IsNullOrEmpty(use1))
                        {
                            ipt.M1UseNew = use1;
                        }
                        if (!String.IsNullOrEmpty(use2))
                        {
                            ipt.M2UseNew = use2;
                        }
                        if (!String.IsNullOrEmpty(use3))
                        {
                            ipt.M3UseNew = use3;
                        }
                        if (!String.IsNullOrEmpty(use4))
                        {
                            ipt.M4UseNew = use4;
                        }
                        if (!String.IsNullOrEmpty(use5))
                        {
                            ipt.M5UseNew = use5;
                        }
                        if (!String.IsNullOrEmpty(use6))
                        {
                            ipt.M6UseNew = use6;
                        }
                        if (!String.IsNullOrEmpty(use7))
                        {
                            ipt.M7UseNew = use7;
                        }
                        if (!String.IsNullOrEmpty(use8))
                        {
                            ipt.M8UseNew = use8;
                        }
                        if (!String.IsNullOrEmpty(use9))
                        {
                            ipt.M9UseNew = use9;
                        }
                        if (!String.IsNullOrEmpty(use10))
                        {
                            ipt.M10UseNew = use10;
                        }
                        if (!String.IsNullOrEmpty(use11))
                        {
                            ipt.M11UseNew =use11;
                        }
                        if (!String.IsNullOrEmpty(use12))
                        {
                            ipt.M12UseNew = use12;
                        }
                        if (!String.IsNullOrEmpty(remain))
                        {
                            ipt.RemainNew =remain;
                        }
                        ipt.CreateUserid = UserInfo.GetUserID();
                        ipt.CreateName = UserInfo.GetUserName();
                        ipt.CreateTime = DateTime.Now;
                        ipt.Validflag = "1";
                        listc.Add(ipt);
                    }
                }
                if (listc.Count <= 0)
                {
                    MessageUtil.ShowWarning("请勾选您要确认导入的数据行！");
                    return;
                }
                str = str1;
                String strtips1 = "确定更新"+strYear+"年的";
                int ii = 0;
                if (ck_yu.Checked)
                {
                    str[0] = "1";
                    strtips1 = strtips1 + "年初余额 ";
                    ii = ii + 1;
                }
                String strtips="";
                if (ck_1m.Checked)
                {
                    str[1] = "1";
                    strtips = strtips + "去年12月 ";
                    ii = ii + 1;
                }
                if (ck_2m.Checked)
                {
                    str[2] = "1";
                    strtips = strtips + "1月 ";
                    ii = ii + 1;
                }
                if (ck_3m.Checked)
                {
                    str[3] = "1";
                    strtips = strtips + "2月 ";
                    ii = ii + 1;
                }
                if (ck_4m.Checked)
                {
                    str[4] = "1";
                    strtips = strtips + "3月 ";
                    ii = ii + 1;
                }
                if (ck_5m.Checked)
                {
                    str[5] = "1";
                    strtips = strtips + "4月 ";
                    ii = ii + 1;
                }
                if (ck_6m.Checked)
                {
                    str[6] = "1";
                    strtips = strtips + "5月 ";
                    ii = ii + 1;
                }
                if (ck_7m.Checked)
                {
                    str[7] = "1";
                    strtips = strtips + "6月 ";
                    ii = ii + 1;
                }
                if (ck_8m.Checked)
                {
                    str[8] = "1";
                    strtips = strtips + "7月 ";
                    ii = ii + 1;
                }
                if (ck_9m.Checked)
                {
                    str[9] = "1";
                    strtips = strtips + "8月 ";
                    ii = ii + 1;
                }
                if (ck_10m.Checked)
                {
                    str[10] = "1";
                    strtips = strtips + "9月 ";
                    ii = ii + 1;
                }
                if (ck_11m.Checked)
                {
                    str[11] = "1";
                    strtips = strtips + "10月 ";
                    ii = ii + 1;
                }
                if (ck_12m.Checked)
                {
                    str[12] = "1";
                    strtips = strtips + "11月 ";
                    ii = ii + 1;
                }
                if (ii <= 0)
                {
                    MessageUtil.ShowTips("请指定更新内容");
                    return;
                }
                if(!String.IsNullOrEmpty(strtips))
                {
                    strtips = "和"+strtips + "的消耗";
                    strtips1 = strtips1 + strtips;
                }
                strtips1 = strtips1 + "项目，共" + listc.Count + "条数据？";
                if (MessageUtil.ShowYesNoAndQuestion(strtips1) != DialogResult.Yes) return;
                CoreResult rt = this.execute<CoreResult>("com.hnshituo.pur.requireplan.service.OilImportService", "insert_Import", new object[] { listc, strYear, str });
                if (rt.Resultcode == 0)
                {
                    MessageUtil.ShowTips("操作成功");
                    ultraGrid1.Refresh();
                    strTips = "1";
                    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>
        /// 打开选择的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 <14)
            {
                MessageUtil.ShowWarning("导入的Excel列数与模板列数不符！");
                return;
            }
            DataTable dt = new DataTable();
            for (int j = 0; j < 14 + 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
                    {
                        if (!String.IsNullOrEmpty(cell.ToString().Trim()))
                        {
                            if (StringUtil.IsDouble(cell.ToString().Trim()))
                            {
                                dr[i] = Convert.ToDecimal(Decimal.Parse(cell.ToString().Trim(), System.Globalization.NumberStyles.Float));
                            }
                            else
                            {
                                dr[i] = cell.ToString().Trim();
                            }
                            k = k + 1;
                        }
                    }
                }
                if (k >= 1)
                {
                    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.ultraToolbarsManager1.Toolbars[0].Tools["doImport"].InstanceProps.Visible = DefaultableBoolean.True;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doSure"].InstanceProps.Visible = DefaultableBoolean.True;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doExcel"].InstanceProps.Visible = DefaultableBoolean.True;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doClear"].InstanceProps.Visible = DefaultableBoolean.True;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doQuery"].InstanceProps.Visible = DefaultableBoolean.False;
            }
            else
            {
                this.ultraToolbarsManager1.Toolbars[0].Tools["doImport"].InstanceProps.Visible = DefaultableBoolean.False;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doSure"].InstanceProps.Visible = DefaultableBoolean.False;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doExcel"].InstanceProps.Visible = DefaultableBoolean.True;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doClear"].InstanceProps.Visible = DefaultableBoolean.False;
                this.ultraToolbarsManager1.Toolbars[0].Tools["doQuery"].InstanceProps.Visible = DefaultableBoolean.True;
            }
        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string path = "";
            try
            {
                path = System.Windows.Forms.Application.StartupPath + "\\OrderTemplate\\OilCardUse.xls";
                System.Diagnostics.Process.Start(path);
            }
            catch (Exception ex)
            {
                MessageUtil.ShowWarning("没有可用的模板！");
            }
        }

        private void ck_ctime_CheckedChanged(object sender, EventArgs e)
        {
            txtCreateTimeEnd.Enabled = ck_ctime.Checked;
            txtCreateTimeStart.Enabled = ck_ctime.Checked;
        }
        /// <summary>
        /// 判断是否为正数数字
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private bool IsNum(string str)
        {
            if (str != "")
            {
                return StringUtil.IsNumber(str);
            }
            return true;
        }


    }
}