예제 #1
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);
        }
예제 #2
0
        private bool PD2SqlDB(string filepath, String dbName)
        {
            bool   bRet     = false;
            string filename = Path.GetFileNameWithoutExtension(filepath);

            try
            {
                var       _ParadoxTable = new ParadoxReader.ParadoxTable(Path.GetDirectoryName(filepath), filename);
                var       columns       = _ParadoxTable.FieldNames;
                var       fieldtypes    = _ParadoxTable.FieldTypes;
                DataTable dt            = new DataTable();
                dt.TableName = Path.GetFileNameWithoutExtension(filepath);//_ParadoxTable.TableName;
                if (columns.Length == 0 || _ParadoxTable.RecordCount == 0)
                {
                    return(bRet);
                }

                string tableName = dt.TableName;
                string typeName  = "[dbo].[" + dt.TableName + "Type]";
                string procName  = "usp_insert" + dt.TableName;

                StringBuilder strSpt = new StringBuilder(string.Format("IF object_id('{0}') IS NOT NULL  drop table  {0}", tableName));
                strSpt.AppendLine(" create    table   " + tableName + "(" + Environment.NewLine);

                StringBuilder strTypetv = new StringBuilder(string.Format("IF type_id('{0}') IS NOT NULL  drop TYPE  " + typeName, typeName));
                strTypetv.AppendLine(" create    TYPE  " + typeName + " as TABLE(" + Environment.NewLine);

                string preProc = " IF EXISTS (SELECT * FROM dbo.sysobjects WHERE type = 'P' AND name = '" + procName + "')   " +
                                 " BEGIN       DROP  Procedure " + procName + "   END  ";
                string createProc = " CREATE PROCEDURE " + procName + "    (@tvpNewValues " + typeName + " READONLY)" +
                                    "as  insert into " + tableName + "   select *   from  @tvpNewValues  ";

                for (int i = 0; i < columns.Length; i++)
                {
                    string     fieldName = columns[i];
                    DataColumn dc        = new DataColumn(fieldName);
                    ParadoxReader.ParadoxFieldTypes fieldType = fieldtypes[i].fType;
                    switch (fieldType)
                    {
                    case ParadoxReader.ParadoxFieldTypes.BCD:
                    case ParadoxReader.ParadoxFieldTypes.Number:
                    case ParadoxReader.ParadoxFieldTypes.Currency:
                    case ParadoxReader.ParadoxFieldTypes.Logical:
                    case ParadoxReader.ParadoxFieldTypes.Short:
                        strSpt.AppendLine(fieldName + " " + "decimal(19,3) null DEFAULT 0,");
                        strTypetv.AppendLine(fieldName + " " + "decimal(19,3) null DEFAULT 0,");
                        dc.DataType = typeof(System.Decimal);
                        break;

                    default:
                        strSpt.AppendLine(fieldName + " " + "nvarchar(1000),");
                        strTypetv.AppendLine(fieldName + " " + "nvarchar(1000),");
                        dc.DataType = typeof(System.String);
                        break;
                    }
                    dt.Columns.Add(dc);
                }
                string dtstring    = strSpt.ToString().Substring(0, strSpt.Length - 3) + ")   " + strTypetv.ToString().Substring(0, strTypetv.Length - 3) + ")";
                string createDTSql = preProc + dtstring + " GO " + createProc;
                if (!string.IsNullOrEmpty(createDTSql))
                {
                    SqlServerHelper.ExecuteSql(createDTSql, conStr);
                }

                int idx = 0;
                foreach (var rec in _ParadoxTable.Enumerate())
                {
                    if (idx % 1000 == 0)
                    {
                        SqlServerHelper.ExecuteProcWithStruct(procName, conStr, typeName, dt);
                        dt.Rows.Clear();
                    }
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < _ParadoxTable.FieldCount; i++)
                    {
                        object OV = rec.DataValues[i];
                        if (!DBNull.Value.Equals(OV) && OV != null)
                        {
                            dr[_ParadoxTable.FieldNames[i]] = OV;
                        }
                    }
                    dt.Rows.Add(dr);
                    idx++;
                }

                _ParadoxTable.Dispose();
                _ParadoxTable = null;
                SqlServerHelper.ExecuteProcWithStruct(procName, conStr, typeName, dt);
                dt.Dispose();
                dt = null;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("转换数据失败", filename), ex);
            }
            return(true);
        }
예제 #3
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);
        }