/// <summary>
/// 连接MYsql数据库
/// </summary>
/// <returns></returns>
public static MySqlHelper connMysql()
{
string ConnenctionString = System.Configuration.ConfigurationManager.ConnectionStrings["mysqlConn"].ToString();
SinoHelper2.MySqlHelper help = new SinoHelper2.MySqlHelper(ConnenctionString, SinoHelper2.ConnectionStringType.ConnString);
return(help);
}
public static void GetProductInfo()
{
SinoHelper2.MySqlHelper helper_mysql = PublicHelper.connMysql();
//判断该会员是否已经发放过
string checksql = "select * from ecm_goods";
DataTable dt = helper_mysql.ExecDataTable(checksql);
SqlHelper helper_mssql = LocalSqlHelper.WH;
for (int i = 0; i < dt.Rows.Count; i++)
{
helper_mssql.Params.Clear();
helper_mssql.Params.Add("shopxpptid", Convert.ToInt32(dt.Rows[i]["goods_id"]));
helper_mssql.Params.Add("supplier_id", Convert.ToInt32(dt.Rows[i]["store_id"]));
helper_mssql.Params.Add("shopxpptname", Convert.ToString(dt.Rows[i]["goods_name"]));
helper_mssql.Params.Add("hezuojia", 0);
helper_mssql.Insert("shopxp_product");
//string updatesql = "update ecm_goods set sys_flag=1 where goods_id=" + dt.Rows[i]["goods_id"].ToString();
//helper_mysql.Execute(updatesql);
}
checksql = "select a.* from ecm_goods_spec a left join ecm_goods b on a.goods_id=b.goods_id";
dt = helper_mysql.ExecDataTable(checksql);
for (int i = 0; i < dt.Rows.Count; i++)
{
helper_mssql.Clear();
helper_mssql.Params.Add("id", Convert.ToInt32(dt.Rows[i]["spec_id"]));
helper_mssql.Params.Add("shopxpptid", Convert.ToInt32(dt.Rows[i]["goods_id"]));
helper_mssql.Params.Add("txm", Convert.ToString(dt.Rows[i]["sku"]));
helper_mssql.Params.Add("p_size", Convert.ToString(dt.Rows[i]["spec_1"]) + "/" + Convert.ToString(dt.Rows[i]["spec_2"]));
helper_mssql.Insert("shopxp_kucun");
//string updatesql = "update ecm_goods_spec set sys_flag=1 where spec_id =" + dt.Rows[i]["spec_id"].ToString();
//helper_mysql.Execute(updatesql);
}
}
protected void LoadData(int page, string str)
{
int count = 0;
SinoHelper2.MySqlHelper helper_mysql = PublicHelper.connMysql();
DataTable dt = helper_mysql.GetPageData("ecm_order_return", "*,ck_remark as checkidstr", "return_id", Pager.PageSize, page, false, "1=1", out count);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["checkidstr"] = Convert.ToInt16(dt.Rows[i]["ck_status"]) == 0 ? "<input type='checkbox' name='cancelid' value='" + dt.Rows[i]["return_id"] + "'/>" : "";
}
rptruku_newlist.DataShow(dt);
Pager.RecordCount = count;
Pager.UrlRewritePattern = "AgentOrderCancel.aspx?page={0}" + querystr;
}
/// <summary>
///
/// </summary>
/// <param name="goods_id"></param>
/// <returns></returns>
public static int getSupplierId(int goods_id)
{
int supplier_id = 0;
SinoHelper2.MySqlHelper helper_mysql = PublicHelper.connMysql();
helper_mysql.Params.Add("?goods_id", goods_id);
//判断该会员是否已经发放过
string checksql = "select supplier_id from ecm_goods where goods_id=?goods_id";
DataTable dt = helper_mysql.ExecDataTable(checksql);
if (dt.Rows.Count > 0)
{
supplier_id = Convert.ToInt32(dt.Rows[0]["supplier_id"]);
}
return(supplier_id);
}
public static void GetSupplierInfo()
{
SinoHelper2.MySqlHelper helper_mysql = PublicHelper.connMysql();
string checksql = "select * from ecm_store where sgrade in (2,4) and sys_flag=0";
DataTable dt = helper_mysql.ExecDataTable(checksql);
SqlHelper helper_mssql = LocalSqlHelper.WH;
for (int i = 0; i < dt.Rows.Count; i++)
{
helper_mssql.Clear();
helper_mssql.Params.Add("id", Convert.ToInt32(dt.Rows[i]["store_id"]));
helper_mssql.Params.Add("supplier_name", dt.Rows[i]["store_name"]);
helper_mssql.Params.Add("serviceTel", dt.Rows[i]["tel"]);
helper_mssql.Params.Add("store_logo", dt.Rows[i]["store_logo"]);
helper_mssql.Insert("supplier");
string updatesql = "update ecm_store set sys_flag=1 where store_id=" + dt.Rows[i]["store_id"].ToString();
helper_mysql.Execute(updatesql);
}
}
public static void GetOrderInfo()
{
int payment_id = 0;
int warehouse_id = 0;
int seller_id = 0;
SqlHelper helper_mssql = LocalSqlHelper.WH;
string StoreIdSql = "select StoreId from WareHouse_List with(nolock) where StoreId>0";
DataTable StoreIdDt = helper_mssql.ExecDataTable(StoreIdSql);
string StoreIdStr = "-1";
for (int i = 0; i < StoreIdDt.Rows.Count; i++)
{
StoreIdStr += "," + StoreIdDt.Rows[i]["StoreId"].ToString();
}
SinoHelper2.MySqlHelper helper_mysql = PublicHelper.connMysql();
//判断该会员是否已经发放过
string checksql = "select a.*,ifnull(b.consignee,'') as consignee,ifnull(b.region_id,0) as region_id,ifnull(b.region_name,'') as region_name,ifnull(b.address,'') as address,ifnull(b.zipcode,'') as zipcode,ifnull(b.phone_tel,'') as phone_tel,ifnull(b.phone_mob,'') as phone_mob,ifnull(b.shipping_id,0) as shipping_id,ifnull(b.shipping_name,'') as shipping_name,ifnull(b.shipping_fee,0) as shipping_fee from ecm_order a left join ecm_order_extm b on a.order_id=b.order_id where a.seller_id in (" + StoreIdStr + ") and a.status in (20,30,40) and a.buyer_id<>4 and a.sys_flag=0 order by a.order_id";
DataTable dt = helper_mysql.ExecDataTable(checksql);
for (int i = 0; i < dt.Rows.Count; i++)
{
helper_mssql.Params.Clear();
helper_mssql.Params.Add("shopxpacid", Convert.ToInt32(dt.Rows[i]["order_id"]));
helper_mssql.Params.Add("userid", Convert.ToInt32(dt.Rows[i]["buyer_id"]));
helper_mssql.Params.Add("user_name", Convert.ToString(dt.Rows[i]["buyer_name"]));
seller_id = Convert.ToInt32(dt.Rows[i]["seller_id"]);
helper_mssql.Params.Add("store_id", seller_id);
warehouse_id = getWarehouseIdByStoreId(seller_id);
//若网上订单，则发货仓待定
if (warehouse_id == 4)
{
warehouse_id = 0;
}
helper_mssql.Params.Add("supplierid", warehouse_id);
helper_mssql.Params.Add("seller_name", dt.Rows[i]["seller_name"]);
helper_mssql.Params.Add("dingdan", dt.Rows[i]["order_sn"]);
helper_mssql.Params.Add("dingdan_type", 0);
helper_mssql.Params.Add("user_type", 0);
helper_mssql.Params.Add("freight", Convert.ToDouble(dt.Rows[i]["shipping_fee"]));
//helper_mssql.Params.Add("usertel", dt.Rows[i]["buyer_name"]);
helper_mssql.Params.Add("fapiao_title", dt.Rows[i]["buyer_name"]);
helper_mssql.Params.Add("shouhuoname", dt.Rows[i]["consignee"]);
string region_name = Convert.ToString(dt.Rows[i]["region_name"]);
string[] regions = region_name.Split(' ');
try
{
helper_mssql.Params.Add("province", regions[0]);
helper_mssql.Params.Add("city", regions[1]);
helper_mssql.Params.Add("xian", regions[2]);
}
catch
{
}
string phone_tel = Convert.ToString(dt.Rows[i]["phone_tel"]);
if (phone_tel.IsNullOrEmpty())
{
phone_tel = dt.Rows[i]["phone_mob"].ToString();
}
else
{
phone_tel += "/" + dt.Rows[i]["phone_mob"].ToString();
}
helper_mssql.Params.Add("usertel", phone_tel);
helper_mssql.Params.Add("shdz", dt.Rows[i]["address"]);
helper_mssql.Params.Add("liuyan", dt.Rows[i]["postscript"]);
helper_mssql.Params.Add("shopxp_shfs", 0);
int pay_time = Convert.ToInt32(dt.Rows[i]["pay_time"]) + 28800;
helper_mssql.Params.Add("fksj", PublicHelper.UnixTimeToTime(pay_time.ToString()));
payment_id = Convert.ToInt32(dt.Rows[i]["payment_id"]);
helper_mssql.Params.Add("zhifufangshi", payment_id);
int OrderStatus = 6;
if (payment_id == 99)
{
OrderStatus = 8;//实体店订单直接打单成功
helper_mssql.Params.Add("fhsj", PublicHelper.UnixTimeToTime(pay_time.ToString()));
}
helper_mssql.Params.Add("zhuangtai", OrderStatus);
helper_mssql.Params.Add("ShippingCompany", "noname");
helper_mssql.Insert("Direct_OrderMain");
string updatesql = "update ecm_order set sys_flag=1 where order_id=" + dt.Rows[i]["order_id"].ToString();
helper_mysql.Execute(updatesql);
}
checksql = "select a.*,b.order_sn,b.buyer_id,seller_id,b.payment_id from ecm_order_goods a left join ecm_order b on a.order_id=b.order_id where b.seller_id in (" + StoreIdStr + ") and b.status in (20,30,40) and a.sys_flag=0 order by a.rec_id";
dt = helper_mysql.ExecDataTable(checksql);
int p_quantity = 0;
string p_txm = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
warehouse_id = 0;
int return_order_id = Convert.ToInt32(dt.Rows[i]["return_order_id"]);
int StoreId = Convert.ToInt32(dt.Rows[i]["seller_id"]);
int needChangeStock = 1;
if (return_order_id > 0)
{
helper_mssql.Params.Clear();
//判断取消订单本地是否已有原单存在
DataTable checkReturnDt = helper_mssql.ExecDataTable("select top 1 shopxpacid,dingdan,supplierid from Direct_OrderMain where shopxpacid=" + return_order_id);
if (checkReturnDt.Rows.Count == 0)
{
needChangeStock = 0; //原单若不存在，不进行取消操作
}
else
{
//退单的本单直接对应原单
helper_mssql.Params.Clear();
warehouse_id = Convert.ToInt32(checkReturnDt.Rows[0]["supplierid"]);
helper_mssql.Params.Add("warehouse_id", warehouse_id);
helper_mssql.Params.Add("dingdan", Convert.ToString(dt.Rows[i]["order_sn"]));
string updateSql = "update Direct_OrderMain set supplierid=@warehouse_id where dingdan=@dingdan and supplierid=0";;
helper_mssql.Execute(updateSql);
}
}
if (needChangeStock == 1)
{
helper_mssql.Params.Clear();
p_quantity = Convert.ToInt32(dt.Rows[i]["quantity"]);
p_txm = Convert.ToString(dt.Rows[i]["goods_txm"]);
helper_mssql.Params.Add("id", Convert.ToInt32(dt.Rows[i]["rec_id"]));
helper_mssql.Params.Add("userid", Convert.ToInt32(dt.Rows[i]["buyer_id"]));
helper_mssql.Params.Add("supplierid", StorageHelper.getSupplierIdByTxm(p_txm));
helper_mssql.Params.Add("dingdan", dt.Rows[i]["order_sn"]);
helper_mssql.Params.Add("shopxpptid", Convert.ToInt32(dt.Rows[i]["goods_id"]));
helper_mssql.Params.Add("style_id", Convert.ToInt32(dt.Rows[i]["spec_id"]));
helper_mssql.Params.Add("shopxpptname", dt.Rows[i]["goods_name"]);
helper_mssql.Params.Add("p_size", dt.Rows[i]["specification"]);
helper_mssql.Params.Add("txm", p_txm);
helper_mssql.Params.Add("productcount", p_quantity);
helper_mssql.Params.Add("danjia", Convert.ToDouble(dt.Rows[i]["price"]));
helper_mssql.Params.Add("voucher", Convert.ToDouble(dt.Rows[i]["voucher_price"]));
helper_mssql.Params.Add("return_order_id", return_order_id);
helper_mssql.Insert("Direct_OrderDetail");
payment_id = Convert.ToInt32(dt.Rows[i]["payment_id"]);
//不是退换单的话判断所属仓库
if (warehouse_id == 0)
{
warehouse_id = getWarehouseIdByStoreId(StoreId);
}
//特殊处理,如果是这俩商家的商品,指向银川仓
if (warehouse_id == 4 && return_order_id == 0)
{
//int SupplierId = getSupplierId(Convert.ToInt32(dt.Rows[i]["goods_id"]));
//if (SupplierId == 11 || SupplierId == 18)
//{
// warehouse_id = 1;
//}
warehouse_id = 0;
}
if (warehouse_id > 0)
{
StorageHelper.changeStock(warehouse_id, p_txm, 0 - p_quantity, dt.Rows[i]["order_sn"].ToString(), "商品销售,订单号：" + dt.Rows[i]["order_sn"]);
}
}
string updatesql = "update ecm_order_goods set sys_flag=1 where rec_id =" + dt.Rows[i]["rec_id"].ToString();
helper_mysql.Execute(updatesql);
}
(new SynHelper()).SetOrderWarehouseByStock();
}