public List<SelectListItem> GetListCategoryDropDown()
{
List<SelectListItem> list = new List<SelectListItem>();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Category", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
var toAdd = new SelectListItem();
toAdd.Text = drI["Title"].ToString();
toAdd.Value = drI["CategoryId"].ToString();
list.Add(toAdd);
}
}
drI.Close();
con.Close();
return list;
}
public List<Picture> GetListPicture(int ClientId)
{
List<Picture> list = new List<Picture>();
Picture ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Picture WHERE ClientId = " + ClientId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Picture();
ins.PictureId = Convert.ToInt32(drI["PictureId"]);
ins.PicUrl = drI["PicUrl"].ToString();
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public AnimalType GetAnimalType(int AnimalTypeId)
{
AnimalType ReturnObject = new AnimalType();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.GetAnimalType;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@AnimalTypeId", AnimalTypeId);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
ReturnObject.AnimalTypeId = Convert.ToInt32(drI["AnimalTypeId"]);
ReturnObject.Description = drI["Description"].ToString();
}
}
cmdI.Connection.Close();
con.Dispose();
return ReturnObject;
}
public List<SelectListItem> GetFields()
{
List<SelectListItem> obj = new List<SelectListItem>();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = new SqlCommand("SELECT * FROM Fields ", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
var result = new SelectListItem();
result.Text = drI["FieldName"].ToString();
result.Value = drI["FieldsId"].ToString();
obj.Add(result);
}
}
drI.Close();
con.Close();
con.Dispose();
return obj;
}
public int GetLastPictureId(int ClientId)
{
int returnValue = 0;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT TOP 1 PictureId FROM Picture WHERE ClientId=" + ClientId + " ORDER BY PictureId DESC", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
returnValue = Convert.ToInt32(drI["PictureId"]);
}
}
drI.Close();
con.Close();
return returnValue;
}
public List<Client> GetListClient()
{
List<Client> list = new List<Client>();
Client ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Client", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Client();
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.Name = drI["Name"].ToString();
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public List<SelectListItem> GetSchoolList()
{
List<SelectListItem> list = new List<SelectListItem>();
SelectListItem ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Client", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new SelectListItem();
ins.Value = drI["ClientId"].ToString();
ins.Text = drI["Name"].ToString();
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public int GetDeviceId(string DeviceAddress)
{
int DeviceId = -1;
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.GetDeviceIdFromAddress;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@Address", DeviceAddress);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
DeviceId = Convert.ToInt32(drI["DeviceId"]);
}
}
cmdI.Connection.Close();
con.Dispose();
return DeviceId;
}
public Client GetClient(int ClientId)
{
Client ins = new Client();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Client WHERE ClientId =" + ClientId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.Name = drI["Name"].ToString();
}
}
drI.Close();
con.Close();
return ins;
}
public Services GetService(int id)
{
Services ins = new Services();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Services WHERE ServiceId =" + id, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.ServiceId = Convert.ToInt32(drI["ServiceId"]);
ins.Service = drI["Service"].ToString();
ins.Query = drI["Query"].ToString();
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.DeviceUserId = Convert.ToInt32(drI["DeviceUserId"]);
}
}
drI.Close();
con.Close();
return ins;
}
public int GetNews(int ClientId)
{
int returnValue = 0;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT COUNT(ClientId) as NumberOfUsers FROM News WHERE ClientId =" + ClientId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
returnValue = Convert.ToInt32(drI["NumberOfUsers"]);
}
}
//...Clean Up...
drI.Close();
con.Close();
//...Return...
return returnValue;
}
public List<SportCategory> GetListSportCategoryportCategorys()
{
List<SportCategory> list = new List<SportCategory>();
SportCategory ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * from SportCategory", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new SportCategory();
ins.SportCategoryID = Convert.ToInt32(drI["SportCategoryId"]);
ins.ClientID = Convert.ToInt32(drI["ClientId"]);
ins.CategoryName = drI["CategoryName"].ToString();
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public SportCategory GetSportCategory(int SportCategoryId)
{
SportCategory ins = new SportCategory();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM SportCategory WHERE SportCategoryId =" + SportCategoryId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.SportCategoryID = Convert.ToInt32(drI["SportCategoryId"]);
ins.ClientID = Convert.ToInt32(drI["ClientId"]);
ins.CategoryName = drI["CategoryName"].ToString();
}
}
drI.Close();
con.Close();
return ins;
}
public string GetFacebookToken(int ClientId)
{
string returnValue = "";
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Settings WHERE ClientId = " + ClientId + " AND Setting = 'facebook'", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
returnValue = drI["Value"].ToString();
}
}
drI.Close();
con.Close();
return returnValue;
}
public AnimalView GetAnimalInteractions(AnimalView ins)
{
DateTime EndDate = DateTime.Now;
DateTime StartDate = EndDate.AddHours(-2);
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.GetAnimalInteractions;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@AnimalId", ins.AnimalId);
cmdI.Parameters.AddWithValue("@StartDate", StartDate);
cmdI.Parameters.AddWithValue("@EndDate", EndDate);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
ins.AverageNumberInteractions = Math.Ceiling(Convert.ToDouble(drI["AverageInteractionsPerDay"]) / 24);
ins.AverageLengthInteractions = Math.Ceiling(Convert.ToDouble(drI["AverageInteractionTimePerDay"]) /24);
ins.RecentNumberInteractions = Convert.ToInt32(drI["RecentInteractions"]);
ins.RecentLengthInteractions = Convert.ToDouble(drI["RecentInteractionsTime"]);
}
}
cmdI.Connection.Close();
con.Dispose();
return ins;
}
public List<SubCategory> GetListSubCategory(int CategoryId)
{
List<SubCategory> list = new List<SubCategory>();
SubCategory ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM SubCategory WHERE CategoryId = " + CategoryId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new SubCategory();
ins.SubCategoryId = Convert.ToInt32(drI["SubCategoryId"]);
ins.Title = drI["Title"].ToString();
ins.Body = drI["Body"].ToString();
ins.CategoryId = Convert.ToInt32(drI["CategoryId"]);
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public bool CheckGRVDuplicate(string Number)
{
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT GRVListID FROM t_GRVList WHERE Number ='" + Number + "' AND Removed = 0", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
bool ret = false;
//...Retrieve Data...
if (drI.HasRows)
{
ret = true;
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return ret;
}
public frmMain()
{
InitializeComponent();
DBC = new DataBaseConnection();
FVSSI = new MSVSS32.MSVSS();
colEmpresas = new Empresass();
//----------
txtInformacionCompartidos.ForeColor = Color.Red;
//----------
lstEmpresas.MouseDoubleClick+=new MouseEventHandler(lstEmpresas_MouseDoubleClick);
lstEmpresas.MouseClick += new MouseEventHandler(lstEmpresas_MouseClick);
//----------
txtBusquedaEmpresa.KeyDown += new KeyEventHandler(txtBusquedaEmpresa_KeyDown);
txtBusquedaEmpresa.KeyPress += new KeyPressEventHandler(txtBusquedaEmpresa_KeyPress);
txtBusquedaEmpresa.TextChanged += new EventHandler(txtBusquedaEmpresa_TextChanged);
txtBusquedaEmpresa.Enter += new EventHandler(txtBusquedaEmpresa_Enter);
//----------
tvw.ContextMenuStrip = conMenu1;
tvw.KeyUp += new KeyEventHandler(tvw_KeyUp);
tvw.NodeMouseClick+=new TreeNodeMouseClickEventHandler(tvw_NodeMouseClick);
tvw.DoubleClick+=new EventHandler(tvw_DoubleClick);
tvw.DrawNode += new DrawTreeNodeEventHandler(tvw_DrawNode);
//tvw.DrawMode = TreeViewDrawMode.OwnerDrawText;
//tvw.DrawNode += new DrawTreeNodeEventHandler(tvw_DrawNode);
}
public List<Fields> GetListFields()
{
List<Fields> list = new List<Fields>();
Fields ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT f.*,sc.CategoryName FROM Fields f inner join SportCategory sc on f.SportCategoryID =sc.SportCategoryID", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Fields();
ins.FieldsId = Convert.ToInt32(drI["FieldsId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.SportCategoryID = Convert.ToInt32(drI["SportCategoryID"]);
ins.FieldName = drI["FieldName"].ToString();
ins.sportcategory = drI["CategoryName"].ToString();
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public Fixtures GetFixtures(int FixturesId)
{
Fixtures ins = new Fixtures();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM fixtures WHERE FixturesId =" + FixturesId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.FixturesId = Convert.ToInt32(drI["FixturesId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.TeamIdA = Convert.ToInt32(drI["TeamIdA"]);
ins.TeamIdB = Convert.ToInt32(drI["TeamIdB"]);
ins.StartTime = Convert.ToDateTime(drI["StartTime"]);
ins.FieldId = Convert.ToInt32(drI["FieldsId"]);
ins.SportCategoryId = Convert.ToInt32(drI["SportCategoryId"]);
}
}
drI.Close();
con.Close();
return ins;
}
public List<SelectListItem> GetFixtures(int? sc)
{
List<SelectListItem> obj = new List<SelectListItem>();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = new SqlCommand("select f.*,s.Schoolabbreviation as teamA,sb.Schoolabbreviation as teamB,t.Age as AgeA,tb.Age as AgeB,t.Ranks as RankA,tb.Ranks as RankB from fixtures f inner join Teams t on f.TeamIdA = t.TeamsId inner join Teams tb on f.TeamIdB=tb.TeamsId inner join Schools s on t.SchoolId = s.SchoolId inner join Schools sb on tb.SchoolId = sb.SchoolId where f.SportCategoryID ='"+sc+"'and f.FixturesId not in (select FixtureId from Results) ", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
var result = new SelectListItem();
result.Text = drI["teamA"].ToString() + " " + drI["AgeA"].ToString() + "/" + drI["RankA"].ToString() + " VS " + drI["teamB"].ToString() + " " + drI["AgeB"].ToString() + "/" + drI["RankB"].ToString() + " " + drI["StartTime"].ToString();
result.Value = drI["FixturesId"].ToString();
obj.Add(result);
}
}
drI.Close();
con.Close();
con.Dispose();
return obj;
}
public List<string> GetAutoCompleteInvoiceNumbers(int SupplierId)
{
List<string> obj = new List<string>();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = new SqlCommand("Select DISTINCT (g.InvoiceNumber + ' ' + s.Supplier) as InvoiceNumber "
+ " from t_GRVList g "
+ " left join t_ProofOfPayment p "
+ " on UPPER(g.InvoiceNumber) = UPPER(p.InvoiceNumber) "
+ " inner join t_Supplier s on g.SupplierID = s.SupplierID "
+ " where g.Removed=0 and g.SupplierID = " + SupplierId
+ " and COALESCE(p.ProofOfPaymentID,0) = 0 "
+ " order by InvoiceNumber ", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
obj.Add(drI["InvoiceNumber"].ToString());
}
}
drI.Close();
con.Close();
con.Dispose();
return obj;
}
public List<int> GetPublicRegistrar()
{
List<int> ReturnObject = new List<int>();
DataBaseConnection dbConn = new DataBaseConnection();
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetPublicRegistrar, con))
{
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ReturnObject.Add(Convert.ToInt32(drI["AnimalId"]));
}
}
}
}
return ReturnObject;
}
public List<string> GetAllUsernames()
{
List<string> usernames = new List<string>();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.GetAllUsers;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
usernames.Add(drI["Username"].ToString());
}
}
cmdI.Connection.Close();
con.Dispose();
return usernames;
}
public List<AnimalSim> GetAnimals(int CompanyId, int SexId)
{
List<AnimalSim> ReturnObject = new List<AnimalSim>();
AnimalSim ins;
DataBaseConnection dbConn = new DataBaseConnection();
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.SimAnimals + " @CompanyId, @SexId", con))
{
cmd.Parameters.AddWithValue("@CompanyId", CompanyId);
cmd.Parameters.AddWithValue("@SexId", SexId);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new AnimalSim();
ins.AnimalId = Convert.ToInt32(drI["AnimalId"]);
ins.DeviceAddress = drI["DeviceAddress"].ToString();
ReturnObject.Add(ins);
}
}
}
}
return ReturnObject;
}
public void GetClientId(string UserName)
{
string ClientId = "";
MembershipUser user = Membership.GetUser(UserName);
Guid guid = (Guid)user.ProviderUserKey;
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.AuthConn();
SqlCommand cmdI = new SqlCommand("SELECT ClientId FROM Users WHERE UserId='" + guid + "'", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
ClientId = drI["ClientId"].ToString();
}
}
drI.Close();
con.Close();
drI.Dispose();
con.Dispose();
HttpContext.Current.Session["ClientId"] = ClientId;
}
public List<Schoolsc> GetListSchools()
{
List<Schoolsc> list = new List<Schoolsc>();
Schoolsc ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Schools ", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Schoolsc();
ins.SchoolId = Convert.ToInt32(drI["SchoolId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.SchoolName = drI["SchoolName"].ToString();
ins.Schoolabbreviation = drI["Schoolabbreviation"].ToString();
ins.PictureId = Convert.ToInt32(drI["PictureId"]);
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public NotificationGroups GetNotificationGroup(int id)
{
NotificationGroups ins = new NotificationGroups();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM NotificationGroups WHERE NotifcationGroupId =" + id, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.NotificationGroupId = Convert.ToInt32(drI["NotificationGroupId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.GroupName = drI["GroupName"].ToString();
}
}
drI.Close();
con.Close();
return ins;
}
public List<string> GetAutoCompleteInvoiceNumbers(DateTime date, int SupplierId)
{
List<string> obj = new List<string>();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = new SqlCommand("Select g.InvoiceNumber, g.GRVTypeID from t_GRVList g left join t_SparRecon sr on g.InvoiceNumber=sr.InvoiceNumber and g.GRVTypeID = sr.GRVTypeId "
+ " where g.InvoiceDate BETWEEN DATEADD(DAY, -7,'" + date.ToShortDateString() + "') AND '" + date.ToShortDateString() + "' and g.SupplierID = " + SupplierId
+ " and COALESCE(sr.SparReconId,0) = 0 and g.Removed =0 ", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
if (Convert.ToInt16(drI["GRVTypeID"]) == 1)
{
obj.Add(drI["InvoiceNumber"].ToString() + " - GRV");
}
else
{
obj.Add(drI["InvoiceNumber"].ToString() + " - CLM");
}
}
}
drI.Close();
con.Close();
con.Dispose();
return obj;
}
public List<RegisterClient> GetListRegisterClient(int ClientId)
{
List<RegisterClient> list = new List<RegisterClient>();
RegisterClient ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM RegisterClient WHERE ClientId = " + ClientId + " ORDER BY RegisterClientId DESC", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new RegisterClient();
ins.RegisterClientID = Convert.ToInt32(drI["RegisterClientId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.ClientNo = drI["TeamIdA"].ToString();
ins.Email = drI["TeamIdB"].ToString();
ins.Passwords = drI["StartTime"].ToString();
ins.Approved = Convert.ToBoolean(drI["Approved"]);
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public List <Order> GetAllProductOrders(string Suffix, string PinkSlipNumber, string Supplier, string From, string To, string Comment)
{
//...Create New Instance of Object...
List <Order> list = new List <Order>();
Order ins;
if (From.Equals(""))
{
From = "1900-01-01";
}
if (To.Equals(""))
{
To = "2100-01-01";
}
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
if (Supplier.Equals(""))
{
if (Comment.Equals(""))
{
cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
+ " inner join t_Comment c on o.CommentID = c.CommentID "
+ " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
+ " AND o.Suffix LIKE '%" + Suffix + "'"
+ " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'"
+ " AND o.Amount = 0", con);
}
else
{
cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
+ " inner join t_Comment c on o.CommentID = c.CommentID "
+ " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
+ " AND o.Suffix LIKE '%" + Suffix + "' AND o.CommentID = " + Comment
+ " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'"
+ " AND o.Amount = 0", con);
}
}
else
{
if (Comment.Equals(""))
{
cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
+ " inner join t_Comment c on o.CommentID = c.CommentID "
+ " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
+ " AND o.Suffix LIKE '%" + Suffix + "'"
+ " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'"
+ " AND o.SupplierID = " + Supplier
+ " AND o.Amount = 0", con);
}
else
{
cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
+ " inner join t_Comment c on o.CommentID = c.CommentID "
+ " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
+ " AND o.Suffix LIKE '%" + Suffix + "' AND o.CommentID = " + Comment
+ " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'"
+ " AND o.SupplierID = " + Supplier
+ " AND o.Amount = 0", con);
}
}
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Order();
ins.OrderID = Convert.ToInt32(drI["OrderID"]);
ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]);
ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]);
ins.Amount = Convert.ToDecimal(drI["Amount"]);
ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]);
ins.SupplierID = Convert.ToInt32(drI["SupplierID"]);
ins.UserID = Convert.ToString(drI["UserID"]);
ins.CommentID = Convert.ToInt32(drI["CommentID"]);
ins.CompanyID = Convert.ToInt32(drI["CompanyID"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
ins.Removed = Convert.ToBoolean(drI["Removed"]);
ins.supplier = drI["Supplier"].ToString();
ins.Suffix = drI["Suffix"].ToString();
ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]);
ins.ordercomment = drI["Comment"].ToString();
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return(list);
}
public VehicleViewModel()
{
vd = new VehicleData();
db = new DataBaseConnection();
}
public OrderProduct InsertOrderProduct(OrderProduct ins)
{
//...Get User and Date Data...
string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
string strTrx = "OrderProductIns_" + EmployeeId;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
//...Command Interface...
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx;
trx = con.BeginTransaction(strTrx);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
//...Insert Record...
cmdI.CommandText = StoredProcedures.OrderProductInsert;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@OrderID", ins.OrderID);
cmdI.Parameters.AddWithValue("@ProductID", ins.ProductID);
cmdI.Parameters.AddWithValue("@StatusID", ins.StatusID);
cmdI.Parameters.AddWithValue("@Quantity", ins.Quantity);
cmdI.Parameters.AddWithValue("@Price", ins.Price);
cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate);
cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId);
cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID);
//...Return new ID
ins.OrderProductID = (int)cmdI.ExecuteScalar();
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null)
{
trx.Rollback();
}
}
finally
{
//Check for close and respond accordingly
if (con.State != ConnectionState.Closed)
{
con.Close();
}
//Clean up
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
return(ins);
}
public HelperSetUp(IWebDriver driver, DataBaseConnection dbConn, IConfiguration configuration)
{
this._driver = driver;
this._dbConn = dbConn;
_configuration = configuration;
}
public Order Insert(Order ins)
{
//...Get User and Date Data...
string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
ins.UserID = EmployeeId;
string strTrx = "OrderIns_" + EmployeeId;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
//...Command Interface...
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx;
trx = con.BeginTransaction(strTrx);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
//...Insert Record...
cmdI.CommandText = StoredProcedures.OrderInsert;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
//cmdI.Parameters.AddWithValue("@OrderID", ins.OrderID);
cmdI.Parameters.AddWithValue("@OrderDate", ins.OrderDate);
cmdI.Parameters.AddWithValue("@ExpectedDeliveryDate", ins.ExpectedDeliveryDate);
cmdI.Parameters.AddWithValue("@Amount", ins.Amount);
cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID);
cmdI.Parameters.AddWithValue("@UserID", ins.UserID);
cmdI.Parameters.AddWithValue("@CommentID", ins.CommentID);
cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID);
cmdI.Parameters.AddWithValue("@ModifiedDate", ModifiedDate);
cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId);
cmdI.Parameters.AddWithValue("@Removed", ins.Removed);
cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber);
cmdI.Parameters.AddWithValue("@Suffix", checkNullString(ins.Suffix));
//...Return new ID
ins.OrderID = (int)cmdI.ExecuteScalar();
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null)
{
trx.Rollback();
}
}
finally
{
//Check for close and respond accordingly
if (con.State != ConnectionState.Closed)
{
con.Close();
}
//Clean up
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
RepopulateGRVList(ins.PinkSlipNumber);
return(ins);
}
public static void SendMessage([FromBody] Message requestFront)
{
Bdd bdd = DataBaseConnection.databaseConnection();
//bdd.SendMessage(requestFront);
}
public MaterialsPresenter(DataBaseConnection db) : base(db)
{
}
public CategoriesAdminManager(
DataBaseConnection db,
SanitizerService sanitizerService) : base(db)
{
this.sanitizerService = sanitizerService;
}
public MenuAdminManager(IRolesCache rolesCache, DataBaseConnection db) : base(db)
{
this.rolesCache = rolesCache;
}
public EstadoDAO(DataBaseConnection context, PaisDAO paisDAO) : base(context, "Estados")
{
this.PaisDAO = paisDAO;
}
public CacheSettingsManager(IOptions <CacheOptions> cacheOptions,
IContentCache contentCache, DataBaseConnection db) : base(db)
{
this.cacheOptions = cacheOptions;
this.contentCache = contentCache;
}
public void database_connection()
{
DataBaseConnection.connectiondatabase();
}
public ContaPagarDAO(DataBaseConnection context) : base(context, "ContasPagar", new[] { "Modelo", "Serie", "Numero", "FornecedorId", "Parcela" })
{
}
public DataBaseSeeder(DataBaseConnection db, DataContainer dataContainer)
{
this.dataContainer = dataContainer;
this.db = db;
}
public PulseController(
DataBaseConnection db,
IServiceProvider serviceProvider) : base(serviceProvider)
{
this.db = db;
}
