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);
}
/// <summary>
/// 更新出入库表主信息
/// </summary>
/// <param name="inoutInfo"></param>
/// <param name="pTran"></param>
private bool UpdateMenu(MenuModel userInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_menu set menu_code = '" + userInfo.Menu_Code + "' ";
sql = pService.GetIsNotNullUpdateSql(sql, "reg_app_id", userInfo.Reg_App_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "parent_menu_id", userInfo.Parent_Menu_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "menu_level", userInfo.Menu_Level.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "url_path", userInfo.Url_Path);
sql = pService.GetIsNotNullUpdateSql(sql, "icon_path", userInfo.Icon_Path);
sql = pService.GetIsNotNullUpdateSql(sql, "display_index", userInfo.Display_Index.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "menu_name", userInfo.Menu_Name);
sql = pService.GetIsNotNullUpdateSql(sql, "user_flag", userInfo.User_Flag.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "menu_eng_name", userInfo.Menu_Eng_Name);
sql = pService.GetIsNotNullUpdateSql(sql, "status", userInfo.Status.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", userInfo.Create_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sql = sql + " where menu_id = '" + userInfo.Menu_Id + "' ;";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <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);
}
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="roleInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetRoleMenuUpdate(RoleModel roleInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update T_Role_Menu set status = '1'";
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", roleInfo.Create_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", roleInfo.Create_Time);
sql = sql + " From ( ";
int i = 0;
foreach (RoleMenuModel roleMenuRole in roleInfo.RoleMenuInfoList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + roleInfo.Role_Id + "' role_id "
+ ",'" + roleMenuRole.Menu_Id + "' Menu_Id ";
i++;
}
sql = sql + " ) a where ( T_Role_Menu.role_id = a.role_id and T_Role_Menu.menu_id = a.menu_id)";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 修改商品属性
/// </summary>
/// <param name="itemInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetItemPropUpdate(ItemInfo itemInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_item_property set status = '1' ,prop_value = a.prop_value";
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", itemInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", itemInfo.Modify_Time);
sql = sql + " From ( ";
int i = 0;
foreach (ItemPropInfo itemPropInfo in itemInfo.ItemPropList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + itemInfo.Item_Id + "' item_id "
+ ",'" + itemPropInfo.PropertyCodeId + "' prop_id "
+ ",'" + itemPropInfo.Item_Property_Id + "' item_property_id "
+ ",'" + itemPropInfo.PropertyCodeValue + "' prop_value ";
i++;
}
sql = sql + " ) a where ( t_item_property.item_id = a.item_id and t_item_property.prop_id = a.prop_id ) or (t_item_property.item_property_id = a.item_property_id)";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 更新角色
/// </summary>
/// <param name="roleInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetRoleUpdate(RoleModel roleInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_role set def_app_id = '" + roleInfo.Def_App_Id + "' ";
sql = pService.GetIsNotNullUpdateSql(sql, "Role_Code", roleInfo.Role_Code);
sql = pService.GetIsNotNullUpdateSql(sql, "Role_Name", roleInfo.Role_Name);
sql = pService.GetIsNotNullUpdateSql(sql, "Role_Eng_Name", roleInfo.Role_Eng_Name);
sql = pService.GetIsNotNullUpdateSql(sql, "Is_Sys", roleInfo.Is_Sys.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "Modify_Time", roleInfo.Modify_Time);
sql = pService.GetIsNotNullUpdateSql(sql, "Modify_User_id", roleInfo.Modify_User_id);
sql = pService.GetIsNotNullUpdateSql(sql, "type_id", roleInfo.type_id);
sql = pService.GetIsNotNullUpdateSql(sql, "org_level", roleInfo.org_level.ToString());
sql = sql + " where role_id = '" + roleInfo.Role_Id + "' ;";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 修改品牌信息
/// </summary>
/// <param name="unitInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetUnitBrandUpdate(UnitInfo unitInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update StoreBrandMapping set IsDelete = '0' ";
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateBy", unitInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateTime", unitInfo.Modify_Time);
sql = sql + " From ( ";
int i = 0;
foreach (var itemBrandInfo in unitInfo.ItemBrandList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + unitInfo.Id + "' StoreId "
+ ",'" + itemBrandInfo.MappingId + "' MappingId "
+ ",'" + itemBrandInfo.BrandId + "' BrandId ";
i++;
}
sql = sql + " ) a, StoreBrandMapping x where ( x.BrandId = a.BrandId and x.StoreId = a.StoreId) or (x.BrandId = a.BrandId)";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <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>
/// 获取库存商品明细公共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);
}
/// <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);
}
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="orderInfo"></param>
/// <param name="pTran"></param>
private bool UpdateOrder(OrderInfo orderInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_order set order_no = '" + orderInfo.order_no + "',if_flag = '" + orderInfo.if_flag + "' ";
sql = pService.GetIsNotNullUpdateSql(sql, "order_type_id", orderInfo.order_type_id);
sql = pService.GetIsNotNullUpdateSql(sql, "order_reason_type_id", orderInfo.order_reason_type_id);
sql = pService.GetIsNotNullUpdateSql(sql, "red_flag", orderInfo.red_flag);
sql = pService.GetIsNotNullUpdateSql(sql, "ref_order_id", orderInfo.ref_order_id);
sql = pService.GetIsNotNullUpdateSql(sql, "ref_order_no", orderInfo.ref_order_no);
sql = pService.GetIsNotNullUpdateSql(sql, "warehouse_id", orderInfo.warehouse_id);
sql = pService.GetIsNotNullUpdateSql(sql, "order_date", orderInfo.order_date);
sql = pService.GetIsNotNullUpdateSql(sql, "request_date", orderInfo.request_date);
sql = pService.GetIsNotNullUpdateSql(sql, "complete_date", orderInfo.complete_date);
sql = pService.GetIsNotNullUpdateSql(sql, "promise_date", orderInfo.promise_date);
sql = pService.GetIsNotNullUpdateSql(sql, "create_unit_id", orderInfo.create_unit_id);
sql = pService.GetIsNotNullUpdateSql(sql, "unit_id", orderInfo.unit_id);
sql = pService.GetIsNotNullUpdateSql(sql, "pos_id", orderInfo.pos_id);
sql = pService.GetIsNotNullUpdateSql(sql, "total_amount", orderInfo.total_amount.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "total_qty", orderInfo.total_qty.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "discount_rate", orderInfo.discount_rate.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "actual_amount", orderInfo.actual_amount.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "receive_points", orderInfo.receive_points.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "pay_points", orderInfo.pay_points.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "print_times", orderInfo.print_times.ToString());
sql = pService.GetIsNotNullUpdateSql(sql, "carrier_id", orderInfo.carrier_id);
sql = pService.GetIsNotNullUpdateSql(sql, "remark", orderInfo.remark);
sql = pService.GetIsNotNullUpdateSql(sql, "order_status", orderInfo.order_status);
sql = pService.GetIsNotNullUpdateSql(sql, "order_status_desc", orderInfo.order_status_desc);
sql = pService.GetIsNotNullUpdateSql(sql, "approve_time", orderInfo.approve_time);
sql = pService.GetIsNotNullUpdateSql(sql, "approve_user_id", orderInfo.approve_user_id);
sql = pService.GetIsNotNullUpdateSql(sql, "send_user_id", orderInfo.send_user_id);
sql = pService.GetIsNotNullUpdateSql(sql, "send_time", orderInfo.send_time);
sql = pService.GetIsNotNullUpdateSql(sql, "accpect_time", orderInfo.accpect_time);
sql = pService.GetIsNotNullUpdateSql(sql, "accpect_user_id", orderInfo.accpect_user_id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", orderInfo.create_time);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", orderInfo.create_user_id);
sql = pService.GetIsNotNullUpdateSql(sql, "sales_unit_id", orderInfo.sales_unit_id);
sql = pService.GetIsNotNullUpdateSql(sql, "purchase_unit_id", orderInfo.purchase_unit_id);
sql = pService.GetIsNotNullUpdateSql(sql, "customer_id", this.loggingSessionInfo.CurrentLoggingManager.Customer_Id);
sql = sql + " where order_id = '" + orderInfo.order_id + "' ;";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <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>
/// <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="_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="ccInfo"></param>
/// <returns></returns>
public bool SetCCStatusUpdate(CCInfo ccInfo)
{
string sql = "update T_CC set [status] = '" + ccInfo.status + "' ,status_desc = '" + ccInfo.status_desc + "'"
+ " ,Modify_Time = '" + ccInfo.modify_time + "' ,Modify_User_Id = '" + ccInfo.modify_user_id + "' ";
PublicService pService = new PublicService();
sql = pService.GetIsNotNullUpdateSql(sql, "approve_user_id", ccInfo.approve_user_id);
sql = pService.GetIsNotNullUpdateSql(sql, "approve_time", ccInfo.approve_time);
sql = sql + " ,if_flag = '0' where order_id = '" + ccInfo.order_id + "'";
this.SQLHelper.ExecuteNonQuery(sql);
return(true);
}
/// <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);
}
public bool updateValue(string item_property_id, string prop_value, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_item_property set prop_value = '" + prop_value + "' where item_property_id='" + item_property_id + "'";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 修改图片信息
/// </summary>
/// <param name="itemInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetItemImageUpdate(ItemInfo itemInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update ObjectImages set IsDelete = '0' ,ImageURL = a.ImageURL, DisplayIndex = a.DisplayIndex, Title = a.Title, Description = a.Description ";
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateBy", itemInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateTime", itemInfo.Modify_Time);
sql = sql + " From ( ";
int i = 0;
foreach (var itemImageInfo in itemInfo.ItemImageList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + itemInfo.Item_Id + "' ObjectId "
+ ",'" + itemImageInfo.ImageURL + "' ImageURL "
+ ",'" + (itemImageInfo.DisplayIndex + 1) + "' DisplayIndex " //让DisplayIndex+1了
+ ",'" + itemImageInfo.Title + "' Title "
+ ",'" + itemImageInfo.Description + "' Description "
+ ",'" + itemImageInfo.ImageId + "' ImageId "
;
i++;
}
sql = sql + " ) a, ObjectImages x where (x.ImageId = a.ImageId)";
// ---( x.ObjectId = a.ObjectId and x.ImageURL = a.ImageURL and x.DisplayIndex = a.DisplayIndex) or
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 删除角色与菜单关系
/// </summary>
/// <param name="roleInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetRoleMenuStatus(RoleModel roleInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update T_Role_Menu set status = '-1' ";
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", roleInfo.Create_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", roleInfo.Create_Time);
sql = sql + " where role_id = '" + roleInfo.Role_Id + "' ";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 删除品牌信息
/// </summary>
/// <param name="unitInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetUnitBrandStatus(UnitInfo unitInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update StoreBrandMapping set IsDelete = '1' ";
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateBy", unitInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateTime", unitInfo.Modify_Time);
sql = sql + " where StoreId = '" + unitInfo.Id + "' ";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <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="itemInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetItemPriceUpdate(ItemInfo itemInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_item_price set status = '1' ,item_price = a.item_price";
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", itemInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", itemInfo.Modify_Time);
sql = pService.GetIsNotNullUpdateSql(sql, "if_flag", "0");
sql = sql + " From ( ";
int i = 0;
foreach (ItemPriceInfo itemPriceInfo in itemInfo.ItemPriceList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + itemInfo.Item_Id + "' item_id "
+ ",'" + itemPriceInfo.item_price + "' item_price "
+ ",'" + itemPriceInfo.item_price_type_id + "' item_price_type_id "
+ ",'" + itemPriceInfo.item_price_id + "' item_price_id "
+ ",'" + this.loggingSessionInfo.CurrentLoggingManager.Customer_Id + "' customer_id ";
i++;
}
sql = sql + " ) a, t_item_price x where ( x.item_id = a.item_id and x.item_price_type_id = a.item_price_type_id and x.customer_id = a.customer_id) or (x.item_price_id = a.item_price_id)";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <summary>
/// 删除商品价格
/// </summary>
/// <param name="itemInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetItemPriceStatus(ItemInfo itemInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update t_item_price set status = '-1' ";
sql = pService.GetIsNotNullUpdateSql(sql, "modify_user_id", itemInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "modify_time", itemInfo.Modify_Time);
sql = pService.GetIsNotNullUpdateSql(sql, "if_flag", "0");
sql = sql + " where item_id = '" + itemInfo.Item_Id + "' and customer_id = '" + this.loggingSessionInfo.CurrentLoggingManager.Customer_Id.ToString() + "' ";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <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="itemInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetItemUnitUpdate(ItemInfo itemInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "update ItemStoreMapping set isDelete = '0' ";
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateBy", itemInfo.Modify_User_Id);
sql = pService.GetIsNotNullUpdateSql(sql, "LastUpdateTime", itemInfo.Modify_Time);
//sql = pService.GetIsNotNullUpdateSql(sql, "if_flag", "0");
sql = sql + " From ( ";
int i = 0;
foreach (ItemStoreMappingEntity itemUnitInfo in itemInfo.ItemUnitList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + itemInfo.Item_Id + "' itemId "
+ ",'" + itemUnitInfo.UnitId + "' UnitId "
+ ",'" + itemUnitInfo.MappingId + "' MappingId ";
//+ ",'" + this.loggingSessionInfo.CurrentLoggingManager.Customer_Id + "' customer_id ";
i++;
}
sql = sql + " ) a, ItemStoreMapping x where x.itemId = a.itemId and x.UnitId = a.UnitId"; // or (x.UnitId = a.UnitId)
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
/// <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>
/// 插入商品门店
/// </summary>
/// <param name="itemInfo"></param>
/// <param name="pTran"></param>
/// <returns></returns>
private bool SetItemUnitInsert(ItemInfo itemInfo, IDbTransaction pTran)
{
PublicService pService = new PublicService();
#region
string sql = "insert into ItemStoreMapping "
+ " (MappingId "
+ " ,itemId "
+ " ,UnitId "
+ " ,IsDelete "
+ " ,CreateBy "
+ " ,CreateTime "
+ " ,LastUpdateBy "
+ " ,LastUpdateTime "
+ " ) "
+ " SELECT P.MappingId "
+ " ,P.itemId "
+ " ,P.UnitId "
+ " ,P.IsDelete "
+ " ,P.CreateBy "
+ " ,P.CreateTime "
+ " ,P.LastUpdateBy "
+ " ,P.LastUpdateTime "
+ " FROM ( ";
int i = 0;
foreach (ItemStoreMappingEntity itemUnitInfo in itemInfo.ItemUnitList)
{
if (i != 0)
{
sql = sql + " union all ";
}
sql = sql + "select '" + itemInfo.Item_Id + "' itemId "
+ ",'" + itemInfo.Create_User_Id + "' LastUpdateBy "
+ ",'" + itemInfo.Create_Time + "' LastUpdateTime "
+ ",'" + itemInfo.Create_User_Id + "' CreateBy "
+ ",'" + itemInfo.Create_Time + "' CreateTime "
+ ",'0' IsDelete "
+ ",'" + itemUnitInfo.UnitId + "' UnitId "
+ ",'" + itemUnitInfo.MappingId + "' MappingId ";
//+ ",'" + this.loggingSessionInfo.CurrentLoggingManager.Customer_Id + "' customer_id ";
i++;
}
sql = sql + " ) P "
+ " left join ItemStoreMapping b "
+ " on(P.MappingId = b.MappingId) "
+ " left join ItemStoreMapping c "
+ " on(P.itemId = c.itemId "
+ " and P.UnitId = c.UnitId) "
//+ " and P.customer_id = c.customer_id) "
+ " where b.UnitId is null "
+ " and c.itemId is null;";
#endregion
if (pTran != null)
{
this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, sql.ToString(), null);
}
else
{
this.SQLHelper.ExecuteNonQuery(sql);
}
return(true);
}
