Пример #1
0
        public DataTable GetAllStyleByYear(BOMModel objBOMModel)
        {
            DataTable dt = new DataTable();

            string sql = "";

            sql = "SELECT " +
                  "TO_CHAR (NVL (BUYER_ID,'0'))BUYER_ID, " +
                  "TO_CHAR (NVL (BUYER_NAME,'N/A'))BUYER_NAME, " +
                  "TO_CHAR (NVL (SEASON_ID,'0'))SEASON_ID, " +
                  "TO_CHAR (NVL (SEASON_NAME,'N/A'))SEASON_NAME, " +
                  "TO_CHAR (NVL (SEASON_YEAR,'0'))SEASON_YEAR, " +
                  "TO_CHAR (NVL (STYLE_NO,'N/A'))STYLE_NO " +
                  " FROM VEW_STYLE_ENQUIRY where head_office_id = '" + objBOMModel.HeadOfficeId + "' AND branch_office_id = '" + objBOMModel.BranchOfficeId + "'  ";

            if (!string.IsNullOrEmpty(objBOMModel.SeasonYear))
            {
                sql = sql + "and SEASON_YEAR = '" + objBOMModel.SeasonYear + "'";
            }
            if (!string.IsNullOrEmpty(objBOMModel.StyleNo))
            {
                sql = sql + " and STYLE_NO = '" + objBOMModel.StyleNo + "'   ";
            }
            if (!string.IsNullOrEmpty(objBOMModel.SeasonName))
            {
                sql = sql + " and SEASON_NAME = '" + objBOMModel.SeasonName + "'   ";
            }
            if (!string.IsNullOrEmpty(objBOMModel.BuyerName))
            {
                sql = sql + " and BUYER_NAME = '" + objBOMModel.BuyerName + "'   ";
            }
            sql = sql + " ORDER BY STYLE_NO";

            OracleCommand     objCommand     = new OracleCommand(sql);
            OracleDataAdapter objDataAdapter = new OracleDataAdapter(objCommand);

            using (OracleConnection strConn = GetConnection())
            {
                try
                {
                    objCommand.Connection = strConn;
                    strConn.Open();
                    objDataAdapter.Fill(dt);
                    dt.AcceptChanges();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error : " + ex.Message);
                }

                finally
                {
                    strConn.Close();
                }
            }


            return(dt);
        }
Пример #2
0
        public DataTable GetBomRecordForEdit(BOMModel objBOMModel)
        {
            DataTable dt2 = new DataTable();

            string sql = "";

            sql = "SELECT " +
                  "STYLE_NO, " +
                  "BUYER_ID, " +
                  "SEASON_ID, " +
                  "SEASON_YEAR, " +
                  "R3_CODE, " +
                  "TO_CHAR(LAST_UPDATE_DATE, 'dd/MM/yyyy') LAST_UPDATE_DATE, " +
                  "ITEM_NAME, " +
                  "ITEM_DESCRIPTION, " +
                  "MODEL_CODE, " +
                  "COMPONENT_NAME, " +
                  "COLOR_CODE, " +
                  "COLOR_NAME, " +
                  "FABRIC_QUANTITY, " +
                  "UNIT_ID, " +
                  "REMARKS, " +
                  "TRAN_ID, " +
                  "HEAD_OFFICE_ID, " +
                  "BRANCH_OFFICE_ID " +
                  "FROM VEW_BILL_OF_MATERIAL_SUB WHERE HEAD_OFFICE_ID = '" + objBOMModel.HeadOfficeId + "' AND BRANCH_OFFICE_ID = '" + objBOMModel.BranchOfficeId + "' ";

            if (!string.IsNullOrEmpty(objBOMModel.StyleNo))
            {
                sql = sql + "and STYLE_NO = '" + objBOMModel.StyleNo + "'";
            }

            if (!string.IsNullOrEmpty(objBOMModel.BuyerId))
            {
                sql = sql + "and BUYER_ID = '" + objBOMModel.BuyerId + "'";
            }

            //if (!string.IsNullOrEmpty(objBOMModel.ItemTypeId))
            //{

            //    sql = sql + "and ITEM_TYPE_ID = '" + objBOMModel.ItemTypeId + "'";
            //}

            if (!string.IsNullOrEmpty(objBOMModel.SeasonYear))
            {
                sql = sql + "and SEASON_YEAR= '" + objBOMModel.SeasonYear + "'";
            }

            if (!string.IsNullOrEmpty(objBOMModel.SeasonId))
            {
                sql = sql + "and SEASON_ID = '" + objBOMModel.SeasonId + "'";
            }


            if (objBOMModel.LastUpdateDate.Length > 6)
            {
                sql = sql + "and LAST_UPDATE_DATE= TO_DATE( '" + objBOMModel.LastUpdateDate + "', 'dd/MM/yyyy' )";
            }

            sql = sql + " ORDER BY STYLE_NO";

            OracleCommand     objCommand     = new OracleCommand(sql);
            OracleDataAdapter objDataAdapter = new OracleDataAdapter(objCommand);

            using (OracleConnection strConn = GetConnection())
            {
                try
                {
                    objCommand.Connection = strConn;
                    strConn.Open();
                    objDataAdapter.Fill(dt2);
                    dt2.AcceptChanges();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error : " + ex.Message);
                }

                finally
                {
                    strConn.Close();
                }
            }


            return(dt2);
        }
