using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net.Sockets;
using System.Net.Security;
using System.IO;
using DataLibrary;
using CoreSavingLibrary;

namespace CoreSavingLibrary
{
    public class IServiceTrading
    {
        public static int CheckUserSsotoken()
        {
            int status = 8;
            string sql = @"select * from ssotoken where user_name = {}";
            return status;
        }

        /// <summary>
        /// ดึงชื่อผู้ใช้งาน
        /// </summary>
        /// <param name="coopcontrol">เลขที่สหกรณ์</param>
        /// <param name="username">user ที่ทำการ login</param>
        /// <returns></returns>
        public static string of_getfullnameuser(string coopcontrol, string username)
        {
            string fullname = "";
            try
            {
                string Sqlse = "select full_name from amsecusers where coop_id = {0} and user_name = {1}";
                Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol, username);
                Sdt dt = WebUtil.QuerySdt(Sqlse);
                if (!dt.Next()) throw new Exception("ไม่มีพบข้อมูลผู้ใช้งาน " + username + " กรุณาติดต่อโปรแกรมเมอร์");
                fullname = dt.GetString("full_name");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return fullname;
        }

        /// <summary>
        /// เช็ควันที่ ณ ปัจจุบันคลังสินค้า
        /// </summary>
        /// <param name="coopcontrol">เลขที่สหกรณ์</param>
        /// <param name="storeid">คลังสินค้า</param>
        /// <returns></returns>
        public static DateTime WorkdateStore(string coopcontrol, string storeid)
        {
            DateTime workdateStore = new DateTime();
            try
            {
                string Sqlse = "select work_date from ststore where coop_id = {0} and store_id = {1}";
                Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol, storeid);
                Sdt dt = WebUtil.QuerySdt(Sqlse);
                if (!dt.Next()) throw new Exception("ไม่มีพบข้อมูลวันที่ทำการคลัง " + storeid + " กรุณาติดต่อโปรแกรมเมอร์");
                workdateStore = dt.GetDate("work_date");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return workdateStore;
        }

        /// <summary>
        /// เช็ควันที่ ณ ครั้งสุดท้ายคลังสินค้า
        /// </summary>
        /// <param name="coopcontrol"></param>
        /// <param name="storeid"></param>
        /// <returns></returns>
        public static DateTime LastWorkdateStore(string coopcontrol, string storeid)
        {
            DateTime lastworkdateStore = new DateTime();
            try
            {
                string Sqlse = "select lastwork_date from ststore where coop_id = {0} and store_id = {1}";
                Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol, storeid);
                Sdt dt = WebUtil.QuerySdt(Sqlse);
                if (!dt.Next()) throw new Exception("ไม่มีพบข้อมูลวันที่ทำการคลัง " + storeid + " กรุณาติดต่อโปรแกรมเมอร์");
                lastworkdateStore = dt.GetDate("lastwork_date");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lastworkdateStore;
        }

        /// <summary>
        /// เช็คคิดภาษีตามคลังสินค้า
        /// </summary>
        /// <param name="coopcontrol">เลขที่สหกรณ์</param>
        /// <param name="storeid">คลังสินค้า</param>
        /// <returns></returns>
        public static decimal of_getvatfromstore(string coopcontrol, string storeid)
        {
            decimal vattype = 0;
            try
            {
                string Sqlse = "select vat_type from ststore where coop_id = {0} and store_id = {1}";
                Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol, storeid);
                Sdt dt = WebUtil.QuerySdt(Sqlse);
                if (!dt.Next()) throw new Exception("ไม่มีพบข้อมูลการคิดภาษีคลัง " + storeid + " กรุณาติดต่อโปรแกรมเมอร์");
                vattype = dt.GetDecimal("vat_type");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return vattype;
        }

