Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }