public clsUsuario GetByLogOn(String LogOn)
{
clsUsuario user;
List <clsUsuario> lista;
try
{
var sql = PetaPoco.Sql.Builder
.Append("SELECT [User_Id], [User_Nombre] , [User_LogOn], [User_Pasw], [User_Pasw_F] ")
.Append("FROM [ICBA Cirugia 2003].[cirugia].[Usuario] ")
.Append("WHERE User_LogOn = @pLogOn", new { pLogOn = LogOn });
lista = db.Fetch <clsUsuario>(sql);
if (lista.Count > 0)
{
user = lista.First();
return(user);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(null);
}
// .Append("[User_Pasw_D], [User_Obs], [User_ContDat], [User_LogOnErr], [User_EnSist], [User_Perf_T], ")
// .Append("[User_Est_T], [User_Usr_T], [User_Menu], [User_Puesto_T], [User_Cons_T], [User_Nombre_Red]")//
}
public override List <DbCodeSegment> GetTraceCodeSegments(string traceName)
{
var trace = Path.Combine(_traceDir, GetTraceName(traceName));
var codeTrace = _db.Fetch <DbCodeSegment>(@"SELECT DISTINCT LineNumber, Offset as StartByte, IntegerData2 as EndByte, ObjectName
FROM ::fn_trace_gettable('" + trace + @"', default)
WHERE EventClass IN (40,41,42,43,44) AND Offset IS NOT NULL AND ObjectName IS NOT NULL
ORDER BY ObjectName, LineNumber ASC, StartByte ASC, IntegerData2 ASC;");
return(codeTrace);
}
public override List <DbCodeSegment> GetTraceCodeSegments(string traceName)
{
var trace = Path.Combine(_traceDir, string.Format(TraceFileFormat, traceName));
var codeTrace = _db.Fetch <DbCodeSegment>(@"SELECT LineNumber, StartByte, EndByte, ObjectName, OBJECT_SCHEMA_NAME(ObjectID) SchemaName
FROM (
SELECT DISTINCT LineNumber, Offset as StartByte, IntegerData2 as EndByte, ObjectName, ObjectID
FROM ::fn_trace_gettable('" + trace + @"', default)
WHERE EventClass IN (40,41,42,43,44) AND Offset IS NOT NULL AND ObjectName IS NOT NULL AND Offset <= IntegerData2
) cs
ORDER BY SchemaName, ObjectName, LineNumber ASC, StartByte ASC, EndByte ASC;");
return(codeTrace);
}
public List <EventModel> GetEvents()
{
List <EventModel> models = new List <EventModel>();
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
models = context.Fetch <EventModel>("select * from OurEvents");
foreach (var item in models)
{
item.Images = context.Fetch <EntityImage>("select * from EntityImages where SectionID = 2 and EntityID = @0", item.OurEventID);
}
}
return(models);
}
public List <LoveStoryModel> GetLoveStories()
{
List <LoveStoryModel> models = new List <LoveStoryModel>();
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
models = context.Fetch <LoveStoryModel>("select * from OurLoveStory");
foreach (var item in models)
{
item.ImageUrl = context.Fetch <string>("select Url from EntityImages where SectionID = 3 and EntityID = @0", item.OurStoryID).FirstOrDefault();
}
}
return(models);
}
public CommentList GetComments()
{
CommentList commentList = new CommentList();
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
commentList.ParentComments = context.Fetch <ParentCommentItem>("select * from Comments where ParentCommentID is null order by datetimenow desc");
foreach (var item in commentList.ParentComments)
{
item.childcomments = context.Fetch <CommentItem>("select * from Comments where ParentCommentID = @0 order by datetimenow desc", item.CommentID);
}
}
return(commentList);
}
//
// GET: /Personnel/
public ActionResult Index()
{
User user = Session["User"] as User;
ViewBag.Username = user.Username;
//Personel List
var results = db.Fetch <Personnel>(PetaPoco.Sql.Builder
.Append("Select p.*, d.DepName as DepartmentName")
.Append("From Personnel p ")
.Append("Join Department d On p.DepartmentID = d.ID")
.Append("Where p.isActive = @0", 1));
return(View(results));
}
public woodpropModel GetWoodModelPropByID(int id)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <woodpropModel>("select * from WoodProp where id = @0", id).FirstOrDefault());
}
}
public List <Permiso> ObetnerPermisosPorUsuario(clsUsuario usuario)
{
try
{
var sql = PetaPoco.Sql.Builder
.Append("");
sql.Where(string.Format("Rol_Id = {0}", usuario.Rol_Id));
var lista = _db.Fetch <Permiso>(sql);
return(lista);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(null);
}
}
public List <EntityImage> GetMainSliderImages()
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <EntityImage>("select * from EntityImages where SectionID = 6"));
}
}
public List <woodpropModel> getWoodPropsWithPrice()
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <woodpropModel>("select * from WoodProp"));
}
}
public List <WoodProp> getWoodProps()
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <WoodProp>("select * from WoodProp"));
}
}
public static List <Models.Comment> GetUserComment(int uid, int top)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.Fetch <Models.Comment>(1, top, "SELECT \"id\", fid ,uid , \"content\" ,replytime FROM jexus_comments WHERE uid=@0 ORDER BY replytime DESC ", uid).ToList());
}
}
public static List <Models.Forums> GetUserForums(int uid, int top)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.Fetch <Models.Forums>(1, top, "SELECT \"fid\",cid,uid,ruid,title,keywords,'' as \"content\",addtime,updatetime,lastreply,views,comments,favorites,closecomment,is_top,is_hidden,ord FROM jexus_forums WHERE uid=@0 ORDER BY addtime DESC ", uid).ToList());
}
}
/// <summary>
/// 获取最新的帖子
/// </summary>
/// <returns></returns>
public static List <Models.Forums> GetLastForum(int top)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.Fetch <Models.Forums>(1, top, " SELECT * FROM jexus_forums ORDER BY addtime DESC").ToList());
}
}
public bamboo GetBambooDetailByID(int id)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <bamboo>("select * from bamboo where id = @0", id).FirstOrDefault());
}
}
public List <pinewood> GetPinewood()
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <pinewood>("select * from pinewood"));
}
}
public pinewood GetPinewoodByID(int id)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <pinewood>("select * from pinewood where id = @0", id).FirstOrDefault());
}
}
public List <bamboo> GetBamboo()
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <bamboo>("select * from bamboo"));
}
}
public static List <pixPaidGEO> loadPaidGEO()
{
string cacheName = "paidGEO";
log.Info("Settings - loadProviders ");
List <pixPaidGEO> paidGEO;
if (!cacheManager.IsIncache(cacheName))
{
var db = new PetaPoco.Database("myConnectionString");
string sQuery = "SELECT * FROM paidGEO";
try
{
var result = db.Fetch <pixPaidGEO>(sQuery);
db.CloseSharedConnection();
paidGEO = result.ToList();
cacheManager.SaveTocache(cacheName, paidGEO);
}
catch (Exception e)
{
log.Fatal("!! cannot load Settings of all providers !!", e);
}
}
return(cacheManager.GetFromCache <List <pixPaidGEO> >(cacheName));
}
protected override List <t_role> GetData()
{
using (var db = new PetaPoco.Database())
{
return(db.Fetch <t_role>("where IsUsing=1"));
}
}
private static List <pixLandingPages_X_Mask> loadLandingPagesMask_X()
{
string cacheName = "landingPages_X_Mask";
log.Info("Settings - loadLandingPagesMask_X ");
List <pixLandingPages_X_Mask> pages_X_Mask;
if (!cacheManager.IsIncache(cacheName))
{
var db = new PetaPoco.Database("myConnectionString");
string sQuery = "SELECT * FROM LandingPages_X_Mask";
try
{
var result = db.Fetch <pixLandingPages_X_Mask>(sQuery);
db.CloseSharedConnection();
pages_X_Mask = result.ToList();
cacheManager.SaveTocache(cacheName, pages_X_Mask);
}
catch (Exception e)
{
log.Error("!! cannot load Settings of all Landing pages X Mask !!", e);
}
}
return(cacheManager.GetFromCache <List <pixLandingPages_X_Mask> >(cacheName));
}
public List <clsNextLab> Seleccionar(string @where, string orderBy, string limit)
{
List <clsNextLab> lista;
try
{
var sql = PetaPoco.Sql.Builder
.Append("SELECT cod_det, res_det")
.Append("FROM dbo.lisord INNER JOIN dbo.lisbitres ON dbo.lisord.nro_ord = dbo.lisbitres.nro_ord");
if (!String.IsNullOrEmpty(@where))
{
sql.Where(@where);
}
if (!String.IsNullOrEmpty(orderBy))
{
sql.OrderBy(orderBy);
}
lista = _db.Fetch <clsNextLab>(sql);
}
catch (Exception e)
{
throw new Exception("Error en LaboratorioJMManager.Seleccionar " + e.Message);
//lista = null;
}
return(lista);
}
public UserModel GetUserByEmailandPassword(string email, string password)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <UserModel>("select * from Users where Email = @0 and Password = @1", email, password).FirstOrDefault());
}
}
public CommonInfoModel GetCommonInfo()
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <CommonInfoModel>("select * from CommonInfo where CommonInfoID = 1").FirstOrDefault());
}
}
public OurEvent GetOurEventByID(int id)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <OurEvent>("select * from OurEvents where OurEventID = @0", id).FirstOrDefault());
}
}
public OurLoveStory GetLoveStoryByLoveStoryID(int id)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <OurLoveStory>("select * from OurLoveStory where OurStoryID = @0", id).FirstOrDefault());
}
}
public EntityImage GetEntityImageByImageID(int id)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <EntityImage>("select * from EntityImages where EntityImageID = @0", id).FirstOrDefault());
}
}
public List <ImagesModel> GetAllImages()
{
var db = new PetaPoco.Database("System.Data.SqlClient");
var allImages = db.Fetch <ImagesModel>("SELECT * FROM Images");
return(allImages);
}
public List <EntityImage> GetImagesBySectionID(int sectionID)
{
using (PetaPoco.Database context = new PetaPoco.Database("DefaultConnection"))
{
return(context.Fetch <EntityImage>("select * from EntityImages where SectionID = @0", sectionID));
}
}
/// <summary>
/// Gets an event stream from SQL for a given ID
/// </summary>
/// <param name="aggregateRootId">Aggregate root ID</param>
/// <param name="startVersion">Starting version of the event stream</param>
/// <returns>List of events</returns>
public List<EventStream> GetEventStream(Guid aggregateRootId, long startVersion)
{
CreateEventTable();
using (var db = new PetaPoco.Database(_eventConnectionStringName))
{
var events = db.Fetch<EventStream>(GetEventsForId, aggregateRootId, startVersion);
return events;
}
}
public ActionResult Search(DateTime? d = null)
{
using (var db = new PetaPoco.Database("DefaultConnection"))
{
var sql = PetaPoco.Sql.Builder.Select("eaj.ahdm,eaj.ah,eaj_ftsy.ktrq,eaj_ftsy.kssj,eaj.dsr,eaj.ayms");
sql.From("eaj");
sql.InnerJoin("eaj_ftsy").On("eaj.ahdm = eaj_ftsy.ahdm");
sql.Where("eaj_ftsy.ktrq = @0", (d ?? DateTime.Now).ToString("yyyyMMdd"));
sql.OrderBy("eaj_ftsy.kssj,eaj.ahdm");
return View(db.Fetch<dynamic>(1, 200, sql));
}
}
public ActionResult CaseSearch(string xm, string fy, string start_time_year, string start_time_month,
string end_time_year, string end_time_month)
{
using (var db = new PetaPoco.Database("HssfConnection"))
{
var data =
db.Fetch<dynamic>(
"SELECT a.ahdm,a.ah,b.ktrq,b.kssj,a.dsr,a.ayms from EAJ a,EAJ_FTSY b where a.ahdm = b.ahdm and b.ktrq = @0 order by b.kssj,a.ahdm",
DateTime.Now.ToString("yyyyMMdd"));
if (!string.IsNullOrEmpty(start_time_year))
{
var startTime = new DateTime(int.Parse(start_time_year), int.Parse(start_time_month), 1);
var endTime = new DateTime(int.Parse(end_time_year), int.Parse(end_time_month), 1).AddMonths(1).AddDays(-1);
var sql = PetaPoco.Sql.Builder.Select("COUNT(*)");
sql.From("EAJ");
sql.LeftJoin("EAJ_FTSY").On("EAJ.ahdm = EAJ_FTSY.ahdm");
if (!fy.IsEmpty())
{
sql.Where("EAJ_FTSY.sly = @0", fy);
}
if (!xm.IsEmpty())
{
sql.Where("EAJ.ayms LIKE @0", "'%" + xm + "%'");
}
sql.Where("EAJ_FTSY.ktrq >= @0 AND EAJ_FTSY.ktrq <= @1", startTime.ToString("yyyyMMdd"), endTime.ToString("yyyyMMdd"));
ViewData["TotalCount"] = db.ExecuteScalar<int>(sql);
}
return View(data);
}
}
private IList<HistoryEntry> FetchInternal(QueryParameters parameters, BonusCalculationType bonusCalculation)
{
Debug.Assert(TableExists(SqlTable.WuHistory));
var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
select.Append(WhereBuilder.Execute(parameters));
GetProduction.BonusCalculation = bonusCalculation;
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
List<HistoryEntry> query = database.Fetch<HistoryEntry>(select);
return query;
}
}
}
/// <summary>
/// 获取最新的帖子
/// </summary>
/// <returns></returns>
public static List<Models.Forums> GetLastForum(int top)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.Fetch<Models.Forums>(1, top, " SELECT * FROM jexus_forums ORDER BY addtime DESC").ToList();
}
}
public static List<Models.Forums> GetUserForums(int uid, int top)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.Fetch<Models.Forums>(1, top, "SELECT \"fid\",cid,uid,ruid,title,keywords,'' as \"content\",addtime,updatetime,lastreply,views,comments,favorites,closecomment,is_top,is_hidden,ord FROM jexus_forums WHERE uid=@0 ORDER BY addtime DESC ", uid).ToList();
}
}
public static List<Models.Comment> GetUserComment(int uid, int top)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.Fetch<Models.Comment>(1, top, "SELECT \"id\", fid ,uid , \"content\" ,replytime FROM jexus_comments WHERE uid=@0 ORDER BY replytime DESC ", uid).ToList();
}
}