Exemple #1
0
        private static Dictionary <string, ItemInfo> GetItemInfoLookup(User user, string sPartner, string dbSales, IEnumerable <string> vendList, IEnumerable <string> upcList)
        {
            Dictionary <string, ItemInfo> itemDict = new Dictionary <string, ItemInfo>();

            try
            {
                DBConnect connection = ConnectionsMgr.GetSalesConnection(user, dbSales);
                string    colVendNum = sPartner == _Partner.Thalia ?
                                       $"SUBSTR(TRIM({VendorNum}),1,11)" :
                                       $"TRIM({VendorNum})";
                using (var queryItemInfo = connection.Select("vendornum,upcnum,prodcat,deptname,prodsubcat,classdesc,subcdesc,itemdesc,colorcode,itemcolor", _Table.Sl_MasterCat, $"WHERE {colVendNum} IN {vendList.ToSqlValueList()} AND TRIM({UPCNum}) IN {upcList.ToSqlValueList()} group by vendornum,upcnum"))
                {
                    while (queryItemInfo.Read())
                    {
                        string key = queryItemInfo.FieldByName(VendorNum).Trim() + queryItemInfo.FieldByName(UPCNum).Trim();
                        if (!itemDict.ContainsKey(key))
                        {
                            itemDict.Add(key, new ItemInfo(queryItemInfo));
                        }
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                Log(user, nameof(GetItemInfoLookup), e);
            }
            return(itemDict);
        }
Exemple #2
0
        public static List <ProcessRecord> GetPreviousRecords(User user)
        {
            List <ProcessRecord> listRecord = new List <ProcessRecord>();
            DBConnect            connection = new DBConnect();

            try
            {
                var filter = string.Format("WHERE {0}='{1}' AND {2}='{3}' AND {4}>'{5}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape(), _Column.PReqDate, DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss"));
                connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
                using (var query = connection.Select(new[] { _Column.PReqDate, _Column.PReqType, _Column.Processed }, _Table.ProcessQ, filter))
                {
                    while (query.Read())
                    {
                        ProcessRecord pr = new ProcessRecord();
                        pr.Date   = query.Field(0, "");
                        pr.Type   = query.Field(1, "");
                        pr.Status = query.Field(2, "");
                        listRecord.Add(pr);
                    }
                }
                connection.Close();
                return(listRecord);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "ProcessQueue", "GetPreviousRecords", e.Message);
                connection.Close();
                return(new List <ProcessRecord>());
            }
        }
Exemple #3
0
        public static List <PoSummary> VerifyPoList(User user, IEnumerable <string> poList)
        {
            List <PoSummary> ret = new List <PoSummary>();

            try
            {
                string sCustomer  = user.Customer.SQLEscape();
                string sPartner   = user.ActivePartner.SQLEscape();
                var    connection = ConnectionsMgr.GetOCConnection(user, ECGB);
                {
                    using (var queryCurrent = connection.Select($"{UniqueKey},{PONumber}", SrcH850, $"WHERE {Customer}='{sCustomer}' AND {Partner}='{sPartner}' AND POStatus IN (0, 1) AND {PONumber} IN {poList.ToSqlValueList()}"))
                    {
                        while (queryCurrent.Read())
                        {
                            ret.Add(new PoSummary(queryCurrent.Field(0), queryCurrent.Field(1)));
                        }
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, nameof(PdsManager), nameof(VerifyPoList), e.Message);
            }
            return(ret);
        }
Exemple #4
0
        /// <summary>
        /// Gets the current list of account executives.
        /// </summary>
        /// <param name="user">The user making the request.</param>
        /// <returns></returns>
        public static List <string> GetAccountExecutiveList(User user)
        {
            List <string> aeList = new List <string>();

            try
            {
                DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
                {
                    var query = connection.Query("SELECT aename FROM deptdesc GROUP BY aename ORDER BY aename");
                    while (query.Read())
                    {
                        var ae = query.Field(0, "").Trim();
                        if (ae != "")
                        {
                            aeList.Add(ae);
                        }
                    }
                }
                connection.Close();
                aeList.Sort();
            }
            catch (Exception e)
            {
                Log(user, nameof(GetAccountExecutiveList), e);
            }
            return(aeList);
        }
Exemple #5
0
        private const int OCSessionTimeOutSeconds = 60; //10 TIMED OUT OFTEN (SENDING TO 404 PAGE), TRYING 30 SECONDS. CHANGED TO 60 SECS BECAUSE OF TEMPORARY TIME DIFF BETWEEN THE TWO SERVERS

        /// <summary>
        /// Authenticates the given user.
        /// </summary>
        /// <param name="usUserName">The username to check.</param>
        /// <param name="usPassword">The password to check.</param>
        /// <returns>True if the login info is valid, false otherwise.</returns>
        public static bool AuthenticateUser(string usUserName, string usPassword)
        {
            string    sUserName   = usUserName.SQLEscape();
            string    sStoredHash = "";
            DBConnect connect     = new DBConnect();

            try
            {
                connect.Connect(ConnectionsMgr.GetAuthConnInfo());
                using (var queryUserAuthInfo = connect.Select(columnPassword, _Table.Users, string.Format("WHERE {0}='{1}'", columnUserName, sUserName)))
                {
                    if (queryUserAuthInfo.AffectedRows <= 0)
                    {
                        connect.Close();
                        return(false);
                    }
                    queryUserAuthInfo.Read();
                    sStoredHash = Encoding.UTF8.GetString((byte[])queryUserAuthInfo.Field2(0));
                }
                connect.Close();
                return(MD5Crypt.Verify(usPassword, sStoredHash));
            }
            catch (Exception ex)
            {
                ProgramLog.LogError(null, "Auth", "AuthenticateUser", ex.Message + " " + ex.StackTrace);
                connect.Close();
                return(false);
            }
        }
Exemple #6
0
        public static bool CreateUploadRecord(User user, DateTime reqDate, string reqType, string fileName)
        {
            // uniquekey, preqdate, customer, partner, preqtype, preqaction, pfilename, pcustomfile, presultfile, presultdate, processed
            DBConnect connection = new DBConnect();
            bool      success    = false;

            try
            {
                connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
                var insertVals = new Dictionary <string, string>()
                {
                    { _Column.UniqueKey, connection.GetNewKey() },
                    { _Column.PReqDate, reqDate.ToString("yyyy-MM-dd HH:mm:ss") },
                    { _Column.Customer, user.Customer.SQLEscape() },
                    { _Column.PReqType, reqType.SQLEscape() },
                    { _Column.PFileName, fileName.SQLEscape() },
                    { _Column.PCustomFile, "1" },
                    { _Column.Partner, user.ActivePartner.SQLEscape() }
                };
                using (var res = connection.Insert(_Table.ProcessQ, insertVals.ToNameValueCollection()))
                {
                    success = res.AffectedRows != 0;
                }
                connection.Close();
                return(success);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "ProcessQueue", "CreateUploadRecord", e.Message);
                return(false);
            }
        }
