JBLEAR-STD/Stone.WinForm/Stone.WinModule/JISData/frmJISLABReport.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Stone.Common;

namespace Stone.WinModule.JISData
{
    public partial class frmJISLABReport : Stone.WinModule.frmBase
    {
        Gm_WMS.DataAccess.DataService.LocalDBService db = new Gm_WMS.DataAccess.DataService.LocalDBService();
        public frmJISLABReport()
        {
            InitializeComponent();

            this.dateTimePicker2.Value = DateTime.Now.AddYears(2);
        }

        private void frmJISLABReport_Load(object sender, EventArgs e)
        {

        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            this.btnQuery.Enabled = false;
            this.btnQuery.Update();

            try
            {
                string d1 = MyDateTime.Format(this.dateTimePicker1.Value, MyDateTimeType.Date);
                string d2 = MyDateTime.Format(this.dateTimePicker2.Value, MyDateTimeType.Date);

                if (this.radioButton1.Checked)
                {
                    Query1(d1, d2);
                }
                else
                {
                    Query2(d1, d2);
                }

            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }
            finally
            {
                this.btnQuery.Enabled = true;
                Application.DoEvents();
            }
        }

        private void Query1(string d1, string d2)
        {

            #region 总数量获取
            string sql = @"
                    select 
                    partNumber, 
                    [Description], 
                    max(releaseId) as releaseId,
                    SUM(quantity) as TotolQuantity 
                    from v_JIS_Lab
                    where [startDate]>='{0}' and [startDate]<='{1}'
                    group by
                    partNumber, 
                    [Description]
                    order by partNumber
                ";
            object[] obj = new object[2];
            obj[0] = d1;
            obj[1] = d2;
            sql = string.Format(sql, obj);

            DataSet dsData = db.Exec_DataSet(sql);

            #endregion

            #region 日期获取
            sql = @"
                    select distinct  convert(varchar(10),startDate,120) + ' ' + convert(varchar(10),endDate,120) as d  
                    from v_JIS_Lab
                    where [startDate]>='{0}' and [startDate]<='{1}'
                    order by d asc
                ";
            sql = string.Format(sql, obj);

            DataSet dsDataD = db.Exec_DataSet(sql);
            foreach (DataRow drData in dsDataD.Tables[0].Rows)
            {
                dsData.Tables[0].Columns.Add(drData["d"].ToString(), typeof(System.Int32));
            }

            #endregion

            #region 日期数量统计

            sql = @"
                    select  
                    partNumber,
                    convert(varchar(10),startDate,120) + ' ' + convert(varchar(10),endDate,120) as d,
                    SUM(quantity) as q
                    from v_JIS_Lab
                    where [startDate]>='{0}' and [startDate]<='{1}'
                    group by partNumber, startDate, endDate
                    order by partNumber asc, d asc

                ";
            sql = string.Format(sql, obj);

            DataSet dsValue = db.Exec_DataSet(sql);


            #endregion

            for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
            {
                string partNumber = dsData.Tables[0].Rows[i]["partNumber"].ToString();
                for (int j = 0; j < dsData.Tables[0].Columns.Count; j++)
                {
                    string colName = dsData.Tables[0].Columns[j].ColumnName;
                    if (colName.Length != 21) continue;

                    DataRow[] drs = dsValue.Tables[0].Select("[partNumber]='" + partNumber + "' and [d]='" + colName + "'");
                    if (drs.Length > 0)
                    {
                        dsData.Tables[0].Rows[i][j] = drs[0]["q"].ToString();
                    }
                }
            }

            this.dgrdView.DataSource = null;
            this.dgrdView.DataSource = dsData.Tables[0];
            this.dgrdView.Columns["PartNumber"].HeaderText = "零件号";
            this.dgrdView.Columns["Description"].HeaderText = "零件描述";
            this.dgrdView.Columns["releaseId"].HeaderText = "版本号";
            this.dgrdView.Columns["TotolQuantity"].HeaderText = "总需求数量"; 

            MyGridViewStyle.SetDataGridMenuCommon(this.dgrdView);

            this.toolStripStatusLabel1.Text = "记录数：" + this.dgrdView.Rows.Count;

        }

