177 lines
10 KiB
C#
177 lines
10 KiB
C#
using Aspose.Words;
|
|
using BLL;
|
|
using Microsoft.Office.Interop.Excel;
|
|
using Model;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Web.Http;
|
|
using static WebAPI.Controllers.HSSE.ImageRecognitionController;
|
|
|
|
|
|
namespace WebAPI.Controllers
|
|
{
|
|
/*
|
|
* 物资管理的一些基础信息
|
|
* **/
|
|
public class MaterialsBasicsController : ApiController
|
|
{
|
|
#region 获取单位列表
|
|
public Model.ResponeData GetUnitList(string projectId, string keyWord = "" )
|
|
{
|
|
Model.ResponeData responeData = new Model.ResponeData();
|
|
try
|
|
{
|
|
if (string.IsNullOrEmpty(keyWord))
|
|
{
|
|
keyWord = ""; // 明确使用默认值
|
|
}
|
|
var list = Funs.DB.Cl_b_unitapp.Where(x => x.ProjectId == projectId && x.UAName.Contains(keyWord)).ToList();
|
|
responeData.data = list;
|
|
|
|
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
responeData.code = 0;
|
|
responeData.message = ex.Message;
|
|
}
|
|
return responeData;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取专业
|
|
public Model.ResponeData getInstallationList(string projectId, string keyWord = "")
|
|
{
|
|
Model.ResponeData responeData = new Model.ResponeData();
|
|
try
|
|
{
|
|
if (string.IsNullOrEmpty(keyWord))
|
|
{
|
|
keyWord = ""; // 明确使用默认值
|
|
}
|
|
responeData.data = Funs.DB.Cl_b_Installation.Where(x => x.ProjectId == projectId&&x.InstallationName.Contains(keyWord))
|
|
.Select(x => new {
|
|
x.InstallationId,
|
|
x.InstallationCode,
|
|
x.InstallationName,
|
|
}).ToList();
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
responeData.code = 0;
|
|
responeData.message = ex.Message;
|
|
}
|
|
return responeData;
|
|
}
|
|
#endregion
|
|
|
|
#region 物资工区
|
|
public Model.ResponeData getSpecialityList(string projectId, string keyWord = "")
|
|
{
|
|
Model.ResponeData responeData = new Model.ResponeData();
|
|
try
|
|
{
|
|
if (string.IsNullOrEmpty(keyWord))
|
|
{
|
|
keyWord = ""; // 明确使用默认值
|
|
}
|
|
responeData.data = Funs.DB.CL_B_Speciality.Where(x => x.ProjectId == projectId && x.SpecialityName.Contains(keyWord))
|
|
.Select(x => new {
|
|
x.Specialityid,
|
|
x.SpecialityCode,
|
|
x.SpecialityName
|
|
}).ToList();
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
responeData.code = 0;
|
|
responeData.message = ex.Message;
|
|
}
|
|
return responeData;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取材料明细
|
|
public Model.ResponeData GetMaterialsBySystemCode(string ProjectId, string InstallationId, string ghfs, string area, string code)
|
|
{
|
|
Model.ResponeData responeData = new Model.ResponeData();
|
|
try
|
|
{
|
|
string strSql = "SELECT cp.ProjectId,cp.InstallationId,zz.Installationname AS InstallationName,ghfs AS Ghfs,cp.MaterialID,MaterialName,specificationtype AS Specificationtype,MaterialQuality,standardspecification ,unit AS Unit," +
|
|
"technicalconditions AS TechnicalConditions,SystemCode,rtrim( ltrim( TAreaMaterialMID ) ) AS AreaCode,0.000 AS bcckl,SUM ( Aquantity ) AS Aquantity,SUM ( Pquantity ) AS jh_quantity,SUM ( SQuantity ) AS dj_quantity,SUM ( HQuantity ) AS hj_quantity,SUM ( Pquantity ) - SUM ( OQuantity ) AS ck_quantity,SUM ( RQuantity ) AS tk_quantity,(SELECT SUM( SQuantity ) - SUM ( OQuantity ) + SUM ( RQuantity ) - SUM ( HQuantity ) FROM cl_w_comp WHERE cl_w_comp.MaterialID= cp.MaterialID AND cl_w_comp.InstallationId= cp.InstallationId AND cl_w_comp.ghfs= cp.ghfs ) AS kc_quantity,ROW_NUMBER ( ) OVER ( ORDER BY zz.InstallationCode,SystemCode ASC ) AS RowNum,0 AS xz,0 AS xh FROM cl_w_comp cp LEFT JOIN CL_B_Material mt ON mt.MaterialID= cp.MaterialID LEFT JOIN cl_b_Installation zz ON zz.InstallationId= cp.InstallationId GROUP BY cp.ProjectId,cp.InstallationId,zz.InstallationCode,zz.Installationname,ghfs,cp.MaterialID,MaterialName,specificationtype,MaterialQuality,standardspecification,unit,technicalconditions,SystemCode,TAreaMaterialMID HAVING ";
|
|
List<SqlParameter> listStr = new List<SqlParameter>();
|
|
strSql += "cp.ProjectId= @ProjectId AND cp.installationid= @InstallationId AND TAreaMaterialMID = @area AND ghfs = @ghfs AND SystemCode = @code";
|
|
listStr.Add(new SqlParameter("@ProjectId", ProjectId));
|
|
listStr.Add(new SqlParameter("@InstallationId", InstallationId));
|
|
listStr.Add(new SqlParameter("@ghfs", ghfs));
|
|
listStr.Add(new SqlParameter("@area", area));
|
|
listStr.Add(new SqlParameter("@code", code));
|
|
SqlParameter[] parameter = listStr.ToArray();
|
|
System.Data.DataTable tb = SQLHelper.GetDataTableRunText(strSql, parameter);
|
|
responeData.data = tb;
|
|
//responeData.data = (
|
|
//from bm in Funs.DB.CL_B_Material
|
|
//where bm.SystemCode == code && bm.ProjectId == ProjectId
|
|
//select new
|
|
//{
|
|
// ProjectId = bm.ProjectId, // 项目ID
|
|
// InstallationId = Funs.DB.Cl_w_comp.FirstOrDefault(x => x.MaterialID == bm.MaterialID).InstallationId, // 专业ID
|
|
// Installationname = Funs.DB.Cl_b_Installation.FirstOrDefault(x => x.InstallationId == Funs.DB.Cl_w_comp.FirstOrDefault(y => y.MaterialID == bm.MaterialID).InstallationId).InstallationName, // 专业名称
|
|
// ghfs = Funs.DB.Cl_w_comp.FirstOrDefault(x => x.MaterialID == bm.MaterialID).Ghfs, // 采购方类别
|
|
// MaterialID = bm.MaterialID, // 物资ID
|
|
// MaterialName = bm.MaterialName, // 物资名称
|
|
// Specificationtype = bm.Specificationtype, // 规格
|
|
// TechnicalConditions = bm.TechnicalConditions,
|
|
// StandardSpecification = bm.StandardSpecification,
|
|
// MaterialQuality = bm.MaterialQuality,
|
|
// Unit = bm.Unit, // 单位
|
|
// SystemCode = bm.SystemCode, // 编号
|
|
// AreaCode = Funs.DB.Cl_w_comp.FirstOrDefault(x => x.MaterialID == bm.MaterialID).TAreaMaterialMID, // 工区
|
|
// jh_quantity = Funs.DB.Cl_w_comp.Where(c => c.MaterialID == bm.MaterialID).Sum(c => c.Pquantity), // 计划量
|
|
// ck_quantity = Funs.DB.Cl_w_comp.Where(c => c.MaterialID == bm.MaterialID).Sum(c => c.Pquantity) - Funs.DB.Cl_w_comp.Where(x => x.MaterialID == bm.MaterialID).Sum(c => c.OQuantity), // 最大允许出库量
|
|
// kc_quantity = Funs.DB.Cl_w_comp.Where(x => x.MaterialID == bm.MaterialID).Sum(c => c.SQuantity) - Funs.DB.Cl_w_comp.Where(x => x.MaterialID == bm.MaterialID).Sum(c => c.OQuantity) + Funs.DB.Cl_w_comp.Where(x => x.MaterialID == bm.MaterialID).Sum(c => c.RQuantity) - Funs.DB.Cl_w_comp.Where(x => x.MaterialID == bm.MaterialID).Sum(c => c.HQuantity) // 库存量
|
|
//}
|
|
//).FirstOrDefault(x => x.SystemCode == code);
|
|
|
|
|
|
}
|
|
catch ( System.Exception ex)
|
|
{
|
|
responeData.code = 0;
|
|
responeData.message = ex.Message;
|
|
}
|
|
return responeData;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取材料列表
|
|
public Model.ResponeData GetMaterialsList(string ProjectId, string InstallationId, string ghfs, string area)
|
|
{
|
|
Model.ResponeData responeData = new Model.ResponeData();
|
|
try
|
|
{
|
|
|
|
string strSql = "SELECT cp.ProjectId,cp.InstallationId,zz.Installationname AS InstallationName,ghfs AS Ghfs,cp.MaterialID,MaterialName,specificationtype AS Specificationtype,MaterialQuality,standardspecification ,unit AS Unit," +
|
|
"technicalconditions AS TechnicalConditions,SystemCode,rtrim( ltrim( TAreaMaterialMID ) ) AS AreaCode,0.000 AS bcckl,SUM ( Aquantity ) AS Aquantity,SUM ( Pquantity ) AS jh_quantity,SUM ( SQuantity ) AS dj_quantity,SUM ( HQuantity ) AS hj_quantity,SUM ( Pquantity ) - SUM ( OQuantity ) AS ck_quantity,SUM ( RQuantity ) AS tk_quantity,(SELECT SUM( SQuantity ) - SUM ( OQuantity ) + SUM ( RQuantity ) - SUM ( HQuantity ) FROM cl_w_comp WHERE cl_w_comp.MaterialID= cp.MaterialID AND cl_w_comp.InstallationId= cp.InstallationId AND cl_w_comp.ghfs= cp.ghfs ) AS kc_quantity,ROW_NUMBER ( ) OVER ( ORDER BY zz.InstallationCode,SystemCode ASC ) AS RowNum,0 AS xz,0 AS xh FROM cl_w_comp cp LEFT JOIN CL_B_Material mt ON mt.MaterialID= cp.MaterialID LEFT JOIN cl_b_Installation zz ON zz.InstallationId= cp.InstallationId GROUP BY cp.ProjectId,cp.InstallationId,zz.InstallationCode,zz.Installationname,ghfs,cp.MaterialID,MaterialName,specificationtype,MaterialQuality,standardspecification,unit,technicalconditions,SystemCode,TAreaMaterialMID HAVING " ;
|
|
List<SqlParameter> listStr = new List<SqlParameter>();
|
|
strSql += "cp.ProjectId= @ProjectId AND cp.installationid= @InstallationId AND TAreaMaterialMID = @area AND ghfs = @ghfs";
|
|
listStr.Add(new SqlParameter("@ProjectId", ProjectId));
|
|
listStr.Add(new SqlParameter("@InstallationId", InstallationId));
|
|
listStr.Add(new SqlParameter("@ghfs", ghfs));
|
|
listStr.Add(new SqlParameter("@area", area));
|
|
SqlParameter[] parameter = listStr.ToArray();
|
|
System.Data.DataTable tb = SQLHelper.GetDataTableRunText(strSql, parameter);
|
|
responeData.data = tb;
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
responeData.code = 0;
|
|
responeData.message = ex.Message;
|
|
}
|
|
return responeData;
|
|
}
|
|
#endregion
|
|
}
|
|
} |