        /// <summary>
        /// ดึงรูปแบบเลขที่เอกสารตามประเภทรายการ
        /// </summary>
        /// <param name="coopcontrol_no">เลขที่สหกรณ์</param>
        /// <param name="sliptype_code">ประเภทรายการ</param>
        /// <returns></returns>
        public static string of_getdoccontrol(string coopcontrol_no, string sliptype_code)
        {
            string document_code = "";
            string Sqlse = @"select document_code from stucfsliptype where coop_id = {0} and sliptype_code = {1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol_no, sliptype_code);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (!ta.Next()) throw new Exception("ไม่มีพบข้อมูลรูปแบบเลขที่เอกสารประเภททำรายการ " + sliptype_code + " กรุณาติดต่อโปรแกรมเมอร์");
            document_code = ta.GetString("document_code").Trim();
            return document_code;
        }

        /// <summary>
        /// ดึงรูปแบบงานพิมพ์
        /// </summary>
        /// <param name="coopcontrolNo">เลขที่สหกรณ์</param>
        /// <param name="storeId">คลังสินค้า</param>
        /// <returns></returns>
        public static string of_gettofromaccidsliptype(string coopcontrol_no, string sliptype_code, string expense_code, string store_id)
        {
            /*string accid_cash = "", accid_lon = "", accid_trn = "", tofromacc_id = "";
            string Sqlse = @"select accid_cash, accid_lon, accid_trn from stucfsliptype where coop_id = {0} and sliptype_code = {1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol_no, sliptype_code);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (ta.Next())
            {
                accid_cash = ta.GetString("accid_cash");
                accid_lon = ta.GetString("accid_lon");
                accid_trn = ta.GetString("accid_trn");
            }

            if (expense_code == "CSH")
            {
                tofromacc_id = accid_cash;
            }
            else if (expense_code == "LON")
            {
                tofromacc_id = accid_lon;
            }
            else if (expense_code == "TRN")
            {
                tofromacc_id = accid_trn;
            }
            return tofromacc_id;*/
            string  tofromacc_id = "";
            string Sqlse = @"select account_id from cmucftofromaccid where applgroup_code = 'MRK' and sliptype_code = {3} and bizz_code = {2} and moneytype_code = {1} and coop_id = {0} ";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol_no, expense_code, store_id ,sliptype_code);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (ta.Next())
            {
                tofromacc_id = ta.GetString("account_id");
            }
            return tofromacc_id;
        }

        /// <summary>
        /// ดึงค่าคงที่ SLIPTYPESIGN_FLAG ตาม STUCFSLIPTYPE
        /// </summary>
        /// <param name="coopcontrol_no"></param>
        /// <param name="sliptype_code"></param>
        /// <returns></returns>
        public static decimal of_getsliptypesignflag(string coopcontrol_no, string sliptype_code)
        {
            decimal sliptypesign_flag = 0;
            string Sqlse = @"select sliptypesign_flag from stucfsliptype where coop_id = {0} and sliptype_code = {1}";
            Sqlse = WebUtil.SQLFormat(Sqlse, coopcontrol_no, sliptype_code);
            Sdt ta = WebUtil.QuerySdt(Sqlse);
            if (!ta.Next()) throw new Exception("ไม่มีพบข้อมูลรูปแบบเลขที่เอกสารประเภททำรายการ " + sliptype_code + " กรุณาติดต่อโปรแกรมเมอร์");
            sliptypesign_flag = ta.GetDecimal("sliptypesign_flag");
            return sliptypesign_flag;
        }

        public static string of_chkproductset(string coopcontrol_no, string product_no)
        {
            string result = "";
            string sqlck = @"select * from stproductmasterset where coop_id = {0} and product_no = {1}";
            sqlck = WebUtil.SQLFormat(sqlck, coopcontrol_no, product_no);
            Sdt ta = WebUtil.QuerySdt(sqlck);
            if (!ta.Next()) throw new Exception("ERROR!!!: Product is have product set >>> สินค้า " + product_no + " ไม่มีรายการย่อยให้ตัด stock กรุณาตรวจสอบ");
            result = ta.GetString("product_no_set");
            return result;
        }

