From 4458b932fa0fdb5dfbda8416a30a12620b33b5d9 Mon Sep 17 00:00:00 2001
From: ouyangqing <ouyangqing@DESKTOP-B85SG1D>
Date: 星期二, 26 一月 2021 11:18:21 +0800
Subject: [PATCH] sql 优化
---
WebAPI/Models/StationOutBillView.cs | 1
WebAPI/Service/YqnQbService.cs | 71 ++++++++++++++++++-----------------
WebAPI/Web References/WebS/Reference.cs | 2
3 files changed, 38 insertions(+), 36 deletions(-)
diff --git a/WebAPI/Models/StationOutBillView.cs b/WebAPI/Models/StationOutBillView.cs
index 963cf48..8a2e4ac 100644
--- a/WebAPI/Models/StationOutBillView.cs
+++ b/WebAPI/Models/StationOutBillView.cs
@@ -40,5 +40,6 @@
public string HBillNo { get; set; }
public string HRemark { get; set; }
public int HBillStatus { get; set; }
+ public int HProcNo { get; set; }
}
}
\ No newline at end of file
diff --git a/WebAPI/Service/YqnQbService.cs b/WebAPI/Service/YqnQbService.cs
index 1cda2af..c6feccf 100644
--- a/WebAPI/Service/YqnQbService.cs
+++ b/WebAPI/Service/YqnQbService.cs
@@ -4,6 +4,7 @@
using System.Collections.Generic;
using System.Data;
using System.Linq;
+using System.Text;
using System.Web;
using WebAPI.Dapper;
using WebAPI.Models;
@@ -102,14 +103,14 @@
{
var stationOutList = YqnDal.GetStationOutBillMailList(oMain.HICMOBillNo);//鑾峰緱璇ユ祦杞崱鎵�鏈夊嚭绔欏崟
var stationInModel = stationInList.OrderByDescending(a => a.HDate).First();//鑾峰彇杩涚珯鍗曟渶鏂拌繘绔欏伐搴�
- if (stationOutList.Count == 0 && oMain.HEmpID == stationInModel.HEmpID)
+ if (stationOutList.Count == 0 && oMain.HProcID == stationInModel.HProcID)
return new ApiResult { code = -1, msg = "閲嶅鎵弿" };
- if (stationOutList.Count == 0 && oMain.HEmpID != stationInModel.HEmpID)
+ if (stationOutList.Count == 0 && oMain.HProcID != stationInModel.HProcID)
return new ApiResult { code = -1, msg = "姝ゅ崟宸插叧鑱�" };
var stationOutModel = stationOutList.OrderByDescending(a => a.HDate).First();//鑾峰彇鍑虹珯鍗曟渶鏂板嚭绔欏伐搴�
- if (stationInModel.HProcID != stationOutModel.HProcID && stationInModel.HEmpID == oMain.HEmpID)
+ if (stationInModel.HProcID != stationOutModel.HProcID && stationInModel.HProcID == oMain.HProcID)
return new ApiResult { code = -1, msg = "閲嶅鎵弿" };
- if (stationInModel.HProcID != stationOutModel.HProcID && stationInModel.HEmpID == oMain.HEmpID)
+ if (stationInModel.HProcID != stationOutModel.HProcID && stationInModel.HProcID == oMain.HProcID)
return new ApiResult { code = -1, msg = "姝ゅ崟宸插叧鑱�" };
if (stationOutList.Exists(a => a.HProcID == oMain.HProcID))
return new ApiResult { code = -1, msg = "璇ュ崟鎹凡瀹屾垚姝ゅ伐鑹�" };
@@ -119,7 +120,6 @@
var result = oWebs.set_SaveStationInBill(oMain, ref sErrMsg);//杩涚珯
if (!result)
{
-
return new ApiResult { code = -1, msg = sErrMsg };
}
return new ApiResult { code = 1, msg = "鎿嶄綔鎴愬姛" };
@@ -186,7 +186,7 @@
/// </summary>
public static ApiResult SetStationOutBill(StationOutBillView omodel)
{
- if (!dicLock.TryAdd(omodel.HBillNo, omodel.HBillNo))
+ if (!dicLock.TryAdd(omodel.HBillNo, omodel.HBillNo))//鍚屼竴鍗曞彿骞跺彂澶勭悊
return new ApiResult { code = -1, msg = "鏈嶅姟鍣ㄧ箒蹇�" };
var stationInBillModel = YqnDal.GetStationInBillMail(omodel.HProcID, omodel.HICMOBillNo);
if (stationInBillModel == null)
@@ -194,8 +194,7 @@
var stationOutBillModel = YqnDal.GetStationOutBillMail(omodel.HProcID, omodel.HICMOBillNo);
if (stationOutBillModel != null)
return new ApiResult { code = -1, msg = "璇ュ伐搴忓凡瀛樺湪鍑虹珯鍗�" };
- WebS.WebService1 oWebs = new WebS.WebService1();
- string sErrMsg = string.Empty;
+ omodel.HYear = DateTime.Now.Year;
var result = SetOutBill(omodel);
return result;
}
@@ -271,22 +270,23 @@
{
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
omodel.HInterID = DBUtility.ClsPub.CreateBillID("3791", ref DBUtility.ClsPub.sExeReturnInfo);
- oCN.BeginTran();
- oCN.RunProc("Insert Into Sc_StationOutBillMain " +
+ //oCN.BeginTran();
+ var sql = "Insert Into Sc_StationOutBillMain " +
"(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate" +
",HYear,HPeriod,HRemark" +
",HICMOInterID,HICMOBillNo,HProcPlanInterID,HProcPlanEntryID,HProcPlanBillNo,HProcExchInterID,HProcExchEntryID" +
",HProcExchBillNo,HMaterID,HProcID,HICMOQty,HPlanQty,HStationOutTime,HSourceID" +
",HGroupID,HDeptID,HEmpID,HBarCode,HAddr,HBarCodeMaker,HBarCodeMakeDate" +
- ",HSupID,HQty,HPrice,HMoney" +
+ ",HSupID,HQty,HPrice,HMoney,HProcNo" +
") " +
" values('3791','3791'," + omodel.HInterID.ToString() + ",'" + omodel.HBillNo + "'," + omodel.HBillStatus.ToString() + ",getdate(),'" + DBUtility.ClsPub.CurUserName + "',getdate()" +
"," + omodel.HYear.ToString() + "," + omodel.HPeriod.ToString() + ",'" + omodel.HRemark + "'" +
"," + omodel.HICMOInterID.ToString() + ",'" + omodel.HICMOBillNo + "'," + omodel.HProcPlanInterID.ToString() + "," + omodel.HProcPlanEntryID.ToString() + ",'" + omodel.HProcPlanBillNo + "'," + omodel.HProcExchInterID.ToString() + "," + omodel.HProcExchEntryID.ToString() +
",'" + omodel.HProcExchBillNo + "'," + omodel.HMaterID.ToString() + "," + omodel.HProcID.ToString() + "," + omodel.HICMOQty.ToString() + "," + omodel.HPlanQty.ToString() + ",getdate()," + omodel.HSourceID.ToString() +
"," + omodel.HGroupID.ToString() + "," + omodel.HDeptID.ToString() + "," + omodel.HEmpID.ToString() + ",'" + omodel.HBarCode + "','" + omodel.HAddr + "','" + omodel.HBarCodeMaker + "',getdate()" +
- "," + omodel.HSupID.ToString() + "," + omodel.HQty.ToString() + "," + omodel.HPrice.ToString() + "," + omodel.HMoney.ToString() +
- ") ");
+ "," + omodel.HSupID.ToString() + "," + omodel.HQty.ToString() + "," + omodel.HPrice.ToString() + "," + omodel.HMoney.ToString() +"," + omodel.HProcNo + ") ";
+ //var result= SqlPools.GetInstance("YqnConn").ExecuteCommand(sql);
+ oCN.RunProc(sql);
oCN.Commit();
return new ApiResult { code = 1, msg = "鏂板鎴愬姛" };
}
@@ -297,36 +297,37 @@
}
public static ApiResult SetInBill(ClsSc_StationInBillMain omodel)
{
- SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
+ //SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
try
{
+ var nowTime = DateTime.Now;
+ omodel.HBillType = "3790";
+ omodel.HYear = nowTime.Year;
+ omodel.HBillSubType = "3790";
//寰楀埌mainid
- omodel.HInterID = DBUtility.ClsPub.CreateBillID("3790", ref DBUtility.ClsPub.sExeReturnInfo);
+ omodel.HInterID = DBUtility.ClsPub.CreateBillID(omodel.HBillType, ref DBUtility.ClsPub.sExeReturnInfo);
+ omodel.HDate = nowTime;
+ omodel.HMakeDate = nowTime.ToString();
+ omodel.HStationInTime = nowTime;
+ omodel.HBarCodeMakeDate = nowTime;
+ omodel.HPeriod = 1;
+ var sqlBuilder = new StringBuilder();
+ sqlBuilder.Append("Insert Into Sc_StationInBillMain(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate,HYear,HPeriod,HRemark,HICMOInterID,HICMOBillNo");
+ sqlBuilder.Append(",HProcPlanInterID,HProcPlanEntryID,HProcPlanBillNo,HProcExchInterID,HProcExchEntryID,HProcExchBillNo,HMaterID,HProcID,HICMOQty,HPlanQty,HStationInTime,HSourceID");
+ sqlBuilder.Append(",HGroupID,HDeptID,HEmpID,HBarCode,HAddr,HBarCodeMaker,HBarCodeMakeDate,HSupID,HQty,HPrice,HMoney,HProcId,HProcNo) values ");
+ sqlBuilder.Append("(@HBillType,@HBillSubType,@HInterID,@HBillNo,@HBillStatus,@HDate,@HMaker,@HMakeDate,@HYear,@HPeriod,@HRemark,@HICMOInterID,@HICMOBillNo");
+ sqlBuilder.Append(",@HProcPlanInterID,@HProcPlanEntryID,@HProcPlanBillNo,@HProcExchInterID,@HProcExchEntryID,@HProcExchBillNo,@HMaterID,@HProcID,@HICMOQty,@HPlanQty,@HStationInTime,@HSourceID");
+ sqlBuilder.Append(",@HGroupID,@HDeptID,@HEmpID,@HBarCode,@HAddr,@HBarCodeMaker,@HBarCodeMakeDate,@HSupID,@HQty,@HPrice,@HMoney,@HProcId,@HProcNo)");
//鑻AINDI閲嶅鍒欓噸鏂拌幏鍙�
- oCN.BeginTran();
- //涓昏〃
- oCN.RunProc("Insert Into Sc_StationInBillMain " +
- "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate" +
- ",HYear,HPeriod,HRemark" +
- ",HICMOInterID,HICMOBillNo,HProcPlanInterID,HProcPlanEntryID,HProcPlanBillNo,HProcExchInterID,HProcExchEntryID" +
- ",HProcExchBillNo,HMaterID,HProcID,HICMOQty,HPlanQty,HStationInTime,HSourceID" +
- ",HGroupID,HDeptID,HEmpID,HBarCode,HAddr,HBarCodeMaker,HBarCodeMakeDate" +
- ",HSupID,HQty,HPrice,HMoney,HMaker" +
- ") " +
- " values('3790','3790'," + omodel.HInterID.ToString() + ",'" + omodel.HBillNo + "'," + omodel.HBillStatus.ToString() + ",getdate(),'" + DBUtility.ClsPub.CurUserName + "',getdate()" +
- "," + omodel.HYear.ToString() + "," + omodel.HPeriod.ToString() + ",'" + omodel.HRemark + "'" +
- "," + omodel.HICMOInterID.ToString() + ",'" + omodel.HICMOBillNo + "'," + omodel.HProcPlanInterID.ToString() + "," + omodel.HProcPlanEntryID.ToString() + ",'" + omodel.HProcPlanBillNo + "'," + omodel.HProcExchInterID.ToString() + "," + omodel.HProcExchEntryID.ToString() +
- ",'" + omodel.HProcExchBillNo + "'," + omodel.HMaterID.ToString() + "," + omodel.HProcID.ToString() + "," + omodel.HICMOQty.ToString() + "," + omodel.HPlanQty.ToString() + ",getdate()," + omodel.HSourceID.ToString() +
- "," + omodel.HGroupID.ToString() + "," + omodel.HDeptID.ToString() + "," + omodel.HEmpID.ToString() + ",'" + omodel.HBarCode + "','" + omodel.HAddr + "','" + omodel.HBarCodeMaker + "',getdate()" +
- "," + omodel.HSupID.ToString() + "," + omodel.HQty.ToString() + "," + omodel.HPrice.ToString() + "," + omodel.HMoney.ToString() +
- ",'"+omodel.HMaker+"') ");
-
- oCN.Commit();
+ //oCN.BeginTran();
+ var result = SqlPools.GetInstance("YqnConn").ExecuteCommand(sqlBuilder.ToString(), omodel);
+ //oCN.RunProc(sql);
+ //oCN.Commit();
return new ApiResult { code = 1, msg = "鏂板鎴愬姛" };
}
catch (Exception e)
{
- oCN.RollBack();
+ //oCN.RollBack();
return new ApiResult { code = -1, msg = "淇濆瓨閿欒/" + e.ToString() };
}
}
diff --git a/WebAPI/Web References/WebS/Reference.cs b/WebAPI/Web References/WebS/Reference.cs
index 88cfa37..bcead3e 100644
--- a/WebAPI/Web References/WebS/Reference.cs
+++ b/WebAPI/Web References/WebS/Reference.cs
@@ -30065,7 +30065,7 @@
//[System.ComponentModel.DesignerCategoryAttribute("code")]
//[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://tempuri.org/")]
public partial class ClsSc_StationInBillMain : ClsXt_BaseBillMain {
-
+ public int HProcNo { get; set; }
private long hPrintQtyField;
private long hICMOInterIDField;
--
Gitblit v1.9.1