/// <summary>
/// 得到一个对象实体
/// </summary>
public XHD.Model.shop_base_code GetModel(string type_no, string code_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select type_no,code_id,code_name,english_name,is_display,code_type,memo from shop_base_code ");
strSql.Append(" where type_no=@type_no and code_id=@code_id ");
MySqlParameter[] parameters =
{
new MySqlParameter("@type_no", MySqlDbType.VarChar, 2),
new MySqlParameter("@code_id", MySqlDbType.VarChar, 30)
};
parameters[0].Value = type_no;
parameters[1].Value = code_id;
XHD.Model.shop_base_code model = new XHD.Model.shop_base_code();
DataSet ds = Shop_DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 获得相关发票
/// </summary>
public DataSet GetRelateBill(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select * from xmh_shop.shop_invoices where invoices_among_id=(select invoices_among_id FROM xmh_shop.shop_invoices where " + strWhere + ")");
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得客户的退货商品详情
/// </summary>
public DataSet GetReturnDetail(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
strSql.Append(" select g.sid,g.goods_name,o.add_date,sum((ifnull(g.allow_sale,0) + ifnull(g.back_factory,0) + ifnull(g.allow_nosale,0))*g.goods_price) total,g.note ");
strSql.Append(" from xmh_shop.shop_return_goods g left join xmh_shop.shop_return_order o on o.return_id=g.return_id ");
//strSql.Append(" left join shop_order_info oi on oi.order_id=");
//strSql.Append(" select i.* ,e.name,CONCAT(c.C_name,',',c. C_tel,',',s.Customer) as customer,s.privatecustomer,s.BeSeats_id FROM xmh_shop.shop_invoices i ");
//strSql.Append(" left join xmh_crm_al.hr_employee e on i.admin_id=e.ID ");
//strSql.Append(" left join xmh_crm_al.crm_contact c on c.id=i.user_id ");
//strSql.Append(" left join xmh_crm_al.crm_customer s on s.id=c.C_customerid ");
strSql1.Append(" select count(o.*) from shop_return_order o left join shop_return_goods g on o.return_id=g.return_id ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
strSql1.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder + " limit " + (PageIndex - 1) * PageSize + "," + PageSize);
Total = Shop_DbHelperMySQL.Query(strSql1.ToString()).Tables[0].Rows[0][0].ToString();
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public XHD.Model.shop_authorize GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select r1.region_name rname1,r2.region_name rname2,sa.id,sa.user_id,sa.apply_admin_id,sa.cat,sa.show_seal,sa.company_name,sa.brand_id,sa.brand,sa.country,sa.province,sa.city,sa.district,sa.comment,sa.add_time,sa.auditor,sa.audit_status,sa.audit_time,sa.audit_notes,sa.is_available,sa.code from shop_authorize sa ");
strSql.Append(" left join xmh_shop.shop_region r1 on r1.region_id=sa.province ");
strSql.Append(" left join xmh_shop.shop_region r2 on r2.region_id=sa.city ");
strSql.Append(" where id=@id ");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 10)
};
parameters[0].Value = id;
XHD.Model.shop_authorize model = new XHD.Model.shop_authorize();
DataSet ds = Shop_DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 获得数据列表和总金额
/// </summary>
public DataSet GetListAndSummeony(string strWhere, out string sumMoney)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select cart_id,user_id,goods_id,goods_name,goods_img,goods_num,goods_price,is_shipping,goods_sn,shipping_fee_price,goods_unit,goods_weight,goods_attr ");
strSql.Append(" FROM shop_cart ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
string strSql1 = "select sum(ifnull(goods_num,0)*ifnull(goods_price,0)) as sumMoney from shop_cart ";
if (strWhere.Trim() != "")
{
strSql1 += " where " + strWhere;
}
DataSet ds = Shop_DbHelperMySQL.Query(strSql1.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
sumMoney = ds.Tables[0].Rows[0]["sumMoney"].ToString();
}
else
{
sumMoney = "0";
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获取购物车产品重量
/// </summary>
public DataSet GetCartweight(string strWhere)
{
string strSql1 = "select sum(ifnull(goods_num,0)*ifnull(goods_weight,9)) as sumWeight from shop_cart ";
if (strWhere.Trim() != "")
{
strSql1 += " where " + strWhere;
}
return(Shop_DbHelperMySQL.Query(strSql1.ToString()));
}
//获得某订单下的所有商品、运费等信息
public DataSet orderCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select sum(goodsNum) goodsNum,sum(shipMoney) shipMoney,sum(totalMoney) totalMoney,sum(shipping_fee) as shipping_fee from (");
strSql.Append(" select sum(g.goods_number) as goodsNum, (o.shipping_fee-o.deduction_shipping_fee) as shipMoney,o.shipping_fee, sum(g.goods_number*g.goods_price)+o.shipping_fee as totalMoney ");
strSql.Append(" from shop_order_goods g inner join shop_order_info o on g.order_id=o.order_id where g.order_id in(" + strWhere + ") group by g.order_id");
strSql.Append(") abc ");
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得开票总金额
/// </summary>
public DataSet GetTotlaMoney(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select sum(total_money) as total_money,sum(goods_num) as goods_num,sum(goods_sold_price*goods_sold_num) as saleTotal FROM shop_invoices_among_goods ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,user_id,apply_admin_id,cat,show_seal,company_name,brand_id,brand,country,province,city,district,comment,add_time,auditor,audit_status,audit_time,audit_notes,is_available ");
strSql.Append(" FROM shop_authorize ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select address_id,address_name,user_id,consignee,email,country,province,city,district,address,zipcode,tel,mobile,sign_building,best_time,is_default,is_invoice ");
strSql.Append(" FROM shop_user_address ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select sid,return_id,ck_admin_id,rec_id,goods_name,goods_id,goods_sn,goods_number,goods_price,order_price,goods_unit,note,return_goods_status,goods_reason,receive_num,allow_sale,noallow_sale,back_factory,allow_nosale,noallow,is_tax,order_id,erp_time,erp_code_as,erp_code_bf,erp_code_an,erp_code_n,is_invoice ");
strSql.Append(" FROM shop_return_goods ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select invoice_id,invoice_sn,invoice2_id,invoice_type,tax_rate,currency,exchange_rate,down_count,admin_id,user_id,address,bank_number,bank,shuihao,company_name,tel,total_count,total_money,note,invoices_note,pay_note,add_time,update_time,invoice_status,pre_status,invoice_time,erp_order_sn,invoice_num,invoice_date,refund_money,refund_djhm_str,sold_total_money,shipping_num,shipping_time,shipping_type,shipping_id,print_num,invoice_sn_temp,invoice_num_temp,last_update_admin,init_refund_money,print_hide,erp_time,invoices_among_id ");
strSql.Append(" FROM shop_invoices ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,order_id,order_sn,add_time,one_review,two_review,one_emp_code,two_emp_code,one_review_time,two_review_time,submit_emp_id,submit_emp_name ");
strSql.Append(" FROM shop_invoices_advance_billing ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select order_id,order_sn,wms_order_sn,erp_order_sn,cwerp_order_sn,user_id,order_status,shipping_status,pay_status,consignee,country,province,city,district,address,role_id,user_market_area,zipcode,tel,mobile,email,postscript,order_note,shipping_id,shipping_name,is_no_shipping_fee,pay_id,pay_name,trans_bank_id,trans_bank_name,inv_payee,inv_content,goods_amount,shipping_fee,insure_fee,pay_fee,pack_fee,money_paid,surplus,integral,integral_money,bonus,order_amount,from_ad,referer,add_time,confirm_time,pay_time,shipping_time,pack_id,card_id,bonus_id,invoice_no,to_buyer,pay_note,agency_id,inv_type,tax,discount,wuliu_id,wuliu_name,need_inv,is_send2_erp,is_send2_cwerp,is_wuliu_img,get_pay_type,is_daifa,is_no_qingdan,admin_user_id,admin_user_name,admin_user_real_name,send_user_name,send_user_tel,send_user_company,send_user_address,cangku_id,payinfo_pic,is_confirm_remind,is_print_remind,order_ip,cwerp_time,erp_time,is_need_review,review_info,operator,det_stats,dealer_id,is_show_price,operator_user_id,invoice_weight,order_confirm_info,invoice_id,wms_setbook_id,wms_doc_id,wms_account_flag,wms_receive_note,wms_flow_no,wms_finish_amout,wms_unfinish_amout,wms_invoice_type,wms_account_type,wms_voucher,wms_amount,wms_storage,supply_user_id,is_invoice_advance ");
strSql.Append(" FROM shop_order_info ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,target_id,instorage_goods_cat_id,type,goods_sn,goods_name,goods_packing,goods_unit,goods_num,goods_old_price,goods_old_num,goods_sold_price,goods_sold_num,goods_price,total_money,tax_rate,update_time,add_time,rec_id,order_id,t_is_shipping_fee,is_replace,is_hidden,goods_id ");
strSql.Append(" FROM shop_invoices_among_goods ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select gid,oid,rec_id,from_order_id,user_type,customer_id,user_id,goods_id,goods_sn2,goods_sn,goods_name,brand_id,old_price,goods_price,goods_unit,goods_desc,goods_number,booking_time,p_status,dispose_user,dispose_time,dispose_note,demand_time,grade,reviewer,is_paid,is_remind,arrive_time,last_update_time,is_show,already_bought,emp_code ");
strSql.Append(" FROM shop_purchase_goods ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ordergood_id,cart_id,order_id,order_sn,goods_id,goods_name,goods_sn,goods_number,invoice_number,market_price,goods_price,shipping_fee_price,brand_id,cat_id,user_id,admin_id,purchase_goods_status,is_submit_purchase,stock_warn,is_invoice,is_add_order,doc_id,wms_setbook_id,wms_position,wms_record,goods_attr ");
strSql.Append(" FROM shop_order_goods ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select type_no,code_id,code_name,english_name,is_display,code_type,memo ");
strSql.Append(" FROM shop_base_code ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
public DataSet GetReturnTotalMoney(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select sum((ifnull(g.allow_sale,0) + ifnull(g.back_factory,0) + ifnull(g.allow_nosale,0))*g.goods_price) as total from shop_return_order o ");
strSql.Append(" left join shop_return_goods g on o.return_id=g.return_id ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select attr_id,pay_id,attr_key,attr_values,input_type ");
strSql.Append(" FROM shop_payment_attr ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select Id,user_id,reg_field_id,content,user_name,crm_userId ");
strSql.Append(" FROM shop_reg_extend_info ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,goods_id,order_id,rec_id,admin_id,goods_num,add_time,locked_num,match_time,match_num,is_use,wms_storage ");
strSql.Append(" FROM shop_locked_goods ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,type,order_sn,add_time,money,user_id,employee_id,emp_code,msg,status,start_valid_time,user_yingshou ");
strSql.Append(" FROM shop_progress_payments ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select oid,purchase_sn,add_time,admin_id,p_status,last_update_time,arrive_time ");
strSql.Append(" FROM shop_purchase_order ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetListOrder(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select i.*,o.* FROM shop_invoices_among_goods i ");
strSql.Append(" inner join shop_order_goods o on o.ordergood_id=i.rec_id ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pay_id,pay_name,pay_code,pay_notes,pay_fee,pay_sort,is_enabled,is_cod,is_online,is_user_show ");
strSql.Append(" FROM shop_payment ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select invoice_id,invoice_type,tax_rate,admin_id,invoice_no,user_id,total_count,total_money,total_tax,invoice_create_time,tax_account,add_time,note,invoice1_id,update_time ");
strSql.Append(" FROM shop_invoices2 ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select aid,oid,gid,p_status,username,action_info,action_time ");
strSql.Append(" FROM shop_purchase_log ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select shipping_id,shipping_code,shipping_name,shipping_tel,shipping_address,shipping_desc,is_available,send_pay ");
strSql.Append(" FROM shop_shipping ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select unload_id,unload_name,comp_id,province_id,city_id,distinct_id,unload_tel,unload_address ");
strSql.Append(" FROM shop_ship_unload ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Shop_DbHelperMySQL.Query(strSql.ToString()));
}