        // as_productno		= รหัสสินค้าที่จะคิดทุนเฉลี่ย
        // adc_itemqty		= จำนวน ณ ที่ซื้อปัจจุบัน
        // adc_productprice	= ราคาต่อหน่อย ณ ที่ซื้อปัจจุบัน
        /// <summary>
        /// ฟังค์ชั่น คำนวณ ทุนเฉลี่ย
        /// </summary>
        /// <param name="coop_id">สาขา</param>
        /// <param name="store_id">คลัง</param>
        /// <param name="produc_no">รหัสสินค้า</param>
        /// <param name="item_qty">จำนวนสินค้าที่รับเข้า</param>
        /// <param name="product_price">ราคาต่อชิ้น ไม่รวมภาษีมูลค่าเพิ่ม</param>
        /// <returns></returns>
        public static decimal of_calavgcost(string coop_id, string store_id, string produc_no, decimal item_qty, decimal product_price)
        {
            string sqlsecalcost = "";
            decimal costavg_amt = 0, sumlotqty_amt = 0, sumproduct_price = 0, calavgcost_flag = 0, decimal_type = 0;
            DateTime startcost_date = new DateTime();
            ///ดึงค่าคงที่  startcost_date, costavg_flag
            string secont = @"select * from ststockconstant where coop_id = {0}";
            secont = WebUtil.SQLFormat(secont, coop_id);
            Sdt ta = WebUtil.QuerySdt(secont);
            if (ta.Next())
            {
                calavgcost_flag = ta.GetDecimal("costavg_flag");
                startcost_date = ta.GetDate("startcost_date");
                decimal_type = ta.GetDecimal("decimal_type");
            }

            // ถ้าไม่มีค่าให้กำหนดเป็นวันที่ เริ่มต้นปีบัญชี
            if(startcost_date == null)
            {


            }

            switch(calavgcost_flag.ToString())
            {
                case "1":   // คิดทุนเฉลี่ยจากยอดสินค้าไม่รวมภาษี แยกคลัง
                    sqlsecalcost = @"select sum( a.balance_qty) as sumlotqty, sum( a.costavg_amt ) as sumproductprice
		                from		ststockmaster a
		                where	a.product_no 		= {2}
		                and		a.coop_id   		= {0}
		                and		a.store_id			= {1}";
                    sqlsecalcost = WebUtil.SQLFormat(sqlsecalcost, coop_id, store_id, produc_no);
                    ta = WebUtil.QuerySdt(sqlsecalcost);
                    if(ta.Next())
                    {
                        sumlotqty_amt = ta.GetDecimal("sumlotqty");
                        sumproduct_price = ta.GetDecimal("sumproductprice");
                    }
                    break;
                case "2":   // คิดทุนเฉลี่ยจากยอดสินค้ารวมภาษี แยกคลัง
                    sqlsecalcost = @"select sum( a.item_qty) as sumlotqty, sum( a.itemnet_amt ) as sumproductprice
		                from		ststockslipdet a
		                where	a.slip_no in ( select b.refdoc_no from ststocklot b 
                                                where a.product_no = b.product_no and a.coop_id = b.coop_id 
                                                and a.store_id = b.store_id
                                                and b.lot_date >= {3}
                                                and b.stocklot_status <> -9 )
		                and		a.product_no 		= {2}
		                and		a.coop_id   		= {0}
		                and		a.store_id			= {1}";
                    sqlsecalcost = WebUtil.SQLFormat(sqlsecalcost, coop_id, store_id, produc_no, startcost_date.ToString("yyyy-MM-dd"));
                    ta = WebUtil.QuerySdt(sqlsecalcost);
                    if(ta.Next())
                    {
                        sumlotqty_amt = ta.GetDecimal("sumlotqty");
                        sumproduct_price = ta.GetDecimal("sumproductprice");
                    }
                    break;
                case "3": //คิดทุนสินค้า แบบรวมคลัง ยอดปัจจุบัน 
                    sqlsecalcost = @"select sum(balance_qty) as sumlotqty, sum(costavg_amt) / count(1) as sumproductprice
                                        from ststockmaster 
                                        where product_no = {1}
                                        and coop_id = {0}";
                    sqlsecalcost = WebUtil.SQLFormat(sqlsecalcost, coop_id, produc_no);
                    ta = WebUtil.QuerySdt(sqlsecalcost);
                    if(ta.Next())
                    {
                        sumlotqty_amt = ta.GetDecimal("sumlotqty");
                        sumproduct_price = ta.GetDecimal("sumproductprice");
                    }
                    sumproduct_price = sumlotqty_amt * sumproduct_price;
                    break;
            }

            // หาจำนวนสินค้าทั้งหมด ณ lot นี้ด้วย
            sumproduct_price = (sumproduct_price * sumlotqty_amt) + (item_qty * product_price);	// ราคาทุนรวมทั้งหมดรวมทั้ง lot นี้ด้วย
            //sumproduct_price = sumproduct_price + (item_qty * product_price);
            sumlotqty_amt    = sumlotqty_amt + item_qty;                         // จำนวนสินค้าทั้งหมดรวมทั้ง lot นี้ด้วย
            
            if (sumlotqty_amt == 0 )
            {
	            costavg_amt = 0;
            }
            else
            {
	            costavg_amt = sumproduct_price / sumlotqty_amt;     // ราคาทุนเฉลี่ย
            }

            costavg_amt = Math.Round(costavg_amt, 2, MidpointRounding.AwayFromZero);

            return costavg_amt;
        }

