コード例 #1
0
        public WithdrawalRetrunlst GetData_List(Withdrawal_Search data)
        {
            db  = new PTTGSP_DWSContext();
            pis = new PISContext();
            WithdrawalRetrunlst         result = new WithdrawalRetrunlst();
            List <Withdrawal_history_H> H_TB   = new List <Withdrawal_history_H>();
            int?nReasonID;
            int?sStepID;

            if (!string.IsNullOrEmpty(data.sReasonID))
            {
                nReasonID = data.sReasonID.ToIntOrNull();
            }
            else
            {
                nReasonID = null;
            }
            if (!string.IsNullOrEmpty(data.sStepID))
            {
                sStepID = data.sStepID.ToIntOrNull();
            }
            else
            {
                sStepID = null;
            }


            var lstData = (from R in db.T_Request.Where(w =>
                                                        (!string.IsNullOrEmpty(data.sRequestNo) ? (w.sRequestNo.ToLower().Contains(data.sRequestNo.ToLower().Trim())) : true))
                           from Re in db.TB_Reason.Where(w => w.nReasonID == R.nReasonID && (nReasonID.HasValue ? w.nReasonID == nReasonID : true))
                           from U in db.TB_User.Where(w => w.sEmployeeID == R.sCreateBy &&
                                                      (!string.IsNullOrEmpty(data.sEmployeeID) ? (w.sEmployeeID.ToLower().Contains(data.sEmployeeID.ToLower().Trim())) : true))
                           from SR in db.TM_Step_Request.Where(w => w.nStepID == R.nStepID && (sStepID.HasValue ? w.nStepID == sStepID : true))
                           select new
            {
                R.nRequestID,
                R.sRequestNo,           //เลขที่ใบขอเบิก
                R.sCreateBy,            //รหัสผู้ขอเบิก
                U.sFirstName,           //ชื่อผู้ขอเบิก
                U.sLastName,            //นามสกุลผู้ขอเบิก
                R.nReasonID,            //เหตุผลในการเบิก
                sReasonName = Re.sName, //ชื่อเหตุผลในการเบิก
                R.dCreate,              //วันที่เบิก
                R.nRequest_TotalPrice,
                R.sOrgID,               //หน่วยงาน
                R.nStepID,              //สถานะของรายการ
                SR.sName                //ชื่อสถานะของรายการ
            }).ToList();

            foreach (var i in lstData)
            {
                List <Withdrawal_history_S> S_TB = new List <Withdrawal_history_S>();
                var H      = new Withdrawal_history_H();
                var lstSub = db.T_Request_Material.Where(w => w.IsApprove == true && w.nRequestID == i.nRequestID).Select(s => new Withdrawal_history_S
                {
                    IsApprove           = s.IsApprove,
                    nMaterialID         = s.nMaterialID,
                    nPay_Amount         = s.nPay_Amount,
                    sNote               = s.sNote,
                    nPay_Price          = s.nPay_Price,
                    nPay_TotalPrice     = s.nPay_TotalPrice,
                    nRequestID          = s.nRequestID,
                    nRequest_Amount     = s.nRequest_Amount,
                    nRequest_Price      = s.nRequest_Price,
                    nRequest_TotalPrice = s.nRequest_TotalPrice,
                }).ToList();
                foreach (var sub in lstSub)
                {
                    Withdrawal_history_S s = new Withdrawal_history_S();
                    var Materials          = db.TB_Materials.FirstOrDefault(w => w.nMaterialID == sub.nMaterialID);
                    if (Materials != null)
                    {
                        s.sMaterialCode = Materials.sMaterialCode; // รหัสวัสดุ
                        s.sMaterialName = Materials.sName;         // ชื่อวัสดุ
                        var Unit = db.TB_Material_Unit.FirstOrDefault(f => f.nUnitID == Materials.nUnitID);
                        if (Unit != null)
                        {
                            s.sUnitName = Unit.sName;               // หน่วยนับ
                        }
                        else
                        {
                            s.sUnitName = "";
                        }
                    }
                    s.nRequest_Amount = sub.nRequest_Amount; // จำนวนขอเบิก
                    s.nPay_Amount     = sub.nPay_Amount;     //จำนวนที่ได้รับ
                    s.nPay_TotalPrice = sub.nPay_TotalPrice; //ราคารวม
                    S_TB.Add(s);
                }
                if (lstSub.Count > 0)
                {
                    H.nReasonID           = i.nReasonID;
                    H.nRequestID          = i.nRequestID;
                    H.nRequest_TotalPrice = i.nRequest_TotalPrice;
                    H.nStepID             = i.nStepID;
                    H.sCreateBy           = i.sCreateBy;
                    H.sFullName           = i.sFirstName + " " + i.sLastName;
                    H.sReasonName         = i.sReasonName;
                    H.sOrgID     = i.sOrgID;
                    H.sRequestNo = i.sRequestNo;
                    H.StepName   = i.sName;
                    //H.dCreate = i.dCreate.SetMonth_th();
                    H.sub_List = S_TB;
                    H_TB.Add(H);
                }
            }
            result.Withdrawal_history_H = H_TB;
            var lstStep   = db.TM_Step_Request.OrderBy(o => o.sName).ToList();
            var lstReason = db.TB_Reason.Where(w => !w.IsDel && w.IsActive).OrderBy(o => o.sName).ToList();

            result.TB_Reason       = lstReason;
            result.TM_Step_Request = lstStep;

            var lstUser = (from U in db.TB_User.Where(w => w.IsActive && !w.IsDel)
                           select new lstAutoCompleted_Withdrawal
            {
                sEmployeeID = U.sEmployeeID,
                value = U.sEmployeeID,
                label = U.sEmployeeID + " " + "-" + " " + U.sFirstName + " " + U.sLastName,
                sFirstName = U.sFirstName,
                sLastName = U.sLastName,
                sOrgID = U.sOrgID,
                sOrgName = U.sOrgName,
                sPosName = U.sPosName,
            }
                           ).OrderBy(o => o.sFirstName).ToList();

            var lstUnit_DB = pis.unit.Where(w => (w.DUMMY_RELATIONSHIP == "1-1" || w.DUMMY_RELATIONSHIP.StartsWith("1-1-"))).ToList(); //หน่วยงาน

            var    cf4           = db.TM_Config.FirstOrDefault(w => w.nID == 4);
            string sUnitCode_GSP = cf4 != null ? cf4.sValue : "80000563";
            var    lstMember     = PISFunction.ListUnit_ToLower(sUnitCode_GSP).Select(s => s.CODE).ToList();

            // string[] arrWSTCode = new string[] { "A", "B", "I", "J" };
            // string[] arrPersonel = new string[] { };
            // var lstMember = PISFunction.AllReportTo_EmpCode("270073");
            // var lstPersonel = (from p in pis.personel_info.Where(w => lstMember.Contains(w.CODE))
            //                    select new lstAutoCompleted_RequestApproval
            //                    {
            //                        sCODE = p.CODE, // รหัส user
            //                        value = p.CODE,
            //                        label = p.CODE + " " + "-" + " " + p.FULLNAMETH,
            //                        sPrefix = p.INAME, // คำนำหน้า
            //                        sFirstName = p.FNAME,
            //                        sLastName = p.LNAME,
            //                        sFullName = p.FULLNAMETH,
            //                    }
            //).OrderBy(o => o.sFirstName).ToList();
            result.lstAutoCompleted = lstUser;
            return(result);
        }
