Example #1
0
        private static LocPrtSpecific GetPartnerProperties(string sPartner)
        {
            LocPrtSpecific p = new LocPrtSpecific();

            p.StoreWhere = "s.byid";
            switch (sPartner)
            {
            case _Partner.AAFES:
                p.BuyerName = "d.aafesbuyer";
                p.StoreJoin = "d856.byid=" + p.StoreWhere;
                break;

            case _Partner.Navy:
                p.BuyerName = "d.nexbuyer";
                p.StoreJoin = "substr(lpad(replace(replace(d856.byid,'E',''),'W',''),4,'0'),2,3)=" + p.StoreWhere;
                break;

            case _Partner.Marines:
                p.BuyerName = "d.mcxbuyer";
                p.StoreJoin = "if(LENGTH(trim(d856.byid))>4,s.byid=d856.byid, trim(s.xrefid)=substr(trim(LEADING '0' from d856.byid),1,3))";     //WE ARE EXLUDING 4 CHAR STORES THAT DON'T END IN E/W
                break;

            default:
                p.BuyerName = "d.mcxbuyer";
                p.StoreJoin = "d856.byid=" + p.StoreWhere;
                break;
            }
            return(p);
        }
Example #2
0
        private static string GetConditionDetail(LocPrtSpecific prtInfo, LocationReportDetails reportDetails, string partner)
        {
            string conditionDetail = "1";

            if (!string.IsNullOrWhiteSpace(reportDetails.BOLQuery))
            {
                if (partner == _Partner.Anastasia)
                {
                    conditionDetail += string.Format(" AND h810.bolnumber='{0}'", reportDetails.BOLQuery.Trim().SQLEscape());
                }
                else
                {
                    conditionDetail += string.Format(" AND h856.bolnumber='{0}'", reportDetails.BOLQuery.Trim().SQLEscape());
                }
            }

            if (!string.IsNullOrWhiteSpace(reportDetails.POQuery))
            {
                if (partner == _Partner.Anastasia)
                {
                    conditionDetail += string.Format(" AND h810.ponumber='{0}'", reportDetails.POQuery.Trim().SQLEscape());
                }
                else
                {
                    conditionDetail += string.Format(" AND d856.ponumber='{0}'", reportDetails.POQuery.Trim().SQLEscape());
                }
            }

            if (reportDetails.Stores.Count > 0)
            {
                conditionDetail += string.Format(" AND {0} IN {1}", prtInfo.StoreWhere, reportDetails.Stores.ToSqlValueList());
            }

            if (partner != _Partner.Anastasia && reportDetails.BrandNames.Count > 0)
            {
                conditionDetail += string.Format(" AND IFNULL(c.branddesc,d.deptname) IN {0}", reportDetails.BrandNames.ToSqlValueList());
            }

            if (partner != _Partner.Anastasia && reportDetails.AENames.Count > 0)
            {
                conditionDetail += string.Format(" AND d.aename IN {0}", reportDetails.AENames.ToSqlValueList());
            }

            return(conditionDetail);
        }
