/// <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);
}