using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;
namespace Core.StlMes.Client.Sale.SaleFundMgt.FundUI.UIFrm
{
public partial class FrmReceiveAndDebtMgt : FrmFundBase
{
public FrmReceiveAndDebtMgt()
{
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_Proce = base.GetDataSetByColumnName(this.GetDataStruct()
, null,
base.ConvertListStringArrayToHashTable(base.GetDataStructCaption()));
ds_P_Gm = base.GetDataSetByColumnName(this.GetDataStruct()
, base.GetNumberColumnHs(),
base.ConvertListStringArrayToHashTable(base.GetDataStructCaption()));
ds_P_Group = base.GetDataSetByColumnName(this.GetDataStruct()
, null,
base.ConvertListStringArrayToHashTable(base.GetDataStructCaption()));
ds_P_Ggm = 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.ultraGrid3.DataSource = ds_Proce;
this.ultraGrid4.DataSource = ds_P_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 void HiddelColumn()
{
foreach (UltraGrid ugs in GetAllUltraGrid())
{
try
{
foreach (string s in HiddenColumns())
ugs.DisplayLayout.Bands[0].Columns[s].Hidden = true;
}
catch
{ }
}
this.ultraGrid4.DisplayLayout.Bands[0].Columns[this.SECONDACCOUNT].Hidden = true;
this.ultraGrid4.DisplayLayout.Bands[0].Columns[this.BUYERCODE].Hidden = true;
}
private UltraGrid[] GetAllUltraGrid()
{
return new UltraGrid[] { this.ultraGrid1, this.ultraGrid2, this.ultraGrid3, this.ultraGrid4};
}
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_org_desc <> '国际贸易部'
and 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) 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";
PubValueList = new Core.StlMes.Client.Sale.SaleFundMgt.FundBLL.ValueList.PubValueList(this.ob);
InitDs();
HiddelColumn();
InitGridValue();
base.SetGridStyle(ref this.ultraGrid1, ref this.ultraGrid2, ref this.ultraGrid3,ref this.ultraGrid4,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 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;
case "制造对外":
Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel("账本信息", this.ultraGrid3);
break;
case "制造对内":
Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel("账本信息", this.ultraGrid4);
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);
}
else if (this.ultraTabControl1.SelectedTab.Index == 2)
{
SelectAllRows(this.ultraGrid3);
}
else if (this.ultraTabControl1.SelectedTab.Index == 3)
{
SelectAllRows(this.ultraGrid4);
}
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();
}
else if (this.ultraTabControl1.SelectedTab.Index == 2)
{
this.QueryProce();
}
else if (this.ultraTabControl1.SelectedTab.Index == 3)
{
this.QueryProceAll();
}
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);
}
}
else if (this.ultraTabControl1.SelectedTab.Index == 2)
{
if (this.ultraGrid3.Rows.Count == 0)
return ugrs;
this.ultraGrid3.UpdateData();
if (this.ultraGrid3.ActiveRow != null)
this.ultraGrid3.ActiveRow.Selected = true;
foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid3.Selected.Rows)
{
ugrs.Add(ugr);
}
}
else if (this.ultraTabControl1.SelectedTab.Index == 3)
{
if (this.ultraGrid4.Rows.Count == 0)
return ugrs;
this.ultraGrid4.UpdateData();
if (this.ultraGrid4.ActiveRow != null)
this.ultraGrid4.ActiveRow.Selected = true;
foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid4.Selected.Rows)
{
ugrs.Add(ugr);
}
}
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",
ORDERNO
};
}
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 ORDERNO = "ORDERNO";
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 money_deliver = '0'
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(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 = '2'
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
union all
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.money_deliver = '2'
and busiseq not like '%-1'
and fu.balancesubject = '国内贸易部'
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 = '120603'
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 = '120603'
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 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,c.orderno
from SEL_FUND_RECEIVE_ACCOUNT b
left join (select sum(c.inoutmoney) inoutmoney,
c.balancesubject,
c. buyercode,
c.secondaccount,
c.orderno
from (select sum(fu.inoutmoney) inoutmoney,
fu.balancesubject,
fu.buyercode buyercode,
fu.secondaccount,
fu.orderno
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,
fu.orderno
) c
group by c.balancesubject, c.buyercode, c.secondaccount,c.orderno) 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_AfterRowActivate(object sender, EventArgs e)
{
}
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();
}
}
}
}