OMAY_WMS/WMS-BS/NFine.Application/OMAY/OMAYApp.cs

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;
        }


  





    }
}