public void searchSongs()
{
System.Windows.Data.CollectionViewSource pianoSongViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("pianoSongViewSource")));
bool displayAll = false;
if (prvtSongFilter == -1 && prvtArtistFilter == -1 && prvtBookFilter == -1)
{
displayAll = true;
}
else if (prvtSongText.Trim() == "" && prvtArtistText.Trim() == "" && prvtBookText.Trim() == "")
{
displayAll = true;
}
if (displayAll)
{
bindingTable = PianoSongDatabase.getAllData();
}
else
{
bindingTable = PianoSongDatabase.searchData(prvtSongFilter, prvtArtistFilter, prvtBookFilter, prvtSongText.Trim(), prvtArtistText.Trim(), prvtBookText.Trim());
}
bindingTable.AcceptChanges();
pianoSongViewSource.Source = bindingTable;
lblCount.Content = "Count: " + bindingTable.Rows.Count.ToString();
}
public void Execute(string query, out Object data)
{
//execute the query and return data from it
//i.e. like a select query
SqlCommand cmd = new SqlCommand(query, _conn);
SqlDataReader rdr = cmd.ExecuteReader();
//tbd....check that rows aren't empty first
//get the column list
System.Data.DataColumnCollection cols = rdr.GetSchemaTable().Columns;
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < cols.Count; i++)
{
dt.Columns.Add(cols[i]);
}
dt.AcceptChanges();
//read the data
while (rdr.Read())
{
//who knows if this works i'm experimenting for now...
object[] values = null;
rdr.GetValues(values);
dt.Rows.Add(values);
dt.AcceptChanges();
}
rdr.Close();
data = dt;
}
private void bNew_OnClick(object sender, EventArgs e)
{
System.Data.DataRow newRow = dtValues.NewRow();
newRow["X"] = false;
newRow[m_strFileExtensionField] = " ";
newRow[m_strBeginCommentField] = " ";
newRow[m_strEndCommentField] = " ";
dtValues.Rows.Add(newRow);
dtValues.AcceptChanges();
}
//protected string[][] ArmadoParametros()
//{
// string[][] Params = new string[grdParams.Rows.Count][];
// HiddenField hddTipoDato = new HiddenField();
// HiddenField hddValor = new HiddenField();
// HiddenField hddNombre = new HiddenField();
// HiddenField hddLongitud = new HiddenField();
// int Longitud = 0; //Para forzar la salida de un número
// for (int w = 0; w < grdParams.Rows.Count; w++)
// {
// hddTipoDato = (HiddenField)grdParams.Rows[w].FindControl("hddTipoDato");
// hddValor = (HiddenField)grdParams.Rows[w].FindControl("hddValor");
// hddNombre = (HiddenField)grdParams.Rows[w].FindControl("hddNombre");
// hddLongitud = (HiddenField)grdParams.Rows[w].FindControl("hddLongitud");
// int.TryParse(hddLongitud.Value, out Longitud);
// Params[w] = new string[] { hddNombre.Value, hddValor.Value, hddTipoDato.Value.ToLowerInvariant(), Longitud.ToString() };
// }
// return Params;
//}
protected System.Data.DataTable ArmadoParametros()
{
System.Data.DataTable Params = new System.Data.DataTable("Parametros");
System.Data.DataRow dr;
HiddenField hddTipoDato = new HiddenField();
HiddenField hddValor = new HiddenField();
HiddenField hddNombre = new HiddenField();
HiddenField hddLongitud = new HiddenField();
int Longitud = 0; //Para forzar la salida de un número
Params.Columns.Add("Nombre");
Params.Columns.Add("Valor");
Params.Columns.Add("Tipo");
Params.Columns.Add("Longitud");
for (int w = 0; w < grdParams.Rows.Count; w++)
{
hddTipoDato = (HiddenField)grdParams.Rows[w].FindControl("hddTipoDato");
hddValor = (HiddenField)grdParams.Rows[w].FindControl("hddValor");
hddNombre = (HiddenField)grdParams.Rows[w].FindControl("hddNombre");
hddLongitud = (HiddenField)grdParams.Rows[w].FindControl("hddLongitud");
int.TryParse(hddLongitud.Value, out Longitud);
dr = Params.NewRow();
dr["Nombre"] = hddNombre.Value;
dr["Valor"] = hddValor.Value;
dr["Tipo"] = hddTipoDato.Value.ToLowerInvariant();
dr["Longitud"] = Longitud.ToString();
Params.Rows.Add(dr);
}
Params.AcceptChanges();
return(Params);
}
public System.Data.DataTable ToDataTable()
{
var dtable = new System.Data.DataTable();
dtable.TableName = this.TableName;
foreach (var column in this.Columns)
{
dtable.Columns.Add(new System.Data.DataColumn(column.ColumnName, typeof(int).Assembly.GetType(column.DataType)));
}
foreach (var row in this.Rows)
{
var newrow = dtable.NewRow();
foreach (var column in this.Columns)
{
if (row[column.ColumnName] != null)
{
var value = row[column.ColumnName];
if (column.DataType == "System.Byte[]" && value is string)
{
value = Convert.FromBase64String((string)value);
}
newrow[column.ColumnName] = value;
}
}
dtable.Rows.Add(newrow);
}
dtable.AcceptChanges();
return(dtable);
}
void DeleteRow()
{
foreach (DataGridView dgv in dgvList)
{
if (dgv.Visible)
{
DialogResult dr = MessageBox.Show("确定删除？", "提示",
MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (DialogResult.Yes == dr)
{
List <DataGridViewRow> rl = new List <DataGridViewRow>();
foreach (DataGridViewCell cell in dgv.SelectedCells)
{
DataGridViewRow row = cell.OwningRow;
dgv.Rows.Remove(row);
}
string cname = controllerDropList.SelectedValue as string;
string IOType = IOTab.SelectedTab.Text;
IODataCollection.dataTableType dtt = (IODataCollection.dataTableType)Array.IndexOf(
Enum.GetNames(typeof(IODataCollection.dataTableType)), IOType);
if (dgv.Rows.Count <= 0)
{
IODataCollection.FillEmptyTable(cname, dtt);
}
System.Data.DataTable table = dgv.DataSource as System.Data.DataTable;
table.AcceptChanges();
}
}
}
}
/// <summary>
/// 将指定的对象序列化成 JSON 数据。
/// </summary>
/// <param name="obj">要序列化的对象。</param>
/// <returns></returns>
public static string ToJson(this object obj)
{
try
{
if (null == obj)
{
return(null);
}
if (obj is System.Data.DataTable)
{
System.Data.DataTable _sourceTable = (System.Data.DataTable)obj;
foreach (System.Data.DataRow row in _sourceTable.Rows)
{
foreach (System.Data.DataColumn column in _sourceTable.Columns)
{
if (column.DataType == typeof(System.Int32))
{
if (row[column] == DBNull.Value)
{
row[column.ColumnName] = 0;
}
}
if (column.DataType == typeof(System.Decimal))
{
if (row[column] == DBNull.Value)
{
row[column.ColumnName] = 0M;
}
}
}
}
_sourceTable.AcceptChanges();
return(JsonConvert.SerializeObject(_sourceTable, Formatting.None, _jsonSettings));
}
else
{
if (obj is IPagedList)
{
var page = obj as IPagedList;
//处理分页结果的对象
return(JsonConvert.SerializeObject(new
{
Model = obj,
PageIndex = page.PageIndex,
PageSize = page.PageSize,
TotalCount = page.TotalCount,
TotalPages = page.TotalPages
}, Formatting.None, _jsonSettings));
}
return(JsonConvert.SerializeObject(obj, Formatting.None, _jsonSettings));
}
}
catch (Exception)
{
return(null);
}
}
public static void ToCSV(string path, string[,] data)
{
System.Data.DataTable dt = new System.Data.DataTable();
int row = data.GetLength(0);
int column = data.GetLength(1);
for (int j = 0; j < column; j++)
{
dt.Columns.Add(data[0, j], typeof(String));
}
for (int i = 0; i < row; i++) //含表头
{
dt.Rows.Add(dt.NewRow());
for (int j = 0; j < column; j++)
{
if (!String.IsNullOrEmpty(data[i, j]))
{
dt.Rows[i][j] = "\"" + data[i, j].Replace("\"", "\"\"") + "\"";
}
}
}
dt.AcceptChanges();
CsvOptions options = new CsvOptions("String[,]", ',', data.GetLength(1));
CsvEngine.DataTableToCsv(dt, path, options);
}
public System.Data.DataTable ListaServidoresCompletaApp(bool IncluirValorInicial = true)
{
DLSoftware DataLayerSoftware = new DLSoftware();
System.Data.DataTable Resultados = new System.Data.DataTable();
Resultados.Columns.Add("Srv_Id");
Resultados.Columns.Add("Srv_Nombre");
Resultados.Columns.Add("EsPropietaria");
foreach (System.Data.DataRow dr in DataLayerSoftware.getServidoresCompletaApp(IncluirValorInicial).Rows)
{
System.Data.DataRow drN;
drN = Resultados.NewRow();
drN[0] = dr[0].ToString();
drN[1] = dr[1].ToString();
drN[2] = "";
Resultados.Rows.Add(drN);
}
Resultados.AcceptChanges();
return(Resultados);
}
public static void ToCSV(string path, string[,] data)
{
System.Data.DataTable dt = new System.Data.DataTable();
int row = data.GetLength(0);
int column = data.GetLength(1);
for (int j = 0; j < column; j++)
{
dt.Columns.Add(data[0, j], typeof(String));
}
for (int i = 0; i < row; i++) //含表头
{
dt.Rows.Add(dt.NewRow());
for (int j = 0; j < column; j++)
{
if (!String.IsNullOrEmpty(data[i, j]))
{
dt.Rows[i][j] = "\"" + data[i, j].Replace("\"", "\"\"") + "\"";
}
}
}
dt.AcceptChanges();
CsvOptions options = new CsvOptions("String[,]", ',', data.GetLength(1));
CsvEngine.DataTableToCsv(dt, path, options);
}
public static void TipoCatalogoRpt(ref DropDownList ddl)
{
System.Data.DataTable Combo = new System.Data.DataTable();
System.Data.DataRow row;
Combo.Columns.Add("Valor");
Combo.Columns.Add("Descripcion");
Combo.AcceptChanges();
row = Combo.NewRow();
row[0] = "";
row[1] = "";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "txt";
row[1] = "Caja de texto";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "txtm";
row[1] = "Caja de texto multilínea";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "chk";
row[1] = "Casilla de verificación";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "lchk";
row[1] = "Lista de selección múltiple";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "lst";
row[1] = "Lista desplegable";
Combo.Rows.Add(row);
Combo.AcceptChanges();
ddl.DataValueField = "Valor";
ddl.DataTextField = "Descripcion";
ddl.DataSource = Combo;
ddl.DataBind();
}
public static void DeleteColumn(System.Data.DataTable table, string columnName)
{
if (table.Columns.Contains(columnName))
{
table.Columns.Remove(columnName);
}
table.AcceptChanges();
}
private System.Data.DataTable ProcessObjects(bool useHeaderRow, bool ignoreEmptyRows, object[,] valueArray)
{
System.Data.DataTable dt = new System.Data.DataTable();
var beginat = 1;
if (useHeaderRow)
{
for (int k = 1; k <= valueArray.GetLength(1); k++)
{
dt.Columns.Add((string)valueArray[1, k]); //add columns to the data table.
}
beginat = 2;
}
else
{
for (int k = 1; k <= valueArray.GetLength(1); k++)
{
dt.Columns.Add(k.ToString()); //add columns to the data table.
}
beginat = 1;
}
object[] singleDValue = new object[valueArray.GetLength(1)];
//value array first row contains column names. so loop starts from 2 instead of 1
for (int i = beginat; i <= valueArray.GetLength(0); i++)
{
bool hasValue = false;
for (int j = 0; j < valueArray.GetLength(1); j++)
{
if (valueArray[i, j + 1] != null)
{
singleDValue[j] = valueArray[i, j + 1].ToString();
}
else
{
singleDValue[j] = valueArray[i, j + 1];
}
if (ignoreEmptyRows && singleDValue[j] != null)
{
if (!string.IsNullOrEmpty(singleDValue[j].ToString()))
{
hasValue = true;
}
}
}
if (!ignoreEmptyRows)
{
hasValue = true;
}
if (hasValue)
{
dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
}
}
dt.AcceptChanges();
return(dt);
}
/// <summary>
/// Преобразует список записей к табличному виду
/// </summary>
/// <param name="objWaybillItemsList">список строк из приложения к накладной</param>
/// <param name="strErr">сообщение об ошибке</param>
/// <returns>таблица с приложением к накладной</returns>
public static System.Data.DataTable ConvertListToTable(List <CWaybillItem> objWaybillItemsList, ref System.String strErr)
{
System.Data.DataTable objTable = new System.Data.DataTable();
try
{
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
objTable.Columns.Add(new System.Data.DataColumn("SupplItem_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
objTable.Columns.Add(new System.Data.DataColumn("Parts_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
objTable.Columns.Add(new System.Data.DataColumn("Measure_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_Quantity", typeof(int)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_RetQuantity", typeof(int)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_PriceImporter", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_Price", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_Discount", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_DiscountPrice", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_CurrencyPrice", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_CurrencyDiscountPrice", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_NDSPercent", typeof(System.Data.SqlTypes.SqlMoney)));
objTable.Columns.Add(new System.Data.DataColumn("WaybItem_Id", typeof(int)));
System.Data.DataRow newRow = null;
foreach (CWaybillItem objItem in objWaybillItemsList)
{
newRow = objTable.NewRow();
newRow["WaybItem_Guid"] = objItem.ID;
newRow["SupplItem_Guid"] = objItem.SupplItemID;
newRow["Parts_Guid"] = objItem.Product.ID;
newRow["Measure_Guid"] = objItem.Measure.ID;
newRow["WaybItem_Quantity"] = objItem.Quantity;
newRow["WaybItem_RetQuantity"] = objItem.QuantityReturn;
newRow["WaybItem_PriceImporter"] = (System.Data.SqlTypes.SqlMoney)objItem.PriceImporter;
newRow["WaybItem_Price"] = (System.Data.SqlTypes.SqlMoney)objItem.Price;
newRow["WaybItem_Discount"] = (System.Data.SqlTypes.SqlMoney)objItem.DiscountPercent;
newRow["WaybItem_DiscountPrice"] = (System.Data.SqlTypes.SqlMoney)objItem.PriceWithDiscount;
newRow["WaybItem_CurrencyPrice"] = (System.Data.SqlTypes.SqlMoney)objItem.PriceInAccountingCurrency;
newRow["WaybItem_CurrencyDiscountPrice"] = (System.Data.SqlTypes.SqlMoney)objItem.PriceWithDiscountInAccountingCurrency;
newRow["WaybItem_NDSPercent"] = (System.Data.SqlTypes.SqlMoney)objItem.NDSPercent;
newRow["WaybItem_Id"] = objItem.Ib_ID;
objTable.Rows.Add(newRow);
}
if (objWaybillItemsList.Count > 0)
{
objTable.AcceptChanges();
}
}
catch (System.Exception f)
{
objTable = null;
strErr += (String.Format("ConvertListToTable. Текст ошибки: {0}", f.Message));
}
finally // очищаем занимаемые ресурсы
{
}
return(objTable);
}
private System.Data.DataTable removeSummerTerms(System.Data.DataTable dtTerms)
{
for (int i = dtTerms.Rows.Count - 1; i >= 0; i--)
{
if (dtTerms.Rows[i]["term"].ToString().EndsWith("3"))
{
dtTerms.Rows.RemoveAt(i);
}
}
dtTerms.AcceptChanges();
return(dtTerms);
}
public static void TipoEquipo(ref DropDownList ddl)
{
System.Data.DataTable Combo = new System.Data.DataTable();
System.Data.DataRow row;
Combo.Columns.Add("Id");
Combo.Columns.Add("Descripcion");
Combo.AcceptChanges();
row = Combo.NewRow();
row[0] = "";
row[1] = "";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "App";
row[1] = "Aplicación";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "BD";
row[1] = "Base de datos";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "Mixto";
row[1] = "Mixto";
Combo.Rows.Add(row);
row = Combo.NewRow();
row[0] = "FileServer";
row[1] = "FileServer";
Combo.Rows.Add(row);
Combo.AcceptChanges();
ddl.DataValueField = "Id";
ddl.DataTextField = "Descripcion";
ddl.DataSource = Combo;
ddl.DataBind();
}
public void Del()
{
if (this.m_dtMedCommonUseDel == null)
{
m_dtMedCommonUseDel = m_dtMedCommonUse.Clone();
}
int count = this.m_objViewer.m_dgCommonUse.m_arrSelectRows().Length;
for (int i1 = 0; i1 < this.m_objViewer.m_dgCommonUse.RowCount; i1++)
{
if (this.m_objViewer.m_dgCommonUse.m_blnRowIsSelected(i1))
{
m_dtMedCommonUseDel.ImportRow(this.m_objViewer.m_dgCommonUse.m_objGetRow(i1)); m_dtMedCommonUse.Rows[i1].Delete();
m_dtMedCommonUse.AcceptChanges();
count--;
}
if (count <= 0)
{
break;
}
}
}
public static void DeleteRows(System.Data.DataTable table, string expression)
{
System.Data.DataRow[] rows = table.Select(expression);
for (int i = rows.Length - 1; i > -1; i--)
{
if (rows[i].HasVersion(System.Data.DataRowVersion.Current))
{
table.Rows.Remove(rows[i]);
}
}
table.AcceptChanges();
}
public static bool DeleteRowByID(System.Data.DataTable table, string expression)
{
System.Data.DataRow[] rows = table.Select(expression);
if (rows.Length == 1)
{
if (rows[0].HasVersion(System.Data.DataRowVersion.Current))
{
table.Rows.Remove(rows[0]);
table.AcceptChanges();
}
return(true);
}
return(false);
}
public static System.Data.DataTable ToDataTable(this Newtonsoft.Json.Linq.JArray jArray)
{
var result = new System.Data.DataTable();
foreach (var row in jArray)
{
foreach (var jToken in row)
{
var jproperty = jToken as Newtonsoft.Json.Linq.JProperty;
if (jproperty == null)
{
continue;
}
if (result.Columns[jproperty.Name] == null)
{
result.Columns.Add(jproperty.Name, typeof(string));
}
}
}
foreach (var row in jArray)
{
var datarow = result.NewRow();
foreach (var jToken in row)
{
var jProperty = jToken as Newtonsoft.Json.Linq.JProperty;
if (jProperty == null)
{
continue;
}
datarow[jProperty.Name] = jProperty.Value.ToString();
}
result.Rows.Add(datarow);
}
result.AcceptChanges();
return(result);
}
protected void btnBuscar_Click(object sender, EventArgs e)
{
Guid rolId = new Guid(ddlBusquedaRoles.SelectedValue);
System.Data.DataRow row = null;
List<Usuario> result = Usuario.Buscar(txtBusquedaNombre.Text, txtBusquedaPrimerNombre.Text, txtBusquedaPrimerApellido.Text, rolId);
System.Data.DataTable tablaUsuarios = new System.Data.DataTable();
tablaUsuarios.Columns.Add("Id");
tablaUsuarios.Columns.Add("NombreUsuario");
foreach (Usuario actual in result)
{
row = tablaUsuarios.NewRow();
row["Id"] = actual.Id.ToString();
row["NombreUsuario"] = actual.NombreUsuario;
tablaUsuarios.Rows.Add(row);
}
tablaUsuarios.AcceptChanges();
gvUsuarios.DataSource = tablaUsuarios;
gvUsuarios.DataBind();
}
protected override Colosoft.Reports.IReportDocument LoadReport(ref LocalReport report, ref List <ReportParameter> lstParam,
HttpRequest PageRequest, System.Collections.Specialized.NameValueCollection Request, object[] outrosParametros, LoginUsuario login, string diretorioLogotipos)
{
var relatorioDinamicoFluxo = Microsoft.Practices.ServiceLocation.ServiceLocator
.Current.GetInstance <Glass.Global.Negocios.IRelatorioDinamicoFluxo>();
// Recupera dados do relatório dinâmico
var relatorioDinamico = relatorioDinamicoFluxo.ObterRelatorioDinamico(int.Parse(PageRequest["id"]));
// Define o caminho do relatório
report.ReportPath = string.Format("Upload/RelatorioDinamico/{0}.rdlc", relatorioDinamico.IdRelatorioDinamico);
// Monta os filtros da consulta
var filtros = new List <Tuple <Glass.Global.Negocios.Entidades.RelatorioDinamicoFiltro, string> >();
foreach (var key in PageRequest.QueryString.AllKeys)
{
if (key == null || key.IndexOf('_') <= 0)
{
continue;
}
// Pega o id do RelatorioDinamicoFiltro e o recupera em seguida
var idRelatorioDinamicoFiltro = key.Split('_')[1];
var filtro = relatorioDinamico.Filtros.Where(f => f.IdRelatorioDinamicoFiltro == idRelatorioDinamicoFiltro.StrParaInt()).FirstOrDefault();
filtros.Add(new Tuple <Glass.Global.Negocios.Entidades.RelatorioDinamicoFiltro, string>(filtro, PageRequest[key]));
}
int count;
// Recupera os dados a serem preenchidos no relatório
var lista = relatorioDinamicoFluxo.PesquisarListaDinamica(relatorioDinamico.IdRelatorioDinamico, filtros, 0, int.MaxValue, out count);
System.Data.DataTable dt = new System.Data.DataTable();
// Cria as colunas da grid
foreach (var campo in lista.First().Keys)
{
dt.Columns.Add(campo.RemoverAcentosEspacos(), typeof(string));
}
foreach (var item in lista)
{
var dr = dt.NewRow();
foreach (var campo in item)
{
dr[campo.Key] = campo.Value;
}
dt.Rows.Add(dr);
}
dt.AcceptChanges();
report.DataSources.Add(new ReportDataSource("ListaRegistros", dt));
// Recupera parâmetro de agrupamento
var agrupamento = filtros.Where(f => f.Item1.TipoControle == Data.Model.TipoControle.Agrupamento).FirstOrDefault();
var criterio = string.Join(" ", filtros.OrderBy(f => f.Item1.NumSeq)
.Where(f => f.Item2 != "" && f.Item2 != "|")
.Select(f => f.Item1.NomeFiltro + ": " + f.Item2.Replace("|", " "))
);
// Define parâmetro padrões
lstParam.Add(new ReportParameter("AgruparPor", agrupamento != null ? agrupamento.Item2 : "."));
lstParam.Add(new ReportParameter("Criterio", criterio));
lstParam.Add(new ReportParameter("NomeRelatorio", relatorioDinamico.NomeRelatorio));
lstParam.Add(new ReportParameter("Logotipo", Logotipo.GetReportLogo(PageRequest)));
lstParam.Add(new ReportParameter("TextoRodape", Geral.TextoRodapeRelatorio(login.Nome, true)));
lstParam.Add(new ReportParameter("CorRodape", "DimGray"));
return(null);
}
/// <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);
}
/// <summary>
/// Привязывает список сертификатов к товару в БД
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <param name="objCertificateList">список сертификатов</param>
/// <param name="ProductId">УИ товара</param>
/// <param name="strErr">сообщение об ошибке</param>
/// <returns>true - удачное завершение операции; false - ошибка</returns>
public static System.Boolean AssignCertificateListWithProduct(UniXP.Common.CProfile objProfile, System.Data.SqlClient.SqlCommand cmdSQL,
List <CCertificate> objCertificateList, System.Guid ProductId, ref System.String strErr)
{
System.Boolean bRet = false;
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr = "Не удалось получить соединение с базой данных.";
return(bRet);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
System.Data.DataTable addedCategories = new System.Data.DataTable();
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
addedCategories.Columns.Add(new System.Data.DataColumn("CertificateType_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_Num", typeof(System.Data.SqlTypes.SqlString)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_WhoGive", typeof(System.Data.SqlTypes.SqlString)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_BeginDate", typeof(System.Data.SqlTypes.SqlDateTime)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_EndDate", typeof(System.Data.SqlTypes.SqlDateTime)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_NumForWaybill", typeof(System.Data.SqlTypes.SqlString)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_IsActive", typeof(System.Data.SqlTypes.SqlBoolean)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_Description", typeof(System.Data.SqlTypes.SqlString)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_Image", typeof(System.Data.SqlTypes.SqlBinary)));
addedCategories.Columns.Add(new System.Data.DataColumn("Certificate_ImageFileFullName", typeof(System.Data.SqlTypes.SqlString)));
addedCategories.Columns.Add(new System.Data.DataColumn("ActionType_Id", typeof(System.Data.SqlTypes.SqlInt32)));
System.Data.DataRow newRow = null;
if ((objCertificateList != null) || (objCertificateList.Count > 0))
{
foreach (CCertificate objItem in objCertificateList)
{
newRow = addedCategories.NewRow();
newRow["Certificate_Guid"] = objItem.ID;
newRow["CertificateType_Guid"] = objItem.CertificateType.ID;
newRow["Certificate_Num"] = objItem.Num;
newRow["Certificate_WhoGive"] = objItem.WhoGive;
newRow["Certificate_BeginDate"] = objItem.BeginDate;
if ((objItem.EndDate.CompareTo(System.DateTime.MaxValue) == 0) || (objItem.EndDate.CompareTo(System.DateTime.MinValue) == 0))
{
newRow["Certificate_EndDate"] = System.DBNull.Value;
}
else
{
newRow["Certificate_EndDate"] = objItem.EndDate;
}
newRow["Certificate_NumForWaybill"] = objItem.NumForWaybill;
newRow["Certificate_IsActive"] = objItem.IsActive;
newRow["Certificate_Description"] = objItem.Description;
newRow["ActionType_Id"] = System.Convert.ToInt32(objItem.ActionType);
if (objItem.ImageCertificate != null)
{
newRow["Certificate_Image"] = objItem.ImageCertificate;
}
newRow["Certificate_ImageFileFullName"] = objItem.ImageCertificateFileName;
addedCategories.Rows.Add(newRow);
}
}
addedCategories.AcceptChanges();
cmd.Parameters.Clear();
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_AssignCertificateWithPart]", 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("@Parts_Guid", System.Data.SqlDbType.UniqueIdentifier));
//cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CERTIFICATE_IMAGE", System.Data.SqlDbType.Image));
cmd.Parameters.AddWithValue("@tCertificate", addedCategories);
cmd.Parameters["@tCertificate"].SqlDbType = System.Data.SqlDbType.Structured;
cmd.Parameters["@tCertificate"].TypeName = "dbo.udt_Certificate";
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;
cmd.Parameters["@Parts_Guid"].Value = ProductId;
cmd.ExecuteNonQuery();
System.Int32 iRes = (System.Int32)cmd.Parameters["@RETURN_VALUE"].Value;
if (iRes != 0)
{
strErr = (System.String)cmd.Parameters["@ERROR_MES"].Value;
}
if (cmdSQL == null)
{
DBConnection.Close();
}
bRet = (iRes == 0);
}
catch (System.Exception f)
{
strErr = f.Message;
}
finally
{
if (DBConnection != null)
{
DBConnection.Close();
}
}
return(bRet);
}
/// <summary>
/// Возвращает таблицу с накладными за указанный период
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <param name="IntWaybill_Guid">УИ документа</param>
/// <param name="IntWaybill_DateBegin">начало периода для выборки</param>
/// <param name="IntWaybill_DateEnd">конец периода для выборки</param>
/// <param name="IntWaybill_SrcCompanyGuid">УИ компании "Откуда"</param>
/// <param name="IntWaybill_SrcStockGuid">УИ склада "Откуда"<</param>
/// <param name="IntWaybill_DstCompanyGuid">УИ компании "Куда"</param>
/// <param name="IntWaybill_DstStockGuid">УИ склада "Куда"<</param>
/// <param name="Waybill_PaymentTypeGuid">УИ формы оплаты</param>
/// <param name="strErr">текст ошибки</param>
/// <param name="SelectIntWaybillInfoFromIntOrder">признак "информация для накладной запрашивается из заказа"</param>
/// <param name="OnlyUnShippedWaybills">признак "запрос только НЕ отгруженных накладных"</param>
/// <returns>таблицу</returns>
public static System.Data.DataTable GetWaybillTable(UniXP.Common.CProfile objProfile,
System.Data.SqlClient.SqlCommand cmdSQL, System.Guid IntWaybill_Guid,
System.DateTime IntWaybill_DateBegin, System.DateTime IntWaybill_DateEnd,
System.Guid IntWaybill_SrcCompanyGuid, System.Guid IntWaybill_SrcStockGuid,
System.Guid IntWaybill_DstCompanyGuid, System.Guid IntWaybill_DstStockGuid,
System.Guid Waybill_PaymentTypeGuid, ref System.String strErr,
System.Boolean SelectIntWaybillInfoFromIntOrder = false,
System.Boolean OnlyUnShippedWaybills = false
)
{
System.Data.DataTable dtReturn = new System.Data.DataTable();
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntOrder_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcStock_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcStock_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcStock_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcStock_IsActive", typeof(System.Boolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcStock_IsTrade", typeof(System.Boolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcWarehouse_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcWarehouseType_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcCompany_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcCompany_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcCompany_Acronym", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("SrcCompany_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstStock_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstStock_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstStock_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstStock_IsActive", typeof(System.Boolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstStock_IsTrade", typeof(System.Boolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstWarehouse_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstWarehouseType_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstCompany_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstCompany_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstCompany_Acronym", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("DstCompany_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("Currency_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("Currency_Abbr", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("Depart_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("Depart_Code", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("PaymentType_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("PaymentType_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillShipMode_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillShipMode_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillShipMode_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillState_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillState_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillState_Name", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Num", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("RetailWaybill_Num", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_BeginDate", typeof(System.DateTime)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillParent_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_ShipDate", typeof(System.DateTime)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Description", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_CurrencyRate", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_AllPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_RetailAllPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_AllDiscount", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_TotalPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Quantity", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_ForStock", typeof(System.Boolean)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Send", typeof(System.Boolean)));
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("Не удалось получить соединение с базой данных.");
return(dtReturn);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
if (IntWaybill_Guid.CompareTo(System.Guid.Empty) == 0)
{
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetIntWaybillList]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_DateBegin", System.Data.DbType.Date));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_DateEnd", System.Data.DbType.Date));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OnlyUnShippedWaybills", System.Data.DbType.Guid));
cmd.Parameters["@IntWaybill_DateBegin"].Value = IntWaybill_DateBegin;
cmd.Parameters["@IntWaybill_DateEnd"].Value = IntWaybill_DateEnd;
cmd.Parameters["@OnlyUnShippedWaybills"].Value = OnlyUnShippedWaybills;
if (IntWaybill_SrcCompanyGuid.CompareTo(System.Guid.Empty) != 0)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_SrcCompanyGuid", System.Data.DbType.Guid));
cmd.Parameters["@IntWaybill_SrcCompanyGuid"].Value = IntWaybill_SrcCompanyGuid;
}
if (IntWaybill_SrcStockGuid.CompareTo(System.Guid.Empty) != 0)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_SrcStockGuid", System.Data.DbType.Guid));
cmd.Parameters["@IntWaybill_SrcStockGuid"].Value = IntWaybill_SrcStockGuid;
}
if (IntWaybill_DstCompanyGuid.CompareTo(System.Guid.Empty) != 0)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_DstCompanyGuid", System.Data.DbType.Guid));
cmd.Parameters["@IntWaybill_DstCompanyGuid"].Value = IntWaybill_DstCompanyGuid;
}
if (IntWaybill_DstStockGuid.CompareTo(System.Guid.Empty) != 0)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_DstStockGuid", System.Data.DbType.Guid));
cmd.Parameters["@IntWaybill_DstStockGuid"].Value = IntWaybill_DstStockGuid;
}
if (Waybill_PaymentTypeGuid.CompareTo(System.Guid.Empty) != 0)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Waybill_PaymentTypeGuid", System.Data.DbType.Guid));
cmd.Parameters["@Waybill_PaymentTypeGuid"].Value = Waybill_PaymentTypeGuid;
}
}
else
{
if (SelectIntWaybillInfoFromIntOrder == true)
{
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetIntWaybillFromIntOrder]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntOrder_Guid", System.Data.DbType.Guid));
cmd.Parameters["@IntOrder_Guid"].Value = IntWaybill_Guid;
}
else
{
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetIntWaybill]", objProfile.GetOptionsDllDBName());
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_Guid", System.Data.DbType.Guid));
cmd.Parameters["@IntWaybill_Guid"].Value = IntWaybill_Guid;
}
}
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)
{
Direction = System.Data.ParameterDirection.Output
});
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
System.Int32 iRecordCount = 0;
if (rs.HasRows)
{
System.Data.DataRow newRow = null;
while (rs.Read())
{
iRecordCount++;
newRow = dtReturn.NewRow();
newRow["IntWaybill_Guid"] = ((rs["IntWaybill_Guid"] != System.DBNull.Value) ? (System.Guid)rs["IntWaybill_Guid"] : System.Guid.Empty);
newRow["IntWaybillParent_Guid"] = ((rs["IntWaybillParent_Guid"] != System.DBNull.Value) ? (System.Guid)rs["IntWaybillParent_Guid"] : System.Guid.Empty);
newRow["IntOrder_Guid"] = ((rs["IntOrder_Guid"] != System.DBNull.Value) ? (System.Guid)rs["IntOrder_Guid"] : System.Guid.Empty);
newRow["IntWaybill_Id"] = ((rs["IntWaybill_Id"] != System.DBNull.Value) ? (System.Int32)rs["IntWaybill_Id"] : 0);
newRow["SrcStock_Guid"] = ((rs["SrcStock_Guid"] != System.DBNull.Value) ? (System.Guid)rs["SrcStock_Guid"] : System.Guid.Empty);
newRow["SrcStock_Id"] = ((rs["SrcStock_Id"] != System.DBNull.Value) ? (System.Int32)rs["SrcStock_Id"] : 0);
newRow["SrcStock_Name"] = ((rs["SrcStock_Name"] != System.DBNull.Value) ? System.Convert.ToString(rs["SrcStock_Name"]) : System.String.Empty);
newRow["SrcStock_IsActive"] = ((rs["SrcStock_IsActive"] != System.DBNull.Value) ? System.Convert.ToBoolean(rs["SrcStock_IsActive"]) : false);
newRow["SrcStock_IsTrade"] = ((rs["SrcStock_IsTrade"] != System.DBNull.Value) ? System.Convert.ToBoolean(rs["SrcStock_IsTrade"]) : false);
newRow["SrcCompany_Guid"] = ((rs["SrcCompany_Guid"] != System.DBNull.Value) ? (System.Guid)rs["SrcCompany_Guid"] : System.Guid.Empty);
newRow["SrcCompany_Id"] = ((rs["SrcCompany_Id"] != System.DBNull.Value) ? (System.Int32)rs["SrcCompany_Id"] : 0);
newRow["SrcCompany_Acronym"] = ((rs["SrcCompany_Acronym"] != System.DBNull.Value) ? System.Convert.ToString(rs["SrcCompany_Acronym"]) : System.String.Empty);
newRow["SrcCompany_Name"] = ((rs["SrcCompany_Name"] != System.DBNull.Value) ? System.Convert.ToString(rs["SrcCompany_Name"]) : System.String.Empty);
newRow["SrcWarehouse_Guid"] = ((rs["SrcWarehouse_Guid"] != System.DBNull.Value) ? (System.Guid)rs["SrcWarehouse_Guid"] : System.Guid.Empty);
newRow["SrcWarehouseType_Guid"] = ((rs["SrcWarehouseType_Guid"] != System.DBNull.Value) ? (System.Guid)rs["SrcWarehouseType_Guid"] : System.Guid.Empty);
newRow["DstStock_Guid"] = ((rs["DstStock_Guid"] != System.DBNull.Value) ? (System.Guid)rs["DstStock_Guid"] : System.Guid.Empty);
newRow["DstStock_Id"] = ((rs["DstStock_Id"] != System.DBNull.Value) ? (System.Int32)rs["DstStock_Id"] : 0);
newRow["DstStock_Name"] = ((rs["DstStock_Name"] != System.DBNull.Value) ? System.Convert.ToString(rs["DstStock_Name"]) : System.String.Empty);
newRow["DstStock_IsActive"] = ((rs["DstStock_IsActive"] != System.DBNull.Value) ? System.Convert.ToBoolean(rs["DstStock_IsActive"]) : false);
newRow["DstStock_IsTrade"] = ((rs["DstStock_IsTrade"] != System.DBNull.Value) ? System.Convert.ToBoolean(rs["DstStock_IsTrade"]) : false);
newRow["DstWarehouse_Guid"] = ((rs["DstWarehouse_Guid"] != System.DBNull.Value) ? (System.Guid)rs["DstWarehouse_Guid"] : System.Guid.Empty);
newRow["DstWarehouseType_Guid"] = ((rs["DstWarehouseType_Guid"] != System.DBNull.Value) ? (System.Guid)rs["DstWarehouseType_Guid"] : System.Guid.Empty);
newRow["DstCompany_Guid"] = ((rs["DstCompany_Guid"] != System.DBNull.Value) ? (System.Guid)rs["DstCompany_Guid"] : System.Guid.Empty);
newRow["DstCompany_Id"] = ((rs["DstCompany_Id"] != System.DBNull.Value) ? (System.Int32)rs["DstCompany_Id"] : 0);
newRow["DstCompany_Acronym"] = ((rs["DstCompany_Acronym"] != System.DBNull.Value) ? System.Convert.ToString(rs["DstCompany_Acronym"]) : System.String.Empty);
newRow["DstCompany_Name"] = ((rs["DstCompany_Name"] != System.DBNull.Value) ? System.Convert.ToString(rs["DstCompany_Name"]) : System.String.Empty);
newRow["Depart_Guid"] = ((rs["Depart_Guid"] != System.DBNull.Value) ? (System.Guid)rs["Depart_Guid"] : System.Guid.Empty);
newRow["Depart_Code"] = ((rs["Depart_Code"] != System.DBNull.Value) ? System.Convert.ToString(rs["Depart_Code"]) : System.String.Empty);
newRow["Currency_Guid"] = ((rs["Currency_Guid"] != System.DBNull.Value) ? (System.Guid)rs["Currency_Guid"] : System.Guid.Empty);
newRow["Currency_Abbr"] = ((rs["Currency_Abbr"] != System.DBNull.Value) ? System.Convert.ToString(rs["Currency_Abbr"]) : System.String.Empty);
newRow["PaymentType_Guid"] = ((rs["PaymentType_Guid"] != System.DBNull.Value) ? (System.Guid)rs["PaymentType_Guid"] : System.Guid.Empty);
newRow["PaymentType_Name"] = ((rs["PaymentType_Name"] != System.DBNull.Value) ? System.Convert.ToString(rs["PaymentType_Name"]) : System.String.Empty);
newRow["IntWaybillState_Guid"] = rs["IntWaybillState_Guid"];
newRow["IntWaybillState_Id"] = rs["IntWaybillState_Id"];
newRow["IntWaybillState_Name"] = rs["IntWaybillState_Name"];
newRow["IntWaybillShipMode_Guid"] = rs["IntWaybillShipMode_Guid"];
newRow["IntWaybillShipMode_Id"] = rs["IntWaybillShipMode_Id"];
newRow["IntWaybillShipMode_Name"] = rs["IntWaybillShipMode_Name"];
newRow["IntWaybill_BeginDate"] = ((rs["IntWaybill_BeginDate"] != System.DBNull.Value) ? rs["IntWaybill_BeginDate"] : System.DBNull.Value);
newRow["IntWaybill_ShipDate"] = ((rs["IntWaybill_ShipDate"] != System.DBNull.Value) ? rs["IntWaybill_ShipDate"] : System.DBNull.Value);
newRow["IntWaybill_Num"] = rs["IntWaybill_Num"];
newRow["RetailWaybill_Num"] = rs["RetailWaybill_Num"];
newRow["IntWaybill_Description"] = rs["IntWaybill_Description"];
newRow["IntWaybill_AllPrice"] = rs["IntWaybill_AllPrice"];
newRow["IntWaybill_RetailAllPrice"] = rs["IntWaybill_RetailAllPrice"];
newRow["IntWaybill_AllDiscount"] = rs["IntWaybill_AllDiscount"];
newRow["IntWaybill_Quantity"] = System.Convert.ToDecimal(rs["IntWaybill_Quantity"]);
newRow["IntWaybill_ForStock"] = rs["IntWaybill_ForStock"];
newRow["IntWaybill_Send"] = rs["IntWaybill_Send"];
dtReturn.Rows.Add(newRow);
}
dtReturn.AcceptChanges();
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
strErr += (String.Format("\nНе удалось получить таблицу с накладными.\nТекст ошибки: {0}", f.Message));
}
return(dtReturn);
}
/// <summary>
/// Возвращает таблицу с приложением к накладной на внутреннее перемещение
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">объект "SQL-команда"</param>
/// <param name="uuidIntWaybillId">уи накладной на внутреннее перемещение</param>
/// <param name="strErr">строка с сообщением об ошибке</param>
/// <param name="bTableFromIntOrder">признак "приложение запрашивается из заказа на внутреннее перемещение"</param>
/// <returns>приложение к накладной в виде объекта класса DataTable</returns>
public static System.Data.DataTable GetIntWaybilItemTable(UniXP.Common.CProfile objProfile,
System.Data.SqlClient.SqlCommand cmdSQL, System.Guid uuidIntWaybillId, ref System.String strErr, System.Boolean bTableFromIntOrder = false)
{
System.Data.DataTable dtReturn = new System.Data.DataTable();
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybillItem_Id", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntOrderItem_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybill_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("Parts_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("Measure_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("ProductOwnerName", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("PARTS_NAME", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("PARTS_ARTICLE", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("Measure_ShortName", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_Quantity", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_Price", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_PriceImporter", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_RetailPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_Discount", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_DiscountPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_AllPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_TotalPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_RetailAllPrice", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("IntWaybItem_NDSPercent", typeof(System.Double)));
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("Не удалось получить соединение с базой данных.");
return(dtReturn);
}
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
if (bTableFromIntOrder == true)
{
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetIntWaybItemsFromIntOrder]", 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.Add(new System.Data.SqlClient.SqlParameter("@IntOrder_Guid", System.Data.DbType.Guid));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@IntOrder_Guid"].Value = uuidIntWaybillId;
}
else
{
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_GetIntWaybItems]", 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.Add(new System.Data.SqlClient.SqlParameter("@Waybill_Guid", System.Data.DbType.Guid));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@IntWaybill_Guid"].Value = uuidIntWaybillId;
}
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
System.Int32 iRecordCount = 0;
if (rs.HasRows)
{
System.Data.DataRow newRow = null;
while (rs.Read())
{
iRecordCount++;
newRow = dtReturn.NewRow();
newRow["IntWaybItem_Guid"] = ((rs["IntWaybItem_Guid"] != System.DBNull.Value) ? (System.Guid)rs["IntWaybItem_Guid"] : System.Guid.Empty);
newRow["IntOrderItem_Guid"] = ((rs["IntOrderItem_Guid"] != System.DBNull.Value) ? (System.Guid)rs["IntOrderItem_Guid"] : System.Guid.Empty);
newRow["IntWaybill_Guid"] = ((rs["IntWaybill_Guid"] != System.DBNull.Value) ? (System.Guid)rs["IntWaybill_Guid"] : System.Guid.Empty);
newRow["IntWaybillItem_Id"] = ((rs["IntWaybillItem_Id"] != System.DBNull.Value) ? (System.Int32)rs["IntWaybillItem_Id"] : 0);
newRow["Measure_Guid"] = ((rs["Measure_Guid"] != System.DBNull.Value) ? (System.Guid)rs["Measure_Guid"] : System.Guid.Empty);
newRow["Parts_Guid"] = ((rs["Parts_Guid"] != System.DBNull.Value) ? (System.Guid)rs["Parts_Guid"] : System.Guid.Empty);
newRow["ProductOwnerName"] = ((rs["ProductOwnerName"] != System.DBNull.Value) ? System.Convert.ToString(rs["ProductOwnerName"]) : System.String.Empty);
newRow["PARTS_NAME"] = ((rs["PARTS_NAME"] != System.DBNull.Value) ? System.Convert.ToString(rs["PARTS_NAME"]) : System.String.Empty);
newRow["PARTS_ARTICLE"] = ((rs["PARTS_ARTICLE"] != System.DBNull.Value) ? System.Convert.ToString(rs["PARTS_ARTICLE"]) : System.String.Empty);
newRow["Measure_ShortName"] = ((rs["Measure_ShortName"] != System.DBNull.Value) ? System.Convert.ToString(rs["Measure_ShortName"]) : System.String.Empty);
newRow["IntWaybItem_Quantity"] = rs["IntWaybItem_Quantity"];
newRow["IntWaybItem_Price"] = rs["IntWaybItem_Price"];
newRow["IntWaybItem_PriceImporter"] = rs["IntWaybItem_PriceImporter"];
newRow["IntWaybItem_RetailPrice"] = rs["IntWaybItem_RetailPrice"];
newRow["IntWaybItem_Discount"] = rs["IntWaybItem_Discount"];
newRow["IntWaybItem_DiscountPrice"] = rs["IntWaybItem_DiscountPrice"];
newRow["IntWaybItem_NDSPercent"] = rs["IntWaybItem_NDSPercent"];
newRow["IntWaybItem_AllPrice"] = rs["IntWaybItem_AllPrice"];
newRow["IntWaybItem_TotalPrice"] = rs["IntWaybItem_TotalPrice"];
newRow["IntWaybItem_RetailAllPrice"] = rs["IntWaybItem_RetailAllPrice"];
dtReturn.Rows.Add(newRow);
}
dtReturn.AcceptChanges();
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
strErr += (String.Format("\nНе удалось получить таблицу с приложением к накладной на внутреннее перемещение.\nТекст ошибки: {0}", f.Message));
}
return(dtReturn);
}
private void Form1_Load(object sender, EventArgs e)
{
周次数.Text = GetWeekOfYear().ToString();
excelApplication = null;//Excel Application Object
excelWorkBooks = null;//Workbooks
excelWorkBook = null;//Excel Workbook Object
excelWorkSheet = null;//Excel Worksheet Object
ActiveSheetIndex = 1;
System.Data.DataTable dt = new System.Data.DataTable();
dt.TableName = "table1";
dt.AcceptChanges();
}
/// <summary>
/// Возвращает таблицу с приложением к накладной на внутреннее перемещение
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">объект "SQL-команда"</param>
/// <param name="uuidIntWaybillId">уи накладной на внутреннее перемещение</param>
/// <param name="strErr">строка с сообщением об ошибке</param>
/// <returns>приложение к накладной на внутреннее перемещение в виде объекта класса DataTable</returns>
public static System.Data.DataTable GetIntWaybilItemTableForExportToExcel(UniXP.Common.CProfile objProfile,
System.Data.SqlClient.SqlCommand cmdSQL, System.Guid uuidIntWaybillId, ref System.String strErr)
{
System.Data.DataTable dtReturn = new System.Data.DataTable();
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_ID", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("PARTS_ID", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("PARTS_FULLNAME", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("OWNER_NAME", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("MEASURE_SHORTNAME", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_QUANTITY", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_BASEPRICE", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_PERCENT", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_DOUBLEPERCENT", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_TOTALPRICEWITHOUTNDS", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_NDS", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_STRNDS", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_NDSTOTALPRICE", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_TOTALPRICE", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_WEIGHT", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_PLACES", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_INTPLACES", typeof(System.Int32)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_TARA", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_QTYINPLACE", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("PARTS_CERTIFICATE", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_NOTES", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("WAYBITMS_PRICEWITHOUTNDS", typeof(System.Double)));
dtReturn.Columns.Add(new System.Data.DataColumn("COUNTRY_NAME", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("Parts_Guid", typeof(System.Guid)));
dtReturn.Columns.Add(new System.Data.DataColumn("Parts_Barcode", typeof(System.String)));
dtReturn.Columns.Add(new System.Data.DataColumn("Parts_BarcodeList", typeof(System.String)));
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
try
{
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr += ("Не удалось получить соединение с базой данных.");
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_GetIntWaybillProduction4StockFromIB]", 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.Add(new System.Data.SqlClient.SqlParameter("@IntWaybill_Guid", System.Data.DbType.Guid));
cmd.Parameters["@ERROR_MES"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@IntWaybill_Guid"].Value = uuidIntWaybillId;
System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader();
System.Int32 iRecordCount = 0;
if (rs.HasRows)
{
System.Data.DataRow newRow = null;
while (rs.Read())
{
iRecordCount++;
newRow = dtReturn.NewRow();
newRow["WAYBITMS_ID"] = rs["WAYBITMS_ID"];
newRow["PARTS_ID"] = rs["PARTS_ID"];
newRow["PARTS_FULLNAME"] = rs["PARTS_FULLNAME"];
newRow["OWNER_NAME"] = rs["OWNER_NAME"];
newRow["MEASURE_SHORTNAME"] = rs["MEASURE_SHORTNAME"];
newRow["WAYBITMS_QUANTITY"] = rs["WAYBITMS_QUANTITY"];
newRow["WAYBITMS_BASEPRICE"] = rs["WAYBITMS_BASEPRICE"];
newRow["WAYBITMS_PERCENT"] = rs["WAYBITMS_PERCENT"];
newRow["WAYBITMS_DOUBLEPERCENT"] = rs["WAYBITMS_DOUBLEPERCENT"];
newRow["WAYBITMS_TOTALPRICEWITHOUTNDS"] = rs["WAYBITMS_TOTALPRICEWITHOUTNDS"];
newRow["WAYBITMS_NDS"] = rs["WAYBITMS_NDS"];
newRow["WAYBITMS_STRNDS"] = rs["WAYBITMS_STRNDS"];
newRow["WAYBITMS_NDSTOTALPRICE"] = rs["WAYBITMS_NDSTOTALPRICE"];
newRow["WAYBITMS_TOTALPRICE"] = rs["WAYBITMS_TOTALPRICE"];
newRow["WAYBITMS_WEIGHT"] = rs["WAYBITMS_WEIGHT"];
newRow["WAYBITMS_PLACES"] = rs["WAYBITMS_PLACES"];
newRow["WAYBITMS_INTPLACES"] = rs["WAYBITMS_INTPLACES"];
newRow["WAYBITMS_TARA"] = rs["WAYBITMS_TARA"];
newRow["WAYBITMS_QTYINPLACE"] = rs["WAYBITMS_QTYINPLACE"];
newRow["PARTS_CERTIFICATE"] = rs["PARTS_CERTIFICATE"];
newRow["WAYBITMS_NOTES"] = rs["WAYBITMS_NOTES"];
newRow["WAYBITMS_PRICEWITHOUTNDS"] = rs["WAYBITMS_PRICEWITHOUTNDS"];
newRow["COUNTRY_NAME"] = rs["COUNTRY_NAME"];
newRow["Parts_Guid"] = rs["Parts_Guid"];
newRow["Parts_Barcode"] = rs["Parts_Barcode"];
newRow["Parts_BarcodeList"] = rs["Parts_BarcodeList"];
dtReturn.Rows.Add(newRow);
}
dtReturn.AcceptChanges();
}
rs.Dispose();
if (cmdSQL == null)
{
cmd.Dispose();
DBConnection.Close();
}
}
catch (System.Exception f)
{
strErr += (String.Format("\nНе удалось получить таблицу с приложением к накладной на внутренее перемещение.\nТекст ошибки: {0}", f.Message));
}
return(dtReturn);
}
private System.Data.DataTable ProcessObjects(Microsoft.Office.Interop.Excel.Range range, bool useHeaderRow, bool guessColumnType, bool ignoreEmptyRows, object[,] valueArray)
{
System.Data.DataTable dt = new System.Data.DataTable();
var beginat = 1;
if (useHeaderRow)
{
for (int k = 1; k <= valueArray.GetLength(1); k++)
{
var v = (worksheet.Cells[range.Row + 1, range.Column + (k - 1)] as Range).get_Value(Type.Missing);
if (v == null)
{
v = (worksheet.Cells[range.Row, range.Column + (k - 1)] as Range).get_Value(Type.Missing);
}
Type type = typeof(string);
if (guessColumnType && v != null)
{
type = v.GetType();
}
dt.Columns.Add((string)valueArray[1, k], type); //add columns to the data table.
}
beginat = 2;
}
else
{
for (int k = 1; k <= valueArray.GetLength(1); k++)
{
var v = (worksheet.Cells[range.Row, range.Column + (k - 1)] as Range).get_Value(Type.Missing);
Type type = typeof(string);
if (guessColumnType && v != null)
{
type = v.GetType();
}
dt.Columns.Add(k.ToString(), type); //add columns to the data table.
}
beginat = 1;
}
object[] singleDValue = new object[valueArray.GetLength(1)];
//value array first row contains column names. so loop starts from 2 instead of 1
for (int i = beginat; i <= valueArray.GetLength(0); i++)
{
bool hasValue = false;
for (int j = 0; j < valueArray.GetLength(1); j++)
{
if (valueArray[i, j + 1] != null)
{
singleDValue[j] = valueArray[i, j + 1].ToString();
}
else
{
singleDValue[j] = valueArray[i, j + 1];
}
if (ignoreEmptyRows && singleDValue[j] != null)
{
if (!string.IsNullOrEmpty(singleDValue[j].ToString()))
{
hasValue = true;
}
}
}
if (!ignoreEmptyRows)
{
hasValue = true;
}
if (hasValue)
{
dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
}
}
dt.AcceptChanges();
return(dt);
}
public void RefreshPanel()
{
//Laws Lu,2006/12/27 减少Open/Close次数
((SQLDomainDataProvider)DataProvider).PersistBroker.AutoCloseConnection = false;
((SQLDomainDataProvider)DataProvider).PersistBroker.OpenConnection();
if (_shelfFacade == null)
{
_shelfFacade = new ShelfFacade(this.DataProvider);
}
/* 尚未到期的 */
this.ultraDataSource1.Rows.Clear();
Hashtable expiredHt = _shelfFacade.GetNotExpiredShelf();
if (expiredHt != null)
{
System.Data.DataTable dtExpire = new System.Data.DataTable();
dtExpire.Columns.Add("ShelfNO1", typeof(string));
dtExpire.Columns.Add("ResidualTime", typeof(int));
dtExpire.Columns.Add("ShelfPosition", typeof(string));
foreach (DictionaryEntry dic in expiredHt)
{
BurnOutShelf sf = (BurnOutShelf)dic.Value;
TimeSpan span = sf.TimeSpan;
int min = span.Days * 24 * 60 + span.Hours * 60 + span.Minutes;
//she
dtExpire.Rows.Add(new object[] { sf.ShelfNO, min, sf.memo });
dtExpire.AcceptChanges();
}
System.Data.DataView dv = new System.Data.DataView(dtExpire);
dv.Sort = "ResidualTime DESC";
for (int i = 0; i < dv.Count; i++)
{
// try
// {
Infragistics.Win.UltraWinDataSource.UltraDataRow row = ultraDataSource1.Rows.Add();
row["ShelfNO1"] = dv[i]["ShelfNO1"].ToString();
row["ResidualTime"] = dv[i]["ResidualTime"].ToString() + "min";
row["ShelfPosition"] = dv[i]["ShelfPosition"].ToString();
// }
// catch{}
}
//ultraGrid1.DisplayLayout.Override. = Infragistics.Win.UltraWinGrid.RowFilterMode.
//ultraDataSource1.Band.Columns.
}
/* 已经到期的 */
this.ultraDataSource2.Rows.Clear();
Hashtable notExpiredHt = _shelfFacade.GetExpiredShelf();
if (notExpiredHt != null)
{
ArrayList alList = new ArrayList();
alList.AddRange(notExpiredHt.Keys);
alList.Sort();
foreach (object key in alList)
{
// try
// {
Infragistics.Win.UltraWinDataSource.UltraDataRow row = ultraDataSource2.Rows.Add();
if (notExpiredHt.ContainsKey(key))
{
BurnOutShelf bs = (BurnOutShelf)notExpiredHt[key];
row["ShelfNO2"] = bs.ShelfNO;
row["ShelfPosition2"] = bs.memo;
}
// }
// catch{}
}
}
/* 车位数量的 */
object volumn = _shelfFacade.GetBurnInOutVolumn(Guid.Empty.ToString());
if (volumn != null)
{
this.lblTotal.Text = (volumn as BurnInOutVolumn).Total.ToString("##.##");
this.lblUsed.Text = (volumn as BurnInOutVolumn).Used.ToString("##.##");
decimal residual = (volumn as BurnInOutVolumn).Total - (volumn as BurnInOutVolumn).Used;
this.lblResidual.Text = residual.ToString("##.##");
}
//Laws Lu,2006/12/27 减少Open/Close次数
((SQLDomainDataProvider)DataProvider).PersistBroker.CloseConnection();
((SQLDomainDataProvider)DataProvider).PersistBroker.AutoCloseConnection = true;
Application.DoEvents();
this.uclShelfNO.TextFocus(true, true);
// System.Windows.Forms.SendKeys.Send("+{TAB}");
}
/// <summary>
/// Count all tables records.
/// </summary>
/// <param name="p_tables">List of table names.</param>
/// <param name="p_filters">List of table filters.</param>
public System.Data.DataTable CountTablesRecords(System.Collections.Generic.List <string> p_tables, System.Collections.Generic.List <string> p_filters)
{
System.Data.DataTable v_count_total_table = new System.Data.DataTable();
int v_timeout = v_connection.v_timeout;
v_connection.SetTimeout(0);
string v_sql = "";
bool v_first = true;
int v_block_counter = 0;
for (int j = 0; j < p_tables.Count; j++)
{
if (!v_first)
{
v_sql += " union all ";
}
v_first = false;
v_block_counter++;
string v_table_name = "";
if (v_has_schema)
{
v_table_name = v_schema + "." + p_tables[j];
}
else
{
v_table_name = p_tables[j];
}
v_sql += "select count(*) as total, " + v_trim_function + "('" + p_tables[j] + "') as table_name from " + v_table_name + " " + p_filters[j] + " ";
if (v_block_counter >= 50 || (j == p_tables.Count - 1))
{
if (v_count_total_table == null)
{
v_count_total_table = v_connection.Query(v_sql, "t1");
}
else
{
v_count_total_table.Merge(v_connection.Query(v_sql, "t1"));
}
v_first = true;
v_block_counter = 0;
v_sql = "";
}
}
System.Data.DataTable dt2 = v_count_total_table.Clone();
dt2.Columns["total"].DataType = Type.GetType("System.Int32");
foreach (System.Data.DataRow dr in v_count_total_table.Rows)
{
dt2.ImportRow(dr);
}
dt2.AcceptChanges();
System.Data.DataView dv = dt2.DefaultView;
dv.Sort = "total DESC";
v_count_total_table = dv.ToTable();
v_connection.SetTimeout(v_timeout);
return(v_count_total_table);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
System.Data.DataTable dtData = new System.Data.DataTable();
if (fileUpload.HasFile)
{
using (TextFieldParser parser = new TextFieldParser(fileUpload.PostedFile.InputStream))
{
parser.CommentTokens = new string[] { "#" };
parser.SetDelimiters(new string[] { "," });
parser.HasFieldsEnclosedInQuotes = false;
//read column line
if (dtData.Columns.Count == 0)
{
if (!parser.EndOfData)
{
foreach (string col in parser.ReadFields())
{
dtData.Columns.Add(col);
}
}
}
while (!parser.EndOfData)
{
Boolean addRow = false;
string[] fields = parser.ReadFields();
foreach (string field in fields)
{
if (!String.IsNullOrWhiteSpace(field))
{
addRow = true;
break;
}
}
if (addRow)
{
System.Data.DataRow newRow = dtData.NewRow();
for (int i = 0; i < fields.Length; i++)
{
newRow[i] = fields[i];
}
dtData.Rows.Add(newRow);
dtData.AcceptChanges();
}
}
}
System.Data.DataTable dtreturn = new System.Data.DataTable();
Dictionary <string, object> rtrn = new Dictionary <string, object>();
rtrn.Add("dbCols", DBFields());
rtrn.Add("dataCols", dtData.Columns.OfType <System.Data.DataColumn>().Select(x => x.ColumnName).ToList());
rtrn.Add("data", new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(DataToJson(dtData)));
String json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(rtrn);
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "importdata", string.Format(@"var importdata = {0}; ShowData(importdata);", json), true);
//dgData.DataSource = dtData;
//dgData.DataBind();
//var y = DBFields();
}
}
/// <summary>
/// Сохраняет в БД информацию о кредитных лимитах
/// </summary>
/// <param name="objProfile">профайл</param>
/// <param name="cmdSQL">SQL-команда</param>
/// <param name="objCreditLimitList">список кредитных лимитов</param>
/// <param name="strErr">сообщение об ошибке</param>
/// <returns>true - удачное завершение; false - ошибка</returns>
public static System.Boolean SaveCreditLimitList(UniXP.Common.CProfile objProfile, System.Data.SqlClient.SqlCommand cmdSQL,
List <CCreditLimit> objCreditLimitList, CCustomer objCustomer, CCurrency objCurrency, ref System.String strErr)
{
System.Boolean bRet = false;
System.Data.SqlClient.SqlConnection DBConnection = null;
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlTransaction DBTransaction = null;
try
{
if (objCreditLimitList == null)
{
return(bRet);
}
if (cmdSQL == null)
{
DBConnection = objProfile.GetDBSource();
if (DBConnection == null)
{
strErr = "Не удалось получить соединение с базой данных.";
return(bRet);
}
DBTransaction = DBConnection.BeginTransaction();
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = DBConnection;
cmd.Transaction = DBTransaction;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
}
else
{
cmd = cmdSQL;
cmd.Parameters.Clear();
}
System.Data.DataTable addedCategories = new System.Data.DataTable();
addedCategories.Columns.Add(new System.Data.DataColumn("Customer_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
addedCategories.Columns.Add(new System.Data.DataColumn("Company_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
addedCategories.Columns.Add(new System.Data.DataColumn("Currency_Guid", typeof(System.Data.SqlTypes.SqlGuid)));
addedCategories.Columns.Add(new System.Data.DataColumn("CustomerLimit_ApprovedCurrencyValue", typeof(System.Data.SqlTypes.SqlMoney)));
addedCategories.Columns.Add(new System.Data.DataColumn("CustomerLimit_CurrencyValue", typeof(System.Data.SqlTypes.SqlMoney)));
addedCategories.Columns.Add(new System.Data.DataColumn("CustomerLimit_ApprovedDays", typeof(float)));
addedCategories.Columns.Add(new System.Data.DataColumn("CustomerLimit_Days", typeof(float)));
System.Data.DataRow newRow = null;
foreach (CCreditLimit objItem in objCreditLimitList)
{
newRow = addedCategories.NewRow();
newRow["Customer_Guid"] = objCustomer.ID;
newRow["Company_Guid"] = objItem.Company.ID;
newRow["Currency_Guid"] = objCurrency.ID;
newRow["CustomerLimit_ApprovedCurrencyValue"] = System.Convert.ToDecimal(objItem.ApprovedCurrencyValue);
newRow["CustomerLimit_CurrencyValue"] = System.Convert.ToDecimal(objItem.CurrencyValue);
newRow["CustomerLimit_ApprovedDays"] = objItem.ApprovedDays;
newRow["CustomerLimit_Days"] = objItem.Days;
addedCategories.Rows.Add(newRow);
}
if (objCreditLimitList.Count > 0)
{
addedCategories.AcceptChanges();
}
cmd.Parameters.Clear();
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_AssignCustomerLimit]", 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("@Customer_Guid", System.Data.SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Currency_Guid", System.Data.SqlDbType.UniqueIdentifier));
cmd.Parameters.AddWithValue("@tCreditLimitList", addedCategories);
cmd.Parameters["@tCreditLimitList"].SqlDbType = System.Data.SqlDbType.Structured;
cmd.Parameters["@tCreditLimitList"].TypeName = "dbo.udt_CreditLimitList";
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;
cmd.Parameters["@Customer_Guid"].Value = objCustomer.ID;
cmd.Parameters["@Currency_Guid"].Value = objCurrency.ID;
cmd.ExecuteNonQuery();
System.Int32 iRes = (System.Int32)cmd.Parameters["@RETURN_VALUE"].Value;
if (iRes != 0)
{
strErr = (System.String)cmd.Parameters["@ERROR_MES"].Value;
}
if (cmdSQL == null)
{
if (iRes == 0)
{
// подтверждаем транзакцию
if (DBTransaction != null)
{
DBTransaction.Commit();
// 2010.04.22
// временно пишем в ERP
cmd.Parameters.Clear();
cmd.CommandText = System.String.Format("[{0}].[dbo].[usp_AssignCustomerLimit_ERP]", 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("@Customer_Guid", System.Data.SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Currency_Guid", System.Data.SqlDbType.UniqueIdentifier));
cmd.Parameters.AddWithValue("@tCreditLimitList", addedCategories);
cmd.Parameters["@tCreditLimitList"].SqlDbType = System.Data.SqlDbType.Structured;
cmd.Parameters["@tCreditLimitList"].TypeName = "dbo.udt_CreditLimitList";
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;
cmd.Parameters["@Customer_Guid"].Value = objCustomer.ID;
cmd.Parameters["@Currency_Guid"].Value = objCurrency.ID;
cmd.ExecuteNonQuery();
iRes = (System.Int32)cmd.Parameters["@RETURN_VALUE"].Value;
}
}
else
{
// откатываем транзакцию
if (DBTransaction != null)
{
DBTransaction.Rollback();
}
}
DBConnection.Close();
}
bRet = (iRes == 0);
}
catch (System.Exception f)
{
if ((cmdSQL == null) && (DBTransaction != null))
{
DBTransaction.Rollback();
}
strErr = f.Message;
}
finally
{
if (DBConnection != null)
{
DBConnection.Close();
}
}
return(bRet);
}