Example #3
0
        private static string GenASNReportGeneral(User user, LocationReportDetails reportDetails)
        {
            try
            {
                string sPartner       = user.ActivePartner.SQLEscape();
                string reportTemplate = "SELECT d856.ponumber AS `Mil PO`," +
                                        "gh855.custodate AS `Mil PO Date`," +
                                        "UPPER(DATE_FORMAT(gh855.ARRIVDATE,'%b-%Y')) AS `DC Month`," +
                                        "d856.shipnum AS `ASN #`," +
                                        "TRIM(gd855.vendornum) AS `PID Style`," +
                                        "TRIM(d856.upcnum) AS `UPC #`," +
                                        "TRIM(c.colorcode) AS `Color Code`," +
                                        "TRIM(c.itemcolor) AS `Color Decription`," +
                                        "d856.cartid AS `Carton ID`," +
                                        "TRIM(d.category) AS `Category`," +
                                        "TRIM(IFNULL(c.branddesc,d.deptname)) AS `Brand Name`," +
                                        "TRIM(c.scategory) AS `Sub Category`," +
                                        "TRIM(c.prodclass) AS `Class`," +
                                        "TRIM(c.sprodclass) AS `Sub Class`," +
                                        "TRIM(c.itemdesc) AS `Description`," +
                                        "ROUND(gd855.unitprice * SUM(d856.shpunits),2) AS `GLC`," +
                                        "ROUND(gd855.chgprice * SUM(d856.shpunits),2) AS `Mil Cost`," +
                                        "ROUND(gd855.retailprc * SUM(d856.shpunits),2) AS `Mil Retail`," +
                                        "gh855.fobtype AS `East/West`," +
                                        "d856.byid AS `Store #`," +
                                        "s.stname AS `Store Name`," +
                                        "h856.shipdate AS `Ship Date`," +
                                        "ROUND(SUM(d856.ordunits)) AS `On Order`," +
                                        "ROUND(SUM(d856.shpunits)) AS `Shipped`," +
                                        "gh855.department AS `MMG Dept.`," +
                                        "{0} AS `Buyer Name`," +
                                        "d.aename AS `Account Executive`" +
                                        //" FROM(SELECT uniquekey,shipdate,bolnumber FROM ecgb.head856 WHERE partner='{1}' AND shipdate>='{2}' AND shipdate<='{3}') AS h856" +
                                        //" JOIN ecgb.detl856 AS d856 ON h856.uniquekey=d856.uniquekey" +
                                        " FROM(SELECT uniquekey,shipdate,bolnumber FROM mrsk3pl.head856 WHERE partner IN {1} AND shipdate>='{2}' AND shipdate<='{3}') AS h856" +
                                        " JOIN mrsk3pl.detl856 AS d856 ON h856.uniquekey=d856.uniquekey and !isnull(d856.invoiceno)" +
                                        GetJoinThla(user.ActivePartner) +
                                        " JOIN ecgb.ghead855 AS gh855 ON gh855.custorder=d856.ponumber" +
                                        " LEFT JOIN temp.xdept AS d ON d.department=gh855.department" +
                                        " LEFT JOIN ecgb.gdetl855 AS gd855 ON gd855.uniquekey=gh855.uniquekey" +
                                        " AND gd855.upcnum=d856.upcnum" +
                                        //" AND gd855.vendornum=d856.vendornum" + /* UPDATE FROM NEIL: We don't need to match Vendornum here since we're using ponumber + upcnum already */
                                        " LEFT JOIN ecgb.catinfo AS c ON c.ponumber = gh855.ponumber" +
                                        " AND c.upcnum=gd855.upcnum" +
                                        " AND c.vendornum=gd855.vendornum" +
                                        " LEFT JOIN temp.xstore AS s ON {4}" +
                                        " WHERE " + GetConditionThla(user.ActivePartner) + " AND {5}" +
                                        " GROUP BY h856.shipdate,d856.shipnum,d856.upcnum,d856.cartid,d856.ponumber,d856.podate,gd855.vendornum,gd855.itemdesc,d856.byid,d.aename,{0},gh855.department;";
                LocPrtSpecific prtInfo = GetPartnerProperties(sPartner);

                string conditionDetail = GetConditionDetail(prtInfo, reportDetails, user.ActivePartner);

                string formatReport = string.Format(reportTemplate, prtInfo.BuyerName, GetPartnerQueryStr(user).ToSqlValueList(), reportDetails.StartDate.ToString("yyyy-MM-dd"), reportDetails.EndDate.ToString("yyyy-MM-dd"), prtInfo.StoreJoin, conditionDetail);
                //string formatReport = string.Format(reportTemplate, prtInfo.BuyerName, user.ActivePartner.SQLEscape(), reportDetails.StartDate.ToString("yyyy-MM-dd"), reportDetails.EndDate.ToString("yyyy-MM-dd"), prtInfo.StoreJoin, conditionDetail);

                var header = new[] { "Mil PO", "Mil PO Date", "DC Month", "ASN #", "PID Style", "UPC #", "Color Code", "Color Description", "Carton ID", "Category", "Brand Name", "Sub Category", "Class", "Sub Class", "Description", "GLC", "Mil Cost", "Mil Retail", "East/West", "Store #", "Store Name", "Ship Date", "On Order", "Shipped", "MMG Dept.", "Buyer Name", "Account Executive" };

                int colCount = 0;
                int rowCount = 0;
                object[,] reportData = null;

                DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB);
                if (!CreateTempStoreTable(user, connection, reportDetails))
                {
                    return("");
                }
                if (!CreateTempDeptTable(user, connection))
                {
                    return("");
                }

                using (var resultReport = connection.Query(formatReport))
                {
                    colCount   = resultReport.FieldCount;
                    rowCount   = resultReport.AffectedRows;
                    reportData = CopyResultDataToArray(resultReport);
                }

                return(SaveReportToExcelFile(user, header, reportData, rowCount, colCount, new[] { "P:P", "Q:Q", "R:R" }));
            }
            catch (Exception e)
            {
                Log(user, nameof(GenASNReportGeneral), e);
                return("");
            }
        }
