using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Web.Services.Protocols;
using System.Data;
using DataLibrary;
using Sybase.DataWindow.Web;
using System.Configuration;
using System.Web;
using CoreSavingLibrary.WcfNShrlon;
using System.Diagnostics;
using System.Security;
using System.IO;
using System.Net.Mail;
using CoreSavingLibrary;
using System.Web.UI;
using System.Web.UI.WebControls;
using Sybase.DataWindow;
using System.Net;
using System.IO;
using System.Runtime.InteropServices;
using System.Security.Principal;
using System.Net.Sockets;
using System.Net.Security;
using System.ComponentModel;
using System.Collections;
using System.Security.Cryptography;
using System.Text.RegularExpressions;
using Microsoft.Win32;
using System.Threading;

namespace CoreSavingLibrary
{
    public enum PermissType
    {
        /// <summary>
        /// ไม่มีสิทธิ์อ่านข้อมูล, เปิดหน้าจอ, ดึงข้อมูล
        /// </summary>
        ReadDeny = 1,
        /// <summary>
        /// ไม่มีสิทธิ์เขียนข้อมูล, บันทึกข้อมูล, แก้ไขข้อมูล
        /// </summary>
        WriteDeny = 2,
        /// <summary>
        /// ไม่มีสิทธิ์ใช้งาน, ยังไม่ได้ Login
        /// </summary>
        LoginDeny = 3,
    }

    public class WebUtil
    {
        public static string coreRoot = "C:\\ICOOP_ALL";
        public static string coreURI = "ICORE";
        public static string coreSubURI = "ICOOP";
        private static CultureInfo th;
        private static CultureInfo en;

        public static CultureInfo TH
        {
            get
            {
                if (th == null)
                {
                    th = new CultureInfo("th-TH");
                }
                return th;
            }
        }

        public static CultureInfo EN
        {
            get
            {
                if (en == null)
                {
                    en = new CultureInfo("en-US");
                }
                return en;
            }
        }

        /// <summary>
        /// get:คือค่า path เป็น Folder GCOOP ตัวอย่าง: C:\GCOOP_ALL\EGAT\GCOOP\
        /// </summary>
        public static String PhysicalPath
        {
            get
            {
                string appPath = HttpContext.Current.Request.ApplicationPath;
                string physicalPath = HttpContext.Current.Request.MapPath(appPath);
                string physicalPathGCOOP = physicalPath.Substring(0, physicalPath.ToUpper().IndexOf("\\" + coreSubURI + "\\") + 7);
                return physicalPathGCOOP;
            }
        }

        public static string Left(string param, int length)
        {
            //we start at 0 since we want to get the characters starting from the
            //left and with the specified lenght and assign it to a variable
            string result = param.Substring(0, length);
            //return the result of the operation
            return result;
        }

        public static string Right(string param, int length)
        {
            //start at the index based on the lenght of the sting minus
            //the specified lenght and assign it a variable
            int temp = param.Length - length;
            string result = param.Substring(temp, length);
            //return the result of the operation
            return result;
        }

        public static string Mid(string param, int startIndex, int length)
        {
            //start at the specified index in the string ang get N number of
            //characters depending on the lenght and assign it to a variable
            string result = param.Substring(startIndex, length);
            //return the result of the operation
            return result;
        }

