private bool InitTBAux()
{
try
{
DataTable auxTable = new DataTable();
auxTable.TableName = "TBAux";
auxTable.Columns.Add("ProjectID");
auxTable.Columns.Add("AccountCode");
auxTable.Columns.Add("AuxiliaryCode");
auxTable.Columns.Add("AuxiliaryName");
auxTable.Columns.Add("FSCode");
auxTable.Columns.Add("kmsx");
auxTable.Columns.Add("YEFX", typeof(int));
auxTable.Columns.Add("TBGrouping");
auxTable.Columns.Add("Sqqmye", typeof(decimal));
auxTable.Columns.Add("Qqccgz", typeof(decimal));
auxTable.Columns.Add("jfje", typeof(decimal));
auxTable.Columns.Add("dfje", typeof(decimal));
auxTable.Columns.Add("qmye", typeof(decimal));
string qsql = "select distinct idet.accountcode,idet.AuxiliaryCode, isnull(xm.xmmc,space(0)) as AuxiliaryName,xmye.ncye as Sqqmye from AuxiliaryFDetail idet with(nolock) join xm xm on LTRIM(rtrim(xm.xmdm)) COLLATE Chinese_PRC_CS_AS_KS_WS=idet.AuxiliaryCode COLLATE Chinese_PRC_CS_AS_KS_WS join xmye xmye on idet.accountcode COLLATE Chinese_PRC_CS_AS_KS_WS = ltrim(rtrim(xmye.kmdm)) COLLATE Chinese_PRC_CS_AS_KS_WS and idet.AuxiliaryCode COLLATE Chinese_PRC_CS_AS_KS_WS = LTRIM(rtrim(xmye.xmdm)) COLLATE Chinese_PRC_CS_AS_KS_WS ";
dynamic ds = SqlMapperUtil.SqlWithParams <dynamic>(qsql, null, conStr);
foreach (var vd in ds)
{
DataRow dr = auxTable.NewRow();
dr["ProjectID"] = _projectID;
dr["AccountCode"] = vd.accountcode;
dr["AuxiliaryCode"] = vd.AuxiliaryCode;
dr["AuxiliaryName"] = vd.AuxiliaryName;
dr["FSCode"] = string.Empty;
dr["kmsx"] = 0;
dr["YEFX"] = 0;
dr["TBGrouping"] = vd.accountcode;
dr["Sqqmye"] = vd.Sqqmye == null ? 0M : vd.Sqqmye;
dr["Qqccgz"] = 0M;
dr["jfje"] = 0M;
dr["dfje"] = 0M;
dr["qmye"] = 0M;
auxTable.Rows.Add(dr);
}
if (auxTable.Rows.Count == 0)
{
return(false);
}
else
{
string execSQL = " truncate table " + auxTable.TableName;
SqlMapperUtil.CMDExcute(execSQL, null, conStr);
SqlServerHelper.SqlBulkCopy(auxTable, conStr);
}
}
catch (Exception err)
{
return(false);
}
return(true);
}
private bool InitVoucher()
{
try
{
string jzpzSQL = " truncate table TBVoucher" +
" insert TBVoucher(VoucherID,Clientid,ProjectID,IncNo,Date,Period,Pzh,Djh,AccountCode,Zy,Jfje,Dfje,jfsl,fsje,jd,dfsl, ZDR,dfkm,Wbdm,Wbje,Hl,fllx,FDetailID) ";
jzpzSQL += "select newid() as VoucherID,'" + _clientid + "' as clientID, '" + _projectID + "' as ProjectID,IncNo, Pz_Date as [date],Kjqj as Period ,Pzh,fjzs as Djh,Kmdm as AccountCode ," +
" zy,case when jd = '借' then rmb else 0 end as jfje, " +
" case when jd = '贷' then rmb else 0 end as dfje, " +
" case when jd = '借' then isnull(sl,0) else 0 end as jfsl, " +
" case when jd = '借' and rmb>0 then 1 else -1 end *(rmb) as fsje,"+
" case when jd = '借' and rmb>0 then 1 else -1 end as jd, "+
" case when jd = '贷' then isnull(sl,0) else 0 end as dfsl, sr as ZDR, DFKM,Wbdm,Wbje,isnull(Hl,0) as Hl, 1 as fllx, FDetailID from jzpz ";
SqlMapperUtil.CMDExcute(jzpzSQL, null, conStr);
string expzk = " select Pzk_TableName from pzk where Pzk_TableName!='jzpz' and Pzk_TableName like 'jzpz%' ";
dynamic ds = SqlMapperUtil.SqlWithParams <dynamic>(expzk, null, conStr);
string pzkname = "jzpz";
foreach (var d in ds)
{
jzpzSQL = jzpzSQL.Replace("from " + pzkname, "from " + d.Pzk_TableName).Replace("truncate table TBVoucher", "");
SqlMapperUtil.CMDExcute(jzpzSQL, null, conStr);
pzkname = d.Pzk_TableName;
}
string incNoSql = ";with t1 as( select ROW_NUMBER() OVER (ORDER BY period) AS RowNumber, period,pzh from TBVoucher group by period,pzh having COUNT(period)>1 AND count(pzh)>1)"+
" update vv set vv.IncNo = v.RowNumber from TBVoucher vv , t1 v where vv.period = v.period and vv.pzh = v.pzh; ";
SqlMapperUtil.CMDExcute(incNoSql, null, conStr);
string updatesql = " update v set v.fllx = case when a.Syjz = 0 then 1 else a.Syjz end from dbo.tbvoucher v join ACCOUNT a on a.AccountCode = v.AccountCode ";
SqlMapperUtil.CMDExcute(updatesql, null, conStr);
updatesql = "update z set z.HashCode =HASHBYTES('SHA1', (select z.* FOR XML RAW, BINARY BASE64)) from TBVoucher z";
SqlMapperUtil.CMDExcute(updatesql, null, conStr);
}
catch (Exception err)
{
throw err;
}
return(true);
}
private bool InitAccount()
{
try
{
string sql = "select 1 as be from km where Kmdm !=Kmdm_Jd";
object ret = DapperHelper <int> .Create(conStr).ExecuteScalar(sql, null);
if (ret != null)
{
return(false);
}
DataTable accountTable = new DataTable();
accountTable.TableName = "ACCOUNT";
accountTable.Columns.Add("ProjectID");
accountTable.Columns.Add("AccountCode");
accountTable.Columns.Add("UpperCode");
accountTable.Columns.Add("AccountName");
//accountTable.Columns.Add("Attribute",typeof(int));
accountTable.Columns.Add("Jd", typeof(int));
accountTable.Columns.Add("Hsxms", typeof(int));
accountTable.Columns.Add("TypeCode");
accountTable.Columns.Add("Jb", typeof(int));
accountTable.Columns.Add("IsMx", typeof(int));
accountTable.Columns.Add("Ncye", typeof(decimal));
accountTable.Columns.Add("Qqccgz", typeof(decimal));
accountTable.Columns.Add("Jfje", typeof(decimal));
accountTable.Columns.Add("Dfje", typeof(decimal));
accountTable.Columns.Add("Ncsl", typeof(int));
accountTable.Columns.Add("Syjz", typeof(int));
//按级别排序
string qsql = "SELECT km.kmdm,km.kmmc,Xmhs,Kmjb,IsMx,Ncye,Jfje1,Dfje1,Ncsl FROM KM left join kmye on km.kmdm = kmye.kmdm order by Kmjb";
dynamic ds = SqlMapperUtil.SqlWithParams <dynamic>(qsql, null, conStr);
foreach (var vd in ds)
{
DataRow dr = accountTable.NewRow();
dr["ProjectID"] = _projectID;
dr["AccountCode"] = vd.kmdm;
dr["UpperCode"] = "";
dr["AccountName"] = vd.kmmc;
//dr["Attribute"] = vd.KM_TYPE == "损益" ? 1 : 0;
dr["Jd"] = 1;//default(1)
dr["Hsxms"] = 0;
dr["TypeCode"] = "";
dr["Jb"] = vd.Kmjb;
dr["IsMx"] = vd.IsMx == null ? 0 : 1;
dr["Ncye"] = vd.Ncye == null ? 0M : vd.Ncye;
dr["Qqccgz"] = 0M;
dr["Jfje"] = vd.Jfje1 == null ? 0M : vd.Jfje1;
dr["Dfje"] = vd.Dfje1 == null ? 0M : vd.Dfje1;
dr["Ncsl"] = vd.Ncsl == null ? 0M : vd.Ncsl;
dr["Syjz"] = 0;
accountTable.Rows.Add(dr);
}
BuildUpperCode(accountTable, conStr);
BuildTypeCode(accountTable, conStr);
string execSQL = " truncate table ACCOUNT ";
SqlMapperUtil.CMDExcute(execSQL, null, conStr);
SqlServerHelper.SqlBulkCopy(accountTable, conStr);
}
catch (Exception err)
{
return(false);
}
return(true);
}
private bool InitFdetail()
{
try
{
DataTable auxfdetail = new DataTable();
auxfdetail.TableName = "AuxiliaryFDetail";
auxfdetail.Columns.Add("projectid");
auxfdetail.Columns.Add("Accountcode");
auxfdetail.Columns.Add("AuxiliaryCode");
auxfdetail.Columns.Add("Ncye", typeof(decimal));
auxfdetail.Columns.Add("Jfje1", typeof(decimal));
auxfdetail.Columns.Add("Dfje1", typeof(decimal));
auxfdetail.Columns.Add("FDetailID", typeof(int));
auxfdetail.Columns.Add("DataType", typeof(int));
auxfdetail.Columns.Add("DataYear", typeof(int));
string itemclass = "select * from t_itemclass";
var tab_ic = SqlMapperUtil.SqlWithParams <dynamic>(itemclass, null, conStr);
List <string> xmField = new List <string>();
foreach (var iid in tab_ic)
{
xmField.Add("F" + iid.FItemClassID);
}
string sql1 = "select * from t_itemdetail t join t_fzye f on t.FDetailID = f.FDetailID ";
var d1 = SqlMapperUtil.SqlWithParams <dynamic>(sql1, null, conStr);
foreach (var d in d1)
{
Array.ForEach(xmField.ToArray(), f =>
{
foreach (var xv in d)
{
if (xv.Key == f)
{
if (!string.IsNullOrWhiteSpace(xv.Value))
{
DataRow dr1 = auxfdetail.NewRow();
dr1["projectid"] = _projectID;
dr1["Accountcode"] = d.Kmdm;
dr1["AuxiliaryCode"] = xv.Value;
dr1["Ncye"] = d.Ncye;
dr1["Jfje1"] = d.Jfje1;
dr1["Dfje1"] = d.Dfje1;
dr1["FDetailID"] = d.FDetailID;
dr1["DataType"] = 0;
dr1["DataYear"] = _auditYear;
auxfdetail.Rows.Add(dr1);
}
}
}
});
}
string execSQL = " truncate table " + auxfdetail.TableName;
SqlMapperUtil.CMDExcute(execSQL, null, conStr);
SqlServerHelper.SqlBulkCopy(auxfdetail, conStr);
}
catch (Exception err)
{
return(false);
}
return(true);
}