using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataLibrary;
using CoreSavingLibrary;
using DataLibrary;
using System.Data;

namespace Saving.Applications.trading
{
    public class TradingService
    {
        Sdt dt = new Sdt();
        string Sqlse = string.Empty;

        public DateTime _ofgetworkdatestore(string storeid)
        {
            DateTime workdate = new DateTime();
            Sqlse = "select work_date from ststore where store_id = {0}";
            Sqlse = WebUtil.SQLFormat(Sqlse, storeid);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                workdate = dt.GetDate("work_date");
            }
            return workdate;
        }

        public int _ofgetvattype(int itemamt, int vattype)
        {
            int vatAtm = 0;
            return vatAtm;
        }

        public decimal _ofgetvatrate(string coopid)
        {
            decimal vat_rate = 0;
            Sqlse = "select vat_rate from ststockconstant where coop_id = {0}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                vat_rate = dt.GetDecimal("vat_rate");
            }
            return vat_rate;
        }

        public Int32 GetLastSeqnoStkCard(string productno, string storeid, string coopid)
        {
            Int32 seq_no = 0;
            string sql = @"select nvl(max(seq_no),0) as seq_no from ststockcard where product_no = {0} and 
                    store_id = {1} and coop_id = {2}";
            sql = WebUtil.SQLFormat(sql, productno, storeid, coopid);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                seq_no = dt.GetInt32("seq_no");
            }
            seq_no++;
            return seq_no;
        }

        public decimal GetBalStkMast(string productno, string storeid, string coopid)
        {
            decimal balance_qty = 0;
            string sql = @"select nvl( balance_qty, 0 ) as balance_qty from ststockmaster
                    where product_no = {0} and 
                    store_id = {1} and coop_id = {2}";
            sql = WebUtil.SQLFormat(sql, productno, storeid, coopid);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                balance_qty = dt.GetDecimal("balance_qty");
            }
            return balance_qty;
        }

        public  decimal GetVatRate(string coop_id)
        {
            decimal vat_rate = 0;
            string sql = "select vat_rate from ststockconstant where coop_id = '" + coop_id + "' ";
            Sdt sSQL = WebUtil.QuerySdt(sql);
            if (sSQL.Next())
            {
                vat_rate = sSQL.GetDecimal("vat_rate");
            }
            return vat_rate;
        }

        public  decimal GetLoanFlag(string coop_id,string debttype_code)
        {
            decimal loan_flag = 0;
            if (debttype_code == "001")
            {
                string sql = @"select loancre_flag from ststockconstant where coop_id={0}";
                sql = WebUtil.SQLFormat(sql, coop_id);
                Sdt dt = WebUtil.QuerySdt(sql);
                if (dt.Next())
                {
                    loan_flag = dt.GetDecimal("loancre_flag");
                }
            }
            else if (debttype_code == "002")
            {
                string sql = @"select loandebt_flag from ststockconstant where coop_id={0}";
                sql = WebUtil.SQLFormat(sql, coop_id);
                Sdt dt = WebUtil.QuerySdt(sql);
                if (dt.Next())
                {
                    loan_flag = dt.GetDecimal("loandebt_flag");
                }
            }
            return loan_flag;
        }

        public  decimal GetIncDebtType(string coop_id)
        {
            decimal debt_type = 0;
            string sql = @"select incdebt_type from ststockconstant where coop_id={0}";
            sql = WebUtil.SQLFormat(sql, coop_id);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                debt_type = dt.GetDecimal("incdebt_type");
            }
            return debt_type;
        }

        public  decimal GetVatType(string coop_id, string store_id)
        {
            decimal vat_type = 0;
            string sql = @"select vat_type from ststore where coop_id={0} and store_id = {1}";
            sql = WebUtil.SQLFormat(sql, coop_id, store_id);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                vat_type = dt.GetDecimal("vat_type");
            }
            return vat_type;
        }

        public decimal GetCostAVG(string coop_id)
        {
            decimal costavg_flag = 0;
            string sql = @"select costavg_flag from ststockconstant where coop_id={0} ";
            sql = WebUtil.SQLFormat(sql, coop_id);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                costavg_flag = dt.GetDecimal("costavg_flag");
            }
            return costavg_flag;
        }

        public string GetFullname(string coopid, string debttypecode, string debtfromtypecode, string debtno)
        {
            string fullname = "";
            Sqlse = @"select debt_name from stdebtmaster where coop_id = {0} and debttype_code = {1} and
                        debtfromtype_code = {2} and debt_no = {3}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, debttypecode, debtfromtypecode, debtno);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                fullname = dt.GetString("debt_name").Trim();
            }
            return fullname;
        }

        public string[] GetProductMaster(string coopid, string storeid, string productno)
        {
            string [] productData = new string[4];
            Sqlse = @"select sm.product_no as product_no, sm.store_id as store_id,
                    sm.cost_amt as cost_amt, sp.product_desc as product_desc
                    from ststockmaster sm, stproductmaster sp
                    where sm.coop_id = sp.coop_id and
                        sm.product_no = sp.product_no and
                        sm.coop_id = {0} and 
                        sm.store_id = {1} and 
                        sm.product_no = {2} ";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, storeid, productno);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                productData[0] = dt.GetString("product_no");
                productData[1] = dt.GetString("store_id");
                productData[2] = dt.GetString("cost_amt");
                productData[3] = dt.GetString("product_desc");
            }
            return productData;
        }

        public string ChkBarcode(string coopid, string storeid, string barcode)
        {
            string productNo = "";
            Sqlse = @"select sm.product_no as product_no, sm.store_id as store_id,
                    sm.cost_amt as cost_amt, sp.product_desc as product_desc
                    from ststockmaster sm, stproductmaster sp
                    where sm.coop_id = sp.coop_id and
                        sm.product_no = sp.product_no and
                        sm.coop_id = {0} and sm.store_id = {1} and 
                        sp.product_no = {2} 
                    union
                    select sm.product_no as product_no, sm.store_id as store_id,
                    sm.cost_amt as cost_amt, sp.product_desc as product_desc
                    from ststockmaster sm, stproductmaster sp
                    where sm.coop_id = sp.coop_id and
                        sm.product_no = sp.product_no and
                        sm.coop_id = {0} and sm.store_id = {1} and 
                        sp.bar_code = {2} ";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, storeid, barcode);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                productNo = dt.GetString("product_no").Trim();
            }
            return productNo;
        }

        public DateTime GetWorkDate(string coopid, string store_id)
        {
            DateTime work_date = DateTime.MinValue;
            Sqlse = @"select work_date from ststore where coop_id = {0} and store_id={1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, store_id);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                work_date = dt.GetDate("work_date");
            }
            return work_date;
        }

        public string GetStatusWorkDate(string coopid, string store_id)
        {
            string closeday_status = "";
            Sqlse = @"select closeday_status from ststore where coop_id = {0} and store_id={1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, store_id);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                closeday_status = dt.GetString("closeday_status").Trim();
            }
            return closeday_status;
        }

        public string GetPostToFin(string coopid, string store_id)
        {
            string posttofin = "";
            Sqlse = @"select posttofin from ststore where coop_id = {0} and store_id={1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, store_id);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                posttofin = dt.GetString("posttofin").Trim();
            }
            return posttofin;
        }

        public decimal cal_int(decimal amt, int day, decimal rate)
        {
            decimal cal_int = 0;
            rate = rate / 100;
            cal_int = (amt * day * rate) / 365;
            cal_int = Math.Round(cal_int);
            //cal_int = Math.Ceiling(cal_int); 
            return cal_int;
        }

        public decimal cal_fine(decimal amt, int day, decimal rate)
        {
            decimal cal_fine = 0;
            rate = rate / 100;
            cal_fine = (amt * day * rate) / 365;
            cal_fine = Math.Round(cal_fine);
           // cal_fine = Math.Ceiling(cal_fine); 
            return cal_fine;
        }


        public decimal of_getcontractno(string coopid, string document_code)
        {
            decimal last_documentno = 0;
            Sqlse = @"select last_documentno from cmdocumentcontrol where coop_id={0} and system_code='MRK' and  document_code={1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, document_code);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                last_documentno = dt.GetDecimal("last_documentno") + 1 ;
            }
            return last_documentno;
        }

        public string of_getyearprefix(string coopid, string document_code)
        {
            string document_year = "";
            Sqlse = @"select SUBSTR(document_year,3,4) document_year from cmdocumentcontrol where coop_id={0} and system_code='MRK' and  document_code={1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, document_code);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                document_year = dt.GetString("document_year");
            }
            return document_year;
        }

        public decimal of_processdecimal(decimal amount_per1 , decimal amount_per2)
        {
            double _caldecimal = 0;
            decimal _adecimal = 0;
            if (amount_per1 <= amount_per2)
            {
                _adecimal = amount_per1;
                _caldecimal = Convert.ToDouble(amount_per2 - amount_per1);
            }
            else
            {
                _adecimal = amount_per2;
                _caldecimal = Convert.ToDouble(amount_per1 - amount_per2);
            }
            if (_caldecimal >= 0.005)
            {
                _adecimal = (Math.Truncate(_adecimal * 100) / 100) + Convert.ToDecimal(0.01);
            }
            else
            {
                _adecimal = (Math.Truncate(_adecimal * 100) / 100);
            }
            return _adecimal;
        }

        public decimal of_processupdecimal(decimal amount_per1, decimal amount_per2)
        {
            double _caldecimal = 0;
            decimal _adecimal = 0;
            if (amount_per1 <= amount_per2)
            {
                _adecimal = amount_per1;
                _caldecimal = Convert.ToDouble(amount_per2 - amount_per1);
            }
            else
            {
                _adecimal = amount_per2;
                _caldecimal = Convert.ToDouble(amount_per1 - amount_per2);
            }
            if (_caldecimal >= 0.001)
            {
                _adecimal = (Math.Truncate(_adecimal * 100) / 100) + Convert.ToDecimal(0.01);
            }
            else
            {
                _adecimal = (Math.Truncate(_adecimal * 100) / 100);
            }
            return _adecimal;
        }


        public decimal of_getrowprintslip(string coopid, string store_id,string expense_code)
        {
            decimal row_print = 0;
            Sqlse = @"select row_printcsh,row_printlon from ststore where coop_id={0} and store_id={1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopid, store_id);
            dt = WebUtil.QuerySdt(Sqlse);
            if (dt.Next())
            {
                if (expense_code == "CSH")
                {
                    row_print = dt.GetDecimal("row_printcsh");
                }
                else if (expense_code == "LON")
                {
                    row_print = dt.GetDecimal("row_printlon");
                }
            }
            return row_print;
        }
        public void GenStatement(string coop_id, string CONTRACT_NO, DateTime start_date, DateTime end_date)
        {
            WebUtil.QuerySdt(" delete from STCONTRACTSLIP ");
            string Sqlvat = @" select * from stcontractlimitdet where coop_id = {0} and contract_no = {1} and slip_status <> -9 and slip_date between {2} and {3} order by slip_date,slip_no,ref_incdecno asc  ";
            Sqlvat = WebUtil.SQLFormat(Sqlvat, coop_id, CONTRACT_NO, start_date, end_date);
            Sdt ds = WebUtil.QuerySdt(Sqlvat);
            int i = 1; decimal principal_balance = 0;
            while (ds.Next())
            {
                if (ds.GetString("DEBTINCDEC_TYPE") == "001")
                {
                    principal_balance += ds.GetDecimal("PRINCIPAL_PAYMENT");
                }
                else if (ds.GetString("DEBTINCDEC_TYPE") == "B/F")
                {
                    principal_balance = ds.GetDecimal("BFPRINCIPAL_ARREAR");
                }
                else
                { //ชำระ
                    principal_balance = principal_balance - ds.GetDecimal("PRINCIPAL_PAYMENT");
                }
                string sql = @" insert into STCONTRACTSLIP( COOP_ID  ,   CONTRACT_NO ,SLIP_NO,REF_INCDECNO,SEQ_NO  , DEBT_NO , SLIP_DATE  ,
                ENTRY_ID , CALINT_FROM  , CALINT_TO  ,PRINCIPAL_PAYMENT , PRINCIPAL_BALANCE , INT_PAYMENT , FINE_PAYMENT ,
                INTYEAR_ARREAR_AMT , FINEYEAR_ARREAR_AMT , FINE_ARREAR_AMT , FINE_PERIOD_AMT , INTEREST_ARREAR_AMT , 
                INTEREST_PERIOD_AMT , PRINCIPAL_ARREAR_AMT , BF_INTPERIOD , BF_FINEPERIOD , BFINTYEAR_ARREAR , BFFINEYEAR_ARREAR 
                , BFINT_ARREAR , BFFINE_ARREAR , BFPRINCIPAL_ARREAR,DEBTINCDEC_TYPE,ref_slipno,calfine_from,calfine_to,print_status) values 
                ({0},{1},{2},{3},{4}, 
                {5},{6},{7},{8},{9},{10},{11},
                {12},{13},{14},{15},{16},
                {17},{18},{19},{20},{21},{22},
                {23},{24},{25},{26},{27},{28},{29},{30},{31},{32})";


                sql = WebUtil.SQLFormat(sql, coop_id, CONTRACT_NO, ds.GetString("SLIP_NO"), ds.GetString("REF_INCDECNO"), i, ds.GetString("DEBT_NO"), ds.GetDate("SLIP_DATE"),
                 ds.GetString("ENTRY_ID"), ds.GetDate("CALINT_FROM"), ds.GetDate("CALINT_TO"), ds.GetDecimal("PRINCIPAL_PAYMENT"), principal_balance, ds.GetDecimal("INT_PAYMENT"), ds.GetDecimal("FINE_PAYMENT"),
                ds.GetDecimal("INTYEAR_ARREAR_AMT"), ds.GetDecimal("FINEYEAR_ARREAR_AMT"), ds.GetDecimal("FINE_ARREAR_AMT"), ds.GetDecimal("FINE_PERIOD_AMT"), ds.GetDecimal("INTEREST_ARREAR_AMT"),
                ds.GetDecimal("INTEREST_PERIOD_AMT"), ds.GetDecimal("PRINCIPAL_ARREAR_AMT"), ds.GetDecimal("BF_INTPERIOD"), ds.GetDecimal("BF_FINEPERIOD"), ds.GetDecimal("BFINTYEAR_ARREAR"), ds.GetDecimal("BFFINEYEAR_ARREAR")
                , ds.GetDecimal("BFINT_ARREAR"), ds.GetDecimal("BFFINE_ARREAR"), ds.GetDecimal("BFPRINCIPAL_ARREAR"), ds.GetString("DEBTINCDEC_TYPE"), ds.GetString("ref_slipno"), ds.GetDate("CALFINE_FROM"), ds.GetDate("CALFINE_TO"), ds.GetString("PRINT_STATUS"));
                WebUtil.QuerySdt(sql);
                i++;
            }
            ds.Clear();


        }


        public void ofprocess_genvat(string coop_id, string store_id, DateTime start_date, DateTime end_date)
        {
            sub_ofprocess_genvat( coop_id,  store_id,  start_date,  end_date,"LON");
            sub_ofprocess_genvat(coop_id, store_id, start_date, end_date, "CSH");
        }

        public void sub_ofprocess_genvat(string coop_id, string store_id, DateTime start_date, DateTime end_date,string expense_code)
        {
            #region        
            string slipttype = "002";   
                //ค้นหาใบเสร็จ
                decimal sum_DetVat = 0, sum_SlipVat = 0, _calsumnet = 0;
                string Sqlslip = @"select * from ststockslip s where s.coop_id ={0} and s.store_id={1} and s.slip_status = 1 and s.slip_date between {2} and {3} and s.sliptype_code = {4} and s.expense_code = {5} order by s.slip_date,s.sliptype_code,s.slip_no";
                Sqlslip = WebUtil.SQLFormat(Sqlslip, coop_id, store_id, start_date, end_date, slipttype, expense_code);
                dt = WebUtil.QuerySdt(Sqlslip);
                if (dt.Rows.Count > 0)
                {
                    while (dt.Next())
                    {
                        string slip_no = "", sliptype_code = "";
                        decimal slipnet_amt = 0;
                        slip_no = dt.GetString("slip_no");
                        sliptype_code = dt.GetString("sliptype_code");
                        slipnet_amt = dt.GetDecimal("slipnet_amt"); 
                    }

                    decimal itemnet_novat = 0;
                    string Sqlno = @"select d.slip_no,m.product_no,sum(d.vat_amt) vat_amt,sum(d.itemnet_amt) itemnet_novat  from ststockslip s left join  ststockslipdet d  on s.coop_id = d.coop_id and s.store_id = d.store_id and s.slip_no = d.slip_no  and d.slipdet_status = 1
left join ststockmaster m on m.coop_id = d.coop_id and m.store_id = d.store_id and m.product_no = d.product_no
 where d.coop_id ={0} and d.store_id={1} and slip_date between {2} and  {3} and s.sliptype_code ={4}  and s.slip_status = 1 and m.vat_flag = 0 and s.expense_code = {5} group by d.slip_no,m.product_no order by d.slip_no,m.product_no";
                    Sqlno = WebUtil.SQLFormat(Sqlno, coop_id, store_id, start_date, end_date, slipttype, expense_code);
                    Sdt dtno = WebUtil.QuerySdt(Sqlno);
                    while (dtno.Next())
                    {
                        string productno = "", slipno = ""; 
                        decimal item_novat = 0, vatamt = 0;
                        slipno = dtno.GetString("slip_no");
                        productno = dtno.GetString("product_no");
                        vatamt = dtno.GetDecimal("vat_amt");
                        item_novat = dtno.GetDecimal("itemnet_novat");
                        itemnet_novat += item_novat;
                        if (vatamt != 0)
                        {
                            string Sqlupdet = @"update ststockslipdet set vat_amt = 0 , item_amt = {5} where coop_id ={0} and store_id={1} and slip_no ={2} and itemtype_code = {3} and product_no = {4} ";
                            Sqlupdet = WebUtil.SQLFormat(Sqlupdet, coop_id, store_id, slipno, slipttype, productno, item_novat);
                            WebUtil.QuerySdt(Sqlupdet);
                        }
                    }

                    // Gen slip ห้ามเปิด เอาไว้ใช้เฉพาะสลิปวันนี้มีปัญหา ปิดข้างล่างก่อนด้วย sum_SlipVat != sum_DetVat  by God
                    string Sqlvat = @"select d.slip_no,m.product_no,sum(d.vat_amt) vat_amt,sum(d.itemnet_amt) itemnet_vat , d.seq_no  from ststockslip s left join  ststockslipdet d  on s.coop_id = d.coop_id and s.store_id = d.store_id and s.slip_no = d.slip_no  and d.slipdet_status = 1
left join ststockmaster m on m.coop_id = d.coop_id and m.store_id = d.store_id and m.product_no = d.product_no
 where d.coop_id ={0} and d.store_id={1} and slip_date between {2} and  {3} and s.sliptype_code ={4}  and s.expense_code = {5} and s.slip_status = 1 and m.vat_flag = 1 group by d.slip_no,m.product_no, d.seq_no  order by d.slip_no,m.product_no, d.seq_no ";
                    Sqlvat = WebUtil.SQLFormat(Sqlvat, coop_id, store_id, start_date, end_date, slipttype, expense_code);
                    Sdt dtvat = WebUtil.QuerySdt(Sqlvat);
                    while (dtvat.Next())
                    {
                        string productno = "", slipno = "";
                        decimal itemnet_vat = 0, vatamt = 0, seq_no = 0, _calvat = 0, _calitem = 0;
                        slipno = dtvat.GetString("slip_no");
                        productno = dtvat.GetString("product_no");
                        vatamt = dtvat.GetDecimal("vat_amt");
                        itemnet_vat = dtvat.GetDecimal("itemnet_vat");
                        seq_no = dtvat.GetDecimal("seq_no");
                        _calvat = Math.Round(itemnet_vat * 7 / 107, 2);
                        _calitem = Math.Round(itemnet_vat - _calvat, 2);
                        if (vatamt != _calvat)
                        {
                            string Sqlupdet = @"update ststockslipdet set vat_amt = {6} , item_amt = {7} where coop_id ={0} and store_id={1} and slip_no ={2} and itemtype_code = {3} and seq_no ={4} and product_no= {5}";
                            Sqlupdet = WebUtil.SQLFormat(Sqlupdet, coop_id, store_id, slipno, slipttype, seq_no, productno, _calvat, _calitem);
                            WebUtil.QuerySdt(Sqlupdet);
                        }
                    }

                    string Sqlsumslip = @"select sum(d.itemnet_amt) as itemnet_amt , sum(d.vat_amt) vat_amt from ststockslip s left join ststockslipdet d on s.coop_id = d.coop_id and d.store_id = s.store_id and d.slip_no = s.slip_no where s.coop_id ={0} and s.store_id={1} and s.slip_status = 1 and s.slip_date between {2} and {3} and s.sliptype_code = {4} and s.expense_code = {5} ";
                     Sqlsumslip = WebUtil.SQLFormat(Sqlsumslip, coop_id, store_id, start_date, end_date, slipttype, expense_code);
                     Sdt dtcal = WebUtil.QuerySdt(Sqlsumslip);
                     if (dtcal.Next())
                     {
                         _calsumnet = dtcal.GetDecimal("itemnet_amt");
                         sum_DetVat = dtcal.GetDecimal("vat_amt");
                     }
                    

                    //ลบวินค้าที่ไม่คิดภาษี
                    _calsumnet -= itemnet_novat;
                    sum_SlipVat = Math.Round(_calsumnet * 7 / 107, 2);

                    if (sum_SlipVat != sum_DetVat)
                    {
                        decimal cal_vat = 0, _cal_type = 0;
                        if (sum_SlipVat >= sum_DetVat)
                        {
                            _cal_type = 1;
                            cal_vat = sum_SlipVat - sum_DetVat;
                        }
                        else
                        {
                            _cal_type = 2;
                            cal_vat = sum_DetVat - sum_SlipVat;
                        }

                        string Sqlgen = @"select d.slip_no,m.product_no,d.seq_no,d.item_qty item_qty,d.item_amt item_amt,d.vat_amt vat_amt,d.itemnet_amt itemnet_amt  from ststockslip s 
left join  ststockslipdet d  on s.coop_id = d.coop_id and s.store_id = d.store_id and s.slip_no = d.slip_no  and d.slipdet_status = 1
left join ststockmaster m on m.coop_id = d.coop_id and m.store_id = d.store_id and m.product_no = d.product_no
 where d.coop_id ={0} and d.store_id={1} and slip_date between {2} and  {3} and s.sliptype_code ={4}  and s.slip_status = 1 and m.vat_flag = 1 and s.expense_code = {5} and d.itemnet_amt > 0
order by item_qty desc,slip_no,seq_no,product_no";
                        Sqlgen = WebUtil.SQLFormat(Sqlgen, coop_id, store_id, start_date, end_date, slipttype, expense_code);
                        Sdt dtgen = WebUtil.QuerySdt(Sqlgen);
                        while (dtgen.Next())
                        {
                            if (cal_vat > 0)
                            {
                                string G_slip_no = "", G_product_no = "";
                                decimal G_item_amt = 0, G_vat_amt = 0, G_seq_no = 0;
                                G_seq_no = dtgen.GetDecimal("seq_no");
                                G_slip_no = dtgen.GetString("slip_no");
                                G_product_no = dtgen.GetString("product_no");
                                G_item_amt = dtgen.GetDecimal("item_amt");
                                G_vat_amt = dtgen.GetDecimal("vat_amt");
                                if (_cal_type == 1)
                                {
                                    G_vat_amt = G_vat_amt + Convert.ToDecimal(0.01);
                                    G_item_amt = G_item_amt - Convert.ToDecimal(0.01);
                                }
                                else if (_cal_type == 2)
                                {
                                    G_vat_amt = G_vat_amt - Convert.ToDecimal(0.01);
                                    G_item_amt = G_item_amt + Convert.ToDecimal(0.01);
                                }

                                string Sqlupgen = @"update ststockslipdet set item_amt = {4} , vat_amt = {5} where coop_id ={0} and store_id={1} and slip_no ={2} and product_no = {3} and seq_no = {6} ";
                                Sqlupgen = WebUtil.SQLFormat(Sqlupgen, coop_id, store_id, G_slip_no, G_product_no, G_item_amt, G_vat_amt, G_seq_no);
                                WebUtil.QuerySdt(Sqlupgen);
                                //1 รอบลด 0.01 by god
                                cal_vat = cal_vat - Convert.ToDecimal(0.01);
                            }
                        }
                    }
                }
          
            //gen เงินในใบเสร็จ by god
                string Sqlgenslip = @"select s.slip_no, s.slip_amt as slip1_amt ,s.vat_amt as vat1_amt,s.slipnet_amt as slipnet1_amt,
sum(d.item_amt) as item2_amt , sum(d.vat_amt) vat2_amt ,sum(d.itemnet_amt) as itemnet2_amt from ststockslip s 
left join ststockslipdet d on s.coop_id = d.coop_id and d.store_id = s.store_id and d.slip_no = s.slip_no
 where s.coop_id ={0}and s.store_id={1} and s.slip_status = 1 
and s.slip_date between {2} and {3}  and s.sliptype_code = {4} and s.expense_code = {5} group by  s.slip_no , s.slip_amt ,s.vat_amt ,s.slipnet_amt";
                Sqlgenslip = WebUtil.SQLFormat(Sqlgenslip, coop_id, store_id, start_date, end_date, slipttype, expense_code);
                Sdt dtslip = WebUtil.QuerySdt(Sqlgenslip);
                while (dtslip.Next())
                {
                    string slip_no = "";
                    decimal slip1_amt = 0, vat1_amt = 0, slipnet1_amt = 0, item2_amt = 0, vat2_amt = 0, itemnet2_amt = 0;
                    slip_no = dtslip.GetString("slip_no");
                    slip1_amt = dtslip.GetDecimal("slip1_amt");
                    vat1_amt = dtslip.GetDecimal("vat1_amt");
                    slipnet1_amt = dtslip.GetDecimal("slipnet1_amt");
                    item2_amt = dtslip.GetDecimal("item2_amt");
                    vat2_amt = dtslip.GetDecimal("vat2_amt");
                    itemnet2_amt = dtslip.GetDecimal("itemnet2_amt");

                    if (slip1_amt != item2_amt || vat1_amt != vat2_amt || slipnet1_amt != itemnet2_amt)
                    {
                        string Sqlupgen = @"update ststockslip set slip_amt = {3} , vat_amt = {4} , slipnet_amt = {5} where coop_id ={0} and store_id={1} and slip_no ={2}  ";
                        Sqlupgen = WebUtil.SQLFormat(Sqlupgen, coop_id, store_id, slip_no, item2_amt, vat2_amt, itemnet2_amt);
                        WebUtil.QuerySdt(Sqlupgen);
                    }

                }
            
            #endregion
        }

        public void GenStatement(string coop_id,string CONTRACT_NO)
        {
            
  
                WebUtil.QuerySdt(" delete from STCONTRACTSLIP ");
                Sdt ds = WebUtil.QuerySdt(" select * from stcontractlimitdet where contract_no = '" + CONTRACT_NO + "' and slip_status <> -9 order by slip_date,slip_no,ref_incdecno asc ");
                int i = 1; decimal principal_balance = 0;
                while (ds.Next())
                {
                    if (ds.GetString("DEBTINCDEC_TYPE") == "001")
                    {
                        principal_balance += ds.GetDecimal("PRINCIPAL_PAYMENT");
                    }
                    else if (ds.GetString("DEBTINCDEC_TYPE") == "B/F")
                    {
                        principal_balance = ds.GetDecimal("BFPRINCIPAL_ARREAR");
                    }
                    else
                    { //ชำระ
                        principal_balance = principal_balance - ds.GetDecimal("PRINCIPAL_PAYMENT");
                    }
                    string sql = @" insert into STCONTRACTSLIP( COOP_ID  ,   CONTRACT_NO ,SLIP_NO,REF_INCDECNO,SEQ_NO  , DEBT_NO , SLIP_DATE  ,
                ENTRY_ID , CALINT_FROM  , CALINT_TO  ,PRINCIPAL_PAYMENT , PRINCIPAL_BALANCE , INT_PAYMENT , FINE_PAYMENT ,
                INTYEAR_ARREAR_AMT , FINEYEAR_ARREAR_AMT , FINE_ARREAR_AMT , FINE_PERIOD_AMT , INTEREST_ARREAR_AMT , 
                INTEREST_PERIOD_AMT , PRINCIPAL_ARREAR_AMT , BF_INTPERIOD , BF_FINEPERIOD , BFINTYEAR_ARREAR , BFFINEYEAR_ARREAR 
                , BFINT_ARREAR , BFFINE_ARREAR , BFPRINCIPAL_ARREAR,DEBTINCDEC_TYPE,ref_slipno,calfine_from,calfine_to) values 
                ({0},{1},{2},{3},{4}, 
                {5},{6},{7},{8},{9},{10},{11},
                {12},{13},{14},{15},{16},
                {17},{18},{19},{20},{21},{22},
                {23},{24},{25},{26},{27},{28},{29},{30},{31})";


                    sql = WebUtil.SQLFormat(sql, coop_id, CONTRACT_NO, ds.GetString("SLIP_NO"), ds.GetString("REF_INCDECNO"), i, ds.GetString("DEBT_NO"), ds.GetDate("SLIP_DATE"),
                     ds.GetString("ENTRY_ID"), ds.GetDate("CALINT_FROM"), ds.GetDate("CALINT_TO"), ds.GetDecimal("PRINCIPAL_PAYMENT"), principal_balance, ds.GetDecimal("INT_PAYMENT"), ds.GetDecimal("FINE_PAYMENT"),
                    ds.GetDecimal("INTYEAR_ARREAR_AMT"), ds.GetDecimal("FINEYEAR_ARREAR_AMT"), ds.GetDecimal("FINE_ARREAR_AMT"), ds.GetDecimal("FINE_PERIOD_AMT"), ds.GetDecimal("INTEREST_ARREAR_AMT"),
                    ds.GetDecimal("INTEREST_PERIOD_AMT"), ds.GetDecimal("PRINCIPAL_ARREAR_AMT"), ds.GetDecimal("BF_INTPERIOD"), ds.GetDecimal("BF_FINEPERIOD"), ds.GetDecimal("BFINTYEAR_ARREAR"), ds.GetDecimal("BFFINEYEAR_ARREAR")
                    , ds.GetDecimal("BFINT_ARREAR"), ds.GetDecimal("BFFINE_ARREAR"), ds.GetDecimal("BFPRINCIPAL_ARREAR"), ds.GetString("DEBTINCDEC_TYPE"), ds.GetString("ref_slipno"), ds.GetDate("CALFINE_FROM"), ds.GetDate("CALFINE_TO"));
                    WebUtil.QuerySdt(sql);
                    i++;
                }
                ds.Clear();
            
            
        }


        public decimal GetCreditFlag(string coop_id, string debttype_code)
        {
            decimal credit_flag = 0;
            if (debttype_code == "001")
            {
                string sql = @"select creditcre_flag from ststockconstant where coop_id={0}";
                sql = WebUtil.SQLFormat(sql, coop_id);
                Sdt dt = WebUtil.QuerySdt(sql);
                if (dt.Next())
                {
                    credit_flag = dt.GetDecimal("creditcre_flag");
                }
            }
            else if (debttype_code == "002")
            {
                string sql = @"select creditdebt_flag from ststockconstant where coop_id={0}";
                sql = WebUtil.SQLFormat(sql, coop_id);
                Sdt dt = WebUtil.QuerySdt(sql);
                if (dt.Next())
                {
                    credit_flag = dt.GetDecimal("creditdebt_flag");
                }
            }
            return credit_flag;
        }

        public string of_getsitecode(string coopcontrol_id)
        {
            string site_code = "";
            Sqlse = @"select site_code from ststockconstant where coop_id = {0}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol_id);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (ta.Next())
            {
                site_code = ta.GetString("site_code").Trim();
            }
            return site_code;
        }

        public decimal of_getvat_store(string coop_id,string store_id)
        {
            decimal vat_store = 0;
            Sqlse = @"select vat_store from ststore where coop_id = {0} and store_id = {1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coop_id, store_id);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (ta.Next())
            {
                vat_store = ta.GetDecimal("vat_store");
            }
            return vat_store;
        }

        public string of_genarate_vatstore(string coop_id, string store_id)
        {
            string closeday_avgvat = "";
            Sqlse = @"select closeday_avgvat from ststore where coop_id = {0} and store_id = {1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coop_id, store_id);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (ta.Next())
            {
                closeday_avgvat = ta.GetString("closeday_avgvat");
            }
            return closeday_avgvat;
        }

        public void of_update_store(string coop_id, string store_id, string APPLICATION,string USERNAME,string TOKEN_ID)
        {
            string strUsso = @"UPDATE SSOTOKEN SET STORE_ID ={4}  WHERE APPLICATION ={3} AND COOP_ID ={0} AND USERNAME ={1} AND TOKEN_ID ={2}";
            strUsso = WebUtil.SQLFormat(strUsso, coop_id, USERNAME, TOKEN_ID, APPLICATION,store_id);
            WebUtil.Query(strUsso);         
        }
        
    }
}