Exemple #7
0
        public static List <SalesRequestDetail> GetPastOrders(User user)
        {
            List <SalesRequestDetail> pastRequests = new List <SalesRequestDetail>();

            try
            {
                var connection = ConnectionsMgr.GetAdminConnection();
                {
                    var request = connection.Select(new[] { _Column.RequestDate, _Column.OutputName, _Column.ToEmail, _Column.Processed }, _Table.SalesRequest, string.Format("WHERE {0}='{1}' AND {2}='{3}' ORDER BY reqdate DESC LIMIT 10", _Column.Customer, user.Customer.SQLEscape(), _Column.ConnectID, user.OCConnID.SQLEscape()));

                    while (request.Read())
                    {
                        pastRequests.Add(new SalesRequestDetail(
                                             ((DateTime)request.Field2(0)).ToString("MMM dd, yyyy"),
                                             request.Field(1, ""),
                                             request.Field(2, ""),
                                             request.Field(3, "").ToUpper() == "Y" ? "Processed" : "Unprocessed"
                                             ));
                    }
                }
                connection.Close();
                return(pastRequests);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "SalesRequestManager", "GetPastOrders", e.Message);
                return(new List <SalesRequestDetail>());
            }
        }
Exemple #8
0
        public static Dictionary <string, string> GetAllowedApps(User user)
        {
            DBConnect connection = new DBConnect();

            try
            {
                connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
                Dictionary <string, string> ret = new Dictionary <string, string>();

                using (var queryUserLevelAP = connection.Select(appFlags, _Table.UserInfo, string.Format("WHERE {0}='{1}' AND {2}='{3}' AND {4}='{5}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape(), _Column.UserName, user.UserName.SQLEscape())))
                {
                    while (queryUserLevelAP.Read())
                    {
                        for (int i = 0; i < appFlags.Length; i++)
                        {
                            string f = queryUserLevelAP.Field(i);
                            if (f == "1")
                            {
                                string name = columnToDetail[appFlags[i]].appName;
                                string url  = columnToDetail[appFlags[i]].appURL;
                                if (!ret.ContainsKey(name))
                                {
                                    ret.Add(name, url);
                                }
                            }
                        }
                    }
                    if (ret.Count > 0)
                    {
                        return(ret);
                    }
                }

                using (var queryCustPartAP = connection.Select(appFlags, _Table.NetGroup, string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape())))
                {
                    while (queryCustPartAP.Read())
                    {
                        for (int i = 0; i < appFlags.Length; i++)
                        {
                            string f = queryCustPartAP.Field(i);
                            if (f == "1")
                            {
                                string name = columnToDetail[appFlags[i]].appName;
                                string url  = columnToDetail[appFlags[i]].appURL;
                                if (!ret.ContainsKey(name))
                                {
                                    ret.Add(name, url);
                                }
                            }
                        }
                    }
                    return(ret);
                }
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, nameof(AppManagement), nameof(GetAllowedApps), e.Message);
                return(new Dictionary <string, string>());
            }
        }
Exemple #9
0
        /// <summary>
        /// Gets the current list of brand names.
        /// </summary>
        /// <param name="user">The user making the request.</param>
        /// <returns></returns>
        public static List <string> GetBrandNameList(User user)
        {
            List <string> bnList = new List <string>();

            try
            {
                string    sCustomer  = user.Customer.SQLEscape().ToLower();
                DBConnect connection = ConnectionsMgr.GetSalesConnection(user, "sales_" + sCustomer);
                {
                    var query = connection.Query("SELECT brand FROM sl_filters GROUP BY brand ORDER BY brand");
                    while (query.Read())
                    {
                        var bn = query.Field(0, "").Trim();
                        if (bn != "")
                        {
                            bnList.Add(bn);
                        }
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                Log(user, nameof(GetBrandNameList), e);
            }
            return(bnList);
        }
Exemple #10
0
        public static List <XrefRecord> GetXrefList(User user, FilterInfo filter)
        {
            List <XrefRecord> ret = new List <XrefRecord>();

            try
            {
                var connection = ConnectionsMgr.GetOCConnection(user, Home);
                {
                    var recCount = _GetRecordCount(user, connection, filter);
                    if (recCount > 0)
                    {
                        filter.ResultCount = recCount;
                        filter.MaxPage     = (int)(Math.Ceiling(recCount / (double)resultPageSize));
                    }

                    using (var queryCurrent = connection.Select("*", CatXref, $"WHERE {filter.ToFetchQueryString(user, searchCols, resultPageSize)}"))
                    {
                        while (queryCurrent.Read())
                        {
                            XrefRecord xr = new XrefRecord(queryCurrent);
                            ret.Add(xr);
                        }
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(GetXrefList), e.Message);
            }
            return(ret);
        }
Exemple #11
0
        public static Dictionary <string, string> GetReqTypeDict(User user)
        {
            Dictionary <string, string> ret = new Dictionary <string, string>();
            DBConnect connection            = new DBConnect();

            try
            {
                connection.Connect(ConnectionsMgr.GetAdminConnInfo());
                using (var query = connection.Select(new[] { _Column.TrxType, _Column.TrxDesc }, _Table.TrxInfo))
                {
                    while (query.Read())
                    {
                        ret.Add(query.Field(0), query.Field(1));
                    }
                }
                connection.Close();
                ret.Add("832_A", "Item Attributes");
                ret.Add("850_X", "PO Store X-Ref");
                ret.Add("856_X", "ASN Release X-Ref");
                ret.Add("832_P", "Al Tayer Item Attributes");
                return(ret);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "ProcessQueue", "GetReqTypeDict", e.Message);
                connection.Close();
                return(new Dictionary <string, string>());
            }
        }
Exemple #12
0
        public static List <PoItem> GetCurrentPoList(User user, FilterInfo filter)
        {
            List <PoItem> ret = new List <PoItem>();

            try
            {
                var connection = ConnectionsMgr.GetOCConnection(user, ECGB);
                {
                    var recCount = _GetRecordCount(user, connection, filter);
                    if (recCount > 0)
                    {
                        filter.ResultCount = recCount;
                        filter.MaxPage     = (int)(Math.Ceiling(recCount / (double)resultPageSize));
                    }

                    using (var queryCurrent = connection.Select($"{_Column.UniqueKey},{_Column.PONumber},{_Column.PODate},{_Column.ShipmentDate},{_Column.CancelDate},potype,round(totcmpinv) totcmpinv,round(totcmpasn) totcmpasn,round({_Column.TotalItems}) {_Column.TotalItems}", SrcH850, $"WHERE POStatus IN (0, 1) AND {filter.ToFetchQueryString(user, searchCols, resultPageSize)}"))
                    {
                        while (queryCurrent.Read())
                        {
                            PoItem po = new PoItem(queryCurrent);
                            ret.Add(po);
                        }
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, nameof(PdsManager), nameof(GetCurrentPoList), e.Message);
            }
            return(ret);
        }
Exemple #13
0
        private static StLookupDict GetStoreInfoLookup(User user, string storePartner, IEnumerable <string> storeList)
        {
            StLookupDict stLookup = new StLookupDict();
            string       compList = storeList.ToSqlValueList();

            try
            {
                DBConnect connection = ConnectionsMgr.GetSharedConnection(user);
                using (var querySt = connection.Select(new[] { BYId, XrefId, STName }, _Table.Stinfo, $"WHERE {Partner}='{storePartner}' AND ({BYId} IN {compList} OR TRIM({XrefId}) IN {compList})"))
                {
                    while (querySt.Read())
                    {
                        string xref   = querySt.FieldByName(XrefId);
                        string byid   = querySt.FieldByName(BYId);
                        string stname = querySt.FieldByName(STName);
                        if (!stLookup.byidDict.ContainsKey(byid))
                        {
                            stLookup.byidDict.Add(byid, stname);
                        }
                        if (!stLookup.xrefDict.ContainsKey(xref))
                        {
                            stLookup.xrefDict.Add(xref, stname);
                        }
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                Log(user, nameof(GetStoreInfoLookup), e);
            }
            return(stLookup);
        }
Exemple #14
0
        /// <summary>
        /// Gets the current list of stores.
        /// </summary>
        /// <param name="user">The user making the request.</param>
        /// <returns></returns>
        public static List <Store> GetStoreList(User user)
        {
            List <Store> stores       = new List <Store>();
            string       sPartner     = user.ActivePartner.SQLEscape();
            string       prtCondition = "";
            string       storePartner = GetStorePartner(sPartner);

            if (sPartner == _Partner.Marines)
            {
                prtCondition = "AND (LENGTH(xrefid)=3 OR LENGTH(xrefid)=5)"; //EXCLUDE 4 CHAR STORES THAT DON'T END IN 'E' OR 'W' (AKA HAVE 4 CHAR XREFID)
            }
            try
            {
                DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
                using (DBResult res = connection.Select(new[] { STName, BYId }, _Table.Stinfo, $"WHERE partner='{storePartner}' and byid!='' {prtCondition} GROUP BY byid ORDER BY byid,upddate DESC"))
                {
                    while (res.Read())
                    {
                        var s = new Store(res);
                        stores.Add(s);
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {
                Log(user, nameof(GetStoreList), e);
            }
            return(stores);
        }
Exemple #15
0
        public static List <RetailWeekData> GetRetailWeeks(User user)
        {
            string dateLatest = "";
            List <RetailWeekData> retailWeekList = new List <RetailWeekData>();

            try
            {
                var connectionSalesData = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
                {
                    var querySalesDataLatest = connectionSalesData.Select("startdate", tableSales, string.Format("WHERE {0}='{1}' ORDER BY {2} DESC LIMIT 1", _Column.Partner, user.ActivePartner.SQLEscape(), "startdate"));

                    if (querySalesDataLatest.Read())
                    {
                        DateTime temp;
                        if (!DateTime.TryParse(querySalesDataLatest.Field(0), out temp))
                        {
                            dateLatest = DateTime.Now.ToMySQLDateStr();
                        }
                        else
                        {
                            dateLatest = querySalesDataLatest.Field(0);
                        }
                    }
                    else
                    {
                        dateLatest = DateTime.Now.ToMySQLDateStr();
                    }
                }
                connectionSalesData.Close();

                var connectionAdminData = ConnectionsMgr.GetAdminConnection();
                {
                    var resultQueryWeek = connectionAdminData.Select(new[] { columnRetailWeek, columnRetailYear, columnRetailWeekStart, columnRetailWeekEnd }, _Table.RetailCalendar, string.Format("WHERE {0}<'{1}'", columnRetailWeekStart, dateLatest));

                    while (resultQueryWeek.Read())
                    {
                        var date = resultQueryWeek.Field2(2) as DateTime?;
                        if (date != null && date < DateTime.Now)
                        {
                            RetailWeekData retailWeek = new RetailWeekData();
                            retailWeek.Week      = int.Parse(resultQueryWeek.Field(0));
                            retailWeek.Year      = int.Parse(resultQueryWeek.Field(1));
                            retailWeek.WeekStart = resultQueryWeek.Field(2);
                            retailWeek.WeekEnd   = resultQueryWeek.Field(3);
                            retailWeekList.Add(retailWeek);
                        }
                    }
                }
                connectionAdminData.Close();
                retailWeekList.Sort((weekA, weekB) => weekB.CompareTo(weekA));
                return(retailWeekList);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "SalesRequestManager", "GetRetailWeeks", e.Message);
                return(new List <RetailWeekData>());
            }
        }
Exemple #16
0
        public static List <ItemInfo> GetItemData(User user)
        {
            Dictionary <string, QuantityInfo> currentDict = new Dictionary <string, QuantityInfo>();
            List <ItemInfo> _itemTable = new List <ItemInfo>();

            try
            {
                DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
                {
                    using (var reader = connection.Query(string.Format(qFetchCurrDist, user.ActivePartner.SQLEscape())))
                    {
                        while (reader.Read())
                        {
                            string       vendor = reader.Field(0, "").ToString();
                            QuantityInfo cqi    = new QuantityInfo();
                            string       min    = reader.Field(1, "0");
                            string       max    = reader.Field(2, "0");
                            string       reo    = reader.Field(3, "0");
                            cqi.Min     = (int)double.Parse(min);
                            cqi.Max     = (int)double.Parse(max);
                            cqi.Reorder = (int)double.Parse(reo);
                            currentDict.Add(vendor, cqi);
                        }
                    }

                    using (var reader = connection.Query(qFetchBaseDist))
                    {
                        while (reader.Read())
                        {
                            ItemInfo info = new ItemInfo();
                            info.Vendor      = reader.Field(0, "").ToString();
                            info.ItemUPC     = reader.Field(1, "").ToString();
                            info.Description = reader.Field(2, "").ToString();
                            info.Base        = new QuantityInfo()
                            {
                                Min     = int.Parse(reader.Field(3, "0")),
                                Max     = int.Parse(reader.Field(4, "0")),
                                Reorder = int.Parse(reader.Field(5, "0"))
                            };
                            if (currentDict.ContainsKey(info.Vendor))
                            {
                                info.Current = currentDict[info.Vendor];
                            }
                            _itemTable.Add(info);
                            ItemInfo infoCopy = info.Clone();
                        }
                    }
                }
                connection.Close();
                return(_itemTable);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "ItemTable", "GetItemData", e.Message);
                return(new List <ItemInfo>());
            }
        }
Exemple #17
0
        public static bool Submit(HttpRequest request, ReportDetail req, string usUserName, string usCustomer, string usPartner)
        {
            if (request == null || req == null)
            {
                return(false);
            }
            DBConnect connectionAdmin = new DBConnect();

            try
            {
                connectionAdmin.Connect(new DatabaseInfo(ConnectionsMgr.GetAdminConnInfo())
                {
                    Database = databaseEmailRequest
                });

                string defaultToEmail   = "*****@*****.**";
                string defaultFromEmail = "*****@*****.**";

                StringBuilder builtMessage = new StringBuilder();
                builtMessage.AppendLine("From: " + req.Name);
                builtMessage.AppendLine("Company: " + req.Company);
                builtMessage.AppendLine("Email: " + req.Email);
                builtMessage.AppendLine("Message: " + req.Message);
                builtMessage.AppendLine();
                builtMessage.AppendLine("Additional Info");
                builtMessage.AppendLine("IP Address: " + request.UserHostAddress);
                builtMessage.AppendLine("Browser Info: " + request.UserAgent);
                builtMessage.AppendLine("Referral: " + request.UrlReferrer.ToString());
                DateTime requestTime = DateTime.Now;
                var      vals        = new Dictionary <string, string>()
                {
                    { columnUniqueKey, connectionAdmin.GetNewKey() },
                    { columnCustomer, (usCustomer ?? "").SQLEscape() },
                    { columnPartner, (usPartner ?? "").SQLEscape() },
                    { columnRequestDate, requestTime.ToString("yyyy-MM-dd HH:mm:ss") },
                    { columnToEmail, defaultToEmail },
                    { columnFromEmail, defaultFromEmail },
                    { columnSubject, "EDIOC- Support Submission" },
                    { columnMessage, WrapTextTo70(builtMessage.ToString().SQLEscape()) },
#if DEBUG
                    { columnProcessed, "Y" },
#else
                    { columnProcessed, "" },
#endif
                    { columnSendAfter, requestTime.ToString("yyyy-MM-dd HH:mm:ss") }
                };
                var result = connectionAdmin.Insert(tableEmailReq, vals.ToNameValueCollection());
                return(result.AffectedRows > 0);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(usUserName, usCustomer, usPartner, "SupportRequest", "Submit", e.Message);
                return(false);
            }
        }
Exemple #18
0
        public static string GenerateReport(User user, DateTime startDate, DateTime endDate)
        {
            try
            {
                DBConnect   connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB);
                Application app        = new Application();
                app.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
                Workbook  xlWB = app.Workbooks.Add();
                Worksheet ws   = xlWB.Worksheets.Add();
                app.ActiveWindow.SplitRow    = 1;
                app.ActiveWindow.FreezePanes = true;

                //string queryReport = "SELECT sum(d.quantity) AS PidQty,d.unitprice AS GLC,d.chgprice AS MilCost,d.retailprc AS MilRetail,d.vendornum AS PID,trim(h.ponumber) AS contract,trim(h.custorder) AS PO,h.department AS Dept,h.deptname AS Brand,h.arrivdate AS INDCDate" +
                //    " FROM gdetl855 AS d JOIN ghead855 AS h ON d.uniquekey=h.uniquekey WHERE arrivdate BETWEEN '{0}' AND '{1}' GROUP BY d.vendornum,h.ponumber ORDER BY h.ponumber,d.upcnum";
                string queryReport = "SELECT sum(d.quantity) AS PidQty,d.unitprice AS GLC,d.chgprice AS MilCost,d.retailprc AS MilRetail,d.vendornum AS PID,c.COLORCODE, c.ITEMCOLOR,trim(h.ponumber) AS contract,trim(h.custorder) AS PO,h.department AS Dept,h.deptname AS Brand,h.arrivdate AS INDCDate" +
                                     " FROM gdetl855 AS d JOIN ghead855 AS h ON d.uniquekey=h.uniquekey " +
                                     " LEFT join catinfo c on d.VENDORNUM=c.VENDORNUM and d.UPCNUM = c.UPCNUM and h.PONUMBER = c.PONUMBER " +
                                     " WHERE h.arrivdate BETWEEN '{0}' AND '{1}' " +
                                     //" GROUP BY d.vendornum,h.ponumber ORDER BY h.ponumber,d.upcnum";
                                     " GROUP BY h.ponumber,d.vendornum,c.colorcode ORDER BY h.ponumber,d.vendornum,c.colorcode";
                string   formatReport = string.Format(queryReport, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                DBResult resultReport = connection.Query(formatReport);

                char   colLastLetter = (char)('A' + resultReport.FieldCount - 1);
                string rowLastIndex  = (2 + resultReport.AffectedRows - 1).ToString();

                object[,] reportData = new object[resultReport.AffectedRows, resultReport.FieldCount];
                for (int rowIndex = 0; rowIndex < resultReport.AffectedRows; rowIndex++)
                {
                    resultReport.Read();
                    for (int columnIndex = 0; columnIndex < resultReport.FieldCount; columnIndex++)
                    {
                        reportData[rowIndex, columnIndex] = resultReport.Field2(columnIndex, "'---");
                    }
                }

                var rangeHead = ws.Range["A1", colLastLetter + "1"];
                var rangeBody = ws.Range["A2", colLastLetter + rowLastIndex];
                rangeHead.Value = new[] { "PidQty", "GLC", "MilCost", "MilRetail", "PID", "Color Code", "Color Description", "Contract", "PO #", "Dept.", "Brand", "INDCDate" };
                rangeBody.Value = reportData;
                ws.Columns.AutoFit();
                string diFileName = SiteFileSystem.GetTempFileName();
                xlWB.SaveCopyAs(diFileName);
                xlWB.Close(false);
                string eoToken = Crypt.EncryptFileToFile(user, diFileName);
                File.Delete(diFileName);
                return(eoToken);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "GVMReport", "GenerateReport", e.Message);
                return("");
            }
        }
Exemple #19
0
        private static ShipReport _GenShipReport(User user, string store, List <RetailWeekData> retailWeeks)
        {
            ShipReport shipReport = new ShipReport();

            shipReport.Store     = store.SQLEscape();
            shipReport.Shipments = new List <ShipReportData>();

            var queryShipInfo = string.Format("SELECT {0},{1},IFNULL({2},'')FROM {3}", _Column.UniqueKey, _Column.ShipmentDate, _Column.BOLNumber, _Table.PHead850) +
                                string.Format(" WHERE {0}='{1}' AND {2}='{3}' AND({4})", _Column.Partner, user.ActivePartner.SQLEscape(), _Column.STId, shipReport.Store, string.Join("OR", from week in retailWeeks select string.Format("({0}>='{1}' AND {0}<='{2}')", _Column.ShipmentDate, week.WeekStart.SQLEscape(), week.WeekEnd.SQLEscape())));
            var queryQtyInfo = string.Format("SELECT {0},{1},IFNULL({2},0) FROM {3}", _Column.VendorNum, _Column.UPCNum, _Column.Quantity, _Table.PDetl850);

            try
            {
                var connectionSalesData = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
                var result = connectionSalesData.Query(queryShipInfo);

                while (result.Read())
                {
                    ShipReportData srd     = new ShipReportData();
                    string         keyShip = result.Field(0, "");
                    srd.ShipDate       = ((DateTime)result.Field2(1)).ToString("MMM dd, yyyy");
                    srd.TrackingNumber = result.Field(2, "").Trim();
                    srd.Quantity       = 0;
                    srd.Items          = new List <ShipItemInfo>();
                    if (keyShip != "")
                    {
                        var resultQuantity = connectionSalesData.Query(queryQtyInfo + string.Format(" WHERE {0}='{1}'", _Column.UniqueKey, keyShip));
                        while (resultQuantity.Read())
                        {
                            ShipItemInfo itemInfo = new ShipItemInfo();
                            itemInfo.VendorNum = resultQuantity.Field(0);
                            itemInfo.UPCNum    = resultQuantity.Field(1);
                            itemInfo.Quantity  = (int)decimal.Parse(resultQuantity.Field(2));
                            srd.Items.Add(itemInfo);
                        }
                        if (srd.Items.Count > 0)
                        {
                            srd.Quantity = srd.Items.Sum(item => item.Quantity);
                        }
                    }
                    shipReport.Shipments.Add(srd);
                }
                connectionSalesData.Close();
                return(shipReport);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "SalesRequestManager", "_GenShipReport", e.Message);
                return(shipReport);
            }
        }
Exemple #20
0
        public static void EditXref(User user, List <XrefRecord> updateRecs)
        {
            try
            {
                DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
                {
                    List <string> updateCols = new List <string>()
                    {
                        UniqueKey,
                        VendorName,
                        VendorId,
                        VendorSeq,
                        BrandName,
                        Customer,
                        Partner
                    };

                    List <string> replaceCols = new List <string>()
                    {
                        string.Format("{0}=VALUES({0})", VendorName),
                        string.Format("{0}=VALUES({0})", VendorId),
                        string.Format("{0}=VALUES({0})", VendorSeq),
                        string.Format("{0}=VALUES({0})", BrandName),
                    };

                    List <string> updateVals = new List <string>();

                    foreach (var record in updateRecs)
                    {
                        List <string> recVals = new List <string>();
                        recVals.Add(record.Key);
                        recVals.Add(record.CompanyName.Truncate(60).SQLEscape());
                        recVals.Add(record.GXSAccount.Truncate(15).SQLEscape());
                        recVals.Add(record.SelectionCode.Truncate(3).SQLEscape());
                        recVals.Add(record.BrandName.Truncate(80).SQLEscape());
                        recVals.Add(user.Customer.SQLEscape());
                        recVals.Add(user.ActivePartner.SQLEscape());
                        updateVals.Add(recVals.ToSqlValueList());
                    }

                    connection.Query(string.Format("INSERT INTO {0} ({1}) VALUES {2} ON DUPLICATE KEY UPDATE {3}", CatXref, updateCols.ToSqlColumnList(), string.Join(",", updateVals), string.Join(",", replaceCols)));
                }
                connection.Close();
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(EditXref), e.Message);
            }
        }
Exemple #21
0
 public static void RemoveXref(User user, List <string> keyList)
 {
     try
     {
         DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
         {
             connection.Delete(CatXref, $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}");
         }
         connection.Close();
     }
     catch (Exception e)
     {
         ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(RemoveXref), e.Message);
     }
 }
Exemple #22
0
 public static PdsOptions GetOptions(User user)
 {
     try
     {
         DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
         var       opt        = _GetOptions(user, connection);
         connection.Close();
         return(opt);
     }
     catch (Exception e)
     {
         ProgramLog.LogError(user, nameof(PdsManager), nameof(GetOptions), e.Message);
         return(new PdsOptions());
     }
 }
Exemple #23
0
        public static List <CPOSummaryHead> GetChangeList(User user)
        {
            List <CPOSummaryHead> ret        = new List <CPOSummaryHead>();
            DBConnect             connection = new DBConnect();

            try
            {
                connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB));
                var queryCPOHead = connection.Select(new[] { _Column.UniqueKey, _Column.PONumber, _Column.POChangeDate, _Column.Purpose, _Column.TotalItems, _Column.HProcessed }, _Table.Head860, string.Format("WHERE {0}='{1}' AND {2}='{3}' AND ({4}='{5}' OR {4}='{6}')", _Column.Customer, user.Customer, _Column.Partner, user.ActivePartner, _Column.HProcessed, _ProgressFlag.Unprocessed, _ProgressFlag.Error));

                while (queryCPOHead.Read())
                {
                    CPOSummaryHead newHead = new CPOSummaryHead();
                    newHead.UniqueKey    = queryCPOHead.Field(0, "");
                    newHead.PONumber     = queryCPOHead.Field(1, "");
                    newHead.POChangeDate = queryCPOHead.Field(2, "");
                    newHead.Purpose      = ElementLookup.GetDesc(user, _Element.Purpose, queryCPOHead.Field(3, ""));
                    newHead.Affected     = queryCPOHead.Field(4, "");
                    newHead.Status       = queryCPOHead.Field(5, "");
                    newHead.Details      = new List <CPOSummaryDetail>();
                    var queryCPODetail = connection.Select(new[] { _Column.ChangeType, _Column.Quantity, _Column.ChangeQuantity, _Column.UnitPrice, _Column.RetailPrice, _Column.UPCNum, _Column.VendorNum, _Column.ItemDesc, _Column.PackSize, _Column.Dropship, _Column.Processed }, _Table.Detail860, string.Format("WHERE {0}='{1}'", _Column.UniqueKey, newHead.UniqueKey));
                    while (queryCPODetail.Read())
                    {
                        CPOSummaryDetail newDetail = new CPOSummaryDetail();
                        newDetail.ChangeType     = ElementLookup.GetDesc(user, _Element.ChangeType, queryCPODetail.Field(0, ""));
                        newDetail.Quantity       = decimal.Parse(queryCPODetail.Field(1, "0")).ToString("N0");
                        newDetail.ChangeQuantity = decimal.Parse(queryCPODetail.Field(2, "0")).ToString("N0");
                        newDetail.UnitPrice      = queryCPODetail.Field(3, "");
                        newDetail.RetailPrc      = queryCPODetail.Field(4, "");
                        newDetail.UPC            = queryCPODetail.Field(5, "");
                        newDetail.VendorNum      = queryCPODetail.Field(6, "");
                        newDetail.ItemDesc       = queryCPODetail.Field(7, "");
                        newDetail.PackSize       = queryCPODetail.Field(8, "");
                        newDetail.Dropship       = queryCPODetail.Field(9, "").Replace('\r', ' ');
                        newDetail.Status         = queryCPODetail.Field(10, "");
                        newHead.Details.Add(newDetail);
                    }
                    ret.Add(newHead);
                }
                return(ret.OrderBy(h => h.POChangeDate).ThenBy(h => h.PONumber).ThenBy(h => h.Purpose).ToList());
            }
            catch (Exception e)
            {
                connection.Close();
                ProgramLog.LogError(user, "ChangePOTracker", "GetChangeList", e.Message);
                return(new List <CPOSummaryHead>());
            }
        }
Exemple #24
0
        public static OCUserInfo GetOCRecord(string usToken)
        {
            OCUserInfo info = new OCUserInfo();

            if (string.IsNullOrEmpty(usToken))
            {
                return(info);
            }
            string    sToken     = usToken.SQLEscape();
            DBConnect connection = new DBConnect();
            DateTime  expTime    = new DateTime();

            try
            {
                connection.Connect(ConnectionsMgr.GetAdminConnInfo());
                using (var queryUserAuthInfo = connection.Select(new[] { columnUserName, columnActivePartner, columnIsTest, columnCreateDate }, tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken)))
                {
                    if (queryUserAuthInfo.AffectedRows <= 0)
                    {
                        connection.Close();
                        return(info);
                    }
                    queryUserAuthInfo.Read();
                    info.UserName      = queryUserAuthInfo.Field(0);
                    info.ActivePartner = queryUserAuthInfo.Field(1);
                    info.IsTest        = queryUserAuthInfo.Field(2) == "1";
                    expTime            = (DateTime)queryUserAuthInfo.Field2(3, DateTime.MinValue);
                    connection.Delete(tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken));
                }
                connection.Close();
                var authLimit = DateTime.Now.AddSeconds(-OCSessionTimeOutSeconds);
                if (expTime >= authLimit) //expire date must be within the last OCSessionTimeOutSeconds seconds
                {
                    info.IsValid = true;
                }
                else
                {
                    throw new Exception("Authentication time is not within range allowed. Auth Time: " + expTime.ToString("yyyy-MM-dd HH:mm:ss") + ", Auth Limit: " + authLimit.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                return(info);
            }
            catch (Exception ex)
            {
                ProgramLog.LogError(null, "Auth", "GetOCRecord", ex.Message);
                connection.Close();
                return(info);
            }
        }
Exemple #25
0
        private static ResponseType Apply860(User user, string sHeadUniqueKey860)
        {
            try
            {
                DBConnect connection      = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB);
                string    filterUniqueKey = string.Format("WHERE {0}='{1}'", _Column.UniqueKey, sHeadUniqueKey860);
                var       resultCheckHead = connection.Select(new[] { _Column.PONumber, _Column.Purpose }, _Table.Head860, filterUniqueKey);
                if (resultCheckHead.AffectedRows == 0)
                {
                    return(ResponseType.ErrorCPOCouldNotApplyItemChange);
                }
                resultCheckHead.Read();
                string sPONumber = resultCheckHead.Field(0, "");
                string sPurpose  = resultCheckHead.Field(1, "");

                ResponseType resultOperation = ResponseType.ErrorCPOUnknown;

                switch (sPurpose)
                {
                case Code353.Cancel:
                    resultOperation = CancelPO(connection, user, sPONumber);
                    break;

                case Code353.Change:
                    resultOperation = ChangePO(connection, user, sPONumber, sHeadUniqueKey860);
                    break;

                default:
                    resultOperation = ResponseType.ErrorCPOPurposeUnrecognized;
                    break;
                }
                if (resultOperation == ResponseType.SuccessCPO)
                {
                    UpdateHead860ProcFlag(connection, sHeadUniqueKey860, _ProgressFlag.Success);
                }
                else
                {
                    UpdateHead860ProcFlag(connection, sHeadUniqueKey860, _ProgressFlag.Error);
                }
                return(resultOperation);
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "ChangePOTracker", "Apply860", e.Message);
                return(ResponseType.ErrorCPOUnknown);
            }
        }
