using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Shared;
using CrystalDecisions.Windows.Forms;
using System.Data.SqlClient;

namespace Aram_CRM_Project.UIL
{
    public partial class frmPrintReport : Form
    {
        public frmPrintReport()
        {
            InitializeComponent();
        }

        private string _Mode = string.Empty;
        private string _ConditionQuery = string.Empty;

        public string ConditionQuery
        {
            set { _ConditionQuery = value; }
            get { return _ConditionQuery; }
        }

        public string Mode
        {
            set { _Mode = value; }
            get { return _Mode; }
        }

        private string[] QueryColumnName;
        private string[] PersianColumnName;
        private string[] ColumnsWidth;
        private int[] CheckBoxValue;

        
        private int CheckedColQuantity;
        private int ColQuantity = 0;
        private string TableName = string.Empty;
        private string QueryReport = string.Empty;
        ArrayList CheckedColumnsWidth = new ArrayList();

        private void frmPrintReport_Load(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            //Create DataGridView Columns
            DataGridViewColumn ColCheckBox = new DataGridViewColumn();
            ColCheckBox.HeaderText = "";
            ColCheckBox.Name = "ColCheckBox";
            ColCheckBox.CellTemplate = new DataGridViewCheckBoxCell();
            dgvColumns.Columns.Add(ColCheckBox);

            DataGridViewColumn ColPersianName = new DataGridViewColumn();
            ColPersianName.HeaderText = "نام ستون";
            ColPersianName.Name = "ColPersianName";
            ColPersianName.CellTemplate = new DataGridViewTextBoxCell();
            dgvColumns.Columns.Add(ColPersianName);

            DataGridViewColumn ColEnglishName = new DataGridViewColumn();
            ColEnglishName.Name = "ColEnglishName";
            ColEnglishName.Visible = false;
            ColEnglishName.CellTemplate = new DataGridViewTextBoxCell();
            dgvColumns.Columns.Add(ColEnglishName);

            DataGridViewColumn ColWidth = new DataGridViewColumn();
            ColWidth.HeaderText = "اندازه ستون";
            ColWidth.Name = "ColWidth";
            ColWidth.CellTemplate = new DataGridViewTextBoxCell();
            dgvColumns.Columns.Add(ColWidth);

            dgvColumns.Columns[0].Width = 25;
            dgvColumns.Columns[1].Width = 150;

            //ِDate
            string[] Date = Aram_CRM_Project.BLL.Entity.StaticVariable.SystemDate.Split('/');
            nudDay.Value = decimal.Parse(Date[2]);
            nudMonth.Value = decimal.Parse(Date[1]);
            nudYear.Value = decimal.Parse(Date[0]);

            switch (Mode)
            {
                case "User":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblUser.Id Desc) AS nvarchar(30))", "dbo.tblUser.Name", "dbo.tblUser.Username", "dbo.tblUserStatus.Name", "dbo.tblUser.Comment" };
                        PersianColumnName = new string[] { "ردیف", "نام کاربر", "کلمه کاربری", "وضعیت", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "2000", "1500", "1000", "4000" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 1 };
                        TableName = "dbo.tblUser INNER JOIN dbo.tblUserStatus ON dbo.tblUser.UserStatusId = dbo.tblUserStatus.Id";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست کاربران";
                        break;
                    }
                case "CityState":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblCityState.Id Desc) AS nvarchar(30))", "dbo.tblCityState.Name" };
                        PersianColumnName = new string[] { "ردیف", "نام منطقه" };
                        ColumnsWidth = new string[] { "900", "2000" };
                        CheckBoxValue = new int[] { 1, 1 };
                        TableName = "dbo.tblCityState";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست مناطق شهری";
                        break;
                    }
                case "CrType":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblCrType.Id Desc) AS nvarchar(30))", "dbo.tblCrType.Name", "dbo.tblCrType.Comment" };
                        PersianColumnName = new string[] { "ردیف", "نحوه ارتباط","توضیحات" };
                        ColumnsWidth = new string[] { "900", "3000", "4000" };
                        CheckBoxValue = new int[] { 1, 1, 1 };
                        TableName = "dbo.tblCrType";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست نحوه ارتباط با مشتری";
                        break;
                    }
                case "RelationshipWay":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblRelationshipWay.Id Desc) AS nvarchar(30))", "dbo.tblRelationshipWay.Name", "dbo.tblRelationshipWay.StartDate", "dbo.tblRelationshipWay.EndDate", "dbo.tblRelationshipWay.Address", "dbo.tblRelationshipWay.Comment" };
                        PersianColumnName = new string[] { "ردیف", "نام", "تاریخ شروع", "تاریخ پایان", "آدرس", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "2000", "1200", "1200", "4000", "3000" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 1, 0 };
                        TableName = "dbo.tblRelationshipWay";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست نحوه آشنایی مشتریان";
                        break;
                    }
                case "CustomerGroup":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblCustomerGroup.Id Desc) AS nvarchar(30))", "dbo.tblCustomerGroup.Name", "dbo.tblCustomerGroup.Comment" };
                        PersianColumnName = new string[] { "ردیف", "نام گروه", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "3000", "5000" };
                        CheckBoxValue = new int[] { 1, 1, 1 };
                        TableName = "dbo.tblCustomerGroup";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست گروه مشتریان";
                        break;
                    }
                case "StuffGroup":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblStuffGroup.Id Desc) AS nvarchar(30))", "dbo.tblStuffGroup.Name", "dbo.tblStuffGroup.Comment" };
                        PersianColumnName = new string[] { "ردیف", "نام گروه کالا", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "3000", "5000" };
                        CheckBoxValue = new int[] { 1, 1, 1 };
                        TableName = "dbo.tblStuffGroup";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست گروه کالا";
                        break;
                    }
                case "Stuff":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblStuff.Id Desc) AS nvarchar(30))", "dbo.tblStuff.Name", "dbo.tblStuffGroup.Name", "REPLACE(dbo.tblStuff.Feature,'$',', ')", "dbo.tblStuff.Comment" };
                        PersianColumnName = new string[] { "ردیف", "نام کالا", "گروه کالا", "ویژگی ها", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "3000", "2000", "4000", "3000" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 0 };
                        TableName = "dbo.tblStuff INNER JOIN dbo.tblStuffGroup ON dbo.tblStuff.StuffGroupId = dbo.tblStuffGroup.Id";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست کالا";
                        break;
                    }
                case "Reminder":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblReminder.Id Desc) AS nvarchar(30))", "dbo.tblReminder.Name", "dbo.tblReminder.Date", "dbo.tblReminder.Comment" };
                        PersianColumnName = new string[] { "ردیف", "موضوع", "تاریخ", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "4000", "1200", "3000" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1 };
                        TableName = "dbo.tblReminder";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست یادآوری ها";
                        break;
                    }
                case "ReminderForAdmin":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblReminder.Id Desc) AS nvarchar(30))", "dbo.tblReminder.Name", "dbo.tblReminder.Date", "dbo.tblUser.Name", "dbo.tblReminder.Comment" };
                        PersianColumnName = new string[] { "ردیف", "موضوع", "تاریخ", "ایجاد کننده", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "4000", "1200", "2000", "3000" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 0 };
                        TableName = "dbo.tblReminder INNER JOIN dbo.tblUser ON dbo.tblReminder.UserId = dbo.tblUser.Id";
                        rdoPortrait.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست یادآوری ها";
                        break;
                    }
                case "Reserve":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblReserve.Id Desc) AS nvarchar(30))", "CAST(dbo.tblReserve.Id AS nvarchar(50))", "dbo.tblCustomer.Name", "dbo.tblReserve.Date", "dbo.tblCustomerGroup.Name", "dbo.tblCityState.Name", "dbo.tblPayType.Name", "dbo.tblResultReserve.Name", "dbo.tblReserve.PayerName", "dbo.tblReserve.ReceptionName" };
                        PersianColumnName = new string[] { "ردیف", "کد رزرو", "نام و نام خانوادگی", "تاریخ", "گروه مشتریان", "منطقه شهری", "نحوه پرداخت", "نتیجه رزرو", "پرداخت کننده", "دریافت کننده" };
                        ColumnsWidth = new string[] { "900", "700", "2500", "1200", "2500", "2000", "2000", "2000", "2500", "2500" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 1, 1, 1, 1, 0, 0 };
                        TableName = "dbo.tblReserve INNER JOIN dbo.tblCustomer ON dbo.tblReserve.CustomerId = dbo.tblCustomer.Id INNER JOIN dbo.tblResultReserve ON dbo.tblReserve.ResultReserveId = dbo.tblResultReserve.Id INNER JOIN dbo.tblPayType ON dbo.tblReserve.PayTypeId = dbo.tblPayType.Id INNER JOIN dbo.tblCustomerGroup ON dbo.tblCustomer.CustomerGroupId = dbo.tblCustomerGroup.Id INNER JOIN dbo.tblCityState ON dbo.tblCustomer.CityStateId = dbo.tblCityState.Id";
                        rdoLandscape.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست رزرو سفارش";
                        break;
                    }
                case "Order":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblOrder.Id Desc) AS nvarchar(30))", "CAST(dbo.tblOrder.Id AS nvarchar(50))", "dbo.tblCustomer.Name", "dbo.tblOrder.Date", "dbo.tblCustomerGroup.Name", "dbo.tblCityState.Name", "dbo.tblPayType.Name" };
                        PersianColumnName = new string[] { "ردیف", "کد سفارش", "نام و نام خانوادگی", "تاریخ", "گروه مشتریان", "منطقه شهری", "نحوه پرداخت" };
                        ColumnsWidth = new string[] { "900", "1200", "2500", "1200", "2000", "1500", "1500" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 1, 1, 1 };
                        TableName = "dbo.tblOrder INNER JOIN dbo.tblCustomer ON dbo.tblOrder.CustomerId = dbo.tblCustomer.Id INNER JOIN dbo.tblPayType ON dbo.tblOrder.PayTypeId = dbo.tblPayType.Id INNER JOIN dbo.tblCustomerGroup ON dbo.tblCustomer.CustomerGroupId = dbo.tblCustomerGroup.Id INNER JOIN dbo.tblCityState ON dbo.tblCustomer.CityStateId = dbo.tblCityState.Id";
                        rdoLandscape.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست سفارش";
                        break;
                    }
                case "Cr":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblCr.Id DESC) AS nvarchar(30))", "dbo.tblCr.Subject", "dbo.tblCustomer.Name", "dbo.tblCustomer.Tel", "dbo.tblCustomer.Address", "dbo.tblCustomerGroup.Name", "dbo.tblCr.Date", "dbo.tblCrType.Name", "dbo.tblResultCr.Name", "dbo.tblCr.Comment" };
                        PersianColumnName = new string[] { "ردیف", "موضوع", "نام و نام خانوادگی", "تلفن", "آدرس", "گروه مشتریان", "تاریخ", "نحوه ارتباط", "نتیجه ارتباط", "توضیحات" };
                        ColumnsWidth = new string[] { "900", "2500", "2500", "1500", "3000", "2000", "1200", "2000","2000","3000" };
                        CheckBoxValue = new int[] { 1, 1, 1, 0, 0, 1, 1, 1, 1, 0 };
                        TableName = "dbo.tblCr INNER JOIN dbo.tblCustomer ON dbo.tblCr.CustomerId = dbo.tblCustomer.Id INNER JOIN dbo.tblCustomerGroup ON dbo.tblCustomer.CustomerGroupId = dbo.tblCustomerGroup.Id INNER JOIN dbo.tblResultCr ON dbo.tblCr.ResultCrId = dbo.tblResultCr.Id INNER JOIN dbo.tblCrType ON dbo.tblCr.CrTypeId = dbo.tblCrType.Id";
                        rdoLandscape.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست ارتباط با مشتریان";
                        break;
                    }
                case "Customer":
                    {
                        QueryColumnName = new string[] { "CAST(ROW_NUMBER() OVER(ORDER BY dbo.tblCustomer.Id DESC) AS nvarchar(30))", "dbo.tblCustomer.Name", "dbo.tblCustomer.Tel", "dbo.tblCustomer.Cell", "dbo.tblCustomer.BirthDate", "dbo.tblCustomer.MarriageDate", "dbo.tblCustomer.Address", "dbo.tblCustomerGroup.Name", "dbo.tblCityState.Name", "dbo.tblRelationshipWay.Name" };
                        PersianColumnName = new string[] { "ردیف", "نام و نام خانوادگی", "تلفن", "موبایل", "تاریخ تولد", "تاریخ ازدواج", "آدرس", "گروه مشتریان", "منطقه شهری", "نحوه آشنایی" };
                        ColumnsWidth = new string[] { "900", "2500", "1500", "1500", "1200", "1200", "5500", "2500", "2500", "2500" };
                        CheckBoxValue = new int[] { 1, 1, 1, 1, 1, 1, 1, 0, 0, 0 };
                        TableName = "dbo.tblCustomer INNER JOIN dbo.tblCityState ON dbo.tblCustomer.CityStateId = dbo.tblCityState.Id INNER JOIN dbo.tblCustomerGroup ON dbo.tblCustomer.CustomerGroupId = dbo.tblCustomerGroup.Id INNER JOIN dbo.tblRelationshipWay ON dbo.tblCustomer.RelationshipWayId = dbo.tblRelationshipWay.Id";
                        rdoLandscape.Checked = true;
                        //Show Columns To DataGridView
                        FillDataGridView();

                        txtPrintTitle.Text = "لیست مشتریان";
                        break;

                    }
            }

            //ColQuantity = QueryColumnName.Length;
            ColQuantity = 20;
            btnShow_Click(sender, e);

            this.Cursor = Cursors.Default;

        }

        
        private void FillDataGridView()
        {
            for (int i = 0; i < PersianColumnName.Length; i++)
            {
                DataGridViewRow NewRow = new DataGridViewRow();
                dgvColumns.Rows.Add(NewRow);
                dgvColumns.Rows[dgvColumns.Rows.Count - 1].Cells[0].Value = CheckBoxValue[i];
                dgvColumns.Rows[dgvColumns.Rows.Count - 1].Cells[1].Value = PersianColumnName[i];
                dgvColumns.Rows[dgvColumns.Rows.Count - 1].Cells[2].Value = QueryColumnName[i];
                dgvColumns.Rows[dgvColumns.Rows.Count - 1].Cells[3].Value = ColumnsWidth[i];
            }
        }

        private string CreateQueryAndParameters()
        {
            ReportDocument ReportDoc;
            ParameterFields ParamFields;

            ParameterField ParamField;
            ParameterDiscreteValue ParamDiscreteValue;
            
            ReportDoc = new ReportDocument();
            ParamFields = new ParameterFields();

            CheckedColumnsWidth.Clear();
            string Query = "Select ";
            CheckedColQuantity = 0;

            for (int j = 0; j < dgvColumns.Rows.Count; j++)
            {
                if (dgvColumns.Rows[j].Cells[0].Value.ToString() == "1" || dgvColumns.Rows[j].Cells[0].Value.ToString() == "True")
                {
                    CheckedColQuantity++;
                    if (Query.Contains("Column"))
                        Query = Query.Insert(Query.Length, ", ");

                    Query = Query.Insert(Query.Length, dgvColumns.Rows[j].Cells[2].Value.ToString() + " AS Column" + CheckedColQuantity.ToString());

                    ParamField = new ParameterField();
                    ParamField.Name = "Col" + CheckedColQuantity.ToString();
                    ParamDiscreteValue = new ParameterDiscreteValue();
                    ParamDiscreteValue.Value = dgvColumns.Rows[j].Cells[1].Value.ToString();
                    ParamField.CurrentValues.Add(ParamDiscreteValue);
                    ParamFields.Add(ParamField);

                    if (dgvColumns.Rows[j].Cells[3].Value == null)
                    {
                        dgvColumns.Rows[j].Cells[3].Value = "0";
                    }
                    CheckedColumnsWidth.Add(int.Parse(dgvColumns.Rows[j].Cells[3].Value.ToString()));
                }
            }

            //Set Empty Value For Remaining Parameters
            for (int i = CheckedColQuantity; i < ColQuantity; i++)
            {
                ParamField = new ParameterField();
                ParamField.Name = "Col" + (i + 1).ToString();
                ParamDiscreteValue = new ParameterDiscreteValue();
                ParamDiscreteValue.Value = "";
                ParamField.CurrentValues.Add(ParamDiscreteValue);
                ParamFields.Add(ParamField);
            }

            //Set Parameter To CrystalReportViewer
            crvPrint.ParameterFieldInfo = ParamFields;
            Query += " FROM " + TableName + " " + ConditionQuery;

            return Query;
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            frmMessage fMessage = new frmMessage();

            string Day, Month;
            if (nudDay.Value >= 1 && nudDay.Value <= 9)
                Day = "0" + nudDay.Value.ToString();
            else
                Day = nudDay.Value.ToString();
            if (nudMonth.Value >= 1 && nudMonth.Value <= 9)
                Month = "0" + nudMonth.Value.ToString();
            else
                Month = nudMonth.Value.ToString();

            //Create Query
            QueryReport = CreateQueryAndParameters();

            //Prevent UnChecked CheckBox
            if (CheckedColQuantity == 0)
            {
                fMessage.ErrorNoSelectColumn();
                this.Cursor = Cursors.Default;
                return;
            }

            int ColLeft;
            Aram_CRM_Project.CrystalReport.rptPrint RptPrint = new Aram_CRM_Project.CrystalReport.rptPrint();

            //Check Page Status
            if (rdoPortrait.Checked == true)
            {
                RptPrint.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Portrait;
                ColLeft = 10545;
                //BoxLandscape,LineLandscape.Visible=False;
                RptPrint.Section2.ReportObjects["BoxLandscape"].ObjectFormat.EnableSuppress = true;
                RptPrint.Section2.ReportObjects["LineLandscape"].ObjectFormat.EnableSuppress = true;
                
                //Set Location,Width Title And Date
                RptPrint.Section2.ReportObjects["Date"].Left = 8280;
            }
            else
            {
                RptPrint.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape;
                ColLeft = 15495;
                //BoxPortrait,LinePortrait.Visible=False;
                RptPrint.Section2.ReportObjects["BoxPortrait"].ObjectFormat.EnableSuppress = true;
                RptPrint.Section2.ReportObjects["LinePortrait"].ObjectFormat.EnableSuppress = true;
                //Set Location,Width Title And Date
                RptPrint.Section2.ReportObjects["Date"].Left = 13320;
                RptPrint.Section2.ReportObjects["Title"].Width = 12000;
            }

            //Set Value To Report Document
            for (int i = 1; i <= CheckedColQuantity; i++)
            {
                RptPrint.ReportDefinition.ReportObjects["Col" + i.ToString()].Width = int.Parse(CheckedColumnsWidth[i - 1].ToString());
                RptPrint.ReportDefinition.ReportObjects["Column" + i.ToString()].Width = int.Parse(CheckedColumnsWidth[i - 1].ToString());
                ColLeft = ColLeft - (160 + RptPrint.ReportDefinition.ReportObjects["Col" + i.ToString()].Width);
                if (ColLeft < 0)
                {
                    fMessage.ErrorSizeColumn();
                    this.Cursor = Cursors.Default;
                    return;
                }
                else
                {
                    RptPrint.ReportDefinition.ReportObjects["Col" + i.ToString()].Left = ColLeft;
                    RptPrint.ReportDefinition.ReportObjects["Column" + i.ToString()].Left = ColLeft;
                }
            }

            RptPrint.SetDataSource(FillDataSet("dtPrint"));
            RptPrint.SetParameterValue("Title", txtPrintTitle.Text.Trim());
            RptPrint.SetParameterValue("Date", nudYear.Value.ToString() + "/" + Month + "/" + Day);

            crvPrint.ReportSource = RptPrint;
            crvPrint.ShowExportButton = false;
            crvPrint.ShowRefreshButton = false;

            this.Cursor = Cursors.Default;
        }

        private DataTable FillDataSet(string DataTableName)
        {
            frmMessage fMessage = new frmMessage();
            try
            {
                SqlConnection Connection = new SqlConnection(Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource);
                SqlDataAdapter DataAdaptor = new SqlDataAdapter(QueryReport, Connection);
                DataSet DataSetReport = new DataSet();
                DataAdaptor.Fill(DataSetReport, DataTableName);
                return DataSetReport.Tables[DataTableName];
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("Incorrect syntax near the keyword 'FROM'"))
                    fMessage.ErrorNoSelectColumn();
                else
                    MessageBox.Show(exp.Message);

                return null;
            }
        }

        private void dgvColumns_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            if (dgvColumns.CurrentCell.ColumnIndex == 3)
            {
                e.Control.KeyDown += new KeyEventHandler(this.NumericCell_KeyDown);
            }
        }

        private void NumericCell_KeyDown(object sender, KeyEventArgs e)
        {
            if (!(e.KeyCode >= Keys.D0 && e.KeyCode <= Keys.D9) && !(e.KeyCode == Keys.Back) && !(e.KeyCode == Keys.Delete) && !(e.KeyCode == Keys.Left) && !(e.KeyCode == Keys.Right))
                e.SuppressKeyPress = true;
        }

        private void ShowReport()
        {
            this.Cursor = Cursors.WaitCursor;

            frmMessage fMessage = new frmMessage();

            string Day, Month;
            if (nudDay.Value >= 1 && nudDay.Value <= 9)
                Day = "0" + nudDay.Value.ToString();
            else
                Day = nudDay.Value.ToString();
            if (nudMonth.Value >= 1 && nudMonth.Value <= 9)
                Month = "0" + nudMonth.Value.ToString();
            else
                Month = nudMonth.Value.ToString();

            //Create Query
            QueryReport = CreateQueryAndParameters();

            //Prevent UnChecked CheckBox
            if (CheckedColQuantity == 0)
            {
                fMessage.ErrorNoSelectColumn();
                this.Cursor = Cursors.Default;
                return;
            }

            int ColLeft;
            Aram_CRM_Project.CrystalReport.rptPrint RptPrint = new Aram_CRM_Project.CrystalReport.rptPrint();

            //Check Page Status
            if (rdoPortrait.Checked == true)
            {
                RptPrint.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Portrait;
                ColLeft = 10545;
                //BoxLandscape,LineLandscape.Visible=False;
                RptPrint.Section2.ReportObjects["BoxLandscape"].ObjectFormat.EnableSuppress = true;
                RptPrint.Section2.ReportObjects["LineLandscape"].ObjectFormat.EnableSuppress = true;
                //Set Location,Width Title And Date
                RptPrint.Section2.ReportObjects["Date"].Left = 8280;
            }
            else
            {
                RptPrint.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape;
                ColLeft = 15495;
                //BoxPortrait,LinePortrait.Visible=False;
                RptPrint.Section2.ReportObjects["BoxPortrait"].ObjectFormat.EnableSuppress = true;
                RptPrint.Section2.ReportObjects["LinePortrait"].ObjectFormat.EnableSuppress = true;
                //Set Location,Width Title And Date
                RptPrint.Section2.ReportObjects["Date"].Left = 13320;
                RptPrint.Section2.ReportObjects["Title"].Width = 12000;
            }

            //Set Value To Report Document
            for (int i = 1; i <= CheckedColQuantity; i++)
            {
                RptPrint.ReportDefinition.ReportObjects["Col" + i.ToString()].Width = int.Parse(CheckedColumnsWidth[i - 1].ToString());
                RptPrint.ReportDefinition.ReportObjects["Column" + i.ToString()].Width = int.Parse(CheckedColumnsWidth[i - 1].ToString());
                ColLeft = ColLeft - (160 + RptPrint.ReportDefinition.ReportObjects["Col" + i.ToString()].Width);
                if (ColLeft < 0)
                {
                    fMessage.ErrorSizeColumn();
                    this.Cursor = Cursors.Default;
                    return;
                }
                else
                {
                    RptPrint.ReportDefinition.ReportObjects["Col" + i.ToString()].Left = ColLeft;
                    RptPrint.ReportDefinition.ReportObjects["Column" + i.ToString()].Left = ColLeft;
                }
            }

            RptPrint.SetDataSource(FillDataSet("dtPrint"));
            RptPrint.SetParameterValue("Title", txtPrintTitle.Text.Trim());
            RptPrint.SetParameterValue("Date", nudYear.Value.ToString() + "/" + Month + "/" + Day);

            crvPrint.ShowRefreshButton = false;
            crvPrint.ShowExportButton = false;
            crvPrint.ReportSource = RptPrint;
            
            this.Cursor = Cursors.Default;
        }

        
    }
}
