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; using System.IO; using System.Collections; using CoreFS.CA06; using Core.Mes.Client.Comm.Tool; namespace Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIFrm { public partial class FrmReceiveAndDebtMgtNew : FrmFundBase { public FrmReceiveAndDebtMgtNew() { InitializeComponent(); } private DataSet ds_Group = new DataSet(); private DataSet ds_Gm = new DataSet(); private DataSet ds_Proce = new DataSet(); private DataSet ds_Adjust = new DataSet(); private DataSet ds_P_Gm = new DataSet(); private DataSet ds_P_Group = new DataSet(); private DataSet ds_P_Ggm= new DataSet(); FundBLL.ValueList.PubValueList PubValueList = null; Infragistics.Win.ValueList listBuyercode = null; private Infragistics.Win.ValueList GetStsList() { Infragistics.Win.ValueList lit = new Infragistics.Win.ValueList(); lit.ValueListItems.Add("0", "未结转"); lit.ValueListItems.Add("1", "已经结转"); return lit; } 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 void InitDs() { ds_Group = base.GetDataSetByColumnName(this.GetDataStruct() ,null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); ds_Gm = base.GetDataSetByColumnName(this.GetDataStruct() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); ds_Adjust = base.GetDataSetByColumnName(this.GetDataStructAdj() , null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); ChangeAdjDsCaption(); this.ultraGrid1.DataSource = ds_Group; this.ultraGrid2.DataSource = ds_Gm; this.ultraGrid5.DataSource = ds_Adjust; } private void ChangeAdjDsCaption() { try { ds_Adjust.Tables[0].Columns[PRE_MONEY].Caption = "金额(调整前)"; ds_Adjust.Tables[0].Columns["MONEY"].Caption = "金额(调整后)"; ds_Adjust.Tables[0].Columns["INOUTTIME"].Caption = "时间"; } catch { } } private string[] HiddenColumns() { return new string[] { this.INVOICE_MONTH, this.INVOICE_ACCOUNT, "MONEY" }; } private UltraGrid[] GetAllUltraGrid() { return new UltraGrid[] { this.ultraGrid1, this.ultraGrid2}; } private void InitGrid() { foreach (UltraGrid ugs in GetAllUltraGrid()) { try { ugs.DisplayLayout.Bands[0].Columns[this.PRE_MONEY].CellActivation = Activation.AllowEdit; ugs.DisplayLayout.Bands[0].Columns[this.PRE_MONEY].CellAppearance.BackColor = base.GetEditColor(); ugs.DisplayLayout.Bands[0].Columns[this.PRE_MONEY].CellAppearance.TextHAlign = Infragistics.Win.HAlign.Right; ugs.DisplayLayout.Bands[0].Columns[this.INMONEY].CellAppearance.BackColor = base.GetEditColor(); ugs.DisplayLayout.Bands[0].Columns[this.INVOICE_MONEY].CellAppearance.BackColor = base.GetEditColor(); ugs.DisplayLayout.Bands[0].Columns[this.INVOICE_STS].ValueList = GetStsList(); } catch { } } } private void InitGridValue() { foreach (UltraGrid ugs in GetAllUltraGrid()) { try { ugs.DisplayLayout.Bands[0].Columns[this.BUYERCODE].ValueList = PubValueList.GetCustomerInfo(); } catch { } } try { this.ultraGrid5.DisplayLayout.Bands[0].Columns[this.BUYERCODE].ValueList = PubValueList.GetCustomerInfo(); } catch { } } private void InitAllAccoutBook() { InitAllBool( this.dateTimePicker2.Value.ToString("yyyyMM")); } private void InitAllBool(string month) { string sql = @" declare v_invoimon varchar2(8) := '{0}'; v_int integer := 0; begin for r_info in (select distinct a.sale_area_desc, a.stlmt_unit_no buyercode, a.sale_org_desc forecorpcode, fst_drawee_unit from slm_order_head a where a.sale_area_desc is not null and a.stlmt_unit_no is not null and fst_drawee_unit is not null) loop select count(1) into v_int from sel_fund_receive_account a where a.INVOICE_MONTH = v_invoimon and invoice_account = '国内贸易部' and balancesubject = r_info.forecorpcode and secondaccount = r_info.sale_area_desc and buyercode = r_info.buyercode; if (v_int = 0 and r_info.forecorpcode = '国内贸易部') then insert into sel_fund_receive_account (INVOICE_MONTH, invoice_account, balancesubject, secondaccount, buyercode) select v_invoimon, '国内贸易部', r_info.forecorpcode, r_info.sale_area_desc, r_info.buyercode from dual; end if; select count(1) into v_int from sel_fund_receive_account a where a.INVOICE_MONTH = v_invoimon and invoice_account = '国际贸易部' and balancesubject = r_info.forecorpcode and secondaccount = r_info.sale_area_desc and buyercode = r_info.buyercode; if (v_int = 0 and r_info.forecorpcode = '国际贸易部') then insert into sel_fund_receive_account (INVOICE_MONTH, invoice_account, balancesubject, secondaccount, buyercode) select v_invoimon, '国际贸易部', r_info.forecorpcode, r_info.sale_area_desc, r_info.buyercode from dual; end if; end loop; commit; end; "; sql = string.Format(sql,month); base.ExecuteNoReader(sql); } private void ChgGroupNextInvoiceAccount() { } private DataSet GetGmMoneyInOut(string balancesubject,string buyercode,string areacode,string inmonth) { DataSet dsDetail = base.GetDataSetByColumnName(GetTbColumn_SEL_FUND_CUST_FUNDINOUT(), new System.Collections.Hashtable(), base.GetFundColumnNameAndCaption()); string sqlConditon = string.Format(" and balancesubject = '{0}' ",balancesubject); sqlConditon += string.Format(" and secondaccount = '{0}' and money_deliver = '0' ", areacode); sqlConditon += string.Format(" and buyercode = '{0}' ", buyercode); sqlConditon += string.Format(" and inouttime > to_date('{0}', 'yyyymm')", inmonth); sqlConditon += string.Format(" and inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1", inmonth); DataSet ds = GetMoneyInout(sqlConditon); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsDetail, ds); return dsDetail; } private DataSet GetGroupMoneyInOut(string balancesubject, string buyercode, string areacode, string inmonth) { DataSet dsDetail = base.GetDataSetByColumnName(GetTbColumn_SEL_FUND_CUST_FUNDINOUT(), new System.Collections.Hashtable(), base.GetFundColumnNameAndCaption()); string sqlConditon = string.Format(" and balancesubject = '{0}' ", balancesubject); sqlConditon += string.Format(" and secondaccount = '{0}' ", areacode); sqlConditon += string.Format(" and buyercode = '{0}' ", buyercode); sqlConditon += string.Format(" and inouttime > to_date('{0}', 'yyyymm')", inmonth); sqlConditon += string.Format(" and inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1", inmonth); sqlConditon += " and money_deliver = '2' "; DataSet ds = GetMoneyInout(sqlConditon); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsDetail, ds); return dsDetail; } private DataSet GetProcMoneyInOut(string balancesubject, string buyercode, string areacode, string inmonth) { DataSet dsDetail = base.GetDataSetByColumnName(GetTbColumn_SEL_FUND_CUST_FUNDINOUT(), new System.Collections.Hashtable(), base.GetFundColumnNameAndCaption()); string sqlConditon = string.Format(" and balancesubject = '{0}' ", balancesubject); sqlConditon += string.Format(" and secondaccount = '{0}' ", areacode); sqlConditon += string.Format(" and buyercode = '{0}' ", buyercode); sqlConditon += string.Format(" and inouttime > to_date('{0}', 'yyyymm')", inmonth); sqlConditon += string.Format(" and inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1", inmonth); sqlConditon += " and money_deliver = '1' "; DataSet ds = GetMoneyInout(sqlConditon); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsDetail, ds); return dsDetail; } private DataSet GetProcAllMoneyInOut(string balancesubject, string inmonth) { DataSet dsDetail = base.GetDataSetByColumnName(GetTbColumn_SEL_FUND_CUST_FUNDINOUT(), new System.Collections.Hashtable(), base.GetFundColumnNameAndCaption()); string sqlConditon = ""; if (balancesubject == "钢贸") { sqlConditon = " and balancesubject = '钢贸公司' and money_deliver = '0' "; } else if (balancesubject == "国贸") { sqlConditon = " and balancesubject = '国际贸易部' and money_deliver = '0' "; } else if (balancesubject == "制造") { sqlConditon = " and balancesubject = '国内贸易部' and money_deliver = '1' "; } else if (balancesubject == "集团") { sqlConditon = " and balancesubject = '国内贸易部' and money_deliver = '2' "; } sqlConditon += string.Format(" and inouttime > to_date('{0}', 'yyyymm')", inmonth); sqlConditon += string.Format(" and inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1", inmonth); DataSet ds = GetMoneyInout(sqlConditon); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref dsDetail, ds); return dsDetail; } private DataSet GetGmInvoicePact(string month,string buyercode,string forecode,string salearea) { DataSet ds = base.GetDataSetByColumnName(this.GetInvoiceStruct(), null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); DataSet dsData = GetGmInvoicePactByInvoiceMonthAndBuyercodeAndSaleAreaAndForeCode (month,buyercode,forecode,salearea); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref ds, dsData); return ds; } private DataSet GetGroupInvoicePact(string month, string buyercode, string forecode, string salearea) { DataSet ds = base.GetDataSetByColumnName(this.GetInvoiceStruct(), null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); DataSet dsData = GetGroupInvoicePactByInvoiceMonthAndBuyercodeAndSaleAreaAndForeCode (month, buyercode, forecode, salearea); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref ds, dsData); return ds; } private DataSet GetProcInvoicePact(string month, string buyercode, string forecode, string salearea) { DataSet ds = base.GetDataSetByColumnName(this.GetInvoiceStruct(), null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); DataSet dsData = GetProcInvoicePactByInvoiceMonthAndBuyercodeAndSaleAreaAndForeCode (month, buyercode, forecode, salearea); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref ds, dsData); return ds; } private DataSet GetProcAllInvoicePact(string balancesubject, string inmonth) { DataSet ds = base.GetDataSetByColumnName(this.GetInvoiceStruct(), null, base.ConvertListStringArrayToHashTable(base.GetDataStructCaption())); string sql = GetQueryOrgInvoicePact(); string sqlConditon = ""; if (balancesubject == "钢贸") { sqlConditon = " and fst_drawee_unit = '120604' "; } else if (balancesubject == "国贸") { sqlConditon = " and fst_drawee_unit = '120605' "; } else if (balancesubject == "制造") { sqlConditon = " and fst_drawee_unit = '120606' "; } else if (balancesubject == "集团") { sqlConditon = " and fst_drawee_unit = '120603' "; } sqlConditon += string.Format(" and realinvoiceno_date_org <= last_day(to_date('{0}', 'yyyymm')) + 1", inmonth); sqlConditon += string.Format(" and realinvoiceno_date_org > to_date('{0}', 'yyyymm')", inmonth); sql = string.Format(sql, sqlConditon); DataSet dsData = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref ds, dsData); return ds; } private DataSet GetGmInvoicePactByInvoiceMonthAndBuyercodeAndSaleAreaAndForeCode (string month,string buyercode,string forcode,string salearea) { string sql = GetQueryInvoicePact(); string sqlCondition = ""; sqlCondition += string.Format(" and invoice_month = '{0}' ", month); sqlCondition += string.Format(" and buyercode = '{0}' ", buyercode); sqlCondition += string.Format(" and forecorpcode = '{0}' ", forcode); sqlCondition += string.Format(" and sale_area_desc = '{0}' ", salearea); sql = string.Format(sql,sqlCondition); DataSet ds = base.ExecuteReader(sql); return ds; } private DataSet GetGroupInvoicePactByInvoiceMonthAndBuyercodeAndSaleAreaAndForeCode (string month, string buyercode, string forcode, string salearea) { string sql = GetQueryInvoicePact(); string sqlCondition = ""; sqlCondition += string.Format(" and invoice_month = '{0}' ", month); sqlCondition += string.Format(" and buyercode = '{0}' ", buyercode); sqlCondition += string.Format(" and forecorpcode = '{0}' ", forcode); sqlCondition += string.Format(" and sale_area_desc = '{0}' ", salearea); sqlCondition += string.Format(" and fst_drawee_unit = '{0}' ", "120603"); sql = string.Format(sql, sqlCondition); DataSet ds = base.ExecuteReader(sql); return ds; } private DataSet GetProcInvoicePactByInvoiceMonthAndBuyercodeAndSaleAreaAndForeCode (string month, string buyercode, string forcode, string salearea) { string sql = GetQueryInvoicePact(); string sqlCondition = ""; sqlCondition += string.Format(" and invoice_month = '{0}' ", month); sqlCondition += string.Format(" and buyercode = '{0}' ", buyercode); sqlCondition += string.Format(" and forecorpcode = '{0}' ", forcode); sqlCondition += string.Format(" and sale_area_desc = '{0}' ", salearea); sqlCondition += string.Format(" and fst_drawee_unit = '{0}' ", "120606"); sql = string.Format(sql, sqlCondition); DataSet ds = base.ExecuteReader(sql); return ds; } private string GetQueryInvoicePact() { string sql = @" select a.ord_no pactno, sum(a.weight) weight, sum(a.money) money, min(a.buyername) buyername, min(a.sale_area_desc) sale_area_desc, min(b.basename) fst_drawee_unit from slm_balanceinfo a left join com_base_info b on a.fst_drawee_unit = b.basecode and b.sortcode = '1206' where a.balancestatus = '3' {0} and a.realinvoiceno is not null group by a.ord_no "; return sql; } private string GetQueryOrgInvoicePact() { string sql = @" select a.ord_no pactno, sum(a.weight) weight, sum(a.money_org) money, min(a.buyername) buyername, min(a.sale_area_desc) sale_area_desc, min(b.basename) fst_drawee_unit from slm_balanceinfo a left join com_base_info b on a.fst_drawee_unit = b.basecode and b.sortcode = '1206' where a.balancestatus_org = '3' {0} and a.realinvoiceno_org is not null group by a.ord_no "; return sql; } private DataSet GetMoneyInout(string sqlCondition) { string sql = " select * from sel_fund_cust_fundinout a where 1 = 1 and isvalid = '1' {0}"; sql = string.Format(sql,sqlCondition); return base.ExecuteReader(sql); } private void FrmReceiveAndDebtMgt_Load(object sender, EventArgs e) { this.dateTimePicker2.Format = DateTimePickerFormat.Custom; this.dateTimePicker2.CustomFormat = "yyyyMM"; if (this.dateTimePicker2.Value.Day >= 26) { this.dateTimePicker2.Value = this.dateTimePicker2.Value.AddMonths(1); } PubValueList = new Core.StlMes.Client.Sale.SaleFundMgt.FundBLL.ValueList.PubValueList(this.ob); InitDs(); InitGridValue(); base.SetGridStyle(ref this.ultraGrid1, ref this.ultraGrid2,ref this.ultraGrid5); InitGrid(); InitAllAccoutBook(); InitGmData(); } private void InitGmData() { try { if (this.CustomInfo == "3") { for (int i = 0; i < this.ultraTabControl1.Tabs.Count; i++) { if (i == 1) { this.ultraTabControl1.Tabs[i].Visible = true; this.ultraTabControl1.Tabs[i].Selected = true; } else this.ultraTabControl1.Tabs[i].Visible = false; } } if (this.CustomInfo == "4") { for (int i = 0; i < this.ultraTabControl1.Tabs.Count; i++) { if (i == 1) { this.ultraTabControl1.Tabs[i].Visible = true; } else this.ultraTabControl1.Tabs[i].Visible = false; } this.ultraTabControl1.Tabs[4].Visible = true; } } catch { } } public override void Query() { QueryAll(); } public override void Update() { UpdateAll(); } public override void ImportTemplate() { Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel("导入模板", this.ultraGrid3); } public override void Import() { List lists = null; var openFileDialog = new OpenFileDialog { Filter = "导入模板|*.xls;*.xlsx" }; if (openFileDialog.ShowDialog() == DialogResult.OK) { lists = ReadExcelToPlnReqinfGpExcel(openFileDialog.FileName); } if (lists == null) return; if (lists.Count <= 0) { MessageBox.Show("Excel无数据"); return; } System.Collections.ArrayList sqllist = new System.Collections.ArrayList(); foreach (var list in lists) { string sql = @" UPDATE SEL_FUND_RECEIVE_ACCOUNT SET PRE_MONEY = '{0}' WHERE INVOICE_MONTH = '{1}'AND INVOICE_ACCOUNT = '{2}' AND BALANCESUBJECT = '{3}' AND SECONDACCOUNT = '{4}' AND BUYERCODE ='{5}' "; sql = string.Format(sql, list[6], list[0], list[1], list[2], list[3], list[4]); sqllist.Add(sql); } base.ExecuteNoReader(sqllist); this.QueryAll(); } public List ReadExcelToPlnReqinfGpExcel(string fileName) { //定义要返回的datatable对象 List plnReqinfGpExcels = new List(); try { if (!File.Exists(fileName)) { return null; } //根据指定路径读取文件 using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { //根据文件流创建excel数据结构 NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); //IWorkbook workbook = new HSSFWorkbook(fs); for (int k = 0; k < workbook.NumberOfSheets; k++) { NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(k); if (sheet != null) { //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = 1; i <= rowCount; ++i) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null　　　　 ArrayList arr = new ArrayList(); arr.Add(row.Cells[0]); arr.Add(row.Cells[1]); arr.Add(row.Cells[2]); arr.Add(row.Cells[3]); arr.Add(row.Cells[4]); arr.Add(row.Cells[5]); arr.Add(row.Cells[6]); plnReqinfGpExcels.Add(arr); } } } } return plnReqinfGpExcels; } catch (Exception ex) { MessageBox.Show("EXCEL格式错误:" + ex.Message); return null; } } public override void Export() { switch (ultraTabControl1.ActiveTab.Text) { case "国内贸易部": Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel("账本信息", this.ultraGrid1); break; case "国际贸易部": Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel("账本信息", this.ultraGrid2); break; default: Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel("账本信息", this.ultraGrid5); break; } } private void SelectAllRows( Infragistics.Win.UltraWinGrid.UltraGrid ugr ) { if (ugr == null || ugr.Rows.Count == 0) return; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugs in ugr.Rows ) { ugs.Selected = true; } } public override void Add() { if (Core.StlMes.Client.Sale.Util.MessageUtil.ShowYesNoAndTips("是否确认结转") == DialogResult.No) return; if (this.ultraTabControl1.SelectedTab.Index == 1) { SelectAllRows(this.ultraGrid2); } else if (this.ultraTabControl1.SelectedTab.Index == 0) { SelectAllRows(this.ultraGrid1); } string errMsg = ""; AddAll(GetUgrs(), out errMsg); if (errMsg.Length == 0) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("操作成功"); } else { Core.StlMes.Client.Sale.Util.MessageUtil.ShowError(errMsg); } QueryAll(); } /// /// 结转期初，钢贸 /// private void AddAll( List< Infragistics.Win.UltraWinGrid.UltraGridRow > list,out string errMsg) { errMsg = ""; if (list == null || list.Count == 0) return; string actName = ""; string montht = ""; System.Collections.ArrayList listt = new System.Collections.ArrayList(); foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in list) { string premonth = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); DateTime dt = DateTime.ParseExact(premonth, "yyyyMM", System.Globalization.CultureInfo.CurrentCulture); if (dt > System.DateTime.Now.AddDays(-System.DateTime.Now.Day)) { errMsg = "当月不能结转"; return; } montht = premonth; string month = dt.AddMonths(1).ToString("yyyyMM"); string invoiceacct = ugr.Cells[this.INVOICE_ACCOUNT].Value.ToString(); actName = invoiceacct; string balancesubject = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string secaccount = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); double premoney = Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(ugr.Cells[this.PRE_MONEY].Value); double inmoney = Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(ugr.Cells[this.INMONEY].Value); double invmoney = Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(ugr.Cells[this.INVOICE_MONEY].Value); double lstmoney = Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(ugr.Cells[this.LST_MONEY].Value); listt.AddRange(GetSqlInserAndUpdateNextAccountBook(month, premonth, invoiceacct, balancesubject ,buyercode,secaccount,premoney,inmoney,invmoney,lstmoney)); } if (actName == "集团公司") { listt.AddRange(GetUpdateGroupInvoice(montht)); } if (actName == "钢贸公司") { listt.AddRange(GetUpdateGmInvoice(montht)); } if (actName == "制造公司") { listt.AddRange(GetUpdateProcInvoice(montht)); } base.ExecuteNoReader(listt); } private System.Collections.ArrayList GetUpdateGroupInvoice(string invoiceMonth) { System.Collections.ArrayList list = new System.Collections.ArrayList(); string sql = @" update Slm_Balanceinfo set invoice_sts = '1' where invoice_month = '{0}' and balancestatus = '3' and realinvoiceno is not null and fst_drawee_unit = '120603' "; sql = string.Format(sql,invoiceMonth); string sqlT = @" update Slm_Balance_Transmoney set invoice_sts = '1' where invoice_month = '{0}' and status_cd = '2' and realinvoiceno is not null and fst_drawee_unit = '120603' "; sqlT = string.Format(sqlT, invoiceMonth); list.Add(sql); list.Add(sqlT); return list; } private System.Collections.ArrayList GetUpdateGmInvoice(string invoiceMonth) { System.Collections.ArrayList list = new System.Collections.ArrayList(); string sql = @" update Slm_Balanceinfo set invoice_sts = '1' where invoice_month = '{0}' and balancestatus = '3' and realinvoiceno is not null and forecorpcode = '钢贸公司' "; sql = string.Format(sql, invoiceMonth); string sqlT = @" update Slm_Balance_Transmoney set invoice_sts = '1' where invoice_month = '{0}' and status_cd = '2' and realinvoiceno is not null and forecorpcode = '钢贸公司' "; sqlT = string.Format(sqlT, invoiceMonth); list.Add(sql); list.Add(sqlT); return list; } private System.Collections.ArrayList GetUpdateProcInvoice(string invoiceMonth) { System.Collections.ArrayList list = new System.Collections.ArrayList(); string sql = @" update Slm_Balanceinfo set invoice_sts = '1' where invoice_month = '{0}' and balancestatus = '3' and realinvoiceno is not null and fst_drawee_unit = '120606' "; sql = string.Format(sql, invoiceMonth); string sqlT = @" update Slm_Balance_Transmoney set invoice_sts = '1' where invoice_month = '{0}' and status_cd = '2' and realinvoiceno is not null and fst_drawee_unit = '120606' "; sqlT = string.Format(sqlT, invoiceMonth); list.Add(sql); list.Add(sqlT); return list; } private System.Collections.ArrayList GetSqlInserAndUpdateNextAccountBook(string month, string premonth, string invoiceacct, string balancesubject, string buyercode, string secaccount, double premoney, double inmoney, double invmoney, double lstmoney ) { System.Collections.ArrayList list = new System.Collections.ArrayList(); string sql = @" begin insert into sel_fund_receive_account t (invoice_month,invoice_account,balancesubject,buyercode,secondaccount) select '{0}','{1}','{2}','{3}','{4}' from dual ; commit;exception when others then null; end "; string sqlUpdate = @" update sel_fund_receive_account set PRE_MONEY = {0} where invoice_account = '{1}' and invoice_month = '{2}' and balancesubject = '{3}' and buyercode = '{4}' and secondaccount = '{5}' and INVOICE_STS = '0' "; string sqlUpdatethisMonth = @" update sel_fund_receive_account set INMONEY = {0},INVOICE_MONEY = {1}, LST_MONEY = {2} where invoice_account = '{3}' and invoice_month = '{4}' and balancesubject = '{5}' and buyercode = '{6}' and secondaccount = '{7}' and INVOICE_STS = '0' "; string sqlUpdateSts = @" update sel_fund_receive_account set INVOICE_STS = '1' where invoice_account = '{0}' and invoice_month = '{1}' and balancesubject = '{2}' and buyercode = '{3}' and secondaccount = '{4}' "; sql = string.Format(sql, month, invoiceacct, balancesubject, buyercode, secaccount); sqlUpdate = string.Format(sqlUpdate, lstmoney, invoiceacct, month, balancesubject,buyercode,secaccount); sqlUpdatethisMonth = string.Format(sqlUpdatethisMonth, inmoney, invmoney, lstmoney, invoiceacct, premonth, balancesubject, buyercode, secaccount ); sqlUpdateSts = string.Format(sqlUpdateSts, invoiceacct, premonth, balancesubject, buyercode, secaccount); list.Add(sql); list.Add(sqlUpdate); list.Add(sqlUpdatethisMonth); list.Add(sqlUpdateSts); return list; } private void QueryAdjust() { string month = this.dateTimePicker2.Value.ToString("yyyyMM"); string sql = @"select INVOICE_MONTH, INVOICE_ACCOUNT, BALANCESUBJECT, SECONDACCOUNT, BUYERCODE, PRE_MONEY, MONEY, UPDATE_PERSON, UPDATE_DATE INOUTTIME, REMARK from sel_fund_receive_adjust_detail a where 1 = 1 and pre_money <> money {0} order by update_date desc "; string sqlCondition = string.Format(" and invoice_month = '{0}' ",month); sql = string.Format(sql,sqlCondition); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref this.ds_Adjust, ds); } private void QueryAll() { if (this.ultraTabControl1.SelectedTab.Index == 1) { this.QueryGm();//国际贸易部 } else if (this.ultraTabControl1.SelectedTab.Index == 0) { this.QueryGroup();//国内贸易部 } QueryAdjust(); } private List GetUgrs() { List ugrs = new List(); if (this.ultraTabControl1.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.ultraTabControl1.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); } } return ugrs; } private void UpdateAll() { UpdatePremoney(GetUgrs()); this.QueryAll(); } private void UpdatePremoney(List ugrs) { if (ugrs == null) return; if (ugrs.Count == 0) return; System.Collections.ArrayList list = new System.Collections.ArrayList(); System.Collections.ArrayList listTemp = new System.Collections.ArrayList(); foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ugrs) { if (ugr.Cells[this.INVOICE_STS].Value.ToString() == "1") { Core.StlMes.Client.Sale.Util.MessageUtil.ShowError("数据已经结转，不允许修改期初"); return; } listTemp = GetUpdatePreMoney( Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble( ugr.Cells["MONEY"].Value), Core.StlMes.Client.Sale.Util.ConverObject.ConvertToDouble(ugr.Cells[PRE_MONEY].Value), CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName(), ugr.Cells[this.INVOICE_MONTH].Value.ToString(), ugr.Cells[this.INVOICE_ACCOUNT].Value.ToString(), ugr.Cells[this.BALANCESUBJECT].Value.ToString(), ugr.Cells[this.SECONDACCOUNT].Value.ToString(), ugr.Cells[this.BUYERCODE].Value.ToString(), "" ); list.AddRange(listTemp); } base.ExecuteNoReader(list); // this.QueryAll(); } //国际贸易部 private void QueryGm() { string month = this.dateTimePicker2.Value.ToString("yyyyMM"); string sql = GetGmSql(month); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref this.ds_Gm, ds); } //国内贸易部 private void QueryGroup() { string month = this.dateTimePicker2.Value.ToString("yyyyMM"); string sql = GetGroupSql(month); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref this.ds_Group, ds); } private void QueryProce() { string month = this.dateTimePicker2.Value.ToString("yyyyMM"); string sql = this.GetproceSql(month); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref this.ds_Proce, ds); } private void QueryProceAll() { string month = this.dateTimePicker2.Value.ToString("yyyyMM"); string sql = this.GetProceAllSql(month); DataSet ds = base.ExecuteReader(sql); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref this.ds_P_Gm, ds); } private System.Collections.ArrayList GetUpdatePreMoney(double primoney, double money, string person, string month, string account,string balancesubj,string secondaccount,string buyercode,string adjusitremark ) { string sql = @" update SEL_FUND_RECEIVE_ACCOUNT set PRE_MONEY = {0} ,UPDATE_PERSON = '{1}',UPDATE_DATE = sysdate where INVOICE_MONTH = '{2}' and INVOICE_ACCOUNT = '{3}' and BALANCESUBJECT = '{4}' and SECONDACCOUNT = '{5}' and BUYERCODE = '{6}' "; string sqlInsert = @" insert into SEL_FUND_RECEIVE_ADJUST_DETAIL ( INVOICE_MONTH , INVOICE_ACCOUNT , BALANCESUBJECT , SECONDACCOUNT , BUYERCODE , PRE_MONEY , MONEY , UPDATE_PERSON , UPDATE_DATE , REMARK ) values ( '{0}', '{1}', '{2}', '{3}', '{4}', {5}, {6}, '{7}', sysdate, '{8}' )"; sql = string.Format(sql,money,person,month,account,balancesubj,secondaccount,buyercode); sqlInsert = string.Format(sqlInsert, month, account, balancesubj, secondaccount, buyercode , primoney, money, person, adjusitremark ); System.Collections.ArrayList list = new System.Collections.ArrayList(); list.Add(sql); list.Add(sqlInsert); return list; } private string[] GetDataStruct() { return new string[] { INVOICE_MONTH, INVOICE_ACCOUNT, BALANCESUBJECT, SECONDACCOUNT, BUYERCODE, PRE_MONEY, INMONEY, INVOICE_MONEY, LST_MONEY, // INVOICE_PERSON, // INVOICE_DATE, // UPDATE_PERSON, // UPDATE_DATE, INVOICE_STS, "MONEY", "ACT_WEIGHT", "BALNCE_PRICE", "WEIGHT1", "MONEY1", "MONEY2" }; } private string[] GetDataStructAdj() { return new string[] { INVOICE_MONTH, INVOICE_ACCOUNT, BALANCESUBJECT, SECONDACCOUNT, BUYERCODE, PRE_MONEY, "MONEY", "UPDATE_PERSON", "INOUTTIME" }; } private string[] GetInvoiceStruct() { return new string[] { "PACTNO", "WEIGHT", "MONEY", "BUYERNAME", "SALE_AREA_DESC", "FST_DRAWEE_UNIT" }; } public string[] GetTbColumn_SEL_FUND_CUST_FUNDINOUT() { return new string[] { "BUSISEQ", "BALANCESUBJECT", "SECONDACCOUNT", // "BUYERCODE", "INOUTMONEY", "INOUTTIME", "FUNDTYPE", "BILLNO", // "ISVALID", // "MONEY_DELIVER", // "PREBUYERCODE", "REMARK", "IMPORTOR", "IMPORTTIME", "AFFRIMPER", "AFFRIMDATE" }; } private string INVOICE_MONTH = "INVOICE_MONTH"; private string INVOICE_ACCOUNT = "INVOICE_ACCOUNT"; private string BALANCESUBJECT = "BALANCESUBJECT"; private string SECONDACCOUNT = "SECONDACCOUNT"; private string BUYERCODE = "BUYERCODE"; private string PRE_MONEY = "PRE_MONEY"; private string INMONEY = "INMONEY"; private string INVOICE_MONEY = "INVOICE_MONEY"; private string LST_MONEY = "LST_MONEY"; private string INVOICE_PERSON = "INVOICE_PERSON"; private string INVOICE_DATE = "INVOICE_DATE"; private string UPDATE_PERSON = "UPDATE_PERSON"; private string UPDATE_DATE = "UPDATE_DATE"; private string INVOICE_STS = "INVOICE_STS"; private string GetQuerySqlByMonthAndAccountBook(string month,string accountBook) { string sqlStr = " select * from SEL_FUND_RECEIVE_ACCOUNT a where 1 = 1 {0}"; string sqlCondition = string.Format(" and INVOICE_MONTH = '{0}' ",month); sqlCondition += string.Format(" and INVOICE_ACCOUNT = '{0}' ", accountBook); sqlStr = string.Format(sqlStr, sqlCondition); return sqlStr; } private string GetGmSql(string month) { string sql = @" select b.balancesubject, b.buyercode, b.secondaccount, nvl(b.pre_money, 0) pre_money, nvl(b.pre_money, 0) money, b.INVOICE_ACCOUNT, nvl(inmoney, nvl(c.inoutmoney, 0)) INMONEY, nvl(invoice_money, f.money) INVOICE_MONEY, nvl(lst_money, nvl(b.pre_money, 0) - nvl(c.inoutmoney, 0) + nvl( f.money,0) ) LST_MONEY, b.invoice_person, b.invoice_date, b.invoice_date,b.invoice_month,b.invoice_account,b.INVOICE_STS from SEL_FUND_RECEIVE_ACCOUNT b left join (select sum(fu.inoutmoney) inoutmoney, fu.balancesubject, fu.buyercode, fu.secondaccount from sel_fund_cust_fundinout fu where fu.isvalid = '1' and fu.inouttime > to_date('{0}', 'yyyymm') and fu.inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1 group by fu.balancesubject, fu.buyercode, fu.secondaccount) c on b.balancesubject = c.balancesubject and b.buyercode = c.buyercode and b.secondaccount = c.secondaccount left join ( select sum(s.money) money, s.buyercode, s.sale_area_desc, s.forecorpcode from (select sum(s.money) money, s.buyercode, s.sale_area_desc, s.forecorpcode from slm_balanceinfo s where s.balancestatus = '3' and s.realinvoiceno is not null and s.forecorpcode = '国际贸易部' and s.invoice_month = '{0}' group by s.buyercode, s.sale_area_desc, s.forecorpcode union all select sum(b.money), b.buyercode, c.sale_area_desc, b.forecorpname from slm_balance_transmoney b join slm_order_head c on b.ord_pk = c.ord_pk where 1 = 1 and b.status_cd = '2' and b.tickettype = '应收' and b.invoice_month = '{0}' and b.realinvoiceno is not null and b.forecorpname = '国际贸易部' group by b.buyercode, c.sale_area_desc, b.forecorpname ) s group by s.buyercode, s.sale_area_desc,s.forecorpcode ) f on b.balancesubject = f.forecorpcode and b.buyercode = f.buyercode and b.secondaccount = f.sale_area_desc where 1 = 1 and b.balancesubject = '国际贸易部' and b.invoice_account = '国际贸易部' and b.invoice_month = '{0}' "; sql = string.Format(sql,month); return sql; } private string GetGroupSql(string month) { string sql = @" select b.balancesubject, b.buyercode, b.secondaccount, nvl(b.pre_money, 0) pre_money, nvl(b.pre_money, 0) money, b.INVOICE_ACCOUNT, nvl(inmoney, nvl(c.inoutmoney, 0)) INMONEY, nvl(invoice_money, f.money) INVOICE_MONEY, nvl(lst_money, nvl( b.pre_money, 0 ) + nvl( inmoney, nvl( c.inoutmoney, 0 ) ) - nvl(nvl( invoice_money, f.money ),0) ) LST_MONEY, b.invoice_person, b.invoice_date, b.invoice_date,b.invoice_month,b.invoice_account,b.INVOICE_STS ,BILL.ACT_WEIGHT,BILL.BALNCE_PRICE, BALANCE.WEIGHT WEIGHT1,BALANCE.MONEY MONEY1, nvl( b.pre_money, 0 ) + nvl( inmoney, nvl( c.inoutmoney, 0 ) ) -nvl( BILL.BALNCE_PRICE, 0 ) - nvl( BALANCE.MONEY, 0 ) - nvl( BALANCE2.MONEY, 0 ) MONEY2 from SEL_FUND_RECEIVE_ACCOUNT b left join (select sum(c.inoutmoney) inoutmoney, c.balancesubject, c. buyercode, c.secondaccount from ( select sum(fu.inoutmoney) inoutmoney, fu.balancesubject, fu.buyercode, fu.secondaccount from sel_fund_cust_fundinout fu where fu.isvalid = '1' AND GET_FISCAL_MONTH1(fu.inouttime) = '{0}' and busiseq not like '%-1' and fu.balancesubject = '国内贸易部' group by fu.balancesubject, fu.buyercode, fu.secondaccount ) c group by c.balancesubject, c.buyercode, c.secondaccount) c on b.balancesubject = c.balancesubject and b.buyercode = c.buyercode and b.secondaccount = c.secondaccount left join ( select sum(s.money) money, s.buyercode, s.sale_area_desc, s.forecorpcode from (select CASE CONTAIN_TAX WHEN '1' THEN sum( s.money ) ELSE ROUND(sum( s.money )* 1.13,3) END money, s.buyercode, s.sale_area_desc, s.forecorpcode from slm_balanceinfo s where s.balancestatus = '3' and s.forecorpcode = '国内贸易部' and s.FST_DRAWEE_UNIT = '120604' and GET_FISCAL_MONTH1(s.INVOICEDATE) = '{0}' group by CONTAIN_TAX,s.buyercode, s.sale_area_desc, s.forecorpcode union all select sum(b.money), b.buyercode, c.sale_area_desc, b.forecorpname from slm_balance_transmoney b join slm_order_head c on b.ord_pk = c.ord_pk where 1 = 1 and b.status_cd = '2' and b.tickettype = '应收' and b.forecorpname = '国内贸易部' and b.FST_DRAWEE_UNIT = '120604' and GET_FISCAL_MONTH1(b.REG_DTIME) = '{0}' group by b.buyercode, c.sale_area_desc, b.forecorpname) s group by s.buyercode, s.sale_area_desc, s.forecorpcode ) f on b.balancesubject = f.forecorpcode and b.buyercode = f.buyercode and b.secondaccount = f.sale_area_desc LEFT JOIN ( SELECT T.CUSTOMER_NO,T.SALE_ORG_DESC,T.MANAGEMENT_NAME,SUM(T1.ACT_COUNT) ACT_COUNT,SUM(T1.ACT_WEIGHT) ACT_WEIGHT,SUM(T1.SEND_NUM) SEND_NUM, SUM(t2.BALNCE_PRICE * t1.ACT_WEIGHT) BALNCE_PRICE FROM YDM_ZC_BILL_M t LEFT JOIN YDM_ZC_BILL_C T1 ON T.BILL_NO = T1.BILL_NO AND t.ORD_LN_PK = t1.ORD_LN_PK LEFT JOIN SLM_ORDER_LINE t2 on t.ORD_LN_PK = t2.ORD_LN_PK WHERE T.BILL_STATUS IN ( '1', '2', '3' ) and FST_DRAWEE_UNIT = '120604' AND NOT EXISTS ( SELECT 1 FROM SLM_BALANCEINFO WHERE (BALANCESTATUS = '3' OR BALANCESTATUS_ORG = '3')AND ASKPLAN_ID = T.BILL_NO ) GROUP BY T.CUSTOMER_NO,T.SALE_ORG_DESC,T.MANAGEMENT_NAME ) BILL ON BILL.CUSTOMER_NO =B.buyercode AND BILL.MANAGEMENT_NAME = B.SECONDACCOUNT AND BILL.SALE_ORG_DESC = B.INVOICE_ACCOUNT LEFT JOIN ( SELECT BUYERCODE, FORECORPCODE, SALE_AREA_DESC, sum(WEIGHT) WEIGHT, sum(MONEY) MONEY FROM SLM_BALANCEINFO T WHERE ( ( BALANCESTATUS IN ( '0', '1', '2' ) AND ISPRECLOSE = '是' AND EXISTS ( SELECT 1 FROM YDM_ZC_BILL_M WHERE BILL_NO = T.ASKPLAN_ID AND BILL_STATUS = '4') ) OR ( BALANCESTATUS IN ( '0', '1', '2' ) AND ISPRECLOSE = '否' ) ) and FST_DRAWEE_UNIT = '120604' GROUP BY BUYERCODE, FORECORPCODE, SALE_AREA_DESC ) BALANCE ON BALANCE.BUYERCODE =B.buyercode AND BALANCE.FORECORPCODE = B.INVOICE_ACCOUNT AND BALANCE.SALE_AREA_DESC = B.SECONDACCOUNT LEFT JOIN ( SELECT BUYERCODE, FORECORPCODE, SALE_AREA_DESC, sum( WEIGHT ) WEIGHT, sum( MONEY ) MONEY FROM SLM_BALANCEINFO T WHERE GET_FISCAL_MONTH1 ( INVOICEDATE ) = '{0}' AND BALANCESTATUS = '3' and FST_DRAWEE_UNIT = '120604' AND EXISTS ( SELECT 1 FROM YDM_ZC_BILL_M WHERE BILL_STATUS IN ( '4', '5' ) AND BILL_NO = T.ASKPLAN_ID ) GROUP BY BUYERCODE, FORECORPCODE, SALE_AREA_DESC ) BALANCE2 ON BALANCE2.BUYERCODE = B.buyercode AND BALANCE2.FORECORPCODE = B.INVOICE_ACCOUNT AND BALANCE2.SALE_AREA_DESC = B.SECONDACCOUNT where 1 = 1 and b.invoice_account = '国内贸易部' and b.invoice_month = '{0}' "; if (!string.IsNullOrEmpty(_buyercode)) { sql += " and b.buyercode = '" + _buyercode + "'"; } sql = string.Format(sql, month, (int.Parse(month) - 1).ToString()); return sql; } private string GetproceSql(string month) { string sql = @" select b.balancesubject, b.buyercode, b.secondaccount, nvl(b.pre_money, 0) pre_money, nvl(b.pre_money, 0) money, b.INVOICE_ACCOUNT, nvl(inmoney, nvl(c.inoutmoney, 0)) INMONEY, nvl(invoice_money, f.money) INVOICE_MONEY, nvl(lst_money, nvl(b.pre_money, 0) - nvl(c.inoutmoney, 0) + nvl( f.money,0) ) LST_MONEY, b.invoice_person, b.invoice_date, b.invoice_date,b.invoice_month,b.invoice_account,b.INVOICE_STS from SEL_FUND_RECEIVE_ACCOUNT b left join (select sum(c.inoutmoney) inoutmoney, c.balancesubject, c. buyercode, c.secondaccount from (select sum(fu.inoutmoney) inoutmoney, fu.balancesubject, fu.buyercode buyercode, fu.secondaccount from sel_fund_cust_fundinout fu where fu.isvalid = '1' and fu.inouttime > to_date('{0}', 'yyyymm') and fu.money_deliver = '1' and fu.balancesubject = '国内贸易部' -- and fu.buyercode = '1010101184' -- and 1 = 2 and fu.inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1 group by fu.balancesubject, fu.buyercode, fu.secondaccount ) c group by c.balancesubject, c.buyercode, c.secondaccount) c on b.balancesubject = c.balancesubject and b.buyercode = c.buyercode and b.secondaccount = c.secondaccount left join ( select sum(s.money) money, s.buyercode, s.sale_area_desc, s.forecorpcode from (select sum(s.money) money, s.buyercode, s.sale_area_desc, s.forecorpcode from slm_balanceinfo s where s.balancestatus = '3' and s.realinvoiceno is not null and s.fst_drawee_unit = '120606' and s.invoice_month = '{0}' group by s.buyercode, s.sale_area_desc, s.forecorpcode union all select sum(b.money), b.buyercode, c.sale_area_desc, b.forecorpname from slm_balance_transmoney b join slm_order_head c on b.ord_pk = c.ord_pk where 1 = 1 and b.status_cd = '2' and b.tickettype = '应收' and b.realinvoiceno is not null and b.fst_drawee_unit = '120606' and b.invoice_month = '{0}' group by b.buyercode, c.sale_area_desc, b.forecorpname) s group by s.buyercode, s.sale_area_desc, s.forecorpcode ) f on b.balancesubject = f.forecorpcode and b.buyercode = f.buyercode and b.secondaccount = f.sale_area_desc where 1 = 1 and b.invoice_account = '制造公司' and b.invoice_month = '{0}' "; sql = string.Format(sql, month); return sql; } private string GetProceAllSql(string month) { string sql = @" select a.invoice_month, a.invoice_account, a.balancesubject, a.secondaccount, a.buyercode, a.pre_money, INMONEY, invoice_money, a.invoice_sts, nvl(a.lst_money, nvl(pre_money,0) - nvl(INMONEY, 0) + nvl(invoice_money, 0)) lst_money from ( select a.invoice_month, a.invoice_account, a.balancesubject, a.secondaccount, a.buyercode, a.pre_money, nvl(INMONEY, case when a.balancesubject = '钢贸' then gm when a.balancesubject = '国贸' then gmx when a.balancesubject = '集团' then groupm when a.balancesubject = '制造' then prc else 0 end) INMONEY, nvl(a.invoice_money, case when a.balancesubject = '钢贸' then c.gmmoney1 when a.balancesubject = '国贸' then c.gmxmoney when a.balancesubject = '集团' then c.groupm1 when a.balancesubject = '制造' then c.procmoney else 0 end) invoice_money, a.invoice_sts, a.lst_money from sel_fund_receive_account a left join (select sum(case when a.balancesubject = '钢贸公司' and a.money_deliver = '0' then a.inoutmoney else 0 end) gm, sum(case when a.balancesubject = '国贸公司' and a.money_deliver = '0' then a.inoutmoney else 0 end) gmx, sum(case when a.balancesubject = '国内贸易部' and a.money_deliver = '1' then a.inoutmoney else 0 end) prc, sum(case when a.balancesubject = '国内贸易部' and a.money_deliver = '2' then 0 else 0 end) groupm from sel_fund_cust_fundinout a where a.isvalid = '1' and inouttime > to_date('{0}', 'yyyymm') and inouttime <= last_day(to_date('{0}', 'yyyymm')) + 1 ) t on 1 = 1 left join (select sum(case when fst_drawee_unit = '120603' then money else 0 end) groupm1, sum(case when fst_drawee_unit = '120604' then money else 0 end) gmmoney1, sum(case when fst_drawee_unit = '120605' then money else 0 end) gmxmoney, sum(case when fst_drawee_unit = '120606' then money else 0 end) procmoney from (select sum(s.money_org) money, fst_drawee_unit from slm_balanceinfo s where s.balancestatus_org = '3' and s.realinvoiceno_org is not null and s.invoice_month = '{0}' group by fst_drawee_unit union all select sum(b.money), b.fst_drawee_unit from slm_balance_transmoney b join slm_order_head c on b.ord_pk = c.ord_pk where 1 = 1 and b.tickettype = '应收' and b.realinvoiceno_org is not null and b.invoice_month = '{0}' group by b.fst_drawee_unit) ) c on 1 = 1 where a.invoice_month = '{0}' and a.invoice_account = '制造全' ) a "; sql = string.Format(sql,month); return sql; } private void ultraGrid2_ClickCell(object sender, ClickCellEventArgs e) { } private void ultraGrid1_ClickCell(object sender, ClickCellEventArgs e) { } private void ultraGrid3_ClickCell(object sender, ClickCellEventArgs e) { } private void ultraGrid1_DoubleClickCell(object sender, DoubleClickCellEventArgs e) { if (e.Cell.Column.Key == INVOICE_MONEY) { return; UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetGroupInvoicePact(month, buyercode, fore, salearea); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "合同查询"; bankfrm.ShowDialog(); } if (e.Cell.Column.Key == this.INMONEY) { UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetGroupMoneyInOut(fore, buyercode, salearea, month); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "进出款查询"; bankfrm.ShowDialog(); } } private void ultraGrid2_DoubleClickCell(object sender, DoubleClickCellEventArgs e) { if (e.Cell.Column.Key == INVOICE_MONEY) { return; UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetGmInvoicePact(month, buyercode, fore, salearea); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "合同查询"; bankfrm.ShowDialog(); } if (e.Cell.Column.Key == this.INMONEY) { UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetGmMoneyInOut(fore, buyercode, salearea, month); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "进出款查询"; bankfrm.ShowDialog(); } } private void ultraGrid3_DoubleClickCell(object sender, DoubleClickCellEventArgs e) { if (e.Cell.Column.Key == INVOICE_MONEY) { return; UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetProcInvoicePact(month, buyercode, fore, salearea); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "合同查询"; bankfrm.ShowDialog(); } if (e.Cell.Column.Key == this.INMONEY) { UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetProcMoneyInOut(fore, buyercode, salearea, month); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "进出款查询"; bankfrm.ShowDialog(); } } private void ultraGrid4_DoubleClickCell(object sender, DoubleClickCellEventArgs e) { if (e.Cell.Column.Key == INVOICE_MONEY) { return; UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); DataSet ds = this.GetProcAllInvoicePact(fore, month); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "合同查询"; bankfrm.ShowDialog(); } if (e.Cell.Column.Key == this.INMONEY) { UltraGridRow ugr = e.Cell.Row; string month = ugr.Cells[this.INVOICE_MONTH].Value.ToString(); string buyercode = ugr.Cells[this.BUYERCODE].Value.ToString(); string salearea = ugr.Cells[this.SECONDACCOUNT].Value.ToString(); string fore = ugr.Cells[this.BALANCESUBJECT].Value.ToString(); if (fore == "集团") return; DataSet ds = this.GetProcAllMoneyInOut(fore, month); FundUI.UIDlgBox.DlgBankFrm bankfrm = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgBankFrm(); bankfrm._Ds = ds; bankfrm._FrmText = "进出款查询"; bankfrm.ShowDialog(); } } private string _buyercode = ""; private void button1_Click(object sender, EventArgs e) { UIDlgBox.DlgCustChoose custChoose = new Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIDlgBox.DlgCustChoose(this.ob); custChoose.CustUse = "100202"; custChoose.SaleOrg = base.GetSaleOrg(); custChoose.ShowDialog(); if (custChoose.DialogResult == DialogResult.OK) { this.textBox1.Text = custChoose.customerName; _buyercode = custChoose.customerNo; this.Query(); } } } }