Example #4
0
        private static string GenLocationReportANAS(User user, LocationReportDetails reportDetails)
        {
            try
            {
                string sCustomer      = user.Customer.SQLEscape();
                string sPartner       = user.ActivePartner.SQLEscape();
                string reportTemplate = "SELECT shipmonth AS `Ship Month`," +
                                        "shipdate AS `Ship Date`," +
                                        "ponumber AS `PO #`," +
                                        "podate AS `PO Date`," +
                                        "canceldate AS `Cancel Date`," +
                                        "stid AS `STID`," +
                                        "stname AS `Ship To Name`," +
                                        "SUM(quantity) AS `Quantity`," +
                                        "vendornum AS `Vendor #`," +
                                        "upcnum AS `UPC #`," +
                                        "itemdesc AS `Item Description`," +
                                        "SUM(CAST(unitprice AS DECIMAL(14,2))) AS `Unit Price`," +
                                        "SUM(CAST(lineprice AS DECIMAL(14,2))) AS `Extended Price`" +
                                        "FROM" +
                                        "(SELECT DATE_FORMAT(h810.shipdate,'%M %Y') AS shipmonth," +
                                        "h810.shipdate AS shipdate," +
                                        "h810.ponumber AS ponumber," +
                                        "h810.podate AS podate," +
                                        "h850.canceldate AS canceldate," +
                                        "h810.stid AS stid," +
                                        "s.stname AS stname," +
                                        "ROUND(d810.quantity) AS quantity," +
                                        "TRIM(d810.vendornum) AS vendornum," +
                                        "d810.upcnum AS upcnum," +
                                        "d850.itemdesc AS itemdesc," +
                                        "d810.unitprice AS unitprice," +
                                        "d810.unitprice * d810.quantity AS lineprice" +
                                        " FROM (SELECT uniquekey,uniquepo,shipdate,ponumber,podate,stid,byid FROM ecgb.trxh810 WHERE customer='{0}' AND partner='ANAS' AND shipdate>='{1}' AND shipdate<='{2}') AS h810" +
                                        " JOIN ecgb.trxd810 AS d810 ON h810.uniquekey=d810.uniquekey AND !isnull(d810.invoiceno)" +
                                        " LEFT JOIN srch850 AS h850 ON h810.uniquepo=h850.uniquekey" +
                                        " LEFT JOIN srcd850 AS d850 ON h850.uniquekey=d850.uniquekey AND d810.uniqpoitem=d850.uniqueitem" +
                                        " LEFT JOIN temp.xstore AS s ON h810.byid=s.byid" +
                                        " WHERE {3}" +
                                        " GROUP BY ponumber,vendornum,upcnum,h810.byid,shipdate)" +
                                        " AS st GROUP BY ponumber,vendornum,upcnum,shipdate;";

                LocPrtSpecific prtInfo = GetPartnerProperties(sPartner);

                string conditionDetail = GetConditionDetail(prtInfo, reportDetails, sPartner);

                string formatReport = string.Format(reportTemplate, sCustomer, reportDetails.StartDate.ToMySQLDateStr(), reportDetails.EndDate.ToMySQLDateStr(), conditionDetail);

                var header = new[] { "Ship Month", "Ship Date", "PO #", "PO Date", "Cancel Date", "STID", "Ship To Name", "Quantity", "Vendor #", "UPC #", "Item Description", "Unit Price", "Extended Price" };

                int colCount = 0;
                int rowCount = 0;
                object[,] reportData = null;

                List <List <string> > storeNameData = new List <List <string> >();
                {
                    string condStore = "";
                    if (reportDetails.Stores.Count > 0)
                    {
                        condStore = string.Format("AND byid IN {0}", reportDetails.Stores.ToSqlValueList());
                    }

                    DBConnect connectionSHR = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
                    using (var queryStore = connectionSHR.Select("byid,stname", _Table.Stinfo, string.Format("WHERE partner='NAVY' {0} GROUP BY byid", condStore)))
                    {
                        while (queryStore.Read())
                        {
                            storeNameData.Add(new List <string>()
                            {
                                queryStore.Field(0), queryStore.Field(1)
                            });
                        }
                    }
                    connectionSHR.Close();
                }

                DBConnect connection = ConnectionsMgr.GetOCConnection(user, _Database.ECGB);

                if (!CreateTempStoreTableFromData(user, connection, storeNameData))
                {
                    return("");
                }

                using (var resultReport = connection.Query(formatReport))
                {
                    colCount   = resultReport.FieldCount;
                    rowCount   = resultReport.AffectedRows;
                    reportData = CopyResultDataToArray(resultReport);
                }

                return(SaveReportToExcelFile(user, header, reportData, rowCount, colCount, new[] { "L:L", "M:M" }));
            }
            catch (Exception e)
            {
                Log(user, nameof(GenLocationReportANAS), e);
                return("");
            }
        }