        /// <summary>
        /// สร้าง JavaScript Postback
        /// </summary>
        /// <param name="page"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public static String JsPostBack(System.Web.UI.Page page, String name)
        {
            return "<script>function " + name + "(){" + page.ClientScript.GetPostBackEventReference(page, name) + "}</script>";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="permissType"></param>
        /// <returns></returns>
        public static String PermissionDeny(PermissType permissType)
        {
            if (permissType == PermissType.ReadDeny)
            {
                return "<font color=\"red\">คุณยังไม่ได้รับสิทธิ์การใช้งานหน้านี้</font>";
            }
            else if (permissType == PermissType.WriteDeny)
            {
                return "<font color=\"red\">คุณยังไม่ได้รับสิทธิ์การการบันทึกข้อมูลหน้านี้</font>";
            }
            else if (permissType == PermissType.LoginDeny)
            {
                return "<font color=\"red\">คุณยังไม่ได้ทำการ Login</font>";
            }
            return "";
        }

        public static String ErrorMessage(String message)
        {
            return "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/error.gif\" /></td><td align=\"left\"><font color=\"red\">" + message + "</font></td></tr></table></div>";
        }

        public static String ErrorMessage(Exception ex)
        {
            if (ex.Message.IndexOf("#ERROR-20001#") >= 0)
            {
                string first = ex.Message.Substring(ex.Message.IndexOf("#ERROR-20001#") + 13);
                string last = first.Substring(0, first.LastIndexOf("##20001##"));
                return WebUtil.ErrorMessage(last);
            }
            if (ex.GetType().FullName == "System.Web.Services.Protocols.SoapException")
            {
                return WebUtil.ErrorMessage(WebUtil.SoapMessage((SoapException)ex));
            }
            else
            {
                return WebUtil.ErrorMessage(ex.Message);
            }
        }

        public static String CompleteMessage(String message)
        {
            return "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/complete.gif\" /></td><td align=\"left\"><font color=\"green\">" + message + "</font></td></tr></table></div>";
        }

        public static String WarningMessage(String message)
        {
            return "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/warning.gif\" /></td><td align=\"left\"><font color=\"orange\">" + message + "</font></td></tr></table></div>";
        }

        public static String WarningMessage(Exception ex)
        {
            if (ex.GetType().FullName == "System.Web.Services.Protocols.SoapException")
            {
                return WebUtil.WarningMessage(WebUtil.SoapMessage((SoapException)ex));
            }
            else
            {
                return WebUtil.WarningMessage(ex.Message);
            }
        }

        public static String WarningMessage2(String message)
        {
            return "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/warning.gif\" /></td><td align=\"left\"><font color=\"#ff2e00\">" + message + "</font></td></tr></table></div>";
        }

        public static String WarningMessage2(Exception ex)
        {
            if (ex.GetType().FullName == "System.Web.Services.Protocols.SoapException")
            {
                return WebUtil.WarningMessage2(WebUtil.SoapMessage((SoapException)ex));
            }
            else
            {
                return WebUtil.WarningMessage2(ex.Message);
            }
        }

        public static String StringFormat(String str, String formats)
        {
            String formatted = "";
            int tempStr = 0;
            try
            {
                tempStr = Convert.ToInt32(str);
            }
            catch { }


            formatted = tempStr.ToString(formats);

            return formatted;
        }

        public static String SoapMessage(SoapException ex)
        {
            try
            {
                String prefix = "[SW]";
                String message = "";
                String exMessage = ex.Message;
                String pbException = "Sybase.PowerBuilder.PBThrowableE: ";
                String soapException = "System.Web.Services.Protocols.SoapException: ";
                String sysException = "System.Exception: ";
                String at = "\n   at ";
                int indexOfStart = 0;
                int indexOfPbService = exMessage.IndexOf(pbException);
                int indexOfSystemEx = exMessage.IndexOf(sysException);
                if (indexOfPbService > 0)
                {
                    prefix = "[PB]";
                    indexOfStart = indexOfPbService + pbException.Length;
                }
                else if (indexOfSystemEx > 0)
                {
                    prefix = "[SY]";
                    indexOfStart = indexOfSystemEx + sysException.Length;
                }
                else
                {
                    indexOfStart = exMessage.IndexOf(soapException) + soapException.Length;
                }
                message = exMessage.Substring(indexOfStart);
                message = message.Substring(0, message.IndexOf(at));
                message = message.Replace("Server was unable to process request. --->", "");
                return prefix + message;
            }
            catch
            {

                return ex.Message.Replace("Server was unable to process request. --->", "");
            }
        }

        public static String MessageClearText(String message, out String msgType)
        {
            msgType = "N";
            string errHeadText = "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/error.gif\" /></td><td align=\"left\"><font color=\"red\">";
            string comHeadText = "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/complete.gif\" /></td><td align=\"left\"><font color=\"green\">";
            string wrnHeadText = "<div align=\"center\"><table><tr><td valign=\"top\"><img style\"text-align:center;\" src=\"" + new WebState().SsUrl + "Image/warning.gif\" /></td><td align=\"left\"><font color=\"orange\">";
            string lastText = "</font></td></tr></table></div>";
            if (message.IndexOf(errHeadText) >= 0)
            {
                message = message.Replace(errHeadText, "");
                message = message.Replace(lastText, "");
                msgType = "E";
            }
            else if (message.IndexOf(comHeadText) >= 0)
            {
                message = message.Replace(comHeadText, "");
                message = message.Replace(lastText, "");
                msgType = "C";
            }
            else if (message.IndexOf(wrnHeadText) >= 0)
            {
                message = message.Replace(wrnHeadText, "");
                message = message.Replace(lastText, "");
                msgType = "W";
            }
            return message;
        }

        //ตอนนี้มีให้ใช้กับ Report Master (รอเปลี่ยนเป็น state.SsThaiApplicaion)
        public static String GetApplicationThai(String app)
        {
            String rs = "";
            if (app == "shrlon") rs = "หุ้นหนี้";
            else if (app == "ap_deposit") rs = "เงินฝาก";
            else if (app == "app_finance") rs = "การเงิน";
            else if (app == "app_assist") rs = "สวัสดิการ";
            else if (app == "account") rs = "บัญชี";
            else if (app == "keeping") rs = "จัดเก็บ";
            else if (app == "hr") rs = "บริหารงานบุคคล";
            else if (app == "mis") rs = "การลงทุน";
            else if (app == "app_assist") rs = "สวัสดิการ";
            else if (app == "cmd") rs = "พัสดุครุภัณฑ์";
            return rs;
        }

        /// <summary>
        /// แปลงค่า String ให้เป็น int โดยหากเจอ Exception จะ return 0 
        /// </summary>
        /// <param name="number">ตัวเลขในรูปแบบ String</param>
        /// <returns>คืนค่าเป็นตัวเลข หากเกิด Exception จะคืนค่า 0</returns>
        public static int ParseInt(String number)
        {
            try
            {
                return int.Parse(number);
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// เช็คว่า String ที่ได้อยู่ในรูปแบบ XML หรือไม่
        /// </summary>
        /// <param name="xml"></param>
        /// <returns></returns>
        public static bool IsXML(String xml)
        {
            try
            {
                return xml.ToLower().IndexOf("<?xml") >= 0;
            }
            catch
            {
                return false;
            }
        }

        //กำหนดค่าของ UI
        public static String BGselectedTab() { return "rgb(211,213,255)"; }

        public static String BGmenuTab() { return "rgb(200,235,255)"; }

        public static String GenHeadTabMenu(String nameTab, int tabAmt, int selectedTab)
        {
            String tdWidth = (100 / tabAmt).ToString();
            String[] tabList = nameTab.Split(',');

            String output = "<table style=\"width: 100%; border: solid 1px; margin-top: 5px;\">" +
                "<tr align=\"center\" class=\"dwtab\">";

            for (int i = 0; i < tabList.Length; i++)
            {
                if (selectedTab == (i + 1))
                {
                    output += "<td style=\"background-color:" + BGselectedTab() + "; cursor: pointer;\" id=\"stab" + (i + 1) + "\" width=\"25%\"" +
                              "onclick=\"showTabPage(" + (i + 1) + ");\" width=\"" + tdWidth + "%\">" +
                              "" + tabList.GetValue(i) + "</td>";
                }
                else
                {
                    output += "<td style=\"background-color:" + BGmenuTab() + "; cursor: pointer;\" id=\"stab" + (i + 1) + "\" width=\"25%\"" +
                              "onclick=\"showTabPage(" + (i + 1) + ");\" width=\"" + tdWidth + "%\">" +
                              "" + tabList.GetValue(i) + "</td>";
                }
            }

            output += "</tr></table>";
            return output;
        }

        public static String GenJavaScriptTabPage(int tabAmt, int selectedTab)
        {
            String output = "<script type=\"text/javascript\">" +
                            "function showTabPage(tab){var i = 1;var tabamount = " + tabAmt + ";" +
                            "for (i = 1; i <= tabamount; i++) {" +
                //"document.getElementById(\"tab\" + i).style.visibility = \"hidden\";" +
                            "document.getElementById(\"stab\" + i).style.backgroundColor = \"" + BGmenuTab() + "\";" +
                            "if (i == tab) {" +
                //"document.getElementById(\"tab\" + i).style.visibility = \"visible\";" +
                            "document.getElementById(\"stab\" + i).style.backgroundColor = \"" + BGselectedTab() + "\";" +
                            "}}}" +
                            "</script>";

            return output;
        }

        public static String GetConnectionElement(String elementName)
        {
            String result = "";
            try
            {
                String connectionString = new System.Web.UI.Page().Session["ss_connectionstring"].ToString();
                String[] conArray = connectionString.Split(';');
                for (int i = 0; i < conArray.Length; i++)
                {
                    if (conArray[i].IndexOf(elementName) == 0)
                    {
                        String[] ar2 = conArray[i].Split('=');
                        result = ar2[1].Trim();
                        break;
                    }
                }
            }
            catch { }
            return result;
        }

        public static String EncryptType(int type)
        {
            switch (type)
            {
                case 1: return ""; break;
                case 2: return ""; break;
                case 3: return ""; break;
                default: return ""; break;
            }
        }

        public static DataTable XmlToDataTable(String xml, String tbName)
        {
            System.IO.StringReader strReader = new System.IO.StringReader(xml);
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.ReadXml(strReader);
            dt.TableName = tbName;
            return dt;
        }

        public static String datatabletoXML(Sdt dr)
        {
            StringBuilder xmlReturn = new StringBuilder("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
            int ii = 0;
            while (dr.Next())
            {
                xmlReturn.Append("  <ajax_interpreter_row>\n");
                for (int c = 0; c < dr.Columns.Count; c++)
                {
                    string cname = dr.Columns[c].ColumnName.ToLower().Trim();
                    xmlReturn.Append("    <" + cname + ">");
                    try
                    {
                        string vv = "";
                        string ty = dr.Columns[c].DataType.ToString().ToLower();
                        if (dr.Rows[ii][c] != DBNull.Value)
                        {
                            if (ty == "system.datetime")
                            {
                                vv = dr.GetDate(c).ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else
                            {
                                vv = dr.GetString(c);
                            }
                        }
                        xmlReturn.Append(vv);
                    }
                    catch { }
                    xmlReturn.Append("</" + cname + ">\n");
                }
                xmlReturn.Append("  </ajax_interpreter_row>\n");
                ii++;
            }
            return xmlReturn.ToString();
        }

        public static DataTable Query(String sql)
        {
            WebState state = new WebState();
            //WcfCalling wcf = new WcfCalling(null);
            //CommonClient cm = wcf.Common;
            DataTable dt = state.SsOracleTA.QueryDataTable(sql);// cm.GetDataTable(state.SsWsPass, sql, "Sdt");
            //wcf.Close();
            return dt;
        }

        public static Sdt QuerySdt(String sql)
        {
            WebState state = new WebState();
            //WcfCalling wcf = new WcfCalling(null);
            //CommonClient cm = wcf.Common;
            Sdt dt = state.SsOracleTA.Query(sql);// new Sdt(cm.GetDataTable(state.SsWsPass, sql, "Sdt"));
            //wcf.Close();
            return dt;
        }

        public static int ExeSQL(String sql)
        {
            WebState state = new WebState();
            //WcfCalling wcf = new WcfCalling(null);
            //CommonClient cm = wcf.Common;
            //Sdt dt = state.SsOracleTA.Query(sql);// new Sdt(cm.GetDataTable(state.SsWsPass, sql, "Sdt"));
            //wcf.Close();
            return state.SsOracleTA.Exe(sql);
        }

        //public static String ConvertDateThaiToEng(WebDataWindowControl dwObj, String thaiDateColumn, String thDate)
        //{
        //    String thaiDate = "";
        //    if (thDate == null || thDate == "") { thaiDate = dwObj.GetItemString(1, thaiDateColumn); }
        //    else { thaiDate = thDate; }
        //    thaiDate = thaiDate.Replace("/", "");
        //    DateTime engdate = DateTime.ParseExact(thaiDate, "ddMMyyyy", WebUtil.EN);
        //    String str_date = engdate.Day.ToString("00");
        //    String str_month = engdate.Month.ToString("00");
        //    String str_year = (Convert.ToInt32(engdate.Year.ToString()) - 543).ToString("0000");
        //    CommonClient cm = wcf.Common;
        //    String printType = "";
        //    try
        //    {
        //        printType = cm.GetConstantValue(new WebState().SsWsPass, "reportservice.ws.pdfmethod");
        //    }
        //    catch { }
        //    if (printType == "1") // WebService
        //    {
        //        thaiDate = str_month + "/" + str_date + "/" + str_year;
        //    }
        //    else // WinPrint
        //    {
        //        //thaiDate = str_month + "/" + str_date + "/" + str_year;
        //        thaiDate = str_date + "/" + str_month + "/" + str_year;
        //    }
        //    return thaiDate;
        //}

        public static String ConvertDateThaiToEng(WebDataWindowControl dwObj, String thaiDateColumn, String thDate)
        {
            String thaiDate = "";
            if (thDate == null || thDate == "") { thaiDate = dwObj.GetItemString(1, thaiDateColumn); }
            else { thaiDate = thDate; }
            thaiDate = thaiDate.Replace("/", "");

            String str_date = thaiDate.Substring(0, 2);
            String str_month = thaiDate.Substring(2, 2);
            String str_year = Convert.ToString(Convert.ToInt32(thaiDate.Substring(4, 4)) - 543);

            DateTime dtReturn;

            try
            {
                dtReturn = new DateTime(int.Parse(str_year), int.Parse(str_month), int.Parse(str_date));
            }
            catch
            {
                dtReturn = new DateTime(1500, 1, 1);
            }

            thaiDate = dtReturn.ToString("yyyy-MM-dd HH:mm:ss", WebUtil.EN);

            return thaiDate;
        }

        public static void RetrieveDDDW(WebDataWindowControl dwObj, String columnName, String libraryList, params object[] args)
        {
            DwUtil.RetrieveDDDW(dwObj, columnName, "", args);
        }

        public static String MemberNoFormat(string memberNo)
        {
            WebState state = null;
            try
            {
                //string membno_format = "";
                string mem_no = "";
                //string sql = "select membno_format from cmcoopconstant";
                //Sdt dt = WebUtil.QuerySdt(sql);
                //if (dt.Next())
                //{
                //    membno_format = dt.GetString("membno_format");
                //}
                //for (int i = 0; i < membno_format.Length; i++)
                //{
                //    mem_no += "0";
                //}
                //mem_no += memberNo;
                //mem_no = mem_no.Substring(mem_no.Length - membno_format.Length);
                string memberno = memberNo;
                if (memberno.Length <= 5)
                {
                    for (int i = memberno.Length; i < 5; i++)
                    {
                        mem_no += "0";
                    }
                    mem_no += memberNo;

                    memberno = mem_no;
                }

                String sqlStr2 = @"select member_no from mbmembmaster  where  mbmembmaster.member_no like '%" + memberno + "' " + (Sta.IS_MYSQL_MODE ? " order by member_no desc limit 0,1 " : " and ROWNUM <= 1  order by member_no asc");
                sqlStr2 = WebUtil.SQLFormat(sqlStr2);

                Sdt dt121 = WebUtil.QuerySdt(sqlStr2);

                if (dt121.Next())
                {
                    mem_no = dt121.GetString("member_no");
                }

                return mem_no;
            }
            catch
            {
                return "";
            }
        }

        public static String MemberNoFormat2(string memberNo)
        {
            WebState state = null;
            try
            {
                string membno_format = "";
                string mem_no = "";
                string sql = "select membno_format from cmcoopconstant";
                Sdt dt = WebUtil.QuerySdt(sql);
                if (dt.Next())
                {
                    membno_format = dt.GetString("membno_format");
                }
                for (int i = 0; i < membno_format.Length; i++)
                {
                    mem_no += "0";
                }
                mem_no += memberNo;
                mem_no = mem_no.Substring(mem_no.Length - membno_format.Length);

               

                return mem_no;
            }
            catch
            {
                return "";
            }
        }
        public static String GetDoc_no(String as_doccode, String coop_id)
        {
            string ls_docprefix = "", ls_docformat = "", ls_docyear = "";
            string ls_newdocno = "", ls_fillzero = "";
            int ll_lastdocno = 0;
            int li_doclength = 0, li_lenrunning, li_lenlastdocno;


            of_new(coop_id, as_doccode, ref ll_lastdocno, ref li_doclength, ref ls_docprefix, ref ls_docformat, ref ls_docyear);

            // ถ้าไม่มีการกำหนดรูปแบบเลขที่เอกสาร
            if (ls_docformat == "")
            {

                // ถ้าความยาวของเลขที่เอกสารไม่ได้กำหนดหรือกำหนดต่ำกว่า 0
                if (li_doclength <= 0)
                {
                    li_doclength = 10;
                }

                // จำนวนหลักของ Running Number
                li_lenrunning = li_doclength - ls_docprefix.Length;

                // ความยาวของ Running number ล่าสุด
                li_lenlastdocno = ll_lastdocno;

                if (li_lenlastdocno > li_lenrunning)
                {
                    ls_fillzero = "";
                }
                else
                {
                    for (int i = 0; i < (li_lenrunning - li_lenlastdocno); i++)
                    {
                        ls_fillzero += "0";
                    }

                }

                ls_newdocno = ls_docprefix + ls_fillzero + Convert.ToString(ll_lastdocno);
            }
            else
            {
                // Y ปีเอกสาร, R เลขที running, B สาขาที่ส่งเข้ามา, P คำนำหน้าที่กำหนดไว้ที่ doccontrol, C คำนำหน้าที่กำหนดเองโดยคนเรียก Srv


                for (int j = 0; j < ls_docformat.Length; j++)
                {

                    string p = ls_docformat.Substring(j, 1);//ได้ตัวแรก
                    string pp = "";
                    int len1 = ls_docformat.LastIndexOf(p) + 1;
                    int len2 = ls_docformat.IndexOf(p);
                    pp = ls_docformat.Substring(j, len1 - len2);
                    if (p == "Y")
                    {
                        ls_docyear = ls_docyear.Substring(len2, pp.Length);
                        ls_newdocno += ls_docyear;
                    }
                    else if (p == "P")
                    {
                        ls_newdocno += ls_docprefix;
                    }
                    else if (p == "R")
                    {
                        if (Convert.ToString(ll_lastdocno).Length == (len1 - len2))
                        {
                            ls_newdocno += Convert.ToString(ll_lastdocno);
                        }
                        else
                        {
                            int u = (len1 - len2) - Convert.ToString(ll_lastdocno).Length;
                            for (int h = 1; h <= u; h++)
                            {
                                ls_fillzero += "0";
                            }
                            ls_newdocno += ls_fillzero + Convert.ToString(ll_lastdocno);
                        }
                    }


                    j = len1 - 1;
                }

            }

            return ls_newdocno;
        }

        private static void of_new(string coop_id, string as_doccode, ref int ll_lastdocno, ref int li_doclength, ref string ls_docprefix, ref string ls_docformat, ref string ls_docyear)
        {
            //  parameter check
            ll_lastdocno = 0;
            li_doclength = 0;
            ls_docprefix = "";
            ls_docformat = "";
            ls_docyear = "";

            int li_count = 0;
            Sdt d = WebUtil.QuerySdt(@" select count(1) as oop  from cmdocumentcontrol
                            where (trim(document_code) = trim('" + as_doccode + "')) and  (coop_id = '" + coop_id + "')");
            if (d.Next())
            {
                li_count = d.GetInt32("oop");
            }

            if (li_count > 0)
            {

                Sdt d2 = WebUtil.QuerySdt(@" select last_documentno, document_length, document_prefix, document_format, document_year  from cmdocumentcontrol
                            where (trim(document_code) = trim('" + as_doccode + "')) and  (coop_id = '" + coop_id + "')");
                if (d2.Next())
                {
                    ll_lastdocno = d2.GetInt32("last_documentno");
                    li_doclength = d2.GetInt32("last_documentno");
                    ls_docprefix = d2.GetString("document_prefix");
                    ls_docformat = d2.GetString("document_format");
                    ls_docyear = d2.GetString("document_year");

                    WebUtil.QuerySdt(@" update cmdocumentcontrol set last_documentno= last_documentno+1 where (trim(document_code) = trim('" + as_doccode + "')) and  (coop_id = '" + coop_id + "')");

                }
            }



        }


        public static Decimal Calint(String loancontract, DateTime date_now,string coop_id)
        {
            
            
            Decimal intrate = 0,int_cal=0;//อัตราดอกเบี้ย


            
            // get yaer จาก ค่าคงที่
            decimal year = 0, rdintdec_type = 0, INTROUNDSUM_TYPE = 0;
            string sql21 = @" 
                 select dayinyear,rdintdec_type,INTROUNDSUM_TYPE from lnloanconstant  ";
            sql21 = WebUtil.SQLFormat(sql21, loancontract);
            Sdt dt21 = WebUtil.QuerySdt(sql21);

            if (dt21.Next())
            {
                year = dt21.GetDecimal("dayinyear");
                rdintdec_type = dt21.GetDecimal("rdintdec_type");
                INTROUNDSUM_TYPE = dt21.GetDecimal("INTROUNDSUM_TYPE");
            }
            string sql3 = @" select contract_interest from lncontmaster where loancontract_no={0}";
            sql3 = WebUtil.SQLFormat(sql3, loancontract);

            Sdt dt3 = WebUtil.QuerySdt(sql3);
            if (dt3.Next())
            {
                intrate = dt3.GetDecimal("contract_interest");
                

            }
            if (intrate == -1)// ถ้ามากกว่า 0 ให้ไปดึงดอกเบี้ยจาก lncontmaster
            {

                if (INTROUNDSUM_TYPE == 0)
                { //คิดแบบ ไม่จัดชั้น

                    decimal chkgrtworktime_flag = 0;
                    decimal principal_arrear = 0;


                    string sql2 = @" 
                            select lncfloanintratedet.interest_rate,chkgrtworktime_flag,principal_arrear from lncfloanintratedet left join  lnloantype on lnloantype.inttabfix_code = lncfloanintratedet.loanintrate_code 
            left join lncontmaster on lncontmaster.loantype_code=lnloantype.loantype_code where lncontmaster.loancontract_no= {0}";
                    sql2 = WebUtil.SQLFormat(sql2, loancontract);
                    Sdt dt2 =  WebUtil.QuerySdt(sql2);
                  //  Sdt dt2 = WebUtil.QuerySdt(sql2);
                    if (dt2.Next())
                    {
                        intrate = dt2.GetDecimal("interest_rate");
                        chkgrtworktime_flag = dt2.GetDecimal("chkgrtworktime_flag"); // คิดดอกเบี้ย เมื่อมีต้น ค้าง เมื่อมีค่าเท่ากับ 1 
                        principal_arrear = dt2.GetDecimal("principal_arrear"); 
                    }

                    if (chkgrtworktime_flag == 1) // คิดดอกเบี้ยเมื่อมีต้นค้าง
                    {
                        //เช็คว่ามีต้นค้างหรือไม่
                        if (principal_arrear == 0) //เมื่อไม่มีต้นค้าง
                        {

                            intrate = 0;// ไม่คิดดอกเบี้ย
                           
                        }

                    }

                }
                else
                {//คิดแบบ จัดชั้น

                       decimal chkgrtworktime_flag = 0;
                       decimal principal_arrear = 0;
                        string sql2 = @" 
                           select lir.int_rate,lt.chkgrtworktime_flag,lc.principal_arrear from lncontmaster lc inner join   
                         lnloantypeintrate lir   on lir.loantype_code = lc.loantype_code inner join mbmembmaster mb on mb.member_no = lc.member_no and
                         mb.mblntype_level = lir.mblntype_level inner join  lnloantype lt on lt.loantype_code = lc.loantype_code where  lc.loancontract_no= {0}";
                        sql2 = WebUtil.SQLFormat(sql2, loancontract);
                        Sdt dt2 = WebUtil.QuerySdt(sql2);
                       // Sdt dt2 = WebUtil.QuerySdt(sql2);
                        if (dt2.Next())
                        {
                            intrate = dt2.GetDecimal("int_rate");
                            chkgrtworktime_flag = dt2.GetDecimal("chkgrtworktime_flag"); // คิดดอกเบี้ย เมื่อมีต้น ค้าง เมื่อมีค่าเท่ากับ 1 
                            principal_arrear = dt2.GetDecimal("principal_arrear"); 
                        }


                      
                        

                        Sdt dss = WebUtil.QuerySdt("select addr_amphur from cmcoopmaster inner join lncontmaster on cmcoopmaster.coop_id = lncontmaster.coop_id  where cmcoopmaster.coop_id = '500001' and cmcoopmaster.addr_amphur='ย่านตาขาว' and  principal_arrear > 0  and lncontmaster.loancontract_no= '" + loancontract + "'");
                        if (dss.Next())
                        {
                            if (intrate == 0) //fix ย่านตาขาว
                            {
                                string sq21 = @" 
                           select lir.intarr_rate from lncontmaster lc inner join   
                             lnloantypeintrate lir   on lir.loantype_code = lc.loantype_code inner join mbmembmaster mb on mb.member_no = lc.member_no and
                             mb.mblntype_level = lir.mblntype_level where  lc.loancontract_no= {0}";
                                sq21 = WebUtil.SQLFormat(sq21, loancontract);
                                Sdt dt211 = WebUtil.QuerySdt(sq21);
                               // Sdt dt211 = WebUtil.QuerySdt(sq21);
                                if (dt211.Next())
                                {
                                    intrate = dt211.GetDecimal("intarr_rate");

                                }
                            }

                        }


                        if (chkgrtworktime_flag == 1) // คิดดอกเบี้ยเมื่อมีต้นค้าง
                        {
                            //เช็คว่ามีต้นค้างหรือไม่
                            if (principal_arrear == 0) //เมื่อไม่มีต้นค้าง
                            {

                                intrate = 0;// ไม่คิดดอกเบี้ย

                            }

                        }
                }

            }

          

            string sql = @" 
                 SELECT  principal_balance,principal_arrear,loantype_code,lastcalint_date FROM lncontmaster  WHERE loancontract_no = {0}";
            sql = WebUtil.SQLFormat(sql, loancontract);
            Sdt dt = WebUtil.QuerySdt(sql);
           // Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                DateTime calint = dt.GetDate("lastcalint_date");
                Decimal principal_balance = dt.GetDecimal("principal_balance"); // ยอดคงเหลือ
                TimeSpan diff1 = date_now.Subtract(calint);
                Double TotalDay2 = diff1.TotalDays;
                int CalDay = Convert.ToInt32(TotalDay2); // คำนวณวัน

                intrate = intrate / 100;


               //แม่จัน ถ้าเป็นประเภท สัญญา ด ให้เอาต้นค้าง มาคิด ด/บใหม่
                if (dt.GetString("loantype_code") == "80" && coop_id == "000501")
                {
                    principal_balance = dt.GetDecimal("principal_arrear"); // ยอดต้นเงินค้าง
                }

                //คำนวณดอกเบี้ย กล้า
                Decimal cal_int = (principal_balance * intrate * CalDay) / year; //   ด/บใหม่

                if (rdintdec_type == 0)//ทศนิยม 2 ตำแหน่ง
                {
                    int_cal = Convert.ToDecimal(Math.Round(cal_int, 2));

                }
                else if (rdintdec_type == 1)//ปัดทิ้ง
                {
                    int_cal = Convert.ToDecimal(Math.Floor(cal_int));
                }
                    else if (rdintdec_type == 2)//ปัดทิ้ง
                {
                int_cal = Convert.ToDecimal(Math.Ceiling(cal_int));
                }
                else
                {
                int_cal = Convert.ToDecimal(Math.Round(cal_int, 0));
                }
                if (int_cal < 0)
                {
                    int_cal = 0;
                }
                 
            }
            return int_cal;
        }
 
        public static Decimal calfine_new(String loancontract, DateTime date_now, string coop_id)// 
        {
            

            decimal fine = 0;
          

            DateTime lastcalfine_date = date_now;
            DateTime expireperiod_date = date_now;
            Double principal_arrear = 0;
            Double cal_int = 0; //ผลลัพธ์
            Double year = 0;
            Double cal_fineall = 0;
            int rdintsatang_type = 0;
            // get yaer จาก ค่าคงที่
            string sql2 = @" 
                 select dayinyear,rdintsatang_type  from lnloanconstant";
            sql2 = WebUtil.SQLFormat(sql2, loancontract);
            // Sdt dt2 = WebUtil.QuerySdt(sql2);
            Sdt dt2 = WebUtil.QuerySdt(sql2);
            if (dt2.Next())
            {
                year = dt2.GetDouble("dayinyear");
                rdintsatang_type = dt2.GetInt32("rdintsatang_type");//ปัดเศษ

            }

            string sql = @" 
                 select  lastcalfine_date,(period_payment-prinpaid_amt) as principal_arrear,expireperiod_date,lastcalfine_date  from lncontperiodpaydet 
                 where loancontract_no= {0}  and (period_payment-prinpaid_amt) > 0 and expireperiod_date < {1}  order by period asc ";
            sql = WebUtil.SQLFormat(sql, loancontract, date_now);
         //   Sdt dt = WebUtil.QuerySdt(sql);
            Sdt dt = WebUtil.QuerySdt(sql);
            while (dt.Next())
            {
                //    lastcalfine_date = dt.GetDate("lastcalfine_date"); //วันที่คิดล่าสุด
                expireperiod_date = dt.GetDate("expireperiod_date"); //วันที่ต้องจ่าย
                
                lastcalfine_date = dt.GetDate("lastcalfine_date");
                principal_arrear = dt.GetDouble("principal_arrear"); // ยอดคงเหลือ
//                  string sql21 = @" 
//                 select  sum(period_payment-prinpaid_amt) as  period_payment from lncontperiodpaydet 
//                 where loancontract_no= {0}  and (period_payment-prinpaid_amt) > 0 and expireperiod_date < {1}   ";
//            sql21 = WebUtil.SQLFormat(sql21, loancontract, date_now);
//            Sdt dt2s = WebUtil.QuerySdt(sql21);
//            if (dt2s.Next())
//            {
//                principal_arrear = dt2s.GetDouble("period_payment"); // ยอดคงเหลือ
//            }
//            dt2s.Clear();


                TimeSpan diff1 = date_now.Subtract(expireperiod_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                Double TotalDay2 = diff1.TotalDays;

                if (TotalDay2 < 0)
                {
                    cal_int = 0;
                }
                else
                {

                    Double percen = 0.03;
                    TimeSpan diff2 = date_now.Subtract(lastcalfine_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                    Double TotalDay3 = diff2.TotalDays;
                    int CalDay = Convert.ToInt32(TotalDay3); // คำนวณวัน
                    
                    String hart = "select usemangrt_comaxvalue,principal_balance from lnloantype inner join  lncontmaster on lnloantype.loantype_code = lncontmaster.loantype_code  where loancontract_no = '" + loancontract + "'";
                   // Sdt sd = WebUtil.QuerySdt(hart);
                    Sdt sd = WebUtil.QuerySdt(hart);
                    if (sd.Next())
                    {
                        percen = Convert.ToDouble( sd.GetDecimal("usemangrt_comaxvalue")/100);
                        if (sd.GetDecimal("principal_balance") <= 0)
                        {
                            principal_arrear = 0;
                        }

                    }


                    cal_int = (principal_arrear * percen * CalDay) / year; //   ค่าปรับใหม่
                }
                cal_fineall += cal_int;
            }
 
            //  <asp:ListItem Value="0">ทศนิยม 2 ตำแหน่ง</asp:ListItem>
            //       <asp:ListItem Value="1">ปัดทิ้ง</asp:ListItem>
            //       <asp:ListItem Value="2">ปัดขึ้น</asp:ListItem>   
            if (rdintsatang_type == 0)//ทศนิยม 2 ตำแหน่ง
            {
                fine = Convert.ToDecimal(Math.Round(cal_fineall,2));

            }
            else if (rdintsatang_type == 1)//ปัดทิ้ง
            {
                fine = Convert.ToDecimal(Math.Floor(cal_fineall));
            }
            else if (rdintsatang_type == 2)//ปัดขึ้น
            {
                fine = Convert.ToDecimal(Math.Ceiling(cal_fineall));
            }
            else
            {
                decimal fils = Convert.ToDecimal(Math.Floor(cal_fineall));
                fine = Convert.ToDecimal(Math.Round(cal_fineall, 2));
                fils = (fine - fils) * 100;

                if (fils >= 49)
                {
                    fine = Convert.ToDecimal(Math.Ceiling(cal_fineall));
                }
                else
                {
                    fine = Convert.ToDecimal(Math.Floor(cal_fineall));

                }
                
            }

         
            if (fine < 0)
            {
                fine = 0;
            }
            return fine;

        }

        public static Decimal Calint_bar(String loancontract, DateTime date_now, string coop_id, Sta taSrc, Sta taSrcQuery)
        {
            

            Decimal intrate = 0, int_cal = 0;//อัตราดอกเบี้ย



            // get yaer จาก ค่าคงที่
            decimal year = 0, rdintdec_type = 0, INTROUNDSUM_TYPE = 0;
            string sql21 = @" 
                 select dayinyear,rdintdec_type,INTROUNDSUM_TYPE from lnloanconstant  ";
            sql21 = WebUtil.SQLFormat(sql21, loancontract);
            Sdt dt21 = new Sdt();
            dt21 = taSrcQuery.Query(sql21);

            if (dt21.Next())
            {
                year = dt21.GetDecimal("dayinyear");
                rdintdec_type = dt21.GetDecimal("rdintdec_type");
                INTROUNDSUM_TYPE = dt21.GetDecimal("INTROUNDSUM_TYPE");
            }
            string sql3 = @" select contract_interest from lncontmaster where loancontract_no={0}";
            sql3 = WebUtil.SQLFormat(sql3, loancontract);

            Sdt dt3 = new Sdt();
            dt3 = taSrcQuery.Query(sql3);
            if (dt3.Next())
            {
                intrate = dt3.GetDecimal("contract_interest");


            }
            if (intrate == -1)// ถ้ามากกว่า 0 ให้ไปดึงดอกเบี้ยจาก lncontmaster
            {

                if (INTROUNDSUM_TYPE == 0)
                { //คิดแบบ ไม่จัดชั้น

                    string sql2 = @" 
                            select lncfloanintratedet.interest_rate from lncfloanintratedet left join  lnloantype on lnloantype.inttabfix_code = lncfloanintratedet.loanintrate_code 
            left join lncontmaster on lncontmaster.loantype_code=lnloantype.loantype_code where lncontmaster.loancontract_no= {0}";
                    sql2 = WebUtil.SQLFormat(sql2, loancontract);
                    Sdt dt2 = new Sdt();
                    dt2 = taSrcQuery.Query(sql2);
                    //  Sdt dt2 = WebUtil.QuerySdt(sql2);
                    if (dt2.Next())
                    {
                        intrate = dt2.GetDecimal("interest_rate");

                    }
                }
                else
                {//คิดแบบ จัดชั้น

                    string sql2 = @" 
                           select lir.int_rate from lncontmaster lc inner join   
                         lnloantypeintrate lir   on lir.loantype_code = lc.loantype_code inner join mbmembmaster mb on mb.member_no = lc.member_no and
                         mb.mblntype_level = lir.mblntype_level where  lc.loancontract_no= {0}";
                    sql2 = WebUtil.SQLFormat(sql2, loancontract);
                    Sdt dt2 = new Sdt();
                    dt2 = taSrcQuery.Query(sql2);
                    // Sdt dt2 = WebUtil.QuerySdt(sql2);
                    if (dt2.Next())
                    {
                        intrate = dt2.GetDecimal("int_rate");

                    }
                    Sdt dss = taSrcQuery.Query("select addr_amphur from cmcoopmaster inner join lncontmaster on cmcoopmaster.coop_id = lncontmaster.coop_id  where cmcoopmaster.coop_id = '500001' and cmcoopmaster.addr_amphur='ย่านตาขาว' and  principal_arrear > 0  and lncontmaster.loancontract_no= '" + loancontract + "'");
                    if (dss.Next())
                    {
                        if (intrate == 0) //fix ย่านตาขาว
                        {
                            string sq21 = @" 
                           select lir.intarr_rate from lncontmaster lc inner join   
                             lnloantypeintrate lir   on lir.loantype_code = lc.loantype_code inner join mbmembmaster mb on mb.member_no = lc.member_no and
                             mb.mblntype_level = lir.mblntype_level where  lc.loancontract_no= {0}";
                            sq21 = WebUtil.SQLFormat(sq21, loancontract);
                            Sdt dt211 = new Sdt();
                            dt211 = taSrcQuery.Query(sq21);
                            // Sdt dt211 = WebUtil.QuerySdt(sq21);
                            if (dt211.Next())
                            {
                                intrate = dt211.GetDecimal("intarr_rate");

                            }
                        }

                    }
                }

            }



            string sql = @" 
                 SELECT  principal_balance,principal_arrear,loantype_code,lastcalint_date FROM lncontmaster  WHERE loancontract_no = {0}";
            sql = WebUtil.SQLFormat(sql, loancontract);
            Sdt dt = new Sdt();
            dt = taSrcQuery.Query(sql);
            // Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                DateTime calint = dt.GetDate("lastcalint_date");
                Decimal principal_balance = dt.GetDecimal("principal_balance"); // ยอดคงเหลือ
                TimeSpan diff1 = date_now.Subtract(calint);
                Double TotalDay2 = diff1.TotalDays;
                int CalDay = Convert.ToInt32(TotalDay2); // คำนวณวัน

                intrate = intrate / 100;


                //แม่จัน ถ้าเป็นประเภท สัญญา ด ให้เอาต้นค้าง มาคิด ด/บใหม่
                if (dt.GetString("loantype_code") == "80" && coop_id == "000501")
                {
                    principal_balance = dt.GetDecimal("principal_arrear"); // ยอดต้นเงินค้าง
                }

                //คำนวณดอกเบี้ย กล้า
                Decimal cal_int = (principal_balance * intrate * CalDay) / year; //   ด/บใหม่

                if (rdintdec_type == 0)//ทศนิยม 2 ตำแหน่ง
                {
                    int_cal = Convert.ToDecimal(Math.Round(cal_int, 2));

                }
                else if (rdintdec_type == 1)//ปัดทิ้ง
                {
                    int_cal = Convert.ToDecimal(Math.Floor(cal_int));
                }
                else if (rdintdec_type == 2)//ปัดทิ้ง
                {
                    int_cal = Convert.ToDecimal(Math.Ceiling(cal_int));
                }
                else
                {
                    int_cal = Convert.ToDecimal(Math.Round(cal_int, 0));
                }
                if (int_cal < 0)
                {
                    int_cal = 0;
                }

            }
            return int_cal;
        }

        public static Decimal calfine_new_bar(String loancontract, DateTime date_now, string coop_id, Sta taSrc, Sta taSrcQuery)// 
        {
            //Sta taSrc = null;
            //Sta taSrcQuery = null;

            //taSrc = new Sta(HttpContext.Current.Session["SsConnectionString"].ToString());
            //taSrcQuery = new Sta(HttpContext.Current.Session["SsConnectionString"].ToString());
            //taSrc.Transection();


            decimal fine = 0;


            DateTime lastcalfine_date = date_now;
            DateTime expireperiod_date = date_now;
            Double principal_arrear = 0;
            Double cal_int = 0; //ผลลัพธ์
            Double year = 0;
            Double cal_fineall = 0;
            int rdintsatang_type = 0;
            // get yaer จาก ค่าคงที่
            string sql2 = @" 
                 select dayinyear,rdintsatang_type  from lnloanconstant";
            sql2 = WebUtil.SQLFormat(sql2, loancontract);
            // Sdt dt2 = WebUtil.QuerySdt(sql2);
            Sdt dt2 = new Sdt();
            dt2 = taSrcQuery.Query(sql2);
            if (dt2.Next())
            {
                year = dt2.GetDouble("dayinyear");
                rdintsatang_type = dt2.GetInt32("rdintsatang_type");//ปัดเศษ

            }

            string sql = @" 
                 select  lastcalfine_date,(period_payment-prinpaid_amt) as principal_arrear,expireperiod_date,lastcalfine_date  from lncontperiodpaydet 
                 where loancontract_no= {0}  and (period_payment-prinpaid_amt) > 0 and expireperiod_date < {1}  order by period asc ";
            sql = WebUtil.SQLFormat(sql, loancontract, date_now);
            //   Sdt dt = WebUtil.QuerySdt(sql);
            Sdt dt = new Sdt();
            dt = taSrcQuery.Query(sql);
            while (dt.Next())
            {
                //    lastcalfine_date = dt.GetDate("lastcalfine_date"); //วันที่คิดล่าสุด
                expireperiod_date = dt.GetDate("expireperiod_date"); //วันที่ต้องจ่าย

                lastcalfine_date = dt.GetDate("lastcalfine_date");
                principal_arrear = dt.GetDouble("principal_arrear"); // ยอดคงเหลือ
                //                  string sql21 = @" 
                //                 select  sum(period_payment-prinpaid_amt) as  period_payment from lncontperiodpaydet 
                //                 where loancontract_no= {0}  and (period_payment-prinpaid_amt) > 0 and expireperiod_date < {1}   ";
                //            sql21 = WebUtil.SQLFormat(sql21, loancontract, date_now);
                //            Sdt dt2s = WebUtil.QuerySdt(sql21);
                //            if (dt2s.Next())
                //            {
                //                principal_arrear = dt2s.GetDouble("period_payment"); // ยอดคงเหลือ
                //            }
                //            dt2s.Clear();


                TimeSpan diff1 = date_now.Subtract(expireperiod_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                Double TotalDay2 = diff1.TotalDays;

                if (TotalDay2 < 0)
                {
                    cal_int = 0;
                }
                else
                {

                    Double percen = 0.03;
                    TimeSpan diff2 = date_now.Subtract(lastcalfine_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                    Double TotalDay3 = diff2.TotalDays;
                    int CalDay = Convert.ToInt32(TotalDay3); // คำนวณวัน

                    String hart = "select usemangrt_comaxvalue,principal_balance from lnloantype inner join  lncontmaster on lnloantype.loantype_code = lncontmaster.loantype_code  where loancontract_no = '" + loancontract + "'";
                    // Sdt sd = WebUtil.QuerySdt(hart);
                    Sdt sd = new Sdt();
                    sd = taSrcQuery.Query(hart);
                    if (sd.Next())
                    {
                        percen = Convert.ToDouble(sd.GetDecimal("usemangrt_comaxvalue") / 100);
                        if (sd.GetDecimal("principal_balance") <= 0)
                        {
                            principal_arrear = 0;
                        }

                    }


                    cal_int = (principal_arrear * percen * CalDay) / year; //   ค่าปรับใหม่
                }
                cal_fineall += cal_int;
            }

            //  <asp:ListItem Value="0">ทศนิยม 2 ตำแหน่ง</asp:ListItem>
            //       <asp:ListItem Value="1">ปัดทิ้ง</asp:ListItem>
            //       <asp:ListItem Value="2">ปัดขึ้น</asp:ListItem>   
            if (rdintsatang_type == 0)//ทศนิยม 2 ตำแหน่ง
            {
                fine = Convert.ToDecimal(Math.Round(cal_fineall, 2));

            }
            else if (rdintsatang_type == 1)//ปัดทิ้ง
            {
                fine = Convert.ToDecimal(Math.Floor(cal_fineall));
            }
            else if (rdintsatang_type == 2)//ปัดขึ้น
            {
                fine = Convert.ToDecimal(Math.Ceiling(cal_fineall));
            }
            else
            {
                decimal fils = Convert.ToDecimal(Math.Floor(cal_fineall));
                fine = Convert.ToDecimal(Math.Round(cal_fineall, 2));
                fils = (fine - fils) * 100;

                if (fils >= 49)
                {
                    fine = Convert.ToDecimal(Math.Ceiling(cal_fineall));
                }
                else
                {
                    fine = Convert.ToDecimal(Math.Floor(cal_fineall));

                }

            }


            if (fine < 0)
            {
                fine = 0;
            }
            return fine;

        }



        public static Decimal Calint_Return(String loancontract, DateTime date_now, string coop_id, Decimal payment, DateTime lastcalint_date, int seq_no)
        {

            Decimal intrate = 0, int_cal = 0, cal_int = 0;//อัตราดอกเบี้ย


            // get yaer จาก ค่าคงที่
            decimal year = 0, rdintdec_type = 0, INTROUNDSUM_TYPE = 0;
            string sql21 = @" 
                 select dayinyear,rdintdec_type,INTROUNDSUM_TYPE from lnloanconstant where coop_id = '" + coop_id + "'";
            sql21 = WebUtil.SQLFormat(sql21, loancontract);
            Sdt dt21 = WebUtil.QuerySdt(sql21);
            if (dt21.Next())
            {
                year = dt21.GetDecimal("dayinyear");
                rdintdec_type = dt21.GetDecimal("rdintdec_type");
                INTROUNDSUM_TYPE = dt21.GetDecimal("INTROUNDSUM_TYPE");
            }


            string sql3 = @" select contract_interest from lncontmaster where loancontract_no={0}";
            sql3 = WebUtil.SQLFormat(sql3, loancontract);
            Sdt dt3 = WebUtil.QuerySdt(sql3);
            if (dt3.Next())
            {
                intrate = dt3.GetDecimal("contract_interest");

            }
            if (intrate == -1)// ถ้ามากกว่า 0 ให้ไปดึงดอกเบี้ยจาก lncontmaster
            {

                if (INTROUNDSUM_TYPE == 0)
                { //คิดแบบ ไม่จัดชั้น

                    string sql2 = @" 
                            select lncfloanintratedet.interest_rate from lncfloanintratedet left join  lnloantype on lnloantype.inttabfix_code = lncfloanintratedet.loanintrate_code 
            left join lncontmaster on lncontmaster.loantype_code=lnloantype.loantype_code where lncontmaster.loancontract_no= {0}";
                    sql2 = WebUtil.SQLFormat(sql2, loancontract);
                    Sdt dt2 = WebUtil.QuerySdt(sql2);
                    if (dt2.Next())
                    {
                        intrate = dt2.GetDecimal("interest_rate");

                    }
                }
                else
                {//คิดแบบ จัดชั้น

                    string sql2 = @" 
                           select lir.int_rate from lncontmaster lc inner join   
 lnloantypeintrate lir   on lir.loantype_code = lc.loantype_code inner join mbmembmaster mb on mb.member_no = lc.member_no and
 mb.mblntype_level = lir.mblntype_level where  lc.loancontract_no= {0}";
                    sql2 = WebUtil.SQLFormat(sql2, loancontract);
                    Sdt dt2 = WebUtil.QuerySdt(sql2);
                    if (dt2.Next())
                    {
                        intrate = dt2.GetDecimal("int_rate");

                    }


                }

            }



            DateTime calint = lastcalint_date;
            //เช็คว่าจ่าดอกเบี้ยไปยัง
            string sql11 = @"  select count(*) as interest_payment  from lncontstatement where loancontract_no={0} and calint_to={1}  and seq_no < " + seq_no + " and interest_payment>0 and item_status <> -9 and loanitemtype_code='LPX'";
            sql11 = WebUtil.SQLFormat(sql11, loancontract, calint);
            Sdt dt11 = WebUtil.QuerySdt(sql11);
            Decimal interest_payment = 0;
            if (dt11.Next())
            {
                interest_payment = dt11.GetDecimal("interest_payment"); // ยอดที่จ่าย
            }
            if (interest_payment > 0)
            {
                TimeSpan diff1 = calint.Subtract(date_now);
                Double TotalDay2 = diff1.TotalDays;
                int CalDay = Convert.ToInt32(TotalDay2); // คำนวณวัน

                if (TotalDay2 < 0)
                {
                    CalDay = 0;
                }
                else
                {

                    //คำนวณดอกเบี้ยคืน god
                    cal_int = (payment * intrate * CalDay) / year;
                }
            }

            if (rdintdec_type == 0)//ทศนิยม 2 ตำแหน่ง
            {
                int_cal = Convert.ToDecimal(Math.Round(cal_int, 2));

            }
            else if (rdintdec_type == 1)//ปัดทิ้ง
            {
                int_cal = Convert.ToDecimal(Math.Floor(cal_int));
            }
            else if (rdintdec_type == 2)//ปัดทิ้ง
            {
                int_cal = Convert.ToDecimal(Math.Ceiling(cal_int));
            }
            else
            {
                int_cal = Convert.ToDecimal(Math.Round(cal_int, 0));
            }

            if (int_cal < 0)
            {
                int_cal = 0;
            }

            return int_cal;
        }
     
        public static void DepositFormat(string depositNo)
        {
        }

        /// <summary>
        /// จะคืนค่า Path ตาม Application เช่น \\192.168.1.2\STORE\GSTORE\shrlon\file
        /// </summary>
        /// <param name="file"></param>
        /// <param name="application"></param>
        /// <returns></returns>
        public static String GetStoreFile(string application, string file)
        {
            return @"\\" + ConfigurationManager.AppSettings["gstore_ip"].ToString() + @"\GSTORE\" + application + @"\" + file;
        }

        /// <summary>
        /// จะคืนค่า url ตาม Application เช่น http://localhost/GCOOP/Saving/aaa.aspx
        /// </summary>
        /// <param name="application"></param>
        /// <param name="file"></param>
        /// <returns></returns>
        public static String CreateLinkDownload(string application, string file)
        {
            //<add key="gstore_ip" value="192.168.1.24"/>
            //<identity impersonate="true" userName="192.168.1.24\Administrator" password="Admin123" />
            file = file.Trim().Replace(' ', '+');
            file = file.Replace("/", "\\");
            return (new WebState().SsUrl) + "/WebDownload.aspx?DlApp=" + application + "&DlFile=" + file;
        }

        public static string getmemcoopid(string coopid, string memberno)
        {
            string memcoopid = "";
            try
            {
                DataTable dt = WebUtil.Query("select coop_control as coopcontrol from cmcoopmaster where coop_id ='" + coopid + "'");
                if (dt.Rows.Count > 0)
                {
                    string coop_control = dt.Rows[0]["coopcontrol"].ToString();

                    DataTable dt2 = WebUtil.Query("select mbmembmaster.coop_id as memcoopid from mbmembmaster , cmcoopmaster where  mbmembmaster.coop_id = cmcoopmaster.coop_id and mbmembmaster.member_no ='" + memberno + "' and cmcoopmaster.coop_control = '" + coop_control + "' ");
                    memcoopid = dt2.Rows[0]["memcoopid"].ToString();

                }
                else { return ""; }
            }
            catch { }
            return memcoopid;
        }

        public static string GetCoopControl(string coopid)
        {
            string coop_control = "";

            try
            {
                DataTable dt = WebUtil.Query("select coop_control as coopcontrol from cmcoopmaster where coop_id ='" + coopid + "'");
                if (dt.Rows.Count > 0)
                {
                    coop_control = dt.Rows[0]["coopcontrol"].ToString();
                }
                else { return ""; }
            }
            catch { }
            return coop_control;
        }

        public static string GetShareType(String CoopControl, String Member_no)
        {
            string sharetype = "";

            try
            {
                DataTable dt = WebUtil.Query("select sharetype_code from shsharemaster where member_no ='" + Member_no + "' and coop_id ='" + CoopControl + "'");
                if (dt.Rows.Count > 0)
                {
                    sharetype = dt.Rows[0]["sharetype_code"].ToString();
                }
                else { return ""; }
            }
            catch { }
            return sharetype;
        }

        public static decimal GetMemberType(String Coopid, String Member_no)
        {
            decimal member_type = 0;

            try
            {
                DataTable dt = WebUtil.Query("select member_type from mbmembmaster where member_no ='" + Member_no + "' and coop_id ='" + Coopid + "'");
                if (dt.Rows.Count > 0)
                {
                    member_type = Convert.ToInt32(dt.Rows[0]["member_type"]);
                }
                else { return 0; }
            }
            catch { }
            return member_type;
        }

        public static String GetDocumentPrefix(String Coopid, String DocumentCode)
        {
            String Document_Prefix = "";

            try
            {
                DataTable dt = WebUtil.Query("select document_prefix from cmdocumentcontrol where document_code ='" + DocumentCode + "' and coop_id ='" + Coopid + "'");
                if (dt.Rows.Count > 0)
                {
                    Document_Prefix = (dt.Rows[0]["document_prefix"]).ToString();
                }
                else { return null; }
            }
            catch { }
            return Document_Prefix;
        }

        public static String XmlPath
        {
            get
            {
                return ConfigurationManager.AppSettings["xmlPath"].ToString();
            }
        }

        public static DateTime GetProcessDate(String coopid, int year, int month)
        {

            DateTime process_date = DateTime.Now;
            year = year + 543;
            try
            {
                DataTable dt = WebUtil.Query("select * from amworkcalendar where coop_id ='" + coopid + "' and year =" + year + " and month = " + month + "");
                if (dt.Rows.Count > 0)
                {
                    int day = Convert.ToInt32(dt.Rows[0]["processdate"]);
                    year = year - 543;
                    string smonth = month.ToString();
                    if (smonth.Length < 2)
                    {
                        smonth = "0" + smonth;
                    }
                    string date = smonth + "/" + day.ToString() + "/" + year.ToString();
                    process_date = Convert.ToDateTime(date);
                }
                else { return process_date; }
            }
            catch { }
            return process_date;
        }

        public static str_itemchange str_itemchange_session(PageWeb page)
        {
            str_itemchange strList;
            try
            {
                strList = (str_itemchange)page.Session["strItemchange"];
            }
            catch
            {
                strList = new str_itemchange();
            }
            return strList;
        }

      
        public static CoreSavingLibrary.WcfNShrlon.str_itemchange nstr_itemchange_session(PageWeb page)
        {
            CoreSavingLibrary.WcfNShrlon.str_itemchange strList;
            try
            {
                strList = (CoreSavingLibrary.WcfNShrlon.str_itemchange)page.Session["strItemchange"];
            }
            catch
            {
                strList = new CoreSavingLibrary.WcfNShrlon.str_itemchange();
            }
            return strList;
        }

        public static String SQLFormat(string sql, params object[] args)
        {
            string[] nargs = new string[args.Length];
            for (int i = 0; i < args.Length; i++)
            {
                if (WebUtil.IsDateType(args[i].GetType())) //date type
                {
                    try
                    {
                        DateTime dtm = (DateTime)args[i];
                        if (dtm.Year < 1700)
                        {
                            nargs[i] = "NULL";
                        }
                        else
                        {
                            if (Sta.IS_MYSQL_MODE)
                            {

                                nargs[i] = "'" + dtm.ToString("yyyy-MM-dd", WebUtil.EN) + "'";
                            }
                            else
                            {
                                nargs[i] = "to_date('" + dtm.ToString("yyyy-MM-dd HH:mm:ss", WebUtil.EN) + "', 'yyyy-mm-dd hh24:mi:ss')";
                            }
                        }
                    }
                    catch
                    {
                        nargs[i] = "NULL";
                    }
                }
                else if (WebUtil.IsNumberType(args[i].GetType())) //number type
                {
                    try
                    {
                        decimal dec = Convert.ToDecimal(args[i]);
                        nargs[i] = dec.ToString();
                    }
                    catch
                    {
                        nargs[i] = "NULL";
                    }
                }
                else
                {
                    try
                    {
                        nargs[i] = "'" + args[i].ToString().Trim() + "'";
                    }
                    catch
                    {
                        nargs[i] = "NULL";
                    }
                }
            }
            return string.Format(sql, nargs);
        }

        public static bool IsDateType(Type type)
        {
            try
            {
                return type.FullName.ToLower() == "system.datetime";
            }
            catch { }
            return false;
        }

        public static bool IsNumberType(Type type)
        {
            try
            {
                string tName = type.FullName.ToLower();
                return tName == "system.int16" || tName == "system.int32" || tName == "system.int64" || tName == "system.decimal" || tName == "system.float";
            }
            catch { }
            return false;
        }

        public static string Fill(string p, int roundNum)
        {
            StringBuilder strb = new StringBuilder();
            for (int i = 0; i < roundNum; i++)
            {
                strb.Append(p);
            }
            return strb.ToString();
        }

        public static decimal MathTruncate(decimal number, int digit)
        {
            string xxx = number.ToString("0." + WebUtil.Fill("0", digit));
            return Convert.ToDecimal(xxx.Substring(0, xxx.IndexOf(".") + digit));
        }

        /// <summary>
        /// หาค่า Virtual directory ที่แท้จริง แต่จะไม่นำไปใช้ในระบบโดยตรง
        /// </summary>
        /// <returns></returns>
        public static string GetVirtualDirectoryCurrent()
        {
            try
            {
                string savingPath = GetSavingUrlCurrent();
                savingPath = savingPath.Replace(("/" + coreSubURI + "/Saving/"), "");
                string vDir = savingPath.Substring(savingPath.LastIndexOf("/") + 1);
                return vDir.ToUpper();
            }
            catch
            {
                return coreURI;
            }
        }

        /// <summary>
        /// หาค่า Virtual directory ที่จะใช้ในระบบ
        /// </summary>
        /// <returns></returns>
        public static string GetVirtualDirectory()
        {
            // jmeter support
            try
            {
                String jmeterVdir = HttpContext.Current.Request["jmeter_vdir"];
                if (!String.IsNullOrEmpty(jmeterVdir))
                {
                    return jmeterVdir;
                }
            }
            catch { }

            string vDir = GetVirtualDirectoryCurrent();

            if (vDir == coreURI)
            {
                try
                {
                    string cvDir = HttpContext.Current.Request.Cookies["vdir"].Value;
                    if (!string.IsNullOrEmpty(cvDir))
                    {
                        return cvDir;
                    }
                }
                catch { }
            }

            return vDir;
        }

        /// <summary>
        /// ดึงค่า url ปัจจุบันถึง path Saving เช่น http://localhost/CORE/GCOOP/Saving/
        /// </summary>
        /// <returns></returns>
        public static string GetSavingUrlCurrent()
        {
            string fullUrl = HttpContext.Current.Request.Url.AbsoluteUri;
            int indexOfSaving = fullUrl.ToLower().IndexOf(("/" + coreSubURI.ToLower() + "/saving"));
            string savUrl = fullUrl.Substring(0, indexOfSaving) + "/" + coreSubURI + "/Saving/";
            return savUrl;
        }

        /// <summary>
        /// ดึงค่า url path Saving ตาม virtual directory เช่น http://localhost/CEN/GCOOP/Saving/
        /// </summary>
        /// <returns></returns>
        public static string GetSavingUrl()
        {
            string savUrl = GetUrlAddressOnly() + GetVirtualDirectory() + "/" + coreSubURI + "/Saving/";
            return savUrl;
        }

        /// <summary>
        /// ดึงค่า url vdir = CORE ปัจจุบันถึง path Saving เช่น http://localhost/CORE/GCOOP/Saving/
        /// </summary>
        /// <returns></returns>
        public static string GetSavingUrlCore()
        {
            return GetUrlAddressOnly() + coreURI + "/" + coreSubURI + "/Saving/";
        }

        /// <summary>
        /// ดึงค่า url vdir = CORE ปัจจุบันถึง path Saving เช่น http://localhost/CORE/GCOOP/Saving/
        /// </summary>
        /// <returns></returns>
        public static string GetUrlCore()
        {
            return GetUrlAddressOnly() + coreURI + "/" + coreSubURI + "/";
        }

        /// <summary>
        /// ดึงค่า url ปัจจุบันไม่รวม path เช่น http://localhost/ , https://icoopthai.co:443/
        /// </summary>
        /// <returns></returns>
        public static string GetUrlAddressOnly()
        {
            string fullUrl = HttpContext.Current.Request.Url.AbsoluteUri;
            int indexOfSaving = fullUrl.ToLower().IndexOf("/" + coreSubURI.ToLower() + "/saving");
            string savUrl = fullUrl.Substring(0, indexOfSaving);
            savUrl = savUrl.Substring(0, savUrl.LastIndexOf("/") + 1);
            return savUrl;
        }

        /// <summary>
        /// คืนค่า physical gcoop path ที่โปรแกรมกำลังทำงานอยู่ (แต่อาจไม่นำไปใช้งานจริง) เช่น C:\\ICOOP_ALL\\ICORE\\ICOOP\\
        /// </summary>
        /// <returns></returns>
        public static string GetGcoopPathCurrent()
        {
            string appPath = HttpContext.Current.Request.ApplicationPath;
            string physicalPath1 = HttpContext.Current.Request.MapPath(appPath);
            string physicalPathGCOOP = physicalPath1.Substring(0, physicalPath1.ToUpper().IndexOf(("\\" + coreSubURI + "\\")) + 7);
            return physicalPathGCOOP;
        }

        /// <summary>
        /// คืนค่า physical gcoop path ตาม virtual directory เช่น C:\\ICOOP_ALL\\ICORE\\ICOOP\\
        /// </summary>
        /// <returns></returns>
        public static string GetGcoopPath()
        {
            string path = GetGcoopPathCurrent();
            path = path.Replace(("\\" + coreSubURI + "\\"), "");
            path = path.Substring(0, path.LastIndexOf("\\") + 1);
            path += GetVirtualDirectory() + "\\" + coreSubURI + "\\";
            return path;
        }

        public static string GetGcoopRootDir()
        {
            string rootDir = HttpContext.Current.Request.MapPath("~/").Substring(0, 1);
            coreRoot = "C:\\ICOOP_ALL".Replace("C:", (rootDir + ":"));
            return coreRoot;

        }

        /// <summary>
        /// คืนค่า physical gcoop path CORE เช่น C:\GCOOP_ALL\CORE\GCOOP\
        /// </summary>
        /// <returns></returns>
        public static string GetGcoopPathCore()
        {
            coreRoot = GetGcoopRootDir();
            return coreRoot + "\\" + coreURI + "\\" + coreSubURI + "\\";
        }

        /// <summary>
        /// ดึงค่าเฉพาะ ip/domain address จาก web server เช่น 192.168.10.10 หรือ sav.icoopthai.co หรือ localhost
        /// </summary>
        /// <returns></returns>
        public static string GetSavingAddress()
        {
            string fullUrl = "127.0.0.1";
            try
            {
                fullUrl = HttpContext.Current.Request.Url.Host;
            }
            catch { 
            
            }
            return fullUrl;
        }
        public static string ViewAccountNoFormat(String deptAccountNo)
        {

            DataTable dt = WebUtil.Query("select deptcode_mask from DPDEPTCONSTANT");
            String format = dt.Rows[0]["deptcode_mask"].ToString().ToUpper();//"X-XX-XXXXXXX";
            char[] fc = format.ToCharArray();
            char[] ac = deptAccountNo.ToCharArray();
            String accNo = "";
            int j = 0;
            for (int i = 0; i < fc.Length; i++)
            {
                if (fc[i] != 'X')
                {
                    accNo += fc[i].ToString();
                }
                else
                {
                    try
                    {
                        accNo += ac[j++];
                    }
                    catch { accNo += ""; }
                }
            }
            return accNo;
        }
        public static string ChangeAccountNoFormat(String deptAccountNo)
        {
            String accNo = "";
            try
            {
                DataTable dt = WebUtil.Query("select deptaccount_no from dpdeptmaster where deptaccount_no like '%" + deptAccountNo + "' "+(Sta.IS_MYSQL_MODE?" limit 0,2 ":" and ROWNUM <= 1 ")+"");
                accNo = dt.Rows[0]["deptaccount_no"].ToString();  

            }
            catch { accNo = "000000000"; }

            return accNo;
        }

        public static string ChangeAccountNoFormat2(String deptAccountNo)
        {
            String accNo = "",oop="";
            try
            {
                oop = deptAccountNo;
                if (deptAccountNo.Length >= 3)
                {
                    ////Runja
                    string dept1 = deptAccountNo.Substring(0, 2);
                    Sdt dt2 = WebUtil.QuerySdt("select depttype_code from dpdepttype where depttype_code ='" + dept1 + "' ");
                    if (dt2.Next())
                    {
                        string dept2 = deptAccountNo.Substring(2);
                        int g = 10 - deptAccountNo.Length;
                        string dept = "";
                        for (int i = 0; i < g; i++)
                        {
                            dept += "0";
                        }                       
                        deptAccountNo = dept1 + dept + dept2;
                        //Sdt fg = WebUtil.QuerySdt("select deptaccount_no from dpdeptmaster where deptaccount_no like '%" + deptAccountNo + "' and depttype_code='" + dept1 + "' ");
                         Sdt fg = WebUtil.QuerySdt("select deptaccount_no from dpdeptmaster where deptaccount_no = '" + deptAccountNo + "' " ) ;
                        if (!fg.Next())
                        {
                            deptAccountNo = oop;
                        }
                        else
                        {
                            deptAccountNo = fg.GetString("deptaccount_no");
                            accNo = deptAccountNo;
                        }
                    }
                    
                }


               
                DataTable dt = WebUtil.Query("select deptaccount_no from dpdeptmaster where deptaccount_no like '%" + deptAccountNo + "' " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " and ROWNUM <= 1 ") + "");
                accNo = dt.Rows[0]["deptaccount_no"].ToString();//"X-XX-XXXXXXX";

            }
            catch { accNo = "000000000"; }

            return accNo;
        }
        public static string GetDpDeptConstant(String column)
        {
            String result = "";
            try
            {
                DataTable dt = WebUtil.Query("select " + column + " from DPDEPTCONSTANT");
                if (dt.Rows.Count < 1)
                {
                    //ta.Close();
                    throw new Exception("ไม่มีข้อมูล column " + column);
                }
                result = dt.Rows[0][column].ToString();
            }
            catch { }
            return result;
        }
        public static string GetRecpPayTypeDesc(String recpPayCode)
        {
            String result = "";
            try
            {
                String sql = "SELECT RECPPAYTYPE_DESC FROM DPUCFRECPPAYTYPE WHERE RECPPAYTYPE_CODE = '" + recpPayCode + "'";
                DataTable dt = WebUtil.Query(sql);
                //Sdt dt = ta.Query(sql);
                if (dt.Rows.Count > 0)
                {
                    result = dt.Rows[0]["RECPPAYTYPE_DESC"].ToString();
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
            return result;
        }
        public static DataTable GetChildDeptWith(String wsPass, String itemType)
        {

            try
            {
                String itemCode = "";
                String where = "";
                if (itemType == "+")
                {
                    itemCode = "DEP";
                }
                else if (itemType == "-")
                {
                    itemCode = "WID";
                }
                else if (itemType == "/")
                {
                    itemCode = "CLS";
                }
                if (itemCode != "")
                {
                    where = "and group_itemtpe='" + itemCode + "'";
                }
                else
                {
                    where = "and 1 = 2";
                }
                String sql = @"
                SELECT RECPPAYTYPE_CODE,   
                     RECPPAYTYPE_DESC,   
                     RECPPAYTYPE_SORT,   
                     GROUP_ITEMTPE,   
                     MONEYTYPE_SUPPORT,   
                     RECPPAYTYPE_FLAG,   
                     ACTIVE_FLAG  
                FROM DPUCFRECPPAYTYPE       
                WHERE 1 = 1 " + where + @" and active_flag=1 
                order by order_sort, RECPPAYTYPE_CODE";
                DataTable dt = WebUtil.Query(sql);
                dt.TableName = "deptwith";
                return dt;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public static String GetCardPerson(String membNo)
        {
            String result = "";
            try
            {
                String sql = @"
                SELECT        CARD_PERSON
                FROM          MBMEMBMASTER LEFT OUTER JOIN
                              MBUCFPRENAME ON MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE
                WHERE        (MBMEMBMASTER.MEMBER_NO = '" + membNo + "')";
                DataTable dt = WebUtil.Query(sql);
                if (dt.Rows.Count > 0)
                {
                    result = dt.Rows[0]["CARD_PERSON"].ToString();
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
            return result;
        }

        public static String ViewCardMemberFormat(String memberCard)
        {
            String format = "X-XXXX-XXXXX-XX-X";//"X-XX-XXXXXXX";
            char[] fc = format.ToCharArray();
            char[] ac = memberCard.ToCharArray();
            String cardNo = "";
            int j = 0;
            for (int i = 0; i < fc.Length; i++)
            {
                if (fc[i] != 'X')
                {
                    cardNo += fc[i].ToString();
                }
                else
                {
                    try
                    {
                        cardNo += ac[j++];
                    }
                    catch { cardNo += ""; }
                }
            }
            return cardNo;
        }

        /// <summary>
        /// month in thai
        /// </summary>
        public static String ConvertMonthtoThai(int int_month)
        {
            try
            {
                string t_month = "";
                switch (int_month)
                {
                    case 1: t_month = "มกราคม";
                        break;
                    case 2: t_month = "กุมภาพันธ์";
                        break;
                    case 3: t_month = "มีนาคม";
                        break;
                    case 4: t_month = "เมษายน";
                        break;
                    case 5: t_month = "พฤษภาคม";
                        break;
                    case 6: t_month = "มิถุนายน";
                        break;
                    case 7: t_month = "กรกฎาคม";
                        break;
                    case 8: t_month = "สิงหาคม";
                        break;
                    case 9: t_month = "กันยายน";
                        break;
                    case 10: t_month = "ตุลาคม";
                        break;
                    case 11: t_month = "พฤศจิกายน";
                        break;
                    case 12: t_month = "ธันวาคม";
                        break;
                }
                return t_month;
            }
            catch
            {
                return "";
            }
        }

        /// <summary>
        /// Get Accyear
        /// </summary>
        public static int GetAccyear(String as_coopid, DateTime adtm_workdate)
        {
            string ls_sql = "";
            int li_year = DateTime.Now.Year + 543;
            ls_sql = @"select account_year
                from cmaccountyear
                where coop_id = {0}
                and {1} between accstart_date and accend_date";
            ls_sql = SQLFormat(ls_sql, as_coopid, adtm_workdate);

            try
            {
                Sdt dt = QuerySdt(ls_sql);
                if (dt.Next())
                {
                    li_year = dt.GetInt32("account_year");
                }
                return li_year;
            }
            catch
            {
                return li_year;
            }
        }

        /// <summary>
        /// Get Accyear
        /// </summary>
        public static DateTime GetDateAccyear(String as_coopid, DateTime adtm_workdate)
        {
            string ls_sql = "";
            DateTime li_year = DateTime.Now;
            ls_sql = @"select accstart_date
                from cmaccountyear
                where coop_id = {0}
                and {1} between accstart_date and accend_date";
            ls_sql = SQLFormat(ls_sql, as_coopid, adtm_workdate);

            try
            {
                Sdt dt = QuerySdt(ls_sql);
                if (dt.Next())
                {
                    li_year = dt.GetDate("accstart_date");
                }
                return li_year;
            }
            catch
            {
                return li_year;
            }
        }
        public static string GetMembnoBySalaryid(string salary_id, string coop_id)
        {
            string member_no = "";
            try
            {
                string sql = "select member_no from mbmembmaster where trim(salary_id)={0} and coop_id={1}";
                sql = WebUtil.SQLFormat(sql, salary_id.Trim(), coop_id);
                DataTable dt = WebUtil.Query(sql);

                if (dt.Rows.Count > 0)
                {
                    member_no = Convert.ToString(dt.Rows[0]["member_no"]);
                }
                else { member_no = ""; }
                return member_no;
            }
            catch (Exception ex)
            {
                return "";
            }

        }
        public static String GetDeptCodeMask()
        {

            try
            {
                String sql = "SELECT deptcode_mask  FROM DPDEPTCONSTANT";
                Sdt dt = QuerySdt(sql);
                if (!dt.Next()) throw new Exception("ไม่มีข้อมูลการแสดงผลเลขที่บัญชี");
                String deptcodeMask = dt.GetString(0);
                return deptcodeMask;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public static int UpdateMaxBookSeqNo(String wsPass, String deptAccountNo, String branchId)
        {

            try
            {
                String maxSql = @"
                SELECT max(seq_no) as max_seqno
                FROM 
                    DPDEPTSTATEMENT,
                    DPUCFDEPTITEMTYPE
                WHERE ( DPUCFDEPTITEMTYPE.DEPTITEMTYPE_CODE = DPDEPTSTATEMENT.DEPTITEMTYPE_CODE) 
                AND ( ( dpdeptstatement.deptaccount_no = '" + deptAccountNo + @"' ) 
                AND ( DPDEPTSTATEMENT.FORPRNBK_FLAG = 1 ) 
                AND ( DPDEPTSTATEMENT.BRANCH_ID = '" + branchId + @"' ))
                AND prntopb_status = '1'";

                Sdt dt = QuerySdt(maxSql);
                if (!dt.Next())
                {
                    throw new Exception("ไม่พบข้อมูลรายการพิมพ์สมุด");
                }
                int re = dt.GetInt32(0);
                Sdt update = QuerySdt("update dpdeptmaster set lastrec_no_pb = " + re + " where deptaccount_no='" + deptAccountNo + "'");
                return re;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public static String runProcessingReportExtend(WebState state, string app, string gid, string rid, string criteriaXML, string pdfFileName, string printerName)
        {
            return runProcessingReportCmd(true, state, app, gid, rid, criteriaXML, pdfFileName, printerName);
        }
        public static String runProcessingReport(WebState state, string app, string gid, string rid, string criteriaXML, string pdfFileName, string printerName)
        {
            return runProcessingReportCmd(false,state,  app,  gid,  rid,  criteriaXML,  pdfFileName,  printerName);
        }
        public static String runProcessingReportCmd(bool extendFlag,WebState state, string app, string gid, string rid, string criteriaXML, string pdfFileName, string printerName)
        {
            if (printerName == null || printerName.Trim() == "")
            {
                printerName = "PDF";
            }
            if (printerName.ToLower().Trim().IndexOf("xls") >= 0)
            {
                pdfFileName = pdfFileName.Replace(".pdf", ".xls");
            }
            string workdate = "to_date('" + state.SsWorkDate.ToString("yyyy-MM-dd", WebUtil.EN) + "', 'yyyy-mm-dd')";
            XmlService xs = new XmlService(WebUtil.GetGcoopPath());
            XmlConnection xc = xs.GetXmlConnection(state.SsConnectionIndex);

            String rootPathCore = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (WebUtil.coreURI) + "\\ICOOP\\";
            String rootPathExtend = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (WebUtil.GetVirtualDirectory()) + "\\ICOOP\\";
            String rootExe = "PBReport125\\pbreport.exe";
            extendFlag = xs.PBpreport125ExtendFlag ? (FileExists(rootPathExtend + rootExe)) : extendFlag;
            
            String rootPath = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (extendFlag ? WebUtil.GetVirtualDirectory():WebUtil.coreURI) + "\\ICOOP\\";
            String rootTempPath = System.Environment.GetEnvironmentVariable("TEMP");
            String report_path = rootPath + "Saving\\WSRPDF\\" + pdfFileName;
            String report_temppath = rootTempPath + "\\" + pdfFileName;
            string xmlConnPath = rootPath + "\\XMLConfig\\server.connection_string.xml";
            string cmd = "PBReport125\\pbreport.exe process_id={process_id} dbconnect=" + xc.ConnectionString;
            //string p = WebUtil.execProcessReport(rootPath, cmd, object_id, state.SsCoopControl, state.SsCoopId, state.SsUsername, aug1, aug2, aug3);
            String PROCESS_ID = DateTime.Now.Ticks.ToString();//DateTime.Now.ToLongDateString()+DateTime.Now.ToLongTimeString();
            cmd = cmd.Replace("{process_id}", PROCESS_ID);
            //string sql = "insert into cmprocessing (PROCESS_ID,COOP_CONTROL,COOP_ID,ENTRY_ID,ENTRY_DATE,RUNTIME_STATUS,RUNTIME_MESSAGE,LABEL_NAME,object_name,cmd,CRITERIA_XML,application,report_group_id,report_id,report_path,printer,workdate)values";
            //sql += "('" + PROCESS_ID + "','" + state.SsCoopControl + "','" + state.SsCoopId + "','" + state.SsUsername + "',sysdate,0,'กำลังดำเนินการเริ่มประมวลผลรายงาน','','" + rid + "','" + (rootPath + cmd) + "','" + criteriaXML + "','" + app + "','" + gid + "','" + rid + "','" + report_path + "','" + printerName + "'," + workdate + ")";
            //WebUtil.ExeSQL(sql);
            Sta ta_ = new Sta(state.SsConnectionString);
            try
            {
                ta_.Transection();
                if (Sta.IS_MYSQL_MODE)
                {
                    ta_.Exe("alter table cmprocessing add core_flag decimal(1,0) default 1 not null ", true);
                }
                else
                {
                    ta_.Exe("alter table cmprocessing add core_flag number(1,0) default 1 not null ", true);
                }
                ta_.Commit();
            }
            catch
            {
                ta_.RollBack();
            }
            finally
            {
                ta_.Close();
            }
            //Fix SQL Error : ORA-01704
            string sql = "insert into cmprocessing (PROCESS_ID,COOP_CONTROL,COOP_ID,ENTRY_ID,ENTRY_DATE,RUNTIME_STATUS,RUNTIME_MESSAGE,LABEL_NAME,object_name,cmd,CRITERIA_XML,application,report_group_id,report_id,report_path,printer,workdate,core_flag)values";
            sql += "('" + PROCESS_ID + "','" + state.SsCoopControl + "','" + state.SsCoopId + "','" + state.SsUsername + "',sysdate,0,'กำลังดำเนินการเริ่มประมวลผลรายงาน','','" + rid + "','" + (rootPath + cmd) + "'," + Sta.SQL_PARAM_PREFIX + "CRITERIA_XML,'" + app + "','" + gid + "','" + rid + "','" + report_path + "','" + printerName + "'," + workdate + "," + (extendFlag ? 0 : 1) + ")";
            //WebState state = new WebState();
            Sta ta = new Sta(state.SsConnectionString);
            try
            {
                ta.Transection();
                ta.AddParameter("CRITERIA_XML", criteriaXML);
                //ta.AddParameter("CRITERIA_XML_1", aug2);
                //ta.AddParameter("CRITERIA_XML_2", aug3);
                ta.Exe(sql, true);
                ta.Commit();
                ta.Close();
            }
            catch
            {
                try
                {
                    ta.RollBack();
                }
                catch { }
            }
            finally
            {
                try
                {
                    ta.Close();
                }
                catch { }
            }

            RunCommandReport(rootPath, cmd + " " + PROCESS_ID, PROCESS_ID, report_temppath, report_path);
            String currentProcessing = "<div id=\"msgProcessing\" style=\"display:none;\"><iframe id=\"processFrame\" frameborder=0 style=\"display:;\" height=100 width=750 src=\"" + WebUtil.GetSavingUrlCore() + "webprocessingframe.aspx?p=" + PROCESS_ID + "&d=" + xc.ConnectionString + "\" ></iframe></div>";
            return currentProcessing;
        }

        public static String runProcessingExtend(WebState state, string object_id, string aug1, string aug2, string aug3)
        {
            return runProcessingCmd(true, state, object_id, aug1, aug2, aug3);
        }

        public static String runProcessing(WebState state, string object_id, string aug1, string aug2, string aug3)
        {
            return runProcessingCmd(false, state, object_id, aug1, aug2, aug3);
        }
        public static String runProcessingCmd(bool extendFlag,WebState state, string object_id, string aug1, string aug2, string aug3)
        {
            XmlService xs = new XmlService(WebUtil.GetGcoopPath());
            XmlConnection xc = xs.GetXmlConnection(state.SsConnectionIndex);

            String rootPathCore = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (WebUtil.coreURI) + "\\ICOOP\\";
            String rootPathExtend = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (WebUtil.GetVirtualDirectory()) + "\\ICOOP\\";
            String rootExe = "PBProcess\\PBProcess.exe";
            extendFlag = xs.PBprocess125ExtendFlag ? (FileExists(rootPathExtend + rootExe)) : extendFlag;
            
            String rootPath = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (extendFlag ? WebUtil.GetVirtualDirectory() : WebUtil.coreURI) + "\\ICOOP\\";
            string xmlConnPath = rootPath + "\\XMLConfig\\server.connection_string.xml";
            string cmd = "PBProcess\\PBProcess.exe process_id={process_id} dbconnect=" + xc.ConnectionString + "";
            string p = WebUtil.execProcess(extendFlag,rootPath, cmd, object_id, state.SsCoopControl, state.SsCoopId, state.SsUsername, aug1, aug2, aug3, state.SsApplication, state.SsWorkDate);
            String currentProcessing = "<div id=\"msgProcessing\" style=\"display:none;\"><iframe id=\"processFrame\" frameborder=0 style=\"display:;\" height=100 width=750 src=\"" + WebUtil.GetSavingUrlCore() + "webprocessingframe.aspx?p=" + p + "&d=" + xc.ConnectionString + "\" ></iframe></div>";
            return currentProcessing;
        }

        public static String runProcessingCappExtend(WebState state, string object_id, string aug1, string aug2, string aug3)
        {
            return runProcessingCappCmd(true, state, object_id, aug1, aug2, aug3);
        }

        public static String runProcessingCapp(WebState state, string object_id, string aug1, string aug2, string aug3)
        {
            return runProcessingCappCmd(false, state, object_id, aug1, aug2, aug3);
        }
        public static String runProcessingCappCmd(bool extendFlag, WebState state, string object_id, string aug1, string aug2, string aug3)
        {
            XmlService xs = new XmlService(WebUtil.GetGcoopPath());
            XmlConnection xc = xs.GetXmlConnection(state.SsConnectionIndex);

            String rootPathCore = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (WebUtil.coreURI) + "\\ICOOP\\";
            String rootPathExtend = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (WebUtil.GetVirtualDirectory()) + "\\ICOOP\\";
            String rootExe = "AppProcessing\\bin\\Release\\AppProcessing.exe";
            extendFlag = xs.PBprocess125ExtendFlag ? (FileExists(rootPathExtend + rootExe)) : extendFlag;

            String rootPath = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + (extendFlag ? WebUtil.GetVirtualDirectory() : WebUtil.coreURI) + "\\ICOOP\\";
            string xmlConnPath = rootPath + "\\XMLConfig\\server.connection_string.xml";
            string cmd = "AppProcessing\\bin\\Release\\AppProcessing.exe process_id={process_id} dbconnect=" + xc.ConnectionString + "";
            string p = WebUtil.execProcess(extendFlag,rootPath, cmd, object_id, state.SsCoopControl, state.SsCoopId, state.SsUsername, aug1, aug2, aug3, state.SsApplication, state.SsWorkDate);
            String currentProcessing = "<div id=\"msgProcessing\" style=\"display:none;\"><iframe id=\"processFrame\" frameborder=0 style=\"display:;\" height=100 width=750 src=\"" + WebUtil.GetSavingUrlCore() + "webprocessingframe.aspx?p=" + p + "&d=" + xc.ConnectionString + "\" ></iframe></div>";
            return currentProcessing;
        }
        public static String execProcess(bool extendFlag, string rootPath, string cmd, string object_name, string coop_control, string coop_id, string entry_id, string aug1, string aug2, string aug3, string application, DateTime SsWorkDate)
        {
            string workdate = "to_date('" + SsWorkDate.ToString("yyyy-MM-dd", WebUtil.EN) + "', 'yyyy-mm-dd')";
            String PROCESS_ID = DateTime.Now.Ticks.ToString();//DateTime.Now.ToLongDateString()+DateTime.Now.ToLongTimeString();
            cmd = cmd.Replace("{process_id}", PROCESS_ID);
            //string sql = "insert into cmprocessing (PROCESS_ID,COOP_CONTROL,COOP_ID,ENTRY_ID,ENTRY_DATE,RUNTIME_STATUS,RUNTIME_MESSAGE,LABEL_NAME,object_name,cmd,CRITERIA_XML,CRITERIA_XML_1,CRITERIA_XML_2,application,workdate)values";
            //sql += "('" + PROCESS_ID + "','" + coop_control + "','" + coop_id + "','" + entry_id + "',sysdate,0,'กำลังดำเนินการเริ่มประมวลผล','','" + object_name + "','" + (rootPath + cmd) + "','" + aug1 + "','" + aug2 + "','" + aug3 + "','" + application + "'," + workdate + ")";
            //WebUtil.ExeSQL(sql);
            WebState state = new WebState();
            Sta ta_ = new Sta(state.SsConnectionString);
            try
            {
                ta_.Transection();
                if (Sta.IS_MYSQL_MODE)
                {
                    ta_.Exe("alter table cmprocessing add core_flag decimal(1,0) default 1 not null ", true);
                }
                else {
                    ta_.Exe("alter table cmprocessing add core_flag number(1,0) default 1 not null ", true);
                }
                ta_.Commit();
            }
            catch
            {
                ta_.RollBack();
            }
            finally
            {
                ta_.Close();
            }
            //Fix SQL Error : ORA-01704
            string sql = "insert into cmprocessing (PROCESS_ID,COOP_CONTROL,COOP_ID,ENTRY_ID,ENTRY_DATE,RUNTIME_STATUS,RUNTIME_MESSAGE,LABEL_NAME,object_name,cmd,CRITERIA_XML,CRITERIA_XML_1,CRITERIA_XML_2,application,workdate,core_flag)values";
            sql += "('" + PROCESS_ID + "','" + coop_control + "','" + coop_id + "','" + entry_id + "',sysdate,0,'กำลังดำเนินการเริ่มประมวลผล','','" + object_name + "','" + (rootPath + cmd) + "'," + Sta.SQL_PARAM_PREFIX + "CRITERIA_XML," + Sta.SQL_PARAM_PREFIX + "CRITERIA_XML_1," + Sta.SQL_PARAM_PREFIX + "CRITERIA_XML_2,'" + application + "'," + workdate + "," + (extendFlag ? 0 : 1) + ")";
            Sta ta = new Sta(state.SsConnectionString);
            try
            {
                ta.Transection();
                ta.AddParameter("CRITERIA_XML", aug1);
                ta.AddParameter("CRITERIA_XML_1", aug2);
                ta.AddParameter("CRITERIA_XML_2", aug3);
                ta.Exe(sql, true);
                ta.Commit();
                ta.Close();
            }
            catch
            {
                try
                {
                    ta.RollBack();
                }
                catch { }
            }
            finally
            {
                try
                {
                    ta.Close();
                }
                catch { }
            }

            RunCommand(rootPath, cmd + " " + PROCESS_ID, PROCESS_ID);
            return PROCESS_ID;
        }

        public static System.Diagnostics.Process process;

        public static void CloseProcess(string process_id)
        {
            RunKillcommand("pbprocess.exe", process_id);
            RunKillcommand("pbreport.exe", process_id);
            CloseProcess();
        }

        public static void CloseProcess()
        {
            try
            {
                process.CloseMainWindow();
            }
            catch { }
            try
            {
                process.Close();
            }
            catch { }
        }

        /*
        
        CREATE TABLE "CMDBPROFILE" (
                 "DBPROFILE_ID" VARCHAR2(30) NOT NULL, 
                 "COOP_ID" VARCHAR2(10) NOT NULL,
                 "ORA_ADM_USR" VARCHAR2(20) NOT NULL,
                 "ORA_ADM_PWD" VARCHAR2(20) NOT NULL,
                 "ORA_ADM_EXP_USR" VARCHAR2(20) NOT NULL,
                 "ORA_ADM_EXP_PWD" VARCHAR2(20) NOT NULL,
                 "ORA_TARGET_DB_HOST" VARCHAR2(40) NOT NULL,
                 "ORA_TARGET_DB_PORT" VARCHAR2(6) NOT NULL,
                 "ORA_TARGET_DB_SID" VARCHAR2(10) NOT NULL,
                 "ORA_TARGET_USR" VARCHAR2(20) NOT NULL,
                 "ORA_TARGET_PWD" VARCHAR2(20) NOT NULL,
                 "ORA_VERSION" VARCHAR2(10) NOT NULL,
                 "DATAPUMP_DIR" CHAR(1) NOT NULL,
                 "DATAPUMP" VARCHAR2(20) NOT NULL,
                 "ORA_OS_USR" VARCHAR2(20) NOT NULL,
                 "ORA_OS_PWD" VARCHAR2(20) NOT NULL,
                 "NLS_LANG" VARCHAR2(50) NOT NULL,
                 "USED_FLAG" NUMBER(1) DEFAULT 1 NOT NULL ,
                 "BACKUP_PATH" VARCHAR2(250) NOT NULL,
				 "SCRIPT_PATH" VARCHAR2(250) NOT NULL,
				 "SCHEDULER" VARCHAR2(250) NOT NULL,
                 "SCHEDULER_DEPLOY_FLAG" NUMBER(1) DEFAULT 0 NOT NULL 
                 ) ;
        ALTER TABLE "CMDBPROFILE" ADD ( CONSTRAINT CMDBPROFILE_PK PRIMARY KEY ( "DBPROFILE_ID","COOP_ID" )) ;        
     

        */
        public static string createBatchBackupOracle(string batchFile)
        {
            string batchfilename = "OracleBackup";
            List<string> cmdList = new List<string>();
            cmdList.Add(""); 
            try
            {
                string readText = File.ReadAllText(batchFile);
                    cmdList.Add(readText);
                string filename = RunCommand(cmdList, batchfilename);
                string output = "การดำเนินการโดย " + filename+" <br/>สามารถตรวจสอบได้ในหน้าจอนี้ โดยใช้ระยะเวลาสักครู่ (5-10 นาที)";
                return output;                
            }
            catch 
            {
                return null;
            }
        }

        public static string BACKUP_SCRIPT_PATH_ROOT {
            get{
                return WebUtil.GetGcoopPathCore() + "\\DB\\";
            }
        }

        public static string killOracleSessionByUser(string ora_user,string ora_sys_usr,string ora_sys_pwd,string ora_host)
        {            
            string batchfilename="KillOracleSession";
            List<string> cmdList = new List<string>();
            cmdList.Add("");
            try
            {

                   cmdList.Add(" begin \r\n");
                   cmdList.Add("  for i in (SELECT concat(concat(' ALTER SYSTEM KILL SESSION ''',concat(concat( s.sid,',' ),s.serial#) ),''' IMMEDIATE') as sql  FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE   s.username='" + ora_user.ToUpper() + "' ) LOOP \r\n");
                   cmdList.Add("   execute immediate i.\"SQL\"||' '; \r\n");
                   cmdList.Add("  end loop; \r\n");
                   cmdList.Add(" end; \r\n");
                   cmdList.Add(" / \r\n");

                 string filename = createSQLScript(cmdList, batchfilename);

                 cmdList = new List<string>();
                 cmdList.Add("echo quit|@" + filename + ";|sqlplus " + ora_sys_usr+"/"+ora_sys_pwd+"@"+ora_host+" as sysdba");

                 batchfilename = "KillOracleSession";
                 filename = createBatchScript(cmdList, batchfilename);

                 System.Diagnostics.Process.Start(filename);
                return filename;
            }catch{
                return null;
            }
        }

        public static string createBatchBackupOracle(string DBPROFILE_ID, string coop_id,bool execute_flag,ref string fileBatchPath)
        {
            string batchfilename="OracleBackup";
            List<string> cmdList = new List<string>();
            cmdList.Add("");
            try
            {
                string sql = "select * from CMDBPROFILE where trim(DBPROFILE_ID)={0} and coop_id={1} and USED_FLAG=1";
                sql = WebUtil.SQLFormat(sql, DBPROFILE_ID.Trim(), coop_id);
                DataTable dt = WebUtil.Query(sql);
                if (dt.Rows.Count > 0)
                {
                    string readText = File.ReadAllText(BACKUP_SCRIPT_PATH_ROOT + "ExpDP_Oracle.Template.bat");
                    readText = readText.Replace("SET ORACLE_HOME=", "SET ORACLE_HOME=" + WebUtil.getOracleClient32Home());
                    string ORA_ADM_USR = Convert.ToString(dt.Rows[0]["ORA_ADM_USR"]);
                    readText=readText.Replace("SET ORA_ADM_USR=", "SET ORA_ADM_USR=" + ORA_ADM_USR);
                    string ORA_ADM_PWD = Convert.ToString(dt.Rows[0]["ORA_ADM_PWD"]);
                    readText=readText.Replace("SET ORA_ADM_PWD=", "SET ORA_ADM_PWD=" + ORA_ADM_PWD);
                    string ORA_ADM_EXP_USR = Convert.ToString(dt.Rows[0]["ORA_ADM_EXP_USR"]);
                    readText=readText.Replace("SET ORA_ADM_EXP_USR=", "SET ORA_ADM_EXP_USR=" + ORA_ADM_EXP_USR);
                    string ORA_ADM_EXP_PWD = Convert.ToString(dt.Rows[0]["ORA_ADM_EXP_PWD"]);
                    readText=readText.Replace("SET ORA_ADM_EXP_PWD=", "SET ORA_ADM_EXP_PWD=" + ORA_ADM_EXP_PWD);
                    string ORA_TARGET_DB_HOST = Convert.ToString(dt.Rows[0]["ORA_TARGET_DB_HOST"]);
                    readText=readText.Replace("SET ORA_TARGET_DB_HOST=", "SET ORA_TARGET_DB_HOST=" + ORA_TARGET_DB_HOST);
                    string ORA_TARGET_DB_PORT = Convert.ToString(dt.Rows[0]["ORA_TARGET_DB_PORT"]);
                    readText=readText.Replace("SET ORA_TARGET_DB_PORT=", "SET ORA_TARGET_DB_PORT=" + ORA_TARGET_DB_PORT);
                    string ORA_TARGET_DB_SID = Convert.ToString(dt.Rows[0]["ORA_TARGET_DB_SID"]);
                    readText=readText.Replace("SET ORA_TARGET_DB_SID=", "SET ORA_TARGET_DB_SID=" + ORA_TARGET_DB_SID);
                    string ORA_TARGET_USR = Convert.ToString(dt.Rows[0]["ORA_TARGET_USR"]).ToUpper();
                    readText=readText.Replace("SET ORA_TARGET_USR=", "SET ORA_TARGET_USR=" + ORA_TARGET_USR);
                    string ORA_TARGET_PWD = Convert.ToString(dt.Rows[0]["ORA_TARGET_PWD"]);
                    readText=readText.Replace("SET ORA_TARGET_PWD=", "SET ORA_TARGET_PWD=" + ORA_TARGET_PWD);
                    string ORA_VERSION = Convert.ToString(dt.Rows[0]["ORA_VERSION"]);
                    readText=readText.Replace("SET ORA_VERSION=", "SET ORA_VERSION=" + ORA_VERSION);
                    string DATAPUMP_DIR = Convert.ToString(dt.Rows[0]["DATAPUMP_DIR"]);
                    readText=readText.Replace("SET DATAPUMP_DIR=", "SET DATAPUMP_DIR=" + DATAPUMP_DIR);
                    string DATAPUMP = Convert.ToString(dt.Rows[0]["DATAPUMP"]);
                    readText=readText.Replace("SET DATAPUMP=", "SET DATAPUMP=" + DATAPUMP);
                    string ORA_OS_USR = Convert.ToString(dt.Rows[0]["ORA_OS_USR"]);
                    readText=readText.Replace("SET ORA_OS_USR=", "SET ORA_OS_USR=" + ORA_OS_USR);
                    string ORA_OS_PWD = Convert.ToString(dt.Rows[0]["ORA_OS_PWD"]);
                    readText=readText.Replace("SET ORA_OS_PWD=", "SET ORA_OS_PWD=" + ORA_OS_PWD);
                    string NLS_LANG = Convert.ToString(dt.Rows[0]["NLS_LANG"]);
                    readText=readText.Replace("SET NLS_LANG=", "SET NLS_LANG=" + NLS_LANG);
                    string BACKUP_PATH = Convert.ToString(dt.Rows[0]["BACKUP_PATH"]);
                    readText=readText.Replace("SET BACKUP_PATH=", "SET BACKUP_PATH=" + BACKUP_PATH);
                    cmdList.Add(readText);
                    string filename = createBatchScript(cmdList, batchfilename);
                    string fileBatch = "ExpDP_Oracle.DBPROFILE_ID." + Convert.ToString(dt.Rows[0]["DBPROFILE_ID"]);
                    string filenameBatchData = File.ReadAllText(filename);
                    fileBatchPath = BACKUP_SCRIPT_PATH_ROOT + fileBatch + ".bat";
                    sql = "update CMDBPROFILE set SCRIPT_PATH={2} where trim(DBPROFILE_ID)={0} and coop_id={1} and USED_FLAG=1";
                    sql = WebUtil.SQLFormat(sql, DBPROFILE_ID.Trim(), coop_id, fileBatch+".bat");
                    WebUtil.ExeSQL(sql);
                    File.WriteAllText(fileBatchPath, filenameBatchData);
                    WebUtil.updateOracleClient32SupportExpDP();
                    if (execute_flag)
                    {
                        System.Diagnostics.Process.Start(filename);
                        string output = "การดำเนินการโดย " + filename + " <br/>สามารถตรวจสอบได้ในหน้าจอนี้ โดยใช้ระยะเวลาสักครู่ (5-10 นาที)<br/>";
                        output += "EXPDP ไปที่ " + ORA_TARGET_DB_HOST + "\\" + DATAPUMP_DIR + "$\\" + DATAPUMP + "\\ <br/>";
                        output += "COPY EXPDP ที่ได้มาที่ " + System.Environment.GetEnvironmentVariable("TEMP") + "\\ <br/>";
                        output += "Backup EXPDP .dmp .log ทำเป็น Zip ที่ได้มาไว้ที่ " + BACKUP_SCRIPT_PATH_ROOT + "\\ <br/>";
                        output += "สำรอง Backup EXPDP .dmp .log ทำเป็น Zip ที่ได้มาไว้ที่ " + BACKUP_PATH + "\\ <br/>";
                        return output;
                    }
                    else
                    {
                        return filename;
                    }
                }
            }
            catch 
            {
                return null;
            }
            return null;
        }

        public static string createSQLScript(List<string> cmdList, string batchfilename)
        {
            try
            {

                String PROCESS_ID = batchfilename + "_" + DateTime.Now.Ticks + "";
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + ".sql";
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + ".sql");
                cmdList.ForEach(delegate(String cmd)
                {
                    w.WriteLine(cmd);
                });
                w.Close();
                return filename;
            }
            catch
            {
                return null;
            }
        }

        public static string createTempFile(List<string> cmdList, string batchfilename)
        {
            try
            {
                String suffix = "dat";
                String PROCESS_ID = batchfilename + "_" + DateTime.Now.Ticks + "";
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + "." + suffix;
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + "." + suffix);
                cmdList.ForEach(delegate(String cmd)
                {
                    w.WriteLine(cmd);
                });
                w.Close();
                return filename;
            }
            catch
            {
                return null;
            }
        }

        public static string createBatchScript(List<string> cmdList,string batchfilename)
        {
            try
            {

                String PROCESS_ID = batchfilename+"_"+DateTime.Now.Ticks + "";
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + ".bat";
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + ".bat");
                cmdList.ForEach(delegate(String cmd)
                {
                    w.WriteLine(cmd);
                });
                w.Close();
                return filename;
            }
            catch {
                return null;
            }
        }

        public static string RunCommand(string rootPath, string cmd)
        {
            try
            {
                String rootPathCoreReport = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + WebUtil.coreURI + "\\ICOOP\\PBReport125\\";
                String rootPathCoreProcess = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + WebUtil.coreURI + "\\ICOOP\\PBProcess\\";
                List<string> cmdList = new List<string>();
                cmdList.Add("SET PATH=%PATH%;" + rootPathCoreProcess);
                cmdList.Add("SET PATH=%PATH%;" + rootPathCoreReport);
                cmdList.Add(rootPath + cmd);
                string filename = createBatchScript(cmdList, "");
                /*
                String PROCESS_ID = DateTime.Now.Ticks + "";
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + ".bat";
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + ".bat");
                w.WriteLine(rootPath + cmd);
                w.Close();
                 */

                /*
                System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo(filename, "");
                info.UserName = "Administrator";
                String password = "Admin123";
                SecureString sPassword = new SecureString();
                char[] passwords = password.ToCharArray();
                for (int i = 0; i < password.Length; i++)
                {
                    sPassword.AppendChar(passwords[i]);
                }
                info.Password = sPassword;
                System.Diagnostics.Process.Start(info);
                 */
                //System.Diagnostics.Process.Start(filename);
                ExecuteCommandDOS(filename);
                return filename;
            }
            catch {
                return null;
            }
        }

        public static void ExecuteCommandDOS(string command)
        {
            int ExitCode;
            ProcessStartInfo ProcessInfo;
            Process Process;

            ProcessInfo = new ProcessStartInfo("cmd.exe", "/c " + command);
            ProcessInfo.CreateNoWindow = true;
            ProcessInfo.UseShellExecute = false;

            Process = Process.Start(ProcessInfo);
            Process.WaitForExit();

            ExitCode = Process.ExitCode;
            Process.Close();

            //MessageBox.Show("ExitCode: " + ExitCode.ToString(), "ExecuteCommand");
        }

        public static string RunCommand(List<string> cmdList, string batchfilename)
        {
            try
            {
                string filename = createBatchScript(cmdList, batchfilename);
                /*
                String PROCESS_ID = DateTime.Now.Ticks + "";
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + ".bat";
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + ".bat");
                w.WriteLine(rootPath + cmd);
                w.Close();
                 */

                /*
                System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo(filename, "");
                info.UserName = "Administrator";
                String password = "Admin123";
                SecureString sPassword = new SecureString();
                char[] passwords = password.ToCharArray();
                for (int i = 0; i < password.Length; i++)
                {
                    sPassword.AppendChar(passwords[i]);
                }
                info.Password = sPassword;
                System.Diagnostics.Process.Start(info);
                 */
                //System.Diagnostics.Process.Start(filename);
                ExecuteCommandDOS(filename);
                return filename;
            }
            catch
            {
                return null;
            }
        }

        public static string RunCommand(string rootPath, string cmd, String PROCESS_ID)
        {
            return RunCommand( rootPath,  cmd,  PROCESS_ID,"PRC");
        }

        public static string RunCommand(string rootPath, string cmd, String PROCESS_ID, string batchfilename)
        {
            try
            {
                String rootPathCoreReport = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + WebUtil.coreURI + "\\ICOOP\\PBReport125\\";
                String rootPathCoreProcess = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + WebUtil.coreURI + "\\ICOOP\\PBProcess\\";
                List<string> cmdList = new List<string>();
                cmdList.Add("SET PATH=%PATH%;" + rootPathCoreProcess);
                cmdList.Add(rootPath + cmd);
                string filename=RunCommand(cmdList, batchfilename+"_" + PROCESS_ID);
                /*
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + ".bat";
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + ".bat");
                w.WriteLine(rootPath + cmd);
                w.Close();
                 */
                /*
                System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo(filename, "");
                info.UserName = "Administrator";
                String password = "Admin123";
                SecureString sPassword = new SecureString();
                char[] passwords = password.ToCharArray();
                for (int i = 0; i < password.Length; i++)
                {
                    sPassword.AppendChar(passwords[i]);
                }
                info.Password = sPassword;
                System.Diagnostics.Process.Start(info);
                 */
                //process = System.Diagnostics.Process.Start(filename);
                //ThreadStart ths = new ThreadStart(delegate() { process = System.Diagnostics.Process.Start(filename); });

                return filename;
            }
            catch
            {
                return null;
            }
        }

        public static string RunKillcommand(string exe, String PROCESS_ID)
        {
            try
            {

                List<string> cmdList = new List<string>();
                cmdList.Add("wmic process where ( CommandLine like '%" + PROCESS_ID + "%' and name like '%" + exe + "%' )  call terminate ");
                string filename=RunCommand(cmdList, "Kill" + "_" + PROCESS_ID);
                /*
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + "_kill.bat";
                StreamWriter w = new StreamWriter(filename);
                w.WriteLine("wmic process where ( CommandLine like '%" + PROCESS_ID + "%' and name like '%" + exe + "%' )  call terminate ");
                w.Close();
                 */
                /*
                System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo(filename, "");
                info.UserName = "Administrator";
                String password = "Admin123";
                SecureString sPassword = new SecureString();
                char[] passwords = password.ToCharArray();
                for (int i = 0; i < password.Length; i++)
                {
                    sPassword.AppendChar(passwords[i]);
                }
                info.Password = sPassword;
                System.Diagnostics.Process.Start(info);
                 */
                //process = System.Diagnostics.Process.Start(filename);
                //ThreadStart ths = new ThreadStart(delegate() { process = System.Diagnostics.Process.Start(filename); });

                return filename;
            }
            catch
            {
                return null;
            }
        }

        public static string RunCommandReport(string rootPath, string cmd, String PROCESS_ID, String report_temppath, String report_path)
        {
            try
            {
                String rootPathCoreReport = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + WebUtil.coreURI + "\\ICOOP\\PBReport125\\";
                String rootPathCoreProcess = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + WebUtil.coreURI + "\\ICOOP\\PBProcess\\";
                List<string> cmdList = new List<string>();
                cmdList.Add("SET PATH=%PATH%;" + rootPathCoreProcess);
                cmdList.Add("SET PATH=%PATH%;" + rootPathCoreReport);
                cmdList.Add(rootPath + cmd);
                string filename = RunCommand(cmdList, "RPT" + "_" + PROCESS_ID);
                /*
                string tempath = System.Environment.GetEnvironmentVariable("TEMP");
                //string filename = rootPath + "\\" + PROCESS_ID + ".bat";
                string filename = tempath + "\\" + PROCESS_ID + ".bat";
                StreamWriter w = new StreamWriter(tempath + "\\" + PROCESS_ID + ".bat");
                w.WriteLine(rootPath + cmd);
                //w.WriteLine("copy " + report_temppath + " " + report_path);
                w.Close();
                 */
                //process = System.Diagnostics.Process.Start(filename);
                //ThreadStart ths = new ThreadStart(delegate() { process = System.Diagnostics.Process.Start(filename); });
                //กรณีจะให้สร้างรายงานได้ต้องไป Set ที่ IIS ดังนี้ 
                //1. IIS=>Authentication=>Anonymous Authentication -->Set to Application Pools Identity
                //2. ที่ Application Pools ที่ Run Web App ให้ Set Advance Settting => Identity = Administrator 

                return filename;
            }
            catch
            {
                return null;
            }
        }

        public static string ExecuteCmd(string Arguments, string user, string password, string domain)
        {
            return ExecuteCommand("cmd", Arguments, user, password, domain);
        }

        public static string ExecuteCommand(string command, string Arguments, string user, string password, string domain)
        {
            string output = null;

            Process p = new Process();

            ProcessStartInfo s = new ProcessStartInfo();
            if (domain != null || domain != "") s.Domain = domain;
            if (user != null || user != "") s.UserName = user;
            if (password != null || password != "")
            {
                s.Password = new SecureString();
                char[] passwords = password.ToCharArray();
                for (int i = 0; i < password.Length; i++)
                {
                    s.Password.AppendChar(passwords[i]);
                }
            }
            s.FileName = command;
            s.UseShellExecute = false;
            s.RedirectStandardOutput = true;
            s.RedirectStandardError = true;
            s.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
            if (Arguments != null && Arguments != "") s.Arguments = "/C \"" + Arguments + "\"";
            p.StartInfo = s;
            p.EnableRaisingEvents = true;
            try
            {
                p.Start();

                while (!p.HasExited)
                {
                    System.Threading.Thread.Sleep(1000);
                }

                //check to see what the exit code was
                if (p.ExitCode != 0)
                {
                    output = "Exitcode: " + p.ExitCode + "  - Err1: " + p.StandardError + " - Executor: " + System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
                }
                else
                {
                    output = "Command Result: " + p.StandardOutput.ReadToEnd();
                }
            }
            catch (Exception ex)
            { output += ex.Message; }

            return output;
        }

        public static int sendGMail(string fromAddress, string fromPassword, string[] toAddress, string subject, string body)
        {
            //กรณีผู้ส่งเป็น gmail ต้อง Set lesssecure = on ที่ Link https://www.google.com/settings/security/lesssecureapps
            return sendMail(fromAddress, fromPassword, toAddress, subject, body, "smtp.gmail.com", 587);
        }
        public static int sendMail(string fromAddress, string fromPassword, string[] toAddress, string subject, string body, string host, int port)
        {

            string your_id = fromAddress;
            string your_password = fromPassword;
            try
            {
                SmtpClient client = new SmtpClient
                {
                    Host = host,
                    Port = port,
                    EnableSsl = true,
                    DeliveryMethod = SmtpDeliveryMethod.Network,
                    Credentials = new System.Net.NetworkCredential(your_id, your_password),
                    Timeout = 10000,
                };
                MailMessage mm = new MailMessage();
                mm.From = new MailAddress(your_id);
                for (int i = 0; i < toAddress.Length; i++)
                {
                    mm.To.Add(toAddress[i]);
                }
                mm.BodyEncoding = UTF8Encoding.UTF8;
                mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
                mm.IsBodyHtml = true;
                //mm. = MailFormat.Html;
                mm.Subject = subject;
                mm.Body = body;
                client.Send(mm);
                Console.WriteLine("Email Sent");

                return 1;
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not end email\n\n" + e.ToString());
                return -1;
            }
        }

        public static void StartSMSBuilder(int connIndex)
        {
            try
            {
                string drive = WebUtil.GetGcoopPath().Substring(0, 1);
                string cmd = "java -jar " + drive + ":\\ICOOP_ALL\\ICORE\\ICOOP\\iReport\\ReportBuilderCORE\\dist\\ReportBuilderCORE.jar " + GetVirtualDirectory() + " 30 " + drive + ":\\ICOOP_ALL ICORE ICOOP SMS " + connIndex;
                //System.Diagnostics.Process.Start( WebUtil.GetGcoopPath().Substring(0, 1)+":\\ICOOP_ALL\\IEXT\\ICOOP\\run_ireport_builder.bat");
                WebUtil.RunCommand("", cmd);

            }
            catch
            {
            }
        }

        public static void StartIreportBuilder(bool killFlag)
        {
            bool status = false;
            try
            {
                Process[] process = System.Diagnostics.Process.GetProcessesByName("javaw");
                for (int i = 0; i < process.Length; i++)
                {
                    status = true;
                    if (killFlag)
                        process[i].Kill();
                }

                if (killFlag)
                    WebUtil.ExecuteCmd("taskkill /F /IM javaw.exe", "", "", "");
                if (status == false || killFlag)
                {
                    string drive = WebUtil.GetGcoopPath().Substring(0, 1);
                    string cmd = "start javaw.exe -jar " + drive + ":\\ICOOP_ALL\\ICORE\\ICOOP\\iReport\\ReportBuilderCORE\\dist\\ReportBuilderCORE.jar "+WebUtil.GetVirtualDirectory()+" 30 " + drive + ":\\ICOOP_ALL ICORE ICOOP";
                    //System.Diagnostics.Process.Start( WebUtil.GetGcoopPath().Substring(0, 1)+":\\ICOOP_ALL\\IEXT\\ICOOP\\run_ireport_builder.bat");
                    WebUtil.RunCommand("", cmd);
                }
            }
            catch
            {
            }
            /*
            string args = "";
            string str2 = "C:\\GCOOP_ALL\\FSCT\\0.ResetIreportBuilder.bat";

            ProcessStartInfo startinfo = new ProcessStartInfo(str2, args);
            startinfo.CreateNoWindow = true;
            startinfo.UseShellExecute = false;
            startinfo.WorkingDirectory = "C:\\GCOOP_ALL\\FSCT";
            // *** Redirect the output ***
            startinfo.RedirectStandardError = true;
            startinfo.RedirectStandardOutput = true;

            Process process;
            process = Process.Start(startinfo);
            process.WaitForExit();
            //string stdinput = process.StandardOutput.ReadToEnd();
            //string stdoutput = process.StandardError.ReadToEnd();
            int ecode = process.ExitCode;
            LbServerMessage.ForeColor = Color.Green;
            //LbServerMessage.Text = (string)ecode+"";
            //+": <br/>" + stdoutput;
             */
        }

        public String uploadFTP(String ftphost, String ftpUser, String ftpPwd, String filePath, String filename)
        {
            String status = "";
            // Get the object used to communicate with the server.
            Uri ftp = new Uri(ftphost);
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create(ftp + "/" + filename);
            request.Method = WebRequestMethods.Ftp.UploadFile;

            // This example assumes the FTP site uses anonymous logon.
            request.Credentials = new NetworkCredential(ftpUser, ftpPwd);

            // Copy the contents of the file to the request stream.
            StreamReader sourceStream = new StreamReader(filePath);
            byte[] fileContents = Encoding.ASCII.GetBytes(sourceStream.ReadToEnd());
            sourceStream.Close();
            request.ContentLength = fileContents.Length;

            Stream requestStream = request.GetRequestStream();
            requestStream.Write(fileContents, 0, fileContents.Length);
            requestStream.Close();

            FtpWebResponse response = (FtpWebResponse)request.GetResponse();

            status = response.StatusDescription;

            //logMessage.WriteLog("", response.StatusDescription);

            response.Close();

            return status;
        }

        public static bool urlExists(string url)
        {
            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = WebRequestMethods.Http.Head;
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                bool pageExists = response.StatusCode == HttpStatusCode.OK;
                return pageExists;
            }
            catch
            {
                return false;
            }
        }

        public static void remoteExecute()
        {
            try
            {
                string AppPath = "\\\\spri11U1118\\SampleBatch\\Bin\\";
                string strFilePath = AppPath + "ABCED120D_XXX.bat";
                System.Diagnostics.Process proc = new System.Diagnostics.Process();

                proc.StartInfo.FileName = strFilePath;
                string pwd = "s44erver";

                proc.StartInfo.Domain = "abcd";
                proc.StartInfo.UserName = "sysfaomyulm";

                System.Security.SecureString secret = new System.Security.SecureString();
                foreach (char c in pwd)

                    secret.AppendChar(c);

                proc.StartInfo.Password = secret;
                proc.StartInfo.UseShellExecute = false;

                proc.StartInfo.WorkingDirectory = "psexec \\\\spri11U1118\\SampleBatch\\Bin ";

                proc.Start();
                while (!proc.HasExited)
                {
                    proc.Refresh();
                    // Thread.Sleep(1000);
                }

                proc.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static string createConnectionString(string host,string port,string sid,string user,string pwd)
        {
            return "Data Source=" + host + ":" + port + "/" + sid + ";Persist Security Info=True;User ID=" + user + ";Password=" + pwd + ";Unicode=True";
        }

        public static int compareOracleClientVOracleDB(string host, string port, string sid, string user, string pwd)
        {
            string connectionString = WebUtil.createConnectionString( host,  port,  sid,  user,  pwd);
            return compareOracleClientVOracleDB(connectionString);
        }

        public static int compareOracleClientVOracleDB(string connectionString)
        {
            string currentOracleDb = getOracleDatabaseVersion(connectionString);
            string currentOracleClient = getOracleClient32Version();
            return (currentOracleClient.CompareTo(currentOracleDb));
        }

        public static string getOracleDatabaseVersion(string connectionString)
        {
            //string connectionString = TbConnectionString.Text;
            string version = "0";
            Sta ta;
            try
            {
                ta = new Sta(connectionString);
                string sql = "SELECT version FROM V$INSTANCE";
                DataTable dt = ta.QueryDataTable(sql);
                if (dt.Rows.Count > 0)
                {
                    version=Convert.ToString(dt.Rows[0]["version"]);
                }
                ta.Close();
            }
            catch
            {
               
            }
            return version;
            ;
        }

        [DllImport("kernel32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
        static extern uint GetFileAttributes(string lpFileName);

        public static bool DirectoryExists(string path)
        {
            uint attributes = GetFileAttributes(path.StartsWith(@"\\?\") ? path : @"\\?\" + path);
            if (attributes != 0xFFFFFFFF)
            {
                return ((FileAttributes)attributes).HasFlag(FileAttributes.Directory);
            }
            else
            {
                return false;
            }
        }

        public static bool FileExists(string path)
        {
            uint attributes = GetFileAttributes(path.StartsWith(@"\\?\") ? path : @"\\?\" + path);
            if (attributes != 0xFFFFFFFF)
            {
                return !((FileAttributes)attributes).HasFlag(FileAttributes.Directory);
            }
            else
            {
                return false;
            }
        }

        public static bool checkOracleClient32SupportExpDP()
        {
            bool status = false;

            try
            {
                string sqlnetfile = getOracleClient32Home() + "\\bin\\expdp.exe";
                status = FileExists(sqlnetfile);
            }
            catch {
            }

            return status;
        }

        public static void updateOracleClient32SupportExpDP()
        {
            try
            {
                string sqlnetfile = getOracleClient32Home() + "\\network\\admin\\sqlnet.ora";
                string sqlnetfile_data = File.ReadAllText(sqlnetfile).Replace("NTS", "NONE");
                File.WriteAllText(sqlnetfile, sqlnetfile_data);
            }
            catch { }
        }

        public static string getOracleClient32Home()
        {
            string finder_v = "<SAVED_WITH>";
            string oracle_client_version = getOracleClient32Version();
            oracle_client_version = oracle_client_version.Substring(0, oracle_client_version.IndexOf("."));
            string xml = System.Environment.GetEnvironmentVariable("ProgramFiles(x86)")+"\\Oracle\\Inventory\\ContentsXML\\inventory.xml";
            string[] xmlData_=File.ReadAllLines(xml);
            string xmlData = "";
            for (int i = 0; i < xmlData_.Length;i++ )
            {
                if (xmlData_[i].IndexOf(oracle_client_version) >= 0 && xmlData_[i].IndexOf(finder_v) < 0)
                {
                xmlData = xmlData_[i];
                string finder = "LOC=\"";
                int pos = xmlData.IndexOf(finder);
                int len = xmlData.Length;
                xmlData = xmlData.Substring(pos, len - pos).Replace(finder, "");
                finder = "\"";
                pos = xmlData.IndexOf(finder);
                xmlData = xmlData.Substring(0, pos).Replace(finder, "");
                }
            }
            return xmlData;
        }

        public static string getOracleClient32Version()
        {
            string xml = System.Environment.GetEnvironmentVariable("ProgramFiles(x86)") + "\\Oracle\\Inventory\\ContentsXML\\inventory.xml";
            string xmlData = File.ReadAllText(xml);
            string finder = "<SAVED_WITH>";
            int pos = xmlData.IndexOf(finder);
            int len = xmlData.Length;
            xmlData = xmlData.Substring(pos, len - pos).Replace(finder, "");
            finder = "</SAVED_WITH>";
            pos = xmlData.IndexOf(finder);
            xmlData = xmlData.Substring(0, pos).Replace(finder, "");
            return xmlData;
        }

        public static WindowsPrincipal LogonUser(string userName, string password)
        {
            return LogonUser("localhost", userName, WebUtil.GetMachineNetBiosDomain(), password);
        }

        public static WindowsPrincipal LogonUser(string host,string userName, string domain, string password)
        {
            // need a full duplex stream - loopback is easiest way to get that
            TcpListener tcpListener = new TcpListener(IPAddress.Loopback, 0);
            tcpListener.Start();

            WindowsIdentity id = null;
            tcpListener.BeginAcceptTcpClient(delegate(IAsyncResult asyncResult)
            {
                using (NegotiateStream serverSide = new NegotiateStream(
                         tcpListener.EndAcceptTcpClient(asyncResult).GetStream()))
                {
                    serverSide.AuthenticateAsServer(CredentialCache.DefaultNetworkCredentials,
                         ProtectionLevel.None, TokenImpersonationLevel.Impersonation);
                    id = (WindowsIdentity)serverSide.RemoteIdentity;
                }
            }, null);

            using (NegotiateStream clientSide = new NegotiateStream(new TcpClient(host,
                         ((IPEndPoint)tcpListener.LocalEndpoint).Port).GetStream()))
            {
                clientSide.AuthenticateAsClient(new NetworkCredential(userName, password, domain),
                         "", ProtectionLevel.None, TokenImpersonationLevel.Impersonation);
            }
            return new WindowsPrincipal(id);
        }

        [DllImport("netapi32.dll", CharSet = CharSet.Auto)]
        static extern int NetWkstaGetInfo(string server, int level, out IntPtr info);

        [DllImport("netapi32.dll")]
        static extern int NetApiBufferFree(IntPtr pBuf);

        [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
        class WKSTA_INFO_100
        {
            public int wki100_platform_id;
            [MarshalAs(UnmanagedType.LPWStr)]
            public string wki100_computername;
            [MarshalAs(UnmanagedType.LPWStr)]
            public string wki100_langroup;
            public int wki100_ver_major;
            public int wki100_ver_minor;
        }

        public static string GetMachineNetBiosDomain()
        {
            IntPtr pBuffer = IntPtr.Zero;

            WKSTA_INFO_100 info;
            int retval = NetWkstaGetInfo(null, 100, out pBuffer);
            if (retval != 0)
                throw new Win32Exception(retval);

            info = (WKSTA_INFO_100)Marshal.PtrToStructure(pBuffer, typeof(WKSTA_INFO_100));
            string domainName = info.wki100_langroup;
            NetApiBufferFree(pBuffer);
            return domainName;
        }

        public static string IntToStringWithLeftPad(int number, int totalWidth)
        {
            return number.ToString().PadLeft(totalWidth, '0');
        }

        public static ArrayList GetInstalledPrinters(bool createPrinterListsFlag)
        {
            ArrayList printerLists = new ArrayList();
            String PrinterListsFile = System.Environment.GetEnvironmentVariable("TEMP") + "\\PrinterLists.txt";
            File.Delete(PrinterListsFile);
            String script="powershell.exe -ExecutionPolicy Bypass -Command \"get-WmiObject -class Win32_printer|ft name\">>" + PrinterListsFile + "";
            //WebUtil.ExecuteCmd(script, "", "", "");
            List<string> cmdList = new List<string>();
            cmdList.Add(script);
            String batchfilename = "PrinterLists";
            String filename = createBatchScript(cmdList, batchfilename);
            System.Diagnostics.Process.Start(filename);
            Thread.Sleep(5000);
            string[] readText = File.ReadAllLines(PrinterListsFile);
            String sql = "";
            if (createPrinterListsFlag)
            {
                int cnt = 0;
                try
                {
                    sql = @"delete from cmprinter"; 
                    WebUtil.Query(sql);
//                    sql = @"insert into cmprinter ( PRINTER_ID,COOP_CONTROL,COOP_ID,PRINTER_NAME,PRINTER_IP,default_flag  )values(
//                              " + ((++cnt)).ToString("D4") + @",
//                              (select COOP_CONTROL from cmcoopmaster where "+(Sta.IS_MYSQL_MODE?" limit 0,1 ":" and ROWNUM = 1 ")+@"),
//                              (select COOP_CONTROL from cmcoopmaster where "+(Sta.IS_MYSQL_MODE?" limit 0,1 ":" and ROWNUM = 1 ")+@"),
//                              'PDF','127.0.0.1','1')";

                    sql = @"insert into cmprinter ( PRINTER_ID,COOP_CONTROL,COOP_ID,PRINTER_NAME,PRINTER_IP,default_flag  )values(
                               " + ((++cnt)).ToString("D4") + @",
                              (select COOP_CONTROL from cmcoopmaster  " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " where ROWNUM = 1 ") + @"),
                              (select COOP_CONTROL from cmcoopmaster  " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " where ROWNUM = 1 ") + @"),
                              'PDF','127.0.0.1','1')";
                    WebUtil.Query(sql);
                    printerLists.Add("PDF");
                }
                catch { }
                try
                {
                    sql = @"insert into cmprinter ( PRINTER_ID,COOP_CONTROL,COOP_ID,PRINTER_NAME,PRINTER_IP,default_flag )values(
                              " + ((++cnt)).ToString("D4") + @",
                             (select COOP_CONTROL from cmcoopmaster  " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " where ROWNUM = 1 ") + @" ),
                             (select COOP_CONTROL from cmcoopmaster  " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " where ROWNUM = 1 ") + @" ), 'XLS','127.0.0.1','0')";
                    WebUtil.Query(sql);
                    printerLists.Add("XLS");
                }
                catch { }

                String[] printerNameIgnores = { "Sybase","Snagit","Send To","Microsoft","Fax","Bullzip"};

                for (int i = 3; i < readText.Length; i++)
                {
                    bool passCheck = true;
                    for (int x = 0; x < printerNameIgnores.Length && passCheck; x++) { passCheck = readText[i].Trim().IndexOf(printerNameIgnores[x]) < 0; }
                    if (passCheck&&readText[i].Trim().Length>0)
                    {
                        try
                        {
                            sql = @"insert into cmprinter ( PRINTER_ID,COOP_CONTROL,COOP_ID,PRINTER_NAME,PRINTER_IP )values(
                              " + ((++cnt)).ToString("D4") + @",
                              (select COOP_CONTROL from cmcoopmaster " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " where ROWNUM = 1 ") + @") ,
                              (select COOP_CONTROL from cmcoopmaster " + (Sta.IS_MYSQL_MODE ? " limit 0,1 " : " where ROWNUM = 1 ") + @") ,'" + readText[i].Trim() + @"','127.0.0.1')";
                            WebUtil.Query(sql);
                            printerLists.Add(readText[i]);
                        }
                        catch { }
                    }
                }
            }
            return printerLists;
        }


        // This constant is used to determine the keysize of the encryption algorithm in bits.
        // We divide this by 8 within the code below to get the equivalent number of bytes.
        private const int Keysize = 256;

        // This constant determines the number of iterations for the password bytes generation function.
        private const int DerivationIterations = 1000;

        public static string Encrypt(string plainText, string passPhrase)
        {
            // Salt and IV is randomly generated each time, but is preprended to encrypted cipher text
            // so that the same Salt and IV values can be used when decrypting.  
            var saltStringBytes = Generate256BitsOfRandomEntropy();
            var ivStringBytes = Generate256BitsOfRandomEntropy();
            var plainTextBytes = Encoding.UTF8.GetBytes(plainText);
            using (var password = new Rfc2898DeriveBytes(passPhrase, saltStringBytes, DerivationIterations))
            {
                var keyBytes = password.GetBytes(Keysize / 8);
                using (var symmetricKey = new RijndaelManaged())
                {
                    symmetricKey.BlockSize = 256;
                    symmetricKey.Mode = CipherMode.CBC;
                    symmetricKey.Padding = PaddingMode.PKCS7;
                    using (var encryptor = symmetricKey.CreateEncryptor(keyBytes, ivStringBytes))
                    {
                        using (var memoryStream = new MemoryStream())
                        {
                            using (var cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write))
                            {
                                cryptoStream.Write(plainTextBytes, 0, plainTextBytes.Length);
                                cryptoStream.FlushFinalBlock();
                                // Create the final bytes as a concatenation of the random salt bytes, the random iv bytes and the cipher bytes.
                                var cipherTextBytes = saltStringBytes;
                                cipherTextBytes = cipherTextBytes.Concat(ivStringBytes).ToArray();
                                cipherTextBytes = cipherTextBytes.Concat(memoryStream.ToArray()).ToArray();
                                memoryStream.Close();
                                cryptoStream.Close();
                                return Convert.ToBase64String(cipherTextBytes);
                            }
                        }
                    }
                }
            }
        }

        public static string Decrypt(string cipherText, string passPhrase)
        {
            // Get the complete stream of bytes that represent:
            // [32 bytes of Salt] + [32 bytes of IV] + [n bytes of CipherText]
            var cipherTextBytesWithSaltAndIv = Convert.FromBase64String(cipherText);
            // Get the saltbytes by extracting the first 32 bytes from the supplied cipherText bytes.
            var saltStringBytes = cipherTextBytesWithSaltAndIv.Take(Keysize / 8).ToArray();
            // Get the IV bytes by extracting the next 32 bytes from the supplied cipherText bytes.
            var ivStringBytes = cipherTextBytesWithSaltAndIv.Skip(Keysize / 8).Take(Keysize / 8).ToArray();
            // Get the actual cipher text bytes by removing the first 64 bytes from the cipherText string.
            var cipherTextBytes = cipherTextBytesWithSaltAndIv.Skip((Keysize / 8) * 2).Take(cipherTextBytesWithSaltAndIv.Length - ((Keysize / 8) * 2)).ToArray();

            using (var password = new Rfc2898DeriveBytes(passPhrase, saltStringBytes, DerivationIterations))
            {
                var keyBytes = password.GetBytes(Keysize / 8);
                using (var symmetricKey = new RijndaelManaged())
                {
                    symmetricKey.BlockSize = 256;
                    symmetricKey.Mode = CipherMode.CBC;
                    symmetricKey.Padding = PaddingMode.PKCS7;
                    using (var decryptor = symmetricKey.CreateDecryptor(keyBytes, ivStringBytes))
                    {
                        using (var memoryStream = new MemoryStream(cipherTextBytes))
                        {
                            using (var cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read))
                            {
                                var plainTextBytes = new byte[cipherTextBytes.Length];
                                var decryptedByteCount = cryptoStream.Read(plainTextBytes, 0, plainTextBytes.Length);
                                memoryStream.Close();
                                cryptoStream.Close();
                                return Encoding.UTF8.GetString(plainTextBytes, 0, decryptedByteCount);
                            }
                        }
                    }
                }
            }
        }

        private static byte[] Generate256BitsOfRandomEntropy()
        {
            var randomBytes = new byte[32]; // 32 Bytes will give us 256 bits.
            using (var rngCsp = new RNGCryptoServiceProvider())
            {
                // Fill the array with cryptographically secure random bytes.
                rngCsp.GetBytes(randomBytes);
            }
            return randomBytes;
        }

        /*
        public static IEnumerable<IList<T>> SplitBy<T>(this IEnumerable<T> source,
                                               Func<T, bool> startPredicate,
                                               Func<T, bool> endPredicate,
                                               bool includeDelimiter)
        {
            var l = new List<T>();
            foreach (var s in source)
            {
                if (startPredicate(s))
                {
                    if (l.Any())
                    {
                        l = new List<T>();
                    }
                    l.Add(s);
                }
                else if (l.Any())
                {
                    l.Add(s);
                }

                if (endPredicate(s))
                {
                    if (includeDelimiter)
                        yield return l;
                    else
                        yield return l.GetRange(1, l.Count - 2);

                    l = new List<T>();
                }
            }
        }
        */
        /*
        public static List<string> ExtractString(string source, string start, string end)
        {
            var results = new List<string>();

            string pattern = string.Format(
                "{0}{1}{2}",
                Regex.Escape(start),
                ".+?",
                 Regex.Escape(end));

            foreach (var m in Regex.Matches(source, pattern))
            {
                results.Add(m.Groups[1].Value);
            }

            return results;
        }
        */
        public static List<string> ExtractFromString(
            string text, string startString, string endString)
        {
            List<string> matched = new List<string>();
            int indexStart = 0, indexEnd = 0;
            String tmp = "";
            bool exit = false;
            while (!exit)
            {
                indexStart = text.IndexOf(startString);
                if (indexStart != -1)
                {
                    text = text.Substring(indexStart);
                    indexStart = 0;
                    indexEnd = text.IndexOf(endString);
                    if (indexStart != -1 && indexEnd != -1)
                    {
                        tmp = text.Substring(indexStart + startString.Length,
                            indexEnd - indexStart - startString.Length);
                        matched.Add(tmp);
                        text = text.Substring(indexEnd + endString.Length);
                    }
                    else
                        exit = true;
                }
                else
                    exit = true;
            }
            return matched;
        }


        public static List<string> getStringBetween(String filepath, String findStrS, String findStrE)
        {
            List<string> output = new List<string>();
            //filepath = WebUtil.GetGcoopRootDir() + filepath.Replace("/","\\");
            String fileData = File.ReadAllText(filepath);
            //String findStrS = "Gcoop.Open";
            //String findStrE = ");";
            output=WebUtil.ExtractFromString(fileData,findStrS,findStrE);

            /*
            String[] outp=fileData.Split(new[] { findStrS, findStrE }, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < outp.Length;i++ )
            {
                output.Add(outp[i]);
            }
            */
            return output;
        }

        public static List<string> getStringBetweenLine(String filepath, String findStrS, bool show_line_flag = true)
        {
            List<string> output = new List<string>();
            //filepath = WebUtil.GetGcoopRootDir() + filepath.Replace("/","\\");
            String[] fileData = File.ReadAllLines(filepath);
            //String findStrS = "Gcoop.Open";
            //String findStrE = ");";
            //output = WebUtil.ExtractFromString(fileData, findStrS, findStrE);

            for (int i = 0; i < fileData.Length;i++ )
            {
                if (fileData[i].IndexOf(findStrS) != -1)
                {
                    output.Add(fileData[i].Trim().Replace("\t", "") + (show_line_flag ? ("[Ln=" + (i + 1) + "]") : ""));
                }
            }
            return output;
        }
        public static List<string> getStringBetweenLine(String filepath, String[] findStrF, ref List<string> findStrFname, String[] findStrS,bool show_line_flag =true)
        {
            List<string> output = new List<string>();
                         findStrFname = new List<string>(); 
            //filepath = WebUtil.GetGcoopRootDir() + filepath.Replace("/","\\");
            String[] fileData = File.ReadAllLines(filepath);
            //String findStrS = "Gcoop.Open";
            //String findStrE = ");";
            //output = WebUtil.ExtractFromString(fileData, findStrS, findStrE);
            String temp = "";
            for (int i = 0; i < fileData.Length; i++)
            {
                for (int j = 0; j < findStrF.Length; j++)
                {
                    if (fileData[i].IndexOf(findStrF[j]) != -1)
                    {
                        temp = fileData[i].Trim().Replace("\t", "")+(show_line_flag?("[Ln="+(i+1)+"]"):"");
                    }
                }
                for (int j = 0; j < findStrS.Length; j++)
                {
                    if (fileData[i].IndexOf(findStrS[j]) != -1)
                    {
                        output.Add(fileData[i].Trim().Replace("\t", "") + (show_line_flag ? ("[Ln=" + (i + 1) + "]") : ""));
                        findStrFname.Add(temp);
                    }
                }
            }
            return output;
        }

        public static String getURIExtendRoot()
        {

            XmlConfigService xml = new XmlConfigService(WebUtil.GetGcoopPath());
            String URI_Extend_Root = "/" + xml.SavPathOpenType1 + "/" + xml.SavPathPattern;

            return URI_Extend_Root;
        }


        public static String getFilepathExtendRoot()
        {

            XmlConfigService xml = new XmlConfigService(WebUtil.GetGcoopPath());
            String path_Extend_Root = WebUtil.GetGcoopPath().Substring(0, 1) + ":\\ICOOP_ALL\\" + xml.SavPathOpenType1 + "/" + xml.SavPathPattern;
            path_Extend_Root = path_Extend_Root.Replace("/", "\\");
            return path_Extend_Root;
        }

        public static void SetCurrentFileContentDownload(string filepath)
        {
            HttpContext.Current.Session["filepath"] = filepath;
        }

        public static String GenerateFileContentDownload()
        {
            String output = "<iframe frameborder=0 style=\"display:none;\" src=\"" + WebUtil.GetSavingUrlCore() + "/GetFile.aspx" + "\" ></iframe>";
            if (HttpContext.Current.Session["filepath"] == null || File.Exists(HttpContext.Current.Session["filepath"].ToString()) == false)
            {
                output = "";
            }
            return output;
        }

        public static void GetResponseGetFileDownload(string filepath)
        {

            string filename = "", contentType = "";
            //string filepath = HttpContext.Current.Request["filepath"];
            WebUtil.GetContentType(filepath, ref filename, ref contentType);

            var client = new WebClient();
            var buffer = client.DownloadData(filepath);
            HttpContext.Current.Response.ContentType = contentType;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + "");
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.OutputStream.Write(buffer, 0, buffer.Length);
            HttpContext.Current.Response.Flush();
        }

        public static void GetContentType(string filepath, ref string filename, ref string contentType)
        {
            // set a default mimetype if not found.
            contentType = "application/octet-stream";

            try
            {
                // get the registry classes root
                RegistryKey classes = Registry.ClassesRoot;

                // find the sub key based on the file extension
                RegistryKey fileClass = classes.OpenSubKey(Path.GetExtension(filepath));
                filename = Path.GetFileName(filepath);
                contentType = fileClass.GetValue("Content Type").ToString();
            }
            catch { }

        }


        //Start CMTXTFORMAT
        //TABLE CMTXTFORMATUCFTYPE,CMTXTFORMATMAS,CMTXTFORMATHEADER,CMTXTFORMATDETAIL,CMTXTFORMATFOOTER

        public static void txtFormatRegisterDB()
        {
            ArrayList sql = new ArrayList();

            sql.Add("CREATE TABLE CMTXTFORMATUCFTYPE (TXTFORMATTYPE_CODE VARCHAR2(20) NOT NULL, TXTFORMATTYPE_DESC VARCHAR2(50) NOT NULL,TXTFILED_VALUE VARCHAR2(2) , TXTREPLACE_VALUE VARCHAR2(2),TXTFORMAT  VARCHAR2(50) ) ");
            sql.Add("ALTER TABLE CMTXTFORMATUCFTYPE ADD ( CONSTRAINT CMTXTFORMATUCFTYPE_PK PRIMARY KEY ( TXTFORMATTYPE_CODE )) ");

            sql.Add("INSERT INTO CMTXTFORMATUCFTYPE (TXTFORMATTYPE_CODE , TXTFORMATTYPE_DESC,TXTFILED_VALUE ,TXTREPLACE_VALUE ,TXTFORMAT )VALUES('T','TEXT with Fill Space ',' ',' ','') ");
            sql.Add("INSERT INTO CMTXTFORMATUCFTYPE (TXTFORMATTYPE_CODE , TXTFORMATTYPE_DESC,TXTFILED_VALUE ,TXTREPLACE_VALUE ,TXTFORMAT )VALUES('N','Decimal with no dot  ','.','','0.00') ");
            sql.Add("INSERT INTO CMTXTFORMATUCFTYPE (TXTFORMATTYPE_CODE , TXTFORMATTYPE_DESC,TXTFILED_VALUE ,TXTREPLACE_VALUE ,TXTFORMAT )VALUES('D_YYYYMMDD','DATE with no dot  ','','','YYYYMMDD') ");
            sql.Add("INSERT INTO CMTXTFORMATUCFTYPE (TXTFORMATTYPE_CODE , TXTFORMATTYPE_DESC,TXTFILED_VALUE ,TXTREPLACE_VALUE ,TXTFORMAT )VALUES('D_YYMMDD','DATE with no dot  ','','','YYMMDD') ");
            sql.Add("INSERT INTO CMTXTFORMATUCFTYPE (TXTFORMATTYPE_CODE , TXTFORMATTYPE_DESC,TXTFILED_VALUE ,TXTREPLACE_VALUE ,TXTFORMAT )VALUES('D_HHMMSS','DATE with no dot  ','','','HHmmss') ");

            sql.Add("CREATE TABLE CMTXTFORMATMAS (TXTFORMAT_CODE VARCHAR2(20) NOT NULL, TXTFORMAT_DESC VARCHAR2(50) NOT NULL,TXTFORMAT_FILENAME VARCHAR2(50) ) ");
            sql.Add("ALTER TABLE CMTXTFORMATMAS ADD ( CONSTRAINT CMTXTFORMATMAS_PK PRIMARY KEY ( TXTFORMAT_CODE )) ");

            sql.Add("CREATE TABLE CMTXTFORMATHEADER (TXTFORMAT_CODE VARCHAR2(20) NOT NULL, TXTFORMATHD_NM VARCHAR2(150) NOT NULL,SEQ_NO NUMBER(2,0) NOT NULL,TXTFORMATTYPE_CODE VARCHAR2(20) NOT NULL,TXTFORMAT_DESC VARCHAR2(150) NOT NULL,TXTLENG NUMBER(3,0) NOT NULL ,FILLVALUE_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL ,FILLVALUE_DATA VARCHAR2(5) ,FORMATE_EXAMPLE VARCHAR2(150),TXTFILED_VALUE VARCHAR2(1) , TXTDEFAULT_VALUE VARCHAR2(150) ,FORMATE_VALUE VARCHAR2(1000),TXTSTARTPOS NUMBER(3,0) default 0 NOT NULL ) ");
            sql.Add("ALTER TABLE CMTXTFORMATHEADER ADD ( CONSTRAINT CMTXTFORMATHEADER_PK PRIMARY KEY ( TXTFORMAT_CODE,TXTFORMATHD_NM,SEQ_NO )) ");

            sql.Add("CREATE TABLE CMTXTFORMATDETAIL (TXTFORMAT_CODE VARCHAR2(20) NOT NULL, TXTFORMATDT_NM VARCHAR2(150) NOT NULL,SEQ_NO NUMBER(2,0) NOT NULL,TXTFORMATTYPE_CODE VARCHAR2(20) NOT NULL,TXTFORMAT_DESC VARCHAR2(150) NOT NULL,TXTLENG NUMBER(3,0) NOT NULL ,FILLVALUE_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL ,FILLVALUE_DATA VARCHAR2(5) ,FORMATE_EXAMPLE VARCHAR2(150),TXTFILED_VALUE VARCHAR2(1) , TXTDEFAULT_VALUE VARCHAR2(150) ,FORMATE_VALUE VARCHAR2(1000),TXTSTARTPOS NUMBER(3,0) default 0 NOT NULL ) ");
            sql.Add("ALTER TABLE CMTXTFORMATDETAIL ADD ( CONSTRAINT CMTXTFORMATDETAIL_PK PRIMARY KEY ( TXTFORMAT_CODE,TXTFORMATDT_NM,SEQ_NO )) ");

            sql.Add("CREATE TABLE CMTXTFORMATFOOTER (TXTFORMAT_CODE VARCHAR2(20) NOT NULL, TXTFORMATFT_NM VARCHAR2(150) NOT NULL,SEQ_NO NUMBER(2,0) NOT NULL,TXTFORMATTYPE_CODE VARCHAR2(20) NOT NULL,TXTFORMAT_DESC VARCHAR2(150) NOT NULL,TXTLENG NUMBER(3,0) NOT NULL ,FILLVALUE_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL ,FILLVALUE_DATA VARCHAR2(5) ,FORMATE_EXAMPLE VARCHAR2(150),TXTFILED_VALUE VARCHAR2(1) , TXTDEFAULT_VALUE VARCHAR2(150) ,FORMATE_VALUE VARCHAR2(1000),TXTSTARTPOS NUMBER(3,0) default 0 NOT NULL ) ");
            sql.Add("ALTER TABLE CMTXTFORMATFOOTER ADD ( CONSTRAINT CMTXTFORMATFOOTER_PK PRIMARY KEY ( TXTFORMAT_CODE,TXTFORMATFT_NM,SEQ_NO )) ");

            for (int i = 0; i < sql.Count; i++)
            {
                try
                {
                    WebUtil.ExeSQL(sql[i].ToString());
                }
                catch { }
            }
        }



        public static void txtFormatRegisterATMBAYImportFile()
        {

            ArrayList sql = new ArrayList();
            String TXTFORMAT_CODE = "ATM_BAY_IMP";
            String sqlT = "";
            sqlT = "INSERT INTO CMTXTFORMATMAS (TXTFORMAT_CODE , TXTFORMAT_DESC ,TXTFORMAT_FILENAME ) ";
            sqlT += "VALUES ('" + TXTFORMAT_CODE + "' , 'ATM BAY IMPORT TRANSACTION' ,'YYMMDD_COOPIDXXX_CODE.txt' )";
            sql.Add(sqlT);

            sqlT = "INSERT INTO CMTXTFORMATHEADER (TXTFORMAT_CODE , TXTFORMATHD_NM ,SEQ_NO ,TXTFORMATTYPE_CODE ,TXTFORMAT_DESC ,TXTLENG ,FILLVALUE_FLAG ,FILLVALUE_DATA, FORMATE_EXAMPLE ,TXTFILED_VALUE, TXTDEFAULT_VALUE,FORMATE_VALUE   ) ";
            sqlT += "VALUES ('" + TXTFORMAT_CODE + "'  , 'DCSDCOT-RECORD-FORMAT' ,1 ,'T' ,'Record format \"0\" = Header Record' ,1 , 1 ,'1', '1' ,'1', '1',''   ) ";
            sql.Add(sqlT);
            sqlT = "INSERT INTO CMTXTFORMATHEADER (TXTFORMAT_CODE , TXTFORMATHD_NM ,SEQ_NO ,TXTFORMATTYPE_CODE ,TXTFORMAT_DESC ,TXTLENG ,FILLVALUE_FLAG ,FILLVALUE_DATA, FORMATE_EXAMPLE ,TXTFILED_VALUE, TXTDEFAULT_VALUE,FORMATE_VALUE   ) ";
            sqlT += "VALUES ('" + TXTFORMAT_CODE + "'  , 'DCSDCOT-DATA' ,2 ,'T' ,'DATA' ,1 , 1 ,'1', '1' ,'1', '1',''   ) ";
            sql.Add(sqlT);

            sqlT = "INSERT INTO CMTXTFORMATDETAIL (TXTFORMAT_CODE , TXTFORMATDT_NM ,SEQ_NO ,TXTFORMATTYPE_CODE ,TXTFORMAT_DESC ,TXTLENG ,FILLVALUE_FLAG ,FILLVALUE_DATA, FORMATE_EXAMPLE,TXTFILED_VALUE  , TXTDEFAULT_VALUE  ,FORMATE_VALUE  ) ";
            sqlT += "VALUES ('" + TXTFORMAT_CODE + "'  , TXTFORMATDT_NM ,SEQ_NO ,TXTFORMATTYPE_CODE ,TXTFORMAT_DESC ,TXTLENG ,FILLVALUE_FLAG ,FILLVALUE_DATA, FORMATE_EXAMPLE,TXTFILED_VALUE  , TXTDEFAULT_VALUE  ,FORMATE_VALUE  )  ";
            sql.Add(sqlT);

            sqlT = "INSERT INTO CMTXTFORMATFOOTER (TXTFORMAT_CODE , TXTFORMATFT_NM ,SEQ_NO ,TXTFORMATTYPE_CODE ,TXTFORMAT_DESC ,TXTLENG ,FILLVALUE_FLAG ,FILLVALUE_DATA, FORMATE_EXAMPLE ,TXTFILED_VALUE  , TXTDEFAULT_VALUE  ,FORMATE_VALUE  ) ";
            sqlT += "VALUES ('" + TXTFORMAT_CODE + "'  , TXTFORMATFT_NM ,SEQ_NO ,TXTFORMATTYPE_CODE ,TXTFORMAT_DESC ,TXTLENG ,FILLVALUE_FLAG ,FILLVALUE_DATA, FORMATE_EXAMPLE ,TXTFILED_VALUE  , TXTDEFAULT_VALUE  ,FORMATE_VALUE  ) ";
            sql.Add(sqlT);

            for (int i = 0; i < sql.Count; i++)
            {
                try
                {
                    WebUtil.ExeSQL(sql[i].ToString());
                }
                catch { }
            }
        }

        public static Dictionary<string, object> getFileFormatDataBy(String format_code, String filepath)
        {
            Dictionary<string, object> data = new Dictionary<string, object>();

            data.Add("HEADER", getFileFormatHeaderDataBy(format_code, filepath));
            data.Add("DETAIL", getFileFormatDetailDataBy(format_code, filepath));
            data.Add("FOOTER", getFileFormatFooterDataBy(format_code, filepath));

            return data;
        }



        public static Dictionary<string, string>[] getFileFormatDataBy(String format_code, String data_type, String filepath)
        {
            Dictionary<string, string>[] data = null;

            //filepath = "D:\\GCOOP_ALL\\CORE\\GCOOP\\Saving\\Applications\\atm_offline\\Examples\\Import\\161210_coa004_loan_d1.09.txt";

            string[] fileData = File.ReadAllLines(filepath);
            int str = 0;
            int end = 1;
            string field_nm = "";
            if (data_type == "HEADER")
            {
                str = 0;
                end = 1;
                field_nm = "HD";
                data = new Dictionary<string, string>[end - str];
            }
            else if (data_type == "FOOTER")
            {
                str = fileData.Length - 1;
                end = fileData.Length;
                field_nm = "FT";
                data = new Dictionary<string, string>[end - str];
            }
            else
            {
                str = 1;
                end = fileData.Length - 1;
                field_nm = "DT";
                data = new Dictionary<string, string>[end - str];
            }

            try
            {
                for (int i = str, j = 0; i < fileData.Length && i < end; i++, j++)
                {
                    data[j] = new Dictionary<string, string>();
                    String DATA = fileData[i];
                    String sql = "select t.* ,TXTFORMAT  from CMTXTFORMAT" + data_type + " t,CMTXTFORMATUCFTYPE tf  where t.TXTFORMAT_CODE='" + format_code + "' and t.TXTFORMATTYPE_CODE=tf.TXTFORMATTYPE_CODE order by t.seq_no asc ";
                    Sdt sdt = WebUtil.QuerySdt(sql);
                    while (sdt.Next())
                    {
                        int strpos = sdt.GetInt32("TXTSTARTPOS");
                        int strlen = sdt.GetInt32("TXTLENG");
                        string key = sdt.GetString("TXTFORMAT" + field_nm + "_NM");
                        string datatype = sdt.GetString("TXTFORMAT_CODE");
                        string dataformat = sdt.GetString("TXTFORMAT");
                        string value = DATA.Substring(strpos - 1, strlen);
                        try
                        {
                            if (datatype == "N")
                            {
                                String value1 = value.Substring(0, strlen - 2);
                                String value2 = value.Substring(strlen - 2, 2);
                                value = value1 + value2;
                                //Decimal val = Decimal.Parse(value);
                                data[j].Add(key, value);
                            }
                            else if (datatype.IndexOf("D") > 0)
                            {
                                //DateTime val = DateTime.ParseExact(value, dataformat, CultureInfo.InvariantCulture);
                                data[j].Add(key, value);
                            }
                            else
                            {
                                data[j].Add(key, value);
                            }
                        }
                        catch { }
                    }
                }
            }
            catch { }

            return data;
        }

        public static Dictionary<string, string>[] getFileFormatHeaderDataBy(String format_code, String filepath)
        {
            return getFileFormatDataBy(format_code, "HEADER", filepath);
        }

        public static Dictionary<string, string>[] getFileFormatFooterDataBy(String format_code, String filepath)
        {
            return getFileFormatDataBy(format_code, "FOOTER", filepath);
        }

        public static Dictionary<string, string>[] getFileFormatDetailDataBy(String format_code, String filepath)
        {
            return getFileFormatDataBy(format_code, "DETAIL", filepath);
        }

        //END CMTXTFORMAT

        public static String saveToFileFormat(String format_code, Dictionary<string, object> data, String root_path, String coop_id, String suffix, String prefix, DateTime operate_date)
        {
            String filename = root_path;
            String filenameformat = "";
            String sql = "select t.*  from CMTXTFORMATMAS t where t.TXTFORMAT_CODE='" + format_code + "'  ";
            Sdt sdt = WebUtil.QuerySdt(sql);
            while (sdt.Next())
            {
                filenameformat = sdt.GetString("txtformat_filename");
            }
            //data
            filenameformat = operate_date.ToString("yyyyMMdd") + "_" + coop_id;
            filename += "\\" + prefix + filenameformat + suffix;

            String data_txt = "";
            String[] keys = { "HEADER", "DETAIL", "FOOTER" };
            for (int k = 0; k < keys.Length; k++)
            {
                Dictionary<string, string>[] list = (Dictionary<string, string>[])data[keys[k]];
                //data_txt += "" + keys[k] + "\r\n";
                for (int i = 0; i < list.Length; i++)
                {
                    Dictionary<string, string> d = (Dictionary<string, string>)list[i];
                    foreach (string key in d.Keys)
                    {
                        //data_txt += (i + 1) + ")." + key + "=" + d[key] + "";
                        data_txt += d[key] + "";
                    }
                    data_txt += "" + "\r\n";
                }
            }

            File.WriteAllText(filename, data_txt);
            return filename;
        }


        public static string GenerateUpdateQuery(DataTable Table, string TableName, ArrayList ParameterArray)
        {
            int i = 0;
            string tempstr = "";
            int temp_index = -1;

            //string TableName = (string)Session["TableSelected"];
            string Query = "";
            Query = "Update  " + TableName + " set ";

            for (i = 0; i < Table.Columns.Count; i++)
            {
                switch (Table.Columns[i].DataType.Name)
                {

                    case "Boolean":
                    case "Int32":
                    case "Byte":
                    case "Decimal":
                        if ((string)ParameterArray[i] == "True")
                            ParameterArray[i] = "1";
                        else if ((string)ParameterArray[i] == "False")
                            ParameterArray[i] = "0";

                        if (i == Table.Columns.Count - 1)
                            Query = Query + Table.Columns[i].ColumnName + "=" + ParameterArray[i];
                        else
                            Query = Query + Table.Columns[i].ColumnName + "=" + ParameterArray[i] + ", ";

                        break;
                    case "String":
                    case "DateTime":
                        if (((string)ParameterArray[i]).Contains("'"))
                        {
                            tempstr = ((string)ParameterArray[i]);
                            ParameterArray[i] = ((string)ParameterArray[i]).Replace("'", "''");
                            temp_index = i;
                        }

                        if (i == Table.Columns.Count - 1)
                            Query = Query + Table.Columns[i].ColumnName + "='" + ParameterArray[i] + "' ";
                        else
                            Query = Query + Table.Columns[i].ColumnName + "='" + ParameterArray[i] + "', ";
                        break;

                }
            }
            if (temp_index > -1)
                ParameterArray[temp_index] = tempstr;
            if (Table.Columns[0].DataType.Name == "String" || Table.Columns[0].DataType.Name == "DateTime")
                Query = Query + " where " + Table.Columns[0].ColumnName + " = '" + ParameterArray[0] + "'";
            else
                Query = Query + " where " + Table.Columns[0].ColumnName + " = " + ParameterArray[0];

            return Query;
        }

        public static string GenerateInsertQuery(DataTable Table, string TableName, ArrayList ParameterArray)
        {
            int i = 0;
            string tempstr = "";
            int temp_index = -1;

            //string TableName = (string)Session["TableSelected"];
            string Query = "";
            Query = "Insert into  " + TableName + "(";

            for (i = 0; i < Table.Columns.Count; i++)
            {
                if (i == Table.Columns.Count - 1)
                    Query = Query + Table.Columns[i].ColumnName;
                else
                    Query = Query + Table.Columns[i].ColumnName + ", ";

            }
            Query = Query + ")" + "Values (";
            for (i = 0; i < Table.Columns.Count; i++)
            {

                switch (Table.Columns[i].DataType.Name)
                {

                    case "Boolean":
                    case "Int32":
                    case "Byte":
                    case "Decimal":
                        if ((string)ParameterArray[i] == "True")
                            ParameterArray[i] = "1";
                        else if ((string)ParameterArray[i] == "False")
                            ParameterArray[i] = "0";

                        if (i == Table.Columns.Count - 1)
                            Query = Query + ParameterArray[i];
                        else
                            Query = Query + ParameterArray[i] + ", ";

                        break;
                    case "String":
                    case "DateTime":
                        if (((string)ParameterArray[i]).Contains("'"))
                        {
                            tempstr = ((string)ParameterArray[i]);
                            ParameterArray[i] = ((string)ParameterArray[i]).Replace("'", "''");
                            temp_index = i;
                        }
                        if (i == Table.Columns.Count - 1)
                            Query = Query + "'" + ParameterArray[i] + "' ";
                        else
                            Query = Query + "'" + ParameterArray[i] + "', ";
                        break;

                }


            }
            Query = Query + ")";


            return Query;
        }

        public static string GenerateDeleteQuery(int index, DataTable Table, string TableName)
        {
            //string TableName = (string)Session["TableSelected"];
            string query = "";
            if (Table.Columns[0].DataType.Name == "String" || Table.Columns[0].DataType.Name == "DateTime")
                query = "Delete from " + TableName + " where " + Table.Columns[0].ColumnName + "='" + Table.Rows[index][0].ToString() + "'";
            else
                query = "Delete from " + TableName + " where " + Table.Columns[0].ColumnName + "=" + Table.Rows[index][0].ToString();

            return query;
        }

        public static void GenerateGridViewEditable(ref GridView gridView, String tablename)
        {
            DynamicallyGridView d = (DynamicallyGridView)gridView;
            d.CreateTemplatedGridView(tablename);
            gridView = d;
        }


        public static string checkconcoll(string member_no, string coop_id, string loancolltype_code,string memb_request)
        { //ฟังชันก์เช็คค้ำประกัน

            string word = "";
            string membtype_code = "0";
            string sql_membtype = @" select membtype_code from mbmembmaster where  member_no ={0} and  coop_id ={1}"; // ดึงประเภทสมาชิกออกมา 10 ปกติ นอกนั้น สมทบ
            sql_membtype = WebUtil.SQLFormat(sql_membtype, member_no, coop_id);
            Sdt dtIns = WebUtil.QuerySdt(sql_membtype);
            if (dtIns.Next())
            {
                membtype_code = dtIns.GetString("membtype_code");

            }

            if (membtype_code == "10")  //ถ้า เป็นปกติ
            {
                if (loancolltype_code == "04")  //เช็ค ว่าใช้ คนค้ำ  loancolltype_code == "04"  
                {
                    decimal countcoll = 0, maxpeople = 0;
                    string sql66 = @"SELECT grtright_contract,grtright_member,grtmemco_contract,grtmemco_member FROM LNLOANCONSTANT where  coop_id= {0}";

                    sql66 = WebUtil.SQLFormat(sql66, coop_id);
                    Sdt dt = WebUtil.QuerySdt(sql66);
                    if (dt.Next())
                    {
                        countcoll = dt.GetDecimal("grtright_contract"); //จำนวน สัญญา 
                        maxpeople = dt.GetDecimal("grtright_member");  // จำนวนคน
                    }
                    if (countcoll > 0 && maxpeople > 0) // ถ้าค่าคงที่จำกัด  ทั้ง คน และสัญญา ที่ใช้ค้ำ
                    {
                        decimal countcoll2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา
                        decimal maxpeople2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา

                        string sql67 = @"select count(lncontcoll.loancontract_no)as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) ";

                        sql67 = WebUtil.SQLFormat(sql67, coop_id, member_no);
                        Sdt dt2 = WebUtil.QuerySdt(sql67);
                        if (dt2.Next())
                        {

                            countcoll2 = dt2.GetDecimal("cln");

                        }

                        string sql68 = @"select count(DISTINCT  lncontmaster.member_no) as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) and lncontmaster.member_no not in ({2})";  //ไม่เอา คนที่ขอกู้มานับ

                        sql68 = WebUtil.SQLFormat(sql68, coop_id, member_no, memb_request);
                        Sdt dt3 = WebUtil.QuerySdt(sql68);
                        if (dt3.Next())
                        {

                            maxpeople2 = dt3.GetDecimal("cln");

                        }


                        if ((countcoll <= countcoll2) && (maxpeople <= maxpeople2)) //เช็คสัญญา ที่ conmaster มากกว่า ใน ค่าคง ที่
                        {//ถ้า จำนวนสัญญา ที่สำมารถค้ำได้ มากกว่า สัญญาที่ค้ำอยู่แล้ว
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + countcoll + " สัญญา และ คำไปแล้ว :" + maxpeople + " คน";
                        }
                        else if (maxpeople <= maxpeople2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + maxpeople + " คน ";

                        }
                        else if (countcoll <= countcoll2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + countcoll + " สัญญา ";

                        }
                        else
                        {
                            word = "";
                        }
                    }
                    else if (countcoll > 0) // ถ้ามีเงื่อนไข เฉพาะสัญญาค้ำ
                    {
                        decimal countcoll2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา
                        string sql67 = @"select count(lncontcoll.loancontract_no)as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) ";

                        sql67 = WebUtil.SQLFormat(sql67, coop_id, member_no);
                        Sdt dt2 = WebUtil.QuerySdt(sql67);
                        if (dt2.Next())
                        {

                            countcoll2 = dt2.GetDecimal("cln");

                        }
                        if (countcoll <= countcoll2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + countcoll + " สัญญา ";

                        }
                        else {

                            word = "";
                        }
                    }
                    else if (maxpeople > 0) // ถ้ามีเงื่อนไข เฉพาะคนค้ำ
                    {
                        decimal maxpeople2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา
                        string sql68 = @"select count(DISTINCT  lncontmaster.member_no) as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) and lncontmaster.member_no not in ({2})";

                        sql68 = WebUtil.SQLFormat(sql68, coop_id, member_no,memb_request);
                        Sdt dt3 = WebUtil.QuerySdt(sql68);
                        if (dt3.Next())
                        {

                            maxpeople2 = dt3.GetDecimal("cln");

                        }

                        if (maxpeople <= maxpeople2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + maxpeople + " คน ";

                        }
                        else
                        {

                            word = "";
                        }
                    }
                    else {

                        word = "";
                    
                    }
                }


            }
            else
            {//สมทบ
                if (loancolltype_code == "04")  //เช็ค ว่าใช้ หลักทรัพย์ค้ำ  loancolltype_code == "04"  นอกนั้นใช้คนค้ำ
                {
                    decimal countcoll = 0, maxpeople = 0;
                    string sql66 = @"SELECT grtright_contract,grtright_member,grtmemco_contract,grtmemco_member FROM LNLOANCONSTANT where  coop_id= {0}";

                    sql66 = WebUtil.SQLFormat(sql66, coop_id);
                    Sdt dt = WebUtil.QuerySdt(sql66);
                    if (dt.Next())
                    {
                        countcoll = dt.GetDecimal("grtmemco_contract"); //จำนวน สัญญา 
                        maxpeople = dt.GetDecimal("grtmemco_member");  // จำนวนคน
                    }
                    if (countcoll > 0 && maxpeople > 0) // ถ้าค่าคงที่จำกัด  ทั้ง คน และสัญญา ที่ใช้ค้ำ
                    {
                        decimal countcoll2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา
                        decimal maxpeople2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา

                        string sql67 = @"select count(lncontcoll.loancontract_no)as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) ";

                        sql67 = WebUtil.SQLFormat(sql67, coop_id, member_no);
                        Sdt dt2 = WebUtil.QuerySdt(sql67);
                        if (dt2.Next())
                        {

                            countcoll2 = dt2.GetDecimal("cln");

                        }

                        string sql68 = @"select count(DISTINCT  lncontmaster.member_no) as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) and lncontmaster.member_no not in ({2})";

                        sql68 = WebUtil.SQLFormat(sql68, coop_id, member_no,memb_request);
                        Sdt dt3 = WebUtil.QuerySdt(sql68);
                        if (dt3.Next())
                        {

                            maxpeople2 = dt3.GetDecimal("cln");

                        }


                        if ((countcoll <= countcoll2) && (maxpeople <= maxpeople2)) //เช็คสัญญา ที่ conmaster มากกว่า ใน ค่าคง ที่
                        {//ถ้า จำนวนสัญญา ที่สำมารถค้ำได้ มากกว่า สัญญาที่ค้ำอยู่แล้ว
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + countcoll + " สัญญา และ คำไปแล้ว :" + maxpeople + " คน";
                        }
                        else if (maxpeople <= maxpeople2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" +  maxpeople + " คน ";

                        }
                        else if (countcoll <= countcoll2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + countcoll + " สัญญา ";

                        }
                        else
                        {
                            word = "";
                        }

                    }
                    else if (countcoll > 0) // ถ้ามีเงื่อนไข เฉพาะสัญญาค้ำ
                    {
                        decimal countcoll2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา
                        string sql67 = @"select count(lncontcoll.loancontract_no)as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) ";

                        sql67 = WebUtil.SQLFormat(sql67, coop_id, member_no);
                        Sdt dt2 = WebUtil.QuerySdt(sql67);
                        if (dt2.Next())
                        {

                            countcoll2 = dt2.GetDecimal("cln");

                        }
                        if (countcoll <= countcoll2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + countcoll + " สัญญา ";

                        }
                        else
                        {

                            word = "";
                        }
                    }
                    else if (maxpeople > 0) // ถ้ามีเงื่อนไข เฉพาะคนค้ำ
                    {
                        decimal maxpeople2 = 0; //เช็ค ว่าสัญญา ที่ยังไม่หมด  ค้ำไปแล้วกี่สัญญา
                        string sql68 = @"select count(DISTINCT  lncontmaster.member_no) as cln from lncontcoll left join lncontmaster on lncontcoll.loancontract_no = lncontmaster.loancontract_no
                                where ( lncontcoll.coop_id = {0} ) and ( lncontmaster.contract_status = 1 ) and ( lncontcoll.ref_collno = {1} ) and lncontmaster.member_no not in ({2})";

                        sql68 = WebUtil.SQLFormat(sql68, coop_id, member_no,memb_request);
                        Sdt dt3 = WebUtil.QuerySdt(sql68);
                        if (dt3.Next())
                        {

                            maxpeople2 = dt3.GetDecimal("cln");

                        }

                        if (maxpeople <= maxpeople2)
                        {
                            word = "สมาชิกท่านนี้ ไม่สามารถค้ำประกันได้!! \\n เนื่องจากสมาชิกท่านนี้ ค้ำประกันไปแล้ว :" + maxpeople + " คน ";

                        }
                        else
                        {

                            word = "";
                        }
                    }
                    else
                    {

                        word = "";

                    }
                }

               
            }
            return word;
        }

        public static void processPostToFin()
        {
            XmlConfigService xmlconfig = new XmlConfigService(WebUtil.GetGcoopPath());
            WebState state = new WebState();
            WcfCalling wcf = new WcfCalling(xmlconfig);
            String prcoXml = "";
            prcoXml = "<?xml version=\"1.0\" encoding=\"UTF-16LE\" standalone=\"no\"?><d_listchooseproc><d_listchooseproc_row><process_dep>1</process_dep><process_shl>1</process_shl><process_shr>1</process_shr><process_lon>1</process_lon><process_lrt>1</process_lrt><process_div>0</process_div></d_listchooseproc_row></d_listchooseproc>";

            try
            {
                //int re = fin.OfProcessOtherToFin(state.SsWsPass, state.SsCoopId, state.SsUsername, state.SsWorkDate, state.SsClientIp, prcoXml);
                int re = wcf.NFinance.of_postprocessotherto_fin(state.SsWsPass, state.SsCoopId, state.SsUsername, state.SsWorkDate, state.SsClientIp, prcoXml);
            }
            catch (Exception ex)
            {
                //Ex
            }
        }


        //ได้ไม่จำกัด
        public static string ThaiBahtText(string txt)
        {
            return ThaiBahtTextUtil.ThaiBahtText(Convert.ToDecimal(txt));
        }

        //ได้แค่่ ยอด 1 ล้านบาท
        public static string ThaiBaht(string txt)
        {
            string bahtTxt, n, bahtTH = "";
            double amount;
            try { amount = Convert.ToDouble(txt); }
            catch { amount = 0; }
            bahtTxt = amount.ToString("####.00");
            string[] num = { "ศูนย์", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า", "หก", "เจ็ด", "แปด", "เก้า", "สิบ" };
            string[] rank = { "", "สิบ", "ร้อย", "พัน", "หมื่น", "แสน", "ล้าน" };
            string[] temp = bahtTxt.Split('.');
            string intVal = temp[0];
            string decVal = temp[1];
            if (Convert.ToDouble(bahtTxt) == 0)
                bahtTH = "ศูนย์บาทถ้วน";
            else
            {
                for (int i = 0; i < intVal.Length; i++)
                {
                    n = intVal.Substring(i, 1);
                    if (n != "0")
                    {
                        if ((i == (intVal.Length - 1)) && (n == "1"))
                            bahtTH += "เอ็ด";
                        else if ((i == (intVal.Length - 2)) && (n == "2"))
                            bahtTH += "ยี่";
                        else if ((i == (intVal.Length - 2)) && (n == "1"))
                            bahtTH += "";
                        else
                            bahtTH += num[Convert.ToInt32(n)];
                        bahtTH += rank[(intVal.Length - i) - 1];
                    }
                }
                bahtTH += "บาท";
                if (decVal == "00")
                    bahtTH += "ถ้วน";
                else
                {
                    for (int i = 0; i < decVal.Length; i++)
                    {
                        n = decVal.Substring(i, 1);
                        if (n != "0")
                        {
                            if ((i == decVal.Length - 1) && (n == "1"))
                                bahtTH += "เอ็ด";
                            else if ((i == (decVal.Length - 2)) && (n == "2"))
                                bahtTH += "ยี่";
                            else if ((i == (decVal.Length - 2)) && (n == "1"))
                                bahtTH += "";
                            else
                                bahtTH += num[Convert.ToInt32(n)];
                            bahtTH += rank[(decVal.Length - i) - 1];
                        }
                    }
                    bahtTH += "สตางค์";
                }
            }
            return bahtTH;
        }
    }
    /// <summary>
    /// รูปแบบการใช้คำว่า 'เอ็ด' เมื่อมีค่าหนึ่งที่หลักหน่วย
    /// </summary>
    public enum UsesEt
    {

        /// <summary>
        /// ใช้เอ็ดกับหลักสิบเท่านั้น (ยี่สิบเอ็ด-เก้าสิบเอ็ด)
        /// </summary>
        TensOnly = 0,

        /// <summary>
        /// ใช้เอ็ดเสมอ (รวมถึงร้อยเอ็ด พันเอ็ด ล้านเอ็ด เป็นต้น)
        /// เป็นรูปแบบที่ราชบัณฑิตยสภาแนะนำ
        /// </summary>
        Always = 1
    }
    public class ThaiBahtTextOptions
    {

        public UsesEt UsesEt { get; set; }
        public Unit Unit { get; set; }
        public bool AppendBahtOnly { get; set; }
        public int DecimalPlaces { get; set; }

        public ThaiBahtTextOptions()
            : this(usesEt: UsesEt.TensOnly,
                   unit: Unit.Baht,
                   decimalPlaces: 2,
                   appendBahtOnly: true)
        {
            //
        }

        public ThaiBahtTextOptions(UsesEt usesEt = UsesEt.TensOnly,
                                   Unit unit = Unit.Baht,
                                   int decimalPlaces = 2,
                                   bool appendBahtOnly = true)
        {
            this.UsesEt = usesEt;
            this.Unit = unit;
            this.AppendBahtOnly = appendBahtOnly;
        }
    }
    /// <summary>
    /// หน่วยของจำนวนเงิน
    /// </summary>
    public enum Unit
    {

        /// <summary>
        /// จำนวนเงินมีหน่วยเป็น บาท
        /// </summary>
        Baht,

        /// <summary>
        /// จำนวนเงินมีหน่วยเป็น ล้านบาท
        /// </summary>
        Million,

        /// <summary>
        /// จำนวนเงินมีหน่วยเป็น พันล้านบาท
        /// </summary>
        Billion,

        /// <summary>
        /// จำนวนเงินมีหน่วยเป็น ล้านล้านบาท
        /// </summary>
        Trillion
    }

    public static class ThaiBahtTextUtil
    {

        // Largest acceptable values is 999,999,999,999,999,999.99
        public const decimal MaxValue = 999999999999999999.99m;

        // Smallest acceptable values is -999,999,999,999,999,999.99
        public const decimal MinValue = -999999999999999999.99m;

        // This array may looks strange. Let's see example:
        // if value is "512", its length is 3
        //              ^-----so at hundreds' place will uses thaiPlaces[3] that is Roi.
        //               ^----at tens' place will be thaiPlaces[2] that is Sib.
        private static string[] thaiPlaces = new string[] { 
      string.Empty, string.Empty, "สิบ", "ร้อย", "พัน", "หมื่น", "แสน", "ล้าน" 
    };

        // Simply the number reading in Thai.
        private static string[] thaiNumbers = new string[] {
      "ศูนย์", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า",  "หก", "เจ็ด", "แปด", "เก้า" 
    };


        /// <summary>
        /// ให้ข้อความจำนวนเงินภาษาไทย เช่น จำนวน 121.50 บาท จะให้ผลลัพธ์เป็น "หนึ่งร้อยยี่สิบเอ็ดบาทห้าสิบสตางค์"
        /// </summary>
        /// <param name="amount">จำนวนเงิน</param>
        /// <param name="options">ตัวเลือก</param>
        /// <returns>ข้อความจำนวนเงินภาษาไทย</returns>
        public static string ThaiBahtText(this decimal? amount,
                                          ThaiBahtTextOptions options)
        {

            return ThaiBahtText(amount ?? 0m,
                                options.UsesEt,
                                options.Unit,
                                options.DecimalPlaces,
                                options.AppendBahtOnly);
        }


        /// <summary>
        /// ให้ข้อความจำนวนเงินภาษาไทย เช่น จำนวน 121.50 บาท จะให้ผลลัพธ์เป็น "หนึ่งร้อยยี่สิบเอ็ดบาทห้าสิบสตางค์"
        /// </summary>
        /// <param name="amount">จำนวนเงิน</param>
        /// <param name="options">ตัวเลือก</param>
        /// <returns>ข้อความจำนวนเงินภาษาไทย</returns>
        public static string ThaiBahtText(this decimal amount,
                                          ThaiBahtTextOptions options)
        {

            return ThaiBahtText(amount,
                                options.UsesEt,
                                options.Unit,
                                options.DecimalPlaces,
                                options.AppendBahtOnly);
        }

        /// <summary>
        /// ให้ข้อความจำนวนเงินภาษาไทย เช่น จำนวน 121.50 บาท จะให้ผลลัพธ์เป็น "หนึ่งร้อยยี่สิบเอ็ดบาทห้าสิบสตางค์"
        /// </summary>
        /// <param name="amount">จำนวนเงิน</param>
        /// <param name="usesEt">รูปแบบการใช้เอ็ดสำหรับค่าหนึ่งที่หลักหน่วย</param>
        /// <param name="unit">หน่วยของจำนวนเงิน</param>
        /// <param name="appendBahtOnly">เพิ่มคำว่า 'ถ้วน' ท้ายข้อความ</param>
        /// <returns>ข้อความจำนวนเงินภาษาไทย</returns>
        public static string ThaiBahtText(this decimal? amount,
                                          UsesEt usesEt = UsesEt.Always,
                                          Unit unit = Unit.Baht,
                                          int decimalPlaces = 2,
                                          bool appendBahtOnly = true)
        {

            return ThaiBahtText(amount ?? 0m,
                                usesEt: usesEt,
                                unit: unit,
                                decimalPlaces: decimalPlaces,
                                appendBahtOnly: appendBahtOnly);
        }


        /// <summary>
        /// ให้ข้อความจำนวนเงินภาษาไทย เช่น จำนวน 121.50 บาท จะให้ผลลัพธ์เป็น "หนึ่งร้อยยี่สิบเอ็ดบาทห้าสิบสตางค์"
        /// </summary>
        /// <param name="amount">จำนวนเงิน</param>
        /// <param name="usesEt">รูปแบบการใช้เอ็ดสำหรับค่าหนึ่งที่หลักหน่วย</param>
        /// <param name="unit">หน่วยของจำนวนเงิน</param>
        /// <param name="appendBahtOnly">เพิ่มคำว่า 'ถ้วน' ท้ายข้อความ</param>
        /// <returns>ข้อความจำนวนเงินภาษาไทย</returns>
        public static string ThaiBahtText(this decimal amount,
                                          UsesEt usesEt = UsesEt.Always,
                                          Unit unit = Unit.Baht,
                                          int decimalPlaces = 2,
                                          bool appendBahtOnly = true)
        {

            var result = new StringBuilder();

            if (amount == 0)
            {
                switch (unit)
                {
                    case Unit.Baht: result.Append("ศูนย์บาท"); break;
                    case Unit.Million: result.Append("ศูนย์ล้านบาท"); break;
                    case Unit.Billion: result.Append("ศูนย์พันล้านบาท"); break;
                    case Unit.Trillion: result.Append("ศูนย์ล้านล้านบาท"); break;
                }
                if (appendBahtOnly)
                {
                    result.Append("ถ้วน");
                }

                return result.ToString();
            }

            string format = "#.00";
            bool isBaht = unit == Unit.Baht;

            if (!isBaht)
            {
                switch (unit)
                {
                    case Unit.Million: amount /= 1000000.0m; break;
                    case Unit.Billion: amount /= 1000000000.0m; break;
                    case Unit.Trillion: amount /= 1000000000000.0m; break;
                }
                switch (decimalPlaces)
                {
                    case 0: format = "0.0"; break; // we still need satang
                    case 1: format = "0.0"; break;
                    case 2: format = "0.0#"; break;
                    case 3: format = "0.0##"; break;
                    case 4: format = "0.0###"; break;
                    case 5: format = "0.0####"; break;
                    case 6: format = "0.0#####"; break;
                    default:
                        format = "0.0#";
                        decimalPlaces = 2;
                        break;
                }
            }
            else
            {
                decimalPlaces = 2; // always 2 for unit Baht
            }

            amount = Math.Round(amount, decimalPlaces, MidpointRounding.AwayFromZero);

            if (amount < MinValue || MaxValue < amount)
            {
                throw new NotSupportedException();
            }

            if (amount < 0)
            {
                result.Append("ลบ");
                amount = -amount;
            }

            string text = amount.ToString(format);
            string[] parts = decompose(text);

            if (parts[0].Length > 0)
            {
                speakTo(result, parts[0], usesEt);
                result.Append("ล้าน");
            }

            if (parts[1].Length > 0)
            {
                speakTo(result, parts[1], usesEt);
                result.Append("ล้าน");
            }

            if (parts[2].Length > 0)
            {
                speakTo(result, parts[2], usesEt);
                if (isBaht) result.Append("บาท");
            }
            else if (parts[1].Length > 0)
            {
                if (isBaht) result.Append("บาท");
            }

            if (parts[3].Length > 0)
            {
                if (isBaht)
                {
                    speakTo(result, parts[3], usesEt);
                    result.Append("สตางค์");
                }
                else
                {
                    if (int.Parse(parts[3]) != 0)
                    {
                        speakDotTo(result, parts[3]);
                    }
                    switch (unit)
                    {
                        case Unit.Million: result.Append("ล้านบาท"); break;
                        case Unit.Billion: result.Append("พันล้านบาท"); break;
                        case Unit.Trillion: result.Append("ล้านล้านบาท"); break;
                    }
                }
            }
            else
            {
                if (appendBahtOnly)
                {
                    result.Append("ถ้วน");
                }
            }

            return result.ToString();
        }

        private static string[] decompose(string text)
        {

            string s1 = string.Empty;
            string s2 = string.Empty;
            string s3;
            string s4;
            int position;

            position = text.IndexOf('.');

            s3 = text.Substring(0, position);
            s4 = text.Substring(position + 1);
            if (s4 == "00")
            {
                s4 = string.Empty;
            }

            int length = s3.Length;
            if (length > 6)
            {
                s2 = s3.Substring(0, length - 6);
                s3 = s3.Substring(length - 6);
            }

            length = s2.Length;
            if (length > 6)
            {
                s1 = s2.Substring(0, length - 6);
                s2 = s2.Substring(length - 6);
            }

            if ((s3.Length > 0) && (int.Parse(s3) == 0))
            {
                s3 = string.Empty;
            }

            return new string[] { s1, s2, s3, s4 };
        }


        private static void speakDotTo(StringBuilder sb, string text)
        {

            sb.Append("จุด");
            for (int i = 0; i < text.Length; i++)
            {
                int c = int.Parse(text[i].ToString());
                sb.Append(thaiNumbers[c]);
            }
        }

        private static void speakTo(StringBuilder sb, string text, UsesEt mode)
        {

            int length = text.Length;
            int c = 0;
            int lastc = -1;
            bool negative = false;

            for (int i = 0; i < length; i++)
            {
                if (text[i] == '-')
                {
                    negative = true;
                }
                else
                {
                    c = int.Parse(text[i].ToString());

                    if ((i == length - 1) && (c == 1))
                    {
                        if (length == 1                  //  1
                          || (negative && length == 2)   // -1
                          || (length == 2 && lastc == 0) // 01 (satang)
                          )
                        {
                            sb.Append("หนึ่ง");
                            return;
                        }
                        if (mode == UsesEt.Always)
                        {
                            sb.Append("เอ็ด");
                        }
                        else
                        { // if (mode == UsesEt.TensOnly) {
                            if (lastc == 0)
                            {
                                sb.Append("หนึ่ง");
                            }
                            else
                            {
                                sb.Append("เอ็ด");
                            }
                        }
                    }
                    else if ((i == length - 2) && (c == 2))
                    {
                        sb.Append("ยี่สิบ");
                    }
                    else if ((i == length - 2) && (c == 1))
                    {
                        sb.Append("สิบ");
                    }
                    else if (c != 0)
                    {
                        sb.Append(thaiNumbers[c] + thaiPlaces[length - i]);
                    }
                }
                lastc = c;
            }
        }
		
		

    }

}