大致思路：（需要保证集团内一级科目一致）
1.变更各公司科目的FMASTERID，FMASTERID为分配科目的ID。
2.变更各公司科目的分组ID：FGROUPID。
3.变更各公司科目的科目表ID：FACCTTBLID。
更新数据库sql语句：
1.update T_BD_ACCOUNT set FMASTERID={0} where FACCTID={1}。
2.update T_BD_ACCOUNT set FGROUPID=(select top 1 FACCTTYPEID from T_BD_ACCOUNTTYPE_L where FNAME=(select FNAME from T_BD_ACCOUNTTYPE_L where FACCTTYPEID='{0}') order by FACCTTYPEID ASC) where FACCTID='{1}'。
3.update T_BD_ACCOUNT set FACCTTBLID='2001' where FACCTID='{0}'。
示例代码：
private void button2_Click(object sender, EventArgs e)
{
//筛选一级科目
string sql1 = "select FACCTTBLID,FMASTERID,FGROUPID,FParentID,FCreateOrgId,FUseOrgId,FNUMBER,FACCTID from T_BD_ACCOUNT where LEN(FNUMBER)=4 and FNUMBER !='1504' and FDOCUMENTSTATUS='C' and FUSEORGID='" + this.textBox2.Text + "' and FACCTTBLID='" + this.textBox4.Text+"'";
DataSet ds1 = DBHelper.ExecuteDataset(sql1);
DataTable dt1 = ds1.Tables[0];
//筛选二级级科目
string sql2 = "select FACCTTBLID,FMASTERID,FGROUPID,FParentID,FCreateOrgId,FUseOrgId,FNUMBER,FACCTID from T_BD_ACCOUNT where LEN(FNUMBER)>4 and FUSEORGID='" + this.textBox2.Text + "'";
DataSet ds2 = DBHelper.ExecuteDataset(sql2);
DataTable dt2 = ds2.Tables[0];
//筛选集团科目
string sqlJT = "select FACCTTBLID,FMASTERID,FGROUPID,FParentID,FCreateOrgId,FUseOrgId,FNUMBER,FACCTID from T_BD_ACCOUNT where FUSEORGID='1'";
DataSet dsJT = DBHelper.ExecuteDataset(sqlJT);
DataTable dtJT = dsJT.Tables[0];
//修改一级科目信息
for (int i = 0; i < dt1.Rows.Count; i++)
{
DataRow dr = dt1.Rows[i];
string acctnumber = dr["FNUMBER"].ToString();
string Acctid = dr["FACCTID"].ToString();
String FGROUPID = dr["FGROUPID"].ToString();
DataRow[] drs = dtJT.Select("FNUMBER = '" + acctnumber + "'");
if (drs.Length > 0)
{
String FMASTERID = drs[0]["FMASTERID"].ToString();
String JTAcctId = drs[0]["FACCTID"].ToString();
String updateFMasterID =String.Format("update T_BD_ACCOUNT set FMASTERID={0} where FACCTID={1}", FMASTERID, Acctid);
DBHelper.ExecuteNonQuery(updateFMasterID);
String updateFGroupID = String.Format("update T_BD_ACCOUNT set FGROUPID=(select top 1 FACCTTYPEID from T_BD_ACCOUNTTYPE_L where FNAME=(select FNAME from T_BD_ACCOUNTTYPE_L where FACCTTYPEID='{0}') order by FACCTTYPEID ASC) where FACCTID='{1}'", FGROUPID, Acctid);
DBHelper.ExecuteNonQuery(updateFGroupID);
String updateAcctTblID = String.Format("update T_BD_ACCOUNT set FACCTTBLID='2001' where FACCTID='{0}'", Acctid);
DBHelper.ExecuteNonQuery(updateAcctTblID);
if (!isExistDistrbute(Acctid))
{
String insertIntoAcctDsitr = insertIntoAcctDistrbute(JTAcctId, this.textBox2.Text, Acctid);
DBHelper.ExecuteNonQuery(insertIntoAcctDsitr);
}
}
}
//修改二级科目信息
for (int i = 0; i < dt2.Rows.Count; i++)
{
DataRow dr = dt2.Rows[i];
string acctnumber = dr["FNUMBER"].ToString();
string Acctid = dr["FACCTID"].ToString();
String FGROUPID = dr["FGROUPID"].ToString();
String updateFGroupID = String.Format("update T_BD_ACCOUNT set FGROUPID=(select top 1 FACCTTYPEID from T_BD_ACCOUNTTYPE_L where FNAME=(select FNAME from T_BD_ACCOUNTTYPE_L where FACCTTYPEID='{0}') order by FACCTTYPEID ASC) where FACCTID='{1}'", FGROUPID, Acctid);
DBHelper.ExecuteNonQuery(updateFGroupID);
String updateAcctTblID = String.Format("update T_BD_ACCOUNT set FACCTTBLID='2001' where FACCTID='{0}'", Acctid);
DBHelper.ExecuteNonQuery(updateAcctTblID);
}
//using (StreamWriter fs = new StreamWriter(@"D:\KingdeeCloudLog\DBDSyncLog.txt", true)) { fs.WriteLine("变更凭证字:"); }
//string pzzidsql = "select FNumber,FVCHGROUPID from T_BD_VOUCHERGROUP where fnumber='" + this.textBox3.Text + "'";
//DataSet pzzds = DBHelper.ExecuteDataset(pzzidsql);
//if (pzzds.Tables[0].Rows.Count > 0)
//{
// string pzzid = pzzds.Tables[0].Rows[0]["FVCHGROUPID"].ToString();
// string updateVoucherGroup = "update T_GL_VOUCHER set FVOUCHERGROUPID='1' where FVOUCHERGROUPID = '" + pzzid + "'";
// using (StreamWriter fs = new StreamWriter(@"D:\KingdeeCloudLog\DBDSyncLog.txt", true)) { fs.WriteLine(updateVoucherGroup); }
// DBHelper.ExecuteNonQuery(updateVoucherGroup);
//}
//变更账簿信息
using (StreamWriter fs = new StreamWriter(@"D:\KingdeeCloudLog\DBDSyncLog.txt", true)) { fs.WriteLine("变更账簿信息:"); }
string updateaccountbook = "update T_BD_ACCOUNTBOOK set Faccttableid='2001',FDEFAULTVOUCHERTYPE=1 where FCREATEORGID='" + this.textBox2.Text + "'";
using (StreamWriter fs = new StreamWriter(@"D:\KingdeeCloudLog\DBDSyncLog.txt", true)) { fs.WriteLine(updateaccountbook); }
DBHelper.ExecuteNonQuery(updateaccountbook);
}
private String insertIntoAcctDistrbute(String JTAcctId,String useOrgId,String aimAcctId)
{
String sql = String.Format(@"INSERT INTO [T_BD_ACCOUNTDISTRIBUTE]
(
[FACCTID]
,[FDISTRIBUTEORGID]
,[FUSEORGID]
,[FDISTRIBUTEORID]
,[FDISTRIBUTEDATE]
,[FISADDCHILD]
,[FFORBIDSTATUS]
,[FFORBIDDERID]
,[FFORBIDORGID]
,[FFORBIDDATE]
,[FISREDISTRIBUTE]
,[FMASTERID]
,[FAIMACCTID])
VALUES
('{0}'
,'1'
,'{1}'
,'100007'
,'{3}'
,'1'
,'A'
,'0'
,'0'
,null
,'0'
,'{0}'
,'{2}')", JTAcctId, useOrgId, aimAcctId,DateTime.Now);
return sql;
}
private Boolean isExistDistrbute(String aimAcctId)
{
String sql = String.Format("select * from T_BD_ACCOUNTDISTRIBUTE where FAIMACCTID='{0}'", aimAcctId);
DataSet ds= DBHelper.ExecuteDataset(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
推荐阅读