using NFine.Code;
using NFine.Domain.Entity.SystemManage;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using NFine.Repository;
using System.Text;
using System;
using System.Configuration;
using NFine.Data;
using System.IO;
using Newtonsoft.Json;
using NFine.Domain._02_ViewModel;
using NFine.Data.Extensions;
using Newtonsoft.Json.Linq;

namespace NFine.Application.SRM
{
    public class WatchPanelApp : RepositoryFactory<ModuleEntity>
    {
        //十天生产订单开工&生产汇总
        public DataTable GetList1()
        {
            string ORGCode = ConfigurationManager.ConnectionStrings["ORGCode"].ConnectionString;

            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"

 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempdatePerr'))
			 drop table #TempdatePerr 

 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOWIPQty'))
			 drop table #TempMOWIPQty 

declare   @DateTimeZZ datetime=CONVERT(varchar(10),dateadd(day,-20, getdate()), 21) + ' 00:00:00' 

			 
	select *
 into #TempMOWIPQty
from (
	
select  mo.StartDate 开工开始日期,mo.docno,  min(rr.CompleteDate)  生产开始日期,
case when mo.ClosedOn is not null and  min(rr.CompleteDate)  is not null then mo.ClosedOn 
else (case when  min(rr.CompleteDate)  is not null then getdate() else null end) end   生产完成日期
,case when mo.ClosedOn is not null then mo.ClosedOn 
else   getdate()   end  开工完成日期

from mo_mo mo with(nolock)  
left join base_organization oo with(nolock) on   mo.org=oo.id
left join MO_CompleteRpt rr   with(nolock) on rr.mo=mo.id
where  mo.DocState in (2,3) and oo.code='" + ORGCode + @"'
group by mo.docno, mo.StartDate , mo.ClosedOn
) lll

where  (开工开始日期<=@DateTimeZZ and 开工完成日期 >=@DateTimeZZ )
or  (生产开始日期<=@DateTimeZZ  and 生产完成日期 >=@DateTimeZZ )
or 开工开始日期>=@DateTimeZZ
or 生产开始日期>=@DateTimeZZ
 
								select convert(varchar(10),DATEADD(day,number*-1,getdate()) ,21) AS 日期  
								into #TempdatePerr
								from master.dbo.spt_values
								where type='p' 
								AND number<=20
								and number>=0
								order by 1 desc 
								 
							 
							
						select substring(日期,6,5) 日期,isnull(开工数,0) 开工数,isnull(生产数,0)  生产数 from (	
								select    aa.日期,
								 row_number() over ( order by aa.日期 desc) rn ,
								(select count(1) from #TempMOWIPQty bb where bb.开工开始日期<=aa.日期
								and bb.开工完成日期>=aa.日期) 开工数,
							(select count(1) from #TempMOWIPQty bb where bb.生产开始日期<=aa.日期
								and bb.生产完成日期>=aa.日期) 生产数
								from #TempdatePerr  aa
								) kk
								where kk.rn <=10
								order by  日期    
								
								
	  if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempdatePerr'))
			 drop table #TempdatePerr 

 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOWIPQty'))
			 drop table #TempMOWIPQty 
								 

 
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "U9connstr", null);


            return dt;

        }

        //定子/转子生产明细
        public DataTable GetList2(string ttype)
        {



            string ORGCode = ConfigurationManager.ConnectionStrings["ORGCode"].ConnectionString;
            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"
 
 select row_number() over (  order by mo.docno  ) 序号 ,
mo.docno 工单代码,mo.productqty 计划数量,mo.TotalCompleteQty 完工数量,ii.code 产品代码,
ii.name 产品名称,ii.SPECS  产品规格,mo.DescFlexField_PrivateDescSeg2 机台号,mo.DescFlexField_PrivateDescSeg3 模具号,
mo.DescFlexField_PrivateDescSeg1 客户   
from mo_mo mo with(nolock)  
left join base_organization oo with(nolock) on   mo.org=oo.id 
left join CBO_ItemMaster ii with(nolock)  on ii.id=mo.itemmaster
where  mo.DocState in (2,3)
 and mo.StartDate<=CONVERT(varchar(10), GETDATE(), 21) + ' 23:59:59'
 and case when mo.ClosedOn is not null then mo.ClosedOn  else   getdate()   end   >=CONVERT(varchar(10), GETDATE(), 21) + ' 00:00:00'
and ii.name like '%" + ttype + @"%' and oo.code='" + ORGCode + @"'
 
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "U9connstr", null);