Exemple #26
0
        private static ResponseType Cancel860(User user, string sHeadUniqueKey860)
        {
            DBConnect connection = new DBConnect();

            try
            {
                connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB));
                UpdateAll860ProcFlags(connection, sHeadUniqueKey860, _ProgressFlag.Canceled);
                connection.Close();
                return(ResponseType.SuccessCPO);
            }
            catch (Exception e)
            {
                connection.Close();
                ProgramLog.LogError(user, "ChangePOTracker", "Cancel860", e.Message);
                return(ResponseType.ErrorCPOUnknown);
            }
        }
Exemple #27
0
        protected void SetDevelopmentInfo(User user)
        {
            DatabaseInfo  di      = null;
            string        sFormat = "{1} ({0})";
            List <string> conns   = new List <string>();

            di = ConnectionsMgr.GetOCConnInfo(user);
            conns.Add(string.Format(sFormat, di.Port, di.Id));
            di = ConnectionsMgr.GetNPConnInfo(user);
            conns.Add(string.Format(sFormat, di.Port, di.Id));
            di = ConnectionsMgr.GetSHConnInfo(user);
            conns.Add(string.Format(sFormat, di.Port, di.Id));
            di = ConnectionsMgr.GetSLConnInfo(user);
            conns.Add(string.Format(sFormat, di.Port, di.Id));

            lblDeNotice.Text    = string.Format("Development Environment: {0}", string.Join(", ", conns));
            divDeNotice.Visible = true;
        }
