public IEnumerable<UserSettings> GetAll()
{
var db = UmbracoContext.Application.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_usettings");
return db.Fetch<UserSettings>(query);
}
public static EventLocation GetLocation(int id)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_locations").Where<EventLocation>(x => x.Id == id);
return db.Fetch<EventLocation>(query).FirstOrDefault();
}
public static IEnumerable<RecurringEvent> GetAllEvents()
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_recevents");
return db.Fetch<RecurringEvent>(query);
}
internal static PostsByTagModel GetContentByTag(this UmbracoHelper helper, IMasterModel masterModel, string tag, string tagGroup, string baseUrlName)
{
//TODO: Use the new 7.1.2 tags API to do this
//TODO: Umbraco core needs to have a method to get content by tag(s), in the meantime we
// need to run a query
var sql = new Sql().Select("cmsTagRelationship.nodeId, cmsTagRelationship.tagId, cmsTags.tag")
.From("cmsTagRelationship")
.InnerJoin("cmsTags")
.On("cmsTagRelationship.tagId = cmsTags.id")
.Where("cmsTags.tag = @tagName AND cmsTags." + SqlSyntaxContext.SqlSyntaxProvider.GetQuotedColumnName("group") + " = @tagGroup", new
{
tagName = tag,
tagGroup = tagGroup
});
var taggedContent = ApplicationContext.Current.DatabaseContext.Database.Fetch<TagDto>(sql);
return taggedContent.GroupBy(x => x.TagId)
.Select(x => new PostsByTagModel(
helper.TypedContent(
x.Select(t => t.NodeId).Distinct())
.Select(c => new PostModel(c)).OrderByDescending(c => c.PublishedDate),
x.First().Tag,
masterModel.RootBlogNode.Url.EnsureEndsWith('/') + baseUrlName + "/" + x.First().Tag.ToLowerInvariant()))
.FirstOrDefault();
}
public void Can_Verify_Base_Clause()
{
var NodeObjectType = new Guid(Constants.ObjectTypes.Document);
var expected = new Sql();
expected.Select("*")
.From("[cmsDocument]")
.InnerJoin("[cmsContentVersion]").On("[cmsDocument].[versionId] = [cmsContentVersion].[VersionId]")
.InnerJoin("[cmsContent]").On("[cmsContentVersion].[ContentId] = [cmsContent].[nodeId]")
.InnerJoin("[umbracoNode]").On("[cmsContent].[nodeId] = [umbracoNode].[id]")
.Where("[umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'");
var sql = new Sql();
sql.Select("*")
.From<DocumentDto>()
.InnerJoin<ContentVersionDto>()
.On<DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
.InnerJoin<ContentDto>()
.On<ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
.InnerJoin<NodeDto>()
.On<ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
.Where<NodeDto>(x => x.NodeObjectType == NodeObjectType);
Assert.That(sql.SQL, Is.EqualTo(expected.SQL));
Console.WriteLine(sql.SQL);
}
public IEnumerable<object> GetAll(string typeName, string sortColumn, string sortOrder)
{
var currentType = Type.GetType(typeName);
var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
var strTableName = tableName.Value;
var db = (Database)DatabaseContext.Database;
if(!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
db = new Database(uioMaticAttri.ConnectionStringName);
if (strTableName.IndexOf("[") < 0)
{
strTableName = "[" + strTableName + "]";
}
var query = new Sql().Select("*").From(strTableName);
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
{
var strSortColumn = sortColumn;
if (strSortColumn.IndexOf("[") < 0)
{
strSortColumn = "[" + strSortColumn + "]";
}
query.OrderBy(strSortColumn + " " + sortOrder);
}
foreach (dynamic item in db.Fetch<dynamic>(query))
{
// get settable public properties of the type
var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => x.GetSetMethod() != null);
// create an instance of the type
var obj = Activator.CreateInstance(currentType);
// set property values using reflection
var values = (IDictionary<string, object>)item;
foreach (var prop in props)
{
var columnAttri =
prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = prop.Name;
if (columnAttri.Any())
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
if(values.ContainsKey(propName))
prop.SetValue(obj, values[propName]);
}
yield return obj;
}
}
public static DbCommand GetCommand(this MapperDb db, Sql sql)
{
var command = db.DbFactory.CreateCommand();
if (command == null)
{
throw new MapperException("Could not create database command.");
}
command.CommandText = sql.GetQuery();
var parameters = sql.GetParameterValues();
//Console.WriteLine(sql.GetQuery());
//Console.WriteLine(string.Join(",", sql.GetParameterValues()));
foreach (int arg in sql.GetParameters())
{
var parameter = db.DbFactory.CreateParameter();
if (parameter == null)
{
throw new MapperException("Could not create database command.");
}
parameter.ParameterName = "@" + arg;
parameter.Value = parameters[arg] ?? DBNull.Value;
command.Parameters.Add(parameter);
}
return command;
}
public IEnumerable<EventLocation> GetAll()
{
var db = UmbracoContext.Application.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_locations");
return db.Fetch<EventLocation>(query);
}
public static IEnumerable<EventLocation> GetAllLocations()
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_locations");
return db.Fetch<EventLocation>(query);
}
public void Can_Verify_Base_Where_Clause()
{
var NodeObjectType = new Guid("A2CB7800-F571-4787-9638-BC48539A0EFB");
var expected = new Sql();
expected.Select("*")
.From("[cmsDocumentType]")
.RightJoin("[cmsContentType]")
.On("[cmsContentType].[nodeId] = [cmsDocumentType].[contentTypeNodeId]")
.InnerJoin("[umbracoNode]")
.On("[cmsContentType].[nodeId] = [umbracoNode].[id]")
.Where("[umbracoNode].[nodeObjectType] = 'a2cb7800-f571-4787-9638-bc48539a0efb'")
.Where("[cmsDocumentType].[IsDefault] = 1")
.Where("[umbracoNode].[id] = 1050");
var sql = new Sql();
sql.Select("*")
.From<DocumentTypeDto>()
.RightJoin<ContentTypeDto>()
.On<ContentTypeDto, DocumentTypeDto>(left => left.NodeId, right => right.ContentTypeNodeId)
.InnerJoin<NodeDto>()
.On<ContentTypeDto, NodeDto>(left => left.NodeId, right => right.NodeId)
.Where<NodeDto>(x => x.NodeObjectType == NodeObjectType)
.Where<DocumentTypeDto>(x => x.IsDefault == true)
.Where<NodeDto>(x => x.NodeId == 1050);
Assert.That(sql.SQL, Is.EqualTo(expected.SQL));
Console.WriteLine(sql.SQL);
}
public IEnumerable<Models.Forum> GetForums(int rootId)
{
var sql = new Sql();
sql.Where<Models.Forum>(x => x.ParentId == rootId);
sql.OrderBy<Models.Forum>(x => x.SortOrder);
return _databaseContext.Database.Fetch<Models.Forum>(sql);
}
public IEnumerable<Person> GetAll()
{
var db = UmbracoContext.Application.DatabaseContext.Database;
var query = new Sql().Select("*").From("people");
return db.Fetch<Person>(query);
}
public void Can_Verify_Base_Where_With_Version_Clause()
{
var NodeObjectType = new Guid(Constants.ObjectTypes.Document);
var versionId = new Guid("2b543516-a944-4ee6-88c6-8813da7aaa07");
var expected = new Sql();
expected.Select("*")
.From("[cmsDocument]")
.InnerJoin("[cmsContentVersion]").On("[cmsDocument].[versionId] = [cmsContentVersion].[VersionId]")
.InnerJoin("[cmsContent]").On("[cmsContentVersion].[ContentId] = [cmsContent].[nodeId]")
.InnerJoin("[umbracoNode]").On("[cmsContent].[nodeId] = [umbracoNode].[id]")
.Where("[umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'")
.Where("[umbracoNode].[id] = 1050")
.Where("[cmsContentVersion].[VersionId] = '2b543516-a944-4ee6-88c6-8813da7aaa07'")
.OrderBy("[cmsContentVersion].[VersionDate] DESC");
var sql = new Sql();
sql.Select("*")
.From<DocumentDto>()
.InnerJoin<ContentVersionDto>()
.On<DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
.InnerJoin<ContentDto>()
.On<ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
.InnerJoin<NodeDto>()
.On<ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
.Where<NodeDto>(x => x.NodeObjectType == NodeObjectType)
.Where<NodeDto>(x => x.NodeId == 1050)
.Where<ContentVersionDto>(x => x.VersionId == versionId)
.OrderByDescending<ContentVersionDto>(x => x.VersionDate);
Assert.That(sql.SQL, Is.EqualTo(expected.SQL));
Console.WriteLine(sql.SQL);
}
public int DeleteGroup(int id)
{
var query = new Sql().Where<EasyADGroup2User>(e => e.GroupId == id);
_database.Delete<EasyADGroup2User>(query);
return _database.Delete<EasyADGroup>(id);
}
public virtual async Task NonQueryAsync(MapperDb db, Sql sql)
{
using (var command = db.GetCommand(sql))
{
await this.NonQueryAsync(db, command).ConfigureAwait(false);
}
}
/// <summary>
/// Returns a calendar by its id
/// </summary>
/// <param name="id">The id of the calendar</param>
/// <returns>The calendar with the specified id</returns>
public static ECalendar GetCalendarById(int id)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_calendars").Where<ECalendar>(x => x.Id == id);
return db.Fetch<ECalendar>(query).FirstOrDefault();
}
/// <summary>
/// Returns all available calendar
/// </summary>
/// <returns>IEnumerable<ECalendar> of all available calendar</returns>
public static IEnumerable<ECalendar> GetAllCalendar()
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var query = new Sql().Select("*").From("ec_calendars");
return db.Fetch<ECalendar>(query);
}
public IEnumerable<Schedule> GetAllSchedules()
{
var query = new Sql()
.Select("*")
.From(SchedulerConstants.Database.ScheduleTable);
return DatabaseContext.Database.Fetch<Schedule>(query);
}
/// <summary>
/// Return the content override object for this content type and property type.
/// </summary>
/// <param name="contentTypeAlias"></param>
/// <param name="propertyTypeAlias"></param>
/// <param name="type"></param>
/// <param name="configAlias"></param>
/// <returns></returns>
public static ContentOverrideDto GetContentOverride(
string contentTypeAlias,
string propertyTypeAlias,
string archetypeAlias = null,
int? nodeId = null,
ContentOverrideType type = ContentOverrideType.Config,
string configAlias = null
)
{
var typeValue = type.ToString();
var sql = new Sql();
sql.Select("*")
.From<ContentOverrideDto>()
.Where<ContentOverrideDto>(x => x.ContentTypeAlias == contentTypeAlias)
.Where<ContentOverrideDto>(x => x.PropertyTypeAlias == propertyTypeAlias)
.Where<ContentOverrideDto>(x => x.Type == typeValue);
if (!string.IsNullOrWhiteSpace(archetypeAlias))
{
sql.Where<ContentOverrideDto>(x => x.ArchetypeAlias == archetypeAlias);
}
if (nodeId.HasValue)
{
sql.Where<ContentOverrideDto>(x => x.NodeId == nodeId.Value);
}
if (type == ContentOverrideType.Config && !string.IsNullOrWhiteSpace(configAlias))
{
sql.Where<ContentOverrideDto>(x => x.ConfigAlias == configAlias);
}
return Database.SingleOrDefault<ContentOverrideDto>(sql);
}
public IEnumerable<Ingredient> GetAll()
{
var db = UmbracoContext.Application.DatabaseContext.Database;
var query = new Sql().Select("*").From("ingredient");
return db.Fetch<Ingredient>(query);
}
/// <summary>
/// Loads the data value from the database.
/// </summary>
protected virtual void LoadValueFromDatabase()
{
var sql = new Sql();
sql.Select("*")
.From<PropertyDataDto>()
.InnerJoin<PropertyTypeDto>()
.On<PropertyTypeDto, PropertyDataDto>(x => x.Id, y => y.PropertyTypeId)
.InnerJoin<DataTypeDto>()
.On<DataTypeDto, PropertyTypeDto>(x => x.DataTypeId, y => y.DataTypeId)
.Where<PropertyDataDto>(x => x.Id == _propertyId);
var dto = Database.Fetch<PropertyDataDto, PropertyTypeDto, DataTypeDto>(sql).FirstOrDefault();
if (dto != null)
{
//the type stored in the cmsDataType table
var strDbType = dto.PropertyTypeDto.DataTypeDto.DbType;
//get the enum of the data type
var dbType = BaseDataType.GetDBType(strDbType);
//get the column name in the cmsPropertyData table that stores the correct information for the data type
var fieldName = BaseDataType.GetDataFieldName(dbType);
//get the value for the data type, if null, set it to an empty string
_value = dto.GetValue;
//now that we've set our value, we can update our BaseDataType object with the correct values from the db
//instead of making it query for itself. This is a peformance optimization enhancement.
_dataType.SetDataTypeProperties(fieldName, dbType);
}
}
public Person GetById(int id)
{
var db = UmbracoContext.Application.DatabaseContext.Database;
var query = new Sql().Select("*").From("people").Where<Person>(x => x.Id == id);
return db.Fetch<Person>(query).FirstOrDefault();
}
public ScheduleUrl GetTaskById(int id)
{
var query = new Sql().Select("*").From("TD_ScheduleUrl").Where("id =" + id);
var result = DatabaseContext.Database.Fetch<ScheduleUrl>(query).FirstOrDefault();
return result;
}
public void exec(string strConn)
{
Sql sql = new Sql(strConn);
//リソースファイルにあるデータベース初期化SQLを取得
string strCommand = Properties.Resources.InitSQL;
sql.exec(strCommand);
}
public MainForm()
{
InitializeComponent();
Csql = new Sql();
B = new Basic();
TasksBs = new BindingSource();
TasksBs.PositionChanged += new EventHandler(TasksBs_PositionChanged);
CheckedTask = new SortedList();
}
public void SendNotification(Topic topic, string memberName, string url)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var sql = new Sql().Select("memberId")
.From("forumSubscribers")
.Where("forumId = @forumId", new { forumId = topic.ParentId });
var results = db.Query<int>(sql).ToList();
using (ContextHelper.EnsureHttpContext())
{
var memberShipHelper = new MembershipHelper(UmbracoContext.Current);
{
foreach (var memberId in results.Where(memberId => memberId != topic.MemberId))
{
try
{
var member = memberShipHelper.GetById(memberId);
if (member.GetPropertyValue<bool>("bugMeNot"))
continue;
var from = new MailAddress(_details.SelectSingleNode("//from/email").InnerText,
_details.SelectSingleNode("//from/name").InnerText);
var contentService = ApplicationContext.Current.Services.ContentService;
var forum = contentService.GetById(topic.ParentId);
var subject = _details.SelectSingleNode("//subject").InnerText;
subject = string.Format(subject, forum.Name);
var domain = _details.SelectSingleNode("//domain").InnerText;
var body = _details.SelectSingleNode("//body").InnerText;
body = string.Format(body, forum.Name, "https://" + domain + url, memberName, topic.Title,
HttpUtility.HtmlDecode(umbraco.library.StripHtml(topic.Body)));
var mailMessage = new MailMessage
{
Subject = subject,
Body = body
};
mailMessage.To.Add(member.GetPropertyValue<string>("Email"));
mailMessage.From = from;
using (var smtpClient = new SmtpClient())
{
smtpClient.Send(mailMessage);
}
}
catch (Exception exception)
{
LogHelper.Error<NewForumTopic>(
string.Format("Error sending mail to member id {0}", memberId), exception);
}
}
}
}
}
public void FixtureSetup()
{
SqlSyntaxProviderTestHelper.EstablishSqlSyntax();
var cacheProvider = new Mock<IRuntimeCacheProvider>();
GatewayProviderService = new GatewayProviderService();
var providers =
GatewayProviderService.GetAllGatewayProviders()
.Where(x => x.GatewayProviderType == GatewayProviderType.Payment);
GatewayProvider = providers.FirstOrDefault(x => x.Key == new Guid("C6BF6743-3565-401F-911A-33B68CACB11B"));
if (GatewayProvider != null)
{
GatewayProviderService.Delete(GatewayProvider);
}
var petaPoco = new PetaPocoUnitOfWorkProvider();
var xLogin = ConfigurationManager.AppSettings["xlogin"];
var xtrankey = ConfigurationManager.AppSettings["xtrankey"];
var sql = new Sql();
var dto = new GatewayProviderDto()
{
Key = new Guid("C6BF6743-3565-401F-911A-33B68CACB11B"),
Name = "AuthorizeNet",
Description = "AuthorizeNet",
TypeFullName =
"Merchello.Plugin.Payments.AuthorizeNet.AuthorizeNetPaymentGatewayProvider, Merchello.Plugin.Payments.AuthorizeNet, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null",
ExtendedData = "<extendedData />",
EncryptExtendedData = false,
ProviderTfKey = Constants.TypeFieldKeys.GatewayProvider.PaymentProviderKey,
CreateDate = DateTime.Now,
UpdateDate = DateTime.Now
};
petaPoco.GetUnitOfWork().Database.Insert(dto);
GatewayProvider =
GatewayProviderService.GetGatewayProviderByKey(new Guid("C6BF6743-3565-401F-911A-33B68CACB11B"));
var providerSettings = new AuthorizeNetProcessorSettings()
{
LoginId = xLogin,
TransactionKey = xtrankey
};
GatewayProvider.ExtendedData.SaveProcessorSettings(providerSettings);
Provider = new AuthorizeNetPaymentGatewayProvider(GatewayProviderService, GatewayProvider,
cacheProvider.Object);
}
public void FixtureSetup()
{
SqlSyntaxProviderTestHelper.EstablishSqlSyntax();
var cacheProvider = new Mock<IRuntimeCacheProvider>();
GatewayProviderService = new GatewayProviderService();
var providers =
GatewayProviderService.GetAllGatewayProviders()
.Where(x => x.GatewayProviderType == GatewayProviderType.Payment);
GatewayProviderSettings = providers.FirstOrDefault(x => x.Key == new Guid("15C87B6F-7987-49D9-8444-A2B4406941A8"));
if (GatewayProviderSettings != null)
{
GatewayProviderService.Delete(GatewayProviderSettings);
}
var petaPoco = new PetaPocoUnitOfWorkProvider();
var xLogin = ConfigurationManager.AppSettings["xlogin"];
var xtrankey = ConfigurationManager.AppSettings["xtrankey"];
var sql = new Sql();
var dto = new GatewayProviderSettingsDto()
{
Key = new Guid("15C87B6F-7987-49D9-8444-A2B4406941A8"),
Name = "Stripe",
Description = "Stripe",
ExtendedData = "<extendedData />",
EncryptExtendedData = false,
ProviderTfKey = Constants.TypeFieldKeys.GatewayProvider.PaymentProviderKey,
CreateDate = DateTime.Now,
UpdateDate = DateTime.Now
};
petaPoco.GetUnitOfWork().Database.Insert(dto);
GatewayProviderSettings =
GatewayProviderService.GetGatewayProviderByKey(new Guid("15C87B6F-7987-49D9-8444-A2B4406941A8"));
var providerSettings = new StripeProcessorSettings()
{
// TODO
//LoginId = xLogin,
//TransactionKey = xtrankey
};
GatewayProviderSettings.ExtendedData.SaveProcessorSettings(providerSettings);
Provider = new StripePaymentGatewayProvider(GatewayProviderService, GatewayProviderSettings,
cacheProvider.Object);
}
public IEnumerable<ProjectContributor> GetContributors(int projectId)
{
var sql = new Sql()
.Select("*")
.From<ProjectContributor>();
sql.Where<ProjectContributor>(x => x.ProjectId == projectId);
return _dbContext.Database.Query<ProjectContributor>(sql);
}
public IEnumerable<ChartData> GetAll2()
{
var db = UmbracoContext.Application.DatabaseContext.Database;
var q = new Sql()
.Select("CAST([dCreatedDate] AS DATE) as 'd',[sUpdatedBy] as 's', count(*) as 'c'")
.From("Message")
.Where("[sUpdatedBy] is not null")
.GroupBy("CAST([dCreatedDate] AS DATE),[sUpdatedBy]");
return db.Fetch<ChartData>(q);
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
Guid gCONTACT_ID = Sql.ToGuid(txtCONTACT_ID.Value);
if (!Sql.IsEmptyGuid(gCONTACT_ID))
{
try
{
// 06/12/2007 Paul. Enable Contracts / Contacts relationship.
SqlProcs.spCONTRACTS_CONTACTS_Update(gID, gCONTACT_ID);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTRACTS_CONTACTS " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/27/2006 Paul. Make sure to filter relationship data based on team access rights.
Security.Filter(cmd, m_sMODULE, "list");
cmd.CommandText += " and CONTRACT_ID = @CONTRACT_ID" + ControlChars.CrLf;
cmd.CommandText += " order by DATE_ENTERED asc " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@CONTRACT_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwCONTRACTS_CONTACTS", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
/// <summary>
/// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a
/// very particular syntax that
/// needs to be used to work for all servers
/// </summary>
/// <returns></returns>
/// <remarks>
/// See: http://issues.umbraco.org/issue/U4-3876
/// </remarks>
public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery, WhereInType whereInType = WhereInType.In) =>
// TODO: This is no longer necessary since this used to be a specific requirement for MySql!
// Now we can do a Delete<T> + sub query, see RelationRepository.DeleteByParent for example
new Sql(
string.Format(
whereInType == WhereInType.In
? @"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)"
: @"DELETE FROM {0} WHERE {1} NOT IN (SELECT {1} FROM ({2}) x)",
sqlProvider.GetQuotedTableName(tableName),
sqlProvider.GetQuotedColumnName(columnName),
subQuery.SQL), subQuery.Arguments);
public static Page <T> Page(long page, long itemsPerPage, Sql sql)
{
return(repo.Page <T>(page, itemsPerPage, sql));
}
private Sql GetSortedSqlForPagedResults(Sql sql, Direction orderDirection, string orderBy, bool orderBySystemField, Tuple <string, string> nodeIdSelect)
{
//copy to var so that the original isn't changed
var sortedSql = new Sql(sql.SQL, sql.Arguments);
if (orderBySystemField)
{
// Apply order according to parameters
if (string.IsNullOrEmpty(orderBy) == false)
{
var orderByParams = new[] { GetDatabaseFieldNameForOrderBy(orderBy) };
if (orderDirection == Direction.Ascending)
{
sortedSql.OrderBy(orderByParams);
}
else
{
sortedSql.OrderByDescending(orderByParams);
}
}
}
else
{
// Sorting by a custom field, so set-up sub-query for ORDER BY clause to pull through value
// from most recent content version for the given order by field
var sortedInt = string.Format(SqlSyntax.ConvertIntegerToOrderableString, "dataInt");
var sortedDate = string.Format(SqlSyntax.ConvertDateToOrderableString, "dataDate");
var sortedString = string.Format("COALESCE({0},'')", "dataNvarchar");
var sortedDecimal = string.Format(SqlSyntax.ConvertDecimalToOrderableString, "dataDecimal");
//these are defaults that will be used in the query - they can be overridden for non-versioned entities or document entities
var versionQuery = " AND cpd.versionId = cd.versionId";
var newestQuery = string.Empty;
//cmsDocument needs to filter by the 'newest' parameter in the query
if (nodeIdSelect.Item1 == "cmsDocument")
{
newestQuery = " AND cd.newest = 1";
}
//members do not use versions so clear the versionQuery string
if (nodeIdSelect.Item1 == "cmsMember")
{
versionQuery = string.Empty;
}
//needs to be an outer join since there's no guarantee that any of the nodes have values for this property
var outerJoinTempTable = string.Format(@"LEFT OUTER JOIN (
SELECT CASE
WHEN dataInt Is Not Null THEN {0}
WHEN dataDecimal Is Not Null THEN {1}
WHEN dataDate Is Not Null THEN {2}
ELSE {3}
END AS CustomPropVal,
cd.{4} AS CustomPropValContentId
FROM {5} cd
INNER JOIN cmsPropertyData cpd ON cpd.contentNodeId = cd.{4}{6}
INNER JOIN cmsPropertyType cpt ON cpt.Id = cpd.propertytypeId
WHERE cpt.Alias = @{7}{8}) AS CustomPropData
ON CustomPropData.CustomPropValContentId = umbracoNode.id
", sortedInt, sortedDecimal, sortedDate, sortedString, nodeIdSelect.Item2, nodeIdSelect.Item1, versionQuery, sortedSql.Arguments.Length, newestQuery);
//insert this just above the last WHERE
string newSql = sortedSql.SQL.Insert(sortedSql.SQL.LastIndexOf("WHERE"), outerJoinTempTable);
var newArgs = sortedSql.Arguments.ToList();
newArgs.Add(orderBy);
sortedSql = new Sql(newSql, newArgs.ToArray());
if (orderDirection == Direction.Descending)
{
sortedSql.OrderByDescending("CustomPropData.CustomPropVal");
}
else
{
sortedSql.OrderBy("CustomPropData.CustomPropVal");
}
}
if (orderBySystemField && orderBy != "umbracoNode.id")
{
//no matter what we always MUST order the result also by umbracoNode.id to ensure that all records being ordered by are unique.
// if we do not do this then we end up with issues where we are ordering by a field that has duplicate values (i.e. the 'text' column
// is empty for many nodes)
// see: http://issues.umbraco.org/issue/U4-8831
sortedSql.OrderBy("umbracoNode.id");
}
return(sortedSql);
}
private static void SetSorting(IEnumerable<SortOption> sortOptions, PocoData pocoData, Sql sql)
{
if (sortOptions?.Any() == true)
{
var sortedFields = sortOptions.OrderBy(x => x.SortIndex).ToArray()
.Select(x => $"{GetColumnName(pocoData, x.Field)} {GetSortOrder(x.SortOrder)}");
sql.Append($"ORDER BY {string.Join(",", sortedFields)}");
}
}
/// <summary>
/// Gets the property collection for a query
/// </summary>
/// <param name="pagingSqlQuery"></param>
/// <param name="documentDefs"></param>
/// <returns></returns>
protected IDictionary <Guid, PropertyCollection> GetPropertyCollection(
PagingSqlQuery pagingSqlQuery,
IReadOnlyCollection <DocumentDefinition> documentDefs)
{
if (documentDefs.Count == 0)
{
return(new Dictionary <Guid, PropertyCollection>());
}
//initialize to the query passed in
var docSql = pagingSqlQuery.PrePagedSql;
//we need to parse the original SQL statement and reduce the columns to just cmsContent.nodeId, cmsContentVersion.VersionId so that we can use
// the statement to go get the property data for all of the items by using an inner join
var parsedOriginalSql = "SELECT {0} " + docSql.SQL.Substring(docSql.SQL.IndexOf("FROM", StringComparison.Ordinal));
if (pagingSqlQuery.HasPaging)
{
//if this is a paged query, build the paged query with the custom column substitution, then re-assign
docSql = pagingSqlQuery.BuildPagedQuery("{0}");
parsedOriginalSql = docSql.SQL;
}
else if (parsedOriginalSql.InvariantContains("ORDER BY "))
{
//now remove everything from an Orderby clause and beyond if this is unpaged data
parsedOriginalSql = parsedOriginalSql.Substring(0, parsedOriginalSql.LastIndexOf("ORDER BY ", StringComparison.Ordinal));
}
//This retrieves all pre-values for all data types that are referenced for all property types
// that exist in the data set.
//Benchmarks show that eagerly loading these so that we can lazily read the property data
// below (with the use of Query intead of Fetch) go about 30% faster, so we'll eagerly load
// this now since we cannot execute another reader inside of reading the property data.
var preValsSql = new Sql(@"SELECT a.id, a.value, a.sortorder, a.alias, a.datatypeNodeId
FROM cmsDataTypePreValues a
WHERE EXISTS(
SELECT DISTINCT b.id as preValIdInner
FROM cmsDataTypePreValues b
INNER JOIN cmsPropertyType
ON b.datatypeNodeId = cmsPropertyType.dataTypeId
INNER JOIN
(" + string.Format(parsedOriginalSql, "cmsContent.contentType") + @") as docData
ON cmsPropertyType.contentTypeId = docData.contentType
WHERE a.id = b.id)", docSql.Arguments);
var allPreValues = Database.Fetch <DataTypePreValueDto>(preValsSql);
//It's Important with the sort order here! We require this to be sorted by node id,
// this is required because this data set can be huge depending on the page size. Due
// to it's size we need to be smart about iterating over the property values to build
// the document. Before we used to use Linq to get the property data for a given content node
// and perform a Distinct() call. This kills performance because that would mean if we had 7000 nodes
// and on each iteration we will perform a lookup on potentially 100,000 property rows against the node
// id which turns out to be a crazy amount of iterations. Instead we know it's sorted by this value we'll
// keep an index stored of the rows being read so we never have to re-iterate the entire data set
// on each document iteration.
var propSql = new Sql(@"SELECT cmsPropertyData.*
FROM cmsPropertyData
INNER JOIN cmsPropertyType
ON cmsPropertyData.propertytypeid = cmsPropertyType.id
INNER JOIN
(" + string.Format(parsedOriginalSql, "cmsContent.nodeId, cmsContentVersion.VersionId") + @") as docData
ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId
ORDER BY contentNodeId, versionId, propertytypeid
", docSql.Arguments);
//This does NOT fetch all data into memory in a list, this will read
// over the records as a data reader, this is much better for performance and memory,
// but it means that during the reading of this data set, nothing else can be read
// from SQL server otherwise we'll get an exception.
var allPropertyData = Database.Query <PropertyDataDto>(propSql);
var result = new Dictionary <Guid, PropertyCollection>();
var propertiesWithTagSupport = new Dictionary <string, SupportTagsAttribute>();
//used to track the resolved composition property types per content type so we don't have to re-resolve (ToArray) the list every time
var resolvedCompositionProperties = new Dictionary <int, PropertyType[]>();
//keep track of the current property data item being enumerated
var propertyDataSetEnumerator = allPropertyData.GetEnumerator();
var hasCurrent = false; // initially there is no enumerator.Current
var comparer = new DocumentDefinitionComparer(SqlSyntax);
try
{
//This must be sorted by node id because this is how we are sorting the query to lookup property types above,
// which allows us to more efficiently iterate over the large data set of property values
foreach (var def in documentDefs.OrderBy(x => x.Id).ThenBy(x => x.Version, comparer))
{
// get the resolved properties from our local cache, or resolve them and put them in cache
PropertyType[] compositionProperties;
if (resolvedCompositionProperties.ContainsKey(def.Composition.Id))
{
compositionProperties = resolvedCompositionProperties[def.Composition.Id];
}
else
{
compositionProperties = def.Composition.CompositionPropertyTypes.ToArray();
resolvedCompositionProperties[def.Composition.Id] = compositionProperties;
}
// assemble the dtos for this def
// use the available enumerator.Current if any else move to next
var propertyDataDtos = new List <PropertyDataDto>();
while (hasCurrent || propertyDataSetEnumerator.MoveNext())
{
//Not checking null on VersionId because it can never be null - no idea why it's set to nullable
// ReSharper disable once PossibleInvalidOperationException
if (propertyDataSetEnumerator.Current.VersionId.Value == def.Version)
{
hasCurrent = false; // enumerator.Current is not available
propertyDataDtos.Add(propertyDataSetEnumerator.Current);
}
else
{
hasCurrent = true; // enumerator.Current is available for another def
break; // no more propertyDataDto for this def
}
}
var properties = PropertyFactory.BuildEntity(propertyDataDtos, compositionProperties, def.CreateDate, def.VersionDate).ToArray();
foreach (var property in properties)
{
//NOTE: The benchmarks run with and without the following code show very little change so this is not a perf bottleneck
var editor = PropertyEditorResolver.Current.GetByAlias(property.PropertyType.PropertyEditorAlias);
var tagSupport = propertiesWithTagSupport.ContainsKey(property.PropertyType.PropertyEditorAlias)
? propertiesWithTagSupport[property.PropertyType.PropertyEditorAlias]
: TagExtractor.GetAttribute(editor);
if (tagSupport != null)
{
//add to local cache so we don't need to reflect next time for this property editor alias
propertiesWithTagSupport[property.PropertyType.PropertyEditorAlias] = tagSupport;
//this property has tags, so we need to extract them and for that we need the prevals which we've already looked up
var preValData = allPreValues.Where(x => x.DataTypeNodeId == property.PropertyType.DataTypeDefinitionId)
.Distinct()
.ToArray();
var asDictionary = preValData.ToDictionary(x => x.Alias, x => new PreValue(x.Id, x.Value, x.SortOrder));
var preVals = new PreValueCollection(asDictionary);
var contentPropData = new ContentPropertyData(property.Value, preVals);
TagExtractor.SetPropertyTags(property, contentPropData, property.Value, tagSupport);
}
}
if (result.ContainsKey(def.Version))
{
var msg = string.Format("The query returned multiple property sets for document definition {0}, {1}, {2}", def.Id, def.Version, def.Composition.Name);
if (ThrowOnWarning)
{
throw new InvalidOperationException(msg);
}
else
{
Logger.Warn <VersionableRepositoryBase <TId, TEntity> >(msg);
}
}
result[def.Version] = new PropertyCollection(properties);
}
}
finally
{
propertyDataSetEnumerator.Dispose();
}
return(result);
}
/// <summary>
/// A helper method for inheritors to get the paged results by query in a way that minimizes queries
/// </summary>
/// <typeparam name="TDto">The type of the d.</typeparam>
/// <param name="query">The query.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalRecords">The total records.</param>
/// <param name="nodeIdSelect">The tablename + column name for the SELECT statement fragment to return the node id from the query</param>
/// <param name="defaultFilter">A callback to create the default filter to be applied if there is one</param>
/// <param name="processQuery">A callback to process the query result</param>
/// <param name="orderBy">The order by column</param>
/// <param name="orderDirection">The order direction.</param>
/// <param name="orderBySystemField">Flag to indicate when ordering by system field</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">orderBy</exception>
protected IEnumerable <TEntity> GetPagedResultsByQuery <TDto>(IQuery <TEntity> query, long pageIndex, int pageSize, out long totalRecords,
Tuple <string, string> nodeIdSelect,
Func <Sql, PagingSqlQuery <TDto>, IEnumerable <TEntity> > processQuery,
string orderBy,
Direction orderDirection,
bool orderBySystemField,
Func <Tuple <string, object[]> > defaultFilter = null)
{
if (orderBy == null)
{
throw new ArgumentNullException("orderBy");
}
// Get base query for returning IDs
var sqlBaseIds = GetBaseQuery(BaseQueryType.Ids);
// Get base query for returning all data
var sqlBaseFull = GetBaseQuery(BaseQueryType.FullMultiple);
if (query == null)
{
query = new Query <TEntity>();
}
var translatorIds = new SqlTranslator <TEntity>(sqlBaseIds, query);
var sqlQueryIds = translatorIds.Translate();
var translatorFull = new SqlTranslator <TEntity>(sqlBaseFull, query);
var sqlQueryFull = translatorFull.Translate();
//get sorted and filtered sql
var sqlNodeIdsWithSort = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(sqlQueryIds, defaultFilter),
orderDirection, orderBy, orderBySystemField, nodeIdSelect);
// Get page of results and total count
IEnumerable <TEntity> result;
var pagedResult = Database.Page <TDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
//NOTE: We need to check the actual items returned, not the 'totalRecords', that is because if you request a page number
// that doesn't actually have any data on it, the totalRecords will still indicate there are records but there are none in
// the pageResult, then the GetAll will actually return ALL records in the db.
if (pagedResult.Items.Any())
{
//Create the inner paged query that was used above to get the paged result, we'll use that as the inner sub query
var args = sqlNodeIdsWithSort.Arguments;
string sqlStringCount, sqlStringPage;
Database.BuildPageQueries <TDto>(pageIndex * pageSize, pageSize, sqlNodeIdsWithSort.SQL, ref args, out sqlStringCount, out sqlStringPage);
//We need to make this FULL query an inner join on the paged ID query
var splitQuery = sqlQueryFull.SQL.Split(new[] { "WHERE " }, StringSplitOptions.None);
var fullQueryWithPagedInnerJoin = new Sql(splitQuery[0])
.Append("INNER JOIN (")
//join the paged query with the paged query arguments
.Append(sqlStringPage, args)
.Append(") temp ")
.Append(string.Format("ON {0}.{1} = temp.{1}", nodeIdSelect.Item1, nodeIdSelect.Item2))
//add the original where clause back with the original arguments
.Where(splitQuery[1], sqlQueryIds.Arguments);
//get sorted and filtered sql
var fullQuery = GetSortedSqlForPagedResults(
GetFilteredSqlForPagedResults(fullQueryWithPagedInnerJoin, defaultFilter),
orderDirection, orderBy, orderBySystemField, nodeIdSelect);
return(processQuery(fullQuery, new PagingSqlQuery <TDto>(Database, sqlNodeIdsWithSort, pageIndex, pageSize)));
}
else
{
result = Enumerable.Empty <TEntity>();
}
return(result);
}
public IEnumerable <Rule> GetBy(Expression <Func <Rule, bool> > predicate)
{
var query = new Sql().Select("*").From(TableConstants.Rules.TableName).Where(predicate, this.SqlSyntax);
return(this.Database.Fetch <Rule>(query));
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCAMPAIGN_TRKRS_List" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/24/2006 Paul. Use new Security.Filter() function to apply Team and ACL security rules.
Security.Filter(cmd, m_sMODULE, "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
public static T First(Sql sql)
{
return(repo.First <T>(sql));
}
public async Task API_PostgreSql_Examples_Async()
{
var db = OpenDbConnection();
db.DropAndCreateTable <Person>();
db.DropAndCreateTable <PersonWithAutoId>();
await db.InsertAsync(Person.Rockstars);
await db.SelectAsync <Person>(x => x.Age > 40);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" \nFROM \"person\"\nWHERE (\"age\" > :0)"));
await db.SelectAsync(db.From <Person>().Where(x => x.Age > 40).OrderBy(x => x.Id));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" \nFROM \"person\"\nWHERE (\"age\" > :0)\nORDER BY \"id\""));
await db.SelectAsync(db.From <Person>().Where(x => x.Age > 40));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" \nFROM \"person\"\nWHERE (\"age\" > :0)"));
await db.SingleAsync <Person>(x => x.Age == 42);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" \nFROM \"person\"\nWHERE (\"age\" = :0)\nLIMIT 1"));
await db.SingleAsync(db.From <Person>().Where(x => x.Age == 42));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" \nFROM \"person\"\nWHERE (\"age\" = :0)\nLIMIT 1"));
await db.ScalarAsync <Person, int>(x => Sql.Max(x.Age));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Max(\"age\") \nFROM \"person\""));
await db.ScalarAsync <Person, int>(x => Sql.Max(x.Age), x => x.Age < 50);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Max(\"age\") \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.CountAsync <Person>(x => x.Age < 50);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.CountAsync(db.From <Person>().Where(x => x.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.SelectAsync <Person>("age > 40");
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age > 40"));
await db.SelectAsync <Person>("SELECT * FROM person WHERE age > 40");
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age > 40"));
await db.SelectAsync <Person>("age > :Age", new { age = 40 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age > :Age"));
await db.SelectAsync <Person>("SELECT * FROM person WHERE age > :Age", new { age = 40 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age > :Age"));
await db.SelectAsync <Person>("SELECT * FROM person WHERE age > :Age", new[] { db.CreateParam("age", 40) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age > :Age"));
await db.SelectAsync <Person>("age > :Age", new Dictionary <string, object> {
{ "age", 40 }
});
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age > :Age"));
await db.SelectAsync <Person>("SELECT * FROM person WHERE age > :Age", new Dictionary <string, object> {
{ "age", 40 }
});
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age > :Age"));
await db.SelectAsync <EntityWithId>(typeof(Person));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\" FROM \"person\""));
await db.SelectAsync <EntityWithId>(typeof(Person), "age > @age", new { age = 40 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\" FROM \"person\" WHERE age > @age"));
await db.WhereAsync <Person>("Age", 27);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"age\" = :Age"));
await db.WhereAsync <Person>(new { Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"age\" = :Age"));
await db.SelectByIdsAsync <Person>(new[] { 1, 2, 3 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"id\" IN (:0,:1,:2)"));
await db.SelectNonDefaultsAsync(new Person { Id = 1 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"id\" = :Id"));
await db.SelectNonDefaultsAsync("age > :Age", new Person { Age = 40 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age > :Age"));
await db.SingleByIdAsync <Person>(1);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"id\" = :Id"));
await db.SingleAsync <Person>(new { Age = 42 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"age\" = :Age"));
await db.SingleAsync <Person>("age = :Age", new { age = 42 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age = :Age"));
await db.SingleAsync <Person>("age = :Age", new[] { db.CreateParam("age", 42) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age = :Age"));
await db.SingleByIdAsync <Person>(1);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"id\" = :Id"));
await db.SingleWhereAsync <Person>("Age", 42);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"age\" = :Age"));
await db.ScalarAsync <int>(db.From <Person>().Select(Sql.Count("*")).Where(q => q.Age > 40));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"person\"\nWHERE (\"age\" > :0)"));
await db.ScalarAsync <int>(db.From <Person>().Select(x => Sql.Count("*")).Where(q => q.Age > 40));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Count(*) \nFROM \"person\"\nWHERE (\"age\" > :0)"));
await db.ScalarAsync <int>("SELECT COUNT(*) FROM person WHERE age > :Age", new { age = 40 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM person WHERE age > :Age"));
await db.ScalarAsync <int>("SELECT COUNT(*) FROM person WHERE age > :Age", new[] { db.CreateParam("age", 40) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM person WHERE age > :Age"));
await db.ColumnAsync <string>(db.From <Person>().Select(x => x.LastName).Where(q => q.Age == 27));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"last_name\" \nFROM \"person\"\nWHERE (\"age\" = :0)"));
await db.ColumnAsync <string>("SELECT last_name FROM person WHERE age = :Age", new { age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT last_name FROM person WHERE age = :Age"));
await db.ColumnAsync <string>("SELECT last_name FROM person WHERE age = :Age", new[] { db.CreateParam("age", 27) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT last_name FROM person WHERE age = :Age"));
await db.ColumnDistinctAsync <int>(db.From <Person>().Select(x => x.Age).Where(q => q.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"age\" \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.ColumnDistinctAsync <int>("SELECT age FROM person WHERE age < :Age", new { age = 50 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT age FROM person WHERE age < :Age"));
await db.ColumnDistinctAsync <int>("SELECT age FROM person WHERE age < :Age", new[] { db.CreateParam("age", 50) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT age FROM person WHERE age < :Age"));
await db.LookupAsync <int, string>(db.From <Person>().Select(x => new { x.Age, x.LastName }).Where(q => q.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"age\", \"last_name\" \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.LookupAsync <int, string>("SELECT age, last_name FROM person WHERE age < :Age", new { age = 50 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT age, last_name FROM person WHERE age < :Age"));
await db.LookupAsync <int, string>("SELECT age, last_name FROM person WHERE age < :Age", new[] { db.CreateParam("age", 50) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT age, last_name FROM person WHERE age < :Age"));
await db.DictionaryAsync <int, string>(db.From <Person>().Select(x => new { x.Id, x.LastName }).Where(x => x.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"last_name\" \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.DictionaryAsync <int, string>("SELECT id, last_name FROM person WHERE age < :Age", new { age = 50 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT id, last_name FROM person WHERE age < :Age"));
await db.DictionaryAsync <int, string>("SELECT id, last_name FROM person WHERE age < :Age", new[] { db.CreateParam("age", 50) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT id, last_name FROM person WHERE age < :Age"));
await db.ExistsAsync <Person>(x => x.Age < 50);
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT 'exists' \nFROM \"person\"\nWHERE (\"age\" < :0)\nLIMIT 1"));
await db.ExistsAsync(db.From <Person>().Where(x => x.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT 'exists' \nFROM \"person\"\nWHERE (\"age\" < :0)\nLIMIT 1"));
await db.ExistsAsync <Person>(new { Age = 42 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE \"age\" = :Age"));
await db.ExistsAsync <Person>("age = :Age", new { age = 42 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"id\", \"first_name\", \"last_name\", \"age\" FROM \"person\" WHERE age = :Age"));
await db.ExistsAsync <Person>("SELECT * FROM person WHERE age = :Age", new { age = 42 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age = :Age"));
await db.SqlListAsync <Person>(db.From <Person>().Select("*").Where(q => q.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.SqlListAsync <Person>("SELECT * FROM person WHERE age < :Age", new { age = 50 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age < :Age"));
await db.SqlListAsync <Person>("SELECT * FROM person WHERE age < :Age", new[] { db.CreateParam("age", 50) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age < :Age"));
await db.SqlListAsync <Person>("SELECT * FROM person WHERE age < :Age", new Dictionary <string, object> {
{ "age", 50 }
});
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM person WHERE age < :Age"));
await db.SqlColumnAsync <string>(db.From <Person>().Select(x => x.LastName).Where(q => q.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"last_name\" \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.SqlColumnAsync <string>("SELECT last_name FROM person WHERE age < :Age", new { age = 50 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT last_name FROM person WHERE age < :Age"));
await db.SqlColumnAsync <string>("SELECT last_name FROM person WHERE age < :Age", new[] { db.CreateParam("age", 50) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT last_name FROM person WHERE age < :Age"));
await db.SqlColumnAsync <string>("SELECT last_name FROM person WHERE age < :Age", new Dictionary <string, object> {
{ "age", 50 }
});
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT last_name FROM person WHERE age < :Age"));
await db.SqlScalarAsync <int>(db.From <Person>().Select(Sql.Count("*")).Where(q => q.Age < 50));
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"person\"\nWHERE (\"age\" < :0)"));
await db.SqlScalarAsync <int>("SELECT COUNT(*) FROM person WHERE age < :Age", new { age = 50 });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM person WHERE age < :Age"));
await db.SqlScalarAsync <int>("SELECT COUNT(*) FROM person WHERE age < :Age", new[] { db.CreateParam("age", 50) });
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM person WHERE age < :Age"));
await db.SqlScalarAsync <int>("SELECT COUNT(*) FROM person WHERE age < :Age", new Dictionary <string, object> {
{ "age", 50 }
});
Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM person WHERE age < :Age"));
var rowsAffected = await db.ExecuteNonQueryAsync("UPDATE Person SET last_name=@name WHERE id=:Id", new { name = "WaterHouse", id = 7 });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE Person SET last_name=@name WHERE id=:Id"));
await db.InsertAsync(new Person { Id = 7, FirstName = "Amy", LastName = "Winehouse", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"person\" (\"id\",\"first_name\",\"last_name\",\"age\") VALUES (:Id,:FirstName,:LastName,:Age)"));
await db.InsertAsync(new Person { Id = 8, FirstName = "Tupac", LastName = "Shakur", Age = 25 },
new Person { Id = 9, FirstName = "Tupac", LastName = "Shakur2", Age = 26 });
Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"person\" (\"id\",\"first_name\",\"last_name\",\"age\") VALUES (:Id,:FirstName,:LastName,:Age)"));
await db.InsertAllAsync(new[] { new Person {
Id = 10, FirstName = "Biggie", LastName = "Smalls", Age = 24
} });
Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"person\" (\"id\",\"first_name\",\"last_name\",\"age\") VALUES (:Id,:FirstName,:LastName,:Age)"));
await db.InsertOnlyAsync(new PersonWithAutoId { FirstName = "Amy", Age = 27 }, p => new { p.FirstName, p.Age });
Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"person_with_auto_id\" (\"first_name\",\"age\") VALUES (:FirstName,:Age)"));
await db.InsertOnlyAsync(() => new PersonWithAutoId { FirstName = "Amy", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"person_with_auto_id\" (\"first_name\",\"age\") VALUES (:FirstName,:Age)"));
await db.UpdateAsync(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName, \"last_name\"=:LastName, \"age\"=:Age WHERE \"id\"=:Id"));
await db.UpdateAsync(new Person { Id = 8, FirstName = "Tupac", LastName = "Shakur3", Age = 27 },
new Person { Id = 9, FirstName = "Tupac", LastName = "Shakur4", Age = 28 });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName, \"last_name\"=:LastName, \"age\"=:Age WHERE \"id\"=:Id"));
await db.UpdateAsync(new[] { new Person {
Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27
} });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName, \"last_name\"=:LastName, \"age\"=:Age WHERE \"id\"=:Id"));
await db.UpdateAllAsync(new[] { new Person {
Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27
} });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName, \"last_name\"=:LastName, \"age\"=:Age WHERE \"id\"=:Id"));
await db.UpdateAsync(new Person { Id = 1, FirstName = "JJ", Age = 27 }, p => p.LastName == "Hendrix");
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"id\"=:Id, \"first_name\"=:FirstName, \"last_name\"=:LastName, \"age\"=:Age WHERE (\"last_name\" = :0)"));
await db.UpdateAsync <Person>(new { FirstName = "JJ" }, p => p.LastName == "Hendrix");
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName WHERE (\"last_name\" = :0)"));
await db.UpdateNonDefaultsAsync(new Person { FirstName = "JJ" }, p => p.LastName == "Hendrix");
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName WHERE (\"last_name\" = :0)"));
await db.UpdateOnlyFieldsAsync(new Person { FirstName = "JJ" }, p => p.FirstName);
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName"));
await db.UpdateOnlyAsync(() => new Person { FirstName = "JJ" });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName"));
await db.UpdateOnlyFieldsAsync(new Person { FirstName = "JJ" }, p => p.FirstName, p => p.LastName == "Hendrix");
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName WHERE (\"last_name\" = :0)"));
await db.UpdateOnlyAsync(() => new Person { FirstName = "JJ" }, p => p.LastName == "Hendrix");
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName WHERE (\"last_name\" = :0)"));
await db.UpdateOnlyFieldsAsync(new Person { FirstName = "JJ", LastName = "Hendo" }, db.From <Person>().Update(p => p.FirstName));
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName"));
await db.UpdateOnlyFieldsAsync(new Person { FirstName = "JJ" }, db.From <Person>().Update(p => p.FirstName).Where(x => x.FirstName == "Jimi"));
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName WHERE (\"first_name\" = :0)"));
await db.UpdateAddAsync(() => new Person { Age = 3 });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"age\"=\"age\"+:Age"));
await db.UpdateAddAsync(() => new Person { Age = 5 }, where : x => x.LastName == "Presley");
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"age\"=\"age\"+:Age WHERE (\"last_name\" = :0)"));
await db.DeleteAsync <Person>(new { FirstName = "Jimi", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE \"first_name\"=:FirstName AND \"age\"=:Age"));
await db.DeleteAsync(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE \"id\"=:Id AND \"first_name\"=:FirstName AND \"last_name\"=:LastName AND \"age\"=:Age"));
await db.DeleteNonDefaultsAsync(new Person { FirstName = "Jimi", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE \"first_name\"=:FirstName AND \"age\"=:Age"));
await db.DeleteNonDefaultsAsync(new Person { FirstName = "Jimi", Age = 27 },
new Person { FirstName = "Janis", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE \"first_name\"=:FirstName AND \"age\"=:Age"));
await db.DeleteByIdAsync <Person>(1);
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE \"id\" = :0"));
await db.DeleteByIdsAsync <Person>(new[] { 1, 2, 3 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE \"id\" IN (:0,:1,:2)"));
await db.DeleteAsync <Person>("age = @age", new { age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE age = @age"));
await db.DeleteAsync(typeof(Person), "age = @age", new { age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE age = @age"));
await db.DeleteAsync <Person>(p => p.Age == 27);
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE (\"age\" = :0)"));
await db.DeleteAsync(db.From <Person>().Where(p => p.Age == 27));
Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"person\" WHERE (\"age\" = :0)"));
await db.SaveAsync(new Person { Id = 11, FirstName = "Amy", LastName = "Winehouse", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"person\" (\"id\",\"first_name\",\"last_name\",\"age\") VALUES (:Id,:FirstName,:LastName,:Age)"));
await db.SaveAsync(new Person { Id = 11, FirstName = "Amy", LastName = "Winehouse", Age = 27 });
Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"person\" SET \"first_name\"=:FirstName, \"last_name\"=:LastName, \"age\"=:Age WHERE \"id\"=:Id"));
await db.SaveAsync(new Person { Id = 12, FirstName = "Amy", LastName = "Winehouse", Age = 27 },
new Person { Id = 13, FirstName = "Amy", LastName = "Winehouse", Age = 27 });
await db.SaveAllAsync(new[] { new Person {
Id = 14, FirstName = "Amy", LastName = "Winehouse", Age = 27
},
new Person {
Id = 15, FirstName = "Amy", LastName = "Winehouse", Age = 27
} });
db.Dispose();
}
public static ColumnConfig getConfig(double id)
{
bool inherit = false;
double classId = 0, moduleId = 0;
string parentId = "";
ColumnConfig config = new ColumnConfig();
MySqlDataReader rs = Sql.ExecuteReader("select thumbnailWidth,thumbnailHeight,thumbnailForce,saveRemoteImages,inherit,classId,parentId,moduleId,titleRepeat,watermark,childId from class where id=@id", new MySqlParameter[] {
new MySqlParameter("id", id)
});
if (rs.Read())
{
inherit = rs.GetInt32(4) == 1;
config.picForce = rs.GetInt32(2) == 1;
config.picSave = rs.GetInt32(3) == 1;
config.picWidth = rs.GetInt32(0);
config.picHeight = rs.GetInt32(1);
classId = rs.GetDouble(5);
parentId = rs.GetString(6);
moduleId = rs.GetDouble(7);
config.titleRepeat = (rs.IsDBNull(8) || rs.GetInt32(8) == 1);
config.isRoot = rs.GetDouble(5) == 7;
config.isColumn = rs.GetDouble(5) != 7;
config.isModule = false;
config.pId = id;
config.watermarkFlag = rs.IsDBNull(9) || rs.GetInt32(9) == 1;
config.childId = rs.GetString(10);
}
rs.Close();
if (inherit)
{
string sql = "";
if (classId == 7)
{
rs = Sql.ExecuteReader("select thumbnailWidth,thumbnailHeight,thumbnailForce,saveRemoteImages,titleRepeat,watermark from module where id=@moduleId", new MySqlParameter[] { new MySqlParameter("moduleId", moduleId) });
if (rs.Read())
{
config.picForce = rs.GetInt32(2) == 1;
config.picSave = rs.GetInt32(3) == 1;
config.picWidth = rs.GetInt32(0);
config.picHeight = rs.GetInt32(1);
config.titleRepeat = (rs.IsDBNull(4) || rs.GetInt32(4) == 1);
config.isModule = true;
config.isRoot = false;
config.isColumn = false;
config.pId = moduleId;
config.watermarkFlag = rs.IsDBNull(5) || rs.GetInt32(5) == 1;
}
rs.Close();
}
else
{
sql = "select thumbnailWidth,thumbnailHeight,thumbnailForce,saveRemoteImages,titleRepeat,classId,childId,id,watermark from class where id in (" + parentId + ") and inherit=0 order by layer desc ";
bool flag = false;
rs = Sql.ExecuteReader(sql);
if (rs.Read())
{
flag = true;
config.picForce = rs.GetInt32(2) == 1;
config.picSave = rs.GetInt32(3) == 1;
config.picWidth = rs.GetInt32(0);
config.picHeight = rs.GetInt32(1);
config.titleRepeat = (rs.IsDBNull(4) || rs.GetInt32(4) == 1);
config.isRoot = rs.GetDouble(5) == 7;
config.isColumn = rs.GetDouble(5) != 7;
config.isModule = false;
config.childId = rs.GetString(6);
config.pId = rs.GetDouble(7);
config.watermarkFlag = rs.IsDBNull(8) || rs.GetInt32(8) == 1;
}
rs.Close();
if (!flag)//从模块中查找配制
{
rs = Sql.ExecuteReader("select thumbnailWidth,thumbnailHeight,thumbnailForce,saveRemoteImages,titleRepeat,watermark from module where id=@moduleId", new MySqlParameter[] { new MySqlParameter("moduleId", moduleId) });
if (rs.Read())
{
config.picForce = rs.GetInt32(2) == 1;
config.picSave = rs.GetInt32(3) == 1;
config.picWidth = rs.GetInt32(0);
config.picHeight = rs.GetInt32(1);
config.titleRepeat = (rs.IsDBNull(4) || rs.GetInt32(4) == 1);
config.isModule = true;
config.isRoot = false;
config.isColumn = false;
config.pId = moduleId;
config.watermarkFlag = rs.IsDBNull(5) || rs.GetInt32(5) == 1;
}
rs.Close();
}
}
return(config);
}
else
{
return(config);
}
}
public static ReturnValue move(double classId, double moduleId1, double classId1, UserInfo user)
{
ReturnValue err = new ReturnValue();
if (classId == classId1)
{
err.errNo = -1;
err.errMsg = "移动栏目不能为父栏目";
return(err);
}
bool mtag2 = false, tag = true;
double dataTypeId2 = -1;
MySqlDataReader rs;
rs = Sql.ExecuteReader("select type,saveDataType from module where id=@id", new MySqlParameter[] { new MySqlParameter("id", moduleId1) });
if (rs.Read())
{
dataTypeId2 = rs.GetDouble(1);
mtag2 = rs.GetBoolean(0);
if (moduleId1 == classId1 && !mtag2)
{
classId1 = 7;
}
}
rs.Close();
if (dataTypeId2 == -1)
{
err.errNo = -1;
err.errMsg = "目标模块不存在";
return(err);
}
ColumnInfo column = ColumnClass.get(classId);
Permissions p = null;
#region 获取要移动栏目的父栏目权限
if (column.classId == 7)
{
p = user.getModulePermissions(column.moduleId);
}
else
{
p = user.getColumnPermissions(column.classId);
}
if (!p.all)
{
err.errNo = -1;
err.errMsg = "越权操作，移动栏目失败";
return(err);
}
#endregion
#region 获取目标位置权限
if (classId1 == 7)
{
p = user.getModulePermissions(moduleId1);
}
else
{
p = user.getColumnPermissions(classId1);
}
if (!p.all)
{
err.errNo = -1;
err.errMsg = "越权操作，无目标栏目权限，移动栏目失败";
return(err);
}
#endregion
#region 栏目移动
if (column.saveDataType == dataTypeId2)
{
string where = "";
if (classId1 != 7)
{
rs = Sql.ExecuteReader("select rootid from class where id=@id", new MySqlParameter[] { new MySqlParameter("id", classId1) });
if (rs.Read())
{
where = ",rootid=" + rs[0].ToString();
}
rs.Close();
}
Sql.ExecuteNonQuery("update class set moduleid=" + moduleId1 + ",classid=" + classId1 + where + " where id=" + classId);
ColumnClass.reset(column.rootId);//重置旧栏目结构
}
else
{
err.errMsg = "数据类型不匹配";
err.errNo = -1;
tag = false;//移动失败，类型不匹配
}
#endregion
return(err);
}
public static void add(ColumnInfo info, UserInfo user)
{
ReturnValue err = new ReturnValue();
#region 验证
if (info.className.Trim() == "")
{
throw new Exception("栏目名不能为空");
}
if (info.dirName == "")
{
throw new Exception("目录名不能为空");
}
#endregion
if (info.id < 1)
{
info.id = double.Parse(Tools.GetId());
}
int count = int.Parse(Sql.ExecuteScalar("select count(1) from class where classId=@classId and (dirName=@dirName or className=@className)", new MySqlParameter[] {
new MySqlParameter("classId", info.classId),
new MySqlParameter("dirName", info.dirName.ToLower()),
new MySqlParameter("className", info.className)
}).ToString());
if (count > 0)
{
throw new Exception("所在栏目下栏目名或目录名已存在");
}
downFiles(ref info.maxIco);
Sql.ExecuteNonQuery("insert into class " +
"(id,className,classId,moduleId,rootId,keyword,maxIco,skinId,contentSkinId,info,custom,thumbnailWidth,thumbnailHeight,thumbnailForce,saveRemoteImages,inherit,domainName,updateDate,createDate,userId,childId,pddir,dirPath,parentId,saveDataType,dirName,layer,orderId,titleRepeat,watermark,_domainName)" +
"values" +
"(@id,@className,@classId,@moduleId,@rootId,@keyword,@maxIco,@skinId,@contentSkinId,@info,@custom,@thumbnailWidth,@thumbnailHeight,@thumbnailForce,@saveRemoteImages,@inherit,@domainName,@updateDate,@createDate,@userId,@childId,@pddir,@dirPath,@parentId,@saveDataType,@dirName,@layer,0,@titleRepeat,@watermark,@_domainName)", new MySqlParameter[] {
new MySqlParameter("id", info.id),
new MySqlParameter("className", info.className),
new MySqlParameter("classId", info.classId),
new MySqlParameter("moduleId", info.moduleId),
new MySqlParameter("rootId", info.rootId),
new MySqlParameter("keyword", info.keyword),
new MySqlParameter("maxIco", info.maxIco),
new MySqlParameter("skinId", info.skinId),
new MySqlParameter("contentSkinId", info.contentSkinId),
new MySqlParameter("info", info.info),
new MySqlParameter("custom", info.custom),
new MySqlParameter("thumbnailWidth", info.thumbnailWidth),
new MySqlParameter("thumbnailHeight", info.thumbnailHeight),
new MySqlParameter("thumbnailForce", info.thumbnailForce),
new MySqlParameter("saveRemoteImages", info.saveRemoteImages),
new MySqlParameter("inherit", info.inherit),
new MySqlParameter("domainName", info.domainName),
new MySqlParameter("_domainName", info._domainName),
new MySqlParameter("createDate", info.createDate),
new MySqlParameter("updateDate", info.updateDate),
new MySqlParameter("userId", user.id),
new MySqlParameter("childId", info.childId),
new MySqlParameter("dirPath", info.dirPath),
new MySqlParameter("pddir", info.pddir),
new MySqlParameter("parentId", info.parentId),
new MySqlParameter("saveDataType", info.saveDataType),
new MySqlParameter("dirName", info.dirName),
new MySqlParameter("layer", info.layer),
new MySqlParameter("titleRepeat", info.titleRepeat),
new MySqlParameter("watermark", info.watermark)
});
RecordClass.addKeyword(info.id, info.keyword, 0);
reset(info.id);
}
public static ReturnValue del(double id, UserInfo user)
{
ReturnValue err = new ReturnValue();
string childId = null;
double classId = 0;
string parentId = null;
string dirName = null;
string className = null;
double rootId = 0;
double moduleId = 0;
double userId = 0;
MySqlDataReader rs = Sql.ExecuteReader("select childId,classId,parentId,dirname,className,rootid,moduleId,userId from class where id=@id", new MySqlParameter[] { new MySqlParameter("id", id) });
if (rs.Read())
{
childId = rs[0].ToString();
classId = rs.GetDouble(1);
parentId = rs[2].ToString();
dirName = rs[3].ToString();
className = rs[4].ToString();
rootId = rs.GetDouble(5);
moduleId = rs.GetDouble(6);
userId = rs.GetDouble(7);
}
rs.Close();
if (childId == null)
{
err.errNo = -1;
err.errMsg = "模块不存在";
return(err);
}
Permissions p = null;
if (classId < 8)
{
p = user.getModulePermissions(moduleId);
}
else
{
p = user.getColumnPermissions(classId);
}
if ((p.write & p.delete && userId == user.id) | p.all)//有上级栏目决定权限
{
rs = Sql.ExecuteReader("select savedatatype,id from class where id in (" + childId + ")");
while (rs.Read())
{
object tablename = Sql.ExecuteScalar("select tablename from datatype where id=@id", new MySqlParameter[] { new MySqlParameter("id", rs.GetDouble(0)) });
if (tablename != null)
{
Sql.ExecuteNonQuery("delete from " + (string)tablename + " where id in (select id from maintable where classid=@id )", new MySqlParameter[] { new MySqlParameter("id", rs.GetDouble(1)) });
Sql.ExecuteNonQuery("delete from maintable where classid=@id", new MySqlParameter[] { new MySqlParameter("id", rs.GetDouble(1)) });
}
}
rs.Close();
int Count = Sql.ExecuteNonQuery("delete from class where id in (" + childId + ")");
if (Count > 0)
{
if (classId != 7)
{
reset(rootId);
}
else
{
Sql.ExecuteNonQuery("delete from htmltemplate where classid=@id",
new MySqlParameter[] { new MySqlParameter("id", id) });
}
}
err.errNo = 0;
Tools.writeLog("1", "删除栏目" + className);
}
else
{
err.errNo = -1;
err.errMsg = "越权操作，删除栏目失败";
Tools.writeLog("2", "越权操作，删除栏目" + className + "失败");
}
return(err);
}
public static List <T> Fetch(Sql sql)
{
return(repo.Fetch <T>(sql));
}
public IEnumerable <Rule> Get()
{
var query = new Sql().Select("*").From(TableConstants.Rules.TableName);
return(this.Database.Fetch <Rule>(query));
}
public static T Single(Sql sql)
{
return(repo.Single <T>(sql));
}
public PagingSqlQuery(Sql prePagedSql)
{
PrePagedSql = prePagedSql;
}
public static T FirstOrDefault(Sql sql)
{
return(repo.FirstOrDefault <T>(sql));
}
/// <summary>
/// Gets the property collection for a non-paged query
/// </summary>
/// <param name="sql"></param>
/// <param name="documentDefs"></param>
/// <returns></returns>
protected IDictionary <Guid, PropertyCollection> GetPropertyCollection(
Sql sql,
IReadOnlyCollection <DocumentDefinition> documentDefs)
{
return(GetPropertyCollection(new PagingSqlQuery(sql), documentDefs));
}
public static T SingleOrDefault(Sql sql)
{
return(repo.SingleOrDefault <T>(sql));
}
public static int Delete(Sql sql)
{
return(repo.Delete <T>(sql));
}
protected override void Page_Command(object sender, CommandEventArgs e)
{
try
{
if (e.CommandName == "Layout.Edit")
{
if (ctlNewRecord != null)
{
ctlNewRecord.Clear();
int nFieldIndex = Sql.ToInteger(e.CommandArgument);
DataView vwFields = new DataView(dtFields);
vwFields.RowFilter = "DELETED = 0 and " + LayoutIndexName() + " = " + nFieldIndex.ToString();
if (vwFields.Count == 1)
{
foreach (DataRowView row in vwFields)
{
ctlNewRecord.FIELD_ID = Sql.ToGuid(row["ID"]);
ctlNewRecord.FIELD_INDEX = Sql.ToInteger(row[LayoutIndexName()]);
ctlNewRecord.FIELD_TYPE = Sql.ToString(row[LayoutTypeName()]);
ctlNewRecord.DATA_FORMAT = Sql.ToString(row["DATA_FORMAT"]);
ctlNewRecord.DATA_LABEL = Sql.ToString(row["HEADER_TEXT"]);
ctlNewRecord.DATA_FIELD = Sql.ToString(row["DATA_FIELD"]);
ctlNewRecord.SORT_EXPRESSION = Sql.ToString(row["SORT_EXPRESSION"]);
ctlNewRecord.ITEMSTYLE_WIDTH = Sql.ToString(row["ITEMSTYLE_WIDTH"]);
ctlNewRecord.ITEMSTYLE_CSSCLASS = Sql.ToString(row["ITEMSTYLE_CSSCLASS"]);
ctlNewRecord.ITEMSTYLE_HORIZONTAL_ALIGN = Sql.ToString(row["ITEMSTYLE_HORIZONTAL_ALIGN"]);
ctlNewRecord.ITEMSTYLE_VERTICAL_ALIGN = Sql.ToString(row["ITEMSTYLE_VERTICAL_ALIGN"]);
ctlNewRecord.ITEMSTYLE_WRAP = Sql.ToBoolean(row["ITEMSTYLE_WRAP"]);
ctlNewRecord.URL_FIELD = Sql.ToString(row["URL_FIELD"]);
ctlNewRecord.URL_FORMAT = Sql.ToString(row["URL_FORMAT"]);
ctlNewRecord.URL_TARGET = Sql.ToString(row["URL_TARGET"]);
ctlNewRecord.URL_MODULE = Sql.ToString(row["URL_MODULE"]);
ctlNewRecord.URL_ASSIGNED_FIELD = Sql.ToString(row["URL_ASSIGNED_FIELD"]);
ctlNewRecord.LIST_NAME = Sql.ToString(row["LIST_NAME"]);
ctlNewRecord.Visible = true;
break;
}
}
}
}
else if (e.CommandName == "NewRecord.Save")
{
if (ctlNewRecord != null)
{
DataView vwFields = new DataView(dtFields);
vwFields.RowFilter = "DELETED = 0 and ID = '" + ctlNewRecord.FIELD_ID + "'";
if (vwFields.Count == 1)
{
// 01/09/2006 Paul. Make sure to use ToDBString to convert empty stings to NULL.
foreach (DataRowView row in vwFields)
{
row[LayoutTypeName()] = Sql.ToDBString(ctlNewRecord.FIELD_TYPE);
row["DATA_FORMAT"] = Sql.ToDBString(ctlNewRecord.DATA_FORMAT);
row["HEADER_TEXT"] = Sql.ToDBString(ctlNewRecord.DATA_LABEL);
row["DATA_FIELD"] = Sql.ToDBString(ctlNewRecord.DATA_FIELD);
row["SORT_EXPRESSION"] = Sql.ToDBString(ctlNewRecord.SORT_EXPRESSION);
row["ITEMSTYLE_WIDTH"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_WIDTH);
row["ITEMSTYLE_CSSCLASS"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_CSSCLASS);
row["ITEMSTYLE_HORIZONTAL_ALIGN"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_HORIZONTAL_ALIGN);
row["ITEMSTYLE_VERTICAL_ALIGN"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_VERTICAL_ALIGN);
row["ITEMSTYLE_WRAP"] = Sql.ToDBBoolean(ctlNewRecord.ITEMSTYLE_WRAP);
row["URL_FIELD"] = Sql.ToDBString(ctlNewRecord.URL_FIELD);
row["URL_FORMAT"] = Sql.ToDBString(ctlNewRecord.URL_FORMAT);
row["URL_TARGET"] = Sql.ToDBString(ctlNewRecord.URL_TARGET);
row["URL_MODULE"] = Sql.ToDBString(ctlNewRecord.URL_MODULE);
row["URL_ASSIGNED_FIELD"] = Sql.ToDBString(ctlNewRecord.URL_ASSIGNED_FIELD);
row["LIST_NAME"] = Sql.ToDBString(ctlNewRecord.LIST_NAME);
break;
}
}
else
{
// 01/08/2006 Paul. If not found, then insert a new field.
if (ctlNewRecord.FIELD_INDEX == -1)
{
ctlNewRecord.FIELD_INDEX = DynamicTableNewFieldIndex();
}
else
{
// Make room for the new record.
DynamicTableInsert(ctlNewRecord.FIELD_INDEX);
}
// 01/09/2006 Paul. Make sure to use ToDBString to convert empty stings to NULL.
DataRow row = dtFields.NewRow();
dtFields.Rows.Add(row);
row["ID"] = Guid.NewGuid();
row["DELETED"] = 0;
row["GRID_NAME"] = Sql.ToDBString(ViewState["LAYOUT_VIEW_NAME"]);
row[LayoutIndexName()] = Sql.ToDBInteger(ctlNewRecord.FIELD_INDEX);
row[LayoutTypeName()] = Sql.ToDBString(ctlNewRecord.FIELD_TYPE);
row["DATA_FORMAT"] = Sql.ToDBString(ctlNewRecord.DATA_FORMAT);
row["HEADER_TEXT"] = Sql.ToDBString(ctlNewRecord.DATA_LABEL);
row["DATA_FIELD"] = Sql.ToDBString(ctlNewRecord.DATA_FIELD);
row["SORT_EXPRESSION"] = Sql.ToDBString(ctlNewRecord.SORT_EXPRESSION);
row["ITEMSTYLE_WIDTH"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_WIDTH);
row["ITEMSTYLE_CSSCLASS"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_CSSCLASS);
row["ITEMSTYLE_HORIZONTAL_ALIGN"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_HORIZONTAL_ALIGN);
row["ITEMSTYLE_VERTICAL_ALIGN"] = Sql.ToDBString(ctlNewRecord.ITEMSTYLE_VERTICAL_ALIGN);
row["ITEMSTYLE_WRAP"] = Sql.ToDBBoolean(ctlNewRecord.ITEMSTYLE_WRAP);
row["URL_FIELD"] = Sql.ToDBString(ctlNewRecord.URL_FIELD);
row["URL_FORMAT"] = Sql.ToDBString(ctlNewRecord.URL_FORMAT);
row["URL_TARGET"] = Sql.ToDBString(ctlNewRecord.URL_TARGET);
row["URL_MODULE"] = Sql.ToDBString(ctlNewRecord.URL_MODULE);
row["URL_ASSIGNED_FIELD"] = Sql.ToDBString(ctlNewRecord.URL_ASSIGNED_FIELD);
row["LIST_NAME"] = Sql.ToDBString(ctlNewRecord.LIST_NAME);
}
SaveFieldState();
LayoutView_Bind();
if (ctlNewRecord != null)
{
ctlNewRecord.Clear();
}
}
}
else if (e.CommandName == "Defaults")
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwGRIDVIEWS_COLUMNS " + ControlChars.CrLf
+ " where GRID_NAME = @GRID_NAME " + ControlChars.CrLf
+ " and DEFAULT_VIEW = 1 " + ControlChars.CrLf
+ " order by " + LayoutIndexName() + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@GRID_NAME", Sql.ToString(ViewState["LAYOUT_VIEW_NAME"]));
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
//dtFields = new DataTable();
// 01/09/2006 Paul. Mark existing records for deletion.
// This is so that the save operation can update only records that have changed.
foreach (DataRow row in dtFields.Rows)
{
row["DELETED"] = 1;
}
da.Fill(dtFields);
// 01/09/2006 Paul. We need to change the IDs for two reasons, one is to prevent updating the Default Values,
// the second reason is that we need the row to get a Modified state. Otherwise the update loop will skip it.
foreach (DataRow row in dtFields.Rows)
{
if (Sql.ToInteger(row["DELETED"]) == 0)
{
row["ID"] = Guid.NewGuid();
}
}
}
}
}
SaveFieldState();
LayoutView_Bind();
if (ctlNewRecord != null)
{
ctlNewRecord.Clear();
}
}
else
{
base.Page_Command(sender, e);
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlLayoutButtons.ErrorText = ex.Message;
}
}
//[EAAuthorize(FunctionName = "FinanceInfo", Writable = false)]
public PartialViewResult _FinanceInfo(int?mode, DateTime?FromDate, DateTime?ToDate, int?DriverID, bool?All, bool?Paid, bool?Pending, int?SupplierID, string AgentId, int?CustomerID)
{
PaymentViewModel paymentdetailvws = new PaymentViewModel();
Sql leftsq = new Sql();
Sql rightsq = new Sql();
if (mode == (int)PayToEnum.Driver)
{
leftsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, " +
"srd.SRDID,srq.SRID, srd.Cost, srd.AgentPaymentId,srd.SupplierPaymentId,srd.CancelledAgentPaymentId,srd.CancelledSupplierPaymentId,srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo " +
"from SRdetails srd, ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and IsLead = 1 and IsCancelled is null " +
$"and DriverID = {DriverID} and srd.PayTo = 'Payed to us' and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
rightsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, " +
"srd.SRDID,srq.SRID, srd.Cost, srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo,srd.AgentPaymentId,srd.SupplierPaymentId " +
"from SRdetails srd, ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and IsLead = 1 and IsCancelled is null " +
$"and DriverID = {DriverID} and srd.PayTo = 'Pay to driver' and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
}
if (mode == (int)PayToEnum.Supplier)
{
leftsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, " +
"srd.SRDID,srq.SRID, srd.Cost, srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo,Coalesce(IsCancelled,0) as IsCancelled,srd.SupplierPaymentId " +
"from SRdetails srd, ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and IsLead = 1 " +
$"and IsCancelled is null and SupplierID = {SupplierID} and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
rightsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, srd.SRDID,srq.SRID, srd.Cost, srq.BookingNo,srd.CancelledSupplierPaymentId, srd.Fdate, SellPrice, srd.PayTo,srd.SupplierPaymentId, " +
"rf.SRDID,rf.ProdCanxCost, rf.SBCanxCost,Coalesce(IsCancelled,0) as IsCancelled, rf.RefundId from SRdetails srd, ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc, Refunds rf " +
"where sc.SRDID = srd.SRDID and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID " +
"and rf.SRDID = srd.SRDID and IsLead = 1 " +
$"and IsCancelled = 1 and srd.CancelledSupplierPaymentId is null and SupplierID = {SupplierID} and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
}
if (mode == (int)PayToEnum.Agent)
{
leftsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, srd.SRDID,srq.SRID, srd.Cost,srd.AgentPaymentId, " +
"srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo,Coalesce(IsCancelled,0) as IsCancelled from SRdetails srd, " +
"ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and IsLead = 1 " +
$"and IsCancelled is null and AgentId = '{AgentId}' and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
rightsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, " +
"srd.SRDID,srq.SRID, srd.Cost, srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo,srd.AgentPaymentId,Coalesce(IsCancelled,0) as IsCancelled,srd.CancelledAgentPaymentId,rf.ProdCanxCost,rf.SRDID, rf.SBCanxCost, rf.RefundId " +
"from SRdetails srd, ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc, Refunds rf where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and rf.SRDID = srd.SRDID and IsLead = 1 " +
$"and IsCancelled = 1 and srd.CancelledAgentPaymentId is null and AgentId = '{AgentId}' and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
}
if (mode == (int)PayToEnum.Walkin)
{
leftsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, srd.SRDID,srq.SRID, srd.Cost,srd.AgentPaymentId, " +
"srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo,Coalesce(IsCancelled,0) as IsCancelled from SRdetails srd, " +
"ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and IsLead = 1 " +
$"and IsCancelled is null and c.CustomerID = {CustomerID} and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
rightsq.Append("Select CONCAT(c.fName, ' ',c.sName) as PaxName, " +
"srd.SRDID,srq.SRID, srd.Cost, srq.BookingNo, srd.Fdate, SellPrice, srd.PayTo,Coalesce(IsCancelled,0) as IsCancelled,srd.AgentPaymentId,srd.CancelledAgentPaymentId,rf.ProdCanxCost,rf.SRDID, rf.SBCanxCost, rf.RefundId " +
"from SRdetails srd, ServiceRequest srq, SR_Cust src, Customer c, SRD_Cust sc, Refunds rf where sc.SRDID = srd.SRDID " +
"and c.CustomerID = sc.CustomerID and srq.SRID = srd.SRID and src.ServiceRequestID = srq.SRID and rf.SRDID = srd.SRDID and IsLead = 1 " +
$"and IsCancelled = 1 and c.CustomerID = {CustomerID} and srd.Fdate between '{String.Format("{0:yyyy-MM-dd}", FromDate)}' and '{String.Format("{0:yyyy-MM-dd}", ToDate)}'");
}
if (Paid == true && mode == (int)PayToEnum.Driver)
{
leftsq.Append($" and srd.AgentPaymentId is not null");
rightsq.Append($" and srd.SupplierPaymentId is not null");
}
if (Pending == true && mode == (int)PayToEnum.Driver)
{
leftsq.Append($" and srd.AgentPaymentId is null");
rightsq.Append($" and srd.SupplierPaymentId is null");
}
if (Paid == true && mode == (int)PayToEnum.Supplier)
{
leftsq.Append($" and srd.SupplierPaymentId is not null");
rightsq.Append($" and srd.CancelledSupplierPaymentId is not null");
}
if (Pending == true && mode == (int)PayToEnum.Supplier)
{
leftsq.Append($" and srd.SupplierPaymentId is null");
rightsq.Append($" and srd.CancelledSupplierPaymentId is null");
}
if (Paid == true && mode == (int)PayToEnum.Agent)
{
leftsq.Append($" and srd.AgentPaymentId is not null");
rightsq.Append($" and srd.CancelledAgentPaymentId is not null");
}
if (Pending == true && mode == (int)PayToEnum.Agent)
{
leftsq.Append($" and srd.AgentPaymentId is null and srd.PayTo <> 'Pay to driver'");
rightsq.Append($" and srd.CancelledAgentPaymentId is null and srd.PayTo <> 'Pay to driver'");
}
if (Paid == true && mode == (int)PayToEnum.Walkin)
{
leftsq.Append($" and srd.AgentPaymentId is not null");
rightsq.Append($" and srd.CancelledAgentPaymentId is not null");
}
if (Pending == true && mode == (int)PayToEnum.Walkin)
{
leftsq.Append($" and srd.AgentPaymentId is null");
rightsq.Append($" and srd.CancelledAgentPaymentId is null");
}
paymentdetailvws.LeftBkPayVm = db.Query <BKPayVM>(leftsq).ToList();
paymentdetailvws.RightBkPayVm = db.Query <BKPayVM>(rightsq).ToList();
ViewBag.DriverID = DriverID;
ViewBag.SupplierID = SupplierID;
ViewBag.AgentId = AgentId;
ViewBag.CustomerId = CustomerID;
ViewBag.Mode = mode;
return(PartialView("_FinanceInfo", paymentdetailvws));
}
public override Sql SelectTop(Sql sql, int top)
{
return(new Sql(string.Concat(sql.SQL, " LIMIT ", top), sql.Arguments));
}
/// <summary>
/// Returns the SQL with the name and the CTE (Common Table Expression)
/// </summary>
public void GetExecSQLName(ref string CTE, ref string name)
{
CTE = "";
string sql = "";
if (Sql != null && Sql.Length > 5 && Sql.ToLower().Trim().StartsWith("with"))
{
var startIndex = Sql.IndexOf("(");
var depth = 1;
if (startIndex > 0)
{
bool inComment = false, inQuote = false;
for (int i = startIndex + 1; i < Sql.Length - 5; i++)
{
switch (Sql[i])
{
case ')':
if (!inComment && !inQuote)
{
depth--;
if (depth == 0)
{
CTE = Sql.Substring(0, i + 1).Trim() + "\r\n";
sql = Sql.Substring(i + 1).Trim();
if (sql.ToLower().StartsWith("select"))
{
//end of CTE
i = Sql.Length;
}
}
}
break;
case '(':
if (!inComment && !inQuote)
{
depth++;
}
break;
case '\'':
inQuote = !inQuote;
break;
case '/':
if (Sql[i + 1] == '*')
{
inComment = true;
}
break;
case '*':
if (inComment && Sql[i + 1] == '/')
{
inComment = false;
}
break;
case '-':
if (!inComment && Sql[i + 1] == '-')
{
while (i < Sql.Length - 5 && Sql[i] != '\r' && Sql[i] != '\n')
{
i++;
}
}
break;
}
}
}
}
if (string.IsNullOrEmpty(CTE) || string.IsNullOrEmpty(sql))
{
name = FullSQLName;
}
else
{
name = string.Format("(\r\n{0}\r\n) {1}", sql, AliasName);;
}
}
public Rule GetSingleBy(Expression <Func <Rule, bool> > predicate)
{
var query = new Sql().Select("*").From(TableConstants.Rules.TableName).Where(predicate, this.SqlSyntax);
return(this.Database.SingleOrDefault <Rule>(query));
}
public static IEnumerable <T> Query(Sql sql)
{
return(repo.Query <T>(sql));
}
public static List <T> Fetch(long page, long itemsPerPage, Sql sql)
{
return(repo.Fetch <T>(page, itemsPerPage, sql));
}