/// <summary>
/// 通过会员名来获取会员的订单
/// </summary>
/// <param name="name"></param>
/// <param name="pay_status"></param>
/// <returns></returns>
public ArrayList getOrdersArray(string name, string pay_status)
{
ArrayList orders = new ArrayList();
string sql = "select * from tb_auto_order where is_pay='" + pay_status + "' and name='" + name + "' order by order_time desc";
DataTable table = new DBOperateCommon().excuteQuery(sql);
foreach (DataRow dr in table.Rows)
{
AutoOrder order = new AutoOrder();
order.Auto_no = dr["auto_no"].ToString();
order.Name = dr["name"].ToString();
order.Order_time = Convert.ToDateTime(dr["order_time"].ToString()).ToString("yyyyMMdd HHmm");
order.Order_count = Convert.ToInt32(dr["order_count"].ToString());
order.Collection_count = Convert.ToInt32(dr["collection_count"].ToString());
order.Take_charge = Convert.ToInt32(dr["take_charge"].ToString());
order.Discount_code = dr["discount_code"].ToString();
order.Discount = Convert.ToSingle(dr["discount"].ToString());
order.Pay_before_discount = Convert.ToSingle(dr["pay_before_discount"].ToString());
order.Pay_after_discount = Convert.ToSingle(dr["pay_after_discount"].ToString());
order.Less_pay = Convert.ToSingle(dr["less_pay"].ToString());
order.Is_pay = dr["is_pay"].ToString();
order.Pay_way = dr["pay_way"].ToString();
order.Pay_time = dr["pay_time"].ToString();
order.Excel_path = dr["excel_path"].ToString();
orders.Add(order);
}
return(orders);
}
/// <summary>
/// 通过 auto_no 来获取订单
/// </summary>
/// <param name="auto_no"></param>
/// <returns></returns>
public AutoOrder getAutoOrder(string auto_no)
{
AutoOrder order = new AutoOrder();
string sql = "select * from tb_auto_order where auto_no='" + auto_no + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
foreach (DataRow dr in table.Rows)
{
order.Auto_no = dr["auto_no"].ToString();
order.Name = dr["name"].ToString();
order.Order_time = Convert.ToDateTime(dr["order_time"].ToString()).ToString("yyyy-MM-dd HH:mm");
order.Order_count = Convert.ToInt32(dr["order_count"].ToString());
order.Collection_count = Convert.ToInt32(dr["collection_count"].ToString());
order.Take_charge = Convert.ToSingle(dr["take_charge"].ToString());
order.Discount_code = dr["discount_code"].ToString();
order.Discount = Convert.ToSingle(dr["discount"].ToString());
order.Pay_before_discount = Convert.ToSingle(dr["pay_before_discount"].ToString());
order.Pay_after_discount = Convert.ToSingle(dr["pay_after_discount"].ToString());
order.Less_pay = Convert.ToSingle(dr["less_pay"].ToString());
order.Is_pay = dr["is_pay"].ToString();
order.Pay_way = dr["pay_way"].ToString();
order.Pay_time = dr["pay_time"].ToString();
order.Excel_path = dr["excel_path"].ToString();
}
return(order);
}
/// <summary>
/// 设置本地取件邮费
/// </summary>
/// <param name="base_weight"></param>
/// <param name="base_price"></param>
/// <param name="per_price"></param>
/// <returns></returns>
public bool setLocalRate(float base_weight, float base_price, float per_price)
{
bool flag = false;
string sql = "select * from tb_local_pick_up where kg=" + base_weight;
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
string up_sql = "update tb_local_pick_up set base_price=" + base_price + ", up_per_kg=" + per_price + " where kg=" + base_weight;
if (new DBOperateCommon().excuteNoQuery(up_sql))
{
flag = true;
}
}
else
{
string insert_sql = "insert into tb_local_pick_up values(" + base_weight + "," + base_price + "," + per_price + ")";
if (new DBOperateCommon().excuteNoQuery(insert_sql))
{
flag = true;
}
}
return(flag);
}
/// <summary>
/// 通过起始时间、终止时间、会员名获取订单信息
/// </summary>
/// <param name="start">起始时间</param>
/// <param name="end">终止时间</param>
/// <param name="name">会员名</param>
/// <returns>返回订单信息，将订单信息存放在ArrayList对象中</returns>
public ArrayList getOrders(string start, string end, string pay_status, int pageNow, int pageSize)
{
ArrayList orders = new ArrayList();
string sql = "select top " + pageSize + " * from tb_auto_order where is_pay='" + pay_status +
"' and pay_time>='" + start + "' and pay_time<='" + end + "' and auto_no not in (select top " + (pageNow - 1) * pageSize + " auto_no from tb_auto_order where is_pay='" + pay_status + "' and pay_time>='" + start + "' and pay_time<='" + end + "' order by pay_time desc) order by pay_time desc";
DataTable table = new DBOperateCommon().excuteQuery(sql);
foreach (DataRow dr in table.Rows)
{
AutoOrder order = new AutoOrder();
order.Auto_no = dr["auto_no"].ToString();
order.Name = dr["name"].ToString();
order.Order_time = Convert.ToDateTime(dr["order_time"].ToString()).ToString("yyyyMMdd HHmm");
order.Order_count = Convert.ToInt32(dr["order_count"].ToString());
order.Collection_count = Convert.ToInt32(dr["collection_count"].ToString());
order.Take_charge = Convert.ToInt32(dr["take_charge"].ToString());
order.Discount_code = dr["discount_code"].ToString();
order.Discount = Convert.ToSingle(dr["discount"].ToString());
order.Pay_before_discount = Convert.ToSingle(dr["pay_before_discount"].ToString());
order.Pay_after_discount = Convert.ToSingle(dr["pay_after_discount"].ToString());
order.Less_pay = Convert.ToSingle(dr["less_pay"].ToString());
order.Is_pay = dr["is_pay"].ToString();
order.Pay_way = dr["pay_way"].ToString();
order.Pay_time = dr["pay_time"].ToString();
orders.Add(order);
}
return(orders);
}
/// <summary>
/// 通过用户名获取用户本地未付款的订单
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public DataTable getUnPayInnerPackageTable(string name)
{
string sql = "select * from tb_inner_packages where name='" + name + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
//判断工作人员是否存在
/// <summary>
///
/// </summary>
/// <param name="name">职工名</param>
/// <param name="password">密码</param>
/// <param name="type">类型</param>
/// <returns>存在返回 1，否则返回 -1</returns>
public int checkStaff(string name, string password, string type)
{
int flag = 0;
string sql = "select password from tb_staff where name='" + name + "' and type='" + type + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i][0].Equals(password))
{
flag = 1;
break;
}
}
}
else
{
flag = -1;
}
return(flag);
}
/// <summary>
/// 通过用户名获取地址信息
/// </summary>
/// <param name="user_name"></param>
/// <returns></returns>
public ArrayList getAddress(string user_name)
{
ArrayList address_array = new ArrayList();
string sql = "select * from tb_addr_book where user_name='" + user_name + "' order by is_default desc,time desc";
DataTable table = new DBOperateCommon().excuteQuery(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
Address address = new Address();
address.Addr_id = Convert.ToInt32(table.Rows[i]["addr_id"]);
address.User_name = table.Rows[i]["user_name"].ToString();
address.Company_name = table.Rows[i]["company_name"].ToString();
address.Addr_type = table.Rows[i]["addr_type"].ToString();
address.Addr_contact = table.Rows[i]["addr_contact"].ToString();
address.Addr_line1 = table.Rows[i]["addr_line1"].ToString();
address.Addr_line2 = table.Rows[i]["addr_line2"].ToString();
address.Addr_line3 = table.Rows[i]["addr_line3"].ToString();
address.City = table.Rows[i]["city"].ToString();
address.Post_code = table.Rows[i]["post_code"].ToString();
address.Country = table.Rows[i]["country"].ToString();
address.Phone = table.Rows[i]["phone"].ToString();
address.Email = table.Rows[i]["email"].ToString();
address.Is_default = table.Rows[i]["is_default"].ToString();
address_array.Add(address);
}
return(address_array);
}
/// <summary>
/// 从英国到中国的 EMS方式的运费计算
/// </summary>
/// <param name="weight">重量</param>
/// <param name="length">长度</param>
/// <param name="width">宽度</param>
/// <param name="height">高度</param>
/// <returns>返回价格</returns>
private float getPriceEMS(float weight, float length, float width, float height)
{
float price = 0;
string sql = "";
//获得包裹的实际重量，如果体积过大就计算体积重
weight = (float)Math.Max(weight, ((length * width * height) / 5000.0));
if (weight <= 30.0)
{
sql = "select * from tb_rate_ems where kg>=" + weight + " order by kg asc";
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
price = Convert.ToSingle(table.Rows[0]["rate1"]);
}
}
else
{
price = 9000.0f;
}
return(price);
}
/// <summary>
/// 获取修改购物车信息所需要的包裹信息
/// </summary>
/// <returns></returns>
public ArrayList getPackageCartInfo(string order_number)
{
ArrayList packages = new ArrayList();
string sql = "select * from tb_package where order_number='" + order_number + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
PackageCartInfo package = new PackageCartInfo();
package.Package_id = Convert.ToInt32(table.Rows[i]["package_id"].ToString());
package.Description = table.Rows[i]["description"].ToString();
package.Value = Convert.ToSingle(table.Rows[i]["package_value"].ToString());
package.Weight = Convert.ToSingle(table.Rows[i]["weight"].ToString());
package.Length = Convert.ToSingle(table.Rows[i]["length"].ToString());
package.Width = Convert.ToSingle(table.Rows[i]["width"].ToString());
package.Height = Convert.ToSingle(table.Rows[i]["height"].ToString());
packages.Add(package);
}
return(packages);
}
// 跟新用户的 欠款表
public bool updateUserDebt(UserDebt userdebt)
{
bool flag = false;
string str = "select * from tb_user_debt where user_name='" + userdebt.User_name + "'";
DataTable table = new DBOperateCommon().excuteQuery(str);
if (table.Rows.Count > 0)
{
// 该用户不是第一次借款
float old_amount = Convert.ToSingle(table.Rows[0]["amount"].ToString());
float new_amount = old_amount + userdebt.Amount;
string up = "update tb_user_debt set amount=" + new_amount + " where user_name='" + userdebt.User_name + "'";
if (new DBOperateCommon().excuteNoQuery(up))
{
flag = true;
}
}
else
{
string sql = "insert into tb_user_debt values('" + userdebt.User_name + "'," + userdebt.Amount + ")";
if (new DBOperateCommon().excuteNoQuery(sql))
{
flag = true;
}
}
return(flag);
}
/// <summary>
/// 分页获取交易记录
/// </summary>
/// <param name="pageNow">当前页数</param>
/// <param name="pageSize">每页显示的条数</param>
/// <returns></returns>
public ArrayList getMyAccountTrans(int pageNow, int pageSize, string name)
{
ArrayList trans = new ArrayList();
string sql = "select top " + pageSize + " * from tb_my_account_trans where user_name='" + name + "' and id not in(select top " + (pageNow - 1) * pageSize + " id from tb_my_account_trans where user_name='" + name + "' order by trans_time desc ) order by trans_time desc";
DataTable table = new DBOperateCommon().excuteQuery(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
MyAccountTrans myaccounttans = new MyAccountTrans();
myaccounttans.User_name = table.Rows[i]["user_name"].ToString();
myaccounttans.Amout = Convert.ToSingle(table.Rows[i]["amount"].ToString());
myaccounttans.Operation = table.Rows[i]["operation"].ToString();
myaccounttans.Ratio = Convert.ToSingle(table.Rows[i]["ratio"].ToString());
myaccounttans.Charge_way = table.Rows[i]["charge_way"].ToString();
myaccounttans.Bank_account_number = table.Rows[i]["bank_account_number"].ToString();
myaccounttans.Order_number = table.Rows[i]["order_number"].ToString();
myaccounttans.Time = Convert.ToDateTime(table.Rows[i]["trans_time"].ToString());
trans.Add(myaccounttans);
}
return(trans);
}
/// <summary>
/// 用户使用账户付款
/// </summary>
/// <param name="account">账户</param>
/// <param name="money">付款金额</param>
/// <returns></returns>
public bool userPayUseMyAccount(string account, float money)
{
bool flag = false;
string sql = "select * from tb_my_account where name='" + account + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
float old_balance = Convert.ToSingle(table.Rows[0]["balance"].ToString());
float new_balance = old_balance - money;
// 跟新账户表
string update = "update tb_my_account set balance=" + new_balance + " where name='" + account + "'";
if (new DBOperateCommon().excuteNoQuery(update))
{
// 付款成功
flag = true;
// 向账户交易记录表中添加交易记录
MyAccountTrans myaccounttrans = new MyAccountTrans();
myaccounttrans.Amout = -money;
myaccounttrans.Operation = "下单付款";
myaccounttrans.User_name = account;
myaccounttrans.Time = DateTime.Now;
new MyAccountTransDAO().addMyAccountTrans(myaccounttrans);
}
return(flag);
}
/// <summary>
/// 通过addr_id获取地址信息
/// </summary>
/// <param name="addr_id"></param>
/// <returns></returns>
public Address getAddress(int addr_id)
{
Address address = new Address();
string sql = "select * from tb_addr_book where addr_id=" + addr_id;
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
address.Addr_id = addr_id;
address.User_name = table.Rows[0]["user_name"].ToString();
address.Company_name = table.Rows[0]["company_name"].ToString();
address.Addr_type = table.Rows[0]["addr_type"].ToString();
address.Addr_contact = table.Rows[0]["addr_contact"].ToString();
address.Addr_line1 = table.Rows[0]["addr_line1"].ToString();
address.Addr_line2 = table.Rows[0]["addr_line2"].ToString();
address.Addr_line3 = table.Rows[0]["addr_line3"].ToString();
address.City = table.Rows[0]["city"].ToString();
address.Post_code = table.Rows[0]["post_code"].ToString();
address.Country = table.Rows[0]["country"].ToString();
address.Phone = table.Rows[0]["phone"].ToString();
address.Email = table.Rows[0]["email"].ToString();
address.Is_default = table.Rows[0]["is_default"].ToString();
}
return(address);
}
/// <summary>
/// 更新用户的EMS邮费类型
/// </summary>
/// <param name="username">用户名</param>
/// <param name="ratetype">邮费类别</param>
/// <param name="admin">管理员</param>
/// <returns>1 表示更新成功， 2 表示更新失败 ， 3 表示 设置成功，4 表示设置失败</returns>
public int setRateForUser(string username, string ratetype, string admin)
{
int flag = -1;
string sql = "select * from tb_set_user_ems_rate where username='******'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
//更新用户的邮费类别
string up_sql = "update tb_set_user_ems_rate set ratetype='" + ratetype + "' , setername='" + admin + "' , settime='" + DateTime.Now.ToString() + "'";
if (new DBOperateCommon().excuteNoQuery(up_sql))
{
flag = 1;
}
else
{
flag = 2;
}
}
else
{
string insert_sql = "insert into tb_set_user_ems_rate values('" + username + "','" + ratetype + "','" + admin + "','" + DateTime.Now.ToString() + "')";
if (new DBOperateCommon().excuteNoQuery(insert_sql))
{
flag = 3;
}
else
{
flag = 4;
}
}
return(flag);
}
/// <summary>
/// 获取没有付款的本地订单
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public ArrayList getUnPayLocalOrder(string name)
{
ArrayList array = new ArrayList();
string sql = "select * from tb_local_order where is_pay='unpay' and name='" + name + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
LocalOrder local_order = new LocalOrder();
local_order.Id = Convert.ToInt32(table.Rows[i]["order_id"].ToString());
local_order.Order_no = table.Rows[i]["order_no"].ToString();
local_order.Name = table.Rows[i]["name"].ToString();
local_order.Collectioncompanyname = table.Rows[i]["collectioncompanyname"].ToString();
local_order.Collectioncontactname = table.Rows[i]["collectioncontactname"].ToString();
local_order.Collectionphone = table.Rows[i]["collectionphone"].ToString();
local_order.Collectionaddressline = table.Rows[i]["collectionaddressline"].ToString();
local_order.Collectiontown = table.Rows[i]["collectiontown"].ToString();
local_order.Collectionpostcode = table.Rows[i]["collectionpostcode"].ToString();
local_order.Collectioncountry = table.Rows[i]["collectioncountry"].ToString();
local_order.Recipientcompanyname = table.Rows[i]["recipientcompanyname"].ToString();
local_order.Recipientcontactname = table.Rows[i]["recipientcontactname"].ToString();
local_order.Recipientaddressline = table.Rows[i]["recipientaddressline"].ToString();
local_order.Recipientphone = table.Rows[i]["recipientphone"].ToString();
local_order.Recipientpostcode = table.Rows[i]["recipientpostcode"].ToString();
local_order.Recipienttown = table.Rows[i]["recipienttown"].ToString();
local_order.Recipientcountry = table.Rows[i]["recipientcountry"].ToString();
local_order.Servicecode = table.Rows[i]["servicecode"].ToString();
local_order.Delivery_date = table.Rows[i]["delivery_date"].ToString();
local_order.Delivery_time = table.Rows[i]["delivery_time"].ToString();
local_order.Purpose = table.Rows[i]["purpose"].ToString();
local_order.Description = table.Rows[i]["description"].ToString();
local_order.Estimate_value = Convert.ToSingle(table.Rows[i]["estimate_value"].ToString());
local_order.Insurance = Convert.ToSingle(table.Rows[i]["insurance"].ToString());
local_order.Quantity = Convert.ToInt32(table.Rows[i]["quantity"].ToString());
local_order.Weight = Convert.ToSingle(table.Rows[i]["weight"].ToString());
local_order.Wm_track_no = table.Rows[i]["wm_track_no"].ToString();
local_order.Track_no = table.Rows[i]["track_no"].ToString();
local_order.Pay_before_discount = Convert.ToSingle(table.Rows[i]["pay_before_discount"].ToString());
local_order.Discount = Convert.ToSingle(table.Rows[i]["discount"].ToString());
local_order.Pay_after_discount = Convert.ToSingle(table.Rows[i]["pay_after_discount"].ToString());
local_order.Less_pay = Convert.ToSingle(table.Rows[i]["less_pay"].ToString());
local_order.Wm_pay = Convert.ToSingle(table.Rows[i]["wm_pay"].ToString());
local_order.Profit = Convert.ToSingle(table.Rows[i]["profit"].ToString());
local_order.Pay_way = table.Rows[i]["pay_way"].ToString();
local_order.Is_pay = table.Rows[i]["is_pay"].ToString();
local_order.Pay_time = table.Rows[i]["pay_time"].ToString();
local_order.Order_time = table.Rows[i]["order_time"].ToString();
array.Add(local_order);
}
return(array);
}
/// <summary>
/// 计算EMS的邮费
/// </summary>
/// <param name="name">用户名</param>
/// <param name="weight">重量</param>
/// <param name="chargeable">实际计算的重量</param>
/// <returns>价格</returns>
private float getEMSPrice(string name, float weight, float chargeable)
{
float price = 0;
string sql = "";
if (chargeable <= 30.0)
{
string sql1 = "select ratetype from tb_set_user_ems_rate where username='******'";
DataTable table = new DBOperateCommon().excuteQuery(sql1);
if (table.Rows.Count > 0)
{
sql = "select kg," + table.Rows[0][0].ToString() + " from tb_rate_ems where kg>=" + chargeable + " order by kg asc";
DataTable table1 = new DBOperateCommon().excuteQuery(sql);
price = Convert.ToSingle(table1.Rows[0][1]);
}
else
{
sql = "select kg,rate1 from tb_rate_ems where kg>=" + chargeable + " order by kg asc";
DataTable table1 = new DBOperateCommon().excuteQuery(sql);
price = Convert.ToSingle(table1.Rows[0][1]);
}
}
else
{
price = 9000.0f;
}
return(price);
}
/// <summary>
/// 通过地址编号获取地址对象
/// </summary>
/// <param name="addr_id"></param>
/// <returns></returns>
public SendAddress getSendAddress(int addr_id)
{
SendAddress send = new SendAddress();
string sql = "select * from tb_senderAddress where addr_id=" + addr_id;
DataTable dt = new DBOperateCommon().excuteQuery(sql);
if (dt.Rows.Count > 0)
{
send.Addr_id = Convert.ToInt32(dt.Rows[0][0].ToString());
send.Name = dt.Rows[0][1].ToString();
send.Sender_name = dt.Rows[0][2].ToString();
send.Country = dt.Rows[0][3].ToString();
send.Province = dt.Rows[0][4].ToString();
send.City = dt.Rows[0][5].ToString();
send.Area = dt.Rows[0][6].ToString();
send.Street_info = dt.Rows[0][7].ToString();
send.Company_name = dt.Rows[0][8].ToString();
send.Postcode = dt.Rows[0][9].ToString();
send.E_mail = dt.Rows[0][10].ToString();
send.Phone = dt.Rows[0][11].ToString();
send.Is_default = dt.Rows[0][12].ToString();
send.Time = Convert.ToDateTime(dt.Rows[0][13].ToString());
}
return(send);
}
/// <summary>
/// 获取申请还款的 列表
/// </summary>
/// <returns></returns>
public ArrayList getPayBackApplyArray()
{
ArrayList array = new ArrayList();
string sql = "select * from tb_payback_apply where is_agree=0";
DataTable table = new DBOperateCommon().excuteQuery(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
PayBackApply payback = new PayBackApply();
payback.Id = Convert.ToInt32(table.Rows[i]["id"].ToString());
payback.User_name = table.Rows[i]["user_name"].ToString();
payback.Amount = Convert.ToSingle(table.Rows[i]["amount"].ToString());
payback.Staff_apply = table.Rows[i]["apply_staff"].ToString();
payback.Operation = table.Rows[i]["operation"].ToString();
payback.Operation_time = Convert.ToDateTime(table.Rows[i]["operation_time"].ToString());
payback.Note = table.Rows[i]["note"].ToString();
payback.Image = table.Rows[i]["image"].ToString();
payback.Is_aggree = Convert.ToInt32(table.Rows[i]["is_agree"].ToString());
array.Add(payback);
}
return(array);
}
/// <summary>
/// 获取员工信息表
/// </summary>
/// <returns></returns>
public DataTable getStaffs(int pageNow, int pageSize)
{
string sql = "select top " + pageSize + " * from tb_staff where type='staff' or type='manager' and staff_id not in(select top " + (pageNow - 1) * pageSize + " staff_id from tb_staff where type='staff' or type='manager' order by time asc) order by time asc";
DataTable table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// 获取收件地址表
/// </summary>
/// <param name="user_name"></param>
/// <returns></returns>
public DataTable getReceiveAddressTable(string user_name)
{
DataTable table = new DataTable();
string sql = "select * from tb_addr_book where user_name='" + user_name + "' and addr_type='R' order by is_default desc, time desc";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// 分页获取取件免费的用户信息
/// </summary>
/// <param name="pageNow">当前页</param>
/// <param name="pageSize">每页显示的条数</param>
/// <returns></returns>
public DataTable getUserDeliveryFree(int pageNow, int pageSize)
{
DataTable table = new DataTable();
string sql = "select top " + pageSize + " name,delivery from tb_user where delivery='free' and name not in(select top " + (pageNow - 1) * pageSize + " name from tb_user where delivery='free' )";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// is_agree = 0 表示申请 还款
/// </summary>
/// <returns></returns>
public DataTable getPayBackApplyTable(int pageNow, int pageSize)
{
DataTable table = new DataTable();
string sql = "select top " + pageSize + " * from tb_payback_apply where is_agree=0 and id not in(select top " + (pageNow - 1) * pageSize + " id from tb_payback_apply where is_agree=0)";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
public DataTable getDiscountArray(string discountCode, string name)
{
string sql = "select * from tb_postnl_discount where discount_code='" + discountCode + "' and name='" + name + "'";
DataTable table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// 获取用户的邮费信息
/// </summary>
/// <returns></returns>
public DataTable getUserRate(int pageNow, int pageSize)
{
DataTable table = new DataTable();
string sql = "select top " + pageSize + " * from tb_set_user_pf_gpr_collection_rate where username not in(select top " + (pageNow - 1) * pageSize + " username from tb_set_user_pf_gpr_collection_rate)";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
//////////////////////////////////////////////////////////////////////////
//获得卡充值的记录
public DataTable getCardChargeRecordTable(int is_checked, int pageNow, int pageSize)
{
DataTable table = new DataTable();
string sql = "select top " + pageSize + " * from tb_my_account_trans where operation='自己给账户充值' and ratio=" + is_checked + " and id not in(select top " + (pageNow - 1) * pageSize + " id from tb_my_account_trans where operation='自己给账户充值' and ratio=" + is_checked + " order by trans_time desc ) order by trans_time";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// 获取所有本地取件邮费信息
/// </summary>
/// <returns></returns>
public DataTable getLocalRateInfoTable()
{
DataTable table = new DataTable();
string sql = "select * from tb_local_pick_up ";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// 分页获取收件地址信息
/// </summary>
/// <param name="name">用户名</param>
/// <param name="pageNow">当前页</param>
/// <param name="pageSize">每页显示的条数</param>
/// <returns></returns>
public DataTable getReceiveAddressTable(string name, int pageNow, int pageSize)
{
DataTable table = new DataTable();
string sql = "select top " + pageSize + " * from tb_addr_book where user_name='" + name + "' and addr_type='R' and addr_id not in (select top " + (pageNow - 1) * pageSize + " addr_id from tb_addr_book where user_name='"
+ name + "' and addr_type='R') order by is_default desc, time desc";
table = new DBOperateCommon().excuteQuery(sql);
return(table);
}
/// <summary>
/// 获取用户邮费记录条数
/// </summary>
/// <returns></returns>
public int getUserRateCount()
{
int count = 0;
string sql = "select count(*) from tb_set_user_pf_gpr_collection_rate";
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
count = Convert.ToInt32(table.Rows[0][0]);
}
return(count);
}
//获取交易记录条数
public int getRecordCount()
{
int record_count = 0;
string sql = "select count(*) from tb_user_debt_trans ";
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
record_count = Convert.ToInt32(table.Rows[0][0].ToString());
}
return(record_count);
}
/// <summary>
/// 获取取件费用
/// </summary>
/// <param name="count">包裹个数</param>
/// <returns>取件费用</returns>
public float getDeliveryRate(int count)
{
float money = 0;
string sql = "select * from tb_delivery_rate where package_count =" + count;
DataTable table = new DBOperateCommon().excuteQuery(sql);
if (table.Rows.Count > 0)
{
money = Convert.ToSingle(table.Rows[0]["retailrate1"].ToString());
}
return(money);
}