        private void Query2(string d1, string d2)
        {

            #region 总数量获取

            string sql = @"
                    select 
                       partNumber,
                       Description,
                       max(releaseId) as releaseId,
                       startDate,
                       endDate,
                       SUM(quantity) as quantity
                    from v_JIS_Lab
                    where quantity <>0 and [startDate]>='{0}' and [startDate]<='{1}'
                    group by partNumber, Description, startDate,endDate
                    order by startDate, partNumber
                ";
            object[] obj = new object[2];
            obj[0] = d1;
            obj[1] = d2;
            sql = string.Format(sql, obj);

            DataSet dsData = db.Exec_DataSet(sql);
            MyGridViewStyle.SetDataGridMenuCommon(this.dgrdView);

            #endregion

            this.dgrdView.DataSource = null;
            this.dgrdView.DataSource = dsData.Tables[0];

            this.dgrdView.Columns["PartNumber"].HeaderText = "零件号";
            this.dgrdView.Columns["Description"].HeaderText = "零件描述";
            this.dgrdView.Columns["releaseId"].HeaderText = "版本号";
            this.dgrdView.Columns["startDate"].HeaderText = "需求开始日期";
            this.dgrdView.Columns["endDate"].HeaderText = "需求结束日期";
            this.dgrdView.Columns["quantity"].HeaderText = "需求数量";


            this.toolStripStatusLabel1.Text = "记录数：" + this.dgrdView.Rows.Count;

        }

        private void btnOut_Click(object sender, EventArgs e)
        {
            try
            {
                //if (this.radioButton1.Checked)
                //{
                //    if (this.dgrdView.DataSource == null) throw new Exception("请先查询数据");

                //    if (this.saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                //    {




                //        DataTable dtData = (DataTable)this.dgrdView.DataSource;
                //        DataSet dsData = new DataSet();
                //        dsData.Tables.Add(dtData.Copy());

                //        ExportExcelX(this.saveFileDialog1.FileName, "LAB数据", dsData);



                //    }
                //}
                //else
                //{
                //    MyExport.ShowExport(this.dgrdView);
                //}

                if (this.dgrdView.DataSource == null) throw new Exception("请先查询数据");

                MyExport.ShowExport(this.dgrdView);
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }
        }


        public static void ExportExcelX(string filename, string title, DataSet dsData)
        {
            try
            {
                MyExcelX excel = new MyExcelX();
                excel.NewExcel(filename);
                excel.NewSheet(title);


                //创建第一行的标题列
                int rowindex = 0;
                excel.CreateRow(rowindex);
                excel.CreateCell(rowindex, 0);
                excel.SetCellValue(rowindex, 0, title);

                //创建第二行的字段
                rowindex++;
                excel.CreateRow(rowindex, 1000);
                for (int i = 0; i < dsData.Tables[0].Columns.Count; i++)
                {
                    excel.CreateCell(rowindex, i);
                    excel.SetCellValue(rowindex, i, dsData.Tables[0].Columns[i].ColumnName.Replace(" ", "\n"));
                }

                rowindex++;
                foreach (DataRow drData in dsData.Tables[0].Rows)
                {
                    excel.CreateRow(rowindex);
                    for (int k = 0; k < dsData.Tables[0].Columns.Count; k++)
                    {
                        excel.CreateCell(rowindex, k);
                        //excel.SetCellValue(rowindex, k, drData[dsData.Tables[0].Columns[k].ColumnName].ToString());

                        if (dsData.Tables[0].Columns[k].DataType == typeof(System.Int32))
                        {
                            if (drData[dsData.Tables[0].Columns[k].ColumnName] != DBNull.Value)
                            {
                                excel.SetCellValue(rowindex, k, Convert.ToDouble(drData[dsData.Tables[0].Columns[k].ColumnName]));
                            }
                        }
                        else
                        {
                            excel.SetCellValue(rowindex, k, drData[dsData.Tables[0].Columns[k].ColumnName].ToString());
                        }


                    }
                    rowindex++;
                }

   


                excel.Save();

                MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }


        }

       
    }
}