/// <summary>
/// 上传
/// </summary>
/// <param name="server"></param>
/// <param name="client"></param>
/// <param name="tableSynch"></param>
/// <returns></returns>
private int Upload(OracleDapperDber server, OracleDapperDber client, TableSynch tableSynch)
{
int synchCount = 0;
DataTable dtClient = client.ExecuteDataTable(OracleSqlBuilder.BuildGetNeedSynchTableSQL(tableSynch.TableName, tableSynch.SynchField));
foreach (DataRow dr in dtClient.Rows)
{
string execSql = string.Empty;
if (server.ExecuteScalar <int>(OracleSqlBuilder.BuildHasRecordSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString())) == 0)
{
execSql = OracleSqlBuilder.BuildInsertSQL(tableSynch.TableName, tableSynch.SynchField, dr);
}
else
{
execSql = OracleSqlBuilder.BuildUpdateSQL(tableSynch.TableName, tableSynch.SynchField, tableSynch.PrimaryKey, dr);
}
if (server.Execute(execSql) > 0)
{
synchCount += 1;
client.Execute(OracleSqlBuilder.BuildUpdateSynchFieldSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString(), tableSynch.SynchField));
}
}
return(synchCount);
}
/// <summary>
/// 下达
/// </summary>
/// <param name="server"></param>
/// <param name="client"></param>
/// <param name="tableSynch"></param>
/// <returns></returns>
private int Download(OracleDapperDber server, OracleDapperDber client, TableSynch tableSynch)
{
int synchCount = 0;
DataTable dtServer = server.ExecuteDataTable(OracleSqlBuilder.BuildGetNeedSynchTableSQL(tableSynch.TableName, tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier));
foreach (DataRow dr in dtServer.Rows)
{
string execSql = string.Empty;
if (client.ExecuteDataTable(OracleSqlBuilder.BuildHasRecordSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString())).Rows[0][0].ToString() == "0")
{
execSql = OracleSqlBuilder.BuildInsertSQL(tableSynch.TableName, tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier, dr);
}
else
{
execSql = OracleSqlBuilder.BuildUpdateSQL(tableSynch.TableName, tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier, tableSynch.PrimaryKey, dr);
}
if (client.Execute(execSql) > 0)
{
synchCount += 1;
server.Execute(OracleSqlBuilder.BuildUpdateSynchFieldSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString(), tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier));
}
}
return(synchCount);
}
void AnalyticData(SendMessage data)
{
int syncCount = 0;
bool IsMatch = false;
//MachineCodes item = ServerConfiguration.Instance.Details.Where(a => a.UpLoadIdentifer == data.UpLoadIdentifier).FirstOrDefault();
foreach (MachineCodes item in ServerConfiguration.Instance.Details.Where(a => a.UpLoadIdentifer == data.UpLoadIdentifier))
{
if (data.UpLoadIdentifier == item.UpLoadIdentifer)
{
IsMatch = true;
// 未设置主键名则跳过
if (string.IsNullOrEmpty(item.PrimarKeys))
{
grabPerformer_OutputInfo(string.Format("{0} 提取未执行，原因：未设置主键(PrimaryKeys)参数", item.UpLoadIdentifer));
break;
}
OracleDapperDber selfDber = new OracleDapperDber(ServerConfiguration.Instance.SelfConnStr);
using (OracleConnection connection = selfDber.CreateConnection() as OracleConnection)
{
if (data.DataColumns != null)
{
// 在数据中创建表
if (connection.ExecuteScalar <int>(CMCS.CommonADGS.Core.OracleSqlBuilder.BuildHasTableSQL(item.DataTableName)) == 0)
{
connection.Execute(CMCS.CommonADGS.Core.OracleSqlBuilder.BuildTableSQL(item.DataTableName, data.DataColumns));
}
}
else if (data.DataRows != null)
{
string execSql = string.Empty;
// 生成主键值
string primaryKeyValue = item.UpLoadIdentifer + "-" + CMCS.CommonADGS.Core.OracleSqlBuilder.BuildPrimaryKeyValue(item.PrimarKeys, data.DataRows);
if (connection.ExecuteScalar <int>(CMCS.CommonADGS.Core.OracleSqlBuilder.BuildHasRecordSQL(item.DataTableName, primaryKeyValue)) == 0)
{
execSql = CMCS.CommonADGS.Core.OracleSqlBuilder.BuildInsertSQL(item.DataTableName, primaryKeyValue, item.UpLoadIdentifer, data.DataRows);
}
else
{
execSql = CMCS.CommonADGS.Core.OracleSqlBuilder.BuildUpdateSQL(item.DataTableName, primaryKeyValue, item.UpLoadIdentifer, data.DataRows);
}
syncCount += connection.Execute(execSql);
}
}
}
}
if (!IsMatch)
{
grabPerformer_OutputInfo(string.Format("接收到:{0}数据,但未找到对应配置", data.UpLoadIdentifier));
}
if (syncCount > 0)
{
grabPerformer_OutputInfo(string.Format("成功接收:{0}一条数据", data.UpLoadIdentifier));
}
}
/// <summary>
/// 提取数据
/// </summary>
private void Grab()
{
OracleDapperDber selfDber = new OracleDapperDber(this._ADGSAppConfig.SelfConnStr);
using (OracleConnection connection = selfDber.CreateConnection() as OracleConnection)
{
foreach (AssayGraber assayGraber in this._ADGSAppConfig.AssayGrabers)
{
try
{
if (!assayGraber.Enabled)
{
continue;
}
// 未设置主键名则跳过
if (string.IsNullOrEmpty(assayGraber.PrimaryKeys))
{
OutputInfoMethod(string.Format("{0} 提取未执行，原因：未设置主键(PrimaryKeys)参数", assayGraber.MachineCode));
continue;
}
DataTable dtlAssay = assayGraber.ExecuteGrab();
// 在数据中创建表
if (connection.ExecuteScalar <int>(OracleSqlBuilder.BuildHasTableSQL(assayGraber.TableName)) == 0)
{
connection.Execute(OracleSqlBuilder.BuildTableSQL(assayGraber.TableName, dtlAssay));
}
int syncCount = 0;
foreach (DataRow drAssay in dtlAssay.Rows)
{
string execSql = string.Empty;
// 生成主键值
string primaryKeyValue = assayGraber.MachineCode + "-" + OracleSqlBuilder.BuildPrimaryKeyValue(assayGraber.PrimaryKeys, drAssay);
if (connection.ExecuteScalar <int>(OracleSqlBuilder.BuildHasRecordSQL(assayGraber.TableName, primaryKeyValue)) == 0)
{
execSql = OracleSqlBuilder.BuildInsertSQL(assayGraber.TableName, primaryKeyValue, assayGraber.MachineCode, drAssay);
}
else
{
execSql = OracleSqlBuilder.BuildUpdateSQL(assayGraber.TableName, primaryKeyValue, assayGraber.MachineCode, drAssay);
}
syncCount += connection.Execute(execSql);
}
OutputInfoMethod(string.Format("{0} 本次提取 {1} 条", assayGraber.MachineCode, syncCount));
}
catch (Exception ex)
{
OutputErrorMethod(string.Format("{0} 提取失败", assayGraber.MachineCode), ex);
}
}
}
}
/// <summary>
/// 生成列的默认值的SQL
/// </summary>
/// <param name="columns"></param>
/// <param name="serverDber"></param>
/// <returns></returns>
public static string BuildColumnDefaultSQL(IList <DataColumn> columns, OracleDapperDber serverDber)
{
StringBuilder sql = new StringBuilder();
foreach (DataColumn item in columns)
{
sql.AppendFormat(Environment.NewLine + "comment on column {0}.{1} is '{2}'", item.Table.TableName, item.ColumnName, GetColumnComment(item.Table.TableName, item.ColumnName, serverDber));
}
return(sql.ToString());
}
/// <summary>
/// 生成添加列的SQL
/// </summary>
/// <param name="columns"></param>
/// <returns></returns>
public static string BuildAddColumnSQL(IList <DataColumn> columns, OracleDapperDber serverDber)
{
StringBuilder sql = new StringBuilder();
foreach (DataColumn item in columns)
{
sql.AppendFormat("alter table {0} add {1} {2} {3}", item.Table.TableName, item.ColumnName, SystemTypeToOracleType(item.DataType, item.MaxLength), DefaultValueChange(GetColumnDefaultValue(item.Table.TableName, item.ColumnName, serverDber)));
}
return(sql.ToString());
}
private DcDbers()
{
BeltSampler_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("皮带采样机接口连接字符串"));
AutoCupboard_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("智能存样柜接口连接字符串"));
PneumaticTransfer_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("气动传输接口连接字符串"));
CarJXSampler_Dber = new OracleDapperDber(commonDAO.GetCommonAppletConfigString("汽车机械采样机接口连接字符串"));
TurnCarWeighterMutualDber = new OracleDapperDber(string.Format(commonDAO.GetCommonAppletConfigString("翻车衡数据库连接字符串"), DateTime.Now.Year));
}
private DcDbers()
{
BeltSampler_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("皮带采样机接口连接字符串"));
AutoCupboard_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("智能存样柜接口连接字符串"));
PneumaticTransfer_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("气动传输接口连接字符串"));
WeightBridger_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("轨道衡数据库连接字符串"));
CarJXSampler_Dber1 = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("#1汽车机械采样机接口连接字符串"));
CarJXSampler_Dber2 = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("#2汽车机械采样机接口连接字符串"));
WeightBridger_Dber1 = new AccessDapperDber(string.Format(commonDAO.GetAppletConfigString("轨道衡数据库连接字符串1"), DateTime.Now.Year));
TruckWeight_Dber = new OracleDapperDber(commonDAO.GetAppletConfigString("汽车衡数据库连接字符串"));
}
public void TransferAssayQulity(Action <string, eOutputType> output)
{
string interfaceUrl = commonDAO.GetAppletConfigString("数据同步智仁接口", "接口地址");
if (string.IsNullOrWhiteSpace(interfaceUrl))
{
output("未在【小程序参数配置】模块中添加配置“接口地址”", eOutputType.Error);
return;
}
OracleDapperDber thirdDber = new OracleDapperDber(interfaceUrl);
int intervalValue = 7;
string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔（天）");
if (!string.IsNullOrWhiteSpace(configValue))
{
Int32.TryParse(configValue, out intervalValue);
}
DateTime startTime = DateTime.Now.AddDays(-intervalValue);
string searchSql = string.Format("select * from V_JK_HUAY where assaycode like 'Z%' and RIQ>=to_date('{0}','yyyy-MM-dd hh24:mi:ss')", startTime.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = thirdDber.ExecuteDataTable(searchSql);
if (dt == null || dt.Rows.Count <= 0)
{
return;
}
foreach (DataRow item in dt.Rows)
{
string assaycode = string.Empty;
DataTable assaydata = SelfDber.ExecuteDataTable(string.Format("select b.assaycode from cmcstbmake a inner join cmcstbassay b on a.id=b.makeid where a.makecode='{0}'", item["ASSAYCODE"]));
if (assaydata != null && assaydata.Rows.Count > 0)
{
assaycode = assaydata.Rows[0][0].ToString();
}
if (string.IsNullOrEmpty(assaycode))
{
continue;
}
string assaySql = string.Format("update cmcstbassay set ASSAYDATE=to_date('{0}','yyyy-MM-dd hh24:mi:ss'),ASSAYPLE='{1}' where ASSAYCODE='{2}'", item["RIQ"], item["HUYY"], assaycode);
string qualitySql = string.Format(@"update fultbfuelquality t set t.mt={0},t.mad={1},t.ad={2},t.aar={3},t.aad={4},t.vad={5},t.vdaf={6},t.std={7},t.stad={8},t.had={9},t.hdaf={10},t.qnetarmj={11},t.qbad={12},t.qgrd={13},t.qgrad={14} where t.id in(select FUELQUALITYID from cmcstbassay where ASSAYCODE='{15}')", item["MT"], item["MAD"], item["AD"], item["AAR"], item["AAD"], item["VAD"], item["VDAF"], item["STD"], item["STAD"], item["HAD"], item["HDAF"], item["QNETAR"], item["QBAD"], item["QGRD"], item["QGRAD"], assaycode);
if (SelfDber.Execute(assaySql) + SelfDber.Execute(qualitySql) > 0)
{
output(string.Format("化验编码：{0}，煤质信息同步成功！", assaycode), eOutputType.Normal);
}
}
}
/// <summary>
/// 测试数据库连接
/// </summary>
/// <returns></returns>
private bool TestDBConnect()
{
if (string.IsNullOrEmpty(txtSelfConnStr.Text.Trim()))
{
MessageBoxEx.Show("请先输入数据库连接字符串", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
try
{
OracleDapperDber dber = new OracleDapperDber(txtSelfConnStr.Text.Trim());
dber.CreateConnection().Open();
dber.CreateConnection().Close();
return(true);
}
catch (Exception ex)
{
MessageBoxEx.Show("数据库连接失败，" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
}
/// <summary>
/// ²âÊÔÊý¾Ý¿âÁ¬½Ó
/// </summary>
/// <returns></returns>
private bool TestDBConnect()
{
if (string.IsNullOrEmpty(txtSelfConnStr.Text.Trim()))
{
MessageBoxEx.Show("ÇëÏÈÊäÈëÊý¾Ý¿âÁ¬½Ó×Ö·û´®", "²Ù×÷Ìáʾ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
try
{
OracleDapperDber dber = new OracleDapperDber(txtSelfConnStr.Text.Trim());
using (OracleConnection conn = dber.CreateConnection() as OracleConnection)
{
conn.Open();
}
return(true);
}
catch (Exception ex)
{
MessageBoxEx.Show("Êý¾Ý¿âÁ¬½Óʧ°Ü£¬" + ex.Message, "²Ù×÷Ìáʾ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
}
/// <summary>
/// 同步数据
/// </summary>
/// <param name="output"></param>
/// <returns></returns>
public void TransferData(List <TableOrView> list, Action <string, eOutputType> output)
{
string interfaceUrl = commonDAO.GetAppletConfigString("数据同步智仁接口", "接口地址");
if (string.IsNullOrWhiteSpace(interfaceUrl))
{
output("未在【小程序参数配置】模块中添加配置“接口地址”", eOutputType.Error);
return;
}
OracleDapperDber thirdDber = new OracleDapperDber(interfaceUrl);
foreach (var item in list)
{
if (string.IsNullOrWhiteSpace(item.Source) || string.IsNullOrWhiteSpace(item.Destination) || string.IsNullOrWhiteSpace(item.Type))
{
continue;
}
if (item.PropertySetDetails == null || item.PropertySetDetails.Count <= 0)
{
continue;
}
if (item.Type.ToLower() == "download")
{
DownLoadData(item, thirdDber, output);
}
else if (item.Type.ToLower() == "upload")
{
UpLoadData(item, thirdDber, output);
}
else
{
output(string.Format("节点<TableOrView source='{0}' destination={1}>中的类型配置错误，支持类型：upload、download", item.Source, item.Destination), eOutputType.Error);
return;
}
}
}
/// <summary>
/// 同步数据
/// </summary>
private void Synch()
{
if (!TestPing(this._CommonAppConfig.ServerIp))
{
OutputInfoMethod(string.Format("服务器网络已断开"), eOutputType.Error);
return;
}
OracleDapperDber serverDber = new OracleDapperDber(this._CommonAppConfig.ServerConnStr);
OracleDapperDber clientDber = new OracleDapperDber(this._CommonAppConfig.ClientConnStr);
if (string.IsNullOrEmpty(this._CommonAppConfig.SyncIdentifier))
{
OutputInfoMethod(string.Format("同步标识未设置"), eOutputType.Error);
return;
}
foreach (TableSynch tableSynch in this._CommonAppConfig.TableSynchs)
{
try
{
if (!tableSynch.Enabled)
{
continue;
}
// 未设置表名则跳过
if (string.IsNullOrEmpty(tableSynch.TableName.Trim()))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：未设置表名(TableName)参数", tableSynch.SynchTitle), eOutputType.Error);
continue;
}
// 未设置主键名则跳过
if (string.IsNullOrEmpty(tableSynch.PrimaryKey.Trim()))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：未设置主键(PrimaryKey)参数", tableSynch.TableName), eOutputType.Error);
continue;
}
// 未设置同步标识则跳过
if (string.IsNullOrEmpty(tableSynch.SynchField.Trim()))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：未设置同步标识字段(SynchField)参数", tableSynch.TableName), eOutputType.Error);
continue;
}
//判断两个库表是否存在
if (serverDber.Execute(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0)
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：服务器端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error);
continue;
}
if (clientDber.Execute(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0)
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：就地端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error);
continue;
}
//判断两个库表字段是否一致
DataTable dt1 = serverDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName));
DataTable dt2 = clientDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName));
if (!OracleSqlBuilder.CompareTableField(dt1, dt2))
{
OutputInfoMethod(string.Format("【{0}】两端表{1}列名不一致", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Important);
IList <DataColumn> columns = OracleSqlBuilder.CompareTableFieldAndReturn(dt1, dt2);
if (columns != null && columns.Count > 0)
{
clientDber.Execute(OracleSqlBuilder.BuildAddColumnSQL(columns, serverDber));
clientDber.Execute(OracleSqlBuilder.BuildColumnDefaultSQL(columns, serverDber));
OutputInfoMethod(string.Format("【{0}】列已同步", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Important);
}
}
int res = 0;
switch (tableSynch.SynchType)
{
case "上传":
res = Upload(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
break;
case "下达":
res = Download(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
break;
case "双向":
//先下达 再上传
res = Download(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
res = Upload(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
break;
}
}
catch (Exception ex)
{
OutputErrorMethod(string.Format("【{0}】 {1}同步失败", tableSynch.SynchTitle, tableSynch.SynchType), ex);
}
}
//删除数据
foreach (CmcsWaitForHandleEvent item in clientDber.Entities <CmcsWaitForHandleEvent>("where DataFlag=0 and EventCode like '%删除%'"))
{
bool isSuccess = false;
eEventCode eventCode;
bool a = Enum.TryParse <eEventCode>(item.EventCode, out eventCode);
if (!Enum.TryParse <eEventCode>(item.EventCode, out eventCode))
{
continue;
}
switch (eventCode)
{
case eEventCode.汽车智能化_删除未完成运输记录:
isSuccess = clientDber.DeleteBySQL <CmcsUnFinishTransport>("where TransportId=:TransportId", new { TransportId = item.ObjectId }) > 0;
if (isSuccess)
{
OutputInfoMethod(string.Format("事件：{0} ObjectId：{1}", eEventCode.汽车智能化_删除未完成运输记录.ToString(), item.ObjectId), eOutputType.Normal);
}
break;
case eEventCode.汽车智能化_删除入厂煤运输记录:
isSuccess = clientDber.DeleteBySQL <CmcsBuyFuelTransport>("where Id=:Id", new { Id = item.ObjectId }) > 0;
if (isSuccess)
{
OutputInfoMethod(string.Format("事件：{0} ObjectId：{1}", eEventCode.汽车智能化_删除入厂煤运输记录.ToString(), item.ObjectId), eOutputType.Normal);
}
break;
case eEventCode.汽车智能化_删除出厂煤运输记录:
isSuccess = clientDber.DeleteBySQL <CmcsSaleFuelTransport>("where Id=:Id", new { Id = item.ObjectId }) > 0;
if (isSuccess)
{
OutputInfoMethod(string.Format("事件：{0} ObjectId：{1}", eEventCode.汽车智能化_删除出厂煤运输记录.ToString(), item.ObjectId), eOutputType.Normal);
}
break;
default:
break;
}
if (isSuccess)
{
item.DataFlag = 1;
clientDber.Update(item);
}
}
}
/// <summary>
/// 同步数据
/// </summary>
private void Synch()
{
OracleDapperDber serverDber = new OracleDapperDber(this._CommonAppConfig.ServerConnStr);
OracleDapperDber clientDber = new OracleDapperDber(this._CommonAppConfig.ClientConnStr);
foreach (TableSynch tableSynch in this._CommonAppConfig.TableSynchs)
{
try
{
if (!tableSynch.Enabled)
{
continue;
}
// 未设置表名则跳过
if (string.IsNullOrEmpty(tableSynch.TableName.Trim()))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：未设置表名(TableName)参数", tableSynch.SynchTitle), eOutputType.Error);
continue;
}
// 未设置主键名则跳过
if (string.IsNullOrEmpty(tableSynch.PrimaryKey.Trim()))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：未设置主键(PrimaryKey)参数", tableSynch.TableName), eOutputType.Error);
continue;
}
// 未设置同步标识则跳过
if (string.IsNullOrEmpty(tableSynch.SynchField.Trim()))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：未设置同步标识字段(SynchField)参数", tableSynch.TableName), eOutputType.Error);
continue;
}
//判断两个库表是否存在
if (serverDber.ExecuteScalar <int>(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0)
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：服务器端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error);
continue;
}
if (clientDber.ExecuteScalar <int>(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0)
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：就地端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error);
continue;
}
//判断两个库表字段是否一致
DataTable dt1 = serverDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName));
DataTable dt2 = clientDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName));
if (!OracleSqlBuilder.CompareTableField(dt1, dt2))
{
OutputInfoMethod(string.Format("【{0}】 同步未执行，原因：两端表{1}列名不一致", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error);
continue;
}
int res = 0;
switch (tableSynch.SynchType)
{
case "上传":
res = Upload(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
break;
case "下达":
res = Download(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
break;
case "双向":
//先下达 再上传
res = Download(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
res = Upload(serverDber, clientDber, tableSynch);
OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal);
break;
}
}
catch (Exception ex)
{
OutputErrorMethod(string.Format("【{0}】 {1}同步失败", tableSynch.SynchTitle, tableSynch.SynchType), ex);
}
}
}
private EquAssayDeviceDAO()
{
_ADGSAppConfig = ADGSAppConfig.GetInstance();
selfDber = new OracleDapperDber(this._ADGSAppConfig.SelfConnStr);
}
/// <summary>
/// 上报数据
/// </summary>
/// <param name="entity"></param>
/// <param name="thirdDber"></param>
/// <param name="output"></param>
private void UpLoadData(TableOrView entity, OracleDapperDber thirdDber, Action <string, eOutputType> output)
{
//查询本地是否有待上报数据
string sbSearch = "select ";
foreach (var item in entity.PropertySetDetails)
{
sbSearch += string.Format("{0},", item.Source);
}
sbSearch = sbSearch.Trim(',');
sbSearch += string.Format(" from {0} ", entity.Source);
if (!string.IsNullOrWhiteSpace(entity.TimeIntervalProperty))
{
int intervalValue = 7;
string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "数据上报时间间隔（天）");
if (!string.IsNullOrWhiteSpace(configValue))
{
Int32.TryParse(configValue, out intervalValue);
}
DateTime startTime = DateTime.Now.AddDays(-intervalValue);
sbSearch += string.Format(" where {0}>=to_date('{1}','yyyy-MM-dd hh24:mi:ss')", entity.TimeIntervalProperty, startTime);
}
DataTable dt = SelfDber.ExecuteDataTable(sbSearch);
if (dt == null || dt.Rows.Count <= 0)
{
return;
}
Boolean ishavepk = entity.PropertySetDetails.Count(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true") > 0;
foreach (DataRow item in dt.Rows)
{
//只有更新和新增操作
string strChaXun = string.Format("select * from {0} where 1=1 ", entity.Destination);
if (ishavepk)
{
foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true"))
{
if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime")
{
pk.Format = "yyyy-MM-dd hh24:mi:ss";
strChaXun += string.Format("and {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format);
}
else
{
strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString());
}
}
}
else
{
foreach (var pk in entity.PropertySetDetails)
{
if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime")
{
pk.Format = "yyyy-MM-dd hh24:mi:ss";
strChaXun += string.Format("and {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format);
}
else
{
strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString());
}
}
}
DataTable dtHaveData = thirdDber.ExecuteDataTable(strChaXun);
if (dtHaveData == null || dtHaveData.Rows.Count <= 0)
{
//新增
string insertSql = string.Format(@"insert into {0} (", entity.Destination);
string names = "";
string values = "";
foreach (var detail in entity.PropertySetDetails)
{
names += string.Format("{0},", detail.Destination);
if (detail.DesType != null && detail.DesType.ToLower() == "datetime" && !string.IsNullOrWhiteSpace(detail.Format))
{
values += string.Format("{0},", item[detail.Source] == DBNull.Value ? "''" : ("'" + DateTime.Parse(item[detail.Source].ToString()).ToString(detail.Format) + "'"));
}
else
{
values += string.Format("'{0}',", item[detail.Source] == DBNull.Value ? "" : item[detail.Source].ToString());
}
}
insertSql += names.Trim(',') + ") values (" + values.Trim(',') + ")";
if (thirdDber.Execute(insertSql) > 0)
{
output(string.Format("接口上报数据【{0}】已同步，操作：新增", entity.Description), eOutputType.Normal);
}
}
else
{
//更新
string updateSql = string.Format("update {0} set ", entity.Destination);
foreach (var detail in entity.PropertySetDetails)
{
if (detail.DesType != null && detail.DesType.ToLower() == "datetime" && !string.IsNullOrWhiteSpace(detail.Format))
{
updateSql += string.Format("{0}={1},", detail.Destination, item[detail.Source] == DBNull.Value ? "''" : ("'" + DateTime.Parse(item[detail.Source].ToString()).ToString(detail.Format) + "'"));
}
else
{
updateSql += string.Format("{0}='{1}',", detail.Destination, item[detail.Source] == DBNull.Value ? "" : item[detail.Source].ToString());
}
}
//if (entity.Description == "编码接口表")
// updateSql = updateSql.Trim(',') + string.Format(" where id='{0}'", dtHaveData.Rows[0]["TRUCKENTERID"].ToString());
//else
updateSql = updateSql.Trim(',');
string updateWhere = "";
if (ishavepk)
{
int index = 0;
foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true"))
{
if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime")
{
pk.Format = "yyyy-MM-dd hh24:mi:ss";
updateWhere += string.Format("{3} {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format, (index == 0 ? "" : "and"));
}
else
{
updateWhere += string.Format("{2} {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), (index == 0 ? "" : "and"));
}
index++;
}
if (!string.IsNullOrWhiteSpace(updateWhere))
{
updateWhere = " where " + updateWhere;
}
}
if (string.IsNullOrWhiteSpace(updateWhere))
{
updateWhere = string.Format(" where {0}='{1}'", entity.PropertySetDetails.First().Destination, dtHaveData.Rows[0][entity.PropertySetDetails.First().Destination].ToString());
}
updateSql = updateSql + " " + updateWhere;
//updateSql = updateSql.Trim(',') + string.Format(" where {0}='{1}'", entity.PropertySetDetails.First().Destination, dtHaveData.Rows[0][entity.PropertySetDetails.First().Destination].ToString());
if (thirdDber.Execute(updateSql) > 0)
{
output(string.Format("接口取数【{0}】已同步，操作：更新", entity.Description), eOutputType.Normal);
}
}
}
}
/// <summary>
/// 获取列注释
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="serverDber"></param>
/// <returns></returns>
public static string GetColumnComment(string tableName, string columnName, OracleDapperDber serverDber)
{
return(serverDber.ExecuteDataTable(string.Format("select COMMENTS from user_col_comments where Table_Name='{0}' and COLUMN_NAME='{1}'", tableName.ToUpper(), columnName.ToUpper())).Rows[0][0].ToString());
}
/// <summary>
/// 获取列默认值
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="serverDber"></param>
/// <returns></returns>
public static string GetColumnDefaultValue(string tableName, string columnName, OracleDapperDber serverDber)
{
return(serverDber.ExecuteDataTable(string.Format("SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME ='{0}' AND COLUMN_NAME = '{1}'", tableName.ToUpper(), columnName.ToUpper())).Rows[0][0].ToString());
}
public void TransferBaseOperLog(Action <string, eOutputType> output)
{
string interfaceUrl = commonDAO.GetAppletConfigString("数据同步智仁接口", "接口地址");
if (string.IsNullOrWhiteSpace(interfaceUrl))
{
output("未在【小程序参数配置】模块中添加配置“接口地址”", eOutputType.Error);
return;
}
OracleDapperDber thirdDber = new OracleDapperDber(interfaceUrl);
int intervalValue = 7;
string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔（天）");
if (!string.IsNullOrWhiteSpace(configValue))
{
Int32.TryParse(configValue, out intervalValue);
}
DateTime startTime = DateTime.Now.AddDays(-intervalValue);
string searchSql = string.Format("select * from V_JK_COALLOG where RIQ>=to_date('{0}','yyyy-MM-dd hh24:mi:ss') order by RIQ desc", startTime.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = thirdDber.ExecuteDataTable(searchSql);
if (dt == null || dt.Rows.Count <= 0)
{
return;
}
foreach (DataRow item in dt.Rows)
{
string sql = "";
if (item["LEIX"].ToString() == "煤矿单位")
{
#region 煤矿
if (item["DONGZ"].ToString() == "增加")
{
//先查有没有，没有就新增，有就更新
DataTable dtTemp = SelfDber.ExecuteDataTable(string.Format("select * from fultbmine where name='{0}' or CompanyCode='{1}'", item["NEWNAME"], item["NEWCODE"]));
if (dtTemp != null && dtTemp.Rows.Count > 0)
{
if (dtTemp.Rows.Count > 1)
{
output(string.Format("矿点：名称【{0}】，编码【{1}】，查询到多条记录，更新失败！请在系统中检查数据合理性！", item["NEWNAME"], item["NEWCODE"]), eOutputType.Error);
continue;
}
else
{
sql = string.Format("update fultbmine set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]);
}
}
else //新增
{
sql = string.Format(@"insert into fultbmine (ID, CREATIONTIME, CREATORUSERID, ISDELETED, CODE, SORT, NAME, ISSTOP, DATAFROM, PARENTID, SYNCFLAG, COMPANYCODE, SHORTNAME, DATAFLAG, SYNCTIME)
values ('{0}', sysdate, 1, 0, '{1}', 1, '{2}', 0, '集团接口', '-1', 0, '{1}', '{2}', 0, sysdate)", Guid.NewGuid().ToString(), item["NEWCODE"], item["NEWNAME"]);
Log4Neter.Info(sql);
}
}
else if (item["DONGZ"].ToString() == "更新")
{
sql = string.Format("update fultbmine set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]);
}
else if (item["DONGZ"].ToString() == "删除")
{
sql = string.Format("update fultbmine set isstop=1,SYNCTIME=sysdate where name='{0}' and CompanyCode='{1}'", item["OLDNAME"], item["OLDCODE"]);
}
#endregion
}
else if (item["LEIX"].ToString() == "运输单位")
{
#region 运输单位
if (item["DONGZ"].ToString() == "增加")
{
//先查有没有，没有就新增，有就更新
DataTable dtTemp = SelfDber.ExecuteDataTable(string.Format("select * from fultbtransportcompany where name='{0}' or code='{1}'", item["NEWNAME"], item["NEWCODE"]));
if (dtTemp != null && dtTemp.Rows.Count > 0)
{
if (dtTemp.Rows.Count > 1)
{
output(string.Format("运输单位：名称【{0}】，编码【{1}】，查询到多条记录，更新失败！请在系统中检查数据合理性！", item["OLDNAME"], item["OLDCODE"]), eOutputType.Error);
continue;
}
else
{
sql = string.Format("update fultbtransportcompany set name='{0}',code='{1}',SYNCTIME=sysdate where name='{2}' and code='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]);
}
}
else //新增
{
sql = string.Format(@"insert into fultbtransportcompany (ID, CREATIONTIME, CREATORUSERID, ISDELETED, CODE, NAME, ISSTOP, DATAFROM, SYNCFLAG, SYNCTIME)
values ('{0}', sysdate, 1, 0, '{1}', '{2}', 0, '集团接口', 0, sysdate)", Guid.NewGuid().ToString(), item["NEWCODE"], item["NEWNAME"]);
}
}
else if (item["DONGZ"].ToString() == "更新")
{
sql = string.Format("update fultbtransportcompany set name='{0}',code='{1}',SYNCTIME=sysdate where name='{2}' and code='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]);
}
else if (item["DONGZ"].ToString() == "删除")
{
sql = string.Format("update fultbtransportcompany set isstop=1,SYNCTIME=sysdate where name='{0}' and code='{1}'", item["OLDNAME"], item["OLDCODE"]);
}
#endregion
}
else if (item["LEIX"].ToString() == "煤种")
{
#region 煤种
if (item["DONGZ"].ToString() == "增加")
{
//先查有没有，没有就新增，有就更新
DataTable dtTemp = SelfDber.ExecuteDataTable(string.Format("select * from fultbfuelkind where name='{0}' or CompanyCode='{1}'", item["NEWNAME"], item["NEWCODE"]));
if (dtTemp != null && dtTemp.Rows.Count > 0)
{
if (dtTemp.Rows.Count > 1)
{
output(string.Format("煤种：名称【{0}】，编码【{1}】，查询到多条记录，更新失败！请在系统中检查数据合理性！", item["OLDNAME"], item["OLDCODE"]), eOutputType.Error);
continue;
}
else
{
sql = string.Format("update fultbfuelkind set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]);
}
}
else //新增
{
sql = string.Format(@"insert into fultbfuelkind (ID, CREATIONTIME, CREATORUSERID, ISDELETED, CODE, SORT, NAME, ISSTOP, DATAFROM, PARENTID, SYNCFLAG, COMPANYCODE, SHORTNAME, DATAFLAG, SYNCTIME)
values ('{0}', sysdate, 1, 0, '{1}', 1, '{2}', 0, '集团接口', '-1', 0, '{1}', '{2}', 0, sysdate)", Guid.NewGuid().ToString(), item["NEWCODE"], item["NEWNAME"]);
}
}
else if (item["DONGZ"].ToString() == "更新")
{
sql = string.Format("update fultbfuelkind set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]);
}
else if (item["DONGZ"].ToString() == "删除")
{
sql = string.Format("update fultbfuelkind set isstop=1,SYNCTIME=sysdate where name='{0}' and CompanyCode='{1}'", item["OLDNAME"], item["OLDCODE"]);
}
#endregion
}
if (string.IsNullOrWhiteSpace(sql))
{
continue;
}
if (SelfDber.Execute(sql) > 0)
{
output(string.Format("基础信息操作记录处理完成，操作内容：{0}，操作动作：{1}，修改前编码：{2}，修改后编码：{3}，修改前名称：{4}，修改后名称：{5}", item["LEIX"], item["DONGZ"], item["OLDCODE"], item["NEWCODE"], item["OLDNAME"], item["NEWNAME"]), eOutputType.Normal);
}
}
}
/// <summary>
/// 同步集团数据
/// </summary>
/// <param name="entity"></param>
/// <param name="thirdDber"></param>
/// <param name="output"></param>
private void DownLoadData(TableOrView entity, OracleDapperDber thirdDber, Action <string, eOutputType> output)
{
//同步集团数据
string sbSearch = "select ";
foreach (var item in entity.PropertySetDetails)
{
sbSearch += string.Format("{0},", item.Source);
}
sbSearch = sbSearch.Trim(',');
sbSearch += string.Format(" from {0} ", entity.Source);
if (!string.IsNullOrWhiteSpace(entity.TimeIntervalProperty))
{
int intervalValue = 7;
string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔（天）");
if (!string.IsNullOrWhiteSpace(configValue))
{
Int32.TryParse(configValue, out intervalValue);
}
DateTime startTime = DateTime.Now.AddDays(-intervalValue);
sbSearch += string.Format(" where {0}>=to_date('{1}','yyyy-MM-dd hh24:mi:ss')", entity.TimeIntervalProperty, startTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
DataTable dt = thirdDber.ExecuteDataTable(sbSearch);
if (dt == null || dt.Rows.Count <= 0)
{
return;
}
Boolean ishavepk = entity.PropertySetDetails.Count(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true") > 0;
foreach (DataRow item in dt.Rows)
{
//只有更新和新增操作
string strChaXun = string.Format("select * from {0} where 1=1 ", entity.Destination);
if (ishavepk)
{
foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true"))
{
strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == null ? "" : item[pk.Source].ToString());
}
}
else
{
foreach (var pk in entity.PropertySetDetails)
{
strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == null ? "" : item[pk.Source].ToString());
}
}
DataTable dtHaveData = SelfDber.ExecuteDataTable(strChaXun);
if (dtHaveData == null || dtHaveData.Rows.Count <= 0)
{
//新增
string insertSql = string.Format(@"insert into {0} (", entity.Destination);
string names = "ID, CREATIONTIME, CREATORUSERID,LASTMODIFICATIONTIME,";
string values = string.Format("'{0}', sysdate,1,sysdate,", Guid.NewGuid().ToString());
if (entity.Description == "矿点同步")
{
string code = commonDAO.GetMineNewChildCode("000");
if (!string.IsNullOrEmpty(code))
{
names += "Code,";
values += "'" + code + "',";
}
names += "Sort,";
values += commonDAO.GetMineSort() + ",";
}
else if (entity.Description == "煤种同步")
{
string code = commonDAO.GetFuelKindNewChildCode("000");
if (!string.IsNullOrEmpty(code))
{
names += "Code,";
values += "'" + code + "',";
}
names += "Sort,";
values += commonDAO.GetFuelKindSort() + ",";
}
if (!string.IsNullOrWhiteSpace(entity.IsSoftDelete) && entity.IsSoftDelete.ToLower() == "true")
{
names += "ISDELETED,";
values += "0,";
}
if (!string.IsNullOrWhiteSpace(entity.TreeParentId))
{
names += "parentid,";
values += string.Format("'{0}',", entity.TreeParentId);
}
foreach (var detail in entity.PropertySetDetails)
{
names += string.Format("{0},", detail.Destination);
values += string.Format("'{0}',", item[detail.Source] == null ? "" : item[detail.Source].ToString());
}
if (!string.IsNullOrWhiteSpace(entity.IsHaveSyncTime) && entity.IsHaveSyncTime.ToLower() == "true")
{
names += "SYNCTIME,";
values += "sysdate,";
}
insertSql += names.Trim(',') + ") values (" + values.Trim(',') + ")";
if (SelfDber.Execute(insertSql) > 0)
{
output(string.Format("接口取数【{0}】已同步，操作：新增", entity.Description), eOutputType.Normal);
}
}
else
{
//更新
string updateSql = string.Format("update {0} set ", entity.Destination);
foreach (var detail in entity.PropertySetDetails)
{
updateSql += string.Format("{0}='{1}',", detail.Destination, item[detail.Source] == null ? "" : item[detail.Source].ToString());
}
if (!string.IsNullOrWhiteSpace(entity.IsHaveSyncTime) && entity.IsHaveSyncTime.ToLower() == "true")
{
updateSql += "SYNCTIME=sysdate,";
}
updateSql = updateSql.Trim(',') + string.Format(" where id='{0}'", dtHaveData.Rows[0]["ID"].ToString());
if (SelfDber.Execute(updateSql) > 0)
{
output(string.Format("接口取数【{0}】已同步，操作：更新", entity.Description), eOutputType.Normal);
}
}
}
}
