MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundUI/UIFrm/FrmReceiveAndDebtMgtNew.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;
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<ArrayList> 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<ArrayList> ReadExcelToPlnReqinfGpExcel(string fileName)
        {
            //定义要返回的datatable对象
            List<ArrayList> plnReqinfGpExcels = new List<ArrayList>();


            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();
            
        }



        /// <summary>
        ///  结转期初，钢贸
        /// </summary>
        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<Infragistics.Win.UltraWinGrid.UltraGridRow> GetUgrs()
        {
            List<Infragistics.Win.UltraWinGrid.UltraGridRow> ugrs = new List<UltraGridRow>();

            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<Infragistics.Win.UltraWinGrid.UltraGridRow> 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();
            }
        }
    }
}