/// <summary>
/// XXXX年度各产品销售汇总表
/// </summary>
/// <param name="countryName">CountryName</param>
/// <param name="year">Year</param>
/// <returns></returns>
public static DataTable SummaryOfAllSalesByProduct(string countryName, string year)
{
SqlParameter[] sparams = new SqlParameter[]
{
new SqlParameter("@countryName", SqlDbType.NVarChar, 40),
new SqlParameter("@year", SqlDbType.VarChar, 40)
};
sparams[0].Value = countryName;
sparams[1].Value = year;
return(DBHelper.ExecuteDataTable("Pro_SummaryOfAllSalesByProduct", sparams, CommandType.StoredProcedure));
}
public DataTable GetExtendTreeView_newAz(string number, string tree, int leyerNumber, int excempt, int type)
{
SqlParameter[] para =
{
new SqlParameter("@BH", number),
new SqlParameter("@qishu", excempt),
new SqlParameter("@TREE", ""),
new SqlParameter("@TREE12", tree),
new SqlParameter("@CS", leyerNumber),
new SqlParameter("@type", type)
};
return(DBHelper.ExecuteDataTable("JS_TreeNet1", para, CommandType.StoredProcedure));
}
/// <summary>
/// 获取菜单个数
/// </summary>
/// <param name="type">菜单类型</param>
/// <param name="pid">父菜单编号</param>
/// <returns>返回菜单个数</returns>
public static DataTable GetLeftMenuNameDT(int pid)
{
string field = System.Web.HttpContext.Current.Session["LanguageCode"].ToString();
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@pid", pid);
StringBuilder sql = new StringBuilder();
sql.Append("select t." + field + " as menuName,m.menuFile,m.id,m.isfold from menuleft m,t_translation t where m.id = t.primarykey and t.tablename = 'menuleft' and m.parentID=@pid and m.isfold=1 order by m.sortid");
DataTable dt = DBHelper.ExecuteDataTable(sql.ToString(), paras, CommandType.Text);
return(dt);
}
public DataTable GetExtendTreeView_newAz1(string number, string tree, int leyerNumber, int excempt, int type, int cw)
{
SqlParameter[] para =
{
new SqlParameter("@ID", number),
new SqlParameter("@TREE", tree),
new SqlParameter("@ISAZ", 1),
new SqlParameter("@CS", leyerNumber),
new SqlParameter("@ExpectNum", excempt),
new SqlParameter("@storeID", "")
};
return(DBHelper.ExecuteDataTable("JS_TreeNet", para, CommandType.StoredProcedure));
}
/// <summary>
/// 获取店铺换货列表
/// </summary>
/// <returns></returns>
public IList <ReplacementModel> GetReplacementlList(int pageIndex, string key,
int pageSize, string table, string columns, string condition, out int recordCount, out int pageCount)
{
IList <ReplacementModel> list = new List <ReplacementModel>();
recordCount = 0;
pageCount = 0;
SqlParameter[] parm0 = { new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@table", SqlDbType.VarChar, 1000),
new SqlParameter("@columns", SqlDbType.NVarChar, 2000),
new SqlParameter("@condition", SqlDbType.NVarChar, 2000),
new SqlParameter("@key", SqlDbType.VarChar, 50),
new SqlParameter("@RecordCount", SqlDbType.Int),
new SqlParameter("@PageCount", SqlDbType.Int) };
parm0[0].Value = pageIndex;
parm0[1].Value = pageSize;
parm0[2].Value = table;
parm0[3].Value = columns;
parm0[4].Value = condition;
parm0[5].Value = key;
parm0[6].Value = recordCount;
parm0[7].Value = pageCount;
parm0[6].Direction = System.Data.ParameterDirection.Output;
parm0[7].Direction = System.Data.ParameterDirection.Output;
DataTable dt = DBHelper.ExecuteDataTable("GetCustomersDataPage", parm0, CommandType.StoredProcedure);
recordCount = Convert.ToInt32(parm0[6].Value);
pageCount = Convert.ToInt32(parm0[7].Value);
foreach (DataRow item in dt.Rows)
{
ReplacementModel replacementModel = new ReplacementModel((int)item["id"]);
list.Add(replacementModel);
replacementModel.StoreID = (string)item["StoreID"];
replacementModel.RefundmentOrderID = (string)item["RefundmentOrderID"];
replacementModel.StoreOrderID = (string)item["StoreOrderID"];
replacementModel.ExpectNum = (int)item["ExpectNum"];
replacementModel.StateFlag = item["StateFlag"].ToString(); //(int )item["StateFlag"];
replacementModel.CloseFlag = item["CloseFlag"].ToString(); // (int)item["CloseFlag"];
//replacementModel.InGoodsMoney = 444;// Convert.ToDouble(item["ingoodsmoney"]);
//replacementModel.OutGoodsMoney = 333;// Convert.ToDouble(item["outgoodmoney"]);
replacementModel.MakeDocDate = (DateTime)item["MakeDocDate"];
}
return(list);
}
/// <summary>
/// 根据新的要货编号获得老订单号
/// </summary>
/// <param name="fahuoOrder">要货单号</param>
/// <returns></returns>
public static DataTable GetOrderGoodsNoteTable(string fahuoOrder)
{
string strSql = @"select StoreID,ordergoodsid,ExpectNum,IsCheckOut,fahuoorder,
OrderType,InceptPerson,city.country,city.province,city.city,
InceptAddress,PostalCode,TotalMoney,TotalPV,Telephone,Weight,Carriage
,OrderDateTime,Description from ordergoods so left outer join city on so.cpccode=city.cpccode
where fahuoOrder = @fahuoOrder";
SqlParameter[] parm = new SqlParameter[]
{
new SqlParameter("@fahuoOrder", fahuoOrder)
};
return(DBHelper.ExecuteDataTable(strSql, parm, CommandType.Text));
}
/// <summary>
/// 电子转账 查询
/// </summary>
/// <param name="number"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataTable GetMember(string number, string name)
{
string sql = "select Number,Name,Bank,BankCard,Jackpot-ECTPay-membership-Out as TransferMoney,ReleaseMoney from MemberInfo where Jackpot-ECTPay-membership-Out>0 ";
if (number != string.Empty)
{
sql = sql + " and Number='" + number + "'";
}
if (name != string.Empty)
{
sql = sql + " and Name='" + name + "'";
}
return(DBHelper.ExecuteDataTable(sql, CommandType.Text));
}
/// <summary>
/// 增加退货单时需要显示指定店铺库存情况
/// cyb
/// </summary>
/// <param name="storeid"></param>
/// <returns></returns>
public static DataTable GetStrockSByStoreid(string storeid)
{
string sql = @"Select P.productID,p.productcode, P.productName ,P.bigProductUnitID ,(K.TotalIn-K.TotalOut) as MaxCount,
(Select ProductUnitName From ProductUnit Where ProductUnitID = P.bigProductUnitID) as BigUnitName ,
P.smallProductUnitID,
(Select ProductUnitName From ProductUnit Where ProductUnitID = P.smallProductUnitID) as SmallUnitName ,
P.BigSmallMultiPle ,
PreferentialPrice ,PreferentialPV From Stock as K ,Product as P
Where K.ProductID=P.ProductID And (K.TotalIn-K.TotalOut)>0 And P.isFold=0 And K.StoreId=@num" ;
SqlParameter[] spa = new SqlParameter[] { new SqlParameter("@num", SqlDbType.NVarChar, 50) };
spa[0].Value = storeid;
return(DBHelper.ExecuteDataTable(sql, spa, CommandType.Text));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetCardRange(string condition, string mark)
{
List <VIPCardRangeModel> list = new List <VIPCardRangeModel>();
string sql = "select id,RangeID,StoreID,BeginCard,EndCard,inuse,CASE inuse WHEN 1 THEN '已分配' ELSE '未分配' END AS _inuse from VIPCardRange where 1=1";
if (mark == "LIKE")
{
sql = sql + " and StoreID like '%" + condition + "%'";
}
else
{
sql = sql + " and StoreID = '" + condition + "'";
}
return(DBHelper.ExecuteDataTable(sql, CommandType.Text));
}
public static DataTable GetDtBYPv(double pv)
{
DataTable dt = null;
object pvId = DBHelper.ExecuteScalar("select isnull(Id,-1) as id from SetGivePV where " + pv + " -totalpvStart>=0 and totalpvEnd-" + pv + ">=0");
if (pvId != null)
{
if (pvId.ToString() != "-1")
{
dt = DBHelper.ExecuteDataTable("select g.productId,productCode,productName,productImage,g.price,g.pv,g.productQuantity,totalPrice,totalPv from product p,GiveProduct g where g.productId=p.productId and setgivepvid=" + pvId.ToString());
}
}
return(dt);
}
/// <summary>
/// 获取支付方式
/// </summary>
/// <returns>返回支付方式</returns>
public static DataTable GetPayment(int isStore)
{
string field = System.Web.HttpContext.Current.Session["LanguageCode"].ToString();
// string strSql = "select t." + field + " as paymentName,p.payID,p.availability,p.id from payment p,T_translation t where t.primarykey = p.id and t.tableName = 'payment' and p.isStore=@isStore";
string strSql = "select paymentName,p.payID,p.availability,p.id from payment p where p.isStore=@isStore";
SqlParameter[] para =
{
new SqlParameter("@isStore", SqlDbType.Int)
};
para[0].Value = isStore;
DataTable dt = DBHelper.ExecuteDataTable(strSql, para, CommandType.Text);
return(dt);
}
/// <summary>
/// 查询所有单据类型详细
/// </summary>
/// <returns></returns>
public IList <DocTypeDetailModel> GetDocTypeDetailAll()
{
DataTable table = DBHelper.ExecuteDataTable("GetDocTypeDetailAll", CommandType.StoredProcedure);
IList <DocTypeDetailModel> list = new List <DocTypeDetailModel>();
foreach (DataRow row in table.Rows)
{
DocTypeDetailModel doc = new DocTypeDetailModel();
doc.DocTypeID = Convert.ToInt32(row["DocTypeID"]);
doc.SubDocTypeName = row["SubDocTypeName"].ToString();
doc.SubID = Convert.ToInt32(row["SubID"]);
list.Add(doc);
}
return(list);
}
/// <summary>
/// 获取语言翻译信息
/// </summary>
/// <param name="columnNames">列名</param>
/// <param name="tableName">表名</param>
/// <param name="conditions">查询条件</param>
/// <param name="key">排序关键字</param>
/// <returns>返回DataTable对象</returns>
public static DataTable GetTranToNInfoByCondition(string columnNames, string tableName, string conditions, string key)
{
SqlParameter[] sparams = new SqlParameter[]
{
new SqlParameter("@columnNames", SqlDbType.NVarChar, 200),
new SqlParameter("@tableName", SqlDbType.NVarChar, 100),
new SqlParameter("@conditions", SqlDbType.NVarChar, 200),
new SqlParameter("@key", SqlDbType.NVarChar, 20)
};
sparams[0].Value = columnNames;
sparams[1].Value = tableName;
sparams[2].Value = conditions;
sparams[3].Value = key;
return(DBHelper.ExecuteDataTable("GetTranToNInfoByCondition", sparams, CommandType.StoredProcedure));
}
public DataTable GetExtendTreeView_NewTj(string number, string tree, int leyerNumber, int excempt, int type)
{
SqlParameter[] para =
{
new SqlParameter("@BH", number),
new SqlParameter("@qishu", excempt),
new SqlParameter("@TREE", ""),
new SqlParameter("@TREE12", tree),
new SqlParameter("@CS", leyerNumber),
new SqlParameter("@type", type)
};
DataTable dt = DBHelper.ExecuteDataTable("JS_TreeNet2_New", para, CommandType.StoredProcedure);
int count = dt.Rows.Count;
return(dt);
}
/// <summary>
/// 订单明细
/// </summary>
/// <param name="storeid"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public static DataTable GetOrderMingXi(string storeid, string beginTime, string endTime)
{
SqlParameter[] parm =
{
new SqlParameter("@Storeid", SqlDbType.VarChar),
new SqlParameter("@BeginDate", SqlDbType.DateTime),
new SqlParameter("@EndDate", SqlDbType.DateTime)
};
parm[0].Value = storeid;
parm[1].Value = beginTime;
parm[2].Value = endTime;
DataTable dt = DBHelper.ExecuteDataTable("d_order_SumDetail", parm, CommandType.StoredProcedure);
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="rbtn_typeSelectedIndex"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <param name="ddl_itemSelectedValue"></param>
/// <returns></returns>
public static ArrayList ConstructData_II(string condition)
{
int top = 9;
bool need = true;
decimal other = 0;
string column = "";
string result = "";
DataTable table = new DataTable();
ArrayList coll = new ArrayList();
SqlParameter[] param =
{
new SqlParameter("@Storeid", SqlDbType.VarChar)
};
param[0].Value = condition;
column = "ProductName";
table = DBHelper.ExecuteDataTable("D_Kucun_getData", param, CommandType.StoredProcedure);
result = "TotalEnd";
int rows = table.Rows.Count;
DataView dv = table.DefaultView;
dv.Sort = result + "" + " Desc";
if (top >= dv.Count)
{
need = false;
top = dv.Count;
}
for (int i = 0; i < top; i++)
{
coll.Add(new Item(dv[i][column].ToString(), Convert.ToDecimal(dv[i][result].ToString())));
}
if (need)
{
for (int i = top; i < dv.Count; i++)
{
other = other + Convert.ToDecimal(dv[i][result].ToString());
}
coll.Add(new Item("其它", other));
}
return(coll);
}
/// <summary>
/// 获取支付方式
/// </summary>
/// <returns>返回获取支付方式</returns>
public static void GetPaymentType2(DropDownList ddl, int isStore)
{
string field = System.Web.HttpContext.Current.Session["LanguageCode"].ToString();
string strSql = "select p.payId,paymentName,p.id from payment p where isStore=@isStore and p.availability=1";
SqlParameter[] para =
{
new SqlParameter("@isStore", SqlDbType.Int)
};
para[0].Value = isStore;
DataTable dt = DBHelper.ExecuteDataTable(strSql, para, CommandType.Text);
ddl.DataSource = dt;
ddl.DataTextField = "paymentName";
ddl.DataValueField = "payId";
ddl.DataBind();
}
/// <summary>
/// 获取扣补款信息
/// </summary>
/// <param name="expct"></param>
/// <param name="deduct"></param>
/// <param name="mark"></param>
/// <param name="search"></param>
/// <returns></returns>
public DataTable GetMember(int expct, int deduct, string mark, string search)
{
string sql = "select MemberInfo.Number,MemberInfo.Name,Deduct.ID,Deduct.DeductMoney,Deduct.DeductReason,Deduct.ExpectNum,case when Deduct.IsDeduct = 0 then '扣款' else '补款' end as IsDeduct,KeyinDate,Deduct.OperateNum from MemberInfo,Deduct where Deduct.Number=MemberInfo.Number";
if (expct != -1)
{
sql = sql + " and Deduct.ExpectNum=" + expct;
}
if (deduct != -1)
{
sql = sql + " and IsDeduct =" + deduct;
}
if (mark != string.Empty && search != string.Empty)
{
sql = sql + " and " + mark + " like '%" + search + "%' ";
}
return(DBHelper.ExecuteDataTable(sql, CommandType.Text));
}
/// <summary>
/// 订单汇总--产品
/// </summary>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public static DataTable GetOrderCollect_II(string beginTime, string endTime)
{
DataTable dt = null;
SqlParameter[] param =
{
new SqlParameter("@BeginDate", SqlDbType.DateTime),
new SqlParameter("@EndDate", SqlDbType.DateTime),
new SqlParameter("@qishu", SqlDbType.Int)
};
param[0].Value = beginTime;
param[1].Value = endTime;
param[2].Value = 0;
dt = DBHelper.ExecuteDataTable("D_OrderDetail_getData", param, CommandType.StoredProcedure);
return(dt);
}
/// <summary>
/// 返回合单表DataTable
/// </summary>
/// <returns></returns>
public static DataTable GetUniteDoc(string condition)
{
// string cmd = @"select so.StoreID,ud.UniteDocID,ud.DocIDs,ud.UniteDocTime,ud.Weight,ud.Carriage,ud.Remark,
// so.InceptPerson,so.InceptAddress,so.PostalCode,so.Telephone,so.ConveyanceMode,so.ConveyanceCompany,
// wh.WareHouseName,case so.OrderType when 1 then '周转货' when 0 then '正常订货' end as OrderType,
// so.ForeCastArriveDateTime from dbo.UniteDoc ud
// left outer join StoreOrder so on substring(ud.DocIDs,1,charindex(',',ud.DocIDs)-1)=so.OutStorageOrderID
// left outer join InventoryDoc ity on so.OutStorageOrderID=ity.DocID
// left outer join WareHouse wh on wh.WareHouseID=ity.WareHouseID
// where " + condition;
string cmd = @"select so.StoreID,ud.UniteDocID,ud.DocIDs,ud.UniteDocTime,ud.Weight,ud.Carriage,ud.Remark,
so.InceptPerson,so.InceptAddress,so.PostalCode,so.Telephone,so.ConveyanceMode,so.ConveyanceCompany,
case so.OrderType when 1 then '周转货' when 0 then '正常订货' end as OrderType,
so.ForeCastArriveDateTime from dbo.UniteDoc ud
left outer join StoreOrder so on substring(ud.DocIDs,1,charindex(',',ud.DocIDs)-1)=so.OutStorageOrderID where " + condition + " order by ud.UniteDocTime desc";
return(DBHelper.ExecuteDataTable(cmd));
}
//返回产品仓库明细
public static DataTable ProductStockDetail_Company_Store(string productID, string flag)
{
DataTable dt = null;
SqlParameter[] param =
{
new SqlParameter("@ProductId", SqlDbType.VarChar)
};
param[0].Value = productID;
if (flag == "Company")
{
dt = DBHelper.ExecuteDataTable("ProductQuantity_getdatabyproduct", param, CommandType.StoredProcedure);
}
else if (flag == "Store")
{
dt = DBHelper.ExecuteDataTable("S_Stock_getDatabyproductid", param, CommandType.StoredProcedure);
}
return(dt);
}
/// <summary>
/// 获取会员结算信息
/// ExpectNum:期数 Level：级别 MemberInfoBalanceN：结算表 Number：编号
/// </summary>
/// <returns></returns>
public DataTable GetMemberInfoBalance(string ExpectNum, string Level, string MemberInfoBalanceN, string Number, params double[] Currency)
{
string sql = string.Empty;
if (Currency.Length == 0)
{
sql = @"select '" + ExpectNum + "' as qishu,h1.Number,h2.Name,'" + Level + "' as strJiBie," +
"isnull(h1.TotalNetNum,0) TotalNetNum,isnull(h1.CurrentNewNetNum,0) CurrentNewNetNum,isnull(h1.CurrentTotalNetRecord,0) CurrentTotalNetRecord,isnull(h1.TotalNetRecord,0) TotalNetRecord,isnull(h1.CurrentOneMark,0) CurrentOneMark,isnull(h1.TotalOneMark,0) TotalOneMark," +
"isnull(h1.Bonus0,0) as jiangjin0," +
"isnull(h1.Bonus1,0) as jiangjin1," +
"isnull(h1.Bonus2,0) as jiangjin2," +
"isnull(h1.Bonus3,0) as jiangjin3," +
"isnull(h1.Bonus4,0) as jiangjin4," +
"isnull(h1.Bonus5,0) as jiangjin5," +
"isnull(h1.CurrentTotalMoney,0) as zongji," +
"isnull(h1.DeductTax,0) as koushui," +
"isnull(h1.DeductMoney,0) as koukuan," +
"isnull(h1.CurrentSolidSend,0) as shifa," +
"isnull(h1.BonusAccumulation,0) as zongji_lj," +
"isnull(h1.SolidSendAccumulation,0) as shifa_lj " +
"from " + MemberInfoBalanceN + " h1,MemberInfo h2 where h1.Number=h2.Number and h1.Number='" + Number + "'";
}
else
{
sql = @"select '" + ExpectNum + "' as qishu,h1.Number,h2.Name,'" + Level + "' as strJiBie," +
"isnull(h1.TotalNetNum,0) TotalNetNum,isnull(h1.CurrentNewNetNum,0) CurrentNewNetNum,isnull(h1.CurrentTotalNetRecord,0) CurrentTotalNetRecord,isnull(h1.TotalNetRecord,0) TotalNetRecord,isnull(h1.CurrentOneMark,0) CurrentOneMark,isnull(h1.TotalOneMark,0) TotalOneMark," +
"" + Currency[0] + "*isnull(h1.Bonus0,0) as jiangjin0," +
"" + Currency[0] + "*isnull(h1.Bonus1,0) as jiangjin1," +
"" + Currency[0] + "*isnull(h1.Bonus2,0) as jiangjin2," +
"" + Currency[0] + "*isnull(h1.Bonus3,0) as jiangjin3," +
"" + Currency[0] + "*isnull(h1.Bonus4,0) as jiangjin4," +
"" + Currency[0] + "*isnull(h1.Bonus5,0) as jiangjin5," +
"" + Currency[0] + "*isnull(h1.CurrentTotalMoney,0) as zongji," +
"" + Currency[0] + "*isnull(h1.DeductTax,0) as koushui," +
"" + Currency[0] + "*isnull(h1.DeductMoney,0) as koukuan," +
"" + Currency[0] + "*isnull(h1.CurrentSolidSend,0) as shifa," +
"" + Currency[0] + "*isnull(h1.BonusAccumulation,0) as zongji_lj," +
"" + Currency[0] + "*isnull(h1.SolidSendAccumulation,0) as shifa_lj " +
"from " + MemberInfoBalanceN + " h1,MemberInfo h2 where h1.Number=h2.Number and h1.Number='" + Number + "'";
}
return(DBHelper.ExecuteDataTable(sql));
}
/// <summary>
/// 获取城市信息
/// </summary>
/// <param name="cpccode"></param>
/// <returns></returns>
public static CityModel GetCityInfoByCPCCode(string cpccode)
{
DataTable dt = DBHelper.ExecuteDataTable(" select top 1 ID,CPCCode,Country,Province,City,Xian,PostCode from City where CPCCode='" + cpccode + "'", CommandType.Text);
if (dt != null && dt.Rows.Count > 0)
{
CityModel city = new CityModel();
city.Id = Convert.ToInt32(dt.Rows[0]["ID"]);
city.CPCCode = dt.Rows[0]["CPCCode"].ToString();
city.Country = dt.Rows[0]["Country"].ToString();
city.Province = dt.Rows[0]["Province"].ToString();
city.City = dt.Rows[0]["City"].ToString();
city.Xian = dt.Rows[0]["Xian"].ToString();
city.Postcode = dt.Rows[0]["PostCode"].ToString();
return(city);
}
return(null);
}
/// <summary>
/// 获取会员注册报单信息
/// </summary>
/// <param name="storeId">店编号</param>
/// <param name="expectNum">期数</param>
/// <param name="condition">根据字段</param>
/// <param name="symbol">查询符号</param>
/// <param name="character">条件</param>
/// <returns></returns>
public static DataTable QueryDeclaration2(string storeId, string condition, string symbol, string character, int expectNum)
{
DataTable dt = new DataTable();
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@storeId", storeId),
new SqlParameter("@symbol", symbol),
new SqlParameter("@character", character),
new SqlParameter("@expectNum", expectNum),
new SqlParameter("@condition", condition),
//new SqlParameter("@defraystate",SqlDbType.Int),
//new SqlParameter("@isAgin",SqlDbType.Int),
//new SqlParameter("@IsReceivables",SqlDbType.Int)
};
dt = DBHelper.ExecuteDataTable("P_GetMemberRepeatConsume", param, CommandType.StoredProcedure);
return(dt);
}
public static DataSet GetAllList(string productid)
{
string sql = "";
if (productid != "")
{
string pid = productid + ",";
string pidstr = productid + ",";
while (1 == 1)
{
DataTable dt = DBHelper.ExecuteDataTable("select productid from product where pid in (" + pidstr.Substring(0, pidstr.Length - 1).ToString() + ") and issell=0");
pidstr = "";
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
pidstr = pidstr + dt.Rows[i][0].ToString() + ",";
pid = pid + dt.Rows[i][0].ToString() + ",";
}
}
else
{
break;
}
}
sql = @"select productcode,ProductID,ProductName,CommonPrice,CommonPV,PreferentialPrice,PreferentialPV,Description,ProductImage,
currency.name as currency from Product,currency where Product.currency=currency.id and Product.isfold=0 and Product.pid in (" + pid.Substring(0, pid.Length - 1).ToString() + ") and Product.issell=0 and (Product.Yongtu=0 or Product.Yongtu=2)";
}
else
{
sql = @"select productcode,ProductID,ProductName,CommonPrice,CommonPV,PreferentialPrice,PreferentialPV,Description,ProductImage,
currency.name as currency from Product,currency where Product.currency=currency.id and Product.isfold=0 and Product.issell=0 and (Product.Yongtu=0 or Product.Yongtu=2)";
}
SqlDataAdapter da = DBHelper.ExecuteDataAdapter(sql, CommandType.Text);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
/// <summary>
/// 产品销售明细
/// </summary>
/// <returns></returns>
public static DataTable GetSellMingXi(string beginTime, string endTime, string productID, string wareHouseID, string depotSeatID)
{
SqlParameter[] param =
{
new SqlParameter("@BeginDate", SqlDbType.DateTime),
new SqlParameter("@EndDate", SqlDbType.DateTime),
new SqlParameter("@Productid", SqlDbType.VarChar),
new SqlParameter("@WareHouseid", SqlDbType.Int),
new SqlParameter("@DepotSeatID", SqlDbType.Int)
};
param[0].Value = beginTime;
param[1].Value = endTime;
param[2].Value = productID;
param[3].Value = wareHouseID;
param[4].Value = depotSeatID;
DataTable dt = DBHelper.ExecuteDataTable("Opda_docDetail_getDetail", param, CommandType.StoredProcedure);
return(dt);
}
public static List <CompanyBankModel> GetCompanyBanks()
{
List <CompanyBankModel> list = new List <CompanyBankModel>();
//按国家查询公司账户信息
string sql = "select * from companybank order by id ";
DataTable dt = DBHelper.ExecuteDataTable(sql, CommandType.Text);
CompanyBankModel company = null;
foreach (DataRow dr in dt.Rows)
{
company = new CompanyBankModel();
company.ID = int.Parse(dr["ID"].ToString());
company.Bank = dr["Bank"].ToString();
company.CountryID = int.Parse(dr["countryID"].ToString());
company.BankBook = dr["BankBook"].ToString();
company.Bankname = dr["bankname"].ToString();
list.Add(company);
}
return(list);
}
//得到换货信息用于编辑页面中绑定表单
public DataTable GetRemplacementTable(string storeId, string displaceOrderId)
{
String sqlStr = "select a.id from country a,storeinfo b where a.countrycode=substring(b.SCPCCode,1,2) and b.storeid=@num";
SqlParameter sPara = new SqlParameter("@num", SqlDbType.NVarChar, 50);
sPara.Value = storeId;
string Country = Convert.ToString(DBHelper.ExecuteScalar(sqlStr, sPara, CommandType.Text));
string sql = "Select P.productID, IsNull((Select ActualStorage From Stock Where ProductID=P.ProductID And StoreID=@StoreID ),0) as Quantity,IsNull((Select OutQuantity From ReplacementDetail Where ProductID=P.ProductID And DisplaceOrderID=@OrderID ),0) as OutQuantity,IsNull((Select InQuantity From ReplacementDetail Where ProductID=P.ProductID And DisplaceOrderID=@OrderID ),0) as InQuantity,P.productName ,P.bigProductUnitID ,(Select ProductUnitName From ProductUnit Where ProductUnitID = P.bigProductUnitID) as BigUnitName ,P.smallProductUnitID,(Select ProductUnitName From ProductUnit Where ProductUnitID = P.smallProductUnitID) as SmallUnitName ,P.BigSmallMultiPle ,PreferentialPrice ,PreferentialPV from product as P where isFold=0 ";
SqlParameter[] para =
{
new SqlParameter("@StoreID", SqlDbType.VarChar, 20),
new SqlParameter("@OrderID", SqlDbType.VarChar, 20)
};
para[0].Value = storeId;
para[1].Value = displaceOrderId;
return(DBHelper.ExecuteDataTable(sql, para, CommandType.Text));
}
/// <summary>
/// 获取支付方式——ds2012——tianfeng
/// </summary>
/// <returns>返回获取支付方式</returns>
public static void GetPaymentType(RadioButtonList rbt, int isStore)
{
string field = System.Web.HttpContext.Current.Session["LanguageCode"].ToString();
string strSql = "select p.payId,paymentName from payment p where p.isStore=@isStore and availability=1";
SqlParameter[] para =
{
new SqlParameter("@isStore", SqlDbType.Int)
};
para[0].Value = isStore;
DataTable dt = DBHelper.ExecuteDataTable(strSql, para, CommandType.Text);
rbt.DataSource = dt;
rbt.DataTextField = "paymentName";
rbt.DataValueField = "payId";
rbt.DataBind();
if (dt.Rows.Count > 0)
{
rbt.Items[0].Selected = true;
}
}
/// <summary>
/// 调网
/// </summary>
/// <param name="tran"></param>
/// <param name="number">编号</param>
/// <param name="old">原位置</param>
/// <param name="newbh">新位置</param>
/// <param name="isAzTj">0：推荐；1：安置</param>
/// <param name="qishu">期数</param>
public static int ExecuteUpdateNet(string number, string old, string newbh, int isAzTj, int qishu, int newqushu, string opreateBH, DateTime adjustdate)
{
SqlParameter[] parm2 = { new SqlParameter("@Number", SqlDbType.VarChar, 30),
new SqlParameter("@old", SqlDbType.VarChar, 30),
new SqlParameter("@new", SqlDbType.VarChar, 30),
new SqlParameter("@IsAz", SqlDbType.Bit, 2),
new SqlParameter("@qishu", SqlDbType.Int),
new SqlParameter("@newqushu", SqlDbType.Int),
new SqlParameter("@opreateBH", SqlDbType.VarChar, 50),
new SqlParameter("@adjustdate", SqlDbType.DateTime) };
parm2[0].Value = number;
parm2[1].Value = old;
parm2[2].Value = newbh;
parm2[3].Value = isAzTj;
parm2[4].Value = qishu;
parm2[5].Value = newqushu;
parm2[6].Value = opreateBH;
parm2[7].Value = adjustdate;
return(DBHelper.ExecuteDataTable("js_UpdateNet_w", parm2, CommandType.StoredProcedure).Rows.Count);
}
