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.
324 lines
14 KiB
324 lines
14 KiB
using NFine.Data.Extensions;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Text;
|
|
using NFine.Code;
|
|
using NFine.Repository;
|
|
using System.Data.Common;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using Newtonsoft.Json;
|
|
using System.Configuration;
|
|
using System.Net;
|
|
using System.IO;
|
|
using Newtonsoft.Json.Linq;
|
|
|
|
namespace NFine.Application.OMAY
|
|
{
|
|
public class OMAYApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public DataTable GetYLAndPC(string Type ,string Group, string TimeFrom, string TimeArrive)
|
|
{
|
|
// string sql = @"
|
|
// select isnull((b.YLTR-aaa.TL),0) as YLTR,b.PCLRK from(
|
|
// select
|
|
//cast( isnull( sum( case when c.BusinessCode='13' and SUBSTRING(b.InvCode, 1, 2) IN ('01','04','05') then isnull(c.Quantity,0) else 0 end ),0)/1000 as decimal(18,2)) as YLTR ,
|
|
//cast(isnull( sum( case when c.BusinessCode='50' and b.EATTRIBUTE1='1' and SUBSTRING(b.InvCode, 1, 2) IN ('05') then isnull(c.Quantity,0) else 0 end) ,0)/1000 as decimal(18,2)) as PCLRK
|
|
// from dbo.ICSMO a
|
|
// left join dbo.ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
|
|
// inner join dbo.ICSWareHouseLotInfoLog c on a.MOCode=c.TransCode and a.Sequence+'~'+b.Sequence=c.TransSequence and c.EATTRIBUTE1<>'1'
|
|
// inner join dbo.ICSDepartment d on a.DepCode=d.DepCode
|
|
//where 1=1 and isnull(a.EATTRIBUTE7,'')<>'1' {0})b
|
|
// left join
|
|
// (select
|
|
//isnull(sum(isnull(c.Quantity,0)),0)/1000 as TL
|
|
// from dbo.ICSMO a
|
|
// left join dbo.ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
|
|
// inner join dbo.ICSWareHouseLotInfoLog c on a.MOCode=c.TransCode and a.Sequence+'~'+b.Sequence=c.TransSequence and c.EATTRIBUTE1<>'1' and c.BusinessCode='16-1'
|
|
// inner join dbo.ICSDepartment d on a.DepCode=d.DepCode
|
|
// where SUBSTRING(b.InvCode, 1, 2) IN ('01','04','05') and isnull(a.EATTRIBUTE7,'')<>'1' {0}) aaa on 1=1";
|
|
string sql = @"select isnull((b.YLTR-aaa.TL),0) as YLTR from(
|
|
select
|
|
cast(sum(isnull(c.Quantity,0)) /1000 as decimal(18,2)) as YLTR
|
|
from
|
|
ICSWareHouseLotInfoLog c
|
|
left JOIN ICSMO a ON c.TransCode=a.MOCode AND SUBSTRING(c.TransSequence, 1, CHARINDEX('~', c.TransSequence) - 1)=a.Sequence and c.WorkPoint=a.WorkPoint
|
|
where c.BusinessCode='13' and SUBSTRING(c.InvCode, 1, 2) IN ('01','04','05')
|
|
and isnull( a.EATTRIBUTE7,'')<>'1' and c.EATTRIBUTE1<>'1' {0})b
|
|
left join
|
|
(select
|
|
isnull(sum(isnull(c.Quantity,0)),0)/1000 as TL
|
|
from
|
|
ICSWareHouseLotInfoLog c
|
|
left JOIN ICSMO a ON c.TransCode=a.MOCode and
|
|
(case when c.TransSequence like '%~%'
|
|
then SUBSTRING(c.TransSequence, 1, CHARINDEX('~', c.TransSequence) - 1)
|
|
else c.TransSequence end )=a.Sequence
|
|
and c.WorkPoint=a.WorkPoint
|
|
where c.BusinessCode='16-1'
|
|
and isnull( a.EATTRIBUTE7,'')<>'1' and isnull(c.EATTRIBUTE1,'')<>'1'
|
|
and SUBSTRING(c.InvCode, 1, 2) IN ('01','04','05') {0}) aaa on 1=1
|
|
";
|
|
|
|
string where = "and LEFT(c.MUSERName,2) = '" + Type + "' and c.MUSERName = '" + Type + Group + "'";
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
where += "and left(c.MTIME,7)>='" + TimeFrom + "'";
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeArrive))
|
|
{
|
|
where += "and left(c.MTIME,7)<='" + TimeArrive + "'";
|
|
}
|
|
|
|
sql = string.Format(sql, where);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetYLAndPCLRK(string Type, string Group, string TimeFrom, string TimeArrive)
|
|
{
|
|
string sql = @" select cast (isnull(sum(a.Quantity),0)/1000 as decimal(18,2))as PCLRK
|
|
from dbo.ICSInventoryLot a
|
|
inner join dbo.ICSWareHouseLotInfo b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
|
|
--inner join dbo.ICSWareHouseLotInfoLog c on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
|
|
where a.Type='19'
|
|
and a.MUSERName= '" + Type + Group + "' ";
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
sql += "and left(b.MTIME,7)>='" + TimeFrom + "'";
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeArrive))
|
|
{
|
|
sql += "and left(b.MTIME,7)<='" + TimeArrive + "'";
|
|
}
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetCC(string Type, string Group, string TimeFrom, string TimeArrive)
|
|
{
|
|
|
|
//string sql = @" select cast( isnull( sum(isnull(b.Quantity,0)),0)/1000 as decimal(18,2)) as CC
|
|
// from dbo.ICSMO a
|
|
//inner join dbo.ICSWareHouseLotInfoLog b on a.MOCode=b.TransCode and a.Sequence=b.TransSequence and b.BusinessCode='17' and b.EATTRIBUTE1<>'1'
|
|
// inner join dbo.ICSDepartment d on a.DepCode=d.DepCode
|
|
// inner join dbo.ICSInventoryLot c on b.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
|
|
// where isnull(a.EATTRIBUTE7,'')<>'1' and LEFT(c.MUSERName,2)='" + Type + "' and c.MUSERName= '" + Type + Group + "' ";
|
|
|
|
string sql = @" select cast( isnull( sum(isnull(b.Quantity,0)),0)/1000 as decimal(18,2)) as CC
|
|
from
|
|
dbo.ICSWareHouseLotInfoLog b
|
|
left JOIN ICSMO a on a.MOCode=b.TransCode and a.Sequence=b.TransSequence
|
|
left join dbo.ICSInventoryLot c on b.LotNo=c.LotNo and b.WorkPoint=c.WorkPoint
|
|
where isnull(a.EATTRIBUTE7,'')<>'1' and b.BusinessCode='17' and b.EATTRIBUTE1<>'1' and LEFT(c.MUSERName,2)='" + Type + "' and c.MUSERName= '" + Type + Group + "' ";
|
|
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
sql += "and left(b.MTIME,7)>='" + TimeFrom + "'";
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeArrive))
|
|
{
|
|
sql += "and left(b.MTIME,7)<='" + TimeArrive + "'";
|
|
}
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetCCL7(string Type, string Group, string TimeFrom, string TimeArrive)
|
|
{
|
|
string sqlwhere = string.Empty;
|
|
// string sql = @" select
|
|
//cast( isnull( sum(isnull(e.RowCC,0)),0) as decimal(18,2)) as CC
|
|
// from dbo.ICSMO a
|
|
|
|
// left join dbo.ICSDepartment d on a.DepCode=d.DepCode
|
|
|
|
// inner join ( select a.MOCode,a.Sequence,a.WorkPoint, e.EATTRIBUTE5,e.EATTRIBUTE1,case when e.EATTRIBUTE3='平方' then isnull(b.Quantity,0) /isnull(e.EATTRIBUTE1,1)*e.EATTRIBUTE5/1000 else isnull( isnull(b.Quantity,0),0)/1000 end as RowCC from dbo.ICSMO a
|
|
//inner join dbo.ICSWareHouseLotInfoLog b on a.MOCode=b.TransCode and a.Sequence=b.TransSequence and b.BusinessCode='17'
|
|
// inner join dbo.ICSInventory e on a.InvCode=e.InvCode and a.WorkPoint=e.WorkPoint
|
|
// inner join dbo.ICSInventoryLot c on b.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
|
|
// where isnull(a.EATTRIBUTE7,'')<>'1' and c.MUSERName= '" + Type + Group + "' {0} )e on a.MOCode = e.MOCode and a.Sequence = e.Sequence and a.WorkPoint = e.WorkPoint where d.DepName = '" + Type + "' ";
|
|
string sql = @" select
|
|
cast( isnull( sum(isnull(e.RowCC,0)),0) as decimal(18,2)) as CC
|
|
from
|
|
|
|
(select a.MOCode,a.Sequence,a.WorkPoint, e.EATTRIBUTE5,e.EATTRIBUTE1,
|
|
case when e.EATTRIBUTE3='平方' then isnull(b.Quantity,0) /isnull(e.EATTRIBUTE1,1)*e.EATTRIBUTE5/1000 else isnull( isnull(b.Quantity,0),0)/1000 end as RowCC from
|
|
dbo.ICSWareHouseLotInfoLog b
|
|
left JOIN ICSMO a on a.MOCode=b.TransCode and a.Sequence=b.TransSequence
|
|
left join dbo.ICSInventory e on b.InvCode=e.InvCode and b.WorkPoint=e.WorkPoint
|
|
left join dbo.ICSInventoryLot c on b.LotNo=c.LotNo and b.WorkPoint=c.WorkPoint
|
|
where isnull(a.EATTRIBUTE7,'')<>'1' and c.MUSERName= '" + Type + Group + "' and b.BusinessCode='17' {0} ) e ";
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
sqlwhere += "and left(b.MTIME,7)>='" + TimeFrom + "'";
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeArrive))
|
|
{
|
|
sqlwhere += "and left(b.MTIME,7)<='" + TimeArrive + "'";
|
|
}
|
|
sql = string.Format(sql, sqlwhere);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
public string CreateKPIMaintain(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"IF EXISTS(SELECT * FROM dbo.ICSKPIMaintain
|
|
where Team = '{0}' and Date='{1}' and WorkPoint='{5}')
|
|
BEGIN
|
|
RAISERROR('班组:{0},已维护过{1}信息!',16,1);
|
|
RETURN
|
|
END
|
|
INSERT INTO dbo.ICSKPIMaintain
|
|
( ID ,Team,Date,Metrics ,Picking ,CreatePerson,CreateDate,MUSER,MTIME,WorkPoint )
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}','{4}',GETDATE(),'{4}',GETDATE(),'{5}')
|
|
";
|
|
sql = string.Format(sql, queryParam["Team"].ToString(), queryParam["Date"].ToString(), queryParam["Metrics"].ToString(), queryParam["Picking"].ToString(), MUSER, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string UpdateKPIMaintain(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSKPIMaintain set Team = '{0}',Date='{1}',Metrics='{2}',Picking='{3}',MTIME=GETDATE(),MUSER='{4}',WorkPoint='{5}' WHERE ID='{6}'";
|
|
sql = string.Format(sql, queryParam["Team"].ToString(), queryParam["Date"].ToString(), queryParam["Metrics"].ToString(), queryParam["Picking"].ToString(), MUSER, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string DeleteOMAYMaintain(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSKPIMaintain WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetZB(string TimeFrom, string TimeArrive)
|
|
{
|
|
var Condition = "";
|
|
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"CREATE TABLE #TempTable(Team VARCHAR(50))
|
|
INSERT INTO #TempTable ( Team)
|
|
VALUES ( 'L1'),('L2'), ( 'L3'), ('L4'),('L5'), ('L6');
|
|
|
|
select a.Team as Team,
|
|
sum(case when b.Metrics='' then 0 else cast(b.Metrics as decimal(18,2)) end )as Metrics
|
|
from #TempTable a left join ICSKPIMaintain b on a.Team=SUBSTRING(b.Team, 1, 2) {0}
|
|
group by a.Team
|
|
drop table #TempTable";
|
|
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
Condition += "and b.Date>='"+ TimeFrom + "'";
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeArrive))
|
|
{
|
|
Condition += "and b.Date<='" + TimeArrive + "'";
|
|
}
|
|
sql= string.Format(sql, Condition);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetYFFJ(string TimeFrom, string TimeArrive)
|
|
{
|
|
var Condition = "";
|
|
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" CREATE TABLE #TempTable(Team VARCHAR(50))
|
|
INSERT INTO #TempTable ( Team)
|
|
VALUES ( 'L1A'),( 'L1B'),( 'L2A'),( 'L2B'),( 'L3A'),( 'L3B'), ( 'L4A'),( 'L4B'), ( 'L5A'),( 'L5B'),( 'L6A'),( 'L6B')
|
|
|
|
select a.Team as Team,
|
|
sum(case when b.Picking='' then 0 else cast(b.Picking as decimal(18,2)) end) as Picking
|
|
from #TempTable a left join ICSKPIMaintain b on a.Team=b.Team {0}
|
|
group by a.Team
|
|
|
|
drop table #TempTable";
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
Condition += "and b.Date>='" + TimeFrom + "'";
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeArrive))
|
|
{
|
|
Condition += "and b.Date<='" + TimeArrive + "'";
|
|
}
|
|
sql = string.Format(sql, Condition);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
}
|