MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundUI/UIFrm/FrmCustReceivableQuery.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;

namespace Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIFrm
{
    public partial class FrmCustReceivableQuery : FrmFundBase
    {
        public FrmCustReceivableQuery()
        {
            InitializeComponent();
        }


        private DataSet dsOne = new DataSet();
        private DataSet dsTwo = new DataSet();
        private DataSet dsThree = new DataSet();
        FundBLL.ValueList.PubValueList PubValueList = null;

        private void FrmCustReceivableQuery_Load(object sender, EventArgs e)
        {
            this.checkBox1.Checked = true;
            this.dateTimePicker2.Format = DateTimePickerFormat.Custom;
            this.dateTimePicker2.CustomFormat = "yyyyMM";
            PubValueList = new Core.StlMes.Client.Sale.SaleFundMgt.FundBLL.ValueList.PubValueList(this.ob);

            dsOne = base.GetDataSetByColumnName(GetDataOneStruct(), null, GetColumnCaption());
            this.ultraGrid1.DataSource = dsOne;
            dsTwo = base.GetDataSetByColumnName(this.GetDataTwoStruct(), null, GetColumnCaption());
            this.ultraGrid2.DataSource = dsTwo;
            dsThree = base.GetDataSetByColumnName(this.GetDataThreeStruct(), null, GetColumnCaption());
            this.ultraGrid2.DataSource = dsThree;
         //   this.ultraGrid3.DataSource = dsThree;
          base.SetGridStyle(ref this.ultraGrid1, ref this.ultraGrid2);
            HIddenColumn();
            Init();

        }

        private void Init()
        {
            try
            {
                this.ultraGrid1.DisplayLayout.Bands[0].Columns["BUYERCODE"].ValueList
                   = PubValueList.GetCustomerInfo();
            }
            catch
            { }

            foreach (string s in GetNumberFormatColumn())
            {
                try
                {
                    string input = "-nn,nnn,nnn.nnnn";
                    if (s == "WEIGHT")
                    {
                        input = "-nn,nnn,nnn.nnn";
                    }
                    try
                    { this.ultraGrid1.DisplayLayout.Bands[0].Columns[s].MaskInput = input; }
                    catch
                    { }

                    try
                    {
                        this.ultraGrid2.DisplayLayout.Bands[0].Columns[s].MaskInput = input;
                    }
                    catch
                    { }
                   
                   
                }
                catch
                { 
                }
            }


            /*
             
               hs.Add("BUYERCODE", 180);
            hs.Add("PREMONEY", 80);
            hs.Add("CURMONEY", 80);
            hs.Add("CUR_RECEIVE_MONEY", 80);
            hs.Add("DE_MONEY", 90);
            hs.Add("SALE_AREA_DESC", 80);
            hs.Add("MONEY", 80);
            hs.Add("PACTNO", 100);
            hs.Add("WEIGHT", 80);
             */

        }


        private string[] GetNumberFormatColumn()
        {
            return new string[] { "PREMONEY", "CURMONEY", "CUR_RECEIVE_MONEY", "MONEY", "WEIGHT" };

        }


        private void HIddenColumn()
        {
            try
            {
                this.ultraGrid1.DisplayLayout.Bands[0].Columns["BALANCESUBJECT"].Hidden = true;
                this.ultraGrid2.DisplayLayout.Bands[0].Columns["BALANCESUBJECT"].Hidden = true;
                this.ultraGrid2.DisplayLayout.Bands[0].Columns["BUYERCODE"].Hidden = true;
                //this.ultraGrid3.DisplayLayout.Bands[0].Columns["BALANCESUBJECT"].Hidden = true;
                //this.ultraGrid3.DisplayLayout.Bands[0].Columns["BUYERCODE"].Hidden = true;
                //this.ultraGrid3.DisplayLayout.Bands[0].Columns["SALE_AREA_DESC"].Hidden = true;
            }
            catch
            { }

            
          

 
        }

        public override void ToolBar_Click(object sender, string ToolbarKey)
        {
            if (ToolbarKey == "Query")
            {
                this.Query();
            }
            if (ToolbarKey == "Close")
            {
                this.Close();
            }

            if (ToolbarKey == "Export")
            {
                Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel(this.Text, this.ultraGrid1);
            }
        }

      
        private string month = "201509";
        private DataSet dsInvoice = null;

        private void Query()
        {
            this.month = this.dateTimePicker2.Value.ToString("yyyyMM");
            DataSet ds = this.GetDataSetOne();
            DeleteZeroRecords(ref ds);
            Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsOne, ds);
            

 
        }

        private void DeleteZeroRecords(ref DataSet ds)
        { 
                 if(ds == null ||ds.Tables.Count == 0|| ds.Tables[0].Rows.Count == 0)
                 {
                     return;
                 }
            foreach(DataRow dr in ds.Tables[0].Rows)
            {
                if (Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(dr["PREMONEY"]) == 0
                   && Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(dr["CURMONEY"]) == 0
                      && Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(dr["CUR_RECEIVE_MONEY"]) == 0
                          && Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(dr["DE_MONEY"]) == 0
                    )
                {
                    dr.Delete();
                }
            }
            ds.AcceptChanges();
        }

