You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
592 lines
36 KiB
592 lines
36 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using MESClassLibrary.BLL.Log;
|
|
|
|
namespace MESClassLibrary.DAL.painting
|
|
{
|
|
public class StockInDAL
|
|
{
|
|
public DataTable SearchInfoA()
|
|
{
|
|
try
|
|
{
|
|
string sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime dateTime
|
|
|
|
IF OBJECT_ID('TEMPDB..#a') IS NOT NULL
|
|
DROP TABLE #a
|
|
create table #a (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #a(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
SELECT COUNT(*) AS c1 ,
|
|
(SELECT COUNT(*) FROM #a WHERE productInfo LIKE '%槛%' ) c2
|
|
FROM #a WHERE productInfo LIKE '%保%'";
|
|
// SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql1, null);
|
|
// string sql = @"SELECT COUNT(*) AS c1 ,
|
|
// (SELECT COUNT(*) FROM #c WHERE productInfo LIKE '%槛%' ) c2
|
|
// FROM #c WHERE productInfo LIKE '%保%' ";
|
|
// string sql = @"select count(*) as c1 ,
|
|
// (select count(*) from view_stockin where ([createtime] between convert(datetime,substring(convert(nvarchar(50),getdate(),120),1,10)+ ' 07:30:01') and convert(datetime,substring(convert(nvarchar(50),getdate(),120),1,10)+ ' 19:30:00')) and productinfo like '%槛%' and pass=1) c2
|
|
// from view_stockin where ([createtime] between convert(datetime,substring(convert(nvarchar(50),getdate(),120),1,10)+ ' 07:30:01') and convert(datetime,substring(convert(nvarchar(50),getdate(),120),1,10)+ ' 19:30:00')) and productinfo like '%保%' and pass=1 ";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoB()
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 19:30:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 23:59:59"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime dateTime
|
|
|
|
IF OBJECT_ID('TEMPDB..#c') IS NOT NULL
|
|
DROP TABLE #c
|
|
create table #c (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #c(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
--PRINT '用户ID：'+@productInfo
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
SELECT COUNT(*) AS c1 ,
|
|
(SELECT COUNT(*) FROM #c WHERE productInfo LIKE '%槛%' ) c2
|
|
FROM #c WHERE productInfo LIKE '%保%'";
|
|
// sql = @"SELECT COUNT(*) AS c1 ,
|
|
// (SELECT COUNT(*) FROM View_StockIn WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59')) AND productInfo LIKE '%槛%' and pass=1) c2
|
|
// FROM View_StockIn WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59')) AND productInfo LIKE '%保%' and pass=1";
|
|
}
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 00:00:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 07:30:00"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime dateTime
|
|
|
|
IF OBJECT_ID('TEMPDB..#c') IS NOT NULL
|
|
DROP TABLE #c
|
|
create table #c (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #c(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
--PRINT '用户ID：'+@productInfo
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
SELECT COUNT(*) AS c1 ,
|
|
(SELECT COUNT(*) FROM #c WHERE productInfo LIKE '%槛%' ) c2
|
|
FROM #c WHERE productInfo LIKE '%保%'";
|
|
// sql = @"SELECT COUNT(*) AS c1 ,
|
|
// (SELECT COUNT(*) FROM View_StockIn WHERE ([createTime] BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00')) AND productInfo LIKE '%槛%' and pass=1) c2
|
|
// FROM View_StockIn WHERE ([createTime] BETWEEN DATEADD(day,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59')) AND productInfo LIKE '%保%' and pass=1";
|
|
}
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoAByB()
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 19:30:00") && DateTime.Now <= DateTime.Parse(DateTime.Now.ToShortDateString() + " 23:59:59"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime dateTime
|
|
|
|
IF OBJECT_ID('TEMPDB..#d') IS NOT NULL
|
|
DROP TABLE #d
|
|
create table #d (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #d(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
--PRINT '用户ID：'+@productInfo
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
SELECT COUNT(*) AS c1 ,
|
|
(SELECT COUNT(*) FROM #d WHERE productInfo LIKE '%槛%' ) c2
|
|
FROM #d WHERE productInfo LIKE '%保%'";
|
|
// sql = @"SELECT COUNT(*) AS c1 ,
|
|
// (SELECT COUNT(*) FROM View_StockIn WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00')) AND productInfo LIKE '%槛%' and pass=1) c2
|
|
// FROM View_StockIn WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00')) AND productInfo LIKE '%保%' and pass=1";
|
|
}
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 00:00:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 07:30:00"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime dateTime
|
|
|
|
IF OBJECT_ID('TEMPDB..#d') IS NOT NULL
|
|
DROP TABLE #d
|
|
create table #d (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01')) AND DATEADD(day,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00')) AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #d(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
--PRINT '用户ID：'+@productInfo
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
SELECT COUNT(*) AS c1 ,
|
|
(SELECT COUNT(*) FROM #d WHERE productInfo LIKE '%槛%' ) c2
|
|
FROM #d WHERE productInfo LIKE '%保%'";
|
|
// sql = @"SELECT COUNT(*) AS c1 ,
|
|
// (SELECT COUNT(*) FROM View_StockIn WHERE ([createTime] BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01')) AND DATEADD(day,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00'))) AND productInfo LIKE '%槛%' and pass=1) c2
|
|
// FROM View_StockIn WHERE ([createTime] BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01')) AND DATEADD(day,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00'))) AND productInfo LIKE '%保%' and pass=1 ";
|
|
}
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoBByA()
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime dateTime
|
|
|
|
IF OBJECT_ID('TEMPDB..#b') IS NOT NULL
|
|
DROP TABLE #b
|
|
create table #b (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #b(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
--PRINT '用户ID：'+@productInfo
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
SELECT COUNT(*) AS c1 ,
|
|
(SELECT COUNT(*) FROM #b WHERE productInfo LIKE '%槛%' ) c2
|
|
FROM #b WHERE productInfo LIKE '%保%'";
|
|
// sql = @"SELECT COUNT(*) AS c1 ,
|
|
// (SELECT COUNT(*) FROM View_StockIn WHERE ([createTime] BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59')) AND productInfo LIKE '%槛%' and pass=1) c2
|
|
// FROM View_StockIn WHERE([createTime] BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59')) AND productInfo LIKE '%保%' and pass=1";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchDetailA()
|
|
{
|
|
try
|
|
{
|
|
string sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime DATETIME,
|
|
@totle INT ,
|
|
@ptype NVARCHAR(100),
|
|
@color NVARCHAR(100)
|
|
|
|
|
|
IF OBJECT_ID('TEMPDB..#a') IS NOT NULL
|
|
DROP TABLE #a
|
|
create table #a (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #a(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL
|
|
DROP TABLE #temp
|
|
create table #temp (id INT IDENTITY(1,1)NOT NULL, totle INT,ptype nvarchar(100),color NVARCHAR(100))
|
|
|
|
DECLARE a1 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%槛%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a1 --打开游标
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a1 --关闭游标
|
|
DEALLOCATE a1 --释放游标
|
|
|
|
|
|
DECLARE a2 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%保%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a2 --打开游标
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a2 --关闭游标
|
|
DEALLOCATE a2 --释放游标
|
|
|
|
SELECT * FROM #temp ORDER BY id ";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchDetailB()
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 19:30:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 23:59:59"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime DATETIME,
|
|
@totle INT ,
|
|
@ptype NVARCHAR(100),
|
|
@color NVARCHAR(100)
|
|
|
|
|
|
IF OBJECT_ID('TEMPDB..#a') IS NOT NULL
|
|
DROP TABLE #a
|
|
create table #a (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #a(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL
|
|
DROP TABLE #temp
|
|
create table #temp (id INT IDENTITY(1,1)NOT NULL, totle INT,ptype nvarchar(100),color NVARCHAR(100))
|
|
|
|
DECLARE a1 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%槛%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a1 --打开游标
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a1 --关闭游标
|
|
DEALLOCATE a1 --释放游标
|
|
|
|
|
|
DECLARE a2 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%保%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a2 --打开游标
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a2 --关闭游标
|
|
DEALLOCATE a2 --释放游标
|
|
|
|
SELECT * FROM #temp ORDER BY id ";
|
|
}
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 00:00:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 07:30:00"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime DATETIME,
|
|
@totle INT ,
|
|
@ptype NVARCHAR(100),
|
|
@color NVARCHAR(100)
|
|
|
|
|
|
IF OBJECT_ID('TEMPDB..#a') IS NOT NULL
|
|
DROP TABLE #a
|
|
create table #a (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #a(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL
|
|
DROP TABLE #temp
|
|
create table #temp (id INT IDENTITY(1,1)NOT NULL, totle INT,ptype nvarchar(100),color NVARCHAR(100))
|
|
|
|
DECLARE a1 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%槛%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a1 --打开游标
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a1 --关闭游标
|
|
DEALLOCATE a1 --释放游标
|
|
|
|
|
|
DECLARE a2 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%保%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a2 --打开游标
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a2 --关闭游标
|
|
DEALLOCATE a2 --释放游标
|
|
|
|
SELECT * FROM #temp ORDER BY id ";
|
|
}
|
|
|
|
if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 07:30:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 19:30:00"))
|
|
{
|
|
sql = @"DECLARE @barcode nvarchar(100),
|
|
@productInfo nvarchar(100),
|
|
@createTime DATETIME,
|
|
@totle INT ,
|
|
@ptype NVARCHAR(100),
|
|
@color NVARCHAR(100)
|
|
|
|
|
|
IF OBJECT_ID('TEMPDB..#a') IS NOT NULL
|
|
DROP TABLE #a
|
|
create table #a (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
|
|
--truncate table #c
|
|
DECLARE cursor_name CURSOR FOR --定义游标
|
|
SELECT barcode,CreateTime FROM dbo.tb_StockIn WHERE [createTime] BETWEEN DATEADD(day,-1, CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:01')) AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00') AND pass=1
|
|
ORDER BY createTime DESC
|
|
OPEN cursor_name --打开游标
|
|
FETCH NEXT FROM cursor_name INTO @barcode ,@createTime --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
|
|
|
|
insert into #a(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
|
|
FETCH NEXT FROM cursor_name INTO @barcode,@createTime
|
|
END
|
|
CLOSE cursor_name --关闭游标
|
|
DEALLOCATE cursor_name --释放游标
|
|
|
|
IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL
|
|
DROP TABLE #temp
|
|
create table #temp (id INT IDENTITY(1,1)NOT NULL, totle INT,ptype nvarchar(100),color NVARCHAR(100))
|
|
|
|
DECLARE a1 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%槛%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a1 --打开游标
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a1 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a1 --关闭游标
|
|
DEALLOCATE a1 --释放游标
|
|
|
|
|
|
DECLARE a2 CURSOR FOR --定义游标
|
|
SELECT COUNT(0) totle,SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 )) AS PType,LEFT(productInfo,CHARINDEX('，',productInfo)-1) AS color
|
|
FROM #a WHERE productInfo LIKE '%保%'
|
|
GROUP BY LEFT(productInfo,CHARINDEX('，',productInfo)-1),SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
ORDER BY SUBSTRING(productInfo, dbo.fn_find('，',productInfo,3 )+1,LEN(productInfo)-dbo.fn_find('，',productInfo,3 ))
|
|
OPEN a2 --打开游标
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color --抓取下一行游标数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
insert into #temp(totle,ptype,color) values(@totle,@ptype,@color)
|
|
FETCH NEXT FROM a2 INTO @totle ,@ptype,@color
|
|
END
|
|
CLOSE a2 --关闭游标
|
|
DEALLOCATE a2 --释放游标
|
|
|
|
SELECT * FROM #temp ORDER BY id
|
|
";
|
|
}
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchColorByBarCode(string barcode)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select top 1 paintCode from tb_StockIn where barcode='" + barcode + @"' order by createTime desc";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(),MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|