private void CheckForNewServices(DbBase ctx, String configPath)
{
string[] confFiles = Directory.GetFiles(configPath, "*.conf");
foreach (string file in confFiles)
{
try
{
CASPluginConfig cfg = new CASPluginConfig();
try
{
cfg.LoadFromXML(new FileInfo(file));
}
catch (Exception ex)
{
throw new Exception("Error parsing config file '" + file + "'", ex);
}
Uri svc = CASPluginService.Normalize(cfg.Service);
//Verifica se o contexto é novo
if (ctx.ExecuteScalar<Int64>("select count(*) from [CAS_Context] where [Name] = '" + cfg.Context + "'") == 0)
{
//Adiciona
ctx.ExecuteNonQuery("INSERT INTO [CAS_Context] ([Name],[Host]) VALUES ('" + cfg.Context + "','" + Environment.MachineName + "');");
}
//Verifica se o serviço é novo
if (ctx.ExecuteScalar<Int64>("select count(*) from [CAS_Service] where [Uri] = '" + svc.AbsoluteUri + "'") == 0)
{
//Adiciona o serviço
ctx.ExecuteNonQuery("INSERT INTO [CAS_Service] ([Context_Name],[Uri],[Plugin_Assembly],[Permit_Password_Recover],[External_Password_Recover],[Password_RecoverUri],[Permit_Change_Password],[Admin]) VALUES ('" + cfg.Context + "','" + svc.AbsoluteUri + "','" + cfg.PluginAssembly + "'," + (cfg.PermitPasswordRecover ? 1 : 0) + "," + (cfg.ExternalPasswordRecover ? 1 : 0) + ",'" + cfg.PasswordRecoverUri + "'," + (cfg.PermitChangePassword ? 1 : 0) + ",'" + cfg.Admin + "');");
}
else
{
//Atualiza o serviço
ctx.ExecuteNonQuery("update [CAS_Service] set [Context_Name] = '" + cfg.Context + "', [Plugin_Assembly] = '" + cfg.PluginAssembly + "',[Permit_Password_Recover] = " + (cfg.PermitPasswordRecover ? 1 : 0) + ",[External_Password_Recover] = " + (cfg.ExternalPasswordRecover ? 1 : 0) + ",[Password_RecoverUri] = '" + cfg.PasswordRecoverUri + "',[Permit_Change_Password] = " + (cfg.PermitChangePassword ? 1 : 0) + ",[Admin] = '" + cfg.Admin + "' where [Uri] = '" + svc.AbsoluteUri + "'");
//Apaga as propriedades
ctx.ExecuteNonQuery("delete from [CAS_Service_Attributes] where [Service_Uri] = '" + svc.AbsoluteUri + "'");
}
//Adiciona as propriedades
foreach(String key in cfg.Attributes.Keys)
ctx.ExecuteNonQuery("INSERT INTO [CAS_Service_Attributes] ([Service_Uri],[Key],[Value]) VALUES ('" + svc.AbsoluteUri + "','" + key + "','" + (cfg.Attributes[key] is DateTime ? ((DateTime)cfg.Attributes[key]).ToString("o") : cfg.Attributes[key].ToString()) + "');");
}
catch(Exception ex) {
throw ex;
}
}
}
public Boolean Send(String mailFom, String smtpServer, String username, String password)
{
try
{
foreach (MailAddress mail in this.mailTo)
{
sendEmail(this.mailSubject, mail.Address, null, this.mailBody, this.isHtml, mailFom, smtpServer, username, password);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@message_id", typeof(Int64)).Value = this.messageId;
par.Add("@status", typeof(String)).Value = "Email enviado com sucesso";
par.Add("@description", typeof(String)).Value = "";
database.ExecuteNonQuery("UPDATE st_messages SET [status] = 'OK' WHERE id = @message_id; INSERT INTO st_messages_status (message_id,date,error,status,description) VALUES(@message_id,getdate(),1,@status,@description);", par);
return(true);
}
catch (Exception ex)
{
DbParameterCollection par = new DbParameterCollection();
par.Add("@message_id", typeof(Int64)).Value = this.messageId;
par.Add("@status", typeof(String)).Value = "Erro no tratamento das variáveis";
par.Add("@description", typeof(String)).Value = ex.Message;
database.ExecuteNonQuery("UPDATE st_messages SET [status] = 'PE' WHERE id = @message_id; INSERT INTO st_messages_status (message_id,date,error,status,description) VALUES(@message_id,getdate(),1,@status,@description);", par);
return(false);
}
}
private void RunInDatabase(DbBase ctx, Object transaction, IEnumerable <ICreateScript> scripts, double minSerial)
{
foreach (ICreateScript item in scripts)
{
if (item.Serial < minSerial)
{
continue;
}
try
{
if (!string.IsNullOrEmpty(item.Precondition))
{
var preConditionResult = ctx.ExecuteScalar <Int64>(item.Precondition, transaction);
if (preConditionResult == 0)
{
continue;
}
}
ctx.ExecuteNonQuery(item.Command, transaction);
}
catch (Exception ex)
{
throw new Exception(ex.Message + " on execute script " + item.GetType().Name + " (" + item.Serial + ")", ex);
}
}
}
public void Destroy(DbBase database)
{
if ((this.Service == null) || (this.GrantTicket == null))
{
return;
}
database.ExecuteNonQuery(String.Format("delete from [CAS_Ticket] where [Service_Uri] = '{0}' and Grant_Ticket = '{1}'", CASPluginService.Normalize(this.Service).AbsoluteUri, this.GrantTicket));
}
/// <summary>
/// 初始化库存(每天晚上执行一次，纠正错误的库存)
/// </summary>
public void initStock()
{
string sql = @" TRUNCATE TABLE Stock1
INSERT INTO Stock1 (id,iden,Stock)
SELECT id,iden,SUM(Stock)Stock FROM View_Stock1 GROUP BY id,iden
DECLARE @@tt TABLE(id VARCHAR(20),stock DECIMAL )
insert into @@tt select a1.id,a1.Stock*b1.sum Stock from Stock1 a1 INNER JOIN
(
SELECT a.id,a.iden,c.sum FROM Stock1 a INNER JOIN dbo.Link b ON a.id=b.t_id AND a.iden=b.iden
INNER JOIN Link_Mid c ON b.id=c.id AND b.iden=c.iden WHERE c.StockType<>1
) b1 ON a1.id=b1.id AND a1.iden=b1.iden
UPDATE Stock1 SET Stock=b2.Stock FROM Stock1 a1 INNER JOIN @@tt b2 ON a1.id=b2.id where a1.Stock<>b2.Stock";
DbBase db = new DbBase();
db.ChangeDBData_Centre();
db.ExecuteNonQuery(sql, 60 * 10);
db.ChangeDBShop();
sql = string.Format("UPDATE dbo.Product SET Stock=b.Stock FROM Product a INNER JOIN {0}.dbo.Stock1 b ON a.spid=b.id WHERE a.Stock<>b.Stock", System.Configuration.ConfigurationManager.AppSettings["centredb"]);
db.ExecuteNonQuery(sql);
}
public void SaveToDb(DbBase database)
{
//Exclui ticket com o mesmo serviço e grant ticket
database.ExecuteNonQuery(String.Format("delete from [CAS_Ticket] where [Service_Uri] = '{0}' and Grant_Ticket = '{1}'", CASPluginService.Normalize(this.Service).AbsoluteUri, this.GrantTicket));
if (!this.Success)
{
return;
}
//Adiciona o ticket
database.ExecuteNonQuery(String.Format("insert into [CAS_Ticket] ([Service_Uri],[User_Id],[User_Name],[Grant_Ticket],[Long_Ticket],[Proxy_Ticket],[Create_Date],[Expires],[Create_By_Credentials]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8})",
CASPluginService.Normalize(this.Service).AbsoluteUri,
this.UserId,
this.UserName,
this.GrantTicket,
this.LongTicket,
this.ProxyTicket,
this.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"),
this.Expires.ToString("yyyy-MM-dd HH:mm:ss"),
(this.CreateByCredentials ? 1 : 0)));
}
/// <summary>
/// 更新库存数量
/// </summary>
/// <param name="spid"></param>
/// <param name="iden"></param>
public void UpdateStock(int spid, int iden)
{
string sql = string.Format(@"SELECT pihao,sxrq,(SELECT ISNULL(SUM(Stock),0) FROM dbo.View_Stock1 WHERE iden=v.iden AND id=v.id)Stock,ISNULL((SELECT sum FROM dbo.Link_Mid WHERE iden=v.iden AND id=(SELECT id FROM dbo.Link WHERE iden={0} AND spid={1}) AND StockType>1),1)sum FROM dbo.View_Stock1 v WHERE iden={0} AND id=((SELECT Product_id FROM dbo.Product_DEF WHERE id={1})) ORDER BY sxrq ASC", iden, spid);
DataTable dt = ExecuteTable(sql, 120);
if (dt.Rows.Count > 0)
{
decimal sum = 0;
decimal.TryParse(dt.Rows[0]["sum"].ToString(), out sum);
if (sum > 1)
{
dt.Rows[0]["Stock"] = Convert.ToDecimal(sum * Convert.ToDecimal(dt.Rows[0]["Stock"].ToString()));
}
BLL.DbBase bll = new DbBase();
bll.ChangeDBShop();
//按库存含义更新库存数量
sql = string.Format("UPDATE Product SET Stock={2},pihao='{3}',sxrq='{4}' WHERE Product_ID={0}", spid, iden, dt.Rows[0]["Stock"], dt.Rows[0]["pihao"], dt.Rows[0]["sxrq"]);
bll.ExecuteNonQuery(sql);
}
}
public static void WriteSelectionScratchTable(DataColumn[] targetColumns, DataTable idList)
{
try
{
int incidOrdinal = -1;
if (idList.Columns.Contains(_incidTable.incidColumn.ColumnName))
{
if (idList.Columns[_incidTable.incidColumn.ColumnName].DataType == _hluLayerStructure.incidColumn.DataType)
{
incidOrdinal = idList.Columns[_incidTable.incidColumn.ColumnName].Ordinal;
}
else
{
return;
}
}
else
{
var q = idList.Columns.Cast <DataColumn>().Where(c => c.ColumnName.EndsWith(
Resources.ColumnTableNameSeparator + _incidTable.incidColumn.ColumnName) &&
c.DataType == _incidTable.incidColumn.DataType);
if (q.Count() == 1)
{
incidOrdinal = q.ElementAt(0).Ordinal;
}
else
{
return;
}
}
// incid column always has the same name as in the GIS layer structure
if (idList.Columns[incidOrdinal].ColumnName != _hluLayerStructure.incidColumn.ColumnName)
{
idList.Columns[incidOrdinal].ColumnName = _hluLayerStructure.incidColumn.ColumnName;
}
try
{
_scratchDb.ExecuteNonQuery(String.Format("DROP TABLE {0}", _scratchSelTable),
_scratchDb.Connection.ConnectionTimeout, CommandType.Text);
}
catch { }
if (String.IsNullOrEmpty(idList.TableName))
{
idList.TableName = _scratchSelTable;
}
if ((idList.PrimaryKey == null) || (idList.PrimaryKey.Length == 0))
{
idList.PrimaryKey = new DataColumn[] { idList.Columns[incidOrdinal] }
}
;
if (!_scratchDb.CreateTable(idList))
{
return;
}
DataTable scratchTable = idList.Clone();
DataSet datasetOut = new DataSet(_scratchSelTable);
IDbDataAdapter adapterOut = _scratchDb.CreateAdapter(scratchTable);
adapterOut.Fill(datasetOut);
adapterOut.TableMappings.Clear();
adapterOut.TableMappings.Add(scratchTable.TableName, datasetOut.Tables[0].TableName);
scratchTable = datasetOut.Tables[0];
foreach (DataRow r in idList.Rows)
{
scratchTable.LoadDataRow(r.ItemArray, false);
}
adapterOut.Update(datasetOut);
}
catch { }
finally
{
if ((_scratchDb != null) && (_scratchDb.Connection.State != ConnectionState.Closed))
{
try { _scratchDb.Connection.Close(); }
catch { }
}
}
}
public override void ExecuteNonQuery(String command, CommandType commandType, DbParameterCollection parameters, Object transaction)
{
baseDB.ExecuteNonQuery(command, commandType, parameters, transaction);
}
/// <summary>
/// 增加一条数据 返回是否默认货源
/// </summary>
public bool Add(DSWebService.Model.Data_Centre.Link model)
{
BLL.DbBase db1 = new DbBase();
db1.ChangeDBShop();
//如果选择的数据是有件装的，则直接映射
string sql = "SELECT Goods_Pcs+Goods_Pcs_Small FROM dbo.Goods_Package WHERE Goods_Package_ID=" + model.id;
object ret = db1.ExecuteScalar(sql);
if ((ret == null?0:(int)ret) > 2)
{
goto label1;
}
sql = string.Format("SELECT TOP 1 Goods_Pcs,Goods_Pcs_Small FROM dbo.Product WHERE id='{0}' AND iden={1}", model.t_id, model.iden);
#region 添加转件装中包装
var dt = ExecuteTable(sql);
int Goods_Pcs = (int)dt.Rows[0]["Goods_Pcs"];
int Goods_Pcs_Small = (int)dt.Rows[0]["Goods_Pcs_Small"];
if ((Goods_Pcs + Goods_Pcs_Small) > 2)
{
db1.ChangeDBShop();
sql = string.Format("SELECT Goods_Pcs,Goods_Pcs_Small,Goods_ID,DrugsBase_ID,Goods_Unit_ID FROM Goods_Package WHERE Goods_Package_ID={0}", model.id);
var dr = db1.ExecuteTable(sql).Rows[0];
int Goods_Pcs1 = (int)dr["Goods_Pcs"];
int Goods_Pcs_Small1 = (int)dr["Goods_Pcs_Small"];
if (Goods_Pcs1 == Goods_Pcs && Goods_Pcs_Small1 == Goods_Pcs_Small)
{
goto label1;
}
if ((Goods_Pcs1 + Goods_Pcs_Small1) < 3)
{
//更新中包装，件装
sql = string.Format("UPDATE Goods_Package SET Goods_Pcs={1},Goods_Pcs_Small={2} WHERE Goods_Package_ID={0}", model.id, Goods_Pcs, Goods_Pcs_Small);
db1.ExecuteNonQuery(sql);
goto label1;
}
if (Goods_Pcs1 == Goods_Pcs && Goods_Pcs_Small1 < 2)
{
//更新中包装
sql = string.Format("UPDATE Goods_Package SET Goods_Pcs_Small={1} WHERE Goods_Package_ID={0}", model.id, Goods_Pcs_Small);
db1.ExecuteNonQuery(sql);
goto label1;
}
if (Goods_Pcs1 == Goods_Pcs && Goods_Pcs_Small1 != Goods_Pcs_Small)
{
goto label2;
}
if (Goods_Pcs1 != Goods_Pcs && Goods_Pcs_Small1 != Goods_Pcs_Small)
{
goto label2;
}
label2:
//增加新的包装，件装
sql = string.Format("SELECT Goods_Package_ID FROM dbo.Goods_Package WHERE Goods_ID={0} AND Goods_Pcs={1} AND Goods_Pcs_Small={2}", dr["Goods_ID"], Goods_Pcs, Goods_Pcs_Small);
object o = db1.ExecuteScalar(sql);
if (Library.Lang.DataValidator.isNumber(o))
{
model.id = int.Parse(o.ToString());
goto label1;
}
sql = string.Format("SELECT TOP 1 Goods_Package_ID FROM dbo.Goods_Package WHERE Goods_ID={0} AND Goods_Pcs={1} AND Goods_Pcs_Small<2", dr["Goods_ID"], Goods_Pcs, 0);
o = db1.ExecuteScalar(sql);
if (Library.Lang.DataValidator.isNumber(o))
{
sql = string.Format("UPDATE Goods_Package SET Goods_Pcs_Small={2} WHERE Goods_ID={0} AND Goods_Pcs={1} AND Goods_Pcs_Small<2", dr["Goods_ID"], Goods_Pcs, Goods_Pcs_Small);
db1.ExecuteNonQuery(sql);
model.id = int.Parse(o.ToString());
goto label1;
}
sql = string.Format("INSERT INTO Goods_Package(DrugsBase_ID,Goods_ID,Goods_Unit_ID,Goods_Pcs,Goods_Pcs_Small) VALUES({0},{1},{2},{3},{4});SELECT @@IDENTITY", dr["DrugsBase_ID"], dr["Goods_ID"], dr["Goods_Unit_ID"], Goods_Pcs, Goods_Pcs_Small);
model.id = int.Parse(db1.ExecuteScalar(sql).ToString());
}
#endregion
label1:
object spid = ExecuteScalar(string.Format("SELECT id FROM Product_DEF WHERE Product_id='{0}' and iden='{1}'", model.t_id, model.iden));
if (!Library.Lang.DataValidator.isNumber(spid))
{
new BLL.Data_Centre.Product_Centre().IdenData(model.iden);
}
spid = ExecuteScalar(string.Format("SELECT id FROM Product_DEF WHERE Product_id='{0}' and iden='{1}'", model.t_id, model.iden));
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Link(");
strSql.Append("id,spid,t_id,iden,created,updated,is_default)");
strSql.Append(" values (");
strSql.Append("@id,@spid,@t_id,@iden,@created,@updated,@is_default)");
model.is_default = false;
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "id", DbType.Int32, model.id);
db.AddInParameter(dbCommand, "spid", DbType.String, spid);
db.AddInParameter(dbCommand, "t_id", DbType.String, model.t_id);
db.AddInParameter(dbCommand, "iden", DbType.Int32, model.iden);
db.AddInParameter(dbCommand, "created", DbType.DateTime, model.created);
db.AddInParameter(dbCommand, "updated", DbType.DateTime, model.updated);
db.AddInParameter(dbCommand, "is_default", DbType.Boolean, model.is_default);
db.ExecuteNonQuery(dbCommand);
return(model.is_default);
}
public void SaveToDb(DbBase database, Object transaction)
{
//Salva no banco, resgata o message key e depois atualiza o body no db
Dictionary <String, String> vars = new Dictionary <String, String>();
try
{
MatchCollection ms = Regex.Matches(this.mailBody, @"%(.*?)%", RegexOptions.IgnoreCase);
foreach (Match m in ms)
{
if (!vars.ContainsKey(m.Groups[1].Value.ToLower()))
{
vars.Add(m.Groups[1].Value.ToLower(), "");
}
}
ms = Regex.Matches(this.mailSubject, @"%(.*?)%", RegexOptions.IgnoreCase);
foreach (Match m in ms)
{
if (!vars.ContainsKey(m.Groups[1].Value.ToLower()))
{
vars.Add(m.Groups[1].Value.ToLower(), "");
}
}
if (this.variables != null)
{
List <String> ks = new List <string>();
ks.AddRange(vars.Keys);
foreach (String k in ks)
{
foreach (String k1 in this.variables.Keys)
{
if (k1.ToLower() == k)
{
vars[k] = this.variables[k1];
break;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Erro on build enviroment variables", ex);
}
foreach (MailAddress mail in this.mailTo)
{
String newBody = this.mailBody;
String newSubject = this.mailSubject;
try
{
try
{
//Imagem para marcação de leitura da mensagem
if (this.isHtml) //A tag |message_key| será substituida automaticamente na procedure pela key da mensagem
{
newBody += "<img src=\"%enterprise_uri%/m/v/|message_key|\" width=\"1\" heigh=\"1\" />";
}
if (!vars.ContainsKey("enterprise_uri"))
{
vars.Add("enterprise_uri", "");
}
vars["enterprise_uri"] = this.serverUri.Scheme + "://" + serverUri.Host + (serverUri.IsDefaultPort ? "" : ":" + serverUri.Port);
if (vars.ContainsKey("mail"))
{
vars["mail"] = mail.Address;
}
foreach (String k in vars.Keys)
{
newBody = Regex.Replace(newBody, "%" + k + "%", vars[k], RegexOptions.IgnoreCase);
newSubject = Regex.Replace(newSubject, "%" + k + "%", vars[k], RegexOptions.IgnoreCase);
}
}
catch (Exception ex)
{
throw new Exception("Erro on build enviroment variables", ex);
}
using (DbParameterCollection par = new DbParameterCollection())
{
par.Add("@enterprise_id", typeof(Int64)).Value = this.enterpriseId;
par.Add("@send_to", typeof(String)).Value = mail.Address;
par.Add("@is_html", typeof(Boolean)).Value = this.isHtml;
par.Add("@subject", typeof(String)).Value = newSubject;
par.Add("@body", typeof(String)).Value = newBody;
DataTable dtMessage = database.ExecuteDataTable("sp_st_new_message", CommandType.StoredProcedure, par, transaction);
if ((dtMessage != null) && (dtMessage.Rows.Count > 0))
{
try
{
newBody = dtMessage.Rows[0]["body"].ToString();//Pega o body atualizado pois há tags nele que a procedure atualiza
HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(newBody);
Boolean renew = false;
//Substitui os links
HtmlNodeCollection aLinks = doc.DocumentNode.SelectNodes("//a[@href]");
if (aLinks != null)
{
foreach (HtmlNode link in aLinks)
{
HtmlAttribute att = link.Attributes["href"];
using (DbParameterCollection par2 = new DbParameterCollection())
{
par2.Add("@message_id", typeof(Int64)).Value = dtMessage.Rows[0]["id"];
par2.Add("@link", typeof(String)).Value = att.Value;
String linkKey = database.ExecuteScalar <String>("sp_st_new_message_link", CommandType.StoredProcedure, par2, transaction);
newBody = newBody.Replace(att.Value, this.serverUri.Scheme + "://" + serverUri.Host + (serverUri.IsDefaultPort ? "" : ":" + serverUri.Port) + "/m/l/" + linkKey);
renew = true;
//
}
}
}
//Se houver links atualiza o body
if (renew)
{
using (DbParameterCollection par2 = new DbParameterCollection())
{
par2.Add("@message_id", typeof(Int64)).Value = dtMessage.Rows[0]["id"];
par2.Add("@body", typeof(String)).Value = newBody;
database.ExecuteNonQuery("update [st_messages] set body = @body where id = @message_id", CommandType.Text, par2, transaction);
}
}
}
catch { }
}
//database.ExecuteNonQuery("UPDATE st_messages SET [status] = 'E' WHERE id = @message_id; INSERT INTO st_messages_status (message_id,date,error,status,description) VALUES(@message_id,getdate(),1,@status,@description);", par);
}
}
finally
{
newBody = null;
newSubject = null;
}
}
}