public SaveResult Update(Cryptocurrencies cur, List <PriceInfos> priceList, int userId, string userName)
{
Cryptocurrencies tmp = FoundationDB.DB.Queryable <Cryptocurrencies>().Where(t => t.Name.Equals(cur.Name)).First();
if (tmp != null && cur.Id != tmp.Id)
{
return(new SaveResult(false, "The name already exists."));
}
Cryptocurrencies oldCur = FoundationDB.CryptocurrencyDb.GetById(cur.Id);
if (cur.IconURL != null)
{
oldCur.IconURL = cur.IconURL;
}
oldCur.Withdrawal_Tier = cur.Withdrawal_Tier / 100;
oldCur.Withdrawal_Fee = cur.Withdrawal_Fee;
oldCur.Sequence = cur.Sequence;
oldCur.DecimalPlace = cur.DecimalPlace;
oldCur.NeedTag = cur.NeedTag;
oldCur.Name = cur.Name;
oldCur.IsFixedPrice = cur.IsFixedPrice;
oldCur.Code = cur.Code;
oldCur.Status = cur.Status;
oldCur.Enable = cur.Enable;
oldCur.IsWhiteLabel = cur.IsWhiteLabel;
StringBuilder sb = new StringBuilder();
if (cur.IsFixedPrice)
{
var pl = FoundationDB.DB.Queryable <PriceInfos>().Where(t => t.CryptoID == cur.Id).ToList();
if (pl.Count == 0)
{
FoundationDB.PriceInfoDb.InsertRange(priceList.ToArray());
}
else
{
int count = 0;
priceList.ForEach(data =>
{
sb.Append(" Update dbo.PriceInfo " +
"SET " +
"[Price]=@Price" + count.ToString() +
" WHERE [CryptoID]=@CryptoId" + count.ToString() + " and [CurrencyID] = @CurrencyID" + count.ToString() + "; ");
count++;
});
count = 0;
SugarParameter[] Paras = new SugarParameter[(priceList.Count * 3)];
int paraCount = 0;
priceList.ForEach(data =>
{
Paras[count] = new SugarParameter("@Price" + paraCount.ToString(), priceList[paraCount].Price);
count++;
Paras[count] = new SugarParameter("@CryptoID" + paraCount.ToString(), priceList[paraCount].CryptoID);
count++;
Paras[count] = new SugarParameter("@CurrencyID" + paraCount.ToString(), priceList[paraCount].CurrencyID);
count++;
paraCount++;
});
FoundationDB.DB.Ado.ExecuteCommand(sb.ToString(), Paras);
}
}
// Create ActionLog
ActionLog actionLog = new ActionLog();
actionLog.IPAddress = GetClientIPAddress();
actionLog.AccountId = userId;
actionLog.CreateTime = DateTime.UtcNow;
actionLog.ModuleCode = typeof(CryptocurrenciesBLL).FullName + ".Update";
actionLog.Username = userName;
actionLog.LogContent = "Update Cryptocurrencies " + cur.Id;
ActionLogBLL ab = new ActionLogBLL();
ab.Create(actionLog);
return(new SaveResult(FoundationDB.CryptocurrencyDb.Update(oldCur)));
}
/// <summary>
/// sql增删改 带参数
/// </summary>
/// <param name="sql"></param>
public virtual void SqlAddDelUpByParam(string sql, SugarParameter param)
{
Db.AddQueue(sql, param);
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Ado 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)));
}
}
});
//sql
var dt = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new List <SugarParameter>()
{
new SugarParameter("@id", 1),
new SugarParameter("@name", "2")
});
//sql
var dt2 = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new { id = 1, name = "2" });
//create sp
db.Ado.ExecuteCommand(@"
if object_id('up_user') is not null
drop proc up_user;");
db.Ado.ExecuteCommand(@"
create proc up_user
@id int,
@name varchar(10) ='' output
as
begin
set @name='abc'
select @id as id
end
");
//get output
var dt3 = db.Ado.UseStoredProcedure().GetDataTable("up_user", new { name = "张三", id = 0 });
var IdP = new SugarParameter("@id", 1);
var NameP = new SugarParameter("@name", null, true);//isOutput=true
var dt4 = db.Ado.UseStoredProcedure().GetDataTable("up_user", IdP, NameP);
var outputValue = NameP.Value;
//There are many methods to under db.ado
var list = db.Ado.SqlQuery <Order>("select * from [order] ");
var list2 = db.Ado.SqlQuery <Order, Order>("select * from [order] where 1=2;select * from [order] ");
var list3 = db.Ado.SqlQuery <int>(" delete from [order] where 2=15 ");
var intValue = db.Ado.SqlQuerySingle <int>("select 1");
db.Ado.ExecuteCommand("delete [order] where id>1000");
db.SqlQueryable <Custom>(@"select *
from custom").ToList();
//db.Ado.xxx
Console.WriteLine("#### Ado End ####");
}
public void tt()
{
var t = new SugarParameter("@p1", "1");
}
/// <summary>
/// 查询商品
/// </summary>
/// <param name="search"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <ProductWeb> GetProductList(ProductSearch search, out int total)
{
string sql = @"SELECT
p.Id,
p.ProductID,
p.Label,
p.CnName,
p.EnName,
p.ExpressCode,
p.Weight,
p.CostPrice,
p.PurchasePrice,
p.Introduce,
pt.TypeName,
p.Remarks,
p.SalesVolume,
p.Stock,
p.IsTop,
p.IsOutOfStock,
p.IsClearStock,
p.State,
p.IsDelete,
p.OnlineTime,
p.Brand,
p.BrandIntroduce,
p.CreatTime,
r.Address as Picture
FROM
Product AS p
LEFT JOIN ProductType AS pt ON p.Type = pt.Id
LEFT JOIN Resources AS r ON p.ProductID = r.ProductID AND r.Type = 1 AND r.Sort = 0
WHERE p.State = @state AND p.IsDelete = 0 ";
string sqlcount = @"SELECT COUNT(0) AS Count
FROM
Product AS p
LEFT JOIN ProductType AS pt ON p.Type = pt.Id
LEFT JOIN Resources AS r ON p.ProductID = r.ProductID AND r.Type = 1 AND r.Sort = 0
WHERE p.State = @state AND p.IsDelete = 0 ";
if (!string.IsNullOrEmpty(search.CnName))
{
sql += "AND locate(@name,p.CnName)>0 ";
sqlcount += "AND locate(@name,p.CnName)>0 ";
}
if (!string.IsNullOrEmpty(search.ProductID))
{
sql += "AND p.ProductID = @productId ";
sqlcount += "AND p.ProductID = @productId ";
}
if (search.Type != null)
{
sql += "AND p.Type = @type ";
sqlcount += "AND p.Type = @type ";
}
if (search.dateType == 0)
{
if (!string.IsNullOrEmpty(search.startDate))
{
sql += "AND p.CreatTime > @startDate ";
sqlcount += "AND p.CreatTime > @startDate ";
}
if (!string.IsNullOrEmpty(search.endDate))
{
sql += "AND p.CreatTime > @endDate ";
sqlcount += "AND p.CreatTime > @endDate ";
}
}
else if (search.dateType == 1)
{
if (!string.IsNullOrEmpty(search.startDate))
{
sql += "AND p.UpdateTime > @startDate ";
sqlcount += "AND p.UpdateTime > @startDate ";
}
if (!string.IsNullOrEmpty(search.endDate))
{
sql += "AND p.UpdateTime > @endDate ";
sqlcount += "AND p.UpdateTime > @endDate ";
}
}
sql += "LIMIT @pageIndex,@page";
search.Evaluate();
SugarParameter[] parameters = new SugarParameter[]
{
new SugarParameter("@name", search.CnName),
new SugarParameter("@productId", search.ProductID),
new SugarParameter("@type", search.Type),
new SugarParameter("@startDate", search.startDate.ToString()),
new SugarParameter("@endDate", search.endDate),
new SugarParameter("@pageIndex", (search.PageIndex - 1) * search.PageSize),
new SugarParameter("@page", search.PageSize),
new SugarParameter("@state", search.State)
};
parameters[0].DbType = System.Data.DbType.String;
parameters[1].DbType = System.Data.DbType.String;
parameters[2].DbType = System.Data.DbType.Int32;
parameters[3].DbType = System.Data.DbType.String;
parameters[4].DbType = System.Data.DbType.String;
parameters[5].DbType = System.Data.DbType.Int32;
parameters[6].DbType = System.Data.DbType.Int32;
parameters[7].DbType = System.Data.DbType.Boolean;
//总数
total = _db.Ado.GetInt(sqlcount, parameters);
List <ProductWeb> list = _db.Ado.SqlQuery <ProductWeb>(sql, parameters).ToList();
return(list);
}
/// <summary>
/// 获取订单列表
/// </summary>
/// <param name="OrderState"></param>
/// <param name="CustomerId"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <returns></returns>
public List <OrderParent> GetOrderParentcsList(OrderStateEnum OrderState, int CustomerId, int PageSize, int PageIndex)
{
//sql语句
string sql = @"SELECT
od.Id,
op.CustomerID,
od.ProductID,
od.OrderID,
op.TraceID,
op.PackPrice,
op.ExpressPrice,
op.OrderState,
op.PaymentType,
da.ReceiveName,
da.Address,
da.Province,
da.City,
da.Area,
da.Phone,
od.Number,
p.PurchasePrice,
p.CnName AS ProductName,
op.TotalPrice,
op.CustomerRemarks,
op.DeliveryTime,
op.PayTime,
op.FinishTime,
op.UpdateTime,
op.CreatTime,
r.Address AS Picture
FROM
OnLineOrderDetail AS od
INNER JOIN OnLineOrderParent AS op ON op.OrderID = od.OrderID
INNER JOIN Product AS p ON p.ProductID = od.ProductID
INNER JOIN DeliveryAddress AS da ON op.DeliveryAddressID = da.Id
LEFT JOIN Resources AS r ON p.ProductID = r.ProductID
AND r.Type = 1 and Sort =0
WHERE op.IsDelete = 0 AND op.CustomerID = @customerId ";
if (OrderState != OrderStateEnum.All)
{
sql += "AND op.OrderState = @orderState ";
}
sql += "LIMIT @pageIndex,@page";
SugarParameter[] parameters = new SugarParameter[]
{
new SugarParameter("@orderState", OrderState),
new SugarParameter("@customerId", CustomerId),
new SugarParameter("@pageIndex", (PageIndex - 1) * PageSize),
new SugarParameter("@page", PageSize)
};
parameters[0].DbType = System.Data.DbType.Int32;
parameters[1].DbType = System.Data.DbType.Int32;
parameters[2].DbType = System.Data.DbType.Int32;
parameters[3].DbType = System.Data.DbType.Int32;
List <OrderParent> orderParentList = _db.Ado.SqlQuery <OrderParent>(sql, parameters).ToList();
return(orderParentList);
}
private void btnSaveRetVal_Click(object sender, EventArgs e)
{
if (skinLVMethods.SelectedItems.Count > 0 && skinLVInterfaces.SelectedItems.Count > 0)
{
try
{
var a = skinLVMethods.SelectedItems[0];
var intf = skinLVInterfaces.SelectedItems[0];
HA_Method ha_method = new HA_Method
{
intf_code = a.SubItems[0].Text,
m_name = a.SubItems[1].Text,
m_code = a.SubItems[2].Text,
m_desc = a.SubItems[3].Text,
//m_reqtype = a.SubItems[4].Text,
m_template = Convert.ToInt32(a.SubItems[4].Text),
m_version = a.SubItems[5].Text,
m_funcode = a.SubItems[6].Text,
m_isencrypted = false, //a.SubItems[2].Text,
m_arggrpcode = a.SubItems[8].Text,
m_rtnvaluecode = a.SubItems[9].Text,
m_pagesize = Convert.ToInt32(a.SubItems[10].Text)
};
//1.connStr: 通过intf_code得到 interface上的dbConn,取出connStr和DBType
string connStr = "";
string connName = "";
string dbType = "";
string intfcode = ha_method.intf_code;
DbService db = DB.GetMainDb();
Hashtable htPara = new Hashtable();
htPara.Add("intf_code", intfcode);
DataSet tempds = db.ExecuteSqlID("GetInterface2", htPara);
if (tempds != null && tempds.Tables.Count > 0)
{
if (tempds.Tables[0].Rows.Count > 0)
{
connName = tempds.Tables[0].Rows[0]["intf_dbconn"].ToString();
}
}
if (string.IsNullOrEmpty(connName))
{
MessageBox.Show("接口没有配置数据库连接");
return;
}
//2.dt
SqlSugar.DbType sdt = SqlSugar.DbType.SqlServer;
Hashtable htParaConn = new Hashtable();
htParaConn.Add("conn_name", connName);
tempds = db.ExecuteSqlID("GetConnection2", htParaConn);
if (tempds != null && tempds.Tables.Count > 0)
{
if (tempds.Tables[0].Rows.Count > 0)
{
connStr = tempds.Tables[0].Rows[0]["conn_string"].ToString();
dbType = tempds.Tables[0].Rows[0]["conn_dbtype"].ToString();
}
}
if (string.IsNullOrEmpty(connStr))
{
MessageBox.Show("数据库连接不存在");
return;
}
switch (dbType)
{
case "MSSql":
sdt = SqlSugar.DbType.SqlServer;
break;
case "Cache*":
break;
case "Oracle*":
sdt = SqlSugar.DbType.Oracle;
break;
default:
break;
}
//3.sqlStmt
string sqlStmt = ha_method.m_funcode;
if (string.IsNullOrEmpty(sqlStmt))
{
MessageBox.Show("没有查询语句");
return;
}
//4.paras 从ag_code里得到 ag组，再去得到所有的参数
string ag_code = ha_method.m_arggrpcode;
Hashtable htParaAG = new Hashtable();
htParaAG.Add("ag_code", ag_code);
tempds = db.ExecuteSqlID("GetParas2", htParaAG);
List <SugarParameter> sParas = new List <SugarParameter>();
if (tempds != null && tempds.Tables.Count > 0)
{
if (tempds.Tables[0].Rows.Count > 0)
{
int rowCount = tempds.Tables[0].Rows.Count;
for (int i = 0; i < rowCount; i++)
{
SugarParameter sp = new SugarParameter(tempds.Tables[0].Rows[i]["para_name"].ToString(), tempds.Tables[0].Rows[i]["para_default"].ToString());
sParas.Add(sp);
}
}
}
if (sParas.Count == 0)
{
MessageBox.Show("没有配置参数");
return;
}
//5 处理pagesize
if (!sqlStmt.ToLower().Contains("top"))
{
if (ha_method.m_pagesize == 0)
{
sqlStmt = sqlStmt.ToLower().Replace("select", "select top 1");
}
else
{
string topnumber = ha_method.m_pagesize.ToString();
if (ha_method.m_pagesize > 5)
{
topnumber = "5";
}
sqlStmt = sqlStmt.ToLower().Replace("select", "select top " + topnumber);
}
}
string resultJSON = SqlSugarUtils.GetSingleJSONResult(connStr, sdt, sqlStmt, sParas);
JsonTool jt = new JsonTool();
//6 美化JSON字符串
resultJSON = jt.ConvertJsonString(resultJSON);
edtJson.Text = resultJSON;
//7 顺便保存到 RetVO 的 类JSON里去
Hashtable htParaRV = new Hashtable();
htParaRV.Add("rv_name", "ret" + ha_method.m_code);
tempds = db.ExecuteSqlID("GetRetVO2", htParaRV);
if (tempds != null && tempds.Tables.Count > 0)
{
HA_RetValObjectDAL dal = new HA_RetValObjectDAL();
HA_RetValObject rvo = new HA_RetValObject
{
rv_name = "ret" + ha_method.m_code,
rv_exttype = tempds.Tables[0].Rows[0]["rv_exttype"].ToString(),
rv_note = tempds.Tables[0].Rows[0]["rv_note"].ToString(),
rv_JSON = resultJSON
};
bool ret = dal.Update(rvo);
if (ret == false)
{
MessageBox.Show("保存到RetVO失败 : " + ret);
}
}
//8 顺手将json转换为类文件
#region 测试后屏蔽这段代码
//string destFolder = System.AppDomain.CurrentDomain.BaseDirectory;
//destFolder += ha_method.intf_code + @"\" + ha_method.m_code + @"\Model";
//DirectoryInfo di = new DirectoryInfo(destFolder);
//if (di.Exists == false)
//{
// Directory.CreateDirectory(destFolder);
//}
//bool writeClassRet = RunTimeHelper.GenClassStringByJSON(resultJSON, "ILAK.API", "ILAK.API.Model", destFolder, "ret" + ha_method.m_code);
//if (!writeClassRet)
//{
// MessageBox.Show("写文件失败");
//}
#endregion
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void btnPreviewReq_Click(object sender, EventArgs e)
{
#region 调用返回json串的操作
//SqlSugarUtils ssu = new SqlSugarUtils();
//string resultJSON = ssu.TestGetDbAndOperate();
//JsonTool jt = new JsonTool();
//resultJSON = jt.ConvertJsonString(resultJSON);
//edtJson.Text = resultJSON;
#endregion
try
{
if (skinLVMethods.SelectedItems.Count > 0)
{
var a = skinLVMethods.SelectedItems[0];
HA_Method ha_method = new HA_Method
{
intf_code = a.SubItems[0].Text,
m_name = a.SubItems[1].Text,
m_code = a.SubItems[2].Text,
m_desc = a.SubItems[3].Text,
//m_reqtype = a.SubItems[4].Text,
m_template = Convert.ToInt32(a.SubItems[4].Text),
m_version = a.SubItems[5].Text,
m_funcode = a.SubItems[6].Text,
m_isencrypted = false, //a.SubItems[2].Text,
m_arggrpcode = a.SubItems[8].Text,
m_rtnvaluecode = a.SubItems[9].Text,
m_pagesize = Convert.ToInt32(a.SubItems[10].Text)
};
string m_code = a.SubItems[2].Text;
SugarParameter sp = new SugarParameter("m_code", m_code);
List <SugarParameter> lsp = new List <SugarParameter>();
lsp.Add(sp);
string sysConnString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//查询所有参数
string sqlStmt = @"SELECT
a.[para_name]
,a.[para_default]
,a.[para_datatype]
FROM [dbo].[HA_Parameter] a
left join [dbo].[HA_ArgGroup] b on a.para_code
in (select col from dbo.split((select i.ag_paras from [dbo].[HA_ArgGroup] i
where i.ag_code=b.ag_code)+',' ,','))
left join [dbo].[HA_Method] c on c.m_arggrpcode = b.ag_code
where c.m_code =@m_code";
using (DataTable dt = SqlSugarUtils.GetQueryDataTableResult(sysConnString, sqlStmt, lsp))
{
int columnNumber = dt.Columns.Count;
int rowNumber = dt.Rows.Count;
//var obj = DynamicParasFactory.ConstructRequestObject(dt);
string codeString = DynamicParasFactory.CodeBuilder(dt);
string resultJSON = DynamicParasFactory.TranslateObjectInCodeToJSON(codeString);
JsonTool jt = new JsonTool();
//美化JSON字符串
resultJSON = jt.ConvertJsonString(resultJSON);
edtJson.Text = resultJSON;
#region 测试完成后屏蔽这段代码
//string destFolder = System.AppDomain.CurrentDomain.BaseDirectory;
//destFolder += ha_method.intf_code + @"\" + ha_method.m_code + @"\Model";
//DirectoryInfo di = new DirectoryInfo(destFolder);
//if (di.Exists == false)
//{
// Directory.CreateDirectory(destFolder);
//}
//bool writeClassRet = RunTimeHelper.GenClassStringByJSON(resultJSON, "ILAK.API", "ILAK.API.Model", destFolder, "Req" + ha_method.m_arggrpcode);
//if (!writeClassRet)
//{
// MessageBox.Show("写文件失败");
//}
#endregion
}
}
}
catch (Exception ex)
{
throw ex;
}
}
