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); }