public static void Insert_Order_Details(Order_Details_DATA Order_Details)
{
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("QUANTITY", Order_Details.Quantity));
Orders_DATA oreder = new Orders_DATA();
parameters.Add(new SqlParameter("ID_ORDER", Order_Details.Order.Id));
parameters.Add(new SqlParameter("ID_PRODUCT", Order_Details.Product.Id));
parameters.Add(new SqlParameter("PRICE", Order_Details.Product.Price));
parameters.Add(new SqlParameter("DISCOUNT", Order_Details.Discount));
parameters.Add(new SqlParameter("TOTAL_PRICE", Order_Details.Total_price));
parameters.Add(new SqlParameter("TOTAL_PRICE_with_discount", Order_Details.Total_price_after_discount));
DBL.execute_query("ORDERS_DETAIAS_insert", parameters);
}
public static List <EN.Item> GetMontoProgPago(string empresa, string proveedor, DateTime dtFechaProgPago)
{
List <EN.Item> lcp = new List <EN.Item>();
string sqlstr = "contabilidad.usp_ComprasPorPagar_MontoProgramadoParaPago_PorFecha";
Hashtable m_params = new Hashtable();
m_params.Add("@Fecha", dtFechaProgPago);
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
EN.Item cp = new EN.Item();
cp.id = Convert.ToInt32(dr["ID"]);
cp.descripcion = dr["FechaProgPago"].ToString();
cp.aux = dr["Monto"].ToString();
lcp.Add(cp);
}
return(lcp);
}
private void button4_Click(object sender, EventArgs e)
{
Users user = new Users();
user.Id = Convert.ToInt16(dataGridView1.SelectedRows[0].Cells[0].Value);
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("ID_User", user.Id));
DBL.execute_query("user_delete", parameters);
List <Users> user_list = new List <Users>();
DataTable tables = DBL.get_DATA("Select_Users");
foreach (DataRow item in tables.Rows)
{
user_list.Add(new Users(item["Name"].ToString(), item["Password"].ToString(), Convert.ToInt16(item["ID_User"]), item["type"].ToString()));
}
DataTable table = new DataTable();
table.Columns.Add("المعرف");
table.Columns.Add("الاسم");
table.Columns.Add("الباسورد");
table.Columns.Add("النوع");
foreach (var item in user_list)
{
table.Rows.Add(item.Id, item.Name, item.Password, item.Type);
count_all++;
}
dataGridView1.DataSource = table;
bmb = BindingContext[table];
dataGridView1.Columns[0].Visible = false;
}
public ProfilePage(Person p, DBL d, ProfilePageType pt)
{
InitializeComponent();
person = p;
dbl = d;
pageType = pt;
FacultyBoxList = new List <Faculty>(dbl.GiveFaculties());
foreach (Faculty f in FacultyBoxList)
{
if (f.University == dbl.LoggedPerson.University)
{
Facylty_Box.Items.Add(new ComboBoxItem {
Content = f.Name
});
}
}
switch (pageType)
{
case ProfilePageType.Edit:
EditType();
break;
case ProfilePageType.NewStudent:
AddType();
NewStudentType();
break;
case ProfilePageType.NewTeacher:
AddType();
NewTeacherType();
break;
case ProfilePageType.NewDean:
AddType();
NewDeanType();
break;
default:
break;
}
}
public static List <Orders_DATA> Select_ALL_Orders_By_Customer_Id(int Customer_Id)
{
List <Orders_DATA> Orders = new List <Orders_DATA>();
Orders_DATA Order = new Orders_DATA();
DataTable table = DBL.get_DATA("ORDERS_select_All");
foreach (DataRow item in table.Rows)
{
Order.Id = Convert.ToInt16(item["ID_ORDER"]);
Order.Order_date = (DateTime)item["DATE_ORDERS"];
Customer_DATA customer = new Customer_DATA();
customer.ID = Convert.ToInt16(item["ID_CUSTOMER"]);
Order.Customer = customer;
Order.Total_paied = Convert.ToDouble(item["Total_paied"]);
Orders.Add(Order);
}
Orders = Orders.Where(x => x.Customer.ID == Customer_Id).ToList();
return(Orders);
}
public static List <Unidad> GetUnidadesConConsumo()
{
List <Unidad> lunidades = new List <Unidad>();
string sqlstr = "dbo.usp_Orsan_ObtieneUnidadesConConsumo";
Hashtable m_params = new Hashtable();
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
Unidad u = new Unidad();
u.id = Convert.ToInt32(dr["ID"]);
u.descripcion = dr["Descripcion"].ToString();
u.Unidad_ID = Convert.ToInt32(dr["ID"]);
u.ModeloUnidad_ID = Convert.ToInt32(dr["ID"]);
u.Modelo = dr["Modelo"].ToString();
u.aux = u.Unidad_ID.ToString();
lunidades.Add(u);
}
return(lunidades);
}
public static List <Order_Details_DATA> Select_ALL_Order_Details_By_Products_Id(int Products_Id)
{
List <Order_Details_DATA> Order_Details = new List <Order_Details_DATA>();
Order_Details_DATA Order_Detail = new Order_Details_DATA();
DataTable table = DBL.get_DATA("ORDERS_DETAIAS_select_All");
foreach (DataRow item in table.Rows)
{
Order_Detail.Id = Convert.ToInt16(item["ID_ORDERS_DETAIAS"]);
Order_Detail.Quantity = Convert.ToInt16(item["QUANTITY"]);
Orders_DATA order = new Orders_DATA();
order.Id = Convert.ToInt16(item["ID_ORDER"]);
Order_Detail.Order = order;
Products_DATA pro = new Products_DATA();
pro.Id = Convert.ToInt16(item["ID_PRODUCT"]);
Order_Detail.Product = pro;
Order_Details.Add(Order_Detail);
}
Order_Details = Order_Details.Where(x => x.Product.Id == Products_Id).ToList();
return(Order_Details);
}
private void users_managmet_Load(object sender, EventArgs e)
{
Users user = new Users();
List <Users> users = new List <Users>();
users.Add(new Users {
Type = "admin"
});
users.Add(new Users {
Type = "user"
});
type.DataSource = users;
type.ValueMember = "Type";
type.DisplayMember = "Type";
List <Users> user_list = new List <Users>();
DataTable tables = DBL.get_DATA("Select_Users");
foreach (DataRow item in tables.Rows)
{
user_list.Add(new Users(item["Name"].ToString(), item["Password"].ToString(), Convert.ToInt16(item["ID_User"]), item["type"].ToString()));
}
DataTable table = new DataTable();
table.Columns.Add("المعرف");
table.Columns.Add("الاسم");
table.Columns.Add("الباسورد");
table.Columns.Add("النوع");
foreach (var item in user_list)
{
table.Rows.Add(item.Id, item.Name, item.Password, item.Type);
count_all++;
}
dataGridView1.DataSource = table;
bmb = BindingContext[table];
dataGridView1.Columns[0].Visible = false;
}
public StudentsGridPage(DBL d)
{
InitializeComponent();
dbl = d;
Students = new ObservableCollection <Student>();
if (dbl.accounttype == DBL.AccountType.Dean)
{
foreach (Student s in dbl.GiveStudents())
{
if (s.University == dbl.LoggedPerson.University && s.Group.Faculty == ((Dean)dbl.LoggedPerson).Faculty)
{
Students.Add(s);
}
}
}
else if (dbl.accounttype == DBL.AccountType.Teacher)
{
foreach (Student s in dbl.GiveStudents())
{
if (s.University == dbl.LoggedPerson.University)
{
Students.Add(s);
}
}
AddStudent.Visibility = Visibility.Hidden;
RemoveStudent.Visibility = Visibility.Hidden;
EditStudent.Visibility = Visibility.Hidden;
}
else if (dbl.accounttype == DBL.AccountType.Rector)
{
foreach (Student s in dbl.GiveStudents())
{
if (s.University == dbl.LoggedPerson.University)
{
Students.Add(s);
}
}
}
StudentsGrid.ItemsSource = Students;
}
public static List <Proveedor> ObtieneListaProveedores(int empresa_id)
{
List <Proveedor> le = new List <Proveedor>();
string sqlstr = "[Abastos].[usp_Proveedores_Consulta_PorEmpresa]";
Hashtable m_params = new Hashtable();
m_params.Add("@Empresa_ID", empresa_id);
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
Proveedor r = new Proveedor();
r.id = Convert.ToInt32(dr["CIDPROVEEDOR"]);
r.descripcion = dr["CRAZONSOCIAL"].ToString();
r.CIDPROVEEDOR = dr["CIDPROVEEDOR"].ToString();
r.CCODIGOPROVEEDOR = dr["CCODIGOPROVEEDOR"].ToString();
r.aux = dr["BaseDeDatos"].ToString();
r.CDIASCREDITO = Convert.ToInt32(dr["CDIASCREDITO"]);
le.Add(r);
}
return(le);
}
public static List <Products_DATA> Select_ALL_Product()
{
List <Products_DATA> Products = new List <Products_DATA>();
DataTable table = DBL.get_DATA("PRODUCT_select_All");
foreach (DataRow item in table.Rows)
{
Products_DATA Product = new Products_DATA();
Product.Id = Convert.ToInt16(item["ID_PRODUCT"]);
Product.Image = (byte[])item["IMAGE_PRODUCT"];
Product.Label_product = item["LABEL_PRODUCT"].ToString();
Product.Price = item["QTE_IN_PRODUCT"].ToString();
Product.Qte_in_product = item["PRICE"].ToString();
Categories_DATA c = new Categories_DATA();
c.Id = Convert.ToInt16(item["ID_CATEGORIES"]);
Product.Category = c;
Products.Add(Product);
}
return(Products);
}
public static List <Almacen> GetAlmacenesPorDivisionEmpresaDepto(int division, int empresa, int departamento)
{
List <Almacen> la = new List <Almacen>();
string sqlstr = "[Abastos].[usp_Almacenes_ConsultaPorDivisionEmpresaDepartamento]";
Hashtable m_params = new Hashtable();
m_params.Add("@Division_ID", division);
m_params.Add("@Empresa_ID", empresa);
m_params.Add("@Departamento_ID", departamento);
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
Almacen ar = new Almacen();
ar.id = Convert.ToInt32(dr["Almacen_ID"]);
ar.descripcion = dr["Descripcion"].ToString();
ar.aux = dr["CIDALMACEN"].ToString();
ar.Orden = Convert.ToInt32(dr["ORDEN"]);
la.Add(ar);
}
return(la);
}
public static void SetSolicitudDeMaterialesDetalle(EN.Abastos.SolicitudMateriales req)
{
List <DetalleSolicitudMateriales> lr = new List <DetalleSolicitudMateriales>();
string sqlstr = "[Abastos].[SolicitudDeMaterialesDetalle_Consulta]";
Hashtable m_params = new Hashtable();
m_params.Add("@SolicitudDeMaterial_ID", req.id);
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
DetalleSolicitudMateriales r = new DetalleSolicitudMateriales();
r.id = Convert.ToInt32(dr["SolicitudDeMaterialDetalle_ID"]);
r.cantidad = Convert.ToInt32(dr["Cantidad"]);
r.item.id = Convert.ToInt32(dr["Servicio_ID"]);
r.item.aux = Convert.ToDouble(dr["CPRECIOCOMPRA"]).ToString("C2");
r.item.descripcion = dr["CNOMBREPRODUCTO"].ToString();
r.item.preciocompra = Convert.ToDouble(dr["CPRECIOCOMPRA"]);
r.item.CIDPRODUCTO = Convert.ToInt32(dr["CIDPRODUCTO"]);
r.item.CIDPROVEEDOR = Convert.ToInt32(dr["CIDPROVEEDOR"]);
r.item.CCODIGOPRODUCTO = Convert.ToInt32(dr["CCODIGOPRODUCTO"]);
if (!Convert.IsDBNull(dr["CIDDOCUMENTO"]))
{
r.CIDDOCUMENTO = Convert.ToInt32(dr["CIDDOCUMENTO"]);
}
if (!Convert.IsDBNull(dr["FOLIODOCUMENTO"]))
{
r.FolioDocumento = dr["FOLIODOCUMENTO"].ToString();
}
if (!Convert.IsDBNull(dr["SERIEDOCUMENTO"]))
{
r.SerieDocumento = dr["SERIEDOCUMENTO"].ToString();
}
lr.Add(r);
}
req.articulos = lr;
}
public static List <ConsumoCombustible> GetConsumosPorFecha(int unidad, DateTime dteIni, DateTime dteFin)
{
List <ConsumoCombustible> lconsumos = new List <ConsumoCombustible>();
string sqlstr = "dbo.usp_Orsan_ObtieneConsumos";
Hashtable m_params = new Hashtable();
m_params.Add("@NoVehiculo", unidad);
m_params.Add("@FechaInicial", dteIni);
m_params.Add("@FechaFinal", dteFin);
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
ConsumoCombustible cc = new ConsumoCombustible();
cc.id = Convert.ToInt32(dr["ConsumoCombustible_ID"]);
cc.descripcion = dr["Unidad"].ToString();
cc.NoVehiculo = Convert.ToInt32(dr["NoVehiculo"]);
cc.Conductor = dr["Conductor"].ToString();
cc.FechaInicial = Convert.ToDateTime(dr["FechaInicial"]);
cc.FechaFinal = Convert.ToDateTime(dr["FechaFinal"]);
cc.Litros = Convert.ToDouble(dr["Litros"]);
cc.Pesos = Convert.ToDouble(dr["Pesos"]);
cc.KmInicial = Convert.ToInt32(dr["KmInicial"]);
cc.KmFinal = Convert.ToInt32(dr["KmFinal"]);
cc.KmRecorridos = Convert.ToInt32(dr["KmRecorridos"]);
cc.Rendimiento = Convert.ToDouble(dr["Rendimiento"]);
cc.Servicios = Convert.ToInt32(dr["Servicios"]);
cc.Ingresos = Convert.ToDouble(dr["Ingresos"]);
cc.ModeloUnidad_ID = Convert.ToInt32(dr["ModeloUnidad_ID"]);
cc.Modelo = dr["Modelo"].ToString();
cc.aux = cc.KmRecorridos.ToString();
lconsumos.Add(cc);
}
return(lconsumos);
}
public static List <ArticuloSolicitudMateriales> GetServiciosPorDivisionEmpresaDeptoAlmacen(int division, int empresa, int departamento, int?almacen)
{
List <ArticuloSolicitudMateriales> lar = new List <ArticuloSolicitudMateriales>();
string sqlstr = "[Abastos].[usp_Servicios_ConsultaPorDivisionEmpresaDepartamentoAlmacen]";
Hashtable m_params = new Hashtable();
m_params.Add("@Division_ID", division);
m_params.Add("@Empresa_ID", empresa);
m_params.Add("@Departamento_ID", departamento);
if (almacen != null)
{
m_params.Add("@Almacen_ID", almacen);
}
DataTable dt = DBL.ExecuteSelectSP(m_params, sqlstr, Utils.CadenaConexionSICAS, Utils.ConexionTimeOut).Tables[0];
foreach (DataRow dr in dt.Rows)
{
ArticuloSolicitudMateriales ar = new ArticuloSolicitudMateriales();
ar.SERVICIO_ID = Convert.ToInt32(dr["SERVICIO_ID"]);
ar.id = ar.SERVICIO_ID;
ar.CIDPRODUCTO = dr["CIDPRODUCTO"].ToString();
ar.CIDPROVEEDOR = dr["CIDPROVEEDOR"].ToString();
ar.CODIGOPRODUCTO = dr["CCODIGOPRODUCTO"].ToString();
ar.CNOMBREPRODUCTO = dr["CNOMBREPRODUCTO"].ToString();
ar.descripcion = ar.CNOMBREPRODUCTO;
ar.preciocompra = Convert.ToDouble(dr["CPRECIOCOMPRA"]);
ar.aux = ar.preciocompra.ToString();
if (dr["KILOMETRAJE_MILES"] != DBNull.Value)
{
ar.KILOMETRAJE_MILES = Convert.ToInt32(dr["KILOMETRAJE_MILES"]);
}
lar.Add(ar);
}
return(lar);
}
public static int InsertUserCourse(Guid UserId, int CourseId)
{
int rowsAffected = DBL.ExecuteDML("insert into UserCourse(Fk_UserId, FK_CourseId) values ('" + UserId + "', " + CourseId + ")");
return(rowsAffected);
}
public static DataTable GetAllUserCourseById(Guid UserId)
{
DataTable dt = DBL.ExecuteQuery("select Course.CourseName,aspnet_Users.UserId from UserCourse left join aspnet_Users on UserId = FK_UserId left join Course on CourseId =FK_CourseId where UserId =" + UserId);
return(dt);
}
public static DataTable GetAllCourseById(int CourseId)
{
DataTable dt = DBL.ExecuteQuery("select * from Course where Course.CourseId=" + CourseId);
return(dt);
}
public static DataTable GetAllCourseAssocWith()
{
DataTable dt = DBL.ExecuteQuery("select CourseId,CourseAssociatedWith from Course");
return(dt);
}
public static DataTable GetAllCourse()
{
DataTable dt = DBL.ExecuteQuery("select * from Course");
return(dt);
}
public static int Updatecert(int CertificateId, string CertificateName, string CertificateDate)
{
int rowsAffected = DBL.ExecuteDML($"update Certificate set CertificateName = '{CertificateName}' , CertificateDate = '{CertificateDate}' where CertificateId = '{CertificateId}' ");
return(rowsAffected);
}
public static DataTable deleteCertificate(int CertificateId)
{
DataTable dt = DBL.ExecuteQuery("delete Certificate where CertificateId= " + CertificateId);
return(dt);
}
public static DataTable deleteCertificateName(string CertificateName)
{
DataTable dt = DBL.ExecuteQuery("delete Certificate where CertificateName= " + CertificateName);
return(dt);
}
public static DataTable GetByFK_UserId_(Guid FK_UserId)
{
DataTable dt = DBL.ExecuteQuery($"select * from Certificate where FK_UserId= '{FK_UserId}'");
return(dt);
}
public static int UpdateUserCourse(int CourseId, string CourseName, string CourseDate)
{
int rowsAffected = DBL.ExecuteDML("update Course set CourseName='" + CourseName + "' , CourseDate='" + CourseDate + "' where CourseId=" + CourseId + "");
return(rowsAffected);
}
public static int InsertNewCourse(string CourseName, int CourseNumber, string CourseDate, string CourseAssociatedWith)
{
int rowsAffected = DBL.ExecuteDML("insert into Course(CourseName, CourseNumber, CourseDate, CourseAssociatedWith) values('" + CourseName + "', " + CourseNumber + ",' " + CourseDate + "', '" + CourseAssociatedWith + "')");
return(rowsAffected);
}
public static int RemoveCourse(int CourseId)
{
int rowsAffected = DBL.ExecuteDML("delete from Course where CourseId=" + CourseId);
return(rowsAffected);
}
public static DataTable GetByCertificateName(string CertificateName)
{
DataTable dt = DBL.ExecuteQuery("select * from Certificate where Certificate.CertificateName = " + CertificateName);
return(dt);
}
private void BindFreezeDetails()
{
string _sStartDate = "";
string _sEndDate = "";
string _strStart = "";
string _strEnd = "";
int DateSelect = Convert.ToInt32(hfvDateval.Value);
var dt = DateTime.Now;
if (DateSelect == 0)
{
_sStartDate = dt.Month + "/" + dt.Day + "/" + dt.Year;
_sEndDate = dt.Month + "/" + (dt.Day) + "/" + dt.Year;
txtSDate.Enabled = false;
txtEDate.Enabled = false;
txtSDate.Text = "";
txtEDate.Text = "";
}
else if (DateSelect == 1)
{
_sStartDate = "01/01/" + DateTime.Now.Year;
_sEndDate = dt.Month + "/" + dt.Day + "/" + dt.Year;
txtSDate.Enabled = false;
txtEDate.Enabled = false;
txtSDate.Text = "";
txtEDate.Text = "";
}
else if (DateSelect == 2)
{
txtSDate.Enabled = true;
txtEDate.Enabled = true;
_strStart = txtSDate.Text;
_strEnd = txtEDate.Text;
if (string.IsNullOrEmpty(_strStart) || string.IsNullOrEmpty(_strEnd))
{
return;
}
else
{
_sStartDate = _strStart;
_sEndDate = _strEnd;
}
}
DataSet dsFreezeView = new DataSet();
string ConQuery = string.Empty;
var ConStr = ConnectionString.GetDBConnectionString();
ConQuery = "SELECT DISTINCT M.freeze_MASTER_ID, freeze_VIN_NUMBER, c.crt_DTC_CODE, C.crt_PLATFORM_NAME, c.crt_ECU_NAME, ISNULL(M.freeze_PUSH_NOTIFY, 0) AS freeze_PUSH_NOTIFY, ISNULL(M.freeze_NOTIFY_COUNTER, 0) AS freeze_NOTIFY_COUNTER ";
ConQuery += "FROM tab_CA_EMS_MASTER_INFO M ";
ConQuery += "INNER JOIN tab_CA_EMS_DTC_INFO D ON M.freeze_MASTER_ID = D.freeze_MASTER_ID ";
ConQuery += "INNER JOIN tab_CA_CRITICAL_DTC_ADD C ON D.freeze_DTC_CODE = C.crt_DTC_CODE WHERE CONVERT(date, M.freeze_TIMESTAMP, 101) between '" + _sStartDate + "' and '" + _sEndDate + "'and C.crt_ISCRITICAL = 1";
dsFreezeView = DBL.executeDataSet(ConStr, CommandType.Text, ConQuery, null);
if (dsFreezeView != null && dsFreezeView.Tables[0].Rows.Count > 0)
{
gvFreezeView.DataSource = dsFreezeView;
gvFreezeView.DataBind();
}
else
{
gvFreezeView.DataSource = null;
gvFreezeView.DataBind();
}
}
public static DataTable GetByFK_UserId(Guid FK_UserId)
{
DataTable dt = DBL.ExecuteQuery("select UserId,CertificateId,CertificateName,CONVERT(Date,CertificateDate) AS Date from aspnet_Users join Certificate on aspnet_Users.UserId =Certificate.FK_UserId= " + FK_UserId);
return(dt);
}
