public JsonNetResult Execute(string name, eDriverType drv, string sql, int?id, string qname, object [] args)
{
byte result = 1;
string msg = null;
long total = 0;
IEnumerable <dynamic> rows = new List <dynamic>();
try
{
string query = " (" + qname + ")";
if (id != null)
{
query = " №" + id + query;
}
ThreadContext.Properties["user"] = User.Identity.Name;
log.Info("Выполнение запроса -" + query + ".");
rows = dm.GetQData(name, drv, sql, args, itemsPerPage).ToList();
total = rows.Count();
}
catch (Exception e)
{
msg = e.Message;
result = 0;
}
JsonNetResult jr = new JsonNetResult();
jr.Data = new { success = result, message = msg, data = rows, total = total };
return(jr);
}
private void SetData()
{
settingup = true;
try
{
grpMachineConfig.Text = m_config.m_name;
Monitors.Enabled = true;
//grpPrjSerial.Enabled = true;
m_saved = m_config.m_driverconfig.m_drivertype;
FillMultiMon();
cmbMultiSel.SelectedItem = m_config.m_multimontype.ToString();
chkOverride.Checked = m_config.m_OverrideRenderSize;
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize.ToString("0.00");
txtPlatHeight.Text = "" + m_config.m_PlatYSize.ToString("0.00");
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
//projwidth.Text = "" + m_config.m_monitorconfig.XRes;
//projheight.Text = "" + m_config.m_monitorconfig.YRes;
txtXRes.Text = "" + m_config.XRenderSize.ToString();
txtYRes.Text = "" + m_config.YRenderSize.ToString();
SetMachineControls(m_config.MachineControls);
txtIp.Text = m_config.m_ip;
labelPressApply.Visible = false;
//FillConfiguredDisplays();
ShowMicron();
}
catch (Exception ex)
{
DebugLogger.Instance().LogError(ex);
}
settingup = false;
}
public ConnectionConfig m_connection; // main serial connection to printer
public DeviceDriverConfig()
{
m_drivertype = eDriverType.eGENERIC; // default to a null driver
m_connection = new ConnectionConfig();
m_connection.CreateDefault();
}
private void SetData()
{
try
{
lblMachineName.Text = m_config.m_name;
cmbMachineType.Items.Clear();
foreach (String s in Enum.GetNames(typeof(MachineConfig.eMachineType)))
{
cmbMachineType.Items.Add(s);
}
cmbMachineType.SelectedItem = m_config.m_machinetype.ToString();
//cmbMachineType_SelectedIndexChanged(null, null); // update the gui
/*
* lstDrivers.Items.Clear();
* foreach (String s in Enum.GetNames(typeof(eDriverType)))
* {
* lstDrivers.Items.Add(s);
* }
* lstDrivers.SelectedItem = m_config.m_driverconfig.m_drivertype.ToString();
* */
m_saved = m_config.m_driverconfig.m_drivertype;
//() check connection
/*
* if (UVDLPApp.Instance().m_deviceinterface.Connected)
* {
* grpDriver.Enabled = false;
* }
* else
* {
* grpDriver.Enabled = true;
* }
*/
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize;
txtPlatHeight.Text = "" + m_config.m_PlatYSize;
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
projwidth.Text = "" + m_config.m_monitorconfig.XRes;
projheight.Text = "" + m_config.m_monitorconfig.YRes;
//txtXFeed.Text = m_config.XMaxFeedrate.ToString();
//txtYFeed.Text = m_config.YMaxFeedrate.ToString();
//txtZFeed.Text = m_config.ZMaxFeedrate.ToString();
//select the current monitor
int idx = 0;
foreach (String s in lstMonitors.Items)
{
if (s.Equals(m_config.m_monitorconfig.Monitorid))
{
lstMonitors.SelectedIndex = idx;
}
idx++;
}
}
catch (Exception)
{
}
}
public JsonNetResult GetTables(string name, eDriverType drv, int page, int limit, string query)
{
byte result = 1;
string msg = null;
long total = 0;
List <dynamic> tables = new List <dynamic>();
try
{
// поиск по алиасу (qb_aliases)
List <string> tnames = null;
if (!string.IsNullOrEmpty(query))
{
tnames = db.Fetch <string>("SELECT a.name FROM qb_aliases a WHERE " + Misc.FilterField1("a.remark", query));
}
// поиск по имени таблицы
asv.Managers.DataManager dm = new asv.Managers.DataManager();
tables = dm.GetSData(name, drv, page, limit, query, tnames).ToList();
total = tables.Count();
tnames = tables.ConvertAll <string>(new Converter <dynamic, string>(t => { return(t["name"]); }));
string sql = "SELECT a.name key, a.remark value FROM qb_aliases a WHERE a.parentid IS NULL";
if (tnames.Count > 0)
{
sql += " AND a.name IN ('" + string.Join("', '", tnames) + "')";
}
string key = null;
IDictionary <string, string> aliases = db.Fetch <Pair <string, string> >(sql).ToDictionary(i => i.Key, i => i.Value);
foreach (IDictionary <string, object> t in tables)
{
key = t["name"].ToString();
if (aliases.ContainsKey(key))
{
t["rem"] = aliases[key];
}
}
}
catch (Exception e)
{
msg = e.Message;
result = 0;
}
JsonNetResult jr = new JsonNetResult();
jr.Data = new { success = result, message = msg, data = tables, total = total };
return(jr);
}
public bool Load(XmlHelper xh, XmlNode parent) // use new xml system -SHS
{
bool retval = false;
XmlNode mdc = xh.FindSection(parent, "MotorsDriverConfig");
m_drivertype = (eDriverType)xh.GetEnum(mdc, "DriverType", typeof(eDriverType), eDriverType.eGENERIC);
if (m_connection.Load(xh, mdc))
{
retval = true;
}
return retval;
}
public static DeviceDriver Create(eDriverType type)
{
switch (type)
{
case eDriverType.eNULL_DRIVER:
return new NULLdriver();
case eDriverType.eGENERIC:
return new GenericDriver();
default:
return null;
}
}
public bool Load(XmlHelper xh, XmlNode parent) // use new xml system -SHS
{
bool retval = false;
XmlNode mdc = xh.FindSection(parent, "MotorsDriverConfig");
m_drivertype = (eDriverType)xh.GetEnum(mdc, "DriverType", typeof(eDriverType), eDriverType.eGENERIC);
if (m_connection.Load(xh, mdc))
{
retval = true;
}
return(retval);
}
/// <summary>
/// Струтктура таблицы
/// </summary>
/// <param name="name">имя соединения</param>
/// <param name="table">имя таблицы</param>
/// <param name="drv">драйвер соединения</param>
/// <param name="idx">выводить список индексов</param>
/// <returns>List</returns>
public List <dynamic> GetTData(string name, string table, eDriverType drv)
{
List <dynamic> nodes = new List <dynamic>();
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[name];
if (css != null)
{
string[] items = System.Text.RegularExpressions.Regex.Split(css.ConnectionString, @";(\s)?");
string schema = null,
v = Array.Find <string>(items, pair => { return(pair.StartsWith("CURRENTSCHEME=", StringComparison.CurrentCultureIgnoreCase)); });
if (!string.IsNullOrEmpty(v))
{
schema = v.Split('=')[1];
}
// caché default schema
if (drv == eDriverType.DriverCaché && table.StartsWith("SQLUser."))
{
table = table.Replace("SQLUser.", "User.");
}
OdbcConnection con = new OdbcConnection(css.ConnectionString);
con.Open();
// поля
IList <Field> fields = new List <Field>();
foreach (Field f in GetFields(con, table, schema, drv, false))
{
f.Leaf = 1;
fields.Add(f);
}
nodes.Add(new { name = "Поля", nt = eNodeType.NodeFolder, data = fields });
// индексы
nodes.Add(new { name = "Индексы", nt = eNodeType.NodeFolder, data = GetIndexes(con, table, schema, drv).ToList() });
// внешние ключи
IList <Field> fkeys = new List <Field>();
foreach (ForeignKey f in GetFKeys(con, table, schema, drv))
{
f.Leaf = 1;
fkeys.Add(f);
}
nodes.Add(new { name = "Внешние ключи", nt = eNodeType.NodeFolder, data = fkeys });
con.Close();
}
return(nodes);
}
public static DeviceDriver Create(eDriverType type)
{
switch (type)
{
case eDriverType.eNULL_DRIVER:
return new NULLdriver();
case eDriverType.eGENERIC:
return new GenericDriver();
case eDriverType.eRF_3DLPRINTER:
return new RobotFactorySRL_3DLPrinter();
default:
return null;
}
}
public static DeviceDriver Create(eDriverType type)
{
switch (type)
{
case eDriverType.eNULL_DRIVER:
return(new NULLdriver());
case eDriverType.eGENERIC:
return(new GenericDriver());
default:
return(null);
}
}
public static DeviceDriver Create(eDriverType type)
{
switch (type)
{
case eDriverType.eNULL_DRIVER:
return(new NULLdriver());
case eDriverType.eGENERIC:
return(new GenericDriver());
case eDriverType.eRF_3DLPRINTER:
return(new RobotFactorySRL_3DLPrinter());
default:
return(null);
}
}
public static ePlatformType GetDriverPlatformType(eDriverType driver)
{
switch (driver)
{
case eDriverType.InternalBrowser:
case eDriverType.SeleniumFireFox:
case eDriverType.SeleniumChrome:
case eDriverType.SeleniumIE:
case eDriverType.SeleniumRemoteWebDriver:
case eDriverType.SeleniumEdge:
return(ePlatformType.Web);
case eDriverType.ASCF:
return(ePlatformType.ASCF);
case eDriverType.DOSConsole:
return(ePlatformType.DOS);
case eDriverType.UnixShell:
return(ePlatformType.Unix);
case eDriverType.WebServices:
return(ePlatformType.WebServices);
case eDriverType.WindowsAutomation:
return(ePlatformType.Windows);
case eDriverType.Appium:
return(ePlatformType.Mobile);
case eDriverType.PowerBuilder:
return(ePlatformType.PowerBuilder);
case eDriverType.JavaDriver:
return(ePlatformType.Java);
case eDriverType.MainFrame3270:
return(ePlatformType.MainFrame);
//case eDriverType.AndroidADB:
// return ePlatformType.AndroidDevice;
default:
return(ePlatformType.NA);
}
}
private void SetData()
{
try
{
foreach (String s in Enum.GetNames(typeof(eDriverType)))
{
lstDrivers.Items.Add(s);
}
lstDrivers.SelectedItem = m_config.m_driverconfig.m_drivertype.ToString();
m_saved = m_config.m_driverconfig.m_drivertype;
//() check connection
if (UVDLPApp.Instance().m_deviceinterface.Connected)
{
grpDriver.Enabled = false;
}
else
{
grpDriver.Enabled = true;;
}
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize;
txtPlatHeight.Text = "" + m_config.m_PlatYSize;
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
projwidth.Text = "" + m_config.XRes;
projheight.Text = "" + m_config.YRes;
txtXFeed.Text = m_config.XMaxFeedrate.ToString();
txtYFeed.Text = m_config.YMaxFeedrate.ToString();
txtZFeed.Text = m_config.ZMaxFeedrate.ToString();
//select the current monitor
int idx = 0;
foreach (String s in lstMonitors.Items)
{
if (s.Equals(m_config.Monitorid))
{
lstMonitors.SelectedIndex = idx;
}
idx++;
}
}
catch (Exception)
{
}
}
private void SetData()
{
settingup = true;
try
{
//lblMachineName.Text = m_config.m_name;
grpMachineConfig.Text = m_config.m_name;
Monitors.Enabled = true;
grpPrjSerial.Enabled = true;
//cmbMachineType.Items.Clear();
/*
* foreach(String s in Enum.GetNames(typeof(MachineConfig.eMachineType)))
* {
* cmbMachineType.Items.Add(s);
* }
* cmbMachineType.SelectedItem = m_config.m_machinetype.ToString();
* */
m_saved = m_config.m_driverconfig.m_drivertype;
FillMultiMon();
cmbMultiSel.SelectedItem = m_config.m_multimontype.ToString();
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize.ToString("0.00");
txtPlatHeight.Text = "" + m_config.m_PlatYSize.ToString("0.00");
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
//projwidth.Text = "" + m_config.m_monitorconfig.XRes;
//projheight.Text = "" + m_config.m_monitorconfig.YRes;
txtXRes.Text = "" + m_config.XRenderSize.ToString();
txtYRes.Text = "" + m_config.YRenderSize.ToString();
SetMachineControls(m_config.MachineControls);
labelPressApply.Visible = false;
FillConfiguredDisplays();
ShowMicron();
}
catch (Exception ex)
{
DebugLogger.Instance().LogError(ex);
}
settingup = false;
}
public JsonNetResult GetReport(string name, eDriverType drv, string sql, string json, int repId, int?id, string qname, string group, string subgroup, int userdefined)
{
byte result = 1;
string msg = null;
List <UParam> pars = Newtonsoft.Json.JsonConvert.DeserializeObject <List <UParam> >(json);
string path = null;
try
{
string query = " (" + qname + ")";
if (id != null)
{
query = " №" + id + query;
}
ThreadContext.Properties["user"] = User.Identity.Name;
log.Info("Экспорт запроса -" + query + ".");
ReportManager rm = new ReportManager(System.Web.HttpContext.Current.Server.MapPath(@"\" + REPORTSPATH));
rm.userParams = pars;
string[] args = null;
if (userdefined == 0)
{
args = pars.ConvertAll <string>(new Converter <UParam, string>(p => { return(p.Def); })).ToArray();
}
path = REPORTSPATH + "/" + repId + "/" + rm.GenerateReport(name, drv, sql, args, qname, group, subgroup, repId, User);
}
catch (Exception e)
{
msg = e.Message;
result = 0;
}
JsonNetResult jr = new JsonNetResult();
jr.Data = new { success = result, message = msg, link = path };
return(jr);
}
public bool Load(XmlReader xr)
{
try
{
bool retval = false;
xr.ReadStartElement("DriverConfig");
m_drivertype = (eDriverType)Enum.Parse(typeof(eDriverType), xr.ReadElementString("DriverType"));
if (m_connection.Load(xr))
{
retval = true;
}
xr.ReadEndElement();
return(retval);
}
catch (Exception ex)
{
DebugLogger.Instance().LogRecord(ex.Message);
return(false);
}
}
public bool Load(XmlReader xr)
{
try
{
bool retval = false;
xr.ReadStartElement("DriverConfig");
m_drivertype = (eDriverType)Enum.Parse(typeof(eDriverType), xr.ReadElementString("DriverType"));
if (m_connection.Load(xr))
{
retval = true;
}
xr.ReadEndElement();
return retval;
}
catch (Exception ex)
{
DebugLogger.Instance().LogRecord(ex.Message);
return false;
}
}
private void SetData()
{
try
{
//lblMachineName.Text = m_config.m_name;
grpMachineConfig.Text = m_config.m_name;
Monitors.Enabled = true;
grpPrjSerial.Enabled = true;
//cmbMachineType.Items.Clear();
/*
foreach(String s in Enum.GetNames(typeof(MachineConfig.eMachineType)))
{
cmbMachineType.Items.Add(s);
}
cmbMachineType.SelectedItem = m_config.m_machinetype.ToString();
* */
m_saved = m_config.m_driverconfig.m_drivertype;
FillMultiMon();
cmbMultiSel.SelectedItem = m_config.m_multimontype.ToString();
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize;
txtPlatHeight.Text = "" + m_config.m_PlatYSize;
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
//projwidth.Text = "" + m_config.m_monitorconfig.XRes;
//projheight.Text = "" + m_config.m_monitorconfig.YRes;
txtXRes.Text = "" + m_config.XRenderSize.ToString();
txtYRes.Text = "" + m_config.YRenderSize.ToString();
SetMachineControls(m_config.MachineControls);
labelPressApply.Visible = false;
FillConfiguredDisplays();
}
catch (Exception)
{
}
}
public ConnectionConfig m_connection; // main serial connection to printer
public DeviceDriverConfig()
{
m_drivertype = eDriverType.eGENERIC; // default to a null driver
m_connection = new ConnectionConfig();
m_connection.CreateDefault();
}
public static ePlatformType GetDriverPlatformType(eDriverType driver)
{
switch (driver)
{
case eDriverType.InternalBrowser:
return(ePlatformType.Web);
case eDriverType.SeleniumFireFox:
return(ePlatformType.Web);
case eDriverType.SeleniumChrome:
return(ePlatformType.Web);
case eDriverType.SeleniumIE:
return(ePlatformType.Web);
case eDriverType.SeleniumRemoteWebDriver:
return(ePlatformType.Web);
case eDriverType.SeleniumEdge:
return(ePlatformType.Web);
case eDriverType.SeleniumPhantomJS:
return(ePlatformType.Web);
case eDriverType.ASCF:
return(ePlatformType.ASCF);
case eDriverType.DOSConsole:
return(ePlatformType.DOS);
case eDriverType.UnixShell:
return(ePlatformType.Unix);
case eDriverType.WebServices:
return(ePlatformType.WebServices);
case eDriverType.WindowsAutomation:
return(ePlatformType.Windows);
case eDriverType.FlaUIWindow:
return(ePlatformType.Windows);
case eDriverType.MobileAppiumAndroid:
case eDriverType.MobileAppiumIOS:
//Add Perfecto Mobile
case eDriverType.PerfectoMobile:
return(ePlatformType.Mobile);
case eDriverType.MobileAppiumAndroidBrowser:
case eDriverType.MobileAppiumIOSBrowser:
return(ePlatformType.Mobile);
case eDriverType.PowerBuilder:
return(ePlatformType.PowerBuilder);
case eDriverType.FlaUIPB:
return(ePlatformType.PowerBuilder);
case eDriverType.JavaDriver:
return(ePlatformType.Java);
case eDriverType.MainFrame3270:
return(ePlatformType.MainFrame);
case eDriverType.AndroidADB:
return(ePlatformType.AndroidDevice);
default:
return(ePlatformType.NA);
}
}
/// <summary>
/// Возвращает результат запроса
/// </summary>
/// <param name="name">Имя соединения</param>
/// <param name="sql">Текст запроса</param>
/// <param name="drv">Драйвер соединения</param>
/// <param name="fromPage">Страница с (если указана -1 выбираются все записи)</param>
/// <param name="toPage">Страница по</param>
/// <param name="limit">Записей на странице</param>
/// <returns>IEnumerable</returns>
public IEnumerable <dynamic> GetQData(string name, eDriverType drv, string sql, object [] args, int limit = -1)
{
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[name];
if (css != null)
{
string conStr = css.ConnectionString;
// авторизация на сервере
if (Person.ServerLogin == 1)
{
string passw = null;
string[] tokens = Person.Identity.Name.Split(':');
if (tokens.Length == 2)
{
passw = tokens[1];
}
conStr = Misc.ConnCredentials(css.ConnectionString, Person.Identity.Name, passw);
}
;
OdbcConnection con = new OdbcConnection(conStr);
con.Open();
if (limit != -1)
{
switch (drv)
{
case eDriverType.DriverCaché:
sql = sql.Insert(6, " TOP " + limit);
break;
case eDriverType.DriverDB2:
sql = sql.TrimEnd(';') + " FETCH FIRST " + limit + " ROWS ONLY;";
break;
}
}
OdbcCommand com = new OdbcCommand(sql, con);
com.CommandTimeout = ConnTimeout;
if (args != null)
{
for (int i = 0; i < args.Length; ++i)
{
com.Parameters.AddWithValue("@" + i + 1, args[i]);
}
}
OdbcDataReader reader = com.ExecuteReader();
while (reader.Read())
{
yield return(DataToExpando(reader));
}
reader.Close();
con.Close();
}
}
private IEnumerable <Field> GetIndexes(OdbcConnection con, string table, string schema, eDriverType drv)
{
OdbcCommand com = new OdbcCommand();
com.Connection = con;
com.CommandTimeout = ConnTimeout;
com.Parameters.AddWithValue("@var", table);
string cmdText = null;
switch (drv)
{
case eDriverType.DriverCaché:
cmdText = "SELECT Name, CAST(PrimaryKey AS INT) pk FROM %Dictionary.IndexDefinition WHERE parent->Id = ? ORDER BY pk DESC";
break;
case eDriverType.DriverDB2:
cmdText = "SELECT CONSTNAME name, (CASE WHEN TYPE = 'P' THEN 1 END) pk FROM syscat.tabconst WHERE type IN ('P', 'U') AND tabname = ?";
if (!string.IsNullOrEmpty(schema))
{
cmdText += " AND tabschema = ?";
com.Parameters.AddWithValue("@var", schema);
}
cmdText += " ORDER BY type";
break;
}
com.CommandText = cmdText;
OdbcDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Field f = new Field(reader["name"], reader["pk"].Equals(1) ? eNodeType.NodePrimaryKey : eNodeType.NodeField);
f.Leaf = 1;
yield return(f);
}
reader.Close();
}
public string ExportReport(string name, eDriverType drv, string sql, object [] args, string qname, string group, string subgroup, MemberPrincipal person)
{
this.person = person;
GenerateParams(qname, group, subgroup);
int cn = 0, rn = 4, sn = 0;
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Лист" + sn++);
IRow row = null;
sheet.SetColumnWidth(cn, 6144);
#region header cell style
IFont hf = wb.CreateFont();
hf.FontHeightInPoints = 18;
hf.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle hs = wb.CreateCellStyle();
hs.SetFont(hf);
#endregion
#region border cell style
ICellStyle bs = wb.CreateCellStyle();
bs.BorderLeft = bs.BorderRight = bs.BorderTop = bs.BorderBottom = BorderStyle.Thin;
#endregion
#region table header cell style
IFont tf = wb.CreateFont();
tf.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle ts = wb.CreateCellStyle();
ts.CloneStyleFrom(bs);
ts.Alignment = HorizontalAlignment.Center;
ts.FillPattern = FillPattern.SolidForeground;
ts.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
ts.SetFont(tf);
#endregion
#region cell style
ICellStyle cs = wb.CreateCellStyle();
cs.CloneStyleFrom(bs);
cs.WrapText = true;
#endregion
#region date cell style
ICellStyle ds1 = wb.CreateCellStyle();
ds1.DataFormat = wb.CreateDataFormat().GetFormat("d.mm.yyyy hh:mm");
ICellStyle ds2 = wb.CreateCellStyle();
ds2.CloneStyleFrom(cs);
ds2.DataFormat = (short)BuiltinFormats.GetBuiltinFormat("m/d/yy");
#endregion
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellStyle = hs;
cell.SetCellValue(qname);
row = sheet.CreateRow(2);
row.CreateCell(0).SetCellValue("Подготовил:");
row.CreateCell(1).SetCellValue(person.Fio);
row = sheet.CreateRow(3);
row.CreateCell(0).SetCellValue("Дата:");
cell = row.CreateCell(1);
cell.CellStyle = ds1;
cell.SetCellValue(DateTime.Now);
string v = null;
foreach (UParam p in userParams)
{
v = !string.IsNullOrEmpty(p.Descr) ? p.Descr : p.Field;
row = sheet.CreateRow(rn++);
row.CreateCell(0).SetCellValue(v);
row.CreateCell(1).SetCellValue(p.Def);
}
// 1 пустой ряд
rn++;
DataManager dm = new DataManager();
dm.Person = person;
dynamic q;
List <dynamic> data = dm.GetQData(name, drv, sql, args, -1).ToList();
int total = data.Count();
if (total > 0)
{
int i, j,
sheets = total / XLSMAXROWS,
rem = total % XLSMAXROWS;
for (i = 0; i < sheets; ++i)
{
// заголовок
q = data[0];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (string key in q.Keys)
{
sheet.SetColumnWidth(cn, 6144);
cell = row.CreateCell(cn++);
cell.CellStyle = ts;
cell.SetCellValue(key);
}
for (j = 0; j < XLSMAXROWS; ++j)
{
q = data[i * XLSMAXROWS + j];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (object val in q.Values)
{
cell = row.CreateCell(cn++);
cell.CellStyle = cs;
if (val is DateTime)
{
cell.CellStyle = ds2;
cell.SetCellValue((DateTime)val);
}
else if (val is Double)
{
cell.SetCellValue((Double)val);
}
else if (val is Int16)
{
cell.SetCellValue((Int16)val);
}
else if (val is Int32)
{
cell.SetCellValue((Int32)val);
}
else if (val is Int64)
{
cell.SetCellValue((Int64)val);
}
else if (val is byte[])
{
cell.SetCellValue(Convert.ToBase64String((byte[])val));
}
else
{
cell.SetCellValue(val.ToString());
}
}
}
sheet = wb.CreateSheet("Лист" + sn++);
rn = 0;
}
// последние ряды, если есть
// заголовки
q = data[0];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (string key in q.Keys)
{
sheet.SetColumnWidth(cn, 6144);
cell = row.CreateCell(cn++);
cell.CellStyle = ts;
cell.SetCellValue(key);
}
for (j = 0; j < rem; ++j)
{
q = data[i * XLSMAXROWS + j];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (object val in q.Values)
{
cell = row.CreateCell(cn++);
cell.CellStyle = cs;
if (val is DateTime)
{
cell.CellStyle = ds2;
cell.SetCellValue((DateTime)val);
}
else if (val is Double)
{
cell.SetCellValue((Double)val);
}
else if (val is Int16)
{
cell.SetCellValue((Int16)val);
}
else if (val is Int32)
{
cell.SetCellValue((Int32)val);
}
else if (val is Int64)
{
cell.SetCellValue((Int64)val);
}
else if (val is byte[])
{
cell.SetCellValue(Convert.ToBase64String((byte[])val));
}
else
{
cell.SetCellValue(val.ToString());
}
}
}
}
string path = "export_" + person.Id + ".xls";
using (FileStream fs = new FileStream(repPath + path, FileMode.Create))
{
wb.Write(fs);
}
return(path);
}
public JsonNetResult GetTables(string name, eDriverType drv)
{
byte result = 1;
string msg = null;
List <Node> nodes = new List <Node>();
try
{
// список таблиц
List <dynamic> tables = dm.GetSData(name, drv).ToList();
// разделы
string sql = @"SELECT n.id, (CASE WHEN n.leaf == 0 THEN '' ELSE n.name END) name, n.internalid, IFNULL(n.parentid, '') parentid, n.leaf, (CASE WHEN n.leaf == 1 THEN a.remark ELSE n.name END) rem
FROM qb_catalogs c JOIN qb_nodes n ON n.catalogid = c.id LEFT JOIN qb_aliases a ON a.name = n.name WHERE c.conn = @0 ORDER BY n.leaf, n.name";
nodes = db.Fetch <Node>(sql, name);
List <Node> leafs = nodes.FindAll(nd => nd.Leaf == 1);
int i = 0, ix;
string key = null;
Node node = null;
IDictionary <string, object> t;
IList <Node> nds;
while (i < tables.Count)
{
t = tables[i];
key = t["name"].ToString();
nds = leafs.FindAll(nd => nd.Name == key);
if (nds.Count > 0)
{
foreach (Node nd in nds)
{
switch (drv)
{
case eDriverType.DriverCaché:
nd.Od = t["od"].ToString();
break;
case eDriverType.DriverDB2:
nd.Schema = t["schema"].ToString();
break;
}
}
tables.RemoveAt(i);
}
else
{
i++;
}
}
if (nodes.Count > 0)
{
nodes = Misc.GetNodes(nodes, "");
}
int hidesys = db.ExecuteScalar <int>("SELECT c.hidesys FROM qb_connections c WHERE c.name = @0", name);
if (hidesys == 0 || User.IsAdmin == 1)
{
// алиасы
sql = "SELECT a.name key, a.remark value FROM qb_aliases a WHERE a.parentid IS NULL";
IDictionary <string, string> aliases = (IDictionary <string, string>)HttpContext.Cache[Misc.aliaskey];
if (aliases == null)
{
aliases = db.Fetch <Pair <string, string> >(sql).ToDictionary(o => o.Key, o => o.Value);
HttpContext.Cache.Add(Misc.aliaskey, aliases, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 20, 0), CacheItemPriority.Normal, null);
}
foreach (IDictionary <string, object> tb in tables)
{
key = tb["name"].ToString();
ix = leafs.FindIndex(nd => nd.Name == key);
if (ix == -1)
{
node = new Node();
node.Name = key;
node.Leaf = 1;
switch (drv)
{
case eDriverType.DriverCaché:
node.Od = tb["od"].ToString();
break;
case eDriverType.DriverDB2:
node.Schema = tb["schema"].ToString();
break;
}
if (aliases.ContainsKey(key))
{
node.Rem = aliases[key];
}
nodes.Add(node);
}
}
}
}
catch (Exception e)
{
msg = e.Message;
result = 0;
}
JsonNetResult jr = new JsonNetResult();
jr.Data = new { success = result, message = msg, data = nodes };
return(jr);
}
private void SetData()
{
settingup = true;
try
{
grpMachineConfig.Text = m_config.m_name;
Monitors.Enabled = true;
grpPrjSerial.Enabled = true;
m_saved = m_config.m_driverconfig.m_drivertype;
FillMultiMon();
cmbMultiSel.SelectedItem = m_config.m_multimontype.ToString();
chkOverride.Checked = m_config.m_OverrideRenderSize;
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize.ToString("0.00");
txtPlatHeight.Text = "" + m_config.m_PlatYSize.ToString("0.00");
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
//projwidth.Text = "" + m_config.m_monitorconfig.XRes;
//projheight.Text = "" + m_config.m_monitorconfig.YRes;
txtXRes.Text = "" + m_config.XRenderSize.ToString();
txtYRes.Text = "" + m_config.YRenderSize.ToString();
SetMachineControls(m_config.MachineControls);
labelPressApply.Visible = false;
FillConfiguredDisplays();
ShowMicron();
}
catch (Exception ex)
{
DebugLogger.Instance().LogError(ex);
}
settingup = false;
}
/// <summary>
/// Возвращает поля таблицы
/// </summary>
/// <param name="con">ODBC соединение</param>
/// <param name="table">Таблица</param>
/// <param name="schema">Схема</param>
/// <param name="drv">Драйвер</param>
/// <param name="needTp">Возвращать тип поля</param>
/// <returns>Список полей</returns>
public IEnumerable <Field> GetFields(OdbcConnection con, string table, string schema, eDriverType drv, bool needTp = true)
{
OdbcCommand com = new OdbcCommand();
com.Connection = con;
com.CommandTimeout = ConnTimeout;
com.Parameters.AddWithValue("@var", table);
string cmdText = null;
switch (drv)
{
case eDriverType.DriverCaché:
cmdText = @"SELECT (CASE WHEN p.SqlFieldName = '%%OID' THEN 'ID' ELSE p.SqlFieldName END) name, (CASE WHEN p.Name = '%%OID' THEN 1 ELSE i.primarykey END) pk,
(CASE WHEN p.Type = '%Library.Boolean' THEN 0
WHEN p.Type = '%Library.Date' THEN 1
WHEN p.Type = '%Library.Time' THEN 1
WHEN p.Type = '%Library.TimeStamp' THEN 1
WHEN p.Type = '%Library.BigInt' THEN 2
WHEN p.Type = '%Library.Decimal' THEN 2
WHEN p.Type = '%Library.Double' THEN 2
WHEN p.Type = '%Library.Float' THEN 2
WHEN p.Type = '%Library.Integer' THEN 2
WHEN p.Type = '%Library.Numeric' THEN 2
WHEN p.Type = '%Library.Smallint' THEN 2
WHEN p.Type = '%Library.TinyInt' THEN 2
ELSE 3
END) tp
FROM %Dictionary.CompiledProperty p LEFT JOIN %Dictionary.IndexDefinition i ON (i.Parent = p.Parent AND i.Data = p.Name)
WHERE (p.private = 1 OR p.storable = 1) AND p.parent = ? ORDER BY pk DESC, name";
break;
case eDriverType.DriverDB2:
cmdText = @"SELECT COLNAME name, (CASE WHEN KEYSEQ = 0 THEN NULL ELSE KEYSEQ END) pk,
(CASE WHEN TYPENAME = 'DATE' THEN 1
WHEN TYPENAME = 'DATETIME' THEN 1
WHEN TYPENAME = 'TIMESTAMP' THEN 1
WHEN TYPENAME = 'BIGINT' THEN 2
WHEN TYPENAME = 'DECFLOAT' THEN 2
WHEN TYPENAME = 'DECIMAL' THEN 2
WHEN TYPENAME = 'DOUBLE' THEN 2
WHEN TYPENAME = 'INT' THEN 2
WHEN TYPENAME = 'INTEGER' THEN 2
WHEN TYPENAME = 'NUMERIC' THEN 2
WHEN TYPENAME = 'REAL' THEN 2
WHEN TYPENAME = 'SMALLINT' THEN 2
ELSE 3
END) tp FROM syscat.columns WHERE tabname = ?";
if (!string.IsNullOrEmpty(schema))
{
cmdText += " AND tabschema = ?";
com.Parameters.AddWithValue("@var", schema);
}
cmdText += " ORDER BY pk, name";
break;
}
com.CommandText = cmdText;
eNodeType nt;
OdbcDataReader reader = com.ExecuteReader();
while (reader.Read())
{
nt = (reader["pk"] == DBNull.Value) ? eNodeType.NodeField : eNodeType.NodePrimaryKey;
TableField tf = new TableField(reader["name"], nt);
if (needTp)
{
tf.Ft = (eFieldType)reader["tp"];
}
yield return(tf);
}
reader.Close();
}
private void SetData()
{
try
{
lblMachineName.Text = m_config.m_name;
cmbMachineType.Items.Clear();
foreach(String s in Enum.GetNames(typeof(MachineConfig.eMachineType)))
{
cmbMachineType.Items.Add(s);
}
cmbMachineType.SelectedItem = m_config.m_machinetype.ToString();
//cmbMachineType_SelectedIndexChanged(null, null); // update the gui
lstDrivers.Items.Clear();
foreach (String s in Enum.GetNames(typeof(eDriverType)))
{
lstDrivers.Items.Add(s);
}
lstDrivers.SelectedItem = m_config.m_driverconfig.m_drivertype.ToString();
m_saved = m_config.m_driverconfig.m_drivertype;
//() check connection
if (UVDLPApp.Instance().m_deviceinterface.Connected)
{
grpDriver.Enabled = false;
}
else
{
grpDriver.Enabled = true;
}
//list the drivers
txtPlatWidth.Text = "" + m_config.m_PlatXSize;
txtPlatHeight.Text = "" + m_config.m_PlatYSize;
txtPlatTall.Text = m_config.m_PlatZSize.ToString();
projwidth.Text = "" + m_config.m_monitorconfig.XRes;
projheight.Text = "" + m_config.m_monitorconfig.YRes;
txtXFeed.Text = m_config.XMaxFeedrate.ToString();
txtYFeed.Text = m_config.YMaxFeedrate.ToString();
txtZFeed.Text = m_config.ZMaxFeedrate.ToString();
//select the current monitor
int idx = 0;
foreach (String s in lstMonitors.Items)
{
if (s.Equals(m_config.m_monitorconfig.Monitorid))
{
lstMonitors.SelectedIndex = idx;
}
idx++;
}
}
catch (Exception)
{
}
}
public IEnumerable <ForeignKey> GetFKeys(OdbcConnection con, string table, string schema, eDriverType drv)
{
OdbcCommand com = new OdbcCommand();
com.Connection = con;
com.CommandTimeout = ConnTimeout;
com.Parameters.AddWithValue("@var", table);
string cmdText = null;
switch (drv)
{
case eDriverType.DriverCaché:
cmdText = @"SELECT f.Name, c.SqlQualifiedNameQ reftabname, f.ReferencedClass refod, f.Properties fkcolnames, 'ID' pkcolnames, '' refschema FROM %Dictionary.ForeignKeyDefinition f
JOIN %Dictionary.CompiledClass c on c.Id = f.ReferencedClass WHERE f.Parent->ID = ?
UNION ALL SELECT p.Name, c.SqlQualifiedNameQ reftabname, p.Type refod, p.Name fkcolnames, NVL(p.Inverse, 'ID') pkcolnames, '' refschema FROM %Dictionary.CompiledProperty p
JOIN %Dictionary.CompiledClass c on (c.Id = p.Type AND c.ClassType='persistent') WHERE p.Parent->ID = ? AND p.Storable = 1 AND (p.RelationShip = 1 OR NOT p.Type %STARTSWITH '%')";
com.Parameters.AddWithValue("@var", table);
break;
case eDriverType.DriverDB2:
cmdText = "SELECT CONSTNAME name, TRIM(FK_COLNAMES) fkcolnames, TRIM(PK_COLNAMES) pkcolnames, REFTABNAME, TRIM(tabschema) refschema, '' refod FROM syscat.references WHERE tabname = ?";
if (!string.IsNullOrEmpty(schema))
{
cmdText += " AND tabschema = ?";
com.Parameters.AddWithValue("@var", schema);
}
cmdText += " ORDER BY constname";
break;
}
com.CommandText = cmdText;
OdbcDataReader reader = com.ExecuteReader();
while (reader.Read())
{
ForeignKey fk = new ForeignKey(reader["name"], reader["reftabname"], reader["fkcolnames"], reader["pkcolnames"]);
fk.RefOd = reader["refod"].ToString();
fk.RefSchema = reader["refschema"].ToString();
yield return(fk);
}
reader.Close();
}
/// <summary>
/// Структура соединения
/// </summary>
/// <param name="name">имя соединения</param>
/// <param name="drv">драйвер соединения</param>
/// <param name="page">страница с (если указана -1 выбираются все записи)</param>
/// <param name="limit">записей на странице</param>
/// <param name="query">фильтр</param>
/// <param name="tables">список доп. таблиц для фильтра</param>
/// <returns></returns>
public IEnumerable <dynamic> GetSData(string name, eDriverType drv, int page = -1, int limit = -1, string query = null, IList <string> tables = null)
{
List <dynamic> nodes = new List <dynamic>();
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[name];
if (css != null)
{
OdbcConnection con = new OdbcConnection(css.ConnectionString);
OdbcCommand com = new OdbcCommand();
OdbcDataReader reader = null;
con.Open();
com.Connection = con;
com.CommandTimeout = ConnTimeout;
bool uor = false;
string cmdText = null;
IList <string> filters = new List <string>();;
switch (drv)
{
case eDriverType.DriverCaché:
cmdText = "SELECT Id od, SqlQualifiedNameQ name FROM %Dictionary.CompiledClass WHERE ClassType = 'persistent' AND System = 0";
if (!string.IsNullOrEmpty(query))
{
cmdText += " AND " + Misc.FilterField("SqlqualifiedNameQ", query);
uor = true;
}
if (tables != null && tables.Count > 0)
{
cmdText += uor ? " OR " : " AND ";
foreach (string t in tables)
{
filters.Add("SqlqualifiedNameQ = '" + t + "'");
}
cmdText += string.Join(" OR ", filters);
}
cmdText += " ORDER BY name";
break;
case eDriverType.DriverDB2:
cmdText = "SELECT tabname name, TRIM(tabschema) schema FROM syscat.tables WHERE type = 'T'";
if (!string.IsNullOrEmpty(query))
{
cmdText += " AND " + Misc.FilterField("tabname", query);
uor = true;
}
if (tables != null && tables.Count > 0)
{
cmdText += uor ? " OR " : " AND ";
foreach (string t in tables)
{
filters.Add("tabname = '" + t + "'");
}
cmdText += string.Join(" OR ", filters);
}
string[] items = System.Text.RegularExpressions.Regex.Split(css.ConnectionString, @";(\s)?");
string db = null, v = Array.Find <string>(items, pair => { return(pair.StartsWith("CURRENTSCHEME=", StringComparison.CurrentCultureIgnoreCase)); });
if (!string.IsNullOrEmpty(v))
{
db = v.Split('=')[1];
}
if (!string.IsNullOrEmpty(db))
{
cmdText += " AND tabschema = ?";
com.Parameters.AddWithValue("@var", db);
}
cmdText += " ORDER BY tabname";
break;
}
if (page != -1)
{
// Split the SQL
PagingHelper.SQLParts parts;
if (!PagingHelper.SplitSQL(cmdText, out parts))
{
throw new Exception("Unable to parse SQL statement for paged query");
}
com.CommandText = parts.sqlCount;
TotalItems = Convert.ToInt64(com.ExecuteScalar());
string sqlPage = null;
int skip = (page - 1) * limit,
take = skip + limit;
switch (drv)
{
case eDriverType.DriverCaché:
parts.sqlSelectRemoved = parts.sqlSelectRemoved.Replace(parts.sqlOrderBy, string.Empty);
sqlPage = "SELECT TOP " + limit + " od, name, %vid FROM (SELECT " + parts.sqlSelectRemoved + ") WHERE %vid > " + skip + " AND %vid <= " + take + " ORDER BY name";
break;
case eDriverType.DriverDB2:
sqlPage = "SELECT name FROM (SELECT ROW_NUMBER() OVER (ORDER BY tabname) rn, " + parts.sqlSelectRemoved + ") WHERE rn >" + skip + " AND rn <=" + take + ";";
break;
}
com.CommandText = sqlPage;
}
else
{
com.CommandText = cmdText;
}
reader = com.ExecuteReader();
while (reader.Read())
{
var data = DataToExpando(reader);
data["nt"] = eNodeType.NodeTable;
yield return(data);
}
reader.Close();
}
}
public JsonNetResult GetTable(string name, eDriverType drv, string table, string od)
{
byte result = 1;
string msg = null;
Table sqltable = new Table();
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[name];
if (css != null)
{
string[] items = System.Text.RegularExpressions.Regex.Split(css.ConnectionString, @";(\s)?");
string schema = null,
v = Array.Find <string>(items, pair => { return(pair.StartsWith("CURRENTSCHEME=", StringComparison.CurrentCultureIgnoreCase)); });
if (!string.IsNullOrEmpty(v))
{
schema = v.Split('=')[1];
}
if (drv == eDriverType.DriverDB2)
{
od = table;
}
using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(css.ConnectionString))
{
try
{
con.Open();
// алиасы-поля
string arg = table.ToUpper(), key;
IDictionary <string, string> rems = (IDictionary <string, string>)HttpContext.Cache[Misc.remkey + arg];
if (rems == null)
{
rems = db.Fetch <Pair <string, string> >("SELECT UPPER(f.name) key, f.remark value FROM qb_aliases a JOIN qb_aliases f ON f.parentid = a.id WHERE UPPER(a.name) = @0", arg).ToDictionary(o => o.Key, o => o.Value);
HttpContext.Cache.Add(Misc.remkey + arg, rems, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 20, 0), CacheItemPriority.Normal, null);
}
List <Field> fields = new List <Field>();
foreach (TableField f in dm.GetFields(con, od, schema, drv))
{
key = f.Name.ToUpper();
if (rems.ContainsKey(key))
{
f.Remark = rems[key];
}
fields.Add(f);
}
Field field;
IList <ForeignKey> fkeys = new List <ForeignKey>();
foreach (ForeignKey k in dm.GetFKeys(con, od, schema, drv))
{
field = fields.Find(f => f.Name.Equals(k.Name));
if (field != null)
{
field.Nt = eNodeType.NodeForeignKey;
}
else
{
fields.Add(new TableField(k.Name, eNodeType.NodeForeignKey));
}
fkeys.Add(k);
}
sqltable.Fields = fields;
sqltable.FKeys = fkeys;
con.Close();
}
catch (Exception e)
{
msg = e.Message;
result = 0;
}
}
}
JsonNetResult jr = new JsonNetResult();
jr.Data = new { success = result, message = msg, table = sqltable };
return(jr);
}
private void SetData()
{
try
{
foreach (String s in Enum.GetNames(typeof(eDriverType)))
{
lstDrivers.Items.Add(s);
}
lstDrivers.SelectedItem = UVDLPApp.Instance().m_printerinfo.m_driverconfig.m_drivertype.ToString();
m_saved = UVDLPApp.Instance().m_printerinfo.m_driverconfig.m_drivertype;
//() check connection
if (UVDLPApp.Instance().m_deviceinterface.Connected)
{
grpDriver.Enabled = false;
}
else
{
grpDriver.Enabled = true; ;
}
//list the drivers
txtPlatWidth.Text = "" + UVDLPApp.Instance().m_printerinfo.m_PlatXSize;
txtPlatHeight.Text = "" + UVDLPApp.Instance().m_printerinfo.m_PlatYSize;
txtPlatTall.Text = UVDLPApp.Instance().m_printerinfo.m_PlatZSize.ToString();
projwidth.Text = "" + UVDLPApp.Instance().m_printerinfo.XRes;
projheight.Text = "" + UVDLPApp.Instance().m_printerinfo.YRes;
txtZFeed.Text = UVDLPApp.Instance().m_printerinfo.m_ZMaxFeedrate.ToString();
//select the current monitor
int idx = 0;
foreach (String s in lstMonitors.Items)
{
if (s.Equals(UVDLPApp.Instance().m_printerinfo.m_monitorid))
{
lstMonitors.SelectedIndex = idx;
}
idx++;
}
}
catch (Exception)
{
}
}