Exemple #28
0
        public static List <ReportDesc> GetReportList(User user, List <string> keyList)
        {
            List <ReportDesc> repList   = new List <ReportDesc>();
            List <string>     polist    = new List <string>();
            string            sCustomer = user.Customer.SQLEscape();
            string            sPartner  = user.ActivePartner.SQLEscape();
            string            prefix    = $"trx/{user.OCConnID}/downloads/";

            if (keyList.Count == 0)
            {
                return(repList);
            }
            try
            {
                var connection = ConnectionsMgr.GetOCConnection(user);

                using (var queryPoNum = connection.Select(PONumber, $"{ECGB}.{SrcH850}", $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}"))
                {
                    while (queryPoNum.Read())
                    {
                        polist.Add(queryPoNum.Field(0));
                    }
                }
                if (polist.Count <= 0)
                {
                    return(repList);
                }
                string filter = $"WHERE {Customer}='{sCustomer}' AND {Partner}='{sPartner}' AND {PONumber} IN {polist.ToSqlValueList()}";

                using (var queryReport = connection.Select("*", $"{GrpAdmin}.{Reports}", filter))
                {
                    while (queryReport.Read())
                    {
                        repList.Add(new ReportDesc(queryReport, prefix));
                    }
                }
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, nameof(PdsManager), nameof(GetReportList), e.Message);
            }
            return(repList);
        }