        private void QueryTwo()
        {
            this.dsTwo.Clear();
            this.dsThree.Clear();
            Infragistics.Win.UltraWinGrid.UltraGridRow ugr = null;
            if (this.ultraGrid1.Rows.Count == 0)
                return;
            if (this.ultraGrid1.Rows.Count == 1)
                ugr = this.ultraGrid1.Rows[0];
            else
                ugr = this.ultraGrid1.ActiveRow;
            if (ugr == null)
                return;            
             
            string balancesubjct = ugr.Cells["BALANCESUBJECT"].Value.ToString();
             string buyercode = ugr.Cells["BUYERCODE"].Value.ToString();
             DataSet ds = this.GetDataSetTwo(balancesubjct, buyercode);
             Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsTwo, ds);
        }

        private void QueryThree()
        {
            this.dsThree.Clear();
            Infragistics.Win.UltraWinGrid.UltraGridRow ugr = null;
            if (this.ultraGrid2.Rows.Count == 0)
                return;
            if (this.ultraGrid2.Rows.Count == 1)
                ugr = this.ultraGrid2.Rows[0];
            else
                ugr = this.ultraGrid2.ActiveRow;
            if (ugr == null)
                return;       
               string balancesubjct = ugr.Cells["BALANCESUBJECT"].Value.ToString();
             string buyercode = ugr.Cells["BUYERCODE"].Value.ToString();
            string saleareadesc=  ugr.Cells["SALE_AREA_DESC"].Value.ToString();
             DataSet ds = this.GetDataSetThree(balancesubjct, buyercode,saleareadesc);
             Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsThree, ds);
 
        }

        private void QueryThreeF()
        {
            this.dsThree.Clear();
            Infragistics.Win.UltraWinGrid.UltraGridRow ugr = null;
            if (this.ultraGrid1.Rows.Count == 0)
                return;
            if (this.ultraGrid1.Rows.Count == 1)
                ugr = this.ultraGrid1.Rows[0];
            else
                ugr = this.ultraGrid1.ActiveRow;
            if (ugr == null)
                return;
            string balancesubjct = ugr.Cells["BALANCESUBJECT"].Value.ToString();
            string buyercode = ugr.Cells["BUYERCODE"].Value.ToString();
            string saleareadesc = ugr.Cells["SECONDACCOUNT"].Value.ToString();
            DataSet ds = this.GetDataSetThree(balancesubjct, buyercode, saleareadesc);
            Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsThree, ds);

        }


        private DataSet GetDataSetThree(string balancesubject, string buyercode,string sale_area)
        {
            string sql = @"select sum(a.weight) weight,
                                           sum(a.money) money,       
                                           a.sale_area_desc,
                                        a.forecorpcode BALANCESUBJECT, a.buyercode,a.ord_no pactno
                                      from slm_balanceinfo a
                                     where a.balancestatus = '3'
                                       and a.invoicedate >= to_date('{3}', 'yyyymm')
                                       and a.invoicedate < last_day(to_date('{3}', 'yyyymm')) + 1
                                         and a.buyercode = '{0}' and forecorpcode = '{1}' and sale_area_desc = '{2}'
                                     group by  a.sale_area_desc, a.forecorpcode, a.buyercode,a.ord_no";
            sql = string.Format(sql,buyercode,balancesubject,sale_area,this.month);
            DataSet ds = base.ExecuteReader(sql);
            return ds;
        }


        private DataSet GetDataSetTwo(string balancesubject,string buyercode)
        {
                        string sql = @"select sum(a.weight) weight,
                                               sum(a.money) money,       
                                               a.sale_area_desc   , a.forecorpcode balancesubject, a.buyercode                                    
                                          from slm_balanceinfo a
                                         where a.balancestatus = '3'
                                           and a.invoicedate >= to_date('{2}', 'yyyymm')
                                           and a.invoicedate < last_day(to_date('{2}', 'yyyymm')) + 1
                                           and a.buyercode = '{0}' and forecorpcode = '{1}'
                                         group by  a.sale_area_desc, a.forecorpcode, a.buyercode";
                        sql = string.Format(sql, buyercode, balancesubject, this.month);
                        DataSet ds = base.ExecuteReader(sql);
                        return ds;

 
        }

        private DataSet GetDataSetOne()
        {
            string sql = @" with t as (
                                        select b.balancesubject,
                                               b.buyercode,
                                            a.secondaccount,
                                               sum(case
                                                     when a.inouttime < {1} then
                                                      a.inoutmoney
                                                     else
                                                      0
                                                   end) inoutmoney,
                                                    sum(case
                                                     when a.inouttime >= {1} and   a.inouttime < {2} then
                                                      a.inoutmoney
                                                     else
                                                      0
                                                   end) curmoney
                                         from sel_fund_cust_account b
                                      left join sel_fund_cust_fundinout a on b.balancesubject =
                                         a.balancesubject
                                     and b.buyercode = a.buyercode
                                     where 1 = 1
                                     group by a.balancesubject, a.buyercode,b.balancesubject,
                                           b.buyercode, a.secondaccount
                                     ),
                                         b as
                                         (select sum(case
                                                     when a.invoicedate < to_date('{0}', 'yyyymm') then
                                                      a.weight
                                                     else
                                                      0
                                                   end) tolweight,
                                               sum(case
                                                     when a.invoicedate < to_date('{0}', 'yyyymm') then
                                                      a.money
                                                     else
                                                      0
                                                   end) tolmoney,
                                               sum(case
                                                     when a.invoicedate >= to_date('{0}', 'yyyymm') and
                                                          a.invoicedate < last_day(to_date('{0}', 'yyyymm')) + 1 then
                                                      a.weight
                                                     else
                                                      0
                                                   end) curtolweight,
                                               sum(case
                                                     when a.invoicedate >= to_date('{0}', 'yyyymm') and
                                                          a.invoicedate < last_day(to_date('{0}', 'yyyymm')) + 1 then
                                                      a.money
                                                     else
                                                      0
                                                   end) curtolmoney,
                                               a.ord_no,
                                               a.sale_area_desc,
                                               a.forecorpcode,
                                               a.buyercode
                                          from slm_balanceinfo a
                                         where a.balancestatus = '3'
                                         group by a.ord_no, a.sale_area_desc, a.forecorpcode, a.buyercode)
                                         select  t.balancesubject,t.buyercode,t.secondaccount,nvl(t.inoutmoney,0) - nvl(b.tolmoney,0) premoney,nvl(t.curmoney,0)curmoney,
                                         nvl(b.curtolmoney,0) cur_receive_money ,
                                         nvl(t.inoutmoney,0) - nvl(b.tolmoney,0) + nvl(t.curmoney,0) - nvl(b.curtolmoney,0) de_money
                                         from t 
                                         left join b
                                         on t.balancesubject = b.forecorpcode
                                         and t.buyercode = b.buyercode  and t.secondaccount = b.sale_area_desc ";
            sql = string.Format(sql, this.month, string.Format(" to_date('{0}','yyyymm')", this.month), string.Format("  last_day(to_date('{0}','yyyymm')) + 1", this.month));
            DataSet ds = base.ExecuteReader(sql);
            return ds;
        }







        private System.Collections.Hashtable GetColumnCaption()
        {
            System.Collections.Hashtable hs = new System.Collections.Hashtable();
            hs.Add("BUYERCODE","结算单位");
            hs.Add("PREMONEY", "上月余款");
            hs.Add("CURMONEY", "本月回款");
            hs.Add("CUR_RECEIVE_MONEY", "本月应收");
            hs.Add("DE_MONEY", "差额");
            hs.Add("SALE_AREA_DESC", "销售区域");
            hs.Add("MONEY", "金额");
            hs.Add("PACTNO", "合同号");
            hs.Add("WEIGHT", "重量");
            hs.Add("SECONDACCOUNT", "二级帐户");
            return hs;
        }

        protected override List<string> GetNumberColumn()
        {
            return new List<string>(new string[]{  
                "CURMONEY",
                "PREMONEY",
                "CUR_RECEIVE_MONEY",
              "DE_MONEY",
                "WEIGHT",
                "MONEY"
            
            });
        }

        protected override List<string> GetNumberTypeColulmn()
        {
            return new List<string>(new string[]{  
                "CURMONEY",
                "PREMONEY",
                "CUR_RECEIVE_MONEY",
              "DE_MONEY",
                "WEIGHT",
                "MONEY"
            
            });
        }


        protected override System.Collections.Hashtable GetGridColumnLength()
        {
            System.Collections.Hashtable hs = new System.Collections.Hashtable();
            hs.Add("BUYERCODE", 180);
            hs.Add("PREMONEY", 80);
            hs.Add("CURMONEY", 80);
            hs.Add("CUR_RECEIVE_MONEY", 80);
            hs.Add("DE_MONEY", 90);
            hs.Add("SALE_AREA_DESC", 80);
            hs.Add("MONEY", 80);
            hs.Add("PACTNO", 100);
            hs.Add("WEIGHT", 80);
            return hs;
        }




 


        private string[] GetDataOneStruct()
        {
            return new string[] 
            {
                "BALANCESUBJECT",
                "BUYERCODE",
                "SECONDACCOUNT",
                "PREMONEY",
                 "CURMONEY",
                 "CUR_RECEIVE_MONEY",
                 "DE_MONEY"
            };
        }

        private string[] GetDataTwoStruct()
        {
            return new string[] 
            {
                "BALANCESUBJECT",
                "BUYERCODE",
                "SALE_AREA_DESC",
                 "MONEY"
            };
        }

        private string[] GetDataThreeStruct()
        {
            return new string[] 
            {
                "BALANCESUBJECT",
                "BUYERCODE",
                "SALE_AREA_DESC",
                "PACTNO",
                "WEIGHT",
                "MONEY"
            };
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void ultraGrid1_AfterRowActivate(object sender, EventArgs e)
        {
            this.QueryThreeF();
        }

        private void ultraGrid2_AfterRowActivate(object sender, EventArgs e)
        {
            this.QueryThree();
        }
    }
}