/// <summary> /// 查询公共部分 //Jermyn20131021 添加 /// </summary> /// <param name="_ht"></param> /// <returns></returns> public string SearchPublicSql(Hashtable _ht) { PublicService pService = new PublicService(); #region string sql = "Declare @TmpTable Table " + " (item_category_id nvarchar(100) " + " ,row_no int " + " ); " + " Declare @iCount int; " + " insert into @TmpTable(item_category_id,row_no) " + " select x.item_category_id ,x.rownum_ From ( select rownum_=row_number() over(order by a.item_category_code),item_category_id" + " from t_item_category a where 1=1 "; sql = pService.GetLinkSql(sql, "a.item_category_code", _ht["item_category_code"].ToString(), "%"); sql = pService.GetLinkSql(sql, "a.item_category_name", _ht["item_category_name"].ToString(), "%"); sql = pService.GetLinkSql(sql, "a.pyzjm", _ht["pyzjm"].ToString(), "%"); sql = pService.GetLinkSql(sql, "a.status", _ht["status"].ToString(), "="); sql = pService.GetLinkSql(sql, "a.customerId", this.CurrentUserInfo.CurrentUser.customer_id.ToString().Trim(), "="); if (_ht["item_category_id"] != null && _ht["item_category_id"].ToString().Length > 0) { sql += " and a.parent_id='" + _ht["item_category_id"].ToString() + "' "; } sql = sql + " ) x ;"; sql = sql + " select @iCount = COUNT(*) From @TmpTable; "; #endregion return(sql); }
/// <summary> /// 获取库存商品明细公共sql部分 /// </summary> /// <param name="_ht"></param> /// <returns></returns> public string GetStockBalancePublicString(Hashtable _ht) { PublicService pService = new PublicService(); string sql = "Declare @TmpTable Table " + " (stock_balance_id nvarchar(100) " + " ,row_no int " + " ); " + " insert into @TmpTable (stock_balance_id,row_no) " + " select x.stock_balance_id ,x.rownum_ From ( " + " select a.stock_balance_id " + " ,rownum_=row_number() over(order by a.stock_balance_id) " + " From T_Stock_Balance a " + " where 1=1 " + " and a.status = '1' "; sql = pService.GetLinkSql(sql, "a.unit_id", _ht["UnitId"].ToString(), "="); sql = pService.GetLinkSql(sql, "a.warehouse_id", _ht["WarehouseId"].ToString(), "="); sql = sql + " ) x ; " + " Declare @iCount int; " + " select @iCount = COUNT(*) From @TmpTable;"; return(sql); }
public string SearchRoleByAppSysIdPub(Hashtable _ht) { string sql = " Declare @TmpTable Table " + " (role_id nvarchar(100) " + " ,row_no int " + " ); " + " Declare @iCount int; " + " insert into @TmpTable(role_id,row_no) " + " select a.role_id " + " ,row_no=row_number() over(order by a.modify_time desc) " + " From t_role a " + " where 1=1 and a.[status] = '1' "; PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "a.def_app_id", _ht["ApplicationId"].ToString(), "%"); sql = pService.GetLinkSql(sql, "a.Customer_Id", _ht["CustomerId"].ToString(), "="); sql = pService.GetLinkSql(sql, "a.role_name", _ht["role_name"] == null ? "" : _ht["role_name"].ToString(), "%"); sql = pService.GetLinkSql(sql, "a.type_id", _ht["type_id"] == null ? "" : _ht["type_id"].ToString(), "="); sql = pService.GetLinkSql(sql, "a.type_id", _ht["type_id"] == null ? "" : _ht["type_id"].ToString(), "="); sql += " and a.role_code!='Administrator'";//不显示超级管理员 if (string.IsNullOrEmpty(_ht["UserID"].ToString())) { sql += @" and a.org_level >=( select min(z.type_level) from T_User_Role x inner join t_role y on x.role_id=y.role_id inner join t_type z on y.type_id=z.type_id where type_code!='OnlineShopping' and x.user_id='" + _ht["UserID"].ToString() + "')"; } sql = sql + " select @iCount = COUNT(*) From @TmpTable; "; return(sql); }
/// <summary> /// 获取满足查询条件的终端的记录总数 /// </summary> /// <param name="HoldType"></param> /// <param name="Type"></param> /// <param name="Code"></param> /// <param name="SN"></param> /// <param name="PurchaseDateBegin"></param> /// <param name="PurchaseDateEnd"></param> /// <param name="InsuranceDateBegin"></param> /// <param name="InsuranceDateEnd"></param> /// <returns></returns> public int SelectPosListCount(string HoldType , string Type , string Code , string SN , string PurchaseDateBegin , string PurchaseDateEnd , string InsuranceDateBegin , string InsuranceDateEnd ) { #region string sql = "select count(b.pos_id) from t_pos b, t_pos_type a where b.pos_type=a.pos_type_code "; PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "b.pos_hold_type ", HoldType, "="); sql = pService.GetLinkSql(sql, "b.pos_type ", Type, "="); sql = pService.GetLinkSql(sql, "b.pos_code ", Code, "="); sql = pService.GetLinkSql(sql, "b.pos_sn ", SN, "="); sql = pService.GetLinkSql(sql, "b.pos_purchase_date ", PurchaseDateBegin, ">="); sql = pService.GetLinkSql(sql, "b.pos_purchase_date ", PurchaseDateEnd, "<="); sql = pService.GetLinkSql(sql, "b.pos_insurance_date ", InsuranceDateBegin, ">="); sql = pService.GetLinkSql(sql, "b.pos_insurance_date ", InsuranceDateEnd, ">="); #endregion DataSet ds = new DataSet(); int i = 0; ds = this.SQLHelper.ExecuteDataset(sql); if (ds != null && ds.Tables != null && ds.Tables.Count > 0) { i = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()); } return(i); }
private string GetVipListSql(VipSearchEntity vipSearchInfo) { PublicService pService = new PublicService(); string sql = string.Empty; sql += " select a.*, DisplayIndex=row_number() over(order by a.VipName desc ) into #tmp from ( "; sql += "SELECT a.* " + " ,(SELECT x.VipSourceName FROM dbo.SysVipSource x WHERE x.VipSourceID = a.VipSourceId) VipSourceName " + " ,CASE WHEN a.Status = '1' THEN '潜在会员' ELSE '正式会员' END StatusDesc " + " ,'' LastUnit " + " ,CASE WHEN a.VipLevel = '1' THEN '基本' ELSE '高级' END VipLevelDesc " + " ,(select sum(Integral) from VipIntegralDetail where IsDelete='0' " + " and fromVipId=a.vipId and vipId='" + vipSearchInfo.HigherVipId + "') IntegralForHightUser " + " ,CASE WHEN a.Gender = '1' THEN '男' ELSE '女' END GenderInfo " + " from vip a " + " WHERE a.IsDelete = '0') a where 1=1 "; if (vipSearchInfo.VipInfo != null && !vipSearchInfo.VipInfo.Equals("")) { sql += " and (a.VipCode like '%" + vipSearchInfo.VipInfo + "%' or a.VipName like '%" + vipSearchInfo.VipInfo + "%' ) "; } sql = pService.GetLinkSql(sql, "a.Phone", vipSearchInfo.Phone, "%"); return(sql); }
private string WebGetListSql(VipSearchEntity vipSearchInfo) { PublicService pService = new PublicService(); string sql = string.Empty; //处理循环处理标签 sql = SetTagsSql(vipSearchInfo); #region sql += " select a.*,DisplayIndex=row_number() over(order by a.LastUpdateTime desc ) into #tmp from ( "; sql += "SELECT a.*, b.EventId " + " ,(SELECT x.VipSourceName FROM dbo.SysVipSource x WHERE x.VipSourceID = a.VipSourceId) VipSourceName " + " ,CASE WHEN a.Status = '1' THEN '潜在会员' ELSE '正式会员' END StatusDesc " + " ,'' LastUnit " + " ,CASE WHEN a.VipLevel = '1' THEN '基本' ELSE '高级' END VipLevelDesc " + " " + " ,(select sum(Integral) from VipIntegralDetail where IsDelete='0' " + " and fromVipId=a.vipId and vipId='" + vipSearchInfo.HigherVipId + "') IntegralForHightUser " + " ,CASE WHEN a.Gender = '1' THEN '男' ELSE '女' END GenderInfo " + " ,(SELECT AnswerID FROM MarketQuesAnswer x WHERE x.MarketEventID='4' AND QuestionID = '41' AND x.OpenID = a.WeiXinUserId) UserName " + " ,(SELECT AnswerID FROM MarketQuesAnswer x WHERE x.MarketEventID='4' AND QuestionID = '42' AND x.OpenID = a.WeiXinUserId) Enterprice " + " ,(SELECT y.OptionsId FROM MarketQuesAnswer x INNER JOIN dbo.QuesOption y " + " ON(x.AnswerID =y.OptionsID) WHERE x.MarketEventID='4' AND x.QuestionID = '43' AND x.OpenID = a.WeiXinUserId) IsChainStoresId " + " ,(SELECT y.OptionsText FROM MarketQuesAnswer x INNER JOIN dbo.QuesOption y " + " ON(x.AnswerID =y.OptionsID) WHERE x.MarketEventID='4' AND x.QuestionID = '43' AND x.OpenID = a.WeiXinUserId) IsChainStores " + " ,(SELECT y.OptionsText FROM MarketQuesAnswer x INNER JOIN dbo.QuesOption y " + " ON(x.AnswerID =y.OptionsID) WHERE x.MarketEventID='4' AND x.QuestionID = '45' AND x.OpenID = a.WeiXinUserId) IsWeiXinMarketing " + " from #vip a " + " left join MarketSignIn b on (b.openId=a.weiXinUserId and a.isDelete='0') " + " WHERE a.IsDelete = '0') a where 1=1 and ClientID = '" + this.CurrentUserInfo.CurrentUser.customer_id + "' "; if (vipSearchInfo.Gender != null && vipSearchInfo.Gender.Trim().Length > 0) { sql = pService.GetLinkSql(sql, "a.Gender ", vipSearchInfo.Gender.Trim(), "="); } if (vipSearchInfo.UserName != null && vipSearchInfo.UserName.Trim().Length > 0) { sql += " and a.VIPName like '%" + vipSearchInfo.UserName + "%' "; } if (vipSearchInfo.Enterprice != null && vipSearchInfo.Enterprice.Trim().Length > 0) { sql += " and a.Enterprice like '%" + vipSearchInfo.Enterprice + "%' "; } if (vipSearchInfo.IsChainStores != null && vipSearchInfo.IsChainStores.Trim().Length > 0) { sql += " and a.IsChainStoresId = '" + vipSearchInfo.IsChainStores + "' "; } if (vipSearchInfo.IsWeiXinMarketing != null && vipSearchInfo.IsWeiXinMarketing.Trim().Length > 0) { sql += " and a.IsWeiXinMarketing = '" + vipSearchInfo.IsWeiXinMarketing + "' "; } //if (vipSearchInfo.EventId != null && vipSearchInfo.EventId.Trim().Length > 0) //{ // sql += " and (a.EventID = '" + vipSearchInfo.EventId + "' or a.EventID = '4') "; //} //sql += " order by a.LastUpdatetime desc"; #endregion return(sql); }
/// <summary> /// 判断是否重复 /// </summary> /// <param name="barcode">条形码</param> /// <param name="sku_id">sku标识</param> /// <returns></returns> public int IsExistBarcode(string barcode, string sku_id) { int count = 0; string sql = "select isnull(count(*),0) From t_sku where 1=1 and barcode = '" + barcode + "' "; PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "sku_id", sku_id, "!="); count = Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql).ToString()); return(count); }
/// <summary> /// 查询结果 /// </summary> /// <param name="_ht"></param> /// <returns></returns> public DataSet SelectWarehouseList(Hashtable _ht) { DataSet ds = new DataSet(); string sql = "select a.* from ( " + " select rownum_=row_number() over(order by b.wh_code), " + " b.*, " + " case b.wh_status when 1 then '正常' else '停用' end as wh_status_desc, " + " case b.is_default when 1 then '是' else '否' end as is_default_desc, " + " d.unit_id, d.unit_name, d.unit_code, d.unit_name_short " + " from t_warehouse b, t_unit_warehouse c, t_unit d " + " where b.warehouse_id=c.warehouse_id and c.unit_id=d.unit_id and d.customer_id='" + this.CurrentUserInfo.CurrentUser.customer_id + "'"; PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "d.unit_name", _ht["UnitName"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_code", _ht["Code"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_name", _ht["Name"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_contacter", _ht["Contacter"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_tel", _ht["Tel"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_status", _ht["Status"].ToString(), "="); sql = sql + " ) a where rownum_ > '" + _ht["StartRow"].ToString() + "' and rownum_ <= '" + _ht["EndRow"].ToString() + "'"; ds = this.SQLHelper.ExecuteDataset(sql); return(ds); }
/// <summary> /// 判断号码是否唯一 /// </summary> /// <returns></returns> public int IsExistMenuCode(string menu_code, string menu_id) { string sql = " select count(*) From t_menu where status=1 and menu_code = '" + menu_code + "' and customer_id = '" + this.loggingSessionInfo.CurrentLoggingManager.Customer_Id.ToString() + "'"; if (menu_id != null && !menu_id.Equals("")) { PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "menu_id", menu_id, "!="); } var count = Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql)); return(count); }
/// <summary> /// 判断盘点单号码是否重复 /// </summary> /// <param name="loggingSessionInfo">登录model</param> /// <param name="order_no">订单号</param> /// <param name="order_id">订单标识</param> /// <returns></returns> public bool IsExistOrderCode(string order_no, string order_id) { try { PublicService pService = new PublicService(); string sql = "select count(*) From T_CC where 1=1 and order_no = '" + order_no + "'"; sql = pService.GetLinkSql(sql, "order_id", order_id, "!="); int n = Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql)); return(n > 0 ? false : true); } catch (Exception ex) { throw (ex); } }
/// <summary> /// 获取表单动作 /// </summary> /// <param name="billKindId"></param> /// <param name="billActionType"></param> /// <param name="con"></param> /// <returns></returns> public DataSet GetBillAction(string billKindId, BillActionType billActionType, string con) { string sql = " select a.bill_action_id Id, a.bill_kind_id KindId, a.bill_action_code Code " + " ,a.create_flag CreateFlag, a.modify_flag ModifyFlag, a.approve_flag ApproveFlag " + " , a.reject_flag RejectFlag, a.cancel_flag CancelFlag,a.bill_action_name Description,a.display_index display_index " + " from t_def_bill_action a " + "where a.bill_kind_id= '" + billKindId + "' and a.customer_id = '" + this.loggingSessionInfo.CurrentLoggingManager.Customer_Id + "'" + con; PublicService p = new PublicService(); sql = p.GetLinkSql(sql, "a.bill_action_code", billActionType.ToString(), "="); DataSet ds = new DataSet(); ds = this.SQLHelper.ExecuteDataset(sql); return(ds); }
/// <summary> /// 查询数量 /// </summary> /// <param name="_ht"></param> /// <returns></returns> public int SelectWarehouseListCount(Hashtable _ht) { PublicService pService = new PublicService(); string sql = "select count(b.warehouse_id) from t_warehouse b, t_unit_warehouse c, t_unit d " + " where b.warehouse_id=c.warehouse_id and c.unit_id=d.unit_id "; sql = pService.GetLinkSql(sql, "d.unit_name", _ht["UnitName"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_code", _ht["Code"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_name", _ht["Name"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_contacter", _ht["Contacter"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_tel", _ht["Tel"].ToString(), "%"); sql = pService.GetLinkSql(sql, "b.wh_status", _ht["Status"].ToString(), "="); return(Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql))); }
/// <summary> /// 获取满足查询条件的终端的某页上的所有终端 /// </summary> /// <param name="HoldType"></param> /// <param name="Type"></param> /// <param name="Code"></param> /// <param name="SN"></param> /// <param name="PurchaseDateBegin"></param> /// <param name="PurchaseDateEnd"></param> /// <param name="InsuranceDateBegin"></param> /// <param name="InsuranceDateEnd"></param> /// <param name="StartRow"></param> /// <param name="EndRow"></param> /// <returns></returns> public DataSet SelectPosList(string HoldType , string Type , string Code , string SN , string PurchaseDateBegin , string PurchaseDateEnd , string InsuranceDateBegin , string InsuranceDateEnd , int StartRow , int EndRow ) { #region string sql = "select a.* from ( " + " select rownum_=row_number() over(order by b.pos_hold_type,b.pos_type,b.pos_code), " + " b.*, " + " case b.pos_hold_type when '1' then '租赁' when '2' then '自有' else b.pos_hold_type end as pos_hold_type_desc, " + " a.pos_type_name as pos_type_desc " + " from t_pos b, t_pos_type a " + " where b.pos_type=a.pos_type_code "; PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "b.pos_hold_type ", HoldType, "="); sql = pService.GetLinkSql(sql, "b.pos_type ", Type, "="); sql = pService.GetLinkSql(sql, "b.pos_code ", Code, "="); sql = pService.GetLinkSql(sql, "b.pos_sn ", SN, "="); sql = pService.GetLinkSql(sql, "b.pos_purchase_date ", PurchaseDateBegin, ">="); sql = pService.GetLinkSql(sql, "b.pos_purchase_date ", PurchaseDateEnd, "<="); sql = pService.GetLinkSql(sql, "b.pos_insurance_date ", InsuranceDateBegin, ">="); sql = pService.GetLinkSql(sql, "b.pos_insurance_date ", InsuranceDateEnd, ">="); sql = sql + " ) a where rownum_ > '" + StartRow + "' and rownum_ <= '" + EndRow + "' "; #endregion DataSet ds = new DataSet(); ds = this.SQLHelper.ExecuteDataset(sql); return(ds); }
/// <summary> /// 判断调价单号码是否重复 /// </summary> /// <param name="order_no">订单号码</param> /// <param name="order_id">订单标识</param> /// <param name="pTran">是否事物</param> /// <returns></returns> public bool IsExistOrderCode(string order_no, string order_id, IDbTransaction pTran) { try { string sql = "select isnull(count(*),0) From T_Order where 1=1 and order_no = '" + order_no + "' "; PublicService pService = new PublicService(); sql = pService.GetLinkSql(sql, "order_id", order_id, "!="); int n = 0; if (pTran != null) { n = Convert.ToInt32(this.SQLHelper.ExecuteScalar((SqlTransaction)pTran, CommandType.Text, sql, null)); } else { n = Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql)); } return(n > 0 ? false : true); } catch (Exception ex) { throw (ex); } }
/// <summary> /// 获取CC库单据查询脚本公共部分 /// </summary> /// <param name="orderSearchInfo"></param> /// <returns></returns> private string GetSearchPublicSql(OrderSearchInfo orderSearchInfo) { PublicService pService = new PublicService(); string sql = "Declare @TmpTable Table " + " (order_id nvarchar(100) " + " ,row_no int " + " ); " + " insert into @TmpTable (order_id,row_no) " + " select x.order_id ,x.rownum_ From ( " + " select " + " rownum_=row_number() over(order by a.order_date desc,a.order_no desc) " + " ,order_id " + " from t_cc a where 1=1 and a.status != '-1'"; sql = pService.GetLinkSql(sql, "a.order_id", orderSearchInfo.order_id, "%"); sql = pService.GetLinkSql(sql, "a.customer_id", orderSearchInfo.customer_id, "%"); sql = pService.GetLinkSql(sql, "a.order_no", orderSearchInfo.order_no, "%"); sql = pService.GetLinkSql(sql, "a.order_type_id", orderSearchInfo.order_type_id, "%"); sql = pService.GetLinkSql(sql, "a.order_reason_id", orderSearchInfo.order_reason_id, "%"); sql = pService.GetLinkSql(sql, "a.unit_id", orderSearchInfo.unit_id, "="); sql = pService.GetLinkSql(sql, "a.order_date", orderSearchInfo.order_date_begin, ">="); sql = pService.GetLinkSql(sql, "a.order_date", orderSearchInfo.order_date_end, "<="); sql = pService.GetLinkSql(sql, "a.complete_date", orderSearchInfo.complete_date_begin, ">="); sql = pService.GetLinkSql(sql, "a.complete_date", orderSearchInfo.complete_date_end, "<="); sql = pService.GetLinkSql(sql, "a.status", orderSearchInfo.status, "="); sql = pService.GetLinkSql(sql, "a.warehouse_id", orderSearchInfo.warehouse_id, "="); sql = sql + " ) x ; "; sql = sql + " Declare @iCount int;"; sql = sql + " select @iCount = COUNT(*) From @TmpTable;"; return(sql); }
/// <summary> /// 查询公共部分 /// </summary> /// <param name="orderSearchInfo"></param> /// <returns></returns> public string GetSearchPublicSql(OrderSearchInfo orderSearchInfo) { #region PublicService pService = new PublicService(); string sql = "Declare @TmpTable Table " + " (order_id nvarchar(100) " + " ,row_no int " + " ); " + " insert into @TmpTable (order_id,row_no) " + " select x.order_id ,x.rownum_ From ( " + " select " + " rownum_=row_number() over(order by a.order_date desc,a.order_no desc) " + " ,order_id " + " from t_order a where 1=1 "; sql = pService.GetLinkSql(sql, "a.order_id", orderSearchInfo.order_id, "%"); sql = pService.GetLinkSql(sql, "a.customer_id", orderSearchInfo.customer_id, "%"); sql = pService.GetLinkSql(sql, "a.order_no", orderSearchInfo.order_no, "%"); sql = pService.GetLinkSql(sql, "a.order_type_id", orderSearchInfo.order_type_id, "%"); sql = pService.GetLinkSql(sql, "a.order_reason_type_id", orderSearchInfo.order_reason_id, "%"); sql = pService.GetLinkSql(sql, "a.unit_id", orderSearchInfo.unit_id, "="); sql = pService.GetLinkSql(sql, "a.red_flag", orderSearchInfo.red_flag, "="); sql = pService.GetLinkSql(sql, "a.order_date", orderSearchInfo.order_date_begin, ">="); sql = pService.GetLinkSql(sql, "a.order_date", orderSearchInfo.order_date_end, "<="); sql = pService.GetLinkSql(sql, "a.request_date", orderSearchInfo.request_date_begin, ">="); sql = pService.GetLinkSql(sql, "a.request_date", orderSearchInfo.request_date_end, "<="); //sql = pService.GetLinkSql(sql, "a.order_status", orderSearchInfo.status, "="); if (orderSearchInfo.status != null && orderSearchInfo.status.Length > 0) { sql += string.Format("a.order_status in ({0})", orderSearchInfo.status.Aggregate("", (i, j) => i + string.Format("'{0}',", j)).Trim(',')); } sql = pService.GetLinkSql(sql, "a.warehouse_id", orderSearchInfo.warehouse_id, "="); sql = pService.GetLinkSql(sql, "a.ref_order_no", orderSearchInfo.ref_order_no, "%"); sql = pService.GetLinkSql(sql, "a.data_from_id", orderSearchInfo.data_from_id, "="); sql = pService.GetLinkSql(sql, "a.sales_unit_id", orderSearchInfo.sales_unit_id, "="); sql = pService.GetLinkSql(sql, "a.purchase_unit_id", orderSearchInfo.purchase_unit_id, "="); if (orderSearchInfo.item_name != null && !orderSearchInfo.item_name.Equals("")) { sql = sql + " and a.order_id in (select distinct x.order_id From t_order_detail x " + " inner join T_Sku y" + " on(x.sku_id = y.sku_id)" + " inner join T_Item z" + " on(y.item_id = z.item_id) "; if (orderSearchInfo.item_name != null && !orderSearchInfo.item_name.Equals("")) { sql = sql + " z.item_name like '%' + '" + orderSearchInfo.item_name + "' + '%' "; sql = sql + " or z.item_code like '%' + '" + orderSearchInfo.item_name + "' + '%' "; } sql = sql + " ) "; } sql = sql + " ) x ; "; sql = sql + " Declare @iCount int;"; sql = sql + " select @iCount = COUNT(*) From @TmpTable;"; #endregion return(sql); }