using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MercatorApi;
using MercatorController;
using MercatorUi;

namespace Mercator.Ribbon.ButtonsCodes
{
    public static class Script
    {

        public static void Exec(DevComponents.DotNetBar.ButtonItem clickedButtonItem)
        {
            string seqOrig = "Comm";
            string seqDest = "Blivr";


            string req =
                @"
                
                -- ATTENTIE kolom Q_LIV_AUTO toevoegen in de tabel LIGNES V:
                -- ALTER TABLE dbo.LIGNES_V ADD Q_LIV_AUTO float(53) NOT NULL CONSTRAINT DF_LIGNES_V_Q1 DEFAULT ((0))
                
                -- Aanmaak van de vereiste kolom indien die nog niet bestaat
                if not exists(select * from sys.columns where Name = N'Q_LIV_AUTO' and Object_ID = Object_ID(N'LIGNES_V'))
                    ALTER TABLE dbo.LIGNES_V ADD Q_LIV_AUTO float(53) NOT NULL CONSTRAINT DF_LIGNES_V_Q1 DEFAULT ((0))
                
                -- aanmaak van een index op q_liv_auto om de module te optimaliseren wanneer er veel records in lignes_v staan
                if not exists(select * from sysindexes where (id=OBJECT_ID('LIGNES_V')) and (name='Q_LIV_AUTO'))
                    create index Q_LIV_AUTO on dbo.LIGNES_V (q_liv_auto);

                -- op nul zetten van q_liv_auto
                update lignes_v set q_liv_auto=0 where q_liv_auto<>0

                -- inventariseert alle bestelde artikels evenals hun beschikbare hoeveelheid ( als dispo > 0 )
                -- Cursor voor de lijst van artikels
                DECLARE curseur_articles CURSOR FOR
                    select distinct lignes_v.id_article,pieds_v.id_depot,dispo.dispo
                    from lignes_v (NOLOCK)
                        inner join pieds_v on ((lignes_v.id=pieds_v.id) and (lignes_v.journal=pieds_v.journal) and (lignes_v.piece=pieds_v.piece))
                        inner join dispo on (lignes_v.id_article=dispo.id_stock) and (dispo.id_magasin=pieds_v.id_depot)
                    where pieds_v.type=3 and dispo.dispo > 0
                    order by id_article



                DECLARE @id_article char(10), @id_depot float, @dispo float, @qtt_stock float
                OPEN curseur_articles
                FETCH curseur_articles INTO @id_article, @id_depot, @dispo

                -- voor elk artikel worden de bestelregels en de betrokken hoeveelheden teruggevonden volgens de voorkeursvolgorde van de klant
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    SET @qtt_stock = @dispo;
    
                    DECLARE @id char(10), @journal char(5), @piece numeric(11,0), @dl_id char(10), @q float
            
                    DECLARE curseur_lignes CURSOR FOR
                        select lignes_v.id, lignes_v.journal, lignes_v.piece, lignes_v.dl_id, lignes_v.q
                        from lignes_v
                            inner join pieds_v on ((lignes_v.id=pieds_v.id) and (lignes_v.journal=pieds_v.journal) and (lignes_v.piece=pieds_v.piece))
                        where pieds_v.type=3
                            and id_article=@id_article
                            and (pieds_v.id_depot=@id_depot)
                        order by date
    
                    OPEN curseur_lignes
                    FETCH curseur_lignes INTO @id, @journal, @piece, @dl_id, @q
    
                    -- update q_liv_auto in de verschillende bestellingen
                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        DECLARE @minQ float
                        if @q > @qtt_stock
                            set @minQ = @qtt_stock;
                        else
                            set @minQ = @q;
        
                        update lignes_v set q_liv_auto = @minQ
                        where (id=@id) and (journal=@journal) and (piece=@piece) and (dl_id=@dl_id);
        
                        SET @qtt_stock = @qtt_stock - @minQ;
                        IF (@qtt_stock <= 0)
                            BREAK
                    
                        FETCH curseur_lignes INTO @id, @journal, @piece, @dl_id, @q
                    END
            
                    CLOSE curseur_lignes
                    DEALLOCATE curseur_lignes
            
                    FETCH curseur_articles INTO @id_article, @id_depot, @dispo
                END

                CLOSE curseur_articles
                DEALLOCATE curseur_articles
                ";

            //Uitvoering van het update script van Q_LIV_AUTO
            MercatorSqlConnection connectionMercator = new MercatorSqlConnection(MercatorUi.Globals.RepData);

            using (SqlCommand cmd = new SqlCommand(req, connectionMercator.Connection))
            {
                if (!Api.SqlExec(MercatorUi.Globals.RepData, cmd))
                {
                    Dialogs.Stop("De uitvoering is mislukt");
                    return;
                }

            }

            //Ophalen van de originele en bestemmingssequenties
            DataSet ds = Api.Zselect(MercatorUi.Globals.RepData,
                @"select * from SEQUENC where (journal=@seqOrig)
                  select * from SEQUENC where (journal=@seqDest)",
                new MercatorSqlParam("@seqOrig", seqOrig, SqlDbType.Char),
                new MercatorSqlParam("@seqDest", seqDest, SqlDbType.Char));

            if (ds.Tables.Count < 2 || ds.Tables[0].Rows.Count == 0 || ds.Tables[1].Rows.Count == 0)
            {
                Dialogs.Stop(string.Format("Een sequentie werd niet gevonden (Oorsprong:{0} - Bestemming:{1})", seqOrig, seqDest));
                return;
            }

            //Parameters voor de uitvoering van de automatische levering
            MercatorUi.Forms.Gescom.GescomDialogs.GescomAskOrdersDelivery.GescomAskOrdersDeliveryRet askRet =
                new MercatorUi.Forms.Gescom.GescomDialogs.GescomAskOrdersDelivery.GescomAskOrdersDeliveryRet(
                ds.Tables[0].Rows[0]// sequencOrig
                , ds.Tables[1].Rows[0]// sequencDest
                , string.Format("(pieds_v.date>={0}) and (pieds_v.date<={1})", Api.DateTimeSqlString(new DateTime(1900, 1, 1)), Api.DateTimeSqlString(DateTime.Today))  // where-clause
                , false // groupLines
                , false //BoolSimulation
                , 5 // checkstock = auto (int)   
                , "1" // priority (string)
                , 0 // minimum (double)
                , new DateTime(1900, 1, 1)// datebegin
                , DateTime.Today // dateend)
            );

            MercatorUi.Forms.Gescom.GescomProcedures.Procedures.OrdersDelivery(MercatorUi.Engine.Gescom.Billing.TypeVAEnum.V, askRet);

        }
    }
}