/// <summary>
/// Write vols back to DB - update if there already
/// </summary>
/// <param name="ins"></param>
/// <param name="PriceDate"></param>
internal void SaveTickerData_Volatility(List <BloombergDataInstrument> instruments, int Reference)
{
string sql = string.Format("OptionValueManagerVolatilityInsert {0}", Reference);
string ret = db.ExecSql(sql);
if (ret != "")
{
throw new Exception(string.Format("Error in {0}.\r\n{1}", "SaveTickerData_Volatility", ret));
}
}
internal string RunCommand(string Server, string cmd)
{
string ret = "";
string server = Server;
if (cmd.Length > 8 && cmd.Substring(0, 9).ToLower() == ":connect ")
{
// This is targeted against a specific server
server = cmd.Substring(9, cmd.IndexOfAny(new Char[] { '\r', '\n' }) - 9);
cmd = cmd.Substring(9 + server.Length);
}
SQLServer db = new SQLServer(server, "master", "", "");
try {
db.Timeout = 300; // Some processes are long
// Split commands that are seperated by a GO
string[] lines = cmd.Split(new Char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
string sql = "";
foreach (string line in lines)
{
if (line.Trim().ToUpper() == "GO")
{
ret = db.ExecSql(sql);
sql = "";
if (ret != "")
{
break;
}
}
else
{
sql += line + "\r\n";
}
}
if (sql != "")
{
ret = db.ExecSql(sql);
}
} catch (Exception ex) {
ret = "Error: " + ex.Message;
} finally {
db.Close();
}
return(ret);
}
internal static void RemoveWorkItemsSince(DateTime workSince)
{
GetDbConn();
string sql = "Delete WorkItem Where WorkDate >= '{0}'".Args(workSince.ToString("yyyyMMdd"));
SQLServer.ExecSql(conn, sql);
}
public static int EditAccountData(string PostData)
{
JObject jo = (JObject)JsonConvert.DeserializeObject(PostData);
int intResult = 99;
bool bol = false;
string Msg = "";
string str = "select id from t_AccountStatus where year=" + jo["Year"] + " and month=" + jo["Month"];
DataTable dt = SQLServer.GetDataTable(str, ref Msg);
if (dt != null && dt.Rows.Count > 0)
{
string id = dt.Rows[0][0].ToString();
str = "update t_AccountStatus set status=" + jo["Switch"] + " where id=" + id + "";
}
else if (dt != null && dt.Rows.Count == 0)
{
str = "insert into t_AccountStatus values(" + jo["Year"] + "," + jo["Month"] + "," + jo["Switch"] + ")";
}
bol = SQLServer.ExecSql(str, ref Msg);
if (bol == true)
{
intResult = 100;
}
return(intResult);
}
public static bool SaveCPXLDataSup(JArray ja)
{
string sql = "";
sql = "delete from t_chanpinxilie where xilieID=(select FItemID from t_Item where FName='" + ja[0]["cpxl"] + "')";
bol = SQLServer.ExecSql(sql, ref Msg);
if (bol == false)
{
return(bol);
}
for (int i = 0; i < ja.Count; i++)
{
sql = @"insert into t_chanpinxilie(xilieID,childmatID,supID) select it.FItemID,ic.FItemID,per.FItemID from (select FItemID from t_item where FName= '" + ja[i]["cpxl"] + "' ) it join (select FItemID from t_icitem where FNumber='" + ja[i]["mat"] + "') ic on 1=1 left join (select FItemID from t_Supplier where FNumber='" + ja[i]["sup"] + "') per on 1=1";
bol = SQLServer.ExecSql(sql, ref Msg);
if (bol == false)
{
return(bol);
}
}
return(true);
}
public void ExecSql(string sql, string failMessage, string DSN = "", int timeout = 0)
{
SqlCommand cmd = new SqlCommand(sql);
if (DSN == "")
{
DSN = Settings.Default.DSN;
}
SQLServer db = new SQLServer(DSN);
Utils.Log(string.Format("Calling DSN: {0} {1}", DSN, sql));
db.ConnectionTimeout = timeout;
db.Timeout = timeout;
//SQLServer db = new SQLServer(Settings.Default.DSN);
try
{
string ret = db.ExecSql(cmd);
if (ret != "")
{
Utils.Log(failMessage);
throw new Exception(failMessage + " " + ret);
}
}
finally
{
db.Close();
}
}
public static bool CPXLEditSup(string id, string sup, ref string msg)
{
string sql = "update t_chanpinxilie set supID=per.FItemID from(select*from t_Supplier where FNumber ='" + sup + "')per where id=" + id + "";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
/// <summary>
/// call the dm.ExecSQL method and raise an error if an error string is returned
/// </summary>
/// <param name="sqlCommand"></param>
public void ExecSql(SqlCommand sqlCommand)
{
string err = db.ExecSql(sqlCommand);
if (err != "")
{
throw new System.Exception("Could not execute command: " + err);
}
}
public static bool ChangePlugRole(string username, string rolename, ref string msg)
{
sql = "begin tran begin try update t_MyUserAccount set BelongToRole=ur.id from (select *from t_PlugUserRole where RoleName='" + rolename + "')ur where username='******'";
sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="cpxl"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static bool CPXLProductionDelete(string cpxl, ref string msg)
{
string sql = @"declare @delid int,@cpxl nvarchar(50)
select @cpxl=FItemID from t_Item where FItemClassID=(select FItemClassID from t_ItemClass where FName='产品系列分类') and FName='" + cpxl + @"'
select @delid=FID from t_xiliefenlei fl join t_Item it on fl.Series=it.FItemID where it.FItemID=@cpxl
delete from t_xiliefenlei where FID=@delid
delete from t_xiliefenleiEntry where FID=@delid";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
public static bool EditPlugRolePermission(string rolename, JArray rows)
{
sql = "begin tran begin try declare @id int select @id=id from t_PlugUserRole where RoleName='" + rolename + "' delete from t_PlugUserPermission where RoleID=@id";
for (int i = 0; i < rows.Count; i++)
{
sql += " insert into t_PlugUserPermission select @id," + rows[i]["id"] + "," + rows[i]["ResourceTypeID"];
}
sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran";
bol = SQLServer.ExecSql(sql, ref Msg);
return(bol);
}
/// <summary>
/// 批量删除产品系列供应商关系
/// </summary>
/// <param name="id"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static bool BatchDeleteCPXLSup(string[] id, ref string msg)
{
string sql = "delete from t_chanpinxilie where id in(";
sql += id[0];
for (int i = 1; i < id.Length; i++)
{
sql += "," + id[i];
}
sql += ")";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
public static bool CPXLCancelReview(string[] id, ref string msg)
{
string sql = "update t_chanpinxilie set status=0 where id in(";
sql += id[0];
for (int i = 1; i < id.Length; i++)
{
sql += "," + id[i];
}
sql += ")";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
internal static void AddInWorkItemPlaceHolder()
{
GetDbConn();
string sql = "EXEC UpdateWorkItem {0},'{1}','{2}','{3}',{4},'{5}',".Args(0, "n/a", DateTime.Now.ToString("yyyyMMdd"),
"n/a", 0, "LastUpdateTime");
sql += "'{0}','{1}',{2},{3},{4},'{5}'".Args("Last update placeholder",
"",
0,
0,
0,
DateTime.Now.AddMinutes(-5.0).ToString("yyyyMMdd HH:mm:ss"));
SQLServer.ExecSql(conn, sql);
}
public static bool SaveCPXLData(bool repeat, string xilie, string mat, string sup)
{
string sql = "";
if (repeat == true)
{
sql = @"update t_chanpinxilie set supID=a.supid from t_chanpinxilie cp join(select it.FItemID as itid,ic.FItemID as icid,per.FItemID as supid from t_item it , t_icitem ic,t_Supplier per where it.FName= '" + xilie + "' and ic.FNumber='" + mat + "' and per.FNumber='" + sup + "')a on cp.xilieID=itid and cp.childmatID=a.icid where xilieID=itid and childmatID=icid";
}
else
{
sql = @"insert into t_chanpinxilie(xilieID,childmatID,supID) select it.FItemID,ic.FItemID,per.FItemID from t_item it , t_icitem ic,t_Supplier per where it.FName= '" + xilie + "' and ic.FNumber='" + mat + "' and per.FNumber='" + sup + "'";
}
return(SQLServer.ExecSql(sql, ref Msg));
}
internal static void UpdateWorkItem(Model.WorkItem item)
{
GetDbConn();
string sql = "EXEC UpdateWorkItem {0},'{1}','{2}','{3}',{4},'{5}',".Args(item.ID, item.BusinessOwner, item.WorkDate,
item.Employee, item.Hours, item.Issue);
sql += "'{0}','{1}',{2},{3},{4},'{5}'".Args(item.IssueSummary.Replace("'", "''"),
item.WorkDescription.Replace("'", "''"),
item.LondonTrading == "Y" ? 1 : 0,
item.StockLoan == "Y" ? 1 : 0,
item.CreditTrading == "Y" ? 1 : 0,
item.UpdateTime.ToString("yyyyMMdd HH:mm:ss"));
SQLServer.ExecSql(conn, sql);
}
public static bool AddChanPinXiLieData(string PostData)
{
string Msg = "";
JArray ja = (JArray)JsonConvert.DeserializeObject(PostData);
JObject o = (JObject)ja[0];
bool bol = false;
if (ja != null && ja.Count > 0)
{
for (int i = 0; i < ja.Count; i++)
{
string str = "insert into t_chanpinxilie values(" + ja[i]["xilie"] + "," + ja[i]["material"] + "," + ja[i]["supplier"] + ")";
bol = SQLServer.ExecSql(str, ref Msg);
}
}
return(bol);
}
public static bool ImportLeaveData(string year, string month, DataTable exceltable)
{
string sql = "";
bool bol = false;
sql = "delete from t_ImportLeaveData where year=" + year + "and month= " + month + "";
for (int i = 0; i < exceltable.Rows.Count; i++)
{
string str1 = exceltable.Rows[i]["工号"].ToString().Trim();
string str2 = exceltable.Rows[i]["请假小时"].ToString().Trim();
sql += @" insert into t_ImportLeaveData(year,month,EmpID,LeaveHours) select " + year + "," + month + ",emp.FItemID," + str2 + " from t_Emp emp where emp.FNumber='" + str1 + "'";
bol = SQLServer.ExecSql(sql, ref Msg);
}
return(bol);
}
public static bool DeletePlugRole(string rolename, ref string msg)
{
sql = @"select 1 from t_PlugUserRole ur join t_MyUserAccount ua on ur.id=ua.BelongToRole where RoleName='" + rolename + "'";
DataTable dt = SQLServer.GetDataTable(sql, ref Msg);
if (dt != null && dt.Rows.Count > 0)
{
msg = "角色已被用户使用！";
return(false);
}
sql = "begin tran begin try delete from t_PlugUserRole where RoleName='" + rolename + "'";
sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
private static void ExecSql(SqlCommand cmd, string failMessage)
{
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
try
{
string ret = db.ExecSql(cmd);
if (ret != "")
{
throw new Exception(failMessage + " " + ret + "\r\n\r\n" + cmd.CommandText);
}
}
finally
{
db.Close();
}
}
/// <summary>
/// 保存
/// </summary>
/// <param name="cpxl"></param>
/// <param name="prd"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static bool CPXLProductionSave(string cpxl, string[] prd, ref string msg)
{
string sql = @"declare @delid int,@maxid int,@cpxl nvarchar(50)
select @cpxl=FItemID from t_Item where FItemClassID=(select FItemClassID from t_ItemClass where FName='产品系列分类') and FName='" + cpxl + @"'
select @delid=FID from t_xiliefenlei fl join t_Item it on fl.Series=it.FItemID where it.FItemID=@cpxl
delete from t_xiliefenlei where FID=@delid
delete from t_xiliefenleiEntry where FID=@delid
select @maxid=max(FID) from t_xiliefenlei
if @maxid is null set @maxid=0
insert into t_xiliefenlei(FID,Series) select @maxid+1,it.FItemID from t_Item it where it.FItemID=@cpxl
insert into t_xiliefenleiEntry(FID,FIndex,Material) select @maxid+1,ROW_NUMBER() OVER(ORDER BY FNumber),ic.FItemID from t_ICItem ic where ic.FItemID in(";
sql += prd[0];
for (int i = 1; i < prd.Length; i++)
{
sql += "," + prd[i];
}
sql += ")";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
public static bool AddPlugRole(string rolename, string remark, string creator, JArray rows, ref string msg)
{
sql = @"select 1 from t_PlugUserRole where RoleName='" + rolename + "'";
DataTable dt = SQLServer.GetDataTable(sql, ref Msg);
if (dt != null && dt.Rows.Count > 0)
{
msg = "用户名已存在";
return(false);
}
sql = "begin tran begin try declare @id int insert into t_PlugUserRole select '" + rolename + "',GETDATE(),u.id,'" + remark + "' from t_MyUserAccount u where u.username='******' select @id=max(id) from t_PlugUserRole";
for (int i = 0; i < rows.Count; i++)
{
sql += " insert into t_PlugUserPermission select @id," + rows[i]["id"] + "," + rows[i]["ResourceTypeID"];
}
sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran";
bol = SQLServer.ExecSql(sql, ref Msg);
msg = Msg;
return(bol);
}
internal string AlterObject(DbObject Ob, string ObjectText)
{
string ret = "";
// In order to avoid changing the CREATE keyword to ALTER we grab the permissions, drop the object and recreate it with the
// same text that was produced by sp_helptext
string permissionSql = GetPermissions(Ob);
string dropSql = "";
switch (Ob.ObjectType)
{
case "P":
dropSql = "DROP PROC [" + Ob.ObjectName + "]";
break;
case "V":
dropSql = "DROP VIEW [" + Ob.ObjectName + "]";
break;
case "FN":
case "IF":
dropSql = "DROP FUNCTION [" + Ob.ObjectName + "]";
break;
default:
throw new Exception(string.Format("Unhandled object type of {0} in AlterObject method.", Ob.ObjectType));
}
// Maybe a temporary entry, waiting for a decision on this
bool ok = true;
if (ObjectText.ToLower().Contains("openquery") && ObjectText.ToLower().Contains("remotedb.dbo.execkondorlive"))
{
ok = false;
}
if (ok)
{
SQLServer db = new SQLServer(Ob.Server, Ob.Database, "", "");
try {
string err = db.ExecSql("BEGIN TRAN");
if (err == "")
{
err = db.ExecSql(dropSql);
}
if (err == "")
{
err = db.ExecSql(ObjectText);
}
if (err == "")
{
err = db.ExecSql("COMMIT TRAN");
}
else
{
err += "\r\n" + db.ExecSql("IF @@trancount > 0 RollBack Tran");
}
if (err != "")
{
ret += err;
}
else
{
if (permissionSql != "")
{
ret += db.ExecSql(permissionSql);
}
}
} finally {
db.Close();
}
}
return(ret);
}
