JBLEAR-STD/Stone.WinForm/Stone.WinModule/Standard/frmInventoryAlarm.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Stone.Common;
using Stone.Entity;
using System.Data.SqlClient;

namespace Stone.WinModule.Standard
{
    public partial class frmInventoryAlarm : Stone.WinModule.frmBase
    {
        public frmInventoryAlarm()
        {
            InitializeComponent();
        }

        private void frmInventoryAlarmJIT_Load(object sender, EventArgs e)
        {
            this.txtEndDate.Value = DateTime.Now.AddDays(1);
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                Warning();
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            MyExport.ShowExport(this.dgrdView);
        }

        public void Warning()
        {

            string StartDate = this.txtStartDate.Value.ToString("yyyy-MM-dd");
            string EndDate = this.txtEndDate.Value.ToString("yyyy-MM-dd");

            Gm_WMS.DataAccess.DataService.LocalDBService db = new Gm_WMS.DataAccess.DataService.LocalDBService();

            string sql = @"
                select 

                PartNumber, 
                Description, 
                SUM(quantity) as Quantity, 
                0 as Inventory,
                0 as [DifferentialQuantity]

                from v_JIS_Lab 

                where startDate>='{0}' and startDate<='{1}' and [IsStandard]=1 and Description is not null


                group by partNumber, Description
                order by partNumber desc

            ";

            sql = string.Format(sql, StartDate, EndDate);

            DataTable dtData = db.Exec_DataSet(sql).Tables[0];
            DataTable dtInv = GetInv();
            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                string PartNumber = dtData.Rows[i]["PartNumber"].ToString();
                DataRow[] drs = dtInv.Select("[零件号]='" + PartNumber + "'");
                int Inventory = 0;
                if (drs.Length > 0)
                {
                    Inventory = Convert.ToInt32(drs[0]["总计"]); 
                }


                int Quantity = Convert.ToInt32(dtData.Rows[i]["Quantity"]);

                dtData.Rows[i]["Inventory"] = Inventory;
                dtData.Rows[i]["DifferentialQuantity"] = Inventory - Quantity;
            }


            this.dgrdView.DataSource = dtData;
            this.dgrdView.Columns["PartNumber"].HeaderText = "零件号";
            this.dgrdView.Columns["Description"].HeaderText = "描述";
            this.dgrdView.Columns["Quantity"].HeaderText = "数量";
            this.dgrdView.Columns["Inventory"].HeaderText = "当前库存";
            this.dgrdView.Columns["PartNumber"].HeaderText = "零件号";
            this.dgrdView.Columns["DifferentialQuantity"].HeaderText = "差异";

            this.lblState.Text = "Record:" + dtData.Rows.Count;
        }

        private void dgrdView_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            try
            {
                if (e.RowIndex < 0) return;
                if (e.ColumnIndex < 0) return;

                if (Convert.ToInt32(this.dgrdView.Rows[e.RowIndex].Cells["DifferentialQuantity"].Value) < 0)
                {
                    this.dgrdView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Red;
                }


            }
            catch
            {
            }
        }

        //获取库存
        public DataTable GetInv()
        {
            Entity_t_Sys_Appconfig t_Sys_Appconfig = new Entity_t_Sys_Appconfig();
            DataTable dtAppconfig = t_Sys_Appconfig.GetData("[Code]='BarCode'").Tables[0];

            string connstring = dtAppconfig.Rows[0]["Value"].ToString();

            using (SqlConnection conn = new SqlConnection(connstring))
            {
                conn.Open();

                string sql = @"
                    DECLARE @RC int
                    EXECUTE @RC = [HZCX] 
                ";

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                DataSet dsData = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dsData);

                return dsData.Tables[0];

            }

        }

    }
}