using System;
using System.Data;
using System.Windows.Forms;
using SAP.Middleware.Connector;
namespace Thinkape
{
public partial class HXSAP : Form
{
public HXSAP()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
NewMethod1();
}
/// <summary>
/// 获取sap的数据
/// </summary>
private void NewMethod1()
{
RfcDestination dest;
RfcRepository rfcrep;
NewMethod(out dest, out rfcrep);
IRfcFunction myfun = null;
//获取sap的数据
myfun = rfcrep.CreateFunction("ZFK_WERKS_TRAN");//凭证函数
myfun.Invoke(dest);
//ITAB sap系统表名
IRfcTable retb = myfun.GetTable("ITAB");
string INBILLNO = "";
string INBILLNO2 = "";
prog.Maximum = retb.RowCount * 2;
prog.Value = 0;
textBox1.Text = "";
if (retb.RowCount == 0)
{
textBox1.Text = "暂时没有数据！无需导入...";
}
else
{
for (int i = 0; i < retb.RowCount; i++)
{
retb.CurrentIndex = i;
//失败的二次发送
string zbudn= retb.CurrentRow.GetValue("ZBUDN").ToString ();
string ZITEM_NO = retb.CurrentRow.GetValue("ZITEM_NO").ToString();
//根据ZITEM_NO，ZBUDN 判断，ZITEM_NO(项目)+ZBUDN(门店预算号），这个组合才是唯一的
string str_con = " select m.BillNo from sap_detail d left join sap_main m on d.billid=m.billid where d.ZBUDN='" + zbudn + "' and d.ZITEM_NO='" + ZITEM_NO + "' and Isdiscard is null ";
DataSet resultSecond = SapVoucher.SAPinsert.GetDataSet(str_con);
if (resultSecond.Tables[0].Rows.Count > 0)
{
string BillNO = resultSecond.Tables[0].Rows[0][0].ToString();
INBILLNO += "'" + BillNO + "',";
}
else
{
string BillNO = GetBillNO();
INBILLNO += "'" + BillNO + "',";
INBILLNO2 += "" + BillNO + ",";
string sql = string.Format(@"INSERT INTO sapmain( BillID,billno,billdate,MANDT,ZBUDN,ZITEM_NO,WERKS,ZDATE,ZDEPT,ZFKACC,ZNETPR,
ZJG,ZQTY,ZSTS_TR,ZERNAM,ZERDAT,ZERTIM,ZERPRG,ZUPNAM,ZUPDAT,ZUPTIM,ZUPPRG,CurrentProgramID,Create_Date,HsCompanyID)
VALUES((SELECT CAST(ISNULL( MAX(BillID),0) AS INT)+1 FROM sapmain),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',132102,'" + DateTime.Now + "',(SELECT HsCompanyID FROM sys_dept WHERE cDepCode='{21}'))",
BillNO, retb.CurrentRow.GetValue("ZBUDAT"), retb.CurrentRow.GetValue("MANDT"), retb.CurrentRow.GetValue("ZBUDN"), retb.CurrentRow.GetValue("ZITEM_NO"),
retb.CurrentRow.GetValue("WERKS"), retb.CurrentRow.GetValue("ZBUDAT"), retb.CurrentRow.GetValue("ZDEPT"),
retb.CurrentRow.GetValue("ZFKACC"), retb.CurrentRow.GetValue("ZSTAJG"),
retb.CurrentRow.GetValue("ZJG"), retb.CurrentRow.GetValue("ZQTY"), retb.CurrentRow.GetValue("ZSTS_TR"),
retb.CurrentRow.GetValue("ZERNAM"), retb.CurrentRow.GetValue("ZERDAT"), retb.CurrentRow.GetValue("ZERTIM"),
retb.CurrentRow.GetValue("ZERPRG"), retb.CurrentRow.GetValue("ZUPNAM"), retb.CurrentRow.GetValue("ZUPDAT"),
retb.CurrentRow.GetValue("ZUPTIM"), retb.CurrentRow.GetValue("ZUPPRG"), retb.CurrentRow.GetValue("ZDEPT"));
//新增字表数据
string sqlZB = string.Format("INSERT INTO sapdetail(BillID,BillMoney,DeptID,CostTypeID,ZJG,ZBUDN,ZITEM_NO,ZERDAT,FyType,ZDEPT,ZFKACC,WERKS) VALUES((SELECT CAST(ISNULL( MAX(BillID),0) AS INT)+1 FROM sapmain),'{0}',(SELECT iDepID FROM dbo.Sys_Dept WHERE cDepCode='{1}'),(select CostID from EB_CostType where CostCode ='{2}'),{3},'{4}','{5}','{6}','公司承担','{7}','{8}','{9}');", retb.CurrentRow.GetValue("ZJG"), retb.CurrentRow.GetValue("ZDEPT"), retb.CurrentRow.GetValue("ZFKACC"), retb.CurrentRow.GetValue("ZSTAJG"), retb.CurrentRow.GetValue("ZBUDN"), retb.CurrentRow.GetValue("ZITEM_NO"), retb.CurrentRow.GetValue("ZERDAT"), retb.CurrentRow.GetValue("ZDEPT"), retb.CurrentRow.GetValue("ZFKACC"), retb.CurrentRow.GetValue("WERKS"));
SapVoucher.SAPinsert.InsertSAP(sqlZB);
string msg = SapVoucher.SAPinsert.InsertSAP(sql);
}
}
//合并单据
//string hbsql = string.Format("EXEC HX_SAPHB @billNO='{0}'", INBILLNO2);
//DataSet s = SapVoucher.SAPinsert.GetDataSet(hbsql);
// string sqlll = s.Tables[0].Rows[0][0].ToString();
//DataSet sss = SapVoucher.SAPinsert.GetDataSet(sqlll);
//提交单据
INBILLNO = INBILLNO.Substring(0, INBILLNO.Length - 1);
string INBILLNOsql = string.Format("SELECT BillID,* FROM sap_main WHERE BillNo IN({0})", INBILLNO);
DataSet datads = SapVoucher.SAPinsert.GetDataSet(INBILLNOsql);
double f = 0;
foreach (DataRow a in datads.Tables[0].Rows )
{
string tempsql = string.Format(" select billmoney from sap_detail where billid='{0}'", a[0]);
DataSet tempdatads = SapVoucher.SAPinsert.GetDataSet(tempsql);
foreach (DataRow b in tempdatads.Tables[0].Rows)
{
f += Convert.ToDouble(b[0]);
}
string updatesql = string.Format(" update sap_main set ZJG='{1}' where billid='{0}'", a[0],f);
SapVoucher.SAPinsert.GetCount(updatesql);
f = 0;
}
foreach (DataRow dr in datads.Tables[0].Rows)
{
///未审核单据才提交扣减
string str_status = "select flowstatus from sap_Main where billNO= '" + dr["billNO"]+"'";
DataSet ds_status = SapVoucher.SAPinsert.GetDataSet(str_status);
if (ds_status.Tables[0].Rows[0][0].ToString() == "0" || ds_status.Tables[0].Rows[0][0].ToString() == "")
{
#region 提交单据
//提交单据
string procsql = string.Format(@"declare @result varchar(8000);
exec Sys_WFSubmit @ProgramID = 132102,@PrimaryKey ={0},
@BillNo = '{1}',@Uid = 7,@AppOpinion = '',
@AppTerminal = 'PC',@FlowCode = 'submit',@SimSubmit = 0,@result = @result output; select @result as result; ", dr["BillID"].ToString(), dr["BillNo"].ToString());
DataSet result = SapVoucher.SAPinsert.GetDataSet(procsql);
//预算是否充足
string str_param = "select m.BillNo,d.zdept,d.zfkacc,year(d.zerdat) as zerdat,c.CostName,de.cDepName from " + " sap_detail d left join sap_main m on d.billid=m.billid " +
" left join Sys_Dept de on de.cDepCode=d.zdept left join EB_CostType c on c.CostCode=d.zfkacc " +
" where m.BillNo='" + dr["BillNo"].ToString() + "'";
DataSet ds_param = SapVoucher.SAPinsert.GetDataSet(str_param);
//预算扣减
string year = ds_param.Tables[0].Rows[0][3].ToString();
string cdepcode = ds_param.Tables[0].Rows[0][1].ToString(), costcode = ds_param.Tables[0].Rows[0][2].ToString();
string CostName = ds_param.Tables[0].Rows[0][4].ToString(), cDepName = ds_param.Tables[0].Rows[0][5].ToString();
if (result.Tables[0].Rows[0][0].ToString() == "ok")
{
textBox1.Text += dr["BillNo"].ToString() + "扣减预算成功!";
textBox1.Text += "\r\n\r\n";
string con_AdequacyOfBudget = "select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + cdepcode + "') total " + " where total. CostCode='" + costcode + "' and year(begindate) ='" + year + "' group by year(begindate)";
DataSet ds_AdequacyOfBudget = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudget);
try
{
if (Convert.ToDouble(ds_AdequacyOfBudget.Tables[0].Rows[0][0].ToString().Trim()) > 0)
{
//修改单据状态
string SQL2 = string.Format("UPDATE sap_main SET ZRETN='{0}',ZSTS_BU='Y',FlowStatus='3' WHERE billno='{1}'", dr["BillNo"].ToString(), dr["BillNo"].ToString());
SapVoucher.SAPinsert.GetCount(SQL2);
}
else
{
string SQL2 = string.Format("UPDATE sap_main SET ZRETN='{0}',ZSTS_BU='N',FlowStatus='3' WHERE billno='{1}'", dr["BillNo"].ToString(), dr["BillNo"].ToString());
SapVoucher.SAPinsert.GetCount(SQL2);
}
}
catch
{
string SQL2 = string.Format("UPDATE sap_main SET ZRETN='{0}',ZSTS_BU='N',FlowStatus='3' WHERE billno='{1}'", dr["BillNo"].ToString(), dr["BillNo"].ToString());
SapVoucher.SAPinsert.GetCount(SQL2);
}
}
else
{
textBox1.Text += dr["BillNo"].ToString() + "扣减预算失败!";
textBox1.Text += "\r\n\r\n";
//修改当前数据的状态result.Tables[0].Rows[0][0].ToString()
string str_dept = "select cDepName,* from Sys_Dept where cDepCode='" + cdepcode + "'";
string str_class = "";
// string SQL2 = string.Format("UPDATE sap_main SET ZREASON='{0}',ZRETN='{2}' WHERE billno='{1}'", "" + cDepName + "(部门)" + CostName + "(科目)为预算受控项，请先导入预算 ", dr["BillNo"].ToString(), dr["BillNo"].ToString());
//result
string reson = result.Tables[0].Rows[0][0].ToString();
reson = ReplaceHtmlTag(reson);
// string temp = reson.Substring(reson.IndexOf('<')+1, reson.IndexOf('<') - reson.IndexOf('<'));
// reson += "为预算受控项，请先导入预算";
string SQL2 = string.Format("UPDATE sap_main SET ZREASON='{0}',ZRETN='{2}',FlowStatus='3' WHERE billno='{1}'", "", dr["BillNo"].ToString(), dr["BillNo"].ToString());
SapVoucher.SAPinsert.GetCount(SQL2);
prog.Value = prog.Value + 1;
}
#endregion
}
}
if (INBILLNO.Length > 0)
{
//查询费控单据
string INBILLNOsql1 = string.Format("SELECT * FROM sap_main WHERE BillNo IN({0})", INBILLNO);
DataSet datads1 = SapVoucher.SAPinsert.GetDataSet(INBILLNOsql1);
IRfcFunction myfu = null;
myfu = rfcrep.CreateFunction("ZFK_WERKS_CAL");//凭证函数
IRfcTable Itb_Money = myfu.GetTable("ITAB");
int ii = 0;
foreach (DataRow dr in datads1.Tables[0].Rows)
{
string sql11 = "SELECT * FROM sap_detail WHERE BillID=" + dr["BillID"].ToString();
DataSet ds = SapVoucher.SAPinsert.GetDataSet(sql11);
foreach (DataRow ds1 in ds.Tables[0].Rows)
{
string str_Dimentions = "select count(*) from BudSolutionMap b left join Sys_dept s on b.deptid =s.iDepID left join EB_CostType e on e.CostID=b.CostID where s.cDepCode='" + ds1["ZDEPT"].ToString() + "' and e.CostCode='" + ds1["ZFKACC"].ToString() + "'";
DataSet ds_Dimentions = SapVoucher.SAPinsert.GetDataSet(str_Dimentions);
string con_AdequacyOfBudget = "select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + ds1["ZDEPT"].ToString() + "') total " + " where total. CostCode='" + ds1["ZFKACC"].ToString() + "' and year(begindate) ='" + Convert.ToDateTime(ds1["ZERDAT"].ToString()).Year + "' group by year(begindate)";
DataSet ds_AdequacyOfBudget = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudget);
double Budget = -9.9;
double tag=0;
double tag1 = 0;
try
{
Budget=Convert.ToDouble(ds_AdequacyOfBudget.Tables[0].Rows[0][0].ToString().Trim()) ;
}catch
{
tag=1;
}
if (Convert.ToInt32(ds_Dimentions.Tables[0].Rows[0][0].ToString()) > 0)
{
if (tag == 1)
{
string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='N' WHERE BillDetailID='{0}'", ds1["BillDetailID"]);
SapVoucher.SAPinsert.GetCount(SQL2);
tag1 = 1;
}
else
{
if (Budget > 0)
{
string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='Y' WHERE BillDetailID='{0}'", ds1["BillDetailID"]);
SapVoucher.SAPinsert.GetCount(SQL2);
tag1 = 3;
}
else {
string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='N' WHERE BillDetailID='{0}'", ds1["BillDetailID"]);
SapVoucher.SAPinsert.GetCount(SQL2);
tag1 = 3;
}
}
}
else {
if (tag == 1)
{
string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='', ZREASON='预算维度和预算数据 不存在' WHERE BillDetailID='{0}'", ds1["BillDetailID"]);
SapVoucher.SAPinsert.GetCount(SQL2);
tag1 = 2;
}
else
{
if (Budget > 0)
{
string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='Y' WHERE BillDetailID='{0}'", ds1["BillDetailID"]);
SapVoucher.SAPinsert.GetCount(SQL2);
tag1 = 3;
}
else
{
string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='N' WHERE BillDetailID='{0}'", ds1["BillDetailID"]);
SapVoucher.SAPinsert.GetCount(SQL2);
tag1 = 3;
}
}
}
string sql11temp = "SELECT * FROM sap_detail WHERE BillDetailID=" + ds1["BillDetailID"];
DataSet dstemp = SapVoucher.SAPinsert.GetDataSet(sql11temp);
ii++;
Itb_Money.Insert();
if (Convert.ToInt32(ds.Tables[0].Rows.Count) > 1)
{
Itb_Money.CurrentRow.SetValue("ZDEPT", ds1["ZDEPT"].ToString());
Itb_Money.CurrentRow.SetValue("ZFKACC", ds1["ZFKACC"].ToString());
Itb_Money.CurrentRow.SetValue("ZBUDN", ds1["ZBUDN"].ToString());
Itb_Money.CurrentRow.SetValue("ZITEM_NO", ds1["ZITEM_NO"].ToString());
}
else
{
Itb_Money.CurrentRow.SetValue("ZDEPT", dr["ZDEPT"].ToString());
Itb_Money.CurrentRow.SetValue("ZFKACC", dr["ZFKACC"].ToString());
Itb_Money.CurrentRow.SetValue("ZBUDN", dr["ZBUDN"].ToString());
Itb_Money.CurrentRow.SetValue("ZITEM_NO", dr["ZITEM_NO"].ToString());
}
Itb_Money.CurrentRow.SetValue("WERKS", ds1["WERKS"].ToString());
Itb_Money.CurrentRow.SetValue("ZBUDAT", dr["ZDATE"].ToString());
Itb_Money.CurrentRow.SetValue("ZSTAJG", dr["ZNETPR"].ToString());
Itb_Money.CurrentRow.SetValue("ZJG", dr["ZJG"].ToString());
Itb_Money.CurrentRow.SetValue("ZQTY", dr["ZQTY"].ToString());
Itb_Money.CurrentRow.SetValue("ZSTS_TR", dr["ZSTS_TR"].ToString());
Itb_Money.CurrentRow.SetValue("ZRETN", dr["ZRETN"].ToString());
if (tag1 == 2)
{
string SQLdept = string.Format(" select * from Sys_dept where cDepCode= (select zdept from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int dept_count = SapVoucher.SAPinsert.GetDataSet(SQLdept).Tables [0].Rows .Count ;
if (dept_count > 0)
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", "");
Itb_Money.CurrentRow.SetValue("ZREASON", "预算维度和预算数据 不存在");
}
else
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", "");
Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此部门");
Itb_Money.CurrentRow.SetValue("ZRETN", "");
string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int discard_count = SapVoucher.SAPinsert.GetCount (SQLdiscard);
}
string SQLcost = string.Format(" select * from EB_CostType where CostCode= (select zfkacc from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int cost_count = SapVoucher.SAPinsert.GetCount(SQLcost);
if (cost_count > 0)
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", "");
Itb_Money.CurrentRow.SetValue("ZREASON", "预算维度和预算数据 不存在");
}
else
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", "");
Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此科目");
Itb_Money.CurrentRow.SetValue("ZRETN", "");
string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int discard_count = SapVoucher.SAPinsert.GetCount(SQLdiscard);
}
}
else
{
string SQLdept = string.Format(" select * from Sys_dept where cDepCode= (select zdept from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int dept_count = SapVoucher.SAPinsert.GetDataSet(SQLdept).Tables[0].Rows.Count; ;
if(dept_count>0)
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", dstemp.Tables [0].Rows[0]["ZSTS_BU"].ToString());
Itb_Money.CurrentRow.SetValue("ZREASON", dstemp.Tables [0].Rows [0]["ZREASON"].ToString());
}
else
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", "");
Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此部门");
Itb_Money.CurrentRow.SetValue("ZRETN", "");
string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int discard_count = SapVoucher.SAPinsert.GetCount(SQLdiscard);
}
string SQLcost = string.Format(" select * from EB_CostType where CostCode= (select zfkacc from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int cost_count = SapVoucher.SAPinsert.GetDataSet(SQLcost).Tables[0].Rows.Count; ;
if (cost_count > 0)
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", dstemp.Tables [0].Rows[0]["ZSTS_BU"].ToString());
Itb_Money.CurrentRow.SetValue("ZREASON", dstemp.Tables [0].Rows [0]["ZREASON"].ToString());
}
else
{
Itb_Money.CurrentRow.SetValue("ZSTS_BU", "");
Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此科目");
Itb_Money.CurrentRow.SetValue("ZRETN", "");
string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]);
int discard_count = SapVoucher.SAPinsert.GetCount(SQLdiscard);
}
}
Itb_Money.CurrentRow.SetValue("ZERNAM", dr["ZERNAM"].ToString());
Itb_Money.CurrentRow.SetValue("ZERDAT", dr["ZERDAT"].ToString());
Itb_Money.CurrentRow.SetValue("ZERTIM", dr["ZERTIM"].ToString());
Itb_Money.CurrentRow.SetValue("ZERPRG", dr["ZERPRG"].ToString());
Itb_Money.CurrentRow.SetValue("ZUPNAM", dr["ZUPNAM"].ToString());
Itb_Money.CurrentRow.SetValue("ZUPDAT", dr["ZUPDAT"].ToString());
Itb_Money.CurrentRow.SetValue("ZUPTIM", dr["ZUPTIM"].ToString());
Itb_Money.CurrentRow.SetValue("ZUPPRG", dr["ZUPPRG"].ToString());
textBox1.Text += dr["ZRETN"].ToString() + "操作成功！";
textBox1.Text += "\r\n\r\n";
}
}
myfu.Invoke(dest);//提交
prog.Value = prog.Maximum;
MessageBox.Show("一共返回" + ii + "条数据！");
SapVoucher.SAPinsert.GetCount(" exec Bud_RefreshAct ");
NewMethod2();
}
}
}
public static string ReplaceHtmlTag(string html, int length = 0)
{
string strText = System.Text.RegularExpressions.Regex.Replace(html, "<[^>]+>", "");
strText = System.Text.RegularExpressions.Regex.Replace(strText, "&[^;]+;", "");
if (length > 0 && strText.Length > length)
return strText.Substring(0, length);
return strText;
}
/// <summary>
/// 链接SAP的配置
/// </summary>
/// <param name="dest"></param>
/// <param name="rfcrep"></param>
private static void NewMethod(out RfcDestination dest, out RfcRepository rfcrep)
{
RfcConfigParameters rfcPar = new RfcConfigParameters();
rfcPar.Add(RfcConfigParameters.Name, "Conn");
rfcPar.Add(RfcConfigParameters.AppServerHost, "192.168.2.195");
rfcPar.Add(RfcConfigParameters.Client, "800");
rfcPar.Add(RfcConfigParameters.User, "THINKAPEUSER");
rfcPar.Add(RfcConfigParameters.Password, "Th123456");
rfcPar.Add(RfcConfigParameters.SystemNumber, "00");
rfcPar.Add(RfcConfigParameters.Language, "zh");
dest = RfcDestinationManager.GetDestination(rfcPar);
rfcrep = dest.Repository;
}
/// <summary>
/// 生成流水号
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public static string GetBillNO()
{
string result = string.Empty;
string prefix = "WFL";
string date = DateTime.Now.ToString("yyyyMMdd");
string sql = string.Format("SELECT TOP 1 BillNo FROM dbo.sapMain WHERE BillNo LIKE '%{0}%' and ISNULL(IsDiscard,0)!=1 ORDER BY BillID DESC", date);
DataSet datads = SapVoucher.SAPinsert.GetDataSet(sql);
if (datads.Tables[0].Rows.Count > 0)
{
string billno = datads.Tables[0].Rows[0][0].ToString();
billno = billno.Substring(3, billno.Length - 3);
result = prefix + (Convert.ToInt64(billno) + 1).ToString();
}
else
{
result = prefix + date + "0001";
}
return result;
}
private void button2_Click(object sender, EventArgs e)
{
RfcDestination dest;
RfcRepository rfcrep;
NewMethod(out dest, out rfcrep);
IRfcFunction myfun = null;
myfun = rfcrep.CreateFunction("ZFK_WERKS_TRAN");//凭证函数
myfun.Invoke(dest);
IRfcTable retb = myfun.GetTable("ITAB");
prog.Maximum = retb.RowCount;
prog.Value = 0;
textBox1.Text = "";
if (retb.RowCount == 0)
{
textBox1.Text = "暂时没有数据！";
}
for (int i = 0; i < retb.RowCount; i++)
{
retb.CurrentIndex = i;
prog.Value = Convert.ToInt32(prog.Value) + 1;
textBox1.Text += retb.CurrentRow.GetValue("ZITEM_NO");
textBox1.Text += retb.CurrentRow[1];
textBox1.Text += retb.CurrentRow[2];
textBox1.Text += retb.CurrentRow[3];
textBox1.Text += retb.CurrentRow[4];
textBox1.Text += retb.CurrentRow[5];
textBox1.Text += retb.CurrentRow[6];
textBox1.Text += retb.CurrentRow[7];
textBox1.Text += retb.CurrentRow[8];
textBox1.Text += retb.CurrentRow[9];
textBox1.Text += retb.CurrentRow[10];
textBox1.Text += retb.CurrentRow[11];
textBox1.Text += retb.CurrentRow[12];
textBox1.Text += retb.CurrentRow[13];
textBox1.Text += retb.CurrentRow[14];
textBox1.Text += retb.CurrentRow[15];
textBox1.Text += retb.CurrentRow[16];
textBox1.Text += retb.CurrentRow[17];
textBox1.Text += retb.CurrentRow[18];
textBox1.Text += retb.CurrentRow[19];
textBox1.Text += retb.CurrentRow[20];
textBox1.Text += retb.CurrentRow[21];
textBox1.Text += retb.CurrentRow[22];
textBox1.Text += retb.CurrentRow[23];
textBox1.Text += retb.CurrentRow[24];
textBox1.Text += "\r\n\r\n";
}
MessageBox.Show("一共" + retb.RowCount + "条数据！");
}
private void button3_Click(object sender, EventArgs e)
{
textBox1.Text = "";
this.timer1.Start();
// NewMethod2();
}
/// <summary>
/// 返回待sap查询的数据！
/// </summary>
private void NewMethod2()
{
textBox1.Text += "\r\n\r\n";
textBox1.Text += "单据生成成功，预算扣减完成，正在导入预算数据";
RfcDestination dest;
RfcRepository rfcrep;
NewMethod(out dest, out rfcrep);
IRfcFunction QUERY = null;
QUERY = rfcrep.CreateFunction("ZFK_WERKS_QUERY");//凭证函数
QUERY.Invoke(dest);
IRfcTable YSQUERY = QUERY.GetTable("ITAB");
string ZDEPT = "and DeptID in(";//部门
string ZFKACC = "and CostID in(";//科目
//string ZDEPT = string.Empty;//部门
//string ZFKACC = string.Empty;//科目
string MONTH = string.Empty;
string YEAR = string.Empty;
string SQLQUERY = @"SELECT * FROM HX_SAP_Ys WHERE 1=1 and";
for (int i = 0; i < YSQUERY.RowCount; i++)
{
YSQUERY.CurrentIndex = i;
MONTH += YSQUERY.CurrentRow[18].GetValue() + ",";
YEAR += YSQUERY.CurrentRow[15].GetValue() + ",";
ZDEPT += "(SELECT iDepID FROM dbo.Sys_Dept WHERE cDepCode='" + YSQUERY.CurrentRow.GetValue("ZDEPT") + "'),";
ZFKACC += "(SELECT CostID FROM dbo.EB_CostType WHERE CostCode='" + YSQUERY.CurrentRow.GetValue("ZFKACC") + "'),";
}
MONTH = MONTH.Substring(0, MONTH.Length - 1);
YEAR = YEAR.Substring(0, YEAR.Length - 1);
ZDEPT = ZDEPT.Substring(0, ZDEPT.Length - 1) + ")";
ZFKACC = ZFKACC.Substring(0, ZFKACC.Length - 1) + ")";
//查询预算金额
SQLQUERY += " MONTH in(" + MONTH + ")" + "and YEAR in(" + YEAR + ") " + ZDEPT + ZFKACC;
DataSet BudPlands = SapVoucher.SAPinsert.GetDataSet(SQLQUERY);
IRfcFunction myBudPlan = null;
myBudPlan = rfcrep.CreateFunction("ZFK_WERKS_FINAL");//凭证函数
IRfcTable Itb_BudPlan = myBudPlan.GetTable("ITAB");
prog.Maximum = BudPlands.Tables[0].Rows.Count;
if (BudPlands.Tables[0].Rows.Count > 0)
{
prog.Value = 0;
foreach (DataRow dr in BudPlands.Tables[0].Rows)
{
string con_AdequacyOfBudget = "select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money ,sum(ISNULL(budmoney,0)) as budmoney from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + dr["cDepCode"].ToString() + "') total " + " where iLevel='1' and total. CostCode='" + dr["CostCode"].ToString() + "' and year(begindate) ='" + dr["YEAR"].ToString() + "' group by year(begindate)";
DataSet ds_AdequacyOfBudget = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudget);
string con_AdequacyOfBudgetM = "select * from (select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money ,sum(ISNULL(budmoney,0)) as budmoney , month(begindate) as months from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + dr["cDepCode"].ToString() + "') total " + " where total. CostCode='" + dr["CostCode"].ToString() + "' and year(begindate) ='" + dr["YEAR"].ToString() + "' group by year(begindate), month(begindate) ) t where t.months='" + dr["MONTH"].ToString() + "'";
DataSet ds_AdequacyOfBudgetM = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudgetM);
textBox1.Text += "成功导入" + prog.Value + 1 + "条";
textBox1.Text += "\r\n\r\n";
prog.Value = prog.Value + 1;
Itb_BudPlan.Insert();
Itb_BudPlan.CurrentRow.SetValue("ZDEPT", dr["cDepCode"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZFKACC", dr["CostCode"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZPRD_Y", dr["YEAR"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZBUJG_Y", ds_AdequacyOfBudget.Tables[0].Rows[0]["budmoney"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZREMJG_Y", ds_AdequacyOfBudget.Tables[0].Rows[0]["money"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZPRD_M", dr["MONTH"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZBUJG_M", dr["BudMoney"].ToString());
Itb_BudPlan.CurrentRow.SetValue("ZREMJG_M", ds_AdequacyOfBudgetM.Tables [0].Rows[0]["money"].ToString());
}
myBudPlan.Invoke(dest);//提交
}
else
{
textBox1.Text += "\r\n\r\n";
textBox1.Text += "无有效预算数据！";
}
}
private void timer1_Tick(object sender, EventArgs e)
{
//每晚一点执行
int h = DateTime.Now.Hour;
if (h == 1)
{
this.timer1.Enabled = false;
NewMethod1(); NewMethod2();
this.timer1.Enabled = true;
}
}
private void HXSAP_Load(object sender, EventArgs e)
{
}
}
}
