private bool InitFdetail(string conStr)
{
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"] = dbName;
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"] = int.Parse(auditYear);
auxfdetail.Rows.Add(dr1);
}
}
}
});
}
string execSQL = " truncate table " + auxfdetail.TableName;
SqlMapperUtil.CMDExcute(execSQL, null, conStr);
SqlServerHelper.SqlBulkCopy(auxfdetail, conStr);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
return(false);
}
return(true);
}
private bool InitAccount(string conStr)
{
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)
{
MessageBox.Show("Kmdm !=Kmdm_jd");
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"] = dbName;
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)
{
MessageBox.Show(err.Message);
return(false);
}
return(true);
}
private async void PD2SqlDB(string filepath, String dbName)
{
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;
}
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);
}
}