public string[] createstationeryDataBase(CreateStationery StationeryData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
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 = "INSERT INTO PropertyDatabase (Unit, PropertyID, PropertyName, ItemUnit, SafetyStock, Category, Remark, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) " +
"VALUES(@Unit, @PropertyID, @PropertyName, @ItemUnit, @SafetyStock, @Category, @Remark, @CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]);
cmd.Parameters.Add("@PropertyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StationeryData.stationeryID);
cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StationeryData.stationeryName);
cmd.Parameters.Add("@ItemUnit", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StationeryData.stationeryUnit);
cmd.Parameters.Add("@SafetyStock", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StationeryData.safeQuantity);
cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StationeryData.stationeryType);
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StationeryData.remark);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (returnValue[0] != "0")
{
Int64 Column = 0;
sql = "select IDENT_CURRENT('PropertyDatabase') AS cID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Column = Int64.Parse(dr["cID"].ToString());
}
dr.Close();
if (Column != 0)
{
sql = "SELECT Count(*) AS QCOUNT FROM PropertyDatabase WHERE Category=(@Category) ";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StationeryData.stationeryType);
string stuNumber = cmd.ExecuteScalar().ToString();
string stuIDName = Chk.CheckStringtoIntFunction(StationeryData.stationeryType) + stuNumber.PadLeft(3, '0');
sql = "UPDATE PropertyDatabase SET PropertyID=(@PropertyID) WHERE ID=(@TID) ";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@TID", SqlDbType.BigInt).Value = Column;
cmd.Parameters.Add("@PropertyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stuIDName);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
//returnValue[1] = Column.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 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[] createstationeryDataBase(CreateStationery stationeryData)
{
OtherDataBase sDB = new OtherDataBase();
if (int.Parse(sDB._StaffhaveRoles[2]) == 1)
{
return sDB.createstationeryDataBase(stationeryData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
