Exemple #1
0
 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);
 }
Exemple #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);
        }
Exemple #3
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);
        }
Exemple #4
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);
        }