        public static Boolean of_calallcostavgday(string coopId, string storeId, DateTime sworkdate)
        {
            Boolean result = true;
            String sql = @"select 0 as mainseq, sb.coop_id, sb.store_id, sb.product_no, 'CLSDAY' as slip_no, 0 seq_no, sb.lot_no, 
                            'CSL' as sliptype_code, sb.working_date as slip_date, sb.balance_qty as item_qty, sb.costavg_amt as item_amt, 
                            sb.balance_qty as balance_amt, 1 as stockcard_status, sb.costavg_amt  as cost_amt, 0 as before_meter, 0 as after_meter, 
                            '' as refdoc_no, 0 as balance_latex, 0 as latex_qty ,  sb.costavg_amt
                        from strptdailybalance sb 
                        where sb.coop_id = {0} and sb.store_id = {1} 
                            and sb.working_date = (select max(working_date) from strptdailybalance where coop_id = '500001' and store_id = '001'
                                                    and working_date <= date_add( {2} , INTERVAL -1 DAY) )
                        union 
                        select 1 as mainseq, sc.coop_id, sc.store_id, sc.product_no, sc.slip_no, sc.seq_no, sc.lot_no, sc.sliptype_code, sc.slip_date, 
	                        if(sc.item_qty = null, 0, sc.item_qty) as item_qty, 
	                        ifnull(if(st.sliptypesign_flag = 1, sd.item_amt, sc.item_amt), sc.item_amt) as item_amt, 
	                        if(sc.balance_amt = null, 0,sc.balance_amt) as balance_amt, sc.stockcard_status, 
                            if(st.sliptypesign_flag = 1, round(ifnull(sd.item_amt, sc.item_amt*sc.item_qty) / sc.item_qty, 2), sc.cost_amt) as cost_amt, 
	                        sc.before_meter, sc.after_meter, sc.refdoc_no, sc.balance_latex, sc.latex_qty, sc.costavg_amt
                        from ststockcard sc 
	                        join stucfsliptype st on sc.sliptype_code = st.sliptype_code and st.coop_id = {0}
	                        left join ststockslipdet sd on sc.coop_id = sd.coop_id and sc.store_id = sd.store_id and sc.product_no = sd.product_no 
                            and sc.slip_no = sd.slip_no and sc.sliptype_code = sd.itemtype_code
                        where sc.coop_id = {0} and sc.store_id = {1} and sc.slip_date >= {2} 
                        order by product_no, mainseq, seq_no";
            sql = WebUtil.SQLFormat(sql, coopId, storeId, sworkdate);
            Sdt ta = WebUtil.QuerySdt(sql);
            DateTime slip_date = new DateTime();
            string slip_no = "", sliptype_code = "", product_no = "", oldproduct_no = "";
            decimal seq_no = 0, item_qty = 0, item_amt = 0, cost_amt = 0, costavg_amt = 0, newcostavg_amt = 0, balance_amt = 0, stockcard_status = 0;
            decimal bfitembal = 0, bfcostavg = 0, oldseq_no = 0;
            while(ta.Next())
            {
                try
                {
                    slip_date = ta.GetDate("slip_date");
                    slip_no = ta.GetString("slip_no");
                    product_no = ta.GetString("product_no");
                    seq_no = ta.GetDecimal("seq_no");
                    sliptype_code = ta.GetString("sliptype_code");
                    item_qty = ta.GetDecimal("item_qty");
                    try { item_amt = ta.GetDecimal("item_amt"); }
                    catch { item_amt = 0; }
                    try { cost_amt = ta.GetDecimal("cost_amt"); }
                    catch { cost_amt = 0; }
                    try { costavg_amt = ta.GetDecimal("costavg_amt"); }
                    catch { costavg_amt = 0; }
                    balance_amt = ta.GetDecimal("balance_amt");
                    stockcard_status = ta.GetDecimal("stockcard_status");
                    if (product_no == oldproduct_no)
                    {
                        if (sliptype_code == "BUY" || sliptype_code == "TRI" || sliptype_code == "SRP" || sliptype_code == "REC" || sliptype_code == "RTRO" || sliptype_code == "RSDB" || sliptype_code == "RSEL") //เนื่องจาก sql ทำต้นทุนจาก slip ให้เสร็จแล้ว
                        {
                            if (sliptype_code == "REC")
                            {
                                item_amt = OfgetCostRec(coopId, storeId, product_no, slip_no);
                                item_amt = item_amt * item_qty;
                            }
                            else if (sliptype_code == "RTRO" || sliptype_code == "RSDB")
                            {
                                item_amt = item_amt * item_qty;
                            }
                            else if (sliptype_code == "RSEL")
                            {
                                item_amt = OfgetCostSlipcclSel(coopId, storeId, product_no, slip_no);
                                item_amt = item_amt * item_qty;
                            }
                            //กรณีที่ลำดับซ้ำกันให้ทำการตรวจสอบว่าจำนวนตรงกับ slip หรือป่าว
                            decimal itemtemp_amt = OfCheckItemAmtSameSeq(slip_no, product_no, storeId, item_qty);
                            if (itemtemp_amt != item_amt && sliptype_code != "REC")
                            {
                                continue;
                            }
                            if (itemtemp_amt == item_amt && sliptype_code == "SRP" && oldseq_no == seq_no)
                            {
                                continue;
                            }
                            if (bfitembal < 0)
                            {
                                if (balance_amt < 0)
                                {
                                    newcostavg_amt = cost_amt;
                                }
                                else
                                {
                                    //กรณีที่แถวก่อนหน้าติดลบ ให้ทำการเอาทุนใหม่ หารด้วยจำนวนของแถวที่รับเข้าแถวนั้น
                                    newcostavg_amt = Math.Round(item_amt / item_qty, 2, MidpointRounding.AwayFromZero);
                                }
                            }
                            else
                            {
                                if (balance_amt < 0)
                                {
                                    newcostavg_amt = cost_amt;
                                }
                                else if (balance_amt > 0)
                                {
                                    newcostavg_amt = Math.Round(((bfcostavg * bfitembal) + item_amt) / balance_amt, 2, MidpointRounding.AwayFromZero);
                                }
                            }
                        }
                        else if (sliptype_code == "RBUY" || sliptype_code == "SEN" || sliptype_code == "RTRI" || sliptype_code == "RSRP") //กรณีมีการยกเลิกซื้อและส่งคืนจะต้องทำการคำนวณต้นทุนใหม่
                        {
                            item_amt = item_amt * item_qty;
                            if (sliptype_code == "RBUY")
                            {
                                item_amt = OfCheckItemAmtSameSeq(slip_no, product_no, storeId, item_qty);
                            }
                            if (balance_amt == 0)
                            {
                                newcostavg_amt = Math.Round(((bfcostavg * bfitembal) - item_amt), 2, MidpointRounding.AwayFromZero);
                            }
                            else
                            {
                                newcostavg_amt = Math.Round((((bfcostavg * bfitembal) - item_amt) / balance_amt), 2, MidpointRounding.AwayFromZero);
                            }
                            if (newcostavg_amt < 0) //กรณีมีติดลบให้ set เป็น 0
                            {
                                newcostavg_amt = 0;
                            }
                        }
                        else
                        {
                            newcostavg_amt = bfcostavg;
                        }
                        if (sliptype_code != "CSL")
                        {
                            string sqlup = @"update ststockcard set costavg_amt = {0} where product_no = {1} and store_id = {2} and seq_no = {3} ";
                            sqlup = WebUtil.SQLFormat(sqlup, newcostavg_amt, product_no, storeId, seq_no);
                            Sdt ta2 = WebUtil.QuerySdt(sqlup);
                        }
                    }
                    else
                    {
                        if (sliptype_code == "BUY" || sliptype_code == "TRI" || sliptype_code == "SRP" || sliptype_code == "REC") //เนื่องจาก sql ทำต้นทุนจาก slip ให้เสร็จแล้ว
                        {
                            newcostavg_amt = cost_amt;
                        }
                        else
                        {
                            newcostavg_amt = costavg_amt;
                        }
                        if (sliptype_code != "CSL")
                        {
                            string sqlup = @"update ststockcard set costavg_amt = {0} where product_no = {1} and store_id = {2} and seq_no = {3} ";
                            sqlup = WebUtil.SQLFormat(sqlup, newcostavg_amt, product_no, storeId, seq_no);
                            Sdt ta3 = WebUtil.QuerySdt(sqlup);
                        }

                    }
                    oldproduct_no = product_no;
                    oldseq_no = seq_no;
                    bfitembal = balance_amt;
                    bfcostavg = newcostavg_amt;

                }
                catch (Exception ex)
                {
                    result = false;
                }
            }

            string upcost = @"update  ststockmaster sm
                         join ( select sc.coop_id, sc.store_id, sc.product_no, sc.balance_amt, sc.costavg_amt from  ststockcard sc
                                            join ( select store_id, product_no, max(seq_no) as seq_no from ststockcard 
		                                            where store_id = {1} and coop_id = {0}
		                                            group by store_id, product_no ) sct
                                            on sc.store_id = sct.store_id and sc.product_no = sct.product_no and sc.seq_no = sct.seq_no ) scc
                        on sm.coop_id = scc.coop_id and sm.store_id = scc.store_id and sm.product_no = scc.product_no and sm.store_id = {1}
                        and sm.costavg_amt <> scc.costavg_amt
                        set sm.costavg_amt = scc.costavg_amt, sm.cost_amt = scc.costavg_amt ";
            upcost = WebUtil.SQLFormat(upcost, coopId, storeId);
            Sdt ta4 = WebUtil.QuerySdt(upcost);
            result = true;
            return result;
        }

