public List <FormPropertyTable> FormFieldList(int formmid)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
List <FormPropertyTable> ListTmp = new List <FormPropertyTable>();
FormPropertyTable ObjTmp = null;
DBConn Obj = new DBConn();
try
{
string Query = "SELECT * FROM FormTableProperty where FormTableId=" + formmid;
cmd = new System.Data.SqlClient.SqlCommand(Query, Obj.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
ObjTmp = new FormPropertyTable();
ObjTmp.TableFieldId = int.Parse(SDR["TableFieldId"].ToString());
ObjTmp.FormTableId = int.Parse(SDR["FormTableId"].ToString());
ObjTmp.FieldName = SDR["FormFieldName"].ToString();
ObjTmp.FieldType = SDR["FieldType"].ToString();
ObjTmp.FieldValue = SDR["FieldValue"].ToString();
ObjTmp.DefualValue = SDR["DefaultValue"].ToString();
ObjTmp.FormFieldOrder = int.Parse(SDR["FormFieldOrder"].ToString());
ObjTmp.FieldColSpan = SDR["FieldColSpan"].ToString();
ObjTmp.FiledClass = SDR["Class"].ToString();
ObjTmp.Format = SDR["Format"].ToString();
ObjTmp.JavaScript = SDR["JavaScript"].ToString();
ObjTmp.CheckRights = SDR["CheckRights"].ToString();
ObjTmp.DisplayInDataTable = (bool)(SDR["DisplayInDataTable"]);
ListTmp.Add(ObjTmp);
}
}
catch (System.Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Obj.Con.Close(); Obj.Con.Dispose(); Obj.Con = null; }
return(ListTmp);
}
/// <summary>
/// Binds the data.
/// </summary>
private void BindData()
{
listView.Items.Clear();
string query = BuildQuery();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = query;
cmd.CommandTimeout = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["CommandTimedOut"]);
cmd.CommandType = CommandType.Text;
using (System.Data.SqlClient.SqlDataReader reader = SqlHelper.Default.ExecuteReader(cmd))
{
while (reader.Read())
{
ListViewItem objItem = this.listView.Items.Add(reader.GetGuid(0).ToString());
objItem.SubItems.Add(reader.GetString(3)); // Tx Number
objItem.SubItems.Add(reader.GetString(5));
objItem.SubItems.Add(reader.GetDateTime(4).ToString(Common.Utility.GetDateFormat())); //
objItem.SubItems.Add(reader.GetDateTime(6).ToString(Common.Utility.GetDateFormat()) == DateTime.Now.ToString(Common.Utility.GetDateFormat()) ? string.Empty : reader.GetDateTime(6).ToString(Common.Utility.GetDateFormat()));
objItem.SubItems.Add(reader.GetString(1));
}
}
}
/////////////////////////////////////////////////////////////////////////////////
// List free slots in inventory
/////////////////////////////////////////////////////////////////////////////////
public byte GetFreeSlot()
{
#region List free slots
List <byte> ListSlot = new List <byte>(Character.Information.Slots);
MsSQL ms = new MsSQL("SELECT * FROM char_items WHERE owner='" + Character.Information.CharacterID + "' AND slot >= '13' AND slot <= '" + Character.Information.Slots + "' AND inavatar='0'");
using (System.Data.SqlClient.SqlDataReader reader = ms.Read())
{
while (reader.Read())
{
ListSlot.Add(reader.GetByte(5));
}
}
ms.Close();
for (byte i = 13; i < Character.Information.Slots; i++)
{
if (!GetCheckFreeSlot(ListSlot, i))
{
return(i);
}
}
return(0);
#endregion
}
public Model.View[] GetViews()
{
List <Model.View> views = new List <Model.View>();
string sql = string.Format(@"SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS V
ORDER BY V.TABLE_NAME", DatabaseName);
System.Data.SqlClient.SqlDataReader dr = null;
try
{
dr = RunQuerySqlClient(sql);
System.Collections.ArrayList arrTableNames = new System.Collections.ArrayList();
while (dr.Read())
{
arrTableNames.Add((string)dr["TABLE_NAME"]);
}
dr.Close();
for (int i = 0; i < arrTableNames.Count; i++)
{
Model.View view = GetNewView((string)arrTableNames[i]);
views.Add(view);
}
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return((Model.View[])views.ToArray());
}
public Variables(string argConnection)
{
mConnection = argConnection;
System.String sql = " SELECT DISTINCT * FROM ( ";
sql += " SELECT ";
sql += " [Property], ";
sql += " (SELECT [Value] FROM Environment env1 ";
sql += " WHERE env1.Property = Environment.[Property] AND ";
sql += " env1.DtFrom = ";
sql += " (SELECT MAX(env.DtFrom) FROM Environment env ";
sql += " WHERE env.[Property] = env1.Property ";
sql += " AND env.DtFrom <= GetDate()) ";
sql += " ) AS [Value], ";
sql += " (SELECT MAX(env.DtFrom) FROM Environment env ";
sql += " WHERE env.[Property] = Environment.[Property] ";
sql += " AND env.DtFrom <= GetDate()) AS [DtFrom] ";
sql += " FROM Environment) [Environment] ORDER BY Property ";
System.Data.SqlClient.SqlDataReader dr =
SqlHelper.ExecuteReader(Connection, CommandType.Text, sql);
while (dr.Read())
{
this.Add(dr["Property"].ToString(), new Variable());
this[dr["Property"].ToString()].Property = dr["Property"].ToString();
this[dr["Property"].ToString()].Value = dr["Value"].ToString();
try
{
this[dr["Property"].ToString()].DtFrom = (System.DateTime)dr["DtFrom"];
}
catch (Exception)
{
this[dr["Property"].ToString()].DtFrom = DateTime.Today;
}
}
dr.Close();
}
public TList_Tabelasbd Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo, string vOrder)
{
TList_Tabelasbd lista = new TList_Tabelasbd();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo, vOrder));
while (reader.Read())
{
TRegistro_Tabelasbd reg = new TRegistro_Tabelasbd();
if (!(reader.IsDBNull(reader.GetOrdinal("TABLE_NAME"))))
{
reg.nome_tabela = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public Avenue.Workflow.Data.UserIterations GetByUserID(Int32 UserID)
{
System.Data.SqlClient.SqlCommand acommand = new System.Data.SqlClient.SqlCommand();
acommand.Connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AvenueHeightsConnection"].ConnectionString);
acommand.CommandText = "dbo.ave_UserIteration_GetByUserID";
acommand.CommandType = System.Data.CommandType.StoredProcedure;
acommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, 10, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));
acommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, 10, 0, null, System.Data.DataRowVersion.Current, false, UserID, "", "", ""));
try
{
if (acommand.Connection.State == System.Data.ConnectionState.Closed)
{
acommand.Connection.Open();
}
System.Data.SqlClient.SqlDataReader reader = acommand.ExecuteReader();
Avenue.Heights.Data.AvenueCollection refthis = this;
UserIteration.GenerateAvenueList(reader, ref refthis);
}
finally
{
acommand.Connection.Close();
}
return(this);
}
public TList_CentroResultado Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
TList_CentroResultado lista = new TList_CentroResultado();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = ExecutarBusca(SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_CentroResultado reg = new TRegistro_CentroResultado();
if (!(reader.IsDBNull(reader.GetOrdinal("CD_CentroResult"))))
{
reg.Cd_centroresult = reader.GetString(reader.GetOrdinal("CD_CentroResult"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("DS_CentroResultado"))))
{
reg.Ds_centroresultado = reader.GetString(reader.GetOrdinal("DS_CentroResultado"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("CD_CentroResult_Pai"))))
{
reg.Cd_centroresult_pai = reader.GetString(reader.GetOrdinal("CD_CentroResult_Pai"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_CentroResult_Pai")))
{
reg.Ds_centroresult_pai = reader.GetString(reader.GetOrdinal("DS_CentroResult_Pai"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("Nivel"))))
{
reg.Nivel = reader.GetDecimal(reader.GetOrdinal("Nivel"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("ST_Sintetico"))))
{
reg.St_sintetico = reader.GetString(reader.GetOrdinal("ST_Sintetico"));
}
if (!reader.IsDBNull(reader.GetOrdinal("TP_Registro")))
{
reg.Tp_registro = reader.GetString(reader.GetOrdinal("TP_Registro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ST_Deducao")))
{
reg.St_deducao = reader.GetString(reader.GetOrdinal("ST_Deducao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ST_Registro")))
{
reg.St_registro = reader.GetString(reader.GetOrdinal("ST_Registro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("PathCentroresult")))
{
reg.PathCentroresult = reader.GetString(reader.GetOrdinal("PathCentroresult"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
deletarBanco_Dados();
}
}
return(lista);
}
/// <summary>
/// Возвращает список объектов "Проект" из базы данных
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <param name="strErr">сообщение об ошибке</param>
/// <returns>список объектов класса "Проект"</returns>
public static List <CBudgetProject> GetBudgetProjectList(UniXP.Common.CProfile objProfile,
System.Data.SqlClient.SqlCommand cmdSQL, ref System.String strErr)
{
List <CBudgetProject> objList = new List <CBudgetProject>();
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("\nНе удалось получить соединение с базой данных.");
return(objList);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetBudgetProject]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_NUM", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_MES", System.Data.SqlDbType.NVarChar, 4000));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
if (rs.HasRows)
{
System.String strDscrpn = "";
while (rs.Read())
{
strDscrpn = (rs["BUDGETPROJECT_DESCRIPTION"] == System.DBNull.Value) ? "" : (System.String)rs["BUDGETPROJECT_DESCRIPTION"];
objList.Add(
new CBudgetProject()
{
ID = (System.Guid)rs["BUDGETPROJECT_GUID"],
Name = System.Convert.ToString(rs["BUDGETPROJECT_NAME"]),
Description = strDscrpn,
IsActive = System.Convert.ToBoolean(rs["BUDGETPROJECT_ACTIVE"]),
CodeIn1C = System.Convert.ToInt32(rs["BUDGETPROJECT_1C_CODE"])
}
);
}
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
strErr += ("\nНе удалось получить список объектов 'проект'. Текст ошибки: " + f.Message);
}
return(objList);
}
private void LoadDB()
{
System.Data.SqlClient.SqlConnection sqlConnect = null;
try
{
sqlConnect = new System.Data.SqlClient.SqlConnection("server=" + SQLServerAddress + "," + SQLServerPort + "; initial catalog=" + DatabaseName + "; Integrated Security=SSPI");
sqlConnect.Open();
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT ImageFileName, FacePositionXc, FacePositionYc, FacePositionW, FacePositionAngle, Eye1X, Eye1Y, Eye2X, Eye2Y, Template, Image, FaceImage FROM FaceList", sqlConnect);
System.Data.SqlClient.SqlDataReader reader = sqlCmd.ExecuteReader();
while (reader.Read())
{
TFaceRecord fr = new TFaceRecord();
fr.ImageFileName = reader.GetString(0);
fr.FacePosition = new FSDK.TFacePosition();
fr.FacePosition.xc = reader.GetInt32(1);
fr.FacePosition.yc = reader.GetInt32(2);
fr.FacePosition.w = reader.GetInt32(3);
fr.FacePosition.angle = reader.GetFloat(4);
fr.FacialFeatures = new FSDK.TPoint[2];
fr.FacialFeatures[0] = new FSDK.TPoint();
fr.FacialFeatures[0].x = reader.GetInt32(5);
fr.FacialFeatures[0].y = reader.GetInt32(6);
fr.FacialFeatures[1] = new FSDK.TPoint();
fr.FacialFeatures[1].x = reader.GetInt32(7);
fr.FacialFeatures[1].y = reader.GetInt32(8);
fr.Template = new byte[FSDK.TemplateSize];
reader.GetBytes(9, 0, fr.Template, 0, FSDK.TemplateSize);
Image img = Image.FromStream(new System.IO.MemoryStream(reader.GetSqlBinary(10).Value));
Image img_face = Image.FromStream(new System.IO.MemoryStream(reader.GetSqlBinary(11).Value));
fr.image = new FSDK.CImage(img);
fr.faceImage = new FSDK.CImage(img_face);
FaceList.Add(fr);
imageList1.Images.Add(fr.faceImage.ToCLRImage());
string fn = fr.ImageFileName;
listView1.Items.Add((imageList1.Images.Count - 1).ToString(), fn.Split('\\')[fn.Split('\\').Length - 1], imageList1.Images.Count - 1);
textBox1.Text += "File '" + fn + "' read from database\r\n";
img.Dispose();
img_face.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception on loading database");
}
finally
{
if (sqlConnect != null)
{
sqlConnect.Close();
}
}
}
public TList_SeqInutNFe Select(TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_SeqInutNFe lista = new TList_SeqInutNFe();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNm_Campo));
while (reader.Read())
{
TRegistro_SeqInutNFe reg = new TRegistro_SeqInutNFe();
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_serie")))
{
reg.Nr_serie = reader.GetString(reader.GetOrdinal("nr_serie"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_serienf")))
{
reg.Ds_serie = reader.GetString(reader.GetOrdinal("ds_serienf"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_modelo")))
{
reg.Cd_modelo = reader.GetString(reader.GetOrdinal("cd_modelo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_modelo")))
{
reg.Ds_modelo = reader.GetString(reader.GetOrdinal("ds_modelo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("id_sequencia")))
{
reg.Id_sequencia = reader.GetDecimal(reader.GetOrdinal("id_sequencia"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_nfinicial")))
{
reg.Nr_nfinicial = reader.GetDecimal(reader.GetOrdinal("nr_nfinicial"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_nffinal")))
{
reg.Nr_nffinal = reader.GetDecimal(reader.GetOrdinal("nr_nffinal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ano")))
{
reg.Ano = reader.GetDecimal(reader.GetOrdinal("ano"));
}
if (!reader.IsDBNull(reader.GetOrdinal("dh_processamento")))
{
reg.Dh_processamento = reader.GetDateTime(reader.GetOrdinal("dh_processamento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_protocolo")))
{
reg.Nr_protocolo = reader.GetDecimal(reader.GetOrdinal("nr_protocolo"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public TList_VolumeRecebido Select(string Cd_empresa,
string Cd_combustivel,
string Id_tanque,
DateTime Dt_emissao)
{
TList_VolumeRecebido lista = new TList_VolumeRecebido();
bool st_transacao = false;
if (Banco_Dados == null)
{
st_transacao = CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = ExecutarBusca(SqlCodeBusca(Cd_empresa, Cd_combustivel, Id_tanque, Dt_emissao));
try
{
while (reader.Read())
{
TRegistro_VolumeRecebido reg = new TRegistro_VolumeRecebido();
if (!reader.IsDBNull(reader.GetOrdinal("Nr_NotaFiscal")))
{
reg.Nr_notafiscal = reader.GetDecimal(reader.GetOrdinal("Nr_NotaFiscal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NR_LanctoFiscal")))
{
reg.Nr_lanctofiscal = reader.GetDecimal(reader.GetOrdinal("NR_LanctoFiscal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_NFItem")))
{
reg.Id_nfitem = reader.GetDecimal(reader.GetOrdinal("ID_NFItem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_SaiEnt")))
{
reg.Dt_saient = reader.GetDateTime(reader.GetOrdinal("DT_SaiEnt"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Id_Tanque")))
{
reg.Id_tanque = reader.GetDecimal(reader.GetOrdinal("Id_Tanque"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Produto")))
{
reg.Ds_combustivel = reader.GetString(reader.GetOrdinal("DS_Produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("sigla_unidade")))
{
reg.Sigla_unidade = reader.GetString(reader.GetOrdinal("sigla_unidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Quantidade")))
{
reg.Qtd_combustivel = reader.GetDecimal(reader.GetOrdinal("Quantidade"));
}
lista.Add(reg);
}
return(lista);
}
finally
{
reader.Close();
reader.Dispose();
if (st_transacao)
{
deletarBanco_Dados();
}
}
}
/// <summary>
/// Возвращает список настроект
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <param name="SettingNamesListForInitParams">список имён настроек, параметры которых необходимо проинициализировать</param>
/// <param name="strErr">текст ошибки</param>
/// <returns>список настроект</returns>
public static List <CSettingForImportData> GetSettingslist(UniXP.Common.CProfile objProfile, System.Data.SqlClient.SqlCommand cmdSQL,
List <System.String> SettingNamesListForInitParams, ref System.String strErr)
{
List <CSettingForImportData> objRet = new List <CSettingForImportData>();
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("\nНе удалось получить соединение с базой данных.");
return(objRet);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetSettings]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_NUM", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_MES", System.Data.SqlDbType.NVarChar, 4000));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
if (rs.HasRows)
{
CSettingForImportData objItem = null;
while (rs.Read())
{
objItem = new CSettingForImportData();
objItem.ID = (System.Guid)rs["Settings_Guid"];
objItem.Name = ((rs["Settings_Name"] == System.DBNull.Value) ? "" : System.Convert.ToString(rs["Settings_Name"]));
objItem.UserName = ((rs["Settings_UserName"] == System.DBNull.Value) ? System.Convert.ToString(rs["Settings_Name"]) : System.Convert.ToString(rs["Settings_UserName"]));
objItem.SettingsList = new List <CSettingItemForImportData>();
objItem.XMLSettings = new System.Xml.XmlDocument();
objItem.XMLSettings.LoadXml(rs.GetSqlXml(2).Value);
if (SettingNamesListForInitParams.SingleOrDefault <String>(x => x == objItem.Name) != null)
{
foreach (System.Xml.XmlNode objNode in objItem.XMLSettings.ChildNodes)
{
foreach (System.Xml.XmlNode objChildNode in objNode.ChildNodes)
{
objItem.SettingsList.Add(new CSettingItemForImportData()
{
TOOLS_ID = System.Convert.ToInt32(objChildNode.Attributes["TOOLS_ID"].Value),
TOOLS_NAME = System.Convert.ToString(objChildNode.Attributes["TOOLS_NAME"].Value),
TOOLS_USERNAME = System.Convert.ToString(objChildNode.Attributes["TOOLS_USERNAME"].Value),
TOOLS_DESCRIPTION = System.Convert.ToString(objChildNode.Attributes["TOOLS_DESCRIPTION"].Value),
TOOLS_VALUE = System.Convert.ToInt32(objChildNode.Attributes["TOOLS_VALUE"].Value),
TOOLSTYPE_ID = System.Convert.ToInt32(objChildNode.Attributes["TOOLSTYPE_ID"].Value)
});
}
}
}
objRet.Add(objItem);
}
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
strErr += (String.Format("Не удалось получить список настроек.\n\nТекст ошибки: {0}", f.Message));
}
return(objRet);
}
public SQLClient(string ConnStr) {
Reader = null;
setConn(ConnStr);
}
/// <summary>
/// 执行语句并返回结果
/// </summary>
/// <param name="SQL"></param>
public void ExecuteReader(String SQL) {
Conn.Open();
SQLCmd = new System.Data.SqlClient.SqlCommand(SQL, Conn);
Reader = SQLCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
public SQLClient() {
Reader = null;
}
/// <summary>
/// 执行语句并返回结果
/// </summary>
public void ExecuteReader() {
Conn.Open();
Reader = SQLCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
public TList_CartaFrete Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
TList_CartaFrete lista = new TList_CartaFrete();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
while (reader.Read())
{
TRegistro_CartaFrete reg = new TRegistro_CartaFrete();
if (!reader.IsDBNull(reader.GetOrdinal("NR_CartaFrete")))
{
reg.Nr_cartafrete = reader.GetDecimal(reader.GetOrdinal("NR_CartaFrete"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("CD_Empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_lancto")))
{
reg.Nr_lancto = reader.GetDecimal(reader.GetOrdinal("nr_lancto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Motorista")))
{
reg.Cd_motorista = reader.GetString(reader.GetOrdinal("CD_Motorista"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NM_Motorista")))
{
reg.Nm_motorista = reader.GetString(reader.GetOrdinal("NM_Motorista"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Acerto")))
{
reg.Id_acerto = reader.GetDecimal(reader.GetOrdinal("ID_Acerto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_Emissao")))
{
reg.Dt_emissao = reader.GetDateTime(reader.GetOrdinal("DT_Emissao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_Documento")))
{
reg.Vl_documento = reader.GetDecimal(reader.GetOrdinal("Vl_Documento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Observacao")))
{
reg.Ds_observacao = reader.GetString(reader.GetOrdinal("DS_Observacao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ST_Registro")))
{
reg.St_registro = reader.GetString(reader.GetOrdinal("ST_Registro"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
/// <summary>
/// 执行查询，返回结构为xml结构的字符串。
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet"></param>
/// <returns></returns>
public int Query(string strSql, ref string strDataSet)
{
this.command.Connection = this.Conn as System.Data.SqlClient.SqlConnection;
this.command.Transaction = Mder.FC.DB.DBTrans.Trans as System.Data.SqlClient.SqlTransaction;
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
this.tmpReader1 = this.command.ExecuteReader();
XmlDocument doc = new XmlDocument();
XmlNode rootNode;
XmlNode node, tableNode;
rootNode = doc.CreateElement("DataSet");
doc.AppendChild(rootNode);
while (this.tmpReader1.Read())
{
tableNode = doc.CreateElement("Table");
for (int i = 0; i < this.tmpReader1.FieldCount; i++)
{
node = doc.CreateElement(this.tmpReader1.GetName(i).ToString());
node.InnerText = this.tmpReader1[i].ToString() + "";
tableNode.AppendChild(node);
}
rootNode.AppendChild(tableNode);
}//while
strDataSet = doc.OuterXml;
this.tmpReader1.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.ErrorCode;
this.WriteErr();
return -1;
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrCode = strSql;
this.WriteErr();
return -1;
}
WriteDebug("执行查询sql语句！" + strSql);
return 0;
}
public TList_Emprestimos Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
TList_Emprestimos lista = new TList_Emprestimos();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
while (reader.Read())
{
TRegistro_Emprestimos reg = new TRegistro_Emprestimos();
if (!reader.IsDBNull(reader.GetOrdinal("Id_emprestimo")))
{
reg.Id_emprestimo = reader.GetDecimal(reader.GetOrdinal("Id_emprestimo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_clifor")))
{
reg.Cd_clifor = reader.GetString(reader.GetOrdinal("cd_clifor"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_clifor")))
{
reg.Nm_clifor = reader.GetString(reader.GetOrdinal("nm_clifor"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_endereco")))
{
reg.Cd_endereco = reader.GetString(reader.GetOrdinal("cd_endereco"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_endereco")))
{
reg.Ds_endereco = reader.GetString(reader.GetOrdinal("ds_endereco"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_juro")))
{
reg.Cd_juro = reader.GetString(reader.GetOrdinal("cd_juro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_juro")))
{
reg.Ds_juro = reader.GetString(reader.GetOrdinal("ds_juro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("PC_JuroDiario_Atrazo")))
{
reg.Pc_juro = reader.GetDecimal(reader.GetOrdinal("PC_JuroDiario_Atrazo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("dt_emprestimo")))
{
reg.Dt_emprestimo = reader.GetDateTime(reader.GetOrdinal("dt_emprestimo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("tp_emprestimo")))
{
reg.Tp_emprestimo = reader.GetString(reader.GetOrdinal("tp_emprestimo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_observacao")))
{
reg.Ds_observacao = reader.GetString(reader.GetOrdinal("ds_observacao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_registro")))
{
reg.St_registro = reader.GetString(reader.GetOrdinal("st_registro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_contager")))
{
reg.Cd_contager = reader.GetString(reader.GetOrdinal("cd_contager"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_contager")))
{
reg.Ds_contager = reader.GetString(reader.GetOrdinal("ds_contager"));
}
if (!reader.IsDBNull(reader.GetOrdinal("vl_emprestimo")))
{
reg.Vl_emprestimo = reader.GetDecimal(reader.GetOrdinal("vl_emprestimo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("vl_atual")))
{
reg.Vl_atual = reader.GetDecimal(reader.GetOrdinal("vl_atual"));
}
if (!reader.IsDBNull(reader.GetOrdinal("vl_quitado")))
{
reg.Vl_quitado = reader.GetDecimal(reader.GetOrdinal("vl_quitado"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
/// <summary>
/// Возвращает настройки для импорта данных в приложение к плану
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <returns>список настроек</returns>
public static CSettingForImportData GetSettingForImportDataInPlanByDepartCustomerSubtype(UniXP.Common.CProfile objProfile, System.Data.SqlClient.SqlCommand cmdSQL)
{
CSettingForImportData objRet = null;
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
DevExpress.XtraEditors.XtraMessageBox.Show(
"Не удалось получить соединение с базой данных.", "Внимание",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return(objRet);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetImportDataInPlanByDepartCustomerSubtypeSettings]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_NUM", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_MES", System.Data.SqlDbType.NVarChar, 4000));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
if (rs.HasRows)
{
rs.Read();
{
objRet = new CSettingForImportData();
objRet.ID = (System.Guid)rs["Settings_Guid"];
objRet.Name = System.Convert.ToString(rs["Settings_Name"]);
objRet.SettingsList = new List <CSettingItemForImportData>();
objRet.XMLSettings = new System.Xml.XmlDocument();
objRet.XMLSettings.LoadXml(rs.GetSqlXml(2).Value);
foreach (System.Xml.XmlNode objNode in objRet.XMLSettings.ChildNodes)
{
foreach (System.Xml.XmlNode objChildNode in objNode.ChildNodes)
{
objRet.SettingsList.Add(new CSettingItemForImportData()
{
TOOLS_ID = System.Convert.ToInt32(objChildNode.Attributes["TOOLS_ID"].Value),
TOOLS_NAME = System.Convert.ToString(objChildNode.Attributes["TOOLS_NAME"].Value),
TOOLS_USERNAME = System.Convert.ToString(objChildNode.Attributes["TOOLS_USERNAME"].Value),
TOOLS_DESCRIPTION = System.Convert.ToString(objChildNode.Attributes["TOOLS_DESCRIPTION"].Value),
TOOLS_VALUE = System.Convert.ToInt32(objChildNode.Attributes["TOOLS_VALUE"].Value),
TOOLSTYPE_ID = System.Convert.ToInt32(objChildNode.Attributes["TOOLSTYPE_ID"].Value)
});
}
}
}
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
DevExpress.XtraEditors.XtraMessageBox.Show(
"Не удалось получить список настроек.\n\nТекст ошибки: " + f.Message, "Внимание",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
return(objRet);
}
public static string RecoveryRoleWithRollBack(GSSModel.Request.RoleData role)
{
string code = (new DBHandle()).RecoveryRole(role);
if (code != true.ToString())
{
return(code);
}
//读取MySQL匹配的连接串
string linkName = string.Format("LKSV_GSS_6_ZoneRoleDataDB_{0}_{1}", role.BigZoneId, role.ZoneId);
string query = string.Format("select provider_string from sys.servers where name='{0}'", linkName);
string connString = string.Empty;
System.Data.SqlClient.SqlDataReader d = DbHelperSQL.ExecuteReader(query);
if (d.Read())
{
connString = d["provider_string"] as string;
}
d.Close();
if (string.IsNullOrEmpty(connString))
{// 远程数据库连接实例名出现异常
return("4042");
}
//需要进行特殊项过滤： DRIVER,OPTION,STMT
string filter = FilterDBConnString(connString);
DbHelperMySQLP db = new DbHelperMySQLP(filter);
MySqlParameter[] param = new MySqlParameter[] {
new MySqlParameter("ROLEID", MySqlDbType.Int32)
{
Value = role.RoleID
},
new MySqlParameter("USERID", MySqlDbType.Int32)
{
Value = role.UserID
},
new MySqlParameter("OverRoleID", MySqlDbType.Int32)
{
Value = role.RoleID
},
new MySqlParameter("OverUserID", MySqlDbType.Int32)
{
Value = role.UserID
},
new MySqlParameter("CODE", MySqlDbType.Int32)
};
param[param.Length - 1].Direction = ParameterDirection.Output;
string msg = db.RunNoQueryProcedure("TakeRoleData", param);
object outd = param[param.Length - 1].Value;
if (outd == null)
{
return(msg);
}
int c = (int)outd;
if (c > 0)
{
return(true.ToString());
}
return(msg);
}
public System.Data.SqlClient.SqlDataReader GetReader(string strsql)
{
OpenDB();
sqlexecute.CommandText = strsql;
sqlexecute.Connection = dataconnection;
sqlreader = sqlexecute.ExecuteReader();
return sqlreader;
}
/// <summary>
/// 查询获取的xml文件包含有xml处理指令:type="text/xsl" href=xlsFileName
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet"></param>
/// <param name="strXSLFileName">做转换用的的xsl文档</param>
/// <returns></returns>
public int Query(string strSql, ref string strDataSet, string strXSLFileName)
{
this.command.Connection = this.Conn as System.Data.SqlClient.SqlConnection;
this.command.Transaction = Mder.FC.DB.DBTrans.Trans as System.Data.SqlClient.SqlTransaction;
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
this.tmpReader1 = this.command.ExecuteReader();
XmlDocument doc = new XmlDocument();
XmlNode rootNode;
XmlElement node, rowNode;
doc.AppendChild(doc.CreateXmlDeclaration("1.0", "GB2312", ""));
if (strXSLFileName != null && strXSLFileName != "")
{
string PI = "type='text/xsl' href='" + strXSLFileName + "'";
System.Xml.XmlProcessingInstruction xmlProcessingInstruction = doc.CreateProcessingInstruction("xml-stylesheet", PI);
doc.AppendChild(xmlProcessingInstruction);
}
rootNode = doc.CreateElement("Table");
doc.AppendChild(rootNode);
while (this.tmpReader1.Read())
{
rowNode = doc.CreateElement("Row");
for (int i = 0; i < this.tmpReader1.FieldCount; i++)
{
node = doc.CreateElement("Column");
node.SetAttribute("Name", this.tmpReader1.GetName(i).ToString());
node.InnerText = this.tmpReader1[i].ToString() + "";
rowNode.AppendChild(node);
}
rootNode.AppendChild(rowNode);
}
strDataSet = doc.OuterXml;
this.tmpReader1.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.ErrorCode;
this.WriteErr();
return -1;
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrCode = strSql;
this.WriteErr();
return -1;
}
WriteDebug("执行查询sql语句！" + strSql);
return 0;
}
public TList_Emprestimo_X_Caixa Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
TList_Emprestimo_X_Caixa lista = new TList_Emprestimo_X_Caixa();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
while (reader.Read())
{
TRegistro_Emprestimo_X_Caixa reg = new TRegistro_Emprestimo_X_Caixa();
if (!reader.IsDBNull(reader.GetOrdinal("Id_emprestimo")))
{
reg.Id_emprestimo = reader.GetDecimal(reader.GetOrdinal("Id_emprestimo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_contager")))
{
reg.Cd_contager = reader.GetString(reader.GetOrdinal("cd_contager"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_lanctocaixa")))
{
reg.Cd_lanctocaixa = reader.GetDecimal(reader.GetOrdinal("cd_lanctocaixa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("tp_lancto")))
{
reg.Tp_lancto = reader.GetString(reader.GetOrdinal("tp_lancto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_portador")))
{
reg.Cd_portador = reader.GetString(reader.GetOrdinal("cd_portador"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_portador")))
{
reg.Ds_portador = reader.GetString(reader.GetOrdinal("ds_portador"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public List <Tipo54> Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
List <Tipo54> retorno = new List <Tipo54>();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
this.CriarBanco_Dados(false);
podeFecharBco = true;
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
Tipo54 reg = new Tipo54();
if (!reader.IsDBNull(reader.GetOrdinal("cnpj")))
{
reg.Cnpj = reader.GetString(reader.GetOrdinal("cnpj"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_modelo")))
{
reg.Cd_modelo = reader.GetString(reader.GetOrdinal("cd_modelo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_serie")))
{
reg.Nr_serie = reader.GetString(reader.GetOrdinal("nr_serie"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_notafiscal")))
{
reg.Nr_notafiscal = reader.GetDecimal(reader.GetOrdinal("nr_notafiscal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_lanctofiscal")))
{
reg.Nr_lanctofiscal = reader.GetDecimal(reader.GetOrdinal("nr_lanctofiscal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_cfop")))
{
reg.Cd_cfop = reader.GetString(reader.GetOrdinal("cd_cfop"));
}
if (!reader.IsDBNull(reader.GetOrdinal("id_nfitem")))
{
reg.Id_nfitem = reader.GetDecimal(reader.GetOrdinal("id_nfitem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("cd_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("quantidade")))
{
reg.Quantidade = reader.GetDecimal(reader.GetOrdinal("quantidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("vl_subtotal")))
{
reg.Vl_subtotal = reader.GetDecimal(reader.GetOrdinal("vl_subtotal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_st")))
{
reg.Cd_st = reader.GetString(reader.GetOrdinal("cd_st"));
}
if (!reader.IsDBNull(reader.GetOrdinal("vl_basecalc_icms")))
{
reg.Vl_basecalc_icms = reader.GetDecimal(reader.GetOrdinal("vl_basecalc_icms"));
}
if (!reader.IsDBNull(reader.GetOrdinal("vl_basecalcsubsttrib")))
{
reg.Vl_basecalc_icmssubsttrib = reader.GetDecimal(reader.GetOrdinal("vl_basecalcsubsttrib"));
}
if (!reader.IsDBNull(reader.GetOrdinal("pc_aliquota_icms")))
{
reg.Pc_aliquotaicms = reader.GetDecimal(reader.GetOrdinal("pc_aliquota_icms"));
}
retorno.Add(reg);
}
return(retorno);
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
}
public TList_EmprestimoConcedido Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
bool podeFecharBco = false;
TList_EmprestimoConcedido lista = new TList_EmprestimoConcedido();
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, vTop, vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_EmprestimoConcedido reg = new TRegistro_EmprestimoConcedido();
if (!(reader.IsDBNull(reader.GetOrdinal("ID_Emprestimo"))))
{
reg.Id_emprestimo = reader.GetDecimal(reader.GetOrdinal("ID_Emprestimo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("Nr_Lancto"))))
{
reg.Nr_lancto = reader.GetDecimal(reader.GetOrdinal("Nr_Lancto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Retirada")))
{
reg.Id_retirada = reader.GetDecimal(reader.GetOrdinal("ID_Retirada"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Caixa")))
{
reg.Id_caixa = reader.GetDecimal(reader.GetOrdinal("ID_Caixa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Placa")))
{
reg.Placa = reader.GetString(reader.GetOrdinal("Placa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NM_Motorista")))
{
reg.Nm_motorista = reader.GetString(reader.GetOrdinal("NM_Motorista"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_Retirada")))
{
reg.Vl_emprestimo = reader.GetDecimal(reader.GetOrdinal("Vl_Retirada"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_registro")))
{
reg.St_registro = reader.GetString(reader.GetOrdinal("st_registro"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public TList_LoteAnvisa Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_LoteAnvisa lista = new TList_LoteAnvisa();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNm_Campo));
while (reader.Read())
{
TRegistro_LoteAnvisa reg = new TRegistro_LoteAnvisa();
if (!reader.IsDBNull(reader.GetOrdinal("CD_Empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("CD_Empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NM_Empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("NM_Empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Lote")))
{
reg.Id_lote = reader.GetDecimal(reader.GetOrdinal("ID_Lote"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Fornecedor")))
{
reg.Cd_fornecedor = reader.GetString(reader.GetOrdinal("CD_Fornecedor"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NM_Clifor")))
{
reg.Nm_fornecedor = reader.GetString(reader.GetOrdinal("NM_Clifor"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("CD_Produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Produto")))
{
reg.Ds_produto = reader.GetString(reader.GetOrdinal("DS_Produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Sigla_Unidade")))
{
reg.Sg_unidade = reader.GetString(reader.GetOrdinal("Sigla_Unidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NR_Lote")))
{
reg.Nr_lote = reader.GetString(reader.GetOrdinal("NR_Lote"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_Fabric")))
{
reg.Dt_fabric = reader.GetDateTime(reader.GetOrdinal("DT_Fabric"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_Validade")))
{
reg.Dt_validade = reader.GetDateTime(reader.GetOrdinal("DT_Validade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Qtd_entrada")))
{
reg.Qtd_loteEnt = reader.GetDecimal(reader.GetOrdinal("Qtd_entrada"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Qtd_saida")))
{
reg.Qtd_loteSai = reader.GetDecimal(reader.GetOrdinal("Qtd_saida"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public List <Tipo60M> Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
List <Tipo60M> retorno = new List <Tipo60M>();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
Tipo60M reg = new Tipo60M();
if (!reader.IsDBNull(reader.GetOrdinal("ID_Mapa")))
{
reg.Id_mapa = reader.GetDecimal(reader.GetOrdinal("ID_Mapa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_Mapa")))
{
reg.Dt_emissao = reader.GetDateTime(reader.GetOrdinal("DT_Mapa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Nr_serie_equipamento")))
{
reg.Nr_serie_equipamento = reader.GetString(reader.GetOrdinal("Nr_serie_equipamento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Equipamento")))
{
reg.Id_equipamento = reader.GetDecimal(reader.GetOrdinal("ID_Equipamento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_modelo")))
{
reg.Cd_modelo = reader.GetString(reader.GetOrdinal("cd_modelo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NR_COO_Inicial")))
{
reg.Nr_coo_inicial = reader.GetDecimal(reader.GetOrdinal("NR_COO_Inicial"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NR_COO_Final")))
{
reg.Nr_coo_final = reader.GetDecimal(reader.GetOrdinal("NR_COO_Final"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Contador_ReducaoZ")))
{
reg.Contador_reducaoZ = reader.GetDecimal(reader.GetOrdinal("Contador_ReducaoZ"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Contador_Reinicio_Operacao")))
{
reg.Contador_reinicio_operacao = reader.GetDecimal(reader.GetOrdinal("Contador_Reinicio_Operacao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_VendaBruta")))
{
reg.Vl_vendabruta = reader.GetDecimal(reader.GetOrdinal("Vl_VendaBruta"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_TotalGeral")))
{
reg.Vl_totalgeral = reader.GetDecimal(reader.GetOrdinal("Vl_TotalGeral"));
}
retorno.Add(reg);
}
return(retorno);
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
}
public TList_MovLoteAnvisa Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_MovLoteAnvisa lista = new TList_MovLoteAnvisa();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNm_Campo));
while (reader.Read())
{
TRegistro_MovLoteAnvisa reg = new TRegistro_MovLoteAnvisa();
if (!reader.IsDBNull(reader.GetOrdinal("CD_Empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("CD_Empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Lote")))
{
reg.Id_lote = reader.GetDecimal(reader.GetOrdinal("ID_Lote"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_lote")))
{
reg.Nr_lote = reader.GetString(reader.GetOrdinal("nr_lote"));
}
if (!reader.IsDBNull(reader.GetOrdinal("dt_fabric")))
{
reg.Dt_fabric = reader.GetDateTime(reader.GetOrdinal("dt_fabric"));
}
if (!reader.IsDBNull(reader.GetOrdinal("dt_validade")))
{
reg.Dt_validade = reader.GetDateTime(reader.GetOrdinal("dt_validade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Mov")))
{
reg.Id_mov = reader.GetDecimal(reader.GetOrdinal("ID_Mov"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NR_LanctoFiscal")))
{
reg.Nr_lanctofiscal = reader.GetDecimal(reader.GetOrdinal("NR_LanctoFiscal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_NFItem")))
{
reg.Id_nfitem = reader.GetDecimal(reader.GetOrdinal("ID_NFItem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Cupom")))
{
reg.Id_cupom = reader.GetDecimal(reader.GetOrdinal("ID_Cupom"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Lancto")))
{
reg.Id_lancto = reader.GetDecimal(reader.GetOrdinal("ID_Lancto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("CD_Produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_LanctoEstoque")))
{
reg.Id_lanctoestoque = reader.GetDecimal(reader.GetOrdinal("ID_LanctoEstoque"));
}
if (!reader.IsDBNull(reader.GetOrdinal("TP_Mov")))
{
reg.Tp_mov = reader.GetString(reader.GetOrdinal("TP_Mov"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Quantidade")))
{
reg.Quantidade = reader.GetDecimal(reader.GetOrdinal("Quantidade"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
/// <summary>
/// Возвращает список объектов "Состояние возвратной накладной" из базы данных в виде таблицы значений
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <param name="strErr">сообщение об ошибке</param>
/// <returns>таблицу</returns>
public static System.Data.DataTable GetBackWaybillStateList(UniXP.Common.CProfile objProfile,
System.Data.SqlClient.SqlCommand cmdSQL, ref System.String strErr)
{
System.Data.DataTable dtReturn = new System.Data.DataTable();
dtReturn.Columns.Add(new System.Data.DataColumn("BackWaybillState_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
dtReturn.Columns.Add(new System.Data.DataColumn("BackWaybillState_Name", typeof(System.Data.SqlTypes.SqlString)));
dtReturn.Columns.Add(new System.Data.DataColumn("BackWaybillState_Description", typeof(System.Data.SqlTypes.SqlString)));
dtReturn.Columns.Add(new System.Data.DataColumn("BackWaybillState_IsActive", typeof(System.Data.SqlTypes.SqlBoolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("BackWaybillState_IsDefault", typeof(System.Data.SqlTypes.SqlBoolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("BackWaybillState_Id", typeof(System.Data.SqlTypes.SqlInt32)));
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("\nНе удалось получить соединение с базой данных.");
return(dtReturn);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetBackWaybillState]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_NUM", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_MES", System.Data.SqlDbType.NVarChar, 4000));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
if (rs.HasRows)
{
System.Data.DataRow newRow = null;
while (rs.Read())
{
newRow = dtReturn.NewRow();
newRow["BackWaybillState_Guid"] = (System.Guid)rs["BackWaybillState_Guid"];
newRow["BackWaybillState_Name"] = ((rs["BackWaybillState_Name"] == System.DBNull.Value) ? "" : System.Convert.ToString(rs["BackWaybillState_Name"]));
newRow["BackWaybillState_Description"] = ((rs["BackWaybillState_Description"] == System.DBNull.Value) ? "" : System.Convert.ToString(rs["BackWaybillState_Description"]));
newRow["BackWaybillState_IsActive"] = ((rs["BackWaybillState_IsActive"] == System.DBNull.Value) ? false : System.Convert.ToBoolean(rs["BackWaybillState_IsActive"]));
newRow["BackWaybillState_IsDefault"] = ((rs["BackWaybillState_IsDefault"] == System.DBNull.Value) ? false : System.Convert.ToBoolean(rs["BackWaybillState_IsDefault"]));
newRow["BackWaybillState_Id"] = ((rs["BackWaybillState_Id"] == System.DBNull.Value) ? 0 : System.Convert.ToInt32(rs["BackWaybillState_Id"]));
dtReturn.Rows.Add(newRow);
}
dtReturn.AcceptChanges();
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
strErr += ("\nНе удалось получить список объектов \"состояние возвратной накладной\". Текст ошибки: " + f.Message);
}
return(dtReturn);
}
public TList_CFGTaxa Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
TList_CFGTaxa lista = new TList_CFGTaxa();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
this.CriarBanco_Dados(false);
podeFecharBco = true;
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
while (reader.Read())
{
TRegistro_CFGTaxa reg = new TRegistro_CFGTaxa();
if (!reader.IsDBNull(reader.GetOrdinal("tp_taxa")))
{
reg.Tp_taxa = reader.GetString(reader.GetOrdinal("tp_taxa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cfg_pedido")))
{
reg.Cfg_pedido = reader.GetString(reader.GetOrdinal("cfg_pedido"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_tipopedido")))
{
reg.Ds_tipopedido = reader.GetString(reader.GetOrdinal("ds_tipopedido"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("cd_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_produto")))
{
reg.Ds_produto = reader.GetString(reader.GetOrdinal("ds_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Unidade")))
{
reg.Cd_unidproduto = reader.GetString(reader.GetOrdinal("CD_Unidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Moeda")))
{
reg.Cd_moeda = reader.GetString(reader.GetOrdinal("CD_Moeda"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Moeda_Singular")))
{
reg.Ds_moeda = reader.GetString(reader.GetOrdinal("DS_Moeda_Singular"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Sigla")))
{
reg.Sigla = reader.GetString(reader.GetOrdinal("Sigla"));
}
if (!reader.IsDBNull(reader.GetOrdinal("tp_fiscal")))
{
reg.Tp_fiscal = reader.GetString(reader.GetOrdinal("tp_fiscal"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public TList_EncerranteBico Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
bool podeFecharBco = false;
TList_EncerranteBico lista = new TList_EncerranteBico();
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, vTop, vNM_Campo, string.Empty));
try
{
while (reader.Read())
{
TRegistro_EncerranteBico reg = new TRegistro_EncerranteBico();
if (!(reader.IsDBNull(reader.GetOrdinal("id_encerrante"))))
{
reg.Id_encerrante = reader.GetDecimal(reader.GetOrdinal("id_encerrante"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("id_bico"))))
{
reg.Id_bico = reader.GetDecimal(reader.GetOrdinal("id_bico"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_label")))
{
reg.Labelbico = reader.GetString(reader.GetOrdinal("ds_label"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("cd_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_produto")))
{
reg.Ds_produto = reader.GetString(reader.GetOrdinal("ds_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("dt_encerrante")))
{
reg.Dt_encerrante = reader.GetDateTime(reader.GetOrdinal("dt_encerrante"));
}
if (!reader.IsDBNull(reader.GetOrdinal("qtd_encerrante")))
{
reg.Qtd_encerrante = reader.GetDecimal(reader.GetOrdinal("qtd_encerrante"));
}
if (!reader.IsDBNull(reader.GetOrdinal("tp_encerrante")))
{
reg.Tp_encerrante = reader.GetString(reader.GetOrdinal("tp_encerrante"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (textBoxFPH.Text == "")
{
textBoxFPH.Text = strFPH;
return;
}
sqlConn.Open();
sqlComm.CommandText = "SELECT 发票汇总表.ID, 发票汇总表.发票号, 单位表.单位编号, 单位表.单位名称, 发票汇总表.备注, 发票汇总表.发货方式, 发票汇总表.单号, 发票汇总表.发票类型, 发票汇总表.日期, 发票汇总表.原开票金额, 发票汇总表.发票总额 FROM 发票汇总表 INNER JOIN 单位表 ON 发票汇总表.单位ID = 单位表.ID WHERE (发票汇总表.BeActive = 1) AND (发票汇总表.发票号 = N'" + textBoxFPH.Text + "')";
if (dSet.Tables.Contains("发票表"))
{
dSet.Tables.Remove("发票表");
}
sqlDA.Fill(dSet, "发票表");
if (dSet.Tables["发票表"].Rows.Count < 1) //没有发票
{
textBoxFPH.Text = strFPH;
sqlConn.Close();
return;
}
if (dSet.Tables["发票表"].Rows.Count == 1) //只有一个发票
{
iFPH = Int32.Parse(dSet.Tables["发票表"].Rows[0][0].ToString());
}
else //多个发票
{
sqlComm.CommandText = "SELECT 发票汇总表.ID, 发票汇总表.单位ID, 发票汇总表.发票号, 单位表.单位编号, 单位表.单位名称, 发票汇总表.日期, 发票汇总表.发票总额, 发票汇总表.备注, 发票汇总表.操作员ID, 职员表.职员姓名, 发票汇总表.操作员ID AS 操作员 FROM 发票汇总表 INNER JOIN 单位表 ON 发票汇总表.单位ID = 单位表.ID INNER JOIN 职员表 ON 发票汇总表.操作员ID = 职员表.ID WHERE (发票汇总表.BeActive = 1) AND (发票汇总表.发票号 = N'" + textBoxFPH.Text + "')";
FormSelectBill frmSelectBill = new FormSelectBill();
frmSelectBill.strConn = strConn;
frmSelectBill.strSelectText = sqlComm.CommandText;
frmSelectBill.bShowDW = true;
frmSelectBill.ShowDialog();
if (frmSelectBill.iBillNumber == 0)
{
sqlConn.Close();
return;
}
else
{
iFPH = frmSelectBill.iBillNumber;
}
}
sqlComm.CommandText = "SELECT 发票汇总表.ID, 发票汇总表.发票号, 单位表.单位编号, 单位表.单位名称, 发票汇总表.备注, 发票汇总表.发货方式, 发票汇总表.单号, 发票汇总表.发票类型, 发票汇总表.日期, 发票汇总表.原开票金额, 发票汇总表.发票总额 FROM 发票汇总表 INNER JOIN 单位表 ON 发票汇总表.单位ID = 单位表.ID WHERE (发票汇总表.BeActive = 1) AND (发票汇总表.ID = " + iFPH.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (!sqldr.HasRows)
{
textBoxFPH.Text = strFPH;
sqldr.Close();
sqlConn.Close();
return;
}
while (sqldr.Read())
{
iFPH = Convert.ToInt32(sqldr.GetValue(0).ToString());
strFPH = sqldr.GetValue(1).ToString();
textBoxDWBH.Text = sqldr.GetValue(2).ToString();
textBoxDWMC.Text = sqldr.GetValue(3).ToString();
textBoxBZ.Text = sqldr.GetValue(4).ToString();
comboBoxFHFS.Text = sqldr.GetValue(5).ToString();
textBoxDH.Text = sqldr.GetValue(6).ToString();
comboBoxStyle.SelectedIndex = Convert.ToInt32(sqldr.GetValue(7).ToString());
labelZDRQ.Text = Convert.ToDateTime(sqldr.GetValue(8).ToString()).ToString("yyyy年M月dd日");
labelJEHJ.Text = sqldr.GetValue(9).ToString();
labelSJJE.Text = sqldr.GetValue(10).ToString();
labelDX.Text = cGetInformation.changeDAXIE(labelSJJE.Text);
}
sqldr.Close();
switch (comboBoxStyle.SelectedIndex)
{
case 0:
//初始化明细列表
sqlComm.CommandText = "SELECT 单据ID, 冲抵ID, 单据编号, 冲抵编号, 原开票总额, 发票总额, 发货方式, 单号, 备注1, 备注2, ID FROM 发票明细表 WHERE (发票明细表.发票ID = " + iFPH.ToString() + ")";
if (dSet.Tables.Contains("单据明细表"))
{
dSet.Tables.Remove("单据明细表");
}
sqlDA.Fill(dSet, "单据明细表");
dataGridViewDJMX.DataSource = dSet.Tables["单据明细表"];
dataGridViewDJMX.Columns[0].Visible = false;
dataGridViewDJMX.Columns[1].Visible = false;
dataGridViewDJMX.Columns[10].Visible = false;
dataGridViewDJMX.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[6].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[8].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
break;
case 1:
sqlComm.CommandText = "SELECT 单据ID, 冲抵ID, 单据编号, 冲抵编号, 原开票总额, 发票总额, 发货方式, 单号, 备注1, 备注2, ID FROM 发票明细表 WHERE (发票明细表.发票ID = " + iFPH.ToString() + ")";
if (dSet.Tables.Contains("单据明细表"))
{
dSet.Tables.Remove("单据明细表");
}
sqlDA.Fill(dSet, "单据明细表");
dataGridViewDJMX.DataSource = dSet.Tables["单据明细表"];
dataGridViewDJMX.Columns[0].Visible = false;
dataGridViewDJMX.Columns[1].Visible = false;
dataGridViewDJMX.Columns[10].Visible = false;
dataGridViewDJMX.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[6].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridViewDJMX.Columns[8].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
break;
}
sqlConn.Close();
}
/// <summary>
/// Возвращает список настроек
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <returns>список настроек</returns>
public static List <CSetting> GetSettingsList(UniXP.Common.CProfile objProfile, System.Data.SqlClient.SqlCommand cmdSQL)
{
List <CSetting> objList = new List <CSetting>();
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
DevExpress.XtraEditors.XtraMessageBox.Show(
"Не удалось получить соединение с базой данных.", "Внимание",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return(objList);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
cmd.CommandText = System.String.Format("[{0}].[dbo].[sp_GetSettings]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_NUM", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ERROR_MES", System.Data.SqlDbType.NVarChar, 4000));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
if (rs.HasRows)
{
CSetting objSetting = null;
System.Xml.XmlDocument docInfoAboutParam = null;
CAdvancedParam objParam = null;
System.Xml.XmlNode objRootNode = null;
while (rs.Read())
{
objSetting = new CSetting();
objSetting.ID = (System.Guid)rs["Settings_Guid"];
objSetting.Name = System.Convert.ToString(rs["Settings_Name"]);
objSetting.ParamList = new List <CAdvancedParam>();
if (rs["Settings_XML"] != System.DBNull.Value)
{
docInfoAboutParam = new System.Xml.XmlDocument();
docInfoAboutParam.LoadXml(System.Convert.ToString(rs["Settings_XML"]));
if ((docInfoAboutParam != null) && (docInfoAboutParam.ChildNodes.Count > 0))
{
objSetting.xmldocAdvancedParamList = docInfoAboutParam;
objRootNode = docInfoAboutParam.ChildNodes[0];
//objSetting.Name = objRootNode.Name;
foreach (System.Xml.XmlNode objNode in objRootNode.ChildNodes)
{
//if (objNode.Name == "CommonParams")
//{
foreach (System.Xml.XmlNode objChildNode in objNode.ChildNodes)
{
//if (objChildNode.Name == "Params")
//{
foreach (System.Xml.XmlAttribute objAttribute in objChildNode.Attributes)
{
objParam = new CAdvancedParam(System.Guid.Empty, objAttribute.Name, "", new CParamDataType(0, ""));
objParam.GroupName = objNode.Name;
objParam.Value = objAttribute.Value;
objSetting.ParamList.Add(objParam);
}
//}
//}
}
}
}
}
objList.Add(objSetting);
}
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
DevExpress.XtraEditors.XtraMessageBox.Show(
"Не удалось получить список настроек.\n\nТекст ошибки: " + f.Message, "Внимание",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
return(objList);
}
public TList_CFGFaturaCartao Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
TList_CFGFaturaCartao lista = new TList_CFGFaturaCartao();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_CFGFaturaCartao reg = new TRegistro_CFGFaturaCartao();
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Historico_rec")))
{
reg.Cd_historico_rec = reader.GetString(reader.GetOrdinal("CD_Historico_rec"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Historico_rec")))
{
reg.Ds_historico_rec = reader.GetString(reader.GetOrdinal("DS_Historico_rec"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_historico_pag")))
{
reg.Cd_historico_pag = reader.GetString(reader.GetOrdinal("cd_historico_pag"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_historico_pag")))
{
reg.Ds_historico_pag = reader.GetString(reader.GetOrdinal("ds_historico_pag"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Historico_Juro")))
{
reg.Cd_historico_juro = reader.GetString(reader.GetOrdinal("CD_Historico_Juro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Historico_Juro")))
{
reg.Ds_historico_juro = reader.GetString(reader.GetOrdinal("DS_Historico_Juro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_historico_taxa")))
{
reg.Cd_historico_taxa = reader.GetString(reader.GetOrdinal("cd_historico_taxa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_historico_taxa")))
{
reg.Ds_historico_taxa = reader.GetString(reader.GetOrdinal("ds_historico_taxa"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
private void InitTreeView()
{
string st1, st2, st3;
string stt1, stt2, stt3;
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book";
sqlConn.Open();
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
st1 = sqldr.GetValue(0).ToString();
stt1 = sqldr.GetValue(1).ToString();
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE 图书分类号<>'建' and 图书分类号<>'结' and 图书分类号<>'暖' and 图书分类号<>'卫' and 图书分类号<>'电' and 图书分类号<>'施' and 图书分类号<>'样' and 图书分类号<>'材' and 图书分类号<>'总' and 图书分类号<>'构1' and 图书分类号<>'构2' and 图书分类号<>'构3' and 图书分类号<>'构4' and 图书分类号<>'构5' and 图书分类号<>'结1' and 图书分类号<>'外' and 图书分类号<>'内' and 图书分类号<>'饰' and 图书分类号<>'构' ";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
st2 = sqldr.GetValue(0).ToString();
stt2 = sqldr.GetValue(1).ToString();
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE 图书分类号='建' or 图书分类号='结' or 图书分类号='暖' or 图书分类号='卫' or 图书分类号='电' or 图书分类号='施' or 图书分类号='样' or 图书分类号='材' or 图书分类号='总' or 图书分类号='构1' or 图书分类号='构2' or 图书分类号='构3' or 图书分类号='构4' or 图书分类号='构5' or 图书分类号='结1' or 图书分类号='外' or 图书分类号='内' or 图书分类号='饰' or 图书分类号='构'";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
st3 = sqldr.GetValue(0).ToString();
stt3 = sqldr.GetValue(1).ToString();
sqldr.Close();
booktv.BeginUpdate();
booktv.Nodes.Clear();
TreeNode rootNode = new TreeNode(System.DateTime.Now.Year.ToString() + "年" + System.DateTime.Now.Month.ToString() + "月" + System.DateTime.Now.Day.ToString() + "日，图书馆馆藏信息：");
booktv.Nodes.Add(rootNode);
TreeNode tnBook1 = new TreeNode("共有图书：" + st1 + "种，" + stt1 + "本（其中有图书" + st2 + "种，" + stt2 + "本，资料" + st3 + "种，" + stt3 + "本）");
rootNode.Nodes.Add(tnBook1);
TreeNode tnBook01 = new TreeNode("");
rootNode.Nodes.Add(tnBook01);
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE 文种号='1' and 图书分类号<>'建' and 图书分类号<>'结' and 图书分类号<>'暖' and 图书分类号<>'卫' and 图书分类号<>'电' and 图书分类号<>'施' and 图书分类号<>'样' and 图书分类号<>'材' and 图书分类号<>'总' and 图书分类号<>'构1' and 图书分类号<>'构2' and 图书分类号<>'构3' and 图书分类号<>'构4' and 图书分类号<>'构5' and 图书分类号<>'结1' and 图书分类号<>'外' and 图书分类号<>'内' and 图书分类号<>'饰' and 图书分类号<>'构' ";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook2 = new TreeNode("中文图书：" + sqldr.GetValue(0).ToString() + "种，" + sqldr.GetValue(1).ToString() + "本");
rootNode.Nodes.Add(tnBook2);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE (文种号 = N'2') AND (图书分类号 NOT LIKE N'建%') AND (图书分类号 NOT LIKE N'结%') AND (图书分类号 NOT LIKE N'暖%') AND (图书分类号 NOT LIKE N'卫%') AND (图书分类号 NOT LIKE N'电%') AND (图书分类号 NOT LIKE N'施%') AND (图书分类号 NOT LIKE N'材%') AND (图书分类号 NOT LIKE N'总%') AND (图书分类号 NOT LIKE N'内%') AND (图书分类号 NOT LIKE N'构%') AND (图书分类号 NOT LIKE N'外%') AND (图书分类号 NOT LIKE N'样%') AND (图书分类号 NOT LIKE N'饰%')";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook3 = new TreeNode("西文图书：" + sqldr.GetValue(0).ToString() + "种，" + sqldr.GetValue(1).ToString() + "本");
rootNode.Nodes.Add(tnBook3);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE 文种号='4' and 图书分类号<>'建' and 图书分类号<>'结' and 图书分类号<>'暖' and 图书分类号<>'卫' and 图书分类号<>'电' and 图书分类号<>'施' and 图书分类号<>'样' and 图书分类号<>'材' and 图书分类号<>'总' and 图书分类号<>'构1' and 图书分类号<>'构2' and 图书分类号<>'构3' and 图书分类号<>'构4' and 图书分类号<>'构5' and 图书分类号<>'结1' and 图书分类号<>'外' and 图书分类号<>'内' and 图书分类号<>'饰' and 图书分类号<>'构' ";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook4 = new TreeNode("日文图书：" + sqldr.GetValue(0).ToString() + "种，" + sqldr.GetValue(1).ToString() + "本");
rootNode.Nodes.Add(tnBook4);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE 图书分类号='建' or 图书分类号='结' or 图书分类号='暖' or 图书分类号='卫' or 图书分类号='电' or 图书分类号='施' or 图书分类号='样' or 图书分类号='材' or 图书分类号='总' or 图书分类号='构1' or 图书分类号='构2' or 图书分类号='构3' or 图书分类号='构4' or 图书分类号='构5' or 图书分类号='结1' or 图书分类号='内' or 图书分类号='饰' or 图书分类号='构' and 图书分类号<>'外'";;
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook02 = new TreeNode("");
rootNode.Nodes.Add(tnBook02);
TreeNode tnBook5 = new TreeNode("中文资料：" + sqldr.GetValue(0).ToString() + "种，" + sqldr.GetValue(1).ToString() + "本");
rootNode.Nodes.Add(tnBook5);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE (文种号 = N'2') AND (图书分类号 LIKE N'外%')";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook6 = new TreeNode("西文资料：" + sqldr.GetValue(0).ToString() + "种，" + sqldr.GetValue(1).ToString() + "本");
rootNode.Nodes.Add(tnBook6);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*),sum(馆藏量) FROM book WHERE (文种号 = N'4') AND (图书分类号 LIKE N'外%')";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook7 = new TreeNode("日文资料：" + sqldr.GetValue(0).ToString() + "种，" + sqldr.GetValue(1).ToString() + "本");
rootNode.Nodes.Add(tnBook7);
sqldr.Close();
sqlConn.Close();
booktv.EndUpdate();
rootNode.Expand();
makebufferprint();
}
/// <summary>
/// 获取blob类型的字段值
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public byte[] GetLargeField(string strSql)
{
this.command.Connection = this.Conn as System.Data.SqlClient.SqlConnection;
this.command.Transaction = Mder.FC.DB.DBTrans.Trans as System.Data.SqlClient.SqlTransaction;
command.CommandText = strSql + "";
command.CommandType = System.Data.CommandType.Text;
System.Data.SqlClient.SqlDataReader reader;
command.Parameters.Clear();
try
{
reader = command.ExecuteReader();
}
catch (System.Data.SqlClient.SqlException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.ErrorCode;
this.WriteErr();
return null;
}
catch (Exception ex)
{
this.Err = ex.Message;
this.WriteErr();
return null;
}
reader.Read();
byte[] byteData = new byte[0];
try
{
byteData = (byte[])(reader[0]);
}
catch (Exception ex)
{
this.Err = ex.Message;
reader.Close();
return null;
}
reader.Close();
return byteData;
}
