using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using DataLibrary; using CoreSavingLibrary; namespace Saving.Applications.shrlon { public partial class w_sheet_memeber_all_ry : PageWebSheet, WebSheet { public void InitJsPostBack() { } public void WebSheetLoadBegin() { } public void CheckJsPostBack(string eventArg) { } public void SaveWebSheet() { int myear = 0; if (year.Text != null) { myear = int.Parse(year.Text); process(myear); } } public void WebSheetLoadEnd() { } public void process(int myear) { String member_no = null; int yk = myear - 543; String sdate = "04/01/" + yk; String edate = "03/31/" + (yk + 1); DateTime sdt = Convert.ToDateTime(sdate); DateTime edt = Convert.ToDateTime(edate); decimal seq_no = 0; String mbmark_type = null; decimal mbmark_amount2 = 0, mbmark_amount4 = 0, mbmark_amount5 = 0, mbmark_amount7 = 0; decimal mbmark_score2 = 0, mbmark_score4 = 0, mbmark_score5 = 0, mbmark_score7 = 0; try { int y = myear - 543; string sql = @"select member_no from mbmembmaster where member_status =1 and member_date <= {0} and ( resign_status!=1 or RESIGN_status=1 and MBMEMBMASTER.RESIGN_DATE >= {0} ) order by member_no"; sql = WebUtil.SQLFormat(sql, edt); Sdt dt = WebUtil.QuerySdt(sql); while (dt.Next()) { decimal principal_arrear = 0; decimal intyear_arrear = 0; decimal principal_arrear2 = 0; decimal intyear_arrear2 = 0; decimal score_oil = 0; decimal amount_oil = 0; decimal shr = 0; member_no = dt.GetString("member_no"); //หุ้น string sql3 = @"SELECT SLSLIPPAYINDET.ITEM_PAYAMT as share_amount FROM MBMEMBMASTER , MBUCFMEMBGROUP , MBUCFPRENAME , SLSLIPPAYIN , SLSLIPPAYINDET WHERE ( MBUCFMEMBGROUP.MEMBGROUP_CODE = MBMEMBMASTER.MEMBGROUP_CODE ) and (MBMEMBMASTER.PRENAME_CODE =MBUCFPRENAME.PRENAME_CODE ) and (MBMEMBMASTER.MEMBER_NO = SLSLIPPAYIN.MEMBER_NO ) and ( SLSLIPPAYINDET.PAYINSLIP_NO = SLSLIPPAYIN.PAYINSLIP_NO ) and ( SLSLIPPAYIN.SLIP_STATUS = 1 ) And (TRUNC(SLSLIPPAYIN.SLIP_DATE) between {1} and {2}) and ( SLSLIPPAYIN.SLIPTYPE_CODE in ( 'PX ','PMP') ) and ( SLSLIPPAYINDET.SLIPITEMTYPE_CODE = 'SHR' ) and ( SLSLIPPAYIN.coop_id ={3}) and ( SLSLIPPAYIN.MONEYTYPE_CODE ='CSH' ) and (slslippayin.slip_status <> -9) and SLSLIPPAYINDET.ITEM_PAYAMT >0 and MBMEMBMASTER.MEMBER_NO={0} order by MBMEMBMASTER.MEMBER_NO "; sql3 = WebUtil.SQLFormat(sql3, member_no, sdt, edt, state.SsCoopControl); Sdt dt3 = WebUtil.QuerySdt(sql3); while (dt3.Next()) { shr += dt3.GetDecimal("share_amount"); } mbmark_amount2 = shr; mbmark_type = "02"; string sql4 = @"select share_score,start_sharelate,end_shareslate from MBMARKSHARECONT where start_sharelate<={0} and end_shareslate>={0} ORDER BY start_sharelate,end_shareslate ASC "; sql4 = WebUtil.SQLFormat(sql4, mbmark_amount2); Sdt dt4 = WebUtil.QuerySdt(sql4); while (dt4.Next()) { mbmark_score2 = dt4.GetDecimal("share_score"); } seq_no = get_seq_no(member_no, myear); insert(member_no, seq_no, mbmark_type, mbmark_amount2, mbmark_score2, myear); //ต้นค้าง ดอกค้าง string sql5 = @"select member_no,principal_arrear,intyear_arrear from lncontmaster where member_no={0} and contract_status=1"; sql5 = WebUtil.SQLFormat(sql5, member_no); Sdt dt5 = WebUtil.QuerySdt(sql5); while (dt5.Next()) { principal_arrear = dt5.GetDecimal("principal_arrear"); // ต้นค้าง intyear_arrear = dt5.GetDecimal("intyear_arrear");//ดอกค้าง principal_arrear2 += principal_arrear; intyear_arrear2 += intyear_arrear; } mbmark_amount5 = principal_arrear2 + intyear_arrear2; mbmark_type = "05"; if (mbmark_amount5 > 0) { mbmark_score5 = 0; } else if (mbmark_amount5 == 0) { mbmark_score5 = 25; } seq_no = get_seq_no(member_no, myear); insert(member_no, seq_no, mbmark_type, mbmark_amount5, mbmark_score5, myear); //เข้าประชุม decimal conference_mark = 0; string sql6 = @"select conference_mark from mbmarkconference where member_no={0} and account_year={1}"; sql6 = WebUtil.SQLFormat(sql6, member_no,myear); Sdt dt6 = WebUtil.QuerySdt(sql6); while (dt6.Next()) { conference_mark = dt6.GetDecimal("conference_mark"); } mbmark_amount7 = conference_mark; mbmark_type = "07"; if (mbmark_amount7 == 0) { mbmark_score7 = 0; } else if (mbmark_amount7 == 1) { mbmark_score7 = 25; } seq_no = get_seq_no(member_no, myear); insert(member_no, seq_no, mbmark_type, mbmark_amount7, mbmark_score7, myear); //ซื้อสินค้า decimal slipnet_amt = 0; string sql7 = @"select sum(slipnet_amt)as slipnet_amt from ststockslip where debt_no={0} and slip_date between {1} and {2} and sliptype_code='002' and slip_status=1"; sql7 = WebUtil.SQLFormat(sql7, member_no, sdt, edt); Sdt dt7 = WebUtil.QuerySdt(sql7); while (dt7.Next()) { slipnet_amt = dt7.GetDecimal("slipnet_amt"); } //น้ำมัน string sql007 = @"select mbmark_amount from mbmarkoil where mbmark_type='3' and account_year={0} and member_no={1}"; sql007 = WebUtil.SQLFormat(sql007, myear, member_no); Sdt dt007 = WebUtil.QuerySdt(sql007); while (dt007.Next()) { amount_oil = dt007.GetDecimal("mbmark_amount"); } //คะแนนสินค้า+น้ำมัน mbmark_amount4 = slipnet_amt + amount_oil; //สินค้า string sql71 = @"select amount_score from mbmarkstockcont where start_amountlate<={0} and end_amountlate>={0} ORDER BY start_amountlate,end_amountlate "; sql71 = WebUtil.SQLFormat(sql71, mbmark_amount4); Sdt dt71 = WebUtil.QuerySdt(sql71); while (dt71.Next()) { mbmark_score4 = dt71.GetDecimal("amount_score"); } mbmark_type = "04"; seq_no = get_seq_no(member_no, myear); insert(member_no, seq_no, mbmark_type, mbmark_amount4, mbmark_score4, myear); decimal sum_score = 0; sum_score = mbmark_score7 + mbmark_score2 + mbmark_score4 + mbmark_score5; decimal mark_class= 0; //เกณฑ์คะแนน string sql701 = @"select mb_class,start_mark,end_mark from mbmarkclass where start_mark <={0} and end_mark>={0} ORDER BY start_mark,end_mark"; sql701 = WebUtil.SQLFormat(sql701,sum_score); Sdt dt701 = WebUtil.QuerySdt(sql701); while (dt701.Next()) { mark_class = dt701.GetDecimal("mb_class"); } string sql20 = "insert into MBMARKTOTAL (account_year,member_no,mark_conference,mark_share,mark_dept,mark_stock,mark_loan,mark_total,mark_class,mark_stockoil) values({0},{1},{2},{3},0,{4},{5},{6},{7},{8})"; sql20 = WebUtil.SQLFormat(sql20, myear, member_no, mbmark_score7, mbmark_score2, mbmark_score4, mbmark_score5, sum_score, mark_class,score_oil); WebUtil.QuerySdt(sql20); //สก.ตรอน god int yearnow = 0,yeardiff=0; if (state.SsCoopControl == "000101") { yearnow = getyear(); yeardiff =yearnow-myear; //เช่น ปีนี้2560 จะใช้ข้อมูล2558 if (yeardiff == 2) { string sqlclass = @"UPDATE mbmembmaster SET membtype_level = {1} WHERE member_no={0}"; sqlclass = WebUtil.SQLFormat(sqlclass, member_no, mark_class); WebUtil.QuerySdt(sqlclass); } } //else //{ // string sqlclass = @"UPDATE mbmembmaster SET membtype_level = {1} WHERE member_no={0}"; // sqlclass = WebUtil.SQLFormat(sqlclass, member_no, mark_class); // WebUtil.QuerySdt(sqlclass); //} } } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } } public void insert(String member_no, decimal seq_no, String mbmark_type, decimal mbmark_amount, decimal mbmark_score, int myear) { try { string sql10 = "insert into MBMARKDETAIL (Member_No,seq_no,Account_Year,mbmark_type,mbmark_amount,mbmark_score) values({0},{1},{2},{3},{4},{5})"; sql10 = WebUtil.SQLFormat(sql10, member_no, seq_no, myear, mbmark_type, mbmark_amount, mbmark_score); WebUtil.QuerySdt(sql10); } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } } public int getyear() { int myear = 0; string sql21 = @"select account_year from accaccountyear where close_account_stat=0 and coop_id= '" + state.SsCoopControl + "'"; sql21 = WebUtil.SQLFormat(sql21); Sdt dt21 = WebUtil.QuerySdt(sql21); if (dt21.Next()) { myear = dt21.GetInt32("account_year") + 543; } return myear; } public decimal get_seq_no(String member_no, int myear) { decimal seq_no = 0; try { ////get_seq_no string sql2 = @"select max(seq_no)as seq_no1 from MBMARKDETAIL where member_no ={0} and Account_Year = {1} "; sql2 = WebUtil.SQLFormat(sql2, member_no, myear); Sdt dt2 = WebUtil.QuerySdt(sql2); while (dt2.Next()) { seq_no = dt2.GetDecimal("seq_no1"); seq_no += 1; } } catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex); } return seq_no; } } }