コード例 #1
0
        public ActionResult CreateNewPurchase(CreatePurchase purchase)
        {
            List <DetailedPurchase> purchaseCount = purchaseDataAccess.GetAllPurchases();

            if (purchaseCount.Count() > purchase.SupplyId)
            {
                return(Conflict());
            }
            else
            {
                purchaseDataAccess.CreatePurchase(purchase);
                return(Ok());
            }
        }
コード例 #2
0
        public void CreatePurchase(CreatePurchase purchase)
        {
            const string query = @"INSERT Purchase (PurchaseDate, SupplyId) VALUES (@purchaseDate, @supplyId)";

            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.Parameters.AddWithValue("@supplyId", purchase.SupplyId);
                    cmd.Parameters.AddWithValue("@purchaseDate", purchase.purchaseDate);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);;
            }
        }
コード例 #3
0
    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;
    }
コード例 #4
0
    public string[] createPurchaseDataBase(CreatePurchase purchaseData)
    {
        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 PropertyPurchase (Unit, InputID, InputDate, CompanyName, CompanyTel, PropertyID, Quantity, Price, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) " +
                    "VALUES(@Unit, @InputID, @InputDate, @CompanyName, @CompanyTel, @PropertyID, @Quantity, @Price, @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("@InputID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.purchaseID);
                cmd.Parameters.Add("@InputDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.purchaseDate);
                cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(purchaseData.firmName);
                cmd.Parameters.Add("@CompanyTel", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(purchaseData.firmTel);
                cmd.Parameters.Add("@PropertyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.stationeryID);
                cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.stationeryQuantity);
                cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(purchaseData.stationeryPrice);
                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('PropertyPurchase') AS pID";
                    cmd = new SqlCommand(sql, Sqlconn);
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Column = Int64.Parse(dr["pID"].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 PropertyPurchase SET InputID=(@InputID) WHERE ID=(@TID) ";
                        cmd = new SqlCommand(sql, Sqlconn);
                        cmd.Parameters.Add("@TID", SqlDbType.BigInt).Value = Column;
                        cmd.Parameters.Add("@InputID", 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;
    }
コード例 #5
0
 public string[] createPurchaseDataBase(CreatePurchase purchaseData)
 {
     OtherDataBase sDB = new OtherDataBase();
     if (int.Parse(sDB._StaffhaveRoles[2]) == 1)
     {
         return sDB.createPurchaseDataBase(purchaseData);
     }
     else
     {
         return new string[2] { _noRole, _errorMsg };
     }
 }