public string[] GetChaList(string prefixText, int count, string contextKey)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
string sql = @"SELECT a.[AddrName] FROM [DBO].[mstAddress] a
INNER JOIN [dbo].[mstAddressType] at
ON CAST(a.AddrType as int) = at.pk_AddrTypeID
WHERE a.[AddrActive] = 1 AND at.[AddrType] = 'CH'
AND a.[fk_LocationID] = " + contextKey +
" AND [AddrName] LIKE @prefixText";
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["AddrName"].ToString(), i);
i++;
}
return(items);
}
public string[] GetPortList(string prefixText, int count)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
string sql = "select PortName+','+PortCode port,pk_PortID ID from DSR.dbo.mstPort where PortName like @prefixText";
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
//foreach (DataRow dr in dt.Rows)
for (int cnt = 0; cnt < dt.Rows.Count; cnt++)
{
//items.SetValue(dr["port"].ToString(), i);
//items.SetValue(dr["port"].ToString(), dr["ID"].ToString());
items[cnt] = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(Convert.ToString(dt.Rows[cnt]["port"]), Convert.ToString(dt.Rows[cnt]["ID"]));
//i++;
}
return(items);
}
public string[] GetPortListBL(string prefixText, int count)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
string sql = @"SELECT PortName collate SQL_Latin1_General_CP1_CI_AS+' | '+portCode collate SQL_Latin1_General_CP1_CI_AS+' | '+
(SELECT countryname FROM dbo.mstCountry WHERE countryabbr = LEFT(portCode collate SQL_Latin1_General_CP1_CI_AS,2)) Name
FROM DSR.dbo.mstPort where PortName LIKE @prefixText";
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["Name"].ToString(), i);
i++;
}
return(items);
}
public string[] GetRefundPayableTo(string prefixText, int count)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
// string sql = @"SELECT a.[AddrName] FROM [DBO].[mstAddress] a
// INNER JOIN [dbo].[mstAddressType] at
// ON CAST(a.AddrType as int) = at.pk_AddrTypeID
// WHERE a.[AddrActive] = 1 AND at.[AddrType] = 'FD' AND [AddrName] LIKE @prefixText";
string sql = @"SELECT [CustName] FROM [DSR].[DBO].[mstCustomer]
WHERE [ACTIVE]='Y' AND [ISDELETED]=0 AND [CustName] LIKE @prefixText
ORDER BY [CUSTNAME]";
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["CustName"].ToString(), i);
i++;
}
return(items);
}
public string[] GetVolumeUnitList(string prefixText, int count)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
string sql = "SELECT [UnitName] FROM [dbo].[mstUOM] WHERE [UnitType] = 'V' AND UnitName LIKE @prefixText"; //[pk_UOMId] ,[UnitCode] ,
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["UnitName"].ToString(), i);
i++;
}
return(items);
}
public string[] GetInvoiceNo(string prefixText, int count)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
string sql = "select InvoiceNo from finInvoice WHERE InvoiceNo LIKE @prefixText";
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["InvoiceNo"].ToString(), i);
i++;
}
return(items);
}
public string[] GetCountryList(string prefixText, int count)
{
AppCodeClass ac = new AppCodeClass();
//return GetCompletionList1(prefixText, count);
string sql = "Select * from mstCountry Where CountryName like @prefixText";
// SqlDataAdapter da = new SqlDataAdapter(sql, "Data Source=DILP-PC;Initial Catalog=NVOCC;Integrated Security=True;Pooling=true;Connection Timeout=30;Max Pool Size=40;Min Pool Size=5");
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["CountryName"].ToString(), i);
i++;
}
return(items);
}
public string[] GetVesselList(string prefixText, int count)
{
count = 10;
AppCodeClass ac = new AppCodeClass();
//string sql = "select (VesselName + ' | ' + CallSign) Name from dbo.trnVessel where VesselName like @prefixText";
string sql = "select VesselName Name from dbo.trnVessel where VesselName like @prefixText";
SqlDataAdapter da = new SqlDataAdapter(sql, ac.ConnectionString);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["Name"].ToString(), i);
i++;
}
return(items);
}