            return dt;

        }

        //收货
        public DataTable GetList3()
        {


            string ORGCode = ConfigurationManager.ConnectionStrings["ORGCode"].ConnectionString;
            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"

              select  po.docno 采购单号,pol.doclineno 行号,ii.code 产品代码,
	ii.name 产品名称 ,ii.specs  产品规格,posl.ReqQtyTU-isnull(posl.TotalRecievedQtyTU,0) 数量 

from  dbo. PM_POShipLine POsL WITH(NOLOCK)
left join pm_poline pol with(nolock) on POsL.poline=pol.id
left join PM_PurchaseOrder po with(nolock) on po.id=pol.PurchaseOrder 
left join CBO_ItemMaster ii   with(nolock)  on ii.id=pol.iteminfo_itemid
left join base_organization oo  with(nolock)  on oo.id=POsL.CurrentOrg 
 
where  1= 1 
 and oo.code='" + ORGCode + @"'
and pol.status in (2 ) 
 and POsL.DeliveryDate >=CONVERT(varchar(10), GETDATE(), 21) + ' 00:00:00'  
		order by 1,2
 
 
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "U9connstr", null);


            return dt;

        }

        //收货比例
        public DataTable GetList5()
        {


            string ORGCode = ConfigurationManager.ConnectionStrings["ORGCode"].ConnectionString;
            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"

select isnull(sum(数量),0)  数量,isnull(sum(数量),0)-isnull(sum(收货数量),0) 未入数量,
 isnull(sum(收货数量),0) 收货数量, case when isnull(sum(数量),0)=0 then 0 else 
Convert(decimal(18,2),isnull(sum(收货数量),0)/ sum(数量) *100.0 )  end 收货比例
from (
                select  posl.ReqQtyTU 数量,posl.TotalRecievedQtyTU 收货数量 

from  dbo. PM_POShipLine POsL WITH(NOLOCK)
left join pm_poline pol with(nolock) on POsL.poline=pol.id
left join PM_PurchaseOrder po with(nolock) on po.id=pol.PurchaseOrder 
 
left join base_organization oo  with(nolock)  on oo.id=POsL.CurrentOrg 
where  1= 1
 
 and oo.code='" + ORGCode + @"'
and pol.status in (2,3,4,5) 
 and POsL.DeliveryDate >=CONVERT(varchar(10), GETDATE(), 21) + ' 00:00:00' 
 
	)  ll

 
 
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "U9connstr", null);


            return dt;

        }

        //出货
        public DataTable GetList4()
        {


            string ORGCode = ConfigurationManager.ConnectionStrings["ORGCode"].ConnectionString;
            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"

select  ss.docno 出货单号,line.doclineno 行号,ii.code 产品代码,
	ii.name 产品名称 ,ii.specs  产品规格,line.ShipQtyTUAmount 数量
	from sm_ship ss with(nolock)  
 inner join sm_shipline line with(nolock)   on ss.id=line.ship
 
left join CBO_ItemMaster ii   with(nolock)  on ii.id=line.iteminfo_itemid
left join base_organization oo  with(nolock)  on oo.id=line.Org
 where line.status!=3
and oo.code='" + ORGCode + @"'
  and line.createdon >=CONVERT(varchar(10), GETDATE(), 21) + ' 00:00:00' 
 
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "U9connstr", null);


            return dt;

        }

        //出货比例
        public DataTable GetList6()
        {


            string ORGCode = ConfigurationManager.ConnectionStrings["ORGCode"].ConnectionString;
            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"


select isnull(sum(数量),0) 数量,isnull(sum(数量),0)-isnull(sum(出货数量),0) 未出数量,
 isnull(sum(出货数量),0) 出货数量, case when isnull(sum(数量),0)=0 then 0 else 
Convert(decimal(18,2),isnull(sum(出货数量),0)/ sum(数量) *100.0 ) end  出货比例
from (
select   line.ShipQtyTUAmount 数量,case when 
 line.status=3 then line.ShipQtyTUAmount else 0 end 出货数量
	from sm_ship ss with(nolock)  
 inner join sm_shipline line with(nolock)   on ss.id=line.ship
 
left join CBO_ItemMaster ii   with(nolock)  on ii.id=line.iteminfo_itemid
left join base_organization oo  with(nolock)  on oo.id=line.Org
 where  oo.code='" + ORGCode + @"'
  and line.createdon >=CONVERT(varchar(10), GETDATE(), 21) + ' 00:00:00' 
 	)  jjj
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "U9connstr", null);


            return dt;

        }



        //设备开机率
        public DataTable GetList7()
        {



            List<DbParameter> parameter = new List<DbParameter>();
            string SqlText = @"

                SELECT ZQTY,QTY,
                case WHEN ZQTY=0 THEN 0 ELSE Convert(decimal(18,0),QTY/ZQTY*100)  end  EQPRATE
                FROM (select  ISNULL((
                 select count(1) from device_data   with(nolock)  where created_at>=CONVERT(varchar(10), GETDATE(), 21) + ' 00:00:00'),0) QTY,
                ISNULL(  (select count(1) from devices  with(nolock) ),0) ZQTY
                ) KKK
 
                 ";

            DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "connstr", null);


            return dt;

        }


        public static void WriteLogFile(string input, string txtName)
        {
            try
            {
                string logAdress = "C:\\看板日志" + "\\";
                if (!System.IO.Directory.Exists(logAdress))
                {
                    System.IO.Directory.CreateDirectory(logAdress);//不存在就创建目录   
                }

                string adress = logAdress + txtName;
                if (!System.IO.Directory.Exists(adress))
                {
                    System.IO.Directory.CreateDirectory(adress);//不存在就创建目录   
                }

                // string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString();
                /**/
                ///指定日志文件的目录
                string fname = adress + "\\" + "log" + DateTime.Now.ToString("yy-MM-dd") + ".txt";
                /**/
                ///定义文件信息对象

                FileInfo finfo = new FileInfo(fname);

                if (!finfo.Exists)
                {
                    FileStream fs;
                    fs = File.Create(fname);
                    fs.Close();
                    finfo = new FileInfo(fname);
                }

                /**/
                ///判断文件是否存在以及是否大于2K
                if (finfo.Length > 1024 * 1024 * 10)
                {
                    /**/
                    ///文件超过10MB则重命名
                    File.Move(logAdress + "\\Log\\" + txtName + ".txt", Directory.GetCurrentDirectory() + DateTime.Now.TimeOfDay + "\\Log\\" + txtName + ".txt");
                    /**/
                    ///删除该文件
                    //finfo.Delete();
                }
                //finfo.AppendText();
                /**/
                ///创建只写文件流

                using (FileStream fs = finfo.OpenWrite())
                {
                    /**/
                    ///根据上面创建的文件流创建写数据流
                    StreamWriter w = new StreamWriter(fs);

                    /**/
                    ///设置写数据流的起始位置为文件流的末尾

                    ///设置写数据流的起始位置为文件流的末尾
                    w.BaseStream.Seek(0, SeekOrigin.End);

                    w.WriteLine("*****************Start*****************");
                    w.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    /**/
                    ///写入当前系统时间并换行

                    /**/
                    ///写入日志内容并换行
                    w.WriteLine(input);

                    /**/
                    ///写入------------------------------------“并换行
                    w.WriteLine("------------------END------------------------");

                    /**/
                    ///清空缓冲区内容，并把缓冲区内容写入基础流
                    w.Flush();

                    /**/
                    ///关闭写数据流
                    w.Close();
                }
            }
            catch (Exception ex)
            { }

        }


        public string ConvertDataTableToJson(DataTable dt)
        {
            StringBuilder sbs = new StringBuilder();
            if (dt.Rows.Count > 0)//如果有记录
            {
                sbs.Append("{'" + dt.TableName + "':[");
                string str = "";
                foreach (DataRow dr in dt.Rows)//开始拼
                {
                    string result = "";
                    foreach (DataColumn dc in dt.Columns)
                    {
                        result += string.Format(",'{0}':'{1}'",
                            dc.ColumnName, dr[dc.ColumnName]);
                    }
                    result = result.Substring(1);
                    result = ",{" + result + "}";
                    str += result;
                }
                str = str.Substring(1);
                sbs.Append(str);
                sbs.Append("]}");
            }
            else//如果没有记录
            {
                sbs.Append("");
            }
            return sbs.ToString();
        }







    }
}