/// <summary> /// 加载登录用户的分店信息 /// mysql加载本地数据库 /// </summary> private void MysqlloadLoginUserInfo() { MysqlDBHelper dbHelper = new MysqlDBHelper(); MySqlConnection conn = null; MySqlCommand cmd = new MySqlCommand(); try { conn = dbHelper.GetConnection(); string sql = "select a.id, a.branch_code,a.branch_name,d.street " + "from zc_branch_total a " + " left join zc_user_info b on a.id = b.BRANCH_NAME_ID " + " left join ctp_user c on c.id=b.user_id left join zc_zoning d on d.id=a.zoning_id where c.id='" + LoginUserInfo.id + "'"; cmd.CommandText = sql; cmd.Connection = conn; var reader = cmd.ExecuteReader(); if (reader.Read()) { LoginUserInfo.branchId = reader.IsDBNull(0) ? string.Empty : reader.GetString(0); LoginUserInfo.branchCode = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); LoginUserInfo.branchName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); LoginUserInfo.street = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); } } catch (Exception ex) { log.Error("从本地数据库加载登陆用户信息失败", ex); } finally { cmd.Dispose(); dbHelper.CloseConnection(conn); } }
private void check() { MysqlDBHelper dbHelper = new MysqlDBHelper(); MySqlConnection conn = dbHelper.GetConnection(); string sql = null; alreadyTotalAmount = 0; try { sql = "SELECT a.pay_mode,a.pay_amount,a.order_id FROM pay_info a WHERE a.order_id = '" + orderNum + "' "; MySqlDataReader reader = dbHelper.GetReader(sql, conn); while (reader.Read()) { alreadyTotalAmount += float.Parse(reader.IsDBNull(1) ? string.Empty : reader.GetString(1)); } } catch (Exception ex) { log.Error("支付信息明细列表绑定数据失败", ex); } finally { dbHelper.CloseConnection(conn); } }
private string amount() { int con = 0; MysqlDBHelper dbHelper = new MysqlDBHelper(); MySqlConnection conn = dbHelper.GetConnection(); string sql = null; try { sql = "select f.associator_cardnumber,e.consignee,e.cansignphone,f.associator_amount " + " from zc_order_transit e " + " LEFT JOIN zc_associator_info f on e.member_id = f.id " + " WHERE 1=1 "; sql += " and f.associator_cardnumber like '%" + keyStr + "%' or e.consignee like '%" + keyStr + "%' or e.cansignphone like '%" + keyStr + "%' "; MySqlDataReader reader = dbHelper.GetReader(sql, conn); while (reader.Read()) { con += 1; if (con == 1) {// associator_cardnumber = reader.IsDBNull(0) ? string.Empty : reader.GetString(0); associator_amount = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); } } } catch (Exception ex) { log.Error("会员明细列表绑定数据失败", ex); } finally { dbHelper.CloseConnection(conn); } return(associator_amount); }
/// <summary> /// 根据条件获取单个符合条件的订单,会员信息。传入CustomerDelivery页面-退款 /// </summary> /// <param name="searchString"></param> /// <param name="customerDelivery"></param> public void GetReturnOrderInfo(string searchString, CustomerDelivery customerDelivery) { MysqlDBHelper dbHelper = new MysqlDBHelper(); //string sql = "select a.id, a.consignee, a.cansignphone, b.associator_cardnumber, a.ORDERAMOUNT from zc_order_transit a left join zc_associator_info b on a.member_id = b.id where a.orderstatus = '" + Constant.ORDER_STATUS_FININSH + "' "; string sql = "select a.id, a.consignee, a.cansignphone, b.associator_cardnumber, a.ORDERAMOUNT from zc_order_history a left join zc_associator_info b on a.member_id = b.id where a.orderstatus in ('" + Constant.ORDER_STATUS_FININSH + "','" + Constant.ORDER_STATUS_PART_REFUSE + "') "; if (searchString != "") { ///根据条件查询 sql += " and ( a.consignee like '%" + searchString + "%' or a.cansignphone like '%" + searchString + "%' or b.associator_cardnumber like '%" + searchString + "%' ) "; } MySqlConnection conn = null; MySqlCommand cmd = new MySqlCommand(); try { conn = dbHelper.GetConnection(); cmd.Connection = conn; cmd.CommandText = sql; MySqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { string id = reader.IsDBNull(0) ? string.Empty : reader.GetString(0); string name = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); string phone = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); string card = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); string amount = reader.IsDBNull(4) ? string.Empty : reader.GetString(3); customerDelivery.setinform(id, amount, name, phone, card); } } catch (Exception ex) { log.Error("获取订单信息发送异常", ex); } finally { cmd.Dispose(); dbHelper.CloseConnection(conn); } }
/// <summary> /// 加载收货过的商品信息列表 /// </summary> private void loadHarvestGoods() { DateTime first = DateTime.Today; DateTime last = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59")); MysqlDBHelper dbHelper = new MysqlDBHelper(); MySqlConnection conn = null; string sql = "select serialNumber , name, classify, goods_unit, goods_specifications, goods_price as g_price, nums, actual_quantity, order_amount, receive_amount,sortenum from daily_receive_goods where createTime between @first and @last "; MySqlCommand cmd = new MySqlCommand(); try { conn = dbHelper.GetConnection(); cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.AddWithValue("@first", first); cmd.Parameters.AddWithValue("@last", last); DataSet ds = new DataSet(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); da.Fill(ds, "daily_receive_goods"); receiveAmount.DataPropertyName = "receive_amount"; actualQuantity.DataPropertyName = "actual_quantity"; orderAmount.DataPropertyName = "order_amount"; classify.DataPropertyName = "classify"; actualQuantity.ReadOnly = true; itemDataGridView.DataSource = ds; itemDataGridView.DataMember = "daily_receive_goods"; itemDataGridView.CurrentCell = null;//不默认选中 } catch (Exception ex) { log.Error("获取当天收货后的商品信息失败", ex); } finally { cmd.Dispose(); dbHelper.CloseConnection(conn); } }
/// <summary> /// 获取条件查询的记录数量-退款 /// </summary> /// <param name="searchString"></param> /// <returns></returns> public int GetReturnOrderCounts(string searchString) { int count = 0; MysqlDBHelper dbHelper = new MysqlDBHelper(); //string sql = "select count(1) from zc_order_transit a left join zc_associator_info b on a.member_id = b.id where a.orderstatus = '" + Constant.ORDER_STATUS_FININSH + "' "; //string sql = "select count(1) from zc_order_history a left join zc_associator_info b on a.member_id = b.id where a.orderstatus not in ('" + Constant.ORDER_STATUS_ALL_REFUSE + "','" + Constant.ORDER_STATUS_PART_REFUSE + "','" + Constant.ORDER_STATUS_ALL_REFUND + "','" + Constant.ORDER_STATUS_PART_REFUND + "') "; string sql = "select count(1) from zc_order_history a left join zc_associator_info b on a.member_id = b.id where a.orderstatus in ('" + Constant.ORDER_STATUS_FININSH + "','" + Constant.ORDER_STATUS_PART_REFUSE + "') "; if (searchString != "") { ///根据条件查询 sql += " and ( a.consignee like '%" + searchString + "%' or a.cansignphone like '%" + searchString + "%' or b.associator_cardnumber like '%" + searchString + "%' ) "; } MySqlConnection conn = null; MySqlCommand cmd = new MySqlCommand(); try { conn = dbHelper.GetConnection(); cmd.Connection = conn; cmd.CommandText = sql; MySqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { count = reader.IsDBNull(0) ? default(int) : reader.GetInt32(0); } } catch (Exception ex) { log.Error("获取条件查询的订单数量发生异常", ex); } finally { cmd.Dispose(); dbHelper.CloseConnection(conn); } return(count); }
/// <summary> /// 查询条码匹配的记录 /// </summary> /// <param name="bar">条码</param> /// <returns>商品匹配的记录数</returns> public int queryExistGood(ILog log) { int con = 0; ZcGoodsMaster zcGoodsMaster = new ZcGoodsMaster(); MysqlDBHelper dbHelper = new MysqlDBHelper(); MySqlConnection conn = dbHelper.GetConnection(); try { string sql = "select a.SERIALNUMBER ,a.goods_name,a.goods_unit,a.goods_specifications,a.goods_price,a.remark,a.id from zc_goods_master a " + "left join zc_classify_info b on a.goods_class_id = b.id " + "left join zc_classify_info c on a.goods_brand_id = c.id " + "left join zc_provider_info d on a.goods_supplier_id = d.id where 1=1 "; if (this.count.Equals(ParentWindow.CustomerDelivery.ToString()) || this.count.Equals(ParentWindow.DeliveryGoods.ToString())) { sql += " and a.SERIALNUMBER like '%" + bar + "%'"; } else { sql += " and a.SERIALNUMBER like '%" + bar + "%' or a.goods_name like '%" + bar + "%' or a.goods_unit like '%" + bar + "%' or a.goods_specifications like '%" + bar + "%' or a.goods_price like '%" + bar + "%' or a.remark like '%" + bar + "%' ;"; } MySqlDataReader reader = dbHelper.GetReader(sql, conn); while (reader.Read()) { con += 1; if (con == 1) { zcGoodsMaster.SerialNumber = reader.IsDBNull(0) ? string.Empty : reader.GetString(0); zcGoodsMaster.GoodsName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); zcGoodsMaster.GoodsUnit = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); zcGoodsMaster.GoodsSpecifications = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); zcGoodsMaster.GoodsPrice = reader.IsDBNull(4) ? default(float) : reader.GetFloat(4); zcGoodsMaster.Remark = reader.IsDBNull(5) ? string.Empty : reader.GetString(5); zcGoodsMaster.Id = reader.IsDBNull(6) ? string.Empty : reader.GetString(6); } } if (con == 1) { if (num.Equals("")) { num = "1"; } if (this.count.Equals(ParentWindow.DeliveryGoods.ToString())) { this.deliveryGoods.AddGoods(zcGoodsMaster, num); } else if (this.count.Equals(ParentWindow.ReturnGoods.ToString())) { this.returnGoods.AddGoods(zcGoodsMaster, num); } else if (this.count.Equals(ParentWindow.CustomerDelivery.ToString())) { this.customerDelivery.AddGoods(zcGoodsMaster, num); } } } catch (Exception ex) { log.Error("选择扫描商品绑定数据发生异常", ex); } finally { dbHelper.CloseConnection(conn); } return(con); }