public int update(string sp_dm, string name, string lei_bie, string dan_wei, string shou_huo, string gong_huo, string id)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "UPDATE Yh_JinXiaoCun_jichuziliao SET sp_dm = '" + sp_dm + "',name = '" + name + "',lei_bie = '" + lei_bie + "' ,dan_wei = '" + dan_wei + "' ,shou_huo = '" + shou_huo + "' ,gong_huo = '" + gong_huo + "' WHERE id = '" + id + "'";
return(sen.Database.ExecuteSqlCommand(sql));
}
}
public int delete(int id)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "DELETE FROM Yh_JinXiaoCun_jichuziliao WHERE id = '" + id + "'";
return(sen.Database.ExecuteSqlCommand(sql));
}
}
public List <yh_jinxiaocun_mingxi> ri_qi_select(string time_qs, string time_jz, string gs_name)
{
using (ServerEntities sen = new ServerEntities()) {
string sql = "SELECT * FROM Yh_JinXiaoCun_mingxi WHERE shijian between '" + time_qs + "' and '" + time_jz + "' and gs_name = '" + gs_name + "'";
var result = sen.Database.SqlQuery <yh_jinxiaocun_mingxi>(sql);
return(result.ToList());
}
}
public List <yh_jinxiaocun_mingxi> ming_xi_select(string gs_name, int limit1, int limit2)
{
using (ServerEntities sen = new ServerEntities()) {
string sql = "select * from yh_jinxiaocun_mingxi where zh_name = '" + gs_name + "' limit " + limit1 + "," + limit2;
var result = sen.Database.SqlQuery <yh_jinxiaocun_mingxi>(sql);
return(result.ToList());
}
}
public int del_mingxi(int cpid)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "DELETE FROM yh_jinxiaocun_mingxi WHERE _id = '" + cpid + "'";
return(sen.Database.ExecuteSqlCommand(sql));;
}
}
public List <yh_jinxiaocun_zhengli> getList_chaxun(string gongsi, string name)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "select * from yh_jinxiaocun_zhengli where gs_name = '" + gongsi + "' and name like '%" + name + "%'";
var result = sen.Database.SqlQuery <yh_jinxiaocun_zhengli>(sql);
return(result.ToList());
}
}
public List <yh_jinxiaocun_mingxi> getShouHuoMingXi(string gsname)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "select shou_h from yh_jinxiaocun_mingxi where shou_h != '' and gs_name = '" + gsname + "' group by shou_h";
var result = sen.Database.SqlQuery <yh_jinxiaocun_mingxi>(sql);
return(result.ToList());
}
}
public List <yh_jinxiaocun_mingxi> getCpNames(string gongsi)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "select cpname from yh_jinxiaocun_mingxi where gs_name = '" + gongsi + "' group by cpname";
var result = sen.Database.SqlQuery <yh_jinxiaocun_mingxi>(sql);
return(result.ToList());
}
}
public int getPageCount(string gs_name)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "SELECT * FROM Yh_JinXiaoCun_mingxi where zh_name = '" + gs_name + "'";
var result = sen.Database.SqlQuery <yh_jinxiaocun_mingxi>(sql);
return(result.ToList().Count);
}
}
public List <yh_jinxiaocun_qichushu> getQiChu(string gs_name)
{
using (ServerEntities sen = new ServerEntities())
{
var gsParam = new MySqlParameter("@gs", gs_name);
string sql = "select * from yh_jinxiaocun_qichushu where gs_name = @gs";
var result = sen.Database.SqlQuery <yh_jinxiaocun_qichushu>(sql, gsParam);
return(result.ToList());
}
}
public int add_qichu(List <qi_chu_info> qci)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "";
foreach (qi_chu_info item in qci)
{
sql += "insert into Yh_JinXiaoCun_qichushu(_openid,cpid,cpjg,cpjj,cplb,cpname,cpsj,cpsl,mxtype,shijian,zh_name,gs_name) values ('" + item.Openid + "','" + item.Cpid + "','" + item.Cpjg + "','" + item.Cpjj + "','" + item.Cplb + "','" + item.Cpname + "','" + item.Cpsj + "','" + item.Cpsl + "','" + item.Mxtype + "','" + item.Shijian + "','" + item.zh_name + "','" + item.gs_name + "');";
}
return(sen.Database.ExecuteSqlCommand(sql));
}
}
public int add(List <yh_jinxiaocun_jichuziliao> list)
{
using (ServerEntities sen = new ServerEntities())
{
string sql = "";
foreach (yh_jinxiaocun_jichuziliao item in list)
{
sql += "insert into Yh_JinXiaoCun_jichuziliao(sp_dm,name,lei_bie,dan_wei,shou_huo,gong_huo,zh_name,gs_name) values ('" + item.sp_dm + "','" + item.name + "','" + item.lei_bie + "','" + item.dan_wei + "','" + item.shou_huo + "','" + item.gong_huo + "','" + item.zh_name + "','" + item.gs_name + "');";
}
return(sen.Database.ExecuteSqlCommand(sql));
}
}
public List <yh_jinxiaocun_jichuziliao> getList_chaxun(string gongsi, string name)
{
//List<zl_and_jc_info> list = new List<zl_and_jc_info>();
using (ServerEntities sen = new ServerEntities())
{
//var gongsiParam = new MySqlParameter("@gongsi", gongsi);
string sql = "select * from yh_jinxiaocun_jichuziliao as j where gs_name ='" + gongsi + "' and `name` like '%" + name + "%' ";
var result = sen.Database.SqlQuery <yh_jinxiaocun_jichuziliao>(sql);
return(result.ToList());
}
}
public List <yh_jinxiaocun_jichuziliao> getList(string gongsi)
{
List <zl_and_jc_info> list = new List <zl_and_jc_info>();
using (ServerEntities sen = new ServerEntities())
{
var gongsiParam = new MySqlParameter("@gongsi", gongsi);
string sql = "select * from yh_jinxiaocun_jichuziliao as j where gs_name = @gongsi";
var result = sen.Database.SqlQuery <yh_jinxiaocun_jichuziliao>(sql, gongsiParam);
return(result.ToList());
}
}
public List <JinChuZiLiaoItem> getOutStockDetail(string gongsi)
{
List <zl_and_jc_info> list = new List <zl_and_jc_info>();
using (ServerEntities sen = new ServerEntities())
{
var gongsiParam = new MySqlParameter("@gongsi", gongsi);
string sql = "select *,IFNULL((select sum(CASE mxtype WHEN '出库' THEN cpsl ELSE (cpsl*-1) END) as cpsl from yh_jinxiaocun_mingxi where cpname = j.name and gs_name = @gongsi),0) as maxNum from yh_jinxiaocun_jichuziliao as j where gs_name = @gongsi";
var result = sen.Database.SqlQuery <JinChuZiLiaoItem>(sql, gongsiParam);
return(result.ToList());
}
}
public List <MingXiItem> getCpMingXi(string cpname, string gongsi)
{
using (ServerEntities sen = new ServerEntities())
{
var @params = new MySqlParameter[] {
new MySqlParameter("@cpname", cpname),
new MySqlParameter("@gongxi", gongsi)
};
string sql = "select mx.shijian,mx.orderid,mx.sp_dm,mx.cpname,ifnull(ruku.cpsl,0) as ruku_num,ifnull(ruku.cp_price,0) as ruku_price,ifnull(chuku.cpsl,0) as chuku_num,ifnull(chuku.cp_price,0) as chuku_price from yh_jinxiaocun_mingxi as mx left join (select orderid,sum(cpsl) as cpsl,sum(cpsl*cpsj) as cp_price from yh_jinxiaocun_mingxi where mxtype = '入库' and gs_name = @gongxi group by orderid) as ruku on mx.orderid = ruku.orderid left join (select orderid,sum(cpsl) as cpsl,sum(cpsl*cpsj) as cp_price from yh_jinxiaocun_mingxi where mxtype = '出库' and gs_name = @gongxi group by orderid) as chuku on mx.orderid = chuku.orderid where mx.gs_name = @gongxi GROUP BY mx.orderid having mx.cpname = @cpname";
var result = sen.Database.SqlQuery <MingXiItem>(sql, @params);
return(result.ToList());
}
}
