PKPL

data.cs

using System.Collections.Generic;
using System.Data;
using ServiceStack;
using ServiceStack.OrmLite;
using ServiceStack.DataAnnotations;
using System;

 [Alias("WarehouseTransferDetails")]
public class WarehouseTransferDetail
    {
        [AutoIncrement]
        public int WarehouseTransferDetailId { get; set; }
        public int WarehouseTransferId { get; set; }
        public int WarehouseTransactionId { get; set; }
        public int ProductId { get; set; }
        public int Quantity { get; set; }
        public string BatchNumber { get; set; }
        public DateTime Created { get; set; }
    }
 [Alias("Products")]
public class Product
    {
        [AutoIncrement]
        public int ProductId { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int Quantity { get; set; }
        public string Unit { get; set; }
        public string Note { get; set; }
        public int ProductCategoryId { get; set; }
        public ProductType Type { get; set; }
        public bool ForLaser { get; set; }
        public string ActiveSubstance { get; set; }
        public string Specifications { get; set; }
        public string Use { get; set; }
        public string Image { get; set; }
        public string Description { get; set; }
        public short MinQuantity { get; set; }
        public short MaxQuantity { get; set; }
        public string Feature { get; set; }
        public bool Disabled { get; set; }
        public string UserName { get; set; }
    }
 [Alias("WarehouseTransfers")]
 public class WarehouseTransfer
    {
        [AutoIncrement]
        public int WarehouseTransferId { get; set; }
        public DateTime Created { get; set; }
        public string UserId { get; set; }
        public string UserName { get; set; }
        public string UserConfirmId { get; set; }
        public string UserConfirmName { get; set; }
        public WarehouseTransferType Type { get; set; }
        public WarehouseTransferStatus Status { get; set; }
    }
 [Alias("Lasers")]
    public class Laser
    {
        [AutoIncrement, PrimaryKey]
        public int LaserId { get; set; }
        public DateTime Created { get; set; }
        public string Code { get; set; }
        public DateTime? AnesthesiaAt { get; set; }
        public DateTime? StartAt { get; set; }
        public short Wait { get; set; }
        public string Note { get; set; }
        public decimal Total { get; set; }
        public bool IsControl { get; set; }
        public bool IsCallBack { get; set; }
        public bool IsFree { get; set; }
        public bool IsSupport { get; set; }
        public bool PL { get; set; }
        public string DrugDrink { get; set; }
        public Status Status { get; set; }
        public FeeStatus FeeStatus { get; set; }
        public string LaserConfig { get; set; }
        public int CustomerId { get; set; }
        public int AppointmentId { get; set; }
        public string AppointmentCode { get; set; }
        public string UserId { get; set; }
        public string UserName { get; set; }
        public string UserIdAnesthesia { get; set; }
        public string UserNameAnesthesia { get; set; }
        public string UserIdFee { get; set; }
        public string UserNameFee { get; set; }
        public string UserIdDrink { get; set; }
        public string UserNameDrink { get; set; }
        public string CancelUserName { get; set; }
        public string CancelReason { get; set; }
        public DateTime? CancelCreated { get; set; }
        [StringLength(300)]
        public string JustPhenomenon { get; set; }
        [StringLength(300)]
        public string Phenomenon { get; set; }
        [StringLength(500)]
        public string Feature { get; set; }
        public string PatientComplain { get; set; }
        public string EmployeeComplain { get; set; }
        public decimal TotalPaid { get; set; }
        public int LaserRoomId { get; set; }
        
    }
 [Alias("Customers")]
    public class Customer
    {
        [AutoIncrement,PrimaryKey]
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Code { get; set; }
        public string IdentityCard { get; set; }
        public string Phone { get; set; }
        public string Phone1 { get; set; }
        public string Phone2 { get; set; }
        public string PhoneOwned { get; set; }
        public string Address { get; set; }
        public string AddressByIdentity { get; set; }
        public string Email { get; set; }
        public short Birthday { get; set; }
        public Sex Sex { get; set; }
        public string Religion { get; set; }
        public string CareerName { get; set; }
        public string ProvinceName { get; set; }
        public FamilyStatus FamilyStatus { get; set; }
        public string Note { get; set; }
        public DateTime Created { get; set; }
        public string Avatar { get; set; }
        public bool BodySmell { get; set; }
        public bool Vip { get; set; }
        public string Keyword { get; set; }
        public decimal LimitExpense { get; set; }

    }
 [Alias("Tests")]
    public class Test
    {
        [AutoIncrement]
        public int TestId { get; set; }
        public string Title { get; set; }
        public DateTime Created { get; set; }
        public bool IsFree { get; set; }
        public bool PL { get; set; }
        public string Code { get; set; }
        public string ReferenceCode { get; set; }
        public bool IsSentResult { get; set; }
        public DateTime? ResultCreated { get; set; }
        public decimal Total { get; set; }
        public decimal TotalFree { get; set; }
       
        public int CustomerId { get; set; }
        public int AppointmentId { get; set; }
        public string Raw { get; set; }
        public string UserId { get; set; }
        public string UserName { get; set; }
        public string DoctorId { get; set; }
        public string DoctorName { get; set; }
        public string UserFeeId { get; set; }
        public string UserFeeName { get; set; }
        public string UserTakeBloodName { get; set; }
        public string UserCancelId { get; set; }
        public string UserCancelName { get; set; }
        public string CancelReason { get; set; }
        public TestStatus Status { get; set; }
        public FeeStatus FeeStatus { get; set; }
        public string UserNameImportCode { get; set; }
    }
 [Alias("PharmacyInventories")]
    public class PharmacyInventory
    {
        [AutoIncrement]
        public int PharmacyInventoryId { get; set; }
        public int ProductId { get; set; }
        public int Quantity { get; set; }
    }
 [Alias("OrderDetails")]
    public class OrderDetail
    {
        [AutoIncrement]
        public int OrderDetailId { get; set; }
        public int ProductId { get; set; }
        public short Quantity { get; set; }
        public decimal Price { get; set; }
        public bool IsFree { get; set; }
        public int OrderId { get; set; }
        public decimal Total { get; set; }
        public DateTime Created { get; set; }
        public OrderType Type { get; set; }

        public string UserId { get; set; }
        public string UserName { get; set; }
    }
public class LaserMapping : Laser
    {
      

        public string Name { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public string Sex { get; set; }
        public short Birthday { get; set; }
        public string ProvinceName { get; set; }
        public string CareerName { get; set; }
        public bool BodySmell { get; set; }
        public bool Vip { get; set; }

        /// <summary>
        /// Có xét nghiệm máu không
        /// </summary>
        public bool HasTest { get; set; }
    }
    public class OrderDetailMapping : OrderDetail
    {
        public string Name { get; set; }
        public string Code { get; set; }
        public string ActiveSubstance { get; set; }
        public string Unit { get; set; }
        public int TotalAvailableQuantity { get; set; }
        public string UserFee { get; set; }
        public string UserCheck { get; set; }
        public string UserExport { get; set; }
    }

enums.cs

using System;
using System.Collections.Generic;
using ServiceStack.DataAnnotations;
  [EnumAsInt]
    public enum WarehouseTransferType : short
    {
        Normal = 1,
        Quick = 2
    }
     [EnumAsInt]
    public enum TestStatus : short {
        Waiting = 1,
        TakeBlood = 2,
        Cancel = 3,
        HasResult = 4,
        Finish = 5
    }

    [EnumAsInt]
    public enum WarehouseTransferStatus : short
    {
        Pending = 0,
        Confirmed = 1
    }
  [EnumAsInt]
    public enum OrderType : short
    {
        Retail = 1, //toa bán lẻ
        ByPrescription = 2, //toa bs kê
        Refurn = 3, //tra thuoc
        TakeDrug = 4
    }

    [EnumAsInt]
    public enum Sex : short
    {
        Female = 1,

        Male = 0,

        Other = 2
    }

    [EnumAsInt]
    public enum FamilyStatus : short
    {
        Alone = 1,

        Married = 2,

        Other = 3
    }

    [EnumAsInt]
    public enum Status : short
    {
        Waiting = 1, //đã đến phòng khám
        Cancel = 2, //đã hủy khám
        Finish = 3, //đã khám xong
        Pending = 4, //mới đặt khám
        Processing = 5, //đang khám
        Ready = 6 //đã chuyển sang phòng khám
    }

    [EnumAsInt]
    public enum FeeStatus : short
    {
        NotPaid = 0,
        Paid = 1,
        Return = 2,
        Free = 3,
        PaidBefore = 4,
        DoctorReturn = 5,

        //Đóng tiền cho lần khám tiếp
        PaidNext = 6
    }

    [EnumAsInt]
    public enum AppointmentType : short
    {
        //Khám thường
        Register = 1,

        //Khám kiểm tra
        ReExamination = 2,

        //Khám chờ
        Wait = 3,

        //YAG(SP,FRAC)
        Laser = 4,

        //SR(DS,ST,AC)
        IPL = 5,

        //Chăm sóc da
        SkinCare = 6,

        //Thủ thật khác
        Trick = 7
    }

    [EnumAsInt]
    public enum AppointmentList : short
    {
        //Khám thường
        Register = 1,

        //Khám kiểm tra
        ReExamination = 2,

        //Khám chờ
        Wait = 3,

        //YAG(SP,FRAC)
        Laser = 4,

        //SR(DS,ST,AC)
        IPL = 5,

        //Chăm sóc da
        SkinCare = 6,

        //Thủ thật khác
        Trick = 7,

        //Đăng ký trễ
        RegisterLater = 8,

        //VIP
        Vip = 9,

        //3 Chuẩn bị khám
        Ready = 10,

        //Đã khám xong
        Finish = 11
    }

    [EnumAsInt]
    public enum ExportMethod : short
    {
        Expired = 1,
        FiFo = 2,
        Option = 3
    }
    [EnumAsInt]
    public enum PrescriptionStatus : short
    {
        //Đang chờ
        Waiting = 1,

        //Đã hủy
        Cancel = 2,

        //Đã duyệt
        Confirm = 3,

        //Mua đủ
        Buyed = 4,

        //Mua khác
        BuyDifferent = 5
    }
    [EnumAsInt]
    public enum PrescriptionType : short
    {
        Examination = 1,
        Laser = 2,
        TakeDrug = 3,
        External = 4
    }
    [EnumAsInt]
    public enum ProductType : short
    {
        Drink = 1,
        Cosmetic = 2,
    }
    [EnumAsInt]
    public enum IdentityNumberType : short
    {
        Prescription = 1,
        Order = 2,
        Test = 3
    }
    [EnumAsInt]
    public enum UserRole : short
    {
        Admin = 1,
        Doctor = 2,
        User = 3
    }
    [EnumAsInt]
    public enum GroupEnum : short
    {
        Wait = 1,
        Drug = 2,
        Laser = 3,
        Assistant = 4,
        Test = 5,
        Security = 6,
        Doctor = 7,
        Admin = 8,
        Accounting = 9,
        IT = 10,
        Warehouse = 11,
        Skincare = 12
    }
    [EnumAsInt]
    public enum PatientCareType : short
    {
        Daily = 1,
        Custom = 2
    }

    [EnumAsInt]
    public enum SkincareLogType : short
    {
        Edit = 1,
        Process = 2
    }

main.cs

using System;
using System.Linq;
using System.Collections.Generic;
using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.SqlServer;

var dbFactory = new OrmLiteConnectionFactory("Server=112.78.2.206,1433;Database=drph8739_pkpl;User Id=drph8739_user;Password=PKPL1q2w3e#;Trusted_Connection=False;MultipleActiveResultSets=true", SqlServerDialect.Provider);
var db = dbFactory.OpenDbConnection();


var groupTransferDetailByProduct=GetListLaserJoinCustomerWithCheckHasTestByDayAsync(DateTime.Today.AddDays(-150),DateTime.Today);

groupTransferDetailByProduct.PrintDump();  

packages.config

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="ServiceStack.Text" version="5.2.0" targetFramework="net45" />
  <package id="ServiceStack.Interfaces" version="5.2.0" targetFramework="net45" />
  <package id="ServiceStack.Common" version="5.2.0" targetFramework="net45" />
  <package id="ServiceStack.OrmLite" version="5.2.0" targetFramework="net45" />
  <package id="ServiceStack.OrmLite.SqlServer" version="5.4.0" targetFramework="net45" />
</packages>

repository.cs

// WarehouseTransferDetailRepository.cs
// Created by quangquy87 on 2018/12/17
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ServiceStack.Data;
using ServiceStack.OrmLite.SqlServer;
using ServiceStack.OrmLite;

public class WarehouseTransferDetailMapping
    {
        public class WarehouseTransferDetail
        {
            public int WarehouseTransferDetailId { get; set; }
            public int WarehouseTransactionId { get; set; }
            public int ProductId { get; set; }
            public string Name { get; set; }
            public string Unit { get; set; }
            public string Code { get; set; }

            public int Quantity { get; set; }
            public string BatchNumber { get; set; }

            public DateTime CreatedTime { get; set; }
            public string Created => CreatedTime.ToString("dd/MM/yyyy HH:mm");

        }


    }

public IList<WarehouseTransferDetailMapping.WarehouseTransferDetail> GroupTransferDetailByProduct(DateTime fromDay, DateTime toDay)
        {
            using (var db = dbFactory.OpenDbConnection())
            {
                var q = db.From<WarehouseTransferDetail>()
                .Join<Product>((detail, product) => detail.ProductId == product.ProductId)
                .Join<WarehouseTransfer>((detail, warehouseTransfer) => detail.WarehouseTransferId == warehouseTransfer.WarehouseTransferId)
                .Where<WarehouseTransferDetail, Product, WarehouseTransfer>((detail, product, warehouseTransfer) => detail.Created >= fromDay && detail.Created <= toDay && warehouseTransfer.Status == WarehouseTransferStatus.Confirmed)
                 .GroupBy<WarehouseTransferDetail, Product>((detail, product) => new { detail.ProductId, product.Name, product.Code, product.Unit })
                 .Select<WarehouseTransferDetail, Product, WarehouseTransfer>((detail, product, warehouseTransfer) => new
                 {
                     ProductId = detail.ProductId,
                     Name = product.Name,
                     Unit = product.Unit,
                     Code = product.Code,
                     Quantity = Sql.Sum(detail.Quantity),
                 });
                return  db.Select<WarehouseTransferDetailMapping.WarehouseTransferDetail>(q);
            }

        }
 public List<LaserMapping> GetListLaserJoinCustomerWithCheckHasTestByDayAsync (DateTime fromDay, DateTime toDay) {
            using (var db = dbFactory.OpenDbConnection ()) {
                var q = db.From<Laser> ()
                     .Join<Customer> ((laser, customer) => laser.CustomerId == customer.CustomerId)
                    .Where (x => x.Created >= fromDay && x.Created <= toDay)
                    .OrderByDescending<Laser> (x => x.LaserId)
                   
                    .Select<Laser, Customer> ((laser, customer) => new {
                        laser,
                        customer,
                       // HasTest = db.Exists<Test> (t => t.AppointmentId == laser.AppointmentId)
                    });

                return  db.Select<LaserMapping> (q);
            }

        }
public async Task<List<OrderDetailMapping>> GetListOrderDetailJoinProductWithInventoryByOrderIdAsync (int orderId) {
            using (var db = dbFactory.OpenDbConnection ()) {
                List<OrderDetailMapping> orderDetails = await db.SelectAsync<OrderDetailMapping> (db.From<OrderDetail> ()
                    .Join<Product> ((detail, product) => detail.ProductId == product.ProductId)
                    .Where (x => x.OrderId == orderId)
                    .Select<OrderDetail, Product> ((detail, product) => new {
                        detail,
                        product,
                        TotalAvailableQuantity = db.Scalar<PharmacyInventory, int> (p => Sql.Sum (p.Quantity), p => p.ProductId == detail.ProductId)
                    }));

                if (orderDetails.Any ()) {
                    List<int> productIds = orderDetails.Select (x => x.ProductId).Distinct ().ToList ();
                    var q = db.From<PharmacyInventory> ()
                        .Where (x => productIds.Contains (x.ProductId))
                        .GroupBy (x => x.ProductId)
                        .Select (x => new {
                            x.ProductId,
                                Quantity = Sql.Sum (x.Quantity)
                        });
                    Dictionary<int, int> inventories = await db.DictionaryAsync<int, int> (q);

                    foreach (var detail in orderDetails) {
                        if (inventories.ContainsKey (detail.ProductId)) {
                            detail.TotalAvailableQuantity = inventories[detail.ProductId];
                        }
                    }
                }

                return orderDetails;
            }
        }