public void InsertPositionRecommendations(List <PositionRecommendation> recommendations)
{
string login = HttpContext.Current.Request.ServerVariables["Auth_User"];
if (login.IndexOf("\\") != -1)
{
login = login.Split('\\')[1];
}
if (recommendations != null)
{
for (int i = 0; i < recommendations.Count(); i++)
{
using (var db = new DBCommand("InsertPositionRecommendations"))
{
db.AddParameter("@tagSecurityId", recommendations[i].TagSecurityId);
db.AddParameter("@pros", recommendations[i].Pros);
db.AddParameter("@cons", recommendations[i].Cons);
db.AddParameter("@convertAllocation", recommendations[i].ConvertAllocation);
db.AddParameter("@login", login);
db.ExecuteNonQuery();
}
}
}
}
public User Add(User user)
{
if (user is null)
{
throw new NullReferenceException($"User Data empty ({where}) (ADD)");
}
if (user.Email.Length == 0 || user.Passwd.Length == 0)
{
throw new DataException("Email &/or Password Data empty (" + where + ") ADD)");
}
if (EmailIsUsed(user.Email, 0))
{
throw new DataException("$Email Is already Used ({where}) ADD)");
}
if (NatRegNbrIsUsed(user.NatRegNbr, 0))
{
throw new DataException($"National Register Nbr Is already Used ({where}) ADD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_AddUser]", true);
command.AddParameter("Email", user.Email);
command.AddParameter("NatRegNbr", user.NatRegNbr);
command.AddParameter("Passwd", user.Passwd);
command.AddParameter("LastName", user.LastName);
command.AddParameter("FirstName", user.FirstName);
user.Id = (int)_connection.ExecuteScalar(command);
user.Passwd = "";
return(user);
}
/// <summary>
/// 根据仓库，楼层，仓位查询目前存粮情况
/// </summary>
/// <param name="whId">仓库ID</param>
/// <param name="floor">楼层数</param>
/// <param name="wpId">仓位ID</param>
/// <returns></returns>
public static DataTable GetStorageGrainByStorageId(string whId, string floor, string wpId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select sum(WPGBaseQty) as weight
from View_WarePlaceGrain where WHID=@WHID";
cmd.ClearParameters();
if (!string.IsNullOrEmpty(floor))
{
sql += " and Floor=@Floor";
cmd.AddParameter("@Floor", floor);
}
if (!string.IsNullOrEmpty(wpId))
{
sql += " and WPID=@WPID";
cmd.AddParameter("@WPID", wpId);
}
cmd.setCommandText(sql);
cmd.AddParameter("@WHID", whId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
public int Check_DateT(Currency_Exchange cxc)
{
try
{
if (cxc is null)
{
throw new DataException("Currency Exchange Data empty (" + where + ") (CT)");
}
if (cxc.CurrFrom.Length != 3)
{
throw new DataException("Issue with Data entered for Add (" + where + ") (CT1)");
}
if (cxc.CurrTo.Length != 3)
{
throw new DataException("Issue with Data entered for Add (" + where + ") (CT2)");
}
DBCommand command = new DBCommand("[AppUser].[Check_CX_DateT]", true);
command.AddParameter("CurrF", cxc.CurrFrom);
command.AddParameter("CurrT", cxc.CurrTo);
command.AddParameter("Date2Chk", cxc.DateFrom);
return((int)ServiceLocator.Instance.Connection.ExecuteScalar(command));
}
catch (Exception e) { throw e; }
}
public void Update(int id, Types t)
{
DBCommand command = new DBCommand("[RegChacha].[Type_Update]", true);
command.AddParameter("Id", id);
command.AddParameter("Type", t.Type);
_connection.ExecuteNonQuery(command);
}
public void Update(int id, Series s)
{
DBCommand command = new DBCommand("[RegChacha].[Series_Update]", true);
command.AddParameter("Id", id);
command.AddParameter("Nom", s.Nom);
_connection.ExecuteNonQuery(command);
}
public TimeLine Get(int userId, int id)
{
DBCommand command = new DBCommand(Str_Get + " Where [Id] = @Id AND [UserId] = @UserID;");
command.AddParameter("Id", id);
command.AddParameter("UserId", userId);
return(_connection.ExecuteReader(command, dr => dr.ToTimeLine()).SingleOrDefault());
}
public bool VATIsUsed(string vat, int id)
{
DBCommand command = new DBCommand("[PandUser].[DCOSP_CheckVAT]", true);
command.AddParameter("VAT", vat);
command.AddParameter("Id", id);
return((bool)_connection.ExecuteScalar(command));
}
public bool Del(int userId, int id)
{
DBCommand command = new DBCommand("[PandUser].[DCOSP_DelTimeLine]", true);
command.AddParameter("Id", id);
command.AddParameter("UserId", userId);
return(_connection.ExecuteNonQuery(command) == 1);
}
public IEnumerable <Music> GetPage(int page, int jump)
{
try
{
DBCommand command = new DBCommand("[AppUser].[PaginateMusic]", true);
command.AddParameter("page", page);
command.AddParameter("jump", jump);
return(ServiceLocator.Instance.Connection.ExecuteReader(command, dr => dr.ToMusic()));
}
catch (Exception e) { throw e; }
}
public void UpdateICMeeting(long meetingId, string meetingDate, bool?meetingStart3PM, string meetingStartTime, string meetingEndTime, bool?origNotes1PageLength, int?origLengthOfNotes, int?finalLengthOfNotes, string linkToMeetingDoc, bool?isLocked, List <MeetingAttributesModel> meetingAttributesList)
{
string login = HttpContext.Current.Request.ServerVariables["Auth_User"];
if (login.IndexOf("\\") != -1)
{
login = login.Split('\\')[1];
}
using (var db = new DBCommand("UpdateICMeeting"))
{
db.AddParameter("@meetingId", meetingId);
db.AddParameter("@meetingDate", meetingDate);
db.AddParameter("@meetingStart3PM", meetingStart3PM);
db.AddParameter("@meetingStartTime", meetingStartTime);
db.AddParameter("@meetingEndTime", meetingEndTime);
db.AddParameter("@origNotes1PageLength", origNotes1PageLength);
db.AddParameter("@orgiLengthOfNotes", origLengthOfNotes);
db.AddParameter("@finalLengthOfNotes", finalLengthOfNotes);
db.AddParameter("@linkToMeetingDoc", linkToMeetingDoc);
db.AddParameter("@isLocked", isLocked);
// db.AddParameter("@modifiedBy", login);
db.ExecuteNonQuery();
}
//DeleteMeetingAttributes(meetingId);
string symbolList = string.Empty;
for (int i = 0; i < meetingAttributesList.Count; i++)
{
InsertMeetingAttributes(meetingAttributesList[i].Symbol, meetingId, meetingAttributesList[i].Attendees, meetingAttributesList[i].NonAttendees, meetingAttributesList[i].RequiredRecommendation, meetingAttributesList[i].Securities);
if (i == 0)
{
symbolList += "'" + meetingAttributesList[i].Symbol + "'";
}
else
{
symbolList += "," + "'" + meetingAttributesList[i].Symbol + "'";
}
}
using (var db = new DBCommand("ICMeeting_DeleteSymbolList"))
{
db.AddParameter("@symbolList", symbolList);
db.AddParameter("@meetingId", meetingId);
db.ExecuteNonQuery();
}
}
public bool Del(string fldname, string value)
{
try
{
DBCommand command = new DBCommand("[AppUser].[DelCodeMstr]", true);
command.AddParameter("code_fldname", fldname);
command.AddParameter("code_value", value);
return(ServiceLocator.Instance.Connection.ExecuteNonQuery(command) == 1);
}
catch (Exception e) { throw e; }
}
public void SendPositionRecommendationsEmail(long tagId, string addendumText)
{
string message = GetPositionRecommendationsHistoryHtmlEmail(tagId, addendumText);
using (var db = new DBCommand("SendICMeetingNotesEmail"))
{
db.AddParameter("@to", "*****@*****.**");
db.AddParameter("@subject", "Position Recommendations");
db.AddParameter("@message", message);
db.ExecuteNonQuery();
}
}
public void Add(Country ctry)
{
if (ctry is null)
{
throw new NullReferenceException($"Country Data empty ({where}) (ADD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_AddCtry]", true);
command.AddParameter("ISO", ctry.ISO);
command.AddParameter("Ctry", ctry.Ctry);
command.AddParameter("IsEu", ctry.IsEU);
_connection.ExecuteScalar(command);
}
public bool EmailIsUsed(string email, int userId)
{
if (email.Length == 0)
{
throw new DataException($"EMAIL Data empty ({where}) (USED)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_CheckEmail]", true);
command.AddParameter("Email", email ?? (object)DBNull.Value);
command.AddParameter("Id", userId);
return((bool)_connection.ExecuteScalar(command));
}
public User LoginNRN(string natRegNbr, string passwd)
{
if (natRegNbr.Length == 0 || passwd.Length == 0)
{
throw new DataException($"NatRegNbr &/or Password Data empty ({where}) (LOGIN))");
}
DBCommand command = new DBCommand("[PandUser].[DCO_CheckUserNatRegNbr]", true);
command.AddParameter("NatRegNbr", natRegNbr);
command.AddParameter("Passwd", passwd);
return(_connection.ExecuteReader(command, r => r.ToUser()).SingleOrDefault());
}
public bool NatRegNbrIsUsed(String natRegNbr, int userId)
{
if (natRegNbr.Length == 0)
{
throw new DataException($"National Register Nbr Data empty ({where}) (USED)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_CheckNatRegNbr]", true);
command.AddParameter("natRegNbr", natRegNbr ?? (object)DBNull.Value);
command.AddParameter("Id", userId);
return((bool)_connection.ExecuteScalar(command));
}
public bool Upd(Country ctry)
{
if (ctry is null)
{
throw new NullReferenceException($"Country Data empty ({where}) (UPD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_UpdCtry]", true);
command.AddParameter("ISO", ctry.ISO);
command.AddParameter("Ctry", ctry.Ctry);
command.AddParameter("IsEu", ctry.IsEU);
return(_connection.ExecuteNonQuery(command) == 1);
}
/// <summary>
/// 查询统计托盘使用情况
/// </summary>
/// <param name="beginTime">开始日期</param>
/// <param name="beginTime">结束日期</param>
/// <returns></returns>
public static DataTable GetGroupSalver(DateTime beginTime, DateTime endTime)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.StoredProcedure);
cmd.ClearParameters();
cmd.setCommandText("PRO_GROUPSALVER");
cmd.AddParameter("@beginDate", beginTime);
cmd.AddParameter("@endDate", endTime);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
public User Login(string email, string passwd)
{
if (email.Length == 0 || passwd.Length == 0)
{
throw new DataException($"Email &/or Password Data empty ({where}) (LOGIN))");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_CheckUser]", true);
command.AddParameter("Email", email);
command.AddParameter("Passwd", passwd);
return(_connection.ExecuteReader(command, r => r.ToUser()).SingleOrDefault());
}
public void InsertICMeeting(string meetingDate, bool?meetingStart3PM, string meetingStartTime, string meetingEndTime, bool?origNotes1PageLength, int?origLengthOfNotes, int?finalLengthOfNotes, string linkToMeetingDoc, bool?isLocked, List <MeetingAttributesModel> meetingAttributesList)
{
string login = HttpContext.Current.Request.ServerVariables["Auth_User"];
if (login.IndexOf("\\") != -1)
{
login = login.Split('\\')[1];
}
long meetingId;
using (var db = new DBCommand("ICMeeting_GetMeetingId"))
{
db.AddParameter("@meetingDate", meetingDate);
db.AddParameter("@meetingId", System.Data.ParameterDirection.Output, System.Data.DbType.String);
db.ExecuteNonQuery();
meetingId = db.GetOutputParameter("@meetingId").ToLng();
}
if (meetingId == 0)
{
using (var db = new DBCommand("InsertICMeeting"))
{
db.AddParameter("@meetingDate", meetingDate);
db.AddParameter("@meetingStart3PM", meetingStart3PM);
db.AddParameter("@meetingStartTime", meetingStartTime);
db.AddParameter("@meetingEndTime", meetingEndTime);
db.AddParameter("@origNotes1PageLength", origNotes1PageLength);
db.AddParameter("@orgiLengthOfNotes", origLengthOfNotes);
db.AddParameter("@finalLengthOfNotes", finalLengthOfNotes);
db.AddParameter("@linkToMeetingDoc", linkToMeetingDoc);
db.AddParameter("@isLocked", isLocked);
db.AddParameter("@meetingId", System.Data.ParameterDirection.Output, System.Data.DbType.String);
db.AddParameter("@createdBy", login);
db.ExecuteNonQuery();
meetingId = Int64.Parse(db.GetOutputParameter("@meetingId").ToString());
}
for (int i = 0; i < meetingAttributesList.Count; i++)
{
InsertMeetingAttributes(meetingAttributesList[i].Symbol, meetingId, meetingAttributesList[i].Attendees, meetingAttributesList[i].NonAttendees, meetingAttributesList[i].RequiredRecommendation, meetingAttributesList[i].Securities);
}
}
else
{
UpdateICMeeting(meetingId, meetingDate, meetingStart3PM, meetingStartTime, meetingEndTime, origNotes1PageLength, origLengthOfNotes, finalLengthOfNotes, linkToMeetingDoc, isLocked, meetingAttributesList);
}
}
public bool Upd(Currency cur)
{
try
{
if (cur is null)
{
throw new DataException("Currency Data empty (" + where + ") (UPD)");
}
DBCommand command = new DBCommand("[AppUser].[UpdCurr]", true);
command.AddParameter("Curr", cur.Curr);
command.AddParameter("Desc", cur.Desc);
return(ServiceLocator.Instance.Connection.ExecuteNonQuery(command) == 1);
}
catch (Exception e) { throw e; }
}
public bool Upd(int id, TimeLine tl)
{
if (tl is null)
{
throw new NullReferenceException($"TimeLine Data empty ({where}) (UPD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_UpdTimeLine]", true);
command.AddParameter("Id", id);
command.AddParameter("UserId", tl.UserId);
command.AddParameter("RestaurantId", tl.RestaurantId);
command.AddParameter("DinerDate", tl.DinerDate);
command.AddParameter("NbrGuests", tl.NbrGuests);
return(_connection.ExecuteNonQuery(command) == 1);
}
public bool Upd(MusicType mt)
{
try
{
if (mt is null)
{
throw new DataException("Music Type Data empty (" + where + ") (UPD)");
}
DBCommand command = new DBCommand("[AppUser].[UpdMusicType]", true);
command.AddParameter("Id", mt.Id);
command.AddParameter("Name", mt.Name);
return(1 == ServiceLocator.Instance.Connection.ExecuteNonQuery(command));
}
catch (Exception e) { throw e; }
}
public void Add(Currency cur)
{
try
{
if (cur is null)
{
throw new DataException("Currency Data empty (" + where + ") (ADD)");
}
DBCommand command = new DBCommand("[AppUser].[AddCurr]", true);
command.AddParameter("Curr", cur.Curr);
command.AddParameter("Desc", cur.Desc);
ServiceLocator.Instance.Connection.ExecuteScalar(command);
}
catch (Exception e) { throw e; }
}
public Code_Mstr Get(Code_Mstr code)
{
try
{
if (code is null)
{
throw new DataException("Shop Data empty (" + where + ") (GET)");
}
DBCommand command = new DBCommand("[AppUser].[GetCodeMstrOne]", true);
command.AddParameter("code_fldname", code.code_fldname);
command.AddParameter("code_value", code.code_value);
return(ServiceLocator.Instance.Connection.ExecuteReader(command, dr => dr.ToCodeMstr()).SingleOrDefault());
}
catch (Exception e) { throw e; }
}
public TimeLine Add(TimeLine tl)
{
if (tl is null)
{
throw new NullReferenceException($"TimeLine Data empty ({where}) (ADD)");
}
DBCommand command = new DBCommand("[PandUser].[DCOSP_AddTimeLine]", true);
command.AddParameter("UserId", tl.UserId);
command.AddParameter("RestaurantId", tl.RestaurantId);
command.AddParameter("DinerDate", tl.DinerDate);
command.AddParameter("NbrGuests", tl.NbrGuests);
tl.Id = (int)_connection.ExecuteScalar(command);
return(tl);
}
public IEnumerable <TimeLine> Get(int userId)
{
DBCommand command = new DBCommand(Str_Get + "WHERE [UserId] = @UserID;");
command.AddParameter("UserId", userId);
return(_connection.ExecuteReader(command, dr => dr.ToTimeLine()));
}
/// <summary>
/// 查询获取粮食存放在仓库的位置和重量
/// </summary>
/// <param name="TEId">出入厂单据ID</param>
/// <returns></returns>
public static DataTable GetGrainStorage(string TEId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select te.*,t.PRMBNumber,m.MaterialNumber,m.MaterialName,wh.WHNumber,wh.WHName,wp.WPNumber,wp.WPName,
wpOut.WPNumber as OutWPNumber,wpOut.WPName as OutWPName
from TruckEir te
left join Material m on te.MaterialID=m.MaterialID
left join WareHouse wh on te.WHID=wh.WHID
left join WarePlace wp on te.WPID=wp.WPID
left join WarePlace wpOut on te.OutWPID=wpOut.WPID
left join (
select prid,prnumber as prmbnumber from postrequisition
union
select mbid as prid,mbnumber as prmbnumber from movelocationbill) t on te.prmbid=t.prid
where te.TEID=@TEId";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@TEId", TEId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
public Series GetOne(int id)
{
DBCommand command = new DBCommand("Select * from [RegChacha].[V_Series] where id=@id");
command.AddParameter("id", id);
return(_connection.ExecuteReader(command, (dr) => dr.ToSeries()).SingleOrDefault());
}