コード例 #2
0
        public IActionResult Export_Withdrawal()
        {
            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet H1 = workbook.Worksheets.Add("ประวัติการเบิก");
                #region Data
                List <Withdrawal_history_H> H_TB = new List <Withdrawal_history_H>();
                var lstData = (from R in db.T_Request
                               from Re in db.TB_Reason.Where(w => w.nReasonID == R.nReasonID)
                               from U in db.TB_User.Where(w => w.sEmployeeID == R.sCreateBy)
                               from SR in db.TM_Step_Request.Where(w => w.nStepID == R.nStepID)
                               select new
                {
                    R.nRequestID,
                    R.sRequestNo,           //เลขที่ใบขอเบิก
                    R.sCreateBy,            //รหัสผู้ขอเบิก
                    U.sFirstName,           //ชื่อผู้ขอเบิก
                    U.sLastName,            //นามสกุลผู้ขอเบิก
                    R.nReasonID,            //เหตุผลในการเบิก
                    sReasonName = Re.sName, //ชื่อเหตุผลในการเบิก
                    R.dCreate,              //วันที่เบิก
                    R.nRequest_TotalPrice,
                    R.sOrgID,               //หน่วยงาน
                    R.nStepID,              //สถานะของรายการ
                    SR.sName                //ชื่อสถานะของรายการ
                }).ToList();
                foreach (var i in lstData)
                {
                    List <Withdrawal_history_S> S_TB = new List <Withdrawal_history_S>();
                    var H      = new Withdrawal_history_H();
                    var lstSub = db.T_Request_Material.Where(w => w.IsApprove == true && w.nRequestID == i.nRequestID).Select(s => new Withdrawal_history_S
                    {
                        IsApprove           = s.IsApprove,
                        nMaterialID         = s.nMaterialID,
                        nPay_Amount         = s.nPay_Amount,
                        sNote               = s.sNote,
                        nPay_Price          = s.nPay_Price,
                        nPay_TotalPrice     = s.nPay_TotalPrice,
                        nRequestID          = s.nRequestID,
                        nRequest_Amount     = s.nRequest_Amount,
                        nRequest_Price      = s.nRequest_Price,
                        nRequest_TotalPrice = s.nRequest_TotalPrice,
                    }).ToList();
                    foreach (var sub in lstSub)
                    {
                        Withdrawal_history_S s = new Withdrawal_history_S();
                        var Materials          = db.TB_Materials.FirstOrDefault(w => w.nMaterialID == sub.nMaterialID);
                        if (Materials != null)
                        {
                            s.sMaterialCode = Materials.sMaterialCode; // รหัสวัสดุ
                            s.sMaterialName = Materials.sName;         // ชื่อวัสดุ
                            var Unit = db.TB_Material_Unit.FirstOrDefault(f => f.nUnitID == Materials.nUnitID);
                            if (Unit != null)
                            {
                                s.sUnitName = Unit.sName;               // หน่วยนับ
                            }
                            else
                            {
                                s.sUnitName = "";
                            }
                        }
                        s.nRequest_Amount = sub.nRequest_Amount; // จำนวนขอเบิก
                        s.nPay_Amount     = sub.nPay_Amount;     //จำนวนที่ได้รับ
                        s.nPay_TotalPrice = sub.nPay_TotalPrice; //ราคารวม
                        S_TB.Add(s);
                    }
                    if (lstSub.Count > 0)
                    {
                        H.nReasonID           = i.nReasonID;
                        H.nRequestID          = i.nRequestID;
                        H.nRequest_TotalPrice = i.nRequest_TotalPrice;
                        H.nStepID             = i.nStepID;
                        H.sCreateBy           = i.sCreateBy;
                        H.sFullName           = i.sFirstName + " " + i.sLastName;
                        H.sReasonName         = i.sReasonName;
                        H.sOrgID     = i.sOrgID;
                        H.sRequestNo = i.sRequestNo;
                        H.StepName   = i.sName;
                        //H.dCreate = i.dCreate.SetMonth_th();
                        H.sub_List = S_TB;
                        H_TB.Add(H);
                    }
                }
                #endregion
                #region String
                string[] arrHead     = { "เลขที่ใบขอเบิก", "ผู้ขอเบิก", "เหตุผลในการเบิก", "วันที่เบิก", "จำนวนเงิน", "หน่วยงาน", "สถานะ" };
                string[] arrHead_Sub = { "ที่", "รหัสวัสดุ", "ชื่อวัสดุ", "จำนวนขอเบิก", "จำนวนที่ได้รับ", "หน่วยนับ", "ราคารวม" };
                #endregion
                #region Setting
                H1.Style.Font.FontName = "Calibri";
                #endregion
                int Rows = 1;
                foreach (var i in H_TB)
                {
                    string[] lstdata = { i.sRequestNo, i.sFullName, i.sReasonName, i.dCreate, i.nRequest_TotalPrice + "", i.sOrgID, i.StepName };

                    int Columns_arrHead = 1;
                    for (int h = 0; h < arrHead.Length; h++)
                    {
                        H1.Column(Columns_arrHead).Width     = 15;
                        H1.Cell(Rows, Columns_arrHead).Value = arrHead[h];
                        H1.Cell(Rows, Columns_arrHead).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                        H1.Cell(Rows, Columns_arrHead).Style.Fill.BackgroundColor = XLColor.FromHtml("#B4C6E7");
                        H1.Cell(Rows, Columns_arrHead).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        Columns_arrHead++;
                    }
                    Columns_arrHead = 1;
                    for (int h = 0; h < lstdata.Length; h++)
                    {
                        int data = Rows + 1;
                        H1.Cell(data, Columns_arrHead).Value = lstdata[h];
                        H1.Cell(data, Columns_arrHead).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                        H1.Cell(data, Columns_arrHead).Style.Fill.BackgroundColor = XLColor.FromHtml("#ffffff");
                        H1.Cell(data, Columns_arrHead).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        Columns_arrHead++;
                    }
                    int Columns_Subdata = 2;
                    int datasub         = 0;
                    for (int h = 0; h < arrHead_Sub.Length; h++)
                    {
                        datasub = Rows + 2;
                        H1.Column(Columns_Subdata).Width        = 15;
                        H1.Cell(datasub, Columns_Subdata).Value = arrHead_Sub[h];
                        H1.Cell(datasub, Columns_Subdata).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                        H1.Cell(datasub, Columns_Subdata).Style.Fill.BackgroundColor = XLColor.FromHtml("#B4C6E7");
                        H1.Cell(datasub, Columns_Subdata).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

                        Columns_Subdata++;
                    }
                    int Count   = 1;
                    int rowdata = datasub + 1;
                    foreach (var sub in i.sub_List)
                    {
                        string[] lstdata_sub = { Count + "", sub.sMaterialCode, sub.sMaterialName, sub.nRequest_Amount + "", sub.nPay_Amount + "", sub.sUnitName, sub.nPay_TotalPrice + "" };
                        Columns_Subdata = 2;
                        for (int h = 0; h < lstdata_sub.Length; h++)
                        {
                            H1.Cell(rowdata, Columns_Subdata).Value = lstdata_sub[h];
                            H1.Cell(rowdata, Columns_Subdata).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            H1.Cell(rowdata, Columns_Subdata).Style.Fill.BackgroundColor = XLColor.FromHtml("#ffffff");
                            H1.Cell(rowdata, Columns_Subdata).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                            Columns_Subdata++;
                        }
                        Count++;
                        rowdata++;
                    }
                    Rows = Rows + i.sub_List.Count() + 3;// แถว + จำนวน sub_List + แถวนับตั้งแต่ arrHead
                }
                using (MemoryStream fs = new MemoryStream())
                {
                    workbook.SaveAs(fs);
                    fs.Position = 0;
                    return(File(fs.ToArray(), "application/vnd.ms-excel", "TEST.xlsx"));
                }
            }
        }