public async Task finalize_attendance(List <attendance_register> attendance, bool send_sms)
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                string query = @"UPDATE attendance_register
                                SET
                                `attendance` = @attendance,
                                `finalize` = 1
                                WHERE `session` = @session AND `att_date` = @att_date and sr_num = @sr_num";

                foreach (attendance_register att in attendance)
                {
                    await con.ExecuteAsync(query,
                                           new
                    {
                        att.session,
                        att.att_date,
                        att.attendance,
                        att.sr_num
                    });

#if !DEBUG
                    if (!att.attendance && send_sms)
                    {
                        sr_register std         = new sr_register();
                        string      phone_query = @"SELECT 
                                                COALESCE(std_contact, std_contact1, std_contact2) std_contact,
                                                CONCAT(IFNULL(std_first_name, ''),
                                                        ' ',
                                                        IFNULL(std_last_name, ' ')) std_first_name
                                            FROM
                                                sr_register
                                            WHERE
                                                sr_number = @sr_number";
                        std = con.Query <sr_register>(phone_query, new { sr_number = att.sr_num }).SingleOrDefault();



                        SMSMessage sms = new SMSMessage();

                        foreach (var item in sms.smsbody("absent"))
                        {
                            string body = item.Replace("#student_name#", std.std_first_name);

                            body = body.Replace("#current_date#", att.att_date.ToString("dd/MM/yyyy"));

                            await sms.SendSMS(body, std.std_contact, true);
                        }
                    }
#endif
                }
            }
        }