Пример #3
0
        public DataTable GetBomRecord(BOMModel objBOMModel)
        {
            DataTable dt1 = new DataTable();

            string sql = "";

            sql = "SELECT " +
                  "ROWNUM SL, " +
                  "STYLE_NO, " +
                  "BUYER_ID, " +
                  "BUYER_NAME, " +
                  "SEASON_YEAR, " +
                  "SEASON_ID, " +
                  "SEASON_NAME, " +
                  "R3_CODE, " +
                  "TO_CHAR(LAST_UPDATE_DATE, 'dd/MM/yyyy') LAST_UPDATE_DATE, " +
                  "HEAD_OFFICE_ID, " +
                  "BRANCH_OFFICE_ID " +
                  "FROM VEW_BILL_OF_MATERIAL_MAIN WHERE HEAD_OFFICE_ID = '" + objBOMModel.HeadOfficeId + "' AND BRANCH_OFFICE_ID = '" + objBOMModel.BranchOfficeId + "'  ";


            if (!string.IsNullOrEmpty(objBOMModel.SearchBy))
            {
                sql = sql + "and (lower(STYLE_NO) like lower( '%" + objBOMModel.SearchBy + "%')  or upper(STYLE_NO)like upper('%" + objBOMModel.SearchBy + "%') ) or (lower(SEASON_YEAR) like lower( '%" + objBOMModel.SearchBy + "%')  or upper(SEASON_YEAR)like upper('%" + objBOMModel.SearchBy + "%') or (lower(BUYER_NAME) like lower( '%" + objBOMModel.SearchBy + "%')  or upper(BUYER_NAME)like upper('%" + objBOMModel.SearchBy + "%') ) )";
            }

            if (!string.IsNullOrEmpty(objBOMModel.SeasonYear))
            {
                sql = sql + "and SEASON_YEAR = '" + objBOMModel.SeasonYear + "'";
            }
            if (!string.IsNullOrEmpty(objBOMModel.StyleNo))
            {
                sql = sql + " and STYLE_NO = '" + objBOMModel.StyleNo + "'   ";
            }
            if (!string.IsNullOrEmpty(objBOMModel.SeasonName))
            {
                sql = sql + " and SEASON_NAME = '" + objBOMModel.SeasonName + "'   ";
            }
            if (!string.IsNullOrEmpty(objBOMModel.BuyerName))
            {
                sql = sql + " and BUYER_NAME = '" + objBOMModel.BuyerName + "'   ";
            }
            sql = sql + " ORDER BY STYLE_NO";


            OracleCommand     objCommand     = new OracleCommand(sql);
            OracleDataAdapter objDataAdapter = new OracleDataAdapter(objCommand);

            using (OracleConnection strConn = GetConnection())
            {
                try
                {
                    objCommand.Connection = strConn;
                    strConn.Open();
                    objDataAdapter.Fill(dt1);
                    dt1.AcceptChanges();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error : " + ex.Message);
                }

                finally
                {
                    strConn.Close();
                }
            }


            return(dt1);
        }
Пример #4
0
        public string DeleteBomEntry(BOMModel objBOMModel)
        {
            string strMsg = "";

            string[] TranIdArray = objBOMModel.GridTranId.Split(',');
            int      x           = TranIdArray.Count();

            for (int i = 0; i < x; i++)
            {
                var arrayTranId = TranIdArray[i];

                OracleTransaction objOracleTransaction = null;
                OracleCommand     objOracleCommand     = new OracleCommand("pro_bill_of_material_delete");
                objOracleCommand.CommandType = CommandType.StoredProcedure;


                if (objBOMModel.StyleNo != "")
                {
                    objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.StyleNo;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }


                if (objBOMModel.BuyerId != "")
                {
                    objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.BuyerId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }


                if (objBOMModel.SeasonId != "")
                {
                    objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }


                if (objBOMModel.SeasonYear != "")
                {
                    objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonYear;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }

                if (arrayTranId != null)
                {
                    objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }


                objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value        = objBOMModel.UpdateBy;
                objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value   = objBOMModel.HeadOfficeId;
                objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.BranchOfficeId;



                objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;

                using (OracleConnection strConn = GetConnection())
                {
                    try
                    {
                        objOracleCommand.Connection = strConn;
                        strConn.Open();
                        trans = strConn.BeginTransaction();
                        objOracleCommand.ExecuteNonQuery();
                        trans.Commit();
                        strConn.Close();


                        strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString();
                    }

                    catch (Exception ex)
                    {
                        throw new Exception("Error : " + ex.Message);
                        objOracleTransaction.Rollback();
                    }

                    finally
                    {
                        strConn.Close();
                    }
                }
            }


            return(strMsg);
        }
