public string[] SearchStationeryResultCount(SearchStationery stationeryData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStationeryConditionReturn(stationeryData, "PropertyDatabase");
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM PropertyDatabase WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = "%" + stationeryData.txtstationeryName + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
private string SearchStationeryConditionReturn(SearchStationery stationerySystemData,string DBName)
{
string ConditionReturn = "";
if (stationerySystemData.txtstationeryID != null)
{
ConditionReturn += " AND PropertyID=@txtstationeryID ";
}
if (stationerySystemData.txtstationeryName != null)
{
ConditionReturn += " AND PropertyName like @PropertyName ";
}
if (stationerySystemData.txtsafeQuantityStart != null && stationerySystemData.txtsafeQuantityEnd != null)
{
ConditionReturn += " AND SafetyStock BETWEEN @txtsafeQuantityStart AND @txtsafeQuantityEnd";
}
if (stationerySystemData.txtstationeryType != null)
{
ConditionReturn += " AND Category = @txtstationeryType";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND " + DBName + ".Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public List<CreateStationery> SearchStationeryResult(int indexpage, SearchStationery stationeryData)
{
List<CreateStationery> returnValue = new List<CreateStationery>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStationeryConditionReturn(stationeryData, "PropertyDatabase");
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
/*string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PropertyDatabase.PropertyID DESC) " +
"AS RowNum, PropertyDatabase.* " +
"FROM PropertyDatabase WHERE isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";*/
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PropertyDatabase.ID DESC) " +
"AS RowNum, PropertyDatabase.*,(SELECT TOP 1 Price FROM PropertyPurchase " +
"INNER JOIN PropertyDatabase ON PropertyPurchase.PropertyID=PropertyDatabase.PropertyID WHERE PropertyPurchase.isDeleted=0 " +
"ORDER BY PropertyPurchase.InputDate DESC) AS NEWPrice FROM PropertyDatabase " +
"WHERE PropertyDatabase.isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = "%" + stationeryData.txtstationeryName + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateStationery addValue = new CreateStationery();
addValue.stationeryID = dr["PropertyID"].ToString();
addValue.Unit = dr["Unit"].ToString();
addValue.stationeryName = dr["PropertyName"].ToString();
addValue.stationeryUnit = dr["ItemUnit"].ToString();
addValue.stationeryType = dr["Category"].ToString();
addValue.inventory = this.pInventory(addValue.stationeryID);
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
CreateStationery addValue = new CreateStationery();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchStationeryCount(SearchStationery stationeryData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStationeryConditionReturn(stationeryData, "PropertyDatabase");
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "SELECT COUNT(*) FROM PropertyDatabase WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtstationeryID);
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(stationeryData.txtstationeryName) + "%";
cmd.Parameters.Add("@txtsafeQuantityStart", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtsafeQuantityStart);
cmd.Parameters.Add("@txtsafeQuantityEnd", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtsafeQuantityEnd);
cmd.Parameters.Add("@txtstationeryType", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtstationeryType);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<CreateStationery> SearchStationery(int indexpage, SearchStationery stationeryData)
{
List<CreateStationery> returnValue = new List<CreateStationery>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStationeryConditionReturn(stationeryData, "baseDB");
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY baseDB.PropertyID DESC) " +
"AS RowNum, baseDB.*,ISNULL((SELECT TOP 1 Price FROM PropertyPurchase " +
"INNER JOIN PropertyDatabase ON PropertyPurchase.PropertyID=PropertyDatabase.PropertyID "+
"WHERE PropertyPurchase.isDeleted=0 AND PropertyPurchase.PropertyID=baseDB.PropertyID " +
"ORDER BY PropertyPurchase.InputDate DESC, PropertyPurchase.ID DESC),0) "+
"AS NEWPrice FROM PropertyDatabase AS baseDB " +
"WHERE baseDB.isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@txtstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtstationeryID);
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(stationeryData.txtstationeryName) + "%";
cmd.Parameters.Add("@txtsafeQuantityStart", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtsafeQuantityStart);
cmd.Parameters.Add("@txtsafeQuantityEnd", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtsafeQuantityEnd);
cmd.Parameters.Add("@txtstationeryType", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stationeryData.txtstationeryType);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateStationery addValue = new CreateStationery();
addValue.sID = dr["ID"].ToString();
addValue.Unit = dr["Unit"].ToString();
addValue.stationeryID = dr["PropertyID"].ToString();
addValue.stationeryName = dr["PropertyName"].ToString();
addValue.stationeryType = dr["Category"].ToString();
addValue.stationeryUnit = dr["ItemUnit"].ToString();
addValue.safeQuantity = dr["SafetyStock"].ToString();
addValue.remark = dr["Remark"].ToString();
addValue.inventory = this.pInventory(addValue.stationeryID);
addValue.recentPrice = dr["NEWPrice"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateStationery addValue = new CreateStationery();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchStationeryResultCount4(SearchStationery stationeryData)
{
OtherDataBase sDB = new OtherDataBase();
if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
{
return sDB.SearchStationeryResultCount(stationeryData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<CreateStationery> SearchStationeryResult4(int index, SearchStationery stationeryData)
{
OtherDataBase sDB = new OtherDataBase();
return sDB.SearchStationeryResult(index, stationeryData);
}
