/*************************************************

        File name : admin.c
      Description : 管理员模块，拥有所有权限
           Others : Null
         Function : 1. 数据库的创建
                    1. 数据库的查询
                    1. 数据库的修改
          History : 2013-6-26 Gongpei 创建

    Copyright (C), 2013-2014, Gongpei

*************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include "main.h"
#include "sqlite3.h"

#define MAX 999


int adminForm()
{
    printf( "===============================================================================\n"
            "|                              管理员页面                                     |\n"
            "-------------------------------------------------------------------------------\n"
            "|                                                                             |\n"
            "|                           1、输入职工信息                                   |\n"
            "|                           2、查看职工信息                                   |\n"
            "|                           3、查找职工信息                                   |\n"
            "|                           4、删除职工信息                                   |\n"
            "|                           5、退        出                                   |\n"
            "|                                                                             |\n"
            "-------------------------------------------------------------------------------\n"
          );
    return checkOption(5,"请选择所需操作：","输入错误，请重新输入");
}

void deleteDB(sqlite3 *ems)
{
    int option,returnCode;
    char getInfo[20],SQL_delInfomation[MAX] = "",error;
    char choice[4][5] = { {"name"}, {"id"}, {"sex"}, {"tel"} };

    printf( "===============================================================================\n"
            "|                            删除职工信息                                     |\n"
            "-------------------------------------------------------------------------------\n"
            "|                                                                             |\n"
            "|         1.姓名查找   2.ID号查找    3.性别查找   4.电话查找                  |\n"
            "|                                                                             |\n"
            "-------------------------------------------------------------------------------\n"
          );

    option = checkOption(5,"请选择所需操作：","输入错误，请重新输入");
    printf("请输入信息：");
    scanf("%s",getInfo);

    //生成SQL语句
    strcat(SQL_delInfomation,"DELETE FROM emp WHERE ");
    strcat(SQL_delInfomation,choice[option-1]);
    strcat(SQL_delInfomation,"=\"");
    strcat(SQL_delInfomation,getInfo);
    strcat(SQL_delInfomation,"\"");

    //执行以上SQL
    returnCode = sqlite3_exec(ems,SQL_delInfomation,NULL,NULL,&error);
    if( returnCode != SQLITE_OK )
    {
        fprintf(stderr,"删除失败: %s\n",error);
    }
    else
    {
        printf("删除成功");
    }
    printf( "\n-------------------------------------------------------------------------------\n");
    system("pause");
    system("cls");
}

/*----------------------------------------------
       Function : 回调函数
    Description : 输出查询的表单信息
----------------------------------------------*/
int printRecord(void *params, int column, char **columnValue, char **columnName)
{
    int i;
    char blank[7][10] = { {"  "},{"   "},{"     "},{"      "},{"    "},{"      "},{"      "} };
    for(i = 0; i < column; i++)
    {
        printf("%s",blank[i]);
        printf("%s", columnValue[i]);
    }
    printf("\n");
    return 0;
}

/*----------------------------------------------
       Function : 查询信息
    Description : 查询职工信息函数
----------------------------------------------*/
void searchDB(sqlite3 *ems)
{
    int option,returnCode;
    char getInfo[20],SQL_getInfomation[MAX] = "",error;
    char choice[4][5] = { {"name"}, {"id"}, {"sex"}, {"tel"} };

    printf( "===============================================================================\n"
            "|                            查找职工信息                                     |\n"
            "-------------------------------------------------------------------------------\n"
            "|                                                                             |\n"
            "|         1.姓名查找   2.ID号查找    3.性别查找   4.电话查找                  |\n"
            "|                                                                             |\n"
            "-------------------------------------------------------------------------------\n"
          );

    option = checkOption(5,"请选择所需操作：","输入错误，请重新输入");
    printf("请输入信息：");
    scanf("%s",getInfo);

    //生成SQL语句
    strcat(SQL_getInfomation,"SELECT * FROM emp WHERE ");
    strcat(SQL_getInfomation,choice[option-1]);
    strcat(SQL_getInfomation,"=\"");
    strcat(SQL_getInfomation,getInfo);
    strcat(SQL_getInfomation,"\"");

    //执行以上SQL
    system("cls");
    printf( "===============================================================================\n"
            "|                           以下是查询结果                                    |\n"
            "-------------------------------------------------------------------------------\n"
            "|  ID  |  姓名  |  性别  |  出生日期   |   部门   |   工资   |      电话      |\n"
            "-------------------------------------------------------------------------------\n"
          );
    returnCode = sqlite3_exec(ems,SQL_getInfomation,printRecord,NULL,&error);
    if( returnCode != SQLITE_OK || returnCode == SQLITE_NOTFOUND)
    {
        fprintf(stderr,"查找失败: %s\n",error);
    }
    printf( "-------------------------------------------------------------------------------\n");
    system("pause");
    system("cls");

}

/*----------------------------------------------
       Function : 查看信息
    Description : 查看职工信息函数
----------------------------------------------*/
void viewDB(sqlite3 *ems)
{
    int returnCode;
    char error;
    char *SQL_getInfomation = "SELECT * FROM emp";


    //执行以上SQL
    system("cls");
    printf( "===============================================================================\n"
            "|                           以下是查询结果                                    |\n"
            "-------------------------------------------------------------------------------\n"
            "|  ID  |  姓名  |  性别  |  出生日期   |   部门   |   工资   |      电话      |\n"
            "-------------------------------------------------------------------------------\n"
          );
    returnCode = sqlite3_exec(ems,SQL_getInfomation,printRecord,NULL,&error);
    if( returnCode != SQLITE_OK || returnCode == SQLITE_NOTFOUND)
    {
        fprintf(stderr,"查找失败: %s\n",error);
    }
    printf( "-------------------------------------------------------------------------------\n");
    system("pause");
    system("cls");

}