示例#2
0
        public async Task AddRegistration(std_registration std)
        {
            try
            {
                using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    mst_fin fin = new mst_fin();

                    string query1 = @"SELECT fin_id
                              ,fin_start_date
                              ,fin_end_date
                              ,fin_close
                          FROM mst_fin
                          where fin_close = 'N'";

                    fin = con.Query <mst_fin>(query1).SingleOrDefault();

                    if (std.reg_date > fin.fin_start_date && std.reg_date < fin.fin_end_date)
                    {
                        string sess = @"SELECT 
                                        session
                                    FROM
                                        mst_session
                                    WHERE
                                        session_active = 'Y'";

                        string session = con.ExecuteScalar <string>(sess);

                        string maxid = @"SELECT 
                                            IFNULL(MAX(reg_no), 0) + 1
                                        FROM
                                            sr_register
                                        WHERE
                                            adm_session = @adm_session";

                        int id = con.Query <int>(maxid, new { adm_session = session }).SingleOrDefault();

                        string max = @"SELECT 
                                        IFNULL(MAX(reg_no), 0) + 1
                                    FROM
                                        std_registration
                                    WHERE
                                        session = @adm_session";

                        int id1 = con.Query <int>(max, new { adm_session = session }).SingleOrDefault();



                        string query = @"INSERT INTO std_registration
           (session
           ,reg_no
           ,reg_date
           ,std_first_name
           ,std_last_name
           ,std_father_name
           ,std_mother_name
           ,std_address
           ,std_address1
           ,std_address2
           ,std_district
           ,std_state
           ,std_country
           ,std_pincode
           ,std_contact
           ,std_contact1
           ,std_contact2
           ,std_email
           ,std_class_id)
     VALUES
           (@session
           ,@reg_no
           ,@reg_date
           ,@std_first_name
           ,@std_last_name
           ,@std_father_name
           ,@std_mother_name
           ,@std_address
           ,@std_address1
           ,@std_address2
           ,@std_district
           ,@std_state
           ,@std_country
           ,@std_pincode
           ,@std_contact
           ,@std_contact1
           ,@std_contact2
           ,@std_email
           ,@std_class_id)";

                        std.session = session;

                        if (id1 < id)
                        {
                            std.reg_no = id;
                        }
                        else
                        {
                            std.reg_no = id1;
                        }
                        std.reg_date = System.DateTime.Now.AddMinutes(dateTimeOffSet);



                        await con.ExecuteAsync(query,
                                               new
                        {
                            std.session
                            ,
                            std.reg_no
                            ,
                            std.reg_date
                            ,
                            std.std_first_name
                            ,
                            std.std_last_name
                            ,
                            std.std_father_name
                            ,
                            std.std_mother_name
                            ,
                            std.std_address
                            ,
                            std.std_address1
                            ,
                            std.std_address2
                            ,
                            std.std_district
                            ,
                            std.std_state
                            ,
                            std.std_country
                            ,
                            std.std_pincode
                            ,
                            std.std_contact
                            ,
                            std.std_contact1
                            ,
                            std.std_contact2
                            ,
                            std.std_email
                            ,
                            std.std_class_id
                        });

                        out_standing out_std = new out_standing();

                        out_std.acc_id        = 1;
                        out_std.outstd_amount = std.fees_amount;
                        out_std.reg_num       = std.reg_no;

                        out_standingMain out_stdMain = new out_standingMain();



                        out_stdMain.AddOutStanding(out_std);
#if !DEBUG
                        SMSMessage sms = new SMSMessage();



                        foreach (var item in sms.smsbody("student_registration"))
                        {
                            string qry = @"SELECT 
                                        class_name
                                    FROM
                                        mst_class
                                    WHERE
                                        class_id = @class_id
                                            AND session = (SELECT
                                                session
                                            FROM
                                                mst_session
                                            WHERE
                                                session_active = 'Y')";

                            string className = con.Query <string>(qry, new { class_id = std.std_class_id }).SingleOrDefault();

                            string body = item.Replace("#student_name#", std.std_first_name + " " + std.std_last_name);

                            body = body.Replace("#class#", className);

                            await sms.SendSMS(body, std.std_contact, true);
                        }

                        //string text =  std.std_first_name+" "+ std.std_last_name+" is successfully registered in class "+ className + @". This registration is valid for 3 days subject to availability of seats. Thank You. Hariti Public School.";

                        //sms.SendSMS(text, std.std_contact);

                        // text = std.std_first_name + " " + std.std_last_name + " का पंजीकरण कक्षा " + className + " में सफलतापूर्वक हो गया है। यह पंजीकरण 3 दिन तक मान्य रहेगा। कक्षा में सीटों की उपलब्धता सीमित हैं। धन्यवाद। Hariti Public School";

                        //sms.SendSMS(text, std.std_contact);
#endif
                    }
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
        public async Task Updatefees_Bounce(uncleared_cheque unclear)
        {
            try
            {
                using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    string query = @"UPDATE fees_receipt 
                                    SET 
                                        chq_reject = @chq_reject,
                                        nt_clear_reason = @narration,
                                        clear_flag = 1
                                    WHERE
                                        bnk_name = @bnk_name
                                            AND chq_date = DATE_FORMAT(@chq_date, '%Y-%m-%d')
                                            AND chq_no = @chq_no
                                            AND clear_flag = 0";

                    con.Execute(query, unclear);

                    // fees_receipt fee = new fees_receipt();

                    query = @"SELECT DISTINCT
                                serial, session, amount, dc_fine, dc_discount
                            FROM
                                fees_receipt
                            WHERE
                                bnk_name = @bnk_name
                                    AND chq_date = DATE_FORMAT(@chq_date, '%Y-%m-%d')
                                    AND chq_no = @chq_no";

                    var result = con.Query <uncleared_cheque>(query, new { bnk_name = unclear.bnk_name, chq_date = unclear.chq_date, chq_no = unclear.chq_no });



                    foreach (uncleared_cheque val in result)
                    {
                        query = @"UPDATE out_standing 
                                SET 
                                    rmt_amount = rmt_amount - @rmt_amount,
                                    dc_fine = dc_fine - @dc_fine,
                                    dc_discount = dc_discount - @dc_discount
                                WHERE
                                    serial = @serial AND session = @session";

                        con.Execute(query, new { rmt_amount = val.amount, dc_fine = val.dc_fine, dc_discount = val.dc_discount, serial = val.serial, session = val.session });
                    }

                    if (unclear.bnk_charges != 0)
                    {
                        query = @"SELECT DISTINCT
                                    sr_number, session, reg_no, class_id
                                FROM
                                    fees_receipt
                                WHERE
                                    bnk_name = @bnk_name
                                        AND chq_date = DATE_FORMAT(@chq_date, '%Y-%m-%d')
                                        AND chq_no = @chq_no";

                        result = con.Query <uncleared_cheque>(query, new { bnk_name = unclear.bnk_name, chq_date = unclear.chq_date, chq_no = unclear.chq_no });



                        out_standing     std    = new out_standing();
                        out_standingMain outstd = new out_standingMain();

                        std.acc_id        = 3;
                        std.clear_flag    = false;
                        std.outstd_amount = (unclear.bnk_charges) / result.Count();

                        foreach (uncleared_cheque val in result)
                        {
                            std.sr_number = val.sr_number;
                            std.reg_num   = val.reg_no;
                            std.class_id  = val.class_id;
                            std.session   = val.session;
                            outstd.AddOutStanding(std);
                        }
#if !DEBUG
                        if (unclear.chq_reject == "Bounce")
                        {
                            query = @"select coalesce(std_contact, std_contact1, std_contact2) from sr_register where sr_number = @sr_number";
                            string phone = con.Query <string>(query, new { sr_number = std.sr_number }).SingleOrDefault();


                            SMSMessage sms = new SMSMessage();

                            foreach (var item in sms.smsbody("cheque_bounce"))
                            {
                                string body = item.Replace("#cheque_number#", unclear.chq_no);

                                body = body.Replace("#bounce_charge#", unclear.bnk_charges.ToString());

                                await sms.SendSMS(body, phone, true);
                            }
                        }
#endif
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int AddReceipt(List <fees_receipt> fees)
        {
            string  phone;
            int     sr_num = 0;
            decimal amount = 0;
            string  flag   = "";

            try
            {
                using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    out_standingMain out_main = new out_standingMain();
                    out_standing     ot_std   = new out_standing();


                    mst_finMain fin = new mst_finMain();

                    if (fin.checkFYnotExpired())
                    {
                        string fin_id = fin.FindActiveFinId();
                        string query1 = "";
                        string maxid  = "select ifnull(MAX(receipt_no),0)+1 from fees_receipt where fin_id = @fin_id";

                        int rect_no = con.Query <int>(maxid, new { fin_id = fin_id }).SingleOrDefault();

                        DateTime rect_date = System.DateTime.Now.AddMinutes(dateTimeOffSet);

                        con.Open();

                        MySqlCommand myCommand = con.CreateCommand();

                        MySqlTransaction myTrans;

                        myTrans = con.BeginTransaction();

                        myCommand.Connection = con;

                        myCommand.Transaction = myTrans;

                        string sess = "";

                        decimal total_amount = 0;

                        try
                        {
                            foreach (fees_receipt fee in fees)
                            {
                                total_amount = total_amount + fee.amount;
                            }

                            foreach (fees_receipt fee in fees)
                            {
                                query1 = @"SELECT 
                                        IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)
                                    FROM
                                        out_standing
                                    WHERE
                                        serial = @serial AND session = @session";

                                decimal dues = con.Query <decimal>(query1, new { serial = fee.serial, session = fee.session }).SingleOrDefault();

                                if (dues != fee.due_amount)
                                {
                                    rect_no = 0;
                                    myTrans.Rollback();
                                    throw new System.InvalidOperationException("trying to update double entry in a single out_standing serial");
                                }

                                if (fee.fin_id == null)
                                {
                                    fee.fin_id = fin_id;
                                }

                                if (fee.class_id == 0)
                                {
                                    query1 = @"SELECT 
                                            section_id
                                        FROM
                                            mst_std_section 
                                        WHERE
                                            sr_num = @sr_number 
                                        AND session = @session";

                                    int id = con.Query <int>(query1, new { sr_number = fee.sr_number, session = fee.session }).SingleOrDefault();

                                    fee.section_id = id;

                                    query1 = @"SELECT 
                                            class_id
                                        FROM
                                            mst_std_class
                                        WHERE
                                            sr_num = @sr_number
                                        AND session = @session";

                                    id = con.Query <int>(query1, new { sr_number = fee.sr_number, session = fee.session }).SingleOrDefault();


                                    fee.class_id = id;
                                }


                                fee.receipt_no = rect_no;

                                fee.receipt_date = rect_date;


                                fee.dt_date = rect_date;

                                string reg_date;
                                string chq_date;

                                if (fee.reg_date == DateTime.MinValue)
                                {
                                    reg_date = "null";
                                }
                                else
                                {
                                    reg_date = String.Format("'{0}'", fee.reg_date.Value.ToString("yyyy-MM-dd"));
                                }

                                if (fee.chq_date == DateTime.MinValue)
                                {
                                    chq_date = "null";
                                }
                                else
                                {
                                    chq_date = String.Format("'{0}'", fee.chq_date.Value.ToString("yyyy-MM-dd"));
                                }



                                ot_std.serial       = fee.serial;
                                ot_std.rmt_amount   = fee.amount;
                                ot_std.receipt_no   = fee.receipt_no;
                                ot_std.receipt_date = fee.receipt_date;
                                ot_std.session      = fee.session;
                                ot_std.dt_date      = fee.dt_date;
                                ot_std.clear_flag   = fee.clear_flag;
                                ot_std.month_no     = fee.month_no;
                                ot_std.dc_fine      = fee.dc_fine;
                                ot_std.dc_discount  = fee.dc_discount;

                                out_main.updateOutstandingReceipt(ot_std, myCommand);

                                string query = String.Format(@"INSERT INTO fees_receipt
                               (fin_id
                                ,session
                               ,receipt_no
                               ,receipt_date
                               ,acc_id
                               ,fees_name
                               ,sr_number
                               ,class_id
                               ,section_id
                               ,amount
                               ,reg_no
                               ,reg_date
                               ,dc_fine
                               ,dc_discount
                               ,narration
                               ,serial
                               ,dt_date
                               ,bnk_name
                               ,chq_no
                               ,chq_date
                               ,mode_flag
                               ,clear_flag
                                ,user_id
                                ,secret_code)
                         VALUES
                               ('{0}'
                                ,'{1}'
                               ,{2}
                               ,'{3}'
                               ,{4}
                               ,'{5}'
                               ,{6}
                               ,{7}
                               ,{8}
                               ,{9}
                               ,{10}
                               ,{11}
                               ,{12}
                               ,{13}
                               ,'{14}'
                               ,{15}
                               ,'{16}'
                               ,'{17}'
                               ,'{18}'
                               ,{19}
                               ,'{20}'
                               ,{21}
                                ,{22},reverse(concat(unix_timestamp(),'-',base64_encode({2}),'-',base64_encode({6}),'-',base64_encode({23}))));", fee.fin_id,
                                                             fee.session,
                                                             fee.receipt_no,
                                                             fee.receipt_date.ToString("yyyy-MM-dd"),
                                                             fee.acc_id,
                                                             fee.fees_name,
                                                             fee.sr_number,
                                                             fee.class_id,
                                                             fee.section_id,
                                                             fee.amount,
                                                             fee.reg_no,
                                                             reg_date,
                                                             fee.dc_fine,
                                                             fee.dc_discount,
                                                             fee.narration,
                                                             fee.serial,
                                                             fee.dt_date.ToString("yyyy-MM-dd"),
                                                             fee.bnk_name,
                                                             fee.chq_no,
                                                             chq_date,
                                                             fee.mode_flag,
                                                             fee.clear_flag,
                                                             fee.user_id,
                                                             total_amount);


                                myCommand.CommandText = query;

                                myCommand.ExecuteNonQuery();



                                sr_num = fee.sr_number;
                                sess   = fee.session;

                                amount = amount + fee.amount;
                                amount = amount + fee.dc_fine;
                                amount = amount - fee.dc_discount;

                                flag = fee.mode_flag;
                            }
                            myTrans.Commit();

                            query1 = @"SELECT 
                                            std_email
                                        FROM
                                            sr_register
                                        WHERE
                                            sr_number = @sr_number";

                            string email_id = con.Query <string>(query1, new { sr_number = sr_num }).SingleOrDefault();

                            if (email_id != null && email_id.Trim() != "")
                            {
                                query1 = @"SELECT 
                                    sr_number num,
                                    CONCAT(ifnull(std_first_name,''), ' ',ifnull(std_last_name,'')) name,
                                    std_father_name father_name,
                                    CONCAT(c.class_name, ' Sec. ', b.section_name) class_name
                                FROM
                                    sr_register a,
                                    mst_section b,
                                    mst_class c,
                                    mst_std_section d,
                                    mst_std_class e
                                WHERE
                                    d.section_id = b.section_id
                                        AND b.class_id = c.class_id
                                        AND e.class_id = b.class_id
                                        AND a.sr_number = d.sr_num
                                        AND d.sr_num = e.sr_num
                                        AND a.sr_number = @sr_number
                                        AND b.session = c.session
                                        AND c.session = d.session
                                        AND d.session = e.session
                                        AND e.session = @session";

                                var rep = con.Query <rep_fees>(query1, new { sr_number = sr_num, session = sess }).SingleOrDefault();

                                query1 = @"SELECT 
                                        mode_flag,
                                        chq_no,
                                        chq_date,
                                        receipt_date,
                                        fees_name,
                                        sr_number,
                                        class_id,
                                        amount fees,
                                        dc_fine fine,
                                        dc_discount discount,
                                        amount + dc_fine - dc_discount amount,
                                        reg_no,
                                        reg_date,
                                        session
                                    FROM
                                        fees_receipt
                                    WHERE
                                        fin_id = (SELECT 
                                                fin_id
                                            FROM
                                                mst_fin
                                            WHERE
                                                fin_close = 'N')
                                            AND receipt_no = @receipt_no";


                                IEnumerable <fees_receipt> result = con.Query <fees_receipt>(query1, new { receipt_no = rect_no });

                                string fees_Table = "";

                                decimal total = 0;

                                foreach (var fee in result)
                                {
                                    fees_Table = fees_Table + @" <tr>
                                <td>
                                <div style='border-radius:2px;font-size:12px;color:#999;float:left;width:100%;border:solid 1px #f4f4f4'>
                                <ul style='list-style:none;width:96%;float:left;border-bottom:1px solid #e2e2e2;padding:2%;margin:0'>
                                <li style='float:left;width:40%;text-align:center;margin-left: 0px;'>
                                    " + fee.fees_name + @"
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    " + fee.fees + @"
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    " + fee.fine + @"
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    " + fee.discount + @"
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    " + fee.amount + @"
                                </li>

                            </ul>
                        </div>
                    </td>
                </tr>";

                                    total = total + fee.amount;
                                }

                                string Affiliation = ConfigurationManager.AppSettings["Affiliation"].ToString();
                                string SchoolName  = ConfigurationManager.AppSettings["SchoolName"].ToString();


                                string body = @"<div style='width:584px;margin:0 auto;border:#ececec solid 1px'>
    <div style='padding:22px 34px 15px 34px'>
        <div style='float:left'><img title='Institute Logo' src='" + HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + @"/images/logo.jpg' alt='Institute Logo' width='100px' class='CToWUd'></div>
        <div style='float:right; font:bold 30px Arial,Helvetica,sans-serif;margin-top:20px'>" + SchoolName + @"</div>
        <div style='float:right; font:12px Arial,Helvetica,sans-serif;margin-top:5px'>(" + Affiliation + @")</div>
    </div>
    <div style='clear:both'></div>
    <div style='float:left;color:#333333;font:normal 14px Arial,Helvetica,sans-serif;width:250px'>
        
        <!--<div>Order no: #8846417200 <br>  2019-07-27T07:13:42.000Z </div>-->
        <div style='padding-top:10px'></div>
    </div>
    
    <div style='clear:both'> </div>
</div>
<div style='width:584px;background-color:#ffffff;border:#e8e7e7 solid 1px;padding:27px 0;margin:0 auto;border-bottom:0'>
    <div style='border-bottom:#717171 dotted 1px;font:normal 14px Arial,Helvetica,sans-serif;color:#666666;padding:0px 33px 10px'>
        
        <table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
            <tbody>
                <tr>
                    <td width='450px'>Receipt No:</td>
                    <td width='450px'>" + rect_no + @"</td>
                    <td width='450px'>Receipt Date:</td>
                    <td width='450px'>" + result.First().receipt_date.ToString("dd-MMM-yyyy") + @"</td>
                </tr>
                <tr>
                    <td width='450px'>Admission No:</td>
                    <td width='450px'>" + rep.num + @"</td>
                    <td width='450px'>Class:</td>
                    <td width='450px'>" + rep.class_name + @"</td>
                </tr>
                <tr>
                    <td width='450px'>Name:</td>
                    <td width='450px'>" + rep.name + @"</td>
                    
                </tr>
                <tr>
                    <td width='450px'>Father's Name: </td>
                    <td width='450px'>" + rep.father_name + @"</td>
                   
                </tr>
            </tbody>
        </table>
    </div>
    <div style='border-bottom:#717171 dotted 1px;font:normal 14px Arial,Helvetica,sans-serif;color:#666666;padding:10px 33px 10px'>
        <br>
        <table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
            <tbody>

                <tr>
                    <td>
                        <div style='border-radius:2px;font-size:12px;color:#999;float:left;width:100%;border:solid 1px #f4f4f4'>

                            <ul style='list-style:none;width:96%;float:left;border-bottom:1px solid #e2e2e2;padding:2%;margin:0'>
                                <li style='float:left;width:40%;text-align:center;margin-left: 0px;'>
                                    Particulars
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    Fees
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    Fine
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    Discount
                                </li>
                                <li style='float:left;width:15%;text-align:center;margin-left: 0px;'>
                                    Paid
                                </li>
                            </ul>
                        </div>
                    </td>
                </tr>
                            " + fees_Table + @"
            </tbody>
        </table>

        <div style='border-bottom:#717171 dotted 1px;font:600 14px Arial,Helvetica,sans-serif;color:#333333;padding:17px 33px 17px'>
            <table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
                <tbody>
                    <tr>
                        <td width='450px'>Amount Paid</td>
                        <td width='313px' style='text-align:right'>Rs." + total + @"</td>
                    </tr>
                </tbody>
            </table>
        </div>
    </div>
    <div style='border-bottom:#717171 dotted 1px;font:normal 14px Arial,Helvetica,sans-serif;color:#666666;padding:10px 33px 10px'>
        <br>
        <table style='width:100%' border='0' cellspacing='0' cellpadding='2'>
            <tbody>
                <tr>
                    <td colspan='2'>
                        Received with thanks:
                    </td>
                    <td colspan='2'>
                        " + repFees_receipt.NumbersToWords(Decimal.ToInt32(total)) + @"
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
</div>
<div style='margin:0 auto;width:594px'><img title='' src='" + HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + @"/images/fee_receipt.png' alt='' class='CToWUd'></div>";

                                repFees_receipt rep_byte = new repFees_receipt();

                                byte[] bytes = rep_byte.pdf_bytes(rect_no, result.First().receipt_date);

                                if (total != 0)
                                {
                                    _sendMail(bytes, email_id, rect_no.ToString(), result.First().receipt_date.ToString("dd-MMM-yyyy"), body);
                                }
                            }
                        }
                        catch (Exception e)
                        {
                            try
                            {
                                rect_no = 0;
                                myTrans.Rollback();
                            }
                            catch (MySqlException ex)
                            {
                                if (myTrans.Connection != null)
                                {
                                    Console.WriteLine("An exception of type " + ex.GetType() +
                                                      " was encountered while attempting to roll back the transaction.");
                                }
                            }

                            Console.WriteLine("An exception of type " + e.GetType() +
                                              " was encountered while inserting the data.");
                            Console.WriteLine("Neither record was written to database.");
                        }
                        finally
                        {
                            con.Close();
                        }


#if !DEBUG
                        query1 = @"select coalesce(std_contact, std_contact1, std_contact2) from sr_register where sr_number = @sr_number";

                        phone = con.Query <string>(query1, new { sr_number = sr_num }).SingleOrDefault();

                        if (phone != null && amount != 0)
                        {
                            query1 = @"SELECT concat(std_first_name,' ',std_last_name)
                                FROM sr_register
                                where sr_number = @sr_number";

                            string name = con.Query <string>(query1, new { sr_number = sr_num }).SingleOrDefault();



                            if (flag == "Cash")
                            {
                                string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();

                                SMSMessage sms = new SMSMessage();

                                foreach (var item in sms.smsbody("cash_fees_submit"))
                                {
                                    string body = item.Replace("#student_name#", name);

                                    body = body.Replace("#current_date#", dt);

                                    body = body.Replace("#fees_amount#", amount.ToString());

                                    sms.SendSMS(body, phone, true);
                                }


                                //string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();

                                //SMSMessage sms = new SMSMessage();

                                //string text = @"School fees of " + name + " INR " + amount + " is successfully submitted on " + dt + ". Thank you for your cooperation. Hariti Public School";
                                //sms.SendSMS(text, phone);

                                //SMSMessage sms1 = new SMSMessage();
                                //string text1 = name + @" की स्कूल फीस INR " + amount + " दिनांक " + dt + " को सफलतापूर्वक जमा हो चुकी है। आपके सहयोग के लिए धन्यवाद। Hariti Public School";
                                //sms1.SendSMS(text1, phone);
                            }
                            else
                            {
                                string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();

                                SMSMessage sms = new SMSMessage();

                                foreach (var item in sms.smsbody("bank_fees_submit"))
                                {
                                    string body = item.Replace("#student_name#", name);

                                    body = body.Replace("#current_date#", dt);

                                    body = body.Replace("#fees_amount#", amount.ToString());

                                    sms.SendSMS(body, phone, true);
                                }


                                //string dt = DateTime.Now.AddMinutes(dateTimeOffSet).ToString();

                                //SMSMessage sms = new SMSMessage();

                                //string text = @"School fees of " + name + " INR " + amount + " is successfully submitted on " + dt + ". Subject to Bank Clearance. Thank you for your cooperation. Hariti Public School";
                                //sms.SendSMS(text, phone);

                                //SMSMessage sms1 = new SMSMessage();
                                //string text1 = name + @" की स्कूल फीस INR " + amount + " दिनांक " + dt + " को सफलतापूर्वक जमा हो चुकी है। राशि बैंक निकासी के अधीन है। आपके सहयोग के लिए धन्यवाद। Hariti Public School";
                                //sms1.SendSMS(text1, phone);
                            }
                        }

                        DashboardHub dash = new DashboardHub();

                        dash.DailyFeesUpdate();
#endif
                        return(rect_no);
                    }
                    else
                    {
                        throw new System.InvalidOperationException("Financial Year Expired");
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
示例#5
0
        public async Task AddStudent(sr_register std)
        {
            try
            {
                using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    mst_sessionMain sess = new mst_sessionMain();

                    string maxid = @"SELECT 
                                            IFNULL(MAX(sr_number), 0) + 1
                                        FROM
                                            sr_register";

                    //                var id = con.Query<mst_section>(maxid).ToString().Trim();

                    int id = con.ExecuteScalar <int>(maxid);



                    string query = @"INSERT INTO sr_register
                                   (sr_number
                                   ,std_first_name
                                   ,std_last_name
                                   ,std_father_name
                                   ,std_mother_name
                                   ,std_address
                                   ,std_address1
                                   ,std_address2
                                   ,std_district
                                   ,std_state
                                   ,std_country
                                   ,std_pincode
                                   ,std_contact
                                   ,std_contact1
                                   ,std_contact2
                                   ,std_email
                                   ,std_father_occupation
                                   ,std_mother_occupation
                                   ,std_blood_gp
                                   ,std_house_income
                                   ,std_nationality
                                   ,std_category
                                   ,std_cast
                                   ,std_dob
                                   ,std_sex
                                   ,std_last_school
                                   ,std_admission_date
                                   ,std_house
                                   ,std_remark
                                   ,std_active
                                   ,std_pickup_id
                                   ,std_admission_class
                                   ,adm_session
                                   ,reg_no
                                   ,reg_date
                                   ,std_aadhar)
                             VALUES
                                   (@sr_number
                                   ,@std_first_name
                                   ,@std_last_name
                                   ,@std_father_name
                                   ,@std_mother_name
                                   ,@std_address
                                   ,@std_address1
                                   ,@std_address2
                                   ,@std_district
                                   ,@std_state
                                   ,@std_country
                                   ,@std_pincode
                                   ,@std_contact
                                   ,@std_contact1
                                   ,@std_contact2
                                   ,@std_email
                                   ,@std_father_occupation
                                   ,@std_mother_occupation
                                   ,@std_blood_gp
                                   ,@std_house_income
                                   ,@std_nationality
                                   ,@std_category
                                   ,@std_cast
                                   ,@std_dob
                                   ,@std_sex
                                   ,@std_last_school
                                   ,@std_admission_date
                                   ,@std_house
                                   ,@std_remark
                                   ,@std_active
                                   ,@std_pickup_id
                                   ,@std_admission_class
                                   ,@adm_session
                                   ,@reg_no
                                   ,@reg_date
                                   ,@std_aadhar)";



                    std.std_active         = "Y";
                    std.sr_number          = id;
                    std.std_admission_date = DateTime.Parse(std.std_admission_date_str);
                    std.std_dob            = DateTime.Parse(std.std_dob_str);
                    await con.ExecuteAsync(query,
                                           new
                    {
                        std.sr_number
                        ,
                        std.std_first_name
                        ,
                        std.std_last_name
                        ,
                        std.std_father_name
                        ,
                        std.std_mother_name
                        ,
                        std.std_address
                        ,
                        std.std_address1
                        ,
                        std.std_address2
                        ,
                        std.std_district
                        ,
                        std.std_state
                        ,
                        std.std_country
                        ,
                        std.std_pincode
                        ,
                        std.std_contact
                        ,
                        std.std_contact1
                        ,
                        std.std_contact2
                        ,
                        std.std_email
                        ,
                        std.std_father_occupation
                        ,
                        std.std_mother_occupation
                        ,
                        std.std_blood_gp
                        ,
                        std.std_house_income
                        ,
                        std.std_nationality
                        ,
                        std.std_category
                        ,
                        std.std_cast
                        ,
                        std.std_dob
                        ,
                        std.std_sex
                        ,
                        std.std_last_school
                        ,
                        std.std_admission_date
                        ,
                        std.std_admission_class
                        ,
                        std.std_house
                        ,
                        std.std_remark
                        ,
                        std.std_pickup_id
                        ,
                        std.std_active
                        ,
                        std.adm_session
                        ,
                        std.reg_no
                        ,
                        std.reg_date
                        ,
                        std.std_aadhar
                    });

                    query = @"INSERT INTO `mst_std_class`
                            (`session`,
                            `sr_num`,
                            `class_id`)
                            VALUES
                            (@session,
                            @sr_num,
                            @class_id)";



                    await con.ExecuteAsync(query, new { session = sess.findActive_Session(), sr_num = std.sr_number, class_id = std.class_id });

                    query = @"INSERT INTO `mst_std_section`
                            (`session`,
                            `sr_num`,
                            `section_id`)
                            VALUES
                            (@session,
                            @sr_num,
                            @section_id)";



                    await con.ExecuteAsync(query, new { session = sess.findActive_Session(), sr_num = std.sr_number, section_id = std.std_section_id });

                    std_registrationMain main = new std_registrationMain();

                    main.DeleteRegistrationOnly(std.adm_session, std.reg_no, std.reg_date);

                    fees_receiptMain mstfees = new fees_receiptMain();
                    fees_receipt     fees    = new fees_receipt();

                    fees.sr_number  = std.sr_number;
                    fees.class_id   = std.class_id;
                    fees.section_id = std.std_section_id;

                    fees.reg_no = std.reg_no;

                    fees.reg_date = std.reg_date;

                    mstfees.updateReceipt(fees);

                    out_standing out_std = new out_standing();

                    out_std.acc_id        = 2;
                    out_std.outstd_amount = std.fees_amount;


                    out_standingMain out_stdMain = new out_standingMain();

                    out_std.sr_number = std.sr_number;
                    out_std.class_id  = std.class_id;

                    out_stdMain.AddOutStanding(out_std);

                    query = @"SELECT 
                                acc_id, fees_amount outstd_amount
                            FROM
                                mst_fees
                            WHERE
                                session = @session AND bl_onetime = 1
                                    AND acc_id not in (2,1)
                                    AND class_id = @class_id";

                    out_standing out_std_onetime = new out_standing();

                    out_std_onetime = con.Query <out_standing>(query, new { session = sess.findActive_Session(), class_id = std.class_id }).SingleOrDefault();

                    if (out_std_onetime != null)
                    {
                        out_std_onetime.sr_number = std.sr_number;

                        out_std_onetime.class_id = std.class_id;

                        out_stdMain.AddOutStanding(out_std_onetime);
                    }

                    out_std.reg_num = std.reg_no;

                    out_std.dt_date = std.reg_date;

                    out_std.class_id = std.class_id;

                    out_stdMain.updateOutstanding(out_std);

                    var p = new DynamicParameters();
#if !DEBUG
                    SMSMessage sms = new SMSMessage();

                    foreach (var item in sms.smsbody("admission"))
                    {
                        string body = item.Replace("#student_name#", std.std_first_name + " " + std.std_last_name);

                        body = body.Replace("#class#", std.std_admission_class);

                        body = body.Replace("#sr_number#", std.sr_number.ToString());

                        await sms.SendSMS(body, std.std_contact, true);
                    }
#endif

                    //string text = @"Admission of " + std.std_first_name + " " + std.std_last_name + " is confirmed in class " + std.std_admission_class + " via admission number " + std.sr_number + ". Congratulation for being a part of hariti family. Thank You. Hariti Public School.";

                    //sms.SendSMS(text, std.std_contact);

                    // text =  std.std_first_name + " " + std.std_last_name + " का प्रवेश कक्षा "+std.std_admission_class+" में होना सुनिश्चित हुआ है। जिसका प्रवेश क्रमांक " + std.sr_number + " है। हरिति परिवार से जुड़ने के लिये आपका धन्यवाद। Hariti Public School.";

                    //sms.SendSMS(text, std.std_contact);


                    p.Add("@sr_num", std.sr_number);


                    con.Execute("MonthlyFeesFullYear", p, commandType: System.Data.CommandType.StoredProcedure);

                    con.Execute("MonthlyTransportFullYear", p, commandType: System.Data.CommandType.StoredProcedure);

                    DashboardHub hub = new DashboardHub();

                    hub.DashboardSchoolStrength();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }