private string SearchScrapConditionReturn(SearchScrap scrapData)
{
string ConditionReturn = "";
if (scrapData.txtscrappedID != null)
{
ConditionReturn += " AND PropertyScrap.ScrapID = @txtscrappedID ";
}
if (scrapData.txtscrappedDateStart != null && scrapData.txtscrappedDateEnd != null)
{
ConditionReturn += " AND PropertyScrap.ScrapDate BETWEEN @txtscrappedDateStart AND @txtscrappedDateEnd ";
}
if (scrapData.txtscrappedBy != null)
{
ConditionReturn += " AND StaffDatabase.StaffName like @txtscrappedBy ";
}
if (scrapData.txtsstationeryID != null)
{
ConditionReturn += " AND PropertyScrap.PropertyID = @txtsstationeryID ";
}
if (scrapData.txtsstationeryName != null)
{
ConditionReturn += " AND PropertyDatabase.PropertyName like @txtsstationeryName ";
}
return ConditionReturn;
}
public string[] SearchScrapCount(SearchScrap scrapData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchScrapConditionReturn(scrapData);
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 PropertyScrap " +
"INNER JOIN PropertyDatabase ON PropertyScrap.PropertyID=PropertyDatabase.PropertyID AND PropertyDatabase.isDeleted=0 " +
"INNER JOIN StaffDatabase ON PropertyScrap.Transactor=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
"WHERE PropertyScrap.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtscrappedID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(scrapData.txtscrappedID);
cmd.Parameters.Add("@txtscrappedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(scrapData.txtscrappedDateStart);
cmd.Parameters.Add("@txtscrappedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(scrapData.txtscrappedDateEnd);
cmd.Parameters.Add("@txtscrappedBy", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(scrapData.txtscrappedBy) + "%";
cmd.Parameters.Add("@txtsstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(scrapData.txtsstationeryID);
cmd.Parameters.Add("@txtsstationeryName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(scrapData.txtsstationeryName) + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchScrapDataCount(SearchScrap scrapData)
{
OtherDataBase sDB = new OtherDataBase();
if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
{
return sDB.SearchScrapCount(scrapData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<CreateScrap> SearchScrap(int indexpage, SearchScrap scrapData)
{
List<CreateScrap> returnValue = new List<CreateScrap>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchScrapConditionReturn(scrapData);
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 PropertyScrap.ScrapDate DESC,PropertyScrap.ID DESC) " +
"AS RowNum, PropertyScrap.*,PropertyDatabase.PropertyName,PropertyDatabase.ItemUnit,StaffDatabase.StaffName AS receiveByName FROM PropertyScrap " +
"INNER JOIN PropertyDatabase ON PropertyScrap.PropertyID=PropertyDatabase.PropertyID AND PropertyDatabase.isDeleted=0 " +
"INNER JOIN StaffDatabase ON PropertyScrap.Transactor=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
"WHERE PropertyScrap.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("@txtscrappedID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(scrapData.txtscrappedID);
cmd.Parameters.Add("@txtscrappedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(scrapData.txtscrappedDateStart);
cmd.Parameters.Add("@txtscrappedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(scrapData.txtscrappedDateEnd);
cmd.Parameters.Add("@txtscrappedBy", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(scrapData.txtscrappedBy) + "%";
cmd.Parameters.Add("@txtsstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(scrapData.txtsstationeryID);
cmd.Parameters.Add("@txtsstationeryName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(scrapData.txtsstationeryName) + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateScrap addValue = new CreateScrap();
addValue.sID = dr["ID"].ToString();
addValue.Unit = dr["Unit"].ToString();
addValue.scrappedID = dr["ScrapID"].ToString();
addValue.scrappedDate = DateTime.Parse(dr["ScrapDate"].ToString()).ToString("yyyy-MM-dd");
addValue.scrappedByID = dr["Transactor"].ToString();
addValue.scrappedByName = dr["receiveByName"].ToString();
addValue.sstationeryID = dr["PropertyID"].ToString();
addValue.scrappedQuantity = dr["Quantity"].ToString();
addValue.scrappedRemark = dr["Remark"].ToString();
addValue.sstationeryName = dr["PropertyName"].ToString();
addValue.sstationeryUnit = dr["ItemUnit"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateScrap addValue = new CreateScrap();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<CreateScrap> SearchScrapData(int index, SearchScrap scrapData)
{
OtherDataBase sDB = new OtherDataBase();
return sDB.SearchScrap(index, scrapData);
}