Пример #5
0
        public string SaveBomEntry(BOMModel objBOMModel)
        {
            string strMsg = "";


            int x = objBOMModel.ItemDescription.Count();

            for (int i = 0; i < x; i++)
            {
                var arrayItemName        = objBOMModel.ItemName[i];
                var arrayItemDescription = objBOMModel.ItemDescription[i];
                var arrayModelCode       = objBOMModel.ModelCode[i];
                var arrayComponentName   = objBOMModel.ComponentName[i];
                var arrayColorId         = objBOMModel.ColorId[i];
                var arrayColorName       = objBOMModel.ColorName[i];
                var arrayFabricQuantity  = objBOMModel.FabricQuantity[i];
                var arrayUnitId          = objBOMModel.UnitId[i];
                var arrayRemarks         = objBOMModel.Remarks[i];
                var arrayTranId          = objBOMModel.TranId[i];

                OracleTransaction objOracleTransaction = null;
                OracleCommand     objOracleCommand     = new OracleCommand("pro_bill_of_material_save");
                objOracleCommand.CommandType = CommandType.StoredProcedure;


                if (objBOMModel.StyleNo != "")
                {
                    objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.StyleNo;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }

                if (objBOMModel.BuyerId != "")
                {
                    objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.BuyerId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }


                if (objBOMModel.SeasonYear != "")
                {
                    objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonYear;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }



                if (objBOMModel.SeasonId != "")
                {
                    objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }



                if (objBOMModel.R3Code != "")
                {
                    objOracleCommand.Parameters.Add("p_R3_CODE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.R3Code;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_R3_CODE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }



                if (objBOMModel.LastUpdateDate != "")
                {
                    objOracleCommand.Parameters.Add("p_LAST_UPDATE_DATE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.LastUpdateDate;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_LAST_UPDATE_DATE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }



                if (arrayItemName != "")
                {
                    objOracleCommand.Parameters.Add("p_ITEM_NAME", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = arrayItemName;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_ITEM_NAME", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
                }


                if (arrayItemDescription != null)
                {
                    objOracleCommand.Parameters.Add("p_ITEM_DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayItemDescription;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_ITEM_DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }



                if (arrayModelCode != null)
                {
                    objOracleCommand.Parameters.Add("p_MODEL_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayModelCode;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_MODEL_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }


                if (arrayComponentName != null)
                {
                    objOracleCommand.Parameters.Add("p_COMPONENT_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayComponentName;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_COMPONENT_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }



                if (arrayColorId != null)
                {
                    objOracleCommand.Parameters.Add("p_COLOR_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayColorId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_COLOR_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }


                if (arrayColorName != null)
                {
                    objOracleCommand.Parameters.Add("p_COLOR_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayColorName;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_COLOR_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }



                if (arrayFabricQuantity != null)
                {
                    objOracleCommand.Parameters.Add("p_FABRIC_QUANTITY", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayFabricQuantity;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_FABRIC_QUANTITY", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }



                if (arrayUnitId != null)
                {
                    objOracleCommand.Parameters.Add("p_UNIT_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayUnitId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_UNIT_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }


                if (arrayRemarks != null)
                {
                    objOracleCommand.Parameters.Add("p_REMARKS", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayRemarks;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_REMARKS", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }


                if (arrayTranId != null)
                {
                    objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId;
                }
                else
                {
                    objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
                }


                objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value        = objBOMModel.UpdateBy;
                objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value   = objBOMModel.HeadOfficeId;
                objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.BranchOfficeId;



                objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;

                using (OracleConnection strConn = GetConnection())
                {
                    try
                    {
                        objOracleCommand.Connection = strConn;
                        strConn.Open();
                        trans = strConn.BeginTransaction();
                        objOracleCommand.ExecuteNonQuery();
                        trans.Commit();
                        strConn.Close();


                        strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString();
                    }

                    catch (Exception ex)
                    {
                        throw new Exception("Error : " + ex.Message);
                        objOracleTransaction.Rollback();
                    }

                    finally
                    {
                        strConn.Close();
                    }
                }
            }


            return(strMsg);
        }