using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Threading; using System.Collections; using System.Reflection; using CoreFS.CA06; using Infragistics.Win.UltraWinGrid; namespace Core.StlMes.Client.Sale.UI.Balance.Report { public partial class FrmInvoiceAccountMgt : Core.StlMes.Client.Sale.Common.FrmStyleBase.FrmUIBase { public FrmInvoiceAccountMgt() { InitializeComponent(); } private DataSet _dsGroup = new DataSet(); private DataSet _dsGm = new DataSet(); private DataSet _dsProc = new DataSet(); private DataSet _dsProcFist = new DataSet(); private DataSet _dsSale = new DataSet(); private DataSet _dsOutSale = new DataSet(); private void InitDataSource() { _dsGroup = base.GetDataSetByColumnName(this.GetBalanceMainColumn() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); _dsGm = base.GetDataSetByColumnName(this.GetBalanceMainColumn() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); _dsProc = base.GetDataSetByColumnName(this.GetBalanceMainColumn() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); _dsProcFist = base.GetDataSetByColumnName(this.GetBalanceMainColumnOrg() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); _dsSale = base.GetDataSetByColumnName(this.GetBalanceMainColumn() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); _dsOutSale = base.GetDataSetByColumnName(this.GetBalanceMainColumnOrg() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); this.ultraGrid1.DataSource = _dsGroup; this.ultraGrid2.DataSource = _dsGm; this.ultraGrid3.DataSource = _dsProc; this.ultraGrid4.DataSource = _dsProcFist; this.ultraGrid5.DataSource = _dsSale; this.ultraGrid6.DataSource = _dsOutSale; } private Infragistics.Win.UltraWinGrid.UltraGrid[] GetAllUltraGrid() { return new Infragistics.Win.UltraWinGrid.UltraGrid[] {this.ultraGrid1,this.ultraGrid2,this.ultraGrid3,this.ultraGrid4,this.ultraGrid5,this.ultraGrid6}; } private string INVOICE_MONTH = "INVOICE_MONTH"; private string FST_DRAWEE_UNIT = "FST_DRAWEE_UNIT"; private string CONTAIN_TAX = "CONTAIN_TAX"; private string ASKPLAN_ID = "ASKPLAN_ID"; private void InitGridValue() { foreach (Infragistics.Win.UltraWinGrid.UltraGrid ugr in GetAllUltraGrid()) { try { ugr.DisplayLayout.Bands[0].Columns[this.FST_DRAWEE_UNIT].ValueList = GetFst_Drawee_unit(); } catch { } try { ugr.DisplayLayout.Bands[0].Columns[this.CONTAIN_TAX].ValueList = GetAgenCyFlList(); } catch { } try { ugr.DisplayLayout.Bands[0].Columns[this.INVOICE_MONTH].ValueList = this.GetMonthList(); } catch { } } } private void InitEditCell() { foreach (Infragistics.Win.UltraWinGrid.UltraGrid ugr in GetAllUltraGrid()) { try { ugr.DisplayLayout.Bands[0].Columns[this.INVOICE_MONTH].CellActivation = Activation.AllowEdit; ugr.DisplayLayout.Bands[0].Columns[this.INVOICE_MONTH].Style = Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList; ugr.DisplayLayout.Bands[0].Columns[this.INVOICE_MONTH].CellAppearance.BackColor = base.GetEditColor(); } catch { } } } private Infragistics.Win.ValueList GetMonthList() { Infragistics.Win.ValueList lit = new Infragistics.Win.ValueList(); string mon = ""; string val = ""; for (int i = 0; i < 12; i++) { mon = System.DateTime.Now.AddMonths(-i).ToString("yyyyMM"); lit.ValueListItems.Add(mon, mon); } return lit; } private Infragistics.Win.ValueList GetAgenCyFlList() { Infragistics.Win.ValueList lit = new Infragistics.Win.ValueList(); lit.ValueListItems.Add("0", "否"); lit.ValueListItems.Add("1", "是"); return lit; } private Infragistics.Win.ValueList GetFst_Drawee_unit() { Infragistics.Win.ValueList lit = new Infragistics.Win.ValueList(); lit.ValueListItems.Add("120601", "N/A"); lit.ValueListItems.Add("120602", "/"); lit.ValueListItems.Add("120603", "天津钢管集团股份有限公司"); lit.ValueListItems.Add("120604", "天津钢管钢铁贸易有限公司"); lit.ValueListItems.Add("120605", "天津钢管国际经济贸易有限公司"); lit.ValueListItems.Add("120606", "合同结算单位"); lit.ValueListItems.Add("120607", "江苏天淮钢管有限公司"); lit.ValueListItems.Add("120608", "天津钢管制造有限公司"); return lit; } private void FrmInvoiceAccountMgt_Load(object sender, EventArgs e) { this.dateTimePicker2.Format = DateTimePickerFormat.Custom; this.dateTimePicker2.CustomFormat = "yyyyMM"; InitDataSource(); InitGridValue(); base.SetGridStyle(ref this.ultraGrid1, ref this.ultraGrid2, ref this.ultraGrid3,ref this.ultraGrid4, ref this.ultraGrid5, ref this.ultraGrid6); InitEditCell(); InitGmFrm(); } private void InitGmFrm() { if (this.CustomInfo == "4") { for( int i = 0; i < this.ultraTabControl2.Tabs.Count; i ++) { this.ultraTabControl2.Tabs[i].Visible = false; this.ultraTabControl2.Tabs[1].Visible = true; } } } public override void Query() { if (this.ultraTabControl2.SelectedTab.Index == 1) { this.QueryGm(); } else if (this.ultraTabControl2.SelectedTab.Index == 0) { this.QueryGroup(); } else if (this.ultraTabControl2.SelectedTab.Index == 2) { this.QueryProc(); } else if (this.ultraTabControl2.SelectedTab.Index == 3) { this.QueryProcIn(); } else if (this.ultraTabControl2.SelectedTab.Index == 4) { this.QuerySale(); } else if (this.ultraTabControl2.SelectedTab.Index == 5) { this.QueryOutSale(); } } private List GetUgrs() { List ugrs = new List(); if (this.ultraTabControl2.SelectedTab.Index == 1) { if (this.ultraGrid2.Rows.Count == 0) return ugrs; this.ultraGrid2.UpdateData(); if (this.ultraGrid2.ActiveRow != null) this.ultraGrid2.ActiveRow.Selected = true; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid2.Selected.Rows) { ugrs.Add(ugr); } } else if (this.ultraTabControl2.SelectedTab.Index == 0) { if (this.ultraGrid1.Rows.Count == 0) return ugrs; this.ultraGrid1.UpdateData(); if (this.ultraGrid1.ActiveRow != null) this.ultraGrid1.ActiveRow.Selected = true; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid1.Selected.Rows) { ugrs.Add(ugr); } } else if (this.ultraTabControl2.SelectedTab.Index == 2) { if (this.ultraGrid3.Rows.Count == 0) return ugrs; this.ultraGrid3.UpdateData(); if (this.ultraGrid3.ActiveRow != null) this.ultraGrid3.ActiveRow.Selected = true; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid3.Selected.Rows) { ugrs.Add(ugr); } } else if (this.ultraTabControl2.SelectedTab.Index == 3) { if (this.ultraGrid4.Rows.Count == 0) return ugrs; this.ultraGrid4.UpdateData(); if (this.ultraGrid4.ActiveRow != null) this.ultraGrid4.ActiveRow.Selected = true; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid4.Selected.Rows) { ugrs.Add(ugr); } } else if (this.ultraTabControl2.SelectedTab.Index == 4) { if (this.ultraGrid5.Rows.Count == 0) return ugrs; this.ultraGrid5.UpdateData(); if (this.ultraGrid5.ActiveRow != null) this.ultraGrid5.ActiveRow.Selected = true; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid5.Selected.Rows) { ugrs.Add(ugr); } } else if (this.ultraTabControl2.SelectedTab.Index == 5) { if (this.ultraGrid6.Rows.Count == 0) return ugrs; this.ultraGrid6.UpdateData(); if (this.ultraGrid6.ActiveRow != null) this.ultraGrid6.ActiveRow.Selected = true; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid6.Selected.Rows) { ugrs.Add(ugr); } } return ugrs; } public override void Update() { UpdateInvoiceMonth(); } private void UpdateInvoiceMonth() { List list = this.GetUgrs(); if (list.Count == 0) return; string sql = " update Slm_Balanceinfo a set a.invoice_month = '{0}' where askplan_id = '{1}' and invoice_sts = '0' "; string sqlt = " update SLM_BALANCE_TRANSMONEY a set a.invoice_month = '{0}' where askplan_id = '{1}' and invoice_sts = '0' "; ArrayList lists = new ArrayList(); foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in list) { sql = string.Format(sql,ugr.Cells[this.INVOICE_MONTH].Value.ToString(),ugr.Cells[this.ASKPLAN_ID].Value.ToString()); sqlt = string.Format(sqlt, ugr.Cells[this.INVOICE_MONTH].Value.ToString(), ugr.Cells[this.ASKPLAN_ID].Value.ToString()); if (!(ugr.Cells["MONEY_TYPE"].Value.ToString() == "货款")) { lists.Add(sqlt); } else lists.Add(sql); } base.ExecuteNoReader(lists); Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("操作成功"); foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in list) { ugr.Delete(); } } private void QueryGroup() { string sqlcondition = ""; sqlcondition += " and fst_drawee_unit = '120603' "; sqlcondition += " and balancestatus = '3' and realinvoiceno is not null "; string month = this.dateTimePicker2.Value.ToString("yyyyMM"); sqlcondition += string.Format(" and invoice_month = '{0}' ", month); string sql = GetSqlMain(sqlcondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref _dsGroup,ds); string sqlTransConditjon = " and fst_drawee_unit = '120603' "; sqlTransConditjon += " and realinvoiceno is not null "; sqlTransConditjon += string.Format(" and invoice_month = '{0}' ", month); string sqltran = GetBalanceTransMoney(sqlTransConditjon); DataSet dsTrans = base.ExecuteReader(sqltran); try { dsTrans.Tables[0].TableName = this._dsGroup.Tables[0].TableName; this._dsGroup.Merge(dsTrans, false, System.Data.MissingSchemaAction.Ignore); } catch { } } private void QueryGm() { string sqlcondition = ""; sqlcondition += " and forecorpcode = '钢贸公司' "; sqlcondition += " and balancestatus = '3' and realinvoiceno is not null "; string month = this.dateTimePicker2.Value.ToString("yyyyMM"); sqlcondition += string.Format(" and invoice_month = '{0}' ", month); string sql = GetSqlMain(sqlcondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref _dsGm, ds); string sqlTransConditjon = " and forecorpname = '钢贸公司' "; sqlTransConditjon += " and realinvoiceno is not null "; sqlTransConditjon += string.Format(" and invoice_month = '{0}' ", month); string sqltran = GetBalanceTransMoney(sqlTransConditjon); DataSet dsTrans = base.ExecuteReader(sqltran); try { dsTrans.Tables[0].TableName = this._dsGm.Tables[0].TableName; this._dsGm.Merge(dsTrans, false, System.Data.MissingSchemaAction.Ignore); } catch { } } private void QueryProc() { string sqlcondition = ""; sqlcondition += " and fst_drawee_unit = '120606' "; sqlcondition += " and balancestatus = '3' and realinvoiceno is not null "; string month = this.dateTimePicker2.Value.ToString("yyyyMM"); sqlcondition += string.Format(" and invoice_month = '{0}' ", month); string sql = GetSqlMain(sqlcondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref _dsProc, ds); string sqlTransConditjon = " and fst_drawee_unit = '120606' "; sqlTransConditjon += " and realinvoiceno is not null "; sqlTransConditjon += string.Format(" and invoice_month = '{0}' ", month); string sqltran = GetBalanceTransMoney(sqlTransConditjon); DataSet dsTrans = base.ExecuteReader(sqltran); try { dsTrans.Tables[0].TableName = this._dsProc.Tables[0].TableName; this._dsProc.Merge(dsTrans, false, System.Data.MissingSchemaAction.Ignore); } catch { } } private void QueryProcIn() { string sqlcondition = ""; sqlcondition += " and balancestatus_org = '3' and REALINVOICENO_ORG is not null "; string month = this.dateTimePicker2.Value.ToString("yyyyMM"); sqlcondition += string.Format(" and invoice_month = '{0}' ", month); string sql = GetSqlMain(sqlcondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref _dsProcFist, ds); string sqlTransConditjon = " "; sqlTransConditjon += " and realinvoiceno_org is not null "; sqlTransConditjon += string.Format(" and invoice_month = '{0}' ", month); string sqltran = GetBalanceTransMoney(sqlTransConditjon); DataSet dsTrans = base.ExecuteReader(sqltran); try { dsTrans.Tables[0].TableName = this._dsProcFist.Tables[0].TableName; this._dsProcFist.Merge(dsTrans, false, System.Data.MissingSchemaAction.Ignore); } catch { } } private void QuerySale() { string sqlcondition = ""; sqlcondition += " and balancestatus = '3' and realinvoiceno is not null and forecorpcode = '销售公司' "; string month = this.dateTimePicker2.Value.ToString("yyyyMM"); sqlcondition += string.Format(" and invoice_month = '{0}' ", month); string sql = GetSqlMain(sqlcondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref _dsSale, ds); string sqlTransConditjon = " and forecorpname = '销售公司' "; sqlTransConditjon += " and realinvoiceno is not null "; sqlTransConditjon += string.Format(" and invoice_month = '{0}' ", month); string sqltran = GetBalanceTransMoney(sqlTransConditjon); DataSet dsTrans = base.ExecuteReader(sqltran); try { dsTrans.Tables[0].TableName = this._dsSale.Tables[0].TableName; this._dsSale.Merge(dsTrans, false, System.Data.MissingSchemaAction.Ignore); } catch { } } private void QueryOutSale() { string sqlcondition = ""; sqlcondition += " and balancestatus_org = '3' and REALINVOICENO_ORG is not null and forecorpcode = '国贸公司' "; string month = this.dateTimePicker2.Value.ToString("yyyyMM"); sqlcondition += string.Format(" and invoice_month = '{0}' ", month); string sql = GetSqlMain(sqlcondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref _dsOutSale, ds); string sqlTransConditjon = " and forecorpname = '国贸公司' "; sqlTransConditjon += " and realinvoiceno_org is not null "; sqlTransConditjon += string.Format(" and invoice_month = '{0}' ", month); string sqltran = GetBalanceTransMoney(sqlTransConditjon); DataSet dsTrans = base.ExecuteReader(sqltran); try { dsTrans.Tables[0].TableName = this._dsOutSale.Tables[0].TableName; this._dsOutSale.Merge(dsTrans, false, System.Data.MissingSchemaAction.Ignore); } catch { } } private string GetSqlMain(string sqlCondition) { string sql = @" select a.*, b.batchno, b.pno, b.wgt ACT_WEIGHT, b.qty ACT_COUNT, send_num1 INOUTMONEY, round(a.price * nvl(b.wgt, b.send_num1), 5) TOTALMONEY, round(a.price_org * nvl(b.wgt, b.send_num1), 5) LEAVEMONEY,'货款' MONEY_TYPE from Slm_Balanceinfo a join (select * from (select row_number() over(partition by a.ord_no, a.ord_seq, a.ord_ln_dly, a.askplan_id, a.askplan_id_seq, a.steelcodekey, a.spetkey, a.batch_no order by a.ord_no, a.ord_seq, a.ord_ln_dly, a.askplan_id, a.askplan_id_seq, a.steelcodekey, a.spetkey, a.batch_no) rn, sum(a.act_wgt) over(partition by a.ord_no, a.ord_seq, a.ord_ln_dly, a.askplan_id, a.askplan_id_seq, a.steelcodekey, a.spetkey, a.batch_no) wgt, sum(a.PRD_QTY) over(partition by a.ord_no, a.ord_seq, a.ord_ln_dly, a.askplan_id, a.askplan_id_seq, a.steelcodekey, a.spetkey, a.batch_no) qty, sum(a.send_num) over(partition by a.ord_no, a.ord_seq, a.ord_ln_dly, a.askplan_id, a.askplan_id_seq, a.steelcodekey, a.spetkey, a.batch_no) send_num1, a.ord_no, a.ord_seq, a.ord_ln_dly, a.askplan_id, a.askplan_id_seq, a.steelcodekey, a.spetkey, substr(a.batch_no, 1, instr(batch_no, '/', 1) - 1) batchno, substr(a.batch_no, instr(batch_no, '/', 1) + 1) pno from Slm_Balancedetail_All a) where rn = 1) b on a.ord_no = b.ord_no and a.ord_seq = b.ord_seq and a.ord_ln_dly = b.ord_ln_dly and a.askplan_id = b.askplan_id and a.askplan_id_seq = b.askplan_id_seq and a.steelcodekey = b.steelcodekey and a.spetkey = b.spetkey where 1 = 1 {0} "; sql = string.Format(sql, sqlCondition); return sql; } private string GetBalanceTransMoney(string sqlCondition) { string sql = @" select a.askplan_id, a.listnumber, a.buyercode, a.buyername, min(INVOICE_MONTH) INVOICE_MONTH, min(realinvoiceno) realinvoiceno, min(INVOICE_ID) INVOICEPERSON, min(a.order_no) ORD_NO, min(a.order_seq) ORD_SEQ, a.forecorpname forecorpcode, a.fst_drawee_unit, a.inceptcorpcode, a.trans_type TRANS_TYPE_NAME, a.tickettype, a.MONEY_TYPE, sum(a.money) TOTALMONEY, sum(a.weight) ACT_WEIGHT, min(a.reg_id) reg_id, min(a.reg_dtime) reg_dtime, min(b.sale_area_desc) sale_area_desc, min(b.create_dept) create_dept, min(b.create_name) create_name,min(INCEPTCORPCODE) INCEPTCORPCODE, min(b.customer_nm) CUSTOMER_NM,min(a.REALINVOICE_DATE) REALINVOICENO_DATE from Slm_Balance_Transmoney a join (select min(b.create_dept) create_dept, min(b.create_name) create_name,min(b.customer_nm) customer_nm, min(b.sale_area_desc) sale_area_desc, bill_no from ydm_zc_bill_m m join slm_order_head b on m.ord_pk = b.ord_pk group by bill_no) b on a.askplan_id = b.bill_no where 1=1 {0} and a.tickettype = '应收' group by a.askplan_id, a.listnumber, a.buyercode, a.buyername, a.ord_pk, a.ord_ln_pk, a.ord_ln_dly_pk, a.forecorpcode, a.forecorpname, a.fst_drawee_unit, a.inceptcorpcode, a.trans_type, a.tickettype, a.money_type "; sql = string.Format(sql, sqlCondition); return sql; } protected override Hashtable GetDataSetColumnCaption() { Hashtable hs = new Hashtable(); hs.Add("INOUTMONEY", "实发数"); hs.Add("LEAVEMONEY", "金额"); return hs; } private string[] GetBalanceMainColumn() { return new string[] { "INVOICE_MONTH", "MONEY_TYPE", "SALE_AREA_DESC", "FORECORPCODE", "BUYERNAME", "FST_DRAWEE_UNIT", "ASKPLAN_ID", "ORD_NO", "ORD_SEQ", "ORD_LN_DLY", "BATCHNO", "PNO", "STEELCODEKEY", "PACTINDEX", "SPETKEY", "INOUTMONEY", "ORDER_UNIT", "ACT_COUNT", "ACT_WEIGHT", "PRICE", "TOTALMONEY", "INVOICEPERSON", "REALINVOICENO", "REALINVOICENO_DATE", "CONTAIN_TAX", "CATEGORY", "SPEC_ABBSYMNAME", "STD_STYLE_DESC", "MODEL_DESC", "INCEPTCORPCODE", "CUSTOMER_NM" }; } private string[] GetBalanceMainColumnOrg() { return new string[] { "SALE_AREA_DESC", "FORECORPCODE", "BUYERNAME", "FST_DRAWEE_UNIT", "ASKPLAN_ID", "ORD_NO", "ORD_SEQ", "ORD_LN_DLY", "BATCHNO", "PNO", "STEELCODEKEY", "PACTINDEX", "SPETKEY", "INOUTMONEY", "ORDER_UNIT", "ACT_COUNT", "ACT_WEIGHT", "REALINVOICENO_DATE_ORG", "PRICE_ORG", "LEAVEMONEY", "REALINVOICENO_ORG", "CONTAIN_TAX", "CATEGORY", "SPEC_ABBSYMNAME", "STD_STYLE_DESC", "MODEL_DESC", "INCEPTCORPCODE", "CUSTOMER_NM" }; } } }