/// <summary>
/// 获取API订单总条数
/// </summary>
/// <returns></returns>
public int getDataCount()
{
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
int i = context.apiOrderDetails.Count();
return(i);
}
/// <summary>
/// 存储编辑信息 Product主款号表
/// </summary>通过款号对styledescript进行修改 存储信息为产品描述
public int Updatestyledescript(string style, string productDescript, int UserId)
{
try
{
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var check = context.styledescript.Where(a => a.style == style).FirstOrDefault();
if (check != null)//判断是否已存描述信息 存在则update 不存在则insert
{
var q = context.styledescript.Where(a => a.style == style);
foreach (var item in q)
{
item.productDescript = productDescript; //描述
item.UserId = UserId; //操作人
}
context.SubmitChanges();
}
else
{
model.styledescript m = new model.styledescript()
{
style = style, //款号
productDescript = productDescript, //描述
UserId = UserId, //操作人
};
context.styledescript.InsertOnSubmit(m);
context.SubmitChanges();
}
return(1);
}
catch (Exception ex)
{
return(-1);
}
}
/// <summary>
/// 更新季节
/// </summary>
/// <returns></returns>
public string UpdateSeason(string Id, string Cat1, int UserId)
{
string s = string.Empty;
try
{
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var Check = context.Season.Where(a => a.Cat1 == Cat1);
var q = context.Season.Where(a => a.Id == Convert.ToInt32(Id));
if (Check.Count() == 0)
{
foreach (var i in q)
{
i.Cat1 = Cat1;
}
context.SubmitChanges();
s = "修改成功!";
}
else
{
s = "该季节已存在!";
}
}
catch (Exception ex)
{
s = "修改失败!";
}
return(s);
}
/// <summary>
/// 保存编辑客户信息
/// </summary>
/// <returns></returns>
public string SaveEdit(Dictionary <string, string> Dic, int UserId)
{
string s = string.Empty;
int? item = null;
try
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.custom.Where(a => a.Id == Convert.ToInt32(Dic["Id"]));
foreach (var i in q)
{
//i.CustomerId = Dic["CustomerId"];
i.Shop = Dic["Shop"];
i.Contactperson = Dic["Contactperson"];
i.Sex = Dic["Sex"] == "" ? item : Convert.ToInt32(Dic["Sex"]);
i.Age = Dic["Age"] == "" ? item : Convert.ToInt32(Dic["Age"]);
i.Birthday = Dic["Birthday"];
i.IDNumber = Dic["IDNumber"];
i.Telephone = Dic["Telephone"];
i.Phone = Dic["Phone"];
i.Weixin = Dic["Weixin"];
i.QQNo = Dic["QQNo"];
i.CustomerLevel = Dic["CustomerLevel"];
i.Remark = Dic["Remark"];
i.CustomerServiceId = UserId;
}
context.SubmitChanges();
s = "修改成功!";
}
catch (Exception ex)
{
s = "修改失败!";
}
return(s);
}
/// <summary>
/// 添加季节
/// </summary>
/// <returns></returns>
public string AddSeason(string Cat1, int UserId)
{
string s = string.Empty;
try
{
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.Season.Where(a => a.Cat1 == Cat1);
if (q.Count() == 0)
{
model.Season ms = new model.Season()
{
Cat1 = Cat1,//季节
};
context.Season.InsertOnSubmit(ms);
context.SubmitChanges();
s = "添加成功!";
}
else
{
s = "该季节已存在!";
}
}
catch (Exception ex)
{
s = "添加失败!";
}
return(s);
}
/// <summary>
/// 返回订单详情信息(根据主订单编号查询子订单)
/// </summary>
/// <param name="orderId"></param>
/// <param name="hg">海关</param>
/// <returns></returns>
public List <model.apiOrderDetails> getOrderDetailsMsg(string orderId, string hg)
{
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
List <model.apiOrderDetails> list = context.apiOrderDetails.Where(c => c.orderId == orderId).ToList();
return(list);
}
/// <summary>
/// 添加客户地址
/// </summary>
/// <returns></returns>
public string SaveAddAddress(Dictionary <string, string> Dic, int UserId)
{
string s = string.Empty;
try
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
model.customAddress mc = new model.customAddress()
{
CustomerId = Dic["CustomerId"], //客户Id
Provinces = Dic["Provinces"], //所在省
City = Dic["City"], //所在城市
District = Dic["District"], //所在地区
CusAddress = Dic["CusAddress"], //具体地址
CustomerServiceId = UserId, //处理客服
UserId = UserId, //操作人
};
context.customAddress.InsertOnSubmit(mc);
context.SubmitChanges();
s = "添加成功!";
}
catch (Exception ex)
{
s = "添加失败!";
}
return(s);
}
/// <summary>
/// 查看货号属性 选货
/// </summary>
/// <param name="scode"></param>
/// <param name="vencode"></param>
/// <returns></returns>
public DataTable LookAttrByCheck(string scode, string vencode)
{
pbxdatasourceDataContext pddc = new pbxdatasourceDataContext(connectionString);
IDataParameter[] ipr = new IDataParameter[]
{
new SqlParameter("sql", ""),
new SqlParameter("scode", scode),
new SqlParameter("Vencode", vencode),
};
try
{
DataTable dt = Select(ipr, "GetLookAttrByCheck");
dt.Columns.Add("TypeNo");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["TypeNo"] = dt.Rows[i]["Cat2"];
var info = pddc.producttype.Where(a => a.TypeNo == dt.Rows[i]["Cat2"].ToString());
foreach (var temp in info)
{
dt.Rows[i]["Cat2"] = temp.TypeName;
}
}
return(dt);
}
catch (Exception)
{
return(null);
throw;
}
}
public string mytest()
{
try
{
string s = string.Empty;
//FileStream fs = new FileStream(Server.MapPath("~/Uploadxls/test.txt"), FileMode.OpenOrCreate);
//StreamWriter sw = new StreamWriter(fs);
////开始写入
//sw.Write("Hello World!!!!");
////清空缓冲区
//sw.Flush();
////关闭流
//sw.Close();
//fs.Close();
DataTable dt = new DataTable();
pbxdatasourceDataContext con = new pbxdatasourceDataContext();
var q = con.custom.Where(a => a.Id > 0);
dt = LinqToDataTable.LINQToDataTable(q);
s = Excell11(dt);
return(s);
}
catch (Exception ex)
{
return(ex.ToString());
}
}
//******************7.6
/// <summary>
/// 根据excel内容添加对应的品牌
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public string InsertBrand(string [] str)
{
pbxdatasourceDataContext pdc = new pbxdatasourceDataContext(connectionString);
List <BrandVen> info = pdc.BrandVen.Where(a => a.Vencode == str[3] && a.BrandNameVen == str[2]).ToList();//查询当前添加的品牌是否已经存在
if (info.Count > 0)
{
return("当前数据源已存在此品牌！");
}
else
{
try
{
IDataParameter[] ipr = new IDataParameter[]
{
new SqlParameter("BrandName", str[0]),
new SqlParameter("BrandAbridge", str[1]),
new SqlParameter("BrandNameVen", str[2]),
new SqlParameter("Vencode", str[3]),
};
return(Add(ipr, "InsertBrandVencode"));
}
catch (Exception)
{
return("添加失败！");
throw;
}
}
}
/// <summary>
/// 返回(批量)订单信息(查询主订单表当前订单编号的列（所有数据）)
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public List <model.apiOrder> getOrderMsg(string[] orderIds)
{
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
List <model.apiOrder> list = context.apiOrder.Where(c => orderIds.Contains(c.orderId)).ToList();
return(list);
}
/// <summary>
/// 得到以某个字母开头的权限品牌个数
/// </summary>
/// <param name="BeginChar"></param>
/// <returns></returns>
public int GetBrandBeginCount(string BeginChar, string vencode, string roleId)
{
pbxdatasourceDataContext pddc = new pbxdatasourceDataContext(connectionString);
List <BrandConfig> list = pddc.BrandConfig.Where(a => a.Def1 == vencode && a.PersonaId == roleId).ToList();
return(list.Count);
}
/// <summary>
/// 查询客户信息
/// </summary>
/// <returns></returns>
public DataTable GetDate(Dictionary <string, string> Dic, int page, int Selpages, out string counts)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = from c in context.custom select c;
if (Dic["CustomerId"] != "")//客户Id
{
q = q.Where(a => a.CustomerId == Dic["CustomerId"]);
}
if (Dic["Shop"] != "")//客户来源
{
q = q.Where(a => a.Shop == Dic["Shop"]);
}
if (Dic["Sex"] != "")//性别
{
q = q.Where(a => a.Sex == Convert.ToInt32(Dic["Sex"]));
}
if (Dic["CustomerLevel"] != "")//客户等级
{
q = q.Where(a => a.CustomerLevel == Dic["CustomerLevel"]);
}
counts = q.Count().ToString(); //返回查询数量
dt = LinqToDataTable.LINQToDataTable(q.Skip((page - 1) * Selpages).Take(Selpages)); //翻页
return(dt);
}
/// <summary>
/// 编辑客户地址
/// </summary>
/// <returns></returns>
public string SaveEditAddress(Dictionary <string, string> Dic, int UserId)
{
string s = string.Empty;
try
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.customAddress.Where(a => a.Id == Convert.ToInt32(Dic["Id"]));
foreach (var i in q)
{
i.Provinces = Dic["Provinces"];
i.City = Dic["City"];
i.District = Dic["District"];
i.CusAddress = Dic["CusAddress"];
i.CustomerServiceId = UserId;
i.UserId = UserId;
}
context.SubmitChanges();
s = "保存成功!";
}
catch (Exception ex)
{
s = "编辑失败!";
}
return(s);
}
/// <summary>
/// 得到角色当前数据源的其他（首字母在A-Z之外）品牌
/// </summary>
/// <param name="Role"></param>
/// <param name="vencode"></param>
/// <returns></returns>
public string[] GetUserBrandName(string Role, string vencode)
{
pbxdatasourceDataContext pddc = new pbxdatasourceDataContext(connectionString);
try
{
List <BrandConfig> list = pddc.BrandConfig.Where(a => a.Def1 == vencode && a.PersonaId == Role).ToList();
string[] strall = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
for (int i = 0; i < strall.Length; i++)
{
list = list.Where(a => a.BrandName.StartsWith(strall[i]) == false).ToList();//排除首字母A-Z
}
string[] str = new string[list.Count];
for (int i = 0; i < list.Count; i++)
{
str[i] = list[i].BrandId;
}
return(str);
}
catch (Exception)
{
return(null);
throw;
}
}
/// <summary>
/// 获取所有数据源（供应商）
/// </summary>
/// <returns></returns>
public DataTable getSource()
{
DataTable dt = new DataTable();
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = from c in context.productsource orderby c.SourceLevel descending select c.SourceLevel;
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 查询客户信息
/// </summary>
/// <returns></returns>
public DataTable GetDate(string Id)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = context.custom.Where(a => a.Id == Convert.ToInt32(Id));//通过id查询客户信息
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 根据品牌简称获取全称
/// </summary>
/// <param name="brandName">品牌简称</param>
/// <returns></returns>
public string getBrandFullName(string brandName)
{
string s = string.Empty;
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
brand MdBrand = new brand();
s = (from c in context.brand where c.BrandAbridge == brandName select c).SingleOrDefault().BrandName;
return(s);
}
/// <summary>
/// 得到对应货号的价格
/// </summary>
/// <param name="scode"></param>
/// <param name="plaseNo"></param>
/// <returns></returns>
public string GetPlantProductPrice(string scode, string plaseNo)
{
pbxdatasourceDataContext pdc = new pbxdatasourceDataContext(connctionstring);
List <PlantProductStock> info = pdc.PlantProductStock.Where(a => a.PlaseNo == plaseNo && a.Scode == scode).ToList();
string str;
str = info[0].Pricea.ToString() + "❤" + info[0].Priceb.ToString() + "❤" + info[0].Pricec.ToString() + "❤" + info[0].Priced.ToString() + "❤" + info[0].Pricee.ToString();
return(str);
}
/// <summary>
/// 显示客户地址信息
/// </summary>
/// <returns></returns>
public DataTable CustomerAddress(string CustomerId)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = context.customAddress.Where(a => a.CustomerId == CustomerId);
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 根据货号返回VIP价格（报关）
/// </summary>
/// <param name="scode"></param>
/// <returns></returns>
public string getPriceA(string scode)
{
string s = string.Empty;
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = (from c in context.productstock where c.Scode == scode select c).SingleOrDefault();
s = p == null ? "0" : p.Pricec.ToString();
return(s);
}
/// <summary>
/// 根据货号返回已售库存(库存查product表)
/// </summary>
/// <param name="scode"></param>
/// <returns></returns>
public int getScodeBalanceSales(string scode)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
model.product p = (from c in context.product where c.Scode == scode select c).SingleOrDefault();
int i = p == null ? 0 : int.Parse(p.Def3.ToString());
return(i);
}
/// <summary>
/// 根据主订单获取子订单，并对其子订单进行查询，返回库存和供应商和价格。(库存查productstock表)
/// </summary>
/// <param name="scode">货号</param>
/// <param name="isParentOrder"></param>
/// <returns></returns>
public DataTable getScodeBalance(string scode, bool isParentOrder)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = from c in context.productstock where c.Scode == scode select new { vencode = c.Vencode, balance = c.Balance, price = c.Pricee };
//var p = from c in context.product where c.Scode == scode select new { vencode = c.Vencode, balance = c.Balance, price = c.Pricee };
DataTable dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 根据季节名称查询季节表
/// </summary>
/// <returns></returns>
public DataTable GetData(string Cat1)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.Season.Where(a => a.Cat1.Contains(Cat1));
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 供应商类别搜索
/// </summary>
public DataTable SearchBrandDDlist(string BrandName)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.brand.Where(a => a.BrandName.Contains(BrandName) || a.BrandAbridge.Contains(BrandName));
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 根据主订单获取子订单详情
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public DataTable getOrderDetailsMsg(string orderId)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = (from c in context.apiOrderDetails where c.orderId == orderId select c).ToList();
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 返回所有API订单
/// </summary>
/// <returns></returns>
public DataTable getOrderMsg(int pageIndex, int pageSize)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = (from c in context.apiOrder select c).Skip((pageIndex - 1) * pageSize).Take(pageSize);
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 得到角色类别
/// </summary>
/// <param name="vencode"></param>
/// <param name="roleId"></param>
/// <returns></returns>
public string[] GetBrandRole(string vencode, string roleId)
{
pbxdatasourceDataContext pddc = new pbxdatasourceDataContext(connectionString);
List <BrandConfig> list = pddc.BrandConfig.Where(a => a.Def1 == vencode && a.PersonaId == roleId).ToList();
string [] str = new string [list.Count];
for (int i = 0; i < list.Count; i++)
{
str[i] = list[i].BrandId.ToString();
}
return(str);
}
/// <summary>
/// 导入excell中的客户信息到custom表
/// </summary>
/// <returns></returns>
public string UploadExcel()
{
try
{
string s = "导入成功!";
DataTable dt = new DataTable();
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
HttpPostedFileBase file1 = Request.Files[0];
string newFile = DateTime.Now.ToString("yyyyMMddHHmmss");
string filePath = newFile + file1.FileName; //生成新名字
file1.SaveAs(Server.MapPath("~/Uploadxls/" + filePath)); //存到本地
dt = GetExcellData(Server.MapPath("~/Uploadxls/" + filePath), "Sheet1$").Tables[0]; //把excel转换成datatable
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() != "")
{
int check = context.custom.Where(a => a.CustomerId == dt.Rows[i][0].ToString()).Count();//查询是否存在
if (check > 0)
{
s += dt.Rows[i][0] + ":已存在<br />";
}
else
{
custom cu = new custom()
{
CustomerId = dt.Rows[i][0].ToString(),
Shop = dt.Rows[i][1].ToString(),
Contactperson = dt.Rows[i][2].ToString(),
Sex = dt.Rows[i][3].ToString() == "女" ? 0 : 1,
Age = Convert.ToInt32(dt.Rows[i][4].ToString()),
Birthday = Convert.ToDateTime(dt.Rows[i][5].ToString()).ToString("yyyy-MM-dd"),
IDNumber = dt.Rows[i][6].ToString(),
Telephone = dt.Rows[i][7].ToString(),
Phone = dt.Rows[i][8].ToString(),
Weixin = dt.Rows[i][9].ToString(),
QQNo = dt.Rows[i][10].ToString(),
CustomerLevel = dt.Rows[i][11].ToString(),
Remark = dt.Rows[i][12].ToString(),
CustomerServiceId = Convert.ToInt32(dt.Rows[i][13].ToString()),
};
context.custom.InsertOnSubmit(cu);
context.SubmitChanges();
}
}
}
return(s);
}
catch (Exception ex)
{
return("导入失败!");
}
}
/// <summary>
/// 订单是否已拆单
/// </summary>
/// <returns></returns>
public string getOrderSplit(string detailsOrderId)
{
string s = string.Empty;
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
model.apiOrderDetails p = (from c in context.apiOrderDetails where c.detailsSplit == decimal.One && c.detailsOrderId == detailsOrderId select c).FirstOrDefault();
if (p != null)
{
s = "订单已分配,请勿重复分配";
}
return(s);
}