Exemple #29
0
        private static void _Log(string username, string customer, string partner, string source, string method, string type, string message)
        {
            if (customer == "")
            {
                customer = "EDIO";
            }
            if (partner == "")
            {
                partner = "EDIO";
            }
            string sCustomer = TruncateAndEscape(customer, 4);
            string sPartner  = TruncateAndEscape(partner, 4);
            string sUsername = TruncateAndEscape(username, 40);
            string sSource   = TruncateAndEscape(source, 32);
            string sMethod   = TruncateAndEscape(method, 32);
            string sMessage  = (string.IsNullOrWhiteSpace(message) ? "" : message).SQLEscape();
            bool   isTest    = false;

#if DEBUG
            isTest = true;
#endif
            var connection = new DBConnect();
            try
            {
                connection.Connect(ConnectionsMgr.GetAdminConnInfo());
                Dictionary <string, string> insertVals = new Dictionary <string, string>()
                {
                    { _Column.UniqueKey, connection.GetNewKey() },
                    { columnLogTime, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") },
                    { columnSource, sSource },
                    { columnMethod, sMethod },
                    { columnStatus, type },
                    { columnMessage, sMessage },
                    { _Column.UserName, sUsername },
                    { _Column.Customer, sCustomer },
                    { _Column.Partner, sPartner },
                    { columnIsTest, isTest ? "1" : "0" }
                };
                connection.Insert(tableLog, insertVals.ToNameValueCollection());
            }
            catch { }
            connection.Close();
        }
Exemple #30
0
        private static string _GetElemDesc(User user, string usEleNum, string usCode)
        {
            DBConnect connect = new DBConnect();

            try
            {
                connect.Connect(ConnectionsMgr.GetAdminConnInfo());
                var queryCodes = connect.Select(_Column.CodeDesc, _Table.DisaCode, string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.EleNum, usEleNum.SQLEscape(), _Column.Code, usCode.SQLEscape()));
                if (queryCodes.Read())
                {
                    return(queryCodes.Field(0));
                }
                else
                {
                    return("");
                }
            }
            catch (Exception e)
            {
                ProgramLog.LogError(user, "ElementLookup", "_GetElemDesc", e.Message);
                return("");
            }
        }