public object RunStoredProcedure(string strSPName, BusinessObject obj)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strSPName))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (String.IsNullOrWhiteSpace(strSPName))
{
return(null);
}
try
{
DbCommand cmd = GetSPCommand(strSPName);
AddParameterForObject(obj, cmd);
db.ExecuteNonQuery(cmd);
String strIDCol = DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName);
if (!String.IsNullOrWhiteSpace(strIDCol))
{
Guid ret = ABCHelper.DataConverter.ConvertToGuid(db.GetParameterValue(cmd, strIDCol));
return(ret);
}
return(null);
}
catch (System.Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
if (((System.Data.SqlClient.SqlException)ex).ErrorCode == -2146232060 && ex.Message.Contains("TCP Provider"))
{
ShowDisconnectWaitingDialog();
return(0);
}
}
ABCHelper.ABCMessageBox.Show(ex.Source + ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
public static void CreateNewNotifyFromComment(String strUser, String strTableName, Guid iID)
{
if (strUser != ABCUserProvider.CurrentUserName)
{
String strTitle = DataConfigProvider.GetTableCaption(strTableName);
String strDisplayCol = DataStructureProvider.GetDisplayColumn(strTableName);
String strIDCol = DataStructureProvider.GetPrimaryKeyColumn(strTableName);
object obj = BusinessObjectController.GetData(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}' ", strDisplayCol, strTableName, strIDCol, iID));
if (obj != null && obj != DBNull.Value)
{
strTitle = strTitle + " : " + obj.ToString();
}
CreateNewNotify(strUser, strTitle, "", strTableName, iID, "");
}
}
public static string GetDisplayColumn(string strTableName)
{
String strResult = String.Empty;
if (DataConfigProvider.TableConfigList.ContainsKey(strTableName))
{
foreach (DataConfigProvider.FieldConfig fieldConfig in DataConfigProvider.TableConfigList[strTableName].FieldConfigList.Values)
{
if (fieldConfig.IsDisplayField)
{
strResult = fieldConfig.FieldName;
break;
}
}
}
if (String.IsNullOrWhiteSpace(strResult))
{
strResult = DataStructureProvider.GetNAMEColumn(strTableName);
}
if (String.IsNullOrWhiteSpace(strResult))
{
strResult = DataStructureProvider.GetNOColumn(strTableName);
}
if (String.IsNullOrWhiteSpace(strResult))
{
if (DataStructureProvider.IsTableColumn(strTableName, "Remark"))
{
strResult = "Remark";
}
}
if (String.IsNullOrWhiteSpace(strResult))
{
strResult = DataStructureProvider.GetPrimaryKeyColumn(strTableName);
}
return(strResult);
}
public static BusinessObject GetCachedBusinessObject(String strTableName, Guid iID)
{
if (String.IsNullOrWhiteSpace(strTableName))
{
return(null);
}
if (!DataStructureProvider.IsExistedTable(strTableName))
{
return(null);
}
DataView view = TryToGetDataView(strTableName, false);
DataRow[] rows = view.Table.Select(String.Format("{0} = '{1}'", DataStructureProvider.GetPrimaryKeyColumn(strTableName), iID));
if (rows.Length <= 0)
{
return(null);
}
return(BusinessControllerFactory.GetBusinessController(strTableName).GetObjectFromDataRow(rows[0]));
}
public static AccrossStructInfo GetAccrossStructInfo(String strTableName, Guid iFieldValue, String strFieldString, String strIDTableName)
{
if (String.IsNullOrWhiteSpace(strFieldString))
{
return(null);
}
String[] strArr = strFieldString.Split(':');
if (DataStructureProvider.IsForeignKey(strTableName, strArr[0]) == false && strArr[0] != "ID")
{
return(null);
}
AccrossStructInfo result = new AccrossStructInfo();
if (strArr[0] == "ID" && !String.IsNullOrWhiteSpace(strIDTableName))
{
result.TableName = strIDTableName;
}
else
{
result.TableName = DataStructureProvider.GetTableNameOfForeignKey(strTableName, strArr[0]);
}
result.TableID = iFieldValue;
result.FieldName = String.Empty;
if (!DataStructureProvider.IsExistedTable(result.TableName))
{
return(null);
}
BusinessObject objTable = GetCachedBusinessObject(result.TableName, result.TableID);
if (objTable == null)
{
return(null);
}
for (int i = 1; i < strArr.Length; i++)
{
result.FieldName = strArr[i];
if (result.FieldName == "ID" && DataStructureProvider.IsTableColumn(result.TableName, "TableName"))
{
object objTemp = ABCBusinessEntities.ABCDynamicInvoker.GetValue(objTable, "TableName");
if (objTemp == null)
{
break;
}
result.TableName = objTemp.ToString();
objTemp = ABCBusinessEntities.ABCDynamicInvoker.GetValue(objTable, result.FieldName);
if (objTemp == null)
{
break;
}
result.TableID = ABCHelper.DataConverter.ConvertToGuid(objTemp);
}
else
{
if (DataStructureProvider.IsForeignKey(result.TableName, result.FieldName) == false)
{
break;
}
result.TableName = DataStructureProvider.GetTableNameOfForeignKey(result.TableName, result.FieldName);
object objValue = ABCBusinessEntities.ABCDynamicInvoker.GetValue(objTable, result.FieldName);
if (objValue == null)
{
break;
}
result.TableID = ABCHelper.DataConverter.ConvertToGuid(objValue);
objTable = GetCachedBusinessObject(result.TableName, result.TableID);
if (objTable == null)
{
break;
}
}
result.FieldName = String.Empty;
}
if (DataStructureProvider.IsForeignKey(result.TableName, result.FieldName) || result.FieldName == String.Empty)
{
if (DataStructureProvider.IsForeignKey(result.TableName, result.FieldName))
{
result.TableName = DataStructureProvider.GetTableNameOfForeignKey(result.TableName, result.FieldName);
}
result.FieldName = DataStructureProvider.GetDisplayColumn(result.TableName);
}
if (String.IsNullOrWhiteSpace(result.FieldName))
{
result.FieldName = DataStructureProvider.GetDisplayColumn(result.TableName);
}
if (String.IsNullOrWhiteSpace(result.FieldName))
{
result.FieldName = DataStructureProvider.GetPrimaryKeyColumn(result.TableName);
}
return(result);
}
public static void RefreshLookupTable(String strTableName)
{
bool isUpdate = false;
lock ( LookupTables )
{
LoadLookupTable(strTableName);
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colUpdateTime) &&
LastUpdateTimes.ContainsKey(strTableName))
{
#region Has 'UpdateTime'
DateTime lastTimeInDB = TimeProvider.GetTableLastUpdateTime(strTableName);
DataTable lookupTable = LookupTables[strTableName];
DateTime lastTimeInApp = LastUpdateTimes[strTableName];
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colABCStatus))
{
#region Has 'ABCStatus'
if (lastTimeInDB > lastTimeInApp)
{
#region Refresh Modified Items
String strQuery = String.Format(@"SELECT * FROM {0} WHERE ABCStatus ='Alive' AND {1}", strTableName, TimeProvider.GenCompareDateTime(ABCCommon.ABCConstString.colUpdateTime, ">", lastTimeInApp));
DataSet ds = DataQueryProvider.CompanyDatabaseHelper.RunQuery(strQuery);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
BusinessObjectController controller = BusinessControllerFactory.GetBusinessController(strTableName);
String strPK = DataStructureProvider.GetPrimaryKeyColumn(strTableName);
foreach (DataRow row in ds.Tables[0].Rows)
{
#region Row
BusinessObject obj = (BusinessObject)controller.GetObjectFromDataRow(row);
Guid iID = ABCHelper.DataConverter.ConvertToGuid(ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, strPK));
DataRow[] rows = lookupTable.Select(String.Format("{0}='{1}'", strPK, iID));
if (rows.Length == 0) // new
{
lookupTable.ImportRow(row);
}
else
{
DataRow dr = rows[0];
int index = lookupTable.Rows.IndexOf(dr);
if (DataStructureProvider.IsExistABCStatus(obj.AATableName) && ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, ABCCommon.ABCConstString.colABCStatus).ToString().Equals(ABCCommon.ABCConstString.ABCStatusDeleted)) //delete
{
lookupTable.Rows.RemoveAt(index);
}
else //update
{
object[] lstArr = new object[row.ItemArray.Length];
row.ItemArray.CopyTo(lstArr, 0);
lookupTable.Rows[index].ItemArray = lstArr;
}
}
#endregion
}
isUpdate = true;
}
#endregion
}
#endregion
}
else
{
#region Without 'ABCStatus'
if (lastTimeInDB > lastTimeInApp || TimeProvider.GetRecordCountOfTable(strTableName) != lookupTable.Rows.Count)
{
DataSet ds = BusinessControllerFactory.GetBusinessController(strTableName).GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
lookupTable.Rows.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
lookupTable.ImportRow(dr);
}
isUpdate = true;
}
}
#endregion
}
LastUpdateTimes[strTableName] = lastTimeInDB;
#endregion
}
else
{
#region Without 'UpdateTime'
DataTable lookupTable = LookupTables[strTableName];
if (TimeProvider.GetRecordCountOfTable(strTableName) != lookupTable.Rows.Count)
{
DataSet ds = BusinessControllerFactory.GetBusinessController(strTableName).GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
lookupTable.Rows.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
lookupTable.ImportRow(dr);
}
isUpdate = true;
}
}
#endregion
}
}
if (isUpdate && RefreshTableMethods.ContainsKey(strTableName))
{
foreach (MethodInfo method in RefreshTableMethods[strTableName])
{
method.Invoke(null, new object[] {});
}
}
}
public static double GetCredit(Guid partnerID, Guid currencyID, CreditConfigType configType, bool isFCAmt)
{
double amt = 0;
foreach (CRCreditConfigsInfo config in GetCreditConfigs(configType))
{
if (DataStructureProvider.IsExistedTable(config.TableName) == false)
{
continue;
}
String strFKCurrencyIDCol = config.CurrencyIDField;
if (String.IsNullOrWhiteSpace(strFKCurrencyIDCol))
{
strFKCurrencyIDCol = "FK_GECurrencyID";
}
String strFKPartnerIDCol = config.PartnerIDField;
if (String.IsNullOrWhiteSpace(strFKPartnerIDCol))
{
strFKPartnerIDCol = "FK_MAPartnerID";
}
String strQuery = QueryGenerator.GenSelect(config.TableName, String.Format("SUM({0})", isFCAmt?config.AmtFCField:config.AmtField), true);
strQuery = QueryGenerator.AddCondition(strQuery, config.ConditionString);
if (DataStructureProvider.IsTableColumn(config.TableName, strFKPartnerIDCol))
{
strQuery = QueryGenerator.AddEqualCondition(strQuery, strFKPartnerIDCol, partnerID);
}
else
{
if (DataStructureProvider.IsExistedTable(config.ParentTableName))
{
if (DataStructureProvider.IsTableColumn(config.ParentTableName, strFKPartnerIDCol))
{
String strFK = DataStructureProvider.GetForeignKeyOfTableName(config.TableName, config.ParentTableName);
String strQuery2 = QueryGenerator.GenSelect(config.ParentTableName, DataStructureProvider.GetPrimaryKeyColumn(config.ParentTableName), true);
strQuery2 = QueryGenerator.AddEqualCondition(strQuery2, strFKPartnerIDCol, partnerID);
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} IN ({1})", strFK, strQuery2));
}
}
}
if ((configType & CreditConfigType.Increase) == CreditConfigType.Increase)
{
strQuery = QueryGenerator.AddEqualCondition(strQuery, "IsIncrease", true);
}
if ((configType & CreditConfigType.Decrease) == CreditConfigType.Decrease)
{
strQuery = QueryGenerator.AddEqualCondition(strQuery, "IsDecrease", true);
}
if ((configType & CreditConfigType.Purchase) == CreditConfigType.Purchase)
{
strQuery = QueryGenerator.AddEqualCondition(strQuery, "IsPurchase", true);
}
if ((configType & CreditConfigType.Sale) == CreditConfigType.Sale)
{
strQuery = QueryGenerator.AddEqualCondition(strQuery, "IsSale", true);
}
if (DataStructureProvider.IsTableColumn(config.TableName, strFKCurrencyIDCol))
{
strQuery = QueryGenerator.AddEqualCondition(strQuery, strFKCurrencyIDCol, currencyID);
}
else
{
if (DataStructureProvider.IsExistedTable(config.ParentTableName))
{
if (DataStructureProvider.IsTableColumn(config.ParentTableName, strFKCurrencyIDCol))
{
String strFK = DataStructureProvider.GetForeignKeyOfTableName(config.TableName, config.ParentTableName);
String strQuery2 = QueryGenerator.GenSelect(config.ParentTableName, DataStructureProvider.GetPrimaryKeyColumn(config.ParentTableName), true);
strQuery2 = QueryGenerator.AddEqualCondition(strQuery2, strFKCurrencyIDCol, currencyID);
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} IN ({1})", strFK, strQuery2));
}
}
}
object obj = BusinessObjectController.GetData(strQuery);
if (obj != null && obj != DBNull.Value)
{
amt += Convert.ToDouble(obj);
}
}
return(amt);
}
public object RunScript(string strScriptQuery, BusinessObject obj)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strScriptQuery))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (String.IsNullOrWhiteSpace(strScriptQuery))
{
return(null);
}
if (DataQueryProvider.IsSQLConnection(db) == false)
{
strScriptQuery = SQLiteDatabaseHelper.RepairSelectSQLite(strScriptQuery);
}
try
{
DbCommand cmd = GetScriptCommand(strScriptQuery);
AddParameterForObject(obj, cmd);
db.ExecuteNonQuery(cmd);
Guid ret = ABCHelper.DataConverter.ConvertToGuid(db.GetParameterValue(cmd, DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName)));
return(ret);
}
catch (System.Exception ex)
{
ABCHelper.ABCMessageBox.Show(ex.Source + ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
public void AddParameterForObject(BusinessObject obj, DbCommand cmd)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(cmd.CommandText))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (cmd.Parameters.Count == 0)
{
BusinessObjectHelper.InitPropertyList(obj.AATableName);
foreach (PropertyInfo property in BusinessObjectHelper.PropertyList[obj.AATableName].Values)
{
if (property.Name == DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName) && cmd.CommandText.Contains("Insert"))
{
db.AddOutParameter(cmd, property.Name, DbType.Guid, 64);
// AddOutParameter( cmd , property.Name , SqlDbType.Int , 8 );
}
else
{
object objValue = ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, property);
if (DataStructureProvider.IsForeignKey(obj.AATableName, property.Name))
{
if (objValue == null || ABCHelper.DataConverter.ConvertToGuid(objValue) == Guid.Empty)
{
objValue = DBNull.Value;
}
}
if (objValue == null)
{
objValue = DBNull.Value;
}
AddInParameter(cmd, property, objValue);
}
}
}
else
{
foreach (DbParameter param in cmd.Parameters)
{
if (param.Direction == ParameterDirection.Input)
{
String strFieldName = param.ParameterName.Replace("@", "");
object objValue = ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, strFieldName);
if (DataStructureProvider.IsForeignKey(obj.AATableName, strFieldName))
{
if (objValue == null || ABCHelper.DataConverter.ConvertToGuid(objValue) == Guid.Empty)
{
objValue = DBNull.Value;
}
}
if (objValue == null)
{
objValue = DBNull.Value;
}
if (param.Value != objValue && objValue.ToString() != param.Value.ToString())
{
db.SetParameterValue(cmd, param.ParameterName, objValue);
}
}
}
}
}
public static void CreateNewNotifyFromComment(String strTableName, Guid iID)
{
if (DataStructureProvider.IsExistedTable(strTableName) == false)
{
return;
}
String strIDCol = DataStructureProvider.GetPrimaryKeyColumn(strTableName);
#region Get Users
List <String> lstUsers = new List <string>();
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT CreateUser FROM GEComments WHERE TableName ='{0}' AND ID = '{1}' GROUP BY CreateUser", strTableName, iID));
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (lstUsers.Contains(dr[0].ToString()) == false)
{
lstUsers.Add(dr[0].ToString());
}
}
}
ds = BusinessObjectController.RunQuery(String.Format(@"SELECT TagString FROM GEComments WHERE TableName ='{0}' AND ID = '{1}' AND TagString IS NOT NULL AND TagString NOT LIKE '' GROUP BY TagString", strTableName, iID));
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr[0] != null && dr[0] != DBNull.Value && String.IsNullOrWhiteSpace(dr[0].ToString()) == false)
{
string[] arr = { "::" };
arr = dr[0].ToString().Split(arr, StringSplitOptions.None);
for (int i = 0; i < arr.Length; i++)
{
if (lstUsers.Contains(arr[i]) == false)
{
lstUsers.Add(arr[i]);
}
}
}
}
}
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colCreateUser))
{
ds = BusinessObjectController.RunQuery(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}'", ABCCommon.ABCConstString.colCreateUser, strTableName, strIDCol, iID));
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
object objCreateUser = ds.Tables[0].Rows[0][0];
if (objCreateUser != null && objCreateUser != DBNull.Value && lstUsers.Contains(objCreateUser.ToString()) == false)
{
lstUsers.Add(objCreateUser.ToString());
}
}
}
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colUpdateUser))
{
ds = BusinessObjectController.RunQuery(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}'", ABCCommon.ABCConstString.colUpdateUser, strTableName, strIDCol, iID));
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
object objUpdateUser = ds.Tables[0].Rows[0][0];
if (objUpdateUser != null && objUpdateUser != DBNull.Value && lstUsers.Contains(objUpdateUser.ToString()) == false)
{
lstUsers.Add(objUpdateUser.ToString());
}
}
}
#endregion
String strTitle = DataConfigProvider.GetTableCaption(strTableName);
String strDisplayCol = DataStructureProvider.GetDisplayColumn(strTableName);
object obj = BusinessObjectController.GetData(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}' ", strDisplayCol, strTableName, strIDCol, iID));
if (obj != null && obj != DBNull.Value)
{
strTitle = strTitle + " : " + obj.ToString();
}
foreach (String strUser in lstUsers)
{
if (strUser != ABCUserProvider.CurrentUserName)
{
CreateNewNotify(strUser, strTitle, "", strTableName, iID, "");
}
}
}
public static void Synchronize(STDataServerSyncsInfo syncInfo)
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
return;
}
if (!syncInfo.IsPull && !syncInfo.IsPush)
{
return;
}
if (syncInfo.LastSyncDate.HasValue && syncInfo.IntervalMinute.HasValue &&
(DateTime.Now < syncInfo.LastSyncDate.Value || DateTime.Now.Subtract(syncInfo.LastSyncDate.Value).TotalMinutes < syncInfo.IntervalMinute.Value))
{
return;
}
STDataServersController serverCtrl = new STDataServersController();
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
DBConnectionController connection = null;
if (DataStructureProvider.IsSystemTable(syncInfo.TableName))
{
connection = InitSystemConnection(server.STDataServerID);
}
else
{
connection = InitCompanyConnection(server.STDataServerID);
}
if (connection != null)
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
String strRunQuery = @"SET XACT_ABORT ON;BEGIN TRANSACTION ABCSYNC; ";
String strQuery = String.Empty;
if (syncInfo.IsPush)
{
if (syncInfo.IsPushMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [{0}].[{1}].[dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"INSERT INTO [{0}].[{1}].[dbo].[{2}] SELECT * FROM [dbo].[{2}] WHERE [dbo].[{2}].[{3}] IN ({4}) AND [dbo].[{2}].[{3}] NOT IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"UPDATE [{0}].[{1}].[dbo].[{2}] SET #FIELD# FROM [{0}].[{1}].[dbo].[{2}] Target,[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
if (syncInfo.IsPull)
{
if (syncInfo.IsPullMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [{0}].[{1}].[dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"INSERT INTO [dbo].[{2}] SELECT * FROM [{0}].[{1}].[dbo].[{2}] WHERE [{3}] IN ({4}) AND [{3}] NOT IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"UPDATE [dbo].[{2}] SET #FIELD# FROM [dbo].[{2}] Target,[{0}].[{1}].[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
strRunQuery = String.Format(@"{0} COMMIT TRANSACTION ABCSYNC; SET XACT_ABORT OFF;", strRunQuery + Environment.NewLine);
BusinessObjectController.RunQuery(strRunQuery, syncInfo.TableName);
BusinessObjectController.RunQuery(String.Format(@"UPDATE STDataServerSyncs SET LastSyncDate =GetDate() WHERE STDataServerSyncID='{0}'", syncInfo.GetID()), syncInfo.AATableName);
scope.Complete();
}
}
}
public static Numbering GetNumberingConfig(BusinessObject obj)
{
if (NumberingConfigs == null || NumberingTypes == null)
{
InitializeNumberings();
}
foreach (Numbering config in NumberingConfigs)
{
if (config.TableName != obj.AATableName)
{
continue;
}
if (!String.IsNullOrWhiteSpace(config.FieldCondition) && !String.IsNullOrWhiteSpace(config.FieldValue))
{
object fileValue = ABCDynamicInvoker.GetValue(obj, config.FieldCondition);
if (fileValue == null || fileValue == DBNull.Value || fileValue.ToString() != config.FieldValue)
{
continue;
}
}
if (!String.IsNullOrWhiteSpace(config.ConditionString))
{
String strQuery = QueryGenerator.GenSelect(obj.AATableName, "COUNT(*)", false);
strQuery = QueryGenerator.AddCondition(strQuery, config.ConditionString);
strQuery = QueryGenerator.AddCondition(strQuery, String.Format(@"{0}='{1}'", DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName), obj.GetID()));
object objCount = BusinessObjectController.GetData(strQuery);
if (objCount == null || objCount == DBNull.Value || Convert.ToInt32(objCount.ToString()) <= 0)
{
continue;
}
}
return(config);
}
return(null);
}
