private bool InitProject()
{
try
{
string projectsql = " truncate table PROJECT ; INSERT PROJECT SELECT Distinct '" + _projectID + "', LEFT(XMDM, CHARINDEX('.', XMDM)),XMDM,isnull(XMMC,space(0)),NULL,XMJB,XMMX FROM XM " +
" ; update PROJECT set ProjectCode=LTRIM(rtrim(ProjectCode)),TypeCode=LTRIM(rtrim(TypeCode)) ";
SqlMapperUtil.CMDExcute(projectsql, null, conStr);
string jbsql = "update p1 set p1.UPPERCODE = p2.PROJECTCODE from ProJect p1 join ProJect p2 on p1.JB =p2.JB+1 and p1.TYPECODE = p2.TYPECODE and left(p1.PROJECTCODE,len(p2.PROJECTCODE)) = p2.PROJECTCODE and p1.jb>1 ";
SqlMapperUtil.CMDExcute(jbsql, null, conStr);
string projecttypesql = " truncate table ProjectType ; INSERT ProjectType SELECT '" + _projectID + "', FITEMID,FName FROM t_itemclass" +
" ; update PROJECTTYPE set TypeCode=LTRIM(rtrim(TypeCode)) ";
SqlMapperUtil.CMDExcute(projecttypesql, null, 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 void InitDataBase(string dbName)
{
conStr = ConfigurationManager.AppSettings["ConString"];
SqlMapperUtil.GetOpenConnection(conStr);
string exsitsDB = "select count(1) from sys.sysdatabases where name =@dbName";
int result = SqlMapperUtil.SqlWithParamsSingle <int>(exsitsDB, new { dbName = dbName });
if (result == 0)
{
string s1 = " create database [" + dbName + "]";
int ret = SqlMapperUtil.InsertUpdateOrDeleteSql(s1, null);
}
conStr = conStr.Replace("master", dbName);
var StaticStructAndFn = Path.Combine(Directory.GetCurrentDirectory(), "StaticStructAndFn.tsql");
var sqls = File.ReadAllText(StaticStructAndFn);
SqlServerHelper.ExecuteSql(sqls, conStr);
string kjqjInsert = "delete dbo.kjqj where Projectid='{0}' " +
" insert dbo.kjqj(ProjectID,CustomerCode,CustomerName,BeginDate,EndDate,KJDate)" +
" select '{0}','{1}','{1}','{2}','{3}','{4}'";
SqlServerHelper.ExecuteSql(string.Format(kjqjInsert, dbName, _projectID, _beginDate, _endDate, _auditYear), conStr);
}
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);
}
private bool InitTbDetail()
{
try
{
#region old
/*
* DataTable dtDetail = new DataTable();
* dtDetail.TableName = "TBDetail";
#region columns
* dtDetail.Columns.Add("ID");
* dtDetail.Columns.Add("ProjectID");
* dtDetail.Columns.Add("FSCode");
* dtDetail.Columns.Add("AccountCode");
* dtDetail.Columns.Add("AuxiliaryCode");
* dtDetail.Columns.Add("AccAuxName");
* dtDetail.Columns.Add("DataType", typeof(int));
* dtDetail.Columns.Add("TBGrouping");
* dtDetail.Columns.Add("TBType", typeof(int));
* dtDetail.Columns.Add("IsAccMx", typeof(int));
* dtDetail.Columns.Add("IsMx", typeof(int));
* dtDetail.Columns.Add("IsAux", typeof(int));
* dtDetail.Columns.Add("kmsx");
* dtDetail.Columns.Add("Yefx", typeof(int));
* dtDetail.Columns.Add("SourceFSCode");
* dtDetail.Columns.Add("Sqqmye", typeof(decimal));
* dtDetail.Columns.Add("Qqccgz", typeof(decimal));
* dtDetail.Columns.Add("jfje", typeof(decimal));
* dtDetail.Columns.Add("dfje", typeof(decimal));
* dtDetail.Columns.Add("CrjeJF", typeof(decimal));
* dtDetail.Columns.Add("CrjeDF", typeof(decimal));
* dtDetail.Columns.Add("AjeJF", typeof(decimal));
* dtDetail.Columns.Add("AjeDF", typeof(decimal));
* dtDetail.Columns.Add("RjeJF", typeof(decimal));
* dtDetail.Columns.Add("RjeDF", typeof(decimal));
* dtDetail.Columns.Add("TaxBase", typeof(decimal));
* dtDetail.Columns.Add("PY1", typeof(decimal));
* dtDetail.Columns.Add("jfje1", typeof(decimal));
* dtDetail.Columns.Add("dfje1", typeof(decimal));
* dtDetail.Columns.Add("jfje2", typeof(decimal));
* dtDetail.Columns.Add("dfje2", typeof(decimal));
#endregion
* string qsql = "select distinct NEWID() ID ,a.AccountCode,space(0) AS SourceFSCode," +
* " a.AccountName as AccAuxName,a.jb as TBType,0 AS IsMx, a.UpperCode TBGrouping, a.Ncye AS Sqqmye,space(0) fscode,1 yefx,0 kmsx," +
* "0 AS isAux,a.ismx AS isAccMx,0 AS DataType,Qqccgz,Hsxms,TypeCode from dbo.Account a with(nolock) ";
*
* dynamic ds = SqlMapperUtil.SqlWithParams<dynamic>(qsql, null, conStr);
* foreach (var vd in ds)
* {
* DataRow dr = dtDetail.NewRow();
* dr["ID"] = vd.ID;
* dr["ProjectID"]=dbName;
* dr["FSCode"] = vd.fscode;
* dr["AccountCode"] = vd.AccountCode;
* dr["AuxiliaryCode"] = vd.TypeCode;
* dr["AccAuxName"] = vd.AccAuxName;
* dr["DataType"] = vd.DataType;
* dr["TBGrouping"] = vd.TBGrouping;
* dr["TBType"] = vd.TBType;
* dr["IsAccMx"] = vd.isAccMx;
* dr["IsMx"] = vd.IsMx;
* dr["IsAux"] = 0;
* dr["kmsx"] = vd.kmsx;
* dr["Yefx"] = vd.yefx;
* dr["SourceFSCode"] = vd.SourceFSCode;
* dr["Sqqmye"] = vd.Sqqmye==null?0M:vd.Sqqmye;
* dr["Qqccgz"] = vd.Qqccgz;
* dr["jfje"] = 0M;
* dr["dfje"] = 0M;
* dr["CrjeJF"] = 0M;
* dr["CrjeDF"] = 0M;
* dr["AjeJF"] = 0M;
* dr["AjeDF"] = 0M;
* dr["RjeJF"] = 0M;
* dr["RjeDF"] = 0M;
* dr["TaxBase"] = 0M;
* dr["PY1"] = 0M;
* dr["jfje1"] = 0M;
* dr["dfje1"] = 0M;
* dr["jfje2"] = 0M;
* dr["dfje2"] = 0M;
* dtDetail.Rows.Add(dr);
*
* }
* string execSQL = " truncate table " + dtDetail.TableName;
* SqlMapperUtil.CMDExcute(execSQL, null, conStr);
* SqlServerHelper.SqlBulkCopy(dtDetail, conStr); */
#endregion
var p = new DynamicParameters();
p.Add("@ProjectID", _projectID);
SqlMapperUtil.InsertUpdateOrDeleteStoredProc("InitTbAccTable", p, conStr);
}
catch (Exception err)
{
return(false);
}
return(true);
}