/*----------------------------------------------
       Function : 输入信息
    Description : 输入职工信息函数
----------------------------------------------*/
int enterDB(sqlite3 *ems)
{
    char id[10]="",name[10]="",bday[15]="",payment[10]="",branch[20]="",tel[15]="",sex[4]="",
         SQL_enterInfo[MAX] = "",continueEnter,*error;

    printf( "===============================================================================\n"
            "|                               输入信息                                      |\n"
            "-------------------------------------------------------------------------------\n\n"
          );

    //开始循环输入职工信息
    while( 1 )
    {
        //开始输入信息
        printf("  ID：");
        scanf("%s", id);

        printf("  姓名：");
        scanf("%s", name);

        printf("  性别：");
        scanf("%s", sex);

        printf("  出生日期：");
        scanf("%s", bday);

        printf("  部门：");
        scanf("%s", branch);

        printf("  工资：");
        scanf("%s", payment);

        printf("  电话：");
        scanf("%s", tel);

        //开始合成SQL语句
        strcat(SQL_enterInfo,"INSERT INTO emp(id,name,sex,bday,branch,payment,tel) VALUES(");
        strcat(SQL_enterInfo,"\"");
        strcat(SQL_enterInfo,id);
        strcat(SQL_enterInfo,"\",\"");
        strcat(SQL_enterInfo,name);
        strcat(SQL_enterInfo,"\",\"");
        strcat(SQL_enterInfo,sex);
        strcat(SQL_enterInfo,"\",\"");
        strcat(SQL_enterInfo,bday);
        strcat(SQL_enterInfo,"\",\"");
        strcat(SQL_enterInfo,branch);
        strcat(SQL_enterInfo,"\",\"");
        strcat(SQL_enterInfo,payment);
        strcat(SQL_enterInfo,"\",\"");
        strcat(SQL_enterInfo,tel);
        strcat(SQL_enterInfo,"\")");
        printf("%s",SQL_enterInfo);

        //执行以上SQL
        if(sqlite3_exec(ems,SQL_enterInfo,NULL,NULL,&error) != SQLITE_OK)
        {
            fprintf(stderr,"创建失败: %s\n",error);
        }

        //判断是否继续输入信息
        fflush(stdin);
        printf("\n  是否继续输入？（Y/N）：");
        scanf("%c",&continueEnter);
        if( continueEnter == 'Y' || continueEnter == 'y' )
        {
            strcpy(SQL_enterInfo,"");
            fflush(stdin);
            continue;
        }
        else if( continueEnter == 'N' || continueEnter == 'n' )
        {
            system("cls");
            break;
        }
    }

    return 0;
}

/*----------------------------------------------
       Function : 建立数据库
    Description : 如果不存在ems.db则使用
                  此函数新建数据库
----------------------------------------------*/
int createDB()
{
    //创建职工表的SQL
    const char *SQL_createEmployeesTable = "CREATE TABLE emp(id VARCHAR(10),name VARCHAR(10),sex VARCHAR(4),bday VARCHAR(15),"
                                           "branch VARCHAR(20),payment VARCHAR(10),tel VARCHAR(15))";

    char *errmsg = 0;
    int returnCode = 0;

    //打开数据库文件
    sqlite3 *db = 0;
    returnCode = sqlite3_open("ems.db",&db);
    if(returnCode != SQLITE_OK)
    {
        fprintf(stderr,"无法打开数据库: %s\n",sqlite3_errmsg(db));
        return ERROR;
    }

    //创建数据库及表结构
    returnCode = sqlite3_exec(db,SQL_createEmployeesTable,NULL,NULL,&errmsg);
    if(returnCode != SQLITE_OK)
    {
        fprintf(stderr,"创建失败: %s\n",errmsg);
    }
    else
    {
        printf("创建成功，请按任意键继续\n");
    }

    //关闭数据库
    sqlite3_free(errmsg);
    sqlite3_close(db);


    getchar();
    getchar();
    system( "cls" );
    return 0;
}

/*----------------------------------------------
       Function : 数据库创建修改查询
    Description : admin.c主函数
----------------------------------------------*/
void adminMain()
{
    int returnCode = 0;
    char *error;
    sqlite3 *ems;

    returnCode = sqlite3_open("ems.db",&ems);
    if(returnCode != SQLITE_OK)
    {
        fprintf(stderr,"无法打开数据库: %s\n",sqlite3_errmsg(ems));
    }
    else
    {
        while( 1 )
        {
            switch( adminForm() )
            {
                //输入职工信息
                case 1: system( "cls" );
                        //createDB();
                        enterDB(ems);
                        break;
                //查看职工信息
                case 2: system( "cls" );
                        viewDB(ems);
                        break;
                //查找职工信息
                case 3: system( "cls" );
                        searchDB(ems);;
                        break;
                //删除职工信息
                case 4: system( "cls" );
                        deleteDB(ems);
                        break;
                //退出
                case 5: //关闭数据库
                        sqlite3_free(error);
                        sqlite3_close(ems);
                        userType = VISITOR;
                        break;

                default : exit(0);
            }
            if( userType == 0 )
            {
                break;
            }
        }

    }
}