public static bool Insert_tbl_AdProduct(SqlSchema smp, NameValueCollection form)
{
//string sql = @"insert into [Gungnir].[dbo].[tbl_AdProduct]
// (advertiseid,pid,position,state,createdatetime,promotionprice,promotionnum,new_modelid)
// values(@advertiseid,@pid,@position,@state,@cdt,@promotionprice,@promotionnum,@new_modelid)";
if (form == null)
{
return(false);
}
string sql = @" declare @Count int
select @Count = count(1) from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
if(@Count = 0)
begin
insert into [Gungnir].[dbo].[tbl_AdProduct]
(advertiseid,pid,position,state,createdatetime,promotionprice,promotionnum,new_modelid)
values(@advertiseid,@pid,@position,@state,@cdt,@promotionprice,@promotionnum,@new_modelid)
end ";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par(form)
.Par("@cdt", DateTime.Now, SqlDbType.DateTime)
.ExecuteNonQuery() > 0);
}
public static void InsertVIN_REGION(string region)
{
SqlAdapter.Create("INSERT INTO VIN_REGION (region,isenable) VALUES(@region,@isenable)")
.Par("@region", region, SqlDbType.NVarChar)
.Par("@isenable", 0, SqlDbType.Int)
.ExecuteNonQuery();
}
public static bool Update_tbl_AdProduct(SqlSchema smp, NameValueCollection form, string id)
{
//string sql = @"update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
// lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
// promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid";
if (form == null && form["AdvertiseID"] == null)
{
return(false);
}
string sql = @"
declare @GetAdvertiseID varchar(256),@Count int
select @GetAdvertiseID = AdvertiseID from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
select @Count = count(1) from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
if(@Count = 0)
begin
update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid;
end
else if(@Count = 1 and @GetAdvertiseID = @advertiseid)
begin
update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid;
end
";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par(form)
.Par("@lastupdatedatetime", DateTime.Now)
.Par("@id", id, SqlDbType.VarChar, 256)
.ExecuteNonQuery() > 0);
}
public static DyModel GetVin_record(int ps, int pe)
{
return(SqlAdapter.Create("select * from (select (row_number() over(order by id)) as rownum,* from gungnir..vin_record) T where T.rownum between @start and @end order by createtime desc", CommandType.Text, "Aliyun")
.Par("@start", ps, SqlDbType.Int)
.Par("@end", pe, SqlDbType.Int)
.ExecuteModel());
}
public static bool IsRepeatVin_record(string phone, string vin)
{
return(SqlAdapter.Create("select top 1 * from gungnir..vin_record with(NOLOCK) where rphone=@phone and vin=@vin", CommandType.Text, "Aliyun")
.Par("@phone", phone, SqlDbType.NVarChar, 20)
.Par("@vin", vin, SqlDbType.NVarChar, 32)
.ExecuteModel().IsEmpty);
}
public static bool Delete_tal_newappsetdata(SqlSchema smAppModel, string id)
{
string sql = @"delete from gungnir..tal_newappsetdata where id=@id";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par("@id", id).ExecuteNonQuery() > 0);
}
public static DyModel Get_act_salemodule(SqlSchema smSaleModule, int parentid)
{
string sql = @"select * from gungnir..act_salemodule with(nolock) where parentid=@parentid order by sort";
return(SqlAdapter.Create(sql, smSaleModule, CommandType.Text, "Aliyun")
.Par("@parentid", parentid)
.ExecuteModel());
}
public static DyModel Get_tbl_AdProduct(SqlSchema smp, string mid)
{
string sql = @"select * from [Gungnir].[dbo].[tbl_AdProduct] where [new_modelid]=@new_modelid order by Position";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "Gungnir_AlwaysOnRead")
.Par("@new_modelid", mid)
.ExecuteModel());
}
public static int UpdateVin_record(string phone, string vin, string id)
{
return(SqlAdapter.Create("update gungnir..vin_record with (ROWLOCK) set rphone=@phone,vin=@vin where id=@id", CommandType.Text, "Aliyun")
.Par("@phone", phone, SqlDbType.NVarChar, 20)
.Par("@vin", vin, SqlDbType.NVarChar, 32)
.Par("@id", id, SqlDbType.UniqueIdentifier)
.ExecuteNonQuery());
}
public static bool Delete_tbl_AdProduct(SqlSchema smp, string mid, string id)
{
string sql = @"delete from [Gungnir].[dbo].[tbl_AdProduct] where pid=@id and new_modelid=@new_modelid";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par("@new_modelid", mid)
.Par("@id", id, SqlDbType.VarChar, 256).ExecuteNonQuery() > 0);
}
public static bool InsertVin_record(string id, string phone, string vin, string u)
{
return(SqlAdapter.Create("insert into gungnir..vin_record (id,rphone,vin,src,status) values(@id,@phone,@vin,@usr,1)", CommandType.Text, "Aliyun")
.Par("@id", id, SqlDbType.UniqueIdentifier)
.Par("@phone", phone, SqlDbType.NVarChar, 20)
.Par("@vin", vin, SqlDbType.NVarChar, 32)
.Par("@usr", u, SqlDbType.NVarChar, 50)
.ExecuteNonQuery() > 0);
}
public static DyModel Get_act_salemodule()
{
string sql = @"SELECT sm.mname ppname, sm.mstatus ppstatus, cm.mname pname, cm.mstatus pstatus,gm.*
FROM [Gungnir].[dbo].[act_salemodule] gm with(nolock)
left join gungnir..act_salemodule cm with(nolock) on gm.parentid=cm.id
left join gungnir..act_salemodule sm with(nolock) on cm.parentid=sm.id
order by parentid, sort";
return(SqlAdapter.Create(sql).ExecuteModel());
}
public static int Update_act_saleproduct(SqlSchema smSaleProd, NameValueCollection form)
{
string updsql = @"update gungnir..act_saleproduct set mid=@mid,pnum=@pnum,sort=@sort,name=@name,
lprice=@lprice,oprice=@oprice,plimit=@plimit,tlimit=@tlimit,quantityleft=@tlimit,
status=@status,updatetime=@updatetime,pimg=@pimg where id=@id";
return(SqlAdapter.Create(updsql, smSaleProd, CommandType.Text, "Aliyun")
.Par(form)
.Par("@updatetime", DateTime.Now.ToString())
.ExecuteNonQuery());
}
static void Test()
{
try {
var adapter = new SqlAdapter {
ConnectionString = connectionString
};
var book = new Book {
IsbnCode = "4774180947", Title = "C#プログラマーのための 基礎からわかるLINQマジック!"
};
adapter.Insert(book);
adapter.Delete(book);
adapter.Create <Author>();
adapter.Create <Writing>();
adapter.Drop <Author>();
adapter.Drop <Writing>();
}
catch (Exception ex) {
Console.WriteLine($"エラー: {ex.Message}");
}
}
public ActionResult Pinfo(string pnum)
{
var md = SqlAdapter.Create(@"SELECT * FROM Tuhu_productcatalog..[CarPAR_zh-CN] where ProductID + '|' + VariantID COLLATE Chinese_PRC_CI_AS=@pid", CommandType.Text, "Gungnir_AlwaysOnRead")
.Par("@pid", pnum, SqlDbType.NVarChar, 513)
.ExecuteModel();
if (!md.IsEmpty)
{
return(Content(md.Serialize(true)));
}
return(Content("{\"error\":true, \"Msg\":\"产品不存在\"}"));
}
public static int Insert_act_saleproduct(SqlSchema smSaleProd, NameValueCollection form)
{
string addsql = @"insert into gungnir..act_saleproduct
(mid,pnum,sort,name,lprice,oprice,plimit,tlimit,status,createtime,pimg,quantityleft)
values
(@mid,@pnum,@sort,@name,@lprice,@oprice,@plimit,@tlimit,@status,@createtime,@pimg,@tlimit)";
return(SqlAdapter.Create(addsql, smSaleProd, CommandType.Text, "Aliyun")
.Par(form)
.Par("@createtime", DateTime.Now.ToString())
.ExecuteNonQuery());
}
public static bool Insert_tal_newappsetdata(SqlSchema smAppModel, NameValueCollection form)
{
string sql = @"insert into gungnir..tal_newappsetdata
(apptype, modelname,modelfloor,showorder,icoimgurl,jumph5url,showstatic,starttime,overtime,cpshowtype,
cpshowbanner,appoperateval,operatetypeval,pronumberval,keyvaluelenth,umengtongji,createtime)
values(@apptype,@modelname,@modelfloor,@showorder,@icoimgurl,@jumph5url,@showstatic,@starttime,@overtime,@cpshowtype,
@cpshowbanner,@appoperateval,@operatetypeval,@pronumberval,@keyvaluelenth,@umengtongji,@createtime)";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par(form)
.Par("@createtime", DateTime.Now)
.ExecuteNonQuery() > 0);
}
public static bool Update_tal_newappsetdata(SqlSchema smAppModel, NameValueCollection form)
{
string sql = @"update gungnir..tal_newappsetdata set modelname=@modelname,showorder=@showorder,
icoimgurl=@icoimgurl,jumph5url=@jumph5url,showstatic=@showstatic,starttime=@starttime,
overtime=@overtime,cpshowtype=@cpshowtype,cpshowbanner=@cpshowbanner,appoperateval=@appoperateval,
operatetypeval=@operatetypeval,pronumberval=@pronumberval,keyvaluelenth=@keyvaluelenth,umengtongji=@umengtongji,
updatetime=@updatetime,Version=@Version,edittime=@edittime where id=@id";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par(form)
.Par("@updatetime", DateTime.Now)
.ExecuteNonQuery() > 0);
}
public ActionResult AllSectors()
{
var md = SqlAdapter.Create("select * FROM [Gungnir].[dbo].[act_salemodule] with(nolock) order by parentid, sort")
.ExecuteModel();
if (md.IsEmpty)
{
return(Content("{error:true,Msg:'暂无数据'}"));
}
var s = md.Serialize();
return(Content(s));
}
public static void Operate(int isEnable, int id, string region)
{
//改变状态
SqlAdapter.Create("UPDATE gungnir..VIN_REGION SET isenable=@isenable WHERE Id=@Id")
.Par("@isenable", isEnable, SqlDbType.Int)
.Par("@Id", id, SqlDbType.Int).ExecuteNonQuery();
//插入到记录表
SqlAdapter.Create("INSERT INTO gungnir..Vin_OnOffRecord(vinregionid,region,changetime,onoffstate) VALUES(@vinregionid,@region,@changetime,@onoffstate)")
.Par("@vinregionid", id, SqlDbType.Int)
.Par("@region", region, SqlDbType.NVarChar, 50)
.Par("@changetime", DateTime.Now, SqlDbType.DateTime)
.Par("@onoffstate", isEnable == 1 ? "启用" : "禁用", SqlDbType.NVarChar, 10).ExecuteNonQuery();
}
public ActionResult ProdRemove(int?id)
{
if (id.HasValue)
{
var n = SqlAdapter.Create("delete from gungnir..act_saleproduct where id=@id", smSaleProd, CommandType.Text, "Aliyun")
.Par("@id", id).ExecuteNonQuery();
if (n > 0)
{
return(Content(@"{""IsSuccess"":true}"));
}
return(Content(@"{""error"":true, ""Msg"":""删除失败""}"));
}
return(Content(@"{""error"":true, ""Msg"":""标识不存在""}"));
}
public ActionResult Sectors(int?pid)
{
var r = new ModelResult();
int parentid = pid.HasValue ? pid.Value : 0;
var md = SqlAdapter.Create("select * from gungnir..act_salemodule with(nolock) where parentid=@parentid order by sort", smSaleModule, CommandType.Text, "Aliyun")
.Par("@parentid", parentid)
.ExecuteModel();
if (md.IsEmpty)
{
r.Error("暂无数据");
}
r.Model = md;
return(Content(r.Model.Json(false)));
}
public ActionResult Prods(int?mid, string od = null)
{
if (mid.HasValue)
{
if (string.IsNullOrWhiteSpace(od))
{
od = "name";
}
var md = SqlAdapter.Create("select * from gungnir..act_saleproduct where mid=@mid order by sort")
.Par("@od", od, SqlDbType.NVarChar, 50)
.Par("@mid", mid.Value).ExecuteModel();
if (md.IsEmpty)
{
return(Content(@"{""error"":true,""Msg"":""暂无数据""}"));
}
var s = md.Serialize();
return(Content(s));
}
return(Content("{error:true,Msg:'Invalid parameter'}"));
}
//删除SaleModule节点
public ActionResult RemoveChilend(string id)
{
try
{
var chenlidenNod = SqlAdapter.Create(sqlsel, schema).Par("@parentid", id).ExecuteModel();
var parentidNodArray = chenlidenNod.DATA.AsEnumerable().Select(r => r["parentid"]).ToList();
var idArray = chenlidenNod.DATA.AsEnumerable().Select(r => r["id"]).ToList();
if (chenlidenNod.Count > 0)
{
SqlAdapter.Create("delete from gungnir..act_salemodule where parentid=@parentid", schema).Par("@parentid", parentidNodArray[0]).ExecuteNonQuery();
return(Remove(idArray[0].ToString()));
}
hastable.Add("resultCode", "1");
}
catch (Exception ex)
{
hastable.Add("resultCode", "0");
}
return(Content(hastable.ToJson().ToString()));
}
public ActionResult SaveProds(string a)
{
if (string.Equals(a, "1"))
{
var mva = SqlAdapter.Create("select top 1 pnum from gungnir..act_saleproduct with (nolock) where pnum like @pnum and mid=@mid", smSaleProd, CommandType.Text, "Aliyun")
.Par(Request.Form).ExecuteModel();
if (mva.IsEmpty)
{
//Request.Form;
string addsql = "insert into gungnir..act_saleproduct (mid,pnum,sort,name,lprice,oprice,plimit,tlimit,status,createtime,pimg,quantityleft) values(@mid,@pnum,@sort,@name,@lprice,@oprice,@plimit,@tlimit,@status,@createtime,@pimg,@tlimit)";
var n = SqlAdapter.Create(addsql, smSaleProd, CommandType.Text, "Aliyun")
.Par(Request.Form)
.Par("@createtime", DateTime.Now.ToString())
.ExecuteNonQuery();
Session["msg"] = n > 0 ? "添加成功" : "添加失败";
}
else
{
Session["msg"] = "该型号产品已存在";
}
}
else
{
var mvu = SqlAdapter.Create("select top 1 pnum from gungnir..act_saleproduct with (nolock) where pnum like @pnum and mid=@mid and id<>@id", smSaleProd, CommandType.Text, "Aliyun")
.Par(Request.Form).ExecuteModel();
if (mvu.IsEmpty)
{
string updsql = "update gungnir..act_saleproduct set mid=@mid,pnum=@pnum,sort=@sort,name=@name,lprice=@lprice,oprice=@oprice,plimit=@plimit,tlimit=@tlimit,quantityleft=@tlimit,status=@status,updatetime=@updatetime,pimg=@pimg where id=@id";
var n = SqlAdapter.Create(updsql, smSaleProd, CommandType.Text, "Aliyun")
.Par(Request.Form)
.Par("@updatetime", DateTime.Now.ToString())
.ExecuteNonQuery();
Session["msg"] = n > 0 ? "保存成功" : "保存失败";
}
else
{
Session["msg"] = "该型号产品已存在";
}
}
return(Redirect("/sale/saleproindex"));
}
public static DyModel Get_act_saleproduct(string od, int mid)
{
return(SqlAdapter.Create("select * from gungnir..act_saleproduct where mid=@mid order by sort")
.Par("@od", od, SqlDbType.NVarChar, 50)
.Par("@mid", mid).ExecuteModel());
}
public static int DeleteVin_recordById(string id)
{
return(SqlAdapter.Create("delete from gungnir..vin_record where id=@id", CommandType.Text, "Aliyun")
.Par("@id", id, SqlDbType.UniqueIdentifier)
.ExecuteNonQuery());
}
public static DyModel Get_tal_newappsetdata(SqlSchema smAppModel)
{
string sql = @"SELECT * FROM [Gungnir].[dbo].[tal_newappsetdata] order by apptype,modelfloor,showorder,modelname";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "Gungnir_AlwaysOnRead").ExecuteModel());
}
public ActionResult Save(string a)
{
hastable.Clear();
string mname = Request["mname"];
var _file = Request.Files;
long size = _file[0].ContentLength;
//文件类型
string type = _file[0].ContentType;
//文件名
string name = _file[0].FileName;
//文件格式
string _tp = System.IO.Path.GetExtension(name);
// else
// {
string _BImage = string.Empty;
string hiddenBanner = Request["bannerurl"];
if (size > 0)//修改的时候i，，如果没有上传图片，则获取隐藏url里面的插入到数据库
{
if (_tp.ToLower() == ".jpg" || _tp.ToLower() == ".jpeg" || _tp.ToLower() == ".gif" || _tp.ToLower() == ".png" || _tp.ToLower() == ".swf")
{
//获取文件流
System.IO.Stream stream = _file[0].InputStream;
_BImage = UploadFile(stream, name + type);
}
}
else//插入到七牛，并获取返回路径
{
_BImage = hiddenBanner;
}
int mstatus = 0;
int dispbanner = 0;
string banner = _BImage;
string requestMstatus = Request["mstatus"];
string requestDispbanner = Request["dispbanner"];
int id = Convert.ToInt32(Request["id"]);
DateTime stime = Convert.ToDateTime(Request["stime"]);
DateTime etime = Convert.ToDateTime(Request["etime"]);
int sort = Convert.ToInt32(Request["sort"]);
if (requestMstatus == "on")
{
mstatus = 1;
}
if (requestDispbanner == "on")
{
dispbanner = 1;
}
if ("1".Equals(a))
{
var x = SqlAdapter.Create("select count(*) from Gungnir..act_salemodule where mname=@mname", schema)
.Par("@mname", mname)
.ExecuteScalar();
if (Convert.ToInt32(x) > 0) //是否已存在此记录
{
hastable.Add("resultCode", "2"); //已存在此记录
}
else
{
var n = SqlAdapter.Create(sqlins, sqlinsschema)
.Par("@mname", mname)
.Par("@mstatus", mstatus)
.Par("@dispbanner", dispbanner)
.Par("@banner", banner)
.Par("@stime", stime)
.Par("@etime", etime)
.Par("@parentid", id)
.Par("@sort", sort)
.Par("@createtime", DateTime.Now)
.ExecuteNonQuery();
if (n > 0)
{
hastable.Add("resultCode", "1");
}
else
{
hastable.Add("resultCode", "0");
}
}
}
else
{
var n = SqlAdapter.Create(sqlupd, sqlupdschema)
.Par("@mname", mname)
.Par("@mstatus", mstatus)
.Par("@dispbanner", dispbanner)
.Par("@banner", banner)
.Par("@id", id)
.Par("@stime", stime)
.Par("@etime", etime)
.Par("@sort", sort)
.Par("@updatetime", DateTime.Now)
.ExecuteNonQuery();
if (n > 0)
{
hastable.Add("resultCode", "1");
//writeMsg("更新成功");
}
else
{
hastable.Add("resultCode", "0");
//writeMsg("更新失败 " + SqlAdapter.ErrorMsg);
}
}
// }
return(Content(hastable.ToJson().ToString()));
}
public ActionResult Remove(string id)
{
SqlAdapter.Create(sqldel, schema)
.Par("@id", id).ExecuteNonQuery();
return(RemoveChilend(id));
}
