public string[] SearchPurchaseCount(SearchPurchase purchaseData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchPurchaseConditionReturn(purchaseData);
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 PropertyPurchase "+
"INNER JOIN PropertyDatabase AS s1 ON PropertyPurchase.PropertyID=s1.PropertyID AND s1.isDeleted=0 " +
"WHERE PropertyPurchase.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtpurchaseID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtpurchaseID);
cmd.Parameters.Add("@txtfirmName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(purchaseData.txtfirmName) + "%";
cmd.Parameters.Add("@txtpurchaseDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateStart);
cmd.Parameters.Add("@txtpurchaseDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateEnd);
cmd.Parameters.Add("@txtstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtstationeryID);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<CreatePurchase> SearchPurchase(int indexpage, SearchPurchase purchaseData)
{
List<CreatePurchase> returnValue = new List<CreatePurchase>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchPurchaseConditionReturn(purchaseData);
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 PropertyPurchase.InputDate DESC, PropertyPurchase.ID DESC) " +
"AS RowNum, PropertyPurchase.*,s1.PropertyName,s1.ItemUnit FROM PropertyPurchase " +
"INNER JOIN PropertyDatabase AS s1 ON PropertyPurchase.PropertyID=s1.PropertyID AND s1.isDeleted=0 " +
"WHERE PropertyPurchase.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("@txtpurchaseID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtpurchaseID);
cmd.Parameters.Add("@txtfirmName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(purchaseData.txtfirmName) + "%";
cmd.Parameters.Add("@txtpurchaseDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateStart);
cmd.Parameters.Add("@txtpurchaseDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateEnd);
cmd.Parameters.Add("@txtstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtstationeryID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreatePurchase addValue = new CreatePurchase();
addValue.pID = dr["ID"].ToString();
addValue.purchaseID = dr["InputID"].ToString();
addValue.purchaseDate = DateTime.Parse(dr["InputDate"].ToString()).ToString("yyyy-MM-dd");
addValue.firmName = dr["CompanyName"].ToString();
addValue.firmTel = dr["CompanyTel"].ToString();
addValue.stationeryID = dr["PropertyID"].ToString();
addValue.Unit = dr["Unit"].ToString();
addValue.stationeryQuantity = dr["Quantity"].ToString();
addValue.stationeryPrice = dr["Price"].ToString();
addValue.stationeryName = dr["PropertyName"].ToString();
addValue.stationeryUnit = dr["ItemUnit"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreatePurchase addValue = new CreatePurchase();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchPurchaseDataCount(SearchPurchase purchaseData)
{
OtherDataBase sDB = new OtherDataBase();
if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
{
return sDB.SearchPurchaseCount(purchaseData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
private string SearchPurchaseConditionReturn(SearchPurchase purchaseData)
{
string ConditionReturn = "";
if (purchaseData.txtpurchaseID != null)
{
ConditionReturn += " AND PropertyPurchase.InputID = @txtpurchaseID ";
}
if (purchaseData.txtfirmName != null)
{
ConditionReturn += " AND PropertyPurchase.CompanyName like @txtfirmName ";
}
if (purchaseData.txtpurchaseDateStart != null && purchaseData.txtpurchaseDateEnd != null)
{
ConditionReturn += " AND PropertyPurchase.InputDate BETWEEN @txtpurchaseDateStart AND @txtpurchaseDateEnd";
}
if (purchaseData.txtstationeryID != null)
{
ConditionReturn += " AND PropertyPurchase.PropertyID = @txtstationeryID";
}
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 PropertyPurchase.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public List<CreatePurchase> SearchPurchaseData(int index, SearchPurchase purchaseData)
{
OtherDataBase sDB = new OtherDataBase();
return sDB.SearchPurchase(index, purchaseData);
}
