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 GetNumberColumn() { return new List(new string[]{ "CURMONEY", "PREMONEY", "CUR_RECEIVE_MONEY", "DE_MONEY", "WEIGHT", "MONEY" }); } protected override List GetNumberTypeColulmn() { return new List(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(); } } }