public WhereBuilder ParseSQL()
{
string sql = "Select mtb.*,tmptb1." + MstProvinceTable.C_PROVINCE_NAME_CN + " From "
+ MstCityTable.C_TableName + " mtb ";
sql += " Left Join (Select " + MstProvinceTable.C_PROVINCE_ID + "," + MstProvinceTable.C_PROVINCE_NAME_CN + " From " + MstProvinceTable.C_TableName + ") tmptb1 on tmptb1." + MstProvinceTable.C_PROVINCE_ID + "=mtb." + MstCityTable.C_PROVINCE_ID + " ";
WhereBuilder wb = new WhereBuilder(sql);
wb.FixFirstCondition = true;
if (!string.IsNullOrEmpty(CityNameCN))
{
wb.AddAndCondition("mtb", MstCityTable.C_CITY_NAME_CN, SQLOperator.Like, CityNameCN);
}
if (ContainsKey(MstCityTable.C_CITY_NAME_EN) && !string.IsNullOrEmpty(CityNameEn))
{
wb.AddAndCondition("mtb", MstCityTable.C_CITY_NAME_EN, SQLOperator.Like, CityNameEn);
}
if (!string.IsNullOrEmpty(CityShortName))
{
wb.AddAndCondition("mtb", MstCityTable.C_SHORT_NAME, SQLOperator.Like, CityShortName);
}
if (!string.IsNullOrEmpty(CityProvinceID))
{
wb.AddAndCondition("mtb", MstCityTable.C_PROVINCE_ID, SQLOperator.Equal, CityProvinceID);
}
if (!string.IsNullOrEmpty(CityType))
{
wb.AddAndCondition("mtb", MstCityTable.C_CITY_TYPE, SQLOperator.Equal, CityType);
}
//wb.AddCondition(" Order By " + MstCityTable.C_CITY_NAME_EN + " ");
return wb;
}
//For test
public WhereBuilder ParseSQL()
{
StringBuilder sb = new StringBuilder();
sb.Append("Select * from ").Append(LogErrorTable.C_TableName).Append(" e ");
WhereBuilder wb = new WhereBuilder(sb.ToString());
wb.FixFirstCondition = true;
wb.AddAndCondition("1", "1");
if (ContainsKey(LogErrorTable.C_IP) && !string.IsNullOrEmpty(Ip))
{
wb.AddAndCondition("e", LogErrorTable.C_IP, SQLOperator.Like, Ip);
}
if (ContainsKey(LogErrorTable.C_LOG_ERROR_CATEGORY) && !string.IsNullOrEmpty(LogErrorCategory))
{
wb.AddAndCondition("e", LogErrorTable.C_LOG_ERROR_CATEGORY, SQLOperator.Like, LogErrorCategory);
}
if (ContainsKey(LogErrorTable.C_MACHINE_NAME) && !string.IsNullOrEmpty(MachineName))
{
wb.AddAndCondition("e", LogErrorTable.C_MACHINE_NAME, SQLOperator.Like, MachineName);
}
//if (DateFrom != null)
//{
// wb.AddAndCondition("e", LogErrorTable.C_LOG_TIME, SQLOperator.GreaterEquals, DateFrom);
//}
//if (DateTo != null)
//{
// wb.AddAndCondition("e", LogErrorTable.C_LOG_TIME, SQLOperator.LessEquals, DateTo);
//}
////根据LogTime来排序
string sqlExtends = " order by e." + LogErrorTable.C_LOG_TIME + " desc";
wb.AddCondition(sqlExtends);
return wb;
}
public WhereBuilder ParseSQL()
{
StringBuilder sb = new StringBuilder();
sb.Append("Select p.*,c.").Append(SecPageTable.C_PAGE_NAME)
.Append(" from ").Append(SecPermissionTable.C_TableName).Append(" p ")
.Append(" inner join ").Append(SecPageTable.C_TableName).Append(" c on p.")
.Append(SecPermissionTable.C_PAGE_ID).Append("=c.").Append(SecPageTable.C_PAGE_ID);
WhereBuilder wb = new WhereBuilder(sb.ToString());
wb.FixFirstCondition = true;
if (ContainsKey(SecPermissionTable.C_CONTROL_ID) && !string.IsNullOrEmpty(ControlId))
{
wb.AddAndCondition("p", SecPermissionTable.C_CONTROL_ID, SQLOperator.Like, ControlId);
}
if (ContainsKey(SecPermissionTable.C_PERMISSION_NAME) && !string.IsNullOrEmpty(PermissionName))
{
wb.AddAndCondition("p", SecPermissionTable.C_PERMISSION_NAME, SQLOperator.Like, PermissionName);
}
if (ContainsKey(SecPermissionTable.C_PAGE_ID) && !string.IsNullOrEmpty(PageId))
{
wb.AddAndCondition("p", SecPermissionTable.C_PAGE_ID, SQLOperator.Equal, PageId);
}
return wb;
}
public WhereBuilder ParseSQL()
{
StringBuilder sb = new StringBuilder();
sb.Append("Select * from ").Append(LogOperateTable.C_TableName).Append(" o ");
WhereBuilder wb = new WhereBuilder(sb.ToString());
wb.FixFirstCondition = true;
wb.AddAndCondition("1", "1");
if (ContainsKey(LogOperateTable.C_OPERATE_TYPE) && !string.IsNullOrEmpty(OperateType))
{
wb.AddAndCondition("o", LogOperateTable.C_OPERATE_TYPE, SQLOperator.Equal, OperateType);
}
if (ContainsKey(LogOperateTable.C_PAGE_NAME) && !string.IsNullOrEmpty(PageName))
{
wb.AddAndCondition("o", LogOperateTable.C_PAGE_NAME, SQLOperator.Like, PageName);
}
if (DateFrom != null)
{
wb.AddAndCondition("o", LogOperateTable.C_OPERATE_TIME, SQLOperator.GreaterEquals, DateFrom);
}
if (DateTo != null)
{
wb.AddAndCondition("o", LogOperateTable.C_OPERATE_TIME, SQLOperator.LessEquals, DateTo);
}
////根据OperateTime来排序
string sqlExtends = " order by o." + LogOperateTable.C_OPERATE_TIME + " desc";
wb.AddCondition(sqlExtends);
return wb;
}
public DataTable Query()
{
string strSql = @"Select * From V_PriceRecord";
WhereBuilder wb = new WhereBuilder(strSql);
wb.AddAndCondition("1", "1");
return DataAccess.SelectDataSet(wb).Tables[0];
}
public WhereBuilder ParseSQL()
{
WhereBuilder wb;
string sql = " select user_account from SEC_USER A " +
" left join SEC_USER_ROLE B on A.USER_ID=B.USER_ID "+
" where B.ROLE_ID in (select ROLE_ID from MST_CATEGORY C "+
" left join MST_BRAND D on D.CATEGORY_ID=C.CATEGORY_ID "+
" left join MST_ROLE_BRAND E on E.BRAND_ID=D.BRAND_ID where C.CATEGORY_ID='" +CategoryId.Trim()+"' and D.BRAND_ID='"+BrandId.Trim()+"'"+
")";
wb = new WhereBuilder(sql);
return wb;
}
public WhereBuilder ParseSQL()
{
WhereBuilder wb;
string sql = @" select sum(a.total_days) totalday,sum(a.use_days) useday,sum(a.space_days) spaceday
from atd_vaca_result a
where a.person_id='" + PersonId + @"'
and sysdate between to_date(a.use_date,'yyyy-mm-dd') and to_date(a.disable_date,'yyyy-mm-dd')
and (a.belong_year=to_char(sysdate,'yyyy') and to_number(a.belong_month)<=to_number(to_char(sysdate,'mm'))) ";
sql += " order by a.disable_date";
wb = new WhereBuilder(sql);
return wb;
}
/// <summary>
/// 返回WhereBuilder，注意排序的用法
/// </summary>
/// <returns></returns>
public WhereBuilder ParseSQL()
{
string sql = @"select sum(quantity) as qty,sum(TotalValueUSD),action_no as usd from Fake_Product e";
WhereBuilder wb = new WhereBuilder(sql);
// wb.FixFirstCondition = true;
wb.AddCondition(" Where 1>0 ");
if (!string.IsNullOrEmpty(Action_no))
{
wb.AddAndCondition("e", FakeProductTable.C_Action_NO, SQLOperator.Equal, Action_no);
}
wb.AddCondition(" group by action_no ");
return wb;
}
public virtual DataSet Search(string brand_ID, string brandType_ID, string productSize_ID, string subCatrgory_ID)
{
string strSql = @"Select * From CaseCalculation_View";
WhereBuilder wb = new WhereBuilder(strSql);
wb.AddAndCondition("1", "1");
if (!string.IsNullOrEmpty(brand_ID))
wb.AddAndCondition(CasecalculationTable.C_Brand_ID, brand_ID);
if (!string.IsNullOrEmpty(brandType_ID))
wb.AddAndCondition(CasecalculationTable.C_BrandType_ID, brandType_ID);
if (!string.IsNullOrEmpty(productSize_ID))
wb.AddAndCondition(CasecalculationTable.C_Product_Size_ID, productSize_ID);
if (!string.IsNullOrEmpty(subCatrgory_ID))
wb.AddAndCondition(CasecalculationTable.C_SubCategory_ID, subCatrgory_ID);
return DataAccess.SelectDataSet(wb);
}
/// <summary>
/// 根据roleType查出List<SecRoleEntity>, Add by Alex on 2010.3.20
/// </summary>
/// <param name="roleType"></param>
/// <returns></returns>
public static List<SecRoleEntity> GetRoleEntityByRoleType(string roleType)
{
List<SecRoleEntity> roleEntityList = new List<SecRoleEntity>();
string sql = "select * from " + SecRoleTable.C_TableName;
WhereBuilder wb = new WhereBuilder(sql);
wb.FixFirstCondition = true;
wb.AddAndCondition("1", "1");
wb.AddAndCondition(SecRoleTable.C_TableName, SecRoleTable.C_ROLE_TYPE, SQLOperator.Equal, roleType);
//角色种类，1为业务角色
wb.AddAndCondition(SecRoleTable.C_TableName, SecRoleTable.C_ROLE_CATEGORY, SQLOperator.Equal, 1);
//根据RoleName来排序
string sqlExtends = " order by " + SecRoleTable.C_TableName + "." + SecRoleTable.C_ROLE_NAME;
wb.AddCondition(sqlExtends);
roleEntityList = DataAccess.Select<SecRoleEntity>(wb);
return roleEntityList;
}
/// <summary>
/// 返回WhereBuilder，注意排序的用法
/// </summary>
/// <returns></returns>
public WhereBuilder ParseSQL()
{
string sql = @"select a.no ,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=33) as KYQD,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=34) as XDBG,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=35) as XHZM,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=36) as CFJDS,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=37) as XRZL,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=38) as JDBG,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=39) as XSAJZJBG,
(select count(*) from UploadFile b where b.action_no=a.no and b.category_id=40) as XSPJS
from dbo.Normal_Raid a ";
WhereBuilder wb = new WhereBuilder(sql);
// wb.FixFirstCondition = true;
wb.AddCondition(" Where 1>0 " + " and a.I_or_Raid<>'Investigation' "); //2012-7-18 jane modified 在upload report中，不应该出现investigation的数据
if (!string.IsNullOrEmpty(Action_no))
{
wb.AddAndCondition("a", NormalRaidTable.C_NO, SQLOperator.Like, Action_no);
}
if (!string.IsNullOrEmpty(Agency_ID))
{
wb.AddAndCondition("a", NormalRaidTable.C_Agency_ID, SQLOperator.Equal, Agency_ID);
}
wb.AddCondition(" Order by " + NormalRaidTable.C_Action_Date + " desc");
return wb;
}
public DataSet FillSQLDataSet(WhereBuilder wb)
{
return FillSQLDataSet(wb.SQLString, wb.Parameters);
}
public IDataReader ExecuteReader(WhereBuilder wb)
{
return ExecuteReader(wb.SQLString, wb.Parameters, CommandType.Text);
}
public object ExecuteScalar(WhereBuilder wb)
{
return ExecuteScalar(wb.SQLString, wb.Parameters, CommandType.Text);
}
public int ExecuteNonQuery(WhereBuilder wb)
{
return ExecuteNonQuery(wb.SQLString, wb.Parameters, CommandType.Text);
}
/// <summary>
/// 返回WhereBuilder，注意排序的用法
/// </summary>
/// <returns></returns>
public WhereBuilder ParseSQL()
{
string sql = @"select * from dbo.MainPage_View e";
WhereBuilder wb = new WhereBuilder(sql);
// wb.FixFirstCondition = true;
wb.AddCondition(" Where 1>0 ");
if (!string.IsNullOrEmpty(Action_no))
{
wb.AddAndCondition("e", NormalRaidTable.C_NO, SQLOperator.Like, Action_no);
}
if (!string.IsNullOrEmpty(Begindate))
{
wb.AddCondition(" and e." + NormalRaidTable.C_Action_Date + " >='" + Begindate + "'");
}
if (!string.IsNullOrEmpty(Enddate))
{
wb.AddCondition(" and e." + NormalRaidTable.C_Action_Date + " <='" + Enddate + "'");
}
if (!string.IsNullOrEmpty(Province))
{
wb.AddAndCondition("e", NormalRaidTable.C_Province, SQLOperator.Equal, Province);
}
if (!string.IsNullOrEmpty(City))
{
wb.AddAndCondition("e", NormalRaidTable.C_City, SQLOperator.Equal, City);
}
if (!string.IsNullOrEmpty(Status))
{
wb.AddAndCondition("e", "actionstatus", SQLOperator.Equal, Status);
}
if (!string.IsNullOrEmpty(Type))
{
wb.AddAndCondition("e", "type", SQLOperator.Equal, Type);
}
if (!string.IsNullOrEmpty(Agency))
{
wb.AddAndCondition("e", NormalRaidTable.C_Agency_ID, SQLOperator.Equal, Agency);
}
if (!string.IsNullOrEmpty(User))
{
wb.AddAndCondition("e", NormalRaidTable.C_User_ID, SQLOperator.Equal, User);
}
if (!string.IsNullOrEmpty(RequestStatus))
{
if (RequestStatus.Equals("Applying"))
{
wb.AddCondition(" and (e." + NormalRaidTable.C_EditRecordID + " is not null and e.EditRecordID <>'') ");
}
else
{
string sql2 = "";
string s = "";
if (!string.IsNullOrEmpty(RequestEditRecordBLL.CurrentUser.SecUserEntity.AgencyId))
{
s = string.Format(" and rer.RequestAgencyID='{0}' ", RequestEditRecordBLL.CurrentUser.SecUserEntity.AgencyId);
}
if (RequestStatus.Equals("Approved"))
{
sql2 = @" and exists (
select s.IsApproved from
RequestEditRecord s
inner join
(
select rer.[type],rer.Action_NO,rer.RequestAgencyID,MAX(rer.requesttime) as requesttime
from RequestEditRecord rer where e.[type]=rer.[Type] and e.id=rer.Action_NO
{0}
--and rer.RequestAgencyID =''
group by rer.[type],rer.Action_NO,rer.RequestAgencyID
) As A on s.Action_NO =a.Action_NO and s.RequestAgencyID = a.RequestAgencyID and s.[type]=a.[Type]
and s.RequestTime=a.requesttime
where s.IsApproved=1 )";
}
if (RequestStatus.Equals("Rejected"))
{
sql2 = @" and exists (
select s.IsApproved from
RequestEditRecord s
inner join
(
select rer.[type],rer.Action_NO,rer.RequestAgencyID,MAX(rer.requesttime) as requesttime
from RequestEditRecord rer where e.[type]=rer.[Type] and e.id=rer.Action_NO
{0}
--and rer.RequestAgencyID =''
group by rer.[type],rer.Action_NO,rer.RequestAgencyID
) As A on s.Action_NO =a.Action_NO and s.RequestAgencyID = a.RequestAgencyID and s.[type]=a.[Type]
and s.RequestTime=a.requesttime
where s.IsApproved=0 )";
}
wb.AddCondition(string.Format(sql2, s));
}
}
wb.AddCondition(" Order by e." + NormalRaidTable.C_EditRecordID + " desc,e." + NormalRaidTable.C_Action_Date + " Desc");
return wb;
}
/// <summary>
/// 返回WhereBuilder，注意排序的用法
/// </summary>
/// <returns></returns>
public WhereBuilder ParseSQL()
{
string sql = @" select e.*
from (
select a.* ,b.province,b.city,b.agency,b.agency_id,null as Defendant,b.User_ID,b.User_Name
from UploadFile a
left join Normal_Raid b on a.action_no=b.no and a.target='Upload_File_For_Raid'
where a.target='Upload_File_For_Raid'
union all
select a.* ,b.province,b.city,d.agency,d.agency_id,b.defendant,d.User_ID,d.User_Name
from UploadFile a
left join Criminal_Detail b on a.action_no=b.action_no and a.target='Upload_File_For_Criminal'
left join Criminal d on b.action_no=d.action_no
where a.target='Upload_File_For_Criminal') e";
WhereBuilder wb = new WhereBuilder(sql);
// wb.FixFirstCondition = true;
wb.AddCondition(" Where 1>0 ");
if (!string.IsNullOrEmpty(Action_no))
{
wb.AddAndCondition("e", CriminalDetailTable.C_Action_NO, SQLOperator.Like, Action_no);
}
if (!string.IsNullOrEmpty(Begindate))
{
wb.AddCondition(" and e." + UploadfileTable.C_Upload_Date+" >='"+Begindate+"'");
}
if (!string.IsNullOrEmpty(Enddate))
{
wb.AddCondition(" and e." + UploadfileTable.C_Upload_Date + " <='" + Enddate + "'");
}
if (!string.IsNullOrEmpty(Province))
{
wb.AddAndCondition("e", NormalRaidTable.C_Province, SQLOperator.Equal, Province);
}
if (!string.IsNullOrEmpty(City))
{
wb.AddAndCondition("e", NormalRaidTable.C_City, SQLOperator.Equal, City);
}
if (!string.IsNullOrEmpty(Agency))
{
wb.AddAndCondition("e", NormalRaidTable.C_Agency_ID, SQLOperator.Equal, Agency);
}
if (!string.IsNullOrEmpty(Type))
{
wb.AddAndCondition("e", UploadfileTable.C_Category_ID, SQLOperator.Equal, Type);
}
if (!string.IsNullOrEmpty(Defendant))
{
wb.AddAndCondition("e", CriminalDetailTable.C_Defendant, SQLOperator.Like, Defendant);
}
if (!string.IsNullOrEmpty(User))
{
wb.AddAndCondition("e", NormalRaidTable.C_User_ID, SQLOperator.Equal, User);
}
wb.AddCondition(" Order by e." + UploadfileTable.C_Upload_Date);
return wb;
}