public string[] createReceiveDataBase(CreateReceive receiveData)
{
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 PropertyUse (Unit, UseID, UseDate, UseWho, PropertyID, Quantity, Remark, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) " +
"VALUES(@Unit, @UseID, @UseDate, @UseWho, @PropertyID, @Quantity, @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("@UseID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(receiveData.receiveID);
cmd.Parameters.Add("@UseDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(receiveData.receiveDate);
cmd.Parameters.Add("@UseWho", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(receiveData.receiveByID);
cmd.Parameters.Add("@PropertyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(receiveData.rstationeryID);
cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(receiveData.receiveQuantity);
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(receiveData.receiveRemark);
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('PropertyUse') AS rID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Column = Int64.Parse(dr["rID"].ToString());
}
dr.Close();
if (Column != 0)
{
sql = "SELECT SUM(QCOUNT) FROM ("+
"SELECT Count(*) AS QCOUNT FROM PropertyDatabase "+
"LEFT JOIN PropertyPurchase ON PropertyDatabase.PropertyID=PropertyPurchase.PropertyID WHERE DATEDIFF(month,PropertyPurchase.CreateFileDate,getdate())=0 "+
"UNION ALL "+
"SELECT Count(*) AS QCOUNT FROM PropertyDatabase "+
"INNER JOIN PropertyUse ON PropertyDatabase.PropertyID=PropertyUse.PropertyID WHERE DATEDIFF(month,PropertyUse.CreateFileDate,getdate())=0 "+
"UNION ALL "+
"SELECT Count(*) AS QCOUNT FROM PropertyDatabase "+
"INNER JOIN PropertyScrap ON PropertyDatabase.PropertyID=PropertyScrap.PropertyID WHERE DATEDIFF(month,PropertyScrap.CreateFileDate,getdate())=0 "+
"UNION ALL "+
"SELECT Count(*) AS QCOUNT FROM PropertyDatabase "+
"INNER JOIN PropertyReturn ON PropertyDatabase.PropertyID=PropertyReturn.PropertyID WHERE DATEDIFF(month,PropertyReturn.CreateFileDate,getdate())=0 "+
") AS NEWTABLE";
cmd = new SqlCommand(sql, Sqlconn);
string stuNumber = cmd.ExecuteScalar().ToString();
string tcYear = (DateTime.Now.Year - 1911).ToString();
string tcMonth = (DateTime.Now.Month).ToString();
string stuIDName = CreateFileName[2] + "3" + tcYear.Substring(1, tcYear.Length - 1) + tcMonth.PadLeft(2, '0') + stuNumber.PadLeft(3, '0');
sql = "UPDATE PropertyUse SET UseID=(@UseID) WHERE ID=(@TID)";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@TID", SqlDbType.BigInt).Value = Column;
cmd.Parameters.Add("@UseID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stuIDName);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
}
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<CreateReceive> SearchReceive(int indexpage, SearchReceive receiveData)
{
List<CreateReceive> returnValue = new List<CreateReceive>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchReceiveConditionReturn(receiveData);
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 PropertyUse.UseDate DESC, PropertyUse.ID DESC) " +
"AS RowNum, PropertyUse.*,PropertyDatabase.PropertyName,PropertyDatabase.ItemUnit,StaffDatabase.StaffName AS receiveByName FROM PropertyUse " +
"INNER JOIN PropertyDatabase ON PropertyUse.PropertyID=PropertyDatabase.PropertyID AND PropertyDatabase.isDeleted=0 " +
"INNER JOIN StaffDatabase ON PropertyUse.UseWho=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
"WHERE PropertyUse.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("@txtreceiveID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(receiveData.txtreceiveID);
cmd.Parameters.Add("@txtreceiveDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(receiveData.txtreceiveDateStart);
cmd.Parameters.Add("@txtreceiveDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(receiveData.txtreceiveDateEnd);
cmd.Parameters.Add("@txtreceiveBy", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(receiveData.txtreceiveBy) + "%";
cmd.Parameters.Add("@txtrstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(receiveData.txtrstationeryID);
cmd.Parameters.Add("@txtrstationeryName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(receiveData.txtrstationeryName) + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateReceive addValue = new CreateReceive();
addValue.rID = dr["ID"].ToString();
addValue.Unit = dr["Unit"].ToString();
addValue.receiveID = dr["UseID"].ToString();
addValue.receiveDate = DateTime.Parse(dr["UseDate"].ToString()).ToString("yyyy-MM-dd");
addValue.receiveByID = dr["UseWho"].ToString();
addValue.receiveByName = dr["receiveByName"].ToString();
addValue.rstationeryID = dr["PropertyID"].ToString();
addValue.receiveQuantity = dr["Quantity"].ToString();
addValue.receiveRemark = dr["Remark"].ToString();
addValue.rstationeryName = dr["PropertyName"].ToString();
addValue.rstationeryUnit = dr["ItemUnit"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateReceive addValue = new CreateReceive();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] createReceiveDataBase(CreateReceive receiveData)
{
OtherDataBase sDB = new OtherDataBase();
if (int.Parse(sDB._StaffhaveRoles[2]) == 1)
{
return sDB.createReceiveDataBase(receiveData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}