using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using wcftest.orm;
using wcftest.EnumDefine;
using logtxtWrite;
using OfficeOpenXml.Drawing;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using wcftest.sapEntity;
using System.IO;
using System.Drawing;
using Newtonsoft.Json;
using wcftest.EnumDefine.Sys;


namespace wcftest.sql
{
    public static class baseData
    {
        public static List<T> findStorage<T>(string Name, string name, string No, string no, string tableName)
        {
            List<T> tableList = new List<T>();
            try
            {
                using (dbModel mod = new dbModel())
                {//SELECT * FROM [weicai_LA20001].[dbo].[tool] where toolName like '%工%' and toolNo like '%%' 
                    string sql = "select * from [dbo].[" + tableName + "] where " + Name + " like '%" + name + "%' and " + No + " like '%" + no + "%'";
                    tableList = mod.Database.SqlQuery<T>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                logtxt.txtWrite("类名：baseData/函数名：findStorage/ name:" + name.ToString() + "no:" + no.ToString()
                    + "tableName" + tableName.ToString() + logtxt.oneRow(ex.ToString()), 2);
                return null;
            }

            return tableList;
        }
        public static void analyse(wirteExcel excelInfo)
        {
            #region 验证
            if (excelInfo.body.Count < 1)
            {
                logtxt.txtWrite("类名: baseData 方法名: analyse  缺料明细数量为空", 2);
                return;
            }
            //DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss")
            string path = @"D:\缺料分析报表\" + excelInfo.head.workCode + "#第" + excelInfo.head.cequenceQuantity + "套缺料分析表.xlsx";
            if (!Directory.Exists(Path.GetDirectoryName(path))) Directory.CreateDirectory(Path.GetDirectoryName(path));
            FileInfo newFile = new FileInfo(path);
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(path);
            }
            #endregion


            try
            {
                using (ExcelPackage package = new ExcelPackage(newFile))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("PICK缺料分析");

                    Image tablelogo = Properties.Resources.siemensLogo;//获取图片资源
                    //设置表头格式
                    #region 设置表头格式
                    worksheet.Cells[1, 2, 1, 9].Merge = true;//合并单元格 起始行,起始列,目标行,目标列
                    //worksheet.Cells[1, 3].Style.Font.Bold = true;// 字体为粗体
                    worksheet.Row(1).Style.Font.Bold = true;// 字体为粗体;
                    worksheet.Row(2).Style.Font.Bold = true;// 字体为粗体;
                    worksheet.Row(3).Style.Font.Bold = true;// 字体为粗体;
                    worksheet.Cells[1, 3].Style.Font.Color.SetColor(Color.Black);//设置字体为黑色

                    worksheet.Cells[1, 2].Style.Font.Size = 16;//字体大小
                    worksheet.Cells[2, 1, 2, 8].Style.Font.Size = 12;//字体大小
                    worksheet.Cells[3, 1, 3, 8].Style.Font.Size = 10;//字体大小
                    worksheet.Row(1).Height = 40;
                    worksheet.Row(2).Height = 25;
                    worksheet.Cells[1, 2].Value = "广州西门子配变智能化工厂-立库区PICKLIST缺料报表";//给指定位置赋值
                    //表头
                    //  worksheet.Cells[2, 1, 2, 2].Merge = true;//合并单元格 起始行,起始列,目标行,目标列
                    string productionCode = excelInfo.head.workCode;//生产订单号
                    string saleCode = excelInfo.head.saleCode;//销售单号
                    string groupCode = excelInfo.head.tatalQuantity.ToString();//总套数
                    string groupNumber = excelInfo.head.cequenceQuantity.ToString();//第几套
                    worksheet.Cells[2, 1].Value = "生产订单号:";
                    worksheet.Cells[2, 2].Value = productionCode;
                    worksheet.Cells[2, 3].Value = "跟踪号:";
                    worksheet.Cells[2, 4].Value = saleCode;
                    worksheet.Cells[2, 5].Value = "总套数:";
                    worksheet.Cells[2, 6].Value = groupCode;
                    worksheet.Cells[2, 7].Value = "第几套:";
                    worksheet.Cells[2, 8].Value = groupNumber;
                    for (int j = 0; j < 2; j++)
                    {
                        worksheet.Cells[1, 1, 3, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        worksheet.Cells[1, 1, 3, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                        worksheet.Cells[1, 1, 3, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        worksheet.Cells[1, 1, 3, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    }
                    #endregion

                    #region 列名和图片
                    string[] tableTop = { "销售单号", "销售项号", "物料编号", "物料名称", "缺料数量", "暂存区数量", "立库区数量", "库存单位" };
                    for (int i = 0; i < 8; i++)
                    {
                        if (i < 5)
                        {
                            worksheet.Column(i + 1).Width = 15;//设置列宽
                        }
                        else
                        {
                            worksheet.Column(i + 1).Width = 12;
                        }
                        worksheet.Column(i + 1).Style.Font.Name = "宋体";//字体
                        worksheet.Column(i + 1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//水平居中
                        worksheet.Column(i + 1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
                        // worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                        worksheet.Cells[3, i + 1].Value = tableTop[i];
                    }
                    //  worksheet.Cells[1, 1, 20, 20].Style.Fill.PatternType = ExcelFillStyle.Solid;//
                    // worksheet.Cells.Style.WrapText = true;//自动换行
                    //  worksheet.Cells[1, 20].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128,128,128));//设置背景颜色
                    ExcelPicture pic = worksheet.Drawings.AddPicture("logo", tablelogo);//插入图片
                    pic.SetSize(105, 53);//设置图片尺寸
                    pic.SetPosition(0, 0, 0, 0);//设置图片位置,以单元格为单位
                    #endregion

                    #region 明细
                    for (int k = 0; k < excelInfo.body.Count; k++)
                    {
                        worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                        worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                        worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;

                        worksheet.Cells[k + 4, 1].Value = excelInfo.body[k].saleCode;
                        worksheet.Cells[k + 4, 2].Value = excelInfo.body[k].saleCodeItem;
                        // worksheet.Cells[k + 4, 3].Value = excelInfo.body[k].trackNumber;
                        worksheet.Cells[k + 4, 3].Value = excelInfo.body[k].materalCode;
                        worksheet.Cells[k + 4, 4].Value = excelInfo.body[k].materalName;
                        worksheet.Cells[k + 4, 5].Value = excelInfo.body[k].lackQuantity;
                        worksheet.Cells[k + 4, 6].Value = excelInfo.body[k].tempoarayQuantity;
                        worksheet.Cells[k + 4, 7].Value = excelInfo.body[k].stockQuantity;
                        worksheet.Cells[k + 4, 8].Value = excelInfo.body[k].stockUnit;
                    }
                    #endregion

                    package.Save();
                    worksheet.PrinterSettings.Orientation = eOrientation.Portrait;
                }
            }
            catch (Exception ex)
            {
                logtxt.txtWrite("类名: baseData 方法名: analyse  产生异常" + JsonConvert.SerializeObject(excelInfo) + ex.Message, 2);
            }

        }
       
    }
}