        public static decimal OfgetCostRec(string coop_id, string store_id, string product_no, string slip_no)
        {
            decimal itemamt = 0;
            string sql = @"select cslip.costavg_amt as costavg_amt from ststockcard cslip 
                            join ststockcard crec on cslip.coop_id = crec.coop_id and cslip.store_id = crec.store_id and
                            cslip.product_no = crec.product_no and cslip.slip_no = crec.refdoc_no and crec.coop_id = {0} and 
                            crec.store_id = {1} and crec.product_no = {2} and crec.slip_no = {3}";
            sql = WebUtil.SQLFormat(sql, coop_id, store_id, product_no, slip_no);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                itemamt = dt.GetDecimal("costavg_amt");
            }
            return itemamt;
        }

        public static decimal OfgetCostSlipcclSel(string coop_id, string store_id, string product_no, string slip_no)
        {
            decimal itemamt = 0;
            string sql = @"select distinct cslip.costavg_amt as costavg_amt from ststockcard cslip 
                            join ststockcard crec on cslip.coop_id = crec.coop_id and cslip.store_id = crec.store_id and
                                cslip.product_no = crec.product_no and cslip.slip_no = crec.slip_no and cslip.item_qty = crec.item_qty and crec.coop_id = {0}
                                and crec.store_id = {1} and crec.product_no = {2} and crec.slip_no = {3}
                            where cslip.sliptype_code = 'SEL'";
            sql = WebUtil.SQLFormat(sql, coop_id, store_id, product_no, slip_no);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                itemamt = dt.GetDecimal("costavg_amt");
            }
            return itemamt;
        }

        public static decimal OfCheckItemAmtSameSeq(string slipNo, string productNo, string storeId, decimal ItemQty)
        {
            decimal itemamt = 0;
            string sql = @"select item_amt from ststockslipdet where slip_no = {0} and product_no = {1} and store_id = {2} and item_qty = {3} ";
            sql = WebUtil.SQLFormat(sql, slipNo, productNo, storeId, ItemQty);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                itemamt = dt.GetDecimal("item_amt");
            }
            return itemamt;
        }

        /// <summary>
        /// 
        /// 
        /// dayinyear_type = 2 : คิดค่าปรับตามปีที่กำหนดในสินเชื่อ
        /// </summary>
        /// <param name="coop_control"></param>
        /// <param name="store_id"></param>
        /// <param name="fee_rate"></param>
        /// <param name="loanitemnet_amt"></param>
        /// <param name="due_date"></param>
        /// <param name="slip_date"></param>
        /// <returns>จำนวนเงินที่คิดค่าปรับ</returns>
        public static decimal of_calfeeamt(string coop_control, string store_id, decimal fee_rate, decimal loanitemnet_amt, DateTime due_date, DateTime slip_date, int rounddec)
        {
            decimal feeAmt = 0, dayinyear_type = 0, dayinyear = 0;
            int current_accyear = 0, overdue_amt = 0;
            fee_rate = fee_rate / 100; //อัตราค่าปรับ
            string sqlstcont = @"select dayinyear_type, dayinyear, current_accyear from ststockconstant 
                                join ststore on ststore.coop_control = ststockconstant.coop_id and store_id = {1}
                                where ststockconstant.coop_id = {0}";
            sqlstcont = WebUtil.SQLFormat(sqlstcont, coop_control, store_id);
            Sdt ta = WebUtil.QuerySdt(sqlstcont);
            if (ta.Next())
            {
                dayinyear_type = ta.GetDecimal("dayinyear_type");
                dayinyear = ta.GetDecimal("dayinyear");
                current_accyear = ta.GetInt32("current_accyear") - 543 ;
            }
            if (dayinyear_type == 0) //dayinyear_type = 0 : คิดค่าปรับตามปีปฏิทินจริง
            {
                dayinyear = Convert.ToDecimal(new DateTime(slip_date.Year, 12, 31).DayOfYear);
            }
            else if (dayinyear_type == 1) //dayinyear_type = 1 : คิดค่าปรับตามปีปฏิทินรอบบัญชี 
            {
                dayinyear = Convert.ToDecimal(new DateTime(current_accyear, 12, 31).DayOfYear);
            }
            else if (dayinyear_type == 2) //dayinyear_type = 2 : คิดค่าปรับตามปีที่กำหนดใน dayinyear
            {

            }
            else if (dayinyear_type == 3) //dayinyear_type = 3 : คิดค่าปรับตามปีที่กำหนดในสินเชื่อ
            {
                string sqllncont = @"select dayinyear, formatyear_type from lnloanconstant where coop_id = {0}";
                sqllncont = WebUtil.SQLFormat(sqllncont, coop_control);
                ta = WebUtil.QuerySdt(sqllncont);
                if (ta.Next())
                {
                    dayinyear = ta.GetDecimal("dayinyear");
                }
                if (dayinyear == 0)
                {

                }
            }
            overdue_amt = Convert.ToInt32((slip_date - due_date).TotalDays); //วันที่เกินกำหนด

            feeAmt = (loanitemnet_amt * fee_rate) * overdue_amt / dayinyear;
            feeAmt = Math.Round(feeAmt, rounddec);

            return feeAmt;
        }
    }

}