询价单，报价单，excel生成送货单添加字段

yusijie

2023-08-03 7314aa8ffbe86ecc8af1a2c5f00407ab9b5a8916

询价单，报价单，excel生成送货单添加字段

 WebAPI/Controllers/AskPriceBillController.cs

New file
@@ -0,0 +1,66 @@
using DBUtility;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SQLHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using WebAPI.Code;
using WebAPI.Models;
namespace WebAPI.Controllers
{
    public class AskPriceBillController : ApiController
    {
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;
        private json objJsonResult = new json();
        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        public DAL.ClsSc_ICMOBill BillOld = new DAL.ClsSc_ICMOBill();
        DataSet ds;

        #region 供应商询价单列表
        [Route("Sup_AskPriceBill/Sup_AskPriceBillList")]
        [HttpGet]
        public object Sup_AskPriceBillList(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();

                string sql1 = "select * from h_v_Sup_AskPriceBill where 1 = 1  ";
                string sql = sql1 + sWhere + " order by hmainid desc";
                ds = oCN.RunProcReturn(sql, "h_v_Sup_AskPriceBill");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.list = columnNameList;
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

    }
}

 WebAPI/Controllers/POInStockController.cs

@@ -774,13 +774,13 @@
                    }
                    else
                    {
                          //瑞琪字段 ,B.HExpressNumberSub,B.HExpressSupplierSub,B.HRemark
                          //瑞琪字段
                          sql = string.Format(@"
SELECT T3.FNAME AS FTYPENAME,M.HBILLNO,B.HINTERID,B.HENTRYID,C.FID,C.FENTRYID, D.FBILLNO,B.HQTY,CD.FRECEIVEORGID,CDOO.FNUMBER AS FRECEIVENUMBER
,D.FSUPPLIERID,DS.FNUMBER AS FSUPPLIERNUMBER,D.FPURCHASEORGID,DOO.FNUMBER AS FPURCHASEORGNUMBER,CROO.FNUMBER  AS FSETTLEORGNUMBER,
E.FPRICETIMEPOINT,E.FSETTLECURRID,EC.FNUMBER AS FSETTLECURRNUMBER,C.FMATERIALID,CM.FNUMBER AS FMATERIALNUMBER ,C.FUNITID 
,CU.FNUMBER AS FUNITNUMBER,CR.FPRICE,CR.FTAXPRICE,CR.FTAXRATE,D.FPURCHASERID,isnull(T4.FNUMBER,'') AS FPURCHASERNUMBER

,B.HExpressNumberSub,B.HExpressSupplierSub,B.HRemark,B.HPlanArrivalDate,B.HBoxNumber
FROM CG_POINSTOCKBILLSUB B  
JOIN CG_POINSTOCKBILLMAIN M ON B.HINTERID = M.HINTERID
JOIN {0}..T_PUR_POORDERENTRY C ON B.HSOURCEINTERID = C.FID AND B.HSOURCEENTRYID = C.FENTRYID
@@ -864,9 +864,11 @@
                                    FentityModel.Add("FTaxPrice", item["FTAXPRICE"].ToString());//含税单价
                                    FentityModel.Add("FEntryTaxRate", item["FTAXRATE"].ToString());//税率

                                    //FentityModel.Add("F_bsv_Text1", item["HExpressSupplierSub"].ToString());//物流单位 (瑞琪)
                                    //FentityModel.Add("F_bsv_Text2", item["HExpressNumberSub"].ToString());//物流单号 (瑞琪)
                                    //FentityModel.Add("FDescription", item["HRemark"].ToString());//表体备注 (瑞琪)
                                    FentityModel.Add("F_bsv_Text1", item["HExpressSupplierSub"].ToString());//物流单位 (瑞琪)
                                    FentityModel.Add("F_bsv_Text2", item["HExpressNumberSub"].ToString());//物流单号 (瑞琪)
                                    FentityModel.Add("FDescription", item["HRemark"].ToString());//表体备注 (瑞琪)
                                    FentityModel.Add("F_bsv_dhrq", item["HPlanArrivalDate"].ToString());//到货日期 (瑞琪)
                                    FentityModel.Add("F_bsv_xh", item["HBoxNumber"].ToString());//箱号 (瑞琪)

                                    JArray Fentity2 = new JArray();
                                    JObject FentityModel2 = new JObject();

 WebAPI/Controllers/POOrderBillExcelController.cs

@@ -127,6 +127,18 @@
                if (!tb2.Columns.Contains("包装标识"))
                    error += "没有找到【包装标识】的标题,";

                if (!tb2.Columns.Contains("到货日期"))
                    error += "没有找到【到货日期】的标题,";

                if (!tb2.Columns.Contains("物流单号"))
                    error += "没有找到【物流单号】的标题,";

                if (!tb2.Columns.Contains("物流公司"))
                    error += "没有找到【物流公司】的标题,";

                if (!tb2.Columns.Contains("箱号"))
                    error += "没有找到【箱号】的标题,";

                if (error.Length > 0)
                {
                    objJsonResult.code = "0";
@@ -141,14 +153,14 @@
                {
                    for (int j = 0; j < tb2.Columns.Count; j++)
                    {
                        if (tb2.Rows[i][j].ToString() == ""&& tb2.Columns[j].ToString()!= "包装标识")
                        if (tb2.Rows[i][j].ToString() == ""&& tb2.Columns[j].ToString()!= "包装标识" && tb2.Columns[j].ToString() != "到货日期" && tb2.Columns[j].ToString() != "物流单号" && tb2.Columns[j].ToString() != "物流公司" && tb2.Columns[j].ToString() != "箱号")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = $"Excel模板存在错误,第{(i + 1)}行第{(j + 1)}列值不能为空!\r\n";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        }                        
                    }
                }

@@ -164,9 +176,11 @@
                    for (int i = 0; i < tb2.Rows.Count; i++)
                    {
                        oCn.RunProc("insert into POOrderBill_Excel(HItemID,HORGName, SupplierName, Warehouse_Shou," +
                            " MaterialNum, HQty, HPackFlag)values" +
                            " MaterialNum, HQty, HPackFlag, HArrivalDate, HExpressNumberSub, HExpressSupplierSub, HBoxNumber)values" +
                            $"({(i + 1)},'{tb2.Rows[i]["组织"].ToString()}', '{tb2.Rows[i]["供应商"].ToString()}', '{tb2.Rows[i]["收料仓库"].ToString()}', " +
                            $"'{tb2.Rows[i]["物料编码"].ToString()}', {tb2.Rows[i]["数量"].ToString()}, '{tb2.Rows[i]["包装标识"].ToString()}')");
                            $"'{tb2.Rows[i]["物料编码"].ToString()}', {tb2.Rows[i]["数量"].ToString()}, '{tb2.Rows[i]["包装标识"].ToString()}'," +
                            $"'{tb2.Rows[i]["到货日期"].ToString()}','{tb2.Rows[i]["物流单号"].ToString()}','{tb2.Rows[i]["物流公司"].ToString()}'," +
                            $"'{tb2.Rows[i]["箱号"].ToString()}')");
                    }

                    objJsonResult.code = "1";

 WebAPI/Controllers/QuotePriceBilController.cs

New file
@@ -0,0 +1,66 @@
using DBUtility;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SQLHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using WebAPI.Code;
using WebAPI.Models;
namespace WebAPI.Controllers
{
    public class QuotePriceBilController : ApiController
    {
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;
        private json objJsonResult = new json();
        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        public DAL.ClsSc_ICMOBill BillOld = new DAL.ClsSc_ICMOBill();
        DataSet ds;

        #region 供应商报价单列表
        [Route("Sup_QuotePriceBill/Sup_QuotePriceBillList")]
        [HttpGet]
        public object Sup_QuotePriceBillList(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();

                string sql1 = "select * from h_v_Sup_QuotePriceBill where 1 = 1  ";
                string sql = sql1 + sWhere + " order by hmainid desc";
                ds = oCN.RunProcReturn(sql, "h_v_Sup_QuotePriceBill");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.list = columnNameList;
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

    }
}

 WebAPI/DLL/DAL.dll

Binary files differ

 WebAPI/DLL/Model.dll

Binary files differ

 WebAPI/WebAPI.csproj

@@ -210,6 +210,7 @@
    <Compile Include="Code\Common.cs" />
    <Compile Include="Code\Test2.cs" />
    <Compile Include="Code\TestDAL.cs" />
    <Compile Include="Controllers\QuotePriceBilController.cs" />
    <Compile Include="Controllers\BarCodeController.cs" />
    <Compile Include="Controllers\NpoiHelper.cs" />
    <Compile Include="Controllers\POInStockBackController.cs" />
@@ -217,6 +218,7 @@
    <Compile Include="Controllers\PODemandPlanController.cs" />
    <Compile Include="Controllers\EntrustOrderController.cs" />
    <Compile Include="Controllers\POOrderBillExcelController.cs" />
    <Compile Include="Controllers\AskPriceBillController.cs" />
    <Compile Include="Controllers\POOrderController.cs" />
    <Compile Include="Controllers\PublicPageMethodController.cs" />
    <Compile Include="Controllers\ReportController.cs" />