private bool Save()
{
bool flag = false;
if (lblDanh.txt.Text.Length == 0)
{
MessageBox.Show("缸号需要填写");
lblDanh.txt.Focus();
return(flag);
}
if (lblYWY.cobodgv.Text.Length == 0)
{
MessageBox.Show("操作人员必须填写");
lblYWY.cobodgv.txtbox.Focus();
return(flag);
}
//if (lblCW.cobodgv.Text.Length == 0)
//{
// MessageBox.Show("生产工序必须填写");
// lblCW.cobodgv.txtbox.Focus();
// return flag;
//}
try
{
var all = db.Queryable <LCKA>().Where(it => it.liushuihao == lblDanh.txt.Text).First();
if (all == null)
{
MessageBox.Show("这没有发现此缸号重新选择");
return(flag);
}
var all1 = db.Queryable <BaseIList>()
.Where(it => it.leibie == LEIBIE && it.dingdanhao == lblDanh.txt.Text /*&& it.item0 == lblYWY.cobodgv.Text && it.item1 == lblCW.cobodgv.Text*/)
.First();
if (all1 != null)
{
MessageBox.Show("该缸号<>报价已登记");
return(flag);
}
var sd = new BaseIList()
{
leibie = LEIBIE,
dingdanhao = lblDanh.txt.Text,
pingmin = lblTxt1.cobodgv.Text,
seming = lblTxt2.cobodgv.Text,
sehao = lblZPS.cobodgv.Text,
item0 = lblYWY.cobodgv.Text,//操作人员
pishu = lblPS.txt.Text,
//item1 = lblCW.cobodgv.Text,//工序
zongliang = lblZZ.txt.Text,
item2 = lblPIH.txt.Text,//百分比
riqi = DateTime.Now,
item3 = lblBZ1.txt.Text,
kehu = lblKH.cobodgv.Text,
};
db.Insertable <BaseIList>(sd).ExecuteCommand();
flag = true;
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message);
flag = false;
}
return(flag);
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Insertable Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
var insertObj = new Order()
{
Id = 1, Name = "order1", Price = 0
};
var insertObjs = new List <Order> {
new Order()
{
Id = 11, Name = "order11", Price = 0
},
new Order()
{
Id = 12, Name = "order12", Price = 0
}
};
//Ignore CreateTime
db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity
db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity();
//Only insert Name and Price
db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity();
db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity();
//ignore null columns
db.Insertable(insertObjs).ExecuteCommand();//get change row count
//Use Lock
db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand();
insertObjs = new List <Order> {
new Order()
{
Id = 11, Name = "order11", Price = 1
},
new Order()
{
Id = 12, Name = "order12", Price = 20, CreateTime = DateTime.Now, CustomId = 1
}
};
db.Insertable(insertObjs).UseSqlServer().ExecuteBlueCopy();
db.CodeFirst.InitTables <RootTable0, TwoItem, TwoItem2, TwoItem3>();
db.CodeFirst.InitTables <ThreeItem2>();
db.DbMaintenance.TruncateTable("RootTable0");
db.DbMaintenance.TruncateTable("TwoItem");
db.DbMaintenance.TruncateTable("TwoItem2");
db.DbMaintenance.TruncateTable("TwoItem3");
db.DbMaintenance.TruncateTable("ThreeItem2");
Console.WriteLine("SubInsert Start");
db.Insertable(new Order()
{
Name = "订单 1",
CustomId = 1,
Price = 100,
CreateTime = DateTime.Now,
Id = 0,
Items = new List <OrderItem>()
{
new OrderItem()
{
CreateTime = DateTime.Now,
OrderId = 0,
Price = 1,
ItemId = 1
},
new OrderItem()
{
CreateTime = DateTime.Now,
OrderId = 0,
Price = 2,
ItemId = 2
}
}
})
.AddSubList(it => it.Items.First().OrderId).ExecuteReturnPrimaryKey();
db.Insertable(new List <RootTable0>()
{
new RootTable0()
{
Name = "aa",
TwoItem2 = new TwoItem2()
{
Id = "1",
ThreeItem2 = new List <ThreeItem2>()
{
new ThreeItem2()
{
Name = "a", TwoItem2Id = "1"
},
new ThreeItem2()
{
Id = 2, Name = "a2", TwoItem2Id = "2"
}
}
},
TwoItem = new TwoItem()
{
Name = "itema",
RootId = 2
},
TwoItem3 = new List <TwoItem3>()
{
new TwoItem3()
{
Id = 0, Name = "a", Desc = ""
},
}
},
new RootTable0()
{
Name = "bb",
TwoItem2 = new TwoItem2()
{
Id = "2"
},
TwoItem = new TwoItem()
{
Name = "itemb",
RootId = 2,
},
TwoItem3 = new List <TwoItem3>()
{
new TwoItem3()
{
Id = 1, Name = "b", Desc = ""
},
new TwoItem3()
{
Id = 2, Name = "b1", Desc = "1"
},
}
}
})
.AddSubList(it => it.TwoItem.RootId)
.AddSubList(it => new SubInsertTree()
{
Expression = it.TwoItem2.RootId,
ChildExpression = new List <SubInsertTree>()
{
new SubInsertTree()
{
Expression = it.TwoItem2.ThreeItem2.First().TwoItem2Id
}
}
})
.AddSubList(it => it.TwoItem3)
.ExecuteReturnPrimaryKey();
SubNoIdentity(db);
SubIdentity(db);
Console.WriteLine("#### Insertable End ####");
}
/// <summary>
/// 采购明细汇总
/// </summary>
/// <param name="condition"></param>
/// <param name="res"></param>
/// <returns></returns>
public static async Task GetPurchaseDetailPageAsync(SqlSugarClient Db)
{
Db.CodeFirst.InitTables<EGoods, EGoodsBrand, EGoodsClass>();
Db.CodeFirst.InitTables<EOrderAlbaran, EOrderAlbaranDetail, EOrderReturn, EOrderReturnDetail>();
//入库单
Db.Insertable(new EOrderAlbaranDetail()
{
AlbaranId = 1,
BarCode = "a",
ChineseName = "a",
Commentary = "a",
CostPrice = 1,
DelFlag = "a",
Discount = 1,
GoodsBrandId = 1,
GoodsBrandName = "a",
GoodsClassId = 1,
GoodsClassName = "a",
GoodsNo = "a",
Id = new Random().Next(0, 99999999),
Index = 1,
IsBlanceBarCode = true,
IsProhibitedChangeDiscount = true,
IsTemporary = true,
Iva = 1,
IvaId = 1,
Number = 1,
OrderId = 1,
PackageAmount = 1,
Price = 1,
PurchaseSpec = "",
Remark = "a",
Req = 1,
SpanishName = "a",
TenantId = 1,
Total = 1,
UnitName = "a"
}).ExecuteCommand();
var query1 = Db.Queryable<EOrderAlbaranDetail, EOrderAlbaran, EGoods, EGoodsClass, EGoodsBrand>((d, h, g, c, b) => new JoinQueryInfos(
JoinType.Left, h.AlbaranId == d.AlbaranId,
JoinType.Left, d.GoodsNo == g.GoodsNo,
JoinType.Left, g.GoodsClassId == c.GoodsClassId,
JoinType.Left, g.BrandId == b.BrandId
))
.Where((d, h, g, c, b) => h.AlbaranType == null) //固定入库单
.Select((d, h, g, c, b) => new PurchaseDetailModel
{
EntryTime = h.EntryTime,
No = h.AlbaranNo,
Type = 1,
GoodsNo = d.GoodsNo,
GoodsName = g.SpanishName,
GoodsCategory = c.SpanishName,
BrandName = b.BranchSpanishName,
Number = d.Number,
Price = d.Price
});
//退货单
var query2 = Db.Queryable<EOrderReturnDetail, EOrderReturn, EGoods, EGoodsClass, EGoodsBrand>((d, h, g, c, b) => new JoinQueryInfos(
JoinType.Left, h.OrderReturnId == d.OrderReturnId,
JoinType.Left, d.GoodsNo == g.GoodsNo,
JoinType.Left, g.GoodsClassId == c.GoodsClassId,
JoinType.Left, g.BrandId == b.BrandId
)).Where((d, h, g, c, b) => h.OrderReturnType == 1) //固定入库单
.Select((d, h, g, c, b) => new PurchaseDetailModel
{
EntryTime = h.EntryTime,
No = h.OrderReturnNo,
Type = 2,
GoodsNo = d.GoodsNo,
GoodsName = g.SpanishName,
GoodsCategory = c.SpanishName,
BrandName = b.BranchSpanishName,
Number = d.Number,
Price = d.Price
});
SqlSugar.RefAsync<int> totalNum = 0;
var res2 = await query2.Clone().ToPageListAsync(1, 2, totalNum);
var res = await Db.UnionAll(query1, query2).ToPageListAsync(1, 2, totalNum);
var q = Db.Queryable<Order>().Where(it => it.Id == 1).Select(it => new { id=1});
var q2 = q.Clone();
var x = q.ToList();
var y = q2.ToList();
//var res = await Db.UnionAll<PurchaseDetailModel>(query1.Clone(), query2.Clone()).ToPageListAsync(1, 10, totalNum);
}
/// <summary>
/// 添加返回自增列ID
/// </summary>
/// <param name="t">实例</param>
/// <returns></returns>
public int Add <T>(T t) where T : class, new()
{
return(db.Insertable(t).ExecuteReturnIdentity());
}
/// <summary>
/// 对总馆的数据进行添加
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TimedEventBorrow(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
#region 操作sqlsugar
SqlSugarClient db = new SqlSugarClient(
new ConnectionConfig()
{
//ConnectionString = @"server=DESKTOP-FTUFS40\SQLEXPRESS;database=Sy04;uid=sa;pwd=90-=uiop",
ConnectionString = "Data Source=.;Initial Catalog=Sy_DataWallHengDong01;Integrated Security=True",
DbType = SqlSugar.DbType.SqlServer, //设置数据库类型
IsAutoCloseConnection = true, //自动释放数据务，如果存在事务，在事务结束后释放
InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
});
//时间
var afterTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //获取到当前的时间
var beforeTime = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss"); //获取前一小时
#endregion
#region 荣桓城北分馆
string L1 = ConfigurationManager.ConnectionStrings["ShangYe.Data.L1"].ToString().Trim();//获取第一个馆的IP
//获取第一个馆的设备id，此步骤需要查询第一个馆的数据库，客流设备的ID(切记不能和主馆ID重复)
#region 第一个ID
int DeviceID = 2;//设备ID
var url = L1 + "QuqeryList?SysCustDeviceId=" + DeviceID + "&beforeTime=" + beforeTime + "&afterTime=" + afterTime;
//获取返回的结果集
var result = HttpClient.Get(url);
JsonReader reader = new JsonTextReader(new StringReader(result));
var resp = "";
while (reader.Read())
{
resp = reader.Value.ToString();
}
if (resp != "[]")
{
Newtonsoft.Json.Linq.JArray userAarray1 = Newtonsoft.Json.Linq.JArray.Parse(resp) as Newtonsoft.Json.Linq.JArray;
List <SysCustData> userListModel = userAarray1.ToObject <List <SysCustData> >();
foreach (var cus in userListModel)
{
var datas = new SysCustData()
{
SysCustDeviceId = DeviceID, //设备ID
D_Date = Convert.ToDateTime(cus.D_Date), //时间
D_InNum = Convert.ToInt32(cus.D_InNum), //进馆人次
D_OutNum = Convert.ToInt32(cus.D_OutNum), //出馆人次
};
db.Insertable(datas).InsertColumns(it => new { it.SysCustDeviceId, it.D_Date, it.D_InNum, it.D_OutNum }).ExecuteReturnIdentity();
}
LogHelper.InfoLog("荣桓分馆设备ID" + DeviceID + "|时间从" + beforeTime + "开始|到" + afterTime + "结束");
}
#endregion
#region 第二个ID
int DeviceIDA = 7;//设备ID
var urlA = L1 + "QuqeryList?SysCustDeviceId=" + DeviceIDA + "&beforeTime=" + beforeTime + "&afterTime=" + afterTime;
//获取返回的结果集
var resultA = HttpClient.Get(urlA);
JsonReader readerA = new JsonTextReader(new StringReader(resultA));
var respA = "";
while (readerA.Read())
{
respA = readerA.Value.ToString();
}
if (respA != "[]")
{
Newtonsoft.Json.Linq.JArray userAarray1A = Newtonsoft.Json.Linq.JArray.Parse(respA) as Newtonsoft.Json.Linq.JArray;
List <SysCustData> userListModelA = userAarray1A.ToObject <List <SysCustData> >();
foreach (var cusA in userListModelA)
{
var datasA = new SysCustData()
{
SysCustDeviceId = DeviceIDA, //设备ID
D_Date = Convert.ToDateTime(cusA.D_Date), //时间
D_InNum = Convert.ToInt32(cusA.D_InNum), //进馆人次
D_OutNum = Convert.ToInt32(cusA.D_OutNum), //出馆人次
};
db.Insertable(datasA).InsertColumns(it => new { it.SysCustDeviceId, it.D_Date, it.D_InNum, it.D_OutNum }).ExecuteReturnIdentity();
}
LogHelper.InfoLog("荣桓分馆设备ID" + DeviceIDA + "|时间从" + beforeTime + "开始|到" + afterTime + "结束");
}
#endregion
#endregion
#region 荣桓24小时分馆1
//获取第一个馆的IP
string L2 = ConfigurationManager.ConnectionStrings["ShangYe.Data.L2"].ToString().Trim();
//获取第一个馆的设备id，此步骤需要查询第一个馆的数据库，客流设备的ID(切记不能和主馆ID重复)
#region 第一个ID
int DeviceID2 = 4;//设备ID
var url2 = L2 + "QuqeryList?SysCustDeviceId=" + DeviceID2 + "&beforeTime=" + beforeTime + "&afterTime=" + afterTime;
//获取返回的结果集
var result2 = HttpClient.Get(url2);
JsonReader reader2 = new JsonTextReader(new StringReader(result2));
var resp2 = "";
while (reader2.Read())
{
resp2 = reader2.Value.ToString();
}
if (resp2 != "[]")
{
Newtonsoft.Json.Linq.JArray userAarray2 = Newtonsoft.Json.Linq.JArray.Parse(resp2) as Newtonsoft.Json.Linq.JArray;
List <SysCustData> userListMode2 = userAarray2.ToObject <List <SysCustData> >();
foreach (var cus2 in userListMode2)
{
var datas2 = new SysCustData()
{
SysCustDeviceId = DeviceID2, //设备ID
D_Date = Convert.ToDateTime(cus2.D_Date), //时间
D_InNum = Convert.ToInt32(cus2.D_InNum), //进馆人次
D_OutNum = Convert.ToInt32(cus2.D_OutNum), //出馆人次
};
db.Insertable(datas2).InsertColumns(it => new { it.SysCustDeviceId, it.D_Date, it.D_InNum, it.D_OutNum }).ExecuteReturnIdentity();
}
LogHelper.InfoLog("24小时1馆ID" + DeviceID2 + "|时间从" + beforeTime + "开始|到" + afterTime + "结束");
}
#endregion
#endregion
#region 荣桓24小时分馆3
//获取第一个馆的IP
string L3 = ConfigurationManager.ConnectionStrings["ShangYe.Data.L3"].ToString().Trim();
//获取第一个馆的设备id，此步骤需要查询第一个馆的数据库，客流设备的ID(切记不能和主馆ID重复)
#region 第一个ID
int DeviceID3 = 6;//设备ID
var url3 = L3 + "QuqeryList?SysCustDeviceId=" + DeviceID3 + "&beforeTime=" + beforeTime + "&afterTime=" + afterTime;
//获取返回的结果集
var result3 = HttpClient.Get(url3);
JsonReader reader3 = new JsonTextReader(new StringReader(result3));
var resp3 = "";
while (reader3.Read())
{
resp3 = reader3.Value.ToString();
}
if (resp3 != "[]")
{
Newtonsoft.Json.Linq.JArray userAarray3 = Newtonsoft.Json.Linq.JArray.Parse(resp3) as Newtonsoft.Json.Linq.JArray;
List <SysCustData> userListMode3 = userAarray3.ToObject <List <SysCustData> >();
foreach (var cus3 in userListMode3)
{
var datas3 = new SysCustData()
{
SysCustDeviceId = DeviceID3, //设备ID
D_Date = Convert.ToDateTime(cus3.D_Date), //时间
D_InNum = Convert.ToInt32(cus3.D_InNum), //进馆人次
D_OutNum = Convert.ToInt32(cus3.D_OutNum), //出馆人次
};
db.Insertable(datas3).InsertColumns(it => new { it.SysCustDeviceId, it.D_Date, it.D_InNum, it.D_OutNum }).ExecuteReturnIdentity();
}
LogHelper.InfoLog("24小时2馆ID" + DeviceID3 + "|时间从" + beforeTime + "开始|到" + afterTime + "结束");
}
#endregion
#endregion
}
catch (Exception ex)
{
LogHelper.ErrorLog("错误原因：" + ex.Message + "错误位置：" + ex.StackTrace);
}
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Insertable Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.Custom,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
var insertObj = new Order()
{
Id = 1, Name = "order1", Price = 0
};
//var insertObjs = new List<Order> {
// new Order() { Id = 11, Name = "XX", Price=0 },
// new Order() { Id = 12, Name = "XX2" , Price=0}
//};
//var x=db.Insertable(insertObjs).RemoveDataCache().IgnoreColumns(it=>it.CreateTime).UseParameter().ExecuteCommand();
//Ignore CreateTime
db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity
db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity();
//Only insert Name and Price
db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity();
db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity();
//ignore null columns
// db.Insertable(insertObjs).ExecuteCommand();//get change row count
//Use Lock
db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand();
//insertObjs = new List<Order> {
// new Order() { Id = 11, Name = "order11", Price=1 },
// new Order() { Id = 12, Name = "order12" , Price=20, CreateTime=DateTime.Now, CustomId=1}
//};
//db.Insertable(insertObjs).UseSqlServer().ExecuteBulkCopy();
var dt = db.Queryable <Order>().Take(5).ToDataTable();
dt.TableName = "Order";
//db.Insertable(dt).UseSqlServer().ExecuteBulkCopy();
// db.CodeFirst.InitTables<RootTable0, TwoItem, TwoItem2, TwoItem3>();
// db.CodeFirst.InitTables<ThreeItem2>();
// db.DbMaintenance.TruncateTable("RootTable0");
// db.DbMaintenance.TruncateTable("TwoItem");
// db.DbMaintenance.TruncateTable("TwoItem2");
// db.DbMaintenance.TruncateTable("TwoItem3");
// db.DbMaintenance.TruncateTable("ThreeItem2");
// Console.WriteLine("SubInsert Start");
// db.Insertable(new Order()
// {
// Name = "订单 1",
// CustomId = 1,
// Price = 100,
// CreateTime = DateTime.Now,
// Id = 0,
// Items = new List<OrderItem>() {
// new OrderItem(){
// CreateTime=DateTime.Now,
// OrderId=0,
// Price=1,
// ItemId=1
// },
// new OrderItem(){
// CreateTime=DateTime.Now,
// OrderId=0,
// Price=2,
// ItemId=2
// }
// }
// })
// .AddSubList(it => it.Items.First().OrderId).ExecuteCommand();
// db.Insertable(new List<RootTable0>() {
// new RootTable0()
// {
// Name="aa",
// TwoItem2=new TwoItem2() {
// Id="1",
// ThreeItem2=new List<ThreeItem2>(){
// new ThreeItem2(){ Name="a", TwoItem2Id="1" },
// new ThreeItem2(){ Id=2, Name="a2", TwoItem2Id="2" }
// }
// },
// TwoItem=new TwoItem()
// {
// Name ="itema" ,
// RootId=2
// },
// TwoItem3=new List<TwoItem3>(){
// new TwoItem3(){ Id=0, Name="a",Desc="" },
// }
// },
// new RootTable0()
// {
// Name="bb",
// TwoItem2=new TwoItem2() {
// Id="2"
// },
// TwoItem=new TwoItem()
// {
// Name ="itemb" ,
// RootId=2,
// },
// TwoItem3=new List<TwoItem3>(){
// new TwoItem3(){ Id=1, Name="b",Desc="" },
// new TwoItem3(){ Id=2, Name="b1",Desc="1" },
// }
// }
// })
//.AddSubList(it => it.TwoItem.RootId)
//.AddSubList(it => new SubInsertTree()
//{
// Expression = it.TwoItem2.RootId,
// ChildExpression = new List<SubInsertTree>() {
// new SubInsertTree(){
// Expression=it.TwoItem2.ThreeItem2.First().TwoItem2Id
// }
// }
//})
//.AddSubList(it => it.TwoItem3)
//.ExecuteCommand();
// SubNoIdentity(db);
// SubIdentity(db);
//db.Fastest<Order>().BulkCopy(insertObjs);
//var dataTable= db.Queryable<Order>().Select("id,name,Price").Take(2).ToDataTable();
//int result= db.Fastest<Order>().BulkCopy("order", dataTable);
Console.WriteLine("#### Insertable End ####");
}
public bool Insert(T t)
{
return(_db.Insertable(t).ExecuteCommand() > 0);
}
public async Task <int> Add(TEntity model)
{
var i = await Task.Run(() => db.Insertable(model).ExecuteReturnBigIdentity());
return((int)i);
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="DS"></param>
/// <param name="strTable"></param>
/// <returns></returns>
public int InserData(Dictionary <string, object> DS, string strTable)
{
var t66 = db.Insertable(DS).AS(strTable).ExecuteReturnIdentity();
return(int.Parse(t66.ToString()));
}
public ActionResult Publish(FormCollection form)
{
//var address = Request.Form["activity_place"];
DateTime dt = DateTime.Now;
string folder = Server.MapPath("~/images/Activity");
var filePath = folder + "\\" + dt.ToFileTime().ToString() + Request.Files["myfile"].FileName.Substring(Request.Files["myfile"].FileName.LastIndexOf("\\") + 1);
var fileSave = Request.Files["myfile"].FileName;
if (Request.Files["myfile"].FileName != "")
{
fileSave = "../images/Activity/" + dt.ToFileTime().ToString() + Request.Files["myfile"].FileName.Substring(Request.Files["myfile"].FileName.LastIndexOf("\\") + 1);
}
else
{
fileSave = "../iamges/Activity/default.jpg";
}
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
Request.Files["myfile"].SaveAs(filePath);
SqlSugarClient db = new SqlSugarClient(
new ConnectionConfig()
{
ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"],
DbType = DbType.Oracle, //设置数据库类型
IsAutoCloseConnection = true, //自动释放数据务，如果存在事务，在事务结束后释放
InitKeyType = InitKeyType.SystemTable //从实体特性中读取主键自增列信息
});
JObject isSuccess = new JObject();
try
{
var data = new Activity()
{
address = Request.Form["activity_place"],
user_id = (string)Session["user_id"],
activity_name = Request.Form["activity_title"],
max_member_limit = Convert.ToInt16(Request.Form["activity_capacity"]),
act_content = Request.Form["activity_content"],
activity_pictures = fileSave,
note = Request.Form["activity_note"],
cancellation_policy = Request.Form["activity_cancel"]
};
if (db.Insertable(data).ExecuteCommand() >= 1)
{
int productID = db.Queryable <Activity>().Max(it => it.activity_id);
ViewBag.errorMessage = "活动发布成功!活动ID:" + productID;
ViewBag.flag = 1;
ViewBag.productID = productID;
try
{
var myActivityList = db.Queryable <Activity>().Where(it => it.user_id == Session["user_id"]).ToArray();
ViewBag.activityList = myActivityList;
}
catch (Exception ex)
{
throw ex;
}
finally { }
return(View());
}
else
{
ViewBag.errorMessage = "活动发布失败";
ViewBag.flag = 0;
return(View());
}
}
catch (Exception ex)
{
ViewBag.errorMessage = "活动发布失败";
ViewBag.flag = 0;
return(View());
}
finally { }
}
public int Add(Student student)
{
var i = db.Insertable(student).ExecuteReturnBigIdentity();
return(i.ObjToInt());
}
public ActionResult AddFavourites(int productID = -1)
{
JObject isSuccess = new JObject();
SqlSugarClient db = new SqlSugarClient(
new ConnectionConfig()
{
ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"],
DbType = DbType.Oracle, //设置数据库类型
IsAutoCloseConnection = true, //自动释放数据务，如果存在事务，在事务结束后释放
InitKeyType = InitKeyType.SystemTable //从实体特性中读取主键自增列信息
});
if (Session["user_id"] == null || productID == -1)
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "添加失败！你还没有登陆！");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
else
{
DateTime dt_create = DateTime.Now;
var queryResult = db.Queryable <ActivityFavorite>().Where(it => it.user_id == (string)Session["user_id"] && it.activity_id == productID).ToArray();
if (queryResult.Length == 0)
{
try
{
var data = new ActivityFavorite()
{
user_id = (string)Session["user_id"],
activity_id = productID,
times = dt_create
};
if (db.Insertable(data).ExecuteCommand() >= 1)
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "添加成功！");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
else
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "添加失败！这个活动已经在你的心愿单了!");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
}
catch (Exception ex)
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "添加失败！!");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
finally { }
}
else
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "添加失败！这个活动已经在你的心愿单了!");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
}
}
public ActionResult ReceiveMsg1()
{
//新建航班，多表插入
string id = Request.Params["id"];
string pid = Request.Params["pid"];
string t_airport = Request.Params["t_airport"];
string t_terminal = Request.Params["t_terminal"];
string t_time = Request.Params["t_time"];
string l_airport = Request.Params["l_airport"];
string l_terminal = Request.Params["l_terminal"];
string l_time = Request.Params["l_time"];
Actions a = new Actions();
PLANE b = a.GetPLANE(pid);
FLIGHT obj2 = new FLIGHT();
obj2.F_ID = id;
obj2.P_ID = pid;
obj2.CAPACITY = b.CAPACITY;
obj2.RESERVE = 0;
obj2.CHECKED = 0;
obj2.VALUE = 500;
obj2.LUGGAGE = 50;
obj2.BOARD = 0;
FLIGHTIME obj1 = new FLIGHTIME();
obj1.F_ID = id;
obj1.T_AIRPORT = t_airport;
obj1.T_TERMINAL = t_terminal;
obj1.L_AIRPORT = l_airport;
obj1.L_TERMNAL = l_terminal;
obj1.EX_TAKEOFF_TIME = a.SystemTime.AddHours(10);
obj1.EX_LAND_TIME = a.SystemTime.AddHours(12);
obj1.RE_TAKEOFF_TIME = null;
obj1.RE_LAND_TIME = null;
string d_ID = id; //降落飞机ID
char[] str = d_ID.ToCharArray();
string d_ID_re; //返回航班ID
int temp = int.Parse(str[5].ToString());
if (temp % 2 == 0)
{
d_ID_re = d_ID.Substring(0, d_ID.Length - 1) + (temp + 1).ToString();
}
else
{
d_ID_re = d_ID.Substring(0, d_ID.Length - 1) + (temp - 1).ToString();
}
FLIGHTIME obj3 = new FLIGHTIME();
obj3.F_ID = d_ID_re;
obj3.T_AIRPORT = l_airport;
obj3.T_TERMINAL = l_terminal;
obj3.L_AIRPORT = t_airport;
obj3.L_TERMNAL = t_terminal;
obj3.EX_TAKEOFF_TIME = a.SystemTime.AddHours(14);
obj3.EX_LAND_TIME = a.SystemTime.AddHours(16);
obj3.RE_TAKEOFF_TIME = null;
obj3.RE_LAND_TIME = null;
db.Insertable(obj1).ExecuteCommand();
db.Insertable(obj2).ExecuteCommand();
db.Insertable(obj3).ExecuteCommand();
//DataBaseAccess.insertObj(obj1);
//DataBaseAccess.insertObj(obj2);
//DataBaseAccess.insertObj(obj3);
return(View("Airlines"));
}
public int AddEntity(FunctionRepository t)
{
return(db.Insertable(t).ExecuteCommand());
}
/// <summary>
/// 生成领用信息(领用单号和领用详情)
/// </summary>
/// <param name="goodsDtos">操作数据</param>
/// <returns></returns>
public bool InsertFetchOrderInfo(List <GoodsDto> goodsDtos, GoodsChageAttribute goodsChageAttribute, string surgeryOrderCode, out string goodsChangeBusinessOrderCode)
{
goodsChangeBusinessOrderCode = null;
if (goodsDtos.Count <= 0)
{
return(false);
}
//修改商品里的数据为有单领用
goodsDtos.ForEach(it =>
{
if (it.exception_flag == (int)ExceptionFlag.正常 && it.fetch_type == (int)RequisitionAttribute.无单领用)
{
it.fetch_type = (int)RequisitionAttribute.单领用;
}
});
List <GoodsDto> goodsDtosNotEx = goodsDtos.Where(it => it.exception_flag == (int)ExceptionFlag.正常).ToList();
if (goodsDtosNotEx.Count <= 0)
{
return(false);
}
//生成领用单的业务单号，根据实际情况
string business_order_code;
switch (goodsChageAttribute.RequisitionType)
{
case RequisitionType.一般领用:
business_order_code = "-";
break;
case RequisitionType.无单手术领用:
business_order_code = "";
break;
case RequisitionType.单手术领用:
business_order_code = surgeryOrderCode;
break;
default:
business_order_code = "";
break;
}
//事务防止多插入产生脏数据
var result = Db.Ado.UseTran(() =>
{
//领用单id
FetchOrder fetchOrder = Db.Insertable(new FetchOrder
{
//用uuid当作新生成的领用单单号
code = Guid.NewGuid().ToString("N"),
create_time = DateTime.Now,
operator_id = 1,
type = (int)goodsChageAttribute.RequisitionType,
status = (int)goodsChageAttribute.RequisitionStatus,
business_order_code = business_order_code
}).ExecuteReturnEntity();
List <FetchOrderdtl> fetchOrderdtls = goodsDtosNotEx.MapToListIgnoreId <GoodsDto, FetchOrderdtl>();
fetchOrderdtls.ForEach(it =>
{
it.is_add = 0;
it.related_order_id = fetchOrder.id;
it.status = (int)goodsChageAttribute.ConsumablesStatus;
});
Db.Insertable(fetchOrderdtls).ExecuteCommand();
return(fetchOrder);
});
//返回用于生成库存变化单的业务单号
if (result.IsSuccess)
{
goodsChangeBusinessOrderCode = result.Data.code;
}
return(result.IsSuccess);
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Insertable Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.MySql,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
var insertObj = new Order()
{
Id = 1, Name = "order1", Price = 0
};
var updateObjs = new List <Order> {
new Order()
{
Id = 11, Name = "order11", Price = 0
},
new Order()
{
Id = 12, Name = "order12", Price = 0
}
};
var x = db.Insertable(updateObjs).RemoveDataCache().IgnoreColumns(it => it.CreateTime).UseParameter().ExecuteCommand();
//Ignore CreateTime
db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity
db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity();
//Only insert Name and Price
db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity();
db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity();
//ignore null columns
db.Insertable(updateObjs).ExecuteCommand();//get change row count
//Use Lock
db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand();
db.Deleteable <Order>().ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = DateTime.Now,
CustomId = 11,
Name = "11",
Price = 11
}).UseMySql().ExecuteBulkCopy();
var data = db.Queryable <Order>().ToList();
Console.WriteLine("#### Insertable End ####");
}
public bool Edit()
{
//创建数据库对象
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "server=192.168.1.222;uid=sa;pwd=Txl123456;database=yncs",//连接符字串
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
/*
*
*
* GO
* CREATE TABLE[dbo].[Customer](
*
* [id][int] IDENTITY(1, 1) NOT NULL,
*
* [fingerprint] [nvarchar](4000) NULL,
* [name] [nvarchar](50) NULL,
* CONSTRAINT[PK_Customer] PRIMARY KEY CLUSTERED
* (
*
* [id] ASC
* )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
* ) ON[PRIMARY]
* GO
* SET ANSI_NULLS ON
* GO
* SET QUOTED_IDENTIFIER ON
* GO
* CREATE TABLE[dbo].[ProductInfo](
*
* [Id][int] IDENTITY(1, 1) NOT NULL,
*
* [Name] [nvarchar](50) NULL,
* [Quantity] [int] NULL,
* CONSTRAINT[PK_ProductInfo] PRIMARY KEY CLUSTERED
* (
*
* [Id] ASC
* )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
* ) ON[PRIMARY]
* GO
* USE[master]
* GO
* ALTER DATABASE[yncs] SET READ_WRITE
* GO
*
*/
lock (objlock)
{
var pro = db.Queryable <ProductInfo>().First();
if (pro.Quantity > 0)
{
pro.Quantity--;
db.Updateable(pro).ExecuteCommand();
db.Insertable(new Customer {
fingerprint = DateTime.Now.ToString(), name = pro.Quantity.ToString() + Request.QueryString
}).ExecuteCommand();
return(true);
}
else
{
return(false);
}
}
}
private static void ImputEsbDepartment(string parentid)
{
try
{
Stopwatch watch = CommonHelper.TimerStart();
watch = CommonHelper.TimerStart();
LogHelper log = LogFactory.GetLogger("ImputEsbDepartment");
List <DepartmentResult> resultList = new List <DepartmentResult>();
SqlSugarClient Edb = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = Config.ESBConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
var Departmentlist = Edb.Queryable <V_Effective_Tbiz_DepartmentInfo, V_Effective_Tbiz_DepartmentTreeas>((a, b) => new object[] { JoinType.Inner, a.DepartmentId == b.TreeNode })
.Select
(
(a, b) =>
new V_Effective_Tbiz_DepartmentInfo
{
DepartmentId = a.DepartmentId,
FullName = a.FullName
}
).ToList();
log.Info("\r\n------------------------------------------------ESB部门数据导入------------------------------------------------\r\n");
foreach (var item in Departmentlist)
{
DepartmentEntity model = new DepartmentEntity();
model.name = item.FullName;
model.createDeptGroup = true;
model.autoAddUser = true;
model.parentid = parentid;
model.createDeptGroup = true;
model.sourceIdentifier = item.DepartmentId;
DepartmentResult result = DepartmentBll.Create(model);
//将ESB中的id和名称存入到钉钉同步数据库中
result.ESB_DepartmentID = item.DepartmentId;
result.ESB_DepartmentName = item.FullName;
if (result != null)
{
if (result.errcode == 0)
{
string json = result.id;
log.Debug("\n部门id为:" + result.id + "\n");
resultList.Add(result);
Console.Write("成功:" + json + "\n 已插入：" + (Convert.ToInt32(Departmentlist.IndexOf(item)) + 1) + "条,当前进度：" + (Math.Round(Convert.ToDecimal(Convert.ToDouble((Departmentlist.IndexOf(item) + 1)) / Convert.ToDouble(Departmentlist.Count)), 2, MidpointRounding.AwayFromZero) * 100) + "%\n\n");
}
else
{
Console.Write("\r\n" + result.errmsg + "\r\n");
}
}
else
{
Console.Write("\r\n无返回数据\r\n");
}
}
SqlSugarClient Ddb = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = Config.DingTalkConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute
});
Ddb.CodeFirst.InitTables(typeof(DepartmentResult));
//删除目前记录的数据，初始化
Ddb.Deleteable <DepartmentResult>().ExecuteCommand();
int count = Ddb.Insertable <DepartmentResult>(resultList).ExecuteCommand();
log.Info("\n钉钉接口对接成功后，插入到本地共:" + count + " 条数据,共耗时：" + CommonHelper.TimerEnd(watch) + "毫秒\n");
Console.Write("\r\n钉钉接口对接成功后，插入到本地共 " + count + " 条数据,共耗时：" + CommonHelper.TimerEnd(watch) + "毫秒\r\n");
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 设置角色信息
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <SetRoleOutput> SetRole(SetRoleInput input)
{
#region 基础信息更新
var redis = _redisClient.GetDatabase(_configCenter.StringGet(SysConfig.RedisConnectionKey), 2);
var model = _mapper.Map <RoleInfo>(input);
if (model.Id > 0)
{
model.UpdateTime = DateTime.Now;
await _dbContext.Updateable(model)
.IgnoreColumns(it => new { it.ProjectName, it.CreateTime, it.IsSys })
.ExecuteCommandAsync();
}
else
{
model.CreateTime = DateTime.Now;
model.IsDel = false;
model.Id = await _dbContext.Insertable(model)
.ExecuteReturnIdentityAsync();
}
#endregion
#region 菜单权限
// 用户角色操作
List <RoleMenuInfo> roleMenuList = new List <RoleMenuInfo>();
foreach (var id in input.MenuIdList)
{
// 防止重复数据
if (!roleMenuList.Exists(it => it.MenuId == id))
{
roleMenuList.Add(new RoleMenuInfo
{
MenuId = id,
RoleId = model.Id
});
}
}
// 删除用户当前角色
await _dbContext.Deleteable <RoleMenuInfo>().Where(f => f.RoleId == model.Id).ExecuteCommandAsync();
// 添加用户角色
if (roleMenuList.Count > 0)
{
await _dbContext.Insertable(roleMenuList).ExecuteCommandAsync();
}
#endregion
#region 菜单权限
// 用户角色操作
List <RoleApiInfo> roleApiList = new List <RoleApiInfo>();
foreach (var id in input.ApiIdList)
{
// 防止重复数据
if (!roleApiList.Exists(it => it.ApiId == id))
{
roleApiList.Add(new RoleApiInfo
{
ApiId = id,
RoleId = model.Id
});
}
}
// 删除用户当前角色
await _dbContext.Deleteable <RoleApiInfo>().Where(f => f.RoleId == model.Id).ExecuteCommandAsync();
// 添加用户角色
if (roleApiList.Count > 0)
{
await _dbContext.Insertable(roleApiList).ExecuteCommandAsync();
}
#endregion
#region 缓存更新
await redis.KeyDeleteAsync(CacheKeys.RoleAllUseKey);
// 应该立即更新缓存
#endregion
return(new SetRoleOutput {
});
}
public int AddUser(B_TbUser info)
{
int id = db.Insertable(info).With(SqlWith.UpdLock).ExecuteReturnIdentity();
return(id);
}
public bool Insert(SqlSugarClient client, T t)
{
return(client.Insertable(t).ExecuteCommand() > 0);
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### CodeFirst Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.MySql,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
db.Aop.OnLogExecuted = (s, p) =>
{
Console.WriteLine(s);
};
//初始化分表
db.CodeFirst.SplitTables().InitTables <OrderSpliteTest>();
Console.WriteLine();
//根据最近3个表进行查询
var list = db.Queryable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid())
.SplitTable(tabs => tabs.Take(3))
.Where(it => it.Time == DateTime.Now).ToOffsetPage(1, 2);
var first = db.Queryable <OrderSpliteTest>()
.SplitTable(DateTime.MaxValue, DateTime.Now)
.First();//no table
Console.WriteLine();
//根据时间选出的表进行查询
var list2 = db.Queryable <OrderSpliteTest>().SplitTable(tabs => tabs.Where(it => it.Date >= DateTime.Now.AddYears(-2))).ToList();
Console.WriteLine();
//删除数据只在最近3张表执行操作
var x = db.Deleteable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid()).SplitTable(tabs => tabs.Take(3)).ExecuteCommand();
Console.WriteLine();
var tableName = db.SplitHelper <OrderSpliteTest>().GetTableName(DateTime.Now.AddDays(-1));
var tableName2 = db.SplitHelper(new OrderSpliteTest()
{
Time = DateTime.Now
}).GetTableNames();
var tableName3 = db.SplitHelper(new List <OrderSpliteTest> {
new OrderSpliteTest()
{
Time = DateTime.Now
},
new OrderSpliteTest()
{
Time = DateTime.Now
},
new OrderSpliteTest()
{
Time = DateTime.Now.AddMonths(-10)
}
}).GetTableNames();
var x2 = db.Updateable <OrderSpliteTest>()
.SetColumns(it => it.Name == "a")
.Where(it => it.Pk == Guid.NewGuid())
.SplitTable(tabs => tabs.InTableNames(tableName2))
.ExecuteCommand();
Console.WriteLine();
//按日分表
var x3 = db.Insertable(new OrderSpliteTest()
{
Name = "A"
}).SplitTable().ExecuteCommand();
Console.WriteLine();
////强制分表类型
var x4 = db.Insertable(new OrderSpliteTest()
{
Name = "A", Time = DateTime.Now.AddDays(-1)
}).SplitTable().ExecuteCommand();
var tableName21 = db.SplitHelper <OrderSpliteTest>().GetTableName(DateTime.Now.AddDays(-111));
var listNull = db.Queryable <OrderSpliteTest>().SplitTable(ta => ta.InTableNames(tableName21)).ToList();
Console.WriteLine("#### CodeFirst end ####");
}
private bool Save()
{
if (lblKCDH.txt.Text.Length == 0)
{
MessageBoxEx.Show("请选择领用染料", "提示");
return(false);
}
var all1 = db.Queryable <BaseIList>()
.Where(it => it.dingdanhao == lblKCDH.txt.Text)
.OrderBy(it => it.SN, OrderByType.Desc)
.First();
if (all1 == null)
{
MessageBoxEx.Show("没有发现此该染料,重新选择");
return(false);
}
else
{
var a = db.Queryable <BaseIList>()
.Where(it => it.dingdanhao == lblKCDH.txt.Text)
.First();
if (lblZL.txt.Text.Length == 0)
{
MessageBoxEx.Show("重量必须填写");
return(false);
}
if (lblSL.txt.Text.Length == 0)
{
MessageBoxEx.Show("数量必须填写");
return(false);
}
BaseIList baseIList = new BaseIList()
{
kehu = lblKCDH.txt.Text,
dingdanhao = lblRKDH.txt.Text,
//lblKCDH.txt.Text = baseIList.kehu;
//lblRKDH.txt.Text = baseIList.dingdanhao;
item3 = lblRHMC.cobodgv.Text,
item1 = lblRHLB.cobodgv.Text,
seming = lblSL.txt.Text,
item0 = lblDJ.txt.Text,
riqi = DateTime.Now,
pishu = lblZL.txt.Text,
menfu = lblBZ1.txt.Text,
item2 = lblGHS.txt.Text,
leibie = "染料领用",
};
try
{
db.Insertable <BaseIList>(baseIList).ExecuteCommand();
a.pishu = (Int32.Parse(a.pishu) - Int32.Parse(lblZL.txt.Text)) + "";
a.seming = (Int32.Parse(a.seming) - Int32.Parse(lblSL.txt.Text)) + "";
db.Updateable <BaseIList>(a).ExecuteCommand();
return(true);
}catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "提示");
return(false);
}
}
}
public void InsertUser(List <CurrentUser> list)
{
Db.Insertable <CurrentUser>(list).ExecuteCommand();
}
public T Insert <T>(T t) where T : class, new()
{
return(_dbContext.Insertable <T>(t).ExecuteReturnEntity());
}
public IActionResult Create([FromBody] ItemPlanVM parm)
{
var userinfo = _tokenManager.GetSessionInfo();
using (SqlSugarClient db = Core.DbContext.CurrentDB)
{
Core.DbContext.BeginTran();
try
{
List <SugarParameter> parameters = new List <SugarParameter>();
parameters.Add(new SugarParameter("UserID", userinfo.UserID));
parameters.Add(new SugarParameter("UserName", userinfo.UserName));
//体系项目信息
List <Biz_ContractItem_Sub> subitemlist = parm.ContractItem_SubList;
if (subitemlist.Count == 0)
{
throw new Exception("提交数据不包含体系项目信息，请核对！");
}
string IDs = string.Join(",", subitemlist.Select(m => m.AutoID));
if (db.Ado.GetScalar("select 1 from Biz_ContractItem_Sub where status > 20000 and ContractItemSubID in (@ids)", new { ids = IDs }) != null)
{
throw new Exception("提交项目包含已经安排项目，请核对！");
}
//匹配任务信息
Biz_Contract_Plan plan = new Biz_Contract_Plan();
Api.Common.Helpers.ComHelper.MapperMatch <Biz_Contract_Plan, ItemPlanVM>(plan, parm, "ID", "status");
var helper = new Common.SerialNoHelper();
var serialno = helper.Generate("N20"); //生成任务编号
plan.ContractPlanNo = serialno;
plan = plan.Adapt <Biz_Contract_Plan>().ToCreate(_tokenManager.GetSessionInfo());
int ContractPlanID = db.Insertable <Biz_Contract_Plan>(plan).ExecuteReturnIdentity();
//处理项目信息
foreach (var subitem in subitemlist)
{
//subitem.ContractPlanID = plan.ID;
db.Updateable <Biz_ContractItem_Sub>().SetColumns(m => new Biz_ContractItem_Sub()
{
ContractPlanID = plan.ID,
MultiSiteAddDays = subitem.MultiSiteAddDays,
AuditCombinDegree = subitem.AuditCombinDegree,
DeletionScale = subitem.DeletionScale,
PlanTotalDays = subitem.PlanTotalDays,
OffSiteDays = subitem.OffSiteDays,
FirstDays = subitem.FirstDays,
SecondDays = subitem.SecondDays,
MultiSitetTravelDays = subitem.MultiSitetTravelDays,
OtherAddDays = subitem.OtherAddDays,
TrueFirstDays = subitem.TrueFirstDays,
TrueSecondDays = subitem.TrueSecondDays,
}).Where(m => m.ID == subitem.ID).ExecuteCommand();
//提交
Step.Submit(db, subitem, "Biz_ContractItem_Sub", "ID", "status", "201", parameters, UpdateBizEntityAfterSubmitted, "提交安排");
}
//审核员信息
List <PlanAuditorVM> auditorlist = parm.PlanAuditorList;
foreach (var auditorVM in auditorlist)
{
Biz_Contract_PlanAuditor auditor = Api.Common.Helpers.ComHelper.Mapper <Biz_Contract_PlanAuditor, PlanAuditorVM>(auditorVM);
auditor = auditor.Adapt <Biz_Contract_PlanAuditor>().ToCreate(_tokenManager.GetSessionInfo());
auditor.ContractPlanID = plan.ID;
db.Insertable(auditor).AS("Biz_Contract_PlanAuditor").ExecuteCommand();
foreach (var auditoritem in auditorVM.PlanAuditItemList)
{
auditoritem.ID = Guid.NewGuid().ToString();
auditoritem.PlanAuditorID = auditor.ID;
auditoritem.ContractPlanID = plan.ID;
db.Insertable(auditoritem).AS("Biz_Contract_PlanAuditor_Item").ExecuteCommand();
}
}
Core.DbContext.CommitTran();
return(toResponse(plan.ID));
}
catch (Exception ex)
{
Core.DbContext.RollbackTran();
return(toResponse(StatusCodeType.Error, ex.Message));
}
}
}
private static void SubIdentity(SqlSugarClient db)
{
db.CodeFirst.InitTables <Country1, Province1, City1>();
db.DbMaintenance.TruncateTable("Country1");
db.DbMaintenance.TruncateTable("Province1");
db.DbMaintenance.TruncateTable("City1");
db.Insertable(new List <Country1>()
{
new Country1()
{
Id = 1,
Name = "中国",
Provinces = new List <Province1>()
{
new Province1 {
Id = 1001,
Name = "江苏",
citys = new List <City1>()
{
new City1()
{
Id = 1001001, Name = "南通"
},
new City1()
{
Id = 1001002, Name = "南京"
}
}
},
new Province1 {
Id = 1002,
Name = "上海",
citys = new List <City1>()
{
new City1()
{
Id = 1002001, Name = "徐汇"
},
new City1()
{
Id = 1002002, Name = "普陀"
}
}
},
new Province1 {
Id = 1003,
Name = "北京",
citys = new List <City1>()
{
new City1()
{
Id = 1003001, Name = "北京A"
},
new City1()
{
Id = 1003002, Name = "北京B"
}
}
}
}
},
new Country1()
{
Name = "美国",
Id = 2,
Provinces = new List <Province1>()
{
new Province1()
{
Name = "美国小A",
Id = 20001
},
new Province1()
{
Name = "美国小b",
Id = 20002
}
}
},
new Country1()
{
Name = "英国",
Id = 3
}
})
.AddSubList(it => new SubInsertTree()
{
Expression = it.Provinces.First().CountryId,
ChildExpression = new List <SubInsertTree>()
{
new SubInsertTree()
{
Expression = it.Provinces.First().citys.First().ProvinceId
}
}
})
.ExecuteReturnPrimaryKey();
var list = db.Queryable <Country1>()
.Mapper(it => it.Provinces, it => it.Provinces.First().CountryId)
.Mapper(it =>
{
foreach (var item in it.Provinces)
{
item.citys = db.Queryable <City1>().Where(y => y.ProvinceId == item.Id).ToList();
}
})
.ToList();
}
public bool Insert(T t, bool IgnoreNullColumn = true)
{
return(_db.Insertable(t).IgnoreColumns(IgnoreNullColumn).ExecuteCommand() > 0);
}
private static void DistributedTransactionExample()
{
Console.WriteLine("");
Console.WriteLine("#### Distributed TransactionExample Start ####");
SqlSugarClient db = new SqlSugarClient(new List <ConnectionConfig>()
{
new ConnectionConfig()
{
ConfigId = "1", DbType = DbType.MySql, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
},
new ConnectionConfig()
{
ConfigId = "2", DbType = DbType.MySql, ConnectionString = Config.ConnectionString2, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
}
});
var db1 = db.Ado.Connection.Database;
//use db1
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));//
db.Insertable(new Order()
{
Name = "order1", CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count());
//use db2
db.ChangeDatabase("2");
var db2 = db.Ado.Connection.Database;
db.DbMaintenance.CreateDatabase();//Create Database2
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));
db.Insertable(new Order()
{
Name = "order1", CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count());
if (db2 == db1)
{
return;
}
// Example 1
Console.WriteLine("Example 1");
try
{
db.BeginTran();
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception();
db.CommitTran();
}
catch
{
db.RollbackTran();
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use db1
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
// Example 2
Console.WriteLine("Example 2");
var result = db.UseTran(() =>
{
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
if (result.IsSuccess == false)
{
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use db1
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
// Example 3
Console.WriteLine("Example 3");
var result2 = db.UseTranAsync(async() =>
{
db.ChangeDatabase("1");//use db1
await db.Deleteable <Order>().ExecuteCommandAsync();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
await db.Deleteable <Order>().ExecuteCommandAsync();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
result2.Wait();
if (result2.Result.IsSuccess == false)
{
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use sqlserver
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use mysql
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
Console.WriteLine("#### Distributed TransactionExample End ####");
}
private bool Save()
{
var all1 = db.Queryable <CP_TABLE>()
.Where(it => int.Parse(it.ganghao) == int.Parse(lblGH.txt.Text) && it.status == "1")
.ToList();
if (all1 == null)
{
MessageBoxEx.Show("请先入库");
return(false);
}
else
{
int a_pishu = 0;
for (var i = 0; i < all1.Count; i++)
{
a_pishu += Convert.ToInt32(all1[i].pishu);
}
var aa = db.Queryable <CP_TABLE>().Where(it => it.kehu == lblGH.txt.Text && it.status == "2").ToList();
int d = 0;
for (var i = 0; i < aa.Count; i++)
{
d += Convert.ToInt32(aa[i].pishu);
}
if ((d + Convert.ToInt32(lblPS.txt.Text)) > a_pishu)
{
MessageBoxEx.Show("出仓匹数超过仓库内总匹数，总匹数:" + a_pishu, "提示");
return(false);
}
int input = Convert.ToInt32(lblPS.txt.Text);
for (var i = 0; i < all1.Count; i++)
{
if (input > Convert.ToInt32(all1[i].pishu))
{
all1[i].pishu = "0";
input -= Convert.ToInt32(all1[i].pishu);
}
else
{
all1[i].pishu = (Convert.ToInt32(all1[i].pishu) - input) + "";
}
db.Updateable <CP_TABLE>(all1[i]).ExecuteCommand();
}
CP_TABLE a = new CP_TABLE()
{
ganghao = lblGH.txt.Text,
rukudanhao = lblRKDH.txt.Text,
kehu = lblKH.cobodgv.Text,
riqi = riqi.Value,
pinming = lblPM.cobodgv.Text,
cangwei = lblCW.txt.Text,
yewuyuan = lblYWY.cobodgv.Text,
dingdanhao = lblDDH.txt.Text,
pishu = lblPS.txt.Text,
jincangyuan = lblJCY.cobodgv.Text,
zhongliang = lblZL.txt.Text,
beizu = lblBZ.txt.Text,
status = "2",
};
db.Insertable <CP_TABLE>(a).ExecuteCommand();
return(true);
}
}