public static DataTable GetAtendimentosRealizadosDia(
string pDOCMOV_REGUSER,
string pDOCMOV_REGDATE,
decimal pNUC_ID,
ConnectionInfo pInfo
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = DocumentoMovimentoQD.qDocumentoMovimentoCount;
lQuery += string.Format(" WHERE DOCMOV.DOCMOV_STATUS NOT IN ('I','E') AND DOCMOV.CAT_ID IN ({0},{1}) AND DOCMOV.{2} = >>{2} AND TO_CHAR(DOCMOV.{3},'dd/MM/yyyy') = >>{3}", (decimal)Categoria.Atendido, (decimal)Categoria.AtendidoRetorno, DocumentoMovimentoQD._DOCMOV_REGUSER.Name,
DocumentoMovimentoQD._DOCMOV_REGDATE.Name);
lQuery += string.Format(" AND EXISTS (SELECT 0 FROM DOCUMENTO DOC WHERE DOC.DOC_ID = DOCMOV.DOC_ID AND DOC.{0} = >>{0})", DocumentoQD._NUC_ID.Name);
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(DocumentoMovimentoQD._DOCMOV_REGUSER.Name, pDOCMOV_REGUSER, (ItemType)DocumentoMovimentoQD._DOCMOV_REGUSER.DBType);
lSelect.Fields.Add(DocumentoMovimentoQD._DOCMOV_REGDATE.Name, pDOCMOV_REGDATE, (ItemType)DocumentoMovimentoQD._DOCMOV_REGDATE.DBType);
lSelect.Fields.Add(DocumentoQD._NUC_ID.Name, pNUC_ID, (ItemType)DocumentoQD._NUC_ID.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public override IASTNode VisitTableFactor(MySqlCommandParser.TableFactorContext ctx)
{
TableFactorSegment result = new TableFactorSegment();
if (null != ctx.subquery())
{
SelectCommand subquery = (SelectCommand)Visit(ctx.subquery());
SubQuerySegment subquerySegment = new SubQuerySegment(ctx.subquery().Start.StartIndex, ctx.subquery().Stop.StopIndex, subquery);
SubQueryTableSegment subqueryTableSegment = new SubQueryTableSegment(subquerySegment);
if (null != ctx.alias())
{
subqueryTableSegment.SetAlias((AliasSegment)Visit(ctx.alias()));
}
result.SetTable(subqueryTableSegment);
}
if (null != ctx.tableName())
{
SimpleTableSegment table = (SimpleTableSegment)Visit(ctx.tableName());
if (null != ctx.alias())
{
table.SetAlias((AliasSegment)Visit(ctx.alias()));
}
result.SetTable(table);
}
if (null != ctx.tableReferences())
{
CollectionValue <TableReferenceSegment> tableReferences = (CollectionValue <TableReferenceSegment>)Visit(ctx.tableReferences());
result.TableReferences.AddAll(tableReferences.GetValue());
}
return(result);
}
private void GenerateView()
{
ViewView = AgentUICoreMediator.UIManager.GenerateViewOfViewEntityArea();
ViewView.MultipleSelection = ViewInitializer.MultipleSelection;
var selectcommand = new SelectCommand(this);
ViewView.AddCommand(selectcommand.CommandManager);
ViewView.DataContainerLoaded += ViewView_DataContainerLoaded;
ViewView.ItemDoubleClicked += ViewView_ItemDoubleClicked;
// ViewView.CommandExecuted += ViewView_CommandExecuted;
//ViewView.DataSelected += ViewView_DataSelected;
//if (ViewInitializer.ViewEntity.Columns.Count == 0)
//{
// var result = GetFullEntity(ViewInitializer.ViewEntity.ID);
// ViewInitializer.ViewEntity = result.Entity;
// ViewInitializer.Permissoins = result.Permissoins;
// ViewInitializer.UICompositions = result.UICompositions;
//}
ManageViewView();
}
public void ExecuteTest()
{
//po referencjach do kolumn
List <Column> originalColumns = new List <Column>();
var column1 = new Mock <Column>();
var column2 = new Mock <Column>();
var column3 = new Mock <Column>();
originalColumns.Add(column1.Object);
originalColumns.Add(column2.Object);
originalColumns.Add(column3.Object);
List <Column> selectedColumns = new List <Column>();
selectedColumns.Add(column1.Object);
selectedColumns.Add(column3.Object);
SelectCommand target = new SelectCommand(selectedColumns);
var table = new Mock <Table>(); //tabela oryginalna
var table2 = new Mock <Table>(); //tabela, która powinna powstać
table.Setup(foo => foo.Columns).Returns(originalColumns);
table2.Setup(foo => foo.Columns).Returns(selectedColumns);
List <Column> receivedColumns = target.Execute(table.Object).Columns;
CollectionAssert.AreEqual(table2.Object.Columns, receivedColumns);
//po indeksach
List <int> selectedIndeces = new List <int>();
selectedIndeces.Add(0);
selectedIndeces.Add(2);
target = new SelectCommand(selectedIndeces);
receivedColumns = target.Execute(table.Object).Columns;
CollectionAssert.AreEqual(table2.Object.Columns, receivedColumns);
}
protected void Button1_Click1(object sender, EventArgs e)
{
int count = 0;
string cmdText = @"SELECT 1 FROM Login
WHERE Username = @uname
AND Password = @pwd";
using (SqlConnection cnn = new SqlConnection(conString))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
cnn.Open();
cmd.Parameters.Add("@uname", SqlDbType.NVarChar).Value = textBox1.Text;
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = textBox1.Text;
using (SqlDataReader myReader = SelectCommand.ExecuteReader())
{
while (myReader.Read())
{
count = count + 1;
}
}
}
if (count == 1)
{
//open form
}
else
{
}
}
void RaisePostBackEvent(string eventArgument)
{
if (eventArgument == "Upload")
{
HttpPostedFile uploadedFile = Page.Request.Files[ClientID + "_Upload"];
if (uploadedFile != null && uploadedFile.ContentLength > 0)
{
FileManagerItemInfo dir = GetCurrentDirectory();
Controller.ProcessFileUpload(dir, uploadedFile);
}
}
else if (eventArgument == "Select")
{
if (SelectCommand != null)
SelectCommand.Invoke(this, EventArgs.Empty);
}
else if (eventArgument == "Cancel")
{
if (CancelCommand != null)
CancelCommand.Invoke(this, EventArgs.Empty);
}
else if (eventArgument.StartsWith("Toolbar:", StringComparison.Ordinal))
{
int i = int.Parse(eventArgument.Substring(8));
CustomToolbarButton button = CustomToolbarButtons[i];
OnToolbarCommand(new CommandEventArgs(button.CommandName, button.CommandArgument));
}
}
/// <summary>
/// Selects those genomes from the competitive part of the population that are allowed to reproduce.
/// </summary>
/// <param name="generation">
/// The generation.
/// </param>
/// <param name="instancesForEvaluation">
/// The <typeparamref name="TInstance"/>s to use for evaluation.
/// </param>
/// <returns>
/// Competitive genomes allowed to reproduce.
/// </returns>
private TournamentWinnersWithRank <TResult> PerformSelection(int generation, IEnumerable <TInstance> instancesForEvaluation)
{
var selectCommandMessage = new SelectCommand <TInstance>(
this._population.GetCompetitiveIndividuals().Select(genome => new ImmutableGenome(genome)),
instancesForEvaluation,
generation);
var selectGenomeCommand = this._tournamentSelector.Ask <SelectionResultMessage <TResult> >(selectCommandMessage).ContinueWith(
tr =>
{
if (tr.IsFaulted)
{
// It was impossible to determine the best genomes, i.e. something really bad happened.
// In this case, we throw an exception for the caller to handle.
throw new InvalidOperationException("Selection command resulted in an exception.");
}
var result = new TournamentWinnersWithRank <TResult>(
tr.Result.CompetitiveParents,
tr.Result.GenerationBest,
tr.Result.GenerationBestResult,
tr.Result.GenomeToRank);
return(result);
});
selectGenomeCommand.Wait();
return(selectGenomeCommand.Result);
}
public static void Execute(Executor executor, SelectCommand setCommand)
{
var table = executor.DatabaseSelected.TableSelected;
var newResult = new SelectResult
{
DatabaseName = executor.DatabaseSelected.Name,
TableName = table.Name,
Data = new Dictionary <uint, Property[]>()
};
foreach (var data in table)
{
var properties = data.Datasets
.Select(d => new Property
{
Name = d.Key,
Type = (PropertyType)d.Value.Type,
Value = d.Value.Value
})
.ToArray();
newResult.Data.Add(data.Id, properties);
}
executor.Results.Add(newResult);
executor.ExecuteNextCommand();
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="columnName">字段名称</param>
/// <param name="op">条件运算符</param>
/// <param name="command">选择语句</param>
internal SqlBasicCommandCondition(AbstractSqlCommand baseCommand, String columnName, SqlOperator op, SelectCommand command)
: base(baseCommand)
{
this._columnName = columnName;
this._operator = op;
this._command = command;
}
public static int Delete(
ConnectionInfo pInfo,
decimal pMATERIAL_ID
)
{
string lQuery = "";
int lResult;
DataTable lTable = new DataTable();
lQuery = " DELETE FROM " + MATDETXFORNECEDORQD.TableName;
lQuery += " WHERE MATERIAL_ID = " + pMATERIAL_ID;
SelectCommand lSelect = new SelectCommand(lQuery);
try
{
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
lResult = 1;
}
catch (Exception Err)
{
lResult = 0;
}
return lResult;
}
public void LinqCreateNotLikeConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand expectedCommand = fakeDb.CreateSelectCommand("");
SelectCommand actualCommand = fakeDb.CreateSelectCommand("");
SqlConditionBuilder expectedConditionBuilder = expectedCommand.ConditionBuilder;
SqlBasicParameterCondition expectedCondition = expectedConditionBuilder.NotLike("TestColumn1", "test1");
SqlBasicParameterCondition actualCondition = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test1.NotLike("test1")) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition, actualCondition);
SqlBasicParameterCondition expectedCondition2 = expectedConditionBuilder.NotLikeAll("TestColumn1", "test2");
SqlBasicParameterCondition actualCondition2 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test1.NotLikeAll("test2")) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition2, actualCondition2);
SqlBasicParameterCondition expectedCondition3 = expectedConditionBuilder.NotLikeStartWith("TestColumn1", "test3");
SqlBasicParameterCondition actualCondition3 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test1.NotLikeStartWith("test3")) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition3, actualCondition3);
SqlBasicParameterCondition expectedCondition4 = expectedConditionBuilder.NotLikeEndWith("TestColumn1", "test4");
SqlBasicParameterCondition actualCondition4 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test1.NotLikeEndWith("test4")) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition4, actualCondition4);
}
protected override void Awake()
{
base.Awake();
eRightMouseClick.AddListener(CancelCurrentCommand);
selectCommand = new SelectCommand(null, Select);
selectCommand.bPaused = true;
}
public void SelectRegions()
{
var regions = canvas.SelectedRooms.Select(p => p.Region).Distinct().ToList();
var cmd = new SelectCommand();
cmd.Execute(canvas, SelectTypes.Region, regions);
}
public void DoCommand(Command command)
{
switch (command.type)
{
case CommandType.DESCRIBE:
DescribeCommand describeCommand = (DescribeCommand)command;
describeCommand.Execute(mapController, describeCommand.id != null ? FindEntity(describeCommand.id) : null);
break;
case CommandType.SELECT:
SelectCommand selectCommand = (SelectCommand)command;
selectCommand.Execute(mapController, GetPlayerById(selectCommand.playerId));
break;
case CommandType.PERFORMACTION:
PerformActionCommand performActionCommand = (PerformActionCommand)command;
performActionCommand.Execute(mapController, GetPlayerById(performActionCommand.playerId), GetPlayerById(performActionCommand.playerId).selectedEntity);
break;
case CommandType.ENDTURN:
EndTurnCommand endTurnCommand = (EndTurnCommand)command;
endTurnCommand.Execute(turnController);
break;
default:
break;
}
}
/// <summary>
/// 设置指定的查询语句并返回当前语句
/// </summary>
/// <param name="command">选择语句</param>
/// <param name="type">主题类型</param>
/// <param name="relativeID">相关ID</param>
/// <param name="includeHide">是否包含隐藏主题</param>
/// <returns>当前语句</returns>
internal static SelectCommand Where(this SelectCommand command, ForumTopicType type, Int32 relativeID, Boolean includeHide)
{
SqlConditionBuilder c = command.ConditionBuilder;
AbstractSqlCondition condition = command.WhereCondition as AbstractSqlCondition;
AbstractSqlCondition temp = null;
//不包含隐藏
if (!includeHide)
{
temp = c.Equal(ForumTopicRepository.ISHIDE, false);
condition = (condition == null ? temp : condition & temp);
}
if (type == ForumTopicType.Default) //普通讨论板中包括题目专属讨论板的帖子
{
temp = c.LessThan(ForumTopicRepository.TYPE, (Byte)ForumTopicType.Contest); //普通0，题目1，竞赛2
condition = (condition == null ? temp : condition & temp);
}
else//竞赛或题目专属讨论板只有专属帖子
{
temp = c.Equal(ForumTopicRepository.TYPE, (Byte)type) & c.Equal(ForumTopicRepository.RELATIVEID, relativeID);
condition = (condition == null ? temp : condition & temp);
}
return(command.Where(condition));
}
public static DataTable GetAllRelatorioPendente(
ConnectionInfo pInfo, string pPESF_proposto, string pDATA
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = RELATORIOVIAGEMQD.qRELATORIOVIAGEMPendente;
lQuery += @" WHERE D.DIA_STATUS='A'
AND P.PES_STATUS='A'
AND PC.PRES_STATUS='A'
AND PF.PESF_STATUS='A'
AND DP.DIAPRO_STATUS='A'
AND DOC.DDOC_STATUS='A'
and D.PRES_ID = PC.PRES_ID
AND D.DOC_ID = DOC.DOC_ID
AND D.DIA_ID = DP.DIA_ID
AND D.PESF_PROPONENTE = PF.PESF_ID
AND P.PES_ID = PF.PES_ID
AND (SELECT COUNT(*) FROM RELATORIOVIAGEM WHERE DIAPRO_ID = DP.DIAPRO_ID) = 0
AND DP.PESF_PROPOSTO = {0}
AND PC.PRES_DATAPRESTACAO <= '{1}'
ORDER BY PC.PRES_DATAPRESTACAO";
lQuery = string.Format(lQuery, pPESF_proposto, pDATA);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllEscalaMesAtuacao(
ConnectionInfo pInfo,
String pESC_DIA,
decimal pATUA_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = EscalaQD.qEscalaAtividadeList;
lQuery += " WHERE ESC_STATUS NOT IN ('I','E') ";
lQuery += " AND A.ATDEF_ID = B.ATDEF_ID ";
lQuery += string.Format(" AND A.{0} = >>{0} ", EscalaQD._ATUA_ID.Name);
lQuery += string.Format(" AND to_char(A.{0}, 'mm') = to_char(>>{0}, 'mm')", EscalaQD._ESC_DIA.Name);
lQuery += string.Format(" AND to_char(A.{0}, 'yyyy') = to_char(>>{0}, 'yyyy')", EscalaQD._ESC_DIA.Name);
lQuery += "Order By ESC_DIA ";
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(EscalaQD._ATUA_ID.Name, pATUA_ID, (ItemType)EscalaQD._ATUA_ID.DBType);
lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);
lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
/// <summary>
/// 按指定列排序并返回当前语句
/// </summary>
/// <typeparam name="T">实体类类型</typeparam>
/// <param name="cmd">查询语句</param>
/// <param name="expr">实体类属性</param>
/// <param name="orderType">排序类型</param>
/// <exception cref="ExpressionInvalidException">表达式不正确</exception>
/// <exception cref="NullAttributeException">没有设置特性</exception>
/// <returns>当前语句</returns>
/// <example>
/// <code lang="C#">
/// <![CDATA[
/// public class UserRepository : DatabaseTable<User>
/// {
/// //other necessary code
///
/// public List<User> GetAllEntities()
/// {
/// return this.Select()
/// .Querys<User>(c => new { c.UserID, c.UserName })
/// .OrderBy<User>(c => c.UserID, SqlOrderType.Desc)
/// .ToEntityList<User>(this);
///
/// //SELECT UserID, UserName From tbl_Users ORDER BY UserID DESC
/// }
/// }
/// ]]>
/// </code>
/// </example>
public static SelectCommand OrderBy <T>(this SelectCommand cmd, Expression <Func <T, Object> > expr, SqlOrderType orderType)
{
DatabaseColumnAttribute attr = SelectCommandExtension.GetColumnAttribute(cmd, expr.Body);
String tableName = EntityHelper.InternalGetTableName(typeof(T));
return(cmd.OrderBy(tableName, attr.ColumnName, orderType));
}
public static DataTable GetAllBibliotecapendenciasdevolucao2(
ConnectionInfo pInfo,
decimal ept_pesf_id,
string PES_NOME,
string data
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery += Biblioteca_EmprestimoQD.GetAllBibliotecapendenciasdevolucao2;
lQuery += @" WHERE e.ept_pesf_id=" + ept_pesf_id + @"
AND e.ept_id=b.ept_id
and e.ept_status='A'
AND B.IEPT_STATUS='A'
),e.ept_pesf_id,P.PES_NOME";
lQuery += Biblioteca_EmprestimoQD.GetAllBibliotecapendenciasdevolucaosub2;
lQuery += @" where e.ept_id=b.ept_id
AND e.ept_pesf_id=" + ept_pesf_id + @"
and e.ept_status='A'
AND B.IEPT_STATUS='A'
AND UPPER(PES_NOME) LIKE UPPER('%{0}%')
and f.pes_id= P.PES_ID
and e.ept_data_fim > to_date('" + data + "')";
lQuery = string.Format(lQuery, PES_NOME);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static LoginUserDo VerifyLogin(string pEUSR, string pEusr_Password, string pEusr_Type, ConnectionInfo pConnectionInfo)
{
bool lReturn;
string lQuery;
DataTable lTable;
lQuery = UsersQD.qUsersList;
lTable = new DataTable();
if (pEusr_Type == "1")//Portal Garagem
{
lQuery += String.Format(" WHERE {0} = >>{0} AND {1} = >>{1} ", UsersQD._USR_LOGIN.Name, UsersQD._USR_PASSWORD.Name);
lQuery += " AND USR_STATUS='A' ";
SelectCommand lSelect = new SelectCommand(lQuery);
pEusr_Password = APB.Framework.Encryption.Crypto.Encode(pEusr_Password);
lSelect.Fields.Add(UsersQD._USR_LOGIN.Name, pEUSR, (ItemType)UsersQD._USR_LOGIN.DBType);
lSelect.Fields.Add(UsersQD._USR_PASSWORD.Name, pEusr_Password, (ItemType)UsersQD._USR_PASSWORD.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));
}
lReturn = (lTable.Rows.Count > 0) ? true : false;
// User Accept, Check TP_ID
if (lReturn)
{
return new LoginUserDo((decimal)lTable.Rows[0]["USR_ID"], (string)lTable.Rows[0]["USR_LOGIN"]);
}
return null;
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
if (cmd.PageSize > 0 && cmd.PageIndex == 1)
{
sb.AppendSelectLimit(cmd.PageSize);
}
else if (cmd.PageSize > 0 && cmd.PageIndex > 1)
{
sb.AppendSelectLimit(cmd.PageSize * (realPageIndex - 1), cmd.PageSize);
}
return(sb.ToString());
}
public static DataTable GetAllModulosUsuario(ConnectionInfo pInfo, string id_usuario, string id_sistema)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = "select distinct t5.* from ";
lQuery += " SEG_USUARIOXGRUPO t1";
lQuery += " join SEG_GRUPO t2";
lQuery += " on (t1.GRP_ID = t2.GRP_ID";
lQuery += " and t1.SUSR_ID = "+id_usuario;
lQuery += " and t1.susr_regstatus='A')";
lQuery += " join SEG_GRUPOXPROGRAMA t3 ";
lQuery += " on (t2.GRP_ID = t3.GRP_ID";
lQuery += " and t3.grp_regstatus = 'A')";
lQuery += " join SEG_PROGRAMA t4";
lQuery += " on (t3.PRO_ID = t4.PRO_ID";
lQuery += " and t4.pro_regstatus = 'A')";
lQuery += " join SEG_MODULO t5";
lQuery += " on (t4.MOD_ID = t5.MOD_ID";
lQuery += " and t5.mod_regstatus = 'A' and t5.sis_id=" + id_sistema + ")";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
private void SelectItem(object currentItem)
{
if (DisplayMemberPath != String.Empty)
{
var propInfo = currentItem.GetType().GetProperties().
Where(p => p.Name == DisplayMemberPath).FirstOrDefault();
if (propInfo != null)
{
Text = propInfo.GetValue(currentItem, null).ToString();
}
else
{
Text = currentItem.ToString();
}
}
else
{
Text = currentItem.ToString();
}
SelectAll();
SelectCommand.Execute(currentItem);
popup.IsOpen = false;
}
public static int Delete(
ConnectionInfo pInfo,
string pTRA_ID
)
{
string lQuery = "";
int lResult;
DataTable lTable = new DataTable();
lQuery = " DELETE FROM " + TRAMITACAOQD.TableName;
lQuery += " WHERE " + TRAMITACAOQD._TRA_ID.Name + " = " + pTRA_ID;
SelectCommand lSelect = new SelectCommand(lQuery);
try
{
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
lResult = 1;
}
catch (Exception Err)
{
lResult = 0;
}
return lResult;
}
/// <summary>
/// Resets this instance.
/// </summary>
public void Reset()
{
this._selectCommand = null;
this._assignedMiniTournaments.Clear();
this._openMiniTournaments.Clear();
this._miniTournamentResults.Clear();
}
public static DataTable GetDIARIAXTIPODESLOCAMENTObyID(
ConnectionInfo pInfo, string pDIA_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery += " SELECT DISTINCT DT.DIA_ID, TD.DESLOC_ID, TD.DESLOC_NOME ";
lQuery += " FROM DIARIAXTIPODESLOCAMENTO DT, TIPODESLOCAMENTO TD ";
lQuery += " WHERE DIATIPO_STATUS='A' ";
lQuery += " AND TD.DESLOC_STATUS='A' ";
lQuery += " AND DT.DESLOC_ID = TD.DESLOC_ID ";
lQuery += " AND DT.DESLOC_ID NOT IN (8) ";
lQuery += " AND DIA_ID = " + pDIA_ID + " ";
lQuery += " UNION ";
lQuery += " SELECT DISTINCT DT.DIA_ID, TD.DESLOC_ID, TD.DESLOC_NOME || ' - ' || DT.DIATIPO_OUTROS AS DESLOC_NOME ";
lQuery += " FROM DIARIAXTIPODESLOCAMENTO DT, TIPODESLOCAMENTO TD ";
lQuery += " WHERE DIATIPO_STATUS='A' ";
lQuery += " AND TD.DESLOC_STATUS='A' ";
lQuery += " AND DT.DESLOC_ID = TD.DESLOC_ID ";
lQuery += " AND DT.DESLOC_ID = 8 ";
lQuery += " AND DIA_ID = " + pDIA_ID + " ";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
void RefreshSchema()
{
// creates metadata
if (SelectCommand == null)
{
throw new InvalidOperationException("SelectCommand should be valid");
}
if (SelectCommand.Connection == null)
{
throw new InvalidOperationException("SelectCommand's Connection should be valid");
}
CommandBehavior behavior = CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo;
if (SelectCommand.Connection.State != ConnectionState.Open)
{
SelectCommand.Connection.Open();
behavior |= CommandBehavior.CloseConnection;
}
OdbcDataReader reader = SelectCommand.ExecuteReader(behavior);
_schema = reader.GetSchemaTable();
reader.Close();
// force creation of commands
_insertCommand = null;
_updateCommand = null;
_deleteCommand = null;
_tableName = String.Empty;
}
public static DataTable AutenticacaoNpj(ConnectionInfo pConnectionInfo, string pSusr_Login, string pSusr_Senha)
{
DataTable lTable = new DataTable();
string lQuery = "";
pSusr_Senha = APB.Framework.Encryption.Crypto.Encode(pSusr_Senha);
lQuery = SystemUserQD.qAutenticacaoNpj;
lQuery += String.Format(" WHERE {0} = >>{0} AND {1} = >>{1} ", SystemUserQD._SUSR_LOGIN.Name, SystemUserQD._SUSR_PASSWORD.Name);
lQuery += " AND A.PES_ID = B.PES_ID ";
lQuery += " AND B.PES_ID = C.PES_ID ";
lQuery += " AND C.PESF_STATUS = 'A' ";
lQuery += " AND A.SUSR_STATUS = 'A' ";
lQuery += " AND C.SET_ID = D.SET_ID ";
lQuery += " AND N.NUC_ID = D.NUC_ID ";
lQuery += " AND N.NUC_ID = SN.NUC_ID ";
lQuery += " AND A.SUSR_ID = SN.SUSR_ID ";
lQuery += " AND C.FUNC_ID = E.FUNC_ID ";
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(SystemUserQD._SUSR_LOGIN.Name, pSusr_Login, (ItemType)SystemUserQD._SUSR_LOGIN.DBType);
lSelect.Fields.Add(SystemUserQD._SUSR_PASSWORD.Name, pSusr_Senha, (ItemType)SystemUserQD._SUSR_PASSWORD.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));
return lTable;
}
public static DataTable GetMeusTRECHOS(
ConnectionInfo pInfo, decimal pDIA_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery += @" select A.DIATRE_ID,A.DIA_ID,F.UF_DESC AS UFORIGEM,B.CID_DESC AS ORIGEM ,F.UF_DESC AS UFDESTINO, E.CID_DESC AS DESTINO,A.DIATRE_DATA
from DIARIATRECHO A, CIDADE B ,CIDADE E,
DIARIA C,UNIDADEFEDERATIVA F,UNIDADEFEDERATIVA G
WHERE A.DIA_ID = C.DIA_ID
AND C.DIA_ID = A.DIA_ID
AND B.cid_id = A.cid_id_localorigem
AND E.cid_id = A.cid_id_localdestino
AND B.CID_STATUS='A'
AND E.CID_STATUS='A'
AND F.UF_STATUS='A'
AND A.DIATRE_STATUS = 'A'
AND F.UF_ID = B.UF_ID
AND G.UF_ID = B.UF_ID
AND A.DIA_ID = {0}";
lQuery = string.Format(lQuery, pDIA_ID);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
/// <summary>
/// 初始化Sql连接语句抽象类
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="joinType">连接模式</param>
/// <param name="currentTableName">当前表格名称</param>
/// <param name="currentTableField">当前表格主键</param>
/// <param name="anotherTableField">另个表格主键</param>
protected AbstractSqlJoin(SelectCommand baseCommand, SqlJoinType joinType, String currentTableName, String currentTableField, String anotherTableField)
{
this._joinType = joinType;
this._currentTableName = currentTableName;
this._currentTableKeyField = currentTableField;
this._anotherTableKeyField = anotherTableField;
}
public static DataTable GetAllPERIODOXSETORNOME(
ConnectionInfo pInfo, Decimal pSET_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
// lQuery = PERIODOXSETORQD.qPERIODOXSETORList;
// lQuery += @" WHERE PERQ_STATUS='A'
// AND S.SET_ID = P.SET_ID
// AND S.SET_STATUS='A'
// AND P.PERQ_DIAINICIO <= SYSDATE
// AND P.PERQ_DATAFIM >= SYSDATE
// AND S.SET_ID = " + pSET_ID;
lQuery += @" SELECT S.*,P.*
from PERIODOXSETOR P , SETOR S WHERE PERQ_STATUS='A'
AND S.SET_ID = P.SET_ID
AND S.SET_STATUS='A'
AND S.SET_ID = " + pSET_ID;
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetExtratoPortaria(
ConnectionInfo pInfo, string pPORT_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery += @" SELECT DIAP.DIA_ID,PORT.PORT_NUMERO PORTARIA, TO_CHAR(PORT.PORT_REGDATE, 'DD/MM/YYYY')PORT_REGDATE
,DIAQ.DIAQT_DIARIATOTAL TOTAL,TO_CHAR(DIAQ.DIAQT_DATADEINICIO, 'DD/MM/YYYY') INI, TO_CHAR(DIAQ.DIAQT_DATADETERMINO, 'DD/MM/YYYY') FIM
,(SELECT UPPER(CID.CID_DESC) FROM CIDADE CID WHERE CID.CID_ID = DIAQ.CID_ID_LOCALDEPARTIDA) ORIGEM
,(SELECT UPPER(CID.CID_DESC) FROM CIDADE CID WHERE CID.CID_ID = DIAQ.CID_ID_LOCALDEDESTINO) DESTINO
FROM PORTARIA PORT LEFT JOIN DIARIAXPORTARIA DIAP
ON PORT.PORT_ID = DIAP.PORT_ID
LEFT JOIN DIARIAQUANTIDADE DIAQ
ON DIAP.DIA_ID = DIAQ.DIA_ID
WHERE PORT.PORT_ID = {0}
ORDER BY INI ";
lQuery = string.Format(lQuery, pPORT_ID);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public SalesPriceForBaseUOMWithTemplateItem(PXCache cache, int?inventoryID, string uom, decimal qty) : base(cache, inventoryID, uom, qty)
{
SelectCommand.OrderByNew <OrderBy <Desc <ARSalesPrice.isPromotionalPrice,
Desc <ARSalesPrice.siteID,
Desc <ARSalesPrice.breakQty,
Asc <InventoryItem.isTemplate> > > > > >();
}
public override IASTNode VisitSelectClause(SqlServerCommandParser.SelectClauseContext context)
{
var result = new SelectCommand();
result.Projections = (ProjectionsSegment)Visit(context.projections());
if (null != context.duplicateSpecification())
{
result.Projections.SetDistinctRow(IsDistinct(context));
}
if (null != context.fromClause())
{
CollectionValue <TableReferenceSegment> tableReferences = (CollectionValue <TableReferenceSegment>)Visit(context.fromClause());
foreach (var tableReferenceSegment in tableReferences.GetValue())
{
result.TableReferences.Add(tableReferenceSegment);
}
}
if (null != context.whereClause())
{
result.Where = (WhereSegment)Visit(context.whereClause());
}
if (null != context.groupByClause())
{
result.GroupBy = (GroupBySegment)Visit(context.groupByClause());
}
if (null != context.orderByClause())
{
result.OrderBy = (OrderBySegment)Visit(context.orderByClause());
}
return(result);
}
public void CreateNullableConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand baseCommand = fakeDb.CreateSelectCommand("");
SqlConditionBuilder conditionBuilder = baseCommand.ConditionBuilder;
AbstractSqlCondition baseCondition = conditionBuilder.Equal("TestColumn2", 1);
AbstractSqlCondition expectedCondition = baseCondition & null;
AbstractSqlCondition actualCondition = baseCondition;
Assert.AreEqual(expectedCondition, actualCondition);
AbstractSqlCondition expectedCondition2 = null & baseCondition;
AbstractSqlCondition actualCondition2 = baseCondition;
Assert.AreEqual(expectedCondition2, actualCondition2);
AbstractSqlCondition expectedCondition3 = baseCondition | null;
AbstractSqlCondition actualCondition3 = baseCondition;
Assert.AreEqual(expectedCondition3, actualCondition3);
AbstractSqlCondition expectedCondition4 = null | baseCondition;
AbstractSqlCondition actualCondition4 = baseCondition;
Assert.AreEqual(expectedCondition4, actualCondition4);
}
private OrderByContext CreateOrderByContextForDistinctRowWithoutGroupBy(SelectCommand selectCommand, GroupByContext groupByContext)
{
if (!groupByContext.GetItems().Any() && selectCommand.Projections.IsDistinctRow())
{
int index = 0;
ICollection <OrderByItem> orderByItems = new LinkedList <OrderByItem>();
foreach (var projectionSegment in selectCommand.Projections.GetProjections())
{
if (projectionSegment is ColumnProjectionSegment columnProjectionSegment)
{
var columnOrderByItemSegment = new ColumnOrderByItemSegment(columnProjectionSegment.GetColumn(), OrderDirectionEnum.ASC);
OrderByItem item = new OrderByItem(columnOrderByItemSegment);
item.SetIndex(index);
orderByItems.Add(item);
index++;
}
}
if (orderByItems.Any())
{
return(new OrderByContext(orderByItems, true));
}
}
return(null);
}
public static DataTable GetAllMEIADIARIABUSCAID(
ConnectionInfo pInfo, string pCID_DESC
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = MEIADIARIAQD.qMeiaDiariaListCompleteBuscaID;
lQuery += @" WHERE M.DIACID_ID_ORIGEM = C1.CID_ID
AND M.DIACID_ID_DESTINO = C2.CID_ID
AND M.TVD_ID = T.TVD_ID
AND C1.UF_ID = U1.UF_ID
AND C2.UF_ID = U2.UF_ID
AND C1.CID_DESC LIKE '%{0}%'
AND C1.CID_STATUS='A'
AND T.TVD_STATUS='A'
AND C2.CID_STATUS='A'
AND M.MEIA_STATUS='A'
AND U1.UF_STATUS='A'
AND U2.UF_STATUS='A' ";
lQuery = string.Format(lQuery, pCID_DESC);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
/// <summary>
/// 初始化Sql连接语句抽象类
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="joinType">连接模式</param>
/// <param name="currentTableName">当前表格名称</param>
/// <param name="currentTableField">当前表格主键</param>
/// <param name="anotherTableField">另个表格主键</param>
protected AbstractSqlJoin(SelectCommand baseCommand, SqlJoinType joinType, String currentTableName, String currentTableField, String anotherTableField)
{
this._joinType = joinType;
this._currentTableName = currentTableName;
this._currentTableKeyField = currentTableField;
this._anotherTableKeyField = anotherTableField;
}
public void Dispose()
{
SelectCommand.Dispose();
UpdateCommand.Dispose();
InsertCommand.Dispose();
DeleteCommand.Dispose();
}
/// <summary>
/// 设置指定的查询语句并返回当前语句
/// </summary>
/// <param name="command">选择语句</param>
/// <param name="topics">主题列表</param>
/// <param name="includeHide">是否包含隐藏主题</param>
/// <returns>当前语句</returns>
internal static SelectCommand Where(this SelectCommand command, IList <ForumTopicEntity> topics, Boolean includeHide)
{
SqlConditionBuilder c = command.ConditionBuilder;
AbstractSqlCondition condition = command.WhereCondition as AbstractSqlCondition;
AbstractSqlCondition temp = null;
if (!includeHide)
{
temp = c.Equal(ForumPostRepository.ISHIDE, false);
condition = (condition == null ? temp : condition & temp);
}
if (topics != null)
{
temp = c.In <Int32>(ForumPostRepository.TOPICID, () =>
{
List <Int32> ids = new List <Int32>();
for (Int32 i = 0; i < topics.Count; i++)
{
ids.Add(topics[i].TopicID);
}
return(ids);
});
condition = (condition == null ? temp : condition & temp);
}
return(command.Where(condition));
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList());
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList());
sb.AppendWhere(baseCommand.WhereCondition);
sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList());
sb.AppendHaving(baseCommand.InternalGetHavingCondition());
sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse);
if (baseCommand.PageSize > 0 && baseCommand.RecordStart <= 0)
{
sb.AppendSelectLimit(baseCommand.PageSize);
}
else if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)
{
sb.AppendSelectLimit(baseCommand.RecordStart, baseCommand.PageSize);
}
return(sb.ToString());
}
public static string GetPassword(string pSusr_Login, ConnectionInfo pConnectionInfo)
{
bool lReturn;
string lQuery = "";
string lPassWord = "";
DataTable lTable;
lQuery = SystemUserQD.qSystemUserList;
lQuery += String.Format(" WHERE {0} = >>{0} ", SystemUserQD._SUSR_LOGIN.Name);
lQuery += " AND SUSR_STATUS='A' ";
SelectCommand lSelect = new SelectCommand(lQuery);
lSelect.Fields.Add(SystemUserQD._SUSR_LOGIN.Name, pSusr_Login, (ItemType)SystemUserQD._SUSR_LOGIN.DBType);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));
lReturn = (lTable.Rows.Count > 0) ? true : false;
// User Accept, Get Password
if (lReturn)
{
// Decodificar Senha para Envio
lPassWord = APB.Framework.Encryption.Crypto.Decode(lTable.Rows[0]["SUSR_PASSWORD"].ToString());
}
return lPassWord;
}
public void LinqSelectTest()
{
IDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient");
TestEntityRepository repository = new TestEntityRepository(fakeDb);
SelectCommand expectedCommand = fakeDb.CreateSelectCommand(repository.TableName)
.Querys("TestColumn1", "TestColumn2", "TestColumn5", "TestColumn8")
.Query("TestColumn3", "TTTT")
.Query(SqlAggregateFunction.Max, "TestColumn4", "MMMM")
.Where(c => c.GreaterThanOrEqual("TestColumn2", 123) | (c.GreaterThan("TestColumn4", DateTime.Now) & c.LessThan("TestColumn7", DateTime.Now.AddDays(7))))
.GroupBy("TestColumn3")
.InnerJoin("TestColumn2", "TestTable", "TestColumn2")
.OrderBy("TestTable", "TestColumn6", SqlOrderType.Asc);
SelectCommand actualCommand = fakeDb.CreateSelectCommand(repository.TableName)
.Querys <TestEntity>(c => new { c.Test1, c.Test2, c.Test5, c.Test8 })
.Query <TestEntity>(c => c.Test3, "TTTT")
.Query <TestEntity>(c => c.Test4, SqlAggregateFunction.Max, "MMMM")
.Where <TestEntity>(c => c.Test2 >= 123 || (c.Test4 > DateTime.Now && c.Test7 < DateTime.Now.AddDays(7)))
.GroupBy <TestEntity>(c => c.Test3)
.InnerJoin <TestEntity, TestEntity>(c => c.Test2, d => d.Test2)
.OrderBy <TestEntity>(c => c.Test6, SqlOrderType.Asc);
Assert.AreEqual(expectedCommand, actualCommand);
}
protected override void Dispose(bool disposing)
{
lock (this)
{
if (!_disposed)
{
try
{
// Release any unmanaged resources
// Release any managed resources
if (disposing)
{
if (_shouldDisposeSelectCommand)
{
if (SelectCommand != null)
{
SelectCommand.Dispose();
SelectCommand = null;
}
}
}
}
finally
{
_disposed = true;
base.Dispose(disposing);
}
}
}
}
public PersonViewModel()
{
Person = new PersonModel()
{
ForName = "",
SurName = "",
Gender = "",
Age = 0,
ID = 0
};
Persons = new ObservableCollection <PersonModel>();
InsertCommand = new InsertCommand(this);
DeleteAllCommand = new DeleteAllCommand(this);
DeleteCommand = new DeleteCommand(this);
UpdateCommand = new UpdateCommand(this);
SelectAllCommand = new SelectAllCommand(this);
SelectCommand = new SelectCommand(this);
connection = new SqlConnection("Server = .; DataBase = PersonDatabase; Trusted_Connection = true;");
connection.Open();
Select("SELECT * FROM Person;");
}
public static DataTable GetAllBIBLIOTECA_ITEMREVISTAID1(
ConnectionInfo pInfo,
string LV_ID,
decimal JRNL_REVISTA_ID
)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery += BIBLIOTECA_ITEMJRNL_REVISTAQD.GetAllBIBLIOTECA_ITEMLIVROID1;
lQuery += @" where il.JRNL_REVISTA_ID=lvr.JRNL_REVISTA_ID
and il.JRNL_REVISTA_ID=" + JRNL_REVISTA_ID + @"
and not exists(";
lQuery += BIBLIOTECA_ITEMJRNL_REVISTAQD.GetAllBIBLIOTECA_ITEMLIVROID1SUB;
lQuery += @"where ie.cod_item=il.ITEMJRNL_REVISTA_ID
and ie.ttab_id='{0}'
)";
lQuery = string.Format(lQuery, LV_ID);
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
/// <summary>
/// Initializes this instance.
/// </summary>
/// <param name="command">The selection command.</param>
public void Initialize(SelectCommand <TInstance> command)
{
this._selectCommand = command;
this.GenomeToTournamentResults = new Dictionary <ImmutableGenome, List <GenomeTournamentResult> >(new ImmutableGenome.GeneValueComparer());
// Balance selection into mini tournaments.
this._openMiniTournaments = this.CreateMiniTournamentsOutOfSelectionCommand();
}
private void PrcsException(string library, string select, Exception ex, string[] _nParametros, object[] _vParametros)
{
DataTable dtTmp = new DataTable();
dtTmp.Columns.Add("Error", typeof(string));
dtTmp.Rows.Add(ex.Message);
logException(SelectCommand.GetCommand(library, select, _nParametros, _vParametros), dtTmp);
}
public static DataTable GetRelatorioQuantitativoNpj(ConnectionInfo pInfo, string pQuery)
{
DataTable lTable = new DataTable();
SelectCommand lSelect = new SelectCommand(pQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
/// <summary>
/// 初始化Sql IN语句条件语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="columnName">字段名称</param>
/// <param name="isNotIn">是否不在范围内</param>
/// <param name="tableName">查询的表名</param>
/// <param name="action">设置选择语句的方法</param>
private SqlInsideCommandCondition(AbstractSqlCommandWithWhere baseCommand, String columnName, Boolean isNotIn, String tableName, Action<SelectCommand> action)
: base(baseCommand)
{
this._columnName = columnName;
this._isNotIn = isNotIn;
SelectCommand command = baseCommand.Database.InternalCreateSelectCommand((baseCommand.RootSource == null ? baseCommand : baseCommand.RootSource), tableName);
action(command);
this._command = command;
}
/// <summary>
/// Retorna o count, espera já uma query de count
/// </summary>
/// <param name="pConnectionString">String de conexão com o banco</param>
/// <param name="pSelectQuery">Query para rodar (ex.: SELECT Count(PRV_ID) FROM Providers)</param>
/// <returns></returns>
public static decimal Count(ConnectionInfo pInfo, string pSelectQuery)
{
//TODO: Depois vai ter que criptografar pConnectionString e pSelectQuery
SelectCommand lSelect = new SelectCommand(pSelectQuery);
object lValue = lSelect.ReturnScalar(Instance.CreateDatabase(pInfo));
return lValue.DBToDecimal();
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="columnName">字段名称</param>
/// <param name="op">条件运算符</param>
/// <param name="tableName">查询的表名</param>
/// <param name="action">设置选择语句的方法</param>
private SqlBasicCommandCondition(AbstractSqlCommandWithWhere baseCommand, String columnName, SqlOperator op, String tableName, Action<SelectCommand> action)
: base(baseCommand)
{
this._columnName = columnName;
this._operator = op;
SelectCommand command = baseCommand.Database.InternalCreateSelectCommand((baseCommand.RootSource == null ? baseCommand : baseCommand.RootSource), tableName);
action(command);
this._command = command;
}
/// <summary>
/// 初始化Sql连接语句类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="joinType">连接模式</param>
/// <param name="currentTableName">当前表格名称</param>
/// <param name="currentTableField">当前表格主键</param>
/// <param name="anotherTableName">另个表格名称</param>
/// <param name="createSelectAction">创建查询方法</param>
/// <param name="anotherTableIdentity">标识</param>
/// <param name="anotherTableField">另个表格主键</param>
internal SqlJoinTableCommand(SelectCommand cmd, SqlJoinType joinType, String currentTableName, String currentTableField, String anotherTableName, String anotherTableField, String anotherTableIdentity, Action<SelectCommand> createSelectAction)
: base(cmd, joinType, currentTableName, currentTableField, anotherTableField)
{
this._anotherTableIdentity = anotherTableIdentity;
SelectCommand anotherTableCommand = cmd.Database.InternalCreateSelectCommand((cmd.RootSource == null ? cmd : cmd.RootSource), anotherTableName);
createSelectAction(anotherTableCommand);
this._anotherTableCommand = anotherTableCommand;
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
/*
SELECT * FROM
(
SELECT *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
WHERE ROWNUM <= 30
)
WHERE RN > 20
*/
SelectCommand innestCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName);
innestCommand.QueryFields = baseCommand.QueryFields;
innestCommand.SqlJoins = baseCommand.SqlJoins;
innestCommand.SqlWhere = baseCommand.SqlWhere;
innestCommand.GroupByColumns = baseCommand.GroupByColumns;
innestCommand.SqlHaving = baseCommand.SqlHaving;
innestCommand.SqlOrders = baseCommand.SqlOrders;
SelectCommand innerCommand = new SelectCommand(baseCommand.Database, innestCommand, "");
innerCommand.SqlWhere = SqlCondition.LessThanOrEqualColumn(innerCommand, "ROWNUM", (baseCommand.RecordStart + baseCommand.PageSize).ToString());
innerCommand.InternalQuerys(baseCommand.QueryFields.ToArray());
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.GetCommandText(), true);
sb.AppendWhere(SqlCondition.GreaterThanColumn(baseCommand, "RN", baseCommand.RecordStart.ToString()));
}
else//正常模式
{
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
ISqlCondition where = baseCommand.SqlWhere;
if (baseCommand.PageSize > 0)
{
where = SqlCondition.And(baseCommand, where, SqlCondition.LessThanOrEqualColumn(baseCommand, "ROWNUM", baseCommand.PageSize.ToString()));
}
sb.AppendWhere(where);
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
return sb.ToString();
}
public static string GetLotacaoAcesso(string pSusr_id, ConnectionInfo pConnectionInfo)
{
string lQuery = "";
DataTable lTable;
lQuery = "SELECT COUNT(*) AS CONT FROM LOTACAO_USUARIO WHERE LOTUSU_SUSR = "******" AND LOTUSU_STATUS='A' ";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));
return lTable.Rows[0]["CONT"].ToString();
}
public static DataTable GetOrdemBanner(ConnectionInfo pInfo, string pOrdem)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PORT_BANNERQD.qPORT_BANNERCount;
lQuery += " WHERE BAN_REGSTATUS ='A' AND BAN_ORDEM = " + pOrdem + " ORDER BY BAN_REGDATE ";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllPORT_BANNER(ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = PORT_BANNERQD.qPORT_BANNERList;
lQuery += " WHERE BAN_REGSTATUS ='A' ORDER BY BAN_ORDEM ";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
public static DataTable GetAllSEG_MODULO(ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = SEG_MODULOQD.qSEG_MODULOList;
lQuery += " WHERE STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
private IEnumerable<ToDo> LoadToDos() {
using (new ConsistentReadScope()) {
var selectStatement = new SelectCommand(simpleDb.ItemMapping,
string.Format("select * from {0}", simpleDb.DomainName));
var results = simpleDb.Savant.SelectAttributes(selectStatement);
foreach (var propertyValues in results) {
var toDo = (ToDo)PropertyValues.CreateItem(
simpleDb.ItemMapping, typeof(ToDo), propertyValues);
Trace.TraceInformation(" Id: {0}", toDo.Id);
yield return toDo;
}
}
}
public static DataTable GetAllNPJ_ALUNOXATENDIMENTO(ConnectionInfo pInfo)
{
string lQuery = "";
DataTable lTable = new DataTable();
lQuery = NPJ_ALUNOXATENDIMENTOQD.qNPJ_ALUNOXATENDIMENTOList;
lQuery += " WHERE STATUS='A'";
SelectCommand lSelect = new SelectCommand(lQuery);
lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
return lTable;
}
