private void lookUpCityF_EditValueChanged(object sender, EventArgs e)
{
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery1";
if (lookUpCityF.Properties.GetDataSourceValue("city_id", lookUpCityF.ItemIndex).ToString().Trim() == "00")
{
query.Sql = "select 0 as `id`, '00' as `code`, '' as `city`, '_все преподаватели_' as `fname`, '' as `lname`, '' as `sname`, '' as `date_birth`,"
+ " '' as `phone`, '' as `adres`, '' as `descr`, '' as `salary`, '' as `mail` from dual union all "
+ " select id,code,city,fname,ifnull(lname,' ') as lname,ifnull(sname,' ') as sname,ifnull(DATE_FORMAT(date_birth,'%d.%m.%Y'),' ') as date_birth,ifnull(phone,' ') as phone,"
+ " ifnull(adres,' ') as adres,ifnull(descr,' ') as descr,ifnull(salary,' ') as salary,ifnull(mail,' ') as mail from teacher order by city,fname,lname";
}
else
{
query.Sql = "select 0 as `id`, '00' as `code`, '' as `city`, '_все преподаватели_' as `fname`, '' as `lname`, '' as `sname`, '' as `date_birth`,"
+ " '' as `phone`, '' as `adres`, '' as `descr`, '' as `salary`, '' as `mail` from dual union all "
+ "select id,code,city,fname,ifnull(lname,' ') as lname,ifnull(sname,' ') as sname,ifnull(DATE_FORMAT(date_birth,'%d.%m.%Y'),' ') as date_birth,ifnull(phone,' ') as phone,"
+ " ifnull(adres,' ') as adres,ifnull(descr,' ') as descr,ifnull(salary,' ') as salary,ifnull(mail,' ') as mail from teacher"
+ " where city='" + lookUpCityF.Properties.GetDataSourceValue("city_name", lookUpCityF.ItemIndex).ToString().Trim() + "' order by fname,lname";
}
sqlDataTeacher.Queries.Clear();
sqlDataTeacher.Queries.Add(query);
sqlDataTeacher.Fill();
bindTeacher.DataSource = sqlDataTeacher;
bindTeacher.DataMember = "customQuery1";
lookUpTeachers.Properties.DataSource = bindTeacher;
}
void XtraReport1_DataSourceDemanded(object sender, EventArgs e)
{
CustomSqlQuery query = this.sqlDataSource1.Queries[0] as CustomSqlQuery;
int count = (this.parameter1.Value as IList).Count;
if (count == 0)
{
return;
}
StringBuilder builder = new StringBuilder();
builder.Append('(');
for (int i = 0; i < count; i++)
{
//builder.Append('\''); // Uncomment this line when parsing a string parameter value.
builder.Append((this.parameter1.Value as IList)[i]);
//builder.Append('\''); // Uncomment this line when parsing a string parameter value.
if (i != count - 1)
{
builder.Append(',');
}
}
builder.Append(')');
query.Sql += " WHERE [Categories].[CategoryID] IN " + builder.ToString();
sqlDataSource1.RebuildResultSchema();
}
private void lookUpCityScholarF_EditValueChanged(object sender, EventArgs e)
{
groupChangeTeacher.Visible = false;
if (lookUpSubject.ItemIndex > -1)
{
CustomSqlQuery query2 = new CustomSqlQuery();
query2.Name = "customQuery1";
query2.Sql = "select t.id,t.code,t.city,t.fname,ifnull(t.lname,' ') as lname,ifnull(t.sname,' ') as sname,ifnull(DATE_FORMAT(t.date_birth,'%d.%m.%Y'),' ') as date_birth,ifnull(t.phone,' ') as phone,"
+ " ifnull(t.adres,' ') as adres,ifnull(t.descr,' ') as descr,ifnull(t.salary,' ') as salary,ifnull(t.mail,' ') as mail from teacher t, teacher_subj ts"
+ " where t.city_id=ts.city and t.code=ts.code and ts.subj_name='" + lookUpSubject.Properties.GetDataSourceValue("subj_name", lookUpSubject.ItemIndex).ToString().Trim() + "'"
+ " and t.city_id='" + lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + "' order by fname,lname";
sqlDataTeacher.Queries.Clear();
sqlDataTeacher.Queries.Add(query2);
sqlDataTeacher.Fill();
bindTeacher.DataSource = sqlDataTeacher;
bindTeacher.DataMember = "customQuery1";
lookUpTeachers.Properties.DataSource = bindTeacher;
//lookUpTeachers.ItemIndex = 0;
}
if ((lookUpTeachers.ItemIndex > -1) && (lookUpSubject.ItemIndex > -1) && (lookUpCityScholarF.ItemIndex > -1))
{
txtCreateGroup.Text = lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + lookUpSubject.Properties.GetDataSourceValue("subj_id", lookUpSubject.ItemIndex).ToString().Trim() + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim();
btnCreateGroup.Enabled = true;
}
}
private void Dashboard_Parameter()
{
try
{
Dashboard dashboard = new Dashboard();
DashboardSqlDataSource dataSource = (DashboardSqlDataSource)dashboard.DataSources[0];
CustomSqlQuery salesPersonQuery = (CustomSqlQuery)dataSource.Queries[0];
salesPersonQuery.Parameters[0].Value = "ProcessMaterialStockStatus";
//salesPersonQuery.Parameters.Add(new QueryParameter("window_name", typeof(Expression),
// new Expression("[Parameters.yearParameter] + '/01/01'")));
//salesPersonQuery.Parameters.Add(new QueryParameter("endDate", typeof(Expression),
// new Expression("[Parameters.yearParameter] + '/12/31'")));
//salesPersonQuery.Sql. =
// "select * from SalesPerson where OrderDate between @startDate and @endDate";
dashboardViewer1.Dashboard = dashboard;
}
catch (ExceptionManager pExceptionManager)
{
CoFAS_DevExpressManager.ShowErrorMessage(string.Format("{0}\n{1}", pExceptionManager.Exception.Message.ToString(), pExceptionManager.TargetSite.ToString()));
}
}
public Form1()
{
InitializeComponent();
Dashboard dashboard = new Dashboard();
DashboardSqlDataSource dataSource = new DashboardSqlDataSource();
CustomSqlQuery sqlQuery = new CustomSqlQuery("Cities", "select * from Cities");
dataSource.ConnectionParameters =
new Access97ConnectionParameters(@"..\..\Data\Cities.mdb", "", "");
dataSource.Queries.Add(sqlQuery);
GeoPointMapDashboardItem geopointMap = new GeoPointMapDashboardItem();
geopointMap.DataSource = dataSource;
geopointMap.DataMember = "Cities";
geopointMap.Area = ShapefileArea.WorldCountries;
geopointMap.Latitude = new Dimension("Latitude");
geopointMap.Longitude = new Dimension("Longitude");
geopointMap.Value = new Measure("Population");
dashboard.Items.Add(geopointMap);
dashboardViewer1.Dashboard = dashboard;
}
private void SqltoSeries(Series series1, string name, string sql)
{
SqlDataSource sqlDataSourceTrend = new SqlDataSource();
CustomSqlQuery query = new CustomSqlQuery();
query.Name = name;
query.Sql = sql;
sqlDataSourceTrend.ConnectionName = "GlobeTradeGIS.Properties.Settings.GlobeTradeConnectionString";//用到了connectionstrings下的连接名
sqlDataSourceTrend.Queries.Add(query);
sqlDataSourceTrend.Fill();
ITable src = sqlDataSourceTrend.Result[name];
DataTable table = new DataTable(name);
foreach (IColumn column in src.Columns)
{
table.Columns.Add(column.Name, column.Type);
}
foreach (IRow row in src)
{
table.Rows.Add(row.ToArray());
}
for (int i = 0; i < table.Columns.Count; i++)
{
{
for (int j = 0; j < table.Rows.Count; j++)
{
//if(typeof())
double num = 0;
num = (double)table.Rows[j][i];
series1.Points.Add(new SeriesPoint(table.Columns[i].ColumnName, num));
}
}
}
}
private void SetTablesIdentityInsertOn(List <string> tableNames)
{
foreach (var tableName in tableNames)
{
CustomSqlQuery.SetTableIdentityInsertOn(this, tableName);
}
}
public CustomSqlQuery QuerySSCCList(string customerCode, string depotDate)
{
QueryParameter parameter = new QueryParameter
{
Name = "@CustomerCode",
Type = typeof(string),
Value = customerCode
};
QueryParameter dateParameter = new QueryParameter
{
Name = "@DepotDate",
Type = typeof(string),
Value = depotDate
};
CustomSqlQuery query = new CustomSqlQuery()
{
Name = "SSCCList",
Sql = "select sscc, orderpallet from DEA2153_Customer_DeliveryDate_sscc where customer = @CustomerCode and requested_delivery = @DepotDate"
};
query.Parameters.Add(parameter);
query.Parameters.Add(dateParameter);
return(query);
}
public Form1()
{
InitializeComponent();
Dashboard dashboard = new Dashboard();
dashboard.LoadFromXml(@"..\..\Data\Dashboard.xml");
// Creates a new dashboard parameter.
StaticListLookUpSettings staticSettings = new StaticListLookUpSettings();
staticSettings.Values = new string[] { "1994", "1995", "1996" };
DashboardParameter yearParameter = new DashboardParameter("yearParameter",
typeof(string), "1995", "Select year:", true, staticSettings);
dashboard.Parameters.Add(yearParameter);
DashboardSqlDataSource dataSource = (DashboardSqlDataSource)dashboard.DataSources[0];
CustomSqlQuery salesPersonQuery = (CustomSqlQuery)dataSource.Queries[0];
salesPersonQuery.Parameters.Add(new QueryParameter("startDate", typeof(Expression),
new Expression("[Parameters.yearParameter] + '/01/01'")));
salesPersonQuery.Parameters.Add(new QueryParameter("endDate", typeof(Expression),
new Expression("[Parameters.yearParameter] + '/12/31'")));
salesPersonQuery.Sql =
"select * from SalesPerson where OrderDate between @startDate and @endDate";
dashboardViewer1.Dashboard = dashboard;
}
public BundleReport(IContextService user, string primarykey)
{
var server = ConfigurationManager.AppSettings["Server"];
var usuario = ConfigurationManager.AppSettings["User"];
var password = ConfigurationManager.AppSettings["Password"];
DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer));
DataSource.Name = "Report";
var mainQuery = new CustomSqlQuery("Bundle", "SELECT *,'' as [ZonaDescripcion],'' as [OperarioDescripcion],'' as [AlmacenDescripcion] FROM [Bundle] ");
if (!string.IsNullOrEmpty(primarykey))
{
mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa));
mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey));
mainQuery.Sql = "SELECT k.*,a.descripcion as [AlmacenDescripcion], az.descripcion as [ZonaDescripcion],c.descripcion as [OperarioDescripcion] FROM [Bundle] as k " +
" inner join almacenes as a on a.empresa=k.empresa and a.id=k.fkalmacen " +
" left join almaceneszona as az on az.empresa=a.empresa and az.fkalmacenes=a.id and az.id=k.fkzonaalmacen " +
" left join cuentas as c on c.empresa=k.empresa and c.id=k.fkoperarios " +
" where k.empresa=@empresa and concat(k.lote,k.id)=@referencia";
}
DataSource.Queries.Add(mainQuery);
DataSource.Queries.Add(new CustomSqlQuery("Bundlelin", "SELECT * FROM [BundleLin]"));
DataSource.Relations.Add("Bundle", "Bundlelin", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("lote", "fkbundlelote"),
new RelationColumnInfo("id", "fkbundle")
});
DataSource.RebuildResultSchema();
}
public object GetDataSource(NameValueCollection args, bool designTime)
{
var query = new CustomSqlQuery();
query.Name = "Vouchers";
if (designTime)
{
query.Sql = "SELECT top(5) * FROM Voucher";
}
else
{
query.Sql = "SELECT * FROM Voucher";
}
//var mssqlConn = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer);
//var mysqlConn = new MySqlConnectionParameters("localhost", "db name", "username", "password", "port");
var ds = new SqlDataSource("iPro");
ds.Queries.Add(query);
ds.RebuildResultSchema();
return(ds);
}
private void BindToData(XtraReport report)
{
//// Create a data source.
//Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", "");
String connStr = "TestProject.Properties.Settings.NorthwindConnectionString";
DevExpress.DataAccess.Sql.SqlDataSource ds = new DevExpress.DataAccess.Sql.SqlDataSource(connStr);
// Create an SQL query to access the master table.
CustomSqlQuery queryCategories = new CustomSqlQuery();
queryCategories.Name = "queryCategories";
queryCategories.Sql = "SELECT * FROM Categories";
// Create an SQL query to access the detail table.
CustomSqlQuery queryProducts = new CustomSqlQuery();
queryProducts.Name = "queryProducts";
queryProducts.Sql = "SELECT * FROM Products";
// Add the queries to the data source collection.
ds.Queries.AddRange(new SqlQuery[] { queryCategories, queryProducts });
// Create a master-detail relation between the queries.
ds.Relations.Add("queryCategories", "queryProducts", "CategoryID", "CategoryID");
// Assign the data source to the report.
report.DataSource = ds;
report.DataMember = "queryCategories";
}
public void refresh_kuhni()
{
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
OracleConnectionParameters connectionParameters = new OracleConnectionParameters(vivaprocess.Global.ora_serv, "kuhni_modul", "kuhni");
SqlDataSource ds = new SqlDataSource(connectionParameters);
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery1";
query.Sql = "select ITEM_ID,prod_id as NN,prod_name as NN_КЛИЕНТА,client_name as КЛИЕНТ,reklamac_name as РЕКЛАМАЦИЯ,date_in as ДАТА_ПОСТУПЛ,"
+ " date_out as ДАТА_ВЫПОЛН from REKLAMAC where date_in is null"
+ " or (date_in between trunc(ADD_MONTHS((last_day(to_date('" + dateViewZakaz.Text + "','dd.mm.yyyy'))), 0), 'MM')"
+ " and trunc(last_day(to_date('" + dateViewZakaz.Text + "','dd.mm.yyyy')))) order by PROD_ID, reklamac_name";
sqlKuhni.Queries.Clear();
sqlKuhni.Queries.Add(query);
sqlKuhni.Fill();
bindKuhni.DataSource = sqlKuhni;
bindKuhni.DataMember = "customQuery1";
lookUpKuhni.Properties.DataSource = bindKuhni;
lookUpKuhni.Properties.ValueMember = "ITEM_ID";
lookUpKuhni.Properties.DisplayMember = "РЕКЛАМАЦИЯ";
lookUpKuhni.Properties.PopulateColumns();
lookUpKuhni.Properties.Columns["ITEM_ID"].Visible = false;
lookUpKuhni.Properties.Columns["NN"].FormatType = DevExpress.Utils.FormatType.Numeric;
lookUpKuhni.Properties.Columns["NN"].FormatString = "n0";
this.Cursor = System.Windows.Forms.Cursors.Default;
}
public CarteraVencimientosReport(IContextService user, string primarykey)
{
var server = ConfigurationManager.AppSettings["Server"];
var usuario = ConfigurationManager.AppSettings["User"];
var password = ConfigurationManager.AppSettings["Password"];
DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer));
DataSource.Name = "Report";
var mainQuery = new CustomSqlQuery("CarteraVencimientos", "SELECT * FROM [CarteraVencimientos] ");
if (!string.IsNullOrEmpty(primarykey))
{
mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa));
mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey));
mainQuery.Sql = "SELECT * FROM [CarteraVencimientos] where empresa=@empresa and referencia=@referencia";
}
DataSource.Queries.Add(mainQuery);
DataSource.Queries.Add(new CustomSqlQuery("Cuentas", "SELECT * FROM [Cuentas]"));
DataSource.Relations.Add("CarteraVencimientos", "Cuentas", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("fkcuentas", "id")
});
DataSource.RebuildResultSchema();
}
public Form1()
{
InitializeComponent();
Dashboard dashboard = new Dashboard();
DashboardSqlDataSource dataSource = new DashboardSqlDataSource();
CustomSqlQuery sqlQuery = new CustomSqlQuery("Countries", "select * from Countries");
dataSource.ConnectionParameters = new Access97ConnectionParameters(@"..\..\Data\countriesDB.mdb", "", "");
dataSource.Queries.Add(sqlQuery);
ChoroplethMapDashboardItem choroplethMap = new ChoroplethMapDashboardItem();
choroplethMap.DataSource = dataSource;
choroplethMap.DataMember = "Countries";
choroplethMap.Area = ShapefileArea.WorldCountries;
choroplethMap.AttributeName = "NAME";
choroplethMap.AttributeDimension = new Dimension("Country");
ValueMap populationMap = new ValueMap(new Measure("Population"));
choroplethMap.Maps.Add(populationMap);
dashboard.Items.Add(choroplethMap);
dashboardViewer1.Dashboard = dashboard;
}
private void lookUpCityF_EditValueChanged(object sender, EventArgs e)
{
try
{
lookUpTeachers.EditValue = null;
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery1";
if (lookUpCityF.Properties.GetDataSourceValue("city_id", lookUpCityF.ItemIndex).ToString().Trim() != "00")
{
query.Sql = "select 0 as id, '00' as code, '' as city, '_все преподаватели_' as fname, '' as lname, '' as sname, '' as date_birth,"
+ " '' as phone, '' as adres, '' as descr, '' as salary, '' as mail from dual union all "
+ "select t.id,t.code,t.city,t.fname,ifnull(t.lname,' ') as lname,ifnull(t.sname,' ') as sname,ifnull(DATE_FORMAT(t.date_birth,'%d.%m.%Y'),' ') as date_birth,ifnull(t.phone,' ') as phone,"
+ " ifnull(t.adres,' ') as adres,ifnull(t.descr,' ') as descr,ifnull(t.salary,' ') as salary,ifnull(t.mail,' ') as mail from teacher t"
+ " where t.city_id='" + lookUpCityF.Properties.GetDataSourceValue("city_id", lookUpCityF.ItemIndex).ToString().Trim() + "' order by 3,4"; //fname,lname
}
else
{
query.Sql = "select 0 as id, '00' as code, '' as city, '_все преподаватели_' as fname, '' as lname, '' as sname, '' as date_birth,"
+ " '' as phone, '' as adres, '' as descr, '' as salary, '' as mail from dual union all "
+ "select t.id,t.code,t.city,t.fname,ifnull(t.lname,' ') as lname,ifnull(t.sname,' ') as sname,ifnull(DATE_FORMAT(t.date_birth,'%d.%m.%Y'),' ') as date_birth,ifnull(t.phone,' ') as phone,"
+ " ifnull(t.adres,' ') as adres,ifnull(t.descr,' ') as descr,ifnull(t.salary,' ') as salary,ifnull(t.mail,' ') as mail from teacher t"
+ " order by 3,4";
}
sqlDataTeacher.Queries.Clear();
sqlDataTeacher.Queries.Add(query);
sqlDataTeacher.Fill();
bindTeacher.DataSource = sqlDataTeacher;
bindTeacher.DataMember = "customQuery1";
lookUpTeachers.Properties.DataSource = bindTeacher;
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
groupChangeTeacher.Visible = false;
if (lookUpGroup.ItemIndex > -1)
{
DialogResult SaveData = DevExpress.XtraEditors.XtraMessageBox.Show("Удалить группу " + lookUpGroup.Properties.GetDataSourceValue("code_group", lookUpGroup.ItemIndex).ToString().Trim() + " ? Предварительно очистите от учеников!", "Подтвержедние", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (SaveData == System.Windows.Forms.DialogResult.Yes)
{
var dbCon = DBConnection.Instance();
dbCon.DatabaseName = "victory_app";
if (dbCon.IsConnect())
{
try
{
string query = "SELECT count(scholar_id) FROM group_scholar where code_group='" + lookUpGroup.Properties.GetDataSourceValue("code_group", lookUpGroup.ItemIndex).ToString().Trim() + "'";
var cmd = new MySqlCommand(query, dbCon.Connection);
object result = cmd.ExecuteScalar();
if (result != null)
{
if (Convert.ToInt32(result) == 0)
{
try
{
string query2 = "delete from group_list where id=" + lookUpGroup.Properties.GetDataSourceValue("id", lookUpGroup.ItemIndex).ToString().Trim();
var cmd2 = new MySqlCommand(query2, dbCon.Connection);
cmd2.ExecuteNonQuery();
CustomSqlQuery query3 = new CustomSqlQuery();
query3.Name = "customQuery1";
query3.Sql = "select id,city,code_teacher,subj_name,code_group,ifnull(descr,' ') as descr,ifnull(schedule,' ') as schedule,state from group_list"
+ " where subj_name='" + lookUpSubject.Properties.GetDataSourceValue("subj_name", lookUpSubject.ItemIndex).ToString().Trim() + "'"
+ " and city='" + lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + "'"
+ " and code_teacher='" + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim() + "' order by code_group";
sqlDataGroup.Queries.Clear();
sqlDataGroup.Queries.Add(query3);
sqlDataGroup.Fill();
bindGroup.DataSource = sqlDataGroup;
bindGroup.DataMember = "customQuery1";
lookUpGroup.Properties.DataSource = bindGroup;
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
}
}
else
{
DevExpress.XtraEditors.XtraMessageBox.Show("Группа не удалена, очистите от учеников..");
}
}
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
}
}
}
}
}
public AlbaranesReport(IContextService user, string primarykey)
{
var server = ConfigurationManager.AppSettings["Server"];
var usuario = ConfigurationManager.AppSettings["User"];
var password = ConfigurationManager.AppSettings["Password"];
DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer));
DataSource.Name = "Report";
var mainQuery = new CustomSqlQuery("Albaranes", "SELECT * FROM [Albaranes] ");
if (!string.IsNullOrEmpty(primarykey))
{
mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa));
mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey));
mainQuery.Sql = "SELECT * FROM [Albaranes] where empresa=@empresa and referencia=@referencia";
}
DataSource.Queries.Add(new CustomSqlQuery("clientes", string.Format("SELECT c.*,d.direccion as [Direccioncliente],d.poblacion as [Poblacioncliente],d.cp as [Cpcliente],d.telefono as [Telefonocliente] FROM [Clientes] as c left join direcciones as d on d.empresa=c.empresa and d.tipotercero={0} and d.fkentidad=c.fkcuentas", (int)TiposCuentas.Clientes)));
DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT e.*,d.direccion as [Direccionempresa],d.poblacion as [Poblacionempresa],d.cp as [Cpempresa],d.telefono as [Telefonoempresa] FROM [Empresas] as e left join direcciones as d on d.empresa=e.id and d.tipotercero=-1 and d.fkentidad=e.id"));
DataSource.Queries.Add(mainQuery);
DataSource.Queries.Add(new CustomSqlQuery("Albaraneslin", "SELECT al.*,u.textocorto as [Unidadesdescripcion] FROM [AlbaranesLin] as al" +
" inner join Familiasproductos as fp on fp.empresa=al.empresa and fp.id=substring(al.fkarticulos,0,3)" +
" left join unidades as u on fp.fkunidadesmedida=u.id"));
DataSource.Queries.Add(new CustomSqlQuery("Albaranestotales", "SELECT * FROM [AlbaranesTotales]"));
DataSource.Queries.Add(new CustomSqlQuery("Formaspago", "SELECT * FROM [formaspago]"));
DataSource.Queries.Add(new CustomSqlQuery("Articulos", "SELECT * FROM Articulos"));
// Create a master-detail relation between the queries.
DataSource.Relations.Add("Albaranes", "Albaraneslin", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("id", "fkalbaranes")
});
DataSource.Relations.Add("Albaraneslin", "Articulos", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("fkarticulos", "id")
});
DataSource.Relations.Add("Albaranes", "Formaspago", new[] {
new RelationColumnInfo("fkformaspago", "id")
});
DataSource.Relations.Add("Albaranes", "Albaranestotales", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("id", "fkalbaranes")
});
DataSource.Relations.Add("Albaranes", "clientes", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("fkclientes", "fkcuentas")
});
DataSource.Relations.Add("Albaranes", "empresa", new[] {
new RelationColumnInfo("empresa", "id")
});
DataSource.RebuildResultSchema();
}
void CustomSqlQueryInitialization()
{
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "CustomQuery";
query.Sql = "Select top 10 * from Products";
DataSource.Queries.Add(query);
}
private void lookUpTeachers_EditValueChanged(object sender, EventArgs e)
{
groupChangeTeacher.Visible = false;
if (lookUpTeachers.ItemIndex > -1)
{
CustomSqlQuery query3 = new CustomSqlQuery();
query3.Name = "customQuery1";
query3.Sql = "select id,city,code_teacher,subj_name,code_group,ifnull(descr,' ') as descr,ifnull(schedule,' ') as schedule,state from group_list"
+ " where subj_name='" + lookUpSubject.Properties.GetDataSourceValue("subj_name", lookUpSubject.ItemIndex).ToString().Trim() + "'"
+ " and city='" + lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + "'"
+ " and code_teacher='" + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim() + "' order by code_group";
sqlDataGroup.Queries.Clear();
sqlDataGroup.Queries.Add(query3);
sqlDataGroup.Fill();
bindGroup.DataSource = sqlDataGroup;
bindGroup.DataMember = "customQuery1";
lookUpGroup.Properties.DataSource = bindGroup;
//lookUpTeachers.ItemIndex = 0;
var dbCon = DBConnection.Instance();
dbCon.DatabaseName = "victory_app";
if (dbCon.IsConnect())
{
try
{
string query = "select ifnull(time,' ') as time from teacher_subj"
+ " where city='" + lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + "'"
+ " and code='" + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim() + "'"
+ " and subj_name='" + lookUpSubject.Properties.GetDataSourceValue("subj_name", lookUpSubject.ItemIndex).ToString().Trim() + "'";
var cmd = new MySqlCommand(query, dbCon.Connection);
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
txtTeacherSchedule.Text = reader.GetString(0);
}
}
else
{
txtTeacherSchedule.Text = "";
}
reader.Close();
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
}
}
}
if ((lookUpTeachers.ItemIndex > -1) && (lookUpSubject.ItemIndex > -1) && (lookUpCityScholarF.ItemIndex > -1))
{
txtCreateGroup.Text = lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + lookUpSubject.Properties.GetDataSourceValue("subj_id", lookUpSubject.ItemIndex).ToString().Trim() + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim();
btnCreateGroup.Enabled = true;
}
}
public CustomSqlQuery QueryDepotList()
{
CustomSqlQuery query = new CustomSqlQuery
{
Name = "DepotList",
Sql = "SELECT customercode, customername FROM si_sop_customers WHERE master_customer='msho' and active=-1 and customer_type='D' and payment_type=2"
};
return(query);
}
private void btnSave_Click(object sender, EventArgs e)
{
groupChangeTeacher.Visible = false;
if (lookUpGroup.ItemIndex > -1)
{
DialogResult SaveData = DevExpress.XtraEditors.XtraMessageBox.Show("Сохранить данные по группе " + lookUpGroup.Properties.GetDataSourceValue("code_group", lookUpGroup.ItemIndex).ToString().Trim() + " ?", "Подтвержедние", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (SaveData == System.Windows.Forms.DialogResult.Yes)
{
var dbCon = DBConnection.Instance();
dbCon.DatabaseName = "victory_app";
if (dbCon.IsConnect())
{
try
{
int id, state;
id = Convert.ToInt32(lookUpGroup.Properties.GetDataSourceValue("id", lookUpGroup.ItemIndex).ToString().Trim());
if (chkWorkEnd.Checked)
{
state = 2;
}
else if (chkWork.Checked)
{
state = 1;
}
else
{
state = 0;
}
string query = "update group_list set schedule='" + txtSchedule.Text.Trim() + "',descr='" + txtGroupDescr.Text.Trim() + "',state=" + state + ",solo_registr=" + Convert.ToInt32(chkSoloRegistr.CheckState) + ""
+ " where id=" + id; //,code_teacher='" + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim() + "'
var cmd = new MySqlCommand(query, dbCon.Connection);
cmd.ExecuteNonQuery();
CustomSqlQuery query3 = new CustomSqlQuery();
query3.Name = "customQuery1";
query3.Sql = "select id,city,code_teacher,subj_name,code_group,ifnull(descr,' ') as descr,ifnull(schedule,' ') as schedule,state from group_list"
+ " where subj_name='" + lookUpSubject.Properties.GetDataSourceValue("subj_name", lookUpSubject.ItemIndex).ToString().Trim() + "'"
+ " and city='" + lookUpCityScholarF.Properties.GetDataSourceValue("city_id", lookUpCityScholarF.ItemIndex).ToString().Trim() + "'"
+ " and code_teacher='" + lookUpTeachers.Properties.GetDataSourceValue("code", lookUpTeachers.ItemIndex).ToString().Trim() + "' order by code_group";
sqlDataGroup.Queries.Clear();
sqlDataGroup.Queries.Add(query3);
sqlDataGroup.Fill();
bindGroup.DataSource = sqlDataGroup;
bindGroup.DataMember = "customQuery1";
lookUpGroup.Properties.DataSource = bindGroup;
lookUpGroup.EditValue = id;
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
}
}
}
}
}
private static SqlDataSource InitData()
{
MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer);
SqlDataSource ds = new SqlDataSource(connectionParameters);
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery1";
query.Sql = "SELECT * FROM Products";
ds.Queries.Add(query);
ds.Fill();
return(ds);
}
private void BindToData()
{
SqlDataSource ds = new SqlDataSource("Northwind");
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "Products";
query.Sql = "SELECT * FROM Products";
ds.Queries.Add(query);
ds.RebuildResultSchema();
ASPxReportDesigner1.DataSources.Add("Northwind", ds);
}
private void BindingTable(string name)
{
SqlDataSource sqlDataSource = new SqlDataSource(new CustomStringConnectionParameters(
Connection.connectionString));
CustomSqlQuery customSqlQuery = new CustomSqlQuery("cq", "select * from nhanvien");
SqlDataSource.DisableCustomQueryValidation = true;
sqlDataSource.Queries.Clear();
sqlDataSource.Queries.Add(customSqlQuery);
sqlDataSource.Fill();
gridControl1.DataSource = sqlDataSource;
gridControl1.DataMember = "cq";
}
private void reportTon1_DataSourceDemanded(object sender, EventArgs e)
{
CustomSqlQuery query = this.sqlDataSource1.Queries[0] as CustomSqlQuery;
query.Sql = oriQuery.Sql;
DateTime p_dt_date_from = Convert.ToDateTime(p_date_from.Value);
string p_str_date_from = p_dt_date_from.ToString("yyyy-MM-dd");
DateTime p_dt_date_to = Convert.ToDateTime(p_date_to.Value);
string p_str_date_to = p_dt_date_to.ToString("yyyy-MM-dd");
StringBuilder str = new StringBuilder();
StringBuilder sb_driver = new StringBuilder();
int drivercount;
drivercount = (this.p_driver.Value as IList).Count;
if (drivercount == 0)
{
}
else
{
sb_driver.Append(" and DriverName in (");
for (int i = 0; i < drivercount; i++)
{
string temp = (this.p_driver.Value as IList)[i].ToString();
temp.Replace("'", "''");
(this.p_driver.Value as IList)[i] = temp;
sb_driver.Append('\''); // Uncomment this line when parsing a string parameter value.
sb_driver.Append((this.p_driver.Value as IList)[i]);
sb_driver.Append('\''); // Uncomment this line when parsing a string parameter value.
if (i != drivercount - 1)
{
sb_driver.Append(',');
}
}
sb_driver.Append(')');
}
//str.Append("select DriverIC, DriverName, SUM(Amount) as Amount from vw_Salary where vw_Salary.TripTypeName = 'Trip' " + sb_driver.ToString() +" and DODate between \'" + p_str_date_from + "\' and \'" + p_str_date_to + "\' group by DriverIC, DriverName");
str.Append("select DriverIC, DriverName, sum([Amount] * [CommissionRate] / 100) as Amount from vw_Salary where vw_Salary.CommissionRate > 0 " + sb_driver.ToString() + " and DODate between \'" + p_str_date_from + "\' and \'" + p_str_date_to + "\' group by DriverIC, DriverName");
query.Sql = str.ToString();
sqlDataSource1.RebuildResultSchema();
}
//public UC_DM()
//{
// InitializeComponent();
//}
#endregion
#region Private functions
private void BindingTable(string name)
{
SqlDataSource sqlDataSource = new SqlDataSource("DESKTOP-TC830C5_QLDatTiecNhaHang");
//sqlDataSource1 = new SqlDataSource(setConnectionString);
CustomSqlQuery customSqlQuery = new CustomSqlQuery("cq", name);
SqlDataSource.DisableCustomQueryValidation = true;
sqlDataSource.Queries.Clear();
sqlDataSource.Queries.Add(customSqlQuery);
sqlDataSource.Fill();
gridControl1.DataSource = sqlDataSource;
gridControl1.DataMember = "cq";
}
private static CustomSqlQuery CreateSqlStringQuery()
{
CustomSqlQuery customSqlStringQuery = new CustomSqlQuery()
{
Name = "SalesPersons",
Sql = @"SELECT Categories.CategoryName, [Order Details].UnitPrice, [Order Details].Quantity,
Products.ProductName, Orders.OrderDate, Employees.LastName, Employees.FirstName
FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID"
};
return(customSqlStringQuery);
}
public TransformacioneslotesReport(IContextService user, string primarykey)
{
var server = ConfigurationManager.AppSettings["Server"];
var usuario = ConfigurationManager.AppSettings["User"];
var password = ConfigurationManager.AppSettings["Password"];
DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer));
DataSource.Name = "Report";
var mainQuery = new CustomSqlQuery("Transformacioneslotes", "SELECT *,'' as [CodigoTrabajo],'' as [Trabajo],'' as [CodigoAcabadoInicial],'' as [CodigoAcabadoFinal],'' as [AcabadoInicial],'' as [AcabadoFinal] FROM [Transformacioneslotes] ");
if (!string.IsNullOrEmpty(primarykey))
{
mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa));
mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey));
mainQuery.Sql = "SELECT t.*,tr.id as [CodigoTrabajo],tr.descripcion as [Trabajo],tr.fkacabadoinicial as [CodigoAcabadoInicial],tr.fkacabadofinal as [CodigoAcabadoFinal],aini.descripcion as [AcabadoInicial],afin.descripcion as [AcabadoFinal] FROM [Transformacioneslotes] as t " +
" inner join trabajos as tr on tr.empresa= t.empresa and tr.id=t.fktrabajos " +
" left join acabados as aini on aini.id = tr.fkacabadoinicial " +
" left join acabados as afin on aini.id = tr.fkacabadofinal " +
" where t.empresa=@empresa and t.referencia=@referencia";
}
DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT e.*,d.direccion as [Direccionempresa],d.poblacion as [Poblacionempresa],d.cp as [Cpempresa],d.telefono as [Telefonoempresa] FROM [Empresas] as e left join direcciones as d on d.empresa=e.id and d.tipotercero=-1 and d.fkentidad=e.id"));
DataSource.Queries.Add(mainQuery);
DataSource.Queries.Add(new CustomSqlQuery("Transformacionesloteslin", "SELECT pr.*,u.textocorto as [Unidadesdescripcion] FROM [TransformacioneslotesLin] as pr" +
" inner join Familiasproductos as fp on fp.empresa=pr.empresa and fp.id=substring(pr.fkarticulos,0,3) " +
" left join unidades as u on fp.fkunidadesmedida=u.id"));
// Create a master-detail relation between the queries.
DataSource.Relations.Add("Transformacioneslotes", "Transformacionesloteslin", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("id", "fkTransformacioneslotes")
});
DataSource.Relations.Add("Transformacioneslotes", "empresa", new[] {
new RelationColumnInfo("empresa", "id")
});
DataSource.Relations.Add("Transformacioneslotes", "Formaspago", new[] {
new RelationColumnInfo("fkformaspago", "id")
});
DataSource.RebuildResultSchema();
}
public AlbaranesComprasReport(IContextService user, string primarykey)
{
var server = ConfigurationManager.AppSettings["Server"];
var usuario = ConfigurationManager.AppSettings["User"];
var password = ConfigurationManager.AppSettings["Password"];
DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer));
DataSource.Name = "Report";
var mainQuery = new CustomSqlQuery("Albaranes", "SELECT * FROM [AlbaranesCompras] ");
if (!string.IsNullOrEmpty(primarykey))
{
mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa));
mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey));
mainQuery.Sql = "SELECT * FROM [AlbaranesCompras] where empresa=@empresa and referencia=@referencia";
}
DataSource.Queries.Add(new CustomSqlQuery("clientes", "SELECT * FROM [Proveedores]"));
DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT * FROM [Empresas]"));
DataSource.Queries.Add(mainQuery);
DataSource.Queries.Add(new CustomSqlQuery("Albaraneslin", "SELECT * FROM [AlbaranesComprasLin]"));
DataSource.Queries.Add(new CustomSqlQuery("Albaranestotales", "SELECT * FROM [AlbaranesComprasTotales]"));
// Create a master-detail relation between the queries.
DataSource.Relations.Add("Albaranes", "Albaraneslin", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("id", "fkalbaranes")
});
DataSource.Relations.Add("Albaranes", "Albaranestotales", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("id", "fkalbaranes")
});
DataSource.Relations.Add("Albaranes", "clientes", new[] {
new RelationColumnInfo("empresa", "empresa"),
new RelationColumnInfo("fkproveedores", "fkcuentas")
});
DataSource.Relations.Add("Albaranes", "empresa", new[] {
new RelationColumnInfo("empresa", "id")
});
DataSource.RebuildResultSchema();
}
public object GetDataSource(NameValueCollection args, bool designTime)
{
var query = new CustomSqlQuery();
query.Name = "Vouchers";
if (designTime)
{
query.Sql = "SELECT top(5) * FROM Voucher";
}
else
{
query.Sql = "SELECT * FROM Voucher";
}
//var mssqlConn = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer);
//var mysqlConn = new MySqlConnectionParameters("localhost", "db name", "username", "password", "port");
var ds = new SqlDataSource("iPro");
ds.Queries.Add(query);
ds.RebuildResultSchema();
return ds;
}
