private static void BasicQuery(Database db)
{
// Peruskysely
var customers = db.Query<Customer>("SELECT TOP 10 * FROM Customer WITH(NOLOCK)");
foreach (var a in customers)
{
Console.WriteLine("{0} - {1}", a.CustId, a.CustName);
}
Console.WriteLine();
}
public static BoolMessage PpRetrieveList <T>(string tableName, string condition = null)
{
_checkVal.Data = null;
try
{
if (string.IsNullOrEmpty(_cstring))
{
throw new Exception("No connection string provided");
}
List <T> rtnList = null;
if (string.IsNullOrEmpty(condition))
{
rtnList = _pp.Query <T>(string.Format("SELECT * FROM [{0}]", tableName)).ToList();
}
else
{
rtnList = _pp.Query <T>(string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, condition)).ToList();
}
_checkVal.Success = true;
_checkVal.Message = string.Format("Successfully returned list from table {0}", tableName);
_checkVal.Data = rtnList;
}
catch (Exception ex)
{
_checkVal.Data = null;
_checkVal.Success = false;
_checkVal.Message = ex.Message;
}
return(_checkVal);
}
public static IEnumerable <Campaign> GetAll()
{
using (IDatabase db = new PetaPoco.Database(Settings.ConnectionString, providerName: "SqlServer"))
{
return(db.Query <Campaign>(Settings.CampaignsQuery));
}
}
public List <Orders> GetOrders()
{
using (var db = new PetaPoco.Database("AdventureWorks2014"))
{
return(db.Query <Orders>(@"SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO
INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID").ToList());
}
}
static void Main(string[] args)
{
var db = new PetaPoco.Database(connectionString: "server=.;database=orderdb;uid=sa;pwd=1;", providerName: "sqlservers");
foreach (var order in db.Query <Orders>("select * from orders"))
{
Console.WriteLine(order);
}
}
protected void Button4_Click(object sender, EventArgs e)
{
var db = new PetaPoco.Database("myConnectionString");
string queryBooks = "SELECT * FROM Responses order by date_created desc";
var result = db.Query <pixResponses>(queryBooks);
// GridView1.DataSource = result;
// GridView1.DataBind();
}
/// <summary>
/// Gets a user by the query
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
private RegisteredUser GetUserByQuery(Sql query)
{
var results = _database.Query <RegisteredUser, TwitterAuthentication, RegisteredUser>
(
(u, auth) =>
{
u.AuthDetails = auth;
return(u);
}, query
);
RegisteredUser user = results.FirstOrDefault();
if (user == null)
{
return(null);
}
return(user);
}
public List <User> GetUsers()
{
// TODO: Need to make it generic
var dbConnectionString = appSettings.Value.DefaultConnection;
NpgsqlConnection conn = new NpgsqlConnection(dbConnectionString.ToString());
conn.Open();
PetaPoco.Database db = new PetaPoco.Database(conn);
List <User> userslst = db.Query <User>(strGetUsersQuery).ToList();
conn.Close();
return(userslst);
}
static void Main(string[] args)
{
var db = new PetaPoco.Database("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True", "System.Data.SqlClient");
var records = db.Query<Person>("select FirstName,MiddleName,LastName,Demographics,Suffix from Person.Person");
var record = records.First();
Console.WriteLine("Suffix: " + record.Suffix);
Console.WriteLine("FName: " + record.FirstName);
Console.WriteLine("Middle: " + record.MiddleName);
Console.WriteLine("LName: " + record.LastName);
Console.WriteLine("Demographics: " + record.Demographics);
Console.ReadLine();
}
static void Main(string[] args)
{
var db = new PetaPoco.Database("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True", "System.Data.SqlClient");
var records = db.Query <Person>("select FirstName,MiddleName,LastName,Demographics,Suffix from Person.Person");
var record = records.First();
Console.WriteLine("Suffix: " + record.Suffix);
Console.WriteLine("FName: " + record.FirstName);
Console.WriteLine("Middle: " + record.MiddleName);
Console.WriteLine("LName: " + record.LastName);
Console.WriteLine("Demographics: " + record.Demographics);
Console.ReadLine();
}
static void Main(string[] args)
{
var db = new Database("Postgres");
// Show all articles
foreach (var member in db.Query<Members>("SELECT * FROM cd.members"))
{
Console.WriteLine("{0} - {1}, {2}",
member.MemberId,
member.Surname,
member.FirstName);
}
db.Dispose();
Console.WriteLine("Done.");
Console.ReadLine();
}
public static List <System.Web.Mvc.SelectListItem> GetInstructorDropDown()
{
List <System.Web.Mvc.SelectListItem> ls = new List <System.Web.Mvc.SelectListItem>();
BSAMemberController oController = new BSAMemberController();
var db = new PetaPoco.Database(sConnectString);
var lstMember = db.Query <BSAMember>("SELECT * FROM BSAMembers where BSAMemberType = @0 AND DeleteRecordFlag = @1", "Instructor", false);
foreach (var temp in lstMember)
{
ls.Add(new System.Web.Mvc.SelectListItem()
{
Text = temp.FirstName + " " + temp.LastName, Value = temp.BSAMemberId.ToString()
});
}
return(ls);
}
public IEnumerable <BSAMemberViewModel> GetAllBSAMembers()
{
//Connect to the Umbraco DB
var db = new PetaPoco.Database(sConnectString);
List <BSAMemberViewModel> members = new List <BSAMemberViewModel>();
//Get an IENumberable of MeritBadges objects to iterate over
var lstMember = db.Query <BSAMember>("SELECT * FROM BSAMembers where DeleteRecordFlag = @0", false);
foreach (BSAMember item in lstMember)
{
BSAMemberViewModel oModel = FillViewModelBSAMember(item, false);
members.Add(oModel);
}
//Return the view with our model and comments
return(members);
}
public IHttpActionResult GetWinner(string id)
{
var db = new PetaPoco.Database("AGSoftware");
System.Collections.Generic.List <Entities.StorytimePost> storytimewinnerlist = new List <StorytimePost>();
foreach (var a in db.Query <Entities.StorytimePost>("Select * From StoryTimePost Where StorytimeId = @0 Group By UserId, StorytimeId, SeriesId, StorytimePostId, PostText, ImagePath, Votes, DateCreated Order By Votes Desc", id))
{
storytimewinnerlist.Add(a);
}
if (storytimewinnerlist.Count > 0)
{
return(Ok(storytimewinnerlist));
}
else
{
return(NotFound());
}
}
public List<Table> GetAllTables()
{
var db = new Database("document");
var tables =
db.Query<Table>(
"select [table].name as Name, [table].[extract_batch_size] as ExtractBatchSize, [table].[lookup_descriptor] as LookupDescriptor, [table].[parent_table] as ParentTableName, [table].[key_columns] as KeyColumns, [table].connection_string as ExtractConnectionString, [table].environment_name as Environment, [table].extract_version as MaxExtractedVersion, [table].prefetch as Prefetch, [table].custom_sql as CustomSql from [table]")
.ToList();
foreach (var table in tables)
{
if (table.ParentTableName != null)
{
table.Parent =
tables.FirstOrDefault(t => t.Name == table.ParentTableName && t.Environment == table.Environment);
}
}
return tables;
}
/// <summary>
///
/// </summary>
protected static void InitDbAllTablesCache()
{
var defaultDbConnection = ConfigurationManager.AppSettings["defaultDbConnection"].ToString();
//* define a holder to store all db connection exists
List <string> _listOfDbConnNamesToLoad = new List <string>();
//* add the default one to load
_listOfDbConnNamesToLoad.Add(defaultDbConnection);
//* check for additional names to load
if (DbSchemaConfiguration.Configs.Count() > 0)
{
var items = DbSchemaConfiguration.Configs.Select(kvp => kvp.Value).ToList();
foreach (var item in items)
{
if (item.DatabaseConnectionName != defaultDbConnection)
{
_listOfDbConnNamesToLoad.Add(item.DatabaseConnectionName);
}
}
}
//* Finally, query against the database to load
string _sql = "SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()";
foreach (var dbConn in _listOfDbConnNamesToLoad)
{
var petaDb = new PetaPoco.Database(connectionStringName: dbConn);
var tableSchemas = petaDb.Query <InformationSchema>(_sql);
foreach (var item in tableSchemas)
{
var specialKey = item.TABLE_SCHEMA + "_" + item.TABLE_NAME;
if (!_dbAllTablesCache.ContainsKey(specialKey))
{
_dbAllTablesCache.Add(specialKey, item.TABLE_NAME);
}
}
}
}
public IEnumerable<Route> RoutesForUser(int userId, long ts)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
return db.Query<Route>(@"select v.* from MR.vRoute v inner join MR.tRoute r on r.RouteId = v.RouteId where v.CreatorUserId = @0 and r.timestamp > @1", userId, ts);
}
private static void DynamicSql(Database db, int herd, DateTime? date)
{
// Kysely dynaamisella sql:llä
var sql = PetaPoco.Sql.Builder
//.Append("SELECT * FROM idkarbovine with(nolock) sadfdsf") // Eri näppärä virhekäsittely, breakpoint petapoco.cs:n OnException-metodiin.
.Append("SELECT * FROM idkarbovine with(nolock)")
.Append("WHERE idkar=@0", herd);
if (date.HasValue)
sql.Append("AND ValidFromDate<=@0", date.Value);
if (date.HasValue)
sql.Append("AND ValidDueDate>=@0", date.Value);
sql.Append("ORDER BY EarNr");
var results = db.Query<Bovine>(sql);
foreach (var a in results)
{
Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
}
Console.WriteLine();
}
public static IEnumerable <TRet> Query <T1, T2, T3, T4, T5, TRet>(this Database db, Func <T1, T2, T3, T4, T5, TRet> cb, string sql, params object[] args)
{
return(db.Query <TRet>(new Type[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4), typeof(T5) }, cb, sql, args));
}
private static IEnumerable<dynamic> GetItems(string targetTableName, Database database)
{
return database.Query<dynamic>(string.Format("Select * from {0} order by 1", targetTableName));
}
private static IEnumerable<string> GetTableNames(Database database)
{
return
database.Query<string>(
string.Format("SELECT name FROM sqlite_master " + "WHERE type = 'table'" + "ORDER BY 1"));
}
public static void loadDBTabData(string Table, string Fields, string OrderBy, string Where, GridView grd, ListBox lst)
{
// Create a PetaPoco database object
var db = new PetaPoco.Database("Shell_AutoArchDBConnectionString");
//------------------------------------------------------------
string v_fields = "*";
string v_orderby = "";
//
if (Fields != "")
{
v_fields = Fields;
}
if (OrderBy != "")
{
v_orderby = OrderBy;
}
if (Where != "")
{
g_WHERE = new StringBuilder(Where);
}
else
{
if (null == g_WHERE)
{
g_WHERE = new StringBuilder("WHERE 1 = 1");
}
}
//
string v_tabname = "SELECT " + v_fields + " FROM " + Table;
string v_order = "";
if (v_orderby != "")
{
v_order = "ORDER BY " + v_orderby;
}
//
//------------------------------------------------------------
// Use here g_WHERE because generic functions...
//------------------------------------------------------------
var sql = PetaPoco.Sql.Builder
.Append(v_tabname + "\n" + g_WHERE + "\n" + v_order);
//
try
{
//Search the right table and execute Query...
if (Table == "shell_Planning")
{
var q = db.Query <planning>(sql);
if (null != grd)
{
grd.DataSource = q;
grd.DataBind();
}
else
{
//Create List and Show Only uniqueid Field... Obs. this field must be into param...
List <string> list = new List <string>();
foreach (var item in q)
{
list.Add(item.UniqueId.Trim());
}
//
lst.DataSource = list;
lst.DataBind();
}
}
else if (Table == "shell_LAPIconf")
{
var q = db.Query <lapicfg>(sql);
if (null != grd)
{
grd.DataSource = q;
grd.DataBind();
}
else
{
//Create List and Show Only uniqueid Field... Obs. this field must be into param...
List <string> list = new List <string>();
foreach (var item in q)
{
list.Add(item.id_instance.Trim());
}
//
lst.DataSource = list;
lst.DataBind();
}
}
else
{
var q = db.Query <sysid_istid>(sql);
if (null != grd)
{
grd.DataSource = q;
grd.DataBind();
}
else
{
//Create List and Show Only uniqueid Field... Obs. this field must be into param...
List <string> list = new List <string>();
foreach (var item in q)
{
list.Add(item.id_instance.Trim());
}
//
lst.DataSource = list;
lst.DataBind();
}
}
//
}
catch (Exception eg)
{
string exmsg = eg.Message;
}
}
private static void TestMapping(Database db)
{
Console.WriteLine("mapping");
Console.WriteLine();
var herdId = 1505147;
var sql =
Sql.Builder.Append("select * from bovine b with(nolock)")
.Append("join idkarbovine ib with(nolock) on b.BovineId = ib.BovineId")
.Append("where ib.Idkar=@0", herdId);
var res = db.Query<Bovine, IdkarBovine, Bovine>(
(p, a) =>
{
p.IdkarBovine = a;
return p;
}, sql);
}
private static void TestMARS(Database db)
{
Console.WriteLine("mapping");
Console.WriteLine();
var herdId = 1505147;
var sql =
Sql.Builder.Append("execute hepsis");
var res = db.Query<IdkarBovine>(sql);
}
protected override void Process()
{
using (var unit = GetUnitOfWork())
{
try
{
var _assortmentRepo = unit.Scope.Repository <VendorAssortment>();
var connectors = (from c in this.Connectors
where
(((ConnectorType)c.ConnectorType).Has(ConnectorType.WebAssortment) ||
((ConnectorType)c.ConnectorType).Has(ConnectorType.ShopAssortment))
#if !DEBUG
&& c.IsActive
#endif
select c).ToList();
foreach (Connector conn in connectors)
{
bool excludeProducts = conn.ConnectorSettings.GetValueByKey <bool>("ExcludeProducts", false);
using (var pDb = new PetaPoco.Database(Connection, "System.Data.SqlClient"))
{
pDb.CommandTimeout = 15 * 60;
var productMatches = pDb.Query <ProductMatch>("SELECT * FROM ProductMatch WHERE isMatched = 1").ToList();
List <string> vendorItemNumbersToExclude = new List <string>();
Dictionary <int, string> vendorItemNumbers = new Dictionary <int, string>();
Dictionary <int, Content> contentList = new Dictionary <int, Content>();
log.DebugFormat("Start Generating Assortment for {0}", conn.Name);
log.DebugFormat("Connector {0}({1}) has {2} rules for assortment generation", conn.Name, conn.ConnectorID, conn.ContentProducts.Count);
bool assortmentLoaded = false;
var publicationRules = pDb.Fetch <ConnectorPublication>("SELECT * FROM ConnectorPublication WHERE ConnectorID = @0", conn.ConnectorID);
if (excludeProducts)
{
vendorItemNumbersToExclude = pDb.Fetch <string>("Select value from ExcludeProduct where ConnectorID = @0", conn.ConnectorID).ToList();
vendorItemNumbers = pDb.Fetch <Product>("select productID, VendorItemNumber from product").ToDictionary(x => x.ProductID, y => y.VendorItemNumber);
}
foreach (ContentProduct rule in conn.ContentProducts.OrderBy(x => x.ProductContentIndex))
{
var connectorPublicationRules = publicationRules.Where(x => x.VendorID == rule.VendorID && (!x.FromDate.HasValue || (x.FromDate.HasValue && x.FromDate <= DateTime.Now)) && (!x.ToDate.HasValue || (x.ToDate.HasValue && x.ToDate >= DateTime.Now))).ToList();
if (!assortmentLoaded)
{
assortmentLoaded = rule.IsAssortment;
}
#region content logic
log.DebugFormat("Processing Rule {0} for connectorid {1}", rule.ProductContentID, conn.ConnectorID);
if (rule.ProductID.HasValue)
{
#region Single Product
if (assortmentLoaded && !rule.IsAssortment)
{
// exclude product
if (rule.ProductID.HasValue)
{
contentList.Remove(rule.ProductID.Value);
}
}
else
{
var vass = _assortmentRepo.GetSingle(a => a.VendorID == rule.VendorID &&
a.ProductID == rule.ProductID.Value &&
a.IsActive);
if (vass != null)
{
var groups = (from va in _assortmentRepo.GetAllAsQueryable()
from pa in va.ProductGroupVendors
select new
{
va.VendorAssortmentID,
pa.ProductGroupVendorID
}).ToList();
var vendoradd = (from va in _assortmentRepo.GetAllAsQueryable()
where va.VendorAssortmentID == vass.VendorAssortmentID
select new VendorAdds
{
VendorAssortmentID = va.VendorAssortmentID,
ConcentratorStatusID = va.VendorPrices.FirstOrDefault().ConcentratorStatusID,
QuantityOnHand = unit.Scope.Repository <VendorStock>().GetAllAsQueryable(c => c.ProductID == va.ProductID && c.VendorID == va.VendorID).Sum(c => c.QuantityOnHand),
ProductID = va.ProductID,
BrandID = va.Product.BrandID
}).ToList();
Content content = null;
if (!contentList.ContainsKey(vass.ProductID))
{
if (vass.ProductGroupVendors == null)
{
vass.ProductGroupVendors = new List <ProductGroupVendor>();
}
content = new Content
{
ConnectorID = conn.ConnectorID,
ProductID = rule.ProductID.Value,
ShortDescription = vass.ShortDescription,
LongDescription = vass.LongDescription,
ProductContentID = rule.ProductContentID,
ProductGroupVendors = vass.ProductGroupVendors.Select(c => c.ProductGroupID).ToList(),
Product = unit.Scope.Repository <Product>().GetSingle(c => c.ProductID == rule.ProductID.Value)
};
contentList.Add(rule.ProductID.Value, content);
CheckConnectorPublication(content, connectorPublicationRules, vendoradd.FirstOrDefault(x => x.VendorAssortmentID == vass.VendorAssortmentID), unit, contentList);
}
}
else
{
log.DebugFormat(
"Could not add product {0} to content, does not exists in vender assortment (VendorID {1})",
rule.ProductID.Value, rule.VendorID);
}
}
#endregion
}
else
{
#region Non Product
//include scenario
string additionalFilters = String.Empty;
if (rule.BrandID.HasValue)
{
additionalFilters += String.Format(" AND P.BrandID = {0}", rule.BrandID);
}
//if (rule.ProductGroupID.HasValue)
//{
// vendorAssortment = vendorAssortment.Where(c => c.ProductGroupVendors.Any(l => l.ProductGroupID == rule.ProductGroupID.Value && l.VendorID == rule.VendorID));
//}
string query = String.Format(@"SELECT VA.VendorAssortmentID, VP.ConcentratorStatusID, P.BrandID, VA.ProductID, ISNULL(SUM(VS.QuantityOnHand),0) AS QuantityOnHand
FROM VendorAssortment VA
INNER JOIN Product P ON (VA.ProductID = P.ProductID)
INNER JOIN VendorStock VS ON (VA.VendorID = VS.VendorID AND VA.ProductID = VS.ProductID)
INNER JOIN VendorPrice VP ON (VA.VendorAssortmentID = VP.VendorAssortmentID)
WHERE VA.VendorID = {0} {1}
GROUP BY VA.VendorAssortmentID, VP.ConcentratorStatusID, P.BrandID, VA.ProductID
", rule.VendorID, additionalFilters);
var vendoradd = pDb.Query <VendorAdds>(query).ToDictionary(x => x.VendorAssortmentID, y => y);
// base collection
//var vendorAssortment = pDb.Fetch<VendorAssortment>("SELECT * FROM VendorAssortment WHERE VendorID = @0 AND IsActive = 1", rule.VendorID);
var vendorAssortmentQuery = string.Format("SELECT va.*, pavSentToWehkamp.Value as 'SentToWehkamp', pavSentAsDummy.Value as 'SentToWehkampAsDummy' FROM VendorAssortment va LEFT OUTER JOIN ProductAttributeValue pavSentToWehkamp ON va.ProductID = pavSentToWehkamp.ProductID AND pavSentToWehkamp.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkamp') LEFT OUTER JOIN ProductAttributeValue pavSentAsDummy ON va.ProductID = pavSentAsDummy.ProductID AND pavSentAsDummy.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkampAsDummy') WHERE va.VendorID = {0} AND va.IsActive = 1", rule.VendorID);
var vendorAssortment = pDb.Fetch <VendorAssortmentWehkampSent>(vendorAssortmentQuery);
var productGroupVendors = pDb.Fetch <ProductGroupVendor>("SELECT * FROM ProductGroupVendor WHERE VendorID = @0 OR VendorID = @1", rule.VendorID, rule.Vendor.ParentVendorID ?? rule.VendorID);
string groupsQuery = string.Format(@"SELECT DISTINCT VA.VendorAssortmentID, PGV.ProductGroupID
FROM VendorAssortment VA
INNER JOIN VendorProductGroupAssortment VPGA ON (VPGA.VendorAssortmentID = VA.VendorAssortmentID)
INNER JOIN ProductGroupVendor PGV ON (PGV.ProductGroupVendorID = VPGA.ProductGroupVendorID)
WHERE VA.VendorID = {0} AND VA.IsActive=1", rule.VendorID);
var groupSource = pDb.Fetch <VaGroupInfo>(groupsQuery);
var groups = (from g in groupSource
group g by g.VendorAssortmentID into grouped
select grouped).ToDictionary(x => x.Key, y => (from pg in y select pg.ProductGroupID).ToList());
groupSource = null;
var productRepo = pDb.Fetch <Product>("SELECT * FROM Product").ToDictionary(x => x.ProductID, y => y);
List <ProductAttributeValue> values = pDb.Query <ProductAttributeValue>(string.Format(@"select pav.* from productattributevalue pav
inner join connectorpublication cp on pav.attributeid = cp.attributeid
where cp.connectorid = {0}", rule.ConnectorID)).ToList();
foreach (var va in vendorAssortment)
{
if (!groups.ContainsKey(va.VendorAssortmentID))
{
continue;
}
if (assortmentLoaded && !rule.IsAssortment)
{
contentList.Remove(va.ProductID);
}
else
{
if (excludeProducts &&
(string.IsNullOrEmpty(va.SentToWehkamp) || va.SentToWehkamp.ToLowerInvariant() == "false") &&
(string.IsNullOrEmpty(va.SentToWehkampAsDummy) || va.SentToWehkampAsDummy.ToLowerInvariant() == "false"))
{
string vendorItemNumber = vendorItemNumbers[va.ProductID];
if (ExcludeProduct(vendorItemNumber, vendorItemNumbersToExclude))
{
continue;
}
}
Content content = null;
if (!contentList.ContainsKey(va.ProductID))
{
content = new Content
{
ConnectorID = conn.ConnectorID,
ProductID = va.ProductID,
ShortDescription = va.ShortDescription,
LongDescription = va.LongDescription,
LineType = va.LineType,
ProductContentID = rule.ProductContentID,
ProductGroupVendors = groups[va.VendorAssortmentID],
Product = productRepo[va.ProductID]
};
//content.Product.RelatedProductsSource = relatedProducts.Where(c => c.ProductID == content.ProductID).ToList();
content.Product.ProductAttributeValues = values.Where(c => c.ProductID == content.ProductID).ToList();
contentList.Add(va.ProductID, content);
}
VendorAdds vad = null;
vendoradd.TryGetValue(va.VendorAssortmentID, out vad);
CheckConnectorPublication(content, connectorPublicationRules, vad, unit, contentList);
}
}
#endregion
}
log.DebugFormat("Finished Processing Rule {0} for Connector {1}", rule.ProductContentID, conn.Name);
#endregion
}
///put logic here
try
{
var vendorsettings = pDb.Query <ContentProduct>("SELECT * FROM ContentProduct WHERE ConnectorID = @0", conn.ConnectorID)
.Select(v => new
{
v.ProductContentIndex,
v.ProductContentID
}).Distinct().ToDictionary(x => x.ProductContentID, x => x.ProductContentIndex);
var copyContentList = (from c in contentList.Values
join pm in productMatches on c.ProductID equals pm.ProductID
select c).ToList();
foreach (var c in copyContentList)
{
var match = productMatches.Where(x => x.ProductID == c.ProductID).FirstOrDefault();
if (match != null)
{
var matches = productMatches.Where(x => x.ProductMatchID == match.ProductMatchID && x.ProductID != c.ProductID).ToList();
if (matches.Count > 0)
{
foreach (var product in matches)
{
if (!c.ProductContentID.HasValue)
{
continue;
}
int contentVendorIndex = vendorsettings[c.ProductContentID.Value];
Content matchContent = null;
if (contentList.TryGetValue(product.ProductID, out matchContent))
{
if (!matchContent.ProductContentID.HasValue)
{
continue;
}
int matchContentIndex = vendorsettings[matchContent.ProductContentID.Value];
if (contentVendorIndex > matchContentIndex)
{
contentList.Remove(c.ProductID);
}
else
{
contentList.Remove(matchContent.ProductID);
}
}
}
}
}
}
}
catch (Exception ex)
{
log.AuditError("Cleaning Matches failed for {0}", conn.Name);
}
var curContent = pDb.Fetch <Content>("SELECT * FROM Content WHERE ConnectorID = @0", conn.ConnectorID).ToDictionary(x => x.ProductID, y => y);
try
{
log.DebugFormat("Cleaning up assortment for {0}", conn.Name);
var delcontent = (from c in curContent
where !contentList.ContainsKey(c.Key)
select c.Value).ToList();
foreach (var rec in delcontent)
{
pDb.Delete("Content", "ProductID, ConnectorID", rec);
}
log.DebugFormat("Finished cleaning up for {0}", conn.Name);
}
catch (Exception ex)
{
log.FatalFormat("Assorment cleanup failed for {0} error {1}", conn.ConnectorID, ex.StackTrace);
}
#region Existing Content
//OBSOLETE:
//log.Debug("Processing Existing Content");
//foreach (var curCon in contentList.Values)
//{
// Content exCon = null;
// curContent.TryGetValue(curCon.ProductID, out exCon);
// if (exCon != null)
// {
// exCon.ExtendedCatalog = curCon.ExtendedCatalog;
// exCon.LedgerClass = curCon.LedgerClass;
// exCon.LineType = curCon.LineType;
// exCon.LongDescription = curCon.LongDescription;
// exCon.ProductDesk = curCon.ProductDesk;
// exCon.ShortDescription = curCon.ShortDescription;
// exCon.ProductContentID = curCon.ProductContentID;
// pDb.Update("Content", "ProductID, ConnectorID", exCon, new List<String>() { "ShortDescription", "LongDescription", "LineType", "LedgerClass", "ProductDesk", "ExtendedCatalog", "ProductContentID" });
// }
//}
////unit.Save();
//log.Debug("Finished processing existing content");
#endregion
var newContent = (from c in contentList
where !curContent.ContainsKey(c.Key)
select c.Value).ToList();
log.DebugFormat("Inserting {0} rows for connector {1}", newContent.Count(), conn.ConnectorID);
foreach (var c in newContent)
{
pDb.Insert("Content", "ProductID, ConnectorID", false, new { ProductID = c.ProductID, ConnectorID = c.ConnectorID, c.ProductContentID, c.ShortDescription, c.LongDescription, c.ExtendedCatalog, c.LedgerClass, c.LineType, c.ProductDesk, CreatedBy = c.CreatedBy });
}
#region contentproductgroups
try
{
var contentProductGroups = pDb.Fetch <ContentProductGroup>("SELECT * FROM ContentProductGroup WHERE IsCustom = 0 AND ConnectorID = @0", conn.ConnectorID);
//List<ProductGroupMapping> productGroupMappings = pDb.Fetch<ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE Depth = 0 AND (ConnectorID = @0 OR ConnectorID = @1)",
// conn.ConnectorID, conn.ParentConnectorID ?? conn.ConnectorID);
List <ProductGroupMapping> productGroupMappings = pDb.Fetch <ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE (ConnectorID = @0 OR ConnectorID = @1)",
conn.ConnectorID, conn.ParentConnectorID ?? conn.ConnectorID);
Dictionary <int, List <int> > MappingProductGroup = (
from c in productGroupMappings
group c by c.ProductGroupID into gr
select new { gr.Key, Value = gr.Select(c => c.ProductGroupMappingID) })
.ToDictionary(c => c.Key, c => c.Value.ToList());
//Start assortment tree processes
var tree = BuildTree(productGroupMappings);
var products = GetContentsPerMapping(contentList.Values.ToList(), productGroupMappings, MappingProductGroup);
//add products to tree
AddProductsToTree(tree, products, contentList.Values.ToList(), MappingProductGroup);
//end assortment tree processes
var toSync = tree.FlattenTreeToList(conn.ConnectorID, 1);
SyncNewContentProductGroups(toSync, pDb, conn.ConnectorID);
log.DebugFormat("Finish Remove unused contentproductgroups");
}
catch (Exception ex)
{
log.Fatal("Error processing contentproductgroups for connectorID" + conn.ConnectorID.ToString(), ex);
}
#endregion
log.DebugFormat("Finish insert assortment for connector {0}: {1}", conn.ConnectorID, conn.Name);
if (conn.ConnectorSettings.GetValueByKey <bool>("SyncContentProductGroupsWithParentConnector", false))
{
SyncCustomContentProducts(conn, pDb);
}
}
}
}
catch (Exception ex)
{
log.Fatal("Processs assortment error", ex);
}
}
}
private void loadComments()
{
if (_comments == null)
{
_comments = new List<Comment>();
}
var db = new Database("umbracoDbDSN");
foreach (var comment in db.Query<Comment>("Select * from forumComments where topicId = @0", Id))
{
_comments.Add(comment);
}
}
private string WinFlagColumn = "sWin"; // 中獎識別欄位
#endregion Fields
#region Methods
/// <summary>
/// 抽獎方法
/// </summary>
/// <param name="n">欲抽出數量</param>
/// <param name="PKColumn">主索引鍵</param>
/// <param name="MaxDrawQuota">中獎名額</param>
/// <param name="WinFlagColumn">中獎識別欄位</param>
/// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
/// <param name="TableName">資料表</param>
/// <param name="BasicCondition">基本 SQL 條件</param>
/// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
/// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
{
PetaPoco.Database db = new PetaPoco.Database("conn");
int counter = 0;
DrawResult result = new DrawResult();
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
sql.Append(BasicCondition);
if (IsGroup)
{
sql.Append("GROUP BY [" + DistinctColumn + "]");
}
var data = db.Query<DataModel_a12SupauCheckin>(sql);
counter = data.Count();
if (counter < n)
{
result.Result = false;
result.Msg = "名單不足以抽出這樣的數量喔！";
return result;
}
if (n < 1)
{
result.Result = false;
result.Msg = "數量請至少為 1。";
return result;
}
if (n > MaxDrawQuota)
{
result.Result = false;
result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";
return result;
}
#region 檢查剩餘名額
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
sql.Append(BasicCondition);
var r = db.Query<DataModel_a12SupauCheckin>(sql);
// 若目前中獎人數大於等於中獎名額
if (r.Count() >= MaxDrawQuota)
{
result.Result = false;
result.Msg = "名額已滿";
return result;
}
#endregion
if (!IsGroup)
{
if (n == 1)
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = "'" + a.sFBUID.ToString() + "'";
return result;
}
else
{
string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'";
return result;
}
}
else
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn));
var a = db.Query<DataModel_a12SupauCheckin>(sql);
string return_data = "";
foreach (var item in a)
{
return_data += ",'" + item.sFBUID + "'";
}
return_data = return_data.Substring(1, return_data.Length - 1);
result.Result = true;
result.Msg = return_data;
return result;
}
}
public bool Archive()
{
try
{
Database db = new Database("Database");
foreach(var archiveMultimedia in db.Query<Multimedia>("SELECT ID, ProductID, MultimediaTypeID, Name, FileName, FilePath, Version FROM Multimedia WHERE Name = @0", this.Name))
{
archiveMultimedia.IsActive = false;
new Database("Database").Update(archiveMultimedia);
}
return true;
}
catch (Exception ex)
{
Logger.WriteLine("Failed to archive file {0}: {1}", this.Name, ex);
return false;
}
}
/// <summary>
/// 隐式转换
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="connName"></param>
/// <returns></returns>
public static PetaPoco.Page<dynamic> ImplicitConvert(int pageSize, int pageIndex, string connName)
{
PetaPoco.Sql sbRs = new PetaPoco.Sql();
StringBuilder sb = new StringBuilder();
sb.Append("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select * from ( SELECT row_number()over(order by id )as RowNum, * from ( select NEWID() as id, stmt.value('(@@StatementText)[1]', 'varchar(max)') as sqltxt, t.value('(ScalarOperator/Identifier/ColumnReference/@@Schema)[1]', 'varchar(128)') as schemaname, t.value('(ScalarOperator/Identifier/ColumnReference/@@Table)[1]', 'varchar(128)') as tablename, t.value('(ScalarOperator/Identifier/ColumnReference/@@Column)[1]', 'varchar(128)') as columnname, ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value('(@@DataType)[1]', 'varchar(128)') AS ConvertTo, t.value('(@@Length)[1]', 'int') AS ConvertToLength FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@@Implicit=\"1\"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@@Column)[1]', 'varchar(128)') ) a) b where rownum between " + ((pageIndex - 1) * pageSize + 1).ToString() + " and " + (pageIndex * pageSize).ToString());
PetaPoco.Page<dynamic> result = new Page<dynamic>();
var db = new PetaPoco.Database(connName);
try
{
result.CurrentPage = pageIndex;
result.ItemsPerPage = pageSize;
sbRs.Append(sb.ToString());
result.Items = db.Query<dynamic>(sbRs).ToList();
PetaPoco.Sql sbCount = new PetaPoco.Sql();
sb.Length = 0;
sb.Append(
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select count(1) FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@@Implicit=\"1\"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@@Column)[1]', 'varchar(128)') ");
sbCount.Append(sb.ToString());
result.TotalItems = db.ExecuteScalar<int>(sbCount);
// result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
public static List <TRet> Fetch <T1, T2, T3, T4, T5, TRet>(this Database db, Func <T1, T2, T3, T4, T5, TRet> cb, string sql, params object[] args)
{
return(db.Query <T1, T2, T3, T4, T5, TRet>(cb, sql, args).ToList());
}
private void roleGridEdit2_EditValueChanged(object sender, EventArgs e)
{
if (roleGridEdit2.EditValue == null) {
gridunalloc.DataSource = gridalloc.DataSource = null;
gridunalloc.RefreshDataSource();
gridalloc.RefreshDataSource();
return;
}
var re = roleGridEdit2.GetSelectedValue();
if (re == null)
return;
using (var db = new Database()) {
var ur = db.Query<t_user>("select * from t_user")
.Join(db.Query<t_roleuser>("select * from t_roleuser where RoleId=@0", re.Id),
o => o.Id, k => k.UserId, (o, k) => new { o, k }
).Select(z => z.o).OrderBy(k => k.UserName).ToList();
this.gridalloc.DataSource = ur;
this.gridunalloc.DataSource = AllUser.Where(k => !ur.Any(j => j.UserName == k.UserName)).Where(k => k.UserName != "admin").OrderBy(k => k.UserName).ToList();
return;
}
}
private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
{
var et = this.gridControlEx1.GetFocusedDataSource<t_role>();
if (null == et) {
this.gridControlEx2.DataSource = null;
return;
}
using (var db = new Database()) {
var users = db.Query<t_user>("Select * from t_user");
var rus = db.Query<t_roleuser>("Select * from t_roleuser");
var op = (from o in users
join k in rus.Where(t => t.RoleId == et.Id)
on o.Id equals k.UserId
select o);
this.gridControlEx2.DataSource = op.ToList();
}
}
public IHttpActionResult GetWinner(string id)
{
var db = new PetaPoco.Database("AGSoftware");
System.Collections.Generic.List<Entities.StorytimePost> storytimewinnerlist = new List<StorytimePost>();
foreach (var a in db.Query<Entities.StorytimePost>("Select * From StoryTimePost Where StorytimeId = @0 Group By UserId, StorytimeId, SeriesId, StorytimePostId, PostText, ImagePath, Votes, DateCreated Order By Votes Desc", id))
{
storytimewinnerlist.Add(a);
}
if (storytimewinnerlist.Count > 0)
return Ok(storytimewinnerlist);
else
return NotFound();
}
public IEnumerable <BSAMemberViewModel> GetBoyScoutMasters(string District, string Council, string Troop)
{
//Connect to the Umbraco DB
var db = new PetaPoco.Database(sConnectString);
List <BSAMemberViewModel> members = new List <BSAMemberViewModel>();
//Get an IENumberable of MeritBadges objects to iterate over
var lstMember = db.Query <BSAMember>("SELECT * FROM BSAMembers where DeleteRecordFlag = @0 and District = @1 and Council = @2 and Troop = @3", false, District, Council, Troop);
foreach (BSAMember item in lstMember)
{
BSAMemberViewModel oModel = FillViewModelBSAMember(item, false);
members.Add(oModel);
}
//Return the view with our model and comments
return(members);
}
private List<NearestNeighbour> GetNeighbours(Family family)
{
// Using PetaPoco rather than NHibernate because
// - Writing a sproc to query a sql geography type looks a heck of a lot easier than upgrading
// NHibernate Spatial to work with NH 3.3
// See http://build-failed.blogspot.co.nz/2012/02/nhibernate-spatial-part-1.html
// - NH Mapping-by-Code doesn't support Named Queries/Sprocs. Configuration has an AddNamedQuery
// method, but it's only implemented for querying in-memory.
// See https://groups.google.com/forum/?fromgroups=#!topic/nhusers/gxi225tG8aI
// - NH Mapping-by-Code does support mixing conventions with hbm.xml files, but to do so you need
// to add an xml configuration file. Since this would duplicate the fluent configuration, it's
// not clear what this achieves or what needs to go in it.
var databaseContext = new Database("DefaultConnection")
{
EnableAutoSelect = false // otherwise it'll put "select..." in front of "exec..."
};
var sql = string.Format("exec GetNearestNeighbours {0}, {1}",
family.Address.Id, // @AddressID
5); // @NumberOfNeighbours
var neighbours = databaseContext.Query<NearestNeighbour>(sql).ToList();
return neighbours;
}
